Distributed WMS Technical White Paper PDF
Distributed WMS Technical White Paper PDF
Distributed WMS Technical White Paper PDF
System
(Technical Implementation)
An Oracle White Paper
July 2009
TABLE OF CONTENTS
3 SOLUTION .............................................................................................................................. 6
3.1 INBOUND MATERIAL FLOW ............................................................................................... 7
3.1.1 Create and Approve PO in Host System.................................................................... 8
3.1.2 Populate PO Interface on Distributed Instance......................................................... 8
3.1.3 Process Interface Data to Create PO in Distributed System..................................... 9
3.1.4 Receipt of Material in the Warehouse ..................................................................... 10
3.1.5 Populate Receiving Open Interface in the Host System........................................... 10
3.1.6 Process Receipt Information in Host System ........................................................... 12
3.1.7 Other Considerations............................................................................................... 14
3.2 OUTBOUND MATERIAL FLOW .......................................................................................... 17
3.2.1 Create and Book Sales Order in Host System ......................................................... 18
3.2.2 Run Concurrent Program to Generate Shipment Batches....................................... 18
3.2.3 Transfer Shipment Request to Distributed System................................................... 18
3.2.4 Process Shipment Request in Distributed System.................................................... 19
3.2.5 Pick, Pack and Ship Material against Shipment Request in Distributed System..... 20
3.2.6 Generate Shipment Advice XML Message in the Distributed System (If using XML
Gateway) 21
3.2.7 Import Shipment Advice in the Host System ............................................................ 21
3.2.8 Process Shipment Advice in the Host System .......................................................... 22
3.2.9 Other Considerations............................................................................................... 22
3.3 INTERNAL MATERIAL TRANSFER .................................................................................... 25
3.3.1 Create and Approve Internal Requisition/Internal Orders in Host System ............. 26
3.3.2 Run Concurrent Program to Generate Shipment Batches....................................... 26
3.3.3 Transfer Shipment Request to Distributed Shipping Instance and Purchase Order
Data to Receiving Distributed Instance................................................................................... 26
3.3.4 Import Shipment Request and Ship Material from Distributed Shipping Instance.. 27
3.3.5 Import and Process Shipment Advice in the Host System........................................ 27
3.3.6 Import and Process PO in Distributed Receiving Instance ..................................... 27
3.3.7 Receipt of Material in the Receiving Warehouse..................................................... 27
3.3.8 Populate Receiving Interface and Process Receipt Confirmation in Host System .. 28
3.3.9 Other Considerations............................................................................................... 29
3.4 RETURN MATERIAL AUTHORIZATION (RMA)................................................................. 30
3.4.1 Create and Book RMA in Host System .................................................................... 30
3.4.2 Populate Order Interface on Distributed System..................................................... 31
3.4.3 Process Interface Data to Create RMA in Distributed System................................ 32
3.4.4 Receipt of Material in the Warehouse ..................................................................... 32
3.4.5 Extract RMA Receipt Data from Distributed and Import into Host System ............ 32
3.4.6 Process RMA Receipt in the Host System ................................................................ 33
3.5 MANUFACTURING TO DISTRIBUTION CENTER MATERIAL FLOW .................................... 34
3.5.1 Create and Approve Job order in Host System........................................................ 35
3.5.2 Create and Approve Intransit Shipment in Host System.......................................... 35
3.5.3 Transform Intransit Shipment into Equivalent PO and Populate PO Interface
Tables of Distributed System. .................................................................................................. 35
3.5.4 Import and Process PO in Distributed System ........................................................ 35
The current Oracle EBS Warehouse Management System (WMS) is an integral part of
Oracle E-Business Suite and hence both the transaction source systems like Purchasing and
Order Management and execution systems like WMS reside and operate within the same
instance. The main advantage of such an integrated solution is that it eliminates the need for
reference and transaction data integrations, which are needed for a typical ‘bolt-on’ WMS
solution. At the same time it makes it increasingly difficult for the customers to implement
Oracle EBS WMS outside the framework of an E-Business solution.
The need for a Distributed WMS solution has been covered in the White Paper for Distributed
Warehouse Management System (Architecture and Solution Overview).
Salient Features
For the architecture and the key business flows that are supported in the Distributed WMS
solution, please refer to the White Paper for Distributed Warehouse Management System
(Architecture and Solution Overview).
Create Users
Organization
* Create an Organization
* Define a Location
* Assign Organization to Location
For a complete detailed description of the setups, please refer to the White Paper on
Distributed Warehouse System (Setup Document).
3 Solution
This section focuses on the solution and implementation of Oracle Distributed WMS solution for
the distributed warehouse. This assumes that the host system can be any of the following –
legacy system, other ERP system such as SAP, other ‘best of breed’ solutions, or another
Oracle ERP system. The following picture describes the responsibility across Host and
Distributed system for various business functions to enable Oracle Distributed WMS solution
for the distributed warehouse.
Warehouse Transfers Wave & Wave & Count and Labeling &
Functions and Moves Labor Plan Labor Plan Adjustment Value Adds GL Process
Setup &
Master Data Products Customers Vendors Rules Layout
We will take individual flows and see how they can be fulfilled using Oracle Distributed WMS
solution for the distributed warehouse.
The steps involved in implementing information flow between the host system and the
Distributed system for material inbound to the warehouse are described below –
In b o u n d P ro c e s s F lo w
Host (EBS 11.5.10)
In itia te p a y a b le s fo r
th e m a te r ia l r e c e iv e d
C re a te a n d A p p ro v e
P u rc h a s e O rd e rs (P O ) P ro c e s s th e re c e ip t
tra n s a c tio n s
P o p u la te th e
P o p u la te P O in te rfa c e o n
R e c e iv in g o p e n
s ta n d a lo n e in s ta n c e
in te r fa c e .
(EBS 12.1.1)
Distributed
P ro c e s s in te r fa c e d a ta to R e c e iv e a n d P u ta w a y
c re a te P O th e m a te r ia l
Im p le m e n ta tio n S te p O u t o f th e b o x
In the above figures the host is assumed to be EBS 11.5.10 and the Distributed system is
EBS 12.1.1. However the host system can be any other version of EBS, other ERP or
legacy system. Steps described below are applicable only for an EBS host and may
change for a different ERP or legacy system.
For detailed information on how to setup the transmission of PO either using EDI or XML
equivalent, please refer to the sections: "Purchase Order Communication to Suppliers”
and “Document Transmission Methods" in “Oracle Purchasing User’s Guide”. Please
refer to the Oracle Purchasing White Paper on “Article On Purchasing Communication
Methods".
During implementation, the systems integrator will need to read or extract these purchase
orders from the host system. Since standard purchase orders cannot be loaded directly
via EDI or XML, they will need to populate the PO interface tables in the Distributed
system using the Purchasing Document Open Interfaces (PDOI). This can be achieved
by –
The interface tables that needs to be populated in the Distributed system using either of
the above 2 approaches are:
• PO_HEADERS_INTERFACE
• PO_LINES_INTERFACE
• PO_DISTRIBUTIONS_INTERFACE
Some of the key column attributes are mentioned below along with the possible
mappings (unless mentioned, the mapping is distributed system-specific):
The host can pass specific receiving controls like receipt routing, over receipt tolerance,
receipt days early or late for each PO line. If this information is not passed, then it will
default from the receiving parameters defined in the Distributed system for that
organization.
Please refer to “Oracle Purchasing Open Interfaces and APIs manual” for detailed
information on what fields to populate for importing new purchase orders.
Once the interface tables have been loaded, the “Import Standard Purchase Order
program” must be run in the Distributed system to import the data from the interface
tables into Oracle Purchasing. The Purchasing Documents Open Interface program
receives the data, derives and defaults any missing data, and validates the data. If no
errors are found in the submission process, the data in the Purchasing Documents Open
Interface tables is loaded into the purchasing base tables to create the standard purchase
order. The purchase order should be created in approved status and should have the
same document number as in the host system.
If the Purchasing Documents Open Interface program finds errors in the interface tables,
the record identification number and the details of the error are written to the
PO_INTERFACE_ERRORS table. You can launch the Purchasing Interface Errors
Report in Purchasing to view the rows that were not imported by the Purchasing
Documents Open Interface along with the failure reason(s) for each row.
The “Purchasing Documents Open Interface” saves or errors out on a line-by-line basis.
This means that if an error is found in a document line, only that line is rolled back (not
submitted to Purchasing), and you can find the error in the PO_INTERFACE_ERRORS
table. The error records in the interface tables must be updated before the re-run of the
import program. Because the Purchasing Documents Open Interface saves or errors out
line by line, it can accept partial documents. Assumption for importing purchase orders
into the Distributed system is that the required reference data such as item, supplier (or
vendor), supplier site, buyer information, UOM, organization, subinventories, locators
must already be set up in the Distributed system.
Please refer to “Oracle Purchasing Open Interfaces and APIs manual” for detailed
information for importing new purchase orders.
Please refer to “Oracle Warehouse Management Users Guide” for detailed information on
how to perform receiving transactions.
Historical information about the receipts made and results of quality inspection etc. are
stored in RCV_TRANSACTIONS (RT) table in Oracle Purchasing. Distributed solution will
provide a view ‘RCV_RECEIPT_CONFIRMATION_V’ which will contain all the detailed
information about the receipt transactions that have been ‘Delivered’ and that affect the
On-Hand.
The view will contain information about the following transaction types – PO Receipt,
Return to Receiving, Corrections, RMA Receipt, Internal Requisition Intransit Receipt,
and Inventory Intransit Receipt. Only those transactions which affect on hand will be
retrieved by the view and therefore Return to Receiving will be played back as Return to
Vendor or Return to Customer in the host system based on source document type. It will
not have information on lines that have only been received and/or inspected but not
delivered.
Column Mapping
RECEIPT Receipt number of the receiving
transaction
DOCUMENT_TYPE Document type e.g. ‘PO’, ‘RMA’
DOCUMENT_NUMBER Document number against which the
receiving transaction was done
DOCUMENT_LINE_NUMBER Document line number
SHIPMENT Shipment number
ITEM Transacted item
TRANSACTION_TYPE Type of transaction e.g. DELIVER,
CORRECT
PRIMARY_QUANTITY Transacted quantity
PRIMARY_UOM Transacted UOM
LOT Lot number (If there are multiple lots for
the transaction, there will be multiple
records in the view corresponding for
each lot)
LOT_QUANTITY Lot quantity
SERIAL Serial number (If there are multiple serials
for the transaction, there will be multiple
records in the view corresponding for
each serial)
System Integrators can query this view directly to fetch all the new receipts for which
confirmation has not been sent to the host system. They can then convert this information
into appropriate format to populate the receiving interface tables of the host system. This
can be achieved in either of the following ways –
Once the confirmation is successfully sent to the host system, a new public API
‘INV_STANDALONE_SYNC_PUB.Update_RC_Extracted()’ will allow the users to update
‘RECEIPT_CONFIRMATION_EXTRACTED’ flag in the RT table.
Customers can decide and update the flag with the any of the values, which can indicate
any of the following statuses -
NULL – Receipt confirmation not sent to the host system
Sent Pending Confirmation – Receipt confirmation sent to the host system but awaiting
confirmation from host.
Sent Confirmed - Receipt confirmation successfully received by the host system.
If the host system is an Oracle E-Business Suite, then the information can directly be
written into the “Receiving Documents Open Interface” of the host system. This involves
populating the key tables ‘RCV_HEADERS_INTERFACE’ and
RCV_TRANSACTIONS_INTERFACE. System integrator should load the receipt
information of Distributed system in the receiving interface tables of the host system.
Once the receipts have been imported into “Receiving Documents Open Interface”, one
can periodically schedule the “Receiving Transaction Processor” to process these
transactions and create receipts in the host system. This will update the Receiving and
Material Transaction tables in the host system.
For transactions involving Lot and Serial controlled items, the tables
MTL_TRANSACTION_LOTS_INTERFACE and MTL_SERIAL_NUMBERS_INTERFACE
have to be populated with the corresponding lot and serial information.
In the case of Lot controlled items, one record for each lot number with the corresponding
quantity needs to be populated.
In the case of Serial controlled items, one record indicating the ‘From’ serial number to
‘To’ serial number range needs to be populated. In case the range is not continuous or
needs to be broken, multiple records may be required.
Column Mapping
HEADER_INTERFACE_ID Rcv_Headers_Interface_ S.Nextval
GROUP_ID Rcv_Interface_Groups_S.Nextval
PROCESSING_STATUS_CODE ‘PENDING’ for newly inserted
unprocessed transactions
RECEIPT_SOURCE_CODE Specifies the source of the transaction
e.g. ‘VENDOR’ for Purchase orders,
‘CUSTOMER’ for RMAs
SHIP_TO_ORGANIZATION_CODE Host system’s Organization
RECEIPT_NUM Receipt number from the distributed
system
Column Mapping
INTERFACE_TRANSACTION_ID Rcv_Transactions_Interface_S.Nextval
HEADER_INTERFACE_ID Rcv_Headers_Interface_ S.Nextval
GROUP_ID Rcv_Interface_Groups_S.Nextval
PROCESSING_STATUS_CODE ‘PENDING’ for newly inserted
unprocessed transactions
RECEIPT_SOURCE_CODE Specifies the source of the transaction
e.g. ‘VENDOR’ for Purchase orders,
‘CUSTOMER’ for RMAs
SOURCE_DOCUMENT_CODE Type of document in the transaction e.g.
PO, RMA
DOCUMENT_NUMBER Document number against which the
transaction was performed in the
distributed system
DOCUMENT_LINE_NUMBER Document line number against which the
transaction was performed in the
distributed system
SHIPMENT Shipment number in the case of
transactions related to ASNs, etc. in the
distributed system
TRANSACTION_TYPE Receiving transaction type e.g. RECEIVE,
CORRECT, etc.
AUTO_TRANSACT_CODE: Automatic transaction creation code that
is used to specify the level at which the
transaction records will be created e.g.
Shipment line, receipt or deliver.
PRIMARY_QUANTITY Transacted quantity
ITEM_ID Item identifier
TO_ORGANIZATION_CODE Host system’s organization
SUBINVENTORY Subinventory where the material has
delivered
LOCATOR_ID Locator identifier where the material has
been delivered
To create the ‘RECEIVE’ and ‘DELIVER’ transactions in distributed system for the
corresponding Purchase order’s ‘DELIVER’ transaction in the host system:
• RCV_TRANSACTIONS_INTERFACE.TRANSACTION_TYPE should be set as
‘RECEIVE’
• RCV_TRANSACTIONS_INTERFACE.AUTO_TRANSACT_CODE should be set
as ‘DELIVER’
• RCV_TRANSACTIONS_INTERFACE.DESTINATION_TYPE_CODE should be
set as ‘INVENTORY’
• RCV_TRANSACTIONS_INTERFACE.RECEIPT_SOURCE_CODE should be set
as ‘VENDOR’
• RCV_HEADERS_INTERFACE.RECEIPT_SOURCE_CODE should be set as
‘VENDOR’
• VALIDATION_FLAG column in RCV_TRANSACTIONS_INTERFACE and
RCV_HEADERS_INTERFACE tables should be set as ‘Y’
Please refer to “Oracle Purchasing Open Interfaces and APIs manual” for detailed
information on how to make use of “Receiving Documents Open Interface” to update
receipt information in the host system.
Please refer to the following Metalink Notes for “ROI” functionality and scripts:
290489.1: New Receiving Open Interface (ROI) Functionality in Procurement Family
Pack J/11.5.10.
559956.1: Receiving Open Interface: Description of Features and Sample Scripts.
245334.1: Sample Receiving Open Interface Script (ezROI)
368811.1: 11.5.10 / R12 ROI How To Perform Purchase Order Receipt For Lot and Serial
Controlled Item
335699.1: 11.5.10 / R12 ROI How to Correct Receiving Transaction with Receiving Open
Interface
360340.1: 11.5.10 / R12 ROI How to Return Receiving Transaction with Receiving Open
Interface
The Purchase Order Change and Cancel APIs are public APIs that enable one to apply
changes or cancellations to the standard purchase orders that exist in the Purchasing
tables. They perform all the necessary validations before updating the changes.
The key inputs for the API relevant for this transaction are:
• P_ORG_ID
• X_PO_NUMBER
• X_LINE_NUMBER
• NEW_QUANTITY
• NEW_PRICE
• NEW_PROMISED_DATE
• NEW_NEED_BY_DATE
The API will return “1” if the purchase order was updated successfully. If there are any
errors, it will return “0” and populate the output parameter X_API_ERRORS.
The key inputs for the API relevant for this transaction are:
• P_ORG_ID
• P_DOC_TYPE (‘PO’ for standard purchase order)
• P_DOC_SUBTYPE (‘STANDARD’ for standard purchase order)
• P_DOC_ID
• P_DOC_NUM
• P_DOC_LINE_ID
• P_DOC_LINE_NUM
• P_ACTION (‘CANCEL’)
• P_CANCEL_REASON
The API will populate the output parameter X_RETURN_STATUS with ‘S’ (if the
cancellation completed successfully), ‘E’ (if the cancellation resulted in an error) or ‘U’ (if
the cancellation resulted in an unexpected error).
Please refer to “Oracle Purchasing Open Interfaces and APIs manual” and the
“Integration Repository” for detailed information on how to invoke purchase order change
and cancellation APIs.
Import ASNs
Supplier will send the ASNs to the Distributed system. One can import ASNs into Oracle
Distributed System by passing the data in the form of ASC X12 856 EDI or its equivalent
XML transaction. For the EDI/XML import to successfully go through suppliers and
supplier sites should be defined as trading partners and Oracle e-commerce gateway
should be appropriately set up. Once all the setups are complete, one can run the Oracle
e-commerce gateway import program to process the transactions. Supplier can also send
the ASNs to the host system from where they can be imported into the Distributed
System by a variety of methods – I-Supplier portal, EDI, or XML.
If the required data is not provided in the transaction, the Oracle e-Commerce Gateway
import process fails the transaction. Then an exception message is displayed in the View
Staged Documents window. If the trading partner is valid and the transaction is enabled,
the import process proceeds to validate the transaction using the user-defined column
rules. If no process or column rule exceptions are detected, the Oracle e-Commerce
Gateway import program will write the transaction to the “Receiving Open Interface”
tables to be processed by the Receiving Open Interface API. ASNs can also be created
manually using the iSupplier Portal and this will create the necessary information in the
“Receiving Open Interface” tables. The system integrators can also write the ASN
information in the “Receiving Open Interface” tables directly using the public APIs. To
successfully import ASNs into Oracle Purchasing, one must run the “Receiving
Transaction Manager”.
The Purchase order form can also be utilized to manually create a purchase order in the
Distributed system when the integration layer is down and receipt advice cannot be sent
or received. The purchase order number in the Distributed system should be same as
purchase order number in the host system.
Reverse Logistics
Distributed system users can perform a ‘Return to Receiving’ transaction to initiate a
return to vendor. Once the ‘Return to Receiving’ transaction is done in the Distributed
system, the goods will move from inventory to receiving and on hand will get
decremented. The Receipt confirmation view will pick these transactions and once the
host interface tables are updated, the Return to vendor transaction will be created in the
host system even if the Distributed system has performed only Return to Receiving
transaction. This is necessary for inventory synchronization.
If for some reason, Distributed system decides to put the material back into Inventory
(Deliver transaction) ater performing Return to receiving, then the system integrator will
have to post a correction on the Return to Vendor trasnaction and then post a deliver
transaction in the host system.
Assumptions – Some of the key assumptions for the inbound flow described above are
summarized again –
1) All the required reference data must be created in advance in the Distributed system
before purchase order is sent.
2) There should be one to one mapping of purchase orders between host and
Distributed system i.e. for every PO in the host system, a corresponding PO will be
created in the Distributed system.
3) The purchase order in the Distributed system should be created in approved status
and its number should be same as purchase order number in the host system.
4) If the host system wants specific receiving controls on a given PO line, then the
receiving controls must be passed to the Distributed system along with the PO
information. Otherwise system will default the receiving controls from the Receiving
Parameters defined in the Distributed system. For simplicity it is recommended that
receipt routing for PO in the host system be ‘Direct Delivery’ while it can be ‘Standard’
or ‘Inspection Required’ in the Distributed system based on the requirements.
The steps involved in implementing information flow between the host system and the
Distributed system for material outbound from the warehouse are described below -
In the above figure the host is assumed to be EBS 11.5.10 and the Distributed system is
EBS 12.1.1. However the host system can be any other version of EBS, other ERP or
legacy system. Steps described below are applicable only for an EBS host and may
change for a different ERP or legacy system.
Customers can transfer the shipment data from the host system to Distributed system
and shipment advice from Distributed system to the host system using any of the
following ways –
We will discuss each of these methods for data transfer while describing the steps for the
above flow. We can break down the above flow in the following steps –
1) Create and book Sales Order in the host system
2) Run concurrent program ‘Create Shipment Batches for Fulfillment’ to group delivery
details into Shipment Request batches.
3) Transfer and insert shipment batches into interface tables of Distributed system.
4) Process shipment request in Distributed system.
5) Pick, pack and ship the sales order in the Distributed system. For this customer can
use the latest features available in WMS in Oracle 12.1.1 like wave planning,
advanced replenishment to replenish your forward pick area, mobile personalization
to personalize the mobile screens, WMS-OTM integration for dock synchronization
and scheduling, rules engine to configure business rules, advance material status etc.
6) Generate the shipment advice message in the Distributed system (If using XML
Gateway)
7) Use ODI maps or schedule published APIs to extract shipment advice data from
Distributed system
8) Process shipment advices to ship confirm the corresponding SO lines in the host
system.
9) Create invoice against the fulfilled SO in the host system
Please refer to “Oracle Order Management Users Guide” for detailed information on how
to create new sales orders.
• Using ODI maps - Customers can create and configure ODI maps to extract the
data from shipment views and insert into shipping interface tables of the
Distributed system. Please refer to Distributed Warehouse Management System
Integrations white paper for more details on how to create and configure ODI
maps.
• Directly populate the data into shipping interface tables on the Distributed system
using public API “WSH_SHIPMENT_REQUEST_PUB.Shipment_Request”.
This API allows the user to query, create, update and delete a shipment request
and this can be accomplished by passing an input parameter “p_action_code”
with valid values: ‘QUERY’,’CREATE’,’UPDATE’ and ‘DELETE’.
Please refer to “Oracle XML Gateway manual” for detailed information on how to set up
XML gateway to import shipment into Oracle Shipping Execution.
Alternately if XML gateway is not used then customers can use ODI or create a
concurrent program to fetch the shipment request data from shipment views and directly
populate the information in the shipping interface tables of the Distributed system using
the public APIs. Once the records are loaded into the shipping interface tables, they can
be processed in any of the following ways –
This API allows processes shipment requests based on a certain input criteria that
can include shipment request status, from and to shipment request numbers, from
and to shipment request dates and transaction_id.
All the required reference data like customer, ship-to and Bill-to locations, customer
contacts, items, UOMs, ship methods, carriers, freight terms, FOB, organization, sub
inventories and locators must be already set up in the Distributed system before the
shipment request is received and processed. If not, the received shipment requests will
error out. However Distributed system can create customer, customer addresses and
contacts on the fly if the customer data does not already exist in the Distributed system
and is sent in the shipment request.
Any interface error records can be viewed and corrected in the Shipment Message
Corrections UI. Once the records are corrected, they can be re-submitted for processing.
Once the shipment request is processed successfully, delivery details are created or
modified in the Distributed system. Additionally if you use XML gateway to process the
shipment request data then a confirmation Business Object Document (BOD) can be sent
to the host system. The confirmation BOD will be sent to the host system only if the
trading partner is set up to send the confirmation BOD.
The shipment request will contain header and detail level information. The host system
should provide a unique document number (DocumentID in XML schema) in the
shipment request header and a unique document line number
(TXN_SRC_LINE_NUMBER in XML schema) for each shipment request line. The hos
system can choose to use the shipment batch number if they feel that this will help to
simplify the mapping between the batch in the host system and the shipment request in
the distributed system. The shipment request processing will automatically create a sales
order in the Distributed system and the sales order number in the Distributed system will
be same as this document number. The host system sales order and sales order line
number will be stored as reference number and reference line number attribute in the
delivery details of the Distributed system. The Distributed system will maintain the
reference of the shipment request number and shipment request line number and use it
when sending the shipment confirmation to the host system.
Please refer to “Oracle XML Gateway manual” for detailed information on how to set up
XML gateway to import shipment into Oracle Shipping Execution.
3.2.5 Pick, Pack and Ship Material against Shipment Request in Distributed System
Orders are released, tasks are created, and material is picked, packed, and shipped from
the warehouse for the shipment requests. The host system’s sales order and line number
information is stored in the delivery details in the Distributed system. When the shipment
is done from the Distributed system, all the shipping documents like packing slip or
customer labels generated, will contain the host system sales order number.
If required, the Distributed system can send shipment information to the customer in the
form of EDI 856. This EDI document can be generated in Oracle Distributed System,
once the order has been ship confirmed.
Please refer to “Oracle Warehouse Management User guide” for detailed information on
how to process and ship a sales order using Oracle EBS WMS and refer to “Oracle E-
Commerce Shipping Implementation Guide” for detailed information on how to generate
and send ASNs (EDI 856) to the customers.
3.2.6 Generate Shipment Advice XML Message in the Distributed System (If using XML
Gateway)
Shipment advice can be generated in the Distributed system as an XML message when
XML gateway is used. XML shipment advice message can be generated using XML
transaction Shipment_Advice that is equivalent to EDI transaction 945 outbound. Oracle
XML gateway must be installed to generate the XML messages. A new XML map
(WSH_STNDO_OAG721_OUT) has been created based on OAG
161_show_shipment_005 dtd to generate the shipment advice from the Distributed
system. The Distributed system organization must be defined as trading partner and
transaction must be enabled in XML gateway for generating the shipment advice.
A shipment advice will contain information like the document number received in the
shipment request, item, quantity, customer, ship to address etc. Shipment advice can be
generated once the delivery is in Closed or In-transit status in the Distributed system. The
Distributed system can initiate the XML shipment advice in either of the following ways –
• Using the Send Outbound Message action in the Shipment Transaction Form
(Navigation: Shipping Transaction form > Delivery tab > Actions > Send
Outbound Message > Select Send Shipment Advice).
As an implementation step, system integrator can configure ODI maps or using published
APIs, create a concurrent program to read the shipment advice XML generated by a
Distributed system using XML gateway. If XML gateway is not installed, then using the
existing shipping views, implementers can extract the required delivery and delivery
details data from the Distributed system. The program should also update the transaction
status to ‘Sent’ in WSH_TRANSACTIONS_HISTORY table in the Distributed system. The
shipment advice data should be populated in the shipping interface of the host system.
The above process assumes that the organization in the host system is Distributed
organization. This will enable to directly create and ship confirm the delivery without
having to call the APIs to reserve and pick release the delivery in the host system, which
can be a significant overhead in terms of processing the transactions.
You must ensure that on hand quantities are synchronized between the host and
Distributed system before you playback the shipment advice in the host system. For
simplicity, negative balances can be allowed in the organization in the host system and a
negative balance will be an indicator or warning that the inventory has not been
synchronized between the two systems. If negative balances are not allowed in the
organization and sufficient inventory is not available, it will error out the shipment process
in the host system and on hand balances must be synchronized before transactions are
re-processed. Once the sales orders are shipped in the host system, it can generate
invoices for the fulfilled orders.
Please refer to “Oracle Order Management Open Interfaces, APIs and Electronic
Messaging guide” for detailed information on how to use interface and APIs to update
shipment information in the host system.
Reservations - If the host system would like the Distributed system to ship specific
material (lot) from a specific location then it can be done in either of the following ways -
• They can pass this information in the shipment request XML. When the orders are
pick released in the Distributed system, inventory will honour this information
when move orders/tasks are created.
• Directly populate the reservation information into the reservation interface tables
(MTL_RESERVATIONS_INTERFACE) of the Distributed system. The reservation
information should contain a reference to the shipment request number and other
mandatory inventory details like lot numbers, location etc. Once populated,
Inventory’s “Reservations Interface Manager” program should be run to validate
and create the reservations.
If item is serial controlled item, then reservations can only be created through the
reservations interfaces in the Distributed system.
Sales Order Visibility and Status – The Distributed system will allow the users to query
the sales order created against the shipment request sent by the host system using the
Sales Order and Order Organizer form. Distributed system can provide the information to
the host system using the host system’s shipment request number. The shipment request
number of the host is the sales order number in the Distributed system. The Distributed
system can also provide the status of an order based on host system’s sales order
number and sales order line number using the Shipping Transaction Form. Host system’
sales order number and sales order line number will be stored as reference number and
reference line number in the delivery details of the Distributed system and will be
available in the shipment transaction form as query parameters.
The Sales order form can also be utilized to manually create a sales order in the
Distributed system when the integration layer is down and shipment request cannot be
sent or received. However it is recommended not to create orders manually in the
Distributed system unless it is very critical since data will need to be reconciled between
manually created order in the Distributed system and shipment request data generated in
the host, when systems are back up and running. This reconciliation might require
significant effort and update of data in the Distributed and host system.
If the host system has modified or cancelled the shipment request (sales order/delivery)
after it has been sent to the distributed system, such changes can also be communicated
as an XML message using the XML gateway. When the XML message is received the
distributed system will verify if the document number already exists. If there are multiple
shipment requests for a given document number then the document number with the
latest or maximum revision will be processed and workflow for the previous revisions will
be closed. A shipment request can be cancelled provided none of the delivery details are
already ship confirmed.
If the host system is an EBS system and the organization is a Distributed organization,
then in order to reduce an order line quantity, the quantity should first be reduced in the
distributed system. Then, the WSH_SHIPMENT_BATCH_PUB.Cancel_Line public api
can be used to unassign the reduced quantity from the shipment request. Thereafter, the
order line quantity can be reduced in the host system.
Please refer to “Oracle Order Management Open Interfaces, APIs and Electronic
Messaging guide” for detailed information on how to process the shipping interface data.
For more details please refer to EBS Release 12.1 documentation on Oracle Metalink
(www.metalink.oracle.com).
Assumptions – Some of the key assumptions for the outbound flow described above are
summarized again –
1) All the required reference data must be created in advance in the Distributed system
before shipment request is sent.
2) The organization in the host system is a Distributed organization.
3) The lines identified for shipment in the host system must be in ‘Ready to Release’ line
status.
4) The shipment request number sent to the distributed system must be a unique
number.
5) The SO lines for the shipment request in the host system will be identified and
extracted using a seeded concurrent program based on criteria like customer,
schedule ship dates etc. The resulting lines will be grouped by Customer, Bill-to
location, Ship-to location, Freight terms and FOB. Each group will represent one
shipment request and must be identified by a unique number.
6) If the host system wants the Distributed system to ship specific material (lot) from a
specific location, they can provide the information in the shipment request XML.
However
- For serial controlled items, reservation interface needs to be used.
- Separate lines need to be created for each lot or locator. If for example
host system wants to ship 5 different lots for the same SO line, then each
lot has to be sent separately in the shipment request XML. This means
there will be 5 delivery lines (1 for each lot) created in the Distributed
system each referring to same SO and SO line of the host system.
7) On hand balances must be synchronized between the two systems before the
shipment advice data is imported and played back in the host system.
Internal orders can be mapped using the inbound and outbound flows described in the
section 3.1 and 3.2. Figure below provides an overview of the steps involved in internal
material transfer process.
Process the
Process receipt
shipment advice transactions
Transfer shipment request to
standalone shipping instance.
Transform shipment request
into equivalent PO and Populate the
Import shipment
populate PO interface on receiving open
advice
standalone receiving instance. interface
Distributed Shipping
Instance (EBS 12.1)
Process shipment
request to create
shipment
Ship material to
Pick and stage receiving Close shipment
material instance and request
generate DSNO
Optional Flow
Distributed Receiving
Instance (EBS12.1)
In the above figures the host is assumed to be EBS 11.5.10 and the Distributed system is
EBS 12.1.1. However the host system can be any other version of EBS, other ERP or
legacy system. Steps described below are applicable only for an EBS host and may
change for a different ERP or legacy system.
The internal transfer process can be achieved using a combination of outbound and
inbound flow described in earlier sections. This flow assumes that both the shipping and
receiving organization are on separate Distributed instance.
We can break down the internal order flow in the following steps –
1) Create and approve internal requisition/internal order in the host system.
2) Run ‘Create Shipment Batches for Fulfillment’ concurrent program to group details and
create shipment batches.
3) Using ODI maps or published APIs extract the shipment batch information and insert it as
a shipment request into shipping interface tables of Distributed shipping instance. Insert
the internal order data into purchasing interface tables of the Distributed receiving
instance to create an equivalent purchase order.
4) Import shipment request and ship confirm the material from Distributed shipping instance.
5) Use ODI maps or using published APIs create and schedule a concurrent program to
import shipment confirmation data from Distributed shipping system and ship confirm the
corresponding internal order lines in the host instance
6) Import and process purchase order in the Distributed receiving instance.
7) Receive the material in the Distributed receiving instance.
8) Use ODI maps or using published APIs, create and schedule a concurrent program to
import and transform the PO receipt information from Distributed receiving instance and
receive corresponding intransit shipment lines in the host system
Please refer to “Oracle Purchasing Users Guide” for detailed information on how to create
internal sales orders.
Please refer to the Outbound Material Flow section 3.2.2 to get more details on how to
create shipment request and insert the data into Distributed system.
3.3.3 Transfer Shipment Request to Distributed Shipping Instance and Purchase Order
Data to Receiving Distributed Instance
The shipment request can be inserted into shipping interface tables of the shipping
Distributed instance using any of the following ways –
The shipment data from the host system should also be converted to populate the PO
interface tables of the Distributed instance representing receiving organization to create
an equivalent purchase order. The purchase order number in the Distributed receiving
instance should be kept same as internal sales order number in the host system to
maintain the reference between two systems. The shipping organization must be defined
as a supplier in the Distributed receiving instance to facilitate the creation of PO. Please
refer to the inbound flow section 3.1 to get more details on the steps to send PO
information to a Distributed system.
3.3.4 Import Shipment Request and Ship Material from Distributed Shipping Instance
Shipment request data can be imported using XML gateway (if installed) or by directly
populating the shipping interface tables of the Distributed shipping instance. Once the
interface data is populated, users can run the ‘Process Shipment Requests’ program or
use public APIs to process the data. For more details please refer to Outbound Material
Flow section 3.2.4. To facilitate creation of sales order in shipping Distributed instance,
the receiving organization must be defined as a customer.
Orders are released, tasks are created, and material is picked, packed, and shipped from
the warehouse to the destination organization. Optionally the shipping instance can also
send an ASN to the receiving organization.
Please refer to “Oracle Warehouse Management User guide” for detailed information on
how to process and ship a sales order using Oracle EBS WMS and refer to “Oracle E-
Commerce Shipping Implementation Guide” for detailed information on how to generate
and send ASNs (EDI 856) to the customers.
• Using published APIs and views, create a concurrent program to read the
shipment advice generated (if using XML gateway) by shipping instance else the
shipment data be extracted from the existing shipping views. The data should
then be inserted into shipping interface tables of the host system.
• Using ODI maps. Please refer to Distributed Warehouse Management System
Integrations white paper for more details on how to create and configure ODI
maps.
Once the data is loaded into the shipping interface tables of the host system, it can be
processed by scheduling the standard concurrent program, which will create, and ship
confirm the deliveries. For more details please refer to the Outbound Material Flow
section 3.2.7 and 3.2.8
Please refer to “Oracle Purchasing Open Interfaces and APIs manual” for detailed
information on what fields to populate for importing new purchase orders
Please refer to “Oracle Warehouse Management Users Guide” for detailed information on
how to perform receiving transactions.
3.3.8 Populate Receiving Interface and Process Receipt Confirmation in Host System
As an implementation step, system integrators can query the Receipt Confirmation view
directly to fetch all the new receipts for which confirmation has not been sent to the host
system. They will then need to convert the PO receipt of the receiving instance into an
equivalent internal order receipt and insert the data into receiving interface tables of the
host system. This can be done using the dummy supplier setup as explained in step 3.3.3
so that any PO receipts of this supplier can be separated from normal PO receipt and
converted into internal order receipt.
The data can be sent from the receiving instance to the host system using any of the
following ways–
For the “ROI” related tables and interfaces, please refer to the section “Process Receipt
Information in Host System” under the “Inbound Flow” section.
To create the ‘RECEIVE’ and ‘DELIVER’ transactions in distributed system for the
corresponding Internal Material transfer type Purchase order’s ‘DELIVER’ transaction in
the host system:
• Identify whether the ‘DELIVER’ transaction of the Purchase order is linked to
Internal Material transfer and find the corresponding Internal requisition from the
host instance
• RCV_TRANSACTIONS_INTERFACE.RECEIPT_SOURCE_CODE should be set
as ‘INTERNAL ORDER’
• RCV_HEADERS_INTERFACE.RECEIPT_SOURCE_CODE should be set as
‘INTERNAL ORDER’
• RCV_TRANSACTIONS_INTERFACE.SOURCE_DOCUMENT_TYPE should be
set as ‘REQ’
• RCV_TRANSACTIONS_INTERFACE.TRANSACTION_TYPE should be set as
‘RECEIVE’
• RCV_TRANSACTIONS_INTERFACE.AUTO_TRANSACT_CODE should be set
as ‘DELIVER’
The host system should process the receipt information obtained from the Distributed
system and update its inventory records so that the internal requisition or order can be
closed.
For more details on how to receive and process receipt information in the host system,
please refer to Inbound Material Flow section 3.1.5 and 3.1.6.
Assumptions – Some of the key assumptions for the internal order flow described above
are summarized again:
1) All the required reference data must be created in advance in the distributed system
before purchase order is sent.
2) The purchase order number in the distributed receiving system should be same as
internal requisition number in the host system to maintain the reference between two
systems.
The customer should ensure that PO number sequence should not conflict with the
sequence for internal requisition of host as internal requisition number of host will become
PO number in the receiving instance. Document number entry method can be set as
Manual so that PO can be set programmatically to be same as internal requisition
number of the host system.
If the host system decides to update the ISO after the requisition data has been sent to
the distributed instance, then the ISO and IR in the distributed system should be first
cancelled. The changes can be made in the host system and IR data should be sent
again to the distributed system.
R M A P ro c e s s F lo w
P o p u la te o r d e r im p o rt
in te r fa c e o n s ta n d a lo n e P o p u la te th e re c e iv in g
s y s te m o p e n in te rfa c e
(EBS 12.1.1)
Standalone
P ro c e s s in te r fa c e
R e c e iv e a n d P u ta w a y
re c o rd s to c re a te
th e m a te r ia l
RM A
Im p le m e n ta tio n S te p O u t o f th e b o x
In the above figure the host is assumed to be EBS 11.5.10 and the Distributed system is
EBS 12.1.1. However the host system can be any other version of EBS, other ERP or
legacy system. Steps described below are applicable only for an EBS host and may
change for a different ERP or legacy system.
• Using the public APIs, create and schedule a concurrent program, which will
extract the RMA data from the host system and populate the interface tables on
Distributed system.
• Using Oracle Data Integrator (ODI). For more details on how to create and
configure an ODI map, please refer to Distributed WMS Integrations white paper.
New RMAs can be imported into distributed warehouse by using Order import open
interface. Order import is an Order Management open interface that consists of open
interface tables and a set of APIs. Order import can import new and change completed
RMAs from other applications.
The Order import open interface uses Application Program Interfaces (APIs) to process
the data in the Oracle Applications interface tables to ensure that it is valid before
importing it into Oracle Order management.
One must write a custom program or script using SQL loader or some other programming
language to convert data from the host system into the standard data format that order
import expects and load it into the order import interface tables. The following interface
tables are used by order import process –
• OE_HEADERS_IFACE_ALL
• OE_LINES_IFACE_ALL
• OE_CUSTOMER_INFO_IFACE_ALL
Column Mapping
ORDER_NUMBER RMA number from the host system
ORDER_TYPE Pre-defined Order Type e.g. ‘RMA – No
Credit’
OPERATION_CODE ‘INSERT’
ORDER_SOURCE_ID Pre-defined Order Source e.g. ‘Returns’
INVOICE_CUSTOMER_NUMBER Customer and Customer Site information
INVOICE_TO_ORG_ID
SHIP_TO_ORG_ID
SHIP_TO_CUSTOMER_NUMBER
SOLD_TO_ORG_ID
LINE_NUMBER Line number
INVENTORY_ITEM_ID Item identifier in the distributed system
SHIP_FROM_ORG_ID Distributed system’s warehouse
LINE_TYPE_ID Line Type
RETURN_REASON_CODE Mandatory field for Returns and can be
defaulted from one of the seeded return
reason codes e.g. ‘DAMAGED
PRODUCT’
Please refer to “Oracle Order Management Open Interfaces and APIs manual” for
detailed information on how to import orders into Oracle Order management.
If the host system has modified or cancelled the sales order after it has been downloaded
in the distributed warehouse, such changes can also be transmitted to the distributed
warehouse using order import open interface.
Once the interface tables have been loaded, one must run the “Order Import” concurrent
program to import the data from the interface tables into Oracle Order management.
Order import program validates and defaults any missing data. Valid transactions are
then converted into orders with lines, reservations, price adjustments, and sales credits in
the base order management tables. If the Order Import program finds errors in the
interface tables, such as incomplete information, the record identification number and the
details of the error are stored. One can use the Order Import Corrections UI to determine
if the data failed the import process.
For importing RMAs into distributed warehouse, reference data such as item and Bill of
materials must already be set up in the distributed warehouse; though order import
provides the capability to add a new customer, address and contacts using order Import.
This can be accomplished by populating OE_CUSTOMER_INFO_IFACE_ALL and order
import program adds a new customer based on the data in this table and
OE_HEADERS_IFACE_ALL. This table can also be used to import a new address only or
contact information of an existing customer.
Please refer to “Oracle Order Management Open Interfaces and APIs manual” for
detailed information on how to import orders into Oracle Order management.
Please refer to “Oracle Warehouse Management Users Guide” for detailed information on
how to perform RMA receiving transactions
3.4.5 Extract RMA Receipt Data from Distributed and Import into Host System
The system integrator can query all the RMA receipts from the Receipt Confirmation view
(‘RCV_RECEIPT_CONFIRMATION_V’) for which confirmation has not been sent to the host
system. They can then convert this information into appropriate format to populate the
receiving interface tables of the host system. This can be achieved in either of the
following ways –
For more details on how to utilize Receipt Confirmation view please refer to Inbound
Material Flow section 3.1.5.
For the “ROI” related tables and interfaces, please refer to the section “Process Receipt
Information in Host System” under the “Inbound Flow” section.
To create the ‘RMA’ transaction in distributed system for the corresponding ‘RMA’
transaction in the host system:
• RCV_TRANSACTIONS_INTERFACE.RECEIPT_SOURCE_CODE should be set
as ‘CUSTOMER’
• RCV_HEADERS_INTERFACE.RECEIPT_SOURCE_CODE should be set as
‘CUSTOMER’
• RCV_TRANSACTIONS_INTERFACE.SOURCE_DOCUMENT_TYPE should be
set as ‘RMA’
• RCV_TRANSACTIONS_INTERFACE.TRANSACTION_TYPE should be set as
‘RECEIVE’
• RCV_TRANSACTIONS_INTERFACE.AUTO_TRANSACT_CODE should be set
as ‘DELIVER’
• VALIDATION_FLAG column in both RCV_HEADERS_INTERFACE and
RCV_TRANSACTIONS_INTERFACE should be set as ‘Y’.
Assumptions – Some of the assumptions for the above flow are re-summarized -
1) All the required reference data must be created in advance in the Distributed system
before the RMA is sent.
2) There will be one to one mapping of RMAs between host and Distributed system i.e.
for every RMA in the host system, a corresponding RMA will be created in the
Distributed system.
3) The RMA number in the Distributed system should be same as RMA number in the
host system.
a. The manufacturing plant may operate on a legacy system while planning and
other functions are maintained in a central ERP or host system. Warehouse is a
separate organization using the Distributed WMS solution and once the finished
goods are manufactured in the plant, they are transferred to warehouse or DC. The
warehouse may also store raw materials and supply it to the manufacturing plant.
b. When the finished goods are manufactured and stored in a dedicated storage area for
finished goods (subinventory within same organization or a separate warehouse). In
such a case customer may want to upgrade the storage area to Distributed WMS so
that they can utilize the latest features of the EBS WMS and will need a mechanism to
bring the finished goods inventory into Distributed WMS system.
warehouse/DC
Process the
receipt
transactions
Receive and
Process PO interface putaway the
record to create PO product from
manufacturing
The figure above displays how manufacturing to DC material flow can be mapped for
scenario A. In the figure the host is assumed to be EBS 11.5.10 and the Distributed
system is EBS 12.1.1. However the host system can be any other version of EBS, other
ERP or legacy system.
Please refer to “Oracle Work in Process Users Guide” for detailed information on how to
create job orders.
3.5.3 Transform Intransit Shipment into Equivalent PO and Populate PO Interface Tables
of Distributed System.
As an implementation step, the system integrator needs to convert the inter org transfer
shipment data into an equivalent purchase order against which distributed warehouse
can receive the finished goods when it is received from the manufacturing plant. This can
be achieved by:
The data will be inserted into purchasing interface tables of the distributed system. The
PO number should be same as the intransit shipment number to maintain the reference
between the two systems.
using WMS rules engine to direct the users to put away the material to appropriate
locations based on the business constraints.
Please refer to “Oracle Warehouse Management Users Guide” for detailed information on
how to perform receiving transactions.
• Using ODI Maps - For more details on how to create and configure an ODI map,
please refer to Distributed WMS Integrations white paper
• Using published APIs - System integrator can create and schedule a concurrent
request to extract the receipt data from Distributed system and insert into
receiving interface tables of the host system.
The host system should process the receipt information obtained from the distributed
system and update its inventory. Also the program distinguishes these PO receipts from
other supplier PO receipts, which will be processed as PO receipts in the host system.
You can use the supplier to distinguish the receipts, which are done for finished goods
received from the plant. These receipts will be played back as intransit shipment receipt
in the host system. For this you can either create a dummy supplier or setup plant as a
supplier. Any PO receipts queried from the view against this dummy supplier will then be
played back as intransit shipment receipt in the host system.
Assumptions – Some of the key assumptions for the manufacturing to DC material flow
described above are summarized again –
1) All the required reference data must be created in advance in the distributed system
before shipment request is sent.
2) The PO number in the receiving instance is same as the intransit shipment number of
the host system.
3) The manufacturing plant must be defined as supplier in the distributed system.
The view will extract the information for all the adjustment transactions for which
confirmation has not been sent. As an implementation step, system integrators will need
to extract the adjustment information from this view and transform it to populate the
material transaction interface (MTI) table in the host system. This can be done by either
of the following ways –
• Using ODI Maps - For more details on how to create and configure an ODI map,
please refer to Distributed WMS Integrations white paper
• Using published APIs - System integrator can create and schedule a concurrent
request to extract the adjustment data from Distributed system and insert into
interface tables of the host system.
Column Mapping
TRANSACTION_DATE Date of transaction
WAREHOUSE Distributed warehouse
ITEM Item Name
SUBINVENTORY/LOCATOR/LPN SKU of the transaction
TRANSFER_SUBINVENTORY/ Destination SKU for the transfer type transaction
TRANSFER_LOCATOR/
TRANSFER_LPN
TRANSACTION_QUANTITY Quantity transacted
TRANSACTION_TYPE Type of transaction based on lookup codes
TRANSACTION_SOURCE Source of transaction based on lookup codes
TRANSACTION_EXTRACTED Flag to indicate if this transaction has been sent to
the host system
Sent But Pending Confirmation – Adjustment confirmation sent to the host system but
awaiting confirmation if host system received it successfully or not.
Sent Confirmed - Adjustment confirmation successfully received by the host system.
For simplicity, it is recommended that the host system creates an alias receipt or issues
against the adjustments done in the Distributed system, e.g. creating an alias
issue/receipt for cycle count or physical inventory adjustments in warehouse. For greater
control and visibility, the host system can also have a one to one mapping of the
adjustment transactions e.g. populating the cycle count interface of the host with cycle
count adjustments done in warehouse and then processing these interface records.
Inventory Adjustments
Process the
Host System
adjustment
transactions
Populate adjustment
information into material
transaction interface
Distributed
System
Perform adjustment
transactions e.g.
Cycle count
adjustment
Distributed will indicate that transactions are not reconciled between the two systems and
can serve as a trigger to initiate the synchronization process.
Column Mapping
WAREHOUSE Distributed warehouse
ITEM Item available on hand
SUBINVENTORY/LOCATOR/LPN SKU information of the material
PRIMARY_QUANTITY Quantity available in the SKU
SNAPSHOT_DATE Date and time when the snapshot was taken
ONHAND_STATUS Status of the material involved
If the on hand balances between the two systems are not same then users need to make
sure that all the receipt and shipment confirmations have been sent to the host along with
any adjustments transactions. If the discrepancies still persist then they might need to
compare the transactions history between the two systems and take appropriate actions
for the missing transactions. The remedial actions could be:
• Subinventory Transfer: If the material resides in a different SKU
• Miscellaneous Receipt: If the material is in deficit in the SKU
• Miscellaneous Issue: If the material is in excess in the SKU
Customers can use Oracle Data Integrator (ODI) for all data related integrations i.e. to
synchronize the reference data as well as transactional data like Purchase and sales
order from the host system to Distributed and receipt confirmations and shipment advices
from the Distributed to host system. ODI can be used as data transfer tool since –
• ODI is designed for migrating and transforming data across database instances
• ODI has the ability to migrate data across different database technologies. This
means Distributed instance and ERP do not need to be of identical Applications
version or RDBMS release.
• ODI has the ability to directly invoke web services if the customer wants to bypass the
open interface and perform the transactions in real time.
• ODI has a Journalizing capability that discovers which transaction records are new.
This needs to be used to facilitate incremental synchronization.
For more details on ODI mappings and configurations in Distributed WMS, please refer to
Distributed WMS Integrations white paper.
Host System
Use Oracle
B2B or Integration ODI or Create program to extract item,
Layer
supplier and customer data from the host system and
insert into Oracle Distributed WMS open interfaces
APIs
Oracle Distributed
WMS System
One can import items from any source into Oracle Distributed System using the item
open interfaces. It allows you to 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. You can also import item category assignments. This can be
When items are imported through the item open interface in Distributed system, it can
create new item in item master organization, update existing item, or assign existing item
to additional organizations. You can specify values for all the item attributes, or you can
specify just a few attributes and let the remainder default or remain null. The Item
Interface also lets you import revision details, including past and future revisions and
effectivity dates. Item Category assignments can also be imported simultaneously with
the process of importing items, or later as a separate task.
System Integrators must extract item information from the host system and inserts the
records into the item and item category interface tables of distributed system.
Some of the key columns and their recommended values are mentioned below:
Column Mapping
SEGMENTn Segments used should correspond to the
System Item’s key flexfield segments that
were defined for the item and used to
identify the items in Inventory
ORGANIZATION_ID Warehouse Organization that the item
ORGANIZATION_CODE belongs to and either one of these columns
needs to be populated
DESCRIPTION Item description
TRANSACTION_TYPE ‘CREATE’ to import new items
SHIPPABLE_ITEM_FLAG Set as ‘Y’
PURCHASING_ITEM_FLAG
PURCHASING_ENABLED_FLAG
CUSTOMER_ORDER_FLAG
INVENTORY_ITEM_FLAG
SO_TRANSACTIONS_FLAG
MTL_TRANSACTIONS_ENABLED_FLAG
INVOICEABLE_ITEM_FLAG Set as ‘N’ or not populated (default is ‘N’) as
INVOICE_ENABLED_FLAG items are not costed in the distributed
COSTING_ENABLED_FLAG system
SUBINVENTORY/LOCATOR/LPN SKU information of the material
For more details on how to use this API, please refer to Oracle’s Integration Repository
that is shipped as part of Release 12.1 Oracle E-business Suite.
For ease and simplicity, it is recommended to define the items as plain item and maintain
inventory at aggregate (subinventory) level in the host system while maintain specific
inventory controls like lot or serial control for the item in the distributed system. In certain
scenarios, if the host system wants to maintain the exact details then the item and
location controls can be same in the host and distributed system. .
Please refer to “Oracle Inventory’s Open Interfaces and APIs manual” for information on
how to use “Item Open Interfaces” to update item information in Distributed system.
When all validations are passed, records are inserted into PO_VENDORS,
PO_VENDOR_SITES_ALL and PO_VENDOR_CONTACTS respectively.
Please refer to “Oracle Payables Reference Guide” for detailed information on how to
make use of “Supplier Open Interface” to update Supplier information the warehouse
system.
Please refer to “Oracle Trading Community Architecture User Guide” for detailed
information on how to make use of “Customer Open Interface” to update Customer
information in the warehouse system.
Oracle Corporation
World Headquarters
500 Oracle Parkway
Redwood Shores, CA 94065
U.S.A.
Worldwide Inquiries:
Phone: +1.650.506.7000
Fax: +1.650.506.7200
www.oracle.com