p2p report

ReqNo., Item Name, UOM, Qty, PO Num, Receipt Num,AP Inv NUm, Check NUmber


SELECT prha.SEGMENT1 "Req Number" ,     
       msib.segment1   "Item Name" ,
       msib.PRIMARY_UNIT_OF_MEASURE "UOM",
       prla.QUANTITY "Qty",
       pha.segment1 "PO NUM",
       rsh.RECEIPT_NUM "Receipt Num" ,
       aia.INVOICE_NUM "Ap Invoice",
       aca.CHECK_NUMBER "Check Num"
FROM
     PO_REQUISITION_HEADERS_ALL PRHA,   
     PO_REQUISITION_LINES_ALL   PRLA,
     PO_REQ_DISTRIBUTIONS_ALL   PRDA,
     PO_HEADERS_ALL             PHA, 
     PO_LINE_LOCATIONS_ALL      PLLA,           
     PO_DISTRIBUTIONS_ALL       PDA ,
     MTL_SYSTEM_ITEMS_B         MSIB,
     RCV_SHIPMENT_HEADERS       RSH,
     RCV_SHIPMENT_LINES         RSL,
     AP_INVOICES_ALL             AIA,
     AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
     AP_CHECKS_ALL                ACA,
     AP_INVOICE_PAYMENTS_ALL      AIPA     
WHERE
            prha.SEGMENT1 = '9998'  
AND    prha.REQUISITION_HEADER_ID = prla.REQUISITION_HEADER_ID
AND    prla.REQUISITION_LINE_ID = prda.REQUISITION_LINE_ID
AND    prda.DISTRIBUTION_ID = pda.REQ_DISTRIBUTION_ID
AND    pda.po_header_id = pha.po_header_id
AND    msib.INVENTORY_ITEM_ID = prla.ITEM_ID
AND    msib.ORGANIZATION_ID = prla.DESTINATION_ORGANIZATION_ID 
AND    rsl.PO_HEADER_ID = pha.PO_HEADER_ID
AND    rsl.PO_DISTRIBUTION_ID = pda.PO_DISTRIBUTION_ID
AND    rsl.SHIPMENT_HEADER_ID = rsh.SHIPMENT_HEADER_ID 
AND    RSL.TO_ORGANIZATION_ID = PLLA.SHIP_TO_ORGANIZATION_ID
AND    RSL.PO_LINE_LOCATION_ID = PLLA.LINE_LOCATION_ID           
AND    aia.invoice_id = aida.invoice_id
AND    aida.PO_DISTRIBUTION_ID = pda.PO_DISTRIBUTION_ID
AND    pda.LINE_LOCATION_ID = plla.LINE_LOCATION_ID
AND    aia.invoice_id   = aipa.invoice_id
AND    aca.check_id = aipa.check_id