Journal Attachment 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 Attachments query when it is executed.
The GL Wand Journal Attachments query allows customers to join their custom column SQL statements toany fields from the following tables:
- gl_je_headers (aliased as b)
- gl_je_batches (aliased as c)
In addition, the following fields (aliased as a), sourced from the fnd_attached_documents, fnd_documents_vl, fnd_documents_long_text and fnd_documents_short_text tables/views, can be joined to:
- fnd_attached_documents.seq_num
- fnd_documents_vl.datatype_name
- fnd_documents_vl.description
- fnd_documents_vl.media_id
- fnd_documents_vl.datatype_id
- fnd_documents_vl.creation_date
- fnd_documents_vl.last_update_date
- fnd_documents_vl.last_updated_by
- fnd_attached_documents.attached_document_id
- fnd_documents_short_text.short_text
- fnd_documents_long_text.long_text
Test the Default Journal Query
The main journal attachments query selects a number of columns from the journal header and documents tables. For example:
SELECT b.je_header_id,
a.seq_num, etc...
FROM gl_je_headers b,
gl_je_batches c,
(SELECT gjh.je_header_id,
fad.seq_num,
fdo.datatype_name,
fdo.description,
fdo.media_id,
fdo.datatype_id,
fdo.creation_date,
fdo.created_by,
fdo.last_update_date,
fdo.last_updated_by,
fad.attached_document_id ,
fdo.title ,
DECODE(fdo.datatype_id, 5, fdo.url, 6, fdo.file_name, NULL) source ,
fdst.short_text ,
TO_CHAR(fdlt.long_text) long_text
FROM gl_je_headers gjh ,
fnd_attached_documents fad ,
fnd_documents_vl fdo ,
fnd_documents_long_text fdlt ,
fnd_documents_short_text fdst
WHERE gjh.je_header_id = :1
AND fad.entity_name = 'GL_JE_HEADERS'
AND fad.pk1_value = gjh.je_batch_id
AND fad.pk2_value = gjh.je_header_id
AND fdo.document_id = fad.document_id
AND fdo.datatype_id IN (1, 2, 5, 6)
AND fdlt.media_id (+) = fdo.media_id
AND fdst.media_id (+) = fdo.media_id
) a
WHERE b.je_header_id = a.je_header_id
AND c.je_batch_id = b.je_batch_id;
You will need to construct a simple SQL statement using data from your system which returns an example journal attachment 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 document category for the journal attachment:
(SELECT fdv.category_description
FROM fnd_attached_documents fad, fnd_documents_vl fdv
WHERE fad.attached_document_id = a.attached_document_id AND fdv.document_id = fad.document_id) document_category
Test Custom Column Query
Once you have a valid custom drilldown column SQL statement, merge it into the main GL Wand Journal Attachment Query in order to test it and confirm that it will process correctly inside GL Wand. For example, we would merge the document category example into the main journal query as follows:
SELECT b.je_header_id,
(SELECT fdv.category_description
FROM fnd_attached_documents fad, fnd_documents_vl fdv
WHERE fad.attached_document_id = a.attached_document_id AND fdv.document_id = fad.document_id) document_category
FROM gl_je_headers b,
gl_je_batches c,
(SELECT gjh.je_header_id, fad.attached_document_id,
etc...
WHERE gjh.je_header_id = :1
AND fad.entity_name = 'GL_JE_HEADERS'
AND fad.pk1_value = gjh.je_batch_id
AND fad.pk2_value = gjh.je_header_id
AND fdo.document_id = fad.document_id
AND fdo.datatype_id IN (1, 2, 5, 6)
AND fdlt.media_id (+) = fdo.media_id
AND fdst.media_id (+) = fdo.media_id
) a
WHERE b.je_header_id = a.je_header_id
AND c.je_batch_id = b.je_batch_id;
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.