Oracle Ebs-sql (BOM-6): Check for data that is invalid but not invalidated in the BOM. SQL

Select Msi.segment1 assembly Parts Code,

Msi.description Assembly Parts Description,

Msi.item_type type,

Msi.planner_code Planner,

Msi1.segment1 Part code,

Msi1.description Part description,

Msi1.primary_unit_of_measure units

--, NVL (BOM. Alternate_bom_designator, ' primary ') alternatives,

Bic. Item_num serial number,

Bic.component_quantity Quota

From apps. Mtl_system_items MSI,

Apps. Mtl_system_items Msi1,

Apps.bom_bill_of_materials BOM,

Apps. Bom_inventory_components Bic

where msi.organization_id=x

--and msi1.inventory_item_status_code = ' Inactive '--the subkey is invalid and the BOM is not invalidated

and Msi.inventory_item_status_code = ' Inactive '--the parent is invalidated and the BOM is not invalidated

and bom.organization_id=x

and bom.assembly_item_id=msi.inventory_item_id

and bom.bill_sequence_id=bic.bill_sequence_id

and bic.component_item_id=msi1.inventory_item_id

and msi1.organization_id=msi.organization_id

and Bic. Disable_date is null

ORDER BY Msi.segment1,

Bic. Item_num