====== pears.NotificationTemplate ======
<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 =====
SMS/Email templates for shift notification etc

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **NotificationTemplateID** | char(50) | NOT NULL |  |  |
| Description | char(100) | NOT NULL |  |  |
| TransportType | char(20) | NOT NULL |  | EMAIL, SMS |
| NotificationType | char(20) | NOT NULL |  | QueryConfirm, QueryMatch, NotifyConfirm etc. |
| RecipientType | char(20) | NOT NULL |  | CLIENT, CANDIDATE, AGENCY |
| TemplateHTML | long varchar | NULL |  |  |
| SortOrder | smallint | NULL | 0 |  |
| GroupCode | char(20) | NULL |  |  |
| Defunct | smallint | NULL | 0 |  |
| DivisionID | char(20) | NULL |  |  |
| EmailSubject | char(100) | NULL |  |  |
| TemplateSortOrder | smallint | NULL | 0 |  |
| SaveAsDocument | smallint | NULL | 0 |  |
| ClassCode | char(2) | NULL |  |  |
| PushNotificationTemplateID | char(20) | NULL |  |  |
| BoilerPlateID | char(20) | NULL |  |  |
| bcRecipient | long varchar | NULL |  |  |
| CheckListDocumentTemplate | smallint | NULL | 0 |  |
| progressstatus | char(4) | NULL |  |  |
| DeeplinkIDCheck | long varchar | NULL |  |  |
| DeeplinkBody | long varchar | NULL |  |  |
| DeeplinkCompletion | long varchar | NULL |  |  |
| DeeplinkFailure | long varchar | NULL |  |  |
| DeepLinkExpiryDays | tinyint | NULL | 30 |  |
| ID | bigint | NULL | autoincrement |  |
| ccRecipient | long varchar | NULL |  |  |
| IQXNetTagGroupID | char(20) | NULL |  |  |
| EmailToSend | smallint | NULL | 0 |  |
| ESign | smallint | NULL | 0 |  |
| OwnerID | char(20) | NULL |  |  |
| AllowEditing | tinyint | NULL |  |  |
| IQXNetEmailDetailsID | char(20) | NULL |  |  |
| FromEmailAddress | char(250) | NULL |  |  |
| DeeplinkConfirmation | long varchar | NULL |  |  |

===== Primary Key =====
  * NotificationTemplateID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| division | DivisionID | [[database:tables:pears_division|pears.Division (divisionid)]] | ON DELETE SET NULL |
| contactclass | ClassCode | [[database:tables:pears_contactclass|pears.contactclass (classcode)]] | ON DELETE SET NULL |
| PushNotificationTemplate | PushNotificationTemplateID | [[database:tables:pears_pushnotificationtemplate|pears.PushNotificationTemplate (PushNotificationTemplateID)]] | ON DELETE SET NULL |
| BoilerPlate | BoilerPlateID | [[database:tables:pears_boilerplate|pears.BoilerPlate (BoilerPlateID)]] | ON DELETE SET NULL |
| IQXNetTagGroup | IQXNetTagGroupID | [[database:tables:pears_iqxnettaggroup|pears.IQXNetTagGroup (IQXNetTagGroupID)]] | 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_notificationdepartment|pears.NotificationDepartment]] | NotificationTemplate | NotificationTemplateID | NotificationTemplateID |
| [[database:tables:pears_notificationdocuments|pears.NotificationDocuments]] | NotificationTemplate | NotificationTemplateID | NotificationTemplateID |
| [[database:tables:pears_notificationreports|pears.NotificationReports]] | NotificationTemplate | NotificationTemplateID | NotificationTemplateID |
| [[database:tables:pears_referencerequest|pears.ReferenceRequest]] | NotificationTemplate | NotificationTemplateID | NotificationTemplateID |

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| NotificationTemplate_ID | Unique index | ID |  |
| NotificationTemplateNotificationType | Index | NotificationType |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| NotificationAudit | before | update of "TemplateHTML", "DeeplinkIDCheck","DeeplinkBody","DeeplinkCompletion","DeeplinkFailure" order 2 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."NotificationTemplate"
-- Table comment: SMS/Email templates for shift notification etc
-- Statement count: 15

CREATE TABLE "pears"."NotificationTemplate" (
    "NotificationTemplateID"         char(50) NOT NULL
   ,"Description"                    char(100) NOT NULL
   ,"TransportType"                  char(20) NOT NULL
   ,"NotificationType"               char(20) NOT NULL
   ,"RecipientType"                  char(20) NOT NULL
   ,"TemplateHTML"                   long varchar NULL
   ,"SortOrder"                      smallint NULL DEFAULT 0
   ,"GroupCode"                      char(20) NULL
   ,"Defunct"                        smallint NULL DEFAULT 0
   ,"DivisionID"                     char(20) NULL
   ,"EmailSubject"                   char(100) NULL
   ,"TemplateSortOrder"              smallint NULL DEFAULT 0
   ,"SaveAsDocument"                 smallint NULL DEFAULT 0
   ,"ClassCode"                      char(2) NULL
   ,"PushNotificationTemplateID"     char(20) NULL
   ,"BoilerPlateID"                  char(20) NULL
   ,"bcRecipient"                    long varchar NULL
   ,"CheckListDocumentTemplate"      smallint NULL DEFAULT 0
   ,"progressstatus"                 char(4) NULL
   ,"DeeplinkIDCheck"                long varchar NULL
   ,"DeeplinkBody"                   long varchar NULL
   ,"DeeplinkCompletion"             long varchar NULL
   ,"DeeplinkFailure"                long varchar NULL
   ,"DeepLinkExpiryDays"             tinyint NULL DEFAULT 30
   ,"ID"                             bigint NULL DEFAULT autoincrement
   ,"ccRecipient"                    long varchar NULL
   ,"IQXNetTagGroupID"               char(20) NULL
   ,"EmailToSend"                    smallint NULL DEFAULT 0
   ,"ESign"                          smallint NULL DEFAULT 0
   ,"OwnerID"                        char(20) NULL
   ,"AllowEditing"                   tinyint NULL
   ,"IQXNetEmailDetailsID"           char(20) NULL
   ,"FromEmailAddress"               char(250) NULL
   ,"DeeplinkConfirmation"           long varchar NULL
   ,PRIMARY KEY ("NotificationTemplateID" ASC) 
)
go


COMMENT ON COLUMN "pears"."NotificationTemplate"."TransportType" IS 
	'EMAIL, SMS'
go


COMMENT ON COLUMN "pears"."NotificationTemplate"."NotificationType" IS 
	'QueryConfirm, QueryMatch, NotifyConfirm etc.'
go


COMMENT ON COLUMN "pears"."NotificationTemplate"."RecipientType" IS 
	'CLIENT, CANDIDATE, AGENCY'
go


COMMENT ON TABLE "pears"."NotificationTemplate" IS 
	'SMS/Email templates for shift notification etc'
go


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


ALTER TABLE "pears"."NotificationTemplate"
    ADD FOREIGN KEY "contactclass" ("ClassCode" ASC)
    REFERENCES "pears"."contactclass" ("classcode")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."NotificationTemplate"
    ADD FOREIGN KEY "PushNotificationTemplate" ("PushNotificationTemplateID" ASC)
    REFERENCES "pears"."PushNotificationTemplate" ("PushNotificationTemplateID")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."NotificationTemplate"
    ADD FOREIGN KEY "BoilerPlate" ("BoilerPlateID" ASC)
    REFERENCES "pears"."BoilerPlate" ("BoilerPlateID")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."NotificationTemplate"
    ADD FOREIGN KEY "IQXNetTagGroup" ("IQXNetTagGroupID" ASC)
    REFERENCES "pears"."IQXNetTagGroup" ("IQXNetTagGroupID")
    ON DELETE SET NULL
go


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


CREATE UNIQUE INDEX "NotificationTemplate_ID" ON "pears"."NotificationTemplate"
    ( "ID" )
go


CREATE INDEX "NotificationTemplateNotificationType" ON "pears"."NotificationTemplate"
    ( "NotificationType" )
go


create trigger "NotificationAudit" before update of "TemplateHTML",
"DeeplinkIDCheck","DeeplinkBody","DeeplinkCompletion","DeeplinkFailure" order 2 on "pears"."NotificationTemplate"
referencing old as "old_not" new as "new_not"
for each row
begin
  declare @OldDescrip char(250);
  declare @NewDescrip char(250);
  if update("TemplateHTML") then
    call "AuditLog"('NOTIFY',"old_not"."id",'Template Updated ',"old_not"."TemplateHTML","new_not"."TemplateHTML")
  end if;
  if update("DeeplinkIDCheck") then
    call "AuditLog"('NOTIFY',"old_not"."id",'Deeplink ID Check Updated ',"old_not"."DeeplinkIDCheck","new_not"."DeeplinkIDCheck")
  end if;
  if update("DeeplinkBody") then
    call "AuditLog"('NOTIFY',"old_not"."id",'Deeplink Body Updated ',"old_not"."DeeplinkBody","new_not"."DeeplinkBody")
  end if;
  if update("DeeplinkCompletion") then
    call "AuditLog"('NOTIFY',"old_not"."id",'Deeplink Completion Updated',"old_not"."DeeplinkCompletion","new_not"."DeeplinkCompletion")
  end if;
  if update("DeeplinkFailure") then
    call "AuditLog"('NOTIFY',"old_not"."id",'Deeplink Failure Updated ',"old_not"."DeeplinkFailure","new_not"."DeeplinkFailure")
  end if;
  if update("DeeplinkConfirmation") then
    call "AuditLog"('NOTIFY',"old_not"."id",'DeeplinkConfirmation Updated ',"old_not"."DeeplinkConfirmation","new_not"."DeeplinkConfirmation")
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."NotificationTemplate"."NotificationAudit" IS 
{create trigger NotificationAudit 
 before update of TemplateHTML,DeeplinkIDCheck,DeeplinkBody,DeeplinkCompletion,DeeplinkFailure order 2 on pears.NotificationTemplate
referencing old as old_not new as new_not
for each row
begin
  declare @OldDescrip char(250);
  declare @NewDescrip char(250);
  if  update(TemplateHTML) then
    call AuditLog('NOTIFY',old_not.id,'Template Updated ',old_not.TemplateHTML,new_not.TemplateHTML)
  end if;
  if  update(DeeplinkIDCheck) then
    call AuditLog('NOTIFY',old_not.id,'Deeplink ID Check Updated ',old_not.DeeplinkIDCheck,new_not.DeeplinkIDCheck)
  end if;
  if  update(DeeplinkBody) then
    call AuditLog('NOTIFY',old_not.id,'Deeplink Body Updated ',old_not.DeeplinkBody,new_not.DeeplinkBody)
  end if;
  if  update(DeeplinkCompletion) then
    call AuditLog('NOTIFY',old_not.id,'Deeplink Completion Updated',old_not.DeeplinkCompletion,new_not.DeeplinkCompletion)
  end if;
  if  update(DeeplinkFailure) then
    call AuditLog('NOTIFY',old_not.id,'Deeplink Failure Updated ',old_not.DeeplinkFailure,new_not.DeeplinkFailure)
  end if;
  if  update(DeeplinkConfirmation) then
    call AuditLog('NOTIFY',old_not.id,'DeeplinkConfirmation Updated ',old_not.DeeplinkConfirmation,new_not.DeeplinkConfirmation)
  end if;
end
}
go
</code>