====== pears.NetGetPlacementid ======
<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"."NetGetPlacementid"( in "vacid" char(20),in "persid" char(20),in "pweekstartdate" date ) 
returns integer
not deterministic
begin
  declare "placid" char(20);
  set "placid" = (select first "placement"."placementid" from "placement" key join "employment" where "placement"."vacancyid" = "vacid" and "employment"."personid"
       = "persid" and "placement"."temp" <> 0 order by "isnull"("employment"."leavedate",current date+3650) desc);
  if "placid" is null then
    set "placid" = "uniquekey"('pl');
    insert into "employment"( "employmentid","companyid","personid","position","leavedate","temp","concurrent" ) 
      select "placid","employment"."companyid","persid","vacancy"."position","pweekstartdate"+6,1,1 from "vacancy" key join "employment" where "vacancy"."vacancy" = "vacid";
    insert into "placement"( "placementid","employmentid","vacancyid","departmentid","staffid","contractref","theirref","placedate","temp","daysperweek" ) 
      select "placid","placid","vacid","vacancy"."departmentid","userstaffid","vacancy"."contractref","vacancy"."theirref",current date,1,5 from "vacancy" where "vacancy"."vacancy" = "vacid";
    update "progress" join "status" on "progress"."status" = "status"."status" and "status"."type" = 'R' set "progress"."status" = 'X',"progress"."actiondate" = null,
      "progress"."placementid" = "placid" where "progress"."vacancyid" = "vacid" and "progress"."personid" = "persid"
      and "isnull"("status"."final",0) = 0 and "progress"."placementid" is null
  else
    update "placement" key join "employment" set "employment"."leavedate" = "pweekstartdate"+6
      where "placement"."placementid" = "placid" and "employment"."leavedate" < "pweekstartdate"+6 // NB - does not change if null
  end if;
  return "placid"
end
</code>