oracle apps GL Journal Header attributes query

Posted by

Query to get the Journal Header Level Attributes for the Journal Batches

  • Journal Posted Date is NOT NULL
  • Period Name let you fetch records for a particular Period
  • Source Name provides from which Source it is being received
  • Category Name provides for which category Journal is being received
SELECT GJH.JE_BATCH_ID
, GJB.NAME " JOURNAL BATCH NAME"
, GJH.NAME " JOURNAL NAME"
, SOB.SHORT_NAME ||'-'||GL.NAME " LEDGER NAME"
, GJH.PERIOD_NAME " PERIOD NAME"
, GJH.STATUS " JOURNAL STATUS"
, GLS.USER_JE_SOURCE_NAME " JOURNAL SOURCE"
, GLC.USER_JE_CATEGORY_NAME " JOURNAL CATEGORY"
, GJH.CURRENCY_CODE " CURRENCY"
, GJH.POSTED_DATE " POSTED DATE"
, GJH.CREATION_DATE " CREATION DATE"
FROM GL_LEDGERS GL ,
GL_JE_BATCHES GJB,
GL_JE_HEADERS GJH,
GL_JE_SOURCES GLS,
GL_JE_CATEGORIES GLC,
GL_SETS_OF_BOOKS SOB
WHERE 1=1
AND GL.LEDGER_ID = SOB.SET_OF_BOOKS_ID
AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GLS.JE_SOURCE_NAME = GJH.JE_SOURCE
AND GLC.JE_CATEGORY_NAME = GJH.JE_CATEGORY
AND GJH.POSTED_DATE IS NOT NULL
AND GJH.PERIOD_NAME ='MAR-16'