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