Oracle EBS SQL: GL Headers and Lines
Thursday, December 19th, 2013The 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!