pears.tempdesk [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.tempdesk ======
<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 =====
Temp Desk settings.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **tempdeskid** | char(20) | NOT NULL |  |  |
| name | char(30) | NULL |  |  |
| departmentid | char(2) | NOT NULL |  |  |
| currentyearnumber | smallint | NULL |  |  |
| currentweeknumber | smallint | NULL |  |  |
| defpayrollcoid | char(1) | NULL |  |  |
| defanalysiscode | char(20) | NULL |  |  |
| desktype | char(1) | NULL | 'W' | W:Weekly Contract M:Monthly Contract S:Weekly Shift |
| timesheetperiod | char(1) | NULL | 'W' | W:Weekly M:Monthly |
| defprovisionaltimesheets | smallint | NULL | 0 |  |
| divisionid | char(20) | NULL |  |  |
| ExtraCode1 | char(20) | NULL |  |  |
| ExtraCode2 | char(20) | NULL |  |  |
| ExtraCode3 | char(20) | NULL |  |  |
| ExtraCode4 | char(20) | NULL |  |  |
| AllowCopyShifts | smallint | NULL | 0 | Should not be enabled if the Plan is the normal way of working, since the results will be unpredictable |
| UseFastShiftMatching | smallint | NULL | 0 | Fast but no interactive reporting or interruption |
| MatcherLastPageJump | smallint | NULL | 0 | Goes straight to last page of shift matcher |
| MatcherFilterByDefault | smallint | NULL | 0 | Makes the search criteria view on the shift matcher appear by default |
| ShiftListNotDiary | smallint | NULL | 0 | Controls the default vacancy view when opening vacancies in shift context |
| GridStyleShiftAdder | smallint | NULL | 0 | Bulk adding shifts defaults to the new grid style form |
| ShiftsOnContractTimesheet | smallint | NULL | 0 | Shift tab is normally hidden on contract timesheets |
| NumberProvTimesheets | smallint | NULL | 0 | Numbers provisional timesheets even if not E-timesheets |
| ExpenseBenefit | smallint | NULL | 0 | Enables the expense benefit mechanism |
| HoursOnContractTimesheet | smallint | NULL | 0 | Hours tab is normally hidden on contract timesheets |
| MaxPlacementDays | integer | NULL |  | If null no limit |
| AdjustPlanOfCancelledShifts | smallint | NULL | 0 | If set, shift plan times are changed by trigger to match (adjusted) shift times |
| CountShiftsOnVacancyView | smallint | NULL | 0 | New shift count display in day cells of vacancy view on shift temp desk |
| CreateTSFromPlacement | smallint | NULL | 0 | Create timesheets from placements on shift temp desk |
| EnforceShiftPlacementLink | smallint | NULL | 0 | Shifts can only be timesheeted if within the date range of a pre-entered placement |
| AllowCopyTimesheet | smallint | NULL | 0 | Should not be enabled if the Plan is the normal way of working, since the results will be unpredictable |
| Defunct | smallint | NULL |  | Not allowed for new, moves to end of lists |
| ShowTimesheetLineDate | smallint | NULL | 0 | Show pseudo shift date on timesheet entry and completed |
| ShiftMatchFromCurrentDate | smallint | NULL | 0 | Set to change from default of tempdesk week start |
| ShiftMatchToShortList | smallint | NULL | 0 | Allow shift matcher to place people on a short list |
| ShiftMatchToShortListStatus | char(1) | NULL |  | Short list status used for auto-shortlisting |
| ShiftMatchShowSkillColumn | smallint | NULL | 0 | Show skills and grade in a separate column on shift matcher |
| ShowSiteName | smallint | NULL | 0 | Show site name on temp desk views |
| FillShiftAnyStateTemp | smallint | NULL | 0 | Specified Individual on Shift match can be in any State |
| ShiftFillerHideDuplicates | smallint | NULL | 0 | Hide Duplicate Shifts ticked by default |
| AutoCompleteETimesheet | smallint | NULL | 0 | E-Timesheets with this extnumber can be autocompleted |
| ShiftTemplateRequired | smallint | NULL | 0 | Shifts cannot be entered without a template |
| AllowExplicitShiftPlacementID | smallint | NULL | 0 | Shifts can be linked to a placement pre-timesheet for special rates etc. |
| SelfBillStartPeriod | integer | NULL |  | If null no self bill |
| ExtraCode5 | char(100) | NULL |  |  |
| ExtraCode6 | char(100) | NULL |  |  |
| ExtraCode7 | char(100) | NULL |  |  |
| ExtraCode8 | char(100) | NULL |  |  |
| ExtraCode9 | char(100) | NULL |  |  |
| ExtraCode10 | char(100) | NULL |  |  |
| ExtraCode11 | char(100) | NULL |  |  |
| ExtraCode12 | char(100) | NULL |  |  |
| AutoMatchOn | tinyint | NULL | 0 | Automatch off = 0 on = 1 |
| AllowBulkShiftAddingTheirRef | tinyint | NULL | 1 |  |
| IQXNetEmailDetailsID | char(20) | NULL |  |  |
| PostcodeMiles | smallint | NULL | 0 | Default Postcode Mileage |
| ShowExpiryDeptQuestions | tinyint | NULL | 1 |  |
| EnforceMarginCheck | tinyint | NULL | 0 |  |
| MatchExactGradeOnly | tinyint | NULL | 0 |  |
| DaylightSavingAdjustment | tinyint | NULL | 0 |  |
| NotifyClientConfirmOneorAll | tinyint | NULL | 0 |  |

===== Primary Key =====
  * tempdeskid

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| department | departmentid | [[database:tables:pears_department|pears.Department (departmentid)]] | NOT NULL;  |
| division | divisionid | [[database:tables:pears_division|pears.Division (divisionid)]] | ON DELETE SET NULL |
| IQXNetEmailDetails | IQXNetEmailDetailsID | [[database:tables:pears_iqxnetemaildetails|pears.IQXNetEmailDetails (IQXNetEmailDetailsID)]] | ON DELETE SET NULL |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_ebtimesheet|pears.EBTimeSheet]] | TempDesk | TempDeskID | tempdeskid |
| [[database:tables:pears_staff|pears.staff]] | tempdesk | tempdeskid | tempdeskid |
| [[database:tables:pears_staffexttempdeskaccess|pears.StaffExtTempdeskAccess]] | tempdesk | TempdeskID | tempdeskid |
| [[database:tables:pears_tempdeskaccess|pears.TempdeskAccess]] | tempdesk | tempdeskid | tempdeskid |
| [[database:tables:pears_tempdeskagencypoolmember|pears.TempDeskAgencyPoolMember]] | TempDesk | TempDeskID | tempdeskid |
| [[database:tables:pears_temppoolmember|pears.TempPoolMember]] | TempDesk | TempDeskID | tempdeskid |
| [[database:tables:pears_tempprovtimesheet|pears.TempProvTimeSheet]] | TempDesk | TempDeskID | tempdeskid |
| [[database:tables:pears_temptimesheet|pears.TempTimeSheet]] | TempDesk | TempDeskID | tempdeskid |
| [[database:tables:pears_tsquerylog|pears.TSQueryLog]] | TempDesk | TempDeskID | tempdeskid |
| [[database:tables:pears_vacancy|pears.vacancy]] | TempDesk | TempDeskID | tempdeskid |

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

===== Triggers =====
^ Name ^ Timing ^ Event ^
| WPK_tempdesk_TEMPDESK | after | insert,delete,update order 1 |
| TempdeskDelete | before | delete order 2 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."tempdesk"
-- Table comment: Temp Desk settings.
-- Statement count: 42

CREATE TABLE "pears"."tempdesk" (
    "tempdeskid"                     char(20) NOT NULL
   ,"name"                           char(30) NULL
   ,"departmentid"                   char(2) NOT NULL
   ,"currentyearnumber"              smallint NULL
   ,"currentweeknumber"              smallint NULL
   ,"defpayrollcoid"                 char(1) NULL
   ,"defanalysiscode"                char(20) NULL
   ,"desktype"                       char(1) NULL DEFAULT 'W'
   ,"timesheetperiod"                char(1) NULL DEFAULT 'W'
   ,"defprovisionaltimesheets"       smallint NULL DEFAULT 0
   ,"divisionid"                     char(20) NULL
   ,"ExtraCode1"                     char(20) NULL
   ,"ExtraCode2"                     char(20) NULL
   ,"ExtraCode3"                     char(20) NULL
   ,"ExtraCode4"                     char(20) NULL
   ,"AllowCopyShifts"                smallint NULL DEFAULT 0
   ,"UseFastShiftMatching"           smallint NULL DEFAULT 0
   ,"MatcherLastPageJump"            smallint NULL DEFAULT 0
   ,"MatcherFilterByDefault"         smallint NULL DEFAULT 0
   ,"ShiftListNotDiary"              smallint NULL DEFAULT 0
   ,"GridStyleShiftAdder"            smallint NULL DEFAULT 0
   ,"ShiftsOnContractTimesheet"      smallint NULL DEFAULT 0
   ,"NumberProvTimesheets"           smallint NULL DEFAULT 0
   ,"ExpenseBenefit"                 smallint NULL DEFAULT 0
   ,"HoursOnContractTimesheet"       smallint NULL DEFAULT 0
   ,"MaxPlacementDays"               integer NULL
   ,"AdjustPlanOfCancelledShifts"    smallint NULL DEFAULT 0
   ,"CountShiftsOnVacancyView"       smallint NULL DEFAULT 0
   ,"CreateTSFromPlacement"          smallint NULL DEFAULT 0
   ,"EnforceShiftPlacementLink"      smallint NULL DEFAULT 0
   ,"AllowCopyTimesheet"             smallint NULL DEFAULT 0
   ,"Defunct"                        smallint NULL
   ,"ShowTimesheetLineDate"          smallint NULL DEFAULT 0
   ,"ShiftMatchFromCurrentDate"      smallint NULL DEFAULT 0
   ,"ShiftMatchToShortList"          smallint NULL DEFAULT 0
   ,"ShiftMatchToShortListStatus"    char(1) NULL
   ,"ShiftMatchShowSkillColumn"      smallint NULL DEFAULT 0
   ,"ShowSiteName"                   smallint NULL DEFAULT 0
   ,"FillShiftAnyStateTemp"          smallint NULL DEFAULT 0
   ,"ShiftFillerHideDuplicates"      smallint NULL DEFAULT 0
   ,"AutoCompleteETimesheet"         smallint NULL DEFAULT 0
   ,"ShiftTemplateRequired"          smallint NULL DEFAULT 0
   ,"AllowExplicitShiftPlacementID"  smallint NULL DEFAULT 0
   ,"SelfBillStartPeriod"            integer NULL
   ,"ExtraCode5"                     char(100) NULL
   ,"ExtraCode6"                     char(100) NULL
   ,"ExtraCode7"                     char(100) NULL
   ,"ExtraCode8"                     char(100) NULL
   ,"ExtraCode9"                     char(100) NULL
   ,"ExtraCode10"                    char(100) NULL
   ,"ExtraCode11"                    char(100) NULL
   ,"ExtraCode12"                    char(100) NULL
   ,"AutoMatchOn"                    tinyint NULL DEFAULT 0
   ,"AllowBulkShiftAddingTheirRef"   tinyint NULL DEFAULT 1
   ,"IQXNetEmailDetailsID"           char(20) NULL
   ,"PostcodeMiles"                  smallint NULL DEFAULT 0
   ,"ShowExpiryDeptQuestions"        tinyint NULL DEFAULT 1
   ,"EnforceMarginCheck"             tinyint NULL DEFAULT 0
   ,"MatchExactGradeOnly"            tinyint NULL DEFAULT 0
   ,"DaylightSavingAdjustment"       tinyint NULL DEFAULT 0
   ,"NotifyClientConfirmOneorAll"    tinyint NULL DEFAULT 0
   ,PRIMARY KEY ("tempdeskid" ASC) 
)
go


COMMENT ON COLUMN "pears"."tempdesk"."desktype" IS 
	'W:Weekly Contract M:Monthly Contract S:Weekly Shift'
go


COMMENT ON COLUMN "pears"."tempdesk"."timesheetperiod" IS 
	'W:Weekly M:Monthly'
go


COMMENT ON COLUMN "pears"."tempdesk"."AllowCopyShifts" IS 
	'Should not be enabled if the Plan is the normal way of working, since the results will be unpredictable'
go


COMMENT ON COLUMN "pears"."tempdesk"."UseFastShiftMatching" IS 
	'Fast but no interactive reporting or interruption'
go


COMMENT ON COLUMN "pears"."tempdesk"."MatcherLastPageJump" IS 
	'Goes straight to last page of shift matcher'
go


COMMENT ON COLUMN "pears"."tempdesk"."MatcherFilterByDefault" IS 
	'Makes the search criteria view on the shift matcher appear by default'
go


COMMENT ON COLUMN "pears"."tempdesk"."ShiftListNotDiary" IS 
	'Controls the default vacancy view when opening vacancies in shift context'
go


COMMENT ON COLUMN "pears"."tempdesk"."GridStyleShiftAdder" IS 
	'Bulk adding shifts defaults to the new grid style form'
go


COMMENT ON COLUMN "pears"."tempdesk"."ShiftsOnContractTimesheet" IS 
	'Shift tab is normally hidden on contract timesheets'
go


COMMENT ON COLUMN "pears"."tempdesk"."NumberProvTimesheets" IS 
	'Numbers provisional timesheets even if not E-timesheets'
go


COMMENT ON COLUMN "pears"."tempdesk"."ExpenseBenefit" IS 
	'Enables the expense benefit mechanism'
go


COMMENT ON COLUMN "pears"."tempdesk"."HoursOnContractTimesheet" IS 
	'Hours tab is normally hidden on contract timesheets'
go


COMMENT ON COLUMN "pears"."tempdesk"."MaxPlacementDays" IS 
	'If null no limit'
go


COMMENT ON COLUMN "pears"."tempdesk"."AdjustPlanOfCancelledShifts" IS 
	'If set, shift plan times are changed by trigger to match (adjusted) shift times'
go


COMMENT ON COLUMN "pears"."tempdesk"."CountShiftsOnVacancyView" IS 
	'New shift count display in day cells of vacancy view on shift temp desk'
go


COMMENT ON COLUMN "pears"."tempdesk"."CreateTSFromPlacement" IS 
	'Create timesheets from placements on shift temp desk'
go


COMMENT ON COLUMN "pears"."tempdesk"."EnforceShiftPlacementLink" IS 
	'Shifts can only be timesheeted if within the date range of a pre-entered placement'
go


COMMENT ON COLUMN "pears"."tempdesk"."AllowCopyTimesheet" IS 
	'Should not be enabled if the Plan is the normal way of working, since the results will be unpredictable'
go


COMMENT ON COLUMN "pears"."tempdesk"."Defunct" IS 
	'Not allowed for new, moves to end of lists'
go


COMMENT ON COLUMN "pears"."tempdesk"."ShowTimesheetLineDate" IS 
	'Show pseudo shift date on timesheet entry and completed'
go


COMMENT ON COLUMN "pears"."tempdesk"."ShiftMatchFromCurrentDate" IS 
	'Set to change from default of tempdesk week start'
go


COMMENT ON COLUMN "pears"."tempdesk"."ShiftMatchToShortList" IS 
	'Allow shift matcher to place people on a short list'
go


COMMENT ON COLUMN "pears"."tempdesk"."ShiftMatchToShortListStatus" IS 
	'Short list status used for auto-shortlisting'
go


COMMENT ON COLUMN "pears"."tempdesk"."ShiftMatchShowSkillColumn" IS 
	'Show skills and grade in a separate column on shift matcher'
go


COMMENT ON COLUMN "pears"."tempdesk"."ShowSiteName" IS 
	'Show site name on temp desk views'
go


COMMENT ON COLUMN "pears"."tempdesk"."FillShiftAnyStateTemp" IS 
	'Specified Individual on Shift match can be in any State'
go


COMMENT ON COLUMN "pears"."tempdesk"."ShiftFillerHideDuplicates" IS 
	'Hide Duplicate Shifts ticked by default'
go


COMMENT ON COLUMN "pears"."tempdesk"."AutoCompleteETimesheet" IS 
	'E-Timesheets with this extnumber can be autocompleted'
go


COMMENT ON COLUMN "pears"."tempdesk"."ShiftTemplateRequired" IS 
	'Shifts cannot be entered without a template'
go


COMMENT ON COLUMN "pears"."tempdesk"."AllowExplicitShiftPlacementID" IS 
	'Shifts can be linked to a placement pre-timesheet for special rates etc.'
go


COMMENT ON COLUMN "pears"."tempdesk"."SelfBillStartPeriod" IS 
	'If null no self bill'
go


COMMENT ON COLUMN "pears"."tempdesk"."AutoMatchOn" IS 
	'Automatch off = 0 on = 1'
go


COMMENT ON COLUMN "pears"."tempdesk"."PostcodeMiles" IS 
	'Default Postcode Mileage'
go


COMMENT ON TABLE "pears"."tempdesk" IS 
	'Temp Desk settings.'
go


ALTER TABLE "pears"."tempdesk"
    ADD NOT NULL FOREIGN KEY "department" ("departmentid" ASC)
    REFERENCES "pears"."Department" ("departmentid")
go


ALTER TABLE "pears"."tempdesk"
    ADD FOREIGN KEY "division" ("divisionid" ASC)
    REFERENCES "pears"."Division" ("divisionid")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."tempdesk"
    ADD FOREIGN KEY "IQXNetEmailDetails" ("IQXNetEmailDetailsID" ASC)
    REFERENCES "pears"."IQXNetEmailDetails" ("IQXNetEmailDetailsID")
    ON DELETE SET NULL
go


create trigger "WPK_tempdesk_TEMPDESK" after insert,delete,update order 1 on
"pears"."tempdesk"
for each statement
begin
  call "WPKTrackChange"('P','TEMPDESK')
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."tempdesk"."WPK_tempdesk_TEMPDESK" IS 
{create trigger WPK_tempdesk_TEMPDESK 
 after insert,delete,update order 1 on
tempdesk
for each statement
begin
  call WPKTrackChange('P','TEMPDESK')
end
}
go


create trigger "TempdeskDelete" before delete order 2 on
"pears"."tempdesk"
referencing old as "old_name"
for each row
begin
  delete from "compliancepersonstatus" where "tempdeskid" = "old_name"."tempdeskid"
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."tempdesk"."TempdeskDelete" IS 
{create trigger TempdeskDelete 
 before delete  order 2 on
pears."tempdesk"
referencing old as old_name
for each row
begin
    delete from "compliancepersonstatus" where "tempdeskid" = "old_name"."tempdeskid" 
end
}
go
</code>