- 浏览: 31266 次
- 性别:
- 来自: 上海
-
最近访客 更多访客>>
最新评论
-
punan7005：
这不就是我们公司吗
转（坚决抛弃powerdesigner建模） -
jkluooop：
我尝试过啊，很郁闷，在第一次访问的时候IE跟FFX下老弹出登录 ...
JCIFS相关 -
every：
你好
请教2008 ad 如何配置ssl
AD、CA、SSL，绑定keystore
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
<
发表评论
-
resin3.1配置说明
2010-12-14 16:07 851<!-- Resin 3.1 配置文件. --> ... -
resion 2.x配置
2010-12-14 16:06 1166resion 2.x配置 1. <http h ... -
sqlserver 递归查询
2010-12-01 17:20 794WITH read_tree (id,department ... -
20个非常有用的Java程序片段
2010-08-31 22:58 1382下面是20个非常有用的Java程序片段，希望能对你有用。1. ... -
ajax post提交乱码
2010-08-10 17:28 15946月30日 jQuery ajax乱码问题解决 一、测试环 ... -
IE注册表设置安全项
2010-07-30 17:16 2256Windows安全设置之注册表项在IE4.0以上的版本中，IE ... -
AD、CA、SSL，绑定keystore
2010-04-12 22:37 2636本文原文出处：http://blog.csdn.net/fre ... -
理解REST软件架构（转）
2010-01-01 23:47 860一种思维方式影响了软件行业的发展。REST软件架构是当今世界上 ... -
数据整合_SP
2009-12-18 14:50 757CREATE OR REPLACE PACKAGE BODY ... -
屏蔽数据_SP
2009-12-18 14:46 744CREATE OR REPLACE PACKAGE BODY ... -
合并清洗
2009-12-18 14:41 740CREATE OR REPLACE PACKAGE BODY ... -
数据清洗_sp
2009-12-18 14:39 760CREATE OR REPLACE PACKAGE BODY ... -
test_help_sp
2009-12-18 08:24 715CREATE OR REPLACE PACKAGE BODY ... -
数据清洗2
2009-12-18 08:23 811create or replace package body ... -
数据清洗3
2009-12-18 08:22 819CREATE OR REPLACE PACKAGE BODY ... -
dwr reverse-ajax 实例解析(初步认识)
2009-10-30 15:20 1036在网上找了一个老外写的dwr reverse-ajax的例子 ... -
java反射
2009-10-17 21:46 971Reflection 是Java被视为动态（或准动态）语言 ... -
oracle sql
2009-09-23 10:29 630select * from PDS_DELIVERY_REQU ... -
开闭原则 思考(转)
2009-09-17 12:56 855开闭原则的定义 Software entities shou ... -
oracle函数
2009-09-16 23:38 1026oracle时间函数 相信很多人都有过统计某些数据的经历，比 ...
相关推荐
接下来，清除默认的清理作业，使用`sys.sp_cdc_drop_job 'cleanup'`，并用OGG的清理脚本创建新的清理作业，如`ogg_cdc_cleanup_setup.bat createjob ogg ogg dbname (local) ogg2`。 进入OGG的ggsci控制台，创建...
exec sp_addlinkedserver @server='srv_lnk', @srvproduct='', @provider='SQLOLEDB', @provstr='k-magic.vicp.net' -- 设置登录凭证 exec sp_addlinkedsrvlogin @rmtsrvname = 'srv_lnk', @useself = 'false...
* 海典 BI 系统采用数据仓库和数据抽取机制，运用每天晚上服务器空闲时间将业务系统数据抽取到数据仓库之中。 * 海典 BI 系统可以集成 Excel 进行互动式报表分析和报表定制。 * 海典 BI 系统是一种开放报表系统，...
K-PageSearch是由Kwindsoft自主研发的专业网页搜索引擎系统，拥有先进的智能分析和海量数据检索技术，核心由多线程采集系统、智能分析系统、海量索引系统、全文检索系统四大部分构成。系统采用专业级的搜索引擎系统...
1. 进入Diagram程序：用户可以通过运行AVEVA Diagrams，选择所有程序> AVEVA > Plant 12.0 SP5 > Run PDMS，进入Diagram程序。 2. 新建图纸基本步骤：用户可以通过Diagram程序新建图纸，包括选择项目名、用户名、...
首先，"读取源文件到etl staging 表"这一阶段涉及到数据抽取（Extract）。数据源可以是XML文件或平面文件（flat file），这两种格式在数据处理中非常常见。XML文件是一种结构化数据格式，用于存储和交换数据，而平面...
接下来，`sp_1.zip`和`sp_2.zip`可能包含了存储过程。在数据库管理系统中，存储过程是一组预编译的SQL语句，可封装成一个命名实体，便于重复使用和执行复杂的业务逻辑。它们可能是特定业务场景下的数据库操作集合，...
K-PageSearch是由Kwindsoft自主研发的专业网页搜索引擎系统，...智能网页正文抽取 基于词库的智能中文分词 中文分词词库管理 海量数据毫秒级全文检索 缓存技术 网页快照 高级搜索 竞价排名 网络蜘蛛
K-PageSearch是由Kwindsoft自主研发的专业网页...智能网页正文抽取 基于词库的智能中文分词 中文分词词库管理 海量数据毫秒级全文检索 缓存技术 网页快照 高级搜索 竞价排名 网络蜘蛛 页面截图展示
BW 730的SP8，可以使用SLT实时的抽取数据进入到BW中，以减少隔夜产生的大数据量进入到BW系统中，也减少业务系统抽取的压力。在SLT中，都是采用增量抽取的方式，无论是连接SAP业务系统或者Non-SAP的业务系统。真正有...
从给定的文件信息来看，我们正在探讨的是一个在Oracle数据库环境下编写的存储过程，名为`sp_kr_ns_hn_mtv_dtal`。这个存储过程主要服务于河南移动的手机电视专区日累计报表的需求，其设计与实现包含了多个层面的技术...
以上知识点从文档中抽取了关于西门子ProTool V6.0 SP2 Runtime在OPC连接方面的关键信息，并提供了在实际应用中可能出现的问题及其解决策略。通过了解这些知识，自动化系统的设计者和维护者可以更加有效地设置和优化...
4. 新图数据处理框架的设计：文档中提到了新图数据处理框架的三个主要部分，包括大规模图的分割算法、数据抽取的优化以及计算层与持久层结合机制。通过这三个方面的设计，可以有效提高图数据处理的效率和性能。 5. ...
- 大规模数据抽取速度慢，相较于原生工具性能较低。 2. **基于原生工具的ETL**： - **优点**： - 抽取和加载速度快，适用于首次大数据量迁移； - 开发灵活性高，可以通过编程控制工具实现数据的高速抽取和加载，...
ADC12DJ5200-SP采用JESD204C串行数据接口，最大通道速率可达17.16Gbps，支持64b/66b和8b/10b编码。JESD204C接口的子类1特性确保了确定性的延迟和多器件同步。8b/10b模式与JESD204B兼容，64b/66b编码提供了前向纠错...
在DataStage中，调用存储过程通常在设计数据抽取和加载流程时是必要的。以下是调用存储过程的基本步骤： - 首先，打开DataStage Designer，这是设计和构建DataStage作业的主要环境。 - 接着，拖拽DB2 Connector到...