DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

ALTER PROC [dbo].[CM_REJECT_LOG_ByType_Ins]

@p_LOG_ID	varchar(15)	= NULL,

@p_STAGE	varchar(5)	 = NULL,

@p_TRN_ID	varchar(15)	 = NULL,

@p_TRN_TYPE	nvarchar(100)	 = NULL,

@p_LOG_DT	VARCHAR	 = NULL,

@p_AUTH_STAT	varchar(3)	 = NULL,

@p_REASON	nvarchar(500)	 = NULL,

@p_IS_LATEST	varchar(1)	 = NULL,

@p_REJECTED_BY	varchar(20)	 = NULL,

@p_REJECTED_DT	VARCHAR	 = NULL,

@p_TYPE VARCHAR(20) =NULL,

@p_PROCESS_ID VARCHAR(20),

@p_REF_ID INT=NULL

AS

BEGIN TRANSACTION

DECLARE @PARENT_PROCESS_ID VARCHAR(20),@ROLE_ID VARCHAR(20),@ROLE_DES NVARCHAR(200),@ROLE_PARENT_ID VARCHAR(20),@ROLE_DES_PARENT NVARCHAR(200)

		DECLARE @p_MESSAGE NVARCHAR(100) = NULL

		IF(EXISTS (SELECT * FROM CM_REJECT_LOG WHERE TRN_ID = @p_TRN_ID))

		BEGIN

			UPDATE CM_REJECT_LOG SET IS_LATEST ='N' WHERE TRN_ID =@p_TRN_ID

		END

		-- END PHIEU DE THI THANH TOAN 19-11-2019

			--- PHIEU YEU CAU MUA SAM

				IF(@p_TRN_TYPE='PL_REQUEST_DOC')

				BEGIN	IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_TRN_ID AND PROCESS_ID='APPROVE'))

				BEGIN

					SET @p_MESSAGE =N'Trả về thất bại! Tờ trình chủ trương đã được duyệt'

					ROLLBACK TRANSACTION

					SELECT '0' as Result, @p_LOG_ID  LOG_ID ,@p_MESSAGE ErrorDesc

					RETURN '0'

				END

				ELSE

				BEGIN

				IF(@p_TYPE='CREATE')

				BEGIN

				UPDATE dbo.PL_REQUEST_DOC SET PROCESS_ID='REJECT',AUTH_STATUS='R' WHERE REQ_ID=@p_TRN_ID

				UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='R',NOTES=N'Tờ trình bị trả về' WHERE PROCESS_ID='APPNEW' AND REQ_ID=@p_TRN_ID

				UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='U' WHERE PROCESS_ID <>'APPNEW' AND REQ_ID=@p_TRN_ID

				INSERT INTO dbo.PL_PROCESS

				(

				REQ_ID,

				PROCESS_ID,

				CHECKER_ID,

			    APPROVE_DT,

			    PROCESS_DESC,

				NOTES

				)

				VALUES

				(   @p_TRN_ID,        -- REQ_ID - varchar(15)

					'REJECT',        -- PROCESS_ID - varchar(10)

					@p_REJECTED_BY,        -- CHECKER_ID - varchar(15)

					GETDATE(), -- APPROVE_DT - datetime

					@p_REASON,

					N'Trả về người tạo'

					       -- PROCESS_DESC - nvarchar(1000)

				)

				END

				ELSE IF(@p_TYPE='NVXL')

				BEGIN

				UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='U' WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID 

				UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='R' WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID AND TYPE_JOB='XL'			

				INSERT INTO dbo.PL_PROCESS

				(

				REQ_ID,

				PROCESS_ID,

				CHECKER_ID,

			    APPROVE_DT,

			    PROCESS_DESC,

				NOTES

				)

				VALUES

				(   @p_TRN_ID,        -- REQ_ID - varchar(15)

					'REJECT',        -- PROCESS_ID - varchar(10)

					@p_REJECTED_BY,        -- CHECKER_ID - varchar(15)

					GETDATE(), -- APPROVE_DT - datetime

					@p_REASON,

					N'Trả về nhân viên xử lý'

					       -- PROCESS_DESC - nvarchar(1000)

				)

				END

				ELSE IF(@p_TYPE='PRE_APP')

				BEGIN

					SET @PARENT_PROCESS_ID =(SELECT TOP 1 PARENT_PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_TRN_ID)

					SET @ROLE_ID=(SELECT TOP 1 ROLE_USER FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_TRN_ID)

					SET @ROLE_PARENT_ID=(SELECT TOP 1 ROLE_USER FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@PARENT_PROCESS_ID AND REQ_ID=@p_TRN_ID)

					SET @ROLE_DES=(SELECT ROLE_DESC FROM dbo.TL_SYSROLE WHERE ROLE_ID=@ROLE_ID)

					SET @ROLE_DES_PARENT=(SELECT ROLE_DESC FROM dbo.TL_SYSROLE WHERE ROLE_ID=@ROLE_PARENT_ID)

					UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='R',NOTES=@ROLE_DES + N' đã trả về, chờ ' + @ROLE_DES_PARENT +N' xử lý' WHERE PROCESS_ID=@PARENT_PROCESS_ID AND REQ_ID=@p_TRN_ID

					UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='U' WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_TRN_ID

					UPDATE dbo.PL_REQUEST_DOC SET PROCESS_ID=@PARENT_PROCESS_ID WHERE REQ_ID=@p_TRN_ID

					IF(@PARENT_PROCESS_ID='APPNEW')

						UPDATE dbo.PL_REQUEST_DOC SET AUTH_STATUS='U' WHERE REQ_ID=@p_TRN_ID

					INSERT INTO dbo.PL_PROCESS

					(

					REQ_ID,

					PROCESS_ID,

					CHECKER_ID,

					APPROVE_DT,

					PROCESS_DESC,

					NOTES

					)

					VALUES

					(   @p_TRN_ID,        -- REQ_ID - varchar(15)

						'REJECT',        -- PROCESS_ID - varchar(10)

						@p_REJECTED_BY,        -- CHECKER_ID - varchar(15)

						GETDATE(), -- APPROVE_DT - datetime

						@p_REASON,N'Trả về ' + @ROLE_DES_PARENT

						       -- PROCESS_DESC - nvarchar(1000)

					)

				END

				 SET @p_MESSAGE =N'Phiếu yêu cầu đã được trả về thành công'

				 END		

			END

			ELSE IF(@p_TRN_TYPE='TR_REQUEST_DOC')

			BEGIN

			--IF(@p_STAGE='DVMS')

			--BEGIN

			--END

				IF(EXISTS(SELECT * FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_TRN_ID AND PROCESS_ID='APPROVE'))

				BEGIN

					SET @p_MESSAGE =N'Trả về thất bại! Phiếu yêu cầu đã được duyệt'

					ROLLBACK TRANSACTION

					SELECT '0' as Result, @p_LOG_ID  LOG_ID ,@p_MESSAGE ErrorDesc

					RETURN '0'

				END

				ELSE

				BEGIN

				IF(@p_TYPE='CREATE')

				BEGIN

				UPDATE dbo.TR_REQUEST_DOC SET PROCESS_ID='REJECT',AUTH_STATUS='R' WHERE REQ_ID=@p_TRN_ID

				UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='R',NOTES=N'Tờ trình bị trả về' WHERE PROCESS_ID='APPNEW' AND REQ_ID=@p_TRN_ID

				UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='U' WHERE PROCESS_ID <>'APPNEW' AND REQ_ID=@p_TRN_ID

				INSERT INTO dbo.PL_PROCESS

				(

				REQ_ID,

				PROCESS_ID,

				CHECKER_ID,

			    APPROVE_DT,

			    PROCESS_DESC,

				NOTES

				)

				VALUES

				(   @p_TRN_ID,        -- REQ_ID - varchar(15)

					'REJECT',        -- PROCESS_ID - varchar(10)

					@p_REJECTED_BY,        -- CHECKER_ID - varchar(15)

					GETDATE(), -- APPROVE_DT - datetime

					@p_REASON,N'Trả về người tạo'

					       -- PROCESS_DESC - nvarchar(1000)

				)

				END

				ELSE IF(@p_TYPE='NVXL')

				BEGIN

				UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='U' WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID 

				UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='R' WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID AND TYPE_JOB='XL'		

				INSERT INTO dbo.PL_PROCESS

				(

				REQ_ID,

				PROCESS_ID,

				CHECKER_ID,

			    APPROVE_DT,

			    PROCESS_DESC,

				NOTES

				)

				VALUES

				(   @p_TRN_ID,        -- REQ_ID - varchar(15)

					'REJECT',        -- PROCESS_ID - varchar(10)

					@p_REJECTED_BY,        -- CHECKER_ID - varchar(15)

					GETDATE(), -- APPROVE_DT - datetime

					@p_REASON,N'Trả về nhân viên xử lý'

					       -- PROCESS_DESC - nvarchar(1000)

				)

				END

				ELSE IF(@p_TYPE='PRE_APP')

				BEGIN

					SET @PARENT_PROCESS_ID =(SELECT TOP 1 PARENT_PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_TRN_ID)

					SET @ROLE_ID=(SELECT TOP 1 ROLE_USER FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_TRN_ID)

					SET @ROLE_PARENT_ID=(SELECT TOP 1 ROLE_USER FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@PARENT_PROCESS_ID AND REQ_ID=@p_TRN_ID)

					SET @ROLE_DES=(SELECT ROLE_DESC FROM dbo.TL_SYSROLE WHERE ROLE_ID=@ROLE_ID)

					SET @ROLE_DES_PARENT=(SELECT ROLE_DESC FROM dbo.TL_SYSROLE WHERE ROLE_ID=@ROLE_PARENT_ID)

					UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='R',NOTES=@ROLE_DES + N' đã trả về, chờ ' + @ROLE_DES_PARENT +N' xử lý' WHERE PROCESS_ID=@PARENT_PROCESS_ID AND REQ_ID=@p_TRN_ID

					IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_TRN_ID AND PROCESS_ID=@PARENT_PROCESS_ID AND IS_HAS_CHILD=1))

					BEGIN

						UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='R' WHERE REQ_ID=@p_TRN_ID AND PROCESS_ID IN (SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_TRN_ID AND PROCESS_ID=@PARENT_PROCESS_ID AND IS_HAS_CHILD=1) AND LEVEL_JOB=1

					END

					IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE  REQ_ID =@p_TRN_ID AND PROCESS_ID='DVCM'))

					BEGIN

							UPDATE dbo.TR_REQUEST_COSTCENTER SET AUTH_STATUS='R' WHERE REQ_ID=@p_TRN_ID

					END

					UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='U' WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_TRN_ID

					UPDATE dbo.TR_REQUEST_DOC SET PROCESS_ID=@PARENT_PROCESS_ID WHERE REQ_ID=@p_TRN_ID

					IF(@PARENT_PROCESS_ID='APPNEW')

						UPDATE dbo.TR_REQUEST_DOC SET AUTH_STATUS='U' WHERE REQ_ID=@p_TRN_ID

					INSERT INTO dbo.PL_PROCESS

					(

					REQ_ID,

					PROCESS_ID,

					CHECKER_ID,

					APPROVE_DT,

					PROCESS_DESC,NOTES

					)

					VALUES

					(   @p_TRN_ID,        -- REQ_ID - varchar(15)

						'REJECT',        -- PROCESS_ID - varchar(10)

						@p_REJECTED_BY,        -- CHECKER_ID - varchar(15)

						GETDATE(), -- APPROVE_DT - datetime

						@p_REASON,N'Trả về ' + @ROLE_DES_PARENT

						       -- PROCESS_DESC - nvarchar(1000)

					)

				END

				 SET @p_MESSAGE =N'Phiếu yêu cầu đã được trả về thành công'

				END	

		END

		-- END IF

		ELSE

		BEGIN

			SET @p_MESSAGE =N'Trả về giao dịch thành công'

		END

		--- INSERT VAO BANG CM_REJECT_LOG

		EXEC SYS_CodeMasters_Gen 'CM_REJECT_LOG', @p_LOG_ID out

		IF @p_LOG_ID='' OR @p_LOG_ID IS NULL GOTO ABORT

		INSERT INTO [dbo].[CM_REJECT_LOG] VALUES (@p_LOG_ID,@p_STAGE,@p_TRN_ID,@p_TRN_TYPE,CONVERT(DATETIME,GETDATE(),103),'R',@p_REASON,@p_IS_LATEST,@p_REJECTED_BY,CONVERT(DATETIME,GETDATE(),103))

		IF @@Error <> 0 GOTO ABORT

COMMIT TRANSACTION

SELECT '0' as Result, @p_LOG_ID  LOG_ID ,@p_MESSAGE ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' LOG_ID, '' ErrorDesc

		RETURN '-1'

END