pears.EBTimeSheet

Expense Benefit timesheet

• EBTimeSheetID

-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."EBTimeSheet"
-- Table comment: Expense Benefit timesheet
-- Statement count: 18
 
CREATE TABLE "pears"."EBTimeSheet" (
    "EBTimeSheetID"                  CHAR(20) NOT NULL
   ,"PersonID"                       CHAR(20) NULL
   ,"TempDeskID"                     CHAR(20) NULL
   ,"SerialNumber"                   CHAR(12) NULL
   ,"Period"                         INTEGER NOT NULL
   ,"PeriodLength"                   CHAR(1) NOT NULL
   ,"PayrollIdentifier"              CHAR(1) NULL
   ,"AnalysisCode"                   CHAR(20) NULL
   ,"CorrectionFlag"                 SMALLINT NULL
   ,"Billed"                         SMALLINT NULL
   ,"Paid"                           SMALLINT NULL
   ,"TransferBatch"                  INTEGER NULL
   ,"PayrollNumber"                  CHAR(15) NULL
   ,"TheirRef"                       CHAR(50) NULL
   ,"Description"                    long VARCHAR NULL
   ,"Currency"                       CHAR(3) NULL
   ,"EStatus"                        SMALLINT NULL DEFAULT 0
   ,"StartDate"                      DATE NULL
   ,"EndDate"                        DATE NULL
   ,"ExternalTimesheetReference"     CHAR(20) NULL
   ,"ExternalInvoiceReference"       CHAR(20) NULL
   ,"SecondaryAgencyID"              CHAR(20) NULL
   ,"WhenEntered"                    TIMESTAMP NULL DEFAULT CURRENT TIMESTAMP
   ,"StaffID"                        CHAR(20) NULL
   ,"AccordTransferBatch"            INTEGER NULL
   ,"AccordPayrollNumber"            CHAR(15) NULL
   ,"ScanPathFilename"               CHAR(250) NULL
   ,"WhenPaid"                       TIMESTAMP NULL
   ,"ImageBlobID"                    CHAR(20) NULL
   ,PRIMARY KEY ("EBTimeSheetID" ASC) 
)
GO
 
 
COMMENT ON COLUMN "pears"."EBTimeSheet"."PeriodLength" IS 
	'Week or Month'
GO
 
 
COMMENT ON COLUMN "pears"."EBTimeSheet"."PayrollIdentifier" IS 
	'For external payroll link'
GO
 
 
COMMENT ON COLUMN "pears"."EBTimeSheet"."CorrectionFlag" IS 
	'Non zero if this was to correct errors'
GO
 
 
COMMENT ON COLUMN "pears"."EBTimeSheet"."PayrollNumber" IS 
	'In case employee payroll number changes'
GO
 
 
COMMENT ON COLUMN "pears"."EBTimeSheet"."EStatus" IS 
	'1: E-Timesheet completed on line 2: E-Timesheet changed local copy 3: E-Timesheet awaiting confirmation'
GO
 
 
COMMENT ON COLUMN "pears"."EBTimeSheet"."SecondaryAgencyID" IS 
	'CompanyID of Secondary Agency which supplied the worker'
GO
 
 
COMMENT ON COLUMN "pears"."EBTimeSheet"."AccordPayrollNumber" IS 
	'In case employee payroll number changes'
GO
 
 
COMMENT ON TABLE "pears"."EBTimeSheet" IS 
	'Expense Benefit timesheet'
GO
 
 
ALTER TABLE "pears"."EBTimeSheet"
    ADD FOREIGN KEY "Person" ("PersonID" ASC)
    REFERENCES "pears"."Person" ("personid")
GO
 
 
ALTER TABLE "pears"."EBTimeSheet"
    ADD FOREIGN KEY "TempDesk" ("TempDeskID" ASC)
    REFERENCES "pears"."tempdesk" ("tempdeskid")
GO
 
 
ALTER TABLE "pears"."EBTimeSheet"
    ADD FOREIGN KEY "Company" ("SecondaryAgencyID" ASC)
    REFERENCES "pears"."Company" ("companyid")
GO
 
 
ALTER TABLE "pears"."EBTimeSheet"
    ADD FOREIGN KEY "Staff" ("StaffID" ASC)
    REFERENCES "pears"."staff" ("staffid")
    ON DELETE SET NULL
GO
 
 
CREATE INDEX "EBTimeSheet_Period" ON "pears"."EBTimeSheet"
    ( "Period" )
GO
 
 
CREATE INDEX "EBTimeSheet_transferbatch" ON "pears"."EBTimeSheet"
    ( "TransferBatch" )
GO
 
 
CREATE INDEX "EBTimeSheet_PerdPID" ON "pears"."EBTimeSheet"
    ( "Period","PersonID" )
GO
 
 
CREATE TRIGGER "EBTimesheet_Insert" BEFORE INSERT ORDER 1 ON
"pears"."EBTimesheet"
REFERENCING NEW AS "new_ts"
FOR each ROW
WHEN("new_ts"."staffid" IS NULL)
BEGIN
  SET "new_ts"."staffid" = "userstaffid"
exception
  WHEN others THEN
    SET "new_ts"."staffid" = NULL
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."EBTimeSheet"."EBTimesheet_Insert" IS 
{CREATE TRIGGER EBTimesheet_Insert 
 BEFORE INSERT ORDER 1 ON
pears.EBTimesheet
REFERENCING NEW AS new_ts
FOR each ROW
WHEN(new_ts.staffid IS NULL)
BEGIN
  SET new_ts.staffid=userstaffid
exception
  WHEN others THEN
    SET new_ts.staffid=NULL
END
}
GO