procedure Proc_GetSupTotBalance
(p_Month varchar,
p_EndMonth varchar,
p_SupNo varchar,
p_AccNo varchar,
p_OrgType varchar, --p_DataType=1时,是SupEndNo
p_OrgList varchar, --p_DataType=1时,是含税和不含税
p_DataType varchar,
returnInfo out PKG_RPT.my_cursor)
as
p_PreMonth varchar(6);
P_Sql varchar(500);
p_count integer;
begin
dbms_output.put_line(p_OrgType||p_OrgList||p_SupNo||p_AccNo);
delete tmp_supbizrpt;
delete tmp_conaccrpt;
delete temp_hqdis;
P_Sql :=' insert into temp_hqdis(FDCNO)'
||' select fsupno from ts_supplier';
if p_SupNo<>'000000' then
P_Sql :=P_Sql ||' where fsupno between '''||p_SupNo||''' and '''||p_OrgType||'''';
else p_sql :=p_sql;
end if;
execute immediate p_sql;
select to_char(max(fmonth)) into p_PreMonth from ts_month where fmonth<p_Month;
if p_DataType='1' then
if p_OrgList='1' then
insert into tmp_supbizrpt(fmemo1,forgno,fnoamt,famt)
select fmonth,fsupno,famt fpreamt,0 famt
from TH_SupBalance a,temp_hqdis b
where fmonth=p_PreMonth and a.fsupno=b.FDCNO; insert into tmp_supbizrpt(fmemo1,forgno,fnoamt,famt)
select fmonth,fsupno,0 fpreamt,famt
from TH_SupBalance a,temp_hqdis b
where fmonth=p_EndMonth and a.fsupno=b.FDCNO;
--进货验收
insert into tmp_supbizrpt(forgno,fselamt)
select c.fsupno,round(sum(f.fqtybase*f.fbuyprice),2)
from tc_hqreceive_head a,tc_hqreceive_detail f,ts_contract b,
ts_supplier c,ts_month d,ts_organize e,temp_hqdis g
where a.fbillid=f.fbillid and trim(a.fconno)=b.fconno and b.fsupid=c.fsupid
and a.fbillstate between 2 and 7 and a.forgno=e.forgno
and c.fsupno=g.FDCNO
and a.forgno not in(select a.forgno from ts_organize a,ts_store b
where a.forgid=b.forgid and b.fstorekind=3)
and substr(a.forgno,1,1)<>'9' and d.fmonth between p_Month and p_EndMonth
and a.FBILLCREDATE between d.faccbegdate and d.faccenddate
group by c.fsupno;
--退货
insert into tmp_supbizrpt(forgno,fselamt)
select c.fsupno,round(sum(f.frealqty*f.fbuyprice),2)*-1
from tc_hqreturn_head a,tc_hqreturn_detail f,ts_contract b,
ts_supplier c,ts_month d,ts_organize e,temp_hqdis g
where a.fbillid=f.fbillid and trim(a.fconno)=b.fconno and b.fsupid=c.fsupid
and a.fbillstate between 2 and 7 and a.forgno=e.forgno and c.fsupno=g.FDCNO
and a.forgno not in(select a.forgno from ts_organize a,ts_store b
where a.forgid=b.forgid and b.fstorekind=3)
and substr(a.forgno,1,1)<>'9' and d.fmonth between p_Month and p_EndMonth
and a.FBILLCREDATE between d.faccbegdate and d.faccenddate
group by c.fsupno;
--到票数据
insert into tmp_supbizrpt(forgno,ftaxamt)
select a.fconno,round(sum(h.fqtybase*h.fbuyprice),2)
from tc_invocheck_head a,tc_invocheck_ord c,Tc_InvoCheck_User d,
ts_month e,ts_supplier f,tc_hqreceive_head g,tc_hqreceive_detail h,temp_hqdis i
where a.fbillid=c.fbillid and a.fbillid=d.fbillid and d.fusertype=4
and a.fbillstate between 4 and 6 and a.fconno=f.fsupno
and c.frecno=g.fbillno and g.fbillid=h.fbillid and f.fsupno=i.FDCNO
and to_date(to_char(d.foperdate,'yyyy-mm-dd'),'yyyy-mm-dd') between e.faccbegdate and e.faccenddate
and e.fmonth between p_Month and p_EndMonth
group by a.fconno;
--到票数据
insert into tmp_supbizrpt(forgno,ftaxamt)
select a.fconno,round(sum(h.frealqty*h.fbuyprice),2)*-1
from tc_invocheck_head a,tc_invocheck_ord c,Tc_InvoCheck_User d,
ts_month e,ts_supplier f,tc_hqreturn_head g,tc_hqreturn_detail h,temp_hqdis i
where a.fbillid=c.fbillid and a.fbillid=d.fbillid and d.fusertype=4
and a.fbillstate between 4 and 6 and a.fconno=f.fsupno
and c.frecno=g.fbillno and g.fbillid=h.fbillid and f.fsupno=i.FDCNO
and to_date(to_char(d.foperdate,'yyyy-mm-dd'),'yyyy-mm-dd') between e.faccbegdate and e.faccenddate
and e.fmonth between p_Month and p_EndMonth
group by a.fconno;
else
insert into tmp_supbizrpt(fmemo1,forgno,fnoamt,famt)
select fmonth,fsupno,Fnoamt fpreamt,0 famt
from TH_SupBalance a,temp_hqdis b
where fmonth=p_PreMonth and a.fsupno=b.FDCNO; insert into tmp_supbizrpt(fmemo1,forgno,fnoamt,famt)
select fmonth,fsupno,0 fpreamt,Fnoamt
from TH_SupBalance a,temp_hqdis b
where fmonth=p_EndMonth and a.fsupno=b.FDCNO;
--进货验收
insert into tmp_supbizrpt(forgno,fselamt)
select c.fsupno,round(sum(f.fqtybase*f.fbuyprice/(1+f.fbuyvat)),2)
from tc_hqreceive_head a,tc_hqreceive_detail f,ts_contract b,
ts_supplier c,ts_month d,ts_organize e,temp_hqdis g
where a.fbillid=f.fbillid and trim(a.fconno)=b.fconno and b.fsupid=c.fsupid
and a.fbillstate between 2 and 7 and a.forgno=e.forgno and c.fsupno=g.FDCNO
and a.forgno not in(select a.forgno from ts_organize a,ts_store b
where a.forgid=b.forgid and b.fstorekind=3)
and substr(a.forgno,1,1)<>'9' and d.fmonth between p_Month and p_EndMonth
and a.FBILLCREDATE between d.faccbegdate and d.faccenddate
group by c.fsupno;
--退货
insert into tmp_supbizrpt(forgno,fselamt)
select c.fsupno,round(sum(f.frealqty*f.fbuyprice/(1+f.fbuyvat)),2)*-1
from tc_hqreturn_head a,tc_hqreturn_detail f,ts_contract b,
ts_supplier c,ts_month d,ts_organize e,temp_hqdis g
where a.fbillid=f.fbillid and trim(a.fconno)=b.fconno and b.fsupid=c.fsupid
and a.fbillstate between 2 and 7 and a.forgno=e.forgno and c.fsupno=g.FDCNO
and a.forgno not in(select a.forgno from ts_organize a,ts_store b
where a.forgid=b.forgid and b.fstorekind=3)
and substr(a.forgno,1,1)<>'9' and d.fmonth between p_Month and p_EndMonth
and a.FBILLCREDATE between d.faccbegdate and d.faccenddate
group by c.fsupno;
--到票数据
insert into tmp_supbizrpt(forgno,ftaxamt)
select a.fconno,round(sum(h.fqtybase*h.fbuyprice/(1+h.fbuyvat)),2)
from tc_invocheck_head a,tc_invocheck_ord c,Tc_InvoCheck_User d,
ts_month e,ts_supplier f,tc_hqreceive_head g,tc_hqreceive_detail h,temp_hqdis i
where a.fbillid=c.fbillid and a.fbillid=d.fbillid and d.fusertype=4
and a.fbillstate between 4 and 6 and a.fconno=f.fsupno and f.fsupno=i.FDCNO
and c.frecno=g.fbillno and g.fbillid=h.fbillid
and to_date(to_char(d.foperdate,'yyyy-mm-dd'),'yyyy-mm-dd') between e.faccbegdate and e.faccenddate
and e.fmonth between p_Month and p_EndMonth
group by a.fconno;
--到票数据
insert into tmp_supbizrpt(forgno,ftaxamt)
select a.fconno,round(sum(h.frealqty*h.fbuyprice/(1+h.fbuyvat)),2)*-1
from tc_invocheck_head a,tc_invocheck_ord c,Tc_InvoCheck_User d,
ts_month e,ts_supplier f,tc_hqreturn_head g,tc_hqreturn_detail h,temp_hqdis i
where a.fbillid=c.fbillid and a.fbillid=d.fbillid and d.fusertype=4
and a.fbillstate between 4 and 6 and a.fconno=f.fsupno and f.fsupno=i.FDCNO
and c.frecno=g.fbillno and g.fbillid=h.fbillid
and to_date(to_char(d.foperdate,'yyyy-mm-dd'),'yyyy-mm-dd') between e.faccbegdate and e.faccenddate
and e.fmonth between p_Month and p_EndMonth
group by a.fconno;
end if;
else if p_DataType='2' then
insert into tmp_supbizrpt(fmemo1,forgno,fnoamt,famt)
select fmonth,forgno,famt fpreamt,0 famt
from TH_SupBalance where fmonth=p_PreMonth; insert into tmp_supbizrpt(fmemo1,forgno,fnoamt,famt)
select fmonth,forgno,0 fpreamt,famt
from TH_SupBalance where fmonth=p_EndMonth;
--进货验收
insert into tmp_supbizrpt(forgno,fselamt)
select a.forgno,sum(a.ftotamt)
from tc_hqreceive_head a,ts_contract b,ts_supplier c,ts_month d,ts_organize e
where trim(a.fconno)=b.fconno and b.fsupid=c.fsupid and a.fbillstate between 2 and 7
and a.forgno=e.forgno and a.forgno not in(select a.forgno from ts_organize a,ts_store b
where a.forgid=b.forgid and b.fstorekind=3)
and substr(a.forgno,1,1)<>'9' and d.fmonth between p_Month and p_EndMonth
and a.FBILLCREDATE between d.faccbegdate and d.faccenddate
group by a.forgno;
--退货
insert into tmp_supbizrpt(forgno,fselamt)
select a.forgno,sum(a.ftotamt)*-1
from tc_hqreturn_head a,ts_contract b,ts_supplier c,ts_month d,ts_organize e
where trim(a.fconno)=b.fconno and b.fsupid=c.fsupid and a.fbillstate between 2 and 7
and a.forgno=e.forgno and a.forgno not in(select a.forgno from ts_organize a,ts_store b
where a.forgid=b.forgid and b.fstorekind=3)
and substr(a.forgno,1,1)<>'9' and d.fmonth between p_Month and p_EndMonth
and a.FBILLCREDATE between d.faccbegdate and d.faccenddate
group by a.forgno;
--到票数据
insert into tmp_supbizrpt(forgno,ftaxamt)
select a.forgno,sum(a.famt) from
(select g.forgno,c.finvno,b.famt
from tc_invocheck_head a,tc_invocheck_ord b,tc_invocheck_inv c,
Tc_InvoCheck_User d,ts_month e,ts_supplier f,ts_organize g
where a.fbillid=b.fbillid and a.fbillid=c.fbillid and a.fbillid=d.fbillid and d.fusertype=4
and a.fbillstate between 4 and 6 and a.fconno=f.fsupno and b.frecorgno=g.forgno
and to_date(to_char(d.foperdate,'yyyy-mm-dd'),'yyyy-mm-dd') between e.faccbegdate and e.faccenddate
and e.fmonth between p_Month and p_EndMonth)a
group by a.forgno;
end if;
select count(9) into p_count from tmp_supbizrpt;
if p_count>0 then
insert into tmp_conaccrpt(forgno,fpayamt,famt,fnumber1,fnumber2)
select forgno,sum(nvl(fnoamt,0)),sum(nvl(famt,0)),sum(nvl(fselamt,0)),sum(nvl(ftaxamt,0))
from tmp_supbizrpt
group by forgno;
insert into tmp_conaccrpt(forgno,fpayamt,famt,fnumber1,fnumber2)
select '合计',sum(nvl(fnoamt,0)),sum(nvl(famt,0)),sum(nvl(fselamt,0)),sum(nvl(ftaxamt,0))
from tmp_supbizrpt;
end if ;
commit;
if p_DataType='1' then
open returnInfo for
select a.forgno fsupno,b.faccountno,b.fsupname,fpayamt fpreamt,
a.fnumber1 frecamt,a.fnumber2 finvcamt,famt
from tmp_conaccrpt a
left join ts_supplier b on a.forgno=b.fsupno
order by 1;
else if p_DataType='2' then
open returnInfo for
select a.forgno,b.forgname,fpayamt fpreamt,
a.fnumber1 frecamt,a.fnumber2 finvcamt,famt
from tmp_conaccrpt a
left join ts_organize b on a.forgno=b.forgno
order by 1;
end if;
end if ;
end if;
end;
提示 未执行语句句柄， 24338错误
撤到语言板块去提问了。
1.右击存储过程名称
2.选择“TEST”
3.在弹出窗口中的存储过程参数列表栏（窗口底部）的“Value”中填入值
4.F8运行，若出错，会自动定位到出错的行
如果想设断点的话，在存储过程中想要设置断点的行，单击行号，会出现红色的东西，再点击“start debugger”就会自动跳到你设置断点的部分了！！！