CREATE OR REPLACE PROCEDURE "Delete_records_rank_greater_than_one" (
   myntilevalue IN NUMBER)
AS
   CURSOR cur
   IS
      SELECT Unique_id
        FROM (SELECT Sequence_ID,
                     NTILE (8) OVER (ORDER BY Customer_ID) myntile
                FROM (SELECT Customer_ID,
                             Unique_id,
                             Department_ID,
                             drank
                        FROM (SELECT tbl.Customer_ID Customer_ID,
                                     tbl.Unique_id Unique_id,
                                     tbl.Department_ID Department_ID,
                                     DENSE_RANK ()
                                     OVER (
                                        PARTITION BY ih.Customer_ID,
                                                     ih.Department_ID
                                        ORDER BY ih.Joindate DESC)
                                        AS drank
                                FROM Emp_Table tbl)
                       WHERE     drank > 1
                             AND SUBSTR (Department_ID, 1, 7) !=
                                    UPPER ('CSE001_')))
       WHERE myntile = myntilevalue;
BEGIN
   OPEN cur;

   FETCH cur
   BULK COLLECT INTO i;                                    --------limit 10000

   FORALL c1_rec IN i.FIRST .. i.LAST
      DELETE FROM oly_peg_data_owner.pr_data_ih_fact prih
            WHERE prih.pxfactid = i (c1_rec);


   DBMS_OUTPUT.PUT_LINE (TO_CHAR (SQL%ROWCOUNT) || ' rows deleted');
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error (
         -20001,
            'Error_encountered in procedure Delete_records_rank_greater_than_one:: '
         || l_err_msg
         || '>>>>'
         || DBMS_UTILITY.format_error_backtrace,
         TRUE);
END;