====== pears.Placement ======
<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 =====
Header record for placements.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **placementid** | char(20) | NOT NULL |  |  |
| placedate | date | NOT NULL |  |  |
| employmentid | char(20) | NOT NULL |  |  |
| vacancyid | char(20) | NULL |  |  |
| commchgd | numeric(12,2) | NULL |  |  |
| billed | smallint | NULL |  |  |
| departmentid | char(2) | NOT NULL |  |  |
| staffid | char(20) | NOT NULL |  |  |
| temp | smallint | NOT NULL | 0 |  |
| str1 | char(20) | NULL |  |  |
| str2 | char(20) | NULL |  |  |
| str3 | char(20) | NULL |  |  |
| str4 | char(20) | NULL |  |  |
| str5 | char(20) | NULL |  |  |
| salary | numeric(12,2) | NULL |  |  |
| clientrate | numeric(12,2) | NULL |  |  |
| temprate | numeric(12,2) | NULL |  |  |
| note | char(1) | NULL |  |  |
| State | char(1) | NULL |  | NULL:new A:awaits invoice I:invoiced C:cancelled |
| RefCode | char(20) | NULL |  |  |
| TheirRef | char(50) | NULL |  |  |
| ContractRef | char(20) | NULL |  |  |
| DaysPerWeek | smallint | NULL |  |  |
| TransferBatch | integer | NULL |  |  |
| Currency | char(3) | NULL |  |  |
| TempJobTypeID | char(20) | NULL |  |  |
| ExtendedNotes | long varchar | NULL |  |  |
| ExpenseBenefitSchemeID | char(20) | NULL |  |  |
| PartCredited | smallint | NULL | 0 | Can only be set once invoiced. Incremented for each part credit |
| WorkMonday | tinyint | NULL |  |  |
| WorkTuesday | tinyint | NULL |  |  |
| WorkWednesday | tinyint | NULL |  |  |
| WorkThursday | tinyint | NULL |  |  |
| WorkFriday | tinyint | NULL |  |  |
| WorkSaturday | tinyint | NULL |  |  |
| WorkSunday | tinyint | NULL |  |  |
| WorkNormalHours | double | NULL |  |  |
| WorkStartTime | time | NULL |  |  |
| lastcontactevent | timestamp | NULL |  | Added V2.2.2.13 Will be null for existing placements |
| documenttemplateid | char(12) | NULL |  |  |
| WithdrawReason | char(100) | NULL |  |  |
| PlacementRoleID | char(20) | NULL |  |  |
| SpecialShiftContainer | tinyint | NULL |  | To allow shifts to be linked pre-timesheet, for special rates etc. |
| NotTaxable | smallint | NULL | 0 | Flag to override client public sector |
| WhenEntered | timestamp | NULL | current timestamp |  |
| DirectEngagement | smallint | NULL | 0 |  |
| AlternativeInvoiceAddressEmailID | char(20) | NULL |  |  |
| SalesBrandID | char(20) | NULL |  |  |
| InvoiceAddress | long varchar | NULL |  |  |
| InvoiceEmail | char(250) | NULL |  |  |
| InvoicePrefix | char(250) | NULL |  |  |
| DirectEngagementPAYE | smallint | NULL | 0 |  |

===== Primary Key =====
  * placementid

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| employment | employmentid | [[database:tables:pears_employment|pears.employment (employmentid)]] | NOT NULL;  |
| department | departmentid | [[database:tables:pears_department|pears.Department (departmentid)]] | NOT NULL;  |
| staff | staffid | [[database:tables:pears_staff|pears.staff (staffid)]] | NOT NULL;  |
| TempJobType | TempJobTypeID | [[database:tables:pears_tempjobtype|pears.TempJobType (TempJobTypeID)]] | ON DELETE SET NULL |
| ExpenseBenefitScheme | ExpenseBenefitSchemeID | [[database:tables:pears_expensebenefitscheme|pears.ExpenseBenefitScheme (ExpenseBenefitSchemeID)]] | ON DELETE SET NULL |
| vacancy | vacancyid | [[database:tables:pears_vacancy|pears.vacancy (vacancyid)]] |  |
| PlacementRole | PlacementRoleID | [[database:tables:pears_placementrole|pears.PlacementRole (PlacementRoleID)]] |  |
| iqacdocumenttemplate | documenttemplateid | [[database:tables:pears_iqacdocumenttemplate|pears.IQacDocumentTemplate (DocumentTemplateID)]] | ON DELETE SET NULL |
| AlternativeInvoiceAddressEmail | AlternativeInvoiceAddressEmailID | [[database:tables:pears_alternativeinvoiceaddressemail|pears.AlternativeInvoiceAddressEmail (AlternativeInvoiceAddressEmailID)]] | ON DELETE SET NULL |
| SalesBrand | SalesBrandID | [[database:tables:pears_salesbrand|pears.SalesBrand (SalesBrandID)]] | ON DELETE SET NULL |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_awrjobmaster|pears.AWRJobMaster]] | Placement | PlacementID | placementid |
| [[database:tables:pears_contactevent|pears.contactevent]] | placement | placementid | placementid |
| [[database:tables:pears_diary|pears.diary]] | placement | placementid | placementid |
| [[database:tables:pears_expensebenefitplacementitem|pears.ExpenseBenefitPlacementItem]] | Placement | PlacementID | placementid |
| [[database:tables:pears_placementanalysis|pears.PlacementAnalysis]] | Placement | PlacementID | placementid |
| [[database:tables:pears_placementattribution|pears.placementattribution]] | placement | placementid | placementid |
| [[database:tables:pears_placementdayvariation|pears.PlacementDayVariation]] | placement | PlacementID | placementid |
| [[database:tables:pears_placementelement|pears.PlacementElement]] | Placement | PlacementID | placementid |
| [[database:tables:pears_placementextension|pears.PlacementExtension]] | Placement | PlacementID | placementid |
| [[database:tables:pears_placementlink|pears.PlacementLink]] | placement | PlacementID1 | placementid |
| [[database:tables:pears_placementlink|pears.PlacementLink]] | PlacementID2 | PlacementID2 | placementid |
| [[database:tables:pears_placementremuneration|pears.placementremuneration]] | placement | placementid | placementid |
| [[database:tables:pears_placementtransfer|pears.PlacementTransfer]] | Placement | PlacementID | placementid |
| [[database:tables:pears_progress|pears.progress]] | placement | placementid | placementid |
| [[database:tables:pears_staffhistory|pears.StaffHistory]] | Placement | PlacementID | placementid |
| [[database:tables:pears_stagedpayments|pears.StagedPayments]] | placement | PlacementID | placementid |
| [[database:tables:pears_tempjobrate|pears.TempJobRate]] | placement | PlacementID | placementid |
| [[database:tables:pears_tempprovtimesheet|pears.TempProvTimeSheet]] | Placement | PlacementID | placementid |
| [[database:tables:pears_tempratemarginaudit|pears.TempRateMarginAudit]] | placement | PlacementID | placementid |
| [[database:tables:pears_tempshift|pears.TempShift]] | Placement | PlacementID | placementid |
| [[database:tables:pears_temptimesheet|pears.TempTimeSheet]] | Placement | PlacementID | placementid |

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| placement_placedate | Index | placedate |  |
| placement_state | Index | State |  |
| person_lastcontactevent | Index | lastcontactevent |  |
| PlacementWhenEntered | Index | WhenEntered |  |
| placement_extendednotestext | Text index | ExtendedNotes | CONFIGURATION "SYS"."default_char" IMMEDIATE REFRESH |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| placementsalaryupd | after | update of "salary" order 1 |
| placementinsert | before | insert order 1 |
| placementAudit | before | update of "staffid", "salary","placedate","extendednotes","refcode","nottaxable","theirref","invoiceaddress","invoiceemail","invoiceprefix", "documenttemplateid" order 1 |
| Placement_UpdateTrim | before | update order 2 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."Placement"
-- Table comment: Header record for placements.
-- Statement count: 30

CREATE TABLE "pears"."Placement" (
    "placementid"                    char(20) NOT NULL
   ,"placedate"                      date NOT NULL
   ,"employmentid"                   char(20) NOT NULL
   ,"vacancyid"                      char(20) NULL
   ,"commchgd"                       numeric(12,2) NULL
   ,"billed"                         smallint NULL
   ,"departmentid"                   char(2) NOT NULL
   ,"staffid"                        char(20) NOT NULL
   ,"temp"                           smallint NOT NULL DEFAULT 0
   ,"str1"                           char(20) NULL
   ,"str2"                           char(20) NULL
   ,"str3"                           char(20) NULL
   ,"str4"                           char(20) NULL
   ,"str5"                           char(20) NULL
   ,"salary"                         numeric(12,2) NULL
   ,"clientrate"                     numeric(12,2) NULL
   ,"temprate"                       numeric(12,2) NULL
   ,"note"                           char(1) NULL
   ,"State"                          char(1) NULL
   ,"RefCode"                        char(20) NULL
   ,"TheirRef"                       char(50) NULL
   ,"ContractRef"                    char(20) NULL
   ,"DaysPerWeek"                    smallint NULL
   ,"TransferBatch"                  integer NULL
   ,"Currency"                       char(3) NULL
   ,"TempJobTypeID"                  char(20) NULL
   ,"ExtendedNotes"                  long varchar NULL
   ,"ExpenseBenefitSchemeID"         char(20) NULL
   ,"PartCredited"                   smallint NULL DEFAULT 0
   ,"WorkMonday"                     tinyint NULL
   ,"WorkTuesday"                    tinyint NULL
   ,"WorkWednesday"                  tinyint NULL
   ,"WorkThursday"                   tinyint NULL
   ,"WorkFriday"                     tinyint NULL
   ,"WorkSaturday"                   tinyint NULL
   ,"WorkSunday"                     tinyint NULL
   ,"WorkNormalHours"                double NULL
   ,"WorkStartTime"                  time NULL
   ,"lastcontactevent"               timestamp NULL
   ,"documenttemplateid"             char(12) NULL
   ,"WithdrawReason"                 char(100) NULL
   ,"PlacementRoleID"                char(20) NULL
   ,"SpecialShiftContainer"          tinyint NULL
   ,"NotTaxable"                     smallint NULL DEFAULT 0
   ,"WhenEntered"                    timestamp NULL DEFAULT current timestamp
   ,"DirectEngagement"               smallint NULL DEFAULT 0
   ,"AlternativeInvoiceAddressEmailID" char(20) NULL
   ,"SalesBrandID"                   char(20) NULL
   ,"InvoiceAddress"                 long varchar NULL
   ,"InvoiceEmail"                   char(250) NULL
   ,"InvoicePrefix"                  char(250) NULL
   ,"DirectEngagementPAYE"           smallint NULL DEFAULT 0
   ,PRIMARY KEY ("placementid" ASC) 
)
go


COMMENT ON COLUMN "pears"."Placement"."State" IS 
	'NULL:new A:awaits invoice I:invoiced C:cancelled'
go


COMMENT ON COLUMN "pears"."Placement"."PartCredited" IS 
	'Can only be set once invoiced. Incremented for each part credit'
go


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


COMMENT ON COLUMN "pears"."Placement"."SpecialShiftContainer" IS 
	'To allow shifts to be linked pre-timesheet, for special rates etc.'
go


COMMENT ON COLUMN "pears"."Placement"."NotTaxable" IS 
	'Flag to override client public sector'
go


COMMENT ON TABLE "pears"."Placement" IS 
	'Header record for placements.'
go


ALTER TABLE "pears"."Placement"
    ADD NOT NULL FOREIGN KEY "employment" ("employmentid" ASC)
    REFERENCES "pears"."employment" ("employmentid")
go


ALTER TABLE "pears"."Placement"
    ADD NOT NULL FOREIGN KEY "department" ("departmentid" ASC)
    REFERENCES "pears"."Department" ("departmentid")
go


ALTER TABLE "pears"."Placement"
    ADD NOT NULL FOREIGN KEY "staff" ("staffid" ASC)
    REFERENCES "pears"."staff" ("staffid")
go


ALTER TABLE "pears"."Placement"
    ADD FOREIGN KEY "TempJobType" ("TempJobTypeID" ASC)
    REFERENCES "pears"."TempJobType" ("TempJobTypeID")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."Placement"
    ADD FOREIGN KEY "ExpenseBenefitScheme" ("ExpenseBenefitSchemeID" ASC)
    REFERENCES "pears"."ExpenseBenefitScheme" ("ExpenseBenefitSchemeID")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."Placement"
    ADD FOREIGN KEY "vacancy" ("vacancyid" ASC)
    REFERENCES "pears"."vacancy" ("vacancyid")
go


ALTER TABLE "pears"."Placement"
    ADD FOREIGN KEY "PlacementRole" ("PlacementRoleID" ASC)
    REFERENCES "pears"."PlacementRole" ("PlacementRoleID")
go


ALTER TABLE "pears"."Placement"
    ADD FOREIGN KEY "iqacdocumenttemplate" ("documenttemplateid" ASC)
    REFERENCES "pears"."IQacDocumentTemplate" ("DocumentTemplateID")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."Placement"
    ADD FOREIGN KEY "AlternativeInvoiceAddressEmail" ("AlternativeInvoiceAddressEmailID" ASC)
    REFERENCES "pears"."AlternativeInvoiceAddressEmail" ("AlternativeInvoiceAddressEmailID")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."Placement"
    ADD FOREIGN KEY "SalesBrand" ("SalesBrandID" ASC)
    REFERENCES "pears"."SalesBrand" ("SalesBrandID")
    ON DELETE SET NULL
go


CREATE INDEX "placement_placedate" ON "pears"."Placement"
    ( "placedate" DESC )
go


CREATE INDEX "placement_state" ON "pears"."Placement"
    ( "State" )
go


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


CREATE INDEX "PlacementWhenEntered" ON "pears"."Placement"
    ( "WhenEntered" )
go


CREATE TEXT INDEX "placement_extendednotestext" ON "pears"."Placement"
    ( "ExtendedNotes" ) CONFIGURATION "SYS"."default_char" IMMEDIATE REFRESH
go


create trigger "placementsalaryupd" after update of "salary"
order 1 on "pears"."Placement"
referencing new as "new_plac"
for each row
begin
  update "employment" set "salary" = "new_plac"."salary" where "employmentid" = "new_plac"."employmentid"
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Placement"."placementsalaryupd" IS 
{create trigger placementsalaryupd 
 after update of salary
order 1 on pears.Placement
referencing new as new_plac
for each row
begin
  update employment set salary = new_plac.salary where employmentid = new_plac.employmentid
end
}
go


create trigger "placementinsert" before insert order 1 on
"pears"."placement"
referencing new as "new_placement"
for each row
begin
  declare "autonumplacs" smallint;
  declare "lastplacnum" integer;
  set "new_placement"."refcode" = "trim"("new_placement"."refcode");
  set "new_placement"."theirref" = "trim"("new_placement"."theirref");
  set "new_placement"."contractref" = "trim"("new_placement"."contractref");
  if "new_placement"."refcode" is null then
    select "autoplacnumber","nextplacnumber" into "autonumplacs","lastplacnum" from "params";
    if "isnull"("autonumplacs",0) <> 0 then
      set "new_placement"."refcode" = "isnull"("lastplacnum",0)+1;
      update "params" set "nextplacnumber" = "isnull"("nextplacnumber",0)+1
    end if
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Placement"."placementinsert" IS 
{create trigger placementinsert 
 before insert order 1
on pears.placement
referencing new as new_placement
for each row
begin
  declare autonumplacs smallint;
  declare lastplacnum integer;
  set new_placement.refcode = trim(new_placement.refcode);
  set new_placement.theirref = trim(new_placement.theirref);
  set new_placement.contractref = trim(new_placement.contractref);
  if new_placement.refcode is null then
    select autoplacnumber,nextplacnumber into autonumplacs,lastplacnum from params;
    if isnull(autonumplacs,0)<>0 then
      set new_placement.refcode=isnull(lastplacnum,0)+1;
      update params set nextplacnumber=isnull(nextplacnumber,0)+1
    end if
  end if
end
}
go


create trigger "placementAudit" before update of "staffid",
"salary","placedate","extendednotes","refcode","nottaxable","theirref","invoiceaddress","invoiceemail","invoiceprefix",
"documenttemplateid" order 1 on "pears"."placement"
referencing old as "old_place" new as "new_place"
for each row
when(exists(select * from "AuditItems" where "AreaName" = 'Placement' and "AuditFlag" = 1 and "audititemid" <> 'XXMAN0000000000015'))
begin
  declare "AuditList" long varchar;
  declare "OldDescrip" char(250);
  declare "NewDescrip" char(250);
  select "string"(',',"list"("ItemName"),',') into "AuditList" from "AuditItems" where "AreaName" = 'Placement' and "AuditFlag" = 1;
  -- Consultant
  if "locate"("AuditList",',Consultant,') > 0 and update("staffid") and "old_place"."staffid" <> "new_place"."staffid" then
    select "userid" into "OldDescrip" from "staff" where "staffid" = "old_place"."staffid";
    select "userid" into "NewDescrip" from "staff" where "staffid" = "new_place"."staffid";
    call "AuditLog"('PLACEMENT',"new_place"."placementid","string"('Consultant Updated Our Ref. - ',"new_place"."refcode"),"OldDescrip","NewDescrip");
    insert into "staffhistory"( "staffhistoryid","placementid","oldstaff","newstaff","whoentered","whenentered" ) values( "uniquekey"("new_place"."staffid"),"new_place"."placementid","old_place"."staffid","new_place"."staffid","userstaffid",current timestamp ) 
  end if;
  -- Salary
  if "locate"("AuditList",',Salary,') > 0 and update("salary") then
    call "AuditLog"('PLACEMENT',"new_place"."placementid","string"('Salary Updated Our Ref. - ',"new_place"."refcode"),"old_place"."salary","new_place"."salary")
  end if;
  -- Notes
  if "locate"("AuditList",',Notes,') > 0 and update("extendednotes") then
    call "AuditLog"('PLACEMENT',"new_place"."placementid","string"('Notes Updated Our Ref. - ',"new_place"."refcode"),"old_place"."extendednotes","new_place"."extendednotes")
  end if;
  -- TheirRef
  if "locate"("AuditList",',TheirRef,') > 0 and update("TheirRef") then
    call "AuditLog"('PLACEMENT',"new_place"."placementid","string"('TheirRef Updated Our Ref. - ',"new_place"."refcode"),"old_place"."TheirRef","new_place"."TheirRef")
  end if;
  -- Our Ref
  if "locate"("AuditList",',Our Ref,') > 0 and update("refcode") then
    call "AuditLog"('PLACEMENT',"new_place"."placementid","string"('Our Ref Updated Our Ref. - ',"new_place"."refcode"),"old_place"."refcode","new_place"."refcode")
  end if;
  -- PlaceDate
  if "locate"("AuditList",',Placement Date,') > 0 and update("placedate") then
    call "AuditLog"('PLACEMENT',"new_place"."placementid","string"('Placement Date Updated Our Ref. - ',"new_place"."refcode"),"string"("old_place"."placedate"),"string"("new_place"."placedate"))
  end if;
  if "locate"("AuditList",',Invoice Email,') > 0 and update("InvoiceEmail") then
    call "AuditLog"('PLACEMENT',"new_place"."placementid","string"('Placement Date Invoice Email - ',"new_place"."refcode"),"old_place"."invoiceemail","new_place"."invoiceemail")
  end if;
  if "locate"("AuditList",',Invoice Address,') > 0 and update("InvoiceAddress") then
    call "AuditLog"('PLACEMENT',"new_place"."placementid","string"('Placement Invoice Address - ',"new_place"."refcode"),"old_place"."invoiceaddress","new_place"."invoiceaddress")
  end if;
  if "locate"("AuditList",',Invoice Prefix,') > 0 and update("InvoicePrefix") then
    call "AuditLog"('PLACEMENT',"new_place"."placementid","string"('Placement Date Invoice Prefix - ',"new_place"."refcode"),"old_place"."invoiceprefix","new_place"."invoiceprefix")
  end if;
  if "locate"("AuditList",',Override IR35 Public Sector,') > 0 and update("nottaxable") then
    call "AuditLog"('PLACEMENT',"new_place"."placementid","string"('Override IR35 Public Sector - ',"new_place"."refcode"),"old_place"."nottaxable","new_place"."nottaxable")
  end if;
  --  Override Invoice Layout
  if "locate"("AuditList",',Override Invoice Layout,') > 0 and update("documenttemplateid") then
    set "OldDescrip" = (select "name" from "iqacdocumenttemplate" where "documenttemplateid" = "old_place"."documenttemplateid");
    set "NewDescrip" = (select "name" from "iqacdocumenttemplate" where "documenttemplateid" = "new_place"."documenttemplateid");
    call "AuditLog"('PLACEMENT',"old_place"."PlacementId","string"('Override Invoice Layout Updated - ',"new_place"."refcode"),"OldDescrip","NewDescrip")
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Placement"."placementAudit" IS 
{create trigger placementAudit 
 before update of staffid, salary, placedate, extendednotes, refcode,nottaxable, theirref,invoiceaddress, invoiceemail, invoiceprefix, documenttemplateid
 order 1 on pears.placement
referencing old as old_place new as new_place
for each row
when(exists(select* from AuditItems where AreaName = 'Placement' and AuditFlag = 1 and audititemid <> 'XXMAN0000000000015'))
begin
  declare AuditList long varchar;
  declare OldDescrip char(250);
  declare NewDescrip char(250);
  select string(',',list(ItemName),',') into AuditList from AuditItems where AreaName = 'Placement' and AuditFlag = 1;
  -- Consultant
  if locate(AuditList,',Consultant,') > 0 and update(staffid) and old_place.staffid <> new_place.staffid then
    select userid into OldDescrip from staff where staffid = old_place.staffid ;
    select userid into NewDescrip from staff where staffid = new_place.staffid ;
    call AuditLog('PLACEMENT',new_place.placementid,string('Consultant Updated Our Ref. - ',new_place.refcode),OldDescrip,NewDescrip);
    insert into staffhistory (staffhistoryid, placementid, oldstaff, newstaff, whoentered, whenentered) values (uniquekey(new_place.staffid),new_place.placementid, old_place.staffid, new_place.staffid, userstaffid, current timestamp)
  end if;
  -- Salary
  if locate(AuditList,',Salary,') > 0 and update(salary) then
    call AuditLog('PLACEMENT',new_place.placementid,string('Salary Updated Our Ref. - ',new_place.refcode),old_place.salary,new_place.salary)
  end if;
  -- Notes
  if locate(AuditList,',Notes,') > 0 and update(extendednotes) then
    call AuditLog('PLACEMENT',new_place.placementid,string('Notes Updated Our Ref. - ',new_place.refcode),old_place.extendednotes,new_place.extendednotes)
  end if;
  -- TheirRef
  if locate(AuditList,',TheirRef,') > 0 and update(TheirRef) then
    call AuditLog('PLACEMENT',new_place.placementid,string('TheirRef Updated Our Ref. - ',new_place.refcode),old_place.TheirRef,new_place.TheirRef)
  end if;
  -- Our Ref
  if locate(AuditList,',Our Ref,') > 0 and update(refcode) then
    call AuditLog('PLACEMENT',new_place.placementid,string('Our Ref Updated Our Ref. - ',new_place.refcode),old_place.refcode,new_place.refcode)
  end if;
  -- PlaceDate
  if locate(AuditList,',Placement Date,') > 0 and update(placedate) then
    call AuditLog('PLACEMENT',new_place.placementid,string('Placement Date Updated Our Ref. - ',new_place.refcode),string(old_place.placedate),string(new_place.placedate))
  end if;
  if locate(AuditList,',Invoice Email,') > 0 and update(InvoiceEmail) then
    call AuditLog('PLACEMENT',new_place.placementid,string('Placement Date Invoice Email - ',new_place.refcode),old_place.invoiceemail,new_place.invoiceemail)
  end if;
  if locate(AuditList,',Invoice Address,') > 0 and update(InvoiceAddress) then
    call AuditLog('PLACEMENT',new_place.placementid,string('Placement Invoice Address - ',new_place.refcode),old_place.invoiceaddress,new_place.invoiceaddress)
  end if;
  if locate(AuditList,',Invoice Prefix,') > 0 and update(InvoicePrefix) then
    call AuditLog('PLACEMENT',new_place.placementid,string('Placement Date Invoice Prefix - ',new_place.refcode),old_place.invoiceprefix,new_place.invoiceprefix)
  end if;
  if locate(AuditList,',Override IR35 Public Sector,') > 0 and update(nottaxable) then
    call AuditLog('PLACEMENT',new_place.placementid,string('Override IR35 Public Sector - ',new_place.refcode),old_place.nottaxable,new_place.nottaxable)
  end if;
  --  Override Invoice Layout
  if locate(AuditList,',Override Invoice Layout,') > 0 and update(documenttemplateid) then
  set OldDescrip=(select name from iqacdocumenttemplate where documenttemplateid = old_place.documenttemplateid);
  set NewDescrip=(select name from iqacdocumenttemplate where documenttemplateid = new_place.documenttemplateid);
    call AuditLog('PLACEMENT',old_place.PlacementId,string('Override Invoice Layout Updated - ',new_place.refcode),OldDescrip,NewDescrip)
  end if;
end
}
go


create trigger "Placement_UpdateTrim" before update order 2 on
"pears"."placement"
referencing new as "new_placement"
for each row
begin
  set "new_placement"."refcode" = "trim"("new_placement"."refcode");
  set "new_placement"."theirref" = "trim"("new_placement"."theirref");
  set "new_placement"."contractref" = "trim"("new_placement"."contractref")
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Placement"."Placement_UpdateTrim" IS 
{create trigger Placement_UpdateTrim 
 before update order 2 on
pears.placement
referencing new as new_placement
for each row
begin
  set new_placement.refcode = trim(new_placement.refcode);
  set new_placement.theirref = trim(new_placement.theirref);
  set new_placement.contractref = trim(new_placement.contractref)
end
}
go
</code>