ALTER PROCEDURE [dbo].[TR_REQUEST_DOC_DVMS_Upd]
@p_REQ_ID varchar(15),
@p_REQ_CODE	nvarchar(100)  = NULL,
@p_REQ_NAME	nvarchar(200)  = NULL,
@p_REQ_DT	DATETIME = NULL,
@p_REQ_TYPE	int = NULL,
@p_REQ_CONTENT NVARCHAR(1000)=NULL,
@p_REQ_REASON NVARCHAR(500)=NULL,
@p_PL_REQ_ID VARCHAR(15),
@p_TOTAL_AMT	decimal(18, 2) = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(12)  = NULL,
@p_CREATE_DT	DATETIME = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(12)  = NULL,
@p_APPROVE_DT	DATETIME = NULL,
@p_BRANCH_DO VARCHAR(15)=NULL,
@p_BRANCH_CREATE VARCHAR(15)=NULL,
@p_USER_REQUEST VARCHAR(15)=NULL,
@p_ListGood XML,
@p_ListCostCenter XML
AS
	--IF EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE [REQ_CODE] = @p_REQ_CODE AND REQ_ID <> @p_REQ_ID)
	--BEGIN
	--	SELECT ErrorCode Result, '' REQ_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'REQ-00001'
	--	RETURN '0'
	--END
	DECLARE @sErrorCode VARCHAR(20)
	
  BEGIN TRANSACTION

		UPDATE dbo.TR_REQUEST_DOC
		SET BRANCH_DO=@p_BRANCH_DO,USER_DVMS=@p_MAKER_ID
		WHERE REQ_ID=@p_REQ_ID
		IF @@Error <> 0 GOTO ABORT


		--DELETE FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID
		DELETE FROM dbo.TR_REQUEST_COSTCENTER WHERE REQ_ID=@p_REQ_ID
		--Insert into TABLE PL_REQUEST_DOC_DT
		Declare @hdoc INT
		Exec sp_xml_preparedocument @hdoc Output,@p_ListGood
		DECLARE ListGoods  CURSOR FOR
		SELECT *
		FROM OPENXML(@hDoc,'/Root/ListGood',2)
		WITH 
		(
			REQDT_ID 	varchar(15)  ,
			PL_REQDT_ID	varchar(15)  ,
			GOODS_ID	varchar(15)  ,
			[DESCRIPTION] nvarchar(500),
			QUANTITY	decimal(18, 0)  ,
			PRICE	decimal(18, 2)  ,
			TOTAL_AMT	decimal(18, 2),	
			NOTES	nvarchar(1000),
			REQ_DT DATETIME,
			AMORT_MONTH DECIMAL(18,2),
			TRADE_TYPE_ID varchar(15),
			SUP_ID varchar(15),
			BID_ID VARCHAR(20),
			SUP_NAME NVARCHAR(2000),
			OFFERING_VALUE decimal(18, 2),
			NOTE_DVMS NVARCHAR(500),
			PO_ID VARCHAR(15),
			PO_AMT DECIMAL(18,2),
			TOTAL_AMT_ETM DECIMAL(18,2)
		)
		OPEN ListGoods

		Declare 
		@REQDT_ID 	varchar(15)  ,
		@PL_REQDT_ID	varchar(15),
		@SUP_ID	varchar(15),
		@GOODS_ID	varchar(15),
		@DESCRIPTION nvarchar(500),
		@QUANTITY	decimal(18, 0),
		@PRICE	decimal(18, 2),
		@TOTAL_AMT	decimal(18, 2),		
		@NOTES	nvarchar(1000),
		@TRADE_TYPE_ID varchar(15),
		@AMORT_MONTH DECIMAL(18,2),
		@RED_DT DATETIME,
		@BID_ID VARCHAR(20),
		@SUP_NAME NVARCHAR(2000),
		@l_SUP_ID VARCHAR(15),
		@OFFERING_VALUE DECIMAL(18,2),
		@NOTE_DVMS NVARCHAR(500),
		@PO_ID VARCHAR(15),
		@PO_AMT DECIMAL(18,2),
		@TOTAL_AMT_ETM DECIMAL(18,2)
		

		FETCH NEXT FROM ListGoods INTO @REQDT_ID, @PL_REQDT_ID,@GOODS_ID,@DESCRIPTION,@QUANTITY,@PRICE,@TOTAL_AMT,
		@NOTES,@RED_DT,@AMORT_MONTH, @TRADE_TYPE_ID, @SUP_ID,@BID_ID,@SUP_NAME,@OFFERING_VALUE,@NOTE_DVMS,@PO_ID,@PO_AMT,@TOTAL_AMT_ETM
		WHILE @@FETCH_STATUS = 0	
		BEGIN
			--LUCTV 21052020 KIEM TRA NEU CHUA TON TAI NHA CUNG CAP THI THEM MOI NHA CUNG CAP
			IF((@SUP_NAME IS NOT NULL AND @SUP_NAME <> '') AND NOT EXISTS(SELECT * FROM CM_SUPPLIER WHERE SUP_NAME =@SUP_NAME AND SUP_NAME IS NOT NULL AND SUP_NAME <>''))
			BEGIN
			EXEC SYS_CodeMasters_Gen 'CM_SUPPLIER', @l_SUP_ID out
				IF @l_SUP_ID='' OR @l_SUP_ID IS NULL GOTO ABORT
				INSERT INTO CM_SUPPLIER([DISCIPLINES],[SUP_ID],[SUP_CODE],[SUP_NAME],[SUP_TYPE_ID],[REGION_ID],[ADDR],[EMAIL],[TAX_NO],[TEL],[CONTACT_PERSON],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],ACC_NUM)
				VALUES('', @l_SUP_ID ,@l_SUP_ID ,@SUP_NAME ,NULL ,NULL ,'' ,'' ,'' ,'' ,'' ,'' ,'1' ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,'U' ,NULL,NULL,NULL)
			END
			----
			--IF(@SUP_ID IS NULL OR @SUP_ID='')
			IF(EXISTS(SELECT * FROM CM_SUPPLIER WHERE SUP_NAME =@SUP_NAME AND SUP_NAME IS NOT NULL AND SUP_NAME <>''))
				SET @SUP_ID =(SELECT TOP 1 SUP_ID FROM dbo.CM_SUPPLIER WHERE SUP_NAME=@SUP_NAME)

			IF(EXISTS(SELECT * FROM CM_SUPPLIER WHERE SUP_NAME =@SUP_NAME AND SUP_NAME IS NOT NULL AND SUP_NAME <>'' AND SUP_ID =@SUP_ID))
				SET @SUP_ID =(SELECT TOP 1 SUP_ID FROM dbo.CM_SUPPLIER WHERE SUP_ID=@SUP_ID AND SUP_NAME = @SUP_NAME)


			IF(@SUP_NAME IS NULL OR @SUP_NAME = '')
				SET @SUP_ID = NULL

			UPDATE dbo.TR_REQUEST_DOC_DT SET PRICE=@PRICE,TOTAL_AMT=((QUANTITY * @PRICE + TAXES) * EXCHANGE_RATE),TRAN_TYPE_ID=@TRADE_TYPE_ID,SUP_ID=@SUP_ID,BID_ID=@BID_ID,OFFERING_VALUE =@OFFERING_VALUE,
			NOTE_DVMS =@NOTE_DVMS,PO_ID = @PO_ID,PO_AMT = @PO_AMT, TOTAL_AMT_ETM = @TOTAL_AMT_ETM, DESCRIPTION = @DESCRIPTION
			WHERE REQDT_ID=@REQDT_ID
			UPDATE dbo.BID_MASTER SET REQUEST_ID=@p_REQ_ID WHERE BID_ID=@BID_ID
			
			IF @@ERROR <> 0 GOTO ABORT1
		-- next Group_Id
			FETCH NEXT FROM ListGoods INTO @REQDT_ID, @PL_REQDT_ID,@GOODS_ID,@DESCRIPTION,@QUANTITY,@PRICE,@TOTAL_AMT,
		@NOTES,@RED_DT,@AMORT_MONTH, @TRADE_TYPE_ID, @SUP_ID,@BID_ID,@SUP_NAME,@OFFERING_VALUE,@NOTE_DVMS,@PO_ID,@PO_AMT,@TOTAL_AMT_ETM
		END
		CLOSE ListGoods
		DEALLOCATE ListGoods
		IF @@Error <> 0 GOTO ABORT

		UPDATE dbo.TR_REQUEST_DOC SET TOTAL_AMT=(SELECT SUM(TOTAL_AMT) FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID) WHERE REQ_ID=@p_REQ_ID 

		--Insert into TABLE PL_REQUEST_DOC_DT
		Exec sp_xml_preparedocument @hdoc Output,@p_ListCostCenter
		DECLARE ListCostCenters  CURSOR FOR
		SELECT *
		FROM OPENXML(@hDoc,'/Root/ListCostCenter',2)
		WITH 
		(
			COST_ID	varchar(15),	
			NOTES	nvarchar(1000),
			AUTH_STATUS varchar(15),
			MAKER_ID varchar(15),
			CREATE_DT DATETIME,
			CHECKER_ID varchar(15),
			APPROVE_DT DATETIME
		)

		OPEN ListCostCenters
		Declare 
		@COST_ID	varchar(15),
		@AUTH_STATUS varchar(15),
		@MAKER_ID varchar(15),
		@CREATE_DT DATETIME,
		@CHECKER_ID varchar(15),
		@APPROVE_DT DATETIME

		FETCH NEXT FROM ListCostCenters INTO @COST_ID, @NOTES,@AUTH_STATUS,@MAKER_ID,@CREATE_DT,@CHECKER_ID,@APPROVE_DT
		WHILE @@FETCH_STATUS = 0	
		BEGIN
			
			DECLARE @l_REQ_COST_ID VARCHAR(15)
			EXEC SYS_CodeMasters_Gen 'TR_REQUEST_COSTCENTER', @l_REQ_COST_ID out
			IF @l_REQ_COST_ID='' OR @l_REQ_COST_ID IS NULL GOTO ABORT
	
			INSERT INTO dbo.TR_REQUEST_COSTCENTER
			(
			    REQ_COST_ID,
			    COST_ID,
			    REQ_ID,
			    NOTES,
			    AUTH_STATUS,
			    MAKER_ID,
			    CREATE_DT,
			    CHECKER_ID,
			    APPROVE_DT
			)
			VALUES
			(   @l_REQ_COST_ID,        -- REQ_COST_ID - varchar(15)
			    @COST_ID,        -- COST_ID - varchar(15)
			    @p_REQ_ID,        -- REQ_ID - varchar(15)
			    @NOTES,       -- NOTES - nvarchar(500)
			    @AUTH_STATUS,        -- AUTH_STATUS - varchar(1)
			    @MAKER_ID,        -- MAKER_ID - varchar(15)
			    @CREATE_DT, -- CREATE_DT - datetime
			    @CHECKER_ID,        -- CHECKER_ID - varchar(15)
			   @APPROVE_DT -- APPROVE_DT - datetime
			 )
			
			IF @@ERROR <> 0 GOTO ABORT1
		-- next Group_Id
			FETCH NEXT FROM ListCostCenters INTO @COST_ID, @NOTES,@AUTH_STATUS,@MAKER_ID,@CREATE_DT,@CHECKER_ID,@APPROVE_DT
		END
		CLOSE ListCostCenters
		DEALLOCATE ListCostCenters

		UPDATE dbo.TR_REQUEST_DOC SET TOTAL_AMT= (SELECT SUM(TOTAL_AMT) FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID) WHERE REQ_ID=@p_REQ_ID

		

		
COMMIT TRANSACTION
SELECT '0' as Result, @p_REQ_ID  REQ_ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' AS RESULT
		RETURN '-1'
End
ABORT1:
BEGIN
		CLOSE ListGoods
		DEALLOCATE ListGoods
		CLOSE ListCostCenters
		DEALLOCATE ListCostCenters
		ROLLBACK TRANSACTION
		SELECT '-1' AS RESULT
		RETURN '-1'
End