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 C.P. Registration :
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
