Subledger 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 columns from the following replicated tables to join their custom column SQL statements to:

• XLA_JOURNAL_LINE_ENTRY (alias XLA)
• GL_IMPORT_REFERENCES (alias GIR)

Below we have set out an example to add an accounts receivable column to the drilldown report. Its the AR Adjustment Transaction Type.

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.

Test the Default SLA Query

We have provided a simplified version of the main GL Wand SLA Query. Use it 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.
SELECT
gir.je_header_id,
gir.je_line_num,
xla.gl_sl_link_id,
xla.gl_sl_link_table,
xla.ae_header_id,
xla.ae_line_num,
xla.application_id,
xla.entity_code,
xla.entity_id,
xla.event_class_code,
xla.event_type_code,
xla.event_type_name,
xla.event_id,
xla.accounting_date,
xla.entered_dr,
xla.entered_cr,
xla.accounted_dr,
xla.accounted_cr,
xla.code_combination_id,
xla.currency_code,
xla.currency_conversion_date,
xla.currency_conversion_rate,
xla.accounting_entry_type_code,
xla.ledger_id,
xla.ledger_name,
xla.je_category_name,
xla.gl_transfer_date,
xla.period_name,
xla.gl_transfer_status_code
FROM
gl_import_references gir,
xla_journal_line_entry xla
WHERE
gir.je_header_id = :p_header_id
AND gir.je_line_num = :p_line_number
AND gir.gl_sl_link_id = xla.gl_sl_link_id
AND gir.gl_sl_link_table = xla.gl_sl_link_table

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 Transaction Type column.
This resulted in the following custom drilldown column SQL to retrieve the field:
Transaction Type
/* Transaction Type Column */
(CASE
WHEN (event_class_code = 'RECEIPT' AND entity_code = 'RECEIPTS') THEN
(SELECT type
FROM ar_adjustments_all
WHERE adjustment_id = te.source_id_int1
AND event_class_code = et.event_class_code
AND entity_code = et.entity_code)
ELSE NULL
END) ar_adj_trx_type

Columns available for Joining

XLA_JOURNAL_LINE_ENTRY