Accounting Detail Query for AP Invoice

Accounting Detail Query is something you will have to write. Not before or at the time of Go Live, but a few months down the lane your client will ask a report to fetch you the details of accounting of the invoice. Implemented P2P Cycle? You will need the query to get the accounting entries of AP Invoice.

If you look closely, we have used the table xla_transaction_entities to find out the entity_id which was later used to get hold of subledger accounting header table first and sub ledger line details table eventually. Sub ledger accounting line table (XLA_AE_LINES) is the one which contains most of the required information.

In fact finding out the entity_id is the first step in order to find the sub ledger details of any entity,

So For the purpose of finding the AP Invoice accounting details, I am giving the query below which can help you fetch following details:

• Invoice Number
• Invoice Id
• Supplier
• Invoice Creation Date
• Accounting Class Code
• Code combination
• Debited Amount
• Credited Amount
• Accounted Debited Amount
• Accounted Credited Amount

By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...

Accounting Detail Query

select aia.invoice_num,
aia.invoice_id,
hp.party_name as Supplier,
aia.creation_date,
xal.ACCOUNTING_CLASS_CODE,
gcc.segment1||’.’||gcc.segment2||’.’||gcc.segment3||’.’||gcc.segment4||’.’||gcc.segment5||’.’||gcc.segment6||’.’||gcc.segment7 CC,
xal.ENTERED_DR,
xal.ENTERED_CR,
xal.ACCOUNTED_DR,
xal.ACCOUNTED_CR
from ap_invoices_all aia,
poz_suppliers pos ,
hz_parties hp,
xla_transaction_entities xte,
xla_ae_headers xah ,
xla_ae_lines xal ,
gl_code_combinations gcc
where to_char(aia.creation_date,’YYYY’) = :CREATION_YEAR –‘2020’
and pos.vendor_id = aia.vendor_id
and hp.party_Id = pos.party_Id
AND xte.entity_code = ‘AP_INVOICES’
AND xte.SOURCE_ID_INT_1 =aia.invoice_id
AND xah.entity_id = xte.entity_id
AND xal.ae_header_id = xah.ae_header_id
and gcc.code_combination_id = xal.CODE_COMBINATION_ID
and aia.invoice_num = NVL(:INVOICE_NUM,aia.invoice_num)

As you can see that query takes 2 parameters. First Is “Creation Year” and other is “Invoice Number”. It means this query will return you the data of invoices created in a particular year. And if the invoice number parameter is provided, data will be fetched only for that invoice.

Related Posts

Payment Process Request Query
Payment Detail Query
AP Invoice Table
AP Invoice Lines Table
AP Invoice Distributions Table

Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.

Reference:
support.oracle.com

Check out the Amazon Deals of the day based on your interests..