pears.TempJobType

pears.TempJobType

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.

Description

Header records for Temp Rate Schemes. Detail lines for each constituent band are in TempJobRateMaster.

Columns

Column	Type	Null	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	pears.Department (departmentid)
TempRateScript	TempRateScriptID	pears.TempRateScript (TempRateScriptID)	ON DELETE SET NULL
Company	SecondaryAgencyID	pears.Company (companyid)	ON DELETE CASCADE
division	divisionid	pears.Division (divisionid)	ON DELETE SET NULL

Referenced By

Table	Constraint	Columns	Referenced columns
pears.Placement	TempJobType	TempJobTypeID	TempJobTypeID
pears.TempJobRateMaster	TempJobType	TempJobTypeID	TempJobTypeID
pears.TempProvTimeSheet	TempJobType	TempJobTypeID	TempJobTypeID
pears.TempSecAgencyVacRateScheme	TempJobType	TempJobTypeID	TempJobTypeID
pears.vacancy	TempJobType	TempJobTypeID	TempJobTypeID
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

-- 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

Table of Contents