, , ,

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
, , ,

AP invoice interface

This interface helps us to import vendor invoices into Oracle applications from external systems into Oracle Applications.

Interface tables: 

1] AP_INVOICES_INTERFACE
This is the open interface table for importing AP Invoices from external sources and stores header information about invoices. Invoice data comes from sources including:

  • EDI invoices from suppliers that are loaded through Oracle e-Commerce Gateway
  • Supplier invoices that are transferred through the Oracle XML Gateway
  • Invoices that are loaded using Oracle SQL*Loader
  • Lease invoices from Oracle Property Manager
  • Lease payments from Oracle Assets
  • Credit card transaction data that are loaded using the Credit Card Invoice Interface Summary
  • Expense Report invoices from Oracle Internet Expenses
  • Payment Requests from Receivables
  • Invoices that are entered through the Invoice Gateway.

There is one row for each invoice you import. Oracle Payables application uses this information to create invoice header information when Payables Open Interface program is submitted.
Data in the AP_INVOICES_INTERFACE table used in conjunction with AP_INVOICE_LINES_INTERFACE table to create Payables Invoice, Invoice lines, Distributions and Schedule payments. Data in this table can be viewed and edited using ‘Open Interface Invoices’ window. The Payables Open Interface program validates each record in this interface table selected for import, and if the record contains valid data then the program creates a Payables Invoice.

Important columns: 
INVOICE_ID (Required) : Unique identifier for this invoice within this batch. Same value should be populated in invoice’s lines in the AP_INVOICE_LINES_INTERFACE table to identify the data as belonging to the same invoice.
INVOICE_NUM (Required) :  Enter the invoice number that needs to be assigned to the invoice created in Payables from this record.
INVOICE_TYPE_LOOKUP_CODE (Optional) : Type of invoice: Credit or Standard.
INVOICE DATE (Optional) : Date of the invoice. If you do not enter a value, the system uses the date you submit Payables Open Interface Import as the invoice date.
PO_NUMBER (Optional)  : Purchase order number from PO_HEADERS.SEGMENT1. This column needs to be populated if invoice to be matched with an purchase order.
VENDOR_ID & VENDOR_SITE_ID (Required) : VENDOR_ID is unique identifier for a supplier and VENDOR_SITE_ID is Internal supplier site identifier. Supplier of the invoice to be derived by value in one of the following columns in this table: VENDOR_ID, VENDOR_NUM, VENDOR_NAME, VENDOR_SITE_ID or PO_NUMBER.
VENDOR_NUM & VENDOR_NAME (Optional) : Supplier number and name. You must identify the supplier by entering a value for one of the following columns in this table: VENDOR_ID, VENDOR_NUM, VENDOR_SITE_ID, VENDOR_SITE CODE, or PO_NUMBER.
INVOICE_AMOUNT (Required) : Amount of the invoice.
INVOICE_CURRENCY_CODE (Optional)  : Currency code for the invoice. If you want to create foreign currency invoices, enter a currency code that is different from your functional currency.
EXCHANGE_RATE (Optional) :  This column is required if you enter a foreign currency code in the INVOICE_CURRENCY_CODE column and you enter User as the EXCHANGE_RATE_TYPE.
TERMS_ID (Optional) : Internal identifier for the payment terms.
DESCRIPTION (Optional) : Enter the description that you want to assign to the invoice created from this record.
SOURCE (Required) : Source of the invoice data. If you import EDI invoices from the Oracle EDI Gateway, the source is EDI Gateway. For invoices you import using SQL*Loader, use a QuickCode with the type Source that you have defined in the QuickCodes window in Payables.
2] AP_INVOICE_LINES_INTERFACE
This is the lines interface table for the AP Invoice Open Interface and it is used in conjunction with AP_INVOICE_INTERFACE table. AP_INVOICE_LINES_INTERFACE stores information used to create one or more invoice distributions. Note that one row in this table may create, during the import process, more than one invoice distribution.
Important columns: 
INVOICE_ID (Required) :Enter the INVOICE_ID of the corresponding invoice in the AP_INVOICES_INTERFACE table. 
INVOICE_LINE_ID : This value is not required. You can enter a unique number for each invoice line of an invoice.
LINE_NUMBER (Optional) : You can enter a unique number to identify the line.
LINE_TYPE_LOOKUP_CODE (Required) : Enter the lookup code for the type of invoice distribution that you want Payables Open Interface Import to create from this record. The code you enter must be ITEM, TAX, MISCELLANEOUS, or FREIGHT. These lookup codes are stored in the AP_LOOKUP_CODES table.
AMOUNT (Required) : The invoice distribution amount. If you are matching to a purchase order, the AMOUNT = QUANTITY_INVOICED x UNIT PRICE. If the total amount of all the invoice distributions does not equal the amount of the invoice that has the same INVOICE_ID, then Payables Open Interface Import will reject the invoice.

Concurrent program:  

Payables Open Interface Import
Parameters:
Source: Choose the source of the invoices from the list of values. Use EDI Gateway, Credit Card, or a Source type QuickCode you defined in the Payables QuickCodes window.
Group: To limit the import to invoices with a particular Group ID, enter the Group ID. The Group must exactly match the GROUP_ID in the Payables Open Interface tables.
Batch Name: Payables groups the invoices created from the invoices you import and creates an invoice batch with the batch name you enter. You can enter a batch name only if you have enabled the Use Batch Control Payables option, and if you have enabled the Use Batch Control Payables option, you must enter a batch name. If you use a batch name and some invoices are rejected during the import process, you can import the corrected invoices into the same batch if you enter the exact batch name during the subsequent import.
Hold Name: If you want to place all invoices on hold at the time of import, enter an Invoice Hold Reason. You can define your own hold reasons in the Invoice Approvals window.
Hold Reason: Payables displays the Invoice Hold Reason Description.
GL Date: If you want to assign a specific GL Date to all invoices, enter a GL Date. If you do not enter a value here, the system will assign a GL Date based on the GL Date Payables option.
Purge: Enter Yes if you want Payables to delete all successfully imported invoice records that match the Source and Group ID of this import. Payables does not delete any invoice data for which it has not yet created invoices. If you want to purge later, you can use the Payables Open Interface Purge Program.
 Steps:
1] Firstly, let’s get a unique number to be used as INVOICE_ID to the invoice to be imported.  This method ensures that each invoice has a unique INVOICE_ID assignment.
 select ap_invoices_interface_s.nextval from dual;
NEXTVAL
————-
132277

 2] Then, create records in the Invoice Open Interface tables through SQL queries.

insert into AP_INVOICES_INTERFACE (
            invoice_id,
            invoice_num,
            vendor_id,
            vendor_site_id,
            invoice_amount,
            INVOICE_CURRENCY_CODE,
            invoice_date,
            DESCRIPTION,
            PAY_GROUP_LOOKUP_CODE,
            source,
            org_id
                )
values (
            132277,
            ‘INV100’,
            ‘7124’,
            ‘11792’,
            1200.00,
            ‘USD’,
            to_date(’01-31-2010′,’mm-dd-yyyy’),
            ‘This Invoice is created for test purpose’,
            ‘WUFS SUPPLIER’,
            ‘Manual Invoice Entry’,
            81
);

insert into AP_INVOICE_LINES_INTERFACE (
            invoice_id,
            line_number,
            line_type_lookup_code,
            amount
            )
values     (
            132277,
            1,
            ‘ITEM’,
            1200.00
);
3] You can go to Payables > Invoices > Entry > Open Interface Invoices to check the details of Invoice and Invoice Lines from the front end. If required you can do any modifications here. Alternatively you can use these forms to put invoice data in AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE tables.
4] Go to the front end and run the concurrent program “Payables Open Interface Import”  to submit a request for Invoice Import.

Output:

output of Payables Open Interface Import

5] The imported invoice becomes available for review in Invoices Workbench.

Interface Invoice through Invoice Bench

/by
,

Interfaces in Oracle Application: An Introduction

What are Interfaces?

  • Interfaces are used in Oracle Applications to integrate external systems and Data Conversion.
  • The interfaces are mainly used to either transfer data from Oracle Applications to a flat file or data from legacy system to Oracle Applications.
  • Used extensively at the time of Data Conversion from legacy/ old systems to a fresh implementation of Oracle Applications.
  • Used also at regular intervals when data transfer is from other live systems if the systems are not defined in Oracle Applications implementation.
  • Oracle provides flexible and flexible tools in the form of Interface programs to import the master and transactional data like Customers, Invoices, and Sales Orders etc from external systems into Oracle Applications.

Types of Interfaces

There are two major types of Interfaces:

  • Inbound Interface : These interfaces are used to transfer data from external systems to Oracle Applications.
  • Outbound Interface :  These interfaces are used to transfer data from Oracle Applications to external systems.

Two other distinctions of Interfaces:

  • Open Interface: If the interface logic is provided by Oracle Applications, it is called an Open Interface.
  • Custom Interface: If the interface logic needs to be developed by the implementation team, it is called a Custom Interface.

Interface Components

Open Interface Logic

  • First the data from the source application is loaded into a database table (called Interface table).
  • Then the provided validation program logic validates the records whether they are correct or not .
  • If the validation fails, the errors are transferred into another table (called Error Table).
  • If the validation succeeds, the correct records are transferred through a process into the destination application table.

Components of an Interface

a] Source Application:
You obtain data from a source application to pass on to a destination application for further processing and/or storage.
b] Source Data Issues:
Type of file, Size, Frequency of upload, Record Length (Variable or fixed), Delimiter, Datatype for each field, Any unwanted data, Naming convention and uniqueness of file, Location of the file, Access on the file.
c] Destination Application:
You send data to a destination application so that the application can perform further processing and/or storage.
d] Interface Table:
For inbound interfaces, the interface table is the intermediary table where the data from your source application temporarily resides until it is validated and processed into the destination application.
e] Identifier columns:
Uniquely identify rows in the interface table provide foreign key reference to both the source and destination applications.
f] Control Columns:

  • Control columns track the status of each row in the interface table, as it is inserted, validated, rejected, processed, and ultimately deleted.
  • WHO columns are also control columns.

g] Data Columns:

  • Stores the data that is being converted.
  • Required columns store the minimum information needed by the destination application to successfully process the interface row.

h] Derived Columns:
Derived columns are created by the destination application from information in the required columns.
i] Optional Columns:
Optional columns are not necessarily required by the destination application, but can be used by the destination application for additional value-added functionality beyond the basics.
j] Error Table:

  • For inbound interfaces, the errors table stores all errors found by the validation and processing functions.
  • In some cases, the errors table is a child of the interface table. This allows each row in the interface table to have many errors, so that you can easily manage multiple errors at once.
  • In other cases, the errors are stored in a column within the interface table, which requires you to fix each error independently.

Developing an Interface

1] Identification:
Find out if there exists an Open Interface to carry out the functionality.
2] Creation of Pre-Interface table ( staging Table):
A table in the format of the data file which can be pruned to load as clean a data into the Interface table.
3] Load data into Pre-Interface table:
SQL*LOADER can be used to load the flat file into the pre-interface table.
4] Validate data in the Pre-Interface table:
Basic validation of the data loaded into the Pre-Interface table can be carried out like:

  • For checking NULL values in required columns
  • Checking for Foreign Key and Quick Code values.
  • Duplication Validation
  • Business Rule validation

5] Mapping the values:
Generated fields in Oracle Applications can be mapped in this step to either default values or sequences.
6] Load data into Interface table:

  • Once the data is as clean as you can get it, the data can be inserted into the Interface table.
  • At such a time, certain columns, which are necessary in Applications but not found in legacy system, need to be populated accordingly like WHO columns.

7] Run the interface program
8] Check for Errors
9] Report on the Interface

/by
, , , , , , ,

Oracle APIs and Open Interfaces

Oracle Puchasing:
1. Requisitions Open Interface
2. Purchasing Documents Open Interface
3. Cancel PO APIs
4. Receiving Open Interface
Oracle Inventory:
1. Open Transaction Interface
2.1 Customer Item Interface
2.2 Open Item Interface
2.3 Cycle Count Open Interface
3.1 Open Replenishment Interface
3.2 Reservations Open Interface
3.3 Move Orders Open Interface
OM:
1.    Order Import
2.    Process Order API
3.    RLM Open Interfaces
Actions, APIs, and Parameters: Descriptions of the APIs used for various functions and the API parameters.
Application Parameter Initialization: Description of the application parameter initialization call.
Trip API: Create and update trip records and perform actions on trips.
Stop API: Create and update stop records and perform actions on stops.
Deliveries API: Create and update trip stop records and perform actions on trip stops.
Delivery Details API: Assign and unassign delivery details to and from deliveries, split a delivery detail, update a delivery detail with new
information, and create trips and deliveries for multiple delivery lines.
Container API: Create container records, update container records, autopack containers, perform actions on containers.
Freight Cost APIs: Create freight cost records, update freight cost records, validate freight cost types, delete freight cost records.
Tables
OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL
WSH_DELIVERY_DETAILS
OE_ORDER_HOLDS_ALL
OE_PRICE_ADJUSTMENTS
OE_TRANSACTION_TYPES_ALL
OE_DROP_SHIP_SOURCES
OE_SETS
OE_SYSTEM_PARAMETSR
MTL_DEMANDS
MTL_RESRVATIONS
Inventory
Open Transaction Interface
Oracle Inventory provides an open interface for you to load transactions from external applications and feeder systems. These transactions could include sales order shipment transactions from an Order Management system other than Oracle Order Management, or they could be simple material issues, receipts, or transfers loaded from data collection devices. The following transaction types are supported by this interface:
• Inventory issues and receipts (including user-defined transaction types)
• Subinventory transfers
• Direct interorganization transfers
• Intransit shipments
• WIP component issues and returns
• WIP assembly completions and returns
• Sales order shipments
• Inventory average cost updates
• LPN Pack
• Unpack
• Split Transactions
• Inventory Lot Split/ Merge/ Translate Transactions
This interface is also used as an integration point with Oracle Order Management for shipment transactions. Oracle Order Management’s Inventory Interface program populates the interface tables with transactions submitted through the Confirm
Shipments window.
You must write the load program that inserts a single row for each transaction into the MTL_TRANSACTIONS_INTERFACE table. For material movement of items that are under lot or serial control, you must also insert rows into MTL_TRANSACTION_LOTS_INTERFACE and MTL_SERIAL_NUMBERS_INTERFACE respectively. If you insert WIP
assembly/completion transactions that complete or return job assemblies, you must also insert rows into the CST_COMP_SNAP_ INTERFACE table if the organization referenced uses average costing. The system uses this information to calculate completion cost.
There are two modes you can use to process your transactions through the interface. In the first processing mode, you populate the interface table only. Then the Transaction Manager polls the interface table asynchronously looking for transactions to process, groups the transaction rows, and launches a Transaction Worker to process each group.
In the second processing mode, you insert the rows in the interface table and call a Transaction Worker directly, passing the group identifier of the interfaced transactions as a parameter so that the worker can recognize which subset of transactions to process.
The Transaction Worker calls the Transaction Validator, which validates the row, updates the error code and explanation if a validation or processing error occurs, and derives or defaults any additional columns. Next, the Transaction Processor records the transaction details in the transaction history table along with relevant current cost information. All material movement transactions update inventory perpetual balances for the issue, receipt, or transfer locations. Once the transaction has been successfully processed, the corresponding row is deleted from the interface table. Finally, the transaction is costed by the transaction cost processor, which runs periodically, picking up all transactions from the history table that have not yet been marked as costed.
Open Replenishment Interface
Oracle Inventory provides an open interface for you to easily load replenishment requests from external systems such as a barcode application. Such requests may be in the form of stock-take counts or requisition requests for subinventories in which you do not track quantities.
Cycle Count Entries Interface
You can import cycle count entries from an external system into Oracle Inventory using the Cycle Count Entries Interface. This interface validates all data that you import into Oracle Inventory. It also performs foreign key validation and checks for attribute inter-dependencies, acceptable values, and value ranges. The interface ensures that the imported cycle count entries contain the same detail as items entered manually using the Cycle Count Entries window. Errors detected during validation are written to the Cycle Count Interface Errors table.
Kanban Application Program Interface
The Kanban API is a public API that allows you to update the supply status of kanban cards. To accomplish this task, you use the public procedure update_card_supply_status
Item Open Interface
You can import items from any source into Oracle Inventory and Oracle Engineering using the Item Open Interface. With this interface, you can convert inventory items from another inventory system, migrate assembly and component items from a legacy manufacturing system, convert purchased items from a custom purchasing system, and import new items from a product data management package. The Item Open Interface validates your data, ensuring that your imported items contain the same item detail as items that you enter manually in the Master Item window.
You can also import item category assignments. This can be done simultaneously with a process of importing items, or as a separate task of importing item category assignments only. For this purpose, the Inventory menu contains the Import submenu with the Import Items and Import Item Category Assignments menu entries.
Receiving Open Interface
You use the Receiving Open Interface to process and validate receipt data that comes from sources other than the Receipts window in Oracle Purchasing. These sources include:
• Receipt information from other Oracle applications or legacy systems
• Brocades and other receiving information from scanners and radio frequency devices
• Advance Shipment Notices (ASNs) from suppliers
The Receiving Open Interface maintains the integrity of the new data as well as the receipt data that resides in Oracle Purchasing.
The Receiving Open Interface does not support:
• Movement statistics
• Dynamic locators

BOM
Bills of Materials Open Interfaces
WIP
Open Move Transaction Interface
You can load Move transaction information into the Open Move Transaction Interface from a variety of sources, including external data collection devices such as bar code readers, automated test equipment, cell controllers, and other manufacturing execution systems. You then use the interface to load these transactions into Oracle Work in Process. All transactions are validated and invalid transactions are marked, so that you can correct and resubmit them.
Open Resource Transaction Interface
You can use external data collection devices such as bar code readers, payroll systems, and time card entry forms to collect resource and overhead transaction data, then load the data into the Open Resource Transaction Interface for Oracle Work in Process to process.
Work Order Interface
The Work Order Interface enables you to import Discrete job and Repetitive schedule header information, and Discrete job operations, material, resource, and scheduling information from any source, using a single process.
You can import:
• Planned orders for new Discrete jobs,
• Discrete job operations, components, resources, resource usage, and scheduling details
• Update and reschedule recommendations for existing Discrete jobs
• Suggested Repetitive schedules Work in Process then uses this information to automatically create new Discrete jobs
and pending Repetitive Schedules, or to update existing Discrete jobs.
MRP
Open Forecast Interface
You can import forecasts from any source using the Open Forecast Interface table. Oracle Master Scheduling/MRP automatically validates and implements imported forecasts as new forecasts in Oracle Master Scheduling/MRP.
Cost Management
Periodic Cost Open Interface
The Oracle Periodic Cost Open Interface provides an open interface for you to easily load periodic item costs from external applications or legacy systems and migrate them into the Oracle Cost Management Application. This interface should only be used to bring in periodic costs for the first opened periodic period. It cannot be used for subsequent periods. Costs in subsequent periods are calculated by the system.
Cost Import Interface
The Oracle Cost Import Interface enables you to import costs for items from legacy systems, and import new cost information for existing items. Importing resource costs and resource overhead rates is also supported. You will also be able to replace existing cost information with the new cost information. However, updating of existing costs is not supported. Importing costs into frozen cost type is also not supported. The item costs will have to be imported into another cost type and then the cost update may be run to update the frozen cost type
OM
Order Import
Pricing Open interface
Pick release
/by