Application Transaction Detail

This chapter contains information about application transaction detail.

This chapter covers the following topics:

Oracle Inventory

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:

Oracle Inventory Transactions
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:

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.

Outbound Movement Statistics (MVSTO/No X12/CUSDEC)

Trading Partner Link to Oracle e-Commerce Gateway

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.

Extract Criteria

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:

The ECE_MVSTO_DETAILS_V view is used to identify which movement statistics are eligible for extraction. The extract criteria are as follows:

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.

Columns Updated Upon Extraction

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:

Columns Updated Upon Extraction
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:

Oracle Order Management

The following table shows the transactions that are delivered with Release 12:

Oracle Order Management Transactions
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:

The topics covered for outbound transactions include the following:

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.

Note: See Oracle Order Management Transaction Summaries for layout details.

Inbound Purchase Order (POI/850/ORDERS)

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.

Trading Partner Link to Oracle e-Commerce Gateway

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.

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 Order Management Open Interface tables to be processed by the Order Management 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 POI
TP_TRANSLATOR_CODE 0010 70 Translator identifier for this trading partner
TP_LOCATION_CODE 0010 80 The EDI Location Code

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

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

Resolve Oracle e-Commerce Gateway Exceptions

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.

Relevant Oracle Order Management Profiles and Setup Steps

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.

Oracle Order Management Defaulting Functionality

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.

Order Import Open Interface Data Levels

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:

  1. Order Header

  2. Order Line

  3. Order Shipment (Schedules)

The Order Import open interface tables have a two-level structure:

  1. Order Header

  2. 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.

Example of X12 -850 Purchase Order and e-Commerce Gateway Transaction Mapping
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 Inbound Purchase Order Transaction Structure
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.

Line and Shipment Records
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.

Original System Reference Data in all Order Import Tables

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.

Original System Reference Data in all Order Import Tables
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.

Unique Data for Each Customer
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

ORIG_SYS_DOCUMENT_REF

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:

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.

ORIG_SYS_LINE_REF

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.

ORIG_SYS_SHIPMENT_REF

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.

Sample Original System Shipment Reference
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.)

Alternative Original System Shipment Reference

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:

Sample 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

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

Comment Text

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.

Flags

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.

Order Management Flags
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

Validate Mode Parameter in Concurrent Manager

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

Order Import Open Interface Data

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:

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:

Sample Columns from the 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:

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.

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.

Leave at least one column activated to appear in the transaction interface file.

Header 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 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.

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.

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.

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

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

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

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

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    

OE_HEADERS_INTERFACE Table

CUSTOMER_PO_NUMBER

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.

Construction of CUSTOMER_PO_NUMBER: Modified Purchase Order Number 86420-0 (Constructed by the EDI Translator to load into Oracle Order Management)
Purchase Order Number
from Customer's Purchasing Application		 Purchase Order Release Number
from Customer's Purchasing Application
86420 03
Construction of ORIG_SYS_DOCUMENT_REF: Original System Document Reference 86420-03 (Defaulted by the POI Program to use CUSTOMER_PO_NUMBER)
Your Modified Purchase Order Number
moved into CUSTOMER_PO_NUMBER
86420 -03
Note: You concatenated the fields.
Construction of ORIG_SYS_DOCUMENT_REF: Original System Document Reference 86420-03 (Constructed by the EDI Translator then copied to the file)
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.

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

Original System Reference Data:

ORIG_SYS_DOCUMENT_REF

See Definition above.

Update Columns:

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_LINES_INTERFACE Table

Configuration Data

The following data elements must be entered for Configured items to show the relationship of line items in the file.

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.

Update Columns:

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.

Original System Reference Data:

ORIG_SYS_DOCUMENT_REF,

ORIG_SYS_LINE_REF,

ORIG_SYS_SHIPMENT_REF

See Definition above.

ORIG_SYS_CREDITS_REF

Update Columns:

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_PRICE_ADJS_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.

Original System Reference Data:

ORIG_SYS_DOCUMENT_REF,

ORIG_SYS_LINE_REF,

ORIG_SYS_SHIPMENT_REF

See Definition above.

ORIG_SYS_DISCOUNT_REF

Update Columns:

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.

Original System Reference Data

ORIG_SYS_DOCUMENT_REF,

ORIG_SYS_LINE_REF,

ORIG_SYS_SHIPMENT_REF

See Definition above.

ORIG_SYS_LOTSERIAL_REF

Update Columns:

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_RESERVTNS_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.

Original System Reference Data

ORIG_SYS_DOCUMENT_REF,

ORIG_SYS_LINE_REF,

ORIG_SYS_SHIPMENT_REF

See Definition above.

ORIG_SYS_RESERVATION_REF

Update Columns:

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_ACTIONS_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.

Original System Reference Data:

ORIG_SYS_DOCUMENT_REF,

ORIG_SYS_LINE_REF,

ORIG_SYS_SHIPMENT_REF

See Definition above.

Update Columns:

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.

Review Order Management Open Interface Exceptions

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.

Resolve Order Management Open Interface Exceptions

There are three ways to resolve Order Management Open Interface exceptions as follows:

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.

Order Import Item Cross-Referencing

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:

Inventory Tables

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 for the Customer Item Cross Reference Table

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.

Cross Reference Types for the Inventory Item Cross Reference Table

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.

Data in the e-Commerce Gateway Transaction

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.

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.

This field contains the customer item or a generic item as defined in the customer's inventory application.

This field contains a valid qualifier that identifies the type of item placed in CUSTOMER_ITEM_NAME.

CUSTOMER_ITEM_ID_TYPE

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.)

CUSTOMER_ITEM_ID_TYPE Code Conversion

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.

Table Searches

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)

Combination of Item Data in the Transaction Record 2000

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

Error Situations:

  1. In all cases where more than one table entry is retrieved during the table search, an error message is created.

  2. 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.

Results of Item Cross Referencing on the Sales Order

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.

Miscellaneous Item Data

ITEM_REVISION on Record 2000

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:

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.

Inbound Purchase Order Change (POCI/860/ORDCHG)

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  

Outbound Purchase Order Acknowledgment (POAO/855/ORDRSP) (POCAO/865/ORDRSP)

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.

Oracle Payables

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:

The topics covered for outbound transactions include the following:

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.

Inbound Shipment and Billing Notice (SBNI/857/No EDIFACT)

Refer to the Oracle Purchasing section for details regarding this transaction.

Outbound Application Advice (ADVO/824/APERAK)

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:

The ECE_ADVO_HEADERS_V view is used to identify which application advice data is eligible for extraction. The extract criteria are as follows:

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:

Outbound Payment Order/Remittance Advice (PYO/820/PAYORD-PAYEXT-REMADV)

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:

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.

  1. 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.

  2. 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.

  3. 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.

  4. 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:

      1. EDI to Payer's bank

      2. EDI to Payee's bank

      3. EDI to Payee

      4. EDI to third party

      5. Do not route

      6. Remittance Instruction - Additional text instructions.

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

        1. Verify that the invoices you wish to pay electronically are defined with "Electronic" as the payment method.

        2. Verify that the invoices you wish to pay do not have active holds placed on them.

        3. Approve the invoices you wish to pay.

        4. Verify that you have confirmed the default remit-to bank account for each scheduled payment.

        5. 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.

        6. Create the Payment Batch. Refer to the Initiating Payment Batches section, Oracle Payables User's Guide for details.

        7. Refer to the Modifying Payment Batches section, Oracle Payables User's Guide, for details on how to modify a payment batch if necessary.

        8. 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:

The ECE_PYO_PAYMENTS_V view used to identify which payments and associated invoices are eligible for extraction. The extract criteria are as follows:

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.

Inbound Invoice (INI/810/INVOIC)

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:

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:

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.

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

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

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:

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.

There are three ways to resolve Payables Open Interface exceptions as follows:

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.

Inbound Shipment and Billing Notice (SBNI/857/No EDIFACT)

Refer to the Oracle Purchasing section for details regarding this transaction.

Outbound Application Advice (ADVO/824/APERAK)

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.

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.

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

The ECE_ADVO_HEADERS_V view is used to identify which application advice data is eligible for extraction. The extract criteria are as follows:

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:

Outbound Payment Order/Remittance Advice (PYO/820/PAYORD-PAYEXT-REMADV)

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:

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.

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.

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 Oracle Payables User's Guide, Creating Electronic Payments with Oracle e-Commerce Gateway section for the details.

  1. 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.

  2. 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.

  3. 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.

  4. 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:

      1. EDI to Payer's bank

      2. EDI to Payee's bank

      3. EDI to Payee

      4. EDI to third party

      5. Do not route

      6. Remittance Instruction - Additional text instructions.

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

        1. Verify that the invoices you wish to pay electronically are defined with "Electronic" as the payment method.

        2. Verify that the invoices you wish to pay do not have active holds placed on them.

        3. Approve the invoices you wish to pay.

        4. Verify that you have confirmed the default remit-to bank account for each scheduled payment.

        5. 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.

        6. Create the Payment Batch. Refer to Oracle Payables User's Guide, Initiating Payment Batches section for the details.

        7. Refer to Oracle Payables User's Guide, Modifying Payment Batches section for details on how to modify a payment batch if necessary.

        8. 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 Oracle e-Commerce Gateway User's Guide, Outbound Transactions chapter for a list of the program parameters).

The two database views are as follows:

The ECE_PYO_PAYMENTS_V view used to identify which payments and associated invoices are eligible for extraction. The extract criteria are as follows:

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 Oracle Payables User's Guide, Confirming Payment Batches section for the details.

Oracle Process Manufacturing

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.

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.

Oracle Purchasing

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:

The topics covered for outbound transactions include the following:

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.

Inbound Price/Sales Catalog (CATI/832/PRICAT) Inbound Response to Request for Quotation (RRQI/843/QUOTES)

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

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

Relevant Oracle Purchasing, Inventory Profiles and Setups

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.

  1. 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.

  2. 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

  3. 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."

  4. Set Up Price Tolerance

    Define price tolerances in Oracle Purchasing for price increases associated with a price/sales catalog update.

Purchasing Documents Open Interface Required Fields

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:

The following types of data are supported with a catalog change:

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.

Review Purchasing Documents Open Interface Exceptions

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.

Resolve Purchasing Documents Open Interface Exceptions

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.

Inbound Ship Notice/Manifest (ASNI/856/DESADV) Inbound Shipment and Billing Notice (SBNI/857/No EDIFACT)

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

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

Relevant Oracle Purchasing Profiles and Setups

The following is a list of the Purchasing setups related to the Receiving Open Interface.

  1. 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.

  2. 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.

  3. 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.

Receiving Open Interface Required Fields

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.

Required Fields for the RCV_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
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.

Required Fields for the RCV_TRANSACTIONS_INTERFACE Table
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.

Review Receiving Open Interface Exceptions

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.

Return Application Advice to Supplier

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.

Resolve Receiving Open Interface Exceptions

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.

Oracle Warehouse Management Required Fields

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 Required Fields
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.

Outbound Application Advice (ADVO/824/APERAK)

Refer to the Oracle Payables section for details regarding this transaction.

Outbound Purchase Order (POO/850/ORDERS)

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.

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.

Relevant Oracle Purchasing Profiles and Set Ups

The following is a list of the Purchasing set ups related to the outbound Purchase Order transaction.

  1. 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.

Extract Criteria

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:

The ECE_POO_HEADERS_V view is used to identify which purchase orders are eligible for extraction. The extract criteria are as follows:

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.

Columns Updated Upon Extraction

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.

Outbound Purchase Order Change (POCO/860/ORDCHG)

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.

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.

Relevant Oracle Purchasing Profiles and Set Ups

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:

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.

Extract Criteria

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:

The ECE_POCO_HEADERS_V view is used to identify which purchase order changes are eligible for extraction. The extract criteria are as follows:

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.

Columns Updated Upon Extraction

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.

Oracle Receivables

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.

Oracle Receivables Transactions
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:

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.

Outbound Invoice (INO/810/INVOIC)

Trading Partner Link to Oracle e-Commerce Gateway

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.

Two Methods of Sending Credit Memos/Debit Memos

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.

Extract Criteria

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:

The ECE_INO_HEADERS_V view is used to identify which supplier invoices are eligible for extraction. The extract criteria are as follows:

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.

Columns Updated Upon Extraction

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

Outbound Credit Memo/Debit Memo (CDMO/812/CREADV-DEBADV)

Trading Partner Link to Oracle e-Commerce Gateway

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.

Two Methods of Sending Credit Memos/Debit Memos

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.

Extract Criteria

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:

The ECE_CDMO_HEADERS_V view is used to identify which credit/debit memos are eligible for extraction. The extract criteria are as follows:

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.

Columns Updated Upon Extraction

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

Oracle Release Management

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:

Oracle Release Management Transactions
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:

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 Oracle Release Management Transaction Summaries for a diagram of the record hierarchy, record looping structure, and record summaries.

Planning/Material Release (SPSI/830/DELFOR/DELINS) Shipping Schedule (SSSI/862/DELJIT/DELINS/CALDEL/KANBAN)

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

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.

Trading Partner Link to Oracle e-Commerce Gateway

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.

Oracle e-Commerce Gateway Required Fields

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 Required Fields
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:

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:

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.

Code Conversion Categories

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.

Code Conversion Categories
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

Interface Tables

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.

Demand Schedule Subdetail Records

Record 5000

Record 5000 contains the schedule subdetail as noted in the following table:

Example Record 5000
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:

Example Record 5010
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.

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

Relevant Application Profile Options and Setup

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.

  1. 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.

  2. 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.

  3. RLM: Print CUM Data on Shipping Documents

    Determines whether or not CUM Data should be printed on shipping documents.

  4. 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)

  5. RLM: Workflow Enabled

    Determines whether or not the Demand Processor is enabled to run in workflow mode.

  6. ECE: SPSI-Enabled

    Determines whether or not inbound planning schedule transaction is enabled.

  7. ECE: SSSI-Enabled

    Determines whether or not inbound shipping schedule transaction is enabled.

  8. ECE: PSQI-Enabled

    Determines whether or not inbound sequenced shipping schedule transaction is enabled.

  9. 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)

Release Management Demand Processor Required Fields

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:

Required Fields for 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:

Required Fields for 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:

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:

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.

Review Release Management Demand Processor Exceptions

The Oracle Release Management Demand Processor Exception Handling lets the supplier accomplish the following tasks:

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.

Resolve Release Management Demand Processor Exceptions

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.

Production Sequence Schedule (PSQI/866/DELJIT/SYNCRO/SYNPAC)

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.

Oracle e-Commerce Gateway Required Fields

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 Required Fields
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:

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:

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.

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

Relevant Oracle Release Management Profiles and Setup

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.

  1. 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.

  2. 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.

  3. RLM: Print CUM Data on Shipping Documents

    Determines whether or not CUM Data should be printed on shipping documents.

  4. 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.)

  5. RLM: Workflow Enabled

    Determines whether or not the Demand Processor is enabled to run in workflow mode.

  6. ECE: SPSI-Enabled

    Determines whether or not inbound planning schedule transaction is enabled.

  7. ECE: SSSI-Enabled

    Determines whether or not inbound shipping schedule transaction is enabled.

  8. ECE: PSQI-Enabled

    Determines whether or not inbound sequenced shipping schedule transaction is enabled.

  9. 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)

Release Management Demand Processor Required Fields

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:

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:

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.

Review Release Management Demand Processor Exceptions

The Oracle Release Management Demand Processor Exception Handling lets the supplier accomplish the following tasks:

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.

Resolve Release Management Demand Processor Exceptions

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.

Oracle Shipping Execution

The following table lists the transaction delivered with Release 12:

Oracle Shipping Execution Transactions
Transaction Name Direction Transaction
Code		 ASC
X12		 EDIFACT
Ship Notice/Manifest Outbound DSNO 856 DESADV

The topics covered for outbound transactions include the following:

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.

Note: See Oracle Shipping Execution Transaction Summaries for layout details.

Outbound Shipment Notice/Manifest (DSNO/856/DESADV)

Trading Partner Link to Oracle e-Commerce Gateway

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.

Relevant Oracle Shipping Execution Profiles and Setups

There are no required setups in Oracle Shipping Execution for this transaction.

Extract Criteria

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.

The ECE_DSNO_DELIVERIES_V view is used to identify which Shipment Notice/Manifest is eligible for extraction.

The extract criteria are the following:

Columns Updated Upon Extraction

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

Oracle Supplier Scheduling

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:

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.

Note: See Oracle Supplier Scheduling Transaction Summaries for layout details.

Outbound Planning Schedule (SPSO/830/DELFOR)

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.

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.

Relevant Application Profile Options and Setups

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.

Planning Schedule Extract Options

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.

Extract Criteria

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:

The ECE_SPSO_HEADERS_V view is used to identify which planning schedules are eligible for extraction. The extract criteria are as follows:

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.

Columns Updated Upon Extraction

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 PRINT
EDI Yes NONE

Outbound Shipping Schedule (SSSO/862/DELJIT)

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.

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.

Relevant Application Profile Options and Setups

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.

Shipping Schedule Extract Options

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.

Extract Criteria

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:

The ECE_SSSO_HEADERS_V view is used to identify which shipping schedules are eligible for extraction. The extract criteria are as follows:

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.

Columns Updated Upon Extraction

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 PRINT
EDI Yes NONE