PO & Receipt Query

Query to get PO & Receipt Data:

select
aps.segment1  "Supplier Number",
poh.segment1  "Purchase Order Number",
poh.AUTHORIZATION_STATUS  "Purchase Order Status",
Null  "Purchase Order Release Number",
poh.START_DATE  "Purchase Order Date",
papf.full_name  "Buyer",
msi.segment1   "Purchase Order Item Number",
pll.closed_code  "Purchase Order Item Status",
pol.PURCHASE_BASIS  "Purchase Order Item Type",
msi.segment1  "Location Part Number",
msi.description  "Location Part Description",
pol.VENDOR_PRODUCT_NUM  "Supplier Part Number",
POL.QUANTITY  "Item Order Quantity",
pol.unit_price  "Location Item Price",
poh.CURRENCY_CODE  "Location Currency Code",
pol.unit_price  "Supplier Item Price",
aps.PAYMENT_CURRENCY_CODE  "Supplier Currency Code",
msi.PRIMARY_UNIT_OF_MEASURE  "Inventory Unit of Measure",
pol.UNIT_MEAS_LOOKUP_CODE  "Purchase Unit of Measure",
Null  "Unit of Measure Conversion",
Null  "Item Schedule Number",
Null  "Schedule Quantity",
Null  "Schedule Open Quantity",
pll.Need_by_Date  "Need Date",
Null  "Expected Delivery Date",
pll.Promised_date  "Supplier Promise Date",
rct.transaction_date  "Receipt Date",
rct.receipt_num  "Receipt Number",
rct.quantity  "Receipt Quantity",
Null  "Rejected Quantity",
Null  "Rebate Amount",
Null  "Surcharge Amount",
Null  "PO Location Account Number",
POC.name  "PO Location Company Name",
POC.ADDRESS_LINE_1 "PO Location Address Line 1",
POC.ADDRESS_LINE_2 "PO Location Address Line 2",
POC.ADDRESS_LINE_3 "PO Location Address Line 3",
POC.TOWN_OR_CITY   "PO Location City",
POC.REGION_2       "PO Location State/Province",
POC.POSTAL_CODE    "PO Location Postal Code",
POC.COUNTRY        "PO Location Country Code"
from
apps.po_headers_all poh,
apps.po_lines_all pol,
apps.PO_LINE_LOCATIONS_ALL pll,
apps.PO_distributIONS_ALL pod,
apps.mtl_system_items_b msi,
apps.AP_SUPPLIERS aps,
apps.per_all_people_f papf,
(select
hou.name,
ADDRESS_LINE_1,
ADDRESS_LINE_2,
ADDRESS_LINE_3,
TOWN_OR_CITY,
hl.REGION_2,
hl.POSTAL_CODE,
hl.COUNTRY,
hou.ORGANIZATION_ID
from
hr_all_organization_units hou,
hr_locations hl
where hou.LOCATION_ID = hl.LOCATION_ID
) POC,
(select rt.transaction_date,
rsh.receipt_num,
rt.quantity,
rt.po_distribution_id,
rt.po_header_id,
rt.po_line_location_id,
rt.po_line_id
from
apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl
where
rsh.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=rt.po_header_id
AND rsl.po_line_id=rt.po_line_id
AND rsl.po_line_location_id=rt.po_line_location_id
AND rt.transaction_type = 'RECEIVE'
)RCT
where
poh.po_header_id = pol.po_header_id
and poh.po_header_id = pll.po_header_id
and poh.po_header_id = pod.po_header_id
and pol.po_line_id = pll.po_line_id
and pol.po_line_id = pod.po_line_id
and pol.item_id = msi.INVENTORY_ITEM_ID
and poh.vendor_id = APS.VENDOR_ID
and pod.destination_ORGANIZATION_ID = msi.ORGANIZATION_ID
and poh.agent_id=papf.person_id
AND pll.line_location_id = rct.po_line_location_id
AND pol.po_line_id = rct.po_line_id
AND pod.po_distribution_id = rct.po_distribution_id(+)
AND poh.po_header_id = rct.po_header_id(+);