pears.NetClientAddShiftsOneDay

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original SQL

CREATE PROCEDURE "pears"."NetClientAddShiftsOneDay"( IN "pWebUserID" CHAR(20),IN "pvacancyid" CHAR(20),IN "pshifttemplateid" CHAR(20),IN "pdate" DATE,
  IN "ptimefrom" CHAR(25) DEFAULT NULL,IN "ptimeto" CHAR(25) DEFAULT NULL,IN "pBreak" CHAR(20) DEFAULT NULL,IN "pTheirRef" CHAR(50) DEFAULT NULL,IN "pNote" CHAR(50) DEFAULT NULL,IN "pQty" CHAR(20) DEFAULT NULL,
  IN "pOrderReason" CHAR(50) DEFAULT NULL,IN "pSkills" CHAR(100) DEFAULT NULL,IN "pGrade" CHAR(50) DEFAULT NULL,IN "pWebUserName" CHAR(50) DEFAULT NULL,IN "pCanAuthorise" SMALLINT DEFAULT 0,INOUT "pLog" long VARCHAR ) 
-- 20130123 check whether order reason is compulsory before inserting
BEGIN
  DECLARE "sid" CHAR(20);
  DECLARE "cnt" INTEGER;
  DECLARE "i" INTEGER;
  DECLARE "tfrom" TIME;
  DECLARE "tto" TIME;
  DECLARE "ibreak" INTEGER;
  DECLARE "slog" CHAR(200);
  DECLARE "isReasonCompulsory" INTEGER;
  IF "pdate" IS NULL THEN
    RETURN
  END IF;
  SET "i" = "charindex"('|',"pshifttemplateid");
  IF "i" > 0 THEN -- Get rid of any extra info tacked onto the code
    SET "pshifttemplateid" = "left"("pshifttemplateid","i"-1)
  END IF;
  IF "trim"("isnull"("pshifttemplateid",'')) = '' THEN
    RETURN
  END IF;
  SET "cnt" = "isnull"("iqxnetstringtointeger"("pqty"),0);
  IF "cnt" <= 0 THEN
    RETURN
  END IF;
  SET "tfrom" = "iqxnetstringtotime"("ptimefrom");
  SET "tto" = "iqxnetstringtotime"("ptimeto");
  SET "ibreak" = "iqxnetstringtointeger"("pbreak"); -- If times and break not supplied they will come from the template
  SET "slog" = (SELECT "string"("dateformat"("pdate",'dd/mm/yyyy'),' ',"dateformat"("isnull"("tfrom","timefrom"),'hh:nn'),'-',"dateformat"("isnull"("tto","timeto"),'hh:nn'),' ',"description",' Qty: ',"cnt")
      FROM "tempshifttemplate" WHERE "tempshifttemplateid" = "pshifttemplateid");
  IF "trim"("isnull"("pnote",'')) <> '' THEN
    SET "slog" = "string"("slog",' Note: ',"pnote")
  END IF;
  while "cnt" > 0 loop
    SET "sid" = "uniquekey"("cnt");
    SET "isReasonCompulsory" = (SELECT FIRST "ShiftOrderReasonRequired" FROM "TempShiftTemplate" WHERE "TempShiftTemplateID" = "pshifttemplateid");
    IF "pOrderReason" = '' AND "isReasonCompulsory" = 1 THEN
      SELECT '105~Reason is compulsory';
      RETURN
    END IF;
    INSERT INTO "tempshiftplan"( "TempShiftPlanID","VacancyID","TimeFrom","TimeTo","BreakMinutes","ShiftDate","Moveable","Description","ClientNote","EssentialSkill","EssentialSkillChoiceList","EssentialSkillGradeID","ReferenceCode","ReferenceRequired","ClientMustConfirm","TempMustConfirm","OrderedBy","TempShiftTypeID","AnalysisCode","RecoveryHours","TempShiftOrderReasonID" ) 
      SELECT "sid","pvacancyid","isnull"("tfrom","timefrom"),"isnull"("tto","timeto"),"isnull"("ibreak","breakminutes"),"pdate","Moveable","Description","pNote","string"("taglocation",';',"tagid",';',"tagchoiceid",';',"gradetagid"),"replace"("pskills","char"(9),';'),"pgrade","ucase"("pTheirRef"),"ReferenceRequired","ClientMustConfirm","TempMustConfirm","pWebUserName","TempShiftTypeID","AnalysisCode","RecoveryHours","nullif"("pOrderReason",'')
        FROM "tempshifttemplate" WHERE "tempshifttemplateid" = "pshifttemplateid";
    CALL "NetSetShiftPlanCascade"("sid");
    IF "pCanAuthorise" = 0 THEN
      INSERT INTO "tempshift"( "tempshiftid","vacancyid","shiftdate","timefrom","timeto","breakminutes","state","tempshiftplanid","cancelreason" ) 
        SELECT "sid","pvacancyid","pdate","isnull"("tfrom","timefrom"),"isnull"("tto","timeto"),"isnull"("ibreak","breakminutes"),'C',"sid",'~'
          FROM "tempshifttemplate" WHERE "tempshifttemplateid" = "pshifttemplateid"
    END IF;
    SET "cnt" = "cnt"-1
  END loop;
  SET "pLog" = "string"("pLog","slog","char"(13),"char"(10))
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientAddShiftsOneDay" IS 
{CREATE PROCEDURE pears."NetClientAddShiftsOneDay"( IN "pWebUserID" CHAR(20),IN "pvacancyid" CHAR(20),IN "pshifttemplateid" CHAR(20),IN "pdate" DATE,
  IN "ptimefrom" CHAR(25) DEFAULT NULL,IN "ptimeto" CHAR(25) DEFAULT NULL,IN "pBreak" CHAR(20) DEFAULT NULL,IN "pTheirRef" CHAR(50) DEFAULT NULL,IN "pNote" CHAR(50) DEFAULT NULL,IN "pQty" CHAR(20) DEFAULT NULL,
  IN "pOrderReason" CHAR(50) DEFAULT NULL,IN "pSkills" CHAR(100) DEFAULT NULL,IN "pGrade" CHAR(50) DEFAULT NULL,IN "pWebUserName" CHAR(50) DEFAULT NULL,IN "pCanAuthorise" SMALLINT DEFAULT 0,INOUT "pLog" long VARCHAR ) 
-- 20130123 check whether order reason is compulsory before inserting
BEGIN
  DECLARE "sid" CHAR(20);
  DECLARE "cnt" INTEGER;
  DECLARE "i" INTEGER;
  DECLARE "tfrom" TIME;
  DECLARE "tto" TIME;
  DECLARE "ibreak" INTEGER;
  DECLARE "slog" CHAR(200);
  DECLARE "isReasonCompulsory" INTEGER;
  IF "pdate" IS NULL THEN
    RETURN
  END IF;
  SET "i" = "charindex"('|',"pshifttemplateid");
  IF "i" > 0 THEN -- Get rid of any extra info tacked onto the code
    SET "pshifttemplateid" = "left"("pshifttemplateid","i"-1)
  END IF;
  IF "trim"("isnull"("pshifttemplateid",'')) = '' THEN
    RETURN
  END IF;
  SET "cnt" = "isnull"("iqxnetstringtointeger"("pqty"),0);
  IF "cnt" <= 0 THEN
    RETURN
  END IF;
  SET "tfrom" = "iqxnetstringtotime"("ptimefrom");
  SET "tto" = "iqxnetstringtotime"("ptimeto");
  SET "ibreak" = "iqxnetstringtointeger"("pbreak"); -- If times and break not supplied they will come from the template
  SET "slog" = (SELECT "string"("dateformat"("pdate",'dd/mm/yyyy'),' ',"dateformat"("isnull"("tfrom","timefrom"),'hh:nn'),'-',"dateformat"("isnull"("tto","timeto"),'hh:nn'),' ',"description",' Qty: ',"cnt")
      FROM "tempshifttemplate" WHERE "tempshifttemplateid" = "pshifttemplateid");
  IF "trim"("isnull"("pnote",'')) <> '' THEN
    SET "slog" = "string"("slog",' Note: ',"pnote")
  END IF;
  while "cnt" > 0 loop
    SET "sid" = "uniquekey"("cnt");
    SET "isReasonCompulsory" = (SELECT FIRST "ShiftOrderReasonRequired" FROM "TempShiftTemplate" WHERE "TempShiftTemplateID" = "pshifttemplateid");
    IF "pOrderReason" = '' AND "isReasonCompulsory" = 1 THEN
      SELECT '105~Reason is compulsory';
      RETURN
    END IF;
    INSERT INTO "tempshiftplan"( "TempShiftPlanID","VacancyID","TimeFrom","TimeTo","BreakMinutes","ShiftDate","Moveable","Description","ClientNote","EssentialSkill","EssentialSkillChoiceList","EssentialSkillGradeID","ReferenceCode","ReferenceRequired","ClientMustConfirm","TempMustConfirm","OrderedBy","TempShiftTypeID","AnalysisCode","RecoveryHours","TempShiftOrderReasonID" ) 
      SELECT "sid","pvacancyid","isnull"("tfrom","timefrom"),"isnull"("tto","timeto"),"isnull"("ibreak","breakminutes"),"pdate","Moveable","Description","pNote","string"("taglocation",';',"tagid",';',"tagchoiceid",';',"gradetagid"),"replace"("pskills","char"(9),';'),"pgrade","ucase"("pTheirRef"),"ReferenceRequired","ClientMustConfirm","TempMustConfirm","pWebUserName","TempShiftTypeID","AnalysisCode","RecoveryHours","nullif"("pOrderReason",'')
        FROM "tempshifttemplate" WHERE "tempshifttemplateid" = "pshifttemplateid";
    CALL "NetSetShiftPlanCascade"("sid");
    IF "pCanAuthorise" = 0 THEN
      INSERT INTO "tempshift"( "tempshiftid","vacancyid","shiftdate","timefrom","timeto","breakminutes","state","tempshiftplanid","cancelreason" ) 
        SELECT "sid","pvacancyid","pdate","isnull"("tfrom","timefrom"),"isnull"("tto","timeto"),"isnull"("ibreak","breakminutes"),'C',"sid",'~'
          FROM "tempshifttemplate" WHERE "tempshifttemplateid" = "pshifttemplateid"
    END IF;
    SET "cnt" = "cnt"-1
  END loop;
  SET "pLog" = "string"("pLog","slog","char"(13),"char"(10))
END
}