pears.TempShiftUnavailableReason

User defined list of reasons why candidate is unavailable for a shift. Also link to resultant AWR implication.

• TempShiftUnavailableReasonID

• No outgoing foreign keys found.

• No indexes found.

-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."TempShiftUnavailableReason"
-- Table comment: User defined list of reasons why candidate is unavailable for a shift. Also link to resultant AWR implication.
-- Statement count: 4
 
CREATE TABLE "pears"."TempShiftUnavailableReason" (
    "TempShiftUnavailableReasonID"   CHAR(1) NOT NULL
   ,"Name"                           CHAR(50) NOT NULL
   ,"SortOrder"                      SMALLINT NULL
   ,"PublishToWeb"                   SMALLINT NULL DEFAULT 0
   ,"AWRAction"                      SMALLINT NULL DEFAULT 0
   ,PRIMARY KEY ("TempShiftUnavailableReasonID" ASC) 
)
GO
 
 
COMMENT ON TABLE "pears"."TempShiftUnavailableReason" IS 
	'User defined list of reasons why candidate is unavailable for a shift. Also link to resultant AWR implication.'
GO
 
 
CREATE TRIGGER "TempShiftUnavailableReasonAudit" BEFORE DELETE ORDER 1 ON
"pears"."TempShiftUnavailableReason"
REFERENCING OLD AS "old_shiftunavailablereason"
FOR each ROW
BEGIN
  DECLARE "mergedid" CHAR(1);
  DECLARE "nameold" CHAR(50);
  DECLARE "mergedname" CHAR(50);
  SET "mergedid" = (SELECT "RIGHT"("old_shiftunavailablereason"."name",1));
  SET "nameold" = (SELECT "LEFT"("old_shiftunavailablereason"."name","LEN"("old_shiftunavailablereason"."name")-1));
  SET "mergedname" = (SELECT "name" FROM "tempshiftunavailablereason" WHERE "tempshiftunavailablereasonid" = "mergedid");
  IF EXISTS(SELECT * FROM "audititems" WHERE "isnull"("auditflag",0) = 1 AND "itemname" = 'Unavailability Reason') THEN
    CALL "auditlog"('Shift',"old_shiftunavailablereason"."TempShiftUnavailableReasonID",
    'Shift Unavailability Reason deleted',"nameold",'Removed - Merged into '+"mergedid"+' - '+"mergedname")
  END IF
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TempShiftUnavailableReason"."TempShiftUnavailableReasonAudit" IS 
{CREATE TRIGGER TempShiftUnavailableReasonAudit 
 BEFORE DELETE ORDER 1 ON
pears.TempShiftUnavailableReason
REFERENCING OLD AS old_shiftunavailablereason
FOR each ROW
BEGIN
DECLARE mergedid CHAR(1);
DECLARE nameold CHAR(50);
DECLARE mergedname CHAR(50);
SET mergedid = (SELECT RIGHT(old_shiftunavailablereason.name, 1));
SET nameold = (SELECT LEFT(old_shiftunavailablereason.name, LEN(old_shiftunavailablereason.name) - 1));
SET mergedname = (SELECT name FROM tempshiftunavailablereason WHERE tempshiftunavailablereasonid = mergedid) ;
IF  EXISTS(SELECT * FROM audititems WHERE isnull(auditflag, 0) = 1 AND itemname= 'Unavailability Reason')  THEN
CALL auditlog('Shift',old_shiftunavailablereason.TempShiftUnavailableReasonID, 
 'Shift Unavailability Reason deleted',nameold,'Removed - Merged into '+mergedid+' - '+mergedname)
END IF;
END
}
GO