Thursday 13 September 2012

Bill of Material Query in Oracles Apps using Connect by Prior Clause


Bill of Material Query in Oracles Apps using Connect by Prior Clause




SELECT     (SELECT msi.segment1
              FROM mtl_system_items msi
             WHERE msi.inventory_item_id = bom.assembly_item_id
               AND msi.organization_id = 3) parent_item,
           bom.assembly_item_id,
              LPAD (' ', 2 * (LEVEL - 1), ' ')
           || (SELECT msi.segment1
                 FROM mtl_system_items msi
                WHERE msi.inventory_item_id = bic.component_item_id
                  AND msi.organization_id = 3) child_item,
           bic.component_item_id child_item_id,
--bic.component_sequence_id              ,
                                               bic.bill_sequence_id,
--parent_bill_seq_id             ,
           bic.operation_seq_num,
--bic.bom_item_type                  ,
--bic.item_num                           ,
                                 LEVEL, bic.component_quantity
      FROM bom_inventory_components bic,
           (SELECT *
              FROM bom_bill_of_materials
             WHERE organization_id = 3) bom
     WHERE bom.bill_sequence_id = bic.bill_sequence_id
START WITH bom.assembly_item_id = 1734
CONNECT BY PRIOR bic.component_item_id = bom.assembly_item_id

No comments:

Post a Comment