有BOM且有采购自制件

select 
pha.segment1 po_num,
pha.creation_date,
pla.line_num,
msi.segment1 main_item,
msi.description main_desc,
pla.unit_price,
pha.currency_code,
nvl(pha.rate,'1') rate,
pla.unit_meas_lookup_code  uom_code,
pv.vendor_name
from
  po.po_lines_all pla,
  po.po_headers_all pha,
  inv.mtl_system_items msi,
  po.po_vendors pv
where
pla.org_id=pha.org_id
and pla.po_header_id=pha.po_header_id
and pha.org_id=msi.organization_id
and pla.item_id=msi.inventory_item_id
and pv.vendor_id=pha.vendor_id
and msi.organization_id=255
and msi.planning_make_buy_code=1
and pha.org_id=255
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 exists (select 1 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  pla.item_id=bom.assembly_item_id
  and　bic.implementation_date is not null
  and　nvl(bic.disable_date,sysdate+1)>sysdate
  and　nvl(bic.effectivity_date,sysdate)<=sysdate)

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

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