ALTER PROCEDURE [dbo].[TR_REQ_ADVANCE_PAYMENT_KT_Upd]
@p_REQ_PAY_ID	varchar(15)= NULL,
@p_REQ_PAY_CODE	varchar(50)	= NULL,
@p_REQ_DT VARCHAR(20)= NULL,
@p_BRANCH_ID	varchar(15)	= NULL,
@p_DEP_ID	varchar(15)	= NULL,
@p_REQ_REASON	nvarchar(MAX)	= NULL,
@p_REQ_TYPE	varchar(15)	= NULL,
@P_REQ_ENTRIES nvarchar(MAX)	= NULL,
@p_REQ_DESCRIPTION	nvarchar(MAX)	= NULL,
@p_REF_ID	varchar(15)	= NULL,
@p_RECEIVER_PO	nvarchar(250)	= NULL,
@p_RECEIVER_DEBIT	nvarchar(250)	= NULL,
@p_REQ_PAY_TYPE	varchar(15)	= NULL,
@p_REQ_TYPE_CURRENCY	nvarchar(50)	= NULL,
@p_REQ_AMT	decimal(18, 0)	= NULL,
@p_REQ_TEMP_AMT	decimal(18, 0)	= NULL,
@p_MAKER_ID	varchar(15)	= NULL,
@p_CREATE_DT	varchar(25)	= NULL,
@p_EDITOR_ID	varchar(15)	= NULL,
@p_AUTH_STATUS	varchar(1)	= NULL,
@p_CHECKER_ID	varchar(15)	= NULL,
@p_APPROVE_DT	varchar(25)	= NULL,
@p_CREATE_DT_KT	varchar(25)	= NULL,
@p_MAKER_ID_KT	varchar(15)	= NULL,
@p_AUTH_STATUS_KT	varchar(1)	= NULL,
@p_CHECKER_ID_KT	varchar(1)	= NULL,
@p_APPROVE_DT_KT  varchar(25)= null,
@p_CONFIRM_NOTE	nvarchar(500)	= NULL,
@p_BRANCH_CREATE	varchar(15)	= NULL,
@p_NOTES	varchar(15)	= NULL,
@p_RECORD_STATUS	varchar(1)	= NULL,
@p_TRANSFER_MAKER	nvarchar(50)	= NULL,
@p_TRANSFER_DT	varchar(25)	= NULL,
@p_TRASFER_USER_RECIVE	varchar(15)	= NULL,
@p_PROCESS	varchar(15)	= NULL,
@p_PAY_PHASE VARCHAR(15)= NULL,
@p_DVDM_ID VARCHAR(15) = NULL,
@p_RATE DECIMAL(18,0)= 0,
@p_XMP_TEMP XML = NULL
AS
--Validation is here
/*
END 
*/
DECLARE @ENTRY_PAIR varchar(20),@DR_CR varchar(20),@ACCT VARCHAR(50), @ACCT_NAME VARCHAR(500), @AMT decimal(18,2),@CURRENCY VARCHAR(15), @EXC_RATE DECIMAL(18,0),
	@BRANCH_ID VARCHAR(15), @DEP_ID VARCHAR(15),@TRN_DESC nvarchar(1000),@GL_CODE VARCHAR(10)
DECLARE @hdoc INT;
EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TEMP;
DECLARE XmlData CURSOR FOR
SELECT *
FROM
	OPENXML(@hdoc, '/Root/XmlData', 2)
	WITH(ENTRY_PAIR varchar(20),DR_CR varchar(20),ACCT VARCHAR(50), ACCT_NAME VARCHAR(500), AMT decimal(18,2),CURRENCY VARCHAR(15), EXC_RATE DECIMAL(18,0),
	BRANCH_ID VARCHAR(15), DEP_ID VARCHAR(15),TRN_DESC nvarchar(1000), GL_CODE VARCHAR(10))
	OPEN XmlData;
	DECLARE @INDEX INT  =0
BEGIN TRANSACTION
		-- KHAI BAO CAC BUOC DUYET- XAC NHAN
		DECLARE @LEVEL_JOB VARCHAR(5), @LEVEL_JOB_PREV INT
		SET @LEVEL_JOB =(SELECT LEVEL_JOB FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_PAY_ID AND TLNAME=@p_MAKER_ID_KT AND TYPE_JOB ='XL')
		SET @LEVEL_JOB_PREV = CONVERT(INT,@LEVEL_JOB) -1
		----
		--IF(EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND MAKER_ID_KT IS NULL))
		IF(@p_XMP_TEMP IS NOT NULL)
		BEGIN
			UPDATE TR_REQ_ADVANCE_PAYMENT SET 
			AUTH_STATUS_KT ='U', CREATE_DT_KT = GETDATE(),MAKER_ID_KT =@p_MAKER_ID_KT,CHECKER_ID_KT=NULL,APPROVE_DT_KT = NULL, CONFIRM_NOTES=@p_CONFIRM_NOTE
			WHERE REQ_PAY_ID =@p_REQ_PAY_ID
			DELETE FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID = @p_REQ_PAY_ID
		IF @@Error <> 0 GOTO ABORT
		DECLARE @DR_CR_NAME NVARCHAR(50)
		FETCH NEXT FROM XmlData INTO @ENTRY_PAIR ,@DR_CR,@ACCT, @ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE,@BRANCH_ID, @DEP_ID,@TRN_DESC,@GL_CODE
		WHILE @@fetch_status=0 BEGIN

			SET @INDEX = @INDEX +1
			IF(@DR_CR='D')
			BEGIN
				SET @DR_CR_NAME =N'Nợ'
			END
			ELSE
			BEGIN
				SET @DR_CR_NAME =N'Có'
			END
			SET @ACCT_NAME =(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@ACCT)
			IF(@ACCT_NAME IS NULL OR @ACCT_NAME ='')
			BEGIN
				ROLLBACK TRANSACTION
				CLOSE XmlData;
				DEALLOCATE XmlData;
				SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Tên tài khoản hạch toán chưa đúng. Vui lòng bấm vào nút Check GL & Check CASA để đồng bộ tài khoản từ Core' ErrorDesc
				RETURN '-1'
			END
			IF(EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID))
			BEGIN
				IF(@DR_CR ='C' AND ISNULL(@AMT,0) <> (SELECT ISNULL(SUM(TOTAL_AMT),0) FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID))
				BEGIN
					ROLLBACK TRANSACTION
					CLOSE XmlData;
					DEALLOCATE XmlData;
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Số tiền hạch toán có của tài khoản: '+@ACCT + N' phải bằng với số tiền trên phương thức thanh toán của tài khoản đó là: '+FORMAT((SELECT ISNULL(SUM(TOTAL_AMT),0) FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID),'#,###')  ErrorDesc
					RETURN '-1'
				END
			END
			--IF(@DR_ACCT IS NULL OR @DR_ACCT ='')
			--BEGIN
			--	ROLLBACK TRANSACTION
			--	CLOSE XmlData;
			--	DEALLOCATE XmlData;
			--	SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Tài khoản có không được phép để trống' ErrorDesc
			--	RETURN '-1'
			--END
			--IF(@TRN_DESC IS NULL OR @TRN_DESC ='')
			--BEGIN
			--	ROLLBACK TRANSACTION
			--	CLOSE XmlData;
			--	DEALLOCATE XmlData;
			--	SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Diễn giải hạch toán không được phép để trống' ErrorDesc
			--	RETURN '-1'
			--END
			DECLARE @p_ET_ID VARCHAR(15);
			EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_ENTRIES', @p_ET_ID OUT;
			IF @p_ET_ID='' OR @p_ET_ID IS NULL GOTO ABORT;
			INSERT INTO TR_REQ_PAY_ENTRIES (REQ_PAY_DT_ID,REQ_PAY_ID,ENTRY_PAIR,DR_CR, DR_CR_NAME,ACCT,ACCT_NAME,AMT,CURRENCY,EXC_RATE,BRANCH_ID,DEP_ID,TRN_DESC,TRN_DATE,MAKER_ID_KT)
			VALUES (@p_ET_ID,@p_REQ_PAY_ID,@ENTRY_PAIR,@DR_CR,@DR_CR_NAME,@ACCT,@ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE, @BRANCH_ID, @DEP_ID,@TRN_DESC,GETDATE(),@p_MAKER_ID_KT)

			-- NEU CHUA CO TAI KHOAN THI THEM VO CM_ACCOUNT
			IF(NOT EXISTS(SELECT * FROM CM_ACCOUNT WHERE ACC_NO =@ACCT))
			BEGIN
				INSERT INTO CM_ACCOUNT (ACC_NO, ACC_NAME, TK_GL, TK_GL_NAME, MAKER_ID, CHECKER_ID) VALUES
				(@ACCT,@ACCT_NAME,@GL_CODE,(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@GL_CODE),'admin','admin')
			END
			ELSE
			BEGIN
				UPDATE CM_ACCOUNT SET ACC_NAME =@ACCT_NAME, TK_GL =@GL_CODE, TK_GL_NAME =(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@GL_CODE)
				WHERE ACC_NO=@ACCT
			END
			IF @@error<>0 GOTO ABORT;
		FETCH NEXT FROM XmlData INTO @ENTRY_PAIR ,@DR_CR,@ACCT, @ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE,@BRANCH_ID, @DEP_ID,@TRN_DESC,@GL_CODE
		END;
	CLOSE XmlData;
	DEALLOCATE XmlData;
	END
COMMIT TRANSACTION
--- BAT DAU CAP NHAT XAC DINH CAC BUOC DUYET SAU KHI KE TOAN CAP NHAT THONG TIN
	IF(@p_XMP_TEMP IS NULL)
	BEGIN
		-- CHAN NEU CHI CO 1 BUT NO 1 BUT CO THI KHONG DUOC DI TIEP
		IF(EXISTS(SELECT * FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID))
		BEGIN
			DECLARE  @SUM_CR DECIMAL(18,2), @SUM_DR DECIMAL(18,2)
			SET @SUM_CR =ISNULL((SELECT SUM(AMT*ISNULL(EXC_RATE,1)) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND DR_CR ='C'),0)
			SET @SUM_DR =ISNULL((SELECT SUM(AMT*ISNULL(EXC_RATE,1)) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND DR_CR ='D'),0)
			IF(@SUM_CR<>@SUM_DR)
			BEGIN
			--ROLLBACK TRANSACTION
			--CLOSE XmlData;
			--DEALLOCATE XmlData;
				SELECT '-1' as Result, '' REQ_PAY_ID, N'Tổng hạch toán nợ phải bằng tổng hạch toán có' ErrorDesc
				RETURN '-1'
			END
		END
		UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='P' WHERE REQ_ID=@p_REQ_PAY_ID AND TLNAME=@p_MAKER_ID_KT
		UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='C' WHERE REQ_ID=@p_REQ_PAY_ID AND LEVEL_JOB = CONVERT(VARCHAR(5),@LEVEL_JOB_PREV)
		-- CAP NHAT TINH TRANG VE DANG XU LY
		UPDATE TR_REQ_ADVANCE_PAYMENT SET AUTH_STATUS_KT='P',CREATE_DT_KT =GETDATE() WHERE REQ_PAY_ID=@p_REQ_PAY_ID
		INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)
				   VALUES(@p_REQ_PAY_ID,'SEND',@p_MAKER_ID_KT,GETDATE(), N'Giao dịch viên gửi phê duyệt phiếu',N'Giao dịch viên cập nhật thông tin')
		SELECT '4' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị tạm ứng số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N'đã được gửi phê duyệt thành công! Vui lòng đợi kiểm soát viên phê duyệt phiếu' ErrorDesc
		RETURN '4'
	END
SELECT '0' as Result, @p_REQ_PAY_ID  REQ_PAY_ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		CLOSE XmlData;
		DEALLOCATE XmlData;
		SELECT '-1' as Result, '' REQ_PAY_ID, '' ErrorDesc
		RETURN '-1'
End