PROCEDURE clean_up
IS
  v_rowcount      NUMBER;
  v_text          VARCHAR2 (1000);
  v_log_table     ecs_misc.description_tab;
  cnt             NUMBER                   := 0;
  sys_date        DATE                     := SYSDATE;
  v_commitcount   NUMBER                   := 0;
  v_delcount      NUMBER                   := 0;

  CURSOR tran_cur (v_interval NUMBER)
  IS
    SELECT *
    FROM   TRANSACTION
    WHERE  completion_time <
                (sys_date - v_interval
                )                        --sys_date>(completion_time+interval)
    AND    completion_time IS NOT NULL;
BEGIN
  v_log_table    := ecs_misc.description_tab ();

/* loop through system parameter table to get interval values to delete records from respective tables*/
  FOR i IN (SELECT *
            FROM   system_parameter
            WHERE  component_name = 'REMOVER')
  LOOP
    IF UPPER (i.NAME) = UPPER ('TransactionCleanupInterval')
    THEN
      FOR tran IN tran_cur (i.VALUE)
      LOOP
        BEGIN
          DELETE FROM TRANSACTION
          WHERE       transaction_id = tran.transaction_id
          AND         sub_transaction_id = tran.sub_transaction_id;

          v_commitcount    := v_commitcount + 1;
          v_delcount       := v_delcount + 1;
        EXCEPTION
          WHEN OTHERS
          THEN
            v_text    := 'no found';
        END;

        IF v_commitcount > 1000
        THEN
          COMMIT;
          v_commitcount    := 0;
        END IF;
      END LOOP;

      COMMIT;
--Delete from Transaction where sys_date>(completion_time+i.Value)
-- and completion_time is not null;
      v_rowcount    := SQL%ROWCOUNT;
      v_rowcount    := v_delcount;
      v_text        :=
           'No of rows deleted from Transaction table on '
        || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI')
        || '-->'
        || v_rowcount;
    ELSIF UPPER (i.NAME) = UPPER ('SellableItemCleanupInterval')
    THEN
      UPDATE sellable_item
      SET is_deleted = 1
      WHERE  (purchase_code, dco_id) IN (SELECT purchase_code, dco_id
                                         FROM   program
                                         WHERE  end_time < sys_date);

      DELETE FROM sellable_item
      WHERE       is_deleted = 1 AND sys_date > (modif_date + i.VALUE);

      v_rowcount    := SQL%ROWCOUNT;
      v_text        :=
           'Number of rows deleted from SELLABLE_ITEM table on '
        || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI')
        || '-->'
        || v_rowcount;
      COMMIT;
    ELSIF UPPER (i.NAME) = UPPER ('PurchaseCleanupInterval')
    THEN
      DELETE FROM purchase
      WHERE       subscription_type IN ('single', 'preview')
      AND         is_active = 0
      AND         sys_date > (subscription_end_date + i.VALUE);

      v_rowcount    := SQL%ROWCOUNT;

      DELETE FROM purchase
      WHERE       subscription_type IN ('open')
      AND         is_active = 0
      AND         sys_date > (modif_date + i.VALUE);

      v_rowcount    := v_rowcount + SQL%ROWCOUNT;
      v_text        :=
           'Number of rows deleted from PURCHASE table on '
        || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI')
        || '-->'
        || v_rowcount;
      COMMIT;
    END IF;

    v_log_table.EXTEND;
    cnt                  := cnt + 1;
    v_log_table (cnt)    := v_text;
  END LOOP;

  IF v_log_table.EXISTS (1)
  THEN
    log_writter (v_log_table);
  END IF;

  COMMIT;
END clean_up;

PROCEDURE log_writter (v_description IN OUT ecs_misc.description_tab)
IS
  fileid         UTL_FILE.file_type;
  line_counter   NUMBER             := 1;
  buffer         VARCHAR2 (2000);
BEGIN
/*
fileID := UTL_FILE.FOPEN ('ECS_CLEAN_LOG', 'ECSclean_'||to_char(sysdate,'DDMMYYHHMI')||'.dat' , 'W');
UTL_FILE.NEW_LINE (fileID,1);

FOR servrec IN (SELECT * FROM bundle)
LOOP
UTL_FILE.PUT_LINE
(fileID,servrec.purchase_code || ',' || servrec.dco_id);
END LOOP;
UTL_FILE.FCLOSE (fileID);
*/
/* writing logs to file on server side */
  fileid    :=
    UTL_FILE.fopen ('BAM_CLEAN',
                    'BAMclean_' || TO_CHAR (SYSDATE, 'DDMMYYHHMI') || '.log',
                    'W'
                   );

  IF v_description.EXISTS (1)
  THEN
    FOR i IN v_description.FIRST .. v_description.LAST
    LOOP
--BUFFER :=V_DESCRIPTION(i).result_des;
      buffer          := v_description (i);
      line_counter    := i;
      UTL_FILE.put_line (fileid, buffer);
    END LOOP;

    UTL_FILE.fclose (fileid);
  END IF;
END log_writter;