Skip to main content

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

Copy
SELECT qrslt.*
FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10) use_hash(lk5,lk7) */
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,
te.security_id_int_1
FROM xla_ae_headers h,
xla_ae_lines l,
xla_events e,
xla.xla_transaction_entities te,
xla_gl_ledgers_v gl,
gl_budget_versions bud,
xle_entity_profiles le,
fnd_user ue,
fnd_application_vl app,
xla_event_types_tl et,
xla_event_classes_tl ec,
xla_lookups lk5,
xla_lookups lk7,
gl_import_references ir
WHERE     gl.ledger_id = h.ledger_id
AND bud.budget_version_id(+) = h.budget_version_id
AND le.legal_entity_id(+) = te.legal_entity_id
AND ue.user_id = e.created_by
AND ec.application_id = et.application_id
AND ec.entity_code = et.entity_code
AND ec.event_class_code = et.event_class_code
AND ec.language = USERENV ('LANG')
AND et.application_id = h.application_id
AND et.entity_code = te.entity_code
AND e.entity_id = te.entity_id
AND e.application_id = te.application_id
AND e.application_id = et.application_id
AND e.event_type_code = et.event_type_code
AND et.event_type_code = h.event_type_code
AND et.language = USERENV ('LANG')
AND app.application_id = h.application_id
AND et.application_id = te.application_id
AND te.application_id = h.application_id
AND te.entity_id = h.entity_id
AND e.event_id = h.event_id
AND e.application_id = h.application_id
AND h.ae_header_id = l.ae_header_id
AND h.application_id = l.application_id
AND lk5.lookup_code = NVL (h.funds_status_code, 'REQUIRED')
AND lk5.lookup_type = 'XLA_FUNDS_STATUS'
AND lk7.lookup_code(+) = l.accounting_class_code
AND lk7.lookup_type(+) = 'XLA_ACCOUNTING_CLASS'
AND l.gl_sl_link_id = ir.gl_sl_link_id
AND l.gl_sl_link_table = ir.gl_sl_link_table
AND ir.je_header_id = :je_header_id
AND ir.je_line_num = :je_line_num) qrslt;


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.

Tip: The main SLA query is aliased qrslt. This is used below to ensure the correct columns are being referenced.

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

Copy
SELECT qrslt.*,
(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
FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10) use_hash(lk5,lk7) */
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,
te.security_id_int_1
FROM xla_ae_headers h,
xla_ae_lines l,
xla_events e,
xla.xla_transaction_entities te,
xla_gl_ledgers_v gl,
gl_budget_versions bud,
xle_entity_profiles le,
fnd_user ue,
fnd_application_vl app,
xla_event_types_tl et,
xla_event_classes_tl ec,
xla_lookups lk5,
xla_lookups lk7,
gl_import_references ir
WHERE     gl.ledger_id = h.ledger_id
AND bud.budget_version_id(+) = h.budget_version_id
AND le.legal_entity_id(+) = te.legal_entity_id
AND ue.user_id = e.created_by
AND ec.application_id = et.application_id
AND ec.entity_code = et.entity_code
AND ec.event_class_code = et.event_class_code
AND ec.language = USERENV ('LANG')
AND et.application_id = h.application_id
AND et.entity_code = te.entity_code
AND e.entity_id = te.entity_id
AND e.application_id = te.application_id
AND e.application_id = et.application_id
AND e.event_type_code = et.event_type_code
AND et.event_type_code = h.event_type_code
AND et.language = USERENV ('LANG')
AND app.application_id = h.application_id
AND et.application_id = te.application_id
AND te.application_id = h.application_id
AND te.entity_id = h.entity_id
AND e.event_id = h.event_id
AND e.application_id = h.application_id
AND h.ae_header_id = l.ae_header_id
AND h.application_id = l.application_id
AND lk5.lookup_code = NVL (h.funds_status_code, 'REQUIRED')
AND lk5.lookup_type = 'XLA_FUNDS_STATUS'
AND lk7.lookup_code(+) = l.accounting_class_code
AND lk7.lookup_type(+) = 'XLA_ACCOUNTING_CLASS'
AND l.gl_sl_link_id = ir.gl_sl_link_id
AND l.gl_sl_link_table = ir.gl_sl_link_table
AND ir.je_header_id = :je_header_id
AND ir.je_line_num = :je_line_num) qrslt;

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.

Was this article helpful?

We're sorry to hear that.