Introduction:
This SQL query is used to fetching the data of FG Items where used details like Component Item, Component Item Description, Parent ,Parent Item Description, Item Seq, Effettivity Date, Disable Date
Cause of the issue:
Business wants a report that details of FG Items where used details
How do we solve:
Create a report in BI publisher using the below SQL query to extract the FG Items where used details.
SQL Query:
SELECT msi.segment1 “Item”, msi.description “Item Description”,
msi.item_type “Item Type”,
msi.inventory_item_status_code “Item Status”,
ood.organization_code “Organization”, msi1.segment1 “Parent”,
msi1.description “Parent Item Description”,
msi1.item_type “Parent Item Type”,
msi1.inventory_item_status_code “Parent Item Status”,
TO_CHAR (bic.effectivity_date, ‘DD-MON-YYYY’) “Effectivity Date”,
TO_CHAR (bic.disable_date, ‘DD-MON-YYYY’) “Disable Date”
FROM bom_inventory_components_v bic,
bom_bill_of_materials bc,
mtl_system_items_b msi,
mtl_system_items_b msi1,
org_organization_definitions ood
WHERE 1 = 1
AND SYSDATE BETWEEN bic.effectivity_date AND NVL (bic.disable_date,
SYSDATE)
AND bic.bill_sequence_id = bc.bill_sequence_id
AND bic.component_item_id = msi.inventory_item_id
AND bc.assembly_item_id = msi1.inventory_item_id
AND bc.organization_id = msi1.organization_id
AND bc.organization_id = msi.organization_id
AND ood.organization_id = bc.organization_id
