pears.NetReportProvTimesheets

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original SQL

CREATE PROCEDURE "pears"."NetReportProvTimesheets"( 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,"Details__Eat" CHAR(100) ) 
-- 20121003 NetReportTimesheets modified for Current timesheets
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"."NetReportProvTimesheets" IS 
{CREATE PROCEDURE pears."NetReportProvTimesheets"( 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,"Details__Eat" CHAR(100) ) 
-- 20121003 NetReportTimesheets modified for Current timesheets
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
}