pears.EnsurePlacementForShift

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
}