Problema: Cuando se hace una decolucion de un expedientes en AptusME el sistema asigna asignado el valor -1 al Status y el expediente ya ni se muestra en el sistema AptusME, el cual debe ser rastreado en la Base de datos y ejecutarse el sisguiente procemiento almacenado para evitar que esto suceda.
/****** Object: StoredProcedure [dbo].[sp_InsUpd_Rendered] Script Date: 13/11/2019 16:32:11 ******/
DROP PROCEDURE [dbo].[sp_InsUpd_Rendered]
GO
CREATE PROCEDURE [dbo].[sp_InsUpd_Rendered] (
@recid int,
@Expedient int,
@userk int,
@lent_date varchar(10),
@date_return varchar(10),
@real_date_return varchar(10),
@comment varchar(8000),
@user_it_rendered varchar(10),
@user_it_received varchar(10),
@New_Status int
)
AS
DECLARE @myerror int
DECLARE @NewRecId int
-- Modificado por FFR en Nov 13, 2019
-- Para prevenir el estado invalido -1
IF @New_Status = -1 BEGIN
SET @New_Status = 0
END
BEGIN TRANSACTION
SET DATEFORMAT dmy
If @real_date_return = '' Begin
Set @real_date_return = Null
end
IF @recid = 0
BEGIN
INSERT INTO rendered(Expedient, userk, lent_date, date_return, real_date_return, comment,
user_it_rendered, user_it_received, Returned)
VALUES(@Expedient, @userk, @lent_date, @date_return,
@real_date_return, @comment, @user_it_rendered, @user_it_received, @New_Status)
SET @NewRecId = @@IDENTITY * -1
SELECT @myerror = @@ERROR
IF @myerror <> 0 BEGIN
ROLLBACK TRAN
return @myerror
END
UPDATE Expedients SET Status = 3 WHERE recid = @Expedient
UPDATE Expedients SET Lending = @NewRecId * -1 WHERE recid = @Expedient
SELECT @myerror = @@ERROR
IF @myerror <> 0 BEGIN
ROLLBACK TRAN
return @myerror
END
END
ELSE
BEGIN
UPDATE rendered SET
Expedient = @Expedient,
userk = @userk,
lent_date = @lent_date,
date_return = @date_return,
real_date_return = @real_date_return,
comment = @comment,
user_it_rendered = @user_it_rendered,
user_it_received = @user_it_received,
Returned = -1
where recid = @recid
SET @NewRecId = 0
SELECT @myerror = @@ERROR
IF @myerror <> 0 BEGIN
ROLLBACK TRAN
return @myerror
END
IF @real_date_return <> '' BEGIN
UPDATE Expedients SET Status = @New_Status
WHERE recid = @Expedient
END
SELECT @myerror = @@ERROR
IF @myerror <> 0 BEGIN
ROLLBACK TRAN
return @myerror
END
END
COMMIT TRANSACTION
return @NewRecId
GO
/*
sp_InsUpd_Rendered 2168, 10, 100, '13/11/2019', '14/11/2019', '13/11/2019', 'Prueba de prestamo de Felipe Faccinetto. Y ahora lo devuelvo para continuar con las pruebas', '2', '2', 1
select * from Expedients where status = -1
select * from rendered where recid = 2168
select * from Expedients where recid = 10
*/