查一ITEM各底层所需物料

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

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

转载于:http://blog.itpub.net/12122734/viewspace-450455/