====== pears.EnsurePlacementForShift ======
<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 procedure "pears"."EnsurePlacementForShift"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "shiftid" char(20),in "vacid" char(20),in "persid" char(20),in "shiftdate" date ) 
begin
  declare "placid" char(20);
  declare "compid" char(20);
  declare "pos" char(50);
  declare "clientdept" char(30);
  declare "deptid" char(20);
  declare "contref" char(20);
  declare "tref" char(50);
  declare "curr" char(3);
  if not exists(select "placement"."placementid" from "placement" key join "employment" where "placement"."vacancyid" = "vacid" and "employment"."personid" = "persid") then
    select "vacancy"."contractref","vacancy"."theirref","vacancy"."departmentid","vacancy"."position","vacancy"."clientdepartment","vacancy"."currency","employment"."companyid"
      into "contref","tref","deptid","pos","clientdept","curr","compid" from "vacancy" key join "employment" where "vacancy"."vacancyid" = "vacid";
    set "placid" = "uniquekey"('X');
    insert into "employment"( "employmentid","companyid","personid","position","department","startdate","leavedate","temp","concurrent" ) values
      ( "placid","compid","persid","pos","clientdept","shiftdate","shiftdate",1,1 ) ;
    insert into "placement"( "placementid","employmentid","vacancyid","departmentid","staffid","contractref","theirref","placedate","temp",
      "daysperweek","currency" ) values( "placid","placid","vacid","deptid","userstaffid","contref","tref",current date,1,5,"curr" ) ;
    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
  end if
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."EnsurePlacementForShift" IS 
{create procedure EnsurePlacementForShift 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

(in shiftid char(20),in vacid char(20),in persid char(20),in shiftdate date)
begin
  declare placid char(20);
  declare compid char(20);
  declare pos char(50);
  declare clientdept char(30);
  declare deptid char(20);
  declare contref char(20);
  declare tref char(50);
  declare curr char(3);
  if not exists (select placement.placementid from placement key join employment where placement.vacancyid = vacid and employment.personid = persid) then 
    select vacancy.contractref,vacancy.theirref,vacancy.departmentid,vacancy.position,vacancy.clientdepartment, vacancy.currency, employment.companyid
       into contref, tref, deptid, pos, clientdept, curr, compid from vacancy key join employment where vacancy.vacancyid = vacid; 
    set placid = uniquekey('X');
    insert into employment (employmentid,companyid,personid,position,department,startdate,leavedate,temp,concurrent) values 
       (placid,compid,persid,pos,clientdept,shiftdate,shiftdate,1,1);
    insert into placement (placementid,employmentid,vacancyid,departmentid,staffid,contractref,theirref,placedate,temp,
       daysperweek,currency) values (placid,placid,vacid,deptid,userstaffid,contref,tref,current date,1,5,curr);
    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
  end if
end
}
</code>