账户余额查询SQL（分类帐）

/*BEGIN
  MO_GLOBAL.INIT('AR');
END;*/

SELECT je_line_num,AE_HEADER_ID,doc_sequence_value,accounting_date,description,accounted_dr,accounted_cr,entered_dr ,entered_cr,currency_code,
 CODE_ACCOUNTS, SOURCEDESCRIPTION,startdate,enddate FROM (

select
distinct
XAH.AE_HEADER_ID as AE_HEADER_ID,
xal.ae_line_num as je_line_num,
xah.doc_sequence_value AS doc_sequence_value,
xal.accounting_date AS accounting_date,
null as description ,
 xal.accounted_dr AS accounted_dr ,
 xal.accounted_cr AS accounted_cr,
 xal.entered_dr AS entered_dr ,
 xal.entered_cr AS entered_cr,
 xal.currency_code as currency_code,
 SUBSTR(GCC_ORI.CONCATENATED_SEGMENTS,
        INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2) + 1,
        INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2)) AS CODE_ACCOUNTS,
 SUBSTR(  FA_RX_FLEX_PKG.GET_DESCRIPTION(101,
                                       'GL#',
                                       GCC_ORI.CHART_OF_ACCOUNTS_ID,
                                       'ALL',
                                       GCC_ORI.CONCATENATED_SEGMENTS),
              INSTR(  FA_RX_FLEX_PKG.GET_DESCRIPTION(101,
                                       'GL#',
                                       GCC_ORI.CHART_OF_ACCOUNTS_ID,
                                       'ALL',
                                       GCC_ORI.CONCATENATED_SEGMENTS), '-', 1, 2) + 1,
              INSTR(  FA_RX_FLEX_PKG.GET_DESCRIPTION(101,
                                       'GL#',
                                       GCC_ORI.CHART_OF_ACCOUNTS_ID,
                                       'ALL',
                                       GCC_ORI.CONCATENATED_SEGMENTS), '-', 3, 3)) as SOURCEDESCRIPTION,
      to_char(:BEGINDATE,'yyyy-mm-dd') as startdate,
      to_char(:ENDDATE,'yyyy-mm-dd') as enddate
 
  from xla_transaction_entities xte,
       xla_entity_types_tl      xett,
       xle_entity_profiles      le,
       xla_events               xe,
       xla_event_types_tl       xent,
       xla_ae_headers           xah,
       xla_ae_lines             xal,
       xla_lookups              xlp,
       xla_distribution_links   xdl,
       GL_CODE_COMBINATIONS_KFV GCC_ORI
 where 1 = 1
   and xte.entity_id = xe.entity_id
   and xte.application_id = xe.application_id
   and xte.legal_entity_id = le.legal_entity_id(+)
   and xah.event_id = xe.event_id
   and xah.application_id = xe.application_id(+)
   and xent.event_type_code = xe.event_type_code
   and xent.application_id(+) = xe.application_id
   and xent.language = 'ZHS'
   and xah.ae_header_id(+)= xal.ae_header_id
   and xlp.lookup_type(+) = 'XLA_ACCOUNTING_CLASS'
   and xlp.lookup_code(+) = xal.accounting_class_code
   and xal.ae_header_id = xdl.ae_header_id
   and xal.ae_line_num = xdl.ae_line_num(+)
   and xal.application_id = xdl.application_id(+)
   and xett.entity_code = xte.entity_code
   and xett.application_id = xte.application_id
   AND XAL.CODE_COMBINATION_ID = GCC_ORI.CODE_COMBINATION_ID
   and xett.language = 'ZHS'
   and SUBSTR(GCC_ORI.CONCATENATED_SEGMENTS,
              INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2) + 1,
              INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2)) = NVL(:BANK,SUBSTR(GCC_ORI.CONCATENATED_SEGMENTS,
              INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2) + 1,
              INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2)))
   --and xah.period_name = 'Jul-10'
  AND TO_CHAR(xal.accounting_date, 'yyyy-mm-dd') between
   to_char(:BEGINDATE,'yyyy-mm-dd') and
      to_char(:ENDDATE,'yyyy-mm-dd')
   union all
select
       HEADERS.je_header_id as AE_HEADER_ID,
       lines.je_line_num as je_line_num,
       headers.doc_sequence_value as doc_sequence_value,
       headers.default_effective_date as accounting_date,
       lines.description as description,
       lines.accounted_dr as accounted_dr,
       lines.accounted_cr as accounted_cr,
       lines.entered_dr as entered_dr,
       lines.entered_cr as entered_cr,
       headers.currency_code as currency_code,
       lines.segment3 as CODE_ACCOUNTS,
       SUBSTR(  FA_RX_FLEX_PKG.GET_DESCRIPTION(101,
                                       'GL#',
                                       GCC_ORI.CHART_OF_ACCOUNTS_ID,
                                       'ALL',
                                       GCC_ORI.CONCATENATED_SEGMENTS),
              INSTR(  FA_RX_FLEX_PKG.GET_DESCRIPTION(101,
                                       'GL#',
                                       GCC_ORI.CHART_OF_ACCOUNTS_ID,
                                       'ALL',
                                       GCC_ORI.CONCATENATED_SEGMENTS), '-', 1, 2) + 1,
              INSTR(  FA_RX_FLEX_PKG.GET_DESCRIPTION(101,
                                       'GL#',
                                       GCC_ORI.CHART_OF_ACCOUNTS_ID,
                                       'ALL',
                                       GCC_ORI.CONCATENATED_SEGMENTS), '-', 3, 3)) as SOURCEDESCRIPTION,
      to_char(:BEGINDATE,'yyyy-mm-dd') as startdate,
      to_char(:ENDDATE,'yyyy-mm-dd') as enddate
 

  from GL_JE_HEADERS_v          headers,
       GL_JE_LINES_V            lines,
       GL_CODE_COMBINATIONS_KFV GCC_ORI
 where headers.je_header_id = lines.je_header_id
 --  and headers.batch_period_name_qry = 'Jul-10'
   and HEADERS.JE_CATEGORY = '1'
    and lines.segment3 = NVL(:BANK,lines.segment3)
   and lines.code_combination_id = gcc_ori.code_combination_id
  AND TO_CHAR(headers.default_effective_date, 'yyyy-mm-dd') between
    to_char(:BEGINDATE,'yyyy-mm-dd') and
      to_char(:ENDDATE,'yyyy-mm-dd')

)      ORDER BY  accounting_date, doc_sequence_value

账户余额分三部分，一部分是GL的手工帐，另外是由xla表得到的ap和ar的数据.由三部分的本币借贷方金额计算得出帐户余额；计算公式为：上期余额+借方-贷方=本期余额。

（上期余额由开帐金额得出）

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24899662/viewspace-678541/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24899662/viewspace-678541/