/* GL INTERFACE DETAIL
Written by Daniel North,  ORAFINAPPS Limited 2007
Shows transactions level detail with full accounting and status information for each line in the GL interface across multiple sets of books
Can be used for SOX and system audits. 
(Tested on Vision 11.5.10.2  June 2007 ) */
SELECT	SOB.SHORT_NAME "BOOK"
,	    GLI.SET_OF_BOOKS_ID "SOB ID"
,		TRUNC(GLI.ACCOUNTING_DATE) "GL DATE"
,		GLI.CURRENCY_CODE "CUR"
,		GLI.USER_JE_CATEGORY_NAME "JE CATEGOTY"
,		GLI.USER_JE_SOURCE_NAME "JE SOURCE"
,		GLI.ENTERED_DR "ENT DR"
,		GLI.ENTERED_CR "ENT CR"
,		GLI.ACCOUNTED_DR "ACC DR"
,		GLI.ACCOUNTED_CR "ACC CR"
,		GLI.SEGMENT1||'.'||GLI.SEGMENT2||'.'||GLI.SEGMENT3||'.'||GLI.SEGMENT4||'.'||GLI.SEGMENT5
		||'.'||GLI.SEGMENT6||'.'||GLI.SEGMENT7||'.'||GLI.SEGMENT8||'.'||GLI.SEGMENT9||'.'||GLI.SEGMENT10 "ACCOUNT COMB."
,		GLI.REFERENCE1 "REF 1"
,		GLI.REFERENCE2 "REF 2"
,		GLI.REFERENCE4 "REF 4"
,		GLI.REFERENCE7 "REF 7"
,		GLI.REFERENCE10 "REF 10"
,		GLI.WARNING_CODE
,		GLI.STATUS_DESCRIPTION
,		GLI.STATUS
--SELECT GLI.REFERENCE10 "REF 10"
--SELECT DISTINCT GLI.SEGMENT4--,GLI.SEGMENT2, GLI.SEGMENT3, SOB.SHORT_NAME, GLI.SET_OF_BOOKS_ID
FROM GL_INTERFACE GLI, GL_SETS_OF_BOOKS SOB
WHERE SOB.SET_OF_BOOKS_ID(+) = GLI.SET_OF_BOOKS_ID 
--AND GLI.WARNING_CODE IS NOT NULL
--AND GLI.STATUS <> 'P'
and GLI.USER_JE_SOURCE_NAME = 'Payables'
--and trunc(GLI.DATE_CREATED) > '01-DEC-2005'
--and GLI.CURRENCY_CODE  = 'GBP'
--and (GLI.ENTERED_DR <> GLI.ACCOUNTED_DR
--	or GLI.ENTERED_CR <> GLI.ACCOUNTED_CR)
--and GLI.USER_JE_CATEGORY_NAME = 'Bill'
--and substr(SOB.SHORT_NAME,1,2) in ('BE')
--and GLI.SEGMENT3 = '8181'
--AND GLI.STATUS_DESCRIPTION IS NOT NULL
order by 3