Script to get Payables Invoice Approval Status:

CREATE OR REPLACE FUNCTION APPS.XXAP_INVOICE_STATUS
       (p_invoice_id NUMBER)
   RETURN VARCHAR2
IS
   v_inv_status  VARCHAR2 (500);
BEGIN
   SELECT DECODE
  (AP_INVOICES_UTILITY_PKG.GET_APPROVAL_STATUS
  (aia.invoice_id,
     aia.invoice_amount,
     aia.payment_status_flag,
     aia.invoice_type_lookup_code),
     'FULL', 'Fully Applied',
     'NEVER APPROVED', 'Never Validated',
     'NEEDS REAPPROVAL', 'Needs Revalidation',
     'CANCELLED', 'Cancelled',
     'UNPAID', 'Unpaid',
     'AVAILABLE', 'Available',
     'UNAPPROVED', 'Unvalidated',
     'APPROVED', 'Validated',
    'PERMANENT', 'Permanent Prepayment',
     NULL)

     INTO v_inv_status
     FROM ap_invoices_all aia
    WHERE 1=1
      AND aia.invoice_id = p_invoice_id;

   RETURN (v_inv_status);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN NULL;
END;
/

Query to Get VENDOR, PO, INVOICE AND PAYMENT DETAILS

--Query to Get VENDOR, PO, INVOICE AND PAYMENT DETAILS
SELECT DISTINCT a.org_id
      ,e.segment1 VENDOR_NUMBER
      ,e.vendor_name
      ,UPPER (e.vendor_type_lookup_code) VENDOR_TYPE
     ,f.vendor_site_code
     ,TO_CHAR (TRUNC(d.creation_date)) PO_DATE
     ,d.segment1 PO_NUM
     ,d.type_lookup_code PO_TYPE
     ,c.quantity_ordered QTY_ORDERED
     ,c.quantity_cancelled QTY_CANCELLED
     ,g.item_description ITEM_DESCRIPTION
     ,g.unit_price UNIT_PRICE
     ,((NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0))
      * NVL (g.unit_price, 0)) PO_LINE_AMOUNT
     ,(SELECT DECODE (ph.approved_flag,'Y', 'Approved')
         FROM po.po_headers_all ph
        WHERE ph.po_header_id = d.po_header_id) PO_APPROVED
     ,a.invoice_type_lookup_code INVOICE_TYPE
     ,a.invoice_amount INVOICE_AMOUNT
     ,TO_CHAR (TRUNC (a.invoice_date)) INVOICE_DATE
     ,a.invoice_num INVOICE_NUMBER
     ,(SELECT DECODE (x.match_status_flag,'A', 'Approved')
         FROM ap.ap_invoice_distributions_all x
        WHERE x.invoice_distribution_id = b.invoice_distribution_id) INVOICE_APPROVED
     ,a.amount_paid
     ,h.check_id
     ,i.check_number
     ,h.invoice_payment_id
     ,TO_CHAR (TRUNC(i.check_date)) PAYMENT_DATE
FROM ap_invoices_all a
    ,ap_invoice_distributions_all b
    ,po_distributions_all c
    ,po_headers_all d
    ,ap_suppliers e
    ,ap_supplier_sites_all f
    ,po_lines_all g
    ,ap_invoice_payments_all h
    ,ap_checks_all i
WHERE a.invoice_id = b.invoice_id
  AND b.po_distribution_id = c.po_distribution_id(+)
  AND c.po_header_id = d.po_header_id(+)
  AND e.vendor_id(+) = d.vendor_id
  AND f.vendor_site_id(+) = d.vendor_site_id
  AND d.po_header_id = g.po_header_id
  AND c.po_line_id = g.po_line_id
  AND a.invoice_id = h.invoice_id
  AND h.check_id = i.check_id
  AND c.po_header_id IS NOT NULL
  AND a.payment_status_flag = 'Y'
  AND d.type_lookup_code != 'BLANKET'
  AND EXISTS (SELECT 1
      FROM hr_operating_units
     WHERE 1=1
       AND organization_id = d.org_id
       AND business_group_id = 81)
ORDER BY E.VENDOR_NAME

Query to Check Period Status for GL/PO/AP/AR/FA Applications

--Query to Check Period Status for GL/PO/AP/AR/FA Applications
SELECT (SELECT sob.NAME
          FROM gl_sets_of_books sob
         WHERE sob.set_of_books_id = a.set_of_books_id) ledger_Name
      ,a.period_name Period_Name
      ,a.period_num Period_Num
      ,a.gl_status GL_Period_Status
      ,b.po_status PO_period_Status
      ,c.ap_status AP_period_Status
      ,d.ar_status AR_period_Status
      ,e.fa_status FA_period_Status
 FROM (SELECT period_name
             ,period_num
             ,DECODE (closing_status,
            'O', 'Open',
            'C', 'Closed',
            'F', 'Future',
            'N', 'Never', closing_status) gl_status
     ,set_of_books_id
 FROM gl_period_statuses
WHERE application_id = 101
  AND UPPER (period_name) = UPPER (:PERIOD_NAME)
  AND set_of_books_id =  :P_SOB_ID) a,
    (SELECT period_name,
            DECODE (closing_status,
            'O', 'Open',
            'C', 'Closed',
            'F', 'Future',
            'N', 'Never',closing_status) po_status
           ,set_of_books_id
    FROM gl_period_statuses
    WHERE application_id = 201
    AND UPPER (period_name) = UPPER (:PERIOD_NAME)
    AND set_of_books_id = :P_SOB_ID ) b,
        (SELECT period_name,
                DECODE (closing_status,
            'O', 'Open',
            'C', 'Closed',
            'F', 'Future',
            'N', 'Never',closing_status) ap_status
           ,set_of_books_id
      FROM gl_period_statuses
     WHERE application_id = 200
       AND UPPER (period_name) = UPPER (:PERIOD_NAME)
       AND set_of_books_id = :P_SOB_ID ) c,
        (SELECT period_name,
         DECODE (closing_status,
            'O', 'Open',
            'C', 'Closed',
            'F', 'Future',
            'N', 'Never',
            closing_status) ar_status
           ,set_of_books_id
      FROM gl_period_statuses
     WHERE application_id = 222
       AND UPPER (period_name) = UPPER (:PERIOD_NAME)
       AND set_of_books_id = :P_SOB_ID) d,
    (SELECT fdp.period_name,
            DECODE (fdp.period_close_date,
            NULL,'Open','Closed') fa_status
          ,fbc.set_of_books_id
    FROM fa_book_controls fbc
        ,fa_deprn_periods fdp
    WHERE fbc.set_of_books_id = :P_SOB_ID
    AND fbc.book_type_code = fdp.book_type_code
    AND UPPER (fdp.period_name) = UPPER (:PERIOD_NAME)) e
WHERE a.period_name = b.period_name(+)
  AND a.period_name = c.period_name(+)
  AND a.period_name = d.period_name(+)
  AND a.period_name = e.period_name(+)
  AND a.set_of_books_id=b.set_of_books_id(+)
  AND a.set_of_books_id=c.set_of_books_id(+)
  AND a.set_of_books_id=d.set_of_books_id(+)
  AND a.set_of_books_id=e.set_of_books_id(+)
ORDER BY 1

Query to get Prepay apply and Standard Invoice information

--Query to get Prepay apply and Standard Invoice information
SELECT pv.vendor_name
      ,aia.invoice_num
      ,aia.invoice_id
      ,aia.invoice_amount
      ,ail.line_number Invoice_Line_Num
      ,prepay.invoice_id Prepay_Invoice_Id
      ,prepay.invoice_num Prepay_Invoice_Num
      ,ail.prepay_line_number Prepay_Invoice_Line_Num
      ,(-1)*(ail.amount - NVL(ail.included_tax_amount,0)) Prepay_Amt_Applied
      ,NULLIF((-1)*(NVL(ail.total_rec_tax_amount, 0) +     
       NVL(ail.total_nrec_tax_amount, 0)), 0) Prepay_Tax_amount_Applied
  FROM AP_INVOICES_ALL aia
      ,AP_INVOICES_ALL prepay
      ,AP_INVOICE_LINES_ALL ail
      ,AP_SUPPLIERS pv
WHERE aia.invoice_id = ail.invoice_id
  AND prepay.invoice_id = ail.prepay_invoice_id
  AND ail.amount < 0
  AND NVL(ail.discarded_flag,'N') <> 'Y'
  AND ail.line_type_lookup_code = 'PREPAY'
  AND aia.vendor_id = pv.vendor_id
  AND aia.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')
  AND aia.invoice_id = 641876;

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
  );

Query to find Bank, Bank Account and Bank Branches Information

SELECT hou.name Operating_Unit,
       cbb.bank_name,
       cbb.BANK_NUMBER,
       cba.bank_account_num,
       cba.bank_account_name,
       cba.account_classification,
       cbb.bank_branch_number Routing_NUmber,
       cbb.bank_branch_name,
       cbb.bank_branch_type,
       cba.multi_currency_allowed_flag,
       cba.zero_amount_allowed,
       cbb.eft_swift_code BIC_Code,
       gcf.concatenated_segments
  FROM ce_bank_accounts cba,
       ce_bank_acct_uses_all cbau,
       cefv_bank_branches cbb,
       hr_operating_units hou,
       gl_code_combinations_kfv gcf
 WHERE     1 = 1
       AND cba.bank_account_id = cbau.bank_account_id
       AND cba.bank_branch_id = cbb.bank_branch_id
       AND hou.organization_id = cbau.org_id
       AND cba.asset_code_combination_id = gcf.code_combination_id
       AND (cba.end_date IS NULL OR cba.end_date >= TRUNC (SYSDATE))
       AND (hou.business_group_id = :P_BG_ID OR :P_BG_ID IS NULL)

Query to Link Between AP and GL with XLA tables in R12

SELECT GCC.CODE_COMBINATION_ID,
            GJH.JE_HEADER_ID,
            XDL.AE_HEADER_ID,
            XDL.AE_LINE_NUM,
            XTE.SOURCE_ID_INT_1,
            XTE.APPLICATION_ID,
            XTE.ENTITY_ID,
            GJH.JE_SOURCE,
            GJH.JE_CATEGORY,
            AIA.GL_DATE,
            AP.VENDOR_NAME,
            AP.SEGMENT1 SUPPLIER_NO,
            XDL.EVENT_CLASS_CODE EVENT_CLASS,
            AIA.INVOICE_ID,
           AIDA.INVOICE_DISTRIBUTION_ID,
           AIA.INVOICE_NUM TRANSACTION_NUMBER,
           AIA.INVOICE_DATE,
           INITCAP (GJL.DESCRIPTION) DESCRIPTION,
           GJL.ACCOUNTED_DR DEBIT,
           GJL.ACCOUNTED_CR CREDIT
           --NVL (Jl.Accounted_Dr, 0) –  NVL(Jl.Accounted_Cr, 0) Net_Amount
  FROM GL_JE_HEADERS  GJH,
           GL_JE_LINES GJL,
           GL_CODE_COMBINATIONS GCC,
           GL_IMPORT_REFERENCES GIR,
           XLA.XLA_AE_LINES XAL,
           XLA.XLA_AE_HEADERS XAH,
           XLA.XLA_DISTRIBUTION_LINKS XDL,
           AP_INVOICES_ALL AIA,
           AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
           AP_SUPPLIERS AP,
           XLA_EVENTS XE,
           XLA_TRANSACTION_ENTITIES XTE
WHERE 1=1
    AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
    AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
    AND GIR.JE_HEADER_ID = GJL.JE_HEADER_ID
    AND GIR.JE_LINE_NUM = GJL.JE_LINE_NUM
    AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
    AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
    AND XAL.APPLICATION_ID = XAH.APPLICATION_ID
    AND XAH.APPLICATION_ID = XE.APPLICATION_ID
    AND XAH.EVENT_ID = XE.EVENT_ID
    AND XE.APPLICATION_ID = XTE.APPLICATION_ID(+)
    AND XE.ENTITY_ID = XTE.ENTITY_ID(+)
    AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
    AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
    AND XDL.APPLIED_TO_SOURCE_ID_NUM_1 = AIA.INVOICE_ID
    AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AIDA.INVOICE_DISTRIBUTION_ID
    AND AIDA.INVOICE_ID = AIA.INVOICE_ID
    AND AIA.VENDOR_ID = AP.VENDOR_ID
    AND AIDA.LINE_TYPE_LOOKUP_CODE = 'ITEM'
    AND AIDA.ACCOUNTING_DATE BETWEEN :STARTDATE AND :ENDDATE
    --AND GCC.CODE_COMBINATION_ID = 6429
ORDER BY AIA.GL_DATE DESC

Query to Find Suppliers With Banking Details

SELECT ap.segment1 Vendor_number,       
              ap.vendor_name,
            aps.vendor_site_code,
            aps.ADDRESS_LINE1,
            aps.ADDRESS_LINE2,
            aps.ADDRESS_LINE3,
            aps.CITY ,
            aps.STATE ,
            aps.country,
            (select tax_reference
               from hz_parties
              where party_id = ap.party_id) Tax_Registration_No,
            ieb.bank_name,
            ieb.Bank_Number,
            ieba.bank_account_name,
            ieba.bank_account_num,
            ieba.iban iban_Number,
            iebb.bank_branch_name,
            iebb.eft_swift_code,
            iebb.branch_number,
            iebb.bank_branch_type,
            ieb.country
  FROM ap_suppliers ap,
           ap_supplier_sites_all aps,
           iby_ext_bank_accounts ieba,
           iby_account_owners iao,
           iby_ext_banks_v ieb,
           iby_ext_bank_branches_v iebb
 WHERE 1=1
   AND ap.vendor_id = aps.vendor_id
   AND iao.account_owner_party_id = ap.party_id
   AND ieba.ext_bank_account_id = iao.ext_bank_account_id
   AND ieb.bank_party_id = iebb.bank_party_id
   AND ieba.branch_id = iebb.branch_party_id
   AND ieba.bank_id = ieb.bank_party_id
   AND iao.primary_flag = 'Y'