>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;

        
      -- 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 loop;
    
  end modify;