Oracle EBS-SQL (PO-10):检查过期采购未接收订单.sql
Select pha.segment1 采购订单,MSI.SEGMENT1 物料编码,
MSI.DESCRIPTION 物料描述,
pla.LINE_NUM 行号,
PLLA.QUANTITY 数量,
ppf.FULL_NAME 采购员,
plla.NEED_BY_DATE 需求日期
From po.po_headers_all pha,
po.po_lines_all pla,
po.po_line_locations_all plla,
apps.per_people_f ppf,
INV.MTL_SYSTEM_ITEMS_B MSI
Where pha.PO_HEADER_ID = pla.PO_HEADER_ID AND
pha.type_lookup_code = 'STANDARD' AND
pha.PO_HEADER_ID = pla.PO_HEADER_ID AND
ppf.PERSON_ID = pha.agent_id AND
pla.po_line_id = plla.po_line_id AND
MSI.INVENTORY_ITEM_ID = PLA.ITEM_ID AND
MSI.ORGANIZATION_ID = X AND
((pha.CLOSED_CODE = 'OPEN') Or (pha.CLOSED_CODE Is Null)) AND
pha.AUTHORIZATION_STATUS = 'APPROVED' AND
((pha.user_hold_flag = 'N') or (pha.user_hold_flag is null)) AND
((pha.CANCEL_FLAG = 'N') or (pha.CANCEL_FLAG is null)) AND
(plla.NEED_BY_DATE+15) < Sysdate and not exists
(select 'X'
from po.rcv_transactions rt
where rt.PO_HEADER_ID = plla.PO_HEADER_ID AND
rt.po_line_id = plla.po_line_id AND
rt.PO_LINE_LOCATION_ID = plla.LINE_LOCATION_ID)
页:
[1]