账户余额查询SQL（分类帐）代码,OracleEBS代码库,德仔网

代码语言
代码分类
【OracleEBS】账户余额查询SQL（分类帐）
作者:专注Oracle EBS / 发布于2012/11/29/ 932
/*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,
                                instr(fa_rx_flex_pkg.get_description(101,
                                                                     'GL#',
                                                                     gcc_ori.chart_of_accounts_id,
                                                                     'ALL',
                                                                     gcc_ori.concatenated_segments),
                                      '-',
,
)) 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,
                      instr(fa_rx_flex_pkg.get_description(101,
                                                           'GL#',
                                                           gcc_ori.chart_of_accounts_id,
                                                           'ALL',
                                                           gcc_ori.concatenated_segments),
                            '-',
,
)) 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的数据.由三部分的本币借贷方金额计算得出帐户余额；计算公式为：上期余额+借方-贷方=本期余额。
（上期余额由开帐金额得出）*/



--创建临时表
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,
                                   instr(gcc_ori.concatenated_segments,
                                         '-',
,
)) 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,
                                           instr(gcc_ori.concatenated_segments,
                                                 '-',
,
)) 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,
                                  instr(gcc_ori.concatenated_segments,
                                        '-',
,
)) =
                           nvl(brankacct,
                               substr(gcc_ori.concatenated_segments,
                                      instr(gcc_ori.concatenated_segments,
                                            '-',
,
) + 1,
                                      instr(gcc_ori.concatenated_segments,
                                            '-',
,
)))
                       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;

    --调用方法
试试其它关键字
　账户余额查询　
同语言下
可能有用的
专注Oracle EBS贡献的其它代码(66)