Thursday, 16 July 2015

Bom Explosion Query

SELECT DISTINCT LPAD (' ', LEVEL * 2) || LEVEL order_level
              , msib.segment1 assembly_item
              , msib.description assembly_description
              , msib.inventory_item_status_code assembly_item_status
              , SYS_CONNECT_BY_PATH (msib2.segment1, '/') PATH
              , msib2.segment1 AS component_item
              , msib2.description component_item_description
              , msib2.inventory_item_status_code component_item_status
              , bic.item_num
              , bic.operation_seq_num
              , bic.component_quantity
FROM            bom.bom_components_b bic
              , bom.bom_structures_b bom
              , inv.mtl_system_items_b msib
              , inv.mtl_system_items_b msib2
              , mtl_parameters mp
WHERE           1 = 1
AND             bic.bill_sequence_id = bom.bill_sequence_id
AND             SYSDATE BETWEEN bic.effectivity_date AND Nvl(bic.disable_date, SYSDATE)
AND             bom.assembly_item_id = msib.inventory_item_id
AND             bom.organization_id = msib.organization_id
AND             bic.component_item_id = msib2.inventory_item_id
AND             bom.organization_id = msib2.organization_id
AND             mp.organization_id = msib.organization_id
AND             mp.organization_code = :p_org_code            /* organization here */
AND             bom.alternate_bom_designator IS NULL
START WITH      msib.segment1 = :p_item_number                /*  component item to be used here */
CONNECT BY NOCYCLE PRIOR bic.component_item_id = msib.inventory_item_id
ORDER BY        PATH

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete