Thursday, November 17, 2011

GL Journal Header Summary

/* GL Journal Header Summary
Summary listing of journal header records by category and source across multiple sets of books.
(Tested on Vision 11.5.10.2  June 2007 ) */
SELECT sob.SHORT_NAME"Book"
,    gjh.STATUS
,    gjh.POSTED_DATE
,    gjh.CREATION_DATE
,    gls.user_je_source_name"Source"
,    glc.user_je_category_name"Category"
,    gjh.PERIOD_NAME"Period"
,    gjb.NAME"Batch Name"
,    gjh.NAME"Journal Name"
,    gjh.currency_code"Currency"
FROM GL_JE_BATCHES gjb, GL_JE_HEADERS gjh,GL_SETS_OF_BOOKS sob,
  GL_JE_SOURCES gls, GL_JE_CATEGORIES glc
WHERE gjb.JE_BATCH_ID = gjh.JE_BATCH_ID
AND gjh.SET_OF_BOOKS_ID = sob.set_of_books_id
AND gls.JE_SOURCE_NAME = gjh.JE_SOURCE
AND  glc.JE_CATEGORY_NAME = gjh.JE_CATEGORY
--and gjh.NAME   = 'QUV-DECLARATION TVA 11/04'       -- JOURNAL NAME    
--and gls.user_je_source_name like '%Mass%'      -- JOURNAL SOURCE
--AND glc.user_je_category_name= 'Adjustment'     -- JOURNAL CATEGORY
--and gjh.PERIOD_NAME in ('MAY-06')          -- JOURNAL PERIOD
and (trunc(gjh.creation_date) >= to_date('01/07/2002','dd/mm/yyyy')
 or trunc(gjh.posted_date) >= to_date('01/07/2002','dd/mm/yyyy'))
--and substr(sob.short_name,1,2) in ('DE')
order by 1,2 desc,3,4,5,7

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect