Wednesday, March 30, 2011

AR INTERFACES

INTERFACE TABLES

RA_CUSTOMERS_INTERFACE_ALL

RA_CUSTOMER_PROFILES_INT_ALL

RA_CONTACT_PHONES_INT_ALL

RA_CUSTOMER_BANKS_INT_ALL

RA_CUST_PAY _METHOD_INT_ALL

RA_Cust_Pay_Method_Int_All

BASE TABLES

RA_CUSTOMERS

RA_ADDRESSES_ALL

RA_CUSTOMER_RELATIONSHIPS_ALL

RA_SITE_USES_ALL

Moving the Staging Table Data into Base tables of AR Customer thro’ Interfaces:

Creating three Staging tables and Inserting data into the Tables:

CREATE TABLE XXAR_CUST_STG (

INSERT_UPDATE_FLAG VARCHAR2 (1 Byte),

PARTY_TYPE VARCHAR2 (30 Byte),

CUSTOMER_NAME VARCHAR2 (50 Byte),

CUSTOMER_NUMBER VARCHAR2 (30 Byte),

ORIG_SYSTEM_CUSTOMER_REFERENCE VARCHAR2 (240 Byte),

PRIMARY_USE_CODE_FLAG VARCHAR2 (1 Byte),

STATUS VARCHAR2 (1 Byte),

SITE_USE_CODE VARCHAR2 (30 BYTE),

ADDRESS_ID NUMBER (15),

ORIG_SYSTEM_ADDRESS_REFERENCE VARCHAR2 (240 Byte),

ADDRESS1 VARCHAR2 (240 Byte),

ADDRESS2 VARCHAR2 (240 Byte),

CITY VARCHAR2 (60 Byte),

STATE VARCHAR2 (60 Byte),

COUNTRY VARCHAR2 (60 Byte)

);

Desc XXAR_CUST_STG

Alter table XXAR_CUST_STG add primary_site_use_flag VARCHAR2 (30 BYTE)

Drop table XXAR_CUST_STG

INSERT INTO XXAR_CUST_STG

Values ('I','ORGANIZATION','Archies','', 10112,'A'

,'SHIP_TO', ,'2021','#456, Lane 78'

,'Sustain Lane', 'SINGAPORE', 'SG', ’SG’,'Y');

INSERT INTO XXAR_CUST_STG

Values ('I','ORGANIZATION','Archies','', 10112,'A'

,'BILL_TO', ,'2021','#456,A-Block, Lane 78'

,'Sustain Lane', 'SINGAPORE', 'SG', ’SG’,'Y');

create table XXAR_PROFILE_STG (

INSERT_UPDATE_FLAG VARCHAR2 (1 BYTE),

ORIG_SYSTEM_CUSTOMER_REF VARCHAR2 (240 BYTE),

CUSTOMER_PROFILE_CLASS_NAME VARCHAR2 (30 BYTE),

CREDIT_CHECKING VARCHAR2 (1 BYTE),

COLLECTOR_NAME VARCHAR2 (30 BYTE),

CREDIT_HOLD VARCHAR2 (1 BYTE),

DUNNING_LETTERS VARCHAR2 (1 BYTE),

STATEMENTS VARCHAR2 (1 BYTE)

DUNNING_LETTER_SET_NAME VARCHAR2 (30 Byte)

STATEMENT_CYCLE_NAME VARCHAR2 (15 Byte));

INSERT INTO XXAR_PROFILE_STG

Values ('I', 10112,'BR Profile 1',’Y’,'Kerry','Y','N','N')

ORIG_SYSTEM_CUSTOMER_REF VARCHAR2 (240 BYTE),

ORIG_SYSTEM_ADDRESS_REF VARCHAR2 (240 BYTE),

INSERT_UPDATE_FLAG VARCHAR2 (1 BYTE),

ORIG_SYSTEM_TELEPHONE_REF VARCHAR2 (240 BYTE),

TELEPHONE VARCHAR2 (25 BYTE),

TELEPHONE_TYPE VARCHAR2 (30 BYTE),

ORIG_SYSTEM_CONTACT_REF VARCHAR2 (240 BYTE),

CONTACT_LAST_NAME VARCHAR2 (50 BYTE)

)

XXAR_PHONE_STG

insert into XXAR_PHONE_STG Values('10112','2021','I','Tel002','9849771099','PHONE','Cnt01','Arnold');

create table XXAR_BANK_STG(

ORIG_SYSTEM_CUSTOMER_REF VARCHAR2 (240 BYTE),

PRIMARY_FLAG VARCHAR2 (1 BYTE),

START_DATE DATE,

BANK_ACCOUNT_NAME VARCHAR2 (80 BYTE),

BANK_ACCOUNT_CURRENCY_CODE VARCHAR2 (15 BYTE),

BANK_ACCOUNT_NUM VARCHAR2 (30 BYTE),

BANK_BRANCH_NAME VARCHAR2 (60 BYTE),

ORIG_SYSTEM_ADDRESS_REF VARCHAR2 (240 BYTE));

insert into XXAR_BANK_STG values('10112',

'Y','20-Nov-08','LGS Bank','INR','289012384','Lgs Branch','2021');

create table XXAR_CUSTPAY_STG(

ORIG_SYSTEM_CUSTOMER_REF VARCHAR2 (240 BYTE),

PAYMENT_METHOD_NAME VARCHAR2 (30 BYTE),

PRIMARY_FLAG VARCHAR2 (1 BYTE),

START_DATE date,

ORIG_SYSTEM_ADDRESS_REF VARCHAR2 (240 BYTE));

insert into XXAR_CUSTPAY_STG values(

'10122','CASH','Y','05-jan-2009','2020')

------------------------------------------------------------------

Create table XXAR_LOC_STG (

SITE_USE_ID NUMBER (15) ,

SITE_USE_CODE VARCHAR2 (30 Byte),

ADDRESS_ID NUMBER (15) ,

PRIMARY_FLAG VARCHAR2 (1 Byte),

STATUS VARCHAR2 (1 Byte),

LOCATION VARCHAR2 (40 Byte) )

create table XXAR_SITE_STG(

SITE_USE_ID NUMBER (15),

SITE_USE_CODE VARCHAR2 (30 Byte),

ADDRESS_ID NUMBER (15),

PRIMARY_FLAG VARCHAR2 (1 Byte),

STATUS VARCHAR2 (1 Byte),

LOCATION VARCHAR2 (40 Byte) )

SQL * LOADER

options(skip=0)

load data

infile '/ebs12/oracle/apps/apps_st/appl/ar/12.0.0/bin/customer flat file.csv'

insert into table xxcustomersstg

fields terminated by ','

optionally enclosed by '"'

trailing nullcols

(orig_system_customer_ref,

INSERT_UPDATE_FLAG,

Customer_name,

Customer_number,

Customer_status,

person_flag,

person_first_name,

Person_last_name,

primary_site_use_flag,

Location,

site_use_code,

address1,

address2,

city,

Province,

Postal_code,

state,

County,

Country,

orig_system_address_ref,

Customer_profile_class_name,

credit_hold,

Collector_name,

credit_balence_statements,

CREDIT_CHECKING,

AUTO_REC_MIN_RECEIPT_AMOUNT,

DISCOUNT_TERMS,

DUNNING_LETTERS,

STATEMENTS,

INTEREST_CHARGES,

INTEREST_PERIOD_DAYS,

TOLERANCE,

OVERRIDE_TERMS,

ORIG_SYSTEM_TELEPHONE_REF,

TELEPHONE,

TELEPHONE_TYPE,

PRIMARY_FLAG,

START_DATE DATE,

BANK_ACCOUNT_NUM,

BANK_ACCOUNT_CURRENCY_CODE,

bank_account_name,

Bank_branch_name,

PAYMENT_METHOD_NAME,bank_home_country,org_id)

Create a package with validations to move the data from Staging table to Interface table

CREATE OR REPLACE PACKAGE XXAR_CUSTINT_PKG

IS

PROCEDURE xxar_custint_prc (errbuf out varchar2,Retcode out varchar2);

END;

CREATE OR REPLACE Package body XXAR_CUSTINT_PKG

Is

Procedure XXAR_CUSTINT_PRC (errbuf out varchar2, Retcode out varchar2)

Is

Cursor AR_CUR

IS

SELECT INSERT_UPDATE_FLAG, primary_site_use_flag

, CUSTOMER_NAME, CUSTOMER_NUMBER, ORIG_SYSTEM_CUSTOMER_REF, SITE_USE_CODE, STATUS, ORIG_SYSTEM_ADDRESS_REF, ADDRESS1, ADDRESS2, CITY, STATE, COUNTRY FROM XXAR_CUST_STG;

CURSOR AR_PROF (ORIG_SYSTEM_CUSTOMER_REF NUMBER)

IS

SELECT INSERT_UPDATE_FLAG, ORIG_SYSTEM_CUSTOMER_REF

, CUSTOMER_PROFILE_CLASS_NAME, CREDIT_HOLD

, COLLECTOR_NAME, DUNNING_LETTER_SET_NAME, STATEMENT_CYCLE_NAME

FROM XXAR_PROFILE_STG;

--Variable Declaration

Lv_INSERT_UPDATE_FLAG varchar2 (1);

Lv_primary_site_use_flag varchar2 (1);

Lv_site_use_code VARCHAR2 (30 Byte);

Lv_country varchar2 (6);

LV_CUSTOMER_PROFILE_CLASS_NAME VARCHAR2 (30 BYTE);

LV_COLLECTOR_NAME VARCHAR2 (30 BYTE);

LV_CREDIT_HOLD VARCHAR2 (1 BYTE);

LV_DUNNING_LETTER_SET_NAME VARCHAR2 (30 BYTE);

LV_STATEMENT_CYCLE_NAME VARCHAR2 (15 BYTE);

Lv_status varchar2 (1);

Lv_err_flag char (1):='A';

lv_exp varchar2 (5);

BEGIN

-- Cursor for Loop

For i in AR_CUR

LOOP

BEGIN

Select INSERT_UPDATE_FLAG into lv_INSERT_UPDATE_FLAG

From XXAR_CUST_STG where INSERT_UPDATE_FLAG IN ('I','U');

EXCEPTION

WHEN OTHERS THEN

lv_err_flag:='E';

fnd_file.put_line

(fnd_file.LOG, 'INSERT_UPDATE_FLAG is not valid-- ');

END;

fnd_file.put_line

(fnd_file.LOG, 'Inserting INSERT_UPDATE_FLAG -- '||lv_INSERT_UPDATE_FLAG);

Begin

Select primary_SITE_USE_flag into lv_primary_site_use_flag from XXAR_CUST_STG

Where primary_site_use_flag in ('Y','N');

Exception

When no_data_found then

lv_err_flag:= 'N';

fnd_file.put_line (fnd_file.log,'The flag should be either yes or no');

When too_many_rows then

lv_exp:= null;

End;

fnd_file.put_line (fnd_file.log,'Inserting the primary_SITE_USE_flag --'||lv_primary_SITE_USE_flag );

Begin

Select Territory_code into lV_Country from fnd_territories

Where Territory_code = i.country;

Exception

When no_data_found then

lv_err_flag := 'N';

FND_file.put_line(fnd_file.log,'The Country code given doesnot exist');

When too_many_rows then

lv_eXp := null;

End;

FND_file.put_line(fnd_file.log,'Inserting The Country code--'||lv_country);

Begin

Select lookup_code into lv_site_use_code from ar_lookups

Where lookup_type = 'SITE_USE_CODE'

And lookup_code = i.site_use_code;

Exception

When no_data_found then

lv_err_flag:= 'N';

lv_site_use_code:= null;

fnd_file.put_line (fnd_file.log, 'The Site_use_code doesn’t exist please change it and run it one more time');

When too_many_rows then

lv_exp:= null;

End;

fnd_file.put_line (fnd_file.log,'Inserting Site_use_code--'||lv_site_use_code);

BEGIN

Select status into lv_status from XXAR_CUST_STG where

Status in ('A','I');

EXCEPTION

WHEN OTHERS THEN

lv_err_flag:='E';

fnd_file.put_line

(fnd_file.LOG, 'CUSTOMER_status is not valid... ');

END;

fnd_file.put_line

(fnd_file.LOG, 'Inserting CUSTOMER_status -- '||lv_status);

BEGIN

SELECT CREDIT_HOLD into lv_credit_hold from XXAR_PROFILE_STG

Where credit_hold in ('Y','N');

EXCEPTION

WHEN OTHERS THEN

lv_err_flag:='E';

fnd_file.put_line

(fnd_file.LOG,' CREDIT_HOLD is not valid... ');

END;

fnd_file.put_line

( fnd_file.LOG,'Inserting CREDIT_HOLD -- '|| lv_CREDIT_HOLD);

--Cursor loop starts here ---

FOR j in AR_PROF (i.ORIG_SYSTEM_CUSTOMER_REF)

LOOP

BEGIN

Select NAME into LV_CUSTOMER_PROFILE_CLASS_NAME from AR_CUSTOMER_PROFILE_CLASSES where

NAME=j.CUSTOMER_PROFILE_CLASS_NAME;

EXCEPTION

WHEN OTHERS THEN

lv_err_flag:='E';

fnd_file.put_line

(fnd_file.LOG,' CUSTOMER_PROFILE_CLASS_NAME is not valid... ');

END;

fnd_file.put_line

(fnd_file.LOG,'Inserting CUSTOMER_PROFILE_CLASS_NAME -- '|| lv_CUSTOMER_PROFILE_CLASS_NAME);

BEGIN

Select NAME into LV_COLLECTOR_NAME from ar_collectors where NAME=j.COLLECTOR_NAME;

EXCEPTION

WHEN OTHERS THEN

lv_err_flag:='E';

fnd_file.put_line

(fnd_file.LOG,' COLLECTOR_NAME is not valid... ');

END;

fnd_file.put_line

(fnd_file.LOG,'Inserting COLLECTOR_NAME -- '|| lv_COLLECTOR_NAME);

BEGIN

Select ac.NAME into LV_DUNNING_LETTER_SET_NAME from ar_dunning_letter_sets ac where

ac.NAME=j.DUNNING_LETTER_SET_NAME;

EXCEPTION

WHEN OTHERS THEN

lv_err_flag:='E';

fnd_file.put_line

(fnd_file.LOG,' DUNNING_LETTER_SET_NAME is not valid... ');

END;

fnd_file.put_line

(fnd_file.LOG, 'Inserting DUNNING_LETTER_SET_NAME -- '|| lv_DUNNING_LETTER_SET_NAME);

BEGIN

Select NAME into LV_STATEMENT_CYCLE_NAME from ar_statement_cycles where NAME=j.STATEMENT_CYCLE_NAME;

EXCEPTION

WHEN OTHERS THEN

lv_err_flag:='E';

fnd_file.put_line

(fnd_file.LOG,' STATEMENT_CYCLE_NAME is not valid... ');

END;

fnd_file.put_line

(fnd_file.LOG,'Inserting STATEMENT_CYCLE_NAME -- '|| lv_STATEMENT_CYCLE_NAME);

IF lv_err_flag='A' THEN

INSERT INTO RA_CUSTOMERS_INTERFACE_ALL (

INSERT_UPDATE_FLAG

, ORIG_SYSTEM_CUSTOMER_REF

, CUSTOMER_STATUS

, CUSTOMER_NAME

, CUSTOMER_NUMBER

, SITE_USE_CODE

, PRIMARY_SITE_USE_FLAG

, ORIG_SYSTEM_ADDRESS_REF

, ADDRESS1, ADDRESS2

, CITY, STATE

, COUNTRY

, LAST_UPDATED_BY

, LAST_UPDATE_DATE

, CREATED_BY

, CREATION_DATE)

VALUES (LV_INSERT_UPDATE_FLAG

, i.ORIG_SYSTEM_CUSTOMER_REF

, LV_STATUS

--, i.PARTY_NUMBER

, i.CUSTOMER_NAME

, i.CUSTOMER_NUMBER

, i.site_use_code

, lv_PRIMARY_SITE_USE_FLAG

, i.ORIG_SYSTEM_ADDRESS_REF

, i.ADDRESS1, i.ADDRESS2

, i.CITY

, i.STATE

, i.COUNTRY

, -1, SYSDATE

, -1, SYSDATE

);

INSERT INTO RA_CUSTOMER_PROFILES_INT_ALL (

INSERT_UPDATE_FLAG

, ORIG_SYSTEM_CUSTOMER_REF

, CREDIT_BALANCE_STATEMENTS

, DUNNING_LETTERS

, STATEMENTS

, CUSTOMER_PROFILE_CLASS_NAME

, DUNNING_LETTER_SET_NAME

, COLLECTOR_NAME

, CREDIT_HOLD

, STATEMENT_CYCLE_NAME

, LAST_UPDATED_BY

, LAST_UPDATE_DATE

, CREATED_BY

, CREATION_DATE)

VALUES (

LV_INSERT_UPDATE_FLAG

, j.ORIG_SYSTEM_CUSTOMER_REF

, 'N'

, 'Y'

, 'N'

, LV_CUSTOMER_PROFILE_CLASS_NAME

, LV_DUNNING_LETTER_SET_NAME

, LV_COLLECTOR_NAME

, LV_CREDIT_HOLD

, LV_STATEMENT_CYCLE_NAME

,-1, SYSDATE

,-1, SYSDATE);

END IF;

END LOOP;

END LOOP;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG,

'Error in inserting data into interface tables ');

fnd_file.put_line (fnd_file.LOG, SQLCODE || ' ' || SQLERRM);

Commit;

END XXAR_CUSTINT_PRC;

END XXAR_CUSTINT_PKG;

/

IN ORACLE APPLICATIONS:

Create an Executable in Apps – XXAR_CUSTINT_PKG_EXEC

Application— xxmz Custom

Create a Concurrent Program by attaching the Executable --

XXAR_CUSTINT_PKG_EXEC IFace Conc Prgm

Attach the Concurrent program to Request Group – xxmz Request Group

In XXMZ Custom Module, Run the Concurrent Program

In RECEIVABLE Module, Run the Standard Concurrent Program

· Customer Interface

Power of Autoinvoice


Here is note on one of most efficient tool used in Oracle application. It is Auto Invoice, most industry accepted tool in Oracle apps.

What is Auto Invoice??

Auto Invoice is a tool that can be used to import and validate transaction data from other financial systems from which one can create invoices, debit memos, credit memos, and on-account credits. It rejects transactions with invalid information to insure the integrity of the data.

Where its fits

This fits well with in Oracle ERP or to integrate with any third party application< ?xml:namespace prefix ="" o />

What Module data can be integrated?

Oracle Order Management

Oracle Project Accounting

Oracle services

To make fully functional what else required

Loader program

Validation program

Top 10 reasons for using Auto Invoice

1. Powerful Interface Tool

2. Supports Oracle & Non-Oracle Systems

3. Import Large Amount of Data

4. Calculate or Import Tax

5. Group Lines & Invoices

6. Online Error Correction

7 .Lines Validation

8. Derive GL Date

9 .Import Flex fields

10.Import or Derive Accounting Info

What is inside AutoInvoice

AutoInvoice is a tool consists of 3 main programs. Each program will have unique nature of work to do and they are called internally except Purge program whose execution is derived on the setup otherwise ready to execute stand alone.

§ Master (RAXMTR)

§ Import (RAXTRX)

§ Purge (RAXDEL)

1. Auto Invoice Master program RAXMTR

Selects and marks records in the interface tables to be processed based on the parameters the user entered and then calls the AutoInvoice Import program. Auto Invoice Master program has no report output.

•Gathers statistics, it means it gathers the stats on interface tables and set the stats on certain indices on interface tables

•Marks interface records for processing by marking request_id

•Submits multiple workers for Parallel Processing by creating instances for request.


2. Auto Invoice Import Program Validates the selected record and creates transaction if it passes validation. Any record that fails validation is left in the interface table with an error code. Depending on the setup, related records may be rejected as well. This program has an output file called Auto Invoice Execution report, which you can view by clicking the View Report button in the Requests window.

Workhorse of Auto invoice

Validates data

Inserts records

Deletes interface data

Only when system option purge set to ‘Y’

3. Auto Invoice Purge Program Deletes records from the interface tables. If you set the Purge Interface Table system option to No in Define System Option window, Auto Invoice does not delete processed records from the interface tables after each run,and we must submit Auto Invoice Purge Program periodically to clean up the interface tables. This program only deletes transaction lines that have been successfully imported.

•Deletes all rows where interface_status =‘P’

•Ra_interface_lines

•Ra_interface_distributions

•Ra_interface_sales credits

How to start

As discussed above, oracle Receivable’s Auto Invoice program will be used to import and validate Invoices.

A custom feeder program is required to transfer data from the Advantage extract files and populate the Auto Invoice interface tables (RA_INTERFACE_LINES_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL).If there is need to run populate sales credit into RA_INTERFACE_SALESCREDITS_ALL table.

When run, AutoInvoice produces the AutoInvoice Execution Report and the AutoInvoice Validation Report.

Any entries which failed validation can be reviewed in Oracle Receivables’ AutoInvoice Interface Exceptions window. Depending on the error, changes may need to be made in Receivables, the feeder program or the imported records in the interface tables.

How Auto invoice Execution works

Normally, Auto Invoice can be divided into three major phases


Pre-grouping: here the validates all of the line level data takes place

Grouping: groups lines based on the grouping rules and validates header level data

Transfer :validates information that exists in Receivables tables

What happen when Auto invoice run


Once the Auto invoice Program gets called, the following activity takes place is part of execution process. This can be analyzed by debug options.

Line, accounting, and sales credit information for each line populates 3 interface tables

Lines are ordered and grouped

Tax is calculated

GL date is determined

GL accounts are assigned using Auto Accounting

Tax, freight, commitments, and credit memos are linked to transaction lines

All transactions are batched

Validated lines are used to create the transaction

How Data is flowing

Select, insert and update and delete take place on certain tables once it is logged out.

Selects

– RA_INTERFACE_LINES_ALL

– RA_INTERFACE_DISTRIBUTIONS_ALL

– RA_INTERFACE_SALESCREDITS_ALL

Updates/Insert

– RA_INTERFACE_ERRORS_ALL

– RA_CUSTOMER_TRX_ALL

– RA_CUSTOMER_TRX_LINES_ALL

– AR_PAYMENT_SCHEDULES_ALL

– AR_RECEIVABLE_APPLICATIONS_ALL

Inserts

– RA_INTERFACE_ERRORS_ALL


AutoInvoice Exception Handling


Records that fail validation are called ‘Exceptions’

Exceptions stay in Interface Tables which is RA_INTERFACE_ERRORS_ALL

Errors can be corrected in the Exception Handling window

Once corrections are made, Auto invoice must be resubmitted

Records that pass validation get transferred to Receivables tables

AutoInvoice Exception Handling Windows

Interface Exception window displays exception messages associated with all invalid records

Interface Lines window displays records that fail validation, provides an error message and can be used to correct the errors

The Line Errors windows displays errors associated with a specific line, and can only be opened from Interface Lines window

Interface Exceptions window displays Interface Id, Exception Type, Error Message and Invalid Value associated to the error

Data cannot be edited in this window, but error can be viewed and corrected by clicking the Details button

Error Message and Column name with invalid data are displayed in the Message column, and the invalid value that needs to be corrected is displayed in the Invalid Value column

0 comments:

About This Blog

  © Blogger templates The Professional Template by Ourblogtemplates.com 2008

Back to TOP