ALTER PROCEDURE dbo.TR_REQUEST_SHOP_DOC_Upd @p_REQ_ID varchar(15) = null , @p_REQ_CODE nvarchar(100) = NULL, @p_REQ_NAME nvarchar(200) = NULL, @p_REQ_DT VARCHAR(20) = NULL, @p_NOTES nvarchar(1000) = NULL, @p_RECORD_STATUS varchar(1) = NULL, @p_MAKER_ID VARCHAR(100) = NULL, @p_CREATE_DT VARCHAR(20) = NULL, @p_AUTH_STATUS varchar(50) = NULL, @p_CHECKER_ID VARCHAR(100) = NULL, @p_APPROVE_DT VARCHAR(20) = NULL, @p_LISTASSET XML = NULL, @p_ListCostCenter XML, @p_REQ_TYPE varchar(10) = NULL, @p_REQ_CONTENT NVARCHAR(1000)=NULL, @p_BRANCH_ID VARCHAR(15)=NULL, @p_DEP_ID VARCHAR(15)=NULL, @p_CURRENT_URI VARCHAR(500)=NULL, @p_PL_CODE VARCHAR(50) = NULL --PHUCVH 14/11/22 BỔ SUNG SỐ TỜ TRÌNH AS DECLARE @sErrorCode VARCHAR(20) IF @sErrorCode <> '' BEGIN SELECT ErrorCode Result, '' REQ_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @sErrorCode RETURN '0' END Declare @hdoc INT Exec sp_xml_preparedocument @hdoc Output,@p_LISTASSET DECLARE AssetDetail CURSOR FOR SELECT * FROM OPENXML(@hdoc,'/Root/AssetDetail',2) WITH ( REQDT_ID VARCHAR(15), PLAN_ID varchar(15) , TRADE_ID varchar(15) , ASS_GROUP_ID varchar(15) , [DESCRIPTION] nvarchar(500), UNIT_ID varchar(15) , QUANTITY decimal(18) , PRICE decimal(18) , TOTAL_AMT decimal(18), NOTES nvarchar(1000), RECEIVE_BRANCH varchar(15), RECEIVE_ADDR nvarchar(1000), RECEIVE_PERSON nvarchar(500), RECEIVE_TEL varchar(100), RECEIVE_SUBBRANCH varchar(20), RECEIVE_DEP varchar(20), RECEIVE_EMAIL nvarchar(200), REQ_DT_TYPE VARCHAR(20), QTY_ETM DECIMAL(18,0), EMP_CODE VARCHAR(15), EMP_ID VARCHAR(15), TYPE_XL VARCHAR(50), DO_EMAIL VARCHAR(200), ASS_ID VARCHAR(20), REASON NVARCHAR(1200) ) OPEN AssetDetail SET @p_DEP_ID=(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID) IF(@p_BRANCH_ID <> 'DV0001') SET @p_DEP_ID = NULL PRINT 'PASS KHOI TAO' DECLARE @STATUS VARCHAR(15) = (SELECT STATUS FROM TR_REQUEST_SHOP_DOC WHERE REQ_ID= @p_REQ_ID) BEGIN TRANSACTION IF(@p_REQ_NAME IS NULL OR @p_REQ_NAME='' ) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_ID, N'Tên PYC bắt buộc nhập' ErrorDesc RETURN '-1' END -- IF(@p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT='' ) -- BEGIN -- ROLLBACK TRANSACTION -- SELECT '-1' as Result, '' REQ_ID, N'Nội dung PYC bắt buộc nhập' ErrorDesc -- RETURN '-1' -- END --insert master BEGIN IF(@STATUS = 'NEW') SET @p_AUTH_STATUS = 'E' IF(@STATUS = 'QLTS_NL' OR @STATUS = 'QLTS_N') BEGIN UPDATE TR_REQUEST_SHOP_DOC SET [REQ_CONTENT]=@p_REQ_CONTENT ,[REQ_NAME] = @p_REQ_NAME ,[NOTES] = @p_NOTES,[AUTH_STATUS] = 'U' ,PL_CODE = @p_PL_CODE WHERE REQ_ID= @p_REQ_ID END ELSE BEGIN UPDATE TR_REQUEST_SHOP_DOC SET [REQ_TYPE] = @p_REQ_TYPE,[REQ_CONTENT]=@p_REQ_CONTENT,[REQ_NAME] = @p_REQ_NAME, [NOTES] = @p_NOTES,[RECORD_STATUS] = @p_RECORD_STATUS,DEP_ID = @p_DEP_ID,[AUTH_STATUS] = @p_AUTH_STATUS,[CHECKER_ID] = @p_CHECKER_ID,[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103),[BRANCH_ID]=@p_BRANCH_ID ,PL_CODE = @p_PL_CODE WHERE REQ_ID= @p_REQ_ID END IF (@STATUS = 'REJECT') BEGIN UPDATE TR_REQUEST_SHOP_DOC SET STATUS = 'NEW', AUTH_STATUS = 'E' WHERE REQ_ID= @p_REQ_ID UPDATE PL_REQUEST_PROCESS SET [STATUS]='P' WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='REJECT' INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, CHECKER_ID) VALUES(@p_REQ_ID,'ADDNEW','C',@p_BRANCH_ID,'',@p_DEP_ID, @p_MAKER_ID) INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, PROCESS_TYPE) VALUES(@p_REQ_ID,'APPNEW','U','GDDV',@p_BRANCH_ID,'ADDNEW',@p_DEP_ID, 'Approve') -- IF(@p_BRANCH_ID='DV0001') -- BEGIN -- UPDATE PL_REQUEST_PROCESS SET [STATUS]='P' WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='ADDNEW' -- UPDATE PL_REQUEST_PROCESS SET [STATUS]='P' WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='APPNEW' -- --GỬI QLTS -- INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID, DEP_ID) -- VALUES(@p_REQ_ID,'QLTS_N','C','QLTS','DV0001','APPNEW', 'DEP000000000048') -- UPDATE TR_REQUEST_SHOP_DOC SET [STATUS]='QLTS_N', AUTH_STATUS = 'U' WHERE REQ_ID=@p_REQ_ID -- END INSERT INTO dbo.PL_PROCESS ( REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC,NOTES ) VALUES ( @p_REQ_ID, 'UPDATE', @p_MAKER_ID, GETDATE(), N'Cập nhật phiếu bị từ chối', N'Nhân viên tạo phiếu cập nhật sau từ chối' ) END END IF @@Error <> 0 GOTO ABORT PRINT 'UPD MASTER SUCCESS' --insert asset detail tương đương như NỘI DUNG trong bảng contract detail Declare @REQDT_ID VARCHAR(15), @PLAN_ID varchar(15), @TRADE_ID varchar(15), @ASS_GROUP_ID varchar(15), @DESCRIPTION nvarchar(500), @UNIT_ID varchar(15), @QUANTITY decimal(18), @PRICE decimal(18), @TOTAL_AMT decimal(18), @NOTES nvarchar(1000), @REQ_ID varchar(15) = NULL, @RECEIVE_BRANCH varchar(15)=NULL, @RECEIVE_ADDR nvarchar(1000)=NULL, @RECEIVE_PERSON nvarchar(500)=NULL, @RECEIVE_TEL varchar(100)=NULL, @RECEIVE_SUBBRANCH varchar(20), @RECEIVE_DEP varchar(20), @RECEIVE_EMAIL nvarchar(200), @REQ_DT_TYPE VARCHAR(20), @QTY_ETM DECIMAL(18,0), @EMP_CODE VARCHAR(15), @EMP_ID VARCHAR(15), @TYPE_XL VARCHAR(50), @DO_EMAIL VARCHAR(200), @ASS_ID VARCHAR(20), @REASON NVARCHAR(1200) IF(@STATUS = 'QLTS_NL') BEGIN DELETE FROM TR_REQUEST_SHOP_DOC_DT WHERE REQ_DOC_ID = @p_REQ_ID AND (REQ_DT_TYPE = 'BUYNEW' OR REQ_DT_TYPE = 'XKSD') END ELSE IF(@STATUS = 'NEW' OR @STATUS = 'REJECT') BEGIN DELETE FROM TR_REQUEST_SHOP_DOC_DT WHERE REQ_DOC_ID = @p_REQ_ID AND (REQ_DT_TYPE = 'ORGINAL' OR REQ_DT_TYPE = 'ASSET_BROKEN') END ELSE DELETE FROM TR_REQUEST_SHOP_DOC_DT WHERE REQ_DOC_ID = @p_REQ_ID AND REQ_DT_TYPE <> 'ASSET_BROKEN' FETCH NEXT FROM AssetDetail INTO @REQDT_ID,@PLAN_ID,@TRADE_ID,@ASS_GROUP_ID,@DESCRIPTION,@UNIT_ID,@QUANTITY, @PRICE,@TOTAL_AMT,@NOTES, @RECEIVE_BRANCH, @RECEIVE_ADDR, @RECEIVE_PERSON, @RECEIVE_TEL,@RECEIVE_SUBBRANCH ,@RECEIVE_DEP,@RECEIVE_EMAIL,@REQ_DT_TYPE, @QTY_ETM ,@EMP_CODE,@EMP_ID,@TYPE_XL,@DO_EMAIL,@ASS_ID,@REASON WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @l_REQDT_ID VARCHAR(15) EXEC SYS_CodeMasters_Gen 'TR_REQUEST_SHOP_DOC_DT', @l_REQDT_ID out IF @l_REQDT_ID='' OR @l_REQDT_ID IS NULL GOTO ABORT -- IF(@EMP_ID IS NULL OR @EMP_ID='' ) -- BEGIN -- ROLLBACK TRANSACTION -- SELECT '-1' as Result, @p_REQ_ID REQ_ID, N'Người sử dụng bắt buộc nhập' ErrorDesc -- RETURN '-1' -- END -- IF(@RECEIVE_EMAIL IS NULL OR @RECEIVE_EMAIL='' AND @REQ_DT_TYPE <> 'ASSET_BROKEN') -- BEGIN -- ROLLBACK TRANSACTION -- SELECT '-1' as Result, @p_REQ_ID REQ_ID, N'Email người nhận bắt buộc nhập' ErrorDesc -- RETURN '-1' -- END IF(@QTY_ETM IS NULL AND @REQ_DT_TYPE <> 'ASSET_BROKEN') BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, @p_REQ_ID REQ_ID, N'Số lượng bắt buộc nhập' ErrorDesc RETURN '-1' END IF((SELECT COUNT(*) FROM TR_REQUEST_SHOP_DOC_DT WHERE TRADE_ID = @p_REQ_ID AND ASS_GROUP_ID = @ASS_GROUP_ID) > 0) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, @p_REQ_ID REQ_ID, N'Nhóm tài sản chọn không thể trùng nhau' ErrorDesc RETURN '-1' END IF(@REQ_DT_TYPE = 'ASSET_BROKEN' AND NOT EXISTS(SELECT * FROM TR_REQUEST_SHOP_DOC_DT TRSDD WHERE TRSDD.ASS_ID = @ASS_ID AND TRSDD.REQ_DOC_ID = @p_REQ_ID)) BEGIN INSERT INTO TR_REQUEST_SHOP_DOC_DT(REQDT_ID,REQ_DOC_ID,PLAN_ID,TRADE_ID,ASS_GROUP_ID,[DESCRIPTION],UNIT_ID, QUANTITY,PRICE,TOTAL_AMT,NOTES, RECEIVE_BRANCH, RECEIVE_ADDR, RECEIVE_PERSON, RECEIVE_TEL,RECORD_STATUS, MAKER_ID,CREATE_DT,AUTH_STATUS,CHECKER_ID,APPROVE_DT,RECEIVE_SUBBRANCH,RECEIVE_DEP,RECEIVE_EMAIL ,REQ_DT_TYPE,QTY_ETM,EMP_CODE,EMP_ID,TYPE_XL,REASON,ASS_ID) VALUES(@l_REQDT_ID,@p_REQ_ID,@PLAN_ID,@TRADE_ID,@ASS_GROUP_ID,@DESCRIPTION,@UNIT_ID, @QUANTITY,@PRICE,@TOTAL_AMT,@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) ,@RECEIVE_SUBBRANCH,@RECEIVE_DEP,@RECEIVE_EMAIL,@REQ_DT_TYPE,@QTY_ETM ,@EMP_CODE,@EMP_ID,@TYPE_XL,@REASON,@ASS_ID) END ELSE IF(@REQ_DT_TYPE <> 'ASSET_BROKEN') BEGIN INSERT INTO TR_REQUEST_SHOP_DOC_DT(REQDT_ID,REQ_DOC_ID,PLAN_ID,TRADE_ID,ASS_GROUP_ID,[DESCRIPTION],UNIT_ID, QUANTITY,PRICE,TOTAL_AMT,NOTES, RECEIVE_BRANCH, RECEIVE_ADDR, RECEIVE_PERSON, RECEIVE_TEL,RECORD_STATUS, MAKER_ID,CREATE_DT,AUTH_STATUS,CHECKER_ID,APPROVE_DT,RECEIVE_SUBBRANCH,RECEIVE_DEP,RECEIVE_EMAIL ,REQ_DT_TYPE,QTY_ETM,EMP_CODE,EMP_ID,TYPE_XL,REASON,ASS_ID) VALUES(@l_REQDT_ID,@p_REQ_ID,@PLAN_ID,@TRADE_ID,@ASS_GROUP_ID,@DESCRIPTION,@UNIT_ID, @QUANTITY,@PRICE,@TOTAL_AMT,@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) ,@RECEIVE_SUBBRANCH,@RECEIVE_DEP,@RECEIVE_EMAIL,@REQ_DT_TYPE,@QTY_ETM ,@EMP_CODE,@EMP_ID,@TYPE_XL,@REASON,@ASS_ID) END IF @@ERROR <> 0 GOTO ABORT -- next Group_Id FETCH NEXT FROM AssetDetail INTO @REQDT_ID,@PLAN_ID,@TRADE_ID,@ASS_GROUP_ID,@DESCRIPTION,@UNIT_ID,@QUANTITY, @PRICE,@TOTAL_AMT,@NOTES, @RECEIVE_BRANCH, @RECEIVE_ADDR, @RECEIVE_PERSON, @RECEIVE_TEL,@RECEIVE_SUBBRANCH ,@RECEIVE_DEP,@RECEIVE_EMAIL,@REQ_DT_TYPE, @QTY_ETM ,@EMP_CODE,@EMP_ID,@TYPE_XL,@DO_EMAIL,@ASS_ID,@REASON END CLOSE AssetDetail DEALLOCATE AssetDetail IF(EXISTS(SELECT * FROM TR_REQUEST_SHOP_DOC TRSD WHERE TRSD.REQ_ID = @p_REQ_ID AND TRSD.STATUS = 'NEW' OR TRSD.STATUS = 'REJECT' OR TRSD.STATUS = 'QLTS_N')) BEGIN IF(@STATUS <> 'QLTS_NL') BEGIN Declare @hdoc2 INT Declare @l_NOTES NVARCHAR(MAX), @l_AUTH_STATUS VARCHAR(5), @COST_ID varchar(15), @REQ_COST_ID varchar(15) Exec sp_xml_preparedocument @hdoc2 Output,@p_ListCostCenter DECLARE ListCostCenters CURSOR FOR SELECT * FROM OPENXML(@hdoc2,'/Root/ListCostCenter',2) WITH ( REQ_COST_ID VARCHAR(15), COST_ID varchar(15), AUTH_STATUS VARCHAR(5), NOTES nvarchar(MAX) ) OPEN ListCostCenters DELETE TR_REQUEST_SHOP_COSTCENTER WHERE REQ_ID = @p_REQ_ID FETCH NEXT FROM ListCostCenters INTO @REQ_COST_ID,@COST_ID,@l_AUTH_STATUS, @l_NOTES WHILE @@FETCH_STATUS = 0 BEGIN BEGIN EXEC SYS_CodeMasters_Gen 'TR_REQUEST_SHOP_COSTCENTER', @REQ_COST_ID out IF @REQ_COST_ID='' OR @REQ_COST_ID IS NULL GOTO ABORT INSERT INTO dbo.TR_REQUEST_SHOP_COSTCENTER ( REQ_COST_ID, COST_ID, REQ_ID, NOTES, AUTH_STATUS, MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT ) VALUES ( @REQ_COST_ID, -- REQ_COST_ID - varchar(15) @COST_ID, -- COST_ID - varchar(15) @p_REQ_ID, -- REQ_ID - varchar(15) @l_NOTES, -- NOTES - nvarchar(500) 'E', -- AUTH_STATUS - varchar(1) NULL, -- MAKER_ID - varchar(15) GETDATE(), -- CREATE_DT - datetime '', -- CHECKER_ID - varchar(15) NULL -- APPROVE_DT - datetime ) END IF @@ERROR <> 0 GOTO ABORT2 -- next Group_Id FETCH NEXT FROM ListCostCenters INTO @REQ_COST_ID,@COST_ID,@l_AUTH_STATUS, @l_NOTES END CLOSE ListCostCenters DEALLOCATE ListCostCenters END END COMMIT TRANSACTION SELECT '0' as Result, @p_REQ_ID REQ_ID, '' ErrorDesc RETURN '0' ABORT: BEGIN CLOSE AssetDetail DEALLOCATE AssetDetail ROLLBACK TRANSACTION SELECT '-1' AS RESULT RETURN '-1' End ABORT2: BEGIN CLOSE ListCostCenters DEALLOCATE ListCostCenters ROLLBACK TRANSACTION SELECT '-1' AS RESULT RETURN '-1' End