pears.AutoMatchProcessShiftQueue

CREATE PROCEDURE "pears"."AutoMatchProcessShiftQueue"()
/* Application Maintained Function / Procedure - DO NOT EDIT*/
BEGIN
  CREATE OR REPLACE variable "GlobalMaxEmails" INTEGER;
  CREATE OR REPLACE variable "GlobalMaxSMS" INTEGER;
  CREATE OR REPLACE variable "GlobalMaxPush" INTEGER;
  CREATE OR REPLACE variable "GlobalMinEmailGap" INTEGER;
  CREATE OR REPLACE variable "GlobalMinSMSGap" INTEGER;
  CREATE OR REPLACE variable "GlobalMinPushGap" INTEGER;
  SELECT FIRST "MaxEmails","MaxSMS","MaxPush","MinDelayBetweenEmails","MinDelayBetweenSMS","MinDelayBetweenPush" INTO "GlobalMaxEmails",
    "GlobalMaxSMS","GlobalMaxPush","GlobalMinEmailGap","GlobalMinSMSGap","GlobalMinPushGap" FROM "AutoMatchGlobalConfig";
  FOR "s" AS "tscursor" no scroll cursor FOR
    SELECT "AutoMatchShiftQueueID" AS "QID","TempShiftPlanID" AS "TPID","Created"
      FROM(SELECT "AutoMatchShiftQueueID","TempShiftPlanID","Created","max"("Created") OVER(partition BY "TempShiftPlanID") AS "maxCreate"
          FROM "AutoMatchShiftQueue" WHERE "NotRequired" = 0 AND "Completed" IS NULL) AS "Shifts"
      WHERE "Created" = "maxCreate"
      ORDER BY "TPID" ASC FOR READ ONLY do
    CALL "AutoMatchProcessShift"("TPID");
    UPDATE "AutoMatchShiftQueue" SET "completed" = CURRENT TIMESTAMP WHERE "AutoMatchShiftQueueID" = "QID";
    UPDATE "AutoMatchShiftQueue" SET "NotRequired" = 1 WHERE "AutoMatchShiftQueueID" <> "QID" AND "Completed" IS NULL AND "TempShiftPlanID" = "TPID"
  END FOR
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."AutoMatchProcessShiftQueue" IS 
{CREATE PROCEDURE AutoMatchProcessShiftQueue 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
()
BEGIN
  CREATE OR REPLACE VARIABLE GlobalMaxEmails INTEGER; 
  CREATE OR REPLACE VARIABLE GlobalMaxSMS INTEGER; 
  CREATE OR REPLACE VARIABLE GlobalMaxPush INTEGER; 
  CREATE OR REPLACE VARIABLE GlobalMinEmailGap INTEGER; 
  CREATE OR REPLACE VARIABLE GlobalMinSMSGap INTEGER; 
  CREATE OR REPLACE VARIABLE GlobalMinPushGap INTEGER; 
  SELECT FIRST MaxEmails, MaxSMS, MaxPush, MinDelayBetweenEmails, MinDelayBetweenSMS, MinDelayBetweenPush INTO GlobalMaxEmails, 
    GlobalMaxSMS, GlobalMaxPush, GlobalMinEmailGap, GlobalMinSMSGap, GlobalMinPushGap FROM AutoMatchGlobalConfig; 
  FOR s AS tscursor no scroll cursor FOR
    SELECT  AutoMatchShiftQueueID AS QID, TempShiftPlanID AS TPID, Created FROM
					(SELECT AutoMatchShiftQueueID, TempShiftPlanID, Created, MAX(Created) OVER (partition BY TempShiftPlanID ) maxCreate
						FROM AutoMatchShiftQueue WHERE NotRequired = 0 AND Completed IS NULL) Shifts
					WHERE Created = maxCreate
					ORDER BY TPID FOR READ ONLY do
      CALL AutoMatchProcessShift(TPID);
		   UPDATE AutoMatchShiftQueue SET completed = CURRENT TIMESTAMP WHERE AutoMatchShiftQueueID = QID; 
		   UPDATE AutoMatchShiftQueue SET NotRequired = 1  WHERE AutoMatchShiftQueueID <> QID AND Completed IS NULL AND TempShiftPlanID = TPID;
		END FOR;			
END
}