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