====== pears.NetReportGenevaShiftsAndInvoices ======
<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"."NetReportGenevaShiftsAndInvoices"( in "pWebUserID" char(20),in "From__D" char(20),in "To__D" char(20) ) 
result( "Title__H" char(100),"Shift_Ref__1" char(10),"Date_Requested__2" char(10),"Time_Requested" char(5),"Ordered_By" char(50),"Date_Of_Shift__3" char(10),"Shift_Times" char(20),"Position__4" char(30),"Speciality__5" char(30),"Hospital__6" char(50),"Staff_Name__7" char(50),"Confirmed_With" char(50),"Order_Number" char(20),"Cancelled" char(10),"Invoice_Number__8" char(20) ) 
-- 20120809 RC2 with Shift Ref
begin
  declare "pstart" date;
  declare "pend" date;
  declare "sites" long varchar;
  declare "rTitle" char(100);
  declare local temporary table "Cos"(
    "CompanyID" char(20) null,
    ) not transactional;
  insert into "Cos"( "CompanyID" ) select distinct "companyid" from "employment" key join "iqxnetuserlink"
      where "iqxnetuserid" = "pWebUserID";
  set "pstart" = "iqxnetstringtodate"("From__D");
  set "pend" = "iqxnetstringtodate"("To__D");
  set "rTitle" = "dateformat"("pstart",'dd/mm/yyyy')+' - '+"dateformat"("pend",'dd/mm/yyyy');
  select "rTitle"+' - '+"c"."name",
    "tp"."shiftserialnumber" as "ShiftSerial",
    "dateformat"("t"."whenentered",'dd/mm/yyyy') as "DateRequested",
    "dateformat"("t"."whenentered",'hh:nn') as "TimeRequested",
    "tp"."orderedby" as "OrderedBy",
    "dateformat"("t"."shiftdate",'dd/mm/yyyy') as "DateOfShift",
    cast("dateformat"("t"."timefrom",'hh:nn') as char)+' - '+cast("dateformat"("t"."timeto",'hh:nn') as char) as "ShiftTimes",
    "v"."position" as "Position",
    (select "td"."name" from "tempdesk" as "td" where "td"."tempdeskid" = "v"."tempdeskid") as "Speciality",
    "v"."sitename" as "Hospital",
    "isnull"("p"."name",'Unfilled') as "StaffName",
    "t"."confirmedWith" as "ConfirmedWith",
    "v"."theirref" as "OrderNumber",
    "dateformat"("t"."whencancelled",'dd/mm/yyyy') as "Cancelled",
    "d"."ourref" as "Invoiced"
    from "vacancy" as "v"
      key join "TempShift" as "t"
      key join "person" as "p"
      left outer join "tempshiftplan" as "tp" on "tp"."tempshiftplanid" = "t"."tempshiftplanid"
      left outer join "temptimesheet" as "ts" on "t"."temptimesheetid" = "ts"."temptimesheetid"
      left outer join "iqacdocument" as "d" on "ts"."temptimesheetid" = "d"."xrefid"
      left outer join "staff" as "s" on "s"."staffid" = "t"."staffid"
      left outer join "employment" as "e" on "e"."employmentid" = "v"."employmentid"
      key join "company" as "c"
    where "t"."shiftdate" between "pstart" and "pend"
    and "c"."companyid" = any(select * from "Cos")
    order by "t"."shiftdate" asc,
    "shifttimes" asc,
    "v"."position" asc
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportGenevaShiftsAndInvoices" IS 
{create procedure pears."NetReportGenevaShiftsAndInvoices"( in "pWebUserID" char(20),in "From__D" char(20),in "To__D" char(20) ) 
result( "Title__H" char(100),"Shift_Ref__1" char(10),"Date_Requested__2" char(10),"Time_Requested" char(5),"Ordered_By" char(50),"Date_Of_Shift__3" char(10),"Shift_Times" char(20),"Position__4" char(30),"Speciality__5" char(30),"Hospital__6" char(50),"Staff_Name__7" char(50),"Confirmed_With" char(50),"Order_Number" char(20),"Cancelled" char(10),"Invoice_Number__8" char(20) ) 
-- 20120809 RC2 with Shift Ref
begin
  declare "pstart" date;
  declare "pend" date;
  declare "sites" long varchar;
  declare "rTitle" char(100);
  declare local temporary table "Cos"(
    "CompanyID" char(20) null,
    ) not transactional;
  insert into "Cos"( "CompanyID" ) select distinct "companyid" from "employment" key join "iqxnetuserlink"
      where "iqxnetuserid" = "pWebUserID";
  set "pstart" = "iqxnetstringtodate"("From__D");
  set "pend" = "iqxnetstringtodate"("To__D");
  set "rTitle" = "dateformat"("pstart",'dd/mm/yyyy')+' - '+"dateformat"("pend",'dd/mm/yyyy');
  select "rTitle"+' - '+"c"."name",
    "tp"."shiftserialnumber" as "ShiftSerial",
    "dateformat"("t"."whenentered",'dd/mm/yyyy') as "DateRequested",
    "dateformat"("t"."whenentered",'hh:nn') as "TimeRequested",
    "tp"."orderedby" as "OrderedBy",
    "dateformat"("t"."shiftdate",'dd/mm/yyyy') as "DateOfShift",
    cast("dateformat"("t"."timefrom",'hh:nn') as char)+' - '+cast("dateformat"("t"."timeto",'hh:nn') as char) as "ShiftTimes",
    "v"."position" as "Position",
    (select "td"."name" from "tempdesk" as "td" where "td"."tempdeskid" = "v"."tempdeskid") as "Speciality",
    "v"."sitename" as "Hospital",
    "isnull"("p"."name",'Unfilled') as "StaffName",
    "t"."confirmedWith" as "ConfirmedWith",
    "v"."theirref" as "OrderNumber",
    "dateformat"("t"."whencancelled",'dd/mm/yyyy') as "Cancelled",
    "d"."ourref" as "Invoiced"
    from "vacancy" as "v"
      key join "TempShift" as "t"
      key join "person" as "p"
      left outer join "tempshiftplan" as "tp" on "tp"."tempshiftplanid" = "t"."tempshiftplanid"
      left outer join "temptimesheet" as "ts" on "t"."temptimesheetid" = "ts"."temptimesheetid"
      left outer join "iqacdocument" as "d" on "ts"."temptimesheetid" = "d"."xrefid"
      left outer join "staff" as "s" on "s"."staffid" = "t"."staffid"
      left outer join "employment" as "e" on "e"."employmentid" = "v"."employmentid"
      key join "company" as "c"
    where "t"."shiftdate" between "pstart" and "pend"
    and "c"."companyid" = any(select * from "Cos")
    order by "t"."shiftdate" asc,
    "shifttimes" asc,
    "v"."position" asc
end
}
</code>