create or replace PROCEDURE pr_sp_drop_empty_sp_asgnew2 (t_name IN varchar2)
   IS
      TYPE tb_subpart IS TABLE OF user_tab_subpartitions.subpartition_name%TYPE;

      lc_subpart_arr      tb_subpart   :=tb_subpart();
      v_part_tab          VARCHAR2 (50 CHAR)   := NULL;
      v_sql               VARCHAR2 (4000 CHAR) := NULL;
      v_sql1              VARCHAR2 (4000 CHAR) := NULL;
      v_count             NUMBER               := 0;
      v_exec_code         VARCHAR2 (10 CHAR)   := NULL;
      v_table_name        VARCHAR2 (100 CHAR)  := NULL;
      vsp_param1_strval   VARCHAR2 (100 CHAR)  := NULL;
      vsp_param1_num1     NUMBER               := 0;
      v_subpart_cnt       NUMBER               := 0;
   BEGIN
      SELECT param1_string_value, param1_num_value
        INTO vsp_param1_strval, vsp_param1_num1
        FROM edx_rpt_sys_param_x
       WHERE param1_name = 'PURGE_EMPTY_SUBPART';

      IF vsp_param1_strval = 'YES'
      THEN
         -- Loop for each part_table find empty subpartitions
         FOR x IN (SELECT table_name FROM part_table where table_name=t_name)
         LOOP
	    v_part_tab := NULL;
            v_part_tab := x.table_name;

            v_subpart_cnt := 0;

            BEGIN
               SELECT ao.subobject_name
               BULK COLLECT INTO lc_subpart_arr
                 FROM test2_asg ao
                WHERE upper(ao.table_name) = upper(v_part_tab);
                  

               edx_rpt_etl.insert_etl_log
                        (SYSDATE,
                         p_etl_key                 => NULL,
                         p_event_type              => 'INFO',
                         p_process_name            => 'SP_DROP_EMPTY_SUBPART',
                         p_message                 =>    'SP_DROP_EMPTY_SUBPART STARTED For '
                                                      || v_part_tab,
                         p_execution_time          => TO_CHAR (SYSDATE,
                                                               'HH24:MI:SS'
                                                              ),
                         p_etl_execution_code      => NULL
                        );

               -- Loop For all subpartitions for each part_table
               FOR j IN lc_subpart_arr.FIRST .. lc_subpart_arr.LAST
               LOOP
                  v_count := 999;

                  v_sql1 :=
                        'SELECT COUNT(1) FROM '
                     || v_part_tab
                     || ' SUBPARTITION ('
                     || lc_subpart_arr (j)
                     || ')';

                  EXECUTE IMMEDIATE (v_sql1)
                               INTO v_count;
                  --  DBMS_OUTPUT.put_line ('Vcount- ' || v_count);

                  IF v_count = 0
                  THEN
                     v_sql :=
                           'ALTER TABLE '
                        || v_part_tab
                        || ' DROP SUBPARTITION '
                        || lc_subpart_arr (j);

                     BEGIN
                        EXECUTE IMMEDIATE (v_sql);
                        --DBMS_OUTPUT.put_line (v_sql);
                        v_subpart_cnt := v_subpart_cnt +1;

                        edx_rpt_etl.insert_etl_log
                                  (p_load_date               => SYSDATE,
                                   p_etl_key                 => NULL,
                                   p_event_type              => 'INFO',
                                   p_process_name            => 'SP_DROP_EMPTY_SUBPART',
                                   p_message                 => v_sql,
                                   p_table_name              => v_part_tab,
                                   p_rows_updated            => NULL,
                                   p_execution_time          => TO_CHAR
                                                                   (SYSDATE,
                                                                    'HH24:MI:SS'
                                                                   ),
                                   p_etl_execution_code      => substr(lc_subpart_arr(j),10,2)
                                  );
                     EXCEPTION
                        WHEN OTHERS
                        THEN
                           edx_rpt_etl.insert_etl_log
                              (SYSDATE,
                               p_etl_key                 => NULL,
                               p_event_type              => 'ERROR',
                               p_process_name            =>    'SP_DROP_EMPTY_SUBPART FAILED FOR TABLE: '
                                                            || v_part_tab
                                                            || ' '
                                                            || lc_subpart_arr
                                                                           (j),
                               p_message                 =>    'ORA- ERROR :'
                                                            || SQLERRM,
                               p_execution_time          => TO_CHAR
                                                                 (SYSDATE,
                                                                  'HH24:MI:SS'
                                                                 ),
                               p_etl_execution_code      => SUBSTR
                                                               (lc_subpart_arr(j),
                                                                10,
                                                                2
                                                               )
                              );
                     END;
                  END IF;                   -- If subpartition is really empty
               END LOOP;                    -- For each empty subpartition

            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  edx_rpt_etl.insert_etl_log
                          (SYSDATE,
                           p_etl_key                 => NULL,
                           p_event_type              => 'WARNING',
                           p_process_name            => 'SP_DROP_EMPTY_SUBPART FAILED ',
                           p_message                 =>    'ORA- ERROR :'
                                                        || SQLERRM
                                                        || ' NO EMPTY SUBPARTITION EXISTS '
                                                        || v_part_tab,
                           p_execution_time          => TO_CHAR (SYSDATE,
                                                                 'HH24:MI:SS'
                                                                ),
                           p_etl_execution_code      => NULL
                          );
               WHEN OTHERS
               THEN
                  edx_rpt_etl.insert_etl_log
                          (SYSDATE,
                           p_etl_key                 => NULL,
                           p_event_type              => 'WARNING',
                           p_process_name            => 'SP_DROP_EMPTY_SUBPART FAILED ',
                           p_message                 =>    'ORA- ERROR :'
                                                        || SQLERRM
                                                        || ' '
                                                        || v_part_tab,
                           p_execution_time          => TO_CHAR (SYSDATE,
                                                                 'HH24:MI:SS'
                                                                ),
                           p_etl_execution_code      => NULL
                          );
            END;

            edx_rpt_etl.insert_etl_log
                        (SYSDATE,
                         p_etl_key                 => NULL,
                         p_event_type              => 'INFO',
                         p_process_name            => 'SP_DROP_EMPTY_SUBPART',
                         p_message                 =>    'SP_DROP_EMPTY_SUBPART DONE For '
                                                      || v_part_tab || ' SUBPARTITIONS DROPPED ' || v_subpart_cnt,
                         p_execution_time          => TO_CHAR (SYSDATE,
                                                               'HH24:MI:SS'
                                                              ),
                         p_etl_execution_code      => NULL
                        );

         END LOOP;

      END IF;
      -- For each part_table
         edx_rpt_etl.insert_etl_log
                                  (SYSDATE,
                                   p_etl_key                 => NULL,
                                   p_event_type              => 'INFO',
                                   p_process_name            => 'SP_DROP_EMPTY_SUBPART',
                                   p_message                 => 'SP_DROP_EMPTY_SUBPART DONE ',
                                   p_execution_time          => TO_CHAR
                                                                   (SYSDATE,
                                                                    'HH24:MI:SS'
                                                                   ),
                                   p_etl_execution_code      => NULL
                                  );
          COMMIT;


   END pr_sp_drop_empty_sp_asgnew2;