oracle bom 查询,求:如何写查询BOM表的SQL

呵呵

响应大家的号召

再发一次:

某个ITEM 的 BOM /ROUTING 全有了

select DISTINCT

msi1.segment1 P_item,

to_char(b.lvl) LV,

c.Department_Code,

nvl(bom.alternate_bom_designator,'P') Alternate_bom_designator,

msi2.segment1 C_item,

c.Usage,

b.component_quantity Component_Qty

from   inv.mtl_system_items_b msi1,

inv.mtl_system_items_b msi2,

bom.bom_bill_of_materials bom,

(select level lvl,

bic.bill_sequence_id,

bic.component_item_id,

bic.component_quantity,

bic.COMPONENT_YIELD_FACTOR,

bic.item_num,

bic.wip_supply_type,

bic.supply_subinventory,

bic.effectivity_date

FROM   bom.bom_inventory_components bic

where disable_date  IS  NULL

start with  bic.bill_sequence_id in

( select bill_sequence_id

from   bom.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.segment1  = 'FSC13AA4BMNCBB'

and    msi.organization_id = 83

and  bom2.alternate_bom_designator  is  null

)

CONNECT BY bic.bill_sequence_id =

prior (SELECT distinct  bill_sequence_id

FROM   bom.bom_bill_of_materials BO,

inv.mtl_system_items_b msi

WHERE  BO.assembly_item_id = bic.component_item_id

AND    BO.organization_id  = 83

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,

(select msib.inventory_item_id inventory_item_id,

nvl(bor.alternate_routing_designator,'P') Alternate,

bd.department_code Department_Code,

sum(bore.usage_rate_or_amount) Usage

from inv.mtl_system_items_b msib,

bom.bom_operational_routings bor,

bom.bom_operation_sequences bos,

bom.bom_operation_resources bore,

bom.bom_departments bd,

bom. bom_resources br

where msib.inventory_item_id = bor.assembly_item_id

and msib.organization_id = bor.organization_id

and msib.organization_id = 83

and bor.routing_sequence_id = bos.routing_sequence_id

and bos.operation_sequence_id = bore.operation_sequence_id

and bos.department_id = bd.department_id

and bore.resource_id = br.resource_id

and bos.DISABLE_DATE is null

and  bor.alternate_routing_designator is null

and bore.usage_rate_or_amount != 0

group by msib.inventory_item_id, bor.alternate_routing_designator, bd.department_code

) c

where b.bill_sequence_id = bom.bill_sequence_id

and bom.ORGANIZATION_ID = 83

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.SEGMENT1  not lIKE  'F9%'

AND MSI2.SEGMENT1  not like  'R__B%'

and msi2.segment1  not like  'F8%'

and  msi2.segment1 not like  'R9%'

and  msi2.segment1 not like  'Z%'

and bom.ASSEMBLY_ITEM_ID = c.inventory_item_id

order by 2