Query to list all Documents and their information under a Payment Process Request

Shailender Thallam

8 years ago

This query list all payment documents under a payment batch with detailed invoice and Supplier/Employee information.

/**********************************************************************
 *PURPOSE: Query to list all Documents and their information under a Payment Process Request
 *AUTHOR: Shailender Thallam 
 ***********************************************************************/
SELECT ip.payment_process_request_name,
  invh.invoice_id ,
  invh.vendor_id ,
  invh.invoice_num ,
  invh.invoice_amount ,
  invh.amount_paid ,
  idpa.payment_date ,
  idpa.document_type ,
  idpa.payment_amount ,
  idpa.payment_method_code ,
  idpa.po_number ,
  idpa.document_description ,
  ip.payment_id ,
  ip.payment_service_request_id ,
  ip.payment_instruction_id ,
  ip.paper_document_number ,
  ip.payment_amount ,
  ip.int_bank_number ,
  ip.int_bank_branch_name ,
  ip.int_bank_branch_number ,
  ip.int_bank_account_name ,
  ip.payer_legal_entity_name ,
  ip.org_name ,
  ip.payee_party_name ,
  ip.payee_address_concat ,
  ip.payee_supplier_number ,
  ip.employee_person_id ,
  (SELECT employee_number
  FROM apps.per_all_people_f papf
  WHERE papf.person_id = ip.employee_person_id
  AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date
  ) employee_number,
  ip.employee_payment_flag ,
  iupd.date_used ,
  iupd.document_use ,
  ieba.ext_bank_account_id ,
  ieba.country_code ,
  ieba.bank_account_name ,
  ieba.bank_account_num ,
  hzb.party_id bank_id ,
  hzb.party_name bank_name ,
  hzbb.party_id bank_branch_id ,
  hzbb.party_name bank_branch_name ,
  hzbb.address1 ,
  hzbb.address2 ,
  hzbb.address3 ,
  hzbb.city ,
  cpd.payment_document_id ,
  cpd.payment_doc_category ,
  cpd.payment_document_name ,
  cpd.format_code ,
  cpd.first_available_document_num ,
  cpd.last_available_document_number
FROM apps.ap_invoices_all invh ,
  apps.iby_docs_payable_all idpa ,
  apps.iby_payments_all ip ,
  apps.iby_used_payment_docs iupd ,
  apps.iby_ext_bank_accounts ieba ,
  apps.hz_parties hzb ,
  apps.hz_parties hzbb ,
  apps.ce_payment_documents cpd
WHERE 1                             = 1
AND idpa.calling_app_doc_ref_number = invh.invoice_num
AND idpa.calling_app_doc_unique_ref2= invh.invoice_id
AND idpa.payment_id                 = ip.payment_id
AND ip.paper_document_number        = iupd.used_document_number
AND ip.external_bank_account_id     = ieba.ext_bank_account_id
AND ieba.bank_id                    = hzb.party_id
AND ieba.branch_id                  = hzbb.party_id
AND iupd.payment_document_id        = cpd.payment_document_id
AND ip.payment_process_request_name = 'test - 1';

SELECT aisc.checkrun_name,
  aisc.checkrun_id,
  apt.template_name,
  iapp.system_profile_code,
  aisc.status,
  aisc.*
FROM apps.ap_payment_templates apt,
  apps.iby_acct_pmt_profiles_b iapp,
  apps.ap_inv_selection_criteria_all aisc
WHERE 1                     = 1
AND apt.payment_profile_id  = iapp.payment_profile_id
AND apt.template_id         = aisc.template_id
AND TRUNC(sysdate)         <= TRUNC(NVL(apt.inactive_date,sysdate))
AND TRUNC(sysdate)         <= TRUNC(NVL(iapp.inactive_date,sysdate))
AND iapp.payment_profile_id = aisc.payment_profile_id
  --AND aisc.checkrun_id        = 10137 --p_checkrun_id
AND aisc.checkrun_name = 'A Test 20th June';

For Further Reading