R12 Accounts Payable Useful Queries

Get Invoice Details

select aia.invoice_id, aia.invoice_num,gcc.segment1,gcc.concatenated_segments,aia.creation_date, aia.invoice_date,aia.pay_group_lookup_code,aia.source,aia.*
aia.org_id,hou.name,aia.source, to_char(aia.creation_date, 'MM-YYYY'),aia.payment_method_code,aia.invoice_currency_code,aia.pay_group_lookup_code,aia.invoice_num,gcc.segment1,gcc.concatenated_segments
from
apps.ap_invoices_all aia,
apps.ap_invoice_lines_all ail,
apps.ap_invoice_distributions_all aid,
apps.gl_code_combinations_kfv gcc
where
aia.invoice_id = ail.invoice_id
and aia.invoice_id = aid.invoice_id
and ail.line_number = aid.invoice_line_number
and aia.org_id = <ENTER ORG ID>
and aid.dist_code_combination_id = gcc.code_combination_id
--and gcc.segment1 = '<ENTER SEGMENT 1>'
--and aia.source = '<ENTER SOURCE>'
and aia.cancelled_date is null
and aia.payment_status_flag = 'Y'
and aia.invoice_num in ('<ENTER INVOIC NUMBER>')
and aia.creation_date >trunc(sysdate-10) -- For invoices created in some time in past

;




Get the Invoice and Payment Details (For Paid Invoices)

select
aia.org_id,aia.invoice_id,aia.vendor_id,aca.check_number, aia.invoice_num,aia.invoice_amount,aia.amount_paid,aia.discount_amount_taken, aca.amount "payment_amount",gcc.concatenated_segments,aia.creation_date, aia.invoice_date, aia.payment_method_code, aba.bank_account_name,aca.check_id
from
apps.ap_invoices_all aia,
apps.ap_invoice_lines_all ail,
apps.ap_invoice_distributions_all aid,
apps.gl_code_combinations_kfv gcc,
apps.gl_code_combinations_kfv gcc1,
apps.ap_invoice_payments_all aip,
apps.ap_checks_all aca
,apps.ce_bank_accounts aba
,apps.CE_BANK_ACCT_USES_ALL abu
,apps.hr_operating_units hou

where
aia.invoice_id = ail.invoice_id
and aia.invoice_id = aid.invoice_id
and ail.line_number = aid.invoice_line_number
and aip.invoice_id = aia.invoice_id
and aip.check_id = aca.check_id
and aid.dist_code_combination_id = gcc1.code_combination_id
and aca.CE_BANK_ACCT_USE_ID = abu.BANK_ACCT_USE_ID
and abu.bank_account_id = aba.bank_account_id
and aia.org_id = hou.organization_id
and aia.org_id = <ORG_ID>
and aba.asset_code_combination_id = gcc.code_combination_id
and aia.creation_date >trunc(sysdate-90)
--and gcc.segment1 = <COMPANY>

--and aia.invoice_currency_code = <CURR>
--and aia.payment_method_code = <PAYMENT METHOD>
--and aia.vendor_id in (<ENTER VENDOR IDs>)
and aia.cancelled_date is null
and aia.payment_status_flag = 'Y'
order by aia.org_id, to_char(aia.creation_date, 'MM-YYYY')
;

To Find Invoices on Hold

select aha.* from apps.ap_invoices_all aia,
apps.ap_holds_all aha
where
aia.invoice_id = aha.invoice_id
and aia.org_id = <Enter ORG ID>
and aha.hold_lookup_code = <ENTER HOLD CODE> -- Holds Codes can be found in table : AP_HOLD_CODES
--and aia.VENDOR_SITE_ID in (<ENTER VENDOR SITE ID>)
;

Holds Codes can be found in table: AP_HOLD_CODES

Query to Get Bank account details

select cba.bank_account_type,cbb.bank_name,cba.*
from
apps.ce_bank_accounts cba,
apps.cefv_bank_branches cbb,
apps.CE_BANK_ACCT_USES_ALL cbu
where
cba.bank_branch_id = cbb.bank_branch_id
and cba.bank_account_id = cbu.bank_account_id
AND ( cba.end_date IS NULL OR cba.end_date > TRUNC(SYSDATE) )
;

Bank account and payment template analysis

SELECT xep.name                                       legal_entity_name, 

       ou.name                                        "Operating Unit", 

       cba.bank_account_name                          "Bank Account Name", 

       cba.bank_account_num                           "Bank Account Number", 

       NVL(cba.attribute1, cba.currency_code)         currency, 

       cba.multi_currency_allowed_flag                "Multi Currency Flag", 

       cba.account_classification                     "Account Classification", 

       bb.bank_name                                   "Bank Name", 

       bb.bank_branch_type                            "Bank Branch Type", 

       bb.bank_branch_name                            "Bank Branch Name", 

       bb.bank_branch_number                          "Bank Branch Number", 

       bb.eft_swift_code                              "Swift Code", 

       bau.ap_use_enable_flag                         bank_setup_ap_use_flag, 

       bau.ar_use_enable_flag                         bank_setup_ar_use_flag, 

       cba.iban_number,

       gcf.concatenated_segments       "ASSET GL Code Combination", 

       (SELECT RTRIM(XMLAGG(XMLELEMENT(e, apg.vendor_pay_group|| 

                     ',')) .extract('//text()'), 

                       ',') 

        FROM   (SELECT DISTINCT apg.vendor_pay_group, 

                                bank_account_id 

                FROM   apps.ap_payment_templates apt, 

                       apps.ap_pay_group apg 

                WHERE  1 = 1 

                   AND apg.template_id = apt.template_id 

                   AND NVL(apt.inactive_date, SYSDATE + 1) > SYSDATE) apg 

        WHERE  bank_account_id = cba.bank_account_id) used_in_ap_pay_groups, 

       (SELECT RTRIM(XMLAGG(XMLELEMENT(e, arm.name|| CHR(10)|| 

                     ',')) .extract('//text()'), ',') 

        FROM   apps.ar_receipt_method_accounts_all arma, 

               apps.ar_receipt_methods arm 

        WHERE  arm.receipt_method_id = arma.receipt_method_id 

           AND arma.remit_bank_acct_use_id = bau.bank_acct_use_id) 

                                                      used_in_ar_recipt_mthods 

FROM   apps.ce_bank_accounts cba, 

       apps.ce_bank_acct_uses_all bau, 

       apps.cefv_bank_branches bb, 

       apps.hr_operating_units ou, 

       apps.xle_entity_profiles xep, 

       apps.gl_code_combinations_kfv gcf 

WHERE  cba.bank_account_id = bau.bank_account_id 

   AND cba.bank_branch_id = bb.bank_branch_id 

   AND ou.organization_id = bau.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 ou.default_legal_context_id = xep.legal_entity_id 

ORDER  BY ( cba.bank_account_num ); 

LINK PAYMENT AND INTERNAL BANK ACCOUNT

select 

aisc.checkrun_name,

apt.template_name,

apt.payment_method_code,

app.system_profile_code,

--api.payment_currency_code,

cb.bank_name,

cba.bank_account_name,

cba.currency_code,

cba.multi_currency_allowed_flag,

cba.payment_multi_currency_flag,

cba.zero_amount_allowed,

from

APPS.AP_INV_SELECTION_CRITERIA_all aisc,

apps.ap_checks_all aca,

apps.ap_payment_templates apt,

apps.IBY_ACCT_PMT_PROFILES_B app,

apps.ce_bank_accounts cba,

apps.cefv_bank_branches cb

where 1=1

and aca.checkrun_id= aisc.checkrun_id

and aisc.template_id = apt.template_id

and apt.payment_profile_id = app.payment_profile_id

--and app.payment_profile_id = api.payment_profile_id

and aisc.bank_account_id = cba.bank_account_id

and cba.bank_branch_id = cb.bank_branch_id

--and aisc.org_id = api.org_id

--and app.system_profile_code = <PAYMENT PROCESS PROFILE>

and aca.check_id in (<ENTER CHECK ID>)