pears.NetClientProvTimesheets

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

CREATE PROCEDURE "pears"."NetClientProvTimesheets"( IN "pWebUserID" CHAR(20) ) 
RESULT( "tempprovtimesheetid" CHAR(20),"serialnumber" CHAR(20),"tempname" CHAR(60),"position" CHAR(60),"companyname" CHAR(60),"companyaddress" CHAR(120),"weekenddate" DATE,"timesheettype" CHAR(1),"completed" SMALLINT,"theirref" CHAR(100),"theirrefrequired" SMALLINT ) 
BEGIN
  SELECT "t"."tempprovtimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position",
    "company"."name" AS "companyname","getcompanyaddressonline"("company"."companyid") AS "companyaddress",
    "NetTimesheetEndDate"(1,1,"t"."tempprovtimesheetid") AS "weekenddate",
    IF "NetVacancyHasRateScript"("t"."tempjobtypeid","t"."vacancyid") = 0 THEN 'C'
    ELSE IF "tempdesk"."desktype" = 'S' THEN 'S' ELSE IF "tempdesk"."desktype" = 'W'
        AND EXISTS(SELECT * FROM "placement" WHERE "placementid" = "t"."placementid" AND "worknormalhours" IS NOT NULL AND "workstarttime" IS NOT NULL
          AND("isnull"("workmonday",0) = 1 OR "isnull"("worktuesday",0) = 1 OR "isnull"("workwednesday",0) = 1 OR "isnull"("workthursday",0) = 1 OR "isnull"("workfriday",0) = 1
          OR "isnull"("worksaturday",0) = 1 OR "isnull"("worksunday",0) = 1)) THEN 'T'
        ELSE 'C'
        endif endif
    endif AS "timesheettype",
    IF "t"."extnumber" = 2 THEN 1 ELSE 0 endif AS "completed",
    "isnull"("t"."theirref","GetPlacementTheirRef"("person"."personid","vacancy"."vacancyid")) AS "theirref",
    "companyaccount"."theirrefrequired"
    FROM "tempprovtimesheet" AS "t" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company" KEY JOIN "employment" AS "allemps" KEY JOIN "iqxnetuserlink"
      ,"tempprovtimesheet" AS "t" KEY JOIN "tempdesk"
      ,"tempprovtimesheet" AS "t" KEY JOIN "person"
      ,"company" KEY LEFT OUTER JOIN "companyaccount"
    WHERE "iqxnetuserlink"."iqxnetuserid" = "pWebUserID" AND "t"."extnumber" > 0
    AND "weekenddate" > CURRENT date-160
    ORDER BY "weekenddate" ASC,"t"."serialnumber" ASC,"tempname" ASC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientProvTimesheets" IS 
{CREATE PROCEDURE pears."NetClientProvTimesheets"( IN "pWebUserID" CHAR(20) ) 
RESULT( "tempprovtimesheetid" CHAR(20),"serialnumber" CHAR(20),"tempname" CHAR(60),"position" CHAR(60),"companyname" CHAR(60),"companyaddress" CHAR(120),"weekenddate" DATE,"timesheettype" CHAR(1),"completed" SMALLINT,"theirref" CHAR(100),"theirrefrequired" SMALLINT ) 
BEGIN
  SELECT "t"."tempprovtimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position",
    "company"."name" AS "companyname","getcompanyaddressonline"("company"."companyid") AS "companyaddress",
    "NetTimesheetEndDate"(1,1,"t"."tempprovtimesheetid") AS "weekenddate",
    IF "NetVacancyHasRateScript"("t"."tempjobtypeid","t"."vacancyid") = 0 THEN 'C'
    ELSE IF "tempdesk"."desktype" = 'S' THEN 'S' ELSE IF "tempdesk"."desktype" = 'W'
        AND EXISTS(SELECT * FROM "placement" WHERE "placementid" = "t"."placementid" AND "worknormalhours" IS NOT NULL AND "workstarttime" IS NOT NULL
          AND("isnull"("workmonday",0) = 1 OR "isnull"("worktuesday",0) = 1 OR "isnull"("workwednesday",0) = 1 OR "isnull"("workthursday",0) = 1 OR "isnull"("workfriday",0) = 1
          OR "isnull"("worksaturday",0) = 1 OR "isnull"("worksunday",0) = 1)) THEN 'T'
        ELSE 'C'
        endif endif
    endif AS "timesheettype",
    IF "t"."extnumber" = 2 THEN 1 ELSE 0 endif AS "completed",
    "isnull"("t"."theirref","GetPlacementTheirRef"("person"."personid","vacancy"."vacancyid")) AS "theirref",
    "companyaccount"."theirrefrequired"
    FROM "tempprovtimesheet" AS "t" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company" KEY JOIN "employment" AS "allemps" KEY JOIN "iqxnetuserlink"
      ,"tempprovtimesheet" AS "t" KEY JOIN "tempdesk"
      ,"tempprovtimesheet" AS "t" KEY JOIN "person"
      ,"company" KEY LEFT OUTER JOIN "companyaccount"
    WHERE "iqxnetuserlink"."iqxnetuserid" = "pWebUserID" AND "t"."extnumber" > 0
    AND "weekenddate" > CURRENT date-160
    ORDER BY "weekenddate" ASC,"t"."serialnumber" ASC,"tempname" ASC
END
}