Journal 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 Journal query when it is executed.

The GL Wand Journal query allows customers to join their custom column SQL statements to any fields from the following tables:

GL_JE_HEADERS (aliased as b)
GL_JE_LINES (aliased as l)
GL_CODE_COMBINATIONS (aliased as c)

Important: These tables contain replicated data and have been built from Oracle ERP Cloud PVO’s and not direct table replications. This has been done for replication performance reasons. JournalHeaderPVO
JournalLinePVO
CodeCombinationPVO

Test the Default Journal Query

The main journal query selects a number of columns from the journal headers and lines tables. For example:

Copy

SELECT b.je_header_id,
l.je_line_num,
etc...
FROM gl_je_headers b, gl_je_lines l, gl_code_combinations c
WHERE b.ledger_id = 1
AND etc...

You will need to construct a simple SQL statement using data from your system which returns an example journal to use for testing your custom column SQL.

Create Custom Column SQL Statement

Now you simply need to create the SQL to return the field that you are after and construct it as a subquery that can be merged into the main SQL statement and correctly joins to one or more fields that are available in the main query. For example, the SQL below fetches the username of the user that last updated the journal:

Copy

(SELECT u.user_name
FROM fnd_user u
WHERE u.user_id = b.last_updated_by) last_updated_by_name

Test Custom Column Query

Once you have a valid custom drilldown column SQL statement, merge it into the main GL Wand Journal Query in order to test it and confirm that it will process correctly inside GL Wand. For example, we would merge the last updated by example into the main journal query as follows:

Copy

SELECT b.je_header_id,
l.je_line_num,
etc...,
(SELECT u.user_name
FROM fnd_user u
WHERE u.user_id = b.last_updated_by) last_updated_by_name
FROM gl_je_headers b, gl_je_lines l, gl_code_combinations c
WHERE b.ledger_id = 1
AND etc...

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.

Available Columns for Joins

GL_JE_HEADERS

B.ACCRUAL_REV_CHANGE_SIGN_FLAG

B.ACCRUAL_REV_EFFECTIVE_DATE

B.ACCRUAL_REV_FLAG

B.ACCRUAL_REV_JE_HEADER_ID

B.ACCRUAL_REV_PERIOD_NAME

B.ACCRUAL_REV_STATUS

B.ACTUAL_FLAG

B.BALANCED_JE_FLAG

B.BALANCING_SEGMENT_VALUE

B.BUDGET_VERSION_ID

B.CLOSE_ACCT_SEQ_ASSIGN_ID

B.CLOSE_ACCT_SEQ_VALUE

B.CLOSE_ACCT_SEQ_VERSION_ID

B.CONTROL_TOTAL

B.CONVERSION_FLAG

B.CR_BAL_SEG_VALUE

B.CREATED_BY

B.CREATION_DATE

B.CURRENCY_CODE

B.CURRENCY_CONVERSION_DATE

B.CURRENCY_CONVERSION_RATE

B.CURRENCY_CONVERSION_TYPE

B.DATE_CREATED

B.DEFAULT_EFFECTIVE_DATE

B.DESCRIPTION

B.DISPLAY_ALC_JOURNAL_FLAG

B.DOC_SEQUENCE_ID

B.DOC_SEQUENCE_VALUE

B.DR_BAL_SEG_VALUE

B.EARLIEST_POSTABLE_DATE

B.ENCUMBRANCE_TYPE_ID

B.EXTERNAL_REFERENCE

B.FROM_RECURRING_HEADER_ID

B.INTERCOMPANY_MODE

B.JE_BATCH_ID

B.JE_CATEGORY

B.JE_FROM_SLA_FLAG

B.JE_HEADER_ID

B.JE_SOURCE

B.LAST_UPDATE_DATE

B.LAST_UPDATE_LOGIN

B.LAST_UPDATED_BY

B.LEDGER_ID

B.LOCAL_DOC_SEQUENCE_ID

B.LOCAL_DOC_SEQUENCE_VALUE

B.MULTI_BAL_SEG_FLAG

B.MULTI_CURRENCY_FLAG

B.NAME

B.ORIGINATING_BAL_SEG_VALUE

B.PARENT_JE_HEADER_ID

B.PERIOD_NAME

B.POST_CURRENCY_CODE

B.POST_MULTI_CURRENCY_FLAG

B.POSTED_DATE

B.POSTING_ACCT_SEQ_ASSIGN_ID

B.POSTING_ACCT_SEQ_VALUE

B.POSTING_ACCT_SEQ_VERSION_ID

B.REFERENCE_DATE

B.REVERSED_JE_HEADER_ID

B.RUNNING_TOTAL_ACCOUNTED_CR

B.RUNNING_TOTAL_ACCOUNTED_DR

B.RUNNING_TOTAL_CR

B.RUNNING_TOTAL_DR

B.STATUS

B.TAX_STATUS_CODE

B.TAX_LEGAL_ENTITY_ID

GL_JE_LINES

L.ACCOUNTED_CR

L.ACCOUNTED_DR

L.AMOUNT_INCLUDES_TAX_FLAG

L.CODE_COMBINATION_ID

L.CO_PROCESSED_FLAG

L.CO_THIRD_PARTY

L.CREATED_BY

L.CREATION_DATE

L.CURRENCY_CODE

L.CURRENCY_CONVERSION_DATE

L.CURRENCY_CONVERSION_RATE

L.CURRENCY_CONVERSION_TYPE

L.DESCRIPTION

L.EFFECTIVE_DATE

L.ENTERED_CR

L.ENTERED_DR

L.GL_SL_LINK_ID

L.GL_SL_LINK_TABLE

L.IGNORE_RATE_FLAG

L.INVOICE_AMOUNT

L.INVOICE_DATE

L.INVOICE_IDENTIFIER

L.JE_HEADER_ID

L.JE_LINE_NUM

L.LAST_UPDATED_BY

L.LAST_UPDATE_DATE

L.LAST_UPDATE_LOGIN

L.LEDGER_ID

L.LINE_TYPE_CODE

L.NO1

L.PERIOD_NAME

L.REFERENCE_1

L.REFERENCE_10

L.REFERENCE_2

L.REFERENCE_3

L.REFERENCE_4

L.REFERENCE_5

L.REFERENCE_6

L.REFERENCE_7

L.REFERENCE_8

L.REFERENCE_9

L.STATUS

L.STAT_AMOUNT

L.SUBLEDGER_DOC_SEQUENCE_ID

L.SUBLEDGER_DOC_SEQUENCE_VALUE

L.TAXABLE_LINE_FLAG

L.TAX_CODE

L.TAX_CODE_ID

L.TAX_CUSTOMER_NAME

L.TAX_CUSTOMER_REFERENCE

L.TAX_DOCUMENT_DATE

L.TAX_DOCUMENT_IDENTIFIER

L.TAX_GROUP_ID

L.TAX_LINE_FLAG

L.TAX_REGISTRATION_NUMBER

L.TAX_ROUNDING_RULE_CODE

L.TAX_TYPE_CODE

L.USSGL_TRANSACTION_CODE

GL_CODE_COMBINATIONS

C.account_type

C.allocation_create_flag

C.alternate_code_combination_id

C.chart_of_accounts_id

C.code_combination_id

C.company_cost_center_org_id

C.detail_budgeting_allowed_flag

C.detail_posting_allowed_flag

C.enabled_flag

C.end_date_active

C.financial_category

C.igi_balanced_budget_flag

C.jgzz_recon_context

C.jgzz_recon_flag

C.last_update_date

C.last_updated_by

C.ledger_segment

C.ledger_type_code

C.preserve_flag

C.reference1

C.reference2

C.reference3

C.reference4

C.reference5

C.refresh_flag

C.revaluation_id

C.segment_attribute1

C.segment_attribute10

C.segment_attribute11

C.segment_attribute12

C.segment_attribute13

C.segment_attribute14

C.segment_attribute15

C.segment_attribute16

C.segment_attribute17

C.segment_attribute18

C.segment_attribute19

C.segment_attribute2

C.segment_attribute20

C.segment_attribute21

C.segment_attribute22

C.segment_attribute23

C.segment_attribute24

C.segment_attribute25

C.segment_attribute26

C.segment_attribute27

C.segment_attribute28

C.segment_attribute29

C.segment_attribute3

C.segment_attribute30

C.segment_attribute31

C.segment_attribute32

C.segment_attribute33

C.segment_attribute34

C.segment_attribute35

C.segment_attribute36

C.segment_attribute37

C.segment_attribute38

C.segment_attribute39

C.segment_attribute4

C.segment_attribute40

C.segment_attribute41

C.segment_attribute42

C.segment_attribute5

C.segment_attribute6

C.segment_attribute7

C.segment_attribute8

C.segment_attribute9

C.segment1

C.segment10

C.segment11

C.segment12

C.segment13

C.segment14

C.segment15

C.segment16

C.segment17

C.segment18

C.segment19

C.segment2

C.segment20

C.segment21

C.segment22

C.segment23

C.segment24

C.segment25

C.segment26

C.segment27

C.segment28

C.segment29

C.segment3

C.segment30

C.segment4

C.segment5

C.segment6

C.segment7

C.segment8

C.segment9

C.start_date_active

C.summary_flag

C.template_id

Journal Custom Columns

Building a Custom Drilldown Column Query

Test the Default Journal Query

Create Custom Column SQL Statement

Test Custom Column Query

Available Columns for Joins

GL_JE_HEADERS

GL_CODE_COMBINATIONS

Was this article helpful?