create table BALANCESTEMP
(
  je_line_num number,
  headerID number(38),
  dr  number,
  cr number,
  BALANCE  VARCHAR2(4000) not null,
  BANKNAME VARCHAR2(4000) not null,
  MM       DATE not null,
  ID       NUMBER not null
)

创建临时表

CREATE OR REPLACE FUNCTION GAB_FUNC(line_num number,
                                    ae_header_id number,
                                    DR     NUMBER,
                                    CR     NUMBER,
                                    startdate date,
                                    ENDDATE  DATE,
                                    accounting_date DATE,
                                    BRANKACCT VARCHAR2) RETURN CHAR IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  BLtableCUT NUMBER(38);
  LStableCUT NUMBER(38);
  BL  varchar(4000);
  STR NUMBER;
  CF  varchar(4000);
  X   number;
  CT  NUMBER;
  jishu NUMBER:=0;
  c number;
  c1 number;
  c2 number;
  enBL number;
  balance number;
BEGIN

/**查询余额表 2010-06-01至用户参数开始日期前的所有数据**/

SELECT count(*) into BLtableCUT
  FROM (select distinct xal.accounting_date AS accounting_date,xal.ae_header_id as a,
                        xah.doc_sequence_value AS doc_sequence_value,
                        xal.accounted_dr AS accounted_dr,
                        xal.accounted_cr AS accounted_cr,
                        SUBSTR(GCC_ORI.CONCATENATED_SEGMENTS,
                               INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2) + 1,
                               INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2)) AS CODE_ACCOUNTS
          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(BRANKACCT,
                   SUBSTR(GCC_ORI.CONCATENATED_SEGMENTS,
                          INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2) + 1,
                          INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2)))
           AND TO_CHAR(xal.accounting_date, 'yyyy-mm-dd') between
               '2010-06-30' and TO_CHAR(startdate-1, 'yyyy-mm-dd')
        union all
        select headers.default_effective_date as accounting_date,headers.je_header_id as a,
               headers.doc_sequence_value as doc_sequence_value,
               lines.accounted_dr               as accounted_dr,
               lines.accounted_cr               as accounted_cr,
               lines.segment3
          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.JE_CATEGORY = '1'
           and lines.segment3 = NVL(BRANKACCT, lines.segment3)
           and lines.code_combination_id = gcc_ori.code_combination_id
           AND TO_CHAR(headers.default_effective_date, 'yyyy-mm-dd') between
               '2010-06-30' and TO_CHAR(startdate-1, 'yyyy-mm-dd')
               )
 ORDER BY  accounting_date, doc_sequence_value;

  /**查询2010-06-01至用户参数开始时间之间的数据*判断是否有余额*/
  SELECT COUNT(*)
    INTO LStableCUT
    FROM BALANCESTEMP B1
   WHERE
   B1.BANKNAME=BRANKACCT
   AND  B1.MM BETWEEN to_date('2010-6-30','yyyy-mm-dd') AND  TO_DATE(TO_CHAR(startdate-1, 'yyyy-mm-dd'),'yyyy-mm-dd') ;

IF TO_CHAR(startdate-1, 'yyyy-mm-dd')='2010-06-30' THEN--判断用户参数开始日期是否为开帐日期
  LStableCUT:=LStableCUT;
END IF;
IF TO_CHAR(startdate-1, 'yyyy-mm-dd')<>'2010-06-30' THEN
  LStableCUT:=LStableCUT-1;
END IF;

 SELECT count(*)
   INTO jishu
   FROM BALANCESTEMP b
  WHERE MM = TO_DATE(TO_CHAR(accounting_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') --判断是否已经存在
    and b.bankName = BRANKACCT
    and b.headerID = ae_header_id
    and b.je_line_num = line_num ;
  dbms_output.put_line(LStableCUT||'：LStableCUTLStableCUT');
  dbms_output.put_line(BLtableCUT||'：BLtableCUTBLtableCUT');
  IF LStableCUT=BLtableCUT THEN --如果临时表科目总记录数与余额表记录数相等;则直接进行余额递减操作
      dbms_output.put_line(jishu||'：jishu');
  IF jishu<=0 THEN
  SELECT MAX(id)
    INTO X
    FROM BALANCESTEMP
   WHERE MM <= TO_DATE(TO_CHAR(ENDDATE, 'yyyy-mm-dd'),
                      'yyyy-mm-dd') and bankName=BRANKACCT;
  SELECT BT.BALANCE INTO BL FROM BALANCESTEMP BT WHERE bt.id=x;
  STR := TO_NUMBER(BL);
  CF  := TO_CHAR(STR + NVL(DR, 0) - NVL(CR, 0));
   select max(id) into c from BALANCESTEMP;
    c1:=c+1;
    INSERT INTO BALANCESTEMP (je_line_num,HEADERID,dr,cr,BALANCE, MM,bankName,id) VALUES (line_num,ae_header_id,dr,cr,CF, accounting_date,BRANKACCT,c1);
  COMMIT;
  RETURN CF;
  end if;
  END IF;

  IF LStableCUT<>BLtableCUT THEN --如果临时表科目总记录数与余额表记录数 不相等; 则将用户参数开始日期前的所有数据初始化
   IF jishu<=0 THEN
    enBL:= STARTBALANCE(startdate,BRANKACCT );
  SELECT MAX(id)
    INTO X
    FROM BALANCESTEMP
   WHERE MM <= TO_DATE(TO_CHAR(ENDDATE, 'yyyy-mm-dd'),
                      'yyyy-mm-dd') and bankName=BRANKACCT;
  SELECT BT.BALANCE INTO BL FROM BALANCESTEMP BT WHERE bt.id=x;
  STR := TO_NUMBER(BL);
  CF  := TO_CHAR(STR + NVL(DR, 0) - NVL(CR, 0));
   select max(id) into c from BALANCESTEMP;
    c1:=c+1;
    INSERT INTO BALANCESTEMP (je_line_num,HEADERID,dr,cr,BALANCE, MM,bankName,id) VALUES (line_num,ae_header_id,dr,cr,CF, accounting_date,BRANKACCT,c1);
  COMMIT;
  RETURN CF;
  END IF;
  END IF;
  RETURN CF;
END;

CREATE OR REPLACE FUNCTION STARTBALANCE(startdate date,
                                        BRANKACCT VARCHAR2) RETURN CHAR IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  BL  varchar(4000);
  X   number;
  STR NUMBER;
  CF  varchar(4000);
  c   number;
  c1  number;
  c2  number;
  CUT number;
  enBL number;
begin
  declare
    cursor cc is
      SELECT ae_header_id,
             doc_sequence_value,
             je_line_num,
             accounting_date,
             accounted_dr,
             accounted_cr,
             entered_dr,
             entered_cr,
             CODE_ACCOUNTS
        FROM (select distinct xal.accounting_date AS accounting_date,
                              xah.doc_sequence_value AS doc_sequence_value,
                              xal.ae_line_num as je_line_num,
                              xal.ae_header_id as ae_header_id,
                              xal.accounted_dr AS accounted_dr,
                              xal.accounted_cr AS accounted_cr,
                              xal.entered_dr AS entered_dr,
                              xal.entered_cr AS entered_cr,
                              SUBSTR(GCC_ORI.CONCATENATED_SEGMENTS,
                                     INSTR(GCC_ORI.CONCATENATED_SEGMENTS,
                                           '-',
                                           1,
                                           2) + 1,
                                     INSTR(GCC_ORI.CONCATENATED_SEGMENTS,
                                           '-',
                                           1,
                                           2)) AS CODE_ACCOUNTS
                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(BRANKACCT,
                         SUBSTR(GCC_ORI.CONCATENATED_SEGMENTS,
                                INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2) + 1,
                                INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2)))
                 AND TO_CHAR(xal.accounting_date, 'yyyy-mm-dd') between
                     '2010-06-30' and TO_CHAR(startdate - 1, 'yyyy-mm-dd')
              union all
              select headers.default_effective_date as accounting_date,
                      headers.doc_sequence_value as doc_sequence_value,
                     lines.je_line_num as je_line_num,
                     headers.je_header_id             as ae_header_id,
                     lines.accounted_dr               as accounted_dr,
                     lines.accounted_cr               as accounted_cr,
                     lines.entered_dr                 as entered_dr,
                     lines.entered_cr                 as entered_cr,
                     lines.segment3
                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.JE_CATEGORY = '1'
                 and lines.segment3 = NVL(BRANKACCT, lines.segment3)
                 and lines.code_combination_id = gcc_ori.code_combination_id
                 AND TO_CHAR(headers.default_effective_date, 'yyyy-mm-dd') between
                     '2010-06-30' and TO_CHAR(startdate - 1, 'yyyy-mm-dd'))
       ORDER BY accounting_date, doc_sequence_value;
  begin

    for i in cc loop

     SELECT MAX(id)
      INTO X
      FROM BALANCESTEMP
     WHERE MM < TO_DATE(TO_CHAR(startdate, 'yyyy-mm-dd'), 'yyyy-mm-dd')
       and bankName = BRANKACCT;
    SELECT BT.BALANCE INTO BL FROM BALANCESTEMP BT WHERE bt.id = x;
    STR := TO_NUMBER(BL);
      SELECT count(*)
        INTO CUT
        FROM BALANCESTEMP
       WHERE MM = TO_DATE(TO_CHAR(i.accounting_date, 'yyyy-mm-dd'), 'yyyy-mm-dd')
         and bankName = BRANKACCT
         and je_line_num=i.je_line_num
         and headerID =i.ae_header_id;
      IF CUT<=0 THEN
      CF := TO_CHAR(STR + NVL(i.accounted_dr, 0) - NVL(i.accounted_cr, 0));
      select max(id) into c from BALANCESTEMP;
      c1 := c + 1;
      INSERT INTO BALANCESTEMP
        (je_line_num,headerID, dr, cr, BALANCE, MM, bankName, id)
      VALUES
        (
        i.je_line_num,
        i.ae_header_id,
        i.accounted_dr,
         i.accounted_cr,
         CF,
         i.accounting_date,
         BRANKACCT,
         c1);
      COMMIT;
     END IF;
    end loop;
    return CF;

  end;
end;

调用方法