pears.NetReportProvTimesheetsExpanded

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
}