ALTER PROCEDURE dbo.TR_REQUEST_SHOP_PROCESS_Update
@p_REQ_ID varchar(15) = NULL,
@p_TYPE_PROCESS varchar(15) = NULL,
@p_MAKERID varchar(100)= NULL,
@p_AUTH_STATUS VARCHAR(1) = NULL,
@p_CHECKER_ID VARCHAR(100)  = NULL,
@p_APPROVE_DT VARCHAR(50) = NULL,
@p_USERNAME varchar(100)  = NULL,
@p_CURRENT_URI NVARCHAR(200) = NULL,
@p_NOTES NVARCHAR(500)= NULL,
@p_DVKD_USER_APP NVARCHAR(500)= NULL

AS
BEGIN

DECLARE 
@NOTIFATION NVARCHAR(100) = NULL,
@NOTIFY_TO_USER NVARCHAR(100) = NULL,
@EMAIL_CONTENT NVARCHAR(500) = NULL,
@TYPE VARCHAR(10),
@ROLE VARCHAR(20)= NULL,
@BRANCHID VARCHAR(20)= NULL,
@PROCESS_CURR VARCHAR(20)= NULL,
@PROCESS_NEXT VARCHAR(20)= NULL,
@BRANCH_NEXT VARCHAR(20)=NULL,
@DEP_NEXT VARCHAR(20) = NULL,
@DEP_ID VARCHAR(20)


IF(@p_NOTES = 'QLTSconfirmandSendApprove') SET @p_NOTES = N'Bộ phận QLTS gửi đến Bộ phận ĐVCM tham vấn'

SELECT @BRANCHID=TLSUBBRID ,@DEP_ID=DEP_ID FROM dbo.TL_USER WHERE TLNANME=@p_USERNAME

DECLARE @lstCOST TABLE (
	COST_ID VARCHAR(20)
)

DECLARE @usersToNotify TABLE (
	TLNANME VARCHAR(100)
)

INSERT INTO @lstCOST
SELECT COST_ID FROM dbo.TR_REQUEST_SHOP_COSTCENTER TRSC WHERE TRSC.REQ_ID = @p_REQ_ID
	
DECLARE @p_LOG_ID VARCHAR(20),@COMPLETE BIT,@PROCESS_PARENT VARCHAR(20),@PROCESS_DES NVARCHAR(500)

DECLARE  @ROLE_LOGIN TABLE(ROLE_USER VARCHAR(20), BRANCH_ID VARCHAR(20), DEP_ID VARCHAR(20))
INSERT INTO @ROLE_LOGIN
SELECT tugr.ROLE_ID, tugr.BRANCH_ID, tugr.DEPT_ID FROM dbo.TL_USER_GET_ROLES(@p_USERNAME) tugr
SET @PROCESS_CURR=(SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS
WHERE REQ_ID=@p_REQ_ID AND STATUS='C'
AND EXISTS(SELECT * FROM PL_REQUEST_PROCESS A WHERE A.ID = PL_REQUEST_PROCESS.ID AND A.REQ_ID = @p_REQ_ID AND A.STATUS = 'C'
  AND EXISTS(SELECT RL.ROLE_USER FROM @ROLE_LOGIN RL WHERE RL.BRANCH_ID = A.BRANCH_ID
    AND (A.BRANCH_ID = 'DV0001' AND A.DEP_ID = RL.DEP_ID OR A.BRANCH_ID <> 'DV0001')
    AND RL.ROLE_USER = A.ROLE_USER)))
SET @COMPLETE=0

DECLARE @DEP_QLTS VARCHAR(20), @DEP_DVNB VARCHAR(20), @DEP_HTKD VARCHAR(20)
SET @DEP_QLTS = (SELECT TOP 1 sp.ParaValue FROM SYS_PARAMETERS sp WHERE sp.ParaKey = 'DEP_QLTS')
SET @DEP_DVNB = (SELECT TOP 1 sp.ParaValue FROM SYS_PARAMETERS sp WHERE sp.ParaKey = 'DEP_TTQLTS')
SET @DEP_HTKD = (SELECT TOP 1 sp.ParaValue FROM SYS_PARAMETERS sp WHERE sp.ParaKey = 'DEP_KQLTS')

BEGIN TRANSACTION

	-- GỬI DVKD
	IF(@p_TYPE_PROCESS='S_TDV')
	BEGIN
    --check tài sản thay thế ở phiếu đã duyệt
    BEGIN
    IF(EXISTS(SELECT * FROM TR_REQUEST_SHOP_DOC_DT TRSDD WHERE TRSDD.REQ_DOC_ID = @p_REQ_ID AND REQ_DT_TYPE = 'ASSET_BROKEN' AND TRSDD.ASS_ID IS NOT NULL OR TRSDD.ASS_ID <> ''))
          BEGIN
              DECLARE @MESSAGE_VALIDATION NVARCHAR(MAX)
    
              DECLARE @TABLE_ASSCODE_VALIDATION TABLE (ASSET_CODE VARCHAR(100), REQ_CODE VARCHAR(100))
              INSERT INTO @TABLE_ASSCODE_VALIDATION
              SELECT ISNULL(C.ASSET_CODE,C.ASS_CODE_TMP),B.REQ_CODE
              FROM TR_REQUEST_SHOP_DOC_DT A
              LEFT JOIN TR_REQUEST_SHOP_DOC B ON A.REQ_DOC_ID = B.REQ_ID
              LEFT JOIN ASS_MASTER C ON A.ASS_ID = C.ASSET_ID
              WHERE A.ASS_ID IN (SELECT C.ASS_ID FROM TR_REQUEST_SHOP_DOC_DT C WHERE C.REQ_DOC_ID = @p_REQ_ID AND C.REQ_DT_TYPE = 'ASSET_BROKEN')
              --AND B.IS_DONE = '0'
              AND B.REQ_TYPE = 'CPTS'
              AND B.AUTH_STATUS <> 'E'
              AND A.REQ_DOC_ID <> @p_REQ_ID
             
              IF(EXISTS(SELECT 1 FROM @TABLE_ASSCODE_VALIDATION))
              BEGIN
                  SET @MESSAGE_VALIDATION = (SELECT REPLACE((SELECT 
                                                                    CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT 0)) = 1 THEN ''
                                                                    ELSE '|' END 
                                                                    + N'Tài sản ' + C.ASSET_CODE + N' đã được chọn trong tài sản yêu cầu thay thế ở phiếu số ' + C.REQ_CODE
                                                              FROM (SELECT DISTINCT ISNULL(C.ASSET_CODE,C.ASS_CODE_TMP) AS ASSET_CODE,B.REQ_CODE
                                                                    FROM TR_REQUEST_SHOP_DOC_DT A
                                                                    LEFT JOIN TR_REQUEST_SHOP_DOC B ON A.REQ_DOC_ID = B.REQ_ID
                                                                    LEFT JOIN ASS_MASTER C ON A.ASS_ID = C.ASSET_ID
                                                                    WHERE A.ASS_ID IN (SELECT C.ASS_ID FROM TR_REQUEST_SHOP_DOC_DT C WHERE C.REQ_DOC_ID = @p_REQ_ID AND C.REQ_DT_TYPE = 'ASSET_BROKEN')
                                                                    --AND B.IS_DONE = '0'
                                                                    AND B.REQ_TYPE = 'CPTS'
                                                                    AND B.AUTH_STATUS <> 'E'
                                                                    AND A.REQ_DOC_ID <> @p_REQ_ID) C
                                                              FOR XML PATH (''))
                                                            , '|', '<br />'))
              ROLLBACK TRANSACTION
              SELECT '-1' as Result, @MESSAGE_VALIDATION ErrorDesc
              RETURN '-1'
              END
          END
        END
		-- CẬP NHẬT TRẠNG THÁI CHO REQUEST_PROCESS

		--IF(@PROCESS_CURR IS NULL OR @PROCESS_CURR='')
		BEGIN	
			UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='C', DVKD_USER_APP = @p_DVKD_USER_APP, RECEPTION_DT = GETDATE() WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='APPNEW'
			UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='P',CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=CONVERT(DATETIME, @p_APPROVE_DT, 103), RECEPTION_DT = GETDATE() WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='ADDNEW'

            INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID, DEP_ID, DVKD_USER_APP, PROCESS_TYPE)
		    VALUES(@p_REQ_ID,'QLTS_N','U','QLTS','DV0001','APPNEW', @DEP_QLTS, @p_DVKD_USER_APP, 'Update')
			IF @@Error <> 0 GOTO ABORT

			UPDATE TR_REQUEST_SHOP_DOC SET [STATUS]='APPNEW', AUTH_STATUS = 'U' WHERE REQ_ID=@p_REQ_ID
			
			INSERT INTO @usersToNotify
			SELECT DISTINCT U.TLNANME FROM TL_USER u
			where u.TLNANME= @p_DVKD_USER_APP  
			SET @p_CURRENT_URI = REPLACE(@p_CURRENT_URI, 'add', 'edit') + ';id=' + @p_REQ_ID

			IF @@Error <> 0 GOTO ABORT
			IF(EXISTS(SELECT TLNANME FROM @usersToNotify))
				SET @COMPLETE=1
			ELSE
				SET @COMPLETE=0
			SET @PROCESS_CURR = 'APPNEW'

		END
		SET @COMPLETE=1
		SET @NOTIFATION= N'Gửi trưởng đơn vị thành công'
		SET @PROCESS_DES=N'NEW'
		SET @NOTIFY_TO_USER = 'DVKD_APP'
		SET @EMAIL_CONTENT = 'TYPE_PROCESS_REQ_ADD_NEW';
    	SET @PROCESS_DES=N'Nhân viên tạo phiếu và gửi đến Trưởng đơn vị phê duyệt'
	END

	-- GỬI HỘI SỞ
	ELSE IF(@p_TYPE_PROCESS='S_QLTS')
	BEGIN
		IF(EXISTS(SELECT * FROM TR_REQUEST_SHOP_DOC WHERE REQ_ID = @p_REQ_ID AND STATUS = 'CANCEL'))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' WH_O_HCQT_MUL_ID, N'Phiếu yêu cầu đã bị hủy' ErrorDesc
			RETURN '-1'
		END
		-- CẬP NHẬT TRẠNG THÁI CHO REQUEST_PROCESS
		--SET @PROCESS_PARENT=(SELECT PARENT_PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='QLTS_N')

		--IF(@PROCESS_CURR=@PROCESS_PARENT)
		BEGIN

			UPDATE PL_REQUEST_PROCESS SET [STATUS]='P',CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=CONVERT(DATETIME, @p_APPROVE_DT, 103) 
            WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='APPNEW' 
            --AND (ROLE_USER IN (SELECT ROLE_ID FROM dbo.TL_USER_GET_ROLES(@p_USERNAME)))

			IF @@Error <> 0 GOTO ABORT

			UPDATE PL_REQUEST_PROCESS SET [STATUS]='C', RECEPTION_DT = CONVERT(DATETIME, @p_APPROVE_DT, 103), NOTES = @p_NOTES
            WHERE PROCESS_ID = 'QLTS_N' AND REQ_ID=@p_REQ_ID

			SELECT @PROCESS_NEXT=PROCESS_ID,@ROLE=ROLE_USER,@BRANCH_NEXT=BRANCH_ID,@DEP_NEXT=DEP_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C'	

			UPDATE TR_REQUEST_SHOP_DOC SET [STATUS]='QLTS_N', CHECKER_ID = @p_CHECKER_ID,APPROVE_DT=CONVERT(DATETIME, @p_APPROVE_DT, 103), SEND_APP_DT = CONVERT(DATETIME, @p_APPROVE_DT, 103)  WHERE REQ_ID=@p_REQ_ID
			
		
			IF @@Error <> 0 GOTO ABORT
			
			SET @COMPLETE=1
		END
		SET @NOTIFATION= N'Phiếu đã được chuyển đến bộ phận QLTS - HO'
		SET @PROCESS_DES=N'Gửi phòng hành'
		SET @NOTIFY_TO_USER = 'UD_REQ_S_HCQT'
		SET @EMAIL_CONTENT = 'TYPE_PROCESS_REQ_S_HCQT';
    	SET @PROCESS_DES=N'Trưởng đơn vị phê duyệt và gửi đến bộ phận QLTS'
	END
    
	ELSE IF(@p_TYPE_PROCESS='S_DVCM')
	BEGIN

		SET @PROCESS_PARENT=(SELECT TOP (1) PARENT_PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DVCM')
		-- CẬP NHẬT TRẠNG THÁI CHO REQUEST_PROCESS
  	UPDATE PL_REQUEST_PROCESS SET [STATUS]='P',CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=CONVERT(DATETIME, @p_APPROVE_DT, 103)
    WHERE REQ_ID=@p_REQ_ID AND [STATUS]='C' AND PROCESS_ID='QLTS_N' 
      IF((SELECT COUNT(*) FROM TR_REQUEST_SHOP_COSTCENTER TRSC WHERE TRSC.REQ_ID = @p_REQ_ID) = 0)
      BEGIN
      	INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, PROCESS_TYPE)
  		  VALUES(@p_REQ_ID,'QLTS_NL','C','QLTS','DV0001','QLTS_N',@DEP_QLTS, 'Update')

        INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID,PROCESS_TYPE)
        VALUES(@p_REQ_ID,'TBP_D','U','TBP_QLTS','DV0001','QLTS_NL',@DEP_QLTS, 'Approve')
        -- DUYỆT
        INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID,PROCESS_TYPE)
        VALUES(@p_REQ_ID,'QLTS_D','U','GDDV_QLTS','DV0001','TBP_D',@DEP_QLTS, 'Approve')
        INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],PARENT_PROCESS_ID)
        VALUES(@p_REQ_ID,'APPROVE','U','QLTS_D')
        -- HOÀN TẤT
        INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],PARENT_PROCESS_ID)
        VALUES(@p_REQ_ID,'DONE','U','APPROVE')

        UPDATE TR_REQUEST_SHOP_DOC SET [STATUS]='QLTS_NL' , SEND_APP_DT = CONVERT(DATETIME, @p_APPROVE_DT, 103) WHERE REQ_ID=@p_REQ_ID
                UPDATE TR_REQUEST_SHOP_DOC SET HO_NOTES = N'- Kế hoạch (Trong/Ngoài NS): ' + CHAR(10) + N'- Nội dung cấp: ' + CHAR(10) +  N'- Ghi chú khác: ' WHERE REQ_ID=@p_REQ_ID

        INSERT INTO @usersToNotify
  			SELECT DISTINCT U.TLNANME FROM TL_USER u
  			LEFT JOIN AbpUserRoles ur ON ur.UserId = u.ID
  			LEFT JOIN AbpRoles r ON ur.RoleId = r.Id
  			INNER JOIN
  			(
  				SELECT CC.COST_ID AS DEP_ID,CD.BRANCH_ID FROM dbo.PL_REQUEST_PROCESS PL 
  				LEFT JOIN dbo.TR_REQUEST_SHOP_COSTCENTER CC ON CC.COST_ID=pl.COST_ID
  				LEFT JOIN CM_DEPARTMENT CD ON CC.COST_ID = CD.DEP_ID
  				WHERE PL.REQ_ID=@p_REQ_ID AND PL.PROCESS_ID='DVCM'
  			) T ON T.DEP_ID=u.DEP_ID AND T.BRANCH_ID=u.TLSUBBRID
  			where r.DisplayName = @ROLE AND u.TLNANME != @p_USERNAME
  
  			IF @@Error <> 0 GOTO ABORT

  			SET @COMPLETE=1
            SET @p_NOTES = N'Bộ phận QLTS gửi đến Trưởng Bộ phận QLTS phê duyệt'
    		SET @NOTIFATION= N'Bộ phận QLTS đã tiếp nhận phiếu'
    		SET @NOTIFY_TO_USER = 'UD_REQ_S_APP'
    		SET @EMAIL_CONTENT = 'TYPE_PROCESS_REQ_S_APPP';
      	SET @PROCESS_DES=N'QLTS tiếp nhận và đang kiểm duyệt'
      END
      ELSE
      BEGIN
  	  --UPDATE PL_REQUEST_PROCESS SET [STATUS]='C', RECEPTION_DT = CONVERT(DATETIME, @p_APPROVE_DT, 103) WHERE PROCESS_ID = 'DVCM' AND REQ_ID=@p_REQ_ID
  
        SELECT TOP 1 @PROCESS_NEXT=PROCESS_ID,@ROLE=ROLE_USER FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C'	
        
        INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, PROCESS_TYPE)
        SELECT trsc.REQ_ID, 'DVCM', 'C', 'DVCM', 'DV0001', 'QLTS_N', trsc.COST_ID,'Update' FROM TR_REQUEST_SHOP_COSTCENTER trsc 
        WHERE trsc.REQ_ID = @p_REQ_ID

        INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID,PROCESS_TYPE)
        SELECT trsc.REQ_ID, 'DVCM_D', 'U', 'GDDV', 'DV0001', 'DVCM', trsc.COST_ID, 'Approve' FROM TR_REQUEST_SHOP_COSTCENTER trsc
        WHERE trsc.REQ_ID = @p_REQ_ID
        --
        INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID,PROCESS_TYPE)
        VALUES(@p_REQ_ID,'QLTS_NL','U','QLTS','DV0001','DVCM_D',@DEP_QLTS, 'Update')
        INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID,PROCESS_TYPE)
        VALUES(@p_REQ_ID,'TBP_D','U','TBP_QLTS','DV0001','QLTS_NL',@DEP_QLTS, 'Approve')
        INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID,PROCESS_TYPE)
        VALUES(@p_REQ_ID,'QLTS_D','U','GDDV_QLTS','DV0001','TBP_D',@DEP_QLTS, 'Approve')
        -- DUYỆT
        INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],PARENT_PROCESS_ID)
        VALUES(@p_REQ_ID,'APPROVE','U','QLTS_D')
        -- HOÀN TẤT
        INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],PARENT_PROCESS_ID)
        VALUES(@p_REQ_ID,'DONE','U','APPROVE')
 
  	    UPDATE TR_REQUEST_SHOP_DOC SET [STATUS]='DVCM' WHERE REQ_ID=@p_REQ_ID
  
  			INSERT INTO @usersToNotify
  			SELECT DISTINCT U.TLNANME FROM TL_USER u
  			LEFT JOIN AbpUserRoles ur ON ur.UserId = u.ID
  			LEFT JOIN AbpRoles r ON ur.RoleId = r.Id
  			INNER JOIN
  			(
  				SELECT CC.COST_ID AS DEP_ID,CD.BRANCH_ID FROM dbo.PL_REQUEST_PROCESS PL 
  				LEFT JOIN dbo.TR_REQUEST_SHOP_COSTCENTER CC ON CC.COST_ID=pl.COST_ID
  				LEFT JOIN CM_DEPARTMENT CD ON CC.COST_ID = CD.DEP_ID
  				WHERE PL.REQ_ID=@p_REQ_ID AND PL.PROCESS_ID='DVCM'
  			) T ON T.DEP_ID=u.DEP_ID AND T.BRANCH_ID=u.TLSUBBRID
  			where r.DisplayName = @ROLE AND u.TLNANME != @p_USERNAME
  
  			IF @@Error <> 0 GOTO ABORT
    			
  			SET @COMPLETE=1
        SET @NOTIFATION= N'Gửi ĐVCM thành công'
        SET @PROCESS_DES=N'Gửi ĐVCM'
        SET @NOTIFY_TO_USER = 'UD_REQ_S_DVCM'
        SET @EMAIL_CONTENT = 'TYPE_PROCESS_REQ_S_DVCM';
  	    SET @PROCESS_DES=N'QLTS tiếp nhận và gửi đến ĐVCM'
      END
	END
    ELSE IF(@p_TYPE_PROCESS='S_TDVCM')
	BEGIN
    	DECLARE @USERDVCM VARCHAR(15) = ''
        SELECT @USERDVCM = TU.SECUR_CODE FROM TL_USER TU WHERE TU.TLNANME = @p_CHECKER_ID
        IF(EXISTS(SELECT * FROM dbo.TR_REQUEST_SHOP_COSTCENTER TRSC WHERE REQ_ID = @p_REQ_ID AND TRSC.COST_ID = @USERDVCM AND (TRSC.RE_CONTENT IS NULL OR TRSC.RE_CONTENT = '')))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' WH_O_HCQT_MUL_ID, N'Chưa nhập ý kiến bộ phận Đơn vị chuyên môn' ErrorDesc
			RETURN '-1'
		END
        UPDATE PL_REQUEST_PROCESS SET STATUS = 'P', APPROVE_DT = GETDATE(), CHECKER_ID = @p_CHECKER_ID, NOTES = N'Đơn vị chuyên môn đã xác nhận'
            WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'DVCM' AND STATUS = 'C' AND DEP_ID = @USERDVCM
        UPDATE TR_REQUEST_SHOP_COSTCENTER SET AUTH_STATUS = 'U' WHERE REQ_ID = @p_REQ_ID AND COST_ID = @USERDVCM
        UPDATE PL_REQUEST_PROCESS SET STATUS = 'C' WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'DVCM_D' AND DEP_ID = @USERDVCM

--  			INSERT INTO @usersToNotify
--  			SELECT DISTINCT U.TLNANME FROM TL_USER u
--  			LEFT JOIN AbpUserRoles ur ON ur.UserId = u.ID
--  			LEFT JOIN AbpRoles r ON ur.RoleId = r.Id
--  			INNER JOIN
--  			(
--  				SELECT CC.COST_ID AS DEP_ID,CD.BRANCH_ID FROM dbo.PL_REQUEST_PROCESS PL 
--  				LEFT JOIN dbo.TR_REQUEST_SHOP_COSTCENTER CC ON CC.COST_ID=pl.COST_ID
--  				LEFT JOIN CM_DEPARTMENT CD ON CC.COST_ID = CD.DEP_ID
--  				WHERE PL.REQ_ID=@p_REQ_ID AND PL.PROCESS_ID='DVCM'
--  			) T ON T.DEP_ID=u.DEP_ID AND T.BRANCH_ID=u.TLSUBBRID
--  			where r.DisplayName = @ROLE AND u.TLNANME != @p_USERNAME
  
  			IF @@Error <> 0 GOTO ABORT
    	BEGIN		
      		SET @COMPLETE=1
            SET @NOTIFATION= N'Gửi Trưởng ĐVCM duyệt thành công'
            SET @PROCESS_DES=N'Gửi Trưởng ĐVCM duyệt'
            SET @NOTIFY_TO_USER = 'UD_REQ_S_DVCM'
            SET @EMAIL_CONTENT = 'TYPE_PROCESS_REQ_S_DVCM';
      END
	END
	ELSE IF(@p_TYPE_PROCESS='DVCM_D')
	BEGIN
		IF(@PROCESS_CURR='DVCM_D')
		BEGIN
			UPDATE PL_REQUEST_PROCESS SET [STATUS]='P', CHECKER_ID=@p_USERNAME, APPROVE_DT=CONVERT(DATETIME, @p_APPROVE_DT, 103),
                                           NOTES = @p_NOTES
      WHERE REQ_ID=@p_REQ_ID AND STATUS='C' AND PROCESS_ID = 'DVCM_D' AND DEP_ID = @DEP_ID
      UPDATE TR_REQUEST_SHOP_COSTCENTER SET AUTH_STATUS = 'A', CHECKER_ID = @p_CHECKER_ID WHERE REQ_ID = @p_REQ_ID AND COST_ID = @DEP_ID
			IF(NOT EXISTS(SELECT REQ_COST_ID FROM dbo.TR_REQUEST_SHOP_COSTCENTER WHERE REQ_ID=@p_REQ_ID AND AUTH_STATUS <> 'A'))
			BEGIN
				UPDATE PL_REQUEST_PROCESS SET [STATUS]='C' WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@PROCESS_CURR

				-- CẬP NHẬT TRẠNG THÁI CHO REQUEST_PROCESS
				SELECT @PROCESS_NEXT=PROCESS_ID,@ROLE=ROLE_USER,@BRANCH_NEXT=BRANCH_ID,@DEP_NEXT=DEP_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C'		
				UPDATE TR_REQUEST_SHOP_DOC SET [STATUS]= @PROCESS_NEXT WHERE REQ_ID=@p_REQ_ID
                UPDATE TR_REQUEST_SHOP_DOC SET HO_NOTES = N'- Kế hoạch (Trong/Ngoài NS): ' + CHAR(10) + N'- Nội dung cấp: ' + CHAR(10) +  N'- Ghi chú khác: ' 
                , SEND_APP_DT = CONVERT(DATETIME, @p_APPROVE_DT, 103)
                WHERE REQ_ID=@p_REQ_ID

				IF @@Error <> 0 GOTO ABORT
			
				SET @COMPLETE=1
			
			END
		END
		SET @NOTIFATION= N'Duyệt thành công'
		SET @NOTIFY_TO_USER = 'UD_REQ_DVCM_XN'
		SET @EMAIL_CONTENT = 'TYPE_PROCESS_REQ_DVCM_XN';
		SET @PROCESS_DES=N'Trưởng đơn vị chuyển môn duyệt'
	END
    ELSE IF(@p_TYPE_PROCESS='QLTS_NL')
	BEGIN
		-- Bắt buộc nhập ý kiến phòng hành chính
		IF( NOT EXISTS (SELECT HO_NOTES FROM dbo.TR_REQUEST_SHOP_DOC WHERE REQ_ID = @p_REQ_ID AND HO_NOTES IS NOT NULL AND HO_NOTES <> ''))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' WH_O_HCQT_MUL_ID, N'Chưa nhập ý kiến bộ phận Quản lý tài sản' ErrorDesc
			RETURN '-1'
		END
        -- Check có chọn loại ts ở lưới QLTS chưa, ko thì ko cho gửi
		IF( NOT EXISTS (SELECT * FROM dbo.TR_REQUEST_SHOP_DOC_DT WHERE REQ_DOC_ID = @p_REQ_ID AND REQ_DT_TYPE IN ('BUYNEW','XKSD')))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' WH_O_HCQT_MUL_ID, N'Thông tin ở lưới CHI TIẾT LOẠI TÀI SẢN CẤP PHÁT - QLTS đang trống' ErrorDesc
			RETURN '-1'
		END

        UPDATE PL_REQUEST_PROCESS SET [STATUS]='P', CHECKER_ID=@p_USERNAME, APPROVE_DT=CONVERT(DATETIME, @p_APPROVE_DT, 103)
        WHERE REQ_ID=@p_REQ_ID AND STATUS='C' AND PROCESS_ID = 'QLTS_NL'
		UPDATE PL_REQUEST_PROCESS SET [STATUS]='C' WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@PROCESS_CURR
        UPDATE TR_REQUEST_SHOP_DOC SET [STATUS]= 'TBP_D' WHERE REQ_ID=@p_REQ_ID

        SET @COMPLETE=1
            SET @p_NOTES = N'Bộ phận QLTS gửi đến Trưởng Bộ phận QLTS phê duyệt'
    		SET @NOTIFATION= N'Trưởng bộ phận QLTS đã tiếp nhận phiếu'
    		SET @NOTIFY_TO_USER = 'UD_REQ_S_APP'
    		SET @EMAIL_CONTENT = 'TYPE_PROCESS_REQ_S_APPP';
      	SET @PROCESS_DES=N'Trưởng Bộ phận QLTS tiếp nhận và đang kiểm duyệt'
    END
	ELSE IF(@p_TYPE_PROCESS='QLTS_D')
	BEGIN
--        IF(@p_DVKD_USER_APP IS NULL OR @p_DVKD_USER_APP = '')
--        BEGIN
--            ROLLBACK TRANSACTION
--            SELECT '-1' as Result, '' WH_O_HCQT_MUL_ID, N'Chưa chọn TT. QLTS để gửi duyệt' ErrorDesc
--            RETURN '-1'
--        END
--        IF(@p_DVKD_USER_APP IS NOT NULL OR @p_DVKD_USER_APP <> '')
        BEGIN
          INSERT INTO PL_REQUEST_PROCESS (REQ_ID, [PROCESS_ID], [STATUS], ROLE_USER, BRANCH_ID, PARENT_PROCESS_ID, DEP_ID, DVKD_USER_APP,PROCESS_TYPE)
          VALUES (@p_REQ_ID, 'TTQLTS_D', 'C', 'GDDV_QLTS', 'DV0001', 'QLTS_D', @DEP_DVNB, @p_DVKD_USER_APP, 'Approve') --DVKD USER APP TẠI BƯƠC NÀY LÀ TT QLTS USER

          UPDATE PL_REQUEST_PROCESS SET [STATUS]='P',CHECKER_ID=@p_USERNAME,APPROVE_DT=CONVERT(DATETIME, @p_APPROVE_DT, 103)
          WHERE REQ_ID=@p_REQ_ID  AND  STATUS='C' AND PROCESS_ID = 'QLTS_D'
          UPDATE PL_REQUEST_PROCESS SET PARENT_PROCESS_ID = 'TTQLTS_D' WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'APPROVE'
          UPDATE TR_REQUEST_SHOP_DOC SET [STATUS]= 'TTQLTS_D' WHERE REQ_ID=@p_REQ_ID
          IF @@Error <> 0 GOTO ABORT
	
				SET @COMPLETE=1
            SET @NOTIFATION= N'QLTS gửi đến Trung Tâm QLTS duyệt'
    		SET @NOTIFY_TO_USER = 'QLTS_S_TTQLTS'
    		SET @EMAIL_CONTENT = 'TYPE_PROCESS_REQ_QLTS_S_TTQLTS';
    		SET @PROCESS_DES=N'QLTS gửi đến Trung Tâm QLTS duyệt'
        END
	END
    ELSE IF(@p_TYPE_PROCESS='TBP_D')
	BEGIN

          UPDATE PL_REQUEST_PROCESS SET [STATUS]='P',CHECKER_ID=@p_USERNAME,APPROVE_DT=CONVERT(DATETIME, @p_APPROVE_DT, 103), NOTES = @p_NOTES
          WHERE REQ_ID=@p_REQ_ID AND STATUS='C' AND PROCESS_ID = @PROCESS_CURR

		    UPDATE PL_REQUEST_PROCESS SET [STATUS]='C' WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@PROCESS_CURR
          UPDATE TR_REQUEST_SHOP_DOC SET [STATUS]= 'QLTS_D' WHERE REQ_ID=@p_REQ_ID
          IF @@Error <> 0 GOTO ABORT
	
				SET @COMPLETE=1
            SET @NOTIFATION= N'Trưởng Bộ Phận QLTS phê duyệt và gửi đến Trưởng đơn vị QLTS duyệt'
    		SET @NOTIFY_TO_USER = 'QLTS_S_TTQLTS'
    		SET @EMAIL_CONTENT = 'TYPE_PROCESS_REQ_QLTS_S_TTQLTS';
    		SET @PROCESS_DES=N'Trưởng Bộ Phận QLTS phê duyệt và gửi đến Trưởng đơn vị QLTS duyệt'
	END
    ELSE IF(@p_TYPE_PROCESS='QLTS_A')
	BEGIN
		IF(EXISTS(SELECT * FROM TR_REQUEST_SHOP_DOC WHERE REQ_ID = @p_REQ_ID AND STATUS = 'CANCEL'))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' WH_O_HCQT_MUL_ID, N'Phiếu yêu cầu đã bị hủy' ErrorDesc
			RETURN '-1'
		END

		BEGIN

			UPDATE PL_REQUEST_PROCESS SET [STATUS]='P',CHECKER_ID=@p_USERNAME,APPROVE_DT=CONVERT(DATETIME, @p_APPROVE_DT, 103), NOTES = @p_NOTES
            WHERE REQ_ID=@p_REQ_ID AND [STATUS]='C' 
            AND PROCESS_ID='QLTS_D' 
            AND (ROLE_USER IN (SELECT ROLE_ID FROM dbo.TL_USER_GET_ROLES(@p_USERNAME)))

			IF @@Error <> 0 GOTO ABORT

			UPDATE PL_REQUEST_PROCESS SET [STATUS]='C', RECEPTION_DT = CONVERT(DATETIME, @p_APPROVE_DT, 103), CHECKER_ID=@p_USERNAME,APPROVE_DT=CONVERT(DATETIME, @p_APPROVE_DT, 103)  
            , NOTES = @p_NOTES
            WHERE PROCESS_ID = 'APPROVE' AND REQ_ID=@p_REQ_ID

			SELECT @PROCESS_NEXT=PROCESS_ID,@ROLE=ROLE_USER,@BRANCH_NEXT=BRANCH_ID,@DEP_NEXT=DEP_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C'	

			UPDATE TR_REQUEST_SHOP_DOC SET [STATUS]='APPROVE', AUTH_STATUS = 'A' WHERE REQ_ID=@p_REQ_ID
			
		
			IF @@Error <> 0 GOTO ABORT
			
			SET @COMPLETE=1
		END
		SET @NOTIFATION= N'Duyệt thành công phiếu yêu cầu'
		SET @PROCESS_DES=N'QLTS duyệt'
		SET @NOTIFY_TO_USER = 'UD_REQ_S_HCQT'
		SET @EMAIL_CONTENT = 'TYPE_PROCESS_REQ_QLTS_APP';
    	SET @PROCESS_DES=N'Bộ phận QLTS đã phê duyệt'
	END
    ELSE IF(@p_TYPE_PROCESS='CANCEL')
	BEGIN
		IF(EXISTS(SELECT * FROM TR_REQUEST_SHOP_DOC WHERE REQ_ID = @p_REQ_ID AND STATUS = 'CANCEL'))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' WH_O_HCQT_MUL_ID, N'Phiếu yêu cầu đã bị hủy' ErrorDesc
			RETURN '-1'
		END

        UPDATE TR_REQUEST_SHOP_DOC SET STATUS = 'CANCEL', AUTH_STATUS = 'D' WHERE REQ_ID = @p_REQ_ID

--        INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES)
--        VALUES (@p_REQ_ID, 'CANCEL', @p_CHECKER_ID, GETDATE(), N'Chuyên viên QLTS huỷ phiếu.', @p_NOTES);

--      DELETE PL_REQUEST_PROCESS 
--      WHERE REQ_ID = @p_REQ_ID AND STATUS = 'C' AND PROCESS_ID IN ('QLTS_N','QLTS_NL')

        DELETE PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND ((STATUS = 'C' AND PROCESS_ID IN ('QLTS_N','QLTS_NL'))
                                                                OR STATUS = 'U')

        INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,CHECKER_ID,APPROVE_DT,PARENT_PROCESS_ID,IS_LEAF,NOTES,BRANCH_ID,DEP_ID)
        VALUES(@p_REQ_ID,'CANCEL','C','QLTS',@p_CHECKER_ID,GETDATE(),@PROCESS_CURR,'Y',N'Chuyên viên QLTS huỷ phiếu',@BRANCHID,@DEP_ID)
      
        --SET @p_NOTES = N'Huỷ phiếu thành công'
        SET @NOTIFATION= N'Huỷ phiếu thành công'
	    SET @COMPLETE=1

	END

	-- NOTIFATIONS
	IF(@COMPLETE=1)
	BEGIN
    DECLARE @BXL NVARCHAR(MAX) = ''
    IF(@p_TYPE_PROCESS = 'S_TDV') SET @BXL = N'Gửi Trưởng đơn vị phê duyệt'
    IF(@p_TYPE_PROCESS = 'S_QLTS') SET @BXL = N'Trưởng đơn vị phê duyệt'
    IF(@p_TYPE_PROCESS = 'S_DVCM') SET @BXL = N'Bộ phận QLTS nhận phiếu'
    IF(@p_TYPE_PROCESS = 'S_TDVCM') SET @BXL = N'Bộ phận ĐVCM xử lý phiếu'
    IF(@p_TYPE_PROCESS = 'DVCM_D') SET @BXL = N'Trưởng đơn vị chuyên môn duyệt'
    IF(@p_TYPE_PROCESS = 'QLTS_NL') SET @BXL = N'Bộ phận QLTS xác nhận'
    IF(@p_TYPE_PROCESS = 'TBP_D') SET @BXL = N'Trưởng Bộ phận QLTS phê duyệt'
    IF(@p_TYPE_PROCESS = 'QLTS_A') SET @BXL = N'Bộ phận QLTS phê duyệt'
    IF(@p_TYPE_PROCESS = 'QLTS_D') SET @BXL = N'Gửi Trung Tâm QLTS phê duyệt'

    IF(@p_TYPE_PROCESS = 'CANCEL') SET @BXL = N'Chuyên viên QLTS huỷ phiếu'
    IF(@p_TYPE_PROCESS = 'CANCEL') SET @PROCESS_CURR = 'CANCEL'
    
IF(@p_TYPE_PROCESS = 'S_TDV' AND @p_NOTES IS NULL) SET @p_NOTES = N'Nhân viên tạo phiếu và gửi đến Trưởng đơn vị phê duyệt'
IF(@p_TYPE_PROCESS = 'S_TDVCM' AND @p_NOTES IS NULL) SET @p_NOTES = N'Nhân viên ĐVCM gửi Trưởng ĐCVM vị phê duyệt'
IF(@p_TYPE_PROCESS = 'QLTS_NL' AND @p_NOTES IS NULL) SET @p_NOTES = N'QLTS gửi đến Trưởng bộ phận QLTS duyệt'
IF(@p_TYPE_PROCESS = 'QLTS_D' AND @p_NOTES IS NULL) SET @p_NOTES = N'QLTS gửi đến Trung Tâm QLTS duyệt'
IF(@p_TYPE_PROCESS = 'QLTS_A' AND @p_NOTES IS NULL) SET @p_NOTES = N'Trung Tâm Dịch Vụ Nội Bộ phê duyệt phiếu thành công'
							-- insert log
							INSERT INTO dbo.PL_PROCESS
							(
								REQ_ID,
								PROCESS_ID,
								CHECKER_ID,
								APPROVE_DT,
								PROCESS_DESC,
								NOTES
							)
							VALUES
							(   @p_REQ_ID,        -- REQ_ID - varchar(15)
								@PROCESS_CURR,        -- PROCESS_ID - varchar(10)
								@p_USERNAME,        -- CHECKER_ID - varchar(100)
								CONVERT(DATETIME, @p_APPROVE_DT, 103), -- APPROVE_DT - datetime
								@BXL,       -- PROCESS_DESC - nvarchar(1000)
								@p_NOTES        -- NOTES - nvarchar(1000)
							)

							DECLARE @ID_PROCESS INT
							IF(@p_TYPE_PROCESS='REJECT' OR @p_TYPE_PROCESS='REJECT_DVCM' OR @p_TYPE_PROCESS='REJECT_CVMS' OR @p_TYPE_PROCESS='REJECT_QLTS' )
							BEGIN
							SET @ID_PROCESS=(SELECT  TOP 1 ID FROM dbo.PL_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_DESC=@p_TYPE_PROCESS ORDER BY ID DESC)
							END


							IF NOT EXISTS (SELECT 1 FROM @usersToNotify)
							BEGIN
								--INSERT INTO @usersToNotify
								--SELECT U.TLNANME FROM TL_USER u
								--		LEFT JOIN AbpUserRoles ur ON ur.UserId = u.ID
								--		LEFT JOIN AbpRoles r ON ur.RoleId = r.Id
								--		where r.DisplayName = @ROLE and u.TLNANME != @p_USERNAME
								INSERT INTO @usersToNotify
								SELECT U.TLNANME FROM TL_USER u
										WHERE u.TLNANME != @p_USERNAME
										and U.ID IN (SELECT ur.UserId FROM AbpUserRoles ur
										LEFT JOIN AbpRoles r ON ur.RoleId = r.Id
										where r.DisplayName = @ROLE)


							END


							Declare @C_TLNAME VARCHAR(100)
							-- declare a cursor
							DECLARE insert_cursor CURSOR FOR SELECT TLNANME FROM @usersToNotify WHERE TLNANME IS NOT NULL AND TLNANME <>''

							-- open cursor and fetch first row into variables
							OPEN insert_cursor
							
							UPDATE dbo.TL_ROLE_NOTIFICATION SET RECORD_STATUS = '0' WHERE PO_ID = @p_REQ_ID AND RECORD_STATUS = '1'

							FETCH NEXT FROM insert_cursor into @C_TLNAME
							WHILE @@FETCH_STATUS=0
							BEGIN
									DECLARE @NOTIFI_ID VARCHAR(15)
									EXEC SYS_CodeMasters_Gen 'TL_ROLE_NOTIFICATION', @NOTIFI_ID OUT
                                    
									INSERT INTO [dbo].[TL_ROLE_NOTIFICATION]
								   ([NOTIFI_ID]
								   ,[NOTIFI_CODE]
								   ,[NOTIFI_NAME]
								   ,[TYPE]
								   ,[PO_ID]
								   ,[TL_NAME]
								   ,[BRANCH_ID]
								   ,[RECORD_STATUS]
								   ,[AUTH_STATUS]
								   ,[EDITOR_ID]
								   ,[EDIT_DT]
								   ,[NOTES]
								  ,[MAKER_ID]
								  ,[CREATE_DT])
									 VALUES
									 (@NOTIFI_ID
								   ,NULL
								   ,NULL
								   ,'UD_REQ'
								   ,@p_REQ_ID
								   ,@C_TLNAME
								   ,@BRANCHID
								   ,'1'
								   ,'U'
								   ,@C_TLNAME
								   ,CONVERT(DATETIME,GETDATE(),103)
								   ,NULL
								   ,@C_TLNAME
								   ,CONVERT(DATETIME,GETDATE(),103))

								   -- Thông báo email
								   DECLARE @NFM_ID VARCHAR(15)
								   EXEC SYS_CodeMasters_Gen 'NF_MESSAGE', @NFM_ID OUT
								   INSERT INTO dbo.NF_MESSAGE
								   (
								       MESSAGE_ID,
								       SENDER_ID,
								       RECIPIENT_ID,
									   TARGET_ID,
								       MESSAGE_CONTENT,
									   CREATE_DATE,
								       SEND_DATE,
								       MESSAGE_STATUS,
								       READ_STATUS,
								       PAGE_OCCUR,
								       RECORD_ID,
								       DATATYPE_NAME
								   )
								   VALUES
								   (   @NOTIFI_ID,        -- MESSAGE_ID - varchar(15)
								       NULL,        -- SENDER_ID - varchar(100)
								       'UD_REQ',        -- RECIPIENT_ID - varchar(100)
									   @p_REQ_ID, -- TARGET_ID -- 
								       @EMAIL_CONTENT, -- MESSAGE_CONTENT - nvarchar(1000)
									   GETDATE(), -- CREATE_DATE - datetime
								       NULL, -- SEND_DATE - datetime
								       'NS',        -- MESSAGE_STATUS - varchar(100)
								       NULL,        -- READ_STATUS - varchar(100)
									   @p_CURRENT_URI, --'http://localhost:4200/app/admin/trade-request-doc-edit;id=' + @p_REQ_ID, -- PAGE_OCCUR - varchar(100)
								       @NFM_ID,        -- RECORD_ID - varchar(100)
								       @ID_PROCESS         -- DATATYPE_NAME - varchar(100)
								       )

								   print @C_TLNAME
							IF @@ERROR <> 0 GOTO ABORT
							-- check for a new row
							
							-- do complex operation here
							
							FETCH NEXT FROM insert_cursor into @C_TLNAME 
							END
							close insert_cursor
							Deallocate insert_cursor
							DECLARE @NOTIFI_ID_MAKER VARCHAR(15)
							 DECLARE @NFM_ID_MAKER VARCHAR(15)
							IF(@p_TYPE_PROCESS <> 'S_DVKD' AND (NOT EXISTS (SELECT * FROM @usersToNotify N WHERE N.TLNANME = @p_MAKERID)))
							BEGIN
								
									EXEC SYS_CodeMasters_Gen 'TL_ROLE_NOTIFICATION', @NOTIFI_ID_MAKER OUT
                                    
									INSERT INTO [dbo].[TL_ROLE_NOTIFICATION]
								   ([NOTIFI_ID]
								   ,[NOTIFI_CODE]
								   ,[NOTIFI_NAME]
								   ,[TYPE]
								   ,[PO_ID]
								   ,[TL_NAME]
								   ,[BRANCH_ID]
								   ,[RECORD_STATUS]
								   ,[AUTH_STATUS]
								   ,[EDITOR_ID]
								   ,[EDIT_DT]
								   ,[NOTES]
								  ,[MAKER_ID]
								  ,[CREATE_DT])
									 VALUES
									 (@NOTIFI_ID_MAKER
								   ,NULL
								   ,NULL
								   ,'UD_REQ_MAKER'
								   ,@p_REQ_ID
								   ,@p_MAKERID --@C_TLNAME
								   ,@BRANCHID
								   ,'1'
								   ,'U'
								   ,@p_MAKERID
								   ,CONVERT(DATETIME,GETDATE(),103)
								   ,NULL
								   ,@p_MAKERID
								   ,CONVERT(DATETIME,GETDATE(),103))

								   -- Thông báo email
								
								   EXEC SYS_CodeMasters_Gen 'NF_MESSAGE', @NFM_ID_MAKER OUT
								   INSERT INTO dbo.NF_MESSAGE
								   (
								       MESSAGE_ID,
								       SENDER_ID,
								       RECIPIENT_ID,
									   TARGET_ID,
								       MESSAGE_CONTENT,
									   CREATE_DATE,
								       SEND_DATE,
								       MESSAGE_STATUS,
								       READ_STATUS,
								       PAGE_OCCUR,
								       RECORD_ID,
								       DATATYPE_NAME
								   )
								   VALUES
								   (   @NOTIFI_ID_MAKER,        -- MESSAGE_ID - varchar(15)
								       NULL,        -- SENDER_ID - varchar(100)
								       'UD_REQ_MAKER',        -- RECIPIENT_ID - varchar(100)
									   @p_REQ_ID, -- TARGET_ID -- 
								       @EMAIL_CONTENT, -- MESSAGE_CONTENT - nvarchar(1000)
									   GETDATE(), -- CREATE_DATE - datetime
								       NULL, -- SEND_DATE - datetime
								       'NS',        -- MESSAGE_STATUS - varchar(100)
								       NULL,        -- READ_STATUS - varchar(100)
									   @p_CURRENT_URI, --'http://localhost:4200/app/admin/trade-request-doc-edit;id=' + @p_REQ_ID, -- PAGE_OCCUR - varchar(100)
								       @NFM_ID_MAKER,        -- RECORD_ID - varchar(100)
								       @ID_PROCESS         -- DATATYPE_NAME - varchar(100)
								    )
								 
							END
							ELSE
							BEGIN

								
									EXEC SYS_CodeMasters_Gen 'TL_ROLE_NOTIFICATION', @NOTIFI_ID_MAKER OUT
                                    
									INSERT INTO [dbo].[TL_ROLE_NOTIFICATION]
								   ([NOTIFI_ID]
								   ,[NOTIFI_CODE]
								   ,[NOTIFI_NAME]
								   ,[TYPE]
								   ,[PO_ID]
								   ,[TL_NAME]
								   ,[BRANCH_ID]
								   ,[RECORD_STATUS]
								   ,[AUTH_STATUS]
								   ,[EDITOR_ID]
								   ,[EDIT_DT]
								   ,[NOTES]
								  ,[MAKER_ID]
								  ,[CREATE_DT])
									 VALUES
									 (@NOTIFI_ID_MAKER
								   ,NULL
								   ,NULL
								   ,'UD_REQ_MAKER'
								   ,@p_REQ_ID
								   ,'app.qlts' --@C_TLNAME
								   ,@BRANCHID
								   ,'1'
								   ,'U'
								   ,'app.qlts'
								   ,CONVERT(DATETIME,GETDATE(),103)
								   ,NULL
								   ,'app.qlts'
								   ,CONVERT(DATETIME,GETDATE(),103))

								   -- Thông báo email
								 
								   EXEC SYS_CodeMasters_Gen 'NF_MESSAGE', @NFM_ID_MAKER OUT
								   INSERT INTO dbo.NF_MESSAGE
								   (
								       MESSAGE_ID,
								       SENDER_ID,
								       RECIPIENT_ID,
									   TARGET_ID,
								       MESSAGE_CONTENT,
									   CREATE_DATE,
								       SEND_DATE,
								       MESSAGE_STATUS,
								       READ_STATUS,
								       PAGE_OCCUR,
								       RECORD_ID,
								       DATATYPE_NAME
								   )
								   VALUES
								   (   @NOTIFI_ID_MAKER,        -- MESSAGE_ID - varchar(15)
								       NULL,        -- SENDER_ID - varchar(100)
								       'UD_REQ_MAKER',        -- RECIPIENT_ID - varchar(100)
									   @p_REQ_ID, -- TARGET_ID -- 
								       @EMAIL_CONTENT, -- MESSAGE_CONTENT - nvarchar(1000)
									   GETDATE(), -- CREATE_DATE - datetime
								       NULL, -- SEND_DATE - datetime
								       'NS',        -- MESSAGE_STATUS - varchar(100)
								       NULL,        -- READ_STATUS - varchar(100)
									   @p_CURRENT_URI, --'http://localhost:4200/app/admin/trade-request-doc-edit;id=' + @p_REQ_ID, -- PAGE_OCCUR - varchar(100)
								       @NFM_ID_MAKER,        -- RECORD_ID - varchar(100)
								       @ID_PROCESS         -- DATATYPE_NAME - varchar(100)
								    )
							END
							
END
COMMIT TRANSACTION
SELECT '0' as Result, @NOTIFATION AS NOTIFATION, '' ErrorDesc, @NOTIFY_TO_USER AS NEXT_USER_NOTIFI, @COMPLETE COMPLETE
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' AS RESULT
		RETURN '-1'
End
END