Introduction
This post is about to get an opening, purchasing, receive, sales, and closing quantity of items query.
Script to get opening, purchasing, receive, sales and closing quantity of items.
SELECT *
FROM (SELECT b.item_code, b.item_description item_description,
b.primary_uom, b.sub_inventory, b.LOCATOR,
b.opening_qty opening_qty, b.opening_value opening_value,
SUM (b.purchase_qty) purchase_qty,
SUM (b.purchasing_value) purchasing_value,
SUM (b.receive_qty) receive_qty,
SUM (b.receive_value) receive_value,
SUM (b.sales_qty) sales_qty, SUM (b.sale_value) sale_value,
SUM (b.other_plant_qty) other_plant_qty,
SUM (b.other_plant_value) other_plant_value,
(SUM (b.purchase_qty) + SUM (b.receive_qty) + b.opening_qty
)
– (ABS (SUM (b.sales_qty)) – SUM (b.other_plant_qty))
closing_qty,
( ( ( SUM (b.purchase_qty)
+ SUM (b.receive_qty)
+ b.opening_qty
)
– (ABS (SUM (b.sales_qty)) – SUM (b.other_plant_qty))
)
* cmpnt_cost
) closing_value,
cmpnt_cost
FROM (SELECT a.item_code, a.item_description, a.primary_uom,
a.sub_inventory, a.LOCATOR,
NVL (a.opening_qty, 0) opening_qty,
(NVL (opening_qty, 0) * cmpnt_cost) opening_value,
a.purchase_qty,
NVL (purchase_qty, 0) * cmpnt_cost purchasing_value,
a.receive_qty,
NVL (receive_qty, 0) * cmpnt_cost receive_value,
a.sales_qty,
NVL (sales_qty, 0) * cmpnt_cost sale_value,
a.other_plant_qty,
NVL (other_plant_qty, 0)
* cmpnt_cost other_plant_value,
cmpnt_cost
FROM (SELECT msib.segment1 item_code,
msib.description item_description,
msib.primary_uom_code primary_uom,
msi.secondary_inventory_name sub_inventory,
(SELECT SUM
(CASE
WHEN mmt1.transaction_type_id IN
(36,15,63,52,21)
THEN DECODE
(mmt1.primary_quantity
,
1, 0,
mmt1.primary_quantity
)
WHEN mmt1.transaction_type_id IN
(18, 12, 42, 43, 61,
33,35, 32, 34, 54,
62,21)
THEN mmt1.primary_quantity
ELSE 0
END
)
FROM mtl_material_transactions mmt1,
mtl_transaction_types mtt1
WHERE 1 = 1
AND mmt1.transaction_type_id =
mtt1.transaction_type_id
AND mmt1.organization_id =
msib.organization_id
AND mmt1.subinventory_code =
msi.secondary_inventory_name
AND mmt1.inventory_item_id =
msib.inventory_item_id
AND (mmt1.locator_id =
mil.inventory_location_id OR mil.inventory_location_id IS NULL)
AND TRUNC (mmt1.transaction_date) <
TRUNC (TO_DATE (:p_from_date)))
opening_qty,
mil.concatenated_segments LOCATOR,
TRUNC
(mmt.transaction_date)
transaction_date,
SUM
(CASE
WHEN mmt.transaction_type_id IN
(36)
THEN DECODE
(primary_quantity,
1, 0,
primary_quantity
)
WHEN mmt.transaction_type_id = 18
THEN primary_quantity
ELSE 0
END
) purchase_qty,
SUM
(CASE
WHEN mmt.subinventory_code LIKE
‘Staging_SI’
THEN
CASE
WHEN mmt.transaction_type_id IN
(12, 42, 43, 61,
15, 52)
THEN primary_quantity
ELSE 0
END
ELSE
CASE
WHEN mmt.transaction_type_id IN
(12, 42, 43, 61, 15)
THEN primary_quantity
ELSE 0
END
END
) receive_qty,
(SUM
(CASE
WHEN mmt.subinventory_code LIKE
‘Staging_SI’
THEN CASE
WHEN mmt.transaction_type_id IN
(33, 35)
THEN primary_quantity
ELSE 0
END
ELSE CASE
WHEN mmt.transaction_type_id IN
(33, 35, 52,21)
THEN primary_quantity
ELSE 0
END
END
)
) sales_qty,
(SELECT (SUM
(CASE
WHEN mmt1.transaction_type_id IN
(36, 15)
THEN DECODE
(mmt1.primary_quantity
,
1, 0,
mmt1.primary_quantity
)
WHEN mmt1.transaction_type_id IN
(18, 12, 42, 43, 61,
33, 35, 32, 34, 54,
62, 52)
THEN mmt1.primary_quantity
ELSE 0
END
)
)
FROM mtl_material_transactions mmt1,
mtl_transaction_types mtt1
WHERE 1 = 1
AND mmt1.transaction_type_id =
mtt1.transaction_type_id
AND mmt1.organization_id =
msib.organization_id
AND mmt1.subinventory_code =
mil.subinventory_code
AND mmt1.inventory_item_id =
msib.inventory_item_id
AND mmt1.subinventory_code = msi.secondary_inventory_name
AND mmt1.locator_id =
mil.inventory_location_id
AND TRUNC (mmt1.transaction_date) =
TRUNC (mmt.transaction_date))
closing_qty,
SUM
(CASE
WHEN mmt.subinventory_code LIKE
‘SLT_SI’
THEN CASE
WHEN mmt.transaction_type_id IN
(32, 34, 54, 62, 63)
THEN primary_quantity
ELSE 0
END
WHEN mmt.subinventory_code LIKE
‘RM_SI’
THEN CASE
WHEN mmt.transaction_type_id IN
(34, 54, 62, 63)
THEN primary_quantity
ELSE 0
END
ELSE CASE
WHEN mmt.transaction_type_id IN
(32, 34, 54, 62, 63,64)
THEN primary_quantity
ELSE 0
END
END
) other_plant_qty,
xx_item_costs
(msib.inventory_item_id,
msib.organization_id,
:p_to_date,
:p_from_date
) cmpnt_cost
FROM mtl_system_items_b msib,
mtl_secondary_inventories msi,
mtl_item_locations_kfv mil,
mtl_item_categories mic,
mtl_categories_kfv mcb,
org_organization_definitions ood,
mtl_material_transactions mmt,
mtl_transaction_types mtt
WHERE 1 = 1
AND ood.organization_id = msib.organization_id
AND msib.organization_id = msi.organization_id
AND msi.secondary_inventory_name(+) =
mmt.subinventory_code
AND mic.inventory_item_id =
msib.inventory_item_id
AND mic.organization_id = msib.organization_id
AND mic.category_id = mcb.category_id
AND msib.organization_id = :p_organization_id
AND msi.secondary_inventory_name =
NVL (:p_sub_inventory,
msi.secondary_inventory_name
)
AND ( mil.concatenated_segments =
NVL (:p_locator,
mil.concatenated_segments
)
OR mil.concatenated_segments IS NULL
)
AND mcb.segment1 =
NVL (:p_segment1, mcb.segment1)
AND mcb.segment2 =
NVL (:p_segment2, mcb.segment2)
AND mcb.segment3 =
NVL (:p_segment3, mcb.segment3)
AND mcb.segment4 =
NVL (:p_segment4, mcb.segment4)
AND ood.operating_unit = :p_org_id
AND mmt.transaction_type_id =
mtt.transaction_type_id
AND mmt.transaction_type_id IN
(18, 12, 42, 43, 61, 33, 35, 32, 34, 54,
62, 36, 15, 52, 63, 64,21)
AND mmt.organization_id = msib.organization_id(+)
AND mmt.subinventory_code = mil.subinventory_code(+)
AND mmt.locator_id = mil.inventory_location_id(+)
AND mmt.inventory_item_id = msib.inventory_item_id(+)
AND TRUNC (mmt.transaction_date)
BETWEEN TRUNC (TO_DATE (:p_from_date))
AND TRUNC (TO_DATE (:p_to_date))
AND category_set_id = 1
— AND MMT.INVENTORY_ITEM_ID=24256
GROUP BY msib.segment1,
msib.description,
msib.primary_uom_code,
msi.secondary_inventory_name,
mil.concatenated_segments,
TRUNC (mmt.transaction_date),
msib.organization_id,
mil.subinventory_code,
mil.inventory_location_id,
msib.inventory_item_id) a) b
GROUP BY b.item_code,
b.item_description,
b.primary_uom,
b.sub_inventory,
b.LOCATOR,
b.opening_qty,
b.opening_value,
cmpnt_cost
HAVING ( b.opening_qty <> 0
OR SUM (b.purchase_qty) <> 0
OR SUM (b.receive_qty) <> 0
OR SUM (b.sales_qty) <> 0
OR SUM (b.other_plant_qty) <> 0
OR ( SUM (b.purchase_qty)
+ SUM (b.receive_qty)
+ b.opening_qty
)
– (ABS (SUM (b.sales_qty)) – SUM (b.other_plant_qty)) <>
0
)
ORDER BY b.item_code, b.sub_inventory, b.LOCATOR)
UNION ALL
SELECT a.item_code, a.item_description, a.primary_uom, a.sub_inventory,
a.LOCATOR, NVL (a.opening_qty, 0) opening_qty, NVL(a.opening_qty * a.cmpnt_cost,0) opening_value,
NVL(a.purchase_qty,0) purchase_qty, NVL(a.purchasing_value,0) purchasing_value, NVL(a.receive_qty,0) receive_qty,
NVL(a.receive_value,0)receive_value,
NVL(a.sales_qty,0) sales_qty, NVL(a.sale_value,0)sale_value, NVL(a.other_plant_qty,0) other_plant_qty, NVL(a.other_plant_value,0)other_plant_value,
NVL (a.closing_qty, 0) closing_qty, NVL(a.closing_qty * a.cmpnt_cost,0) closing_value,
a.cmpnt_cost
FROM(SELECT msib.segment1 item_code, msib.description item_description,
msib.primary_uom_code primary_uom,
msi.secondary_inventory_name sub_inventory,
mil.concatenated_segments LOCATOR,
(SELECT SUM
(CASE
WHEN mmt1.transaction_type_id IN
(36,15,52,63,21)
THEN DECODE (mmt1.primary_quantity,
1, 0,
mmt1.primary_quantity
)
WHEN mmt1.transaction_type_id IN
(18, 12, 42, 43, 61, 33, 35, 32, 34, 54,62)
THEN mmt1.primary_quantity
ELSE 0
END
)
FROM mtl_material_transactions mmt1, mtl_transaction_types mtt1
WHERE 1 = 1
AND mmt1.transaction_type_id = mtt1.transaction_type_id
AND mmt1.organization_id = msib.organization_id
AND mmt1.subinventory_code = msi.secondary_inventory_name
AND msi.organization_id = :p_organization_id
AND mmt1.inventory_item_id = msib.inventory_item_id
AND mmt1.locator_id = mil.inventory_location_id
AND mmt1.organization_id = :p_organization_id
AND TRUNC (mmt1.transaction_date) <
TRUNC (TO_DATE (:p_from_date)))
opening_qty,
NULL purchase_qty, NULL purchasing_value, NULL receive_qty,
NULL receive_value, NULL sales_qty, NULL sale_value,
NULL other_plant_qty, NULL other_plant_value,
(SELECT (SUM
(CASE
WHEN mmt1.transaction_type_id IN
(36, 15,21)
THEN DECODE
(mmt1.primary_quantity,
1, 0,
mmt1.primary_quantity
)
WHEN mmt1.transaction_type_id IN
(18, 12, 42, 43, 61,
33, 35, 32, 34, 54,
62,52)
THEN mmt1.primary_quantity
ELSE 0
END
)
)
FROM mtl_material_transactions mmt1,
mtl_transaction_types mtt1
WHERE 1 = 1
AND mmt1.transaction_type_id =
mtt1.transaction_type_id
AND mmt1.organization_id =msib.organization_id
AND mmt1.subinventory_code =mil.subinventory_code
AND mmt1.inventory_item_id = msib.inventory_item_id
AND mmt1.subinventory_code = msi.secondary_inventory_name
AND mmt1.locator_id = mil.inventory_location_id
AND TRUNC (mmt1.transaction_date) <= TRUNC (TO_DATE (:p_to_date)))closing_qty,
xx_item_costs (msib.inventory_item_id,
msib.organization_id,
:p_to_date,
:p_from_date
) cmpnt_cost
FROM mtl_secondary_inventories msi,
mtl_item_locations_kfv mil,
mtl_system_items_b msib,
org_organization_definitions ood,
mtl_item_categories mic,
mtl_categories_kfv mcb,
mtl_material_transactions mmt,
mtl_transaction_types mtt
WHERE 1 = 1
AND ood.organization_id = msib.organization_id
AND msib.organization_id = msi.organization_id
AND msi.secondary_inventory_name = mil.subinventory_code
AND msib.organization_id = :p_organization_id
AND ood.operating_unit = :p_org_id
AND mic.inventory_item_id = msib.inventory_item_id
AND mic.organization_id = msib.organization_id
AND mic.category_id = mcb.category_id
AND msi.secondary_inventory_name =
NVL (:p_sub_inventory, msi.secondary_inventory_name)
AND mil.concatenated_segments =
NVL (:p_locator, mil.concatenated_segments)
AND mcb.segment1 = NVL (:p_segment1, mcb.segment1)
AND mcb.segment2 = NVL (:p_segment2, mcb.segment2)
AND mcb.segment3 = NVL (:p_segment3, mcb.segment3)
AND mcb.segment4 = NVL (:p_segment4, mcb.segment4)
AND category_set_id = 1
AND msib.inventory_item_id NOT IN (
SELECT inventory_item_id
FROM mtl_material_transactions mmt
WHERE 1 = 1
AND TRUNC (mmt.transaction_date)
BETWEEN TRUNC (TO_DATE (:p_from_date))
AND TRUNC (TO_DATE (:p_to_date))
AND mmt.organization_id = :p_organization_id
AND mmt.subinventory_code= mil.subinventory_code)
AND mmt.transaction_type_id = mtt.transaction_type_id
AND mmt.subinventory_code =mil.subinventory_code
AND mmt.inventory_item_id = msib.inventory_item_id
AND mmt.subinventory_code = msi.secondary_inventory_name
AND mmt.locator_id = mil.inventory_location_id
— AND MMT.INVENTORY_ITEM_ID=24256
GROUP BY msib.segment1,
msib.description,
msib.primary_uom_code,
msib.inventory_item_id,
msib.organization_id,
msi.secondary_inventory_name,
mil.inventory_location_id,
msi.organization_id,
mil.subinventory_code,
mil.concatenated_segments)a
where ( a.opening_qty <> 0
OR a.closing_qty <> 0 )
What we expect in the script.
This script helps us to comprehend how to get opening, purchasing, receive, sales and closing quantity of items. Couple of tables which is being used aremtl_secondary_inventories,mtl_item_locations_kfv, mtl_system_items_b,org_organization_definitions, mtl_item_categories,mtl_categories_kfv,mtl_material_transactions,mtl_transaction_types mtt etc.
Summary
This Post described the script how to get opening, purchasing, receive, sales and closing quantity of items in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions
