CREATE OR REPLACE PACKAGE BODY NETS_TCIMS_PC_EXCH IS
/***********************************************************
--功能说明: 处理ITS成功件获取接口数据
--参数说明:
--调用函数:
--修改记录： create by zengjingchao001
--*********************************************************/
/*
--ITS系统表名
ITS成功件明细接口表(T_INT_SUCC_INFO);
ITS成功件批次表(T_INT_SUCC_BATCH)
ITS成功件险种信息表(T_INT_SUCC_DUTY)
*/
/*
IDL_SEP_SRC_DATA：产险入库基表
ITS成功件险种信息表：IDL_AST_SELL_SUCC_INSURE_INFO，存放从ITS同步的险种数据，不用转换，可直接导入BDL层
IDL_AST_SELL_SUCC_INFO：ITS成功件明细信息表，存放从ITS同步的数据
IDL_EX_BATCH：获取同步批次表
IDL_EXCH_SELL_SUCC_1_TMP:与 IDL_AST_SELL_SUCC_INFO 同构，存放待合并C01C51的成功件数据
IDL_EXCH_SELL_SUCC_2_TMP:与 IDL_SEP_SRC_DATA 同构，存放已合并C01C51的成功件数据，即待入库清洗数据
IDL_AST_SELL_SUCC_INFO_ARCH :ITS成功件明细信息表归档表,与表IDL_AST_SELL_SUCC_INFO同构，仅增加ARCH_ID字段,
IDL_RULE_ID_TYPE_EXCHANGE 证件类型转换规则表
二级机构/city对应表 BDL_RULE_SECOND_ORG_CITY
四级机构/city对应表 BDL_RULE_DEP_ORG_CITY
IDL_TASK_GROUP_ID_ARCH : 任务组ID归档表
IDL_EXCH_SELL_SUCC_3_TMP :与 IDL_AST_SELL_SUCC_INFO 同构，存放待合并C01C51的成功件数据
BDL_AGT_BUSINESS_DUTY_INFO ：商业险险别表
BDL_AGT_TRAFFIC_DUTY_INFO ： 交强险险别明细表
*/
PROCEDURE SP_EXCH_DEAL_SELL_SUCC_INFO IS
--查询上个月并且未处理的所有批次
CURSOR CUR_SUCC_BATCH_ID IS
SELECT T.TCIMS_BATCH_ID
FROM IDL_EX_BATCH T
WHERE T.CREATED_DATE <
TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, 0), 'yyyymm'), 'yyyymm') --当月1号
AND T.CREATED_DATE >=
TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyymm'), 'yyyymm') --上月1号
AND T.BATCH_STATUS = '01'
AND T.SRC_SYS_TYPE = '01';
V_DEAL_ITS_SUCC_INFO_DATE VARCHAR2(5); --每月开始处理ITS接口成功件的日期
BEGIN
SELECT T.PROPERTIES_VALUE
INTO V_DEAL_ITS_SUCC_INFO_DATE
FROM BDL_COM_PROPERTIES T
WHERE T.PROPERTIES_NAME = 'DEAL_ITS_SUCC_INFO_DATE';
--每月的1号才开始统一处理上个月未处理的所有成功件数据
IF SYSDATE >=
TO_DATE(TO_CHAR(SYSDATE, 'YYYYMM') || V_DEAL_ITS_SUCC_INFO_DATE,
'yyyymmdd') THEN
--清空临时表
--把所有批次数据都导入到临时表中，再统一处理
FOR SUCC_TCIMS_BATCH_ID IN CUR_SUCC_BATCH_ID LOOP
INSERT /*+APPEND*/
INTO IDL_EXCH_SELL_SUCC_ALL_TMP
(TCIMS_BATCH_ID,
POLICY_NO,
POLICY_BUSINESS_FLAG,
LAST_POLICY_NO,
DEPARTMENT_CODE,
DEPARTMENT_CHINESE_NAME,
SECONDARY_ORG,
THIRD_ORG,
CITY,
SALE_AGENT_CODE,
EMPLOYEE_NAME,
BUSINESS_SOURCE_CODE,
BUSINESS_SOURCE_NAME,
BUSINESS_SOURCE_DETAIL_CODE,
BUSINESS_SOURCE_DETAIL_NAME,
CHANNEL_SOURCE_CODE,
CHANNEL_SOURCE_NAME,
CHANNEL_SOURCE_DETAIL_CODE,
CHANNEL_SOURCE_DETAIL_NAME,
LAST_YEAR_APPLY_COMPANY,
VALUE_CHINESE_NAME,
APPLY_DAY,
INSURANCE_BEGIN_TIME,
INSURANCE_END_TIME,
TOTAL_ACTUAL_PREMIUM,
PREMIUM_INFO,
INPUT_BY,
APPLY_TIME,
UNDERWRITE_TIME,
INSURANT_PARTY_NO,
INSURANT_PERSONNEL_NAME,
INSURANT_POSTCODE,
INSURANT_ADDRESS,
INSURANT_CERTIFICATE_TYPE,
INSURANT_CERTIFICATE_NO,
INSURANT_SEX_CODE,
INSURANT_BIRTHDAY,
INSURANT_MOBILE_TELEPHONE,
INSURANT_HOME_TELEPHONE,
INSURANT_OFFICE_TELEPHONE,
INSURANT_EMAIL,
INSURANT_PROFESSION_CODE,
INSURANT_MARRIED_STATUS,
APPLICANT_PARTY_NO,
APPLICANT_PERSONNEL_NAME,
APPLICANT_CERTIFICATE_TYPE,
APPLICANT_CERTIFICATE_NO,
APPLICANT_BIRTHDAY,
APPLICANT_SEX_CODE,
APPLICANT_ADDRESS,
APPLICANT_POSTCODE,
APPLICANT_MOBILE_TELEPHONE,
APPLICANT_OFFICE_TELEPHONE,
APPLICANT_HOME_TELEPHONE,
APPLICANT_EMAIL,
APPLICANT_PERSONNEL_CODE,
APPLICANT_FIRST_ISSUE_DATE,
PROFESSION_CODE,
MARRIED_STATUS,
USAGE_ATTRIBUTE_CODE,
OWNERSHIP_ATTRIBUTE_CODE,
AUTO_MODEL_CODE,
BRAND_CHN_NAME,
AUTO_MODEL_CHN_NAME,
VEHICLE_TYPE,
FIRST_SALE_DATE,
VEHICLE_LICENCE_CODE,
EXHAUST_CAPABILITY,
LICENCE_TYPE_CODE,
ENGINE_NO,
VEHICLE_FRAME_NO,
FIRST_REGISTER_DATE,
PURCHASE_PRICE,
VEHICLE_TONNAGES,
WHOLE_WEIGHT,
VEHICLE_SEATS,
CAMPAIGN_NAME,
CAMPAIGN_ID,
BIZ_MODEL,
SUB_BIZMODEL,
EXPIRED_DATE,
CAMPAIGN_SPLIT_NAME,
CAMPAIGN_SPLIT_ID,
BATCH_NAME,
BATCH_ID,
TCIMS_CUST_ID,
TCIMS_VT_ID,
TASK_GROUP_ID,
TASK_ID)
SELECT /*+ PARALLEL(A,4) */
TCIMS_BATCH_ID,
POLICY_NO,
POLICY_BUSINESS_FLAG,
LAST_POLICY_NO,
DEPARTMENT_CODE,
DEPARTMENT_CHINESE_NAME,
SECONDARY_ORG,
THIRD_ORG,
CITY,
SALE_AGENT_CODE,
EMPLOYEE_NAME,
BUSINESS_SOURCE_CODE,
BUSINESS_SOURCE_NAME,
BUSINESS_SOURCE_DETAIL_CODE,
BUSINESS_SOURCE_DETAIL_NAME,
CHANNEL_SOURCE_CODE,
CHANNEL_SOURCE_NAME,
CHANNEL_SOURCE_DETAIL_CODE,
CHANNEL_SOURCE_DETAIL_NAME,
LAST_YEAR_APPLY_COMPANY,
VALUE_CHINESE_NAME,
APPLY_DAY,
INSURANCE_BEGIN_TIME,
INSURANCE_END_TIME,
TOTAL_ACTUAL_PREMIUM,
PREMIUM_INFO,
INPUT_BY,
APPLY_TIME,
UNDERWRITE_TIME,
INSURANT_PARTY_NO,
INSURANT_PERSONNEL_NAME,
INSURANT_POSTCODE,
INSURANT_ADDRESS,
INSURANT_CERTIFICATE_TYPE,
INSURANT_CERTIFICATE_NO,
INSURANT_SEX_CODE,
INSURANT_BIRTHDAY,
INSURANT_MOBILE_TELEPHONE,
INSURANT_HOME_TELEPHONE,
INSURANT_OFFICE_TELEPHONE,
INSURANT_EMAIL,
INSURANT_PROFESSION_CODE,
INSURANT_MARRIED_STATUS,
APPLICANT_PARTY_NO,
APPLICANT_PERSONNEL_NAME,
APPLICANT_CERTIFICATE_TYPE,
APPLICANT_CERTIFICATE_NO,
APPLICANT_BIRTHDAY,
APPLICANT_SEX_CODE,
APPLICANT_ADDRESS,
APPLICANT_POSTCODE,
APPLICANT_MOBILE_TELEPHONE,
APPLICANT_OFFICE_TELEPHONE,
APPLICANT_HOME_TELEPHONE,
APPLICANT_EMAIL,
APPLICANT_PERSONNEL_CODE,
APPLICANT_FIRST_ISSUE_DATE,
PROFESSION_CODE,
MARRIED_STATUS,
USAGE_ATTRIBUTE_CODE,
OWNERSHIP_ATTRIBUTE_CODE,
AUTO_MODEL_CODE,
BRAND_CHN_NAME,
AUTO_MODEL_CHN_NAME,
VEHICLE_TYPE,
FIRST_SALE_DATE,
VEHICLE_LICENCE_CODE,
EXHAUST_CAPABILITY,
LICENCE_TYPE_CODE,
ENGINE_NO,
VEHICLE_FRAME_NO,
FIRST_REGISTER_DATE,
PURCHASE_PRICE,
VEHICLE_TONNAGES,
WHOLE_WEIGHT,
VEHICLE_SEATS,
CAMPAIGN_NAME,
CAMPAIGN_ID,
BIZ_MODEL,
SUB_BIZMODEL,
EXPIRED_DATE,
CAMPAIGN_SPLIT_NAME,
CAMPAIGN_SPLIT_ID,
BATCH_NAME,
BATCH_ID,
TCIMS_CUST_ID,
TCIMS_VT_ID,
TASK_GROUP_ID,
TASK_ID
FROM IDL_AST_SELL_SUCC_INFO A
WHERE A.TCIMS_BATCH_ID = SUCC_TCIMS_BATCH_ID.TCIMS_BATCH_ID;
COMMIT;
--插入BDL层险种明细表 BDL_AGT_POLICY_DUTY_INFO
MERGE INTO BDL_AGT_POLICY_DUTY_INFO A
USING (SELECT *
FROM IDL_AST_SELL_SUCC_INSURE_INFO
WHERE TCIMS_BATCH_ID = SUCC_TCIMS_BATCH_ID.TCIMS_BATCH_ID) B
ON (A.POLICY_NO = B.POLICY_NO AND A.DUTY_CODE = B.DUTY_CODE)
WHEN MATCHED THEN
UPDATE
SET A.PLAN_CODE = B.PLAN_CODE,
A.PLAN_CHINESE_NAME = B.PLAN_CHINESE_NAME,
A.DUTY_CHINESE_NAME = B.DUTY_CHINESE_NAME,
A.INSURED_AMOUNT = B.INSURED_AMOUNT,
A.TOTAL_ACTUAL_PREMIUM = B.TOTAL_ACTUAL_PREMIUM,
A.UPDATED_DATE = SYSDATE,
A.UPDATED_BY = 'SYSTEM'
WHEN NOT MATCHED THEN
INSERT
VALUES
(B.POLICY_NO,
B.PLAN_CODE,
B.PLAN_CHINESE_NAME,
B.DUTY_CHINESE_NAME,
B.DUTY_CODE,
B.INSURED_AMOUNT,
B.TOTAL_ACTUAL_PREMIUM,
SYSDATE,
SYSDATE,
'SYSTEM',
'SYSTEM');
COMMIT;
--归档险别表
INSERT INTO IDL_AST_SELL_SUCC_INSURE_ARCH
(ARCH_ID,
TCIMS_BATCH_ID,
POLICY_NO,
PLAN_CODE,
PLAN_CHINESE_NAME,
DUTY_CHINESE_NAME,
DUTY_CODE,
INSURED_AMOUNT,
TOTAL_ACTUAL_PREMIUM,
CREATED_DATE,
CREATED_BY,
UPDATED_DATE,
UPDATED_BY)
SELECT SEQ_I_AST_SUCC_INSURE_ARCHID.NEXTVAL,
TCIMS_BATCH_ID,
POLICY_NO,
PLAN_CODE,
PLAN_CHINESE_NAME,
DUTY_CHINESE_NAME,
DUTY_CODE,
INSURED_AMOUNT,
TOTAL_ACTUAL_PREMIUM,
CREATED_DATE,
CREATED_BY,
UPDATED_DATE,
UPDATED_BY
FROM IDL_AST_SELL_SUCC_INSURE_INFO T
WHERE T.TCIMS_BATCH_ID = SUCC_TCIMS_BATCH_ID.TCIMS_BATCH_ID;
COMMIT;
--删除险种表
DELETE FROM IDL_AST_SELL_SUCC_INSURE_INFO T
WHERE T.TCIMS_BATCH_ID = SUCC_TCIMS_BATCH_ID.TCIMS_BATCH_ID;
COMMIT;
END LOOP;
--将数据导入到同构表中
--存放商业险C0的数据
INSERT /*+APPEND*/
INTO IDL_EXCH_SELL_SUCC_1_TMP
(TCIMS_BATCH_ID,
POLICY_NO,
POLICY_BUSINESS_FLAG,
LAST_POLICY_NO,
DEPARTMENT_CODE,
DEPARTMENT_CHINESE_NAME,
SALE_AGENT_CODE,
EMPLOYEE_NAME,
BUSINESS_SOURCE_CODE,
BUSINESS_SOURCE_NAME,
BUSINESS_SOURCE_DETAIL_CODE,
BUSINESS_SOURCE_DETAIL_NAME,
CHANNEL_SOURCE_CODE,
CHANNEL_SOURCE_NAME,
CHANNEL_SOURCE_DETAIL_CODE,
CHANNEL_SOURCE_DETAIL_NAME,
LAST_YEAR_APPLY_COMPANY,
VALUE_CHINESE_NAME,
APPLY_DAY,
INSURANCE_BEGIN_TIME,
INSURANCE_END_TIME,
TOTAL_ACTUAL_PREMIUM,
PREMIUM_INFO,
INPUT_BY,
APPLY_TIME,
UNDERWRITE_TIME,
INSURANT_PARTY_NO,
INSURANT_PERSONNEL_NAME,
INSURANT_POSTCODE,
INSURANT_ADDRESS,
INSURANT_CERTIFICATE_TYPE,
INSURANT_CERTIFICATE_NO,
INSURANT_SEX_CODE,
INSURANT_BIRTHDAY,
INSURANT_MOBILE_TELEPHONE,
INSURANT_HOME_TELEPHONE,
INSURANT_OFFICE_TELEPHONE,
INSURANT_EMAIL,
INSURANT_PROFESSION_CODE,
INSURANT_MARRIED_STATUS,
APPLICANT_PARTY_NO,
APPLICANT_PERSONNEL_NAME,
APPLICANT_CERTIFICATE_TYPE,
APPLICANT_CERTIFICATE_NO,
APPLICANT_BIRTHDAY,
APPLICANT_SEX_CODE,
APPLICANT_ADDRESS,
APPLICANT_POSTCODE,
APPLICANT_MOBILE_TELEPHONE,
APPLICANT_OFFICE_TELEPHONE,
APPLICANT_HOME_TELEPHONE,
APPLICANT_EMAIL,
APPLICANT_PERSONNEL_CODE,
APPLICANT_FIRST_ISSUE_DATE,
PROFESSION_CODE,
MARRIED_STATUS,
USAGE_ATTRIBUTE_CODE,
OWNERSHIP_ATTRIBUTE_CODE,
AUTO_MODEL_CODE,
BRAND_CHN_NAME,
AUTO_MODEL_CHN_NAME,
VEHICLE_TYPE,
FIRST_SALE_DATE,
VEHICLE_LICENCE_CODE,
EXHAUST_CAPABILITY,
LICENCE_TYPE_CODE,
ENGINE_NO,
VEHICLE_FRAME_NO,
FIRST_REGISTER_DATE,
PURCHASE_PRICE,
VEHICLE_TONNAGES,
WHOLE_WEIGHT,
VEHICLE_SEATS,
CAMPAIGN_NAME,
BIZ_MODEL,
SUB_BIZMODEL,
EXPIRED_DATE,
CAMPAIGN_SPLIT_NAME,
BATCH_NAME,
TCIMS_CUST_ID,
TCIMS_VT_ID,
TASK_GROUP_ID,
TASK_ID,
CREATED_DATE,
CREATED_BY,
UPDATED_DATE,
UPDATED_BY)
SELECT /*+ PARALLEL(A,2) */
TCIMS_BATCH_ID,
POLICY_NO,
POLICY_BUSINESS_FLAG,
LAST_POLICY_NO,
DEPARTMENT_CODE,
DEPARTMENT_CHINESE_NAME,
SALE_AGENT_CODE,
EMPLOYEE_NAME,
BUSINESS_SOURCE_CODE,
BUSINESS_SOURCE_NAME,
BUSINESS_SOURCE_DETAIL_CODE,
BUSINESS_SOURCE_DETAIL_NAME,
CHANNEL_SOURCE_CODE,
CHANNEL_SOURCE_NAME,
CHANNEL_SOURCE_DETAIL_CODE,
CHANNEL_SOURCE_DETAIL_NAME,
LAST_YEAR_APPLY_COMPANY,
VALUE_CHINESE_NAME,
APPLY_DAY,
INSURANCE_BEGIN_TIME,
INSURANCE_END_TIME,
TOTAL_ACTUAL_PREMIUM,
PREMIUM_INFO,
INPUT_BY,
APPLY_TIME,
UNDERWRITE_TIME,
INSURANT_PARTY_NO,
INSURANT_PERSONNEL_NAME,
INSURANT_POSTCODE,
INSURANT_ADDRESS,
INSURANT_CERTIFICATE_TYPE,
INSURANT_CERTIFICATE_NO,
INSURANT_SEX_CODE,
INSURANT_BIRTHDAY,
INSURANT_MOBILE_TELEPHONE,
INSURANT_HOME_TELEPHONE,
INSURANT_OFFICE_TELEPHONE,
INSURANT_EMAIL,
INSURANT_PROFESSION_CODE,
INSURANT_MARRIED_STATUS,
APPLICANT_PARTY_NO,
APPLICANT_PERSONNEL_NAME,
APPLICANT_CERTIFICATE_TYPE,
APPLICANT_CERTIFICATE_NO,
APPLICANT_BIRTHDAY,
APPLICANT_SEX_CODE,
APPLICANT_ADDRESS,
APPLICANT_POSTCODE,
APPLICANT_MOBILE_TELEPHONE,
APPLICANT_OFFICE_TELEPHONE,
APPLICANT_HOME_TELEPHONE,
APPLICANT_EMAIL,
APPLICANT_PERSONNEL_CODE,
APPLICANT_FIRST_ISSUE_DATE,
PROFESSION_CODE,
MARRIED_STATUS,
USAGE_ATTRIBUTE_CODE,
OWNERSHIP_ATTRIBUTE_CODE,
AUTO_MODEL_CODE,
BRAND_CHN_NAME,
AUTO_MODEL_CHN_NAME,
VEHICLE_TYPE,
FIRST_SALE_DATE,
VEHICLE_LICENCE_CODE,
EXHAUST_CAPABILITY,
LICENCE_TYPE_CODE,
ENGINE_NO,
VEHICLE_FRAME_NO,
FIRST_REGISTER_DATE,
PURCHASE_PRICE,
VEHICLE_TONNAGES,
WHOLE_WEIGHT,
VEHICLE_SEATS,
CAMPAIGN_NAME,
BIZ_MODEL,
SUB_BIZMODEL,
EXPIRED_DATE,
CAMPAIGN_SPLIT_NAME,
BATCH_NAME,
TCIMS_CUST_ID,
TCIMS_VT_ID,
TASK_GROUP_ID,
TASK_ID,
CREATED_DATE,
CREATED_BY,
UPDATED_DATE,
UPDATED_BY
FROM IDL_EXCH_SELL_SUCC_ALL_TMP A
WHERE A.POLICY_BUSINESS_FLAG = 'C0';
--存放交强险C5的数据
INSERT /*+APPEND*/
INTO IDL_EXCH_SELL_SUCC_3_TMP
(TCIMS_BATCH_ID,
POLICY_NO,
POLICY_BUSINESS_FLAG,
LAST_POLICY_NO,
DEPARTMENT_CODE,
DEPARTMENT_CHINESE_NAME,
SALE_AGENT_CODE,
EMPLOYEE_NAME,
BUSINESS_SOURCE_CODE,
BUSINESS_SOURCE_NAME,
BUSINESS_SOURCE_DETAIL_CODE,
BUSINESS_SOURCE_DETAIL_NAME,
CHANNEL_SOURCE_CODE,
CHANNEL_SOURCE_NAME,
CHANNEL_SOURCE_DETAIL_CODE,
CHANNEL_SOURCE_DETAIL_NAME,
LAST_YEAR_APPLY_COMPANY,
VALUE_CHINESE_NAME,
APPLY_DAY,
INSURANCE_BEGIN_TIME,
INSURANCE_END_TIME,
TOTAL_ACTUAL_PREMIUM,
PREMIUM_INFO,
INPUT_BY,
APPLY_TIME,
UNDERWRITE_TIME,
INSURANT_PARTY_NO,
INSURANT_PERSONNEL_NAME,
INSURANT_POSTCODE,
INSURANT_ADDRESS,
INSURANT_CERTIFICATE_TYPE,
INSURANT_CERTIFICATE_NO,
INSURANT_SEX_CODE,
INSURANT_BIRTHDAY,
INSURANT_MOBILE_TELEPHONE,
INSURANT_HOME_TELEPHONE,
INSURANT_OFFICE_TELEPHONE,
INSURANT_EMAIL,
INSURANT_PROFESSION_CODE,
INSURANT_MARRIED_STATUS,
APPLICANT_PARTY_NO,
APPLICANT_PERSONNEL_NAME,
APPLICANT_CERTIFICATE_TYPE,
APPLICANT_CERTIFICATE_NO,
APPLICANT_BIRTHDAY,
APPLICANT_SEX_CODE,
APPLICANT_ADDRESS,
APPLICANT_POSTCODE,
APPLICANT_MOBILE_TELEPHONE,
APPLICANT_OFFICE_TELEPHONE,
APPLICANT_HOME_TELEPHONE,
APPLICANT_EMAIL,
APPLICANT_PERSONNEL_CODE,
APPLICANT_FIRST_ISSUE_DATE,
PROFESSION_CODE,
MARRIED_STATUS,
USAGE_ATTRIBUTE_CODE,
OWNERSHIP_ATTRIBUTE_CODE,
AUTO_MODEL_CODE,
BRAND_CHN_NAME,
AUTO_MODEL_CHN_NAME,
VEHICLE_TYPE,
FIRST_SALE_DATE,
VEHICLE_LICENCE_CODE,
EXHAUST_CAPABILITY,
LICENCE_TYPE_CODE,
ENGINE_NO,
VEHICLE_FRAME_NO,
FIRST_REGISTER_DATE,
PURCHASE_PRICE,
VEHICLE_TONNAGES,
WHOLE_WEIGHT,
VEHICLE_SEATS,
CAMPAIGN_NAME,
BIZ_MODEL,
SUB_BIZMODEL,
EXPIRED_DATE,
CAMPAIGN_SPLIT_NAME,
BATCH_NAME,
TCIMS_CUST_ID,
TCIMS_VT_ID,
TASK_GROUP_ID,
TASK_ID,
CREATED_DATE,
CREATED_BY,
UPDATED_DATE,
UPDATED_BY)
SELECT /*+ PARALLEL(A,2) */
TCIMS_BATCH_ID,
POLICY_NO,
POLICY_BUSINESS_FLAG,
LAST_POLICY_NO,
DEPARTMENT_CODE,
DEPARTMENT_CHINESE_NAME,
SALE_AGENT_CODE,
EMPLOYEE_NAME,
BUSINESS_SOURCE_CODE,
BUSINESS_SOURCE_NAME,
BUSINESS_SOURCE_DETAIL_CODE,
BUSINESS_SOURCE_DETAIL_NAME,
CHANNEL_SOURCE_CODE,
CHANNEL_SOURCE_NAME,
CHANNEL_SOURCE_DETAIL_CODE,
CHANNEL_SOURCE_DETAIL_NAME,
LAST_YEAR_APPLY_COMPANY,
VALUE_CHINESE_NAME,
APPLY_DAY,
INSURANCE_BEGIN_TIME,
INSURANCE_END_TIME,
TOTAL_ACTUAL_PREMIUM,
PREMIUM_INFO,
INPUT_BY,
APPLY_TIME,
UNDERWRITE_TIME,
INSURANT_PARTY_NO,
INSURANT_PERSONNEL_NAME,
INSURANT_POSTCODE,
INSURANT_ADDRESS,
INSURANT_CERTIFICATE_TYPE,
INSURANT_CERTIFICATE_NO,
INSURANT_SEX_CODE,
INSURANT_BIRTHDAY,
INSURANT_MOBILE_TELEPHONE,
INSURANT_HOME_TELEPHONE,
INSURANT_OFFICE_TELEPHONE,
INSURANT_EMAIL,
INSURANT_PROFESSION_CODE,
INSURANT_MARRIED_STATUS,
APPLICANT_PARTY_NO,
APPLICANT_PERSONNEL_NAME,
APPLICANT_CERTIFICATE_TYPE,
APPLICANT_CERTIFICATE_NO,
APPLICANT_BIRTHDAY,
APPLICANT_SEX_CODE,
APPLICANT_ADDRESS,
APPLICANT_POSTCODE,
APPLICANT_MOBILE_TELEPHONE,
APPLICANT_OFFICE_TELEPHONE,
APPLICANT_HOME_TELEPHONE,
APPLICANT_EMAIL,
APPLICANT_PERSONNEL_CODE,
APPLICANT_FIRST_ISSUE_DATE,
PROFESSION_CODE,
MARRIED_STATUS,
USAGE_ATTRIBUTE_CODE,
OWNERSHIP_ATTRIBUTE_CODE,
AUTO_MODEL_CODE,
BRAND_CHN_NAME,
AUTO_MODEL_CHN_NAME,
VEHICLE_TYPE,
FIRST_SALE_DATE,
VEHICLE_LICENCE_CODE,
EXHAUST_CAPABILITY,
LICENCE_TYPE_CODE,
ENGINE_NO,
VEHICLE_FRAME_NO,
FIRST_REGISTER_DATE,
PURCHASE_PRICE,
VEHICLE_TONNAGES,
WHOLE_WEIGHT,
VEHICLE_SEATS,
CAMPAIGN_NAME,
BIZ_MODEL,
SUB_BIZMODEL,
EXPIRED_DATE,
CAMPAIGN_SPLIT_NAME,
BATCH_NAME,
TCIMS_CUST_ID,
TCIMS_VT_ID,
TASK_GROUP_ID,
TASK_ID,
CREATED_DATE,
CREATED_BY,
UPDATED_DATE,
UPDATED_BY
FROM IDL_EXCH_SELL_SUCC_ALL_TMP A
WHERE A.POLICY_BUSINESS_FLAG = 'C5';
COMMIT;
--合并商业险、交强险成功件数据，查找C01存在的数据或C01C51都存在的数据，并插入到合并后成功件表中
--以C01商业险数据为基准
--根据所属性质转换证件类型代码（IDL_AST_SELL_SUCC_INFO.OWNERSHIP_ATTRIBUTE_CODE），转投保人的证件类型
INSERT /*+APPEND*/
INTO IDL_SEP_SRC_DATA
(SYS_ID,
TCIMS_BATCH_ID, --2
TCIMS_CUST_ID, --3
TCIMS_VT_ID, --4
APPLICANT_PARTY_NO, --5
CUST_NAME, --6
SEX, --7
MARITAL_STATUS, --8
CUST_DOB, --9
POSITION, --10
ID_TYPE, --11
ID_NUMBER, --12
CONTACT_NAME, --13
TELEPHONE_NUMBER, --14
APPLICANT_EMAIL, --15
ZIP_CODE, --16
ADDRESS, --17
VEHICLE_NO, --18
BRAND_TYPE_CODE, --19
USAGE_CODE, --20
ATTRIBUTE_CODE, --21
VEHICLE_TYPE, --22
BRAND_TYPE_FIRST_SALE_DATE, --23
EXHAUST, --24
ENGINE_NUMBER, --25
VEHICLE_FRAME, --26
FIRST_REGISTER_DATE, --27
VEHICLE_OWNER, --28
LICENSE_ISSUE_DATE, --29
INSURED_PERSON_NAME, --30
INSURED_PERSON_ADDRESS, --31
C01_POLICY_NO, --32
C01_DEPARTMENT_CODE, --33
C01_DEPARTMENT_CHINESE_NAME, --34
C01_LAST_POLICY_NO, --35
C01_LAST_YEAR_APPLY_COMPANY, --36
C01_VALUE_CHINESE_NAME, --37
C01_SALE_AGENT_CODE, --38
C01_EMPLOYEE_NAME, --39
C01_BUSINESS_SRC_CODE, --40
C01_BUSINESS_SRC_NAME, --41
C01_BUSINESS_SRC_DETAIL_CODE, --42
C01_BUSINESS_SRC_DETAIL_NAME, --43
C01_CHANNEL_SRC_CODE, --44
C01_CHANNEL_SRC_NAME, --45
C01_CHANNEL_SRC_DETAIL_CODE, --46
C01_CHANNEL_SRC_DETAIL_NAME, --47
C01_INSURANCE_BEGIN_TIME, --48
C01_INSURANCE_END_TIME, --49
C01_TOTAL_ACTUAL_PREMIUM, --50
C01_PREMIUM_INFO, --51
C01_APPLY_TIME, --52
C01_UNDERWRITE_TIME, --53
C01_INPUT_BY, --54
C51_POLICY_NO, --55
C51_DEPARTMENT_CODE, --56
C51_DEPARTMENT_CHINESE_NAME, --57
C51_LAST_POLICY_NO, --58
C51_LAST_YEAR_APPLY_COMPANY, --59
C51_VALUE_CHINESE_NAME, --60
C51_SALE_AGENT_CODE, --61
C51_EMPLOYEE_NAME, --62
C51_BUSINESS_SRC_CODE, --63
C51_BUSINESS_SRC_NAME, --64
C51_BUSINESS_SRC_DETAIL_CODE, --65
C51_BUSINESS_SRC_DETAIL_NAME, --66
C51_CHANNEL_SRC_CODE, --67
C51_CHANNEL_SRC_NAME, --68
C51_CHANNEL_SRC_DETAIL_CODE, --69
C51_CHANNEL_SRC_DETAIL_NAME, --70
C51_INSURANCE_BEGIN_TIME, --71
C51_INSURANCE_END_TIME, --72
C51_TOTAL_ACTUAL_PREMIUM, --73
C51_PREMIUM_INFO, --74
C51_APPLY_TIME, --75
C51_UNDERWRITE_TIME, --76
C51_INPUT_BY, --77
BIZ_MODEL, --78
SUB_BIZMODEL, --79
EXPIRED_DATE, --80
CAMPAIGN_NAME, --81
CAMPAIGN_SPLIT_NAME, --82
BATCH_NAME, --83
TASK_GROUP_ID, --84
CITY,
SECONDARY_ORG,
THIRD_ORG,
SRC_TYPE,
LIST_TYPE,
TASK_ID, --85
CREATED_DATE,
CREATED_BY,
UPDATED_DATE,
UPDATED_BY)
SELECT /*+ PARALLEL(A,2) PARALLEL(B,2) PARALLEL(C,2)*/
SEQ_IDL_SEP_SRC_DATA_SYSID.NEXTVAL,
A.TCIMS_BATCH_ID, --2
A.TCIMS_CUST_ID, --3
A.TCIMS_VT_ID, --4
A.APPLICANT_PARTY_NO, --5
A.APPLICANT_PERSONNEL_NAME, --6
A.APPLICANT_SEX_CODE, --7
A.MARRIED_STATUS, --8
A.APPLICANT_BIRTHDAY, --9
A.PROFESSION_CODE, --10
C.ID_TYPE_EXCHANGED, --11 证件类型
A.APPLICANT_CERTIFICATE_NO, --12
A.INSURANT_PERSONNEL_NAME, --13
SUBSTR(A.INSURANT_MOBILE_TELEPHONE || '/' ||
A.INSURANT_HOME_TELEPHONE || '/' ||
A.INSURANT_OFFICE_TELEPHONE || '/' ||
A.APPLICANT_MOBILE_TELEPHONE || '/' ||
A.APPLICANT_OFFICE_TELEPHONE || '/' ||
A.APPLICANT_HOME_TELEPHONE,
1,
200), --14 合并电话号码
A.APPLICANT_EMAIL, --15
A.APPLICANT_POSTCODE, --16
A.APPLICANT_ADDRESS, --17
A.VEHICLE_LICENCE_CODE, --18
A.LICENCE_TYPE_CODE, --19
A.USAGE_ATTRIBUTE_CODE, --20
A.OWNERSHIP_ATTRIBUTE_CODE, --21
A.VEHICLE_TYPE, --22
A.FIRST_SALE_DATE, --23
A.EXHAUST_CAPABILITY, --24
A.ENGINE_NO, --25
A.VEHICLE_FRAME_NO, --26
A.FIRST_REGISTER_DATE, --27
A.APPLICANT_PERSONNEL_CODE, --28
A.APPLICANT_FIRST_ISSUE_DATE, --29
A.INSURANT_PERSONNEL_NAME, --30
A.INSURANT_ADDRESS, --31
A.POLICY_NO, --32
A.DEPARTMENT_CODE, --33
A.DEPARTMENT_CHINESE_NAME, --34
A.LAST_POLICY_NO, --35
A.LAST_YEAR_APPLY_COMPANY, --36
A.VALUE_CHINESE_NAME, --37
A.SALE_AGENT_CODE, --38
A.EMPLOYEE_NAME, --39
A.BUSINESS_SOURCE_CODE, --40
A.BUSINESS_SOURCE_NAME, --41
A.BUSINESS_SOURCE_DETAIL_CODE, --42
A.BUSINESS_SOURCE_DETAIL_NAME, --43
A.CHANNEL_SOURCE_CODE, --44
A.CHANNEL_SOURCE_NAME, --45
A.CHANNEL_SOURCE_DETAIL_CODE, --46
A.CHANNEL_SOURCE_DETAIL_NAME, --47
A.INSURANCE_BEGIN_TIME, --48
A.INSURANCE_END_TIME, --49
A.TOTAL_ACTUAL_PREMIUM, --50
A.PREMIUM_INFO, --51
A.APPLY_TIME, --52
A.UNDERWRITE_TIME, --53
A.INPUT_BY, --54
B.POLICY_NO, --55
B.DEPARTMENT_CODE, --56
B.DEPARTMENT_CHINESE_NAME, --57
B.LAST_POLICY_NO, --58
B.LAST_YEAR_APPLY_COMPANY, --59
B.VALUE_CHINESE_NAME, --60
B.SALE_AGENT_CODE, --61
B.EMPLOYEE_NAME, --62
B.BUSINESS_SOURCE_CODE, --63
B.BUSINESS_SOURCE_NAME, --64
B.BUSINESS_SOURCE_DETAIL_CODE, --65
B.BUSINESS_SOURCE_DETAIL_NAME, --66
B.CHANNEL_SOURCE_CODE, --67
B.CHANNEL_SOURCE_NAME, --68
B.CHANNEL_SOURCE_DETAIL_CODE, --69
B.CHANNEL_SOURCE_DETAIL_NAME, --70
B.INSURANCE_BEGIN_TIME, --71
B.INSURANCE_END_TIME, --72
B.TOTAL_ACTUAL_PREMIUM, --73
B.PREMIUM_INFO, --74
B.APPLY_TIME, --75
B.UNDERWRITE_TIME, --76
B.INPUT_BY, --77
A.BIZ_MODEL, --78
A.SUB_BIZMODEL, --79
A.EXPIRED_DATE, --80
A.CAMPAIGN_NAME, --81
A.CAMPAIGN_SPLIT_NAME, --82
A.BATCH_NAME, --83
A.TASK_GROUP_ID, --84
DECODE(A.CITY, NULL, D.CITY_CODE, A.CITY), --转换CITY等
DECODE(A.CITY, NULL, E.SECOND_CODE, A.SECONDARY_ORG),
DECODE(A.CITY, NULL, E.THIRD_CODE, A.THIRD_ORG),
'XB',
'XB',
A.TASK_ID, --85
SYSDATE,
'SYSTEM',
SYSDATE,
'SYSTEM'
FROM IDL_EXCH_SELL_SUCC_1_TMP A,
IDL_EXCH_SELL_SUCC_3_TMP B,
BDL_RULE_ID_TYPE_EXCHANGE C,
BDL_RULE_DEP_ORG_CITY D,
BDL_COM_DISTRICT_ORG E
WHERE A.OWNERSHIP_ATTRIBUTE_CODE = C.ATTRIBUTE_CODE(+) --转换证件类型
AND A.INSURANT_CERTIFICATE_TYPE = C.ID_TYPE_OLD
AND A.DEPARTMENT_CODE = D.DEPARTMENT_CODE
AND D.CITY_CODE = E.CITY_CODE --转换CITY*/
AND A.TASK_GROUP_ID = B.TASK_GROUP_ID(+);
COMMIT;
--合并商业险、交强险成功件数据，查找仅有C5存在的客户，并插入到合并后成功件表中
INSERT /*+APPEND*/
INTO IDL_SEP_SRC_DATA
(SYS_ID, --1
TCIMS_BATCH_ID, --2
TCIMS_CUST_ID, --3
TCIMS_VT_ID, --4
APPLICANT_PARTY_NO, --5
CUST_NAME, --6
SEX, --7
MARITAL_STATUS, --8
CUST_DOB, --9
POSITION, --10
ID_TYPE, --11
ID_NUMBER, --12
CONTACT_NAME, --13
TELEPHONE_NUMBER, --14
APPLICANT_EMAIL, --15
ZIP_CODE, --16
ADDRESS, --17
VEHICLE_NO, --18
BRAND_TYPE_CODE, --19
USAGE_CODE, --20
ATTRIBUTE_CODE, --21
VEHICLE_TYPE, --22
BRAND_TYPE_FIRST_SALE_DATE, --23
EXHAUST, --24
ENGINE_NUMBER, --25
VEHICLE_FRAME, --26
FIRST_REGISTER_DATE, --27
VEHICLE_OWNER, --28
LICENSE_ISSUE_DATE, --29
INSURED_PERSON_NAME, --30
INSURED_PERSON_ADDRESS, --31
C51_POLICY_NO, --32
C51_DEPARTMENT_CODE, --33
C51_DEPARTMENT_CHINESE_NAME, --34
C51_LAST_POLICY_NO, --35
C51_LAST_YEAR_APPLY_COMPANY, --36
C51_VALUE_CHINESE_NAME, --37
C51_SALE_AGENT_CODE, --38
C51_EMPLOYEE_NAME, --39
C51_BUSINESS_SRC_CODE, --40
C51_BUSINESS_SRC_NAME, --41
C51_BUSINESS_SRC_DETAIL_CODE, --42
C51_BUSINESS_SRC_DETAIL_NAME, --43
C51_CHANNEL_SRC_CODE, --44
C51_CHANNEL_SRC_NAME, --45
C51_CHANNEL_SRC_DETAIL_CODE, --46
C51_CHANNEL_SRC_DETAIL_NAME, --47
C51_INSURANCE_BEGIN_TIME, --48
C51_INSURANCE_END_TIME, --49
C51_TOTAL_ACTUAL_PREMIUM, --50
C51_PREMIUM_INFO, --51
C51_APPLY_TIME, --52
C51_UNDERWRITE_TIME, --53
C51_INPUT_BY, --54
BIZ_MODEL, --55
SUB_BIZMODEL, --56
EXPIRED_DATE, --57
CAMPAIGN_NAME, --58
CAMPAIGN_SPLIT_NAME, --59
BATCH_NAME, --60
TASK_GROUP_ID, --61
CITY,
SECONDARY_ORG,
THIRD_ORG,
SRC_TYPE,
LIST_TYPE,
TASK_ID, --62
CREATED_DATE,
CREATED_BY,
UPDATED_DATE,
UPDATED_BY)
SELECT /*+ PARALLEL(A,2) PARALLEL(B,2) PARALLEL(C,2)*/
SEQ_IDL_SEP_SRC_DATA_SYSID.NEXTVAL,
A.TCIMS_BATCH_ID, --2
A.TCIMS_CUST_ID, --3
A.TCIMS_VT_ID, --4
A.APPLICANT_PARTY_NO, --5
A.APPLICANT_PERSONNEL_NAME, --6
A.APPLICANT_SEX_CODE, --7
A.MARRIED_STATUS, --8
A.APPLICANT_BIRTHDAY, --9
A.PROFESSION_CODE, --10
C.ID_TYPE_EXCHANGED, --11
A.APPLICANT_CERTIFICATE_NO, --12
A.INSURANT_PERSONNEL_NAME, --13
SUBSTR(A.INSURANT_MOBILE_TELEPHONE || '/' ||
A.INSURANT_HOME_TELEPHONE || '/' ||
A.INSURANT_OFFICE_TELEPHONE || '/' ||
A.APPLICANT_MOBILE_TELEPHONE || '/' ||
A.APPLICANT_OFFICE_TELEPHONE || '/' ||
A.APPLICANT_HOME_TELEPHONE,
1,
200), --14 合并电话号码
A.APPLICANT_EMAIL, --15
A.APPLICANT_POSTCODE, --16
A.APPLICANT_ADDRESS, --17
A.VEHICLE_LICENCE_CODE, --18
A.LICENCE_TYPE_CODE, --19
A.USAGE_ATTRIBUTE_CODE, --20
A.OWNERSHIP_ATTRIBUTE_CODE, --21
A.VEHICLE_TYPE, --22
A.FIRST_SALE_DATE, --23
A.EXHAUST_CAPABILITY, --24
A.ENGINE_NO, --25
A.VEHICLE_FRAME_NO, --26
A.FIRST_REGISTER_DATE, --27
A.APPLICANT_PERSONNEL_CODE, --28
A.APPLICANT_FIRST_ISSUE_DATE, --29
A.INSURANT_PERSONNEL_NAME, --30
A.INSURANT_ADDRESS, --31
A.POLICY_NO, --32
A.DEPARTMENT_CODE, --33
A.DEPARTMENT_CHINESE_NAME, --34
A.LAST_POLICY_NO, --35
A.LAST_YEAR_APPLY_COMPANY, --36
A.VALUE_CHINESE_NAME, --37
A.SALE_AGENT_CODE, --38
A.EMPLOYEE_NAME, --39
A.BUSINESS_SOURCE_CODE, --40
A.BUSINESS_SOURCE_NAME, --41
A.BUSINESS_SOURCE_DETAIL_CODE, --42
A.BUSINESS_SOURCE_DETAIL_NAME, --43
A.CHANNEL_SOURCE_CODE, --44
A.CHANNEL_SOURCE_NAME, --45
A.CHANNEL_SOURCE_DETAIL_CODE, --46
A.CHANNEL_SOURCE_DETAIL_NAME, --47
A.INSURANCE_BEGIN_TIME, --48
A.INSURANCE_END_TIME, --49
A.TOTAL_ACTUAL_PREMIUM, --50
A.PREMIUM_INFO, --51
A.APPLY_TIME, --52
A.UNDERWRITE_TIME, --53
A.INPUT_BY, --54
A.BIZ_MODEL, --55
A.SUB_BIZMODEL, --56
A.EXPIRED_DATE, --57
A.CAMPAIGN_NAME, --58
A.CAMPAIGN_SPLIT_NAME, --59
A.BATCH_NAME, --60
A.TASK_GROUP_ID, --61
DECODE(A.CITY, NULL, D.CITY_CODE, A.CITY), --转换CITY等
DECODE(A.CITY, NULL, E.SECOND_CODE, A.SECONDARY_ORG),
DECODE(A.CITY, NULL, E.THIRD_CODE, A.THIRD_ORG),
'XB',
'XB', --根据业务模式、细分（代码）转换来源类型、名单类型—成功件都是XB
A.TASK_ID, --62
SYSDATE,
'SYSTEM',
SYSDATE,
'SYSTEM'
FROM IDL_EXCH_SELL_SUCC_3_TMP A,
BDL_RULE_ID_TYPE_EXCHANGE C,
BDL_RULE_DEP_ORG_CITY D,
BDL_COM_DISTRICT_ORG E
WHERE A.OWNERSHIP_ATTRIBUTE_CODE = C.ATTRIBUTE_CODE(+) --转换证件类型
AND A.INSURANT_CERTIFICATE_TYPE = C.ID_TYPE_OLD
AND A.DEPARTMENT_CODE = D.DEPARTMENT_CODE
AND D.CITY_CODE = E.CITY_CODE --转换CITY
AND NOT EXISTS
(SELECT 1
FROM IDL_EXCH_SELL_SUCC_1_TMP B
WHERE A.TASK_GROUP_ID = B.TASK_GROUP_ID);
COMMIT;
--CITY转换已完毕
--已合并
--放入归档表
INSERT /*+APPEND*/
INTO IDL_AST_SELL_SUCC_INFO_ARCH
(ARCH_ID,
TCIMS_BATCH_ID,
POLICY_NO,
POLICY_BUSINESS_FLAG,
LAST_POLICY_NO,
DEPARTMENT_CHINESE_NAME,
SALE_AGENT_CODE,
BUSINESS_SOURCE_NAME,
BUSINESS_SOURCE_DETAIL_CODE,
BUSINESS_SOURCE_DETAIL_NAME,
CHANNEL_SOURCE_CODE,
CHANNEL_SOURCE_NAME,
CHANNEL_SOURCE_DETAIL_CODE,
CHANNEL_SOURCE_DETAIL_NAME,
LAST_YEAR_APPLY_COMPANY,
VALUE_CHINESE_NAME,
APPLY_DAY,
INSURANCE_BEGIN_TIME,
INSURANCE_END_TIME,
TOTAL_ACTUAL_PREMIUM,
PREMIUM_INFO,
INPUT_BY,
UNDERWRITE_TIME,
INSURANT_PARTY_NO,
INSURANT_PERSONNEL_NAME,
INSURANT_CERTIFICATE_TYPE,
INSURANT_BIRTHDAY,
INSURANT_PROFESSION_CODE,
APPLICANT_PERSONNEL_NAME,
APPLICANT_ADDRESS,
APPLICANT_POSTCODE,
APPLICANT_PERSONNEL_CODE,
APPLICANT_FIRST_ISSUE_DATE,
PROFESSION_CODE,
MARRIED_STATUS,
USAGE_ATTRIBUTE_CODE,
OWNERSHIP_ATTRIBUTE_CODE,
AUTO_MODEL_CODE,
BRAND_CHN_NAME,
AUTO_MODEL_CHN_NAME,
VEHICLE_TYPE,
FIRST_SALE_DATE,
VEHICLE_LICENCE_CODE,
EXHAUST_CAPABILITY,
LICENCE_TYPE_CODE,
ENGINE_NO,
VEHICLE_FRAME_NO,
PURCHASE_PRICE,
VEHICLE_TONNAGES,
WHOLE_WEIGHT,
VEHICLE_SEATS,
CAMPAIGN_ID,
BIZ_MODEL,
SUB_BIZMODEL,
EXPIRED_DATE,
CAMPAIGN_SPLIT_ID,
BATCH_NAME,
BATCH_ID,
TCIMS_CUST_ID,
TASK_GROUP_ID,
TASK_ID,
CREATED_DATE,
CREATED_BY,
UPDATED_DATE,
UPDATED_BY)
SELECT /*+ PARALLEL(A,4)*/
SEQ_I_SELL_SUCC_ARCH_ARCHID.NEXTVAL,
TCIMS_BATCH_ID,
POLICY_NO,
POLICY_BUSINESS_FLAG,
LAST_POLICY_NO,
DEPARTMENT_CHINESE_NAME,
SALE_AGENT_CODE,
BUSINESS_SOURCE_NAME,
BUSINESS_SOURCE_DETAIL_CODE,
BUSINESS_SOURCE_DETAIL_NAME,
CHANNEL_SOURCE_CODE,
CHANNEL_SOURCE_NAME,
CHANNEL_SOURCE_DETAIL_CODE,
CHANNEL_SOURCE_DETAIL_NAME,
LAST_YEAR_APPLY_COMPANY,
VALUE_CHINESE_NAME,
APPLY_DAY,
INSURANCE_BEGIN_TIME,
INSURANCE_END_TIME,
TOTAL_ACTUAL_PREMIUM,
PREMIUM_INFO,
INPUT_BY,
UNDERWRITE_TIME,
INSURANT_PARTY_NO,
INSURANT_PERSONNEL_NAME,
INSURANT_CERTIFICATE_TYPE,
INSURANT_BIRTHDAY,
INSURANT_PROFESSION_CODE,
APPLICANT_PERSONNEL_NAME,
APPLICANT_ADDRESS,
APPLICANT_POSTCODE,
APPLICANT_PERSONNEL_CODE,
APPLICANT_FIRST_ISSUE_DATE,
PROFESSION_CODE,
MARRIED_STATUS,
USAGE_ATTRIBUTE_CODE,
OWNERSHIP_ATTRIBUTE_CODE,
AUTO_MODEL_CODE,
BRAND_CHN_NAME,
AUTO_MODEL_CHN_NAME,
VEHICLE_TYPE,
FIRST_SALE_DATE,
VEHICLE_LICENCE_CODE,
EXHAUST_CAPABILITY,
LICENCE_TYPE_CODE,
ENGINE_NO,
VEHICLE_FRAME_NO,
PURCHASE_PRICE,
VEHICLE_TONNAGES,
WHOLE_WEIGHT,
VEHICLE_SEATS,
CAMPAIGN_ID,
BIZ_MODEL,
SUB_BIZMODEL,
EXPIRED_DATE,
CAMPAIGN_SPLIT_ID,
BATCH_NAME,
BATCH_ID,
TCIMS_CUST_ID,
TASK_GROUP_ID,
TASK_ID,
SYSDATE,
'SYSTEM',
SYSDATE,
'SYSTEM'
FROM IDL_EXCH_SELL_SUCC_ALL_TMP A
WHERE A.TCIMS_BATCH_ID = TCIMS_BATCH_ID;
--将成功件的任务组ID保存在归档表中，后续用于从失败件中剔除成功件操作。
INSERT INTO IDL_TASK_GROUP_ID_ARCH
(TASK_GROUP_ID, CREATED_DATE, CREATED_BY)
SELECT DISTINCT TASK_GROUP_ID, SYSDATE, 'SYSTEM'
FROM IDL_EXCH_SELL_SUCC_ALL_TMP B
WHERE NOT EXISTS (SELECT 1
FROM IDL_TASK_GROUP_ID_ARCH A
WHERE A.TASK_GROUP_ID = B.TCIMS_BATCH_ID)
AND B.TASK_GROUP_ID IS NOT NULL;
COMMIT;
FOR SUCC_TCIMS_BATCH_ID_UPDATE IN CUR_SUCC_BATCH_ID LOOP
--修改批次状态
UPDATE IDL_EX_BATCH A
SET A.BATCH_STATUS = '11', --修改状态为“待清洗”，到时还需要进行批次计算
A.UPDATED_DATE = SYSDATE,
A.UPDATED_BY = 'SYSTEM'
WHERE A.TCIMS_BATCH_ID = SUCC_TCIMS_BATCH_ID_UPDATE.TCIMS_BATCH_ID;
DELETE FROM IDL_AST_SELL_SUCC_INFO A
WHERE A.TCIMS_BATCH_ID = SUCC_TCIMS_BATCH_ID_UPDATE.TCIMS_BATCH_ID;
COMMIT;
END LOOP;
END IF;
END SP_EXCH_DEAL_SELL_SUCC_INFO;
/***********************************************************
--功能说明: 处理ITS批改件获取接口数据
--参数说明:
--调用函数:
--修改记录： create by zengjingchao001
--*********************************************************/
/*
--ITS系统表名
批单信息接口视图(VW_EP_ENDORSE_INFO)
BDL_RELA_AGTINF_EVTINF：事件与协议关系表
BDL_EVT_ENDORSE：批改表
IDL_AST_REWORK_INFO：批单信息接口表
IDL_AST_REWORK_INFO_ARCH ：归档表
*/
PROCEDURE SP_EXCH_DEAL_REWORK_INFO IS
BEGIN
--将批改号、保单号、协议号、事件号对应关系保存在临时表中
--根据ENDORSE_NO, ENDORSE_ITEM_CODE查询在批改表中不存在的数据，此类数据需新增到基表中
INSERT /*+APPEND*/
INTO IDL_RELA_AGTINF_ENDINF_TMP1
(EVENT_ID,
POLICY_ID,
ENDORSE_NO,
POLICY_NO,
ENDORSE_ITEM_CODE,
POLICY_TYPE_CODE)
SELECT SEQ_BDL_EVT_INFO_ID.NEXTVAL,
C.POLICY_ID,
C.ENDORSE_NO,
C.POLICY_NO,
C.ENDORSE_ITEM_CODE,
C.POLICY_TYPE_CODE
FROM --查询在商业险、交强险中存在的批改件,得到对应的协议号、保单号、
(SELECT DISTINCT T1.POLICY_NO,
T1.ENDORSE_NO,
T1.ENDORSE_ITEM_CODE,
A.POLICY_ID,
'C51' AS POLICY_TYPE_CODE
FROM IDL_AST_REWORK_INFO T1, BDL_AGT_TRAFFIC_INSURE_INFO A
WHERE T1.POLICY_NO = A.C51_POLICY_NO
UNION
SELECT DISTINCT T2.POLICY_NO,
T2.ENDORSE_NO,
T2.ENDORSE_ITEM_CODE,
B.POLICY_ID,
'C01' AS POLICY_TYPE_CODE
FROM IDL_AST_REWORK_INFO T2, BDL_AGT_BUSINESS_INSURE_INFO B
WHERE T2.POLICY_NO = B.C01_POLICY_NO) C
WHERE NOT EXISTS (SELECT 1
FROM BDL_EVT_ENDORSE D
WHERE D.ENDORSE_NO = C.ENDORSE_NO
AND D.ENDORSE_ITEM_CODE = C.ENDORSE_ITEM_CODE);
COMMIT;
--根据临时表数据增加事件协议关系表
INSERT INTO BDL_RELA_AGTINF_EVTINF
(EVENT_ID,
POLICY_ID,
POLICY_TYPE_CODE,
EVENT_TYPE,
CREATED_DATE,
CREATED_BY)
SELECT A.EVENT_ID, --得确定一下对应的SEQ
A.POLICY_ID,
POLICY_TYPE_CODE, --协议分类代码是啥？
'01', --事件类型
SYSDATE,
'SYSTEM'
FROM IDL_RELA_AGTINF_ENDINF_TMP1 A;
COMMIT;
--根据临时表数据新增批改表
INSERT INTO BDL_EVT_ENDORSE
(EVENT_ID,
ENDORSE_NO,
POLICY_NO,
APPLY_DATE,
EFFECTIVE_DATE,
CHECK_ENDORSE_PREMIUM,
ENDORSE_ITEM_CODE,
ENDORSE_ITEM_COMMENT,
CREATED_DATE,
UPDATED_DATE,
CREATED_BY,
UPDATED_BY)
SELECT A.EVENT_ID,
A.ENDORSE_NO,
A.POLICY_NO,
B.APPLY_DATE,
B.EFFECTIVE_DATE,
B.CHECK_ENDORSE_PREMIUM,
A.ENDORSE_ITEM_CODE,
B.ENDORSE_ITEM_COMMENT,
SYSDATE,
SYSDATE,
'SYSTEM',
'SYSTEM'
FROM IDL_RELA_AGTINF_ENDINF_TMP1 A, IDL_AST_REWORK_INFO B
WHERE A.POLICY_NO = B.POLICY_NO
AND A.ENDORSE_NO = B.ENDORSE_NO
AND A.ENDORSE_ITEM_CODE = B.ENDORSE_ITEM_CODE;
COMMIT;
--根据ENDORSE_NO, ENDORSE_ITEM_CODE查询在批改表中存在的数据，此类数据需更新到批改表中
INSERT /*+APPEND*/
INTO IDL_AST_REWORK_INFO_TMP
(POLICY_NO,
ENDORSE_NO,
ENDORSE_ITEM_CODE,
APPLY_DATE,
EFFECTIVE_DATE,
CHECK_ENDORSE_PREMIUM,
ENDORSE_ITEM_COMMENT)
SELECT POLICY_NO,
ENDORSE_NO,
ENDORSE_ITEM_CODE,
APPLY_DATE,
EFFECTIVE_DATE,
CHECK_ENDORSE_PREMIUM,
ENDORSE_ITEM_COMMENT
FROM (
--查询在商业险、交强险中存在的批改件,得到对应的协议号、保单号
SELECT DISTINCT T1.POLICY_NO,
T1.ENDORSE_NO,
T1.ENDORSE_ITEM_CODE,
T1.APPLY_DATE,
T1.EFFECTIVE_DATE,
T1.CHECK_ENDORSE_PREMIUM,
T1.ENDORSE_ITEM_COMMENT,
A.POLICY_ID
FROM IDL_AST_REWORK_INFO T1, BDL_AGT_TRAFFIC_INSURE_INFO A
WHERE T1.POLICY_NO = A.C51_POLICY_NO
UNION
SELECT DISTINCT T2.POLICY_NO,
T2.ENDORSE_NO,
T2.ENDORSE_ITEM_CODE,
T2.APPLY_DATE,
T2.EFFECTIVE_DATE,
T2.CHECK_ENDORSE_PREMIUM,
T2.ENDORSE_ITEM_COMMENT,
B.POLICY_ID
FROM IDL_AST_REWORK_INFO T2, BDL_AGT_BUSINESS_INSURE_INFO B
WHERE T2.POLICY_NO = B.C01_POLICY_NO) T3
WHERE EXISTS
(SELECT 1
FROM BDL_EVT_ENDORSE C
WHERE C.ENDORSE_NO = T3.ENDORSE_NO
AND C.ENDORSE_ITEM_CODE = T3.ENDORSE_ITEM_CODE);
COMMIT;
--更新批改表
UPDATE BDL_EVT_ENDORSE A
SET ( POLICY_NO, APPLY_DATE, EFFECTIVE_DATE, CHECK_ENDORSE_PREMIUM, ENDORSE_ITEM_COMMENT) = (SELECT POLICY_NO,
APPLY_DATE,
EFFECTIVE_DATE,
CHECK_ENDORSE_PREMIUM,
ENDORSE_ITEM_COMMENT
FROM IDL_AST_REWORK_INFO_TMP B
WHERE A.ENDORSE_NO =
B.ENDORSE_NO
AND A.ENDORSE_ITEM_CODE =
B.ENDORSE_ITEM_CODE),
UPDATED_DATE = SYSDATE,
UPDATED_BY = 'SYSTEM'
WHERE EXISTS (SELECT 1
FROM IDL_AST_REWORK_INFO_TMP T1
WHERE A.ENDORSE_NO = T1.ENDORSE_NO
AND A.ENDORSE_ITEM_CODE = T1.ENDORSE_ITEM_CODE);
COMMIT;
--更新保单状态为失败，同时修改客户类型
--暂定义保单状态2为失败
--交强险
UPDATE BDL_AGT_TRAFFIC_INSURE_INFO T
SET POLICY_STATUS = '2'
WHERE EXISTS (SELECT 1
FROM IDL_AST_REWORK_INFO T1
WHERE T1.POLICY_NO = T.C51_POLICY_NO);
COMMIT;
--商业险
UPDATE BDL_AGT_BUSINESS_INSURE_INFO T
SET T.POLICY_STATUS = '2'
WHERE EXISTS (SELECT 1
FROM IDL_AST_REWORK_INFO T1
WHERE T1.POLICY_NO = T.C01_POLICY_NO);
COMMIT;
--修改客户类型
INSERT /*+APPEND*/
INTO IDL_AST_REWORK_CUST_INFO_TMP
(TCIMS_CUST_ID, SERIES_TYPE)
--查询所有更新过批改信息的客户ID/系列。
SELECT DISTINCT T2.TCIMS_CUST_ID, T2.SERIES_TYPE
FROM (
--查询所有此次更新的客户协议号
SELECT A.POLICY_ID, A.C51_POLICY_NO AS POLICY_NO
FROM IDL_AST_REWORK_INFO B, BDL_AGT_TRAFFIC_INSURE_INFO A
WHERE B.POLICY_NO = A.C51_POLICY_NO
153
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
