'Create Consumption Advice' Concurrent Program Stamps PO_DISTRIBUTION_ID in MTL_CONSUMPTION_TRANSACTIONS Incorrectly. (Doc ID 2917323.1)

Last updated on DECEMBER 23, 2022

Applies to:

Symptoms

ACTUAL BEHAVIOR
After submitting request "Create Consumption Advice", the mtl_consumption_transactions table has incorrect PO_DISTRIBUTION_ID

EXPECTED BEHAVIOR
Expect that the correct PO_DISTRIBUTION_ID is stamped in the mtl_consumption_transactions table

STEPS
1. Login to Oracle Apps
2. R) Oracle Purchasing -> Supply Base -> Approved Supplier list[ASL], query for the ASL -> Attributes -> Inventory Tab -> 'Consigned from Supplier' checkbox is enabled
3. R) Oracle Purchasing -> Purchase Orders -> Create GBPA (General Blanket Purchase Agreement) and have two records of the same period, same Item, same price.

CHECK GBPA DATA
------------------
SELECT ph.type_lookup_code, ph.segment1 ,PL.LINE_NUM,PL.PO_LINE_ID,
PL.ITEM_ID ,pl.unit_price, pll.start_date, pll.end_date
FROM
po_headers_all ph,
po_lines_all pl,
po_line_locations_all pll
WHERE 1=1
AND ph.type_lookup_code = 'BLANKET'
AND ph.po_header_id = pl.po_header_id
AND pl.po_line_id = pll.po_line_id
AND PL.ITEM_ID= <item_id>
AND TRUNC(SYSDATE) BETWEEN pll.start_date AND PLL.END_DATE;
-----------------

4) R) Inventory Super User > Setup > Transactions > Consigned/VMI Consumption > Transaction Type: Subinventory Transfer

5) Create Consigned Purchase order & Receipt to subinventory

6) INV > Transactions > Subinventory Transfer > type: Subinventory Transfer
The first transfer: Quantity=1 > save
The second transfer: Quantity=2 > save

7) When Subinventory Transfer was done, the mtl_consumption_transactions data is created and GBPA PO_LINE_ID is set.

Check the data
---------------
SELECT TRANSACTION_ID,TRANSACTION_DATE, INVENTORY_ITEM_ID,NET_QTY,NEED_BY_DATE,CONSUMPTION_PO_HEADER_ID, PO_DISTRIBUTION_ID,transaction_source_id,interface_distribution_ref,PO_LINE_ID
FROM mtl_consumption_transactions MCT
WHERE 1=1 AND TRUNC(CREATION_DATE)>SYSDATE-1
AND NVL(CONSUMPTION_PROCESSED_FLAG,'N')='N';

8) Run 'Create Consumption Advice' concurrent program

9) Check Consumption PO
-------------------------
SELECT ph.segment1 po_number,pl.line_num,pl.item_id,pll.quantity,
PH.po_header_id,pda.po_distribution_id
FROM
po_headers_all ph,
po.po_lines_all pl,
po_line_locations_all pll,
po_distributions_all pda
WHERE ph.type_lookup_code = 'STANDARD'
AND NVL(ph.consigned_consumption_flag,'N')='Y'
AND ph.po_header_id = pl.po_header_id
AND pl.po_line_id = pll.po_line_id
AND pll.line_location_id = pda.line_location_id
AND ph.po_header_id= <MCT.CONSUMPTION_PO_HEADER_ID>;

10) Find that the po_distributions_all.PO_DISTRIBUTION_ID is not equal to mtl_consumption_transactions.PO_DISTRIBUTION_ID

11) INVRCADB.pls version where this issue is identified: 120.29.12020000.12 and lower file versions have the issue.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.