数据整合_SP

最新推荐文章于 2022-02-25 20:00:37 发布

haofeng0305

最新推荐文章于 2022-02-25 20:00:37 发布

阅读量120

点赞数

分类专栏：资料文章标签： F# C C++ C# SQL

本文链接：https://blog.csdn.net/haofeng0305/article/details/83519069

版权

资料专栏收录该内容

21 篇文章 0 订阅

订阅专栏

CREATE OR REPLACE PACKAGE BODY NETS_TCIMS_BACK_CLEAN_DATA
IS

SPLIT_CHR VARCHAR2(1) := ',';

/***********************************************************
--功能说明:   整合需手工清洗数据前准备1
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项：顺序在所有清洗功能完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_PRE_BACK_DATA_1
IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500); -- 错误记录

BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,41,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','IDL_SEP_BACK_DATA_1_STG_TMP');

    --合并数据给sqlserver手工清洗
    INSERT /*+APPEND*/
      INTO IDL_SEP_BACK_DATA_1_STG_TMP
           (SYS_ID,CLEAN_FLAG)
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(F 2)*/
           A.SYS_ID,
           NVL2(B.SYS_ID,'A'||SPLIT_CHR,'')||
           NVL2(C.SYS_ID,'B'||SPLIT_CHR,'')||
           NVL2(D.SYS_ID,'C'||SPLIT_CHR,'') CLEAN_FLAG
      FROM IDL_SEP_SRC_DATA                A,
           PC_CLEAN_CITY_STG_TMP           B,
           (SELECT SYS_ID, VEHICLE_NO, CITY,
                   VEHICLE_NO_INTEGRITY ,CLEAN_STATUS
             FROM PC_CLEAN_VEHICLE_NO_STG_TMP
           WHERE CLEAN_STATUS = '0')       C,
           (SELECT DISTINCT SYS_ID           --, TEL_NO, CITY,CLEAN_STATUS
              FROM PC_CLEAN_TEL_NO_STG_TMP   --PC_CLEAN_TELEPHONE_STG_TMP
           WHERE CLEAN_STATUS = '0')       D,    -- 返回手工清洗时使用
           --PC_CLEAN_TEL_NO_STG_TMP         D
           IDL_EX_BATCH                     F
     WHERE A.SYS_ID = B.SYS_ID(+)
       AND A.SYS_ID = C.SYS_ID(+)
       AND A.SYS_ID = D.SYS_ID(+)
       AND A.TCIMS_BATCH_ID = F.TCIMS_BATCH_ID
       AND F.SERIES_TYPE = '01'
       AND F.BATCH_STATUS = '11'
       AND F.PREPARE_FLAG = '1';
    COMMIT;

    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,41,NULL,'02',NULL,NULL,NULL,NULL);

EXCEPTION
     WHEN OTHERS THEN
        P_ERRMSG := SUBSTR(SQLERRM,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,41,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
        RAISE;
END SP_UNITE_PC_PRE_BACK_DATA_1;

/***********************************************************
--功能说明:   整合需手工清洗数据前准备2
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项：顺序在所有清洗功能完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_PRE_BACK_DATA_2
IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500); -- 错误记录

BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,42,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','IDL_SEP_BACK_DATA_2_STG_TMP');

    --合并数据给sqlserver手工清洗
    INSERT /*+APPEND*/
      INTO IDL_SEP_BACK_DATA_2_STG_TMP
           (SYS_ID,CLEAN_FLAG)
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(F 2)*/
           A.SYS_ID,
           NVL2(B.SYS_ID,'D'||SPLIT_CHR,'')||
           NVL2(C.SYS_ID,'E'||SPLIT_CHR,'')||
           NVL2(D.SYS_ID,'F'||SPLIT_CHR,'') CLEAN_FLAG
      FROM IDL_SEP_SRC_DATA                A,
          ( SELECT SYS_ID ,BRAND_TYPE_CODE, CLEAN_STATUS
             FROM PC_CLEAN_BRAND_TYPE_CD_STG_TMP
           WHERE CLEAN_STATUS = '0') B,
          ( SELECT SYS_ID, USAGE_CODE, USAGE_ATTRIBUTE, CLEAN_STATUS
             FROM PC_CLEAN_USAGE_CODE_STG_TMP
           WHERE CLEAN_STATUS = '0')   C,
          ( SELECT SYS_ID, VEHICLE_TYPE_CODE, VEHICLE_TYPE, CLEAN_STATUS
             FROM PC_CLEAN_VEHIC_TYPE_CD_STG_TMP
            WHERE CLEAN_STATUS = '0') D,
            IDL_EX_BATCH                    F
     WHERE A.SYS_ID = B.SYS_ID(+)
       AND A.SYS_ID = C.SYS_ID(+)
       AND A.SYS_ID = D.SYS_ID(+)
       AND A.TCIMS_BATCH_ID = F.TCIMS_BATCH_ID
       AND F.SERIES_TYPE = '01'
       AND F.BATCH_STATUS = '11'
       AND F.PREPARE_FLAG = '1';
    COMMIT;

    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,42,NULL,'02',NULL,NULL,NULL,NULL);

EXCEPTION
     WHEN OTHERS THEN
        P_ERRMSG := SUBSTR(SQLERRM,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,42,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
        RAISE;
END SP_UNITE_PC_PRE_BACK_DATA_2;

/***********************************************************
--功能说明:   整合需手工清洗数据前准备3
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项：顺序在所有清洗功能完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_PRE_BACK_DATA_3
IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500); -- 错误记录

BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,43,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','IDL_SEP_BACK_DATA_3_STG_TMP');

    --合并数据给sqlserver手工清洗
    INSERT /*+APPEND*/
      INTO IDL_SEP_BACK_DATA_3_STG_TMP
           (SYS_ID,CLEAN_FLAG)
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(F 2)*/
           A.SYS_ID,
           NVL2(B.SYS_ID,'G'||SPLIT_CHR,'')||
           NVL2(C.SYS_ID,'H'||SPLIT_CHR,'')||
           NVL2(D.SYS_ID,'I'||SPLIT_CHR,'') CLEAN_FLAG
      FROM IDL_SEP_SRC_DATA                A,
           (SELECT SYS_ID ,FIRST_REGISTER_DATE ,CLEAN_STATUS, POLICY_END_DATE
               FROM PC_CLEAN_FST_REG_DATE_STG_TMP
           WHERE CLEAN_STATUS = '0') B,
           (SELECT SYS_ID ,POLICY_END_DATE ,CLEAN_STATUS
               FROM PC_CLEAN_POLI_END_DATE_STG_TMP
           WHERE CLEAN_STATUS = '0') C,
           (SELECT SYS_ID ,POLICY_EFFECTIVE_DATE ,CLEAN_STATUS
               FROM PC_CLEAN_POLI_EFC_DATE_STG_TMP
             WHERE CLEAN_STATUS = '0') D,
             IDL_EX_BATCH                    F
     WHERE A.SYS_ID = B.SYS_ID(+)
       AND A.SYS_ID = C.SYS_ID(+)
       AND A.SYS_ID = D.SYS_ID(+)
       AND A.TCIMS_BATCH_ID = F.TCIMS_BATCH_ID
       AND F.SERIES_TYPE = '01'
       AND F.BATCH_STATUS = '11'
       AND F.PREPARE_FLAG = '1';
    COMMIT;

    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,43,NULL,'02',NULL,NULL,NULL,NULL);

EXCEPTION
     WHEN OTHERS THEN
        P_ERRMSG := SUBSTR(SQLERRM,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,43,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
        RAISE;
END SP_UNITE_PC_PRE_BACK_DATA_3;

/***********************************************************
--功能说明:   整合需手工清洗数据前准备4
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项：顺序在所有清洗功能完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_PRE_BACK_DATA_4
IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500); -- 错误记录

BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,44,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','IDL_SEP_BACK_DATA_4_STG_TMP');

    --合并数据给sqlserver手工清洗
    INSERT /*+APPEND*/
      INTO IDL_SEP_BACK_DATA_4_STG_TMP
           (SYS_ID,CLEAN_FLAG)
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(F 2)*/
           A.SYS_ID,
           NVL2(B.SYS_ID,'J'||SPLIT_CHR,'')||
           NVL2(C.SYS_ID,'K'||SPLIT_CHR,'')||
           NVL2(D.SYS_ID,'L'||SPLIT_CHR,'') CLEAN_FLAG
      FROM IDL_SEP_SRC_DATA                           A,
           (SELECT SYS_ID, CUST_DOB, CLEAN_STATUS
              FROM PC_CLEAN_CUST_DOB_STG_TMP K
             WHERE NOT EXISTS(
                SELECT 1 FROM PC_CLEAN_ID_NUMBER_STG_TMP E
                 WHERE K.SYS_ID = E.SYS_ID
                   AND E.CLEAN_STATUS = '1')
               AND CLEAN_STATUS = '0')                B,
           (SELECT SYS_ID, MAIN_DRIVER_DOB, CLEAN_STATUS
             FROM PC_CLEAN_MAIN_DRIV_DOB_STG_TMP
           WHERE CLEAN_STATUS = '0')                  C,
           (SELECT SYS_ID, DRIVER_LICENSE_FST_ISSUE_DATE, CLEAN_STATUS
             FROM PC_CLEAN_LIC_ISUE_DATE_STG_TMP
           WHERE CLEAN_STATUS = '0')                  D,
           IDL_EX_BATCH                               F
     WHERE A.SYS_ID = B.SYS_ID(+)
       AND A.SYS_ID = C.SYS_ID(+)
       AND A.SYS_ID = D.SYS_ID(+)
       AND A.TCIMS_BATCH_ID = F.TCIMS_BATCH_ID
       AND F.SERIES_TYPE = '01'
       AND F.BATCH_STATUS = '11'
       AND F.PREPARE_FLAG = '1';
    COMMIT;

    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,44,NULL,'02',NULL,NULL,NULL,NULL);

EXCEPTION
     WHEN OTHERS THEN
        P_ERRMSG := SUBSTR(SQLERRM,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,44,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
        RAISE;
END SP_UNITE_PC_PRE_BACK_DATA_4;

/***********************************************************
--功能说明:   整合需手工清洗数据(产险)
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项：顺序在<整合需手工清洗数据前准备1,2,3,4>完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_BACK_DATA
IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500); -- 错误记录

BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,45,NULL,'01',NULL,NULL,NULL,NULL);

--清空结果临时表无须清空数据
--NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','');

    --合并数据给sqlserver手工清洗
    INSERT /*+APPEND*/
      INTO IDL_SEP_BACK_DATA_TMP
           (SYS_ID,CLEAN_FLAG)
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(E 2) PARALLEL(F 2)*/
           A.SYS_ID,
           B.CLEAN_FLAG||C.CLEAN_FLAG||D.CLEAN_FLAG||E.CLEAN_FLAG CLEAN_FLAG
      FROM IDL_SEP_SRC_DATA                A,
           IDL_SEP_BACK_DATA_1_STG_TMP     B,
           IDL_SEP_BACK_DATA_2_STG_TMP     C,
           IDL_SEP_BACK_DATA_3_STG_TMP     D,
           IDL_SEP_BACK_DATA_4_STG_TMP     E,
           IDL_EX_BATCH                    F
     WHERE A.SYS_ID = B.SYS_ID(+)
       AND A.SYS_ID = C.SYS_ID(+)
       AND A.SYS_ID = D.SYS_ID(+)
       AND A.SYS_ID = E.SYS_ID(+)
       AND A.TCIMS_BATCH_ID = F.TCIMS_BATCH_ID
       AND F.SERIES_TYPE = '01'
       AND F.BATCH_STATUS = '11'
       AND F.PREPARE_FLAG = '1';
   COMMIT;

    --合并数据给sqlserver手工清洗, 提取需要的数据
    INSERT /*+APPEND*/
      INTO IDL_SQL_SEP_CUST_RETURN --IDL_SEP_BACK_DATA
            (
            SQL_SYS_ID,
            SYS_ID,
            TCIMS_BATCH_ID,
            TCIMS_CUST_ID,
            TCIMS_VT_ID,
            CITY,
            THIRD_ORG,
            SECONDARY_ORG,
            PROVINCE,
            AREA_INFO,
            SRC_TYPE,
            LIST_TYPE,
            CUST_CLASS,
            CUST_TYPE,
            APPLICANT_PARTY_NO,
            CIF2_ID,
            CIF2_ID_TYPE,
            CUST_NAME,
            SEX,
            SALUTATION,
            MARITAL_STATUS,
            CUST_DOB,
            POSITION,
            ID_TYPE,
            ID_NUMBER,
            CONTACT_NAME,
            TELEPHONE_NUMBER,
            FAX_AREA_CODE,
            FAX_NUM,
            APPLICANT_EMAIL,
            ZIP_CODE,
            ADDRESS,
            REMARK,
            DRIVER_LICENSE_NO,
            DRIVER_LICENSE_FST_ISSUE_DATE,
            DRIVE_VEHICLE_TYPE_CODE,
            VEHICLE_NO,
            BRAND_TYPE,
            BRAND_TYPE_CODE,
            USAGE_ATTRIBUTE,
            USAGE_CODE,
            ATTRIBUTE_CODE,
            VEHICLE_TYPE,
            VEHICLE_TYPE_CODE,
            VEHICLE_CLASS_CODE,
            VEHICLE_BODY_COLOR,
            AUTOMODEL_NAME,
            VEHICLE_MODEL_NAME_CN,
            VEHICLE_MODEL_NO,
            FACTORY_LOGO,
            VEHICLE_SERIES,
            PURCHASE_PRICE,
            BRAND_TYPE_FIRST_SALE_DATE,
            SEAT_NUMBER,
            TON_NUMBER,
            FULL_CAPACITY,
            EXHAUST,
            FOREIGN_VEHICLE_NO,
            ENGINE_NUMBER,
            VEHICLE_FRAME,
            DEV_CODE,
            REFIX_DESC,
            VEHICLE_VALUE,
            VEHICLE_REMARK,
            VEHICLE_GENERAL_TYPE_CODE,
            VEHICLE_SPECIFIC_TYPE_CODE,
            MANUFACTURE_COUNTRY,
            VT_FACTORY,
            VEHICLE_STATUS,
            USED_YEARS,
            FIRST_REGISTER_DATE,
            LEAVE_FACTORY_DATE,
            BUY_VEHICLE_DATE,
            VEHICLE_RANK_CODE,
            VEHICLE_OWNER,
            LICENSE_ISSUE_DATE,
            MAIN_DRIVER_NO,
            MAIN_DRIVER_DOB,
            MAIN_DRIVER_SEX,
            MAIN_DRIVER_NAME,
            DRIVE_AREA_CODE,
            INSURED_PERSON_NAME,
            INSURED_PERSON_ADDRESS,
            C01_POLICY_NO,
            C01_DEPARTMENT_CODE,
            C01_DEPARTMENT_CHINESE_NAME,
            C01_LAST_POLICY_NO,
            C01_LAST_YEAR_APPLY_COMPANY,
            C01_VALUE_CHINESE_NAME,
            C01_SALE_AGENT_CODE,
            C01_EMPLOYEE_NAME,
            C01_BUSINESS_SRC_CODE,
            C01_BUSINESS_SRC_NAME,
            C01_BUSINESS_SRC_DETAIL_CODE,
            C01_BUSINESS_SRC_DETAIL_NAME,
            C01_CHANNEL_SRC_CODE,
            C01_CHANNEL_SRC_NAME,
            C01_CHANNEL_SRC_DETAIL_CODE,
            C01_CHANNEL_SRC_DETAIL_NAME,
            C01_INSURANCE_BEGIN_TIME,
            C01_INSURANCE_END_TIME,
            C01_TOTAL_ACTUAL_PREMIUM,
            C01_PREMIUM_INFO,
            C01_APPLY_TIME,
            C01_UNDERWRITE_TIME,
            C01_INPUT_BY_ID,
            C01_INPUT_BY,
            C51_POLICY_NO,
            C51_DEPARTMENT_CODE,
            C51_DEPARTMENT_CHINESE_NAME,
            C51_LAST_POLICY_NO,
            C51_LAST_YEAR_APPLY_COMPANY,
            C51_VALUE_CHINESE_NAME,
            C51_SALE_AGENT_CODE,
            C51_EMPLOYEE_NAME,
            C51_BUSINESS_SRC_CODE,
            C51_BUSINESS_SRC_NAME,
            C51_BUSINESS_SRC_DETAIL_CODE,
            C51_BUSINESS_SRC_DETAIL_NAME,
            C51_CHANNEL_SRC_CODE,
            C51_CHANNEL_SRC_NAME,
            C51_CHANNEL_SRC_DETAIL_CODE,
            C51_CHANNEL_SRC_DETAIL_NAME,
            C51_INSURANCE_BEGIN_TIME,
            C51_INSURANCE_END_TIME,
            C51_TOTAL_ACTUAL_PREMIUM,
            C51_PREMIUM_INFO,
            C51_APPLY_TIME,
            C51_UNDERWRITE_TIME,
            C51_INPUT_BY_ID,
            C51_INPUT_BY,
            VIOLATION_RATIO,
            CLAIM_RATIO,
            VIOLATION_PREMIUM_CHANGE,
            INSURANCE_TYPE_FLAG,
            POLICY_EFFECTIVE_DATE,
            POLICY_END_DATE,
            PA_APPLY_HISTORY,
            PA_LIFE_CLIENT,
            BIZ_MODEL,
            SUB_BIZMODEL,
            EXPIRED_DATE,
            CAMPAIGN_ID,
            CAMPAIGN_NAME,
            GROUP_ID,
            CAMPAIGN_SPLIT_NAME,
            BATCH_NAME,
            TASK_GROUP_ID,
            TASK_ID,
            IS_CONTACTED,
            C51_PHONE_RESULT,
            C51_SALE_STAGE,
            C51_SALE_DECISION,
            C51_ADDED_EXPLAIN,
            C01_PHONE_RESULT,
            C01_SALE_STAGE,
            C01_SALE_DECISION,
            C01_ADDED_EXPLAIN,
            C01_SALE_RESULT_CLASS,
            C51_SALE_RESULT_CLASS,
            SALE_RESULT_CLASS,
            IS_AGENCY_PHONE,
            IS_ADDRESS_VALID,
            SHIELD_FLAG,
            RISK_TIMES,
            LIST_PRIORITY,
            CREATED_DATE,
            CREATED_BY,
            UPDATED_DATE,
            UPDATED_BY,
            INVALID_CLEAN_FLAG
            )
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(F 2)*/
            A.SQL_SYS_ID,
            A.SYS_ID,
            A.TCIMS_BATCH_ID,
            A.TCIMS_CUST_ID,
            A.TCIMS_VT_ID,
            A.CITY,
            A.THIRD_ORG,
            A.SECONDARY_ORG,
            A.PROVINCE,
            A.AREA_INFO,
            A.SRC_TYPE,
            A.LIST_TYPE,
            A.CUST_CLASS,
            A.CUST_TYPE,
            A.APPLICANT_PARTY_NO,
            A.CIF2_ID,
            A.CIF2_ID_TYPE,
            A.CUST_NAME,
            A.SEX,
            A.SALUTATION,
            A.MARITAL_STATUS,
            A.CUST_DOB,
            A.POSITION,
            A.ID_TYPE,
            A.ID_NUMBER,
            A.CONTACT_NAME,
            A.TELEPHONE_NUMBER,
            A.FAX_AREA_CODE,
            A.FAX_NUM,
            A.APPLICANT_EMAIL,
            A.ZIP_CODE,
            A.ADDRESS,
            A.REMARK,
            A.DRIVER_LICENSE_NO,
            A.DRIVER_LICENSE_FST_ISSUE_DATE,
            A.DRIVE_VEHICLE_TYPE_CODE,
            A.VEHICLE_NO,
            A.BRAND_TYPE,
            A.BRAND_TYPE_CODE,
            A.USAGE_ATTRIBUTE,
            A.USAGE_CODE,
            A.ATTRIBUTE_CODE,
            A.VEHICLE_TYPE,
            A.VEHICLE_TYPE_CODE,
            A.VEHICLE_CLASS_CODE,
            A.VEHICLE_BODY_COLOR,
            A.AUTOMODEL_NAME,
            A.VEHICLE_MODEL_NAME_CN,
            A.VEHICLE_MODEL_NO,
            A.FACTORY_LOGO,
            A.VEHICLE_SERIES,
            A.PURCHASE_PRICE,
            A.BRAND_TYPE_FIRST_SALE_DATE,
            A.SEAT_NUMBER,
            A.TON_NUMBER,
            A.FULL_CAPACITY,
            A.EXHAUST,
            A.FOREIGN_VEHICLE_NO,
            A.ENGINE_NUMBER,
            A.VEHICLE_FRAME,
            A.DEV_CODE,
            A.REFIX_DESC,
            A.VEHICLE_VALUE,
            A.VEHICLE_REMARK,
            A.VEHICLE_GENERAL_TYPE_CODE,
            A.VEHICLE_SPECIFIC_TYPE_CODE,
            A.MANUFACTURE_COUNTRY,
            A.VT_FACTORY,
            A.VEHICLE_STATUS,
            A.USED_YEARS,
            A.FIRST_REGISTER_DATE,
            A.LEAVE_FACTORY_DATE,
            A.BUY_VEHICLE_DATE,
            A.VEHICLE_RANK_CODE,
            A.VEHICLE_OWNER,
            A.LICENSE_ISSUE_DATE,
            A.MAIN_DRIVER_NO,
            A.MAIN_DRIVER_DOB,
            A.MAIN_DRIVER_SEX,
            A.MAIN_DRIVER_NAME,
            A.DRIVE_AREA_CODE,
            A.INSURED_PERSON_NAME,
            A.INSURED_PERSON_ADDRESS,
            A.C01_POLICY_NO,
            A.C01_DEPARTMENT_CODE,
            A.C01_DEPARTMENT_CHINESE_NAME,
            A.C01_LAST_POLICY_NO,
            A.C01_LAST_YEAR_APPLY_COMPANY,
            A.C01_VALUE_CHINESE_NAME,
            A.C01_SALE_AGENT_CODE,
            A.C01_EMPLOYEE_NAME,
            A.C01_BUSINESS_SRC_CODE,
            A.C01_BUSINESS_SRC_NAME,
            A.C01_BUSINESS_SRC_DETAIL_CODE,
            A.C01_BUSINESS_SRC_DETAIL_NAME,
            A.C01_CHANNEL_SRC_CODE,
            A.C01_CHANNEL_SRC_NAME,
            A.C01_CHANNEL_SRC_DETAIL_CODE,
            A.C01_CHANNEL_SRC_DETAIL_NAME,
            A.C01_INSURANCE_BEGIN_TIME,
            A.C01_INSURANCE_END_TIME,
            A.C01_TOTAL_ACTUAL_PREMIUM,
            A.C01_PREMIUM_INFO,
            A.C01_APPLY_TIME,
            A.C01_UNDERWRITE_TIME,
            A.C01_INPUT_BY_ID,
            A.C01_INPUT_BY,
            A.C51_POLICY_NO,
            A.C51_DEPARTMENT_CODE,
            A.C51_DEPARTMENT_CHINESE_NAME,
            A.C51_LAST_POLICY_NO,
            A.C51_LAST_YEAR_APPLY_COMPANY,
            A.C51_VALUE_CHINESE_NAME,
            A.C51_SALE_AGENT_CODE,
            A.C51_EMPLOYEE_NAME,
            A.C51_BUSINESS_SRC_CODE,
            A.C51_BUSINESS_SRC_NAME,
            A.C51_BUSINESS_SRC_DETAIL_CODE,
            A.C51_BUSINESS_SRC_DETAIL_NAME,
            A.C51_CHANNEL_SRC_CODE,
            A.C51_CHANNEL_SRC_NAME,
            A.C51_CHANNEL_SRC_DETAIL_CODE,
            A.C51_CHANNEL_SRC_DETAIL_NAME,
            A.C51_INSURANCE_BEGIN_TIME,
            A.C51_INSURANCE_END_TIME,
            A.C51_TOTAL_ACTUAL_PREMIUM,
            A.C51_PREMIUM_INFO,
            A.C51_APPLY_TIME,
            A.C51_UNDERWRITE_TIME,
            A.C51_INPUT_BY_ID,
            A.C51_INPUT_BY,
            A.VIOLATION_RATIO,
            A.CLAIM_RATIO,
            A.VIOLATION_PREMIUM_CHANGE,
            A.INSURANCE_TYPE_FLAG,
            A.POLICY_EFFECTIVE_DATE,
            A.POLICY_END_DATE,
            A.PA_APPLY_HISTORY,
            A.PA_LIFE_CLIENT,
            A.BIZ_MODEL,
            A.SUB_BIZMODEL,
            A.EXPIRED_DATE,
            A.CAMPAIGN_ID,
            A.CAMPAIGN_NAME,
            A.GROUP_ID,
            A.CAMPAIGN_SPLIT_NAME,
            A.BATCH_NAME,
            A.TASK_GROUP_ID,
            A.TASK_ID,
            A.IS_CONTACTED,
            A.C51_PHONE_RESULT,
            A.C51_SALE_STAGE,
            A.C51_SALE_DECISION,
            A.C51_ADDED_EXPLAIN,
            A.C01_PHONE_RESULT,
            A.C01_SALE_STAGE,
            A.C01_SALE_DECISION,
            A.C01_ADDED_EXPLAIN,
            A.C01_SALE_RESULT_CLASS,
            A.C51_SALE_RESULT_CLASS,
            A.SALE_RESULT_CLASS,
            A.IS_AGENCY_PHONE,
            A.IS_ADDRESS_VALID,
            A.SHIELD_FLAG,
            A.RISK_TIMES,
            A.LIST_PRIORITY,
            A.CREATED_DATE,
            A.CREATED_BY,
            A.UPDATED_DATE,
            A.UPDATED_BY,
            B.CLEAN_FLAG
      FROM IDL_SEP_SRC_DATA       A,
           IDL_SEP_BACK_DATA_TMP B,
           IDL_EX_BATCH           F
     WHERE A.SYS_ID = B.SYS_ID
       AND B.CLEAN_FLAG IS NOT NULL
       AND A.TCIMS_BATCH_ID = F.TCIMS_BATCH_ID
       AND F.SERIES_TYPE = '01'
       AND F.BATCH_STATUS = '11'
       AND F.PREPARE_FLAG = '1';
    COMMIT;

-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,45,NULL,'02',NULL,NULL,NULL,NULL);

EXCEPTION
     WHEN OTHERS THEN
        P_ERRMSG := SUBSTR(SQLERRM,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,45,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
        RAISE;
END SP_UNITE_PC_BACK_DATA;

/***********************************************************
--功能说明:   整合需手工清洗数据(寿险)
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项：顺序在寿险数据清洗完成之后
--*********************************************************/
PROCEDURE SP_UNITE_LA_BACK_DATA
IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500); -- 错误记录

BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,53,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表无须清空数据
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','IDL_SEL_BACK_DATA_1_TMP');

    --合并数据给sqlserver手工清洗
    INSERT /*+APPEND*/
      INTO IDL_SEL_BACK_DATA_1_TMP
           (SYS_ID,CLEAN_FLAG)
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2)*/
           A.SYS_ID,
           NVL2(B.SYS_ID,'C'||SPLIT_CHR,'')||
           NVL2(C.SYS_ID,'A'||SPLIT_CHR,'') CLEAN_FLAG
      FROM IDL_SEL_SRC_DATA                A,
           (SELECT DISTINCT SYS_ID
             FROM LA_CLEAN_TEL_NO_STG_TMP
             WHERE CLEAN_STATUS = '0')     B,
           LA_CLEAN_CITY_STG_TMP           C,
           IDL_EX_BATCH                    D
     WHERE A.SYS_ID = B.SYS_ID(+)
       AND A.SYS_ID = C.SYS_ID(+)
       AND A.TCIMS_BATCH_ID = D.TCIMS_BATCH_ID
       AND D.SERIES_TYPE = '02'
       AND D.BATCH_STATUS = '11'
       AND D.PREPARE_FLAG = '1';
    COMMIT;

    --合并数据给sqlserver手工清洗
    INSERT /*+APPEND*/
      INTO IDL_SQL_SEL_CUST_RETURN   --IDL_SEL_BACK_DATA
           (
            SQL_SYS_ID,
            SYS_ID,
            TCIMS_BATCH_ID,
            TCIMS_CUST_ID,
            CLIENT_NO,
            APPLICANT_PARTY_NO,
            SRC_TYPE,
            LIST_TYPE,
            LIST_PRIORITY,
            CUST_NAME,
            SEX,
            ID_TYPE,
            ID_NUMBER,
            CUST_DOB,
            WORK_UNIT,
            DEPARTMENT_CHINESE_NAME,
            LIST_CREATED_DATE,
            POSITION,
            POSITION_CODE,
            EDUCATIONAL_BACKGROUND,
            MARITAL_STATUS,
            PROF_GRADE,
            ANNUAL_INCOME,
            CONTACT_ADDRESS,
            CONTACT_ZIPCODE,
            ADDRESS,
            ZIP_CODE,
            EMAIL,
            SHIELD_FLAG,
            CAMPAIGN_NAME,
            CAMPAIGN_SPLIT_NAME,
            BATCH_NAME,
            SUPERVISOR_ID,
            TEAM_LEADER_ID,
            TMR_UM_ID,
            PRODUCT_NAME,
            CONTACT_DATE,
            CALL_RESULT,
            IS_INSURE_FLAG,
            IS_SUBMIT_FLAG,
            IS_FREE_FLAG,
            CALL_BACK_REMARK,
            SUCCESS_DATE,
            TRANSFER_SUCCESS_DAY,
            LIST_STATUS,
            REGION_CODE,
            CREDIT_CARD_CALL_DATE,
            SUBMIT_DATE,
            CREATED_DATE,
            CREATED_BY,
            UPDATED_DATE,
            UPDATED_BY,
            INVALID_CLEAN_FLAG
           )
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(F 2)*/
            A.SQL_SYS_ID,
            A.SYS_ID,
            A.TCIMS_BATCH_ID,
            A.TCIMS_CUST_ID,
            A.CLIENT_NO,
            A.APPLICANT_PARTY_NO,
            A.SRC_TYPE,
            A.LIST_TYPE,
            A.LIST_PRIORITY,
            A.CUST_NAME,
            A.SEX,
            A.ID_TYPE,
            A.ID_NUMBER,
            A.CUST_DOB,
            A.WORK_UNIT,
            A.DEPARTMENT_CHINESE_NAME,
            A.LIST_CREATED_DATE,
            A.POSITION,
            A.POSITION_CODE,
            A.EDUCATIONAL_BACKGROUND,
            A.MARITAL_STATUS,
            A.PROF_GRADE,
            A.ANNUAL_INCOME,
            A.CONTACT_ADDRESS,
            A.CONTACT_ZIPCODE,
            A.ADDRESS,
            A.ZIP_CODE,
            A.EMAIL,
            A.SHIELD_FLAG,
            A.CAMPAIGN_NAME,
            A.CAMPAIGN_SPLIT_NAME,
            A.BATCH_NAME,
            A.SUPERVISOR_ID,
            A.TEAM_LEADER_ID,
            A.TMR_UM_ID,
            A.PRODUCT_NAME,
            A.CONTACT_DATE,
            A.CALL_RESULT,
            A.IS_INSURE_FLAG,
            A.IS_SUBMIT_FLAG,
            A.IS_FREE_FLAG,
            A.CALL_BACK_REMARK,
            A.SUCCESS_DATE,
            A.TRANSFER_SUCCESS_DAY,
            A.LIST_STATUS,
            A.REGION_CODE,
            A.CREDIT_CARD_CALL_DATE,
            A.SUBMIT_DATE,
            A.CREATED_DATE,
            A.CREATED_BY,
            A.UPDATED_DATE,
            A.UPDATED_BY,
            B.CLEAN_FLAG
      FROM IDL_SEL_SRC_DATA              A,
           IDL_SEL_BACK_DATA_1_TMP       B,
           IDL_EX_BATCH                  F
     WHERE B.CLEAN_FLAG IS NOT NULL
       AND A.SYS_ID = B.SYS_ID
       AND A.TCIMS_BATCH_ID = F.TCIMS_BATCH_ID
       AND F.SERIES_TYPE = '02'
       AND F.BATCH_STATUS = '11'
       AND F.PREPARE_FLAG = '1';
   COMMIT;

    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,53,NULL,'02',NULL,NULL,NULL,NULL);

EXCEPTION
     WHEN OTHERS THEN
        P_ERRMSG := SUBSTR(SQLERRM,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,53,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
        RAISE;
END SP_UNITE_LA_BACK_DATA;

END NETS_TCIMS_BACK_CLEAN_DATA;