open interfaces
TRANSCRIPT
Oracle Apps Open Interfaces
Objectives ….
The concept of Open Interface
The tasks involved in Open Interfaces
To Understand ….
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
Customer InterfaceAutoInvoiceAutoLock Box
Purpose
To Integrate Oracle Financial with External / Feeder Systems
To Import Historical Data from Previous systems
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
Overview
Feeder SystemData
Oracle ApplicationsInterface Tables
Oracle ApplicationsSystem
Feeder Program
Oracle Applications Import Program
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
Feeder program types
- SQL*Loader
- PL/SQL
- PRO*C
- PRO*COBOL
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
Features of Import Programs
- Easy Import
- Data Validation
- Review of Import
- Error Identification
- Error Correction
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
Commonly Used Interfaces
- Customer Interface
- Invoices for Receivables Interface
- LockBox/Receipts Interface
- Invoices for Payables Interface
- Inventory Item Interface
- Inventory Transaction Interface
- Journal Entries
- Mass Editions
- PO Requisitions
- Sales Orders
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
Customer Interface
Feeder System
Customer Tables
Use an import programto format & load data intoCustomer Interface tables
Submit Customer Interfaceto validate data & transfer itto Customer tables (Executable - RACUST)
Over View
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
RA_CUSTOMER_INTERFACE RA_CUSTOMER_PROFILES_INTERFACE
RA_CONTACT_PHONES_INTERFACE
RA_CUSTOMERS_BANKS_INTERFACE
RA_CUST_PAY_METHOD_INTERFACE
Validations
• QuickCodes
- Countries
- Site use codes
- Credit ratings
- Customer classes
….
• Payment Methods
• Payment Terms
• Customer profile classes
• Bank Information
•…..
Customer Interface
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
Required Data ( Ra_Customers_Interface )
Column Name ValidationOrig_System_Customer_Ref Must not exist in RA_CUSTOMERS for insert record
Must exists in RA_CUSTOMERS for update record(Desc:- Unique customer identifier for feeder system
Orig_System_Address_Ref Must not exist in RA_ADDRESSES for insert record.(If you are importing an address) Must exists in RA_ADDRESSES for update record.
(Desc:- Unique address identifier for feeder system)
Site_Use_Code Must exists in AR_LOOKUPS.Lookup_Code(If you are importing a business purpose)Customer_Number Must be NULL if automatic customer numbering is used
It should be unique within RA_CUSTOMERSCustomer_Name Same customer reference cannot have different name
Insert_Update_Flag ‘I’ for insert & ‘U’ for updateCustomer_Status ‘A’ for active & ‘I’ for inactiveInterface_Status Should be NULL
(Desc:- Error messages that apply to this interface record.)Primary_Site_Use_Flag ‘Y’ or ‘N’ to indicate whether this is the primary business purpose.(If you are importing an address) Only for new records & you have value in
Orig_System_Address_Ref
Customer Interface
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
Required Data ( Ra_Customers_Profiles_Interface )
Column Name ValidationOrig_System_ Customer_Ref Must not exist in RA_CUSTOMERS for insert record.
Must exists in RA_CUSTOMERS for update record.
Orig_System_Address_Ref Must not exist in RA_ADDRESSES for insert record.(If you are importing a profile Must exists in RA_ADDRESSES for update record.for customer address)
Customer_Profile_Class_Name Must exists in AR_CUSTOMER_PROFILE_CLASSES with Status=‘A’
Insert_Update_Flag ‘I’ for insert & ‘U’ for updateInterface_Status Should be NULL Credit_Hold ‘Y’ for yes & ‘N’ for no Credit_Rating Must exists in AR_LOOKUPS.Lookup_Code
Customer Interface
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
Required Data ( Ra_Contact_Phones_Interface )
Column Name ValidationOrig_System_Customer_Ref Must not exist in RA_CUSTOMERS for insert record.
Must exists in RA_CUSTOMER for update record.
Orig_System_Address_Ref Required if telephone no for an address.
Orig_System_Contact_Ref Required if telephone no for contact
Orig_System_Telephone_Ref To uniquely identify this telephone in your original system.
Telephone_Type Must exists in AR_LOOKUP.Lookup_CodeContact_Last_Name Required if telephone no for contact
Insert_Update_Flag ‘I’ for insert & ‘U’ for update
Interface_Status Should be NULL
Customer Interface
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
Required Data ( Ra_Customer_Banks_Interface )
Column Name ValidationOrig_System_Customer_Ref Must not exist in RA_CUSTOMERS for insert record.
Must exists in RA_CUSTOMER for update record.
Primary_Flag ‘Y’ or “N’ to indicate whether this is primary bank account for customer or Bill-To address
Start_Date To get bank account effective
Bank_Account_Name Should exists in AP_BANK_ACCOUNTS.Bank_Name
Interface_Status Should be NULL
Customer Interface
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
Required Data ( Ra_Cust_Pay_Method_Interface )
Column Name ValidationOrig_System_Customer_Ref Must not exist in RA_CUSTOMERS for insert record.
Must exists in RA_CUSTOMER for update record.
Primary_Flag. ‘Y’ or ‘N’
Start_Date Effective Date
Payment_Method_Name Must exists in AR_RECEIPTS_METHOD
Interface_Status Should be NULL
Customer Interface
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
System Tables updated by Customer Interface
• RA_CUSTOMERS• RA_ADDRESSES• RA_CONTACTS• RA_PHONES• RA_CUST_RECEIPT_METHODS• AR_CUSTOMER_PROFILES
Customer Interface
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
TIPS
Customers with multiple addresses (Say two addresses)- Enter two records in RA_CUSTOMER_INTEFACE- With identical Orig_System_Customer_Ref- But different Orig_System_Address_Ref
Customers with multiple contacts (Say two contacts)- Enter two records in RA_CONTACT_PHONES_INTERFACE- With identical Orig_System_Customer_Ref- But different Orig_System_Contact_Ref
Customer Interface
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
TIPS
Customers with multiple telephone numbers (Say two telephones)- Enter two records in RA_CONTACT_PHONES_INTERFACE- With identical Orig_System_Customer_Ref- But different Orig_System_Contact_Ref
Addresses with multiple business purposes (Say two purposes)- Enter two records in RA_CUSTOMERS_INTERFACE- With identical Orig_System_Customer_Ref- With identical Orig_System_Address_Ref- But different Site_Use_Code
Customer Interface
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
Invoice Interface - Receivable
Using AutoInvoice (Executable - RAXMTR)
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
• Invoices
• Credit memos
• Debit memos
• On-Account credits
Invoice Interface - Receivable
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
Feeder System
Oracle Receivable Tables
Use an import programto load data intoInterface tables
Submit the AutoInvoice Interface Program
Over View
RA_INTERFACE_LINES RA_INTERFACE_DISTRIBUTIONS
RA_INTERFACE_SALESCREDITS
RA_Interface_Errors
Validations
- Accts code combination id
- Payment term
- Transaction type
- Line Code
- Batch source
- Salesperson
- Unit of measure
Invoice Interface - Receivable
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
Steps Required
- Running AutoInvoice program
- Reviewing the AutoInvoice execution & validation reports
- Correcting errors
- Rerunning AutoInvoice program
Invoice Interface - Receivable
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
Invoice Interface - Receivable
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
System Tables updated with AutoInvoice Program
- RA_BATCHES
- RA_CUSTOMER_TRX
- RA_CUSTOMER_TRX_LINES
- RA_CUST_TRX_LINE_GL_DIST
- RA_CUST_TRX_LINE_SALESREPS
- AR_PAYMENT_SCHEDULES
- AR_RECEIVABLE_APPLICATIONS
Using Autolockbox
- Submit ImportReads & formats the data from flat file into autolockbox tables
using SQL*Loader script.
- Submit ValidationValidates & transfers data into QuickCash tables.
- Submit Post Quick CashApplies the receipts & updates customer’s balances.
Note : These steps can be submitted individually or at the same time from the SubmitLockbox processing window.
Receipts
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
Bank file/ Data file
Ar_PaymentsInterface table
Import program Lockbox processing report
Ar_Interim_Cash_ReceiptsAr_Interim_Lines
Auto lockbox validation Lockbox processing report
AR Standard tables
Post batch Post batch execution report
Receipts
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
Over view
Receipts
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
Identifying Customers for a receipt
- Customer Number
- MICR Number-The customer number is not included in the transmission format-The MICR number is passed
- AutoAssociate-To determine the customer using matching number-Matching numbers like :
Transaction NumberSales order number
Receipts
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
To use AutoAssociate
- Check the AutoAssociate check box while defining the LockBox
- Matching numbers must be unique within the transmission
- All invoices to which any single receipt will be applied must belong to the same customer
Note : If Customer number or MICR number is not included in the transmissionand auto associate is set to ‘No’, LockBox imports the receipt as unidentified.You can do the application manually.
Receipts
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
System Tables to be updated after QuickCash post
- AR_CASH_RECEIPTS
- AR_RECEIVABLES_APPLICATIONS
- AR_CASH_RECEIPT_HISTORY
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
THANKS