GL INTERFACE SUMMARY

/* GL INTERFACE SUMMARY
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