pears.TempShiftCancel

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

CREATE PROCEDURE "pears"."TempShiftCancel"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "TSPID" CHAR(20),IN "CancReasID" CHAR(2),IN "InNote" CHAR(100) ) 
BEGIN
  DECLARE "TSID" CHAR(20);
  DECLARE @Bill INTEGER;
  DECLARE @ClientAware INTEGER;
  DECLARE @TempAware INTEGER;
  DECLARE @Pay INTEGER;
  DECLARE @Refill INTEGER;
  SELECT FIRST "tempshiftid" INTO "tsid" FROM "tempshift" WHERE "tempshiftplanid" = "tspid" ORDER BY "isnull"("whencancelled","whenentered") DESC;
  SELECT "Bill","ClientAware","TempAware","Pay","Refill" INTO @Bill,@ClientAware,@TempAware,@Pay,@Refill FROM "tempshiftcancelreason" WHERE "tempshiftcancelreasonid" = "CancReasID";
  IF "TSID" > '' THEN
    UPDATE "tempshift" SET "state" = 'C',"cancelreason" = "CancReasID","whencancelled" = CURRENT TIMESTAMP,"whocancelled" = "userstaffid",
      "clientconfirmed" = @ClientAware,"tempconfirmed" = @TempAware,"cbill" = @bill,"cpay" = @pay,"crefill" = @Refill,"Note" = "InNote" WHERE "tempshiftid" = "TSID"
  ELSE
    INSERT INTO "tempshift"( "tempshiftid","vacancyid","shiftdate","timefrom","timeto","state","note","tempshiftplanid","breakminutes","essentialskillgradeid",
      "cancelreason","whencancelled","whocancelled","cbill","clientconfirmed","tempconfirmed","cpay","crefill","referencecode" ) SELECT "uniquekey"(''''),"vacancyid","shiftdate","timefrom","timeto",'C',"InNote",
        "tempshiftplanid","breakminutes","essentialskillgradeid","CancReasID",CURRENT TIMESTAMP,"userstaffid",@Bill,@ClientAware,1,0,0,"referencecode" FROM "tempshiftplan" WHERE "tempshiftplanid" = "TSPID"
  END IF
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."TempShiftCancel" IS 
{CREATE PROCEDURE TempShiftCancel 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
( IN TSPID CHAR(20),IN CancReasID CHAR(2),IN InNote CHAR(100) ) 
BEGIN
  DECLARE TSID CHAR(20);
  DECLARE @Bill INTEGER;
  DECLARE @ClientAware INTEGER;
  DECLARE @TempAware INTEGER;
  DECLARE @Pay INTEGER;
  DECLARE @Refill INTEGER;
  SELECT FIRST tempshiftid INTO tsid FROM tempshift WHERE tempshiftplanid = tspid ORDER BY isnull(whencancelled,whenentered) DESC;
  SELECT Bill,ClientAware,TempAware,Pay,Refill INTO @Bill,@ClientAware,@TempAware,@Pay,@Refill FROM tempshiftcancelreason WHERE tempshiftcancelreasonid = CancReasID; 
  IF TSID > '' THEN
    UPDATE tempshift SET state = 'C',cancelreason = CancReasID,whencancelled = CURRENT TIMESTAMP,whocancelled = userstaffid,
      clientconfirmed = @ClientAware,tempconfirmed = @TempAware,cbill = @bill,cpay = @pay,crefill = @Refill,Note = InNote WHERE tempshiftid = TSID
  ELSE
    INSERT INTO tempshift( tempshiftid,vacancyid,shiftdate,timefrom,timeto,state,note,tempshiftplanid,breakminutes,essentialskillgradeid,
      cancelreason,whencancelled,whocancelled,cbill,clientconfirmed,tempconfirmed,cpay,crefill,referencecode ) SELECT uniquekey(''''),vacancyid,shiftdate,timefrom,timeto,'C',InNote,
        tempshiftplanid,breakminutes,essentialskillgradeid,CancReasID,CURRENT TIMESTAMP,userstaffid,@Bill,@ClientAware,1,0,0,referencecode FROM tempshiftplan WHERE tempshiftplanid = TSPID
  END IF
END
}