SQL -PO with Rcts, PO req and Internal Req

Query - PO


--PO with receipts
  SELECT
rsh.receipt_num
--
            ph.segment1 po_num,
--         ood.organization_name,
--         pol.po_line_id,
--         pll.quantity,
--         rsh.receipt_source_code,
--         rsh.vendor_id,
--         rsh.vendor_site_id,
--         rsh.organization_id,
--         rsh.shipment_num,
--         rsh.receipt_num,
--         rsh.ship_to_location_id,
--         rsh.bill_of_lading,
--         rsl.shipment_line_id,
--         rsl.QUANTITY_SHIPPED,
--         rsl.QUANTITY_RECEIVED
--         rct.transaction_type,
--         rct.transaction_id,
--         NVL (rct.source_doc_quantity, 0) transaction_qty
    FROM
         rcv_transactions rct,
         rcv_shipment_headers rsh,
         rcv_shipment_lines rsl,
         po_lines_all pol,
         po_line_locations_all pll,
         po_headers_all ph,
         org_organization_definitions ood
   WHERE     1 = 1
        -- AND TO_CHAR (rct.creation_date, 'YYYY') IN ('2010', '2011')
         AND rct.po_header_id = ph.po_header_id
         AND rct.po_line_location_id = pll.line_location_id
         AND rct.po_line_id = pol.po_line_id
         AND rct.shipment_line_id = rsl.shipment_line_id
         AND rsl.shipment_header_id = rsh.shipment_header_id
         AND rsh.ship_to_org_id = ood.organization_id
         and ph.segment1 in ('300046929')




--Purchase Requisition Details
SELECT prh.segment1 "Req #",
       prh.creation_date,
       prh.created_by,
       poh.segment1 "PO #",
       ppx.full_name "Requestor Name",
       prh.description "Req Description",
       prh.authorization_status,
       prh.note_to_authorizer,
       prh.type_lookup_code,
       prl.line_num,
       prl.line_type_id,
       prl.item_description,
       prl.unit_meas_lookup_code,
       prl.unit_price,
       prl.quantity,
       prl.quantity_delivered,
       prl.need_by_date,
       prl.note_to_agent,
       prl.currency_code,
       prl.rate_type,
       prl.rate_date,
       prl.quantity_cancelled,
       prl.cancel_date,
       prl.cancel_reason
  FROM po_requisition_headers_all prh,
       po_requisition_lines_all prl,
       po_req_distributions_all prd,
       per_people_x ppx,
       po_headers_all poh,
       po_distributions_all pda
 WHERE     prh.requisition_header_id = prl.requisition_header_id
       AND ppx.person_id = prh.preparer_id
       AND prh.type_lookup_code = 'PURCHASE'
       AND prd.requisition_line_id = prl.requisition_line_id
       AND pda.req_distribution_id = prd.distribution_id
       AND pda.po_header_id = poh.po_header_id
       --AND TO_CHAR (prh.creation_date, 'YYYY') IN ('2010', '2011')


-- INternal requisitions
SELECT prh.segment1 "Req #",
       prh.creation_date,
       prh.created_by,
       poh.segment1 "PO #",
       ppx.full_name "Requestor Name",
       prh.description "Req Description",
       prh.authorization_status,
       prh.note_to_authorizer,
       prl.line_num,
       prl.line_type_id,
       prl.source_type_code,
       prl.item_description,
       prl.unit_meas_lookup_code,
       prl.unit_price,
       prl.quantity,
       prl.quantity_delivered,
       prl.need_by_date,
       prl.note_to_agent,
       prl.currency_code,
       prl.rate_type,
       prl.rate_date,
       prl.quantity_cancelled,
       prl.cancel_date,
       prl.cancel_reason
  FROM po_requisition_headers_all prh,
       po_requisition_lines_all prl,
       po_req_distributions_all prd,
       per_people_x ppx,
       po_headers_all poh,
       po_distributions_all pda
 WHERE     prh.requisition_header_id = prl.requisition_header_id
       AND ppx.person_id = prh.preparer_id
       AND prh.type_lookup_code = 'INTERNAL'
       AND prd.requisition_line_id = prl.requisition_line_id
       AND pda.req_distribution_id(+) = prd.distribution_id
       AND pda.po_header_id = poh.po_header_id(+)
       AND TO_CHAR (prh.creation_date, 'YYYY') IN ('2010', '2011')