When Loading Advanced Shipping Receipt (ASR) for Lot Controlled Items, the PO_RECVLOAD Process Failed

(Doc ID 2306027.1)

Last updated on SEPTEMBER 11, 2017

Applies to:

PeopleSoft Enterprise SCM Purchasing - Version 9.1 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

When loading ASRs for lot controlled items, the PO_RECVLOAD process failed with below listed issues.


Issue 1 - SQL error.SQL Error: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
Issue 2 - SQL Error: ORA-00001: unique constraint (SYSADM.PS_RECV_LOAD_TA14) violated. This issue occurs when issue 1 is fixed.
Issue 3 - Vendor lot id is not populating into RECV_LN_ASSET from RECV_AOLS_EC record.

Steps to reproduce Issue-1

1) Load an ASN flat file with lot controlled item information through Inbound file publish (PeopleSoft delivered) process.
2) Navigation (Enterprise Components>Integration definitions>Initiate processes>Inbound file publish)
3) Run the Load receipts (PO_RECVLOAD) process.
Navigation (Purchasing>Receipts>Load receipts


Error:

Process abended at Step PO_RECVLOAD.ASNLOTEX.STEP0020
SQL Error: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Steps to reproduce Issue-2

1) For Item id (eg: LT5001) Define Shelf life days under BU US010
2) Make sure that there are multiple LOT id’s for the same item
3) Create a PO. Please make sure that the due date is current date and inventory BU under distribution is populated.
4) Dispatch the PO and load below ASN file using handler tester. Please note to that you need to change the shipment_no, PO id, Lot id, Vendor lot id and dates in the below xml file based on the PO which you have created.
5) Run Receipt Load process
6) Process fails with below error:

Error:

Process abended at Step PO_RECVLOAD.ASNLOTEX.STEP0010

SQL Error: ORA-00001: unique constraint (SYSADM.PS_RECV_LOAD_TA14) violated.

Steps to reproduce Issue-3

1) Make sure the ASN test file has a vendor lot id in it
2) Load the ASN file by running the inbound file publish process
3) The ASN file is loaded and lot information can be viewed on below page:
    Navigation: Main menu > Purchasing > receipts > Review Receipt Information > Advanced Shipment Receipts
4) The vendor lot id got loaded into PS_RECV_AOLS_EC table
5) Run the receipt Load Process
6) The vendor lot id is not loaded into RECV_LN_ASSET from RECV_AOLS_EC record as you can see on the below page:
    Main Menu > Purchasing > Receipts > add/Update Receipts


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms