====== pears.NetClientVacancyTemplates ======
<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"."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
}
</code>