Oracle Ebs-sql (BOM-15): Check the multilayer BOM (with common BOM). SQL

SELECT DISTINCT

B.LVL level,

B.operation_seq_num process,

Msi1.segment1 the parent code,

Msi1.description Parent Part description,

Msi1.item_type Parent Type,

Msi1.inventory_item_status_code The parent code State,

Msi2.segment1 Sub-piece code,

Msi2.description description of child parts,

Msi2.item_type type of sub-piece,

B.component_quantity dosage,

B.component_yield_factor yield

From Inv.mtl_system_items_b Msi1,

Inv.mtl_system_items_b Msi2,

Apps. Bom_bill_of_materials BOM,

(Select Level LVL,

BIC.BILL_SEQUENCE_ID,

BIC.COMPONENT_ITEM_ID,

Bic.component_quantity,

Bic. Operation_seq_num,

Bic.component_yield_factor,

BIC.COMPONENT_SEQUENCE_ID,

Bic.item_num,

Bic.wip_supply_type,

Bic.supply_subinventory,

Bic.effectivity_date

From apps. Bom_inventory_components Bic

where Bic.disable_date is NULL

and Bic. Implementation_date is not NULL

Start with bic.bill_sequence_id in

(select NVL (common_bill_sequence_id,bill_sequence_id)

From apps. Bom_bill_of_materials Bom2,

Inv.mtl_system_items_b MSI

where bom2.assembly_item_id = msi.inventory_item_id

and bom2.organization_id = msi.organization_id

and msi.organization_id in (1,157)

and Bom2.alternate_bom_designator is null)

CONNECT by bill_sequence_id in prior

(SELECT distinct NVL (common_bill_sequence_id,bill_sequence_id)

From apps. Bom_bill_of_materials BO,

Inv.mtl_system_items_b MSI

WHERE bo.assembly_item_id = bic.component_item_id

and bo.organization_id in (1,157)

and bo.organization_id = msi.organization_id

and Bo. assembly_item_id = msi. inventory_item_id

and Bo.alternate_bom_designator is null

and Disable_date is NULL)) b

where b.bill_sequence_id = NVL (bom.common_bill_sequence_id,bom.bill_sequence_id)

and bom.organization_id in (1,157)

and bom.organization_id = msi1.organization_id

and BOM. assembly_item_id = Msi1. inventory_item_id

and bom.organization_id = msi2.organization_id

and b.component_item_id = MSI2. inventory_item_id

and Msi2.inventory_item_status_code <> ' Inactive '

ORDER BY B.LVL