pears.NetClientVacancyTemplates

CREATE PROCEDURE "pears"."NetClientVacancyTemplates"( IN "pWebUserID" CHAR(20) ) 
RESULT( "IsShift" SMALLINT,"VacancyID" CHAR(20),"TempShiftTemplateID" CHAR(20),"Position" CHAR(50),"ShiftDescription" CHAR(100),"CompanyName" CHAR(60),"ContactName" CHAR(30),"Department" CHAR(30) ) 
BEGIN
  SELECT 0 AS "IsShift",
    "v"."VacancyID",
    CAST(NULL AS CHAR(20)) AS "TempShiftTemplateID",
    "v"."Position" AS "Position",
    CAST(NULL AS CHAR(50)) AS "ShiftDescription",
    "Company"."Name" AS "CompanyName",
    "Person"."Name" AS "ContactName",
    (SELECT "Name" FROM "Department" WHERE "Department"."DepartmentID" = "v"."DepartmentID") AS "Department"
    FROM "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN("Company","Person")
    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' UNION ALL
  SELECT 1 AS "IsShift",
    "v"."VacancyID",
    "p"."TempShiftTemplateID",
    "v"."Position",
    "p"."Description" AS "ShiftDescription",
    "Company"."Name" AS "CompanyName",
    "Person"."Name" AS "ContactName",
    (SELECT "Name" FROM "Department" WHERE "Department"."DepartmentID" = "v"."DepartmentID") AS "Department"
    FROM "TempShiftTemplate" AS "p" KEY JOIN "TempShiftTemplateAllowed" KEY JOIN "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN("Company","Person")
    WHERE "Company"."CompanyID" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
    ORDER BY 4 ASC,5 ASC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientVacancyTemplates" IS 
{CREATE PROCEDURE pears."NetClientVacancyTemplates"( IN "pWebUserID" CHAR(20) ) 
RESULT( "IsShift" SMALLINT,"VacancyID" CHAR(20),"TempShiftTemplateID" CHAR(20),"Position" CHAR(50),"ShiftDescription" CHAR(100),"CompanyName" CHAR(60),"ContactName" CHAR(30),"Department" CHAR(30) ) 
BEGIN
  SELECT 0 AS "IsShift",
    "v"."VacancyID",
    CAST(NULL AS CHAR(20)) AS "TempShiftTemplateID",
    "v"."Position" AS "Position",
    CAST(NULL AS CHAR(50)) AS "ShiftDescription",
    "Company"."Name" AS "CompanyName",
    "Person"."Name" AS "ContactName",
    (SELECT "Name" FROM "Department" WHERE "Department"."DepartmentID" = "v"."DepartmentID") AS "Department"
    FROM "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN("Company","Person")
    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' UNION ALL
  SELECT 1 AS "IsShift",
    "v"."VacancyID",
    "p"."TempShiftTemplateID",
    "v"."Position",
    "p"."Description" AS "ShiftDescription",
    "Company"."Name" AS "CompanyName",
    "Person"."Name" AS "ContactName",
    (SELECT "Name" FROM "Department" WHERE "Department"."DepartmentID" = "v"."DepartmentID") AS "Department"
    FROM "TempShiftTemplate" AS "p" KEY JOIN "TempShiftTemplateAllowed" KEY JOIN "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN("Company","Person")
    WHERE "Company"."CompanyID" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
    ORDER BY 4 ASC,5 ASC
END
}