分支机构预算申请控制报表

create or replace procedure rp_companybudgetcontrol(in_querydate varchar2, --当前时间
in_orgLevelCode varchar2,
ResultCursor out dr_package_sinosteel.rc_report) as
--分支机构费用预算资金申请控制表

--固定运营成本项目参数
a_amount number(30, 6);
a_planamount number(30, 6);
--变动管理费用项目参数
b_amount number(30, 6);
b_planamount number(30, 6);
--保单获取成本项目参数
c_amount number(30, 6);
c_planamount number(30, 6);
--公司级别编号前十位数
suborglevelcode varchar2(50);
--公司级别编号
orglevelcode varchar2(50);
--特定时间的年预算周期
perioid number;
--定义账户类型(部分属性)
type orgInfo is record(
orgid number,
orgname varchar2(75));
org orgInfo;
--定义一个游标 查询所有公司
CURSOR v_cursor1 is
select o.id, o.orgname
from bs_organization o
where o.status = 1
and o.orglevelcode like in_orgLevelCode || '%'
and o.id in (select t.orgid from tb_budgetitem t where t.status=1)
and o.id != 1;--不需显示总公司

begin
delete from temp_rp_companybudgetcontrol;
commit;
begin
--获得特定时间的年预算周期
select per.perid
into perioid
from (select tper.id perid
from tb_budgetperiod tper
where tper.status = 1
and to_date(tper.startdate, 'YYYY-MM-DD') <=
to_date(in_querydate, 'YYYY-MM-DD')
and to_date(tper.enddate, 'YYYY-MM-DD') >=
to_date(in_querydate, 'YYYY-MM-DD')) per;
exception
when no_data_found then
perioid := 0;
end;
open v_cursor1;
--对公司进行循环遍历
fetch v_cursor1
into org;
while v_cursor1%found loop
begin
--获得某公司度固定运营成本项目本年预算额度
select aa.amount
into a_amount
from (select sum(tdet.amount) amount
from tb_budgetdetail tdet
where tdet.budgetitemcode = 0001
and tdet.status = 1
and tdet.budgetid in
(select tbud.id --获得某公司的非汇总预算
from tb_budget tbud
where tbud.status = 1
and tbud.orgid = org.orgid
and tbud.gatherbatchno is null
and tbud.perioddetailid in
(select tpde.id
from tb_budgetperioddetail tpde
where tpde.status = 1
and tpde.budgetperiodid = perioid))
and tdet.batchno =
(select max(a.batchno)
from tb_budgetdetail a
where a.status = 1
and a.budgetitemcode = 0001
and a.budgetid = tdet.budgetid
and a.budgetstatus in (7))) aa;
--固定运营成本项目的已申请额度（从计划明细表中取数据）
select aaa.amount
into a_planamount
from (select sum(tpd.amount) amount
from tp_plandetail tpd
where tpd.status = 1
and tpd.planitemcode = 0001
and tpd.planid in
(select tp.id
from tp_plan tp
where tp.status = 1
and tp.orgid = org.orgid
and tp.budgetid in
(select tbud.id --获得某公司的非汇总预算
from tb_budget tbud
where tbud.status = 1
and tbud.orgid = org.orgid
and tbud.gatherbatchno is null
and tbud.perioddetailid in
(select tpde.id
from tb_budgetperioddetail tpde
where tpde.status = 1
and tpde.budgetperiodid = perioid))
and tp.planstatus in (7)
and ((tp.planstartdate <=
to_date(in_querydate, 'YYYY-MM-DD') and
tp.planenddate >=
to_date(in_querydate, 'YYYY-MM-DD')) or
tp.planenddate <=
to_date(in_querydate, 'YYYY-MM-DD')))
and tpd.batchno =
(select max(tpdet.batchno)
from tp_plandetail tpdet
where tpdet.planid = tpd.planid)) aaa;
--获得某公司本年度变动管理费用项目的预算（从预算明细表中取数据）
select bb.amount
into b_amount
from (select sum(tdet.amount) amount
from tb_budgetdetail tdet
where tdet.budgetitemcode = 0002
and tdet.status = 1
and tdet.budgetid in
(select tbud.id --获得某公司的非汇总预算
from tb_budget tbud
where tbud.status = 1
and tbud.orgid = org.orgid
and tbud.gatherbatchno is null
and tbud.perioddetailid in
(select tpde.id
from tb_budgetperioddetail tpde
where tpde.status = 1
and tpde.budgetperiodid = perioid))
and tdet.batchno =
(select max(a.batchno)
from tb_budgetdetail a
where a.status = 1
and a.budgetitemcode = 0002
and a.budgetid = tdet.budgetid
and a.budgetstatus in (7))) bb;
--获得某公司变动管理费用项目的已申请额度（从计划明细表中取数据）
select bbb.amount
into b_planamount
from (select sum(tpd.amount) amount
from tp_plandetail tpd
where tpd.status = 1
and tpd.planitemcode = 0002
and tpd.planid in
(select tp.id
from tp_plan tp
where tp.status = 1
and tp.orgid = org.orgid
and tp.budgetid in
(select tbud.id --获得某公司的非汇总预算
from tb_budget tbud
where tbud.status = 1
and tbud.orgid = org.orgid
and tbud.gatherbatchno is null
and tbud.perioddetailid in
(select tpde.id
from tb_budgetperioddetail tpde
where tpde.status = 1
and tpde.budgetperiodid = perioid))
and tp.planstatus in (7)
and ((tp.planstartdate <=
to_date(in_querydate, 'YYYY-MM-DD') and
tp.planenddate >=
to_date(in_querydate, 'YYYY-MM-DD')) or
tp.planenddate <=
to_date(in_querydate, 'YYYY-MM-DD')))
and tpd.batchno =
(select max(tpdet.batchno)
from tp_plandetail tpdet
where tpdet.planid = tpd.planid)) bbb;

--获得某公司本年度保单获取成本项目的预算（从预算明细表中取数据）
select cc.amount
into c_amount
from (select sum(tdet.amount) amount
from tb_budgetdetail tdet
where tdet.budgetitemcode = 0005--取'保单获取成本'
and tdet.status = 1
and tdet.budgetid in
(select tbud.id --获得某公司的非汇总预算
from tb_budget tbud
where tbud.status = 1
and tbud.orgid = org.orgid
and tbud.gatherbatchno is null
and tbud.perioddetailid in
(select tpde.id
from tb_budgetperioddetail tpde
where tpde.status = 1
and tpde.budgetperiodid = perioid))
and tdet.batchno =
(select max(a.batchno)
from tb_budgetdetail a
where a.status = 1
and a.budgetitemcode = 0005--yylic修改:应取'保单获取成本'
and a.budgetid = tdet.budgetid
and a.budgetstatus in (7))) cc;

--获得某公司保单获取成本项目的已申请额度(从计划明细表中取数据)
select ccc.amount
into c_planamount
from (select sum(tpd.amount) amount
from tp_plandetail tpd
where tpd.status = 1
and tpd.planitemcode = 0005--yylic修改:应取'保单获取成本'
and tpd.planid in
(select tp.id
from tp_plan tp
where tp.status = 1
and tp.orgid = org.orgid
and tp.budgetid in
(select tbud.id --获得某公司的非汇总预算
from tb_budget tbud
where tbud.status = 1
and tbud.orgid = org.orgid
and tbud.gatherbatchno is null
and tbud.perioddetailid in
(select tpde.id
from tb_budgetperioddetail tpde
where tpde.status = 1
and tpde.budgetperiodid = perioid))
and tp.planstatus in (7)
and ((tp.planstartdate <=
to_date(in_querydate, 'YYYY-MM-DD') and
tp.planenddate >=
to_date(in_querydate, 'YYYY-MM-DD')) or
tp.planenddate <=
to_date(in_querydate, 'YYYY-MM-DD')))
and tpd.batchno =
(select max(tpdet.batchno)
from tp_plandetail tpdet
where tpdet.planid = tpd.planid)) ccc;

end;
--截取公司编号前十位
select substr(bso.orglevelcode, 0, 10),bso.orglevelcode
into suborglevelcode,orglevelcode
from bs_organization bso
where bso.id = org.orgid;
--将查询结果插入临时表中
insert into temp_rp_companybudgetcontrol
values
(org.orgname,
a_amount,
a_planamount,
b_amount,
b_planamount,
c_amount,
c_planamount,
orglevelcode,
suborglevelcode
);

fetch v_cursor1
into org;
end loop;
close v_cursor1;

open ResultCursor for
select t.orgname,
t.a_amount,
t.a_useamount,
t.b_amount,
t.b_useamount,
t.c_amount,
t.c_useamount,
t.suborglevelcode,
t.orglevelcode
from temp_rp_companybudgetcontrol t
order by t.suborglevelcode ,t.orglevelcode;
end;