Oracle MASTER BOM展开
select rownum seq_num
,top_item
,lpad(to_char(level),decode(level,1,1,level+1),'.') bom_level
,bbm.ASSEMBLY_ITEM_ID
,msi.segment1 assembly_item
,msi.description assembly_description
,bbm.COMMON_ASSEMBLY_ITEM_ID
,bic.item_NUM
,bbm.COMMON_BILL_SEQUENCE_ID
,bbm.BILL_SEQUENCE_ID
,msic.segment1 component_item
,msic.description c_item_description
,bic.COMPONENT_ITEM_ID
,bic.COMPONENT_QUANTITY
,msic.primary_unit_of_measure
,bic.COMPONENT_YIELD_FACTOR
,bic.EFFECTIVITY_DATE
,bic.ATTRIBUTE1
,bic.ATTRIBUTE2
,bic.CHANGE_NOTICE
,ood.ORGANIZATION_CODE
,ood.ORGANIZATION_NAME
,ood.ORGANIZATION_ID
from bom_bill_of_materials bbm
,bom_inventory_components bic
,mtl_system_items_b msi
,mtl_system_items_b msic
,org_organization_definitions ood
where
bbm.ASSEMBLY_ITEM_ID = msi.inventory_item_id
and bbm.ORGANIZATION_ID = msi.organization_id
and bic.COMPONENT_ITEM_ID =msic.inventory_item_id
and bic.PK2_VALUE =msic.organization_id
and bbm.BILL_SEQUENCE_ID=bic.BILL_SEQUENCE_ID
and (bic.DISABLE_DATE is null or bic.DISABLE_DATE >= sysdate)
and bic.EFFECTIVITY_DATE <= sysdate
and ood.ORGANIZATION_ID=msi.organization_id
connect by bbm.ASSEMBLY_ITEM_ID = prior bic.COMPONENT_ITEM_ID
start with msi.segment1=:P_ITEM
and ood.ORGANIZATION_CODE =:P_ORG