====== pears.NetOwnerRequirementContracts ======
<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"."NetOwnerRequirementContracts"( 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 "pStatus" char(1) default null,in "pTempdeskID" char(20) default null ) 
result( "RowID" char(20),"StartDate" char(20),"Position" char(50),"CompanyID" char(20),"CompanyName" char(60),"ContactID" char(20),"ContactName" char(60),"DetailStatus" char(30),"OurRef" char(20),"ContractRef" char(20),"ShiftRef" char(20),"FinishDate" char(30),"LongDescription" long varchar,"CompanyAddress" char(250),"TextStatus" char(100),"PONumber" char(50),"CreatedBy" char(255),"DeskType" char(20),"FilledBy" char(100) ) 
begin
  // IQXNet
  declare "DateRangeStart" date;
  declare "DateRangeEnd" date;
  declare "x" integer;
  declare "y" integer;
  declare "DefaultTempdeskID" char(20);
  set "DateRangeStart" = "IQXNetStringToDate"("pStartDate");
  set "DateRangeEnd" = "IQXNetStringToDate"("pEndDate");
  set "DateRangeStart" = "isnull"("DateRangeStart","dateadd"("week",-1,current date));
  set "DateRangeEnd" = "isnull"("DateRangeEnd","dateadd"("week",1,current date));
  set "x" = "pSliceSize";
  set "y" = "x"*"pSlice"+1;
  if not exists(select * from "staff" key join "iqxnetuser" where "iqxnetuserid" = "pWebUserID" and "IQXNetUserClassID" = 'OWNER') then
    return
  end if;
  set "DefaulttempdeskID" = "isnull"("pTempdeskID",(select top 1 "tempdeskid" from "staff" key join "iqxnetuser" where "iqxnetuserid" = "pWebUserID" and "IQXNetUserClassID" = 'OWNER' order by "tempdeskid" asc));
  select top "x" start at "y"
    "v"."VacancyID" as "RowID",
    "v"."startdate" as "StartDate",
    "v"."Position" as "Position",
    "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",
    "v"."RefCode" as "OurRef",
    "v"."ContractRef",
    "v"."refcode" as "ShiftRef",
    "v"."finishdate" as "FinishDate",
    "v"."OtherNotes" as "LongDescription",
    "GetCompanyAddressOnLine"("e"."companyid") as "CompanyAddress",
    (select "Name" from "Status" where "Status"."Status" = "v"."Status" and "Status"."Type" = 'V') as "statusText",
    "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",
    "isnull"("td"."desktype",'') as "DeskType",
    (select "list"("personname") from "NetOwnerRequirementShortListPlaced"("pWebUserID","v"."VacancyID",0)) as "FilledBy"
    from "vacancy" as "v"
      key join "Employment" as "e"
      key join("Company","Person")
      ,"vacancy" as "v"
      key join "tempdesk" as "td"
    where("StartDate" <= "DateRangeEnd" and("FinishDate" is null or "FinishDate" >= "DateRangeStart")
    and "v"."status" not in( 'D','[' ) 
    and "v"."status" = "isnull"("pStatus","v"."status")
    and "DeskType" <> 'S'
    and "v"."tempdeskid" = "DefaulttempdeskID"
    and "isnull"("company"."divisionid",'') = any(select "DivisionID" from "DashboardStaffDivisions"()))
    order by "StartDate" desc
end /* DOC
2018-10-08 PC IW-700 Derived from NetOwnerRequirementList
2018-11-28 IW-759 prioritise selection by tempdesk
2019-7-4 VHS/MHS fixed erroneous date criteria and removed dangerous tests
*/
/* TEST
select * from NetOwnerRequirementContracts('test.owner',0,100,null,null,null,'Z');
expect EOF;
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerRequirementContracts" IS 
{create procedure pears.NetOwnerRequirementContracts( 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 pStatus char(1) default null,in pTempdeskID char(20) default null ) 
result( RowID char(20),StartDate char(20),Position char(50),CompanyID char(20),CompanyName char(60),ContactID char(20),ContactName char(60),DetailStatus char(30),OurRef char(20),ContractRef char(20),ShiftRef char(20),FinishDate char(30),LongDescription long varchar,CompanyAddress char(250),TextStatus char(100),PONumber char(50),CreatedBy char(255),DeskType char(20),FilledBy char(100) ) 
begin
  // IQXNet
  declare DateRangeStart date;
  declare DateRangeEnd date;
  declare x integer;
  declare y integer;
  declare DefaultTempdeskID char(20);
  set DateRangeStart = IQXNetStringToDate(pStartDate);
  set DateRangeEnd = IQXNetStringToDate(pEndDate);
  set DateRangeStart = isnull(DateRangeStart,dateadd(week,-1,current date));
  set DateRangeEnd = isnull(DateRangeEnd,dateadd(week,1,current date));
  set x = pSliceSize;
  set y = x*pSlice+1;
  if not exists(select * from staff key join iqxnetuser where iqxnetuserid = pWebUserID and IQXNetUserClassID = 'OWNER') then
    return
  end if;
  set DefaulttempdeskID = isnull(pTempdeskID,(select top 1 tempdeskid from staff key join iqxnetuser where iqxnetuserid = pWebUserID and IQXNetUserClassID = 'OWNER' order by tempdeskid asc));
  select top x start at y
    v.VacancyID as RowID,
    v.startdate as StartDate,
    v.Position as Position,
    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,
    v.RefCode as OurRef,
    v.ContractRef,
    v.refcode as ShiftRef,
    v.finishdate as FinishDate,
    v.OtherNotes as LongDescription,
    GetCompanyAddressOnLine(e.companyid) as CompanyAddress,
    (select Name from Status where Status.Status = v.Status and Status.Type = 'V') as statusText,
    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,
    isnull(td.desktype,'') as DeskType,
    (select list(personname) from NetOwnerRequirementShortListPlaced(pWebUserID,v.VacancyID,0)) as FilledBy
    from vacancy as v
      key join Employment as e
      key join(Company,Person)
      ,vacancy as v
      key join tempdesk as td
    where (StartDate <= DateRangeEnd and ( FinishDate is null or FinishDate >= DateRangeStart)
    and v.status not in( 'D','[' ) 
    and v.status = isnull(pStatus,v.status)
    and DeskType <> 'S'
    and v.tempdeskid = DefaulttempdeskID
    and isnull(company.divisionid,'') = any(select DivisionID from DashboardStaffDivisions()))
    order by StartDate desc
end /* DOC
2018-10-08 PC IW-700 Derived from NetOwnerRequirementList
2018-11-28 IW-759 prioritise selection by tempdesk
2019-7-4 VHS/MHS fixed erroneous date criteria and removed dangerous tests
*/
/* TEST
select * from NetOwnerRequirementContracts('test.owner',0,100,null,null,null,'Z');
expect EOF;
*/
}
</code>