Frozen Inventory Value Report–All Types in Oracle Apps R12.

Introduction: This blog has the SQL query and View query that can be used for Frozen Inventory Value Report–All Types in Oracle Apps R12.

Cause of the issue: Business wants a PLSQL-based excel report that contains the Frozen Inventory Value Report.

How do we solve: Create a PLSQL based report using the following query -.

SELECT item_type, detail_class, detail_type, qty, itemcost, mtlcost,
mtloverheadcost, resourcecost, outsidecost, overheadcost, plt_qty
FROM (
— Detail level data
SELECT item_type, detail_class, detail_type, qty, itemcost, mtlcost,
mtloverheadcost, resourcecost, outsidecost, overheadcost,
plt_qty, 1 AS sort_order
FROM apps.xx_frozen_inv_rep_v
WHERE 1 = 1
–AND organization_id = 124
UNION ALL
— Total summary row
SELECT ‘TOTAL’ AS item_type, NULL AS detail_class,
NULL AS detail_type, SUM (qty), SUM (itemcost),
SUM (mtlcost), SUM (mtloverheadcost), SUM (resourcecost),
SUM (outsidecost), SUM (overheadcost), SUM (plt_qty),
2 AS sort_order
FROM apps.xx_frozen_inv_rep_v
WHERE 1 = 1
–AND organization_id = 124
AND detail_type IN
(‘Finished good’, ‘Kit’, ‘Purchased item’, ‘Phantom item’,
‘Subassembly’))
ORDER BY sort_order, item_type, qty DESC, detail_type DESC;–DDL for – XX_FROZEN_INV_REP_V
CREATE OR REPLACE FORCE EDITIONABLE VIEW “APPS”.”XX_FROZEN_INV_REP_V” (
“ORGANIZATION_ID”,
“ITEM_TYPE”,
“DETAIL_CLASS”,
“DETAIL_TYPE”,
“QTY”,
“ITEMCOST”,
“MTLCOST”,
“MTLOVERHEADCOST”,
“RESOURCECOST”,
“OUTSIDECOST”,
“OVERHEADCOST”,
“PLT_QTY”
) AS
SELECT DISTINCT
rep.organization_id,
rep.item_type,
rep.detail_class,
rep.detail_type,
rep.qty,
rep.itemcost,
rep.mtlcost,
rep.mtloverheadcost,
rep.resourcecost,
rep.outsidecost,
rep.overheadcost,
rep.plt_qty
FROM
(

SELECT
msi.organization_id,
msi.item_type,
DECODE(msi.item_type,’FG’,’SUB-INV’,’P’,’PUR’,’K’,’KIT’,’PH’,’PH’,’SA’) detail_class,
fl.meaning AS detail_type,
round(SUM(miqv.primary_transaction_quantity),2) AS qty,
SUM(cc.item_cost * miqv.primary_transaction_quantity) AS itemcost,
SUM(cc.material_cost * miqv.primary_transaction_quantity) AS mtlcost,
SUM(cc.material_overhead_cost * miqv.primary_transaction_quantity) AS mtloverheadcost,
SUM(cc.resource_cost * miqv.primary_transaction_quantity) AS resourcecost,
SUM(cc.outside_processing_cost * miqv.primary_transaction_quantity) AS outsidecost,
SUM(cc.overhead_cost * miqv.primary_transaction_quantity) AS overheadcost,
SUM(xdmc.dmc_uom_conv_fr(msi.inventory_item_id,miqv.primary_transaction_quantity) ) AS plt_qty
FROM
inv.mtl_onhand_quantities_detail miqv,
inv.mtl_system_items_b msi,
(
SELECT
cictv.item_cost,
cictv.material_cost,
cictv.material_overhead_cost,
cictv.resource_cost,
cictv.outside_processing_cost,
cictv.overhead_cost,
cictv.inventory_item_id,
cictv.organization_id,
cictv.cost_type
FROM
apps.cst_item_cost_type_v cictv
WHERE
cictv.cost_type = ‘Frozen’
) cc,
fnd_common_lookups fl
WHERE
miqv.inventory_item_id = msi.inventory_item_id
AND miqv.owning_organization_id = msi.organization_id
AND msi.inventory_item_id = cc.inventory_item_id (+)
AND msi.organization_id = cc.organization_id (+)
— AND msi.organization_id = :p_org_id
AND msi.item_type = fl.lookup_code
AND fl.lookup_type = ‘ITEM_TYPE’
GROUP BY
msi.organization_id,
msi.item_type,
fl.meaning
UNION ALL
SELECT
organization_id,
DECODE(detail_class1,’SUB-INV’,’FG’,’PUR’,’P’,’KIT’,’K’,’SA’,’SA’) AS item_type,
detail_class1 AS detail_class,
detail_type,
qty,
itemcost,
mtlcost,
mtloverheadcost,
resourcecost,
outsidecost,
overheadcost,
plt_qty
FROM
(
SELECT
msi.organization_id,
‘FG’ AS item_type,
‘SUB-INV’ detail_class1,
DECODE(msi.planning_make_buy_code,1,’Make’,2,’Buy’) detail_type,
round(SUM(miqv.primary_transaction_quantity),2) qty,
SUM(cc.item_cost * miqv.primary_transaction_quantity) itemcost,
SUM(cc.material_cost * miqv.primary_transaction_quantity) mtlcost,
SUM(cc.material_overhead_cost * miqv.primary_transaction_quantity) mtloverheadcost,
SUM(cc.resource_cost * miqv.primary_transaction_quantity) resourcecost,
SUM(cc.outside_processing_cost * miqv.primary_transaction_quantity) outsidecost,
SUM(cc.overhead_cost * miqv.primary_transaction_quantity) overheadcost,
SUM(xdmc.dmc_uom_conv_fr(msi.inventory_item_id,miqv.primary_transaction_quantity) ) plt_qty
FROM
inv.mtl_onhand_quantities_detail miqv,
inv.mtl_system_items_b msi,
(
SELECT
cictv.item_cost,
cictv.material_cost,
cictv.material_overhead_cost,
cictv.resource_cost,
cictv.outside_processing_cost,
cictv.overhead_cost,
cictv.inventory_item_id,
cictv.organization_id,
cictv.cost_type
FROM
apps.cst_item_cost_type_v cictv
WHERE
cictv.cost_type = ‘Frozen’
) cc
WHERE
miqv.inventory_item_id = msi.inventory_item_id
AND miqv.owning_organization_id = msi.organization_id
AND msi.inventory_item_id = cc.inventory_item_id (+)
AND msi.organization_id = cc.organization_id (+)
AND msi.item_type = ‘FG’
— AND msi.organization_id = :p_org_id
GROUP BY
msi.organization_id,
msi.planning_make_buy_code
UNION ALL
SELECT
msi.organization_id,
‘P’ AS item_type,
‘PUR’ detail_class1,
‘RAW: Resin’ detail_type,
round(SUM(miqv.primary_transaction_quantity),2),
SUM(cc.item_cost * miqv.primary_transaction_quantity) itemcost,
SUM(cc.material_cost * miqv.primary_transaction_quantity) mtlcost,
SUM(cc.material_overhead_cost * miqv.primary_transaction_quantity) mtloverheadcost,
SUM(cc.resource_cost * miqv.primary_transaction_quantity) resourcecost,
SUM(cc.outside_processing_cost * miqv.primary_transaction_quantity) outsidecost,
SUM(cc.overhead_cost * miqv.primary_transaction_quantity) overheadcost,
SUM(xdmc.dmc_uom_conv_fr(msi.inventory_item_id,miqv.primary_transaction_quantity) ) plt_qty
FROM
inv.mtl_onhand_quantities_detail miqv,
inv.mtl_system_items_b msi,
(
SELECT
cictv.item_cost,
cictv.material_cost,
cictv.material_overhead_cost,
cictv.resource_cost,
cictv.outside_processing_cost,
cictv.overhead_cost,
cictv.inventory_item_id,
cictv.organization_id,
cictv.cost_type
FROM
apps.cst_item_cost_type_v cictv
WHERE
cictv.cost_type = ‘Frozen’
) cc
WHERE
miqv.inventory_item_id = msi.inventory_item_id
AND miqv.owning_organization_id = msi.organization_id
AND msi.inventory_item_id = cc.inventory_item_id (+)
AND msi.organization_id = cc.organization_id (+)
AND msi.item_type = ‘P’
AND msi.segment1 LIKE ‘750%’
— AND msi.organization_id = :p_org_id
GROUP BY
msi.organization_id
UNION ALL
SELECT
msi.organization_id,
‘P’ AS item_type,
‘PUR’ detail_class1,
‘RAW: Non-Resin’ detail_type,
round(SUM(miqv.primary_transaction_quantity),2) qty,
SUM(cc.item_cost * miqv.primary_transaction_quantity) itemcost,
SUM(cc.material_cost * miqv.primary_transaction_quantity) mtlcost,
SUM(cc.material_overhead_cost * miqv.primary_transaction_quantity) mtloverheadcost,
SUM(cc.resource_cost * miqv.primary_transaction_quantity) resourcecost,
SUM(cc.outside_processing_cost * miqv.primary_transaction_quantity) outsidecost,
SUM(cc.overhead_cost * miqv.primary_transaction_quantity) overheadcost,
SUM(xdmc.dmc_uom_conv_fr(msi.inventory_item_id,miqv.primary_transaction_quantity) ) plt_qty
FROM
inv.mtl_onhand_quantities_detail miqv,
inv.mtl_system_items_b msi,
(
SELECT
cictv.item_cost,
cictv.material_cost,
cictv.material_overhead_cost,
cictv.resource_cost,
cictv.outside_processing_cost,
cictv.overhead_cost,
cictv.inventory_item_id,
cictv.organization_id,
cictv.cost_type
FROM
apps.cst_item_cost_type_v cictv
WHERE
cictv.cost_type = ‘Frozen’
) cc
WHERE
miqv.inventory_item_id = msi.inventory_item_id
AND miqv.owning_organization_id = msi.organization_id
AND msi.inventory_item_id = cc.inventory_item_id (+)
AND msi.organization_id = cc.organization_id (+)
AND msi.item_type = ‘P’
AND msi.segment1 NOT LIKE ‘750%’
— AND msi.organization_id = :p_org_id
GROUP BY
msi.organization_id
UNION ALL
SELECT DISTINCT
msi.organization_id,
‘SA’ AS item_type,
‘SA’ AS detail_class,
‘Subassembly’ AS detail_type,
round(SUM(miqv.primary_transaction_quantity),2) AS qty,
SUM(cc.item_cost * miqv.primary_transaction_quantity) AS itemcost,
SUM(cc.material_cost * miqv.primary_transaction_quantity) AS mtlcost,
SUM(cc.material_overhead_cost * miqv.primary_transaction_quantity) AS mtloverheadcost,
SUM(cc.resource_cost * miqv.primary_transaction_quantity) AS resourcecost,
SUM(cc.outside_processing_cost * miqv.primary_transaction_quantity) AS outsidecost,
SUM(cc.overhead_cost * miqv.primary_transaction_quantity) AS overheadcost,
SUM(xdmc.dmc_uom_conv_fr(msi.inventory_item_id,miqv.primary_transaction_quantity) ) AS plt_qty
FROM
inv.mtl_onhand_quantities_detail miqv,
inv.mtl_system_items_b msi,
(
SELECT
cictv.item_cost,
cictv.material_cost,
cictv.material_overhead_cost,
cictv.resource_cost,
cictv.outside_processing_cost,
cictv.overhead_cost,
cictv.inventory_item_id,
cictv.organization_id,
cictv.cost_type
FROM
apps.cst_item_cost_type_v cictv
WHERE
cictv.cost_type = ‘Frozen’
) cc
WHERE
miqv.inventory_item_id = msi.inventory_item_id
AND miqv.owning_organization_id = msi.organization_id
AND msi.inventory_item_id = cc.inventory_item_id (+)
AND msi.organization_id = cc.organization_id (+)
AND msi.item_type NOT IN (
‘FG’,
‘P’,
‘K’
)
AND msi.segment1 NOT LIKE ‘750%’
— AND msi.organization_id = :p_org_id
GROUP BY
msi.organization_id
UNION ALL
SELECT
msi.organization_id,
‘K’ AS item_type,
‘KIT’ detail_class1,
DECODE(msi.planning_make_buy_code,1,’Make’,2,’Buy’) detail_type,
round(SUM(miqv.primary_transaction_quantity),2) qty,
SUM(cc.item_cost * miqv.primary_transaction_quantity) itemcost,
SUM(cc.material_cost * miqv.primary_transaction_quantity) mtlcost,
SUM(cc.material_overhead_cost * miqv.primary_transaction_quantity) mtloverheadcost,
SUM(cc.resource_cost * miqv.primary_transaction_quantity) resourcecost,
SUM(cc.outside_processing_cost * miqv.primary_transaction_quantity) outsidecost,
SUM(cc.overhead_cost * miqv.primary_transaction_quantity) overheadcost,
SUM(xdmc.dmc_uom_conv_fr(msi.inventory_item_id,miqv.primary_transaction_quantity) ) plt_qty
FROM
inv.mtl_onhand_quantities_detail miqv,
inv.mtl_system_items_b msi,
(
SELECT
cictv.item_cost,
cictv.material_cost,
cictv.material_overhead_cost,
cictv.resource_cost,
cictv.outside_processing_cost,
cictv.overhead_cost,
cictv.inventory_item_id,
cictv.organization_id,
cictv.cost_type
FROM
apps.cst_item_cost_type_v cictv
WHERE
cictv.cost_type = ‘Frozen’
) cc
WHERE
miqv.inventory_item_id = msi.inventory_item_id
AND miqv.owning_organization_id = msi.organization_id
AND msi.inventory_item_id = cc.inventory_item_id (+)
AND msi.organization_id = cc.organization_id (+)
AND msi.item_type = ‘K’
AND msi.segment1 NOT LIKE ‘750%’
— AND msi.organization_id = :p_org_id
GROUP BY
msi.organization_id,
DECODE(msi.planning_make_buy_code,1,’Make’,2,’Buy’)
) sub
ORDER BY
item_type,
detail_class,
detail_type
) rep
ORDER by         item_type,
qty desc,
detail_type desc;

Conclusion: With these SQL and view queries, users can easily build the Frozen Inventory Value Report for all item types in Oracle Apps R12.

Recent Posts