CREATE OR REPLACE PROCEDURE EBILL_BULK_UPDATE_SERVICE(in_cycle  VARCHAR2)
  AS

      v_cnt   NUMBER;    -----Variable used for checking table is partitioned or not partitioned

    CURSOR cur_update                -----Cursor defined for Updating EBILL tables for service_id
    is
    SELECT  table_name
          , cycle_name
    FROM    NNP_EBILL_UPDATE
    WHERE   TABLE_NAME IN
     (
        'EBILL_ACCESS_FEES',
        'EBILL_ACCESS_FEES_REFUND',
        'EBILL_CURRENT_DUE',
        'EBILL_DETAIL_DSL_CALL',
        'EBILL_DISCOUNT_DETAILS',
        'EBILL_FAMILY_FRIENDS',
        'EBILL_JAWAL_NET',
        'EBILL_LOCAL_NETWORK_CALL',
        'EBILL_OTHER_CHARGES',
        'EBILL_ROAMING_CHARGE',
        'EBILL_SERVICES',
        'EBILL_SERVICES_TOTAL',
        'EBILL_SERVICE_DETAILS',
        'EBILL_USAGE_CHARGES',
        'EQUIP_INSTALL_TOTALS',
        'EQUIP_INST_INSTALLMENTS',
        'EQUIP_INST_NEW_AGREEMENT',
        'EQUIP_INST_PAYMENTS'
    )
    AND cycle_name like  in_cycle ;

-------------------------------------------------------------------------------------------------------
        BEGIN

        dbms_application_info.set_module(module_name => 'E-Bill',action_name => 'EBILL_GATHER_SP_STATS Procedure');


        FOR  cur_rec IN cur_update
        LOOP

           v_table_name := cur_rec.table_name;
           v_cycle_name := cur_rec.cycle_name;


            SELECT  COUNT(1)
            INTO    v_cnt
            FROM    all_tab_partitions
            WHERE   table_owner = 'EBILL'
            AND     table_name = v_table_name;

           IF v_cnt = 0 THEN

             EXECUTE IMMEDIATE 'update ebill.' ||v_table_name || ' set service_id = ''01'' || service_id where service_id like ''1%'''  ;
             dbms_output.put_line('Rows updated: ' || To_char(SQL%RowCount));
             DBMS_OUTPUT.put_line('Bulk Updates   : ' || (DBMS_UTILITY.get_time - l_start));

              v_count:= SQL%RowCount;

              UPDATE   NNP_EBILL_UPDATE
              SET      updated_count =  v_count
                     , update_status =  1
              WHERE    table_name = v_table_name
              AND      cycle_name = v_cycle_name
              AND      update_status = 0;

           ELSE

           BEGIN

            SELECT    partition_starts_with
            INTO      v_partition_starts_with
            FROM      EBILL_PARTITION_TAB_DTLS
            WHERE     table_name = v_table_name;

            v_partition_name := v_partition_starts_with||v_cycle_name;
            Dbms_output.put_line ('The table ' || v_table_name  || ' Has  partition '|| v_partition_name );

            EXECUTE IMMEDIATE  'select COUNT(1) FROM ebill.' ||v_table_name  || ' partition("' ||v_partition_name ||'")'
            INTO  v_count ;

            Dbms_output.put_line ('The v_sql is '|| v_sql|| ' The table ' || v_table_name  || ' Has '|| v_count|| ' rows');

            l_start:=DBMS_UTILITY.get_time;


            EXECUTE IMMEDIATE 'update ebill.' ||v_table_name || ' partition("' ||v_partition_name ||'")' || ' set service_id = ''01'' || service_id where service_id like ''1%'''  ;

            dbms_output.put_line('Rows updated: ' || To_char(SQL%RowCount));

            DBMS_OUTPUT.put_line('Bulk Updates   : ' || (DBMS_UTILITY.get_time - l_start));

            v_count:= SQL%RowCount;

               UPDATE    NNP_EBILL_UPDATE
               SET       updated_count =  v_count
                       , update_status =1
               WHERE     table_name = v_table_name
               AND       cycle_name = v_cycle_name
               AND       update_status = 0;

          EXCEPTION
          WHEN  no_data_found THEN

            v_err_cd  :=  SQLCODE;
            v_err_msg :=  substr(SQLERRM,1,500);

            INSERT INTO
            err_log
            VALUES(v_err_cd,v_err_msg,v_table_name,'Error During ebill_partition_tab_dtls table');

            commit;

          END;

          END IF;

        End loop;

        COMMIT;
       END EBILL_BULK_UPDATE_SERVICE; 

FOR cur_rec IN cur_update LOOP
  LOOP
    IF job_table.COUNT = 5
      THEN
        -- check if any job in job_table completed by querying USER_JOBS view
        -- do what ever you need if some job(s) failed
        -- delete finished job(s) from job_table and issue EXIT;
        -- if all 5 jobs are still running issue DBMS_LOCK.SLEEP(n);
      ELSE
        EXIT;
    END IF;
-- change EXECUTE IMMEDIATE to DBMS_JOB.SUBMIT + add job number to nested table + COMMIT;
  END LOOP;