Wrong Quantity In MTL_TXN_REQUEST_LINES

(Doc ID 1410995.1)

Last updated on SEPTEMBER 28, 2016

Applies to:

Oracle Inventory Management - Version: 11.5.10.2 and later   [Release: 11.5.10 and later ]
Information in this document applies to any platform.

Symptoms

Find that for some closed move orders (LINE_STATUS=5), the QUANTITY, QUANTITY_DELIVERED, and QUANTITY_DETAILED are not equal.

EXPECTED BEHAVIOR
-----------------------
Expect the quantities to be equal.



The following SQL identified the invalid / incorrect data:

SELECT b.request_number,b.organization_id,a.line_id, a.quantity_delivered,
a.quantity, sum(c.primary_quantity)
FROM mtl_txn_request_lines a, mtl_txn_request_headers b,
mtl_material_transactions c
WHERE c.move_order_line_id=a.line_id
and a.header_id = b.header_id
and c.primary_quantity>0
AND NOT EXISTS(SELECT 1
FROM mtl_material_transactions_temp
WHERE a.line_id = move_order_line_id)
AND a.line_status = 5
GROUP BY b.request_number, b.organization_id, a.line_id,
a.quantity_delivered, a.quantity
having sum(c.primary_quantity)<>quantity_delivered;

select mtrl.*
from mtl_txn_request_lines mtrl, (SELECT
b.request_number,b.organization_id,a.line_id, a.quantity_delivered,
a.quantity, sum(c.primary_quantity)
FROM mtl_txn_request_lines a, mtl_txn_request_headers b,
mtl_material_transactions c
WHERE c.move_order_line_id=a.line_id
and a.header_id = b.header_id
and c.primary_quantity>0
AND NOT EXISTS(SELECT 1
FROM mtl_material_transactions_temp
WHERE a.line_id = move_order_line_id)
AND a.line_status = 5
GROUP BY b.request_number, b.organization_id, a.line_id,
a.quantity_delivered, a.quantity
having sum(c.primary_quantity)<>quantity_delivered) X
where mtrl.line_id = X.line_id;

select mmt.*
from mtl_txn_request_lines mtrl, mtl_material_transactions mmt, (SELECT
b.request_number,b.organization_id,a.line_id, a.quantity_delivered,
a.quantity, sum(c.primary_quantity)
FROM mtl_txn_request_lines a, mtl_txn_request_headers b,
mtl_material_transactions c
WHERE c.move_order_line_id=a.line_id
and a.header_id = b.header_id
and c.primary_quantity>0
AND NOT EXISTS(SELECT 1
FROM mtl_material_transactions_temp
WHERE a.line_id = move_order_line_id)
AND a.line_status = 5
GROUP BY b.request_number, b.organization_id, a.line_id,
a.quantity_delivered, a.quantity
having sum(c.primary_quantity)<>quantity_delivered) X
where mtrl.line_id = X.line_id
and mmt.move_order_line_id = mtrl.line_id;


SELECT b.request_number,b.organization_id,a.line_id, a.quantity_delivered,
a.quantity, sum(abs(c.primary_quantity))
FROM mtl_txn_request_lines a, mtl_txn_request_headers b,
mtl_material_transactions c
WHERE c.move_order_line_id=a.line_id
and a.header_id = b.header_id
and c.primary_quantity<0
AND NOT EXISTS(SELECT 1
FROM mtl_material_transactions_temp
WHERE a.line_id = move_order_line_id)
AND a.line_status = 5
group by b.request_number, b.organization_id, a.line_id,
a.quantity_delivered, a.quantity
having sum(abs(c.primary_quantity))<>quantity_delivered;

select mtrl.*
from mtl_txn_request_lines mtrl, (SELECT
b.request_number,b.organization_id,a.line_id, a.quantity_delivered,
a.quantity, sum(abs(c.primary_quantity))
FROM mtl_txn_request_lines a, mtl_txn_request_headers b,
mtl_material_transactions c
WHERE c.move_order_line_id=a.line_id
and a.header_id = b.header_id
and c.primary_quantity<0
AND NOT EXISTS(SELECT 1
FROM mtl_material_transactions_temp
WHERE a.line_id = move_order_line_id)
AND a.line_status = 5
group by b.request_number, b.organization_id, a.line_id,
a.quantity_delivered, a.quantity
having sum(abs(c.primary_quantity))<>quantity_delivered) X
where mtrl.line_id = X.line_id;

select mmt.*
from mtl_txn_request_lines mtrl, mtl_material_transactions mmt, (SELECT
b.request_number,b.organization_id,a.line_id, a.quantity_delivered,
a.quantity, sum(abs(c.primary_quantity))
FROM mtl_txn_request_lines a, mtl_txn_request_headers b,
mtl_material_transactions c
WHERE c.move_order_line_id=a.line_id
and a.header_id = b.header_id
and c.primary_quantity<0
AND NOT EXISTS(SELECT 1
FROM mtl_material_transactions_temp
WHERE a.line_id = move_order_line_id)
AND a.line_status = 5
group by b.request_number, b.organization_id, a.line_id,
a.quantity_delivered, a.quantity
having sum(abs(c.primary_quantity))<>quantity_delivered) X
where mtrl.line_id = X.line_id
and mmt.move_order_line_id = mtrl.line_id;

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