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
2 comments:
Not Good
Its very good
Post a Comment