CREATE OR REPLACE PROCEDURE shanglifeecif.init_insurancearrangement_main() -- �������洢���� IS BEGIN DELETE FROM shanglifeecif.insurancearrangement; --����policy_information ���±����� shanglifeecif.init_insurancearrangement_policy_information(); --��������ֶ� ���� shanglifeecif.update_risk_categories(); END; / CREATE OR REPLACE PROCEDURE shanglifeecif.init_insurancearrangement_policy_information() -- �������洢���� IS BEGIN INSERT INTO shanglifeecif.insurancearrangement( iaid ,--'����ID', policyno ,-- '���յ��� INSURANCEINFO.CONTNO', pindate ,-- '������ POLICY_INFORMATION.CVALIDATE', pmdate ,-- '�ձ����� POLICY_INFORMATION.ENDDATE', norenewal ,-- '�������� POLICY_INFORMATION.PAYCOUNT', payment ,-- '�ɷѷ�ʽ POLICY_INFORMATION.PAYINTV', applicantscustid ,-- 'Ͷ���� ���οͻ��ţ����ҵ�individualid', appname ,-- 'Ͷ�������� POLICY_INFORMATION.NAME', appcertid ,-- 'Ͷ����֤������ POLICY_INFORMATION.IDNO', insuredscustid ,-- '���������� �οͻ���', insname ,-- '�������������� POLICY_INFORMATION.INSUREDNAME', inscertid ,-- '����������֤������ POLICY_INFORMATION.INSUREDIDNO', productid ,-- '���ִ��� POLICY_INFORMATION.RISKCODE', productname,--�������� policybelong ,-- 'ҵ������ POLICY_INFORMATION.SALECOM', payendyear ,-- '�ɷ����� POLICY_INFORMATION.PAYENDYEAR', policystate,--����״̬ prem,--���� sumprem, --�ۼƱ��ѣ� Risk,--���� NPDate,--�´νɷ����� PADate,--Ͷ������ pisdate,--ǩ������ AgentOrg,--�������� schannel,--�������� salecomname,--ҵ���������� POService,--��ȫ��־ PWComp,--�б��ֹ�˾ security ,--������ԭʼֵ agrmntage ,--�������� salesperson,--ҵ��Ա��ʶ���� SPName,--ҵ��Ա���� created_by ,-- '������', created_time -- '����ʱ��', ) SELECT row_number()over(), trim(CONTNO) as CONTNO,--policyno ,-- '���յ��� INSURANCEINFO.CONTNO', trim(CVALIDATE) as CVALIDATE,--pindate ,-- '������ POLICY_INFORMATION.CVALIDATE', trim(ENDDATE) as ENDDATE,--pmdate ,-- '�ձ����� POLICY_INFORMATION.ENDDATE', PAYCOUNT,--norenewal ,-- '�������� POLICY_INFORMATION.PAYCOUNT', trim(PAYINTV) as PAYINTV,--payment ,-- '�ɷѷ�ʽ POLICY_INFORMATION.PAYINTV', trim(CUSTOMERNO) as CUSTOMERNO,--applicantid ,-- 'Ͷ���� ����POLICY_INFORMATION.CUSTOMERNO����individual���οͻ��ţ����ҵ�individualid', trim(NAME) as NAME,--appname ,-- 'Ͷ�������� POLICY_INFORMATION.NAME', trim(IDNO) as IDNO,--appcertid ,-- 'Ͷ����֤������ POLICY_INFORMATION.IDNO', trim(insuredno) as insuredno,--insuredid ,-- '���������� insuredno', trim(INSUREDNAME) as INSUREDNAME,--insname ,-- '�������������� POLICY_INFORMATION.INSUREDNAME', trim(INSUREDIDNO) as INSUREDIDNO,--inscertid ,-- '����������֤������ POLICY_INFORMATION.INSUREDIDNO', trim(RISKCODE) as RISKCODE,--productid ,-- '���ִ��� POLICY_INFORMATION.RISKCODE', trim(RISKNAME) as RISKNAME, --productname �������� trim(SALECOM) as SALECOM,--policybelong ,-- 'ҵ������ POLICY_INFORMATION.SALECOM', PAYENDYEAR,--payendyear ,-- '�ɷ����� POLICY_INFORMATION.PAYENDYEAR', trim(APPFLAG) as APPFLAG, --����״̬ prem,--���� sumprem,--�ۼƱ��� AMNT,--���� trim(PAYTODATE) as PAYTODATE,--�´νɷ����� trim(polapplydate) as polapplydate,--Ͷ������ trim(SIGNDATE) as SIGNDATE,--ǩ������ trim(AGENTCOM) as AGENTCOM,--�������� trim(SALECHNL) as SALECHNL,--�������� trim(salecomname) as salecomname,--ҵ���������� trim(PRESERVATIONFLAG) as PRESERVATIONFLAG,--��ȫ��־ "�Ϻ������Ϻ��ֹ�˾", trim(security) as security, --������ԭʼֵ case when security ='����' then 42720 when security = '��100����' then 36500 when security = '��80����' then 29200 when security = '70��' then 25550 when security = '��70����' then 25550 when security = '��65����' then 23725 when security = '��60����' then 21900 when security = '30��' then 10950 when security = '20��' then 7300 when security = '10��' then 3650 when security = '6��' then 2190 when security = '5��' then 1825 when security = '1��' then 365 when security = '180��' then 180 when security = '6��' then 180 when security = '90��' then 90 when security = '3��' then 90 when security = '1��' then 30 when security = '30��' then 30 when security = '15��' then 15 when security = '7��' then 7 end, trim(AGENTCODE) as AGENTCODE,--�����˴��� trim(AGENTNAME) as AGENTNAME,--���������� 'admin', sysdate() FROM policy_information; --����INSURANCEINFO ���е���Ϣ UPDATE shanglifeecif.insurancearrangement a SET ( pano ,-- 'Ͷ������ INSURANCEINFO.PRTNO', --pisdate ,-- 'ǩ������ INSURANCEINFO.SIGNDATE', --padate ,-- 'Ͷ������ INSURANCEINFO.POLAPPLYDATE', --policystate ,--INSURANCEINFO,appflag --prem ,-- '�ܱ��� INSURANCEINFO.PREM', --productname ,-- '�������� INSURANCEINFO.RISKNAME' agentchannel -- '�������� INSURANCEINFO.SELLTYPE', ) = ( select trim(b.PRTNO) as PRTNO,--pano ,-- 'Ͷ������ INSURANCEINFO.PRTNO', --SIGNDATE,--pisdate ,-- 'ǩ������ INSURANCEINFO.SIGNDATE', --POLAPPLYDATE,--padate ,-- 'Ͷ������ INSURANCEINFO.POLAPPLYDATE', --appflag,--policystate ,--INSURANCEINFO,appflag --PREM,--prem ,-- '�ܱ��� INSURANCEINFO.PREM', --RISKNAME,--productname ,-- '�������� INSURANCEINFO.RISKNAME' trim(b.SELLTYPE) as SELLTYPE--agentchannel ,-- '�������� INSURANCEINFO.SELLTYPE', from INSURANCEINFO b where b.contno = a.policyno ) WHERE 1=1 ; 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; / CREATE OR REPLACE PROCEDURE shanglifeecif.update_risk_categories() -- �������洢���� IS BEGIN --�������ִ��� UPDATE shanglifeecif.insurancearrangement a SET ( risk_categories_name ) = ( select kindtype from riskkind b WHERE a.productid = b.riskcode ) WHERE 1=1; 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; / /* CREATE OR REPLACE PROCEDURE shanglifeecif.update_risk_categories() -- �������洢���� IS BEGIN --�������ִ��� UPDATE shanglifeecif.insurancearrangement a SET ( risk_categories, risk_categories_name ) = ( select b.kindcode, (CASE b.kindcode WHEN 'A' THEN '�����˺���' WHEN 'U' THEN '���ܱ���' WHEN 'R' THEN '�����' WHEN 'S' THEN '�ؼ�����' WHEN 'L' THEN '���ٱ���' WHEN 'H' THEN '������' END) AS kindname from lmriskapp b WHERE a.productid = b.riskcode ) WHERE 1=1; 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; DROP TABLE IF EXISTS shanglifeecif.insurancearrangement_health_tmp; CREATE TABLE shanglifeecif.insurancearrangement_health_tmp( id string, orderid string DEFAULT NULL COMMENT '���ڷ���������', contno string DEFAULT NULL COMMENT '������', AGENTGROUP string DEFAULT NULL COMMENT '�б��ֹ�˾',--pwcomp ,-- '�б��ֹ�˾ HEALTH_INSURANCE_LISTING.AGENTGROUPAREA Ӫҵ��HEALTH_INSURANCE_LISTING.AGENTGROUP Ӫҵ��', SALECHANNELS string DEFAULT NULL COMMENT '��������',--schannel ,-- '�������� HEALTH_INSURANCE_LISTING.SALECHANNELS 1 2 3 4 5', AMNT string DEFAULT NULL COMMENT '�ܱ���',--risk ,-- '�ܱ��� HEALTH_INSURANCE_LISTING.AMNT(���Ի����У��˱��������������������磺POLICY_INFORMATION��INSURANCEINFO ȱ������һ����������)', AGENTCODE string DEFAULT NULL COMMENT'ҵ��Ա����'--spname ,-- 'ҵ��Ա���� HEALTH_INSURANCE_LISTING.AGENTCODE', ) COMMENT '�����������ճб��嵥�������� ������ʱ�� ' STORED AS ES with shard number 10 replication 1; CREATE OR REPLACE PROCEDURE shanglifeecif.up_insurancearrangement_health_insurance_listing() -- �������洢���� IS BEGIN --�����ʱ������ delete from shanglifeecif.insurancearrangement_health_tmp; --��ʼ����ʱ�� insert into shanglifeecif.insurancearrangement_health_tmp ( id, orderid, contno, agentgroup, SALECHANNELS, AMNT, AGENTCODE ) select row_number()over(), row_number()over(PARTITION BY contno), contno, agentgroup, SALECHANNELS, AMNT, AGENTCODE from HEALTH_INSURANCE_LISTING --������ʱ������insurancearrangement �� UPDATE shanglifeecif.insurancearrangement a SET ( pwcomp ,-- '�б��ֹ�˾ HEALTH_INSURANCE_LISTING.AGENTGROUPAREA Ӫҵ��HEALTH_INSURANCE_LISTING.AGENTGROUP Ӫҵ��', schannel ,-- '�������� HEALTH_INSURANCE_LISTING.SALECHANNELS 1 2 3 4 5', risk ,-- '�ܱ��� HEALTH_INSURANCE_LISTING.AMNT(���Ի����У��˱��������������������磺POLICY_INFORMATION��INSURANCEINFO ȱ������һ����������)', spname -- 'ҵ��Ա���� HEALTH_INSURANCE_LISTING.AGENTCODE', ) = ( select b.AGENTGROUP,--pwcomp ,-- '�б��ֹ�˾ HEALTH_INSURANCE_LISTING.AGENTGROUPAREA Ӫҵ��HEALTH_INSURANCE_LISTING.AGENTGROUP Ӫҵ��', b.SALECHANNELS,--schannel ,-- '�������� HEALTH_INSURANCE_LISTING.SALECHANNELS 1 2 3 4 5', b.AMNT,--risk ,-- '�ܱ��� HEALTH_INSURANCE_LISTING.AMNT(���Ի����У��˱��������������������磺POLICY_INFORMATION��INSURANCEINFO ȱ������һ����������)', b.AGENTCODE--spname ,-- 'ҵ��Ա���� HEALTH_INSURANCE_LISTING.AGENTCODE', from shanglifeecif.insurancearrangement_health_tmp b where b.contno = a.policyno and b.orderid = 1 ) WHERE 1=1 ; END; */ / CREATE OR REPLACE PROCEDURE shanglifeecif.up_insurancearrangement_other() -- �������洢���� IS BEGIN UPDATE shanglifeecif.insurancearrangement a SET ( policytype -- '��������', ) = ( select c.CONTTYPE from ( select row_number()over(PARTITION BY b.contno) rn, b.CONTTYPE, b.contno from PERSONAL_INSURANCE b ) c WHERE c.contno = a.policyno and c.rn=1 ) WHERE 1=1 ; UPDATE shanglifeecif.insurancearrangement a SET ( poservice -- '�Ƿ�ȫ', ) = ( SELECT IF(count(b.CONTNO) >0,1,0) FROM AUDIT_EDORLIST b where b.contno = a.policyno ) WHERE 1=1 ; UPDATE shanglifeecif.insurancearrangement a SET ( soinsured-- '��������', ) = ( SELECT PEOPLES3 FROM HEALTH_GROUP_LISTING b where b.contno = a.policyno ) WHERE 1=1 ; END; / BEGIN shanglifeecif.init_insurancearrangement(); end /* UPDATE shanglifeecif.insurancearrangement a SET ( pindate ,-- '������ POLICY_INFORMATION.CVALIDATE', pmdate ,-- '�ձ����� POLICY_INFORMATION.ENDDATE', norenewal ,-- '�������� POLICY_INFORMATION.PAYCOUNT', payment ,-- '�ɷѷ�ʽ POLICY_INFORMATION.PAYINTV', applicantscustid ,-- 'Ͷ���� ���οͻ��ţ����ҵ�individualid', appname ,-- 'Ͷ�������� POLICY_INFORMATION.NAME', appcertid ,-- 'Ͷ����֤������ POLICY_INFORMATION.IDNO', insuredscustid ,-- '���������� �οͻ���', insname ,-- '�������������� POLICY_INFORMATION.INSUREDNAME', inscertid ,-- '����������֤������ POLICY_INFORMATION.INSUREDIDNO', productid ,-- '���ִ��� POLICY_INFORMATION.RISKCODE', productname,--�������� policybelong ,-- 'ҵ������ POLICY_INFORMATION.SALECOM', payendyear ,-- '�ɷ����� POLICY_INFORMATION.PAYENDYEAR', policystate,--����״̬ prem,--���� Risk,--���� NPDate,--�´νɷ����� PADate,--Ͷ������ AgentOrg,--�������� schannel,--�������� PWComp--�б��ֹ�˾ ) = ( select CVALIDATE,--pindate ,-- '������ POLICY_INFORMATION.CVALIDATE', ENDDATE,--pmdate ,-- '�ձ����� POLICY_INFORMATION.ENDDATE', PAYCOUNT,--norenewal ,-- '�������� POLICY_INFORMATION.PAYCOUNT', PAYINTV,--payment ,-- '�ɷѷ�ʽ POLICY_INFORMATION.PAYINTV', CUSTOMERNO,--applicantid ,-- 'Ͷ���� ����POLICY_INFORMATION.CUSTOMERNO����individual���οͻ��ţ����ҵ�individualid', NAME,--appname ,-- 'Ͷ�������� POLICY_INFORMATION.NAME', IDNO,--appcertid ,-- 'Ͷ����֤������ POLICY_INFORMATION.IDNO', insuredno,--insuredid ,-- '���������� insuredno', INSUREDNAME,--insname ,-- '�������������� POLICY_INFORMATION.INSUREDNAME', INSUREDIDNO,--inscertid ,-- '����������֤������ POLICY_INFORMATION.INSUREDIDNO', RISKCODE,--productid ,-- '���ִ��� POLICY_INFORMATION.RISKCODE', RISKNAME, --productname �������� SALECOM,--policybelong ,-- 'ҵ������ POLICY_INFORMATION.SALECOM', PAYENDYEAR,--payendyear ,-- '�ɷ����� POLICY_INFORMATION.PAYENDYEAR', APPFLAG, --����״̬ prem,--���� AMNT,--���� PAYTODATE,--�´νɷ����� polapplydate,--Ͷ������ AGENTCOM,--�������� SALECHNL,--�������� "�Ϻ������Ϻ��ֹ�˾" from shanghailifeecif.policy_information b where b.contno = a.policyno ) WHERE 1=1 ; */