====== pears.TempPayBand ======
<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 =====
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 | [[database:tables:pears_division|pears.Division (divisionid)]] | ON DELETE SET NULL |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_iqacshift|pears.IQAcShift]] | TempPayBand | TempPayBandID | TempPayBandID |
| [[database:tables:pears_tempjobrate|pears.TempJobRate]] | TempPayBand | TempPayBandID | TempPayBandID |
| [[database:tables:pears_tempjobratemaster|pears.TempJobRateMaster]] | TempPayBand | TempPayBandID | TempPayBandID |
| [[database:tables:pears_tempprovtimesheetline|pears.TempProvTimeSheetLine]] | TempPayBand | TempPayBandID | TempPayBandID |
| [[database:tables:pears_tempratemarginaudit|pears.TempRateMarginAudit]] | TempPayBand | TempPayBandID | TempPayBandID |
| [[database:tables:pears_temptimesheetline|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 =====
<code 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
</code>