getreport_zg_xlj1.prc

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

               year=:yearbtem and period=:monbtem and dr=0) and b.SUBJCODE  LIKE '''||rootkemu||'%''  '||bumenstrcon||'
               '||subcondition||'  and a.pk_corp='||pk_corp||' and a.debitamount=0 order by a.PREPAREDDATEV,c.NO,a.creditamount) y
               group by y.yearv,y.mony,y.dayy,y.jz,y.explanation order by y.yearv,y.mony,y.dayy,y.jz';   
          --形成本月合计语句
             sqlyhcon:=sqlyhcon||' FROM GL_detail a left join bd_accsubj b on a.pk_accsubj = b.pk_accsubj  '||bumenstr||'
               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=:yearbtem and period=:monbtem and dr=0) and b.SUBJCODE  LIKE '''||rootkemu||'%''  '||bumenstrcon||'
               '||subcondition||'  and a.pk_corp='||pk_corp||' and a.debitamount<>0 order by a.PREPAREDDATEV,c.NO,a.creditamount) y
               group by y.byhj,y.dr ';     
          --形成本年累计语句
             sqlnhcon:=sqlnhcon||' FROM GL_detail a left join bd_accsubj b on a.pk_accsubj = b.pk_accsubj  '||bumenstr||'
               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=:yearbtem and period<=:monbtem and dr=0) and b.SUBJCODE  LIKE '''||rootkemu||'%'' 
               '||subcondition||'  and a.pk_corp='||pk_corp||' and a.debitamount<>0 order by a.PREPAREDDATEV,c.NO,a.creditamount) y
               group by y.byhj,y.dr ';   
          --形成分录的月末结转插入临时表语句的查询后部分
              bootseh := 'select :yearbtem as yearv,to_char(to_date(a.PREPAREDDATEV,''YYYY-MM-DD hh24:mi:ss''),''MM'') AS MONY,
              to_char(to_date(a.PREPAREDDATEV,''YYYY-MM-DD hh24:mi:ss''),''DD'') AS DAYY,''记账-''||c.NO AS jz,
              a.explanation,a.debitamount as jiesum,a.creditamount as daisum,b.subjcode 
              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 
               '||bumenstr||'  where a.pk_voucher in (select pk_voucher from gl_voucher where year=:yearbtem and period=:month and dr=0)
              '||subcondition||'  and a.pk_corp='||pk_corp||' and a.debitamount=0   '||bumenstrcon;
              bootsehfirst := 'select y.yearv,y.mony,y.dayy,y.jz,y.explanation,y.jiesum,y.daisum,y.subjcode from(
              select :yearbtem as yearv,to_char(to_date(a.PREPAREDDATEV,''YYYY-MM-DD hh24:mi:ss''),''MM'') AS MONY,
              to_char(to_date(a.PREPAREDDATEV,''YYYY-MM-DD hh24:mi:ss''),''DD'') AS DAYY,''记账-''||c.NO AS jz,
              a.explanation,a.debitamount as jiesum,a.creditamount as daisum,b.subjcode 
              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 
               '||bumenstr||'  where a.pk_voucher in (select pk_voucher from gl_voucher where year=:yearbtem and period=:month and dr=0)
              '||subcondition||'  and a.pk_corp='||pk_corp||' and a.debitamount<>0   '||bumenstrcon ||'
              order by a.PREPAREDDATEV,a.detailindex desc) y where rownum=1';
              
              
                
          --开始明细插
         yearbtem:=byearv;
         while yearbtem<=eyearv loop
         
          if(byearv=eyearv) then
           begin
             monbtem:=bmonv;
             monetem:=emonv;
             while monbtem<=monetem loop
               sqlsumin:= sqlstrmx1||sqlstrse1||sqlstrse1in1;
               execute immediate sqlsumin using yearbtem,yearbtem,monbtem;
               commit;
               sqlsumin:= sqlstrmx2||sqlstrse1||sqlstrse1in1;
               execute immediate sqlsumin using yearbtem,yearbtem,monbtem;
               commit;   
               --开始计算余额
                   open cu2 for
                    ' select XLJID from '||qutem_table1||' order by XLJID';    
                   loop 
                   fetch cu2 into dix;
                    sqlsupz:=sqlsup||sqlsupcon;
                    execute immediate sqlsupz using dix,dix;
                    commit;
                   exit when cu2%notfound;
                   end loop;   
                   sqlinzz:=sqlinz||sqlinzcon;
                   execute immediate sqlinzz;
                   commit;
                   temsql:= 'delete from '||qutem_table1;
                   execute immediate temsql;
                   commit;  
                   temsql:= 'delete from '||qutem_table2;
                   execute immediate temsql;
                   commit;
                   if(hebing is not null) then
                    begin
                      execute immediate sqlym using yearbtem,yearbtem,monbtem;
                    end;
                    else
                    begin
                      bootcon:=bootinh||bootsehfirst;
                      execute immediate bootcon using yearbtem,yearbtem,monbtem;
                      commit;                    
                      bootcon:=bootinh||bootseh;
                      execute immediate bootcon using yearbtem,yearbtem,monbtem;
                      commit;
                    end;
                    end if;  
                   commit;  
                   sqlyhz:= sqlyh||sqlyhconh||sqlyhcon;
                   execute immediate sqlyhz using yearbtem,monbtem;
                   commit;
                   sqlnhz:= sqlyh||sqlnhconh||sqlnhcon;
                   execute immediate sqlnhz using yearbtem,monbtem;
                   commit;                   
               monbtem:=monbtem+1;                           
             end loop;
           end;
           else
           begin
             if(yearbtem=byearv) then --开始年
           begin
             monbtem:=bmonv;
             monetem:=12;
             while monbtem<=monetem loop
               sqlsumin:= sqlstrmx1||sqlstrse1||sqlstrse1in1;
               execute immediate sqlsumin using yearbtem,yearbtem,monbtem;
               commit;
               sqlsumin:= sqlstrmx2||sqlstrse1||sqlstrse1in1;
               execute immediate sqlsumin using yearbtem,yearbtem,monbtem;
               commit;   
               --开始计算余额
                   open cu2 for
                    ' select XLJID from '||qutem_table1||' order by XLJID';    
                   loop 
                   fetch cu2 into dix;
                    sqlsupz:=sqlsup||sqlsupcon;
                    execute immediate sqlsupz using dix,dix;
                    commit;
                   exit when cu2%notfound;
                   end loop;   
                   sqlinzz:=sqlinz||sqlinzcon;
                   execute immediate sqlinzz;
                   commit;
                   temsql:= 'delete from '||qutem_table1;
                   execute immediate temsql;
                   commit;  
                   temsql:= 'delete from '||qutem_table2;
                   execute immediate temsql;
                   commit;
                   execute immediate sqlym using yearbtem,yearbtem,monbtem;
                   commit;  
                   sqlyhz:= sqlyh||sqlyhconh||sqlyhcon;
                   execute immediate sqlyhz using yearbtem,monbtem;
                   commit;
                   sqlnhz:= sqlyh||sqlnhconh||sqlnhcon;
                   execute immediate sqlnhz using yearbtem,monbtem;
                   commit;                   
               monbtem:=monbtem+1;                           
             end loop;
           end;
              end if;
             if(yearbtem=eyearv) then --结束年
           begin
             monbtem:=1;
             monetem:=emonv;
             while monbtem<=monetem loop
               sqlsumin:= sqlstrmx1||sqlstrse1||sqlstrse1in1;
               execute immediate sqlsumin using yearbtem,yearbtem,monbtem;
               commit;
               sqlsumin:= sqlstrmx2||sqlstrse1||sqlstrse1in1;
               execute immediate sqlsumin using yearbtem,yearbtem,monbtem;
               commit;   
               --开始计算余额
                   open cu2 for
                    ' select XLJID from '||qutem_table1||' order by XLJID';    
                   loop 
                   fetch cu2 into dix;
                    sqlsupz:=sqlsup||sqlsupcon;
                    execute immediate sqlsupz using dix,dix;
                    commit;
                   exit when cu2%notfound;
                   end loop;   
                   sqlinzz:=sqlinz||sqlinzcon;
                   execute immediate sqlinzz;
                   commit;
                   temsql:= 'delete from '||qutem_table1;
                   execute immediate temsql;
                   commit;  
                   temsql:= 'delete from '||qutem_table2;
                   execute immediate temsql;
                   commit;
                   execute immediate sqlym using yearbtem,yearbtem,monbtem;
                   commit;  
                   sqlyhz:= sqlyh||sqlyhconh||sqlyhcon;
                   execute immediate sqlyhz using yearbtem,monbtem;
                   commit;
                   sqlnhz:= sqlyh||sqlnhconh||sqlnhcon;
                   execute immediate sqlnhz using yearbtem,monbtem;
                   commit;                   
               monbtem:=monbtem+1;                           
             end loop;
           end;
              end if;
             if((yearbtem>byearv) and (yearbtem<eyearv)) then --中间年
           begin
             monbtem:=1;
             monetem:=12;
             while monbtem<=monetem loop
               sqlsumin:= sqlstrmx1||sqlstrse1||sqlstrse1in1;
               execute immediate sqlsumin using yearbtem,yearbtem,monbtem;
               commit;
               sqlsumin:= sqlstrmx2||sqlstrse1||sqlstrse1in1;
               execute immediate sqlsumin using yearbtem,yearbtem,monbtem;
               commit;   
               --开始计算余额
                   open cu2 for
                    ' select XLJID from '||qutem_table1||' order by XLJID';    
                   loop 
                   fetch cu2 into dix;
                    sqlsupz:=sqlsup||sqlsupcon;
                    execute immediate sqlsupz using dix,dix;
                    commit;
                   exit when cu2%notfound;
                   end loop;   
                   sqlinzz:=sqlinz||sqlinzcon;
                   execute immediate sqlinzz;
                   commit;
                   temsql:= 'delete from '||qutem_table1;
                   execute immediate temsql;
                   commit;  
                   temsql:= 'delete from '||qutem_table2;
                   execute immediate temsql;
                   commit;
                   execute immediate sqlym using yearbtem,yearbtem,monbtem;
                   commit;  
                   sqlyhz:= sqlyh||sqlyhconh||sqlyhcon;
                   execute immediate sqlyhz using yearbtem,monbtem;
                   commit;
                   sqlnhz:= sqlyh||sqlnhconh||sqlnhcon;
                   execute immediate sqlnhz using yearbtem,monbtem;
                   commit;                   
               monbtem:=monbtem+1;                           
             end loop;
           end;
              end if;

           end;
           end if;
           yearbtem:= yearbtem+1;
         end loop;


           --计算余额合计
           declare 
           cuye cursor1;
           dix1 int;
           yuee number(20,4);
           sqlyesq string(32767);
           sqlyhups string(32767);
           yev varchar2(4);
           monv varchar2(2);
           dav varchar2(2);
           jzv varchar2(60);
           begin
           sqlyesq:= sqlyeup||' select '||sqlye||' from '||qu_table||' where XLJID=:xljid ) '||sqlyeupcon;
           open cuye for
           'select xljid,'||sqlye||',yearv,mony,dayy,jz  from '||qu_table||' order by xljid';
           loop 
            fetch cuye into dix1,yuee,yev,monv,dav,jzv;
            exit when cuye%notfound;
            execute immediate sqlyesq using dix1,dix1;
            commit;
            --更新 如果合并分录的时候
            if(hebing is not null) then 
             begin