====== pears.NetVacancyFill ======
<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"."NetVacancyFill"( in "pWebUserID" char(20),in "pvacancyid" char(20),in "pshiftplanid" char(20),in "ppersonid" char(20) ) 
result( "pResult" char(250) ) 
begin
  declare "sid" char(20);
  declare "swebusername" char(50);
  declare "availability" char(20);
  declare "spopup" char(100);
  declare local temporary table "IDs"(
    "ID" char(20) null,
    ) not transactional;
  insert into "IDs"( "ID" ) 
    select distinct "e"."companyid" from "employment" as "e" key join "iqxnetuserlink" as "i" where "i"."iqxnetuserid" = "pWebUserID";
  if not "pvacancyid" = any(select "v"."vacancyid" from "vacancy" as "v" key join "employment" as "e" join "IDs" on "e"."CompanyID" = "IDs"."ID") then
    select '99:~Permission denied';
    return
  end if;
  if "IQXNetHasPermission"("pWebUserID",'FILLJOBS') = 0 then
    select '99:~Permission denied';
    return
  end if;
  set "sid" = "uniquekey"("ppersonid");
  set "swebusername" = (select "name" from "iqxnetuser" where "iqxnetuserid" = "pwebuserid");
  if "trim"("isnull"("pshiftplanid",'')) = '' then
    insert into "employment"( "temp","employmentid","personid","companyid","startdate","leavedate","position","department" ) 
      select "v"."temp","sid","ppersonid","e"."companyid","v"."startdate","v"."finishdate","v"."position","v"."clientdepartment"
        from "vacancy" as "v" key join "employment" as "e" where "v"."vacancyid" = "pvacancyid";
    insert into "placement"( "placementid","placedate","employmentid","vacancyid","departmentid","staffid","temp","daysperweek","salary",
      "TheirRef","ContractRef","Currency","WorkMonday","WorkTuesday","WorkWednesday","WorkThursday","WorkFriday","WorkSaturday","WorkSunday",
      "WorkNormalHours","WorkStartTime" ) 
      select "sid",current date,"sid","pvacancyid","v"."departmentid","userstaffid","v"."temp",5,"v"."salary","v"."theirref","v"."contractref","v"."currency",
        "v"."WorkMonday","v"."WorkTuesday","v"."WorkWednesday","v"."WorkThursday","v"."WorkFriday","v"."WorkSaturday","v"."WorkSunday",
        "v"."WorkNormalHours","v"."WorkStartTime"
        from "vacancy" as "v" where "v"."vacancyid" = "pvacancyid";
    call "IQXNetPopup"("pWebUserID",null,null,"pvacancyid","sid",null,'CLIENTBOOKING','Client made placement',null);
    call "IQXNetRequestAction"("pWebUserID",'ClientFill','L',"sid")
  else
    set "availability" = "isnull"((select "personavailableforplan"("ppersonid","p"."ShiftDate","p"."TimeFrom","p"."TimeTo","p"."RecoveryHours",0,0,null,null,null,null,null)
        from "tempshiftplan" as "p" where "p"."tempshiftplanid" = "pshiftplanid"),'');
    if not("trim"("availability") = '' or "availability" like '[AM]%') then
      select '1:~Candidate not available';
      return
    end if;
    insert into "tempshift"( "TempShiftID","VacancyID","PersonID","ShiftDate","TimeFrom","TimeTo","State","TempShiftPlanID",
      "BreakMinutes","EssentialSkillGradeID","ReferenceCode","ClientConfirmed","TempConfirmed","StaffID",
      "ConfirmedWith","TempShiftTypeID","AnalysisCode","RecoveryHours" ) 
      select "sid","pVacancyID","pPersonID","p"."ShiftDate","p"."TimeFrom","p"."TimeTo",'P',"pShiftPlanID",
        "p"."BreakMinutes","p"."EssentialSkillGradeID","p"."ReferenceCode",1,0,"userStaffID",
        "swebusername","p"."TempShiftTypeID","p"."AnalysisCode","p"."RecoveryHours"
        from "tempshiftplan" as "p" where "p"."tempshiftplanid" = "pshiftplanid";
    set "spopup" = (select "dateformat"("p"."shiftdate",'Ddd dd/mm/yyyy')+' '+"dateformat"("p"."timefrom",'hh:nn')+' with '+"person"."name" from "tempshiftplan" as "p","person"
        where "p"."tempshiftplanid" = "pshiftplanid" and "person"."personid" = "ppersonid");
    call "IQXNetPopup"("pWebUserID",null,null,"pvacancyid",null,null,'CLIENTBOOKING','Client filled shift',"spopup");
    call "IQXNetRequestAction"("pWebUserID",'ClientFill','SHIFT',"sid")
  end if;
  select '0:~Success'
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetVacancyFill" IS 
{create procedure pears."NetVacancyFill"( in "pWebUserID" char(20),in "pvacancyid" char(20),in "pshiftplanid" char(20),in "ppersonid" char(20) ) 
result( "pResult" char(250) ) 
begin
  declare "sid" char(20);
  declare "swebusername" char(50);
  declare "availability" char(20);
  declare "spopup" char(100);
  declare local temporary table "IDs"(
    "ID" char(20) null,
    ) not transactional;
  insert into "IDs"( "ID" ) 
    select distinct "e"."companyid" from "employment" as "e" key join "iqxnetuserlink" as "i" where "i"."iqxnetuserid" = "pWebUserID";
  if not "pvacancyid" = any(select "v"."vacancyid" from "vacancy" as "v" key join "employment" as "e" join "IDs" on "e"."CompanyID" = "IDs"."ID") then
    select '99:~Permission denied';
    return
  end if;
  if "IQXNetHasPermission"("pWebUserID",'FILLJOBS') = 0 then
    select '99:~Permission denied';
    return
  end if;
  set "sid" = "uniquekey"("ppersonid");
  set "swebusername" = (select "name" from "iqxnetuser" where "iqxnetuserid" = "pwebuserid");
  if "trim"("isnull"("pshiftplanid",'')) = '' then
    insert into "employment"( "temp","employmentid","personid","companyid","startdate","leavedate","position","department" ) 
      select "v"."temp","sid","ppersonid","e"."companyid","v"."startdate","v"."finishdate","v"."position","v"."clientdepartment"
        from "vacancy" as "v" key join "employment" as "e" where "v"."vacancyid" = "pvacancyid";
    insert into "placement"( "placementid","placedate","employmentid","vacancyid","departmentid","staffid","temp","daysperweek","salary",
      "TheirRef","ContractRef","Currency","WorkMonday","WorkTuesday","WorkWednesday","WorkThursday","WorkFriday","WorkSaturday","WorkSunday",
      "WorkNormalHours","WorkStartTime" ) 
      select "sid",current date,"sid","pvacancyid","v"."departmentid","userstaffid","v"."temp",5,"v"."salary","v"."theirref","v"."contractref","v"."currency",
        "v"."WorkMonday","v"."WorkTuesday","v"."WorkWednesday","v"."WorkThursday","v"."WorkFriday","v"."WorkSaturday","v"."WorkSunday",
        "v"."WorkNormalHours","v"."WorkStartTime"
        from "vacancy" as "v" where "v"."vacancyid" = "pvacancyid";
    call "IQXNetPopup"("pWebUserID",null,null,"pvacancyid","sid",null,'CLIENTBOOKING','Client made placement',null);
    call "IQXNetRequestAction"("pWebUserID",'ClientFill','L',"sid")
  else
    set "availability" = "isnull"((select "personavailableforplan"("ppersonid","p"."ShiftDate","p"."TimeFrom","p"."TimeTo","p"."RecoveryHours",0,0,null,null,null,null,null)
        from "tempshiftplan" as "p" where "p"."tempshiftplanid" = "pshiftplanid"),'');
    if not("trim"("availability") = '' or "availability" like '[AM]%') then
      select '1:~Candidate not available';
      return
    end if;
    insert into "tempshift"( "TempShiftID","VacancyID","PersonID","ShiftDate","TimeFrom","TimeTo","State","TempShiftPlanID",
      "BreakMinutes","EssentialSkillGradeID","ReferenceCode","ClientConfirmed","TempConfirmed","StaffID",
      "ConfirmedWith","TempShiftTypeID","AnalysisCode","RecoveryHours" ) 
      select "sid","pVacancyID","pPersonID","p"."ShiftDate","p"."TimeFrom","p"."TimeTo",'P',"pShiftPlanID",
        "p"."BreakMinutes","p"."EssentialSkillGradeID","p"."ReferenceCode",1,0,"userStaffID",
        "swebusername","p"."TempShiftTypeID","p"."AnalysisCode","p"."RecoveryHours"
        from "tempshiftplan" as "p" where "p"."tempshiftplanid" = "pshiftplanid";
    set "spopup" = (select "dateformat"("p"."shiftdate",'Ddd dd/mm/yyyy')+' '+"dateformat"("p"."timefrom",'hh:nn')+' with '+"person"."name" from "tempshiftplan" as "p","person"
        where "p"."tempshiftplanid" = "pshiftplanid" and "person"."personid" = "ppersonid");
    call "IQXNetPopup"("pWebUserID",null,null,"pvacancyid",null,null,'CLIENTBOOKING','Client filled shift',"spopup");
    call "IQXNetRequestAction"("pWebUserID",'ClientFill','SHIFT',"sid")
  end if;
  select '0:~Success'
end
}
</code>