pears.AWRWeeklyDetail

Records indicate qualifying / pausing activity etc in given week for AWRJobMaster records.

• AWRWeeklyDetailID

• No incoming foreign keys found.

-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."AWRWeeklyDetail"
-- Table comment: Records indicate qualifying / pausing activity etc in given week for AWRJobMaster records.
-- Statement count: 15
 
CREATE TABLE "pears"."AWRWeeklyDetail" (
    "AWRWeeklyDetailID"              CHAR(20) NOT NULL
   ,"Period"                         INTEGER NULL
   ,"QualType"                       SMALLINT NULL
   ,"Locked"                         SMALLINT NULL
   ,"Disregard"                      SMALLINT NULL
   ,"WhenEntered"                    TIMESTAMP NULL DEFAULT CURRENT TIMESTAMP
   ,"WhoEntered"                     CHAR(20) NULL
   ,"AWRreason"                      CHAR(1) NULL
   ,"PersonID"                       CHAR(20) NULL
   ,"AWRJobMasterID"                 CHAR(20) NULL
   ,"CompanyID"                      CHAR(20) NULL
   ,"TempTimesheetID"                CHAR(20) NULL
   ,"Notes"                          long VARCHAR NULL
   ,PRIMARY KEY ("AWRWeeklyDetailID" ASC) 
   ,CONSTRAINT "OnlyOne" CHECK(("personid" IS NULL AND "companyid" IS NULL AND "awrjobmasterid" IS NOT NULL) OR("personid" IS NULL AND "companyid" IS NOT NULL AND "awrjobmasterid" IS NULL)
OR("personid" IS NOT NULL AND "companyid" IS NULL AND "awrjobmasterid" IS NULL))
)
GO
 
 
COMMENT ON COLUMN "pears"."AWRWeeklyDetail"."QualType" IS 
	'1=Pause 2= Continue'
GO
 
 
COMMENT ON COLUMN "pears"."AWRWeeklyDetail"."AWRreason" IS 
	'if personid then unavail reason, if company 1 = shutdown 2 = strike, if neither 1 = other agency work'
GO
 
 
COMMENT ON TABLE "pears"."AWRWeeklyDetail" IS 
	'Records indicate qualifying / pausing activity etc in given week for AWRJobMaster records.'
GO
 
 
ALTER TABLE "pears"."AWRWeeklyDetail"
    ADD FOREIGN KEY "TempTimesheet" ("TempTimesheetID" ASC)
    REFERENCES "pears"."TempTimeSheet" ("TempTimeSheetID")
    ON DELETE SET NULL
GO
 
 
ALTER TABLE "pears"."AWRWeeklyDetail"
    ADD FOREIGN KEY "Person" ("PersonID" ASC)
    REFERENCES "pears"."Person" ("personid")
GO
 
 
ALTER TABLE "pears"."AWRWeeklyDetail"
    ADD FOREIGN KEY "AWRJobMaster" ("AWRJobMasterID" ASC)
    REFERENCES "pears"."AWRJobMaster" ("AWRJobMasterID")
    ON DELETE CASCADE
GO
 
 
ALTER TABLE "pears"."AWRWeeklyDetail"
    ADD FOREIGN KEY "Company" ("CompanyID" ASC)
    REFERENCES "pears"."Company" ("companyid")
GO
 
 
CREATE INDEX "AWRWeeklyDetail_Personid_Period" ON "pears"."AWRWeeklyDetail"
    ( "PersonID","Period" )
GO
 
 
CREATE INDEX "AWRWeeklyDetail_Companyid_Period" ON "pears"."AWRWeeklyDetail"
    ( "CompanyID","Period" )
GO
 
 
CREATE INDEX "AWRWeeklyDetail_AWRjobmasterid_period" ON "pears"."AWRWeeklyDetail"
    ( "AWRJobMasterID","Period" )
GO
 
 
CREATE TRIGGER "AWRWeeklyDetail_update" BEFORE UPDATE OF "TempTimesheetID"
ORDER 1 ON "pears"."AWRWeeklyDetail"
REFERENCING OLD AS "old_awr" NEW AS "new_awr"
FOR each ROW
BEGIN
  SET "new_awr"."disregard" = 1;
  SET "new_awr"."Notes" = "string"('Timesheet ',(SELECT "serialnumber" FROM "temptimesheet" WHERE "temptimesheetid" = "old_awr"."temptimesheetid"),
    ' was deleted by ',(SELECT "userid" FROM "staff" WHERE "staffid" = "userstaffid"),' on ',CURRENT TIMESTAMP)
exception
  WHEN others THEN
    SET "new_awr"."Notes" = "string"('Timesheet ',(SELECT "serialnumber" FROM "temptimesheet" WHERE "temptimesheetid" = "old_awr"."temptimesheetid"),
      'was deleted by External System on ',CURRENT TIMESTAMP,' connection - ',@@SPID)
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."AWRWeeklyDetail"."AWRWeeklyDetail_update" IS 
{CREATE TRIGGER AWRWeeklyDetail_update 
 BEFORE UPDATE OF TempTimesheetID
ORDER 1 ON pears.AWRWeeklyDetail
REFERENCING OLD AS old_awr NEW AS new_awr
FOR each ROW
BEGIN
  SET new_awr.disregard = 1;
  SET new_awr.Notes = string('Timesheet ',(SELECT serialnumber FROM temptimesheet WHERE temptimesheetid = old_awr.temptimesheetid), 
 ' was deleted by ',(SELECT userid FROM staff WHERE staffid = userstaffid),' on ',CURRENT TIMESTAMP)
  exception 
  WHEN others THEN 
  SET new_awr.Notes = string('Timesheet ',(SELECT serialnumber FROM temptimesheet WHERE temptimesheetid = old_awr.temptimesheetid), 
   'was deleted by External System on ',CURRENT TIMESTAMP, ' connection - ',@@SPID) 
END
}
GO
 
 
CREATE TRIGGER "AWRWeeklyDetail_updateaudit" after UPDATE OF "disregard"
ORDER 1 ON "pears"."AWRWeeklyDetail"
REFERENCING OLD AS "old_awr" NEW AS "new_awr"
FOR each ROW
BEGIN
  DECLARE "ref" CHAR(20);
  DECLARE @grade CHAR(4);
  DECLARE @speciality CHAR(4);
  DECLARE "rname" CHAR(50);
  DECLARE "auditstr" long VARCHAR;
  -- audit
  SELECT FIRST "Name" INTO "rname" FROM "TempShiftUnavailableReason" WHERE "TempShiftUnavailableReasonID" = "new_awr"."AWRReason";
  SELECT "refcode" INTO "ref" FROM "placement" KEY JOIN "awrjobmaster" WHERE "awrjobmasterid" = "new_awr"."awrjobmasterid";
  SET "auditstr" = "string"('AWR Weekly Placement OurRef - ',"ref",(IF @Grade IS NOT NULL THEN "string"(', Grade - ',@Grade) ELSE '' endif),
    IF @speciality IS NOT NULL THEN "string"(', Speciality - ',@speciality)
    ELSE ''
    endif,'Period - ',"new_awr"."period",', Type - ',"isnull"((IF "new_awr"."personid" IS NOT NULL THEN
      "rname" ELSE NULL endif),
    (IF "new_awr"."awrjobmasterid" IS NOT NULL THEN IF "new_awr"."AWRReason" = '1' THEN 'Other Agency' ELSE 'Timesheet' endif ELSE NULL endif),
    (IF "new_awr"."companyid" IS NOT NULL THEN IF "new_awr"."AWRReason" = '1' THEN 'Holiday Shutdown' ELSE 'Strike' endif endif)));
  CALL "AuditLog"('AWRWEEK',"old_awr"."AWRWeeklyDetailID",
  "auditstr",
  "old_awr"."disregard","new_awr"."disregard")
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."AWRWeeklyDetail"."AWRWeeklyDetail_updateaudit" IS 
{CREATE TRIGGER AWRWeeklyDetail_updateaudit 
 after UPDATE OF disregard
ORDER 1 ON pears.AWRWeeklyDetail
REFERENCING OLD AS old_awr NEW AS new_awr
FOR each ROW
BEGIN
  DECLARE REF CHAR(20);
  DECLARE @grade CHAR(4);
  DECLARE @speciality CHAR(4);
  DECLARE rname CHAR(50);
  DECLARE auditstr long VARCHAR;
  -- audit
  SELECT FIRST Name INTO rname FROM TempShiftUnavailableReason WHERE TempShiftUnavailableReasonID = new_awr.AWRReason; 
  SELECT refcode INTO REF FROM placement KEY JOIN awrjobmaster WHERE awrjobmasterid = new_awr.awrjobmasterid;
  SET auditstr = string('AWR Weekly Placement OurRef - ',REF,(IF @Grade IS NOT NULL THEN string(', Grade - ',@Grade) ELSE '' 
  endif),IF @speciality IS NOT NULL THEN string(', Speciality - ',@speciality)
  ELSE ''
  endif, 'Period - ',new_awr.period,', Type - ', isnull((IF new_awr.personid IS NOT NULL THEN 
 rname ELSE NULL endif),
 (IF new_awr.awrjobmasterid IS NOT NULL THEN IF new_awr.AWRReason = '1' THEN 'Other Agency' ELSE 'Timesheet' endif ELSE NULL endif), 
 (IF new_awr.companyid IS NOT NULL THEN IF new_awr.AWRReason = '1' THEN 'Holiday Shutdown' ELSE 'Strike' endif endif)));
  CALL AuditLog('AWRWEEK',old_awr.AWRWeeklyDetailID,
   auditstr,
 old_awr.disregard, new_awr.disregard)
END
}
GO