oracle package

begin
-- Call the procedure
pkg_new_bddj3_20200703.p_insert_websale_main_piece_v3(to_date('2020-07-02','yyyy-mm-dd'),null);
end;
/

create or replace package PKG_NEW_BDDJ3_20200703 is
--存储信息主程序
PROCEDURE p_insert_websale_main_piece_v3(i_date in date ,i_contno in varchar2);
--存储个模块保单信息
--提取之前先删除所有中间表
PROCEDURE p_delete_zbxtempv3table;

PROCEDURE p_insert_busino_v3(i_strdate in date);
PROCEDURE p_insert_businobycont_v3(i_strdate in date,i_contno in varchar2);
--1
PROCEDURE p_insert_lcpoltransaction_v3(i_strdate in date);
--2
PROCEDURE p_insert_lccont_v3(i_strdate in date);
--3
PROCEDURE p_insert_lcprodinsurela_v3(i_strdate in date);
--4
PROCEDURE p_insert_lcproduct_v3(i_strdate in date);
--5
PROCEDURE p_insert_LCLiability_v3(i_strdate in date);
--6
PROCEDURE p_insert_lcbnf_v3(i_strdate in date);
--7
PROCEDURE p_insert_lcinsured_v3(i_strdate in date);
--8
PROCEDURE p_insert_lcinsureacc_v3(i_strdate in date);
--9
PROCEDURE p_insert_llclaimpolicy_v3(i_strdate in date);
--10
PROCEDURE p_insert_llbnf_v3(i_strdate in date);
--11
PROCEDURE p_insert_LLClaimDetail_v3(i_strdate in date);
--12
PROCEDURE p_insert_LLClaimInfo_v3(i_strdate in date);
--13
PROCEDURE p_insert_LLClaimantInfo_v3(i_strdate in date);
--14
PROCEDURE p_insert_LLReceipt_v3(i_strdate in date);
--15
PROCEDURE p_insert_LLCostItem_v3(i_strdate in date);
--16
PROCEDURE p_insert_LLDisease_v3(i_strdate in date);
--17
PROCEDURE p_insert_LLOperation_v3(i_strdate in date);
--18
PROCEDURE p_insert_lasales_v3(i_strdate in date);
--19
PROCEDURE p_insert_laagent_v3(i_strdate in date);
--20
PROCEDURE p_insert_LACommission_v3(i_strdate in date);
--21
PROCEDURE p_insert_ljtempfee_v3(i_strdate in date);
--22
PROCEDURE p_insert_ljdivdistrib_v3(i_strdate in date);
--23
PROCEDURE p_insert_ljagetlivbene_v3(i_strdate in date);
--24
PROCEDURE p_insert_ljapay_v3(i_strdate in date);
--25
PROCEDURE p_insert_lmproduct_v3(i_strdate in date);
--26
PROCEDURE p_insert_ljaget_v3(i_strdate in date);
--27
PROCEDURE p_insert_LAllUnderWriting_v3(i_strdate in date);
--28
PROCEDURE p_insert_LCBlacklist_v3(i_strdate in date);

--3期新增
PROCEDURE p_insert_lcinsureacctrace_v3(i_strdate in date);
--29保全补退费表
PROCEDURE p_insert_ljagetendorse_v3(i_strdate in date);
--30客户回访表
PROCEDURE p_insert_lccusrecall_v3(i_strdate in date);
--31再保产品信息表
PROCEDURE p_insert_lrproduct_v3(i_strdate in date);
--32再保合同信息表
PROCEDURE p_insert_LRInsureCont_v3(i_strdate in date);
--33再保账单信息表
PROCEDURE p_insert_LRAccount_v3(i_strdate in date);
--34再保首续期险种明细表
PROCEDURE p_insert_LRCont_v3(i_strdate in date);
--35再保保全变更信息表
PROCEDURE p_insert_LREdor_v3(i_strdate in date);
--36再保理赔信息表
PROCEDURE p_insert_LRClaim_v3(i_strdate in date);
--37再保生存金信息表
PROCEDURE p_insert_LRBenefit_v3(i_strdate in date);
--38保单借款及自垫信息表
PROCEDURE p_insert_LJLoan_v3(i_strdate in date);
--39保单还款信息表
PROCEDURE p_insert_LJLoanRepayment_v3(i_strdate in date);
--40保单增值税信息表
PROCEDURE p_insert_LJTax_v3(i_strdate in date);
--41保单发票关联表
PROCEDURE p_insert_ljinvRelation_v3(i_strdate in date);
--42保单发票信息表
PROCEDURE p_insert_LJInvoice_v3(i_strdate in date);

--43险种责任定义表
PROCEDURE p_insert_LMLiability_v3(i_strdate in date);
--删除中保信表
--PROCEDURE p_delete_zbxtemtable_v3;

--44
procedure p_changecode_v3;

procedure p_insert_zbx_v3_logs(i_pushdate in date,i_msg in varchar2);

end PKG_NEW_BDDJ3_20200703;
/
CREATE OR REPLACE PACKAGE BODY PKG_NEW_BDDJ3_20200703 is
--存储过程主函数
PROCEDURE p_insert_websale_main_piece_v3(i_date in date,i_contno in varchar2) AS

m_sysdate date;
v_sqlerrm varchar2(300);
m_contno varchar2(20);
BEGIN
--判断是否传具体日期
m_sysdate := i_date;
if m_sysdate is null then
m_sysdate := to_date(to_char(sysdate - 1, 'yyyy-mm-dd'), 'yyyy-mm-dd');
-- -- dbms_output.put_line('null');
-- dbms_output.put_line(m_sysdate);
else
m_sysdate := i_date;
-- dbms_output.put_line('y');
end if;

-- 删除本地中间表
p_delete_zbxtempv3table;
--判断保单号是否为空
m_contno := i_contno;
if m_contno is null then
--生成对应日期的交易流水号
p_insert_busino_v3(m_sysdate);
else
p_insert_businobycont_v3(m_sysdate,m_contno);
end if ;
--保单交易表
p_insert_lcpoltransaction_v3(m_sysdate);
-- -- dbms_output.put_line('1');
--个人保单表
p_insert_lccont_v3(m_sysdate);
-- -- dbms_output.put_line('2');
--被保险人险种关联表
p_insert_lcprodinsurela_v3(m_sysdate);
-- -- dbms_output.put_line('3');
--个人险种表
p_insert_lcproduct_v3(m_sysdate);
-- -- dbms_output.put_line('4');
--保单险种责任表
p_insert_LCLiability_v3(m_sysdate);
-- -- dbms_output.put_line('5');
--受益人表
p_insert_lcbnf_v3(m_sysdate);
-- -- dbms_output.put_line('6');
--被保险人信息表
p_insert_lcinsured_v3(m_sysdate);
-- dbms_output.put_line('7');
--保险账户表
p_insert_lcinsureacc_v3(m_sysdate);
-- dbms_output.put_line('8');
--理赔保单明细表
p_insert_llclaimpolicy_v3(m_sysdate);
-- dbms_output.put_line('9');
--理赔受益人表
p_insert_llbnf_v3(m_sysdate);
-- dbms_output.put_line('10');
--理赔险种责任项表
p_insert_LLClaimDetail_v3(m_sysdate);
-- dbms_output.put_line('11');
--理赔案件信息表
p_insert_LLClaimInfo_v3(m_sysdate);
-- dbms_output.put_line('12');
--出险人信息表
p_insert_LLClaimantInfo_v3(m_sysdate);
-- dbms_output.put_line('13');
--医疗收据信息表
p_insert_LLReceipt_v3(m_sysdate);
-- dbms_output.put_line('14');
--医疗费用项目表
p_insert_LLCostItem_v3(m_sysdate);
-- dbms_output.put_line('15');
--疾病信息表
p_insert_LLDisease_v3(m_sysdate);
-- dbms_output.put_line('16');
--手术信息表
p_insert_LLOperation_v3(m_sysdate);
-- dbms_output.put_line('17');
--保险公司销售服务人员信息表
p_insert_lasales_v3(m_sysdate);
-- dbms_output.put_line('18');
-- 保险中介渠道及从业人员信息表
p_insert_laagent_v3(m_sysdate);
-- dbms_output.put_line('19');
--保单佣金信息表
p_insert_LACommission_v3(m_sysdate);
-- dbms_output.put_line('20');
--暂收费表
p_insert_ljtempfee_v3(m_sysdate);
-- dbms_output.put_line('21');
--红利分配
p_insert_ljdivdistrib_v3(m_sysdate);
-- dbms_output.put_line('22');
--生存金领取
p_insert_ljagetlivbene_v3(m_sysdate);
-- dbms_output.put_line('23');
--保费明细表
p_insert_ljapay_v3(m_sysdate);
-- dbms_output.put_line('24');
--险种定义表
p_insert_lmproduct_v3(m_sysdate);
-- dbms_output.put_line('25');
--付费明细表
p_insert_ljaget_v3(m_sysdate);
-- dbms_output.put_line('26');
--保单险种核保信息表
p_insert_LAllUnderWriting_v3(m_sysdate);
-- dbms_output.put_line('27');
--客户黑名单信息表
p_insert_LCBlacklist_v3(m_sysdate);
-- dbms_output.put_line('28');
--3期新增表存储
--账户履历表
p_insert_lcinsureacctrace_v3(m_sysdate);
-- dbms_output.put_line('29');
--保全补退费表
p_insert_ljagetendorse_v3(m_sysdate);
-- dbms_output.put_line('30');
--客户回访表
p_insert_lccusrecall_v3(m_sysdate);
-- dbms_output.put_line('31');

--保单借款及自垫信息表
p_insert_LJLoan_v3(m_sysdate);
-- dbms_output.put_line('39');
--保单还款信息表
p_insert_LJLoanRepayment_v3(m_sysdate);
-- dbms_output.put_line('40');
--保单增值税信息表
p_insert_LJTax_v3(m_sysdate);
-- dbms_output.put_line('41');
--保单发票关联表
p_insert_ljinvRelation_v3(m_sysdate);
-- dbms_output.put_line('42');
--保单发票信息表
p_insert_LJInvoice_v3(m_sysdate);
-- dbms_output.put_line('43');
--险种责任定义表
p_insert_LMLiability_v3(m_sysdate);
-- dbms_output.put_line('44');

-------------------再保begin------------------------
--再保产品信息表
p_insert_lrproduct_v3(m_sysdate);
-- dbms_output.put_line('32');
--再保合同信息表
p_insert_LRInsureCont_v3(m_sysdate);
-- dbms_output.put_line('33');
--再保账单信息表
p_insert_LRAccount_v3(m_sysdate);
-- dbms_output.put_line('34');
--再保首续期险种明细表
p_insert_LRCont_v3(m_sysdate);
-- dbms_output.put_line('35');
--再保保全变更信息表
p_insert_LREdor_v3(m_sysdate);
-- dbms_output.put_line('36');
--再保理赔信息表
p_insert_LRClaim_v3(m_sysdate);
-- dbms_output.put_line('37');
--再保生存金信息表
p_insert_LRBenefit_v3(m_sysdate);
-- dbms_output.put_line('38');
-------------------再保end------------------------

--删除中保信表
--p_delete_zbxtemtable_v3;
---推送至保单登记平台
p_changecode_v3;
-- dbms_output.put_line('45');
--提数成功记录
--insert into t_log_error values (sysdate, '提数成功', '');
--commit;
p_insert_zbx_v3_logs(m_sysdate,'提数成功');
--如有异常 记录异常信息到表
EXCEPTION
WHEN OTHERS THEN
begin
p_insert_zbx_v3_logs(m_sysdate,'insert_websale_main_piece'||SQLERRM || '|' ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||'');
end;
-- dbms_output.put_line('46');
END p_insert_websale_main_piece_v3;

-- -2
PROCEDURE p_insert_businobycont_v3(i_strdate in date,i_contno in varchar2) as
begin
insert into updatetemp_v3
select *
from (select '000103' ||
SUBSTR(to_char(i_strdate, 'yyyy-mm-dd'), 1, 4) ||
SUBSTR(to_char(i_strdate, 'yyyy-mm-dd'), 6, 2) ||
substr(to_char(i_strdate, 'yyyy-mm-dd'), 9, 2) ||
lpad(rownum, 10, '0') as busino,
OTHERNO,
CONTNO,
OTHERNOTYPE,
nvl(TRANSAMNT, '0.00'),
TRANSDATE ,
standbystring1,
standbystring2
from ( --02：承保
select contno as otherno,
contno as contno,
'02' as othernotype,
prem as transamnt,
signdate as transdate,
'' standbystring1,
'' standbystring2
from lccont
where signdate = i_strdate
and appflag = '1'
and contno = i_contno
union all
--03续期
select incomeno as otherno,
incomeno as contno,
'03' as othernotype,
sumactupaymoney as transamnt,
enteraccdate as transdate,
'' standbystring1,
'' standbystring2
from ljapay
where (paytypeflag != '1' or paytypeflag is null)
and othernotype in ('2', '3')
and enteraccdate = i_strdate
and incomeno = i_contno
union all
--04:续保
select incomeno as otherno,
incomeno as contno,
'04' as othernotype,
sumactupaymoney as transamnt,
enteraccdate as transdate,
'' standbystring1,
'' standbystring2
from ljapay
where paytypeflag = '1'
and othernotype in ('2', '3')
and enteraccdate = i_strdate
and incomeno = i_contno
union all
--05:满期
select otherno as otherno,
contno as contno,
'05' as othernotype,
money as transamnt,
paydate as transdate,
'' standbystring1,
'' standbystring2
from lcinsureacctrace
where insuaccno = '000009'
and paydate = i_strdate
and contno = i_contno
union all
--06:保单终止；07：失效
select distinct contno as otherno,
contno as contno,
(case
when statetype = 'Terminate' then
'06'
when statetype = 'Available' then
'07'
end) as othernotype,
0 as transamnt,
startdate as transdate,
'' standbystring1,
'' standbystring2
from lccontstate
where state = '1'
and statetype in ('Terminate','Available')
and startdate = i_strdate
and contno = i_contno
union all
--11:自动垫交
select distinct contno as otherno,
contno as contno,
'11' as othernotype,
0 as transamnt,
modifydate as transdate,
'' standbystring1,
'' standbystring2
from lccontstate
where statetype = 'PayPrem'
and state = '1'
and enddate is null
and modifydate = i_strdate
and contno = i_contno
union all
--15:理赔
select aa.clmno as otherno,
aa.contno as contno,
'15' as othernotype,
-round(sum(aa.pay), 2) as transamnt,
bb.ExamDate as transdate,
'' standbystring1,
'' standbystring2
from llbalance aa, LLCLAIMUWMAIN bb
where aa.clmno = bb.caseno
and bb.ExamDate = i_strdate
and aa.contno = i_contno
group by aa.clmno, aa.contno, bb.ExamDate
union all
--16:生存金领取
select actugetno as otherno,
contno as contno,
'16' as othernotype,
GETMONEY as transamnt,
GETDATE as transdate,
'' standbystring1,
'' standbystring2
from LJAGetDraw
where GETDATE = i_strdate
and contno = i_contno
and feefinatype='YF'
union all
select lc.contno as otherno ,
lc.contno as contno ,
'16' as othernotype,
lc.money as transamnt,
lc.makedate as transdate,
lc.serialno standbystring1,
'' standbystring2
from lcinsureacctrace lc
where lc.moneytype = 'LVTI'
and lc.insuaccno = '000006'
and lc.makedate = i_strdate
and lc.contno = i_contno
--16a:红利转万能中间表 20200528 chenzhuo start
union all
select lc.contno as otherno ,
lc.contno as contno ,
'16a' as othernotype,
lc.money as transamnt,
lc.makedate as transdate,
lc.serialno standbystring1,
'' standbystring2
from lcinsureacctrace lc
where lc.moneytype = 'HTW'
and lc.insuaccno = '000006'
and lc.makedate = i_strdate
and lc.contno = i_contno
--16a:红利转万能中间表 20200528 chenzhuo end
union all
--17:分红
select contno as otherno,
contno as contno,
'17' as othernotype,
bonusmoney as transamnt,
makedate as transdate,
'' standbystring1,
'' standbystring2
from lobonuspol
where makedate = i_strdate
and contno = i_contno
union all--2019-7-29保额分红
select contno as otherno,
contno as contno,
'17' as othernotype,
bonusamnt as transamnt,
makedate as transdate,
'' standbystring1,
'' standbystring2
from loengbonuspol
where makedate = i_strdate
and contno = i_contno
union all
--保全：18-38
select item.edoracceptno as otherno,
item.contno as contno,
(case
when item.edortype = 'RE' then
'08' --复效
when item.edortype = 'DP' then
'09' --缓缴
when item.edortype = 'TS' then
'10' --复缴
when item.edortype = 'TR' then
'12' --还垫（保单自垫清偿）
when item.edortype = 'LN' then
'13' --借款（保单贷款）
when item.edortype = 'RF' then
'14' --还款（保单贷款清偿）
when item.edortype = 'AM' then
'18' --被保人资料变更
when item.edortype = 'IO' then
'19' --被保人职业变更
when item.edortype = 'IC' then
'20' --客户资料变更
when item.edortype = 'AE' then
'21' --投保人变更
when item.edortype = 'BC' then
'22' --受益人变更
when (item.edortype = 'AA' or
item.edortype = 'WP' or
item.edortype = 'IP') then
'24' --增加保额 (AA:附加险加保,WP:万能追加保费,IP:投连追加保费)
when (item.edortype = 'PT' or
item.edortype = 'XS' or
item.edortype = 'RT') then
'25' --减少保额(PT:减保,XS:协议减保,RT:犹豫期减保)
when item.edortype = 'PP' and
item.standbyflag1 = '1' then
'26' --保单质押
when item.edortype = 'PP' and
item.standbyflag1 = '0' then
'27' --保单质押解除
when item.edortype = 'NS' or edortype = 'NR' then
'28' --新增附加险(NR:被保人增加险种，NS:新增附加险)
when item.edortype = 'PR' then
'29' --保单迁移
when item.edortype = 'PU' then
'30' --减额缴清
when item.edortype = 'CT' then
'31' --退保
when item.edortype = 'WT' then
'32' --犹豫期退保
when item.edortype = 'PM' then
'33' --缴费方式变更 (缴费频率变更PM)
when item.edortype = 'GC' then
'34' --生存金领取方式变更（生存给付转账授权变更GC）
/*when item.edortype = '' then
'35' --险种变更 */
when item.edortype = 'TI' then
'36' --账户转换 (投连账户转换)
when item.edortype in ('OP','AR') then
'37' --部分领取 (万能部分领取)
when item.edortype in ('UP', 'WP') then
'38' --追加保费 (万能追加保费)
when item.edortype = 'FM' then
'61' --缴费期限变更
when item.edortype = 'RE' then
'67' --保单复效
when item.edortype in ('DB', 'LG', 'OP') then
'69' --账户领取
when item.edortype = 'GB' then
'70' --领取年龄变更
else
'99'
end) as othernotype,
item.getmoney as transamnt,
item.edorvalidate as transdate,
'' standbystring1,
'' standbystring2
from lpedoritem item
where item.edorstate = '0'
and item.edorvalidate = i_strdate
and item.contno = i_contno
--39:理赔回退
union all
select a.backno as otherno,
b.contno as contno,
'39' as othernotype,
b.realpay as transamnt,
a.backdate as transdate,
'' standbystring1,
'' standbystring2
from llcaseback a, llclaimpolicy b
where a.clmno = b.clmno
and a.backdate = i_strdate
and b.contno = i_contno
union all
-- 40：万能险结息和月结处理
select polno as otherno,
contno as contno,
'40' as othernotype,
0 as transamnt,
paydate as transdate,
'' standbystring1,
'' standbystring2
from lcinsureacctrace
where insuaccno = '000006'
and state = '0'
and moneytype in
('CS', 'FX', 'GL', 'HTW', 'LX', 'LVTI')
and paydate = i_strdate
and contno = i_contno
group by contno, polno, paydate
-- 41：投连计价和月终处理
union all
select otherno as otherno,
contno as contno,
'41' as othernotype,
round(sum(money),'2') as transamnt,
modifydate as transdate,
riskcode standbystring1,
'' standbystring2
from lcinsureacctrace_invest
where insuaccno <> '000000'
and state = '1'
and moneytype in
( 'FX', 'GL', 'BF', 'TI', 'IN','TBFY','EFLQ','WF','JJ','IV','TB','WT','CL')
and modifydate = i_strdate
and contno = i_contno
group by contno, otherno, modifydate,riskcode
union all
--47:产品报送
select '000000' as otherno,
'' as contno,
'47' as othernotype,
0 as transamnt,
i_strdate as transdate,
'' standbystring1,
'' standbystring2
from dual
where 1 = 1
union all
--57：拒保 新契约
select contno as otherno,
contno as contno,
'57' as othernotype,
0 as transamnt,
modifydate as transdate,
'' standbystring1,
'' standbystring2
from lccuwmaster
where modifydate = i_strdate
and passflag = '1'
and contno = i_contno
union all
--57：拒保 保全
select lpd.edoracceptno as otherno,
lpu.contno as contno,
'57' as othernotype,
0 as transamnt,
lpu.modifydate as transdate,
'' standbystring1,
'' standbystring2
from lpcuwmaster lpu, lpedoritem lpd
where lpu.modifydate = i_strdate
and lpu.passflag = '1'
and lpu.contno = lpd.contno
and lpu.edorno = lpd.edorno
and lpu.edortype = lpd.edortype
and lpu.contno = i_contno
union all
--57：拒保 理赔
select caseno as otherno,
contno as contno,
'57' as othernotype,
0 as transamnt,
modifydate as transdate,
'' standbystring1,
'' standbystring2
from llcuwmaster
where modifydate = i_strdate
and passflag = '1'
and contno = i_contno
union all
--57：拒保 续期
select contno as otherno,
contno as contno,
'57' as othernotype,
0 as transamnt,
modifydate as transdate,
'' standbystring1,
'' standbystring2
from rnewinduwmaster
where modifydate = i_strdate
and passflag = '1'
and contno = i_contno
union all
--58：延期 新契约
select contno as otherno,
contno as contno,
'58' as othernotype,
0 as transamnt,
modifydate as transdate,
'' standbystring1,
'' standbystring2
from lccuwmaster
where modifydate = i_strdate
and passflag = '2'
and contno = i_contno
union all
--58：延期 保全
select lpd.edoracceptno as otherno,
lpu.contno as contno,
'58' as othernotype,
0 as transamnt,
lpu.modifydate as transdate,
'' standbystring1,
'' standbystring2
from lpcuwmaster lpu, lpedoritem lpd
where lpu.modifydate = i_strdate
and lpu.passflag = '2'
and lpu.contno = lpd.contno
and lpu.edorno = lpd.edorno
and lpu.edortype = lpd.edortype
and lpu.contno = i_contno
union all
--58：延期 续期
select contno as otherno,
contno as contno,
'57' as othernotype,
0 as transamnt,
modifydate as transdate,
'' standbystring1,
'' standbystring2
from rnewinduwmaster
where modifydate = i_strdate
and passflag = '2'
and contno = i_contno
union all
--59：回执签收
select contno as otherno,
contno as contno,
'59' as othernotype,
0 as transamnt,
customgetpoldate as transdate,
'' standbystring1,
'' standbystring2
from lccont
where customgetpoldate = i_strdate
and contno = i_contno
--62:保全二核
union all
select DISTINCT lpe.edoracceptno as otherno,
lpu.contno as contno,
'62' as othernotype,
0 as transamnt,
lpu.modifydate as transdate,
'' standbystring1,
'' standbystring2
from lpedoritem lpe,lpuwmaster lpu
where lpe.edorno = lpu.edorno
and lpe.contno = lpu.contno
and lpu.passflag <> '1'
and lpu.modifydate = i_strdate
and lpu.contno = i_contno
--63：理赔二核
union all
select llc.clmno as otherno,
llu.contno as contno,
'63' as othernotype,
0 as transamnt,
llu.modifydate as transdate,
'' standbystring1,
'' standbystring2
from llclaimpolicy llc,lluwmaster llu
where llc.clmno = llu.caseno
and llu.passflag <> '1'
and llu.modifydate = i_strdate
and llu.contno = i_contno
union all
--66：保单失效
select polno as otherno,
contno as contno,
'66' as othernotype,
0 as transamnt,
i_strdate as transdate,
'' standbystring1,
'' standbystring2
from lcpol
where paytodate < payenddate
and paytodate + 60 = i_strdate
and appflag = '1'
and contno = i_contno
union all
--99:佣金信息
select to_char(item_id) as otherno,
policy_code as contno,
'99' as othernotype,
-commision as transamnt,
i_strdate as transdate,
'' standbystring1,
'' standbystring2
from MV_GX_COMMISION_INFO
where to_char(update_time,'yyyy-mm-dd') = to_char(I_STRDATE,'yyyy-mm-dd')
and policy_code = i_contno
--黑名单
/* union all
SELECT substr(bu.id,-30) AS OTHERNO,
'' AS CONTNO,
'71' AS OTHERNOTYPE,
0 AS TRANSAMNT,
i_strdate AS TRANSDATE,
'' standbystring1,
'' standbystring2
FROM busi_blacklist bu
WHERE to_char(bu.first_insert,'yyyy-mm-dd') = to_char(I_STRDATE,'yyyy-mm-dd')*/
--72 分保
union all
SELECT RRM.Reins_Seq AS OTHERNO,
RRM.POLICY_NO AS CONTNO,
'72' AS OTHERNOTYPE,
RRM.REINS_PREM AS TRANSAMNT,
I_STRDATE AS TRANSDATE,
'' standbystring1,
'' standbystring2
FROM RIS_REINSURANCE_MONTH RRM
WHERE to_char(RRM.CREATE_DATE,'yyyy-mm-dd') = to_char(I_STRDATE,'yyyy-mm-dd')
and reins_prem >0
and exists (select 1 from RIS_TREATY_INFO rit where rit.treaty_no = rrm.treaty_no)
and policy_no = i_contno
--73 客户回访
union all
select
tc.policy_code as otherno,
tc.policy_code as contno,
'73' as othernotype,
tc.total_prem as transamnt,
i_strdate as transdate,
'' standbystring1,
'' standbystring2
from t_cc_visit tc
where tc.hf_date= i_strdate
and policy_code = i_contno
--74 增值税计算
union all
select
cs.pk_outbillflow as otherno,
cs.transerial as contno,
'74' as othernotype,
cs.tranamt as transamnt,
i_strdate as transdate,
'' standbystring1,
'' standbystring2
from cs_outbillflow cs
where cs.vdata = to_char(i_strdate,'yyyy-mm-dd')
and cs.busino like 'PLIS%'
and cs.transerial = i_contno
--75 开具发票
union all
select
distinct
a.billnum as otherno,
b.transerial as contno,
'75' as othernotype,
a.totalpts as transamnt,
i_strdate as transdate,
'' standbystring1,
'' standbystring2
from vt_billopen a,cs_outbillflow b,vt_billexe e
where
a.fdate = to_char(i_strdate,'yyyy-mm-dd')
and a.pk_billopen = e.pk_billopen
and e.pk_outbillflow = b.pk_outbillflow
and b.busino like 'PLIS%'
and b.transerial = i_contno
--76 保全补退费
union all
select
lj.polno as otherno ,
lj.contno as contno ,
'76' as othernotype ,
lj.getmoney as transamnt ,
lj.GetConfirmDate as transdate,
lj.actugetno as standbystring1,
lj.subfeeoperationtype as standbystring2
from ljagetendorse lj
where lj.GetConfirmDate=i_strdate
and lj.getmoney<>0
and lj.contno = i_contno
));

commit;

exception
when others then
p_insert_zbx_v3_logs(i_strdate, 'updatetemp_v3'||SQLERRM || '|' ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||'');
end p_insert_businobycont_v3;

PROCEDURE p_insert_busino_v3(i_strdate in date) as
begin
insert into updatetemp_v3
select *
from (select '000103' ||
SUBSTR(to_char(i_strdate, 'yyyy-mm-dd'), 1, 4) ||
SUBSTR(to_char(i_strdate, 'yyyy-mm-dd'), 6, 2) ||
substr(to_char(i_strdate, 'yyyy-mm-dd'), 9, 2) ||
lpad(rownum, 10, '0') as busino,
OTHERNO,
CONTNO,
OTHERNOTYPE,
nvl(TRANSAMNT, '0.00'),
TRANSDATE ,
standbystring1,
standbystring2
from ( --02：承保
select contno as otherno,
contno as contno,
'02' as othernotype,
prem as transamnt,
signdate as transdate,
'' standbystring1,
'' standbystring2
from lccont
where signdate = i_strdate
and appflag <>'0'
union all
--03续期
select incomeno as otherno,
incomeno as contno,
'03' as othernotype,
sumactupaymoney as transamnt,
enteraccdate as transdate,
'' standbystring1,
'' standbystring2
from ljapay
where (paytypeflag != '1' or paytypeflag is null)
and othernotype in ('2', '3')
and enteraccdate = i_strdate
union all
--04:续保
select incomeno as otherno,
incomeno as contno,
'04' as othernotype,
sumactupaymoney as transamnt,
enteraccdate as transdate,
'' standbystring1,
'' standbystring2
from ljapay
where paytypeflag = '1'
and othernotype in ('2', '3')
and enteraccdate = i_strdate
union all
--05:满期
select otherno as otherno,
contno as contno,
'05' as othernotype,
money as transamnt,
paydate as transdate,
'' standbystring1,
'' standbystring2
from lcinsureacctrace
where insuaccno = '000009'
and paydate = i_strdate
union all
--06:保单终止；07：失效
select distinct contno as otherno,
contno as contno,
(case
when statetype = 'Terminate' then
'06'
when statetype = 'Available' then
'07'
end) as othernotype,
0 as transamnt,
startdate as transdate,
'' standbystring1,
'' standbystring2
from lccontstate
where state = '1'
and statetype in ('Terminate','Available')
and startdate = i_strdate
union all
--11:自动垫交
select distinct contno as otherno,
contno as contno,
'11' as othernotype,
0 as transamnt,
modifydate as transdate,
'' standbystring1,
'' standbystring2
from lccontstate
where statetype = 'PayPrem'
and state = '1'
and enddate is null
and modifydate = i_strdate
union all
--15:理赔
select aa.clmno as otherno,
aa.contno as contno,
'15' as othernotype,
-round(sum(aa.pay), 2) as transamnt,
bb.ExamDate as transdate,
'' standbystring1,
'' standbystring2
from llbalance aa, LLCLAIMUWMAIN bb
where aa.clmno = bb.caseno
and bb.ExamDate = i_strdate
--赔案存在，赔案中涉及的保单再llclaimpolicy不存在
and exists(select 1 from llclaimpolicy ll where aa.clmno =ll.clmno and aa.contno = ll.contno)
group by aa.clmno, aa.contno, bb.ExamDate
union all
--16:生存金领取
select actugetno as otherno,
contno as contno,
'16' as othernotype,
GETMONEY as transamnt,
GETDATE as transdate,
'' standbystring1,
'' standbystring2
from LJAGetDraw
where GETDATE = i_strdate
and feefinatype='YF'
union all
select lc.contno as otherno ,
lc.contno as contno ,
'16' as othernotype,
lc.money as transamnt,
lc.makedate as transdate,
lc.serialno standbystring1,
'' standbystring2
from lcinsureacctrace lc
where lc.moneytype = 'LVTI'
and lc.insuaccno = '000006'
and lc.makedate = i_strdate
--16a:红利转万能中间表 20200528 chenzhuo start
union all
select lc.contno as otherno ,
lc.contno as contno ,
'16a' as othernotype,
lc.money as transamnt,
lc.makedate as transdate,
lc.serialno standbystring1,
'' standbystring2
from lcinsureacctrace lc
where lc.moneytype = 'HTW'
and lc.insuaccno = '000006'
and lc.makedate = i_strdate
--16a:红利转万能中间表 20200528 chenzhuo end
union all
--17:分红
select contno as otherno,
contno as contno,
'17' as othernotype,
bonusmoney as transamnt,
makedate as transdate,
'' standbystring1,
'' standbystring2
from lobonuspol
where makedate = i_strdate
union all--2019-7-29保额分红
select contno as otherno,
contno as contno,
'17' as othernotype,
bonusamnt as transamnt,
makedate as transdate,
'' standbystring1,
'' standbystring2
from loengbonuspol
where makedate = i_strdate
union all
--保全：18-38
select item.edoracceptno as otherno,
item.contno as contno,
(case
when item.edortype = 'RE' then
'08' --复效
when item.edortype = 'DP' then
'09' --缓缴
when item.edortype = 'TS' then
'10' --复缴
when item.edortype = 'TR' then
'12' --还垫（保单自垫清偿）
when item.edortype = 'LN' then
'13' --借款（保单贷款）
when item.edortype = 'RF' then
'14' --还款（保单贷款清偿）
when item.edortype = 'AM' then
'18' --被保人资料变更
when item.edortype = 'IO' then
'19' --被保人职业变更
when (item.edortype = 'IC' or item.edortype = 'CM'or item.edortype = 'PC') then
'20' --客户资料变更
when item.edortype = 'AE' then
'21' --投保人变更
when item.edortype = 'BC' then
'22' --受益人变更
when (item.edortype = 'AA' or
item.edortype = 'WP' or
item.edortype = 'IP') then
'24' --增加保额 (AA:附加险加保,WP:万能追加保费,IP:投连追加保费)
when (item.edortype = 'PT' or
item.edortype = 'XS' or
item.edortype = 'RT') then
'25' --减少保额(PT:减保,XS:协议减保,RT:犹豫期减保)
when item.edortype = 'PP' and
item.standbyflag1 = '1' then
'26' --保单质押
when item.edortype = 'PP' and
item.standbyflag1 = '0' then
'27' --保单质押解除
when item.edortype = 'NS' or edortype = 'NR' then
'28' --新增附加险(NR:被保人增加险种，NS:新增附加险)
when item.edortype = 'PR' then
'29' --保单迁移
when item.edortype = 'PU' then
'30' --减额缴清
when item.edortype = 'CT' then
'31' --退保
when item.edortype = 'WT' then
'32' --犹豫期退保
when item.edortype = 'PM' then
'33' --缴费方式变更 (缴费频率变更PM)
when item.edortype = 'GC' then
'34' --生存金领取方式变更（生存给付转账授权变更GC）
/*when item.edortype = '' then
'35' --险种变更 */
when item.edortype = 'TI' then
'36' --账户转换 (投连账户转换)
when item.edortype in ('OP','AR') then
'37' --部分领取 (万能部分领取)
when item.edortype in ('UP', 'WP') then
'38' --追加保费 (万能追加保费)
when item.edortype = 'FM' then
'61' --缴费期限变更
when item.edortype = 'RE' then
'67' --保单复效
when item.edortype in ('DB', 'LG', 'OP') then
'69' --账户领取
when item.edortype = 'GB' then
'70' --领取年龄变更
else
'99'
end) as othernotype,
item.getmoney as transamnt,
item.edorvalidate as transdate,
'' standbystring1,
'' standbystring2
from lpedoritem item
where item.edorstate = '0'
and item.edorvalidate = i_strdate
--39:理赔回退
union all
select a.backno as otherno,
b.contno as contno,
'39' as othernotype,
b.realpay as transamnt,
a.backdate as transdate,
'' standbystring1,
'' standbystring2
from llcaseback a, llclaimpolicy b
where a.clmno = b.clmno
and a.backdate = i_strdate
union all
-- 40：万能险结息和月结处理
select polno as otherno,
contno as contno,
'40' as othernotype,
0 as transamnt,
paydate as transdate,
'' standbystring1,
'' standbystring2
from lcinsureacctrace
where insuaccno = '000006'
and state = '0'
and moneytype in
('CS', 'FX', 'GL', 'HTW', 'LX', 'LVTI')
and paydate = i_strdate
group by contno, polno, paydate
-- 41：投连计价和月终处理
union all
select otherno as otherno,
contno as contno,
'41' as othernotype,
round(sum(money),'2') as transamnt,
modifydate as transdate,
riskcode standbystring1,
'' standbystring2
from lcinsureacctrace_invest
where insuaccno <> '000000'
and state = '1'
and moneytype in
( 'FX', 'GL', 'BF', 'TI', 'IN','TBFY','EFLQ','WF','JJ','IV','TB','WT','CL')
and modifydate = i_strdate
group by contno, otherno, modifydate,riskcode
union all
--47:产品报送
select '000000' as otherno,
'' as contno,
'47' as othernotype,
0 as transamnt,
i_strdate as transdate,
'' standbystring1,
'' standbystring2
from dual
where 1 = 1
union all
--57：拒保 新契约
select contno as otherno,
contno as contno,
'57' as othernotype,
0 as transamnt,
modifydate as transdate,
'' standbystring1,
'' standbystring2
from lccuwmaster
where modifydate = i_strdate
and passflag = '1'
union all
--57：拒保 保全
select lpd.edoracceptno as otherno,
lpu.contno as contno,
'57' as othernotype,
0 as transamnt,
lpu.modifydate as transdate,
'' standbystring1,
'' standbystring2
from lpcuwmaster lpu, lpedoritem lpd
where lpu.modifydate = i_strdate
and lpu.passflag = '1'
and lpu.contno = lpd.contno
and lpu.edorno = lpd.edorno
and lpu.edortype = lpd.edortype
union all
--57：拒保 理赔
select caseno as otherno,
contno as contno,
'57' as othernotype,
0 as transamnt,
modifydate as transdate,
'' standbystring1,
'' standbystring2
from llcuwmaster
where modifydate = i_strdate
and passflag = '1'
union all
--57：拒保 续期
select contno as otherno,
contno as contno,
'57' as othernotype,
0 as transamnt,
modifydate as transdate,
'' standbystring1,
'' standbystring2
from rnewinduwmaster
where modifydate = i_strdate
and passflag = '1'
union all
--58：延期 新契约
select contno as otherno,
contno as contno,
'58' as othernotype,
0 as transamnt,
modifydate as transdate,
'' standbystring1,
'' standbystring2
from lccuwmaster
where modifydate = i_strdate
and passflag = '2'
union all
--58：延期 保全
select lpd.edoracceptno as otherno,
lpu.contno as contno,
'58' as othernotype,
0 as transamnt,
lpu.modifydate as transdate,
'' standbystring1,
'' standbystring2
from lpcuwmaster lpu, lpedoritem lpd
where lpu.modifydate = i_strdate
and lpu.passflag = '2'
and lpu.contno = lpd.contno
and lpu.edorno = lpd.edorno
and lpu.edortype = lpd.edortype
union all
--58：延期 续期
select contno as otherno,
contno as contno,
'57' as othernotype,
0 as transamnt,
modifydate as transdate,
'' standbystring1,
'' standbystring2
from rnewinduwmaster
where modifydate = i_strdate
and passflag = '2'
union all
--59：回执签收
select contno as otherno,
contno as contno,
'59' as othernotype,
0 as transamnt,
customgetpoldate as transdate,
'' standbystring1,
'' standbystring2
from lccont
where customgetpoldate = i_strdate
--62:保全二核
union all
select DISTINCT lpe.edoracceptno as otherno,
lpu.contno as contno,
'62' as othernotype,
0 as transamnt,
lpu.modifydate as transdate,
'' standbystring1,
'' standbystring2
from lpedoritem lpe,lpuwmaster lpu
where lpe.edorno = lpu.edorno
and lpe.contno = lpu.contno
and lpu.passflag <> '1'
and lpu.modifydate = i_strdate
--63：理赔二核
union all
select llc.clmno as otherno,
llu.contno as contno,
'63' as othernotype,
0 as transamnt,
llu.modifydate as transdate,
'' standbystring1,
'' standbystring2
from llclaimpolicy llc,lluwmaster llu
where llc.clmno = llu.caseno
and llu.passflag <> '1'
and llu.modifydate = i_strdate
union all
--66：保单失效
select polno as otherno,
contno as contno,
'66' as othernotype,
0 as transamnt,
i_strdate as transdate,
'' standbystring1,
'' standbystring2
from lcpol
where paytodate < payenddate
and paytodate + 60 = i_strdate
and appflag = '1'
union all
--99:佣金信息
select to_char(item_id) as otherno,
policy_code as contno,
'99' as othernotype,
-commision as transamnt,
i_strdate as transdate,
'' standbystring1,
'' standbystring2
from MV_GX_COMMISION_INFO
where to_char(update_time,'yyyy-mm-dd') = to_char(I_STRDATE,'yyyy-mm-dd')

--76 保全补退费
union all
select
lj.polno as otherno ,
lj.contno as contno ,
'76' as othernotype ,
lj.getmoney as transamnt ,
lj.GetConfirmDate as transdate,
lj.actugetno as standbystring1,
lj.subfeeoperationtype as standbystring2
from ljagetendorse lj
where lj.GetConfirmDate=i_strdate
and lj.getmoney<>0
));

commit;

exception
when others then
p_insert_zbx_v3_logs(i_strdate, 'updatetemp_v3'||SQLERRM || '|' ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||'');
end p_insert_busino_v3;
-- -1
procedure p_delete_zbxTempv3Table as
begin
EXECUTE IMMEDIATE 'truncate table updatetemp_v3 ';
EXECUTE IMMEDIATE 'truncate table temp_lcpoltransaction_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_lccont_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_lcprodinsurela_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_lcproduct_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_LCLiability_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_lcbnf_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_lcinsured_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_lcinsureacc_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_llclaimpolicy_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_llbnf_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_LLClaimDetail_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_LLClaimInfo_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_LLClaimantInfo_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_LLReceipt_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_LLCostItem_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_LLDisease_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_LLOperation_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_lasales_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_laagent_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_LACommission_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_ljtempfee_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_ljdivdistrib_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_ljagetlivbene_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_ljapay_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_lmproduct_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_ljaget_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_LAllUnderWriting_v3' ;
EXECUTE IMMEDIATE 'truncate table temp_LCBlacklist_v3' ;
--3期新增表完成删除
EXECUTE IMMEDIATE 'truncate table TEMP_LCInsureAccTrace_v3';
EXECUTE IMMEDIATE 'truncate table TEMP_LJAGetEndorse_v3';
EXECUTE IMMEDIATE 'truncate table TEMP_LMLiability_v3';
EXECUTE IMMEDIATE 'truncate table TEMP_LRAccount_v3';
EXECUTE IMMEDIATE 'truncate table TEMP_LRCont_v3';
EXECUTE IMMEDIATE 'truncate table TEMP_LREdor_v3';
EXECUTE IMMEDIATE 'truncate table TEMP_LRInsureCont_v3';
EXECUTE IMMEDIATE 'truncate table TEMP_LRProduct_v3';
EXECUTE IMMEDIATE 'truncate table Temp_LCCustomerReturnCall_v3' ;
EXECUTE IMMEDIATE 'truncate table Temp_LJInvoice_v3';
EXECUTE IMMEDIATE 'truncate table Temp_LJInvoiceRelation_v3' ;
EXECUTE IMMEDIATE 'truncate table Temp_LJTax_v3' ;
EXECUTE IMMEDIATE 'truncate table Temp_LJLoanRepayment_v3';
EXECUTE IMMEDIATE 'truncate table Temp_LJLoan_v3' ;
EXECUTE IMMEDIATE 'truncate table Temp_LRBenefit_v3';
EXECUTE IMMEDIATE 'truncate table Temp_LRClaim_v3';

COMMIT;
END p_delete_zbxTempv3Table;

--1
PROCEDURE p_insert_lcpoltransaction_v3(i_strdate in date) AS
begin
insert into temp_lcpoltransaction_v3
select distinct a.busino as BUSINO,
-- '0000000' as BatchNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
a.contno as PolicyNo,
'01' as GPFlag,
a.othernotype as BussType,
'100' as TransTypeCorrBussType,
a.transamnt as TransAmnt,
a.TransDate as TransDate,
b.newbankcode as BankCode,
(select bankname from ldbankinfo where bankcode = b.newbankcode) as BankName,
b.newbankaccno as BankAccNo,
b.newaccname as AccName,
changecode_v2(b.appntidtype, 'CertType', '') as CertType,
b.appntidno as CertNo,
'' as ClaimNo,
'' as EndorAcceptNo,
'' as EndorsementNo,
null as EndorsementApplicationDate,
'' as ConsumerInitiatedFlag,
'' as PreserveAppCode,
'' as DataField1,
'' as DataField2,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from Updatetemp_v3 a, lccont b
where a.otherno = b.contno
and a.contno = b.contno
and a.TRANSDATE = i_strdate
and a.othernotype not in ('03', '04','05','16','72','73','76','74','75','16a')
union
select a.busino as BUSINO,
-- '0000000' as BatchNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
a.contno as PolicyNo,
'01' as GPFlag,
a.othernotype as BussType,
'500' as TransTypeCorrBussType,
a.transamnt as TransAmnt,
a.TransDate as TransDate,
d.bankcode as BankCode,
(select bankname from ldbankinfo where bankcode = d.bankcode) as BankName,
d.bankaccno as BankAccNo,
d.accname as AccName,
changecode_v2((select appnttype
from lcappnt
where contno = c.contno),
'CertType',
'') as CertType,
(select appntno from lcappnt where contno = c.contno) as CertNo,
'' as ClaimNo,
c.edoracceptno as EndorAcceptNo,
c.edorno as EndorsementNo,
d.edorappdate as EndorsementApplicationDate,
'1' as ConsumerInitiatedFlag,
decode(d.apptype, '1', '01', '02') as PreserveAppCode,
'' as DataField1,
'' as DataField2,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from Updatetemp_v3 a, lpedoritem c, lpedorapp d
where a.otherno = c.edoracceptno
and c.edoracceptno = d.edoracceptno
and a.othernotype not in ('41')
and a.TRANSDATE = i_strdate
union
select distinct a.busino as BUSINO,
-- '0000000' as BatchNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
a.contno as PolicyNo,
'01' as GPFlag,
a.othernotype as BussType,
'200' as TransTypeCorrBussType,
a.transamnt as TransAmnt,
a.TransDate as TransDate,
f.bankcode as BankCode,
(select bankname from ldbankinfo where bankcode = f.bankcode) as BankName,
f.bankaccno as BankAccNo,
trim(f.accname) as AccName,
changecode_v2(f.idtype,
'CertType',
'') as CertType,
f.idno as CertNo,
a.otherno as ClaimNo,
'' as EndorAcceptNo,
'' as EndorsementNo,
null as EndorsementApplicationDate,
'' as ConsumerInitiatedFlag,
'' as PreserveAppCode,
'' as DataField1,
'' as DataField2,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from Updatetemp_v3 a left join
(select clmno,contno,bankcode,bankaccno,accname,idtype,idno from llbnf where bnfno = '1') f
on a.otherno = f.clmno
and a.contno = f.contno
where exists (select 1 from llregister where rgtno = a.otherno)
and a.othernotype in ('15','39','57','63')
union
select a.busino as BUSINO,
-- '0000000' as BatchNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
a.contno as PolicyNo,
'01' as GPFlag,
a.othernotype as BussType,
'999' as TransTypeCorrBussType,
a.transamnt as TransAmnt,
a.TransDate as TransDate,
b.newbankcode as BankCode,
(select bankname from ldbankinfo where bankcode = b.newbankcode) as BankName,
b.newbankaccno as BankAccNo,
b.newaccname as AccName,
changecode_v2(b.appntidtype, 'CertType', '') as CertType,
b.appntidno as CertNo,
'' as ClaimNo,
'' as EndorAcceptNo,
'' as EndorsementNo,
null as EndorsementApplicationDate,
'' as ConsumerInitiatedFlag,
'' as PreserveAppCode,
'' as DataField1,
'' as DataField2,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from Updatetemp_v3 a, lccont b
where a.contno = b.contno
and a.TRANSDATE = i_strdate
and a.othernotype in ('05','16','40','66','41','99')
and not exists (select 1 from lpedorapp lp where a.otherno= lp.edoracceptno and a.othernotype = '99')
--红利转万能 20200528 chenzhuo start
union
select a.busino as BUSINO,
-- '0000000' as BatchNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
a.contno as PolicyNo,
'01' as GPFlag,
'16' as BussType,
'999' as TransTypeCorrBussType,
a.transamnt as TransAmnt,
a.TransDate as TransDate,
b.newbankcode as BankCode,
(select bankname from ldbankinfo where bankcode = b.newbankcode) as BankName,
b.newbankaccno as BankAccNo,
b.newaccname as AccName,
changecode_v2(b.appntidtype, 'CertType', '') as CertType,
b.appntidno as CertNo,
'' as ClaimNo,
'' as EndorAcceptNo,
'' as EndorsementNo,
null as EndorsementApplicationDate,
'' as ConsumerInitiatedFlag,
'' as PreserveAppCode,
'' as DataField1,
'' as DataField2,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from Updatetemp_v3 a, lccont b
where a.contno = b.contno
and a.TRANSDATE = i_strdate
and a.othernotype in ('16a')
--红利转万能 20200528 chenzhuo end
union
select a.busino as BUSINO,
-- '0000000' as BatchNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
a.contno as PolicyNo,
'01' as GPFlag,
a.othernotype as BussType,
decode(a.othernotype,'74','300','75','300','999') as TransTypeCorrBussType,
a.transamnt as TransAmnt,
a.TransDate as TransDate,
b.bankcode as BankCode,
(select bankname from ldbankinfo where bankcode = b.bankcode) as BankName,
b.bankaccno as BankAccNo,
b.accname as AccName,
changecode_v2(b.appntidtype, 'CertType', '') as CertType,
b.appntidno as CertNo,
'' as ClaimNo,
'' as EndorAcceptNo,
'' as EndorsementNo,
null as EndorsementApplicationDate,
'' as ConsumerInitiatedFlag,
'' as PreserveAppCode,
'' as DataField1,
'' as DataField2,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from Updatetemp_v3 a, lccont b
where a.contno = b.contno
and a.TRANSDATE = i_strdate
and a.othernotype in ('03','04','72','73','76','74','75')
union all
select a.busino as BUSINO,
-- '0000000' as BatchNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
'' as PolicyNo,
'01' as GPFlag,
a.othernotype as BussType,
'999' as TransTypeCorrBussType,
a.transamnt as TransAmnt,
a.TransDate as TransDate,
'' as BankCode,
'' as BankName,
'' as BankAccNo,
'' as AccName,
'' as CertType,
'' as CertNo,
'' as ClaimNo,
'' as EndorAcceptNo,
'' as EndorsementNo,
null as EndorsementApplicationDate,
'' as ConsumerInitiatedFlag,
'' as PreserveAppCode,
'' as DataField1,
'' as DataField2,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from Updatetemp_v3 a
where a.othernotype ='71';
commit;
-- dbms_output.put_line('!!!!!!!!!!!!!!!!!');

insert into temp_lcpoltransaction_v3 (BUSINO, COMPANYCODE, GRPPOLICYNO, POLICYNO, GPFLAG, BUSSTYPE, TRANSTYPECORRBUSSTYPE, TRANSAMNT, TRANSDATE, BANKCODE, BANKNAME, BANKACCNO, ACCNAME, CERTTYPE, CERTNO, CLAIMNO, ENDORACCEPTNO, ENDORSEMENTNO, ENDORSEMENTAPPLICATIONDATE, CONSUMERINITIATEDFLAG, PRESERVEAPPCODE, DATAFIELD1, DATAFIELD2, PUSHDATE)
values ('000000', '000103', null, null, '01', '47', '999', 0.00, i_strdate, null, null, null, null, null, null, null, null, null, null, null, null, null, null, to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'));
commit;

exception
when others then
p_insert_zbx_v3_logs(i_strdate, 'temp_lcpoltransaction_v3'||SQLERRM || '|' ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||'');
end p_insert_lcpoltransaction_v3;

--2
PROCEDURE p_insert_lccont_v3(i_strdate in date) AS
cursor cur_temp_lccont is
SELECT temp.busino as TransactionNo,
'000103' as COMPANYCODE,
'' as GRPPOLICYNO,
cont.ContNo as POLICYNO,
cont.PrtNo as PRTNO,
'01' as GPFLAG,
nvl(changecode_v2(cont.FamilyType, 'FamilyPolType', ''), 'N') as FAMILYPOLTYPE,
nvl(changecode_v2(substr(cont.managecom,0,4),'MANAGE',''),'000103') as MANAGECOM,
nvl((select ld.targetcodename from ldcodeMapping_v2 ld where ld.basiccode = substr(cont.managecom,0,4) and ld.codetype='MANAGE'),'恒大人寿保险有限公司') as MANAGECOMNAME,
nvl(changecode_v2(substr(cont.ManageCom, 0, 4),
'CircPopedomCode',''),
'N') as CircPopedomCode,
changecode_v2(cont.ManageCom,'ManageToProvince','') as EffPrefectureCode,
nvl(changecode_v2(cont.SaleChnl, 'SaleChnl', ''),'999') as DISTRIBCHNL,
(case
when cont.salechnl = '06' then nvl((select ta.supervision_code from icms.T_AGENCY ta where ta.agency_code = cont.agentcom),
cont.agentcom )
when cont.salechnl = '03' then nvl((select ta.supervision_code from icms.T_bank ta where ta.bank_code = cont.agentcom),
cont.agentcom )
else cont.agentcom
end) as AgencyCode,
(case
when cont.salechnl = '06' then nvl((select ta.supervision_name from icms.T_AGENCY ta where ta.agency_code = cont.agentcom),
la.name )
when cont.salechnl = '03' then nvl((select ta.supervision_name from icms.T_bank ta where ta.bank_code = cont.agentcom),
( select banknetname from ldbanknet where banknetid = substr(cont.agentbankcode,0,4) and rownum =1))
when cont.salechnl ='02' then (select name from ldcom where comcode = cont.managecom) when cont.salechnl ='05' then (select name from ldcom where comcode = cont.managecom)
else la.name

end) as AgencyName,
/* cont.AgentCom as AGENCYCODE,
(case cont.salechnl when '03' then (select banknetname from ldbanknet where banknetid = substr(cont.agentbankcode,0,4) and rownum =1)
when '02' then (select name from ldcom where comcode = cont.managecom) when '05' then (select name from ldcom where comcode = cont.managecom)
when '06' then (select lc.name from lacom lc where lc.agentcom = cont.agentcom) else la.name end) as AGENCYNAME,*/
cont.AppntNo as APPNTNO,
'' as CUSTOMERNO,
b.appntname as APPNTNAME,
changecode_v2(cont.AppntSex, 'sex', '') as APPNTSEX,
cont.AppntBirthday as APPNTBIRTHDATE,
( case when length(cont.appntidno)=18 then '111' else changecode_v2(cont.AppntIDType, 'CertType', '') end) as APPNTCERTTYPE,
cont.AppntIDNo as APPNTCERTNO,
nvl((case when bb.homeprovince in (select addresscode from ldaddressmapping_zbx) then bb.homeprovince
else '' end),'000000') as PROVINCE,
nvl((case when bb.homecity in (select addresscode from ldaddressmapping_zbx) then bb.homecity
else '' end),'000000') as CITY,
nvl((case when bb.homecounty in (select addresscode from ldaddressmapping_zbx) then bb.homecounty
else '' end),'000000') as COUNTY,
C.HOMEADDRESS as STREET,
(case when regexp_like(c.mobile,'[^0-9]') then '' else c.mobile end) as APPNTMP,
C.PHONE AS APPNTFP,
(case when exists (select 1 from dual
where instr(c.Email, '@') > instr(c.Email, '.')
or instr(c.Email, '@') = 0
or instr(c.Email, '.') = 0)
then '' else c.email end) AS EMAIL,
changecode_v2(to_char(cont.PayIntv), 'PayMode', '') as PAYMODE,
changecode_v2(cont.PayMode, 'BenefitPayMthd', '') as PAYWAY,
nvl(cont.SignDate,date'1900-01-01') as SIGNDATE,
nvl(changecode_v2(cont.Currency, 'Currency', ''), '156') as CURRENCY,
nvl(cont.Mult, '0') as COPIES,
nvl((select sum(lc.prem) from lcpol lc where lc.contno = cont.contno and lc.appflag not in ('2','9')),0) as PREMIUM,
nvl((select sum(amnt) from lcpol where contno = cont.contno and appflag not in ('2','9')),0) as SUMINSURED,
nvl((select sum(ad.polcash) from lcpolcash ad where ad.contno = cont.contno),0) as CASHVALUE,
cont.Dif as POLBALANCE,
nvl((select sum(sumprem) from lcpol where contno = cont.contno and appflag not in ('2','9') ),0.0) as AccumPremium,
(select nvl(sum(a.leavemoney), 0)
from loloan a
where a.payoffflag = '0'
and a.loantype = '0'
and a.contno = cont.contNo) as ACCUMLOANMONEY,
(select nvl(sum(a.leavemoney), 0)
from loloan a
where a.loantype = '1'
and a.contno = cont.ContNo
and a.leavemoney > 0
and a.payoffflag = '0') as APLMONEY,
decode(cont.uwflag,
'1',
nvl(cont.paytodate,date '1900-01-01'),
'2',
nvl(cont.paytodate,date '1900-01-01'),
'a',--撤单
nvl(cont.paytodate,date'1900-01-01'),
nvl(cont.PaytoDate,nvl(cont.signdate,cont.cvalidate))) as CURPAIDTODATE,
decode(cont.uwflag,
'1',
nvl(cont.FirstPayDate,date '1900-01-01'),
'2',
nvl(cont.FirstPayDate,date '1900-01-01'),
'a', --撤单
nvl(cont.FirstPayDate,date'1900-01-01'),
nvl(cont.FirstPayDate,nvl(cont.signdate,cont.cvalidate))) as PAYBEGINDATE,
nvl(cont.CValiDate,date'1900-01-01') as EFFDATE,
(case when cont.uwdate is not null and cont.uwdate > cont.signdate
then cont.signdate else nvl(cont.UWDate, cont.signdate) end)
as UWDATE,--核保完成日期必须小于或等于签单日期
(select aa.autouwflag from lccuwmaster aa
where aa.contno = cont.contno) as UWTYPE,---'1' 自核 2 人核
to_date(to_char(cont.PolApplyDate,'yyyy-mm-dd'),'yyyy-mm-dd') as POLAPPLYDATE,
(case when cont.customgetpoldate is null then '0' else '1' end) as PolSignFlag,
cont.CustomGetPolDate as CUSTOMGETPOLDATE,
changecode_v2(cont.appflag, 'contstate', '') as POLSTATUS,
changecode_v2(cont.newbankcode, 'BankCode', '') as BANKCODE,
(SELECT ff.bankname FROM ldbankinfo ff WHERE ff.bankcode = cont.newbankcode and rownum = 1) as BANKNAME,
cont.newbankaccno as BANKACCNO,
cont.newaccname as ACCNAME,
nvl((select 1 from lccontprint where contno = cont.contno and rownum =1),2) as POLICYTYPE,
(select max(enddate) from lcpol where polno = mainpolno and contno = cont.contno) as PolicyEndDate,
(case cont.appflag
when '4' then
nvl((select max(startdate)
from lccontstate
where contno = cont.contno
and state = '1'
and statetype = 'Terminate' ),cont.modifydate)
else
null
end) as TERMINATIONDATE,
decode(temp.othernotype, '66', transdate, '') as SUSPENDDATE,
decode(temp.othernotype, '67', transdate, '') as RECOVERDATE,
(case cont.appflag
when '4' then
(case
when exists(select 1
from lpedoritem
where contno = cont.contno
and edortype = 'CT') then
'03'
when exists(select 1
from lccontstate
where contno = cont.contno
and statereason = '04') then
'02'
when exists(select 1
from lpedoritem
where contno = cont.contno
and edortype in ('WT')) then
'09'
when exists(select 1
from lccontstate
where contno = cont.contno
and statereason = '04') then
'01'
else
'06'
end)
else
''
end) as TERMINATIONREASON,
nvl((select 1 from lccontprint where contno = cont.contno and rownum =1),0) as ContSendFlag,--保单寄送标志 电子保单
0 as AppntSalary,
--3期新增字段
changecode_v2(b.occupationcode,'OccupationType','') as AppntOccupationType,
changecode_v2(b.nativeplace,'Country','') as AppntCountry,
decode(b.cityorcounty,'0','1','1','2','') as AppntResidentType,--有空值 可为空
'01' as PolicySource, --个险只有新投保
'' as FormerPolicNo ,
0 as RenewalTimes,
cont.cvalidate as FirstEffectiveDate,
nvl( (select decode(state,'1','1','0')
FROM lccontstate a
WHERE contno = cont.contno
and statetype = 'BankLoan'
AND enddate is null
and rownum = 1
),'0') as PledgeFlag,
'' as InterComName , --保单表新增字段？
'' as InterComSocialCreditCode,
'0'as ViaticalSettlementsFlag ,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
FROM updatetemp_v3 temp, LCCONT cont,lcappnt b,ldcom ld,lacom la,lcaddress bb,LCADDRESS C
WHERE cont.ContNo = temp.contNo
and cont.contno = b.contno
and cont.managecom = ld.comcode
and cont.agentcom = la.agentcom(+)
and bb.customerno = b.appntno
and bb.addressno = b.addressno
AND C.CUSTOMERNO = B.APPNTNO
AND B.ADDRESSNO = C.ADDRESSNO
and temp.TRANSDATE = i_strdate;
v_count number := 1;

begin
for v_temp_lccont in cur_temp_lccont loop
v_count := v_count + 1;
insert into temp_lccont_v3
(BUSINO,
COMPANYCODE,
GRPPOLICYNO,
POLICYNO,
PRTNO,
GPFLAG,
FAMILYPOLTYPE,
MANAGECOM,
MANAGECOMNAME,
CIRCPOPEDOMCODE,
EFFPREFECTURECODE,
DISTRIBCHNL,
AGENCYCODE,
AGENCYNAME,
APPNTNO,
CUSTOMERNO,
APPNTNAME,
APPNTSEX,
APPNTBIRTHDATE,
APPNTCERTTYPE,
APPNTCERTNO,
PROVINCE,
CITY,
COUNTY,
STREET,
APPNTMP,
APPNTFP,
EMAIL,
PAYMODE,
PAYWAY,
SIGNDATE,
CURRENCY,
COPIES,
PREMIUM,
SUMINSURED,
CASHVALUE,
POLBALANCE,
ACCUMPREMIUM,
ACCUMLOANMONEY,
APLMONEY,
CURPAIDTODATE,
PAYBEGINDATE,
EFFDATE,
UWDATE,
UWTYPE,
POLAPPLYDATE,
POLSIGNFLAG,
CUSTOMGETPOLDATE,
POLSTATUS,
BANKCODE,
BANKNAME,
BANKACCNO,
ACCNAME,
POLICYTYPE,
POLICYENDDATE,
TERMINATIONDATE,
SUSPENDDATE,
RECOVERDATE,
TERMINATIONREASON,
CONTSENDFLAG,
APPNTSALARY,
AppntOccupationType,
AppntCountry,
AppntResidentType,
PolicySource,
FormerPolicNo,
RenewalTimes,
FirstEffectiveDate,
PledgeFlag,
InterComName,
InterComSocialCreditCode,
ViaticalSettlementsFlag,
PUSHDATE
)
values
(v_temp_lccont.TransactionNo,
v_temp_lccont.COMPANYCODE,
v_temp_lccont.GRPPOLICYNO,
v_temp_lccont.POLICYNO,
v_temp_lccont.PRTNO,
v_temp_lccont.GPFLAG,
v_temp_lccont.FAMILYPOLTYPE,
v_temp_lccont.MANAGECOM,
v_temp_lccont.MANAGECOMNAME,
v_temp_lccont.CIRCPOPEDOMCODE,
v_temp_lccont.EFFPREFECTURECODE,
v_temp_lccont.DISTRIBCHNL,
v_temp_lccont.AGENCYCODE,
v_temp_lccont.AGENCYNAME,
v_temp_lccont.APPNTNO,
v_temp_lccont.CUSTOMERNO,
v_temp_lccont.APPNTNAME,
v_temp_lccont.APPNTSEX,
v_temp_lccont.APPNTBIRTHDATE,
v_temp_lccont.APPNTCERTTYPE,
v_temp_lccont.APPNTCERTNO,
v_temp_lccont.PROVINCE,
v_temp_lccont.CITY,
v_temp_lccont.COUNTY,
v_temp_lccont.STREET,
v_temp_lccont.APPNTMP,
v_temp_lccont.APPNTFP,
v_temp_lccont.EMAIL,
v_temp_lccont.PAYMODE,
v_temp_lccont.PAYWAY,
v_temp_lccont.SIGNDATE,
v_temp_lccont.CURRENCY,
v_temp_lccont.COPIES,
v_temp_lccont.PREMIUM,
v_temp_lccont.SUMINSURED,
v_temp_lccont.CASHVALUE,
v_temp_lccont.POLBALANCE,
v_temp_lccont.ACCUMPREMIUM,
v_temp_lccont.ACCUMLOANMONEY,
v_temp_lccont.APLMONEY,
v_temp_lccont.CURPAIDTODATE,
v_temp_lccont.PAYBEGINDATE,
v_temp_lccont.EFFDATE,
v_temp_lccont.UWDATE,
v_temp_lccont.UWTYPE,
v_temp_lccont.POLAPPLYDATE,
v_temp_lccont.POLSIGNFLAG,
v_temp_lccont.CUSTOMGETPOLDATE,
v_temp_lccont.POLSTATUS,
v_temp_lccont.BANKCODE,
v_temp_lccont.BANKNAME,
v_temp_lccont.BANKACCNO,
v_temp_lccont.ACCNAME,
v_temp_lccont.POLICYTYPE,
v_temp_lccont.POLICYENDDATE,
v_temp_lccont.TERMINATIONDATE,
v_temp_lccont.SUSPENDDATE,
v_temp_lccont.RECOVERDATE,
v_temp_lccont.TERMINATIONREASON,
v_temp_lccont.CONTSENDFLAG,
v_temp_lccont.APPNTSALARY,
v_temp_lccont.AppntOccupationType,
v_temp_lccont.AppntCountry,
v_temp_lccont.AppntResidentType,
v_temp_lccont.PolicySource,
v_temp_lccont.FormerPolicNo,
v_temp_lccont.RenewalTimes,
v_temp_lccont.FirstEffectiveDate,
v_temp_lccont.PledgeFlag,
v_temp_lccont.InterComName,
v_temp_lccont.InterComSocialCreditCode,
v_temp_lccont.ViaticalSettlementsFlag,
v_temp_lccont.PUSHDATE
);

--分步提交
if mod(v_count, 5000) = 0 then
commit;
end if;
end loop;
commit;
-- dbms_output.put_line('!!!!!!!!!!!!!!!!!');
exception
when others then
p_insert_zbx_v3_logs(i_strdate, 'temp_lccont_v3'||SQLERRM || '|' ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||'');
end p_insert_lccont_v3;
--3
PROCEDURE p_insert_lcprodinsurela_v3(i_strdate in date) AS
begin

insert into temp_lcprodinsurela_v3
SELECT b.busino as TransactionNo,
'000103' as COMPANYCODE,
'' as GRPPOLICYNO,
'01' as GPFlag,
'' as GrpProductNo,
a.contno as POLICYNO,
a. Polno as PRODUCTNO,
a.InsuredNo as INSUREDNO,
'' as CUSTOMERNO,
nvl(changecode_v2(substr(a.managecom,0,4),'MANAGE',''),'000103') as MANAGECOM,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
FROM LCPol a, updatetemp_v3 b, lccont c
where b.contno = a.contno
and c.contno = a.contno
and b.contno = c.contno
and a.appflag <> '9' and a.appflag <>'2'
and b.TRANSDATE = i_strdate;
commit;
--异常后删除交易表不报送
exception
when others then
p_insert_zbx_v3_logs(i_strdate, 'temp_lcprodinsurela_v3'||SQLERRM || '|' ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||'');
EXECUTE IMMEDIATE 'truncate table temp_lcpoltransaction_v3' ;

commit;
-- dbms_output.put_line('!!!!!!!!!!!!!!!!!');
end p_insert_lcprodinsurela_v3;
--4
PROCEDURE p_insert_lcproduct_v3(i_strdate in date) AS
begin
insert into temp_lcproduct_v3
select tempp.busino as TransactionNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
a.ContNo as PolicyNo,
a.PolNo as ProductNo,
'01' as GPFlag,
changecode_v2(a.PolTypeFlag, 'PolTypeFlag', '') PolTypeFlag,
a.mainpolno as MainProductNo,
nvl((select changecode_v2(b.subriskflag, 'MainProductFlag', '')
from lmriskapp b
where b.riskcode = a.riskcode),
'2') MainProductFlag,
a.riskCode as ProductCode,
nvl(changecode_v2(substr(a.managecom,0,4),'MANAGE',''),'000103') as ManageCom,
a.CValiDate as EffDate,
nvl(a.FirstPayDate,a.signdate) as PayBeginDate,--首次缴费日期字段逻辑调整
(case when a.PayEndDate>a.PaytoDate then a.PayEndDate else a.PaytoDate end) as FinalPayDate,
a.PaytoDate as CurPaidToDate,
(case lmp.producttype when '14100'then (select min(getstartdate) from lcget where polno = a.polno and getdutykind is not null)else null end) as AnnStartWithdwlDate,
(case lmp.producttype when '14100'then (select max(getenddate) from lcget where polno = a.polno and getdutykind is not null) else null end) as AnnGetTodwlDate,
(case lmp.producttype when '14200'then (select min(getstartdate) from lcget where polno = a.polno and getdutykind is not null) else null end) as PenStartWithdwlDate,
(case lmp.producttype when '14200'then (select max(getenddate) from lcget where polno = a.polno and getdutykind is not null) else null end) as PenGetTodwlDate,
decode(a.insuyearflag,'Y', a.EndDate-1,'M', a.EndDate-1,'A',add_months(a.cvalidate,12*(a.insuyear-a.insuredappage))-1, a.EndDate) as InvalidDate,
changecode_v2(to_char(a.PayIntv), 'PayMode', '') as PayMode,
(case
when to_char(a.PayIntv) in ('0', '01') then
null
else
changecode_v2(to_char(a.payendyearflag), 'PayTermType', '')
end) as paytermtype,
decode(changecode_v2(to_char(a.PayIntv), 'PayMode', ''),
'01',
'0',
'06',
'-1',
'99',
'-1',
a.PayYears) as PayTerm,
(case when a.insuyearflag = 'A' and a.insuyear = '106' then 'O' else
changecode_v2(a.InsuYearFlag, 'InsurancePeriodFlag', '') end)
as InsurancePeriodFlag,
(case when a.insuyearflag = 'A' and a.insuyear = '106' then '999'
when a.insuyearflag = 'M' then to_char(to_number(a.insuyear) * 12)
else to_char(a.insuyear) end) as InsurancePeriod,
a.Mult as Copies,
a.prem as Premium,
nvl(a.sumprem,0.0) as AccumPremium,
a.Amnt as BasicSumInsured,
a.RiskAmnt as RiskAmnt,
nvl((select ad.polcash from lcpolcash ad where ad.polno = a.polno ),0) as CashValue,
nvl((select BonusMoney
from LOBonusPol
where ContNo = a.PolNo
and polno = a.polno),
0.0) as AccumDiv,
nvl((select sum(BonusInterest)
from LOBONUSPOL
where contno = a.contno
and polno = a.polno),
0.0) as AccumDivInt,
nvl((select sum(BONUSAMNT)
from LOENGBONUSPOL
where polno = a.polno
and contno = a.contno),
0.0) as AccumDivSI,
nvl((select max(aa.sgetdate) from lobonuspol aa where aa.polno = a.polno),(select max(l.bonusmakedate) from loengbonuspol l where l.polno = a.polno)) as LstDistriDate,
'0.0' as SurrenderAmnt,
nvl((select sum(GetMoney)
from ljagetdraw
where FeeOperationType = 'AG'
and FeeFinaType = 'YF'
and polno = a.polno
and riskcode = a.riskcode
and contno = a.contno),
0.0) as AccumAnnuityWD,
nvl((select GetMoney
from ljsgetdraw
where FeeOperationType = 'AG'
and FeeFinaType = 'EF'
and polno = a.polno
and riskcode = a.riskcode
and contno = a.contno),
0.0) as AccumMaturityWD,
nvl((select sum(Pay)
from ljagetclaim
where polno = a.polno
and contno = a.contno and feefinatype like '%PK%'),
0.0) as AccumClmBenefit,
(case when exists (select 1 from ljagetclaim where polno = a.polno and contno = a.contno and pay>0 and feefinatype like '%PK%' ) then '1' else '0' end) as AccumClmTimes,
'01' as InsuredType,
changecode_v2(nvl(a.UWFlag,'9'), 'UWConclusion', '') as UWConclusion,
(case when exists(SELECT 1 FROM lccontstate WHERE contno = a.contno and polno = a.polno
and statetype = 'Available' and state = '1' and enddate IS NULL) then '02'
else nvl(changecode_v2(a.appflag, 'contstate', ''), '99') end) as Status,
--
'0' as ReInsuranceFlag,
'0' as CoInsuranceFlag,
(select lm.specificbusiness from lmproduct lm where lm.riskcode = a.riskcode) as SpecificBusiness,
(select lm.specificbusinesscode from lmproduct lm where lm.riskcode = a.riskcode) as SpecificBusinessCode,
--3期新增字段
(select max(enddate) from lccontstate where contno = a.contno and statetype='Available' and polno = a.polno and enddate is not null ) as ProductSuspendDate,
(select max(startdate) from lccontstate where contno = a.contno and statetype='Available' and polno = a.polno and enddate is null
and exists (select 1 from lccontstate where contno = a.contno and statetype='Available' and polno = a.polno and enddate is not null)
) as ProductRecoverDate,--效力恢复日期
case a.appflag when '4' then nvl((select max(startdate) from lccontstate lc where lc.contno=a.contno and lc.polno=a.polno and lc.statetype='Terminate' and lc.state='1'),a.modifydate) else null end as TerminationDate,
0 as CoInsurancePercentage,
nvl((select abs(lo.bonusmoney) from lobonuspol lo where lo.polno = a.polno and lo.fiscalyear = (select max(fiscalyear) from lobonuspol where polno = a.polno)),0) as CurrentDivAmnt,
nvl((select standprem from lcprem where contno = a.contno and polno = a.polno and state in ('1','2') and payplantype in ('02','04') and rownum=1),0) as ProfessionalFee,
0 as SubStandardFee ,--直接加保费上,没字段专门存次标准体加费金额
0 as EMRate,--？EM值没有
decode((select risktype7 from lmriskapp where riskcode = a.riskcode),'0','0','1') as WaiverFlag,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from LCPol a, updatetemp_v3 tempp,lmproduct lmp
where a.contno = tempp.contno and a.riskcode = lmp.riskcode(+) and a.appflag <> '9' and a.appflag <>'2'
and tempp.TRANSDATE = i_strdate;
commit;
--异常后删除交易表不报送
exception
when others then
p_insert_zbx_v3_logs(i_strdate, 'temp_lcproduct_v3'||SQLERRM || '|' ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||'');
EXECUTE IMMEDIATE 'truncate table temp_lcpoltransaction_v3' ;
commit;
-- dbms_output.put_line('!!!!!!!!!!!!!!!!!');
end p_insert_lcproduct_v3;
--5
PROCEDURE p_insert_LCLiability_v3(i_strdate in date) AS
begin
insert into temp_lcliability_v3
select temp.busino as TransactionNo,
'000103' as COMPANYCODE,
'' as GRPPOLICYNO,
lp.ContNo as POLICYNO,
lp.PrtNo as ApplicationFormNo,
lp.polno as ProductNo,
'01' as GPFLAG,
lp.riskcode as ProductCode,
lb.LiabilityCode as LiabilityCode,
lb.LiabilityName as LiabilityName,
lb.classification as Classification,
'0' as InitialPremium,
lp.prem as CurrentPremium,
nvl((select sum(sumduepaymoney) from ljapayperson where polno = lp.polno),0.0) as AccumPremium,
'0' as InitialAmnt,
lp.amnt as EffectiveAmnt,
'0' as RiskAmnt,
decode(lm.risktype8,'08','1','0') as AllowanceType,
decode(lm.risktype8,'08',lp.amnt,'0') as DailyAllowanceAmnt,
nvl(lg.getstartdate,lp.cvalidate) as EffDate,
(case lp.insuyearflag when 'Y' then nvl(lg.getenddate-1,lp.enddate-1) when 'M' then nvl(lg.getenddate-1,lp.enddate-1)
else nvl(lg.getenddate-1,lp.enddate-1) end ) as InvalidDate,
(case lg.getendstate when '0' then '01' when '1' then '03' else '99' end) as Status,
'0' as WaitingPeriod,
'000000' as DeductibleType,---填默认值“000000”
'0' as Deductible,
'0' as ClaimRatio,
nvl(changecode_v2(substr(lp.managecom,0,4),'MANAGE',''),'000103') as ManageCom,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from updatetemp_v3 temp, lcget lg,lmliability lb,lcpol lp,lmriskapp lm
WHERE temp.contno = lg.contNo
and lp.ContNo = lg.contNo
and lg.polno = lp.polno
and lp.riskcode = lm.riskcode
and lg.getdutycode = lb.liabilitycode
and lp.riskcode = lb.riskcode
and temp.TRANSDATE = i_strdate;
commit;
--异常后删除交易表不报送
exception
when others then
p_insert_zbx_v3_logs(i_strdate, 'temp_lcliability_v3'||SQLERRM || '|' ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||'');
EXECUTE IMMEDIATE 'truncate table temp_lcpoltransaction_v3' ;
commit;
-- dbms_output.put_line('!!!!!!!!!!!!!!!!!');
end p_insert_LCLiability_v3;
--6
PROCEDURE p_insert_lcbnf_v3(i_strdate in date) AS
begin
insert into temp_lcbnf_v3
SELECT d.busino as TransactionNo,
'000103' as COMPANYCODE,
'' as GrpPolicyNo,
'01' as GPFlag,
a.ContNo as POLICYNO,
b.PolNo as PRODUCTNO,
a.InsuredNo as INSUREDNO,
changecode_v2(a.BnfType, 'BnfCat', '') as BNFCAT,
a.customerno||a.bnftype as BNFSERIALNUM,
changecode_v2(a.BnfGrade, 'BNFLevel', '') as BNFLEVEL,
nvl(changecode_v2(a.RelationToInsured, 'RelationToInsured', ''),
'99') as RELATIONTOINSURED,
a.BnfLot as PERCENTOFBENEFIT,
a.CustomerNo as ICCUSTOMERNO,
'' as CUSTOMERNO,
nvl(a.Name, '法定受益人') as NAME,
changecode_v2(a.Sex, 'sex', '') as SEX,
a.Birthday as BIRTHDATE,
( case when length(a.idno)=18 then '111' else changecode_v2(a.IDType, 'CertType', '') end) as CERTTYPE,
a.IDNo as CERTNO,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from LCBnf a, LCPol b, LCCont c, updatetemp_v3 d
where a.ContNo = b.ContNo
and a.ContNo = c.ContNo
and b.ContNo = c.ContNo
and d.CONTNO = a.ContNo
and a.polno = b.polno
and b.appflag <> '9' and b.appflag <>'2'
and d.contno = a.contno
and d.TRANSDATE = i_strdate;
commit;
exception
when others then
p_insert_zbx_v3_logs(i_strdate, 'temp_lcbnf_v3'||SQLERRM || '|' ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||'');
EXECUTE IMMEDIATE 'truncate table temp_lcpoltransaction_v3' ;
commit;
-- dbms_output.put_line('!!!!!!!!!!!!!!!!!');
end p_insert_lcbnf_v3;
--7
PROCEDURE p_insert_lcinsured_v3(i_strdate in date) AS
begin
insert into temp_lcinsured_v3
select d.busino as TransactionNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
'01' as GPFlag,
a.ContNo as PolicyNo,
a.InsuredNo as InsuredNo,
'' as CustomerNo,
nvl(changecode_v2(substr(a.managecom,0,4),'MANAGE',''),'000103') as ManageCom,
nvl(changecode_v2(a.RelationToMainInsured,
'RelationToInsured',
''),
'99') as RelationToMainInsured,
a.InsuredNo as MainInsuredNo,
nvl(decode(a.relationtoappnt,'01','02','02','03',changecode_v2(a.relationtoappnt,'RelationToInsured','')),'99') as RelationToAppnt,
a.Name as InsuredName,
changecode_v2(a.Sex, 'sex', '') as InsuredSex,
to_date(to_char(a.Birthday,'yyyy-mm-dd'),'yyyy-mm-dd') as InsuredBirthDate,
( case when length(a.idno)=18 then '111' else changecode_v2(a.IDType, 'CertType', '') end) as InsuredCertType,
a.IDNo as InsuredCertNo,
nvl((select max(b.InsuredAppAge)
from lcpol b
where b.contno = a.contno and b.mainpolno = b.polno
and b.insuredno = a.insuredno),
floor(MONTHS_BETWEEN(sysdate, date '2000-1-1') / 12)) as AppAge,
changecode_v2(a.nativeplace, 'Country', '') as Country,
'' as Nationality,
a.RgtAddress as RgtAddress,
(case marriage when '0' then '10' when '1' then '20' when '2'
then '30' when '3' then '40' else '90' end) as MarriageStatus,
a.MarriageDate as MarriageDate,
'' as HealthCondition,
(case when lct.policysource = 'EBAOSYS'
then nvl(a.Stature, 0.00)
else
nvl((select to_number(aa.impartparam) from lccustomerimpartparams aa where aa.impartparamname = 'InsuredHeight' and aa.customernotype = '1' and aa.contno = a.contno and aa.customerno = a.insuredno and rownum = 1),0.00)
end)as Height,

(case when lct.policysource = 'EBAOSYS'
then nvl(a.Avoirdupois, 0.00)
else
nvl((select to_number(aa.impartparam) from lccustomerimpartparams aa where aa.impartparamname = 'InsuredWeig' and aa.customernotype = '1' and aa.contno = a.contno and aa.customerno = a.insuredno and rownum = 1),0.00)
end) as Weight,
'' as EducateLevel,
a.StartWorkDate as StartWorkDate,
'' as Position,
(case when lct.policysource = 'EBAOSYS'
then nvl(a.Salary, 0.00)
else
nvl((select to_number(aa.impartparam) from lccustomerimpartparams aa where aa.impartparamname = 'InsuredIncome' and aa.customernotype = '1' and aa.contno = a.contno and aa.customerno = a.insuredno and regexp_like(aa.impartparam,'^[[:digit:]]+\.{0,1}[[:digit:]]*$') and rownum = 1),0.00)
end) as Salary,
nvl(changecode_v2(a.OccupationCode, 'OccupationType', ''),
'9900000') as OccupationType,
a.WorkType as Occupation,
'' as PartTimeOccupationType,
a.PluralityType as PartTimeOccupation,
decode((select 1 from lccustomerimpart aa where aa.contno = a.contno and aa.customerno = a.insuredno and aa.impartver = 'B01' and aa.impartcode = '7' and rownum =1),'1','Y','N') as SmokeFlag,
calbmi(a.contno,a.insuredno) as BMI,
a.License as License,
'' as LicenseType,
a.SocialInsuNo as SocialInsuNo,
'' as SocialInsuFlag,
changecode_v2(a.cityorcounty, 'ResidentType', '') as ResidentType,
'' as InsuredType,
'0' as AnomalyInformFlag,
'' as AnomalyInformDesc,
'' as ServiceFlag,
'' as ArriveCity,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from LCInsured a, updatetemp_v3 d, lccont lct
where a.contno = d.contno
and a.contno = lct.contno
and d.TRANSDATE = i_strdate;

commit;
exception
when others then
p_insert_zbx_v3_logs(i_strdate, 'temp_lcinsured_v3'||SQLERRM || '|' ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||'');
EXECUTE IMMEDIATE 'truncate table temp_lcpoltransaction_v3' ;
commit;

-- dbms_output.put_line('!!!!!!!!!!!!!!!!!');
end p_insert_lcinsured_v3;
--8
PROCEDURE p_insert_lcinsureacc_v3(i_strdate in date) AS
begin --账户结算日期应不早于账户成立日期
insert into temp_lcinsureacc_v3
select d.busino as TransactionNo,
'000103' as COMPANYCODE,
'' as GRPPOLICYNO,
'01' as GPFlag,
a.ContNo as POLICYNO,
nvl(changecode_v2(substr(a.managecom,0,4),'MANAGE',''),'000103') as MANAGECOM,
a.PolNo as PRODUCTNO,
a.InsuAccNo as INSUACCNO,
changecode_v2(a.AccType, 'AccType', '') as ACCTYPE,
nvl((select abs(SUM(money)) from LCInsureAccTrace
where polno = a.polno and insuaccno = a.insuaccno and moneytype = 'CS'),
0.0) as ACCUMCOMMISSION,
nvl((select abs(SUM(MONEY)) from LCInsureAccTrace
where moneytype = 'GL' and polno = a.polno and insuaccno = a.insuaccno),
0.0) as ACCUMMNGFEE,
nvl((select abs(SUM(money)) from LCInsureAccTrace
where polno = a.polno and insuaccno = a.insuaccno and moneytype = 'FX'),
0.0) as ACCUMRISKPREMIUM,
case when a.MakeDate<b.signdate then b.signdate else a.makedate end as ACCOUNTDATE,
case when a.MakeDate<b.signdate then b.signdate else a.makedate end as SETTLEMENTDATE,
decode(a.insuaccno,'000006', abs(nvl(a.SumPay, 0.0)),0) as ACCUMPAY,
nvl(a.SumPaym, 0.0) as ACCUMDWAMNT,
case when a.InsuAccBala <0 then 0 else a.InsuAccBala end as ACCOUNTVALUE,
nvl(round(a.UnitCount, 2), '0.00') as ACCOUNTUNITNUMBER,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from LCInsureAcc a, LCCont b, updatetemp_v3 d
where a.ContNo = b.ContNo
and d.contno = b.contno
and d.TRANSDATE = i_strdate
union all
select d.busino as TransactionNo,
'000103' as COMPANYCODE,
'' as GRPPOLICYNO,
'01' as GPFlag,
a.ContNo as POLICYNO,
nvl(changecode_v2(substr(a.managecom,0,4),'MANAGE',''),'000103') as MANAGECOM,
a.PolNo as PRODUCTNO,
a.InsuAccNo as INSUACCNO,
changecode_v2(a.AccType, 'AccType', '') as ACCTYPE,
nvl((select abs(SUM(money)) from Lcinsureacctrace_Invest
where polno = a.polno and contno = a.contno and moneytype in('TBF','WF')),
0.0) as ACCUMCOMMISSION,
nvl((select -abs(SUM(MONEY)) from Lcinsureacctrace_Invest
where moneytype = 'GL' and polno = a.polno and insuaccno = a.insuaccno),
0.0) as ACCUMMNGFEE,
nvl((select -abs(SUM(money)) from LCInsureAccTrace
where polno = a.polno and insuaccno = a.insuaccno and moneytype = 'FX'),
0.0) as ACCUMRISKPREMIUM,
a.MakeDate as ACCOUNTDATE,
a.BalaDate as SETTLEMENTDATE,
nvl(a.SumPay, 0.0) as ACCUMPAY,
nvl(a.SumPaym, 0.0) as ACCUMDWAMNT,
case when a.InsuAccBala <0 then 0 else a.InsuAccBala end as ACCOUNTVALUE,
nvl(round(a.UnitCount, 2), '0.00') as ACCOUNTUNITNUMBER,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from Lcinsureacc_Invest a, LCCont b, updatetemp_v3 d
where a.ContNo = b.ContNo
and d.contno = b.contno
and a.insuaccno<>'000000'
and d.TRANSDATE = i_strdate;
commit;
exception
when others then
p_insert_zbx_v3_logs(i_strdate, 'temp_lcinsureacc_v3'||SQLERRM || '|' ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||'');
EXECUTE IMMEDIATE 'truncate table temp_lcpoltransaction_v3' ;
commit;
-- dbms_output.put_line('!!!!!!!!!!!!!!!!!');
end p_insert_lcinsureacc_v3;
--9
PROCEDURE p_insert_llclaimpolicy_v3(i_strdate in date) AS
begin
insert into temp_llclaimpolicy_v3
SELECT temp.busino as TransactionNo,
'000103' as CompanyCode,
lp.clmno as ClaimNo,
lp.caseno as ClmRegisterNo,
'' as GrpPolicyNo,
'01' as GPFlag,
lp.contno as PolicyNo,
lp.polno as ProductNo,
lr.rgtdate as RgtDate,
(case
when lp.getdutykind like '%00' then
lc.medaccdate
else
lc.accdate
end) as LossOccurDate,
lr.accidentsite as LossOccurLocation,
changecode_v2(lr.accidentreason, 'LossOccurReason', '') as LossOccurReason,
lc.accdentdesc as AccidentDesc,
changecode_v2(lp.GetDutyKind, 'BenefitType', '') as BenefitType,
lp.riskcode as ProductCode,
changecode_v2(lp.Currency, 'Currency', '') as Currency,
lp.standpay as ClmAcountAmnt,
--lp.realpay as ClmAssessAmnt,
nvl((select sum(ll.getmoney) from llbnf ll where ll.bnfkind='A' and ll.clmno = lp.clmno and ll.contno = lp.contno and ll.polno = lp.polno),lp.realpay) as ClmAssessAmnt,
(case lw.auditconclusion when '1' then (select nvl(sum(declineamnt), 0)
from llclaimdetail
where polno = lp.polno
and clmno = lp.clmno) when '3' then (select nvl(sum(declineamnt), 0)
from llclaimdetail
where polno = lp.polno
and clmno = lp.clmno) else 0 end) as RejectAmnt,
(select nvl(sum(prepaysum), 0)
from llclaimdetail
where polno = lp.polno
and clmno = lp.clmno) as PrePayAmnt,
(case lw.auditconclusion
when '4' then
lp.realpay
else
0
end) as ApproveAmnt,
(case lw.auditconclusion
when '5' then
lp.realpay
else
0
end) as AgreeAmnt,
nvl(lr.endcasedate,lw.examdate) as ClmSettDate,
'2' as PayTypeCode,
nvl(changecode_v2(lw.auditconclusion, 'PayStatusCode', ''),
'99') as PayStatusCode,
'' as AdjusterCode,
'' as AdjusterName,
nvl(changecode_v2(substr(lr.mngcom,0,4),'MANAGE',''),'000103') AS ManageCom,--核赔机构与赔案表保持一致
nvl((select ld.targetcodename from ldcodeMapping_v2 ld where ld.basiccode = substr(lr.mngcom,0,4) and ld.codetype='MANAGE'),'恒大人寿保险有限公司') as ManageComName,
--3期新增字段
lp.realpay as ClaimAmnt,
0 as AssessmentMoney,--公估费用
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from llclaimpolicy lp,
llregister lr,
llcase lc,
LLClaimUWMain lw,
ldcom ld,
updatetemp_v3 temp
where lp.clmno = lr.rgtno
and lp.clmno = lc.caseno
and lp.clmno = lw.clmno
--and lp.standpay > 0
and lr.rgtconclusion = '01'
and nvl(lw.examcom,lw.mngcom) = ld.comcode
and temp.otherno = lp.ClmNo
and temp.contno = lp.contno
and temp.othernotype in ('01', '15', '16', '17', '34', '99')
and temp.TRANSDATE = i_strdate;
commit;
end p_insert_llclaimpolicy_v3;
--10
PROCEDURE p_insert_llbnf_v3(i_strdate in date) AS
begin
insert into temp_llbnf_v3
select TransactionNo,
CompanyCode,
ClaimNo,
GrpPolicyNo,
GPFlag,
PolicyNo,
ProductNo,
InsuredNo,
CustomerNo,
BnfSerialNum,
BnfCat,
BnfLevel,
RelationToInsured,
Name,
Sex,
BirthDate,
CertType,
CertNo,
RelationOfPayeeAndInsured,
PayeeNo,
PayeeName,
PayeeSex,
PayeeBirthday,
PayeeCertType,
PayeeCertNo,
PercentOfBenefit,
sum(DrawingAmnt) as DrawingAmnt,
BenefitWDMthd,
BenefitPayMthd,
BankCode,
BankName,
BankAccNo,
AccName,
EnterAccDate, PushDate from (select distinct b.busino as TransactionNo,
'000103' as CompanyCode,
a.ClmNo as ClaimNo,
'' as GrpPolicyNo,
'01' as GPFlag,
a.ContNo as PolicyNo,
a.PolNo as ProductNo,
a.InsuredNo as InsuredNo,
a.CustomerNo as CustomerNo,
a.BnfNo as BnfSerialNum,
changecode_v2(a.BnfType, 'BnfCat', '') as BnfCat,
nvl(changecode_v2(a.BnfGrade, 'BNFLevel', ''),'1') as BnfLevel,
nvl(changecode_v2(a.RelationToInsured,
'RelationToInsured',
''),
'99') as RelationToInsured,
a.Name as Name,
changecode_v2(a.Sex, 'sex', '') as Sex,
a.Birthday as BirthDate,
( case when length(a.idno)=18 then '111' else changecode_v2(a.IDType, 'CertType', '') end) as CertType,
a.idno as CertNo,
changecode_v2(decode(a.RelationToPayee,'00',a.RelationToInsured,a.RelationToPayee),
'RelationToInsured',
'99') as RelationOfPayeeAndInsured,
a.PayeeNo as PayeeNo,
a.PayeeName as PayeeName,
changecode_v2(a.PayeeSex, 'sex', '') as PayeeSex,
a.PayeeBirthday as PayeeBirthday,
( case when length(a.payeeidno)=18 then '111' else changecode_v2(a.PayeeIDType, 'CertType', '') end) as PayeeCertType,
a.PayeeIDNo as PayeeCertNo,
a.BnfLot as PercentOfBenefit,
a.getmoney as DrawingAmnt,
changecode_v2(nvl(a.CaseGetMode,'1'), 'BenefitWDMthd', '') as BenefitWDMthd,
changecode_v2(a.CasePayMode, 'BenefitPayMthd', '') as BenefitPayMthd,
changecode_v2(a.BankCode, 'BankCode', '') as BankCode,
(select bankname
from ldbankinfo
where bankcode = a.BankCode) as BankName,
a.BankAccNo as BankAccNo,
a.AccName as AccName,
(select enteraccdate
from ljaget
where otherno = a.clmno
and bankcode = a.bankcode
and rownum = 1) as EnterAccDate,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from llbnf a, updateTemp_v3 b
where a.bnfkind <> 'C'
--and a.getmoney > 0
and a.ClmNo = b.otherno
and a.ContNo = b.contno
and b.othernotype in ('01', '15', '16', '17', '34', '99')
and b.TRANSDATE = i_strdate)
group by TransactionNo,
CompanyCode,
ClaimNo,
GrpPolicyNo,
GPFlag,
PolicyNo,
ProductNo,
InsuredNo,
CustomerNo,
BnfSerialNum,
BnfCat,
BnfLevel,
RelationToInsured,
Name,
Sex,
BirthDate,
CertType,
CertNo,
RelationOfPayeeAndInsured,
PayeeNo,
PayeeName,
PayeeSex,
PayeeBirthday,
PayeeCertType,
PayeeCertNo,
PercentOfBenefit,
BenefitWDMthd,
BenefitPayMthd,
BankCode,
BankName,
BankAccNo,
AccName,
EnterAccDate,
PushDate;
commit;
end p_insert_llbnf_v3;
--11
PROCEDURE p_insert_LLClaimDetail_v3(i_strdate in date) AS
begin
insert into temp_LLClaimDetail_v3
select g.busino as TransactionNo,
'000103' as CompanyCode,
a.clmno as ClmCaseNo,
a.caseno as ClmRegisterNo,
'' as GrpPolicyNo,
'01' as GPFlag,
a.ContNo as PolicyNo,
a.PolNo as ProductNo,
a.riskcode as ProductCode,
changecode_v2(a.GetDutyKind, 'BenefitType', '') as BenefitType,
a.dutycode as LiabilityCode,
b.dutyname as LiabilityName,
decode(c.risktype8,'08','1','0') as AllowanceType,
a.getdutycode as GetLiabilityCode,
d.getdutyname as GetLiabilityName,
changecode_v2(a.Currency, 'Currency', '') as Currency,
a.standpay as ClmAcountAmnt,
a.realpay as ClmAssessAmnt,
a.declineamnt as RejectAmnt,
nvl(a.prepaysum, 0) as PrePayAmnt,
(case e.givetype
when '4' then
a.realpay
else
0
end) as ApproveAmnt,
(case e.givetype
when '5' then
a.realpay
else
0
end) as AgreeAmnt,
'1' as DeductibleType,
0 as Deductible, --免赔额，产品定好后添加
0 as ClaimRatio, --赔付比例，同上
f.examdate as ClaimCheckDat,
nvl(e.endcasedate,f.examdate) as ClmSettDate,
nvl(changecode_v2(f.auditconclusion, 'PayStatusCode', ''), '99') as PayStatusCode,
f.examidea as ClaimDesc,
nvl(changecode_v2(f.auditnopassreason, 'llprotestreason', ''),
'99') as RefusePaymentReason,
nvl(changecode_v2(substr(a.polmngcom,0,4),'MANAGE',''),'000103') as ManageCom,
--3期新增字段
changecode_v2(a.getdutykind,'Classification','') as Classification,
decode(a.givetype,'1',a.givereasondesc,'') as RefusePaymentReasonDesc,
decode(c.risktype8,'08','1','0') as OperationAllowanceFlag,--目前只有1款产品 默认否
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from llclaimdetail a,
lmduty b,
lmriskapp c,
lmdutyget d,
llclaim e,
llclaimuwmain f,
Updatetemp_v3 g
where a.dutycode = b.dutycode
and a.riskcode = c.riskcode
and a.getdutycode = d.getdutycode
and a.clmno = e.clmno
and a.clmno = f.clmno
and a.ClmNo = g.otherno
and a.ContNo = g.contno
and g.othernotype in ('01', '15', '16', '17', '34', '99')
and g.TRANSDATE = i_strdate;
commit;
end p_insert_LLClaimDetail_v3;
--12
PROCEDURE p_insert_LLClaimInfo_v3(i_strdate in date) AS
begin
insert into temp_LLClaimInfo_v3
select temp.busino as TransactionNo,
'000103' as CompanyCode,
lp.rptno as ReportNo,
lg.rgtno as ClmRegisterNo,
lg.rgtno as ClaimNo,
lp.rptdate as ReportDate,
lg.applydate as ApplyDate,
lg.rgtdate as RegisterDate,
changecode_v2(lp.rptmode, 'rptmode', '') as ReportMode,
'' as ReporterCustomerNo,
lg.applicantno as ReporterNo,
lp.rptorname as ReporterName,
changecode_v2(lp.relation, 'RelationToInsured', '') as ReporterRelationToInsured,
changecode_v2(la.sex, 'sex', '') as Sex,
la.birthday as ReporterBirthDate,
( case when length(la.idno)=18 then '111' else changecode_v2(la.idtype, 'CertType', '') end) as ReporterCertType,
la.idno as ReporterCertNo,
lp.rptormobile as ReporterContectNo,
lp.rptoraddress as ReporterContectAddress,
'' as ApplyerCustomerNo,
lg.applicantno as ApplyerNo,
lg.rgtantname as ApplyerName,
changecode_v2(lg.relation, 'RelationToInsured', '') as ApplyerRelationToInsured,
changecode_v2(la.sex, 'sex', '') as ApplyerSex,
la.birthday as ApplyerBirthDate,
( case when length(la.idno)=18 then '111' else changecode_v2(la.idtype, 'CertType', '') end) as ApplyerCertType,
la.idno as ApplyerCertNo,
lg.rgtantmobile as ApplyerContectNo,
lg.rgtantaddress as ApplyerContectAddress,
nvl(nvl(lc.medaccdate,lc.accdate),lg.accidentdate) as AccidentDate,
changecode_v2(lg.accidentreason, 'LossOccurReason', '') as AccidentReason,
'' as Province,
'' as City,
'' as County,
replace(lg.accidentsite,'|','') as Street,
substr(lc.accdentdesc,0,230) as AccidentCourse,
changecode_v2((select diseasecode
from llspdisease
where clmno = lg.rgtno
and typecode in ('ZJ', 'TD')
and rownum = 1),
'desease',
'') as CriticalIllnessCode,
case when lg.accidentreason = '1' then
changecode_v2(lc.AccidentDetail, 'InjuryReasonCode', '')
else '' end as InjuryReasonCode,
'' as DisabilityCode, --伤残代码，待后期维护
lg.accepteddate as AffixGetDate,
null as OneTimeInform, --一次性告知时间，核心无
'0' as MicroClmFlag, --系统未指定小额案件，所有案件均走正常流程
null as MicroClmSettDate,
(case (select '1'
from llinqapply
where clmno = lp.rptno
and rownum = 1)
when '1' then
'1'
else
'0'
end) as ClaimInvestigationFlag,
(case (select '1'
from llinqapply
where clmno = lp.rptno
and rownum = 1)
when '1' then
'99'
else
''
end) as RemoteClaimInvestigationCode, --异地理赔暂不做处理
(select min(inqstartdate)
from llinqapply
where clmno = lp.rptno) as InvestigationStartDate,
(select max(inqenddate) from llinqapply where clmno = lp.rptno) as InvestigationEndDate,
(select inqconclusion
from llinqconclusion
where clmno = lp.rptno
and batno <> '000000'
and rownum = 1) as InvestigationConclusion,
(select masflag
from llinqconclusion
where clmno = lp.rptno
and batno = '000000') as PositiveFlag,
'' as CoordinateFlag, --协调标志，核心无
'' as CoordinateReason, --协调原因，核心无
nvl(changecode_v2(lg.rgtconclusion, 'rgtconclusion', ''), '2') as RegisterFlag,
(select codename
from ldcode
where code = lg.norgtreason
and codetype = 'llnorgtreason') as NoRegisterReason,
(case
when lm.auditconclusion in ('0', '1', '4', '5') then
'1'
else
'0'
end) as CaseReviewPassFlag,
lm.auditdate as CaseReviewDate,
lm.auditidea as CaseReviewOpinion,
'' as RecallFlag, --报案注销标志，核心无 结案日期
null as RecallDate, --报案注销日期，核心无
nvl(changecode_v2(substr(lg.mngcom,0,4),'MANAGE',''),'000103') as ManageCom,
nvl((select ld.targetcodename from ldcodeMapping_v2 ld where ld.basiccode = substr(lg.mngcom,0,4) and ld.codetype='MANAGE'),'恒大人寿保险有限公司') as ManageComName,
--3期平台新增字段
'' as DisabilityLevel ,--目前伤残等级 待取值
nvl((select sum(lt.realpay) from llclaimdetail lt where lt.rgtno = lp.rptno),0) as ClaimAmnt,
'0' as ReOpenCaseFlag ,--重开赔案标志 无取值
'' as OriginClaimNo,--原赔案号
nvl(lg.endcasedate,lm.examdate) as ClmSettDate,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate,
'01' as GPFlag
from llreport lp,
llregister lg,
llapplicants la,
llcase lc,
llclaimuwmain lm,
ldcom ld,
updatetemp_v3 temp
where lp.rptno = lg.rgtno
and lg.applicantno = la.applicantno
and lg.rgtno = la.rgtno
and lp.rptno = lc.caseno
and lp.rptno = lm.clmno(+)
and lg.mngcom = ld.comcode
and temp.otherno = lp.rptno
and temp.othernotype in ('01', '15', '16', '17', '34', '99')
and temp.TRANSDATE = i_strdate;
commit;
end p_insert_LLClaimInfo_v3;
--13
PROCEDURE p_insert_LLClaimantInfo_v3(i_strdate in date) AS
begin
insert into temp_LLClaimantInfo_v3
select temp.busino as TransactionNo,
'000103' as CompanyCode,
lp.rptno as ReportNo,
lg.rgtno as ClmRegisterNo,
lg.rgtno as ClaimNo,
lc.customerno as ClaimantNo,
'' as CustomerNo,
ld.name as Name,
changecode_v2(lc.customersex, 'sex', '') as Sex,
ld.birthday as BirthDate,
( case when length(ld.idno)=18 then '111' else changecode_v2(ld.idtype, 'CertType', '')end) as DangerCertTypeCode,
ld.idno as CertNo,
(case when exists (select 1 from lccont lt where lt.appntno = lc.customerno and lt.contno = temp.contno and rownum =1) then '1' else '2' end) as PolType, --默认全部为被保人 ，判断是否为投保人
lc.dieflag as DieFlag,
lc.deathdate as DeathDate,
(case lg.accidentreason
when '1' then
'2'
when '2' then
'1'
end) as DeathReason,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate,
'01' as GPFlag
from llreport lp,
llregister lg,
llcase lc,
ldperson ld,
updatetemp_v3 temp
where lp.rptno = lg.rgtno
and lp.rptno = lc.caseno
and lc.customerno = ld.customerno
and temp.otherno = lp.rptno
and temp.othernotype in ('01', '15', '16', '17', '34', '99')
and temp.TRANSDATE = i_strdate;
commit;
end p_insert_LLClaimantInfo_v3;
--14
PROCEDURE p_insert_LLReceipt_v3(i_strdate in date) is
v_otherno varchar2(20);
cursor cursor_temp_update is
select busino,contno, otherno, othernotype, transdate
from updatetemp_v3
where othernotype in ('01', '15', '16', '17', '34', '99');
begin
v_otherno := '' ;
for v_temp_update in cursor_temp_update loop
v_otherno := v_temp_update.otherno ;---2019-07-29 主收据号重复问题
insert into temp_LLReceipt_v3
select distinct v_temp_update.busino as TransactionNo,
'000103' as CompanyCode,
lf.clmno as ClaimNo,
lf.clmno as ClmRegisterNo,
(case when exists (select 1 from llcasereceipt ll where ll.clmno = lcr.clmno and ll.mainfeeno = lcr.mainfeeno and ll.startdate is not null and ll.startdate<>lcr.startdate )
then lcr.feedetailno else lcr.mainfeeno end ) as ReceiptNo,
'1' as ReceiptType, --原收据编号
lf.customerno as ClaimantNo,
'' as CustomerNo,
lp.name as name,
changecode_v2(lca.customersex, 'sex', '') as Sex,
lp.birthday as BirthDate,
( case when length(lp.idno)=18 then '111' else changecode_v2(lp.idtype, 'CertType', '') end) as DangerCertTypeCode,
lp.idno as CertNo,
'9' as InsuranceType,
changecode_v2(lf.feetype, 'llfeetype', '') as ReceiptMedicalType,
'9' as HospitalNature,
lf.hospitalcode as HospitalCode,
case when lengthb(lf.hospitalname)>120 then substr(lf.hospitalname,0,instr(lf.hospitalname,'（')) else lf.hospitalname end as HospitalName,
(select lt.mngcom from llcommendhospital lt where lt.hospitalcode = lf.hospitalcode) as HospitalRegion,--3期修改地区码为标准码
(case lcr.feeitemtype
when 'A' then
lcr.startdate
else
null
end) as ClinicDate,
(case lcr.feeitemtype
when 'B' then
lcr.startdate
else
null
end) as InHospitalDate,
(case lcr.feeitemtype
when 'B' then
lcr.enddate
else
null
end) as DischardeDate,
(case lcr.feeitemtype
when 'B' then
to_number(lcr.daycount)
else
0
end) as HospitalStay,
(case
when lcr.feeitemcode like '%C002' then
to_number(lcr.daycount)
else
0
end) as IntensiveCareDays,
changecode_v2(lcr.currency, 'Currency', '') as Currency,
(select sum(fee)
from llcasereceipt
where clmno = lf.clmno
and mainfeeno = lf.mainfeeno) as FeeAmount,
(select sum(selfamnt)
from llcasereceipt
where clmno = lf.clmno
and mainfeeno = lf.mainfeeno) as FeeDeductibleAmount,
0 as ReceiptClaimAmount, --收据赔付金额，后面计算
(case
when (select sum(fee - selfamnt - securityamnt -
thirdpartyamnt)
from llcasereceipt
where clmno = lf.clmno
and mainfeeno = lf.mainfeeno) > 0 then
(select sum(fee - selfamnt - securityamnt -
thirdpartyamnt)
from llcasereceipt
where clmno = lf.clmno
and mainfeeno = lf.mainfeeno)
else
0
end) as MedicalPayment,
0 as AnnualMedicalPayment,
0 as Payment,
0 as PaymentA,
0 as PaymentFrom,
0 as PaymentCap,
0 as PaymentB,
(select sum(selfamnt)
from llcasereceipt
where clmno = lf.clmno
and mainfeeno = lf.mainfeeno) as OwnExpense,
0 as AccountPayment,
0 as AccountBalance,
0 as FundPayment,
0 as LargeAmountPayment,
0 as AnnualFundPayment,
0 as AnnualLargeAmountPayment,
0 as LargeAmountBalance,
'0' as NRCMSFlag,
0 as NRCMSReimbursementMoney,
(case
when (select sum(thirdpartyamnt)
from llcasereceipt
where clmno = lf.clmno
and mainfeeno = lf.mainfeeno) > 0 then
'1'
else
'0'
end) as OtherPaymentFlag,
(select sum(thirdpartyamnt)
from llcasereceipt
where clmno = lf.clmno
and mainfeeno = lf.mainfeeno) as OtherPayment,
'0' as BirthFlag,
(case
when lcr.feeitemcode like '%S001' then
'1'
else
'0'
end) as OperationFlag,
'0' as SpecialFlag,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate,
'01' as GPFlag
from llcasereceipt lcr,
llfeemain lf,
llcase lca,
ldperson lp
where lcr.clmno = lf.clmno
and lcr.mainfeeno = lf.mainfeeno
and lcr.clmno = lca.caseno
and lcr.customerno = lp.customerno
and lcr.clmno = v_temp_update.otherno
;
commit;

end loop ;
exception
when others then
p_insert_zbx_v3_logs(i_strdate, 'p_insert_LLReceipt_v3'||SQLERRM || '|' ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||': '||v_otherno );
end p_insert_LLReceipt_v3;
--15
PROCEDURE p_insert_LLCostItem_v3(i_strdate in date) AS
begin
insert into temp_LLCostItem_v3
select TransactionNo,
CompanyCode,
ClaimNo,
ClmRegisterNo,
ClaimantNo,
CustomerNo,
name,
Sex,
BirthDate,
DangerCertTypeCode,
CertNo,
ReceiptNo,
nvl(ReceiptMedicalType,'99'),
nvl(CostItemName,'其它-不细分'),
sum(FeeAmount),
sum(FeeDeductibleAmount),
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate,
'01' as GPFlag
from
(select temp.busino as TransactionNo,
'000103' as CompanyCode,
lf.clmno as ClaimNo,
lf.clmno as ClmRegisterNo,
lf.customerno as ClaimantNo,
'' as CustomerNo,
lp.name as name,
changecode_v2(lp.sex, 'sex', '') as Sex,
lp.birthday as BirthDate,
( case when length(lp.idno)=18 then '111' else changecode_v2(lp.idtype, 'CertType', '') end) as DangerCertTypeCode,
lp.idno as CertNo,
(case when exists (select 1 from llcasereceipt ll where ll.clmno = lca.clmno and ll.mainfeeno = lca.mainfeeno and ll.feeitemtype=lca.feeitemtype and ll.startdate is not null and ll.startdate<>lca.startdate )
then lca.feedetailno else lca.mainfeeno end ) as ReceiptNo,
changecode_v2(substr(lca.feeitemcode, 2), 'CostItem', '') as ReceiptMedicalType,
nvl(lca.feeitemname, ( select ld.basiccodename from ldcodemapping_v2 ld where ld.codetype='CostItem' and ld.basiccode = substr(lca.feeitemcode, 2)) ) as CostItemName,
lca.fee as FeeAmount,
lca.selfamnt as FeeDeductibleAmount

from llcasereceipt lca,
llfeemain lf,
ldperson lp,
updatetemp_v3 temp
where lca.clmno = lf.clmno
and lca.mainfeeno = lf.mainfeeno
and lf.customerno = lp.customerno
and lca.clmno = temp.otherno
and lca.fee>0
and temp.othernotype in ('01', '15', '16', '17', '34', '99')
and temp.TRANSDATE = i_strdate
) group by
TransactionNo,
CompanyCode,
ClaimNo,
ClmRegisterNo,
ClaimantNo,
CustomerNo,
name,
Sex,
BirthDate,
DangerCertTypeCode,
CertNo,
ReceiptNo,
ReceiptMedicalType,
CostItemName
;

commit;
end p_insert_LLCostItem_v3;
--16
PROCEDURE p_insert_LLDisease_v3(i_strdate in date) AS
begin
insert into temp_LLDisease_v3
select temp.busino as TransactionNo,
'000103' as CompanyCode,
lg.rgtno as ClaimNo,
lg.rgtno as ClmRegisterNo,
lc.customerno as ClaimantNo,
'' as CustomerNo,
lp.name as name,
changecode_v2(lp.sex, 'sex', '') as Sex,
lp.birthday as BirthDate,
( case when length(lp.idno)=18 then '111' else changecode_v2(lp.idtype, 'CertType', '' ) end ) as DangerCertTypeCode,
lp.idno as CertNo,
nvl(lc.hospitalcode, '9999999') as hospitalcode, --医院编码，待维护
nvl(lc.hospitalname,'其他') as hospitalname,
'2' as DiseaseType,
lc.accresult2 as DiseaseCode,
ld.icdname as DiseaseName,
'' as TumorCode,
'' as TumorName,
'' as DiagnosisTypeCode,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate,
'01' as GPFlag
from llregister lg,
llcase lc,
ldperson lp,
lddisease ld,
updatetemp_v3 temp
where lg.rgtno = lc.caseno
and lc.customerno = lp.customerno
and lc.accresult2 = ld.icdcode
and lg.rgtno = temp.otherno
and exists
(select 1
from llclaimpolicy
where ((substr(getdutykind,0,1) = '2') or getdutykind in ('104','105'))
and clmno = lg.rgtno)
and temp.othernotype in ('01', '15', '16', '17', '34', '99')
and temp.TRANSDATE = i_strdate;
commit;
end p_insert_LLDisease_v3;
--17
PROCEDURE p_insert_LLOperation_v3(i_strdate in date) AS
begin
--insert into temp_LLOperation_v3 commit;
dbms_output.put_line('!!!!!!!!!手术信息表(LLOperation)!!!!!!!!');
end p_insert_LLOperation_v3;
--18
PROCEDURE p_insert_lasales_v3(i_strdate in date) AS
begin

insert into temp_lasales_v3
select c.busino as TransactionNo,
'000103' as CompanyCode,
a.agentcode as Agentcode,
'' as GrpPolicyNo,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
b.contno as PolicyNo,
nvl(changecode_v2(substr(a.managecom,0,4),'MANAGE',''),'000103') as ManageCom,
nvl(a.name, '') as Name,
changecode_v2(a.sex, 'sex', '') as Sex,
(case
when exists
(select 1
from dual
where (to_char(a.birthday, 'yyyy-mm-dd') NOT LIKE '19%' AND
to_char(a.birthday, 'yyyy-mm-dd') NOT LIKE '20%' AND
to_char(a.birthday, 'yyyy-mm-dd') NOT LIKE '21%' AND
to_char(a.birthday, 'yyyy-mm-dd') NOT LIKE '99%')) then
to_date(substr(a.idno,7,8), 'yyyy-mm-dd')
else
a.birthday
end) as BirthDate,
a.quafno as QuafCertNo,
a.quafstartdate as CertStartDate,
a.quafenddate as CertEndDate,
'000000' as BusiDevCertifNo,
nvl((select ta.indoorwork_flag from icms.t_agent ta where ta.agent_code = a.agentcode),'') as OfficeWorkFlag,
'99' as AgentSignFlag,
nvl((select ta.fulltime_flag from icms.t_agent ta where ta.agent_code = a.agentcode),'') as FullTimeFlag,
nvl((select ta.saleservice_flag from icms.t_agent ta where ta.agent_code = a.agentcode), '3') as SaleSvcFlag,
( case when length(a.idno)=18 then '111' else changecode_v2(a.idtype, 'CertType', '') end) as CertType,
a.idno as CertNo,
nvl((select ta.saleservice_appraise from icms.t_agent ta where ta.agent_code = a.agentcode),'') as SaleJudge,
--3期新增字段
a.mobile as Mobile,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from laagent a, lccont b, updatetemp_v3 c
where a.agentcode = b.agentcode(+)
and b.contno = c.contno
and b.salechnl in ('01','02','05')
--and b.appflag <> '0'
and c.TRANSDATE = i_strdate
;

commit;
-- -- dbms_output.put_line('!!!!!!!!!保险公司销售人员信息表LASales!!!!!!!!');
end p_insert_lasales_v3;
--19
PROCEDURE p_insert_laagent_v3(i_strdate in date) AS
begin
insert into temp_laagent_v3
select c.busino as TransactionNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
b.contno as PolicyNo,
nvl(changecode_v2(substr(a.managecom,0,4),'MANAGE',''),'000103') as ManageCom,
/*b.agentcom as AgencyCode,
nvl((case
when b.salechnl = '06' then
(select lc.name from lacom lc where lc.agentcom = b.agentcom)
when b.salechnl = '03' then
(select banknetname from ldbanknet where banknetid = substr(b.agentbankcode,0,4) and rownum =1)
else
'000103510000'
end),
'000103510000') as AgencyName,*/
(case
when b.salechnl = '06' then nvl((select ta.supervision_code from icms.T_AGENCY ta where ta.agency_code = b.agentcom),
b.agentcom )
when b.salechnl = '03' then nvl((select ta.supervision_code from icms.T_bank ta where ta.bank_code = b.agentcom),
b.agentcom )
else '000103510000'
end) as AgencyCode,
(case
when b.salechnl = '06' then nvl((select ta.supervision_name from icms.T_AGENCY ta where ta.agency_code = b.agentcom),
(select lc.name from lacom lc where lc.agentcom = b.agentcom and rownum =1))
when b.salechnl = '03' then nvl((select ta.supervision_name from icms.T_bank ta where ta.bank_code = b.agentcom),
( select banknetname from ldbanknet where banknetid = substr(b.agentbankcode,0,4) and rownum =1))
else '000103510000'
end) as AgencyName,
a.agentcode as agentcode,
a.name as Name,
changecode_v2(a.sex, 'sex', '') as Sex,
(case
when exists
(select 1
from dual
where (to_char(a.birthday, 'yyyy-mm-dd') NOT LIKE '19%' AND
to_char(a.birthday, 'yyyy-mm-dd') NOT LIKE '20%' AND
to_char(a.birthday, 'yyyy-mm-dd') NOT LIKE '21%' AND
to_char(a.birthday, 'yyyy-mm-dd') NOT LIKE '99%')) then
to_date(substr(a.idno,7,8), 'yyyy-mm-dd')
else
a.birthday
end) as BirthDate,
(case
when b.salechnl = '03' then nvl((select ta.qualification_id from icms.T_BANK_EMPLOYEE ta where ta.emp_code = a.agentcode),
a.quafno )
when b.salechnl = '06' then nvl((select ta.qualification_id from icms.T_AGENCY_MEMBER ta where ta.agent_code = a.agentcode),
a.quafno )
else ''
end) as QuafCertNo,
(case
when b.salechnl = '03' then nvl((select ta.credentials_start from icms.T_BANK_EMPLOYEE ta where ta.emp_code = a.agentcode),
a.quafstartdate )
when b.salechnl = '06' then nvl((select ta.credentials_start from icms.T_AGENCY_MEMBER ta where ta.agent_code = a.agentcode),
a.quafstartdate )
else null
end) as CertStartDate,
(case
when b.salechnl = '03' then nvl((select ta.credentials_end from icms.T_BANK_EMPLOYEE ta where ta.emp_code = a.agentcode),
a.quafenddate )
when b.salechnl = '06' then nvl((select ta.credentials_end from icms.T_AGENCY_MEMBER ta where ta.agent_code = a.agentcode),
a.quafenddate )
else null
end) as CertEndDate,
'000000' as BusiDevCertifNo,
(case
when b.salechnl = '03' then (select ta.indoorwork_flag from icms.T_BANK_EMPLOYEE ta where ta.emp_code = a.agentcode)
when b.salechnl = '06' then (select ta.indoorwork_flag from icms.T_AGENCY_MEMBER ta where ta.agent_code = a.agentcode)
else ''
end) as OfficeWorkFlag,
'99' as AgentSignFlag,
(case
when b.salechnl = '03' then (select ta.fulltime_flag from icms.T_BANK_EMPLOYEE ta where ta.emp_code = a.agentcode)
when b.salechnl = '06' then (select ta.fulltime_flag from icms.T_AGENCY_MEMBER ta where ta.agent_code = a.agentcode)
else ''
end) as FullTimeFlag,
changecode_v2(a.idtype, 'CertType', '') as CertType,
a.idno as CertNo,
(case
when b.salechnl = '03' then (select ta.service_appraise from icms.T_BANK_EMPLOYEE ta where ta.emp_code = a.agentcode)
when b.salechnl = '06' then (select ta.service_appraise from icms.T_AGENCY_MEMBER ta where ta.agent_code = a.agentcode)
else ''
end) as SaleJudge,
--3期新增字段
a.mobile as Mobile,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from laagent a, lccont b, updatetemp_v3 c
where a.agentcode = b.agentcode
and b.contno = c.contno
and b.salechnl in ( '03','06')
--and b.appflag<> '0'
--and a.agentcode is not null
and c.TRANSDATE = i_strdate;

-- dbms_output.put_line('!!!!!!!!!保险中介渠道及从业人员信息表(LAAgent)!!!!!!!!');
commit;
exception
when others then
p_insert_zbx_v3_logs(i_strdate, 'temp_laagent_v3'||SQLERRM || '|' ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||'');
EXECUTE IMMEDIATE 'truncate table temp_lcpoltransaction_v3' ;
commit;
end p_insert_laagent_v3;
--20
PROCEDURE p_insert_LACommission_v3(i_strdate in date) AS
begin
insert into temp_LACommission_v3
select b.busino as busino,
'000103' as CompanyCode,
'' as GrpPolicyNo,
'' as GrpProductNo,
a.policy_code as PolicyNo,
a.benefit_item_id as ProductNo,
'01' as GPFlag,
a.internal_id as ProductCode,
a.fee_type as BusinessType,
a.item_id as SequenceNo,
'156' as Currency,
a.commision_rate as CommissionRate,
a.commision as CommissionAmount,
a.update_time as ConfDate,
a.update_time as EnterAccDate,
a.comm_bank_code as BankCode,
a.bank_name as BankName,
a.comm_account as BankAccNo,
a.real_name as AccName,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from MV_GX_COMMISION_INFO a,updatetemp_v3 b
where to_char(a.item_id) = b.otherno
and a.policy_code = b.contno
and b.othernotype = '99'
and to_char(a.update_time,'yyyy-mm-dd') = to_char(i_strdate,'yyyy-mm-dd');
commit;
end p_insert_LACommission_v3;
--21
PROCEDURE p_insert_ljtempfee_v3(i_strdate in date) AS

begin
insert into temp_ljtempfee_v3
select ljd.busino as TransactionNo,
ljd.CompanyCode,
ljd.GrpPolicyNo,
ljd.PolicyNo,
ljd.GPFlag,
ljd.PrtNo,
ljd.ManageCom,
ljd.ReceiptNo,
ljd.PayAmnt,
ljd.Currency,
ljd.EnterAccDate,
ljd.RecogDate,
ljd.BankCode,
ljd.BankName,
ljd.BankAccNo,
ljd.AccName,
ljd.CertType,
ljd.CertNo,
ljd.PushDate
from (select d.busino as busino,
'000103' as CompanyCode,
'' as GrpPolicyNo,
b.contno as PolicyNo,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
b.PrtNo as PrtNo,
nvl(changecode_v2(substr(b.managecom,0,4),'MANAGE',''),'000103') as ManageCom,
c.TempFeeNo as ReceiptNo,
c.PayMoney as PayAmnt,
changecode_v2(c.Currency, 'Currency', '') as Currency,
c.EnterAccDate as EnterAccDate,
c.ConfDate as RecogDate,
c.BankCode as BankCode,
(select BankName
from ldbankinfo
where bankcode = c.bankcode) as BankName,
b.BankAccNo as BankAccNo,
b.AccName as AccName,
( case when length(b.appntidno)=18 then '111' else changecode_v2(b.AppntIDType, 'CertType', '') end) as CertType,
b.AppntIDNo as CertNo,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from LCCont b, LJTempFeeclass c, updatetemp_v3 d
where (b.contno = c.otherno or b.prtno = c.otherno)
and b.contno = d.contno
and d.othernotype in
('02', '03', '04', '29', '39', '44', '45', '46', '99')
and d.otherno = b.contno
and (c.confdate is null or
(c.confdate = i_strdate and c.paydate < c.confdate))
union all
select e.busino as busino,
'000103' as CompanyCode,
'' as GrpPolicyNo,
b.contno as PolicyNo,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
b.PrtNo as PrtNo,
nvl(changecode_v2(substr(b.managecom,0,4),'MANAGE',''),'000103') as ManageCom,
c.TempFeeNo as ReceiptNo,
c.PayMoney as PayAmnt,
nvl(changecode_v2(c.Currency, 'Currency', ''), '156') as Currency,
c.EnterAccDate as EnterAccDate,
c.ConfDate as RecogDate,
c.BankCode as BankCode,
(select BankName
from ldbankinfo
where bankcode = c.bankcode) as BankName,
b.BankAccNo as BankAccNo,
b.AccName as AccName,
( case when length(b.appntidno)=18 then '111' else changecode_v2(b.AppntIDType, 'CertType', '') end) as CertType,
b.AppntIDNo as CertNo,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from LCCont b,
LJTempFeeclass c,
lpedoritem d,
updatetemp_v3 e
where b.contno = d.contno
and (c.otherno = d.edoracceptno or c.otherno = d.edorno)
and e.othernotype in ('02',
'03',
'04',
'09',
'10',
'11',
'12',
'14',
'15',
'29',
'39',
'44',
'45',
'46',
'99')
and e.otherno = d.edoracceptno
and (c.confdate is null or
(c.confdate = i_strdate and c.paydate < c.confdate))) ljd
where ljd.enteraccdate is not null;
-- dbms_output.put_line('!!!!!!!!暂收费表(LJTempFee)!!!!!!!!');
commit;
end p_insert_ljtempfee_v3;
--22
PROCEDURE p_insert_ljdivdistrib_v3(i_strdate in date) AS
begin
insert into temp_ljdivdistrib_v3
select b.busino as TransactionNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
a.ContNo as PolicyNo,
a.polno as ProductNo,
to_char(a.FiscalYear) as DivYear,
'1' as DivType,
nvl(changecode_v2(a.BonusGetMode, 'DivDistribMeth', ''), '2') as DivDistribMeth,
a.BonusMoney as DivAmnt,
nvl(changecode_v2(substr(c.managecom,0,4),'MANAGE',''),'000103') as ManageCom,
c.appntno as AppntNo,
c.appntname as AppntName,
c.bankcode as BankCode,
(select bk.bankname
from ldbankinfo bk
where bk.bankcode = c.bankcode) as BankName,
c.bankaccno as BankAccNo,
c.accname as AccName,
( case when length(c.idno)=18 then '111' else changecode_v2(c.idtype, 'CertType', '') end) as CertType,
c.idno as CertNo,
--3期新增字段
decode(a.BONUSFLAG,'1',a.bonusmoney,0) as DivAmntDraw,
(case a.BonusGetMode
when '2' then
(select confdate
from ljaget
where otherno = a.contno
and othernotype = '7'
and confdate is not null
and rownum = 1)
else
decode(a.BONUSFLAG , '1', a.agetdate)
end) as DivDrawDate,--现金分红未实际领取时，值为空
a.bonusmoney as ExpectedDivAmnt,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from LOBonusPol a, lcappnt c, updatetemp_v3 b
where a.contno = c.contno
and b.contno = a.contno
and b.otherno = a.contno
and b.othernotype = '17'
and a.BonusMoney > 0
and b.TRANSDATE = i_strdate
union all
select b.busino as TransactionNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
d.ContNo as PolicyNo,
d.polno as ProductNo,
to_char(d.FiscalYear) as DivYear,
'2' as DivType,
'3' as DivDistribMeth,
d.bonusamnt as DivAmnt,
nvl(changecode_v2(substr(c.managecom,0,4),'MANAGE',''),'000103') as ManageCom,
c.appntno as AppntNo,
c.appntname as AppntName,
c.bankcode as BankCode,
(select bk.bankname
from ldbankinfo bk
where bk.bankcode = c.bankcode) as BankName,
c.bankaccno as BankAccNo,
c.accname as AccName,
( case when length(c.idno)=18 then '111' else changecode_v2(c.idtype, 'CertType', '') end) as CertType,
c.idno as CertNo,
--3期新增字段
decode(d.getflag,'1',d.bonusamnt,0) as DivAmntDraw,
decode(d.getflag,'1',d.sdispatchdate) as DivDrawDate,
d.bonusamnt as ExpectedDivAmnt,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from loengbonuspol d, lcappnt c, updatetemp_v3 b
where d.contno = c.contno
and b.contno = d.contno
and b.otherno = d.contno
and b.othernotype = '17'
and d.bonusamnt > 0
and b.TRANSDATE = i_strdate;
-- -- dbms_output.put_line('!!!!!!!!!红利分配表(LJDivDistrib)!!!!!!!!');
commit;
end p_insert_ljdivdistrib_v3;
--23
PROCEDURE p_insert_ljagetlivbene_v3(i_strdate in date) AS

begin
insert into temp_ljagetlivbene_v3
select c.busino as TransactionNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
a.ContNo as PolicyNo,
a.polno as ProductNo,
row_number() over(partition by a.ContNo, a.polno order by a.GetDate) as WDNo,
a.AppntNo as AppntNo,
a.InsuredNo as InsuredNo,
a.GetDate as BenefitDate,
a.GetMoney as BenefitAmount,
a.EnterAccDate as EnterAccDate,
nvl(changecode_v2(substr(a.managecom,0,4),'MANAGE',''),'000103') as ManageCom,
a.CurGetToDate as WithdrawalToDate,
e.bankcode as BankCode,
(select bk.bankname
from ldbankinfo bk
where bk.bankcode = e.bankcode) as BankName,
e.bankaccno as BankAccNo,
e.accname as AccName,
( case when length(e.edorappidno)=18 then '111' else changecode_v2(e.edorappidtype, 'CertType', '') end) as CertType,
e.edorappidno as CertNo,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from LJAGetDraw a, lpedorapp e, updatetemp_v3 c ,ljaget lj
where a.contno = c.contno
and a.contno = e.otherno
and a.actugetno = lj.actugetno
and lj.otherno = e.edoracceptno
and a.feefinatype ='YF'
and c.otherno = a.actugetno
and c.othernotype = '16'
and exists (select 1
from ldperson d
where d.DeathDate is null
and a.InsuredNo = d.CustomerNo)
and c.TRANSDATE = i_strdate
--生存金周年日直接发放
union all
select c.busino as TransactionNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
a.ContNo as PolicyNo,
a.polno as ProductNo,
row_number() over(partition by a.ContNo, a.polno order by a.GetDate) as WDNo,
a.AppntNo as AppntNo,
a.InsuredNo as InsuredNo,
a.GetDate as BenefitDate,
a.GetMoney as BenefitAmount,
a.EnterAccDate as EnterAccDate,
nvl(changecode_v2(substr(a.managecom,0,4),'MANAGE',''),'000103') as ManageCom,
a.CurGetToDate as WithdrawalToDate,
lj.bankcode as BankCode,
(select bk.bankname
from ldbankinfo bk
where bk.bankcode = lj.bankcode) as BankName,
lj.bankaccno as BankAccNo,
lj.drawer as AccName,
( case when length(lj.drawerid)=18 then '111' else changecode_v2(nvl(lj.drawertype,'0'), 'CertType', '' ) end) as CertType,
lj.drawerid as CertNo,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from LJAGetDraw a, updatetemp_v3 c ,ljaget lj
where a.contno = c.contno
and c.otherno = a.actugetno
and c.othernotype = '16'
and a.feefinatype ='YF'
and lj.actugetno=a.actugetno and lj.othernotype='2' and lj.otherno = a.contno
and c.TRANSDATE = i_strdate
--生存金转万能账户
union all
select c.busino as TransactionNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
a.ContNo as PolicyNo,
a.polno as ProductNo,
row_number() over(partition by a.ContNo, a.polno order by a.paydate) as WDNo,
lc.AppntNo as AppntNo,
lc.InsuredNo as InsuredNo,
a.paydate as BenefitDate,
a.money as BenefitAmount,
null as EnterAccDate,
nvl(changecode_v2(substr(a.managecom,0,4),'MANAGE',''),'000103') as ManageCom,
null as WithdrawalToDate,
'' as BankCode,
'' as BankName,
'' as BankAccNo,
'' as AccName,
'' as CertType,
'' as CertNo,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from lcinsureacctrace a, updatetemp_v3 c ,lccont lc
where a.contno = c.contno
and lc.contno = a.contno
and c.othernotype = '16'
and a.moneytype ='LVTI'
and a.serialno = c.standbystring1

;

-- -- dbms_output.put_line('!!!!!!!!!生存金领取表(LJAGetLivBene)!!!!!!!!');
commit;

end p_insert_ljagetlivbene_v3;
--24
PROCEDURE p_insert_ljapay_v3(i_strdate in date) AS

begin
insert into temp_ljapay_v3
select d.busino as TransactionNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
b.contno as PolicyNo,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
(case
when b.paycount = '1' and b.mainpolyear = 1 then
'1'
else
'2'
end) as PremiumType, --保费业务类型代码 1-首期，2-续期，9-其他
(row_number() over(partition by b.ContNo order by b.PolNo)) as PayNo,
b.polno as ProductNo,
b.riskcode as ProductCode,
nvl(changecode_v2((select substr(managecom,0,4) from lccont where contno =b.contno),'MANAGE',''),'000103') as ManageCom,
nvl((select AppntNo from lccont where contno = b.contno),
'00000') as AppntNo,
b.SumActuPayMoney as SumActuPayMoney,
b.EnterAccDate as EnterAccDate,
nvl(b.ConfDate, b.EnterAccDate) as ConfDate,
(case when b.curpaytodate>to_date('9999-01-01','yyyy-mm-dd') then b.paydate else b.curpaytodate end) as CurPaidToDate,
a.bankcode as BankCode,
(select bk.bankname
from ldbankinfo bk
where bk.bankcode = a.bankcode) as BankName,
a.bankaccno as BankAccNo,
a.accname as AccName,
( case when length(a.idno)=18 then '111' else changecode_v2(a.idtype, 'CertType', '') end) as CertType,
a.idno as CertNo,
--3期新增字段
'' as PaymentType,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from ljapay a, ljapayperson b, updatetemp_v3 d
where b.payno = a.payno(+)
--and a.bankcode is not null
--and a.makedate <= d.transdate + 1
and d.othernotype not in('16','16a')
and b.contno = d.contno
and d.TRANSDATE = i_strdate
union all
select
d.busino as TransactionNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
lc.contno as PolicyNo,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
(case d.othernotype
when '16' then
'5'
else
'6'
end) as PremiumType, --保费业务类型代码 1-首期，2-续期，9-其他,5年金\红利 6 生存金
(row_number() over(partition by lc.ContNo order by lc.makedate)) as PayNo,
lc.polno as ProductNo,
lc.riskcode as ProductCode,
nvl(changecode_v2(substr(t.managecom,0,4) ,'MANAGE',''),'000103') as ManageCom,
nvl(t.appntno, '00000') as AppntNo,
lc.money as SumActuPayMoney,
lc.makedate as EnterAccDate,
lc.makedate as ConfDate,
lc.makedate as CurPaidToDate,
'' as BankCode,
'' as BankName,
'' as BankAccNo,
'' as AccName,
'' as CertType,
'' as CertNo,
--3期新增字段
'' as PaymentType,
trunc(sysdate) as PushDate
from lcinsureacctrace lc,updatetemp_v3 d ,lccont t
where lc.contno = d.otherno and lc.contno =t.contno
and d.othernotype in ('16')
and lc.contno = d.contno
and d.transdate = i_strdate
and lc.insuaccno = '000006'
and lc.moneytype in ('LVTI')
and lc.money>0
and lc.makedate = d.transdate
--红利转万能 20200528 chenzhuo start
union all
select
d.busino as TransactionNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
lc.contno as PolicyNo,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
'9' as PremiumType, --保费业务类型代码 1-首期，2-续期，9-其他,5年金\红利 6 生存金
(row_number() over(partition by lc.ContNo order by lc.makedate)) as PayNo,
lc.polno as ProductNo,
lc.riskcode as ProductCode,
nvl(changecode_v2(substr(t.managecom,0,4) ,'MANAGE',''),'000103') as ManageCom,
nvl(t.appntno, '00000') as AppntNo,
lc.money as SumActuPayMoney,
lc.makedate as EnterAccDate,
lc.makedate as ConfDate,
lc.makedate as CurPaidToDate,
'' as BankCode,
'' as BankName,
'' as BankAccNo,
'' as AccName,
'' as CertType,
'' as CertNo,
--3期新增字段
'' as PaymentType,
trunc(sysdate) as PushDate
from lcinsureacctrace lc,updatetemp_v3 d ,lccont t
where lc.contno = d.otherno and lc.contno =t.contno
and d.othernotype in ('16a')
and lc.contno = d.contno
and d.transdate = i_strdate
and lc.insuaccno = '000006'
and lc.moneytype in ('HTW')
and lc.money>0
and lc.makedate = d.transdate
--红利转万能 20200528 chenzhuo end
;
-- dbms_output.put_line('!!!!!!!!!保费明细表(LJAPay)!!!!!!!!');
commit;
end p_insert_ljapay_v3;
--25
PROCEDURE p_insert_lmproduct_v3(i_strdate in date) AS
begin
insert into temp_lmproduct_v3
select '000000' as TransactionNo,
'000103' as CompanyCode,
risk.riskcode as ProductCode,
risk.riskname as ProductName,
risk.productabbr as ProductAbbr,
risk.productenname as ProductEnName,
risk.productenname as PorductEnAbbr,
risk.insuaccflag as InsuAccFlag,
risk.assumintrate as AssumIntRate,
risk.producttype as ProductType,
risk.investmenttype as InvestmentType,
risk.termtype as TermType,
'01' as GPFlag,
risk.mainproductflag as MainProductFlag,
risk.stopdate as StopDate,
risk.saleflag as SaleFlag,
risk.circriskcode as CircRiskCode,
risk.shortdurationproduct as ShortDurationProduct, --中短存续期产品
risk.changefeeproduct as ChangeFeeProduct, --费改产品标志
--3期新增字段
risk.taxratevaliddate as TaxRateValidDate,
risk.taxrateexpirydate as TaxRateExpiryDate,
risk.taxrate as TaxRate,
risk.mininterestrate as MinInterestRate,
risk.startdate as StartDate,
risk.openarea as OpenArea,
risk.saleschannels as SalesChannels,
risk.specificbusiness as SpecificBusiness,
risk.specificbusinesscode as SpecificBusinessCode,
risk.hesitateperiod as HesitatePeriod ,
risk.waitingperiod as WaitingPeriod,
risk.loanratio as LoanRatio,
risk.loanperiod as LoanPeriod,
risk.minappage as MinAppAge,
risk.maxappage as MaxAppAge,
risk.appsex as AppSex,
risk.divtype as DivType,
risk.majordiseasesnum as MajorDiseasesNum,
risk.milddiseasenum as MildDiseaseNum,
risk.majordiseasesmaxbenefitnum as MajorDiseasesMaxBenefitNum,
risk.milddiseasesmaxbenefitnum as MildDiseasesMaxBenefitNum,
risk.majordiseasesbenefittype as MajorDiseasesBenefitType,
risk.majordiseasesbenefitpolstate as MajorDiseasesBenefitPolState,
risk.annuitytype as AnnuityType,
risk.annuityperiod as AnnuityPeriod,
risk.policyfeeflag as PolicyFeeFlag,
risk.initialfeeflag as InitialFeeFlag,
risk.breakthroughsocialsecurityflag as BreakThroughSocialSecurityFlag,
risk.designatedhospitalflag as DesignatedHospitalFlag,
risk.vipclinicflag as VIPClinicFlag,
risk.specialhospital as SpecialHospital ,
risk.medicalcoveragearea as MedicalCoverageArea ,
risk.deductiblecategory as DeductibleCategory,
risk.deductible1 as Deductible1,
risk.deductible2 as Deductible2,
risk.deductibleratio1 as DeductibleRatio1,
risk.deductibleratio2 as DeductibleRatio2,
risk.claimratio1 as ClaimRatio1,
risk.claimratio2 as ClaimRatio2,
risk.guaranteedrenewableflag GuaranteedRenewableFlag,
risk.guaranteedrenewableperiod GuaranteedRenewablePeriod,
risk.maxguaranteedrenewableage as MaxGuaranteedRenewableAge,
risk.operationallowanceflag as OperationAllowanceFlag,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from LMproduct risk
;
--and u.otherno = risk.riskcode;
-- -- dbms_output.put_line('!!!!!!!!!险种定义表(LMProduct)!!!!!!!!');
commit;
for tm in(
select startdate,aa.riskcode from lmriskapp aa
)loop
update lmproduct bb
set bb.startdate=tm.startdate
WHERE bb.riskcode=tm.riskcode ;
end loop;
commit;
end p_insert_lmproduct_v3;
--26
PROCEDURE p_insert_ljaget_v3(i_strdate in date) AS

begin
insert into temp_ljaget_v3
select b.busino as TransactionNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
d.contno as PolicyNo,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
c.polno as ProductNo, --个单保险险种号码
c.riskcode as ProductCode, --产品编码
--nvl((select prtno from lccont where contno = b.contno), '00000') as PrtNo,
a.ActuGetNo as ActuGetNo,
(case d.edortype when 'CT' then '03' when 'WT' then '18' else changecode_v2(a.OtherNoType, 'GetType', '') end) as GetType,
changecode_v2(a.PayMode, 'BenefitPayMthd', '') as GetWay,
nvl(changecode_v2(a.Currency, 'Currency', ''), '156') as Currency,
c.money as SumActuGetMoney,
a.EnterAccDate as EnterAccDate,
a.ConfDate as ConfDate,
(case a.paymode when '4'then a.bankcode else '' end) as BankCode,
(case a.paymode when '4'then (select BankName from ldbankmapping_ats where bankcode = a.bankcode) else '' end) as BankName,
(case a.paymode when '4'then a.bankaccno else a.bankaccno end) as BankAccNo,
(case a.paymode when '4'then a.drawer else a.accname end) as AccName,
(case a.paymode when '4'then changecode_v2(a.drawertype, 'CertType', '') else '' end) as CertType,
(case a.paymode when '4'then a.drawerid else '' end) as CertNo,
--3期平台新增字段
decode(a.othernotype,'5',a.otherno,'')as ClaimNo,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from LJAGET a
join lpedoritem d
on a.otherno = d.edoracceptno
join lpedorapp e
on d.edoracceptno = e.edoracceptno
left join (select p.endorsementno,
p.riskcode,
p.polno,
sum(case
when p.getflag = '0' then
-p.getmoney
else
p.getmoney
end) money
from ljagetendorse p
group by p.endorsementno, p.riskcode, p.polno) c
on d.edorno = c.endorsementno
join updatetemp_v3 b
on b.othernotype in ('13', '15', '16', '17', '31', '32', '37'，'69'）--保全补退费增加
and a.otherno = b.otherno
where a.otherno = b.otherno
and a.enteraccdate is not null
and c.riskcode is not null
and d.edorstate = '0'
and b.TRANSDATE = i_strdate
union all
select b.busino as TransactionNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
d.contno as PolicyNo,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
c.polno as ProductNo, --个单保险险种号码
c.riskcode as ProductCode, --产品编码
--nvl((select prtno from lccont where contno = b.contno), '00000') as PrtNo,
a.ActuGetNo as ActuGetNo,
(case d.edortype when 'CT' then '03' when 'WT' then '18' else changecode_v2(a.OtherNoType, 'GetType', '') end) as GetType,
changecode_v2(a.PayMode, 'BenefitPayMthd', '') as GetWay,
nvl(changecode_v2(a.Currency, 'Currency', ''), '156') as Currency,
c.money as SumActuGetMoney,
a.EnterAccDate as EnterAccDate,
a.ConfDate as ConfDate,
(case a.paymode when '4'then a.bankcode else '' end) as BankCode,
(case a.paymode when '4'then (select BankName from ldbankmapping_ats where bankcode = a.bankcode) else '' end) as BankName,
(case a.paymode when '4'then a.bankaccno else a.bankaccno end) as BankAccNo,
(case a.paymode when '4'then a.drawer else a.accname end) as AccName,
(case a.paymode when '4'then changecode_v2(a.drawertype, 'CertType', '') else '' end) as CertType,
(case a.paymode when '4'then a.drawerid else '' end) as CertNo,
--3期平台新增字段
decode(a.othernotype,'5',a.otherno,'')as ClaimNo,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from LJAGET a
join lpedoritem d
on a.otherno = d.edoracceptno
join lpedorapp e
on d.edoracceptno = e.edoracceptno
left join (select p.endorsementno,
p.riskcode,
p.polno,
sum(case
when p.getflag = '0' then
-p.getmoney
else
p.getmoney
end) money
from ljagetendorse p
group by p.endorsementno, p.riskcode, p.polno) c
on d.edorno = c.endorsementno
join updatetemp_v3 b
on b.othernotype ='76'--保全补退费增加
and a.actugetno = b.standbystring1
where a.actugetno = b.standbystring1
and a.enteraccdate is not null
and c.riskcode is not null
and d.edorstate = '0'
and b.TRANSDATE = i_strdate
union all
select b.busino as TransactionNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
c.contno as PolicyNo,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
c.polno as ProductNo, --个单保险险种号码
c.riskcode as ProductCode, --产品编码
--nvl((select prtno from lccont where contno = b.contno), '00000') as PrtNo,
a.ActuGetNo as ActuGetNo,
changecode_v2(a.OtherNoType, 'GetType', '') as GetType,
changecode_v2(a.PayMode, 'BenefitPayMthd', '') as GetWay,
nvl(changecode_v2(a.Currency, 'Currency', ''), '156') as Currency,
c.getmoney as SumActuGetMoney,
a.EnterAccDate as EnterAccDate,
a.ConfDate as ConfDate,
(case a.paymode when '4'then a.bankcode else '' end) as BankCode,
(case a.paymode when '4'then (select BankName from ldbankmapping_ats where bankcode = a.bankcode) else '' end) as BankName,
(case a.paymode when '4'then a.bankaccno else a.bankaccno end) as BankAccNo,
(case a.paymode when '4'then a.drawer else a.accname end) as AccName,
(case a.paymode when '4'then changecode_v2(a.drawertype, 'CertType', '') else '' end) as CertType,
(case a.paymode when '4'then a.drawerid else '' end) as CertNo,
--3期平台新增字段
decode(a.othernotype,'5',a.otherno,'')as ClaimNo,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from LJAGET a ,ljagetdraw c, updatetemp_v3 b
where a.actugetno = c.actugetno
and a.actugetno = b.otherno
and c.contno = b.contno
and b.othernotype='16'
and c.feefinatype ='YF'
and a.enteraccdate is not null
and b.TRANSDATE = i_strdate
union all
select b.busino as TransactionNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
f.contno as PolicyNo,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
f.polno as ProductNo, --个单保险险种号码
f.riskcode as ProductCode, --产品编码
--nvl((select prtno from lccont where contno = b.contno), '00000') as PrtNo,
a.ActuGetNo as ActuGetNo,
changecode_v2(a.OtherNoType, 'GetType', '') as GetType,
changecode_v2(a.PayMode, 'BenefitPayMthd', '') as GetWay,
nvl(changecode_v2(a.Currency, 'Currency', ''), '156') as Currency,
f.money as SumActuGetMoney,
a.EnterAccDate as EnterAccDate,
a.ConfDate as ConfDate,
(case a.paymode when '4'then a.bankcode else '' end) as BankCode,
(case a.paymode when '4'then (select BankName from ldbankmapping_ats where bankcode = a.bankcode) else '' end) as BankName,
(case a.paymode when '4'then a.bankaccno else a.bankaccno end) as BankAccNo,
(case a.paymode when '4'then a.drawer else a.accname end) as AccName,
(case a.paymode when '4'then changecode_v2(a.drawertype, 'CertType', '') else '' end) as CertType,
(case a.paymode when '4'then a.drawerid else '' end) as CertNo,
--3期平台新增字段
decode(a.othernotype,'5',a.otherno,'')as ClaimNo,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from LJAGET a
join (select lc.contno contno,
lc.otherno otherno,
lc.polno polno,
lc.riskcode riskcode,
sum(lc.pay) money
from ljagetclaim lc
group by lc.contno, lc.otherno, lc.polno, lc.riskcode) f
on a.otherno = f.otherno
join updatetemp_v3 b
on a.otherno = b.otherno
and f.contno = b.contno
and b.othernotype in ('13', '15', '16', '17', '31', '32', '37'，'69','76')
where a.enteraccdate is not null
and b.TRANSDATE = i_strdate
union all
select temp.busino as TransactionNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
lp.contno as PolicyNo,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
lp.polno as ProductNo, --个单保险险种号码
lp.riskcode as ProductCode, --产品编码
--nvl((select prtno from lccont where contno = b.contno), '00000') as PrtNo,
a.ActuGetNo as ActuGetNo,
changecode_v2(a.OtherNoType, 'GetType', '') as GetType,
changecode_v2(a.PayMode, 'BenefitPayMthd', '') as GetWay,
nvl(changecode_v2(a.Currency, 'Currency', ''), '156') as Currency,
a.sumgetmoney as SumActuGetMoney,
a.EnterAccDate as EnterAccDate,
a.ConfDate as ConfDate,
a.bankcode as BankCode,
(select BankName from ldbankmapping_ats where bankcode = a.bankcode) as BankName,
a.bankaccno as BankAccNo,
a.accname as AccName,
changecode_v2(a.drawertype, 'CertType', '') as CertType,
a.drawerid as CertNo,
--3期平台新增字段
decode(a.othernotype,'5',a.otherno,'')as ClaimNo,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from LJAGET a ,updatetemp_v3 temp ,lcpol lp
where a.otherno = temp.otherno
and temp.contno = lp.contno
and temp.standbystring1 = lp.riskcode
and temp.othernotype ='41'
and a.enteraccdate is not null
;
-- dbms_output.put_line('!!!!!!!!!付费明细表(LJAGet)!!!!!!!!');
commit;

end p_insert_ljaget_v3;
--27
PROCEDURE p_insert_LAllUnderWriting_v3(i_strdate in date) AS
begin
insert into temp_lallunderwriting_v3
SELECT temp.busino as TransactionNo,
'000103' as COMPANYCODE,
'000000' as GRPPOLICYNO,
'000000' as GrpProductNo,
lm.ContNo as POLICYNO,
lp.polno as ProductNo,
'01' as GPFLAG,
lp.mainpolno as MainProductNo,
decode(lp.polno, lp.mainpolno, '1', '2') as MainProductFlag,
lp.riskcode as ProductCode,
nvl(changecode_v2(substr(lp.managecom,0,4),'MANAGE',''),'000103') as ManageCom,
lm.autouwflag as UWType,
'1' as UWBusinessType,
decode((select 1
from lcpenotice
where contno = lm.contno
and rownum = 1),
'1',
'1',
'0') as PhysicalExamination,
decode((select 1
from lcrreport
where contno = lm.contno
and rownum = 1),
'1',
'1',
'0') as InvestigationFlag,
decode((select 1
from lccspec
where contno = lm.contno
and rownum = 1),
'1',
'1',
'0') as ExceptDutyFlag,
(select replace(replace(bb.speccontent,CHR(10),''),chr(13),'')
from lccspec bb
where bb.contno = lm.contno
and rownum = 1) as ExceptDutyRemark,
case when exists(select 1
from lcprem
where contno = lm.contno
and substr(payplancode, 0, 6) = '000000'
and polno = lp.polno and rownum = 1) then '1' else '0' end as AddFeeFlag,
(select codename
from ldcode
where codetype = 'addfeetype'
and code in (select payplantype
from lcprem c
where substr(c.payplancode, 0, 6) = '000000'
and c.contno = lm.contno
and c.polno = lp.polno) and rownum = 1) as AddFeeRemark,
decode((select 1
from lccspec
where contno = lm.contno
and rownum = 1),
'1',
'1',
'0') as SpecialFlag,
(select replace(replace(bb.speccontent,CHR(10),''),chr(13),'')
from lccspec bb
where bb.contno = lm.contno
and rownum = 1) as SpecialRemark,
decode((select 1
from lccspec
where contno = lm.contno
and rownum = 1),
'1',
'1',
'0') as ContSpecialFlag,
(select replace(replace(bb.speccontent,CHR(10),''),chr(13),'')
from lccspec bb
where bb.contno = lm.contno
and rownum = 1) as ContSpecialRemark,
lm.modifydate as UWDate,
changecode_v2(lm.passflag, 'UWConclusion', '') as UWConclusion,
(case when lm.passflag = '1' then
replace(replace(nvl(nvl(lm.uwidea,lcc.uwidea),'拒保'),CHR(13),''),CHR(10),'')
else lm.uwidea end) as UWDesc,
decode(lm.passflag,
'1',
nvl((changecode_v2((select aa.commonreasoncode
from lccuwmaster aa
where aa.contno = lm.contno
and aa.passflag = '1' and rownum = 1),
'uwderefreason',
'')),
'10'),
'') as DeclinedReason,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
FROM updatetemp_v3 temp, lcuwmaster lm, lcpol lp,lccuwmaster lcc
WHERE lm.ContNo = temp.contno
and lm.polno = lp.polno
and lm.passflag not in ('a','8')
and lcc.passflag <> '8'
and lm.contno = lcc.contno
and lp.appflag <> '9' and lp.appflag <>'2'
--and lp.enddate > i_strdate
and temp.TRANSDATE = i_strdate
union all
SELECT temp.busino as TransactionNo,
'000103' as COMPANYCODE,
'000000' as GRPPOLICYNO,
'000000' as GrpProductNo,
lpm.contno as POLICYNO,
lpm.polno as ProductNo,
'01' as GPFLAG,
lp.mainpolno as MainProductNo,
decode(lp.polno, lp.mainpolno, '1', '2') as MainProductFlag,
lp.riskcode as ProductCode,
nvl(changecode_v2(substr(lp.managecom,0,4),'MANAGE',''),'000103') as ManageCom,
lpm.autouwflag as UWType,
'2' as UWBusinessType,
decode((select 1
from lppenotice
where contno = lpm.contno
and rownum = 1),
'1',
'1',
'0') as PhysicalExamination,
decode((select 1
from lprreport
where contno = lpm.contno
and rownum = 1),
'1',
'1',
'0') as InvestigationFlag,
decode((select 1
from lpcspec
where contno = lpm.contno
and rownum = 1),
'1',
'1',
'0') as ExceptDutyFlag,
(select replace(replace(bb.speccontent,CHR(10),''),chr(13),'')
from lpcspec bb
where bb.contno = lpm.contno
and rownum = 1) as ExceptDutyRemark,
case when exists(select 1
from lcprem
where contno = lp.contno
and substr(payplancode, 0, 6) = '000000'
and polno = lp.polno and rownum = 1) then '1' else '0' end as AddFeeFlag,
(select codename
from ldcode
where codetype = 'addfeetype'
and code in (select payplantype
from lpprem c
where substr(c.payplancode, 0, 6) = '000000'
and c.contno = lpm.contno
and c.polno = lp.polno) and rownum = 1) as AddFeeRemark,
decode((select 1
from lpcspec
where contno = lpm.contno
and rownum = 1),
'1',
'1',
'0') as SpecialFlag,
(select replace(replace(bb.speccontent,CHR(10),''),chr(13),'')
from lpcspec bb
where bb.contno = lpm.contno
and rownum = 1) as SpecialRemark,
decode((select 1
from lpcspec
where contno = lpm.contno
and rownum = 1),
'1',
'1',
'0') as ContSpecialFlag,
(select replace(replace(bb.speccontent,CHR(10),''),chr(13),'')
from lpcspec bb
where bb.contno = lpm.contno
and rownum = 1) as ContSpecialRemark,
lpm.modifydate as UWDate,
changecode_v2(lpm.passflag, 'UWConclusion', '') as UWConclusion,
(case when lpm.passflag = '1' then
replace(replace(nvl(nvl(lpm.uwidea,lpc.uwidea),'拒保'),CHR(13),''),CHR(10),'')
else lpm.uwidea end) as UWDesc,
decode(lpm.passflag,
'1',
nvl((changecode_v2((select aa.commonreasoncode
from lpcuwmaster aa
where aa.contno = lpm.contno
and aa.passflag = '1' and rownum = 1),
'uwderefreason',
'')),
'10'),
'') as DeclinedReason,

to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
FROM updatetemp_v3 temp, lpuwmaster lpm, lpedoritem lpt, lcpol lp,lpcuwmaster lpc
WHERE lpt.edoracceptno = temp.otherno
and lpt.edorno = lpm.edorno
and lpm.polno = lp.polno
and lpm.passflag is not null
and lpm.edorno = lpc.edorno
and lpm.contno = lpc.contno
and lp.appflag <> '9'and lp.appflag <>'2'
--and lp.enddate > i_strdate
and temp.TRANSDATE = i_strdate
union all
SELECT temp.busino as TransactionNo,
'000103' as COMPANYCODE,
'000000' as GRPPOLICYNO,
'000000' as GrpProductNo,
llm.contno as POLICYNO,
llm.polno as ProductNo,
'01' as GPFLAG,
lp.mainpolno as MainProductNo,
decode(lp.polno, lp.mainpolno, '1', '2') as MainProductFlag,
lp.riskcode as ProductCode,
nvl(changecode_v2(substr(lp.managecom,0,4),'MANAGE',''),'000103') as ManageCom,
'2' as UWType,
'3' as UWBusinessType,
decode((select 1
from lluwpenotice
where contno = llm.contno
and rownum = 1),
'1',
'1',
'0') as PhysicalExamination,
'0' as InvestigationFlag,
decode((select 1
from lluwspecmaster
where contno = llm.contno
and rownum = 1),
'1',
'1',
'0') as ExceptDutyFlag,
(select replace(replace(bb.speccontent,CHR(10),''),chr(13),'')
from lluwspecmaster bb
where bb.contno = llm.contno
and rownum = 1) as ExceptDutyRemark,
case when exists(select 1
from lcprem
where contno = lp.contno
and substr(payplancode, 0, 6) = '000000'
and polno = lp.polno and rownum = 1) then '1' else '0' end as AddFeeFlag,
(select codename
from ldcode
where codetype = 'lluwaddfeetype'
and code in (select payplantype
from lluwpremmaster c
where substr(c.payplancode, 0, 6) = '000000'
and c.contno = llm.contno
and c.polno = lp.polno) and rownum = 1) as AddFeeRemark,
decode((select 1
from lluwspecmaster
where contno = llm.contno
and rownum = 1),
'1',
'1',
'0') as SpecialFlag,
(select replace(replace(bb.speccontent,CHR(10),''),chr(13),'')
from lluwspecmaster bb
where bb.contno = llm.contno
and rownum = 1) as SpecialRemark,
decode((select 1
from lluwspecmaster
where contno = llm.contno
and rownum = 1),
'1',
'1',
'0') as ContSpecialFlag,
(select replace(replace(bb.speccontent,CHR(10),''),chr(13),'')
from lluwspecmaster bb
where bb.contno = llm.contno
and rownum = 1) as ContSpecialRemark,
llm.modifydate as UWDate,
changecode_v2(llm.passflag, 'UWConclusion', '') as UWConclusion,
(case when llm.passflag = '1' then
replace(replace(nvl(nvl(llm.uwidea,llc.uwidea),'拒保'),CHR(13),''),CHR(10),'')
else llm.uwidea end) as UWDesc,
'' as DeclinedReason,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
FROM updatetemp_v3 temp, lluwmaster llm, lcpol lp,llcuwmaster llc
WHERE temp.otherno = llm.caseno
and llm.polno = lp.polno
and llm.contno = llc.contno
and llm.caseno = llc.caseno
and lp.appflag <> '9' and lp.appflag <>'2'
--and lp.enddate > i_strdate
and temp.TRANSDATE = i_strdate
union all
SELECT temp.busino as TransactionNo,
'000103' as COMPANYCODE,
'000000' as GRPPOLICYNO,
'000000' as GrpProductNo,
ruw.contno as POLICYNO,
lp.polno as ProductNo,
'01' as GPFLAG,
lp.mainpolno as MainProductNo,
decode(lp.polno, lp.mainpolno, '1', '2') as MainProductFlag,
lp.riskcode as ProductCode,
nvl(changecode_v2(substr(lp.managecom,0,4),'MANAGE',''),'000103') as ManageCom,
'1' as UWType,
'4' as UWBusinessType,
decode((select 1
from rnewpenotice
where contno = ruw.contno
and rownum = 1),
'1',
'1',
'0') as PhysicalExamination,
decode((select 1
from rnewrreport
where contno = ruw.contno
and rownum = 1),
'1',
'1',
'0') as InvestigationFlag,
decode((select 1
from lccspec
where contno = ruw.contno
and rownum = 1),
'1',
'1',
'0') as ExceptDutyFlag,
(select replace(replace(bb.speccontent,CHR(10),''),chr(13),'')
from lccspec bb
where bb.contno = ruw.contno
and rownum = 1) as ExceptDutyRemark,
case when exists(select 1
from lcprem
where contno = lp.contno
and substr(payplancode, 0, 6) = '000000'
and polno = lp.polno and rownum = 1) then '1' else '0' end as AddFeeFlag,
(select codename
from ldcode
where codetype = 'addfeetype'
and code in (select payplantype
from lcprem c
where substr(c.payplancode, 0, 6) = '000000'
and c.contno = ruw.contno
and c.polno = lp.polno) and rownum = 1) as AddFeeRemark,
decode((select 1
from lccspec
where contno = ruw.contno
and rownum = 1),
'1',
'1',
'0') as SpecialFlag,
(select replace(replace(bb.speccontent,CHR(10),''),chr(13),'')
from lccspec bb
where bb.contno = ruw.contno
and rownum = 1) as SpecialRemark,
decode((select 1
from lccspec
where contno = ruw.contno
and rownum = 1),
'1',
'1',
'0') as ContSpecialFlag,
(select replace(replace(bb.speccontent,CHR(10),''),chr(13),'')
from lccspec bb
where bb.contno = ruw.contno
and rownum = 1) as ContSpecialRemark,
ruw.modifydate as UWDate,
changecode_v2(ruw.passflag, 'UWConclusion', '') as UWConclusion,
(case when lm.passflag = '1' then
replace(replace(nvl(nvl(lm.uwidea,lcc.uwidea),'拒保'),CHR(13),''),CHR(10),'')
else lm.uwidea end) as UWDesc,
decode(ruw.passflag,
'1',
nvl((changecode_v2((select aa.commonreasoncode
from lccuwmaster aa
where aa.contno = ruw.contno
and aa.passflag = '1' and rownum = 1),
'uwderefreason',
'')),
'10'),
'') as DeclinedReason,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
FROM updatetemp_v3 temp,
rnewinduwmaster ruw,
lcpol lp,
lcuwmaster lm,
lccuwmaster lcc
WHERE temp.ContNo = ruw.contno
and ruw.contno = lm.contno
and lm.polno = lp.polno
and lm.contno = lcc.contno
and lm.passflag not in ('a','8')
and lcc.passflag <> '8'
and ruw.passflag <> '8'
and lp.appflag <> '9' and lp.appflag <>'2'
--and lp.enddate > i_strdate
and temp.TRANSDATE = i_strdate;
commit;
insert into temp_lallunderwriting_v3
select temp.busino as TransactionNo,
'000103' as COMPANYCODE,
'000000' as GRPPOLICYNO,
'000000' as GrpProductNo,
lp.contno as POLICYNO,
lp.polno as ProductNo,
'01' as GPFLAG,
lp.mainpolno as MainProductNo,
decode(lp.polno, lp.mainpolno, '1', '2') as MainProductFlag,
lp.riskcode as ProductCode,
nvl(changecode_v2(substr(lp.managecom,0,4),'MANAGE',''),'000103') as ManageCom,
'1' as UWType,
'1' as UWBusinessType,
'0' as PhysicalExamination,
'0' as InvestigationFlag,
'0' as ExceptDutyFlag,
'' as ExceptDutyRemark,
(case when (temp.ProfessionalFee+temp.substandardfee)>0 then '1' else '0' end) as AddFeeFlag,
'' as AddFeeRemark,
'0' as SpecialFlag,
'' as SpecialRemark,
'0' as ContSpecialFlag,
'' as ContSpecialRemark,
nvl(lp.signdate,lp.makedate) as UWDate,
'10' as UWConclusion,
'' as UWDesc,
'' as DeclinedReason,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from temp_LCProduct_v3 temp, lcpol lp
where temp.ProductNo = lp.polno
and temp.policyno = lp.contno
and lp.appflag <> '9'and lp.appflag <>'2'
and temp.ProductNo not in (select ProductNo from temp_lallunderwriting_v3
where UWBusinessType = '1');
commit;
-- -- dbms_output.put_line('!!!!!!!!!!!!!!!!!');
end p_insert_LAllUnderWriting_v3;
--28
PROCEDURE p_insert_LCBlacklist_v3(i_strdate in date) AS
begin
insert into temp_LCBlacklist_v3
select
temp.busino busino ,
'000103' companycode ,
replace(b.id,'-') blacklistno ,
'' customerno ,
'2' blaclistkmarkcode ,
'99' blacklistreason ,
decode(b.list_type,'1','个人','2','国家','3','组织') blacklistremark ,
b.name name ,
b.sex sex ,
'' birthdate ,
(case when b.card_no is not null then (case when length(b.card_no) is null then '990' when length(b.card_no)=18 and b.card_type='11' and ( b.country ='中国' or b.country='CHN') then '111' else '990' end) when b.card_no is not null then '990' else '' end) certtype ,
b.card_no certno ,
'' poltypecode ,
'' blacklistdate ,
'1' status ,
'' expiredreason ,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') pushdate ,
'01' gpflag
from busi_blacklist b ,updatetemp_v3 temp
where temp.otherno = substr(b.id,-30)
and temp.othernotype='71';
commit;

-- dbms_output.put_line('!!!!!!!!!黑名单信息表(LCBlacklist)!!!!!!!!');
end p_insert_LCBlacklist_v3;

/**
3期新增账户履历表
*/
--29
PROCEDURE p_insert_lcinsureacctrace_v3(i_strdate in date) AS
v_count number ;
v_max number ;
begin
select count(1) into v_max from updatetemp_v3 ;
v_count := 1 ;
while v_count < v_max loop
insert into temp_LCInsureAccTrace_v3
select tmp.busino as TransactionNo,
'000103' as CompanyCode,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
'' as GrpPolicyNo,
'' as GrpProductNo,
a.contno as PolicyNo,
a.polno as ProductNo,
a.serialno as SerialNo,
a.insuaccno as InsuAccNo,
a.riskcode as ProductCode,
(case
when a.money < 0 and a.moneytype not in ('FX', 'GL') then
'02'
else
changecode_v2(a.moneytype, 'MoneyType', '')
end) as MoneyType,
a.money as Money,
0 as UnitCount, --单位数 默认0
(case when a.paydate < lc.makedate then lc.makedate else a.paydate end) as PayDate,
nvl(a.calrate, 0) as BalanceRate,
'' as AccountSource,
'' as ContSource,
nvl(changecode_v2((select substr(managecom, 0, 4)
from lccont
where contno = a.contno),
'MANAGE',
''),
'000103') as ManageCom,
(case
when /*exists(select 1 from dual where tmp.othernotype in(
'08','09','10','12','13','14','18','19','20','21','22','24','25','26','27','28',
'29','30','31','32','33','34','35','36','37','38','49','50','51','52','53','54','56',
'61','62','65','67','68','69','70'
))*/
exists (select 1
from temp_lcpoltransaction_v3 tl
where tl.busino = tmp.busino
and tl.transtypecorrbusstype = '500') then
tmp.otherno
else
''
end) as EndorsementNo, -- 账户履历无保全号或是理赔号
(case
when exists
(select 1 from dual where tmp.othernotype in ('15', '63')) then
tmp.otherno
else
''
end) as claimNo,
to_date(to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd') as PushDate
from lcinsureacctrace a,lcinsureacc lc,
(SELECT *
FROM (SELECT tt.*, ROWNUM AS rowno
FROM (SELECT t.*
FROM updatetemp_v3 t
ORDER BY t.busino DESC) tt
WHERE ROWNUM < v_count+10000) table_alias
WHERE table_alias.rowno >= v_count) tmp
where a.contno = tmp.contno
and a.contno = lc.contno
and a.insuaccno = lc.insuaccno
and a.riskcode = lc.riskcode
and a.polno = lc.polno
and a.money <> 0
union all
select tmp.busino as TransactionNo,
'000103' as CompanyCode,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
'' as GrpPolicyNo,
'' as GrpProductNo,
a.contno as PolicyNo,
a.polno as ProductNo,
a.serialno as SerialNo,
a.insuaccno as InsuAccNo,
a.riskcode as ProductCode,
(case
when a.money < 0 and a.moneytype not in ('FX', 'GL') then
'02'
else
changecode_v2(a.moneytype, 'MoneyType', '')
end) as MoneyType,
a.money as Money,
a.unitcount as UnitCount, --单位数 默认0
(case when a.paydate < lc.makedate then lc.makedate else nvl(a.valuedate, a.paydate) end) as PayDate,
0 as BalanceRate,
'' as AccountSource,
'' as ContSource,
nvl(changecode_v2((select substr(managecom, 0, 4)
from lccont
where contno = a.contno),
'MANAGE',
''),
'000103') as ManageCom,
(case
when /*exists(select 1 from dual where tmp.othernotype in(
'08','09','10','12','13','14','18','19','20','21','22','24','25','26','27','28',
'29','30','31','32','33','34','35','36','37','38','49','50','51','52','53','54','56',
'61','62','65','67','68','69','70'
))*/
exists (select 1
from temp_lcpoltransaction_v3 tl
where tl.busino = tmp.busino
and tl.transtypecorrbusstype = '500') then
a.otherno
else
''
end) as EndorsementNo, -- 账户履历无保全号或是理赔号
(case
when exists
(select 1 from dual where tmp.othernotype in ('15', '63')) then
tmp.otherno
else
''
end) as claimNo,
to_date(to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd') as PushDate
from lcinsureacctrace_invest a,lcinsureacc_invest lc,
(SELECT *
FROM (SELECT tt.*, ROWNUM AS rowno
FROM (SELECT t.*
FROM updatetemp_v3 t
ORDER BY t.busino DESC) tt
WHERE ROWNUM < v_count+10000) table_alias
WHERE table_alias.rowno >= v_count) tmp
where a.contno = tmp.contno
and a.money <> 0
and a.contno = lc.contno
and a.insuaccno = lc.insuaccno
and a.riskcode = lc.riskcode
and a.polno = lc.polno
and a.insuaccno <> '000000';
commit ;

v_count := v_count+10000 ;
end loop;
-- dbms_output.put_line('!!!!!!!!!账户履历表(LCInsureAccTrace)!!!!!!!!');
exception
when others then
p_insert_zbx_v3_logs(i_strdate, 'temp_lcinsureacctrace_v3'||SQLERRM || '|' ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||'');

end p_insert_lcinsureacctrace_v3;

/**
3期新增保全补退费表
*/
--30
PROCEDURE p_insert_ljagetendorse_v3(i_strdate in date) AS

begin
insert into temp_LJAGetEndorse_v3
select distinct tmp.busino as TransactionNo,
'000103' as CompanyCode,
a.actugetno as ActuGetNo,
a.endorsementno as EndorsementNo,
changecode_v2(a.feeoperationtype,'FeeOperationType','99') as FeeOperationType,
changecode_v2(substr(a.subfeeoperationtype,0,4),'SubFeeOperationType','') as SubFeeOperationType,
changecode_v2(a.feefinatype,'FeeFinaType','') as FeeFinaType,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
'000000' as GrpPolicyNo,
'000000' as GrpProductNo,
a.contno as PolicyNo,
a.polno as ProductNo,
a.riskcode as ProductCode,
decode(a.getflag,'0','1','2')as GetType,
nvl(a.getdate,a.getconfirmdate) as GetDate,
a.enteraccdate as EnterAccDate,
a.getconfirmdate as GetConfirmDate,
decode(a.getflag,'0',a.getmoney,-a.getmoney)as GetMoney,
nvl(changecode_v2((select substr(managecom,0,4) from lccont where contno =a.contno),'MANAGE',''),'000103') as ManageCom,

to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from ljagetendorse a, updatetemp_v3 tmp
where a.contno = tmp.contno
and tmp.othernotype='76'
and a.polno = tmp.otherno
and a.getmoney = tmp.transamnt
and a.actugetno = tmp.standbystring1
and a.subfeeoperationtype = tmp.standbystring2
and a.getmoney<>0
and a.GetConfirmDate = i_strdate
;
-- dbms_output.put_line('!!!!!!!!!保全补退费表(LJAGetEndorse)!!!!!!!!');
commit;
end p_insert_ljagetendorse_v3;

/**
3期新增客户回访表
*/
--31
PROCEDURE p_insert_lccusrecall_v3(i_strdate in date) AS

begin
insert into temp_LCCustomerReturnCall_v3
select tmp.busino as TransactionNo,
'000103' as CompanyCode,
a.policy_code as PolicyNo,
'1'||substr(tmp.busino,18,7) as SerialNo,--无8位序列号，截取交易号
nvl(changecode_v2(a.single_code, 'SaleChnl', ''),'999') as DistribChnl,
nvl(changecode_v2(substr(a.organ_id,0,4),'MANAGE',''),'000103') as ManageCom,
nvl((select ld.targetcodename from ldcodemapping_v2 ld where ld.basiccode = substr(a.organ_id,0,4) and ld.codetype='MANAGE'),'恒大人寿保险有限公司') as ManageComName,
nvl(a.ph_id,lc.appntno) as AppntNo,
nvl(a.ph_name,lc.appntname) as AppntName,
decode(a.ph_gender,'男','1','女','2','9')as AppntSex,
a.ph_birthday as AppntBirthDate,
changecode_v2((select l.appntidtype from lccont l where l.contno = a.policy_code), 'CertType', '')as AppntCertType,
a.ph_certi_code as AppntCertNo,
a.ph_mobile as AppntMP,
a.ph_home_tel as AppntFP,
a.ph_email as EMail,
decode(a.hf_flag,'0','1','0')as CallReturnFlag,
nvl(nvl(a.ph_mobile,(select nvl(lad.mobile,lad.phone) from lcaddress lad where lad.customerno = a.ph_id and (lad.mobile is not null or lad.phone is not null) and rownum=1)),a.ph_home_tel) as ReturnCall,--回访电话 字段取值困难 无值？电话回访非空
nvl(a.agent_code,lc.agentcode) as AgentCode,
a.agent_mobile as AgentMobile,
lc.polapplydate as PolApplyDate,
lc.signdate as SignDate,
lc.cvalidate as EffDate,
a.received_date as CustomGetPolDate,
nvl(to_date(to_char(a.hf_lastcall_date,'yyyymmdd'),'yyyy-mm-dd'),a.hf_date) as RtrCallSuccDate,
a.hesitateperiod as HesitateDays,
nvl(a.hesitateperiod_end_date,a.validate_date+15 ) as HesitateExpirationDate,
decode((select 1 from dual where a.hf_callend_date< nvl(a.hesitateperiod_end_date,a.validate_date+15 )),'1','1','0')as HesitatePeriodFlag,
decode(a.hf_result,'21','1','0') as RtrCallSuccFlag,--回访结果代码？非空
'0' as ReturnCallType,--无字段，取值？非空
'' as Province,
'' as City,
'' as County,
a.ph_address as Street,
'' as HomeProvince,
'' as HomeCity,
'' as HomeCounty,
a.address as HomeStreet,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate,
'01' as GPFlag
from t_cc_visit a, updatetemp_v3 tmp ,lccont lc
where a.policy_code = tmp.contno
and tmp.othernotype ='73' and a.policy_code = lc.contno
and a.hf_date = tmp.transdate
and exists (select 1 from lcpol lp where lp.contno = lc.contno and lp.riskcode = a.internal_id and lp.mainpolno = lp.polno and (lp.mainriskflag is null or lp.mainriskflag='1'))
and a.received_date is not null
;
-- dbms_output.put_line('!!!!!!!!!客户回访表(t_cc_visit)!!!!!!!!');
commit;
exception
when others then
p_insert_zbx_v3_logs(i_strdate, 'temp_LCCustomerReturnCall_v3'||SQLERRM || '|' ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||'');
EXECUTE IMMEDIATE 'truncate table temp_lcpoltransaction_v3' ;
commit;

end p_insert_lccusrecall_v3;

/**
3期新增再保产品信息表
*/
--32
PROCEDURE p_insert_lrproduct_v3(i_strdate in date) AS
v_count number;
begin
select count(1)+1 into v_count from updatetemp_v3 ;

insert into temp_LRProduct_v3
select
'000103' ||
SUBSTR(to_char(i_strdate, 'yyyy-mm-dd'), 1, 4) ||
SUBSTR(to_char(i_strdate, 'yyyy-mm-dd'), 6, 2) ||
substr(to_char(i_strdate, 'yyyy-mm-dd'), 9, 2) ||
lpad(rownum+v_count, 10, '0') as TransactionNo, --交易编码
CompanyCode,
ReInsuranceContNo,
ReInsuranceContName,
ReInsuranceContTitle,
MainReInsuranceContNo,
ContOrAmendmentType,
ProductCode,
ProductName,
GPFlag,
ProductType,
LiabilityCode,
LiabilityName,
ReinsurerCode,
ReinsurerName,
ReinsuranceShare,
ReinsurMode,
ReInsuranceType,
TermType,
RetentionAmount,
RetentionPercentage,
QuotaSharePercentage,
pushdate
from (
SELECT
'000103' AS CompanyCode, --保险机构代码
RLD.TREATY_NO ReInsuranceContNo, --再保险合同号码
RTI.TREATY_DESC ReInsuranceContName, --再保险合同名称
RTI.TREATY_DESC ReInsuranceContTitle, --再保险合同简称
RTI.MAIN_TREATY_NO MainReInsuranceContNo, --再保险附约主合同号
DECODE(RLD.TREATY_NO, RTI.MAIN_TREATY_NO, '1', '2') ContOrAmendmentType, --1 合同；2 附约
RLD.RISK_CODE ProductCode, --产品编码
(SELECT LCP.RISKNAME
FROM LMRISKAPP LCP
WHERE RLD.RISK_CODE = LCP.RISKCODE) ProductName, --产品名称
'01' AS GPFlag, --保单团个性质代码(01 个险；02团体；99 其它)
(select lm.producttype from lmproduct lm where lm.riskcode = RLD.Risk_Code) AS ProductType, --险类代码
RLD.RIS_LIABILITY_CODE LiabilityCode, --责任代码
(SELECT BASE_TABLE_DESC
FROM RIS_BASE_TBL
WHERE BASE_TABLE_TYPE = 'RIS_LIABILITY_CODE'
AND BASE_TABLE_CODE = RLD.RIS_LIABILITY_CODE) LiabilityName, --责任名称
changecode_v2(RLD.COMPANY_CODE, 'COMPANYCODE', '') ReinsurerCode, --再保险公司代码
RCI.COMPANY_NAME ReinsurerName, --再保险公司名称
RLD.AUTO_ACCEPT_SHARED ReinsuranceShare, --再保人参与份额比例
DECODE(RLD.CEDE_TYPE, '01', '1', '02', '1', '03', '2', '04', '2', '1') ReinsurMode, --分保方式( 1溢额、2成数、3成数溢额混合、4超赔)
/*（01 事故超赔；02 修正共保方式；03 共保方式；
04 风险保费方式；05 赔付率超赔；06 损失终止；07 险位超赔 )*/
'03' AS ReInsuranceType, --再保类型
(SELECT DISTINCT DECODE(A.RISKPERIOD,
'L',
'10',
'M',
'21',
'S',
'22','10')
FROM LMRISKAPP A
WHERE A.RISKCODE = RLD.RISK_CODE
) TermType, --保险期限类型(10 长期险 21 短期)
nvl(RLD.RETENTION_INS,0) RetentionAmount, --自留额
nvl(RLD.RETENTION_RATIO,0) RetentionPercentage, --自留比例
nvl((SELECT (1 - LLD.RETENTION_RATIO)
FROM RIS_LIABILITY_DEFINE LLD
WHERE LLD.CEDE_TYPE IN ('03', '04')
AND lld.risk_code = rld.risk_code
AND lld.ris_liability_code = rld.ris_liability_code
AND lld.company_code = rld.company_code
AND lld.treaty_no = rld.treaty_no
AND lld.reins_plan = rld.reins_plan
AND to_char(lld.create_date,'yyyy-mm-dd') <> '2018-08-15' and rownum = 1),0) QuotaSharePercentage, --分保比例
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') pushdate
FROM RIS_LIABILITY_DEFINE RLD, RIS_TREATY_INFO RTI, RIS_COMPANY_INFO RCI
WHERE RTI.TREATY_NO = RLD.TREATY_NO AND RLD.DATA_SOURCE='I' --个险
AND RLD.COMPANY_CODE = RCI.COMPANY_CODE
AND to_char(RLD.create_date,'yyyy-mm-dd') <> '2018-08-15'
) tq where ProductName is not null and ProductType is not null
;
-- dbms_output.put_line('!!!!!!!!!再保产品信息表(RIS_LIABILITY_DEFINE)!!!!!!!!');
commit;
insert into temp_lcpoltransaction_v3
select b.busino as BUSINO,
-- '0000000' as BatchNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
'' as PolicyNo,
'01' as GPFlag,
'47' as BussType,
'999' as TransTypeCorrBussType,
0 as TransAmnt,
i_strdate as TransDate,
'' as BankCode,
'' as BankName,
'' as BankAccNo,
'' as AccName,
'' as CertType,
'' as CertNo,
'' as ClaimNo,
'' as EndorAcceptNo,
'' as EndorsementNo,
null as EndorsementApplicationDate,
'' as ConsumerInitiatedFlag,
'' as PreserveAppCode,
'' as DataField1,
'' as DataField2,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from temp_LRProduct_v3 b ;
commit;

end p_insert_lrproduct_v3;

/**
3期新增再保合同信息表
*/
--33
PROCEDURE p_insert_LRInsureCont_v3(i_strdate in date) AS
v_count number ;
v_sum number ;
begin
select count(1)+1 into v_count from updatetemp_v3 ;
select count(1)+1 into v_sum from temp_lrproduct_v3 ;
v_sum := v_sum + v_count ;

insert into temp_LRInsureCont_v3
select
'000103' ||
SUBSTR(to_char(i_strdate, 'yyyy-mm-dd'), 1, 4) ||
SUBSTR(to_char(i_strdate, 'yyyy-mm-dd'), 6, 2) ||
substr(to_char(i_strdate, 'yyyy-mm-dd'), 9, 2) ||
lpad(rownum+v_sum, 10, '0') as TransactionNo, --交易编码
CompanyCode,
ReInsuranceContNo,
ReInsuranceContName,
ReInsuranceContTitle,
MainReInsuranceContNo,
ContOrAmendmentType,
ContAttribute,
ContStatus,
TreatyOrFacultativeFlag,
ContSigndate,
PeriodFrom,
PeriodTo,
ContType,
ReinsurerCode,
ReinsurerName,
ChargeType ,
pushdate,
GPFlag
from ( SELECT
'000103' AS CompanyCode, --保险机构代码(000103 恒大人寿保险有限公司)
rti.treaty_no ReInsuranceContNo,--再保险合同号码
rti.treaty_desc ReInsuranceContName,--再保险合同名称
rti.treaty_desc ReInsuranceContTitle,--再保险合同简称
rti.main_treaty_no MainReInsuranceContNo,--再保险附约主合同号
DECODE(rti.TREATY_NO, RTI.MAIN_TREATY_NO, '1', '2') ContOrAmendmentType, --合同附约类型 1 合同；2 附约
'1' AS ContAttribute,--合同属性(1 保险合同;2 混合合同;3 非保险合同)
decode(rti.treaty_status,'1','1','2','2','1') ContStatus,--合同状态(1 有效；2 终止)
'0' AS TreatyOrFacultativeFlag,--合同/临分标志 （临分标志0 否1 是）
nvl(rti.sign_date,rti.treaty_start_date) ContSigndate,--合同签署日期
rti.treaty_start_date PeriodFrom,--合同生效起期
rti.treaty_end_date PeriodTo,--合同生效止期
'1' AS ContType,--合同类型(1 比例合同;2 非比例合同)
changecode_v2(rti.COMPANY_CODE, 'COMPANYCODE', '') ReinsurerCode,--再保险公司代码
rci.company_name ReinsurerName,--再保险公司名称
'1' AS ChargeType ,--佣金核算方式(1 业务年度;2 财务年度)
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') pushdate,
'01' as GPFlag
FROM ris_treaty_info rti,ris_company_info rci
WHERE rti.company_code = rci.company_code
and rti.treaty_start_date is not null
);

-- dbms_output.put_line('!!!!!!!!!再保合同信息表(ris_treaty_info)!!!!!!!!');
commit;

insert into temp_lcpoltransaction_v3
select b.busino as BUSINO,
-- '0000000' as BatchNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
'' as PolicyNo,
'01' as GPFlag,
'47' as BussType,
'999' as TransTypeCorrBussType,
0 as TransAmnt,
i_strdate as TransDate,
'' as BankCode,
'' as BankName,
'' as BankAccNo,
'' as AccName,
'' as CertType,
'' as CertNo,
'' as ClaimNo,
'' as EndorAcceptNo,
'' as EndorsementNo,
null as EndorsementApplicationDate,
'' as ConsumerInitiatedFlag,
'' as PreserveAppCode,
'' as DataField1,
'' as DataField2,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from temp_LRInsureCont_v3 b ;
commit;
end p_insert_LRInsureCont_v3;

/**
3期新增再保账单信息表
*/
--34
PROCEDURE p_insert_LRAccount_v3(i_strdate in date) AS

begin
insert into temp_LRAccount_v3
SELECT '000000' AS TransactionNo,--交易编码
'000103' AS CompanyCode, --保险机构代码(000103 恒大人寿保险有限公司)
rbi.bill_no AccountID,--账单编号
rbi.create_date AS AccountingPeriodfrom,--账单起期
rbi.arrival_date AS AccountingPeriodto,--账单止期
changecode_v2(rbi.COMPANY_CODE, 'COMPANYCODE', '') as ReinsurerCode,--再保险公司代码
rci.company_name ReinsurerName,--再保险公司名称
rbi.treaty_no ReInsuranceContNo,--再保险合同号码
rti.treaty_desc ReInsuranceContName,--再保险合同名称
'156' AS Currency,--货币代码
nvl(rbi.cede_prem_tax,0)+NVL(rbi.cede_prem_no_tax,0) AS ReinsurancePremium,--分保费
0 AS ReinsuranceCommssionRate,--分保佣金率
0 AS ReinsuranceCommssion,--分保佣金
rbi.cede_commission ReturnReinsurancePremium,--退回分保费
0 AS ReturnReinsuranceCommssion,--退回分保佣金
0 AS ReturnSurrenderPay,--摊回退保金
rbi.recovered_amt ReturnClaimPay,--摊回理赔款
0 AS ReturnMaturity,--摊回满期金
0 AS ReturnAnnuity,--摊回年金
0 AS ReturnLivBene,--摊回生存金
'1' AS AccountStatus,--账单状态(1 有效 2无效)
'' AS PairingStatus,--结算状态
'' AS PairingDate,--结算日期
0 AS CurrentRate ,--结算汇率
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') pushdate,
'01' as GPFlag
FROM ris_bill_info rbi,ris_company_info rci,ris_treaty_info rti
WHERE rbi.company_code = rci.company_code
AND rbi.treaty_no = rti.treaty_no;
-- dbms_output.put_line('!!!!!!!!!再保账单信息表(ris_bill_info)!!!!!!!!');
commit;
end p_insert_LRAccount_v3;

/**
3期新增再保首续期险种明细表
*/
--35
PROCEDURE p_insert_LRCont_v3(i_strdate in date) AS

begin
insert into temp_LRCont_v3
SELECT UV.BUSINO AS TransactionNo, --交易编码
'000103' AS CompanyCode, --保险机构代码 (000103 恒大人寿保险有限公司)
RRM.GRP_POLICY_NO GrpPolicyNo, --团体保单号
DECODE(RRM.GRP_POLICY_NO, '', '', RRM.RISK_CODE) GrpProductNo, --团体保单险种号码
RRM.POLICY_NO PolicyNo, --个人保单号
(select max(polno) from lcpol where contno = rrm.policy_no and riskcode like ''||rrm.risk_code||'%' ) ProductNo, --个单保险险种号码
DECODE(RRM.DATA_SOURCE, 'I', '01', 'G', '02', '99') GPFlag, --保单团个性质代码(01 个险；02团体；99 其它)
(SELECT max(polno)
FROM LCPOL L
WHERE L.CONTNO = RRM.POLICY_NO
AND L.POLNO = L.MAINPOLNO
and (l.mainriskflag is null or l.mainriskflag='1')) AS MainProductNo, --主险保险险种号码
DECODE((SELECT COUNT(1)
FROM LCPOL LP
WHERE LP.CONTNO = RRM.POLICY_NO
AND LP.POLNO = LP.MAINPOLNO
AND LP.RISKCODE like ''||rrm.risk_code||'%'
/*AND lp.insuredno=(SELECT DISTINCT p.customerno
FROM LDPERSON p
WHERE p.NAME = RRM.INSURED_NAME
AND p.IDNO = RRM.INSURED_IDNO)*/
),
0,
'2',
'1') AS MainProductFlag, --主附险性质代码（1 主险；2附加险；3不区分）
RRM.RISK_CODE ProductCode, --产品编码
RRM.RIS_LIABILITY_CODE LiabilityCode, --责任代码
(SELECT BASE_TABLE_DESC
FROM RIS_BASE_TBL
WHERE BASE_TABLE_TYPE = 'RIS_LIABILITY_CODE'
AND BASE_TABLE_CODE = RRM.RIS_LIABILITY_CODE) LiabilityName, --责任名称
/*0100 身故;0200 伤残;0300 重大疾病;0400 高残;0500 全残;0600 失能
0700 医疗;0701 医疗费用-住院;0702 医疗费用-门诊;0703 医疗费用-生育
0704 医疗费用-体检;0705 预防接种;0706 定额给付;0707 医疗费用-住院前后门诊
0708 医疗费用-特定门诊;0709 医疗费用-慢性病门诊;0710 医疗费用-牙科
0711 医疗费用-眼科;0799 医疗费用-其他;0800 失业;0900 养老金
1000 满期保险金;1100 生存保险金;1200 护理;1300 特定疾病;9900 其他*/
changecode_v2(RRM.RIS_LIABILITY_CODE,'RISLIABILITYCODE','9900') AS Classification, --责任分类代码
CASE
--新契约
WHEN (SELECT 1
FROM RIS_INITIAL_POLICY RI
WHERE RI.RISK_CHANGE_REASON = '01'
AND RI.POL_YEAR = 1 and RI.Data_Source='I'
AND RI.POLICY_NO = RRM.POLICY_NO
AND RI.PROC_MONTH = RRM.PROC_MONTH
AND RI.INSURED_NAME = RRM.INSURED_NAME
AND RI.INSURED_IDNO = RRM.INSURED_IDNO
AND RI.RISK_CODE = RRM.RISK_CODE) = 1 THEN
'01'

--长期险续期保单
WHEN (SELECT 1
FROM RIS_INITIAL_POLICY RI
WHERE RI.POL_YEAR > 1 and RI.Data_Source='I'
AND RI.COVER_PERIOD > 1
AND RI.RISK_CHANGE_REASON = '01'
AND RI.POLICY_NO = RRM.POLICY_NO
AND RI.PROC_MONTH = RRM.PROC_MONTH
AND RI.INSURED_NAME = RRM.INSURED_NAME
AND RI.INSURED_IDNO = RRM.INSURED_IDNO
AND RI.RISK_CODE = RRM.RISK_CODE) = 1 THEN
'02'
--短期险续保
WHEN (SELECT 1
FROM RIS_INITIAL_POLICY RI
WHERE RI.POL_YEAR > 1 and RI.Data_Source='I'
AND RI.COVER_PERIOD = 1
AND RI.RISK_CHANGE_REASON = '01'
AND RI.POLICY_NO = RRM.POLICY_NO
AND RI.PROC_MONTH = RRM.PROC_MONTH
AND RI.INSURED_NAME = RRM.INSURED_NAME
AND RI.INSURED_IDNO = RRM.INSURED_IDNO
AND RI.RISK_CODE = RRM.RISK_CODE) = 1 THEN
'03' else '02'
END AS EventType, --业务类型（01 新单；02续期；03续保）
RRM.POL_YEAR - 1 AS RenewalTimes, --续期续保次数
(SELECT DISTINCT DECODE(A.RISKPERIOD,
'L',
'10',
'M',
'21',
'S',
'22','10')
FROM LMRISKAPP A
WHERE A.RISKCODE = RRM.RISK_CODE
) TermType, --保险期限类型(10 长期险 21 短期)
nvl(changecode_v2((select substr(managecom,0,4) from lccont where contno =rrm.policy_no),'MANAGE',''),'000103') AS ManageCom, --管理机构代码( 000103440000恒大人寿保险有限公司广东分公司)
(select signdate from lccont where contno=uv.contno) SignDate, --签单日期
(select cvalidate from lccont where contno=uv.contno) EffDate, --保险责任生效日期
RRM.POL_YEAR PolYear, --保单年度
(select max(lc.payenddate) from lcpol lc where lc.contno = uv.contno and lc.riskcode like ''||rrm.risk_code||'%') InvalidDate, --保险责任终止日期
(SELECT changecode_v2(lc.UWFlag, 'UWConclusion', '')
FROM LCCONT LC
WHERE LC.CONTNO = RRM.POLICY_NO) UWCONCLUSION , --核保结论代码
(select DECODE(APPFLAG,'1','01','4','03','0','04','99') from LCCONT where contno = RRM.POLICY_NO) PolStatus, --保单状态代码(01 有效 02中止 99其它)
DECODE(RRM.RISK_CHANGE_REASON, '01', '01', '02', '02', '03','01','03') Status, --保单险种状态代码(01 有效 02中止 99其它)
RRM.BASE_SUM_INS BasicSumInsured, --基本保额
nvl(RRM.CEDE_RISK_SUMINS,0) RiskAmnt, --风险保额
RRM.REINS_PREM Premium, --保费
nvl((SELECT RIP.CASH_VALUE
FROM RIS_INITIAL_POLICY RIP
WHERE RIP.PROC_MONTH = RRM.PROC_MONTH and RIP.Data_Source='I'
AND RIP.POLICY_NO = RRM.POLICY_NO
AND RIP.RISK_CODE = RRM.RISK_CODE
AND RIP.INSURED_IDNO = RRM.INSURED_IDNO
AND RIP.INSURED_NAME = RRM.INSURED_NAME),0) AccountValue, --保险账户价值
DECODE(RRM.RENEWAL_FLAG, '2', '1', '0') FacultativeFlag, --临分标记
'0' AS AnonymousFlag, --无名单标志
decode((select risktype7 from lmriskapp where riskcode = RRM.Risk_Code),'0','0','1') AS WaiverFlag, --豁免险标志
0 AS WaiverPrem, --所需豁免剩余保费
nvl(RRM.RESERVE,0) FinalCashValue, --期末现金价值
nvl(RRM.RESERVE,0) FinalLiabilityReserve, --期末责任准备金
nvl(nvl((SELECT L3.INSUREDNO
FROM LCINSURED L3
WHERE L3.IDNO = RRM.INSURED_IDNO
AND L3.CONTNO = RRM.POLICY_NO and rownum=1),
(select ld.customerno from ldperson ld
where ld.name = rrm.insured_name
and ld.idno = rrm.insured_idno and rownum=1)),(select insuredno from lcinsured where contno =rrm.policy_no and rownum=1)) InsuredNo, --被保人客户号
RRM.INSURED_NAME InsuredName, --被保人姓名
DECODE(RRM.INSURED_SEX, '0', '1', '1', '2', '9') InsuredSex, --被保人性别(1 男性； 2女性； 9 未说明的性别)
/*(111 居民身份证;113 户口簿;114中国人民解放军军官证(包含军官证、士兵证)
117 出生医学证明;414 普通护照;990 其他
*/
nvl((SELECT changecode_v2(L2.IDTYPE, 'CertType', '')
FROM LDPERSON L2
WHERE RRM.INSURED_NAME = L2.NAME
AND L2.IDNO = RRM.INSURED_IDNO and rownum =1),'990')
InsuredCertType, --被保人证件类型
RRM.INSURED_IDNO InsuredCertNo, --被保人证件编码
changecode_v2((SELECT L.OCCUPATIONCODE
FROM LCINSURED L
WHERE L.NAME = RRM.INSURED_NAME
AND L.IDNO = RRM.INSURED_IDNO
AND L.CONTNO = RRM.POLICY_NO AND rownum =1),'OccupationType','') AS OccupationType, --被保人职业代码（待转换码表）
nvl((select min(lc.insuredappage) from lcpol lc where contno = RRM.Policy_No and riskcode = RRM.Risk_Code),rrm.insured_age) AppntAge, --投保年龄 2019-7-29 取险种投保年龄
(case when RRM.CURRENT_AGE<RRM.INSURED_AGE THEN (SELECT TRUNC(months_between(UV.TRANSDATE,RRM.INSURED_BIRTH)/12)||'' FROM DUAL) ELSE RRM.CURRENT_AGE END) PreAge, --当前年龄
nvl((SELECT RIP.OCCU_ADD_PREM
FROM RIS_INITIAL_POLICY RIP
WHERE RIP.PROC_MONTH = RRM.PROC_MONTH and RIP.Data_Source='I'
AND RIP.POLICY_NO = RRM.POLICY_NO
AND RIP.RISK_CODE = RRM.RISK_CODE
AND RIP.INSURED_IDNO = RRM.INSURED_IDNO
AND RIP.INSURED_NAME = RRM.INSURED_NAME),0) ProfessionalFee, --职业加费金额
nvl((SELECT RIP.UNSTANDARD_ADD_PREM
FROM RIS_INITIAL_POLICY RIP
WHERE RIP.PROC_MONTH = RRM.PROC_MONTH and RIP.Data_Source='I'
AND RIP.POLICY_NO = RRM.POLICY_NO
AND RIP.RISK_CODE = RRM.RISK_CODE
AND RIP.INSURED_IDNO = RRM.INSURED_IDNO
AND RIP.INSURED_NAME = RRM.INSURED_NAME),0) SubStandardFee, --次标准体加费金额
nvl(RRM.EM_VALUE,0) EMRate, --EM加点
'0' AS ProjectFlag, --建工险标志
'1' AS InsurePeoples, --被保人数
'1' AS SaparateFlag, --分出标记
RRM.TREATY_NO ReInsuranceContNo, --再保险合同号码
changecode_v2(RRM.COMPANY_CODE, 'COMPANYCODE', '') ReinsurerCode, --再保险公司代码
(select ldc.targetcodename from ldcodemapping_v2 ldc where ldc.codetype='COMPANYCODE' and trim(ldc.basiccode) =RRM.COMPANY_CODE) ReinsurerName, --再保险公司名称
CASE
WHEN (SELECT RLD.CEDE_TYPE
FROM RIS_LIABILITY_DEFINE RLD
WHERE RLD.RISK_CODE = RRM.RISK_CODE AND RLD.Data_Source='I'
AND RLD.TREATY_NO = RRM.TREATY_NO
AND RLD.REINS_PLAN = RRM.REINS_PLAN
AND RLD.COMPANY_CODE = RRM.COMPANY_CODE
AND RLD.RIS_LIABILITY_CODE = RRM.RIS_LIABILITY_CODE
AND to_char(RLD.create_date,'yyyy-mm-dd') <> '2018-08-15' and rownum = 1) IN
('01', '02') THEN
'1'
WHEN (SELECT RLD.CEDE_TYPE
FROM RIS_LIABILITY_DEFINE RLD
WHERE RLD.RISK_CODE = RRM.RISK_CODE AND RLD.Data_Source='I'
AND RLD.TREATY_NO = RRM.TREATY_NO
AND RLD.REINS_PLAN = RRM.REINS_PLAN
AND RLD.COMPANY_CODE = RRM.COMPANY_CODE
AND RLD.RIS_LIABILITY_CODE = RRM.RIS_LIABILITY_CODE
AND to_char(RLD.create_date,'yyyy-mm-dd') <> '2018-08-15' and rownum = 1) IN
('03', '04') THEN
'2' else '3'
END AS ReinsurMode, --分保方式
nvl(RRM.CEDE_RISK_SUMINS,0) ReinsuranceAmnt, --分保保额
nvl(RRM.RETENTION,0) RetentionAmount, --自留额
'156' AS Currency, --货币代码
nvl(RRM.CEDE_RATIO,0) QuotaSharePercentage, --分保比例
nvl(RRM.REINS_PREM,0) ReinsurancePremium, --分保费
nvl(RRM.COMMISSION_RATE,0) ReinsuranceCommssion, --分保佣金
TO_DATE(RRM.PROC_MONTH|| '01', 'yyyy-mm-dd') AS ReComputationsDate, --分保计算日期
CASE
WHEN (SELECT TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'Q')
FROM DUAL) = 1 THEN
TO_DATE(substr(RRM.PROC_MONTH,0,4)|| '-04-15', 'yyyy-mm-dd')
WHEN (SELECT TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'Q')
FROM DUAL) = 2 THEN
TO_DATE(substr(RRM.PROC_MONTH,0,4)|| '-07-15', 'yyyy-mm-dd')
WHEN (SELECT TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'Q')
FROM DUAL) = 3 THEN
TO_DATE(substr(RRM.PROC_MONTH,0,4)|| '-10-15', 'yyyy-mm-dd')
WHEN (SELECT TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'Q')
FROM DUAL) = 4 THEN
to_date(TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'yyyy') + 1 ||
'-01-15','yyyy-mm-dd')
END AS AccountGetDate ,--账单归属日期
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') pushdate
FROM RIS_REINSURANCE_MONTH RRM,
RIS_COMPANY_INFO RCI,
RIS_TREATY_INFO RTI,
UPDATETEMP_v3 UV
WHERE RRM.COMPANY_CODE = RCI.COMPANY_CODE and RRM.DATA_SOURCE='I'
AND RRM.TREATY_NO = RTI.TREATY_NO
AND RRM.BUSINESS_TYPE IN ('01','02', '03')
AND UV.CONTNO = RRM.POLICY_NO
and uv.otherno = rrm.reins_seq
AND TO_CHAR(UV.TRANSDATE, 'yyyy-mm-dd') =
TO_CHAR(RRM.CREATE_DATE, 'yyyy-mm-dd')
AND UV.OTHERNOTYPE = '72';
-- dbms_output.put_line('!!!!!!!!!再保首续期险种明细表(RIS_REINSURANCE_MONTH)!!!!!!!!');
commit;
end p_insert_LRCont_v3;

/**
3期新增再保保全变更信息表
*/
--36
PROCEDURE p_insert_LREdor_v3(i_strdate in date) AS

begin
insert into temp_LREdor_v3
SELECT UV.BUSINO AS TransactionNo, --交易编码
'000103' AS CompanyCode, --保险机构代码 (000103 恒大人寿保险有限公司)
RRM.GRP_POLICY_NO GrpPolicyNo, --团体保单号
DECODE(RRM.GRP_POLICY_NO, '', '', RRM.RISK_CODE) GrpProductNo, --团体保单险种号码
RRM.POLICY_NO PolicyNo, --个人保单号
(select max(polno) from lcpol where contno = rrm.policy_no and riskcode like ''||rrm.risk_code||'%' ) ProductNo, --个单保险险种号码
DECODE(RRM.DATA_SOURCE, 'I', '01', 'G', '02', '99') GPFlag, --保单团个性质代码
(SELECT max(polno)
FROM LCPOL L
WHERE L.CONTNO = RRM.POLICY_NO
AND L.POLNO = L.MAINPOLNO
and (l.mainriskflag is null or l.mainriskflag='1')) AS MainProductNo, --主险保险险种号码
DECODE((SELECT COUNT(1)
FROM LCPOL L
WHERE L.CONTNO = RRM.POLICY_NO
AND L.POLNO = L.MAINPOLNO
AND L.RISKCODE = RRM.RISK_CODE
),
0,
'2',
'1') AS MainProductFlag, --主附险性质代码（1 主险；2附加险；3不区分）
RRM.RISK_CODE ProductCode, --产品编码
RRM.RIS_LIABILITY_CODE LiabilityCode, --责任代码
(SELECT BASE_TABLE_DESC
FROM RIS_BASE_TBL
WHERE BASE_TABLE_TYPE = 'RIS_LIABILITY_CODE'
AND BASE_TABLE_CODE = RRM.RIS_LIABILITY_CODE) LiabilityName, --责任名称
/*0100 身故;0200 伤残;0300 重大疾病;0400 高残;0500 全残;0600 失能
0700 医疗;0701 医疗费用-住院;0702 医疗费用-门诊;0703 医疗费用-生育
0704 医疗费用-体检;0705 预防接种;0706 定额给付;0707 医疗费用-住院前后门诊
0708 医疗费用-特定门诊;0709 医疗费用-慢性病门诊;0710 医疗费用-牙科
0711 医疗费用-眼科;0799 医疗费用-其他;0800 失业;0900 养老金
1000 满期保险金;1100 生存保险金;1200 护理;1300 特定疾病;9900 其他*/
changecode_v2(RRM.RIS_LIABILITY_CODE,'RISLIABILITYCODE','9900') AS Classification, --责任分类代码
(SELECT DISTINCT DECODE(A.RISKPERIOD,
'L',
'10',
'M',
'21',
'S',
'22','10')
FROM LMRISKAPP A
WHERE A.RISKCODE = RRM.RISK_CODE
) TermType, --保险期限类型(10 长期险 21 短期)
nvl(changecode_v2((select substr(managecom,0,4) from lccont where contno =rrm.policy_no),'MANAGE',''),'000103') AS ManageCom, --管理机构代码( 000103440000恒大人寿保险有限公司广东分公司)
(select signdate from lccont where contno=uv.contno) SignDate, --签单日期
(select cvalidate from lccont where contno=uv.contno) EffDate, --保险责任生效日期
RRM.POL_YEAR PolYear, --保单年度
(select max(lc.payenddate) from lcpol lc where lc.contno = uv.contno and lc.riskcode like ''||rrm.risk_code||'%') InvalidDate, --保险责任终止日期
/*核保结论代码(10 标准体; 20 次标准体;21 加费;32 限额;33 特别约定;40 延期;50 拒保;60 优标体*/
(SELECT changecode_v2(lc.UWFlag, 'UWConclusion', '')
FROM LCCONT LC
WHERE LC.CONTNO = RRM.POLICY_NO) UWConclusion,
(select DECODE(APPFLAG,'1','01','4','03','0','04','99') from LCCONT where contno = RRM.POLICY_NO) PolStatus, --保单状态代码(01 有效 02中止 99其它)
DECODE(RRM.RISK_CHANGE_REASON, '01', '01', '02', '02', '03','01','03') Status, --保单险种状态代码(01 有效 02中止 99其它)
RRM.BASE_SUM_INS BasicSumInsured, --基本保额
nvl(RRM.CEDE_RISK_SUMINS,0) RiskAmnt, --风险保额
RRM.REINS_PREM Premium, --保费
nvl((SELECT RIP.CASH_VALUE
FROM RIS_INITIAL_POLICY RIP
WHERE RIP.PROC_MONTH = RRM.PROC_MONTH and RIP.Data_Source='I'
AND RIP.POLICY_NO = RRM.POLICY_NO
AND RIP.RISK_CODE = RRM.RISK_CODE
AND RIP.INSURED_IDNO = RRM.INSURED_IDNO
AND RIP.INSURED_NAME = RRM.INSURED_NAME),0) AccountValue, --保险账户价值
DECODE(RRM.RENEWAL_FLAG, '2', '1', '0') FacultativeFlag, --临分标记
'0' AS AnonymousFlag, --无名单标志
decode((select risktype7 from lmriskapp where riskcode = RRM.Risk_Code),'0','0','1') AS WaiverFlag, --豁免险标志
0 AS WaiverPrem, --所需豁免剩余保费
nvl(RRM.RESERVE,0) FinalCashValue, --期末现金价值
nvl(RRM.RESERVE,0) FinalLiabilityReserve, --期末责任准备金
nvl(nvl((SELECT L3.INSUREDNO
FROM LCINSURED L3
WHERE L3.IDNO = RRM.INSURED_IDNO
AND L3.CONTNO = RRM.POLICY_NO and rownum=1),
(select ld.customerno from ldperson ld
where ld.name = rrm.insured_name
and ld.idno = rrm.insured_idno and rownum=1)),(select insuredno from lcinsured where contno =rrm.policy_no and rownum=1)) InsuredNo, --被保人客户号
RRM.INSURED_NAME InsuredName, --被保人姓名
DECODE(RRM.INSURED_SEX, '0', '1', '1', '2', '9') InsuredSex, --被保人性别(1 男性； 2女性； 9 未说明的性别)
/*(111 居民身份证;113 户口簿;114中国人民解放军军官证(包含军官证、士兵证)
117 出生医学证明;414 普通护照;990 其他
*/
nvl((SELECT changecode_v2(L2.IDTYPE, 'CertType', '')
FROM LDPERSON L2
WHERE RRM.INSURED_NAME = L2.NAME
AND L2.IDNO = RRM.INSURED_IDNO and rownum =1),'990')
InsuredCertType, --被保人证件类型
RRM.INSURED_IDNO InsuredCertNo, --被保人证件编码
changecode_v2((SELECT L.OCCUPATIONCODE
FROM LCINSURED L
WHERE L.NAME = RRM.INSURED_NAME
AND L.IDNO = RRM.INSURED_IDNO
AND L.CONTNO = RRM.POLICY_NO AND rownum =1),'OccupationType','') AS OccupationType, --职业代码
RRM.INSURED_AGE AppntAge, --投保年龄
RRM.CURRENT_AGE PreAge, --当前年龄
nvl((SELECT RIP.OCCU_ADD_PREM
FROM RIS_INITIAL_POLICY RIP
WHERE RIP.PROC_MONTH = RRM.PROC_MONTH and RIP.Data_Source='I'
AND RIP.POLICY_NO = RRM.POLICY_NO
AND RIP.RISK_CODE = RRM.RISK_CODE
AND RIP.INSURED_IDNO = RRM.INSURED_IDNO
AND RIP.INSURED_NAME = RRM.INSURED_NAME),0) ProfessionalFee, --职业加费金额
nvl((SELECT RIP.UNSTANDARD_ADD_PREM
FROM RIS_INITIAL_POLICY RIP
WHERE RIP.PROC_MONTH = RRM.PROC_MONTH and RIP.Data_Source='I'
AND RIP.POLICY_NO = RRM.POLICY_NO
AND RIP.RISK_CODE = RRM.RISK_CODE
AND RIP.INSURED_IDNO = RRM.INSURED_IDNO
AND RIP.INSURED_NAME = RRM.INSURED_NAME),0) SubStandardFee, --次标准体加费金额
nvl(RRM.EM_VALUE,0) EMRate, --EM加点
'0' AS ProjectFlag, --建工险标志
'1' AS InsurePeoples, --投保总人数
(SELECT lp.edoracceptno FROM lpedoritem lp
WHERE lp.contno=rrm.policy_no
AND lp.edortype = (CASE WHEN rrm.risk_change_reason='03' THEN 'RE'
WHEN rrm.risk_change_reason='04' THEN 'WT'
WHEN rrm.risk_change_reason='05' THEN 'CT' END)
AND ROWNUM=1
) AS EndorAcceptNo, --保全受理号码
(SELECT lp.edorno FROM lpedoritem lp
WHERE lp.contno=rrm.policy_no
AND lp.edortype = (CASE WHEN rrm.risk_change_reason='03' THEN 'RE'
WHEN rrm.risk_change_reason='04' THEN 'WT'
WHEN rrm.risk_change_reason='05' THEN 'CT' END)
AND ROWNUM=1
) AS EndorsementNo, --保全批单号码
(SELECT decode(lp.edortype,'RE','08','WT','32','31') FROM lpedoritem lp
WHERE lp.contno=rrm.policy_no
AND lp.edortype = (CASE WHEN rrm.risk_change_reason='03' THEN 'RE'
WHEN rrm.risk_change_reason='04' THEN 'WT'
WHEN rrm.risk_change_reason='05' THEN 'CT' END)
AND ROWNUM=1
) AS EdorType, --保全项目类型
(SELECT lp.edorvalidate FROM lpedoritem lp
WHERE lp.contno=rrm.policy_no
AND lp.edortype = (CASE WHEN rrm.risk_change_reason='03' THEN 'RE'
WHEN rrm.risk_change_reason='04' THEN 'WT'
WHEN rrm.risk_change_reason='05' THEN 'CT' END)
AND ROWNUM=1
) AS EdorValiDate, --保全生效日期
(SELECT nvl(lpe.confdate,lp.edorvalidate) FROM lpedoritem lp,lpedorapp lpe
WHERE lp.contno=rrm.policy_no
AND lp.edortype = (CASE WHEN rrm.risk_change_reason='03' THEN 'RE'
WHEN rrm.risk_change_reason='04' THEN 'WT'
WHEN rrm.risk_change_reason='05' THEN 'CT' END)
and lp.edoracceptno = lpe.edoracceptno
AND ROWNUM=1
) AS EDORCONFDATE , -- 保全确认日期
0 AS EDORMONEY ,
'1' AS SaparateFlag, --分出标记
nvl(RRM.TREATY_NO,'0') ReInsuranceContNo, --再保险合同号码
changecode_v2(rrm.COMPANY_CODE, 'COMPANYCODE', '') as ReinsurerCode, --再保险公司代码
(select ldc.targetcodename from ldcodemapping_v2 ldc where ldc.codetype='COMPANYCODE' and trim(ldc.basiccode) =RRM.COMPANY_CODE) ReinsurerName, --再保险公司名称
CASE
WHEN (SELECT RLD.CEDE_TYPE
FROM RIS_LIABILITY_DEFINE RLD
WHERE RLD.RISK_CODE = RRM.RISK_CODE AND RLD.Data_Source='I'
AND RLD.TREATY_NO = RRM.TREATY_NO
AND RLD.REINS_PLAN = RRM.REINS_PLAN
AND RLD.COMPANY_CODE = RRM.COMPANY_CODE
AND RLD.RIS_LIABILITY_CODE = RRM.RIS_LIABILITY_CODE
AND to_char(RLD.create_date,'yyyy-mm-dd') <> '2018-08-15' and rownum = 1) IN
('01', '02') THEN
'1'
WHEN (SELECT RLD.CEDE_TYPE
FROM RIS_LIABILITY_DEFINE RLD
WHERE RLD.RISK_CODE = RRM.RISK_CODE AND RLD.Data_Source='I'
AND RLD.TREATY_NO = RRM.TREATY_NO
AND RLD.REINS_PLAN = RRM.REINS_PLAN
AND RLD.COMPANY_CODE = RRM.COMPANY_CODE
AND RLD.RIS_LIABILITY_CODE = RRM.RIS_LIABILITY_CODE
AND to_char(RLD.create_date,'yyyy-mm-dd') <> '2018-08-15' and rownum = 1) IN
('03', '04') THEN
'2' else '3'
END AS ReinsurMode, --分保方式(1 溢额; 2 成数)
nvl(RRM.CEDE_RATIO,0) QuotaSharePercentage, --分保比例
RRM.INSURED_AGE AS PreInsuredAge, --变更前被保人投保年龄
RRM.BASE_SUM_INS AS PreBasicSumInsured, --变更前基本保额
nvl(RRM.CEDE_RISK_SUMINS,0) AS PreRiskAmnt, --变更前风险保额
RRM.BASE_SUM_INS AS PreReinsuranceAmnt, --变更前分保保额
nvl(RRM.RETENTION,0) AS PreRetentionAmount, --变更前自留额
RRM.STANDARD_PREM AS PrePremium, --变更前保费
nvl(RRM.RESERVE,0) AS PreAccountValue, --变更前账户价值
0 AS PreWaiverPrem, --变更前所需豁免剩余保费
0 AS ProjectAcreageChange, --建筑面积变化量
0 AS ProjectCostChange, --工程造价变化量
nvl(RRM.CURRENT_SUM_INS,0) ReinsuranceAmntChange, --变更后分保保额
0 AS RetentionAmount, --变更后自留额
nvl(RRM.CHANGED_REINS_PREM,0) ReinsurancePremiumChange, --变更分保费
0 AS ReinsuranceCommssionChange, --变更分保佣金
'156' AS Currency, --货币代码
TO_DATE(RRM.PROC_MONTH|| '01', 'yyyy-mm-dd') AS ReComputationsDate, --分保计算日期
CASE
WHEN (SELECT TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'Q')
FROM DUAL) = 1 THEN
TO_DATE(substr(RRM.PROC_MONTH,0,4)|| '-04-15', 'yyyy-mm-dd')
WHEN (SELECT TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'Q')
FROM DUAL) = 2 THEN
TO_DATE(substr(RRM.PROC_MONTH,0,4)|| '-07-15', 'yyyy-mm-dd')
WHEN (SELECT TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'Q')
FROM DUAL) = 3 THEN
TO_DATE(substr(RRM.PROC_MONTH,0,4)|| '-10-15', 'yyyy-mm-dd')
WHEN (SELECT TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'Q')
FROM DUAL) = 4 THEN
to_date(TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'yyyy') + 1 ||
'-01-15','yyyy-mm-dd')
END AS AccountGetDate ,--账单归属日期
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') pushdate
FROM RIS_REINSURANCE_MONTH RRM,
RIS_COMPANY_INFO RCI,
RIS_TREATY_INFO RTI,
UPDATETEMP_v3 UV
WHERE RRM.COMPANY_CODE = RCI.COMPANY_CODE and RRM.DATA_SOURCE='I'
AND RRM.TREATY_NO = RTI.TREATY_NO
AND RRM.BUSINESS_TYPE = '02'
AND rrm.risk_change_reason IN('03','04','05')
AND UV.CONTNO = RRM.POLICY_NO
and uv.otherno = rrm.reins_seq
and exists (select 1 from lpedoritem where contno = rrm.policy_no and edortype in ('RE','WT','CT'))
AND TO_CHAR(UV.TRANSDATE, 'yyyy-mm-dd') =
TO_CHAR(RRM.CREATE_DATE, 'yyyy-mm-dd')
AND UV.OTHERNOTYPE = '72';
-- dbms_output.put_line('!!!!!!!!!再保保全变更信息表(RIS_REINSURANCE_MONTH)!!!!!!!!');
commit;
end p_insert_LREdor_v3;

/**
3期新增再保理赔信息表
*/
--37
PROCEDURE p_insert_LRClaim_v3(i_strdate in date) AS

begin
insert into temp_LRClaim_v3
SELECT distinct UV.BUSINO AS TransactionNo, --交易编码
'000103' AS CompanyCode, --保险机构代码 (000103 恒大人寿保险有限公司)
RRM.GRP_POLICY_NO GrpPolicyNo, --团体保单号
DECODE(RRM.GRP_POLICY_NO, '', '', RRM.RISK_CODE) GrpProductNo, --团体保单险种号码
RRM.POLICY_NO PolicyNo, --个人保单号
(select max(polno) from lcpol where contno = rrm.policy_no and riskcode like ''||rrm.risk_code||'%' ) ProductNo, --个单保险险种号码
DECODE(RRM.DATA_SOURCE, 'I', '01', 'G', '02', '99') GPFlag, --保单团个性质代码(01 个人；02 团体；99 其他)
(SELECT max(polno)
FROM LCPOL L
WHERE L.CONTNO = RRM.POLICY_NO
AND L.POLNO = L.MAINPOLNO
and (l.mainriskflag is null or l.mainriskflag ='1')) AS MainProductNo, --主险保险险种号码
DECODE((SELECT COUNT(1)
FROM LCPOL L
WHERE L.CONTNO = RRM.POLICY_NO
AND L.POLNO = L.MAINPOLNO
AND L.RISKCODE = RRM.RISK_CODE
),
0,
'2',
'1') AS MainProductFlag, --主附险性质代码（1 主险；2附加险；3不区分）
RRM.RISK_CODE ProductCode, --产品编码
RRM.RIS_LIABILITY_CODE LiabilityCode, --责任代码
(SELECT BASE_TABLE_DESC
FROM RIS_BASE_TBL
WHERE BASE_TABLE_TYPE = 'RIS_LIABILITY_CODE'
AND BASE_TABLE_CODE = RRM.RIS_LIABILITY_CODE) LiabilityName, --责任名称
RRM.RIS_LIABILITY_CODE GetLiabilityCode, --给付责任代码
(SELECT T.BASE_TABLE_DESC
FROM RIS_BASE_TBL T
WHERE T.BASE_TABLE_TYPE = 'RIS_LIABILITY_CODE'
AND T.BASE_TABLE_CODE = RRM.RIS_LIABILITY_CODE) GetLiabilityName, --给付责任名称
/*100 意外医疗;101 意外伤残;102 意外死亡;103 意外高残;104 意外大病;105 意外特种疾病;106 意外失业失能;
109 意外豁免;200 疾病医疗;201 疾病伤残;202 疾病死亡;203 疾病高残;204 疾病大病;205 疾病特种疾病;
206 疾病失业失能;209 疾病豁免;301 赔款支出;302 死伤医疗给付;303 满期给付;304 年金给付;
305 分保赔付支出（损益）;306 其他赔付*/
DECODE(RRM.RIS_LIABILITY_CODE,
'ABFMR',
'100',
'ADB',
'102',
'ADD_CI',
'205',
'ADD_CI1',
'205',
'ADD_JCI',
'205',
'ADD_JL',
'205',
'ADD_MD',
'200',
'ADD_RSCI',
'205',
'AD_D',
'306',
'AD_TPD',
'306',
'AD_TPD_2',
'306',
'AHI',
'306',
'AMR',
'100',
'AVI_AD_TPD',
'306',
'DB',
'306',
'DD',
'306',
'ELE_AD_TPD',
'306',
'ELE_GAS_AD_TPD',
'306',
'GAS_AD_TPD',
'306',
'HI',
'306',
'HOL_AD_TPD',
'306',
'HR',
'306',
'HST_AD_TPD',
'306',
'MD',
'306',
'PCAD',
'306',
'PCAD_TPD',
'306',
'PCAD_TPD2',
'306',
'SDB',
'202',
'SELF_AD_TPD',
'306',
'SELF_AD_TPD2',
'306',
'SI',
'306',
'TADB',
'102',
'TAD_D',
'306',
'TAD_TPD',
'306','306') BenefitType, --赔付责任类型代码
(SELECT DISTINCT DECODE(A.RISKPERIOD,
'L',
'10',
'M',
'21',
'S',
'22','10')
FROM LMRISKAPP A
WHERE A.RISKCODE = RRM.RISK_CODE
) TermType, --保险期限类型(10 长期险 21 短期)
nvl(changecode_v2((select substr(managecom,0,4) from lccont where contno =rrm.policy_no),'MANAGE',''),'000103') AS ManageCom, --管理机构代码( 000103440000恒大人寿保险有限公司广东分公司)
(select signdate from lccont where contno=uv.contno) SignDate, --签单日期
(select cvalidate from lccont where contno=uv.contno) EffDate, --保险责任生效日期
RRM.POL_YEAR PolYear, --保单年度
(select max(lc.payenddate) from lcpol lc where lc.contno = uv.contno and lc.riskcode like ''||rrm.risk_code||'%') InvalidDate, --保险责任终止日期
/*核保结论代码(10 标准体; 20 次标准体;21 加费;32 限额;33 特别约定;40 延期;50 拒保;60 优标体*/
(SELECT changecode_v2(lc.UWFlag, 'UWConclusion', '')
FROM LCCONT LC
WHERE LC.CONTNO = RRM.POLICY_NO) UWConclusion, --核保结论代码
DECODE(RRM.RISK_CHANGE_REASON, '01', '01', '02', '02', '99') PolStatus, --保单状态代码(01 有效 02中止 99其它)
DECODE(RRM.RISK_CHANGE_REASON, '01', '01', '02', '02', '99') Status, --保单险种状态代码(01 有效 02中止 99其它)
RRM.BASE_SUM_INS BasicSumInsured, --基本保额
nvl(RRM.CEDE_RISK_SUMINS,0) RiskAmnt, --风险保额
RRM.REINS_PREM Premium, --保费
'2' AS DeductibleType, --免赔类型代码
0 AS Deductible, --免赔额
0 AS ClaimRatio, --赔付比例
nvl((SELECT RIP.CASH_VALUE
FROM RIS_INITIAL_POLICY RIP
WHERE RIP.PROC_MONTH = RRM.PROC_MONTH and RIP.Data_Source='I'
AND RIP.POLICY_NO = RRM.POLICY_NO
AND RIP.RISK_CODE = RRM.RISK_CODE
AND RIP.INSURED_IDNO = RRM.INSURED_IDNO
AND RIP.INSURED_NAME = RRM.INSURED_NAME),0) AccountValue, --保险账户价值
DECODE(RRM.RENEWAL_FLAG, '2', '1', '0') FacultativeFlag, --临分标记
'0' AS AnonymousFlag, --无名单标志
decode((select risktype7 from lmriskapp where riskcode = rrm.risk_code),'0','0','1') AS WaiverFlag, --豁免险标志
0 AS WaiverPrem, --所需豁免剩余保费
nvl(RRM.RESERVE,0) FinalCashValue, --期末现金价值
nvl(nvl((SELECT L3.INSUREDNO
FROM LCINSURED L3
WHERE L3.IDNO = RRM.INSURED_IDNO
AND L3.CONTNO = RRM.POLICY_NO and rownum=1),
(select ld.customerno from ldperson ld
where ld.name = rrm.insured_name
and ld.idno = rrm.insured_idno and rownum=1)),(select insuredno from lcinsured where contno =rrm.policy_no and rownum=1)) InsuredNo, --被保人客户号
RRM.INSURED_NAME InsuredName, --被保人姓名
DECODE(RRM.INSURED_SEX, '0', '1', '1', '2', '9') InsuredSex, --被保人性别(1 男性； 2女性； 9 未说明的性别)
/*(111 居民身份证;113 户口簿;114中国人民解放军军官证(包含军官证、士兵证)
117 出生医学证明;414 普通护照;990 其他
*/
nvl((SELECT changecode_v2(L2.IDTYPE, 'CertType', '')
FROM LDPERSON L2
WHERE RRM.INSURED_NAME = L2.NAME
AND L2.IDNO = RRM.INSURED_IDNO and rownum =1),'990')
InsuredCertType, --被保人证件类型
RRM.INSURED_IDNO InsuredCertNo, --被保人证件编码
/*(SELECT l.occupationtype FROM LCINSURED l
WHERE rrm.insured_idno = l.idno
AND rrm.insured_name=l.name)*/
changecode_v2((SELECT L.OCCUPATIONCODE
FROM LCINSURED L
WHERE L.NAME = RRM.INSURED_NAME
AND L.IDNO = RRM.INSURED_IDNO
AND L.CONTNO = RRM.POLICY_NO AND rownum =1),'OccupationType','') AS OccupationType, --职业代码
RRM.INSURED_AGE AppntAge, --投保年龄
RRM.CURRENT_AGE PreAge, --当前年龄
nvl(RRM.RESERVE,0) FinalLiabilityReserve, --期末责任准备金
nvl((SELECT RIP.OCCU_ADD_PREM
FROM RIS_INITIAL_POLICY RIP
WHERE RIP.PROC_MONTH = RRM.PROC_MONTH and RIP.Data_Source='I'
AND RIP.POLICY_NO = RRM.POLICY_NO
AND RIP.RISK_CODE = RRM.RISK_CODE
AND RIP.INSURED_IDNO = RRM.INSURED_IDNO
AND RIP.INSURED_NAME = RRM.INSURED_NAME),0) ProfessionalFee, --职业加费金额
nvl((SELECT RIP.UNSTANDARD_ADD_PREM
FROM RIS_INITIAL_POLICY RIP
WHERE RIP.PROC_MONTH = RRM.PROC_MONTH and RIP.Data_Source='I'
AND RIP.POLICY_NO = RRM.POLICY_NO
AND RIP.RISK_CODE = RRM.RISK_CODE
AND RIP.INSURED_IDNO = RRM.INSURED_IDNO
AND RIP.INSURED_NAME = RRM.INSURED_NAME),0) SubStandardFee, --次标准体加费金额
nvl(RRM.EM_VALUE,0) EMRate, --EM加点
'0' AS ProjectFlag, --建工险标志
'1' AS InsurePeoples, --投保总人数
'1' AS SaparateFlag, --分出标记
RRM.TREATY_NO ReInsuranceContNo, --再保险合同号码
changecode_v2(rrm.COMPANY_CODE, 'COMPANYCODE', '') ReinsurerCode, --再保险公司代码
(select ldc.targetcodename from ldcodemapping_v2 ldc where ldc.codetype='COMPANYCODE' and trim(ldc.basiccode) =RRM.COMPANY_CODE) ReinsurerName, --再保险公司名称
CASE
WHEN (SELECT RLD.CEDE_TYPE
FROM RIS_LIABILITY_DEFINE RLD
WHERE RLD.RISK_CODE = RRM.RISK_CODE AND RLD.Data_Source='I'
AND RLD.TREATY_NO = RRM.TREATY_NO
AND RLD.REINS_PLAN = RRM.REINS_PLAN
AND RLD.COMPANY_CODE = RRM.COMPANY_CODE
AND RLD.RIS_LIABILITY_CODE = RRM.RIS_LIABILITY_CODE
AND to_char(RLD.create_date,'yyyy-mm-dd') <> '2018-08-15' and rownum = 1) IN
('01', '02') THEN
'1'
WHEN (SELECT RLD.CEDE_TYPE
FROM RIS_LIABILITY_DEFINE RLD
WHERE RLD.RISK_CODE = RRM.RISK_CODE AND RLD.Data_Source='I'
AND RLD.TREATY_NO = RRM.TREATY_NO
AND RLD.REINS_PLAN = RRM.REINS_PLAN
AND RLD.COMPANY_CODE = RRM.COMPANY_CODE
AND RLD.RIS_LIABILITY_CODE = RRM.RIS_LIABILITY_CODE
AND to_char(RLD.create_date,'yyyy-mm-dd') <> '2018-08-15' and rownum = 1) IN
('03', '04') THEN
'2'else '3'
END AS ReinsurMode, --分保方式(1 溢额; 2 成数)
nvl(RRM.CEDE_RISK_SUMINS,0) AS ReinsuranceAmnt , --分别额度
nvl(RRM.RETENTION,0) AS RetentionAmount ,--自留额
nvl(RRM.CEDE_RATIO,0) QuotaSharePercentage, --分保比例
RCRM.CLAIM_NO ClaimNo, --赔案号
RCRM.ACCIDENT_DATE AccidentDate, --出险日期
RCRM.CLOSE_DATE ClmSettDate, --结案日期
DECODE(RCRM.IS_REJECT, '0', '04', '01') PayStatusCode, --理赔结论代码(04 拒赔;01 正常给付)
(NVL(RCRM.CLAIM_AMT, 0) - NVL(RCRM.REJECT_AMT, 0)) AS ClaimMoney, --实际赔款金额
NVL(RCRM.RECOVERED_AMT, 0) BackClaimMoney, --摊回赔款金额
RCRM.Close_Date AS BackDate, --摊回日期
'156' AS Currency, --货币代码
TO_DATE(RRM.PROC_MONTH|| '01', 'yyyy-mm-dd') AS ReComputationsDate, --分保计算日期
CASE
WHEN (SELECT TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'Q')
FROM DUAL) = 1 THEN
TO_DATE(substr(RRM.PROC_MONTH,0,4)|| '-04-15', 'yyyy-mm-dd')
WHEN (SELECT TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'Q')
FROM DUAL) = 2 THEN
TO_DATE(substr(RRM.PROC_MONTH,0,4)|| '-07-15', 'yyyy-mm-dd')
WHEN (SELECT TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'Q')
FROM DUAL) = 3 THEN
TO_DATE(substr(RRM.PROC_MONTH,0,4)|| '-10-15', 'yyyy-mm-dd')
WHEN (SELECT TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'Q')
FROM DUAL) = 4 THEN
to_date(TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'yyyy') + 1 ||
'-01-15','yyyy-mm-dd')
END AS AccountGetDate, --账单归属日期
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') pushdate
FROM RIS_CLAIM_RECOVERED_MONTH RCRM,
RIS_TREATY_INFO RTI,
RIS_COMPANY_INFO RCI,
RIS_REINSURANCE_MONTH RRM,
UPDATETEMP_v3 UV
WHERE RRM.TREATY_NO = RTI.TREATY_NO
AND RRM.COMPANY_CODE = RCI.COMPANY_CODE and RRM.DATA_SOURCE='I'
AND RRM.PROC_MONTH = RCRM.PROC_MONTH
AND RRM.POLICY_NO = RCRM.POLICY_NO(+)
AND RRM.RISK_CODE = RCRM.RISK_CODE
and rrm.company_code = rcrm.company_code
AND RRM.INSURED_NAME = RCRM.INSURED_NAME
AND RRM.RIS_LIABILITY_CODE = RCRM.RIS_LIABILITY_CODE
AND RRM.Treaty_No = RCRM.Treaty_No
AND RRM.REINS_PLAN = RCRM.REINS_PLAN
AND UV.CONTNO = RRM.POLICY_NO
and uv.otherno = rrm.reins_seq
AND TO_CHAR(UV.TRANSDATE, 'yyyy-mm-dd') =
TO_CHAR(RRM.CREATE_DATE, 'yyyy-mm-dd')
AND UV.OTHERNOTYPE = '72';
-- dbms_output.put_line('!!!!!!!!!再保理赔信息表(ris_claim_recovered_month)!!!!!!!!');
commit;
end p_insert_LRClaim_v3;

/**
3期新增再保生存金信息表
*/
--38
PROCEDURE p_insert_LRBenefit_v3(i_strdate in date) AS

begin
/*insert into temp_LRBenefit_v3
SELECT UV.BUSINO AS TransactionNo, --交易编码
'000103' AS CompanyCode, --保险机构代码 (000103 恒大人寿保险有限公司)
RRM.GRP_POLICY_NO GrpPolicyNo, --团体保单号
DECODE(RRM.DATA_SOURCE, 'I', '01', 'G', '02', '99') GPFlag, --保单团个性质代码(01 个人；02 团体；99 其他)
RRM.POLICY_NO PolicyNo, --个人保单号
RRM.RISK_CODE ProductNo, --个单保险险种号码
RRM.POL_YEAR PolYear, --保单年度
RRM.RISK_CODE ProductCode, --产品编码

RRM.RIS_LIABILITY_CODE LiabilityCode, --责任代码
(SELECT BASE_TABLE_DESC
FROM RIS_BASE_TBL
WHERE BASE_TABLE_TYPE = 'RIS_LIABILITY_CODE'
AND BASE_TABLE_CODE = RRM.RIS_LIABILITY_CODE) LiabilityName, --责任名称
RRM.RIS_LIABILITY_CODE GetLiabilityCode, --给付责任代码
(SELECT T.BASE_TABLE_DESC
FROM RIS_BASE_TBL T
WHERE T.BASE_TABLE_TYPE = 'RIS_LIABILITY_CODE'
AND T.BASE_TABLE_CODE = RRM.RIS_LIABILITY_CODE) GetLiabilityName, --给付责任名称

(SELECT DISTINCT DECODE(A.BUSINESS_TYPE,
'L',
'10',
'M',
'21',
'S',
'21')
FROM RIS_INITIAL_POLICY A
WHERE A.RISK_CODE = RRM.RISK_CODE
AND A.BUSINESS_TYPE IS NOT NULL) TermType, --保险期限类型(10 长期险 21 短期)
0 AS WDNo, --领取序号
(SELECT DISTINCT L.INSUREDNO
FROM LCINSURED L
WHERE L.NAME = RRM.INSURED_NAME
AND L.IDNO = RRM.INSURED_IDNO
AND L.CONTNO = RRM.POLICY_NO) InsuredNo, --被保人客户号
RRM.INSURED_NAME InsuredName, --被保人姓名
DECODE(RRM.INSURED_SEX, '0', '1', '1', '2', '9') InsuredSex, --被保人性别(1 男性； 2女性； 9 未说明的性别)
\*(111 居民身份证;113 户口簿;114中国人民解放军军官证(包含军官证、士兵证)
117 出生医学证明;414 普通护照;990 其他
*\
DECODE((SELECT DISTINCT LLD.CODE
FROM LCINSURED L, LDCODE LLD
WHERE RRM.INSURED_IDNO = L.IDNO
AND RRM.INSURED_NAME = L.NAME
AND L.CONTNO = RRM.POLICY_NO
AND LLD.CODE = L.IDTYPE
AND LLD.CODETYPE = 'idtype'),
'0',
'111',
'1',
'114',
'2',
'414',
'3',
'990',
'4',
'113',
'5',
'117',
'9',
'990') AS InsuredCertType, --被保人证件类型
RRM.INSURED_IDNO InsuredCertNo, --被保人证件编码
\*(SELECT l.occupationtype FROM LCINSURED l
WHERE rrm.insured_idno = l.idno
AND rrm.insured_name=l.name)*\
changecode_v2((SELECT L.OCCUPATIONCODE
FROM LCINSURED L
WHERE L.NAME = RRM.INSURED_NAME
AND L.IDNO = RRM.INSURED_IDNO
AND L.CONTNO = RRM.POLICY_NO AND rownum =1),'OccupationType','') AS OccupationType, --职业代码
RRM.INSURED_AGE AppntAge, --投保年龄
RRM.CURRENT_AGE PreAge, --当前年龄
rcrm.deal_date AS BenefitDate, --给付日期
'1' AS SaparateFlag, --分出标记

'03' AS BenefitClass, --生存金类型
0 AS BenefitAmount, --生存金领取金额
rcrm.deal_date AS EnterAccDate, --到账日期

RRM.TREATY_NO ReInsuranceContNo, --再保险合同号码
DECODE(RRM.COMPANY_CODE,
'CHINA_RE',
'000009',
'HANNOVER',
'000128',
'GENRE',
'000066',
'QHR',
'000215',
'RGA',
'000182',
'MUNICH_RE',
'000059') ReinsurerCode, --再保险公司代码
DECODE(RRM.COMPANY_CODE,
'CHINA_RE',
'中国人寿再保险有限责任公司',
'HANNOVER',
'汉诺威再保险股份公司上海分公司',
'GENRE',
'德国通用再保险股份公司上海分公司',
'QHR',
'前海再保险股份有限公司',
'RGA',
'RGA美国再保险公司上海分公司',
'MUNICH_RE',
'慕尼黑再保险公司北京分公司') ReinsurerName, --再保险公司名称
CASE
WHEN (SELECT RLD.CEDE_TYPE
FROM RIS_LIABILITY_DEFINE RLD
WHERE RLD.RISK_CODE = RRM.RISK_CODE
AND RLD.TREATY_NO = RRM.TREATY_NO
AND RLD.COMPANY_CODE = RRM.COMPANY_CODE
AND RLD.RIS_LIABILITY_CODE = RRM.RIS_LIABILITY_CODE) IN
('01', '02') THEN
'1'
WHEN (SELECT RLD.CEDE_TYPE
FROM RIS_LIABILITY_DEFINE RLD
WHERE RLD.RISK_CODE = RRM.RISK_CODE
AND RLD.TREATY_NO = RRM.TREATY_NO
AND RLD.COMPANY_CODE = RRM.COMPANY_CODE
AND RLD.RIS_LIABILITY_CODE = RRM.RIS_LIABILITY_CODE) IN
('03', '04') THEN
'2'
END AS ReinsurMode, --分保方式(1 溢额; 2 成数)
NVL(RCRM.RECOVERED_AMT, 0) BackClaimMoney, --摊回赔款金额
'' AS BackDate, --摊回日期
'156' AS Currency, --货币代码
TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'yyyy-mm') || '-01' AS ReComputationsDate, --分保计算日期
CASE
WHEN (SELECT TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'Q')
FROM DUAL) = 1 THEN
TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'yyyy') || '-04-15'
WHEN (SELECT TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'Q')
FROM DUAL) = 2 THEN
TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'yyyy') || '-07-15'
WHEN (SELECT TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'Q')
FROM DUAL) = 3 THEN
TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'yyyy') || '-10-15'
WHEN (SELECT TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'Q')
FROM DUAL) = 4 THEN
TO_CHAR(TO_DATE(RRM.PROC_MONTH, 'yyyy-mm'), 'yyyy') + 1 ||
'-01-15'
END AS AccountGetDate ,--账单归属日期
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') pushdate
FROM RIS_CLAIM_RECOVERED_MONTH RCRM,
RIS_TREATY_INFO RTI,
RIS_COMPANY_INFO RCI,
RIS_REINSURANCE_MONTH RRM,
UPDATETEMP_v3 UV
WHERE RRM.TREATY_NO = RTI.TREATY_NO
AND RRM.COMPANY_CODE = RCI.COMPANY_CODE
AND RRM.PROC_MONTH = RCRM.PROC_MONTH(+)
AND RRM.POLICY_NO = RCRM.POLICY_NO(+)
AND RRM.RISK_CODE = RCRM.RISK_CODE(+)
AND RRM.INSURED_NAME = RCRM.INSURED_NAME(+)
AND RRM.RIS_LIABILITY_CODE = RCRM.RIS_LIABILITY_CODE(+)
AND UV.CONTNO = RRM.POLICY_NO
and uv.otherno = rrm.reins_seq
AND TO_CHAR(UV.TRANSDATE, 'yyyy-mm-dd') =
TO_CHAR(RRM.CREATE_DATE, 'yyyy-mm-dd')
AND UV.OTHERNOTYPE = '72';*/
-- dbms_output.put_line('!!!!!!!!!再保生存金信息表(ris_reinsurance_month)!!!!!!!!');
commit;
end p_insert_LRBenefit_v3;

/**
3期新增保单借款及自垫信息表
*/
--39
PROCEDURE p_insert_LJLoan_v3(i_strdate in date) AS

begin
insert into temp_LJLoan_v3
select b.busino as TransactionNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
a.ContNo as PolicyNo,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
a.polno as ProductNo,
a.edorno as EndorsementNo,
(select d.riskcode from lcpol d where d.polno = a.polno and d.contno = a.contno and rownum=1) as ProductCode,
a.actugetno as ActuGetNo,
case a.loantype when '0' then '1' else '2' end as BusinessType,
a.loandate as LoanDate,
case a.loantype when '0' then 6 else 0 end as LoanTerm,
a.summoney as LoanMoney,
decode(nvl(a.interesttype,'2'),'3','2',nvl(a.interesttype,'2')) as InterestType,
a.interestrate as InterestRate,
'1' as InterestMode,
'1' as SpecifyRate,
a.loandate as InterestStartDate,
0 as LoanLimitMoney,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as PushDate
from loloan a, updatetemp_v3 b
where b.contno = a.contno
and b.othernotype in ('11','13')
and b.TRANSDATE = a.makedate;

-- dbms_output.put_line('!!!!!!!!!保单借款及自垫信息表(LJLoan)!!!!!!!!');
commit;
end p_insert_LJLoan_v3;

/**
3期新增保单还款信息表
*/
--40
PROCEDURE p_insert_LJLoanRepayment_v3(i_strdate in date) AS

begin
insert into temp_LJLoanRepayment_v3
select distinct d.busino as TransactionNo,
'000103' as CompanyCode,
'' as GrpPolicyNo,
a.ContNo as PolicyNo,
'01' as GPFlag, --保单团个险性质代码：01：个人，02：团体，99：其他
b.polno as ProductNo,
a.edorno as EndorsementNo,
b.BusinessType as BusinessType,
nvl((select c.edorno from loloan c ,loloanacctrace lc where
c.edorno = lc.loanno(+)
and lc.edorno = a.edorno
and rownum =1
),(select c.edorno from loloan c where c.contno = a.contno and rownum =1)) as LoanEndorsementNo,
b.riskcode as ProductCode,
nvl((select c.payoffflag from loloan c ,loloanacctrace lc where
c.edorno = lc.loanno(+)
and lc.edorno = a.edorno
and rownum =1
),(select c.payoffflag from loloan c where c.contno = a.contno and rownum =1)) as PayOffFlag,
a.edorvalidate as RepaymentDate,
b.RepaymentMoney as RepaymentMoney,
b.RepaymentInterest as RepaymentInterest,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') pushdate
from lpedoritem a,V_LOLan_Trace b,updatetemp_v3 d
where a.edorno = b.endorsementno
and a.contno = d.contno
and a.edoracceptno = d.otherno
and a.polno = b.polno
and d.othernotype in ('12','14')
and exists (select 1 from loloan lo where lo.contno = a.contno)
and a.edortype <> 'LN'
;

-- dbms_output.put_line('!!!!!!!!!保单还款信息表(LJLoanRepayment)!!!!!!!!');
commit;
end p_insert_LJLoanRepayment_v3;

/**
3期新增保单增值税信息表
*/
--41
PROCEDURE p_insert_LJTax_v3(i_strdate in date) AS

begin
insert into temp_LJTax_v3
select
temp.busino as TransactionNo,
'000103' as CompanyCode,
'000000' as GrpPolicyNo,
temp.contno as PolicyNo,
'01'as GPFlag,
cs.pk_outbillflow as SerialNo,
decode(substr(cs.busino,5),'41','1','43','3','9') as BusinessType,
cs.transerial as BusinessCode,
to_date(cs.trandate,'yyyy-mm-dd') as BusinessDate,
decode(cs.procode,'00000','000000',cs.procode) as ProductCode,
decode(substr(cs.busino,5),
'114', '1' , --红利转万能账户
'115', '1' , --年金转万能账户
'41', '1' , --首期保费收入
'42', '1', --契撤保费
'43', '1' , --续期保费收入
'46', '2' , --保单贷款利息
'47', '1' , --新增附约
'48', '1' , --复效保费
'51', '1', --退保
'53', '2' , --欠缴利息
'54', '1', --协议退保
'55', '4' , --保单补发费用
'57', '1' , --职业变更补费
'569', '1' , --追加投资
'65', '1' , --年龄性别变更补费
'66', '1', --年龄性别变更退费
'67', '1' , --核保加费补费
'D10', '4' , --保单费用
'D17', '1', --契撤保费
'D24', '5', --退保费用
'D55', '5' ,'9' --初始费用
) FeeType,
cs.tranamt as Money,
nvl(cs.taxrate,0) as TaxRate,
nvl(cs.localtax,0) as TaxRateAmount,
changecode_v2((select substr(managecom,0,4) from lccont where contno = temp.contno),'MANAGE','000103500000') as ManageCom,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as pushdate
from updatetemp_v3 temp ,cs_outbillflow cs
where temp.othernotype='74'
and cs.vdata = to_char(i_strdate,'yyyy-mm-dd')
and temp.contno = cs.transerial
and temp.otherno =cs.pk_outbillflow
and cs.busino like 'PLIS%';

-- dbms_output.put_line('!!!!!!!!!保单增值税信息表(LJTax)!!!!!!!!');
commit;
end p_insert_LJTax_v3;

/**
3期新增保单发票关联表
*/
--42
PROCEDURE p_insert_ljinvRelation_v3(i_strdate in date) AS

begin
insert into temp_LJInvoiceRelation_v3
select distinct
temp.busino as TransactionNo,
'000103' as CompanyCode,
'000000' as GrpPolicyNo,
temp.contno as PolicyNo,
'01' as GPFlag,
decode(substr(b.busino,5),'41','1','43','3','9') as BussinessType,
b.transerial as BussinessCode,
temp.otherno as InvoiceNo,
a.billcode as InvoiceCode ,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as pushdate
from updatetemp_v3 temp ,vt_billopen a,cs_outbillflow b,vt_billexe e
where
temp.othernotype ='75'
and temp.otherno = a.billnum
and a.pk_billopen = e.pk_billopen
and e.pk_outbillflow = b.pk_outbillflow
;

-- dbms_output.put_line('!!!!!!!!!保单发票关联表(LJInvoiceRelation)!!!!!!!!');
commit;
end p_insert_ljinvRelation_v3;

/**
3期新增保单发票信息表
*/
--43
PROCEDURE p_insert_LJInvoice_v3(i_strdate in date) AS

begin
insert into temp_LJInvoice_v3
select
TransactionNo,
CompanyCode,
InvoiceNo,
InvoiceCode,
TaxCompanyCode,
Drawer,
invoiceClass,
invocetype,
InvoiceAmount,
TaxAmount,
SubFeeCode,
SubFeeName,
sum(SubFee),
SubTaxRate,
sum(SubTaxAmount),
ProductCode,
ProductName,
TaxpayerName ,
TaxpayerID ,
TaxpayerAddress,
TaxpayerType ,
TaxpayerPhone,
TaxpayerBankCode,
TaxpayerBankAccount,
Currency,
InvioceState,
InvoicePrintDate,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') as pushDate,
'01' as GPFlag
from
(select
tmp.busino as TransactionNo,
'000103' as CompanyCode,
a.billnum as InvoiceNo,
a.billcode as InvoiceCode,
changecode_v2((select substr(managecom,0,4) from lccont where contno = b.transerial),'MANAGE','') as TaxCompanyCode,
(select u.user_name from sm_user u where u.cuserid = a.pk_psn) as Drawer,
'1' as invoiceClass,
case
when a.invocetype = '0' then
'2'
when a.invocetype = '1' then
'1'
end as invocetype,
a.totalpts as InvoiceAmount,
a.totaltax as TaxAmount,
(select t.code||b.procode||b.taxrate from cs_taxitem t where t.pk_taxitem = b.pk_taxitem) as SubFeeCode,
(select t.name from cs_taxitem t where t.pk_taxitem = b.pk_taxitem) as SubFeeName,
b.localamt as SubFee,
b.taxrate as SubTaxRate,
b.localtax as SubTaxAmount,
b.procode as ProductCode,
(select riskname from lmriskapp where riskcode = b.procode) as ProductName,
a.salename as TaxpayerName ,
a.saleaxpayerid as TaxpayerID ,
a.saleaddress as TaxpayerAddress,
(case a.pk_taxorgtally when '0' then '2' else '1' end) as TaxpayerType ,
a.salephone as TaxpayerPhone,
case when length(a.salebankacc)>16 then '' else a.salebankacc end as TaxpayerBankCode,
a.salebankacccode as TaxpayerBankAccount,
'156' as Currency,
case
when a.vstatus = '1' then
'1'
when a.vstatus = '2' then
'3'
when a.vstatus = '3' then
'2'
end as InvioceState,
to_date(a.fdate,'yyyy-mm-dd') as InvoicePrintDate

from updatetemp_v3 tmp , vt_billopen a ,cs_outbillflow b,vt_billexe e
where
tmp.othernotype ='75'
and tmp.otherno = a.billnum
and a.pk_billopen = e.pk_billopen
and e.pk_outbillflow = b.pk_outbillflow)
group by
TransactionNo,
CompanyCode,
InvoiceNo,
InvoiceCode,
TaxCompanyCode,
Drawer,
invoiceClass,
invocetype,
InvoiceAmount,
TaxAmount,
SubFeeCode,
SubFeeName,
SubTaxRate,
ProductCode,
ProductName,
TaxpayerName ,
TaxpayerID ,
TaxpayerAddress,
TaxpayerType ,
TaxpayerPhone,
TaxpayerBankCode,
TaxpayerBankAccount,
Currency,
InvioceState,
InvoicePrintDate
;

-- dbms_output.put_line('!!!!!!!!!保单发票信息表(LJInvoice)!!!!!!!!');
commit;
end p_insert_LJInvoice_v3;

/**
3期新增险种责任定义表
*/
--44
PROCEDURE p_insert_LMLiability_v3(i_strdate in date) AS

begin
insert into temp_LMLiability_v3
select '000000' as TransactionNo,
'000103' as CompanyCode,
lm.riskcode as ProductCode,
lm.riskname as ProductName,
ld.LiabilityCode as LiabilityCode,
ld.LiabilityName as LiabilityName,
ld.classification as Classification,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') pushdate,
'01' as GPFlag
from lmriskapp lm ,lmliability ld
where lm.riskcode =ld.riskcode ;
-- dbms_output.put_line('!!!!!!!!!险种责任定义表(LMLiability)!!!!!!!!');
commit;
end p_insert_LMLiability_v3;

--45
procedure p_changecode_v3 as
begin
--更新领款人客户号
update temp_llbnf_v3 set payeeno = customerno where name = payeename and payeeno is null and customerno is not null;
update temp_llbnf_v3 a set payeeno = (select distinct insuredno from lcinsured where name = a.payeename
and idno = a.payeecertno and idtype = '0' and rownum = 1) where a.payeecerttype = '111' and a.payeeno is null;
update temp_llbnf_v3 a set payeeno = (select distinct appntno from lcappnt where appntname = a.payeename
and idno = a.payeecertno and idtype = '0' and rownum = 1) where a.payeecerttype = '111' and a.payeeno is null;
update temp_llbnf_v3 a set payeeno = (select customerno from ldperson where name = a.payeename and idno = a.payeecertno
and rownum = 1) where a.payeeno is null;
update temp_llbnf_v3 set payeebirthday = to_date(substr(payeecertno, 7, 8),'yyyy-mm-dd') where payeecerttype = '111'
and length(payeecertno) = 18 and payeeno is null;
update temp_llbnf_v3 set payeesex = decode(mod(to_number(substr(payeecertno, 17, 1)), 2),0,'1','0')
where payeecerttype = '111' and length(payeecertno) = 18 and payeeno is null;
commit;
--去除“|”、前后空格、回车和换行符
update TEMP_LCCONT_v3 set STREET = replace(STREET, '|', '1') where STREET like '%|%';
update TEMP_LCCONT_v3 set EMAIL = '' where EMAIL like '%|%';
commit;
update TEMP_LCCONT_v3 set STREET = replace(replace(replace(STREET, '|', ''),chr(10),''),chr(13),'')
where instr(STREET, '' || chr(13) || '') > 0
or instr(STREET, '' || chr(10) || '') > 0;
update TEMP_LCLIABILITY_v3 set LIABILITYNAME = replace(replace(replace(LIABILITYNAME, '|', ''),
chr(10),''),chr(13),'') where instr(LIABILITYNAME, '' || chr(13) || '') > 0
or instr(LIABILITYNAME, '' || chr(10) || '') > 0;
update TEMP_LLCLAIMINFO_v3
set CASEREVIEWOPINION = replace(replace(replace(CASEREVIEWOPINION,'|',''),
chr(10),''),chr(13),'')
where instr(CASEREVIEWOPINION, '' || chr(13) || '') > 0
or instr(CASEREVIEWOPINION, '' || chr(10) || '') > 0;
update TEMP_LLCLAIMINFO_v3
set ACCIDENTCOURSE = replace(replace(replace(ACCIDENTCOURSE, '|', ''),chr(10),''),chr(13),'')
where instr(ACCIDENTCOURSE, '' || chr(13) || '') > 0
or instr(ACCIDENTCOURSE, '' || chr(10) || '') > 0;
update TEMP_LLCLAIMINFO_v3
set INVESTIGATIONCONCLUSION = replace(replace(replace(INVESTIGATIONCONCLUSION,
'|',''),chr(10),''),chr(13),'')
where instr(INVESTIGATIONCONCLUSION, '' || chr(13) || '') > 0
or instr(INVESTIGATIONCONCLUSION, '' || chr(10) || '') > 0;
update TEMP_LLCLAIMDETAIL_v3
set CLAIMDESC = replace(replace(replace(CLAIMDESC, '|', ''),chr(10),''),chr(13),'')
where instr(CLAIMDESC, '' || chr(13) || '') > 0
or instr(CLAIMDESC, '' || chr(10) || '') > 0;
update TEMP_LALLUNDERWRITING_v3
set UWDESC = replace(replace(replace(UWDESC, '|', ''), chr(10), ''),chr(13),'')
where instr(UWDESC, '' || chr(13) || '') > 0
or instr(UWDESC, '' || chr(10) || '') > 0;
update TEMP_LLCLAIMPOLICY_v3
set ACCIDENTDESC = replace(replace(replace(ACCIDENTDESC, '|', ''),chr(10),''),chr(13),'')
where instr(ACCIDENTDESC, '' || chr(13) || '') > 0
or instr(ACCIDENTDESC, '' || chr(10) || '') > 0;
update TEMP_LJAPAY_v3
set CERTNO = replace(replace(replace(CERTNO, '|', ''), chr(10), ''),chr(13),'')
where instr(CERTNO, '' || chr(13) || '') > 0
or instr(CERTNO, '' || chr(10) || '') > 0;
commit;
update TEMP_LACOMMISSION_v3
set BANKACCNO = trim(BANKACCNO)
where length(BANKACCNO) <> length(trim(BANKACCNO));
update TEMP_LACOMMISSION_v3
set ACCNAME = trim(ACCNAME)
where length(ACCNAME) <> length(trim(ACCNAME));
update TEMP_LALLUNDERWRITING_v3
set EXCEPTDUTYREMARK = trim(EXCEPTDUTYREMARK)
where length(EXCEPTDUTYREMARK) <> length(trim(EXCEPTDUTYREMARK));
update TEMP_LALLUNDERWRITING_v3
set SPECIALREMARK = trim(SPECIALREMARK)
where length(SPECIALREMARK) <> length(trim(SPECIALREMARK));
update TEMP_LALLUNDERWRITING_v3
set CONTSPECIALREMARK = trim(CONTSPECIALREMARK)
where length(CONTSPECIALREMARK) <> length(trim(CONTSPECIALREMARK));
update TEMP_LALLUNDERWRITING_v3
set UWDESC = trim(UWDESC)
where length(UWDESC) <> length(trim(UWDESC));
update TEMP_LASALES_v3
set NAME = trim(NAME)
where length(NAME) <> length(trim(NAME));
update TEMP_LCBNF_v3
set NAME = trim(NAME)
where length(NAME) <> length(trim(NAME));
update TEMP_LCCONT_v3
set APPNTNAME = trim(APPNTNAME)
where length(APPNTNAME) <> length(trim(APPNTNAME));
update TEMP_LCCONT_v3
set STREET = trim(STREET)
where length(STREET) <> length(trim(STREET));
update TEMP_LCCONT_v3
set EMAIL = trim(EMAIL)
where length(EMAIL) <> length(trim(EMAIL));
update TEMP_LCCONT_v3
set BANKACCNO = trim(BANKACCNO)
where length(BANKACCNO) <> length(trim(BANKACCNO));
update TEMP_LCCONT_v3
set ACCNAME = trim(ACCNAME)
where length(ACCNAME) <> length(trim(ACCNAME));
update TEMP_LCINSURED_v3
set INSUREDNAME = trim(INSUREDNAME)
where length(INSUREDNAME) <> length(trim(INSUREDNAME));
update TEMP_LCINSURED_v3
set INSUREDCERTNO = trim(INSUREDCERTNO)
where length(INSUREDCERTNO) <> length(trim(INSUREDCERTNO));
update TEMP_LCINSURED_v3
set RGTADDRESS = trim(RGTADDRESS)
where length(RGTADDRESS) <> length(trim(RGTADDRESS));
update TEMP_LCINSURED_v3
set OCCUPATION = trim(OCCUPATION)
where length(OCCUPATION) <> length(trim(OCCUPATION));
update TEMP_LCINSURED_v3
set PARTTIMEOCCUPATION = trim(PARTTIMEOCCUPATION)
where length(PARTTIMEOCCUPATION) <> length(trim(PARTTIMEOCCUPATION));
update TEMP_LCPOLTRANSACTION_v3
set BANKACCNO = trim(BANKACCNO)
where length(BANKACCNO) <> length(trim(BANKACCNO));
update TEMP_LCPOLTRANSACTION_v3
set ACCNAME = trim(ACCNAME)
where length(ACCNAME) <> length(trim(ACCNAME));
update TEMP_LCPOLTRANSACTION_v3
set CERTNO = trim(CERTNO)
where length(CERTNO) <> length(trim(CERTNO));
update TEMP_LJAGET_v3
set BANKACCNO = trim(BANKACCNO)
where length(BANKACCNO) <> length(trim(BANKACCNO));
update TEMP_LJAGET_v3
set ACCNAME = trim(ACCNAME)
where length(ACCNAME) <> length(trim(ACCNAME));
update TEMP_LJAPAY_v3
set BANKACCNO = trim(BANKACCNO)
where length(BANKACCNO) <> length(trim(BANKACCNO));
update TEMP_LJAPAY_v3
set ACCNAME = trim(ACCNAME)
where length(ACCNAME) <> length(trim(ACCNAME));
update TEMP_LJAPAY_v3
set CERTNO = trim(CERTNO)
where length(CERTNO) <> length(trim(CERTNO));
update TEMP_LJDIVDISTRIB_v3
set APPNTNAME = trim(APPNTNAME)
where length(APPNTNAME) <> length(trim(APPNTNAME));
update TEMP_LJDIVDISTRIB_v3
set BANKACCNO = trim(BANKACCNO)
where length(BANKACCNO) <> length(trim(BANKACCNO));
update TEMP_LJDIVDISTRIB_v3
set ACCNAME = trim(ACCNAME)
where length(ACCNAME) <> length(trim(ACCNAME));
update TEMP_LLBNF_v3
set PAYEECERTNO = trim(PAYEECERTNO)
where length(PAYEECERTNO) <> length(trim(PAYEECERTNO));
update TEMP_LLBNF_v3
set BANKACCNO = trim(BANKACCNO)
where length(BANKACCNO) <> length(trim(BANKACCNO));
update TEMP_LLCLAIMDETAIL_v3
set CLAIMDESC = trim(CLAIMDESC)
where length(CLAIMDESC) <> length(trim(CLAIMDESC));
update TEMP_LLCLAIMINFO_v3
set REPORTERNAME = trim(REPORTERNAME)
where length(REPORTERNAME) <> length(trim(REPORTERNAME));
update TEMP_LLCLAIMINFO_v3
set REPORTERCONTECTNO = trim(REPORTERCONTECTNO)
where length(REPORTERCONTECTNO) <> length(trim(REPORTERCONTECTNO));
update TEMP_LLCLAIMINFO_v3
set REPORTERCONTECTADDRESS = trim(REPORTERCONTECTADDRESS)
where length(REPORTERCONTECTADDRESS) <>
length(trim(REPORTERCONTECTADDRESS));
update TEMP_LLCLAIMINFO_v3
set APPLYERNAME = trim(APPLYERNAME)
where length(APPLYERNAME) <> length(trim(APPLYERNAME));
update TEMP_LLCLAIMINFO_v3
set APPLYERCONTECTNO = trim(APPLYERCONTECTNO)
where length(APPLYERCONTECTNO) <> length(trim(APPLYERCONTECTNO));
update TEMP_LLCLAIMINFO_v3
set APPLYERCONTECTADDRESS = trim(APPLYERCONTECTADDRESS)
where length(APPLYERCONTECTADDRESS) <> length(trim(APPLYERCONTECTADDRESS));
update TEMP_LLCLAIMINFO_v3
set STREET = trim(STREET)
where length(STREET) <> length(trim(STREET));
update TEMP_LLCLAIMINFO_v3
set ACCIDENTCOURSE = trim(ACCIDENTCOURSE)
where length(ACCIDENTCOURSE) <> length(trim(ACCIDENTCOURSE));
update TEMP_LLCLAIMINFO_v3
set INVESTIGATIONCONCLUSION = trim(INVESTIGATIONCONCLUSION)
where length(INVESTIGATIONCONCLUSION) <>
length(trim(INVESTIGATIONCONCLUSION));
update TEMP_LLCLAIMPOLICY_v3
set LOSSOCCURLOCATION = trim(LOSSOCCURLOCATION)
where length(LOSSOCCURLOCATION) <> length(trim(LOSSOCCURLOCATION));
update TEMP_LLCLAIMPOLICY_v3
set ACCIDENTDESC = trim(ACCIDENTDESC)
where length(ACCIDENTDESC) <> length(trim(ACCIDENTDESC));
update TEMP_LLCOSTITEM_v3
set CERTNO = trim(CERTNO)
where length(CERTNO) <> length(trim(CERTNO));
update TEMP_LLDISEASE_v3
set DISEASENAME = trim(DISEASENAME)
where length(DISEASENAME) <> length(trim(DISEASENAME));
update TEMP_LLRECEIPT_v3
set CERTNO = trim(CERTNO)
where length(CERTNO) <> length(trim(CERTNO));
commit;
--当【个人险种表】中的<职业加费金额>大于0时，<核保结论代码>应为“21-加费”
update Temp_Lcproduct_V3 aa set aa.uwconclusion='21' where aa.ProfessionalFee > 0 and UWConclusion <> '21';
--【个人险种表】中的<保险责任生效日期>应小于等于<保险责任终止日期>
update Temp_Lcproduct_V3 set EffDate=InvalidDate where EffDate > InvalidDate;
--校验保单表 省市区编码
update temp_lccont_v3 a set a.city=a.province where a.city is not null and a.city<>'000000' and substr(a.city,0,2)<>substr(a.province,0,2) ;
commit;
update temp_lccont_v3 a set a.county=a.city where a.county is not null and a.county<>'000000' and substr(a.county,0,2)<>substr(a.province,0,2) ;
--校验保单投保申请日期小于核保完成日期
update temp_lccont_v3 a set a.UWDate =a.polapplydate where a.uwdate is not null and a.uwdate >date'1900-01-01' and a.polapplydate>a.uwdate ;
---校验保单终止日期小于等于满期日
update temp_lccont_v3 a set a.terminationdate =a.policyenddate where a.terminationdate is not null and a.terminationdate>a.policyenddate;
--职业加费金额>大于0时，<核保结论代码>应为21-加费
update temp_lcproduct_v3 a set a.uwconclusion='21' where a.ProfessionalFee>0 ;
--受益人类别代码>为“1-身故受益人”时，<受益人与被保人关系代码>不应为“00-本人”
update temp_llbnf_v3 a set a.relationtoinsured='99' where a.relationtoinsured='00' and a.bnfcat='1';
--当【理赔案件信息表】中的<审核通过日期>非空时，<结案日期>应大于等于<审核通过日期>
update temp_LLClaimInfo_v3 a set a.clmsettdate = a.CaseReviewDate where a.CaseReviewDate Is Not Null And a.ClmSettDate < a.CaseReviewDate;
--职业加费金额>大于0且<次标准体加费金额>等于0时，<核保结论代码>应为“21-加费”
update temp_lrcont_v3 a set a.uwconclusion='21' where a.ProfessionalFee>0 and a.uwconclusion ='20';
update temp_lrcont_v3 a set a.uwconclusion='20' where a.SubStandardFee>0 and a.uwconclusion ='21';
update temp_lccustomerreturncall_v3 a set a.customgetpoldate = (select lt.customgetpoldate from lccont lt where a.policyno = lt.contno)
where exists (select 1 from lccont lt where a.policyno = lt.contno and lt.customgetpoldate is not null and lt.customgetpoldate<>a.customgetpoldate);
commit;
--当【理赔受益人账户表】的<受益人与被保人关系代码>为“00-本人”且同一<个人保单号>下【理赔受益人账户表】的<被保人客户编号>与【被保险人表】的<被保人客户编号>一致时，<受益人姓名>应与【被保险人表】中<被保人名称>一致
update temp_llbnf_v3 a set a.name =(select t.insuredname from temp_lcinsured_v3 t where t.policyno = a.policyno and t.insuredno = a.insuredno and rownum =1),
a.certtype = (select t.InsuredCertType from temp_lcinsured_v3 t where t.policyno = a.policyno and t.insuredno = a.insuredno and rownum =1) ,
a.certno = (select t.insuredcertno from temp_lcinsured_v3 t where t.policyno = a.policyno and t.insuredno = a.insuredno and rownum =1)
where a.RelationToInsured='00' and exists (select 1 from temp_lcinsured_v3 b where a.policyno = b.policyno and a.insuredno = b.insuredno );
commit;
--当【被保险人表】的<与投保人关系代码>为“00-本人”时，同一<个人保单号>下，【被保险人表】的<被保人客户编号>应与【个人保单表】中的<投保人客户编号>一致
update temp_lcinsured_v3 a set a.RelationToAppnt= '99' where a.RelationToAppnt ='00' and exists (select 1 from temp_lccont_v3 t where t.policyno = a.policyno and t.busino = a.busino and t.appntno <> a.insuredno);
commit;
--149 付费明细表 LJAGet 证件号码 CertNo 编号类 当【付费明细表】中的<付费方式代码>为“20-银行卡”时，<证件号码>非空
update temp_ljaget_v3 t set t.certno = (select a.appntidno from lccont a where a.contno = t.policyno) where t.getway= '20' and t.Certno is null ;
commit;
update temp_ljaget_v3 t set t.certtype = (select changecode_v2( a.appntidtype, 'CertType', '') from lccont a where a.contno = t.policyno) where t.getway= '20' and t.certtype is null ;
commit;
--险种表首期交费日期小于保单表首期交费日期时，险种等于保单
update temp_lcproduct_v3 t set t.paybegindate = (select paybegindate from temp_lccont_v3 a where a.busino = t.busino and rownum =1) where exists (select 1 from temp_lccont_v3 b where b.busino = t.busino and t.paybegindate<b.paybegindate);
--50 保险账户表 LCInsureAcc 结算日期 SettlementDate 日期类 当【保险账户表】的<结算日期>不为空时，<结算日期>应大于等于<账户成立日期>
update temp_lcinsureacc_v3 t set t.AccountDate = t.SettlementDate where SettlementDate is not null and SettlementDate < AccountDate ;
--120 当【个人保单表】中的<保单状态代码>为“03-终止”时，<保单终止日期>非空
update temp_lccont_v3 t set t.Terminationdate=(select max(startdate) from lccontstate lc where lc.contno = t.policyno and lc.state='1' and lc.enddate is null) where t.Polstatus = '03' and Terminationdate is null;
--4 个人保单表 LCCont 市编码 City 代码类 当【个人保单表】中的<市编码>不为空且不为“000000”时，对应《业务代码表》中的“县及县以上行政区划代码”时，并且<市编码>的前两位应与<省编码>的前两位一致
update temp_lccont_v3 aa set aa.City='000000' where aa.province ='000000' and City is not null and City <> '000000';
--97 当【保单险种核保信息表】中的<加费标志>为“1-是”时，<加费原因>不应为空
update temp_lallunderwriting_v3 t set t.AddFeeRemark= '健康加费' where AddFeeRemark is null and AddFeeFlag = '1' ;
--101 当【再保首续期险种明细表】中的<业务类型>为“02-续期”或“03-续保”时，<续期续保次数>应大于等于1
update temp_lrcont_v3 aa set RenewalTimes=1 where (aa.Eventtype = '02' or aa.Eventtype = '03') and aa.RenewalTimes < 1;
--153当【客户回访表】中的<回访日期>大于等于<保单回执客户签收日期>且小于等于<犹豫期截止日期>时，<是否犹豫期内回访>应为“1-是”
update temp_lccustomerreturncall_v3 aa set aa.Hesitateperiodflag='1' where aa.Rtrcallsuccdate >= aa.Customgetpoldate and aa.Rtrcallsuccdate <= aa.Hesitateexpirationdate and aa.Hesitateperiodflag <> '1';
commit;
--【个人保单表】中<交易编码>在【保单交易表】中对应的<业务类型代码>为“27-保单质押解除”时，<保单第三方质押标识>应为“0-否”
update temp_lccont_v3 t set t.PledgeFlag ='0' where t.pledgeflag <>'0' and exists (select 1 from temp_lcpoltransaction_v3 b where t.busino = b.busino and b.busstype = '27' );
commit;
--当【个人险种表】和【个人保单表】的<首期交费日期>都不为空时，同一<个人保单号>在【个人险种表】的<首期交费日期>应大于等于在【个人保单表】的<首期交费日期>
update temp_lcproduct_v3 t set t.paybegindate=(select paybegindate from temp_lccont_v3 where policyno =t.policyno and rownum=1)
where exists (select 1 from temp_lccont_v3 lc where lc.busino = t.busino and lc.policyno =t.policyno and t.paybegindate<lc.paybegindate);
commit;
--个人险种表生效日期小于首次缴费日期,生效日为投保日次日，首次缴费日期比投保日晚
update temp_lcproduct_v3 t set t.effdate = (select lc.cvalidate from lcpol lc where lc.polno = t.productno and lc.cvalidate>=t.effdate and lc.cvalidate<=t.invaliddate and rownum=1)
where t.effdate<t.paybegindate and exists (select 1 from lcpol lc where lc.polno = t.productno and lc.cvalidate>=t.effdate and lc.cvalidate<=t.invaliddate and rownum=1);
commit;
--同一<个人保单号>下的同一<个单保险险种号码>在【保单险种责任项表】中的<责任终止日期>应小于等于【个人险种表】的<保险责任终止日期>
update temp_lcliability_v3 t set t.invaliddate = (select max(lc.invaliddate) from temp_lcproduct_v3 lc where lc.busino = t.busino and lc.policyno = t.policyno and lc.productno = t.productno )
where exists (select 1 from temp_lcproduct_v3 lc where lc.busino = t.busino and lc.policyno = t.policyno and lc.productno = t.productno and lc.invaliddate<t.invaliddate);
commit;
update temp_lcliability_v3 t set t.invaliddate = (select min(lc.enddate) from lcpol lc where t.policyno = lc.contno and t.productno = lc.polno and t.invaliddate<lc.cvalidate )
where exists (select 1 from lcpol lc where t.policyno = lc.contno and t.productno = lc.polno and t.invaliddate<lc.cvalidate);
commit;
update temp_lcliability_v3 t set t.effdate = (select lc.cvalidate from lcpol lc where lc.polno = t.productno and lc.cvalidate>=t.effdate and lc.cvalidate<=t.invaliddate and rownum=1
) where exists (select 1 from temp_lcproduct_v3 t3 where t3.policyno = t.policyno and t3.productno =t.productno and t.effdate<t3.effdate);
commit;

--当【出险人信息表】的<出险人保单身份类型代码>为“2-被保人”时，<出险人证件类型代码>应与对应<赔案号>在【理赔保单明细表】中对应的<个人保单号>在【被保险人表】中的<证件类型代码>一致
update temp_llclaimantinfo_v3 t set t.dangercerttypecode = '113'
where t.PolType = '2' and exists (select 1 from temp_lcinsured_v3 c where t.DangerCertTypeCode <> c.InsuredCertType and t.busino=c.busino and t.certno = c.insuredcertno);
commit;
--当【保单险种核保信息表】中的<核保业务类型代码>为“1-契约核保”时，同一<个人保单号>在【保单险种核保信息表】中的<核保完成日期>应大于等于【个人保单表】中的<投保单申请日期>
update temp_Lallunderwriting_v3 a set a.uwdate = (select min(b.polapplydate) from temp_lccont_v3 b where a.policyno = b.policyno )
where a.UWBusinessType = '1' and exists (select 1 from temp_lccont_v3 b where a.busino = b.busino and a.policyno = b.policyno and a.UWDate < b.PolApplyDate);
commit;

end p_changecode_v3;

procedure p_insert_zbx_v3_logs(i_pushdate in date,
i_msg in varchar2
) as
v_pushdate date;
v_msg varchar2(2000);
begin
v_pushdate := i_pushdate;
v_msg := substr(i_msg,0,2000);
insert into t_log_zbx_V3
values(v_pushdate,v_msg,sysdate);
commit;
exception
when others then
null;
end p_insert_zbx_v3_logs;

END PKG_NEW_BDDJ3_20200703;
/