-- 1.过程P_CPIC_policy_no_20092012
create or replace procedure P_CPIC_policy_no_20092012 authid current_user as
v_sql1 varchar2(4000);
v_num3 int;
v_begindate varchar2(50);
v_enddate varchar2(50);
begin
-- 初始化变量
v_begindate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
select count(table_name) into v_num3 from user_tables where table_name = upper('TS_CPIC_policy_no_20092012');
-- 判断相同的表是否存在
if v_num3 = 1 then
execute immediate 'drop table TS_CPIC_policy_no_20092012';
end if;
-- 执行过程
v_sql1:='create table i11test.TS_CPIC_policy_no_20092012 as
select policy_no，
product_code,
inception_date,
planned_end_date,
branch_company_code,
selling_channel_type,
(CASE WHEN (inception_date 0 then 'S'
when t1.SORL_NEW = 'O' and premium_amount_S =0 then 'L'
else SORL_NEW
end as SORL,
claim_folder_no, paid_amt
FROM (select policy_no, branch_company_code,
case when SORL = 'O' then
case when exists (select 1 from TS_CPIC_paid_claim_SL_base where sorl = 'S' and paid_amt_by_sorl > 0) then 'S'
when exists (select 1 from TS_CPIC_paid_claim_SL_base where sorl = 'S' and paid_amt_by_sorl = 0)
and exists (select '1' from TS_CPIC_paid_claim_SL_base where sorl = 'L' and paid_amt_by_sorl > 0) then 'L'
else SORL end
else SORL END as SORL_NEW,
claim_folder_no, paid_amt
from TS_CPIC_paid_claim_SL_base) t1,
CPIC_premium_stat t2
where t1.policy_no = t2.policy_no and t1.branch_company_code = t2.branch_company_code
commit;
-- 日志记录
v_enddate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
insert into t_value_config_2_log select v_begindate, v_enddate, 'P_CPIC_paid_claim_SL', 'Y' from dual;
commit;
-- 异常处理
exception
when others then
v_enddate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
insert into t_value_config_2_log select v_begindate, v_enddate, 'P_CPIC_paid_claim_SL', 'N' from dual;
commit;
end;
/
-- 6.过程P_CPIC_paid_claim_stat
create or replace procedure P_CPIC_paid_claim_stat authid current_user as
v_sql1 varchar2(4000);
v_num3 int;
v_begindate varchar2(50);
v_enddate varchar2(50);
begin
-- 初始化变量
v_begindate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
select count(table_name) into v_num3 from user_tables where table_name = upper('TS_CPIC_paid_claim_stat');
-- 判断相同的表是否存在
if v_num3 = 1 then
execute immediate 'drop table TS_CPIC_paid_claim_stat';
end if;
-- 执行过程
v_sql1:='CREATE TABLE i11test.TS_CPIC_paid_claim_stat NOLOGGING PARALLEL AS
SELECT policy_no, branch_company_code,
sum(CASE WHEN sorl = ''L'' THEN paid_amt ELSE 0 END) AS paid_amt_L,
sum(CASE WHEN sorl = ''S'' THEN paid_amt ELSE 0 END) AS paid_amt_S
FROM (select distinct policy_no, branch_company_code, sorl, claim_folder_no, paid_amt from i11test.TS_CPIC_paid_claim_sl)
group by policy_no, branch_company_code';
execute immediate v_sql1;
-- 日志记录
v_enddate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
insert into t_value_config_2_log select v_begindate, v_enddate, 'P_CPIC_paid_claim_stat', 'Y' from dual;
commit;
-- 异常处理
exception
when others then
v_enddate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
insert into t_value_config_2_log select v_begindate, v_enddate, 'P_CPIC_paid_claim_stat', 'N' from dual;
commit;
end;
/
-- 7.过程P_CPIC_pending_claim_SL_BASE
create or replace procedure P_CPIC_pending_claim_SL_BASE authid current_user as
v_sql1 varchar2(4000);
v_num3 int;
v_begindate varchar2(50);
v_enddate varchar2(50);
begin
-- 初始化变量
v_begindate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
select count(table_name) into v_num3 from user_tables where table_name = upper('TS_CPIC_pending_claim_SL_BASE');
-- 判断相同的表是否存在
if v_num3 = 1 then
execute immediate 'drop table TS_CPIC_pending_claim_SL_BASE';
end if;
-- 执行过程
v_sql1:='create table TS_CPIC_pending_claim_SL_BASE as
select distinct t1.policy_no,
t1.branch_company_code,
t3.sorl,
t1.claim_folder_no,
t1.status,
sum(nvl(t2.estimate_amount, 0)) over (partition by t1.policy_no, t1.branch_company_code, t1.claim_folder_no, t3.sorl) as estimate_amount,
sum(nvl(t2.estimate_amount, 0)) over (partition by t1.policy_no, t1.branch_company_code, t3.sorl) as estimate_amount_by_sorl
from IDS.auto_claim_folder_t t1,
IDS.auto_estimated_loss_t t2,
i11test.CPIC_product_code_SL t3
where t1.status in (0, 4)
and t2.is_lastest = 1
and t1.claim_folder_no = t2.claim_folder_no
and t2.product_code = t3.product_code
--and t1.policy_no = ''ANAJAZ0DX910X001205A''
and t1.inception_date >= TO_DATE(''2008-10-01'', ''YYYY-MM-DD'') --checking previous three months
and t1.planned_end_date 0 then ''S''
when t1.SORL_NEW = ''O'' and premium_amount_S =0 then ''L''
else SORL_NEW end as SORL,
claim_folder_no, status, estimate_amount
FROM (select policy_no, branch_company_code,
case when SORL = ''O'' then
case when exists (select 1 from TS_CPIC_pending_claim_SL_BASE where sorl = ''S'' and estimate_amount_by_sorl > 0) then ''S''
when exists (select 1 from TS_CPIC_pending_claim_SL_BASE where sorl = ''S'' and estimate_amount_by_sorl = 0)
and exists (select ''1'' from TS_CPIC_pending_claim_SL_BASE where sorl = ''L'' and estimate_amount_by_sorl > 0) then ''L''
else SORL end
else SORL END as SORL_NEW,
claim_folder_no, status, estimate_amount
from TS_CPIC_pending_claim_SL_BASE) t1, CPIC_premium_stat t2
where t1.policy_no = t2.policy_no and t1.branch_company_code = t2.branch_company_code';
execute immediate v_sql1;
-- 日志记录
v_enddate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
insert into t_value_config_2_log select v_begindate, v_enddate, 'P_CPIC_pending_claim_SL', 'Y' from dual;
commit;
-- 异常处理
exception
when others then
v_enddate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
insert into t_value_config_2_log select v_begindate, v_enddate, 'P_CPIC_pending_claim_SL', 'N' from dual;
commit;
end;
/
-- 9.过程P_CPIC_pending_claim_Stat_BASE
create or replace procedure P_CPIC_pending_claim_Stat_BASE authid current_user as
v_sql1 varchar2(4000);
v_num3 int;
v_begindate varchar2(50);
v_enddate varchar2(50);
begin
-- 初始化变量
v_begindate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
select count(table_name) into v_num3 from user_tables where table_name = upper('TS_CPIC_pending_claim_Stat_BASE');
-- 判断相同的表是否存在
if v_num3 = 1 then
execute immediate 'drop table TS_CPIC_pending_claim_Stat_BASE';
end if;
-- 执行过程
v_sql1:='create table TS_CPIC_pending_claim_Stat_BASE as
select distinct policy_no, branch_company_code, sorl, claim_folder_no, estimate_amount, paid_amt,
case when status = 4 then
case when (estimate_amount - paid_amt) < 0 then 0
else (estimate_amount - paid_amt) end
else estimate_amount
end as pending_payment_amt
from (select distinct t1.policy_no, t1.branch_company_code, t1.sorl, t1.claim_folder_no, t1.status,
t1.estimate_amount, nvl(t2.paid_amt, 0) as paid_amt
from TS_CPIC_pending_claim_SL t1, TS_CPIC_paid_claim_SL t2
where t1.claim_folder_no = t2.claim_folder_no(+) and t1.branch_company_code = t2.branch_company_code(+)
and t1.sorl = t2.sorl(+)
-- and t1.claim_folder_no = ''F110106ZH809021239'' and t1.policy_no = ''AHAZ750ZH911B008402N'')';
execute immediate v_sql1;
-- 日志记录
v_enddate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
insert into t_value_config_2_log select v_begindate, v_enddate, 'P_CPIC_pending_claim_Stat_BASE', 'Y' from dual;
commit;
-- 异常处理
exception
when others then
v_enddate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
insert into t_value_config_2_log select v_begindate, v_enddate, 'P_CPIC_pending_claim_Stat_BASE', 'N' from dual;
commit;
end;
/
-- 10.过程P_CPIC_pending_claim_Stat
create or replace procedure P_CPIC_pending_claim_Stat authid current_user as
v_sql1 varchar2(4000);
v_num3 int;
v_begindate varchar2(50);
v_enddate varchar2(50);
begin
-- 初始化变量
v_begindate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
select count(table_name) into v_num3 from user_tables where table_name = upper('TS_CPIC_pending_claim_Stat');
-- 判断相同的表是否存在
if v_num3 = 1 then
execute immediate 'drop table TS_CPIC_pending_claim_Stat';
end if;
-- 执行过程
v_sql1:='create table TS_CPIC_pending_claim_Stat as
SELECT policy_no, branch_company_code,
sum(CASE WHEN sorl = ''L'' THEN pending_payment_amt ELSE 0 END) AS pending_pay_amt_L,
sum(CASE WHEN sorl = ''S'' THEN pending_payment_amt ELSE 0 END) AS pending_pay_amt_S
FROM TS_CPIC_pending_claim_Stat_BASE
group by policy_no, branch_company_code';
execute immediate v_sql1;
-- 日志记录
v_enddate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
insert into t_value_config_2_log select v_begindate, v_enddate, 'P_CPIC_pending_claim_Stat', 'Y' from dual;
commit;
-- 异常处理
exception
when others then
v_enddate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
insert into t_value_config_2_log select v_begindate, v_enddate, 'P_CPIC_pending_claim_Stat', 'N' from dual;
commit;
end;
/
-- 11.过程P_CPIC_CLAIM_TIMES_STAT
create or replace procedure P_CPIC_CLAIM_TIMES_STAT authid current_user as
v_sql1 varchar2(4000);
v_num3 int;
v_begindate varchar2(50);
v_enddate varchar2(50);
begin
-- 初始化变量
v_begindate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
select count(table_name) into v_num3 from user_tables where table_name = upper('TS_CPIC_CLAIM_TIMES_STAT');
-- 判断相同的表是否存在
if v_num3 = 1 then
execute immediate 'drop table TS_CPIC_CLAIM_TIMES_STAT';
end if;
-- 执行过程
v_sql1:='Create table TS_CPIC_CLAIM_TIMES_STAT as
select policy_no,
branch_company_code,
sum(CASE WHEN tot_payment_amt_L > 100 THEN 1 ELSE 0 END) as payment_times_L,
sum(CASE WHEN tot_payment_amt_S > 100 THEN 1 ELSE 0 END) as payment_times_S
FROM
(select distinct policy_no,
branch_company_code,
claim_folder_no,
sum(payment_amt_L) over (partition by policy_no, branch_company_code, claim_folder_no) as tot_payment_amt_L,
sum(payment_amt_S) over (partition by policy_no, branch_company_code, claim_folder_no) as tot_payment_amt_S
FROM
(select t1.policy_no,
t1.branch_company_code,
t1.claim_folder_no,
SUM(CASE WHEN t1.sorl = ''L'' THEN nvl(t1.paid_amt,0) + nvl(t2.pending_payment_amt,0) ELSE 0 END) AS payment_amt_L,
SUM(CASE WHEN t1.sorl = ''S'' THEN nvl(t1.paid_amt,0) + nvl(t2.pending_payment_amt,0) ELSE 0 END) AS payment_amt_S
from i11test.TS_CPIC_paid_claim_SL t1, i11test.TS_CPIC_pending_claim_Stat_BASE t2
where t1.policy_no = t2.policy_no(+)
and t1.branch_company_code = t2.branch_company_code(+)
and t1.claim_folder_no = t2.claim_folder_no(+)
and t1.sorl = t2.sorl(+)
--and t1.policy_no = ''ABEJ020ZH910B026796E''
group by t1.policy_no,
t1.branch_company_code,
t1.claim_folder_no
UNION
select t2.policy_no,
t2.branch_company_code,
t2.claim_folder_no,
SUM(CASE WHEN t2.sorl = ''L'' THEN nvl(t1.paid_amt,0) + nvl(t2.pending_payment_amt,0) ELSE 0 END) AS payment_amt_L,
SUM(CASE WHEN t2.sorl = ''S'' THEN nvl(t1.paid_amt,0) + nvl(t2.pending_payment_amt,0) ELSE 0 END) AS payment_amt_S
from i11test.TS_CPIC_paid_claim_SL t1, i11test.TS_CPIC_pending_claim_Stat_BASE t2
where t2.policy_no = t1.policy_no(+)
and t2.branch_company_code = t1.branch_company_code(+)
and t2.claim_folder_no = t1.claim_folder_no(+)
and t2.sorl = t1.sorl(+)
--and t2.policy_no = ''ABEJ020ZH910B026796E''
group by t2.policy_no,
t2.branch_company_code,
t2.claim_folder_no)
)
group by policy_no,
branch_company_code';
execute immediate v_sql1;
-- 日志记录
v_enddate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
insert into t_value_config_2_log select v_begindate, v_enddate, 'P_CPIC_CLAIM_TIMES_STAT', 'Y' from dual;
commit;
-- 异常处理
exception
when others then
v_enddate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
insert into t_value_config_2_log select v_begindate, v_enddate, 'P_CPIC_CLAIM_TIMES_STAT', 'N' from dual;
commit;
end;
/
-- 12.过程P_CPIC_auto_cf_indemnity_basic
create or replace procedure P_CPIC_auto_cf_indemnity_basic authid current_user as
v_sql1 varchar2(4000);
v_num3 int;
v_begindate varchar2(50);
v_enddate varchar2(50);
begin
-- 初始化变量
v_begindate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
select count(table_name) into v_num3 from user_tables where table_name = upper('TS_CPIC_auto_cf_indemnity_bs');
-- 判断相同的表是否存在
if v_num3 = 1 then
execute immediate 'drop table TS_CPIC_auto_cf_indemnity_bs';
end if;
-- 执行过程
v_sql1:='CREATE TABLE i11test.TS_CPIC_auto_cf_indemnity_bs
( policy_no VARCHAR2(60) NOT NULL,
branch_company_code VARCHAR2(30),
claim_folder_no VARCHAR2(60) NOT NULL,
indemnity_no VARCHAR2(60) NOT NULL,
insured_object_indemnity NUMBER(16,2) NOT NULL,
investigation_agent_fee NUMBER(16,2) NOT NULL,
inside_investigation_agent_fee NUMBER(16,2) NOT NULL,
salvage_fee NUMBER(16,2) NOT NULL,
investigation_fee NUMBER(16,2) NOT NULL,
claim_audit_fee NUMBER(16,2) NOT NULL,
other_fee NUMBER(16,2) NOT NULL,
salvage_recovery NUMBER(16,2) NOT NULL)';
execute immediate v_sql1;
INSERT INTO i11test.TS_CPIC_auto_cf_indemnity_bs
SELECT indem.policy_no,
indem.branch_company_code,
indem.claim_folder_no,
indem.indemnity_no,
indem.insured_object_indemnity,
indem.investigation_agent_fee,
indem.inside_investigation_agent_fee,
indem.salvage_fee,
indem.investigation_fee,
indem.claim_audit_fee,
indem.other_fee,
indem.salvage_recovery
FROM ids.auto_cf_indemnity_t indem
where (policy_no, branch_company_code) in (select policy_no, branch_company_code from i11test.TS_CPIC_premium_SL);
commit;
-- 日志记录
v_enddate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
insert into t_value_config_2_log select v_begindate, v_enddate, 'P_CPIC_auto_cf_indemnity_basic', 'Y' from dual;
commit;
-- 异常处理
exception
when others then
v_enddate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
insert into t_value_config_2_log select v_begindate, v_enddate, 'P_CPIC_auto_cf_indemnity_basic', 'N' from dual;
commit;
end;
/
-- 13.过程P_CPIC_auto_cf_indemnity_stat
create or replace procedure P_CPIC_auto_cf_indemnity_stat authid current_user as
v_sql1 varchar2(4000);
v_num3 int;
v_begindate varchar2(50);
v_enddate varchar2(50);
begin
-- 初始化变量
v_begindate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
select count(table_name) into v_num3 from user_tables where table_name = upper('TS_CPIC_auto_cf_indemnity_stat');
-- 判断相同的表是否存在
if v_num3 = 1 then
execute immediate 'drop table TS_CPIC_auto_cf_indemnity_stat';
end if;
-- 执行过程
v_sql1:='CREATE TABLE i11test.TS_CPIC_auto_cf_indemnity_stat
( policy_no VARCHAR2(60 BYTE) NOT NULL,
branch_company_code VARCHAR2(30 byte),
insured_object_indemnity NUMBER(16,2) NOT NULL,
investigation_agent_fee NUMBER(16,2) NOT NULL,
inside_investigation_agent_fee NUMBER(16,2) NOT NULL,
salvage_fee NUMBER(16,2) NOT NULL,
investigation_fee NUMBER(16,2) NOT NULL,
claim_audit_fee NUMBER(16,2) NOT NULL,
other_fee NUMBER(16,2) NOT NULL,
salvage_recovery NUMBER(16,2) NOT NULL)';
execute immediate v_sql1;
INSERT INTO i11test.CPIC_auto_cf_indemnity_stat
SELECT policy_no,
branch_company_code,
sum(nvl(insured_object_indemnity,0)),
sum(nvl(investigation_agent_fee,0)),
sum(nvl(inside_investigation_agent_fee,0)),
sum(nvl(salvage_fee,0)),
sum(nvl(investigation_fee,0)),
sum(nvl(claim_audit_fee,0)),
sum(nvl(other_fee,0)),
sum(nvl(salvage_recovery,0))
FROM i11test.TS_CPIC_auto_cf_indemnity_bs
GROUP BY policy_no, branch_company_code;
commit;
-- 日志记录
v_enddate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
insert into t_value_config_2_log select v_begindate, v_enddate, 'P_CPIC_auto_cf_indemnity_stat', 'Y' from dual;
commit;
-- 异常处理
exception
when others then
v_enddate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
insert into t_value_config_2_log select v_begindate, v_enddate, 'P_CPIC_auto_cf_indemnity_stat', 'N' from dual;
commit;
end;
/
-- 14.过程P_CPIC_motorised_vehicle_DIM
create or replace procedure P_CPIC_motorised_vehicle_DIM authid current_user as
v_sql1 varchar2(4000);
v_num3 int;
v_begindate varchar2(50);
v_enddate varchar2(50);
begin
-- 初始化变量
v_begindate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
select count(table_name) into v_num3 from user_tables where table_name = upper('TS_CPIC_motorised_vehicle_DIM');
-- 判断相同的表是否存在
if v_num3 = 1 then
execute immediate 'drop table TS_CPIC_motorised_vehicle_DIM';
end if;
-- 执行过程
v_sql1:='Create table i11test.TS_CPIC_motorised_vehicle_DIM as
SELECT a.policy_no，
a.branch_company_code,
nvl(value_new, 0) as value_new,
first_registration_date,
(TO_DATE(''2012-12-04'', ''YYYY-MM-DD'') - first_registration_date) / 365 as car_age,
(inception_date-first_registration_date) / 365 as car_age_historical,
usage,
commercial_usage,
vehicle_type,
vehicle_brand,
vehicle_type_name,
vin,
case when value_new =''50000'' and value_new =''100000'' and value_new =''150000'' and value_new =''200000'' and value_new =''300000'' and value_new =''500000'' and value_new =''1000000'' then ''8''
end as car_group_cpic
FROM ids.motorised_vehicle_t a, i11test.TS_CPIC_policy_no_20092012 b
WHERE a.policy_no = b.policy_no and a.branch_company_code = b.branch_company_code';
execute immediate v_sql1;
-- 日志记录
v_enddate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
insert into t_value_config_2_log select v_begindate, v_enddate, 'P_CPIC_motorised_vehicle_DIM', 'Y' from dual;
commit;
-- 异常处理
exception
when others then
v_enddate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
insert into t_value_config_2_log select v_begindate, v_enddate, 'P_CPIC_motorised_vehicle_DIM', 'N' from dual;
commit;
end;
/
-- 15.过程P_CPIC_party_role2_dim
create or replace procedure P_CPIC_party_role2_dim authid current_user as
v_sql1 varchar2(4000);
v_num3 int;
v_begindate varchar2(50);
v_enddate varchar2(50);
begin
-- 初始化变量
v_begindate:=to_char(sysdate, 'yyyymmdd hh24:mi:ss');
select count(table_name) into v_num3 from user_tables where table_name = upper('TS_CPIC_party_role2_dim');
-- 判断相同的表是否存在
if v_num3 = 1 then
execute immediate 'drop table TS_CPIC_party_role2_dim';
end if;
&n
