pears.NetVacancyCancel

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
}