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)
Test the Default Journal Query
The main journal query selects a number of columns from the journal headers and lines tables. For example:
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 user name of the user that last updated the journal:
(SELECT u.user_name
FROM fnd_user u
WHERE u.user_id = b.last_updated_by) last_updated_by_name
Test Custom Column SLA 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:
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. Follow the instructions which can be found here – Add a Custom Drilldown Column.