pears.AccordEmployee [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.AccordEmployee ======
<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 =====
Links Person records to Accord employee records. Used to detect address changes that need to be passed to Accord.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **PersonID** | char(20) | NOT NULL |  |  |
| **PayrollIdentifier** | char(1) | NOT NULL |  | company code |
| **AccordEmployeeCode** | char(20) | NOT NULL |  | payrollnumber |
| surname | char(30) | NULL |  | trunc to 25 |
| forenames | char(30) | NULL |  |  |
| ni | char(15) | NULL |  |  |
| dob | char(8) | NULL |  |  |
| sex | char(1) | NULL |  |  |
| maritalstatus | char(1) | NULL |  | only S, M and space allowed NOT USED |
| addr1 | char(40) | NULL |  |  |
| addr2 | char(40) | NULL |  |  |
| addr3 | char(40) | NULL |  |  |
| postcode | char(30) | NULL |  |  |
| telephone | char(100) | NULL |  | from phone |
| mobile | char(100) | NULL |  | length = 20, telephone 16, email 80 |
| email | char(100) | NULL |  |  |
| employmentdate | char(8) | NULL |  | from latest pay_employment |
| leavingdate | char(8) | NULL |  |  |
| PayFrequency | smallint | NULL |  |  |
| PayMethod | smallint | NULL |  |  |
| BankName | char(30) | NULL |  |  |
| BankAddr1 | char(30) | NULL |  | 51 chars for 1 and 2 |
| BankAddr2 | char(30) | NULL |  |  |
| BankSortCode | char(8) | NULL |  |  |
| BankAcName | char(50) | NULL |  | trunc to 18 |
| BankAcNo | char(20) | NULL |  |  |
| bankref | char(18) | NULL |  |  |
| businesstype | char(1) | NULL |  |  |
| regno | char(20) | NULL |  | trunc to 10 |
| coname | char(50) | NULL |  |  |
| vatregno | char(20) | NULL |  |  |
| CIStype | char(1) | NULL |  |  |
| CISUTR | char(20) | NULL |  | trunc to 13 |
| CISStart | char(8) | NULL |  |  |
| CISEnd | char(8) | NULL |  |  |
| CISVerNo | char(20) | NULL |  | trunc to 13 |
| EmailPayslips | char(1) | NULL |  |  |
| TextPaymentAdvice | char(1) | NULL |  |  |
| DivisionAnalysis | char(6) | NULL |  |  |
| BranchAnalysis | char(6) | NULL |  |  |
| StaffAnalysis | char(6) | NULL |  |  |
| P45Requested | char(1) | NULL |  |  |
| Title | char(20) | NULL |  | trunc to 4 |
| TransferBatch | integer | NULL | 0 |  |
| Appnumber | char(12) | NULL |  |  |
| Town | char(30) | NULL |  |  |
| County | char(30) | NULL |  |  |

===== Primary Key =====
  * PersonID, PayrollIdentifier, AccordEmployeeCode

===== Foreign Keys =====
  * No outgoing foreign keys found.

===== Referenced By =====
  * No incoming foreign keys found.

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| AccordEmployee_personid | Index | PersonID |  |

===== Triggers =====
  * No triggers found.

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."AccordEmployee"
-- Table comment: Links Person records to Accord employee records. Used to detect address changes that need to be passed to Accord.
-- Statement count: 16

CREATE TABLE "pears"."AccordEmployee" (
    "PersonID"                       char(20) NOT NULL
   ,"PayrollIdentifier"              char(1) NOT NULL
   ,"AccordEmployeeCode"             char(20) NOT NULL
   ,"surname"                        char(30) NULL
   ,"forenames"                      char(30) NULL
   ,"ni"                             char(15) NULL
   ,"dob"                            char(8) NULL
   ,"sex"                            char(1) NULL
   ,"maritalstatus"                  char(1) NULL
   ,"addr1"                          char(40) NULL
   ,"addr2"                          char(40) NULL
   ,"addr3"                          char(40) NULL
   ,"postcode"                       char(30) NULL
   ,"telephone"                      char(100) NULL
   ,"mobile"                         char(100) NULL
   ,"email"                          char(100) NULL
   ,"employmentdate"                 char(8) NULL
   ,"leavingdate"                    char(8) NULL
   ,"PayFrequency"                   smallint NULL
   ,"PayMethod"                      smallint NULL
   ,"BankName"                       char(30) NULL
   ,"BankAddr1"                      char(30) NULL
   ,"BankAddr2"                      char(30) NULL
   ,"BankSortCode"                   char(8) NULL
   ,"BankAcName"                     char(50) NULL
   ,"BankAcNo"                       char(20) NULL
   ,"bankref"                        char(18) NULL
   ,"businesstype"                   char(1) NULL
   ,"regno"                          char(20) NULL
   ,"coname"                         char(50) NULL
   ,"vatregno"                       char(20) NULL
   ,"CIStype"                        char(1) NULL
   ,"CISUTR"                         char(20) NULL
   ,"CISStart"                       char(8) NULL
   ,"CISEnd"                         char(8) NULL
   ,"CISVerNo"                       char(20) NULL
   ,"EmailPayslips"                  char(1) NULL
   ,"TextPaymentAdvice"              char(1) NULL
   ,"DivisionAnalysis"               char(6) NULL
   ,"BranchAnalysis"                 char(6) NULL
   ,"StaffAnalysis"                  char(6) NULL
   ,"P45Requested"                   char(1) NULL
   ,"Title"                          char(20) NULL
   ,"TransferBatch"                  integer NULL DEFAULT 0
   ,"Appnumber"                      char(12) NULL
   ,"Town"                           char(30) NULL
   ,"County"                         char(30) NULL
   ,PRIMARY KEY ("PersonID" ASC,"PayrollIdentifier" ASC,"AccordEmployeeCode" ASC) 
)
go


COMMENT ON COLUMN "pears"."AccordEmployee"."PayrollIdentifier" IS 
	'company code'
go


COMMENT ON COLUMN "pears"."AccordEmployee"."AccordEmployeeCode" IS 
	'payrollnumber'
go


COMMENT ON COLUMN "pears"."AccordEmployee"."surname" IS 
	'trunc to 25'
go


COMMENT ON COLUMN "pears"."AccordEmployee"."maritalstatus" IS 
	'only S, M and space allowed NOT USED'
go


COMMENT ON COLUMN "pears"."AccordEmployee"."telephone" IS 
	'from phone'
go


COMMENT ON COLUMN "pears"."AccordEmployee"."mobile" IS 
	'length = 20, telephone 16, email 80'
go


COMMENT ON COLUMN "pears"."AccordEmployee"."employmentdate" IS 
	'from latest pay_employment'
go


COMMENT ON COLUMN "pears"."AccordEmployee"."BankAddr1" IS 
	'51 chars for 1 and 2'
go


COMMENT ON COLUMN "pears"."AccordEmployee"."BankAcName" IS 
	'trunc to 18'
go


COMMENT ON COLUMN "pears"."AccordEmployee"."regno" IS 
	'trunc to 10'
go


COMMENT ON COLUMN "pears"."AccordEmployee"."CISUTR" IS 
	'trunc to 13'
go


COMMENT ON COLUMN "pears"."AccordEmployee"."CISVerNo" IS 
	'trunc to 13'
go


COMMENT ON COLUMN "pears"."AccordEmployee"."Title" IS 
	'trunc to 4'
go


COMMENT ON TABLE "pears"."AccordEmployee" IS 
	'Links Person records to Accord employee records. Used to detect address changes that need to be passed to Accord.'
go


CREATE INDEX "AccordEmployee_personid" ON "pears"."AccordEmployee"
    ( "PersonID" )
go
</code>