Created by Shaun Pendrigh on Apr 22, 2014

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

The GL Wand Sub Ledger query allows customers to join their custom column SQL statements to any fields from the following views:

  • xla_ar_inv_ael_gl_v
  • xla_ar_rec_ael_gl_v
  • xla_ar_adj_ael_gl_v
  • xla_ap_inv_enc_gl_v
  • xla_ap_inv_ael_gl_v
  • xla_ap_pay_ael_gl_v
  • xla_fa_ael_gl_v
  • xla_pa_ei_ael_gl_v
  • xla_pa_dr_ael_gl_v
  • xla_po_ael_gl_v
  • xla_req_enc_ael_gl_v
  • xla_po_enc_ael_gl_v
  • xla_inv_ael_gl_v
  • xla_wip_ael_gl_v
  • xla_fv_tc_gl_v
  • xla_fv_be_gl_v
  • xla_fv_pya_gl_v
  • xla_ozf_cla_ael_gl_v
  • xla_ozf_utl_ael_gl_v

The exact view that is used in the query will depend on the journal source and category that you are drilling down on. The view is always aliased as dv. All the views return the same fields but not all fields are populated on each view. Only those fields applicable for the journal source and category will be populated.

Test the Default Sub Ledger Query

The main sub ledger query selects a number of columns from the applicable drilldown view. For example, an accounts receivable invoice would have a SQL statement as follows:

Icon

SELECT dv.org_id,
dv.entered_dr,
dv.entered_cr,
dv.accounted_dr,
dv.accounted_cr,
etc...
FROM xla_ar_inv_ael_gl_v dv, gl_code_combinations c
WHERE dv.code_combination_id = c.code_combination_id AND dv.application_id = :1 AND dv.je_header_id = :2 AND dv.je_line_num = :3

You will need to construct a simple SQL statement using data from your system which returns an example transaction 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 inventory item description:

Icon

(SELECT msi.description
FROM mtl_system_items msi
WHERE dv.inventory_item_id = msi.inventory_item_id
AND dv.inventory_organization_id = msi.organization_id) item_description

Test Custom Column SLA Query

Once you have a valid custom drilldown column SQL statement, merge it into the main GL Wand Sub Ledger Query in order to test it and confirm that it will process correctly inside GL Wand. For example, we would merge the inventory item description example into the main sub ledger query as follows:

Icon

SELECT dv.org_id,
dv.entered_dr,
dv.entered_cr,
dv.accounted_dr,
dv.accounted_cr,
etc...,
(SELECT msi.description
FROM mtl_system_items msi
WHERE dv.inventory_item_id = msi.inventory_item_id
AND dv.inventory_organization_id = msi.organization_id) item_description
FROM xla_ar_inv_ael_gl_v dv, gl_code_combinations c
WHERE dv.code_combination_id = c.code_combination_id AND dv.application_id = :1 AND dv.je_header_id = :2 AND dv.je_line_num = :3

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.