/*

     add by weiyongle 20160623

     失地农民补足aaz288，针对早期导出的数据(只适用于江安县)

     经测试：江安县 江安县个体劳动者 这个单位一共2194人，成功修复 2166条

   */

   PROCEDURE P_COMPL_AAZ288(PI_DRPH IN VARCHAR2,

                            PO_FHZ  OUT VARCHAR2,

                            PO_MSG  OUT VARCHAR2) IS

     V_CNT_IC51         NUMBER(2);

     V_CNT_IC5152_TEMP_ NUMBER(2);

     V_AAZ288           IC51.AAZ288%TYPE;

     -- 该表 sjql_aac002_tmp 数据 是在 过程 p_compl_aaz288 中插入的数据(根据导入批次号caz005)

     CURSOR C_LINSHI_ IS

       SELECT * FROM SJQL_AAC002_TMP A WHERE A.CAZ005 = PI_DRPH;

     CURSOR C_IC5152_TEMP_ IS

       SELECT * FROM IC5152_TEMP_ A WHERE A.CAZ005 = PI_DRPH;

     CURSOR C_IC51 IS

       SELECT *

         FROM IC51 A

        WHERE A.AAC001 IN

              (SELECT AAC001 FROM SJQL_AAC002_TMP B WHERE B.CAZ005 = PI_DRPH)

          AND A.BAE001 = '';

   BEGIN

     -- 初始化

     PO_FHZ := '';

     PO_MSG := 'pkg_sp3_sdnmsjql.p_compl_aaz288补全aaz288成功：';

     FOR V_CUR IN C_IC5152_TEMP_ LOOP

       SELECT COUNT(1)

         INTO V_CNT_IC51

         FROM IC51 A

        WHERE A.AAC001 = V_CUR.AAC001

          AND A.AAE016 = ''

             -- altered by weiyongle 20160722 临时增加

          AND A.AAE013 LIKE '%导入%'

          and rownum = ''

       --   AND A.AAE013 LIKE '%导入%'

       ;

       SELECT COUNT(1)

         INTO V_CNT_IC5152_TEMP_

         FROM IC5152_TEMP_ A

        WHERE A.AAC001 = V_CUR.AAC001

          and a.caz005 = PI_DRPH;

       -- 如果ic51只有一条 且 导出的excel数据也是一条，那么就认为这两条记录关联,然后对aaz288进行补全

       IF V_CNT_IC51 = V_CNT_IC5152_TEMP_ AND V_CNT_IC51 = 1 THEN

         SELECT AAZ288

           INTO V_AAZ288

           FROM IC51

          WHERE AAC001 = V_CUR.AAC001

            AND AAE016 = ''

               -- add by weiyongle 20160722 临时增加

            AND AAE013 LIKE '%导入%'

            and rownum = ''

         --   AND AAE013 LIKE '%导入%'

         ;

         UPDATE IC5152_TEMP_ A

            SET A.AAZ288 = V_AAZ288

          WHERE AAC001 = V_CUR.AAC001;

         -- 没有修复成功的 查询语句 select * from IC5152_TEMP_ where aaz288 is null;

       END IF;

     END LOOP;

   EXCEPTION

     WHEN NO_DATA_FOUND THEN

       PO_FHZ := 'pkg_sp3_sdnmsjql.p_compl_aaz288_99';

       PO_MSG := '失地农民数据清理导入保存异常：' || SQLERRM;

       RETURN;

   END;