database:tables:pears_temppayband



pears.TempPayBand

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

Description

Details of the available pay bands.

Columns

Column Type Null Default Comment
TempPayBandID char(20) NOT NULL
Description char(50) NOT NULL e.g. Standard, OT1, OT2 etc
Unit char(20) NULL Hours, Days etc
PayrollFlag char(10) NULL Indicates payroll treatment e.g. taxable, holiday etc
SortOrder smallint NULL
IncludeInHolidayPay smallint NULL 0
IsHolidayPay smallint NULL 0
IsDefault smallint NULL 0
ExtNumber integer NULL
LSMExcludeFromTotalHours tinyint NULL 0
ExternalCode char(50) NULL
AnalysisCode char(10) NULL
IsExpenses smallint NULL 0
NIExempt smallint NULL 0
DivisionID char(20) NULL
Defunct smallint NULL
TransferBatch integer NULL
ExpenseImageAllowed smallint NOT NULL 1
ExpenseImageRequired smallint NOT NULL 0
AddExpenseImageToInvoice smallint NOT NULL 1
ExpensePayOnlyByDefault smallint NOT NULL 0
RolledUpHolidayPay smallint NOT NULL 0

Primary Key

  • TempPayBandID

Foreign Keys

Constraint Columns References Delete/update action
division DivisionID pears.Division (divisionid) ON DELETE SET NULL

Referenced By

Table Constraint Columns Referenced columns
pears.IQAcShift TempPayBand TempPayBandID TempPayBandID
pears.TempJobRate TempPayBand TempPayBandID TempPayBandID
pears.TempJobRateMaster TempPayBand TempPayBandID TempPayBandID
pears.TempProvTimeSheetLine TempPayBand TempPayBandID TempPayBandID
pears.TempRateMarginAudit TempPayBand TempPayBandID TempPayBandID
pears.TempTimeSheetLine TempPayBand TempPayBandID TempPayBandID

Indexes

Name Type Columns Detail
temppayband_extnumber Index ExtNumber
temppaybandsortorder Index SortOrder
temppayband_externalcode Index ExternalCode

Triggers

Name Timing Event
WPK_temppayband_TEMPPAYBAND after insert,delete,update order 1
temppaybandupdate before update order 151
TemppaybandAudit after update of “Description”, “Unit”,“PayrollFlag”,“SortOrder”,“IncludeInHolidayPay”,“IsHolidayPay”, “IsDefault”,“ExtNumber”,“LSMExcludeFromTotalHours”,“ExternalCode”,“AnalysisCode”, “IsExpenses”,“NIExempt”,“DivisionID”,“Defunct”,“TransferBatch”,“ExpenseImageAllowed”, “ExpenseImageRequired”,“AddExpenseImageToInvoice”, “ExpensePayOnlyByDefault” order 152

Original SQL

-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."TempPayBand"
-- Table comment: Details of the available pay bands.
-- Statement count: 15
 
CREATE TABLE "pears"."TempPayBand" (
    "TempPayBandID"                  CHAR(20) NOT NULL
   ,"Description"                    CHAR(50) NOT NULL
   ,"Unit"                           CHAR(20) NULL
   ,"PayrollFlag"                    CHAR(10) NULL
   ,"SortOrder"                      SMALLINT NULL
   ,"IncludeInHolidayPay"            SMALLINT NULL DEFAULT 0
   ,"IsHolidayPay"                   SMALLINT NULL DEFAULT 0
   ,"IsDefault"                      SMALLINT NULL DEFAULT 0
   ,"ExtNumber"                      INTEGER NULL
   ,"LSMExcludeFromTotalHours"       tinyint NULL DEFAULT 0
   ,"ExternalCode"                   CHAR(50) NULL
   ,"AnalysisCode"                   CHAR(10) NULL
   ,"IsExpenses"                     SMALLINT NULL DEFAULT 0
   ,"NIExempt"                       SMALLINT NULL DEFAULT 0
   ,"DivisionID"                     CHAR(20) NULL
   ,"Defunct"                        SMALLINT NULL
   ,"TransferBatch"                  INTEGER NULL
   ,"ExpenseImageAllowed"            SMALLINT NOT NULL DEFAULT 1
   ,"ExpenseImageRequired"           SMALLINT NOT NULL DEFAULT 0
   ,"AddExpenseImageToInvoice"       SMALLINT NOT NULL DEFAULT 1
   ,"ExpensePayOnlyByDefault"        SMALLINT NOT NULL DEFAULT 0
   ,"RolledUpHolidayPay"             SMALLINT NOT NULL DEFAULT 0
   ,PRIMARY KEY ("TempPayBandID" ASC) 
)
GO
 
 
COMMENT ON COLUMN "pears"."TempPayBand"."Description" IS 
	'e.g. Standard, OT1, OT2 etc'
GO
 
 
COMMENT ON COLUMN "pears"."TempPayBand"."Unit" IS 
	'Hours, Days etc'
GO
 
 
COMMENT ON COLUMN "pears"."TempPayBand"."PayrollFlag" IS 
	'Indicates payroll treatment e.g. taxable, holiday etc'
GO
 
 
COMMENT ON TABLE "pears"."TempPayBand" IS 
	'Details of the available pay bands.'
GO
 
 
ALTER TABLE "pears"."TempPayBand"
    ADD FOREIGN KEY "division" ("DivisionID" ASC)
    REFERENCES "pears"."Division" ("divisionid")
    ON DELETE SET NULL
GO
 
 
CREATE INDEX "temppayband_extnumber" ON "pears"."TempPayBand"
    ( "ExtNumber" )
GO
 
 
CREATE INDEX "temppaybandsortorder" ON "pears"."TempPayBand"
    ( "SortOrder" )
GO
 
 
CREATE INDEX "temppayband_externalcode" ON "pears"."TempPayBand"
    ( "ExternalCode" )
GO
 
 
CREATE TRIGGER "WPK_temppayband_TEMPPAYBAND" after INSERT,DELETE,UPDATE ORDER 1 ON
"pears"."temppayband"
FOR each statement
BEGIN
  CALL "WPKTrackChange"('P','TEMPPAYBAND')
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TempPayBand"."WPK_temppayband_TEMPPAYBAND" IS 
{CREATE TRIGGER WPK_temppayband_TEMPPAYBAND 
 after INSERT,DELETE,UPDATE ORDER 1 ON
temppayband
FOR each statement
BEGIN
  CALL WPKTrackChange('P','TEMPPAYBAND')
END
}
GO
 
 
CREATE TRIGGER "temppaybandupdate" BEFORE UPDATE ORDER 151 ON
"pears"."TempPayBand"
REFERENCING OLD AS "old_pb" NEW AS "new_pb"
FOR each ROW
WHEN("old_pb"."transferbatch" > 0)
BEGIN
  SET "new_pb"."transferbatch" = 0
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TempPayBand"."temppaybandupdate" IS 
{CREATE TRIGGER temppaybandupdate 
 BEFORE UPDATE ORDER 151 ON
pears.TempPayBand
REFERENCING OLD AS old_pb NEW AS new_pb
FOR each ROW
WHEN(old_pb.transferbatch > 0)
BEGIN
  SET new_pb.transferbatch=0;
END
}
GO
 
 
CREATE TRIGGER "TemppaybandAudit" after UPDATE OF "Description",
"Unit","PayrollFlag","SortOrder","IncludeInHolidayPay","IsHolidayPay",
"IsDefault","ExtNumber","LSMExcludeFromTotalHours","ExternalCode","AnalysisCode",
"IsExpenses","NIExempt","DivisionID","Defunct","TransferBatch","ExpenseImageAllowed",
"ExpenseImageRequired","AddExpenseImageToInvoice",
"ExpensePayOnlyByDefault" ORDER 152 ON "pears"."Temppayband"
REFERENCING OLD AS "old_name" NEW AS "new_name"
FOR each ROW /*  */
BEGIN
  /* Type the trigger statements here */
  IF UPDATE("Description") THEN
    CALL "AuditLog"('PAYBAND',"old_name"."temppaybandid","string"('PAYBAND - Description : ',"old_name"."description"),"old_name"."Description","new_name"."Description")
  END IF;
  IF UPDATE("Unit") THEN
    CALL "AuditLog"('PAYBAND',"old_name"."temppaybandid","string"('PAYBAND - Unit : ',"old_name"."description"),"old_name"."Unit","new_name"."Unit")
  END IF;
  IF UPDATE("PayrollFlag") THEN
    CALL "AuditLog"('PAYBAND',"old_name"."temppaybandid","string"('PAYBAND - PayrollFlag : ',"old_name"."description"),"old_name"."PayrollFlag","new_name"."PayrollFlag")
  END IF;
  IF UPDATE("IncludeInHolidayPay") THEN
    CALL "AuditLog"('PAYBAND',"old_name"."temppaybandid","string"('PAYBAND - IncludeInHolidayPay : ',"old_name"."description"),"old_name"."IncludeInHolidayPay","new_name"."IncludeInHolidayPay")
  END IF;
  IF UPDATE("IsHolidayPay") THEN
    CALL "AuditLog"('PAYBAND',"old_name"."temppaybandid","string"('PAYBAND - IsHolidayPay : ',"old_name"."description"),"old_name"."IsHolidayPay","new_name"."IsHolidayPay")
  END IF;
  IF UPDATE("IsDefault") THEN
    CALL "AuditLog"('PAYBAND',"old_name"."temppaybandid","string"('PAYBAND - IsDefault : ',"old_name"."description"),"old_name"."IsDefault","new_name"."IsDefault")
  END IF;
  IF UPDATE("ExtNumber") THEN
    CALL "AuditLog"('PAYBAND',"old_name"."temppaybandid","string"('PAYBAND - ExtNumber : ',"old_name"."description"),"old_name"."ExtNumber","new_name"."ExtNumber")
  END IF;
  IF UPDATE("LSMExcludeFromTotalHours") THEN
    CALL "AuditLog"('PAYBAND',"old_name"."temppaybandid","string"('PAYBAND - LSMExcludeFromTotalHours : ',"old_name"."description"),"old_name"."LSMExcludeFromTotalHours","new_name"."LSMExcludeFromTotalHours")
  END IF;
  IF UPDATE("ExternalCode") THEN
    CALL "AuditLog"('PAYBAND',"old_name"."temppaybandid","string"('PAYBAND - ExternalCode : ',"old_name"."description"),"old_name"."ExternalCode","new_name"."ExternalCode")
  END IF;
  IF UPDATE("AnalysisCode") THEN
    CALL "AuditLog"('PAYBAND',"old_name"."temppaybandid","string"('PAYBAND - AnalysisCode : ',"old_name"."description"),"old_name"."AnalysisCode","new_name"."AnalysisCode")
  END IF;
  IF UPDATE("IsExpenses") THEN
    CALL "AuditLog"('PAYBAND',"old_name"."temppaybandid","string"('PAYBAND - IsExpenses : ',"old_name"."description"),"old_name"."IsExpenses","new_name"."IsExpenses")
  END IF;
  IF UPDATE("NIExempt") THEN
    CALL "AuditLog"('PAYBAND',"old_name"."temppaybandid","string"('PAYBAND - NIExempt: ',"old_name"."description"),"old_name"."NIExempt","new_name"."NIExempt")
  END IF;
  IF UPDATE("DivisionID") THEN
    CALL "AuditLog"('PAYBAND',"old_name"."temppaybandid","string"('PAYBAND - DivisionID : ',"old_name"."description"),"old_name"."DivisionID","new_name"."DivisionID")
  END IF;
  IF UPDATE("Defunct") THEN
    CALL "AuditLog"('PAYBAND',"old_name"."temppaybandid","string"('PAYBAND - Defunct : ',"old_name"."description"),"old_name"."Defunct","new_name"."Defunct")
  END IF;
  IF UPDATE("TransferBatch") THEN
    CALL "AuditLog"('PAYBAND',"old_name"."temppaybandid","string"('PAYBAND - TransferBatch : ',"old_name"."description"),"old_name"."TransferBatch","new_name"."TransferBatch")
  END IF;
  IF UPDATE("ExpenseImageAllowed") THEN
    CALL "AuditLog"('PAYBAND',"old_name"."temppaybandid","string"('PAYBAND - ExpenseImageAllowed : ',"old_name"."description"),"old_name"."ExpenseImageAllowed","new_name"."ExpenseImageAllowed")
  END IF;
  IF UPDATE("ExpenseImageRequired") THEN
    CALL "AuditLog"('PAYBAND',"old_name"."temppaybandid","string"('PAYBAND - ExpenseImageRequired : ',"old_name"."description"),"old_name"."ExpenseImageRequired","new_name"."ExpenseImageRequired")
  END IF;
  IF UPDATE("AddExpenseImageToInvoice") THEN
    CALL "AuditLog"('PAYBAND',"old_name"."temppaybandid","string"('PAYBAND - AddExpenseImageToInvoice : ',"old_name"."description"),"old_name"."AddExpenseImageToInvoice","new_name"."AddExpenseImageToInvoice")
  END IF;
  IF UPDATE("ExpensePayOnlyByDefault") THEN
    CALL "AuditLog"('PAYBAND',"old_name"."temppaybandid","string"('PAYBAND - ExpensePayOnlyByDefault : ',"old_name"."description"),"old_name"."ExpensePayOnlyByDefault","new_name"."ExpensePayOnlyByDefault")
  END IF
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TempPayBand"."TemppaybandAudit" IS 
{CREATE TRIGGER TemppaybandAudit 
after UPDATE OF 
Description,Unit,PayrollFlag,SortOrder,IncludeInHolidayPay,IsHolidayPay,  
IsDefault,ExtNumber,LSMExcludeFromTotalHours,ExternalCode,AnalysisCode,  
IsExpenses,NIExempt,DivisionID,Defunct,TransferBatch,ExpenseImageAllowed, 
ExpenseImageRequired,AddExpenseImageToInvoice,ExpensePayOnlyByDefault 
 ORDER 152 ON pears.Temppayband
REFERENCING OLD AS old_name NEW AS new_name
FOR each ROW /*  */
BEGIN
/* Type the trigger statements here */
  IF UPDATE(Description) THEN 
    CALL AuditLog('PAYBAND',old_name.temppaybandid,string('PAYBAND - Description : ',old_name.description),old_name.Description,new_name.Description)
  END IF ; 
  IF UPDATE(Unit) THEN 
    CALL AuditLog('PAYBAND',old_name.temppaybandid,string('PAYBAND - Unit : ',old_name.description),old_name.Unit,new_name.Unit)
  END IF ; 
  IF UPDATE(PayrollFlag) THEN 
    CALL AuditLog('PAYBAND',old_name.temppaybandid,string('PAYBAND - PayrollFlag : ',old_name.description),old_name.PayrollFlag,new_name.PayrollFlag)
  END IF ; 
  IF UPDATE(IncludeInHolidayPay) THEN 
    CALL AuditLog('PAYBAND',old_name.temppaybandid,string('PAYBAND - IncludeInHolidayPay : ',old_name.description),old_name.IncludeInHolidayPay,new_name.IncludeInHolidayPay)
  END IF ; 
  IF UPDATE(IsHolidayPay) THEN 
    CALL AuditLog('PAYBAND',old_name.temppaybandid,string('PAYBAND - IsHolidayPay : ',old_name.description),old_name.IsHolidayPay,new_name.IsHolidayPay)
  END IF ; 
  IF UPDATE(IsDefault) THEN 
    CALL AuditLog('PAYBAND',old_name.temppaybandid,string('PAYBAND - IsDefault : ',old_name.description),old_name.IsDefault,new_name.IsDefault)
  END IF ; 
  IF UPDATE(ExtNumber) THEN 
    CALL AuditLog('PAYBAND',old_name.temppaybandid,string('PAYBAND - ExtNumber : ',old_name.description),old_name.ExtNumber,new_name.ExtNumber)
  END IF ; 
  IF UPDATE(LSMExcludeFromTotalHours) THEN 
    CALL AuditLog('PAYBAND',old_name.temppaybandid,string('PAYBAND - LSMExcludeFromTotalHours : ',old_name.description),old_name.LSMExcludeFromTotalHours,new_name.LSMExcludeFromTotalHours)
  END IF ; 
  IF UPDATE(ExternalCode) THEN 
    CALL AuditLog('PAYBAND',old_name.temppaybandid,string('PAYBAND - ExternalCode : ',old_name.description),old_name.ExternalCode,new_name.ExternalCode)
  END IF ; 
  IF UPDATE(AnalysisCode) THEN 
    CALL AuditLog('PAYBAND',old_name.temppaybandid,string('PAYBAND - AnalysisCode : ',old_name.description),old_name.AnalysisCode,new_name.AnalysisCode)
  END IF ; 
  IF UPDATE(IsExpenses) THEN 
    CALL AuditLog('PAYBAND',old_name.temppaybandid,string('PAYBAND - IsExpenses : ',old_name.description),old_name.IsExpenses,new_name.IsExpenses)
  END IF ; 
  IF UPDATE(NIExempt) THEN 
    CALL AuditLog('PAYBAND',old_name.temppaybandid,string('PAYBAND - NIExempt: ',old_name.description),old_name.NIExempt,new_name.NIExempt)
  END IF ; 
  IF UPDATE(DivisionID) THEN 
    CALL AuditLog('PAYBAND',old_name.temppaybandid,string('PAYBAND - DivisionID : ',old_name.description),old_name.DivisionID,new_name.DivisionID)
  END IF ; 
  IF UPDATE(Defunct) THEN 
    CALL AuditLog('PAYBAND',old_name.temppaybandid,string('PAYBAND - Defunct : ',old_name.description),old_name.Defunct,new_name.Defunct)
  END IF ; 
  IF UPDATE(TransferBatch) THEN 
    CALL AuditLog('PAYBAND',old_name.temppaybandid,string('PAYBAND - TransferBatch : ',old_name.description),old_name.TransferBatch,new_name.TransferBatch)
  END IF ; 
  IF UPDATE(ExpenseImageAllowed) THEN 
    CALL AuditLog('PAYBAND',old_name.temppaybandid,string('PAYBAND - ExpenseImageAllowed : ',old_name.description),old_name.ExpenseImageAllowed,new_name.ExpenseImageAllowed)
  END IF ; 
  IF UPDATE(ExpenseImageRequired) THEN 
    CALL AuditLog('PAYBAND',old_name.temppaybandid,string('PAYBAND - ExpenseImageRequired : ',old_name.description),old_name.ExpenseImageRequired,new_name.ExpenseImageRequired)
  END IF ; 
  IF UPDATE(AddExpenseImageToInvoice) THEN 
    CALL AuditLog('PAYBAND',old_name.temppaybandid,string('PAYBAND - AddExpenseImageToInvoice : ',old_name.description),old_name.AddExpenseImageToInvoice,new_name.AddExpenseImageToInvoice)
  END IF ; 
  IF UPDATE(ExpensePayOnlyByDefault) THEN 
    CALL AuditLog('PAYBAND',old_name.temppaybandid,string('PAYBAND - ExpensePayOnlyByDefault : ',old_name.description),old_name.ExpensePayOnlyByDefault,new_name.ExpensePayOnlyByDefault)
  END IF ; 
END
}
GO
  • database/tables/pears_temppayband.txt
  • Last modified: 2026/08/07 19:24
  • by 127.0.0.1