Find AP invoice Data in AP, XLA and GL tables by using Invoice_ID

AP:

Find AP Invoice Summary Data:

SELECT ai.invoice_id,
  SUBSTR(ai.invoice_num,1,25) invoice_num,
  SUBSTR(aps.vendor_name,1,25) vendor_name,
  ai.invoice_date,
  ai.invoice_amount,
  ai.base_amount,
  SUBSTR(ai.invoice_type_lookup_code,1,15) invoice_type_lookup_code,
  SUBSTR(ai.invoice_currency_code,1,3) invoice_currency_code,
  SUBSTR(ai.payment_currency_code,1,3) payment_currency_code,
  ai.legal_entity_id,
  ai.org_id
FROM AP_INVOICES_ALL ai,
  AP_SUPPLIERS aps,
  AP_SUPPLIER_SITES_ALL avs
WHERE ai.invoice_id   = '&Invoice_ID'
AND ai.vendor_id      = aps.vendor_id(+)
and AI.VENDOR_SITE_ID = AVS.VENDOR_SITE_ID(+)

ORDER BY ai.invoice_id ASC;

Find AP Invoice Lines Summary:

SELECT LINE_NUMBER,
  LINE_TYPE_LOOKUP_CODE,
  LINE_SOURCE,
  ACCOUNTING_DATE,
  PERIOD_NAME,
  AMOUNT,
  SUMMARY_TAX_LINE_ID,
  DEFERRED_ACCTG_FLAG,
  ORG_ID
FROM AP_INVOICE_LINES_ALL
where INVOICE_ID = '&Invoice_ID'
ORDER BY LINE_NUMBER ASC;

Find AP Invoice Distributions Summary:

SELECT INVOICE_ID,
  INVOICE_LINE_NUMBER,
  SUBSTR(DISTRIBUTION_LINE_NUMBER,1,8) DISTRIBUTION_LINE_NUMBER,
  SUBSTR(LINE_TYPE_LOOKUP_CODE,1,9) LINE_TYPE_LOOKUP_CODE,
  ACCOUNTING_DATE,
  PERIOD_NAME,
  AMOUNT,
  BASE_AMOUNT,
  POSTED_FLAG,
  MATCH_STATUS_FLAG,
  ENCUMBERED_FLAG,
  HISTORICAL_FLAG,
  SUBSTR(DIST_CODE_COMBINATION_ID,1,15) DIST_CODE_COMBINATION_ID,
  SUBSTR(ACCOUNTING_EVENT_ID,1,15) ACCOUNTING_EVENT_ID,
  SUBSTR(BC_EVENT_ID,1,15) BC_EVENT_ID,
  SUBSTR(INVOICE_DISTRIBUTION_ID,1,15) INVOICE_DISTRIBUTION_ID,
  SUBSTR(PARENT_REVERSAL_ID,1,15) PARENT_REVERSAL_ID,
  SUBSTR(PO_DISTRIBUTION_ID,1,15) PO_DISTRIBUTION_ID,
  SUMMARY_TAX_LINE_ID,
  DETAIL_TAX_DIST_ID,
  ORG_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_ID = '&Invoice_ID'
order by INVOICE_LINE_NUMBER,
  DISTRIBUTION_LINE_NUMBER ASC;

XLA

Find AP Invoice data in XLA_EVENTS table:

SELECT DISTINCT XE.*
FROM AP_INVOICES_ALL AI ,
  XLA_EVENTS XE ,
  XLA.XLA_TRANSACTION_ENTITIES XTE
WHERE XTE.APPLICATION_ID        =200
AND XE.APPLICATION_ID           =200
AND AI.INVOICE_ID               ='&Invoice_ID'
AND XTE.LEDGER_ID               =AI.SET_OF_BOOKS_ID
AND XTE.ENTITY_CODE             ='AP_INVOICES'
AND NVL(XTE.SOURCE_ID_INT_1,-99)=AI.INVOICE_ID
AND XTE.ENTITY_ID               =XE.ENTITY_ID
order by XE.ENTITY_ID ,
  XE.EVENT_NUMBER;

Find AP Invoice data in XLA_AE_HEADERS table:

SELECT DISTINCT XEH.*
FROM XLA_AE_HEADERS XEH ,
  AP_INVOICES_ALL AI ,
  XLA_EVENTS XE ,
  XLA.XLA_TRANSACTION_ENTITIES XTE
WHERE XTE.APPLICATION_ID        =200
AND XEH.APPLICATION_ID          =200
AND XE.APPLICATION_ID           =200
AND XE.ENTITY_ID                =XTE.ENTITY_ID
AND XE.EVENT_ID                 =XEH.EVENT_ID
AND XTE.ENTITY_ID               =XEH.ENTITY_ID
AND AI.INVOICE_ID               ='&Invoice_ID'
AND XTE.LEDGER_ID               =AI.SET_OF_BOOKS_ID
AND XTE.ENTITY_CODE             ='AP_INVOICES'
AND NVL(XTE.SOURCE_ID_INT_1,-99)=AI.INVOICE_ID
order by XEH.EVENT_ID ,
  XEH.AE_HEADER_ID ASC;

Find AP Invoice data in XLA_AE_LINES table:

 select distinct XEL.*
  FROM XLA_AE_LINES XEL ,
  XLA_AE_HEADERS XEH ,
  AP_INVOICES_ALL AI ,
  XLA_EVENTS XE ,
  XLA.XLA_TRANSACTION_ENTITIES XTE
WHERE XTE.APPLICATION_ID        =200
AND XEL.APPLICATION_ID          =200
AND XEH.APPLICATION_ID          =200
AND XE.APPLICATION_ID           =200
AND AI.INVOICE_ID               ='&Invoice_ID'
AND XE.ENTITY_ID                =XTE.ENTITY_ID
AND XE.EVENT_ID                 =XEH.EVENT_ID
AND XEL.AE_HEADER_ID            =XEH.AE_HEADER_ID
AND XTE.ENTITY_CODE             ='AP_INVOICES'
AND NVL(XTE.SOURCE_ID_INT_1,-99)=AI.INVOICE_ID
AND XTE.ENTITY_ID               =XEH.ENTITY_ID
AND XTE.LEDGER_ID               =AI.SET_OF_BOOKS_ID
order by XEL.AE_HEADER_ID ,
  XEL.AE_LINE_NUM ASC;

General Ledger:

Find AP Invoice Data in GL_JE_BATCHES table:

SELECT DISTINCT GJB.*
FROM GL_IMPORT_REFERENCES GIR,
  GL_JE_BATCHES GJB,
  XLA_AE_LINES AEL,
  XLA_AE_HEADERS AEH,
  XLA_EVENTS AEA
WHERE AEA.EVENT_ID IN
  (SELECT AID.ACCOUNTING_EVENT_ID
  FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
  WHERE AID.INVOICE_ID = '&Invoice_ID'
  )
AND AEL.GL_SL_LINK_ID    = GIR.GL_SL_LINK_ID
AND AEL.GL_SL_LINK_TABLE =GIR.GL_SL_LINK_TABLE
AND AEA.APPLICATION_ID   = 200
AND AEH.APPLICATION_ID   = 200
AND AEL.APPLICATION_ID   = 200
AND AEA.EVENT_ID         = AEH.EVENT_ID
AND AEH.AE_HEADER_ID     = AEL.AE_HEADER_ID
AND GJB.JE_BATCH_ID      = GIR.JE_BATCH_ID;

Find AP Invoice data in GL_JE_HEDAERS Table:

SELECT DISTINCT GJH.*
FROM GL_IMPORT_REFERENCES GIR,
  GL_JE_HEADERS GJH,
  XLA_AE_LINES AEL,
  XLA_AE_HEADERS AEH,
  XLA_EVENTS AEA
WHERE AEA.EVENT_ID IN
  (SELECT AID.ACCOUNTING_EVENT_ID
  FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
  WHERE AID.INVOICE_ID = '&Invoice_ID'
  )
AND AEL.GL_SL_LINK_ID    = GIR.GL_SL_LINK_ID
AND AEL.GL_SL_LINK_TABLE =GIR.GL_SL_LINK_TABLE
AND AEA.APPLICATION_ID   = 200
AND AEH.APPLICATION_ID   = 200
AND AEL.APPLICATION_ID   = 200
AND AEA.EVENT_ID         = AEH.EVENT_ID
and AEH.AE_HEADER_ID     = AEL.AE_HEADER_ID
AND GJH.JE_HEADER_ID     = GIR.JE_HEADER_ID;

Find AP Invoice Data in GL_JE_LINES Table:

SELECT DISTINCT GLL.*
FROM GL_IMPORT_REFERENCES GIR,
  GL_JE_LINES GLL,
  XLA_AE_LINES AEL,
  XLA_AE_HEADERS AEH,
  XLA_EVENTS AEA
WHERE AEA.EVENT_ID IN
  (SELECT AID.ACCOUNTING_EVENT_ID
  FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
  WHERE AID.INVOICE_ID = '&Invoice_ID'
  )
AND AEL.GL_SL_LINK_ID    = GIR.GL_SL_LINK_ID
AND AEL.GL_SL_LINK_TABLE =GIR.GL_SL_LINK_TABLE
AND AEA.APPLICATION_ID   = 200
AND AEH.APPLICATION_ID   = 200
AND AEL.APPLICATION_ID   = 200
AND AEA.EVENT_ID         = AEH.EVENT_ID
AND AEH.AE_HEADER_ID     = AEL.AE_HEADER_ID
and GLL.JE_HEADER_ID     = GIR.JE_HEADER_ID
AND GLL.JE_LINE_NUM      = GIR.JE_LINE_NUM;

Find AP Invoice Data in GL_IMPORT_REFERENCES Table:

SELECT DISTINCT GIR.*
FROM GL_IMPORT_REFERENCES GIR,
  XLA_AE_LINES AEL,
  XLA_AE_HEADERS AEH,
  XLA_EVENTS AEA
WHERE AEA.EVENT_ID IN
  (SELECT AID.ACCOUNTING_EVENT_ID
  FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
  WHERE AID.INVOICE_ID = '&Invoice_ID'
  )
AND AEL.GL_SL_LINK_ID    = GIR.GL_SL_LINK_ID
AND AEL.GL_SL_LINK_TABLE =GIR.GL_SL_LINK_TABLE
AND AEA.APPLICATION_ID   = 200
AND AEH.APPLICATION_ID   = 200
AND AEL.APPLICATION_ID   = 200
and AEA.EVENT_ID         = AEH.EVENT_ID
AND AEH.AE_HEADER_ID     = AEL.AE_HEADER_ID;

Find The Account Code Combinations used for a specific AP Invoice:

SELECT DISTINCT GCC.*
FROM GL_CODE_COMBINATIONS GCC
WHERE GCC.CODE_COMBINATION_ID IN
  ( SELECT DISTINCT XEL.CODE_COMBINATION_ID
  FROM XLA_AE_LINES XEL ,
    XLA_AE_HEADERS XEH ,
    AP_INVOICES_ALL AI ,
    XLA.XLA_TRANSACTION_ENTITIES XTE
  WHERE XTE.APPLICATION_ID        =200
  AND XEH.APPLICATION_ID          =200
  AND XEL.APPLICATION_ID          =200
  AND AI.INVOICE_ID               ='1317327'
  AND XEL.AE_HEADER_ID            =XEH.AE_HEADER_ID
  AND XTE.ENTITY_CODE             ='AP_INVOICES'
  AND NVL(XTE.SOURCE_ID_INT_1,-99)=AI.INVOICE_ID
  AND XTE.LEDGER_ID               =AI.SET_OF_BOOKS_ID
  AND XTE.ENTITY_ID               =XEH.ENTITY_ID
  UNION ALL
  SELECT DISTINCT XEL.CODE_COMBINATION_ID
  FROM XLA_AE_LINES XEL ,
    XLA_AE_HEADERS XEH ,
    AP_INVOICE_PAYMENTS_ALL AIP ,
    XLA.XLA_TRANSACTION_ENTITIES XTE
  WHERE XTE.APPLICATION_ID        =200
  AND XEL.APPLICATION_ID          =200
  AND XEH.APPLICATION_ID          =200
  AND AIP.INVOICE_ID              ='1317327'
  AND XEL.AE_HEADER_ID            =XEH.AE_HEADER_ID
  AND XTE.ENTITY_CODE             ='AP_PAYMENTS'
  AND XTE.LEDGER_ID               =AIP.SET_OF_BOOKS_ID
  AND NVL(XTE.SOURCE_ID_INT_1,-99)=AIP.CHECK_ID
  AND XTE.ENTITY_ID               =XEH.ENTITY_ID
  UNION ALL
  SELECT DISTINCT PO.CODE_COMBINATION_ID
  FROM AP_INVOICE_DISTRIBUTIONS_ALL AID ,
    PO_DISTRIBUTIONS_ALL PO
  WHERE AID.INVOICE_ID        ='1317327'
  AND AID.PO_DISTRIBUTION_ID IS NOT NULL
  and PO.PO_DISTRIBUTION_ID   =AID.PO_DISTRIBUTION_ID
  );

List of Tables In Oracle Financials for India Payables

Following is the list of Tables used to get all the information about India Localization Payables Transactions.

1. AP_INVOICES_ALL
AP Invoice Details for the given Invoice

2. AP_INVOICE_LINES_ALL
AP Invoice Lines Details for Passed AP invoice

3. AP_INVOICE_DISTRIBUTIONS_ALL
AP Invoice Distributions Details for Passed invoice

4. JAI_AP_INVOICE_LINES
India Local AP invoice lines with tax details

5. JAI_AP_TDS_INVOICES
- Details of Supplier Credit memo and Standard Invoice on Tax Authority generated on Validation of Invoice
- Details of amount of prepayment applied and amount reversed through RTN generated.
- Details of Source Attribute used for tax type identification TDS/WCT: TDS-Attribute1,WCT-Attribute 2,ESI-Attribute 3

6. JAI_AP_TDS_PAYMENTS
Invoice will get updated to this table only if Challan Details are filled for the Invoice

7. JAI_AP_TDS_INV_PAYMENTS
Invoice will get updated to this table only if India Process TDS Payments got run

8. JAI_AP_TDS_F16_DTLS_ALL
Invoice will get updated to this table only if India Generate TDS concurrent got run

9. JAI_AP_TDS_YEARS
Setups pertaining to tds year definition

10. JAI_RGM_DEFINITIONS
Shows details of various regimes Registrations.(vat,service tax, tds,tcs,excise)

11. JAI_RGM_REGISTRATIONS
Shows details of tan and pan Table shows details of various regimes.(vat,service tax, tds,tcs,excise)

12. JAI_RGM_PARTIES
Shows details various regimes.(vat,service tax, tds,tcs,excise) Assigned at Organization (header level details)

13. JAI_CMN_VENDOR_SITES
Supplier addtional information details

14. JAI_AP_TDS_VENDOR_HDRS
Supplier addtional information TDS details

15. JAI_CMN_TAXES_ALL
JAI_cmn_taxes_all for TDS Tax attached in the Inv Dist Tax definition details, for the tax id attached at invoice distribution

16. JAI_AP_TDS_THHOLD_HDRS
TDS Thresholds Setup

17. JAI_AP_TDS_THHOLD_TYPES
TDS Thresholds Types Setup

18. JAI_AP_TDS_THHOLD_SLABS
TDS Thresholds Slabs Setup

19. JAI_AP_TDS_THHOLD_TAXES
TDS Thresholds Taxes Setup

20. JAI_AP_TDS_THHOLD_XCEPS
TDS Thresholds Exception Setup

21. JAI_AP_TDS_THHOLD_GRPS
TDS Thresholds Groups Information

22. JAI_AP_TDS_THHOLD_TRXS
TDS Thresholds Transactions

23. JAI_AP_TDS_INV_TAXES
TDS Thresholds Transactions Taxes

24. JAI_AP_TDS_PREPAYMENTS
Table we can get Prepayment application details (details of SI and Prepayemnt tax id should exist for both and section code should be same)

25. JAI_AP_TDS_INV_CANCELS
TDS Cancellations

26. JAI_AP_TDS_THGRP_AUDITS
TDS Threshold Audits,(details of transaction group id, in this we can get details of threshold calculation)

27. JAI_RGM_TRX_REFS
This table will be populated only when the invoice is accounted and the concurrent program India Service Tax Processing is run

28. JAI_RGM_TRX_RECORDS
This table will be populated when the invoice is paid and the concurrent program India Service Tax Processing is run

29. JAI_AP_MATCH_ERS_T
Stores Invoice details of ERS invoices, so that taxes can be carried from Purchasing to Payables. This table is used for bulk propagation of taxes by concurrent India - to insert taxes by pay on receipt.

30. JAI_RCV_LINES
Get RCV lines of the receipt from the ap invoice

31. JAI_RCV_LINE_TAXES
This Table holds the Information of the Receipt Taxes.

32. JAI_RCV_TRANSACTIONS
Gets IL transactionslines for the receipt

33. JAI_PO_LINE_LOCATIONS
Po details FROM AP DISTRIBUTIONS

34. JAI_PO_TAXES
Gets PO taxes Details for AP Invoice

35. JAI_CMN_JOURNAL_ENTRIES
Stores Accounting Entries Details

36. JAI_CMN_DOCUMENT_TAXES
This table stores the taxes for various India Localization transactions

37. JAI_AP_MATCH_INV_TAXES
This table stores the details of all the TDS taxes that are applicable on an invoice distribution line. This includes, TDS, WCT and ESSI taxes, attached to the 3 segments of the invoice distribution DFF

Parent Asset functionality in Oracle Assets

Parent asset functionality is used, to group all the subcomponent assets of a major (parent) asset for ease of processing and reporting.

At the time of entering a sub-component of a parent asset, enter the number of parent asset to which the new asset belongs to.

Based on the rule provided in Asset category and parent asset life, Oracle Assets defaults the life for a subcomponent asset. User can select either of;

·         Same End Date (Without specifying a minimum life): Subcomponent asset’s life is remaining life period of the parent asset

·         Same End Date (Specifying a minimum life): Subcomponent asset’s life is remaining life period of the parent asset or the minimum life provided, whichever is more

·         Same Life: Subcomponent asset’s life is actual life period of parent asset. In this scenario, subcomponent asset will continue the depreciation, even after the parent asset is fully depreciated

·         None: There is no connection between the life of the subcomponent asset and the parent asset life. Oracle Assets defaults the subcomponent asset life from the asset category.

As per the life of the subcomponent assets as defaulted above, depreciation rates need to be defined in the system.

When a transaction has been performed on parent asset, the transaction will not be performed automatically on the subcomponent asset, except mass retirements where in user can select automatically retire subcomponents along with the parent asset.

User has to manually do the same transactions on the subcomponent asset, if they are applicable to subcomponent asset as well.

 

Reports available:

Parent Asset Transactions Report: Lists all the transactions performed on parent assets in a particular asset book

Parent Asset Report: Extracts all the parent assets along with its subcomponent assets

 

Uses of parent assets

·         Defaulting the subcomponent asset’s life

·         Reporting purpose

·         Retiring subcomponent assets at the parent asset retiring through mass retirement

FA Account Generation when using SLA

In R12, We have an option to select whether to use FA Account Generator Workflow or not. In case, we wish not to use FA Account Generator workflow, Sub Ledger Accounting (SLA) will generate FA accounts.

The profile option to control this is:
FA: Use Workflow Account Generation. This should be 'Yes' (By Default 'No' if it is null) to use workflow Account Generator.

Based on this profile option, the FA Account Generator Workflow is conditional, while SLA is always used.

This post explains how SLA works in case of the profile option FA: Use Workflow Account Generation is set to No, which means FA Account Generator is not being used.

Note: This view is in context when Payables Code Combination is also null along with code combination generated by FA Account Generator workflow.

Oracle provides default SLA Application Accounting Definition setup. If this meets the requirements, SLA customization is not required and we can use the seeded AAD.

SLA Application Accounting Definition looks like following.

AAD is setup for different Event Classes and Event Types which has Journal Line Definition Assignments. To see the sla rules for particular event class, for example: 'Additions', place the cursor on desired event class and click on Journal Line Definition Tab.

JLD has different Journal Line Assignments, For Example: 'Assets Addition Cost' which has Account Derivation Rules. ADRs can be for all segments or we can create rules for individual segments based on the requirements. Here, the Rule Name and Description shows which is the source to derive account segments.

If you see, for Natural Account segment, rule name and description says, the source is Asset Categories window and for Balancing Segment, Asset Assignments window and for all other segments, source is Book Controls window.

To see the rule in detail, place the cursor on desired ADR rule(For Example: Natural Account Segment field), and click on Account Derivation Rule tab.

Here, we can create the conditions for ADR, where the ADR works only if the conditions are met.  In this case, the ADR derives the Account segment from Asset Categories window to build account code combination when the code combination generated by FA Account Generator workflow is null.

We can also map the segment values by using the value type as Mapping Type, where we can provide constant values or Input-Output segments mapping according to the requirements.


Default Sources when using Oracle Seeded AAD:
---------------------------------------------------------------------------------

Following image represents the default sources when we are using the default SLA setup provided by Oracle.

This shows different Event classes/types in first level, account types/journal line types in second level and default sources to derive segment values for generating the Account code combinations in third level.

Purpose and usage of Tax Rate Variance Account

Tax Rate Variance reflects the tax variance between the invoice and the PO distributions due to difference in tax applicability.

Tax Rate Variance is normally computed for the Non recoverable portion of the Tax. It is computed in two cases:

a) When there is a difference in the Tax amount of the PO and Invoice.
For Example: PO# 122046
Lines Amount: 14.49
Tax Amount: 1.16
Total Amount: 15.65

-----------------------------------


AP invoice# 135324
Item Amount: 14.49
Freight Amount: 14.62
Tax Amount: 0
Total Amount: 29.11
Tax Rate Variance: 1.16

In this case, Tax is calculated on PO# 122046 which is 1.16. But, on invoice there is no tax calculated. Hence, the Tax Rate Variance account recorded the difference in tax amount 1.16.

b) When there is a difference in the Taxes computed (for instance an extra tax calculated on Invoice and the same did not exist in PO).

For Example: On PO# 121985
Lines Amount: 57.62
Tax Amount: 4.60 (State Level Tax: 2.30 + County level Tax: 2.30)
Total amount: 62.22

----------------------------------------

On AP Invoice# FJ17597
Item Amount: 57.62
Freight Amount: 13.71
Tax Amount: 5.70 (State Level Tax: 2.85 + County Level Tax: 2.85)
Total Amount: 77.03

Here the applicable tax rate is 4 and State Level and County level tax is calculated in following way.

Tax for Items+Freight (57.62+13.71) = 71.33 * 0.04 = 2.85
If tax is calculated only for Freight: 13.71 * 0.04 = 0.54
Difference of Total tax and Freight Tax (2.85 - 0.54) = 2.31 which has 0.01 difference from the tax on PO.

Hence, Tax Rate Variance account has recorded the tax variance amount on Invoice Distributions as 0.01

PPR Document Validation error

This post explains the reason for following PPR Document validation error:

PPR Status: Failed Document Validation
Validation Error: Payment Profile on document is not compatible with payment format on document.

Cause of the error: This error occurs when the Payment Format assigned to the Payment Process Request  is different from the Payment Format assigned to Supplier Site.

How to fix the issue:
1. Make sure to use Payment Process Profile which has same Payment Format  as Supplier Site.
(Or)
2. Change the Payment Format on supplier site same as Payment Process Profile being used.

 

Process to update Payment Format for a Supplier Site:

Responsibility: Payables Super User

Navigation: Suppliers → Entry

Query the form with Supplier Name/Number.

1.  Click on Payment Details

2.  Click on Update Payment Details under Supplier Site

3.  Click on Payment Specifications Tab

4.  Update Payee Specified Payment Format

Payment Terms Defaulting Order

According to Order Management Defaulting Rules, system uses following hierarchy to default the Payment terms on Sales Order.

1. Sales Agreement Header
2. Customer Bill To (Invoice To)
3. Customer Shp To
4. Customer

This order can be changed by using following navigation.

1. Order Management responsibility->Setup->Rules->Defaulting
2. Query for Application : Order Management & Entity : Order Header
3. Select Attribute : Payment Term
4. Click on 'Defaulting Rules' button
5. Click on the Defaulting Condition which is Enabled
6. Under Default Sourcing Rules, you can find/change the hierarchy



Receivables uses the following hierarchy to determine the default payment terms, stopping when one is found:

customer Bill-To site level
customer address level
customer level
Transaction Type