Query to get Account Payables Invoice Payment Details

--Query to get PAYMENT_REFERENCE_NUMBER and other Info
SELECT xx.*
FROM IBY_PAYMENTS_ALL xx
WHERE payment_id IN
  (SELECT idp.payment_id
    FROM IBY_DOCS_PAYABLE_ALL idp
    WHERE idp.calling_app_id = 200 --SQLAP Application ID
    AND
      (calling_app_doc_unique_ref1
      ,calling_app_doc_unique_ref2)
      IN
      (SELECT TO_CHAR(aps.checkrun_id) checkrun_id
             ,TO_CHAR(aps.invoice_id)
         FROM ap_payment_schedules_all aps
        WHERE aps.invoice_id = 61332--:P_INV_ID
        UNION
       SELECT TO_CHAR(NVL(ac.checkrun_id, ac.check_id)) checkrun_id
             ,TO_CHAR(aip.invoice_id)
        FROM ap_invoice_payments_all aip
            ,ap_checks_all ac
       WHERE aip.invoice_id = 61332--:P_INV_ID
         AND aip.check_id = ac.check_id
      )
  );
 
--Query to get payment document info
SELECT xx.*
  FROM IBY_PAY_INSTRUCTIONS_ALL xx
 WHERE payment_instruction_id
    IN
  (SELECT ipa.payment_instruction_id
     FROM IBY_DOCS_PAYABLE_ALL idp
         ,IBY_PAYMENTS_ALL ipa
    WHERE idp.calling_app_id = 200 --SQLAP Application ID
      AND ipa.payment_id = idp.payment_id
      AND (calling_app_doc_unique_ref1
          ,calling_app_doc_unique_ref2
          )
       IN(SELECT TO_CHAR(aps.checkrun_id) checkrun_id
                ,TO_CHAR(aps.invoice_id)
            FROM ap_payment_schedules_all aps
           WHERE aps.invoice_id = 61332--:P_INV_ID
        UNION
          SELECT TO_CHAR(NVL(ac.checkrun_id, ac.check_id)) checkrun_id
                ,TO_CHAR(aip.invoice_id)
            FROM ap_invoice_payments_all aip
                ,ap_checks_all ac
           WHERE aip.invoice_id = 61332--:P_INV_ID
             AND aip.check_id = ac.check_id
          )
     ); 

--Query to get Payment Doc Info
SELECT xx.*
FROM AP_DOCUMENTS_PAYABLE xx
WHERE calling_app_id = 200
AND calling_app_doc_unique_ref2 = 61332--:P_INV_ID;

--Query to get Accounts Pay Amount
SELECT DISTINCT fnd_flex_ext.get_segs('SQLGL','GL#', '101',
  aip.accts_pay_code_combination_id) "Accts Pay Account"
  ,aip.*
FROM AP_INVOICE_PAYMENTS_ALL aip,
     AP_INVOICE_PAYMENTS_ALL aip2
WHERE aip2.check_id = aip.check_id
  AND aip2.invoice_id = 61332--:P_INV_ID
ORDER BY aip.check_id ASC,
  aip.invoice_payment_id ASC;
 
--Query to get Payment & Bank Information
SELECT *
FROM AP_CHECKS_ALL
WHERE check_id IN
  (SELECT check_id
    FROM  AP_INVOICE_PAYMENTS_ALL
    WHERE invoice_id = 61332--:P_INV_ID
  );
 
--Query to get Pay Dist Info
SELECT tab.*
FROM AP_INVOICE_PAYMENTS_ALL aip,
  AP_PAYMENT_DISTRIBUTIONS_ALL tab
WHERE aip.invoice_payment_id = tab.invoice_payment_id
AND aip.invoice_id = 61332--:P_INV_ID;

--Query to get PAy History
SELECT *
FROM AP_PAYMENT_HISTORY_ALL
WHERE check_id IN
  (SELECT DISTINCT check_id
    FROM AP_INVOICE_PAYMENTS_ALL
    WHERE invoice_id = 61332--:P_INV_ID
  )
ORDER BY accounting_event_id ASC;
 
--Query to get Payment History and Dist Info
SELECT DISTINCT aphd.*
FROM ap_payment_hist_dists aphd,
  AP_PAYMENT_HISTORY_ALL aph
WHERE aph.payment_history_id = aphd.payment_history_id
AND aph.check_id IN
  (SELECT DISTINCT check_id
    FROM AP_INVOICE_PAYMENTS_ALL
    WHERE invoice_id = 61332--:P_INV_ID
  )
ORDER BY
  aphd.payment_history_id ASC;
 
--Query to get Invoie selection info in Batch Payment
SELECT *
FROM AP_INV_SELECTION_CRITERIA_ALL

WHERE checkrun_name IN
  (SELECT checkrun_name
    FROM AP_CHECKS_ALL
    WHERE check_id IN
      (SELECT check_id
        FROM AP_INVOICE_PAYMENTS_ALL
        WHERE invoice_id = 61332--:P_INV_ID
      )
  );
 
--Query to getSelected  Invoie info in Batch Payment
SELECT *
FROM AP_SELECTED_INVOICES_ALL
WHERE invoice_id = 61332--:P_INV_ID;

-- 
SELECT *
  FROM AP_RECON_DISTRIBUTIONS_ALL
 WHERE check_id
    IN (SELECT check_id
          FROM AP_INVOICE_PAYMENTS_ALL
         WHERE invoice_id = :P_INVOICE_ID
       );
 
--Query to get selected inv and pay info
SELECT asic.*
FROM AP_SELECTED_INVOICE_CHECKS_ALL asic,
  AP_SELECTED_INVOICES_ALL asi
WHERE asi.invoice_id = 61332--:P_INV_ID
AND
  (asic.selected_check_id  = asi.pay_selected_check_id
  OR asic.selected_check_id = print_selected_check_id
  );