, , ,

ABOUT INTERFACES

In Oracle Apps Interfaces are generally tables, which act as a medium to transfer the data from one module to another module or to transfer the data from legacy system into Oracle Applications. There are 352 tables provided by the Oracle Package. Each module has its own Interface Tables.
A typical path to transfer the data from Legacy System to Oracle Apps:

What is Interfacing?

It is the process of converting the records from one format to another format. The main components of this interfacing are
• Transfer Program
• Interface Table and
• Import Program
A] Transfer Program:
If the source modules data are implemented in Oracle Applications then the Transfer Programs are integrated with the Package. If the source modules are implemented in external system (i.e. other than Oracle Applications) then we have to develop our own Transfer Programs. Generally these Transfer Programs are developed using PL/SQL, JAVA or SQL Loader.

What they do?

  • It maps the columns of source table with the columns of Interface Tables.
  • It performs Row Level and Column Level validations.
  • It transfers the data from Source to the Interface Table.

B] Interface Tables:
The Interface tables basically have 4 types of columns.

  1. Mandatory Columns.
  2. Conditionally Required Columns.
  3. Optional Columns.
  4. Internal Processing Columns.

Mandatory Columns:
These are the main columns which are required in the destination tables (i.e. Oracle Application Module Tables). With the help of mandatory columns only the Import Program will converts the records from source to destination.
Conditionally Required Columns:
The values for these columns are based on the values of Mandatory columns. For Example: If you are converting foreign currency transactions to INR then it as compulsory to provide conditionally required columns like Currency conversion rate, Conversion Time and Conversion Date.
Optional Columns:
These are used when a client wanted to transfer some additional information from source to destination. These are based on client’s requirement.
Internal Processing Columns:
Status and Error Message columns are called Internal Processing Columns. These are specific only to Interface Table. These columns are going to be used by the Import Program to update the status and error message, if the record fails its validation while importing from Interface Table to the Destination Table.
C] Import Program:
For all Interface Tables, Oracle Application Package is going to provide Import Programs. These are generally registered with destination modules. These Import Programs are designed using PL/SQL, JAVA, C, C++, etc.
What they do?

  • It maps the columns of the Interface Table with one or more columns in the destination table.
  • It performs row level and column level validation.
  • It imports the data from Interface Table to the Destination tables, if the records validated successfully.
  • It deletes all the successfully validated records from Interface Table.
  • If the record fails its validation then the Import Program will update the status and error message columns of Interface Table.

Interface Vs. Application Program Interface (API):

Interfaces are used to transfer the data from legacy system to Oracle Application system where as API is used to convert the data from one form to another form with in the Oracle Application Module.

/by
, ,

List of Receipts API in Oracle Receivables

List of Receipts API in Oracle Receivables
Below is the list of some of the Receipt API’s in Oracle Receivables. Receipt APIs provide an extension to existing functionality for creating and manipulating receipts through standard AR Receipts forms and lockboxes.
AR_RECEIPT_API_PUB is the main package that has several procedures to perform different actions.

1] AR_RECEIPT_API_PUB.CREATE_CASH
Use this procedure to create a single cash receipt for payment received in the form of a check or cash.
2] AR_RECEIPT_API_PUB.APPLY & AR_RECEIPT_API_PUB.APPLY_IN_DETAIL
Use these procedures to apply the cash receipts from a customer to an invoice, debit memo, or other debit item.
3] AR_RECEIPT_API_PUB.UNAPPLY
Use this procedure to unapply a cash receipt application against a specified installment of a debit item or payment schedule ID.
4] AR_RECEIPT_API_PUB.CREATE_AND_APPLY
Use this procedure  to create a cash receipt and apply it to a specified installment of a debit item.
5] AR_RECEIPT_API_PUB.REVERSE
Use this procedure to reverse cash and miscellaneous receipts.
6] AR_RECEIPT_API_PUB.APPLY_ON_ACCOUNT
Use this procedure to apply a cash receipt on account.
7] AR_RECEIPT_API_PUB.UNAPPLY_ON_ACCOUNT
Use this procedure to unapply an on-account application of a specified cash receipt.
8] AR_RECEIPT_API_PUB.ACTIVITY_APPLICATION
Use this procedure to create an activity application on a cash receipt, including Short Term  Debit (STD) and Receipt Write-off applications.
9] AR_RECEIPT_API_PUB. ACTIVITY_UNAPPLICATION
Use this procedure to create a reversal of an activity application on a cash receipt including Short Term Debt and Receipt write-off.
10] AR_RECEIPT_API_PUB.CREATE_MISC
Use this procedure to create a miscellaneous receipt.
11] AR_RECEIPT_API_PUB.APPLY_OPEN_RECEIPT
Use this procedure to apply a cash receipt to another open receipt. Open receipts include unapplied cash, on-account cash, and claim investigation applications.
12] AR_RECEIPT_API_PUB.UNAPPLY_OPEN_RECEIPT
Use this procedure to reverse a payment netting application on a  cash receipt.
/by
, , , , ,

Transaction/Journal Migration

The migration of transactions & journals from legacy to oracle is the most difficult part of data migration. You should migrate all the GL balance for the current year periods, all the last year periods and the ending balance of ‘Y-2’ to oracle. You need to do a proper reconciliation of GL balances after migration of open AP/AR invoices to the current period.
AP Open Invoice
The charge and tax amounts of the open AP invoices already exist in the legacy system. You need to make sure that you don’t account them again in oracle.
Accounting entries in Legacy
Charge A/C Dr
Tax  A/C Dr                            Supplier Liability A/C Cr
Instead of the liability account use a clearing account(Liability) while transferring the journals to oracle GL. Make the below changes while transferring these journals to oracle GL
Charge A/C Dr
Tax  A/C Dr                            Migration Clearing A/C (Liability) Cr
When you transfer the AP invoice from legacy to oracle. Use the above migration clearing account as charge/tax/freight A/C
Accounts created in oracle AP
Migration Clearing A/C (charge)
Migration Clearing A/C(tax)                       Supplier Liability A/C Cr
Transfer these accounts from AP to Oracle GL. After posting all these journals in the current period the YTD balance of migration clearing A/C should be zero.
AR Open Invoice
Similar to AP open invoice in AR invoice we use a clearing account (ASSET) instead of the receivable A/C in legacy. The revenue and tax amounts of the open AR invoices exist in the legacy system. You need to make sure that you don’t account them again in oracle.
Accounting entries in Legacy
Receivable A/C Dr
                                    Revenue A/C Cr                           
                                    Tax A/C Cr            
              
Make the below changes while transferring these journals to oracle GL
Migration Clearing A/C (Asset) Dr
                                                             Revenue A/C Cr                           
                                                              Tax A/C Cr           
When you transfer the AR invoice from legacy to oracle. Use the above migration clearing account as revenue/tax/freight A/C (use transaction type in auto accounting and put the clearing accounts in transaction type)
Accounts created in oracle AR
Receivable A/C Dr
                                    Migration Clearing A/C (Revenue) Cr                           
                                     Migration Clearing A/C (Tax) Cr            
Transfer these accounts from AR to Oracle GL. After posting all these journals in the current period the YTD balance of migration clearing A/C should be zero.
Fixed Assets
Transfer all the assets from legacy to oracle with Accumulated depreciation till the current period. Oracle ‘ll not create any depreciation for previous period. System ‘ll create below journal entries when you close & transfer the journals for the first asset period
Addition Journals
Addition Cost A/C Dr.
                                  Addition Cost Clearing A/C  Cr.
All these assets are present in legacy GL and transferred as a part of GL migration. You need to reverse all these journals in current period
Depreciation Journals
System‘ll create the depreciation journals for the current period.
All the past period depreciation & acc. Depreciation amounts are transferred through GL migration.
/by
, , , ,

Oracle Data & Migration

The sets of data required by Oracle Applications can be broadly classified in to 4 categories

  • Master Data : Item, Customer, Vendor, Bank Accounts, etc.
  • Opening Balances: On-Hand Quantity of Items, GL Balances, etc.
  • Open Transactions : Open Purchase Orders, Open Sales Order, Open Invoice etc.
  • Set-up Data :  Item Categories, Stock Locators

Similary source of data can be classified as

  • Existing Applications : DB2, Tally, SMS, etc.
  • Electronic Data : Comming through EDI in formats of Excel, Word, etc.
  • Hard Copy Data : In Registers
  • Non – Existent : Equipment Master, Collect and then migrate

The above diagram depicts the high level Data Migration approach from Legacy to ORACLE.
At a minimum, the data migration from Legacy to ORACLE consists of these tasks:

  • Extracting the data from Legacy data base.
  • Loading the extracted data into staging area.
  • Mapping and transformation of source data.
  • Migrating source data from staging area into target data base.
/by
, , , ,

Important APIs/Open Interfaces

Customer APIs
hz_party_v2pub.create_organization
Org Contact Role Hz_party_contact_v2pub.Create_Org_Contact_Role
Relationships HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE
Customer Profile HZ_CUSTOMER_PROFILE_V2PUB. create_customer_profile
Customer Profile Amount HZ_CUSTOMER_PROFILE_V2PUB. create_cust_profile_amt
Customer Credit Rating HZ_PARTY_INFO_V2PUB.create_credit_rating
Sales Person JTF_RS_SALESREPS_PUB.CREATE_SALESREP
Sales reps Territories JTF_RS_SRP_TERRITORIES_PUB.CREATE_RS_SRP_TERRITORIES
Customer contacts HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CUST_ACCOUNT_ROLE
Customer Contact Role HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility
Vendor APIs
Procedure : AP_PO_VENDORS_APIS_PKG.INSERT_NEW_VENDOR
Procedure=> AP_PO_VENDORS_APIS_PKG.INSERT_NEW_VENDOR_SITE
Procedure=> AP_PO_VENDORS_APIS_PKG.INSERT_NEW_VENDOR_CONTACT
Item Import
You must import items into the Item Master organization before you import items into additional organizations. You can accomplish this by specifying only your Item Master organization on a first pass run of the Item Interface. Once this has completed, you can run the Item Interface again, this time specifying an additional or all organizations.
Populate these tables first..
MTL_SYSTEM_ITEM_INTERFACE
MTL_ITEMS_REVISIONS_INTERFACE
MTL_ITEM_CATEGORIES_INTERFACE
Always set the TRANSACTION_TYPE column to CREATE, to create an item record (true when both importing a new item and assigning an already existing item to another organization). This is the only value currently supported by the Item Interface.Set the PROCESS_FLAG to 1 (Pending), so that the Item Interface can pick up the row and process it.The program sets the PROCESS_FLAG to 7 (Import succeeded) or  4 (Import failed) or  3 (validation failed). A row is inserted into the MTL_INTERFACE_ERRORS table for all failed rows.When the Item Interface imports an item, it also assigns the item to the mandatory category sets based on the item defining attributes. The default category for each category set is used. The Item Interface also allows you to assign items to other category sets and categories, when there is data for item category assignments in the MTL_ITEM_CATEGORIES_INTERFACE table.
Run Import Items Report :
Indicate whether to run the interface for all organizations in the item interface table. If you choose No, the interface runs only for the current organization and interface table rows for other organizations are ignored.Parameter Create or Update Items..
1 Create new items.
2 Update existing items.
/by