With the exception of the extra "end;" I had it pretty close. Thanks Martina
Timothy

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