====== pears.NetClientVacanciesLineTimesUpdate ======
<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"."NetClientVacanciesLineTimesUpdate"( in "pWebUserID" char(20),in "pRowData" char(40) default null ) 
result( "retval" char(250) ) 
-- 20120705 update shift times
-- 20120710 suppress popup
-- 20130213 created from GenevaNetClientVacanciesLineTimesUpdate
begin
  declare "TableID" char(1);
  declare "RowID" char(20);
  declare "NewTime" char(6);
  declare "WhichTime" char(8);
  declare "IntString" char(20);
  declare "VacID" char(20);
  declare "EmploymentID" char(20);
  declare "ShiftRef" char(10);
  set "TableID" = "substring"("pRowData",1,2);
  set "RowID" = "substring"("pRowData",2,"charindex"('~',"pRowData")-1);
  set "IntString" = "substring"("pRowData","charindex"('~',"pRowData")+1);
  set "NewTime" = "substring"("IntString",1,"charindex"('~',"IntString")-1);
  set "WhichTime" = "substring"("IntString","charindex"('~',"IntString")+1);
  if "isdate"("NewTime") = 0 or "charindex"(':',"NewTime") = 0 then
    select '1:~Not a valid time'
  end if;
  if "TableID" = 'P' then
    (select "t"."vacancyid","ShiftSerialNumber","e"."employmentid" into "VacID","ShiftRef","EmploymentID" from "tempshiftplan" as "t" key join "vacancy" key join "employment" as "e" where "tempshiftplanid" = "RowID")
  else
    (select "t"."vacancyid","e"."employmentid","p"."ShiftSerialNumber" into "VacID","EmploymentID","ShiftRef" from "tempshiftplan" as "p" key join "tempshift" as "t" key join "vacancy" key join "employment" as "e" left outer join "person" as "p" on "p"."personid" = "t"."personid" where "t"."tempshiftid" = "RowID")
  end if;
  if "TableID" = 'V' then
    if "WhichTime" = 'S' then
      update "tempshift" set "TimeFrom" = "NewTime" where "tempshiftid" = "RowID"
    else
      update "tempshift" set "TimeTo" = "NewTime" where "tempshiftid" = "RowID"
    end if
  else if "WhichTime" = 'S' then
      update "tempshiftplan" set "TimeFrom" = "NewTime" where "tempshiftplanid" = "RowID"
    else
      update "tempshiftplan" set "TimeTo" = "NewTime" where "tempshiftplanid" = "RowID"
    end if end if;
  set "NewTime" = "dateformat"("NewTime",'hh:mm');
  if "WhichTime" = 'S' then
    set "WhichTime" = 'Start'
  else
    set "WhichTime" = 'End'
  end if;
  call "GenevaIQXNetPopup"("pWebUserID",null,"EmploymentID","VacID",null,null,null,'Shift '+"WhichTime"+' time modified - '+"ShiftRef",'Shift '+"WhichTime"+' time modified - '+"NewTime",'N');
  select '1:~success:'+"NewTime"
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientVacanciesLineTimesUpdate" IS 
{create procedure pears."NetClientVacanciesLineTimesUpdate"( in "pWebUserID" char(20),in "pRowData" char(40) default null ) 
result( "retval" char(250) ) 
-- 20120705 update shift times
-- 20120710 suppress popup
-- 20130213 created from GenevaNetClientVacanciesLineTimesUpdate
begin
  declare "TableID" char(1);
  declare "RowID" char(20);
  declare "NewTime" char(6);
  declare "WhichTime" char(8);
  declare "IntString" char(20);
  declare "VacID" char(20);
  declare "EmploymentID" char(20);
  declare "ShiftRef" char(10);
  set "TableID" = "substring"("pRowData",1,2);
  set "RowID" = "substring"("pRowData",2,"charindex"('~',"pRowData")-1);
  set "IntString" = "substring"("pRowData","charindex"('~',"pRowData")+1);
  set "NewTime" = "substring"("IntString",1,"charindex"('~',"IntString")-1);
  set "WhichTime" = "substring"("IntString","charindex"('~',"IntString")+1);
  if "isdate"("NewTime") = 0 or "charindex"(':',"NewTime") = 0 then
    select '1:~Not a valid time'
  end if;
  if "TableID" = 'P' then
    (select "t"."vacancyid","ShiftSerialNumber","e"."employmentid" into "VacID","ShiftRef","EmploymentID" from "tempshiftplan" as "t" key join "vacancy" key join "employment" as "e" where "tempshiftplanid" = "RowID")
  else
    (select "t"."vacancyid","e"."employmentid","p"."ShiftSerialNumber" into "VacID","EmploymentID","ShiftRef" from "tempshiftplan" as "p" key join "tempshift" as "t" key join "vacancy" key join "employment" as "e" left outer join "person" as "p" on "p"."personid" = "t"."personid" where "t"."tempshiftid" = "RowID")
  end if;
  if "TableID" = 'V' then
    if "WhichTime" = 'S' then
      update "tempshift" set "TimeFrom" = "NewTime" where "tempshiftid" = "RowID"
    else
      update "tempshift" set "TimeTo" = "NewTime" where "tempshiftid" = "RowID"
    end if
  else if "WhichTime" = 'S' then
      update "tempshiftplan" set "TimeFrom" = "NewTime" where "tempshiftplanid" = "RowID"
    else
      update "tempshiftplan" set "TimeTo" = "NewTime" where "tempshiftplanid" = "RowID"
    end if end if;
  set "NewTime" = "dateformat"("NewTime",'hh:mm');
  if "WhichTime" = 'S' then
    set "WhichTime" = 'Start'
  else
    set "WhichTime" = 'End'
  end if;
  call "GenevaIQXNetPopup"("pWebUserID",null,"EmploymentID","VacID",null,null,null,'Shift '+"WhichTime"+' time modified - '+"ShiftRef",'Shift '+"WhichTime"+' time modified - '+"NewTime",'N');
  select '1:~success:'+"NewTime"
end
}
</code>