====== pears.NetVacancyCancel ======
<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"."NetVacancyCancel"( in "pWebUserID" char(20),in "pvacancyid" char(20),in "pshiftplanid" char(20) ) 
result( "pResult" char(250) ) 
-- 20130207 remove 'first'  from first permission check
begin
  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;
  if "trim"("isnull"("pshiftplanid",'')) = '' then
    update "vacancy" set "status" = 'X' where "status" = ']' and "vacancyid" = "pvacancyid"
  else
    update "tempshift" set "cancelreason" = 'C' where "tempshiftplanid" = "pshiftplanid" and "vacancyid" = "pvacancyid" and "state" = 'C' and "cancelreason" = '~'
  end if;
  if @@rowcount = 0 then
    select '1:~Cancellation failed';
    return
  end if;
  select '0:~Success'
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetVacancyCancel" IS 
{create procedure pears."NetVacancyCancel"( in "pWebUserID" char(20),in "pvacancyid" char(20),in "pshiftplanid" char(20) ) 
result( "pResult" char(250) ) 
-- 20130207 remove 'first'  from first permission check
begin
  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;
  if "trim"("isnull"("pshiftplanid",'')) = '' then
    update "vacancy" set "status" = 'X' where "status" = ']' and "vacancyid" = "pvacancyid"
  else
    update "tempshift" set "cancelreason" = 'C' where "tempshiftplanid" = "pshiftplanid" and "vacancyid" = "pvacancyid" and "state" = 'C' and "cancelreason" = '~'
  end if;
  if @@rowcount = 0 then
    select '1:~Cancellation failed';
    return
  end if;
  select '0:~Success'
end
}
</code>