select
LPad('.', level*2-2, ' ') || level 层次,
msib1.segment1 父项,
msib1.description 父项说明,
bcb.item_num BOM序号,
msib2.segment1 子项,
msib2.description 子项说明,
(select long_description
from inv.mtl_system_items_tl t
where t.inventory_item_id = msib2.inventory_item_id
and t.organization_id = msib2.organization_id
and t.language= Userenv('LANG')) 图号,
msib2.item_type 物料类型,
msib2.primary_unit_of_measure 单位,
bcb.component_quantity 定额,
bcb.component_yield_factor 产出率,
decode(prior (bcb.component_quantity/
bcb.component_yield_factor),
null,1,
prior (bcb.component_quantity/
bcb.component_yield_factor)
) *
(bcb.component_quantity/
bcb.component_yield_factor) 扩展数,
decode(bcb.wip_supply_type,
1,'推式',
2,'装配拉式',
3,'工序拉式',
6,'虚拟件',
null,null,
'其它') 供应方式,
bcb.supply_subinventory 供应子库
from bom.bom_structures_b bsb,
bom.bom_components_b bcb,
inv.mtl_system_items_b msib1,
inv.mtl_system_items_b msib2
where bsb.bill_sequence_id = bcb.bill_sequence_id
and nvl(bcb.disable_date,sysdate+1) >sysdate
and bsb.alternate_bom_designator is null
and msib1.organization_id = bsb.organization_id
and msib1.inventory_item_id = bsb.assembly_item_id
and msib2.organization_id = bsb.organization_id
and msib2.inventory_item_id = bcb.component_item_id
start with bsb.organization_id = 10 --在此输入ORG ID
and bsb.alternate_bom_designator is null
and msib1.organization_id = 10 --在此输入ORG ID
and msib1.segment1 = '1111' --在此输入父项编码
connect by prior bcb.component_item_id =bsb.assembly_item_id and
prior nvl(bcb.disable_date,sysdate+1) >= sysdate+1/86400 and
-- prior bcb.effectivity_date <= sysdate and
bcb.bill_sequence_id =bsb.bill_sequence_id and
bsb.organization_id =10 --在此输入父项编码
and bsb.alternate_bom_designator is null and
not (msib1.build_in_wip_flag = 'N' and
msib2.build_in_wip_flag = 'N' )
LPad('.', level*2-2, ' ') || level 层次,
msib1.segment1 父项,
msib1.description 父项说明,
bcb.item_num BOM序号,
msib2.segment1 子项,
msib2.description 子项说明,
(select long_description
from inv.mtl_system_items_tl t
where t.inventory_item_id = msib2.inventory_item_id
and t.organization_id = msib2.organization_id
and t.language= Userenv('LANG')) 图号,
msib2.item_type 物料类型,
msib2.primary_unit_of_measure 单位,
bcb.component_quantity 定额,
bcb.component_yield_factor 产出率,
decode(prior (bcb.component_quantity/
bcb.component_yield_factor),
null,1,
prior (bcb.component_quantity/
bcb.component_yield_factor)
) *
(bcb.component_quantity/
bcb.component_yield_factor) 扩展数,
decode(bcb.wip_supply_type,
1,'推式',
2,'装配拉式',
3,'工序拉式',
6,'虚拟件',
null,null,
'其它') 供应方式,
bcb.supply_subinventory 供应子库
from bom.bom_structures_b bsb,
bom.bom_components_b bcb,
inv.mtl_system_items_b msib1,
inv.mtl_system_items_b msib2
where bsb.bill_sequence_id = bcb.bill_sequence_id
and nvl(bcb.disable_date,sysdate+1) >sysdate
and bsb.alternate_bom_designator is null
and msib1.organization_id = bsb.organization_id
and msib1.inventory_item_id = bsb.assembly_item_id
and msib2.organization_id = bsb.organization_id
and msib2.inventory_item_id = bcb.component_item_id
start with bsb.organization_id = 10 --在此输入ORG ID
and bsb.alternate_bom_designator is null
and msib1.organization_id = 10 --在此输入ORG ID
and msib1.segment1 = '1111' --在此输入父项编码
connect by prior bcb.component_item_id =bsb.assembly_item_id and
prior nvl(bcb.disable_date,sysdate+1) >= sysdate+1/86400 and
-- prior bcb.effectivity_date <= sysdate and
bcb.bill_sequence_id =bsb.bill_sequence_id and
bsb.organization_id =10 --在此输入父项编码
and bsb.alternate_bom_designator is null and
not (msib1.build_in_wip_flag = 'N' and
msib2.build_in_wip_flag = 'N' )
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15225049/viewspace-741342/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15225049/viewspace-741342/