CREATE OR REPLACE PACKAGE BODY NETS2_ITSADMIN_TCIMS_SPEEDUPD IS
/*****************************************************************************/
/* */
/* 文件名 : NETS2_ITSADMIN_TCIMS_SPEEDUPD */
/* 功能名 : 产险快速上载 */
/* 作成者 : EX-LIXIAOBING001 */
/* 作成日 : 2011/08/03 */
/* */
/*****************************************************************************/
--包名，用于日志记录
PACKAGE_NAME CONSTANT VARCHAR2(30) := 'NETS2_ITSA_TCIMS_SPEED_UPLOAD';
TYPE CRIS_EXP_BATCH_TYPE IS TABLE OF T_INT_CRIS_EXP_BATCH%ROWTYPE INDEX BY BINARY_INTEGER;
PROCEDURE RECORED_LOG(MESSAGE IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
--INSERT LOG
NETS_COMMOM_UTIL.INSERTLOG(NULL,
PACKAGE_NAME,
PACKAGE_NAME,
MESSAGE,
NULL,
'100');
COMMIT;
END RECORED_LOG;
--==================================================================================
-- 创建人: EX-LIXIAOBING001
-- 功能名： CAMPAIGN_ADAPTATION
-- 功能描述: cris批次信息中取得二级机构、到期月、业务模式、业务模式细分、批次名称信息。二级机构名称、业务模式细分名称，自动建立活动
-- 参数描述： P_CRISBIZORG_ROW 批次与活动组别相关信息
-- P_SECOND_ORG_NAME 二级机构名称
-- P_BIZ_MODE_NAME 业务模式细分名称
--
-- 返回值： T_PUB_CAMPAIGN_ROW 返回活动记录
--==================================================================================
FUNCTION CAMPAIGN_ADAPTATION(P_CRISBIZORG_ROW IN T_INT_CRIS_BIZ_ORG_BATCH%ROWTYPE,
P_SECOND_ORG_NAME IN T_PUB_OPTION.OPTION_NAME%TYPE,
P_BIZ_MODE_NAME IN T_PUB_OPTION.OPTION_NAME%TYPE)
RETURN T_PUB_CAMPAIGN%ROWTYPE IS
T_PUB_CAMPAIGN_ROW T_PUB_CAMPAIGN%ROWTYPE;
BEGIN
SELECT SEQ_PUB_CAMPAIGN_CAMPAIGN_ID.NEXTVAL
INTO T_PUB_CAMPAIGN_ROW.CAMPAIGN_ID
FROM DUAL; --活动ID
T_PUB_CAMPAIGN_ROW.CAMPAIGN_NAME := P_SECOND_ORG_NAME || '-' ||
REPLACE(P_CRISBIZORG_ROW.EXPIRE_DATE,
'-',
'') || '-OB-' ||
P_BIZ_MODE_NAME; --活动名称
T_PUB_CAMPAIGN_ROW.CAMPAIGN_CODE := T_PUB_CAMPAIGN_ROW.CAMPAIGN_ID || '-' ||
P_CRISBIZORG_ROW.SECONDARY_ORG_CODE || '-' ||
SUBSTR(P_CRISBIZORG_ROW.EXPIRE_DATE,
6,
2) || '-' ||
P_CRISBIZORG_ROW.BIZ_MODE; --活动编号
T_PUB_CAMPAIGN_ROW.CAMPAIGN_STATUS := '2'; --活动状态（CAMPAIGN_STATUS）为有效"2"
T_PUB_CAMPAIGN_ROW.PRODUCT_ID := 1000; --产品类型(PRODUCT_ID)为产险，值为1000
T_PUB_CAMPAIGN_ROW.ORG_ID := P_CRISBIZORG_ROW.SECONDARY_ORG_CODE; --二级机CODE
T_PUB_CAMPAIGN_ROW.BIZ_MODEL := P_CRISBIZORG_ROW.BIZ_MODE; --业务模式CODE
T_PUB_CAMPAIGN_ROW.DEFAULT_CAMPAIGN_MODEL := '0'; --预设活动类型(DEFAULT_CAMPAIGN_MODEL)为"名单内客户",值为"0"
T_PUB_CAMPAIGN_ROW.EXPIRED_DATE := P_CRISBIZORG_ROW.EXPIRE_DATE; --到期月份(EXPIRED_DATE)为" 取得到期月"
T_PUB_CAMPAIGN_ROW.SUB_BIZMODEL := P_CRISBIZORG_ROW.SUB_BIZMODEL; --活动描述
T_PUB_CAMPAIGN_ROW.IS_TRIAL := '0'; --是否测试项目(IS_TRIAL)为不是测试项目"0"
T_PUB_CAMPAIGN_ROW.BEGIN_DATE := SYSDATE; --开始日期(BEGIN_DATE)：为第一次建立快速上载活动的日期
T_PUB_CAMPAIGN_ROW.CAMPAIGN_TYPE := '0'; --0代表快速上载类型
SELECT LAST_DAY(ADD_MONTHS(TO_DATE(P_CRISBIZORG_ROW.EXPIRE_DATE,
'YYYY-MM'),
1))
INTO T_PUB_CAMPAIGN_ROW.END_DATE
FROM DUAL; --结束日期(END_DATE)为名单所属到期月的T+1月的最后一天
INSERT INTO T_PUB_CAMPAIGN VALUES T_PUB_CAMPAIGN_row;
RETURN T_PUB_CAMPAIGN_row;
END CAMPAIGN_ADAPTATION;
--==================================================================================
-- 创建人: EX-LIXIAOBING001
-- 功能名： CAMPAIGN_SPLIT_ADAPTATION
-- 功能描述: cris批次信息中取得二级机构、到期月、业务模式、业务模式细分、批次名称信息.和活动编号，自动建立组别
-- 参数描述： P_CRISBIZORG_ROW 批次与活动组别相关信息
-- P_CAMPAIGN_ID 活动编号
--
-- 返回值： T_PUB_CAMPAIGN_SPLIT_ROW 返回组别记录
-- =================================================================================
FUNCTION CAMPAIGN_SPLIT_ADAPTATION(P_CRISBIZORG_ROW IN T_INT_CRIS_BIZ_ORG_BATCH%ROWTYPE,
P_CAMPAIGN_ID IN T_PUB_CAMPAIGN.CAMPAIGN_ID%TYPE)
RETURN T_PUB_CAMPAIGN_SPLIT%ROWTYPE IS
T_PUB_CAMPAIGN_SPLIT_ROW T_PUB_CAMPAIGN_SPLIT%ROWTYPE;
BEGIN
SELECT SEQ_PUB_CMP_SPT_CMP_SPT_ID.NEXTVAL
INTO T_PUB_CAMPAIGN_SPLIT_ROW.CAMPAIGN_SPLIT_ID
FROM DUAL; --活动组别ID
T_PUB_CAMPAIGN_SPLIT_ROW.CAMPAIGN_ID := P_CAMPAIGN_ID;
T_PUB_CAMPAIGN_SPLIT_ROW.CAMPAIGN_SPLIT_NAME := '快速上载';
T_PUB_CAMPAIGN_SPLIT_ROW.CAMPAIGN_SPLIT_CODE := T_PUB_CAMPAIGN_SPLIT_ROW.CAMPAIGN_SPLIT_ID || '-' ||
P_CRISBIZORG_ROW.SECONDARY_ORG_CODE || '-' ||
SUBSTR(P_CRISBIZORG_ROW.EXPIRE_DATE,
6,
2) || '-' ||
P_CRISBIZORG_ROW.BIZ_MODE;
T_PUB_CAMPAIGN_SPLIT_ROW.CAMPAIGN_SPLIT_DESC := '';
T_PUB_CAMPAIGN_SPLIT_ROW.STATUS := '2'; --组别状态为"有效"
T_PUB_CAMPAIGN_SPLIT_ROW.START_DATE := SYSDATE;
SELECT LAST_DAY(ADD_MONTHS(TO_DATE(P_CRISBIZORG_ROW.EXPIRE_DATE,
'YYYY-MM'),
1))
INTO T_PUB_CAMPAIGN_SPLIT_ROW.END_DATE
FROM DUAL; --结束日期为名单所属到期月的T+1月的最后一天
INSERT INTO T_PUB_CAMPAIGN_SPLIT VALUES T_PUB_CAMPAIGN_SPLIT_ROW;
RETURN T_PUB_CAMPAIGN_SPLIT_ROW;
END CAMPAIGN_SPLIT_ADAPTATION;
--==================================================================================
-- 创建人: EX-LIXIAOBING001
-- 功能名： UPLOAD_ADAPTATION
-- 功能描述: 新增一条上载名单记录表(T_PUB_UPLOAD)信息
-- 参数描述： P_BATCH_NAME 批次名称
-- P_CRIS_BATCH_NO cris批次编号
-- P_BATCH_CUST_NUM 批次切分出的客户数量
-- P_CAMPAIGN_ID 活动编号
-- P_CAMPAIGN_SPLIT_ID 组别编号
-- P_ONSITE_MGR 现场经理
--
-- 返回值： T_PUB_UPLOAD_ROW 上载名单记录
-- =================================================================================
FUNCTION UPLOAD_ADAPTATION(P_BATCH_NAME IN T_INT_CRIS_EXP_BATCH.BATCH_NAME%TYPE,
P_CRIS_BATCH_NO IN T_INT_CRIS_EXP_BATCH.CRIS_BATCH_NO%TYPE,
P_BATCH_CUST_NUM IN T_INT_CRIS_EXP_BATCH.BATCH_CUST_NUM%TYPE,
P_CAMPAIGN_ID IN T_PUB_CAMPAIGN.CAMPAIGN_ID%TYPE,
P_CAMPAIGN_SPLIT_ID IN T_PUB_CAMPAIGN_SPLIT.CAMPAIGN_SPLIT_ID%TYPE,
P_ONSITE_MGR IN T_AAS_ONSITE_MGR_SNAPSHOT.ONSITE_MGR%TYPE)
RETURN T_PUB_UPLOAD%ROWTYPE IS
T_PUB_UPLOAD_ROW T_PUB_UPLOAD%ROWTYPE;
BEGIN
SELECT SEQ_PUB_UPLOAD_UPLOAD_ID.NEXTVAL
INTO T_PUB_UPLOAD_ROW.UPLOAD_ID
FROM DUAL;
T_PUB_UPLOAD_ROW.UPLOAD_NAME := P_BATCH_NAME;
T_PUB_UPLOAD_ROW.BATCH_CODE := P_CRIS_BATCH_NO;
T_PUB_UPLOAD_ROW.CAMPAIGN_ID := P_CAMPAIGN_ID;
T_PUB_UPLOAD_ROW.CAMPAIGN_SPLIT_ID := P_CAMPAIGN_SPLIT_ID;
T_PUB_UPLOAD_ROW.INSURE_TYPE := '1'; --险种类型（INSURE_TYPE）为"1"
T_PUB_UPLOAD_ROW.STATUS := '12'; --名单状态(STATUS)为"12"
T_PUB_UPLOAD_ROW.CUST_TYPE := '12'; --客户类型CUST_TYPE为"平安车续保","值为12"
T_PUB_UPLOAD_ROW.PRIORITY := 400; --任务优次PRIORITY为400
T_PUB_UPLOAD_ROW.IS_ONLINE := '0'; --网销用字段IS_ONLINE为"0"
T_PUB_UPLOAD_ROW.SUPERVISOR := P_ONSITE_MGR;
T_PUB_UPLOAD_ROW.ARCHIVE_NUM := P_BATCH_CUST_NUM;
T_PUB_UPLOAD_ROW.BATCH_TYPE := '501001'; --名单批次类型BATCH_TYPE为"电销车险",值为"501001"
T_PUB_UPLOAD_ROW.SOURCE_TYPE := '2'; --数据来源SOURCE_TYPE为"2"
INSERT INTO T_PUB_UPLOAD VALUES T_PUB_UPLOAD_ROW;
RETURN T_PUB_UPLOAD_ROW;
END UPLOAD_ADAPTATION;
--==================================================================================
-- 创建人: EX-LIXIAOBING001
-- 功能名： UPLOAD_AUTOMATION_ADAPTATION
-- 功能描述: 新增一条批量更新任务表(T_PUB_UPLOAD_AUTOMATION)信息
-- 参数描述： P_UPLOAD_ID 上载名单记录编号
--
-- 返回值： T_PUB_UPLOAD_AUTOMATION_ROW 批量更新任务记录
-- =================================================================================
FUNCTION UPLOAD_AUTOMATION_ADAPTATION(P_UPLOAD_ID IN T_PUB_UPLOAD.UPLOAD_ID%TYPE)
RETURN T_PUB_UPLOAD_AUTOMATION%ROWTYPE IS
T_PUB_UPLOAD_AUTOMATION_ROW T_PUB_UPLOAD_AUTOMATION%ROWTYPE;
BEGIN
SELECT SEQ_PUB_UPLOAD_AMT_AMT_ID.NEXTVAL
INTO T_PUB_UPLOAD_AUTOMATION_ROW.AUTOMATION_ID
FROM DUAL;
T_PUB_UPLOAD_AUTOMATION_ROW.UPLOAD_ID := P_UPLOAD_ID;
T_PUB_UPLOAD_AUTOMATION_ROW.CUST_CREATION_CNT := 0; --创建客户资料数量（CUST_CREATION_CNT）为"0"
T_PUB_UPLOAD_AUTOMATION_ROW.CUST_UPDATE_CNT := 0; --更新客户资料数量（CUST_UPDATE_CNT）为"0"
T_PUB_UPLOAD_AUTOMATION_ROW.VEH_CREATION_CNT := 0; --创建车辆资料数量（ VEH_CREATION_CNT）为"0"
T_PUB_UPLOAD_AUTOMATION_ROW.VEH_UPDATE_CNT := 0; --更新车辆资料数量（VEH_UPDATE_CNT）为"0"
T_PUB_UPLOAD_AUTOMATION_ROW.STATUS := 9; --名单处理状态（STATUS）为"9"
T_PUB_UPLOAD_AUTOMATION_ROW.UPLOAD_TYPE := 'CRIS'; --名单上载类型(UPLOAD_TYPE)为"CRIS"
INSERT INTO T_PUB_UPLOAD_AUTOMATION VALUES T_PUB_UPLOAD_AUTOMATION_ROW;
RETURN T_PUB_UPLOAD_AUTOMATION_ROW;
END UPLOAD_AUTOMATION_ADAPTATION;
--==================================================================================
-- 创建人: EX-LIXIAOBING001
-- 功能名： GET_CAMPAIGN_ID_and_SPLIT_ID
-- 功能描述: 根据二级机构code、到期月、业务模式、业务模式细分查询活动和组别
-- 输入参数描述： EXPIRE_DATE 到期月
-- 输入参数描述： SECONDARY_ORG_CODE 二级机构code
-- 输入参数描述： SUB_BIZMODEL 业务模式细分
-- 输入参数描述： BIZ_MODE 业务模式
--
-- 输出参数描述： V_CAMPAIGN_ID 活动id
-- 输出参数描述： V_CAMPAIGN_SPLIT_ID 组别id
-- 输出参数描述： V_SECONDARY_ORG_Name 二级机构名称
-- 输出参数描述： V_SUB_BIZMODEL_name 业务模式细分名称
-- 输出参数描述： V_ERROR_CODE 异常code 0表示获取二级机构名称没有数据、1表示获取业务模式细分名称没有数据、2表示获取活动组别没有数据
-- =================================================================================
PROCEDURE GET_CAMPAIGN_ID_AND_SPLIT_ID(EXPIRE_DATE IN T_INT_CRIS_BIZ_ORG_BATCH.EXPIRE_DATE%TYPE,
SECONDARY_ORG_CODE IN T_INT_CRIS_BIZ_ORG_BATCH.SECONDARY_ORG_CODE%TYPE,
SUB_BIZMODEL IN T_INT_CRIS_BIZ_ORG_BATCH.SUB_BIZMODEL%TYPE,
BIZ_MODE IN T_INT_CRIS_BIZ_ORG_BATCH.BIZ_MODE%TYPE,
V_CAMPAIGN_ID OUT T_PUB_CAMPAIGN.CAMPAIGN_ID%TYPE,
V_CAMPAIGN_SPLIT_ID OUT T_PUB_CAMPAIGN_SPLIT.CAMPAIGN_SPLIT_ID%TYPE,
V_SECONDARY_ORG_NAME OUT T_PUB_OPTION.OPTION_NAME%TYPE,
V_SUB_BIZMODEL_NAME OUT T_PUB_OPTION.OPTION_NAME%TYPE,
V_ERROR_CODE OUT VARCHAR2) IS
BEGIN
--根据二级机构代码获取二级机构名称:
BEGIN
SELECT P.OPTION_NAME
INTO V_SECONDARY_ORG_NAME
FROM T_PUB_OPTION P
WHERE P.OPTION_DEFINITION_ID = 2001
AND P.STATUS = 'A'
AND P.OPTION_CODE = SECONDARY_ORG_CODE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_ERROR_CODE := 0;
END;
--根据业务模式细分编码获取业务模式细分名称
IF V_ERROR_CODE IS NULL THEN
BEGIN
SELECT P.OPTION_NAME
INTO V_SUB_BIZMODEL_NAME
FROM T_PUB_OPTION P
WHERE P.OPTION_DEFINITION_ID = 600
AND P.STATUS = 'A'
AND P.OPTION_CODE = SUB_BIZMODEL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_ERROR_CODE := 1;
END;
END IF;
--查询活动和活动组别
IF V_ERROR_CODE IS NULL THEN
BEGIN
SELECT TEMP.CAMPAIGN_ID, S.CAMPAIGN_SPLIT_ID
INTO V_CAMPAIGN_ID, V_CAMPAIGN_SPLIT_ID
FROM (SELECT C.CAMPAIGN_ID
FROM T_PUB_CAMPAIGN C
WHERE C.ORG_ID = SECONDARY_ORG_CODE
AND C.EXPIRED_DATE = EXPIRE_DATE
AND C.BIZ_MODEL = BIZ_MODE
AND C.SUB_BIZMODEL = SUB_BIZMODEL
AND C.CAMPAIGN_TYPE = '0'
AND C.CAMPAIGN_NAME = V_SECONDARY_ORG_NAME || '-' ||
REPLACE(EXPIRE_DATE, '-', '') ||
'-OB-' || V_SUB_BIZMODEL_NAME
AND C.CAMPAIGN_STATUS = '2') TEMP,
T_PUB_CAMPAIGN_SPLIT S
WHERE TEMP.CAMPAIGN_ID = S.CAMPAIGN_ID
AND S.STATUS = '2'
AND S.CAMPAIGN_SPLIT_NAME = '快速上载'
AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_ERROR_CODE := 2;
END;
END IF;
END GET_CAMPAIGN_ID_AND_SPLIT_ID;
--==================================================================================
-- 创建人: EX-LIXIAOBING001
-- 功能名： UPDATE_BATCH_STATUS
-- 功能描述: 根据批次号更新批次表批次状态
-- 参数描述： P_CRIS_BATCH_NO 批次号
-- P_BATCH_STATUS 批次状态
--==================================================================================
PROCEDURE UPDATE_BATCH_STATUS(P_CRIS_BATCH_NO IN T_INT_CRIS_EXP_BATCH.CRIS_BATCH_NO%TYPE,
P_BATCH_STATUS IN T_INT_CRIS_EXP_BATCH.BATCH_STATUS%TYPE) IS
BEGIN
UPDATE T_INT_CRIS_EXP_BATCH
SET BATCH_STATUS = P_BATCH_STATUS
WHERE CRIS_BATCH_NO = P_CRIS_BATCH_NO;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END UPDATE_BATCH_STATUS;
--==================================================================================
-- 创建人: EX-LIXIAOBING001
-- 功能名： ICIMS_BATCH_AUTO_UPLOAD
-- 功能描述: 产险快速上载 主程序
-- =================================================================================
PROCEDURE ICIMS_BATCH_AUTO_UPLOAD IS
CRIS_EXP_BATCH_LIST CRIS_EXP_BATCH_TYPE;
CRIS_BIZ_ORG_BATCH_ROW T_INT_CRIS_BIZ_ORG_BATCH%ROWTYPE;
V_SECONDARY_ORG_NAME T_PUB_OPTION.OPTION_NAME%TYPE;
V_SUB_BIZMODEL_NAME T_PUB_OPTION.OPTION_NAME%TYPE;
V_CAMPAIGN_SPLIT_ID T_PUB_CAMPAIGN_SPLIT.CAMPAIGN_SPLIT_ID%TYPE;
V_CAMPAIGN_ID T_PUB_CAMPAIGN.CAMPAIGN_ID%TYPE;
T_PUB_CAMPAIGN_ROW T_PUB_CAMPAIGN%ROWTYPE;
CAMPAIGN_SPLIT_ROW T_PUB_CAMPAIGN_SPLIT%ROWTYPE;
V_ONSITE_MGR T_AAS_ONSITE_MGR_SNAPSHOT.ONSITE_MGR%TYPE;
T_PUB_UPLOAD_ROW T_PUB_UPLOAD%ROWTYPE;
T_PUB_UPLOAD_AUTOMATION_ROW T_PUB_UPLOAD_AUTOMATION%ROWTYPE;
V_ERROR_CODE VARCHAR2(20);
NO_BASE_DATA_FOUND EXCEPTION; --自定义异常：当没有查询到基础数据(没有二级机构名称、业务模式细分名称)时，抛出此异常
BEGIN
-- 获取批次编号，需要下发的批次
SELECT TI.* BULK COLLECT
INTO CRIS_EXP_BATCH_LIST
FROM T_INT_CRIS_EXP_BATCH TI
WHERE TI.BATCH_ASSIGN_TYPE = '3'
AND TI.AUTOMATION_ID IS NULL
AND TI.BATCH_STATUS IN ('0','1');
IF CRIS_EXP_BATCH_LIST.COUNT > 0 THEN
--获取到批次后，循环...
FOR i IN CRIS_EXP_BATCH_LIST.FIRST .. CRIS_EXP_BATCH_LIST.LAST LOOP
BEGIN
--根据cris批次编号(CRIS_BATCH_NO)到新建表T_INT_CRIS_BIZ_ORG_BATCH查询获取二级机构、到期月、业务模式、业务模式细分编码、业务模式细分等信息
<<GET_CRIS_BIZ_ORG_BATCH_BLOCK>>
BEGIN
SELECT TIC.*
INTO CRIS_BIZ_ORG_BATCH_ROW
FROM T_INT_CRIS_BIZ_ORG_BATCH TIC
WHERE TIC.CRIS_BATCH_NO = CRIS_EXP_BATCH_LIST(i).CRIS_BATCH_NO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RECORED_LOG('[快速上载 ICIMS_BATCH_AUTO_UPLOAD]根据cris批次编号(CRIS_BATCH_NO){CRIS_BATCH_NO =' ||
CRIS_EXP_BATCH_LIST(i)
.CRIS_BATCH_NO ||
'}到表T_INT_CRIS_BIZ_ORG_BATCH查询获取关联信息.没有找到数据');
RAISE;
END GET_CRIS_BIZ_ORG_BATCH_BLOCK;
--查询到二级机构、到期月、业务模式、业务模式细分编码等信息后，查询活动组别
GET_CAMPAIGN_ID_AND_SPLIT_ID(CRIS_BIZ_ORG_BATCH_ROW.EXPIRE_DATE,
CRIS_BIZ_ORG_BATCH_ROW.SECONDARY_ORG_CODE,
CRIS_BIZ_ORG_BATCH_ROW.SUB_BIZMODEL,
CRIS_BIZ_ORG_BATCH_ROW.BIZ_MODE,
V_CAMPAIGN_ID,
V_CAMPAIGN_SPLIT_ID,
V_SECONDARY_ORG_NAME,
V_SUB_BIZMODEL_NAME,
V_ERROR_CODE);
IF V_ERROR_CODE = '0' THEN
RECORED_LOG('[快速上载 ICIMS_BATCH_AUTO_UPLOAD]根据cris批次编号(CRIS_BATCH_NO){CRIS_BATCH_NO =' ||
CRIS_EXP_BATCH_LIST(i)
.CRIS_BATCH_NO ||
',OPTION_DEFINITION_ID=2001,STATUS=A,OPTION_CODE=' ||
CRIS_BIZ_ORG_BATCH_ROW.SECONDARY_ORG_CODE ||
')}到表(T_PUB_OPTION)查询二级机构名称.没有找到数据.');
RAISE NO_BASE_DATA_FOUND;
END IF;
IF V_ERROR_CODE = '1' THEN
RECORED_LOG('[快速上载 ICIMS_BATCH_AUTO_UPLOAD]根据cris批次编号(CRIS_BATCH_NO){CRIS_BATCH_NO =' ||
CRIS_EXP_BATCH_LIST(i)
.CRIS_BATCH_NO ||
',OPTION_DEFINITION_ID=600,STATUS=A,OPTION_CODE=' ||
CRIS_BIZ_ORG_BATCH_ROW.SUB_BIZMODEL ||
')}到表(T_PUB_OPTION)查询业务模式细分名称.没有找到数据.');
RAISE NO_BASE_DATA_FOUND;
END IF;
IF V_ERROR_CODE = '2' THEN
--自动建立活动
T_PUB_CAMPAIGN_ROW := CAMPAIGN_ADAPTATION(CRIS_BIZ_ORG_BATCH_ROW,
V_SECONDARY_ORG_NAME,
V_SUB_BIZMODEL_NAME);
V_CAMPAIGN_ID := T_PUB_CAMPAIGN_ROW.CAMPAIGN_ID;
--自动建立组别
CAMPAIGN_SPLIT_ROW := CAMPAIGN_SPLIT_ADAPTATION(CRIS_BIZ_ORG_BATCH_ROW,
V_CAMPAIGN_ID);
V_CAMPAIGN_SPLIT_ID := CAMPAIGN_SPLIT_ROW.CAMPAIGN_SPLIT_ID;
END IF;
--根据二级机构、到期月、业务模式到机构模式应用表(T_AAS_BIZ_ORG_APPLICATION)、现场管理权限快照表(T_AAS_ONSITE_MGR_SNAPSHOT)查询随机取得现场经理。如果没有查询到现场经理，记录异常,流结束
<<GET_ONSITE_MGR_BLOCK>>
BEGIN
SELECT TEMP.ONSITE_MGR
INTO V_ONSITE_MGR
FROM (SELECT T.ONSITE_MGR
FROM T_AAS_BIZ_ORG_APPLICATION N,
T_AAS_ONSITE_MGR_SNAPSHOT T
WHERE N.BIZ_ORG_APPLICATION_ID =
T.BIZ_ORG_APPLICATION_ID
AND N.SECONDARY_ORG_CODE =
CRIS_BIZ_ORG_BATCH_ROW.SECONDARY_ORG_CODE
AND N.EXPIRE_DATE =
CRIS_BIZ_ORG_BATCH_ROW.EXPIRE_DATE
AND N.BIZ_MODE = CRIS_BIZ_ORG_BATCH_ROW.BIZ_MODE
ORDER BY DBMS_RANDOM.VALUE) TEMP
WHERE ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RECORED_LOG('[快速上载 ICIMS_BATCH_AUTO_UPLOAD]根据cris批次编号(CRIS_BATCH_NO){CRIS_BATCH_NO =' ||
CRIS_EXP_BATCH_LIST(i)
.CRIS_BATCH_NO || ',二级机构(SECONDARY_ORG_CODE=' ||
CRIS_BIZ_ORG_BATCH_ROW.SECONDARY_ORG_CODE ||
'),到期月(EXPIRE_DATE=' ||
CRIS_BIZ_ORG_BATCH_ROW.EXPIRE_DATE ||
'),业务模式(BIZ_MODE=' ||
CRIS_BIZ_ORG_BATCH_ROW.BIZ_MODE ||
')}到机构模式应用表(T_AAS_BIZ_ORG_APPLICATION)、现场管理权限快照表(T_AAS_ONSITE_MGR_SNAPSHOT)查询随机取得现场经理.没有找到数据.');
RAISE;
END GET_ONSITE_MGR_BLOCK;
--新增一条上载名单记录表(T_PUB_UPLOAD)信息
T_PUB_UPLOAD_ROW := UPLOAD_ADAPTATION(CRIS_EXP_BATCH_LIST(i)
.BATCH_NAME,
CRIS_EXP_BATCH_LIST(i)
.CRIS_BATCH_NO,
CRIS_EXP_BATCH_LIST(i)
.BATCH_CUST_NUM,
V_CAMPAIGN_ID,
V_CAMPAIGN_SPLIT_ID,
V_ONSITE_MGR);
--新增一条批量更新任务表(T_PUB_UPLOAD_AUTOMATION)信息
T_PUB_UPLOAD_AUTOMATION_ROW := UPLOAD_AUTOMATION_ADAPTATION(T_PUB_UPLOAD_ROW.UPLOAD_ID);
--更新T_INT_CRIS_EXP_BATCH
UPDATE T_INT_CRIS_EXP_BATCH
SET AUTOMATION_ID = T_PUB_UPLOAD_AUTOMATION_ROW.AUTOMATION_ID,
CUST_TYPE = '12', --客户类型（CUST_TYPE）为"平安车续保" , 值为"12"
PRIORITY = '400', --任务优次（PRIORITY）为400
BATCH_TYPE = '501001', --名单批次类型 BATCH_TYPE为"电销车险" ,值为"501001"
SUPERVISOR = V_ONSITE_MGR,
BATCH_DESC = NULL,
CAMPAIGN_ID = V_CAMPAIGN_ID,
CAMPAIGN_SPLIT_ID = V_CAMPAIGN_SPLIT_ID,
BATCH_STATUS = '4' --批次状态(BATCH_STATUS) 为"4"
WHERE CRIS_BATCH_NO = CRIS_EXP_BATCH_LIST(i).CRIS_BATCH_NO;
COMMIT WORK;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK;
UPDATE_BATCH_STATUS(CRIS_EXP_BATCH_LIST(i).CRIS_BATCH_NO,'5'); --5表示快速上载自动匹配失败
WHEN NO_BASE_DATA_FOUND THEN
ROLLBACK;
UPDATE_BATCH_STATUS(CRIS_EXP_BATCH_LIST(i).CRIS_BATCH_NO,'5'); --5表示快速上载自动匹配失败
WHEN OTHERS THEN
ROLLBACK;
UPDATE_BATCH_STATUS(CRIS_EXP_BATCH_LIST(i).CRIS_BATCH_NO,'5'); --5表示快速上载自动匹配失败
RECORED_LOG('[快速上载 ICIMS_BATCH_AUTO_UPLOAD]根据cris批次编号(CRIS_BATCH_NO){CRIS_BATCH_NO =' ||
CRIS_EXP_BATCH_LIST(i).CRIS_BATCH_NO || ',错误信息:' ||
substr(sqlerrm, 1, 100));
END;
END LOOP;
ELSE
RECORED_LOG('[快速上载 ICIMS_BATCH_AUTO_UPLOAD]tcims批次查询{batch_assign_type =3，ti.automation_id is null,BATCH_STATUS IN (0,1)}没有找到数据.');
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END ICIMS_BATCH_AUTO_UPLOAD;
END NETS2_ITSADMIN_TCIMS_SPEEDUPD;
在需要单独设置事务的地方加入：PRAGMA AUTONOMOUS_TRANSACTION;
场景：
1、查询活动和组别，如果没有新建活动和组别
2、有了活动和组别后，查询现场经理，如果没有，结束本次循环进入下次循环。记录日志。
而在记录日志时要进行commit,但是新建活动和组别应该是失败的。
这种情况就要让记录日志作为单独的事务进行提交。
2948
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
