现在的位置: 首页 > 综合 > 正文

账户余额查询SQL(分类帐)

2013年03月20日 ⁄ 综合 ⁄ 共 14459字 ⁄ 字号 评论关闭
/*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, 
'-'12+ 1,
                                instr(gcc_ori.concatenated_segments, 
'-'12)) 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, 
'-'12+ 1,
                      instr(gcc_ori.concatenated_segments, 
'-'12)) =
               nvl(:bank,
                   substr(gcc_ori.concatenated_segments,
                          instr(gcc_ori.concatenated_segments, 
'-'12+ 1,
                          instr(gcc_ori.concatenated_segments, 
'-'12)))
              
--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的数据.由三部分的本币借贷方金额计算得出帐户余额;计算公式为:上期余额+借方-贷方=本期余额。
(上期余额由开帐金额得出)
*/



--创建临时表
create table BALANCESTEMP
(
  je_line_num 
number,
  headerID 
number(38),
  dr  
number,
  cr 
number,
  BALANCE  
VARCHAR2(4000not null,
  BANKNAME 
VARCHAR2(4000not 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, 
'-'12+ 1,
                          instr(gcc_ori.concatenated_segments, 
'-'12)) =
                   nvl(brankacct,
                       substr(gcc_ori.concatenated_segments,
                              instr(gcc_ori.concatenated_segments, 
'-'12+ 1,
                              instr(gcc_ori.concatenated_segments, 
'-'12)))
               
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 <=

抱歉!评论已关闭.