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

General Ledger - Sub ledger - Project Accounting Drill Down

General Ledger - Sub ledger - Project Accounting Drill Down

Well This Is Definitely one of the Complicated One!

Not very Often though but you have a Project which involves Project accounting, and you are working on Reports / or Customization which need an integration form Oracle Projects, General Ledger and Sub-ledger Accounting, you need a very good understanding of how various Database tables of these Different Modules Link with each other.

B Below is a query which can be leveraged to Drill Down or Drill up for the data between

GENERAL LEDGER
SUB-LEDGER ACCOUNTING
PROJECT ACCOUNTING


select
GL.NAME,GJH.JE_HEADER_ID,GJH.JE_CATEGORY,GJH.JE_SOURCE,GJH.NAME,GJH.STATUS, GLL.JE_LINE_NUM,GCC.CONCATENATED_SEGMENTS,XAL.ACCOUNTING_CLASS_CODE,PA.PROJECT_ID,PA.SEGMENT1,PA.NAME,PA.PROJECT_TYPE,PT.TASK_ID,PT.TASK_NUMBER,PT.TASK_NAME,PCD.EXPENDITURE_ITEM_ID,
PE.EXPENDITURE_ITEM_DATE ,
PE.TRANSACTION_SOURCE,
PE.EXPENDITURE_ITEM_DATE ,PE.EXPENDITURE_ITEM_DATE
,888
,PCD.*
--,8,GIR.*,8,XAH.*,8,XAL.*,8,XDL.*,8,PCD.*
from
APPS.GL_JE_HEADERS GJH,
APPS.GL_JE_LINES GLL,
APPS.GL_CODE_COMBINATIONS_KFV GCC,
APPS.GL_IMPORT_REFERENCES GIR,
APPS.GL_LEDGERS GL,
APPS.XLA_AE_LINES XAL,
APPS.XLA_AE_HEADERS XAH,
APPS.XLA_DISTRIBUTION_LINKS XDL,
APPS.PA_COST_DISTRIBUTION_LINES_ALL PCD,
APPS.PA_PROJECTS_ALL PA,
APPS.PA_TASKS PT,
APPS.PA_EXPENDITURE_ITEMS_ALL PE,
APPS.PA_TRANSACTION_SOURCES PTS
WHERE 1=1
AND GJH.LEDGER_ID = GL.LEDGER_ID
AND GJH.JE_HEADER_ID = GLL.JE_HEADER_ID
AND GJH.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GLL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = PCD.EXPENDITURE_ITEM_ID
AND PCD.EXPENDITURE_ITEM_ID = PE.EXPENDITURE_ITEM_ID
AND PE.TRANSACTION_SOURCE = PTS.TRANSACTION_SOURCE
--AND PE.TRANSACTION_SOURCE LIKE 'PA%'
AND PCD.PROJECT_ID = PA.PROJECT_ID
AND PCD.TASK_ID = PT.TASK_ID
AND PA.PROJECT_ID = PT.PROJECT_ID
AND TRUNC(GJH.CREATION_DATE) > TRUNC(SYSDATE-25)
--AND UPPER(GJH.JE_CATEGORY) LIKE UPPER('PROJECT%')
AND UPPER(GJH.JE_SOURCE) LIKE UPPER('Project Accounting%')

General Ledger - Accounts Payables Drill Down - Data Base

General Ledger - Accounts Payables Drill Down - Data Base Query


As Part of Regular GL , Accounts Payables Designing and Troubleshooting, It is required to have a good Understanding of the Links between Various General Ledger and Accounts Payables Table involving Sub-ledger accounting tables, Below is a select statement which provides you the link between GENERAL LEDGER, ACCOUNTS PAYABLES AND  SUB-LEDGER ACCOUNTING Data base Tables

select --distinct aia.invoice_num
GL.NAME,GJH.JE_HEADER_ID,GJH.JE_CATEGORY,GJH.JE_SOURCE,GJH.NAME,GJH.STATUS, GLL.JE_LINE_NUM,GCC.CONCATENATED_SEGMENTS,XAH.GL_TRANSFER_STATUS_CODE,XDL.ROUNDING_CLASS_CODE,XDL.APPLIED_TO_ENTITY_CODE
,AIA.INVOICE_ID,AIA.INVOICE_NUM,AIA.INVOICE_AMOUNT,AIL.LINE_NUMBER,AIL.LINE_TYPE_LOOKUP_CODE,AIL.AMOUNT,GL.CURRENCY_CODE "MRC CURRENCY",GLL.ACCOUNTED_DR,GLL.ACCOUNTED_CR
--,8,GIR.*,8,XAH.*,8,XAL.*,8,XDL.*
from
APPS.GL_JE_HEADERS GJH,
APPS.GL_JE_LINES GLL,
APPS.GL_CODE_COMBINATIONS_KFV GCC,
APPS.GL_IMPORT_REFERENCES GIR,
APPS.GL_LEDGERS GL,
APPS.XLA_AE_LINES XAL,
APPS.XLA_AE_HEADERS XAH,
APPS.XLA_DISTRIBUTION_LINKS XDL,
APPS.AP_INVOICE_DISTRIBUTIONS_ALL AID,
APPS.AP_INVOICE_LINES_ALL AIL,
APPS.AP_INVOICES_ALL AIA
WHERE 1=1
AND GJH.LEDGER_ID = GL.LEDGER_ID
AND GJH.JE_HEADER_ID = GLL.JE_HEADER_ID
AND GJH.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GLL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
AND AID.INVOICE_ID = AIA.INVOICE_ID
AND AIL.INVOICE_ID = AIA.INVOICE_ID
AND AIL.INVOICE_ID = AID.INVOICE_ID
AND AIL.LINE_NUMBER = AID.INVOICE_LINE_NUMBER
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AID.INVOICE_DISTRIBUTION_ID
AND TRUNC(GJH.CREATION_DATE) > TRUNC(SYSDATE-25)
AND UPPER(GJH.JE_SOURCE) LIKE UPPER('Payable%')
AND AIA.INVOICE_NUM LIKE 'EXP360319%'
AND GL.LEDGER_CATEGORY_CODE NOT IN ('ALC','SECONDARY')
order by aia.invoice_num;

General Ledger - Sub-ledger Accounting Dril Down

As Part of regular Designing and Trouble shooting of General Ledger and Subledger Accounting Analysis it is required to have knowledge of Technical Drill Down and Link between GL and Subledger Tables.

Please see the Below mentioned select Statement to Identify relation between various tables for DrillDown from GL To Subledger and Vice Verse.



select
GL.NAME,GJH.JE_HEADER_ID,GJH.JE_CATEGORY,GJH.JE_SOURCE,GJH.NAME,GJH.STATUS, GLL.JE_LINE_NUM,GCC.CONCATENATED_SEGMENTS,XAL.ACCOUNTING_CLASS_CODE,xal.*
,8,GIR.*,8,XAH.*,8,XAL.*,8,XDL.*--,8,PCD.*
from
APPS.GL_JE_HEADERS GJH,
APPS.GL_JE_LINES GLL,
APPS.GL_CODE_COMBINATIONS_KFV GCC,
APPS.GL_IMPORT_REFERENCES GIR,
APPS.GL_LEDGERS GL,
APPS.XLA_AE_LINES XAL,
APPS.XLA_AE_HEADERS XAH,
APPS.XLA_DISTRIBUTION_LINKS XDL
WHERE 1=1
AND GJH.LEDGER_ID = GL.LEDGER_ID
AND GJH.JE_HEADER_ID = GLL.JE_HEADER_ID
AND GJH.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GLL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
--AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = PCD.EXPENDITURE_ITEM_ID
--AND TRUNC(GJH.CREATION_DATE) > TRUNC(SYSDATE-10)
--AND GCC.SEGMENT6 != '000'
--AND UPPER(GJH.JE_CATEGORY) LIKE UPPER('PROJECT%')
--AND UPPER(GJH.JE_SOURCE) LIKE UPPER('Project Accounting%')
and gjh.je_header_id = <je header ID>
and gll.je_line_num =<JE LINE NUMBER>

GL Balances Database Queries

GL Balances Database Queries


Troubleshooting and Understanding GL balances is a important part of any Oracle application Implementation, Support or Design Project.

In Oracle Application R12 The GL Balances are stored in the database table GL_BALANCES
Below are few queries which we can leverage to identify, Actual and Budget Balances.


Actual Balances


SELECT gl.name,cc.concatenated_segments,cc.segment4,bal.period_name,BAL.LEDGER_ID,gl.name,BAL.CURRENCY_CODE,
nvl(sum(bal.begin_balance_dr + bal.period_net_dr - bal.begin_balance_cr - bal.period_net_cr),0) "Actual Balance"
FROM apps.gl_balances bal,
apps.gl_code_combinations_KFV cc,
apps.gl_ledgers gl
WHERE cc.code_combination_id = bal.code_combination_id
and bal.ledger_id = gl.ledger_id
--AND bal.Ledger_id = <'your SOB id'>
AND bal.period_name LIKE 'JAN%2015'
AND bal.actual_flag = 'A'
and gl.ledger_category_code = 'PRIMARY'
--AND bal.currency_code = <'you SOB currency code'>
--and cc.segment1 = '101'
and cc.segment4 = '113113'
--and cc.concatenated_segments in ('202.0000.000000000.251001.0000.000.000000','202.0000.000000000.299000.0000.101.000000')
GROUP by cc.concatenated_segments,cc.segment4,bal.period_name,BAL.LEDGER_ID,gl.name,BAL.CURRENCY_CODE
order by gl.name,cc.concatenated_segments,cc.segment4,bal.period_name,BAL.LEDGER_ID,gl.name,BAL.CURRENCY_CODE;


Actual And Budget Balances


SELECT cc.concatenated_segments,cc.segment2,
nvl(sum(bal.begin_balance_dr + bal.period_net_dr - bal.begin_balance_cr - bal.period_net_cr),0) "Actual Balance",
nvl(sum(balb.begin_balance_dr + balb.period_net_dr - balb.begin_balance_cr - balb.period_net_cr),0) "Budget Balance"
FROM apps.gl_balances bal,
apps.gl_balances balb,
apps.gl_code_combinations_KFV cc,

WHERE cc.code_combination_id = bal.code_combination_id
AND balb.code_combination_id = cc.code_combination_id
AND
--AND bal.Ledger_id = <'your SOB id'>
--AND balb.Ledger_id = <'your SOB id'>
--AND bal.period_name = <'your actual period name'>
--AND balb.period_name = <'your budget period_name'>
AND bal.actual_flag = 'A'
AND balb.actual_flag = 'B'
--AND bal.currency_code = <'you SOB currency code'>
--AND balb.currency_code = <'your SOB currency code'>
and cc.segment1 = '101'
and cc.segment4 = '251011'
GROUP by cc.concatenated_segments,cc.segment2;

Important Accounts Receivables Database Tables

Important Accounts Receivables Tables

In an Accounts Receivables implementation, Support or trouble Shooting it is very important to have information about relevant Database tables, These details come very handy while any analysis of an issue or even technical architecture / solution designing, sharing some of the AR database Tables for reference

AR invoice Transactions

RA_CUSTOMER_TRX_ALL Transaction Header table
RA_CUSTOMER_TRX_LINES_ALL Transaction Lines table along with Tax lines.
RA_CUST_TRX_LINE_GL_DIST_ALL Distribution for Transaction Lines

Transaction Interface Tables

RA_INTERFACE_LINES_ALL Transaction Lines interface
RA_INTERFACE_SALESCREDITS_ALL Transaction Sales credit information
RA_INTERFACE_DISTRIBUTIONS_ALL Transaction Distribution information
RA_INTERFACE_ERRORS_ALL Transaction errors table

Receipt Interface Tables

AR_PAYMENTS_INTERFACE_ALL Interface table to import receipts
AR_INTERIM_CASH_RECEIPTS_ALL Lockbox transfers the receipts that pass validation to the interim tables
AR_INTERIM_CASH_RCPT_LINES_ALL Lockbox transfers the receipts that pass validation to the interim tables


Receipts tables
AR_CASH_RECEIPTS_ALL Cash Receipt Header tables
AR_RECEIVABLE_APPLICATIONS_ALL stores Receipt Application details
AR_PAYMENT_SCHEDULES_ALL This table is updated when an activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, bills receivable
AR_CASH_RECEIPT_HISTORY_ALL This table stores all of the activity that is contained for the life cycle of a receipt.

• Each row represents one step.
• The status field for that row tells you which step the receipt has reached.
• Possible statuses are Approved, Confirmed, Remitted, Cleared, and Reversed.


Customer Tables

HZ_PARTIES A party is an entity that can enter into a business relationship.
HZ_CUST_ACCOUNTS This table stores information about customer/financial relationships established between a Party and the deploying company.
HZ_PARTY_SITES This table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-Specific party information such as a person’s mail stops at their work address.
HZ_CUST_ACCT_SITES_ALL This table stores information about customer/financial account sites information.
HZ_CUST_SITE_USES_ALL This table stores information about the business purposes assigned to a customer account site
HZ_LOCATIONS A location is a point in geographical space described by an address and/or geographical Indicators such as latitude or longitude.


Accounts Receivables Setup tables

RA_CUST_TRX_TYPES_ALL This table stores information about each transaction type for all classes of transactions, for example, invoices, commitments, and credit memos.
AR_RECEIPT_CLASSES This table stores the different receipt classes that you define.
AR_RECEIPT_METHODS This table stores information about Payment Methods, receipt attributes that you define and assign to Receipt Classes to account for receipts and their applications