create or replace procedure MKT_TO_HUAXIAO_ROWNUM(i_size in number,
i_campaign_id in varchar2) is
v_count number(16); --变量 每个地市总数
v_str_sql varchar2(4000);
v_all_count number(16) default 0; --所有地市当前月派单总数
v_difference_value number(16) default 0; --差值
current_count number(16) default 0; --当前月派单总数
begin
for r in (select group_id
from mkt_sregioncode
where group_id != '17'
and group_id != '10042') loop
v_str_sql := 'select count(1)
from dbsalesadm.mk_task_info_' || r.group_id ||
' where act_id =' || i_campaign_id ||
' and create_Date between
trunc(add_months(last_day(sysdate), -1) + 1, ''dd'') and trunc(last_day(sysdate), ''dd'')';
EXECUTE IMMEDIATE v_str_sql
into v_count;
v_all_count := v_all_count + v_count;
end loop;
v_difference_value := i_size - v_all_count;
if v_difference_value = 0 then
update contact_order
set status_cd = '99',
remark = '该要每月数量小于6w' || v_difference_value
where trunc(create_Date, 'dd') = trunc(sysdate, 'dd')
and mkt_campaign_id = i_campaign_id;
elsif v_difference_value > 0 then
update contact_order
set status_cd = '99',
remark = '该要每月数量小于6w' || v_difference_value
where trunc(create_Date, 'dd') = trunc(sysdate, 'dd')
and mkt_campaign_id = i_campaign_id;
else
select count(1)
into current_count
from contact_order
where trunc(create_Date, 'dd') = trunc(sysdate, 'dd')
and mkt_campaign_id = i_campaign_id;
if current_count > v_difference_value then
update contact_order
set status_cd = '99',
remark = '该要每月数量小于6w' || v_difference_value
where trunc(create_Date, 'dd') = trunc(sysdate, 'dd')
and mkt_campaign_id = i_campaign_id
and rownum <= current_count - v_difference_value;
end if;
end if;
end MKT_TO_HUAXIAO_ROWNUM;