📄 getreport_zg_xlj1.prc
字号:
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,case a.direction when ''C'' then ''贷'' when ''D'' then ''借'' end as dr';
sqlstrse2:='';
sqlsup := 'update '||qutem_table1||' set(';
sqlsupcon:='(select ';
sqlinz:='insert into '||qu_table||'(yearv,mony,dayy,jz,explanation,jiesum,daisum,dr';
sqlinzcon:='select yearv,mony,dayy,jz,explanation,jiesum,daisum,dr';
sqlyh:='insert into '||qu_table||'(explanation,jiesum,daisum,dr';
sqlyhcon:=' select ''本月合计'' as byhj,debitamount as jiesum,creditamount as daisum,''平'' as dr';
sqlnhcon:=' select ''本年累计'' as byhj,debitamount as jiesum,creditamount as daisum,''平'' as dr';
sqlyhconh:=' select y.byhj,sum(y.jiesum) as jiesum,sum(y.daisum) as daisum,y.dr';
sqlnhconh:=' select y.byhj,sum(y.jiesum) as jiesum,sum(y.daisum) as daisum,y.dr';
sqlye:='';
sqlyeup:='update '||qu_table||' set yusum=(';
sqlyeupcon:=' where XLJID=:xljid';
--循环形成表字段
open c1 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||''' order by c.analycolcode';
j := 0;
loop
fetch c1 into acccode;
acccode := trim(acccode);
exit when c1%notfound;
sqlstr1 := sqlstr1||',J'||acccode||' number(20,4) default 0.0000';
sqlstr1 := sqlstr1||',Y'||acccode||' number(20,4) default 0.0000';
sqlstr2 := sqlstr2||',J'||acccode||' number(20,4) default 0.0000';
sqlstr2 := sqlstr2||',Y'||acccode||' number(20,4) default 0.0000';
sqlstr3 := sqlstr3||',J'||acccode||' number(20,4) default 0.0000';
sqlstr3 := sqlstr3||',Y'||acccode||' number(20,4) default 0.0000';
sqlstr7 := sqlstr7||',J'||acccode||' number(20,4) default 0.0000';
sqlstr7 := sqlstr7||',Y'||acccode||' number(20,4) default 0.0000';
sqlstrmx1:=sqlstrmx1||',J'||acccode;
sqlstrmx2:=sqlstrmx2||',J'||acccode;
if (hebing is not null) then --合并分录
begin
sqlstrse1:=sqlstrse1||',sum(nvl(y.J'||acccode||',0.00)) as J'||acccode;
end;
else
begin
sqlstrse1:=sqlstrse1||',nvl(y.J'||acccode||',0.00) as J'||acccode;
end;
end if;
looptem:=',case when (b.subjcode='''||acccode||''' ) then a.debitamount end as J'||acccode;
looptem:=trim(looptem);
sqlstrse1in1:=rtrim(sqlstrse1in1)||ltrim(looptem);
sqlsup:=sqlsup||'Y'||acccode||',';
sqlsupcon:=sqlsupcon||' sum(nvl(J'||acccode||',0.00)) as J'||acccode||',';
sqlinz:=sqlinz||',J'||acccode||',Y'||acccode;
sqlinzcon:=sqlinzcon||',J'||acccode||',Y'||acccode;
sqlyh:=sqlyh||',J'||acccode;
-- bootinh:=bootinh||',J'||acccode;
sqlyhcon:=sqlyhcon||',case when (b.subjcode='''||acccode||''' ) then a.debitamount end as J'||acccode;
sqlnhcon:=sqlnhcon||',case when (b.subjcode='''||acccode||''' ) then a.debitamount end as J'||acccode;
-- bootseh:=bootseh||',case when (b.subjcode='''||acccode||''' ) then a.creditamount end as J'||acccode;
sqlyhconh:=sqlyhconh||',sum(nvl(y.J'||acccode||',0.00)) as J'||acccode;
sqlnhconh:=sqlnhconh||',sum(nvl(y.J'||acccode||',0.00)) as J'||acccode;
sqlye:=sqlye||'nvl(Y'||acccode||',0.00)+';
--dbms_output.put_line(length(sqlstrse1in1));
if (j=0) then
begin
declare subh cursor1;
sname varchar2(200);
begin
open subh for
'select distinct subjname from bd_accsubj where subjcode='''||acccode||'''';
fetch subh into sname;
sname:=trim(sname);
teminsert:='
insert into '||reh_table||'(idx,ifh,hname)
values('||hh||',1,'''||sname||''')';
execute immediate teminsert;
commit;
close subh;
end;
i:=0;
zlast:=acccode;
loop
exit when i>=10;
sqlstr4:=sqlstr4||',Z'||acccode||to_char(i,'FM99')||' varchar2(1) null';
i:= i+1;
j:= j+1;
end loop;
end;
else
begin
--细表表头
hh := hh+1;
declare subh1 cursor1;
sname varchar2(200);
begin
open subh1 for
'select distinct subjname from bd_accsubj where subjcode='''||acccode||'''';
fetch subh1 into sname;
sname:=trim(sname);
teminsert:='
insert into '||reh_table||'(idx,ifh,hname)
values('||hh||',2,'''||sname||''')';
execute immediate teminsert;
commit;
close subh1;
end;
khh:=khh+1;
--细表表头
k:=0;
loop
exit when k>=10;
sqlstr5:=sqlstr5||',Z'||acccode||to_char(k,'FM99')||' varchar2(1) null';
k:= k+1;
end loop;
end;
end if;
end loop;
close c1;
sqlstr1 := sqlstr1||',daisum number(20,4) default 0.0000,yusum number(20,4) default 0.0000,dr varchar(2) null)';
sqlstr2 := sqlstr2||',daisum number(20,4) default 0.0000,yusum number(20,4) default 0.0000,dr varchar(2) null)';
sqlstr3 := sqlstr3||',daisum number(20,4) default 0.0000,yusum number(20,4) default 0.0000,dr varchar(2) null)';
sqlstr7 := sqlstr7||',daisum number(20,4) default 0.0000,yusum number(20,4) default 0.0000,dr varchar(2) null)';
sqlstr4 := sqlstr4||')';
sqlstr5 := sqlstr5||')';
sqlstrmx1:=sqlstrmx1||')';
sqlstrmx2:=sqlstrmx2||')';
sqlsup:=substr(sqlsup,1,length(sqlsup)-1);
sqlsup:=sqlsup||')=';
sqlsupcon:=substr(sqlsupcon,1,length(sqlsupcon)-1);
sqlsupcon:=sqlsupcon||' from '||qutem_table2||' where XLJID<=:xljid ) where XLJID=:id';
--插入主表的从句
sqlinz:=sqlinz||')';
-- bootinh:=bootinh||')';
sqlinzcon:=sqlinzcon||' from '||qutem_table1||' order by XLJID';
sqlyh:=sqlyh||')';
sqlyhconh:= sqlyhconh||' from(';
sqlnhconh:= sqlnhconh||' from(';
bootseh:=bootseh||' from(';
sqlye:=substr(sqlye,1,length(sqlye)-1);
sqlye:=sqlye||' as yusum';
execute immediate sqlstr1;
execute immediate sqlstr2;
execute immediate sqlstr3;
execute immediate sqlstr4;
execute immediate sqlstr5;
execute immediate sqlstr7;
XLJ_IDEN(TABLENAME =>qu_table,COLUMNNAME => 'XLJID');
XLJ_IDEN(TABLENAME =>qutem_table1,COLUMNNAME => 'XLJID');
XLJ_IDEN(TABLENAME =>qutem_table2,COLUMNNAME => 'XLJID');
XLJ_IDEN(TABLENAME =>re_table,COLUMNNAME => 'XLJID');
XLJ_IDEN(TABLENAME =>boottem,COLUMNNAME => 'XLJID');
-- XLJ_IDEN(TABLENAME =>red_table,COLUMNNAME => 'XLJID');
commit;
--建表完成
--期初 并不是每个表都有
sqlstr := 'insert into '||qu_table||'(explanation) values(''期初'')';
execute immediate sqlstr; commit;
subcondition :=' and b.subjcode in(''1''';
open cu1 for
'select c.analycolcode,''J''||c.analycolcode as kmm 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||'''';
loop
fetch cu1 into kmh,kmm;
kmh:= trim(kmh);
kmm:= trim(kmm);
exit when cu1%notfound;
subcondition:=subcondition||','''||kmh||''' ';
sqlstr := 'update '||qu_table||' set '||kmm||'=(
select sum(nvl(y.'||kmm||',0.00)) as '||kmm||' from
(select case when b.subjcode='''||kmh||''' then sum(a.debitamount) end as '||kmm||'
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='||byearv||' and
period<'||bmonv||' and a.dr=0 ) and b.subjcode LIKE '''||rootkemu||'%'' and b.subjcode <>'''||rootkemu||'''
--and b.subjcode not in(''5502'',''55020601'',''55020602'',''550261'',''55020801'',''550236'',''550231'')
and a.pk_corp='||pk_corp||'
group by b.subjcode) y) where explanation=''期初''';
execute immediate sqlstr;
commit;
end loop;
subcondition:=subcondition||') ';
sqlstr := 'update '||qu_table||' set (jiesum,daisum,dr) =(
select sum(a.debitamount) as s1,sum(a.creditamount) as s2, ''平'' as dr
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='||byearv||' and
period<'||bmonv||' and a.dr=0 ) and b.subjcode LIKE '''||rootkemu||'%'' and b.subjcode <>'''||rootkemu||''' '||bumenstrcon||'
--and b.subjcode not in(''5502'',''55020601'',''55020602'',''550261'',''55020801'',''550236'',''550231'')
'||subcondition||'
and a.pk_corp='||pk_corp||')
where explanation=''期初''';
--dbms_output.put_line(sqlstr);
execute immediate sqlstr;
commit;
close cu1;
--期初完成
--形成明细语句
if (hebing is not null) then --合并分录
begin
sqlstrse1in1:=sqlstrse1in1||' 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=:byearv and period=:bmonv 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.dr order by y.yearv,y.mony,y.dayy,y.jz';
end;
else
begin
sqlstrse1in1:=sqlstrse1in1||' 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=:byearv and period=:bmonv 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
order by y.yearv,y.mony,y.dayy,y.jz';
end;
end if;
--形成月末结转语句
sqlym:=' insert into '||qu_table||'(yearv,mony,dayy,jz,explanation,daisum,dr)
select y.yearv,y.mony,y.dayy,y.jz,y.explanation,sum(y.daisum) as daisum,''平'' as dr
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.creditamount as daisum 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
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -