pears.tempdesk

Temp Desk settings.

• tempdeskid

• No indexes found.

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