/* GL INTERFACE SUMMARY
Written by Daniel North,  ORAFINAPPS Limited 2007
Shows summary by source, book, request_id and group_id of the transactions in the GL interface across multiple sets of books
This can be used for ad-hoc queries such as month and or to include in automated Oracle alerts 
(Tested on Vision 11.5.10.2  June 2007 ) */
SELECT SOB.SHORT_NAME"BOOK NAME" 
,	   GLI.USER_JE_SOURCE_NAME "JRNL SOURCE"
,	   GLI.SET_OF_BOOKS_ID "BOOKS ID"
--,	   TRUNC(ACCOUNTING_DATE)"GL DATE"
,	   PERIOD_NAME"PERIOD"
,	   GLI.STATUS
,	   GLI.GROUP_ID
,	   GLI.REQUEST_ID
,	   TRUNC(GLI.DATE_CREATED) "CREATED DATE"
--,GLI.DATE_CREATED
,	   TRUNC(GLI.ACCOUNTING_DATE)"GL DATE"
,	   COUNT(*)
FROM GL_INTERFACE GLI, GL_SETS_OF_BOOKS SOB
WHERE SOB.SET_OF_BOOKS_ID(+) = GLI.SET_OF_BOOKS_ID 
--AND GLI.USER_JE_SOURCE_NAME = 'PEOPLESOFT HR'
AND GLI.USER_JE_SOURCE_NAME = 'Receivables'
--AND TRUNC(GLI.DATE_CREATED) > '01-DEC-2005'
--AND GLI.USER_JE_SOURCE_NAME = 'PAYROLL'
--AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('ES','BE','LU')
GROUP BY  SOB.SHORT_NAME,GLI.USER_JE_SOURCE_NAME, GLI.SET_OF_BOOKS_ID,
 PERIOD_NAME, GLI.STATUS,  GLI.GROUP_ID, TRUNC(GLI.DATE_CREATED),TRUNC(ACCOUNTING_DATE)--,GLI.DATE_CREATED
,	   GLI.REQUEST_ID
--,GLI.DATE_CREATED
--ORDER BY GLI.DATE_CREATED