Missing Records In Po_lines_all , Po_Line_Locations_all and Po_distributions_all Table Deliver Txn / Exist In Archive Tables (Doc ID 1547080.1)

Last updated on AUGUST 22, 2016

Applies to:

Oracle Purchasing - Version 11.5.10 to 12.1.3 [Release 11.5 to 12.1]
Oracle Cost Management - Version 11.5.10.1 to 12.1.3 [Release 11.5 to 12.1]
Oracle Inventory Management - Version 11.5.10 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.

Symptoms

Find One transaction in Mtl_material_transactions MMT table is not getting costed due to the following error:

CSTPACIN.COST_INV_TXN:CSTPACDP.insert_account (4) ORA-01403: no data found 

 
Checking CSTACDPB.pls, inside insert_account procedure, under STMT 4:

        l_stmt_num := 4;
        SELECT POD.ussgl_transaction_code, nvl(pod.rate,1), pod.rate_date, poh.rate_type
        INTO l_ussgl_tc, l_conv_rate, l_conv_date, l_conv_type
        FROM mtl_material_transactions mmt,
             rcv_transactions rt,
             po_distributions_all pod,
             po_headers_all poh
        WHERE mmt.transaction_id = i_txn_id
        AND   mmt.rcv_transaction_id = rt.transaction_id
        AND   pod.po_distribution_id = rt.po_distribution_id
        AND   pod.po_header_id = poh.po_header_id;


Steps

Receiving Transactions Data Collection Script rcv11i.sql output from <Note 402245.1> shows

. In MTL_MATERIAL_TRANSACTIONS deliver transaction that  fails costing has
TRANSACTION_ID 8907493
INVENTORY_ITEM_ID 7268
ORGANIZATION_ID 84
TRANSACTION_TYPE_ID 18
TRANSACTION_QUANTITY 10
COSTED_FLAG E
RCV_TRANSACTION_ID 574359
SOURCE_CODE RCV
ERROR_EXPLANATION CSTPACIN.COST_INV_TXN:CSTPACDP.insert_account (4) ORA-01403: no data found

. In RCV_TRANSACTIONS table
TRANSACTION_ID 574359
TRANSACTION_TYPE DELIVER
QUANTITY 10
SOURCE_DOCUMENT_CODE PO
PO_HEADER_ID 77812
PO_LINE_ID 166184
PO_LINE_LOCATION_ID 151321
PO_DISTRIBUTION_ID 152981
ORGANIZATION_ID 84

. But in  PO_LINES_ALL, PO_LINE_LOCATIONS_ALL and PO_DISTRIBUTIONS_ALL tables
there are no records for
PO_HEADER_ID 77812
with
PO_LINE_ID 166184
PO_LINE_LOCATION_ID 151321
PO_DISTRIBUTION_ID 152981

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