oracle展bom逻辑,Oracle MASTER BOM展开-- 一句SQL搞定，以后展开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

欢迎指正！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7511831/viewspace-170116/，如需转载，请注明出处，否则将追究法律责任。