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)
These tables are not directly replicated from the ERP Cloud database but rather based on the PVO’s JournalEntryLinePVO and JournalImportReferenceExtractPVO.
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_tableCreate 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_typeColumns available for Joining
XLA_JOURNAL_LINE_ENTRY
XLA.AE_HEADER_ID
XLA.AE_LINE_NUM
XLA.CL_ACCT_SEQ_HEADER_NAME
XLA.CL_ACCT_SEQ_VERSION_NAME
XLA.DOC_SEQUENCE_NAME
XLA.ENTITY_CODE
XLA.EVENT_CLASS_CODE
XLA.EVENT_TYPE_CODE
XLA.LANGUAGE
XLA.EVENT_TYPE_NAME
XLA.ENCUMBRANCE_TYPE
XLA.LEGAL_ENTITY_IDENTIFIER
XLA.LEGAL_ENTITY_NAME
XLA.DRILLDOWN_PROCEDURE_NAME
XLA.LEGAL_ENTITY_ID
XLA.SECURITY_ID_CHAR_1
XLA.SECURITY_ID_CHAR_2
XLA.SECURITY_ID_CHAR_3
XLA.SECURITY_ID_INT_1
XLA.SECURITY_ID_INT_2
XLA.SECURITY_ID_INT_3
XLA.SOURCE_ID_CHAR_1
XLA.SOURCE_ID_CHAR_2
XLA.SOURCE_ID_CHAR_3
XLA.SOURCE_ID_CHAR_4
XLA.SOURCE_ID_INT_1
XLA.SOURCE_ID_INT_2
XLA.SOURCE_ID_INT_3
XLA.SOURCE_ID_INT_4
XLA.TRANSACTION_NUMBER
XLA.VALUATION_METHOD
XLA.CHART_OF_ACCOUNTS_ID
XLA.LEDGER_NAME
XLA.ACCOUNTING_ENTRY_STATUS_CODE
XLA.ACCOUNTING_ENTRY_TYPE_CODE
XLA.BALANCE_TYPE_CODE
XLA.BUDGET_VERSION_ID
XLA.CLOSE_ACCT_SEQ_VALUE
XLA.CLOSE_ACCT_SEQ_VERSION_ID
XLA.COMPLETED_DATE
XLA.HEADER_CREATION_DATE
XLA.HEADER_DESCRIPTION
XLA.DOC_SEQUENCE_ID
XLA.DOC_SEQUENCE_VALUE
XLA.ENTITY_ID
XLA.EVENT_ID
XLA.FUNDS_STATUS_CODE
XLA.GL_TRANSFER_DATE
XLA.GL_TRANSFER_STATUS_CODE
XLA.JE_CATEGORY_NAME
XLA.LEDGER_ID
XLA.PERIOD_NAME
XLA.PRODUCT_RULE_CODE
XLA.PRODUCT_RULE_TYPE_CODE
XLA.PRODUCT_RULE_VERSION
XLA.REFERENCE_DATE
XLA.ACCOUNTED_CR
XLA.ACCOUNTED_DR
XLA.ACCOUNTING_CLASS_CODE
XLA.ACCOUNTING_DATE
XLA.APPLICATION_ID
XLA.CODE_COMBINATION_ID
XLA.CURRENCY_CODE
XLA.CURRENCY_CONVERSION_DATE
XLA.CURRENCY_CONVERSION_RATE
XLA.CURRENCY_CONVERSION_TYPE
XLA.LINE_DESCRIPTION
XLA.DISPLAYED_LINE_NUMBER
XLA.ENCUMBRANCE_TYPE_ID
XLA.ENTERED_CR
XLA.ENTERED_DR
XLA.GL_SL_LINK_ID
XLA.GL_SL_LINK_TABLE
XLA.JGZZ_RECON_REF
XLA.PARTY_ID
XLA.PARTY_SITE_ID
XLA.PARTY_TYPE_CODE
XLA.SOURCE_ID
XLA.SOURCE_TABLE
XLA.STATISTICAL_AMOUNT
GL_IMPORT_REFERENCES
XLA.JE_BATCH_ID
XLA.JE_HEADER_ID
XLA.JE_LINE_NUM
XLA.REFERENCE_1
XLA.REFERENCE_10
XLA.REFERENCE_2
XLA.REFERENCE_3
XLA.REFERENCE_4
XLA.REFERENCE_5
XLA.REFERENCE_6
XLA.REFERENCE_7
XLA.REFERENCE_8
XLA.REFERENCE_9
XLA.GL_SL_LINK_ID
XLA.GL_SL_LINK_TABLE
XLA.SUBLEDGER_DOC_SEQUENCE_ID
XLA.SUBLEDGER_DOC_SEQUENCE_VALUE
XLA.OBJECT_VERSION_NUMBER
XLA.CREATED_BY
XLA.CREATION_DATE
XLA.LAST_UPDATE_DATE
XLA.LAST_UPDATE_LOGIN
XLAL.AST_UPDATED_BY