pears.DBCleanupOrphanRecentObjects [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.DBCleanupOrphanRecentObjects ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create function "pears"."DBCleanupOrphanRecentObjects"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "countordo" char(1) ) 
returns integer
begin
  declare @RC integer;
  set @RC = 0;
  if "countordo" = 'C' then
    select "count"() into @RC from "RecentObjects"
      where((not exists(select * from "staff" where "staffid" = "RecentObjects"."staffid"))
      or(("objecttype" = 'P') and not exists(select * from "person" where "personid" = "RecentObjects"."objectid"))
      or(("objecttype" = 'C') and not exists(select * from "company" where "companyid" = "RecentObjects"."objectid"))
      or(("objecttype" = 'V') and not exists(select * from "vacancy" where "vacancyid" = "RecentObjects"."objectid"))
      or(("objecttype" = 'L') and not exists(select * from "placement" where "placementid" = "RecentObjects"."objectid"))
      or(("objecttype" = 'N') and not exists(select * from "contactevent" where "contacteventid" = "RecentObjects"."objectid"))
      or(("objecttype" = 'R') and not exists(select * from "progress" where "progressid" = "RecentObjects"."objectid"))
      or(("objecttype" = 'E') and not exists(select * from "employment" where "employmentid" = "RecentObjects"."objectid")));
    return @RC
  end if;
  delete from "RecentObjects"
    where not exists(select * from "staff" where "staffid" = "RecentObjects"."staffid");
  set @RC = @RC+(select @@rowcount);
  delete from "RecentObjects" where("objecttype" = 'P')
    and not exists(select * from "person" where "personid" = "RecentObjects"."objectid");
  set @RC = @RC+(select @@rowcount);
  delete from "RecentObjects" where("objecttype" = 'C')
    and not exists(select * from "company" where "companyid" = "RecentObjects"."objectid");
  set @RC = @RC+(select @@rowcount);
  delete from "RecentObjects" where("objecttype" = 'V')
    and not exists(select * from "vacancy" where "vacancyid" = "RecentObjects"."objectid");
  set @RC = @RC+(select @@rowcount);
  delete from "RecentObjects" where("objecttype" = 'L')
    and not exists(select * from "placement" where "placementid" = "RecentObjects"."objectid");
  set @RC = @RC+(select @@rowcount);
  delete from "RecentObjects" where("objecttype" = 'N')
    and not exists(select * from "contactevent" where "contacteventid" = "RecentObjects"."objectid");
  set @RC = @RC+(select @@rowcount);
  delete from "RecentObjects" where("objecttype" = 'R')
    and not exists(select * from "progress" where "progressid" = "RecentObjects"."objectid");
  set @RC = @RC+(select @@rowcount);
  delete from "RecentObjects" where("objecttype" = 'E')
    and not exists(select * from "employment" where "employmentid" = "RecentObjects"."objectid");
  set @RC = @RC+(select @@rowcount);
  call "AuditLog"('CLEANUP',null,"string"(@RC,' Unattached Recent Objects deleted '),null,null);
  return @RC
/* ignore type T */
end
</code>