====== pears.NetClientVacancies2 ======
<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"."NetClientVacancies2"( in "pWebUserID" char(20),in "pStartDate" date default null,in "pEndDate" date default null ) 
result( "RowID__H" char(30),"Position" char(200),"Start_Date__D" date,"Start_Time" time,"Finish_Date__D" date,"Finish_Time" time,"VacancyID__H" char(20),"TempShiftPlanID__H" char(20),"Our_Ref" char(20),"Their_Ref" char(50),"Contract_Ref" char(20),"Filled_By" char(250),"IsShift__H" smallint,"Filled__H" smallint,"Unconfirmed__H" smallint,"GroupID__H" smallint ) 
begin
  declare @DateRangeStart date;
  declare @DateRangeEnd date;
  set @DateRangeStart = "isnull"("pStartDate","dateadd"("week",-1,current date));
  set @DateRangeEnd = "isnull"("pEndDate","dateadd"("year",1,current date));
  select 'V'+"v"."VacancyID" as "RowID",
    "v"."Position" as "Position",
    "v"."StartDate",
    cast(null as time) as "StartTime",
    "v"."FinishDate",
    cast(null as time) as "FinishTime",
    "v"."VacancyID",
    cast(null as char(20)) as "TempShiftPlanID",
    "v"."RefCode" as "OurRef",
    "v"."TheirRef",
    "v"."ContractRef",
    (select "list"("person"."name") from "vacancy" key join "placement" key join "employment" key join "person" where "vacancy"."vacancyid" = "v"."vacancyid") as "FilledBy",
    0 as "IsShift",
    "ifnull"("nullif"("FilledBy",''),0,1) as "Filled",
    (if "v"."status" = ']' then 1 else 0 endif) as "UnConfirmed",
    0 as "GroupID" from "Vacancy" as "v" key join "Employment" key join("Company","Person")
    where "Company"."CompanyID" = any(select "companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID")
    and "v"."StartDate" <= @DateRangeEnd and "isnull"("v"."FinishDate",@DateRangeEnd) >= @DateRangeStart and "v"."temp" = 1
    and "v"."Status" in( 'C','F',']' ) and "isnull"((select "desktype" from "tempdesk" where "tempdeskid" = "v"."tempdeskid"),'') <> 'S'
    and "v"."StartDate" >= "IQXNetCompanyStartDate"("Company"."companyid") union all
  select 'P'+"p"."TempShiftPlanID" as "RowID",
    "string"("v"."Position",' ',"p"."Description") as "Position",
    "p"."ShiftDate" as "StartDate",
    "p"."TimeFrom" as "StartTime",
    (if "p"."TimeFrom" < "p"."TimeTo" then "p"."ShiftDate" else "p"."ShiftDate"+1 endif) as "FinishDate",
    "p"."TimeTo" as "FinishTime",
    "v"."VacancyID",
    "p"."TempShiftPlanID",
    cast("p"."ShiftSerialNumber" as char(20)) as "OurRef",
    "p"."ReferenceCode" as "TheirRef",
    "v"."ContractRef",
    (select first "person"."name" from "person" key join "tempshift" where "tempshift"."tempshiftplanid" = "p"."tempshiftplanid" and "tempshift"."state" <> 'C') as "FilledBy",
    1 as "IsShift",
    "ifnull"("FilledBy",0,1) as "Filled",
    "isnull"((select first 1 from "tempshift" where "tempshiftplanid" = "p"."tempshiftplanid" and "state" = 'C' and "cancelreason" = '~'),0) as "UnConfirmed",0 as "GroupID"
    from "TempShiftPlan" as "p" key join "Vacancy" as "v" key join "Employment" key join("Company","Person")
    where "Company"."CompanyID" = any(select "companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID")
    and "p"."ShiftDate" between @DateRangeStart and @DateRangeEnd
    and not exists(select * from "tempshift" where "tempshiftplanid" = "p"."tempshiftplanid" and "state" = 'C' and "cancelreason" <> '~' and "isnull"("crefill",0) = 0)
    and "StartDate" >= "IQXNetCompanyStartDate"("Company"."companyid")
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientVacancies2" IS 
{create procedure pears."NetClientVacancies2"( in "pWebUserID" char(20),in "pStartDate" date default null,in "pEndDate" date default null ) 
result( "RowID__H" char(30),"Position" char(200),"Start_Date__D" date,"Start_Time" time,"Finish_Date__D" date,"Finish_Time" time,"VacancyID__H" char(20),"TempShiftPlanID__H" char(20),"Our_Ref" char(20),"Their_Ref" char(50),"Contract_Ref" char(20),"Filled_By" char(250),"IsShift__H" smallint,"Filled__H" smallint,"Unconfirmed__H" smallint,"GroupID__H" smallint ) 
begin
  declare @DateRangeStart date;
  declare @DateRangeEnd date;
  set @DateRangeStart = "isnull"("pStartDate","dateadd"("week",-1,current date));
  set @DateRangeEnd = "isnull"("pEndDate","dateadd"("year",1,current date));
  select 'V'+"v"."VacancyID" as "RowID",
    "v"."Position" as "Position",
    "v"."StartDate",
    cast(null as time) as "StartTime",
    "v"."FinishDate",
    cast(null as time) as "FinishTime",
    "v"."VacancyID",
    cast(null as char(20)) as "TempShiftPlanID",
    "v"."RefCode" as "OurRef",
    "v"."TheirRef",
    "v"."ContractRef",
    (select "list"("person"."name") from "vacancy" key join "placement" key join "employment" key join "person" where "vacancy"."vacancyid" = "v"."vacancyid") as "FilledBy",
    0 as "IsShift",
    "ifnull"("nullif"("FilledBy",''),0,1) as "Filled",
    (if "v"."status" = ']' then 1 else 0 endif) as "UnConfirmed",
    0 as "GroupID" from "Vacancy" as "v" key join "Employment" key join("Company","Person")
    where "Company"."CompanyID" = any(select "companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID")
    and "v"."StartDate" <= @DateRangeEnd and "isnull"("v"."FinishDate",@DateRangeEnd) >= @DateRangeStart and "v"."temp" = 1
    and "v"."Status" in( 'C','F',']' ) and "isnull"((select "desktype" from "tempdesk" where "tempdeskid" = "v"."tempdeskid"),'') <> 'S'
    and "v"."StartDate" >= "IQXNetCompanyStartDate"("Company"."companyid") union all
  select 'P'+"p"."TempShiftPlanID" as "RowID",
    "string"("v"."Position",' ',"p"."Description") as "Position",
    "p"."ShiftDate" as "StartDate",
    "p"."TimeFrom" as "StartTime",
    (if "p"."TimeFrom" < "p"."TimeTo" then "p"."ShiftDate" else "p"."ShiftDate"+1 endif) as "FinishDate",
    "p"."TimeTo" as "FinishTime",
    "v"."VacancyID",
    "p"."TempShiftPlanID",
    cast("p"."ShiftSerialNumber" as char(20)) as "OurRef",
    "p"."ReferenceCode" as "TheirRef",
    "v"."ContractRef",
    (select first "person"."name" from "person" key join "tempshift" where "tempshift"."tempshiftplanid" = "p"."tempshiftplanid" and "tempshift"."state" <> 'C') as "FilledBy",
    1 as "IsShift",
    "ifnull"("FilledBy",0,1) as "Filled",
    "isnull"((select first 1 from "tempshift" where "tempshiftplanid" = "p"."tempshiftplanid" and "state" = 'C' and "cancelreason" = '~'),0) as "UnConfirmed",0 as "GroupID"
    from "TempShiftPlan" as "p" key join "Vacancy" as "v" key join "Employment" key join("Company","Person")
    where "Company"."CompanyID" = any(select "companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID")
    and "p"."ShiftDate" between @DateRangeStart and @DateRangeEnd
    and not exists(select * from "tempshift" where "tempshiftplanid" = "p"."tempshiftplanid" and "state" = 'C' and "cancelreason" <> '~' and "isnull"("crefill",0) = 0)
    and "StartDate" >= "IQXNetCompanyStartDate"("Company"."companyid")
end
}
</code>