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博客

有BOM且有采购自制件

最新文章