pears.SetTempProvTimeSheetStatus

CREATE FUNCTION "pears"."SetTempProvTimeSheetStatus"( IN @TempProvTimeSheetID CHAR(20),IN @NewStatus INTEGER,IN @Description CHAR(250),IN @StaffID CHAR(20),IN @ExternalUserID CHAR(40) ) 
RETURNS CHAR(250)
BEGIN
  DECLARE @OldStatus INTEGER;
  DECLARE @NewTempProvTimesheetHistoryID CHAR(20);
  DECLARE @SerialNumber INTEGER;
  IF "varexists"('uniqueroll') = 0 THEN
    CREATE variable "uniqueroll" INTEGER
  END IF;
  SELECT "ExtNumber","SerialNumber" INTO @OldStatus,@SerialNumber FROM "TempProvTimesheet" WHERE "TempProvTimesheetID" = @TempProvTimesheetID;
  IF @@rowcount <> 1 THEN
    RETURN('2:TempProvTimesheet not found')
  END IF;
  BEGIN atomic
    UPDATE "TempProvTimesheet" SET "ExtNumber" = @NewStatus WHERE "TempProvTimesheetID" = @TempProvTimesheetID;
    IF @@rowcount <> 1 THEN
      RETURN('2:TempProvTimesheet not found')
    END IF;
    SET @NewTempProvTimesheetHistoryID = "uniquekey"(1);
    INSERT INTO "TempProvTimesheetHistory"( "TempProvTimesheetHistoryID","TempProvTimeSheetID","SerialNumber","OldStatus","NewStatus","Description",
      "StaffID","ExternalUserID" ) VALUES
      ( @NewTempProvTimesheetHistoryID,@TempProvTimeSheetID,@SerialNumber,@OldStatus,@NewStatus,@Description,
      @StaffID,@ExternalUserID ) ;
    IF @@rowcount <> 1 THEN
      ROLLBACK WORK;
      RETURN('3:Error recording TempProvTimesheet history')
    END IF
  END;
  RETURN('0:TempProvTimesheet Status updated')
exception
  WHEN others THEN RETURN('1:Unknown error updating TempProvTimesheet Status')
END