pears.psHealthInsertUpdateShift [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.psHealthInsertUpdateShift ======
<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"."psHealthInsertUpdateShift"( 
  in @TempShiftID char(20),in @TempShiftPlanID char(20) ) 
begin
  declare "recipid" char(20);
  declare "tempid" char(20);
  declare "vacid" char(20);
  declare "sdate" timestamp;
  declare "edate" timestamp;
  declare "sstate" char(1);
  -- shift and shiftplan
  -- do the insert/update into psHealthShift here
  -- if inserting check sending recipient, temp, vacancy
  -- no recipient do nothing
  -- if updating check just flag resending
  if @TempShiftID is not null then
    select "recipientid" into "recipid" from "vacancy" key join "tempshift" where "tempshiftid" = @TempShiftID;
    select "personid","vacancyid" into "tempid","vacid" from "tempshift" where "tempshiftid" = @TempShiftID
  else
    select "recipientid","vacancy"."vacancyid" into "recipid","vacid" from "vacancy" key join "tempshiftplan" where "tempshiftplanid" = @TempShiftPlanID
  end if;
  if(not "psHealthCanSendVacancy"("vacid") = 1) or "recipid" is null then
    return
  end if;
  if "tempid" is not null then
    if "psHealthCanSendPerson"("tempid",'C') = 1 then
      call "psHealthInsertUpdatePerson"("tempid",'C')
    else return
    end if end if;
  if "psHealthCanSendPerson"("recipid",'R') = 1 then
    call "psHealthInsertUpdatePerson"("recipid",'R');
    if @TempShiftID is not null then
      select "shiftdate"+"timefrom","shiftdate"+"timeto"+(if "timefrom" > "timeto" then 1 else 0 endif),
        "state" into "sdate","edate","sstate" from "tempshift" where "tempshiftid" = @TempShiftID;
      if exists(select * from "psHealthShift" where "tempshiftid" = @TempShiftID) then
        update "psHealthShift" set "transferbatch" = 0,"startdatetime" = "sdate","enddatetime" = "edate","status" = (if "sstate" = 'C' then 'Cancelled'
          else 'Outstanding'
          endif),"updatedDateTime" = current timestamp where "tempshiftid" = @TempShiftID
      else
        insert into "psHealthShift"( "endDateTime",
          "isDeleted","name","psHealthShiftID","startDateTime","status","taskTypeId","tempshiftid","tenantBranchId","tenantCarerId","tenantCaseId","tenantCustomerId",
          "tenantTaskId","transferbatch" ) select "edate",0,(select "shiftserialnumber" from "tempshiftplan" where "tempshiftplanid" = "tempshift"."tempshiftplanid"),"uniquekey"('x'),"sdate",'Outstanding',
            "pshealthtasktype"(@TempShiftID,null),@TempShiftID,"pshealthbranch"(@TempShiftID,null,null,null),
            "appnumber",(select "refcode" from "vacancy" where "vacancyid" = "tempshift"."vacancyid"),(select "appnumber" from "person" where "personid" = (select "recipientid" from "vacancy" where "vacancyid" = "tempshift"."vacancyid")),
            (select "shiftserialnumber" from "tempshiftplan" where "tempshiftplanid" = "tempshift"."tempshiftplanid"),0 from "tempshift" key join "person" where "tempshiftid" = @TempShiftID;
        if not exists(select * from "psHealthVacancy" where "vacancyid" = (select "vacancyid" from "tempshift" where "tempshiftid" = @TempShiftID)) then
          insert into "psHealthVacancy"( "psHealthVacancyID","startDateTime","Status","tenantBranchId","tenantCaseId","tenantCustomerId","transferbatch","vacancyid" ) 
            select "uniquekey"('x'),"startdate",'Referred',
              "pshealthbranch"(@TempShiftID,null,null,null),
              "refcode",(select "appnumber" from "person" where "personid" = "recipientid"),0,"Vacancy"."vacancyid" from "tempshift" key join "vacancy" where "tempshiftid" = @TempShiftID
        end if
      end if
    else if exists(select * from "psHealthShift" where "tempshiftplanid" = @TempShiftPlanID) then
        update "psHealthShift" set "transferbatch" = 0 where "tempshiftplanid" = @TempShiftPlanID
      else
        --            this will need completing if using shiftplan
        --            this will need completing if using shiftplan
      end if
    end if
  end if
end
</code>