IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'[dbo].[CP_Cbtes_Proyecto_Actualizar]') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE [dbo].[CP_Cbtes_Proyecto_Actualizar] 
END
GO


CREATE PROCEDURE [dbo].[CP_Cbtes_Proyecto_Actualizar]
@pId_Trans			INT,			/*Cbtes*/
@pEstado			CHAR(1),		/*A: Aprobado | R: Rechazado | C: Completo*/
@pObservacion		VARCHAR(8000)
AS

DECLARE @Trans TABLE (ErrorNumber	INT 
					,ErrorSeverity  INT 
					,ErrorState		INT
					,ErrorProcedure	VARCHAR(100)
					,ErrorLine		INT
					,ErrorMessage	VARCHAR(1000)
					,Id_Trans		INT)

/*Declaracion Variables Locales*/
DECLARE @Flag_Existe_Trans		TINYINT
DECLARE @Flag_Items_Completos	TINYINT
DECLARE @Id_Estado_Cbte			SMALLINT
DECLARE @Id_Estado_Cbte_Actual	SMALLINT

DECLARE @Mensaje				VARCHAR(100)
/*Fin de Declaraciones Variables Locales */

PRINT 'Procedimiento: CP_Cbtes_Proyecto_Actualizar'

BEGIN TRY
	BEGIN TRAN

	SET @Flag_Existe_Trans = ISNULL((SELECT 1 FROM Cbtes WHERE Id_Trans = @pId_Trans), 0)
	IF (@Flag_Existe_Trans = 0)
	BEGIN
		SET @Mensaje = 'No existe la Transaccion'

		SELECT	2001			AS ErrorNumber 
				,0				AS ErrorSeverity  
				,0				AS ErrorState 
				,0				AS ErrorProcedure  
				,0				AS ErrorLine		
				,@Mensaje		AS ErrorMessage
				,@pId_Trans		AS Id_Trans
		ROLLBACK
		RETURN
	END

	/*************************************************************************************************/
	
	IF(@pEstado NOT IN ('A'/*Aprobado*/, 'R' /*Rechazado*/, 'C' /*Completo*/))
	BEGIN
		SET @Mensaje = 'No es un estado valido'
		
		SELECT	2002			AS ErrorNumber 
				,0				AS ErrorSeverity  
				,0				AS ErrorState 
				,0				AS ErrorProcedure  
				,0				AS ErrorLine		
				,@Mensaje		AS ErrorMessage
				,@pId_Trans		AS Id_Trans
		ROLLBACK
		RETURN
	END
	
	/*************************************************************************************************/
	
	SET @Id_Estado_Cbte_Actual = (SELECT ISNULL(Id_Estado_Cbte, 0) FROM Cbtes WHERE Id_Trans = @pId_Trans)

	IF(@pEstado = 'A'/*Aprobado*/ OR @pEstado = 'R'/*Rechazado*/)
	BEGIN
		IF( @Id_Estado_Cbte_Actual IN ( dbo.CF_xParam('Proy_Estado_Aprob'), dbo.CF_xParam('Proy_Estado_Recha'), dbo.CF_xParam('Proy_Estado_Comple') ) )
		BEGIN
			SET @Mensaje = 'El Proyecto ya se encuentraba Aprobado, Rechazado o Completo.'

			SELECT	3002			AS ErrorNumber 
					,0				AS ErrorSeverity  
					,0				AS ErrorState 
					,0				AS ErrorProcedure  
					,0				AS ErrorLine		
					,@Mensaje		AS ErrorMessage
					,@pId_Trans		AS Id_Trans
			ROLLBACK
			RETURN
		END
	END
	
	/*************************************************************************************************/
	
	IF(@pEstado = 'A'/*Aprobado*/)
	BEGIN
		/*Solo puede aprobar el Proyecto (Cbtes) si todas las tareas (Cbtes_Items) tienen Flag_QA = 'A'*/
		SET @Flag_Items_Completos = ISNULL((SELECT MAX(1) FROM Cbtes_Items WHERE Id_Trans = @pId_Trans AND Flag_QA <> 'A'/*Aprobada*/), 0)
		IF (@Flag_Items_Completos = 1)
		BEGIN
			SET @Mensaje = 'Al menos un item (Tarea) no esta aprobada'

			SELECT	3001			AS ErrorNumber 
					,0				AS ErrorSeverity  
					,0				AS ErrorState 
					,0				AS ErrorProcedure  
					,0				AS ErrorLine		
					,@Mensaje		AS ErrorMessage
					,@pId_Trans		AS Id_Trans
			ROLLBACK
			RETURN
		END
	END

	IF(@pEstado = 'C'/*Completo*/)
	BEGIN
		/* Solo se puede completar si esta en estado aprobado */
		IF(@Id_Estado_Cbte_Actual <> dbo.CF_xParam('Proy_Estado_Aprob'))
		BEGIN
			SET @Mensaje = 'El requerimiento debe estar aprobado.'

			SELECT	3003			AS ErrorNumber 
					,0				AS ErrorSeverity  
					,0				AS ErrorState 
					,0				AS ErrorProcedure  
					,0				AS ErrorLine		
					,@Mensaje		AS ErrorMessage
					,@pId_Trans		AS Id_Trans
			ROLLBACK
			RETURN
		END
	END
	
	/*************************************************************************************************/
	
	IF(@pEstado = 'A')
	BEGIN
		SET @Id_Estado_Cbte = (dbo.CF_xParam('Proy_Estado_Aprob'))
	END
	
	IF(@pEstado = 'R')
	BEGIN
		SET @Id_Estado_Cbte = (dbo.CF_xParam('Proy_Estado_Recha'))
	END
	
	IF(@pEstado = 'C')
	BEGIN
		SET @Id_Estado_Cbte = (dbo.CF_xParam('Proy_Estado_Comple'))
	END
	
	/*************************************************************************************************/
	
	UPDATE Cbtes SET Id_Estado_Cbte = @Id_Estado_Cbte 
	WHERE Id_Trans = @pId_Trans

	IF (@pObservacion <> '')
	BEGIN
		UPDATE Cbtes SET Observacion = CONVERT(VARCHAR(MAX), Observacion) + CHAR(13) + @pObservacion
		WHERE Id_Trans = @pId_Trans
	END

	/************************************************************************************************/
	
	COMMIT

	SET @Mensaje = 'Estado de Transaccion Actualizado'

	SELECT	 0				AS ErrorNumber 
			,0				AS ErrorSeverity  
			,0				AS ErrorState 
			,0				AS ErrorProcedure  
			,0				AS ErrorLine		
			,@Mensaje		AS ErrorMessage
			,@pId_Trans		AS Id_Trans

END TRY  


BEGIN CATCH  
    EXECUTE usp_GetErrorInfo;  
	ROLLBACK
END CATCH

PRINT 'FIN Procedimiento: CP_Cbtes_Proyecto_Actualizar'
