pears.TempTimeSheetLine

Time sheet detail lines.

• TempTimeSheetLineID

• No incoming foreign keys found.

• No indexes found.

-- 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