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:
Post a Comment