====== pears.Person ======
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
===== Description =====
Main Person records (includes Comapny Contacts).
===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **personid** | char(20) | NOT NULL | | |
| staffid | char(20) | NULL | | |
| name | char(30) | NOT NULL | | |
| addr1 | char(40) | NULL | | |
| addr2 | char(40) | NULL | | |
| addr3 | char(40) | NULL | | |
| town | char(30) | NULL | | |
| county | char(30) | NULL | | |
| country | char(30) | NULL | | |
| postcode | char(30) | NULL | | |
| ni | char(15) | NULL | | |
| maritalstatus | char(1) | NULL | | |
| children | smallint | NULL | | |
| payrollnumber | char(15) | NULL | | |
| selfemployed | smallint | NULL | 0 | |
| sex | char(1) | NULL | | |
| dob | date | NULL | | |
| available | char(1) | NULL | | |
| notes | long varchar | NULL | | |
| othernotes | long varchar | NULL | | |
| keyname | char(60) | NOT NULL | | |
| status | char(1) | NULL | | |
| noticeperiod | integer | NULL | 0 | |
| availdate | date | NULL | | |
| forenames | char(30) | NULL | | |
| surname | char(30) | NULL | | |
| salutation | char(40) | NULL | | |
| cvtext | long varchar | NULL | | |
| appnumber | char(12) | NULL | | |
| registrationdate | date | NULL | | |
| changedate | timestamp | NULL | | |
| primarycontact | smallint | NULL | 0 | |
| alert | char(100) | NULL | | |
| wpdoc | char(10) | NULL | | |
| ExtendedType | char(1) | NULL | | |
| RequirementsMandatory | tinyint | NULL | | |
| OnlyMatchIfKnownAvailable | tinyint | NULL | | |
| PayrollIdentifier | char(1) | NULL | | For external payroll link |
| CVLoc | char(250) | NULL | | |
| CheckAvailabilityDate | date | NULL | | A diary date when next to be contacted to determine availability |
| divisionid | char(20) | NULL | | |
| source | char(1) | NULL | | Made available in V2. Hidden by default |
| ExtraNotes | long varchar | NULL | | |
| TransferNotes | long varchar | NULL | | |
| AvailabilityNotifyDate | date | NULL | | Date availability last notified |
| accordpayrollnumber | char(15) | NULL | | |
| lastcontactevent | timestamp | NULL | | Added V2.2.2.13 Will be null for existing people |
| titleforpayroll | char(20) | NULL | | |
| payrollemailaddress | char(250) | NULL | | |
| directlyemployed | smallint | NULL | 0 | |
| PersonWarning | long varchar | NULL | | |
| UnsubscribeToMarketing | tinyint | NULL | 0 | |
| RecipientIdentifier | char(50) | NULL | | |
| ExclusiveVacancyID | char(20) | NULL | | |
| compliancestaffid | char(20) | NULL | | |
| CreatedDate | date | NULL | current date | |
| OriginID | char(20) | NULL | | This is the new Source |
| FilteredLastContactEvent | timestamp | NULL | | Like LastContactEvent but for selected contact event types |
| staffid2 | char(20) | NULL | | Pulse only field but needed in dast wizard |
| pdfcv | tinyint | NULL | 0 | pdf=1, doc=0 |
| P46Declaration | smallint | NULL | 0 | 0=empty space, 1=First Job since April, no benefits, 2=Had another Job or benefits since April, 3=Have another Job or Pension, 4=Put on OT W1/M1 |
| StudentLoan | smallint | NULL | 0 | 0=No, 1=Yes, 2=empty space |
| StudentLoanType | smallint | NULL | 0 | 0=empty space, 1=Plan 1 (Northern Ireland, England, Wales), 2=Plan 2 (England and Wales), 3=Plan 4 (Scotland), 4=Postgraduate Loan (England and Wales) |
===== Primary Key =====
* personid
===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| staff | staffid | [[database:tables:pears_staff|pears.staff (staffid)]] | |
| division | divisionid | [[database:tables:pears_division|pears.Division (divisionid)]] | ON DELETE SET NULL |
| vacancyclass | source | [[database:tables:pears_vacancyclass|pears.vacancyclass (classcode)]] | |
| origin | OriginID | [[database:tables:pears_origin|pears.Origin (OriginID)]] | |
===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_automatchnotificationqueue|pears.AutoMatchNotificationQueue]] | person | PersonID | personid |
| [[database:tables:pears_automatchpersonconfig|pears.AutoMatchPersonConfig]] | person | PersonID | personid |
| [[database:tables:pears_automatchpersonpreferredshifttype|pears.AutoMatchPersonPreferredShiftType]] | Person | PersonID | personid |
| [[database:tables:pears_awrjobmaster|pears.AWRJobMaster]] | Person | PersonID | personid |
| [[database:tables:pears_awrweeklydetail|pears.AWRWeeklyDetail]] | Person | PersonID | personid |
| [[database:tables:pears_broadbeancandidate|pears.BroadbeanCandidate]] | person | personid | personid |
| [[database:tables:pears_ciscard|pears.CISCard]] | Person | PersonID | personid |
| [[database:tables:pears_companysds|pears.CompanySDS]] | person | PersonID | personid |
| [[database:tables:pears_compliancepersondomain|pears.CompliancePersonDomain]] | Person | PersonID | personid |
| [[database:tables:pears_consentpersonoption|pears.ConsentPersonOption]] | Person | PersonID | personid |
| [[database:tables:pears_consentpersonoptionhistory|pears.ConsentPersonOptionHistory]] | Person | PersonID | personid |
| [[database:tables:pears_contactevent|pears.contactevent]] | person | personid | personid |
| [[database:tables:pears_diary|pears.diary]] | person | personid | personid |
| [[database:tables:pears_docpackvalidation|pears.DocPackValidation]] | Person | PersonID | personid |
| [[database:tables:pears_ebtimesheet|pears.EBTimeSheet]] | Person | PersonID | personid |
| [[database:tables:pears_employment|pears.employment]] | person | personid | personid |
| [[database:tables:pears_externalemployment|pears.externalemployment]] | person | personid | personid |
| [[database:tables:pears_interpersonlink|pears.InterPersonLink]] | Person1 | Person1ID | personid |
| [[database:tables:pears_interpersonlink|pears.InterPersonLink]] | Person2 | Person2ID | personid |
| [[database:tables:pears_iqxnetcandidateshifts|pears.IQXNetCandidateShifts]] | Person | PersonID | personid |
| [[database:tables:pears_iqxnetuserlink|pears.IQXNetUserLink]] | Person | PersonID | personid |
| [[database:tables:pears_iqxnetyoti|pears.IQXNetYoti]] | Person | PersonID | personid |
| [[database:tables:pears_masterrosterfaillog|pears.MasterRosterFailLog]] | person | PersonID | personid |
| [[database:tables:pears_masterrosterpreferredperson|pears.MasterRosterPreferredPerson]] | person | PersonID | personid |
| [[database:tables:pears_masterrostershift|pears.MasterRosterShift]] | Person | DefaultPersonID | personid |
| [[database:tables:pears_notificationdeeplink|pears.NotificationDeepLink]] | Person | PersonID | personid |
| [[database:tables:pears_offlimits|pears.OffLimits]] | Person | PersonID | personid |
| [[database:tables:pears_pay_employee|pears.Pay_Employee]] | person | PersonID | personid |
| [[database:tables:pears_pay_employment|pears.Pay_Employment]] | person | PersonID | personid |
| [[database:tables:pears_payrolldocument|pears.PayrollDocument]] | Person | PersonID | personid |
| [[database:tables:pears_personincompatibility|pears.PersonIncompatibility]] | Person1 | Person1ID | personid |
| [[database:tables:pears_personincompatibility|pears.PersonIncompatibility]] | Person2 | Person2ID | personid |
| [[database:tables:pears_personinterest|pears.PersonInterest]] | PersonInterest1 | PersonID | personid |
| [[database:tables:pears_personshiftpreference|pears.PersonShiftPreference]] | Person | personid | personid |
| [[database:tables:pears_personstafflink|pears.PersonStaffLink]] | Person | PersonID | personid |
| [[database:tables:pears_personwillingness|pears.PersonWillingness]] | person | PersonID | personid |
| [[database:tables:pears_personwillingnesshistory|pears.PersonWillingnessHistory]] | person | PersonID | personid |
| [[database:tables:pears_progress|pears.progress]] | person | personid | personid |
| [[database:tables:pears_pshealthperson|pears.psHealthPerson]] | Person | personid | personid |
| [[database:tables:pears_referencerequest|pears.ReferenceRequest]] | Person | PersonID | personid |
| [[database:tables:pears_search|pears.search]] | person | personid | personid |
| [[database:tables:pears_sentcvs|pears.SentCVs]] | person | personid | personid |
| [[database:tables:pears_staffhistory|pears.StaffHistory]] | Person | PersonID | personid |
| [[database:tables:pears_statushistory|pears.StatusHistory]] | Person | PersonID | personid |
| [[database:tables:pears_temppoolmember|pears.TempPoolMember]] | Person | PersonID | personid |
| [[database:tables:pears_tempprovtimesheet|pears.TempProvTimeSheet]] | Person | PersonID | personid |
| [[database:tables:pears_tempshift|pears.TempShift]] | Person | PersonID | personid |
| [[database:tables:pears_tempshiftprogress|pears.TempShiftProgress]] | Person | PersonID | personid |
| [[database:tables:pears_tempsubsidy|pears.TempSubsidy]] | Person | PersonID | personid |
| [[database:tables:pears_temptimesheet|pears.TempTimeSheet]] | Person | PersonID | personid |
| [[database:tables:pears_trengomessage|pears.TrengoMessage]] | Person | PersonID | personid |
| [[database:tables:pears_trengosent|pears.TrengoSent]] | Person | Personid | personid |
| [[database:tables:pears_tsimage|pears.TSImage]] | person | PersonID | personid |
| [[database:tables:pears_tsquerylog|pears.TSQueryLog]] | Person | PersonID | personid |
| [[database:tables:pears_utrcheck|pears.UTRCheck]] | Person | personID | personid |
| [[database:tables:pears_vacancy|pears.vacancy]] | person | RecipientID | personid |
| [[database:tables:pears_withholds|pears.WithHolds]] | person | PersonID | personid |
===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| person_keyname | Index | keyname | |
| person_appnumber | Index | appnumber | |
| person_status | Index | status, keyname | |
| person_postcode | Index | postcode | |
| forename | Index | forenames | |
| person_payrollnumber | Index | payrollnumber | |
| person_lastcontactevent | Index | lastcontactevent | |
| person_exclusivevacancy | Index | ExclusiveVacancyID | |
| person_filteredlastcontactevent | Index | FilteredLastContactEvent | |
| person_ni | Index | ni | |
| personcvtext | Text index | cvtext | CONFIGURATION "SYS"."default_char" IMMEDIATE REFRESH |
| person_notestext | Text index | notes, othernotes | CONFIGURATION "SYS"."default_char" IMMEDIATE REFRESH |
===== Triggers =====
^ Name ^ Timing ^ Event ^
| personupdate | after | update of "name", "addr1","addr2","addr3","town","county","country","postcode","ni","maritalstatus","payrollnumber","selfemployed","sex","dob","forenames","surname", "salutation","extranotes","titleforpayroll" order 1 |
| PersonAudit | before | update of "name", "addr1","addr2","addr3","town","county","country","postcode","ni","payrollnumber","dob","status","forenames","surname","keyname","appnumber","PayrollIdentifier","notes", "divisionid","staffid","alert","originid","titleforpayroll","salutation","registrationdate","directlyemployed","UnsubscribeToMarketing","personwarning" order 2 |
| PersonUnsubscribe | after | update of "UnsubscribeToMarketing" order 2500 |
| psHealthPersonUpdate | after | update of "forenames", "surname","sex","dob","addr1","addr2","addr3","town","county","country","titleforpayroll" order 900 |
| Person_InsertTrim | before | insert order 1 |
| Person_UpdateTrim | before | update order 3 |
| InsertStatusPerson | after | insert order 2 |
| UpdateStatusPerson | after | update of "status" order 2 |
| PersonKeyWordsInsert | after | insert order 20 |
| PersonKeyWordsUpdate | after | update of "Salutation", "Surname","Forenames","Addr1","Addr2","Addr3","Town","County","PostCode","NI","AppNumber","PayrollNumber" order 21 |
| PersonDelete | before | delete order 2 |
===== Original SQL =====
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."Person"
-- Table comment: Main Person records (includes Comapny Contacts).
-- Statement count: 51
CREATE TABLE "pears"."Person" (
"personid" char(20) NOT NULL
,"staffid" char(20) NULL
,"name" char(30) NOT NULL
,"addr1" char(40) NULL
,"addr2" char(40) NULL
,"addr3" char(40) NULL
,"town" char(30) NULL
,"county" char(30) NULL
,"country" char(30) NULL
,"postcode" char(30) NULL
,"ni" char(15) NULL
,"maritalstatus" char(1) NULL
,"children" smallint NULL
,"payrollnumber" char(15) NULL
,"selfemployed" smallint NULL DEFAULT 0
,"sex" char(1) NULL
,"dob" date NULL
,"available" char(1) NULL
,"notes" long varchar NULL
,"othernotes" long varchar NULL
,"keyname" char(60) NOT NULL INLINE 60 PREFIX 8 check("length"("trim"("keyname")) > 0)
,"status" char(1) NULL
,"noticeperiod" integer NULL DEFAULT 0
,"availdate" date NULL
,"forenames" char(30) NULL
,"surname" char(30) NULL
,"salutation" char(40) NULL
,"cvtext" long varchar NULL
,"appnumber" char(12) NULL
,"registrationdate" date NULL
,"changedate" timestamp NULL
,"primarycontact" smallint NULL DEFAULT 0
,"alert" char(100) NULL
,"wpdoc" char(10) NULL
,"ExtendedType" char(1) NULL
,"RequirementsMandatory" tinyint NULL
,"OnlyMatchIfKnownAvailable" tinyint NULL
,"PayrollIdentifier" char(1) NULL
,"CVLoc" char(250) NULL
,"CheckAvailabilityDate" date NULL
,"divisionid" char(20) NULL
,"source" char(1) NULL
,"ExtraNotes" long varchar NULL
,"TransferNotes" long varchar NULL
,"AvailabilityNotifyDate" date NULL
,"accordpayrollnumber" char(15) NULL
,"lastcontactevent" timestamp NULL
,"titleforpayroll" char(20) NULL
,"payrollemailaddress" char(250) NULL
,"directlyemployed" smallint NULL DEFAULT 0
,"PersonWarning" long varchar NULL
,"UnsubscribeToMarketing" tinyint NULL DEFAULT 0
,"RecipientIdentifier" char(50) NULL
,"ExclusiveVacancyID" char(20) NULL
,"compliancestaffid" char(20) NULL
,"CreatedDate" date NULL DEFAULT current date
,"OriginID" char(20) NULL
,"FilteredLastContactEvent" timestamp NULL
,"staffid2" char(20) NULL
,"pdfcv" tinyint NULL DEFAULT 0
,"P46Declaration" smallint NULL DEFAULT 0
,"StudentLoan" smallint NULL DEFAULT 0
,"StudentLoanType" smallint NULL DEFAULT 0
,PRIMARY KEY ("personid" ASC)
)
go
COMMENT ON COLUMN "pears"."Person"."PayrollIdentifier" IS
'For external payroll link'
go
COMMENT ON COLUMN "pears"."Person"."CheckAvailabilityDate" IS
'A diary date when next to be contacted to determine availability'
go
COMMENT ON COLUMN "pears"."Person"."source" IS
'Made available in V2. Hidden by default'
go
COMMENT ON COLUMN "pears"."Person"."AvailabilityNotifyDate" IS
'Date availability last notified'
go
COMMENT ON COLUMN "pears"."Person"."lastcontactevent" IS
'Added V2.2.2.13 Will be null for existing people'
go
COMMENT ON COLUMN "pears"."Person"."OriginID" IS
'This is the new Source'
go
COMMENT ON COLUMN "pears"."Person"."FilteredLastContactEvent" IS
'Like LastContactEvent but for selected contact event types'
go
COMMENT ON COLUMN "pears"."Person"."staffid2" IS
'Pulse only field but needed in dast wizard'
go
COMMENT ON COLUMN "pears"."Person"."pdfcv" IS
'pdf=1, doc=0'
go
COMMENT ON COLUMN "pears"."Person"."P46Declaration" IS
'0=empty space, 1=First Job since April, no benefits, 2=Had another Job or benefits since April, 3=Have another Job or Pension, 4=Put on OT W1/M1'
go
COMMENT ON COLUMN "pears"."Person"."StudentLoan" IS
'0=No, 1=Yes, 2=empty space'
go
COMMENT ON COLUMN "pears"."Person"."StudentLoanType" IS
'0=empty space, 1=Plan 1 (Northern Ireland, England, Wales), 2=Plan 2 (England and Wales), 3=Plan 4 (Scotland), 4=Postgraduate Loan (England and Wales)'
go
COMMENT ON TABLE "pears"."Person" IS
'Main Person records (includes Comapny Contacts).'
go
ALTER TABLE "pears"."Person"
ADD FOREIGN KEY "staff" ("staffid" ASC)
REFERENCES "pears"."staff" ("staffid")
go
ALTER TABLE "pears"."Person"
ADD FOREIGN KEY "division" ("divisionid" ASC)
REFERENCES "pears"."Division" ("divisionid")
ON DELETE SET NULL
go
ALTER TABLE "pears"."Person"
ADD FOREIGN KEY "vacancyclass" ("source" ASC)
REFERENCES "pears"."vacancyclass" ("classcode")
go
ALTER TABLE "pears"."Person"
ADD FOREIGN KEY "origin" ("OriginID" ASC)
REFERENCES "pears"."Origin" ("OriginID")
go
CREATE INDEX "person_keyname" ON "pears"."Person"
( "keyname" )
go
CREATE INDEX "person_appnumber" ON "pears"."Person"
( "appnumber" )
go
CREATE INDEX "person_status" ON "pears"."Person"
( "status","keyname" )
go
CREATE INDEX "person_postcode" ON "pears"."Person"
( "postcode" )
go
CREATE INDEX "forename" ON "pears"."Person"
( "forenames" )
go
CREATE INDEX "person_payrollnumber" ON "pears"."Person"
( "payrollnumber" )
go
CREATE INDEX "person_lastcontactevent" ON "pears"."Person"
( "lastcontactevent" DESC )
go
CREATE INDEX "person_exclusivevacancy" ON "pears"."Person"
( "ExclusiveVacancyID" )
go
CREATE INDEX "person_filteredlastcontactevent" ON "pears"."Person"
( "FilteredLastContactEvent" DESC )
go
CREATE INDEX "person_ni" ON "pears"."Person"
( "ni" )
go
CREATE TEXT INDEX "personcvtext" ON "pears"."Person"
( "cvtext" ) CONFIGURATION "SYS"."default_char" IMMEDIATE REFRESH
go
CREATE TEXT INDEX "person_notestext" ON "pears"."Person"
( "notes","othernotes" ) CONFIGURATION "SYS"."default_char" IMMEDIATE REFRESH
go
create trigger "personupdate" after update of "name",
"addr1","addr2","addr3","town","county","country","postcode","ni","maritalstatus","payrollnumber","selfemployed","sex","dob","forenames","surname",
"salutation","extranotes","titleforpayroll" order 1 on "pears"."Person"
referencing old as "old_pers"
for each row
begin
update "pay_employee" set "transferbatch" = 0 where "personid" = "old_pers"."personid" and "transferbatch" > 0;
update "accordemployee" set "transferbatch" = 0 where "personid" = "old_pers"."personid"
end
go
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Person"."personupdate" IS
{create trigger personupdate
after update of name,addr1,addr2,addr3,town,county,country,postcode,ni,maritalstatus,payrollnumber,selfemployed,sex,dob,forenames,surname,
salutation,extranotes, titleforpayroll order 1 on pears.Person
referencing old as old_pers
for each row
begin
update pay_employee set transferbatch = 0 where personid = old_pers.personid and transferbatch > 0;
update accordemployee set transferbatch = 0 where personid = old_pers.personid
end
}
go
create trigger "PersonAudit" before update of "name",
"addr1","addr2","addr3","town","county","country","postcode","ni","payrollnumber","dob","status","forenames","surname","keyname","appnumber","PayrollIdentifier","notes",
"divisionid","staffid","alert","originid","titleforpayroll","salutation","registrationdate","directlyemployed","UnsubscribeToMarketing","personwarning" order 2 on "pears"."Person"
referencing old as "old_pers" new as "new_pers"
for each row
when(exists(select * from "AuditItems" where "AreaName" = 'Person' and "AuditFlag" = 1))
begin
declare @AuditList long varchar;
declare @OldDescrip char(250);
declare @NewDescrip char(250);
declare @PersonName char(250);
select "string"(',',"list"("ItemName"),',') into @AuditList from "AuditItems" where "AreaName" = 'Person' and "AuditFlag" = 1;
set @PersonName = "old_pers"."Name";
-- DOB
if "locate"(@AuditList,',DOB,') > 0 and update("dob") then
call "AuditLog"('PERSON',"old_pers"."personid","string"('Date of Birth Updated - ',@PersonName),"old_pers"."dob","new_pers"."dob")
end if;
if "locate"(@AuditList,',Title for Payroll,') > 0 and update("titleforpayroll") then
call "AuditLog"('PERSON',"old_pers"."personid","string"('Title for Payroll Updated - ',@PersonName),"old_pers"."titleforpayroll","new_pers"."titleforpayroll")
end if;
-- NI Number
if "locate"(@AuditList,',National Insurance Number,') > 0 and update("NI") then
call "AuditLog"('PERSON',"old_pers"."personid","string"('NI Updated - ',@PersonName),"old_pers"."ni","new_pers"."ni")
end if;
if "locate"(@AuditList,',Warning,') > 0 and update("personwarning") then
call "AuditLog"('PERSON',"old_pers"."personid","string"('Warning Updated - ',@PersonName),"old_pers"."personwarning","new_pers"."personwarning")
end if;
-- Reg Date
if "locate"(@AuditList,',Registration Date,') > 0 and update("RegistrationDate") then
call "AuditLog"('PERSON',"old_pers"."personid","string"('Registration Date Updated - ',@PersonName),"old_pers"."RegistrationDate","new_pers"."RegistrationDate")
end if;
-- Notes
if "locate"(@AuditList,',Notes,') > 0 and update("Notes") then
call "AuditLog"('PERSON',"old_pers"."personid","string"('Notes Updated - ',@PersonName),"old_pers"."notes","new_pers"."notes")
end if;
-- App Number
if "locate"(@AuditList,',Registration Number,') > 0 and update("AppNumber") then
call "AuditLog"('PERSON',"old_pers"."personid","string"('Registration Number Updated - ',@PersonName),"old_pers"."AppNumber","new_pers"."AppNumber")
end if;
-- Status
if "locate"(@AuditList,',Status,') > 0 and update("Status") then
select "name" into @OldDescrip from "status" where "type" = 'P' and "status"."status" = "old_pers"."status";
select "name" into @NewDescrip from "status" where "type" = 'P' and "status"."status" = "new_pers"."status";
call "AuditLog"('PERSON',"old_pers"."personid","string"('Status Updated - ',@PersonName),"string"("old_pers"."Status",' - ',@OldDescrip),"string"("new_pers"."Status",' - ',@NewDescrip))
end if;
-- Payroll Identifier
if "locate"(@AuditList,',Payroll Company,') > 0 and update("PayrollIdentifier") then
call "AuditLog"('PERSON',"old_pers"."personid","string"('Payroll Identifier Updated - ',@PersonName),"old_pers"."PayrollIdentifier","new_pers"."PayrollIdentifier")
end if;
-- Division
if "locate"(@AuditList,',Division,') > 0 and update("DivisionID") then
select "name" into @OldDescrip from "Division" where "divisionid" = "old_pers"."DivisionID";
select "name" into @NewDescrip from "Division" where "divisionid" = "new_pers"."DivisionID";
call "AuditLog"('PERSON',"old_pers"."personid","string"('Division Updated - ',@PersonName),@OldDescrip,@NewDescrip)
end if;
-- Consultant
if "locate"(@AuditList,',Consultant,') > 0 and update("StaffID") then
select "userid" into @OldDescrip from "staff" where "staffid" = "old_pers"."StaffID";
select "userid" into @NewDescrip from "staff" where "staffid" = "new_pers"."StaffID";
call "AuditLog"('PERSON',"old_pers"."personid","string"('Consultant Updated - ',@PersonName),@OldDescrip,@NewDescrip);
insert into "staffhistory"( "staffhistoryid","personid","oldstaff","newstaff","whoentered","whenentered" ) values( "uniquekey"("new_pers"."StaffID"),"new_pers"."personid","old_pers"."StaffID","new_pers"."StaffID","userstaffid",current timestamp )
end if;
-- Address
if "locate"(@AuditList,',Address,') > 0 and(update("addr1") or update("addr2") or update("addr3") or update("town") or update("county") or update("country") or update("postcode")) then
call "AuditLog"('PERSON',"old_pers"."personid","string"('Address Updated - ',@PersonName),"string"("old_pers"."addr1",', ',"old_pers"."addr2",', ',"old_pers"."addr3",', ',"old_pers"."town",', ',"old_pers"."county",', ',"old_pers"."postcode",', ',"old_pers"."country"),"string"("new_pers"."addr1",', ',"new_pers"."addr2",', ',"new_pers"."addr3",', ',"new_pers"."town",', ',"new_pers"."county",', ',"new_pers"."postcode",', ',"new_pers"."country"))
end if;
-- Alert
if "locate"(@AuditList,',Alert,') > 0 and update("Alert") then
call "AuditLog"('PERSON',"old_pers"."personid","string"('Alert Updated - ',@PersonName),"old_pers"."Alert","new_pers"."Alert")
end if;
-- Name
if "locate"(@AuditList,',Name,') > 0 then
if update("name") then
call "AuditLog"('PERSON',"old_pers"."personid","string"('Name Updated - ',@PersonName),"old_pers"."name","new_pers"."name")
end if;
-- Forenames
if update("Forenames") then
call "AuditLog"('PERSON',"old_pers"."personid","string"('Forenames Updated - ',@PersonName),"old_pers"."Forenames","new_pers"."Forenames")
end if;
-- Surname
if update("Surname") then
call "AuditLog"('PERSON',"old_pers"."personid","string"('Surname Updated - ',@PersonName),"old_pers"."Surname","new_pers"."Surname")
end if;
-- Sslutation
if update("Sslutation") then
call "AuditLog"('PERSON',"old_pers"."personid","string"('Salutation Updated - ',@PersonName),"old_pers"."Salutation","new_pers"."Salutation")
end if;
-- Keyname
if update("Keyname") then
call "AuditLog"('PERSON',"old_pers"."personid","string"('Keyname Updated - ',@PersonName),"old_pers"."Keyname","new_pers"."Keyname")
end if end if;
-- Payroll Number
if "locate"(@AuditList,',Payroll Number,') > 0 and update("PayrollNumber") then
call "AuditLog"('PERSON',"old_pers"."personid","string"('Payroll Number Updated - ',@PersonName),"old_pers"."PayrollNumber","new_pers"."PayrollNumber")
end if;
-- AWR Exempt
if "locate"(@AuditList,',AWR Exemption Reason,') > 0 and update("directlyemployed") then
call "AuditLog"('PERSON',"old_pers"."personid","string"('AWR Exemption Reason Updated - ',@PersonName),"string"("old_pers"."directlyemployed"),"string"("new_pers"."directlyemployed"))
end if;
-- Source
if "locate"(@AuditList,',Source,') > 0 and update("originid") then
select "descrip" into @OldDescrip from "origin" where "originid" = "old_pers"."originid";
select "descrip" into @NewDescrip from "origin" where "originid" = "new_pers"."originid";
call "AuditLog"('PERSON',"old_pers"."personid","string"('Source Updated - ',@PersonName),"string"(@OldDescrip),"string"(@NewDescrip))
end if;
-- Unsubscribe To Marketing
if "locate"(@AuditList,',Unsubscribe To Marketing,') > 0 and update("UnsubscribeToMarketing") then
call "AuditLog"('PERSON',"old_pers"."personid","string"('Unsubscribe To Marketing - ',@PersonName),"string"("old_pers"."UnsubscribeToMarketing"),"string"("new_pers"."UnsubscribeToMarketing"))
end if
end
go
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Person"."PersonAudit" IS
{create trigger PersonAudit
before update of name,addr1,addr2,addr3,town,county,country,postcode,ni,payrollnumber,dob,status,forenames,surname,keyname,appnumber,PayrollIdentifier,notes,
divisionid,staffid,alert,originid, titleforpayroll, salutation, registrationdate, directlyemployed,UnsubscribeToMarketing, personwarning order 2 on pears.Person
referencing old as old_pers new as new_pers
for each row
when(exists(select* from AuditItems where AreaName = 'Person' and AuditFlag = 1))
begin
declare @AuditList long varchar;
declare @OldDescrip char(250);
declare @NewDescrip char(250);
declare @PersonName char(250);
select string(',',list(ItemName),',') into @AuditList from AuditItems where AreaName = 'Person' and AuditFlag = 1;
set @PersonName=old_pers.Name;
-- DOB
if locate(@AuditList,',DOB,') > 0 and update(dob) then
call AuditLog('PERSON',old_pers.personid,string('Date of Birth Updated - ',@PersonName),old_pers.dob,new_pers.dob)
end if;
if locate(@AuditList,',Title for Payroll,') > 0 and update(titleforpayroll) then
call AuditLog('PERSON',old_pers.personid,string('Title for Payroll Updated - ',@PersonName),old_pers.titleforpayroll,new_pers.titleforpayroll)
end if;
-- NI Number
if locate(@AuditList,',National Insurance Number,') > 0 and update(NI) then
call AuditLog('PERSON',old_pers.personid,string('NI Updated - ',@PersonName),old_pers.ni,new_pers.ni)
end if;
if locate(@AuditList,',Warning,') > 0 and update(personwarning) then
call AuditLog('PERSON',old_pers.personid,string('Warning Updated - ',@PersonName),old_pers.personwarning,new_pers.personwarning)
end if;
-- Reg Date
if locate(@AuditList,',Registration Date,') > 0 and update(RegistrationDate) then
call AuditLog('PERSON',old_pers.personid,string('Registration Date Updated - ',@PersonName),old_pers.RegistrationDate,new_pers.RegistrationDate)
end if;
-- Notes
if locate(@AuditList,',Notes,') > 0 and update(Notes) then
call AuditLog('PERSON',old_pers.personid,string('Notes Updated - ',@PersonName),old_pers.notes,new_pers.notes)
end if;
-- App Number
if locate(@AuditList,',Registration Number,') > 0 and update(AppNumber) then
call AuditLog('PERSON',old_pers.personid,string('Registration Number Updated - ',@PersonName),old_pers.AppNumber,new_pers.AppNumber)
end if;
-- Status
if locate(@AuditList,',Status,') > 0 and update(Status) then
select name into @OldDescrip from status where type = 'P' and status.status = old_pers.status;
select name into @NewDescrip from status where type = 'P' and status.status = new_pers.status;
call AuditLog('PERSON',old_pers.personid,string('Status Updated - ',@PersonName),string(old_pers.Status,' - ',@OldDescrip),string(new_pers.Status,' - ',@NewDescrip))
end if;
-- Payroll Identifier
if locate(@AuditList,',Payroll Company,') > 0 and update(PayrollIdentifier) then
call AuditLog('PERSON',old_pers.personid,string('Payroll Identifier Updated - ',@PersonName),old_pers.PayrollIdentifier,new_pers.PayrollIdentifier)
end if;
-- Division
if locate(@AuditList,',Division,') > 0 and update(DivisionID) then
select name into @OldDescrip from Division where divisionid = old_pers.DivisionID;
select name into @NewDescrip from Division where divisionid = new_pers.DivisionID;
call AuditLog('PERSON',old_pers.personid,string('Division Updated - ',@PersonName),@OldDescrip,@NewDescrip)
end if;
-- Consultant
if locate(@AuditList,',Consultant,') > 0 and update(StaffID) then
select userid into @OldDescrip from staff where staffid = old_pers.StaffID;
select userid into @NewDescrip from staff where staffid = new_pers.StaffID;
call AuditLog('PERSON',old_pers.personid,string('Consultant Updated - ',@PersonName),@OldDescrip,@NewDescrip);
insert into staffhistory (staffhistoryid, personid, oldstaff, newstaff, whoentered, whenentered) values (uniquekey(new_pers.StaffID),new_pers.personid, old_pers.StaffID, new_pers.StaffID, userstaffid, current timestamp)
end if;
-- Address
if locate(@AuditList,',Address,') > 0 and (update(addr1) or update(addr2) or update(addr3) or update(town) or update(county) or update(country) or update(postcode)) then
call AuditLog('PERSON',old_pers.personid,string('Address Updated - ',@PersonName),string(old_pers.addr1,', ',old_pers.addr2,', ',old_pers.addr3,', ',old_pers.town,', ',old_pers.county,', ',old_pers.postcode,', ',old_pers.country),string(new_pers.addr1,', ',new_pers.addr2,', ',new_pers.addr3,', ',new_pers.town,', ',new_pers.county,', ',new_pers.postcode,', ',new_pers.country))
end if;
-- Alert
if locate(@AuditList,',Alert,') > 0 and update(Alert) then
call AuditLog('PERSON',old_pers.personid,string('Alert Updated - ',@PersonName),old_pers.Alert,new_pers.Alert)
end if;
-- Name
if locate(@AuditList,',Name,') > 0 then
if update(name) then
call AuditLog('PERSON',old_pers.personid,string('Name Updated - ',@PersonName),old_pers.name,new_pers.name)
end if;
-- Forenames
if update(Forenames) then
call AuditLog('PERSON',old_pers.personid,string('Forenames Updated - ',@PersonName),old_pers.Forenames,new_pers.Forenames)
end if;
-- Surname
if update(Surname) then
call AuditLog('PERSON',old_pers.personid,string('Surname Updated - ',@PersonName),old_pers.Surname,new_pers.Surname)
end if;
-- Sslutation
if update(Sslutation) then
call AuditLog('PERSON',old_pers.personid,string('Salutation Updated - ',@PersonName),old_pers.Salutation,new_pers.Salutation)
end if;
-- Keyname
if update(Keyname) then
call AuditLog('PERSON',old_pers.personid,string('Keyname Updated - ',@PersonName),old_pers.Keyname,new_pers.Keyname)
end if
end if;
-- Payroll Number
if locate(@AuditList,',Payroll Number,') > 0 and update(PayrollNumber) then
call AuditLog('PERSON',old_pers.personid,string('Payroll Number Updated - ',@PersonName),old_pers.PayrollNumber,new_pers.PayrollNumber)
end if;
-- AWR Exempt
if locate(@AuditList,',AWR Exemption Reason,') > 0 and update(directlyemployed) then
call AuditLog('PERSON',old_pers.personid,string('AWR Exemption Reason Updated - ',@PersonName),string(old_pers.directlyemployed),string(new_pers.directlyemployed))
end if;
-- Source
if locate(@AuditList,',Source,') > 0 and update(originid) then
select descrip into @OldDescrip from origin where originid = old_pers.originid;
select descrip into @NewDescrip from origin where originid = new_pers.originid;
call AuditLog('PERSON',old_pers.personid,string('Source Updated - ',@PersonName),string(@OldDescrip),string(@NewDescrip))
end if;
-- Unsubscribe To Marketing
if locate(@AuditList,',Unsubscribe To Marketing,') > 0 and update(UnsubscribeToMarketing) then
call AuditLog('PERSON',old_pers.personid,string('Unsubscribe To Marketing - ',@PersonName),string(old_pers.UnsubscribeToMarketing),string(new_pers.UnsubscribeToMarketing))
end if;
end
}
go
create trigger "PersonUnsubscribe" after update of "UnsubscribeToMarketing"
order 2500 on "pears"."Person"
referencing old as "old_name" new as "new_name"
for each row /* WHEN( search_condition ) */
begin
declare "emailaddr" char(100);
declare "existingemail" integer;
if "new_name"."UnsubscribeToMarketing" = 1 then
select "isnull"("getphone"('P','E-mail',"old_name"."personid"),'') into "emailaddr";
select "count"("email") into "existingemail" from "UnsubscribeToMarketing" where "email" = "emailaddr";
if "emailaddr" <> '' and "existingemail" = 0 then
insert into "UnsubscribeToMarketing"( "email" ) values( "emailaddr" ) ;
update "mailerselectionmember" set "unsubscribe" = 1 where "datesubscribed" is not null and "record" = "old_name"."personid" and "mailerselectionid" = any(select "mailerselectionid" from "mailerselection" where "type" = 'P');
delete from "mailerselectionmember" where "record" = "old_name"."personid" and "datesubscribed" is null and "mailerselectionid" = any(select "mailerselectionid" from "mailerselection" where "type" = 'P')
end if
else
delete from "UnsubscribeToMarketing" where "email" = any(select "getphone"('P','E-mail',"old_name"."personid"))
end if
end
go
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Person"."PersonUnsubscribe" IS
{create trigger PersonUnsubscribe
AFTER UPDATE OF UnsubscribeToMarketing
ORDER 2500 ON Person
REFERENCING OLD AS old_name NEW AS new_name
FOR EACH ROW /* WHEN( search_condition ) */
BEGIN
declare emailaddr char(100);
declare existingemail Integer;
if new_name.UnsubscribeToMarketing = 1 then
select isnull(getphone('P','E-mail', old_name.personid),'') into emailaddr;
select count(email) into existingemail from UnsubscribeToMarketing where email=emailaddr;
if emailaddr<>'' and existingemail=0 then
Insert into UnsubscribeToMarketing (email) values (emailaddr);
update mailerselectionmember set unsubscribe=1 where datesubscribed is not null and record=old_name.personid and mailerselectionid in (select mailerselectionid from mailerselection where type='P') ;
delete from mailerselectionmember where record=old_name.personid and datesubscribed is null and mailerselectionid in (select mailerselectionid from mailerselection where type='P') ;
end if;
else
delete from UnsubscribeToMarketing where email in ( select getphone('P','E-mail', old_name.personid) )
end if;
end
}
go
create trigger "psHealthPersonUpdate" after update of "forenames",
"surname","sex","dob","addr1","addr2","addr3","town","county","country","titleforpayroll" order 900 on "pears"."person"
referencing new as "new_rec"
for each row
begin
if "psHealthCanSendPerson"("new_rec"."personid",null) = 1 then
call "psHealthInsertUpdatePerson"("new_rec"."personid",null)
end if
end
go
create trigger "Person_InsertTrim" before insert order 1 on
"pears"."person"
referencing new as "new_p"
for each row
begin
set "new_p"."keyname" = "trim"("new_p"."keyname");
set "new_p"."surname" = "trim"("new_p"."surname");
set "new_p"."payrollnumber" = "trim"("new_p"."payrollnumber")
end
go
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Person"."Person_InsertTrim" IS
{create trigger Person_InsertTrim
before insert order 1 on
pears.person
referencing new as new_p
for each row
begin
set new_p.keyname = trim(new_p.keyname);
set new_p.surname = trim(new_p.surname);
set new_p.payrollnumber = trim(new_p.payrollnumber)
end
}
go
create trigger "Person_UpdateTrim" before update order 3 on
"pears"."person"
referencing new as "new_p"
for each row
begin
set "new_p"."keyname" = "trim"("new_p"."keyname");
set "new_p"."surname" = "trim"("new_p"."surname");
set "new_p"."payrollnumber" = "trim"("new_p"."payrollnumber")
end
go
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Person"."Person_UpdateTrim" IS
{create trigger Person_UpdateTrim
before update order 3 on
pears.person
referencing new as new_p
for each row
begin
set new_p.keyname = trim(new_p.keyname);
set new_p.surname = trim(new_p.surname);
set new_p.payrollnumber = trim(new_p.payrollnumber)
end
}
go
create trigger "InsertStatusPerson" after insert order 2 on
"pears"."Person"
referencing new as "new_co"
for each row
begin
insert into "StatusHistory"( "StatusHistoryID","personid","staffid","newstatus" ) values
( "uniquekey"("new_co"."personid"),"new_co"."personid","userstaffid","new_co"."status" )
end
go
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Person"."InsertStatusPerson" IS
{create trigger InsertStatusPerson
after insert order 2 on
pears.Person
referencing new as new_co
for each row
begin
insert into StatusHistory (StatusHistoryID, personid, staffid, newstatus)
values
(uniquekey(new_co.personid),new_co.personid, userstaffid,new_co.status)
end
}
go
create trigger "UpdateStatusPerson" after update of "status"
order 2 on "pears"."person"
referencing old as "old_co" new as "new_co"
for each row
begin
declare "ID" char(20);
select first "StatusHistoryID" into "ID" from "StatusHistory" where "personid" = "new_co"."personid"
and "datediff"("minute","whenentered",current timestamp) < 1 and "newstatus" = "old_co"."status" and "oldstatus" = "new_co"."status" order by "whenentered" desc;
if "ID" is not null then
delete from "StatusHistory" where "StatusHistoryID" = "ID"
else
insert into "StatusHistory"( "StatusHistoryID","personid","staffid","oldstatus","newstatus" ) values
( "uniquekey"("new_co"."personid"),"new_co"."personid","userstaffid","old_co"."status","new_co"."status" )
end if
end
go
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Person"."UpdateStatusPerson" IS
{create trigger UpdateStatusPerson
after update of status
order 2 on pears.person
referencing old as old_co new as new_co
for each row
begin
declare ID char(20);
select first StatusHistoryID into ID from StatusHistory where personid = new_co.personid
and datediff(minute, whenentered, current timestamp) < 1 and newstatus = old_co.status and oldstatus = new_co.status order by whenentered desc;
if ID is not null then
delete from StatusHistory where StatusHistoryID = ID
else
insert into StatusHistory (StatusHistoryID, personid, staffid, oldstatus, newstatus)
values
(uniquekey(new_co.personid),new_co.personid, userstaffid, old_co.status, new_co.status)
end if;
end
}
go
create trigger "PersonKeyWordsInsert" after insert order 20 on
"pears"."Person"
referencing new as "NewRow"
for each row
begin
insert into "PersonKeyWords"( "PersonID","RefreshRequired" ) on existing update defaults off values( "NewRow"."PersonID",1 )
end
go
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Person"."PersonKeyWordsInsert" IS
{create trigger PersonKeyWordsInsert
after insert order 20 on
pears.Person
referencing new as NewRow
for each row
begin
insert into PersonKeyWords(PersonID,RefreshRequired) on existing update values(NewRow.PersonID,1);
end
}
go
create trigger "PersonKeyWordsUpdate" after update of "Salutation",
"Surname","Forenames","Addr1","Addr2","Addr3","Town","County","PostCode","NI","AppNumber","PayrollNumber" order 21 on
"pears"."Person"
referencing new as "NewRow"
for each row
begin
update "PersonKeyWords" set "RefreshRequired" = 1 where "PersonID" = "NewRow"."PersonID"
end
go
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Person"."PersonKeyWordsUpdate" IS
{create trigger PersonKeyWordsUpdate
after update of Salutation,Surname,Forenames,Addr1,Addr2,Addr3,Town,County,PostCode,NI,AppNumber,PayrollNumber order 21 on
pears.Person
referencing new as NewRow
for each row
begin
update PersonKeyWords set RefreshRequired = 1 where PersonID = NewRow.PersonID;
end
}
go
create trigger "PersonDelete" before delete order 2 on
"pears"."person"
referencing old as "old_name"
for each row
begin
delete from "compliancepersonstatus" where "personid" = "old_name"."personid"
end
go
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Person"."PersonDelete" IS
{create trigger PersonDelete
before delete order 2 on
pears."person"
referencing old as old_name
for each row
begin
delete from "compliancepersonstatus" where "personid" = "old_name"."personid"
end
}
go