pears.NetClientRequirementTemplates

CREATE PROCEDURE "pears"."NetClientRequirementTemplates"( IN "pWebUserID" CHAR(20),IN "pType" CHAR(2),IN "pVacancyID" CHAR(20) DEFAULT NULL ) 
RESULT( "ID" CHAR(20),"ExtraInfo" CHAR(250),"Descript" CHAR(100),"ExtraDescrip" CHAR(250) ) 
-- 20130219 filter on only current vacancies
BEGIN
  DECLARE @companycount INTEGER;
  SET @companycount = (SELECT "count"() FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID");
  IF "pType" = 'CV' THEN --Contract vacancy templates
    SELECT "v"."VacancyID" AS "ID",'' AS "ExtraInfo",
      "v"."Position" AS "Descrip",
      (IF @companycount > 1 THEN "string"("Company"."Name",', ',"GetCompanyAddressOnLine"("company"."companyid"))
      ELSE '' --Shift vacancies containing shift templates
      endif) AS "ExtraDescrip" --Shifts 
      FROM "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN "Company"
      WHERE "Company"."CompanyID" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
      AND "v"."temp" = 1 AND "v"."Status" = '[' AND "isnull"((SELECT "desktype" FROM "tempdesk" WHERE "tempdeskid" = "v"."tempdeskid"),'') <> 'S'
      AND "isnull"("v"."expiry",'2099-12-31') >= CURRENT DATE
      ORDER BY 2 ASC
  ELSE IF "pType" = 'SV' THEN
      SELECT "v"."VacancyID" AS "ID",'' AS "ExtraInfo",
        "v"."Position" AS "Descrip",
        (IF @companycount > 1 THEN "string"("Company"."Name",', ',"GetCompanyAddressOnLine"("company"."companyid"))
        ELSE ''
        endif) AS "ExtraDescrip" FROM "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN "Company"
        WHERE "Company"."CompanyID" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
        AND "v"."temp" = 1 AND "isnull"((SELECT "desktype" FROM "tempdesk" WHERE "tempdeskid" = "v"."tempdeskid"),'') = 'S'
        AND "v"."vacancyid" = any(SELECT "vacancyid" FROM "TempShiftTemplateAllowed")
        AND "v"."status" = 'C' AND "isnull"("v"."expiry",'2099-12-31') >= CURRENT DATE
        ORDER BY 2 ASC
    ELSE IF "pType" = 'SS' THEN
        SELECT "p"."TempShiftTemplateID" AS "ID",
          "string"("p"."timefrom",'|',"p"."timeto",'|',"p"."breakminutes",'|',"p"."moveable") AS "ExtraInfo",
          "p"."Description" AS "Descrip",'' AS "ExtraDescrip"
          FROM "TempShiftTemplate" AS "p" KEY JOIN "TempShiftTemplateAllowed" KEY JOIN "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN "Company"
          WHERE "v"."vacancyid" = "pvacancyid"
          AND "Company"."CompanyID" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
          AND "v"."status" = 'C' AND "isnull"("v"."expiry",'2099-12-31') >= CURRENT DATE
          ORDER BY 2 ASC
      END IF
    END IF
  END IF
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientRequirementTemplates" IS 
{CREATE PROCEDURE pears."NetClientRequirementTemplates"( IN "pWebUserID" CHAR(20),IN "pType" CHAR(2),IN "pVacancyID" CHAR(20) DEFAULT NULL ) 
RESULT( "ID" CHAR(20),"ExtraInfo" CHAR(250),"Descript" CHAR(100),"ExtraDescrip" CHAR(250) ) 
-- 20130219 filter on only current vacancies
BEGIN
  DECLARE @companycount INTEGER;
  SET @companycount = (SELECT "count"() FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID");
  IF "pType" = 'CV' THEN --Contract vacancy templates
    SELECT "v"."VacancyID" AS "ID",'' AS "ExtraInfo",
      "v"."Position" AS "Descrip",
      (IF @companycount > 1 THEN "string"("Company"."Name",', ',"GetCompanyAddressOnLine"("company"."companyid"))
      ELSE '' --Shift vacancies containing shift templates
      endif) AS "ExtraDescrip" --Shifts 
      FROM "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN "Company"
      WHERE "Company"."CompanyID" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
      AND "v"."temp" = 1 AND "v"."Status" = '[' AND "isnull"((SELECT "desktype" FROM "tempdesk" WHERE "tempdeskid" = "v"."tempdeskid"),'') <> 'S'
      AND "isnull"("v"."expiry",'2099-12-31') >= CURRENT DATE
      ORDER BY 2 ASC
  ELSE IF "pType" = 'SV' THEN
      SELECT "v"."VacancyID" AS "ID",'' AS "ExtraInfo",
        "v"."Position" AS "Descrip",
        (IF @companycount > 1 THEN "string"("Company"."Name",', ',"GetCompanyAddressOnLine"("company"."companyid"))
        ELSE ''
        endif) AS "ExtraDescrip" FROM "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN "Company"
        WHERE "Company"."CompanyID" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
        AND "v"."temp" = 1 AND "isnull"((SELECT "desktype" FROM "tempdesk" WHERE "tempdeskid" = "v"."tempdeskid"),'') = 'S'
        AND "v"."vacancyid" = any(SELECT "vacancyid" FROM "TempShiftTemplateAllowed")
        AND "v"."status" = 'C' AND "isnull"("v"."expiry",'2099-12-31') >= CURRENT DATE
        ORDER BY 2 ASC
    ELSE IF "pType" = 'SS' THEN
        SELECT "p"."TempShiftTemplateID" AS "ID",
          "string"("p"."timefrom",'|',"p"."timeto",'|',"p"."breakminutes",'|',"p"."moveable") AS "ExtraInfo",
          "p"."Description" AS "Descrip",'' AS "ExtraDescrip"
          FROM "TempShiftTemplate" AS "p" KEY JOIN "TempShiftTemplateAllowed" KEY JOIN "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN "Company"
          WHERE "v"."vacancyid" = "pvacancyid"
          AND "Company"."CompanyID" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
          AND "v"."status" = 'C' AND "isnull"("v"."expiry",'2099-12-31') >= CURRENT DATE
          ORDER BY 2 ASC
      END IF
    END IF
  END IF
END
}