ShangHaiLifeECIF/sql-achievement/存储过程/人寿测试环境使用sql整理/客户标签label41-60.sql

CREATE OR REPLACE PROCEDURE shanglifeecif.updateIndividualLable_41_60() IS
DECLARE
BEGIN
	--47	是否仅有1年期短险保单	Label43	仅有1年期短险保单，有1年期短险保单	"policy_information表 SECURITY in('1年','6月','180天') riskperiod=‘短期险’
	--riskcode not in('1066008','1066007','1066006','1066004','1066003','1066002','1066001','1065005','1065003','1065001','1025003')"	客户为保单投保人，所有保单的保障期限为一年
	UPDATE shanglifeecif.Individual si1 SET si1.Label43 = (
		SELECT
			CASE	
				WHEN temp.cnt >0 THEN
						'有1年期短险保单' 	
			END
		FROM
			(
			SELECT
				count(1) cnt,
				policy.customerno 
			FROM
				policy_information policy
			where
				policy.SECURITY in('1年','6月','180天')
			and
				policy.riskperiod='短期险'
			and	
				policy.riskcode not in('1066008','1066007','1066006','1066004','1066003','1066002','1066001','1065005','1065003','1065001','1025003')
			
			GROUP BY
				policy.customerno 
			) temp 
		WHERE
		temp.customerno = si1.scustid 
	);	
	--48	是否有失效保单	Label44	有失效保单	NOIVPolicy	客户为保单投保人，持有保单中保单状态为失效 （最新文档逻辑）
		UPDATE shanglifeecif.Individual si1 SET si1.label44 = '有失效保单' WHERE si1.scustid IN (
		SELECT p.customerno FROM policy_information p WHERE p.contno IN (select contno from lccontstate where statetype = 'Available' and state = '1') 
	);
	--49	是否有缴费期满长险保单	Label45	有缴费期满长险保单	POLICY_INFORMATION:paycount= payendyear AND riskperiod='长期险'	客户持有保险期限为一年以上，剩余保费期数为0
		UPDATE shanglifeecif.Individual si1 SET si1.label45 = '有缴费期满长险保单' WHERE si1.scustid IN (
		SELECT p.customerno FROM policy_information p WHERE p.contno IN (
		select contno from lcpol where payintv > 0 and paytodate = payenddate and months_between(cvalidate, enddate) > 12
		) 
	);
	--50	是否有续期缴费的保单	Label46	有续期缴费的保单		客户为投保人，含有待缴费状态的保单
		UPDATE shanglifeecif.Individual si1 SET si1.label46 = '有续期缴费保单' WHERE si1.scustid IN (
		SELECT p.customerno FROM policy_information p WHERE p.contno IN (
			select otherno from ljspay where othernotype = '2'
		) 
	);
	--51	有责任期满的保单	Label47	有责任期满的保单		客户持有保单含有保险期限已满的保单
		UPDATE shanglifeecif.Individual si1 SET si1.label47 = '有责任期满的保单' WHERE si1.scustid IN (
		SELECT p.customerno FROM policy_information p WHERE p.contno IN (
			select contno from lcpol where enddate < SYSDATE
		) 
	);
		
	--52	第一张保单投保日期	Label48	首次投保日期XXXX-XX-XX	FADate	客户所有保单中投保时间最早的日期
	--第一步  
	UPDATE shanglifeecif.Individual si1 SET si1.FADate = (
	SELECT fadate FROM (
		SELECT p.customerno,min(p.CUSTOMGETPOLDATE) AS fadate  FROM POLICY_INFORMATION p WHERE p.customgetpoldate IS NOT null GROUP BY p.customerno
		) t WHERE t.customerno = si1.scustid
	);

	UPDATE shanglifeecif.Individual si1 SET si1.label48 = '首次投保日期' || si1.fadate WHERE si1.fadate IS NOT NULL;

	--53	最后一次寿险投保距今时长	Label49	最后一次投保距今XXX天	LAPPDate	当前日期减去客户所有保单中最后一次投保的保单的投保时间
	UPDATE shanglifeecif.Individual si1 SET si1.LAPPDate = (
		SELECT fadate FROM (
			SELECT p.customerno,max(p.CUSTOMGETPOLDATE) AS fadate  FROM POLICY_INFORMATION p WHERE p.customgetpoldate IS NOT null GROUP BY p.customerno
			) t WHERE t.customerno = si1.scustid
		);
	
	UPDATE shanglifeecif.Individual si1 SET si1.label49 = '最后一次投保距今' || DATEDIFF(sysdate(), si1.LAPPDate) || '天' WHERE si1.lappdate IS NOT NULL;  
		
	--54	最近一次保单所属渠道	Label50	最近保单团险渠道、最近保单个人营销、最近保单银行代理、最近保单中介渠道、最近保单网销渠道	--LPChannel，InsuranceArrangement.AgentChannel	客户最后一次投保归属渠道
	UPDATE shanglifeecif.Individual si1 SET si1.label50 =  (SELECT '最近保单' || trim(t.salecomname) FROM (
			SELECT p.customerno,row_number()OVER(PARTITION BY p.customerno ORDER BY p.customgetpoldate DESC) rd,p.salecomname  FROM POLICY_INFORMATION p 
			WHERE p.salecomname IS NOT null
		) t WHERE t.rd = 1 AND t.customerno = si1.scustid);
	
	
	--UPDATE shanglifeecif.Individual si1 SET si1.label50 =  (SELECT t.SALECHNL FROM (
			--SELECT p.customerno,row_number()OVER(PARTITION BY p.customerno ORDER BY p.customgetpoldate DESC) rd,p.SALECHNL  FROM POLICY_INFORMATION p WHERE p.salechnl IS NOT null
		--) t WHERE t.rd = 1 AND t.customerno = si1.scustid);

	--UPDATE shanglifeecif.Individual si1 SET si1.label50 =  '最近保单' || si1.label50 WHERE si1.label50 IS NOT NULL;
	--55	最近一次保单所属机构	Label51	最近XX机构	LPOrg,InsuranceArrangement.AgentOrg	客户最后一次投保归属机构
	--UPDATE shanglifeecif.Individual si1 SET si1.label51 =  (SELECT t.AGENTCOM FROM (
		--	SELECT p.customerno,row_number()OVER(PARTITION BY p.customerno ORDER BY p.customgetpoldate DESC) rd,p.AGENTCOM  FROM POLICY_INFORMATION p WHERE p.salechnl IS NOT null
		--) t WHERE t.rd = 1 AND t.customerno = si1.scustid);

	--UPDATE shanglifeecif.Individual si1 SET si1.label51 =  '最近机构' || si1.label51 WHERE si1.label51 IS NOT NULL;
	--56	最早保单所属渠道	Label52	首单团险渠道、首单个人营销、首单银行代理、首单中介渠道、首单网销渠道	FAChannel	客户第一次投保时保单归属渠道
	UPDATE shanglifeecif.Individual si1 SET si1.label52 =  (SELECT '首单' || trim(t.salecomname) FROM (
			SELECT p.customerno,row_number()OVER(PARTITION BY p.customerno ORDER BY p.customgetpoldate asc) rd,p.salecomname  FROM POLICY_INFORMATION p WHERE p.salecomname IS NOT null
		) t WHERE t.rd = 1 AND t.customerno = si1.scustid);	
	
	
	--UPDATE shanglifeecif.Individual si1 SET si1.label52 =  (SELECT t.SALECHNL FROM (
			--SELECT p.customerno,row_number()OVER(PARTITION BY p.customerno ORDER BY p.customgetpoldate asc) rd,p.SALECHNL  FROM POLICY_INFORMATION p WHERE p.salechnl IS NOT null
		--) t WHERE t.rd = 1 AND t.customerno = si1.scustid);

	--UPDATE shanglifeecif.Individual si1 SET si1.label52 =  '首单' || si1.label52 WHERE si1.label52 IS NOT NULL;
	--57	最早保单所属机构	Label53	首单XX机构	FAOrg	客户第一次投保时保单归属机构
	--UPDATE shanglifeecif.Individual si1 SET si1.label53 =  (SELECT t.AGENTCOM FROM (
	--	SELECT p.customerno,row_number()OVER(PARTITION BY p.customerno ORDER BY p.customgetpoldate ASC) rd,p.AGENTCOM  FROM POLICY_INFORMATION p WHERE p.salechnl IS NOT null
	--) t WHERE t.rd = 1 AND t.customerno = si1.scustid);

	--UPDATE shanglifeecif.Individual si1 SET si1.Label53 =  '最近机构' || si1.Label53 WHERE si1.Label53 IS NOT NULL;
	--58	最近保单状态	Label54	"未承保承保新增附加险终止续保未对账对账失败"	LPState	客户最后一张保单的保单状态
	UPDATE shanglifeecif.Individual si1 SET si1.label54 = 
	(SELECT 
		(CASE temp.appflag 
		WHEN '0' THEN '最近保单状态未承保' 
		WHEN '1' THEN '最近保单状态承保' 
		WHEN '2' THEN '最近保单状态新增附加险' 
		WHEN '4' THEN '最近保单状态终止' 
		WHEN '9' THEN '最近保单状态续保' 
		WHEN 'B' THEN '最近保单状态未对账' 
		WHEN 'F' THEN '最近保单状态对账失败' 
		END) a 
	FROM (
		SELECT t.appflag,p.customerno,row_number()OVER(PARTITION BY p.customerno ORDER BY p.customgetpoldate desc) rd FROM policy_information p,lccont t WHERE p.contno = t.contno
	) temp WHERE temp.rd = 1  AND si1.scustid = temp.customerno);
	
	--60	是否有退保保单	Label56	有退保保单	如何判断？	客户所有的保单中是否存在保单状态为退保的保单
		UPDATE shanglifeecif.Individual si1 SET si1.label56 = '有退保保单' WHERE si1.scustid IN (
		SELECT p.customerno FROM policy_information p WHERE p.contno IN (
			select contno from lpedoritem where  edortype in ('CT', 'XT', 'GT') and edorstate = '0'
		) 
	);
	
	--61	最近一次理赔类型	Label57	--意外医疗、意外伤残、意外死亡、意外高残、意外大病、意外特种疾病、意外失业失能、意外生命末期重疾、意外豁免、疾病医疗、疾病伤残、疾病死亡、疾病高残、疾病大病、疾病特种疾病、疾病失---业失能、疾病生命末期重疾、疾病豁免	无	客户最后一次投办理理赔业务的业务类型
	UPDATE shanglifeecif.Individual si1 SET si1.Label57 = (
	SELECT t.ACCIDENTTYPE FROM (
		SELECT row_number()OVER(PARTITION BY c.insuredno ORDER BY c.applydate desc) rd,c.insuredno,c.ACCIDENTTYPE FROM INSURANCE_CLAIM c
		) t WHERE t.rd = 1 AND t.insuredno = si1.scustid
	);
	--62	最近一次出险类型	Label58	疾病出险、意外出险	无	客户最后一次出险的类型
	UPDATE shanglifeecif.Individual si1 SET si1.Label58 = (
		SELECT t.RISKTYPE FROM (
			SELECT row_number()OVER(PARTITION BY c.insuredno ORDER BY c.applydate desc) rd,c.insuredno,c.RISKTYPE FROM INSURANCE_CLAIM c
		) t WHERE t.rd = 1 AND t.insuredno = si1.scustid
	);
	--63	最近一次理赔状态	Label59	理赔报案中、理赔已受理、理赔已立案、理赔审核中、理赔预付审批中、理赔结案、理赔完成、理赔关闭	InsuranceClaimThread.ClaimCaseStatus	--客户最后一次发生理赔的理赔类型
	UPDATE shanglifeecif.Individual si1 SET si1.Label59 = (
		SELECT '最近一次理赔状态' || t.LLCLAIMSTATE FROM (
			SELECT row_number()OVER(PARTITION BY c.insuredno ORDER BY c.applydate desc) rd,c.insuredno,c.LLCLAIMSTATE FROM INSURANCE_CLAIM c
		) t WHERE t.rd = 1 AND t.insuredno = si1.scustid
	);
	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate());
			WHEN Others THEN
				INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate());	
END