--采购订单执行情况查询(七天内接收情况)
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_id
and
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‘);