====== pears.NetClientCombinedRequirementTemplates ======
<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"."NetClientCombinedRequirementTemplates"( in "pWebUserID" char(20) ) 
result( "VacancyID" char(20),"Position" long varchar,"DateFrom" char(10),"DateTo" char(10),"Description" char(2048),"VacType" char(5),"SiteDetails" char(300) ) 
begin
  // IQXWeb
  declare "CompanyCount" integer;
  set "CompanyCount" = (select "count"() from "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID");
  select distinct
    "v"."VacancyID" as "LineID",
    (if "isnull"("SiteDetails",'') <> '' then
      "string"("v"."Position",' ',"c"."Name",', ',"SiteDetails")
    else
      "string"("v"."Position",' ',"c"."Name")
    endif) as "Position",
    "dateformat"("v"."startdate",'dd/mm/yyyy') as "DateFrom",
    "dateformat"("v"."expiry",'dd/mm/yyyy') as "DateTo",
    "v"."othernotes" as "Description",
    'CV' as "VacType",
    "isnull"("string"("v"."sitename",(if "isnull"("v"."addr1",'') <> '' then(if "v"."Sitename" <> '' then ', ' endif)+"v"."addr1" endif),(if "isnull"("v"."addr2",'') <> '' then ', '+"v"."addr2" endif),(if "isnull"("v"."addr3",'') <> '' then ', '+"v"."addr3" endif),(if "isnull"("v"."Town",'') <> '' then ', '+"v"."town" endif),(if "isnull"("v"."postcode",'') <> '' then ', '+"v"."postcode" endif)),"GetCompanyAddressOnLine"("e"."companyid")) as "SiteDetails"
    from "Vacancy" as "v"
      key join "Employment" as "e"
      key join "Company" as "c"
    where "c"."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 union
  select distinct
    "v"."VacancyID" as "LineID",
    (if "isnull"("SiteDetails",'') <> '' then
      "string"("v"."Position",' ',"c"."Name",', ',"SiteDetails")
    else
      "string"("v"."Position",' ',"c"."Name")
    endif) as "Position",
    "dateformat"("v"."startdate",'dd/mm/yyyy') as "DateFrom",
    "dateformat"("v"."expiry",'dd/mm/yyyy') as "DateTo",
    "v"."othernotes" as "Description",
    'SV' as "VacType",
    "isnull"("string"("v"."sitename",(if "isnull"("v"."addr1",'') <> '' then(if "v"."Sitename" <> '' then ', ' endif)+"v"."addr1" endif),(if "isnull"("v"."addr2",'') <> '' then ', '+"v"."addr2" endif),(if "isnull"("v"."addr3",'') <> '' then ', '+"v"."addr3" endif),(if "isnull"("v"."Town",'') <> '' then ', '+"v"."town" endif),(if "isnull"("v"."postcode",'') <> '' then ', '+"v"."postcode" endif)),"GetCompanyAddressOnLine"("e"."companyid")) as "SiteDetails"
    from "TempShiftTemplateAllowed"
      key join "Vacancy" as "v"
      key join "Employment" as "e"
      key join "Company" as "c"
    where "c"."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"."status" = 'C'
    and "isnull"("v"."expiry",'2099-12-31') >= current date union
  select distinct
    "v"."VacancyID" as "LineID",
    "string"("v"."Position",' ',"c"."Name",', ',"SiteDetails") as "Position",
    "dateformat"("v"."startdate",'dd/mm/yyyy') as "DateFrom",
    null as "DateTo",
    "v"."othernotes" as "Description",
    'PV' as "VacType",
    "isnull"("string"("v"."sitename",(if "isnull"("v"."addr1",'') <> '' then(if "v"."Sitename" <> '' then ', ' endif)+"v"."addr1" endif),(if "isnull"("v"."addr2",'') <> '' then ', '+"v"."addr2" endif),(if "isnull"("v"."addr3",'') <> '' then ', '+"v"."addr3" endif),(if "isnull"("v"."Town",'') <> '' then ', '+"v"."town" endif),(if "isnull"("v"."postcode",'') <> '' then ', '+"v"."postcode" endif)),"GetCompanyAddressOnLine"("e"."companyid")) as "SiteDetails"
    from "Vacancy" as "v"
      key join "Employment" as "e"
      key join "Company" as "c"
    where "c"."CompanyID" = any(select "companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID")
    and "v"."temp" = 0
    and "v"."Status" = '['
    and "isnull"("v"."expiry",'2099-12-31') >= current date
    order by 3 asc,2 asc
end /* DOC
2016-10-18 PC Create combined template list for Client Requirements
2017-01-20 PC IW-69 Add PV category
2019-02-18 PC IW-826 add details to template dropdown
2019-02-20 PC slightly modify output and test routines to remove comma when no company details
2019-02-21 PC IW-825 modify to include site details
*/
/* TEST
call NetTestSetup('');
update vacancy set temp=0,status=null,tempdeskid=null,expiry=null,othernotes='test' where vacancyid='test';
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
update tempdesk set desktype='C' where tempdeskid='test';
select * from NetClientCombinedRequirementTemplates(null);
expect EOF;
select * from NetClientCombinedRequirementTemplates('test.client');
expect EOF;
update vacancy set temp=1,status='[',tempdeskid='TEST',expiry=dateadd(dd,-1,now(*)) where vacancyid='test';
select * from NetClientCombinedRequirementTemplates('test.client');
expect EOF;
update vacancy set expiry=dateadd(dd,1,now(*)),status='[',temp=1 where vacancyid='test';
select vacancyID,Position,replace(replace(Description,',','!'),' ','~') as A,VacType from NetClientCombinedRequirementTemplates('test.client');
expect Vacancyid=TEST,Position=test test,A=test,VacType=CV;
insert into tempdesk (tempdeskid,departmentid,name,desktype) on existing update defaults off values ('TEST2','~~','test2','S');
update vacancy set temp=1,tempdeskid='TEST2',expiry=dateadd(dd,1,now(*)) where vacancyid='test';
select vacancyID,Position,replace(replace(Description,',','!'),' ','~') as B,VacType from NetClientCombinedRequirementTemplates('test.client');
expect EOF;
update vacancy set expiry=dateadd(dd,1,now(*)),status='C',temp=1 where vacancyid='test';
update tempdesk set desktype='S' where tempdeskid='test';
select vacancyID,Position,replace(replace(Description,',','!'),' ','~') as C,VacType from NetClientCombinedRequirementTemplates('test.client');
expect Vacancyid=TEST,Position=test test,C=test,VacType=SV;
update vacancy set temp=0,status=null,tempdeskid=null,expiry=null where vacancyid='test';
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientCombinedRequirementTemplates" IS 
{create PROCEDURE pears."NetClientCombinedRequirementTemplates"(in pWebUserID char(20))
RESULT(VacancyID char(20),Position long varchar,DateFrom char(10),DateTo char(10),Description char(2048),VacType char(5),SiteDetails char(300) )
BEGIN
// IQXWeb
  declare CompanyCount integer;
  set CompanyCount = (select count() from iqxnetuserlink where iqxnetuserid = pWebUserID);
  select distinct
    v.VacancyID as LineID,
    (if isnull(SiteDetails,'') != '' then
        string(v.Position,' ',c."Name",', ',SiteDetails)
    else 
        string(v.Position,' ',c."Name")
    endif) as Position,
    dateformat(v.startdate,'dd/mm/yyyy') as DateFrom,
    dateformat(v.expiry,'dd/mm/yyyy') as DateTo,
    v.othernotes as Description,
    'CV' as VacType,
    isnull(string(v.sitename,(if isnull(v.addr1,'') != '' then (if v.Sitename != '' then ', ' endif)+v.addr1 endif ),(if isnull(v.addr2,'') != '' then ', ' + v.addr2 endif ),(if isnull(v.addr3,'') != '' then ', ' +v.addr3 endif ),(if isnull(v.Town,'') != '' then ', ' + v.town endif ),(if isnull(v.postcode,'') != '' then ', ' + v.postcode endif )),GetCompanyAddressOnLine(e.companyid)) as SiteDetails
    
  from
    Vacancy as v
    key join Employment e
    key join Company as c
  where 
    c.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 
  union
  select distinct
    v.VacancyID as LineID,
        (if isnull(SiteDetails,'') != '' then
        string(v.Position,' ',c."Name",', ',SiteDetails)
    else 
        string(v.Position,' ',c."Name")
    endif) as Position,
    dateformat(v.startdate,'dd/mm/yyyy') as DateFrom,
    dateformat(v.expiry,'dd/mm/yyyy') as DateTo,
    v.othernotes as Description,
    'SV' as VacType,
    isnull(string(v.sitename,(if isnull(v.addr1,'') != '' then (if v.Sitename != '' then ', ' endif)+v.addr1 endif ),(if isnull(v.addr2,'') != '' then ', ' + v.addr2 endif ),(if isnull(v.addr3,'') != '' then ', ' +v.addr3 endif ),(if isnull(v.Town,'') != '' then ', ' + v.town endif ),(if isnull(v.postcode,'') != '' then ', ' + v.postcode endif )),GetCompanyAddressOnLine(e.companyid)) as SiteDetails 
  from 
    TempShiftTemplateAllowed
    key join Vacancy as v
    key join Employment e
    key join Company as c
  where 
    c.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.status = 'C' 
    and isnull(v.expiry,'2099-12-31') >= current date 
union
    select distinct
    v.VacancyID as LineID,
    string(v.Position,' ',c."Name",', ',SiteDetails) as Position,
    dateformat(v.startdate,'dd/mm/yyyy') as DateFrom,
    null as DateTo,
    v.othernotes as Description,
    'PV' as VacType,
    isnull(string(v.sitename,(if isnull(v.addr1,'') != '' then (if v.Sitename != '' then ', ' endif)+v.addr1 endif ),(if isnull(v.addr2,'') != '' then ', ' + v.addr2 endif ),(if isnull(v.addr3,'') != '' then ', ' +v.addr3 endif ),(if isnull(v.Town,'') != '' then ', ' + v.town endif ),(if isnull(v.postcode,'') != '' then ', ' + v.postcode endif )),GetCompanyAddressOnLine(e.companyid)) as SiteDetails
  from
    Vacancy as v
    key join Employment e
    key join Company as c
  where 
    c.CompanyID = any(select companyid from employment key join iqxnetuserlink where iqxnetuserid = pWebUserID)
    and v.temp = 0 
    and v.Status = '[' 
    and isnull(v.expiry,'2099-12-31') >= current date 
  order by 3 asc,2 asc
END

/* DOC
2016-10-18 PC Create combined template list for Client Requirements
2017-01-20 PC IW-69 Add PV category
2019-02-18 PC IW-826 add details to template dropdown
2019-02-20 PC slightly modify output and test routines to remove comma when no company details
2019-02-21 PC IW-825 modify to include site details
*/

/* TEST
call NetTestSetup('');
update vacancy set temp=0,status=null,tempdeskid=null,expiry=null,othernotes='test' where vacancyid='test';
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
update tempdesk set desktype='C' where tempdeskid='test';
select * from NetClientCombinedRequirementTemplates(null);
expect EOF;
select * from NetClientCombinedRequirementTemplates('test.client');
expect EOF;
update vacancy set temp=1,status='[',tempdeskid='TEST',expiry=dateadd(dd,-1,now(*)) where vacancyid='test';
select * from NetClientCombinedRequirementTemplates('test.client');
expect EOF;
update vacancy set expiry=dateadd(dd,1,now(*)),status='[',temp=1 where vacancyid='test';
select vacancyID,Position,replace(replace(Description,',','!'),' ','~') as A,VacType from NetClientCombinedRequirementTemplates('test.client');
expect Vacancyid=TEST,Position=test test,A=test,VacType=CV;
insert into tempdesk (tempdeskid,departmentid,name,desktype) on existing update defaults off values ('TEST2','~~','test2','S');
update vacancy set temp=1,tempdeskid='TEST2',expiry=dateadd(dd,1,now(*)) where vacancyid='test';
select vacancyID,Position,replace(replace(Description,',','!'),' ','~') as B,VacType from NetClientCombinedRequirementTemplates('test.client');
expect EOF;
update vacancy set expiry=dateadd(dd,1,now(*)),status='C',temp=1 where vacancyid='test';
update tempdesk set desktype='S' where tempdeskid='test';
select vacancyID,Position,replace(replace(Description,',','!'),' ','~') as C,VacType from NetClientCombinedRequirementTemplates('test.client');
expect Vacancyid=TEST,Position=test test,C=test,VacType=SV;
update vacancy set temp=0,status=null,tempdeskid=null,expiry=null where vacancyid='test';
*/
}
</code>