====== pears.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 =====
Link between Person and Company defining employment.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| temp | smallint | NOT NULL | 0 |  |
| **employmentid** | char(20) | NOT NULL |  |  |
| personid | char(20) | NOT NULL |  |  |
| companyid | char(20) | NOT NULL |  |  |
| startdate | date | NULL |  |  |
| leavedate | date | NULL |  |  |
| noreemploy | smallint | NULL |  |  |
| note | char(100) | NULL |  |  |
| salary | numeric(12,2) | NULL |  |  |
| position | char(50) | NULL |  |  |
| department | char(30) | NULL |  |  |
| Concurrent | smallint | NULL | 0 | 1 used by shift placements to prevent availability blocks |
| ExtendedNotes | long varchar | NULL |  |  |
| lastcontactevent | timestamp | NULL |  | Added V2.2.2.13 Will be null for existing employment |
| UnsubscribeToMarketing | tinyint | NULL | 0 |  |
| ownerstaffid | char(20) | NULL |  |  |

===== Primary Key =====
  * employmentid

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| person | personid | [[database:tables:pears_person|pears.Person (personid)]] | NOT NULL;  |
| company | companyid | [[database:tables:pears_company|pears.Company (companyid)]] | NOT NULL;  |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_companyaccount|pears.CompanyAccount]] | Employment | AccountsContact | employmentid |
| [[database:tables:pears_companyaccount|pears.CompanyAccount]] | Employment2 | TimesheetContact | employmentid |
| [[database:tables:pears_companysds|pears.CompanySDS]] | employment | EmploymentID | employmentid |
| [[database:tables:pears_contactevent|pears.contactevent]] | employment | employmentid | employmentid |
| [[database:tables:pears_diary|pears.diary]] | employment | employmentid | employmentid |
| [[database:tables:pears_etip|pears.ETip]] | Employment | EmploymentID | employmentid |
| [[database:tables:pears_iqxnetuserlink|pears.IQXNetUserLink]] | Employment | EmploymentID | employmentid |
| [[database:tables:pears_placement|pears.Placement]] | employment | employmentid | employmentid |
| [[database:tables:pears_vacancy|pears.vacancy]] | employment | employmentid | employmentid |
| [[database:tables:pears_vacancyroleallocation|pears.VacancyRoleAllocation]] | employment | EmploymentID | employmentid |

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| employment_leavedate | Index | leavedate |  |
| person_lastcontactevent | Index | lastcontactevent |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| employmentleaveupdate | after | update of "leavedate" order 1 |
| EmploymentAudit | before | update of "leavedate", "startdate","position","salary","note","extendednotes", "UnsubscribeToMarketing" order 1 |
| EmploymentUnsubscribe | after | update of "UnsubscribeToMarketing" order 2501 |
| EmploymentAudit_insert | before | insert order 1 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."employment"
-- Table comment: Link between Person and Company defining employment.
-- Statement count: 16

CREATE TABLE "pears"."employment" (
    "temp"                           smallint NOT NULL DEFAULT 0
   ,"employmentid"                   char(20) NOT NULL
   ,"personid"                       char(20) NOT NULL
   ,"companyid"                      char(20) NOT NULL
   ,"startdate"                      date NULL
   ,"leavedate"                      date NULL
   ,"noreemploy"                     smallint NULL
   ,"note"                           char(100) NULL
   ,"salary"                         numeric(12,2) NULL
   ,"position"                       char(50) NULL
   ,"department"                     char(30) NULL
   ,"Concurrent"                     smallint NULL DEFAULT 0
   ,"ExtendedNotes"                  long varchar NULL
   ,"lastcontactevent"               timestamp NULL
   ,"UnsubscribeToMarketing"         tinyint NULL DEFAULT 0
   ,"ownerstaffid"                   char(20) NULL
   ,PRIMARY KEY ("employmentid" ASC) 
)
go


COMMENT ON COLUMN "pears"."employment"."Concurrent" IS 
	'1 used by shift placements to prevent availability blocks'
go


COMMENT ON COLUMN "pears"."employment"."lastcontactevent" IS 
	'Added V2.2.2.13 Will be null for existing employment'
go


COMMENT ON TABLE "pears"."employment" IS 
	'Link between Person and Company defining employment.'
go


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


ALTER TABLE "pears"."employment"
    ADD NOT NULL FOREIGN KEY "company" ("companyid" ASC)
    REFERENCES "pears"."Company" ("companyid")
go


CREATE INDEX "employment_leavedate" ON "pears"."employment"
    ( "leavedate" DESC )
go


CREATE INDEX "person_lastcontactevent" ON "pears"."employment"
    ( "lastcontactevent" DESC )
go


create trigger "employmentleaveupdate" after update of "leavedate"
order 1 on "pears"."employment"
referencing old as "old_emp" new as "new_emp"
for each row
begin
  declare "idum" smallint;
  begin
    update "placement" as "l" join "sync_row" as "s" on "s"."record_type" = 'L' and "s"."record_id" = "l"."placementid" set "s"."batch" = 0 where "l"."employmentid" = "new_emp"."employmentid" and "s"."batch" > 0
  exception
    when others then set "idum" = 0
  end;
  insert into "placementextension"( "placementextensionid","placementid","leavedate","startdate","whochanged","whenchanged","notes","oldleavedate" ) 
    select "uniquekey"("placementid"),"placementid","new_emp"."leavedate","new_emp"."startdate","userstaffid",current timestamp,"string"('leavedate changed'),"old_emp"."leavedate"
      from "placement" as "l" where "l"."employmentid" = "new_emp"."employmentid" and "new_emp"."leavedate" <> "old_emp"."leavedate"
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."employment"."employmentleaveupdate" IS 
{create trigger employmentleaveupdate 
 after update of leavedate
order 1 on pears.employment
referencing old as old_emp new as new_emp
for each row
begin
  declare idum smallint;
  begin
    update placement as l join sync_row as s on s.record_type = 'L' and s.record_id = l.placementid set s.batch = 0 where l.employmentid = new_emp.employmentid and s.batch > 0;
  exception
    when others then set idum=0
  end;
  insert into placementextension(placementextensionid, placementid, leavedate, startdate,  whochanged, whenchanged,notes, oldleavedate)  
  select uniquekey(placementid), placementid, new_emp.leavedate, new_emp.startdate, userstaffid, current timestamp, string('leavedate changed') ,  old_emp.leavedate  
  from placement l where l.employmentid = new_emp.employmentid  and new_emp.leavedate != old_emp.leavedate 
end
}
go


create trigger "EmploymentAudit" before update of "leavedate",
"startdate","position","salary","note","extendednotes",
"UnsubscribeToMarketing" order 1 on "pears"."Employment"
referencing old as "old_emp" new as "new_emp"
for each row
when(exists(select * from "AuditItems" where(("AreaName" = 'Company Contact' and "AuditFlag" = 1) or("AreaName" = 'Placement' and "AuditFlag" = 1) or("AreaName" = 'Person History' and "AuditFlag" = 1))))
begin
  declare @AuditList long varchar;
  declare @ContactName char(250);
  declare @CName char(250);
  declare @Placement char(20);
  declare @Company char(20);
  declare @OurRef char(20);
  declare @History char(1);
  set @ContactName = (select "name" from "person" where "personid" = "old_emp"."personid");
  set @Placement = (select first "placementid" from "placement" where "employmentid" = "old_emp"."employmentid");
  set @OurRef = (select first "refcode" from "placement" where "employmentid" = "old_emp"."employmentid");
  set @Company = (select "Companyid" from "Company" where "Companyid" = "old_emp"."Companyid");
  set @CName = (select "name" from "Company" where "Companyid" = "old_emp"."Companyid");
  if exists(select * from "person" where "personid" = "old_emp"."personid" and "status" = 'L') then
    set @History = null
  else set @History = 'Y'
  end if;
  select "string"(',',"list"("ItemName"),',') into @AuditList from "AuditItems" where(("AreaName" = 'Company Contact' and "AuditFlag" = 1) or("AreaName" = 'Placement' and "AuditFlag" = 1) or("AreaName" = 'Person History' and "AuditFlag" = 1));
  -- Leave Date
  if "locate"(@AuditList,',Leave Date,') > 0 and update("LeaveDate") and(@Placement is null) and(@History is null) then
    call "AuditLog"('COMPANY',@Company,"string"('Leave Date Updated - ',@ContactName),"old_emp"."leavedate","new_emp"."leavedate")
  end if;
  if "locate"(@AuditList,',To,') > 0 and update("LeaveDate") and(@History = 'Y') then
    call "AuditLog"('PERSON',"old_emp"."personid","string"(@CName,' History Leave Date Updated - '),"old_emp"."leavedate","new_emp"."leavedate")
  end if;
  -- Start Date
  if "locate"(@AuditList,',Start Date,') > 0 and update("StartDate") and(@Placement is null) and(@History is null) then
    call "AuditLog"('COMPANY',@Company,"string"('Start Date Updated - ',@ContactName),"old_emp"."startdate","new_emp"."startdate")
  end if;
  if "locate"(@AuditList,',To,') > 0 and update("StartDate") and(@History = 'Y') then
    call "AuditLog"('PERSON',"old_emp"."personid","string"(@CName,' History Start Date Updated - '),"old_emp"."startdate","new_emp"."startdate")
  end if;
  if "locate"(@AuditList,',Placement Leave Date,') > 0 and update("LeaveDate") and(@Placement is not null) then
    call "AuditLog"('PLACEMENT',@Placement,"string"('Leave Date Updated Our Ref. - ',@OurRef),"old_emp"."leavedate","new_emp"."leavedate")
  end if;
  -- Start Date
  if "locate"(@AuditList,',Placement Start Date,') > 0 and update("StartDate") and(@Placement is not null) then
    call "AuditLog"('PLACEMENT',@Placement,"string"('Start Date Updated Our Ref. - ',@OurRef),"old_emp"."startdate","new_emp"."startdate")
  end if;
  -- Job Title
  if "locate"(@AuditList,',Job Title,') > 0 and update("position") and(@Placement is null) and(@History is null) then
    call "AuditLog"('COMPANY',@Company,"string"('Job Title Updated - ',@ContactName),"old_emp"."position","new_emp"."position")
  end if;
  if "locate"(@AuditList,',Job Title,') > 0 and update("position") and(@History = 'Y') then
    call "AuditLog"('PERSON',"old_emp"."personid","string"(@CName,' History Job Title Updated - '),"old_emp"."position","new_emp"."position")
  end if;
  if "locate"(@AuditList,',Salary,') > 0 and update("salary") and(@History = 'Y') then
    call "AuditLog"('PERSON',"old_emp"."personid","string"(@CName,' History Salary Updated - '),"old_emp"."salary","new_emp"."salary")
  end if;
  if "locate"(@AuditList,',Note,') > 0 and update("note") and(@History = 'Y') then
    call "AuditLog"('PERSON',"old_emp"."personid","string"(@CName,' History Note Updated - '),"old_emp"."note","new_emp"."note")
  end if;
  if "locate"(@AuditList,',Details,') > 0 and update("ExtendedNotes") and(@History = 'Y') then
    call "AuditLog"('PERSON',"old_emp"."personid","string"(@CName,' History Details Updated - '),"old_emp"."ExtendedNotes","new_emp"."ExtendedNotes")
  end if;
  -- Unsubscribe To Marketing
  if "locate"(@AuditList,',Unsubscribe To Marketing,') > 0 and update("UnsubscribeToMarketing") then
    call "AuditLog"('PERSON',"old_emp"."personid","string"('Unsubscribe To Marketing - ',@ContactName,' - ',@CNAME),"string"("old_emp"."UnsubscribeToMarketing"),"string"("new_emp"."UnsubscribeToMarketing"))
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."employment"."EmploymentAudit" IS 
{create trigger EmploymentAudit 
 before update of leavedate, startdate, position, salary, note, extendednotes,UnsubscribeToMarketing
 order 1 on pears.Employment
referencing old as old_emp new as new_emp
for each row
when(exists(select * from AuditItems where (AreaName = 'Company Contact' and AuditFlag = 1) or (AreaName = 'Placement' and AuditFlag = 1) or (AreaName = 'Person History' and AuditFlag = 1)))
begin
  declare @AuditList long varchar;
  declare @ContactName char(250);
  declare @CName char(250);
  declare @Placement char(20);
  declare @Company char(20);
  declare @OurRef char(20);
  declare @History char(1);
  set @ContactName=(select name from person where personid = old_emp.personid);
  set @Placement=(select first placementid from placement where employmentid = old_emp.employmentid);
  set @OurRef=(select first refcode from placement where employmentid = old_emp.employmentid);
  set @Company=(select Companyid from Company where Companyid = old_emp.Companyid);
  set @CName=(select name from Company where Companyid = old_emp.Companyid);
  if exists(select * from person where personid = old_emp.personid and status = 'L' ) 
    then set @History = null
    else set @History = 'Y'
  end if;
  select string(',',list(ItemName),',') into @AuditList from AuditItems where (AreaName = 'Company Contact' and AuditFlag = 1) or (AreaName = 'Placement' and AuditFlag = 1) or (AreaName = 'Person History' and AuditFlag = 1);
  -- Leave Date
  if locate(@AuditList,',Leave Date,') > 0 and update(LeaveDate)  and (@Placement is null) and (@History is null) then 
    call AuditLog('COMPANY',@Company,string('Leave Date Updated - ',@ContactName),old_emp.leavedate,new_emp.leavedate)
  end if;
  if locate(@AuditList,',To,') > 0 and update(LeaveDate) and (@History = 'Y') then 
    call AuditLog('PERSON',old_emp.personid,string(@CName,' History Leave Date Updated - '),old_emp.leavedate,new_emp.leavedate)
  end if;
  -- Start Date
  if locate(@AuditList,',Start Date,') > 0 and update(StartDate) and (@Placement is null) and (@History is null) then 
    call AuditLog('COMPANY',@Company,string('Start Date Updated - ',@ContactName),old_emp.startdate,new_emp.startdate)
  end if;
  if locate(@AuditList,',To,') > 0 and update(StartDate) and (@History = 'Y') then 
    call AuditLog('PERSON',old_emp.personid,string(@CName,' History Start Date Updated - '),old_emp.startdate,new_emp.startdate)
  end if;
  if locate(@AuditList,',Placement Leave Date,') > 0 and update(LeaveDate) and (@Placement is not null) then 
    call AuditLog('PLACEMENT',@Placement,string('Leave Date Updated Our Ref. - ',@OurRef),old_emp.leavedate,new_emp.leavedate)
  end if;
  -- Start Date
  if locate(@AuditList,',Placement Start Date,') > 0 and update(StartDate) and (@Placement is not null) then 
    call AuditLog('PLACEMENT',@Placement,string('Start Date Updated Our Ref. - ',@OurRef),old_emp.startdate,new_emp.startdate)
  end if;
  -- Job Title
  if locate(@AuditList,',Job Title,') > 0 and update(position) and (@Placement is null) and (@History is null) then 
    call AuditLog('COMPANY',@Company,string('Job Title Updated - ',@ContactName),old_emp.position,new_emp.position)
  end if;
  if locate(@AuditList,',Job Title,') > 0 and update(position) and (@History = 'Y') then 
    call AuditLog('PERSON',old_emp.personid,string(@CName,' History Job Title Updated - '),old_emp.position,new_emp.position)
  end if;
  if locate(@AuditList,',Salary,') > 0 and update(salary) and (@History = 'Y') then 
    call AuditLog('PERSON',old_emp.personid,string(@CName,' History Salary Updated - '),old_emp.salary,new_emp.salary)
  end if;
  if locate(@AuditList,',Note,') > 0 and update(note) and (@History = 'Y') then 
    call AuditLog('PERSON',old_emp.personid,string(@CName,' History Note Updated - '),old_emp.note,new_emp.note)
  end if;
  if locate(@AuditList,',Details,') > 0 and update(ExtendedNotes) and (@History = 'Y') then 
    call AuditLog('PERSON',old_emp.personid,string(@CName,' History Details Updated - '),old_emp.ExtendedNotes,new_emp.ExtendedNotes)
  end if;
  -- Unsubscribe To Marketing
  if locate(@AuditList,',Unsubscribe To Marketing,') > 0 and update(UnsubscribeToMarketing) then
    call AuditLog('PERSON',old_emp.personid,string('Unsubscribe To Marketing - ',@ContactName,' - ',@CNAME),string(old_emp.UnsubscribeToMarketing),string(new_emp.UnsubscribeToMarketing))
  end if;
end
}
go


create trigger "EmploymentUnsubscribe" after update of "UnsubscribeToMarketing"
order 2501 on "pears"."Employment"
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"('E','E-mail',"old_name"."Employmentid"),'') 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"."employmentid" and "mailerselectionid" = any(select "mailerselectionid" from "mailerselection" where "type" = 'E');
      delete from "mailerselectionmember" where "record" = "old_name"."employmentid" and "datesubscribed" is null and "mailerselectionid" = any(select "mailerselectionid" from "mailerselection" where "type" = 'E')
    end if
  else
    delete from "UnsubscribeToMarketing" where "email" = any(select "getphone"('E','E-mail',"old_name"."Employmentid"))
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."employment"."EmploymentUnsubscribe" IS 
{create trigger EmploymentUnsubscribe 
 AFTER UPDATE OF UnsubscribeToMarketing 
 ORDER 2501 ON Employment 
  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('E','E-mail', old_name.Employmentid),'') 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.employmentid and mailerselectionid in (select mailerselectionid from mailerselection where type='E'); 
        delete from mailerselectionmember where record=old_name.employmentid and datesubscribed is null and mailerselectionid in (select mailerselectionid from mailerselection where type='E');  
      end if;
   else  
     delete from UnsubscribeToMarketing where email in ( select getphone('E','E-mail', old_name.Employmentid) )
   end if; 
 end
}
go


create trigger "EmploymentAudit_insert" before insert order 1 on
"pears"."Employment"
referencing new as "new_emp"
for each row
begin
  declare @AuditList long varchar;
  select "string"(',',"list"("ItemName"),',') into @AuditList from "AuditItems" where("AreaName" = 'Company Contact' and "AuditFlag" = 1);
  if "locate"(@AuditList,',New Company Contact,') > 0 then
    call "AuditLog"('COMPANY',"new_emp"."companyid",'New Contact','',(select "name" from "person" where "personid" = "new_emp"."personid"))
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."employment"."EmploymentAudit_insert" IS 
{create trigger EmploymentAudit_insert 
 before insert 
 order 1 on pears.Employment
referencing new as new_emp
for each row
begin
  declare @AuditList long varchar;
  select string(',',list(ItemName),',') into @AuditList from AuditItems where (AreaName = 'Company Contact' and AuditFlag = 1);
  if locate(@AuditList,',New Company Contact,') > 0 then
    call AuditLog('COMPANY',new_emp.companyid,'New Contact','',(select name from person where personid = new_emp.personid))
  end if;
end
}
go
</code>