pears.TempJobType [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.TempJobType ======
<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 records for Temp Rate Schemes. Detail lines for each constituent band are in TempJobRateMaster.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **TempJobTypeID** | char(20) | NOT NULL |  |  |
| DepartmentID | char(2) | NULL |  |  |
| ClientTempChargeCode | char(12) | NULL |  | Allows pay structures to be customised for clients or groups |
| Description | char(50) | NOT NULL |  |  |
| ErNI | double | NULL |  | % |
| HolidayAllowance | double | NULL |  | % |
| Discount | double | NULL |  | % |
| TempRateScriptID | char(20) | NULL |  |  |
| ExtraCode1 | char(20) | NULL |  |  |
| ExtraCode2 | char(20) | NULL |  |  |
| ExtraCode3 | char(20) | NULL |  |  |
| ExtraCode4 | char(20) | NULL |  |  |
| Dynamic | tinyint | NULL |  |  |
| SecondaryAgencyID | char(20) | NULL |  |  |
| Defunct | smallint | NULL |  |  |
| SortOrder | smallint | NULL |  |  |
| divisionid | char(20) | NULL |  |  |
| Notes | long varchar | NULL |  |  |

===== Primary Key =====
  * TempJobTypeID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| department | DepartmentID | [[database:tables:pears_department|pears.Department (departmentid)]] |  |
| TempRateScript | TempRateScriptID | [[database:tables:pears_tempratescript|pears.TempRateScript (TempRateScriptID)]] | ON DELETE SET NULL |
| Company | SecondaryAgencyID | [[database:tables:pears_company|pears.Company (companyid)]] | ON DELETE CASCADE |
| division | divisionid | [[database:tables:pears_division|pears.Division (divisionid)]] | ON DELETE SET NULL |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_placement|pears.Placement]] | TempJobType | TempJobTypeID | TempJobTypeID |
| [[database:tables:pears_tempjobratemaster|pears.TempJobRateMaster]] | TempJobType | TempJobTypeID | TempJobTypeID |
| [[database:tables:pears_tempprovtimesheet|pears.TempProvTimeSheet]] | TempJobType | TempJobTypeID | TempJobTypeID |
| [[database:tables:pears_tempsecagencyvacratescheme|pears.TempSecAgencyVacRateScheme]] | TempJobType | TempJobTypeID | TempJobTypeID |
| [[database:tables:pears_vacancy|pears.vacancy]] | TempJobType | TempJobTypeID | TempJobTypeID |
| [[database:tables:pears_vacancyoverrideratescheme|pears.VacancyOverrideRateScheme]] | RateScheme | TempJobTypeID | TempJobTypeID |

===== Indexes =====
  * No indexes found.

===== Triggers =====
^ Name ^ Timing ^ Event ^
| TempJobTypeAudit | after | update of "DepartmentID", "ClientTempChargeCode","Description","ErNI","HolidayAllowance","Discount","TempRateScriptID","ExtraCode1","ExtraCode2","ExtraCode3","ExtraCode4","Dynamic","SecondaryAgencyID","Defunct", "divisionid","Notes" order 1 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."TempJobType"
-- Table comment: Header records for Temp Rate Schemes. Detail lines for each constituent band are in TempJobRateMaster.
-- Statement count: 12

CREATE TABLE "pears"."TempJobType" (
    "TempJobTypeID"                  char(20) NOT NULL
   ,"DepartmentID"                   char(2) NULL
   ,"ClientTempChargeCode"           char(12) NULL
   ,"Description"                    char(50) NOT NULL
   ,"ErNI"                           double NULL
   ,"HolidayAllowance"               double NULL
   ,"Discount"                       double NULL
   ,"TempRateScriptID"               char(20) NULL
   ,"ExtraCode1"                     char(20) NULL
   ,"ExtraCode2"                     char(20) NULL
   ,"ExtraCode3"                     char(20) NULL
   ,"ExtraCode4"                     char(20) NULL
   ,"Dynamic"                        tinyint NULL
   ,"SecondaryAgencyID"              char(20) NULL
   ,"Defunct"                        smallint NULL
   ,"SortOrder"                      smallint NULL
   ,"divisionid"                     char(20) NULL
   ,"Notes"                          long varchar NULL
   ,PRIMARY KEY ("TempJobTypeID" ASC) 
)
go


COMMENT ON COLUMN "pears"."TempJobType"."ClientTempChargeCode" IS 
	'Allows pay structures to be customised for clients or groups'
go


COMMENT ON COLUMN "pears"."TempJobType"."ErNI" IS 
	'%'
go


COMMENT ON COLUMN "pears"."TempJobType"."HolidayAllowance" IS 
	'%'
go


COMMENT ON COLUMN "pears"."TempJobType"."Discount" IS 
	'%'
go


COMMENT ON TABLE "pears"."TempJobType" IS 
	'Header records for Temp Rate Schemes. Detail lines for each constituent band are in TempJobRateMaster.'
go


ALTER TABLE "pears"."TempJobType"
    ADD FOREIGN KEY "department" ("DepartmentID" ASC)
    REFERENCES "pears"."Department" ("departmentid")
go


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


ALTER TABLE "pears"."TempJobType"
    ADD FOREIGN KEY "Company" ("SecondaryAgencyID" ASC)
    REFERENCES "pears"."Company" ("companyid")
    ON DELETE CASCADE
go


ALTER TABLE "pears"."TempJobType"
    ADD FOREIGN KEY "division" ("divisionid" ASC)
    REFERENCES "pears"."Division" ("divisionid")
    ON DELETE SET NULL
go


create trigger "TempJobTypeAudit" after update of "DepartmentID",
"ClientTempChargeCode","Description","ErNI","HolidayAllowance","Discount","TempRateScriptID","ExtraCode1","ExtraCode2","ExtraCode3","ExtraCode4","Dynamic","SecondaryAgencyID","Defunct",
"divisionid","Notes" order 1 on "pears"."TempJobType"
referencing old as "old_name" new as "new_name"
for each row
begin
  declare "OldV" long varchar;
  declare "NewV" long varchar;
  declare "OldName" char(50);
  declare "NewName" char(50);
  declare @CRLF char(2);
  set @CRLF = "char"(10)+"char"(13);
  if update("Description") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Description - ',"old_name"."Description"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Description - ',"new_name"."Description"))
  end if;
  if update("Dynamic") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Dynamic - ',"old_name"."Dynamic"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Dynamic - ',"new_name"."Dynamic"))
  end if;
  if update("Departmentid") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Department - ',"old_name"."Departmentid"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Department - ',"new_name"."Departmentid"))
  end if;
  if update("Divisionid") then
    select "name" into "OldName" from "division" where "divisionid" = "old_name"."Divisionid";
    select "name" into "NewName" from "division" where "divisionid" = "new_name"."Divisionid";
    set "OldV" = "trim"("string"("OldV",@CRLF,'Division - ',"OldName"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Division - ',"NewName"))
  end if;
  if update("ClientTempChargeCode") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Client Charge Code - ',"old_name"."ClientTempChargeCode"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Client Charge Code - ',"new_name"."ClientTempChargeCode"))
  end if;
  if update("ERNI") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Employer NI - ',"old_name"."ERNI"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Employer NI - ',"new_name"."ERNI"))
  end if;
  if update("HolidayAllowance") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Holiday Allowance - ',"old_name"."HolidayAllowance"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Holiday Allowance - ',"new_name"."HolidayAllowance"))
  end if;
  if update("Discount") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Discount - ',"old_name"."Discount"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Discount - ',"new_name"."Discount"))
  end if;
  if update("TempRateScriptID") then
    set "OldName" = '';
    set "NewName" = '';
    select "description" into "OldName" from "TempRateScript" where "TempRateScriptID" = "old_name"."TempRateScriptID";
    select "description" into "NewName" from "TempRateScript" where "TempRateScriptID" = "new_name"."TempRateScriptID";
    set "OldV" = "trim"("string"("OldV",@CRLF,'Division - ',"OldName"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Division - ',"NewName"))
  end if;
  if update("ExtraCode1") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Extra Code 1 - ',"old_name"."ExtraCode1"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Extra Code 1 - ',"new_name"."ExtraCode1"))
  end if;
  if update("ExtraCode2") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Extra Code 2 - ',"old_name"."ExtraCode2"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Extra Code 2 - ',"new_name"."ExtraCode2"))
  end if;
  if update("ExtraCode3") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Extra Code 3 - ',"old_name"."ExtraCode3"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Extra Code 3 - ',"new_name"."ExtraCode3"))
  end if;
  if update("ExtraCode4") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Extra Code 4 - ',"old_name"."ExtraCode4"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Extra Code 4 - ',"new_name"."ExtraCode4"))
  end if;
  if update("SecondaryAgencyID") then
    set "OldName" = '';
    set "NewName" = '';
    select "name" into "OldName" from "Company" where "CompanyID" = "old_name"."SecondaryAgencyID";
    select "name" into "NewName" from "Company" where "CompanyID" = "new_name"."SecondaryAgencyID";
    set "OldV" = "trim"("string"("OldV",@CRLF,'Secondary Agency - ',"OldName"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Secondary Agency - ',"NewName"))
  end if;
  if update("Defunct") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Defunct - ',"old_name"."Defunct"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Defunct - ',"new_name"."Defunct"))
  end if;
  if update("Notes") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Notes - ',"old_name"."Notes"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Notes - ',"new_name"."Notes"))
  end if;
  call "AuditLog"('RATESCHEME',"new_name"."tempjobtypeid","string"("old_name"."description",' Updated'),"OldV","NewV")
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TempJobType"."TempJobTypeAudit" IS 
{create trigger TempJobTypeAudit 
 after update of "DepartmentID","ClientTempChargeCode","Description","ErNI","HolidayAllowance","Discount","TempRateScriptID","ExtraCode1","ExtraCode2","ExtraCode3","ExtraCode4","Dynamic","SecondaryAgencyID","Defunct",
"divisionid","Notes" order 1 on "pears"."TempJobType"
referencing old as "old_name" new as "new_name"
for each row
begin
  declare "OldV" long varchar;
  declare "NewV" long varchar;
  declare "OldName" char(50);
  declare "NewName" char(50);
  declare @CRLF char(2);
  set @CRLF = "char"(10)+"char"(13);
  if update("Description") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Description - ',"old_name"."Description"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Description - ',"new_name"."Description"))
  end if;
  if update("Dynamic") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Dynamic - ',"old_name"."Dynamic"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Dynamic - ',"new_name"."Dynamic"))
  end if;
  if update("Departmentid") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Department - ',"old_name"."Departmentid"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Department - ',"new_name"."Departmentid"))
  end if;
  if update("Divisionid") then
    select "name" into "OldName" from "division" where "divisionid" = "old_name"."Divisionid";
    select "name" into "NewName" from "division" where "divisionid" = "new_name"."Divisionid";
    set "OldV" = "trim"("string"("OldV",@CRLF,'Division - ',"OldName"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Division - ',"NewName"))
  end if;
  if update("ClientTempChargeCode") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Client Charge Code - ',"old_name"."ClientTempChargeCode"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Client Charge Code - ',"new_name"."ClientTempChargeCode"))
  end if;
  if update("ERNI") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Employer NI - ',"old_name"."ERNI"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Employer NI - ',"new_name"."ERNI"))
  end if;
  if update("HolidayAllowance") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Holiday Allowance - ',"old_name"."HolidayAllowance"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Holiday Allowance - ',"new_name"."HolidayAllowance"))
  end if;
  if update("Discount") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Discount - ',"old_name"."Discount"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Discount - ',"new_name"."Discount"))
  end if;
  if update("TempRateScriptID") then
    set "OldName" = '';
    set "NewName" = '';
    select "description" into "OldName" from "TempRateScript" where "TempRateScriptID" = "old_name"."TempRateScriptID";
    select "description" into "NewName" from "TempRateScript" where "TempRateScriptID" = "new_name"."TempRateScriptID";
    set "OldV" = "trim"("string"("OldV",@CRLF,'Division - ',"OldName"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Division - ',"NewName"))
  end if;
  if update("ExtraCode1") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Extra Code 1 - ',"old_name"."ExtraCode1"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Extra Code 1 - ',"new_name"."ExtraCode1"))
  end if;
  if update("ExtraCode2") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Extra Code 2 - ',"old_name"."ExtraCode2"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Extra Code 2 - ',"new_name"."ExtraCode2"))
  end if;
  if update("ExtraCode3") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Extra Code 3 - ',"old_name"."ExtraCode3"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Extra Code 3 - ',"new_name"."ExtraCode3"))
  end if;
  if update("ExtraCode4") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Extra Code 4 - ',"old_name"."ExtraCode4"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Extra Code 4 - ',"new_name"."ExtraCode4"))
  end if;
  if update("SecondaryAgencyID") then
    set "OldName" = '';
    set "NewName" = '';
    select "name" into "OldName" from "Company" where "CompanyID" = "old_name"."SecondaryAgencyID";
    select "name" into "NewName" from "Company" where "CompanyID" = "new_name"."SecondaryAgencyID";
    set "OldV" = "trim"("string"("OldV",@CRLF,'Secondary Agency - ',"OldName"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Secondary Agency - ',"NewName"))
  end if;
  if update("Defunct") then
    set "OldV" = "trim"("string"("OldV",@CRLF,'Defunct - ',"old_name"."Defunct"));
    set "NewV" = "trim"("string"("NewV",@CRLF,'Defunct - ',"new_name"."Defunct"))
  end if;
  if  update("Notes") then
     set "OldV" = "trim"("string"("OldV",@CRLF,'Notes - ',"old_name"."Notes"));
     set "NewV" = "trim"("string"("NewV",@CRLF,'Notes - ',"new_name"."Notes"))
  end if;
  call "AuditLog"('RATESCHEME',"new_name"."tempjobtypeid","string"("old_name"."description",' Updated'),"OldV","NewV");
end
}
go
</code>