I would have thought that one case owner can own many applications and each application can own many mortgages. Hence I the person managing the mortgage process gets an application from every borrower and each borrower can have more than one loan. There was a time when people would buy a house and to avoid PMI (private mortgage insurance) they would get a loan for 80% of what they pay for the house at the "good" rate and then get a second mortgage for a slightly higher rate.

Anyway here is some code that will do what you want to a single mortgage application. When deleting you don't need to aggregate the data, you don't even need to join the data, except to find out that the data you want to delete can be deleted. So if you want to delete all of the mortgages over a certain date that have been processed you still need three delete statements and each one has to filter out applications with stage > 20 and app_date greater than sysdate-365. That code is also below. Your welcome!

Code:
create or replace purge_mortgages ( p_mort_no in number, p_app_no in number )
as

   function cant_delete_mortgage ( p_mort_no in number, p_app_no in number )
   return boolean
   as
      v_record_count NUMBER := 0;
   begin
      select count(*)
        into v_record_count
        from application app
       inner join case_owner cas
          on app.mort_no  = cas.mort_no
         and app.app_no   = cas.app_no
       where app.mort_no  = p_mort_no
         and app.app_no   = p_app_no
         and cas.stage    > 20
         and app.app_date > SYSDATE-365;

     if v_record_count > 0
     then return true
     else return false
     END IF;
   end cant_delete_mortgage;
begin
   if cant_delete_mortgage ( p_mort_no, p_app_no )
   then
      raise_application_error(-20012, 
        'The mortgage application for mortgage id '||p_mort_no||chr(10)||
        ' and applcation id '||p_app_no||chr(10)||
        ' can not be deleted due to the newness of the application or the stage being too high';
   END IF;

   delete case_owner
    where mort_no = p_mort_no
      and app_no  = p_app_no;

   delete application
    where mort_no = p_mort_no
      and app_no  = p_app_no;

   delete mortgage
    where mort_no = p_mort_no
      and app_no  = p_app_no;

   COMMIT;
END ;
/
Code:
create global temporary table mortgages_to_delete 
       ( mort_no number, app_no number) 
    ON COMMIT THEN DELETE ROWS;

INSERT INTO mortgages_to_delete
      select mort_no, app_no
        from application app
       inner join case_owner cas
          on app.mort_no  = cas.mort_no
         and app.app_no   = cas.app_no
       where app.mort_no  = p_mort_no
         and app.app_no   = p_app_no
         and cas.stage    > 20
         and app.app_date > sysdate-365;

   delete case_owner
    where mort_no = p_mort_no
      and app_no  = p_app_no;
      and ( mort_no, app_no ) not in
          ( select mort_no, app_no
              from mortgages_to_delete );

   delete application
    where mort_no = p_mort_no
      and app_no  = p_app_no
      and ( mort_no, app_no ) not in
          ( select mort_no, app_no
              from mortgages_to_delete );

   delete mortgage
    where mort_no = p_mort_no
      and app_no  = p_app_no
      and ( mort_no, app_no ) not in
          ( select mort_no, app_no
              from mortgages_to_delete );

COMMIT;