RCVRCERC Error APP-PO-14142 post_query-020: ORA-01403: When Searching For Amount Based Po (Doc ID 469603.1)

Last updated on JUNE 01, 2016

Applies to:

Oracle Inventory Management - Version 12.0.0 to 12.1.3 [Release 12.0 to 12.1]
Information in this document applies to any platform.
FORM:RCVRCERC.FMB - Enter Receipts


Symptoms

When attempting to receive Amount based Purchase Orders with Expense destination
the following error occurs when searching for the Expected receipts:

APP-P0-14142: post_query-020: ORA-01403: no data found
Cause: A SQL error has occurred in post_query. The
SQL error is ORA-01403: no data found.



Steps To Reproduce:
1. Create a Standard Purchase Order
Add line of Amount based Line type
Save and Approve the PO

2. Navigate to Enter Receipts form
Transactions --> Receiving --> Receipts
On  Find Expected Receipts form
Enter the Purchase Order number created in the initial steps
Click on Find
Error message appears
APP-P0-14142: post_query-020: ORA-01403: no data found

3. Navigate to Purchase Order Summary POXPOVPO form and query the purchase order line:
at line level the unit of measure is null for the related line type

 

More Analysis

1) The tkprofed trace shows the last instructions are the following
********************************************************************************
SELECT
RCV_ENTER_RECEIPTS_V.UNIT_PRICE,RCV_ENTER_RECEIPTS_V.LINE_CHKBOX, RCV_ENTER_RECEIPTS_V.PRIMARY_UOM,RCV_ENTER_RECEIPTS_V.DESTINATION_TYPE_CODE,
....
RCV_ENTER_RECEIPTS_V.SECONDARY_DEFAULT_IND
FROM
RCV_ENTER_RECEIPTS_SUPPLIER_V RCV_ENTER_RECEIPTS_V WHERE
(RCV_ENTER_RECEIPTS_V.SOURCE_TYPE_CODE = 'VENDOR' OR
RCV_ENTER_RECEIPTS_V.SOURCE_TYPE_CODE = 'ASN') and
((NVL(RCV_ENTER_RECEIPTS_V.closed_code,'OPEN') NOT IN ('CLOSED', 'CLOSED
FOR RECEIVING'))) and (RCV_ENTER_RECEIPTS_V.PO_HEADER_ID=:1) and
(RCV_ENTER_RECEIPTS_V.TO_ORGANIZATION_ID=:2) and
(RCV_ENTER_RECEIPTS_V.VENDOR_ID=:3) order by expected_receipt_date,
need_by_date, po_line_id, po_line_location_id, rcv_line_number, oe_order_line_num

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 72 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 72 0 2
********************************************************************************
...
********************************************************************************
SELECT UNIT_OF_MEASURE
FROM
MTL_UNITS_OF_MEASURE MUM WHERE UOM_CLASS = :B1 AND MUM.BASE_UOM_FLAG = 'Y'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 0
********************************************************************************
begin FND_MESSAGE.RETRIEVE(:result); end;

2) Output of rcv11i_sql script for the Purchase Order per <Note 402245.1> shows
In PO_LINES_ALL table
PO_LINE_ID = 482
PO_HEADER_ID = 488
LINE_TYPE_ID = 1000
LINE_NUM = 1
ITEM_ID = null
CATEGORY_ID = 5123
ITEM_DESCRIPTION = Test
UNIT_MEAS_LOOKUP_CODE = EACH
UNIT_PRICE = 1
QUANTITY = 20
PURCHASE_BASIS = SERVICES
MATCHING_BASIS = QUANTITY
ORDER_TYPE_LOOKUP_CODE = AMOUNT

3) In RCV_ENTER_RECEIPTS_SUPPLIER_V , for the specific PO_HEADER_ID and PO_LINE_ID, it can be seen
that PRIMARY_UOM= EACH
but the PRIMARY_UOM_CLASS is NULL

4) In PO_LINE_TYPES_B table, for the purchase order LINE_TYPE_ID = 1000 the following can be seen
LINE_TYPE= IGC CONTRACT COMMITMENT
PURCHASE_BASIS = SERVICES
MATCHING_BASIS = QUANTITY
ORDER_TYPE_LOOKUP_CODE = AMOUNT
UNIT_OF_MEASURE= EACH

5) In MTL_UNITS_OF_MEASURE table, there is no record with UNIT_OF_MEASURE= EACH

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