Oracle SLA Tables

Given below are the tables to be referred when an invoice has been created in Payables and accounted either online or using create accounting program.

1. select * from xla_transaction_entities where source_id_int_1=<invoice_id>

Few of the columns explained:
source_id_int_1 = invoice_id
entity_code='AP_INVOICES'
security_id_int_1=org_id
transaction_number=invoice number

get the entity_id from xla_transaction_entities

2. select * from xla_events where entity_id=<entity_id>

Few of the columns explained:
event_type_code can be 'INVOICE VALIDATED','CREDIT MEMO VALIDATED','PAYMENT CREATED' etc
event_status_code can be 'P' processed 'U' unprocessed etc
'P' when the accounting is done in 'Final' or 'Final Post' mode.
'U' when the accounting is done in 'Draft' mode.
process_status_code can 'D' for draft 'P' for processed etc

get the event_id from xla_events

3. select * from xla_ae_headers where event_id=<event_id>

gl_transfer_status_code will be 'Y' when accounting done in 'Final' mode, where accounting
entries are transferred to GL.
gl_transfer_status_code will be 'N' when accounting done in 'Draft' mode.

get ae_header_id from xla_ae_headers

4. select * from xla_ae_lines where ae_header_id=<ae_header_id>

5. select * from xla_distribution_link where ae_header_id=<ae_header_id>

source_distribution_id_num_1 will have the invoice distribution id

Oracle R12 SLA Supporting References

Supporting References:
This is in continuation of the SLA setup I mentioned in my previous post.
Oracle R12 SLA setup with simple example

Supporting references can be defined to store additional information on the subledger journal header or line level. The additional information could be transaction information like invoice type or line type, or accounting information or any supplier information etc. Supporting references can then be used for finding the subledger balance for the defined reference for a given period.

We shall see the process of creating a supporting reference and assigning it to journal line definition, creating an invoice and checking the supporting reference in the subledger journal entries after accounting. The below mentioned supporting reference is for AP Invoice Distribution Type.

Define Supporting Reference:

Subledger Accounting Setup --> Accounting Methods Builder --> Journal Entry Setup --> Supporting References

Click on Create.

Give the code as XX_DISTTYPE_SR, name as XX Dist Type SR, description as ‘Supp Ref for distribution line type’. Select ‘Enabled’ flag and ‘Maintain Balances’ which will enable us to check the subledger balance for the Supporting reference. Select year end carry forward as ‘Never’.

 

Click on ‘Add Detail’ in the ‘Supporting Reference Detail’ section.

Upto five details can be added to a supporting reference. Give the Code as ‘DISTTYPE’, name as ‘Distribution Type’.

 

 

Click on ‘Assign Sources’. In the search section, select application name as ‘Payables’ and source name as ‘Invoice Distribution Type’. Click on Go, in the search results select ‘Invoices’ in the Event Class Name. Click on ‘Apply’.

 

 

 

 

Assign Supporting Reference to the Journal Line Definition.

Subledger Accounting Setup --> Accounting Methods Builder --> Methods and Definition --> Journal Lines Definition

Query for the Journal Line Definition for the event class ‘Invoices’, in the journal line type select the ‘Liability’ . Go to the supporting references tab and assign the supporting reference ‘XX Dist Type SR’.

 

 

Validate the Application Accounting Definition.

Create an invoice, validate and do accounting.

Check the journal lines created.

 

 

 

Click on the supporting references for the Liability

Check the supporting reference details. Supporting reference has been created with Detail Name as 'Distribution Type' and value as 'Item', which is the invoice distribution line type.

 

 

 

Supporting Reference Balances

We can check the balances for the supporting reference in the Supporting Reference Balances. Run the ‘Subledger Accounting Balances Update’ program.

Subledger Accounting -> Supporting Reference Balances

Query for the supporting reference