折磨人的存储过程

PROCEDURE drs_return_update_check_up(P_BARCODE IN VARCHAR,
P_POLNO IN VARCHAR,
P_CARD_NO IN VARCHAR,
P_BIRTH_DATE IN VARCHAR,
P_CUSTOMER_NAME IN VARCHAR,
P_ID_TYPE IN VARCHAR,
P_ID_NO IN VARCHAR,
P_GENDER IN VARCHAR,
P_UPLOADNO IN VARCHAR,
p_error_flag OUT VARCHAR,
p_error_msg OUT VARCHAR) IS

v_status varchar2(2);
-- v_customer_no_pol customer.CUSTOMER_NO%type;
-- v_customer_no_card customer.CUSTOMER_NO%type;
v_customer_no varchar2(50);
--v_barcode checkup_report_main.bar_code_no%type;
v_id_type id_type_tbl.id_type%type;
v_gender sex_tbl.sex%type;
v_is_found varchar2(1);
v_bar_code checkup_report_main.bar_code_no%type;
v_cur_num number;
v_card_no varchar2(500);
v_length number;
v_cur_card_no varchar2(500);
v_description varchar2(500);
v_char varchar2(1);
v_region_code region_code_tbl.REGION_CODE%type;
v_birth_date varchar2(20);
v_relate_exist varchar2(20);
v_customer_count number;
v_card_bar_no varchar2(20);
--test_date date;

--通过五项信息去customer,person表中匹配客户
cursor c_customer_match is
select a.customer_no
from person a,
customer c
where a.GENDER_CODE = v_gender
-- and a.CERTIFICATE_TYPE_CODE = v_id_type
and a.CERTIFICATE_NO = P_ID_NO
and a.CUSTOMER_NO = c.CUSTOMER_NO
and c.CUSTOMER_NAME = P_CUSTOMER_NAME;
--and a.BIRTH_DATE = to_date(v_birth_date,'YYYY-MM-DD');

cursor c_customer_count is
select count(*)
from person a,
customer c
where a.GENDER_CODE = v_gender
-- and a.CERTIFICATE_TYPE_CODE = v_id_type
and a.CERTIFICATE_NO = P_ID_NO
and a.CUSTOMER_NO = c.CUSTOMER_NO
and c.CUSTOMER_NAME = P_CUSTOMER_NAME;

--通过保单号去pol_list匹配
cursor c_polno_match is
select 'X'
from pol_list
where insno = v_customer_no
and polno = P_POLNO;

--通过五项信息去health_check_card_customer匹配客户
cursor c_card_match_1 is
select 'X'
from health_check_card_info a,
health_check_card_customer b
where
--b.GENDER_CODE = v_gender
--and b.CERTIFICATE_TYPE_CODE = v_id_type
--and b.CERTIFICATE_NO = P_ID_NO
b.CUSTOMER_ID = a.CUSTOMER_ID
and a.health_check_card_no = v_card_no
and b.CUSTOMER_ID = a.CUSTOMER_ID
and b.customer_no=v_customer_no;
--and b.CUSTOMER_NAME = P_CUSTOMER_NAME
--and b.BIRTH_DATE = to_date(v_birth_date,'YYYY-MM-DD');

--通过卡号去health_check_corp_card_info匹配客户
cursor c_card_match_2 is
select 'X'
from health_check_corp_card_info
where card_no = v_card_no
and policy_no = nvl(p_polno,policy_no);

--通过卡号去pol_list匹配客户
cursor c_card_match_3 is
select 'X'
from pol_list
where INSNO = v_customer_no
and polno = nvl(P_POLNO,polno)
and MEMBER_CODE = v_card_no;

cursor c_get_card_no is
select checkup_card_no
from checkup_report_card
where bar_code_no = P_BARCODE;

cursor test_card_bar_no is
select 'X' from checkup_report_card
where bar_code_no = P_BARCODE
or checkup_card_no = v_cur_card_no;

cursor test_card_bar_no2 is
select 'X' from checkup_report_card
where bar_code_no = P_BARCODE
or checkup_card_no = v_card_no;
cursor test_card_bar_no3 is
select 'X' from checkup_report_card
where bar_code_no = P_BARCODE;

cursor testBarcode is
select 'X' from checkup_report_des_input
where bar_code_no=P_BARCODE;

--五项信息不全且卡号不为空的情况下，去health_check_card_customer匹配
/*cursor c_is_card_match is
select d.CUSTOMER_NO
from health_check_card_info a,
health_check_card_customer b,
person c,
customer d
where b.GENDER_CODE = c.GENDER_CODE
and b.CERTIFICATE_TYPE_CODE = c.CERTIFICATE_TYPE_CODE
and b.CERTIFICATE_NO = c.CERTIFICATE_NO
and b.CUSTOMER_ID = a.CUSTOMER_ID
and a.health_check_card_no = v_card_no
and b.customer_no = d.CUSTOMER_NO
and b.CUSTOMER_NAME = d.CUSTOMER_NAME
and b.customer_no = c.CUSTOMER_NO
and b.BIRTH_DATE = c.BIRTH_DATE;*/

cursor c_get_region_1 is
select a.branch_code
from health_check_card_batch_info a,
health_check_card_info b
where b.health_check_card_no = v_card_no
and a.CARD_BATCH_NO= b.card_batch_no;

cursor c_get_region_2 is
select b.region_code
from health_check_corp_card_info a,
pol_main b
where a.card_no = v_card_no
and a.policy_no= b.polno;

cursor c_get_region_3 is
select a.region_code
from pol_list a,
pol_main b
where a.polno= b.polno
and a.member_code= v_card_no;

cursor checkup_report_relate_exist is
select 'X' from
checkup_report_relate c
where c.upload_no=P_UPLOADNO
and c.bar_code_no = P_BARCODE;
BEGIN
--初始化错误代码
p_error_flag := '00';

v_id_type := P_ID_TYPE;

if P_GENDER = '1' then
v_gender := 'M';
elsif P_GENDER = '2' then
v_gender := 'F';
elsif P_GENDER = '男' then
v_gender := 'M';
elsif P_GENDER = '女' then
v_gender := 'F';
else
v_gender :='';
end if;

--这里把DES传过来的日期类型做一个转换，如果抛错则捕获
if P_BIRTH_DATE is not null then
begin
--test_date := to_date(P_BIRTH_DATE,'yyyymmdd');
v_birth_date := substr(P_BIRTH_DATE,0,8);
EXCEPTION
WHEN OTHERS THEN
v_birth_date :='';
end;
end if;
/*
select bar_code_no
into v_bar_code
from checkup_report_des_input
where task_id = P_TASK_ID;*/

--卡号可能包含'/'符号，需要截取为多个卡号
open test_card_bar_no3;
fetch test_card_bar_no3 into v_card_bar_no;
close test_card_bar_no3;
if p_card_no is not null then
v_card_no := p_card_no;
loop
exit when instr(v_card_no,'/')=0 or instr(v_card_no,'/') is null;
v_cur_num := instr(v_card_no,'/');
v_length := length(v_card_no);
v_cur_card_no := substr(v_card_no,0,v_cur_num-1);
v_card_no := substr(v_card_no,v_cur_num+1,v_length);
if v_cur_card_no is not null then
open test_card_bar_no;
fetch test_card_bar_no into v_card_bar_no;
if test_card_bar_no%notfound then
insert into checkup_report_card(seq_no,
bar_code_no,
checkup_card_no)
values(seq_checkup_report_card_seq_no.nextval,
P_BARCODE,
substr(v_cur_card_no,0,20));

else
insert into CHECKUP_REPORT_RELATE(
ID,
UPLOAD_NO,
BAR_CODE_NO,
RELATE_RESULT,
FAIL_DESC,
CREATED_BY,
CREATED_DATE,
UPDATED_BY,
UPDATED_DATE
)values(
(seq_checkup_report_relate_id.nextval),
P_UPLOADNO,
P_BARCODE,
'失败',
'卡号和条形码已存在不作处理',
'hcs',
sysdate,
'hcs',
sysdate
);
end if;
close test_card_bar_no;
end if;

end loop;

if v_card_no is not null then
open test_card_bar_no2;
fetch test_card_bar_no2 into v_card_bar_no;
if test_card_bar_no2%notfound then
insert into checkup_report_card(seq_no,
bar_code_no,
checkup_card_no)
values(seq_checkup_report_card_seq_no.nextval,
P_BARCODE,
substr(v_card_no,0,20));
else
insert into CHECKUP_REPORT_RELATE(
ID,
UPLOAD_NO,
BAR_CODE_NO,
RELATE_RESULT,
FAIL_DESC,
CREATED_BY,
CREATED_DATE,
UPDATED_BY,
UPDATED_DATE
)values(
(seq_checkup_report_relate_id.nextval),
P_UPLOADNO,
P_BARCODE,
'失败',
'卡号和条形码已存在不作处理',
'hcs',
sysdate,
'hcs',
sysdate
);
end if;
close test_card_bar_no2;
end if;

end if;

--如果包含波浪号，则转待处理
/*
if instr(P_POLNO,'~')>0 or instr(P_CARD_NO,'~')>0 or instr(P_BIRTH_DATE,'~')>0
or instr(P_CUSTOMER_NAME,'~')>0 or instr(P_ID_TYPE,'~')>0 or instr(P_ID_NO,'~')>0
or instr(P_GENDER,'~')>0 then
v_status := '04';
v_description := '包含波浪号，转待处理';*/
--如果五项信息不全，则转未关联客户(如果是健康险通卡还需进一步处理。这个已实现见注释)
/* elsif P_BIRTH_DATE is null or P_CUSTOMER_NAME is null or P_ID_NO is null then*/

/* 如果五项信息不全，可以通过卡号反匹配出客户号，这块暂时注释掉，需要的时候再加上
if P_CARD_NO is not null then
open c_get_card_no;
loop
fetch c_get_card_no into v_card_no;
exit when c_get_card_no%notfound or v_is_found = 'N';
v_char := 'N';
open c_is_card_match;
fetch c_is_card_match into v_customer_no;
if c_is_card_match%found then
if p_polno is not null then
open c_polno_match;
fetch c_polno_match into v_char;
if c_polno_match%found then
v_char := 'Y';
open c_get_region_1;
fetch c_get_region_1 into v_region_code;
close c_get_region_1;
end if;
close c_polno_match;
else
v_char := 'Y';
open c_get_region_1;
fetch c_get_region_1 into v_region_code;
close c_get_region_1;
end if;
end if;
close c_is_card_match;

if v_char = 'Y' then
v_is_found := 'Y';
v_status := '02';
v_description := '根据体检卡号'||v_card_no||'成功匹配客户';
else
v_is_found := 'N';
v_status := '03';
v_description := '根据体检卡号'||v_card_no||'未匹配客户';
end if;

end loop;
close c_get_card_no;
else
*/
/*
v_status := '03';
v_description := '客户信息不全，转未关联客户';*/
/*
end if;
*/

--五项信息全且没有波浪号，则进行判断
-- else

if v_card_bar_no is null then
v_status := '03';
v_description := '客户信息未匹配客户';
open c_customer_match;
loop
fetch c_customer_match into v_customer_no;

exit when c_customer_match%notfound or v_status='02';

--如果卡号不为空，则检验卡号信息（保单号可能为空可能不空）
if P_CARD_NO is not null then
--循环取所有的卡号，只要发现其中有一个卡号无法匹配，则认为未关联
open c_get_card_no;
loop
fetch c_get_card_no into v_card_no;
exit when c_get_card_no%notfound or v_is_found = 'N';

v_char := 'N';

--校验规则1（加上如果保单号不为空的情况），体检卡
open c_card_match_1;
fetch c_card_match_1 into v_char;
if c_card_match_1%found then
if p_polno is not null then
open c_polno_match;
fetch c_polno_match into v_char;
if c_polno_match%found then
v_char := 'Y';
open c_get_region_1;
fetch c_get_region_1 into v_region_code;
close c_get_region_1;
end if;
close c_polno_match;
else
v_char := 'Y';
open c_get_region_1;
fetch c_get_region_1 into v_region_code;
close c_get_region_1;
end if;
end if;
close c_card_match_1;

--校验规则2，企业健康通卡
open c_card_match_2;
fetch c_card_match_2 into v_char;
if c_card_match_2%found then
v_char := 'Y';
open c_get_region_2;
fetch c_get_region_2 into v_region_code;
close c_get_region_2;
end if;
close c_card_match_2;

--校验规则3，集团大单
open c_card_match_3;
fetch c_card_match_3 into v_char;
if c_card_match_3%found then
v_char := 'Y';
open c_get_region_3;
fetch c_get_region_3 into v_region_code;
close c_get_region_3;
end if;
close c_card_match_3;

if v_char = 'Y' then
v_is_found := 'Y';
v_status := '02';
v_description := '根据体检卡号'||v_card_no||'成功匹配客户';
else
v_is_found := 'N';
--员工编号、保单号不为空
if P_POLNO is not null and v_cur_card_no is not null then
v_status := '04';
v_description := '根据员工号或体检卡号、保单号'||v_card_no||','||P_POLNO||'未匹配客户';
else
v_status := '03';
v_description := '根据体检卡号'||v_card_no||'未匹配客户';
end if;
end if;

end loop;
close c_get_card_no;
--保单号不为空，卡号为空
elsif P_POLNO is not null then
open c_polno_match;
fetch c_polno_match into v_char;
if c_polno_match%found then
v_status := '02';
v_description := '根据保单号'||P_POLNO||'成功匹配客户';
select region_code
into v_region_code
from pol_main
where polno=P_POLNO;
else
v_status := '03';
v_description := '根据保单号'||P_POLNO||'未匹配客户';
end if;
close c_polno_match;
else
v_status := '03';
v_description := '保单号，卡号均为空';
end if;

--更新数据
/*
update checkup_report_des_input
set policy_no = P_POLNO,
person_name = substr(P_CUSTOMER_NAME,0,60),
gender_code = substr(v_gender,0,1),
birth_date = to_date(v_birth_date,'YYYYMMDD'),
id_type_code = substr(v_id_type,0,2),
id_no = substr(P_ID_NO,0,20),
input_birth_date = substr(p_birth_date,0,8),
Input_id_type = substr(P_ID_TYPE,0,50),
input_gender = substr(P_GENDER,0,10),
input_card_no = P_CARD_NO,
updated_date = sysdate,
readin_date = sysdate
where task_id = P_TASK_ID;*/

--将数据放入des
open testBarcode;
fetch testBarcode into v_bar_code;
if testBarcode%found then
update checkup_report_des_input
set policy_no=P_POLNO,
person_name=P_CUSTOMER_NAME,
GENDER_CODE=v_gender,
BIRTH_DATE= to_date(v_birth_date,'YYYY-MM-DD'),
ID_TYPE_CODE=P_ID_TYPE,
ID_NO=P_ID_NO,
INPUT_GENDER=P_GENDER,
INPUT_BIRTH_DATE=P_BIRTH_DATE,
INPUT_ID_TYPE=P_ID_TYPE,
INPUT_CARD_NO=P_CARD_NO,
READIN_DATE=sysdate
where bar_code_no=P_BARCODE;
else
insert into checkup_report_des_input
(
bar_code_no,
policy_no,
person_name,
GENDER_CODE,
BIRTH_DATE,
ID_TYPE_CODE,
ID_NO,
INPUT_GENDER,
INPUT_BIRTH_DATE,
INPUT_ID_TYPE,
INPUT_CARD_NO,
READIN_DATE,
CREATED_BY,
CREATED_DATE,
UPDATED_BY,
UPDATED_DATE
)values
(
P_BARCODE,
P_POLNO,
P_CUSTOMER_NAME,
v_gender,
to_date(v_birth_date,'YYYY-MM-DD'),
substr(v_id_type,0,2),
P_ID_NO,
P_GENDER,
P_BIRTH_DATE,
P_ID_TYPE,
P_CARD_NO,
sysdate,
'hcs',
sysdate,
'hcs',
sysdate
);
end if;
close testBarcode;


update checkup_report_main
set status = v_status,
customer_no = decode(v_status,'02',v_customer_no,customer_no),
description = v_description,
region_code = decode(v_status,'02',v_region_code,region_code)
where bar_code_no = P_BARCODE;

--只有02已匹配才写main表的卡号和保单号字段
if v_status = '02' then
update checkup_report_main
Set policy_no= nvl(p_polno,(select polno from pol_list where member_code=v_card_no and rownum=1)),
checkup_card_no = v_card_no
where bar_code_no = P_BARCODE;
end if;

--写关联情况表
open checkup_report_relate_exist;
fetch checkup_report_relate_exist into v_relate_exist;
if checkup_report_relate_exist%found then
update CHECKUP_REPORT_RELATE
set RELATE_RESULT = decode(v_status,'02','成功','失败'),
FAIL_DESC= decode(v_status,'02','',v_description)
where BAR_CODE_NO = P_BARCODE;

else
insert into CHECKUP_REPORT_RELATE(
ID,
UPLOAD_NO,
BAR_CODE_NO,
RELATE_RESULT,
FAIL_DESC,
CREATED_BY,
CREATED_DATE,
UPDATED_BY,
UPDATED_DATE
)values(
(seq_checkup_report_relate_id.nextval),
P_UPLOADNO,
P_BARCODE,
decode(v_status,'02','成功','失败'),
decode(v_status,'02','',v_description),
'hcs',
sysdate,
'hcs',
sysdate
);
end if;
close checkup_report_relate_exist;
--写更新记录表
insert into checkup_report_modify_history(seq_no,
bar_code_no,
old_status_code,
new_status_code,
modify_reason,
operator_id,
operate_date)
values(seq_checkup_rpt_mdi_his_seq_no.nextval,
P_BARCODE,
'06',
v_status,
'上载csv文件关联客户调HCS更新数据',
'EHISJOB',
sysdate);
end loop;
close c_customer_match;
open c_customer_count;
fetch c_customer_count into v_customer_count;
if v_customer_count=0 then

--将数据放入des
open testBarcode;
fetch testBarcode into v_bar_code;
if testBarcode%found then
update checkup_report_des_input
set policy_no=P_POLNO,
person_name=P_CUSTOMER_NAME,
GENDER_CODE=v_gender,
BIRTH_DATE= to_date(v_birth_date,'YYYY-MM-DD'),
ID_TYPE_CODE=P_ID_TYPE,
ID_NO=P_ID_NO,
INPUT_GENDER=P_GENDER,
INPUT_BIRTH_DATE=P_BIRTH_DATE,
INPUT_ID_TYPE=P_ID_TYPE,
INPUT_CARD_NO=P_CARD_NO,
READIN_DATE=sysdate
where bar_code_no=P_BARCODE;
else
insert into checkup_report_des_input
(
bar_code_no,
policy_no,
person_name,
GENDER_CODE,
BIRTH_DATE,
ID_TYPE_CODE,
ID_NO,
INPUT_GENDER,
INPUT_BIRTH_DATE,
INPUT_ID_TYPE,
INPUT_CARD_NO,
READIN_DATE,
CREATED_BY,
CREATED_DATE,
UPDATED_BY,
UPDATED_DATE
)values
(
P_BARCODE,
P_POLNO,
P_CUSTOMER_NAME,
v_gender,
to_date(v_birth_date,'YYYY-MM-DD'),
substr(v_id_type,0,2),
P_ID_NO,
P_GENDER,
P_BIRTH_DATE,
P_ID_TYPE,
P_CARD_NO,
sysdate,
'hcs',
sysdate,
'hcs',
sysdate
);
end if;
close testBarcode;


update checkup_report_main
set status = v_status,
customer_no = decode(v_status,'02',v_customer_no,customer_no),
description = v_description,
region_code = decode(v_status,'02',v_region_code,region_code)
where bar_code_no = P_BARCODE;

--只有02已匹配才写main表的卡号和保单号字段
if v_status = '02' then
update checkup_report_main
Set policy_no= nvl(p_polno,(select polno from pol_list where member_code=v_card_no and rownum=1)),
checkup_card_no = v_card_no
where bar_code_no = P_BARCODE;
end if;

--写关联情况表
open checkup_report_relate_exist;
fetch checkup_report_relate_exist into v_relate_exist;
if checkup_report_relate_exist%found then
update CHECKUP_REPORT_RELATE
set RELATE_RESULT = decode(v_status,'02','成功','失败'),
FAIL_DESC= decode(v_status,'02','',v_description)
where BAR_CODE_NO = P_BARCODE;

else
insert into CHECKUP_REPORT_RELATE(
ID,
UPLOAD_NO,
BAR_CODE_NO,
RELATE_RESULT,
FAIL_DESC,
CREATED_BY,
CREATED_DATE,
UPDATED_BY,
UPDATED_DATE
)values(
(seq_checkup_report_relate_id.nextval),
P_UPLOADNO,
P_BARCODE,
decode(v_status,'02','成功','失败'),
decode(v_status,'02','',v_description),
'hcs',
sysdate,
'hcs',
sysdate
);
end if;
close checkup_report_relate_exist;
--写更新记录表
insert into checkup_report_modify_history(seq_no,
bar_code_no,
old_status_code,
new_status_code,
modify_reason,
operator_id,
operate_date)
values(seq_checkup_rpt_mdi_his_seq_no.nextval,
P_BARCODE,
'06',
v_status,
'上载csv文件关联客户调HCS更新数据',
'EHISJOB',
sysdate);
end if;
close c_customer_count;
end if;

EXCEPTION
--例外处理，返回出错代码和信息
WHEN OTHERS THEN
ROLLBACK;
p_error_flag := '01'; --失败
p_error_msg := 'drs_return_update_check_up' || substr(SQLERRM, 1, 200);


END drs_return_update_check_up;
END hhcs_package_outbound;