pears.NetAgencyCandidateSubmit

CREATE PROCEDURE "pears"."NetAgencyCandidateSubmit"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20),IN "pVacancyID" CHAR(20) DEFAULT NULL,IN "pShiftPlanID" CHAR(20) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
-- 20120814 modified to check if candidate has already been selected for that vacancy
BEGIN
  DECLARE @SecAgID CHAR(20);
  DECLARE @sname CHAR(100);
  IF "IQXNetHasPermission"("pWebUserID",'SUBMITCANDIDATES') = 0 THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  SET @SecAgID = (SELECT FIRST "employment"."companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid");
  IF "trim"("isnull"("pShiftPlanID",'')) <> '' THEN
    IF NOT "pshiftplanid" = any(SELECT "tempshiftplanid" FROM "cascadedshift" WHERE "secondaryagencyid" = @secagid) THEN
      SELECT '99:~Permission denied';
      RETURN
    END IF;
    INSERT INTO "tempshiftprogress"( "tempshiftprogressid","tempshiftplanid","personid","staffid","status" ) VALUES( "uniquekey"("ppersonid"+"pshiftplanid"),"pshiftplanid","ppersonid","userstaffid",'A' ) 
  ELSE
    IF NOT "pvacancyid" = any(SELECT "vacancyid" FROM "cascadedvacancy" WHERE "secondaryagencyid" = @secagid) THEN
      SELECT '99:~Permission denied';
      RETURN
    END IF;
    SET @sname = (SELECT FIRST "name" FROM "person" WHERE "personid" = "ppersonid");
    IF(SELECT(IF "count"() = 0 THEN 'FALSE'
        ELSE 'TRUE'
        endif) AS "IsPresent" FROM "progress" AS "pr" LEFT OUTER JOIN "status" AS "s" ON "s"."status" = "pr"."status" WHERE "pr"."personid" = "pPersonID" AND "pr"."vacancyid" = "pVacancyID" AND "s"."type" = 'R' AND "s"."final" = 0) = 'FALSE' THEN
      INSERT INTO "progress"( "progressid","vacancyid","personid","staffid","status","actiondate" ) VALUES( "uniquekey"("ppersonid"+"pvacancyid"),"pvacancyid","ppersonid","userstaffid",'A',CURRENT DATE ) 
    ELSE
      SELECT '99:~'+@sname+' has already been submitted';
      RETURN
    END IF END IF;
  CALL "IQXNetPopup"("pWebUserID","pPersonID",NULL,"pvacancyid",NULL,NULL,'SUBMITCANDIDATE','Secondary Agency candidate submitted',@sname);
  SELECT '0:~Success'
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyCandidateSubmit" IS 
{CREATE PROCEDURE pears."NetAgencyCandidateSubmit"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20),IN "pVacancyID" CHAR(20) DEFAULT NULL,IN "pShiftPlanID" CHAR(20) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
-- 20120814 modified to check if candidate has already been selected for that vacancy
BEGIN
  DECLARE @SecAgID CHAR(20);
  DECLARE @sname CHAR(100);
  IF "IQXNetHasPermission"("pWebUserID",'SUBMITCANDIDATES') = 0 THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  SET @SecAgID = (SELECT FIRST "employment"."companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid");
  IF "trim"("isnull"("pShiftPlanID",'')) <> '' THEN
    IF NOT "pshiftplanid" = any(SELECT "tempshiftplanid" FROM "cascadedshift" WHERE "secondaryagencyid" = @secagid) THEN
      SELECT '99:~Permission denied';
      RETURN
    END IF;
    INSERT INTO "tempshiftprogress"( "tempshiftprogressid","tempshiftplanid","personid","staffid","status" ) VALUES( "uniquekey"("ppersonid"+"pshiftplanid"),"pshiftplanid","ppersonid","userstaffid",'A' ) 
  ELSE
    IF NOT "pvacancyid" = any(SELECT "vacancyid" FROM "cascadedvacancy" WHERE "secondaryagencyid" = @secagid) THEN
      SELECT '99:~Permission denied';
      RETURN
    END IF;
    SET @sname = (SELECT FIRST "name" FROM "person" WHERE "personid" = "ppersonid");
    IF(SELECT(IF "count"() = 0 THEN 'FALSE'
        ELSE 'TRUE'
        endif) AS "IsPresent" FROM "progress" AS "pr" LEFT OUTER JOIN "status" AS "s" ON "s"."status" = "pr"."status" WHERE "pr"."personid" = "pPersonID" AND "pr"."vacancyid" = "pVacancyID" AND "s"."type" = 'R' AND "s"."final" = 0) = 'FALSE' THEN
      INSERT INTO "progress"( "progressid","vacancyid","personid","staffid","status","actiondate" ) VALUES( "uniquekey"("ppersonid"+"pvacancyid"),"pvacancyid","ppersonid","userstaffid",'A',CURRENT DATE ) 
    ELSE
      SELECT '99:~'+@sname+' has already been submitted';
      RETURN
    END IF END IF;
  CALL "IQXNetPopup"("pWebUserID",pPersonID,NULL,"pvacancyid",NULL,NULL,'SUBMITCANDIDATE','Secondary Agency candidate submitted',@sname);
  SELECT '0:~Success'
END
}