DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

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(50), @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(50), 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(@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