Oracle EBS SQL: GL Headers and Lines

The following SQL Statement is useful to find information about your GL Journals:

Note, this SQL statement is designed to link attributes 1 and 2 on the GL Journal lines to Projects, as such should be removed for any use elsewhere.

SELECT gjh.je_header_id jnl_hdr_id ,
gjct.user_je_category_name category ,
gjst.user_je_source_name source ,
gjb.status post_status ,
gjb.name batch_name ,
gjh.period_name ,
gjh.name journal_name ,
gjh.external_reference REF ,
gjh.date_created ,
gjh.description ,
gjh.running_total_dr ttl ,
gjh.creation_date ,
fu.description created_by ,
gjl.je_line_num ,
gjl.description line_descr ,
gjl.reference_1 line_ref ,
gjl.reference_5 ap_inv_num ,
gjl.reference_6 line_src ,
gjl.reference_10 line_type ,
GCC.SEGMENT1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| GCC.SEGMENT4
|| '-'
|| gcc.segment5 cgh_acct ,
GJL.ACCOUNTED_DR DR ,
GJL.ACCOUNTED_CR CR ,
GJL.ATTRIBUTE1 PROJECT_ID ,
GJL.ATTRIBUTE2 TASK_ID ,
PPA.SEGMENT1 PROJECTNUMBER,
PT.TASK_NUMBER TASKNUMBER
FROM GL.GL_JE_HEADERS GJH ,
GL.GL_JE_BATCHES GJB ,
GL.GL_JE_LINES GJL ,
GL.GL_JE_SOURCES_TL GJST ,
GL.GL_JE_CATEGORIES_TL GJCT ,
GL.GL_CODE_COMBINATIONS GCC ,
APPLSYS.FND_USER FU ,
PA.PA_PROJECTS_ALL PPA ,
PA.PA_TASKS pt
WHERE GJH.JE_BATCH_ID                                            = GJB.JE_BATCH_ID(+)
AND gjh.je_header_id                                             = gjl.je_header_id
AND GJH.JE_SOURCE                                                = GJST.JE_SOURCE_NAME
AND GJH.JE_CATEGORY                                              = GJCT.JE_CATEGORY_NAME
AND GJH.CREATED_BY                                               = FU.USER_ID
AND GJL.CODE_COMBINATION_ID                                      = GCC.CODE_COMBINATION_ID
-- These allow for duff information from other sources to be excluded.
AND DECODE(GJL.CONTEXT,'Capital Contribution',GJL.ATTRIBUTE1,-1) = PPA.PROJECT_ID(+)
AND DECODE(GJL.CONTEXT,'Capital Contribution',GJL.ATTRIBUTE2,-1) = PT.TASK_ID (+)
AND GJH.JE_CATEGORY                                              = 'Capital Contribution'
ORDER BY GJH.CREATION_DATE DESC ,
GJB.NAME ,
GJH.NAME ,
GJL.JE_LINE_NUM;

* All SQL statements are provided without warranty or support. Use at your own risk!

Leave a Reply