1、整篇package
声明
create or replace package pkg_verify_filedtl_info is
procedure p_main(i_trans_id number);
procedure p_processValidate(i_detail_id number);
function p_validatePolicyNo(i_detail_id number) return varchar2;
function p_validateNext(i_detail_id number) return varchar2;
function p_validateInsuredDateBuild(i_detail_id number) return varchar2;
function p_validateCiEffExpDate(i_detail_id number) return varchar2;
end pkg_verify_filedtl_info;
实现
create or replace package body pkg_verify_filedtl_info is
procedure p_main(i_trans_id number) is
l_count number;
begin
--to verify wheather summary info is ok
select count(1)
into l_count
from t_ci_upload_log t
where t.trans_id = i_trans_id
-- err_code = 1 means summer info verfiy sucess
and t.err_code = 1;
if(l_count>1) then
for v_trans_detail in(
select d.detail_id
from t_ci_upload_log t,
t_ci_upload_file_detail d
where t.trans_id = i_trans_id
and t.err_code = 1
and t.detail_id = d.detail_id
)loop
p_processValidate(v_trans_detail.detail_id);
end loop;
end if;
end;
procedure p_processValidate(i_detail_id number) is
l_result varchar2(100);
begin
l_result := p_validatePolicyNo(i_detail_id);
if(l_result = 'ok') then
l_result := p_validateNext(i_detail_id);
end if;
if(l_result = 'ok') then
l_result := p_validateInsuredDateBuild(i_detail_id);
end if;
if(l_result = 'ok') then
l_result :=p_validateCiEffExpDate(i_detail_id);
end if;
if(l_result != 'ok') then
update t_ci_upload_log d
set d.err_code = l_result
where d.detail_id = i_detail_id;
update t_ci_upload_file_detail d
set d.process_status = 2
where d.detail_id = i_detail_id;
end if;
end;
function p_validatePolicyNo(i_detail_id number) return varchar2
as
l_count number;
l_result varchar2(100);
begin
select count(1)
into l_count
from t_ci_upload_file_detail d,
t_policy_general g
where d.detail_id = i_detail_id
and d.policy_no = g.policy_no
and g.contract_status_code=2;
if(l_count=0) then
--100:Policy No. not exsits
l_result := '100';
else
l_result := 'ok';
end if;
return l_result;
end;
function p_validateNext(i_detail_id number) return varchar2
as
l_count number;
l_result varchar2(100);
begin
select count(1)
into l_count
from t_ci_upload_file_detail d
where d.detail_id = i_detail_id
and d.insured_cpf is null;
if(l_count>0) then
--105 means insured cpf is null
l_result := '105';
else
l_result := 'ok';
end if;
return l_result;
end;
function p_validateInsuredDateBuild(i_detail_id number) return varchar2
as
l_count number;
l_result varchar2(100);
begin
select count(1)
into l_count
from t_ci_upload_file_detail d
where d.detail_id = i_detail_id
and (TO_DATE(TO_CHAR(sysdate, 'dd/MM/yyyy'), 'dd/MM/yyyy')-TO_DATE(TO_CHAR(d.insured_date_build,'dd/MM/yyyy'), 'dd/MM/yyyy'))/365 <70;
if(l_count>0) then
--101 means insured is less than 70 years
l_result := '101';
else
l_result := 'ok';
end if;
return l_result;
end;
---validate Ci EffDate and expdate
function p_validateCiEffExpDate(i_detail_id number) return varchar2
as
l_movementType number;
l_Idays number;
l_Edays number;
l_retroactiveDays number;
l_CalculationByProRate number;
l_result varchar2(100);
begin
select t.movement_type
into l_movementType
from t_ci_upload_file_detail t
where t.detail_id=i_detail_id;
select to_date(t.competence,'mmyyyy')-d.inclusion_date as days
into l_Idays
from t_ci_upload_file_detail d,
t_ci_upload_file_summary t
where d.detail_id = i_detail_id
and t.trans_id=d.trans_id;
select to_date(t.competence,'mmyyyy')-d.Exclusion_Date as days
into l_Edays
from t_ci_upload_file_detail d,
t_ci_upload_file_summary t
where d.detail_id = i_detail_id
and t.trans_id=d.trans_id;
select TO_NUMBER(t.retroactive_day)
into l_retroactiveDays
from t_cts_mp_contract t,
t_ci_upload_file_detail d,
t_mp_contract m
where t.mp_contract_id=m.mp_contract_id
and d.policy_no=m.mp_contract_code
and d.detail_id=i_detail_id;
select t.calculate_by_prorate
into l_CalculationByProRate
from t_cts_mp_contract t,
t_ci_upload_file_detail d,
t_mp_contract m
where t.mp_contract_id=m.mp_contract_id
and d.policy_no=m.mp_contract_code
and d.detail_id=i_detail_id;
if(l_movementType=1) then
if(l_Idays-l_retroactiveDays>0) then
--103 means certificate inclusion effective date is wrong generated
l_result := '103';
else
l_result := 'ok';
if(l_CalculationByProRate=1) then
update t_ci_upload_file_detail t
set t.eff_date=t.inclusion_date
where t.detail_id=i_detail_id;
else
update t_ci_upload_file_detail t
set t.eff_date=trunc(t.inclusion_date,'mm')
where t.detail_id=i_detail_id;
end if;
end if;
end if;
if(l_movementType=2) then
if(l_Edays-l_retroactiveDays>0) then
--104 means certificate excusion EXPIRY date is wrong generated
l_result := '104';
else
l_result := 'ok';
if(l_CalculationByProRate=1) then
update t_ci_upload_file_detail t
set t.Exp_Date=t.Exclusion_Date
where t.detail_id=i_detail_id;
else
update t_ci_upload_file_detail t
set t.Exp_Date=to_date(to_char(last_day(t.exclusion_date),'ddmmyyyy'),'ddmmyyyy')
where t.detail_id=i_detail_id;
end if;
end if;
end if;
if(l_movementType=3) then
if(l_CalculationByProRate=1) then
update t_ci_upload_file_detail t
set t.eff_date= (select sysdate from dual)
where t.detail_id=i_detail_id;
else
update t_ci_upload_file_detail t
set t.eff_date=(select trunc(sysdate,'mm') from dual)
where t.detail_id=i_detail_id;
end if;
end if;
return l_result;
end;
end pkg_verify_filedtl_info;
在Java文件中调用 调用存储过程 或者没有返回值的function ：
public void processAllFile(Long transId) throws Exception {
DBean db = new DBean();
Connection con = null;
CallableStatement cs = null;
try {
db.connect();
con = db.getConnection();
cs = con
.prepareCall("{Call pkg_verify_filedtl_info.p_main(?)}");
cs.setLong(1, transId);
cs.execute();
} catch (Exception e) {
throw e;
} finally {
DBUtils.clear(null, cs, db);
}
};
调用 pkg 里面有返回值的function
String policyId = null;
DBean db = new DBean();
CallableStatement cs = null;
try {
db.connect();
Connection con = db.getConnection();
String expression = "{ ? = call pkg_verify_filedtl_info.f_getCertNoByCNPJ(?,?) }";
cs = con.prepareCall(expression);
cs.registerOutParameter(1, java.sql.Types.LONGVARCHAR);
cs.setString(2, mpNo);
cs.setString(3, insuredCpf);
cs.execute();
policyId = cs.getString(1);
} catch (Exception ex) {
throw ExceptionFactory.parse(ex);
} finally {
cs.close();
db.close();
}
扫一扫
2879
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
