====== pears.AWRvacancy ======
<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 =====
AWR settings for Vacancy records. One-to-one relationship with Vacancy table. Overrides AWRCompany.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **vacancyid** | char(20) | NOT NULL |  |  |
| AWRStatus | smallint | NOT NULL | 1 |  |
| pay | long varchar | NULL |  |  |
| holidays | long varchar | NULL |  |  |
| benefits | long varchar | NULL |  |  |
| notes | long varchar | NULL |  |  |
| bonus | long varchar | NULL |  |  |
| extrahols | double | NULL |  |  |
| AWRCheckDate | date | NULL |  |  |

===== Primary Key =====
  * vacancyid

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| vacancy | vacancyid | [[database:tables:pears_vacancy|pears.vacancy (vacancyid)]] | NOT NULL; ON DELETE CASCADE |

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

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

===== Triggers =====
^ Name ^ Timing ^ Event ^
| AWRvacancy_update | after | update of "AWRStatus", "AWRCheckDate" order 1 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."AWRvacancy"
-- Table comment: AWR settings for Vacancy records. One-to-one relationship with Vacancy table. Overrides AWRCompany.
-- Statement count: 5

CREATE TABLE "pears"."AWRvacancy" (
    "vacancyid"                      char(20) NOT NULL
   ,"AWRStatus"                      smallint NOT NULL DEFAULT 1
   ,"pay"                            long varchar NULL
   ,"holidays"                       long varchar NULL
   ,"benefits"                       long varchar NULL
   ,"notes"                          long varchar NULL
   ,"bonus"                          long varchar NULL
   ,"extrahols"                      double NULL
   ,"AWRCheckDate"                   date NULL
   ,PRIMARY KEY ("vacancyid" ASC) 
)
go


COMMENT ON TABLE "pears"."AWRvacancy" IS 
	'AWR settings for Vacancy records. One-to-one relationship with Vacancy table. Overrides AWRCompany.'
go


ALTER TABLE "pears"."AWRvacancy"
    ADD NOT NULL FOREIGN KEY "vacancy" ("vacancyid" ASC)
    REFERENCES "pears"."vacancy" ("vacancyid")
    ON DELETE CASCADE
go


create trigger "AWRvacancy_update" after update of "AWRStatus",
"AWRCheckDate" order 1 on "pears"."AWRvacancy"
referencing old as "old_awr" new as "new_awr"
for each row
begin
  declare @CompName char(250);
  set @CompName = (select "string"("Company"."Name",' - ',"vacancy"."position",'(',"vacancy"."RefCode",')') from "Company" key join "Employment" key join "vacancy" where "vacancy"."vacancyid" = "old_awr"."vacancyID");
  -- audit
  if update("AWRStatus") then
    call "AuditLog"('AWRVAC',"old_awr"."vacancyID",
    "string"('AWR Status Vacancy - ',@CompName),case "old_awr"."AWRStatus" when 1 then 'Not Known' when 2 then 'AWR Applies' when 3 then 'AWR not Applicable' end,
    case "new_awr"."AWRStatus" when 1 then 'Not Known' when 2 then 'AWR Applies' when 3 then 'AWR not Applicable' end)
  end if;
  if update("AWRCheckDate") then
    call "AuditLog"('AWRVAC',"old_awr"."vacancyID",
    "string"('AWR Checked Date Vacancy - ',@CompName),"string"("old_awr"."AWRCheckDate"),"string"("new_awr"."AWRCheckDate"))
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."AWRvacancy"."AWRvacancy_update" IS 
{create trigger AWRvacancy_update 
 after update of AWRStatus, AWRCheckDate
order 1 on pears.AWRvacancy
referencing old as old_awr new as new_awr
for each row
begin
  declare @CompName char(250);
  set @CompName=(select string(Company.Name,' - ',vacancy.position,'(',vacancy.RefCode,')') from Company key join Employment key join vacancy where vacancy.vacancyid = old_awr.vacancyID);
  -- audit
  if update(AWRStatus) then 
    call AuditLog('AWRVAC',old_awr.vacancyID,
    string('AWR Status Vacancy - ',@CompName),case old_awr.AWRStatus when 1 then 'Not Known' when 2 then 'AWR Applies' when 3 then 'AWR not Applicable'
    end,case new_awr.AWRStatus when 1 then 'Not Known' when 2 then 'AWR Applies' when 3 then 'AWR not Applicable'
    end);
  end if;
  if update(AWRCheckDate) then 
    call AuditLog('AWRVAC',old_awr.vacancyID,
    string('AWR Checked Date Vacancy - ',@CompName),string(old_awr.AWRCheckDate),string(new_awr.AWRCheckDate));
  end if
end
}
go
</code>