GL INTERFACE DETAIL

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