P2P Flow SQL Query Link

R12

/* Formatted on 7/10/2014 9:13:18 AM (QP5 v5.256.13226.35510) */
SELECT prh.segment1 "PO Requisition Number"
     , pha.segment1 "PO Number"
     , aps.SEGMENT1 "Supplier Number"
     , aps.vendor_name
     , apss.vendor_site_code
     , apsc.first_name
     , apsc.last_name
     , pla.item_id
     , plla.ship_to_organization_id
     , plla.ship_to_location_id
     , rt.transaction_type
     , rt.destination_type_code
     , rsh.receipt_num "PO Receipt Number"
     , aia.invoice_num
     , aida.dist_code_combination_id
     , aca.check_number
     , gjh.ledger_id
     , gjh.name
  FROM po_requisition_headers_all prh
     , po_requisition_lines_all prl
     , po_req_distributions_all prd
     , po_headers_all pha
     , po_lines_all pla
     , po_distributions_all pda
     , po_line_locations_all plla
     , ap_suppliers aps
     , ap_supplier_sites_all apss
     , ap_supplier_contacts apsc
     , rcv_transactions rt
     , rcv_shipment_headers rsh
     , rcv_shipment_lines rsl
     , ap_invoices_all aia
     , ap_invoice_lines_all aila
     , ap_invoice_distributions_all aida
     , ap_invoice_payments_all aipa
     , ap_checks_all aca
     , xla.xla_transaction_entities xte
     , xla_events xe
     , xla_ae_headers xah
     , xla_ae_lines xal
     , xla_distribution_links xdl
     , gl_import_references gir
     , gl_je_batches gjb
     , gl_je_headers gjh
     , gl_je_lines gjl
 WHERE prh.segment1 = :RequitionNumber --Right click :RequitionNumber from Toad Enable Prompt For Substitution Variables
   AND aps.vendor_id = pha.vendor_id
   AND apss.vendor_id = aps.vendor_id
   AND apss.vendor_site_id(+) = pha.vendor_site_id
   AND apss.vendor_site_id = aca.vendor_site_id
   AND apsc.vendor_site_id = apss.vendor_site_id
   AND apsc.vendor_contact_id = pha.vendor_contact_id
   AND prl.requisition_header_id = prh.requisition_header_id
   AND prd.requisition_line_id = prl.requisition_line_id
   AND pda.req_distribution_id = prd.distribution_id
   AND pla.po_header_id = pda.po_header_id
   AND pla.po_line_id = pda.po_line_id
   AND pha.po_header_id = pla.po_header_id
   AND pha.org_id = 204
   AND plla.po_header_id = pla.po_header_id
   AND plla.po_line_id = pla.po_line_id
   AND rt.transaction_type = 'DELIVER'
   AND rt.po_header_id = pha.po_header_id
   AND rt.po_line_id = pla.po_line_id
   AND rsh.shipment_header_id = rt.shipment_header_id
   AND rsl.shipment_header_id = rsh.shipment_header_id
   AND rsl.shipment_line_id = rt.shipment_line_id
   AND aila.po_header_id = pha.po_header_id
   AND aila.po_line_id = pla.po_line_id
   AND aia.invoice_id = aila.invoice_id
   AND aida.invoice_id = aila.invoice_id
   AND aida.invoice_line_number = aila.line_number
   AND aipa.invoice_id = aia.invoice_id
   AND aca.check_id = aipa.check_id
   AND xte.entity_code = 'AP_PAYMENTS'
   AND xte.transaction_number = aca.check_number
   AND xte.source_id_int_1 = aipa.check_id
   AND xte.security_id_int_1 = aia.org_id
   AND xe.entity_id = xte.entity_id
   AND xah.event_id = xe.event_id
   AND xal.ae_header_id = xah.ae_header_id
   AND xal.ae_line_num = aida.invoice_line_number
   AND xdl.ae_header_id = xah.ae_header_id
   AND xdl.ae_line_num = xal.ae_line_num
   AND xdl.applied_to_dist_id_num_1 = aida.invoice_distribution_id
   AND gir.reference_5 = xte.entity_id                            -- Entity Id
   AND gir.reference_6 = TO_CHAR (xe.event_id)                      --Event Id
   AND gir.reference_7 = TO_CHAR (xah.ae_header_id)            -- AE Header Id
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   --and gir.created_by = 1318
   AND gjb.je_batch_id = gir.je_batch_id
   AND gjh.je_batch_id = gjb.je_batch_id
   AND gjh.je_header_id = gir.je_header_id
   AND gjl.je_header_id = gjh.je_header_id

   AND gjl.je_line_num = gir.je_line_num

11i

/* Formatted on 7/10/2014 9:22:34 AM (QP5 v5.256.13226.35510) */
SELECT A.ORG_ID "ORG ID"
     , E.VENDOR_NAME "VENDOR NAME"
     , UPPER (E.VENDOR_TYPE_LOOKUP_CODE) "VENDOR TYPE"
     , F.VENDOR_SITE_CODE "VENDOR SITE"
     , F.ADDRESS_LINE1 "ADDRESS"
     , F.CITY "CITY"
     , F.COUNTRY "COUNTRY"
     , TO_CHAR (TRUNC (D.CREATION_DATE)) "PO DATE"
     , D.SEGMENT1 "PO NUMBER"
     , D.TYPE_LOOKUP_CODE "PO TYPE"
     , C.QUANTITY_ORDERED "QTY ORDERED"
     , C.QUANTITY_CANCELLED "QTY CANCALLED"
     , G.ITEM_DESCRIPTION "ITEM DESCRIPTION"
     , G.UNIT_PRICE "UNIT PRICE"
     ,   (NVL (C.QUANTITY_ORDERED, 0) - NVL (C.QUANTITY_CANCELLED, 0))
       * NVL (G.UNIT_PRICE, 0)
          "PO Line Amount"
     , (SELECT DECODE (PH.APPROVED_FLAG, 'Y', 'Approved')
          FROM PO.PO_HEADERS_ALL PH
         WHERE PH.PO_HEADER_ID = D.PO_HEADER_ID)
          "PO STATUS"
     , A.INVOICE_TYPE_LOOKUP_CODE "INVOICE TYPE"
     , A.INVOICE_AMOUNT "INVOICE AMOUNT"
     , TO_CHAR (TRUNC (A.INVOICE_DATE)) "INVOICE DATE"
     , A.INVOICE_NUM "INVOICE NUMBER"
     , (SELECT DECODE (X.MATCH_STATUS_FLAG, 'A', 'Approved')
          FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL X
         WHERE X.INVOICE_DISTRIBUTION_ID = B.INVOICE_DISTRIBUTION_ID)
          "Invoice Approved?"
     , A.AMOUNT_PAID
     , H.AMOUNT
     , I.CHECK_NUMBER "CHEQUE NUMBER"
     , TO_CHAR (TRUNC (I.CHECK_DATE)) "PAYMENT DATE"
  FROM AP.AP_INVOICES_ALL A
     , AP.AP_INVOICE_DISTRIBUTIONS_ALL B
     , PO.PO_DISTRIBUTIONS_ALL C
     , PO.PO_HEADERS_ALL D
     , PO.PO_VENDORS E
     , PO.PO_VENDOR_SITES_ALL F
     , PO.PO_LINES_ALL G
     , AP.AP_INVOICE_PAYMENTS_ALL H
     , AP.AP_CHECKS_ALL I
 WHERE A.INVOICE_ID = B.INVOICE_ID
   AND B.PO_DISTRIBUTION_ID = C.PO_DISTRIBUTION_ID(+)
   AND C.PO_HEADER_ID = D.PO_HEADER_ID(+)
   AND E.VENDOR_ID(+) = D.VENDOR_ID
   AND F.VENDOR_SITE_ID(+) = D.VENDOR_SITE_ID
   AND D.PO_HEADER_ID = G.PO_HEADER_ID
   AND C.PO_LINE_ID = G.PO_LINE_ID
   AND A.INVOICE_ID = H.INVOICE_ID
   AND H.CHECK_ID = I.CHECK_ID
   AND F.VENDOR_SITE_ID = I.VENDOR_SITE_ID
   AND C.PO_HEADER_ID IS NOT NULL
   AND A.PAYMENT_STATUS_FLAG = 'Y'
   AND D.TYPE_LOOKUP_CODE != 'BLANKET';