====== pears.NetClientShiftTemplate ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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
}
</code>