REM Copyright 2001, Reed-Matthews, Inc.
-- RMINC_DLFCR.sql Delete records from RMINC_fnd_concurrent_requests
-- 1 = Number of days to save
-- 2 = Number of records to save
-- 3 = Concurrent Programs From
-- 4 = Concurrent Programs To

-- alter session set SQL_TRACE true;

set feedback 1
set heading on
set termout on
set serveroutput on
-- set timing on

declare
   cursor c1 is
      select r.concurrent_program_id,
             r.program_application_id,
             count(*) total_cnt,
             sum(decode(sign(r.actual_completion_date-(sysdate-&&1)),-1,0,1)) cur_cnt
      from RMINC_fnd_concurrent_requests r,fnd_concurrent_programs_tl p,fnd_application a
      where p.concurrent_program_id = r.concurrent_program_id
      and p.application_id = r.program_application_id
      and a.application_id = p.application_id
      and p.user_concurrent_program_name between '&&3' and '&&4'
      group by r.concurrent_program_id,
               r.program_application_id
      ;

   cnt number := 0;
begin

for req in c1 loop
   if req.cur_cnt >= &&2 then
      FOR dloop_cnt in 1..5000 LOOP
         delete from RMINC_fnd_concurrent_requests
         where concurrent_program_id = req.concurrent_program_id
         and program_application_id = req.program_application_id
         and actual_completion_date <= sysdate-&&1
         and rownum < 5001  -- Delete 5000 at a time
         ;
       cnt := cnt + SQL%ROWCOUNT;
       IF SQL%ROWCOUNT = 0 THEN
          exit;  -- Exit this loop
       END IF;

       commit;
      END LOOP;

   elsif req.total_cnt < &&2 then
      null;
   else
      declare
         cursor c2 is
            select request_id
            from RMINC_fnd_concurrent_requests
            where concurrent_program_id = req.concurrent_program_id
            and program_application_id = req.program_application_id
            order by actual_completion_date
            ;
         i number;
      begin
      i := req.total_cnt - &&2;
      for req2 in c2 loop
         i := i - 1; 
         delete from RMINC_fnd_concurrent_requests
         where request_id = req2.request_id
         and concurrent_program_id+0 = req.concurrent_program_id
         and program_application_id+0 = req.program_application_id
         ;
         cnt := cnt + SQL%ROWCOUNT;
         if i = 0 then 
            exit;  -- Exit loop;
         end if;
      end loop;
      end;
   end if;
   commit;
end loop;
commit;
dbms_output.put_line (cnt||' records deleted.');
end;
/