Oracle Apps Order Import Interface training with examples

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. You can even do using Order import (Refer blog post)

·         Check for sales_rep_id. It 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.

Order Management provides the capability to add a new customer, the address and contacts using the Order Import feature. The table OE_CUSTOMER_INFO_IFACE_ALL needs to be populated for this to occur. Based on the data available in the OE_HEADERS_IFACE_ALL the data from the table OE_CUSTOMER_INFO_IFACE_ALL will be processed to add a new customer. This table can also be used to import a new address only for contact information of an existing Customer. Refer the blog post

Orders can be categorized based on their status:

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

There are two ways to indicate that you want the order to be booked. You can load the actions interface table OE_ACTIONS_IFACE_ALL with a value of BOOK_ORDER in the OPERATION_CODE column to import orders in a booked status, or you can set the booked flag. We can perform multiple operations while importing the order using the actions interface table. Refer the blog post

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