Oracle EBS-SQL (PO-9):检查期间采购订单执行情况.sql
--采购订单执行情况查询(七天内接收情况)select pha.segment1 采购订单,
msib.segment1 物料编码,
pla.quantity 订单数量,
plla.need_by_date 需求日期,
ppf.FULL_NAME 采购员,
nvl(rcv.quantity,0)接收数量
from inv.mtl_system_items_b msib,
po.po_line_locations_all plla,
po.po_lines_all pla,
po.po_headers_all pha,
apps.per_people_f ppf,
(select rt.po_line_location_id,
sum(rt.quantity * DECODE(rt.transaction_type,
'DELIVER',
1,
'RETURN TO VENDOR',
-1,
0)) quantity
from po.rcv_transactions rt, po.po_line_locations_all plla
where rt.po_line_location_id = plla.line_location_id and
abs(rt.transaction_date - plla.need_by_date) <= 3 and
rt.transaction_type in ('DELIVER', 'RETURN TO VENDOR') and
plla.need_by_date between
to_date('20**-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and
to_date('20**-01-07 23:59:00', 'YYYY-MM-DD HH24:MI:SS') and
plla.line_location_id >0
group by rt.po_line_location_id ) rcv
where plla.po_line_id = pla.po_line_id and
pla.po_header_id = pha.po_header_id and
ppf.PERSON_ID = pha.agent_id and
pla.item_id = msib.inventory_item_id and
plla.ship_to_organization_id = msib.organization_idand
plla.line_location_id = rcv.po_line_location_id(+) and
plla.need_by_date between
to_date('20**-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and
to_date('20**-01-07 23:59:00', 'YYYY-MM-DD HH24:MI:SS');
页:
[1]