USE [gAMSPro_VietcapitalBank_v2]
GO
/****** Object:  StoredProcedure [dbo].[TR_PO_MASTER_Upd]    Script Date: 16-Aug-20 15:10:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
SELECT * FROM TR_PO_DETAIL where PO_ID = 'TRPM00000000052'
SELECT * FROM TR_PO_MASTER WHERE PO_ID = 'TRPM00000000052'
SELECT * FROM TR_PO_PAYMENT WHERE PO_ID = 'TRPM00000000052'
SELECT * FROM TR_PO_PAYMENT
[TR_PO_MASTER_Upd] 'TRPM00000000052',1,'','MUA HANG DOT 1','','','','','11/11/2013','11/11/2013',0,'','12/12/2013','12/12/2013','','','','','','','',
'<Root xmlns="">
  <AssetDetail>
	<PD_ID>TRPD00000000156</PD_ID>
	<TRADE_ID>PLT0000001</TRADE_ID>
	<GOODS_ID>CMG00001</GOODS_ID>
	<DESCRIPTION>Dien giai</DESCRIPTION>
    <UNIT_ID>CMU00001</UNIT_ID>
    <QUANTITY>2</QUANTITY>
    <PRICE>49500000</PRICE>
	<TOTAL_AMT>49500000</TOTAL_AMT>
	<IS_DELIVERY>N</IS_DELIVERY>
	<DELIVERY_DT>30/10/2011</DELIVERY_DT>
	<PAYMENT_STATUS>N</PAYMENT_STATUS>
	<AMOUNT_PAID>30000000</AMOUNT_PAID>
	<PAID_DT>29/11/2013</PAID_DT>
	<INVOICENO>00001</INVOICENO>
    <NOTES>GHI CHU MANG TINH CHAT MINH HOA</NOTES>
  </AssetDetail>
</Root>'
,NULL

*/
/*
declare @p24 xml
set @p24=convert(xml,N'<Root xmlns=""><AssetDetail><PD_ID>TRPD00000000325</PD_ID><PLAN_ID>PLM000000000144</PLAN_ID><TRADE_ID>PLT000000000030</TRADE_ID><GOODS_ID>GOO000000000185</GOODS_ID><UNIT_ID>CMU000000000150</UNIT_ID><QUANTITY>1</QUANTITY><PRICE>2700000</PRICE><TOTAL_AMT>2700000</TOTAL_AMT><IS_DELIVERY>0</IS_DELIVERY><DELIVERY_DT>01/01/2014</DELIVERY_DT><PAYMENT_STATUS>CTT</PAYMENT_STATUS><AMOUNT_PAID>0</AMOUNT_PAID><PAID_DT>01/01/2014</PAID_DT><INVOICENO/><NOTES/></AssetDetail><AssetDetail><PD_ID>TRPD00000000326</PD_ID><PLAN_ID>PLM000000000144</PLAN_ID><TRADE_ID>PLT000000000031</TRADE_ID><GOODS_ID>GOO000000000186</GOODS_ID><UNIT_ID>CMU000000000150</UNIT_ID><QUANTITY>1</QUANTITY><PRICE>1600000</PRICE><TOTAL_AMT>1600000</TOTAL_AMT><IS_DELIVERY>0</IS_DELIVERY><DELIVERY_DT>02/01/2014</DELIVERY_DT><PAYMENT_STATUS>CTT</PAYMENT_STATUS><AMOUNT_PAID>0</AMOUNT_PAID><PAID_DT>02/01/1900</PAID_DT><INVOICENO/><NOTES/></AssetDetail><AssetDetail><PD_ID>TRPD00000000327</PD_ID><PLAN_ID>PLM000000000144</PLAN_ID><TRADE_ID>PLT000000000033</TRADE_ID><GOODS_ID>GOO000000000188</GOODS_ID><UNIT_ID>CMU000000000150</UNIT_ID><QUANTITY>1</QUANTITY><PRICE>1500000</PRICE><TOTAL_AMT>1500000</TOTAL_AMT><IS_DELIVERY>0</IS_DELIVERY><DELIVERY_DT>04/01/2014</DELIVERY_DT><PAYMENT_STATUS>CTT</PAYMENT_STATUS><AMOUNT_PAID>0</AMOUNT_PAID><PAID_DT>03/01/1900</PAID_DT><INVOICENO/><NOTES/></AssetDetail><AssetDetail><PD_ID>TRPD00000000328</PD_ID><PLAN_ID>PLM000000000144</PLAN_ID><TRADE_ID>PLT000000000035</TRADE_ID><GOODS_ID>GOO000000000190</GOODS_ID><UNIT_ID>CMU000000000150</UNIT_ID><QUANTITY>1</QUANTITY><PRICE>2500000</PRICE><TOTAL_AMT>2500000</TOTAL_AMT><IS_DELIVERY>0</IS_DELIVERY><DELIVERY_DT>05/01/2014</DELIVERY_DT><PAYMENT_STATUS>CTT</PAYMENT_STATUS><AMOUNT_PAID>0</AMOUNT_PAID><PAID_DT>05/01/1900</PAID_DT><INVOICENO/><NOTES/></AssetDetail></Root>')
declare @p25 xml
set @p25=convert(xml,N'<Root xmlns=""><PaymentDetail><PAY_ID></PAY_ID><PAY_PHASE>1</PAY_PHASE><EXPECTED_DT>18/10/2013</EXPECTED_DT><PERCENT>100</PERCENT><AMOUNT>8300000</AMOUNT><NOTES/></PaymentDetail></Root>')
declare @p26 int
set @p26=0
exec sp_executesql N'EXEC @RETURN_VALUE = [dbo].[TR_PO_MASTER_Upd] @p_PO_ID = @p0, @P_PO_TYPE = @p1, @p_PO_CODE = @p2, @p_PO_NAME = @p3, @p_CONTRACT_ID = @p4, @p_SUP_ID = @p5, @p_SUP_NAME = @p6, @p_SUP_ADDR = @p7, @p_INPUT_DT = @p8, @p_PAYMENT_DT = @p9, @p_TOTAL_AMT = @p10, @p_REQ_DOC_ID = @p11, @p_DELIVERY_DT = @p12, @p_PAYAPP_DT = @p13, @p_NOTES = @p14, @p_RECORD_STATUS = @p15, @p_MAKER_ID = @p16, @p_CREATE_DT = @p17, @p_AUTH_STATUS = @p18, @p_CHECKER_ID = @p19, @p_APPROVE_DT = @p20, @P_LISTASSET = @p21, @P_LISTPAYMENT = @p22',N'@p0 varchar(8000),@p1 int,@p2 varchar(8000),@p3 nvarchar(4000),@p4 varchar(8000),@p5 varchar(8000),@p6 nvarchar(4000),@p7 nvarchar(4000),@p8 varchar(8000),@p9 varchar(8000),@p10 decimal(18,0),@p11 varchar(8000),@p12 varchar(8000),@p13 varchar(8000),@p14 nvarchar(4000),@p15 varchar(8000),@p16 varchar(8000),@p17 varchar(8000),@p18 varchar(8000),@p19 varchar(8000),@p20 varchar(8000),@p21 xml,@p22 xml,@RETURN_VALUE int output',@p0='TRPM00000000103',@p1=1,@p2='PO5555',@p3=N'Mua sam TB VP',@p4='TRC000000000035',@p5=NULL,@p6=N'',@p7=N'',@p8='15/10/2013',@p9='01/01/1900',@p10=8300000,@p11='FFFFF',@p12='01/10/2013',@p13='10/10/2013',@p14=NULL,@p15='1',@p16='taidt',@p17='15/10/2013',@p18='U',@p19=NULL,@p20='01/01/1900',@p21=@p24,@p22=@p25,@RETURN_VALUE=@p26 output
*/
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
AS
	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) 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)  ,
		GOODS_ID	varchar(15)  ,
		[DESCRIPTION] nvarchar(500),
		UNIT_ID	varchar(15)  ,
		QUANTITY	decimal(18)  ,
		PRICE	decimal(18)  ,
		TOTAL_AMT	decimal(18),
		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),
		[AMOUNT]	decimal(18),
		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
		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] = @p_AUTH_STATUS,
		[CHECKER_ID] = @p_CHECKER_ID,[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103), BRANCH_ID = @p_BRANCH_ID
		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),
		@GOODS_ID	varchar(15),
		@DESCRIPTION nvarchar(500),
		@UNIT_ID	varchar(15),
		@QUANTITY	decimal(18),
		@PRICE	decimal(18),
		@TOTAL_AMT	decimal(18),
		@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)  = NULL,
		@AMOUNT	decimal(18)  = 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,@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

			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],[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 ,@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,@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)
			--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) ,@TOTAL_AMT*ISNULL(@RATE,1))
			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
COMMIT TRANSACTION
IF(@p_RECORD_STATUS ='U')
BEGIN
	-- 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