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_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,

@p_IS_KT bit = null

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, IS_KT = @p_IS_KT

		WHERE REQ_ID=@p_REQ_ID

		-- UPDATE TOTAL_AMT MASTER

		UPDATE dbo.TR_REQUEST_DOC

		SET TOTAL_AMT=(SELECT SUM(ISNULL(TOTAL_AMT,0)) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_REQ_ID) WHERE REQ_ID =@p_REQ_ID

		IF @@Error <> 0 GOTO ABORT

		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),

			CURRENCY VARCHAR(15),

			EXCHANGE_RATE DECIMAL(18,2),

			TAXES 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),

		@CURRENCY VARCHAR(15),

		@EXCHANGE_RATE DECIMAL(18,2),

		@TAXES 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,@CURRENCY,@EXCHANGE_RATE ,@TAXES 

		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 AND @SUP_NAME = '')

				SET @SUP_ID = NULL

			UPDATE dbo.TR_REQUEST_DOC_DT SET CURRENCY =@CURRENCY, EXCHANGE_RATE =@EXCHANGE_RATE,TAXES =@TAXES, PRICE=@PRICE,TOTAL_AMT=ROUND(((QUANTITY * @PRICE + @TAXES) * @EXCHANGE_RATE),0),

			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,@CURRENCY,@EXCHANGE_RATE ,@TAXES 

		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