====== pears.NetReportProvTimesheetsExpanded ======
<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"."NetReportProvTimesheetsExpanded"( in "pWebUserID" char(20) ) 
result( "Title__H" char(100),"Number__1" char(20),"Temp" char(60),"Position" char(60),"Client__B" char(100),"End_Date__DXA" date,"Total__RMTG" double,"DPay_Type" char(100),"Unit" char(100),"Units__R" double,"Rate__MR" double,"Total__MR" double ) 
-- 20121019 NetReportTimesheets modified to move detail up onto main report
begin
  declare "pstart" date;
  declare "pend" date;
  declare "pStartPeriod" integer;
  declare "pEndPeriod" integer;
  declare "rTitle" char(100);
  select '' as "rTitle",
    "t"."serialnumber",
    "string"("person"."surname",', ',"person"."forenames") as "tempname",
    "vacancy"."position",
    "company"."name" as "companyname",
    "weekmonthenddate"("t"."period","t"."periodlength") as "weekenddate",
    "sum"((select "sum"("unitspaid"*"payrate") from "tempprovtimesheetline" where "tempprovtimesheetid" = "t"."tempprovtimesheetid")) as "timesheettotal",'NetReportProvTimesheetLines&pProvTimesheetID='
    +"t"."tempprovtimesheetid" as "Details"
    from "tempprovtimesheet" as "t"
      key join "vacancy"
      key join "employment"
      key join "company","tempprovtimesheet" as "t"
      key join "tempdesk","tempprovtimesheet" as "t"
      key join "person"
      key join "pay_employee"
      key join "company" as "agcomp"
      key join "employment" as "agemp"
      key join "iqxnetuserlink"
    where "iqxnetuserlink"."iqxnetuserid" = "pWebUserID"
    and "weekenddate" >= current date-60
    and "t"."extnumber" > 0
    group by grouping sets((),("weekenddate"),(("weekenddate"),"rTitle","t"."serialnumber","tempname","vacancy"."position","companyname","Details"))
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportProvTimesheetsExpanded" IS 
{create procedure pears."NetReportProvTimesheetsExpanded"( in "pWebUserID" char(20) ) 
result( "Title__H" char(100),"Number__1" char(20),"Temp" char(60),"Position" char(60),"Client__B" char(100),"End_Date__DXA" date,"Total__RMTG" double,"DPay_Type" char(100),"Unit" char(100),"Units__R" double,"Rate__MR" double,"Total__MR" double ) 
-- 20121019 NetReportTimesheets modified to move detail up onto main report
begin
  declare "pstart" date;
  declare "pend" date;
  declare "pStartPeriod" integer;
  declare "pEndPeriod" integer;
  declare "rTitle" char(100);
  select '' as "rTitle",
    "t"."serialnumber",
    "string"("person"."surname",', ',"person"."forenames") as "tempname",
    "vacancy"."position",
    "company"."name" as "companyname",
    "weekmonthenddate"("t"."period","t"."periodlength") as "weekenddate",
    "sum"((select "sum"("unitspaid"*"payrate") from "tempprovtimesheetline" where "tempprovtimesheetid" = "t"."tempprovtimesheetid")) as "timesheettotal",'NetReportProvTimesheetLines&pProvTimesheetID='
    +"t"."tempprovtimesheetid" as "Details"
    from "tempprovtimesheet" as "t"
      key join "vacancy"
      key join "employment"
      key join "company","tempprovtimesheet" as "t"
      key join "tempdesk","tempprovtimesheet" as "t"
      key join "person"
      key join "pay_employee"
      key join "company" as "agcomp"
      key join "employment" as "agemp"
      key join "iqxnetuserlink"
    where "iqxnetuserlink"."iqxnetuserid" = "pWebUserID"
    and "weekenddate" >= current date-60
    and "t"."extnumber" > 0
    group by grouping sets((),("weekenddate"),(("weekenddate"),"rTitle","t"."serialnumber","tempname","vacancy"."position","companyname","Details"))
end
}
</code>