查一ITEM各底层所需物料

最新推荐文章于 2025-09-09 10:02:29 发布

转载最新推荐文章于 2025-09-09 10:02:29 发布 · 176 阅读

本文介绍了一种通过SQL查询来获取复杂物料清单(BOM)结构的方法。该查询能递归地展示从第一级到第七级的组件信息，并关联采购订单详情，确保所获取的数据为已批准且未取消的有效记录。

create table temp as
(Select
msi.segment1 main_item,
msi.description main_desc,
msi.attribute10 main_att10,
msi.inventory_item_id main_item_id,
msi1.segment1 comp_item,
msi1.description comp_desc,
msi1.attribute10 comp_att10,
msi1.inventory_item_id comp_item_id,
bbom.component_quantity comp_qty,
bbom.leve,
msi1.PLANNER_CODE
from
inv.mtl_system_items msi,
inv.mtl_system_items msi1,
(
select --1th level
bom.assembly_item_id,
bic.component_item_id,
bic.component_quantity,
'1' AS leve
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate
--bom.assembly_item_id =119352
union
select --2th level
bom.assembly_item_id,
bic.component_item_id,
bic.component_quantity,
'2' AS leve
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate
--bom.assembly_item_id =119352
)
union
select --3th level
bom.assembly_item_id,
bic.component_item_id,
bic.component_quantity,
'3' AS leve
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate
--bom.assembly_item_id =119352
))
union
select --4th level
bom.assembly_item_id,
bic.component_item_id,
bic.component_quantity,
'4' AS leve
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate
--bom.assembly_item_id =119352
)))
union
select --5th level
bom.assembly_item_id,
bic.component_item_id,
bic.component_quantity,
'5' AS leve
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate
--bom.assembly_item_id=119352
))))
union
select --6th level
bom.assembly_item_id,
bic.component_item_id,
bic.component_quantity,
'6' AS leve
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate
--bom.assembly_item_id =119352
)))))
union
select --7th level
bom.assembly_item_id,
bic.component_item_id,
bic.component_quantity,
'7' AS leve
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate and
bom.assembly_item_id in(
select
bic.component_item_id
from
bom.bom_bill_of_materials bom,
bom.bom_inventory_components bic
where
bom.common_bill_sequence_id=bic.bill_sequence_id and
bom.organization_id=255 and
bic.implementation_date is not null and
nvl(bic.disable_date,sysdate+1)>sysdate and
nvl(bic.effectivity_date,sysdate)<=sysdate
--bom.assembly_item_id=119352
))))))
) bbom
where
bbom.assembly_item_id=msi.inventory_item_id and
bbom.component_item_id=msi1.inventory_item_id and
msi.organization_id=msi1.organization_id and
msi.organization_id=255)

---------------------------------
select a.comp_item,comp_desc,
pha.creation_date,
pla.unit_price
from temp a,
po.po_lines_all pla,
po.po_headers_all pha
where a.comp_item_id=pla.item_id
and pla.org_id=pha.org_id and
pla.po_header_id=pha.po_header_id and
nvl(pha.approved_flag,'N')='Y' and
pha.type_lookup_code='STANDARD' AND
nvl(pHa.CANCEL_FLAG,'N')='N' and
nvl(pla.CANCEL_FLAG,'N')='N' and
nvl(plla.CANCEL_FLAG,'N')='N' AND
plla.need_by_date is not null and
plla.quantity is not null and
pha.org_id=255