pears.NetClientVacancies [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.NetClientVacancies ======
<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"."NetClientVacancies"( in "pWebUserID" char(20),in "pStartDate" date default null,in "pEndDate" date default null ) 
result( "RowID" char(30),"StartDate" date,"StartTime" time,"FinishTime" time,"FinishDate" date,"IsShift" smallint,"VacancyID" char(20),"TempShiftPlanID" char(20),"Position" char(50),"ShiftDescription" char(50),"CompanyName" char(60),"ContactName" char(30),"Status" char(40),"Department" char(30),"Consultant" char(60),"OurRef" char(20),"TheirRef" char(50),"ContractRef" char(20),"CascadeDateTime" timestamp,"CascadeLevel" smallint,"WhenEntered" timestamp,"FilledBy" char(250),"Filled" smallint,"Unconfirmed" smallint,"LongDescription" long varchar,"CompanyAddress" char(250) ) 
-- modified 20110815 to include shift date and time as well as plan date and time ref ASA
-- modified 20110823 to use temporary table for lookup to improve speed
begin
  declare @DateRangeStart date;
  declare @DateRangeEnd date;
  declare @companycount integer;
  declare local temporary table "IDs"(
    "ID" char(20) not null,
    "CompanyStartDate" date null,
    ) not transactional;
  insert into "IDs"( "ID","CompanyStartDate" ) 
    select distinct "companyid","IQXNetCompanyStartDate"("companyid") from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID";
  set @DateRangeStart = "isnull"("pStartDate","dateadd"("month",-3,current date));
  set @DateRangeEnd = "isnull"("pEndDate","dateadd"("year",1,current date));
  set @companycount = (select "count"() from "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID");
  select 'V'+"v"."VacancyID" as "RowID",
    "v"."StartDate",
    cast(null as time) as "StartTime",
    cast(null as time) as "FinishTime",
    "v"."FinishDate",
    0 as "IsShift",
    "v"."VacancyID",
    cast(null as char(20)) as "TempShiftPlanID",
    "v"."Position" as "Position",
    cast(null as char(50)) as "ShiftDescription",
    "Company"."Name" as "CompanyName",
    "Person"."Name" as "ContactName",
    (select "Name" from "Status" where "Status"."Status" = "v"."Status" and "Status"."Type" = 'V') as "Status",
    (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"."TheirRef",
    "v"."ContractRef",
    "v"."CascadeDateTime",
    "v"."CascadeLevel",
    "v"."WhenEntered",
    (select "list"("person"."name") from "vacancy" key join "placement" key join "employment" key join "person" where "vacancy"."vacancyid" = "v"."vacancyid") as "FilledBy",
    "ifnull"("nullif"("FilledBy",''),0,1) as "Filled",
    (if "v"."status" = ']' then 1 else 0 endif) as "UnConfirmed",
    "v"."OtherNotes" as "LongDescription",
    (if @companycount > 1 then "GetCompanyAddressOnLine"("company"."companyid")
    else ''
    endif) as "CompanyAddress"
    from "Vacancy" as "v" key join("Employment","tempshift" as "t") key join("Company","Person") join "IDs" on "Company"."CompanyID" = "IDs"."ID"
    where "v"."StartDate" <= @DateRangeEnd and "isnull"("v"."FinishDate",@DateRangeEnd) >= @DateRangeStart and "v"."temp" = 1
    and "isnull"((select "desktype" from "tempdesk" where "tempdeskid" = "v"."tempdeskid"),'') <> 'S'
    and "v"."StartDate" >= "IDs"."CompanyStartDate" union all
  select 'P'+"p"."TempShiftPlanID" as "RowID",
    "isnull"("ts"."shiftdate","p"."ShiftDate") as "StartDate",
    "isnull"("ts"."timefrom","p"."TimeFrom") as "StartTime",
    "isnull"("ts"."timeto","p"."TimeTo") as "FinishTime",
    (if "StartTime" < "FinishTime" then "StartDate" else "StartDate"+1 endif) as "FinishDate",
    1 as "IsShift",
    "v"."VacancyID",
    "p"."TempShiftPlanID",
    "v"."Position",
    "p"."Description" as "ShiftDescription",
    "Company"."Name" as "CompanyName",
    "Pe"."Name" as "ContactName",
    (select "Name" from "Status" where "Status"."Status" = "v"."Status" and "Status"."Type" = 'V') as "Status",
    (select "Name" from "Department" where "Department"."DepartmentID" = "v"."DepartmentID") as "Department",
    (select "Name" from "Staff" where "Staff"."StaffID" = "v"."StaffID") as "Consultant",
    cast("p"."ShiftSerialNumber" as char(20)) as "OurRef",
    "p"."ReferenceCode" as "TheirRef",
    "v"."ContractRef",
    "p"."CascadeDateTime",
    "p"."CascadeLevel",
    "p"."WhenEntered",
    (select "name" from "person" where "personid" = "ts"."personid") as "FilledBy",
    "ifnull"("FilledBy",0,1) 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 @companycount > 1 then "GetCompanyAddressOnLine"("company"."companyid")
    else ''
    endif) as "CompanyAddress"
    from "TempShiftPlan" as "p" key join "Vacancy" as "v" key join "Employment" key join("Company","Person" as "pe") join "IDs" on "Company"."CompanyID" = "IDs"."ID"
      ,"TempShiftPlan" as "p" left outer join "tempshift" as "ts" on "ts"."tempshiftplanid" = "p"."tempshiftplanid" and("ts"."state" <> 'C' or "ts"."cancelreason" = '~')
    where "p"."ShiftDate" between @DateRangeStart and @DateRangeEnd
    and "StartDate" >= "IDs"."CompanyStartDate"
    and not exists(select * from "tempshift" where "tempshiftplanid" = "p"."tempshiftplanid" and "state" = 'C' and "isnull"("crefill",0) = 0 and "isnull"("cancelreason",'') <> '~')
    order by 2 asc,3 asc
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientVacancies" IS 
{create procedure pears."NetClientVacancies"( in "pWebUserID" char(20),in "pStartDate" date default null,in "pEndDate" date default null ) 
result( "RowID" char(30),"StartDate" date,"StartTime" time,"FinishTime" time,"FinishDate" date,"IsShift" smallint,"VacancyID" char(20),"TempShiftPlanID" char(20),"Position" char(50),"ShiftDescription" char(50),"CompanyName" char(60),"ContactName" char(30),"Status" char(40),"Department" char(30),"Consultant" char(60),"OurRef" char(20),"TheirRef" char(50),"ContractRef" char(20),"CascadeDateTime" timestamp,"CascadeLevel" smallint,"WhenEntered" timestamp,"FilledBy" char(250),"Filled" smallint,"Unconfirmed" smallint,"LongDescription" long varchar,"CompanyAddress" char(250) ) 
-- modified 20110815 to include shift date and time as well as plan date and time ref ASA
-- modified 20110823 to use temporary table for lookup to improve speed
begin
  declare @DateRangeStart date;
  declare @DateRangeEnd date;
  declare @companycount integer;
  declare local temporary table "IDs"(
    "ID" char(20) not null,
    "CompanyStartDate" date null,
    ) not transactional;
  insert into "IDs"( "ID","CompanyStartDate" ) 
    select distinct "companyid","IQXNetCompanyStartDate"("companyid") from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID";
  set @DateRangeStart = "isnull"("pStartDate","dateadd"("month",-3,current date));
  set @DateRangeEnd = "isnull"("pEndDate","dateadd"("year",1,current date));
  set @companycount = (select "count"() from "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID");
  select 'V'+"v"."VacancyID" as "RowID",
    "v"."StartDate",
    cast(null as time) as "StartTime",
    cast(null as time) as "FinishTime",
    "v"."FinishDate",
    0 as "IsShift",
    "v"."VacancyID",
    cast(null as char(20)) as "TempShiftPlanID",
    "v"."Position" as "Position",
    cast(null as char(50)) as "ShiftDescription",
    "Company"."Name" as "CompanyName",
    "Person"."Name" as "ContactName",
    (select "Name" from "Status" where "Status"."Status" = "v"."Status" and "Status"."Type" = 'V') as "Status",
    (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"."TheirRef",
    "v"."ContractRef",
    "v"."CascadeDateTime",
    "v"."CascadeLevel",
    "v"."WhenEntered",
    (select "list"("person"."name") from "vacancy" key join "placement" key join "employment" key join "person" where "vacancy"."vacancyid" = "v"."vacancyid") as "FilledBy",
    "ifnull"("nullif"("FilledBy",''),0,1) as "Filled",
    (if "v"."status" = ']' then 1 else 0 endif) as "UnConfirmed",
    "v"."OtherNotes" as "LongDescription",
    (if @companycount > 1 then "GetCompanyAddressOnLine"("company"."companyid")
    else ''
    endif) as "CompanyAddress"
    from "Vacancy" as "v" key join("Employment","tempshift" as "t") key join("Company","Person") join "IDs" on "Company"."CompanyID" = "IDs"."ID"
    where "v"."StartDate" <= @DateRangeEnd and "isnull"("v"."FinishDate",@DateRangeEnd) >= @DateRangeStart and "v"."temp" = 1
    and "isnull"((select "desktype" from "tempdesk" where "tempdeskid" = "v"."tempdeskid"),'') <> 'S'
    and "v"."StartDate" >= "IDs"."CompanyStartDate" union all
  select 'P'+"p"."TempShiftPlanID" as "RowID",
    "isnull"("ts"."shiftdate","p"."ShiftDate") as "StartDate",
    "isnull"("ts"."timefrom","p"."TimeFrom") as "StartTime",
    "isnull"("ts"."timeto","p"."TimeTo") as "FinishTime",
    (if "StartTime" < "FinishTime" then "StartDate" else "StartDate"+1 endif) as "FinishDate",
    1 as "IsShift",
    "v"."VacancyID",
    "p"."TempShiftPlanID",
    "v"."Position",
    "p"."Description" as "ShiftDescription",
    "Company"."Name" as "CompanyName",
    "Pe"."Name" as "ContactName",
    (select "Name" from "Status" where "Status"."Status" = "v"."Status" and "Status"."Type" = 'V') as "Status",
    (select "Name" from "Department" where "Department"."DepartmentID" = "v"."DepartmentID") as "Department",
    (select "Name" from "Staff" where "Staff"."StaffID" = "v"."StaffID") as "Consultant",
    cast("p"."ShiftSerialNumber" as char(20)) as "OurRef",
    "p"."ReferenceCode" as "TheirRef",
    "v"."ContractRef",
    "p"."CascadeDateTime",
    "p"."CascadeLevel",
    "p"."WhenEntered",
    (select "name" from "person" where "personid" = "ts"."personid") as "FilledBy",
    "ifnull"("FilledBy",0,1) 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 @companycount > 1 then "GetCompanyAddressOnLine"("company"."companyid")
    else ''
    endif) as "CompanyAddress"
    from "TempShiftPlan" as "p" key join "Vacancy" as "v" key join "Employment" key join("Company","Person" as "pe") join "IDs" on "Company"."CompanyID" = "IDs"."ID"
      ,"TempShiftPlan" as "p" left outer join "tempshift" as "ts" on "ts"."tempshiftplanid" = "p"."tempshiftplanid" and("ts"."state" <> 'C' or "ts"."cancelreason" = '~')
    where "p"."ShiftDate" between @DateRangeStart and @DateRangeEnd
    and "StartDate" >= "IDs"."CompanyStartDate"
    and not exists(select * from "tempshift" where "tempshiftplanid" = "p"."tempshiftplanid" and "state" = 'C' and "isnull"("crefill",0) = 0 and "isnull"("cancelreason",'') <> '~')
    order by 2 asc,3 asc
end
}
</code>