/*
Run this script on:

        118.69.72.241,5036.gAMSPro_VIETBANK_UAT_CUS    -  This database will be modified

to synchronize it with:

        118.69.72.241,5036.gAMSPro_VIETBANK_DEV

You are recommended to back up your database before running this script

Script created by SQL Compare version 13.1.6.5463 from Red Gate Software Ltd at 4/18/2023 2:14:51 PM

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL Serializable
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_DT_ById]'
GO
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_DT_ById]
	@p_IMP_MASTER_ID	VARCHAR(15)
AS	
SELECT A.*,B.BRANCH_NAME,C.DEP_NAME,D.EMP_NAME,E.ASSET_CODE,
F.BRANCH_NAME AS BRANCH_NAME_OLD, G.DEP_NAME AS DEP_NAME_OLD, H.EMP_NAME AS EMP_NAME_OLD
FROM ASS_IMPORT_UPDATE_DT A
LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
LEFT JOIN CM_EMPLOYEE D ON A.EMP_ID = D.EMP_ID
LEFT JOIN ASS_MASTER E ON A.ASSET_ID = E.ASSET_ID

LEFT JOIN CM_BRANCH F ON A.BRANCH_ID_OLD = F.BRANCH_ID
LEFT JOIN CM_DEPARTMENT G ON A.DEP_ID_OLD = G.DEP_ID
LEFT JOIN CM_EMPLOYEE H ON A.EMP_ID_OLD = H.EMP_ID

WHERE A.IMP_MASTER_ID = @p_IMP_MASTER_ID
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_App]'
GO
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_App]
	@p_IMP_MASTER_ID	VARCHAR(15),
  @p_CHECKER_ID VARCHAR(100),
  @p_APPROVE_DT VARCHAR(50)
AS	
	DECLARE @MESSAGE_VALIDATION NVARCHAR(MAX)

	IF(EXISTS(SELECT 1 FROM ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID AND AUTH_STATUS = 'A'))
	BEGIN
		SELECT '-1' Result, N'Phiếu đã được phê duyệt' ErrorDesc 
		RETURN '-1'
	END	

  IF(EXISTS(SELECT 1 FROM ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID AND AUTH_STATUS = 'R'))
	BEGIN
		SELECT '-1' Result, N'Phiếu đang được trả về' ErrorDesc 
		RETURN '-1'
	END	


BEGIN TRANSACTION
		UPDATE ASS_IMPORT_UPDATE_MASTER SET AUTH_STATUS = 'A' , CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@p_APPROVE_DT,103)
    WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID

    --kiểm tra branch,dep của tài sản hiện tại có giống branch,dep import không
    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' đã bị thay đổi Đơn vị/Phòng ban so với File Import'
                              FROM ( SELECT B.ASSET_CODE
                                    FROM ASS_IMPORT_UPDATE_DT A
                                    LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID
                                    WHERE (ISNULL(A.BRANCH_ID,'') <> ISNULL(B.BRANCH_ID,'') 
                                          OR ISNULL(A.DEP_ID,'') <> ISNULL(B.DEPT_ID,''))
                                          AND A.IMP_MASTER_ID = @p_IMP_MASTER_ID) C
                              FOR XML PATH (''))
                            , '|', '<br />'))    

    IF(@MESSAGE_VALIDATION IS NOT NULL AND @MESSAGE_VALIDATION <> '')
    BEGIN
        ROLLBACK TRANSACTION
    		SELECT '-1' Result, @MESSAGE_VALIDATION ErrorDesc 
    		RETURN '-1'
    END

    --check branch,dep của tài sản hiện tại có giống với branch,dep của người dùng không
    SET @MESSAGE_VALIDATION = (SELECT REPLACE((SELECT 
                                    CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT 0)) = 1 THEN ''
                                    ELSE '|' END 
                                    + N'Người dùng ' + C.EMP_NAME + '(' + C.EMP_CODE + ')' + N' không cùng đơn vị/phòng ban với tài sản ' + C.ASSET_CODE
                              FROM ( SELECT B.ASSET_CODE,D.EMP_CODE,D.EMP_NAME
                                    FROM ASS_IMPORT_UPDATE_DT A
                                    LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID
                                    LEFT JOIN CM_EMPLOYEE D ON A.EMP_ID = D.EMP_ID
                                    WHERE (ISNULL(D.BRANCH_ID,'') <> ISNULL(B.BRANCH_ID,'') 
                                          OR ISNULL(D.DEP_ID,'') <> ISNULL(B.DEPT_ID,''))
                                          AND A.IMP_MASTER_ID = @p_IMP_MASTER_ID) C
                              FOR XML PATH (''))
                            , '|', '<br />'))     

    IF(@MESSAGE_VALIDATION IS NOT NULL AND @MESSAGE_VALIDATION <> '')
    BEGIN
        ROLLBACK TRANSACTION
    		SELECT '-1' Result, @MESSAGE_VALIDATION ErrorDesc 
    		RETURN '-1'
    END

    UPDATE B SET B.EMP_ID = A.EMP_ID, B.ASSET_SERIAL_NO = A.ASSET_SERIAL_NO, B.ASSET_DESC = A.ASSET_DESC, B.NOTES = A.NOTES
    FROM ASS_IMPORT_UPDATE_DT A
    LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID
    WHERE A.IMP_MASTER_ID = @p_IMP_MASTER_ID

    INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES)
    VALUES (@p_IMP_MASTER_ID, 'APPROVE', @p_CHECKER_ID, GETDATE(), N'Duyệt phiếu cập nhật tài sản', N'Duyệt phiếu cập nhật tài sản thành công');

		IF @@Error <> 0 GOTO ABORT
		
COMMIT TRANSACTION
SELECT '0' as Result, '' ErrorDesc
RETURN '0'

ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ErrorDesc
		RETURN '-1'
End
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_ById]'
GO
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_ById]
	@p_IMP_MASTER_ID	VARCHAR(15)
AS	
SELECT A.* , B.TLFullName AS MAKER_NAME, C.AUTH_STATUS_NAME
FROM ASS_IMPORT_UPDATE_MASTER A
LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS = C.AUTH_STATUS
WHERE A.IMP_MASTER_ID = @p_IMP_MASTER_ID
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_Del]'
GO
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_Del]
	@p_IMP_MASTER_ID	VARCHAR(15)
AS	
	
	IF(EXISTS(SELECT 1 FROM ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID AND AUTH_STATUS = 'U'))
	BEGIN
		SELECT '-1' Result, N'Phiếu đang chờ phê duyệt. Xoá thất bại' ErrorDesc 
		RETURN '-1'
	END	

  IF(EXISTS(SELECT 1 FROM ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID AND AUTH_STATUS = 'A'))
	BEGIN
		SELECT '-1' Result, N'Phiếu đã được phê duyệt. Xoá thất bại' ErrorDesc 
		RETURN '-1'
	END	


BEGIN TRANSACTION
		DELETE ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID
    DELETE ASS_IMPORT_UPDATE_DT WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID

		IF @@Error <> 0 GOTO ABORT
		
COMMIT TRANSACTION
SELECT '0' as Result, '' ErrorDesc
RETURN '0'

ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ErrorDesc
		RETURN '-1'
End
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_Ins]'
GO
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_Ins]
@p_NOTES NVARCHAR(4000) = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID VARCHAR(100)  = NULL,
@p_CREATE_DT	VARCHAR(50) = NULL,
@p_AUTH_STATUS	VARCHAR(50)  = NULL,
@p_CHECKER_ID VARCHAR(100)  = NULL,
@p_APPROVE_DT	VARCHAR(50) = NULL,
@p_JSON_DATA NVARCHAR(MAX) = NULL
AS

  DECLARE 
          @ASSET_ID VARCHAR(15),
          @BRANCH_ID VARCHAR(15),
          @DEP_ID VARCHAR(15),
          @EMP_ID VARCHAR(15),
          @NOTES NVARCHAR(4000),
          @ASSET_DESC NVARCHAR(4000),
          @ASSET_SERIAL_NO NVARCHAR(4000),
          @ASSET_ID_OLD VARCHAR(15),
          @BRANCH_ID_OLD VARCHAR(15),
          @DEP_ID_OLD VARCHAR(15),
          @EMP_ID_OLD VARCHAR(15),
          @NOTES_OLD NVARCHAR(4000),
          @ASSET_DESC_OLD NVARCHAR(4000),
          @ASSET_SERIAL_NO_OLD NVARCHAR(4000)
  
  DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
  SELECT *
  FROM OPENJSON(@p_JSON_DATA)
  WITH 
  (
  ASSET_ID	VARCHAR(15) '$.ASSET_ID',
  BRANCH_ID	VARCHAR(15) '$.BRANCH_ID', 
  DEP_ID VARCHAR(15) '$.DEP_ID',
  EMP_ID VARCHAR(15) '$.EMP_ID',
  NOTES NVARCHAR(4000) '$.NOTES', 
  ASSET_DESC NVARCHAR(4000) '$.ASSET_DESC', 
  ASSET_SERIAL_NO NVARCHAR(4000) '$.ASSET_SERIAL_NO',  
  BRANCH_ID_OLD	VARCHAR(15) '$.BRANCH_ID_OLD', 
  DEP_ID_OLD VARCHAR(15) '$.DEP_ID_OLD',
  EMP_ID_OLD VARCHAR(15) '$.EMP_ID_OLD',
  NOTES_OLD NVARCHAR(4000) '$.NOTES_OLD', 
  ASSET_DESC_OLD NVARCHAR(4000) '$.ASSET_DESC_OLD', 
  ASSET_SERIAL_NO_OLD NVARCHAR(4000) '$.ASSET_SERIAL_NO_OLD'
  )	
  
  OPEN cur
  

BEGIN TRANSACTION

    DECLARE @p_IMP_MASTER_ID VARCHAR(15)
		EXEC SYS_CodeMasters_Gen 'ASS_IMPORT_UPDATE_MASTER', @p_IMP_MASTER_ID out
		IF @p_IMP_MASTER_ID ='' OR @p_IMP_MASTER_ID IS NULL GOTO ABORT

    INSERT INTO ASS_IMPORT_UPDATE_MASTER (IMP_MASTER_ID, NOTES, RECORD_STATUS, AUTH_STATUS, MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT)
    VALUES (@p_IMP_MASTER_ID, @p_NOTES, '1', 'E', @p_MAKER_ID, GETDATE(), NULL, NULL);
    
    
    FETCH NEXT FROM cur INTO @ASSET_ID,@BRANCH_ID,@DEP_ID,@EMP_ID,@NOTES,@ASSET_DESC,@ASSET_SERIAL_NO,@BRANCH_ID_OLD,@DEP_ID_OLD,@EMP_ID_OLD,@NOTES_OLD,@ASSET_DESC_OLD,@ASSET_SERIAL_NO_OLD
    
    WHILE @@FETCH_STATUS = 0 BEGIN
    
      DECLARE @l_IMP_DT_ID VARCHAR(15)
  		EXEC SYS_CodeMasters_Gen 'ASS_IMPORT_UPDATE_DT', @l_IMP_DT_ID out
  		IF @l_IMP_DT_ID='' OR @l_IMP_DT_ID IS NULL GOTO ABORT
  
      INSERT INTO ASS_IMPORT_UPDATE_DT (IMP_DT_ID, IMP_MASTER_ID, ASSET_ID, BRANCH_ID, DEP_ID, EMP_ID, NOTES, ASSET_DESC, ASSET_SERIAL_NO, BRANCH_ID_OLD, DEP_ID_OLD, EMP_ID_OLD, NOTES_OLD, ASSET_DESC_OLD, ASSET_SERIAL_NO_OLD)
      VALUES (@l_IMP_DT_ID, @p_IMP_MASTER_ID, @ASSET_ID, @BRANCH_ID, @DEP_ID, @EMP_ID, @NOTES, @ASSET_DESC, @ASSET_SERIAL_NO,  @BRANCH_ID_OLD, @DEP_ID_OLD, @EMP_ID_OLD, @NOTES_OLD, @ASSET_DESC_OLD, @ASSET_SERIAL_NO_OLD);
    
    FETCH NEXT FROM cur INTO @ASSET_ID,@BRANCH_ID,@DEP_ID,@EMP_ID,@NOTES,@ASSET_DESC,@ASSET_SERIAL_NO,@BRANCH_ID_OLD,@DEP_ID_OLD,@EMP_ID_OLD,@NOTES_OLD,@ASSET_DESC_OLD,@ASSET_SERIAL_NO_OLD
    
    END
    
    CLOSE cur
    DEALLOCATE cur		
		


    INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES)
    VALUES (@p_IMP_MASTER_ID, 'CREATE', @p_MAKER_ID, GETDATE(), N'Nhân viên thêm mới', N'Thêm mới import tài sản thành công');


		IF @@ERROR <> 0 GOTO ABORT

COMMIT TRANSACTION
SELECT '0' as Result,@p_IMP_MASTER_ID ID, N'Thêm mới thành công' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' AS RESULT
		RETURN '-1'
End
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_Search]'
GO
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_Search]
		@p_IMP_MASTER_ID	VARCHAR(15)  = NULL,
		@p_NOTES	NVARCHAR(4000)  = NULL,
		@p_MAKER_NAME	NVARCHAR(1000)  = NULL,
		@p_TOP INT		= NULL
AS 
BEGIN -- PAGING

	IF @p_TOP IS NULL OR @p_TOP  = ''
	BEGIN
	-- PAGING BEGIN
		SELECT A.*,B.TLFullName AS MAKER_NAME, C.AUTH_STATUS_NAME
		-- SELECT END
		FROM ASS_IMPORT_UPDATE_MASTER A
    LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
    LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS = C.AUTH_STATUS
    WHERE 1=1
    AND (@p_IMP_MASTER_ID = A.IMP_MASTER_ID OR @p_IMP_MASTER_ID IS NULL OR @p_IMP_MASTER_ID = '')
    AND (B.TLFullName  COLLATE Latin1_general_CI_AI LIKE N'%' + @p_MAKER_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_MAKER_NAME IS NULL OR @p_MAKER_NAME = '')
    AND (A.NOTES  COLLATE Latin1_general_CI_AI LIKE N'%' + @p_NOTES + N'%' COLLATE Latin1_general_CI_AI OR @p_NOTES IS NULL OR @p_NOTES = '')
		ORDER BY A.CREATE_DT DESC
	-- PAGING END
	END
	ELSE
	BEGIN
	-- PAGING BEGIN
		SELECT TOP (@p_TOP)	A.*, B.TLFullName AS MAKER_NAME, C.AUTH_STATUS_NAME
		-- SELECT END
		FROM ASS_IMPORT_UPDATE_MASTER A
    LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
    LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS = C.AUTH_STATUS
    WHERE 1=1
    AND (@p_IMP_MASTER_ID = A.IMP_MASTER_ID OR @p_IMP_MASTER_ID IS NULL OR @p_IMP_MASTER_ID = '')
    AND (B.TLFullName  COLLATE Latin1_general_CI_AI LIKE N'%' + @p_MAKER_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_MAKER_NAME IS NULL OR @p_MAKER_NAME = '')
    AND (A.NOTES  COLLATE Latin1_general_CI_AI LIKE N'%' + @p_NOTES + N'%' COLLATE Latin1_general_CI_AI OR @p_NOTES IS NULL OR @p_NOTES = '')
		ORDER BY A.CREATE_DT DESC
	-- PAGING END
	END
END -- PAGING
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_SendAppr]'
GO
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_SendAppr]
	@p_IMP_MASTER_ID	VARCHAR(15)
AS	
	
	IF(EXISTS(SELECT 1 FROM ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID AND AUTH_STATUS = 'U'))
	BEGIN
		SELECT '-1' Result, N'Phiếu đã được gửi phê duyệt' ErrorDesc 
		RETURN '-1'
	END	

  IF(EXISTS(SELECT 1 FROM ASS_IMPORT_UPDATE_MASTER WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID AND AUTH_STATUS = 'A'))
	BEGIN
		SELECT '-1' Result, N'Phiếu đã được phê duyệt' ErrorDesc 
		RETURN '-1'
	END	


BEGIN TRANSACTION
		UPDATE ASS_IMPORT_UPDATE_MASTER SET AUTH_STATUS = 'U' WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID

		IF @@Error <> 0 GOTO ABORT
		
COMMIT TRANSACTION
SELECT '0' as Result, N'Gửi phê duyệt thành công' ErrorDesc
RETURN '0'

ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ErrorDesc
		RETURN '-1'
End
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_Upd]'
GO
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_Upd]
@p_IMP_MASTER_ID VARCHAR(15) = NULL,
@p_NOTES NVARCHAR(4000) = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID VARCHAR(100)  = NULL,
@p_CREATE_DT	VARCHAR(50) = NULL,
@p_AUTH_STATUS	VARCHAR(50)  = NULL,
@p_CHECKER_ID VARCHAR(100)  = NULL,
@p_APPROVE_DT	VARCHAR(50) = NULL,
@p_JSON_DATA NVARCHAR(MAX) = NULL
AS

  DECLARE 
          @ASSET_ID VARCHAR(15),
          @BRANCH_ID VARCHAR(15),
          @DEP_ID VARCHAR(15),
          @EMP_ID VARCHAR(15),
          @NOTES NVARCHAR(4000),
          @ASSET_DESC NVARCHAR(4000),
          @ASSET_SERIAL_NO NVARCHAR(4000),
          @ASSET_ID_OLD VARCHAR(15),
          @BRANCH_ID_OLD VARCHAR(15),
          @DEP_ID_OLD VARCHAR(15),
          @EMP_ID_OLD VARCHAR(15),
          @NOTES_OLD NVARCHAR(4000),
          @ASSET_DESC_OLD NVARCHAR(4000),
          @ASSET_SERIAL_NO_OLD NVARCHAR(4000)
  
  DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
  SELECT *
  FROM OPENJSON(@p_JSON_DATA)
  WITH 
  (
  ASSET_ID	VARCHAR(15) '$.ASSET_ID',
  BRANCH_ID	VARCHAR(15) '$.BRANCH_ID', 
  DEP_ID VARCHAR(15) '$.DEP_ID',
  EMP_ID VARCHAR(15) '$.EMP_ID',
  NOTES NVARCHAR(4000) '$.NOTES', 
  ASSET_DESC NVARCHAR(4000) '$.ASSET_DESC', 
  ASSET_SERIAL_NO NVARCHAR(4000) '$.ASSET_SERIAL_NO',  
  BRANCH_ID_OLD	VARCHAR(15) '$.BRANCH_ID_OLD', 
  DEP_ID_OLD VARCHAR(15) '$.DEP_ID_OLD',
  EMP_ID_OLD VARCHAR(15) '$.EMP_ID_OLD',
  NOTES_OLD NVARCHAR(4000) '$.NOTES_OLD', 
  ASSET_DESC_OLD NVARCHAR(4000) '$.ASSET_DESC_OLD', 
  ASSET_SERIAL_NO_OLD NVARCHAR(4000) '$.ASSET_SERIAL_NO_OLD'
  )	
  
  OPEN cur
  

BEGIN TRANSACTION

    UPDATE ASS_IMPORT_UPDATE_MASTER
    SET NOTES = @p_NOTES
    WHERE IMP_MASTER_ID = @p_IMP_MASTER_ID
    
    
    FETCH NEXT FROM cur INTO @ASSET_ID,@BRANCH_ID,@DEP_ID,@EMP_ID,@NOTES,@ASSET_DESC,@ASSET_SERIAL_NO,@BRANCH_ID_OLD,@DEP_ID_OLD,@EMP_ID_OLD,@NOTES_OLD,@ASSET_DESC_OLD,@ASSET_SERIAL_NO_OLD
    
    WHILE @@FETCH_STATUS = 0 BEGIN
    
      DECLARE @l_IMP_DT_ID VARCHAR(15)
  		EXEC SYS_CodeMasters_Gen 'ASS_IMPORT_UPDATE_DT', @l_IMP_DT_ID out
  		IF @l_IMP_DT_ID='' OR @l_IMP_DT_ID IS NULL GOTO ABORT
  
      INSERT INTO ASS_IMPORT_UPDATE_DT (IMP_DT_ID, IMP_MASTER_ID, ASSET_ID, BRANCH_ID, DEP_ID, EMP_ID, NOTES, ASSET_DESC, ASSET_SERIAL_NO, BRANCH_ID_OLD, DEP_ID_OLD, EMP_ID_OLD, NOTES_OLD, ASSET_DESC_OLD, ASSET_SERIAL_NO_OLD)
      VALUES (@l_IMP_DT_ID, @p_IMP_MASTER_ID, @ASSET_ID, @BRANCH_ID, @DEP_ID, @EMP_ID, @NOTES, @ASSET_DESC, @ASSET_SERIAL_NO,  @BRANCH_ID_OLD, @DEP_ID_OLD, @EMP_ID_OLD, @NOTES_OLD, @ASSET_DESC_OLD, @ASSET_SERIAL_NO_OLD);
    
    FETCH NEXT FROM cur INTO @ASSET_ID,@BRANCH_ID,@DEP_ID,@EMP_ID,@NOTES,@ASSET_DESC,@ASSET_SERIAL_NO,@BRANCH_ID_OLD,@DEP_ID_OLD,@EMP_ID_OLD,@NOTES_OLD,@ASSET_DESC_OLD,@ASSET_SERIAL_NO_OLD
    
    END
    
    CLOSE cur
    DEALLOCATE cur			
		


    INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES)
    VALUES (@p_IMP_MASTER_ID, 'UPDATE', @p_MAKER_ID, GETDATE(), N'Nhân viên cập nhật', N'Cập nhật import tài sản thành công');


		IF @@ERROR <> 0 GOTO ABORT

COMMIT TRANSACTION
SELECT '0' as Result,@p_IMP_MASTER_ID ID, N'Cập nhật thành công' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' AS RESULT
		RETURN '-1'
End
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[CM_DEPARTMENT_GET_BLOCK_CENTER]'
GO
CREATE PROCEDURE [dbo].[CM_DEPARTMENT_GET_BLOCK_CENTER]
	@p_DEP_ID_INPUT VARCHAR(15),
	@p_BLOCK_ID VARCHAR(15)  OUT,
  @p_CENTER_ID VARCHAR(15)  OUT,
  @p_DEP_ID VARCHAR(15)  OUT
AS

SELECT 
@p_BLOCK_ID = (CASE WHEN DP.TYPE = 'K' THEN DP.DEP_ID ELSE K.DEP_ID END),
@p_CENTER_ID = (CASE WHEN DP.TYPE = 'TT' THEN DP.DEP_ID ELSE TT.DEP_ID END),
@p_DEP_ID = (CASE WHEN DP.TYPE = 'PB' THEN DP.DEP_ID ELSE NULL END)

FROM CM_DEPARTMENT DP
LEFT JOIN CM_DEPARTMENT TT ON DP.FATHER_ID = TT.DEP_ID --TRUNG TÂM
LEFT JOIN CM_DEPARTMENT K ON DP.KHOI_ID = K.DEP_ID --KHỐI
WHERE DP.DEP_ID = @p_DEP_ID_INPUT
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_IMPORT_UPDATE_Import]'
GO
CREATE PROCEDURE [dbo].[ASS_IMPORT_UPDATE_Import] 	
	@P_JSON_DATA NVARCHAR(MAX)
AS
BEGIN

	DECLARE @TMPASSETCODE TABLE(
		ASSET_CODE		VARCHAR(100),
		BRANCH_CODE		VARCHAR(100),
		DEP_CODE		VARCHAR(100),
    EMP_CODE		VARCHAR(100),
    NOTES NVARCHAR(4000),
    ASSET_DESC NVARCHAR(4000),
    ASSET_SERIAL_NO NVARCHAR(4000)
    
	)

	INSERT INTO @TMPASSETCODE
  SELECT *
  FROM OPENJSON(@P_JSON_DATA)
  WITH 
  (
  ASSET_CODE	VARCHAR(100) '$.ASSET_CODE',
  BRANCH_CODE	VARCHAR(1000) '$.BRANCH_CODE', 
  DEP_CODE VARCHAR(100) '$.DEP_CODE',
  EMP_CODE VARCHAR(100) '$.EMP_CODE',
  NOTES NVARCHAR(4000) '$.NOTES', 
  ASSET_DESC NVARCHAR(4000) '$.ASSET_DESC', 
  ASSET_SERIAL_NO NVARCHAR(4000) '$.ASSET_SERIAL_NO'
  )	

	DECLARE @ERROR_MESSAGE NVARCHAR(MAX)

  SELECT @ERROR_MESSAGE = STUFF( (
  SELECT ',' + A.ROW FROM (
  SELECT CONVERT(VARCHAR(10),ROW_NUMBER() OVER (ORDER BY (SELECT 1))) AS ROW, T.ASSET_CODE
  FROM @TMPASSETCODE T ) A
  WHERE A.ASSET_CODE IS NULL
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

	IF(LEN(@ERROR_MESSAGE) > 0)
	BEGIN
		SET @ERROR_MESSAGE = N'Dòng: ' + @ERROR_MESSAGE + N' mã tài sản không được để trống'
		EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
		RETURN '-1'
	END

  SELECT @ERROR_MESSAGE = STUFF( (
  SELECT ',' + A.ROW FROM (
  SELECT CONVERT(VARCHAR(10),ROW_NUMBER() OVER (ORDER BY (SELECT 1))) AS ROW, T.BRANCH_CODE
  FROM @TMPASSETCODE T 
  LEFT JOIN ASS_MASTER B ON T.ASSET_CODE = B.ASSET_CODE
  WHERE B.BRANCH_ID IS NOT NULL AND B.BRANCH_ID <> '' ) A
  WHERE A.BRANCH_CODE IS NULL 
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

	IF(LEN(@ERROR_MESSAGE) > 0)
	BEGIN
		SET @ERROR_MESSAGE = N'Dòng: ' + @ERROR_MESSAGE + N' mã đơn vị không được để trống'
		EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
		RETURN '-1'
	END

  SELECT @ERROR_MESSAGE = STUFF( (
  SELECT T.EMP_CODE
  FROM @TMPASSETCODE T 
  LEFT JOIN CM_EMPLOYEE E ON T.EMP_CODE = E.EMP_CODE
  WHERE E.EMP_ID IS NULL AND T.EMP_CODE IS NOT NULL AND T.EMP_CODE <> ''
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

	IF(LEN(@ERROR_MESSAGE) > 0)
	BEGIN
		SET @ERROR_MESSAGE = N'Người dùng không tồn tại trên hệ thống: ' + @ERROR_MESSAGE
		EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
		RETURN '-1'
	END

  SELECT @ERROR_MESSAGE = STUFF( (
  SELECT N'Người dùng '  + A.EMP_CODE + N' và tài sản ' + A.ASSET_CODE + N' không cùng Đơn vị/Phòng ban với nhau </br>'
  FROM @TMPASSETCODE A
  LEFT JOIN ASS_MASTER B ON A.ASSET_CODE = B.ASSET_CODE
  LEFT JOIN CM_EMPLOYEE E ON A.EMP_CODE = E.EMP_CODE
  WHERE A.EMP_CODE IS NOT NULL AND A.EMP_CODE <> '' AND (ISNULL(E.BRANCH_ID,'') <> ISNULL(B.BRANCH_ID,'') OR ISNULL(E.DEP_ID,'') <> ISNULL(B.DEPT_ID,''))
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

	IF(LEN(@ERROR_MESSAGE) > 0)
	BEGIN
		SET @ERROR_MESSAGE = @ERROR_MESSAGE
		EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
		RETURN '-1'
	END



	SELECT @ERROR_MESSAGE = STUFF( (
	SELECT ',' + T.ASSET_CODE 
  FROM @TMPASSETCODE T 
	LEFT JOIN ASS_MASTER B ON T.ASSET_CODE = B.ASSET_CODE 
  WHERE B.ASSET_ID IS NULL
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

	IF(LEN(@ERROR_MESSAGE) > 0)
	BEGIN
		SET @ERROR_MESSAGE = N'Mã tài sản không tồn tại trong hệ thống: ' + @ERROR_MESSAGE
		EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
		RETURN '-1'
	END


	SELECT @ERROR_MESSAGE = STUFF( (
	SELECT ',' + T.ASSET_CODE FROM @TMPASSETCODE T 
  GROUP BY T.ASSET_CODE
  HAVING COUNT(*) > 1
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

	IF(LEN(@ERROR_MESSAGE) > 0)
	BEGIN
		SET @ERROR_MESSAGE = N'Mã tài sản bị trùng: ' + @ERROR_MESSAGE
		EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
		RETURN '-1'
	END


	SELECT @ERROR_MESSAGE = STUFF( (
	SELECT ',' + T.BRANCH_CODE FROM @TMPASSETCODE T 
	WHERE T.BRANCH_CODE IS NOT NULL AND T.BRANCH_CODE <> '' 
  AND NOT EXISTS(SELECT 1 FROM CM_BRANCH B WHERE B.BRANCH_CODE = T.BRANCH_CODE AND B.RECORD_STATUS = '1') 
  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

	IF(LEN(@ERROR_MESSAGE) > 0)
	BEGIN
		SET @ERROR_MESSAGE = N'Mã đơn vị không tồn tại trong hệ thống: ' + @ERROR_MESSAGE
    EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
		RETURN '-1'
	END


	SELECT @ERROR_MESSAGE = STUFF( (
	  SELECT ',' + A.ASSET_CODE
    FROM @TMPASSETCODE A
    LEFT JOIN ASS_MASTER B ON A.ASSET_CODE = B.ASSET_CODE
    LEFT JOIN CM_BRANCH C ON B.BRANCH_ID = C.BRANCH_ID
    LEFT JOIN CM_DEPARTMENT D ON B.DEPT_ID = D.DEP_ID
    WHERE (ISNULL(C.BRANCH_CODE,'') <> ISNULL(A.BRANCH_CODE,'')
          OR ISNULL(D.DEP_CODE,'') <> ISNULL(A.DEP_CODE,'')) AND A.BRANCH_CODE IS NOT NULL AND A.BRANCH_CODE <> ''
    FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

	IF(LEN(@ERROR_MESSAGE) > 0)
	BEGIN
		SET @ERROR_MESSAGE = N'Những tài sản có Đơn vị/Phòng ban không khớp với tài sản trên hệ thống: ' + @ERROR_MESSAGE
    EXEC SP_SETERRORMESSAGE @ERROR_MESSAGE
		RETURN '-1'
	END	

  
  SELECT B.ASSET_ID,B.ASSET_CODE,B.ASSET_NAME,
          G.BRANCH_ID,G.BRANCH_CODE,G.BRANCH_NAME,
          H.DEP_ID,H.DEP_CODE,H.DEP_NAME,
          E.EMP_ID,E.EMP_CODE,E.EMP_NAME,
          A.NOTES,A.ASSET_DESC,A.ASSET_SERIAL_NO,
          
          B.ASSET_ID AS ASSET_ID_OLD,B.ASSET_CODE AS ASSET_CODE_OLD,B.ASSET_NAME AS ASSET_NAME_OLD,
          C.BRANCH_ID AS BRANCH_ID_OLD,C.BRANCH_CODE AS BRANCH_CODE_OLD,C.BRANCH_NAME AS BRANCH_NAME_OLD,
          D.DEP_ID AS DEP_ID_OLD,D.DEP_CODE AS DEP_CODE_OLD,D.DEP_NAME AS DEP_NAME_OLD,
          F.EMP_ID AS EMP_ID_OLD,F.EMP_CODE AS EMP_CODE_OLD,F.EMP_NAME AS EMP_NAME_OLD,
          B.NOTES AS NOTES_OLD,B.ASSET_DESC AS ASSET_DESC_OLD,B.ASSET_SERIAL_NO AS ASSET_SERIAL_NO_OLD
        
        
  FROM @TMPASSETCODE A
  LEFT JOIN ASS_MASTER B ON A.ASSET_CODE = B.ASSET_CODE

  LEFT JOIN CM_BRANCH C ON B.BRANCH_ID = C.BRANCH_ID
  LEFT JOIN CM_DEPARTMENT D ON B.DEPT_ID = D.DEP_ID
  LEFT JOIN CM_EMPLOYEE F ON B.EMP_ID = F.EMP_ID


  LEFT JOIN CM_BRANCH G ON A.BRANCH_CODE = G.BRANCH_CODE
  LEFT JOIN CM_DEPARTMENT H ON A.DEP_CODE = H.DEP_CODE
  LEFT JOIN CM_EMPLOYEE E ON A.EMP_CODE = E.EMP_CODE
  
	
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ASS_COST_ALLOCATION_Import]'
GO
CREATE PROCEDURE [dbo].[ASS_COST_ALLOCATION_Import]
@p_XmlData XML = NULL

AS
DECLARE 
@BRANCH_CODE VARCHAR(25),
@DEP_CODE VARCHAR(25),
@COST_AMOUNT DECIMAL(18,2),
@COST_MONTH INT,
@COST_RATE DECIMAL(18,2)
	

DECLARE @TableCost  TABLE (
BRANCH_ID   VARCHAR(25),
DEPT_ID     VARCHAR(25),
COST_AMOUNT VARCHAR(25),
COST_RATE   VARCHAR(25),
COST_MONTH  VARCHAR(25)
)

  Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output,@p_XmlData

 SELECT T.BRANCH_CODE
       ,T.DEP_CODE
       ,T.COST_AMOUNT
       ,T.COST_MONTH
       ,T.COST_RATE,
       cb.BRANCH_ID,
       cb.BRANCH_NAME,
       cd.DEP_ID,
       cd.DEP_NAME FROM 
  (
	SELECT BRANCH_CODE,DEP_CODE,COST_AMOUNT,COST_MONTH,COST_RATE
	FROM OPENXML(@hdoc,'/Root/XmlData',2)
	WITH 
	(
		BRANCH_CODE varchar(15),
		DEP_CODE	VARCHAR(15),
		COST_AMOUNT DECIMAL(18,2),
    COST_MONTH INT,
		COST_RATE DECIMAL(18,2)		
	)

) T
LEFT JOIN CM_BRANCH cb ON cb.BRANCH_CODE=T.BRANCH_CODE
LEFT JOIN CM_DEPARTMENT cd ON cd.DEP_CODE=T.DEP_CODE
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[TR_REQUEST_SHOP_DOC_Excel]'
GO
CREATE PROCEDURE [dbo].[TR_REQUEST_SHOP_DOC_Excel]
@p_REQ_ID	varchar(15)  = NULL,
@p_REQ_CODE	nvarchar(100)  = NULL,
@p_REQ_NAME	nvarchar(200)  = NULL,
@p_REQ_DT	VARCHAR(20) = NULL,
@p_REQ_TYPE	varchar(20) = NULL,
@p_REQ_CONTENT NVARCHAR(1000)=NULL,
@p_TOTAL_AMT	decimal = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID NVARCHAR(100)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID VARCHAR(100)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_RECEIVE_BRANCH VARCHAR(15) = NULL,
@p_USERNAME VARCHAR(100) = NULL,
@p_BRANCH_ID VARCHAR(15)=NULL,
@p_DEP_ID VARCHAR(15)=NULL,
@p_STATUS  VARCHAR(15)=NULL,
@p_TOP	INT = 10,
@p_DVKD_MANAGE_APP_FROM	VARCHAR(20) = NULL,
@p_DVKD_MANAGE_APP_TO	VARCHAR(20) = NULL,
@p_REGION_ID varchar(15)  = NULL,
@p_CDTYPE_PYC VARCHAR(20) = NULL --Phucvh Truyền thêm CDTYPE để xác định loại PYC


AS
BEGIN -- PAGING
    DECLARE @ROLE_KT VARCHAR(50) = (SELECT TU.RoleName FROM TL_USER TU WHERE TU.TLNANME = @p_USERNAME)
	DECLARE @DEP_ID VARCHAR(20),@BRANCH_ID VARCHAR(20)
	DECLARE  @ROLE_LOGIN TABLE(ROLE_USER VARCHAR(20), BRANCH_ID VARCHAR(20), DEP_ID VARCHAR(20))
--	INSERT INTO @ROLE_LOGIN SELECT DisplayName AS ROLE_USER FROM AbpRoles WHERE Id IN (SELECT A.RoleId FROM AbpUserRoles A 
--																		LEFT JOIN TL_USER B ON A.UserId = B.ID
--																		WHERE B.TLNANME = @p_USERNAME)
  INSERT INTO @ROLE_LOGIN
  SELECT tugr.ROLE_ID, tugr.BRANCH_ID, tugr.DEPT_ID FROM dbo.TL_USER_GET_ROLES(@p_USERNAME) tugr
	SET @DEP_ID= (SELECT DEP_ID FROM dbo.TL_USER WHERE TLNANME=@p_USERNAME)
	SET @BRANCH_ID= (SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_USERNAME)
	DECLARE @lstCOST TABLE
	(
		COST_ID VARCHAR(20)
	)
	INSERT INTO @lstCOST
	SELECT COST_ID FROM dbo.CM_COSTCENTER_DT WHERE BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID
	-- TienLee 11/14/21 --
	
	--

	DECLARE @TempSTATUS   TABLE
	(
		STATUS VARCHAR(20)
	)

	IF(EXISTS(SELECT ROLE_USER FROM @ROLE_LOGIN WHERE ROLE_USER ='QLTS' ))
	BEGIN
		INSERT INTO @TempSTATUS VALUES('DVKD')
		INSERT INTO @TempSTATUS VALUES('DVCM')
	END
	ELSE IF(EXISTS(SELECT ROLE_USER FROM @ROLE_LOGIN WHERE ROLE_USER ='DVCM' ))
	BEGIN
		INSERT INTO @TempSTATUS VALUES('QLTS_N')
		INSERT INTO @TempSTATUS VALUES('DVCM')
	END
	ELSE IF(EXISTS(SELECT ROLE_USER FROM @ROLE_LOGIN WHERE ROLE_USER ='CVMS' ))
	BEGIN
		INSERT INTO @TempSTATUS VALUES('QLTS_N')
		INSERT INTO @TempSTATUS VALUES('QLTS_XL')
		INSERT INTO @TempSTATUS VALUES('DVCM')
	END

		DECLARE @lstBRANCH_DEP TABLE
	(
		BRANCH_ID VARCHAR(20),
		DEP_ID VARCHAR(20)
	) 

--	IF(NOT EXISTS(SELECT ROLE_USER FROM @ROLE_LOGIN WHERE ROLE_USER ='DVCM' OR ROLE_USER='CVMS_TBP' ))
--	BEGIN
--		INSERT INTO @lstBRANCH_DEP
--		(BRANCH_ID,DEP_ID)
--		SELECT TLSUBBRID,DEP_ID FROM dbo.TL_USER WHERE TLNANME=@p_USERNAME 
--
--		INSERT INTO @lstBRANCH_DEP
--		(BRANCH_ID,DEP_ID)
--		SELECT BRANCH_ID,DEP_ID FROM dbo.CM_KHOI_DT
--		WHERE KHOI_ID IN (SELECT KHOI_ID FROM dbo.CM_KHOI_DT WHERE DEP_ID= @DEP_ID AND BRANCH_ID=@BRANCH_ID)
--	END
--
--
--
--  DECLARE @tbDep TABLE(DEP_ID VARCHAR(20))
--INSERT INTO @tbDep
--SELECT ckd.DEP_ID FROM CM_DEPARTMENT cd
----LEFT JOIN CM_KHOI ck ON cd.DEP_CODE=ck.KHOI_CODE
----LEFT JOIN CM_KHOI_DT ckd ON ck.KHOI_ID = ckd.KHOI_ID
--WHERE cd.DEP_ID=@p_DEP_ID

DECLARE @REQ_ID_Temp TABLE(REQ_ID VARCHAR(20))
INSERT INTO @REQ_ID_Temp
SELECT A.REQ_ID FROM TR_REQUEST_SHOP_DOC A
LEFT JOIN PL_REQUEST_PROCESS B ON A.REQ_ID = B.REQ_ID AND B.STATUS = 'C'
WHERE (EXISTS(SELECT RL.ROLE_USER FROM @ROLE_LOGIN RL WHERE RL.BRANCH_ID = B.BRANCH_ID
    AND (B.BRANCH_ID = 'DV0001' AND B.DEP_ID = RL.DEP_ID OR B.BRANCH_ID <> 'DV0001')
    AND RL.ROLE_USER = B.ROLE_USER)
  OR EXISTS(SELECT * FROM @ROLE_LOGIN RL
    JOIN PL_REQUEST_PROCESS C ON C.REQ_ID = A.REQ_ID AND C.STATUS = 'P' AND RL.BRANCH_ID = C.BRANCH_ID
    AND (C.BRANCH_ID = 'DV0001' AND C.DEP_ID = RL.DEP_ID OR C.BRANCH_ID <> 'DV0001')
    AND RL.ROLE_USER = C.ROLE_USER)
  OR A.MAKER_ID = @p_USERNAME)
GROUP BY A.REQ_ID

	IF((EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_SLAS' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))))
	BEGIN
		IF(@p_TOP IS NULL OR @p_TOP = '' OR @p_TOP = 0)
			BEGIN
			-- PAGING BEGIN
				SELECT A.REQ_ID,
					   A.REQ_CODE,
					   A.REQ_NAME,
					   --ISNULL(PRP.APPROVE_DT,PRP2.APPROVE_DT) AS REQ_DT,
                       A.REQ_DT,
					   A.REQ_TYPE,
					   A.REQ_CONTENT,
					   A.TOTAL_AMT,
					   A.NOTES,
					   A.RECORD_STATUS,
					   A.MAKER_ID,
					   A.CREATE_DT,
					   A.AUTH_STATUS,
					   A.CHECKER_ID,
					   A.APPROVE_DT,
					   A.BRANCH_ID,
                       CASE WHEN A.REQ_TYPE = 'CPTS' AND A.IS_DONE = '1' AND A.STATUS = 'DONE' THEN 'APPROVE' ELSE A.STATUS END AS STATUS,
					   A.DEP_ID,
					   A.HO_NOTES, K.CONTENT AS REQ_TYPE_NAME, 
                       --I.CONTENT AS REQ_STATUS_NAME,
                       CASE WHEN A.IS_DONE = '1' AND A.STATUS = 'APPROVE' THEN N'Hoàn tất' 
                       ELSE I.CONTENT
                       END REQ_STATUS_NAME,
						 @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE,
             CASE 
             	WHEN A.DEP_ID IS NOT NULL AND A.DEP_ID <> '' THEN G.BRANCH_NAME + ' - ' + CD.DEP_NAME
             	 ELSE G.BRANCH_NAME
             END AS BRANCH_NAME,
						--CASE WHEN  RQT.STATUS = 'C' OR RQT.STATUS = 'U' THEN N'Chờ duyệt' 
						--ELSE N'Đã duyệt' END AS AUTH_STATUS_NAME
						D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME,
            I.CONTENT AS REQ_TYPE_NAME,
             CASE WHEN A.SEND_APP_DT IS NOT NULL AND A.SEND_APP_DT <> '' AND A.AUTH_STATUS <> 'A' AND A.AUTH_STATUS <> 'R' 
                      AND A.AUTH_STATUS <> 'E' AND DATEDIFF(DAY,GETDATE(),DATEADD(DAY,CONVERT(INT,SP.ParaValue),A.SEND_APP_DT)) < 0 THEN '#f2dede' ELSE ''
            END AS COLOR
						--D.AUTH_STATUS_NAME 
				-- SELECT END
				FROM TR_REQUEST_SHOP_DOC A

				LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS 
				LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
				LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQASSTYPE' AND K.CDVAL = A.REQ_TYPE
				LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS] AND I.CDTYPE = @p_CDTYPE_PYC
--        LEFT JOIN PL_REQUEST_PROCESS prp1 ON prp1.REQ_ID = A.REQ_ID AND prp1.STATUS = 'C'
				--LEFT JOIN dbo.CM_AUTH_STATUS AUTH ON AUTH.AUTH_STATUS = A.AUTH_STATUS
				--LEFT JOIN dbo.PL_REQUEST_PROCESS RQT ON RQT.REQ_ID = A.REQ_ID 
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR (RQT.PROCESS_ID = A.STATUS AND EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))))
				--LEFT JOIN dbo.PL_REQUEST_PROCESS PRP1 ON PRP1.REQ_ID = A.REQ_ID  AND PRP1.PROCESS_ID = 'CVMS_N'
				--LEFT JOIN dbo.PL_REQUEST_PROCESS PRP2 ON PRP2.REQ_ID = A.REQ_ID  AND PRP2.PROCESS_ID = 'DVKD'
				
				LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=A.MAKER_ID
        LEFT JOIN SYS_PARAMETERS SP ON 1=1 AND SP.ParaKey = 'NUM_OF_DAYS_LATE'
        LEFT JOIN CM_DEPARTMENT CD ON A.DEP_ID = CD.DEP_ID
				WHERE 1 = 1
				AND (G.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')
--				AND (CONVERT(DATETIME,@p_DVKD_MANAGE_APP_FROM,103) <= CAST (PRP2.RECEPTION_DT AS DATE)  OR @p_DVKD_MANAGE_APP_FROM IS NULL OR @p_DVKD_MANAGE_APP_FROM = '')
--				AND (DATEADD(day,1,CONVERT(DATETIME,@p_DVKD_MANAGE_APP_TO,103)) > CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO = '')
		        AND (@p_DVKD_MANAGE_APP_FROM IS NULL AND @p_DVKD_MANAGE_APP_TO IS NULL OR
            	@p_DVKD_MANAGE_APP_TO IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) >= (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) 
            	OR  @p_DVKD_MANAGE_APP_FROM IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103)) 
            	OR (CONVERT(DATE, A.REQ_DT, 103)) >=  (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103))
            	)
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))
				--AND ((@p_AUTH_STATUS = 'U' AND (RQT.STATUS = 'C' OR RQT.STATUS = 'U')) OR (@p_AUTH_STATUS = 'A' AND (RQT.STATUS = 'P' OR RQT.STATUS IS NULL))OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = ''))		
				AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' 
            --OR A.REQ_ID LIKE '%' + @p_REQ_CODE + '%'
            OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
				AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
				AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
				AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
				--AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
				
				AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
--				AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
				AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
				AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')		
				AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE = '')
				AND (A.STATUS IN ('NEW','REJECT') AND A.MAKER_ID = @p_USERNAME OR A.STATUS NOT IN ('NEW','REJECT')
            OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
                WHERE US.TLNANME = @p_USERNAME))
        AND (@p_STATUS IS NULL OR @p_STATUS = '' OR A.STATUS = @p_STATUS)
        AND (EXISTS(SELECT 1 FROM @REQ_ID_Temp RL WHERE RL.REQ_ID = A.REQ_ID) 
              OR (@ROLE_KT IN ('GDV','KSV') AND A.AUTH_STATUS = 'A' ) 
              OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
                WHERE US.TLNANME = @p_USERNAME))

				AND A.RECORD_STATUS = '1'
				AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID)
				AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND( TU.TLFullName COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI  OR TU.TLNANME COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI 
				 OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')

				ORDER BY A.REQ_DT DESC
			-- PAGING END
			END
		   ELSE 
		   BEGIN
		   -- PAGING BEGIN
				SELECT TOP(CONVERT(INT,@p_TOP))  A.REQ_ID,
					   A.REQ_CODE,
					   A.REQ_NAME,
					   --ISNULL(PRP.APPROVE_DT,PRP2.APPROVE_DT) AS REQ_DT,
                       A.REQ_DT,
					   A.REQ_TYPE,
					   A.REQ_CONTENT,
					   A.TOTAL_AMT,
					   A.NOTES,
					   A.RECORD_STATUS,
					   A.MAKER_ID,
					   A.CREATE_DT,
					   A.AUTH_STATUS,
					   A.CHECKER_ID,
					   A.APPROVE_DT,
					   A.BRANCH_ID,
                       CASE WHEN A.REQ_TYPE = 'CPTS' AND A.IS_DONE = '1' AND A.STATUS = 'DONE' THEN 'APPROVE' ELSE A.STATUS END AS STATUS,
					   A.DEP_ID,
					   A.HO_NOTES, K.CONTENT AS REQ_TYPE_NAME, 
                       --I.CONTENT AS REQ_STATUS_NAME,
                       CASE WHEN A.IS_DONE = '1' AND A.STATUS = 'APPROVE' THEN N'Hoàn tất' 
                       ELSE I.CONTENT
                       END REQ_STATUS_NAME,
						 @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE,
              CASE 
             	WHEN A.DEP_ID IS NOT NULL AND A.DEP_ID <> '' THEN G.BRANCH_NAME + ' - ' + CD.DEP_NAME
             	 ELSE G.BRANCH_NAME
              END AS BRANCH_NAME,
						--CASE WHEN  RQT.STATUS = 'C' OR RQT.STATUS = 'U' THEN N'Chờ duyệt' 
						--ELSE N'Đã duyệt' END AS AUTH_STATUS_NAME
						D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME,
            I.CONTENT AS REQ_TYPE_NAME,
             CASE WHEN A.SEND_APP_DT IS NOT NULL AND A.SEND_APP_DT <> '' AND A.AUTH_STATUS <> 'A' AND A.AUTH_STATUS <> 'R' 
                      AND A.AUTH_STATUS <> 'E' AND DATEDIFF(DAY,GETDATE(),DATEADD(DAY,CONVERT(INT,SP.ParaValue),A.SEND_APP_DT)) < 0 THEN '#f2dede' ELSE ''
            END AS COLOR
						--D.AUTH_STATUS_NAME 
				-- SELECT END
				FROM TR_REQUEST_SHOP_DOC A

				LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS 
				LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
				LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQASSTYPE' AND K.CDVAL = A.REQ_TYPE
				LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS] AND I.CDTYPE = @p_CDTYPE_PYC
--        LEFT JOIN PL_REQUEST_PROCESS prp1 ON prp1.REQ_ID = A.REQ_ID AND prp1.STATUS = 'C'
				 
				--LEFT JOIN dbo.CM_AUTH_STATUS AUTH ON AUTH.AUTH_STATUS = A.AUTH_STATUS
				--LEFT JOIN dbo.PL_REQUEST_PROCESS RQT ON RQT.REQ_ID = A.REQ_ID 
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR (RQT.PROCESS_ID = A.STATUS AND EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))))
				--LEFT JOIN dbo.PL_REQUEST_PROCESS PRP1 ON PRP1.REQ_ID = A.REQ_ID  AND PRP1.PROCESS_ID = 'CVMS_N'
				LEFT JOIN dbo.PL_REQUEST_PROCESS PRP2 ON PRP2.REQ_ID = A.REQ_ID  AND PRP2.PROCESS_ID = 'DVKD'
				
				LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=A.MAKER_ID
        LEFT JOIN SYS_PARAMETERS SP ON 1=1 AND SP.ParaKey = 'NUM_OF_DAYS_LATE'
        LEFT JOIN CM_DEPARTMENT CD ON A.DEP_ID = CD.DEP_ID
				WHERE 1 = 1
				AND (G.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')
			--	AND (CONVERT(DATETIME,@p_DVKD_MANAGE_APP_FROM,103) <= CAST (PRP2.RECEPTION_DT AS DATE)  OR @p_DVKD_MANAGE_APP_FROM IS NULL OR @p_DVKD_MANAGE_APP_FROM = '')
			--	AND (DATEADD(day,1,CONVERT(DATETIME,@p_DVKD_MANAGE_APP_TO,103)) > CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO = '')
		        AND (@p_DVKD_MANAGE_APP_FROM IS NULL AND @p_DVKD_MANAGE_APP_TO IS NULL OR
            	@p_DVKD_MANAGE_APP_TO IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) >= (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) 
            	OR  @p_DVKD_MANAGE_APP_FROM IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103)) 
            	OR (CONVERT(DATE, A.REQ_DT, 103)) >=  (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103))
            	)
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))
				--AND ((@p_AUTH_STATUS = 'U' AND (RQT.STATUS = 'C' OR RQT.STATUS = 'U')) OR (@p_AUTH_STATUS = 'A' AND (RQT.STATUS = 'P' OR RQT.STATUS IS NULL))OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = ''))		
				AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' 
            --OR A.REQ_ID LIKE '%' + @p_REQ_CODE + '%'
            OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
        AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
				AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
				AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
				--AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
				
				AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
--				AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
				AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
				AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')		
				AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE = '')
				AND (A.STATUS IN ('NEW','REJECT') AND A.MAKER_ID = @p_USERNAME OR A.STATUS NOT IN ('NEW','REJECT')
            OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
                WHERE US.TLNANME = @p_USERNAME))
        AND (@p_STATUS IS NULL OR @p_STATUS = '' OR A.STATUS = @p_STATUS)
        AND (EXISTS(SELECT 1 FROM @REQ_ID_Temp RL WHERE RL.REQ_ID = A.REQ_ID) 
              OR (@ROLE_KT IN ('GDV','KSV') AND A.AUTH_STATUS = 'A' ) 
              OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
                WHERE US.TLNANME = @p_USERNAME))
				AND A.RECORD_STATUS = '1'
				AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID)
				AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND( TU.TLFullName COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI  OR TU.TLNANME COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI 
				 OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
				ORDER BY A.REQ_DT DESC
			 -- PAGING END 
		END
	END
	ELSE
	BEGIN
		IF(@p_TOP IS NULL OR @p_TOP = '' OR @p_TOP = 0)
			BEGIN
			-- PAGING BEGIN
				SELECT  A.REQ_ID,
					   A.REQ_CODE,
					   A.REQ_NAME,
					   --ISNULL(PRP.APPROVE_DT,PRP2.APPROVE_DT) AS REQ_DT,
                       A.REQ_DT,
					   A.REQ_TYPE,
					   A.REQ_CONTENT,
					   A.TOTAL_AMT,
					   A.NOTES,
					   A.RECORD_STATUS,
					   A.MAKER_ID,
					   A.CREATE_DT,
					   A.AUTH_STATUS,
					   A.CHECKER_ID,
					   A.APPROVE_DT,
					   A.BRANCH_ID,
                       CASE WHEN A.REQ_TYPE = 'CPTS' AND A.IS_DONE = '1' AND A.STATUS = 'DONE' THEN 'APPROVE' ELSE A.STATUS END AS STATUS,
					   A.DEP_ID,
					   A.HO_NOTES, K.CONTENT AS REQ_TYPE_NAME, 
                       --I.CONTENT AS REQ_STATUS_NAME,
                       CASE WHEN A.IS_DONE = '1' AND A.STATUS = 'APPROVE' THEN N'Hoàn tất' 
                       ELSE I.CONTENT
                       END REQ_STATUS_NAME,
						 @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE,
              CASE 
             	WHEN A.DEP_ID IS NOT NULL AND A.DEP_ID <> '' THEN G.BRANCH_NAME + ' - ' + CD.DEP_NAME
             	 ELSE G.BRANCH_NAME
              END AS BRANCH_NAME,
						--CASE WHEN  RQT.STATUS = 'C' OR RQT.STATUS = 'U' THEN N'Chờ duyệt' 
						--ELSE N'Đã duyệt' END AS AUTH_STATUS_NAME
						D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME,
            CASE WHEN A.SEND_APP_DT IS NOT NULL AND A.SEND_APP_DT <> '' AND A.AUTH_STATUS <> 'A' AND A.AUTH_STATUS <> 'R' 
                  AND DATEDIFF(DAY,GETDATE(),DATEADD(DAY,CONVERT(INT,SP.ParaValue),A.SEND_APP_DT)) < 0 THEN '#f2dede' ELSE ''
            END AS COLOR
						--D.AUTH_STATUS_NAME 
				-- SELECT END
				FROM TR_REQUEST_SHOP_DOC A

				LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
				LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
				LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQASSTYPE' AND K.CDVAL = A.REQ_TYPE
				LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS] AND I.CDTYPE = @p_CDTYPE_PYC
--        LEFT JOIN PL_REQUEST_PROCESS prp1 ON prp1.REQ_ID = A.REQ_ID AND prp1.STATUS = 'C'		
				 
				--LEFT JOIN dbo.CM_AUTH_STATUS AUTH ON AUTH.AUTH_STATUS = A.AUTH_STATUS
				--LEFT JOIN dbo.PL_REQUEST_PROCESS RQT ON RQT.REQ_ID = A.REQ_ID 
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR (RQT.PROCESS_ID = A.STATUS AND EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))))
				--LEFT JOIN dbo.PL_REQUEST_PROCESS PRP1 ON PRP1.REQ_ID = A.REQ_ID  AND PRP1.PROCESS_ID = 'CVMS_N'
				LEFT JOIN dbo.PL_REQUEST_PROCESS PRP2 ON PRP2.REQ_ID = A.REQ_ID  AND PRP2.PROCESS_ID = 'DVKD'
				
				LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=A.MAKER_ID
        LEFT JOIN SYS_PARAMETERS SP ON 1=1 AND SP.ParaKey = 'NUM_OF_DAYS_LATE'
        LEFT JOIN CM_DEPARTMENT CD ON A.DEP_ID = CD.DEP_ID
				WHERE 1 = 1
				AND (G.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')
--				AND (CONVERT(DATETIME,@p_DVKD_MANAGE_APP_FROM,103) <= CAST (PRP2.RECEPTION_DT AS DATE)  OR @p_DVKD_MANAGE_APP_FROM IS NULL OR @p_DVKD_MANAGE_APP_FROM = '')
--				AND (DATEADD(day,1,CONVERT(DATETIME,@p_DVKD_MANAGE_APP_TO,103)) > CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO = '')
		        AND (@p_DVKD_MANAGE_APP_FROM IS NULL AND @p_DVKD_MANAGE_APP_TO IS NULL OR
            	@p_DVKD_MANAGE_APP_TO IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) >= (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) 
            	OR  @p_DVKD_MANAGE_APP_FROM IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103)) 
            	OR (CONVERT(DATE, A.REQ_DT, 103)) >=  (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103))
            	)
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))
				--AND ((@p_AUTH_STATUS = 'U' AND (RQT.STATUS = 'C' OR RQT.STATUS = 'U')) OR (@p_AUTH_STATUS = 'A' AND (RQT.STATUS = 'P' OR RQT.STATUS IS NULL))OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = ''))		
				AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' 
            --OR A.REQ_ID LIKE '%' + @p_REQ_CODE + '%'
            OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
				AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
				AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
				AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
				--AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
				
				AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
--				AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
				AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
				AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')		
				AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE = '')
				AND (A.STATUS IN ('NEW','REJECT') AND A.MAKER_ID = @p_USERNAME OR A.STATUS NOT IN ('NEW','REJECT')
            OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
                WHERE US.TLNANME = @p_USERNAME))
        AND (@p_STATUS IS NULL OR @p_STATUS = '' OR A.STATUS = @p_STATUS)
        AND (EXISTS(SELECT 1 FROM @REQ_ID_Temp RL WHERE RL.REQ_ID = A.REQ_ID) 
              OR (@ROLE_KT IN ('GDV','KSV') AND A.AUTH_STATUS = 'A' ) 
              OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
                WHERE US.TLNANME = @p_USERNAME))

				AND A.RECORD_STATUS = '1'
				AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID)
				AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND( TU.TLFullName COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI  OR TU.TLNANME COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI 
				 OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
				ORDER BY  A.REQ_DT  DESC
			-- PAGING END
			END
		   ELSE 
		   BEGIN
		   -- PAGING BEGIN
				SELECT TOP(CONVERT(INT,@p_TOP))  A.REQ_ID,
					   A.REQ_CODE,
					   A.REQ_NAME,
					   --ISNULL(PRP.APPROVE_DT,PRP2.APPROVE_DT) AS REQ_DT,
                       A.REQ_DT,
					   A.REQ_TYPE,
					   A.REQ_CONTENT,
					   A.TOTAL_AMT,
					   A.NOTES,
					   A.RECORD_STATUS,
					   A.MAKER_ID,
					   A.CREATE_DT,
					   A.AUTH_STATUS,
					   A.CHECKER_ID,
					   A.APPROVE_DT,
					   A.BRANCH_ID,
                       CASE WHEN A.REQ_TYPE = 'CPTS' AND A.IS_DONE = '1' AND A.STATUS = 'DONE' THEN 'APPROVE' ELSE A.STATUS END AS STATUS,
					   A.DEP_ID,
					   A.HO_NOTES, K.CONTENT AS REQ_TYPE_NAME, 
                       --I.CONTENT AS REQ_STATUS_NAME,
                       CASE WHEN A.IS_DONE = '1' AND A.STATUS = 'APPROVE' THEN N'Hoàn tất' 
                       ELSE I.CONTENT
                       END REQ_STATUS_NAME,
						 @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE,
              CASE 
             	WHEN A.DEP_ID IS NOT NULL AND A.DEP_ID <> '' THEN G.BRANCH_NAME + ' - ' + CD.DEP_NAME
             	 ELSE G.BRANCH_NAME
              END AS BRANCH_NAME,
						--CASE WHEN  RQT.STATUS = 'C' OR RQT.STATUS = 'U' THEN N'Chờ duyệt' 
						--ELSE N'Đã duyệt' END AS AUTH_STATUS_NAME
						D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME,
            CASE WHEN A.SEND_APP_DT IS NOT NULL AND A.SEND_APP_DT <> '' AND A.AUTH_STATUS <> 'A' AND A.AUTH_STATUS <> 'R' 
                  AND DATEDIFF(DAY,GETDATE(),DATEADD(DAY,CONVERT(INT,SP.ParaValue),A.SEND_APP_DT)) < 0 THEN '#f2dede' ELSE ''
            END AS COLOR
						--D.AUTH_STATUS_NAME 
				-- SELECT END
				FROM TR_REQUEST_SHOP_DOC A

				LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
				LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
				LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQASSTYPE' AND K.CDVAL = A.REQ_TYPE
				LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS] AND I.CDTYPE = @p_CDTYPE_PYC
--        LEFT JOIN PL_REQUEST_PROCESS prp1 ON prp1.REQ_ID = A.REQ_ID AND prp1.STATUS = 'C'		
				 
				--LEFT JOIN dbo.CM_AUTH_STATUS AUTH ON AUTH.AUTH_STATUS = A.AUTH_STATUS
				--LEFT JOIN dbo.PL_REQUEST_PROCESS RQT ON RQT.REQ_ID = A.REQ_ID 
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR (RQT.PROCESS_ID = A.STATUS AND EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))))
				--LEFT JOIN dbo.PL_REQUEST_PROCESS PRP1 ON PRP1.REQ_ID = A.REQ_ID  AND PRP1.PROCESS_ID = 'CVMS_N'
				LEFT JOIN dbo.PL_REQUEST_PROCESS PRP2 ON PRP2.REQ_ID = A.REQ_ID  AND PRP2.PROCESS_ID = 'DVKD'
				
				LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=A.MAKER_ID
        LEFT JOIN SYS_PARAMETERS SP ON 1=1 AND SP.ParaKey = 'NUM_OF_DAYS_LATE'
        LEFT JOIN CM_DEPARTMENT CD ON A.DEP_ID = CD.DEP_ID
				WHERE 1 = 1
				AND (G.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')
				--AND (CONVERT(DATETIME,@p_DVKD_MANAGE_APP_FROM,103) <= CAST (PRP2.RECEPTION_DT AS DATE)  OR @p_DVKD_MANAGE_APP_FROM IS NULL OR @p_DVKD_MANAGE_APP_FROM = '')
				--AND (DATEADD(day,1,CONVERT(DATETIME,@p_DVKD_MANAGE_APP_TO,103)) > CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO = '')
		        AND (@p_DVKD_MANAGE_APP_FROM IS NULL AND @p_DVKD_MANAGE_APP_TO IS NULL OR
            	@p_DVKD_MANAGE_APP_TO IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) >= (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) 
            	OR  @p_DVKD_MANAGE_APP_FROM IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103)) 
            	OR (CONVERT(DATE, A.REQ_DT, 103)) >=  (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103))
            	)
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))
				--AND ((@p_AUTH_STATUS = 'U' AND (RQT.STATUS = 'C' OR RQT.STATUS = 'U')) OR (@p_AUTH_STATUS = 'A' AND (RQT.STATUS = 'P' OR RQT.STATUS IS NULL))OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = ''))		
				AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' 
            --OR A.REQ_ID LIKE '%' + @p_REQ_CODE + '%'
            OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
				AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
				AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
				AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
				--AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
				
				AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
--				AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
				AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
				AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')		
				AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE = '')
				AND (A.STATUS IN ('NEW','REJECT') AND A.MAKER_ID = @p_USERNAME OR A.STATUS NOT IN ('NEW','REJECT') 
            OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
                WHERE US.TLNANME = @p_USERNAME))
        AND (@p_STATUS IS NULL OR @p_STATUS = '' OR A.STATUS = @p_STATUS)
        AND (EXISTS(SELECT 1 FROM @REQ_ID_Temp RL WHERE RL.REQ_ID = A.REQ_ID) 
              OR (@ROLE_KT IN ('GDV','KSV') AND A.AUTH_STATUS = 'A' ) 
              OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
                WHERE US.TLNANME = @p_USERNAME))

				AND A.RECORD_STATUS = '1'
				AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID)
				AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND( TU.TLFullName COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI  OR TU.TLNANME COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI 
				 OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
				ORDER BY A.REQ_DT  DESC
			 -- PAGING END 
		END
	END
	
		
		
   END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
-- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.
IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1
BEGIN
    DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)
    SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')
    SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'
    EXECUTE sys.xp_logevent 55000, @eventMessage
END
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
	PRINT 'The database update failed'
END
GO