Oracle Ebs-sql (PO-9): Checks the purchase order execution during the period. SQL

--Purchase Order Execution enquiry (received within seven days)
Select Pha.segment1 Purchase order,
Msib.segment1 Material Code,
Pla.quantity Order Quantity,
Plla.need_by_date Demand Date,
Ppf. Full_name Buyer,
NVL (rcv.quantity,0) Receive quantity
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 ');