====== pears.NetVacancyConfirm ======
<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"."NetVacancyConfirm"( in "pWebUserID" char(20),in "pvacancyid" char(20),in "pshiftplanid" char(20) ) 
result( "pResult" char(250) ) 
begin
  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",'AUTHORISEJOBS') = 0 then
    select '99:~Permission denied';
    return
  end if;
  set "pshiftplanid" = "nullif"("trim"("pshiftplanid"),'');
  if "pshiftplanid" is null then
    update "vacancy" set "status" = 'C' where "status" = ']' and "vacancyid" = "pvacancyid";
    update "vacancy" key join "employment" join "params" on "params"."secondarycascade" = 1
      set "vacancy"."CascadeDateTime" = "GetCascadeTime"("employment"."CompanyID","vacancy"."VacancyID",current timestamp,"vacancy"."startdate",current timestamp,0)
      where "vacancy"."vacancyid" = "pvacancyid"
  else
    delete from "tempshift" where "tempshiftplanid" = "pshiftplanid" and "vacancyid" = "pvacancyid" and "state" = 'C' and "cancelreason" = '~';
    update "TempShiftPlan" as "p" key join "vacancy" key join "employment" join "params" on "params"."secondarycascade" = 1
      set "p"."CascadeDateTime" = "GetCascadeTime"("employment"."CompanyID","vacancy"."VacancyID",current timestamp,"p"."ShiftDate"+"p"."TimeFrom",current timestamp,0)
      where "p"."TempShiftPlanID" = "pshiftplanid"
  end if;
  if @@rowcount = 0 then
    select '1:~Confirmation failed';
    return
  end if;
  if "pshiftplanid" is not null then
    set "spopup" = 'New shift requirement confirmed '+"dateformat"((select first "shiftdate" from "tempshiftplan" where "tempshiftplanid" = "pshiftplanid"),'Ddd dd/mm/yyyy')
  else
    set "spopup" = 'New vacancy requirement confirmed'
  end if;
  call "IQXNetPopup"("pWebUserID",null,null,"pvacancyid",null,null,'ADDVACANCY',"spopup",null);
  select '0:~Success'
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetVacancyConfirm" IS 
{create procedure pears."NetVacancyConfirm"( in "pWebUserID" char(20),in "pvacancyid" char(20),in "pshiftplanid" char(20) ) 
result( "pResult" char(250) ) 
begin
  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",'AUTHORISEJOBS') = 0 then
    select '99:~Permission denied';
    return
  end if;
  set "pshiftplanid" = "nullif"("trim"("pshiftplanid"),'');
  if "pshiftplanid" is null then
    update "vacancy" set "status" = 'C' where "status" = ']' and "vacancyid" = "pvacancyid";
    update "vacancy" key join "employment" join "params" on "params"."secondarycascade" = 1
      set "vacancy"."CascadeDateTime" = "GetCascadeTime"("employment"."CompanyID","vacancy"."VacancyID",current timestamp,"vacancy"."startdate",current timestamp,0)
      where "vacancy"."vacancyid" = "pvacancyid"
  else
    delete from "tempshift" where "tempshiftplanid" = "pshiftplanid" and "vacancyid" = "pvacancyid" and "state" = 'C' and "cancelreason" = '~';
    update "TempShiftPlan" as "p" key join "vacancy" key join "employment" join "params" on "params"."secondarycascade" = 1
      set "p"."CascadeDateTime" = "GetCascadeTime"("employment"."CompanyID","vacancy"."VacancyID",current timestamp,"p"."ShiftDate"+"p"."TimeFrom",current timestamp,0)
      where "p"."TempShiftPlanID" = "pshiftplanid"
  end if;
  if @@rowcount = 0 then
    select '1:~Confirmation failed';
    return
  end if;
  if "pshiftplanid" is not null then
    set "spopup" = 'New shift requirement confirmed '+"dateformat"((select first "shiftdate" from "tempshiftplan" where "tempshiftplanid" = "pshiftplanid"),'Ddd dd/mm/yyyy')
  else
    set "spopup" = 'New vacancy requirement confirmed'
  end if;
  call "IQXNetPopup"("pWebUserID",null,null,"pvacancyid",null,null,'ADDVACANCY',"spopup",null);
  select '0:~Success'
end
}
</code>