順展BOM實用SQL

依網上貼略有整理.[@more@]SELECT distinct
bb.BOMcc,
bb.zhpeijian,
bb.zhpeijiandesc,
bb.SUB_SEQUENCE,
bb.SUB_ITEM,
bb.SUB_ITEMDESC,
bb.component_quantity,
bb.UOM,
bb.LYL_RATE,
bb.SUBINV,
bb.LOCATION,
bb.ITEM_TYPE,
bb.BOMtimes,
BOR.COMPLETION_SUBINVENTORY ,--入庫,
MSI.WIP_SUPPLY_SUBINVENTORY,-- 沖減,
MSI.MAXIMUM_ORDER_QUANTITY ,-- 最大批次,
MSI.LEAD_TIME_LOT_SIZE ,-- 提前期,
MSI.FULL_LEAD_TIME ,-- 製造週期,
MSI.FIXED_LOT_MULTIPLIER ,-- 固定增加,
MSI.MINIMUM_ORDER_QUANTITY ,--最小批次,
MSI.FIXED_ORDER_QUANTITY ,-- 固定定貨量,
MSI.FIXED_DAYS_SUPPLY ,-- 固定天數,
MSI.PLANNER_CODE -- 計畫員
FROM MTL_SYSTEM_ITEMS MSI,
BOM_OPERATIONAL_ROUTINGS BOR,
BOM_OPERATION_SEQUENCES BOS,
(select distinct
aa.lvl BOMcc,
msi.segment1 zhpeijian,
msi.description zhpeijiandesc,
aa.item_num SUB_SEQUENCE,
msi1.segment1 SUB_ITEM,
msi1.description SUB_ITEMDESC,
aa.component_quantity ,
msi1.primary_unit_of_measure UOM,
aa.COMPONENT_YIELD_FACTOR LYL_RATE,
msi1.wip_supply_subinventory SUBINV,
mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || '.' ||
mil.segment4 LOCATION,
decode(msi1.planning_make_buy_code,1,'SUB',2,'PUR') ITEM_TYPE,
to_char(msi1.creation_date,'yyyy.mm.dd hh24:mm.ss') BOMtimes,
msi1.inventory_item_id
from mtl_system_items_b msi,
mtl_system_items_b msi1,
bom_bill_of_materials bom,
bom_inventory_components bic,
mtl_item_locations mil,
(
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 bom_inventory_components bic
where disable_date IS NULL
start with bic.bill_sequence_id in
(select bill_sequence_id
from 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 ='500012NCP'
and msi.organization_id =803 -- 組織ID
and bom2.alternate_bom_designator is null)
CONNECT BY bic.bill_sequence_id in prior
(SELECT distinct bill_sequence_id
FROM bom_bill_of_materials BO, inv.mtl_system_items_b msi
WHERE BO.assembly_item_id = bic.component_item_id
AND BO.organization_id = 803 --組織ID
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)) aa
where msi.organization_id=803 --組織ID
and msi1.organization_id=803 --組織ID
and bom.organization_id=803 --組織ID
and msi.inventory_item_id=bom.assembly_item_id
and bom.bill_sequence_id=bic.bill_sequence_id
and bic.component_item_id=msi1.inventory_item_id
and bic.disable_date is null
and aa.bill_sequence_id=bic.bill_sequence_id
and aa.component_item_id=msi1.inventory_item_id
and mil.inventory_location_id(+)=msi1.wip_supply_locator_id
and mil.organization_id(+)=803 --組織ID
order by
aa.lvl,
aa.item_num) bb
WHERE BOR.ASSEMBLY_ITEM_ID(+) = MSI.INVENTORY_ITEM_ID
AND BOS.ROUTING_SEQUENCE_ID(+) = BOR.ROUTING_SEQUENCE_ID
AND MSI.ORGANIZATION_ID(+) =803 -- 組織ID
and bor.alternate_routing_designator is null
and bb.inventory_item_id=msi.inventory_item_id
order by
bb.BOMcc,
bb.zhpeijian,
bb.zhpeijiandesc,
bb.sub_SEQUENCE,
bb.sub_item