pears.NetCandidateAddEventsOneDay [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.NetCandidateAddEventsOneDay ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."NetCandidateAddEventsOneDay"( in "pWebUserID" char(20),in "pvacancyid" char(20),in "pshifttemplateid" char(20),in "pDateFrom" date,
  in "pTimeFrom" char(25) default null,in "pDateTo" date,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 "pShiftType" 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 ) 
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 "pOrderReason" char(100);
  if "pdateFrom" 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
  while "cnt" > 0 loop
    set "sid" = "uniquekey"("cnt");
    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"),"pdateFrom","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","pdateFrom","isnull"("tfrom","timefrom"),"isnull"("tto","timeto"),"isnull"("ibreak","breakminutes"),'C',"sid",'~'
          from "tempshifttemplate" where "tempshifttemplateid" = "pshifttemplateid"
    end if;
    set "slog" = (select "string"("dateformat"("pdateFrom",'dd/mm/yyyy'),' ',"dateformat"("isnull"("tfrom","timefrom"),'hh:nn'),'-',"dateformat"("isnull"("tto","timeto"),'hh:nn'),' ',"description")
        from "tempshifttemplate" where "tempshifttemplateid" = "pshifttemplateid");
    set "pLog" = "string"("pLog","slog","char"(13),"char"(10));
    set "cnt" = "cnt"-1
  end loop
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateAddEventsOneDay" IS 
{create procedure pears."NetCandidateAddEventsOneDay"( in "pWebUserID" char(20),in "pvacancyid" char(20),in "pshifttemplateid" char(20),in "pDateFrom" date,
  in "pTimeFrom" char(25) default null,in "pDateTo" date,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 "pShiftType" 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 ) 
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 "pOrderReason" char(100);
  if "pdateFrom" 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
  while "cnt" > 0 loop
    set "sid" = "uniquekey"("cnt");
    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"),"pdateFrom","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","pdateFrom","isnull"("tfrom","timefrom"),"isnull"("tto","timeto"),"isnull"("ibreak","breakminutes"),'C',"sid",'~'
          from "tempshifttemplate" where "tempshifttemplateid" = "pshifttemplateid"
    end if;
    set "slog" = (select "string"("dateformat"("pdateFrom",'dd/mm/yyyy'),' ',"dateformat"("isnull"("tfrom","timefrom"),'hh:nn'),'-',"dateformat"("isnull"("tto","timeto"),'hh:nn'),' ',"description")
        from "tempshifttemplate" where "tempshifttemplateid" = "pshifttemplateid");
    set "pLog" = "string"("pLog","slog","char"(13),"char"(10));
    set "cnt" = "cnt"-1
  end loop
end
}
</code>