open interfaces
TRANSCRIPT
![Page 1: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/1.jpg)
Oracle Apps Open Interfaces
![Page 2: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/2.jpg)
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
![Page 3: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/3.jpg)
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
![Page 4: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/4.jpg)
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
![Page 5: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/5.jpg)
Feeder program types
- SQL*Loader
- PL/SQL
- PRO*C
- PRO*COBOL
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
![Page 6: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/6.jpg)
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
![Page 7: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/7.jpg)
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
![Page 8: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/8.jpg)
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
![Page 9: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/9.jpg)
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
![Page 10: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/10.jpg)
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
![Page 11: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/11.jpg)
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
![Page 12: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/12.jpg)
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
![Page 13: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/13.jpg)
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
![Page 14: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/14.jpg)
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
![Page 15: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/15.jpg)
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
![Page 16: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/16.jpg)
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
![Page 17: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/17.jpg)
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
![Page 18: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/18.jpg)
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
![Page 19: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/19.jpg)
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
![Page 20: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/20.jpg)
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
![Page 21: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/21.jpg)
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
![Page 22: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/22.jpg)
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
![Page 23: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/23.jpg)
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
![Page 24: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/24.jpg)
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
![Page 25: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/25.jpg)
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
![Page 26: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/26.jpg)
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.
![Page 27: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/27.jpg)
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
![Page 28: Open Interfaces](https://reader031.vdocuments.us/reader031/viewer/2022012401/544ff063b1af9f19098b4899/html5/thumbnails/28.jpg)
Oracle Apps Open InterfacesI
N
T
E
R
F
A
C
E
THANKS