ALTER PROCEDURE [dbo].[PL_REQUEST_DOC_Ins] @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_TOTAL_AMT decimal = NULL, @p_NOTES nvarchar(1000) = NULL, @p_RECORD_STATUS varchar(1) = NULL, @p_MAKER_ID varchar(20) = NULL, @p_CREATE_DT DATETIME = NULL, @p_AUTH_STATUS varchar(50) = NULL, @p_CHECKER_ID varchar(20) = NULL, @p_APPROVE_DT DATETIME = NULL, @p_BRANCH_ID VARCHAR(15)=NULL, @p_DVDM_ID VARCHAR(20) = NULL, @p_REQ_PARENT_ID VARCHAR(20) = NULL, @p_BRANCH_FEE NVARCHAR(500) = NULL, @p_DEP_ID VARCHAR(20)=NULL, @p_DEP_FEE_ID VARCHAR(20)= NULL, @p_IS_BACKDAY BIT = NULL, @p_REQ_LINE VARCHAR(20), @p_SIGN_USER VARCHAR(20) = NULL, @p_ListGood XML, @p_ListCostCenter XML, @p_ListTransfer XML AS BEGIN TRANSACTION DECLARE @DEP_ID VARCHAR(15) = (SELECT SECUR_CODE FROM TL_USER WHERE TLNANME = @p_MAKER_ID) exec [dbo].[PL_REQUEST_DOC_CODE_GenKey] @p_BRANCH_ID,@DEP_ID, @p_REQ_CODE out IF(@p_REQ_CODE IS NULL OR @p_REQ_CODE ='') BEGIN SELECT 'REQ-00001' Result, '' REQ_ID, N'Mã tờ trình chủ trương bắc buộc nhập' ErrorDesc ROLLBACK TRANSACTION RETURN '-1' END IF EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE [REQ_CODE] = @p_REQ_CODE) BEGIN SELECT ErrorCode Result, '' REQ_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'REQ-00001' ROLLBACK TRANSACTION RETURN '-1' END DECLARE @sErrorCode VARCHAR(20) --insert master DECLARE @l_REQ_ID VARCHAR(15) EXEC SYS_CodeMasters_Gen 'PL_REQUEST_DOC', @l_REQ_ID out IF @l_REQ_ID='' OR @l_REQ_ID IS NULL GOTO ABORT DECLARE @BRANCH_CREATE VARCHAR(20),@DEP_CREATE VARCHAR(20) SET @BRANCH_CREATE= (SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID) IF(EXISTS(SELECT * FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE AND BRANCH_TYPE='HS')) SET @DEP_CREATE=(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID) ELSE SET @DEP_CREATE='' INSERT INTO dbo.PL_REQUEST_DOC ( REQ_ID, REQ_CODE, REQ_NAME, REQ_DT, REQ_TYPE, REQ_CONTENT, REQ_REASON, BRANCH_ID, TOTAL_AMT, NOTES, RECORD_STATUS, MAKER_ID, CREATE_DT, AUTH_STATUS, CHECKER_ID, APPROVE_DT, DVDM_APP_ID, REQ_PARENT_ID, BRANCH_FEE, IS_BACKDAY, PROCESS_ID, DEP_ID, DEP_FEE, BRANCH_CREATE, DEP_CREATE, REQ_LINE,SIGN_USER ) VALUES ( @l_REQ_ID, -- REQ_ID - varchar(15) @p_REQ_CODE, -- REQ_CODE - varchar(100) @p_REQ_NAME, -- REQ_NAME - nvarchar(200) CAST(@p_REQ_DT AS DATE), -- REQ_DT - datetime @p_REQ_TYPE, -- REQ_TYPE - int @p_REQ_CONTENT, -- REQ_CONTENT - nvarchar(1000) @p_REQ_REASON, -- REQ_REASON - nvarchar(500) @p_BRANCH_ID, -- BRANCH_ID - varchar(15) @p_TOTAL_AMT, -- TOTAL_AMT - decimal(18, 0) @p_NOTES, -- NOTES - nvarchar(1000) @p_RECORD_STATUS, -- RECORD_STATUS - varchar(1) @p_MAKER_ID, -- MAKER_ID - varchar(12) CAST(@p_CREATE_DT AS DATE), -- CREATE_DT - datetime 'E', -- AUTH_STATUS - varchar(50) @p_CHECKER_ID, -- CHECKER_ID - varchar(12) CAST(@P_APPROVE_DT AS DATE), -- APPROVE_DT - datetime @p_DVDM_ID, @p_REQ_PARENT_ID, @p_BRANCH_FEE, @p_IS_BACKDAY, '', @p_DEP_ID, @p_DEP_FEE_ID, @BRANCH_CREATE, @DEP_CREATE, @p_REQ_LINE,@p_SIGN_USER ) IF @@Error <> 0 GOTO ABORT DECLARE @TABLE TABLE( PLAN_ID varchar(15) , TRADE_ID varchar(15) , GOODS_ID varchar(15) , [DESCRIPTION] nvarchar(500), UNIT_ID varchar(15) , QUANTITY decimal(18,0) , PRICE decimal(18,2) , TOTAL_AMT decimal(18,2), NOTES nvarchar(1000), REQDT_TYPE VARCHAR(1), NAME NVARCHAR(500), DVDM_ID VARCHAR(20), HH_ID VARCHAR(20), CURRENCY nvarchar(50), EXCHANGE_RATE decimal(18,2), TAXES decimal(18, 2), SUP_ID VARCHAR(20), TRADE_TYPE VARCHAR(20), KHOI_ID VARCHAR(20), UNIT_NAME nvarchar(200) ) --Insert into TABLE PL_REQUEST_DOC_DT Declare @hdoc INT Exec sp_xml_preparedocument @hdoc Output,@p_ListGood INSERT INTO @TABLE SELECT PLAN_ID, TRADE_ID, GOODS_ID, DESCRIPTION, UNIT_ID, QUANTITY, PRICE, TOTAL_AMT, NOTES, REQDT_TYPE, NAME, DVDM_ID, HH_ID, CURRENCY, EXCHANGE_RATE, TAXES, SUP_ID, TRADE_TYPE, KHOI_ID,UNIT_NAME FROM OPENXML(@hDoc,'/Root/ListGood',2) WITH ( PLAN_ID varchar(15) , TRADE_ID varchar(15) , GOODS_ID varchar(15) , [DESCRIPTION] nvarchar(500), UNIT_ID varchar(15) , QUANTITY decimal(18,0) , PRICE decimal(18,2) , TOTAL_AMT decimal(18,2), NOTES nvarchar(1000), REQDT_TYPE VARCHAR(1), NAME NVARCHAR(500), DVDM_ID VARCHAR(20), HH_ID VARCHAR(20), CURRENCY nvarchar(50), EXCHANGE_RATE decimal(18,2), TAXES decimal(18, 2), SUP_ID VARCHAR(20), TRADE_TYPE VARCHAR(20), KHOI_ID VARCHAR(20), UNIT_NAME nvarchar(200) ) DECLARE @TABLE_TRANSFER TABLE ( FR_PLAN_ID varchar(15), FR_TRADE_ID varchar(15), FR_GOOD_ID varchar(15), FR_BRN_ID varchar(15), TO_BRN_ID varchar(15), TO_PLAN_ID varchar(15), TO_TRADE_ID varchar(15), TO_GOOD_ID varchar(15), QTY DECIMAL(18,0), TOTAL_AMT decimal(18), NOTES nvarchar(1000), FR_DEP_ID VARCHAR(20), TO_DEP_ID VARCHAR(20), FR_DVDM_ID VARCHAR(20), TO_DVDM_ID VARCHAR(20), FR_KHOI_ID VARCHAR(20), TO_KHOI_ID VARCHAR(20), FR_GD_TYPE VARCHAR(20), TO_GD_TYPE VARCHAR(20) ) Exec sp_xml_preparedocument @hdoc Output,@p_ListTransfer INSERT INTO @TABLE_TRANSFER SELECT FR_PLAN_ID, FR_TRADE_ID, FR_GOOD_ID, FR_BRN_ID, TO_BRN_ID, TO_PLAN_ID, TO_TRADE_ID, TO_GOOD_ID, QTY, TOTAL_AMT, NOTES, FR_DEP_ID, TO_DEP_ID, FR_DVDM_ID, TO_DVDM_ID, FR_KHOI_ID, TO_KHOI_ID, FR_GD_TYPE, TO_GD_TYPE FROM OPENXML(@hdoc,'/Root/ListTransfer',2) WITH ( FR_PLAN_ID varchar(15), FR_TRADE_ID varchar(15), FR_GOOD_ID varchar(15), FR_BRN_ID varchar(15), TO_BRN_ID varchar(15), TO_PLAN_ID varchar(15), TO_TRADE_ID varchar(15), TO_GOOD_ID varchar(15), QTY DECIMAL(18,0), TOTAL_AMT decimal(18), NOTES nvarchar(1000), FR_DEP_ID VARCHAR(20), TO_DEP_ID VARCHAR(20), FR_DVDM_ID VARCHAR(20), TO_DVDM_ID VARCHAR(20), FR_KHOI_ID VARCHAR(20), TO_KHOI_ID VARCHAR(20), FR_GD_TYPE VARCHAR(20), TO_GD_TYPE VARCHAR(20) ) WHERE FR_BRN_ID !='' AND FR_BRN_ID IS NOT NULL DECLARE ListGoods CURSOR FOR SELECT PLAN_ID, TRADE_ID, GOODS_ID, DESCRIPTION, UNIT_ID, QUANTITY, PRICE, TOTAL_AMT, NOTES, REQDT_TYPE, NAME, DVDM_ID, HH_ID, CURRENCY, EXCHANGE_RATE, TAXES, SUP_ID, TRADE_TYPE,KHOI_ID,UNIT_NAME FROM @TABLE OPEN ListGoods Declare @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,2), @TOTAL_AMT decimal(18,2), @NOTES nvarchar(1000), @REQDT_TYPE VARCHAR(1), @NAME NVARCHAR(500), @DVDM_ID VARCHAR(20), @HH_ID VARCHAR(20), @CURRENCY nvarchar(50), @EXCHANGE_RATE decimal(18, 2), @TAXES decimal(18, 2), @SUP_ID VARCHAR(20), @TRADE_TYPE VARCHAR(20), @KHOI_ID VARCHAR(20), @UNIT_NAME nvarchar(200) FETCH NEXT FROM ListGoods INTO @PLAN_ID,@TRADE_ID,@GOODS_ID,@DESCRIPTION,@UNIT_ID,@QUANTITY, @PRICE,@TOTAL_AMT,@NOTES, @REQDT_TYPE, @NAME,@DVDM_ID,@HH_ID,@CURRENCY,@EXCHANGE_RATE,@TAXES,@SUP_ID,@TRADE_TYPE,@KHOI_ID,@UNIT_NAME WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @l_REQDT_ID VARCHAR(15) EXEC SYS_CodeMasters_Gen 'PL_REQUEST_DOC_DT', @l_REQDT_ID out IF @l_REQDT_ID='' OR @l_REQDT_ID IS NULL GOTO ABORT SET @TOTAL_AMT=(@PRICE * @QUANTITY * @EXCHANGE_RATE) + (@TAXES*@EXCHANGE_RATE) INSERT INTO dbo.PL_REQUEST_DOC_DT ( REQDT_ID, REQ_ID, PLAN_ID, TRADE_ID, GOODS_ID, NAME, DESCRIPTION, REQDT_TYPE, UNIT_ID, QUANTITY, PRICE, TOTAL_AMT, RECORD_STATUS, MAKER_ID, CREATE_DT, AUTH_STATUS, CHECKER_ID, APPROVE_DT, DVDM_ID, HANGHOA_ID, CURRENCY, EXCHANGE_RATE, TAXES,SUP_ID,TRADE_TYPE,KHOI_ID,UNIT_NAME ) VALUES ( @l_REQDT_ID, -- REQDT_ID - varchar(15) @l_REQ_ID, -- REQ_ID - varchar(15) @PLAN_ID, -- PLAN_ID - varchar(15) @TRADE_ID, -- TRADE_ID - varchar(15) @GOODS_ID, -- GOODS_ID - varchar(15) @NAME, -- NAME - nvarchar(200) @DESCRIPTION, -- DESCRIPTION - nvarchar(500) @REQDT_TYPE, -- REQDT_TYPE - varchar(1) @UNIT_ID, -- UNIT_ID - varchar(15) @QUANTITY, -- QUANTITY - decimal(18, 0) @PRICE, -- PRICE - decimal(18, 0) @TOTAL_AMT, -- TOTAL_AMT - decimal(18, 0) @p_RECORD_STATUS, -- RECORD_STATUS - varchar(1) @p_MAKER_ID, -- MAKER_ID - varchar(15) CAST(@p_CREATE_DT AS DATE) , -- CREATE_DT - datetime @p_AUTH_STATUS, -- AUTH_STATUS - varchar(50) @p_CHECKER_ID, -- CHECKER_ID - varchar(15) CAST(@P_APPROVE_DT AS DATE), -- APPROVE_DT - datetime @DVDM_ID, @HH_ID, @CURRENCY, @EXCHANGE_RATE, @TAXES,@SUP_ID,@TRADE_TYPE,@KHOI_ID,@UNIT_NAME ) IF @@ERROR <> 0 GOTO ABORT1 -- next Group_Id FETCH NEXT FROM ListGoods INTO @PLAN_ID,@TRADE_ID,@GOODS_ID,@DESCRIPTION,@UNIT_ID,@QUANTITY, @PRICE,@TOTAL_AMT,@NOTES, @REQDT_TYPE, @NAME,@DVDM_ID,@HH_ID,@CURRENCY,@EXCHANGE_RATE,@TAXES,@SUP_ID,@TRADE_TYPE,@KHOI_ID,@UNIT_NAME END CLOSE ListGoods DEALLOCATE ListGoods IF @@Error <> 0 GOTO ABORT --Insert into TABLE PL_REQUEST_DOC_DT UPDATE dbo.PL_REQUEST_DOC SET TOTAL_AMT = (SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID =@l_REQ_ID) WHERE PL_REQUEST_DOC.REQ_ID=@l_REQ_ID Exec sp_xml_preparedocument @hdoc Output,@p_ListTransfer DECLARE ListTransfers CURSOR FOR SELECT * FROM @TABLE_TRANSFER OPEN ListTransfers Declare @FR_PLAN_ID varchar(15), @FR_TRADE_ID varchar(15), @FR_GOOD_ID varchar(15), @FR_BRN_ID varchar(15), @TO_BRN_ID varchar(15), @TO_PLAN_ID varchar(15), @TO_TRADE_ID varchar(15), @TO_GOOD_ID varchar(15), @QTY DECIMAL(18,0), @FR_DEP_ID VARCHAR(20), @TO_DEP_ID VARCHAR(20), @FR_DVDM_ID VARCHAR(20), @TO_DVDM_ID VARCHAR(20), @FR_KHOI_ID VARCHAR(20), @TO_KHOI_ID VARCHAR(20), @FR_GD_TYPE VARCHAR(20), @TO_GD_TYPE VARCHAR(20) FETCH NEXT FROM ListTransfers INTO @FR_PLAN_ID,@FR_TRADE_ID,@FR_GOOD_ID,@FR_BRN_ID,@TO_BRN_ID,@TO_PLAN_ID, @TO_TRADE_ID,@TO_GOOD_ID,@QTY,@TOTAL_AMT, @NOTES,@FR_DEP_ID,@TO_DEP_ID,@FR_DVDM_ID,@TO_DVDM_ID,@FR_KHOI_ID,@TO_KHOI_ID,@FR_GD_TYPE,@TO_GD_TYPE WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @l_REQ_TRANSFER_ID VARCHAR(15) EXEC SYS_CodeMasters_Gen 'PL_REQUEST_TRANSFER', @l_REQ_TRANSFER_ID out IF @l_REQ_TRANSFER_ID='' OR @l_REQ_TRANSFER_ID IS NULL GOTO ABORT INSERT INTO dbo.PL_REQUEST_TRANSFER ( REQ_TRANSFER_ID, REQ_DOC_ID, FR_PLAN_ID, FR_TRADE_ID, FR_GOOD_ID, FR_BRN_ID, TO_BRN_ID, TO_PLAN_ID, TO_TRADE_ID, TO_GOOD_ID, QTY, TOTAL_AMT, NOTES, AUTH_STATUS, MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT, FR_DEP_ID, TO_DEP_ID, FR_DVDM_ID, TO_DVDM_ID, FR_KHOI_ID, TO_KHOI_ID ) VALUES ( @l_REQ_TRANSFER_ID, -- REQ_TRANSFER_ID - varchar(15) @l_REQ_ID, -- REQ_DOC_ID - varchar(15) @FR_PLAN_ID, -- FR_PLAN_ID - varchar(15) @FR_TRADE_ID, -- FR_TRADE_ID - varchar(15) @FR_GOOD_ID, -- FR_GOOD_ID - varchar(15) @FR_BRN_ID, -- FR_BRN_ID - varchar(15) @TO_BRN_ID, -- TO_BRN_ID - varchar(15) @TO_PLAN_ID, -- TO_PLAN_ID - varchar(15) @TO_TRADE_ID, -- TO_TRADE_ID - varchar(15) @TO_GOOD_ID, -- TO_GOOD_ID - varchar(15) @QTY, -- QTY - decimal(18, 0) @TOTAL_AMT, -- TOTAL_AMT - decimal(18, 0) @NOTES, -- NOTES - nvarchar(500) @p_AUTH_STATUS, -- AUTH_STATUS - varchar(1) @p_MAKER_ID, -- MAKER_ID - varchar(15) CAST(@p_CREATE_DT AS DATE), -- CREATE_DT - datetime @p_CHECKER_ID, -- CHECKER_ID - varchar(15) CAST(@p_APPROVE_DT AS DATE), @FR_DEP_ID, @TO_DEP_ID, @FR_DVDM_ID, @TO_DVDM_ID, @FR_KHOI_ID, @TO_KHOI_ID -- APPROVE_DT - datetime ) IF @@ERROR <> 0 GOTO ABORT1 -- next Group_Id FETCH NEXT FROM ListTransfers INTO @FR_PLAN_ID,@FR_TRADE_ID,@FR_GOOD_ID,@FR_BRN_ID,@TO_BRN_ID,@TO_PLAN_ID, @TO_TRADE_ID,@TO_GOOD_ID,@QTY,@TOTAL_AMT, @NOTES,@FR_DEP_ID,@TO_DEP_ID,@FR_DVDM_ID,@TO_DVDM_ID,@FR_KHOI_ID,@TO_KHOI_ID,@FR_GD_TYPE,@TO_GD_TYPE END CLOSE ListTransfers DEALLOCATE ListTransfers IF @@Error <> 0 GOTO ABORT --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) ) OPEN ListCostCenters Declare @COST_ID varchar(15) FETCH NEXT FROM ListCostCenters INTO @COST_ID, @NOTES WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @l_REQ_COST_ID VARCHAR(15) EXEC SYS_CodeMasters_Gen 'PL_REQUEST_COSTCENTER', @l_REQ_COST_ID out IF @l_REQ_COST_ID='' OR @l_REQ_COST_ID IS NULL GOTO ABORT INSERT INTO dbo.PL_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) @l_REQ_ID, -- REQ_ID - varchar(15) @NOTES, -- NOTES - nvarchar(500) @p_AUTH_STATUS, -- AUTH_STATUS - varchar(1) @p_MAKER_ID, -- MAKER_ID - varchar(15) CAST(@p_CREATE_DT AS DATE) , -- CREATE_DT - datetime @p_CHECKER_ID, -- CHECKER_ID - varchar(15) CAST(@p_APPROVE_DT AS DATE) -- APPROVE_DT - datetime ) IF @@ERROR <> 0 GOTO ABORT1 -- next Group_Id FETCH NEXT FROM ListCostCenters INTO @COST_ID, @NOTES END CLOSE ListCostCenters DEALLOCATE ListCostCenters COMMIT TRANSACTION SELECT '0' as Result, @l_REQ_ID REQ_ID, @p_REQ_CODE ErrorDesc RETURN '0' ABORT: BEGIN ROLLBACK TRANSACTION SELECT '-1' AS Result ,'' REQ_ID, '' ErrorDesc RETURN '-1' End ABORT1: BEGIN CLOSE ListGoods DEALLOCATE ListGoods CLOSE ListCostCenters DEALLOCATE ListCostCenters CLOSE ListCostCenters DEALLOCATE ListCostCenters ROLLBACK TRANSACTION SELECT '-1' AS Result ,'' REQ_ID, '' ErrorDesc RETURN '-1' End ¿ ALTER PROCEDURE [dbo].[PL_REQUEST_DOC_Upd] @p_REQ_ID VARCHAR(15)=NULL, @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_TOTAL_AMT decimal = NULL, @p_NOTES nvarchar(1000) = NULL, @p_RECORD_STATUS varchar(1) = NULL, @p_MAKER_ID varchar(20) = NULL, @p_CREATE_DT DATETIME = NULL, @p_AUTH_STATUS varchar(50) = NULL, @p_CHECKER_ID varchar(20) = NULL, @p_APPROVE_DT DATETIME = NULL, @p_BRANCH_ID VARCHAR(15)=NULL, @p_DVDM_ID VARCHAR(20) = NULL, @p_REQ_PARENT_ID VARCHAR(20) = NULL, @p_BRANCH_FEE NVARCHAR(500) = NULL, @p_DEP_ID VARCHAR(20)=NULL, @p_DEP_FEE_ID VARCHAR(20)= NULL, @p_IS_BACKDAY BIT= NULL, @p_REQ_LINE VARCHAR(20), @p_SIGN_USER VARCHAR(20) = NULL, @p_ListGood XML, @p_ListCostCenter XML, @p_ListTransfer XML AS SET @p_CREATE_DT =CAST(@p_CREATE_DT AS DATE) SET @p_APPROVE_DT=CAST(@p_APPROVE_DT AS DATE) SET @p_REQ_DT = CAST(@p_REQ_DT AS DATE) IF(@p_REQ_CODE IS NULL OR @p_REQ_CODE ='') BEGIN SELECT 'REQ-00001' Result, '' REQ_ID, N'Mã tờ trình chủ trương bắt buộc nhập' ErrorDesc RETURN '-1' END IF EXISTS(SELECT * FROM PL_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 '-1' END DECLARE @sErrorCode VARCHAR(20) BEGIN TRANSACTION UPDATE dbo.PL_REQUEST_DOC SET REQ_CODE=@p_REQ_CODE,REQ_NAME=@p_REQ_NAME,REQ_DT=@p_REQ_DT,REQ_TYPE=@p_REQ_TYPE,REQ_CONTENT=@p_REQ_CONTENT,REQ_REASON=@p_REQ_REASON,RECORD_STATUS=@p_RECORD_STATUS ,BRANCH_ID=@p_BRANCH_ID,TOTAL_AMT=@p_TOTAL_AMT,NOTES=@p_NOTES,MAKER_ID=@p_MAKER_ID,CREATE_DT=@p_CREATE_DT,CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=@p_APPROVE_DT,AUTH_STATUS=@p_AUTH_STATUS,DVDM_APP_ID=@p_DVDM_ID ,REQ_PARENT_ID=@p_REQ_PARENT_ID,BRANCH_FEE=@p_BRANCH_FEE,IS_BACKDAY=@p_IS_BACKDAY,DEP_ID=@p_DEP_ID,DEP_FEE=@p_DEP_FEE_ID,REQ_LINE=@p_REQ_LINE,SIGN_USER = @p_SIGN_USER WHERE REQ_ID=@p_REQ_ID DELETE FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID DELETE FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID DELETE FROM dbo.PL_REQUEST_COSTCENTER WHERE REQ_ID=@p_REQ_ID IF @@Error <> 0 GOTO ABORT --Insert into TABLE PL_REQUEST_DOC_DT DECLARE @TABLE TABLE( PLAN_ID varchar(15) , TRADE_ID varchar(15) , GOODS_ID varchar(15) , [DESCRIPTION] nvarchar(500), UNIT_ID varchar(15) , QUANTITY decimal(18,0) , PRICE decimal(18,2) , TOTAL_AMT decimal(18,2), NOTES nvarchar(1000), REQDT_TYPE VARCHAR(1), NAME NVARCHAR(500), DVDM_ID VARCHAR(20), HH_ID VARCHAR(20), CURRENCY nvarchar(50), EXCHANGE_RATE decimal(18,2), TAXES decimal(18, 2), SUP_ID VARCHAR(20), TRADE_TYPE VARCHAR(20), KHOI_ID VARCHAR(20), UNIT_NAME NVARCHAR(200) ) --Insert into TABLE PL_REQUEST_DOC_DT Declare @hdoc INT Exec sp_xml_preparedocument @hdoc Output,@p_ListGood INSERT INTO @TABLE SELECT PLAN_ID, TRADE_ID, GOODS_ID, DESCRIPTION, UNIT_ID, QUANTITY, PRICE, TOTAL_AMT, NOTES, REQDT_TYPE, NAME, DVDM_ID, HH_ID, CURRENCY, EXCHANGE_RATE, TAXES, SUP_ID, TRADE_TYPE, KHOI_ID,UNIT_NAME FROM OPENXML(@hDoc,'/Root/ListGood',2) WITH ( PLAN_ID varchar(15) , TRADE_ID varchar(15) , GOODS_ID varchar(15) , [DESCRIPTION] nvarchar(500), UNIT_ID varchar(15) , QUANTITY decimal(18,0) , PRICE decimal(18,2) , TOTAL_AMT decimal(18,2), NOTES nvarchar(1000), REQDT_TYPE VARCHAR(1), NAME NVARCHAR(500), DVDM_ID VARCHAR(20), HH_ID VARCHAR(20), CURRENCY nvarchar(50), EXCHANGE_RATE decimal(18,2), TAXES decimal(18, 2), SUP_ID VARCHAR(20), TRADE_TYPE VARCHAR(20), KHOI_ID VARCHAR(20), UNIT_NAME NVARCHAR(200) ) DECLARE @TABLE_TRANSFER TABLE ( FR_PLAN_ID varchar(15), FR_TRADE_ID varchar(15), FR_GOOD_ID varchar(15), FR_BRN_ID varchar(15), TO_BRN_ID varchar(15), TO_PLAN_ID varchar(15), TO_TRADE_ID varchar(15), TO_GOOD_ID varchar(15), QTY DECIMAL(18,0), TOTAL_AMT decimal(18), NOTES nvarchar(1000), FR_DEP_ID VARCHAR(20), TO_DEP_ID VARCHAR(20), FR_DVDM_ID VARCHAR(20), TO_DVDM_ID VARCHAR(20), FR_KHOI_ID VARCHAR(20), TO_KHOI_ID VARCHAR(20), FR_GD_TYPE VARCHAR(20), TO_GD_TYPE VARCHAR(20) ) Exec sp_xml_preparedocument @hdoc Output,@p_ListTransfer INSERT INTO @TABLE_TRANSFER SELECT FR_PLAN_ID, FR_TRADE_ID, FR_GOOD_ID, FR_BRN_ID, TO_BRN_ID, TO_PLAN_ID, TO_TRADE_ID, TO_GOOD_ID, QTY, TOTAL_AMT, NOTES, FR_DEP_ID, TO_DEP_ID, FR_DVDM_ID, TO_DVDM_ID, FR_KHOI_ID, TO_KHOI_ID, FR_GD_TYPE, TO_GD_TYPE FROM OPENXML(@hdoc,'/Root/ListTransfer',2) WITH ( FR_PLAN_ID varchar(15), FR_TRADE_ID varchar(15), FR_GOOD_ID varchar(15), FR_BRN_ID varchar(15), TO_BRN_ID varchar(15), TO_PLAN_ID varchar(15), TO_TRADE_ID varchar(15), TO_GOOD_ID varchar(15), QTY DECIMAL(18,0), TOTAL_AMT decimal(18), NOTES nvarchar(1000), FR_DEP_ID VARCHAR(20), TO_DEP_ID VARCHAR(20), FR_DVDM_ID VARCHAR(20), TO_DVDM_ID VARCHAR(20), FR_KHOI_ID VARCHAR(20), TO_KHOI_ID VARCHAR(20), FR_GD_TYPE VARCHAR(20), TO_GD_TYPE VARCHAR(20) ) WHERE FR_BRN_ID !='' AND FR_BRN_ID IS NOT NULL DECLARE ListGoods CURSOR FOR SELECT PLAN_ID, TRADE_ID, GOODS_ID, DESCRIPTION, UNIT_ID, QUANTITY, PRICE, TOTAL_AMT, NOTES, REQDT_TYPE, NAME, DVDM_ID, HH_ID, CURRENCY, EXCHANGE_RATE, TAXES, SUP_ID, TRADE_TYPE,KHOI_ID,UNIT_NAME FROM @TABLE OPEN ListGoods Declare @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,2), @TOTAL_AMT decimal(18,2), @NOTES nvarchar(1000), @REQDT_TYPE VARCHAR(1), @NAME NVARCHAR(500), @DVDM_ID VARCHAR(20), @HH_ID VARCHAR(20), @CURRENCY nvarchar(50), @EXCHANGE_RATE decimal(18, 2), @TAXES decimal(18, 2), @SUP_ID VARCHAR(20), @TRADE_TYPE VARCHAR(20), @KHOI_ID VARCHAR(20), @UNIT_NAME NVARCHAR(200) FETCH NEXT FROM ListGoods INTO @PLAN_ID,@TRADE_ID,@GOODS_ID,@DESCRIPTION,@UNIT_ID,@QUANTITY, @PRICE,@TOTAL_AMT,@NOTES, @REQDT_TYPE, @NAME,@DVDM_ID,@HH_ID,@CURRENCY,@EXCHANGE_RATE,@TAXES,@SUP_ID,@TRADE_TYPE,@KHOI_ID,@UNIT_NAME WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @l_REQDT_ID VARCHAR(15) EXEC SYS_CodeMasters_Gen 'PL_REQUEST_DOC_DT', @l_REQDT_ID out IF @l_REQDT_ID='' OR @l_REQDT_ID IS NULL GOTO ABORT SET @TOTAL_AMT=(@PRICE * @QUANTITY * @EXCHANGE_RATE) + (@TAXES *@EXCHANGE_RATE) INSERT INTO dbo.PL_REQUEST_DOC_DT ( REQDT_ID, REQ_ID, PLAN_ID, TRADE_ID, GOODS_ID, NAME, DESCRIPTION, REQDT_TYPE, UNIT_ID, QUANTITY, PRICE, TOTAL_AMT, RECORD_STATUS, MAKER_ID, CREATE_DT, AUTH_STATUS, CHECKER_ID, APPROVE_DT, DVDM_ID, HANGHOA_ID, CURRENCY, EXCHANGE_RATE, TAXES,SUP_ID,TRADE_TYPE,KHOI_ID,UNIT_NAME ) VALUES ( @l_REQDT_ID, -- REQDT_ID - varchar(15) @p_REQ_ID, -- REQ_ID - varchar(15) @PLAN_ID, -- PLAN_ID - varchar(15) @TRADE_ID, -- TRADE_ID - varchar(15) @GOODS_ID, -- GOODS_ID - varchar(15) @NAME, -- NAME - nvarchar(200) @DESCRIPTION, -- DESCRIPTION - nvarchar(500) @REQDT_TYPE, -- REQDT_TYPE - varchar(1) @UNIT_ID, -- UNIT_ID - varchar(15) @QUANTITY, -- QUANTITY - decimal(18, 0) @PRICE, -- PRICE - decimal(18, 0) @TOTAL_AMT, -- TOTAL_AMT - decimal(18, 0) @p_RECORD_STATUS, -- RECORD_STATUS - varchar(1) @p_MAKER_ID, -- MAKER_ID - varchar(15) CAST(@p_CREATE_DT AS DATE) , -- CREATE_DT - datetime @p_AUTH_STATUS, -- AUTH_STATUS - varchar(50) @p_CHECKER_ID, -- CHECKER_ID - varchar(15) CAST(@P_APPROVE_DT AS DATE), -- APPROVE_DT - datetime @DVDM_ID, @HH_ID, @CURRENCY, @EXCHANGE_RATE, @TAXES,@SUP_ID,@TRADE_TYPE,@KHOI_ID,@UNIT_NAME ) IF @@ERROR <> 0 GOTO ABORT1 -- next Group_Id FETCH NEXT FROM ListGoods INTO @PLAN_ID,@TRADE_ID,@GOODS_ID,@DESCRIPTION,@UNIT_ID,@QUANTITY, @PRICE,@TOTAL_AMT,@NOTES, @REQDT_TYPE, @NAME,@DVDM_ID,@HH_ID,@CURRENCY,@EXCHANGE_RATE,@TAXES,@SUP_ID,@TRADE_TYPE,@KHOI_ID,@UNIT_NAME END CLOSE ListGoods DEALLOCATE ListGoods IF @@Error <> 0 GOTO ABORT --Insert into TABLE PL_REQUEST_DOC_DT UPDATE dbo.PL_REQUEST_DOC SET TOTAL_AMT = (SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID =@p_REQ_ID) WHERE PL_REQUEST_DOC.REQ_ID=@p_REQ_ID DECLARE ListTransfers CURSOR FOR SELECT * FROM @TABLE_TRANSFER OPEN ListTransfers Declare @FR_PLAN_ID varchar(15), @FR_TRADE_ID varchar(15), @FR_GOOD_ID varchar(15), @FR_BRN_ID varchar(15), @TO_BRN_ID varchar(15), @TO_PLAN_ID varchar(15), @TO_TRADE_ID varchar(15), @TO_GOOD_ID varchar(15), @QTY DECIMAL(18,0), @FR_DEP_ID VARCHAR(20), @TO_DEP_ID VARCHAR(20), @FR_DVDM_ID VARCHAR(20), @TO_DVDM_ID VARCHAR(20), @FR_KHOI_ID VARCHAR(20), @TO_KHOI_ID VARCHAR(20), @FR_GD_TYPE VARCHAR(20), @TO_GD_TYPE VARCHAR(20) FETCH NEXT FROM ListTransfers INTO @FR_PLAN_ID,@FR_TRADE_ID,@FR_GOOD_ID,@FR_BRN_ID,@TO_BRN_ID,@TO_PLAN_ID, @TO_TRADE_ID,@TO_GOOD_ID,@QTY,@TOTAL_AMT, @NOTES,@FR_DEP_ID,@TO_DEP_ID,@FR_DVDM_ID,@TO_DVDM_ID,@FR_KHOI_ID,@TO_KHOI_ID,@FR_GD_TYPE,@TO_GD_TYPE WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @l_REQ_TRANSFER_ID VARCHAR(15) EXEC SYS_CodeMasters_Gen 'PL_REQUEST_TRANSFER', @l_REQ_TRANSFER_ID out IF @l_REQ_TRANSFER_ID='' OR @l_REQ_TRANSFER_ID IS NULL GOTO ABORT INSERT INTO dbo.PL_REQUEST_TRANSFER ( REQ_TRANSFER_ID, REQ_DOC_ID, FR_PLAN_ID, FR_TRADE_ID, FR_GOOD_ID, FR_BRN_ID, TO_BRN_ID, TO_PLAN_ID, TO_TRADE_ID, TO_GOOD_ID, QTY, TOTAL_AMT, NOTES, AUTH_STATUS, MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT, FR_DEP_ID, TO_DEP_ID, FR_DVDM_ID, TO_DVDM_ID, FR_KHOI_ID, TO_KHOI_ID ) VALUES ( @l_REQ_TRANSFER_ID, -- REQ_TRANSFER_ID - varchar(15) @p_REQ_ID, -- REQ_DOC_ID - varchar(15) @FR_PLAN_ID, -- FR_PLAN_ID - varchar(15) @FR_TRADE_ID, -- FR_TRADE_ID - varchar(15) @FR_GOOD_ID, -- FR_GOOD_ID - varchar(15) @FR_BRN_ID, -- FR_BRN_ID - varchar(15) @TO_BRN_ID, -- TO_BRN_ID - varchar(15) @TO_PLAN_ID, -- TO_PLAN_ID - varchar(15) @TO_TRADE_ID, -- TO_TRADE_ID - varchar(15) @TO_GOOD_ID, -- TO_GOOD_ID - varchar(15) @QTY, -- QTY - decimal(18, 0) @TOTAL_AMT, -- TOTAL_AMT - decimal(18, 0) @NOTES, -- NOTES - nvarchar(500) @p_AUTH_STATUS, -- AUTH_STATUS - varchar(1) @p_MAKER_ID, -- MAKER_ID - varchar(15) CAST(@p_CREATE_DT AS DATE), -- CREATE_DT - datetime @p_CHECKER_ID, -- CHECKER_ID - varchar(15) CAST(@p_APPROVE_DT AS DATE), @FR_DEP_ID, @TO_DEP_ID, @FR_DVDM_ID, @TO_DVDM_ID, @FR_KHOI_ID, @TO_KHOI_ID -- APPROVE_DT - datetime ) IF @@ERROR <> 0 GOTO ABORT1 -- next Group_Id FETCH NEXT FROM ListTransfers INTO @FR_PLAN_ID,@FR_TRADE_ID,@FR_GOOD_ID,@FR_BRN_ID,@TO_BRN_ID,@TO_PLAN_ID, @TO_TRADE_ID,@TO_GOOD_ID,@QTY,@TOTAL_AMT, @NOTES,@FR_DEP_ID,@TO_DEP_ID,@FR_DVDM_ID,@TO_DVDM_ID,@FR_KHOI_ID,@TO_KHOI_ID,@FR_GD_TYPE,@TO_GD_TYPE END CLOSE ListTransfers DEALLOCATE ListTransfers IF @@Error <> 0 GOTO ABORT --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) ) OPEN ListCostCenters Declare @COST_ID varchar(15) FETCH NEXT FROM ListCostCenters INTO @COST_ID, @NOTES WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @l_REQ_COST_ID VARCHAR(15) EXEC SYS_CodeMasters_Gen 'PL_REQUEST_COSTCENTER', @l_REQ_COST_ID out IF @l_REQ_COST_ID='' OR @l_REQ_COST_ID IS NULL GOTO ABORT INSERT INTO dbo.PL_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) @p_AUTH_STATUS, -- AUTH_STATUS - varchar(1) @p_MAKER_ID, -- MAKER_ID - varchar(15) CAST(@p_CREATE_DT AS DATE) , -- CREATE_DT - datetime @p_CHECKER_ID, -- CHECKER_ID - varchar(15) CAST(@p_APPROVE_DT AS DATE) -- APPROVE_DT - datetime ) IF @@ERROR <> 0 GOTO ABORT1 -- next Group_Id FETCH NEXT FROM ListCostCenters INTO @COST_ID, @NOTES END CLOSE ListCostCenters DEALLOCATE ListCostCenters COMMIT TRANSACTION SELECT '0' as Result, @p_REQ_ID REQ_ID, '' ErrorDesc RETURN '0' ABORT: BEGIN ROLLBACK TRANSACTION SELECT '-1' AS Result, '' REQ_ID, '' ErrorDesc RETURN '-1' End ABORT1: BEGIN CLOSE ListGoods DEALLOCATE ListGoods CLOSE ListCostCenters DEALLOCATE ListCostCenters CLOSE ListCostCenters DEALLOCATE ListCostCenters ROLLBACK TRANSACTION SELECT '-1' AS Result, '' REQ_ID, '' ErrorDesc RETURN '-1' End