pears.NetClientRequirementList [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.NetClientRequirementList ======
<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"."NetClientRequirementList"( in "pWebUserID" char(20),in "pSlice" integer default 0,in "pSliceSize" integer default 10,in "pIncCancelled" smallint default 0,in "pStartDate" char(26) default null,in "pEndDate" char(26) default null,in "SearchString" char(100) default null,in "pStatus" char(1) default null ) 
result( "RowID" char(20),"StartDate" char(20),"CombinedTime" char(20),"IsShift" smallint,"Position" char(50),"ShiftDescription" char(50),"CompanyID" char(20),
  "CompanyName" char(60),"ContactName" char(60),"Consultant" char(60),"OurRef" char(50),"ContractRef" char(20),"ShiftRef" char(20),
  "FilledBy" char(250),"StartTime" char(15),"FinishTime" char(15),
  "FinishDate" char(30),"LongDescription" long varchar,"PersonID" char(20),
  "TextStatus" char(100),"CreatedBy" char(255),"CancelState" char(50),"ReqDescription" char(10),"ClientNote" char(50),
  "VacancyNotes" long varchar,"TextStatusFull" char(100),"SiteDetails" char(300),"NoOfPlacements" integer,"NoOfActivePlacements" integer ) 
begin
  // IQXNet
  declare "DateRangeStart" date;
  declare "DateRangeEnd" date;
  declare "x" integer;
  declare "y" integer;
  declare "SendVNotesToWeb" char(10);
  declare "SendVOtherNotesToWeb" char(10);
  declare local temporary table "IDs"(
    "ID" char(20) not null,
    "CompanyStartDate" date null,
    ) not transactional;
  if "IQXNetHasPermission"("pWebUserID",'ORDERJOBS') = 0 then
    return
  end if;
  select "isnull"("WPKMaintainGetSwitchValue"('SENDVNOTESTOWEB','WEBSITE','l'),'') into "SendVNotesToWeb";
  select "isnull"("WPKMaintainGetSwitchValue"('SENDVOTHERNOTESTOWEB','WEBSITE','l'),'') into "SendVOtherNotesToWeb";
  insert into "IDs"( "ID","CompanyStartDate" ) 
    select distinct "companyid","IQXNetCompanyStartDate"("companyid") from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID";
  set "DateRangeStart" = "IQXNetStringToDate"("pStartDate");
  set "DateRangeEnd" = "IQXNetStringToDate"("pEndDate");
  set "DateRangeStart" = "isnull"("DateRangeStart","dateadd"("week",-1,current date));
  set "DateRangeEnd" = "isnull"("DateRangeEnd","dateadd"("year",1,current date));
  set "x" = "pSliceSize";
  set "y" = "x"*"pSlice"+1;
  select top "x" start at "y" *
    from(select "v"."VacancyID" as "RowID",
        "v"."startdate" as "StartDate",
        null as "CombinedTime",
        0 as "IsShift",
        "v"."Position" as "Position",
        null as "ShiftDescription",
        "Company"."companyID" as "CompanyID",
        "Company"."Name" as "CompanyName",
        "Person"."Name" as "ContactName",
        "st"."Name" as "Consultant",
        "v"."TheirRef" as "OurRef",
        "v"."ContractRef",
        "v"."refcode" as "ShiftRef",
        if("isnull"("v"."noofposts",0) <= 1) then
          "isnull"((select first "ep"."name" from "placement" key join "employment" as "em" key join "person" as "ep" where "placement"."vacancyid" = "v"."vacancyid" and current date between "em"."startdate" and "isnull"("em"."leavedate",current date)),'Unfilled')
        else
          "string"("v"."noofposts",' Posts, ',
          "NoOfPlacements",' Placements, ',
          "NoOfActivePlacements",' Active')
        endif as "FilledBy",
        null as "StartTime",
        null as "FinishTime",
        if("v"."noofposts" = 1) then
          "isnull"((select first "em"."leavedate" from "placement" key join "employment" as "em" key join "person" as "ep" where "placement"."vacancyid" = "v"."vacancyid" and current date between "em"."startdate" and "isnull"("em"."leavedate",current date) and "em"."leavedate" is not null),"v"."finishdate")
        else
          "v"."finishdate"
        endif as "FinishDate",
        (if "SendVOtherNotesToWeb" = 'Y' then "v"."OtherNotes" else '' endif) as "LongDescription",
        '' as "PersonID",
        (if "NoOfActivePlacements" = 0 then 'Unfilled' else if "NoOfActivePlacements" < "v"."noofposts" then 'Partially filled' else "s"."name" endif endif) as "TextStatus",
        "string"('Created by: ',"st"."name",' at: ',"dateformat"("v"."whenentered",'dd/mm/yyyy hh:mm')) as "CreatedBy",
        null as "CancelState",
        'Contract' as "ReqDescription",
        '' as "ClientNote",
        (if "SendVNotesToWeb" = 'Y' then "v"."notes" else '' endif) as "VacancyNotes",
        "TextStatus" as "TextStatusFull",
        "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",
        "isnull"((select "count"() from "placement" key join "employment" as "em" key join "person" as "ep" where "placement"."vacancyid" = "v"."vacancyid" and "isnull"("placement"."withdrawreason",'') = ''),0) as "NoOfPlacements",
        (select "count"() from "placement" key join "employment" as "em" key join "person" as "ep" where "placement"."vacancyid" = "v"."vacancyid" and current date between "isnull"("em"."startdate",current date) and "isnull"("em"."leavedate",current date) and "isnull"("placement"."withdrawreason",'') = '') as "NoOfActivePlacements"
        from "vacancy" as "v" key join "Employment" as "e" key join("Company","Person")
          join "IDs" on "e"."CompanyID" = "IDs"."ID"
          ,"vacancy" as "v" key left outer join "Staff" as "st"
          ,"vacancy" as "v" left outer join "Status" as "s" on "s"."Status" = "v"."Status" and "s"."Type" = 'V'
          ,"vacancy" as "v" key join("tempdesk","Department" as "d")
        where "StartDate" <= "DateRangeEnd"
        and "v"."status" <> '['
        and "tempdesk"."desktype" <> 'S'
        and "v"."status" = "isnull"("pStatus","v"."status")
        and "isnull"("v"."expiry","today"()) >= "today"()
        and("pIncCancelled" = 0 and "v"."status" <> 'X'
        or "pIncCancelled" = 1) union all
      select "p"."TempShiftPlanID" as "RowID",
        "isnull"("s"."shiftdate","p"."ShiftDate","v"."startdate") as "StartDate",
        cast("dateformat"("StartTime",'hh:nn') as char)+' - '+cast("dateformat"("FinishTime",'hh:nn') as char) as "CombinedTime",
        1 as "IsShift",
        "v"."Position" as "Position",
        "p"."Description" as "ShiftDescription",
        "Company"."companyID" as "CompanyID",
        "Company"."Name" as "CompanyName",
        "Pe"."Name" as "ContactName",
        "staff"."name" as "Consultant",
        "isnull"("s"."referencecode","p"."referencecode") as "OurRef",
        "v"."ContractRef",
        cast("p"."ShiftSerialNumber" as char(20)) as "ShiftRef",
        "string"("pt"."name",(if "pt"."status" = 'A' then ' (S)' endif)) as "FilledBy",
        "dateformat"("isnull"("s"."timefrom","p"."TimeFrom"),'hh:mm') as "StartTime",
        "dateformat"("isnull"("s"."timeto","p"."TimeTo"),'hh:mm') as "FinishTime",
        (if "FinishTime" < "StartTime" then "dateadd"("dd",1,"StartDate") else "StartDate" endif) as "FinishDate",
        (if "SendVOtherNotesToWeb" <> 'Y' then ''
        else if "isnull"("d"."showvacextranotes",0) = 1 then cast("v"."extranotes" as long varchar)
          else cast("v"."othernotes" as long varchar)
          endif
        endif) as "LongDescription","s"."personid" as "PersonID",
        "NetTempShiftStatusString"("s"."state") as "TextStatus",
        ('Ordered by: '+"p"."orderedby"+' at: '+"dateformat"("p"."whenentered",'dd/mm/yyyy hh:mm')) as "CreatedBy",
        "string"("dateformat"("s"."WhenCancelled",'dd/mm/yyyy hh:nn:ss'),' by ',"isnull"((select "name" from "staff" where "staff"."staffid" = "s"."WhoCancelled"),'(not recorded)')) as "CancelState",
        'Shift' as "ReqDescription",
        "p"."ClientNote",
        (if "SendVNotesToWeb" = 'Y' then "v"."notes" else '' endif) as "VacancyNotes",
        "NetTempShiftStatusString"("s"."state","s"."tempconfirmed","s"."clientconfirmed","cr"."name") as "TextStatusFull",
        "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")),
        0 as "NoOfPlacements",
        0 as "NoOfActivePlacements"
        from "TempShiftplan" as "p"
          key left outer join "tempshift" as "s"
          ,"TempShiftplan" as "p" key join "vacancy" as "v" key join "tempdesk" as "td"
          key join "Employment" as "e"
          key join("Company","Person" as "Pe")
          join "IDs" on "e"."CompanyID" = "IDs"."ID"
          ,"vacancy" as "v" key join "Department" as "d"
          ,"vacancy" as "v" key left outer join "Staff"
          ,"tempshift" as "s" key left outer join "person" as "pt"
          ,"tempshift" as "s" key left outer join "tempshiftcancelreason" as "cr"
        where "StartDate" between "DateRangeStart" and "DateRangeEnd"
        and "v"."status" = "isnull"("pStatus","v"."status")
        and "td"."desktype" = 'S'
        and "StartDate" >= "IDs"."CompanyStartDate"
        and("pIncCancelled" = 0 and not("isnull"("s"."State",'') = 'C' and "isnull"("s"."cRefill",0) = 0)
        or "pIncCancelled" = 1)) as "innerquery"
    order by "StartDate" desc,"RowID" desc
end /* DOC
2016-10-21 PC IW-71 Create list of Current Requirements for Client. SearchString not implemented.
2018-02-28 PC IW-388 Client reference code
2018-03-12 PC IW-390 changes by JB incorporated
2018-04-13 JB iw-410 return vacancy notes, full status, change cancel
2018-05-30 PC IW-588 modify shift status to include candidate and client confirmed, and other changes to simplify linking
2018-06-06 MHS reduced and simplified, got TextStatusFull working. Fixed tests.
2019-01-10 PC IW-622 adjust visibility of permanent requirements
2019-02-19 PC IW-839 modify ReqDescription to check for tempdesk
2019-02-19 PC IW-825 add site details and clients details columns
2019-02-21 PC IW-825 modify address to refer to site address first
2019-02-22 PC IW-825 adjust temp filter to look for only non-temp
2019-02-27 PC IW-825 split temps into contract/shift
2019-05-03 MHS fixed to show Contract temp vacancies which had been erroneously excluded
2019-05-03 MHS Removed weird hybrid contract/shift query. Fixed erroneous outer join. Extended tests to cover shifts.
2020-01-31 AW removed seconds from date formatting on ln 105 + ln 63
2020-03-16 GJ/MHS Added include cancelled switch and expanded the tests
2020-05-27 GJ IW-1187 Added companyID to result set
2020-06-09 MHS put the union into an inner query and the slicing and ordering on the outer query (adding RowID to the order by so that it is more deterministic), so that slicing works properly
*/
/* TEST
call NetTestSetup('');
delete from tempshift from tempshift key join vacancy where vacancy.employmentid in (select employmentid from iqxnetuserlink where iqxnetuserid='test.client');
delete from tempshiftplan from tempshiftplan key join vacancy where vacancy.employmentid in (select employmentid from iqxnetuserlink where iqxnetuserid='test.client');
update vacancy set startdate=current date-100,finishdate=current date-100,expiry=current date-100 where employmentid='TEST' and vacancyid<>'TEST';
update vacancy set status='C',startdate=current date,finishdate=current date+5 where vacancyid='TEST';
update tempdesk set desktype='W' where tempdeskid='test';
select * from NetClientRequirementList(null,0,9);
expect EOF;
delete from iqxnetrightallowed where iqxnetrightid='ORDERJOBS' and IQXNetUserClassID='CLIENT';
select * from NetClientRequirementList('test.client',0,10);
expect EOF;
insert into iqxnetrightallowed (iqxnetrightid,iqxnetuserclassid,id,maintainonweb) values ('ORDERJOBS','CLIENT','*',1);
select * from NetClientRequirementList('test.client',0,11);
expect CompanyName=test, isshift=0;
select * from NetClientRequirementList('test.client',0,12,1,dateformat(current date+10,'dd/mm/yyyy'),dateformat(current date+20,'dd/mm/yyyy'));
expect CompanyName=test, isshift=0;
select * from NetClientRequirementList('test.client',0,13,1,current date-10,current date+20);
expect CompanyName=test;
update vacancy set status='X' where vacancyid='TEST';
select * from NetClientRequirementList('test.client',0,131,1,current date-10,current date+20);
expect CompanyName=test;
select * from NetClientRequirementList('test.client',0,132,0,current date-10,current date+20);
expect EOF;
update tempdesk set desktype='S' where tempdeskid='test';
select * from NetClientRequirementList('test.client',0,14) where isshift=1;
expect EOF;
insert into tempshiftplan(tempshiftplanid,vacancyid,description,shiftdate) values ('TEST','TEST','TestShift',current date);
select * from NetClientRequirementList('test.client',0,15) where isshift=1;
expect ShiftDescription=TestShift;
insert into tempshift(tempshiftid,tempshiftplanid,vacancyid,shiftdate,state,personid) values ('TEST','TEST','TEST',current date,'P','TEST');
select * from NetClientRequirementList('test.client',0,16) where isshift=1;
expect ShiftDescription=TestShift;
update tempshift set state='C' where tempshiftid='TEST';
select * from NetClientRequirementList('test.client',0,17) where isshift=1;
expect EOF;
select * from NetClientRequirementList('test.client',0,18,1) where isshift=1;
expect ShiftDescription=TestShift;
update tempshift set crefill=1 where tempshiftid='TEST';
select * from NetClientRequirementList('test.client',0,19) where isshift=1;
expect ShiftDescription=TestShift;
â€‹
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientRequirementList" IS 
{create PROCEDURE pears."NetClientRequirementList"( in pWebUserID char(20),in pSlice integer default 0,in pSliceSize integer default 10,in pIncCancelled smallint default 0, in pStartDate char(26) default null,in pEndDate char(26) default null,in SearchString char(100) default null,in pStatus char(1) default null ) 
result( RowID char(20),StartDate char(20),CombinedTime char(20),IsShift smallint,Position char(50),ShiftDescription char(50), CompanyID char(20),
  CompanyName char(60),ContactName char(60),Consultant char(60),OurRef char(50),ContractRef char(20),ShiftRef char(20),
  FilledBy char(250),StartTime char(15),FinishTime char(15),
  FinishDate char(30),LongDescription long varchar,PersonID char(20),
  TextStatus char(100),CreatedBy char(255),CancelState char(50),ReqDescription char(10),ClientNote char(50),
  VacancyNotes long varchar,TextStatusFull char(100),SiteDetails char(300),NoOfPlacements integer,NoOfActivePlacements integer ) 
begin
  // IQXNet
  declare DateRangeStart date;
  declare DateRangeEnd date;
  declare x integer;
  declare y integer;
  declare SendVNotesToWeb char(10);
  declare SendVOtherNotesToWeb char(10);
  declare local temporary table IDs(
    ID char(20) not null,
    CompanyStartDate date null,
    ) not transactional;
  if IQXNetHasPermission(pWebUserID,'ORDERJOBS') = 0 then
    return
  end if;
  select isnull(WPKMaintainGetSwitchValue('SENDVNOTESTOWEB','WEBSITE','l'),'') into SendVNotesToWeb;
  select isnull(WPKMaintainGetSwitchValue('SENDVOTHERNOTESTOWEB','WEBSITE','l'),'') into SendVOtherNotesToWeb;
  insert into IDs( ID,CompanyStartDate ) 
    select distinct companyid,IQXNetCompanyStartDate(companyid) from employment key join iqxnetuserlink where iqxnetuserid = pWebUserID;
  set DateRangeStart = IQXNetStringToDate(pStartDate);
  set DateRangeEnd = IQXNetStringToDate(pEndDate);
  set DateRangeStart = isnull(DateRangeStart,dateadd(week,-1,current date));
  set DateRangeEnd = isnull(DateRangeEnd,dateadd(year,1,current date));
  set x = pSliceSize;
  set y = x*pSlice+1;
  select top x start at y * from (select
    v.VacancyID as RowID,
    v.startdate as StartDate,
    null as CombinedTime,
    0 as IsShift,
    v.Position as Position,
    null as ShiftDescription,
    Company.companyID as CompanyID,
    Company.Name as CompanyName,
    Person.Name as ContactName,
    st.Name as Consultant,
    v.TheirRef as OurRef,
    v.ContractRef,
    v.refcode as ShiftRef,
    if(isnull(v.noofposts,0) <= 1) then
      isnull((select first ep.name from placement key join employment as em key join person as ep where placement.vacancyid = v.vacancyid and current date between em.startdate and isnull(em.leavedate,current date)),'Unfilled')
    else
      string(v.noofposts,' Posts, ',
      NoOfPlacements,' Placements, ',
      NoOfActivePlacements,' Active')
    endif as FilledBy,
    null as StartTime,
    null as FinishTime,
    if(v.noofposts = 1) then
      isnull((select first em.leavedate from placement key join employment as em key join person as ep where placement.vacancyid = v.vacancyid and current date between em.startdate and isnull(em.leavedate,current date) and em.leavedate is not null),v.finishdate)
    else
      v.finishdate
    endif as FinishDate,
    (if SendVOtherNotesToWeb = 'Y' then v.OtherNotes else '' endif) as LongDescription,
    '' as PersonID,
    (if NoOfActivePlacements = 0 then 'Unfilled' else if NoOfActivePlacements < v.noofposts then 'Partially filled' else s.name endif endif) as TextStatus,
    string('Created by: ',st.name,' at: ',dateformat(v.whenentered,'dd/mm/yyyy hh:mm')) as CreatedBy,
    null as CancelState,
    'Contract' as ReqDescription,
    '' as ClientNote,
    (if SendVNotesToWeb = 'Y' then v.notes else '' endif) as VacancyNotes,
    TextStatus as TextStatusFull,
    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,
    isnull((select count() from placement key join employment as em key join person as ep where placement.vacancyid = v.vacancyid and isnull(placement.withdrawreason,'') = ''),0) as NoOfPlacements,
    (select count() from placement key join employment as em key join person as ep where placement.vacancyid = v.vacancyid and current date between isnull(em.startdate,current date) and isnull(em.leavedate,current date) and isnull(placement.withdrawreason,'') = '') as NoOfActivePlacements
    from vacancy as v key join Employment as e key join(Company,Person)
      join IDs on e.CompanyID = IDs.ID
      ,vacancy as v key left outer join Staff as st
      ,vacancy as v left outer join Status as s on s.Status = v.Status and s.Type = 'V'
      ,vacancy as v key join (tempdesk, Department as d)
    where StartDate <= DateRangeEnd
    and v.status <> '['
    and tempdesk.desktype <> 'S'
    and v.status = isnull(pStatus,v.status)
    and isnull(v.expiry,today()) >= today()
    AND (
        pIncCancelled = 0 AND v.status <> 'X'
      OR
        pIncCancelled = 1 
    )
 union all
  select p.TempShiftPlanID as RowID,
    isnull(s.shiftdate,p.ShiftDate,v.startdate) as StartDate,
    cast(dateformat(StartTime,'hh:nn') as char)+' - '+cast(dateformat(FinishTime,'hh:nn') as char) as CombinedTime,
    1 as IsShift,
    v.Position as Position,
    p.Description as ShiftDescription,
    Company.companyID as CompanyID,
    Company.Name as CompanyName,
    Pe.Name as ContactName,
    staff.name as Consultant,
    isnull(s.referencecode,p.referencecode) as OurRef,
    v.ContractRef,
    cast(p.ShiftSerialNumber as char(20)) as ShiftRef,
    string(pt.name,(if pt.status = 'A' then ' (S)' endif)) as FilledBy,
    dateformat(isnull(s.timefrom,p.TimeFrom),'hh:mm') as StartTime,
    dateformat(isnull(s.timeto,p.TimeTo),'hh:mm') as FinishTime,
    (if FinishTime < StartTime then dateadd(dd,1,StartDate) else StartDate endif) as FinishDate,
    (if SendVOtherNotesToWeb <> 'Y' then ''
    else if isnull(d.showvacextranotes,0) = 1 then cast(v.extranotes as long varchar)
      else cast(v.othernotes as long varchar)
      endif
    endif) as LongDescription,s.personid as PersonID,
    NetTempShiftStatusString(s.state) as TextStatus,
    ('Ordered by: '+p.orderedby+' at: '+dateformat(p.whenentered,'dd/mm/yyyy hh:mm')) as CreatedBy,
    string(dateformat(s.WhenCancelled,'dd/mm/yyyy hh:nn:ss'),' by ',isnull((select name from staff where staff.staffid = s.WhoCancelled),'(not recorded)')) as CancelState,
    'Shift' as ReqDescription,
    p.ClientNote,
    (if SendVNotesToWeb = 'Y' then v.notes else '' endif) as VacancyNotes,
    NetTempShiftStatusString(s.state,s.tempconfirmed,s.clientconfirmed,cr.name) as TextStatusFull,
    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)),
    0 as NoOfPlacements,
    0 as NoOfActivePlacements
    from TempShiftplan as p
      key left outer join tempshift as s,
      TempShiftplan as p key join vacancy as v key join tempdesk as td
      key join Employment as e
      key join(Company,Person as Pe)
      join IDs on e.CompanyID = IDs.ID
      ,vacancy as v key join Department as d
      ,vacancy as v key left outer join Staff
      ,tempshift as s key left outer join person as pt
      ,tempshift as s key left outer join tempshiftcancelreason as cr
    where StartDate between DateRangeStart and DateRangeEnd
    and v.status = isnull(pStatus,v.status)
    and td.desktype = 'S'
    and StartDate >= IDs.CompanyStartDate
    AND (
        pIncCancelled = 0 AND not (isnull(s.State,'') = 'C' AND isnull(s.cRefill,0) = 0)
      OR
        pIncCancelled = 1 
    ) ) as innerquery
    order by StartDate desc, RowID desc
end /* DOC
2016-10-21 PC IW-71 Create list of Current Requirements for Client. SearchString not implemented.
2018-02-28 PC IW-388 Client reference code
2018-03-12 PC IW-390 changes by JB incorporated
2018-04-13 JB iw-410 return vacancy notes, full status, change cancel
2018-05-30 PC IW-588 modify shift status to include candidate and client confirmed, and other changes to simplify linking
2018-06-06 MHS reduced and simplified, got TextStatusFull working. Fixed tests.
2019-01-10 PC IW-622 adjust visibility of permanent requirements
2019-02-19 PC IW-839 modify ReqDescription to check for tempdesk
2019-02-19 PC IW-825 add site details and clients details columns
2019-02-21 PC IW-825 modify address to refer to site address first
2019-02-22 PC IW-825 adjust temp filter to look for only non-temp
2019-02-27 PC IW-825 split temps into contract/shift
2019-05-03 MHS fixed to show Contract temp vacancies which had been erroneously excluded
2019-05-03 MHS Removed weird hybrid contract/shift query. Fixed erroneous outer join. Extended tests to cover shifts.
2020-01-31 AW removed seconds from date formatting on ln 105 + ln 63
2020-03-16 GJ/MHS Added include cancelled switch and expanded the tests
2020-05-27 GJ IW-1187 Added companyID to result set
2020-06-09 MHS put the union into an inner query and the slicing and ordering on the outer query (adding RowID to the order by so that it is more deterministic), so that slicing works properly
*/
/* TEST
call NetTestSetup('');
delete from tempshift from tempshift key join vacancy where vacancy.employmentid in (select employmentid from iqxnetuserlink where iqxnetuserid='test.client');
delete from tempshiftplan from tempshiftplan key join vacancy where vacancy.employmentid in (select employmentid from iqxnetuserlink where iqxnetuserid='test.client');
update vacancy set startdate=current date-100,finishdate=current date-100,expiry=current date-100 where employmentid='TEST' and vacancyid<>'TEST';
update vacancy set status='C',startdate=current date,finishdate=current date+5 where vacancyid='TEST';
update tempdesk set desktype='W' where tempdeskid='test';
select * from NetClientRequirementList(null,0,9);
expect EOF;
delete from iqxnetrightallowed where iqxnetrightid='ORDERJOBS' and IQXNetUserClassID='CLIENT';
select * from NetClientRequirementList('test.client',0,10);
expect EOF;
insert into iqxnetrightallowed (iqxnetrightid,iqxnetuserclassid,id,maintainonweb) values ('ORDERJOBS','CLIENT','*',1);
select * from NetClientRequirementList('test.client',0,11);
expect CompanyName=test, isshift=0;
select * from NetClientRequirementList('test.client',0,12,1,dateformat(current date+10,'dd/mm/yyyy'),dateformat(current date+20,'dd/mm/yyyy'));
expect CompanyName=test, isshift=0;
select * from NetClientRequirementList('test.client',0,13,1,current date-10,current date+20);
expect CompanyName=test;
update vacancy set status='X' where vacancyid='TEST';
select * from NetClientRequirementList('test.client',0,131,1,current date-10,current date+20);
expect CompanyName=test;
select * from NetClientRequirementList('test.client',0,132,0,current date-10,current date+20);
expect EOF;
update tempdesk set desktype='S' where tempdeskid='test';
select * from NetClientRequirementList('test.client',0,14) where isshift=1;
expect EOF;
insert into tempshiftplan(tempshiftplanid,vacancyid,description,shiftdate) values ('TEST','TEST','TestShift',current date);
select * from NetClientRequirementList('test.client',0,15) where isshift=1;
expect ShiftDescription=TestShift;
insert into tempshift(tempshiftid,tempshiftplanid,vacancyid,shiftdate,state,personid) values ('TEST','TEST','TEST',current date,'P','TEST');
select * from NetClientRequirementList('test.client',0,16) where isshift=1;
expect ShiftDescription=TestShift;
update tempshift set state='C' where tempshiftid='TEST';
select * from NetClientRequirementList('test.client',0,17) where isshift=1;
expect EOF;
select * from NetClientRequirementList('test.client',0,18,1) where isshift=1;
expect ShiftDescription=TestShift;
update tempshift set crefill=1 where tempshiftid='TEST';
select * from NetClientRequirementList('test.client',0,19) where isshift=1;
expect ShiftDescription=TestShift;
â€‹
*/
}
</code>