SQL Queries - P2P Cycle

Payment against Invoices

SELECT b.segment1 vendor_number,
       b.vendor_name vendor_name,
       c.vendor_site_code,
       c.pay_group_lookup_code,
       a.invoice_num invoice_number,
       a.invoice_date,
       a.gl_date,
       d.due_date,
       a.invoice_currency_code,
       a.invoice_amount,
       a.amount_paid,
       a.pay_group_lookup_code,
       d.payment_priority,
       (SELECT MAX (check_date)
          FROM ap_checks_all aca, ap_invoice_payments_all aip
          WHERE aca.CHECK_ID = aip.CHECK_ID AND aip.invoice_id =a.invoice_id)
          "Last Payment Made on",
          a.cancelled_date
  FROM apps.ap_invoices_all a,
       apps.ap_suppliers b,
       apps.ap_supplier_sites_all c,
       apps.ap_payment_schedules_all d,
       apps.ap_invoice_payments_all ap,
       ap_checks_all ac
WHERE     a.vendor_id = b.vendor_id
       AND a.vendor_site_id = c.vendor_site_id
       AND b.vendor_id = c.vendor_id
       AND a.invoice_id = d.invoice_id
       AND ap.invoice_id = a.invoice_id
       AND ac.CHECK_ID = ap.CHECK_ID
       and ac.STATUS_LOOKUP_CODE <> 'VOIDED'
       AND a.org_id = 89
       and a.invoice_id= 1234
       AND a.pay_group_lookup_code IN ('DISTRIBUTOR')
       AND ac.check_date BETWEEN TO_DATE ('01-Apr-2014', 'DD-MON-YYYY') AND TO_DATE ('30-Jun-2014 23:59:59', 'DD-MON-YYYY HH24:MI:SS')

P2P Cycle - PO, Invoice and Payment Details

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