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

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

  1. Query to list Supplier Bank, Bank Branch, Bank Account information
  2. SQL Query to find out Duplicate Customer Contacts
  3. SQL Query to list out Responsibilities assigned to a Request Group
  4. SQL Query to findout information about a Concurrent Request
  5. OPM Quality – Query to List Samples Information
  6. OPM Quality – Query to List Specification and Tests Information
  7. OPM Quality – Query to List Test Codes Information
Tags:
OracleAppsDNA.com