Introduction:
This blog has the SQL query that can be used for Item where used Report in Oracle Apps R12.
Cause of the issue: Business wants a PLSQL excel based report that contains the Item where used Report.
How do we solve: Create a PLSQL based report using the following query -.
|
SELECT
inv_code “Org”,
TRIM(component_item) “Item”,
comp_item_desc “Item_Desc”,
comp_status “Item_Status”,
comp_item_type “Item_Type”,
TRIM(assembly_item) “Parent_Item”,
assembly_item_desc “Parent_Item_Desc”,
assembly_item_status “Parent_Item_Status”,
— COMPONENT_QUANTITY ,
TRIM(order_level) “level”,
extended_qty “Usage”
FROM ( SELECT
(
SELECT
ood.organization_code
FROM
org_organization_definitions ood
WHERE
ood.organization_id = msi1.organization_id
) AS inv_code,
(
SELECT
segment1
FROM
mtl_system_items_b
WHERE
inventory_item_id = path
AND organization_id = ‘ || p_org_id || ‘
) AS assembly_item,
(
SELECT
description
FROM
mtl_system_items_b
WHERE
inventory_item_id = path
AND organization_id = ‘ || p_org_id || ‘
) assembly_item_desc,
— msi.description AS assembly_item_desc,
msi.inventory_item_status_code AS assembly_item_status,
item_num,
operation_seq_num,
lpad
( ” ”, 10 * (lvl – 1)) || msi1.segment1 AS component_item,
LPAD(” ”, lvl * 2) || lvl AS order_level,
msi1.description AS comp_item_desc,
msi1.item_type AS comp_item_type,
msi1.inventory_item_status_code AS comp_status,
bom.component_quantity,
bom.extended_qty,
msi1.primary_unit_of_measure AS uom
FROM
mtl_system_items_b msi,
mtl_system_items_b msi1,
(
SELECT
assembly_item_id,
LEVEL AS lvl,
SUBSTR(SYS_CONNECT_BY_PATH(assembly_item_id, ”/”) || ”/”, 2,
INSTR(SYS_CONNECT_BY_PATH(assembly_item_id, ”/”) || ”/”, ”/”, 2) – 2) AS path,
component_item_id,
component_quantity,
item_num,
supply_subinventory,
component_quantity * NVL(PRIOR component_quantity, 1) AS extended_qty,
alternate_bom_designator,
implementation_date,
creation_date,
from_date,
to_date,
operation_seq_num
FROM (
SELECT DISTINCT
bom.assembly_item_id,
bic.component_item_id,
bom.organization_id,
bom.alternate_bom_designator,
TRUNC(bom.implementation_date) AS implementation_date,
bic.component_quantity,
bic.item_num,
bic.supply_subinventory,
bom.creation_date,
bic.effectivity_date AS from_date,
bic.disable_date AS to_date,
bic.operation_seq_num
FROM
bom_bill_of_materials bom,
bom_inventory_components bic
WHERE
bom.bill_sequence_id = bic.bill_sequence_id
AND (bic.disable_date IS NULL OR bic.disable_date > SYSDATE)
AND bom.assembly_type = 1
AND bic.implementation_date IS NOT NULL
AND bom.alternate_bom_designator IS NULL
AND bom.organization_id = NVL(‘ || p_org_id || ‘, bom.organization_id)
)
START WITH assembly_item_id IN (
SELECT inventory_item_id
FROM mtl_system_items_b
WHERE organization_id = NVL(‘ || p_org_id || ‘, organization_id)
)
CONNECT BY NOCYCLE PRIOR component_item_id = assembly_item_id
) bom
WHERE
bom.assembly_item_id = msi.inventory_item_id
AND msi.organization_id = msi1.organization_id
AND bom.component_item_id = msi1.inventory_item_id
AND msi.enabled_flag = ”Y ” AND msi.organization_id = ‘ || p_org_id || ‘
)
WHERE
1 = 1
ORDER BY
TRIM(component_item),
TRIM(order_level),
TRIM(assembly_item) ;
|
Conclusion – The Item Where-Used Report in Apps R12 helps users quickly trace item usage across assemblies and BOMs, supporting accurate impact analysis and better decision-making
Recent Posts