Query to get AP invoice Payment details

AP Invoice payments details query

AP_DOCUMENTS_PAYABLE

SELECT
  *
FROM
  AP_DOCUMENTS_PAYABLE
WHERE
  calling_app_id                = 200
AND calling_app_doc_unique_ref2 = :P_INVOICE_ID;

AP_INVOICE_PAYMENTS_ALL

SELECT DISTINCT
  aip.*,
  fnd_flex_ext.get_segs('SQLGL','GL#', '101' ,
  aip.accts_pay_code_combination_id) "Accts Pay Account"
FROM
  AP_INVOICE_PAYMENTS_ALL aip,
  AP_INVOICE_PAYMENTS_ALL aip2
WHERE
  aip2.check_id     = aip.check_id
AND aip2.invoice_id = :P_INVOICE_ID
ORDER BY
  aip.check_id ASC,
  aip.invoice_payment_id ASC;
  
AP_CHECKS_ALL

SELECT
  *
FROM
  AP_CHECKS_ALL
WHERE
  check_id IN
  (
    SELECT
      check_id
    FROM
      AP_INVOICE_PAYMENTS_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  );
  
  AP_PAYMENT_DISTRIBUTIONS_ALL
  
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       = :P_INVOICE_ID;

AP_PAYMENT_HISTORY_ALL
SELECT
  *
FROM
  AP_PAYMENT_HISTORY_ALL
WHERE
  check_id IN
  (
    SELECT DISTINCT
      check_id
    FROM
      AP_INVOICE_PAYMENTS_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  )
ORDER BY
  accounting_event_id ASC;
  
AP_PAYMENT_HIST_DISTS

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 = :P_INVOICE_ID
  )
ORDER BY
  aphd.payment_history_id ASC;
  
AP_INV_SELECTION_CRITERIA_ALL

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 = :P_INVOICE_ID
      )
  );
  
AP_SELECTED_INVOICES_ALL

SELECT
  *
FROM
  AP_SELECTED_INVOICES_ALL
WHERE
  invoice_id = :P_INVOICE_ID;
  
AP_SELECTED_INVOICE_CHECKS_ALL

SELECT
  asic.*
FROM
  AP_SELECTED_INVOICE_CHECKS_ALL asic,
  AP_SELECTED_INVOICES_ALL asi
WHERE
  asi.invoice_id = :P_INVOICE_ID
AND
  (
    asic.selected_check_id  = asi.pay_selected_check_id
  OR asic.selected_check_id = print_selected_check_id
  );
  
IBY_DOCS_PAYABLE_ALL

SELECT
  *
FROM
  IBY_PAYMENTS_ALL
WHERE
  payment_id IN
  (
    SELECT
      idp.payment_id
    FROM
      IBY_DOCS_PAYABLE_ALL idp
    WHERE
      idp.calling_app_id =200
    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=:P_INVOICE_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 =:P_INVOICE_ID
        AND aip.check_id =ac.check_id
      )
  );
  
  
IBY_PAYMENTS_ALL

SELECT
  *
FROM
  IBY_PAYMENTS_ALL
WHERE
  payment_id IN
  (
    SELECT
      idp.payment_id
    FROM
      IBY_DOCS_PAYABLE_ALL idp
    WHERE
      idp.calling_app_id =200
    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=:P_INVOICE_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 =:P_INVOICE_ID
        AND aip.check_id =ac.check_id
      )
  );
  
  
IBY_PAY_INSTRUCTIONS_ALL

SELECT
  *
FROM
  IBY_PAY_INSTRUCTIONS_ALL
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
    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=:P_INVOICE_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 =:P_INVOICE_ID
        AND aip.check_id =ac.check_id
      )
  );
  
  
AP_RECON_DISTRIBUTIONS_ALL 

SELECT
  *
FROM
  AP_RECON_DISTRIBUTIONS_ALL
WHERE
  check_id IN
  (
    SELECT
      check_id
    FROM
      AP_INVOICE_PAYMENTS_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  );