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_STGValues('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
Dear Mr.Bhaskar,
ReplyDeleteIs there any possibility to change the GL date of the transcaction pending in Auto invoice interface table.
Two lines from Apr-12 are stucked in autoinvoice interface table is due to GL period not open.
As we finish the auditing for the said period,i cannot open the period.
Is there any possibility to account these transactions in Oct-12 by changing the GL Dat from interface table.
Yours great support is highly appreciated.
Mohamed Irshad
Abu Dhabi