Wednesday, 30 July 2014

Bom Explosion Query

Below query can be handy to explode and Query BOM Details
 /* Formatted on 7/30/2014 7:01:32 PM (QP5 v5.115.810.9015) */
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

2 comments:

  1. Very useful.. Thank you

    ReplyDelete
  2. 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