-------------------------------------------------
select count(*) --计算主表中连续六个月不合格的数据的条数
into v_count6
from dim_service_center
inner join fact_repair_sheet on dim_service_center.sc_id =
fact_repair_sheet.sc_id
inner join fact_vefpart on fact_vefpart.repid =
fact_repair_sheet.repair_sheet_id
where ((fact_vefpart.vef_result1 = '2' --初核结果为不合格
and fact_vefpart.MAJOR_FLAG = '2') --且不需要进行复核
or (fact_vefpart.vef_result2 = '2')) --或者复核结果为不合格
and fact_vefpart.ismajor = 1 --且是主要备件
and fact_repair_sheet.close_date > Add_months(sysdate, -6)--六个月内
and dim_service_center.sc_id = v_sc_id;
v_allcount6 := v_count6; --把不合格的备件数据的数量赋值给总数量计数器
select count(*) --计算电话回访中连续六个月不合格的数据条数
into v_count6
from fact_verify
inner join fact_repair_sheet on fact_verify.repid =
fact_repair_sheet.repair_sheet_id
inner join dim_service_center on dim_service_center.sc_id =
fact_repair_sheet.sc_id
where fact_verify.
CALLSTATUS = '不合格' --回访不合格
and fact_repair_sheet.close_date > Add_months(sysdate, -6)
and dim_service_center.sc_id = v_sc_id;
v_allcount6 := v_allcount6 + v_count6; --计算连续六个月总的不合格数量
select Add_months(sysdate, -6) - creation_date
into v_datediff
from dim_service_center
where sc_id = v_sc_id;
if v_allcount3 > 2 then
v_rate := 100;
elsif v_allcount3 > 1 then
v_rate := 50;
elsif v_allcount3 = 0 then
v_rate := 20;
end if;
if (v_allcount6 = 0) then
v_rate := 5;
end if;
if (v_datediff < 0) then
v_rate := 50;
end if;
insert into VEF_FUHERATE
(vef_fuherate_id,
scid,
auto_rate,
change_rate,
status,
modify_status,
createdate,
updatedate)
values
(sys_guid(), v_sc_id, v_rate, v_rate, '1', '0', sysdate, sysdate);
end loop;
close cur_sc_id;
end;