oracle ebs 招标 比价 询价 议价 字段 sql,Oracle EBS-SQL (PO-13):检查采购物料无一揽子协议价格.sql...

Select

msi.segment1                               物料编码,

msi.DESCRIPTION                         物料描述,

msi.primary_unit_of_measure     主计量单位

FROM

inv.MTL_SYSTEM_ITEMS_b  msi

WHERE

msi.organization_id = X

AND MSI.INVENTORY_ITEM_ID NOT IN (

SELECT

MSI.INVENTORY_ITEM_ID

FROM

PO.PO_LINES_ALL PLA,

PO.PO_HEADERS_ALL         PHA,

INV.MTL_SYSTEM_ITEMS_B MSI,

po.po_vendors                      pv,

po.po_vendor_sites_all         pvs,

po.po_line_locations_all       PLLA

WHERE

PLA.PO_HEADER_ID=PHA.PO_HEADER_ID

AND PLA.ITEM_ID=MSI.INVENTORY_ITEM_ID

AND MSI.ORGANIZATION_ID=X

AND PHA.ORG_ID=PLA.ORG_ID

AND PHA.ORG_ID=Y

AND PLLA.PO_HEADER_ID=PLA.PO_HEADER_ID

AND PLLA.PO_LINE_ID=PLA.PO_LINE_ID

and pha.vendor_id=pv.vendor_id

and pv.vendor_id=pvs.vendor_id

and plla.org_id=pla.org_id

and pha.TYPE_LOOKUP_CODE='BLANKET'

AND PLA.CLOSED_CODE IS NULL

AND pha.closed_code IS NULL)

AND MSI.ITEM_TYPE='P'