ALTER PROCEDURE [dbo].[RET_MASTER_Upd]
@p_RET_ID	varchar(15) = null ,
@p_ASSET_ID VARCHAR(15) = NULL,
@P_ASSET_CODE VARCHAR(15) = NULL,
@P_OWNER VARCHAR(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

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
	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),
				----HOTFIX------
				--REQ_PAY_ID VARCHAR(15),
				REQ_PAY_ID VARCHAR(100),
				---------------
				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),
		------HOTFIX--------
		--REQ_ID	varchar(15),
		REQ_ID	varchar(100),
		-------------------
		REPAIR_SUG_DT	varchar(30) ,		
		BRANCH_ID varchar(15) ,
		BRANCH_SUGGEST nvarchar(4000),
		TOTAL_AMT decimal,
		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),
		---HOTFIX---------
		--REQ_PAY_ID	varchar(15),
		REQ_PAY_ID	varchar(100),
		----------------------
		REPAIR_DT	varchar(30) ,		
		BRANCH_REPAIR nvarchar(4000),
		TOTAL_AMT decimal,
		REPAIR_CONTENT_REAL nvarchar(4000),
		NOTES	nvarchar(4000) 
	)
	OPEN RetRepairReal
BEGIN TRANSACTION
	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)

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),
				----HOTFIX--------
				--@d_REQ_PAY_ID VARCHAR(15),
				@d_REQ_PAY_ID VARCHAR(100),
				-------------------
				@d_FRM_DATE varchar(30),
				@d_TO_DATE varchar(30),
				@d_TOTAL_AMT decimal,
				@RET_ID	varchar(15),
				----HOTFIX------
				--@REQ_ID varchar(15),
				@REQ_ID varchar(100),
				------------------
				@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)

		--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)

			---------------PDN thanh toán chặn---------------
			------TH KO CÓ PDN thanh toán------
			IF(NOT EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_CODE = @d_REQ_PAY_ID))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, '' RET_ID, 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_CODE = @d_REQ_PAY_ID AND AUTH_STATUS_KT = 'A'))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, '' RET_ID, N'Phiếu đề nghị thanh toán chưa được duyệt' 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,@TOTAL_AMT,@USER_SUGGEST,@REPAIR_REASON,@REPAIR_CONTENT
		WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT @REQ_ID
			---------------TTCT chặn---------------
			------TH KO CÓ TTCT------
			IF(NOT EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE REQ_CODE = @REQ_ID))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, '' RET_ID, 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_CODE = @REQ_ID AND PROCESS_ID = 'APPROVE'))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, '' RET_ID, 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],[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,@TOTAL_AMT,@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,@TOTAL_AMT,@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,@TOTAL_AMT,@REPAIR_CONTENT_REAL,@NOTES
		WHILE @@FETCH_STATUS = 0
		BEGIN

			---------------PDN thanh toán chặn---------------
			------TH KO CÓ PDN thanh toán------
			IF(NOT EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_CODE = @d_REQ_PAY_ID))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, '' RET_ID, 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_CODE = @d_REQ_PAY_ID AND AUTH_STATUS_KT = 'A'))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, '' RET_ID, 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_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 ,@TOTAL_AMT,@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,@TOTAL_AMT,@REPAIR_CONTENT_REAL,@NOTES
		END

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

		--------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

COMMIT TRANSACTION
		SELECT '0' as Result, @p_RET_ID  RET_ID, '' 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, '' ErrorDesc
		RETURN '-1'
End