CREATE OR REPLACE PROCEDURE shanglifeecif.insuranceclaimthread_main() -- 创建主存储过程
IS    
BEGIN
	   insert into shanglifeecif.insuranceclaimthread (
		icthreadid ,--'保险理赔Id',1
		crno ,--'立案号',2
		--companyno ,-- '公司代码',
		policyno ,--'保险单号 INSURANCE_CLAIM.CONTNO',3
		--applicantid ,--'投保人ID',
		--appname ,--'投保人名称',
		--appphone ,--'投保人手机',
		--appcertid ,--'投保人证件号码 INSURANCE_CLAIM.RISKDATE',
		cnno ,--'报案号',4
		losstime ,-- '损失发生时间',5
		--rpid ,-- '报案人',
		rpname ,--'报案人名称',6
		--rpphone ,-- '报案人电话',
		--lpid ,--'出险人	根据INSURANCE_CLAIM.INSUREDNO获取indid',
		lpscutid,--'出险人上游客户号7
		lpname ,-- '出险人名称',
		cndate ,--'报案日期 INSURANCE_CLAIM.RPTDATE',
		lossdescribe ,-- '损失原因 案件发生的原因:INSURANCE_CLAIM.LLOCCURREASON',
		crdate ,-- '立案日期 INSURANCE_CLAIM.RGTDATE',
		cndescribe ,--'报案描述 INSURANCE_CLAIM.ACCIDENTDETAIL',
		claimstatus ,--'理赔状态 案件的状态，例如：录入、已立案等',
		csdate ,--'理赔状态日期',
		relationship ,-- '报案人与出险人关系',
		ccno ,-- '赔案号 AUDIT_CLAIM_INSURANCE.CLMNO',
		ccstatus ,--'赔案状态',
		--ccsdate ,-- '赔案状态日期',
		productid ,--'险种代码	INSURANCE_CLAIM_HEALTH.RISKCODE',
		productname, --'险种名称	INSURANCE_CLAIM_HEALTH.RISKNAME',
		ccamt ,--'赔付金额	INSURANCE_CLAIM_HEALTH.realpay',
		--branchcode,-- '机构代码',
		claimcasestatus ,--'案件状态INSURANCE_CLAIM_HEALTH.LLCLAIMSTATE',
		accidenttype,
		created_by ,
		created_time 
		-- updated_time,
		-- updated_by
	) select 
   		row_number()over(),--1
   		trim(rgtno) as rgtno,--2
		trim(contno) as contno,--3
		trim(rptno) as rptno,--报案号4
		trim(riskdate) as riskdate,--损失发生的时间5
		trim(rptorname) as rptorname,--报案人名称6
   		trim(INSUREDNO) as INSUREDNO,--出险人 上游客户号
   		trim(INSUREDNAME) as INSUREDNAME,
		trim(rptdate) as rptdate,
		trim(lloccurreason) as lloccurreason,
		trim(rgtdate) as rgtdate,
		trim(ACCDESC) as ACCDESC,--报案描述
		trim(llclaimstate) as llclaimstate,
		trim(auditdate) as auditdate,--审批日期
		trim(relationname) as relationname,
		trim(clmno) as clmno,--赔案号
		trim(llgettype) as llgettype,--赔案状态
		trim(riskcode) as riskcode,
		trim(riskname) as riskname,
		realpay,
		trim(llclaimstate) as llclaimstate,--案件状态
		trim(accidenttype) as accidenttype,
		'admin',
		sysdate()
	from dsj.INSURANCE_CLAIM where contno is not null;
	
	-- 更新渠道字段
	shanglifeecif.update_insuranceclaimthread_add_salecom();
	dbms_output.put_line('insuranceclaimthread_main函数跑批完成！');
	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());
			WHEN Others THEN
				INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());	

END;