getreport_zg_xlj1.prc

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

               if(yev is not null) then 
                 begin 
                    sqlyhups:='update '||qu_table||' set explanation=
                    (select explanation FROM GL_detail a left join bd_accsubj b on a.pk_accsubj = b.pk_accsubj  
                     left join gl_voucher c on a.pk_voucher=c.pk_voucher   where a.pk_voucher in (select pk_voucher from gl_voucher where 
                     year='||yev||' and period='||monv||' and ''记账-''||no ='''||jzv||''' and dr=0) and b.SUBJCODE  LIKE '''||rootkemu||'%''  '||bumenstrcon||'
                    '||subcondition||' and a.pk_corp='||pk_corp||' and a.debitamount<>0  and rownum<=1) where XLJID='||dix1;
                    execute immediate sqlyhups;
                    commit;
                 end;
                 end if;
             end;
             end if;
            
             
            if(yuee<0) then
             begin
               sqlyhups:='update '||qu_table||' set(yusum,dr)=
               (select abs('||yuee||') as yusum,''贷'' as dr from dual) where XLJID='||dix1;
               execute immediate sqlyhups;
               commit;
             end;
             end if;
           end loop;
           close cuye;
           end;  
           
           
           
        
--执行拆分 主表是一张表 比较容易拆分 所以用一个语句来拆 在开始循环表头的时候 已经记住了
--主表的最后一个字段的名字 所以主表的全部字段都是已知的了 这个方法比较笨 还能改进
             declare 
             temstrex string(32767);
             temstrzb string(500);
             temcol string(60);
             im int:=0;
             begin
             temstrzb:='';
             loop 
              exit when im>=10;
              temstrzb:=trim(temstrzb)||',Z'||trim(zlast)||im;
              temcol:='Y'||trim(zlast);
              im:= im+1;
             end loop;
                    temstrex:=' INSERT INTO '||re_table||'(YUE,RI,JIZHANG,ZHAIYAO,A0,A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,
                           B0,B1,B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,DR,C0,C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,D0,D1,D2,D3,D4,D5,D6,
                           D7,D8,D9'||temstrzb||')
                   select mony,dayy,jz,explanation,
                   case when length(trim(to_char((a.jiesum),''9999999999d9999'')))<15 then null 
                   else substr(trim(to_char((a.jiesum),''9999999999d9999'')),length(trim(to_char((a.jiesum),''9999999999d9999'')))-15+1,1) end as a1,
                   case when length(trim(to_char((a.jiesum),''9999999999d9999'')))<14 then null 
                   else substr(trim(to_char((a.jiesum),''9999999999d9999'')),length(trim(to_char((a.jiesum),''9999999999d9999'')))-14+1,1) end as a2,
                   case when length(trim(to_char((a.jiesum),''9999999999d9999'')))<13 then null 
                   else substr(trim(to_char((a.jiesum),''9999999999d9999'')),length(trim(to_char((a.jiesum),''9999999999d9999'')))-13+1,1) end as a3,
                   case when length(trim(to_char((a.jiesum),''9999999999d9999'')))<12 then null 
                   else substr(trim(to_char((a.jiesum),''9999999999d9999'')),length(trim(to_char((a.jiesum),''9999999999d9999'')))-12+1,1) end as a4,
                   case when length(trim(to_char((a.jiesum),''9999999999d9999'')))<11 then null 
                   else substr(trim(to_char((a.jiesum),''9999999999d9999'')),length(trim(to_char((a.jiesum),''9999999999d9999'')))-11+1,1) end as a5,
                   case when length(trim(to_char((a.jiesum),''9999999999d9999'')))<10 then null 
                   else substr(trim(to_char((a.jiesum),''9999999999d9999'')),length(trim(to_char((a.jiesum),''9999999999d9999'')))-10+1,1) end as a6,
                   case when length(trim(to_char((a.jiesum),''9999999999d9999'')))<9 then null 
                   else substr(trim(to_char((a.jiesum),''9999999999d9999'')),length(trim(to_char((a.jiesum),''9999999999d9999'')))-9+1,1) end as a7,
                   case when length(trim(to_char((a.jiesum),''9999999999d9999'')))<8 then null 
                   else substr(trim(to_char((a.jiesum),''9999999999d9999'')),length(trim(to_char((a.jiesum),''9999999999d9999'')))-8+1,1) end as a8,
                   case when length(trim(to_char((a.jiesum),''9999999999d9999'')))<7 then null 
                   else substr(trim(to_char((a.jiesum),''9999999999d9999'')),length(trim(to_char((a.jiesum),''9999999999d9999'')))-7+1,1) end as a9,
                   case when length(trim(to_char((a.jiesum),''9999999999d9999'')))<6 then null 
                   else substr(trim(to_char((a.jiesum),''9999999999d9999'')),length(trim(to_char((a.jiesum),''9999999999d9999'')))-6+1,1) end as a10,
                   case when length(trim(to_char((a.jiesum),''9999999999d9999'')))<5 then null 
                   else substr(trim(to_char((a.jiesum),''9999999999d9999'')),length(trim(to_char((a.jiesum),''9999999999d9999'')))-4+1,1) end as a11,
                   case when length(trim(to_char((a.jiesum),''9999999999d9999'')))<4 then null 
                   else substr(trim(to_char((a.jiesum),''9999999999d9999'')),length(trim(to_char((a.jiesum),''9999999999d9999'')))-3+1,1) end as a12,
                   case when length(trim(to_char((a.daisum),''9999999999d9999'')))<15 then null 
                   else substr(trim(to_char((a.daisum),''9999999999d9999'')),length(trim(to_char((a.daisum),''9999999999d9999'')))-15+1,1) end as b1,
                   case when length(trim(to_char((a.daisum),''9999999999d9999'')))<14 then null 
                   else substr(trim(to_char((a.daisum),''9999999999d9999'')),length(trim(to_char((a.daisum),''9999999999d9999'')))-14+1,1) end as b2,
                   case when length(trim(to_char((a.daisum),''9999999999d9999'')))<13 then null 
                   else substr(trim(to_char((a.daisum),''9999999999d9999'')),length(trim(to_char((a.daisum),''9999999999d9999'')))-13+1,1) end as b3,
                   case when length(trim(to_char((a.daisum),''9999999999d9999'')))<12 then null 
                   else substr(trim(to_char((a.daisum),''9999999999d9999'')),length(trim(to_char((a.daisum),''9999999999d9999'')))-12+1,1) end as b4,
                   case when length(trim(to_char((a.daisum),''9999999999d9999'')))<11 then null 
                   else substr(trim(to_char((a.daisum),''9999999999d9999'')),length(trim(to_char((a.daisum),''9999999999d9999'')))-11+1,1) end as b5,
                   case when length(trim(to_char((a.daisum),''9999999999d9999'')))<10 then null 
                   else substr(trim(to_char((a.daisum),''9999999999d9999'')),length(trim(to_char((a.daisum),''9999999999d9999'')))-10+1,1) end as b6,
                   case when length(trim(to_char((a.daisum),''9999999999d9999'')))<9 then null 
                   else substr(trim(to_char((a.daisum),''9999999999d9999'')),length(trim(to_char((a.daisum),''9999999999d9999'')))-9+1,1) end as b7,
                   case when length(trim(to_char((a.daisum),''9999999999d9999'')))<8 then null 
                   else substr(trim(to_char((a.daisum),''9999999999d9999'')),length(trim(to_char((a.daisum),''9999999999d9999'')))-8+1,1) end as b8,
                   case when length(trim(to_char((a.daisum),''9999999999d9999'')))<7 then null 
                   else substr(trim(to_char((a.daisum),''9999999999d9999'')),length(trim(to_char((a.daisum),''9999999999d9999'')))-7+1,1) end as b9,
                   case when length(trim(to_char((a.daisum),''9999999999d9999'')))<6 then null 
                   else substr(trim(to_char((a.daisum),''9999999999d9999'')),length(trim(to_char((a.daisum),''9999999999d9999'')))-6+1,1) end as b10,
                   case when length(trim(to_char((a.daisum),''9999999999d9999'')))<5 then null when a.daisum = 0 then null
                   else substr(trim(to_char((a.daisum),''9999999999d9999'')),length(trim(to_char((a.daisum),''9999999999d9999'')))-4+1,1) end as b11,
                   case when length(trim(to_char((a.daisum),''9999999999d9999'')))<4 then null when a.daisum = 0 then null
                   else substr(trim(to_char((a.daisum),''9999999999d9999'')),length(trim(to_char((a.daisum),''9999999999d9999'')))-3+1,1) end as b12,
                   DR,
                   case when length(trim(to_char((a.yusum),''9999999999d9999'')))<15 then null 
                   else substr(trim(to_char((a.yusum),''9999999999d9999'')),length(trim(to_char((a.yusum),''9999999999d9999'')))-15+1,1) end as c1,
                   case when length(trim(to_char((a.yusum),''9999999999d9999'')))<14 then null 
                   else substr(trim(to_char((a.yusum),''9999999999d9999'')),length(trim(to_char((a.yusum),''9999999999d9999'')))-14+1,1) end as c2,
                   case when length(trim(to_char((a.yusum),''9999999999d9999'')))<13 then null 
                   else substr(trim(to_char((a.yusum),''9999999999d9999'')),length(trim(to_char((a.yusum),''9999999999d9999'')))-13+1,1) end as c3,
                   case when length(trim(to_char((a.yusum),''9999999999d9999'')))<12 then null 
                   else substr(trim(to_char((a.yusum),''9999999999d9999'')),length(trim(to_char((a.yusum),''9999999999d9999'')))-12+1,1) end as c4,
                   case when length(trim(to_char((a.yusum),''9999999999d9999'')))<11 then null 
                   else substr(trim(to_char((a.yusum),''9999999999d9999'')),length(trim(to_char((a.yusum),''9999999999d9999'')))-11+1,1) end as c5,
                   case when length(trim(to_char((a.yusum),''9999999999d9999'')))<10 then null 
                   else substr(trim(to_char((a.yusum),''9999999999d9999'')),length(trim(to_char((a.yusum),''9999999999d9999'')))-10+1,1) end as c6,
                   case when length(trim(to_char((a.yusum),''9999999999d9999'')))<9 then null 
                   else substr(trim(to_char((a.yusum),''9999999999d9999'')),length(trim(to_char((a.yusum),''9999999999d9999'')))-9+1,1) end as c7,
                   case when length(trim(to_char((a.yusum),''9999999999d9999'')))<8 then null 
                   else substr(trim(to_char((a.yusum),''9999999999d9999'')),length(trim(to_char((a.yusum),''9999999999d9999'')))-8+1,1) end as c8,
                   case when length(trim(to_char((a.yusum),''9999999999d9999'')))<7 then null 
                   else substr(trim(to_char((a.yusum),''9999999999d9999'')),length(trim(to_char((a.yusum),''9999999999d9999'')))-7+1,1) end as c9,
                   case when length(trim(to_char((a.yusum),''9999999999d9999'')))<6 then null 
                   else substr(trim(to_char((a.yusum),''9999999999d9999'')),length(trim(to_char((a.yusum),''9999999999d9999'')))-6+1,1) end as c10,
                   case when length(trim(to_char((a.yusum),''9999999999d9999'')))<5 then null when a.yusum = 0 then null
                   else substr(trim(to_char((a.yusum),''9999999999d9999'')),length(trim(to_char((a.yusum),''9999999999d9999'')))-4+1,1) end as c11,
                   case when length(trim(to_char((a.yusum),''9999999999d9999'')))<4 then null when a.yusum = 0 then null
                   else substr(trim(to_char((a.yusum),''9999999999d9999'')),length(trim(to_char((a.yusum),''9999999999d9999'')))-3+1,1) end as c12,                  
                   case when length(trim(to_char((a.yusum),''99999999d9999'')))<13 then null 
                   else substr(trim(to_char((a.yusum),''99999999d9999'')),length(trim(to_char((a.yusum),''99999999d9999'')))-13+1,1) end as d1,
                   case when length(trim(to_char((a.yusum),''99999999d9999'')))<12 then null 
                   else substr(trim(to_char((a.yusum),''99999999d9999'')),length(trim(to_char((a.yusum),''99999999d9999'')))-12+1,1) end as d2,
                   case when length(trim(to_char((a.yusum),''99999999d9999'')))<11 then null 
                   else substr(trim(to_char((a.yusum),''99999999d9999'')),length(trim(to_char((a.yusum),''99999999d9999'')))-11+1,1) end as d3,
                   case when length(trim(to_char((a.yusum),''99999999d9999'')))<10 then null 
                   else substr(trim(to_char((a.yusum),''99999999d9999'')),length(trim(to_char((a.yusum),''99999999d9999'')))-10+1,1) end as d4,
                   case when length(trim(to_char((a.yusum),''99999999d9999'')))<9 then null 
                   else substr(trim(to_char((a.yusum),''99999999d9999'')),length(trim(to_char((a.yusum),''99999999d9999'')))-9+1,1) end as d5,
                   case when length(trim(to_char((a.yusum),''99999999d9999'')))<8 then null 
                   else substr(trim(to_char((a.yusum),''99999999d9999'')),length(trim(to_char((a.yusum),''99999999d9999'')))-8+1,1) end as d6,
                   case when length(trim(to_char((a.yusum),''99999999d9999'')))<7 then null 
                   else substr(trim(to_char((a.yusum),''99999999d9999'')),length(trim(to_char((a.yusum),''99999999d9999'')))-7+1,1) end as d7,
                   case when length(trim(to_char((a.yusum),''99999999d9999'')))<6 then null 
                   else substr(trim(to_char((a.yusum),''99999999d9999'')),length(trim(to_char((a.yusum),''99999999d9999'')))-6+1,1) end as d8,
                   case when length(trim(to_char((a.yusum),''99999999d9999'')))<4 then null when a.yusum = 0 then null
                   else substr(trim(to_char((a.yusum),''99999999d9999'')),length(trim(to_char((a.yusum),''99999999d9999'')))-4+1,1) end as d9,
                   case when length(trim(to_char((a.yusum),''99999999d9999'')))<3 then null when a.yusum = 0 then null
                   else substr(trim(to_char((a.yusum),''99999999d9999'')),length(trim(to_char((a.yusum),''99999999d9999'')))-3+1,1) end as d10,   
                   case when length(trim(to_char((a.'||temcol||'),''99999999d9999'')))<13 then null 
                   else substr(trim(to_char((a.'||temcol||'),''99999999d9999'')),length(trim(to_char((a.'||temcol||'),''99999999d9999'')))-13+1,1) end as e1,
                   case when length(trim(to_char((a.'||temcol||'),''99999999d9999'')))<12 then null 
                   else substr(trim(to_char((a.'||temcol||'),''99999999d9999'')),length(trim(to_char((a.'||temcol||'),''99999999d9999'')))-12+1,1) end as e2,
                   case when length(trim(to_char((a.'||temcol||'),''99999999d9999'')))<11 then null 
                   else substr(trim(to_char((a.'||temcol||'),''99999999d9999'')),length(trim(to_char((a.'||temcol||'),''99999999d9999'')))-11+1,1) end as e3,
                   case when length(trim(to_char((a.'||temcol||'),''99999999d9999'')))<10 then null 
                   else substr(trim(to_char((a.'||temcol||'),''99999999d9999'')),length(trim(to_char((a.'||temcol||'),''99999999d9999'')))-10+1,1) end as e4,
                   case when length(trim(to_char((a.'||temcol||'),''99999999d9999'')))<9 then null 
                   else substr(trim(to_char((a.'||temcol||'),''99999999d9999'')),length(trim(to_char((a.'||temcol||'),''99999999d9999'')))-9+1,1) end as e5,
                   case when length(trim(to_char((a.'||temcol||'),''99999999d9999'')))<8 then null 
                   else substr(trim(to_char((a.'||temcol||'),''99999999d9999'')),length(trim(to_char((a.'||temcol||'),''99999999d9999'')))-8+1,1) end as e6,
                   case when length(trim(to_char((a.'||temcol||'),''99999999d9999'')))<7 then null 
                   else substr(trim(to_char((a.'||temcol||'),''99999999d9999'')),length(trim(to_char((a.'||temcol||'),''99999999d9999'')))-7+1,1) end as e7,
                   case when length(trim(to_char((a.'||temcol||'),''99999999d9999'')))<6 then null 
                   else substr(trim(to_char((a.'||temcol||'),''99999999d9999'')),length(trim(to_char((a.'||temcol||'),''99999999d9999'')))-6+1,1) end as e8,
                   case when length(trim(to_char((a.'||temcol||'),''99999999d9999'')))<4 then null when a.'||temcol||' = 0 then null
                   else substr(trim(to_char((a.'||temcol||'),''99999999d9999'')),length(trim(to_char((a.'||temcol||'),''99999999d9999'')))-4+1,1) end as e9,
                   case when length(trim(to_char((a.'||temcol||'),''99999999d9999'')))<3 then null when a.'||temcol||' = 0 then null
                   else substr(trim(to_char((a.'||temcol||'),''99999999d9999'')),length(trim(to_char((a.'||temcol||'),''99999999d9999'')))-3+1,1) end as e10                    
                   FROM '||qu_table||'  a  order by XLJID  ';
                                      
                   execute immediate temstrex;
                   commit;     
              end;
              
              --拆分到附表
              declare 
              cumx1 cursor1;
              kemu varchar2(60);
              idrt cursor1;
              sqlsmxup string(32767);
              a1 varchar2(60);
              a2 varchar2(60);
              a3 varchar2(60);
              a4 varchar2(60);
              a5 varchar2(60);
              a6 varchar2(60);
              a7 varchar2(60);
              a8 varchar2(60); 
              a9 varchar2(60);
              a10 varchar2(60);
              xljird int;
              cloname varchar2(60);
              begin
               sqlsmxup:='insert into '||red_table||'(XLJID) select XLJID from '||qu_table||' order by XLJID ';
               execute immediate sqlsmxup;
               commit;
               --  dbms_output.put_line(sqlsmxup);
                open cumx1 for
                  'select  c.analycolcode 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 trim(c.analycolcode) <>'''||zlast||''' order by c.analycolcode';
                  loop
                   fetch cumx1 into kemu;
                   kemu:= trim(kemu);
                   exit when cumx1%notfound;
                   open idrt for 
                     'select XLJID from '||qu_table||' order by XLJID ';  
                     loop
                      fetch idrt into xljird;
                      exit when idrt%notfound;
                      a1:='Z'||kemu||'0';a2:='Z'||kemu||'1';a3:='Z'||kemu||'2';a4:='Z'||kemu||'3';a5:='Z'||kemu||'4';
                      a6:='Z'||kemu||'5';a7:='Z'||kemu||'6';a8:='Z'||kemu||'7';a9:='Z'||kemu||'8';a10:='Z'||kemu||'9';
                      cloname := 'Y'||kemu;
                      cloname:=trim(cloname);
                         sqlsmxup:='update '||red_table||' set ('||a1||','||a2||','||a3||','||a4||','||a5||','||a6||','||a7||','
                         ||a8||','||a9||','||a10||') = (select 
                         case when length(trim(to_char(('||cloname||'),''99999999d9999'')))<13 then null 
                         else substr(trim(to_char(('||cloname||'),''99999999d9999'')),length(trim(to_char(('||cloname||'),''99999999d9999'')))-13+1,1) end as e1,
                         case when length(trim(to_char(('||cloname||'),''99999999d9999'')))<12 then null 
                         else substr(trim(to_char(('||cloname||'),''99999999d9999'')),length(trim(to_char(('||cloname||'),''99999999d9999'')))-12+1,1) end as e2,
                         case when length(trim(to_char(('||cloname||'),''99999999d9999'')))<11 then null 
                         else substr(trim(to_char(('||cloname||'),''99999999d9999'')),length(trim(to_char(('||cloname||'),''99999999d9999'')))-11+1,1) end as e3,
                         case when length(trim(to_char(('||cloname||'),''99999999d9999'')))<10 then null 
                         else substr(trim(to_char(('||cloname||'),''99999999d9999'')),length(trim(to_char(('||cloname||'),''99999999d9999'')))-10+1,1) end as e4,
                         case when length(trim(to_char(('||cloname||'),''99999999d9999'')))<9 then null 
                         else substr(trim(to_char(('||cloname||'),''99999999d9999'')),length(trim(to_char(('||cloname||'),''99999999d9999'')))-9+1,1) end as e5,
                         case when length(trim(to_char(('||cloname||'),''99999999d9999'')))<8 then null 
                         else substr(trim(to_char(('||cloname||'),''99999999d9999'')),length(trim(to_char(('||cloname||'),''99999999d9999'')))-8+1,1) end as e6,
                         case when length(trim(to_char(('||cloname||'),''99999999d9999'')))<7 then null 
                         else substr(trim(to_char(('||cloname||'),''99999999d9999'')),length(trim(to_char(('||cloname||'),''99999999d9999'')))-7+1,1) end as e7,
                         case when length(trim(to_char(('||cloname||'),''99999999d9999'')))<6 then null 
                         else substr(trim(to_char(('||cloname||'),''99999999d9999'')),length(trim(to_char(('||cloname||'),''99999999d9999'')))-6+1,1) end as e8,
                         case when length(trim(to_char(('||cloname||'),''99999999d9999'')))<4 then null when  '||cloname||' = 0 then null
                         else substr(trim(to_char(('||cloname||'),''99999999d9999'')),length(trim(to_char(('||cloname||'),''99999999d9999'')))-4+1,1) end as e9,
                         case when length(trim(to_char(('||cloname||'),''99999999d9999'')))<3 then null when  '||cloname||' = 0 then null
                         else substr(trim(to_char(('||cloname||'),''99999999d9999'')),length(trim(to_char(('||cloname||'),''99999999d9999'')))-3+1,1) end as e10 
                         from '||qu_table||' where xljid='||xljird||') where xljid = '||xljird;

                      execute immediate sqlsmxup ;
                      commit;
                     end loop;
                  end loop;  
                  close idrt;
                  close cumx1;  
              end;
         
         
  end;
  
end getreport_zg_xljex;
/