getreport_zg_xlj1.prc

用友NC 多栏帐 账簿查询 存储过程 oracle

create or replace procedure getreport_zg_xljex
(byearv in integer,eyearv in integer, bmonv in integer,emonv in integer,pk_corp in varchar2,
deptcode in varchar2,multiname in varchar2,oper in varchar2,hebing int) 
Authid Current_User 
is
begin
 declare
  type   cursor1   is   ref   cursor;   
  rootkemu varchar2(60);
  sqlstr string(32167); 
  sqlstr1 string(32167); 
  sqlstr2 string(32167); 
  sqlstr3 string(32167); 
  sqlstr4 string(32167);  
  sqlstr5 string(32167);  
  sqlstr6 string(32167); 
  sqlstr7 string(32167);
  sqlstrmx1 string(32167);
  sqlstrmx2 string(32167);
  subcondition varchar2(4000);
  sqlstrse1 string(4000);
  sqlstrse2 string(4000); 
  sqlstrse1in1 string(15000);
  sqlstrse2in1 string(15000);  
  sqlsumin string(32167); 
  sqlsup string(15000);
  sqlsupcon string(15000);
  sqlsupz string(32167);
  sqlinz string(15000);
  sqlinzcon string(15000);
  sqlinzz string(32167);
  sqlym string(15000);
  sqlyh string(10000);
  sqlyhcon string(10000);
  sqlyhconh string(10000);
  sqlyhz string(32167);
  sqlnh string(10000);
  sqlnhcon string(10000);
  sqlnhconh string(10000);
  sqlnhz string(32167);
  sqlye string(1000);
  sqlyeup string(5000);
  sqlyeupcon string(500);
  teminsert string(500);
  bootinh string(10000);
  bootseh string(10000);
  bootcon string(10000);
  bootsehfirst string(10000);
  yearbtem integer;  
  monbtem  integer; 
  monetem  integer;
  qu_table varchar2(60);
  qutem_table1 varchar2(60);
  qutem_table2 varchar2(60);
  boottem varchar2(60);
  re_table varchar2(60);
  red_table varchar2(60);
  reh_table varchar2(60);
  acccode varchar2(60);
  looptem varchar2(400);
  temsql string(500);
  zlast varchar2(60);
  bumenstr varchar2(100);
  bumenstrcon varchar2(100);
  i number(2);
  j number(2);
  k number(2);
  li number(2);
  mv number(2);
  dix int;
  hh int;
  khh int;
  c1 cursor1;
  cu1 cursor1;
  cu2 cursor1;
  kmh varchar2(60);
  kmm varchar2(60);
  v_i number;
   begin
   --debug
    
    if(deptcode is not null) then
     begin
       bumenstr:= ' left join  gl_freevalue d on a.assid=d.freevalueid ';
       bumenstrcon:= ' and  d.valuecode = '''||deptcode||'''';
     end;
     else
     begin
       bumenstr:= '';
       bumenstrcon:=' and 1=1';
     end;
     end if;
   
     open c1 for
     'select  substr(c.analycolcode,1,4)  as dd  from gl_multibook_h a left join gl_multibook_b1 b on a.pk_multicol=b.pk_multicol
     left join gl_multibook_b2 c on a.pk_multicol=c.pk_multicol 
     where a.pk_corp='||pk_corp||' and a.multicolname='''||multiname||''' and rownum<=1 ';
     fetch c1 into rootkemu;
     close c1;
    -- dbms_output.put_line(rootkemu);    取出根科目了 比如 5502 
     commit;
    --开始生成随机数 以生成随即查询表
    --先用输入的操作员号代替 
    qu_table :='QUERY_MASTER'||rootkemu||oper; --查询用表
    qutem_table1 := 'QUERY_MASTER_TEM1'||rootkemu||oper;-- 查询用临时表1
    qutem_table2 := 'QUERY_MASTER_TEM2'||rootkemu||oper;--查询用临时表2
    re_table := 'REPORT_ZG_'||rootkemu||oper; --打印用表主表
    red_table := 'REPORT_ZG_'||rootkemu||'D'||oper;--打印用表明细表
    reh_table := 'REPORT_ZG_'||rootkemu||'H'||oper;--打印用表表头
    boottem := 'BOOTZ_ZG_TEM'||rootkemu||oper; --拆分月末结转用临时表
   i:=0;
   --建表 
   select count(*) into i from USER_TABLES where table_name = qu_table; 
   if(i>0) then
     sqlstr:='truncate  table  '||qu_table; 
     execute immediate sqlstr;
     commit;
     sqlstr:='drop table '||qu_table;
     execute immediate sqlstr;
     commit;
   end if;
   select count(*) into i from USER_TABLES where table_name = qutem_table1; 
   if(i>0) then
     sqlstr:='truncate  table  '||qutem_table1; 
     execute immediate sqlstr;
     commit;
     sqlstr:='drop table '||qutem_table1;
     execute immediate sqlstr;
     commit;
   end if;
   select count(*) into i from USER_TABLES where table_name = qutem_table2; 
   if(i>0) then
     sqlstr:='truncate  table  '||qutem_table2; 
     execute immediate sqlstr;
     commit;
     sqlstr:='drop table '||qutem_table2;
     execute immediate sqlstr;
     commit;
   end if;
   select count(*) into i from USER_TABLES where table_name = re_table; 
   if(i>0) then
     sqlstr:='truncate  table  '||re_table; 
     execute immediate sqlstr;
     commit;
     sqlstr:='drop table '||re_table;
     execute immediate sqlstr;
     commit;
   end if;
   select count(*) into i from USER_TABLES where table_name = red_table; 
   if(i>0) then
     sqlstr:='truncate  table  '||red_table; 
     execute immediate sqlstr;
     commit;
     sqlstr:='drop table '||red_table;
     execute immediate sqlstr;
     commit;
   end if;     
   select count(*) into i from USER_TABLES where table_name = reh_table; 
   if(i>0) then
     sqlstr:='truncate  table  '||reh_table; 
     execute immediate sqlstr;
     commit;
     sqlstr:='drop table '||reh_table;
     execute immediate sqlstr;
     commit;
   end if;     
   select count(*) into i from USER_TABLES where table_name = boottem; 
   if(i>0) then
     sqlstr:='truncate  table  '||boottem; 
     execute immediate sqlstr;
     commit;
     sqlstr:='drop table '||boottem;
     execute immediate sqlstr;
     commit;
   end if;              
     sqlstr1:='CREATE  TABLE '||qu_table||'
     (XLJID INT,yearv varchar2(4) null,mony varchar2(2) null,dayy varchar2(2) null,jz varchar2(60) null,explanation varchar2(100) null,';  
     sqlstr1:= sqlstr1||'jiesum number(20,4)';
     sqlstr2:='CREATE  TABLE '||qutem_table1||'
     (XLJID INT,yearv varchar2(4) null,mony varchar2(2) null,dayy varchar2(2) null,jz varchar2(60) null,explanation varchar2(100) null,';  
     sqlstr2:= sqlstr2||'jiesum number(20,4) default 0.0000';
     sqlstr3:='CREATE  TABLE '||qutem_table2||'
     (XLJID INT,yearv varchar2(4) null,mony varchar2(2) null,dayy varchar2(2) null,jz varchar2(60) null,explanation varchar2(100) null,';  
     sqlstr3:= sqlstr3||'jiesum number(20,4) default 0.0000';
     
     sqlstr4:='CREATE  TABLE '||re_table||'
     (XLJID INT, YUE varchar2(2) null,RI varchar2(2) null,JIZHANG varchar2(20) null,ZHAIYAO varchar2(60) null,
     A0 varchar2(1) null,A1 varchar2(1) null,A2 varchar2(1) null,A3 varchar2(1) null,A4 varchar2(1) null,A5 varchar2(1) null,A6 varchar2(1) null,
     A7 varchar2(1) null,A8 varchar2(1) null,A9 varchar2(1) null,A10 varchar2(1) null,A11 varchar2(1) null,
     B0 varchar2(1) null,B1 varchar2(1) null,B2 varchar2(1) null,B3 varchar2(1) null,B4 varchar2(1) null,B5 varchar2(1) null,B6 varchar2(1) null,
     B7 varchar2(1) null,B8 varchar2(1) null,B9 varchar2(1) null,B10 varchar2(1) null,B11 varchar2(1) null,dr varchar2(2) null,
     C0 varchar2(1) null,C1 varchar2(1) null,C2 varchar2(1) null,C3 varchar2(1) null,C4 varchar2(1) null,C5 varchar2(1) null,C6 varchar2(1) null,
     C7 varchar2(1) null,C8 varchar2(1) null,C9 varchar2(1) null,C10 varchar2(1) null,C11 varchar2(1) null,
     D0 varchar2(1) null,D1 varchar2(1) null,D2 varchar2(1) null,D3 varchar2(1) null,D4 varchar2(1) null,D5 varchar2(1) null,D6 varchar2(1) null,
     D7 varchar2(1) null,D8 varchar2(1) null,D9 varchar2(1) null';  
     sqlstr5:='CREATE  TABLE '||red_table||'(XLJID INT null';      
      
     sqlstr6:='CREATE TABLE '||reh_table||'(idx number(4) null,dix1  number(4) null,ifh number(4) null,hname varchar2(60) null)';
     sqlstr7:='CREATE TABLE '||boottem||'
     (XLJID INT,yearv varchar2(4) null,mony varchar2(2) null,dayy varchar2(2) null,jz varchar2(60) null,explanation varchar2(100) null,';
     sqlstr7:= sqlstr7||'jiesum number(20,4) default 0.0000,kemuhao varchar2(60) null';
     

     
     
     execute immediate sqlstr6;
     commit;
     --插入主表固定表头
                 if (byearv=eyearv) then
                   begin
                   teminsert:='
                   insert into '||reh_table||'(idx,ifh,hname)
                   values(1,1,'''||byearv||''')';
                   end;
                  else
                   begin
                   teminsert:='
                   insert into '||reh_table||'(idx,ifh,hname)
                   values(1,1,'''||byearv||'-'||eyearv||''')';
                   end;
                   end if; 
                   
                 

                   execute immediate teminsert;
                   commit;
                   teminsert:='
                   insert into '||reh_table||'(idx,ifh,hname)
                   values(2,1,''合计'')';
                   execute immediate teminsert;
                   commit;
    hh:=3;
    khh:=1;
      
     sqlstrmx1 := 'insert into '||qutem_table1||'(yearv,mony,dayy,jz,explanation,jiesum,daisum,dr'; 
     sqlstrmx2 := 'insert into '||qutem_table2||'(yearv,mony,dayy,jz,explanation,jiesum,daisum,dr';
     bootinh:='insert into '||boottem||'(yearv,mony,dayy,jz,explanation,jiesum,daisum,kemuhao)';
     if (hebing is not null) then --合并分录
      begin
       sqlstrse1:='select y.yearv,y.mony,y.dayy,y.jz,''摘要'' as explanation,sum(y.jiesum) as jiesum,sum(y.daisum) as daisum,y.dr as dr';
      end;
      else
      begin
       sqlstrse1:='select y.yearv,y.mony,y.dayy,y.jz,y.explanation,y.jiesum,y.daisum,y.dr';      
      end;
      end if; 

      
     sqlstrse1in1:=' from (select :yearb as yearv, to_char(to_date(a.PREPAREDDATEV,''YYYY-MM-DD hh24:mi:ss''),''MM'') AS MONY,