====== pears.ReferenceRequest ======
<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 =====
Individual Reference Request records

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **ReferenceRequestID** | char(20) | NOT NULL |  |  |
| PersonID | char(20) | NOT NULL |  |  |
| DepartmentID | char(2) | NOT NULL |  |  |
| RefereeAddressName | char(60) | NOT NULL |  |  |
| RefereeSalutation | char(60) | NOT NULL |  |  |
| RefereePosition | char(40) | NOT NULL |  |  |
| CompanyName | char(60) | NULL |  |  |
| Keyname | char(60) | NOT NULL |  |  |
| Addr1 | char(40) | NULL |  |  |
| Addr2 | char(40) | NULL |  |  |
| Addr3 | char(40) | NULL |  |  |
| Town | char(30) | NULL |  |  |
| County | char(30) | NULL |  |  |
| Country | char(30) | NULL |  |  |
| PostCode | char(20) | NULL |  |  |
| HomePhone | char(100) | NULL |  |  |
| WorkPhone | char(100) | NULL |  |  |
| MobilePhone | char(100) | NULL |  |  |
| Fax | char(100) | NULL |  |  |
| Email | char(250) | NOT NULL |  |  |
| Position | char(100) | NOT NULL |  |  |
| PositionNotes | long varchar | NULL |  |  |
| URLRequestCode | char(250) | NULL |  |  |
| StaffID | char(20) | NULL |  |  |
| WhenCreated | timestamp | NULL | current timestamp |  |
| WhenCompleted | timestamp | NULL |  |  |
| WhenProcessed | timestamp | NULL |  |  |
| WhoProcessed | char(20) | NULL |  |  |
| WhenSent | timestamp | NULL |  |  |
| WhenReturned | timestamp | NULL |  |  |
| EmploymentID | char(20) | NULL |  |  |
| RefType | smallint | NULL |  |  |
| StartDate | timestamp | NULL |  |  |
| EndDate | timestamp | NULL |  |  |
| brandid | char(20) | NULL |  |  |
| ReferenceConfirmationRequired | tinyint | NULL |  |  |
| NotificationTemplateID | char(50) | NULL |  |  |
| ReferenceMasterID | char(20) | NULL |  |  |
| ReminderLastSent | timestamp | NULL |  |  |
| ReferenceRefused | timestamp | NULL |  |  |
| ApprovedToSend | tinyint | NULL | 0 |  |
| Blocked | tinyint | NULL | 0 |  |
| AskCandidateBeforeSend | tinyint | NULL | 0 |  |

===== Primary Key =====
  * ReferenceRequestID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| Person | PersonID | [[database:tables:pears_person|pears.Person (personid)]] | NOT NULL; ON DELETE CASCADE |
| department | DepartmentID | [[database:tables:pears_department|pears.Department (departmentid)]] | NOT NULL;  |
| staff | StaffID | [[database:tables:pears_staff|pears.staff (staffid)]] |  |
| NotificationTemplate | NotificationTemplateID | [[database:tables:pears_notificationtemplate|pears.NotificationTemplate (NotificationTemplateID)]] | ON DELETE SET NULL |
| ReferenceMaster | ReferenceMasterID | [[database:tables:pears_referencemaster|pears.ReferenceMaster (ReferenceMasterID)]] | ON DELETE SET NULL |

===== Referenced By =====
  * No incoming foreign keys found.

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| ReferenceRequest_URLRequestCode | Index | URLRequestCode |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| ReferenceRequestUpdate | after | update of "WhenCompleted", "WhenSent","WhenReturned","RefereeAddressName","companyname", "Addr1","Addr2","Addr3","Town","County","Country","Postcode","Homephone","mobilephone","workphone","fax","email","position","positionnotes" order 3501 |
| ReferenceRequestBlocked | after | update of "Blocked" order 1 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."ReferenceRequest"
-- Table comment: Individual Reference Request records
-- Statement count: 12

CREATE TABLE "pears"."ReferenceRequest" (
    "ReferenceRequestID"             char(20) NOT NULL
   ,"PersonID"                       char(20) NOT NULL
   ,"DepartmentID"                   char(2) NOT NULL
   ,"RefereeAddressName"             char(60) NOT NULL
   ,"RefereeSalutation"              char(60) NOT NULL
   ,"RefereePosition"                char(40) NOT NULL
   ,"CompanyName"                    char(60) NULL
   ,"Keyname"                        char(60) NOT NULL
   ,"Addr1"                          char(40) NULL
   ,"Addr2"                          char(40) NULL
   ,"Addr3"                          char(40) NULL
   ,"Town"                           char(30) NULL
   ,"County"                         char(30) NULL
   ,"Country"                        char(30) NULL
   ,"PostCode"                       char(20) NULL
   ,"HomePhone"                      char(100) NULL
   ,"WorkPhone"                      char(100) NULL
   ,"MobilePhone"                    char(100) NULL
   ,"Fax"                            char(100) NULL
   ,"Email"                          char(250) NOT NULL
   ,"Position"                       char(100) NOT NULL
   ,"PositionNotes"                  long varchar NULL
   ,"URLRequestCode"                 char(250) NULL
   ,"StaffID"                        char(20) NULL
   ,"WhenCreated"                    timestamp NULL DEFAULT current timestamp
   ,"WhenCompleted"                  timestamp NULL
   ,"WhenProcessed"                  timestamp NULL
   ,"WhoProcessed"                   char(20) NULL
   ,"WhenSent"                       timestamp NULL
   ,"WhenReturned"                   timestamp NULL
   ,"EmploymentID"                   char(20) NULL
   ,"RefType"                        smallint NULL
   ,"StartDate"                      timestamp NULL
   ,"EndDate"                        timestamp NULL
   ,"brandid"                        char(20) NULL
   ,"ReferenceConfirmationRequired"  tinyint NULL
   ,"NotificationTemplateID"         char(50) NULL
   ,"ReferenceMasterID"              char(20) NULL
   ,"ReminderLastSent"               timestamp NULL
   ,"ReferenceRefused"               timestamp NULL
   ,"ApprovedToSend"                 tinyint NULL DEFAULT 0
   ,"Blocked"                        tinyint NULL DEFAULT 0
   ,"AskCandidateBeforeSend"         tinyint NULL DEFAULT 0
   ,PRIMARY KEY ("ReferenceRequestID" ASC) 
)
go


COMMENT ON TABLE "pears"."ReferenceRequest" IS 
	'Individual Reference Request records'
go


ALTER TABLE "pears"."ReferenceRequest"
    ADD NOT NULL FOREIGN KEY "Person" ("PersonID" ASC)
    REFERENCES "pears"."Person" ("personid")
    ON DELETE CASCADE
go


ALTER TABLE "pears"."ReferenceRequest"
    ADD NOT NULL FOREIGN KEY "department" ("DepartmentID" ASC)
    REFERENCES "pears"."Department" ("departmentid")
go


ALTER TABLE "pears"."ReferenceRequest"
    ADD FOREIGN KEY "staff" ("StaffID" ASC)
    REFERENCES "pears"."staff" ("staffid")
go


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


ALTER TABLE "pears"."ReferenceRequest"
    ADD FOREIGN KEY "ReferenceMaster" ("ReferenceMasterID" ASC)
    REFERENCES "pears"."ReferenceMaster" ("ReferenceMasterID")
    ON DELETE SET NULL
go


CREATE INDEX "ReferenceRequest_URLRequestCode" ON "pears"."ReferenceRequest"
    ( "URLRequestCode" )
go


create trigger "ReferenceRequestUpdate" after update of "WhenCompleted",
"WhenSent","WhenReturned","RefereeAddressName","companyname",
"Addr1","Addr2","Addr3","Town","County","Country","Postcode","Homephone","mobilephone","workphone","fax","email","position","positionnotes" order 3501 on
"pears"."ReferenceRequest"
referencing old as "old_name" new as "new_name"
for each row
begin
  declare "rv" long varchar;
  set "rv" = '';
  if update("WhenSent") then
    call "AuditLog"('PERSON',"old_name"."PersonID",
    "string"('Reference Requested - ',"new_name"."RefereeAddressName"),'',"string"("new_name"."whensent"))
  end if;
  if update("WhenReturned") then
    call "AuditLog"('PERSON',"old_name"."PersonID",
    "string"('Reference Returned - ',"new_name"."RefereeAddressName"),'',"string"("new_name"."whenreturned"))
  end if;
  if update("WhenCompleted") then
    call "AuditLog"('PERSON',"old_name"."PersonID",
    "string"('Reference Completed - ',"new_name"."RefereeAddressName"),'',"string"("new_name"."whencompleted"))
  end if;
  if update("DepartmentID") then set "rv" = "string"("rv",'DepartmentID: ',"old_name"."departmentid",' to ',"new_name"."departmentid","char"(13),"char"(10)) end if;
  if update("RefereeAddressName") then set "rv" = "string"("rv",'RefereeAddressName: ',"old_name"."RefereeAddressName",' to ',"new_name"."RefereeAddressName","char"(13),"char"(10)) end if;
  if update("companyname") then set "rv" = "string"("rv",'CompanyName: ',"old_name"."companyname",' to ',"new_name"."companyname","char"(13),"char"(10)) end if;
  if update("RefereePosition") then set "rv" = "string"("rv",'DepartmentID: ',"old_name"."departmentid",' to ',"new_name"."departmentid","char"(13),"char"(10)) end if;
  if update("Addr1") then set "rv" = "string"("rv",'Addr1: ',"old_name"."Addr1",' to ',"new_name"."Addr1","char"(13),"char"(10)) end if;
  if update("Addr2") then set "rv" = "string"("rv",'Addr2: ',"old_name"."Addr2",' to ',"new_name"."Addr2","char"(13),"char"(10)) end if;
  if update("Addr3") then set "rv" = "string"("rv",'Addr3: ',"old_name"."Addr3",' to ',"new_name"."Addr3","char"(13),"char"(10)) end if;
  if update("Town") then set "rv" = "string"("rv",'Town: ',"old_name"."Town",' to ',"new_name"."Town","char"(13),"char"(10)) end if;
  if update("County") then set "rv" = "string"("rv",'County: ',"old_name"."County",' to ',"new_name"."County","char"(13),"char"(10)) end if;
  if update("Country") then set "rv" = "string"("rv",'Country: ',"old_name"."Country",' to ',"new_name"."Country","char"(13),"char"(10)) end if;
  if update("PostCode") then set "rv" = "string"("rv",'PostCode: ',"old_name"."PostCode",' to ',"new_name"."PostCode","char"(13),"char"(10)) end if;
  if update("HomePhone") then set "rv" = "string"("rv",'HomePhone: ',"old_name"."HomePhone",' to ',"new_name"."HomePhone","char"(13),"char"(10)) end if;
  if update("WorkPhone") then set "rv" = "string"("rv",'WorkPhone: ',"old_name"."WorkPhone",' to ',"new_name"."WorkPhone","char"(13),"char"(10)) end if;
  if update("MobilePhone") then set "rv" = "string"("rv",'MobilePhone: ',"old_name"."MobilePhone",' to ',"new_name"."MobilePhone","char"(13),"char"(10)) end if;
  if update("Fax") then set "rv" = "string"("rv",'Fax: ',"old_name"."Fax",' to ',"new_name"."Fax","char"(13),"char"(10)) end if;
  if update("Email") then set "rv" = "string"("rv",'Email: ',"old_name"."Email",' to ',"new_name"."Email","char"(13),"char"(10)) end if;
  if update("Position") then set "rv" = "string"("rv",'Position: ',"old_name"."Position",' to ',"new_name"."Position","char"(13),"char"(10)) end if;
  if update("PositionNotes") then set "rv" = "string"("rv",'PositionNotes: ',"old_name"."PositionNotes",' to ',"new_name"."PositionNotes","char"(13),"char"(10)) end if;
  if "rv" <> '' then
    call "AuditLog"('PERSON',"old_name"."personid","string"('Reference Update - ',"new_name"."RefereeAddressName"),'',"rv")
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."ReferenceRequest"."ReferenceRequestUpdate" IS 
{create trigger ReferenceRequestUpdate 
 after update  of WhenCompleted, WhenSent, WhenReturned,RefereeAddressName,companyname,
 Addr1,Addr2,Addr3,Town,County,Country,Postcode,Homephone,mobilephone,workphone,fax,email,position,positionnotes order 3501 on
pears.ReferenceRequest
referencing old as old_name new as new_name
for each row
begin
declare rv long varchar;
set rv =''; 
if update(WhenSent) then  
   call AuditLog('PERSON',old_name.PersonID, 
    string('Reference Requested - ',new_name.RefereeAddressName),'',string(new_name.whensent));  
 end if;    
 if update(WhenReturned) then  
    call AuditLog('PERSON',old_name.PersonID, 
    string('Reference Returned - ',new_name.RefereeAddressName),'',string(new_name.whenreturned));  
 end if;  
 if update(WhenCompleted) then     
    call AuditLog('PERSON',old_name.PersonID, 
    string('Reference Completed - ',new_name.RefereeAddressName),'',string(new_name.whencompleted)); 
 end if;  
 if update(DepartmentID) then set rv = string(rv, 'DepartmentID: ', old_name.departmentid, ' to ', new_name.departmentid, char(13),char(10)) end if;  
 if update(RefereeAddressName) then set rv = string(rv,'RefereeAddressName: ', old_name.RefereeAddressName, ' to ', new_name.RefereeAddressName, char(13),char(10)) end if;  
 if update(companyname) then set rv = string(rv,'CompanyName: ', old_name.companyname, ' to ', new_name.companyname, char(13),char(10)) end if; 
 if update(RefereePosition) then set rv = string(rv,'DepartmentID: ', old_name.departmentid, ' to ', new_name.departmentid, char(13),char(10)) end if; 
 if update(Addr1) then set rv =string(rv, 'Addr1: ', old_name.Addr1, ' to ', new_name.Addr1, char(13),char(10)) end if; 
 if update(Addr2) then set rv =string(rv, 'Addr2: ', old_name.Addr2, ' to ', new_name.Addr2, char(13),char(10)) end if;
 if update(Addr3) then set rv = string(rv,'Addr3: ', old_name.Addr3, ' to ', new_name.Addr3, char(13),char(10)) end if;
 if update(Town) then set rv = string(rv,'Town: ', old_name.Town, ' to ', new_name.Town, char(13),char(10)) end if;
 if update(County) then set rv = string(rv,'County: ', old_name.County, ' to ', new_name.County, char(13),char(10)) end if; 
 if update(Country) then set rv = string(rv,'Country: ', old_name.Country, ' to ', new_name.Country, char(13),char(10)) end if; 
 if update(PostCode) then set rv = string(rv,'PostCode: ', old_name.PostCode, ' to ', new_name.PostCode, char(13),char(10)) end if;  
 if update(HomePhone) then set rv = string(rv,'HomePhone: ', old_name.HomePhone, ' to ', new_name.HomePhone, char(13),char(10)) end if; 
 if update(WorkPhone) then set rv = string(rv,'WorkPhone: ', old_name.WorkPhone, ' to ', new_name.WorkPhone, char(13),char(10)) end if;
 if update(MobilePhone) then set rv =string(rv, 'MobilePhone: ', old_name.MobilePhone, ' to ', new_name.MobilePhone, char(13),char(10)) end if;
 if update(Fax) then set rv = string(rv,'Fax: ', old_name.Fax, ' to ', new_name.Fax, char(13),char(10)) end if; 
 if update(Email) then set rv = string(rv,'Email: ', old_name.Email, ' to ', new_name.Email, char(13),char(10)) end if; 
 if update(Position) then set rv = string(rv,'Position: ', old_name.Position, ' to ', new_name.Position, char(13),char(10)) end if;
 if update(PositionNotes) then set rv = string(rv,'PositionNotes: ', old_name.PositionNotes, ' to ', new_name.PositionNotes, char(13),char(10)) end if;
 if rv!='' then  
 call "AuditLog"('PERSON',"old_name"."personid","string"('Reference Update - ',new_name.RefereeAddressName),'', rv);
 end if;
end
}
go


create trigger "ReferenceRequestBlocked" after update of "Blocked"
order 1 on
"pears"."ReferenceRequest"
referencing old as "old_name" new as "new_name"
for each row
begin
  call "AuditLog"('PERSON',"old_name"."PersonID",
  "string"('Reference Request Blocking - ',"new_name"."RefereeAddressName"),"old_name"."blocked","new_name"."blocked")
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."ReferenceRequest"."ReferenceRequestBlocked" IS 
{create trigger ReferenceRequestBlocked 
 after update  of Blocked order 1 on
pears.ReferenceRequest
referencing old as old_name new as new_name
for each row
begin
   call AuditLog('PERSON',old_name.PersonID, 
    string('Reference Request Blocking - ',new_name.RefereeAddressName),old_name.blocked,new_name.blocked);  
end
}
go
</code>