This chapter contains information about application transaction detail.
This chapter covers the following topics:
The implementation of any transaction requires some setup in Oracle E-Business Suite and Oracle e-Commerce Gateway. This section focuses on the application setups necessary to implement a transaction that integrates with Oracle Inventory. The Oracle Inventory transactions are listed in the following table:
Transaction Name | Direction | Transaction Code | ASC X12 | EDIFACT |
---|---|---|---|---|
Movement Statistics | Outbound | MVSTO | N/A | CUSDEC |
Note: See Oracle Inventory Transaction Summaries for layout details.
The topics covered for outbound transactions include the following:
Trading Partner Link to Oracle e-Commerce Gateway
Relevant Application Profile Options and Setups
Extract Criteria
Columns Updated Upon Extraction
The transaction requirements may change when enhancements are made such as additional data added to the transaction. Current transaction details can be found on Oracle Support's web site.
Current detail record layouts are reported via the Transaction Layout Definition Report and the Interface File Data Report.
Legal entities are defined in Oracle Human Resources. Included in the definition is the EDI Location Code that trading partners agree to exchange to represent the full detailed address. Often they do not send the full address, but just the EDI Location Code, a critical data field to Oracle e-Commerce Gateway.
The EDI Location Code is the link between a legal entity in Oracle Human Resources and the trading partner site definition in Oracle e-Commerce Gateway. This enables Oracle e-Commerce Gateway to access the detailed data about the legal entity in Oracle Human Resources without maintaining the detail data in Oracle e-Commerce Gateway.
To ensure that the trading partner link between Oracle e-Commerce Gateway and Oracle Human Resources is set up properly, verify that the legal entity and the EDI Location Code in Oracle Human Resources is the correct legal entity selected for the trading partner definition in Oracle e-Commerce Gateway. The selected legal entity and the EDI Location Code defined in Oracle Human Resources are displayed in the Define Trading Partners window, Assignment tab. If the data is not what you intend it to be, you must make the appropriate changes for the transaction to be extracted for the correct trading partner. This could involve either altering the legal entity in Oracle Human Resources, or assigning a different legal entity to that EDI Location Code in Oracle e-Commerce Gateway.
Please note that if the trading partner is associated with multiple operating units, then the transaction to be extracted is for a specified operating unit only linked to your responsibility. To extract all the data for multiple operating units, you still need to run the program one by one by specifying different operating unit each time when you run the program. See the Outbound Transactions chapter in the Oracle e-Commerce Gateway User's Guide for details.
Refer to the Trading Partner chapter for recommendations on selecting the correct trading partner EDI Location Code for the control record 0010 for the transaction in the transaction interface file.
The outbound Movement Statistics transaction is controlled by four database views that are defined according to the Oracle Inventory data model for inventory movement statistics. The four views contain variables which are dynamically set based on your responses to the extract program parameters (refer to Oracle e-Commerce Gateway User's Guide, Outbound Transactions chapter for a list of the program parameters).
The four database views are as follows:
ECE_MTL_MOV_STAT_V
ECE_MVSTO_DETAILS_V
ECE_MVSTO_HEADERS_V
ECE_MVSTO_LOCATIONS_V
The ECE_MVSTO_DETAILS_V view is used to identify which movement statistics are eligible for extraction. The extract criteria are as follows:
Trading partner is defined
Transaction type enabled for the trading partner
Material movement statistics have not been previously extracted
Material movement statistics status is either FROZEN or VERIFIED
Material movement type matches type selected when transaction was initiated
If necessary, you can use SQLPLUS to verify if there are any eligible documents to be extracted. To do so, you must first set the organization context and then issue the SQL count function as follows:
SQLPLUS> execute fnd_client_info.set_org_context('<Org number>'); SQLPLUS> select count(*) ECE_MVSTO_DETAILS_V;
Review all your set ups if the count value is 0 as this indicates there are no eligible documents to be extracted.
Once an eligible material movement statistic entry is successfully extracted and written to the transaction interface file, it is marked by Oracle e-Commerce Gateway to prevent it from subsequent extraction. The following table is a list of fields updated based on an excerpt of code in the ECEMVSOB.pls file to update the MTL_MOVEMENT_STATISTICS table:
Column | Value |
---|---|
EDI_SENT_FLAG | Y |
EDI_TRANSACTION_DATE | SYSDATE |
EDI_TRANSACTION_REFERENCE | see below |
EDI_TRANSACTION_REFERENCE is the concatenation of the parameter values entered when the transaction was initiated:
Legal entity
Zone code
Period Name
Stat type - either INSTAT or EXSTAT
Movement Type - either A for arrivals, D for Dispatch, AC for arrival adjustments or DC for dispatch adjustments
The following table shows the transactions that are delivered with Release 12:
Transaction Name | Direction | Transaction Code | ASC X12 | EDIFACT |
---|---|---|---|---|
Purchase Order | Inbound | POI | 850 | ORDERS |
Purchase Order Change | Inbound | POCI | 860 | ORDCHG |
Purchase Order Acknowledgment | Outbound | POAO | 855 | ORDRSP |
PO Change Acknowledgment | Outbound | POCAO | 865 | ORDRSP |
The topics covered for inbound transactions include the following:
Trading Partner Link to Oracle e-Commerce Gateway
Oracle e-Commerce Gateway Required Fields
Review Oracle e-Commerce Gateway Exceptions
Resolve Oracle e-Commerce Gateway Exceptions
Relevant Oracle Order Management Profiles and Setup Steps
Order Management Open Interface Required Fields
Review Order Management Open Interface Exceptions
Resolve Order Management Open Interface Exceptions.
The topics covered for outbound transactions include the following:
Transaction Handling Options
Trading Partner Link to Oracle e-Commerce Gateway
Relevant Oracle Order Management Profiles and Setup steps
Extract Criteria
Columns Updated Upon Extraction
The transaction requirements may change when enhancements are made such as additional data added to the transaction. Current transaction details can be found on Oracle Support's web site.
Current detail record layouts are reported via the Transaction Layout Definition Report and the Interface File Data Report.
Note: See Oracle Order Management Transaction Summaries for layout details.
This transaction is the inbound purchase order from the customer's procurement application into the Oracle Order Management application. This transaction uses the Order Management Order Import open interface.
Customers and Customer sites are defined in either Oracle Receivables or Oracle Order Management. Included in the definition is the EDI Location Code that trading partners agree to exchange to represent the full detailed address. Often they do not send the full address, but just the EDI Location Code. This is a critical data field to Oracle e-Commerce Gateway.
The EDI Location Code is the link between a Customer/Customer site in Oracle E-Business Suite and the trading partner site definition in Oracle e-Commerce Gateway. This enables Oracle e-Commerce Gateway to access the detailed data about the Customer or Customer site in the base Oracle E-Business Suite application without maintaining the detail data in Oracle e-Commerce Gateway.
To ensure that the trading partner link between Oracle e-Commerce Gateway and Oracle E-Business Suite is set up properly, verify that the Customer/Customer site and the EDI Location Code in Oracle E-Business Suite is the correct Customer/Customer site selected for the Trading Partner definition in Oracle e-Commerce Gateway. The selected Customer/Customer site and the EDI Location Code defined in Oracle E-Business Suite are displayed in the Define Trading Partners window, Assignment tab. If the data is not correct , you must make the appropriate changes for the transaction to be imported for the correct trading partner. This could involve either altering the Customer/Customer site in the base application, or assigning a different Customer/Customer site to that EDI Location Code in Oracle e-Commerce Gateway.
Refer to Trading Partner chapter for recommendations on selecting the correct trading partner EDI Location Code for the control record 0010 for the transaction in the transaction interface file.
The following is a list of the Oracle e-Commerce Gateway required fields. These fields are required to authenticate the trading partner and transaction. 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 Order Management Open Interface tables to be processed by the Order Management Open Interface API.
Oracle e-Commerce Gateway Column Name for Required Fields | Record Number | Position Number | Note |
---|---|---|---|
TEST_INDICATOR | 0010 | 20 | "T" or "P" |
TP_DOCUMENT_ID | 0010 | 30 | POI |
TP_TRANSLATOR_CODE | 0010 | 70 | Translator identifier for this trading partner |
TP_LOCATION_CODE | 0010 | 80 | The EDI Location Code |
TEST_INDICATOR
This column represents the test or production indicator from the Trading Partner. If this value does not match the test or production indicator associated with the trading partner defined in Oracle e-Commerce Gateway, a process rule exception is detected. Then an exception message is displayed in the View Staged Documents window.
The valid values are ‘T' for test and ‘P' for production.
TP_DOCUMENT_ID
This column identifies the type of document being sent by the Trading Partner. If this document type is not enabled for the trading partner defined in Oracle e-Commerce Gateway, a process rule exception is detected. Then an exception message is displayed in the View Staged Documents window.
The valid value is POI for standard Purchase Order.
TP_TRANSLATOR_CODE, TP_LOCATION_CODE (EDI Location Code)
The two columns in combination uniquely identify a Trading Partner in Oracle e-Commerce Gateway. Once the trading partner definition is accessed, Oracle e-Commerce Gateway can verify whether the transaction is enabled for the Trading Partner.
If this trading partner is not defined in Oracle e-Commerce Gateway, a process rule exception is detected. Then an exception message is displayed in the View Staged Documents window.
Refer to the Trading Partner section to properly define your trading partners and get a better understanding of how these fields are used in the process.
Use the Oracle e-Commerce Gateway View Staged Documents window to review the Oracle e-Commerce Gateway transaction exceptions as specified by the process and column rules in the e-Commerce Gateway. Once the exceptions are identified and resolved, you can submit the transaction for reprocessing, ignore the exception during reprocessing, or delete the transaction. Select the preferred option in the View Staged Documents window.
To resolve Oracle e-Commerce Gateway exceptions, you can either correct the set up data in Oracle e-Commerce Gateway or Oracle E-Business Suite, or ask the Trading Partner to send a corrected transaction.
Note: You cannot change the actual content of the inbound transaction via the View Staged Document window.
If the Trading Partner sends a corrected transaction, be sure to delete the erroneous transaction from Oracle e-Commerce Gateway's staging tables using the View Staged Documents window. The duplicate transaction may cause confusion.
The required Order Management setup steps are the same whether the order is entered online or through Order Import. The following is a sample list of the Order Management setup steps needed to process orders through the e-Commerce Gateway or entered online.
Customer and Customer Sites must be defined in Order Management/ Oracle Receivables.
Order Types must be defined in Order Management.
Item and appropriate customer items must be defined in Oracle Inventory.
Oracle Order Management provides powerful Defaulting functionality that is used by the Order Import process, as well as by orders entered manually using the Order Management forms. If a column is left null in the interface tables, then the Defaulting Rules are invoked, to provide a value for those columns. Many of the attributes can be defaulted per customer or customer site, by populating columns in the Customer tables (Order Management tab). Examples of those attributes are salesperson, shipping warehouse, payment terms, order type, freight terms, ship method, price list, and many others. There are many other sources for data that can be used in defaulting, including Pricing Agreements, Order Type, Line Type, Item, etc.
For a list of what Defaulting Rules are seeded with Order Management, see the Oracle Order Management User's Guide, Appendix E.
You can create your own Defaulting Rules by navigating to Setup, Rules, Defaults from the OM Superuser Menu. For an explanation of the Defaulting Rules forms with examples of their use, see the technical reference paper Using Defaulting Rules in Oracle Order Management, My Oracle Support Knowledge Document 122461.1. With appropriate use of Defaulting Rules, you can significantly reduce the amount of data that has to be entered manually or populated in the Order Import tables.
The EDI standard transaction has three (3) key levels of data: Header, Line (item), and Schedules (Shipments).
The Order Import open interface tables have a two level structure where the order line and a single shipment are combined into one table entry for each combination. Order Import requires that line and shipment data be consolidated into the LINE level table. For example, if an order in a standard EDI transaction has an order line with five (5) shipments, then the order line data is combined with each shipment detail in the transaction interface file. Subsequently, there will be five (5) detail table entries in the application Order Import open interface tables.
Standard Purchase Order EDI Transactions have a three-level structure:
Order Header
Order Line
Order Shipment (Schedules)
The Order Import open interface tables have a two-level structure:
Order Header
Order Line (Combined Order Line and Order Shipment)
The e-Commerce Gateway transaction accommodated the three level transactions so that the item data is written only once on Record 2000 (also records 2010/2020 if needed) followed by all the shipment (schedules) in records 3000-4900. The process will combine the item data to each set of its associated shipment data before writing the data to the Order Import LINE level table.
Though price is often set at the line level in a standard transaction, it was necessary to have the price at the shipment level in this transaction to allow different prices at the Order Import LINE level; hence all price data is on record 3000.
The following table has a simple example of the X12 850 Purchase Order segments mapping to the e-Commerce Gateway transaction.
X12 - 850 Transaction Level | X12 - 850 Transaction Segment | e-Commerce Gateway POI File Transaction Record | e-Commerce Gateway POI File Data Content | Order Import Level |
---|---|---|---|---|
HEADER | BEG | 1000 | Basic PO header | HEADER |
Line (Item 1) | PO1 | 2000 | Basic PO item 1 (for first shipment) |
|
Shipment (first for Item 1) |
SCH | 3000/4000 | Shipment Data (with first shipment) |
LINE (Item 1/shipment 1) |
Shipment (second for Item 1) |
SCH | 3000/4000 | Shipment Data (with second shipment) |
LINE (Item 1/shipment 2) |
Shipment (third for Item 1) |
SCH | 3000/4000 | Shipment Data (with third shipment) |
LINE (Item 1/shipment 3) |
Line (Item 2) | PO1 | 2000 | Basic PO item 2 (for first shipment) |
|
Shipment (first for Item 2) | SCH | 3000/4000 | Shipment Data (with first shipment) | LINE (Item 2/shipment 1) |
Shipment (second for Item 2) | SCH | 3000/4000 | Shipment Data (with second shipment) | LINE (Item 2/shipment 2) |
Shipment (third for Item 2) | SCH | 3000/4000 | Shipment Data (with third shipment) | LINE (Item 2/shipment 3) |
Shipment (fourth for Item 2) | SCH | 3000/4000 | Shipment Data (with fourth shipment) | LINE (Item 2/shipment 4) |
Note: An X12 Schedule (SCH) Segment contains Order Management Shipment data.
The following table illustrates the e-Commerce Gateway Inbound Purchase Order transaction structure with more detail.
e-Commerce Gateway POI Transaction Record | e-Commerce Gateway POI Transaction Record Data Content | Order Import Level |
---|---|---|
1000 | Schedule Header | HEADER |
2000 | Item 1: line numbers, customer item, supplier item | (Copy all item data to each of its shipments) |
2010 | Item 1: INVENTORY_ITEM_SEGMENT_1 -10 | (Copy all item data to each of its shipments) |
2020 | Item 1: INVENTORY_ITEM_SEGMENT_11 -20 | (Copy all item data to each of its shipments) |
3000 | (1st) Prices, configuration/options references | LINE (Item 1/Schedule1 data) |
4000 | (1st) Schedule1 dates, etc | LINE (Item1/Schedule1 data) |
3000 | (2nd) Prices, configuration/options references | LINE (Item1/Schedule2 data) |
4000 | (2nd) Schedule2 dates, etc | LINE (Item1/Schedule2 data) |
3000 | (3rd) Prices, configuration/options references | LINE (Item1/Schedule3 data) |
4000 | (3rd) Schedule3 dates, etc | LINE (Item1/Schedule3 data) |
2000 | Item 2: line numbers, customer item, supplier item | (Copy all item data to each of its shipments) |
2010 | Item 2: INVENTORY_ITEM_SEGMENT_1 -10 | (Copy all item data to each of its shipments) |
2020 | Item 2: INVENTORY_ITEM_SEGMENT_11 -20 | (Copy all item data to each of its shipments) |
3000 | (1st) Prices, configuration/options references | LINE (Item 2/Schedule1 data) |
4000 | (1st) Schedule1 dates, etc | LINE (Item2/Schedule1 data) |
3000 | (2nd) Prices, configuration/options references | LINE (Item 2/Schedule2 data) |
4000 | (2nd) Schedule1 dates, etc | LINE (Item2/Schedule2 data) |
2000 | Item 3: line numbers, customer item, supplier item | (Copy all item data to each of its shipments) |
2010 | Item 3: INVENTORY_ITEM_SEGMENT_1 -10 | (Copy all item data to each of its shipments) |
2020 | Item 3: INVENTORY_ITEM_SEGMENT_11 -20 | (Copy all item data to each of its shipments) |
3000 | (1st) Prices, configuration/options references | LINE (Item 3/Schedule1 data) |
4000 | (1st) Schedule1 dates, etc | LINE (Item3/Schedule1 data) |
3000 | (2nd) Prices, configuration/options references | LINE (Item 3/Schedule2 data) |
4000 | (2nd) Schedule1 dates, etc | LINE (Item3/Schedule2 data) |
Line and Shipment Records
The following table illustrates key data on records 2000-4900 that contain the line and shipment detail required by Order Import. Refer to the transaction record layout for the position of the data on the record.
Case | Record | Customer Line | Customer Item | Item-ID-Type | Inventory Item |
---|---|---|---|---|---|
3000 | Operation | Price | Quantity | UOM | |
4000 | Shipment Number | Customer Request Date | |||
1 | 2000 | 1 | ABC | CUST | XYZ |
2 | 3000 | INSERT | 10.00 | ||
3 | 4000 | 1 | 20000101 230000 | ||
4 | 3000 | INSERT | 10.00 | ||
5 | 4000 | 2 | 20000202 000000 | ||
6 | 3000 | INSERT | 10.00 | ||
7 | 4000 | 3 | 20000202 000000 | ||
8 | 2000 | 2 | WWW | ||
9 | 3000 | INSERT | 20.00 | ||
10 | 4000 | 1 | 20000101 230000 | ||
11 | 3000 | INSERT | 20.00 | ||
12 | 4000 | 2 | 20000102 220000 |
Case 1: ITEM DETAIL
Record 2000 contains the customer line number, customer item, and/or supplier item.
Data for INVENTORY_SEGMENT1 through INVENTORY_SEGMENT20 may be written on records 2010 and 2020 if needed.
The item level data on records 2000-2020 is associated with each set of 3000-4900 records below it. They are combined to write an entry into the Order Import LINE table.
Case 2 and 3: SHIPMENT DETAIL
The first set of shipment level data where price and configuration data is on record 3000, and dates are on record 4000.
Case 4 and 5: SHIPMENT DETAIL
The second set of shipment level data where price and configuration data is on record 3000, and dates are on record 4000.
Case 6 and 7: SHIPMENT DETAIL
The third set of shipment level data where price and configuration data is on record 3000, and dates are on record 4000.
Case 8: ITEM DETAIL
Details for the second line item starts IS THIS AN INCOMPLETE STATEMENT?
Case 9 and 12: SHIPMENT DETAIL
Pairs of shipment level detail for the second line item.
Order Import requires a set of Original System reference fields. These are derived by the e-Commerce Gateway transaction process if one is not found in the transaction.
The POI transaction program derives the header and line level Original System reference fields, if data is not supplied in the transaction when the transaction was created.
The Original System reference fields are used to match change orders to original orders. Thus, these values must be unique within all orders for a customer and entered with the new orders.
The same algorithm that determines this data must be used to derive the data in the new order (POI) transaction and change order (POCI) transaction process to successfully matched change orders to new orders.
Table LEVEL | Data Element | Default Content | Sample |
---|---|---|---|
Header | Original System Document Reference |
Purchase Order Number - Release, if applicable | 86420-03 |
Line | Original System Line Reference |
Customer Line Number | 001 |
Line | Original System Shipment Reference |
Current Request Data + (Ship-to) EDI Location Code | 19990308000+CHC-01 |
Higher level Original System data is propagated to the lower data levels in the transaction. For example, the derived ORIG_SYS_DOCUMENT REFERENCE at the header level is copied to the same field at the LINE and all other used levels for that transaction.
Data must be unique only within the given customer. This is illustrated in the following table.
Table Level | Data Element | Customer A | Customer B |
---|---|---|---|
Header | Original System Document Reference |
86420-03 | 86420-03 |
Line 1 (First Shipment) | Original System Document Reference (Copy from Header) |
86420-03 | 86420-03 |
Line 1 (First Shipment) | Original System Line Reference (1) |
001 | 001 |
Line 1 (First Shipment) | Original System Shipment Reference (1) |
19990308 00000+CHIC-01 | 19990308 00000+CHIC-01 |
Line 1 (Second Shipment) | Original System Document Reference (Copy from Header) |
86420-03 | 86420-03 |
Line 1 (Second Shipment) | Original System Line Reference (1) |
001 | 001 |
Line 1 (Second Shipment) | Original System Shipment Reference (2) |
19990308 00000+ATL-01 | 19990308 00000+ATL-01 |
Sold_to_Customer | 1230 | 1230 |
Original System Document Reference
Original System Document Reference is an identifier for the order within Order Management to guarantee its uniqueness within one operating unit.
Most often, it is the purchase order number, plus release number (if appropriate). A character such as the dash sign between each component improves readability.
The ORIG_SYS_DOCUMENT_REF is derived by the POI program or generated by a process such as the EDI Translator before the order is imported into the Order Import tables. It is not sent by the customer in the transaction as a field known as Original System Document Reference.
Sample Original System Document Reference
Original System Document Reference
86420
Purchase Order Number
86420
Note: The user may enter data in this field or use the purchase order number default.
The following table shows a sample original system document reference:
Original System Document Reference 86420-03 | Original System Document Reference 86420-03 |
---|---|
(Default) Purchase Order Number | (Default) Purchase Order Release Number |
86420 | 03 |
Refer to CUSTOMER_PO_NUMBER under OE_HEADERS_INTERFACE table below for more detail.
Original System Line Reference
Original System Line Reference is an identifier for the line number in the order. This field is used for matching lines and schedules in subsequent transactions to the current order in Order Management.
The ORIG_SYS_LINE_REF and the ORIG_SYS_SHIPMENT_REF are stored at the line level in the base order tables. The combination of the ORIG_SYS_LINE_REF and the ORIG_SYS_SHIPMENT_REF (see below) must be unique within the ORIG_SYS_DOCUMENT_REFERENCE specified at the header.
The ORIG_SYS_LINE is derived by the POI program or generated by a process such as the EDI Translator before the order is imported into the Order Import tables. It is not sent by the customer in the transaction as a field known as Original System Line Reference.
Sample Original System Line Reference
Original System Line Reference
001
(Default)
Customer Line Number
001
Note: The user may enter data in this field or use the customer line number default.
Original System Shipment Reference
Original System Schedule Reference is an identifier for the shipment within a line in the order. This field is used for matching the line's schedules in subsequent transactions to the current order's schedules in Order Management. Often the current request date is the value used.
ORIG_SYS_SHIPMENT_REF (shipment indicator) must be unique within the ORIG_SYS_LINE_REF (line indicator) within the purchase order that is identified by the ORIG_SYS_DOCUMENT_REF (header indicator).
The same customer request data may be entered for several ship-to locations. To guarantee uniqueness concatenate the ship-to EDI LOCATION code to the date. For example, the customer request date (in the transaction) + ship-to EDI location code would be 120000308+CHIC-02. A character such as the plus sign between each component improves readability.
The ORIG_SYS_LINE is derived by the POI program or generated by a process such as the EDI Translator before the order is imported into the Order Import tables. The customer does not send it in the transaction, as the Original System Shipment Reference field.
Original System Shipment Reference 19990308 000000+CHIC-01 | Original System Shipment Reference 19990308 000000+CHIC-01 |
---|---|
(Default) Current Request Date (includes time) |
(Default) Ship-To EDI_LOCATION CODE |
19990308 | CHIC-01 |
Note: The user may enter data in this field or use the default that is the CUSTOMER_REQUEST_SYSDATE+ (Ship-to) EDI_LOCATION CODE.
(Use a plus sign between these fields to improve readability.)
If your trading partner can retain unique shipment numbers to identify the shipment level (shipment/schedule dates, ship-to locations) data in their purchasing application, the shipment number may be copied into the ORIG_SYS_SHIPMENT_REF field in the transaction file.
If you maintain the same coding convention for the three original reference fields for both the inbound purchase order and the inbound purchase order change, the change order process can match the order data in the base Order Management tables. It is assumed that you will only modify the shipment records that changed, and add shipment records that were not already present.
In another scenario, the trading partner may send all the shipment records, even shipment records with no changes for your review, if you cannot accurately match the shipment records from the purchasing application.
Sample Original System Shipment Reference
Original System Shipment Reference
6
Shipment Number
(You copied from the transaction)
6
The following table is a sample of all Original System Data using Shipment Numbers:
LEVEL | Data Element | Content | Sample | Note |
---|---|---|---|---|
Header | Original System Document Reference |
Purchase Order Number (- Release, if applicable) |
86420-03 | the default |
Line | Original System Line Reference |
Customer Line Number | 001 | the default |
Line | Original System Shipment Reference |
Shipment Number | 6 | copied into the transaction |
Order in base Order tables Document Reference (PO) |
Order in base Order tables Line Reference (line number) |
Order in base Order tables Shipment Reference (shipment number) |
Change Order Data in Order Import Document Reference (PO) |
Change Order Data in Order Import Line Reference (line number) |
Change Order Data in Order Import Shipment Reference (shipment number) |
Note on Matching change order to order in base order tables |
---|---|---|---|---|---|---|
86420-03 | 001 | 1 | 86420-03 | 001 | 1 | Change order data match to Line 001, shipment 1 in the base order |
86420-03 | 001 | 2 | No change order data found for Line 001, shipment 2. Do not update the record in the base order. | |||
86420-03 | 001 | 3 | 86420-03 | 001 | 3 | Change order data match to Line 001, shipment 3 in the base order |
86420-03 | 001 | 4 | Change order data not match to base order line 001, shipment number 4. Add line 1, shipment number 4. |
|||
86420-03 | 001 | 5 | Change order data not match to base order line 001, shipment number 5. Add line 2, shipment number 1. |
|||
86420-03 | 002 | 1 | Change order data not match to base order line 002, shipment number 1. Add line 2, shipment number 1. |
|||
86420-03 | 002 | 2 | Change order data not match to base order line 002, shipment number 2. Add line 2, shipment number 2. |
If the customer does not send a unique, non-changeable shipment level identifier that is assigned to the Original System Shipment Reference, such as the Shipment Number, then any subsequent change order processing will require human intervention to do the record matching and determine what shipment data will be updated.
ORIG_SYS_CREDIT_REF (Original System Credit Reference),
ORIG_SYS_DISCOUNT_REF (Original System Discount Reference),
ORIG_SYS_LOTSERIAL_REF (Original System Lot-Serial Reference),
ORIG_SYS_RESERVATION_REF (Original System Reservation Reference)
DEFAULT:
There are no defaults to the Credit, Discount, Lot-Serial, or Reservation reference fields. You need to supply this data in the record if you are using those records.
*When testing this transaction be sure to change all values of the Original System References for each test, otherwise, the transaction will fail in the Order Import validation due to duplicate entries.
CUSTOMER_SHIPMENT_NUMBER is a counter for the set of shipment data within an item that is identified by the same CUSTOMER_LINE_NUMBER. The customer in the transaction sends this number. It can be used to match change order shipment records to the base order line record if you copy it to the ORIG_SYS_SHIPMENT_REF (not use the default). It has no purpose if the inbound purchase order process generates this number. Order Management has its own shipment number counter that is used in Order Import.
Sample X12 Segment | X12 Data Element | Order Import Data Elment for this Customer-Generated Data | Order Import Data |
---|---|---|---|
First PO1 | Assigned Identification on PO1 | CUSTOMER_LINE_NUMBER | 001 |
First SCH within last PO1 |
Assigned Identification on SCH | CUSTOMER_SHIPMENT NUMBER | 1 |
Second SCH within last PO1 |
Assigned Identification on SCH | CUSTOMER_SHIPMENT NUMBER | 2 |
Third SCH within last PO1 |
Assigned Identification on SCH | CUSTOMER_SHIPMENT NUMBER | 3 |
Second PO1 | Assigned Identification on PO1 | CUSTOMER_LINE_NUMBER | 002 |
First SCH within last PO1 |
Assigned Identification on SCH | CUSTOMER_SHIPMENT NUMBER | 1 |
Second SCH within last PO1 |
Assigned Identification on SCH | CUSTOMER_SHIPMENT NUMBER | 2 |
The e-Commerce Gateway allows only a maximum length of 1024 characters per field. To import text more than 1024 characters long, write a custom procedure to concatenate several fields to import them to the longer field allowed in the open interface tables.
There are several flags in the interface tables of Order Management that affect the Order Import processing. Valid values of these flags are Y, N, and null. Null means different things depending on the particular flag. The Order Import process sets the defaults if a code is not found in the transaction.
Most of the flag data elements are not activated on the transaction, since the defaults are usually adequate. Their data elements are listed at the end of the activated data elements in the Transaction Definition form.
Flag | Description | Default | Possible Values |
---|---|---|---|
Booked | This flag can be used instead of populating the oe_actions_interface table with the BOOK_ORDER action, and it has the same effect. | N/A | Y, N, Null |
Cancelled | The Cancelled flag is typically used to indicate that the line or order being imported should be imported in a Cancelled state. | N, or Null | N, Null |
Closed | The Closed flag is typically used to indicate that the line or order being imported should be imported in a Closed state. | N, or Null | N, Null |
Error | The Order Import process sets on the error flag whenever an error is encountered during the validation process. | N, or Null | N, Null |
Ready | The ready flag indicates that the record will be processed in the Order Import Process. | Y, or Null | Y, N, Null |
Rejected | Rejected orders or rejected order lines are DELETED during the next run of the Order Import program. | N, or Null | N, Null |
There is a validate mode parameter in Concurrent Manager for running Order Import. This parameter tells the process to only validate the record, and not to process valid records any further. Base Order Management tables will not have records inserted, updated, or deleted.
READY_FLAG | (Only) Validate Parameter | Processing |
---|---|---|
N | Y | Record is not processed |
N | N | Record is not processed |
Y or NULL | Y | Process to Validate Only |
Y or NULL | N | Process to Insert/Update/Delete in Base Table |
The Order Management Order Import Open Interface is used by the Inbound Purchase Order transaction. This validates the incoming data entered into the Order Management Open Interface tables by the Oracle e-Commerce Gateway import program or any other loading program.
The data for the Order Import tables have the following sources:
Data in the transaction from the trading partner or determined by the EDI Translator.
Data derived or hard codes by the POI Program given the trading partner or the presence of data in the transaction.
Data derived by the Order Import process when it calls APIs to the Oracle tables.
The following is a list of the Order Management Open Interface required fields or other notations about the data elements. These fields are required for the Order Management Open Interface Import program to successfully process and move the data from the Order Management Open Interface tables into the Oracle Order Management base application tables.
Required fields noted as derived or hard coded do not require a value in the transaction on the transaction interface file since the values are determined by the Oracle e-Commerce Gateway process.
Refer to the Oracle Order Management User's Guide for details.
The following table shows sample columns from OE_HEADERS_INTERFACE table:
Oracle E-Business Suite Column Name for Fields | Oracle e-Commerce Gateway Column Name | Hardcoded/Derived | Record Number | Position Number |
---|---|---|---|---|
OPERATION_CODE | OPERATION_CODE(_EXT1) | If Null, default ot "INSERT" | 1000 | 030/040 |
CUSTOMER_PO_NUMBER | CUSTOMER_PO_NUMBER | (Required by Order Import) | 1000 | 050 |
ORDER_TYPE | ORDER_TYPE(_EXT1) | This can be defaulted. Values must match those defined in Order Management. Use code conversion for the standard code in the transaction if necessary. |
1000 | 080/090 |
ORDER_CATEGORY | ORDER_CATEGORY(_EXT1) | If Null, default to "ORDER" | 1000 | 100/110 |
ORIG_SYS_DOCUMENT_REF | ORIG_SYS_DOCUMENT_REF | If Null, derived | 1000 | 120 |
ORDER_DATE_TYPE | ORDER_DATE_TYPE(_EXT1) | If Null, default to "SHIP" so the dates are interpreted as ship dates and not delivery dates. | 1000 | 130/140 |
CUSTOMER_NAME | CUSTOMER_NAME (not required) |
Derived given the EDI Location code and Translator code on Control Record 0010. Data on Record 1400 will be not be imported unless the customer was not found. |
1400 | 010 |
CUSTOMER_NUMBER | CUSTOMER_NUMBER (not required) |
Derived given the EDI Location code and Translator code on Control Record 0010. Data on Record 1400 will not be imported unless the customer was not found. |
1400 | 020 |
(All Sold to Address Data) | SOLD TO Address | Derived given the EDI Location code and Translator code on Control Record 0010. | 1410 | |
CREATED_BY | CREATED_BY | Derived from the Requester ID for the concurrent manager request that. | ||
CREATION_DATE | CREATION_DATE | Set to SYSDATE | ||
LAST_UPDATE_DATE | LAST_UPDATE_DATE | Set to SYSDATE | ||
LAST_UPDATED_BY | LAST_UPDATED_BY | Derived from the Requester ID for the concurrent manager request that.Please check | ||
ORDER_SOURCE_ID | ORDER_SOURCE_ID | Hard code: 6 meaning EDI | ||
SOLD_TO_ORD_ID | SOLD_TO_ORD_ID | Derived during the Trading Partner lookup given the EDI Location code and Translator code on Control Record 0010 |
Sample columns from the OE_LINES_INTERFACES table:
Oracle E-Business Suite Column Name for Fields | Oracle e-Commerce Gateway Column Name | Hardcoded/Derived | Record Number | Position Number |
---|---|---|---|---|
ORIG_SYS_DOCUMENT_REF | ORIG_SYS_DOCUMENT_REF | If Null, derived | 2000 | 030 |
ORIG_SYS_LINE_REF | ORIG_SYS_LINE_REF | If Null, derived | 2000 | 040 |
ORIG_SYS_SHIPMENT_REF | ORIG_SYS_SHIPMENT_REF | If Null, derived | 2000 | 050 |
OPERATION_CODE | OPERATION_CODE(_EXT1) | If Null, default to 'INSERT' | 2000 | 060/070 |
TOP_LINE_MODEL_REF | TOP_LINE_MODEL_REF | (See Configurations above) | 2000 | 080 |
LINE_TO_LINE_REF | LINE_TO_LINE_REF | (See Configurations above) | 2000 | 090 |
MODEL_GROUP_NUMBER | MODEL_GROUP_NUMBER | (See Configurations above) | 2000 | 100 |
OPTION_NUMBER | OPTION_NUMBER | (See Configurations above) | 2000 | 110 |
COMPONENT_CODE | COMPONENT_CODE | (See Configurations above) | 2000 | 120 |
SORT_ORDER | SORT_ORDER | (See Configurations above) | 2000 | 130 |
LINE_NUMBER | LINE_NUMBER | If Null, derived | 2010 | 020 |
CUSTOMER_LINE_NUMBER | CUSTOMER_LINE_NUMBER | If Null, set to LINE_NUMBER. | 2010 | 030 |
ITEM_TYPE_CODE | ITEM_TYPE_CODE | If Null, default to STANDARD | 2010 | 080/090 |
CALCULATE_PRICE_FLAG | CALCULATE_PRICE_FLAG | If Null, default to 'Y' for yes. | ||
CREATED_BY | CREATED_BY | Derived | ||
CREATION_DATE | CREATION_DATE | Set to SYSDATE | ||
DELIVERY_ID | DELIVERY_ID | Derived | ||
LAST_UPDATE_DATE | LAST_UPDATE_DATE | Set to SYSDATE | ||
LAST_UPDATED_BY | LAST_UPDATED_BY | Derived | ||
ORDER_SOURCE_ID | ORDER_SOURCE_ID | Hard code: 6 meaning EDI | ||
READY_FLAG | READY_FLAG | Set to 'Y' for yes. | ||
Shipment Dates | 3000 | many | ||
CUSTOMER_SHIPMENT_ NUMBER |
CUSTOMER_SHIPMENT_ NUMBER |
Increment by one for each set of shipment data within an item that is identified by the same CUSTOMER_LINE_NUMBER. | 4000 | 040 |
Ship to Location (Line Level) | 3800 | many |
Note: Column names denoted with (_EXT1) indicates that it can have code conversion for that data element. Either the internal value or the external value will be passed to the Order Import tables following the logic of the code conversion process. Refer to Code Conversion for details.
The following table levels of data are included in the transaction file at both the Order header and Order line level. Additional Original System reference data is required at the Order line level. Data is only required if these tables are used.
OE_CREDITS_INTERFACE
OE_PRICE_ADJS_INTERFACE
OE_RESERVTNS_INTERFACE
OE_ACTIONS_INTERFACE
OE_PRICE_ATTS_INTERFACE
The following table level of data is included in the transaction file at the Order line level only. Data is only required if the table is used.
OE_LOTSERIALS_INTERFACE
Leave at least one column activated to appear in the transaction interface file.
Oracle E-Business Suite Column Name for Required Fields | Oracle e-Commerce Gateway Column Name | Hardcoded/Derived | Record Number | Position Number |
---|---|---|---|---|
ORIG_SYS_DOCUMENT_REF | ORIG_SYS_DOCUMENT_REF | Required | 1700 | 010 |
ORIG_SYS_CREDIT_REF | ORIG_SYS_ CREDIT _REF | Required | 1700 | 030 |
OPERATION_CODE | OPERATION_CODE(_EXT1) | If Null, default to "INSERT" | 1700 | 040/050 |
ORDER_SOURCE_ID | ORDER_SOURCE_ID | Hard code: 6 meaning EDI | ||
CREATED_BY | CREATED_BY | Derived | ||
LAST_UPDATED_BY | LAST_UPDATED_BY | Derived | ||
CREATION_DATE | CREATION_DATE | Set to SYSDATE | ||
LAST_UPDATE_DATE | LAST_UPDATE_DATE | Set to SYSDATE |
The following table shows the header level records for OE_PROCE_ADJS_INTERFACE.
Oracle E-Business Suite Column Name for Required Fields | Oracle e-Commerce Gateway Column Name | Hardcoded/Derived | Record Number | Position Number |
---|---|---|---|---|
ORIG_SYS_DOCUMENT_REF | ORIG_SYS_DOCUMENT_REF | Required | 1800 | 010 |
ORIG_SYS_DISOUNT_REF | ORIG_SYS_ DISOUNT _REF | Required | 1800 | 030 |
OPERATION_CODE | OPERATION_CODE(_EXT1) | If Null, default to "INSERT" | 1800 | 040/050 |
ORDER_SOURCE_ID | ORDER_SOURCE_ID | Hard code: 6 meaning EDI | ||
LAST_UPDATED_BY | LAST_UPDATED_BY | Derived | ||
LAST_UPDATE_DATE | LAST_UPDATE_DATE | Set to SYSDATE | ||
CREATION_DATE | CREATION_DATE | Set to SYSDATE | ||
CREATED_BY | CREATED_BY | Derived |
The following table shows the header level records for OE_RESERVTNS_INTERFACE.
Oracle E-Business Suite Column Name for Required Fields | Oracle e-Commerce Gateway Column Name | Hardcoded/Derived | Record Number | Position Number |
---|---|---|---|---|
ORIG_SYS_DOCUMENT_REF | ORIG_SYS_DOCUMENT_REF | Required | 1900 | 010 |
ORIG_SYS_RESERVATION_REF | ORIG_SYS_RESERVATION_REF | Required | 1900 | 030 |
OPERATION_CODE | OPERATION_CODE(_EXT1) | If Null, default to "INSERT" | 1900 | 040/050 |
ORDER_SOURCE_ID | ORDER_SOURCE_ID | Hard code: 6 meaning EDI | ||
ORIG_SYS_DOCUMENT_REF | ORIG_SYS_DOCUMENT_REF | Required | 1950 | 010 |
OPERATION_CODE | OPERATION_CODE(_EXT1) | If Null, default to "INSERT" | 1950 | 030/040 |
ORDER_SOURCE_ID | ORDER_SOURCE_ID | Hard code: 6 meaning EDI |
The following table shows the line level records for OE_CREDITS_INTERFACE.
Oracle E-Business Suite Column Name for Required Fields | Oracle e-Commerce Gateway Column Name | Hardcoded/Derived | Record Number | Position Number |
---|---|---|---|---|
ORIG_SYS_DOCUMENT_REF | ORIG_SYS_DOCUMENT_REF | Required | 5000 | 010 |
ORIG_SYS_LINE_REF | ORIG_SYS_ LINE _REF | Required | 5000 | 030 |
ORIG_SYS_SHIPMENT_REF | ORIG_SYS_SHIPMENT_REF | Required | 5000 | 040 |
ORIG_SYS_CREDIT_REF | ORIG_SYS_CREDIT_REF | Required | 5000 | 050 |
OPERATION_CODE | OPERATION_CODE(_EXT1) | If Null, default to "INSERT" | 5000 | 060/070 |
ORDER_SOURCE_ID | ORDER_SOURCE_ID | Hard code: 6 meaning EDI | ||
CREATED_BY | CREATED_BY | Derived | ||
LAST_UPDATED_BY | LAST_UPDATED_BY | Derived | ||
CREATION_DATE | CREATION_DATE | Set to SYSDATE | ||
LAST_UPDATE_DATE | LAST_UPDATE_DATE | Set to SYSDATE |
The following table shows the line level records for OE_PRICE_ADJS_INTERFACE
Oracle E-Business Suite Column Name for Required Fields | Oracle e-Commerce Gateway Column Name | Hardcoded/Derived | Record Number | Position Number |
---|---|---|---|---|
ORIG_SYS_DOCUMENT_REF | ORIG_SYS_DOCUMENT_REF | Required | 7000 | 010 |
ORIG_SYS_LINE_REF | ORIG_SYS_ LINE _REF | Required | 6000 | 030 |
ORIG_SYS_SHIPMENT_REF | ORIG_SYS_SHIPMENT_REF | Required | 6000 | 040 |
ORIG_SYS_DISCOUNT_REF | ORIG_SYS_DISCOUNT_REF | Required | 6000 | 050 |
OPERATION_CODE | OPERATION_CODE(_EXT1) | If Null, default to "INSERT" | 6000 | 060/070 |
ORDER_SOURCE_ID | ORDER_SOURCE_ID | Hard code: 6 meaning EDI | ||
LAST_UPDATED_BY | LAST_UPDATED_BY | Derived | ||
LAST_UPDATE_DATE | LAST_UPDATE_DATE | Set to SYSDATE | ||
CREATION_DATE | CREATION_DATE | Set to SYSDATE | ||
CREATED_BY | CREATED_BY | Derived |
The following table shows the line level records for OE_RESERVTNS_INTERFACE
Oracle E-Business Suite Column Name for Required Fields | Oracle e-Commerce Gateway Column Name | Hardcoded/Derived | Record Number | Position Number |
---|---|---|---|---|
ORIG_SYS_DOCUMENT_REF | ORIG_SYS_DOCUMENT_REF | Required | 7000 | 010 |
ORIG_SYS_LINE_REF | ORIG_SYS_ LINE _REF | Required | 7000 | 030 |
ORIG_SYS_SHIPMENT_REF | ORIG_SYS_SHIPMENT_REF | Required | 7000 | 040 |
ORIG_SYS_RESERVATION_REF | ORIG_SYS_RESERVATION_REF | Required | 7000 | 050 |
OPERATION_CODE | OPERATION_CODE(_EXT1) | If Null, default to "INSERT" | 7000 | 060/070 |
ORDER_SOURCE_ID | ORDER_SOURCE_ID | Hard code: 6 meaning EDI |
The following table shows the line level records for OE_ACTIONS_INTERFACE
Oracle E-Business Suite Column Name for Required Fields | Oracle e-Commerce Gateway Column Name | Hardcoded/Derived | Record Number | Position Number |
---|---|---|---|---|
ORIG_SYS_DOCUMENT_REF | ORIG_SYS_DOCUMENT_REF | Required | 8000 | 010 |
ORIG_SYS_LINE_REF | ORIG_SYS_ LINE _REF | Required | 8000 | 030 |
ORIG_SYS_SHIPMENT_REF | ORIG_SYS_SHIPMENT_REF | Required | 8000 | 040 |
OPERATION_CODE | OPERATION_CODE(_EXT1) | If Null, default to "INSERT" | 8000 | 050/060 |
ORDER_SOURCE_ID | ORDER_SOURCE_ID | Hard code: 6 meaning EDI |
The following table shows the line level records for OE_LOTSERIALS_INTERFACE
Oracle E-Business Suite Column Name for Required Fields | Oracle e-Commerce Gateway Column Name | Hardcoded/Derived | Record Number | Position Number |
---|---|---|---|---|
ORIG_SYS_DOCUMENT_REF | ORIG_SYS_DOCUMENT_REF | Required | 9000 | 010 |
ORIG_SYS_LINE_REF | ORIG_SYS_ LINE _REF | Required | 9000 | 030 |
ORIG_SYS_SHIPMENT_REF | ORIG_SYS_SHIPMENT_REF | Required | 9000 | 040 |
ORIG_SYS_LOTSERIAL_REF | ORIG_SYS_LOTSERIAL_REF | Required | 9000 | 050 |
ORDER_SOURCE_ID | ORDER_SOURCE_ID | Hard code: 6 meaning EDI | ||
LAST_UPDATED_BY | LAST_UPDATED_BY | Derived | ||
LAST_UPDATE_DATE | LAST_UPDATE_DATE | Set to SYSDATE | ||
CREATION_DATE | CREATION_DATE | Set to SYSDATE | ||
CREATED_BY | CREATED_BY | Derived |
This column represents the purchase order number for the customer purchase order being imported into Oracle Order Management.
The number need not be unique for the customer.
If the purchase order is a release, the release number may be concatenated to the purchase order number separated by a dash, for example, PO1234-01 for purchase order PO1234 with release 01.
If you concatenate the customer's purchase order number and release number, you may impact the ORIG_SYS_DOCUMENT_REFERENCE. See the illustration below.
Purchase Order Number from Customer's Purchasing Application |
Purchase Order Release Number from Customer's Purchasing Application |
---|---|
86420 | 03 |
Your Modified Purchase Order Number moved into CUSTOMER_PO_NUMBER |
---|
86420 -03 |
Note: You concatenated the fields. |
Purchase Order Number from Customer's Purchasing Application |
Purchase Order Release Number from Customer's Purchasing Application |
---|---|
86420 | 03 |
This concatenation needs to be reversed in purchase order acknowledgments so customers see the two data elements separately. You may use flexfields to store the data elements separately to facilitate the purchase order acknowledgment transaction that requires two separate components.
OPERATION_CODE
This code identifies that the transaction is to be inserted, changed, or deleted. For new orders, the appropriate code is INSERT. Code conversion is permitted for this data element if a code is placed in the OPERATION_CODE_EXT1 field and the code is set up through the three code conversion forms in the e-Commerce Gateway. The valid values are INSERT, UPDATE, or DELETE.
DEFAULT:
The code is set to INSERT for this transaction by the e-Commerce Gateway if a code is not provided in the transaction or not derived through code conversion. This code is written to all the Order Import tables for the given transaction.
ORDER_CATEGORY
This code identifies the category of the order as defined in Order Management. The valid values are ORDER, MIXED, or RETURN.
DEFAULT:
The code is set to ORDER.
ORDER_DATE_TYPE
The expected type of date is defaulted in Order Import to the value set up for the customer or customer site. The value will apply to all dates in the transaction. You may override the customer default by entering one of the following codes in the transaction or derive it through code conversion. For code conversion, you can copy the date qualifier found in the standard transaction to the e-Commerce Gateway transaction and set up code conversion to derive one of the following codes.
ARRIVAL
SHIP
ORDER_SOURCE_ID
This code identifies that the transaction was loaded into Order Import via the e-Commerce Gateway.
DEFAULT:
The code is set to '6' meaning EDI.
ORDER_TYPE
This code identifies the type of order such as a standard order. You define these codes in Order Management.
There is no default. You must enter a value.
READY_FLAG
This flag indicates if the transaction is ready to be moved to the Order Management base order tables if all validation is passed. If you set it to 'N,' even the valid order would remain in the Order Import tables until the flag is set to 'Y' or the transaction is deleted from the Order Import tables.
SOLD_TO_ORG_ID
(Not activated on the transaction interface file.)
This code identifies that the customer for all the ship-to sites in the transaction. It is derived given the EDI Location Code and Translator on the Control Record 0010. Refer to the Trading Partner Section in the Oracle e-Commerce Gateway Implementation Guide for details
ORIG_SYS_DOCUMENT_REF
See Definition above.
CREATED_BY
This code identifies who loaded this transaction into the Order Import tables. The code is set to the Requester ID associated with the concurrent manager request that processed this transaction.
CREATION_DATE
This is the date that the e-Commerce Gateway loaded the transaction into the Order Import table entries. This date is set to the system date.
LAST_UPDATE_DATE
This date is equal to the CREATION_DATE for this transaction. This date is set to the system date.
LAST_UPDATED_BY
This code is equal to the CREATED_BY code for this transaction.
The following data elements must be entered for Configured items to show the relationship of line items in the file.
TOP_LINE_MODEL_REF
LINE_TO_LINE_REF
MODEL_GROUP_NUMBER
OPTION_NUMBER
COMPONENT_CODE
SORT_ORDER
CALCULATE_PRICE_FLAG
This flag indicates to Order Import to calculate a price (flag is Y) or not calculate a price (flag is N).
DEFAULT:
The code is set to 'Y' meaning Order Import will calculate a price even if one is found in the transactions. The calculated price and the price in the transaction will be compared. There is an error if there is a discrepancy.
CUSTOMER_LINE_NUMBER
This is the customer's line number as defined in their procurement application. There is no default. This reference is passed from the transactions.
LINE_NUMBER
This is the order line number as calculated by the e-Commerce Gateway as required by Order Import. Do not enter data into this field for new orders.
DEFAULT:
Increment the line counter by one for each LINE level record that is read.
OPERATION_CODE
See OPERATION_CODE under the OE_HEADERS_INTERFACE table above.
ORDER_SOURCE_ID
This code identifies that the transaction was loaded into Order Import via the e-Commerce Gateway.
DEFAULT:
The code is set to '6' meaning EDI.
Item Data:
CUSTOMER_ITEM_NAME
This column represents the customer's item number for the buyer item as defined in their purchasing application.
CUSTOMER_ITEM_REVISION
This column represents the customer's item's revision level. This field is for display only. This field is not used in the Inventory tables to look up the supplier item.
INVENTORY_ITEM
This column represents the supplier's item number corresponding to the buyer item as defined in Oracle Inventory.
ITEM_REVISION
This column represents the supplier's item revision level. This field is for display only. This field is not used in the Inventory tables to look up the supplier item.
Original System Reference Data:
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF,
ORIG_SYS_SHIPMENT_REF
See Definition above.
CREATED_BY
This code identifies who loaded this transaction into the Order Import tables. The code is set to the Requester ID associated with the concurrent manager request that processed this transaction.
CREATION_DATE
This is the date that the e-Commerce Gateway loaded the transaction into the Order Import table entries. This date is set to the system date.
LAST_UPDATE_DATE
This date is equal to the CREATION_DATE for this transaction. This date is set to the system date.
LAST_UPDATED_BY
This code is equal to the CREATED_BY code for this transaction.
OE_CREDITS_INTERFACE Table
If you do not plan to use this feature, you should deactivate the records that are associated to this table. If you do not deactivate the records, errors will occur in the Order Import validation, because the records will be incomplete. Records are activated and deactivated in the Transaction Definition window in the e-Commerce Gateway.
ORDER_SOURCE_ID
This code identifies that the transaction was loaded into Order Import via the e-Commerce Gateway.
DEFAULT:
The code is set to ‘6' meaning EDI.
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF,
ORIG_SYS_SHIPMENT_REF
See Definition above.
ORIG_SYS_CREDITS_REF
CREATED_BY
This code identifies who loaded this transaction into the Order Import tables. The code is set to the Requester ID associated with the concurrent manager request that processed this transaction.
CREATION_DATE
This is the date that the e-Commerce Gateway loaded the transaction into the Order Import table entries. This date is set to the system date.
LAST_UPDATE_DATE
This date is equal to the CREATION_DATE for this transaction. This date is set to the system date.
LAST_UPDATED_BY
This code is equal to the CREATED_BY code for this transaction.
If you do not plan to use this feature, you should deactivate the records that are associated to this table. If you do not deactivate the records, errors will occur in the Order Import validation, because the records will be incomplete.
ORDER_SOURCE_ID
This code identifies that the transaction was loaded into Order Import via the e-Commerce Gateway.
DEFAULT:
The code is set to ‘6' meaning EDI.
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF,
ORIG_SYS_SHIPMENT_REF
See Definition above.
ORIG_SYS_DISCOUNT_REF
CREATED_BY
This code identifies who loaded this transaction into the Order Import tables. The code is set to the Requester ID associated with the concurrent manager request that processed this transaction.
CREATION_DATE
This is the date that the e-Commerce Gateway loaded the transaction into the Order Import table entries. This date is set to the system date.
LAST_UPDATE_DATE
This date is equal to the CREATION_DATE for this transaction. This date is set to the system date.
LAST_UPDATED_BY
This code is equal to the CREATED_BY code for this transaction.
OE_LOTSERIALS_INTERFACE Table
If you do not plan to use this feature, you should deactivate the records that are associated to this table. If you do not deactivate the records, errors will occur in the Order Import validation, because the records will be incomplete.
ORDER_SOURCE_ID
This code identifies that the transaction was loaded into Order Import via the e-Commerce Gateway.
DEFAULT:
The code is set to '6' meaning EDI.
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF,
ORIG_SYS_SHIPMENT_REF
See Definition above.
ORIG_SYS_LOTSERIAL_REF
CREATED_BY
This code identifies who loaded this transaction into the Order Import tables. The code is set to the Requester ID associated with the concurrent manager request that processed this transaction.
CREATION_DATE
This is the date that the e-Commerce Gateway loaded the transaction into the Order Import table entries. This date is set to the system date.
LAST_UPDATE_DATE
This date is equal to the CREATION_DATE for this transaction. This date is set to the system date.
LAST_UPDATED_BY
This code is equal to the CREATED_BY code for this transaction.
If you do not plan to use this feature, you should deactivate the records that are associated to this table. If you do not deactivate the records, errors will occur in the Order Import validation, because the records will be incomplete.
ORDER_SOURCE_ID
This code identifies that the transaction was loaded into Order Import via the e-Commerce Gateway.
DEFAULT:
The code is set to '6' meaning EDI.
Original System Reference Data
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF,
ORIG_SYS_SHIPMENT_REF
See Definition above.
ORIG_SYS_RESERVATION_REF
CREATED_BY
This code identifies who loaded this transaction into the Order Import tables. The code is set to the Requester ID associated with the concurrent manager request that processed this transaction.
CREATION_DATE
This is the date that the e-Commerce Gateway loaded the transaction into the Order Import table entries. This date is set to the system date.
LAST_UPDATE_DATE
This date is equal to the CREATION_DATE for this transaction. This date is set to the system date.
LAST_UPDATED_BY
This code is equal to the CREATED_BY code for this transaction.
If you do not plan to use this feature, you should deactivate the records that are associated to this table. If you do not deactivate the records, errors will occur in the Order Import validation, because the records will be incomplete.
ORDER_SOURCE_ID
This code identifies that the transaction was loaded into Order Import via the e-Commerce Gateway.
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF,
ORIG_SYS_SHIPMENT_REF
See Definition above.
CREATED_BY
This code identifies who loaded this transaction into the Order Import tables. The code is set to the Requester ID associated with the concurrent manager request that processed this transaction.
CREATION_DATE
This is the date that the e-Commerce Gateway loaded the transaction into the Order Import table entries. This date is set to the system date.
LAST_UPDATE_DATE
This date is equal to the CREATION_DATE for this transaction. This date is set to the system date.
LAST_UPDATED_BY
This code is equal to the CREATED_BY code for this transaction.
At the completion of the Order Management Open Interface Order Import program, the orders with exception can be viewed in the Order Import window.
Refer to the Oracle Order Management User's Guide for details.
There are three ways to resolve Order Management Open Interface exceptions as follows:
Using the information given by the View Staging Documents window in the e-Commerce Gateway, correct the set up data in Oracle E-Business Suite.
Correct erroneous entries in the Order Management Order Import window.
Request the customer to send a corrected transaction.
If you chose to update Oracle E-Business Suite data or change the erroneous entries using the Order Import window in Order Management, you can resubmit the Order Management Open Interface Order Import process to revalidate the transaction.
If you chose to have the customer send a corrected transaction, you must first delete the rejected Order data in the Order Management Open Interface tables using the Order Import window and then re-import the updated transaction using the Oracle e-Commerce Gateway.
Item cross-referencing and validation are not performed in the e-Commerce Gateway. The e-Commerce Gateway writes data to the Order Import tables. Order Import calls the Process Order API that then calls Oracle Inventory's item cross reference APIs. The API is given the customer item or generic item (and a cross reference type to identify which type it is), and optionally the vendor item to determine the following:
Derive the internal supplier's inventory item given the customer item or generic inventory item, e.g., customer item, UPC, ISBN, or EAN number.
If both the customer/generic items and the supplier inventory item are sent in the transaction, compare the supplier item in the transaction to the derived internal supplier inventory item to be equal. If the supplier item in the transaction and the derived vendor inventory item do not equal, display an error and use the derived internal supplier inventory item in the order. The discrepancy should be discussed with the customer to determine the accurate supplier time and have the customer update their tables if necessary.
If only the supplier inventory item is sent, validate it.
Three Oracle Inventory tables contain various item data: customer item cross reference data, generic inventory items cross reference data, and the (internal) supplier inventory items. The first two tables will be searched to cross reference the items in the transaction to the internal inventory item.
Define all the cross-reference items and the internal inventory item in Oracle Inventory before running the e-Commerce Gateway inbound purchase order process and Order Import.
Table | Content | Navigation |
---|---|---|
Customer Cross Reference table: MTL_CUSTOMER_ITEM_XREFS MTL_CUSTOMER_ITEMS |
Customer-specific items to cross reference to internal supplier item | In Oracle Inventory: Items > Customer Items > Customer Items |
Inventory Item Cross Reference (generic) table: MTL_CROSS_REFERENCES |
Generic inventory items such as UPC codes, ISBN codes, and EAN codes to cross reference to internal supplier item. | In Oracle Inventory: Items > Cross Reference (Click on the Assign button to add cross references to the cross reference type selected.) |
Vendor's Inventory Item table: MTL_SYSTEM_ITEMS |
Internal vendor inventory items | In Oracle Inventory: Items > Master Items |
Cross Reference Types qualify the item that was entered. The corresponding field in the transaction is the CUSTOMER_ITEM_ID_TYPE, is explained below.
The Customer Item Cross Reference table has codes CUST and INT seeded by Oracle Inventory. One of these codes can be written to the Order Import table if you wish to use the Customer Item Cross Reference table during item cross-referencing.
Seeded Cross-Reference Types for Customer Items | Meaning | Use |
---|---|---|
CUST | Customer-Specific Number | Identify the item as a customer defined item. |
INT | Internal Item | The item is recognized as the internal supplier's inventory item, but it is also defined in the customer cross reference table so the item can also be displayed in the Ordered Item field in the order. |
The user can define many different cross reference type codes for generic inventory items while setting up the item cross reference types and their names. The types are stored in the MTL_CROSS_REFERENCE_TYPES table. The corresponding field in the transaction is the CUSTOMER_ITEM_ID_TYPE.
One of the user defined codes can be written to the Order Import table if you wish to use the Inventory Item Cross Reference table during item cross referencing; no values are seeded by Oracle Inventory.
The following table has samples of the CUSTOMER_ITEM_ID_TYPE that may be defined by the user.
Sample of User-Defined Cross-Reference Types for Inventory Items | Meaning |
---|---|
UPC | Uniform Product Code |
EAN | European Article Number (Use any EAN format that you choose) |
ISBN | International Standard Book Number |
Refer the Oracle Inventory User's Guide for details on how to set up the various items.
There are three essential columns in the transaction in record 2000. Either one or both item fields may be populated. The customer must decide if the customer item or generic item is placed in the e-Commerce Gateway transaction since only one field is in the transaction record to hold either field.
Supplier Item (INVENTORY_ITEM)
The supplier's inventory item number as stored in the customer's application and found in the transaction. It should be an item defined as an inventory item in Oracle Inventory since it validated against that table.
Customer Item (CUSTOMER_ITEM_NAME)
This field contains the customer item or a generic item as defined in the customer's inventory application.
Item Type (CUSTOMER_ITEM_ID_TYPE)
This field contains a valid qualifier that identifies the type of item placed in CUSTOMER_ITEM_NAME.
The following table has samples of the CUSTOMER_ITEM_ID_TYPE and its interpretation. The column Sample CUSTOMER_ITEM_ID_TYPE is as it is defined in Oracle Inventory, in record 2000, position 70 (or 80, depending on code conversion). The column Intepretation of CUSTOMER_ITEM_NAME comes from record 2000, position 50.
CUSTOMER_ITEM_ID_TYPE | Sample CUSTOMER_ITEM_ID_TYPE | Interpretation of CUSTOMER_ITEM_NAME | Table to which the search will be directed |
---|---|---|---|
Seeded Code | CUST | Customer-Specific item | Customer Item Cross-Reference Table |
Seeded Code | INT | Internal Item in the Customer Item Cross-Reference table | Customer Item Cross-Reference Table |
User Defined Code | UPC | UPC code | Inventory Item Cross-Reference table |
User Defined Code | EAN | EAN number | Inventory Item Cross Reference table |
If the wrong code is passed to the Order Import tables, you may not get a match on the item. If a code is entered that does not exist, there will be an error on the item. When in doubt, leave it null. (Processing of a null CUSTOMER_ITEM_ID_TYPE a later feature.)
Code conversion on the product qualifier in standard transactions is necessary to associate them to the cross reference type as defined in Oracle Inventory.
X12 Product Qualifier (Data Element 235) | Sample Cross-Reference Type in Oracle Inventory | Table to which the search will be directed |
---|---|---|
BP | CUST | Customer Item Cross-Reference table |
EN | EAN | Inventory Item Cross Reference table |
IB | ISBN | Inventory Item Cross Reference table |
UP | UPC | Inventory Item Cross Reference table |
VP | INT | Customer Item Cross Reference table (if the item is moved into CUSTOMER_ITEM_NAME in the transaction. |
VP | (not applicable if the associated item is moved to INVENTORY_ITEM) | Inventory Item table (if the item is moved into INVENTORY_ITEM in the transaction) |
CUSTOMER_ITEM_ID_TYPE can be set up for code conversion in the e-Commerce Gateway so the standard code in position 80 can be converted to the valid Oracle code. Alternately, the EDI translator may place the valid Oracle Inventory defined code in position 70.
The first item to be used in a search is the customer item or generic item in the CUSTOMER_ITEM_NAME if the field is populated. The customer's purchasing application may not be maintaining accurate supplier items, if they rely on their own customer items and generic items for accurate order fulfilment. If only the supplier item is sent, then they are more likely to be maintained accurately by the customer.
If CUSTOMER_ITEM_ID_TYPE is not null, then only the table corresponding to its value is searched. This enables you to restrict customers to ordering items that are set up using only the specific CUSTOMER_ITEM_ID_TYPE. If you want all the tables to be searched, then leave this field null.
For example, if you know that the customer sent a customer item, and then you should populate CUSTOMER_ITEM_ID_TYPE with CUST, to speed up processing. Only the customer cross reference item table will be accessed for validating the CUSTOMER_ITEM_NAME.
If you know the customer sent a UPC code, then you should populate CUSTOMER_ITEM_ID_TYPE with the UPC. Only the generic item table will be accessed for validating the CUSTOMER_ITEM_NAME.
If the INVENTORY_ITEM is also populated in the transaction, it will be compared to the derived inventory item from the item cross-referencing. If they are different, a warning is generated. The derived inventory items will be used in the sales order.
The following table summarizes the tables that are accessed, given the data on record 2000 in the transaction.
Data in the e-Commerce Gateway Transaction | Table Searched |
---|---|
CUSTOMER_ITEM_NAME is populated and CUSTOMER_ITEM_ID_TYPE is "CUST" or "INT" If the Supplier Inventory Item is in the transaction, compare it to the derived Supplier Inventory Item. Use the derived supplier inventory item, and create an error message (showing the sent inventory item) if the vendor inventory items do not match. |
Customer Cross-Reference table |
CUSTOMER_ITEM_NAME is populated and CUSTOMER_ITEM_ID_TYPE is a valid Cross Reference Type (other than CUST or INV). If the Supplier Inventory Item is in the transaction, compare it to the derived Supplier Inventory Item. Use the derived supplier inventory item, and create an error message (showing the sent inventory item) if the supplier inventory items do not match. |
Inventory Item Cross Reference (Generic) table |
CUSTOMER_ITEM_NAME is NOT populated and only the Supplier Inventory Item is populated. | Inventory Item table (for internal vendor items) |
If the CUSTOMER_ITEM_ID_TYPE is null but data is in the CUSTOMER_ITEM_NAME, the search will span all three tables until the item is found.
You run the risk of getting multiple table hits in the inventory item cross reference (generic) table if the same item number is defined under multiple CUSTOMER_ITEM_ID_TYPEs such as UPC and EAN. However, since the naming conventions for multiple items are diverse, the probability may be slight. Multiple items found will cause an error on the item.
Data in the e-Commerce Gateway Transaction | Order of the Tables Searched |
---|---|
CUSTOMER_ITEM_NAME is populated and CUSTOMER_ITEM_ID_TYPE is null If the Supplier Inventory Item is sent, compare it to the derived Supplier Inventory Item. Use the derived supplier inventory item, and create an error message (showing the sent inventory item) if the vendor inventory items do not match |
Customer Cross Reference table Inventory Item Cross Reference (Generic) table Inventory Item table (internal supplier items) |
The following table summarizes the results of item cross-referencing given the combination of data on the record 2000.
In the follwong table, the column INVENTORY_ITEM is in record 2000, position 30. The column Interpretation of CUSTOMER_ITEM_NAME is in record 2000, position 50. The column Sample CUSTOMER_ITEM_ID_TYPE (as defined in Oracle Inventory) is in record 2000, position 70 (or 80, depending on code conversion).
INVENTORY_ITEM | Interpretation of CUSTOMER_ITEM_NAME | Sample CUSTOMER_ITEM_ID_TYPE | Result of Item Cross-Referencing in Oracle Inventory |
---|---|---|---|
Null | any Customer-Specific item | CUST | Customer Item Cross-Reference table is accessed to validate the customer item and derive its corresponding internal inventory item. |
Null | any Internal Item in the Customer Item Cross-Reference table | INT | Customer Item Cross-Reference table is accessed to validate the customer item and derive its corresponding internal inventory item. |
Null | any UPC code | UPC | Inventory Item Cross-Reference table is accessed with the generic item to validate the generic item and derive its corresponding internal inventory item. |
Null | any EAN number | EAN | Inventory Item Cross-Reference table is accessed with the generic item to validate the generic item and derive its corresponding internal inventory item. |
any Inventory Item | Null | Null | Inventory Item table is accessed to validate the item in the transaction. If the inventory item is invalid, an error message is created. |
any Inventory Item | any Customer Specific item | CUST | Customer Item Cross Reference table is accessed to validate the customer item and derive its corresponding internal inventory item. The derived Inventory Item is compared to the Inventory Item in the transaction. If the derived and the sent Inventory Item do not match, an error message is generated where the sent inventory item is displayed in the error. |
any Inventory Item | any Internal Item in the Customer Item Cross Reference table | INT | Customer Item Cross Reference table is accessed to validate the customer item and derive its corresponding internal inventory item. The derived Inventory Item is compared to the Inventory Item in the transaction. If the derived and the sent Inventory Item do not match, an error message is generated where the sent inventory item is displayed in the error. |
any Inventory Item | any UPC code | UPC | Inventory Item Cross Reference table is accessed to validate the generic item and derive its corresponding internal inventory item. The derived Inventory Item is compared to the Inventory Item in the transaction. If the derived and the sent Inventory Item do not match, an error message is generated where the sent inventory item is displayed in the error. |
any Inventory Item | any EAN number | EAN | Inventory Item Cross Reference table is accessed to validate the generic item and derive its corresponding internal inventory item. The derived Inventory Item is compared to the Inventory Item in the transaction. If the derived and the sent Inventory Item do not match, an error message is generated where the sent inventory item is displayed in the error |
In all cases where more than one table entry is retrieved during the table search, an error message is created.
In all cases where the derived inventory item from the table search and the inventory item found in the transaction do not match, the derived inventory item replaces the sent inventory item in the Order Import tables, and the inventory item from the transaction is shown in the error message.
If the customer item/generic inventory item to supplier inventory item cross referencing is successful and the supplier inventory item validation is successful, the proper item data is moved to the following fields in the sales order in Order Management.
The following table shows results of a cross-referenced sales order. The first column shows the data from the transaction and the second column shows the data passed to the base Orders tables.
Data in the transaction | In the Order passed to the base Orders tables |
---|---|
CUSTOMER_ITEM_NAME if sent (that has the customer item or generic item) | Ordered Item |
Always a validated INVENTORY_ITEM. If only the supplier item was sent, it is the inventory item if it is valid; otherwise it is the derived supplier item. | Inventory Item (the internal item) |
Be sure to modify any form's folder to display both the Ordered Items and the Inventory Item.
The following item revision fields are for display only. These fields are not used in the Inventory tables to look up the Supplier Item for the order in Oracle Order Management:
Customer Item Revision (CUSTOMER_ITEM_REVISION)
The customer's item revision level as defined in the customer's purchasing application.
Supplier Item Revision (ITEM_REVISION)
The supplier's item revision level as defined in the supplier's order management application.
If the customer has multiple revisions to the base item that translates to different supplier items, then the customer item defined in Oracle Inventory should consist of the base item reference concatenated with the revision number/reference. See illustration below.
Base Customer Item in Purchasing | Customer Item Revision in Purchasing | Customer Item into Order Management (defined in Oracle Inventory) | Supplier Item in Oracle Inventory |
---|---|---|---|
86420 | 03 | 86420-03 | 98765 |
86420 | 04 | 86420-04 | 98799 |
To return the base customer item and their revisions to the customer in the purchase order acknowledgement transactions, consider storing the two components in flexfields.
INVENTORY_ITEM_SEGMENT_1 on Record 2010
The internal Oracle Inventory table ID for the valid internal inventory item is moved to INVENTORY_ITEM_SEGMENT_1 in the order in the base Order Management table. If the transaction has data in INVENTORY_ITEM_SEGMENT_1 in Record 2010, the item cross-referencing is not performed. The presence of data in that field assumes that the table ID is to be used in the process.
Note the record layout difference between the Inbound Purchase Order and the Inbound Purchase Order Change.
Level | Order Import Table | New Record | New Position | Added Field Record | Added Field Position | Inserted Field Record |
---|---|---|---|---|---|---|
Header | OE_HEADERS_INTFACE | 1010 | all | |||
Header | OE_HEADERS_INTFACE | 1020-1050 | all | |||
Header | OE_CREDITS_INTERFACE | 1700 | 100-110 | |||
Header | OE_PRICE_ADJS_INTERFACE | 1800 | 200-230 | |||
Header | OE_RESERVTNS_INTERFACE | 1900 | 100-110 | |||
Header | OE_ACTIONS_INTERFACE | 1950 | ||||
Line | OE_LINE_INTERFACE | 3010 | all | |||
Line | OE_LINE_INTERFACE | 3012-3018 | all | |||
Line | OE_CREDITS_INTERFACE | 5000 | 130-140 | |||
Line | OE_PRICE_ADJS_INTERFACE | 6020-6050 | all | 6000 | 220-250 | |
Line | OE_RESERVTNS_INTERFACE | 7000 | 120-130 | |||
Line | OE_ACTIONS_INTERFACE | 8000 | ||||
Line | OE_LOTSERIAL_INTERFACE | 9000 | 120-130 |
When a new Sales Order is changed to a Booked status within Order Management, an API is called. This API determines if the customer and customer location are defined as a Trading Partner within e-Commerce Gateway and if the POAO transaction is enabled for the Trading Partner.
When an existing Sales Order is modified and changed to a Booked status within Order Management, an API is called. This API determines if the customer and customer location are defined as a Trading Partner within e-Commerce Gateway and if the POCAO transaction is enabled for the Trading Partner.
If either of the above mentioned conditions is met, then the Sales Order data is either added or updated in the OE_HEADERS_ACKS and OE_LINE_ACKS tables. The ACKNOWLEDGE_FLAG must be set to ‘Y' in the OE_HEADERS_ACKS table to make the acknowledgment eligible for extraction.
The implementation of any transaction requires some setup in Oracle E-Business Suite and Oracle e-Commerce Gateway. This section focuses on the application setups necessary to implement a transaction that integrates with Oracle Payables. The table below lists the Oracle Payables transactions:
Note: See Oracle Payables Transaction Summaries for layout details.
Transaction Name | Direction | Transaction Code | ASC X12 | EDIFACT |
---|---|---|---|---|
Invoice | Inbound | INI | 810 | INVOIC |
Shipment and Billing Notice | Inbound | SBNI | 857 | N/A |
Application Advice | Outbound | ADVO | 824 | APERAK |
Payment Order/Remittance Advice | Outbound | PYO | 820 | PAYORD-REMADV PAYEXT-REMADV |
The topics covered for inbound transactions include the following:
Trading Partner Link to Oracle e-Commerce Gateway
Oracle e-Commerce Gateway Required Fields
Review Oracle e-Commerce Gateway Exceptions
Resolve Oracle e-Commerce Gateway Exceptions
Relevant Oracle Payables Profiles and Set Ups
Payables Open Interface Required Fields
Review Payables Open Interface Exceptions
Return Application Advice to Trading Partner (if appropriate)
Resolve Payables Open Interface Exceptions
The topics covered for outbound transactions include the following:
Two forms of Electronic Funds Transfer (EFT)
PAYORD, PAYEXT, PAYMUL
Pre-note Payment
Transaction Handling Options
Trading Partner Link to Oracle e-Commerce Gateway
Relevant Oracle Payables Profiles and Set-ups
Extract Criteria
Columns Updated Upon Extraction
Current Information
The transaction requirements may change when enhancements are made such as additional data added to the transaction. Current transaction details can be found on Oracle Support's web site.
Current detail record layouts are reported via the Transaction Layout Definition Report and the Interface File Data Report.
Refer to the Oracle Purchasing section for details regarding this transaction.
Trading Partner Link to Oracle e-Commerce Gateway
Suppliers and supplier sites are defined in either Oracle Purchasing or Oracle Payables. Included in the definition is the EDI Location Code that trading partners agree to exchange to represent the full detailed address. Often they do not send the full address, but just the EDI Location Code. This is a critical data field to Oracle e-Commerce Gateway.
The EDI Location Code is the link between a supplier/supplier site in Oracle E-Business Suite and the trading partner site definition in Oracle e-Commerce Gateway. This enables Oracle e-Commerce Gateway to access the detailed data about the supplier or supplier site in the base Oracle E-Business Suite applications without maintaining the detail data in Oracle e-Commerce Gateway.
To ensure that the trading partner link between Oracle e-Commerce Gateway and Oracle E-Business Suite is set up properly, verify that the supplier/supplier site and the EDI Location Code in Oracle E-Business Suite is the correct supplier/supplier site selected for the Trading Partner definition in Oracle e-Commerce Gateway. The selected supplier/supplier site and the EDI Location Code defined in Oracle E-Business Suite are displayed in the Define Trading Partners window, Assignment tab. If the data is not what you intend it to be, you must make the appropriate changes for the transaction to be extracted for the correct trading partner. This could involve either altering the supplier/supplier site in the base Oracle E-Business Suite application, or assigning a different supplier/supplier site to that EDI Location Code in Oracle e-Commerce Gateway.
Refer to the Trading Partner chapter for recommendations on selecting the correct trading partner EDI Location Code for the control record 0010 for the transaction in the transaction interface file.
Extract Criteria
The outbound Application Advice transaction is controlled by two database views that are defined according to the Oracle e-Commerce Gateway data model for application advice data. The two views contain variables which are dynamically set based on your responses to the extract program parameters (refer to Oracle e-Commerce Gateway User's Guide, Outbound Transactions chapter for a list of the program parameters).
The two database views are as follows:
ECE_ADVO_DETAILS_V
ECE_ADVO_HEADERS_V
The ECE_ADVO_HEADERS_V view is used to identify which application advice data is eligible for extraction. The extract criteria are as follows:
Trading partner is defined
Transaction type enabled for the trading partner
Application advice has not been previously extracted
Application advice provided by one of the following three inbound transactions:
Ship Notice/Manifest (from Oracle Purchasing (Receivables))
Shipment and Billing Notice (from Oracle Purchasing (Receivables)
Invoice (from Oracle Payables)
Refer to the details for the relevant inbound transaction to determine how it populates the ECE_ADVO_HEADERS and ECE_ADVO_DETAILS tables.
If necessary, you can use SQLPLUS to verify if there are any eligible documents to be extracted. To do so, you must first set the organization context and then issue the SQL count function as follows:
SQLPLUS>execute fnd_client_info.set_org_context("<Org number>"); SQLPLUS> select count(*) ECE_ADVO_HEADERS_V;
Review all your set ups if the count value is 0 as this indicates there are no eligible documents to be extracted.
Columns Updated Upon Extraction
Once an eligible application advice is successfully extracted and written to the transaction interface file, it is marked by Oracle e-Commerce Gateway to prevent it from subsequent extraction. The following fields are updated based on an excerpt of code in the ECADVOB.pls file to update the ECE_ADVO_HEADERS table:
EDI_PROCESSED_FLAG - value updated to "Y"
EDI_PROCESS_DATE - value updated to SYSDATE
Two Forms of Electronic Funds Transfer (EFT)
Oracle E-Business Suite supports two forms of Electronic Funds Transfer (EFT). The first is directly from Oracle Payables via a Payables created payment file that is sent to the bank for disbursement directly into the supplier's bank account.
Refer to the Creating Electronic Funds Transfer Payments without the Oracle e-Commerce Gateway section, Oracle Payables User's Guide for details.
The second form of EFT is via the Oracle e-Commerce Gateway outbound Payment Order/Remittance Advice (820/PAYORD-REMADV, 820/PAYEXT-REMADV) transaction.
PAYORD, PAYEXT, PAYMUL
The EDIFACT PAYORD message is for payments to a single supplier. The EDIFACT PAYEXT message is for payments to multiple suppliers.
The EDIFACT PAYMUL message is for multiple currency payment batches. Oracle Payables does not support this feature.
Pre-note Payment
You can set up a pre-note payment by creating a zero dollar invoice. Select Allow Zero Invoices when you create the payment batch to include this invoice. Your disbursement bank will process this pre-note payment to verify the accuracy of the payer and payee bank account data.
Transaction Handling Options
The outbound Payment Order/Remittance Advice transaction is designed to accommodate both the payment and associated invoice data. With a single transaction, you can instruct your disbursement bank to do one of the following:
Disburse funds directly to the supplier's bank account and process the remittance advice
Disburse funds directly to the supplier's bank account, do nothing with the remittance advice
Send remittance advice electronically to the supplier, do nothing with the payment
Disburse funds directly to the supplier's bank account and send remittance advice to the supplier electronically
You indicate your instructions to the disbursement bank by setting the Transaction Handling code when you define your supplier or supplier sites.
Regardless of how you set the Transaction Handling code, Oracle e-Commerce Gateway will construct the entire file so your disbursement bank has everything it needs.
If your supplier cannot receive an electronic remittance advice, a hard copy may be printed using Oracle Payables and sent to the supplier.
Trading Partner Link to Oracle e-Commerce Gateway
Bank branch data for the disbursement bank is defined in Oracle Payables. Included in the definition is the EDI Location Code that trading partners agree to exchange to represent the full detailed address. Often they do not send the full address, but just the EDI Location Code. This is a critical data field to Oracle e-Commerce Gateway.
The EDI Location Code is the link between a bank branch in Oracle Payables and the trading partner site (bank branch) definition in Oracle e-Commerce Gateway. This enables Oracle e-Commerce Gateway to access the detailed data about the bank branch in the Oracle Payables without maintaining the detail data in Oracle e-Commerce Gateway.
To ensure that the trading partner link between Oracle e-Commerce Gateway and Oracle Payables is set up properly, verify that the bank branch and the EDI Location Code in Oracle Payables is the correct bank branch selected for the Trading Partner definition in Oracle e-Commerce Gateway. The selected bank branch and the EDI Location Code defined in Oracle Payables are displayed in the Define Trading Partners window, Assignment tab. If the data is not what you intend it to be, you must make the appropriate changes for the transaction to be extracted for the correct bank branch. This could involve either altering the bank branch in the base Oracle Payables, or assigning a different bank branch to that EDI Location Code in Oracle e-Commerce Gateway.
Refer to the Trading Partner chapter for recommendations on selecting the correct trading partner EDI Location Code for the control record 0010 for the transaction in the transaction interface file.
Relevant Oracle Payables Profiles and Setups
The outbound Payment Order/Remittance Advice transaction is an event driven process initiated in Oracle Payables as a payment format after the invoices are selected and the payment batch is created. This transaction cannot be initiated from Oracle e-Commerce Gateway.
The following is a list of the Payables set ups related to the outbound Payment Order/Remittance Advice transaction. Refer to the Creating Electronic Payments with Oracle e-Commerce Gateway section, Oracle Payables User's Guide for details.
Enter an EDI ID Number in the Banks window for your disbursement bank. Refer to the Defining Banks section, Oracle Payables User's Guide for details.
Define a payment document that uses the EDI Outbound Program as the payment format and assign this payment document to your disbursement bank account. Refer to the Defining and Maintaining Payables Payment Documents section, Oracle Payables User's Guide for details.
Enter bank data for each supplier site you want to pay. Refer to the Defining Supplier Bank Accounts section, Oracle Payables User's Guide for details.
Enter electronic payment data for each supplier site you want to pay. Refer to the Electronic Data Interchange Region of the Suppliers and Supplier Site Windows section, Oracle Payables User's Guide for details. This is where you specify the following:
EDI Location - An identifier for the supplier that links to e-Commerce Gateway trading partner
EDI ID Number - ID number used by Oracle Energy
Payment Method - Indicates how the electronic payment will be made. Payment Method values and their descriptions are listed in the table below:
Payment Method | Description |
---|---|
ACH | Automatic Clearing House |
BACS | Bankers Automatic Clearing System |
BOP | Financial Institution Option |
CWT | Clearing House Inter-bank Payment System (CHIPS) Funds/Wire Transfer |
FEW | Federal Reserve Fund/Wire Transfer, Repetitive |
FWT | Federal Reserve Fund/Wire Transfer, Non-repetitive |
SWT | Society for World-wide Inter-bank Financial Telecommunications (SWIFT) |
Payment Format - Indicates type of data being transmitted and the format of the data. Payment Format values and their descriptions are listed in the table below:
Payment Format | Description |
---|---|
CCD | Cash Concentration/Disbursement (ACH, CCD) |
CCP | Cash Concentration/Disbursement plus Addenda (ACH, CCP) |
CTP | Corporate Trade Payment (ACH, CTP) |
CTX | Corporate Trade Exchange (ACH, CTX) |
PPD | Prearranged Payment and Deposit (ACH, PPD) |
PPP | Prearranged Payment and Deposit plus Addenda (ACH, PPP) |
Remittance Method - Indicates which party is responsible for sending remittance advice to the payee. The valid options are as follows:
EDI to Payer's bank
EDI to Payee's bank
EDI to Payee
EDI to third party
Do not route
Remittance Instruction - Additional text instructions.
Transaction Handling - Indicates how payment and remittance advice should be processed. The Transaction Handling codes and their descriptions are listed in the table below:
Handling Code | Description |
---|---|
C | Payment accompanies remittance advice |
D | Payment only |
I | Remittance advice only |
U | Split payment and remittance advice |
Z | Other types of handling |
Verify that the invoices you wish to pay electronically are defined with "Electronic" as the payment method.
Verify that the invoices you wish to pay do not have active holds placed on them.
Approve the invoices you wish to pay.
Verify that you have confirmed the default remit-to bank account for each scheduled payment.
Create a Pay Group type lookup specifically for EDI payments to separate the non-EDI payments from the EDI payments. This is an optional set-up step.
Create the Payment Batch. Refer to the Initiating Payment Batches section, Oracle Payables User's Guide for details.
Refer to the Modifying Payment Batches section, Oracle Payables User's Guide, for details on how to modify a payment batch if necessary.
Format the Payment Batch. This process initiates the Oracle e-Commerce Gateway outbound Payment Order/Remittance Advice transaction that creates a transaction interface file.
Extract Criteria
The outbound Payment Order/Remittance Advice transaction is controlled by two database views that are defined according to the Oracle Payables data model for payments and their corresponding invoices. The two views contain variables which are dynamically set based on your responses to the extract program parameters (refer to the Outbound Transactions chapter, Oracle e-Commerce Gateway User's Guide for a list of the program parameters).
The two database views are as follows:
ECE_PYO_INVOICE_V
ECE_PYO_PAYMENT_V
The ECE_PYO_PAYMENTS_V view used to identify which payments and associated invoices are eligible for extraction. The extract criteria are as follows:
Trading partner is defined
Transaction type enabled for the trading partner
Payments have not been previously extracted
Payments are flagged as OK to pay
Payments have not been voided
Bank account ID, bank name and bank branch ID assigned to the payment match those in the payment batch
If necessary, you can use SQLPLUS to verify if there are any eligible documents to be extracted. To do so, you must first set the organization context and then issue the SQL count function as follows:
SQLPLUS> execute fnd_client_info.set_org_context('<Org number>'); SQLPLUS> select count(*) ECE_PYO_PAYMENTS_V;
Review all your set ups if the count value is 0 as this indicates there are no eligible documents to be extracted.
Columns Updated Upon Extraction
Once the Translator sends the transactions to the bank and you have verified the bank has received the file, you can confirm the payment batch to indicate the invoices have been paid. This prevents the same invoice from being included in a future payment batch. Refer to the Confirming Payment Batches section, Oracle Payables User's Guide for details.
Trading Partner Link to Oracle e-Commerce Gateway
Suppliers and supplier sites are defined in either Oracle Purchasing or Oracle Payables. Included in the definition is the EDI Location Code that trading partners agree to exchange to represent the full detailed address. Often they do not send the full address, but just the EDI Location Code. This is a critical data field to Oracle e-Commerce Gateway.
The EDI Location Code is the link between a supplier/supplier site in Oracle E-Business Suite and the trading partner site definition in Oracle e-Commerce Gateway. This enables Oracle e-Commerce Gateway to access the detailed data about the supplier or supplier site in the base Oracle E-Business Suite applications without maintaining the detail data in Oracle e-Commerce Gateway.
To ensure that the trading partner link between Oracle e-Commerce Gateway and Oracle E-Business Suite is set up properly, verify that the supplier/supplier site and the EDI Location Code in Oracle E-Business Suite is the correct supplier/supplier site selected for the Trading Partner definition in Oracle e-Commerce Gateway. The selected supplier/supplier site and the EDI Location Code defined in Oracle E-Business Suite are displayed in the Define Trading Partners window, Assignment tab. If the data is not what you intend it to be, you must make the appropriate changes for the transaction to be imported for the correct trading partner. This could involve either altering the supplier/supplier site in the base Oracle E-Business Suite application, or assigning a different supplier/supplier site to that EDI Location Code in Oracle e-Commerce Gateway.
Refer to the Trading Partner chapter for recommendations on selecting the correct trading partner EDI Location Code for the control record 0010 for the transaction in the transaction interface file.
Oracle e-Commerce Gateway Required Fields
The following is a list of the Oracle e-Commerce Gateway required fields. These fields are required to authenticate the trading partner and transaction. 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 Payables Open Interface tables to be processed by the Payables Open Interface API.
Oracle e-Commerce Gateway Column Name for Required Fields | Record Number | Position Number | Note |
---|---|---|---|
TEST_INDICATOR | 0010 | 20 | "T"or "P" |
TP_DOCUMENT_ID | 0010 | 30 | "ASNI" or "SBNI" |
TP_TRANSLATOR_CODE | 0010 | 70 | Translator identifier for this trading partner |
TP_LOCATION_CODE | 0010 | 80 | The EDI Location Code |
INVOICE_NUM | 1000 | 10 | |
INVOICE_AMOUNT | 1000 | 40 | |
ITEM_LINE_TYPE_CODE | 3000 | 10 | |
AMOUNT | 3000 | 110 |
Control Record 0010
TEST_INDICATOR
This column represents the test or production indicator from the Trading Partner. If this value does not match the test or production indicator associated with the trading partner defined in Oracle e-Commerce Gateway, a process rule exception is detected. Then an exception message is displayed in the View Staged Documents window.
The valid values are "T" for test and "P" for production.
TP_DOCUMENT_ID
This column identifies the type of document being sent by the Trading Partner. If this document type is not enabled for the trading partner defined in Oracle e-Commerce Gateway, a process rule exception is detected. Then an exception message is displayed in the View Staged Documents window.
The valid value is INI for standard invoice.
TP_TRANSLATOR_CODE, TP_LOCATION_CODE (EDI Location Code)
The two columns in combination uniquely identify a Trading Partner in Oracle e-Commerce Gateway. Once the trading partner definition is accessed, Oracle e-Commerce Gateway can verify whether the transaction is enabled for the Trading Partner.
If this trading partner is not defined in Oracle e-Commerce Gateway, a process rule exception is detected. Then an exception message is displayed in the View Staged Documents window.
Refer to the Trading Partner chapter for details on how to properly define your trading partners and get a better understanding of how these fields are used in the process.
Transaction Detail Records
AMOUNT
This column represents the invoice distribution amount.
If the Invoice Match Option indicated on the transaction interface file is Purchase Order, this column is the quantity invoiced multiplied by the unit price.
If the Invoice Match Option indicated on the transaction interface file is Receipt, this amount is distributed across the non-invoiced receipts beginning with the oldest. If the invoice amount exceeds the total amount for the non-invoiced receipts, the overage is applied to the newest non-invoiced receipt.
INVOICE_AMOUNT
This column represents the total amount for the invoice calculated as the sum of the invoice line amounts.
INVOICE_NUM
This column represents the invoice number for the supplier invoice being imported into Oracle Payables. The number must be unique for the supplier, as the Payables Open Interface Import program will reject duplicate invoice numbers.
ITEM_LINE_TYPE_CODE
This column identifies the invoice line type. The valid values are ITEM, TAX, FREIGHT or MISCELLANEOUS.
Review Oracle e-Commerce Gateway Exceptions
Use the Oracle e-Commerce Gateway View Staged Documents window to review the Oracle e-Commerce Gateway transaction exceptions. Once the exceptions are identified and resolved, you can submit the transaction for reprocessing, ignore the exception during reprocessing, or delete the transaction. Select the option in the View Staged Documents window.
Resolve Oracle e-Commerce Gateway Exceptions
To resolve Oracle e-Commerce Gateway exceptions, you can either correct the setup data in Oracle e-Commerce Gateway or Oracle E-Business Suite, or ask the Trading Partner to send a corrected transaction.
If the Trading Partner sends a corrected transaction, be sure to delete the erroneous transaction from Oracle e-Commerce Gateway's staging tables using the View Staged Documents window. The duplicate transaction may cause confusion.
Relevant Oracle Payables Profiles and Setups
The following is a list of the Payables set ups related to the Receiving Open Interface.
Invoice Hold Reason
You can optionally set up an Invoice Hold Reason unique to this transaction. All invoices being imported into Oracle Payables may be placed on hold using the unique Invoice Hold Reason. This may be useful during implementation of a new trading partner so that you can review the electronically transmitted invoices before approving them for payment. Use the Invoice Approvals window to define an invoice hold reason.
Refer to Oracle Payables User's Guide for details.
Payables Open Interface Required Fields
The Payables Open Interface is used by the Inbound Invoice transaction. It is used to validate the incoming data entered into the Payables Open Interface tables by the Oracle e-Commerce Gateway import program.
The following is a list of the Payables Open Interface required fields. These fields are required for the Payables Open Interface Import program to successfully process and move the data from the Payables Open Interface tables into the Oracle Payables base application tables.
Required fields noted as derived or hardcoded do not require a value in the transaction on the transaction interface file since the values are determined by the Oracle e-Commerce Gateway process.
Refer to the Payables Open Interface Appendix section, Oracle Payables User's Guide for details on derived, defaulted and conditional fields (see Cond. items in the following tables).
AP_INVOICES_INTERFACE Table
The table below lists the required fields for the AP_INVOICES_INTERFACE table:
Oracle E-Business Suite Column Name for Required Fields | Cond. | Oracle e-Commerce Gateway Column Name | Hardcoded/ Derived | Record Number | Position Number |
---|---|---|---|---|---|
INVOICE_NUM | INVOICE_NUM | 1000 | 10 | ||
PO_NUMBER | Yes | PO_NUMBER | 1000 | 30 | |
INVOICE_AMOUNT | INVOICE_AMOUNT | 1000 | 40 | ||
TERMS_NAME | Yes | TERMS_NAME_INT | 1000 | 70 | |
INVOICE_CURRENCY_CODE | Yes | INVOICE_CURRENCY_CODE | 1010 | 10 | |
EXCHANGE_RATE | Yes | EXCHANGE_RATE | 1010 | 30 | |
EXCHANGE_DATE | Yes | EXCHANGE_DATE | 1010 | 40 | |
INVOICE_ID | Derived | ||||
SOURCE | H:EDI_GATEWAY | ||||
EXCHANGE_RATE_TYPE | Yes |
EXCHANGE_RATE_TYPE is not mapped or referenced by the Oracle e-Commerce Gateway because the data is not required by the transaction.
INVOICE_AMOUNT
This column represents the total amount for the invoice calculated as the sum of the invoice line amounts.
INVOICE_CURRENCY_CODE, EXCHANGE_DATE, EXCHANGE_RATE
Currency code, exchange date and exchange rate are required for foreign currency invoices (where the currency code is different from the functional currency). The exchange date identifies the exchange rate to be used to compute the invoice amount.
INVOICE_NUM
This column represents the invoice number for the supplier invoice being imported into Oracle Payables. The number must be unique for the supplier, as the Payables Open Interface Import program will reject duplicate invoice numbers.
PO_NUMBER
This column represents the purchase order to match the invoice to. Purchase order numbers may be entered at the header or line item level.
TERMS_NAME
This column represents the payment terms and is required if you did not define payment terms for the supplier.
AP_INVOICE_LINES_INTERFACE Table
The following table lists the required fields for the AP_INVOICE_LINES_INTERFACE table:
Oracle E-Business Suite Column Name for Required Fields | Cond. | Oracle e-Commerce Gateway Column Name | Hardcoded/Derived | Record Number | Position Number |
---|---|---|---|---|---|
LINE_TYPE_LOOKUP_CODE | LINE_TYPE_LOOKUP_CODE | 3000 | 10 | ||
AMOUNT | AMOUNT | 3000 | 110 | ||
PO_NUMBER | Yes | PO_NUMBER | 3010 | 10 | |
INVOICE_ID | Derived | ||||
LINE_NUMBER | Derived |
AMOUNT
This column represents the invoice distribution amount.
If the Invoice Match Option indicated on the transaction interface file is Purchase Order, this column is the quantity invoiced multiplied by the unit price.
If the Invoice Match Option indicated on the transaction interface file is Receipt, this amount is distributed across the non-invoiced receipts beginning with the oldest. If the invoice amount exceeds the total amount for the non-invoiced receipts, the overage is applied to the newest non-invoiced receipt.
LINE_TYPE_LOOKUP_CODE
This column identifies the invoice line type. The valid values are ITEM, TAX, FREIGHT or MISCELLANEOUS.
PO_NUMBER
This column represents the purchase order to match the invoice to. Purchase order numbers may be entered at the header or line item level.
Review Payables Open Interface Exceptions
At the completion of the Payables Open Interface Import program, the Payables Open Interface Report is automatically initiated to report the status of all supplier invoices processed. The report contains two sections as follows:
Payables Open Interface Audit Report
Payables Open Interface Rejections Report
The Audit section lists the invoices that were successfully imported while the Rejections section lists invoices that were not successfully imported and the reason for the rejection.
Refer to the Payables Open Interface Import section, Oracle Payables User's Guide for a detailed list of the rejection codes and their meaning.
Return Application Advice to Supplier
To return invoice acknowledgments to the supplier, run the Payables Open Interface Outbound Advice program to populate the Oracle e-Commerce Gateway Application Advice tables (ECE_ADVO_HEADERS and ECE_ADVO_DETAILS) with the status of all supplier invoices processed. Once the Application Advice tables are populated, you can initiate the outbound Application Advice (ADVO/824/APERAK) transaction to acknowledge all invoices processed or just those containing exceptions.
Resolve Payables Open Interface Exceptions
There are three ways to resolve Payables Open Interface exceptions as follows:
Correct set up data in Oracle E-Business Suite
Correct erroneous entries in the Payables Open Interface table using Open Interface Invoices window
Request supplier to send a corrected transaction
If you chose to update Oracle E-Business Suite data or change the erroneous entries using the Open Interface Invoices window in Payables, you can resubmit the Payables Open Interface Import process to re-validate the transaction.
If you chose to have the supplier send a corrected transaction, you must first purge the rejected invoice data in the Payables Open Interface tables by submitting the Purge Payables Open Interface program and then re-import the updated transaction using Oracle e-Commerce Gateway.
Suppliers and supplier sites are defined in either Oracle Purchasing or Oracle Payables. Included in the definition is the EDI Location Code that trading partners agree to exchange to represent the full detailed address. Often they do not send the full address, but just the EDI Location Code. This is a critical data field to Oracle e-Commerce Gateway.
The EDI Location Code is the link between a supplier/supplier site in Oracle E-Business Suite and the trading partner site definition in Oracle e-Commerce Gateway. This enables Oracle e-Commerce Gateway to access the detailed data about the supplier or supplier site in the base Oracle E-Business Suite applications without maintaining the detail data in Oracle e-Commerce Gateway.
To ensure that the trading partner link between Oracle e-Commerce Gateway and Oracle E-Business Suite is set up properly, verify that the supplier/supplier site and the EDI Location Code in Oracle E-Business Suite is the correct supplier/supplier site selected for the Trading Partner definition in Oracle e-Commerce Gateway. The selected supplier/supplier site and the EDI Location Code defined in Oracle E-Business Suite are displayed in the Define Trading Partners window, Assignment tab. If the data is not what you intend it to be, you must make the appropriate changes for the transaction to be imported for the correct trading partner. This could involve either altering the supplier/supplier site in the base Oracle E-Business Suite application, or assigning a different supplier/supplier site to that EDI Location Code in Oracle e-Commerce Gateway.
Refer to the Trading Partner chapter for recommendations on selecting the correct trading partner EDI Location Code for the control record 0010 for the transaction in the transaction interface file.
The following is a list of the Oracle e-Commerce Gateway required fields. These fields are required to authenticate the trading partner and transaction. 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 Payables Open Interface tables to be processed by the Payables Open Interface API.
Oracle e-Commerce Gateway Required Fields
Oracle e-Commerce Gateway Column Name for Required Fields | Record Number | Position Number | Note |
---|---|---|---|
TEST_INDICATOR | 0010 | 20 | "T"or "P" |
TP_DOCUMENT_ID | 0010 | 30 | "ASNI" or "SBNI" |
TP_TRANSLATOR_CODE | 0010 | 70 | Translator identifier for this trading partner |
TP_LOCATION_CODE | 0010 | 80 | The EDI Location Code |
INVOICE_NUM | 1000 | 10 | |
INVOICE_AMOUNT | 1000 | 40 | |
ITEM_LINE_TYPE_CODE | 3000 | 10 | |
AMOUNT | 3000 | 110 |
TEST_INDICATOR
This column represents the test or production indicator from the Trading Partner. If this value does not match the test or production indicator associated with the trading partner defined in Oracle e-Commerce Gateway, a process rule exception is detected. Then an exception message is displayed in the View Staged Documents window.
The valid values are "T" for test and "P" for production.
TP_DOCUMENT_ID
This column identifies the type of document being sent by the Trading Partner. If this document type is not enabled for the trading partner defined in Oracle e-Commerce Gateway, a process rule exception is detected. Then an exception message is displayed in the View Staged Documents window.
The valid value is INI for standard invoice.
TP_TRANSLATOR_CODE, TP_LOCATION_CODE (EDI Location Code)
The two columns in combination uniquely identify a Trading Partner in Oracle e-Commerce Gateway. Once the trading partner definition is accessed, Oracle e-Commerce Gateway can verify whether the transaction is enabled for the Trading Partner.
If this trading partner is not defined in Oracle e-Commerce Gateway, a process rule exception is detected. Then an exception message is displayed in the View Staged Documents window.
Refer to the Trading Partner chapter for details on how to properly define your trading partners and get a better understanding of how these fields are used in the process.
AMOUNT
This column represents the invoice distribution amount.
If the Invoice Match Option indicated on the transaction interface file is Purchase Order, this column is the quantity invoiced multiplied by the unit price.
If the Invoice Match Option indicated on the transaction interface file is Receipt, this amount is distributed across the non-invoiced receipts beginning with the oldest. If the invoice amount exceeds the total amount for the non-invoiced receipts, the overage is applied to the newest non-invoiced receipt.
INVOICE_AMOUNT
This column represents the total amount for the invoice calculated as the sum of the invoice line amounts.
INVOICE_NUM
This column represents the invoice number for the supplier invoice being imported into Oracle Payables. The number must be unique for the supplier, as the Payables Open Interface Import program will reject duplicate invoice numbers.
ITEM_LINE_TYPE_CODE
This column identifies the invoice line type. The valid values are ITEM, TAX, FREIGHT or MISCELLANEOUS.
Use the Oracle e-Commerce Gateway View Staged Documents window to review the Oracle e-Commerce Gateway transaction exceptions. Once the exceptions are identified and resolved, you can submit the transaction for reprocessing, ignore the exception during reprocessing, or delete the transaction. Select the option in the View Staged Documents window.
To resolve Oracle e-Commerce Gateway exceptions, you can either correct the set-up data in Oracle e-Commerce Gateway or Oracle E-Business Suite, or ask the Trading Partner to send a corrected transaction.
If the Trading Partner sends a corrected transaction, be sure to delete the erroneous transaction from Oracle e-Commerce Gateway's staging tables using the View Staged Documents window. The duplicate transaction may cause confusion.
The following is a list of the Payables set ups related to the Receiving Open Interface.
Invoice Hold Reason
You can optionally set up an Invoice Hold Reason unique to this transaction. All invoices being imported into Oracle Payables may be placed on hold using the unique Invoice Hold Reason. This may be useful during implementation of a new trading partner so that you can review the electronically transmitted invoices before approving them for payment. Use the Invoice Approvals window to define an invoice hold reason.
Refer to Oracle Payables User's Guide for details.
The Payables Open Interface is used by the Inbound Invoice transaction. It is used to validate the incoming data entered into the Payables Open Interface tables by the Oracle e-Commerce Gateway import program.
The following is a list of the Payables Open Interface required fields. These fields are required for the Payables Open Interface Import program to successfully process and move the data from the Payables Open Interface tables into the Oracle Payables base application tables.
Required fields noted as derived or hardcoded do not require a value in the transaction on the transaction interface file since the values are determined by the Oracle e-Commerce Gateway process.
Refer to the Payables Open Interface Appendix section, Oracle Payables User's Guide for details on derived, defaulted and conditional fields (see Cond. items in the following tables).
AP_INVOICES_INTERFACE Table
The table below lists the required fields for the AP_INVOICES_INTERFACE table:
Oracle E-Business Suite Column Name for Required Fields | Cond. | Oracle e-Commerce Gateway Column Name | Hardcoded/ Derived | Record Number | Position Number |
---|---|---|---|---|---|
INVOICE_NUM | INVOICE_NUM | 1000 | 10 | ||
PO_NUMBER | Yes | PO_NUMBER | 1000 | 30 | |
INVOICE_AMOUNT | INVOICE_AMOUNT | 1000 | 40 | ||
TERMS_NAME | Yes | TERMS_NAME_INT | 1000 | 70 | |
INVOICE_CURRENCY_CODE | Yes | INVOICE_CURRENCY_CODE | 1010 | 10 | |
EXCHANGE_RATE | Yes | EXCHANGE_RATE | 1010 | 30 | |
EXCHANGE_DATE | Yes | EXCHANGE_DATE | 1010 | 40 | |
INVOICE_ID | Derived | ||||
SOURCE | H:EDI_GATEWAY | ||||
EXCHANGE_RATE_TYPE | Yes |
EXCHANGE_RATE_TYPE is not mapped or referenced by the Oracle e-Commerce Gateway because the data is not required by the transaction.
INVOICE_AMOUNT
This column represents the total amount for the invoice calculated as the sum of the invoice line amounts.
INVOICE_CURRENCY_CODE, EXCHANGE_DATE, EXCHANGE_RATE
Currency code, exchange date and exchange rate are required for foreign currency invoices (where the currency code is different from the functional currency). The exchange date identifies the exchange rate to be used to compute the invoice amount.
INVOICE_NUM
This column represents the invoice number for the supplier invoice being imported into Oracle Payables. The number must be unique for the supplier, as the Payables Open Interface Import program will reject duplicate invoice numbers.
PO_NUMBER
This column represents the purchase order to match the invoice to. Purchase order numbers may be entered at the header or line item level.
TERMS_NAME
This column represents the payment terms and is required if you did not define payment terms for the supplier.
AP_INVOICE_LINES_INTERFACE Table
The following table lists the required fields for the AP_INVOICE_LINES_INTERFACE table:
Oracle E-Business Suite Column Name for Required Fields | Cond. | Oracle e-Commerce Gateway Column Name | Hardcoded/Derived | Record Number | Position Number |
---|---|---|---|---|---|
LINE_TYPE_LOOKUP_CODE | LINE_TYPE_LOOKUP_CODE | 3000 | 10 | ||
AMOUNT | AMOUNT | 3000 | 110 | ||
PO_NUMBER | Yes | PO_NUMBER | 3010 | 10 | |
INVOICE_ID | Derived | ||||
LINE_NUMBER | Derived |
AMOUNT
This column represents the invoice distribution amount.
If the Invoice Match Option indicated on the transaction interface file is Purchase Order, this column is the quantity invoiced multiplied by the unit price.
If the Invoice Match Option indicated on the transaction interface file is Receipt, this amount is distributed across the non-invoiced receipts beginning with the oldest. If the invoice amount exceeds the total amount for the non-invoiced receipts, the overage is applied to the newest non-invoiced receipt.
LINE_TYPE_LOOKUP_CODE
This column identifies the invoice line type. The valid values are ITEM, TAX, FREIGHT or MISCELLANEOUS.
PO_NUMBER
This column represents the purchase order to match the invoice to. Purchase order numbers may be entered at the header or line item level.
At the completion of the Payables Open Interface Import program, the Payables Open Interface Report is automatically initiated to report the status of all supplier invoices processed. The report contains two sections as follows:
Payables Open Interface Audit Report
Payables Open Interface Rejections Report
The Audit section lists the invoices that were successfully imported while the Rejections section lists invoices that were not successfully imported and the reason for the rejection.
Refer to the Payables Open Interface Import section, Oracle Payables User's Guide for a detailed list of the rejection codes and their meaning.
To return invoice acknowledgments to the supplier, run the Payables Open Interface Outbound Advice program to populate the Oracle e-Commerce Gateway Application Advice tables (ECE_ADVO_HEADERS and ECE_ADVO_DETAILS) with the status of all supplier invoices processed. Once the Application Advice tables are populated, you can initiate the outbound Application Advice (ADVO/824/APERAK) transaction to acknowledge all invoices processed or just those containing exceptions.
There are three ways to resolve Payables Open Interface exceptions as follows:
Correct set up data in Oracle E-Business Suite
Correct erroneous entries in the Payables Open Interface table using Open Interface Invoices window
Request supplier to send a corrected transaction
If you chose to update Oracle E-Business Suite data or change the erroneous entries using the Open Interface Invoices window in Payables, you can resubmit the Payables Open Interface Import process to re-validate the transaction.
If you chose to have the supplier send a corrected transaction, you must first purge the rejected invoice data in the Payables Open Interface tables by submitting the Purge Payables Open Interface program and then re-import the updated transaction using Oracle e-Commerce Gateway.
Refer to the Oracle Purchasing section for details regarding this transaction.
Suppliers and supplier sites are defined in either Oracle Purchasing or Oracle Payables. Included in the definition is the EDI Location Code that trading partners agree to exchange to represent the full detailed address. Often they do not send the full address, but just the EDI Location Code. This is a critical data field to Oracle e-Commerce Gateway.
The EDI Location Code is the link between a supplier/supplier site in Oracle E-Business Suite and the trading partner site definition in Oracle e-Commerce Gateway. This enables Oracle e-Commerce Gateway to access the detailed data about the supplier or supplier site in the base Oracle E-Business Suite applications without maintaining the detail data in Oracle e-Commerce Gateway.
To ensure that the trading partner link between Oracle e-Commerce Gateway and Oracle E-Business Suite is set up properly, verify that the supplier/supplier site and the EDI Location Code in Oracle E-Business Suite is the correct supplier/supplier site selected for the Trading Partner definition in Oracle e-Commerce Gateway. The selected supplier/supplier site and the EDI Location Code defined in Oracle E-Business Suite are displayed in the Define Trading Partners window, Assignment tab. If the data is not what you intend it to be, you must make the appropriate changes for the transaction to be extracted for the correct trading partner. This could involve either altering the supplier/supplier site in the base Oracle E-Business Suite application, or assigning a different supplier/supplier site to that EDI Location Code in Oracle e-Commerce Gateway.
Note that if the trading partner is associated with multiple operating units, then the transaction to be extracted is for a specified operating unit only linked to your responsibility. To extract all the data for multiple operating units, you still need to run the program one by one by specifying different operating unit each time when you run the program. See the Outbound Transactions chapter in the Oracle e-Commerce Gateway User's Guide for details.
Refer to the Trading Partner chapter for recommendations on selecting the correct trading partner EDI Location Code for the control record 0010 for the transaction in the transaction interface file.
The outbound Application Advice transaction is controlled by two database views that are defined according to the Oracle e-Commerce Gateway data model for application advice data. The two views contain variables which are dynamically set based on your responses to the extract program parameters (refer to the Outbound Transactions chapter, Oracle e-Commerce Gateway User's Guide for a list of the program parameters).
The two database views are as follows:
ECE_ADVO_DETAILS_V
ECE_ADVO_HEADERS_V
The ECE_ADVO_HEADERS_V view is used to identify which application advice data is eligible for extraction. The extract criteria are as follows:
Trading partner is defined
Transaction type enabled for the trading partner
Application advice has not been previously extracted
Application advice provided by one of the following three inbound transactions:
Ship Notice/Manifest (from Oracle Purchasing (Receivables))
Shipment and Billing Notice (from Oracle Purchasing (Receivables)
Invoice (from Oracle Payables)
Refer to the details for the relevant inbound transaction to determine how it populates the ECE_ADVO_HEADERS and ECE_ADVO_DETAILS tables.
If necessary, you can use SQLPLUS to verify if there are any eligible documents to be extracted. To do so, you must first set the organization context and then issue the SQL count function as follows:
SQLPLUS>execute fnd_client_info.set_org_context("<Org number>"); SQLPLUS> select count(*) ECE_ADVO_HEADERS_V;
Review all your set ups if the count value is 0 as this indicates there are no eligible documents to be extracted.
Once an eligible application advice is successfully extracted and written to the transaction interface file, it is marked by Oracle e-Commerce Gateway to prevent it from subsequent extraction. The following fields are updated based on an excerpt of code in the ECADVOB.pls file to update the ECE_ADVO_HEADERS table:
EDI_PROCESSED_FLAG - value updated to "Y"
EDI_PROCESS_DATE - value updated to SYSDATE
Two Forms of Electronic Funds Transfer (EFT)
Oracle E-Business Suite supports two forms of Electronic Funds Transfer (EFT). The first is directly from Oracle Payables via a Payables created payment file that is sent to the bank for disbursement directly into the supplier's bank account.
Refer to the Creating Electronic Funds Transfer Payments without the Oracle e-Commerce Gateway section, Oracle Payables User's Guide for details.
The second form of EFT is via the Oracle e-Commerce Gateway outbound Payment Order/Remittance Advice (820/PAYORD-REMADV, 820/PAYEXT-REMADV) transaction. See below for details on how to set up this transaction.
PAYORD, PAYEXT, PAYMUL
The EDIFACT PAYORD message is for payments to a single supplier. The EDIFACT PAYEXT message is for payments to multiple suppliers.
The EDIFACT PAYMUL message is for multiple currency payment batches. Oracle Payables does not support this feature.
Pre-note Payment
You can set up a pre-note payment by creating a zero dollar invoice. Select Allow Zero Invoices when you create the payment batch to include this invoice. Your disbursement bank will process this pre-note payment to verify the accuracy of the payer and payee bank account data.
Transaction Handling Options
The outbound Payment Order/Remittance Advice transaction is designed to accommodate both the payment and associated invoice data. With a single transaction, you can instruct your disbursement bank to do one of the following:
Disburse funds directly to the supplier's bank account and process the remittance advice
Disburse funds directly to the supplier's bank account, do nothing with the remittance advice
Send remittance advice electronically to the supplier, do nothing with the payment
Disburse funds directly to the supplier's bank account and send remittance advice to the supplier electronically
You indicate your instructions to the disbursement bank by setting the Transaction Handling code when you define your supplier or supplier sites.
Regardless of how you set the Transaction Handling code, Oracle e-Commerce Gateway will construct the entire file so your disbursement bank has everything it needs.
If your supplier cannot receive an electronic remittance advice, a hard copy may be printed using Oracle Payables and sent to the supplier.
Bank branch data for the disbursement bank is defined in Oracle Payables. Included in the definition is the EDI Location Code that trading partners agree to exchange to represent the full detailed address. Often they do not send the full address, but just the EDI Location Code. This is a critical data field to Oracle e-Commerce Gateway.
The EDI Location Code is the link between a bank branch in Oracle Payables and the trading partner site (bank branch) definition in Oracle e-Commerce Gateway. This enables Oracle e-Commerce Gateway to access the detailed data about the bank branch in the Oracle Payables without maintaining the detail data in Oracle e-Commerce Gateway.
To ensure that the trading partner link between Oracle e-Commerce Gateway and Oracle Payables is set up properly, verify that the bank branch and the EDI Location Code in Oracle Payables is the correct bank branch selected for the Trading Partner definition in Oracle e-Commerce Gateway. The selected bank branch and the EDI Location Code defined in Oracle Payables are displayed in the Define Trading Partners window, Assignment tab. If the data is not what you intend it to be, you must make the appropriate changes for the transaction to be extracted for the correct bank branch. This could involve either altering the bank branch in the base Oracle Payables, or assigning a different bank branch to that EDI Location Code in Oracle e-Commerce Gateway.
Note that if the trading partner is associated with multiple operating units, then the transaction to be extracted is for a specified operating unit only linked to your responsibility. To extract all the data for multiple operating units, you still need to run the program one by one by specifying different operating unit each time when you run the program. See the Outbound Transactions chapter in the Oracle e-Commerce Gateway User's Guide for details.
Refer to the Trading Partner chapter for recommendations on selecting the correct trading partner EDI Location Code for the control record 0010 for the transaction in the transaction interface file.
The outbound Payment Order/Remittance Advice transaction is an event driven process initiated in Oracle Payables as a payment format after the invoices are selected and the payment batch is created. This transaction cannot be initiated from Oracle e-Commerce Gateway.
The following is a list of the Payables set ups related to the outbound Payment Order/Remittance Advice transaction. Refer to the Oracle Payables User's Guide, Creating Electronic Payments with Oracle e-Commerce Gateway section for the details.
Enter an EDI ID Number in the Banks window for your disbursement bank. Refer to Oracle Payables User's Guide, Defining Banks section for the details.
Define a payment document that uses the EDI Outbound Program as the payment format and assign this payment document to your disbursement bank account. Refer to Oracle Payables User's Guide, Defining and Maintaining Payables Payment Documents section for the details.
Enter bank data for each supplier site you want to pay. Refer to Oracle Payables User's Guide, Defining Supplier Bank Accounts section for the details.
Enter electronic payment data for each supplier site you want to pay. Refer to Oracle Payables User's Guide, Electronic Data Interchange Region of the Suppliers and Supplier Site Windows section for the details. This is where you specify the following:
EDI Location - An identifier for the supplier that links to e-Commerce Gateway trading partner
EDI ID Number - ID number used by Oracle Energy
Payment Method - Indicates how the electronic payment will be made. Payment Method values and their descriptions are listed in the table below:
Payment Method | Description |
---|---|
ACH | Automatic Clearing House |
BACS | Bankers Automatic Clearing System |
BOP | Financial Institution Option |
CWT | Clearing House Inter-bank Payment System (CHIPS) Funds/Wire Transfer |
FEW | Federal Reserve Fund/Wire Transfer, Repetitive |
FWT | Federal Reserve Fund/Wire Transfer, Non-repetitive |
SWT | Society for World-wide Inter-bank Financial Telecommunications (SWIFT) |
Payment Format - Indicates type of data being transmitted and the format of the data. Payment Format values and their descriptions are listed in the table below:
Payment Format | Description |
---|---|
CCD | Cash Concentration/Disbursement (ACH, CCD) |
CCP | Cash Concentration/Disbursement plus Addenda (ACH, CCP) |
CTP | Corporate Trade Payment (ACH, CTP) |
CTX | Corporate Trade Exchange (ACH, CTX) |
PPD | Prearranged Payment and Deposit (ACH, PPD) |
PPP | Prearranged Payment and Deposit plus Addenda (ACH, PPP) |
Remittance Method - Indicates which party is responsible for sending remittance advice to the payee. The valid options are as follows:
EDI to Payer's bank
EDI to Payee's bank
EDI to Payee
EDI to third party
Do not route
Remittance Instruction - Additional text instructions.
Transaction Handling - Indicates how payment and remittance advice should be processed. The Transaction Handling codes and their descriptions are listed in the table below:
Handling Code | Description |
---|---|
C | Payment accompanies remittance advice |
D | Payment only |
I | Remittance advice only |
U | Split payment and remittance advice |
Z | Other types of handling |
Verify that the invoices you wish to pay electronically are defined with "Electronic" as the payment method.
Verify that the invoices you wish to pay do not have active holds placed on them.
Approve the invoices you wish to pay.
Verify that you have confirmed the default remit-to bank account for each scheduled payment.
Create a Pay Group type lookup specifically for EDI payments to separate the non-EDI payments from the EDI payments. This is an optional set-up step.
Create the Payment Batch. Refer to Oracle Payables User's Guide, Initiating Payment Batches section for the details.
Refer to Oracle Payables User's Guide, Modifying Payment Batches section for details on how to modify a payment batch if necessary.
Format the Payment Batch. This process initiates the Oracle e-Commerce Gateway outbound Payment Order/Remittance Advice transaction that creates a transaction interface file.
The outbound Payment Order/Remittance Advice transaction is controlled by two database views that are defined according to the Oracle Payables data model for payments and their corresponding invoices. The two views contain variables which are dynamically set based on your responses to the extract program parameters (refer to Oracle e-Commerce Gateway User's Guide, Outbound Transactions chapter for a list of the program parameters).
The two database views are as follows:
ECE_PYO_INVOICE_V
ECE_PYO_PAYMENT_V
The ECE_PYO_PAYMENTS_V view used to identify which payments and associated invoices are eligible for extraction. The extract criteria are as follows:
Trading partner is defined
Transaction type enabled for the trading partner
Payments have not been previously extracted
Payments are flagged as OK to pay
Payments have not been voided
Bank account ID, bank name and bank branch ID assigned to the payment match those in the payment batch
If necessary, you can use SQLPLUS to verify if there are any eligible documents to be extracted. To do so, you must first set the organization context and then issue the SQL count function as follows:
SQLPLUS> execute fnd_client_info.set_org_context('<Org number>'); SQLPLUS> select count(*) ECE_PYO_PAYMENTS_V;
Review all your set ups if the count value is 0 as this indicates there are no eligible documents to be extracted.
Once the Translator sends the transactions to the bank and you have verified the bank has received the file, you can confirm the payment batch to indicate the invoices have been paid. This prevents the same invoice from being included in a future payment batch. Refer to Oracle Payables User's Guide, Confirming Payment Batches section for the details.
The following table lists the transactions delivered with the Oracle Process Manufacturing product:
Transaction Name | Direction | Transaction Code | ASC X12 | EDIFACT |
---|---|---|---|---|
OPM: Purchase Order Acknowledgment | Outbound | GPOAO | 855 | ORDRSP |
Refer to the product documentation for details on how to implement these transactions.
Note: See Oracle Process Manufacturing Transaction Summaries for layout details.
The transaction requirements may change when enhancements are made such as additional data added to the transaction. Current transaction details can be found on Oracle Support's web site.
Current detail record layouts are reported via the Transaction Layout Definition Report and the Interface File Data Report.
The implementation of any transaction requires some setup in Oracle E-Business Suite and Oracle e-Commerce Gateway. This chapter focuses on the application setups necessary to implement a transaction that integrates with Oracle Purchasing.
Note: See Oracle Purchasing Transaction Summaries for layout details.
The following table lists the transactions that integrate with Oracle Purchasing.
Transaction Name | Direction | Transaction Code | ASC X12 | EDIFACT |
---|---|---|---|---|
Price/Sales Catalogue | Inbound | CATI | 832 | PRICAT |
Response to Request for Quotation | Inbound | RRQI | 843 | QUOTES |
Ship Notice/Manifest | Inbound | ASNI | 856 | DESADV |
Shipment and Billing Notice | Inbound | SBNI | 857 | N/A |
Application Advice | Outbound | ADVO | 824 | APERAK |
Purchase Order | Outbound | POO | 850 | ORDERS |
Purchase Order Change | Outbound | POCO | 860 | ORDCHG |
The topics covered for inbound transactions include the following:
Trading Partner Link to Oracle e-Commerce Gateway
Oracle e-Commerce Gateway Required Fields
Review Oracle e-Commerce Gateway Exceptions
Resolve Oracle e-Commerce Gateway Exceptions
Relevant Application Profile Options and Setups
Relevant Application Open Interface Required Fields
Review Application Open Interface Exceptions
Return Application Advice to Trading Partner (if appropriate)
Resolve Application Open Interface Exceptions
The topics covered for outbound transactions include the following:
Trading Partner Link to Oracle e-Commerce Gateway
Relevant Oracle Purchasing Setups
Extract Criteria
Columns Updated Upon Extraction
The transaction requirements may change when enhancements are made such as additional data added to the transaction. Current transaction details can be found on Oracle Support's Web site.
Current detail record layouts are reported via the Transaction Layout Definition Report and the Interface File Data Report.
Suppliers and supplier sites are defined in either Oracle Purchasing or Oracle Payables. Included in the definition is the EDI Location Code that trading partners agree to exchange to represent the full detailed address. Often they do not send the full address, but just the EDI Location Code. This is a critical data field to Oracle e-Commerce Gateway.
The EDI Location Code is the link between a supplier/supplier site in Oracle E-Business Suite and the trading partner site definition in Oracle e-Commerce Gateway. This enables Oracle e-Commerce Gateway to access the detailed data about the supplier or supplier site in the base Oracle E-Business Suite applications without maintaining the detail data in Oracle e-Commerce Gateway.
To ensure that the trading partner link between Oracle e-Commerce Gateway and Oracle E-Business Suite is set up properly, verify that the supplier/supplier site and the EDI Location Code in Oracle E-Business Suite is the correct supplier/supplier site selected for the Trading Partner definition in Oracle e-Commerce Gateway. The selected supplier/supplier site and the EDI Location Code defined in Oracle E-Business Suite are displayed in the Define Trading Partners window, Assignment tab. If the data is not what you intend it to be, you must make the appropriate changes for the transaction to be imported for the correct trading partner. This could involve either altering the supplier/ supplier site in the base Oracle E-Business Suite application, or assigning a different supplier/supplier site to that EDI Location Code in Oracle e-Commerce Gateway.
Refer to the Trading Partner chapter for recommendations on selecting the correct trading partner EDI Location Code for the control record 0010 for the transaction in the transaction interface file.
The following table lists the Oracle e-Commerce Gateway required fields. These fields are required to authenticate the trading partner and transaction. 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 Purchasing Documents Open Interface tables to be processed by the Purchasing Documents Open Interface API.
Oracle e-Commerce Gateway Column Name for Required Fields | Record Number | Position Number | Note |
---|---|---|---|
TEST_INDICATOR | 0010 | 20 | "T" or "P" |
DOCUMENT_ID | 0010 | 30 | Constant "CATI" or "RRQI" |
TP_TRANSLATOR_CODE | 0010 | 70 | Translator identifier for this Trading Partner |
TP_LOCATION_CODE | 0010 | 80 | The EDI Location Code |
Control Record 0010
DOCUMENT_ID
This column identifies the type of document being sent by the Trading Partner. If this document type is not enabled for the trading partner defined in Oracle e-Commerce Gateway, a process rule exception is detected. Then an exception message is displayed in the View Staged Documents window.
The valid values are "CATI" for prices/sales catalog or "RRQI" for response to Request for Quote (RFQ).
TEST_INDICATOR
This column represents the test or production indicator from the Trading Partner. If this value does not match the test or production indicator associated with the trading partner defined in Oracle e-Commerce Gateway, a process rule exception is detected. Then an exception message is displayed in the View Staged Documents window.
The valid values are "T" for test and "P" for production.
TP_TRANSLATOR_CODE, TP_LOCATION_CODE (EDI Location Code)
The two columns in combination uniquely identify a Trading Partner in Oracle e-Commerce Gateway. Once the trading partner definition is accessed, Oracle e-Commerce Gateway can verify whether the transaction is enabled for the Trading Partner.
If this trading partner is not defined in Oracle e-Commerce Gateway, a process rule exception is detected. Then an exception message is displayed in the View Staged Documents window.
Refer to the Trading Partner chapter for details on how to properly define your trading partners and get a better understanding of how these fields are used in the process.
Use the Oracle e-Commerce Gateway View Staged Documents window to review the Oracle e-Commerce Gateway transaction exceptions. Once the exceptions are identified and resolved, you can submit the transaction for reprocessing, ignore the exception during reprocessing, or delete the transaction. Select the option in the View Staged Documents window.
To resolve Oracle e-Commerce Gateway exceptions, you can either correct the set up data in Oracle e-Commerce Gateway or Oracle E-Business Suite, or ask the Trading Partner to send a corrected transaction.
If the Trading Partner sends a corrected transaction, be sure to delete the erroneous transaction from Oracle e-Commerce Gateway's staging tables using the View Staged Documents window. The duplicate transaction may cause confusion.
The following is a list of the Purchasing setups related to the Purchasing Documents Open Interface. Refer to the Oracle Purchasing User's Guide for details.
PO: Archive Catalog on Approval
If the profile option is set to "Yes," Oracle Purchasing archives the price/sales catalog once it is approved.
This profile option works in conjunction with the Approval Status parameter.
If the Approval Status was set to Incomplete, then the imported catalog must be approved before it is archived.
If the Approval Status was set to Approved, then the imported catalog is archived immediately after it is imported into Oracle Purchasing.
If the profile option is set to "No," Oracle Purchasing will not archive the price/sales catalog.
Allow Updating of the Item Master
Create or Update Item Master is one of the program parameters to indicate whether to allow creation of a new item or updating of an existing item in the item master.
To ensure that item descriptions and item status codes may be updated, the following must be set up:
Allow Item Description Update is enabled in the Purchasing Options window, Control Options region.
INV: Default Item Status is set to Active
PO: Write Server Output to File
To facilitate the debugging of the Purchasing Documents Open Interface, error logs normally written to the Concurrent Manager log screen may be written to the file system if this profile option is set to "Yes."
Set Up Price Tolerance
Define price tolerances in Oracle Purchasing for price increases associated with a price/sales catalog update.
The Purchasing Documents Open Interface is used by the Inbound Price/Sales Catalog and Inbound Response to Request for Quotation transactions. It is used to validate the incoming data entered into the Purchasing Documents Open Interface tables by the Oracle e-Commerce Gateway import program.
The following is a list of the Purchasing Documents Open Interface required fields. These fields are required for the Purchasing Documents Open Interface API to successfully process and move the data from the Purchasing Documents Open Interface tables into the Oracle Purchasing base application tables.
Required fields noted as derived or hardcoded do not require a value in the transaction on the transaction interface file since the values are determined by the Oracle e-Commerce Gateway process.
PO_HEADERS_INTERFACE Table
The following table lists the required fields for the PO_HEADERS_INTERFACE table:
Oracle E-Business Suite Column Name for Required Fields | Cond. | Oracle e-Commerce Gateway Column Name | Hardcoded/Derived | Record Number | Position Number |
---|---|---|---|---|---|
VENDOR_DOC_NUM | VENDOR_DOC_NUM | 1000 | 10 | ||
EFFECTIVE_DATE | Yes | EFFECTIVE_DATE | 1000 | 20 | |
EXPIRATION_DATE | Yes | EXPIRATION_DATE | 1000 | 30 | |
ACTION | ACTION_TYPE_CODE_INT | 1030 | 10 | ||
VENDOR_SITE_CODE | SHIP_FROM_ADDRESS_CODE | 1120 | 10 | ||
VENDOR_ID | SHIP_FROM_CUSTOMER_ID | 1120 | 170 | ||
VENDOR_SITE_ID | SHIP_FROM_ADDRESS_ID | 1120 | 180 | ||
DOCUMENT_TYPE_CODE | |||||
INTERFACE_HEADER_ID | INTERFACE_HEADER_ID | Derived |
ACTION_TYPE_CODE_INT
This column is the catalog type indicator. The valid values are as follows:
ORIGINAL: New catalog
REPLACE: Replacement catalog
UPDATE: Catalog change
The following types of data are supported with a catalog change:
Unit price
Item description
Unit of measure
Price breaks for blanket purchase agreements
Expiration date for blanket purchase agreements
Supplier URL to get additional item data
DOCUMENT_TYPE_CODE
This column is not mapped or referenced by the Oracle e-Commerce Gateway because the data is not required by the transaction.
EFFECTIVE_DATE, EXPIRATION_DATE
These two columns are required if you are replacing an existing catalog or if sourcing rules (i.e. if you answered YES to "Create Sourcing Rules" parameter) are to be created. The values are used to locate the old price/sales catalog and retire it.
SHIP_FROM_ADDRESS_CODE
This column represents the supplier site code. The value is derived by the Purchasing Documents Open Interface based on the supplier site id.
SHIP_FROM_ADDRESS_ID
This column represents the vendor site id. The value is derived by Oracle e-Commerce Gateway based on the vendor id which itself is derived from the Translator Code (on record 0010, element 70) and EDI Location Code (on record 0010, element 80). The Purchasing Documents Open Interfaces uses the supplier site id to derive the supplier site code.
SHIP_FROM_CUSTOMER_ID
This column represents the supplier ID. Oracle e-Commerce Gateway derives this value based on the Translator Code (on record 0010, element 70) and EDI Location Code (on record 0010, element 80) combination. The Purchasing Documents Open Interface uses the supplier ID to derive the supplier name and number.
VENDOR_DOC_NUMBER
This column represents the supplier's catalog number as the supplier may not know your Oracle catalog number. The supplier's catalog number is used to locate an existing catalog for catalog replacement or update. If you are importing a new catalog, this column is used to verify that the catalog is not a duplicate.
PO_LINES_INTERFACE Table
The following table lists the required fields for the PO_LINES_INTERFACE table:
Oracle E-Business Suite Column Name for Required Fields | Cond. | Oracle e-Commerce Gateway Column Name | Hardcoded/Derived | Record Number | Position Number |
---|---|---|---|---|---|
LINE_NUM | Yes | :LINE_NUM | 2000 | 10 | |
ITEM | ITEM | 2000 | 20 | ||
QUANTITY | Yes | QUANTITY | 2010 | 10 | |
EFFECTIVE_DATE | Yes | EFFECTIVE_DATE | 2010 | 60 | |
EXPIRATION_DATE | Yes | EXPIRATION_DATE | 2010 | 70 | |
ITEM_DESCRIPTION | Yes | ITEM_DESCRIPTION | 2010 | 80 | |
UNIT_PRICE | Yes | UNIT_PRICE | 2020 | 10 | |
SHIPMENT_NUM | Yes | SHIPMENT_NUM | 2050 | 10 | |
INTERFACE_LINE_ID | INTERFACE_LINE_ID | Derived | |||
INTERFACE_HEADER_ID | INTERFACE_HEADER_ID | Derived |
EFFECTIVE_DATE
This column is required if you want to create sourcing rules (i.e. if you answered YES to "Create Sourcing Rules" parameter) along with importing a catalog. Whether a new sourcing rule is created or an existing rule is updated depends on whether you are importing a new catalog, catalog changes or replacing an existing catalog.
Refer to the Oracle Purchasing Open Interfaces document, Sourcing section for a detail description of how this data is used.
EXPIRATION_DATE
This column is required if you want to create sourcing rules (i.e. if you answered YES to "Create Sourcing Rules" parameter) along with importing a new or replacement catalog. If you are importing catalog changes, the value is used to retire the old catalog item.
Refer to the Oracle Purchasing Open Interfaces document, Sourcing section for a detail description of how this data is used.
ITEM, ITEM_DESCRIPTION
These two columns are required if you want to create or update an item in the item master (i.e. if you answered YES to the "Create or Update Item" parameter). If you are creating new items, both ITEM and ITEM DESCRIPTION are required. If you are updating the item master, ITEM DESCRIPTION is required.
LINE_NUM, SHIPMENT_NUM, QUANTITY, UNIT_PRICE
To enter price break data, the supplier must provide line number, shipment number, quantity and unit price on the transaction interface file in the format shown in the following table to accommodate Oracle Purchasing's data model.
Line Number (Rec 2000) | Shipment Number (Rec 2050) | Quantity (Rec 2010) | Unit Price (Rec 2020) |
---|---|---|---|
1 | 1 | 500 | 10.00 |
1 | 2 | 1000 | 7.00 |
1 | 3 | 5000 | 5.00 |
The price break data is loaded by Oracle e-Commerce Gateway into the PO_LINES_INTERFACE (for PO lines and shipments) table. The Purchasing Documents Open Interface API validates this data and moves the valid data into the PO_LINES (for PO lines) and PO_LINE_LOCATIONS (for PO shipments) tables.
If your supplier offers fixed pricing, then they only need to supply a unit price.
The processing strategy of the Purchasing Documents Open Interface is to process line by line. This means if the Purchasing Documents Open Interface detected an exception at the document header, it will not proceed to the document lines. At the document line level, the Purchasing Documents Open Interface will write valid document lines to the base application tables or write an exception message to the PO_INTERFACE_ERRORS table for each invalid document line thereby accepting partial transactions
You can use the Purchasing Interface Errors Report in Oracle Purchasing to review all exceptions detected by the Purchasing Documents Open Interface API.
Refer to the Oracle Manufacturing, Distribution, Sales & Service Open Interface manual, Oracle Purchasing Open Interfaces document for a detailed list of detectable errors and their meaning.
To resolve Purchasing Documents Open Interface exceptions, you can either correct the set up data in Oracle E-Business Suite or ask the supplier to resend the transaction with the corrected values.
If you chose to update Oracle E-Business Suite data, you can resubmit the Purchasing Documents Open Interface process to re-validate the transaction.
If you chose to have the supplier send a corrected transaction, you must first purge the rejected data sitting in the Purchasing Documents Open Interface tables by submitting the Purge Purchasing Documents Open Interface Processed Data program and then re-import the updated transaction using Oracle e-Commerce Gateway.
Suppliers and supplier sites are defined in either Oracle Purchasing or Oracle Payables. Included in the definition is the EDI Location Code that trading partners agree to exchange to represent the full detailed address. Often they do not send the full address, but just the EDI Location Code. This is a critical data field to Oracle e-Commerce Gateway.
The EDI Location Code is the link between a supplier/supplier site in Oracle E-Business Suite and the trading partner site definition in Oracle e-Commerce Gateway. This enables Oracle e-Commerce Gateway to access the detailed data about the supplier or supplier site in the base Oracle E-Business Suite applications without maintaining the detail data in Oracle e-Commerce Gateway.
To ensure that the trading partner link between Oracle e-Commerce Gateway and Oracle E-Business Suite is set up properly, verify that the supplier/supplier site and the EDI Location Code in Oracle E-Business Suite is the correct supplier site selected for the Trading Partner definition in Oracle e-Commerce Gateway. The selected supplier/supplier site and the EDI Location Code defined in Oracle E-Business Suite are displayed in the Define Trading Partners window, Assignment tab. If the data is not what you intend it to be, you must make the appropriate changes for the transaction to be imported for the correct trading partner. This could involve either altering the supplier/ supplier site in the base Oracle E-Business Suite application, or assigning a different supplier/supplier site to that EDI Location Code in Oracle e-Commerce Gateway.
Refer to the Trading Partner chapter for recommendations on selecting the correct trading partner EDI Location Code for the control record 0010 for the transaction in the transaction interface file.
The following table lists the Oracle e-Commerce Gateway required fields. These fields are required to authenticate the trading partner and transaction. 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.
Oracle e-Commerce Gateway Column Name for Required Fields | Record Number | Position Number | Note |
---|---|---|---|
TEST_INDICATOR | 0010 | 20 | "T" or "P" |
ASN_TYPE (Document ID) | 0010 | 30 | "ASNI" or "SBNI" |
TRAN_PURPOSE_EXT1 | 0010 | 50 | "NEW" or "CANCEL" |
TP_TRANSLATOR_CODE | 0010 | 70 | Translator identifier for this Trading Partner |
TP_LOCATION_CODE | 0010 | 80 | The EDI Location Code |
PICK_SLIP_NUMBER | 1000 | 10 | |
TRAN_PURPOSE_APPLICATION | 1000 | 110 | "NEW" or "CANCEL" |
PURCHASE_ORDER_NUM | 2000 | 50 | |
ORIGINAL_SYSTEM_LINE_REFERENCE | 2000 | 70 | |
QUANTITY | Defaulted to ship quantity in record 2000, position 80 | ||
SHIPPED_UNIT_CODE_INT | 2000 | 90 |
Control Record 0010
ASN_TYPE (Document ID)
This column identifies the type of document being sent by the Trading Partner. If this document type is not enabled for the trading partner defined in Oracle e-Commerce Gateway, a process rule exception is detected. Then an exception message is displayed in the View Staged Documents window.
The valid values are "ASNI" for the ship notices and "SBNI" shipment and billing notice.
TEST_INDICATOR
This column represents the test or production indicator from the Trading Partner. If this value does not match the test or production indicator associated with the trading partner defined in Oracle e-Commerce Gateway, a process rule exception is detected. Then an exception message is displayed in the View Staged Documents window.
The valid values are "T" for test and "P" for production.
TP_TRANSLATOR_CODE, TP_LOCATION_CODE (EDI Location Code)
The two columns in combination uniquely identify a Trading Partner in Oracle e-Commerce Gateway. Once the trading partner definition is accessed, Oracle e-Commerce Gateway can verify whether the transaction is enabled for the Trading Partner.
If this trading partner is not defined in Oracle e-Commerce Gateway, a process rule exception is detected. Then an exception message is displayed in the View Staged Documents window.
Refer to the Trading Partner chapter for details on how to properly define your trading partners and get a better understanding of how these fields are used in the process.
TRAN_PURPOSE_EXT1
This column represents the transaction purpose code. The valid values are NEW or CANCEL. The default value is NEW if no value is provided on the inbound flat file. The value entered for TRAN_PURPOSE_EXT1 should be the same value entered for TRAN_PURPOSE_APPLICATION in record 1000, position 110.
Transaction Detail Records
ORIGINAL_SYSTEM_LINE_REFERENCE
This column represents the purchase order line number associated with the shipment.
PICK_SLIP_NUMBER
This column represents the packing slip number from the supplier. This number will be used during the PO receipt process to recall the shipment data related to the purchase orders and items in the shipment.
If no value is provided, the Receiving Open Interface tries to default a value from the PACKING_SLIP or INVOICE_NUM columns. The values in this column must be unique from the supplier for one year.
PURCHASE_ORDER_NUM
This column represents the purchase order number associated with the shipment.
SHIPPED_UNIT_CODE_INT
This column represents the shipment quantity unit of measure.
TRAN_PURPOSE_APPLICATION
This column represents the transaction purpose code. The valid values are NEW or CANCEL. Place the value in record 1000, position 110. The default value is NEW if no value is provided on the inbound flat file.
The value entered for TRAN_PURPOSE_APPLICATION should be the same value entered for TRAN_PURPOSE_EXT1 in record 0010, position 50.
Use the Oracle e-Commerce Gateway View Staged Documents window to review the Oracle e-Commerce Gateway transaction exceptions. Once the exceptions are identified and resolved, you can submit the transaction for reprocessing, ignore the exception during reprocessing, or delete the transaction. Select the option in the View Staged Documents window.
To resolve Oracle e-Commerce Gateway exceptions, you can either correct the set up data in Oracle e-Commerce Gateway or Oracle E-Business Suite, or ask the Trading Partner to send a corrected transaction.
If the Trading Partner sends a corrected transaction, be sure to delete the erroneous transaction from Oracle e-Commerce Gateway's staging tables using the View Staged Documents window. The duplicate transaction may cause confusion.
The following is a list of the Purchasing setups related to the Receiving Open Interface.
RCV: Fail All ASN Lines if One Line Fails
If the profile option is set to "Yes" and any line failed validation, no further validation is performed for the ship notice.
If the profile option is set to "No" and any line failed validation, the process continues with the next line.
ASN Control
In the Receiving Options window in Purchasing, select Warning, Reject, or None in the ASN Control field to determine how Purchasing handles the receipt against a purchase order shipment for which a ship notice exists. Refer to the Oracle Purchasing User's Guide, Defining Receiving Options sections for details.
PO: Enable SQL Trace for Receiving Processor
If the profile option is set to "Yes," more detailed error data is provided in the View Log screen of the Submit Request window when you run the Receiving Transaction Processor.
Refer to the Oracle Purchasing User's Guide for details.
The Inbound Ship Notice/Manifest and Inbound Shipment and Billing Notice transactions use the Receiving Open Interface. It is used to validate the incoming data entered into the Receiving Open Interface tables by the Oracle e-Commerce Gateway import program.
The following is a list of the Receiving Open Interface required fields. These fields are required for the Receiving Open Interface API to successfully process and move the data from the Receiving Open Interface tables into the Oracle Purchasing base application tables.
Required fields noted as derived or hardcoded do not require a value in the transaction on the transaction interface file since the values are determined by the Oracle e-Commerce Gateway process.
RCV_HEADERS_INTERFACE Table
The following table lists the required fields for the RCV_HEADERS_INTERFACE table. An asterisk (*) by the column name indicates that it is not mapped/referenced by Oracle e-Commerce Gateway because the data is not required by the transaction/message. The <field>_ID fields are derived by the Receiving Open Interface API.
Oracle E-Business Suite Column Name for Required Fields | Cond. | Oracle e-Commerce Gateway Column Name | Hardcoded/Derived | Record Number | Position Number |
---|---|---|---|---|---|
SHIPMENT_NUM | Yes | PICK_SLIP_NUMBER | 1000 | 10 | |
SHIPPED_DATE | Yes | SHIPPED_DATE | 1000 | 70 | |
AUTO_TRANSACT_CODE | Yes | AUTO_TRANSACT_CODE | 1000 | 100/120 | |
TRANSACTION_TYPE | TRAN_PURPOSE_APPLICATION | 1000 | 110 | ||
INVOICE_NUM | Yes | INVOICE_NUM | 1030 | 10 | |
INVOICE_DATE | Yes | INVOICE_DATE | 1030 | 20 | |
TOTAL_INVOICE_AMOUNT | Yes | INVOICE_AMOUNT | 1030 | 30 | |
VENDOR_ID | SHIP_FROM_CUSTOMER_ID | 1100 | 170 | ||
SHIP_TO_ORGANIZATION_CODE | Yes | SHIP_TO_INT_LOCATION_ID | 1120 | 10 | |
CREATED_BY | CREATED_BY | Derived | |||
CREATION_DATE | CREATION_DATE | Derived | |||
GROUP_ID | GROUP_ID | Derived | |||
HEADER_INTERFACE_ID | HEADER_INTERFACE_ID | Derived | |||
LAST_UPDATE_DATE | LAST_UPDATE_DATE | Derived | |||
LAST_UPDATED_BY | LAST_UPDATED_BY | Derived | |||
PROCESSING_STATUS_CODE | H: PENDING | ||||
RECEIPT_SOURCE_CODE | H: VENDOR | ||||
VALIDATION_FLAG | H:Y | ||||
EMPLOYEE_NAME* | |||||
EMPLOYEE_ID* | |||||
RECEIPT_NUM* | Yes | ||||
VENDOR_NAME* | |||||
VENDOR_NUM* | |||||
SHIPPED_DATE | Yes | SHIPPED_DATE | 1000 | 70 | |
AUTO_TRANSACT_CODE | Yes | AUTO_TRANSACT_CODE | 1000 | 100/120 | |
SHIPMENT_NUM | Yes | PICK_SLIP_NUMBER | 1000 | 10 |
AUTO_TRANSACT_CODE
This column identifies the type of incoming data. The valid values are SHIP, RECEIVE or DELIVER.
The default value is RECEIVE if no value is provided in the transaction.
A value of SHIP tells the Receiving Open Interface to process the inbound ship notice as a ship notice only. You will need to run the PO receipt process in Purchasing when the physical goods arrive at your dock.
Use this setting if the physical goods are scheduled to arrive after the ship notice.
A value of RECEIVE tells the Receiving Open Interface to process the inbound ship notice as a ship notice and a PO receipt. Use this setting if the physical goods are scheduled to arrive with the ship notice or this is a service PO that does not require a physical receipt.
A value of DELIVER tells the Receiving Open Interface to process the inbound ship notice as a ship notice, a PO receipt and delivery. Use this setting to receive and deliver the physical goods to the requester or to inventory. This option assumes you do not want to inspect the goods.
INVOICE_NUM, INVOICE_DATE, INVOICE_AMOUNT
Invoice number, date and amount are required for the Inbound Shipment and Billing Notice transaction to create an invoice. The invoice number must be unique for the supplier.
PICK_SLIP_NUMBER
This column represents the packing slip number from the supplier. This number will be used during the PO receipt process to recall the shipment data related to the purchase orders and items in the shipment.
If no value is provided, the Receiving Open Interface tries to default a value from the PACKING_SLIP or INVOICE_NUM columns. The values in this column must be unique from the supplier for one year.
SHIPPED_DATE
This column represents the date the shipment was shipped. The value must be earlier than or equal to the system date.
SHIP_FROM_CUSTOMER_ID
This column represents the vendor ID. Oracle e-Commerce Gateway derives this value based on the Translator Code (on record 0010, element 70) and EDI Location Code (on record 0010, element 80) combination. The Receiving Open Interface uses the vendor ID to derive the vendor name and number.
SHIP_TO_INT_LOCATION_ID
This column represents the destination organization for the shipment. A valid inventory organization code in Purchasing is required for the Inbound Ship Notice/Manifest or Inbound Shipment and Billing Notice transactions. The destination organization code may be specified at the header or line level. However, if it is specified at the header level, then the same value applies to all the shipment lines.
TRANS_PURPOSE_APPLICATION
This column represents the transaction purpose code. The valid values are NEW or CANCEL. Place the value in record 1000, position 110. The default value is NEW if no value is provided on the inbound flat file.
The value entered for TRAN_PURPOSE_APPLICATION should be the same value entered for TRAN_PURPOSE_EXT1 in record 0010, position 50.
RCV_TRANSACTIONS_INTERFACE Table
The following table lists the required fields for the RCV_TRANSACTIONS_INTERFACE table. An asterisk (*) by the column name indicates that it is not mapped/referenced by Oracle e-Commerce Gateway because the data is not required by the transaction/message. The <field>_ID fields are derived by the Receiving Open Interface API.
Oracle E-Business Suite Column Name for Required Fields | Cond. | Oracle e-Commerce Gateway Column Name | Hardcoded/ Derived | Record Number | Position Number |
---|---|---|---|---|---|
ITEM_NUM | ITEM_NUMBER | 2000 | 10 | ||
VENDOR_ITEM_NUM | Yes | VENDOR_ITEM_NUM | 2000 | 20 | |
ITEM_REVISION | Yes | ITEM_REVISION | 2000 | 30 | |
DOCUMENT_NUM | PURCHASE_ORDER_NUM | 2000 | 50 | ||
DOCUMENT_LINE_NUM | ORIGINAL_SYSTEM_LINE_ REFERENCE |
2000 | 70 | ||
QUANTITY | SHIPPED_QUANTITY | 2000 | 80 | ||
UNIT_OF_MEASURE | SHIPPED_UNIT_CODE_INT | 2000 | 90 | ||
ITEM_DESCRIPTION | PRODUCT_DESCRIPTION | 2000 | 140 | ||
AUTO_TRANSACT_CODE | AUTO_TRANSACT_CODE | 2000 | 160 | ||
SHIP_TO_LOCATION_ID | Yes | SHIP_TO_INT_LOCATION_ID | 3000 | 10 | |
SHIP_TO_LOCATION_CODE | SHIP_TO_INT_LOCATION_NAME | 3000 | 30 | ||
DELIVER_TO_LOCATION_CODE | DELIVER_TO_LOCATION_CODE_ INT |
3020 | 10 | ||
DELIVER_TO_PERSON_NAME | DELIVER_TO_PERSON_NAME | 3020 | 70 | ||
CREATED_BY | CREATED_BY | Derived | |||
CREATION_DATE | CREATION_DATE | Derived | |||
GROUP_ID | GROUP_ID | Derived | |||
HEADER_INTERFACE_ID | Derived | ||||
INTERFACE_TRANSACTION_ID | INTERFACE_TRANSACTION_ID | Derived | |||
LAST_UPDATE_DATE | LAST_UPDATE_DATE | Derived | |||
LAST_UPDATED_BY | LAST_UPDATED_BY | Derived | |||
PROCESSING_MODE_CODE | H: BATCH | ||||
PROCESSING_STATUS_CODE | H: PENDING | ||||
RECEIPT_SOURCE_CODE | H: VENDOR | ||||
SOURCE_DOCUMENT_CODE | H: PO | ||||
TRANSACTION_DATE | H: SYSDATE | ||||
TRANSACTION_STATUS_CODE | H: PENDING | ||||
TRANSACTION_TYPE | H: SHIP | ||||
VALIDATION_FLAG | H: Y | ||||
CATEGORY_ID* | Yes | ||||
CUSTOMER_ID* | Yes | ||||
DELIVER_TO_LOCATION_ID* | Yes | ||||
DELIVER_TO_PERSON_ID* | Yes | ||||
EMPLOYEE_ID* | Yes | ||||
EXPECTED_RECEIPT_DATE* | Yes | ||||
ITEM_CATEGORY* | Yes | ||||
ITEM_ID* | Yes | ||||
LOCATOR* | Yes | ||||
OE_ORDER_HEADER_ID* | Yes | ||||
OE_ORDER_LINE_ID* | Yes | ||||
PO_HEADER_ID* | |||||
PO_LINE_ID* | Yes | ||||
SUBINVENTORY* | Yes | ||||
TO_ORGANIZATION_CODE* | Yes | ||||
TO_ORGANIZATION_ID* | Yes | ||||
VENDOR_ID* | |||||
VENDOR_NUM* | |||||
VENDOR_NAME* |
AUTO_TRANSACT_CODE
This column identifies the type of incoming data. The valid values are SHIP, RECEIVE or DELIVER.
The default value is RECEIVE if no value is provided on the inbound flat file.
A value of SHIP tells the Receiving Open Interface to process the inbound ship notice as a ship notice only. You will need to run the PO receipt process in Purchasing when the physical goods arrive at your dock.
Use this setting if the physical goods are scheduled to arrive after the ship notice.
A value of RECEIVE tells the Receiving Open Interface to process the inbound ship notice as a ship notice and a PO receipt. Use this setting if the physical goods are scheduled to arrive with the ship notice or this is a service PO that does not require a physical receipt.
A value of DELIVER tells the Receiving Open Interface to process the inbound ship notice as a ship notice, a PO receipt and delivery. Use this setting to receive and deliver the physical goods to the requester or to inventory. This option assumes you do not want to inspect the goods.
DELIVER_TO_PERSON_NAME, DELIVER_TO_LOCATION_CODE_INT
These two columns are required by the Receiving Open Interface if AUTO_TRANSACT_CODE is set to DELIVER. It represents the deliver-to information associated with the requester.
ITEM_NUMBER, PRODUCT_DESCRIPTION
These two columns represents the buyers item number and item descriptions as defined in Oracle Purchasing.
ITEM_REVISION
This column represents the item's revision level. A value is required if this is an inventory item under revision control and you have distributions with an inventory destination.
ORIGINAL_SYSTEM_LINE_REFERENCE
This column represents the purchase order line number associated with the shipment.
PURCHASE_ORDER_NUM
This column represents the purchase order number associated with the shipment.
SHIP_TO_INT_LOCATION_ID, SHIP_TO_INT_LOCATION_NAME
These two columns represents the destination organization for the shipment. A valid inventory organization code in Purchasing is required for the Inbound Ship Notice/Manifest or Inbound Shipment and Billing Notice transactions. If no values are provided at the line level, the header values are used as the default for all lines.
SHIPPED_QUANTITY
This column represents the shipment quantity.
SHIPPED_UNIT_CODE_INT
This column represents the shipment quantity unit of measure.
VENDOR_ITEM_NUM
This column represents the supplier's item number for the buyer item defined in Oracle Purchasing. This must be specified if buyer item number is not available.
All exceptions detected by the Receiving Open Interface are written to the PO_INTERFACE_ERRORS table to be reviewed using the Receiving Interface Errors Report (for 856/DESADV) or Purchasing Interface Errors Report (for 857).
Refer to the Oracle Manufacturing, Distribution, Sales & Service Open Interface manual, Oracle Purchasing Open Interfaces document for a detailed list of detectable errors and their meaning.
In addition to writing error data to the PO_INTERFACE_ERRORS table, the Receiving Open Interface program writes an entry to Oracle e-Commerce Gateway's Application Advice tables (ECE_ADVO_HEADERS and ECE_ADVO_DETAILS) to be reported back to the supplier via the outbound Application Advice (824/APERAK) transaction.
To resolve Receiving Open Interface exceptions, you can either correct the set up data in Oracle E-Business Suite or ask the supplier to resend the corrected transaction.
If you chose to update Oracle E-Business Suite data, you can resubmit the Receiving Open Interface process to revalidate the transaction.
If you chose to have the supplier send a corrected transaction, you must first purge the rejected data sitting in the Receiving Open Interface tables using the Transactions Status Summary window and then re-import the updated transaction using Oracle e-Commerce Gateway.
The following table lists the Oracle Warehouse Management required fields if you have licensed Oracle Warehouse Management and wish to receive LPN data on the ASN. If the required data is not provided in the transaction, the Oracle Warehouse Management Import process fails to preprocess the LPN data from the ASN. The remainder of the import process may succeed without LPN data.
Oracle Warehouse Management Column Name | Record Number | Position Number | Note |
---|---|---|---|
LICENSE_PLATE_NUMBER | 4000 | 10 | LPN Number |
LOT_NUMBER | 4000 | 20 | NULL for non-lot controlled |
FROM_SERIAL_NUMBER | 4000 | 30 | NULL for non-serial controlled |
TO_SERIAL_NUMBER | 4000 | 40 | NULL for non-serial controlled |
QUANTITY | 4000 | 50 | Quantity on LPN |
UOM_CODE_INT | 4000 | 60 | Internal UOM Code |
UOM_CODE_EXT! | 4000 | 70 | External UOM Code (if code conversion defined) |
License Plate Data
LICENSE_PLATE_NUMBER
This column identifies the License Plate Number that identifies the container for this line of material. In order to be successfully processed, this number must be a new LPN number, not previously defined in Oracle WMS. If the number already exists, the ASN will be processed without LPN data. In that case, a Confirmed Receipt must be performed.
LOT_NUMBER
This column identifies the Lot Number of the material shipped. If this number violates the lot uniqueness constraints, lot information will not be imported and a Confirmed Receipt must be performed.
FROM_SERIAL_NUMBER, TO_SERIAL_NUMBER
The two columns identify the serial numbers shipped. If the serial numbers violate the serial uniqueness constraints, serial information will not be imported and a Confirmed Receipt must be performed.
QUANTITY
This column represents the quantity of material packed on this LPN.
UOM_CODE_INT
This column represents the internal UOM code defined in Oracle E-Business Suite.
UOM_CODE_EXT1...EXT5
These columns represent the external UOM code found in the transaction if code conversion is defined as such.
Refer to the Oracle Payables section for details regarding this transaction.
Suppliers and supplier sites are defined in either Oracle Purchasing or Oracle Payables. Included in the definition is the EDI Location Code that trading partners agree to exchange to represent the full detailed address. Often they do not send the full address, but just the EDI Location Code. This is a critical data field to Oracle e-Commerce Gateway.
The EDI Location Code is the link between a supplier/supplier site in Oracle E-Business Suite and the trading partner site definition in Oracle e-Commerce Gateway. This enables Oracle e-Commerce Gateway to access the detailed data about the supplier or supplier site in the base Oracle E-Business Suite applications without maintaining the detail data in Oracle e-Commerce Gateway.
To ensure that the trading partner link between Oracle e-Commerce Gateway and Oracle E-Business Suite is set up properly, verify that the supplier/supplier site and the EDI Location Code in Oracle E-Business Suite is the correct supplier/supplier site selected for the Trading Partner definition in Oracle e-Commerce Gateway. The selected supplier/supplier site and the EDI Location Code defined in Oracle E-Business Suite are displayed in the Define Trading Partners window, Assignment tab. If the data is not what you intend it to be, you must make the appropriate changes for the transaction to be extracted for the correct trading partner. This could involve either altering the supplier/supplier site in the base Oracle E-Business Suite application, or assigning a different supplier/supplier site to that EDI Location Code in Oracle e-Commerce Gateway.
Note that if the trading partner is associated with multiple operating units, then the transaction to be extracted is for a specified operating unit only linked to your responsibility. To extract all the data for multiple operating units, you still need to run the program one by one by specifying different operating unit each time when you run the program. See the Outbound Transactions chapter in the Oracle e-Commerce Gateway User's Guide for details.
Refer to the Trading Partner chapter for recommendations on selecting the correct trading partner EDI Location Code for the control record 0010 for the transaction in the transaction interface file.
The following is a list of the Purchasing set ups related to the outbound Purchase Order transaction.
Archive on <Attribute> for original orders:
In the Setup > Purchasing > Document Types window, set the following:
Set the "Archive on Approve" for each document type enabled.
DO NOT set the attribute to "Archive on Print." This prevents the eligible purchase orders from being extracted since they will print and archive before the extract can happen.
Refer to the Oracle Purchasing User's Guide for the details.
The outbound Purchase Order transaction is controlled by three database views that are defined according to the Oracle Purchasing data model for purchase orders. The three views contain variables which are dynamically set based on your responses to the extract program parameters (refer to Oracle e-Commerce Gateway User's Guide, Outbound Transactions chapter for a list of the program parameters).
The three database views are as follows:
ECE_POO_HEADERS_V
ECE_POO_LINES_V
ECE_POO_SHIPMENTS_V
The ECE_POO_HEADERS_V view is used to identify which purchase orders are eligible for extraction. The extract criteria are as follows:
Trading partner is defined
Transaction type enabled for the trading partner
Purchase order has not been printed or previously extracted
Purchase order status is Approved
Purchase order has not been canceled
Purchase order is not on hold
If necessary, you can use SQLPLUS to verify if there are any eligible documents to be extracted. To do so, you must first set the organization context and then issue the SQL count function as follows:
SQLPLUS> execute fnd_client_info.set_org_context("<Org number>"); SQLPLUS> select count(*) ECE_POO_HEADERS_V;
Review all your set ups if the count value is 0 as this indicates there are no eligible documents to be extracted.
Once an eligible purchase order is successfully extracted and written to the transaction interface file, it is marked by Oracle e-Commerce Gateway to prevent it from subsequent extraction. The following table lists the fields updated based on an excerpt of code in the UPDATE_PO procedure defined in the ECEPOOB.pls file:
Table | Column | Value |
---|---|---|
PO_HEADERS | EDI_PROCESSED_FLAG | Y |
PO_HEADERS | LAST_UPDATE_DATE | SYSDATE |
PO_HEADERS | PRINTED_DATE | SYSDATE |
PO_HEADERS | PRINT_COUNT | Increment by 1 |
PO_HEADERS_ARCHIVE | EDI_PROCESSED_FLAG | Y |
PO_HEADERS_ARCHIVE | LAST_UPDATE_DATE | SYSDATE |
PO_HEADERS_ARCHIVE | PRINTED_DATE | SYSDATE |
PO_HEADERS_ARCHIVE | PRINT_COUNT | Increment by 1 |
PO_RELEASES | EDI_PROCESSED_FLAG | Y |
PO_RELEASES | LAST_UPDATE_DATE | SYSDATE |
PO_RELEASES | PRINTED_DATE | SYSDATE |
PO_RELEASES | PRINT_COUNT | Increment by 1 |
PO_RELEASES_ARCHIVE | EDI_PROCESSED_FLAG | Y |
PO_RELEASES_ARCHIVE | LAST_UPDATE_DATE | SYSDATE |
PO_RELEASES_ARCHIVE | PRINTED_DATE | SYSDATE |
PO_RELEASES_ARCHIVE | PRINT_COUNT | Increment by 1 |
The PO_HEADERS and PO_HEADERS_ARCHIVE tables are used for standard, planned or blanket purchase orders. The PO_RELEASES and PO_RELEASES_ARCHIVE tables are used for blanket purchase order releases.
Suppliers and supplier sites are defined in either Oracle Purchasing or Oracle Payables. Included in the definition is the EDI Location Code that trading partners agree to exchange to represent the full detailed address. Often they do not send the full address, but just the EDI Location Code. This is a critical data field to Oracle e-Commerce Gateway.
The EDI Location Code is the link between a supplier/supplier site in Oracle E-Business Suite and the trading partner site definition in Oracle e-Commerce Gateway. This enables Oracle e-Commerce Gateway to access the detailed data about the supplier or supplier site in the base Oracle E-Business Suite applications without maintaining the detail data in Oracle e-Commerce Gateway.
To ensure that the trading partner link between Oracle e-Commerce Gateway and Oracle E-Business Suite is set up properly, verify that the supplier/supplier site and the EDI Location Code in Oracle E-Business Suite is the correct supplier/supplier site selected for the Trading Partner definition in Oracle e-Commerce Gateway. The selected supplier/supplier site and the EDI Location Code defined in Oracle E-Business Suite are displayed in the Define Trading Partners window, Assignment tab. If the data is not what you intend it to be, you must make the appropriate changes for the transaction to be extracted for the correct trading partner. This could involve either altering the supplier/supplier site in the base Oracle E-Business Suite application, or assigning a different supplier/supplier site to that EDI Location Code in Oracle e-Commerce Gateway.
Note that if the trading partner is associated with multiple operating units, then the transaction to be extracted is for a specified operating unit only linked to your responsibility. To extract all the data for multiple operating units, you still need to run the program one by one by specifying different operating unit each time when you run the program. See the Outbound Transactions chapter in the Oracle e-Commerce Gateway User's Guide for details.
Refer to the Trading Partner chapter for recommendations on selecting the correct trading partner EDI Location Code for the control record 0010 for the transaction in the transaction interface file.
The following is a list of the Purchasing set ups related to the outbound Purchase Order Change transaction.
Archive on <Attribute> for change orders:
In the Setup > Purchasing > Document Types window, set the following:
Set the "Archive on Approve" for each document type enabled.
DO NOT set the attribute to "Archive on Print." This prevents the eligible purchase orders from being extracted since they will print and archive before the extract can happen.
Refer to the Oracle Purchasing User's Guide for the details.
The outbound Purchase Order Change transaction is controlled by three database views that are defined according to the Oracle Purchasing data model for purchase orders. The three views contain variables which are dynamically set based on your responses to the extract program parameters (refer to Oracle e-Commerce Gateway User's Guide, Outbound Transactions chapter for a list of the program parameters).
The three database views are as follows:
ECE_POCO_HEADERS_V
ECE_POCO_LINES_V
ECE_POCO_SHIPMENTS_V
The ECE_POCO_HEADERS_V view is used to identify which purchase order changes are eligible for extraction. The extract criteria are as follows:
Trading partner is defined
Transaction type enabled for the trading partner
The original purchase order has been printed or previously extracted
The current set of purchase order changes have not been printed or previously extracted
Purchase order change status is Approved
Purchase order is not on hold
If necessary, you can use SQLPLUS to verify if there are any eligible documents to be extracted. To do so, you must first set the organization context and then issue the SQL count function as follows:
SQLPLUS> execute fnd_client_info.set_org_context("<Org number>"); SQLPLUS> select count(*) ECE_POCO_HEADERS_V;
Review all your set ups if the count value is 0 as this indicates there are no eligible documents to be extracted.
Once an eligible purchase order change is successfully extracted and written to the transaction interface file, it is marked by Oracle e-Commerce Gateway to prevent it from subsequent extraction. The following table lists the fields updated based on an excerpt of code in the UPDATE_PO (the outbound purchase order and purchase order change share this procedure) procedure defined in the ECPOCOB.pls file:
Table | Column | Value |
---|---|---|
PO_HEADERS | EDI_PROCESSED_FLAG | Y |
PO_HEADERS | LAST_UPDATE_DATE | SYSDATE |
PO_HEADERS | PRINTED_DATE | SYSDATE |
PO_HEADERS | PRINT_COUNT | Increment by 1 |
PO_HEADERS_ARCHIVE | EDI_PROCESSED_FLAG | Y |
PO_HEADERS_ARCHIVE | LAST_UPDATE_DATE | SYSDATE |
PO_HEADERS_ARCHIVE | PRINTED_DATE | SYSDATE |
PO_HEADERS_ARCHIVE | PRINT_COUNT | Increment by 1 |
PO_RELEASES | EDI_PROCESSED_FLAG | Y |
PO_RELEASES | LAST_UPDATE_DATE | SYSDATE |
PO_RELEASES | PRINTED_DATE | SYSDATE |
PO_RELEASES | PRINT_COUNT | Increment by 1 |
PO_RELEASES_ARCHIVE | EDI_PROCESSED_FLAG | Y |
PO_RELEASES_ARCHIVE | LAST_UPDATE_DATE | SYSDATE |
PO_RELEASES_ARCHIVE | PRINTED_DATE | SYSDATE |
PO_RELEASES_ARCHIVE | PRINT_COUNT | Increment by 1 |
The PO_HEADERS and PO_HEADERS_ARCHIVE tables are used for standard, planned or blanket purchase orders. The PO_RELEASES and PO_RELEASES_ARCHIVE tables are used for blanket purchase order releases.
Note: While transmitting Outbound Purchase Order Change (POCO/860/ORDCHG) to a supplier, the output received contains information for the entire purchase orders, not just the data for the fields that were changed on the purchase order.
The implementation of any transaction requires some setup in Oracle E-Business Suite and Oracle e-Commerce Gateway. This section focuses on the application setups necessary to implement a transaction that integrates with Oracle Receivables. The transactions included with Oracle Receivables are listed in the following table:
Note: See Oracle Receivables Transaction Summaries for layout details.
Transaction Name | Direction | Transaction Code | ASC X12 | EDIFACT |
---|---|---|---|---|
Invoic | Outbound | INO | 810 | INVOIC |
Credit Memo/Debit Memo | Outbound | CDMO | 812 | CREADV/DEBADV |
The topics covered for outbound transactions include the following:
Trading Partner Link to Oracle e-Commerce Gateway
Relevant Application Profile Options and Setups
Extract Criteria
Columns Updated Upon Extraction
The transaction requirements may change when enhancements are made such as additional data added to the transaction. Current transaction details can be found on Oracle Support's web site.
Current detail record layouts are reported via the Transaction Layout Definition Report and the Interface File Data Report.
Customer and customer sites are defined in either Oracle Receivables or Oracle Order Management. Included in the definition is the EDI Location Code that trading partners agree to exchange to represent the full detailed address. Often they do not send the full address, but just the EDI Location Code. This is a critical data field to Oracle e-Commerce Gateway.
The EDI Location Code is the link between a customer/customer site in Oracle E-Business Suite and the trading partner site definition in Oracle e-Commerce Gateway. This enables Oracle e-Commerce Gateway to access the detailed data about the customer or customer site in the base Oracle E-Business Suite applications without maintaining the detail data in Oracle e-Commerce Gateway.
To ensure that the trading partner link between Oracle e-Commerce Gateway and Oracle E-Business Suite is set up properly, verify that the customer/customer site and the EDI Location Code in Oracle E-Business Suite is the correct customer/customer site selected for the Trading Partner definition in Oracle e-Commerce Gateway. The selected customer/customer site and the EDI Location Code defined in Oracle E-Business Suite are displayed in the Define Trading Partners window, Assignment tab. If the data is not what you intend it to be, you must make the appropriate changes for the transaction to be extracted for the correct trading partner. This could involve either altering the customer/customer site in the base Oracle E-Business Suite application, or assigning a different customer/customer site to that EDI Location Code in Oracle e-Commerce Gateway.
Note that if the trading partner is associated with multiple operating units, then the transaction to be extracted is for a specified operating unit only linked to your responsibility. To extract all the data for multiple operating units, you still need to run the program one by one by specifying different operating unit each time when you run the program. See the Outbound Transactions chapter in the Oracle e-Commerce Gateway User's Guide for details.
Refer to the Trading Partner chapter for recommendations on selecting the correct trading partner EDI Location Code for the control record 0010 for the transaction in the transaction interface file.
The 810/INVOIC transaction supports invoices, credit memos and debit memos. In addition, you can use the 812/CREADV-DEBADV transaction to send just the credit memos and debit memos. The method you chose is dependent on your agreement with your supplier. Any credit memo/debit memo extracted using the 810/INVOIC transaction will not be eligible for extraction by the 812/CREADV-DEBADV transaction.
The outbound Invoice transaction is controlled by five database views which are defined according to the Oracle Receivable data model for supplier invoices. The five views contain variables which are dynamically set based on your responses to the extract program parameters (refer to Oracle e-Commerce Gateway User's Guide, Outbound Transactions chapter for a list of the program parameters).
The five database views are as follows:
ECE_INO_ALLOWANCE_CHARGES_V
ECE_INO_HEADERS_1_V
ECE_INO_HEADER_V
ECE_INO_LINE_TAX_V
ECE_INO_LINE_V
The ECE_INO_HEADERS_V view is used to identify which supplier invoices are eligible for extraction. The extract criteria are as follows:
Trading partner is defined
Transaction type (invoice, credit memo or debit memo) enabled for the trading partner
Transaction type is invoice, credit memo or debit memo
Transaction type (invoice, credit memo or debit memo) has not been printed or previously extracted
Transaction type (invoice, credit memo or debit memo) status is COMPLETE
Transaction type (invoice, credit memo or debit memo) print option is "PRI"
If necessary, you can use SQLPLUS to verify if there are any eligible documents to be extracted. To do so, you must first set the organization context and then issue the SQL count function as follows:
SQLPLUS>execute fnd_client_info.set_org_context("<Org number>"); SQLPLUS>select count(*) ECE_INO_HEADERS_V;
Review all your set ups if the count value is 0 as this indicates there are no eligible documents to be extracted.
Once an eligible supplier invoice is successfully extracted and written to the transaction interface file, it is marked by Oracle e-Commerce Gateway to prevent it from subsequent extraction. The following table lists the fields updated in the RA_CUSTOMER_TRX table based on an excerpt of code in the UPDATE_AR procedure defined in the ECEINOB.pls file:
Column | Value Updated To |
---|---|
EDI_PROCESSED_FLAG | Y |
EDI_PROCESSED_STATUS | ED |
LAST_UPDATE_DATE | SYSDATE |
PRINTING_COUNT | increment 1 |
PRINTING_LAST_PRINTED | SYSDATE |
PRINTING_ORIGINAL_DATE | SYSDATE |
PRINTING_PENDING | N |
Customer and customer sites are defined in either Oracle Receivables or Oracle Order Management. Included in the definition is the EDI Location Code that trading partners agree to exchange to represent the full detailed address. Often they do not send the full address, but just the EDI Location Code. This is a critical data field to Oracle e-Commerce Gateway.
The EDI Location Code is the link between a customer/customer site in Oracle E-Business Suite and the trading partner site definition in Oracle e-Commerce Gateway. This enables Oracle e-Commerce Gateway to access the detailed data about the customer or customer site in the base Oracle E-Business Suite applications without maintaining the detail data in Oracle e-Commerce Gateway.
To ensure that the trading partner link between Oracle e-Commerce Gateway and Oracle E-Business Suite is set up properly, verify that the customer/customer site and the EDI Location Code in Oracle E-Business Suite is the correct customer/customer site selected for the Trading Partner definition in Oracle e-Commerce Gateway. The selected customer/customer site and the EDI Location Code defined in Oracle E-Business Suite are displayed in the Define Trading Partners window, Assignment tab. If the data is not what you intend it to be, you must make the appropriate changes for the transaction to be extracted for the correct trading partner. This could involve either altering the customer/customer site in the base Oracle E-Business Suite application, or assigning a different customer/customer site to that EDI Location Code in Oracle e-Commerce Gateway.
Note that if the trading partner is associated with multiple operating units, then the transaction to be extracted is for a specified operating unit only linked to your responsibility. To extract all the data for multiple operating units, you still need to run the program one by one by specifying different operating unit each time when you run the program. See the Outbound Transactions chapter in the Oracle e-Commerce Gateway User's Guide for details.
Refer to the Trading Partner chapter for recommendations on selecting the correct trading partner EDI Location Code for the control record 0010 for the transaction in the transaction interface file.
The 810/INVOIC transaction supports invoices, credit memos and debit memos. In addition, you can use the 812/CREADV-DEBADV transaction to send just the credit memos and debit memos. The method you chose is dependent on your agreement with your supplier. Any credit memo/debit memo extracted using the 812/CREADV-DEBADV transaction will not be eligible for extraction by the 810/INVOIC transaction.
The outbound credit memo/debit memo transaction is controlled by five database views which are defined according to the Oracle Receivable data model for credit and debit memos. The five views contain variables which are dynamically set based on your responses to the extract program parameters (refer to Oracle e-Commerce Gateway User's Guide, Outbound Transactions chapter for a list of the program parameters).
The five database views are as follows:
ECE_CDMO_ALLOWANCE_CHARGES_V
ECE_CDMO_HEADERS_1_V
ECE_CDMO_HEADER_V
ECE_CDMO_LINE_TAX_V
ECE_CDMO_LINE_V
The ECE_CDMO_HEADERS_V view is used to identify which credit/debit memos are eligible for extraction. The extract criteria are as follows:
Trading partner is defined
Credit memo or debit memo enabled for the trading partner
Transaction type is credit memo or debit memo
Credit memo or debit memo has not been printed or previously extracted
Credit memo or debit memo status is COMPLETE
Credit memo or debit memo print option is "PRI"
Print pending is "Y"
If necessary, you can use SQLPLUS to verify if there are any eligible documents to be extracted. To do so, you must first set the organization context and then issue the SQL count function as follows:
SQLPLUS>execute fnd_client_info.set_org_context("<Org number>"); SQLPLUS>select count(*) ECE_CDMO_HEADERS_V;
Review all your set ups if the count value is 0 as this indicates there are no eligible documents to be extracted.
Once an eligible credit memo or debit memo is successfully extracted and written to the transaction interface file, it is marked by Oracle e-Commerce Gateway to prevent it from subsequent extraction. The following table lists the fields updated in the RA_CUSTOMER_TRX table based on an excerpt of code in the UPDATE_AR procedure defined in the ECECDMOB.pls file:
Column | Value Updated To |
---|---|
EDI_PROCESSED_FLAG | Y |
EDI_PROCESSED_STATUS | ED |
LAST_UPDATE_DATE | SYSDATE |
PRINTING_COUNT | increment 1 |
PRINTING_LAST_PRINTED | SYSDATE |
PRINTING_ORIGINAL_DATE | SYSDATE |
PRINTING_PENDING | N |
The implementation of any transaction requires some set up in Oracle E-Business Suite and Oracle e-Commerce Gateway. This section focuses on the application setups necessary to implement a transaction that integrates with Oracle Release Management. The Oracle Release Management transactions are listed in the following table:
Transaction Name | Direction | Transaction Code | ASC X12 | EDIFACT | ODETTE |
---|---|---|---|---|---|
Planning/Material Release | Inbound | SPSI | 830 | DELFOR | DELINS |
Shipping Schedule | Inbound | SSSI | 862 | DELJIT | DELINS/ CALDEL/ KANBAN |
Production Sequence Schedule | Inbound | PSQI | 866 | DELJIT | SYNCRO/ SYNPAC |
The topics covered for inbound transactions include the following:
Trading Partner Link to Oracle e-Commerce Gateway
Oracle e-Commerce Gateway Required Fields
Review Oracle e-Commerce Gateway Exceptions
Resolve Oracle e-Commerce Gateway Exceptions
Relevant Application Profile Options and Setups
Relevant Application Open Interface Required Fields
Review Application Open Interface Exceptions
Return Application Advice to Trading Partner (if appropriate)
Resolve Application Open Interface Exceptions
The transaction requirements may change when enhancements are made such as additional data added to the transaction. Current transaction details can be found on Oracle Support's web site.
Current detail record layouts are reported via the Transaction Layout Definition Report and the Interface File Data Report.
Refer to Oracle Release Management Transaction Summaries for a diagram of the record hierarchy, record looping structure, and record summaries.
The Planning/Material Release transactions can include both forecast and firm requirements. The planning schedule with release capability transaction may be used in various ways or in a combination of ways, such as
a simple forecast
a forecast with the buyer's authorization for the seller to commit resources, such as labor or material
a forecast that is also used as an order release mechanism, containing such elements as resource authorizations, period-to-date cumulative quantities, and specific ship delivery patterns for requirements that have been represented in “buckets,” such as weekly, monthly, or quarterly
The Shipping Schedule transaction contains firm delivery information and is intended by the customer to refine requirements already presented in the planning schedule. It facilitates the practice of Just-In-Time (JIT) manufacturing by providing the customer with a mechanism to issue precise shipping schedule requirements on a more frequent basis, such as daily shipping schedules versus weekly planning schedules.
Customers and customer sites are defined in Oracle Accounts Receivable and Oracle Order Management. Included in the definition is the EDI Location Code. The EDI Location Code is a code that represents a customer's full detailed address. Often the customer does not send the full address, but just the EDI Location Code. This is a critical data field to Oracle e-Commerce Gateway.
The EDI Location Code is the link between a customer address in Oracle E-Business Suite and the trading partner site definition in Oracle e-Commerce Gateway. This enables Oracle e-Commerce Gateway to access the detailed data about the customer address in the base Oracle E-Business Suite applications without maintaining the detail data in Oracle e-Commerce Gateway.
To ensure that the trading partner link between Oracle e-Commerce Gateway and Oracle E-Business Suite is set up properly, verify that the customer address and the EDI Location Code in Oracle E-Business Suite is the correct customer address selected for the Trading Partner definition in Oracle e-Commerce Gateway. The selected customer address and the EDI Location Code defined in Oracle E-Business Suite are displayed in the Assignment tab of the Define Trading Partners window. If the data is not correct, you must make the appropriate changes for the transaction to be imported for the correct trading partner. This could involve either altering the customer address in the base Oracle E-Business Suite application, or assigning a different customer address to that EDI Location Code in Oracle e-Commerce Gateway.
Refer to the Trading Partner chapter for recommendations on selecting the correct trading partner EDI Location Code for the control record 0010 for the transaction.
Note: On inbound EDI Demand Transactions, the customer also sends a Supplier Code. The Supplier Code identifies the Supplier to the customer. The Supplier Codes are defined in the Release Management Processing Rules Window. They are not defined in the e-Commerce Gateway to be used by this transaction process. Refer to the Release Management Implementation Manual and the Release Management User's Guide for additional information on setting up the Supplier Code.
The following table lists the Oracle e-Commerce Gateway required fields. These fields are required to authenticate the trading partner and transaction. If the required data is not provided in the transaction, the Oracle e-Commerce Gateway import process fails the transaction, and an exception message will display 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 Release Management Interface tables to be processed by the Release Management Demand Processor.
Oracle e-Commerce Gateway Column Name for Required Fields | Record Number | Position Number | Note |
---|---|---|---|
TEST_INDICATOR | 0010 | 20 | "T" or "P" |
DOCUMENT_CODE | 0010 | 60 | Schedule Reference number |
TP_TRANSLATOR_CODE | 0010 | 70 | Translator identifier for this trading partner |
TP_LOCATION_CODE | 0010 | 80 | The EDI Location Code |
TRANSACTION_DATE | 0010 | 120 | Schedule generation date |
EDI_CONTROL_NUM_1 | 0010 | 160 | Control number from the electronic envelope, such as the X12 (ISA) or EDIFACT (UNB), assigned by the customer EDI translator for auditing. |
EDI_CONTROL_NUM_2 | 0010 | 170 | The second level control number from the electronic transaction, such as the X12 (GS), assigned by the customer EDI translator for auditing. |
EDI_CONTROL_NUM_3 | 0010 | 180 | The third level control number from the electronic transaction, such as the X12 (ST), assigned by the customer EDI translator for auditing. |
RLM_SCHEDULE_TYPE_INT | 1000 | 10 | Schedule types: Planning, Shipping, or Sequenced |
SCHEDULE_PURPOSE_INT | 1000 | 70 | Transaction Purpose: Original, Replace, Cancel, etc. |
SCHEDULE_SOURCE | 1000 | 280 | EDI or manual transaction for this schedule, such as 830, 862, 866, DELFOR, MANUAL, etc. |
ITEM_DETAIL_TYPE_INT | 4000 | 10 | Past Due, Firm, Forecast, Authorization, Shipment/Receipt Data, or Other |
ITEM_DETAIL_SUBTYPE_INT | 4000 | 70 | Bucket Type, Type of Authorization, or Cumulative or Last Shipment/Receipt |
DATE_TYPE_CODE_INT | 4000 | 130 | Type of Start/End Date: SHIP, DELIVER, RECEIVED, FROM_TO |
START_DATE_TIME_DET | 4000 | 180 | Customer specified date/time |
QTY_TYPE_CODE_INT | 4000 | 210 | ACTUAL or CUMULATIVE |
ITEM_DETAIL_QUANTITY | 4000 | 270 | Requested quantity |
UOM_CODE_INT_D | 4000 | 280 | Abbreviated unit of measure code |
DOCUMENT_CODE
This column represents the customer assigned schedule reference or release number.
TEST_INDICATOR
This column represents the test or production indicator from the Trading Partner. If this value does not match the test or production indicator associated with the trading partner defined in Oracle e-Commerce Gateway, a process rule exception is detected, and an exception message will display in the View Staged Documents window.
The valid values are “T” for test and “P” for production.
Note: The TEST_INDICATOR is examined by the Demand Processor to determine how far the transaction can proceed through the application. Transactions with a TEST_INDICATOR value of “T” are validated and archived, but no further processing is done. In the Release Management Workbench, Test Transactions require the “Test” check box to be checked.
This allows test transactions to be tested in a production environment for front-end data validation. To fully test a transaction through a test database environment, the transactions must be flagged as production to go beyond just the validation.
This Demand Processor feature facilitates setup and implementation for inbound demand schedules with new trading partners.
TP_TRANSLATOR_CODE, TP_LOCATION_CODE (EDI Location Code)
The two columns in combination uniquely identify a Trading Partner in Oracle e-Commerce Gateway. Once the trading partner definition is accessed, Oracle e-Commerce Gateway can verify whether the transaction is enabled for the Trading Partner.
If this trading partner is not defined in Oracle e-Commerce Gateway, a process rule exception is detected, and an exception message will display in the View Staged Documents window.
Refer to the Trading Partner chapter for details on how to properly define your trading partners and get a better understanding of how these fields are used in the process.
TRANSACTION_DATE
This column is the schedule generation date that is sent in the transmission.
EDI_CONTROL_NUM_1
EDI control number 1 is the control number from the electronic envelope, such as the X12 (ISA) or EDIFACT (UNB). It is assigned by the customer EDI translator for auditing.
EDI_CONTROL_NUM_2
EDI control number 2 is the second level control number from the electronic transaction, such as the X12 (GS). It is assigned by the customer EDI translator for auditing
EDI_CONTROL_NUM_3
EDI control number 3 is the third level control number from the transaction, such as the X12 (ST). It is assigned by the customer EDI translator for auditing.
Record 1000
Note: The fields with the suffix “_INT” are the internal codes that must be defined in the Oracle Release Management. One to five external codes from the transaction may be entered into their corresponding external code fields that have the suffixes “_EXT1” through “_EXT5” for the same column name. Either the e-Commerce Gateway's Code Conversion process can derive the internal codes to place them in the open interface tables, or the values may be placed in the internal code fields on the file if they are derived from another process. Only the internal codes are referred to below for simplicity.
RLM_SCHEDULE_TYPE_INT
This column represents the Schedule type.
Valid Values: PLANNING, SHIPPING, SEQUENCED
SCHEDULE_PURPOSE_INT
This column represents the Schedule Purpose code.
Valid Values: ADD, ORIGINAL, REPLACE, CANCEL, CHANGE, DELETE, and CONFIRMATION.
SCHEDULE_SOURCE
This column represents the EDI or manual transaction source for this schedule, such as 830, 862, 866, DELFOR, MANUAL.
Refer to the Release Management Implementation Manual and the Release Management User's Guide for detailed descriptions of all codes and the impact on processing for each code value.
Record 4000
DATE_TYPE_CODE_INT
Date Type is used by the Demand Processor to determine how the start date and end date on each schedule line should be interpreted.
For Demand Detail Types (Past Due, Firm, and Forecast), the Date Type is critical, because it indicates whether the schedule demand is shipment-based or delivery-based.
For other Detail Types (Authorizations, Shipped/Received data, and Other), the Date Type is simply informational, and is not used in processing.
Valid Values:
SHIP
DELIVER
RECEIVED
FROM_TO
ITEM_DETAIL_TYPE_INT
Detail Type is used by the Demand Processor to determine how the schedule line itself should be interpreted.
Valid Values:
0 = Past Due Firm
1 = Firm Demand
2 = Forecast Demand
3 = Authorization
4 = Shipment/Receipt Data
5 = Other
ITEM_DETAIL_SUBTYPE_INT
Detail Subtype is used by the Demand Processor to determine how the schedule line itself should be interpreted in context of its corresponding Detail Type.
Each Item Detail Type has a corresponding list of valid Item Detail Subtypes, listed in the table below:
If ITEM_DETAIL_TYPE Equals | then ITEM_DETAIL_SUBTYPE contains |
---|---|
0, 1, 2 | 1 = Day 2 = Week 3 = Flexible 4 = Month 5 = Quarter |
3 | FINISHED = Finished Goods MATERIAL = Raw Material LABOR_MATERIAL = Labor and Material LABOR = Labor PRIOR_CUM_REQ = Prior Cumulative Required |
4 | SHIPMENT = Shipment RECEIPT = Receipt CUM = Customer CUM CUM_REJECTED = CUM Rejected |
5 | AHEAD_BEHIND = Ahead/Behind INVENTORY_BALANCE = Inventory Balance HOLDOUT_QTY = In Holdout |
START_DATE_TIME_DET
Customer specified date/time, as transmitted by the customer on the transaction. Format: YYYYMMDD HHMMSS
QTY_TYPE_CODE_INT
Quantity Type is used by the Demand Processor to determine how the quantity on schedule lines should be interpreted in context of its Detail Type and Detail Subtype.
Valid Values:
ACTUAL
CUMULATIVE
ITEM_DETAIL_QUANTITY
This column represents the requested quantity. The following table lists the ITEM_DETAIL_QUANTITY values for corresponding ITEM_DETAIL_TYPE:
If ITEM_DETAIL_TYPE Equals | then ITEM_DETAIL_QUANTITY contains |
---|---|
0, 1, 2 | Demand Quantity |
3 | Authorization Quantity |
4 | Shipped, Received, or CUM Quantity |
5 | Miscellaneous Quantity, such as Ahead/Behind or Inventory Balance |
UOM_CODE_INT_D
This is the abbreviated unit of measure codes defined in Oracle Inventory.
Oracle e-Commerce Gateway provides seeded Code Conversions for external values associated with the standards for values that are supported within the Demand Processor. The following Code Conversion Categories are applicable to any or all of the inbound demand schedule transactions.
Data | Demand Processor Column Name |
---|---|
Date Type | RLM_DATE_TYPE |
Detail Type | RLM_DTL_TYPE |
Detail Subtype | RLM_DTL_SUBTYP |
Quantity Type | RLM_QTY_TYPE |
Schedule Type | RLM_SCHED_TYPE |
Purpose Code | RLM_TRX_PURP |
Ship Delivery Pattern | RLM_SHP_DEL_CODE |
Unit of Measure | UOM |
The e-Commerce Gateway transaction has four levels of data. The Demand Processor has two interface tables, one for the Header level data and one for the Line level data. The Line level interface table is loaded with both the item data, schedule detail, and schedule subdetail data from the e-Commerce Gateway transaction. This structure is shown in the table below.
For each 4000 record level data that is loaded into the Line level interface table, the corresponding 2000 record data will also be loaded.
Schedule subdetail data may also be in the transaction. For each 5000 record level data that is loaded into the Line level interface table, the corresponding 2000 record level data and 4000 record level data will also be loaded to create a single entry in the Line level interface table.
Record | Subrecord | Subrecord | e-Commerce Gateway File Data Content (Four Levels of Data) |
Demand Processor Level (Two Levels of Data: HEADER and LINE) |
---|---|---|---|---|
1000 | Schedule Header | HEADER | ||
2000 | Item1 data | (Copy data to all its Schedule Detail below) | ||
2000 | 4000 | Schedule1 data (for first schedule) | LINE (Item1/Schedule1) | |
2000 | 4000 | Schedule2 data (for second schedule) | LINE (Item1/Schedule2) | |
2000 | 4000 | Schedule3 data (for third schedule) | LINE (Item1/Schedule3) | |
2000 | Item2 data | (Copy data to all its Schedule Detail below) | ||
2000 | 4000 | Schedule1 detail (for first schedule) | (Copy schedule for its subdetail data below) | |
2000 | 4000 | 5000 | Schedule1 subdetail1 | LINE (Item2+Schedule1+ subdetail1) |
2000 | 4000 | 5000 | Schedule1 subdetail2 | LINE (Item2+Schedule1+ subdetail2) |
2000 | 4000 | 5000 | Schedule1 subdetail3 | LINE (Item2+Schedule1+ subdetail3) |
2000 | 4000 | Schedule2 data (for second schedule) | (Copy schedule for its subdetail data) | |
2000 | 4000 | 5000 | Schedule2 subdetail1 | LINE (Item2+Schedule2+ subdetail1) |
2000 | 4000 | 5000 | Schedule2 subdetail2 | LINE (Item2+Schedule2+ subdetail2) |
2000 | 4000 | Schedule3 data (for third schedule) | LINE (Item2/Schedule3) | |
2000 | 4000 | Schedule3 data (for fourth schedule) | LINE (Item2/Schedule4) |
Demand Schedule Detail Records
Record 4000 contains the schedule detail as noted in the following table:
Case | Record Num | ITEM_DETAIL_TYPE_INT Pos. 10 | ITEM_DETAIL_SUBTYPE_INT Pos. 70 | DATE_TYPE_CODE_INT Pos. 130 | START_DATE_TIME_DET Pos. 190 | END_DATE_TIME_DET Pos. 200 | QTY_TYPE_CODE_INT Pos. 210 | ITEM_DETAIL_QUANTITY Pos. 270 | UOM_INT_D Pos. 280 |
---|---|---|---|---|---|---|---|---|---|
1 | 4000 | 0 | 1 | SHIP | 20000626 000000 | 20000626 000000 | ACTUAL | 90 | EA |
2 | 4000 | 1 | 1 | DELIVER | 20000724 000000 | 20000724 000000 | CUMULATIVE | 26000 | EA |
3 | 4000 | 2 | 2 | DELIVER | 20000814 000000 | 20000814 000000 | ACTUAL | 100 | EA |
4 | 4000 | 4 | RECEIPT | RECEIVED | 20000626 000000 | 20000626 000000 | ACTUAL | 25 | EA |
5 | 4000 | 4 | CUM | RECEIVED | 20000626 000000 | 20000626 000000 | CUMULATIVE | 40000 | EA |
6 | 4000 | 4 | SHIPMENT | SHIP | 20000626 000000 | 20000626 000000 | ACTUAL | 150 | EA |
Sample 4000 Demand Schedule Records
Case 1:
This is a past due demand record showing that a quantity of 90 was to be shipped on June 26, 2000.
Case 2:
This is firm demand record showing that a cumulative quantity of 26000 is to be delivered July 24, 2000. The discrete quantity is derived by the demand processor.
Case 3:
This is a forecast demand record showing that a weekly bucket quantity of 100 is forecast to be requested for delivery the week of August 14, 2000.
Case 4:
This is a Shipment/Receipt record showing that the customer received a shipment of 25 on June 26, 2000. (The Shipment/Receipt record can also have the Supplier's delivery number in the ITEM_DETAIL_REF_VALUE_1 field in Record 5010.)
Case 5:
This is a Cumulative Shipment/Receipt record showing that the customer has received a cumulative quantity of 40000 as of June 26, 2000.
Case 6:
This is a Shipment/Receipt record showing that the customer received notification of a shipment of 150 on June 26, 2000.
Record 5000
Record 5000 contains the schedule subdetail as noted in the following table:
Case | Record 5000 | START_DATE_TIME_DET_SD Pos. 10 |
CUST_SHIP_TO_EXT_SD Pos. 20 |
ITEM_DETAIL_QUANTITY_SD Pos. 30 |
UOM_CODE_INT_SD Pos. 40 |
---|---|---|---|---|---|
4000* | |||||
1 | 5000 | 20000626 070000 | NULL | 100 | NULL |
2 | 5000 | 20000626 100000 | NULL | 50 | NULL |
Note: Record 4000 data precedes its related 5000 series records.
Cases 1 & 2 are examples of Just-In-Time (JIT) records.
Case 1:
This is a JIT record for a quantity of 100 to be delivered at 7:00 AM on June 26, 2000.
Case 2:
This is a JIT record for a quantity of 50 to be delivered at 10:00 AM on June 26, 2000.
Customer ship-to external codes and unit of measurement (UOM) codes on Record 5000 are required only if they are different than previously indicated at the item level on Record 4000. Usually they are not different. You may place the codes in these fields at any time.
Record 5010
Record 5010 contains the schedule subdetail that consists of three pairs of Item Detail Reference Values and its corresponding Item Detail Reference Codes. The Item Detail Reference Code is a qualifier to indicate the type of data in its corresponding Item Detail Reference Values. The qualifier may be the data qualifier defined in the EDI standard such as X12 or EDIFACT.
Note: You can have a Record 5010 without a Record 5000 and vice versa. They are independent of each other.
An example of a Record 5010 is shown in the table below:
Record 5010 | ITEM_DETAIL_REF_CODE_1_SD Pos. 10 |
ITEM_DETAIL_REF_VALUE_1_SD Pos. 20 |
ITEM_DETAI_REF_CODE_1_SD Pos. 30 |
ITEM_DETAIL_REF_VALUE_2_SD Pos. 40 |
ITEM_DETAIL_REF_CODE_2_SD Pos. 50 |
ITEM_DETAIL_REF_VALUE_3_SD Pos. 60 |
---|---|---|---|---|---|---|
4000* | ||||||
5010 | XX | 29556 |
Note: Record 4000 data precedes its related 5000 series records
Example Record 5010
One use of the Record 5010 is to state the supplier's shipper number (example 29556), when Record 4000 is used for a last shipment received record.
Use the Oracle e-Commerce Gateway View Staged Documents window to review the Oracle e-Commerce Gateway transaction exceptions. Once the exceptions are identified and resolved, you can submit the transaction for reprocessing, ignore the exception during reprocessing, or delete the transaction. Select the option in the View Staged Documents window.
To resolve Oracle e-Commerce Gateway exceptions, you can either correct the set up data in Oracle e-Commerce Gateway or Oracle E-Business Suite, or ask the Trading Partner to send a corrected transaction.
If the Trading Partner sends a corrected transaction, you must delete the erroneous transaction from Oracle e-Commerce Gateway's staging tables using the View Staged Documents window.
The following is a list of the Release Management setups related to the Release Management Demand Processor. Refer to the Oracle Release Management Implementation Manual and Oracle Release Management User's Guide for details.
RLM: MRP Forecast Selection List
If defined, the MRP Forecast Selection List lists all the names of Forecasts to which inbound Release Management forecast demand can be assigned.
If set to “none,” forecast data will not be imported into Planning.
RLM: CUM Management Enabled
Determines whether or not CUM Management is enabled at the Site level. To use the CUM Management features of Oracle Release Management, this must be set to Yes.
RLM: Print CUM Data on Shipping Documents
Determines whether or not CUM Data should be printed on shipping documents.
RLM: Debug Mode
Determines if a debug file is written for running the Demand Processor.
Valid Values are:
0 = highest debug level
Null = no debug file is generated (This is the default value)
RLM: Workflow Enabled
Determines whether or not the Demand Processor is enabled to run in workflow mode.
ECE: SPSI-Enabled
Determines whether or not inbound planning schedule transaction is enabled.
ECE: SSSI-Enabled
Determines whether or not inbound shipping schedule transaction is enabled.
ECE: PSQI-Enabled
Determines whether or not inbound sequenced shipping schedule transaction is enabled.
Release Management Processing Rules
There are five categories of Processing Rules attributes:
Demand Management
Demand Fences
Order Management
CUM Management
General
These five categories are defined for each Ship-From/Ship-To business entity for which Release Management will process demand.
If terms are not defined at the optional lower levels, they will default from higher levels. There are three levels where the Processing Rules can be defined:
Ship-From / Customer (mandatory)
Ship-From / Address (optional)
Ship-From / Customer Item (optional; can be linked to either of the other two levels)
The Release Management Demand Processor is used to process Planning/Material Release, Shipping Schedule, and Production Sequence Schedule transactions. It validates the incoming data entered into the Release Management Interface tables by the Oracle e-Commerce Gateway import program.
The following is a list of the Release Management Demand Processor required fields. These fields are required for the Release Management Demand Processor to successfully process and move the data from the Release Management Interface tables into the Oracle Order Management and Oracle Planning base application tables.
Required fields noted as derived or hardcoded do not require a value in the transaction on the transaction interface file since the values are determined by the Oracle e-Commerce Gateway process.
RLM_INTERFACE_HEADER Table
The following table lists the required fields for the RLM_INTERFACE_HEADER table:
Oracle E-Business Suite Column Name for Required Fields | Cond. | Oracle e-Commerce Gateway Column Name | Hard coded/ Derived | Record Number | Position Number |
---|---|---|---|---|---|
EDI_CONTROL_NUM_3 | 0010 | 180 | |||
RLM_SCHEDULE_TYPE_INT | 1000 | 010 | |||
SCHEDULE_PURPOSE_INT | 1000 | 070 | |||
SCHEDULE_SOURCE | 1000 | 280 | |||
CREATED_BY | Derived | ||||
CREATION_DATE | Derived | ||||
HEADER_ID | HEADER_ID | Derived | |||
LAST_UPDATE_DATE | Derived | ||||
LAST_UPDATED_BY | Derived | ||||
PROCESS_STATUS | Derived | ||||
REQUEST_ID | Derived |
EDI_CONTROL_NUM_3
EDI control number 3 is the third level control number from the transaction, such as the X12 (ST). It is assigned by the customer's EDI translator for auditing.
HEADER_ID
This column represents a sequence generated unique identifier. It is system generated when the interface table is loaded.
RLM_SCHEDULE_TYPE_INT
This column represents the Schedule type.
Valid Values: PLANNING, SHIPPING, SEQUENCED
SCHEDULE_PURPOSE_INT
This column represents the Schedule Purpose code.
Valid Values: ADD, ORIGINAL, REPLACE, CANCELLATION, CHANGE, DELETE, and CONFIRMATION.
SCHEDULE_SOURCE
This column represents the EDI or manual transaction source for this schedule, such as 830, 862, 866, DELFOR, MANUAL.
PROCESS_STATUS
Indicates the current processing status of a record.
Valid Values:
1 = Do not process
2 = Waiting to be processed
3 = In process
4 = Error
5 = Processed
6 = Processed with errors
REQUEST_ID
This column represents a sequence-generated unique identifier assigned to the concurrent request for the demand processor.
Update Columns:
CREATED_BY
This code identifies who loaded this transaction into the Demand Processor tables. The code is set to the Requester ID associated with the concurrent manager request that processed this transaction.
CREATION_DATE
This is the date that the e-Commerce Gateway loaded the transaction into the Demand Processor table entries. This date is set to the system date.
LAST_UPDATE_DATE
This date is equal to the CREATION_DATE for this transaction. This date is set to the system date.
LAST_UPDATED_BY
This code is equal to the CREATED_BY code for this transaction.
RLM_INTERFACE_LINES Table
The following table lists the required fields for the RLM_INTERFACE_LINES table:
Oracle E-Business Suite Column Name for Required Fields | Cond. | Oracle e-Commerce Gateway Column Name | Hard coded/ Derived | Record Number | Position Number |
---|---|---|---|---|---|
ITEM_DETAIL_TYPE_INT | 4000 | 010 | |||
ITEM_DETAIL_SUBTYPE_INT | 4000 | 070 | |||
DATE_TYPE_CODE_INT | 4000 | 130 | |||
START_DATE_TIME_DET | 4000 | 180 | |||
QTY_TYPE_CODE_INT | 4000 | 210 | |||
ITEM_DETAIL_QUANTITY | 4000 | 270 | |||
UOM_CODE_INT_D | 4000 | 280 | |||
CREATED_BY | Derived | ||||
CREATION_DATE | Derived | ||||
HEADER_ID | Derived | ||||
LAST_UPDATE_BY | Derived | ||||
LAST_UPDATE_DATE | Derived | ||||
LINE_ID | Derived | ||||
LINE_SOURCE | Derived | ||||
PROCESS_STATUS | Derived | ||||
REQUEST_ID | Derived | ||||
SCHEDULE_ITEM_NUM | Derived | ||||
SCHEDULE_LINE_NUM | Derived |
LINE_ID
This column represents a sequence-generated unique identifier. It is system-generated when the interface table is loaded.
HEADER_ID
This column represents a sequence-generated unique identifier. It is system-generated when the interface table is loaded.
DATE_TYPE_CODE_INT
Date Type is used by the Demand Processor to determine how the start date and end date on each schedule line should be interpreted.
For Demand Detail Types (Past Due, Firm and Forecast), the Date Type is critical, because it indicates whether the schedule demand is shipment-based or delivery-based.
For other Detail Types (Authorizations, Shipped/Received Information, and Other) the Date Type is simply informational, and is not used in processing.
Valid Values:
SHIP
DELIVER
RECEIVED
FROM_TO
ITEM_DETAIL_TYPE_INT
Detail Type is used by the Demand Processor to determine how the schedule line itself should be interpreted.
Valid Values:
0 = Past Due Firm
1 = Firm Demand
2 = Forecast Demand
3 = Authorization
4 = Shipment/Receipt Data
5 = Other
ITEM_DETAIL_SUBTYPE_INT
Detail Subtype is used by the Demand Processor to determine how the schedule line itself should be interpreted in context of its corresponding Detail Type.
Each Item Detail Type has a corresponding list of valid Item Detail Subtypes as shown in the following table:
If ITEM_DETAIL_TYPE_INT Equals |
then ITEM_DETAIL_SUBTYPE_INT Contains |
---|---|
0, 1, 2 | 1 = Day 2 = Week 3 = Flexible 4 = Month 5 = Quarter |
3 | FINISHED = Finished Goods MATERIAL = Raw Material LABOR_MATERIAL = Labor & Material LABOR = Labor PRIOR_CUM_REQ = Prior Cumulative Required |
4 | SHIPMENT = Shipment RECEIPT = Receipt CUM = Customer CUM CUM_REJECTED = CUM Rejected |
5 | AHEAD_BEHIND = Ahead/Behind INVENTORY_BAL = Inventory Balance HOLDOUT_QTY = In Holdout |
ITEM_DETAIL_QUANTITY
This column represents the requested quantity. The ITEM_DETAIL_QUANTITY value corresponds to the ITEM_DETAIL_TYPE_INT as shown in the following table:
If ITEM_DETAIL_TYPE_INT |
then ITEM_DETAIL_QUANTITY |
---|---|
0, 1, 2 | Demand Quantity |
3 | Authorization Quantity |
4 | Shipped, received or cum quantity |
5 | Miscellaneous quantity, such as ahead/behind, inventory balance |
QTY_TYPE_CODE_INT
Quantity Type is used by the Demand Processor to determine how the quantity on schedule lines should be interpreted in context of its Detail Type and Detail Subtype.
Valid Values:
ACTUAL
CUMULATIVE
START_DATE_TIME_DET
Customer specified date/time, as transmitted by the customer on the transaction. Format: YYYYMMDD HHMMSS
UOM_CODE_INT_D
This is the abbreviated unit of measure code as defined in Oracle Inventory.
LINE_SOURCE
Schedule source at line level since lines can also be manually entered.
PROCESS_STATUS
Indicates the current processing status of a record.
1 = Do not process
2 = Waiting to be processed
3 = In process
4 = Error
5 = Processed
6 = Processed with errors
REQUEST_ID
This column represents a sequence-generated unique identifier assigned to the concurrent request for the demand processor.
Update Columns:
CREATED_BY
This code identifies who loaded this transaction into the Demand Processor tables. The code is set to the Requester ID associated with the concurrent manager request that processed this transaction.
CREATION_DATE
This is the date that the e-Commerce Gateway loaded the transaction into the Demand Processor table entries. This date is set to the system date.
LAST_UPDATE_DATE
This date is equal to the CREATION_DATE for this transaction. This date is set to the system date.
LAST_UPDATED_BY
This code is equal to the CREATED_BY code for this transaction.
The Oracle Release Management Demand Processor Exception Handling lets the supplier accomplish the following tasks:
Receive error messages on problems that have halted demand processing
Receive warning messages on potential demand processing problems
Receive information messages on noteworthy situations detected by the Demand Processor
Generate exceptions report automatically after demand processing completion
Review real time report on inbound demand processing exceptions
View exceptions generated for any schedule via the Release Management Workbench until purged
Correct set-up data to allow successful demand processing
Correct schedule data to allow successful demand processing
Validate customer demand for data that impacts demand processing
You can review all exceptions detected by the Release Management Demand Processor in the Release Management Demand Workbench or on the Demand Management Exception Report.
Refer to the Oracle Release Management User's Guide for more information on the Demand Management Exception Report.
To resolve Release Management Demand Processor exceptions, you can either correct the setup data in Oracle E-Business Suite, make changes to the original schedule on the Release Management Demand Workbench, or ask the supplier to resend the transaction with the corrected values.
If you chose to update Oracle E-Business Suite data, you can resubmit the Release Management Demand Processor to revalidate the transaction.
Customers, addresses and locations are defined in Oracle Accounts Receivable and Oracle Order Management. Included in the definition is the EDI Location Code. The EDI Location Code is a code that represents a customer's full detailed address. Often the customer does not send the full address, but just the EDI Location Code. This is a critical data field to Oracle e-Commerce Gateway.
The EDI Location Code is the link between a customer address in Oracle E-Business Suite and the trading partner site definition in Oracle e-Commerce Gateway. This enables Oracle e-Commerce Gateway to access the detailed data about the customer address in the base Oracle E-Business Suite applications without maintaining the detail data in Oracle e-Commerce Gateway.
To ensure that the trading partner link between Oracle e-Commerce Gateway and Oracle E-Business Suite is set up properly, verify that the customer address and the EDI Location Code in Oracle E-Business Suite is the correct customer address selected for the Trading Partner definition in Oracle e-Commerce Gateway. The selected customer address and the EDI Location Code defined in Oracle E-Business Suite are displayed in the Assignment tab of the Define Trading Partners window. If the data is not correct, you must make the appropriate changes for the transaction to be imported for the correct trading partner. This could involve either altering the customer address in the base Oracle E-Business Suite application, or assigning a different customer address to that EDI Location Code in Oracle e-Commerce Gateway.
Note: On inbound EDI Demand Transactions, the customer also sends a Supplier Code. The Supplier Code identifies the Supplier to the customer. The Supplier Codes are defined in the Release Management Processing Rules Window. They are not defined in the e-Commerce Gateway to be used by this transaction process. Refer to Oracle Release Management Implementation Manual and Oracle Release Management User's Guide for additional information on setting up the Supplier Code.
The following table lists the Oracle e-Commerce Gateway required fields. These fields are required to authenticate the trading partner and transaction. If the required data is not provided in the transaction, the Oracle e-Commerce Gateway import process fails the transaction, and an exception message will display 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.
Oracle e-Commerce Gateway Column Name for Required Fields | Record Number | Position Number | Note |
---|---|---|---|
TEST_INDICATOR | 0010 | 20 | "T" or "P" |
DOCUMENT_CODE | 0010 | 60 | Schedule Reference Number |
TP_TRANSLATOR_CODE | 0010 | 70 | Translator identifier for this Trading Partner |
TP_LOCATION_CODE | 0010 | 80 | The EDI Location Code |
TRANSACTION_DATE_TIME | 0010 | 120 | Schedule Generation date |
EDI_CONTROL_NUM_1 | 0010 | 160 | Level 1 control number assigned by the customer's EDI translator for auditing. |
EDI_CONTROL_NUM_2 | 0010 | 170 | Level 2 control number assigned by the customer's EDI translator for auditing. |
EDI_CONTROL_NUM_3 | 0010 | 180 | Level 3 control number assigned by the customer's EDI translator for auditing. |
RLM_SCHEDULE_TYPE_INT | 1000 | 10 | Planning, Shipping, or Sequenced |
SCHEDULE_PURPOSE_INT | 1000 | 70 | Original, Replace, Cancel, etc. |
SCHEDULE_SOURCE | 1000 | 150 | EDI or manual transaction for this schedule, i.e. 830, 862, 866, DELFOR, MANUAL, etc. |
ITEM_DETAIL_TYPE_INT | 2000 | 10 | Past Due, Firm, Forecast, Authorization, Shipment/Receipt Data, or Other |
ITEM_DETAIL_SUBTYPE_INT | 2000 | 70 | Bucket type, type of Authorization, or cumulative or last shipment/receipt |
DATE_TYPE_CODE_INT | 2000 | 130 | Ship, Deliver, Received, From_To |
START_DATE_TIME | 2000 | 190 | Customer specified date/time |
QTY_TYPE_CODE_INT | 2000 | 210 | Actual or cumulative |
ITEM_DETAIL_QUANTITY | 2000 | 270 | Requested quantity |
UOM_CODE_INT | 2040 | 10 | Abbreviated unit of measure code |
SCHEDULE_LINE_NUM | Derived | Unique identifier of a line within a schedule | |
CUSTOMER_ITEM_EXT | 3100 | 20 | The customer item as defined in their application. This code is consider an externally defined code, hence, the EXT suffix. |
Control Record 0010
TEST_INDICATOR
This column represents the test or production indicator from the Trading Partner. If this value does not match the test or production indicator associated with the trading partner defined in Oracle e-Commerce Gateway, a process rule exception is detected, and an exception message will display in the View Staged Documents window.
The valid values are “T” for test and “P” for production.
Note: The TEST_INDICATOR is examined by the Demand Processor to determine how far the transaction can proceed through the application. Transactions with a TEST_INDICATOR value of “T” are validated and archived, but no further processing is done. In the Release Management Workbench, Test Transactions require that the “Test” check box is checked.
This allows test transactions to be tested in a production environment for front-end data validation. To fully test a transaction through a test database environment, the transactions must be flagged as production to go beyond just the validation.
This Demand Processor feature facilitates set up and implementation for inbound demand schedules with new trading partners.
DOCUMENT_CODE
This column represents the customer-assigned schedule reference or release number.
TP_TRANSLATOR_CODE, TP_LOCATION_CODE (EDI Location Code)
The two columns in combination uniquely identify a Trading Partner in Oracle e-Commerce Gateway. Once the trading partner definition is accessed, Oracle e-Commerce Gateway can verify whether the transaction is enabled for the Trading Partner.
If this trading partner is not defined in Oracle e-Commerce Gateway, a process rule exception is detected, and an exception message will display in the View Staged Documents window.
Refer to the Trading Partner chapter for details on how to properly define your trading partners and get a better understanding of how these fields are used in the process.
TRANSACTION_DATE_TIME
This column is the schedule generation date that is sent in the transmission
EDI_CONTROL_NUM_1
EDI control number 1 is the control number from the outer electronic envelope that is used for auditing purposes. This code, such as the X12 ISA segment or the EDIFACT UNB segment, is assigned by the customer's EDI translator.
EDI_CONTROL_NUM_2
EDI control number 2 is the second level control number from the electronic envelope that is used for auditing purposes. This code, such as the X12 GS segment, is assigned by the customer's EDI translator.
EDI_CONTROL_NUM_3
EDI control number 3 is the third level control number that is used for auditing purposes. Often the control number from the X12 Starting Transaction (ST) segment is used.
Transaction Detail Records
Note: The fields with the suffix “_INT” are the internal codes that must be defined in the Oracle Release Management. One to five external codes from the transaction may be entered into their corresponding external code fields that have the suffixes “_EXT1” through “_EXT5” for the same column name. Either the e-Commerce Gateway's Code Conversion process can derive the internal codes to place them in the open interface tables, or the values may be placed in the internal code fields on the file if they are derived from another process. Refer to the Code Conversion chapter for details. Only the internal codes are referred to below for simplicity.
RLM_SCHEDULE_TYPE_INT
This column represents the Schedule type.
Valid Values: PLANNING, SHIPPING, SEQUENCED
SCHEDULE_PURPOSE_INT
This column represents the Schedule Purpose code:
Valid Values: ADD, ORIGINAL, REPLACE, CANCEL, CHANGE, DELETE, and CONFIRMATION.
SCHEDULE_SOURCE
This column represents the EDI or manual transaction source for this schedule.
For example: 830, 862, 866, DELFOR, MANUAL
ITEM_DETAIL_TYPE_INT
Detail Type is used by the Demand Processor to determine how the schedule line itself should be interpreted.
Valid Values:
0 = Past Due Firm
1 = Firm Demand
2 = Forecast Demand
3 = Authorization
4 = Shipment/Receipt Data
5 = Other
ITEM_DETAIL_SUBTYPE_INT
Detail Subtype is used by the Demand Processor to determine how the schedule line itself should be interpreted in context of its corresponding Detail. Each Detail Type has a corresponding list of valid Detail Subtypes as shown in the following table:
If ITEM_DETAIL_TYPE_INT Equals |
then ITEM_DETAIL_SUBTYPE_INT Contains |
---|---|
0, 1, 2 | 1 = Day 2 = Week 3 = Flexible 4 = Month 5 = Quarter |
3 | FINISHED = Finished Goods MATERIAL = Raw Material LABOR_MATERIAL = Labor and Material LABOR = Labor PRIOR_CUM_REQ = Prior Cumulative Required |
4 | SHIPMENT = Shipment RECEIPT = Receipt CUM = Customer CUM CUM_REJECTED = CUM Rejected |
5 | AHEAD_BEHIND = Ahead/Behind INVENTORY_BALANCE = Inventory Balance HOLDOUT_QTY = In Holdout |
DATE_TYPE_CODE_INT
Date Type is used by the Demand Processor to determine how the start and end date on each schedule line should be interpreted.
For Demand Detail Types (Past Due, Firm, and Forecast), the Date Type is critical, because it indicates whether the schedule demand is shipment-based or delivery-based.
For other Detail Types (Authorizations, Shipped/Received Information, and Other) the Date Type is simply informational, and is not used in processing.
Valid Values:
SHIP
DELIVER
RECEIVED
FROM_TO
START_DATE_TIME
Customer specified date/time, as transmitted by the customer on the transaction. Format: YYYYMMDD HHMMSS
QTY_TYPE_CODE_INT
Quantity Type is used by the Demand Processor to determine how the quantity on schedule lines should be interpreted in context of its Detail Type and Detail Subtype.
Valid Values:
ACTUAL
CUMMULATIVE
ITEM_DETAIL_QUANTITY
This column represents the requested quantity. The ITEM_DETAIL_QUANTITY value corresponds to the ITEM_DETAIL_TYPE_INT as shown in the following table:
If ITEM_DETAIL_TYPE_INT Equals |
then ITEM_DETAIL_QUANTITY Contains |
---|---|
0, 1, 2 | Demand Quantity |
3 | Authorization Quantity |
4 | Shipped, received or cum quantity |
5 | Miscellaneous quantity, such as ahead/behind, inventory balance |
UOM_CODE_INT
This is the abbreviated unit of measure code as defined in Oracle Inventory.
CUSTOMER_ITEM_EXT
This is the external Customer part number that is defined in Oracle Inventory and cross-referenced to an inventory item.
Use the Oracle e-Commerce Gateway View Staged Documents window to review the Oracle e-Commerce Gateway transaction exceptions. Once the exceptions are identified and resolved, you can submit the transaction for reprocessing, ignore the exception during reprocessing, or delete the transaction. Select the option in the View Staged Documents window.
To resolve Oracle e-Commerce Gateway exceptions, you can either correct the set-up data in Oracle e-Commerce Gateway, or Oracle E-Business Suite, or ask the Trading Partner to send a corrected transaction.
If the Trading Partner sends a corrected transaction, you must delete the erroneous transaction from Oracle e-Commerce Gateway's staging tables using the View Staged Documents window.
The following is a list of the Release Management setups related to the Release Management Demand Processor. Refer to the Oracle Release Management Implementation Manual and Oracle Release Management User's Guide for the details.
RLM: MRP Forecast Selection List
If defined, the MRP Forecast Selection List lists all the names of Forecasts to which inbound Release Management forecast demand can be assigned.
If set to “none,” forecast data will not be imported into Planning.
RLM: CUM Management Enabled
Determines whether or not CUM Management is enabled at the Site level. To use the CUM Management features of Oracle Release Management, this must be set to Yes.
RLM: Print CUM Data on Shipping Documents
Determines whether or not CUM Data should be printed on shipping documents.
RLM: Debug Mode
Determines if debug file is written for running the Demand Processor.
Valid Values:
0 = Highest debug level
Null = No debug file is generated (The default value is Null.)
RLM: Workflow Enabled
Determines whether or not the Demand Processor is enabled to run in workflow mode.
ECE: SPSI-Enabled
Determines whether or not inbound planning schedule transaction is enabled.
ECE: SSSI-Enabled
Determines whether or not inbound shipping schedule transaction is enabled.
ECE: PSQI-Enabled
Determines whether or not inbound sequenced shipping schedule transaction is enabled.
Release Management Processing Rules
There are five categories of Processing Rules attributes:
Demand Management
Demand Fences
Order Management
CUM Management
General
These five categories are defined for each Ship-From/Ship-To business entity for which Release Management will process the demand.
If terms are not defined at the optional lower levels, they will default from higher levels. There are three levels where the Processing Rules can be defined:
Ship-From / Customer (mandatory)
Ship-From / Address (optional)
Ship-From / Customer Item (optional; can be linked to either of the other two levels)
The Release Management Demand Processor is used to process Planning/Material Release, Shipping Schedule and Production Sequence Schedule transactions. It validates the incoming data entered into the Release Management interface tables by the Oracle e-Commerce Gateway import program.
The following is a list of the Release Management Demand Processor required fields. These fields are required for the Release Management Demand Processor to successfully process and move the data from the Release Management interface tables into the Oracle Order Management and Oracle Planning base application tables.
Required fields noted as derived or hardcoded do not require a value in the transaction on the transaction interface file since the values are determined by the Oracle e-Commerce Gateway process.
RLM_INTERFACE_HEADER Table
The following table lists the required fields for the RLM_INTERFACE_HEADER table:
Oracle E-Business Suite Column Name for Required Fields | Cond. | Oracle e-Commerce Gateway Column Name | Hardcoded/Derived | Record Number | Position Number |
---|---|---|---|---|---|
RLM_SCHEDULE_TYPE_INT | 1000 | 10 | |||
SCHEDULE_PURPOSE_INT | 1000 | 70 | |||
SCHEDULE_SOURCE | 1000 | 150 | |||
CREATED_BY | Derived | ||||
CREATION_DATE | Derived | ||||
HEADER_ID | HEADER_ID | Derived | |||
LAST_UPDATE_DATE | Derived | ||||
LAST_UPDATED_BY | Derived | ||||
PROCESS_STATUS | Derived | ||||
REQUEST_ID | Derived |
Most of the data was defined in the Oracle e-Commerce Gateway Required Fields section above. Data fields that were not defined in that section but are derived are defined below.
HEADER_ID
This column represents a sequence-generated unique identifier. It is system-generated when the interface table is loaded.
PROCESS_STATUS
Indicates the current processing status of a record.
Valid Values:
1 = Do not process
2 = Waiting to be processed
3 = In process
4 = Error
5 = Processed
6 = Processed with errors
REQUEST_ID
This column represents a sequence-generated unique identifier assigned to the concurrent request for the demand processor.
Update Columns:
CREATED_BY
This code identifies who loaded this transaction into the Demand Processor tables. The code is set to the Requester ID associated with the concurrent manager request that processed this transaction.
CREATION_DATE
This is the date that the e-Commerce Gateway loaded the transaction into the Demand Processor table entries. This date is set to the system date.
LAST_UPDATE_DATE
This date is equal to the CREATION_DATE for this transaction. This date is set to the system date.
LAST_UPDATED_BY
This code is equal to the CREATED_BY code for this transaction.
RLM_INTERFACE_LINES Table
The following table lists the required fields for the RLM_INTERFACE_LINES table:
Oracle E-Business Suite Column Name for Required Fields | Cond. | Oracle e-Commerce Gateway Column Name | Hardcoded/Derived | Record Number | Position Number |
---|---|---|---|---|---|
ITEM_DETAIL_TYPE_INT | 2000 | 010 | |||
ITEM_DETAIL_SUBTYPE_INT | 2000 | 070 | |||
DATE_TYPE_CODE_INT | 2000 | 130 | |||
START_DATE_TIME | 2000 | 190 | |||
QTY_TYPE_CODE_INT | 2000 | 210 | |||
ITEM_DETAIL_QUANTITY | 2000 | 270 | |||
UOM_CODE_INT | 2040 | 010 | |||
CUSTOMER_ITEM_EXT | 3100 | 020 | |||
CREATED_BY | Derived | ||||
CREATION_DATE | Derived | ||||
HEADER_ID | HEADER_ID | Derived | |||
LAST_UPDATE_DATE | Derived | ||||
LAST_UPDATED_BY | Derived | ||||
LINE_ID | LINE_ID | Derived | |||
LINE_SOURCE | Derived | ||||
PROCESS_STATUS | Derived | ||||
REQUEST_ID | Derived | ||||
SCHEDULE_LINE_NUM | Derived |
LINE_ID
This column represents a sequence-generated unique identifier.
HEADER_ID
This column represents a sequence-generated unique identifier. It is system-generated when the interface table is loaded.
LINE_ID
This column represents a sequence-generated unique identifier. It is system-generated when the interface table is loaded.
LINE_SOURCE
Schedule source at line level since lines can also be manually entered.
PROCESS_STATUS
Indicates the current processing status of a record.
Valid Values:
1 = Do not process
2 = Waiting to be processed
3 = In process
4 = Error
5 = Processed
6 = Processed with errors
REQUEST_ID
This column represents a sequence-generated unique identifier assigned to the concurrent request for the demand processor.
Update Columns:
CREATED_BY
This code identifies who loaded this transaction into the Demand Processor tables. The code is set to the Requester ID associated with the concurrent manager request that processed this transaction.
CREATION_DATE
This is the date that the e-Commerce Gateway loaded the transaction into the Demand Processor table entries. This date is set to the system date.
LAST_UPDATE_DATE
This date is equal to the CREATION_DATE for this transaction. This date is set to the system date.
LAST_UPDATED_BY
This code is equal to the CREATED_BY code for this transaction.
The Oracle Release Management Demand Processor Exception Handling lets the supplier accomplish the following tasks:
Receive error messages on problems that have halted demand processing
Receive warning messages on potential demand processing problems
Receive information messages on noteworthy situations detected by the Demand Processor
Generate exceptions report automatically after demand processing completion
Review real time report on inbound demand processing exceptions
View exceptions generated for any schedule via the Release Management Workbench until purged
Correct setup data to allow successful demand processing
Correct schedule data to allow successful demand processing
Validate customer demand for data that impacts demand processing
You can review all exceptions detected by the Release Management Demand Processor in the Release Management Demand Workbench or on the Demand Management Exception Report.
Refer to Oracle Release Management User's Guide for more information on the Demand Management Exception Report.
To resolve Release Management Demand Processor exceptions, you can either correct the setup data in Oracle E-Business Suite, make changes to the original schedule on the Release Management Demand Workbench, or ask the supplier to resend the transaction with the corrected values.
If you chose to update Oracle E-Business Suite data, you can resubmit the Release Management Demand Processor to revalidate the transaction.
The following table lists the transaction delivered with Release 12:
Transaction Name | Direction | Transaction Code |
ASC X12 |
EDIFACT |
---|---|---|---|---|
Ship Notice/Manifest | Outbound | DSNO | 856 | DESADV |
The topics covered for outbound transactions include the following:
Trading Partner Link to Oracle e-Commerce Gateway
Relevant Oracle Shipping Execution Setups
Extract Criteria
Columns Updated Upon Extraction
The transaction requirements may change when enhancements are made such as additional data added to the transaction. Current transaction details can be found on Oracle Support's web site.
Current detail record layouts are reported via the Transaction Layout Definition Report and the Interface File Data Report.
Note: See Oracle Shipping Execution Transaction Summaries for layout details.
Customers and Customer sites are defined in either Oracle Order Management or Oracle Receivables. Included in the definition is the EDI Location Code that trading partners agree to exchange to represent the full detailed address. Often they do not send the full address, but just the EDI Location Code. This is a critical data field to Oracle e-Commerce Gateway.
The EDI Location Code is the link between a customer/customer site in Oracle E-Business Suite and the trading partner site definition in Oracle e-Commerce Gateway. This enables Oracle e-Commerce Gateway to access the detailed data about the customer or customer site in the base Oracle E-Business Suite applications without maintaining the detail data in Oracle e-Commerce Gateway.
To ensure that the trading partner link between Oracle e-Commerce Gateway and Oracle E-Business Suite is set up properly, verify that the customer/customer site and the EDI Location Code in Oracle E-Business Suite is the correct customer/customer site selected for the Trading Partner definition in Oracle e-Commerce Gateway.
The selected customer/customer site and the EDI Location Code defined in Oracle AE-Business Suite are displayed in the Define Trading Partners window, Assignment tab. If the data is not what you intend it to be, you must make the appropriate changes for the transaction to be extracted for the correct trading partner. This could involve either altering the customer/customer site in the base Oracle E-Business Suite application, or assigning a different customer/customer site to that EDI Location Code in Oracle e-Commerce Gateway.
Note that if the trading partner is associated with multiple operating units, then the transaction to be extracted is for a specified operating unit only linked to your responsibility. To extract all the data for multiple operating units, you still need to run the program one by one by specifying different operating unit each time when you run the program. See the Outbound Transactions chapter in the Oracle e-Commerce Gateway User's Guide for details.
Refer to the Trading Partner chapter for recommendations on selecting the correct trading partner EDI Location Code for the control record 0010 for the transaction in the transaction interface file.
There are no required setups in Oracle Shipping Execution for this transaction.
Once the shipment is confirmed, define a trip to specify the exact contents. A trip may be a truck load containing x number of items in y number of boxes defined in z number of pallets.
After the trip is closed, the shipping process initiates the transaction through the Oracle e-Commerce Gateway. This transaction is event-driven by the shipping process; it is not usually driven by a scheduled concurrent manager request. However, Oracle e-Commerce Gateway can be used to extract the transaction again, if necessary.
The outbound Shipment Notice/Manifest transaction is formed by the following seven database views that are defined according to the Oracle Shipping Execution data model for the Shipment Notice/Manifest transaction.
WSH_DSNO_ALLOWANCE_CHARGES_V
WSH_DSNO_CONTAINERS_V
WSH_DSNO_DELIVERIES_V (header)
WSH_DSNO_DELIVERY_ATTRIBS_V (header level)
WSH_DSNO_ITEM_DETAILS_V
WSH_DSNO_ITEMS_V
WSH_DSNO_ORDERS_V
The ECE_DSNO_DELIVERIES_V view is used to identify which Shipment Notice/Manifest is eligible for extraction.
The extract criteria are the following:
Trading partner is defined
Transaction type enabled for the trading partner
The Stop is closed
The following is a list of fields updated by the WSH_ECE_VIEW_DEF_UPDATE_DEL_ASN_INFO procedure that is run after the transaction data is extracted. The table updated is the WSH_NEW_DELIVERIES:
Column | Value |
---|---|
ASN_SEQ_NUMBER | |
ASN_DATE_SENT | SYSDATE |
The implementation of any transaction requires some set up in Oracle E-Business Suite and Oracle e-Commerce Gateway. This chapter focuses on the application set-ups necessary to implement a transaction that integrates with Oracle Supplier Scheduling.
Transaction Name | Direction | Transaction Code | ASC X12 | EDIFACT |
---|---|---|---|---|
Planning Schedule | Outbound | SPSO | 830 | DELFOR |
Shipping Schedule | Outbound | SSSO | 862 | DELJIT |
The topics covered for outbound transactions include the following:
Trading Partner Link to Oracle e-Commerce Gateway
Relevant Application Profile Options and Setups
Planning/Shipping Schedule Extract Options
Extract Criteria
Columns Updated upon Extraction
The transaction requirements may change when enhancements are made such as additional data added to the transaction. Current transaction details can be found on Oracle Support's Web site.
Current detail record layouts are reported via the Transaction Layout Definition Report and the Interface File Data Report.
Note: See Oracle Supplier Scheduling Transaction Summaries for layout details.
Suppliers and supplier sites are defined in either Oracle Purchasing or Oracle Payables. Included in the definition is the EDI Location Code that trading partners agree to exchange to represent the full detailed address. Often they do not send the full address, but just the EDI Location Code. This is a critical data field to Oracle e-Commerce Gateway.
The EDI Location Code is the link between a supplier/supplier site in Oracle E-Business Suite and the trading partner site definition in Oracle e-Commerce Gateway. This enables Oracle e-Commerce Gateway to access the detailed data about the supplier or supplier site in the base Oracle E-Business Suite applications without maintaining the detail data in Oracle e-Commerce Gateway.
To ensure that the trading partner link between Oracle e-Commerce Gateway and Oracle E-Business Suite is set up properly, verify that the supplier/supplier site and the EDI Location Code in Oracle E-Business Suite is the correct supplier/supplier site selected for the Trading Partner definition in Oracle e-Commerce Gateway. The selected supplier/supplier site and the EDI Location Code defined in Oracle E-Business Suite are displayed in the Define Trading Partners window, Assignment tab. If the data is not what you intend it to be, you must make the appropriate changes for the transaction to be extracted for the correct trading partner. This could involve either altering the supplier/supplier site in the base Oracle E-Business Suite application, or assigning a different supplier/supplier site to that EDI Location Code in Oracle e-Commerce Gateway.
Note that if the trading partner is associated with multiple operating units, then the transaction to be extracted is for a specified operating unit only linked to your responsibility. To extract all the data for multiple operating units, you still need to run the program one by one by specifying different operating unit each time when you run the program. See the Outbound Transactions chapter in the Oracle e-Commerce Gateway User's Guide for details.
Refer to the Trading Partner chapter for recommendations on selecting the correct trading partner EDI Location Code for the control record 0010 for the transaction in the transaction interface file.
Use Oracle Supplier Scheduling Scheduler's Workbench to create planning schedules. Oracle Supplier Scheduling will gather the supply and demand data from Oracle Purchasing and Oracle Planning.
Refer to the Oracle Purchasing, Planning, and Supplier Scheduling User's Guide for necessary profile set ups related to Purchasing, MRP, MPS and DRP.
There are no specific profile options that control how the planning schedules are extracted.
The following table lists the planning schedule extract options. The table lists the Oracle E-Business Suite product, the process, and the method.
Oracle E-Business Suite Product | Process | Method |
---|---|---|
Supplier Scheduling | Scheduler's Workbench for manually created schedules | Event Driven |
Supplier Scheduling | AutoSchedule for automatically created schedules | Event Driven |
Oracle e-Commerce Gateway | Outbound Planning Schedule Transaction | Concurrent Manager Request |
Since the purpose of the Planning Schedule is to work closely with your suppliers to get the right materials to the right place at the right time, the most expeditious option is to transmit the planning schedules at the time the schedule is created in the Scheduler Workbench or via AutoSchedule.
All planning schedules must be confirmed before they are eligible for extraction. Schedules may be confirmed using the Scheduler Workbench or in AutoSchedule via the AutoConfirm process.
The outbound Planning Schedule transaction is controlled by two database views which are defined according to the Oracle Supplier Scheduling data model for planning schedules. The two views contain variables which are dynamically set based on your responses to the extract program parameters (refer to Oracle e-Commerce Gateway User's Guide, Outbound Transactions chapter for a list of the program parameters).
The two database views are as follows:
ECE_SPSO_ITEMS_V
ECE_SPSO_HEADERS_V
The ECE_SPSO_HEADERS_V view is used to identify which planning schedules are eligible for extraction. The extract criteria are as follows:
Trading partner is defined
Transaction type enabled for the trading partner
Planning schedule has not been printed or previously extracted
Planning schedule defined in Supplier Scheduling with communication code of EDI or BOTH
Planning schedule has a status of CONFIRMED
Planning schedule horizon start/end dates fall within the organization's period start/end dates
If necessary, you can use SQLPLUS to verify if there are any eligible documents to be extracted. To do so, you must first set the organization context and then issue the SQL count function as follows:
SQLPLUS>execute fnd_client_info.set_org_context("<Org number>"); SQLPLUS>select count(*) ECE_SPSO_HEADERS_V;
Review all your set ups if the count value is 0 as this indicates there are no eligible documents to be extracted.
Once an eligible planning schedule is successfully extracted and written to the transaction interface file, it is marked by Oracle e-Commerce Gateway to prevent it from subsequent extraction. The following tables lists the fields updated in the CHV_SCHEDULE_HEADERS table based on an excerpt of code in the UPDATE_CHV_SCHEDULE_HEADERS procedure defined in the ECSPSOB.pls file:
Column | Value |
---|---|
EDI_COUNT | Increment by 1 |
LAST_EDI_DATE | SYSDATE |
LAST_UPDATED_BY | -1 |
LAST_UPDATE_DATE | SYSDATE |
COMMUNICATION_CODE | see below |
COMMUNICATION_CODE is set based on the original value as shown in the following table:
Original COMMUNICATION_CODE | Extract Successful | Set New COMMUNICATION_CODE |
---|---|---|
BOTH | Yes | |
EDI | Yes | NONE |
Suppliers and supplier sites are defined in either Oracle Purchasing or Oracle Payables. Included in the definition is the EDI Location Code that trading partners agree to exchange to represent the full detailed address. Often they do not send the full address, but just the EDI Location Code. This is a critical data field to Oracle e-Commerce Gateway.
The EDI Location Code is the link between a supplier/supplier site in Oracle E-Business Suite and the trading partner site definition in Oracle e-Commerce Gateway. This enables Oracle e-Commerce Gateway to access the detailed data about the supplier or supplier site in the base Oracle E-Business Suite applications without maintaining the detail data in Oracle e-Commerce Gateway.
To ensure that the trading partner link between Oracle e-Commerce Gateway and Oracle E-Business Suite is set up properly, verify that the supplier/supplier site and the EDI Location Code in Oracle E-Business Suite is the correct supplier/supplier site selected for the Trading Partner definition in Oracle e-Commerce Gateway. The selected supplier/supplier site and the EDI Location Code defined in Oracle E-Business Suite are displayed in the Define Trading Partners window, Assignment tab. If the data is not what you intend it to be, you must make the appropriate changes for the transaction to be extracted for the correct trading partner. This could involve either altering the supplier/supplier site in the base Oracle E-Business Suite application, or assigning a different supplier/supplier site to that EDI Location Code in Oracle e-Commerce Gateway.
Note that if the trading partner is associated with multiple operating units, then the transaction to be extracted is for a specified operating unit only linked to your responsibility. To extract all the data for multiple operating units, you still need to run the program one by one by specifying different operating unit each time when you run the program. See the Outbound Transactions chapter, Oracle e-Commerce Gateway User's Guide for details.
Refer to the Trading Partner chapter for recommendations on selecting the correct trading partner EDI Location Code for the control record 0010 for the transaction in the transaction interface file.
Use Oracle Supplier Scheduling Scheduler's Workbench to create shipping schedules. Oracle Supplier Scheduling will gather the supply and demand data from Oracle Purchasing and Oracle Planning.
Refer to Oracle Master Scheduling/MRP and Oracle Supply Chain Planning User's Guide for necessary profile setups related to Purchasing, MRP, MPS and DRP.
There are no specific profile options that control how the shipping schedules are extracted.
The following table lists the shipping schedule extract options. The table lists the Oracle E-Business Suite product, the process, and the method.
Oracle E-Business Suite Product | Process | Method |
---|---|---|
Supplier Scheduling | Scheduler's Workbench for manually created schedules | Event Driven |
Supplier Scheduling | AutoSchedule for automatically created schedules | Event Driven |
Oracle e-Commerce Gateway | Outbound Shipping Schedule Transaction | Concurrent Manager Request |
Since the purpose of the Shipping Schedule is to work closely with your suppliers to get the right materials to the right place at the right time, the most expeditious option is to transmit the shipping schedules at the time the schedule is created in the Scheduler Workbench or via AutoSchedule.
All shipping schedules must be confirmed before they are eligible for extraction. Schedules may be confirmed using the Scheduler Workbench or in AutoSchedule via the AutoConfirm process.
The outbound Shipping Schedule transaction is controlled by two database views which are defined according to the Oracle Supplier Scheduling data model for shipping schedules. The two views contain variables which are dynamically set based on your responses to the extract program parameters (refer to the Outbound Transactions chapter, Oracle e-Commerce Gateway User's Guide for a list of the program parameters).
The two database views are as follows:
ECE_SSSO_ITEMS_V
ECE_SSSO_HEADERS_V
The ECE_SSSO_HEADERS_V view is used to identify which shipping schedules are eligible for extraction. The extract criteria are as follows:
Trading partner is defined
Transaction type enabled for the trading partner
Shipping schedule has not been printed or previously extracted
Shipping schedule defined in Supplier Scheduling with communication code of EDI or BOTH
Shipping schedule has a status of CONFIRMED
Shipping schedule horizon start/end dates fall within the organization's period start/end dates
If necessary, you can use SQLPLUS to verify if there are any eligible documents to be extracted. To do so, you must first set the organization context and then issue the SQL count function as follows:
SQLPLUS>execute fnd_client_info.set_org_context("<Org number>"); SQLPLUS>select count(*) ECE_SSSO_HEADERS_V;
Review all your set ups if the count value is 0 as this indicates there are no eligible documents to be extracted.
Once an eligible shipping schedule is successfully extracted and written to the transaction interface file, it is marked by Oracle e-Commerce Gateway to prevent it from subsequent extraction. The following table lists the fields updated in the CHV_SCHEDULE_HEADERS table based on an excerpt of code in the UPDATE_CHV_SCHEDULE_HEADERS procedure defined in the ECSPSOB.pls file. This file is shared by both the Planning and Shipping Schedule transaction with specific procedure calls to support each transaction.
Column | Value |
---|---|
EDI_COUNT | Increment by 1 |
LAST_EDI_DATE | SYSDATE |
LAST_UPDATED_BY | -1 |
LAST_UPDATE_DATE | SYSDATE |
COMMUNICATION_CODE | see below |
COMMUNICATION_CODE is set based on the original value as shown in the following table:
Original COMMUNICATION_CODE | Extract Successful | Set New COMMUNICATION_CODE |
---|---|---|
BOTH | Yes | |
EDI | Yes | NONE |