declare
  rowcount         number := 0;
  type rowid_typ is
    table of rowid;
  rowid_rec        rowid_typ;
  var_aud_add_id   varchar2 (40) := 'B-IF-OLTP-PURG';
  cursor cur_c_hdr_parent_tb is
  select rowid
  from c_hdr_parent_tb a1
  where a1.c_tcn_num in (
    select b1.c_tcn_num
    from c_arcvl_cntl_oltp_tb b1
    where c_incld_excld_ind = 'I'
  );

begin
  open cur_c_hdr_parent_tb;
  loop
    fetch cur_c_hdr_parent_tb bulk collect into rowid_rec limit 10000;
    forall i in rowid_rec.first..rowid_rec.last
      delete c_hdr_parent_tb
      where rowid = rowid_rec (i);

    rowcount := rowcount + sql%rowcount;
    commit;
    exit when cur_c_hdr_parent_tb%notfound;

  end loop;

  insert into c_iface_purge_cnt_oltp_tb values (
    'C_HDR_PARENT_TB',
    nvl (rowcount,0),
    var_aud_add_id,
    systimestamp
  );

  commit;
end;
/