createorreplaceprocedure BIS_PROJECT_BUDGET(P_DATE inDATE) is
v_date varchar2(10);
v_year varchar2(10);
v_month varchar2(10);
v_month_name varchar2(10);
v_sql varchar2(4000);
begin
--取当日的上个月的最后一天 每个月首日统计上个月
select to_char((last_day(add_months(P_DATE,-1))),'yyyy-mm-dd') last_day,
to_char((last_day(add_months(P_DATE,'yyyy') years,'mm') months
into v_date,v_year,v_month
from dual;
select
case when v_month='01'then 'bt.JAN_KPI'
when v_month='02'then 'bt.FEB_KPI'
when v_month='03'then 'bt.MAR_KPI'
when v_month='04'then 'bt.APR_KPI'
when v_month='05'then 'bt.MAY_KPI'
when v_month='06'then 'bt.JUN_KPI'
when v_month='07'then 'bt.JUL_KPI'
when v_month='08'then 'bt.AUG_KPI'
when v_month='09'then 'bt.SEP_KPI'
when v_month='10'then 'bt.OCT_KPI'
when v_month='11'then 'bt.NOV_KPI'
else'DEC_KPI'endinto v_month_name
from dual;
delete BIS_SECOND_ZS_PRO where PRO_MONTH=to_number(v_month) and PRO_YEAR = v_year;
commit;
v_sql:= '
insert into BIS_SECOND_ZS_PRO bp(
BIS_SECOND_ZS_PRO_ID,
bis_project_id,
PRO_YEAR,
PRO_MONTH,
EMPTY_PRE,
BUDGETYEAR_MONTH,
CREATOR,
CREATED_DATE,
RECORD_VERSION
)
select
sys_guid(),
m.bis_project_id,
'||v_year||' as years,
to_number('||v_month||') as months,
m.budget_month,
y.budget_year,
''system'',
sysdate,
dbms_random.value(0,100000)
from
(
select
r.bis_project_id,
nvl(decode(r.total_square,round(('||v_month_name||' - r.open_square/r.total_square)*r.total_square,2)),0) budget_month
from
(select pt.bis_project_id,
pt.short_name,
pt.total_square,
nvl(sum(af.open_square_total),0) open_square
from
(select p.bis_project_id,
p.short_name,
nvl(sum(vf.rent_square_total),0) total_square
from bis_project p left join vw_bis_mall_floor_summary vf on p.bis_project_id = vf.bis_project_id
where p.is_business_project = ''1''
and p.oper_status = ''2''
group by p.bis_project_id,p.short_name) pt,
rep_agg_floor_month af
where 1 = 1
and pt.bis_project_id = af.bis_project_id
and af.store_type = ''1''
and af.agg_year = '||v_year||'
and af.agg_month = to_number('||v_month||')
group by pt.bis_project_id,pt.short_name,pt.total_square) r,
bis_project_target bt
where 1=1
and r.bis_project_id = bt.bis_project_id(+)
) m,
(
select r.bis_project_id,0)+r.off_square budget_year
from
(select pt.bis_project_id,
af.agg_month,
af.agg_year,0) open_square,
nvl(sum(af.off_square_total),0) off_square
from
(select p.bis_project_id,rep_agg_floor_month af
where 1 = 1
and pt.bis_project_id = af.bis_project_id
and af.store_type = ''1''
and af.agg_year = '||v_year||'
and af.agg_month = ''1''
group by pt.bis_project_id,pt.total_square,af.agg_month,af.agg_year) r,bis_project_target bt
where r.bis_project_id = bt.bis_project_id(+)) y
where m.bis_project_id = y.bis_project_id
';
executeimmediate v_sql;
-- v_sql:='select 1 as a from dual;';
commit;
EXCEPTION
-- 异常处理部份
WHENOTHERSTHEN
dbms_output.put_line('insertBisSecondZsPro 错误：' || SQLERRM);
null;
end BIS_PROJECT_BUDGET;
