po receiveing

16
Receiving Open Interface
This chapter covers the following topics:
Receiving Open Interface
Functional Overview
Receiving Transaction Processor Activities
ASN Quantity Updates
Cascading Transaction Quantities for ASNs and Receipts
Setting Up the Receiving Open Interface
Inserting into the Receiving Open Interface Table
Receiving Headers Interface Table Description
Receiving Transactions Interface Table Description
Oracle Warehouse Management License Plate Number Interface Table Description
Derived Data
Optional Data
Validation
Debugging
Resolving Failed Receiving Open Interface Rows
Receiving Open Interface Details for Advanced Shipment Notice Import and
Receipt Transactions
Receiving Open Interface Data Details for Post Receipt Transactions
Sample Scripts
Stuck Transaction Scenarios and Scripts
Oracle Warehouse Management LPN Interface Table Supplemental Information
16-2 Oracle Supply Chain Management APIs and Open Interfaces Guide
Receiving Open Interface
You use the Receiving Open Interface to process and validate receipt data that comes
from sources other than the Receipts window in Oracle Purchasing. These sources
include:
Receipt information from other Oracle applications or legacy systems
Brocades and other receiving information from scanners and radio frequency
devices
Advance Shipment Notices (ASNs) from suppliers
The Receiving Open Interface maintains the integrity of the new data as well as the
receipt data that resides in Oracle Purchasing.
The Receiving Open Interface does not support:
Movement statistics
Dynamic locators
The following table identifies the supported transactions:
Receiving Interface Supported Transactions
Transaction ASN-PO Internal Order Interorg
Transfer
RMA
RECEIVE Yes Yes Yes Yes
TRANSFER Yes Yes Yes Yes
ACCEPT/REJEC
T
Yes Yes Yes Yes
DELIVER to
Inventory
Yes Yes Yes Yes
DELIVER to
Expense
Yes Yes NA NA
DELIVER to
Shop Floor
Yes Yes NA NA
Receiving Open Interface 16-3
Transaction ASN-PO Internal Order Interorg
Transfer
RMA
RETURN TO
RECEIVING
Yes No No NA
RETURN TO
VENDOR
Yes No No NA
RETURN TO
CUSTOMER
NA No No Yes
+ CORRECT to
RECEIVE
Yes No Yes No
- CORRECT to
RECEIVE
Yes No Yes Yes
CORRECT to
TRANSFER
Yes No Yes Yes
CORRECT to
ACCEPT/REJEC
T
Yes No Yes Yes
CORRECT to
DELIVER to
Inventory
Yes No No No
CORRECT to
DELIVER to
Expense
Yes No NA NA
CORRECT to
DELIVER to
Shop Floor
Yes No NA NA
CORRECT to
RETURN TO
RECEIVING
No No NA NA
CORRECT to
RETURN TO
VENDOR
Yes * NA NA NA
16-4 Oracle Supply Chain Management APIs and Open Interfaces Guide
Transaction ASN-PO Internal Order Interorg
Transfer
RMA
CORRECT to
RETURN TO
CUSTOMER
NA NA NA Yes
Note: The Receiving Open Interface does not support corrections to
returns for global procurement transactions, nor does it support
corrections for drop shipments that use transaction flows for
accounting.
The Receiving Open Interface does not support:
Corrections to return to receiving
Corrections to deliver, except when the source document is an ASN or a purchase
order (PO)
Positive corrections to receipts against a return material authorization (RMA)
Returns or corrections against internal orders and interorganization transfers
Unordered receipts and matching to unordered receipts
Additions to receipt transactions
Inspection transactions that interface with Oracle Quality
Functional Overview
Within the Receiving Open Interface, receipt data is validated for compatibility with
Oracle Purchasing. There are two Receiving Open Interface tables:
RCV_HEADERS_INTERFACE and RCV_TRANSACTIONS_INTERFACE.
Electronic Data Interchange Transaction Types
The Receiving Open Interface supports these Electronic Data Interchange (EDI)
transaction types:
Inbound advance shipment notices (ASN) (ANSI X12 856 or EDIFACT DESADV)
original (new), canceled, and test ASNs
Inbound ASNs with billing information (ANSI X12 857) original (new), canceled,
Receiving Open Interface 16-5
and test ASNs.
Outbound Application Advices (ANSI X12 824 or EDIFACT APERAK)
An ASN is transmitted through EDI from a supplier to communicate to the receiving
organization that a shipment is coming. For a detailed description of the ASN process,
ASN types, Application Advices, and the effects of ASNs on purchasing supply, see
Advance Shipment Notices (ASNs), Oracle Purchasing User's Guide.
Supported Transaction Features
The Receiving Open interface supports creation of ASN receipts, deliveries returns to
vendor, returns to customers, corrections, and transfers. The Receiving Open Interface
supports all of these transactions for all source documents including purchase orders,
RMAs, interorg transfers and internal orders. The Receiving Open Interface does not
support unordered receipts or any transactions for unordered receipts. The Receiving
Open Interface supports these features:
Receiving subinventory and locator: You can identify the receiving location,
receiving subinventory, and receiving locator in the Receiving Open Interface. You
can enter the subinventory, locator, or locator_id for receive, transfer, and return to
vendor from receipt transactions, and the system transfers this information to other
transactions. In the subsequent transactions, the receiving subinventory becomes
the from subinventory, and the receiving locator becomes the from locator.
Parent-child transactions: The system can create multiple transactions at the same
time. For example, you can receive five units, deliver three units, and correct one
unit from the delivery by inserting all the transactions at the same time in the
rcv_transactions_interface. The parent_interface_transaction_id links these rows.
The interface_transaction_id of the receive rcv_transactions_interface row is the
parent_interface_transaction_id of the deliver row. The interface_transaction_id of
the deliver row is the parent_interface_transaction_id of the correct row.
Lot and serial: The Receiving Open Interface supports lot and serial-based delivery
transactions and some receiving-based transactions, such as receive, transfer, and
return to vendor from receiving. You can define lot and serial information for lot or
serial-controlled items on all receiving transactions that you import through the
Receiving Open Interface. These transactions include: receipts, transfers,
inspections, deliveries, returns, and corrections. When you receive an item, lot and
serial information is optional, but upon delivery, lot and serial information is
required. However, if you specified lot and serial information on previous
transaction then the system requires that you enter this information on all
subsequent transactions. .
Lot support: Multiple rows can exist in the mtl_transaction_lots_interface for
each row in the rcv_transactions_interface. One row must exist for each lot
number in the mtl_transaction_lots_interface (mtli) where
16-6 Oracle Supply Chain Management APIs and Open Interfaces Guide
mtli.product_transacton_id = rti.interface_transaction_id and mtli.product_code
= RCV.
Serial Support: Multiple rows can exist in the mtl_serial_numbers_interface for
each row in the rcv_transactions_interface if the
mtl_serial_numbers_interface.product_transaction_id =
rti.interface_transaction_id or if the msni.transaction_interface_id =
mtli.serial_transaction_temp_id if the item is lot and serial controlled.
Important: The system does not support lot and serial functionality
for receiving transactions through the desktop user interface. For
example, if you use the Receiving Open Interface to perform a
receiving transaction and then attempt to use the desktop user
interface to perform a delivery transaction, the system returns an
error message. If you enter lot and serial information in the
Receiving Open Interface during receipt transaction, you must use
the Receiving Open Interface, Oracle Mobile Supply Chain
Applications, or Oracle Warehouse Management mobile interfaces
to perform all subsequent transactions.
LPN: You can use the Receiving Open Interface to perform the LPN actions pack,
unpack, nest, and transfer on receiving transactions. LPN information is optional,
but once you enter LPN information in a receiving transaction, the LPN remains
with the material unless you change it. Suppliers can also include lot, serial, and
LPN information in an ASN. The system automatically transfers this information to
subsequent transactions.
You can enter the LPN_id or the Transfer_LPN_id, depending upon the transaction.
You can also provide a new LPN in the wms_lpn_interface table. You connect this
row to the rcv_transactions_interface by populating the lpn_group_id in the
rcv_transactions_interface as the source_group_id in the wms_lpn_interface table.
Important: In an inventory organization, no LPNs can have the
same name and an LPN can exist in only one location at any given
time.
The Receiving Open Interface supports LPNs in both Oracle Warehouse
Management-enabled organizations and non-Oracle Warehouse
Management-enabled organizations. You can use all LPN functionality in receiving
until you deliver the LPN to inventory in a non-Oracle Warehouse
Management-enabled organization. At delivery, in a non-Oracle Warehouse
Management-enabled organization, the system automatically unpacks the LPNs
and delivers the contents as loose material. The desktop receiving user interface
does not support LPN use. If you import upstream receiving transactions through
the Receiving Open Interface and use LPNs, then the system automatically unpacks
Receiving Open Interface 16-7
any subsequent transactions that you perform in the desktop user interface.
Validation Overview
The Receipt window derives, defaults, and validates the receipt data that you enter in
the Receipts window. The receiving transaction processor derives, defaults, and
validates most receipt data that you import through the Receiving Open Interface. The
pre-processor is a component of Receiving Transaction Processor that validates and
defaults the missing data. This is similar to the defaulting and validation that happens
on the receiving desktop user interface when transaction is saved.
The following sections provide an overview of what the Receiving Open Interface does
for each transaction:
Header-Level Validation
You use EDI or another program to load the receipt data into the
RCV_HEADERS_INTERFACE and RCV_TRANSACTIONS_INTERFACE tables. The
processor selects unprocessed rows in the RCV_HEADERS_INTERFACE table for
preprocessing. It preprocesses rows with a PROCESSING_STATUS_CODE of
PENDING and a VALIDATION_FLAG of Y. The processor derives or defaults any
missing receipt header information in the RCV_HEADERS_INTERFACE table. For
example, if you provide a TO_ORGANIZATION_CODE, the processor defaults the
correct TO_ORGANIZATION_ID. The processor validates the receipt header
information in the RCV_HEADERS_INTERFACE table to ensure the integrity of the
information. The system imports only validated header information into the Oracle
Purchasing tables. If the system does not detect fatal errors at the header level, then the
Receiving Transaction Processor selects all the lines in the
RCV_TRANSACTIONS_INTERFACE table associated with each header and calls the
processor to perform line-level processing.
Line-Level Validation
The processor derives and defaults any missing receipt line information in the
RCV_TRANSACTIONS_INTERFACE table.
The processor validates the receipt line information to ensure the integrity of the
information.
For successfully validated lines, the processor deletes the original
RCV_TRANSACTIONS_INTERFACE line and creates the new, validated lines. The
system sometimes creates two or more validated rows in the
RCV_TRANSACTIONS_INTERFACE table to represent the original imported row.
You use the table RCV_HEADERS_INTERFACE only to create or update header
information. The system requires that this table must by populated when you create an
ASN, ASBN, or a receipt.
16-8 Oracle Supply Chain Management APIs and Open Interfaces Guide
Errors
If the system detects errors, then the Receiving Open Interface populates the
PO_INTERFACE_ERRORS table and the outbound Application Advice e-Commerce
Gateway interface tables. A separate process in e-Commerce Gateway downloads the
contents of the Outbound Application Advice Interface tables to the Outbound
Application Advice flat file. For ASNs with billing information (also called ASBNs), if
any lines are rejected, the Receiving Open Interface sets the INVOICE_STATUS_CODE
to RCV_ASBN_NO_AUTO_INVOICE so that an invoice will not be created
automatically from the rejected ASBN lines. You can view errors through the Receiving
Interface Errors Report in Oracle Purchasing. To view errors specifically for ASBNs, use
the Purchasing Interface Errors Report.
The system does not import rows that fail validation in the Receiving Open Interface
tables. In other words, the system does not import rows that produce errors into Oracle
Purchasing (into the RCV_SHIPMENT_HEADERS, RCV_SHIPMENTS_LINES, and
other applicable Oracle Purchasing tables). For example, if an ASN contains multiple
purchase orders, and if the purchase order number for one of the shipments is wrong,
the shipment or the entire ASN fails, depending on how you set the RCV: Fail All ASN
Lines if One Line Fails profile option.
Receiving Transaction Processor Activities
After performing header and line-level validation, the processor checks the profile
option RCV: Fail All ASN Lines if One Line Fails for an ASN/ASBN. If the profile option is
set to Yes and any line fails validation, then the processor fails the entire transaction. If
the profile option is set to No (and the TEST_FLAG is not Y), the Receiving Transaction
Processor performs the same steps that occur when you normally save receipt
information in Oracle Purchasing for all successfully processed records. For all
transactions other than ASN/ASBN, if you want a The Receiving Transaction Processor:
Populates the RCV_SHIPMENT_HEADERS table in Oracle Purchasing with the
receipt header information.
Populates the RCV_SHIPMENT_LINES table in Oracle Purchasing for each receipt
header entry in the RCV_SHIPMENT_HEADERS table in Oracle Purchasing.
Populates the RCV_TRANSACTIONS table in Oracle Purchasing for each row in
the RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINES table if the column
AUTO_TRANSACT_CODE in the RCV_TRANSACTIONS_INTERFACE table
contains a value of RECEIVE or DELIVER.
Updates supply for accepted line items in the MTL_SUPPLY and RCV_SUPPLY
tables.
Calls the Oracle Inventory module for processing DELIVER transactions.
Receiving Open Interface 16-9
Calls the Oracle General Ledger module for processing financial transactions, such
as receipt-based accruals.
Updates the corresponding purchase orders with the final received and delivered
quantities.
ASN Quantity Updates
While updating purchasing document quantities received, the Receiving Open Interface
verifies that the quantity shipped was actually received for each item indicated on the
ASN. If not, the interface populates the Application Advice history tables and the
Application Advice e-Commerce Gateway interface tables with an error.
While updating the CUM quantity for Approved Supplier List items, the Receiving
Open Interface also verifies that the new CUM quantity matches the supplier-specified
CUM quantity. If not, the interface populates the Application Advice history tables and
the Application Advice e-Commerce Gateway interface tables with an error. (CUM
management is performed only if Oracle Supplier Scheduling is installed and CUM
Management is enabled for the ship-to organization, the ASN item or items are defined
in the Approved Supplier List, and the items are sourced from the supplier using a
supply agreement blanket purchase order.)
Cascading Transaction Quantities for ASNs and Receipts
A purchase order sent to a supplier can include multiple lines and shipments. If the
supplier does not provide a specific purchase order line number, release line number, or
shipment number on the ASN but references a purchase order number, the Receiving
Open Interface allocates the quantity on a first-in, first-out basis over all applicable
purchase order and release shipments (if an item number is provided). To determine
which shipment lines to consume, the Receiving Open Interface references all
PO_LINE_LOCATIONS associated with the specified purchase order or blanket that
have the same ship-to organization specified on the ASN. The order-by clause, NVL
(PROMISED_DATE, NEED_BY_DATE, CREATION_DATE) determines the order in
which quantities are consumed in a first-in, first-out basis. Therefore, multiple shipment
lines matching the various purchase order shipment lines are created based on the
allocation to the PO_LINE_LOCATIONS table, which stores lines corresponding to
purchase order shipments.
The cascade works on a line-by-line basis, applying the remaining quantity to the last
shipment line. At the last line, the Receiving Open Interface cascades up to the
over-receipt tolerance. For example:
There are 10 purchase order shipment lines of 100 units each, all with the same
Need-By Date.
In the Receiving Controls window in Oracle Purchasing, the Over Receipt Quantity
16-10 Oracle Supply Chain Management APIs and Open Interfaces Guide
Tolerance is 10 percent meaning the Receiving Open Interface can consume 10 more
units for the last shipment line if necessary.
The actual ASN total quantity is 1,111, which exceeds your tolerance.
If the Over Receipt Quantity Action code is set to Reject (and RCV: Fail All ASN
Lines if One Line Fails is set to No), then Oracle Purchasing rejects the last ASN line
(or the whole ASN if the ASN has only one line) and creates an error in the
PO_INTERFACE_ERRORS table. Oracle Purchasing receives none of the units for
those ASN lines that were rejected.
Oracle Purchasing does not require a Promised or Need-By date for an item that is
unplanned; for unplanned items, Oracle Purchasing uses the CREATION_DATE in the
order-by clause, NVL (PROMISED_DATE, NEED_BY_DATE, CREATION_DATE). If
the cascade tries to allocate to an open shipment where the Receipt Date tolerance (the
date after which a shipment cannot be received) is exceeded and the Receipt Date
Action in the Receiving Controls window is set to Reject, Oracle Purchasing skips that
shipment and goes to the next.
Setting Up the Receiving Open Interface
You must complete the following setup steps in Oracle Purchasing to use the Receiving
Open Interface:
Provide a Yes or No value for the profile option RCV: Fail All ASN Lines if One Line
Fails. See Purchasing Profile Options, Oracle Purchasing Users Guide
In the Receiving Options window in Oracle Purchasing, select Warning, Reject, or
None in the ASN Control field to determine how Oracle Purchasing handles the
receipt against a purchase order shipment for which an ASN exists. See Defining
Receiving Options,Oracle Purchasing User's Guide
If you are receiving ASNs in the Receiving Open Interface, install and set up
e-Commerce Gateway. See Oracle e-Commerce Gateway User's Guide
All processing is initiated through standard report submission using the Submit
Request window and choosing the Receiving Transaction Processor program. The
concurrent manager manages; therefore you must ensure that processing has already
been set up and that it is running.
Inserting into the Receiving Open Interface Table
You load receipt data from your source system or e-Commerce Gateway into the
receiving headers and receiving transactions interface tables. For each row that you
insert into the RCV_HEADERS_INTERFACE table, the Receiving Open Interface
creates a shipment header; for each row that you insert into the
RCV_TRANSACTIONS_INTERFACE table, the Receiving Open Interface creates one or
Receiving Open Interface 16-11
more shipment lines. You must provide values for all columns that are required. You
may also have to provide values for columns that are conditionally required.
Required
You must specify values for columns in this category. The Receiving Open Interface
requires values in these columns to process a receiving transaction whether the data is
imported through the e-Commerce Gateway or through a program that you write. For
example, HEADER_INTERFACE_ID is a required column; however, when receiving
ASNs from suppliers through e-Commerce Gateway, e-Commerce Gateway provides
the HEADER_INTERFACE_ID automatically. If a required value is not entered, the
Receiving Open Interface inserts an error record in the PO_INTERFACE_ERRORS table.
Receiving Headers Interface Table Description
The following table contains information about the RVC_HEADERS_INTERFACE:
Column Name Type Required? Receipt Type Source
Information
HEADER_INTE
RFACE_ID
Number Yes PO, ASN, REQ,
INV, RMA
RCV_HEADERS
_INTERFACE_S
GROUP_ID Number Yes PO, ASN, REQ,
INV, RMA
RCV_INTERFA
CE_GROUPS_S
PROCESSING_S
TATUS_CODE
Varchar2(25) Yes PO, ASN, REQ,
INV, RMA
'PENDING'
RECEIPT_SOUR
CE_CODE
Varchar2(25) Yes PO, ASN, REQ,
INV, RMA
PO/ASN='VEND
OR'
REQ='INTERNA
L ORDER
'INV=
'INVENTORY'
RMA=
'CUSTOMER'
TRANSACTION
_TYPE
Varchar2(25) Yes PO, ASN, REQ,
INV, RMA
Possible values
are 'NEW',
'REPLACE',
'ADD',
'CANCEL'
16-12 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column Name Type Required? Receipt Type Source
Information
LAST_UPDATE
_DATE
Date Yes PO, ASN, REQ,
INV, RMA
SYSDATE
LAST_UPDATE
D_BY
Number Yes PO, ASN, REQ,
INV, RMA
FND_GLOBAL.
USER_ID
LAST_UPDATE
_LOGIN
Number Yes PO, ASN, REQ,
INV, RMA
FND_GLOBAL.
LOGIN_ID
CREATION_DA
TE
Date Yes PO, ASN, REQ,
INV, RMA
SYSDATE
CREATED_BY Number Yes PO, ASN, REQ,
INV, RMA
FND_GLOBAL.
USER_ID
VALIDATION_F
LAG
Varchar2(1) Yes PO, ASN, REQ,
INV, RMA
Yes
EDI_CONTROL
_NUM
Varchar2(10) No ASN
TEST_FLAG Varchar2(1) No
NOTICE_CREA
TION_DATE
Date No ASN
RECEIPT_NUM Varchar2(30) Conditionally PO, ASN, REQ,
INV, RMA
RECEIPT_HEAD
ER_ID
Number Conditionally ASN, Req, Inv RCV_SHIPMEN
T_HEADERS.SH
IPMENT_HEAD
ER_ID
VENDOR_NAM
E
Varchar2(240) Conditionally PO, ASN PO_VENDORS.
VENDOR_NAM
E
VENDOR_NUM Varchar2(20) Conditionally PO, ASN PO_VENDORS.S
EGMENT1
Receiving Open Interface 16-13
Column Name Type Required? Receipt Type Source
Information
VENDOR_ID Number Conditionally PO, ASN PO_HEADERS_
ALL.VENDOR_I
D
VENDOR_SITE_
CODE
Varchar2(35) Conditionally PO, ASN PO_VENDOR_SI
TES_ALL.VEND
OR_SITE_CODE
VENDOR_SITE_
ID
Number Conditionally PO, ASN PO_HEADERS_
ALL.VENDOR_S
ITE_ID
FROM_ORGANI
ZATION_CODE
Varchar2(3) Conditionally Req, Inv MTL_PARAMET
ERS.ORGANIZA
TION_CODE
FROM_ORGANI
ZATION_ID
Number Conditionally Req, Inv RCV_SHIPMEN
T_HEADERS.OR
GANIZATION_I
D
SHIP_TO_ORG
ANIZATION_C
ODE
Varchar2(3) Conditionally PO, ASN, REQ,
INV, RMA
MTL_PARAMET
ERS.ORGANIZA
TION_CODE
SHIP_TO_ORG
ANIZATION_ID
Number Conditionally PO, ASN, REQ,
INV, RMA
PO/ASN=
POLINE_LOCA
TIONS_ALL.SHI
P_TO_ORGANI
ZATION_ID
REQ/INV=
RCV_SHIPMEN
T_LINES.TO_OR
GANIZATION_I
D
RMA=
OE_ORDER_LI
NES_ALL.SHIP_
FROM_ORG_ID
16-14 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column Name Type Required? Receipt Type Source
Information
LOCATION_CO
DE
Varchar2(60) Conditionally PO, ASN, REQ,
INV, RMA
HR_LOCATION
S_ALL.LOCATI
ON_CODE
LOCATION_ID Number Conditionally PO, ASN, REQ,
INV, RMA
PO/ASN=
PO_LINE_LOCA
TIONS_ALL.SHI
P_TO_LOCATIO
N_ID
REQ/INV=
RCV_SHIPMEN
T_HEADERS.SH
IP_TO_LOCATI
ON_ID
BILL_OF_LADI
NG
Varchar2(25) Optional PO, ASN, REQ,
INV, RMA
PACKING_SLIP Varchar2(25) Optional PO, ASN, REQ,
INV, RMA
SHIPPED_DATE Date Optional PO, ASN, REQ,
INV, RMA
FREIGHT_CAR
RIER_CODE
Varchar2(25) Optional PO, ASN, REQ,
INV, RMA
EXPECTED_RE
CEIPT_DATE
Date Yes PO, ASN, REQ,
INV, RMA
RECEIVER_ID Number Optional PO, ASN, REQ,
INV, RMA
NUM_OF_CON
TAINERS
Number Optional PO, ASN, REQ,
INV, RMA
WAYBILL_AIRB
ILL_NUM
Varchar2(20) Optional PO, ASN, REQ,
INV, RMA
COMMENTS Varchar2(240) Optional PO, ASN, REQ,
INV, RMA
Receiving Open Interface 16-15
Column Name Type Required? Receipt Type Source
Information
GROSS_WEIGH
T
Number Optional PO, ASN, REQ,
INV, RMA
GROSS_WEIGH
T_UOM_CODE
Varchar2(3) Optional PO, ASN, REQ,
INV, RMA
NET_WEIGHT Number Optional PO, ASN, REQ,
INV, RMA
NET_WEIGHT_
UOM_CODE
Varchar2(3) Optional PO, ASN, REQ,
INV, RMA
TAR_WEIGHT Number Optional PO, ASN, REQ,
INV, RMA
TAR_WEIGHT_
UOM_CODE
Varchar2(3) Optional PO, ASN, REQ,
INV, RMA
PACKAGING_C
ODE
Varchar2(5) Optional PO, ASN, REQ,
INV, RMA
CARRIER_MET
HOD
Varchar2(2) Optional PO, ASN, REQ,
INV, RMA
CARRIER_EQUI
PMENT
Varchar2(10) Optional PO, ASN, REQ,
INV, RMA
SPECIAL_HAN
DLING_CODE
Varchar2(3) Optional PO, ASN, REQ,
INV, RMA
HAZARD_COD
E
Varchar2(1) Optional PO, ASN, REQ,
INV, RMA
HAZARD_CLAS
S
Varchar2(4) Optional PO, ASN, REQ,
INV, RMA
HAZARD_DESC
RIPTION
Varchar2(80) Optional PO, ASN, REQ,
INV, RMA
FREIGHT_TER
MS
Varchar2(25) Optional PO, ASN, REQ,
INV, RMA
16-16 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column Name Type Required? Receipt Type Source
Information
FREIGHT_BILL_
NUMBER
Varchar2(35) Optional PO, ASN, REQ,
INV, RMA
INVOICE_NUM Varchar2(30) Conditionally ASN
INVOICE_DATE Date Conditionally ASN
TOTAL_INVOIC
E_AMOUNT
Number Conditionally ASN
TAX_NAME Varchar2(25) Conditionally ASN
TAX_AMOUNT Number Conditionally ASN
FREIGHT_AMO
UNT
Number Optional ASN
CURRENCY_CO
DE
Varchar2(15) Optional ASN
CONVERSION_
RATE
Number Optional ASN
CONVERSION_
RATE_TYPE
Varchar2(30) Optional ASN
CONVERSION_
RATE_DATE
Date Optional ASN
PAYMENT_TER
MS_NAME
Varchar2(50) Optional ASN
PAYMENT_TER
MS_ID
Number Optional ASN
ATTRIBUTE_CA
TEGORY
Varchar2(30) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE1 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
Receiving Open Interface 16-17
Column Name Type Required? Receipt Type Source
Information
ATTRIBUTE2 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE3 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE4 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE5 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE6 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE7 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE8 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE9 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE10 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE11 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE12 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE13 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE14 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE15 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
16-18 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column Name Type Required? Receipt Type Source
Information
USSGL_TRANS
ACTION_CODE
Varchar2(30) Optional PO, ASN, REQ,
INV, RMA
EMPLOYEE_NA
ME
Varchar2(240) Conditionally PO, ASN, REQ,
INV, RMA
HR_EMPLOYEE
S.FULL_NAME
EMPLOYEE_ID Number Conditionally PO, ASN, REQ,
INV, RMA
HR_EMPLOYEE
S.EMPLOYEE_I
D
INVOICE_STAT
US_CODE
Varchar2(25) Optional ASN
PROCESSING_R
EQUEST_ID
Number NULL PO, ASN, REQ,
INV, RMA
CUSTOMER_AC
COUNT_NUMB
ER
Number Conditionally RMA HZ_CUST_ACC
OUNTS.ACCOU
NT_NUMBER
CUSTOMER_ID Number Conditionally RMA HZ_CUST_ACC
OUNTS.CUST_
ACCOUNT_ID
CUSTOMER_SIT
E_ID
Number Conditionally RMA OE_ORDER_LI
NES_ALL.SHIP_
FROM_ORG_ID
CUSTOMER_PA
RTY_NAME
Varchar2(360) Conditionally RMA HZ_PARTIES.P
ARTY_NAME
REMIT_TO_SITE
_ID
Number Optional ASN
AUTO_TRANSA
CT_CODE
Varchar2(25) Conditionally PO, ASN, REQ,
INV, RMA
SHIPMENT_NU
M
Varchar2(30) Conditionally PO, ASN, REQ,
INV, RMA
Receiving Open Interface 16-19
Column Name Type Required? Receipt Type Source
Information
ASN_TYPE Varchar2(25) Conditionally Possible Values
are ASN, ASBN,
STD
HEADER_INTERFACE_ID
The interface EDI header Sequence generated unique identifier.
GROUP_ID
The interface sequence generated group identifier for set processing. The GROUP_ID
cannot be longer than nine digits.
PROCESSING_STATUS_CODE
This column indicates the status of each row in the RCV_HEADERS_INTERFACE table.
The Receiving Open Interface selects a row for processing only when the value in this
column is PENDING.
RECEIPT_SOURCE_CODE
This column indicates the source of the shipment. It tells the Receiving Open Interface
whether the shipment is from an external supplier or an internal organization.
Currently, this column can accept a value of onlyVENDOR.
TRANSACTION_TYPE
This column indicates the transaction purpose code for the shipment header. This
column accepts a value of NEW or CANCEL.
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, and CREATED_BY
LAST_UPDATE_DATE indicates the date that the header record was last created or
updated. LAST_UPDATED_BY indicates the loading program or user name identifier
(ID) that was used to import the header record. CREATION_DATE indicates the date
that the header record was created. CREATED_BY indicates the loading program or
user ID that was used to import the header record. If you are importing data through
e-Commerce Gateway, values are provided in these columns automatically.
16-20 Oracle Supply Chain Management APIs and Open Interfaces Guide
VALIDATION_FLAG
This column indicates whether to validate a row before processing it. This column
accepts values of Y or N. The Receiving Open Interface provides a default value of Y.
EDI_CONTROL_NUM
This is the EDI transaction control number if you are sending data via EDI.
TEST_FLAG
This flag indicates that the transaction is in test mode.
NOTICE_CREATION_DATE
This is the EDI transaction creation date and time at source if you are importing data via
EDI.
RECEIPT_NUM
This column indicates the receipt number from the supplier. You must provide a value
in this column if AUTO_TRANSACT_CODE is not SHIP, the TRANSACTION_TYPE or
AUTO_TRANSACT_CODE in the RCV_TRANSACTIONS_INTERFACE table is not
SHIP, and the Receipt Number Options Entry method (in the Receiving Options
window) is Manual. The value in this column must be unique from the supplier for a
period of one year.
RECEIPT_HEADER_ID
This is the receipt system ID.
VENDOR_NAME, VENDOR_NUM, or VENDOR_ID
These are required for PO, ASN, or ASBN-related transactions. Leave these columns
blank when you receive against an RMA or intransit shipment.
VENDOR_NAME and VENDOR_NUM indicate the supplier name and number for the
shipment. Both must be a valid name or number in Oracle Purchasing. Either one must
be specified. (If you specify one, the Receiving Open Interface can derive the other.)
VENDOR_ID can be derived if either a VENDOR_NAME or VENDOR_NUM is
provided. If no VENDOR_NAME or VENDOR_NUM is provided, you must provide a
VENDOR_ID.
Receiving Open Interface 16-21
VENDOR_SITE_CODE
This is the supplier site code from PO/ASN; it is used to the derive VENDOR_SITE_ID.
VENDOR_SITE_ID
This is the source supplier site unique identifier.
FROM_ORGANIZATION_CODE
This is the source organization code; it is used to derive from the ORGANIZATION_ID.
FROM_ORGANIZATION_ID
This is the source organization unique identifier (internal transfers only).
SHIP_TO_ORGANIZATION_CODE or SHIP_TO_ORGANIZATION_ID
These columns indicate the destination organization for the shipment. A valid
inventory organization code in Oracle Purchasing is required for an ASN. If the
supplier does not know the ship-to organization, then the supplier can provide a ship-to
location (SHIP_TO_LOCATION_CODE or SHIP_TO_LOCATION_ID) that is tied to an
inventory organization in the Locations window, and the Receiving Open Interface can
derive the inventory organization that way. A SHIP_TO_ORGANIZATION_CODE or
SHIP_TO_ORGANIZATION_ID can be specified here in the
RCV_HEADERS_INTERFACE table, at the header level, or in the
RCV_TRANSACTIONS_INTERFACE table at the transaction line level. If the
SHIP_TO_ORGANIZATION_CODE or SHIP_TO_ORGANIZATION_ID is specified at
the header level, then it must apply to all shipments on the ASN. If it is specified at the
line level, then it can be different for each line.
A SHIP_TO_ORGANIZATION_CODE or SHIP_TO_ORGANIZATION_ID enables the
Receiving Open Interface to validate information at the line level before cascading
quantities at the shipment level. This information helps the Receiving Open Interface
determine if the supplier is providing valid item and shipment information.
LOCATION_CODE
This is the ship to and receiving location code.
LOCATION_ID
This is the receiving location unique identifier.
16-22 Oracle Supply Chain Management APIs and Open Interfaces Guide
BILL_OF_LADING
This is the bill of lading number.
PACKING_SLIP
This is the packing slip number.
SHIPPED_DATE
This column indicates that the date the shipment was shipped. The value in this column
is required for an ASN_TYPE of ASN or ASBN (for an ASN with billing information),
and must be earlier than or equal to the system date. The value must also be earlier than
or equal to the EXPECTED_RECEIPT_DATE.
FREIGHT_CARRRIER_CODE
This is the carrier who is responsible for shipment.
EXPECTED_RECEIPT_DATE
This is the expected arrival date of the shipment.
RECEIVER_ID
This is the employee unique identifier.
NUM_OF_CONTAINERS
This column indicates the umber of containers in the shipment.
WAYBILL_AIRBILL_NUM
This is the waybill or airbill number.
COMMENTS
These are the receiver's comments.
GROSS_WEIGHT and GROSS_WEIGHT_UOM_CODE
These columns indicate the shipment gross weight and shipment gross weight unit of
measure.
Receiving Open Interface 16-23
NET_WEIGHT and NET_WEIGHT_UOM_CODE
These columns indicate the shipment net weight and shipment net weight unit of
measure.
TAR_WEIGHT and TAR_WEIGHT_UOM_CODE
These columns indicate the shipment tar (container) weight and shipment tar
(container) weight unit of measure.
PACKAGING_CODE
This is the shipment packing code.
CARRIER_METHOD
This is the carrier transportation method code.
CARRIER_EQUIPMENT
This is the carrier equipment description code.
SPECIAL_HANDLING_CODE
This is the special handling code.
HAZARD_CODE, HAZARD_CLASS, and HAZARD_DESCRIPTION
These are the hazardous material qualifier code of the shipment, hazardous material
class of the shipment, and hazardous material description.
FREIGHT_TERMS
This is the freight payment method for example, prepaid or collect.
FREIGHT_BILL_NUMBER
This is the freight bill (PRO invoice) number.
INVOICE_NUM
A value for this column is required for ASBN transactions (if the ASN_TYPE is ASBN
for an ASN with billing information). The value must be unique for the given supplier.
16-24 Oracle Supply Chain Management APIs and Open Interfaces Guide
INVOICE_DATE
An invoice date is required for an ASBN transaction (if the ASN_TYPE is ASBN for an
ASN with billing information).
TOTAL_INVOICE_AMOUNT
This column is required for ASBN transactions (ASNs with billing information). For
ASBN transactions, you must provide a non-negative value in this column, even if that
value is 0.
TAX_NAME and TAX_AMOUNT
EDI transaction 857 tax name associated with the shipment or billing notice, and the tax
amount associated indicated at the header level.
FREIGHT_AMOUNT
This is the freight bill amount associated with the shipment or billing notice.
CURRENCY_CODE
This is the currency code associated with the shipment or billing notice.
CONVERSION_RATE_TYPE, CONVERSION_RATE, and CONVERSION_RATE_DATE
These are the exchange rate type, exchange rate, and the exchange rate date.
PAYMENT_TERMS_NAME and PAYMENT_TERMS_ID
These are the payment terms name and the payment terms unique identifier.
ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
ATRRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE13, ATTRIBUTE 14, and
ATTRIBUTE15
These are descriptive flexfield segments.
USGGL_TRANSACTION_CODE
This is the United States standard general ledger transaction code.
Receiving Open Interface 16-25
EMPLOYEE_NAME or EMPLOYEE_ID
This column indicates the employee who created the shipment. You must provide a
value in one of these columns if no value is provided in the corresponding columns in
the RCV_TRANSACTIONS_INTERFACE table and if the AUTO_TRANSACT_CODE is
RECEIVE. The value must be a valid employee name in Oracle Purchasing or Oracle
applications.
INVOICE_STATUS_CODE
For ASN with billing information (ASBN) only, this code indicates when line items are
rejected.
PROCESS_REQUEST_ID
This is the unique identifier for the request.
CUSTOMER_ACCOUNT_NUMBER and CUSTOMER_PARTY_NAME
This is the customer account number and customer name.
CUSTOMER_ID
This is the unique customer identifier. If this field is not populated, then the customer
account number and customer name are required.
CUSTOMER_SITE_ID
This is the unique identifier for the customer site.
REMIT_TO_SITE_ID
This is the remit-to site identifier.
AUTO_TRANSACT_CODE
This column accepts values of SHIP, RECEIVE, or DELIVER. A value is required for
ASN (ASN_TYPE) transactions. The value should be RECEIVE if you want to do a
receiving transaction and if you provide an EMPLOYEE_NAME or EMPLOYEE_ID at
the header level.
SHIPMENT_NUM
This column indicates the shipment number from the supplier. If no value is provided
in this column, the Receiving Open Interface tries to default a value from the
16-26 Oracle Supply Chain Management APIs and Open Interfaces Guide
PACKING_SLIP or INVOICE_NUM columns. The value of the SHIPMENT_NUM
column must be unique from the SUPPLIER, SUPPLIER SITE for a period of one year,
and validation on SHIPMENT_NUM happens only when a unique supplier site could
be derived for the supplier (when supplier site is left NULL in the header interface
table).
ASN_TYPE
This column accepts values of ASN or ASBN to indicate whether the transaction is for
an ASN or an ASN with billing information. A value is required only when importing
ASNs or ASBNs through e-Commerce Gateway. Leaving this column blank means that
the transaction is not for an ASN or ASBN, but for a receipt, depending on the values in
the AUTO_TRANSACT_CODE and TRANSACTION_TYPE columns.
Receiving Transactions Interface Table Description
The following table contains information about the
RVC_TRANSACTIONS_INTERFACE:
Column Name Type Required? Receipt Type Source
Information
INTERFACE_TR
ANSACTION_I
D
Number Yes PO, ASN, REQ,
INV, RMA
RCV_TRANSAC
TIONS_INTERF
ACE_S
GROUP_ID Number Yes PO, ASN, REQ,
INV, RMA
RCV_INTERFA
CE_GROUPS_S
LAST_UPDATE
_DATE
Date Yes PO, ASN, REQ,
INV, RMA
SYSDATE
LAST_UPDATE
D_BY
Number Yes PO, ASN, REQ,
INV, RMA
FND_GLOBAL.
USER_ID
CREATION_DA
TE
Date Yes PO, ASN, REQ,
INV, RMA
SYSDATE
CREATED_BY Number Yes PO, ASN, REQ,
INV, RMA
FND_GLOBAL.
USER_ID
LAST_UPDATE
_LOGIN
Number Yes PO, ASN, REQ,
INV, RMA
FND_GLOBAL.
LOGIN_ID
Receiving Open Interface 16-27
Column Name Type Required? Receipt Type Source
Information
TRANSACTION
_TYPE
Varchar2 Yes PO, ASN, REQ,
INV, RMA
Possible values:
SHIP, RECEIVE,
DELIVER,
TRANSFER,
ACCEPT,
REJECT,
CORRECT,
RETURN TO
VENDOR,
RETURN TO
RECEIVING,
RETURN TO
CUSTOMER
TRANSACTION
_DATE
Date Yes PO, ASN, REQ,
INV, RMA
PROCESSING_S
TATUS_CODE
Varchar2(25) Yes PO, ASN, REQ,
INV, RMA
PENDING
PROCESSING_
MODE_CODE
Varchar2(25) Yes PO, ASN, REQ,
INV, RMA
Possible values:
BATCH,
IMMEDIATE,
ONLINE
QUANTITY Number Yes PO, ASN, REQ,
INV, RMA
UNIT_OF_MEA
SURE
Varchar2(25) Yes PO, ASN, REQ,
INV, RMA
MTL_ITEM_UO
MS_VIEW.UNIT
_OF_MEASURE
RECEIPT_SOUR
CE_CODE
Varchar2(25 Yes PO, ASN, REQ,
INV, RMA
PO/ASN:
VENDOR
REQ:
INTERNAL
ORDER
INV:
INVENTORY
RMA:
CUSTOMER
16-28 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column Name Type Required? Receipt Type Source
Information
SOURCE_DOCU
MENT_CODE
Varchar2(25) Yes PO, ASN, REQ,
INV, RMA
Possible Values:
PO, REQ,
INVENTORY,
RMA
PARENT_TRAN
SACTION_ID
Number Conditionally PO, ASN, REQ,
INV, RMA
RCV_TRANSAC
TIONS.TRANSA
CTION_ID
DESTINATION_
TYPE_CODE
Varchar2(25) Yes PO, ASN, REQ,
INV, RMA
Possible Values:
'RECEIVING',
'INVENTORY',
'EXPENSE',
'SHOP FLOOR'
ITEM_NUM Varchar2(81) Conditionally PO, ASN, REQ,
INV, RMA
DOCUMENT_N
UM
Varchar2(30) Yes PO, ASN, REQ,
INV, RMA
DOCUMENT_LI
NE_NUM
Number Yes PO, ASN, REQ,
INV, RMA
DOCUMENT_S
HIPMENT_LINE
_NUM
Number Yes PO, ASN, REQ,
INV, RMA
DOCUMENT_DI
STRIBUTION_N
UM
Number Yes PO, ASN, REQ,
INV, RMA
VALIDATION_F
LAG
Varchar2(1) Yes PO, ASN, REQ,
INV, RMA
Y
PARENT_INTER
FACE_TXN_ID
Number Conditionally PO, ASN, REQ,
INV, RMA
RCV_TRANSAC
TIONS_INTERF
ACE.INTERFAC
E_TRANSACTI
ON_ID
Receiving Open Interface 16-29
Column Name Type Required? Receipt Type Source
Information
REQUEST_ID Number Null PO, ASN, REQ,
INV, RMA
PROGRAM_AP
PLICATION_ID
Number Null PO, ASN, REQ,
INV, RMA
PROGRAM_ID Number Null PO, ASN, REQ,
INV, RMA
PROGRAM_UP
DATE_DATE
Date Null PO, ASN, REQ,
INV, RMA
PROCESSING_R
EQUEST_ID
Number Null PO, ASN, REQ,
INV, RMA
CATEGORY_ID Number Conditionally PO, ASN, REQ,
INV, RMA
INTERFACE_SO
URCE_CODE
Varchar2(30) Optional PO, ASN, REQ,
INV, RMA
INTERFACE_SO
URCE_LINE_ID
Number Null
INV_TRANSAC
TION_ID
Number Null
ITEM_ID Number Conditionally PO, ASN, REQ,
INV, RMA
MTL_SYSTEM_I
TEMS.INVENT
ORY_ITEM_ID
ITEM_DESCRIP
TION
Varchar2(240) Conditionally PO, ASN, REQ,
INV, RMA
ITEM_REVISIO
N
Varchar2(3) Conditionally PO, ASN, REQ,
INV, RMA
UOM_CODE Varchar2(3) Yes PO, ASN, REQ,
INV, RMA
16-30 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column Name Type Required? Receipt Type Source
Information
EMPLOYEE_ID Number Conditionally PO, ASN, REQ,
INV, RMA
HR_EMPLOYEE
S.EMPLOYEE_I
D
SHIPMENT_HE
ADER_ID
Number Conditionally PO, ASN, REQ,
INV, RMA
RCV_SHIPMEN
T_HEADERS.SH
IPMENT_HEAD
ER_ID
SHIPMENT_LIN
E_ID
Number Conditionally ASN, REQ, INV RCV_SHIPMEN
T_HEADERS.SH
IPMENT_LINE_
ID
SHIP_TO_LOCA
TION_ID
Number Conditionally PO/ASN:
PO_LINE_LOCA
TIONS_ALL.SHI
P_TO_LOCATIO
N_ID
REQ/INV:
RCV_SHIPMEN
T_HEADERS.SH
IP_TO_LOCATI
ON_ID
PRIMARY_QUA
NTITY
Number Conditionally
PRIMARY_UNI
T_OF_MEASUR
E
Varchar2(25) Conditionally MTL_SYSTEM_I
TEMS_KFV.PRI
MARY_UNIT_O
F_MEASURE
VENDOR_ID Number Conditionally PO, ASN PO_HEADERS_
ALL.VENDOR_I
D
VENDOR_SITE_
ID
Number Conditionally PO, ASN PO_HEADERS_
ALL.VENDOR_S
ITE_ID
Receiving Open Interface 16-31
Column Name Type Required? Receipt Type Source
Information
FROM_ORGANI
ZATION_ID
Number Conditionally INV, RMA RCV_SHIPMEN
T_HEADERS.OR
GANIZATION_I
D
FROM_SUBINV
ENTORY
Varchar2(10) Optional INV, RMA
TO_ORGANIZA
TION_ID
Number Conditionally PO, ASN, REQ,
INV, RMA
INTRANSIT_O
WNING_ORG_I
D
Number Optional REQ, INV
ROUTING_HEA
DER_ID
Number Optional PO, ASN, REQ,
INV, RMA
ROUTING_STEP
_ID
Number Null
PO_HEADER_I
D
Number Conditionally PO, ASN PO_HEADERS_
ALL.PO_HEAD
ER_ID
PO_REVISION_
NUM
Number Conditionally PO, ASN
PO_RELEASE_I
D
Number Conditionally PO, ASN PO_RELEASES_
ALL.PO_RELEA
SE_ID
PO_LINE_ID Number Conditionally PO, ASN PO_LINES_ALL.
PO_LINE_ID
PO_LINE_LOCA
TION_ID
Number Conditionally PO, ASN PO_LINE_LOCA
TIONS_ALL.LIN
E_LOCATION_I
D
PO_UNIT_PRIC
E
Number Conditionally Conditionally
16-32 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column Name Type Required? Receipt Type Source
Information
CURRENCY_CO
DE
Varchar2(15) Optional PO, ASN, REQ,
INV, RMA
CURRENCY_CO
NVERSION_TY
PE
Varchar2(30) Optional PO, ASN, REQ,
INV, RMA
CURRENCY_CO
NVERSION_RA
TE
Number Optional PO, ASN, REQ,
INV, RMA
CURRENCY_CO
NVERSION_DA
TE
Date Optional PO, ASN, REQ,
INV, RMA
PO_DISTRIBUTI
ON_ID
Number Conditionally PO, ASN PO_DISTRIBUTI
ONS_ALL.DIST
RIBUTION_ID
REQUISITION_
LINE_ID
Number Null REQ PO_REQUISITI
ON_LINES_ALL
.REQUISITION_
LINE_ID
REQ_DISTRIBU
TION_ID
Number Null REQ PO_REQ_DISTR
IBUTIONS_ALL.
DISTRIBUTION
_ID
CHARGE_ACC
OUNT_ID
Number Optional PO, ASN, REQ
SUBSTITUTE_U
NORDERED_C
ODE
Varchar2(25) Optional PO, ASN
RECEIPT_EXCE
PTION_FLAG
Varchar2(1) Optional PO, ASN, REQ,
INV, RMA
ACCRUAL_STA
TUS_CODE
Varchar2(25) Optional PO, ASN, REQ,
INV, RMA
Receiving Open Interface 16-33
Column Name Type Required? Receipt Type Source
Information
INSPECTION_S
TATUS_CODE
Varchar2(25) Optional PO, ASN, REQ,
INV, RMA
INSPECTION_Q
UALITY_CODE
Varchar2(25) Optional PO, ASN, REQ,
INV, RMA
DELIVER_TO_P
ERSON_ID
Number Optional PO, ASN ,REQ
LOCATION_ID Number Optional PO, ASN, REQ,
INV, RMA
DELIVER_TO_L
OCATION_ID
Number Optional PO, ASN, REQ,
INV, RMA
SUBINVENTOR
Y
Varchar2(10) Conditionally PO, ASN, REQ,
INV, RMA
LOCATOR_ID Number Conditionally PO, ASN, REQ,
INV, RMA
WIP_ENTITY_I
D
Number Optional
WIP_LINE_ID Number Optional
DEPARTMENT_
CODE
Varchar2(10) Optional
WIP_REPETITIV
E_SCHEDULE_I
D
Number Optional
WIP_OPERATIO
N_SEQ_NUM
Number Optional
WIP_RESOURC
E_SEQ_NUM
Number Optional
BOM_RESOURC
E_ID
Number Optional
16-34 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column Name Type Required? Receipt Type Source
Information
SHIPMENT_NU
M
Varchar2(30) Conditionally PO, ASN, REQ,
INV, RMA
RCV_SHIPMEN
T_HEADERS.SH
IPMENT_NUM
FREIGHT_CAR
RIER_CODE
Varchar2(25) Optional PO, ASN, REQ,
INV, RMA
BILL_OF_LADI
NG
Varchar2(25) Optional PO, ASN, REQ,
INV, RMA
PACKING_SLIP Varchar2(25) Optional PO, ASN, REQ,
INV, RMA
SHIPPED_DATE Date Optional
EXPECTED_RE
CEIPT_DATE
Date Conditionally PO, ASN, REQ,
INV, RMA
ACTUAL_COST Number Optional
TRANSFER_CO
ST
Number Optional
TRANSPORTAT
ION_COST
Number Optional
TRANSPORTAT
ION_ACCOUNT
_ID
Number Optional
NUM_OF_CON
TAINERS
Number Optional PO, ASN, REQ,
INV, RMA
WAYBILL_AIRB
ILL_NUM
Varchar2(20) Optional
VENDOR_ITEM
_NUM
Varchar2(25) Optional PO, ASN
VENDOR_LOT_
NUM
Varchar2(30) Optional PO, ASN
Receiving Open Interface 16-35
Column Name Type Required? Receipt Type Source
Information
RMA_REFEREN
CE
Varchar2(30) Optional PO, ASN
COMMENTS Varchar2(24) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE_CA
TEGORY
Varchar2(30) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE1 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE2 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE3 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE4 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE5 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE6 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE7 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE8 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE9 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE10 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE11 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
16-36 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column Name Type Required? Receipt Type Source
Information
ATTRIBUTE12 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE13 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE14 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
ATTRIBUTE15 Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_HEAD_A
TTRIBUTE_CAT
EGORY
Varchar2(30) Optional PO, ASN, REQ,
INV, RMA
SHIP_HEAD_A
TTRIBUTE1
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_HEAD_A
TTRIBUTE2
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_HEAD_A
TTRIBUTE3
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_HEAD_A
TTRIBUTE4
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_HEAD_A
TTRIBUTE5
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_HEAD_A
TTRIBUTE6
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_HEAD_A
TTRIBUTE7
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_HEAD_A
TTRIBUTE8
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
Receiving Open Interface 16-37
Column Name Type Required? Receipt Type Source
Information
SHIP_HEAD_A
TTRIBUTE9
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_HEAD_A
TTRIBUTE10
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_HEAD_A
TTRIBUTE11
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_HEAD_A
TTRIBUTE12
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_HEAD_A
TTRIBUTE13
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_HEAD_A
TTRIBUTE14
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_HEAD_A
TTRIBUTE15
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_LINE_AT
TRIBUTE_CATE
GORY
Varchar2(30) Optional PO, ASN, REQ,
INV, RMA
SHIP_LINE_AT
TRIBUTE1
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_LINE_AT
TRIBUTE2
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_LINE_AT
TRIBUTE3
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_LINE_AT
TRIBUTE4
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_LINE_AT
TRIBUTE5
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
16-38 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column Name Type Required? Receipt Type Source
Information
SHIP_LINE_AT
TRIBUTE6
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_LINE_AT
TRIBUTE7
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_LINE_AT
TRIBUTE8
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_LINE_AT
TRIBUTE9
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_LINE_AT
TRIBUTE10
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_LINE_AT
TRIBUTE11
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_LINE_AT
TRIBUTE12
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_LINE_AT
TRIBUTE13
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_LINE_AT
TRIBUTE14
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
SHIP_LINE_AT
TRIBUTE15
Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
USSGL_TRANS
ACTION_CODE
Varchar2(30) Optional PO, ASN, REQ,
INV, RMA
GOVERNMENT
_CONTEXT
Varchar2(30) Optional PO, ASN, REQ,
INV, RMA
REASON_ID Number Optional PO, ASN, REQ,
INV, RMA
DESTINATION_
CONTEXT
Varchar2(30) Optional PO, ASN, REQ,
INV, RMA
Receiving Open Interface 16-39
Column Name Type Required? Receipt Type Source
Information
SOURCE_DOC_
QUANTITY
Number Optional PO, ASN, REQ,
INV, RMA
SOURCE_DOC_
UNIT_OF_MEA
SURE
Varchar2(25) Optional PO, ASN, REQ,
INV, RMA
MOVEMENT_I
D
Number Null
HEADER_INTE
RFACE_ID
Number Conditionally PO, ASN, REQ,
INV, RMA
VENDOR_CUM
_SHIPPED_QUA
NTITY
Number Null
TRUCK_NUM Varchar2(35) Optional PO, ASN, REQ,
INV, RMA
SHIP_TO_LOCA
TION_CODE
Varchar2(60) Conditionally PO, ASN, REQ,
INV, RMA
MTL_PARAMET
ERS.ORGANIZA
TION_CODE
CONTAINER_N
UM
Varchar2(35) Optional
SUBSTITUTE_IT
EM_NUM
Varchar2(81) Optional PO, ASN
NOTICE_UNIT_
PRICE
Number Optional ASN
ITEM_CATEGO
RY
Varchar2(81) Conditionally PO, ASN, REQ,
INV, RMA
LOCATION_CO
DE
Varchar2(60) Conditionally PO, ASN, REQ,
INV, RMA
16-40 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column Name Type Required? Receipt Type Source
Information
VENDOR_NAM
E
Varchar2(240) Conditionally PO, ASN PO_VENDORS.
VENDOR_NAM
E
VENDOR_NUM Varchar2(30) Conditionally PO, ASN PO_VENDORS.S
EGMENT1
VENDOR_SITE_
CODE
Varchar2(15) Conditionally PO, ASN PO_VENDOR_SI
TES_ALL.VEND
OR_SITE_CODE
FROM_ORGANI
ZATION_CODE
Varchar2(3) Null REQ, INV RCV_SHIPMEN
T_HEADERS.OR
GANIZATION_I
D
TO_ORGANIZA
TION_CODE
Varchar2(3) Null PO, ASN, REQ,
INV, RMA
MTL_PARAMET
ERS.ORGANIZA
TION_CODE
INTRANSIT_O
WNING_ORG_
CODE
Varchar2(3) Optional INV
ROUTING_COD
E
Varchar2(30) Optional PO, ASN, REQ,
INV, RMA
ROUTING_STEP Varchar2(30) Null
RELEASE_NUM Number Conditionally PO, ASN PO_RELEASES_
ALL.RELEASE_
NUM
DELIVER_TO_P
ERSON_NAME
Varchar2(240) Optional PO, ASN, REQ,
INV, RMA
DELIVER_TO_L
OCATION_COD
E
Varchar2(60) Optional PO, ASN, REQ,
INV, RMA
USE_MTL_LOT Number Optional
Receiving Open Interface 16-41
Column Name Type Required? Receipt Type Source
Information
USE_MTL_SERI
AL
Number Optional
LOCATOR Varchar2(81) Conditionally PO, ASN, REQ,
INV, RMA
REASON_NAM
E
Varchar2(30) Optional
SUBSTITUTE_IT
EM_ID
Number Conditionally
QUANTITY_SHI
PPED
Number Conditionally
QUANTITY_IN
VOICED
Number Conditionally
TAX_NAME Varchar2(15) Conditionally
TAX_AMOUNT Number Conditionally
REQ_NUM Varchar2(25) Conditionally REQ PO_REQUISITI
ONS_HEADERS
_ALL.SEGMENT
1
REQ_LINE_NU
M
Number Conditionally REQ PO_REQUISITI
ON_LINES_ALL
.LINE_NUM
REQ_DISTRIBU
TION_NUM
Number Conditionally REQ PO_REQ_DISTR
IBUTIONS_ALL.
DISTRIBUTION
_NUM
WIP_ENTITY_N
AME
Varchar2(24) Optional ASN
WIP_LINE_COD
E
Varchar2(10) Optional ASN
16-42 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column Name Type Required? Receipt Type Source
Information
RESOURCE_CO
DE
Varchar2(30) Optional ASN
SHIPMENT_LIN
E_STATUS_CO
DE
Varchar2(25) Optional
BARCODE_LAB
EL
Varchar2(35) Optional
TRANSFER_PER
CENTAGE
Number Optional
QA_COLLECTI
ON_ID
Number Conditionally
COUNTRY_OF_
ORIGIN_CODE
Varchar2(2)
OE_ORDER_HE
ADER_ID
Number Conditionally RMA OE_ORDER_HE
ADERS_ALL.HE
ADER_ID
OE_ORDER_LI
NE_ID
Number Conditionally RMA OE_ORDER_LI
NES_ALL.LINE_
ID
CUSTOMER_ID Number Conditionally RMA HZ_CUST_ACC
OUNTS.CUST_
ACCOUNT_ID
CUSTOMER_SIT
E_ID
Number Conditionally RMA OE_ORDER_LI
NES_ALL.SHIP_
FROM_ORG_ID
CUSTOMER_IT
EM_NUM
Varchar2(50) Conditionally RMA MTL_CUSTOME
R_ITEMS.CUST
OMER_ITEM_N
UMBER
CREATE_DEBIT
_MEMO_FLAG
Varchar2(1) Optional PO, ASN
Receiving Open Interface 16-43
Column Name Type Required? Receipt Type Source
Information
PUT_AWAY_RU
LE_ID
Number Null
PUT_AWAY_ST
RATEGY_ID
Number Null
LPN_ID Number Conditionally PO, ASN, REQ,
INV, RMA
TRANSFER_LP
N_ID
Number Conditionally PO, ASN, REQ,
INV, RMA
COST_GROUP_I
D
Number Null
MOBILE_TXN Varchar2(2) Null Y for a row
created through
Oracle
Warehouse
Management
mobile page. For
Receiving Open
Interface, this
should be N.
MMTT_TEMP_I
D
Number Null
TRANSFER_CO
ST_GROUP_ID
Number Null
SECONDARY_Q
UANTITY
Number Conditionally PO, ASN, REQ,
INV, RMA
SECONDARY_U
NIT_OF_MEAS
URE
Varchar2(25) Conditionally PO, ASN, REQ,
INV, RMA
SECONDARY_U
OM_CODE
Varchar2(3) Conditionally PO, ASN, REQ,
INV, RMA
16-44 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column Name Type Required? Receipt Type Source
Information
QC_GRADE Varchar2(150) Optional PO, ASN, REQ,
INV, RMA
FROM_LOCAT
OR
Varchar2(81) Conditionally REQ, INV
FROM_LOCAT
OR_ID
Number Conditionally REQ, INV
PARENT_SOUR
CE_TRANSACT
ION_NUM
Varchar2(25) Null
INTERFACE_A
VAILABLE_QTY
Number Null
INTERFACE_TR
ANSACTION_Q
TY
Number Null
INTERFACE_A
VAILABLE_AM
T
Number Null
INTERFACE_TR
ANSACTION_A
MT
Number Null
LICENSE_PLAT
E_NUMBER
Varchar2(30) Conditionally PO, ASN, REQ,
INV, RMA
SOURCE_TRAN
SACTION_NUM
Varchar2(25) Null
TRANSFER_LIC
ENSE_PLATE_N
UMBER
Varchar2(30) Conditionally
LPN_GROUP_I
D
Number Conditionally PO, ASN, REQ,
INV, RMA
Receiving Open Interface 16-45
Column Name Type Required? Receipt Type Source
Information
ORDER_TRANS
ACTION_ID
Number Null
CUSTOMER_AC
COUNT_NUMB
ER
Number Conditionally RMA HZ_CUST_ACC
OUNTS.ACCOU
NT_NUMBER
CUSTOMER_PA
RTY_NAME
Varchar2(360) Conditionally RMA HZ_PARTIES.P
ARTY_NAME
OE_ORDER_LI
NE_NUM
Number Conditionally RMA OE_ORDER_LI
NES_ALL.LINE_
NUMBER
OE_ORDER_NU
M
Number Conditionally RMA OE_ORDER_HE
ADERS_ALL.OR
DER_NUMBER
CUSTOMER_IT
EM_ID
Number Conditionally RMA MTL_CUSTOME
R_ITEMS.CUST
OMER_ITEM_ID
AMOUNT Number Conditionally PO, ASN
JOB_ID Number Optional PO
TIMECARD_ID Number Optional PO
TIMECARD_OV
N
Number Optional PO
ERECORD_ID Number Null
PROJECT_ID Number Null
TASK_ID Number Null
ASN_ATTACH_
ID
Number Null
16-46 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column Name Type Required? Receipt Type Source
Information
AUTO_TRANSA
CT_CODE
Varchar2(25) Conditionally PO, ASN, REQ,
INV, RMA
Possible values:
SHIP, RECEIVE,
DELIVER
INTERFACE_TRANSACTION_ID
Oracle Purchasing provides a unique-sequence generator to generate a unique identifier
for the receiving transaction line. If you are importing data through e-Commerce
Gateway, a value is provided automatically.
GROUP_ID
Oracle Purchasing provides a group identifier for a set of transactions that should be
processed together. The value in this column must match the GROUP_ID in the
RCV_HEADERS_INTERFACE table. The GROUP_ID cannot be longer than nine digits.
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN,
and CREATED_BY
LAST_UPDATE_DATE indicates that the date the line was last created or updated.
LAST_UPDATED_BY indicates the loading program or user name identifier (ID) that
was used to import the line. CREATION_DATE indicates the date that the line was
created. CREATED_BY indicates the loading program or user ID that was used to
import the line. If you are importing data through e-Commerce Gateway, values are
provided in these columns automatically.
TRANSACTION_TYPE
This column indicates the transaction purpose code. Valid values of
TRANSACTION_TYPE are SHIP, RECEIVE, ACCEPT, REJECT, TRANSFER,
CORRECT, DELIVER, RETURN TO RECEIVING, RETURN TO VENDOR, and
RETURN TO CUSTOMER.
TRANSACTION_DATE
This column indicates the date of the transaction. The date must either be in an open or
future entry Oracle Purchasing and Oracle General Ledger period. If Oracle Inventory is
installed the date must be in an open or future Oracle Inventory period.
Receiving Open Interface 16-47
PROCESSING_STATUS_CODE
This column indicates the status of each row in the
RCV_TRANSACTIONS_INTERFACE table. The Receiving Open Interface selects a row
for processing only when the value in this column is PENDING.
PROCESSING_MODE_CODE
This column defines how the Receiving Open Interface is to be called. It accepts a value
of BATCH only. You initiate one of these values when you submit the Receiving
Transaction Processor program through the Submit Request window.
TRANSACTION_STATUS_CODE
This column indicates the status of the transaction record. The Receiving Open Interface
provides a value of ERROR or COMPLETED.
During the cascade process, this quantity is allocated across all purchase order
shipments in a first-in, first-out manner if the DOCUMENT_SHIPMENT_LINE_NUM is
not specified. The cascade applies up to the amount ordered. However, if the quantity
exceeds the quantity on the purchase order shipments, then the last purchase order
shipment consumes the quantity ordered plus the allowable over-receipt tolerance.
All tolerances are checked as the quantity is cascaded. If the expected delivery date is
not within the Receipt Date tolerance (the date after which a shipment cannot be
received), and the Receipt Date Action in the Receiving Controls window is set to
Reject, Purchasing skips the PO_LINE_LOCATIONS row and goes to the next.
QUANTITY, AMOUNT
You must specify either the quantity or the amount, depending on the line type being
received against. For CORRECT transactions, it is possible for these to be negative.
Negative corrections are limited to the available supply on the parent transaction.
UNIT_OF_MEASURE
This column indicates the shipment quantity unit of measure (UOM) and is not
required when transacting against an amount-based line. If the UOM is different from
the primary UOM defined in Oracle Purchasing or the source document UOM, then
you must define a UOM conversion between the two UOMs.
RECEIPT_SOURCE_CODE
This column indicates the source of the shipment. Valid values of
RECEIPT_SOURCE_CODE are VENDOR, CUSTOMER, INTERNAL ORDER, and
INVENTORY. The Receiving Open Interface can derive the value here if one is
16-48 Oracle Supply Chain Management APIs and Open Interfaces Guide
provided in the RCV_HEADERS_INTERFACE table.
SOURCE_DOCUMENT_CODE
This column indicates the document type for the shipment. Valid values of
SOURCE_DOCUMENT_CODE are PO, RMA, REQ, and INVENTORY.
PARENT_TRANSACTION_ID
This column is the unique identifier of the parent receiving transaction.
DESTINATION_TYPE_CODE
You must provide a value for this column if the AUTO_TRANSACT_CODE is
DELIVER. If you do not provide a value, the Receiving Open Interface uses the
Destination Type on the purchase order shipment.
DOCUMENT_LINE_NUM, ITEM_NUM, VENDOR_ITEM_NUM, ITEM_ID, or PO_LINE_ID
You must provide a value for at least one of these columns, or for the CATEGORY_ID
(or ITEM_CATEGORY) and ITEM_DESCRIPTION columns. If at least one value is
provided, the Receiving Open Interface can derive some other values. For example, if a
PO_LINE_ID is provided, the Receiving Open Interface can derive the ITEM_NUM and
ITEM_ID.
DOCUMENT_LINE_NUM indicates the line number against which you are receiving.
The value in this column must be a valid number for the purchase order that you are
receiving against.
ITEM_NUM indicates the Purchasing item number of the item that you are receiving.
The item number must be defined in Oracle Purchasing for the DOCUMENT_NUM
provided and the SHIP_TO_ORGANIZATION_CODE.
VENDOR_ITEM_NUM indicates the supplier item number of the item that you are
receiving. The value in this column must be defined in Oracle Purchasing as a supplier
item number on the specified purchase order.
DOCUMENT_SHIPMENT_LINE_NUM and DOCUMENT_SHIPMENT_DISTRIBUTION_NUM
These columns are the purchase order shipment line number and distribution number.
VALIDATION_FLAG
This column tells the Receiving Open Interface whether to validate the row before
processing it. It accepts values of Y or N. The Receiving Open Interface enters a default
value of Y.
Receiving Open Interface 16-49
PARENT_INTERFACE_TXN_ID
This column is the parent receive transactions interface ID for the current row.
REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE
These columns are standard who columns.
PROCESSING_REQUEST_ID
This column identifies the concurrent request that is processing the interface row. The
Receiving Transaction processor sets this column.
ITEM_CATEGORY or CATEGORY_ID, or DOCUMENT_LINE_NUM or PO_LINE_ID
If you receive a shipment for an item that is not defined in Inventory (a one-time item),
you must provide an ITEM_CATEGORY or CATEGORY_ID, or the
DOCUMENT_LINE_NUM that the supplier is shipping against. This way, the
Receiving Open Interface can match the line and allocate the quantity shipped. If you
do not provide a value for ITEM_CATEGORY or CATEGORY_ID for a one-time item,
you must provide a value for DOCUMENT_LINE_NUM or PO_LINE_ID.
ITEM_DESCRIPTION
This column indicates the item description. If no item description is provided, the
Receiving Open Interface gets the item description from the purchase order line if a
PO_LINE_ID or similar column is provided. See the next description.
ITEM_REVISION
You must provide a value if the item is under revision control and you have
distributions with a destination type of Inventory. The value must be valid (defined in
Oracle Purchasing) for the item you are receiving and the organization that you are
receiving in. If no value is provided and one is required, the Receiving Open Interface
defaults the latest implemented revision.
INTERFACE_SOURCE_LINE_CODE and INTERFACE_SOURCE_LINE_ID
These columns contain the source of the interface row and the source line identifier of
the interface row.
UOM_CODE, PRIMARY_QUANTITY, PRIMARY_UNIT_OF_MEASURE
These columns are the transaction unit of measure code, primary unit of measure of the
item, and the transaction quantity in the primary unit of measure for the item.
16-50 Oracle Supply Chain Management APIs and Open Interfaces Guide
EMPLOYEE_ID
A value in this column is required if the TRANSACTION_TYPE is DELIVER. The value
can be derived if an EMPLOYEE_NUM is provided in the
RCV_HEADERS_INTERFACE table.
SHIPMENT_HEADER_ID and SHIPMENT_LINE_ID
These columns are the shipment header unique identifier and the shipment line unique
identifier.
SHIP_TO_LOCATION_CODE or SHIP_TO_LOCATION_ID
If a SHIP_TO_LOCATION_CODE or SHIP_TO_LOCATION_ID, or
SHIP_TO_ORGANIZATION_CODE or SHIP_TO_ORGANIZATION_ID is provided at
the header level, in the RCV_HEADERS_INTERFACE table, the Receiving Open
Interface can derive the SHIP_TO_LOCATION_CODE or SHIP_TO_LOCATION_ID at
the line level, in the RCV_TRANSACTIONS_INTERFACE table.
A value is always required in the SHIP_TO_LOCATION_CODE or
SHIP_TO_LOCATION_ID column for shipment (ASN or ASBN) transactions.
If the supplier does not provide ship-to organization information, then you must to tie
your ship-to locations to a single Inventory organization in the Locations window. This
way, the Receiving Open Interface can derive an organization based on the ship-to
location.
VENDOR_NAME, VENDOR_NUM, or VENDOR_ID
At least one of these columns is required if they are not already provided in the
RCV_HEADERS_INTERFACE table. These columns are relevant only to PO, ASN, or
ASBN-related transactions. Leave these columns blank when receiving against an RMA
or intransit shipment.
VENDOR_SITE_ID and VENDOR_SITE_CODE
These columns are the supplier site unique identifier and vendor site code.
FROM_ORGANIZATION_ID and FROM_SUBINVENTORY
These columns are the unique identifier for the source organization and source
subinventory.
TO_ORGANIZATION_CODE or TO_ORGANIZATION_ID
You must provide a value for at least one of these columns. (The Receiving Open
Receiving Open Interface 16-51
Interface can derive the other.) However, if you provide a
SHIP_TO_LOCATION_CODE or SHIP_TO_LOCATION_ID, and that location is tied to
an Inventory organization in the Locations window, then the Receiving Open Interface
can derive the TO_ORGANIZATION_CODE and TO_ORGANIZATION_ID.
The TO_ORGANIZATION_CODE indicates the destination ship-to organization code.
For standard receipts, ASNs, or ASBNs the TO_ORGANIZATION_CODE must match
the code in the receipt header.
INTRANSIT_OWNING_ORG_ID
This column is the organization that owns the items while they are intransit.
ROUTING_HEADER_ID
This column is the receiving routing unique identifier.
DOCUMENT_NUM or PO_HEADER_ID
This columns are required for only PO, ASN, or ASBN-related transactions. Leave these
columns blank when receiving against an RMA or intransit shipment.
The column DOCUMENT_NUM indicates the purchase order document number
against which to receive. The value in this column must be a valid purchasing
document in Oracle Purchasing. If you provide a value in either the
DOCUMENT_NUM or PO_HEADER_ID column, the other can be derived.
PO_REVISION_NUM and PO_RELEASE_ID
These columns are the PO revision number and the PO release identifier.
PO_LINE_LOCATION_ID and PO_UNIT_PRICE
These columns are the PO shipment identifier and the PO unit price at the time of
receipt.
CURRENCY_CODE, CURRENCY_CONVERSION, CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_DATE
These are the currency code, conversion type, conversion rate, and conversion date.
PO_DISTIRBUTION_ID
This is the PO distribution identifier.
16-52 Oracle Supply Chain Management APIs and Open Interfaces Guide
REQUISTION_LINE_ID
This column is the requisition line identifier.
REQ_DISTRIBUTION_ID
This column is the requisition distribution identifier.
CHARGE_ACCOUNT_ID
This column is the charge account identifier.
SUBSTITUTE_UNORDERED_CODE
This code indicates if the receipt is a substitute item or is an unordered receipt.
RECEIPT_EXCEPTION_FLAG
This flag indicates that there is an exception to the receipt.
ACCRUAL_STATUS_CODE
This column indicates the accrual status of the receipt.
INSPECTION_STATUS_CODE
This column indicates the quality inspection status of the receipt.
INSPECTION_QUALITY_CODE
This column indicates the quality inspection result of the receipt.
DELIVER_TO_PERSON_ID or DELIVER_TO_PERSON_NAME, SUBINVENTORY, and
LOCATOR or LOCATOR_ID
Values are required in these columns if the AUTO_TRANSACT_CODE is DELIVER and
if the Receiving Open Interface cannot find the values in the purchase order itself.
Additionally, LOCATOR or LOCATOR_ID is required if a Locator Control option is
selected for the delivery transaction at the item level (in the Master Items or
Organization Items windows), subinventory level (in the Subinventories window in
Inventory), or organization level (in the Organization window).
Receiving Open Interface 16-53
WIP_ENTITY_ID, WIP_LINE_ID, DEPARTMENT_CODE, WIP_REPETITIVE_SCHEDULE_ID,
WIP_OPERATION_SEQ_NUM, WIP_RESOURCE_SEQ_NUM, and BOM_RESOURCE_ID
These columns relate to WIP jobs and assemblies. They are:
WIP_ENTITY_ID: WIP job or repetitive assembly identifier
WIP_LINE_ID: WIP line identifier
DEPARTMENT_CODE: WIP department code
WIP_REPETITIVE_SCHEDULE_ID: WIP repetitive schedule identifier
WIP_OPERATION_SEQ_NUM: WIP operation sequence number within a routing
WIP_RESOURCE_SEQ_NUM: WIP resource sequence number
BOM_RESOURCE_ID: BOM resource unique identifier
SHIPMENT_NUMBER
This column is the supplier or source organization shipment number.
FREIGHT_CARRRIER_CODE
This column is the carrier who is responsible for shipment.
BILL_OF_LADING
This column is the bill of lading number.
PACKING_SLIP
This column is the packing slip number.
SHIPPED_DATE
This column indicates the that date the shipment was shipped. The value in this column
is required for an ASN_TYPE of ASN or ASBN (for an ASN with billing information).
This value must be earlier than or equal to the system date. It must also be earlier than
or equal to the EXPECTED_RECEIPT_DATE.
EXPECTED_RECEIPT_DATE
A value in this column is required if none is provided in the
RCV_HEADERS_INTERFACE table. The date must fall within the receipt date
16-54 Oracle Supply Chain Management APIs and Open Interfaces Guide
tolerance for the shipments with which the receipt is being matched.
ACTUAL_COST, TRANSFER_COST and _TRANSPORTATION_COST
These columns are the actual cost for an item, the cost of transferring items between
organizations, and the costing of shipping items between organizations.
TRANSPORTATION_ACCOUNT_ID
This column is the account to be charged for transportation costs.
NUM_OF_CONTAINERS
This column is the number of containers in the shipment.
VENDOR_ITEM_NUM and VENDOR_LOT_NUM
These columns are the supplier item number and the supplier lot number.
RMA_REFERENCE
This column is the RMA reference number for return_to_supplier items.
COMMENTS
These are the receiver's comments.
ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
ATRRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE13, ATTRIBUTE 14, and
ATTRIBUTE15
These columns are descriptive flexfield segments.
SHIP_HEAD_ATTRIBUTE_CATEGORY, SHIP_HEAD_ATTRIBUTE1,
SHIP_HEAD_ATTRIBUTE2, SHIP_HEAD_ATTRIBUTE3, SHIP_HEAD_ATTRIBUTE4,
SHIP_HEAD_ATTRIBUTE5, SHIP_HEAD_ATTRIBUTE6, SHIP_HEAD_ATTRIBUTE7,
SHIP_HEAD_ATTRIBUTE8, SHIP_HEAD_ATTRIBUTE9, SHIP_HEAD_ATTRIBUTE10,
SHIP_HEAD_ATTRIBUTE11, SHIP_HEAD_ATTRIBUTE12, SHIP_HEAD_ATTRIBUTE13,
SHIP_HEAD_ATTRIBUTE14, SHIP_HEAD_ATTRIBUTE15
These columns are shipment header descriptive flexfield segments.
Receiving Open Interface 16-55
SHIP_LINE_ATTRIBUTE2, SHIP_LINE_ATTRIBUTE3, SHIP_LINE_ATTRIBUTE4,
SHIP_LINE_ATTRIBUTE5, SHIP_LINE_ATTRIBUTE6, SHIP_LINE_ATTRIBUTE7,
SHIP_LINE_ATTRIBUTE8, SHIP_LINE_ATTRIBUTE9, SHIP_LINE_ATTRIBUTE10,
SHIP_LINE_ATTRIBUTE11, SHIP_LINE_ATTRIBUTE12, SHIP_LINE_ATTRIBUTE13,
SHIP_LINE_ATTRIBUTE14, SHIP_LINE_ATTRIBUTE15
These columns are shipment line descriptive flexfield segments.
USGGL_TRANSACTION_CODE and GOVERNMENT_CONTEXTS
These are the United States standard general ledger transaction code and descriptive
flexfield context.
REASON_ID
This column is the transaction reason identifier.
DESTINATION_CONTEXT
This column is the destination descriptive flexfield context.
SOURCE_DOC_QUANTITY and SOURCE_DOC_UNIT_OF_MEASURE
These columns are the source document unit of measure and transaction quantity.
HEADER_INTERFACE_ID
The HEADER_INTERFACE_ID (and a corresponding row in
RCV_HEADERS_INTERFACE) are required for SHIP or RECEIVE transactions. Child
transactions should not have a new header.
Oracle Purchasing provides a unique identifier for the corresponding header. The value
in this column must match the HEADER_INTERFACE_ID in the
RCV_HEADERS_INTERFACE table. If you are importing data through e-Commerce
Gateway, a value is provided automatically.
MOVEMENT_ID
This is the movement identifier.
VENDOR_CUM_SHIPPED_QTY
This is the supplier cumulative shipped quantity specified on the advanced shipment
notice.
16-56 Oracle Supply Chain Management APIs and Open Interfaces Guide
TRUCK_NUM
This is the truck number.
CONTAINER_NUM
This is the container number.
SUBSTITUTE_ITEM_NUM
This is the buyer's substitute item number specified on the ASN.
NOTICE_UNIT_PRICE
This is the EDI transaction 857 unit price
FROM_ORGANIZATION_CODE and INTRANSIT_OWNING_ORG_CODE
These are the from organization code for interorganization transfers and the in-transit
owning organization code for interorganization transfers.
ROUTING_CODE
This is the receiving routing name.
ROUTING_STEP
This is the upgrade step unique identifier.
RELEASE_NUMBER
This is the release number for the purchase order.
DELIVER_TO_LOCATION_CODE or DELIVER_TO_LOCATION_ID
A value is required in one of these columns if the AUTO_TRANSACT_CODE is
DELIVER. If you do not provide a value, the Receiving Open Interface uses the
deliver-to location on the purchase order shipment.
PARENT_TRANSACTION_ID, PARENT_INTERFACE_TXN_ID,
SOURCE_TRANSACTION_NUM, PARENT_SOURCE_TRANSACTION_NUM
If the transaction is not a root transaction, (a SHIP or RECEIVE), then the Receiving
Open Interface needs to be able to find the appropriate parent. If the parent transaction
is in the interface tables, and has not yet been processed,
PARENT_INTERFACE_TXN_ID should be populated with the
Receiving Open Interface 16-57
RCV_TRANSACTIONS_INTERFACE.INTERFACE_TRANSACTION_ID of the parent.
If the parent has been processed in an earlier batch, PARENT_TRANSACTION_ID
should be populated with the RCV_TRANSACTIONS.TRANSACTION_ID of the
parent. The Receiving Open Interface can also derive the appropriate value if
SOURCE_TRANSACTION_NUM is populated on the parent, and
PARENT_SOURCE_TRANSACTION_NUM is populated on the new transaction. This
enables equivalent hierarchies to be fed in directly from other systems.
USE_MTL_LOT and USE_MTL_SERIAL
These columns are parameters used for lot and serial processing.
REASON_NAME
This is column is the transaction reason name.
SUBSTITUTE_ITEM_ID
This column is the substitute item unique identifier that the buyer uses.
QUANTITY_SHIPPED and QUANTITY_INVOICED
These columns represent the quantity shipped and the quantity invoiced.
TAX_NAME and TAX_AMOUNT
These columns are the EDI transaction 857 tax name associated with the shipment or
billing notice, and the tax amount associated at the header level.
REQ_NUM, REQ_LINE_NUM, and REQ_DISTRIBUTION_NUM
These columns represent requisition number, requisition line number, and requisition
distribution number.
SHIPMENT_LINES_STATUS_CODE
This column is the status code for the shipment line.
BARCODE_LABEL
Bar-coded detail label serial number if indicated on ASN for inner packaging of this
item; to be matched for barcode-scanned receipt updating.
TRANSFER_PERCENTAGE
This column is the transfer percentage for costing.
16-58 Oracle Supply Chain Management APIs and Open Interfaces Guide
QA_COLLECTION_ID
This column is the primary key for Oracle Quality results.
COUNTRY_OF_ORIGIN
This column is the code for the manufacturing country.
OE_ORDER_NUM, OE_ORDER_HEADER_ID, OE_ORDER_LINE_NUM,
OE_ORDER_LINE_ID
The OE_ORDER_HEADER_ID and OE_ORDER_LINE_ID are required for transactions
against an RMA. They can be derived from OE_ORDER_NUM and
OE_ORDER_LINE_NUM, or DOCUMENT_NUM and DOCUMENT_LINE_NUM.
CUSTOMER_ID
This column is the Unique Customer identifier. If this field is not populated, then the
customer account number and customer name are required.
CUSTOMER_SITE_ID
This column is the unique identifier for the customer site.
CUSTOMER_ITEM_NUM
This is the customer item number.
CREATE_DEBIT_MEMO_FLAG
This parameter determines if you need to create a debit memo.
LICENSE_PLATE_NUMBER, LPN_ID, TRANSFER_LICENSE_PLATE_NUMBER,
TRANSFER_LPN_ID
Once items have been packed into an LPN, it is necessary to specify a from and to LPN
on each subsequent transaction. Failure to do so will lead to implicit unpacking of the
LPN. The TRANSFER_LICENSE_PLATE_NUMBER must be specified when packing
items into an LPN that is being created in the same batch of transactions. If the LPN
already exists, either the TRANSFER_LPN_ID or the
TRANSFER_LICENSE_PLATE_NUMBER is acceptable. Similarly, the from LPN_ID can
be derived from the LICENSE_PLATE_NUMBER, if the LPN already exists.
Receiving Open Interface 16-59
COST_GROUP_ID and TRANSFER_COST_GROUP_ID
These columns are the Oracle Warehouse Management cost group identifier and the
transfer cost group identifier.
MMTT_TEMP_ID
This column references the original inventory transaction for Oracle Warehouse
Management.
SECONDARY_UNIT_OF_MEASURE, SECONDARY_UOM_CODE, and
SECONDARY_QUANTITY,
These columns are the transaction secondary unit of measure, the transaction secondary
unit of measure code, and the transaction quantity in the secondary unit of measure.
QC_GRADE
This column is the quality grade of the received item.
PARENT_SOURCE_TRANSACTION_NUM
This column is the parent source transaction number.
INTERFACE_AVAILABLE_QTY
This column is the available quantity for the receiving transaction interface rows.
INTERFACE_TRANACTION_QTY
This column is the available transaction quantity.
INTERFACE_AVAILABLE_AMT
This column is the available amount for the children receiving transaction interface
rows.
INTERFACE_TRANSACTION_AMT
This column is the available transaction quantity.
SOURCE_TRANSACTION_NUM
This column is the source transaction number.
16-60 Oracle Supply Chain Management APIs and Open Interfaces Guide
LPN_GROUP_ID
The LPN_GROUP_ID is used to group multiple Receiving transactions together when
they represent a single physical LPN-based transaction (for example, moving an LPN
from one location to another). The grouped transactions will succeed or fail as a unit, so
if one row fails validation, all grouped rows will be marked as errors. This column will
be automatically derived in cases where RVCTP explodes the LPN contents into the
component transactions.
ORDER_TRANSACTION_ID
This column is the order transaction identifier.
CUSTOMER_ACCOUNT_NUMBER and CUSTOMER_PARTY_NAME
These columns are the customer account number and customer name.
CUSTOMER_ITEM_ID
This column is the customer item identifier.
AMOUNT
This is the amount.
JOB_ID
This column is the job identifier for services.
TIMECARD_ID
This column is the timecard identifier.
TIMECARD_OVN
This column represents revisions to the timecard.
ERECORD_ID
This is the eRecord identifier.
PROJECT_ID and TASK_ID
These parameters represent the project and task ID for the timecard.
Receiving Open Interface 16-61
ASN_ATTACH_ID
This is the ASN attach ID.
AUTO_TRANSACT_CODE
The AUTO_TRANSACT_CODE is relevant only to SHIP or RECEIVE transactions and
indicates the automatic transaction creation code of the shipment. It accepts values of
RECEIVE for a standard receipt, DELIVER for a standard receipt and delivery
transaction, and SHIP for a shipment (ASN or ASBN) transaction.
Whether you can perform a standard receipt (RECEIVE) or direct receipt (DELIVER)
depends on the ROUTING_HEADER_ID in the PO_LINE_LOCATIONS table and the
Purchasing profile option RCV: Allow routing override.
The AUTO_TRANSACT_CODE in the RCV_TRANSACTIONS_INTERFACE table
overrides that in the RCV_HEADERS_INTERFACE table, if the two values differ.
The table following table shows the combinations of TRANSACTION_TYPE and
AUTO_TRANSACT_CODE values that you can choose in the
RCV_TRANSACTIONS_INTERFACE table to create an ASN or ASBN shipment header
and shipment lines, a receiving transaction, or a receiving and delivery transaction.
TRANSACTION_TYP
E
NULL RECEIVE DELIVER
SHIP Shipment header and
shipment lines
created
Receiving transaction
created
Receiving and
delivery transaction
created
RECEIVE Receiving transaction
created
Receiving transaction
created
Receiving and
delivery transaction
created
Oracle Warehouse Management License Plate Number Interface Table
Description
The following table contains information about the WMS_LPN_INTERFACE table: For
additional information about the WMS_LPN_INTERFACE table see Oracle Warehouse
Management LPN Interface Table Supplemental Information, Oracle Manufacturing APIs
and Open Interfaces Manual.
16-62 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column Name Type Required? Receipt
Type
Source Information
LICENSE_PLATE_
NUMBER
VARCHAR2
(30)
Yes PO, ASN,
REQ, INV
RMA
RCV_TRANSACTIONS_INT
ERFACE.TRANSFER_LICE
NSE_PLATE_NUMBER
LAST_UPDATE_D
ATE
DATE Yes PO, ASN,
REQ, INV
RMA
SYSDATE
LAST_UPDATED_
BY
NUMBER Yes PO, ASN,
REQ, INV
RMA
FND_GLOBAL.USER_ID
CREATION_DATE DATE Yes PO, ASN,
REQ, INV
RMA
SYSDATE
CREATED_BY NUMBER Yes PO, ASN,
REQ, INV
RMA
FND_GLOBAL.USER_ID
LAST_UPDATE_L
OGIN
NUMBER Yes PO, ASN,
REQ, INV
RMA
FND_GLOBAL.LOGIN_ID
PARENT_LICENS
E_PLATE_NUMBE
R
VARCHAR2
(30)
Conditionall
y
PO, ASN,
REQ, INV
RMA
OUTERMOST_LP
N
VARCHAR2
(30)
Yes PO, ASN,
REQ, INV
RMA
SOURCE_GROUP_
ID
NUMBER Yes PO, ASN,
REQ, INV
RMA
RCV_TRANSACTIONS_INT
ERFACE.LPN_GROUP_ID
LPN_ID NUMBER Optional
INVENTORY_ITE
M_ID
NUMBER Optional
REQUEST_ID NUMBER Optional
Receiving Open Interface 16-63
Column Name Type Required? Receipt
Type
Source Information
PROGRAM_APPLI
CATION_ID
NUMBER Optional
PROGRAM_ID NUMBER Optional
PROGRAM_UPDA
TE_DATE
DATE Optional
REVISION VARCHAR2
(3)
Optional
LOT_NUMBER VARCHAR2
(80)
Optional
SERIAL_NUMBER VARCHAR2
(30)
Optional
ORGANIZATION_
ID
NUMBER Optional
SUBINVENTORY_
CODE
VARCHAR2
(10)
Optional
LOCATOR_ID NUMBER Optional
COST_GROUP_ID NUMBER Optional
PARENT_LPN_ID NUMBER Optional
OUTERMOST_LP
N_ID
NUMBER Optional
GROSS_WEIGHT_
UOM_CODE
VARCHAR2
(3)
Optional
GROSS_WEIGHT NUMBER Optional
CONTENT_VOLU
ME_UOM_CODE
VARCHAR2
(3)
Optional
16-64 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column Name Type Required? Receipt
Type
Source Information
CONTENT_VOLU
ME
NUMBER Optional
TARE_WEIGHT_U
OM_CODE
VARCHAR2
(3)
Optional
TARE_WEIGHT NUMBER Optional
STATUS_ID NUMBER Optional
LPN_CONTEXT NUMBER Optional
SEALED_STATUS NUMBER Optional
LPN_REUSABILIT
Y
NUMBER Optional
HOMOGENEOUS
_CONTAINER
NUMBER Optional
ATTRIBUTE_CAT
EGORY
VARCHAR2
(30)
Optional
ATTRIBUTE1 VARCHAR2
(150)
Optional
ATTRIBUTE2 VARCHAR2
(150)
Optional
ATTRIBUTE3 VARCHAR2
(150)
Optional
ATTRIBUTE4 VARCHAR2
(150)
Optional
ATTRIBUTE5 VARCHAR2
(150)
Optional
ATTRIBUTE6 VARCHAR2
(150)
Optional
Receiving Open Interface 16-65
Column Name Type Required? Receipt
Type
Source Information
ATTRIBUTE7 VARCHAR2
(150)
Optional
ATTRIBUTE8 VARCHAR2
(150)
Optional
ATTRIBUTE9 VARCHAR2
(150)
Optional
ATTRIBUTE10 VARCHAR2
(150)
Optional
ATTRIBUTE11 VARCHAR2
(150)
Optional
ATTRIBUTE12 VARCHAR2
(150)
Optional
ATTRIBUTE13 VARCHAR2
(150)
Optional
ATTRIBUTE14 VARCHAR2
(150)
Optional
ATTRIBUTE15 VARCHAR2
(150)
Optional
SOURCE_TYPE_ID NUMBER Optional
SOURCE_HEADE
R_ID
NUMBER Optional
SOURCE_LINE_ID NUMBER Optional
SOURCE_LINE_D
ETAIL_ID
NUMBER Optional
SOURCE_NAME VARCHAR2
(30)
Optional
16-66 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column Name Type Required? Receipt
Type
Source Information
ORGANIZATION_
CODE
VARCHAR2
(3)
Optional
LICENSE_PLATE_NUMBER
This is the license plate number (LPN).
PARENT_LICENSE_PLATE_NUMBER
This is the LPN of the container in which the current LPN resides.
OUTERMOST_LPN
This is the outermost LPN.
SOURCE_GROUP_ID
This is the source group of the LPN. It joins to the group_id column for transactions. For
Receiving Open Interface transactions this column joins to the LPN_GROUP_ID on
RCV_TRANSACTIONS_INTERFACE.
LPN_ID
This is a unique identifier for each row in this table.
INVENTORY_ITEM_ID
This is the unique ID of an inventory item associated with an LPN (for example, a
container item).
PROGRAM_UPDATE_DATE, PROGRAM_ID, REQUEST_ID, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, and LAST_UPDATE_DATE
These are standard who columns.
REVISION
This is the revision of the container item if it is under revision control.
Receiving Open Interface 16-67
LOT_NUMBER
This is the lot number if the container item is under lot control.
SERIAL_NUMBER
This is the serial number if the container item is under serial control.
ORGANIZATION_ID
This is the current organization identifier.
SUBINVENTORY_CODE
This is the current subinventory code.
LOCATOR_ID
This is the current locator if the container item is under locator control.
COST_GROUP_ID
This is the he cost group identifier.
PARENT_LPN_ID
This is the LPN ID of the container in which the current LPN resides.
OUTERMOST_LPN_ID
This is the outermost LPN ID.
GROSS_WEIGHT_UOM_CODE
This is the UOM code for the weight of the container and its contents.
GROSS_WEIGHT
This is the weight of the container body and its contents.
CONTENT_VOLUME_UOM_CODE
This is the UOM code for the container content volume.
16-68 Oracle Supply Chain Management APIs and Open Interfaces Guide
CONTENT_VOLUME
This is the total volume of the container contents.
TARE_WEIGHT_UOM_CODE
This is the UOM code for the tare weight of the container.
TARE_WEIGHT
This is the tare weight for the container and its contents.
STATUS_ID
This is the current status of the container instance (LPN).
LPN_CONTEXT
This is the LPN context.
SEALED_STATUS
This column represents if the container is sealed or not.
LPN_REUSABILITY
This column determines if the LPN is resusable (1. Permanent, 2. Disposable).
HOMOGENEOUS_CONTAINER
This column indicates whether mixed items are allowed within the LPN. The default
null indicates mixed items are allowed within the LPN.
ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, and ATTRIBUTE15
These are descriptive flexfield segment columns.
SOURCE_TYPE_ID
This is the source type of the LPN.
Receiving Open Interface 16-69
SOURCE_HEADER_ID
This is the source header ID of the LPN.
SOURCE_LINE_ID
This is the source line ID of the LPN.
SOURCE_LINE_DETAIL_ID
This is the source line detail ID of the LPN.
SOURCE_NAME
This is the source name of the LPN.
Derived Data
The Receiving Open Interface derives or defaults derived columns using logic similar to
that used by the Receipts, Receiving Transactions, or Manage Shipments windows.
Oracle Purchasing never overrides information that you provide in derived columns.
When a column exists in both the RCV_HEADERS_INTERFACE and
RCV_TRANSACTIONS_INTERFACE tables, if you provide a value for the column in
the RCV_HEADERS_INTERFACE table, the Receiving Open Interface can derive a
value for the same column in the RCV_TRANSACTIONS_INTERFACE table. The
LOCATION_CODE in the headers table and SHIP_TO_LOCATION_CODE in the
transactions table are examples of this. In general, the Receiving Open Interface tries
first to derive values in the RCV_TRANSACTIONS_INTERFACE table based on values
in the RCV_HEADERS_INTERFACE table; then, if no corresponding values are there, it
tries to derive them from the purchase order.
Some examples of derivation are, in the RCV_HEADERS_INTERFACE table. The
RECEIPT_NUM is derived if the AUTO_TRANSACT_CODE is DELIVER or RECEIVE
and, in the RCV_TRANSACTIONS_INTERFACE table, the
DESTINATION_TYPE_CODE is derived if the TRANSACTION_TYPE is DELIVER.
Optional Data
Optional columns in the interface tables use the same rules as their corresponding fields
in the Receipts, Receiving Transactions, and Manage Shipments windows in Oracle
Purchasing. Here are some of these rules:
RELEASE_NUM must be a valid release number for the purchasing document
number provided and, if a release number is not provided, the Receiving Open
Interface allocates the quantity across all open shipments for all releases.
16-70 Oracle Supply Chain Management APIs and Open Interfaces Guide
DOCUMENT_SHIPMENT_LINE_NUM must be a valid number for the line that
you are receiving against if the line number (DOCUMENT_LINE_NUM) is
provided. If a DOCUMENT_SHIPMENT_LINE_NUM is not provided, the
Receiving Open Interface allocates the shipment quantity against the shipments in a
first-in, first-out order based on the PROMISED_DATE or the NEED_BY_DATE in
the Oracle Purchasing tables.
SUBSTITUTE_ITEM_NUM - must be defined in Oracle Purchasing as a related item
for an item on the provided DOCUMENT_NUM. The original item must allow
substitute receipts, and the supplier must be enabled to send substitute items. The
substitute item also must be enabled as an Oracle Purchasing item.
REASON_NAME indicates the transaction reason, as defined in the Transaction
Reasons window in Inventory.
Some other example information about optional data is, in the
RCV_HEADERS_INTERFACE table, the EXPECTED_RECEIPT_DATE must be later
than or equal to the SHIPPED_DATE, if a SHIPPED_DATE is given. Also, if Oracle
Supplier Scheduling is installed and set up, and the value in the
VENDOR_CUM_SHIPPED_QUANTITY column does not match what you have
received, then your supplier is notified through an Application Advice (if you are
receiving ASNs through e-Commerce Gateway).
Validation
Oracle Purchasing validates all required columns in the interface tables. For specific
information on the data implied by these columns, see the Oracle eTechnical Reference
Manual .
Other Validation
If a row in the interface tables fails validation for any reason, the program sets the
PROCESSING_STATUS_CODE to ERROR and enters details about errors on that row
into the PO_INTERFACE_ERRORS table.
In general, the same validations are performed in the Receiving Open Interface tables as
are performed in the Receipts, Receiving Transactions, and Manage Shipments
windows.
Debugging
Debugging enables you perform a test run of the Receiving Open Interface, see and fix
the errors, and run the program again. The system writes the logs to the table
FND_LOG_MESSAGES table. In addition, the system includes debug information in the
Receiving Transaction Processor Request Log in the BATCH/IMMEDIATE mode. You
must set the following profile options so that the system can write the log.
Receiving Open Interface 16-71
RCV: Debug Mode: Yes
FND: Debug Log Enabled: Yes
FND: Debug Log Level: Statement
FND: Debug Log Module: purchase order percent
INV: Debug Trace: Yes
INV: Debug File (including the complete path): <utl_file_dir directory
name>/<filename>
To get the log in the batch immediate mode from the table, use the following script
where &reqid is the Concurrent request ID of the Receiving Transaction Processor in
the batch/immediate mode:
SELECT MODULE, MESSAGE_TEXT
FROM FND_LOG_MESSAGES
WHERE TIMESTAMP > SYSDATE - 2/24 AND
PROCESS_ID = (
SELECT OS_PROCESS_ID
FROM FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID = &REQID)
AND MODULE LIKE 'PO%'
ORDER BY LOG_SEQUENCE ASC;
To place the log in online mode from the table:
1. SELECT MAX(LOG_SEQUENCE) FROM FND_LOG_MESSAGES, and record the
value.
2. Perform the transaction in the online mode.
3. SELECT MAX(LOG_SEQUENCE) FROM FND_LOG_MESSAGES.
4. SELECT MODULE, MESSAGE_TEXT FROM the FND_LOG_MESSAGES WHERE
LOG_SEQUENCE is between the value from step 1 and the value from step 2.
Resolving Failed Receiving Open Interface Rows
Error Messages
Oracle Purchasing may display specific error messages during interface processing. For
more details on these messages, see the Oracle Applications Messages Manual.
Viewing Failed Transactions
For each row in the RCV_HEADERS_INTERFACE and
RCV_TRANSACTIONS_INTERFACE tables that fails validation, the Receiving Open
16-72 Oracle Supply Chain Management APIs and Open Interfaces Guide
Interface creates one or more rows with error information in the
PO_INTERFACE_ERRORS table.
You can report on all rows that failed validation by using the Receiving Interface Errors
report and, for ASBNs, the Purchasing Interface Errors Report. For every transaction in
the interface table that fails validation, these reports list all the columns that failed
validation along with the reason for the failure.
You can identify failed transactions in the interface tables by selecting rows with a
PROCESS_FLAG of ERROR or PRINT. For any previously processed set of rows
identified by the HEADER_INTERFACE_ID and INTERFACE_TRANSACTION_ID,
only rows that failed validation remain in the interface table. As all the successfully
imported rows are deleted from the RCV_TRANSACTIONS_INTERFACE table.
(Successfully imported rows in the RCV_HEADERS_INTERFACE table are not
deleted.)
Related Topics
Purchasing Interface Errors Report, Oracle Purchasing User's Guide
Receiving Interface Errors Report, Oracle Purchasing User's Guide
Receiving Open Interface Details for Advanced Shipment Notice Import
and Receipt Transactions
This section covers the following transactions:
ASN/ASBN Creation
PO Receipt
ASN Receipt
RMA Receipt
Internal Requisition Receipt
Inter-Org Transfer Receipt
Receiving Open Interface 16-73
RCV_HEADERS_INTERFACE (RHI)
Column
Name
ASN/ASB
N
Creation
PO
Receipt
ASN
Receipt
RMA
Receipt
Internal
Requisitio
n
Inter-Org
Transfer
Receipt
HEADER_
INTERFA
CE_ID
RCV_HEA
DERS_INT
ERFACE_
S.NEXTV
AL
RCV_HE
ADERS_I
NTERFAC
E_S.NEXT
VAL
RCV_HEA
DERS_INTE
RFACE_S.N
EXTVAL
RCV_HEA
DERS_INTE
RFACE_S.N
EXTVAL
RCV_HEA
DERS_INT
ERFACE_
S.NEXTV
AL
RCV_HEAD
ERS_INTER
FACE_S.NE
XTVAL
GROUP_I
D
RCV_INT
ERFACE_
GROUPS_
S.NEXTV
AL
RCV_INT
ERFACE_
GROUPS_
S.NEXTV
AL
RCV_INTE
RFACE_GR
OUPS_S.NE
XTVAL
RCV_INTE
RFACE_GR
OUPS_S.NE
XTVAL
RCV_INT
ERFACE_
GROUPS_
S.NEXTV
AL
RCV_INTE
RFACE_GR
OUPS_S.NE
XTVAL
CREATIO
N_DATE
SYSDATE SYSDATE SYSDATE SYSDATE SYSDATE SYSDATE
CREATED
_BY
FND_GLO
BAL.USER
_ID
FND_GL
OBAL.US
ER_ID
FND_GLOB
AL.USER_I
D
FND_GLOB
AL.USER_I
D
FND_GLO
BAL.USER
_ID
FND_GLOB
AL.USER_I
D
LAST_UP
DATE_DA
TE
SYSDATE SYSDATE SYSDATE SYSDATE SYSDATE SYSDATE
LAST_UP
DATED_B
Y
FND_GLO
BAL.USER
_ID
FND_GL
OBAL.US
ER_ID
FND_GLOB
AL.USER_I
D
FND_GLOB
AL.USER_I
D
FND_GLO
BAL.USER
_ID
FND_GLOB
AL.USER_I
D
LAST_UP
DATE_LO
GIN
FND_GLO
BAL.LOGI
N_ID
FND_GL
OBAL.LO
GIN_ID
FND_GLOB
AL.LOGIN_
ID
FND_GLOB
AL.LOGIN_
ID
FND_GLO
BAL.LOGI
N_ID
FND_GLOB
AL.LOGIN_
ID
RECEIPT_
SOURCE_
CODE
VENDOR VENDOR VENDOR CUSTOME
R
INTERNA
L ORDER
INVENTOR
Y
ASN_TYP
E
ASN /
ASBN
STD STD STD STD STD
16-74 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column
Name
ASN/ASB
N
Creation
PO
Receipt
ASN
Receipt
RMA
Receipt
Internal
Requisitio
n
Inter-Org
Transfer
Receipt
VALIDATI
ON_FLAG
Y Y Y Y Y Y
TRANSAC
TION_TYP
E
NEW NEW NEW NEW NEW NEW
VENDOR_
NAME
Supplier
name from
the
document
(PO_VEN
DORS.VE
NDOR_N
AME)
Supplier
name
from the
document
(PO_VEN
DORS.VE
NDOR_N
AME)
Supplier
name from
the
document
(PO_VEND
ORS.VEND
OR_NAME)
NULL NULL NULL
VENDOR_
ID
PO_HEAD
ERS_ALL.
VENDOR_
ID
PO_HEA
DERS_AL
L.VENDO
R_ID
PO_HEADE
RS_ALL.VE
NDOR_ID
NULL NULL NULL
VENDOR_
SITE_COD
E
Supplier
site name
from the
document
Supplier
site name
from the
document
Supplier
site name
from the
document
NULL NULL NULL
(PO_VEN
DOR_SITE
S_ALL.VE
NDOR_SI
TE_CODE
)
(PO_VEN
DOR_SIT
ES_ALL.V
ENDOR_S
ITE_COD
E)
(PO_VEND
OR_SITES_
ALL.VEND
OR_SITE_C
ODE)
VENDOR_
SITE_ID
PO_HEAD
ERS_ALL.
VENDOR_
SITE_ID
PO_HEA
DERS_AL
L.VENDO
R_SITE_I
D
PO_HEADE
RS_ALL.VE
NDOR_SIT
E_ID
NULL NULL NULL
Receiving Open Interface 16-75
Column
Name
ASN/ASB
N
Creation
PO
Receipt
ASN
Receipt
RMA
Receipt
Internal
Requisitio
n
Inter-Org
Transfer
Receipt
SHIP_TO_
ORGANIZ
ATION_C
ODE
Ship to
organizati
on code
from the
document
(MTL_PA
RAMETER
S.ORGAN
IZATION_
CODE)
Ship to
organizati
on code
from the
document
(MTL_PA
RAMETE
RS.ORGA
NIZATIO
N_CODE)
Ship to
organizatio
n code from
the
document
(MTL_PAR
AMETERS.
ORGANIZ
ATION_CO
DE)
Ship to
organizatio
n code from
the
document
(MTL_PAR
AMETERS.
ORGANIZ
ATION_CO
DE)
Ship to
organizati
on code
from the
document
(MTL_PA
RAMETE
RS.ORGA
NIZATIO
N_CODE)
Ship to
organization
code from
the
document
(MTL_PAR
AMETERS.
ORGANIZA
TION_COD
E)
SHIP_TO_
ORGANIZ
ATION_ID
PO_LINE_
LOCATIO
NS_ALL.S
HIP_TO_
ORGANIZ
ATION_I
D
PO_LINE
_LOCATI
ONS_ALL
.SHIP_TO
_ORGANI
ZATION_
ID
PO_LINE_L
OCATIONS
_ALL.SHIP
_TO_ORGA
NIZATION
_ID
OE_ORDER
_LINES_AL
L.SHIP_FR
OM_ORG_I
D
RCV_SHI
PMENT_L
INES.TO_
ORGANIZ
ATION_I
D
RCV_SHIP
MENT_LIN
ES.TO_ORG
ANIZATIO
N_ID
SHIPPED_
DATE
SYSDATE SYSDATE SYSDATE SYSDATE SYSDATE SYSDATE
EXPECTE
D_RECEIP
T_DATE
SYSDATE SYSDATE SYSDATE SYSDATE SYSDATE SYSDATE
CUSTOME
R_PARTY_
NAME
NULL NULL NULL RMA
CUSTOME
R NAME
NULL NULL
(HZ_PARTI
ES.PARTY_
NAME)
CUSTOME
R_SITE_ID
NULL NULL NULL RMA
CUSTOME
R SITE ID
NULL NULL
16-76 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column
Name
ASN/ASB
N
Creation
PO
Receipt
ASN
Receipt
RMA
Receipt
Internal
Requisitio
n
Inter-Org
Transfer
Receipt
(OE_ORDE
R_LINES_A
LL.SHIP_FR
OM_ORG_I
D)
RCV_TRANSACTIONS_INTERFACE (RTI)
Column
Name
ASN/ASB
N
Creation
PO
Receipt
ASN
Receipt
RMA
Receipt
Internal
Requisitio
n Receipt
Inter-Org
Transfer
Receipt
INTERFA
CE_TRAN
SACTION_
ID
RCV_TRA
NSACTIO
NS_INTE
RFACE_S.
NEXTVAL
RCV_TRA
NSACTIO
NS_INTE
RFACE_S.
NEXTVA
L
RCV_TRAN
SACTIONS
_INTERFA
CE_S.NEXT
VAL
RCV_TRAN
SACTIONS
_INTERFA
CE_S.NEXT
VAL
RCV_TRA
NSACTIO
NS_INTE
RFACE_S.
NEXTVAL
RCV_TRAN
SACTIONS_
INTERFAC
E_S.NEXTV
AL
GROUP_I
D
RCV_HEA
DERS_INT
ERFACE.
GROUP_I
D
RCV_HE
ADERS_I
NTERFAC
E.GROUP
_ID
RCV_HEA
DERS_INTE
RFACE.GR
OUP_ID
RCV_HEA
DERS_INTE
RFACE.GR
OUP_ID
RCV_HEA
DERS_IN
TERFACE.
GROUP_I
D
RCV_HEAD
ERS_INTER
FACE.GRO
UP_ID
HEADER_
INTERFA
CE_ID
RCV_HEA
DERS_INT
ERFACE.
HEADER_
INTERFA
CE_ID
RCV_HE
ADERS_I
NTERFAC
E.HEADE
R_INTER
FACE_ID
RCV_HEA
DERS_INTE
RFACE.HE
ADER_INT
ERFACE_ID
RCV_HEA
DERS_INTE
RFACE.HE
ADER_INT
ERFACE_I
D
RCV_HEA
DERS_IN
TERFACE.
HEADER_
INTERFA
CE_ID
RCV_HEAD
ERS_INTER
FACE.HEA
DER_INTER
FACE_ID
LPN_GRO
UP_ID
RCV_INT
ERFACE_
GROUPS_
S.NEXTV
AL
RCV_INT
ERFACE_
GROUPS_
S.NEXTV
AL
RCV_INTE
RFACE_GR
OUPS_S.NE
XTVAL
RCV_INTE
RFACE_GR
OUPS_S.NE
XTVAL
RCV_INT
ERFACE_
GROUPS_
S.NEXTV
AL
RCV_INTE
RFACE_GR
OUPS_S.NE
XTVAL
CREATIO
N_DATE
SYSDATE SYSDATE SYSDATE SYSDATE SYSDATE SYSDATE
Receiving Open Interface 16-77
Column
Name
ASN/ASB
N
Creation
PO
Receipt
ASN
Receipt
RMA
Receipt
Internal
Requisitio
n Receipt
Inter-Org
Transfer
Receipt
CREATED
_BY
FND_GLO
BAL.USER
_ID
FND_GL
OBAL.US
ER_ID
FND_GLOB
AL.USER_I
D
FND_GLOB
AL.USER_I
D
FND_GLO
BAL.USER
_ID
FND_GLOB
AL.USER_I
D
LAST_UP
DATE_DA
TE
SYSDATE SYSDATE SYSDATE SYSDATE SYSDATE SYSDATE
LAST_UP
DATED_B
Y
FND_GLO
BAL.USER
_ID
FND_GL
OBAL.US
ER_ID
FND_GLOB
AL.USER_I
D
FND_GLOB
AL.USER_I
D
FND_GLO
BAL.USER
_ID
FND_GLOB
AL.USER_I
D
LAST_UP
DATE_LO
GIN
FND_GLO
BAL.LOGI
N_ID
FND_GL
OBAL.LO
GIN_ID
FND_GLOB
AL.LOGIN_
ID
FND_GLOB
AL.LOGIN_
ID
FND_GLO
BAL.LOGI
N_ID
FND_GLOB
AL.LOGIN_
ID
TRANSAC
TION_DA
TE
SYSDATE SYSDATE SYSDATE SYSDATE SYSDATE SYSDATE
PROCESSI
NG_MOD
E_CODE
BATCH BATCH BATCH BATCH BATCH BATCH
PROCESSI
NG_STAT
US_CODE
PENDING PENDING PENDING PENDING PENDING PENDING
TRANSAC
TION_STA
TUS_COD
E
PENDING PENDING PENDING PENDING PENDING PENDING
DESTINAT
ION_TYPE
_CODE
NULL RECEIVI
NG
RECEIVIN
G
RECEIVIN
G
RECEIVIN
G
RECEIVING
VALIDATI
ON_FLAG
Y Y Y Y Y Y
16-78 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column
Name
ASN/ASB
N
Creation
PO
Receipt
ASN
Receipt
RMA
Receipt
Internal
Requisitio
n Receipt
Inter-Org
Transfer
Receipt
TRANSAC
TION_TYP
E
SHIP RECEIVE RECEIVE RECEIVE RECEIVE RECEIVE
AUTO_TR
ANSACT_
CODE
SHIP/REC
EIVE/DEL
IVER
SHIP/REC
EIVE/DEL
IVER
NULL NULL NULL NULL
SOURCE_
DOCUME
NT_CODE
PO PO PO RMA REQ INVENTOR
Y
DOCUME
NT_NUM
PO
number
PO
number
ASN
number
RMA
number
REQ
number
SHIPMENT
number
(PO_HEA
DERS_AL
L.SEGME
NT1)
(PO_HEA
DERS_AL
L.SEGME
NT1)
(RCV_SHIP
MENT_HE
ADERS.SHI
PMENT_N
UM)
(OE_ORDE
R_HEADER
S_ALL.ORD
ER_NUM)
(PO_REQ
UISITION
_HEADER
S_ALL.SE
GMENT1)
(RCV_SHIP
MENT_HE
ADERS.SHI
PMENT_N
UM)
DOCUME
NT_LINE_
NUM
PO line
number
PO line
number
NULL RMA line
number
REQ line
number
NULL
(PO_LINE
S_ALL.LI
NE_NUN)
(PO_LINE
S_ALL.LI
NE_NUN)
(OE_ORDE
R_LINES_A
LL.LINE_N
UM)
(PO_REQ
UISITION
_LINES_A
LL.LINE_
NUM)
DOCUME
NT_SHIP
MENT_LI
NE_NUM
NULL PO line
location
number
NULL NULL NULL NULL
(PO_LINE
_LOCATI
ONS_ALL
.SHIPME
NT_NUM
)
Receiving Open Interface 16-79
Column
Name
ASN/ASB
N
Creation
PO
Receipt
ASN
Receipt
RMA
Receipt
Internal
Requisitio
n Receipt
Inter-Org
Transfer
Receipt
ITEM_NU
M
Item from
the
document
Item from
the
document
Item from
the
document
Item from
the
document
Item from
the
document
Item from
the
document
(MTL_SYS
TEM_ITE
MS.SEGM
ENT1)
(MTL_SYS
TEM_ITE
MS.SEGM
ENT1)
(MTL_SYST
EM_ITEMS.
SEGMENT1
)
(MTL_SYST
EM_ITEMS.
SEGMENT1
)
(MTL_SYS
TEM_ITE
MS.SEGM
ENT1)
(MTL_SYST
EM_ITEMS.
SEGMENT1
)
QUANTIT
Y
Transactio
n quantity
Transactio
n quantity
Transaction
quantity
Transaction
quantity
Transactio
n quantity
Transaction
quantity
UNIT_OF_
MEASURE
Transactio
n UOM
Transactio
n UOM
Transaction
UOM
Transaction
UOM
Transactio
n UOM
Transaction
UOM
RECEIPT_
SOURCE_
CODE
VENDOR VENDOR VENDOR CUSTOME
R
INTERNA
L ORDER
INVENTOR
Y
SUBINVE
NTORY
NULL Destinatio
n
subinvent
ory
(MTL_SE
CONDAR
Y_INVEN
TORIES.
SECOND
ARY_INV
ENTORY_
NAME)
Destination
subinventor
y
(MTL_SEC
ONDARY_I
NVENTORI
ES.
SECONDA
RY_INVEN
TORY_NA
ME)
Destination
subinventor
y
(MTL_SEC
ONDARY_I
NVENTORI
ES.
SECONDA
RY_INVEN
TORY_NA
ME)
Destinatio
n
subinvent
ory
(MTL_SEC
ONDARY
_INVENT
ORIES.
SECOND
ARY_INV
ENTORY_
NAME)
Destination
subinventor
y
(MTL_SECO
NDARY_IN
VENTORIE
S.
SECONDA
RY_INVEN
TORY_NA
ME)
LOCATOR
_ID
NULL Destinatio
n locator
(MTL_ITE
M_LOCA
TIONS_K
FV.CONC
ANTENA
TED_SEG
MENTS)
Destination
locator
(MTL_ITEM
_LOCATIO
NS_KFV.C
ONCANTE
NATED_SE
GMENTS)
Destination
locator
(MTL_ITEM
_LOCATIO
NS_KFV.C
ONCANTE
NATED_SE
GMENTS)
Destinatio
n locator
(MTL_ITE
M_LOCA
TIONS_K
FV.CONC
ANTENA
TED_SEG
MENTS)
Destination
locator
(MTL_ITEM
_LOCATIO
NS_KFV.CO
NCANTEN
ATED_SEG
MENTS)
16-80 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column
Name
ASN/ASB
N
Creation
PO
Receipt
ASN
Receipt
RMA
Receipt
Internal
Requisitio
n Receipt
Inter-Org
Transfer
Receipt
LICENSE_
PLATE_N
UMBER
LPN
shipped
by
Supplier
NULL LPN
shipped by
Supplier
NULL LPN
shipped
from the
source
organizati
on
LPN
shipped
from the
source
organization
TRANSFE
R_LICENS
E_PLATE_
NUMBER
NULL LPN into
which the
material is
received
LPN into
which the
material is
received
LPN into
which the
material is
received
LPN into
which the
material is
received
LPN into
which the
material is
received
The following tables are required only if item is lot controlled, or serial controlled, or
the transaction involves LPNs.
MTL_TRANSACTION_LOTS_INTERFACE (MTLI)
Column Name
TRANSACTION_INTERFACE
_ID
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
SERIAL_TRANSACTION_TE
MP_ID
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
PRODUCT_CODE 'RCV'
PRODUCT_TRANSACTION_I
D
RCV_TRANSACTIONS_INTERFACE.INTERFACE_TRANS
ACTION_ID
CREATION_DATE SYSDATE
CREATED_BY FND_GLOBAL.USER_ID
LAST_UPDATE_DATE SYSDATE
LAST_UPDATED_BY FND_GLOBAL.USER_ID
LAST_UPDATE_LOGIN FND_GLOBAL.LOGIN_ID
Receiving Open Interface 16-81
Column Name
LOT_NUMBER Lot number being transacted
TRANSACTION_QUANTITY Transaction quantity for current lot
PRIMARY_QUANTITY Primary quantity for current lot
MTL_SERIAL_NUMBERS_INTERFACE (MSNI)
Column Name
TRANSACTION_INTERF
ACE_ID
MTL_TRANSACTION_LOTS_INTERFACE.SERIAL_TRANSAC
TION_TEMP_ID
OR
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
PRODUCT_CODE RCV
PRODUCT_TRANSACTIO
N_ID
RCV_TRANSACTIONS_INTERFACE.INTERFACE_TRANSACT
ION_ID
CREATION_DATE SYSDATE
CREATED_BY FND_GLOBAL.USER_ID
LAST_UPDATE_DATE SYSDATE
LAST_UPDATED_BY FND_GLOBAL.USER_ID
LAST_UPDATE_LOGIN
FND_GLOBAL.LOGIN_ID
FM_SERIAL_NUMBER From serial number
TO_SERIAL_NUMBER To serial number
16-82 Oracle Supply Chain Management APIs and Open Interfaces Guide
WMS_LPN_INTERFACE (WLPNI)
Column Name
SOURCE_GROUP_ID RCV_TRANSACTIONS_INTERFACE.LPN_GROUP_ID
CREATION_DATE SYSDATE
CREATED_BY FND_GLOBAL.USER_ID
LAST_UPDATE_DATE SYSDATE
LAST_UPDATED_BY FND_GLOBAL.USER_ID
LAST_UPDATE_LOGIN FND_GLOBAL.LOGIN_ID
LICENSE_PLATE_NUMBE
R
New LPN
PARENT_LICENSE_PLAT
E_NUMBER
Parent LPN
The following conditions apply to ASN and receipt transactions:
Populate the WMS_LPN_INTERFACE only if the transaction involves a new LPN
or a change in nesting structure.
Populate MTL_SERIAL_NUMBERS_INTERFACE for only serial controlled items. If
an item is only serial controlled (no lot control) then
MSNI.INTERFACE_TRANSACTION_ID should be
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL.
Populate MTL_TRANSACTION_LOTS_INTERFACE for only lot-controlled items.
If an item is only lot controlled (no serial control), then
MTLI.SERIAL_TRANSACTION_TEMP_ID should be null.
Populate both MTL_SERIAL_NUMBERS_INTERFACE and
MTL_TRANSACTION_LOTS_INTERFACE for lot and serial controlled items.
In all above transactions, AUTO_TRANSACT_CODE can have a value of NULL,
RECEIVE, or DELIVER. If TRANSACTION_TYPE is SHIP, and
AUTO_TRANSACT_CODE is RECEIVE, the system performs the receipt as part of
the ASN/ASBN creation transaction. Similarly, if TRANSACTION_TYPE is SHIP or
RECEIVE, and the AUTO_TRANSACT_CODE is DELIVER, then the system
Receiving Open Interface 16-83
performs the delivery to INVENTORY or EXPENSE destination as part of the
transaction.
The system uses RTI.DESTINATION_TYPE_CODE to determine the destination
type. This column should have a value of RECEIVING for standard receipts,
INVENTORY for a direct receipt into inventory, and EXPENSE for a direct receipt
into an expense destination.
For all the transactions other than ASN/ASBN, if you want a set of transactions to
fail when any one transaction in that set fails, then populate the same
LPN_GROUP_ID for all the rows in that set. LPN_GROUP_ID should not have
same value across HEADER_INTERFACE_IDs. For ASN/ASBN transactions, the
profile option RCV: Fail All ASN Lines if One Line Fails controls this situation.
If you transaction only one serial number then FM_SERIAL_NUMBER and
TO_SERIAL_NUMBER in table MTL_SERIAL_NUMBERS_INTERFACE must have
the same value.
Receiving Open Interface Data Details for Post Receipt Transactions
This section covers information about the following transactions:
Transfer
Accept/Reject
Deliver
Correct
Return to Vendor
Return to Receiving
Return to Customer
You can source these transactions from any source document. These transactions do not
require a record in RCV_HEADERS_INTERFACE_TABLE. Do not insert records in
RCV_HEADERS_INTERFACE for these transactions.
16-84 Oracle Supply Chain Management APIs and Open Interfaces Guide
RCV_TRANSACTIONS_INTERFACE (RTI)
Column Name
Post RECEIPT Transactions (TRANSFER
ACCEPT/REJECT DELIVER CORRECT RETURN TO
VENDOR RETURN TO CUSTOMER RETURN TO
RECEIVING)
INTERFACE_TRANSACTION_ID RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
GROUP_ID RCV_INTERFACE_GROUPS_S.NEXTVAL
HEADER_INTERFACE_ID NULL
LPN_GROUP_ID RCV_INTERFACE_GROUPS_S.NEXTVAL
CREATION_DATE SYSDATE
CREATED_BY FND_GLOBAL.USER_ID
LAST_UPDATE_DATE SYSDATE
LAST_UPDATED_BY FND_GLOBAL.USER_ID
LAST_UPDATE_LOGIN FND_GLOBAL.LOGIN_ID
TRANSACTION_DATE SYSDATE
PROCESSING_MODE_CODE BATCH
PROCESSING_STATUS_CODE PENDING
TRANSACTION_STATUS_CODE PENDING
DESTINATION_TYPE_CODE RECEIVING OR INVENTORY OR EXPENSE
TRANSACTION_TYPE TRANSFER / ACCEPT / REJECT / DELIVER /
CORRECT / RETURN TO VENDOR / RETURN TO
CUSTOMER / RETURN TO RECEIVING
VALIDATION_FLAG Y
Receiving Open Interface 16-85
Column Name
Post RECEIPT Transactions (TRANSFER
ACCEPT/REJECT DELIVER CORRECT RETURN TO
VENDOR RETURN TO CUSTOMER RETURN TO
RECEIVING)
QUANTITY Transaction quantity. For negative corrections give a
negative value.
UNIT_OF_MEASURE Transaction UOM.
RECEIPT_SOURCE_CODE VENDOR / CUSTOMER / INTERNAL ORDER /
INVENTORY
SOURCE_DOCUMENT_CODE PO / RMA / REQ / INVENTORY
SUBINVENTORY Destination subinventory
(MTL_SECONDARY_INVENTORIES.SECONDARY_I
NVENTORY_NAME)
LOCATOR_ID Destination locator
(MTL_ITEM_LOCATIONS_KFV.CONCANTENATED
_SEGMENTS)
LICENSE_PLATE_NUMBER Source LPN (Destination LPN for correction)
TRANSFER_LICENSE_PLATE_NUM
BER
Destination LPN (Source LPN for correction)
PARENT_TRANSACTION_ID Transaction ID for the parent transaction
(RCV_TRANSACTIONS.TRANSACTION_ID)
The following tables are required only if item the is lot controlled, if the item is serial
controlled, or if the transaction involves LPNs.
MTL_TRANSACTION_LOTS_INTERFACE (MTLI)
Column Name Record1
TRANSACTION_INTERFACE_ID MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
SERIAL_TRANSACTION_TEMP_ID MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
16-86 Oracle Supply Chain Management APIs and Open Interfaces Guide
Column Name Record1
PRODUCT_CODE RCV
PRODUCT_TRANSACTION_ID RCV_TRANSACTIONS_INTERFACE.INTERFACE_T
RANSACTION_ID
CREATION_DATE SYSDATE
CREATED_BY FND_GLOBAL.USER_ID
LAST_UPDATE_DATE SYSDATE
LAST_UPDATED_BY FND_GLOBAL.USER_ID
LAST_UPDATE_LOGIN FND_GLOBAL.LOGIN_ID
LOT_NUMBER Lot number being transacted
TRANSACTION_QUANTITY Transaction quantity for current lot
PRIMARY_QUANTITY Primary quantity for current lot
MTL_SERIAL_NUMBERS_INTERFACE (MSNI)
Column Name Record1
TRANSACTION_INTERFACE_ID MTL_TRANSACTION_LOTS_INTERFACE.SERIAL_T
RANSACTION_TEMP_ID
OR
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
PRODUCT_CODE RCV
PRODUCT_TRANSACTION_ID RCV_TRANSACTIONS_INTERFACE.INTERFACE_T
RANSACTION_ID
CREATION_DATE SYSDATE
Receiving Open Interface 16-87
Column Name Record1
CREATED_BY FND_GLOBAL.USER_ID
LAST_UPDATE_DATE SYSDATE
LAST_UPDATED_BY FND_GLOBAL.USER_ID
LAST_UPDATE_LOGIN FND_GLOBAL.LOGIN_ID
FM_SERIAL_NUMBER From serial number
TO_SERIAL_NUMBER To Serial Number
WMS_LPN_INTERFACE (WLPNI)
Column Name Record1
SOURCE_GROUP_ID RCV_TRANSACTIONS_INTERFACE.LPN_GROUP_I
D
CREATION_DATE SYSDATE
CREATED_BY FND_GLOBAL.USER_ID
LAST_UPDATE_DATE SYSDATE
LAST_UPDATED_BY FND_GLOBAL.USER_ID
LAST_UPDATE_LOGIN FND_GLOBAL.LOGIN_ID
LICENSE_PLATE_NUMBER New LPN
PARENT_LICENSE_PLATE_NUMBE
R
Parent LPN
The following conditions apply to post-receipt transactions:
Populate WMS_LPN_INTERFACE only if the transaction involves a new LPN or a
change in the nesting structure.
16-88 Oracle Supply Chain Management APIs and Open Interfaces Guide
Populate MTL_SERIAL_NUMBERS_INTERFACE only for serial controlled items. If
an item is only serial controlled (No lot control) then
MSNI.INTERFACE_TRANSACTION_ID should be
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL.
Populate MTL_TRANSACTION_LOTS_INTERFACE only for lot-controlled items.
If an item is only lot controlled (No serial control) then
MTLI.SERIAL_TRANSACTION_TEMP_ID should be null.
Populate both MTL_SERIAL_NUMBERS_INTERFACE and
MTL_TRANSACTION_LOTS_INTERFACE for lot and serial controlled items.
For all the transactions other than ASN/ASBN transactions, if you want a set of
transactions to fail when any one transaction fails, then populate the same
LPN_GROUP_ID for all the rows in that set. The LPN_GROUP_ID should not have
the same value across multiple GROUP_IDs.
If only one Serial is being transacted, FM_SERIAL_NUMBER and
TO_SERIAL_NUMBER in table MTL_SERIAL_NUMBERS_INTERFACE must have
the same value.
Sample Scripts
This section provides information documents that you can receive using the Receiving
Open Interface.
Purchase Order Sample Script
This script provides a sample SELECT statement to retrieve document information for a
purchase order.
To get header information:
SELECT * FROM PO_HEADERS_ALL POH WHERE SEGMENT1 = 'DOCUMENT_NUMBER';
To get line information:
SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID = PO_HEADER_ID FROM
THE FIRST SQL;
To get distribution (delivery) information:
SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE PO_HEADER_ID = PO_HEADER_ID
FROM THE FIRST SQL;
The SQL to get PO shipments that are eligible for Receiving is as follows:
Note: The SQL uses X_INCLUDE_CLOSED_PO, which is the value of
Receiving Open Interface 16-89
the profile option RCV: Default Include Closed PO Option. It also uses
X_PO_NUM which is the PO number
(PO_HEADERS_ALL.SEGMENT1).
16-90 Oracle Supply Chain Management APIs and Open Interfaces Guide
SELECT PLL.LINE_LOCATION_ID,
PLL.UNIT_MEAS_LOOKUP_CODE,
PLL.UNIT_OF_MEASURE_CLASS,
NVL(PLL.PROMISED_DATE, PLL.NEED_BY_DATE) PROMISED_DATE,
PLL.SHIP_TO_ORGANIZATION_ID,
PLL.QUANTITY QUANTITY_ORDERED,
PLL.QUANTITY_SHIPPED,
PLL.RECEIPT_DAYS_EXCEPTION_CODE,
PLL.QTY_RCV_TOLERANCE,
PLL.QTY_RCV_EXCEPTION_CODE,
PLL.DAYS_EARLY_RECEIPT_ALLOWED,
PLL.DAYS_LATE_RECEIPT_ALLOWED,
NVL(PLL.PRICE_OVERRIDE, PL.UNIT_PRICE) UNIT_PRICE,
PLL.MATCH_OPTION,
PL.CATEGORY_ID,
NVL(PLL.DESCRIPTION, PL.ITEM_DESCRIPTION) ITEM_DESCRIPTION,
PL.PO_LINE_ID,
PH.CURRENCY_CODE,
PH.RATE_TYPE,
POD.PO_DISTRIBUTION_ID,
POD.CODE_COMBINATION_ID,
POD.REQ_DISTRIBUTION_ID,
POD.DELIVER_TO_LOCATION_ID,
POD.DELIVER_TO_PERSON_ID,
POD.RATE_DATE,
POD.RATE,
POD.DESTINATION_TYPE_CODE,
POD.DESTINATION_ORGANIZATION_ID,
POD.DESTINATION_SUBINVENTORY,
POD.WIP_ENTITY_ID,
POD.WIP_OPERATION_SEQ_NUM,
POD.WIP_RESOURCE_SEQ_NUM,
POD.WIP_REPETITIVE_SCHEDULE_ID,
POD.WIP_LINE_ID,
POD.BOM_RESOURCE_ID,
POD.USSGL_TRANSACTION_CODE,
PLL.SHIP_TO_LOCATION_ID,
NVL(PLL.ENFORCE_SHIP_TO_LOCATION_CODE, 'NONE')
ENFORCE_SHIP_TO_LOCATION_CODE,
TO_NUMBER(NULL) SHIPMENT_LINE_ID,
PL.ITEM_ID
FROM PO_DISTRIBUTIONS_ALL POD,
PO_LINE_LOCATIONS_ALL PLL,
PO_LINES_ALL PL,
PO_HEADERS_ALL PH
WHERE PH.SEGMENT1 = X_PO_NUM
AND PL.PO_HEADER_ID = PH.PO_HEADER_ID
AND PLL.PO_LINE_ID = PL.PO_LINE_ID
AND POD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND NVL(PLL.APPROVED_FLAG, 'N') = 'Y'
AND NVL(PLL.CANCEL_FLAG, 'N') = 'N'
AND((NVL(X_INCLUDE_CLOSED_PO, 'N') = 'Y'
AND NVL(PLL.CLOSED_CODE, 'OPEN') <> 'FINALLY CLOSED')
OR(NVL(X_INCLUDE_CLOSED_PO, 'N') = 'N'
AND(NVL(PLL.CLOSED_CODE, 'OPEN') NOT IN('FINALLY CLOSED', 'CLOSED',
'CLOSED FOR RECEIVING'))))
AND PLL.SHIPMENT_TYPE IN('STANDARD', 'BLANKET', 'SCHEDULED',
'PREPAYMENT')
ORDER BY NVL(PLL.PROMISED_DATE, PLL.NEED_BY_DATE);
Receiving Open Interface 16-91
Sample Script to get ASN Information
This section provides a sample select statement to retrieve document information for an
ASN.
The SQL to get ASN shipments is as follows:
Note: The SQL uses X_ASN_NUMBER. This is the ASN shipment
number.
16-92 Oracle Supply Chain Management APIs and Open Interfaces Guide
SELECT RSL.PO_LINE_LOCATION_ID,
PLL.UNIT_MEAS_LOOKUP_CODE,
PLL.UNIT_OF_MEASURE_CLASS,
NVL(PLL.PROMISED_DATE, PLL.NEED_BY_DATE)
PROMISED_DATE,
RSL.TO_ORGANIZATION_ID SHIP_TO_ORGANIZATION_ID,
PLL.QUANTITY QUANTITY_ORDERED,
RSL.QUANTITY_SHIPPED,
PLL.RECEIPT_DAYS_EXCEPTION_CODE,
PLL.QTY_RCV_TOLERANCE,
PLL.QTY_RCV_EXCEPTION_CODE,
PLL.DAYS_EARLY_RECEIPT_ALLOWED,
PLL.DAYS_LATE_RECEIPT_ALLOWED,
NVL(PLL.PRICE_OVERRIDE, PL.UNIT_PRICE) UNIT_PRICE,
PLL.MATCH_OPTION,
RSL.CATEGORY_ID,
RSL.ITEM_DESCRIPTION,
PL.PO_LINE_ID,
PH.CURRENCY_CODE,
PH.RATE_TYPE,
POD.PO_DISTRIBUTION_ID,
POD.CODE_COMBINATION_ID,
POD.REQ_DISTRIBUTION_ID,
POD.DELIVER_TO_LOCATION_ID,
POD.DELIVER_TO_PERSON_ID,
POD.RATE_DATE,
POD.RATE,
POD.DESTINATION_TYPE_CODE,
POD.DESTINATION_ORGANIZATION_ID,
POD.DESTINATION_SUBINVENTORY,
POD.WIP_ENTITY_ID,
POD.WIP_OPERATION_SEQ_NUM,
POD.WIP_RESOURCE_SEQ_NUM,
POD.WIP_REPETITIVE_SCHEDULE_ID,
POD.WIP_LINE_ID,
POD.BOM_RESOURCE_ID,
POD.USSGL_TRANSACTION_CODE,
PLL.SHIP_TO_LOCATION_ID,
NVL(PLL.ENFORCE_SHIP_TO_LOCATION_CODE, 'NONE')
ENFORCE_SHIP_TO_LOCATION_CODE,
RSL.SHIPMENT_LINE_ID,
RSL.ITEM_ID
FROM PO_DISTRIBUTIONS POD,
PO_LINE_LOCATIONS PLL,
PO_LINES PL,
PO_HEADERS PH,
RCV_SHIPMENT_LINES RSL,
RCV_SHIPMENT_HEADERS RSH
WHERE RSH.SHIPMENT_NUM = X_ASN_NUMBER
AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = NVL(V_SHIPMENT_LINE_ID,
RSL.SHIPMENT_LINE_ID)
AND RSL.PO_HEADER_ID = PH.PO_HEADER_ID
AND PH.PO_HEADER_ID = NVL(V_PO_HEADER_ID,
PH.PO_HEADER_ID)
AND((V_PO_HEADER_ID IS NOT NULL
AND PL.LINE_NUM = NVL(V_PO_LINE_NUM, PL.LINE_NUM)
AND PLL.SHIPMENT_NUM = NVL(V_PO_SHIPMENT_NUM,
PLL.SHIPMENT_NUM)
Receiving Open Interface 16-93
AND POD.DISTRIBUTION_NUM = NVL(V_PO_DISTRIBUTION_NUM,
POD.DISTRIBUTION_NUM)) OR V_PO_HEADER_ID IS NULL)
AND POD.PO_HEADER_ID = PH.PO_HEADER_ID
AND POD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND RSL.PO_LINE_ID = PL.PO_LINE_ID
AND RSL.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND NVL(RSL.PO_RELEASE_ID, 0) = NVL(V_PO_RELEASE_ID,
NVL(RSL.PO_RELEASE_ID, 0))
AND NVL(RSL.ITEM_ID, 0) = NVL(V_ITEM_ID,
NVL(RSL.ITEM_ID, 0))
AND RSL.TO_ORGANIZATION_ID = NVL(V_SHIP_TO_ORG_ID,
RSL.TO_ORGANIZATION_ID)
AND(NVL(RSL.SHIPMENT_LINE_STATUS_CODE, 'EXPECTED') <>
'FULLY RECEIVED')
AND RSH.RECEIPT_SOURCE_CODE = 'VENDOR'
AND NVL(RSL.ASN_LINE_FLAG, 'N') = 'Y'
AND RSL.SHIP_TO_LOCATION_ID = NVL(V_SHIP_TO_LOCATION_ID,
RSL.SHIP_TO_LOCATION_ID)
AND PLL.PO_LINE_ID = PL.PO_LINE_ID
AND NVL(PLL.PO_RELEASE_ID, 0) = NVL(RSL.PO_RELEASE_ID,
NVL(PLL.PO_RELEASE_ID, 0))
AND NVL(PLL.APPROVED_FLAG, 'N') = 'Y'
AND NVL(PLL.CANCEL_FLAG, 'N') = 'N'
AND(((NVL(V_INCLUDE_CLOSED_PO, 'N') = 'Y')
AND(NVL(PLL.CLOSED_CODE, 'OPEN') <> 'FINALLY CLOSED'))
OR((NVL(V_INCLUDE_CLOSED_PO, 'N') = 'N')
AND(NVL(PLL.CLOSED_CODE, 'OPEN') NOT IN('FINALLY
CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING'))))
AND PLL.SHIPMENT_TYPE IN('STANDARD', 'BLANKET',
'SCHEDULED')
AND PLL.SHIP_TO_ORGANIZATION_ID =
NVL(RSL.TO_ORGANIZATION_ID, PLL.SHIP_TO_ORGANIZATION_ID)
AND PLL.SHIP_TO_LOCATION_ID =
NVL(RSL.SHIP_TO_LOCATION_ID, PLL.SHIP_TO_LOCATION_ID)
AND NVL(PL.VENDOR_PRODUCT_NUM, '-999') =
NVL(V_VENDOR_PRODUCT_NUM, NVL(PL.VENDOR_PRODUCT_NUM,
'-999'));
Sample Script to Get Internal Requisition Information
This section provides a sample select statement to retrieve document information for an
internal requisition.
SELECT REQUISITION_LINE_ID FROM PO_REQUISITION_LINES_ALL WHERE
REQUISITION_HEADER_ID = (SELECT REQUISITION_HEADER_ID FROM
PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1 = 'REQ NUMBER';
X_REQ_LINE_ID = THE REQUISITION_LINE_ID OF THE REQUISITION LINE
16-94 Oracle Supply Chain Management APIs and Open Interfaces Guide
SELECT RSH.SHIPMENT_HEADER_ID SHIPMENT_HEADER_ID,
RSH.SHIPMENT_NUM SHIPMENT_NUM,
RSL.SHIPMENT_LINE_ID SHIPMENT_LINE_ID,
RSL.ITEM_ID ITEM_ID,
RSL.ITEM_DESCRIPTION ITEM_DESCRIPTION,
RSL.TO_ORGANIZATION_ID TO_ORGANIZATION_ID,
RSL.FROM_ORGANIZATION_ID FROM_ORGANIZATION_ID,
RSL.ROUTING_HEADER_ID ROUTING_HEADER_ID,
RSL.CATEGORY_ID CATEGORY_ID,
RSH.CURRENCY_CODE CURRENCY_CODE,
RSH.CONVERSION_RATE CURRENCY_CONVERSION_RATE,
RSH.CONVERSION_RATE_TYPE CURRENCY_CONVERSION_TYPE,
RSH.CONVERSION_DATE CURRENCY_CONVERSION_DATE,
RSL.TO_SUBINVENTORY TO_SUBINVENTORY,
RSL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID,
RSL.DELIVER_TO_LOCATION_ID DELIVER_TO_LOCATION_ID,
RSL.DELIVER_TO_PERSON_ID DELIVER_TO_PERSON_ID,
RSL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
RSL.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE,
RSL.DESTINATION_CONTEXT DESTINATION_CONTEXT,
RSL.UNIT_OF_MEASURE UNIT_OF_MEASURE,
RSL.PRIMARY_UNIT_OF_MEASURE PRIMARY_UNIT_OF_MEASURE,
RSL.REQUISITION_LINE_ID REQUISITION_LINE_ID,
RSL.PO_LINE_LOCATION_ID PO_LINE_LOCATION_ID,
RSL.EMPLOYEE_ID EMPLOYEE_ID
FROM RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
PO_REQUISITION_LINES_ALL PORL
WHERE PORL.REQUISITION_LINE_ID = X_REQ_LINE_ID
AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND(NVL(RSL.SHIPMENT_LINE_STATUS_CODE, 'EXPECTED') <> 'FULLY
RECEIVED')
AND RSH.RECEIPT_SOURCE_CODE = 'INTERNAL ORDER';
Sample Script to get Inter-Organization Transfer Shipment Information
This section provides a sample select statement to retrieve information about intransit
shipments that are eligible for receiving.
Note: X_SHIPMENT_NUM = Interorganization shipment Number.
Receiving Open Interface 16-95
SELECT RSH.SHIPMENT_HEADER_ID SHIPMENT_HEADER_ID,
RSH.SHIPMENT_NUM SHIPMENT_NUM,
RSL.SHIPMENT_LINE_ID SHIPMENT_LINE_ID,
RSL.ITEM_ID ITEM_ID,
RSL.ITEM_DESCRIPTION ITEM_DESCRIPTION,
RSL.TO_ORGANIZATION_ID TO_ORGANIZATION_ID,
RSL.FROM_ORGANIZATION_ID FROM_ORGANIZATION_ID,
RSL.ROUTING_HEADER_ID ROUTING_HEADER_ID,
RSL.CATEGORY_ID CATEGORY_ID,
RSH.CURRENCY_CODE CURRENCY_CODE,
RSH.CONVERSION_RATE CURRENCY_CONVERSION_RATE,
RSH.CONVERSION_RATE_TYPE CURRENCY_CONVERSION_TYPE,
RSH.CONVERSION_DATE CURRENCY_CONVERSION_DATE,
RSL.TO_SUBINVENTORY TO_SUBINVENTORY,
RSL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID,
RSL.DELIVER_TO_LOCATION_ID DELIVER_TO_LOCATION_ID,
RSL.DELIVER_TO_PERSON_ID DELIVER_TO_PERSON_ID,
RSL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
RSL.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE,
RSL.DESTINATION_CONTEXT DESTINATION_CONTEXT,
RSL.UNIT_OF_MEASURE UNIT_OF_MEASURE,
RSL.PRIMARY_UNIT_OF_MEASURE PRIMARY_UNIT_OF_MEASURE
FROM RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL
WHERE RSH.SHIPMENT_NUM = X_SHIPMENT_NUM
AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND(NVL(RSL.SHIPMENT_LINE_STATUS_CODE, 'EXPECTED') <>
'FULLY RECEIVED')
AND RSH.RECEIPT_SOURCE_CODE = 'INVENTORY';
Sample Script to Get Return Material Authorization (RMA) Information
This section provides a sample select statement to retrieve information about RMA lines
that are eligible for receiving.
Note: X_RMA_NUMBER = RMA Number
16-96 Oracle Supply Chain Management APIs and Open Interfaces Guide
SELECT NVL(OEL.SHIP_TO_ORG_ID, OEH.SHIP_TO_ORG_ID)
CUSTOMER_SITE_ID,
NVL(OEL.SHIP_FROM_ORG_ID, OEH.SHIP_FROM_ORG_ID)
TO_ORGANIZATION_ID,
NVL(OEL.SOLD_TO_ORG_ID, OEH.SOLD_TO_ORG_ID)
CUSTOMER_ID,
NVL(OEL.PROMISE_DATE, OEL.REQUEST_DATE)
EXPECTED_RECEIPT_DATE,
OEL.ORDERED_QUANTITY ORDERED_QTY,
'N' ENFORCE_SHIP_TO_LOCATION_CODE,
OEL.DELIVER_TO_CONTACT_ID DELIVER_TO_PERSON_ID,
OEL.DELIVER_TO_ORG_ID DELIVER_TO_LOCATION_ID,
OEL.HEADER_ID OE_ORDER_HEADER_ID,
OEL.LINE_ID OE_ORDER_LINE_ID,
OEH.ORDER_NUMBER OE_ORDER_NUM,
OEL.LINE_NUMBER OE_ORDER_LINE_NUM,
OEL.INVENTORY_ITEM_ID ITEM_ID,
MUM.UNIT_OF_MEASURE,
MSI.DESCRIPTION DESCRIPTION
FROM OE_ORDER_HEADERS_ALL OEH,
OE_ORDER_LINES_ALL OEL,
OE_TRANSACTION_TYPES_ALL OLT,
OE_TRANSACTION_TYPES_TL T,
MTL_UNITS_OF_MEASURE_TL MUM,
MTL_SYSTEM_ITEMS MSI
WHERE OEH.ORDER_NUMBER = X_RMA_NUMBER
AND OEH.HEADER_ID = OEL.HEADER_ID
AND OEH.OPEN_FLAG = 'Y'
AND OEL.LINE_CATEGORY_CODE = 'RETURN'
AND OEL.OPEN_FLAG = 'Y'
AND OEL.LINE_TYPE_ID = OLT.TRANSACTION_TYPE_ID
AND OLT.TRANSACTION_TYPE_CODE = 'LINE'
AND OLT.TRANSACTION_TYPE_ID = T.TRANSACTION_TYPE_ID
AND T.LANGUAGE = USERENV('LANG')
AND MSI.ORGANIZATION_ID =
OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID',
OEL.ORG_ID)
AND MSI.INVENTORY_ITEM_ID = OEL.INVENTORY_ITEM_ID
AND OEL.BOOKED_FLAG = 'Y'
AND OEL.ORDERED_QUANTITY > NVL(OEL.SHIPPED_QUANTITY, 0)
AND OEL.FLOW_STATUS_CODE = 'AWAITING_RETURN'
AND OEL.ORDER_QUANTITY_UOM = MUM.UOM_CODE
AND MUM.LANGUAGE = USERENV('LANG')
ORDER BY EXPECTED_RECEIPT_DATE;
Sample Script to Populate the Receiving Open Interface for a Receiving Transaction for
a Lot and Serial Controlled Item with an LPN.
The following sample script is for a receipt of a PO (document number 5246). The item
CM13139 is a lot and serial controlled item, and the quantity to be received is 20. The
ship to organization code is V1, and the ship-to location code is V1-New York City. The
supplier is Consolidated Supplies, and the supplier site is Dallas-ERS. The material is
received into LPN1. Serials SER1-SER15 belong to lot number LOT1, and serials SER16
and SER20 belong to lot number LOT2.
Receiving Open Interface 16-97
DECLARE
L_RTI_ID NUMBER;
L_LPN_ID NUMBER;
BEGIN
/* INSERT INTO RCV_HEADERS_INTERFACE */
INSERT INTO RCV_HEADERS_INTERFACE
(HEADER_INTERFACE_ID,
GROUP_ID,
PROCESSING_STATUS_CODE,
RECEIPT_SOURCE_CODE,
ASN_TYPE,
TRANSACTION_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
SHIPPED_DATE,
SHIPMENT_NUM,
VENDOR_NAME,
VENDOR_SITE_CODE,
VALIDATION_FLAG,
SHIP_TO_ORGANIZATION_CODE,
EXPECTED_RECEIPT_DATE
)
SELECT
PO.RCV_HEADERS_INTERFACE_S.NEXTVAL,
PO.RCV_INTERFACE_GROUPS_S.NEXTVAL,
'PENDING',
'VENDOR',
'STD',
'NEW',
SYSDATE,
1,
1,
SYSDATE,
1,
SYSDATE,
'CONSOLIDATED SUPPLIES',
'DALLAS-ERS',
'Y',
'V1',
SYSDATE+5
FROM DUAL;
/* INSERT INTO RCV_TRANSACTIONS_INTERFACE */
INSERT INTO RCV_TRANSACTIONS_INTERFACE
(INTERFACE_TRANSACTION_ID,
HEADER_INTERFACE_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
16-98 Oracle Supply Chain Management APIs and Open Interfaces Guide
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
UNIT_OF_MEASURE,
AUTO_TRANSACT_CODE,
RECEIPT_SOURCE_CODE,
SOURCE_DOCUMENT_CODE,
DOCUMENT_NUM,
SHIP_TO_LOCATION_CODE,
VENDOR_NAME,
VALIDATION_FLAG,
TO_ORGANIZATION_CODE,
ITEM_NUM,
LPN_GROUP_ID,
TRANSFER_LICENSE_PLATE_NUMBER
)
SELECT
PO.RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
PO.RCV_HEADERS_INTERFACE_S.CURRVAL,
PO.RCV_INTERFACE_GROUPS_S.CURRVAL,
SYSDATE,
1,
SYSDATE,
1,
1,
'RECEIVE',
SYSDATE,
'PENDING',
'BATCH',
'PENDING',
20,
'EACH',
'RECEIVE',
'VENDOR',
'PO',
'5246',
'V1- NEW YORK CITY',
'CONSOLIDATED SUPPLIES',
'Y',
'V1',
'CM13139',
PO.RCV_INTERFACE_GROUPS_S.NEXTVAL,
'LPN1'
FROM DUAL;
/* INSERT FIRST LOT INTO MTL_TRANSACTION_LOTS_INTERFACE */
INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
(TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LOT_NUMBER,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID,
Receiving Open Interface 16-99
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID
)
SELECT
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
SYSDATE,
1,
SYSDATE,
1,
1,
'LOT1', --First Lot Number
15,
15,
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
'RCV',
PO.RCV_TRANSACTIONS_INTERFACE_S.CURRVAL –-This should join to
RCV_TRANSACTIONS_INTERFACE.INTERFACE_TRANSACTION_ID
FROM DUAL;
/* INSERT SERIALS CORRESPONDING TO FIRST LOT INTO
MTL_SERIAL_NUMBERS_INTERFACE */
INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE
(TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID
)
SELECT
MTL_MATERIAL_TRANSACTIONS_S.CURRVAL,
SYSDATE,
1,
SYSDATE,
1,
1,
'SER1', --From Serial Number
'SER15', --To Serial Number
'RCV',
PO.RCV_TRANSACTIONS_INTERFACE_S.CURRVAL –-This should join to
RCV_TRANSACTIONS_INTERFACE.INTERFACE_TRANSACTION_ID
FROM DUAL;
/* INSERT SECOND LOT INTO MTL_TRANSACTION_LOTS_INTERFACE */
INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
(TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LOT_NUMBER,
TRANSACTION_QUANTITY,
16-100 Oracle Supply Chain Management APIs and Open Interfaces Guide
PRIMARY_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID
)
SELECT
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
SYSDATE,
1,
SYSDATE,
1,
1,
'LOT2', --Second Lot Number
5,
5,
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
'RCV',
PO.RCV_TRANSACTIONS_INTERFACE_S.CURRVAL –-This should join to
RCV_TRANSACTIONS_INTERFACE.INTERFACE_TRANSACTION_ID
FROM DUAL;
/* INSERT SERIALS CORRESPONDING TO SECOND LOT INTO
MTL_SERIAL_NUMBERS_INTERFACE */
INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE
(TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID
)
SELECT
MTL_MATERIAL_TRANSACTIONS_S.CURRVAL,
SYSDATE,
1,
SYSDATE,
1,
1,
'SER16', --From Serial Number
'SER20', --To Serial Number
'RCV',
PO.RCV_TRANSACTIONS_INTERFACE_S.CURRVAL –-This should join to
RCV_TRANSACTIONS_INTERFACE.INTERFACE_TRANSACTION_ID
FROM DUAL;
/* INSERT INTO WMS_LPN_INTERFACE FOR A NEW LICENSE PLATE NUMBER. */
INSERT INTO WMS_LPN_INTERFACE
(SOURCE_GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
LICENSE_PLATE_NUMBER)
Receiving Open Interface 16-101
VALUES
(PO.RCV_INTERFACE_GROUPS_S.CURRVAL, --This should join to
RCV_TRANSACTIONS_INTERFACE.LPN_GROUP_ID
SYSDATE,
1,
1,
SYSDATE,
1,
'LPN1'
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR');
END;
/
Sample Script to Populate the Receiving Open Interface for a Correct transaction for a
Lot and Serial Controlled item with LPN.
The following script is for a positive correction for the Receive transaction that you
performed in the previous section. Quantity 2 is added to the lot number LOT2.
Note: For these transactions, you do not need to populate the
RCV_HEADERS_INTERFACE table.
16-102 Oracle Supply Chain Management APIs and Open Interfaces Guide
DECLARE
L_PARENT_ID NUMBER;
L_LPN_ID NUMBER;
BEGIN
/* INSERT INTO RCV_TRANSACTIONS_INTERFACE. */
/* SELECT TRANSACTION_ID FROM RCV_TRANSACTIONS FOR THE RECEIVE
TRANSACTION INTO L_PARENT_ID AND POPULATE PARENT_TRANSACTION_ID IN THE
SQL BELOW. ALSO SELECT TRANSFER_LPN_ID OF THE RECEIPT TRANSACTION INTO
L_LPN_ID */
INSERT INTO RCV_TRANSACTIONS_INTERFACE
(INTERFACE_TRANSACTION_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
RECEIPT_SOURCE_CODE,
SOURCE_DOCUMENT_CODE,
DOCUMENT_NUM,
VALIDATION_FLAG,
TO_ORGANIZATION_ID,
PARENT_TRANSACTION_ID,
TRANSFER_LPN_ID
)
SELECT
PO.RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
PO.RCV_INTERFACE_GROUPS_S.NEXTVAL,
SYSDATE,
1,
SYSDATE,
1,
1,
'CORRECT',
SYSDATE,
'PENDING',
'BATCH',
'PENDING',
2,
'VENDOR',
'PO',
'5246',
'Y',
204,
L_PARENT_ID,
L_LPN_ID
FROM DUAL;
/* INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE */
INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
(TRANSACTION_INTERFACE_ID,
Receiving Open Interface 16-103
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LOT_NUMBER,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID
)
SELECT
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
SYSDATE,
1,
SYSDATE,
1,
1,
'LOT2',
2,
2,
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
'RCV',
PO.RCV_TRANSACTIONS_INTERFACE_S.CURRVAL –-This should join to
RCV_TRANSACTIONS_INTERFACE.INTERFACE_TRANSACTION_ID
FROM DUAL;
/* INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE */
INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE
(TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID
)
SELECT
MTL_MATERIAL_TRANSACTIONS_S.CURRVAL,
SYSDATE,
1,
SYSDATE,
1,
1,
'SER21', --From Serial Number
'SER22', --To Serial Number
'RCV',
PO.RCV_TRANSACTIONS_INTERFACE_S.CURRVAL –-This should join to
RCV_TRANSACTIONS_INTERFACE.INTERFACE_TRANSACTION_ID
FROM DUAL;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
16-104 Oracle Supply Chain Management APIs and Open Interfaces Guide
DBMS_OUTPUT.PUT_LINE('ERROR');
END;
/
The following script is for a negative correction for the Receive transaction that is done
in the section 5.1.6. Quantity 2 is removed from the lot number LOT1.
Receiving Open Interface 16-105
DECLARE
L_PARENT_ID NUMBER;
L_LPN_ID NUMBER;
BEGIN
/* INSERT INTO RCV_TRANSACTIONS_INTERFACE. */
/* SELECT TRANSACTION_ID FROM RCV_TRANSACTIONS FOR THE RECEIVE
TRANSACTION INTO L_PARENT_ID AND POPULATE PARENT_TRANSACTION_ID IN THE
SQL BELOW. ALSO SELECT TRANSFER_LPN_ID OF THE RECEIPT TRANSACTION INTO
L_LPN_ID */
INSERT INTO RCV_TRANSACTIONS_INTERFACE
(INTERFACE_TRANSACTION_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
RECEIPT_SOURCE_CODE,
SOURCE_DOCUMENT_CODE,
DOCUMENT_NUM,
VALIDATION_FLAG,
TO_ORGANIZATION_ID,
PARENT_TRANSACTION_ID,
TRANSFER_LPN_ID
)
SELECT
PO.RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
PO.RCV_INTERFACE_GROUPS_S.NEXTVAL,
SYSDATE,
1,
SYSDATE,
1,
1,
'CORRECT',
SYSDATE,
'PENDING',
'BATCH',
'PENDING',
2,
'VENDOR',
'PO',
'5246',
'Y',
204,
L_PARENT_ID,
L_LPN_ID
FROM DUAL;
/* INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE */
INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
(TRANSACTION_INTERFACE_ID,
16-106 Oracle Supply Chain Management APIs and Open Interfaces Guide
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LOT_NUMBER,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID
)
SELECT
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
SYSDATE,
1,
SYSDATE,
1,
1,
'LOT1', --THE SECOND LOT NUMBER
2,
2,
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
'RCV',
PO.RCV_TRANSACTIONS_INTERFACE_S.CURRVAL –-This should join to
RCV_TRANSACTIONS_INTERFACE.INTERFACE_TRANSACTION_ID
FROM DUAL;
/* INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE */
INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE
(TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID
)
SELECT
MTL_MATERIAL_TRANSACTIONS_S.CURRVAL
SYSDATE,
1,
SYSDATE,
1,
1,
'SER3', --From Serial Number
'SER4', --To Serial Number
'RCV',
PO.RCV_TRANSACTIONS_INTERFACE_S.CURRVAL –-This should join to
RCV_TRANSACTIONS_INTERFACE.INTERFACE_TRANSACTION_ID
FROM DUAL;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
Receiving Open Interface 16-107
DBMS_OUTPUT.PUT_LINE('ERROR');
END;
/
Stuck Transaction Scenarios and Scripts
The following section contains information about reprocessing an errored row for an
ASN/RECEIVE transaction. Run the following queries to identify the cause of the error,
and fix the error before reprocessing any stuck records. After you fix the errors, check
the following cases studies and run appropriate scripts.
Note: Verify the Receiving transaction manager is not running while
running the following script.
SELECT ERROR_MESSAGE, INTERFACE_LINE_ID, INTERFACE_HEADER_ID
FROM PO_INTERFACE_ERRORS
WHERE INTERFACE_LINE_ID IN
(SELECT INTERFACE_TRANSACTION_ID
FROM RCV_TRANSACTIONS_INTERFACE
WHERE PROCESSING_STATUS_CODE IN('PENDING', 'ERROR', 'RUNNING',
'COMPLETED'));
SELECT ERROR_MESSAGE, INTERFACE_LINE_ID, INTERFACE_HEADER_ID
FROM PO_INTERFACE_ERRORS
WHERE INTERFACE_HEADER_ID IN
(SELECT INTERFACE_HEADER_ID
FROM RCV_HEADERS_INTERFACE
WHERE PROCESSING_STATUS_CODE IN('PENDING', 'ERROR', 'RUNNING'));
Case 1
Some of the rows in the RCV_TRANSACTIONS_INTERFACE for a given
RCV_HEADERS_INTERFACE processed successfully. This means that the system
created the shipment header in RCV_SHIPMENT_HEADERS, and one or more
shipment lines exist in RCV_SHIPMENT_LINES. You can create a new header to
process the stuck records. Reprocess the records as follows:
1. Create a backup of the RCV_TRANSACTIONS_INTERFACE records.
16-108 Oracle Supply Chain Management APIs and Open Interfaces Guide
CREATE TABLE CASE1_RTI_BACKUP AS
SELECT RTI.* -- SELECT FOR STUCK RECORDS IN ONLINE/IMMEDIATE
FROM APPS.RCV_TRANSACTIONS_INTERFACE RTI,
APPS.PO_LINE_LOCATIONS_ALL PLL,
APPS.PO_HEADERS_ALL POH,
APPS.RCV_HEADERS_INTERFACE RHI,
APPS.RCV_SHIPMENT_HEADERS RSH
WHERE RTI.PO_HEADER_ID = POH.PO_HEADER_ID
AND PLL.LINE_LOCATION_ID = RTI.PO_LINE_LOCATION_ID
AND NVL(PLL.CLOSED_CODE, 'OPEN') = 'OPEN'
AND RTI.SHIPMENT_HEADER_ID IS NOT NULL
AND RTI.VENDOR_ID IS NOT NULL
AND RTI.PROCESSING_MODE_CODE IN('ONLINE', 'IMMEDIATE', 'BATCH')
AND RTI.PROCESSING_STATUS_CODE IN('PENDING', 'ERROR',
'RUNNING', 'COMPLETED')
AND RSH.SHIPMENT_HEADER_ID = RTI.SHIPMENT_HEADER_ID
AND EXISTS
(SELECT 'ONE OR MORE SHIPMENT LINE(S) EXISTS'
FROM APPS.RCV_SHIPMENT_LINES RSL
WHERE RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID);
2. Create a backup of the RCV_HEADERS_INTERFACE records.
CREATE TABLE CASE1_RHI_BACKUP AS
SELECT RHI.*
FROM RCV_HEADERS_INTERFACE RHI
WHERE HEADER_INTERFACE_ID IN
(SELECT DISTINCT HEADER_INTERFACE_ID
FROM CASE1_RTI_BACKUP);
3. Some records might have been ASN direct delivery for example,
RTI.TRANSACTION_TYPE = SHIP and RTI.AUTO_TRANSACT_CODE =
DELIVER. In this case, receiving changes the TRANSACTION_TYPE to RECEIVE.
When the rows are reprocessed, they reverted back to their original state.
DECLARE
CURSOR C1 IS
SELECT RHI.HEADER_INTERFACE_ID
FROM CASE2_RHI_BACKUP RHI,
CASE2_RTI_BACKUP RTI
WHERE RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID
AND RHI.ASN_TYPE IN ('ASN','ASBN')
AND RTI.TRANSACTION_TYPE = 'RECEIVE';
BEGIN
FOR REC1 IN C1
LOOP
UPDATE RCV_TRANSACTIONS_INTERFACE
SET TRANSACTION_TYPE = 'SHIP',
SHIPMENT_HEADER_ID = NULL,
SHIPMENT_LINE_ID = NULL
WHERE HEADER_INTERFACE_ID = REC1.HEADER_INTERFACE_ID;
END LOOP;
END ;
/
Receiving Open Interface 16-109
4. Update the records to be processed.
DECLARE
CURSOR C1 IS
SELECT *
FROM CASE1_RHI_BACKUP;
CURSOR C2 IS
SELECT *
FROM CASE1_RTI_BACKUP;
BEGIN
FOR REC1 IN C1
LOOP
UPDATE RCV_HEADERS_INTERFACE
SET PROCESSING_STATUS_CODE='PENDING',
RECEIPT_HEADER_ID=NULL,
PROCESSING_REQUEST_ID=NULL,
VALIDATION_FLAG='Y', --'Y' FOR PRE-PROCESSOR DERIVATION.
EXPECTED_RECEIPT_DATE=SYSDATE,
SHIPPED_DATE=SYSDATE,
SHIPMENT_NUM='&SHIPMENT_NUM' –-NEW SHIPMENT NUMBER
WHERE HEADER_INTERFACE_ID = REC1.HEADER_INTERFACE_ID;
END LOOP;
FOR REC2 IN C2
LOOP
UPDATE RCV_TRANSACTIONS_INTERFACE
SET PROCESSING_STATUS_CODE='PENDING',
PROCESSING_MODE_CODE='BATCH',
TRANSACTION_STATUS_CODE='PENDING',
PROCESSING_REQUEST_ID=NULL,
VALIDATION_FLAG='Y', --'Y' FOR PRE-PROCESSOR DERIVATION.
SHIPMENT_HEADER_ID=NULL,
TRANSACTION_DATE=SYSDATE, --THIS IS REQUIRED IF THE RECORD
WAS STUCK SOMETIME BACK
CREATION_DATE=SYSDATE, --AND THE CORRESPONDING PERIODS ARE
NOW CLOSED
EXPECTED_RECEIPT_DATE=SYSDATE
WHERE INTERFACE_TRANSACTION_ID = REC2.INTERFACE_TRANSACTION_ID;
END LOOP;
END;
/
COMMIT;
5. Run the processor.
Case 2
RCV_HEADERS_INTERFACE processed successfully. This means that the shipment
header is created in RCV_SHIPMENT_HEADERS but none of the
RCV_TRANSACTIONS_INTERFACEs for the given RCV_HEADERS_INTERFACE
16-110 Oracle Supply Chain Management APIs and Open Interfaces Guide
have processed yet. This means that no shipment lines exist in
RCV_SHIPMENT_LINES. To reprocess records, delete the orphan
RCV_SHIPMENT_HEADERS record, and reset the RCV_HEADERS_INTERFACE and
the RCV_TRANSACTIONS_INTERFACE.
1. Create a backup of the RCV_TRANSACTIONS_INTERFACE,
RCV_HEADERS_INTERFACE, and RCV_SHIPMENT_LINES records tables.
CREATE TABLE CASE2_RTI_BACKUP AS
SELECT RTI.* -- SELECT FOR STUCK RECORDS IN ONLINE/IMMEDIATE
FROM APPS.RCV_TRANSACTIONS_INTERFACE RTI,
APPS.PO_LINE_LOCATIONS_ALL PLL,
APPS.PO_HEADERS_ALL POH,
APPS.RCV_SHIPMENT_HEADERS RSH
WHERE RTI.PO_HEADER_ID = POH.PO_HEADER_ID
AND PLL.LINE_LOCATION_ID = RTI.PO_LINE_LOCATION_ID
AND NVL(PLL.CLOSED_CODE, 'OPEN') = 'OPEN'
AND RTI.SHIPMENT_HEADER_ID IS NOT NULL
AND RTI.VENDOR_ID IS NOT NULL
AND RTI.PROCESSING_MODE_CODE IN('ONLINE', 'IMMEDIATE', 'BATCH')
AND RTI.PROCESSING_STATUS_CODE IN('PENDING', 'ERROR',
'RUNNING', 'COMPLETED')
AND RSH.SHIPMENT_HEADER_ID = RTI.SHIPMENT_HEADER_ID
AND NOT EXISTS
(SELECT 'NO SHIPMENT LINES'
FROM APPS.RCV_SHIPMENT_LINES RSL
WHERE RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID);
CREATE TABLE CASE2_RHI_BACKUP AS
SELECT RHI.*
FROM RCV_HEADERS_INTERFACE RHI
WHERE HEADER_INTERFACE_ID IN
(SELECT DISTINCT HEADER_INTERFACE_ID
FROM CASE2_RTI_BACKUP);
CREATE TABLE CASE2_RSH_BACKUP AS
SELECT *
FROM RCV_SHIPMENT_HEADERS
WHERE SHIPMENT_HEADER_ID IN
(SELECT SHIPMENT_HEADER_ID
FROM CASE2_RTI_BACKUP);
2. Delete the orphan RCV_SHIPMENT_LINES records.
DELETE FROM RCV_SHIPMENT_HEADERS
WHERE SHIPMENT_HEADER_ID IN
(SELECT SHIPMENT_HEADER_ID
FROM CASE2_RTI_BACKUP);
3. Some records might have been ASN direct delivery (for example,
RTI.TRANSACTION_TYPE = SHIP and RTI.AUTO_TRANSACT_CODE =
DELIVER). In this case, receiving changes the TRANSACTION_TYPE to RECEIVE.
When you reprocess the row, it needs to revert back to the original state.
Receiving Open Interface 16-111
DECLARE
CURSOR C1 IS
SELECT RHI.HEADER_INTERFACE_ID
FROM CASE2_RHI_BACKUP RHI,
CASE2_RTI_BACKUP RTI
WHERE RHI.HEADER_INTERFACE_ID =RTI.HEADER_INTERFACE_ID
AND RHI.ASN_TYPE IN ('ASN','ASBN')
AND RTI.TRANSACTION_TYPE = 'RECEIVE';
BEGIN
FOR REC1 IN C1
LOOP
UPDATE RCV_TRANSACTIONS_INTERFACE
SET TRANSACTION_TYPE = 'SHIP',
SHIPMENT_HEADER_ID = NULL
WHERE HEADER_INTERFACE_ID = REC1.HEADER_INTERFACE_ID;
END LOOP;
END ;
/
4. Update the records to be processed.
16-112 Oracle Supply Chain Management APIs and Open Interfaces Guide
DECLARE
CURSOR C1 IS
SELECT *
FROM CASE2_RHI_BACKUP;
CURSOR C2 IS
SELECT *
FROM CASE2_RTI_BACKUP;
BEGIN
FOR REC1 IN C1
LOOP
UPDATE RCV_HEADERS_INTERFACE
SET PROCESSING_STATUS_CODE='PENDING',
RECEIPT_HEADER_ID=NULL,
PROCESSING_REQUEST_ID=NULL,
VALIDATION_FLAG='Y', -- 'Y' FOR PRE-PROCESSOR
DERIVATION.
EXPECTED_RECEIPT_DATE=SYSDATE,
SHIPPED_DATE=SYSDATE,
WHERE HEADER_INTERFACE_ID = REC1.HEADER_INTERFACE_ID;
END LOOP;
FOR REC2 IN C2
LOOP
UPDATE RCV_TRANSACTIONS_INTERFACE
SET PROCESSING_STATUS_CODE='PENDING',
PROCESSING_MODE_CODE='BATCH',
TRANSACTION_STATUS_CODE='PENDING',
PROCESSING_REQUEST_ID=NULL,
VALIDATION_FLAG='Y', -- 'Y' FOR PRE-PROCESSOR
DERIVATION.
SHIPMENT_HEADER_ID=NULL,
TRANSACTION_DATE=SYSDATE, -- THIS IS REQUIRED IF THE
RECORD WAS STUCK SOMETIME BACK
CREATION_DATE=SYSDATE, -- AND THE CORRESPONDING
PERIODS ARE NOW CLOSED
EXPECTED_RECEIPT_DATE=SYSDATE
WHERE INTERFACE_TRANSACTION_ID = REC2.INTERFACE_TRANSACTION_ID;
END LOOP;
END;
/
COMMIT;
5. Run the processor.
Case 3
Reprocess the interorg shipments or internal requisition documents that are stuck in the
RCV_TRANSACTIONS_INTERFACE and run the Receiving Transaction processor.
Receiving Open Interface 16-113
CREATE TABLE RTI_BACKUP AS
SELECT RTI.*
FROM APPS.RCV_TRANSACTIONS_INTERFACE RTI
WHERE SHIPMENT_HEADER_ID IN (
SELECT DISTINCT RTI.SHIPMENT_HEADER_ID
FROM APPS.RCV_TRANSACTIONS_INTERFACE RTI,
APPS.RCV_SHIPMENT_HEADERS RSH
WHERE RTI.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RSH.RECEIPT_SOURCE_CODE IN ('INVENTORY','INTERNAL ORDER')
AND RTI.PROCESSING_STATUS_CODE NOT IN ('PENDING','RUNNING')
);
CREATE TABLE RHI_BACKUP AS
SELECT RHI.*
FROM APPS.RCV_HEADERS_INTERFACE RHI
WHERE HEADER_INTERFACE_ID IN (SELECT DISTINCT HEADER_INTERFACE_ID
FROM RTI_BACKUP);
16-114 Oracle Supply Chain Management APIs and Open Interfaces Guide
DECLARE
CURSOR C1 IS
SELECT *
FROM RHI_BACKUP;
.
CURSOR C2 IS
SELECT *
FROM RTI_BACKUP;
.
BEGIN
FOR REC1 IN C1
LOOP
UPDATE APPS.RCV_HEADERS_INTERFACE
SET PROCESSING_STATUS_CODE='PENDING',
RECEIPT_HEADER_ID=NULL,
PROCESSING_REQUEST_ID=NULL,
VALIDATION_FLAG='Y',
EXPECTED_RECEIPT_DATE=SYSDATE,
SHIPPED_DATE=SYSDATE
WHERE HEADER_INTERFACE_ID = REC1.HEADER_INTERFACE_ID;
.
.
END LOOP;
.
FOR REC2 IN C2
LOOP
.
UPDATE APPS.RCV_TRANSACTIONS_INTERFACE
SET PROCESSING_STATUS_CODE='PENDING',
PROCESSING_MODE_CODE='BATCH',
TRANSACTION_STATUS_CODE='PENDING',
PROCESSING_REQUEST_ID=NULL,
VALIDATION_FLAG='Y',
TRANSACTION_DATE=SYSDATE,
CREATION_DATE=SYSDATE,
EXPECTED_RECEIPT_DATE=SYSDATE ,
INTERFACE_TRANSACTION_QTY = NULL
WHERE INTERFACE_TRANSACTION_ID = REC2.INTERFACE_TRANSACTION_ID;
.
UPDATE APPS.RCV_SHIPMENT_HEADERS
SET RECEIPT_NUM = NULL
WHERE SHIPMENT_HEADER_ID = REC2.SHIPMENT_HEADER_ID;
.
END LOOP;
.
END;
/
.
COMMIT
Case 4
Reprocess the transactions that are stuck in RCV_TRANSACTIONS_INTERFACE that
do not have any header information, such as DELIVER, RETURNS, CORRECTIONS,
and INSPECTION. When finished, run the Receiving Transaction processor.
Receiving Open Interface 16-115
CREATE TABLE RTI_BACKUP AS
SELECT RTI.*
FROM APPS.RCV_TRANSACTIONS_INTERFACE RTI
WHERE RTI.PROCESSING_STATUS_CODE NOT IN ('PENDING','RUNNING')
AND RTI.HEADER_INTERFACE_ID IS NULL;
UPDATE APPS.RCV_TRANSACTIONS_INTERFACE
SET PROCESSING_STATUS_CODE='PENDING',
PROCESSING_MODE_CODE='BATCH',
TRANSACTION_STATUS_CODE='PENDING',
PROCESSING_REQUEST_ID=NULL,
VALIDATION_FLAG='Y',
TRANSACTION_DATE=SYSDATE,
CREATION_DATE=SYSDATE,
EXPECTED_RECEIPT_DATE=SYSDATE ,
INTERFACE_TRANSACTION_QTY = NULL
WHERE RTI.PROCESSING_STATUS_CODE NOT IN ('PENDING','RUNNING')
AND RTI.HEADER_INTERFACE_ID IS NULL;
UPDATE APPS.RCV_TRANSACTIONS_INTERFACE
SET PROCESSING_STATUS_CODE='PENDING',
PROCESSING_MODE_CODE='BATCH',
TRANSACTION_STATUS_CODE='PENDING',
PROCESSING_REQUEST_ID=NULL,
VALIDATION_FLAG='Y',
TRANSACTION_DATE=SYSDATE,
CREATION_DATE=SYSDATE,
EXPECTED_RECEIPT_DATE=SYSDATE ,
INTERFACE_TRANSACTION_QTY = NULL
WHERE RTI.PROCESSING_STATUS_CODE NOT IN ('PENDING','RUNNING')
AND RTI.HEADER_INTERFACE_ID IS NULL;
Oracle Warehouse Management LPN Interface Table Supplemental
Information
The Receiving Transaction Manager uses the WMS_LPN_INTERFACE to process
receiving transactions. The Receiving Transaction Manager uses it to store LPN
transaction information. After the Receiving Transaction Manager processes a
transaction, it stores the LPN information in the WMS_LICENSE_PLATE_NUMBERS
table. The WMS_LPN_INTERFACE table contains the same columns as the
WMS_LICENSE_PLATE_NUMBERS table as well as additional columns that the
Receiving Transaction Manager uses.
Setting Up the LPN Interface Table
The system uses the WMS_LPN_INTERFACE table in conjunction with other receiving
interfaces when processing receiving transactions. For example, if you are packing
LPN1 into LPN2, then the system inserts the following into the
WMS_LPN_INTERFACE table.
16-116 Oracle Supply Chain Management APIs and Open Interfaces Guide
LPN PARENT_LPN SOURCE_GROUP_ID
LPN1 LPN2 <RTI.LPN_GROUP_ID>
If you unpack LPN1 into LPN2, then the system inserts the following in to the
WMS_LPN_INTERFACE table.
LPN PARENT_LPN SOURCE_GROUP_ID
LPN1 <NULL> <RTI.LPN_GROUP_ID>
For transactions that involve a new LPN, such as ASN creation, you insert the following
in to the WMS_INTERFACE table. The system later transfers this information to the
WMS_LICENSE_PLATE_NUMBERS table.
LPN PARENT_
LPN
Weight Attribute1 Attribute2 Attribute3 SOURCE_G
ROUP_ID
LPN2 <NULL> 400 sdfds efgsdf xyzsdf <RTI.LPN_G
ROUP_ID>
LPN1 LPN1 200 abc efg xyz <RTI.LPN_G
ROUP_ID>
Validation
The Receiving Transaction Manager validates the LPN and Transfer LPN columns
stamped on the Receiving Transactions Interface. The system does not perform an
actual validation on the WMS_LPN_INTERFACE table.
Related Topics
Oracle Warehouse Management License Plate Number Interface Table Description,
Oracle Manufacturing APIs and Open Interfaces Manual
eAM