====== pears.TempTimeSheetLine ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Description =====
Time sheet detail lines.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **TempTimeSheetLineID** | char(20) | NOT NULL |  |  |
| TempTimeSheetID | char(20) | NOT NULL |  |  |
| TempPayBandID | char(20) | NOT NULL |  |  |
| LineNumber | smallint | NOT NULL |  |  |
| UnitsWorked | double | NULL |  |  |
| UnitsCharged | double | NULL | 0 |  |
| PayRate | double | NULL | 0 |  |
| ChargeRate | double | NULL | 0 |  |
| UnitsPaid | double | NULL | 0 |  |
| Description | long varchar | NULL |  |  |
| TempShiftID | char(20) | NULL |  |  |
| ShiftTime | timestamp | NULL |  | Where timesheets are itemised by shift or part of shift |
| Grade | char(4) | NULL |  |  |
| Filter1 | char(4) | NULL |  |  |
| Filter2 | char(4) | NULL |  |  |
| Filter3 | char(4) | NULL |  |  |
| AWRJobMasterID | char(20) | NULL |  |  |

===== Primary Key =====
  * TempTimeSheetLineID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| TempTimeSheet | TempTimeSheetID | [[database:tables:pears_temptimesheet|pears.TempTimeSheet (TempTimeSheetID)]] | NOT NULL;  |
| TempPayBand | TempPayBandID | [[database:tables:pears_temppayband|pears.TempPayBand (TempPayBandID)]] | NOT NULL;  |
| TempShift | TempShiftID | [[database:tables:pears_tempshift|pears.TempShift (TempShiftID)]] | ON DELETE SET NULL |
| AWRJobMaster | AWRJobMasterID | [[database:tables:pears_awrjobmaster|pears.AWRJobMaster (AWRJobMasterID)]] | ON DELETE SET NULL |

===== Referenced By =====
  * No incoming foreign keys found.

===== Indexes =====
  * No indexes found.

===== Triggers =====
^ Name ^ Timing ^ Event ^
| TimesheetLine_Insert | before | insert order 1 |
| TimesheetLine_Audit | after | update of "UnitsPaid", "UnitsCharged","PayRate", "ChargeRate" order 2 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."TempTimeSheetLine"
-- Table comment: Time sheet detail lines.
-- Statement count: 12

CREATE TABLE "pears"."TempTimeSheetLine" (
    "TempTimeSheetLineID"            char(20) NOT NULL
   ,"TempTimeSheetID"                char(20) NOT NULL
   ,"TempPayBandID"                  char(20) NOT NULL
   ,"LineNumber"                     smallint NOT NULL
   ,"UnitsWorked"                    double NULL
   ,"UnitsCharged"                   double NULL DEFAULT 0
   ,"PayRate"                        double NULL DEFAULT 0
   ,"ChargeRate"                     double NULL DEFAULT 0
   ,"UnitsPaid"                      double NULL DEFAULT 0
   ,"Description"                    long varchar NULL
   ,"TempShiftID"                    char(20) NULL
   ,"ShiftTime"                      timestamp NULL
   ,"Grade"                          char(4) NULL
   ,"Filter1"                        char(4) NULL
   ,"Filter2"                        char(4) NULL
   ,"Filter3"                        char(4) NULL
   ,"AWRJobMasterID"                 char(20) NULL
   ,PRIMARY KEY ("TempTimeSheetLineID" ASC) 
)
go


COMMENT ON COLUMN "pears"."TempTimeSheetLine"."ShiftTime" IS 
	'Where timesheets are itemised by shift or part of shift'
go


ALTER TABLE "pears"."TempTimeSheetLine"
    ADD CONSTRAINT "IQXTSUniqueLine" UNIQUE ( "TempTimeSheetID","LineNumber" )
go


COMMENT ON TABLE "pears"."TempTimeSheetLine" IS 
	'Time sheet detail lines.'
go


ALTER TABLE "pears"."TempTimeSheetLine"
    ADD NOT NULL FOREIGN KEY "TempTimeSheet" ("TempTimeSheetID" ASC)
    REFERENCES "pears"."TempTimeSheet" ("TempTimeSheetID")
go


ALTER TABLE "pears"."TempTimeSheetLine"
    ADD NOT NULL FOREIGN KEY "TempPayBand" ("TempPayBandID" ASC)
    REFERENCES "pears"."TempPayBand" ("TempPayBandID")
go


ALTER TABLE "pears"."TempTimeSheetLine"
    ADD FOREIGN KEY "TempShift" ("TempShiftID" ASC)
    REFERENCES "pears"."TempShift" ("TempShiftID")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."TempTimeSheetLine"
    ADD FOREIGN KEY "AWRJobMaster" ("AWRJobMasterID" ASC)
    REFERENCES "pears"."AWRJobMaster" ("AWRJobMasterID")
    ON DELETE SET NULL
go


create trigger "TimesheetLine_Insert" before insert order 1 on
"pears"."TempTimeSheetLine"
referencing new as "new_line"
for each row
when("new_line"."linenumber" is null)
begin
  set "new_line"."linenumber" = "isnull"((select "max"("linenumber") from "temptimesheetline" where "temptimesheetid" = "new_line"."temptimesheetid"),0)+1
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TempTimeSheetLine"."TimesheetLine_Insert" IS 
{create trigger TimesheetLine_Insert 
 before insert order 1 on
pears.TempTimeSheetLine
referencing new as new_line
for each row
when(new_line.linenumber is null)
begin
  set new_line.linenumber=isnull((select max(linenumber) from temptimesheetline where temptimesheetid = new_line.temptimesheetid),0)+1
end
}
go


create trigger "TimesheetLine_Audit" after update of "UnitsPaid",
"UnitsCharged","PayRate",
"ChargeRate" order 2 on "pears"."TempTimesheetLine"
referencing old as "OldTSL" new as "NewTSL"
for each row
begin
  declare "SerialNo" char(20);
  select "SerialNumber" into "SerialNo" from "temptimesheet" where "temptimesheetid" = "NewTSL"."TempTimesheetID";
  if update("UnitsPaid") then
    call "AuditLog"('TIMESHEET',"NewTSL"."TempTimesheetLineID",'Timesheet ' || "SerialNo" || ' Units Paid edited Line: ' || "NewTSL"."LineNumber",
    "OldTSL"."UnitsPaid",
    "NewTSL"."UnitsPaid")
  end if;
  if update("UnitsCharged") then
    call "AuditLog"('TIMESHEET',"NewTSL"."TempTimesheetLineID",'Timesheet ' || "SerialNo" || ' Units Charged edited Line: ' || "NewTSL"."LineNumber",
    "OldTSL"."UnitsCharged",
    "NewTSL"."UnitsCharged")
  end if;
  if update("PayRate") then
    call "AuditLog"('TIMESHEET',"NewTSL"."TempTimesheetLineID",'Timesheet ' || "SerialNo" || ' Pay Rate edited Line: ' || "NewTSL"."LineNumber",
    "OldTSL"."PayRate",
    "NewTSL"."PayRate")
  end if;
  if update("ChargeRate") then
    call "AuditLog"('TIMESHEET',"NewTSL"."TempTimesheetLineID",'Timesheet ' || "SerialNo" || ' Charge Rate edited Line: ' || "NewTSL"."LineNumber",
    "OldTSL"."ChargeRate",
    "NewTSL"."ChargeRate")
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TempTimeSheetLine"."TimesheetLine_Audit" IS 
{create trigger TimesheetLine_Audit 
 after update of UnitsPaid,
UnitsCharged,PayRate,
ChargeRate order 2 on pears.TempTimesheetLine
referencing old as OldTSL new as NewTSL
for each row
begin
  declare SerialNo char(20);
  select SerialNumber into SerialNo from temptimesheet where temptimesheetid = NewTSL.TempTimesheetID; 
      if update(UnitsPaid) then
        call AuditLog('TIMESHEET',NewTSL.TempTimesheetLineID,'Timesheet '||SerialNo||' Units Paid edited Line: ' || NewTSL.LineNumber,
        OldTSL.UnitsPaid,
        NewTSL.UnitsPaid)
      end if;
      if update(UnitsCharged) then
        call AuditLog('TIMESHEET',NewTSL.TempTimesheetLineID,'Timesheet '||SerialNo||' Units Charged edited Line: ' || NewTSL.LineNumber,
        OldTSL.UnitsCharged,
        NewTSL.UnitsCharged)
      end if;
      if update(PayRate) then
        call AuditLog('TIMESHEET',NewTSL.TempTimesheetLineID,'Timesheet '||SerialNo||' Pay Rate edited Line: ' || NewTSL.LineNumber,
        OldTSL.PayRate,
        NewTSL.PayRate)
      end if;
      if update(ChargeRate) then
        call AuditLog('TIMESHEET',NewTSL.TempTimesheetLineID,'Timesheet '||SerialNo||' Charge Rate edited Line: ' || NewTSL.LineNumber,
        OldTSL.ChargeRate,
        NewTSL.ChargeRate)
      end if
end
}
go
</code>