DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

ALTER   PROCEDURE [dbo].[TR_PO_MASTER_Upd]

@p_PO_ID	varchar(15) = NULL,

@P_PO_TYPE INT = NULL,

@p_PO_CODE	varchar(15)  = NULL,

@p_PO_NAME	nvarchar(200)  = NULL,

@p_CONTRACT_ID	varchar(15)  = NULL,

@p_SUP_ID	varchar(15)  = NULL,

@p_SUP_NAME	nvarchar(200)  = NULL,

@p_SUP_ADDR	nvarchar(200)  = NULL,

@p_INPUT_DT	VARCHAR(20) = NULL,

@p_PAYMENT_DT	VARCHAR(20) = NULL,

@p_TOTAL_AMT	decimal(18)  = NULL,

@p_REQ_DOC_ID	varchar(15)  = NULL,

@p_DELIVERY_DT	VARCHAR(20) = NULL,

@p_PAYAPP_DT	VARCHAR(20) = NULL,

@p_NOTES	nvarchar(1000)  = NULL,

@p_RECORD_STATUS	varchar(1)  = NULL,

@p_MAKER_ID	varchar(15)  = NULL,

@p_CREATE_DT	VARCHAR(20) = NULL,

@p_AUTH_STATUS	varchar(50)  = NULL,

@p_CHECKER_ID	varchar(15)  = NULL,

@p_APPROVE_DT	VARCHAR(20) = NULL,

@p_TR_REQ_ID VARCHAR(20) = NULL,

@p_TR_REQ_CODE VARCHAR(20)= NULL,

@P_LISTASSET XML = NULL,

@P_LISTPAYMENT XML = NULL,

@P_LISTROLE XML = NULL,

@p_BRANCH_ID varchar(15) = null,

@p_IS_CLOSED VARCHAR(1) = NULL,

-----------------------BAODNQ 2/3/2022 : Thêm tham số----------------------

@p_IS_SEND_APPR VARCHAR(1) = NULL,

@p_SEND_APPR_DT VARCHAR(20) = NULL

AS

	IF(EXISTS(SELECT * FROM TR_PO_MASTER WHERE PO_ID = @p_PO_ID AND AUTH_STATUS = 'U'))

	BEGIN

		SELECT '-1' Result, @p_PO_ID AS PO_ID, 

			N'Không thể chỉnh sửa. PO ' +@p_PO_CODE+ N' đã được gửi yêu cầu phê duyệt' AS ErrorDesc 

		RETURN '-1'

	END

	-----------------------BAODNQ 16/12/2022: User chỉnh sửa ko ph là ng tạo thì ko dc chỉnh sửa----------------

	IF(EXISTS(SELECT PO_ID FROM TR_PO_MASTER WHERE PO_ID = @p_PO_ID AND MAKER_ID <> @p_MAKER_ID))

	BEGIN

		DECLARE @p_PREV_MAKER_ID VARCHAR(20) = (SELECT TOP 1 MAKER_ID FROM TR_PO_MASTER WHERE PO_ID = @p_PO_ID)

		DECLARE @p_PREV_MAKER_NAME NVARCHAR(100) = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @p_PREV_MAKER_ID)

		SELECT '-1' Result, @p_PO_ID AS PO_ID, 

			N'Không thể chỉnh sửa. Chỉ có người tạo PO: ' +@p_PO_CODE+ N' có user domain là: ' + @p_PREV_MAKER_ID + ' - ' + @p_PREV_MAKER_NAME + 

			N' mới được phép chỉnh sửa. Bạn không phải là người tạo PO này' AS ErrorDesc 

		RETURN '-1'

	END

	DECLARE @REF_CODE VARCHAR(15) = NULL, @PDN_TT VARCHAR(15) = NULL

	SET @REF_CODE =

	(	SELECT TOP 1 B.REQ_PAY_CODE

		FROM TR_REQ_ADVANCE_DT A

		INNER JOIN TR_REQ_ADVANCE_PAYMENT B ON A.REQ_PAY_ID = B.REQ_PAY_ID

		WHERE A.REF_ID =@p_PO_ID AND (B.AUTH_STATUS_KT <>'A' OR B.AUTH_STATUS_KT IS NULL)

	)

	--- TIM KIEM XEM PO NAY CO DUOC PHEP THANH TOAN HAY KHONG

	SET @PDN_TT =

	(	SELECT TOP 1 B.REQ_PAY_CODE

		FROM TR_REQ_ADVANCE_DT A

		INNER JOIN TR_REQ_PAYMENT B ON A.REQ_PAY_ID = B.REQ_PAY_ID

		WHERE A.REF_ID =@p_CONTRACT_ID AND (B.AUTH_STATUS_KT <>'A' OR B.AUTH_STATUS_KT IS NULL)

	)

	IF(@p_IS_CLOSED ='Y' AND @REF_CODE IS NOT NULL)

	BEGIN

		--ROLLBACK TRANSACTION

			SELECT '-1' Result, @p_PO_ID AS PO_ID, N'PO đang được thực hiện tạm ứng với số phiếu: '+@REF_CODE AS ErrorDesc 

			RETURN '-1'

	END

	IF(@p_IS_CLOSED ='Y' AND @PDN_TT IS NOT NULL)

	BEGIN

		--ROLLBACK TRANSACTION

			SELECT '-1' Result, @p_PO_ID AS PO_ID, N'PO đang được thực hiện thanh toán với số phiếu: '+@PDN_TT AS ErrorDesc 

			RETURN '-1'

	END

	IF EXISTS(SELECT * FROM TR_PO_MASTER WHERE [PO_CODE] = @p_PO_CODE AND [PO_ID] <> @p_PO_ID)

	BEGIN

		SELECT ErrorCode Result, '' PO_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'PO-00002'

		RETURN '0'

	END

	--- 21052020 KIEM TRA XEM NEU PYCMS DA TON TAI HAY CHUA

	IF((SELECT COUNT(*) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_REQ_DOC_ID)=1)

	BEGIN

			IF(EXISTS(SELECT * FROM TR_PO_MASTER WHERE REQ_DOC_ID =@p_REQ_DOC_ID AND AUTH_STATUS IN ('E','U','A') AND PO_ID <>@p_PO_ID))

			BEGIN

				--ROLLBACK TRANSACTION

				SELECT '-1' Result, @p_PO_ID AS PO_ID, N'Phiếu yêu cầu mua sắm số :'+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_DOC_ID)+

				N' đã được link tới PO có số: '+(SELECT TOP 1 PO_CODE FROM TR_PO_MASTER WHERE REQ_DOC_ID =@p_REQ_DOC_ID AND PO_ID <> @p_PO_ID) AS ErrorDesc 

				RETURN '-1'

			END

	END

	------

	Declare @hdoc INT

	Exec sp_xml_preparedocument @hdoc Output,@P_LISTASSET

	DECLARE AssetDetail CURSOR FOR

	SELECT *

	FROM OPENXML(@hDoc,'/Root/AssetDetail',2)

	WITH 

	(

		PD_ID VARCHAR(15),

		PLAN_ID	varchar(15)  ,

		TRADE_ID	varchar(15)  ,

		REQ_DT_ID VARCHAR(15),

		GOODS_ID	varchar(15)  ,

		[DESCRIPTION] nvarchar(500),

		UNIT_ID	varchar(15)  ,

		QUANTITY	decimal(18)  ,

		PRICE	decimal(18,2)  ,

		TOTAL_AMT	decimal(18,2),

		IS_DELIVERY	varchar(1) ,

		DELIVERY_DT	VARCHAR(20),

		PAYMENT_STATUS	varchar(4),

		AMOUNT_PAID	decimal(18)  ,

		PAID_DT	VARCHAR(20) ,

		INVOICENO	varchar(1000),

		NOTES	nvarchar(1000),

		RECEIVE_BRANCH	varchar(15),

		RECEIVE_ADDR	nvarchar(1000),

		RECEIVE_PERSON	nvarchar(500),

		RECEIVE_TEL	varchar(100),

		EXP_DELIVERY_DT varchar(20),

		GOODS_NAME NVARCHAR(500),

		INVOICE_DT	VARCHAR(20),

		--THIEUVQ 100415 THEM LOAI HANG HOA THUC TE

		GOODSTYPE_REAL	VARCHAR(15),

		GOODSTYPE_REAL_NAME	VARCHAR(15),

		--THIEUVQ 13092016 THEM VAT, CONTRACT_DT

		VAT DECIMAL(18,2),

		PRICE_VAT DECIMAL(18,0),

		CONTRACT_DT VARCHAR(15),

		CURRENCY VARCHAR(15),

		RATE INT,

		TOTAL_AMT_FN  DECIMAL(18,2)

		)

	OPEN AssetDetail

	Exec sp_xml_preparedocument @hdoc Output,@P_LISTPAYMENT

	DECLARE PaymentDetail CURSOR FOR

	SELECT *

	FROM OPENXML(@hDoc,'/Root/PaymentDetail',2)

	WITH 

	(

		PAY_ID	VARCHAR(15),

		PAY_PHASE	varchar(15),

		EXPECTED_DT	VARCHAR(20),

		[PERCENT]	decimal(18,2),

		[AMOUNT]	decimal(18,2),

		NOTES	nvarchar(1000),

		CURRENCY VARCHAR(15),

		RATE INT,

		TOTAL_AMT DECIMAL(18,2)

	)

	OPEN PaymentDetail

	PRINT 'PASS KHOI TAO'

	--Nhom user gui mail (DAO EDIT)

	--Exec sp_xml_preparedocument @hdoc Output,@P_LISTROLE

	--DECLARE LISTROLE CURSOR FOR

	--SELECT *

	--FROM OPENXML(@hDoc,'/Root/LISTROLE',2)

	--WITH 

	--(

	--	NOTIFI_ID	VARCHAR(15),

	--	TL_NAME	varchar(15),

	--	EDITOR_DT VARCHAR(20),

	--	EDITOR_ID VARCHAR(15),

	--	NOTES nvarchar(1000)

	--)

	--OPEN LISTROLE

BEGIN TRANSACTION

		-------------BAODNQ 19/12/2022 : BẢNG TẠM LƯU THÔNG TIN THAY ĐỔI LƯỚI CHI TIẾT HÀNG HÓA PO----------------------

		DECLARE @t_OLD_PO_DETAIL TABLE(

			HH_ID VARCHAR(15), QUANTITY INT, PRICE DECIMAL(18,2), VAT DECIMAL(18,2), TOTAL_AMT DECIMAL(18,2)

		)

		DECLARE @t_NEW_PO_DETAIL TABLE(

			HH_ID VARCHAR(15), QUANTITY INT, PRICE DECIMAL(18,2), VAT DECIMAL(18,2), TOTAL_AMT DECIMAL(18,2)

		)

		-------------ENDBAODNQ 19/12/2022-------------------------------

		IF @p_DELIVERY_DT = ''

			SET @p_DELIVERY_DT = NULL

		IF @p_PAYAPP_DT	= ''

			SET @p_PAYAPP_DT = NULL

		--insert master

		UPDATE TR_PO_MASTER SET [PO_TYPE] = @P_PO_TYPE, [PO_CODE] = @p_PO_CODE,[PO_NAME] = @p_PO_NAME,

		[CONTRACT_ID] = @p_CONTRACT_ID,[SUP_ID] = @p_SUP_ID,[SUP_NAME] = @p_SUP_NAME,[SUP_ADDR] = @p_SUP_ADDR,

		[INPUT_DT] = CONVERT(DATETIME, @p_INPUT_DT, 103),[PAYMENT_DT] = CONVERT(DATETIME, @p_PAYMENT_DT, 103),

		[TOTAL_AMT] = @p_TOTAL_AMT,[REQ_DOC_ID] = @p_TR_REQ_ID,[DELIVERY_DT] = CONVERT(DATETIME, @p_DELIVERY_DT, 103),

		[PAYAPP_DT] = CONVERT(DATETIME, @p_PAYAPP_DT, 103),[NOTES] = @p_NOTES,[RECORD_STATUS] = '1',

		[MAKER_ID] = @p_MAKER_ID,[CREATE_DT] = CONVERT(DATETIME, @p_CREATE_DT, 103),[AUTH_STATUS] = 'E',

		[CHECKER_ID] = @p_CHECKER_ID,[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103), BRANCH_ID = @p_BRANCH_ID,

		-----------------------BAODNQ 2/3/2022 : Thêm cột IS_SEND_APPR, SEND_APPR_DT----------------------

		[IS_SEND_APPR] = @p_IS_SEND_APPR,

		[SEND_APPR_DT] = CONVERT(DATETIME, @p_SEND_APPR_DT, 103),

		IS_CLOSED = @p_IS_CLOSED

		WHERE  PO_ID= @p_PO_ID

		IF @@Error <> 0 GOTO ABORT

		PRINT 'INSERT MASTER SUCCESS'

		UPDATE dbo.TR_PO_MASTER_TEMP SET TR_REQ_ID=@p_TR_REQ_ID,TR_REQ_CODE=@p_TR_REQ_CODE WHERE PO_ID=@p_PO_ID

		--insert asset detail tương đương như NỘI DUNG trong bảng contract detail

		Declare 

		@PAY_ID VARCHAR(15),

		@PD_ID VARCHAR(15),

		@PLAN_ID	varchar(15),

		@TRADE_ID	varchar(15),

		@REQ_DT_ID VARCHAR(15),

		@GOODS_ID	varchar(15),

		@DESCRIPTION nvarchar(500),

		@UNIT_ID	varchar(15),

		@QUANTITY	decimal(18),

		@PRICE	decimal(18,2),

		@TOTAL_AMT	decimal(18,2),

		@IS_DELIVERY	varchar(1),

		@DELIVERY_DT	VARCHAR(20),

		@PAYMENT_STATUS	varchar(4),

		@AMOUNT_PAID	decimal(18),

		@PAID_DT	VARCHAR(20),

		@INVOICENO	varchar(20),

		@NOTES	nvarchar(1000),

		@PO_ID	varchar(15)  = NULL,

		@PAY_PHASE	VARCHAR(20) = NULL,

		@EXPECTED_DT	VARCHAR(20) = NULL,

		@PERCENT	decimal(18,2)  = NULL,

		@AMOUNT	decimal(18,2)  = NULL,

		@RECEIVE_BRANCH	varchar(15)=NULL,

		@RECEIVE_ADDR	nvarchar(1000)=NULL,

		@RECEIVE_PERSON	nvarchar(500)=NULL,

		@RECEIVE_TEL	varchar(100)=NULL,

		@EXP_DELIVERY_DT varchar(20) = NULL,

		@GOODS_NAME NVARCHAR(500) = NULL,

		@INVOICE_DT	VARCHAR(20),

		@GOODSTYPE_REAL	VARCHAR(15) = NULL,

		@GOODSTYPE_REAL_NAME	VARCHAR(15) = NULL,

		@VAT DECIMAL(18,2),

		@PRICE_VAT DECIMAL(18,0),

		@CONTRACT_DT VARCHAR(15),

		--DAO MOI THEM

		@NOTIFI_ID VARCHAR(15),

		@TL_NAME VARCHAR(15),

		@EDITOR_DT VARCHAR(20),

		@EDITOR_ID VARCHAR(15),

		@NOTES_ROLE nvarchar(1000),

		@CURRENCY VARCHAR(15),

		@RATE INT,

		@TOTAL_AMT_FN DECIMAL(18,2)

		DECLARE @l_GOODSTYPE_ID VARCHAR(15)

		DELETE FROM TR_PO_DETAIL WHERE PO_ID = @P_PO_ID 

		FETCH NEXT FROM AssetDetail INTO @PD_ID,@PLAN_ID,@TRADE_ID,@REQ_DT_ID,@GOODS_ID,@DESCRIPTION,@UNIT_ID,@QUANTITY,

		@PRICE,@TOTAL_AMT,@IS_DELIVERY,@DELIVERY_DT,@PAYMENT_STATUS,@AMOUNT_PAID,@PAID_DT,

		@INVOICENO,@NOTES, @RECEIVE_BRANCH, @RECEIVE_ADDR, @RECEIVE_PERSON, @RECEIVE_TEL,@EXP_DELIVERY_DT,@GOODS_NAME, @INVOICE_DT,

		@GOODSTYPE_REAL,@GOODSTYPE_REAL_NAME,@VAT,@PRICE_VAT,@CONTRACT_DT,@CURRENCY,@RATE,@TOTAL_AMT_FN

		WHILE @@FETCH_STATUS = 0

		BEGIN

			--if( NOT EXISTS (SELECT * FROM PL_MASTER A WHERE A.PLAN_ID = @PLAN_ID))

			--BEGIN

			--	SELECT ErrorCode Result, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'TR-00002'

			--	GOTO ABORT

			--END

					---------------------BAODNQ 19/12/2022 : GHI LẠI NHỮNG THÔNG TIN THAY ĐỔI VÀO LOG------------------------------

			DECLARE @d_OLD_QUANTITY DECIMAL = (SELECT QUANTITY FROM TR_PO_DETAIL WHERE PD_ID = @PD_ID),

					@d_OLD_PRICE DECIMAL(18,2) = (SELECT PRICE FROM TR_PO_DETAIL WHERE PD_ID = @PD_ID),

					@d_OLD_VAT DECIMAL(18,2) = (SELECT VAT FROM TR_PO_DETAIL WHERE PD_ID = @PD_ID),

					@d_OLD_TOTAL_AMT DECIMAL(18,2) = (SELECT TOTAL_AMT FROM TR_PO_DETAIL WHERE PD_ID = @PD_ID)

			IF(@d_OLD_QUANTITY <> @QUANTITY OR @d_OLD_PRICE <> @PRICE OR @d_OLD_VAT <> @VAT OR @d_OLD_TOTAL_AMT <> @TOTAL_AMT)

			BEGIN

				INSERT INTO @t_OLD_PO_DETAIL VALUES(@GOODS_ID, @d_OLD_QUANTITY, @d_OLD_PRICE, @d_OLD_VAT, @d_OLD_TOTAL_AMT)

				INSERT INTO @t_NEW_PO_DETAIL VALUES(@GOODS_ID, @QUANTITY, @PRICE, @VAT, @TOTAL_AMT)

			END

			------------DELETE TR_PO_DETAIL ĐỂ INSERT LẠI-----------

			--DELETE FROM TR_PO_DETAIL WHERE PD_ID = @PD_ID AND PO_ID = @P_PO_ID

			--------------------------------ENDBAODNQ 19/12/2022--------------------------------

			IF(LEN(@PD_ID) = 0)

			BEGIN

				EXEC SYS_CodeMasters_Gen 'TR_PO_DETAIL', @PD_ID out

				IF @PD_ID ='' OR @PD_ID IS NULL GOTO ABORT

			END

			IF(@DELIVERY_DT = '')

				SET @DELIVERY_DT = NULL

			IF(@PAID_DT = '')

				SET @PAID_DT = NULL

			IF(@INVOICE_DT = '')

				SET @INVOICE_DT = NULL				

			IF @EXP_DELIVERY_DT = '' SET @EXP_DELIVERY_DT = NULL

			/***THIEUVQ THEM KIEM TRA VA LUU THONG TIN LOAI HANG HOA THUC TE***/

			--BEGIN

			--CHUA CO LOAI HANG HOA THI THEM MOI

			IF @GOODSTYPE_REAL IS NULL OR @GOODSTYPE_REAL = ''

			BEGIN

				--NEU TON TAI THI LAY RA ID

				SET @GOODSTYPE_REAL = (SELECT TOP 1 GD_RETYPE_ID FROM CM_GOODSTYPE_REAL WHERE [TYPE_NAME] = @GOODSTYPE_REAL_NAME)

				--NEU CHUA CO THI THEM MOI

				IF @GOODSTYPE_REAL IS NULL OR @GOODSTYPE_REAL = ''

				BEGIN

					EXEC SYS_CodeMasters_Gen 'CM_GOODSTYPE_REAL', @l_GOODSTYPE_ID OUT

					IF @l_GOODSTYPE_ID='' OR @l_GOODSTYPE_ID IS NULL GOTO ABORT

					INSERT INTO CM_GOODSTYPE_REAL VALUES (@l_GOODSTYPE_ID, @l_GOODSTYPE_ID, @GOODSTYPE_REAL_NAME, '', '1',

					@p_MAKER_ID,CONVERT(DATETIME, @p_CREATE_DT, 103),'A',@p_MAKER_ID,CONVERT(DATETIME, @p_CREATE_DT, 103))

					IF @@ERROR <> 0 GOTO ABORT

					SET @GOODSTYPE_REAL = @l_GOODSTYPE_ID

				END

			END			

			--END

			INSERT INTO TR_PO_DETAIL([PD_ID],[PO_ID],[PLAN_ID],[TRADE_ID],[REQ_DT_ID],[GOODS_ID],[DESCRIPTION],[UNIT_ID],[QUANTITY],[PRICE],[TOTAL_AMT],[IS_DELIVERY],[DELIVERY_DT],

			[PAYMENT_STATUS],[AMOUNT_PAID],[PAID_DT],[INVOICENO],[NOTES], RECEIVE_BRANCH, RECEIVE_ADDR, RECEIVE_PERSON, RECEIVE_TEL,[RECORD_STATUS],[MAKER_ID],[CREATE_DT],

			[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],EXP_DELIVERY_DT,GOODS_NAME,INVOICE_DT,GOODSTYPE_REAL,VAT,PRICE_VAT,CONTRACT_DT,CURRENCY,RATE,TOTAL_AMT_FN)

			VALUES(@PD_ID ,@p_PO_ID ,@PLAN_ID ,@TRADE_ID,@REQ_DT_ID ,@GOODS_ID,@DESCRIPTION ,@UNIT_ID ,@QUANTITY ,@PRICE ,@TOTAL_AMT ,

			@IS_DELIVERY ,CONVERT(DATETIME, @DELIVERY_DT, 103) ,@PAYMENT_STATUS ,@AMOUNT_PAID ,

			CONVERT(DATETIME, @PAID_DT, 103) ,@INVOICENO ,@NOTES , @RECEIVE_BRANCH, @RECEIVE_ADDR, @RECEIVE_PERSON, @RECEIVE_TEL,

			@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103),CONVERT(DATETIME, @EXP_DELIVERY_DT, 103),

			@GOODS_NAME, CONVERT(DATETIME, @INVOICE_DT, 103),@GOODSTYPE_REAL,@VAT,@PRICE_VAT,@CONTRACT_DT,ISNULL(@CURRENCY,'VND'),ISNULL(@RATE,1) ,@TOTAL_AMT*ISNULL(@RATE,1) )

			IF @@ERROR <> 0 GOTO ABORT

		-- next Group_Id

			FETCH NEXT FROM AssetDetail INTO @PD_ID,@PLAN_ID,@TRADE_ID,@REQ_DT_ID,@GOODS_ID,@DESCRIPTION,@UNIT_ID,@QUANTITY,

			@PRICE,@TOTAL_AMT,@IS_DELIVERY,@DELIVERY_DT,@PAYMENT_STATUS,@AMOUNT_PAID,@PAID_DT,

			@INVOICENO,@NOTES, @RECEIVE_BRANCH, @RECEIVE_ADDR, @RECEIVE_PERSON, @RECEIVE_TEL, @EXP_DELIVERY_DT,@GOODS_NAME,@INVOICE_DT,

			@GOODSTYPE_REAL,@GOODSTYPE_REAL_NAME,@VAT,@PRICE_VAT,@CONTRACT_DT,@CURRENCY,@RATE,@TOTAL_AMT_FN

		END

		--insert payment detail

		DELETE FROM TR_PO_PAYMENT WHERE PO_ID = @p_PO_ID

		FETCH NEXT FROM PaymentDetail INTO @PAY_ID,@PAY_PHASE,@EXPECTED_DT,@PERCENT,@AMOUNT,@NOTES,@CURRENCY,@RATE,@TOTAL_AMT_FN

		WHILE @@FETCH_STATUS = 0

		BEGIN

			--EXEC SYS_CodeMasters_Gen 'TR_PO_PAYMENT', @PAY_ID out

			--IF @PAY_ID ='' OR @PAY_ID IS NULL GOTO ABORT

			--PRINT @PAY_ID

			IF(LEN(@PAY_ID) = 0 OR @PAY_ID IS NULL OR @PAY_ID = '')

			BEGIN

					EXEC SYS_CodeMasters_Gen 'TR_PO_PAYMENT', @PAY_ID out

					IF @PAY_ID ='' OR @PAY_ID IS NULL GOTO ABORT

			END

			IF @EXPECTED_DT = ''

				SET @EXPECTED_DT = NULL

			--INSERT INTO TR_PO_PAYMENT([PAY_ID],[PO_ID],[PAY_PHASE],[EXP_DT],[PERCENT],[AMOUNT],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],CURRENCY,RATE,TOTAL_AMT)

			--VALUES(@PAY_ID ,@p_PO_ID ,@PAY_PHASE ,CONVERT(DATETIME, @EXPECTED_DT, 103) ,@PERCENT ,@AMOUNT,@NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103),ISNULL(@CURRENCY,'VND'),ISNULL(@RATE,1) ,@AMOUNT*ISNULL(@RATE,1))

			-------------BAODNQ 15/9/2022: Fix lại theo BVB_v2_PROD-----------

			-------------Nếu @PAY_ID ko tồn tại trong TR_PO_PAYMENT, INSERT mới---------

			IF(NOT EXISTS(SELECT * FROM TR_PO_PAYMENT WHERE PAY_ID = @PAY_ID))

			BEGIN

				INSERT INTO TR_PO_PAYMENT([PAY_ID],[PO_ID],[PAY_PHASE],[EXP_DT],[PERCENT],[AMOUNT],[NOTES],

					[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],

					CURRENCY,RATE,TOTAL_AMT)

				VALUES(@PAY_ID ,@p_PO_ID ,@PAY_PHASE ,CONVERT(DATETIME, @EXPECTED_DT, 103) ,@PERCENT ,

					@AMOUNT,@NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,

					@p_AUTH_STATUS ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103),

					ISNULL(@CURRENCY,'VND'),ISNULL(@RATE,1) ,@AMOUNT*ISNULL(@RATE,1))

			END

			ELSE

			-------------Nếu @PAY_ID đã tồn tại trong TR_PO_PAYMENT, UPDATE---------

			BEGIN

				UPDATE TR_PO_PAYMENT SET 

					PO_ID =@p_PO_ID, 

					PAY_PHASE =@PAY_PHASE, 

					EXP_DT =CONVERT(DATETIME, @EXPECTED_DT, 103), 

					[PERCENT] =@PERCENT,

					AMOUNT =@TOTAL_AMT_FN / ISNULL(@RATE,1), 

					RECORD_STATUS =1, 

					AUTH_STATUS ='A',

					CURRENCY =ISNULL(@CURRENCY,'VND'), 

					RATE =ISNULL(@RATE,1) ,

					TOTAL_AMT =@AMOUNT*ISNULL(@RATE,1), 

					NOTES =@NOTES, 

					MAKER_ID =@p_MAKER_ID, 

					CREATE_DT =CONVERT(DATETIME, @p_CREATE_DT, 103)

				WHERE PAY_ID =@PAY_ID --- XU LY TRONG TRUONG HOP TRUNG KEY THI CAP NHAT KY THANH TOAN 202297

			END

			UPDATE TR_PO_PAYMENT SET TOTAL_AMT = AMOUNT* ISNULL(RATE, 1) WHERE PAY_ID =@PAY_ID

			FETCH NEXT FROM PaymentDetail INTO @PAY_ID,@PAY_PHASE,@EXPECTED_DT,@PERCENT,@AMOUNT,@NOTES,@CURRENCY,@RATE,@TOTAL_AMT_FN 

		END

		CLOSE AssetDetail

		DEALLOCATE AssetDetail

		CLOSE PaymentDetail

		DEALLOCATE PaymentDetail

		---INSERT NHOM QUYEN USER THONG BAO (DAO-EDIT)

		--DELETE FROM TL_ROLE_NOTIFICATION WHERE PO_ID=@p_PO_ID

	 --  FETCH NEXT FROM LISTROLE INTO

		--@NOTIFI_ID,

		--@TL_NAME,

		--@EDITOR_DT,

		--@EDITOR_ID,

		--@NOTES_ROLE

		--WHILE @@FETCH_STATUS = 0

		--BEGIN

		--	IF(LEN(@NOTIFI_ID)=0)

		--	EXEC SYS_CodeMasters_Gen 'TL_ROLE_NOTIFICATION', @NOTIFI_ID out

		--	IF @NOTIFI_ID='' OR @NOTIFI_ID IS NULL GOTO ABORT

		--	INSERT INTO [dbo].[TL_ROLE_NOTIFICATION]

  --         ([NOTIFI_ID]

  --         ,[NOTIFI_CODE]

  --         ,[NOTIFI_NAME]

  --         ,[TYPE]

  --         ,[PO_ID]

  --         ,[TL_NAME]

  --         ,[BRANCH_ID]

  --         ,[RECORD_STATUS]

  --         ,[AUTH_STATUS]

  --         ,[EDITOR_ID]

  --         ,[EDIT_DT]

  --         ,[NOTES])

		--	 VALUES

  --         (@NOTIFI_ID

  --         ,''

  --         ,''

  --         ,'PO'

  --         ,@p_PO_ID

  --         ,@TL_NAME

  --         ,@p_BRANCH_ID

  --         ,'1'

  --         ,'U'

  --         ,@EDITOR_ID

  --         ,CONVERT(DATETIME, @EDITOR_DT, 103)

  --         ,@NOTES_ROLE)

		--	IF @@ERROR <> 0 GOTO ABORT

		--	FETCH NEXT FROM LISTROLE INTO

		--	@NOTIFI_ID,

		--	@TL_NAME,

		--	@EDITOR_DT,

		--	@EDITOR_ID,

		--	@NOTES_ROLE

		--END

		--CLOSE LISTROLE

		--DEALLOCATE LISTROLE

		--- BAT DAU CAP NHAT AUTH_STATUS ='U' NEU BAM GUI PHE DUYET

		IF(@p_RECORD_STATUS ='U')

		BEGIN

			UPDATE TR_PO_MASTER SET AUTH_STATUS ='U' WHERE PO_ID =@p_PO_ID

		END

		DECLARE @p_MSG_PROCESS NVARCHAR(MAX)

		-------------------BAODNQ 19/12/2022 : LƯU LOG THÔNG TIN THAY ĐỔI---------------------

		IF((SELECT COUNT(*) FROM @t_OLD_PO_DETAIL) <> 0)

		BEGIN

			DECLARE @c_HH_ID VARCHAR(15),

					@c_OLD_QUANTITY INT,

					@c_OLD_PRICE DECIMAL(18,2),

					@c_OLD_VAT DECIMAL(18,2),

					@c_OLD_TOTAL_AMT DECIMAL(18,2)

			SET @p_MSG_PROCESS = N'USER: ' +@p_MAKER_ID+ N' thực hiện chỉnh sửa thông tin PO, lưới chi tiết hàng hóa:'

			DECLARE OldPoDetailCursor CURSOR FOR

				SELECT HH_ID, QUANTITY, PRICE, VAT, TOTAL_AMT FROM @t_OLD_PO_DETAIL

			OPEN OldPoDetailCursor

			FETCH NEXT FROM OldPoDetailCursor INTO 

				@c_HH_ID, @c_OLD_QUANTITY, @c_OLD_PRICE, @c_OLD_VAT, @c_OLD_TOTAL_AMT

			WHILE @@FETCH_STATUS = 0

			BEGIN

				DECLARE @c_NEW_QUANTITY INT,

						@c_NEW_PRICE DECIMAL(18,2),

						@c_NEW_VAT DECIMAL(18,2)

				DECLARE @c_HH_NAME NVARCHAR(200) = (SELECT TOP 1 HH_NAME FROM CM_HANGHOA WHERE HH_ID = @c_HH_ID)

				SET @c_NEW_QUANTITY = (SELECT TOP 1 QUANTITY FROM @t_NEW_PO_DETAIL WHERE HH_ID = @c_HH_ID)

				SET @c_NEW_PRICE = (SELECT TOP 1 PRICE FROM @t_NEW_PO_DETAIL WHERE HH_ID = @c_HH_ID)

				SET @c_NEW_VAT = (SELECT TOP 1 VAT FROM @t_NEW_PO_DETAIL WHERE HH_ID = @c_HH_ID)

				SET @p_MSG_PROCESS = @p_MSG_PROCESS

					+CHAR(10)+ N'- Hàng hóa: ' + @c_HH_NAME + N':'

					+CHAR(10)+ N'   Số lượng: chỉnh sửa từ ' +CONVERT(VARCHAR,ISNULL(@c_OLD_QUANTITY,0))+ N' thành ' + CONVERT(VARCHAR,ISNULL(@c_NEW_QUANTITY,0))

					+CHAR(10)+ N'   Đơn giá: chỉnh sửa từ ' +CONVERT(VARCHAR,FORMAT(ISNULL(@c_OLD_PRICE, 0), '#,#', 'vi-VN'))+ N' thành ' + CONVERT(VARCHAR,FORMAT(ISNULL(@c_NEW_PRICE, 0), '#,#', 'vi-VN'))

					+CHAR(10)+ N'   VAT(%): chỉnh sửa từ ' +CONVERT(VARCHAR,CAST(@c_OLD_VAT AS INT))+ N' thành ' + CONVERT(VARCHAR,CAST(@c_NEW_VAT AS INT))

				FETCH NEXT FROM OldPoDetailCursor INTO 

					@c_HH_ID, @c_OLD_QUANTITY, @c_OLD_PRICE, @c_OLD_VAT, @c_OLD_TOTAL_AMT

			END

		END

		ELSE

		BEGIN

			SET @p_MSG_PROCESS = N'Cập nhật phiếu gọi hàng thành công'

		END

		----------------ENDBAODNQ 19/12/2022--------------------

		------------------------BAODNQ 1/3/2022: Insert 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_PO_ID,        -- REQ_ID - varchar(15)

						'UPDATE',        -- PROCESS_ID - varchar(10)

						@p_MAKER_ID,        -- CHECKER_ID - varchar(15)

						GETDATE(), -- APPROVE_DT - datetime

					    @p_MSG_PROCESS,

					   N'Cập nhật phiếu gọi hàng'      -- PROCESS_DESC - nvarchar(1000)

					)

COMMIT TRANSACTION

IF(@p_RECORD_STATUS ='U')

BEGIN

	IF(NOT EXISTS(SELECT * FROM TR_PO_DETAIL WHERE PO_ID =@p_PO_ID))

	BEGIN

		SELECT '-1' as Result, @p_PO_ID  PO_ID, N'PO số: '+ @p_PO_CODE +N' Vui lòng nhập thông tin hàng hóa gọi hàng' ErrorDesc

		RETURN '-1'

	END

	IF(NOT EXISTS(SELECT * FROM TR_PO_PAYMENT WHERE PO_ID =@p_PO_ID))

	BEGIN

		SELECT '-1' as Result, @p_PO_ID  PO_ID, N'PO số: '+ @p_PO_CODE +N' Vui lòng nhập thông tin lịch thanh toán' ErrorDesc

		RETURN '-1'

	END

	-- INSERT VAO LOG

	INSERT INTO dbo.PL_PROCESS

	(

						REQ_ID,

						PROCESS_ID,

						CHECKER_ID,

						APPROVE_DT,

						PROCESS_DESC,NOTES

					)

					VALUES

					(   @p_PO_ID,        -- REQ_ID - varchar(15)

						'SEND',        -- PROCESS_ID - varchar(10)

						@p_MAKER_ID,        -- CHECKER_ID - varchar(15)

						GETDATE(), -- APPROVE_DT - datetime

					   N'Nhân viên gửi phê duyệt phiếu thành công' ,N'Gửi phê duyệt')

	SELECT '2' as Result, @p_PO_ID  PO_ID, N'PO số: '+ @p_PO_CODE +N' đã được gửi phê duyệt thành công. Vui lòng đợi cấp có thẩm quyền phê duyệt' ErrorDesc

	RETURN '2'

END

ELSE

BEGIN

	SELECT '0' as Result, @p_PO_ID  PO_ID, N'PO số: '+ @p_PO_CODE +N' đã được cập nhật dữ liệu thành công. Bạn có thể bấm gửi phê duyệt' ErrorDesc

	RETURN '0'

END

ABORT:

BEGIN

		CLOSE AssetDetail

		DEALLOCATE AssetDetail

		CLOSE PaymentDetail

		DEALLOCATE PaymentDetail

		--CLOSE LISTROLE

		--DEALLOCATE LISTROLE

		ROLLBACK TRANSACTION

		SELECT '-1' AS RESULT

		RETURN '-1'

End

--SELECT * FROM TR_CONTRACT_DT

--SELECT * FROM TR_CONTRACT_PAYMENT

--SELECT * FROM TR_CONTRACT