TYPE RefCur IS REF CURSOR;
   UPD_CUR RefCur;

   TYPE t_ROWIDS IS TABLE OF UROWID INDEX BY PLS_INTEGER;
   col_ROWIDS t_ROWIDS;

   s_TableNm  VARCHAR2(200) := Upper(RTrim(p_TableNm));
   s_FSQLText  t_SQLText;
   s_USQLText  t_SQLText;   

   BEGIN
      CASE 
         -- Special Process for ACCOUNT_DIM Table
         WHEN s_TableNm = 'ACCOUNT_DIM'   
           OR s_TableNm = 'DW.ACCOUNT_DIM'            THEN
            s_TableNm  := s_TableNm || ' PARTITION(ACCT_DIM_ACTIV)';
            s_USQLText := 'UPDATE  '            || s_TableNm || ' SET RECORD_STATUS = 1 WHERE RECORD_STATUS IN (8, 9)';
            EXECUTE IMMEDIATE s_USQLText;
            COMMIT;

         -- Special Process for BBT RTL Table
         WHEN s_TableNm = 'ACCOUNT_DIM_RTL_BBT'
           OR s_TableNm = 'DWBBT.ACCOUNT_DIM_RTL_BBT' THEN
            s_TableNm  := s_TableNm || ' PARTITION(ACTDM_BBT_RTL_ACTIV)';
            s_USQLText := 'UPDATE  '            || s_TableNm || ' SET RECORD_STATUS = 1 WHERE RECORD_STATUS IN (8, 9)';
            EXECUTE IMMEDIATE s_USQLText;
            COMMIT;

         -- Special Process for BBT COM Table
         WHEN s_TableNm = 'ACCOUNT_DIM_COM_BBT'
           OR s_TableNm = 'DWBBT.ACCOUNT_DIM_COM_BBT' THEN
            s_TableNm  := s_TableNm || ' PARTITION(ACTDM_BBT_COM_ACTIV)';
            s_USQLText := 'UPDATE  '            || s_TableNm || ' SET RECORD_STATUS = 1 WHERE RECORD_STATUS IN (8, 9)';
            EXECUTE IMMEDIATE s_USQLText;
            COMMIT;

         -- Special Process for BBT CHH Table
         WHEN s_TableNm = 'CARDHOLDER_HIST_DIM'
           OR s_TableNm = 'DWBBT.CARDHOLDER_HIST_DIM' THEN
            s_TableNm := s_TableNm || ' PARTITION(CH_HIST_ACTIV)';
            s_USQLText := 'UPDATE  '            || s_TableNm || ' SET RECORD_STATUS = 1 WHERE RECORD_STATUS IN (8, 9)';
            EXECUTE IMMEDIATE s_USQLText;
            COMMIT;

         ELSE
            s_FSQLText  := 'SELECT ROWID FROM ' || p_TableNm || ' WHERE RECORD_STATUS IN (8, 9)';
            s_USQLText  := 'UPDATE  '           || p_TableNm || ' SET RECORD_STATUS = 1 WHERE ROWID = :r';   
   
            -- READS ALL STATUS 8/9
            OPEN UPD_CUR FOR s_FSQLText;
            i_RowCount := 0;
            LOOP
                FETCH UPD_CUR 
                  BULK COLLECT INTO col_ROWIDS
                  LIMIT 50000;
            
               EXIT WHEN col_ROWIDS.COUNT = 0;
               FORALL indx IN 1 .. col_ROWIDS.COUNT SAVE EXCEPTIONS
                  EXECUTE IMMEDIATE s_USQLText USING col_ROWIDS (indx);
               COMMIT;
               i_RowCount := i_RowCount + col_ROWIDS.COUNT;
               i_RowCount := i_RowCount - SQL%BULK_EXCEPTIONS.COUNT;
            END LOOP;
            COMMIT;
           CLOSE UPD_CUR;
       END CASE;

   EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
          NULL;
      WHEN OTHERS THEN
         IF SQLCODE = -24381 THEN
            FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
            LOOP
               DBMS_OUTPUT.PUT_LINE (
                     SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
                  || ': '
                  || SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);
            END LOOP;
         ELSE
            RAISE_APPLICATION_ERROR(-20001, 'Error in package PKG_ETL_RECSTAT_MERGE Procedure SP_UPD_RECSTAT_9to1 error_id = ' || SQLCODE || '-' || SQLERRM);
         END IF;
   END SP_UPD_RECSTAT_9to1;