Hi all,

It has been years since doing anything in oracl PL/ SQL. I have a stored procedure that updates a record or records in a table. I need to check if there are any related record in either of two other tables and if so, call a stored procedure for each record found. I have added some logic which is commented out but not sure how to do it correctly. Any help to point me in the right direction would be appreciated.

Stored procedure that updates the record (s)

procedure modify(aList in PS_SHAPE) is
    lItem SS_SHAPE;
  begin
    for i in 1..aList.COUNT loop
      if aList(i).PKEY is null then
        lItem := aList(i);
        select QP_SHAPE.NEXTVAL into lItem.PKEY from dual;
        insert into VP_SHAPE t values lItem;
      else
        update VP_SHAPE t set  value(t) = aList(i)
          where t.PKEY = aList(i).PKEY;
      end if;
      
        -- find site where UniqueID of shape PKEY is found in SHAPEID_OPERATIONAL of Site
--        for site in (select PKEY from VP_SITE where SHAPEID_OPERATIONAL = aList(i).UNIQUEID)
--            loop
--                UM_SHAPE.forceMasterDataUpdate(site.UNIQUEID);
--            end loop;
--        end;
        
        -- find FK_SITE where UniqueID of shape is found in SHAPEID_OPEATIONAL of landfill
--        for landfill in (select FK_SITE from VP_LANDFILL where SHAPEID_OPERATIONAL = aList(i).UNIQUEID)
--            loop
--                UM_SHAPE.forceMasterDataUpdate(landfill.FK_SITE);
--            end loop;
--        end;
    
    end loop;
    
  end modify;