xzzx

创建包：

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;