ALTER PROCEDURE [dbo].[RET_MASTER_Ins]
@p_ASSET_ID VARCHAR(15) = NULL,
@P_ASSET_CODE VARCHAR(15) = NULL,
@P_OWNER VARCHAR(500) = NULL,
@p_LENGTH	DECIMAL(18,2) = NULL,
@p_ADDR nvarchar(1000) = NULL, -- hungdv hieu chinh yeu cau anh toi 250520
@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_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_XML_TEMP XML = NULL,
@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

----INSERT SYS_PREFIX---
	--INSERT INTO SYS_PREFIX VALUES('RET_TAX_SCHEDULE', 'RETX', N'Bảng thông tin quản lý định kỳ thuế nhà đất')
	--INSERT INTO SYS_PREFIX VALUES('RET_REPAIR_SUGGEST', 'RETRS', N'Bảng thông tin sửa chữa dự kiến')
	--INSERT INTO SYS_PREFIX VALUES('RET_REPAIR_REAL', 'RETRR', N'Bảng thông tin sửa chữa thực tế')
----------------------------

DECLARE @ERRORSYS NVARCHAR(15) = '' 
IF ( EXISTS ( SELECT * FROM RET_MASTER WHERE ASSET_ID = @P_ASSET_ID ))
	SET @ERRORSYS = 'RETM-00002'
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 
	(
				----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 
	(
		-------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 
	(
		-----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
DECLARE @l_RET_ID VARCHAR(15)
		EXEC SYS_CodeMasters_Gen 'RET_MASTER', @l_RET_ID out
		IF @l_RET_ID='' OR @l_RET_ID IS NULL GOTO ABORT


		INSERT INTO RET_MASTER
		(
			[BUY_DT],
			[RET_ID],
			[ASSET_ID],
			[ASSET_CODE],
			[OWNER],
			[LENGTH],
			[WIDTH],
			[FLOORS],
			[CURRENT_STATE],
			[RET_TYPE],
			[STATUS],
			[LAND_SQUARE],
			[CONSTRUCT_SQUARE],
			[TOTAL_SQUARE],
			[BOUNDARY],
			[HOUSEDES],
			[PURPOSE_IN_USE],
			[W_USE_CON],
			[USE_STATUS],
			[CONST_STATUS],
			[OWNER_TYPE],
			[USE_PERIOD],
			[PERSON_HOLDER],
			[REASON],
			[NOTES],
			[RECORD_STATUS],
			[AUTH_STATUS],
			[MAKER_ID],
			[CREATE_DT],
			[CHECKER_ID],
			[APPROVE_DT],
			[USE_FORM],
			[USE_FORM_DETAIL],
			[USE_SOURCE],
			[USE_PERIOD_DT],
			[OWNER_TYPE_DETAIL],
			[RET_SAVE_CODE],
			BRANCH_USE,
			[FLUCTUATING_DT],
			[REASON_FLUCTUATING],
			ADDR, -- hungdv hieu chinh yeu cau anh toi 250520
			----------------BAODNQ 16/2/2022: Thêm cột vào bảng RET_MASTER---------
			[IS_SEND_APPR],
			[SEND_APPR_DT],
			[SIGN_USER],
			[SIGN_DT]
		)
		VALUES
		(CONVERT(DATETIME, @P_BUY_DT, 103), @l_RET_ID, @p_ASSET_ID, @P_ASSET_CODE, @P_OWNER, @p_LENGTH, @p_WIDTH, @p_FLOORS,
		 @p_CURRENT_STATE, @p_RET_TYPE, @p_STATUS, @p_LAND_SQUARE, @p_CONSTRUCT_SQUARE, @p_TOTAL_SQUARE, @p_BOUNDARY,
		 @p_HOUSEDES, @p_PURPOSE_IN_USE, @P_W_USE_CON, @p_USE_STATUS, @p_CONST_STATUS, @p_OWNER_TYPE, @p_USE_PERIOD,
		 @p_PERSON_HOLDER, @p_REASON, @p_NOTES, @p_RECORD_STATUS, @p_AUTH_STATUS, @p_MAKER_ID,
		 CONVERT(DATETIME, @p_CREATE_DT, 103), @p_CHECKER_ID, CONVERT(DATETIME, @p_APPROVE_DT, 103), @p_USE_FORM,
		 @p_USE_FORM_DETAIL, @p_USE_SOURCE, CONVERT(DATETIME, @p_USE_PERIOD_DT, 103), @p_OWNER_TYPE_DETAIL, 
		 @p_RET_SAVE_CODE, @p_BRANCH_USE,CONVERT(DATETIME, @p_FLUCTUATING_DT, 103),@p_REASON_FLUCTUATING ,@p_ADDR,
		 @p_IS_SEND_APPR, CONVERT(DATETIME, @p_SEND_APPR_DT, 103), @p_SIGN_USER, CONVERT(DATETIME, @p_SIGN_DT, 103))
		IF @@Error <> 0 GOTO ABORT
		--KHAI BAO TOAN BO BIEN TRUOC KHI FETCH DATA
		DECLARE 
				--@d_REQ_PAY_ID VARCHAR(15),
				---HOTFIX---
				@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),
				@REPAIR_DT varchar(30),
				@BRANCH_ID varchar(15),
				@BRANCH_SUGGEST nvarchar(4000),
				@BRANCH_REPAIR nvarchar(4000),
				@REPAIR_CONTENT_REAL nvarchar(4000),
				@NOTES nvarchar(4000),
				@TOTAL_AMT decimal,
				@USER_SUGGEST varchar(15),
				@REPAIR_REASON nvarchar(4000),
				@REPAIR_CONTENT nvarchar(4000)
		--INSERT RetTaxSchedule
		FETCH NEXT FROM RetTaxSchedule INTO  @d_REQ_PAY_ID,@d_FRM_DATE,@d_TO_DATE,@d_TOTAL_AMT
		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_TAX_SCHEDULE_ID  VARCHAR(15)
			EXEC SYS_CodeMasters_Gen 'RET_TAX_SCHEDULE', @l_TAX_SCHEDULE_ID out
			IF @l_TAX_SCHEDULE_ID='' OR @l_TAX_SCHEDULE_ID IS NULL GOTO ABORT
			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(@l_TAX_SCHEDULE_ID,@l_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  @d_REQ_PAY_ID,@d_FRM_DATE,@d_TO_DATE,@d_TOTAL_AMT
		END


		--INSERT RepairSuggest
		FETCH NEXT FROM RetRepairSuggest INTO  @REQ_ID,@REPAIR_SUG_DT,@BRANCH_ID,@BRANCH_SUGGEST,@TOTAL_AMT,@USER_SUGGEST,@REPAIR_REASON,@REPAIR_CONTENT
		WHILE @@FETCH_STATUS = 0
		BEGIN

			---------------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)
			EXEC SYS_CodeMasters_Gen 'RET_REPAIR_SUGGEST', @l_RET_SUG_REPAIR_ID out
			IF @l_RET_SUG_REPAIR_ID='' OR @l_RET_SUG_REPAIR_ID IS NULL GOTO ABORT
			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(@l_RET_SUG_REPAIR_ID,@l_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 @REQ_ID,@REPAIR_SUG_DT,@BRANCH_ID,@BRANCH_SUGGEST,@TOTAL_AMT,@USER_SUGGEST,@REPAIR_REASON,@REPAIR_CONTENT
		END

		--INSERT RepairReal
		FETCH NEXT FROM RetRepairReal INTO  @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)
			EXEC SYS_CodeMasters_Gen 'RET_REPAIR_REAL', @l_RET_REPAIR_REAL_ID out
			IF @l_RET_REPAIR_REAL_ID='' OR @l_RET_REPAIR_REAL_ID IS NULL GOTO ABORT
			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(@l_RET_REPAIR_REAL_ID,@l_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  @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
				(	@l_RET_ID,       
					'INSERT',
					@p_MAKER_ID,        
					GETDATE(), 
					N'Thêm mới thông tin bất động sản thành công' ,      
					N'Thêm mới thông tin bất động sản'       
				)
		IF @@ERROR <> 0 GOTO ABORT

COMMIT TRANSACTION
SELECT '0' as Result, @l_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