ALTER PROCEDURE [dbo].[RET_MASTER_Search]
@p_RET_ID	varchar(15)  = NULL,
@p_ASSET_ID VARCHAR(15) = NULL,
@P_ASSET_CODE VARCHAR(15) = NULL,
@P_DIV_ID VARCHAR(15) = NULL,
@P_DIV_CODE VARCHAR(15) = NULL,
@P_OWNER VARCHAR(50) = NULL,
@p_LENGTH	DECIMAL(18,2) = NULL,
@p_WIDTH	DECIMAL(18,2) = NULL,
@p_CURRENT_STATE	nvarchar(100)  = NULL,
@p_RET_TYPE	varchar(15)  = NULL,
@p_STATUS	varchar(15)  = NULL,
@p_LAND_SQUARE	DECIMAL(18,2)  = NULL,
@p_CONSTRUCT_SQUARE	DECIMAL(18,2)  = NULL,
@p_TOTAL_SQUARE	DECIMAL(18,2)  = NULL,
@p_BOUNDARY	nvarchar(100)  = NULL,
@p_HOUSEDES	nvarchar(100)  = NULL,
@p_PURPOSE_IN_USE	nvarchar(100)  = NULL,
@P_W_USE_CON NVARCHAR(100) = NULL,
@p_USE_STATUS	varchar(15)  = NULL,
@p_CONST_STATUS	varchar(15)  = NULL,
@p_OWNER_TYPE	varchar(15)  = NULL,
@p_USE_PERIOD	int  = NULL,
@p_PERSON_HOLDER	nvarchar(100)  = NULL,
@p_NOTES	nvarchar(100)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_AUTH_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(15)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_CHECKER_ID	varchar(15)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_TOP	INT = 300,
------------------BAODNQ 16/2/2022: Thêm tham số------------
@p_USER_LOGIN VARCHAR(15),
@p_BRANCH_ID VARCHAR(15) = NULL,
@p_RET_FORM VARCHAR(15) = NULL,
@p_ASSET_STATUS VARCHAR(15) = NULL,
@p_ADDR NVARCHAR(1000) = NULL,
@p_RET_SEARCH_TYPE VARCHAR(20) = NULL 
AS
	--Validation is here
/*
	DECLARE @ERRORSYS NVARCHAR(15) = '' 
	IF ( [NOT] EXISTS ( SELECT * FROM [TABLE] WHERE  [CONDITION] ))
		 SET @ERRORSYS = ''
	IF @ERRORSYS <> '' 
	BEGIN
		SELECT ErrorCode Result, ''  CAR_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
		RETURN '0'
	END */

BEGIN -- PAGING

--DECLARE @p_USER_LOGIN_ROLE VARCHAR(50) = (SELECT RoleName FROM TL_USER WHERE TLNANME = @p_USER_LOGIN)
DECLARE @t_USER_LOGIN_ROLE_TABLE TABLE (BRANCH_ID VARCHAR(20), DEPT_ID VARCHAR(15), ROLE_ID VARCHAR(20))

INSERT INTO @t_USER_LOGIN_ROLE_TABLE(BRANCH_ID, DEPT_ID, ROLE_ID)
	SELECT * FROM [dbo].[TL_USER_GET_ROLES](@p_USER_LOGIN)

DECLARE @p_BRANCH_LOGIN VARCHAR(15) = (SELECT TLSUBBRID FROM TL_USER WHERE TLNANME = @p_USER_LOGIN), 
		@p_DEP_LOGIN VARCHAR(15) = (SELECT DEP_ID FROM TL_USER WHERE TLNANME = @p_USER_LOGIN)

IF(@p_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)
-- PAGING BEGIN
SELECT A.*,(CASE WHEN USE_PERIOD = 0 THEN N'Lâu dài' ELSE convert(varchar(100),USE_PERIOD_DT, 103) END) AS USE_PERIOD_NAME, B.AUTH_STATUS_NAME, C.CONTENT, 
--CASE 
--	WHEN BRU.BRANCH_TYPE = 'HS' THEN DP.DEP_NAME 
--	ELSE BRU.BRANCH_NAME 
--END AS BRANCH_NAME, -- ĐV sử dụng
BRU.BRANCH_NAME AS BRANCH_USE_NAME, -- ĐV sử dụng
G.ADDR as ASS_ADDR,
G.DIV_ID,G.DIV_CODE,G.DIV_NAME,E.CONTENT AS RET_STATUS, AST.TYPE_NAME AS ASSET_TYPE, ASG.GROUP_NAME AS ASS_GROUP,
AAS.STATUS_NAME AS ASSET_STATUS_NAME,
D.BRANCH_ID,
BRC.BRANCH_NAME AS BRANCH_CREATE_NAME, -- ĐV quản lý
TUM.TLFullName AS MAKER_NAME,
-----------BAODNQ 15/3/2022 Lấy thông tin xuất excel
C.CONTENT AS RET_TYPE_NAME, --loại BDS
UF.CONTENT AS USE_FORM_NAME, -- hình thức sử dụng
US.CONTENT AS USE_STATUS_NAME, --tình trạng sử dụng đất
CS.CONTENT AS CONST_STATUS_NAME, --tình trạng xây dựng
E.CONTENT AS RET_STATUS_NAME, --hiện trạng pháp lý gắn liền vs đất
OT.CONTENT AS OWNER_TYPE_NAME, -- loại sỡ hữu
----------BAODNQ 18/4/2022: Check điều kiện khi lấy thông tin tài sản-----
CASE
	WHEN A.ASSET_ID IS NULL OR A.ASSET_ID = '' THEN A.ASS_NAME
	ELSE D.ASSET_NAME
END AS ASSET_NAME,
CASE
	WHEN A.ASSET_ID IS NULL OR A.ASSET_ID = '' THEN A.DESCRIPTION
	ELSE D.ASSET_DESC
END AS ASSET_DESC,
CASE
	WHEN A.ASSET_ID IS NULL OR A.ASSET_ID = '' THEN A.ASSET_PRICE
	ELSE D.BUY_PRICE
END AS BUY_PRICE
-------------------------END BAODNQ---------------
-- SELECT END

FROM RET_MASTER A 
INNER JOIN CM_AUTH_STATUS B ON B.AUTH_STATUS = A.AUTH_STATUS
--INNER JOIN CM_ALLCODE C ON C.CDVAL = A.RET_TYPE AND C.CDNAME = 'RET_TYPE'AND C.CDTYPE = 'RET'
INNER JOIN CM_ALLCODE C ON C.CDVAL = A.RET_TYPE AND C.CDNAME = 'RET_TYPE_2'AND C.CDTYPE = 'RET'
INNER JOIN CM_ALLCODE E ON E.CDVAL = A.[STATUS] AND E.CDNAME = 'RET_STATUS' AND E.CDTYPE = 'RET'
LEFT JOIN ASS_MASTER D ON D.ASSET_ID = A.ASSET_ID
LEFT JOIN CM_DIVISION G ON G.DIV_ID=D.DIVISION_ID
LEFT JOIN CM_BRANCH BRC ON D.BRANCH_CREATE = BRC.BRANCH_ID -- ĐV quản lý
--LEFT JOIN CM_BRANCH BRU ON D.BRANCH_ID = BRU.BRANCH_ID -- ĐV sử dụng
LEFT JOIN CM_BRANCH BRU ON A.BRANCH_USE_ID = BRU.BRANCH_ID -- ĐV sử dụng
LEFT JOIN CM_DEPARTMENT DP ON DP.DEP_ID = D.DEPT_ID
--LEFT JOIN ASS_MASTER AMS ON A.ASSET_ID = AMS.ASSET_ID
LEFT JOIN ASS_TYPE AST ON D.TYPE_ID = AST.TYPE_ID
LEFT JOIN ASS_GROUP ASG ON D.GROUP_ID = ASG.GROUP_ID
LEFT JOIN ASS_AMORT_STATUS AAS ON D.AMORT_STATUS = AAS.STATUS_CODE
LEFT JOIN TL_USER TUM ON A.MAKER_ID = TUM.TLNANME
----------------------BAODNQ 15/3/2022: Lấy thông tin xuất excel-----
LEFT JOIN CM_ALLCODE UF ON A.USE_FORM = UF.CDVAL AND UF.CDNAME = 'USE_FORM' AND UF.CDTYPE = 'RET'
LEFT JOIN CM_ALLCODE US ON A.USE_STATUS = US.CDVAL AND US.CDNAME = 'USE_STATUS' AND US.CDTYPE = 'RET'
LEFT JOIN CM_ALLCODE CS ON A.CONST_STATUS = CS.CDVAL AND CS.CDNAME = 'CONST_STATUS' AND CS.CDTYPE = 'RET'
LEFT JOIN CM_ALLCODE OT ON A.OWNER_TYPE = OT.CDVAL AND OT.CDNAME = 'OWNER_TYPE' AND OT.CDTYPE = 'RET'


 WHERE 1 = 1
	AND (A.RET_ID LIKE '%' + @p_RET_ID + '%' OR  @p_RET_ID IS NULL OR @p_RET_ID = '')
	AND (A.ASSET_ID LIKE '%' + @p_ASSET_ID + '%' OR  @p_ASSET_ID IS NULL OR @p_ASSET_ID = '')
	--AND (A.ASSET_CODE LIKE '%' + @P_ASSET_CODE + '%' OR  @P_ASSET_CODE IS NULL OR @P_ASSET_CODE = '')
	AND (D.ASSET_CODE LIKE '%' + @P_ASSET_CODE + '%' OR  @P_ASSET_CODE IS NULL OR @P_ASSET_CODE = '')
	AND (A.OWNER LIKE '%' + @P_OWNER + '%' OR  @P_OWNER IS NULL OR @P_OWNER = '')
	AND (A.LENGTH = @p_LENGTH OR  @p_LENGTH IS NULL)
	AND (A.WIDTH = @p_WIDTH OR  @p_WIDTH IS NULL)
	AND (A.CURRENT_STATE LIKE '%' + @p_CURRENT_STATE + '%' OR  @p_CURRENT_STATE IS NULL OR @p_CURRENT_STATE = '')
	AND (A.RET_TYPE LIKE '%' + @p_RET_TYPE + '%' OR  @p_RET_TYPE IS NULL OR @p_RET_TYPE = '')
	AND (A.STATUS LIKE '%' + @p_STATUS + '%' OR  @p_STATUS IS NULL OR @p_STATUS = '')
	AND (A.LAND_SQUARE = @p_LAND_SQUARE OR  @p_LAND_SQUARE IS NULL)
	AND (A.CONSTRUCT_SQUARE = @p_CONSTRUCT_SQUARE OR  @p_CONSTRUCT_SQUARE IS NULL)
	AND (A.TOTAL_SQUARE = @p_TOTAL_SQUARE OR  @p_TOTAL_SQUARE IS NULL)
	AND (A.BOUNDARY LIKE '%' + @p_BOUNDARY + '%' OR  @p_BOUNDARY IS NULL OR @p_BOUNDARY = '')
	AND (A.HOUSEDES LIKE '%' + @p_HOUSEDES + '%' OR  @p_HOUSEDES IS NULL OR @p_HOUSEDES = '')
	AND (A.PURPOSE_IN_USE LIKE '%' + @p_PURPOSE_IN_USE + '%' OR  @p_PURPOSE_IN_USE IS NULL OR @p_PURPOSE_IN_USE = '')
	AND (A.W_USE_CON LIKE '%' + @P_W_USE_CON + '%' OR  @P_W_USE_CON IS NULL OR @P_W_USE_CON = '')
	AND (A.USE_STATUS LIKE '%' + @p_USE_STATUS + '%' OR  @p_USE_STATUS IS NULL OR @p_USE_STATUS = '')
	AND (A.CONST_STATUS LIKE '%' + @p_CONST_STATUS + '%' OR  @p_CONST_STATUS IS NULL OR @p_CONST_STATUS = '')
	AND (A.OWNER_TYPE LIKE '%' + @p_OWNER_TYPE + '%' OR  @p_OWNER_TYPE IS NULL OR @p_OWNER_TYPE = '')
	AND (A.USE_PERIOD = @p_USE_PERIOD OR  @p_USE_PERIOD IS NULL)
	AND (A.PERSON_HOLDER LIKE '%' + @p_PERSON_HOLDER + '%' OR  @p_PERSON_HOLDER IS NULL OR @p_PERSON_HOLDER = '')
	AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR  @p_NOTES IS NULL OR @p_NOTES = '')
	AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR  @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
	AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
	AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR  @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
	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.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.RECORD_STATUS = '1')
	AND (G.DIV_ID LIKE '%' + @P_DIV_ID + '%' OR @P_DIV_ID = '' OR @P_DIV_ID IS NULL)
	AND (G.DIV_CODE LIKE '%' + @P_DIV_CODE + '%' OR @P_DIV_CODE = '' OR @P_DIV_CODE IS NULL)
	AND (
			D.BRANCH_CREATE = @p_BRANCH_ID 
			OR A.BRANCH_CREATE = @p_BRANCH_ID
			OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = ''
		)
	--AND (A.BRANCH_CREATE = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
	AND (A.RET_FORM LIKE '%' + @p_RET_FORM + '%' OR @p_RET_FORM IS NULL OR @p_RET_FORM = '')
	AND (A.ADDR LIKE '%' + @p_ADDR + '%' OR @p_ADDR IS NULL OR @p_ADDR = '')
	AND (
			(@p_ASSET_STATUS = 'DTL' AND D.AMORT_STATUS LIKE '%' + @p_ASSET_STATUS + '%') 
			OR (@p_ASSET_STATUS = 'BT' AND D.AMORT_STATUS NOT LIKE '%DTL%')
			OR (@p_ASSET_STATUS IS NULL OR @p_ASSET_STATUS = '')
		)
	AND (
			(@p_RET_SEARCH_TYPE = 'IS_ASSET' AND A.ASSET_ID IS NOT NULL) -- Tìm kiếm BDS đã là tài sản NH
			OR (@p_RET_SEARCH_TYPE = 'NOT_ASSET' AND A.ASSET_ID IS NULL) -- Tìm kiếm BDS chưa là tài sản NH (đang hoàn thiện pháp lý)
			OR (@p_RET_SEARCH_TYPE IS NULL) -- Tìm toàn bộ
		)
	-------BAODNQ 16/2/2022: Thêm điều kiện search-------
		--AND	(A.MAKER_ID = @p_USER_LOGIN		---- user login là ng tạo
		--	OR (A.SIGN_USER = @p_USER_LOGIN AND A.IS_SEND_APPR = 'Y') --- user login là cấp duyệt trung gian và đã gửi YC phê duyệt
		--	OR	(	
		--			--user login là trưởng ĐV và đã gửi YC phê duyệt
		--			(
		--				------user login là trưởng ĐV ở hội sở--------
		--				(
		--					EXISTS(SELECT * FROM CM_BRANCH WHERE BRANCH_ID = A.BRANCH_CREATE AND BRANCH_TYPE = 'HS')
		--					AND (SELECT DEP_ID FROM TL_USER WHERE TLNANME = A.MAKER_ID) 
		--						= (SELECT TOP 1 DEPT_ID FROM @t_USER_LOGIN_ROLE_TABLE WHERE ROLE_ID IN ('GDDV', 'TBP', 'TP', 'PP'))
		--				)
		--				------user login là trưởng ĐV ở CN/PGD
		--				OR
		--				(
		--					NOT EXISTS(SELECT * FROM CM_BRANCH WHERE BRANCH_ID = A.BRANCH_CREATE AND BRANCH_TYPE = 'HS')
		--					AND A.BRANCH_CREATE = (SELECT TOP 1 BRANCH_ID FROM @t_USER_LOGIN_ROLE_TABLE WHERE ROLE_ID IN ('GDDV', 'PGD', 'TPGD', 'PPGD'))
		--				)
		--			)
		--			AND A.IS_SEND_APPR = 'Y'
		--			AND	(
		--					ISNULL(A.SIGN_USER,'') = '' OR (ISNULL(A.SIGN_USER,'') <> '' AND A.SIGN_DT IS NOT NULL)
		--				)
			
		--		)
		--)

		-----------BAODNQ: NẾU Ở HỘI SỞ TÌM THẤY NHỮNG PHIẾU THEO PHÒNG BAN------------------
		----------------NẾU Ở DVKD TÌM THẤY PHIẾU THEO ĐƠN VỊ--------------------
		AND(
			A.BRANCH_CREATE = @p_BRANCH_LOGIN
			AND(
				(SELECT DEP_ID FROM TL_USER WHERE TLNANME = A.MAKER_ID) = (SELECT TOP 1 DEPT_ID FROM @t_USER_LOGIN_ROLE_TABLE)
					OR (SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_LOGIN) <> 'HS'
			)
		)

		ORDER BY A.CREATE_DT DESC
	
-- PAGING END
ELSE
-- PAGING BEGIN
SELECT TOP(CONVERT(INT,@P_TOP)) A.*, (CASE WHEN USE_PERIOD = 0 THEN N'Lâu dài' ELSE convert(varchar(100),USE_PERIOD_DT, 103) END) AS USE_PERIOD_NAME,
B.AUTH_STATUS_NAME, C.CONTENT, 
--CASE 
--	WHEN BRU.BRANCH_TYPE = 'HS' THEN DP.DEP_NAME 
--	ELSE BRU.BRANCH_NAME 
--END AS BRANCH_NAME, -- ĐV sử dụng
BRU.BRANCH_NAME AS BRANCH_USE_NAME, -- ĐV sử dụng
G.DIV_ID,G.DIV_CODE,G.DIV_NAME, E.CONTENT AS RET_STATUS,
 AAS.STATUS_NAME AS ASSET_STATUS_NAME,
D.BRANCH_ID,
BRC.BRANCH_NAME AS BRANCH_CREATE_NAME, -- ĐV quản lý
TUM.TLFullName AS MAKER_NAME,
 -----------BAODNQ 15/3/2022 Lấy thông tin xuất excel
C.CONTENT AS RET_TYPE_NAME, --loại BDS
UF.CONTENT AS USE_FORM_NAME, -- hình thức sử dụng
US.CONTENT AS USE_STATUS_NAME, --tình trạng sử dụng đất
CS.CONTENT AS CONST_STATUS_NAME, --tình trạng xây dựng
E.CONTENT AS RET_STATUS_NAME, --hiện trạng pháp lý gắn liền vs đất
OT.CONTENT AS OWNER_TYPE_NAME, -- loại sỡ hữu
----------BAODNQ 18/4/2022: Check điều kiện khi lấy thông tin tài sản-----
CASE
	WHEN A.ASSET_ID IS NULL OR A.ASSET_ID = '' THEN A.ASS_NAME
	ELSE D.ASSET_NAME
END AS ASSET_NAME,
CASE
	WHEN A.ASSET_ID IS NULL OR A.ASSET_ID = '' THEN A.DESCRIPTION
	ELSE D.ASSET_DESC
END AS ASSET_DESC,
CASE
	WHEN A.ASSET_ID IS NULL OR A.ASSET_ID = '' THEN A.ASSET_PRICE
	ELSE D.BUY_PRICE
END AS BUY_PRICE
-------------------------END BAODNQ---------------
 -- SELECT END
FROM RET_MASTER A 
INNER JOIN CM_AUTH_STATUS B ON B.AUTH_STATUS = A.AUTH_STATUS
--INNER JOIN CM_ALLCODE C ON C.CDVAL = A.RET_TYPE AND C.CDNAME = 'RET_TYPE'AND C.CDTYPE = 'RET'
INNER JOIN CM_ALLCODE C ON C.CDVAL = A.RET_TYPE AND C.CDNAME = 'RET_TYPE_2'AND C.CDTYPE = 'RET'
INNER JOIN CM_ALLCODE E ON E.CDVAL = A.[STATUS] AND E.CDNAME = 'RET_STATUS' AND E.CDTYPE = 'RET'
LEFT JOIN ASS_MASTER D ON D.ASSET_ID = A.ASSET_ID
LEFT JOIN CM_DIVISION G ON G.DIV_ID=D.DIVISION_ID
LEFT JOIN CM_BRANCH BRC ON D.BRANCH_CREATE = BRC.BRANCH_ID -- ĐV quản lý
--LEFT JOIN CM_BRANCH BRU ON D.BRANCH_ID = BRU.BRANCH_ID -- ĐV sử dụng
LEFT JOIN CM_BRANCH BRU ON A.BRANCH_USE_ID = BRU.BRANCH_ID -- ĐV sử dụng
LEFT JOIN CM_DEPARTMENT DP ON DP.DEP_ID = D.DEPT_ID
LEFT JOIN ASS_AMORT_STATUS AAS ON D.AMORT_STATUS = AAS.STATUS_CODE
LEFT JOIN TL_USER TUM ON A.MAKER_ID = TUM.TLNANME
----------------------BAODNQ 15/3/2022: Lấy thông tin xuất excel-----
LEFT JOIN CM_ALLCODE UF ON A.USE_FORM = UF.CDVAL AND UF.CDNAME = 'USE_FORM' AND UF.CDTYPE = 'RET'
LEFT JOIN CM_ALLCODE US ON A.USE_STATUS = US.CDVAL AND US.CDNAME = 'USE_STATUS' AND US.CDTYPE = 'RET'
LEFT JOIN CM_ALLCODE CS ON A.CONST_STATUS = CS.CDVAL AND CS.CDNAME = 'CONST_STATUS' AND CS.CDTYPE = 'RET'
LEFT JOIN CM_ALLCODE OT ON A.OWNER_TYPE = OT.CDVAL AND OT.CDNAME = 'OWNER_TYPE' AND OT.CDTYPE = 'RET'

 WHERE 1 = 1
	AND (A.RET_ID LIKE '%' + @p_RET_ID + '%' OR  @p_RET_ID IS NULL OR @p_RET_ID = '')
	AND (A.ASSET_ID LIKE '%' + @p_ASSET_ID + '%' OR  @p_ASSET_ID IS NULL OR @p_ASSET_ID = '')
	--AND (A.ASSET_CODE LIKE '%' + @P_ASSET_CODE + '%' OR  @P_ASSET_CODE IS NULL OR @P_ASSET_CODE = '')
	AND (D.ASSET_CODE LIKE '%' + @P_ASSET_CODE + '%' OR  @P_ASSET_CODE IS NULL OR @P_ASSET_CODE = '')
	AND (A.OWNER LIKE '%' + @P_OWNER + '%' OR  @P_OWNER IS NULL OR @P_OWNER = '')
	AND (A.LENGTH = @p_LENGTH OR  @p_LENGTH IS NULL)
	AND (A.WIDTH = @p_WIDTH OR  @p_WIDTH IS NULL)
	AND (A.CURRENT_STATE LIKE '%' + @p_CURRENT_STATE + '%' OR  @p_CURRENT_STATE IS NULL OR @p_CURRENT_STATE = '')
	AND (A.RET_TYPE LIKE '%' + @p_RET_TYPE + '%' OR  @p_RET_TYPE IS NULL OR @p_RET_TYPE = '')
	AND (A.STATUS LIKE '%' + @p_STATUS + '%' OR  @p_STATUS IS NULL OR @p_STATUS = '')
	AND (A.LAND_SQUARE = @p_LAND_SQUARE OR  @p_LAND_SQUARE IS NULL)
	AND (A.CONSTRUCT_SQUARE = @p_CONSTRUCT_SQUARE OR  @p_CONSTRUCT_SQUARE IS NULL)
	AND (A.TOTAL_SQUARE = @p_TOTAL_SQUARE OR  @p_TOTAL_SQUARE IS NULL)
	AND (A.BOUNDARY LIKE '%' + @p_BOUNDARY + '%' OR  @p_BOUNDARY IS NULL OR @p_BOUNDARY = '')
	AND (A.HOUSEDES LIKE '%' + @p_HOUSEDES + '%' OR  @p_HOUSEDES IS NULL OR @p_HOUSEDES = '')
	AND (A.PURPOSE_IN_USE LIKE '%' + @p_PURPOSE_IN_USE + '%' OR  @p_PURPOSE_IN_USE IS NULL OR @p_PURPOSE_IN_USE = '')
	AND (A.W_USE_CON LIKE '%' + @P_W_USE_CON + '%' OR  @P_W_USE_CON IS NULL OR @P_W_USE_CON = '')
	AND (A.USE_STATUS LIKE '%' + @p_USE_STATUS + '%' OR  @p_USE_STATUS IS NULL OR @p_USE_STATUS = '')
	AND (A.CONST_STATUS LIKE '%' + @p_CONST_STATUS + '%' OR  @p_CONST_STATUS IS NULL OR @p_CONST_STATUS = '')
	AND (A.OWNER_TYPE LIKE '%' + @p_OWNER_TYPE + '%' OR  @p_OWNER_TYPE IS NULL OR @p_OWNER_TYPE = '')
	AND (A.USE_PERIOD = @p_USE_PERIOD OR  @p_USE_PERIOD IS NULL)
	AND (A.PERSON_HOLDER LIKE '%' + @p_PERSON_HOLDER + '%' OR  @p_PERSON_HOLDER IS NULL OR @p_PERSON_HOLDER = '')
	AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR  @p_NOTES IS NULL OR @p_NOTES = '')
	AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR  @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
	AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
	AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR  @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
	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.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.RECORD_STATUS = '1')
	AND (G.DIV_ID LIKE '%' + @P_DIV_ID + '%' OR @P_DIV_ID = '' OR @P_DIV_ID IS NULL)
	AND (G.DIV_CODE LIKE '%' + @P_DIV_CODE + '%' OR @P_DIV_CODE = '' OR @P_DIV_CODE IS NULL)
	AND (
			D.BRANCH_CREATE = @p_BRANCH_ID 
			OR A.BRANCH_CREATE = @p_BRANCH_ID
			OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = ''
		)
	--AND (A.BRANCH_CREATE = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
	AND (A.RET_FORM LIKE '%' + @p_RET_FORM + '%' OR @p_RET_FORM IS NULL OR @p_RET_FORM = '')
	AND (A.ADDR LIKE '%' + @p_ADDR + '%' OR @p_ADDR IS NULL OR @p_ADDR = '')
	AND (
			(@p_ASSET_STATUS = 'DTL' AND D.AMORT_STATUS LIKE '%' + @p_ASSET_STATUS + '%') 
			OR (@p_ASSET_STATUS = 'BT' AND D.AMORT_STATUS NOT LIKE '%DTL%')
			OR (@p_ASSET_STATUS IS NULL OR @p_ASSET_STATUS = '')
		)
	AND (
			(@p_RET_SEARCH_TYPE = 'IS_ASSET' AND A.ASSET_ID IS NOT NULL) -- Tìm kiếm BDS đã là tài sản NH
			OR (@p_RET_SEARCH_TYPE = 'NOT_ASSET' AND A.ASSET_ID IS NULL) -- Tìm kiếm BDS chưa là tài sản NH (đang hoàn thiện pháp lý)
			OR (@p_RET_SEARCH_TYPE IS NULL) -- Tìm toàn bộ
		)
	-------BAODNQ 16/2/2022: Thêm điều kiện search-------
		--AND	(A.MAKER_ID = @p_USER_LOGIN		---- user login là ng tạo
		--	OR (A.SIGN_USER = @p_USER_LOGIN AND A.IS_SEND_APPR = 'Y') --- user login là cấp duyệt trung gian và đã gửi YC phê duyệt
		--	OR	(
		--			((SELECT ROLE_NAME FROM @t_USER_LOGIN_ROLE_TABLE) IN('GDDV','PGD', 'TP', 'PP','TPGD','PPGD', 'TBP')) --- user login là trưởng ĐV và đã gửi YC phê duyệt
		--			AND A.IS_SEND_APPR = 'Y'
		--			AND	(
		--					ISNULL(A.SIGN_USER,'') = '' OR (ISNULL(A.SIGN_USER,'') <> '' AND A.SIGN_DT IS NOT NULL)
		--				)
			
		--		)
		--	)
		--AND	(A.MAKER_ID = @p_USER_LOGIN		---- user login là ng tạo
		--	OR (A.SIGN_USER = @p_USER_LOGIN AND A.IS_SEND_APPR = 'Y') --- user login là cấp duyệt trung gian và đã gửi YC phê duyệt
		--	OR	(	
		--			--user login là trưởng ĐV và đã gửi YC phê duyệt
		--			(
		--				------user login là trưởng ĐV ở hội sở--------
		--				(
		--					EXISTS(SELECT * FROM CM_BRANCH WHERE BRANCH_ID = A.BRANCH_CREATE AND BRANCH_TYPE = 'HS')
		--					AND (SELECT DEP_ID FROM TL_USER WHERE TLNANME = A.MAKER_ID) 
		--						= (SELECT TOP 1 DEPT_ID FROM @t_USER_LOGIN_ROLE_TABLE WHERE ROLE_ID IN ('GDDV', 'TBP', 'TP', 'PP'))
		--				)
		--				------user login là trưởng ĐV ở CN/PGD
		--				OR 
		--				(
		--					NOT EXISTS(SELECT * FROM CM_BRANCH WHERE BRANCH_ID = A.BRANCH_CREATE AND BRANCH_TYPE = 'HS')
		--					AND A.BRANCH_CREATE = (SELECT TOP 1 BRANCH_ID FROM @t_USER_LOGIN_ROLE_TABLE WHERE ROLE_ID IN ('GDDV', 'PGD', 'TPGD', 'PPGD'))
		--				)
		--			)
		--			AND A.IS_SEND_APPR = 'Y'
		--			AND	(
		--					ISNULL(A.SIGN_USER,'') = '' OR (ISNULL(A.SIGN_USER,'') <> '' AND A.SIGN_DT IS NOT NULL)
		--				)
			
		--		)
		--)

		-----------BAODNQ: NẾU Ở HỘI SỞ TÌM THẤY NHỮNG PHIẾU THEO PHÒNG BAN------------------
		----------------NẾU Ở DVKD TÌM THẤY PHIẾU THEO ĐƠN VỊ--------------------
		AND(
			A.BRANCH_CREATE = @p_BRANCH_LOGIN
			AND(
				(SELECT DEP_ID FROM TL_USER WHERE TLNANME = A.MAKER_ID) = (SELECT TOP 1 DEPT_ID FROM @t_USER_LOGIN_ROLE_TABLE)
					OR (SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_LOGIN) <> 'HS'
			)
		)

		ORDER BY A.CREATE_DT DESC
-- PAGING END
END -- PAGING

GO


ALTER PROCEDURE [dbo].[RET_MASTER_SendAppr]
@p_RET_ID VARCHAR(15),
@p_USER_LOGIN VARCHAR(15),
@p_SEND_APPR_DT VARCHAR(20) = NULL

AS

IF(EXISTS (SELECT * FROM RET_MASTER WHERE RET_ID = @p_RET_ID AND IS_SEND_APPR = 'Y'))
BEGIN
	SELECT '-1' as Result, '' RET_ID, '' IS_SEND_APPR,
		N'Gửi phê duyệt thất bại. Thông tin bất động sản đã được gửi yêu cầu phê duyệt trước đó' ErrorDesc
	RETURN '-1'
END

BEGIN TRANSACTION
	
	UPDATE RET_MASTER SET 
		IS_SEND_APPR = 'Y', 
		SEND_APPR_DT = CONVERT(DATETIME, @p_SEND_APPR_DT, 103),
		SIGN_DT = NULL,
		AUTH_STATUS = 'U'
	WHERE RET_ID = @p_RET_ID
	IF @@ERROR <> 0 GOTO ABORT

	DECLARE @p_IS_SEND_APPR VARCHAR(15) = (SELECT IS_SEND_APPR FROM RET_MASTER WHERE RET_ID = @p_RET_ID)
	DECLARE @p_MESSAGE NVARCHAR(500)
	DECLARE @p_RET_NAME NVARCHAR(1000) = (
		SELECT
		CASE
			WHEN RM.ASSET_ID IS NULL OR RM.ASSET_ID = '' THEN RM.ASS_NAME
			ELSE AM.ASSET_NAME
		END
		FROM RET_MASTER RM
		LEFT JOIN ASS_MASTER AM ON RM.ASSET_ID = AM.ASSET_ID
		WHERE RM.RET_ID = @p_RET_ID
	)

	--------------------------TH gửi phê duyệt có cấp phê duyệt trung gian---------------
	IF(EXISTS(SELECT * FROM RET_MASTER WHERE RET_ID = @p_RET_ID AND SIGN_USER IS NOT NULL))
	BEGIN
		SET @p_MESSAGE = N'Thông tin bất động sản: '+@p_RET_NAME+
			N' đã được gửi phê duyệt thành công. Vui lòng đợi cấp phê duyệt trung gian xác nhận.'
	END
	------------------TH gửi phê duyệt ko có cấp phê duyệt trung gian(gửi thẳng trưởng DV)--------------
	ELSE
	BEGIN
		SET @p_MESSAGE = N'Thông tin bất động sản: '+@p_RET_NAME+
			N' đã được gửi phê duyệt thành công. Vui lòng đợi trưởng đơn vị phê duyệt.'
	END

	--------BAODNQ 16/02/2022: Insert vào PL_PROCESS lưu lịch sử xử lý---------
		INSERT INTO dbo.PL_PROCESS
				(
					REQ_ID,
					PROCESS_ID,
					CHECKER_ID,
					APPROVE_DT,
					PROCESS_DESC,
					NOTES
				)
				VALUES
				(	@p_RET_ID,       
					'SEND',
					@p_USER_LOGIN,        
					GETDATE(), 
					N'Người tạo gửi phê duyệt thành công' ,      
					N'Người tạo gửi phê duyệt'       
				)
		IF @@ERROR <> 0 GOTO ABORT

COMMIT TRANSACTION

SELECT '0' as Result, @p_RET_ID RET_ID,  @p_IS_SEND_APPR IS_SEND_APPR ,@p_MESSAGE ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' RET_ID, '' IS_SEND_APPR,'' ErrorDesc
		RETURN '-1'
End

GO


ALTER PROCEDURE [dbo].[RET_MASTER_Confirm]
@p_RET_ID VARCHAR(15),
@p_SIGN_USER VARCHAR(15),
@p_SIGN_DT VARCHAR(20) = NULL

AS

IF(EXISTS (SELECT * FROM RET_MASTER WHERE RET_ID = @p_RET_ID AND IS_SEND_APPR = 'Y' AND (SIGN_DT IS NOT NULL OR SIGN_DT <> '')))
BEGIN
	SELECT '-1' as Result, '' RET_ID,
		N'Xác nhận thất bại. Thông tin bất động sản đã được cấp trung gian xác nhận trước đó' ErrorDesc
	RETURN '-1'
END

IF(EXISTS (SELECT * FROM RET_MASTER WHERE RET_ID = @p_RET_ID AND AUTH_STATUS = 'R'))
BEGIN
	SELECT '-1' as Result, '' RET_ID,
		N'Xác nhận thất bại. Thông tin bất động sản đang bị trả về' ErrorDesc
	RETURN '-1'
END
IF(EXISTS(SELECT RET_ID FROM RET_MASTER WHERE RET_ID = @p_RET_ID AND AUTH_STATUS = 'E' AND IS_SEND_APPR = 'N'))
BEGIN
	SELECT '-1' as Result, '' RET_ID,
		N'Xác nhận thất bại. Thông tin bất động sản đang ở trạng thái lưu nháp. Vui lòng đợi người tạo gửi yêu cầu phê duyệt' ErrorDesc
	RETURN '-1'
END

BEGIN TRANSACTION

	--------------------------
	UPDATE RET_MASTER SET 
		AUTH_STATUS = 'U', 
		SIGN_DT = CONVERT(DATETIME, @p_SIGN_DT, 103)
	WHERE RET_ID = @p_RET_ID
	IF @@ERROR <> 0 GOTO ABORT


	DECLARE @p_RET_NAME NVARCHAR(1000) = (
		SELECT
		CASE
			WHEN RM.ASSET_ID IS NULL OR RM.ASSET_ID = '' THEN RM.ASS_NAME
			ELSE AM.ASSET_NAME
		END
		FROM RET_MASTER RM
		LEFT JOIN ASS_MASTER AM ON RM.ASSET_ID = AM.ASSET_ID
		WHERE RM.RET_ID = @p_RET_ID
	)

	DECLARE @p_MESSAGE NVARCHAR(500) = N' Thông tin bất động sản: ' +@p_RET_NAME+
			N' đã được xác nhận thành công. Vui lòng đợi trưởng đơn vị phê duyệt.'

	--------BAODNQ 16/02/2022: Insert vào PL_PROCESS lưu lịch sử xử lý---------
		INSERT INTO dbo.PL_PROCESS
				(
					REQ_ID,
					PROCESS_ID,
					CHECKER_ID,
					APPROVE_DT,
					PROCESS_DESC,
					NOTES
				)
				VALUES
				(	@p_RET_ID,       
					'APPROVE',
					@p_SIGN_USER,        
					GETDATE(), 
					N'Cấp trung gian xác nhận thành công' ,      
					N'Cấp trung gian xác nhận'       
				)
		IF @@ERROR <> 0 GOTO ABORT

COMMIT TRANSACTION

SELECT '0' as Result, @p_RET_ID RET_ID, @p_MESSAGE ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' RET_ID,'' ErrorDesc
		RETURN '-1'
End


GO


ALTER PROCEDURE [dbo].[RET_MASTER_App]
@P_RET_ID VARCHAR(15),
@P_AUTH_STATUS VARCHAR(1),
@P_CHECKER_ID VARCHAR(12),
@p_APPROVE_DT VARCHAR(20) = NULL

AS
--Validation is here
DECLARE @ERRORSYS NVARCHAR(15) = '' 
IF ( NOT EXISTS ( SELECT * FROM RET_MASTER R WHERE R.RET_ID = @P_RET_ID))
	SET @ERRORSYS = 'RETM-00001'
IF @ERRORSYS <> '' 
	BEGIN
		SELECT ErrorCode Result, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
	RETURN '-1'
END

IF(EXISTS (SELECT * FROM RET_MASTER WHERE RET_ID = @P_RET_ID AND AUTH_STATUS = 'R'))
BEGIN
	SELECT '-1' as Result, 
		N'Phê duyệt thất bại. Thông tin bất động sản đang bị trả về' ErrorDesc
	RETURN '-1'
END

IF(EXISTS (SELECT * FROM RET_MASTER WHERE RET_ID = @P_RET_ID AND AUTH_STATUS = 'A'))
BEGIN
	SELECT '-1' as Result, 
		N'Phê duyệt thất bại. Thông tin bất động sản đã được phê duyệt' ErrorDesc
	RETURN '-1'
END
IF(EXISTS(SELECT RET_ID FROM RET_MASTER WHERE RET_ID = @P_RET_ID AND AUTH_STATUS = 'E' AND IS_SEND_APPR = 'N'))
BEGIN
	SELECT '-1' as Result, '' RET_ID,
		N'Phê duyệt thất bại. Thông tin bất động sản đang ở trạng thái lưu nháp. Vui lòng đợi người tạo gửi yêu cầu phê duyệt' ErrorDesc
	RETURN '-1'
END
IF(EXISTS(
	SELECT RET_ID FROM RET_MASTER WHERE RET_ID = @P_RET_ID AND IS_SEND_APPR = 'Y' AND AUTH_STATUS = 'U'
	AND (SIGN_USER IS NOT NULL AND SIGN_USER <> '') AND (SIGN_DT IS NULL OR SIGN_DT = '')
))
BEGIN
	SELECT '-1' as Result, '' RET_ID,
		N'Phê duyệt thất bại. Thông tin bất động sản đang chờ cấp phê duyệt trung gian xác nhận. Vui lòng đợi cấp phê duyệt trung gian xác nhận' ErrorDesc
	RETURN '-1'
END

BEGIN TRANSACTION
	DECLARE @p_ASSET_ID VARCHAR(15)
	SET @p_ASSET_ID = (SELECT ASSET_ID FROM RET_MASTER WHERE RET_ID = @P_RET_ID)

	UPDATE RET_MASTER SET AUTH_STATUS = @P_AUTH_STATUS, CHECKER_ID = @P_CHECKER_ID,
	APPROVE_DT = CONVERT(DATETIME,@P_APPROVE_DT,103)
	WHERE RET_ID = @P_RET_ID	
	--THAY DOI LOAI TAI SAN LA 2
	UPDATE ASS_MASTER SET ASS_TYPE = '3' WHERE ASSET_ID = @p_ASSET_ID
	IF @@Error <> 0 GOTO ABORT

	--------BAODNQ 16/02/2022: Insert vào PL_PROCESS lưu lịch sử xử lý---------
		INSERT INTO dbo.PL_PROCESS
				(
					REQ_ID,
					PROCESS_ID,
					CHECKER_ID,
					APPROVE_DT,
					PROCESS_DESC,
					NOTES
				)
				VALUES
				(	@p_RET_ID,       
					'APPROVE',
					@P_CHECKER_ID,        
					GETDATE(), 
					N'Trưởng đơn vị phê duyệt thành công' ,      
					N'Trưởng đơn vị phê duyệt'       
				)
		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


ALTER PROCEDURE [dbo].[RET_MASTER_Upd]
@p_RET_ID	varchar(15) = null ,
@p_ASSET_ID VARCHAR(100) = NULL,
@P_ASSET_CODE VARCHAR(100) = NULL,
@P_OWNER NVARCHAR(500) = NULL,
@p_LENGTH	DECIMAL(18,2) = NULL,
@p_WIDTH	DECIMAL(18,2) = NULL,
@p_FLOORS int = NULL,
@p_CURRENT_STATE	nvarchar(100)  = NULL,
@p_RET_TYPE	varchar(15)  = NULL,
@p_STATUS	varchar(15)  = NULL,
@p_LAND_SQUARE	DECIMAL(18,2)  = NULL,
@p_ADDR nvarchar(1000) = NULL, -- hungdv hieu chinh yeu cau anh toi 250520
@p_CONSTRUCT_SQUARE	DECIMAL(18,2)  = NULL,
@p_TOTAL_SQUARE	DECIMAL(18,2)  = NULL,
@p_BOUNDARY	nvarchar(100)  = NULL,
@p_HOUSEDES	nvarchar(100)  = NULL,
@p_PURPOSE_IN_USE	nvarchar(500)  = NULL,
@P_W_USE_CON NVARCHAR(100) = NULL,
@p_USE_STATUS	varchar(15)  = NULL,
@p_CONST_STATUS	varchar(15)  = NULL,
@p_OWNER_TYPE	varchar(15)  = NULL,
@p_USE_PERIOD	int  = NULL,
@p_PERSON_HOLDER	nvarchar(1000)  = NULL,
@p_REASON nvarchar(1000) = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_AUTH_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(15)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_CHECKER_ID	varchar(15)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@P_BUY_DT VARCHAR(20) = NULL,
@p_USE_FORM VARCHAR(1),
@p_USE_FORM_DETAIL nvarchar(MAX),
@p_USE_SOURCE nvarchar(MAX),
@p_USE_PERIOD_DT VARCHAR(20),
@p_OWNER_TYPE_DETAIL NVARCHAR(MAX),
@p_RET_SAVE_CODE VARCHAR(100),
@p_BRANCH_USE nvarchar(500),
@p_RET_TAX_SCHEDULE XML = NULL,
@p_RET_REPAIR_SUGGEST XML = NULL,
@p_RET_REPAIR_REAL XML = NULL,
@p_FLUCTUATING_DT varchar(20) = null,
@p_REASON_FLUCTUATING nvarchar(max) = null,
----------------BAODNQ 16/2/2022: Thêm cột vào bảng RET_MASTER---------
@p_IS_SEND_APPR VARCHAR(15) = NULL,
@p_SEND_APPR_DT VARCHAR(20) = NULL,
@p_SIGN_USER VARCHAR(15) = NULL,
@p_SIGN_DT VARCHAR(20) = NULL,
@p_RET_FORM VARCHAR(15) = NULL,
@p_BRANCH_USE_ID VARCHAR(15) = NULL,
@p_ASSET_NAME NVARCHAR(1000) = NULL,
@p_ASSET_DESC NVARCHAR(MAX) = NULL,
@p_BUY_PRICE DECIMAL(18,2) = NULL,
@p_BRANCH_CREATE VARCHAR(15) = NULL

AS
--Validation is here
	DECLARE @ERRORSYS NVARCHAR(15) = ''
 
	IF ( NOT EXISTS ( SELECT * FROM RET_MASTER WHERE RET_ID = @p_RET_ID))
		SET @ERRORSYS = 'RETM-00001'
	IF @ERRORSYS <> '' 
	BEGIN
		SELECT ErrorCode Result, ''  RET_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
		RETURN '0'
	END

	IF(EXISTS(SELECT * FROM RET_MASTER WHERE RET_ID = @p_RET_ID AND IS_SEND_APPR = 'Y' AND AUTH_STATUS = 'U'))
	BEGIN
		SELECT '-1' AS Result, ''  RET_ID, N'Không thể chỉnh sửa. Thông tin bất động sản đã được gửi yêu cầu phê duyệt' ErrorDesc 
		RETURN '-1'
	END

	DECLARE @p_RET_TAX_ROW_NO INT = 0,
			@p_RET_REPAIR_SUG_ROW_NO INT = 0,
			@p_RET_REPAIR_REAL_ROW_NO INT = 0

	Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output,@p_RET_TAX_SCHEDULE
	DECLARE RetTaxSchedule CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/RetTaxSchedule',2)
	WITH 
	(
				TAX_SCHEDULE_ID VARCHAR(15),
				REQ_PAY_ID VARCHAR(15),
				FRM_DATE varchar(30),
				TO_DATE varchar(30),
				TOTAL_AMT decimal
	)
	OPEN RetTaxSchedule

	Exec sp_xml_preparedocument @hdoc Output,@p_RET_REPAIR_SUGGEST
	DECLARE RetRepairSuggest CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/RetRepairSuggest',2)
	WITH 
	(
		RET_SUG_REPAIR_ID VARCHAR(15),
		REQ_ID	varchar(15),
		REPAIR_SUG_DT	varchar(30) ,		
		BRANCH_ID varchar(15) ,
		BRANCH_SUGGEST nvarchar(4000),
		TOTAL_AMT_REPAIR decimal(18,2),
		TOTAL_AMT_CONSTRUCT DECIMAL(18,2),
		USER_SUGGEST varchar(15),
		REPAIR_REASON nvarchar(4000),
		REPAIR_CONTENT	nvarchar(4000) 
	)
	OPEN RetRepairSuggest

	Exec sp_xml_preparedocument @hdoc Output,@p_RET_REPAIR_REAL
	DECLARE RetRepairReal CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/RetRepairReal',2)
	WITH 
	(
		RET_REPAIR_REAL_ID varchar(15),
		REQ_PAY_ID	varchar(15),
		REPAIR_DT	varchar(30) ,		
		BRANCH_REPAIR nvarchar(4000),
		TOTAL_AMT_REPAIR decimal(18,2),
		TOTAL_AMT_CONSTRUCT DECIMAL(18,2),
		REPAIR_CONTENT_REAL nvarchar(4000),
		NOTES	nvarchar(4000) 
	)
	OPEN RetRepairReal
BEGIN TRANSACTION

	IF(EXISTS (SELECT * FROM RET_MASTER WHERE RET_ID = @p_RET_ID AND AUTH_STATUS = 'A'))
	BEGIN
		--------------------CẬP NHẬT LẠI SAU KHI ĐÃ DUYỆT----------------
		SET @p_CHECKER_ID = NULL
		SET @p_APPROVE_DT = NULL
		SET @p_SEND_APPR_DT = NULL
		SET @p_SIGN_DT = NULL
	END

	UPDATE RET_MASTER
	SET [BUY_DT] = CONVERT(DATETIME, @P_BUY_DT, 103),
		[ASSET_ID] = @p_ASSET_ID,
		[ASSET_CODE] = @P_ASSET_CODE,
		[OWNER] = @P_OWNER,
		[LENGTH] = @p_LENGTH,
		[WIDTH] = @p_WIDTH,
		[FLOORS] = @p_FLOORS,
		[CURRENT_STATE] = @p_CURRENT_STATE,
		[RET_TYPE] = @p_RET_TYPE,
		[STATUS] = @p_STATUS,
		[LAND_SQUARE] = @p_LAND_SQUARE,
		[CONSTRUCT_SQUARE] = @p_CONSTRUCT_SQUARE,
		[TOTAL_SQUARE] = @p_TOTAL_SQUARE,
		[BOUNDARY] = @p_BOUNDARY,
		[HOUSEDES] = @p_HOUSEDES,
		[PURPOSE_IN_USE] = @p_PURPOSE_IN_USE,
		[W_USE_CON] = @P_W_USE_CON,
		[USE_STATUS] = @p_USE_STATUS,
		[CONST_STATUS] = @p_CONST_STATUS,
		[OWNER_TYPE] = @p_OWNER_TYPE,
		[USE_PERIOD] = @p_USE_PERIOD,
		[PERSON_HOLDER] = @p_PERSON_HOLDER,
		[NOTES] = @p_NOTES,
		[REASON] = @p_REASON,
		[RECORD_STATUS] = @p_RECORD_STATUS,
		[AUTH_STATUS] = @p_AUTH_STATUS,
		[MAKER_ID] = @p_MAKER_ID,
		[CREATE_DT] = CONVERT(DATETIME, @p_CREATE_DT, 103),
		[CHECKER_ID] = @p_CHECKER_ID,
		[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103),
		[USE_FORM] = @p_USE_FORM,
		[USE_FORM_DETAIL] = @p_USE_FORM_DETAIL,
		[USE_SOURCE] = @p_USE_SOURCE,
		[USE_PERIOD_DT] = CONVERT(DATETIME, @p_USE_PERIOD_DT, 103),
		[OWNER_TYPE_DETAIL] = @p_OWNER_TYPE_DETAIL,
		[RET_SAVE_CODE] = @p_RET_SAVE_CODE,
		[BRANCH_USE] = @p_BRANCH_USE,
		ADDR = @p_ADDR, -- hungdv hieu chinh yeu cau anh toi 250520
		[FLUCTUATING_DT] = CONVERT(DATETIME, @p_FLUCTUATING_DT, 103),
		[REASON_FLUCTUATING] = @p_REASON_FLUCTUATING,
		----------------BAODNQ 16/2/2022: Thêm cột vào bảng RET_MASTER---------
		[IS_SEND_APPR] = @p_IS_SEND_APPR,
		[SEND_APPR_DT] = CONVERT(DATETIME, @p_SEND_APPR_DT, 103),
		[SIGN_USER] = @p_SIGN_USER,
		[SIGN_DT] = CONVERT(DATETIME, @p_SIGN_DT, 103),
		[RET_FORM] = @p_RET_FORM,
		[BRANCH_USE_ID] = @p_BRANCH_USE_ID,
		[ASS_NAME] = @p_ASSET_NAME,
		[DESCRIPTION] = @p_ASSET_DESC,
		[ASSET_PRICE] = @p_BUY_PRICE,
		[BRANCH_CREATE] = @p_BRANCH_CREATE

	WHERE RET_ID = @p_RET_ID;
	IF @@Error <> 0 GOTO ABORT
		--UPDATE DETAIL 
		DECLARE
				@TAX_SCHEDULE_ID VARCHAR(15),
				@RET_SUG_REPAIR_ID VARCHAR(15), 
				@RET_REPAIR_REAL_ID varchar(15),
				@d_REQ_PAY_ID VARCHAR(15),
				@d_FRM_DATE varchar(30),
				@d_TO_DATE varchar(30),
				@d_TOTAL_AMT decimal,
				@RET_ID	varchar(15),
				@REQ_ID varchar(15),
				@REPAIR_SUG_DT varchar(30),
				@BRANCH_ID varchar(15),
				@REPAIR_DT varchar(20),
				@BRANCH_REPAIR nvarchar(4000),
				@REPAIR_CONTENT_REAL nvarchar(4000),
				@NOTES nvarchar(4000),
				@BRANCH_SUGGEST nvarchar(4000),
				@TOTAL_AMT decimal,
				@USER_SUGGEST varchar(15),
				@REPAIR_REASON nvarchar(4000),
				@REPAIR_CONTENT nvarchar(4000),
				@d_TOTAL_AMT_REPAIR DECIMAL(18,2),
				@d_TOTAL_AMT_CONSTRUCT DECIMAL(18,2)

		--UPDATE ACCESSORY DETAIL
		DELETE FROM RET_TAX_SCHEDULE WHERE RET_ID = @p_RET_ID
		FETCH NEXT FROM RetTaxSchedule INTO @TAX_SCHEDULE_ID, @d_REQ_PAY_ID,@d_FRM_DATE,@d_TO_DATE,@d_TOTAL_AMT
		WHILE @@FETCH_STATUS = 0
		BEGIN
			print LEN(@TAX_SCHEDULE_ID)
			SET @p_RET_TAX_ROW_NO = @p_RET_TAX_ROW_NO + 1

			---------------PDN thanh toán chặn---------------
			------TH KO CÓ PDN thanh toán------
			IF(NOT EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @d_REQ_PAY_ID))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, '' RET_ID, 
				N'Lưới thông tin quản lý định kỳ thuế nhà đất, dòng ' + CONVERT(VARCHAR, @p_RET_TAX_ROW_NO) + 
				N'Phiếu đề nghị thanh toán không tồn tại trong hệ thống' ErrorDesc
				RETURN '-1'
			END
			---------TH PDN thanh toán CHƯA ĐƯỢC DUYỆT---------
			IF(NOT EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @d_REQ_PAY_ID AND AUTH_STATUS_KT = 'A'))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, '' RET_ID, 
				N'Lưới thông tin quản lý định kỳ thuế nhà đất, dòng ' + CONVERT(VARCHAR, @p_RET_TAX_ROW_NO) +
				N'Phiếu đề nghị thanh toán chưa được duyệt' ErrorDesc
				RETURN '-1'
			END

			IF(CONVERT(DATETIME, @d_TO_DATE,103) <= CONVERT(DATETIME, @d_FRM_DATE, 103))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, '' RET_ID, 
					N'Lưới thông tin quản lý định kỳ thuế nhà đất, dòng ' + CONVERT(VARCHAR, @p_RET_TAX_ROW_NO) + 
					N': Thời gian kết thúc đóng thuế phải lớn hơn thời gian bắt đầu đóng thuế' ErrorDesc
				RETURN '-1'
			END

			IF(LEN(@TAX_SCHEDULE_ID)  = 0)
			BEGIN
				EXEC SYS_CodeMasters_Gen 'RET_TAX_SCHEDULE', @TAX_SCHEDULE_ID out
				IF @TAX_SCHEDULE_ID ='' OR @TAX_SCHEDULE_ID IS NULL GOTO ABORT
			END
			PRINT @TAX_SCHEDULE_ID
			INSERT INTO RET_TAX_SCHEDULE([TAX_SCHEDULE_ID],[RET_ID],[REQ_PAY_ID],[FRM_DATE],[TO_DATE],[TOTAL_AMT],[AUTH_STATUS],[RECORD_STATUS],[MAKER_ID])
			VALUES(@TAX_SCHEDULE_ID,@p_RET_ID ,@d_REQ_PAY_ID , CONVERT(DATETIME, @d_FRM_DATE, 103) ,CONVERT(DATETIME, @d_TO_DATE, 103) ,@d_TOTAL_AMT ,@p_AUTH_STATUS,@p_RECORD_STATUS  ,@p_MAKER_ID  )
			IF @@ERROR <> '' GOTO ABORT
			FETCH NEXT FROM RetTaxSchedule INTO @TAX_SCHEDULE_ID, @d_REQ_PAY_ID,@d_FRM_DATE,@d_TO_DATE,@d_TOTAL_AMT
		END

		PRINT 'RetTaxSchedule'
		--UPDATE RetRepairSuggest
		DELETE FROM RET_REPAIR_SUGGEST WHERE RET_ID = @p_RET_ID
		FETCH NEXT FROM RetRepairSuggest INTO 
			@RET_SUG_REPAIR_ID,   
			@REQ_ID,@REPAIR_SUG_DT,
			@BRANCH_ID,
			@BRANCH_SUGGEST,
			@d_TOTAL_AMT_REPAIR,
			@d_TOTAL_AMT_CONSTRUCT,
			@USER_SUGGEST,
			@REPAIR_REASON,
			@REPAIR_CONTENT
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @p_RET_REPAIR_SUG_ROW_NO = @p_RET_REPAIR_SUG_ROW_NO + 1

			---------------TTCT chặn---------------
			------TH KO CÓ TTCT------
			IF(NOT EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID = @REQ_ID))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, '' RET_ID, 
				N'Lưới thông tin sửa chữa dự kiến, dòng ' +CONVERT(VARCHAR, @p_RET_REPAIR_SUG_ROW_NO)+
				N'Tờ trình chủ trương không tồn tại trong hệ thống' ErrorDesc
				RETURN '-1'
			END
			---------TH TTCT CHƯA ĐƯỢC DUYỆT---------
			IF(NOT EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID = @REQ_ID AND PROCESS_ID = 'APPROVE'))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, '' RET_ID, 
				N'Lưới thông tin sửa chữa dự kiến, dòng ' +CONVERT(VARCHAR, @p_RET_REPAIR_SUG_ROW_NO)+
				N'Tờ trình chủ trương chưa được duyệt' ErrorDesc
				RETURN '-1'
			END


			DECLARE @l_RET_SUG_REPAIR_ID  VARCHAR(15)
			IF LEN(@RET_SUG_REPAIR_ID ) = 0
			BEGIN
				EXEC SYS_CodeMasters_Gen 'RET_REPAIR_SUGGEST', @RET_SUG_REPAIR_ID out
				PRINT @RET_SUG_REPAIR_ID
				IF @RET_SUG_REPAIR_ID ='' OR @RET_SUG_REPAIR_ID IS NULL GOTO ABORT
			END
			
			INSERT INTO RET_REPAIR_SUGGEST
				([RET_SUG_REPAIR_ID],[RET_ID],[REQ_ID],[REPAIR_SUG_DT],[BRANCH_ID],[BRANCH_SUGGEST],
				[TOTAL_AMT_REPAIR],[TOTAL_AMT_CONSTRUCT],[USER_SUGGEST],[REPAIR_REASON],[REPAIR_CONTENT],[AUTH_STATUS],[RECORD_STATUS],
				[MAKER_ID])
			VALUES
				(@RET_SUG_REPAIR_ID,@p_RET_ID ,@REQ_ID ,CONVERT(DATETIME, @REPAIR_SUG_DT, 103) ,@BRANCH_ID  ,
				@BRANCH_SUGGEST,@d_TOTAL_AMT_REPAIR,@d_TOTAL_AMT_CONSTRUCT,@USER_SUGGEST,@REPAIR_REASON,@REPAIR_CONTENT ,@p_AUTH_STATUS  ,
				@p_RECORD_STATUS ,@p_MAKER_ID  )
			IF @@ERROR <> '' GOTO ABORT
			FETCH NEXT FROM RetRepairSuggest INTO 
				@RET_SUG_REPAIR_ID, 
				@REQ_ID,
				@REPAIR_SUG_DT,
				@BRANCH_ID,
				@BRANCH_SUGGEST,
				@d_TOTAL_AMT_REPAIR,
				@d_TOTAL_AMT_CONSTRUCT,
				@USER_SUGGEST,
				@REPAIR_REASON,
				@REPAIR_CONTENT
		END
		
		--UPDATE RetRepairReal
		DELETE FROM RET_REPAIR_REAL WHERE RET_ID = @p_RET_ID
		FETCH NEXT FROM RetRepairReal INTO 
			@RET_REPAIR_REAL_ID, 
			@d_REQ_PAY_ID,
			@REPAIR_DT,
			@BRANCH_REPAIR,
			@d_TOTAL_AMT_REPAIR,
			@d_TOTAL_AMT_CONSTRUCT,
			@REPAIR_CONTENT_REAL,
			@NOTES
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @p_RET_REPAIR_REAL_ROW_NO = @p_RET_REPAIR_REAL_ROW_NO + 1

			---------------PDN thanh toán chặn---------------
			------TH KO CÓ PDN thanh toán------
			IF(NOT EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @d_REQ_PAY_ID))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, '' RET_ID, 
				N'Lưới thông tin sửa chữa thực tế, dòng ' +CONVERT(VARCHAR, @p_RET_REPAIR_REAL_ROW_NO)+
				N'Phiếu đề nghị thanh toán không tồn tại trong hệ thống' ErrorDesc
				RETURN '-1'
			END
			---------TH PDN thanh toán CHƯA ĐƯỢC DUYỆT---------
			IF(NOT EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @d_REQ_PAY_ID AND AUTH_STATUS_KT = 'A'))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, '' RET_ID, 
				N'Lưới thông tin sửa chữa thực tế, dòng ' +CONVERT(VARCHAR, @p_RET_REPAIR_REAL_ROW_NO)+
				N'Phiếu đề nghị thanh toán chưa được duyệt' ErrorDesc
				RETURN '-1'
			END

			DECLARE @l_RET_REPAIR_REAL_ID  VARCHAR(15)
			IF LEN(@RET_REPAIR_REAL_ID ) = 0
			BEGIN
				EXEC SYS_CodeMasters_Gen 'RET_REPAIR_REAL', @RET_REPAIR_REAL_ID out
				PRINT @RET_REPAIR_REAL_ID
				IF @RET_REPAIR_REAL_ID ='' OR @RET_REPAIR_REAL_ID IS NULL GOTO ABORT
			END
			
			INSERT INTO RET_REPAIR_REAL
				([RET_REPAIR_REAL_ID],[RET_ID],[REQ_PAY_ID],[REPAIR_DT],[BRANCH_REPAIR],[TOTAL_AMT_REPAIR],
				[TOTAL_AMT_CONSTRUCT],[REPAIR_CONTENT_REAL],[NOTES] ,  [AUTH_STATUS],[RECORD_STATUS],[MAKER_ID])
			VALUES
				(@RET_REPAIR_REAL_ID,@p_RET_ID,@d_REQ_PAY_ID  ,CONVERT(DATETIME, @REPAIR_DT, 103) ,
				@BRANCH_REPAIR ,@d_TOTAL_AMT_REPAIR,@d_TOTAL_AMT_CONSTRUCT,@REPAIR_CONTENT_REAL,@NOTES ,@p_AUTH_STATUS  ,@p_RECORD_STATUS ,
				@p_MAKER_ID  )
			IF @@ERROR <> '' GOTO ABORT
			FETCH NEXT FROM RetRepairReal INTO 
				@RET_REPAIR_REAL_ID, 
				@d_REQ_PAY_ID,
				@REPAIR_DT,
				@BRANCH_REPAIR,
				@d_TOTAL_AMT_REPAIR,
				@d_TOTAL_AMT_CONSTRUCT,
				@REPAIR_CONTENT_REAL,
				@NOTES
		END

		CLOSE RetTaxSchedule
		DEALLOCATE RetTaxSchedule
		CLOSE RetRepairSuggest
		DEALLOCATE RetRepairSuggest
		CLOSE RetRepairReal
		DEALLOCATE RetRepairReal


		---------------BAODNQ : TH NGƯỜI TẠO CẬP NHẬT NHIỀU LẦN THÌ CHỈ LƯU LỊCH SỬ XỬ LÝ CẬP NHẬT GẦN NHẤT----------------
		IF((SELECT TOP 1 PROCESS_ID FROM PL_PROCESS WHERE REQ_ID = @p_RET_ID ORDER BY APPROVE_DT DESC) = 'UPDATE')
		BEGIN
			WITH RESULT AS(
				SELECT TOP 1 * FROM PL_PROCESS WHERE REQ_ID = @p_RET_ID
				ORDER BY APPROVE_DT DESC
			)
			DELETE FROM RESULT
		END

		--------BAODNQ 16/02/2022: Insert vào PL_PROCESS lưu lịch sử xử lý---------
		INSERT INTO dbo.PL_PROCESS
				(
					REQ_ID,
					PROCESS_ID,
					CHECKER_ID,
					APPROVE_DT,
					PROCESS_DESC,
					NOTES
				)
				VALUES
				(	@p_RET_ID,       
					'UPDATE',
					@p_MAKER_ID,        
					GETDATE(), 
					N'Cập nhật thông tin bất động sản thành công' ,      
					N'Cập nhật thông tin bất động sản'       
				)
		IF @@ERROR <> 0 GOTO ABORT

		---------BAODNQ 15/3/2022: Lấy tên BDS-----
		DECLARE @p_RET_NAME NVARCHAR(MAX) =(
			SELECT  B.ASSET_NAME
			FROM RET_MASTER A
			LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID
			WHERE A.RET_ID = @p_RET_ID
		)

COMMIT TRANSACTION
		SELECT '0' as Result, @p_RET_ID  RET_ID, @p_RET_NAME RET_NAME,'' ErrorDesc
		RETURN '0'
ABORT:
BEGIN
		CLOSE RetTaxSchedule
		DEALLOCATE RetTaxSchedule
		CLOSE RetRepairSuggest
		DEALLOCATE RetRepairSuggest
		CLOSE RetRepairReal
		DEALLOCATE RetRepairReal
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' RET_ID, '' RET_NAME,'' ErrorDesc
		RETURN '-1'
End