pears.NetClientShiftTemplate

CREATE PROCEDURE "pears"."NetClientShiftTemplate"( IN "pWebUserID" CHAR(20),IN "pVacancyID" CHAR(20),IN "pShiftTemplateID" CHAR(20) ) 
RESULT( "VacancyID" CHAR(20),"TempShiftTemplateID" CHAR(20),"Position" CHAR(50),"ShiftDescription" CHAR(50),"CompanyName" CHAR(60),"ContactName" CHAR(30),"Department" CHAR(30),"TimeFrom" TIME,"TimeTo" TIME,"BreakMinutes" SMALLINT,"Moveable" SMALLINT,"RefRequired" SMALLINT,"ChoiceList" long VARCHAR,"GradeList" long VARCHAR,"OrderReasonList" long VARCHAR ) 
BEGIN
  SELECT "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",
    "p"."TimeFrom",
    "p"."TimeTo",
    "p"."BreakMinutes",
    "isnull"("p"."Moveable",0) AS "Moveable",
    (IF "p"."ReferenceRequired" = 'E' THEN 1 ELSE 0 endif) AS "RefRequired",
    "IQXNetTemplateChoiceList"("p"."TempShiftTemplateID","v"."DepartmentID") AS "ChoiceList",
    "IQXNetTemplateGradeList"("p"."TempShiftTemplateID","v"."DepartmentID") AS "GradeList",
    "NetOrderReasonList"() AS "OrderReasonList"
    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")
    AND "v"."VacancyID" = "pVacancyID" AND "p"."TempShiftTemplateID" = "pShiftTemplateID"
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientShiftTemplate" IS 
{CREATE PROCEDURE pears."NetClientShiftTemplate"( IN "pWebUserID" CHAR(20),IN "pVacancyID" CHAR(20),IN "pShiftTemplateID" CHAR(20) ) 
RESULT( "VacancyID" CHAR(20),"TempShiftTemplateID" CHAR(20),"Position" CHAR(50),"ShiftDescription" CHAR(50),"CompanyName" CHAR(60),"ContactName" CHAR(30),"Department" CHAR(30),"TimeFrom" TIME,"TimeTo" TIME,"BreakMinutes" SMALLINT,"Moveable" SMALLINT,"RefRequired" SMALLINT,"ChoiceList" long VARCHAR,"GradeList" long VARCHAR,"OrderReasonList" long VARCHAR ) 
BEGIN
  SELECT "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",
    "p"."TimeFrom",
    "p"."TimeTo",
    "p"."BreakMinutes",
    "isnull"("p"."Moveable",0) AS "Moveable",
    (IF "p"."ReferenceRequired" = 'E' THEN 1 ELSE 0 endif) AS "RefRequired",
    "IQXNetTemplateChoiceList"("p"."TempShiftTemplateID","v"."DepartmentID") AS "ChoiceList",
    "IQXNetTemplateGradeList"("p"."TempShiftTemplateID","v"."DepartmentID") AS "GradeList",
    "NetOrderReasonList"() AS "OrderReasonList"
    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")
    AND "v"."VacancyID" = "pVacancyID" AND "p"."TempShiftTemplateID" = "pShiftTemplateID"
END
}