pears.AWRJobMaster [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.AWRJobMaster ======
<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 =====
Links Person records with Vacancies for AWR purposes.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **AWRJobMasterID** | char(20) | NOT NULL |  |  |
| PlacementID | char(20) | NULL |  |  |
| PersonID | char(20) | NULL |  |  |
| Bonus | long varchar | NULL |  |  |
| Notes | long varchar | NULL |  |  |
| AWRCheckDate | date | NULL |  |  |
| AWRRecvdDate | date | NULL |  |  |
| Benefits | long varchar | NULL |  |  |
| Holidays | long varchar | NULL |  |  |
| Pay | long varchar | NULL |  |  |
| AWRStatus | smallint | NOT NULL | 1 | 1=Not known 2= Applies 3 = Not Applies |
| Grade | char(4) | NULL |  |  |
| Speciality | char(100) | NULL |  |  |
| TempShiftID | char(20) | NULL |  |  |
| WhenEntered | date | NULL | current date |  |
| VacancyID | char(20) | NULL |  |  |
| ExtraHols | double | NULL |  |  |
| AWRLinkCode | char(20) | NULL |  |  |
| OverrideDate | date | NULL |  |  |
| OverrideSystem | smallint | NULL | 1 | 1=Use System 2= Qualified 3 = Not Qualified |

===== Primary Key =====
  * AWRJobMasterID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| Person | PersonID | [[database:tables:pears_person|pears.Person (personid)]] | ON DELETE CASCADE |
| TempShift | TempShiftID | [[database:tables:pears_tempshift|pears.TempShift (TempShiftID)]] |  |
| Vacancy | VacancyID | [[database:tables:pears_vacancy|pears.vacancy (vacancyid)]] |  |
| Placement | PlacementID | [[database:tables:pears_placement|pears.Placement (placementid)]] | ON DELETE CASCADE |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_awrweeklydetail|pears.AWRWeeklyDetail]] | AWRJobMaster | AWRJobMasterID | AWRJobMasterID |
| [[database:tables:pears_awrweeklyqualified|pears.AWRWeeklyQualified]] | AWRJobMaster | AWRJobMasterID | AWRJobMasterID |
| [[database:tables:pears_temptimesheetline|pears.TempTimeSheetLine]] | AWRJobMaster | AWRJobMasterID | AWRJobMasterID |

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| AWRJobMaster_AWRLinkCode | Index | AWRLinkCode |  |
| AWRJobMasterVacIDPersID | Index | VacancyID, PersonID |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| awrjobmaster_update | after | update of "AWRStatus", "AWRCheckDate","AWRLinkCode" order 1 |
| awrjobmaster_updatetwo | after | update of "OverrideDate", "OverrideSystem" order 2 |
| awrjobmaster_updatethree | after | update of "ExtraHols" order 3 |
| awrjobmaster_insert | after | insert order 1 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."AWRJobMaster"
-- Table comment: Links Person records with Vacancies for AWR purposes.
-- Statement count: 18

CREATE TABLE "pears"."AWRJobMaster" (
    "AWRJobMasterID"                 char(20) NOT NULL
   ,"PlacementID"                    char(20) NULL
   ,"PersonID"                       char(20) NULL
   ,"Bonus"                          long varchar NULL
   ,"Notes"                          long varchar NULL
   ,"AWRCheckDate"                   date NULL
   ,"AWRRecvdDate"                   date NULL
   ,"Benefits"                       long varchar NULL
   ,"Holidays"                       long varchar NULL
   ,"Pay"                            long varchar NULL
   ,"AWRStatus"                      smallint NOT NULL DEFAULT 1
   ,"Grade"                          char(4) NULL
   ,"Speciality"                     char(100) NULL
   ,"TempShiftID"                    char(20) NULL
   ,"WhenEntered"                    date NULL DEFAULT current date
   ,"VacancyID"                      char(20) NULL
   ,"ExtraHols"                      double NULL
   ,"AWRLinkCode"                    char(20) NULL
   ,"OverrideDate"                   date NULL
   ,"OverrideSystem"                 smallint NULL DEFAULT 1
   ,PRIMARY KEY ("AWRJobMasterID" ASC) 
)
go


COMMENT ON COLUMN "pears"."AWRJobMaster"."AWRStatus" IS 
	'1=Not known 2= Applies 3 = Not Applies'
go


COMMENT ON COLUMN "pears"."AWRJobMaster"."OverrideSystem" IS 
	'1=Use System 2= Qualified 3 = Not Qualified'
go


COMMENT ON TABLE "pears"."AWRJobMaster" IS 
	'Links Person records with Vacancies for AWR purposes.'
go


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


ALTER TABLE "pears"."AWRJobMaster"
    ADD FOREIGN KEY "TempShift" ("TempShiftID" ASC)
    REFERENCES "pears"."TempShift" ("TempShiftID")
go


ALTER TABLE "pears"."AWRJobMaster"
    ADD FOREIGN KEY "Vacancy" ("VacancyID" ASC)
    REFERENCES "pears"."vacancy" ("vacancyid")
go


ALTER TABLE "pears"."AWRJobMaster"
    ADD FOREIGN KEY "Placement" ("PlacementID" ASC)
    REFERENCES "pears"."Placement" ("placementid")
    ON DELETE CASCADE
go


CREATE INDEX "AWRJobMaster_AWRLinkCode" ON "pears"."AWRJobMaster"
    ( "AWRLinkCode" )
go


CREATE INDEX "AWRJobMasterVacIDPersID" ON "pears"."AWRJobMaster"
    ( "VacancyID","PersonID" )
go


create trigger "awrjobmaster_update" after update of "AWRStatus",
"AWRCheckDate","AWRLinkCode" order 1 on "pears"."awrjobmaster"
referencing old as "old_awr" new as "new_awr"
for each row
begin
  declare "ref" char(20);
  -- audit
  select "refcode" into "ref" from "placement" where "placementid" = "new_awr"."placementid";
  if update("AWRStatus") then
    call "AuditLog"('AWRROLE',"old_awr"."AWRJobMasterID",
    "string"('AWR Status Placement OurRef - ',"ref",(if "new_awr"."Grade" is not null then "string"(', Grade - ',"new_awr"."Grade")
    else ''
    endif),if "new_awr"."speciality" is not null then "string"(', Speciality - ',"new_awr"."speciality")
    else ''
    endif),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);
    update "AWRJobMaster" as "a" set "a"."AWRStatus" = "new_awr"."AWRStatus" where "AWRLinkCode"
       = "new_awr"."awrlinkcode" and "a"."awrjobmasterid" <> "new_awr"."awrjobmasterid"
  end if;
  if update("AWRCheckDate") then
    call "AuditLog"('AWRROLE',"old_awr"."AWRJobMasterID",
    "string"('AWR Checked Date Placement OurRef - ',"ref",(if "new_awr"."Grade" is not null then "string"(', Grade - ',"new_awr"."Grade")
    else ''
    endif),if "new_awr"."speciality" is not null then "string"(', Speciality - ',"new_awr"."speciality")
    else ''
    endif),"string"("old_awr"."AWRCheckDate"),"string"("new_awr"."AWRCheckDate"));
    update "AWRJobMaster" as "a" set "a"."AWRCheckDate" = "new_awr"."AWRCheckDate" where "AWRLinkCode"
       = "new_awr"."awrlinkcode" and "a"."awrjobmasterid" <> "new_awr"."awrjobmasterid"
  end if;
  if update("AWRLinkCode") and "new_awr"."AWRLInkCode" <> "new_awr"."AWRJobMasterID" and "new_awr"."AWRLinkCode" is not null then
    -- linking to other record copy
    update "AWRJobMaster" as "newrec"
      set "newrec"."AWRStatus" = "master"."AWRStatus","newrec"."benefits" = "master"."benefits","newrec"."notes" = "master"."notes","newrec"."holidays" = "master"."holidays",
      "newrec"."pay" = "master"."pay","newrec"."bonus" = "master"."bonus","newrec"."extrahols" = "master"."extrahols","newrec"."overridedate" = "master"."overridedate",
      "newrec"."overridesystem" = "master"."overridesystem","newrec"."AWRcheckdate" = "master"."AWRcheckdate" from
      "AWRJobMaster" as "master"
      where "newrec"."AWRJobMasterID" = "new_awr"."AWRJobMasterID"
      and "master"."AWRJobMasterID" = "new_awr"."AWRLinkCode" and "master"."AWRLinkCode" = "new_awr"."AWRLinkCode"
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."AWRJobMaster"."awrjobmaster_update" IS 
{create trigger awrjobmaster_update 
 after update of AWRStatus,
AWRCheckDate,AWRLinkCode order 1 on pears.awrjobmaster
referencing old as old_awr new as new_awr
for each row
begin
  declare ref char(20);
  -- audit
  select refcode into ref from placement where placementid = new_awr.placementid;
  if update(AWRStatus) then
    call AuditLog('AWRROLE',old_awr.AWRJobMasterID,
    string('AWR Status Placement OurRef - ',ref,(if new_awr.Grade is not null then string(', Grade - ',new_awr.Grade)
    else ''
    endif),if new_awr.speciality is not null then string(', Speciality - ',new_awr.speciality)
    else ''
    endif),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);
    update AWRJobMaster as a set a.AWRStatus = new_awr.AWRStatus where AWRLinkCode
       = new_awr.awrlinkcode and a.awrjobmasterid <> new_awr.awrjobmasterid
  end if;
  if update(AWRCheckDate) then
    call AuditLog('AWRROLE',old_awr.AWRJobMasterID,
    string('AWR Checked Date Placement OurRef - ',ref,(if new_awr.Grade is not null then string(', Grade - ',new_awr.Grade)
    else ''
    endif),if new_awr.speciality is not null then string(', Speciality - ',new_awr.speciality)
    else ''
    endif),string(old_awr.AWRCheckDate),string(new_awr.AWRCheckDate));
    update AWRJobMaster as a set a.AWRCheckDate = new_awr.AWRCheckDate where AWRLinkCode
       = new_awr.awrlinkcode and a.awrjobmasterid <> new_awr.awrjobmasterid
  end if;
  if update(AWRLinkCode) and new_awr.AWRLInkCode <> new_awr.AWRJobMasterID and new_awr.AWRLinkCode is not null then
    -- linking to other record copy
    update AWRJobMaster as newrec set
      newrec.AWRStatus = master.AWRStatus,newrec.benefits = master.benefits,newrec.notes = master.notes,newrec.holidays = master.holidays,
      newrec.pay = master.pay,newrec.bonus = master.bonus,newrec.extrahols = master.extrahols,newrec.overridedate = master.overridedate,
      newrec.overridesystem = master.overridesystem,newrec.AWRcheckdate = master.AWRcheckdate from
      AWRJobMaster as master
      where newrec.AWRJobMasterID = new_awr.AWRJobMasterID
      and master.AWRJobMasterID = new_awr.AWRLinkCode and master.AWRLinkCode = new_awr.AWRLinkCode
  end if
end
}
go


create trigger "awrjobmaster_updatetwo" after update of "OverrideDate",
"OverrideSystem" order 2 on "pears"."awrjobmaster"
referencing old as "old_awr" new as "new_awr"
for each row
begin
  declare "ref" char(20);
  -- audit
  select "refcode" into "ref" from "placement" where "placementid" = "new_awr"."placementid";
  if update("OverrideSystem") then
    call "AuditLog"('AWRROLE',"old_awr"."AWRJobMasterID",
    "string"('AWR Override Placement OurRef - ',"ref",(if "new_awr"."Grade" is not null then "string"(', Grade - ',"new_awr"."Grade")
    else ''
    endif),if "new_awr"."speciality" is not null then "string"(', Speciality - ',"new_awr"."speciality")
    else ''
    endif),case "old_awr"."OverrideSystem" when 1 then 'No' when 2 then 'Qualified' when 3 then 'Not Qualified' end,
    case "new_awr"."OverrideSystem" when 1 then 'No' when 2 then 'Qualified' when 3 then 'Not Qualified' end)
  end if;
  if update("OverrideDate") then
    call "AuditLog"('AWRROLE',"old_awr"."AWRJobMasterID",
    "string"('AWR Override Date Placement OurRef - ',"ref",(if "new_awr"."Grade" is not null then "string"(', Grade - ',"new_awr"."Grade")
    else ''
    endif),if "new_awr"."speciality" is not null then "string"(', Speciality - ',"new_awr"."speciality")
    else ''
    endif),"old_awr"."OverrideDate",
    "new_awr"."OverrideDate")
  end if;
  update "AWRJobMaster" as "a" set "a"."OverrideDate" = "new_awr"."OverrideDate","a"."OverrideSystem" = "new_awr"."OverrideSystem" where "AWRLinkCode"
     = "new_awr"."awrlinkcode" and "a"."awrjobmasterid" <> "new_awr"."awrjobmasterid"
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."AWRJobMaster"."awrjobmaster_updatetwo" IS 
{create trigger awrjobmaster_updatetwo 
 after update of OverrideDate,
OverrideSystem order 2 on pears.awrjobmaster
referencing old as old_awr new as new_awr
for each row
begin
  declare ref char(20);
  -- audit
  select refcode into ref from placement where placementid = new_awr.placementid;
  if update(OverrideSystem) then
    call AuditLog('AWRROLE',old_awr.AWRJobMasterID,
    string('AWR Override Placement OurRef - ',ref,(if new_awr.Grade is not null then string(', Grade - ',new_awr.Grade)
    else ''
    endif),if new_awr.speciality is not null then string(', Speciality - ',new_awr.speciality)
    else ''
    endif),case old_awr.OverrideSystem when 1 then 'No' when 2 then 'Qualified' when 3 then 'Not Qualified'
    end,case new_awr.OverrideSystem when 1 then 'No' when 2 then 'Qualified' when 3 then 'Not Qualified'
    end)
  end if;
  if update(OverrideDate) then
    call AuditLog('AWRROLE',old_awr.AWRJobMasterID,
    string('AWR Override Date Placement OurRef - ',ref,(if new_awr.Grade is not null then string(', Grade - ',new_awr.Grade)
    else ''
    endif),if new_awr.speciality is not null then string(', Speciality - ',new_awr.speciality)
    else ''
    endif),old_awr.OverrideDate,
    new_awr.OverrideDate)
  end if;
  update AWRJobMaster as a set a.OverrideDate = new_awr.OverrideDate,a.OverrideSystem = new_awr.OverrideSystem where AWRLinkCode
     = new_awr.awrlinkcode and a.awrjobmasterid <> new_awr.awrjobmasterid
end
}
go


create trigger "awrjobmaster_updatethree" after update of "ExtraHols"
order 3 on "pears"."awrjobmaster"
referencing old as "old_awr" new as "new_awr"
for each row
begin
  declare "ref" char(20);
  -- audit
  select "refcode" into "ref" from "placement" where "placementid" = "new_awr"."placementid";
  call "AuditLog"('AWRROLE',"old_awr"."AWRJobMasterID",
  "string"('AWR Extra Holidays Placement OurRef - ',"ref",(if "new_awr"."Grade" is not null then "string"(', Grade - ',"new_awr"."Grade")
  else ''
  endif),if "new_awr"."speciality" is not null then "string"(', Speciality - ',"new_awr"."speciality")
  else ''
  endif),"old_awr"."ExtraHols",
  "new_awr"."ExtraHols");
  update "AWRJobMaster" as "a" set "a"."ExtraHols" = "new_awr"."ExtraHols" where "AWRLinkCode"
     = "new_awr"."awrlinkcode" and "a"."awrjobmasterid" <> "new_awr"."awrjobmasterid"
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."AWRJobMaster"."awrjobmaster_updatethree" IS 
{create trigger awrjobmaster_updatethree 
 after update of ExtraHols
order 3 on pears.awrjobmaster
referencing old as old_awr new as new_awr
for each row
begin
  declare ref char(20);
  -- audit
  select refcode into ref from placement where placementid = new_awr.placementid;
  call AuditLog('AWRROLE',old_awr.AWRJobMasterID,
  string('AWR Extra Holidays Placement OurRef - ',ref,(if new_awr.Grade is not null then string(', Grade - ',new_awr.Grade)
  else ''
  endif),if new_awr.speciality is not null then string(', Speciality - ',new_awr.speciality)
  else ''
  endif),old_awr.ExtraHols,
  new_awr.ExtraHols);
  update AWRJobMaster as a set a.ExtraHols = new_awr.ExtraHols where AWRLinkCode
     = new_awr.awrlinkcode and a.awrjobmasterid <> new_awr.awrjobmasterid
end
}
go


create trigger "awrjobmaster_insert" after insert order 1 on
"pears"."awrjobmaster"
referencing new as "new_awr"
for each row
begin
  if "new_awr"."AWRLinkCode" is null then
    update "AWRJobMaster" set "AWRLinkCode" = "AWRJobMasterID" where "AWRJobMasterID" = "new_awr"."AWRJobMasterID"
  end if;
  --not linking, so inherit values from AWRvacancy
  if "new_awr"."AWRLinkCode" is null or "new_awr"."AWRLinkCode" = "new_awr"."AWRJobMasterID" then
    if("new_awr"."benefits" is null) and("new_awr"."notes" is null) and("new_awr"."holidays" is null) and("new_awr"."pay" is null) and("new_awr"."bonus" is null) and("new_awr"."extrahols" is null) then
      update "AWRJobMaster" set "AWRStatus" = "v"."AWRStatus","benefits" = "v"."benefits","notes" = "v"."notes","holidays" = "v"."holidays",
        "pay" = "v"."pay","bonus" = "v"."bonus","extrahols" = "v"."extrahols" from
        "AWRvacancy" as "v" join "AWRJobMaster" on "v"."vacancyid" = "AWRJobMaster"."vacancyid" where "AWRJobMasterID" = "new_awr"."AWRJobMasterID"
    end if
  else --linked, so inherit from the linked AWRJobMasterRecord
    update "AWRJobMaster" as "newrec"
      set "newrec"."AWRStatus" = "master"."AWRStatus","newrec"."benefits" = "master"."benefits","newrec"."notes" = "master"."notes","newrec"."holidays" = "master"."holidays",
      "newrec"."pay" = "master"."pay","newrec"."bonus" = "master"."bonus","newrec"."extrahols" = "master"."extrahols","newrec"."overridedate" = "master"."overridedate",
      "newrec"."overridesystem" = "master"."overridesystem","newrec"."AWRcheckdate" = "master"."AWRcheckdate" from
      "AWRJobMaster" as "master"
      where "newrec"."AWRJobMasterID" = "new_awr"."AWRJobMasterID"
      and "master"."AWRJobMasterID" = "new_awr"."AWRLinkCode" and "master"."AWRLinkCode" = "new_awr"."AWRLinkCode"
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."AWRJobMaster"."awrjobmaster_insert" IS 
{create trigger awrjobmaster_insert 
 after insert order 1 on
pears.awrjobmaster
referencing new as new_awr
for each row
begin
  if new_awr.AWRLinkCode is null then
    update AWRJobMaster set AWRLinkCode = AWRJobMasterID where AWRJobMasterID = new_awr.AWRJobMasterID
  end if;
  --not linking, so inherit values from AWRvacancy
  if new_awr.AWRLinkCode is null or new_awr.AWRLinkCode = new_awr.AWRJobMasterID then
    if(new_awr.benefits is null) and(new_awr.notes is null) and(new_awr.holidays is null) and(new_awr.pay is null) and(new_awr.bonus is null) and(new_awr.extrahols is null) then
      update AWRJobMaster set AWRStatus = v.AWRStatus,benefits = v.benefits,notes = v.notes,holidays = v.holidays,
        pay = v.pay,bonus = v.bonus,extrahols = v.extrahols from
        AWRvacancy as v join AWRJobMaster on v.vacancyid = AWRJobMaster.vacancyid where AWRJobMasterID = new_awr.AWRJobMasterID
    end if
  else
    --linked, so inherit from the linked AWRJobMasterRecord
    update AWRJobMaster as newrec set
      newrec.AWRStatus = master.AWRStatus,newrec.benefits = master.benefits,newrec.notes = master.notes,newrec.holidays = master.holidays,
      newrec.pay = master.pay,newrec.bonus = master.bonus,newrec.extrahols = master.extrahols,newrec.overridedate = master.overridedate,
      newrec.overridesystem = master.overridesystem,newrec.AWRcheckdate = master.AWRcheckdate from
      AWRJobMaster as master
      where newrec.AWRJobMasterID = new_awr.AWRJobMasterID
      and master.AWRJobMasterID = new_awr.AWRLinkCode and master.AWRLinkCode = new_awr.AWRLinkCode
  end if
end
}
go
</code>