凭证号查询

取本期所有的公司的凭证号和制单人，为财务统计使用：

 

SELECT DISTINCT GCC.SEGMENT1,
                GJH.DOC_SEQUENCE_VALUE,
                SUBSTR(FU.DESCRIPTION,
                       INSTR(FU.DESCRIPTION, '_', 1, 1) + 1,
                       LENGTH(FU.DESCRIPTION))
  FROM GL.GL_JE_HEADERS GJH
 INNER JOIN GL.GL_JE_LINES GJL
    ON GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
 INNER JOIN GL.GL_CODE_COMBINATIONS GCC
    ON GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
  LEFT JOIN FND_USER FU
    ON FU.USER_NAME = SUBSTR(GJH.EXTERNAL_REFERENCE,
                             INSTR(GJH.EXTERNAL_REFERENCE, ':', -1, 1) + 1,
                             LENGTH(GJH.EXTERNAL_REFERENCE))
 WHERE GJH.PERIOD_NAME = '09-10'
   AND GJL.PERIOD_NAME = '09-10'
   AND GCC.CHART_OF_ACCOUNTS_ID = 101
   AND GJH.LEDGER_ID = 2026
   AND GJL.LEDGER_ID = 2026
   AND GJH.DEFAULT_EFFECTIVE_DATE >= DATE
 '2010-09-01'
   AND GJH.DEFAULT_EFFECTIVE_DATE < DATE '2010-10-01'