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