pears.EBTimeSheet [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.EBTimeSheet ======
<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 =====
Expense Benefit timesheet

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **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 |  | Week or Month |
| PayrollIdentifier | char(1) | NULL |  | For external payroll link |
| AnalysisCode | char(20) | NULL |  |  |
| CorrectionFlag | smallint | NULL |  | Non zero if this was to correct errors |
| Billed | smallint | NULL |  |  |
| Paid | smallint | NULL |  |  |
| TransferBatch | integer | NULL |  |  |
| PayrollNumber | char(15) | NULL |  | In case employee payroll number changes |
| TheirRef | char(50) | NULL |  |  |
| Description | long varchar | NULL |  |  |
| Currency | char(3) | NULL |  |  |
| EStatus | smallint | NULL | 0 | 1: E-Timesheet completed on line 2: E-Timesheet changed local copy 3: E-Timesheet awaiting confirmation |
| StartDate | date | NULL |  |  |
| EndDate | date | NULL |  |  |
| ExternalTimesheetReference | char(20) | NULL |  |  |
| ExternalInvoiceReference | char(20) | NULL |  |  |
| SecondaryAgencyID | char(20) | NULL |  | CompanyID of Secondary Agency which supplied the worker |
| WhenEntered | timestamp | NULL | current timestamp |  |
| StaffID | char(20) | NULL |  |  |
| AccordTransferBatch | integer | NULL |  |  |
| AccordPayrollNumber | char(15) | NULL |  | In case employee payroll number changes |
| ScanPathFilename | char(250) | NULL |  |  |
| WhenPaid | timestamp | NULL |  |  |
| ImageBlobID | char(20) | NULL |  |  |

===== Primary Key =====
  * EBTimeSheetID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| Person | PersonID | [[database:tables:pears_person|pears.Person (personid)]] |  |
| TempDesk | TempDeskID | [[database:tables:pears_tempdesk|pears.tempdesk (tempdeskid)]] |  |
| Company | SecondaryAgencyID | [[database:tables:pears_company|pears.Company (companyid)]] |  |
| Staff | StaffID | [[database:tables:pears_staff|pears.staff (staffid)]] | ON DELETE SET NULL |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_ebtimesheetline|pears.EBTimeSheetLine]] | EBTimeSheet | EBTimeSheetID | EBTimeSheetID |

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| EBTimeSheet_Period | Index | Period |  |
| EBTimeSheet_transferbatch | Index | TransferBatch |  |
| EBTimeSheet_PerdPID | Index | Period, PersonID |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| EBTimesheet_Insert | before | insert order 1 |

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