创建包：
CREATE OR REPLACE PACKAGE WYL_TEST_PKG_GGYW_XZZX IS -- Purpose : 公共业务-参保险种注销 --注销选择的险种，并将参保缴费信息，转入信息备份到险种注销备份表 --备份数据
PROCEDURE XZZX_BFSJ(PI_YWBM IN VARCHAR2, --业务表名
PI_BFBM IN VARCHAR2, --备份表名
PI_BFTJ IN VARCHAR2, --备份条件（字符串）
PI_BCXXC IN VARCHAR2, --补充信息串（CAE837=?***BFE229=?)
PO_FHZ OUT VARCHAR2, --返回值
PO_MSG OUT VARCHAR2 --返回信息
); --备份判断
PROCEDURE XZZX_HBPD(PI_OWNER1 IN VARCHAR2,
PI_BM1 IN VARCHAR2,
PI_OWNER2 IN VARCHAR2,
PI_BM2 IN VARCHAR2,
PO_ZDXXC1 OUT VARCHAR2, --insert的列字段串
PO_ZDXXC2 OUT VARCHAR2, --select的列字段串
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2); --注销信息登记
PROCEDURE XZZX_START(PI_BAE001 IN VARCHAR2,
PI_BAE007 IN VARCHAR2,
PI_AAC001 IN NUMBER,
PI_AAB001 IN NUMBER,
PI_AAE140 IN VARCHAR2,
PI_AAZ159 IN NUMBER,
PI_AAE002 IN NUMBER,
PI_CAE440 IN VARCHAR2,
PI_AAE011 IN VARCHAR2,
PI_AAE013 IN VARCHAR2,
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2); --险种注销-回退
PROCEDURE XZZX_HT(PI_BAE007 IN VARCHAR2,
PI_AAE012 IN VARCHAR2,
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2); --险种注销-复核通过
PROCEDURE XZZX_FH(PI_BAE007 IN VARCHAR2,
PI_AAE012 IN VARCHAR2,
PI_CAE441 IN VARCHAR2,
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2);
END WYL_TEST_PKG_GGYW_XZZX;
创建包体：
CREATE OR REPLACE PACKAGE BODY WYL_TEST_PKG_GGYW_XZZX IS --常量定义
C_PKG_NAME CONSTANT VARCHAR2(20) := 'Pkg_Ggyw_Xzzx.'; C_AAC008_ZZCB CONSTANT VARCHAR2(6) := PKG_FUN.F_GET_DICT('AAC008', 'ZZCB'); --个人参保状态-终止参保
C_CAE009_ZR CONSTANT VARCHAR2(6) := PKG_FUN.F_GET_DICT('CAE009', 'ZR'); --转移方向-转入
C_CAE441_YHT CONSTANT VARCHAR2(6) := PKG_FUN.F_GET_DICT('CAE441', 'YHT'); --险种注销标志-已回退
C_CAE441_YZX CONSTANT VARCHAR2(6) := PKG_FUN.F_GET_DICT('CAE441', 'YZX'); --险种注销标志-已注销
C_CAE441_WZX CONSTANT VARCHAR2(6) := PKG_FUN.F_GET_DICT('CAE441', 'WZX'); --险种注销标志-未注销
--险种注销信息
TYPE REC_XZZX_INFO IS RECORD(
AAC001 AC01.AAC001%TYPE, --人员ID
AAB001 AC01.AAB001%TYPE, --单位ID
AAE140 AC02.AAE140%TYPE, --险种编号
AAZ159 AC02.AAZ159%TYPE, --参保关系ID
BAE007 SAE52.BAE007%TYPE,
BAE001 SAE52.BAE001%TYPE,
AAE002 SAE52.AAE002%TYPE,
CAE440 SAE52.CAE440%TYPE,
CAE441 SAE52.CAE441%TYPE); --备份数据
PROCEDURE XZZX_BFSJ(PI_YWBM IN VARCHAR2, --业务表名
PI_BFBM IN VARCHAR2, --备份表名
PI_BFTJ IN VARCHAR2, --备份条件（字符串）
PI_BCXXC IN VARCHAR2, --补充信息串（CAE837=?***BFE229=?)
PO_FHZ OUT VARCHAR2, --返回值
PO_MSG OUT VARCHAR2 --返回信息
) IS V_PARAMS VARCHAR2(2000); --传入参数串
V_PROCNAME VARCHAR2(50) := C_PKG_NAME || 'ryhb_bfsj'; -- 过程名
V_OBJECT_NAME1 USER_OBJECTS.OBJECT_NAME%TYPE;
V_OBJECT_TYPE1 USER_OBJECTS.OBJECT_TYPE%TYPE;
V_TABLE_OWNER1 USER_SYNONYMS.TABLE_OWNER%TYPE;
V_TABLE_NAME1 USER_SYNONYMS.TABLE_NAME%TYPE; V_OBJECT_NAME2 USER_OBJECTS.OBJECT_NAME%TYPE;
V_OBJECT_TYPE2 USER_OBJECTS.OBJECT_TYPE%TYPE;
V_TABLE_OWNER2 USER_SYNONYMS.TABLE_OWNER%TYPE;
V_TABLE_NAME2 USER_SYNONYMS.TABLE_NAME%TYPE;
V_ZDXXC1 VARCHAR2(4000); --业务表字段信息串1
V_ZDXXC2 VARCHAR2(4000); --业务表字段信息串2
V_INSERTSQL VARCHAR2(4000);
V_SELECTSQL VARCHAR2(4000);
V_CAE442 VARCHAR2(20); --险种注销业务流水号
V_NAMEVALUE PKG_UTILITY.VC2000_TABLE; BEGIN
--数据初始化
PO_FHZ := '';
PO_MSG := '成功';
V_PARAMS := ',传入参数为:pi_ywbm=' || PI_YWBM || ';pi_bfbm=' || PI_BFBM ||
';pi_bftj=' || PI_BFTJ || ';pi_bcxxc=' || PI_BCXXC;
--判断业务表是否存在对象-----------------
BEGIN
SELECT OBJECT_NAME, OBJECT_TYPE
INTO V_OBJECT_NAME1, V_OBJECT_TYPE1
FROM USER_OBJECTS A
WHERE A.OBJECT_TYPE IN ('SYNONYM', 'TABLE')
AND A.OBJECT_NAME = UPPER(PI_YWBM); EXCEPTION
WHEN OTHERS THEN
PO_FHZ := V_PROCNAME || '_001';
PO_MSG := '需备份的业务表对象不存在或非表对象，请检查,pi_ywbm=' || PI_YWBM;
END; IF V_OBJECT_TYPE1 = 'SYNONYM' THEN
BEGIN
SELECT B.TABLE_OWNER, B.TABLE_NAME
INTO V_TABLE_OWNER1, V_TABLE_NAME1
FROM USER_SYNONYMS B
WHERE B.SYNONYM_NAME = V_OBJECT_NAME1; SELECT OBJECT_NAME, OBJECT_TYPE
INTO V_OBJECT_NAME1, V_OBJECT_TYPE1
FROM ALL_OBJECTS A
WHERE A.OBJECT_TYPE IN ('TABLE')
AND A.OWNER = UPPER(V_TABLE_OWNER1)
AND A.OBJECT_NAME = UPPER(V_TABLE_NAME1);
EXCEPTION
WHEN OTHERS THEN
PO_FHZ := V_PROCNAME || '_002';
PO_MSG := '需备份的业务表对象不存在或非表对象,请检查用户是' || V_TABLE_OWNER1 || '的表' ||
V_TABLE_NAME1;
END;
ELSE
SELECT USER INTO V_TABLE_OWNER1 FROM DUAL;
END IF; --判断备份表是否存在对象-----------------
BEGIN
SELECT OBJECT_NAME, OBJECT_TYPE
INTO V_OBJECT_NAME2, V_OBJECT_TYPE2
FROM USER_OBJECTS A
WHERE A.OBJECT_TYPE IN ('SYNONYM', 'TABLE')
AND A.OBJECT_NAME = UPPER(PI_BFBM); EXCEPTION
WHEN OTHERS THEN
PO_FHZ := V_PROCNAME || '_003';
PO_MSG := '需备份的业务表对象不存在或非表对象，请检查,pi_bfbm=' || PI_BFBM;
END; IF V_OBJECT_TYPE2 = 'SYNONYM' THEN
BEGIN
SELECT B.TABLE_OWNER, B.TABLE_NAME
INTO V_TABLE_OWNER2, V_TABLE_NAME2
FROM USER_SYNONYMS B
WHERE B.SYNONYM_NAME = V_OBJECT_NAME2; SELECT OBJECT_NAME, OBJECT_TYPE
INTO V_OBJECT_NAME2, V_OBJECT_TYPE2
FROM ALL_OBJECTS A
WHERE A.OBJECT_TYPE IN ('TABLE')
AND A.OWNER = UPPER(V_TABLE_OWNER2)
AND A.OBJECT_NAME = UPPER(V_TABLE_NAME2);
EXCEPTION
WHEN OTHERS THEN
PO_FHZ := V_PROCNAME || '_004';
PO_MSG := '需备份的业务表对象不存在或非表对象,请检查用户是' || V_TABLE_OWNER2 || '的表' ||
V_TABLE_NAME2;
END;
ELSE
SELECT USER INTO V_TABLE_OWNER2 FROM DUAL;
END IF; --判断业务表与备份表除去备份字段其它字段是否一致,并生成列串
XZZX_HBPD(V_TABLE_OWNER1,
V_OBJECT_NAME1,
V_TABLE_OWNER2,
V_OBJECT_NAME2,
V_ZDXXC1,
V_ZDXXC2,
PO_FHZ,
PO_MSG); IF PO_FHZ <> '' THEN
RETURN;
END IF; --拆分补充信息串
IF PI_BCXXC IS NOT NULL AND LENGTH(PI_BCXXC) > 0 THEN
-- 拆分where串
PKG_UTILITY.STR_TO_NAMEVALUE(PI_BCXXC,
'CAE442',
V_NAMEVALUE,
'***',
'=');
END IF; --取值
V_CAE442 := V_NAMEVALUE('CAE442'); --注销业务流水号'
IF V_CAE442 IS NULL THEN
PO_FHZ := V_PROCNAME || '_005';
PO_MSG := '险种注销业务流水号不能为空,CAE442=' || NVL(V_CAE442, '空'); RETURN;
END IF;
--备份数据
V_INSERTSQL := 'insert into ' || PI_BFBM || ' (' || V_ZDXXC1 ||
',CAE442) ';
V_SELECTSQL := ' select ' || V_ZDXXC2 || ', ''' || V_CAE442 ||
''' CAE442' || ' from ' || PI_YWBM || ' a '; EXECUTE IMMEDIATE V_INSERTSQL || V_SELECTSQL || PI_BFTJ; EXCEPTION
WHEN OTHERS THEN
PO_FHZ := V_PROCNAME || '_999';
PO_MSG := PKG_FUN.F_ERRMSG(V_PROCNAME, SQLCODE, SQLERRM, V_PARAMS);
RETURN;
END XZZX_BFSJ; --备份判断
PROCEDURE XZZX_HBPD(PI_OWNER1 IN VARCHAR2,
PI_BM1 IN VARCHAR2,
PI_OWNER2 IN VARCHAR2,
PI_BM2 IN VARCHAR2,
PO_ZDXXC1 OUT VARCHAR2, --insert的列字段串
PO_ZDXXC2 OUT VARCHAR2, --select的列字段串
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2) IS
--表判断
V_PARAMS VARCHAR2(2000); --传入参数串
--v_procname VARCHAR2(50) :=C_PKG_NAME||'grbgjl_zzztxsx'; -- 过程名
V_PROCNAME VARCHAR2(50) := '' || 'ryhb_hbpd'; -- 过程名
V_COUNT NUMBER; CURSOR CUR_TAB_COL1 IS
SELECT *
FROM ALL_TAB_COLUMNS A
WHERE A.OWNER = PI_OWNER1
AND A.TABLE_NAME = PI_BM1
ORDER BY A.COLUMN_ID; BEGIN
--数据初始化
PO_FHZ := '';
PO_MSG := '成功';
V_PARAMS := ',传入参数为:pi_owner1=' || PI_OWNER1 || ';pi_bm1=' || PI_BM1 ||
';pi_owner2=' || PI_OWNER2 || ';pi_bm2=' || PI_BM2;
SELECT COUNT(1)
INTO V_COUNT
FROM ALL_TAB_COLUMNS A
WHERE A.OWNER = PI_OWNER1
AND A.TABLE_NAME = PI_BM1
AND NOT EXISTS (SELECT 1
FROM ALL_TAB_COLUMNS B
WHERE B.COLUMN_NAME != 'CAE442'
AND B.OWNER = PI_OWNER2
AND B.TABLE_NAME = PI_BM2
AND A.COLUMN_NAME = B.COLUMN_NAME);
IF V_COUNT > 0 THEN
PO_FHZ := V_PROCNAME || '_001';
PO_MSG := '需备份的业务表:' || PI_BM1 || '的字段比备份表:' || PI_BM2 || '字段多，请检查';
RETURN;
END IF; SELECT COUNT(1)
INTO V_COUNT
FROM ALL_TAB_COLUMNS A
WHERE A.COLUMN_NAME != 'CAE442'
AND A.OWNER = PI_OWNER2
AND A.TABLE_NAME = PI_BM2
AND NOT EXISTS (SELECT 1
FROM ALL_TAB_COLUMNS B
WHERE B.OWNER = PI_OWNER1
AND B.TABLE_NAME = PI_BM1
AND A.COLUMN_NAME = B.COLUMN_NAME);
IF V_COUNT > 0 THEN
PO_FHZ := V_PROCNAME || '_002';
PO_MSG := '需备份的业务表:' || PI_BM1 || '的字段比备份表:' || PI_BM2 || '字段少，请检查';
RETURN;
RETURN;
END IF; --判断备份表是否有备份用的两个字段
SELECT COUNT(1)
INTO V_COUNT
FROM ALL_TAB_COLUMNS A
WHERE A.COLUMN_NAME = 'CAE442'
AND A.OWNER = PI_OWNER2
AND A.TABLE_NAME = PI_BM2;
IF V_COUNT <> 2 THEN
PO_FHZ := V_PROCNAME || '_003';
PO_MSG := '备份表中没有CAE442这个字段，请检查' || PI_BM2;
RETURN;
END IF; --生成业务表的字段串
FOR V_TAB_COL1 IN CUR_TAB_COL1 LOOP
IF V_TAB_COL1.COLUMN_ID = 1 THEN
PO_ZDXXC1 := V_TAB_COL1.COLUMN_NAME;
ELSE
PO_ZDXXC1 := PO_ZDXXC1 || ',' || V_TAB_COL1.COLUMN_NAME; END IF;
IF V_TAB_COL1.COLUMN_ID = 1 THEN
PO_ZDXXC2 := V_TAB_COL1.COLUMN_NAME || ' ' ||
V_TAB_COL1.COLUMN_NAME;
ELSE
PO_ZDXXC2 := PO_ZDXXC2 || ',' || V_TAB_COL1.COLUMN_NAME || ' ' ||
V_TAB_COL1.COLUMN_NAME; END IF; END LOOP;
PO_FHZ := '';
EXCEPTION
WHEN OTHERS THEN
PO_FHZ := V_PROCNAME || '_999';
PO_MSG := PKG_FUN.F_ERRMSG(V_PROCNAME, SQLCODE, SQLERRM, V_PARAMS);
RETURN;
END XZZX_HBPD; --参保资料备份
PROCEDURE XZZX_CBZL_BF(PI_ZXXX IN REC_XZZX_INFO,
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2) IS
V_PARAMS VARCHAR2(32767); --传入参数串
V_PROCNAME VARCHAR2(50) := C_PKG_NAME || 'Xzzx_Cbzl_Bf'; --过程名称 V_BFTJ VARCHAR2(100); --备份条件
V_BCXXC VARCHAR2(100); --补充信息串
BEGIN
--初始化返回值
PO_FHZ := '';
--初始化返回信息入参串
V_PARAMS := ',传入参数为:Pi_Zxxx.Bae007=' || PI_ZXXX.BAE007;
--设置备份条件和备份业务流水号
V_BFTJ := 'WHERE AAC001 =' || PI_ZXXX.AAC001 || ' AND AAE140 = ''' ||
PI_ZXXX.AAE140 || ''' AND AAZ159 = ' || PI_ZXXX.AAZ159;
V_BCXXC := 'CAE442=' || PI_ZXXX.BAE007;
--将注销险种的参保资料都备份到SAE53中
XZZX_BFSJ('AC02', 'SAE53', V_BFTJ, V_BCXXC, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
--删除参保资料
DELETE FROM AC02
WHERE AAC001 = PI_ZXXX.AAC001
AND AAE140 = PI_ZXXX.AAE140
AND AAZ159 = PI_ZXXX.AAZ159;
RETURN;
EXCEPTION
WHEN OTHERS THEN
PO_FHZ := V_PROCNAME || '_999';
PO_MSG := PKG_FUN.F_ERRMSG(V_PROCNAME, SQLCODE, SQLERRM, V_PARAMS);
RETURN;
END XZZX_CBZL_BF; --参保历史备份
PROCEDURE XZZX_CBLS_BF(PI_ZXXX IN REC_XZZX_INFO,
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2) IS
V_PARAMS VARCHAR2(32767); --传入参数串
V_PROCNAME VARCHAR2(50) := C_PKG_NAME || 'Xzzx_Cbls_Bf'; --过程名称 V_BFTJ VARCHAR2(100); --备份条件
V_BCXXC VARCHAR2(100); --补充信息串
BEGIN
--初始化返回值
PO_FHZ := '';
--初始化返回信息入参串
V_PARAMS := ',传入参数为:Pi_Zxxx.Bae007=' || PI_ZXXX.BAE007;
--设置备份条件和备份业务流水号
V_BFTJ := 'WHERE AAC001 =' || PI_ZXXX.AAC001 || ' AND AAE140 = ''' ||
PI_ZXXX.AAE140 || ''' AND AAZ159 = ' || PI_ZXXX.AAZ159;
V_BCXXC := 'CAE442=' || PI_ZXXX.BAE007;
--将注销险种的参保历史都备份到SAE54中
XZZX_BFSJ('AC20', 'SAE54', V_BFTJ, V_BCXXC, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
--删除参保历史
DELETE FROM AC20
WHERE AAC001 = PI_ZXXX.AAC001
AND AAE140 = PI_ZXXX.AAE140
AND AAZ159 = PI_ZXXX.AAZ159;
RETURN;
EXCEPTION
WHEN OTHERS THEN
PO_FHZ := V_PROCNAME || '_999';
PO_MSG := PKG_FUN.F_ERRMSG(V_PROCNAME, SQLCODE, SQLERRM, V_PARAMS);
RETURN;
END XZZX_CBLS_BF; --个人养老账户备份
PROCEDURE XZZX_YLZH_BF(PI_ZXXX IN REC_XZZX_INFO,
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2) IS
V_PARAMS VARCHAR2(32767); --传入参数串
V_PROCNAME VARCHAR2(50) := C_PKG_NAME || 'Xzzx_Ylzh_Bf'; --过程名称 V_BFTJ VARCHAR2(100); --备份条件
V_BCXXC VARCHAR2(100); --补充信息串
BEGIN
--初始化返回值
PO_FHZ := '';
--初始化返回信息入参串
V_PARAMS := ',传入参数为:Pi_Zxxx.Bae007=' || PI_ZXXX.BAE007;
--设置备份条件和备份业务流水号
V_BFTJ := 'WHERE AAC001 =' || PI_ZXXX.AAC001 || ' AND AAE140 = ''' ||
PI_ZXXX.AAE140 || '''';
V_BCXXC := 'CAE442=' || PI_ZXXX.BAE007;
--将注销养老险种的个人账户都备份到SAE55中
XZZX_BFSJ('SIC81', 'SAE55', V_BFTJ, V_BCXXC, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
--将注销养老险种的个人年度账户都备份到SAE56中
XZZX_BFSJ('SIC86', 'SAE56', V_BFTJ, V_BCXXC, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
--删除养老险种的个人账户信息
DELETE FROM SIC81
WHERE AAC001 = PI_ZXXX.AAC001
AND AAE140 = PI_ZXXX.AAE140;
DELETE FROM SIC86
WHERE AAC001 = PI_ZXXX.AAC001
AND AAE140 = PI_ZXXX.AAE140;
RETURN;
EXCEPTION
WHEN OTHERS THEN
PO_FHZ := V_PROCNAME || '_999';
PO_MSG := PKG_FUN.F_ERRMSG(V_PROCNAME, SQLCODE, SQLERRM, V_PARAMS);
RETURN;
END XZZX_YLZH_BF; --个人医保账户备份
PROCEDURE XZZX_YILZH_BF(PI_ZXXX IN REC_XZZX_INFO,
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2) IS
V_PARAMS VARCHAR2(32767); --传入参数串
V_PROCNAME VARCHAR2(50) := C_PKG_NAME || 'Xzzx_Yilzh_Bf'; --过程名称 V_BFTJ VARCHAR2(100); --备份条件
V_BCXXC VARCHAR2(100); --补充信息串
BEGIN
--初始化返回值
PO_FHZ := '';
--初始化返回信息入参串
V_PARAMS := ',传入参数为:Pi_Zxxx.Bae007=' || PI_ZXXX.BAE007;
--设置备份条件和备份业务流水号
V_BFTJ := 'WHERE AAC001 =' || PI_ZXXX.AAC001;
V_BCXXC := 'CAE442=' || PI_ZXXX.BAE007;
--将注销医疗险种的个人账户都备份到SAE57中
XZZX_BFSJ('SKC81', 'SAE57', V_BFTJ, V_BCXXC, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
--将注销医疗险种的个人年度账户都备份到SAE58中
XZZX_BFSJ('SKC86', 'SAE58', V_BFTJ, V_BCXXC, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
--删除医疗险种的个人账户信息
DELETE FROM SKC81 WHERE AAC001 = PI_ZXXX.AAC001;
DELETE FROM SKC86 WHERE AAC001 = PI_ZXXX.AAC001;
RETURN;
EXCEPTION
WHEN OTHERS THEN
PO_FHZ := V_PROCNAME || '_999';
PO_MSG := PKG_FUN.F_ERRMSG(V_PROCNAME, SQLCODE, SQLERRM, V_PARAMS);
RETURN;
END XZZX_YILZH_BF; --个人征缴明细注销备份
PROCEDURE XZZX_GRYJ_BF(PI_ZXXX IN REC_XZZX_INFO,
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2) IS
V_PARAMS VARCHAR2(32767); --传入参数串
V_PROCNAME VARCHAR2(50) := C_PKG_NAME || 'Xzzx_Gryj_Bf'; --过程名称 V_BFTJ VARCHAR2(100); --备份条件
V_BCXXC VARCHAR2(100); --补充信息串
BEGIN
--初始化返回值
PO_FHZ := '';
--初始化返回信息入参串
V_PARAMS := ',传入参数为:Pi_Zxxx.Bae007=' || PI_ZXXX.BAE007;
--设置备份条件和备份业务流水号
V_BFTJ := 'WHERE AAC001 =' || PI_ZXXX.AAC001 || ' AND AAE140 = ''' ||
PI_ZXXX.AAE140 || '''';
V_BCXXC := 'CAE442=' || PI_ZXXX.BAE007;
--将注销险种的个人应缴都备份到SAE59中
XZZX_BFSJ('AC43', 'SAE59', V_BFTJ, V_BCXXC, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
--将注销养老险种的个人应缴都备份到SAE59中
XZZX_BFSJ('SIC84', 'SAE59', V_BFTJ, V_BCXXC, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
--将注销医疗险种的个人应缴都备份到SAE59中
XZZX_BFSJ('SKC84', 'SAE59', V_BFTJ, V_BCXXC, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
--将注销失业险种的个人应缴都备份到SAE59中
XZZX_BFSJ('SJC84', 'SAE59', V_BFTJ, V_BCXXC, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
--将注销工伤险种的个人应缴都备份到SAE59中
XZZX_BFSJ('SLC84', 'SAE59', V_BFTJ, V_BCXXC, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
--将注销生育险种的个人应缴都备份到SAE59中
XZZX_BFSJ('SMC84', 'SAE59', V_BFTJ, V_BCXXC, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
--删除医疗险种的个人账户信息
DELETE FROM AC43
WHERE AAC001 = PI_ZXXX.AAC001
AND AAE140 = PI_ZXXX.AAC001;
DELETE FROM SIC84
WHERE AAC001 = PI_ZXXX.AAC001
AND AAE140 = PI_ZXXX.AAC001;
DELETE FROM SKC84
WHERE AAC001 = PI_ZXXX.AAC001
AND AAE140 = PI_ZXXX.AAC001;
DELETE FROM SJC84
WHERE AAC001 = PI_ZXXX.AAC001
AND AAE140 = PI_ZXXX.AAC001;
DELETE FROM SLC84
WHERE AAC001 = PI_ZXXX.AAC001
AND AAE140 = PI_ZXXX.AAC001;
DELETE FROM SMC84
WHERE AAC001 = PI_ZXXX.AAC001
AND AAE140 = PI_ZXXX.AAC001;
RETURN;
EXCEPTION
WHEN OTHERS THEN
PO_FHZ := V_PROCNAME || '_999';
PO_MSG := PKG_FUN.F_ERRMSG(V_PROCNAME, SQLCODE, SQLERRM, V_PARAMS);
RETURN;
END XZZX_GRYJ_BF; --个人转移(只备份转入)信息注销备份
PROCEDURE XZZX_ZYZL_BF(PI_ZXXX IN REC_XZZX_INFO,
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2) IS
V_PARAMS VARCHAR2(32767); --传入参数串
V_PROCNAME VARCHAR2(50) := C_PKG_NAME || 'Xzzx_Zyzl_Bf'; --过程名称 V_BFTJ VARCHAR2(100); --备份条件
V_BCXXC VARCHAR2(100); --补充信息串
BEGIN
--初始化返回值
PO_FHZ := '';
--初始化返回信息入参串
V_PARAMS := ',传入参数为:Pi_Zxxx.Bae007=' || PI_ZXXX.BAE007;
--设置备份条件和备份业务流水号
V_BFTJ := 'WHERE AAC001 =' || PI_ZXXX.AAC001 || ' AND AAE140 = ''' ||
PI_ZXXX.AAE140 ||
''' AND CAE009 = '''' AND SUBSTR(AAE036,1,6) <= ' ||
NVL(PI_ZXXX.AAE002, '');
V_BCXXC := 'CAE442=' || PI_ZXXX.BAE007;
--将注销险种的转入资料都备份到SAE60中
XZZX_BFSJ('SAC12', 'SAE60', V_BFTJ, V_BCXXC, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
--设置备份条件和备份业务流水号
V_BFTJ := 'WHERE AAC001 =' || PI_ZXXX.AAC001 || ' AND AAE140 = ''' ||
PI_ZXXX.AAE140 || ''' AND CAE009 = '''' ';
V_BCXXC := 'CAE442=' || PI_ZXXX.BAE007;
--将注销险种的转入资料都备份到SAE60中
XZZX_BFSJ('SAC13', 'SAE61', V_BFTJ, V_BCXXC, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
--删除转入资料
DELETE FROM SAC12
WHERE AAC001 = PI_ZXXX.AAC001
AND AAE140 = PI_ZXXX.AAE140
AND CAE009 = C_CAE009_ZR
AND SUBSTR(AAE036, 1, 6) <= NVL(PI_ZXXX.AAE002, '');
DELETE FROM SAC13
WHERE AAC001 = PI_ZXXX.AAC001
AND AAE140 = PI_ZXXX.AAE140
AND CAE009 = C_CAE009_ZR;
RETURN;
EXCEPTION
WHEN OTHERS THEN
PO_FHZ := V_PROCNAME || '_999';
PO_MSG := PKG_FUN.F_ERRMSG(V_PROCNAME, SQLCODE, SQLERRM, V_PARAMS);
RETURN;
END XZZX_ZYZL_BF; --验证注销条件
--个人转移(只备份转入)信息注销备份
PROCEDURE XZZX_YZBGTJ(PI_ZXXX IN REC_XZZX_INFO,
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2) IS
V_PARAMS VARCHAR2(32767); --传入参数串
V_PROCNAME VARCHAR2(50) := C_PKG_NAME || 'Xzzx_Yzbgtj'; --过程名称 V_AAC008 VARCHAR2(6);
BEGIN
--初始化返回值
PO_FHZ := '';
--初始化返回信息入参串
V_PARAMS := ',传入参数为:Pi_Zxxx.Bae007=' || PI_ZXXX.BAE007; --获取参保状态
SELECT AAC008
INTO V_AAC008
FROM AC02
WHERE AAC001 = PI_ZXXX.AAC001
AND AAE140 = PI_ZXXX.AAE140
AND AAZ159 = PI_ZXXX.AAZ159;
IF V_AAC008 != C_AAC008_ZZCB THEN
PO_FHZ := V_PROCNAME || '_901';
PO_MSG := PKG_FUN.F_ERRMSG_PRC(V_PROCNAME,
',注销险种的参保状态必须为终止参保',
V_PARAMS);
RETURN;
END IF; RETURN;
EXCEPTION
WHEN OTHERS THEN
PO_FHZ := V_PROCNAME || '_999';
PO_MSG := PKG_FUN.F_ERRMSG(V_PROCNAME, SQLCODE, SQLERRM, V_PARAMS);
RETURN;
END XZZX_YZBGTJ; --获取注销险种信息
PROCEDURE XZZX_HQZXXX(PI_BAE007 IN VARCHAR2,
PO_ZXXX OUT REC_XZZX_INFO,
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2) IS
V_PARAMS VARCHAR2(32767); --传入参数串
V_PROCNAME VARCHAR2(50) := C_PKG_NAME || 'Xzzx_Hqzxxx'; --过程名称 V_ZXXX REC_XZZX_INFO;
BEGIN
--初始化返回值
PO_FHZ := '';
--初始化返回信息入参串
V_PARAMS := ',传入参数为:Pi_Bae007=' || PI_BAE007;
--获取注销信息
SELECT BAE001, BAE007, AAC001, AAB001, AAE140, AAZ159, AAE002, CAE441
INTO V_ZXXX.BAE001,
V_ZXXX.BAE007,
V_ZXXX.AAC001,
V_ZXXX.AAB001,
V_ZXXX.AAE140,
V_ZXXX.AAZ159,
V_ZXXX.AAE002,
V_ZXXX.CAE441
FROM SAE52
WHERE BAE007 = PI_BAE007;
PO_ZXXX := V_ZXXX;
RETURN;
EXCEPTION
WHEN OTHERS THEN
PO_FHZ := V_PROCNAME || '_999';
PO_MSG := PKG_FUN.F_ERRMSG(V_PROCNAME, SQLCODE, SQLERRM, V_PARAMS);
RETURN;
END XZZX_HQZXXX; --注销信息登记
PROCEDURE XZZX_START(PI_BAE001 IN VARCHAR2,
PI_BAE007 IN VARCHAR2,
PI_AAC001 IN NUMBER,
PI_AAB001 IN NUMBER,
PI_AAE140 IN VARCHAR2,
PI_AAZ159 IN NUMBER, --注销险种参保关系ID
PI_AAE002 IN NUMBER,
PI_CAE440 IN VARCHAR2,
PI_AAE011 IN VARCHAR2,
PI_AAE013 IN VARCHAR2,
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2) IS
V_PARAMS VARCHAR2(32767); --传入参数串
V_PROCNAME VARCHAR2(50) := C_PKG_NAME || 'Xzzx_Start'; --过程名称 V_ZXXX REC_XZZX_INFO;
BEGIN
--初始化返回值
PO_FHZ := '';
--初始化返回信息入参串
V_PARAMS := ',传入参数为:Pi_Bae001=' || PI_BAE001 || ',Pi_Bae007:' ||
PI_BAE007 || ',Pi_Aac001:' || PI_AAC001 ||
',Pi_Aab001:' || PI_AAB001 || ',Pi_Aae140:' ||
PI_AAE140 || ',Pi_Aaz159:' || PI_AAZ159 ||
',Pi_Aae002:' || PI_AAE002 || ',Pi_Cae440:' ||
PI_CAE440 || ',Pi_Aae011:' || PI_AAE011 ||
',Pi_Aae013:' || PI_AAE013;
V_ZXXX.BAE001 := PI_BAE001;
V_ZXXX.BAE007 := PI_BAE007;
V_ZXXX.AAC001 := PI_AAC001;
V_ZXXX.AAB001 := PI_AAB001;
V_ZXXX.AAE140 := PI_AAE140;
V_ZXXX.AAZ159 := PI_AAZ159;
V_ZXXX.AAE002 := PI_AAE002;
V_ZXXX.CAE440 := PI_CAE440;
V_ZXXX.CAE441 := C_CAE441_WZX;
--验证变更条件
XZZX_YZBGTJ(V_ZXXX, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
--插入注销业务信息
INSERT INTO SAE52 --公共业务险种注销主表
(BAE001, --系统机构编码
BAE007, --业务流水号
AAC001, --个人编号
AAB001, --单位编号
AAE140, --注销险种
AAZ159, --注销险种参保关系ID
AAE002, --注销截止时间
CAE440, --注销原因
AAE011, --经办人
AAE036, --经办日期
CAE441, --注销业务状态0未注销，1已注销，9已回退
AAE013) --备注
VALUES
(V_ZXXX.BAE001, --系统机构编码
V_ZXXX.BAE007, --业务流水号
V_ZXXX.AAC001, --个人编号
V_ZXXX.AAB001, --单位编号
V_ZXXX.AAE140, --注销险种
V_ZXXX.AAZ159, --注销险种参保关系ID
V_ZXXX.AAE002, --注销截止时间
V_ZXXX.CAE440, --注销原因
PI_AAE011, --经办人
PKG_FUN.F_GET_SYSDATE(14), --经办日期
V_ZXXX.CAE441, --注销业务状态0未注销，1已注销，9已回退
PI_AAE013); --备注
RETURN;
EXCEPTION
WHEN OTHERS THEN
PO_FHZ := V_PROCNAME || '_999';
PO_MSG := PKG_FUN.F_ERRMSG(V_PROCNAME, SQLCODE, SQLERRM, V_PARAMS);
RETURN;
END XZZX_START; --险种注销-回退
PROCEDURE XZZX_HT(PI_BAE007 IN VARCHAR2,
PI_AAE012 IN VARCHAR2,
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2) IS
V_PARAMS VARCHAR2(32767); --传入参数串
V_PROCNAME VARCHAR2(50) := C_PKG_NAME || 'Xzzx_Ht'; --过程名称 V_ZXXX REC_XZZX_INFO;
BEGIN
--初始化返回值
PO_FHZ := '';
--初始化返回信息入参串
V_PARAMS := ',传入参数为:Pi_BAE007=' || PI_BAE007;
--获取注销险种信息
XZZX_HQZXXX(PI_BAE007, V_ZXXX, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
IF V_ZXXX.CAE441 != '' THEN
PO_FHZ := V_PROCNAME || '_901';
PO_MSG := PKG_FUN.F_ERRMSG_PRC(V_PROCNAME,
',险种注销业务状态不正常，不能继续处理',
V_PARAMS);
RETURN;
END IF;
--复核未通过
UPDATE SAE52 --公共业务险种注销主表
SET CAE441 = C_CAE441_YHT, --注销业务状态0未注销，1已注销，9已回退
CAE030 = PI_AAE012, --取消人
CAE031 = PKG_FUN.F_GET_SYSDATE(14) --取消时间
WHERE BAE007 = PI_BAE007;
RETURN;
EXCEPTION
WHEN OTHERS THEN
PO_FHZ := V_PROCNAME || '_999';
PO_MSG := PKG_FUN.F_ERRMSG(V_PROCNAME, SQLCODE, SQLERRM, V_PARAMS);
RETURN;
END XZZX_HT; --险种注销-处理
PROCEDURE XZZX_CL(PI_ZXXX IN REC_XZZX_INFO,
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2) IS
V_PARAMS VARCHAR2(32767); --传入参数串
V_PROCNAME VARCHAR2(50) := C_PKG_NAME || 'Xzzx_Cl'; --过程名称
BEGIN
--初始化返回值
PO_FHZ := '';
--初始化返回信息入参串
V_PARAMS := ',传入参数为:Pi_Zxxx.Bae007=' || PI_ZXXX.BAE007;
--处理参保资料
XZZX_CBZL_BF(PI_ZXXX, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
--处理参保历史资料
XZZX_CBLS_BF(PI_ZXXX, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
--处理养老账户
IF SUBSTR(PI_ZXXX.AAE140, 1, 1) = '' THEN
XZZX_YLZH_BF(PI_ZXXX, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
END IF;
--处理医保账户
IF SUBSTR(PI_ZXXX.AAE140, 1, 1) = '' THEN
XZZX_YILZH_BF(PI_ZXXX, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
END IF;
--处理个人应缴明细
XZZX_GRYJ_BF(PI_ZXXX, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
--处理转移资料
XZZX_ZYZL_BF(PI_ZXXX, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
RETURN;
EXCEPTION
WHEN OTHERS THEN
PO_FHZ := V_PROCNAME || '_999';
PO_MSG := PKG_FUN.F_ERRMSG(V_PROCNAME, SQLCODE, SQLERRM, V_PARAMS);
RETURN;
END XZZX_CL; --险种注销-复核通过
PROCEDURE XZZX_FH(PI_BAE007 IN VARCHAR2,
PI_AAE012 IN VARCHAR2,
PI_CAE441 IN VARCHAR2,
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2) IS
V_PARAMS VARCHAR2(32767); --传入参数串
V_PROCNAME VARCHAR2(50) := C_PKG_NAME || 'Xzzx_Fh'; --过程名称 V_ZXXX REC_XZZX_INFO;
BEGIN
--初始化返回值
PO_FHZ := '';
--初始化返回信息入参串
V_PARAMS := ',传入参数为:Pi_Bae007=' || PI_BAE007 || ',Pi_Aae012:' ||
PI_AAE012 || ',Pi_Cae441:' || PI_CAE441;
--获取注销险种信息
XZZX_HQZXXX(PI_BAE007, V_ZXXX, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
--按照复核结果处理
IF PI_CAE441 = C_CAE441_YZX THEN
--复核通过
--处理（包括注销险种相应资料备份删除）
XZZX_CL(V_ZXXX, PO_FHZ, PO_MSG);
IF PO_FHZ != '' THEN
RETURN;
END IF;
UPDATE SAE52 --公共业务险种注销主表
SET CAE441 = PI_CAE441, --注销业务状态0未注销，1已注销，9已回退
AAE012 = PI_AAE012, --审核人
BHE949 = PKG_FUN.F_GET_SYSDATE(14) --审核时间
WHERE BAE007 = PI_BAE007;
ELSIF PI_CAE441 = C_CAE441_YHT THEN
--复核未通过
UPDATE SAE52 --公共业务险种注销主表
SET CAE441 = C_CAE441_YHT, --注销业务状态0未注销，1已注销，9已回退
CAE030 = PI_AAE012, --取消人
CAE031 = PKG_FUN.F_GET_SYSDATE(14) --取消时间
WHERE BAE007 = PI_BAE007;
ELSE
PO_FHZ := V_PROCNAME || '_901';
PO_MSG := PKG_FUN.F_ERRMSG_PRC(V_PROCNAME,
',险种注销业务状态不正常，不能继续处理',
V_PARAMS);
RETURN;
END IF;
RETURN;
EXCEPTION
WHEN OTHERS THEN
PO_FHZ := V_PROCNAME || '_999';
PO_MSG := PKG_FUN.F_ERRMSG(V_PROCNAME, SQLCODE, SQLERRM, V_PARAMS);
RETURN;
END XZZX_FH;
END WYL_TEST_PKG_GGYW_XZZX;