====== pears.Pay_Employee ======
<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 =====
Data related to a persons employment by the agency. One to one relationship with Person. Only created when required.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **PersonID** | char(20) | NOT NULL |  |  |
| P45Requested | smallint | NULL | 0 |  |
| TempaidRecordRequired | smallint | NULL | 1 |  |
| BankName | char(30) | NULL |  |  |
| BankAddr1 | char(30) | NULL |  |  |
| BankAddr2 | char(30) | NULL |  |  |
| BankSortCode | char(8) | NULL |  |  |
| BankAcName | char(50) | NULL |  |  |
| BankAcNo | char(15) | NULL |  |  |
| BankRef | char(20) | NULL |  |  |
| HolidayPayNotes | long varchar | NULL |  |  |
| PayMethod | tinyint | NULL | 1 | 1=Bacs 2=Cash 3=Cheque 4=Chaps 5=Intl |
| PayFrequency | tinyint | NULL | 52 | 52=weekly 4=Qtrly 12=Monthly 13=4Wkly 25=2Wkly |
| TaxMethod | tinyint | NULL | 1 | 1=PAYE 2=Ltd 3=Selfemp |
| CompanyName | char(50) | NULL |  |  |
| RegNo | char(20) | NULL |  |  |
| VATRegistered | tinyint | NULL | 0 |  |
| VATNumber | char(20) | NULL |  |  |
| TimesheetsWithPayslip | tinyint | NULL | 0 |  |
| TransferBatch | integer | NULL |  |  |
| ExtNumber | integer | NULL |  |  |
| TaxCode | char(6) | NULL |  |  |
| NILetter | char(1) | NULL |  |  |
| Week1Ind | smallint | NULL |  |  |
| ExtraCode | char(30) | NULL |  |  |
| SecondaryAgencyID | char(20) | NULL |  | If candidate is registered with a Secondary Agency company |
| SecondaryAgencyRef | char(20) | NULL |  |  |
| CISTemplateID | char(20) | NULL |  |  |
| CompositeID | char(20) | NULL |  |  |
| CompositePayCoReference | char(60) | NULL |  |  |
| IBAN | char(34) | NULL |  |  |
| SWIFTBIC | char(11) | NULL |  |  |
| UniqueTaxReference | char(20) | NULL |  |  |
| CISVerificationNumber | char(20) | NULL |  |  |
| CISTaxTreatment | char(10) | NULL |  |  |
| CISVerificationDate | date | NULL |  |  |
| CIS07TemplateID | char(20) | NULL |  |  |
| CISEffectiveDate | date | NULL |  |  |
| CurrencyCode | char(3) | NULL |  | Used for exporting to banking systems |
| EmailPayslips | tinyint | NULL | 0 |  |
| TextPaymentAdvice | tinyint | NULL | 0 |  |
| NoHolidayPay | smallint | NULL |  |  |
| WhenEntered | timestamp | NULL | current timestamp |  |
| WhoEntered | char(20) | NULL |  |  |
| ExcludeForceNI | smallint | NULL | 0 | Visible if ForceNI is on. Use to exclude from NI invoicing when LTD tax method |
| HMRCEngagementDetail | char(1) | NULL | 'Z' |  |
| PayrollAddr1 | char(40) | NULL |  |  |
| PayrollAddr2 | char(40) | NULL |  |  |
| PayrollAddr3 | char(40) | NULL |  |  |
| PayrollAddr4 | char(40) | NULL |  |  |
| PayrollPostcode | char(30) | NULL |  |  |
| ForeignAwaitingNI | tinyint | NULL | 0 |  |
| SupplierCode | char(12) | NULL |  | Self bill account for umbrella etc. |
| SecondaryAgencyCandidateStatus | char(1) | NULL |  |  |
| LSM_TransferBatch | integer | NULL |  |  |

===== Primary Key =====
  * PersonID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| person | PersonID | [[database:tables:pears_person|pears.Person (personid)]] | NOT NULL;  |
| Company | SecondaryAgencyID | [[database:tables:pears_company|pears.Company (companyid)]] | ON DELETE SET NULL |
| CISTemplate | CISTemplateID | [[database:tables:pears_cistemplate|pears.CISTemplate (CISTemplateID)]] | ON DELETE SET NULL |
| Composite | CompositeID | [[database:tables:pears_composite|pears.Composite (CompositeId)]] | ON DELETE SET NULL |
| CIS07Template | CIS07TemplateID | [[database:tables:pears_cis07template|pears.CIS07Template (CIS07TemplateID)]] | ON DELETE CASCADE |
| SecondaryAgencyCandidateStatus | SecondaryAgencyCandidateStatus | [[database:tables:pears_secondaryagencycandidatestatus|pears.SecondaryAgencyCandidateStatus (SecondaryAgencyCandidateStatusID)]] |  |

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

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| payemp_extnumber | Index | ExtNumber |  |
| payemp_transferbatch | Index | TransferBatch |  |
| payemp_p45requested | Index | P45Requested |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| payemployeeupdate | before | update order 1 |
| payemployeeinsert | before | insert order 2 |
| PayEmployeeAudit | before | update of "PayMethod", "CompositeID","TaxCode","TaxMethod","NILetter","VATNumber","VATRegistered","CompanyName","RegNo","ExtNumber","BankSortCode","BankAcNo","Suppliercode", "BankAcName","BankRef","UniqueTaxReference","CISTaxTreatment","CISVerificationDate","CISVerificationNumber","CISEffectiveDate","HMRCEngagementDetail" order 3 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."Pay_Employee"
-- Table comment: Data related to a persons employment by the agency. One to one relationship with Person. Only created when required.
-- Statement count: 24

CREATE TABLE "pears"."Pay_Employee" (
    "PersonID"                       char(20) NOT NULL
   ,"P45Requested"                   smallint NULL DEFAULT 0
   ,"TempaidRecordRequired"          smallint NULL DEFAULT 1
   ,"BankName"                       char(30) NULL
   ,"BankAddr1"                      char(30) NULL
   ,"BankAddr2"                      char(30) NULL
   ,"BankSortCode"                   char(8) NULL
   ,"BankAcName"                     char(50) NULL
   ,"BankAcNo"                       char(15) NULL
   ,"BankRef"                        char(20) NULL
   ,"HolidayPayNotes"                long varchar NULL
   ,"PayMethod"                      tinyint NULL DEFAULT 1
   ,"PayFrequency"                   tinyint NULL DEFAULT 52
   ,"TaxMethod"                      tinyint NULL DEFAULT 1
   ,"CompanyName"                    char(50) NULL
   ,"RegNo"                          char(20) NULL
   ,"VATRegistered"                  tinyint NULL DEFAULT 0
   ,"VATNumber"                      char(20) NULL
   ,"TimesheetsWithPayslip"          tinyint NULL DEFAULT 0
   ,"TransferBatch"                  integer NULL
   ,"ExtNumber"                      integer NULL
   ,"TaxCode"                        char(6) NULL
   ,"NILetter"                       char(1) NULL
   ,"Week1Ind"                       smallint NULL
   ,"ExtraCode"                      char(30) NULL
   ,"SecondaryAgencyID"              char(20) NULL
   ,"SecondaryAgencyRef"             char(20) NULL
   ,"CISTemplateID"                  char(20) NULL
   ,"CompositeID"                    char(20) NULL
   ,"CompositePayCoReference"        char(60) NULL
   ,"IBAN"                           char(34) NULL
   ,"SWIFTBIC"                       char(11) NULL
   ,"UniqueTaxReference"             char(20) NULL
   ,"CISVerificationNumber"          char(20) NULL
   ,"CISTaxTreatment"                char(10) NULL
   ,"CISVerificationDate"            date NULL
   ,"CIS07TemplateID"                char(20) NULL
   ,"CISEffectiveDate"               date NULL
   ,"CurrencyCode"                   char(3) NULL
   ,"EmailPayslips"                  tinyint NULL DEFAULT 0
   ,"TextPaymentAdvice"              tinyint NULL DEFAULT 0
   ,"NoHolidayPay"                   smallint NULL
   ,"WhenEntered"                    timestamp NULL DEFAULT current timestamp
   ,"WhoEntered"                     char(20) NULL
   ,"ExcludeForceNI"                 smallint NULL DEFAULT 0
   ,"HMRCEngagementDetail"           char(1) NULL DEFAULT 'Z'
   ,"PayrollAddr1"                   char(40) NULL
   ,"PayrollAddr2"                   char(40) NULL
   ,"PayrollAddr3"                   char(40) NULL
   ,"PayrollAddr4"                   char(40) NULL
   ,"PayrollPostcode"                char(30) NULL
   ,"ForeignAwaitingNI"              tinyint NULL DEFAULT 0
   ,"SupplierCode"                   char(12) NULL
   ,"SecondaryAgencyCandidateStatus" char(1) NULL
   ,"LSM_TransferBatch"              integer NULL
   ,PRIMARY KEY ("PersonID" ASC) 
)
go


COMMENT ON COLUMN "pears"."Pay_Employee"."PayMethod" IS 
	'1=Bacs 2=Cash 3=Cheque 4=Chaps 5=Intl'
go


COMMENT ON COLUMN "pears"."Pay_Employee"."PayFrequency" IS 
	'52=weekly 4=Qtrly 12=Monthly 13=4Wkly 25=2Wkly'
go


COMMENT ON COLUMN "pears"."Pay_Employee"."TaxMethod" IS 
	'1=PAYE 2=Ltd 3=Selfemp'
go


COMMENT ON COLUMN "pears"."Pay_Employee"."SecondaryAgencyID" IS 
	'If candidate is registered with a Secondary Agency company'
go


COMMENT ON COLUMN "pears"."Pay_Employee"."CurrencyCode" IS 
	'Used for exporting to banking systems'
go


COMMENT ON COLUMN "pears"."Pay_Employee"."ExcludeForceNI" IS 
	'Visible if ForceNI is on. Use to exclude from NI invoicing when LTD tax method'
go


COMMENT ON COLUMN "pears"."Pay_Employee"."SupplierCode" IS 
	'Self bill account for umbrella etc.'
go


COMMENT ON TABLE "pears"."Pay_Employee" IS 
	'Data related to a persons employment by the agency. One to one relationship with Person. Only created when required.'
go


ALTER TABLE "pears"."Pay_Employee"
    ADD NOT NULL FOREIGN KEY "person" ("PersonID" ASC)
    REFERENCES "pears"."Person" ("personid")
go


ALTER TABLE "pears"."Pay_Employee"
    ADD FOREIGN KEY "Company" ("SecondaryAgencyID" ASC)
    REFERENCES "pears"."Company" ("companyid")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."Pay_Employee"
    ADD FOREIGN KEY "CISTemplate" ("CISTemplateID" ASC)
    REFERENCES "pears"."CISTemplate" ("CISTemplateID")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."Pay_Employee"
    ADD FOREIGN KEY "Composite" ("CompositeID" ASC)
    REFERENCES "pears"."Composite" ("CompositeId")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."Pay_Employee"
    ADD FOREIGN KEY "CIS07Template" ("CIS07TemplateID" ASC)
    REFERENCES "pears"."CIS07Template" ("CIS07TemplateID")
    ON DELETE CASCADE
go


ALTER TABLE "pears"."Pay_Employee"
    ADD FOREIGN KEY "SecondaryAgencyCandidateStatus" ("SecondaryAgencyCandidateStatus" ASC)
    REFERENCES "pears"."SecondaryAgencyCandidateStatus" ("SecondaryAgencyCandidateStatusID")
go


CREATE INDEX "payemp_extnumber" ON "pears"."Pay_Employee"
    ( "ExtNumber" )
go


CREATE INDEX "payemp_transferbatch" ON "pears"."Pay_Employee"
    ( "TransferBatch" )
go


CREATE INDEX "payemp_p45requested" ON "pears"."Pay_Employee"
    ( "P45Requested" )
go


create trigger "payemployeeupdate" before update order 1 on
"pears"."Pay_Employee"
referencing old as "old_payemp" new as "new_payemp"
for each row
begin
  if "old_payemp"."transferbatch" > 0 then
    set "new_payemp"."transferbatch" = 0;
    update "accordemployee" set "transferbatch" = 0 where "personid" = "old_payemp"."personid"
  end if;
  if "new_payemp"."p45requested" = 1 then
    update "person" set "p46declaration" = 0 where "personid" = "old_payemp"."personid"
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Pay_Employee"."payemployeeupdate" IS 
{create trigger payemployeeupdate 
 before update order 1 on
pears.Pay_Employee
referencing old as old_payemp new as new_payemp
for each row
begin
  if  old_payemp.transferbatch > 0 then
      set new_payemp.transferbatch = 0;
     update accordemployee set transferbatch = 0 where personid = old_payemp.personid;
  end if;
  if  new_payemp.p45requested = 1 then
    update person set p46declaration = 0 where personid = old_payemp.personid
  end if;
end
}
go


create trigger "payemployeeinsert" before insert order 2 on
"pears"."Pay_Employee"
referencing new as "new_payemp"
for each row
begin
  if "new_payemp"."taxcode" is null then
    set "new_payemp"."taxcode" = (select first "isnull"("defaulttaxcode",'BR') from "params");
    set "new_payemp"."week1ind" = (select first "isnull"("defaultweek1ind",0) from "params")
  else
    if "new_payemp"."week1ind" is null then
      set "new_payemp"."week1ind" = 0
    end if end if;
  if "new_payemp"."niletter" is null then
    set "new_payemp"."niletter" = (select first "isnull"("defaultniletter",'A') from "params")
  end if;
  set "new_payemp"."WhoEntered" = "userstaffid"
exception
  when others then
    set "new_payemp"."WhoEntered" = null
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Pay_Employee"."payemployeeinsert" IS 
{create trigger payemployeeinsert 
 before insert order 2 on
pears.Pay_Employee
referencing new as new_payemp
for each row
begin
  if new_payemp.taxcode is null then
    set new_payemp.taxcode=(select first isnull(defaulttaxcode,'BR') from params);
    set new_payemp.week1ind=(select first isnull(defaultweek1ind,0) from params)
  else
    if new_payemp.week1ind is null then
      set new_payemp.week1ind=0
    end if
  end if;
  if new_payemp.niletter is null then
    set new_payemp.niletter=(select first isnull(defaultniletter,'A') from params)
  end if;
  set new_payemp.WhoEntered=userstaffid
exception
  when others then
    set new_payemp.WhoEntered=null
end
}
go


create trigger "PayEmployeeAudit" before update of "PayMethod",
"CompositeID","TaxCode","TaxMethod","NILetter","VATNumber","VATRegistered","CompanyName","RegNo","ExtNumber","BankSortCode","BankAcNo","Suppliercode",
"BankAcName","BankRef","UniqueTaxReference","CISTaxTreatment","CISVerificationDate","CISVerificationNumber","CISEffectiveDate","HMRCEngagementDetail" order 3 on
"pears"."Pay_Employee"
referencing old as "old_pers" new as "new_pers"
for each row
when(exists(select * from "AuditItems" where "AreaName" = 'Person Pay' 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 Pay' and "AuditFlag" = 1;
  set @PersonName = (select "Name" from "Person" where "PersonID" = "isnull"("old_pers"."PersonID","new_pers"."PersonID"));
  -- Payment Method
  if "locate"(@AuditList,',Payment Method,') > 0 and update("PayMethod") then
    call "AuditLog"('PERSON',"old_pers"."personid","string"('Payment Method - ',@PersonName),"string"("old_pers"."PayMethod",case "old_pers"."PayMethod" when 1 then '- BACS' when 2 then '- Cash' when 3 then '- Cheque' else '- Other' end),
    "string"("new_pers"."PayMethod",case "new_pers"."PayMethod" when 1 then '- BACS' when 2 then '- Cash' when 3 then '- Cheque' else '- Other' end))
  -- Tax Code
  end if;
  if "locate"(@AuditList,',Tax Code,') > 0 and update("TaxCode") then
    call "AuditLog"('PERSON',"old_pers"."personid","string"('Tax Code - ',@PersonName),"old_pers"."TaxCode","new_pers"."TaxCode")
  end if;
  -- Tax Method
  if "locate"(@AuditList,',Tax Method,') > 0 and update("TaxMethod") then
    call "AuditLog"('PERSON',"old_pers"."personid","string"('Tax Method - ',@PersonName),"string"("old_pers"."TaxMethod",case "old_pers"."TaxMethod" when 1 then '- PAYES' when 2 then '- Ltd Co' when 3 then '- SelfEmp' else '- Other' end),
    "string"("new_pers"."TaxMethod",case "new_pers"."TaxMethod" when 1 then '- PAYES' when 2 then '- Ltd Co' when 3 then '- SelfEmp' else '- Other' end))
  -- NI Letter
  end if;
  if "locate"(@AuditList,',NI Letter,') > 0 and update("NILetter") then
    call "AuditLog"('PERSON',"old_pers"."personid","string"('NI Letter - ',@PersonName),"old_pers"."NILetter","new_pers"."NILetter")
  end if;
  -- VAT Number
  if "locate"(@AuditList,',VAT Number,') > 0 and update("VATNumber") then
    call "AuditLog"('PERSON',"old_pers"."personid","string"('VAT Number - ',@PersonName),"old_pers"."VATNumber","new_pers"."VATNumber")
  end if;
  -- VAT Registered
  if "locate"(@AuditList,',VAT Registered,') > 0 and update("VATRegistered") then
    call "AuditLog"('PERSON',"old_pers"."personid","string"('VAT Registered - ',@PersonName),"old_pers"."VATRegistered","new_pers"."VATRegistered")
  end if;
  -- Company Name
  if "locate"(@AuditList,',Company Name,') > 0 and update("CompanyName") then
    call "AuditLog"('PERSON',"old_pers"."personid","string"('Company Name - ',@PersonName),"old_pers"."CompanyName","new_pers"."CompanyName")
  end if;
  -- Company Registration Number
  if "locate"(@AuditList,',Company Registration Number,') > 0 and update("RegNo") then
    call "AuditLog"('PERSON',"old_pers"."personid","string"('Company Registration Number - ',@PersonName),"old_pers"."RegNo","new_pers"."RegNo")
  end if;
  -- External Ref Number
  if "locate"(@AuditList,',External Ref Number,') > 0 and update("ExtNumber") then
    call "AuditLog"('PERSON',"old_pers"."personid","string"('External Ref Number - ',@PersonName),"old_pers"."ExtNumber","new_pers"."ExtNumber")
  end if;
  -- HMRC Engagement Details
  if "locate"(@AuditList,',HMRC Engagement Details,') > 0 and update("HMRCEngagementDetail") then
    call "AuditLog"('PERSON',"old_pers"."personid","string"('HMRC Engagement Details - ',@PersonName),"old_pers"."HMRCEngagementDetail","new_pers"."HMRCEngagementDetail")
  end if;
  -- Bank Account Details
  if "locate"(@AuditList,',Bank Account Details,') > 0 and(update("BankSortCode") or update("BankAcNo") or update("BankAcName") or update("BankRef")) then
    call "AuditLog"('PERSON',"old_pers"."personid","string"('Bank Details - ',@PersonName),"string"('Sort Code ',"old_pers"."BankSortCode",', Acc No. ',"old_pers"."BankAcNo",', Acc Name ',"old_pers"."BankAcName",', Ref. ',"old_pers"."BankRef"),"string"('Sort Code ',"new_pers"."BankSortCode",', Acc No. ',"new_pers"."BankAcNo",', Acc Name ',"new_pers"."BankAcName",', Ref. ',"new_pers"."BankRef"))
  end if;
  -- CompositeID
  if "locate"(@AuditList,',Composite Pay Company,') > 0 and(update("CompositeID")) then
    call "AuditLog"('PERSON',"old_pers"."personid","string"('Composite Pay Company - ',@PersonName),(select "templatename" from "composite" as "c" where "c"."compositeid" = "old_pers"."compositeid"),(select "templatename" from "composite" as "c" where "c"."compositeid" = "new_pers"."compositeid"))
  end if;
  -- SupplierCode
  if "locate"(@AuditList,',Supplier Code,') > 0 and(update("SupplierCode")) then
    call "AuditLog"('PERSON',"old_pers"."personid","string"('Supplier Code - ',@PersonName),"old_pers"."SupplierCode","new_pers"."SupplierCode")
  end if;
  -- CIS (New Style, post 2007)
  if "locate"(@AuditList,',CIS (post 2007),') > 0 and(update("CISVerificationNumber") or update("CISVerificationDate") or update("UniqueTaxReference") or update("CISTaxTreatment")) or update("CISEffectiveDate") then
    call "AuditLog"('PERSON',"old_pers"."personid","string"('CIS Details - ',@PersonName),"string"('Unique Tax Reference ',"old_pers"."UniqueTaxReference",', CIS Verification Number ',"old_pers"."CISVerificationNumber",', CIS Verification Date ',"old_pers"."CISVerificationDate",', CIS Tax Treatment ',"old_pers"."CISTaxTreatment",', CIS Effective Date ',"old_pers"."CISEffectiveDate"),"string"('Unique Tax Reference ',"new_pers"."UniqueTaxReference",', CIS Verification Number ',"new_pers"."CISVerificationNumber",', CIS Verification Date ',"new_pers"."CISVerificationDate",', CIS Tax Treatment ',"new_pers"."CISTaxTreatment",', CIS Effective Date ',"new_pers"."CISEffectiveDate"))
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Pay_Employee"."PayEmployeeAudit" IS 
{create trigger PayEmployeeAudit 
 before update of PayMethod,
CompositeID,TaxCode,TaxMethod,NILetter,VATNumber,VATRegistered,CompanyName,RegNo,ExtNumber,BankSortCode,BankAcNo,Suppliercode,
BankAcName,BankRef,UniqueTaxReference,CISTaxTreatment,CISVerificationDate,CISVerificationNumber,CISEffectiveDate,HMRCEngagementDetail order 3 on
pears.Pay_Employee
referencing old as old_pers new as new_pers
for each row
when(exists(select * from AuditItems where AreaName = 'Person Pay' 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 Pay' and AuditFlag = 1;
  set @PersonName=(select Name from Person where PersonID = isnull(old_pers.PersonID,new_pers.PersonID));
  -- Payment Method
  if locate(@AuditList,',Payment Method,') > 0 and update(PayMethod) then
    call AuditLog('PERSON',old_pers.personid,string('Payment Method - ',@PersonName),string(old_pers.PayMethod,case old_pers.PayMethod when 1 then '- BACS' when 2 then '- Cash' when 3 then '- Cheque' else '- Other'
    end),string(new_pers.PayMethod,case new_pers.PayMethod when 1 then '- BACS' when 2 then '- Cash' when 3 then '- Cheque' else '- Other'
    -- Tax Code
    end))
  end if;
  if locate(@AuditList,',Tax Code,') > 0 and update(TaxCode) then
    call AuditLog('PERSON',old_pers.personid,string('Tax Code - ',@PersonName),old_pers.TaxCode,new_pers.TaxCode)
  end if;
  -- Tax Method
  if locate(@AuditList,',Tax Method,') > 0 and update(TaxMethod) then
    call AuditLog('PERSON',old_pers.personid,string('Tax Method - ',@PersonName),string(old_pers.TaxMethod,case old_pers.TaxMethod when 1 then '- PAYES' when 2 then '- Ltd Co' when 3 then '- SelfEmp' else '- Other'
    end),string(new_pers.TaxMethod,case new_pers.TaxMethod when 1 then '- PAYES' when 2 then '- Ltd Co' when 3 then '- SelfEmp' else '- Other'
    -- NI Letter
    end))
  end if;
  if locate(@AuditList,',NI Letter,') > 0 and update(NILetter) then
    call AuditLog('PERSON',old_pers.personid,string('NI Letter - ',@PersonName),old_pers.NILetter,new_pers.NILetter)
  end if;
  -- VAT Number
  if locate(@AuditList,',VAT Number,') > 0 and update(VATNumber) then
    call AuditLog('PERSON',old_pers.personid,string('VAT Number - ',@PersonName),old_pers.VATNumber,new_pers.VATNumber)
  end if;
  -- VAT Registered
  if locate(@AuditList,',VAT Registered,') > 0 and update(VATRegistered) then
    call AuditLog('PERSON',old_pers.personid,string('VAT Registered - ',@PersonName),old_pers.VATRegistered,new_pers.VATRegistered)
  end if;
  -- Company Name
  if locate(@AuditList,',Company Name,') > 0 and update(CompanyName) then
    call AuditLog('PERSON',old_pers.personid,string('Company Name - ',@PersonName),old_pers.CompanyName,new_pers.CompanyName)
  end if;
  -- Company Registration Number
  if locate(@AuditList,',Company Registration Number,') > 0 and update(RegNo) then
    call AuditLog('PERSON',old_pers.personid,string('Company Registration Number - ',@PersonName),old_pers.RegNo,new_pers.RegNo)
  end if;
  -- External Ref Number
  if locate(@AuditList,',External Ref Number,') > 0 and update(ExtNumber) then
    call AuditLog('PERSON',old_pers.personid,string('External Ref Number - ',@PersonName),old_pers.ExtNumber,new_pers.ExtNumber)
  end if;
  -- HMRC Engagement Details
  if locate(@AuditList,',HMRC Engagement Details,') > 0 and update(HMRCEngagementDetail) then
    call AuditLog('PERSON',old_pers.personid,string('HMRC Engagement Details - ',@PersonName),old_pers.HMRCEngagementDetail,new_pers.HMRCEngagementDetail)
  end if;
  -- Bank Account Details
  if locate(@AuditList,',Bank Account Details,') > 0 and(update(BankSortCode) or update(BankAcNo) or update(BankAcName) or update(BankRef)) then
    call AuditLog('PERSON',old_pers.personid,string('Bank Details - ',@PersonName),string('Sort Code ',old_pers.BankSortCode,', Acc No. ',old_pers.BankAcNo,', Acc Name ',old_pers.BankAcName,', Ref. ',old_pers.BankRef),string('Sort Code ',new_pers.BankSortCode,', Acc No. ',new_pers.BankAcNo,', Acc Name ',new_pers.BankAcName,', Ref. ',new_pers.BankRef))
  end if;
  -- CompositeID
  if locate(@AuditList,',Composite Pay Company,') > 0 and(update(CompositeID) ) then
    call AuditLog('PERSON',old_pers.personid,string('Composite Pay Company - ',@PersonName),(select templatename from composite c where c.compositeid =old_pers.compositeid),(select templatename from composite c where c.compositeid =new_pers.compositeid) )
  end if;
  -- SupplierCode
  if locate(@AuditList,',Supplier Code,') > 0 and(update(SupplierCode) ) then
    call AuditLog('PERSON',old_pers.personid,string('Supplier Code - ',@PersonName),old_pers.SupplierCode,new_pers.SupplierCode )
  end if;
  -- CIS (New Style, post 2007)
  if locate(@AuditList,',CIS (post 2007),') > 0 and(update(CISVerificationNumber) or update(CISVerificationDate) or update(UniqueTaxReference) or update(CISTaxTreatment)) or update(CISEffectiveDate) then
    call AuditLog('PERSON',old_pers.personid,string('CIS Details - ',@PersonName),string('Unique Tax Reference ',old_pers.UniqueTaxReference,', CIS Verification Number ',old_pers.CISVerificationNumber,', CIS Verification Date ',old_pers.CISVerificationDate,', CIS Tax Treatment ',old_pers.CISTaxTreatment,', CIS Effective Date ',old_pers.CISEffectiveDate),string('Unique Tax Reference ',new_pers.UniqueTaxReference,', CIS Verification Number ',new_pers.CISVerificationNumber,', CIS Verification Date ',new_pers.CISVerificationDate,', CIS Tax Treatment ',new_pers.CISTaxTreatment,', CIS Effective Date ',new_pers.CISEffectiveDate))
  end if
end
}
go
</code>