====== pears.TempShiftUnavailableReason ======
<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 =====
User defined list of reasons why candidate is unavailable for a shift. Also link to resultant AWR implication.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **TempShiftUnavailableReasonID** | char(1) | NOT NULL |  |  |
| Name | char(50) | NOT NULL |  |  |
| SortOrder | smallint | NULL |  |  |
| PublishToWeb | smallint | NULL | 0 |  |
| AWRAction | smallint | NULL | 0 |  |

===== Primary Key =====
  * TempShiftUnavailableReasonID

===== Foreign Keys =====
  * No outgoing foreign keys found.

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_tempshift|pears.TempShift]] | TempShiftUnavailableReason | UnavailableReason | TempShiftUnavailableReasonID |

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

===== Triggers =====
^ Name ^ Timing ^ Event ^
| TempShiftUnavailableReasonAudit | before | delete order 1 |

===== Original SQL =====
<code sql>
-- 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
</code>