pears.TempProvTimeSheet [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.TempProvTimeSheet ======
<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 =====
Headers for provisional timesheets.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **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 |  | Week or Month |
| CorrectionFlag | smallint | NULL | 0 | Non zero if this was to correct errors |
| Description | long varchar | NULL |  |  |
| ExtNumber | integer | NULL | 0 | For E-Timesheets |
| 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 | 0 |  |
| WhenEntered | timestamp | NULL | current timestamp |  |

===== Primary Key =====
  * TempProvTimeSheetID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| Placement | PlacementID | [[database:tables:pears_placement|pears.Placement (placementid)]] |  |
| Person | PersonID | [[database:tables:pears_person|pears.Person (personid)]] |  |
| Vacancy | VacancyID | [[database:tables:pears_vacancy|pears.vacancy (vacancyid)]] |  |
| TempDesk | TempDeskID | [[database:tables:pears_tempdesk|pears.tempdesk (tempdeskid)]] |  |
| TempJobType | TempJobTypeID | [[database:tables:pears_tempjobtype|pears.TempJobType (TempJobTypeID)]] | ON DELETE SET NULL |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_pdfextradocument|pears.PDFExtraDocument]] | TempProvTimesheet | TempProvTimesheetID | TempProvTimeSheetID |
| [[database:tables:pears_tempprovtimesheethistory|pears.TempProvTimeSheetHistory]] | TempProvTimeSheet | TempProvTimeSheetID | TempProvTimeSheetID |
| [[database:tables:pears_tempprovtimesheetline|pears.TempProvTimeSheetLine]] | TempProvTimeSheet | TempProvTimeSheetID | TempProvTimeSheetID |
| [[database:tables:pears_tempprovtimesheetshift|pears.TempProvTimeSheetShift]] | TempProvTimeSheet | TempProvTimeSheetID | TempProvTimeSheetID |
| [[database:tables:pears_timesheetapprovallink|pears.TimesheetApprovalLink]] | TempProvTimeSheet | tempprovtimesheetid | TempProvTimeSheetID |
| [[database:tables:pears_timesheetsignature|pears.TimesheetSignature]] | TempProvTimeSheet | TempProvTimeSheetID | TempProvTimeSheetID |
| [[database:tables:pears_tsquerylog|pears.TSQueryLog]] | TempProvTimeSheet | TempProvTimeSheetID | TempProvTimeSheetID |

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| TempProvTimeSheet_Period | Index | Period |  |
| TempProvTimeSheet_ExtNumber | Index | ExtNumber |  |
| TempProvTimeSheet_SerialNumber | Index | SerialNumber |  |
| TempProvTimeSheet_ImageBlobID | Index | ImageBlobID |  |
| TempProvTimeSheet_ScanPathFilename | Index | ScanPathFilename |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| TempProvTimeSheet_insert | before | insert order 1 |
| TempProvTimeSheet_update | before | update order 1 |

===== Original SQL =====
<code sql>
-- 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
</code>