1.

Requisition import

You can automatically import requisitions into Oracle Applications using

the Requisitions Open Interface

Pre-requisites:

            Set of Books

            Code combinations

            Employees

            Items

            Define a Requisition Import Group-By method in the Options

window.

            Associate a customer with your deliver-to location using the

Customer Addresses window for internally sourced requisitions.

            Interface tables:         

                     PO_REQUISITIONS_INTERFACE_ALL

                     PO_REQ_DIST_INTERFACE_ALL                      

            Base tables:                

PO_REQUISITIONS_HEADERS_ALL                                                    
           

PO_REQUISITION_LINES_ALL              

PO_REQ_DISTRIBUTIONS_ALL

Concurrent program: 

REQUISITION IMPORT

            Validations:    

                    Check for interface transaction source code, requisition

destination type.

                    Check for quantity ordered, authorization status type.

Some important columns that need to be populated in the interface tables:

PO_REQUISITIONS_INTERFACE_ALL :

INTERFACE_SOURCE_CODE (to identify the source of your Requisitions)

DESTINATION_TYPE_CODE

AUTHORIZATION_STATUS

PREPARER_ID or PREPARER_NAME

QUANTITY

CHARGE_ACCOUNT_ID or charge account segment values

DESTINATION_ORGANIZATION_ID or
DESTINATION_ORGANIZATION_CODE

DELIVER_TO_LOCATION_ID or DELIVER_TO_LOCATION_CODE

DELIVER_TO_REQUESTOR_ID or DELIVER_TO_REQUESTOR_NAME

ORG_ID

ITEM_ID or item segment values (values if the SOURCE_TYPE_CODE or

DESTINATION_TYPE_CODE is 'INVENTORY')

PO_REQ_DIST_INTERFACE_ALL :

CHARGE_ACCOUNT_ID or charge account segment values

DISTRIBUTION_NUMBER

DESTINATION_ORGANIZATION_ID

DESTINATION_TYPE_CODE

INTERFACE_SOURCE_CODE

ORG_ID

DIST_SEQUENCE_ID (if MULTI_DISTRIBUTIONS is set to Y)

2.  Purchase Order conversion:

The Purchasing Document Open Interface concurrent program was
replaced by two new concurrent programs Import Price Catalogs and
Import Standard Purchase Orders. Import Price Catalogs concurrent
program is used to import Catalog Quotations, Standard Quotations, and
Blanket Purchase Agreements. Import Standard Purchase Orders
concurrent program is used to import Unapproved or Approved Standard
Purchase Orders.

Import Standard Purchase Orders

Pre-requisites:

Suppliers, sites and contacts

Buyers

Line Types

Items

PO

Charge account setup

Interface Tables:

PO_HEADERS_INTERFACE

PO_LINES_INTERFACE

PO_DISTRIBUTIONS_INTERFACE

PO_INTERFACE_ERRORS (Fallouts)

Interface Program:

Import Standard Purchase Orders.

Base Tables:

PO_HEADERS_ALL

PO_LINES_ALL

PO_DISTRIBUTIONS_ALL
PO_LINE_LOCATIONS_ALL

Validations:

Header:

Check if OU name is valid

Check if Supplier is valid

Check if Supplier site is valid

Check if buyer is valid

Check if Payment term is valid

Check if Bill to and ship to are valid

Check if FOB, freight terms are valid

Lines :

Check if Line_type, ship_to_org, item, uom, ship_to_location_id,

requestor, charge_account, deliver_to_location are valid

General:

Check for duplicate records in interface tables

Check if the record already exists in base tables.

Some important columns that need to be populated in the interface tables:

PO_HEADERS_INTERFACE :

INTERFACE_HEADER_ID (PO_HEADERS_INTERFACE_S.NEXTVAL),
BATCH_ID, ORG_ID, INTERFACE_SOURCE_CODE, ACTION
(‘ORIGINAL’,’UPDATE’,’REPLACE’), GROUP_CODE,
DOCUMENT_TYPE_CODE, PO_HEADER_ID (NULL), RELEASE_ID,
RELEASE_NUM, CURRENCY_CODE, RATE, AGENT_NAME,
VENDOR_ID, VENDOR_SITE_ID, SHIP_TO_LOCATION,
BILL_TO_LOCATION, , PAYMENT_TERMS

PO_LINES_INTERFACE :
INTERFACE_LINE_ID, INTERFACE_HEADER_ID, LINE_NUM,
SHIPMENT_NUM, ITEM, REQUISITION_LINE_ID, UOM, UNIT_PRICE,
FREIGHT_TERMS, FOB

PO_DISTRIBUTIONS_INTERFACE :

INTERFACE_LINE_ID, INTERFACE_HEADER_ID,
INTERFACE_DISTRIBUTION_ID, DISTRIBUTION_NUM,
QUANTITY_ORDERED, QTY_DELIVERED, QTY_BILLED,
QTY_CANCELLED, DELIVER_TO_LOCATION_ID,
DELIVER_TO_PERSON_ID, SET_OF_BOOKS, CHARGE_ACCT,
AMOUNT_BILLED.

Import Blanket Purchase Agreements:

Interface Tables:

PO_HEADERS_INTERFACE

PO_LINES_INTERFACE

Interface program:

Import Price Catalogs

Base tables:

PO_HEADERS_ALL

PO_LINES_ALL

PO_LINE_LOCATIONS_ALL

Example:

Suppose you want to create a blanket with one line and two price breaks
and the details for the price break are as below:

1) Quantity = 500, price = 10, effective date from '01-JAN-2006' to

    '31-JUN-2006'

2) Quantity = 500, price = 11, effective date from '01-JUL-2006' to

    '01-JAN-2007'

To create the above the BPA, you would create ONE record in
PO_HEADERS_INTERFACE and THREE records in
PO_LINES_INTERFACE

LINE1: It will have only the line information. LINE NUM would be 1.

LINE2: For the first Price Break details, LINE NUM will be the same as
above i.e. 1. SHIPMENT_NUM would be 1 and SHIPMENT_TYPE would
be ‘PRICE BREAK’

LINE3: For the second Price Break details, LINE NUM will be the same as
above i.e. 1. SHIPMENT_NUM would be 2 and SHIPMENT_TYPE would
be ‘PRICE BREAK’

All the line-level records above must have the same

INTERFACE_HEADER_ID.

For detailed explanation refer to the below article:

http://www.erpschools.com/Apps/oracle-
applications/articles/financials/Purchasing/Import-Blanket-Purchase-
Agreements/index.aspx

3. Vendor conversion/interface

This interface is used to import suppliers, supplier sites and site contacts
into Oracle applications.

Pre-requisites setup’s required:

Payment terms

Pay Groups

CCID

Supplier classifications

Bank Accounts
Employees (if employees have to set up as vendors)

Interface tables:

AP_SUPPLIERS_INT

AP_SUPPLIER_SITES_INT

AP_SUP_SITE_CONTACT_INT

Base Tables:

PO_VENDORS

PO_VENDOR_SITES_ALL

PO_VENDOR_CONTACTS

Interface programs:

Supplier Open Interface Import

Supplier Sites Open Interface Import

Supplier Site Contacts Open Interface Import

Validations:

Check if vendor already exists

Check if vendor site already exists

Check if site contact already exists

Check if term is defined.

Some important columns that need to be populated in the interface tables:

AP_SUPPLIERS_INT :

VENDOR_NUMBER, VENDOR_NAME, VENDOR_TYPE,

STATE_REPORTABLE, FED_REPORTABLE, NUM_1099, TYPE_1099,
PAY_GROUP_LOOKUP_CODE, VENDOR_ID is auto generated.

AP_SUPPLIER_SITES_INT:

VENDOR_SITE_ID, ORG_ID, VENDOR_SITE_CODE, INACTIVE_DATE,

PAY_SITE, PURCHASING_SITE, SITE_PAYMENT_TERM, ADDRESS1,
ADDRESS2.ADDRESS3, CITY, STATE, COUNTRY, ZIP, PH_NUM,
FAX_NUMBER, TAX_REPORTING_SITE_FLAG.

AP_SUP_SITE_CONTACTS_INT:

VENDOR_ID, VENDOR_SITE_ID, FIRST_NAME, LAST_NAME,

AREA_CODE, PHONE, EMAIL, ORG_ID

4. AP invoice interface

This interface helps us to import vendor invoices into Oracle applications

from external systems into Oracle Applications.

Pre-requisites:

            Set of Books

            Code combinations

            Employees

            Lookups

Interface tables:         

AP_INVOICES_INTERFACE        

AP_INVOICE_LINES_INTERFACE

            Base tables:                

                     AP_INVOICES_ALL – header information

                     AP_INVOICE_DISTRIBUTIONS_ALL – lines info

Concurrent program:  

Payables Open Interface Import

            Validations:    

                    Check for valid vendor

                    Check for Source, Location, org_id, currency_code’s validity

                    Check for valid vendor site code.

                    Check if record already exists in payables interface table.     

            Some important columns that need to be populated in the interface

tables:

            AP_INVOICES_INTERFACE:

                        INVOICE_ID

                        INVOICE_NUM

                        INVOICE_DATE

                        VENDOR_NUM

                        VENDOR_SITE_ID

                        INVOICE_AMOUNT

                        INVOICE_CURRENCY_CODE

                        EXCHANGE_RATE

                        EXCHANGE_RATE_TYPE

                        EXCHANGE_DATE

                        DESCRIPTION

                        SOURCE

                        PO_NUMBER

                        PAYMENT_METHOD_LOOKUP_CODE

                        PAY_GROUP_LOOKUP_CODE

                        ATTRIBUTE1 TO 15

                        ORG_ID

            AP_INVOICE_LINES_INTERFACE:

                        INVOICE_ID 

                        INVOICE_LINE_ID
                        LINE_TYPE_LOOKUP_CODE

                        AMOUNT

                        DESCRIPTION

                        TAX_CODE

                        PO_NUMBER

                        PO_LINE_NUMBER

                        PO_SHIPMENT_NUM

                        PO_DISTRIBUTION_NUM

                        PO_UNIT_OF_MEASURE

                        QUANTITY_INVOICED

                        DIST_CODE_CONCATENATED

                        DIST_CODE_COMBINATION_ID

                        ATTRIBUTE1

                        ATTRIBUTE2

                        ATTRIBUTE3

                        ATTRIBUTE4

                        ATTRIBUTE5

                        ORG_ID        

5. Item import (Item conversion)

    The Item Interface lets you import items into Oracle Inventory. 

     Pre-requisites:

Creating an Organization

Code Combinations

Templates
Defining Item Status Codes

Defining Item Types

            Interface tables:         
                     MTL_SYSTEM_ITEMS_INTERFACE

                     MTL_ITEM_REVISIONS_INTERFACE (If importing

revisions)

                     MTL_ITEM_CATEGORIES_INTERFACE (If importing

categories)

                     MTL_INTERFACE_ERRORS (View errors after import)

            Concurrent Program:

                     Item import

In the item import parameters form, for the parameter 'set process id',
specify the 'set process id' value given in
the mtl_item_categories_interface table. The parameter 'Create or Update'
can have any value. Through the import process, we can only create item
category assignment(s). Updating or Deletion of item category assignment
is not supported.

Base Tables:                

MTL_SYSTEM_ITEMS_B              

MTL_ITEM_REVISIONS_B

MTL_CATEGORIES_B

MTL_CATEGORY_SETS_B

MTL_ITEM_STATUS          

MTL_ITEM_TEMPLATES

Validations:    

           Check for valid item type.

           Check for valid part_id/segment of the source table.

           Validate part_id/segment1 for master org.

           Validate and translate template id of the source table.

           Check for valid template id. (Attributes are already set for items,
default attributes for that template, i.e., purchasable, stockable, etc )

           Check for valid item status.

           Validate primary uom of the source table.

           Validate attribute values.

           Validate other UOMs of the source table.

           Check for unique item type. Discard the item, if part has non-
unique item type.

           Check for description, inv_um uniqueness

           Validate organization id.

           Load master records and category records only if all

           Load child record if no error found.

Some important columns that need to populated in the interface tables:

MTL_SYSTEM_ITEMS_INTERFACE:

PROCESS_FLAG = 1 (1= Pending, 2= Assign Complete, 3=

Assign/Validation Failed, 4= Validation succeeded; Import failed, 5 =
Import in Process, 7 = Import succeeded)

TRANSACTION_TYPE = ‘CREATE’, ‘UPDATE’

SET_PROCESS_ID = 1

ORGANIZATION_ID

DESCRIPTION

ITEM_NUMBER and/or SEGMENT (n)

MATERIAL_COST

REVISION
TEMPLATE_ID

SUMMARY_FLAG

ENABLED_FLAG

PURCHASING_ITEM_FLAG

SALES_ACCOUNT (defaulted from

MTL_PARAMETERS.SALES_ACCOUNT)

COST_OF_SALES_ACCOUNT (defaulted from

MTL_PARAMETERS.COST_OF_SALES_ACCOUNT)

MTL_ITEM_CATEGORIES_INTERFACE:

INVENTORY_ITEM_ID or ITEM_NUMBER.

ORGANIZATION_ID or ORGANIZATION_CODE or both.

TRANSACTION_TYPE = 'CREATE' ('UPDATE' or 'DELETE' is not

possible through Item Import).

CATEGORY_SET_ID or CATEGORY_SET_NAME or both.

CATEGORY_ID or CATEGORY_NAME or both.

PROCESS_FLAG = 1

SET_PROCESS_ID (The item and category interface records should have

the same set_process_id, if you are importing item and category
assignment together)

MTL_ITEM_REVISIONS_INTERFACE:

INVENTORY_ITEM_ID or ITEM_NUMBER (Must match the

ORGANIZATION_ID or ORGANIZATION_CODE or both

REVISION

CHANGE_NOTICE

ECN_INITIATION_DATE

IMPLEMENTATION_DATE
IMPLEMENTED_SERIAL_NUMBER

EFFECTIVITY_DATE

ATTRIBUTE_CATEGORY

ATTRIBUTEn

REVISED_ITEM_SEQUENCE_ID

DESCRIPTION

PROCESS_FLAG = 1

TRANSACTION_TYPE = 'CREATE'

SET_PROCESS_ID = 1

Each row in the mtl_item_revisions_interface table must have the

REVISION and EFFECTIVITY_DATE in alphabetical (ASCII sort) and
chronological order.

6. Order Import Interface (Sales Order Conversion)

Order Import enables you to import Sales Orders into Oracle Applications
instead of manually entering them.

             Pre-requisites:

Order Type
Line Type
Items
Customers
Ship Method/ Freight Carrier
Sales Person
Sales Territories
Customer Order Holds
Sub Inventory/ Locations
On hand Quantity
            Interface tables:         

                    OE_HEADERS_IFACE_ALL
                    OE_LINES_IFACE_ALL
                    OE_ACTIONS_IFACE_ALL
                    OE_ORDER_CUST_IFACE_ALL
                    OE_PRICE_ADJS_IFACE_ALL
                    OE_PRICE_ATTS_IFACE_ALL

            Base tables:    

OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL
Pricing tables: QP_PRICING_ATTRIBUTES 

            Concurrent Program:

Order Import

             Validations:

                    Check for sold_to_org_id. If does not exist, create new

customer by calling create_new_cust_info API.

                    Check for sales_rep_id. Should exist for a booked order.

                    Ordered_date should exist (header level)

                    Delivery_lead_time should exist (line level)

                    Earliest_acceptable_date should exist.

                    Freight_terms should exist

             Notes: 

During import of orders, shipping tables are not populated.

If importing customers together with the order,

OE_ORDER_CUST_IFACE_ALL has to be populated and the base tables
are HZ_PARTIES, HZ_LOCATIONS.

Orders can be categorized based on their status:

                                    1. Entered orders               2. Booked
orders                   3. Closed orders

Order Import API     OE_ORDER_PUB.GET_ORDER and

PROCESS_ORDER can also be used to import orders.

            Some important columns that need to populated in the interface

tables:

           OE_HEADERS_IFACE_ALL:                     

ORIG_SYS_DOCUMENT_REF

ORDER_SOURCE

CONVERSION_RATE

ORG_ID

ORDER_TYPE_ID

PRICE_LIST

SOLD_FROM_ORG_ID

SOLD_TO_ORG_ID

SHIP_TO_ORG_ID

SHIP_FROM_ORG_ID

CUSTOMER_NAME

INVOICE_TO_ORG_ID

OPERATION_CODE

OE_LINES_IFACE_ALL

ORDER_SOURCE_ID

ORIG_SYS_DOCUMENT_REF

ORIG_SYS_LINE_REF

ORIG_SYS_SHIPMENT_REF

INVENTORY_ITEM_ID
LINK_TO_LINE_REF

REQUEST_DATE

DELIVERY_LEAD_TIME

DELIVERY_ID

ORDERED_QUANTITY

ORDER_QUANTITY_UOM

SHIPPING_QUANTITY

PRICING_QUANTITY

PRICING_QUANTITY_UOM

SOLD_FROM_ORG_ID

SOLD_TO_ORG_ID

INVOICE_TO_ ORG_ID

SHIP_TO_ORG_ID

PRICE_LIST_ID

PAYMENT_TERM_ID

7. Inventory On-hand quantity Interface

This interface lets you import the on hand inventory into Oracle.

            Interface tables:

MTL_TRANSACTIONS_INTERFACE

MTL_MTL_TRANSACTION_LOTS_INTERFACE (If the item is Lot

Controlled)

MTLL_SERIAL_NUMBERS_INTERFACE (If the item is Serial Controlled)

            Concurrent Program:

Launch the Transaction Manager through Interface Manager or explicitly
call the API – INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS ()
to launch a dedicated transaction worker to process them.

The Transaction Manager picks up the rows to process based on the

LOCK_FLAG, TRANSACTION_MODE, and PROCESS_FLAG. Only
records with TRANSACTION_MODE of 3, LOCK_FLAG of '2', and
PROCESS_FLAG of '1' will be picked up by the Transaction Manager and
assigned to a Transaction Worker. If a record fails to process completely,
then PROCESS_FLAG will be set to '3' and ERROR_CODE and
ERROR_EXPLANATION will be populated with the cause for the error.

Base Tables:

MTL_ON_HAND_QUANTITIES

MTL_LOT_NUMBERS

MTL_SERIAL_NUMBERS

Validations:

Validate organization_id

Check if item is assigned to organization

Validate disposition_id

Check if the item for the org is lot controlled before inserting into the Lots
interface table.

Check if the item for the org is serial controlled before inserting into Serial
interface table.

Check if inventory already exists for that item in that org and for a lot.

Validate organization_id, organization_code.

Validate inventory item id.

Transaction period must be open.

Some important columns that need to be populated in the interface tables:

MTL_TRANSACTIONS_INTERFACE:

TRANSACTION_SOURCE_NAME (ANY USER DEFINED VALUE),

TRANSACTION_HEADER_ID
(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL)

TRANSACTION_INTERFACE_ID
(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL – If item is lot or serial
controlled, use this field to link to mtl_transactions_interface otherwise
leave it as NULL),

TRANSACTION_DATE,

TRANSACTION_TYPE_ID,

PROCESS_FLAG (1 = Yet to be processed, 2 = Processed, 3= Error)

TRANSACTION_MODE (2 = Concurrent – to launch a dedicated

transaction worker to explicitly process a set of transactions, 3 =
Background – will be picked up by transaction manager polling process
and assigned to transaction worker. These will not be picked up until the
transaction manager is running)

SOURCE_CODE,

SOURCE_HEADER_ID,

SOURCE_LINE_ID (Details about the source like Order Entry etc for
tracking purposes)

TRANSACTION_SOURCE_ID

Source Foreign Key Reference

Type

Account GL_CODE_COMBINATIONS.CODE_COMBINATION_ID

Account MTL_GENERIC_DISPOSITIONS.DISPOSITION_ID
Alias
 Job or WIP_ENTITIES.WIP_ENTITY_ID
schedule

Sales MTL_SALES_ORDERS.SALES_ORDER_ID
Order

ITEM_SEGMENT1 TO 20,

TRANSACTION_QTY,

TRANSACTION_UOM,

SUBINVENTORY_CODE,

ORGANIZATION_ID,

LOC_SEGMENT1 TO 20.

MTL_TRANSACTION_LOTS_INTERFACE:

TRANSACTION_INTERFACE_ID,

LOT_NUMBER,

 LOT_EXPIRATION_DATE,

TRANSACTION_QUANTITY,

SERIAL_TRANSACTION_TEMP_ID (This is required for items under both

lot and serial control to identify child records in
mtl_serial_numbers_interface)

MTL_SERIAL_NUMBERS_INTERFACE :

TRANSACTION_INTERFACE_ID,

FM_SERIAL_NUMBER,

TO_SERIAL_NUMBER,

VENDOR_SERIAL_NUMBER
8. GL daily conversion rates

This interface lets you load the rates automatically into General Ledger.
Pre-requisites:

            Currencies

            Conversion rate Types

            Interface tables:         

GL_DAILY_RATES_INTERFACE

            Base tables:                

GL_DAILY_RATES            

GL_DAILY_CONVERSION_TYPES

            Concurrent Program:

You do not need to run any import programs. The insert, update, or
deletion of rates in GL_DAILY_RATES is done automatically by database
triggers on the GL_DAILY_RATES_INTERFACE. All that is required is to
develop program to populate the interface table with daily rates
information.

            Validations:

Check if FROM_CURRENCY and TO_CURRENCY are valid.

Check if USER_CONVERSION_TYPE is valid.

Some important columns that need to be populated in the interface tables:

GL_DAILY_RATES_INTERFACE :

FROM_CURRENCY

TO_CURRENCY

FROM_CONVERSION_DATE

TO_CONVERSION_DATE

USER_CONVERSION_TYPE

CONVERSION_RATE

MODE_FLAG (D= Delete, I = Insert, U = Update)

INVERSE_CONVERSION_RATE