====== pears.Division ======
<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 =====
List of Divisions in the organisation.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **divisionid** | char(20) | NOT NULL |  |  |
| name | char(30) | NOT NULL |  |  |
| Analysis | char(50) | NULL |  |  |
| DefPayrollCoID | char(1) | NULL |  |  |
| Colour | integer | NULL |  |  |
| documenttemplateid | char(12) | NULL |  |  |
| statementfrequency | char(1) | NULL |  | =None W=Weekly M=Monthly |
| BTS_Title | char(30) | NULL |  |  |
| BTS_default | char(4) | NULL |  |  |
| BTX_Default | char(4) | NULL |  |  |
| ShortCode | char(4) | NULL |  |  |
| BTS_EmailBody | long varchar | NULL |  |  |
| DefaultDocPack | char(20) | NULL |  |  |
| PublishToWeb | smallint | NULL | 0 |  |
| WebDescription | char(50) | NULL |  |  |
| ShowAltPersonWizard | smallint | NULL | 0 |  |
| AllowMovingClientCC | char(1) | NULL |  |  timesheet advanced cancel wizard will allow moving to companies with this status |
| ExcludeZeroPayChargeWarning | smallint | NULL | 0 | Warning suppressed if hours 1 col and either 0 and warning enabled |
| Defunct | tinyint | NULL | 0 |  |
| MailChimpAPIKey | char(60) | NULL |  |  |
| MailChimpURL | char(60) | NULL |  |  |
| MailChimpUserName | char(100) | NULL |  |  |
| IQXNetEmailDetailsID | char(20) | NULL |  |  |
| AlternativeInvoiceAddress | tinyint | NULL | 0 |  |
| SortCode | integer | NULL |  |  |

===== Primary Key =====
  * divisionid

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| iqacdocumenttemplate | documenttemplateid | [[database:tables:pears_iqacdocumenttemplate|pears.IQacDocumentTemplate (DocumentTemplateID)]] | ON DELETE SET NULL |
| IQXNetEmailDetails | IQXNetEmailDetailsID | [[database:tables:pears_iqxnetemaildetails|pears.IQXNetEmailDetails (IQXNetEmailDetailsID)]] | ON DELETE SET NULL |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_agencydetails|pears.agencydetails]] | division | divisionid | divisionid |
| [[database:tables:pears_alternativeinvoiceaddressemail|pears.AlternativeInvoiceAddressEmail]] | division | divisionid | divisionid |
| [[database:tables:pears_automatchdivisionconfig|pears.AutoMatchDivisionConfig]] | division | DivisionID | divisionid |
| [[database:tables:pears_automatchnotificationqueue|pears.AutoMatchNotificationQueue]] | division | DivisionID | divisionid |
| [[database:tables:pears_availabilitytemplate|pears.AvailabilityTemplate]] | Division | DivisionID | divisionid |
| [[database:tables:pears_boilerplatedivision|pears.BoilerPlatedivision]] | division | divisionid | divisionid |
| [[database:tables:pears_company|pears.Company]] | division | divisionid | divisionid |
| [[database:tables:pears_consenttype|pears.ConsentType]] | division | DivisionID | divisionid |
| [[database:tables:pears_contactevent|pears.contactevent]] | division | divisionid | divisionid |
| [[database:tables:pears_contacteventspeciality|pears.ContactEventSpeciality]] | division | DivisionID | divisionid |
| [[database:tables:pears_contclasspersondivfilter|pears.ContClassPersonDivFilter]] | division | DivisionID | divisionid |
| [[database:tables:pears_customwpfieldslink|pears.CustomWPFieldsLink]] | division | DivisionID | divisionid |
| [[database:tables:pears_department|pears.Department]] | division | divisionid | divisionid |
| [[database:tables:pears_divisionaccess|pears.DivisionAccess]] | division | divisionid | divisionid |
| [[database:tables:pears_ebpayband|pears.EBPayBand]] | division | DivisionID | divisionid |
| [[database:tables:pears_emailstaffnotification|pears.EmailStaffNotification]] | division | DivisionID | divisionid |
| [[database:tables:pears_emailstaffnotificationaddress|pears.EmailStaffNotificationAddress]] | division | DivisionID | divisionid |
| [[database:tables:pears_globaldocumentlink|pears.GlobalDocumentLink]] | division | DivisionID | divisionid |
| [[database:tables:pears_iqxnetbehaviouroverride|pears.IQXNetBehaviourOverride]] | Division | DivisionID | divisionid |
| [[database:tables:pears_iqxnetbulletindivision|pears.IQXNetBulletinDivision]] | Division | DivisionID | divisionid |
| [[database:tables:pears_iqxnetlanguageoverride|pears.IQXNetLanguageOverride]] | division | DivisionID | divisionid |
| [[database:tables:pears_localwebpagedivision|pears.LocalWebPageDivision]] | division | divisionid | divisionid |
| [[database:tables:pears_nominalsegmentdivision|pears.NominalSegmentDivision]] | Division | DivisionID | divisionid |
| [[database:tables:pears_notificationtemplate|pears.NotificationTemplate]] | division | DivisionID | divisionid |
| [[database:tables:pears_origindivision|pears.OriginDivision]] | division | DivisionID | divisionid |
| [[database:tables:pears_person|pears.Person]] | division | divisionid | divisionid |
| [[database:tables:pears_personstafflinktypedivision|pears.PersonStaffLinkTypeDivision]] | Division | DivisionID | divisionid |
| [[database:tables:pears_personstatusdivision|pears.PersonStatusDivision]] | Division | DivisionID | divisionid |
| [[database:tables:pears_progressstatusdivision|pears.ProgressStatusDivision]] | Division | DivisionID | divisionid |
| [[database:tables:pears_reportdivision|pears.ReportDivision]] | division | divisionid | divisionid |
| [[database:tables:pears_salesbrand|pears.SalesBrand]] | Division | DivisionID | divisionid |
| [[database:tables:pears_staff|pears.staff]] | division | divisionid | divisionid |
| [[database:tables:pears_staffnote|pears.StaffNote]] | Division | DivisionID | divisionid |
| [[database:tables:pears_tempdesk|pears.tempdesk]] | division | divisionid | divisionid |
| [[database:tables:pears_tempjobtype|pears.TempJobType]] | division | divisionid | divisionid |
| [[database:tables:pears_templatestoredivision|pears.templatestoredivision]] | division | divisionid | divisionid |
| [[database:tables:pears_temppayband|pears.TempPayBand]] | division | DivisionID | divisionid |
| [[database:tables:pears_tsimage|pears.TSImage]] | division | DivisionID | divisionid |
| [[database:tables:pears_tsqueryreasons|pears.TSQueryReasons]] | Division | DivisionID | divisionid |

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

===== Triggers =====
^ Name ^ Timing ^ Event ^
| WPK_division_DIVISION | after | insert,delete,update order 1 |
| DivisionAudit | after | update of "documenttemplateid", "analysis","DefPayrollCoID","IQXNetEmailDetailsID" order 1 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."Division"
-- Table comment: List of Divisions in the organisation.
-- Statement count: 11

CREATE TABLE "pears"."Division" (
    "divisionid"                     char(20) NOT NULL
   ,"name"                           char(30) NOT NULL
   ,"Analysis"                       char(50) NULL
   ,"DefPayrollCoID"                 char(1) NULL
   ,"Colour"                         integer NULL
   ,"documenttemplateid"             char(12) NULL
   ,"statementfrequency"             char(1) NULL
   ,"BTS_Title"                      char(30) NULL
   ,"BTS_default"                    char(4) NULL
   ,"BTX_Default"                    char(4) NULL
   ,"ShortCode"                      char(4) NULL
   ,"BTS_EmailBody"                  long varchar NULL
   ,"DefaultDocPack"                 char(20) NULL
   ,"PublishToWeb"                   smallint NULL DEFAULT 0
   ,"WebDescription"                 char(50) NULL
   ,"ShowAltPersonWizard"            smallint NULL DEFAULT 0
   ,"AllowMovingClientCC"            char(1) NULL
   ,"ExcludeZeroPayChargeWarning"    smallint NULL DEFAULT 0
   ,"Defunct"                        tinyint NULL DEFAULT 0
   ,"MailChimpAPIKey"                char(60) NULL
   ,"MailChimpURL"                   char(60) NULL
   ,"MailChimpUserName"              char(100) NULL
   ,"IQXNetEmailDetailsID"           char(20) NULL
   ,"AlternativeInvoiceAddress"      tinyint NULL DEFAULT 0
   ,"SortCode"                       integer NULL
   ,PRIMARY KEY ("divisionid" ASC) 
)
go


COMMENT ON COLUMN "pears"."Division"."statementfrequency" IS 
	'=None W=Weekly M=Monthly'
go


COMMENT ON COLUMN "pears"."Division"."AllowMovingClientCC" IS 
	' timesheet advanced cancel wizard will allow moving to companies with this status'
go


COMMENT ON COLUMN "pears"."Division"."ExcludeZeroPayChargeWarning" IS 
	'Warning suppressed if hours 1 col and either 0 and warning enabled'
go


COMMENT ON TABLE "pears"."Division" IS 
	'List of Divisions in the organisation.'
go


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


ALTER TABLE "pears"."Division"
    ADD FOREIGN KEY "IQXNetEmailDetails" ("IQXNetEmailDetailsID" ASC)
    REFERENCES "pears"."IQXNetEmailDetails" ("IQXNetEmailDetailsID")
    ON DELETE SET NULL
go


create trigger "WPK_division_DIVISION" after insert,delete,update order 1 on
"pears"."division"
for each statement
begin
  call "WPKTrackChange"('P','DIVISION')
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Division"."WPK_division_DIVISION" IS 
{create trigger WPK_division_DIVISION 
 after insert,delete,update order 1 on
division
for each statement
begin
  call WPKTrackChange('P','DIVISION')
end
}
go


create trigger "DivisionAudit" after update of "documenttemplateid",
"analysis","DefPayrollCoID","IQXNetEmailDetailsID" order 1 on "pears"."Division"
referencing old as "old_div" new as "new_div"
for each row
when(exists(select * from "AuditItems" where "AreaName" = 'Division' and "AuditFlag" = 1))
begin
  declare @AuditList long varchar;
  declare @OldDescrip char(250);
  declare @NewDescrip char(250);
  declare @DivName char(30);
  select "string"(',',"list"("ItemName"),',') into @AuditList from "AuditItems" where "AreaName" = 'Division' and "AuditFlag" = 1;
  set @DivName = (select "name" from "division" where "Division"."DivisionID" = "old_div"."DivisionID");
  --  Override Invoice Layout
  if "locate"(@AuditList,',Override Invoice Layout,') > 0 and update("documenttemplateid") then
    set @OldDescrip = (select "name" from "iqacdocumenttemplate" where "documenttemplateid" = "old_div"."documenttemplateid");
    set @NewDescrip = (select "name" from "iqacdocumenttemplate" where "documenttemplateid" = "new_div"."documenttemplateid");
    call "AuditLog"('DIVISION',"old_div"."DivisionID","string"('Override Invoice Layout Updated - ',@DivName),@OldDescrip,@NewDescrip)
  end if;
  --  Analysis
  if "locate"(@AuditList,',Analysis,') > 0 and update("analysis") then
    call "AuditLog"('DIVISION',"old_div"."DivisionID","string"('Analysis updated - ',@DivName),"old_div"."analysis","new_div"."analysis")
  end if;
  --  Default Payroll Identifier
  if "locate"(@AuditList,',Default Payroll Identifier,') > 0 and update("DefPayrollCoID") then
    call "AuditLog"('DIVISION',"old_div"."DivisionID","string"('Default Payroll Identifier updated - ',@DivName),"old_div"."DefPayrollCoID","new_div"."DefPayrollCoID")
  end if;
  --  SMTP Profile
  if "locate"(@AuditList,',SMTP Profile,') > 0 and update("IQXNetEmailDetailsID") then
    call "AuditLog"('DIVISION',"old_div"."DivisionID","string"('SMTP Profile updated - ',@DivName),"old_div"."IQXNetEmailDetailsID","new_div"."IQXNetEmailDetailsID")
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Division"."DivisionAudit" IS 
{create trigger DivisionAudit 
 after update of documenttemplateid, analysis, DefPayrollCoID, IQXNetEmailDetailsID order 1 on pears.Division
referencing old as old_div new as new_div
for each row
when(exists(select* from AuditItems where AreaName = 'Division' and AuditFlag = 1))
begin
  declare @AuditList long varchar;
  declare @OldDescrip char(250);
  declare @NewDescrip char(250);
  declare @DivName char(30);
  select string(',',list(ItemName),',') into @AuditList from AuditItems where AreaName = 'Division' and AuditFlag = 1;
  set @DivName=(select name from division where Division.DivisionID = old_div.DivisionID);
  --  Override Invoice Layout
 if locate(@AuditList,',Override Invoice Layout,') > 0 and update(documenttemplateid) then
  set @OldDescrip=(select name from iqacdocumenttemplate where documenttemplateid = old_div.documenttemplateid);
  set @NewDescrip=(select name from iqacdocumenttemplate where documenttemplateid = new_div.documenttemplateid);
    call AuditLog('DIVISION',old_div.DivisionID,string('Override Invoice Layout Updated - ',@DivName),@OldDescrip,@NewDescrip)
  end if;
  --  Analysis
  if locate(@AuditList,',Analysis,') > 0 and update(analysis) then
    call AuditLog('DIVISION',old_div.DivisionID,string('Analysis updated - ',@DivName),old_div.analysis,new_div.analysis)
  end if;
  --  Default Payroll Identifier
  if locate(@AuditList,',Default Payroll Identifier,') > 0 and update(DefPayrollCoID) then
    call AuditLog('DIVISION',old_div.DivisionID,string('Default Payroll Identifier updated - ',@DivName),old_div.DefPayrollCoID,new_div.DefPayrollCoID)
  end if;
  --  SMTP Profile
  if locate(@AuditList,',SMTP Profile,') > 0 and update(IQXNetEmailDetailsID) then
    call AuditLog('DIVISION',old_div.DivisionID,string('SMTP Profile updated - ',@DivName),old_div.IQXNetEmailDetailsID,new_div.IQXNetEmailDetailsID)
  end if;
end
}
go
</code>