Subledger SLA Custom Columns

Building a Custom Drilldown Column Query

When a custom column is added to a GL Wand drilldown, the SQL statement for the custom column is merged into the GL Wand SLA query when it is executed.

The GL Wand SLA query allows customers to use any of the following columns to join their custom column SQL statements to:

• l.code_combination_id
• h.ae_header_id
• e.event_id
• h.application_id
• et.event_class_code
• et.entity_code
• l.ae_line_num
• e.event_number
• te.transaction_number
• te.source_id_char_1
• te.source_id_char_2
• te.source_id_char_3
• te.source_id_char_4
• te.source_id_int_1
• te.source_id_int_2
• te.source_id_int_3
• te.source_id_int_4
• h.ledger_id
• te.legal_entity_id
• et.event_type_code
• te.security_id_int_1
• te.security_id_int_2
• te.security_id_int_3
• te.security_id_char_1
• te.security_id_char_2
• te.security_id_char_3
• te.valuation_method
• xlas.drilldown_procedure_name
• ir.je_header_id
• ir.je_line_num
• l.source_table
• l.source_id

From these tables:

• xla_ae_headers h
• xla_ae_lines l
• xla_events e
• xla_transaction_entities te
• xla_event_types_tl et
• xla_subledgers xlas
• gl_import_references ir

Below we have set out an example to add two accounts receivable columns to the drilldown report. They are AR Adjustment Transaction Type and AR Adjustment Comment.

Extract Bind Variable Example Values

To execute the main SQL query we need to provide it with two bind variables, JE_HEADER_ID and JE_LINE_NUM. These values can be retrieved in a number of ways for an example transaction. You can query them using a SQL statement in a utility like Toad or SQL Developer, you can extract them from the Oracle Forms etc. We also have a way of retrieving a set of test values directly from the GL Wand journal drilldown report. This method will be explained below.

Drill down to the journal report using data that is applicable for the custom drilldown column that you are adding e.g. if you are adding a column for Receivables adjustments, then drill on journals from accounts receivable.

Now that we have a journal drilldown report, select the row you would like to use for testing your custom drilldown column. Select the “Drill to Subledger” column (1) and then open the Excel parameters for the function by clicking the fx button in the formula bar (2). In the function arguments window take a note of parameter P3 and P4. P3 is the JE_HEADER_ID and P4 is the JE_LINE_NUM.

Activate the R12 Security Feature

In order to activate the multi-org security in Oracle R12, run the following command using the appropriate application short name:

BEGIN
mo_global.init (p_appl_short_name => 'AR');
END;

Test the Default SLA Query

We have provided a simplified version of the main GL Wand SLA Query here – main_sla_query. Download it in order to test your custom drilldown column SQL. First run this SQL statement using the bind variables retrieved in the previous step and confirm the query returns at least one row.

Create Custom Column SQL Statement

For our example, we worked out that for an Accounts Receivable adjustment the adjustment_id is stored in source_id_int_1 and a join to AR_ADJUSTMENTS_V will provide us with access to the Comments and Transaction Type column.

This resulted in the following custom drilldown column SQL to retrieve the two fields:

Transaction Type
/* Transaction Type Column */
(CASE
WHEN (EVENT_CLASS_CODE = 'ADJUSTMENT' AND ENTITY_CODE = 'ADJUSTMENTS') THEN
(SELECT TRX_TYPE
FROM AR_ADJUSTMENTS_V
WHERE ADJUSTMENT_ID = qrslt.source_id_int_1
AND EVENT_CLASS_CODE = qrslt.event_class_code
AND ENTITY_CODE = qrslt.entity_code
)
ELSE
NULL
END) AR_ADJ_TRX_TYPE

Comments
/* Comments Column */
(CASE
WHEN (EVENT_CLASS_CODE = 'ADJUSTMENT' AND ENTITY_CODE = 'ADJUSTMENTS') THEN
(SELECT COMMENTS
FROM AR_ADJUSTMENTS_V
WHERE ADJUSTMENT_ID = qrslt.source_id_int_1
AND EVENT_CLASS_CODE = qrslt.event_class_code
AND ENTITY_CODE = qrslt.entity_code
)
ELSE
NULL
END) AR_ADJ_COMMENTS

This third example shows how to join to a column that is not selected in the main SLA query. In this example, we want to get the Period Year of the period in which the invoice was posted. The Period is in the xla_ae_headers table, but the Period is not selected in the main SLA query (see list of fields selected above).

In order to do this, we include the xla_ae_headers table in the inline select clause:

Period Year
(SELECT period_year
FROM gl_periods a, xla_ae_headers h1
WHERE period_set_name = 'Accounting'
AND a.period_name = h1.period_name
AND h1.ae_header_id = qrslt.ae_header_id
AND h1.application_id = qrslt.application_id) period_year

Note how xla_ae_headers (h1) is joined to the xla_ae_headers (qrslt) columns in the main query.

Test Custom Column SLA Query

Once you have a valid custom drilldown column SQL statement, merge it into the main GL Wand SLA Query in order to test it and confirm that it will process correctly inside GL Wand. In this example we bind to the GL Wand SLA query using the source_id_int_1, event_class_code and entity_code columns. Download our account receivable adjustment example here – custom_column_sla_query.

Once you have confirmed that the query returns the correct records, along with the additional columns that you are after, you can proceed to add the custom drilldown column and test it from GL Wand. Follow the instructions which can be found here – Add a Custom Drilldown Column.