cursor是c_product,
r_product c_product%rowtype;
r_product 是cursor查询出的所有字段,然后用r_product.claimno可以使用具体的某个字段的值,当字段特别多的时候用%rowtype相当节省功夫。
create or replace
procedure sp_convert_claim_rider(buname in char)
as
v_row_count integer default 0;
v_sqlerrm varchar2(512);
v_covercd varchar2(20);
v_claimno varchar2(20);
v_lifeclaim varchar2(20);
v_jlind char(1);
v_enpw char(1);
v_sertax number(9,2);
v_lupvalue number(9,2);
v_poltype varchar2(20);
v_ontrandt date;
v_dtaccp date;
v_debug char(3);
v_temp_char varchar2(50);
v_polno msclmpolcc.polno%type;
cursor c_product
is
select cr.claimno,
cr.polno,
cr.covcode,
cr.sa,
cr.accstatus_a,
cr.basicind,
cr.claimtype,
cr.statuscc,
cr.reason,
cr.paystat,
cr.admitby,
cr.dtadmit,
nvl(cr.actamtpaid, 0.0) as actamtpaid,
r.covercd,
r.xmorind,
r.xmorper,
r.calprm,
r.benterm,
r.prmterm,
r.lpdd,
r.effect,
r.dtorginc as trndt , --- (XXX) TBD
'T' as prod_type
from msclmpolcc cr, mpolicya r
where cr.polno = r.polno and cr.covcode = r.covercd and r.xmorind is null
union all
select cr.claimno,
cr.polno,
cr.covcode,
cr.sa,
cr.accstatus_a,
cr.basicind,
cr.claimtype,
cr.statuscc,
cr.reason,
cr.paystat,
cr.admitby,
cr.dtadmit,
nvl(cr.actamtpaid, 0.0) as actamtpaid,
r.covercd,
r.xmorind,
r.xmorper,
r.calprm,
r.benterm,
r.prmterm,
r.lpdd,
r.effect,
r.trndt,
'L' as prod_type
from msclmpolcc cr, mulpola r
where cr.polno = r.polno and cr.covcode = r.covercd and r.xmorind is null
and r.trno =(select min(r1.trno)
from mulpola r1
where r.polno = r1.polno and r.covercd = r1.covercd);
r_product c_product%rowtype;
r_cprrid cprrid%rowtype;
begin
insert
into dmlog
(
prg,
type,
msg,
crntime
)
values
(
'sp_convert_rider',
'I',
'Starts',
systimestamp
);
commit;
open c_product;
loop
fetch c_product into r_product;
exit when c_product%notfound; -- v_row_count = 100000 ;--
v_row_count := v_row_count + 1;
----------------------
--- (1) rider header
r_cprrid.cccbscid := null;
r_cprrid.clmnum := r_product.claimno;
r_cprrid.cprpolid := null;
r_cprrid.plcnum := r_product.polno;
r_cprrid.coverage := r_product.covercd;
r_cprrid.rider := r_product.covercd;
----------------------
--- (2) life
begin
select (case when m.lanewicno is not null and trim(m.lanewicno) <> '' then m.lanewicno || 'NIC'
when m.laicno is not null and trim(m.laicno) <> '' then m.laicno || 'OIC'
else '' end ),
m.dtdeath,
m.enpw
into r_cprrid.life,
r_cprrid.mevtdte,
v_enpw
from msclmreg m
where m.claimno = r_product.claimno and rownum < 2;
exception
when no_data_found then
r_cprrid.life := '';
r_cprrid.mevtdte := null;
v_enpw := null;
end;
--- rider index
r_cprrid.rindex := r_product.polno||'-'||r_product.covcode||'-'||r_product.covcode||'-'||r_cprrid.life||r_product.covcode;
----------------------
--- (3) jlife and tax, total amount
begin
select p.lifeclaim, nvl(p.sertax, 0.0), nvl(p.lupvalue, 0.0), poltype
into v_lifeclaim, v_sertax, v_lupvalue, v_poltype
from msclmpol p
where p.claimno = r_product.claimno and p.polno = r_product.polno and rownum < 2;
exception
when no_data_found then
v_lifeclaim := null;
v_sertax := 0.0;
v_lupvalue := 0.0;
v_poltype := null;
end;
if v_lifeclaim = '1' then
r_cprrid.jlife := '0';
else
r_cprrid.jlife := '1';
end if;
----------------------
--- (4) rider code
r_cprrid.pcurf := '';
r_cprrid.pcurt := '';
r_cprrid.tranno := null;
r_cprrid.validflag := '2';
r_cprrid.prodcode := r_product.covercd;
r_cprrid.currency := 'MYR';
r_cprrid.sumins := r_product.sa;
r_cprrid.orgsumins := r_product.sa;
r_cprrid.adjsumins := r_product.sa;
----------------------
--- (5) MORTALITY
if r_product.prod_type = 'T' then
-- tranditional product
begin
select trim(xmorind), xmorper, calprm
into r_cprrid.mortcls, r_cprrid.mortprcnt, r_cprrid.extraprem
from mpolicya
where polno = r_product.polno and covercd = r_product.covercd and xmorind is not null and rownum < 2;
exception
when no_data_found then
r_cprrid.mortcls := null;
r_cprrid.mortprcnt := null;
r_cprrid.extraprem := 0;
end;
-- TODO:
-- begin
-- select xmorind
-- into r_cprrid.premload
-- from mpolicya
-- where polno = r_product.polno and covercd = r_product.covercd and xmorind = 'PM' and rownum < 2;
-- exception
-- when no_data_found then
-- r_cprrid.premload := 0;
-- end;
r_cprrid.premload := 0;
else
-- linked product
begin
select xmorind, xmorper, calprm
into r_cprrid.mortcls, r_cprrid.mortprcnt, r_cprrid.extraprem
from mulpola
where polno = r_product.polno and covercd = r_product.covercd and xmorind is not null and rownum < 2;
exception
when no_data_found then
r_cprrid.mortcls := null;
r_cprrid.mortprcnt := null;
r_cprrid.extraprem := 0;
end;
--TODO: --character to number conversion error
-- begin
-- select trim(xmorind)
-- into r_cprrid.premload
-- from mulpola
-- where polno = r_product.polno and covercd = r_product.covercd and xmorind = 'PM' and rownum < 2;
-- exception
-- when no_data_found then
-- r_cprrid.premload := 0;
-- end;
end if;
----------------------
--- (6) LA age, nx prem date, UWSTS, occupation class, dt
begin
select
p.laage,
p.npdd,
p.uwla,
p.jlind,
p.laocpcls,
p.ontrandt,
p.dtaccp
into r_cprrid.anbccd,
r_cprrid.nxpremdate,
r_cprrid.uwsts,
v_jlind,
r_cprrid.occcls,
v_ontrandt,
v_dtaccp
from mpolicy p
where p.polno = r_product.polno
and (r_cprrid.mevtdte >= p.dtonmov and r_cprrid.mevtdte <= p.dtofmov ) and rownum < 2;
exception
when no_data_found then
r_cprrid.anbccd := null;
r_cprrid.nxpremdate := null;
r_cprrid.uwsts := null;
v_jlind := null;
r_cprrid.occcls := null;
v_ontrandt := null;
v_dtaccp := null;
end;
if v_lifeclaim <> '1' then
begin
select p.agenb, occpcls
into r_cprrid.anbccd, r_cprrid.occcls
from mpolicyn p
where p.polno = r_product.polno and p.mtype = v_jlind and rownum < 2;
exception
when no_data_found then
r_cprrid.anbccd := null;
r_cprrid.occcls := null;
end;
end if;
----------------------
--- (7) status
r_cprrid.statcode := r_product.accstatus_a;
r_cprrid.cstatcode := r_product.accstatus_a;
r_cprrid.pstatcode := '';
r_cprrid.cpstatcode := '';
----------------------
--- (8) rider details
r_cprrid.prodprem := r_product.calprm;
r_cprrid.effterm := r_product.benterm;
r_cprrid.tpermterm := r_product.prmterm;
r_cprrid.crrcd := r_product.trndt;
r_cprrid.rcesdte := add_months(r_product.effect, 12 * r_product.benterm) - 1;
r_cprrid.terdate := add_months(r_product.effect, 12 * r_product.benterm) - 1;
r_cprrid.pcesdte := r_product.lpdd;
r_cprrid.bcesdte := r_product.lpdd;
r_cprrid.crdate := null;
r_cprrid.incpdate := r_product.effect;
r_cprrid.upgdate := null;
--- effective date
if r_product.claimtype = 'MEDICAL' then
begin
select t.coverdtfr
into r_cprrid.regdate
from tsclmpmm t
where t.polno = r_product.polno and t.covercd = r_product.covercd and rownum < 2;
exception
when no_data_found then
r_cprrid.regdate := null;
end;
r_cprrid.effdate := r_cprrid.regdate;
r_cprrid.generdate := r_cprrid.regdate;
else
r_cprrid.regdate := r_product.effect;
r_cprrid.effdate := r_product.effect;
r_cprrid.generdate := r_product.effect;
end if;
--- reinstatement date
begin
--select p.onmovdt
select p.dtonmov
into r_cprrid.reinsdate
from mpolicy p
where p.onmovcd in ('11', '19', '72', '18', '20', '17', '25', '26') -- Revival movement code
and rownum < 2;
exception
when no_data_found then
r_cprrid.reinsdate := null;
end;
--- opt
r_cprrid.opt1 := null;
r_cprrid.opt2 := null;
r_cprrid.opt3 := null;
r_cprrid.opt4 := null;
r_cprrid.opt5 := null;
r_cprrid.clntcode := null;
r_cprrid.decreview := null;
r_cprrid.cnvrtfr := null;
r_cprrid.cnvrtprod := null;
r_cprrid.cnvrtsa := null;
r_cprrid.baseind := r_product.basicind;
----------------------
--- (9) claim type
if r_product.claimtype in ('CA', 'DEATH') then
r_cprrid.mctype := 'DTH';
elsif r_product.claimtype in ('ACC', 'PDB') then
r_cprrid.mctype := 'TPD';
elsif r_product.claimtype = 'CC' then
r_cprrid.mctype := 'CIL';
elsif r_product.claimtype in ('PRULADY', 'SI', 'COMP') then
if buname = 'A' then
r_cprrid.mctype := 'OTH';
else
r_cprrid.mctype := 'CIL';
end if;
elsif r_product.claimtype in ('MEDICAL', 'INDEMNITY', 'AMR', 'SN') then
r_cprrid.mctype := 'MED';
else
r_cprrid.mctype := null;
end if;
----------------------
--- (10) evl status
r_cprrid.cfmastatus := 'Y';
r_cprrid.cfmstatus := 'Y';
if r_product.statuscc = 'E' then
r_cprrid.evlastatus := 'EXG';
elsif r_product.statuscc = 'A' then
r_cprrid.evlastatus := 'ELI';
elsif r_product.statuscc = 'D' then
r_cprrid.evlastatus := 'REJ';
elsif r_product.statuscc = 'C' then
r_cprrid.evlastatus := 'CLS';
else
r_cprrid.evlastatus := 'PED';
end if;
r_cprrid.evlstatus := r_cprrid.evlastatus;
r_cprrid.clsstatus := r_cprrid.evlastatus;
r_cprrid.csclmsts := r_cprrid.evlastatus;
r_cprrid.dupastatus := 'N';
r_cprrid.dupstatus := 'N';
r_cprrid.relastatus := null;
r_cprrid.relstatus := null;
--- rejection
if r_cprrid.evlastatus = 'REJ' then
begin
r_cprrid.rejlcode := trim(r_product.reason);
exception
when others then
dbms_output.put_line(length(trim(r_product.reason)));
end;
begin
select descdeath
into r_cprrid.rejdscr
from mtclmilld
where code = r_product.reason and rownum < 2;
exception
when no_data_found then
r_cprrid.rejdscr := '';
end;
else
r_cprrid.rejlcode := null;
r_cprrid.rejdscr := null;
end if;
--- exg
if r_cprrid.evlastatus = 'EXG' then
r_cprrid.exgrapct := trim(r_product.reason);
else
r_cprrid.exgrapct := null;
end if;
r_cprrid.pybkind := null;
r_cprrid.rescstatus := (case when r_cprrid.mctype in ('DTH', 'TPD') then 'R' else 'N' end);
r_cprrid.cresccode := null;
r_cprrid.resccode := null;
r_cprrid.rescdate := null;
r_cprrid.rsstrtdate := null;
r_cprrid.rsenddate := null;
r_cprrid.wpind := (case when v_enpw = 'Y' then '1' else '0' end);
r_cprrid.wpaind := r_cprrid.wpind;
r_cprrid.wpbenfid := null;
r_cprrid.autwpbeid := null;
r_cprrid.wpstrtdate := null;
r_cprrid.wpenddate := null;
r_cprrid.wopinstnum := null;
r_cprrid.wopinstfreq:= null;
r_cprrid.wpamt := null;
----------------------
--- (11) premium
begin
select min(m.duedt), max(m.duedt), sum(nvl(m.amount, 0))
into r_cprrid.prstrtdate, r_cprrid.prenddate, r_cprrid.pramt
from mpremium m
where m.polno = r_product.polno and m.duedt >= r_cprrid.mevtdte
and m.duedt <= r_cprrid.nxpremdate and rownum <=2;
exception
when no_data_found then
r_cprrid.prstrtdate := null;
r_cprrid.prenddate := null;
r_cprrid.pramt := null;
end;
----------------------
--- (12) payment amount
r_cprrid.pyamt := 0.0;
r_cprrid.pocurrency := 'MYR';
r_cprrid.latefee := 0.0;
r_cprrid.polaccval := 0.0;
r_cprrid.odedctpay := 0.0;
r_cprrid.polaccadj := 0.0;
r_cprrid.latefeeadj := 0.0;
r_cprrid.rndadjust := 0.0;
r_cprrid.cmpamt := 0.0;
if r_cprrid.baseind = 'Y' and r_cprrid.mctype = 'DTH' then
-- basic plan for death
r_cprrid.totpayamt := r_product.actamtpaid + v_lupvalue;
else
-- other riders
r_cprrid.totpayamt := r_product.actamtpaid;
end if;
if r_cprrid.baseind = 'Y' then
r_cprrid.taxamt := v_sertax;
else
r_cprrid.taxamt := 0.0;
end if;
----------------------
--- (13) TODO: newly required fields for MY
r_cprrid.grosslimit := r_product.sa;
r_cprrid.trno := 1;
-- TODO: NO TABLE STRUCTURE
-- BEGIN
-- select t.category
-- into r_cprrid."account"
-- from tpolcov t
-- where t.poltype = v_poltype and t.covercd = r_product.covcode and rownum < 2;
-- EXCEPTION
-- WHEN no_data_found THEN
-- r_cprrid."account" := null;
-- END;
begin
select m.annexcd
into r_cprrid.annexcd
from mtannexcd m
where m.poltype = v_poltype and m.covercd = r_product.covcode and rownum < 2;
exception
when no_data_found then
r_cprrid.annexcd := null;
end;
if buname = 'A' then
-- PAMB
if r_product.polno < '32800000' then
-- tranditional product
r_cprrid.coverdt := r_product.trndt;
elsif r_product.polno >= '32800000' and r_product.polno < '48000000' then
-- linked product
r_cprrid.coverdt := v_ontrandt;
elsif r_product.polno >= '48000000' and r_product.polno < '72800000' then
-- group product
r_cprrid.coverdt := v_dtaccp;
else
r_cprrid.coverdt := null;
end if;
else
-- PBTB
if r_product.polno >= '95000000' and r_product.polno < '99999999' then
-- linked product
r_cprrid.coverdt := v_ontrandt;
elsif r_product.polno >= '40000000' and r_product.polno < '89999999' then
-- group product
r_cprrid.coverdt := v_dtaccp;
else
-- tranditional product
r_cprrid.coverdt := r_product.trndt;
end if;
end if;
begin
select s.defer_prd, s.int_rate
into r_cprrid.graceperiod, r_cprrid.interestrate
from mpolicys2 s
where s.polno = r_product.polno and rownum < 2;
exception
when no_data_found then
r_cprrid.graceperiod := null;
r_cprrid.interestrate := null;
end;
--
sp_usrid(r_product.admitby,r_cprrid.crtby,r_cprrid.crtbyn,v_temp_char);
r_cprrid.lastmoddte := r_product.dtadmit;
sp_usrid(r_product.admitby,r_cprrid.lastmodby,r_cprrid.lastmodbyn,v_temp_char);
r_cprrid.lastmodbyn := 'NCS_CONV';
----------------------
--- (14) finally insert into rider table
insert into cprrid values r_cprrid;
if mod(v_row_count,5000) = 0 then
commit;
end if;
end loop;
close c_product;
insert
into dmlog
(
prg,
type,
msg,
crntime
)
values
(
'sp_convert_claim_rider',
'I',
'succeed in '|| v_row_count||' rows',
systimestamp
);
commit;
exception
when others then
dbms_output.put_line
(
'SQLCODE = '||sqlcode
)
;
dbms_output.put_line
(
'SQLERRM = '||sqlerrm
)
;
dbms_output.put_line
(
'DBMS_UTILITY.FORMAT_ERROR_STACK = '||dbms_utility.format_error_stack
)
;
dbms_output.put_line
(
'DBMS_UTILITY.FORMAT_ERROR_BACKTRACE = '|| dbms_utility.format_error_backtrace
)
;
v_sqlerrm := substr
(
'error record key is claimno='||r_product.claimno||',polocyno is '||r_product.polno||',covercd is '||r_product.covercd||',SQLCODE = '||sqlcode||' '||dbms_utility.format_error_stack||' '|| dbms_utility.format_error_backtrace,1,255
)
;
dbms_output.put_line
(
v_sqlerrm
)
;
insert
into dmlog
(
prg,
type,
msg,
crntime
)
values
(
'sp_convert_rider',
'E',
v_sqlerrm,
systimestamp
);
commit;
end sp_convert_claim_rider;