pears.TempProvTimeSheet

Headers for provisional timesheets.

• TempProvTimeSheetID

-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."TempProvTimeSheet"
-- Table comment: Headers for provisional timesheets.
-- Statement count: 19
 
CREATE TABLE "pears"."TempProvTimeSheet" (
    "TempProvTimeSheetID"            CHAR(20) NOT NULL
   ,"PlacementID"                    CHAR(20) NULL
   ,"PersonID"                       CHAR(20) NULL
   ,"VacancyID"                      CHAR(20) NULL
   ,"TempDeskID"                     CHAR(20) NULL
   ,"Period"                         INTEGER NOT NULL
   ,"PeriodLength"                   CHAR(1) NOT NULL
   ,"CorrectionFlag"                 SMALLINT NULL DEFAULT 0
   ,"Description"                    long VARCHAR NULL
   ,"ExtNumber"                      INTEGER NULL DEFAULT 0
   ,"TransferBatch"                  INTEGER NULL
   ,"TempJobTypeID"                  CHAR(20) NULL
   ,"TheirRef"                       CHAR(50) NULL
   ,"ExternalTimesheetReference"     CHAR(20) NULL
   ,"ExternalInvoiceReference"       CHAR(20) NULL
   ,"SerialNumber"                   CHAR(12) NULL
   ,"ScanPathFilename"               CHAR(250) NULL
   ,"Orientation"                    SMALLINT NULL
   ,"DispatchDate"                   TIMESTAMP NULL
   ,"DispatchMethod"                 CHAR(4) NULL
   ,"ImageBlobID"                    CHAR(20) NULL
   ,"ImageApproved"                  tinyint NOT NULL DEFAULT 0
   ,"WhenEntered"                    TIMESTAMP NULL DEFAULT CURRENT TIMESTAMP
   ,PRIMARY KEY ("TempProvTimeSheetID" ASC) 
   ,CONSTRAINT "InvalidPeriod" CHECK(("PeriodLength" = 'W' AND "Period" REGEXP '^(?:19|20)[0-9][0-9](?:[0-0][1-9]|[1-4][0-9]|5[0-3])$') OR("PeriodLength" = 'M' AND "Period" REGEXP '^(?:19|20)[0-9][0-9](?:[0-0][1-9]|[1][1-2])$'))
)
GO
 
 
COMMENT ON COLUMN "pears"."TempProvTimeSheet"."PeriodLength" IS 
	'Week or Month'
GO
 
 
COMMENT ON COLUMN "pears"."TempProvTimeSheet"."CorrectionFlag" IS 
	'Non zero if this was to correct errors'
GO
 
 
COMMENT ON COLUMN "pears"."TempProvTimeSheet"."ExtNumber" IS 
	'For E-Timesheets'
GO
 
 
COMMENT ON TABLE "pears"."TempProvTimeSheet" IS 
	'Headers for provisional timesheets.'
GO
 
 
ALTER TABLE "pears"."TempProvTimeSheet"
    ADD FOREIGN KEY "Placement" ("PlacementID" ASC)
    REFERENCES "pears"."Placement" ("placementid")
GO
 
 
ALTER TABLE "pears"."TempProvTimeSheet"
    ADD FOREIGN KEY "Person" ("PersonID" ASC)
    REFERENCES "pears"."Person" ("personid")
GO
 
 
ALTER TABLE "pears"."TempProvTimeSheet"
    ADD FOREIGN KEY "Vacancy" ("VacancyID" ASC)
    REFERENCES "pears"."vacancy" ("vacancyid")
GO
 
 
ALTER TABLE "pears"."TempProvTimeSheet"
    ADD FOREIGN KEY "TempDesk" ("TempDeskID" ASC)
    REFERENCES "pears"."tempdesk" ("tempdeskid")
GO
 
 
ALTER TABLE "pears"."TempProvTimeSheet"
    ADD FOREIGN KEY "TempJobType" ("TempJobTypeID" ASC)
    REFERENCES "pears"."TempJobType" ("TempJobTypeID")
    ON DELETE SET NULL
GO
 
 
CREATE INDEX "TempProvTimeSheet_Period" ON "pears"."TempProvTimeSheet"
    ( "Period" )
GO
 
 
CREATE INDEX "TempProvTimeSheet_ExtNumber" ON "pears"."TempProvTimeSheet"
    ( "ExtNumber" )
GO
 
 
CREATE INDEX "TempProvTimeSheet_SerialNumber" ON "pears"."TempProvTimeSheet"
    ( "SerialNumber" )
GO
 
 
CREATE INDEX "TempProvTimeSheet_ImageBlobID" ON "pears"."TempProvTimeSheet"
    ( "ImageBlobID" )
GO
 
 
CREATE INDEX "TempProvTimeSheet_ScanPathFilename" ON "pears"."TempProvTimeSheet"
    ( "ScanPathFilename" )
GO
 
 
CREATE TRIGGER "TempProvTimeSheet_insert" BEFORE INSERT ORDER 1 ON
"pears"."TempProvTimeSheet"
REFERENCING NEW AS "new_ts"
FOR each ROW
BEGIN
  SET "new_ts"."serialnumber" = "trim"("new_ts"."serialnumber");
  SET "new_ts"."theirref" = "trim"("new_ts"."theirref")
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TempProvTimeSheet"."TempProvTimeSheet_insert" IS 
{CREATE TRIGGER TempProvTimeSheet_insert 
 BEFORE INSERT ORDER 1
ON pears.TempProvTimeSheet
REFERENCING NEW AS new_ts
FOR each ROW
BEGIN
  SET new_ts.serialnumber = TRIM(new_ts.serialnumber);
  SET new_ts.theirref = TRIM(new_ts.theirref)
END
}
GO
 
 
CREATE TRIGGER "TempProvTimeSheet_update" BEFORE UPDATE ORDER 1 ON
"pears"."TempProvTimeSheet"
REFERENCING NEW AS "new_ts"
FOR each ROW
BEGIN
  SET "new_ts"."serialnumber" = "trim"("new_ts"."serialnumber");
  SET "new_ts"."theirref" = "trim"("new_ts"."theirref")
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TempProvTimeSheet"."TempProvTimeSheet_update" IS 
{CREATE TRIGGER TempProvTimeSheet_update 
 BEFORE UPDATE ORDER 1
ON pears.TempProvTimeSheet
REFERENCING NEW AS new_ts
FOR each ROW
BEGIN
  SET new_ts.serialnumber = TRIM(new_ts.serialnumber);
  SET new_ts.theirref = TRIM(new_ts.theirref)
END
}
GO