pears.Pay_Employment [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.Pay_Employment ======
<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 =====
Contains records of periods of employment of temps by the Agency (operating the Pears system) itself.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **Pay_EmploymentID** | char(20) | NOT NULL |  |  |
| PersonID | char(20) | NOT NULL |  |  |
| StartDate | date | NULL |  |  |
| EndDate | date | NULL |  |  |
| DateContractReturned | date | NULL |  |  |
| ExpenseBenefitOptOutID | char(20) | NULL |  |  |
| MultiSiteWorkingAgreement | smallint | NULL |  |  |
| payrollnumber | char(15) | NULL |  |  |
| TaxMethod | tinyint | NULL |  | 1=PAYE 2=Ltd 3=Selfemp |
| CompanyName | char(50) | NULL |  |  |
| CompanyRegNo | char(20) | NULL |  |  |
| HMRCEngagementDetail | char(1) | NULL |  |  |
| UTR | char(20) | NULL |  |  |
| PayrollAddr1 | char(40) | NULL |  |  |
| PayrollAddr2 | char(40) | NULL |  |  |
| PayrollAddr3 | char(40) | NULL |  |  |
| PayrollAddr4 | char(40) | NULL |  |  |
| PayrollPostcode | char(30) | NULL |  |  |
| PayrollFullAddr | long varchar | NULL |  |  |
| VATRegistered | tinyint | NULL |  |  |
| VATNumber | char(50) | NULL |  |  |
| CompositeID | char(20) | NULL |  |  |

===== Primary Key =====
  * Pay_EmploymentID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| person | PersonID | [[database:tables:pears_person|pears.Person (personid)]] | NOT NULL;  |
| ExpenseBenefitOptOut | ExpenseBenefitOptOutID | [[database:tables:pears_expensebenefitoptout|pears.ExpenseBenefitOptOut (ExpenseBenefitOptOutID)]] |  |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_temptimesheet|pears.TempTimeSheet]] | Pay_Employment | Pay_EmploymentID | Pay_EmploymentID |

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| WeEmploy_PersonID | Index | PersonID |  |
| payemp_startdate | Index | StartDate |  |
| pay_employment_taxmethod_enddate | Index | TaxMethod, EndDate |  |
| pay_employment_payrollnumber | Index | payrollnumber |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| PayEmploymentAudit | after | update of "EndDate", "StartDate","CompanyName","CompanyRegNo","HMRCEngagementDetail","UTR","VATNumber", "VATRegistered" order 1 |
| PayEmploymentAuditInsert | after | insert order 1 |
| PayEmploymentInsert | before | insert order 2 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."Pay_Employment"
-- Table comment: Contains records of periods of employment of temps by the Agency (operating the Pears system) itself.
-- Statement count: 15

CREATE TABLE "pears"."Pay_Employment" (
    "Pay_EmploymentID"               char(20) NOT NULL
   ,"PersonID"                       char(20) NOT NULL
   ,"StartDate"                      date NULL
   ,"EndDate"                        date NULL
   ,"DateContractReturned"           date NULL
   ,"ExpenseBenefitOptOutID"         char(20) NULL
   ,"MultiSiteWorkingAgreement"      smallint NULL
   ,"payrollnumber"                  char(15) NULL
   ,"TaxMethod"                      tinyint NULL
   ,"CompanyName"                    char(50) NULL
   ,"CompanyRegNo"                   char(20) NULL
   ,"HMRCEngagementDetail"           char(1) NULL
   ,"UTR"                            char(20) NULL
   ,"PayrollAddr1"                   char(40) NULL
   ,"PayrollAddr2"                   char(40) NULL
   ,"PayrollAddr3"                   char(40) NULL
   ,"PayrollAddr4"                   char(40) NULL
   ,"PayrollPostcode"                char(30) NULL
   ,"PayrollFullAddr"                long varchar NULL
   ,"VATRegistered"                  tinyint NULL
   ,"VATNumber"                      char(50) NULL
   ,"CompositeID"                    char(20) NULL
   ,PRIMARY KEY ("Pay_EmploymentID" ASC) 
)
go


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


COMMENT ON TABLE "pears"."Pay_Employment" IS 
	'Contains records of periods of employment of temps by the Agency (operating the Pears system) itself.'
go


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


ALTER TABLE "pears"."Pay_Employment"
    ADD FOREIGN KEY "ExpenseBenefitOptOut" ("ExpenseBenefitOptOutID" ASC)
    REFERENCES "pears"."ExpenseBenefitOptOut" ("ExpenseBenefitOptOutID")
go


CREATE INDEX "WeEmploy_PersonID" ON "pears"."Pay_Employment"
    ( "PersonID" )
go


CREATE INDEX "payemp_startdate" ON "pears"."Pay_Employment"
    ( "StartDate" )
go


CREATE INDEX "pay_employment_taxmethod_enddate" ON "pears"."Pay_Employment"
    ( "TaxMethod","EndDate" )
go


CREATE INDEX "pay_employment_payrollnumber" ON "pears"."Pay_Employment"
    ( "payrollnumber" )
go


create trigger "PayEmploymentAudit" after update of "EndDate",
"StartDate","CompanyName","CompanyRegNo","HMRCEngagementDetail","UTR","VATNumber",
"VATRegistered" order 1 on "pears"."Pay_Employment"
referencing old as "old_name" new as "new_name"
for each row
begin
  declare "PName" char(250);
  set "PName" = (select "Name" from "Person" where "PersonID" = "isnull"("old_name"."PersonID","new_name"."PersonID"));
  if update("StartDate") and(exists(select * from "AuditItems" where "AreaName" = 'Person' and "AuditFlag" = 1 and "ItemName" = 'Employment Start Date')) then
    call "AuditLog"('PERSON',"old_name"."personid","string"('Employment Start Date Updated - ',"PName"),"string"("old_name"."startdate"),"string"("new_name"."startdate"))
  end if;
  if update("EndDate") and(exists(select * from "AuditItems" where "AreaName" = 'Person' and "AuditFlag" = 1 and "ItemName" = 'Employment End Date')) then
    call "AuditLog"('PERSON',"old_name"."personid","string"('Employment End Date Updated - ',"PName"),"string"("old_name"."enddate"),"string"("new_name"."enddate"))
  end if;
  if update("CompanyName") and(exists(select * from "AuditItems" where "AreaName" = 'Person' and "AuditFlag" = 1 and "ItemName" = 'Employment Company Name')) then
    call "AuditLog"('PERSON',"old_name"."personid","string"('Employment Company Name - ',"PName"),"string"("old_name"."CompanyName"),"string"("new_name"."CompanyName"))
  end if;
  if update("CompanyRegNo") and(exists(select * from "AuditItems" where "AreaName" = 'Person' and "AuditFlag" = 1 and "ItemName" = 'Employment Company Reg. No')) then
    call "AuditLog"('PERSON',"old_name"."personid","string"('Employment Company Reg. No - ',"PName"),"string"("old_name"."CompanyRegNo"),"string"("new_name"."CompanyRegNo"))
  end if;
  if update("HMRCEngagementDetail") and(exists(select * from "AuditItems" where "AreaName" = 'Person' and "AuditFlag" = 1 and "ItemName" = 'Employment HMRC Engagement Details')) then
    call "AuditLog"('PERSON',"old_name"."personid","string"('Employment HMRC Engagement Details - ',"PName"),"string"("old_name"."HMRCEngagementDetail"),"string"("new_name"."HMRCEngagementDetail"))
  end if;
  if update("UTR") and(exists(select * from "AuditItems" where "AreaName" = 'Person' and "AuditFlag" = 1 and "ItemName" = 'Employment Unique Tax Reference')) then
    call "AuditLog"('PERSON',"old_name"."personid","string"('Employment Unique Tax Reference - ',"PName"),"string"("old_name"."UTR"),"string"("new_name"."UTR"))
  end if;
  if update("VATNumber") and(exists(select * from "AuditItems" where "AreaName" = 'Person' and "AuditFlag" = 1 and "ItemName" = 'Employment Vat Number')) then
    call "AuditLog"('PERSON',"old_name"."personid","string"('Employment Vat Number - ',"PName"),"string"("old_name"."VATNumber"),"string"("new_name"."VATNumber"))
  end if;
  if update("VATRegistered") and(exists(select * from "AuditItems" where "AreaName" = 'Person' and "AuditFlag" = 1 and "ItemName" = 'Employment Vat Registered')) then
    call "AuditLog"('PERSON',"old_name"."personid","string"('Employment Vat Registered - ',"PName"),"string"("old_name"."VATRegistered"),"string"("new_name"."VATRegistered"))
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Pay_Employment"."PayEmploymentAudit" IS 
{create trigger PayEmploymentAudit 
 after update of EndDate, StartDate, CompanyName,CompanyRegNo,HMRCEngagementDetail,UTR,VATNumber,VATRegistered
order 1 on pears.Pay_Employment
referencing old as old_name new as new_name
for each row
begin
  declare PName char(250);
  set PName=(select Name from Person where PersonID = isnull(old_name.PersonID,new_name.PersonID));
  if update (StartDate) and (exists(select * from AuditItems where AreaName = 'Person' and AuditFlag = 1 and ItemName ='Employment Start Date')) then
    call AuditLog('PERSON',old_name.personid,string('Employment Start Date Updated - ',PName),string(old_name.startdate),string(new_name.startdate))
  end if;
  if update (EndDate) and (exists(select * from AuditItems where AreaName = 'Person' and AuditFlag = 1 and ItemName ='Employment End Date')) then
    call AuditLog('PERSON',old_name.personid,string('Employment End Date Updated - ',PName),string(old_name.enddate),string(new_name.enddate))
  end if;
  if update (CompanyName) and (exists(select * from AuditItems where AreaName = 'Person' and AuditFlag = 1 and ItemName ='Employment Company Name')) then
    call AuditLog('PERSON',old_name.personid,string('Employment Company Name - ',PName),string(old_name.CompanyName),string(new_name.CompanyName))
  end if;
  if update (CompanyRegNo) and (exists(select * from AuditItems where AreaName = 'Person' and AuditFlag = 1 and ItemName ='Employment Company Reg. No')) then
    call AuditLog('PERSON',old_name.personid,string('Employment Company Reg. No - ',PName),string(old_name.CompanyRegNo),string(new_name.CompanyRegNo))
  end if;
  if update (HMRCEngagementDetail) and (exists(select * from AuditItems where AreaName = 'Person' and AuditFlag = 1 and ItemName ='Employment HMRC Engagement Details')) then
    call AuditLog('PERSON',old_name.personid,string('Employment HMRC Engagement Details - ',PName),string(old_name.HMRCEngagementDetail),string(new_name.HMRCEngagementDetail))
  end if;
  if update (UTR) and (exists(select * from AuditItems where AreaName = 'Person' and AuditFlag = 1 and ItemName ='Employment Unique Tax Reference')) then
    call AuditLog('PERSON',old_name.personid,string('Employment Unique Tax Reference - ',PName),string(old_name.UTR),string(new_name.UTR))
  end if;
  if update (VATNumber) and (exists(select * from AuditItems where AreaName = 'Person' and AuditFlag = 1 and ItemName ='Employment Vat Number')) then
    call AuditLog('PERSON',old_name.personid,string('Employment Vat Number - ',PName),string(old_name.VATNumber),string(new_name.VATNumber))
  end if;
  if update (VATRegistered) and (exists(select * from AuditItems where AreaName = 'Person' and AuditFlag = 1 and ItemName ='Employment Vat Registered')) then
    call AuditLog('PERSON',old_name.personid,string('Employment Vat Registered - ',PName),string(old_name.VATRegistered),string(new_name.VATRegistered))
  end if;
end
}
go


create trigger "PayEmploymentAuditInsert" after insert order 1 on
"pears"."Pay_Employment"
referencing new as "new_name"
for each row
begin
  declare "PName" char(250);
  set "PName" = (select "Name" from "Person" where "PersonID" = "new_name"."PersonID");
  if "new_name"."StartDate" is not null and(exists(select * from "AuditItems" where "AreaName" = 'Person' and "AuditFlag" = 1 and "ItemName" = 'Employment Start Date')) then
    call "AuditLog"('PERSON',"new_name"."personid","string"('Employment Start Date - ',"PName"),'',"string"("new_name"."startdate"))
  end if;
  if "new_name"."EndDate" is not null and(exists(select * from "AuditItems" where "AreaName" = 'Person' and "AuditFlag" = 1 and "ItemName" = 'Employment End Date')) then
    call "AuditLog"('PERSON',"new_name"."personid","string"('Employment End Date - ',"PName"),'',"string"("new_name"."enddate"))
  end if;
  if "new_name"."CompanyName" is not null and(exists(select * from "AuditItems" where "AreaName" = 'Person' and "AuditFlag" = 1 and "ItemName" = 'Employment Company Name')) then
    call "AuditLog"('PERSON',"new_name"."personid","string"('Employment Company Name - ',"PName"),'',"string"("new_name"."CompanyName"))
  end if;
  if "new_name"."CompanyRegNo" is not null and(exists(select * from "AuditItems" where "AreaName" = 'Person' and "AuditFlag" = 1 and "ItemName" = 'Employment Company Reg. No')) then
    call "AuditLog"('PERSON',"new_name"."personid","string"('Employment Company Reg. No - ',"PName"),'',"string"("new_name"."CompanyRegNo"))
  end if;
  if "new_name"."HMRCEngagementDetail" is not null and(exists(select * from "AuditItems" where "AreaName" = 'Person' and "AuditFlag" = 1 and "ItemName" = 'Employment HMRC Engagement Details')) then
    call "AuditLog"('PERSON',"new_name"."personid","string"('Employment HMRC Engagement Details - ',"PName"),'',"string"("new_name"."HMRCEngagementDetail"))
  end if;
  if "new_name"."UTR" is not null and(exists(select * from "AuditItems" where "AreaName" = 'Person' and "AuditFlag" = 1 and "ItemName" = 'Employment Unique Tax Reference')) then
    call "AuditLog"('PERSON',"new_name"."personid","string"('Employment Unique Tax Reference - ',"PName"),'',"string"("new_name"."UTR"))
  end if;
  if "new_name"."VATNumber" is not null and(exists(select * from "AuditItems" where "AreaName" = 'Person' and "AuditFlag" = 1 and "ItemName" = 'Employment Vat Number')) then
    call "AuditLog"('PERSON',"new_name"."personid","string"('Employment Vat Number - ',"PName"),'',"string"("new_name"."VATNumber"))
  end if;
  if "new_name"."VATRegistered" is not null and(exists(select * from "AuditItems" where "AreaName" = 'Person' and "AuditFlag" = 1 and "ItemName" = 'Employment Vat Registered')) then
    call "AuditLog"('PERSON',"new_name"."personid","string"('Employment Vat Registered - ',"PName"),'',"string"("new_name"."VATRegistered"))
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Pay_Employment"."PayEmploymentAuditInsert" IS 
{create trigger PayEmploymentAuditInsert 
 AFTER INSERT
order 1 on pears.Pay_Employment
referencing new as new_name
for each row
begin
  declare PName char(250);
  set PName=(select Name from Person where PersonID = new_name.PersonID);
  if new_name.StartDate is not null  and (exists(select * from AuditItems where AreaName = 'Person' and AuditFlag = 1 and ItemName ='Employment Start Date')) then
    call AuditLog('PERSON',new_name.personid,string('Employment Start Date - ',PName),'',string(new_name.startdate))
  end if;
  if new_name.EndDate is not null and (exists(select * from AuditItems where AreaName = 'Person' and AuditFlag = 1 and ItemName ='Employment End Date')) then
    call AuditLog('PERSON',new_name.personid,string('Employment End Date - ',PName),'',string(new_name.enddate))
  end if;
  if new_name.CompanyName is not null and (exists(select * from AuditItems where AreaName = 'Person' and AuditFlag = 1 and ItemName ='Employment Company Name')) then
    call AuditLog('PERSON',new_name.personid,string('Employment Company Name - ',PName),'',string(new_name.CompanyName))
  end if;
  if new_name.CompanyRegNo is not null and (exists(select * from AuditItems where AreaName = 'Person' and AuditFlag = 1 and ItemName ='Employment Company Reg. No')) then
    call AuditLog('PERSON',new_name.personid,string('Employment Company Reg. No - ',PName),'',string(new_name.CompanyRegNo))
  end if;
  if new_name.HMRCEngagementDetail is not null and (exists(select * from AuditItems where AreaName = 'Person' and AuditFlag = 1 and ItemName ='Employment HMRC Engagement Details')) then
    call AuditLog('PERSON',new_name.personid,string('Employment HMRC Engagement Details - ',PName),'',string(new_name.HMRCEngagementDetail))
  end if;
  if new_name.UTR is not null and (exists(select * from AuditItems where AreaName = 'Person' and AuditFlag = 1 and ItemName ='Employment Unique Tax Reference')) then
    call AuditLog('PERSON',new_name.personid,string('Employment Unique Tax Reference - ',PName),'',string(new_name.UTR))
  end if;
  if new_name.VATNumber is not null and (exists(select * from AuditItems where AreaName = 'Person' and AuditFlag = 1 and ItemName ='Employment Vat Number')) then
    call AuditLog('PERSON',new_name.personid,string('Employment Vat Number - ',PName),'',string(new_name.VATNumber))
  end if;
  if new_name.VATRegistered is not null and (exists(select * from AuditItems where AreaName = 'Person' and AuditFlag = 1 and ItemName ='Employment Vat Registered')) then
    call AuditLog('PERSON',new_name.personid,string('Employment Vat Registered - ',PName),'',string(new_name.VATRegistered))
  end if;
end
}
go


create trigger "PayEmploymentInsert" before insert order 2 on
"pears"."Pay_Employment"
referencing new as "new_name"
for each row
begin
  declare "PName" char(250);
  set "new_name"."compositeid" = (select "compositeid" from "pay_employee" as "ee" where "ee"."personid" = "new_name"."personid")
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Pay_Employment"."PayEmploymentInsert" IS 
{create trigger PayEmploymentInsert 
 BEFORE INSERT
order 2 on pears.Pay_Employment
referencing new as new_name
for each row
begin
  declare PName char(250);
  Set new_name.compositeid = (select compositeid from pay_employee ee where ee.personid  = new_name.personid)
end
}
go
</code>