/* GL Journal Header Summary
Written by Daniel North,  ORAFINAPPS Limited 2007
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