====== pears.NetOwnerRequirementList ======
<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"."NetOwnerRequirementList"( in "pWebUserID" char(20),in "pSlice" integer default 1,in "pSliceSize" integer default 10,in "pStartDate" char(26) default null,in "pEndDate" char(26) default null,in "SearchString" char(100) default null,in "pStatus" char(1) default null,in "pConsultantFilter" char(2048) 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),"ContactID" char(20),"ContactName" char(60),"DetailStatus" char(30),"ListStatus" char(30),"Department" char(50),"Consultant" char(60),"OurRef" char(20),"ContractRef" char(20),"ShiftRef" char(20),"WhenEntered" timestamp,"FilledBy" char(250),"WhoCancelled" char(60),"StartTime" char(15),"FinishTime" char(15),"FinishDate" char(30),"Filled" smallint,"Unconfirmed" smallint,"LongDescription" long varchar,"CompanyAddress" char(250),"PersonID" char(20),"TextStatus" char(100),"CancelButton" smallint,"ConfirmButton" smallint,"RejectButton" smallint,"FillButton" smallint,"PONumber" char(50),"CreatedBy" char(255),"CancelState" char(50),"ConfirmedBy" char(60) ) 
begin
  // IQXNet
  declare "DateRangeStart" date;
  declare "DateRangeEnd" date;
  declare "companycount" integer;
  declare "SecAgencyCode" char(1);
  declare "x" integer;
  declare "y" integer;
  declare "ConsultantFilter" char(2048);
  --declare local temporary table "IDs"(
  --  "ID" char(20) not null,
  --  "CompanyStartDate" date null,
  --  ) not transactional;
  declare local temporary table "DSet"(
    "RowID" char(20) null,
    "StartDate" char(20) null,
    "CombinedTime" char(20) null,
    "IsShift" smallint null default 0,
    "Position" char(50) null,
    "ShiftDescription" char(50) null,
    "CompanyID" char(20) null,
    "CompanyName" char(60) null,
    "ContactID" char(20) null,
    "ContactName" char(60) null,
    "DetailStatus" char(30) null,
    "ListStatus" char(30) null,
    "Department" char(50) null,
    "Consultant" char(60) null,
    "OurRef" char(20) null,
    "ContractRef" char(20) null,
    "ShiftRef" char(20) null,
    "WhenEntered" timestamp null,
    "FilledBy" char(250) null,
    "WhoCancelled" char(60) null,
    "StartTime" char(15) null,
    "FinishTime" char(15) null,
    "FinishDate" char(30) null,
    "Filled" smallint null default 0,
    "Unconfirmed" smallint null default 0,
    "LongDescription" long varchar null,
    "CompanyAddress" char(250) null,
    "PersonID" char(20) null,
    "TextStatus" char(100) null,
    "CancelButton" smallint null default 0,
    "ConfirmButton" smallint null default 0,
    "RejectButton" smallint null default 0,
    "FillButton" smallint null default 0,
    "PONumber" char(50) null,
    "CreatedBy" char(255) null,
    "CancelState" char(50) null,
    "ConfirmedBy" char(60) null,
    "ConfirmState" char(30) null,
    ) not transactional;
  if "IQXNetHasPermission"("pWebUserID",'ORDERJOBS') = 0 then
    return
  end if;
  if "pConsultantFilter" is null then
    set "ConsultantFilter" = (select "list"("staff"."staffid")
        from "staff"
          key join "division" as "d"
        where "defunct" = 0
        and "staff"."divisionid" = any(select "divisionid" from "dashboardstaffdivisions"()))
  else
    set "ConsultantFilter" = "isnull"("pConsultantFilter",(select "staffid" from "iqxnetuser" where "iqxnetuserid" = "pWebUserID"))
  end if;
  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 "companycount" = (select "count"() from "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID");
  set "SecAgencyCode" = 'A';
  set "x" = "pSliceSize";
  set "y" = "x"*"pSlice"+1;
  insert into "Dset" select "v"."VacancyID" as "RowID",
      "v"."startdate" as "StartDate",
      null as "CombinedTime",
      0 as "IsShift",
      "v"."Position" as "Position",
      null as "ShiftDescription",
      "Company"."CompanyID",
      "Company"."Name" as "CompanyName",
      "Person"."PersonID",
      "Person"."Name" as "ContactName",
      (select "Name" from "Status" where "Status"."Status" = "v"."Status" and "Status"."Type" = 'V') as "DetailStatus",
      "DetailStatus" as "ListStatus",
      (select "Name" from "Department" where "Department"."DepartmentID" = "v"."DepartmentID") as "Department",
      (select "Name" from "Staff" where "Staff"."StaffID" = "v"."StaffID") as "Consultant",
      "v"."RefCode" as "OurRef",
      "v"."ContractRef",
      "v"."refcode" as "ShiftRef",
      "v"."WhenEntered",
      null as "FilledBy",
      null as "WhoCancelled",
      null as "StartTime",
      null as "FinishTime",
      "v"."finishdate" as "FinishDate",
      null as "Filled",
      (if "v"."status" = ']' then 1 else 0 endif) as "UnConfirmed",
      "v"."OtherNotes" as "LongDescription",
      "GetCompanyAddressOnLine"("e"."companyid") as "CompanyAddress",
      null as "PersonID",
      (select "Name" from "Status" where "Status"."Status" = "v"."Status" and "Status"."Type" = 'V') as "statusText",
      0 as "CancelButton",
      0 as "ConfirmButton",
      0 as "RejectButton",
      0 as "FillButton",
      "v"."theirref" as "PONumber",
      ('Consultant: '+(select "name" from "staff" where "staffid" = "v"."staffid")+' at: '+"dateformat"("v"."whenentered",'dd/mm/yyyy hh:mm:ss')) as "CreatedBy",
      null as "CancelState",
      null as "ConfirmedBy",
      null as "ConfirmState"
      from "vacancy" as "v"
        key join "Employment" as "e"
        key join("Company","Person")
      where "StartDate" between "DateRangeStart" and "DateRangeEnd"
      and "v"."status" not in( 'D','[' ) 
      and "v"."status" = "isnull"("pStatus","v"."status")
      and "isnull"((select "desktype" from "tempdesk" where "tempdeskid" = "v"."tempdeskid"),'') <> 'S'
      and "charindex"("v"."staffid","ConsultantFilter") > 0 union all
    select "p"."TempShiftPlanID" as "RowID",
      "isnull"("tes"."shiftdate","p"."ShiftDate") 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",
      "Company"."Name" as "CompanyName",
      "Pe"."PersonID" as "ContactID",
      "Pe"."Name" as "ContactName",
      (select(if "Status"."final" = 1 or "ConfirmStatus" is null then "Status"."Name" else "ConfirmStatus" endif) from "Status" where "Status"."Status" = "v"."Status" and "Status"."Type" = 'V') as "DetailStatus",
      (select "Name" from "Status" where "Status"."Status" = "v"."Status" and "Status"."Type" = 'V') as "ListStatus",
      (select "Name" from "Department" where "Department"."DepartmentID" = "v"."DepartmentID") as "Department",
      (select "Name" from "Staff" where "Staff"."StaffID" = "v"."StaffID") as "Consultant",
      "v"."RefCode" as "OurRef",
      "v"."ContractRef",
      cast("p"."ShiftSerialNumber" as char(20)) as "ShiftRef",
      "p"."WhenEntered",
      (select "name" from "person" where "personid" = "tes"."personid")+(if(select "status" from "person" where "personid" = "tes"."personid") = 'A' then ' (S)' endif) as "FilledBy",
      "isnull"((if "tes"."cancelreason" in( 'C','E','G','L','R' ) then '(C)'
      else if "tes"."cancelreason" in( 'P','S','T','W','X' ) then '(T)'
        else '(G)'
        endif
      endif),'') as "WhoCancelled",
      "dateformat"("isnull"("tes"."timefrom","p"."TimeFrom"),'hh:mm') as "StartTime",
      "dateformat"("isnull"("tes"."timeto","p"."TimeTo"),'hh:mm') as "FinishTime",
      (if "FinishTime" < "StartTime" then "dateadd"("dd",1,"StartDate") else "StartDate" endif) as "FinishDate",
      (if "WhoCancelled" = '(G)' then 3 else if "WhoCancelled" = '(T)' then 2 else if "isnull"((select "surname" from "person" where "personid" = "tes"."personid"),'') <> '' then 0 else 1 endif endif endif) as "Filled",
      "isnull"((select first 1 from "tempshift" where "tempshiftplanid" = "p"."tempshiftplanid" and "state" = 'C' and "cancelreason" = '~'),0) as "UnConfirmed",
      cast(null as long varchar) as "LongDescription",
      (if 1 > 1 then "GetCompanyAddressOnLine"("company"."companyid")
      else ''
      endif) as "CompanyAddress",
      "tes"."personid" as "PersonID",
      (if(select "count"() from "contactevent" where "classcode" = 'SS' and "description" = "ShiftRef") = 1 then 'In Progress'
      else(case "tes"."state" when 'B' then 'Confirmed' when 'W' then 'Worked' when 'P' then 'Pending' when 'C' then(select "cr"."name" from "TempShiftCancelReason" as "cr" where "cr"."TempShiftCancelReasonid" = "tes"."cancelreason")+' '+"tes"."note"+' at '+"dateformat"("tes"."whencancelled",'dd/mm/yyyy hh:nn')
        else 'Pending'
        end)
      endif) as "statusText",
      "isnull"((if "tes"."state" = 'W' or "WhoCancelled" = '(C)' or "WhoCancelled" = '(T)' then 0
      else 1
      endif),1) as "CancelButton","isnull"((if "tes"."clientconfirmed" = 0 then 1
      else 0
      endif),0) as "ConfirmButton","isnull"((if("ConfirmButton" = 1 and "tes"."state" <> 'C') then 1
      else 0
      endif),0) as "RejectButton",
      "isnull"((if("tes"."state" = 'P') then 1 else 0 endif),0) as "FillButton",
      "p"."ReferenceCode" as "PONumber",
      ('Ordered by: '+"p"."orderedby"+' at: '+"dateformat"("p"."whenentered",'dd/mm/yyyy hh:mm:ss')) as "CreatedBy",
      "string"("dateformat"("tes"."WhenCancelled",'dd/mm/yyyy hh:nn:ss'),' by ',"isnull"((select "name" from "staff" where "staff"."staffid" = "tes"."WhoCancelled"),'(not recorded)')) as "CancelState",
      "tes"."confirmedwith" as "ConfirmedBy",
      if "isnull"("tes"."clientconfirmed",0) = 1 then 'Client confirmed'
      else if "isnull"("tes"."tempconfirmed",0) = 1 then 'Candidate confirmed'
        else null
        endif
      endif as "ConfirmStatus"
      from "tempshift" as "tes"
        right outer join "TempShiftPlan" as "p"
        left outer join "Vacancy" as "v"
        key join "Employment" as "e"
        key join("Company","Person" as "Pe")
      where "StartDate" between "DateRangeStart" and "DateRangeEnd"
      and "v"."status" = "isnull"("pStatus","v"."status")
      and "charindex"("v"."staffid","ConsultantFilter") > 0;
  select top "x" start at "y"
    "RowID","StartDate","CombinedTime","IsShift","Position","ShiftDescription","CompanyID","CompanyName","ContactID","ContactName","DetailStatus","ListStatus","Department","Consultant","OurRef","ContractRef","ShiftRef","WhenEntered","FilledBy","WhoCAncelled","StartTime","FinishTime","FinishDate","Filled","Unconfirmed","LongDescription","CompanyAddress","PersonID","TextStatus","CancelButton","ConfirmButton","RejectButton","FillButton","PONumber","CreatedBy","CancelState","ConfirmedBy" from "DSet" order by "StartDate" desc
end /* DOC
2017-03-28 PC Create list of Current Requirements for Consultant. SearchString not implemented.
IW-71
2017-07-28 PC modify initial dataset to use vacancy.staffid as well as retaining original company.staffid
2017-12-21 PC modify to delegate division selction to DivisionAccess table and provide post filtering on consultant
2018-05-03 PC IW-482 view requirements from all consultants 
2018-05-07 PC modify method of selecting consultant division list
2018-05-25 PC IW-558 add candidate and client confirmed to shift status
2018-05-25 PC IW-563 change 'Created by' to 'Consultant'
2018-05-29 MHS confirmedwith is a free text field not a personid. Changed case on 'Candidate' for consistency

Tests:

valid pWebUserID,pSlice,pSliceSize,PstartDate,pEndDate where companystartdate greater than today
change desk to shift
valid pWebUserID,pSlice,pSliceSize,PstartDate,pEndDate
valid pWebUserID,pSlice,pSliceSize,PstartDate,pEndDate with ORDERJOBS and companystartdate a long time ago
valid pWebUserID,pSlice,pSliceSize,PstartDate,pEndDate, invalid pStatus
valid pWebUserID,pSlice,pSliceSize,PstartDate,pEndDate, pStatus
*/
/* TEST
call NetTestSetup('');
delete from tagvalue where taglocation='C' and tagid='9X0' and id='TEST';
update vacancy set status='A' where vacancyid='TEST';
update tempdesk set desktype='W' where tempdeskid='test';
update vacancy set startdate=dateformat(now(*),'yyyy-mm-dd'),status='D',tempdeskid='TEST2',staffid='TEST' where vacancyid='TEST';
update iqxnetuser set staffid='TEST' where iqxnetuserid='TEST.CLIENT';
update company set staffid='TEST' where companyid='TEST';
select * from NetOwnerRequirementList(null,null,null,null,null,null);
expect EOF;
delete from iqxnetrightallowed where iqxnetrightid='ORDERJOBS' and IQXNetUserClassID='CLIENT';
select * from NetOwnerRequirementList('test.client',0,10,null,null,null);
expect EOF;
insert into iqxnetrightallowed (iqxnetrightid,iqxnetuserclassid,id,maintainonweb) values ('ORDERJOBS','CLIENT','*',1);
select * from NetOwnerRequirementList('test.client',0,11,null,null,null) order by whenentered desc;
expect CompanyName=test;
select * from NetOwnerRequirementList('test.client',0,12,dateadd(dd,1,now()),null,null) order by whenentered desc;
expect CompanyName=test;
select * from NetOwnerRequirementList('test.client',0,13,null,null,null) order by whenentered desc;
expect CompanyName=test;
update tempdesk set desktype='S' where tempdeskid='test';
select * from NetOwnerRequirementList('test.client',0,14,null,null,null) order by whenentered desc;
expect CompanyName=test;
insert into tagvalue (taglocation,tagid,id,value) values ('C','9X0','TEST',dateadd(yy,1,today()));
select * from NetOwnerRequirementList('test.client',0,15,null,null,null) order by whenentered desc;
expect CompanyName=test;
delete from tagvalue where taglocation='C' and tagid='9X0' and id='TEST';
select * from NetOwnerRequirementList('test.client',0,16,null,null,null) order by whenentered desc;
expect CompanyName=test;
delete from tagvalue where taglocation='C' and tagid='9X0' and id='TEST';
select * from NetOwnerRequirementList('test.client',0,16,null,null,null,'P') order by whenentered desc;
expect EOF;
update vacancy set status='P' where vacancyid='TEST';
select * from NetOwnerRequirementList('test.client',0,16,null,null,null,'P') order by whenentered desc;
expect CompanyName=test;
update vacancy set status=null where vacancyid='TEST';

*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerRequirementList" IS 
{create PROCEDURE pears."NetOwnerRequirementList"( in "pWebUserID" char(20),in "pSlice" integer default 1,in pSliceSize integer default 10,in "pStartDate" char(26) default null,in "pEndDate" char(26) default null,in "SearchString" char(100) default null, in "pStatus" char(1) default null, in pConsultantFilter char(2048) 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),ContactID char(20),ContactName char(60),DetailStatus char(30),ListStatus char(30),Department char(50),"Consultant" char(60),"OurRef" char(20),"ContractRef" char(20),"ShiftRef" char(20),"WhenEntered" timestamp,"FilledBy" char(250),WhoCancelled char(60),StartTime char(15),FinishTime char(15),FinishDate char(30),"Filled" smallint,"Unconfirmed" smallint,"LongDescription" long varchar,"CompanyAddress" char(250),"PersonID" char(20),"TextStatus" char(100),"CancelButton" smallint,"ConfirmButton" smallint,"RejectButton" smallint,"FillButton" smallint,"PONumber" char(50),CreatedBy char(255),CancelState char(50), ConfirmedBy char(60)) 
BEGIN
// IQXNet
  declare DateRangeStart date;
  declare DateRangeEnd date;
  declare companycount integer;
  declare "SecAgencyCode" char(1);
  declare x integer;
  declare y integer;
  declare ConsultantFilter char(2048);

  --declare local temporary table "IDs"(
  --  "ID" char(20) not null,
  --  "CompanyStartDate" date null,
  --  ) not transactional;
  declare local temporary table "DSet"(
    "RowID" char(20) null,
    "StartDate" char(20) null,
    "CombinedTime" char(20) null,
    "IsShift" smallint  null default 0,
    "Position" char(50) null,
    "ShiftDescription" char(50) null,
    "CompanyID" char(20) null,
    CompanyName char(60) null,
    ContactID char(20) null,
    ContactName char(60) null,
    DetailStatus char(30) null,
    ListStatus char(30) null,
    Department char(50) null,
    "Consultant" char(60) null,
    "OurRef" char(20) null,
    "ContractRef" char(20) null,
    "ShiftRef" char(20) null,
    "WhenEntered" timestamp null,
    "FilledBy" char(250) null,
    WhoCancelled char(60) null,
    StartTime char(15) null,
    FinishTime char(15) null,
    FinishDate char(30) null,
    "Filled" smallint null default 0,
    "Unconfirmed" smallint null default 0,
    "LongDescription" long varchar null,
    "CompanyAddress" char(250) null,
    "PersonID" char(20) null,
    "TextStatus" char(100) null,
    "CancelButton" smallint null default 0,
    "ConfirmButton" smallint null default 0,
    "RejectButton" smallint null default 0,
    "FillButton" smallint null default 0,
    "PONumber" char(50) null,
    CreatedBy char(255) null,
    CancelState char(50) null,
    ConfirmedBy char(60) null,
    ConfirmState char(30) null
) not transactional;
  
    if "IQXNetHasPermission"("pWebUserID",'ORDERJOBS') = 0 then
        return
    end if;

    if pConsultantFilter is null then
        set ConsultantFilter=(select list(staff.staffid) 
            from 
                staff
                key join division d
            where
                defunct=0 
                and staff.divisionid in (select divisionid from dashboardstaffdivisions()))
    else
        set ConsultantFilter=isnull(pConsultantFilter,(select staffid from iqxnetuser where iqxnetuserid=pWebUserID));
    end if;
    
    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 companycount = (select "count"() from "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID");
    set "SecAgencyCode" = 'A';
    set x=pSliceSize;
    set y=x*pSlice+1;


    insert into Dset select "v"."VacancyID" as "RowID",
        "v"."startdate" as "StartDate",
        null as "CombinedTime",
        0 as "IsShift",
        "v"."Position" as "Position",
        null as "ShiftDescription",
        "Company"."CompanyID",
        "Company"."Name" as "CompanyName",
        "Person".PersonID,
        "Person"."Name" as "ContactName",
        (select "Name" from "Status" where "Status"."Status" = "v"."Status" and "Status"."Type" = 'V') as "DetailStatus",
        DetailStatus as ListStatus,
        (select "Name" from "Department" where "Department"."DepartmentID" = "v"."DepartmentID") as "Department",
        (select "Name" from "Staff" where "Staff"."StaffID" = "v"."StaffID") as "Consultant",
        "v"."RefCode" as "OurRef",
        "v"."ContractRef",
        "v"."refcode" as "ShiftRef",
        "v"."WhenEntered",
        null as "FilledBy",
        null as "WhoCancelled",
        null as "StartTime",
        null as "FinishTime",
        v.finishdate as "FinishDate",
        null as "Filled",
        (if "v"."status" = ']' then 1 else 0 endif) as "UnConfirmed",
        "v"."OtherNotes" as "LongDescription",
        "GetCompanyAddressOnLine"("e"."companyid") as "CompanyAddress",
        null as "PersonID",
        (select "Name" from "Status" where "Status"."Status" = "v"."Status" and "Status"."Type" = 'V') as "statusText",
        0 as "CancelButton",
        0 as "ConfirmButton",
        0 as "RejectButton",
        0 as "FillButton",
        "v"."theirref" as "PONumber",
       ('Consultant: '+(select "name" from "staff" where "staffid" = "v"."staffid")+' at: '+"dateformat"("v"."whenentered",'dd/mm/yyyy hh:mm:ss')) as "CreatedBy",
        null as "CancelState",
        null as ConfirmedBy,
        null as ConfirmState
        from 
            "vacancy" as "v"
            key join "Employment" as "e" 
            key join("Company","Person")
        where 
            "StartDate" between DateRangeStart and DateRangeEnd
            and v.status not in('D','[')
            and v.status = isnull(pStatus,v.status)
            and "isnull"((select "desktype" from "tempdesk" where "tempdeskid" = "v"."tempdeskid"),'') <> 'S'
            and charindex(v.staffid,ConsultantFilter) > 0
    union all
      select "p"."TempShiftPlanID" as "RowID",
        "isnull"("tes"."shiftdate","p"."ShiftDate") 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",
        "Company"."Name" as "CompanyName",
        "Pe".PersonID as ContactID,
        "Pe"."Name" as "ContactName",
        (select (if Status.final=1 or ConfirmStatus is null then Status."Name" else ConfirmStatus endif)  from "Status" where "Status"."Status" = "v"."Status" and "Status"."Type" = 'V') as DetailStatus,
        (select "Name" from "Status" where "Status"."Status" = "v"."Status" and "Status"."Type" = 'V') as "ListStatus",
        (select "Name" from "Department" where "Department"."DepartmentID" = "v"."DepartmentID") as "Department",
        (select "Name" from "Staff" where "Staff"."StaffID" = "v"."StaffID") as "Consultant",
        "v"."RefCode" as "OurRef",
        "v"."ContractRef",
        cast("p"."ShiftSerialNumber" as char(20)) as "ShiftRef",
        "p"."WhenEntered",
        (select "name" from "person" where "personid" = "tes"."personid")+(if(select "status" from "person" where "personid" = "tes"."personid") = 'A' then ' (S)' endif) as "FilledBy",
        "isnull"((if "tes"."cancelreason" in( 'C','E','G','L','R' ) then '(C)'
        else if "tes"."cancelreason" in( 'P','S','T','W','X' ) then '(T)'
          else '(G)'
          endif
        endif),'') as "WhoCancelled",
        dateformat("isnull"("tes"."timefrom","p"."TimeFrom"),'hh:mm') as "StartTime",
        dateformat("isnull"("tes"."timeto","p"."TimeTo"),'hh:mm') as "FinishTime",
        (if "FinishTime" < "StartTime" then "dateadd"("dd",1,"StartDate") else "StartDate" endif) as "FinishDate",
        (if "WhoCancelled" = '(G)' then 3 else if "WhoCancelled" = '(T)' then 2 else if "isnull"((select "surname" from "person" where "personid" = "tes"."personid"),'') <> '' then 0 else 1 endif endif endif) as "Filled",
        "isnull"((select first 1 from "tempshift" where "tempshiftplanid" = "p"."tempshiftplanid" and "state" = 'C' and "cancelreason" = '~'),0) as "UnConfirmed",
        cast(null as long varchar) as "LongDescription",
        (if 1 > 1 then "GetCompanyAddressOnLine"("company"."companyid")
        else ''
        endif) as "CompanyAddress",
        "tes"."personid" as "PersonID",
        (if(select "count"() from "contactevent" where "classcode" = 'SS' and "description" = "ShiftRef") = 1 then 'In Progress'
        else(case "tes"."state" when 'B' then 'Confirmed' when 'W' then 'Worked' when 'P' then 'Pending' when 'C' then(select "cr"."name" from "TempShiftCancelReason" as "cr" where "cr"."TempShiftCancelReasonid" = "tes"."cancelreason")+' '+"tes"."note"+' at '+"dateformat"("tes"."whencancelled",'dd/mm/yyyy hh:nn')
          else 'Pending'
          end)
        endif) as "statusText",
        "isnull"((if "tes"."state" = 'W' or "WhoCancelled" = '(C)' or "WhoCancelled" = '(T)' then 0
        else 1
        endif),1) as "CancelButton","isnull"((if "tes"."clientconfirmed" = 0 then 1
        else 0
        endif),0) as "ConfirmButton","isnull"((if("ConfirmButton" = 1 and "tes"."state" <> 'C') then 1
        else 0
        endif),0) as "RejectButton",
        "isnull"((if(tes.state = 'P') then 1 else 0 endif),0) as "FillButton",
        "p"."ReferenceCode" as "PONumber",
        ('Ordered by: '+"p"."orderedby"+' at: '+"dateformat"("p"."whenentered",'dd/mm/yyyy hh:mm:ss')) as "CreatedBy",
        "string"("dateformat"("tes"."WhenCancelled",'dd/mm/yyyy hh:nn:ss'),' by ',"isnull"((select "name" from "staff" where "staff"."staffid" = "tes"."WhoCancelled"),'(not recorded)')) as "CancelState",
        tes.confirmedwith as ConfirmedBy,
        if isnull(tes.clientconfirmed,0)=1 then 'Client confirmed' else 
           if isnull(tes.tempconfirmed,0)=1 then 'Candidate confirmed' else     
              null
           endif
        endif as "ConfirmStatus"
        from "tempshift" as "tes"
          right outer join "TempShiftPlan" as "p"
          left outer join "Vacancy" as "v"
          key join "Employment" as "e"
          key join("Company","Person" as "Pe")
       where 
        "StartDate" between DateRangeStart and DateRangeEnd
        and v.status = isnull(pStatus,v.status)
        and charindex(v.staffid,ConsultantFilter) > 0;

    select top x start at y
         "RowID","StartDate","CombinedTime","IsShift","Position","ShiftDescription",CompanyID,CompanyName,ContactID,ContactName,DetailStatus,ListStatus,Department,"Consultant","OurRef","ContractRef","ShiftRef","WhenEntered","FilledBy",WhoCAncelled,StartTime,FinishTime,FinishDate,"Filled","Unconfirmed","LongDescription","CompanyAddress","PersonID","TextStatus","CancelButton","ConfirmButton","RejectButton","FillButton","PONumber",CreatedBy,CancelState,ConfirmedBy from "DSet" order by StartDate desc
END

/* DOC
2017-03-28 PC Create list of Current Requirements for Consultant. SearchString not implemented.
IW-71
2017-07-28 PC modify initial dataset to use vacancy.staffid as well as retaining original company.staffid
2017-12-21 PC modify to delegate division selction to DivisionAccess table and provide post filtering on consultant
2018-05-03 PC IW-482 view requirements from all consultants 
2018-05-07 PC modify method of selecting consultant division list
2018-05-25 PC IW-558 add candidate and client confirmed to shift status
2018-05-25 PC IW-563 change 'Created by' to 'Consultant'
2018-05-29 MHS confirmedwith is a free text field not a personid. Changed case on 'Candidate' for consistency

Tests:

    valid pWebUserID,pSlice,pSliceSize,PstartDate,pEndDate where companystartdate greater than today
    change desk to shift
    valid pWebUserID,pSlice,pSliceSize,PstartDate,pEndDate
    valid pWebUserID,pSlice,pSliceSize,PstartDate,pEndDate with ORDERJOBS and companystartdate a long time ago
    valid pWebUserID,pSlice,pSliceSize,PstartDate,pEndDate, invalid pStatus
    valid pWebUserID,pSlice,pSliceSize,PstartDate,pEndDate, pStatus
*/

/* TEST
call NetTestSetup('');
delete from tagvalue where taglocation='C' and tagid='9X0' and id='TEST';
update vacancy set status='A' where vacancyid='TEST';
update tempdesk set desktype='W' where tempdeskid='test';
update vacancy set startdate=dateformat(now(*),'yyyy-mm-dd'),status='D',tempdeskid='TEST2',staffid='TEST' where vacancyid='TEST';
update iqxnetuser set staffid='TEST' where iqxnetuserid='TEST.CLIENT';
update company set staffid='TEST' where companyid='TEST';
select * from NetOwnerRequirementList(null,null,null,null,null,null);
expect EOF;
delete from iqxnetrightallowed where iqxnetrightid='ORDERJOBS' and IQXNetUserClassID='CLIENT';
select * from NetOwnerRequirementList('test.client',0,10,null,null,null);
expect EOF;
insert into iqxnetrightallowed (iqxnetrightid,iqxnetuserclassid,id,maintainonweb) values ('ORDERJOBS','CLIENT','*',1);
select * from NetOwnerRequirementList('test.client',0,11,null,null,null) order by whenentered desc;
expect CompanyName=test;
select * from NetOwnerRequirementList('test.client',0,12,dateadd(dd,1,now()),null,null) order by whenentered desc;
expect CompanyName=test;
select * from NetOwnerRequirementList('test.client',0,13,null,null,null) order by whenentered desc;
expect CompanyName=test;
update tempdesk set desktype='S' where tempdeskid='test';
select * from NetOwnerRequirementList('test.client',0,14,null,null,null) order by whenentered desc;
expect CompanyName=test;
insert into tagvalue (taglocation,tagid,id,value) values ('C','9X0','TEST',dateadd(yy,1,today()));
select * from NetOwnerRequirementList('test.client',0,15,null,null,null) order by whenentered desc;
expect CompanyName=test;
delete from tagvalue where taglocation='C' and tagid='9X0' and id='TEST';
select * from NetOwnerRequirementList('test.client',0,16,null,null,null) order by whenentered desc;
expect CompanyName=test;
delete from tagvalue where taglocation='C' and tagid='9X0' and id='TEST';
select * from NetOwnerRequirementList('test.client',0,16,null,null,null,'P') order by whenentered desc;
expect EOF;
update vacancy set status='P' where vacancyid='TEST';
select * from NetOwnerRequirementList('test.client',0,16,null,null,null,'P') order by whenentered desc;
expect CompanyName=test;
update vacancy set status=null where vacancyid='TEST';

*/
}
</code>