ALTER PROCEDURE dbo.MW_OUT_Ins @p_TRN_Date VARCHAR(25) = NULL, @p_TRN_TIME VARCHAR(50) = NULL, @p_BRN_ID VARCHAR(15) = NULL, @p_DEPT_ID VARCHAR(15) = NULL, @p_QTY DECIMAL(18, 2) = NULL, @p_PRICE NUMERIC(18, 0) = NULL, @p_TOTAL_AMT NUMERIC(18, 2) = NULL, @p_NOTES NVARCHAR(1000), @p_OUT_DESC NVARCHAR(500) = NULL, @p_AUTH_STATUS VARCHAR(1) = NULL, @p_MAKER_ID VARCHAR(100) = NULL, @p_CREATE_DT VARCHAR(25) = NULL, @p_CHECKER_ID VARCHAR(100) = NULL, @p_APPROVE_DT VARCHAR(25) = NULL, @p_KT_AUTH_STATUS VARCHAR(1) = NULL, @p_KT_MAKER_ID VARCHAR(100) = NULL, @p_KT_CREATE_DT VARCHAR(25) = NULL, @p_KT_CHECKER_ID VARCHAR(100) = NULL, @p_KT_APPROVE_DT VARCHAR(25) = NULL, @p_RECORD_STATUS VARCHAR(1) = NULL, @p_XmlData XML = NULL, @p_CORE_NOTE NVARCHAR(500) = NULL, @p_BRANCH_CREATE VARCHAR(15) = NULL, @p_WARE_HOUSE VARCHAR(15) = NULL, @p_MW_REQ_ID VARCHAR(50) = NULL, @p_PROMOTION_ID VARCHAR(50) = NULL, @p_WARE_ID VARCHAR(50) = NULL, @p_STATUS VARCHAR(50) = NULL, @p_REQ_ACOUNT VARCHAR(5) = NULL, @p_REQ_USER VARCHAR(150) = NULL AS DECLARE --DVSD --@OUT_DT_ID varchar(15), @p_OUT_CODE NVARCHAR(100) = NULL ,@OUT_ID VARCHAR(15) = NULL ,@MAST_BAL_ID VARCHAR(15) = NULL ,@CUST_NAME NVARCHAR(200) = NULL ,@QTY DECIMAL(18, 2) = NULL ,@QTY_OLD DECIMAL(18, 2) = NULL ,@QTY_REAL_OLD DECIMAL(18, 2) = NULL ,@PRICE NUMERIC(18, 0) = NULL ,@TOTAL_AMT NUMERIC(18, 2) = NULL ,@NOTES NVARCHAR(1000) = NULL ,@TO_BRN_ID VARCHAR(15) = NULL ,@TO_DEPT_ID VARCHAR(15) = NULL ,@EVENT_NAME NVARCHAR(1000) = NULL ,@IS_BCT VARCHAR(1) = 'N' ,-- LUCTV BO SUNG 05-04-2019 TEN SU KIEN TREN LUOI DS XUAT VAT LIEU @COST_ACC VARCHAR(50) = NULL ,@CUSTOMER_NAME NVARCHAR(100) = NULL ,@CUSTOMER_ID VARCHAR(100) = NULL ,@CUSTOMER_CCCD VARCHAR(100) = NULL ,@CUSTOMER_LOCATION NVARCHAR(1000) = NULL ,@UNIT_RECEIVE VARCHAR(50) = NULL ,@DEP_RECEIVE VARCHAR(50) = NULL ,@UNIT_CHARGE VARCHAR(50) = NULL ,@DEP_CHARGE VARCHAR(50) = NULL ,@UNIT_PAY VARCHAR(50) = NULL DECLARE @INDEX INT = 0 DECLARE @PRICE_CODE VARCHAR(30) DECLARE @hdoc INT; DECLARE @QUANTITY_ALLOCATION DECIMAL(18, 2) ,@ALLOCATED DECIMAL(18, 2) ,@MATERIAL_ID VARCHAR(50); DECLARE @BRANCH_OUT VARCHAR(50) ,@UNIT_RECEIVE_TYPE VARCHAR(15) ,@UNIT_CHARGE_TYPE VARCHAR(15) EXEC sp_xml_preparedocument @hdoc OUTPUT ,@p_XmlData; DECLARE XmlData CURSOR FOR SELECT * FROM OPENXML(@hdoc, '/Root/XmlData', 2) WITH (OUT_ID VARCHAR(15), MAST_BAL_ID VARCHAR(15), CUST_NAME NVARCHAR(200), QTY DECIMAL(18, 2), QTY_OLD DECIMAL(18, 2), QTY_REAL_OLD DECIMAL(18, 2), PRICE NUMERIC(18, 0), TOTAL_AMT NUMERIC(18, 2), NOTES NVARCHAR(1000), TO_BRN_ID VARCHAR(15), TO_DEPT_ID VARCHAR(15), EVENT_NAME NVARCHAR(1000), IS_BCT VARCHAR(1), COST_ACC VARCHAR(50), UNIT_RECEIVE VARCHAR(50), DEP_RECEIVE VARCHAR(50), UNIT_CHARGE VARCHAR(50), DEP_CHARGE VARCHAR(50), UNIT_PAY VARCHAR(50), CUSTOMER_NAME VARCHAR(100), CUSTOMER_ID VARCHAR(100), CUSTOMER_CCCD VARCHAR(100), CUSTOMER_LOCATION VARCHAR(1000)) OPEN XmlData; BEGIN TRANSACTION; IF (@p_WARE_ID IS NULL OR @p_WARE_ID = '') BEGIN ROLLBACK TRANSACTION SELECT '-1' AS Result ,'' OUT_ID ,N'Loại kho đang để trống. Vui lòng chọn và thử lại' ErrorDesc RETURN '-1' END DECLARE @p_OUT_ID VARCHAR(15) ,@l_COST_ACC VARCHAR(50); EXEC [MW_OUT_CODE_Gen] @p_BRANCH_CREATE ,@p_WARE_HOUSE ,@p_OUT_CODE OUT; --IF() EXEC SYS_CodeMasters_Gen 'MW_OUT' ,@p_OUT_ID OUT; IF @p_OUT_ID = '' OR @p_OUT_ID IS NULL GOTO ABORT; SET @p_STATUS = 'ADDNEW' INSERT INTO [dbo].[MW_OUT] ([OUT_ID], [TRN_Date], [TRN_TIME], [BRN_ID], [DEPT_ID], [QTY], [PRICE], [TOTAL_AMT], [NOTES], [OUT_DESC], [AUTH_STATUS], [MAKER_ID], [CREATE_DT], [CHECKER_ID], [APPROVE_DT], [RECORD_STATUS], [CORE_NOTE], [BRANCH_CREATE], [WARE_HOUSE], [OUT_CODE], MW_REQ_ID, PROMOTION_ID, WARE_ID, STATUS, REQ_ACOUNT , IS_CANCEL, REQ_USER) VALUES ( @p_OUT_ID ,CONVERT(DATETIME, @p_TRN_Date, 103) ,@p_TRN_TIME ,@p_BRN_ID ,@p_DEPT_ID ,@p_QTY ,@p_PRICE ,@p_TOTAL_AMT ,@p_NOTES ,@p_OUT_DESC ,'E' ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,NULL ,NULL ,'1' ,@p_CORE_NOTE ,@p_BRANCH_CREATE ,@p_WARE_HOUSE ,@p_OUT_CODE ,@p_MW_REQ_ID ,@p_PROMOTION_ID ,@p_WARE_ID ,@p_STATUS ,@p_REQ_ACOUNT ,'0',@p_REQ_USER) IF @@error <> 0 GOTO ABORT; --Insert XmlData FETCH NEXT FROM XmlData INTO @OUT_ID, @MAST_BAL_ID, @CUST_NAME, @QTY, @QTY_OLD, @QTY_REAL_OLD, @PRICE, @TOTAL_AMT, @NOTES, @TO_BRN_ID, @TO_DEPT_ID, @EVENT_NAME, @IS_BCT, @COST_ACC, @UNIT_RECEIVE, @DEP_RECEIVE, @UNIT_CHARGE, @DEP_CHARGE, @UNIT_PAY, @CUSTOMER_NAME, @CUSTOMER_ID, @CUSTOMER_CCCD, @CUSTOMER_LOCATION WHILE @@fetch_status = 0 BEGIN SET @UNIT_RECEIVE_TYPE = (SELECT CB.BRANCH_TYPE FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @UNIT_RECEIVE) SET @UNIT_CHARGE_TYPE = (SELECT CB.BRANCH_TYPE FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @UNIT_CHARGE) IF @UNIT_RECEIVE_TYPE = 'HS' AND (@DEP_RECEIVE IS NULL OR @DEP_RECEIVE = '') BEGIN ROLLBACK TRANSACTION CLOSE XmlData; DEALLOCATE XmlData; SELECT '-1' AS Result ,'' OUT_ID ,N'Dòng ' + CONVERT(VARCHAR(5), @INDEX) + N': Phòng ban nhận không được để trống.' ErrorDesc RETURN '-1' END IF @UNIT_CHARGE_TYPE = 'HS' AND (@DEP_CHARGE IS NULL OR @DEP_CHARGE = '') BEGIN ROLLBACK TRANSACTION CLOSE XmlData; DEALLOCATE XmlData; SELECT '-1' AS Result ,'' OUT_ID ,N'Dòng ' + CONVERT(VARCHAR(5), @INDEX) + N': Phòng ban chịu chi phí không được để trống.' ErrorDesc RETURN '-1' END --Thuantm TẠO PHIẾU XUẤT KIỂM TRA SỐ LƯỢNG CẤP PHÁT PHIẾU YÊU CẦU NẾU số lượng xuất vượt quá số lượng cấp phát thì chặn. SELECT @MATERIAL_ID = MATERIAL_ID FROM MW_MAST_BAL mmb WHERE mmb.MAST_BAL_ID = @MAST_BAL_ID SELECT @QUANTITY_ALLOCATION = mrd.QUANTITY_ALLOCATION ,@ALLOCATED = mrd.ALLOCATED FROM MW_REQ_DT mrd WHERE mrd.REQ_CODE = @p_MW_REQ_ID AND mrd.MATERIAL_ID = @MATERIAL_ID ---- CHẶN SỐ LƯỢNG XUẤT LỚN HƠN PYC ---- DECLARE @CUR_QTY_PYC DECIMAL IF(@p_MW_REQ_ID IS NOT NULL AND @p_MW_REQ_ID <> '') BEGIN SELECT @CUR_QTY_PYC = MRD.QUANTITY_REQ FROM MW_REQ_DT MRD WHERE MRD.REQ_ID = @p_MW_REQ_ID AND MRD.BRANCH_USE = @UNIT_RECEIVE AND ISNULL(MRD.DEP_USE, '') = ISNULL(@DEP_RECEIVE, '') AND MRD.MATERIAL_ID = @MATERIAL_ID IF(@CUR_QTY_PYC < @QTY) BEGIN ROLLBACK TRANSACTION CLOSE XmlData; DEALLOCATE XmlData; SELECT '-1' AS Result ,'' OUT_ID ,N'Dòng ' + CONVERT(VARCHAR(5), @INDEX) + N': Số lượng xuất lớn hơn số lượng được yêu cầu.' ErrorDesc RETURN '-1' END END SET @INDEX = @INDEX + 1 SET @PRICE_CODE = (SELECT PRICE_CODE FROM MW_MAST_PRICE WHERE PRICE_ID = (SELECT PRICE_ID FROM MW_MAST_BAL WHERE MAST_BAL_ID = @MAST_BAL_ID)) -- IF (@COST_ACC IS NULL OR @COST_ACC = '') -- BEGIN -- ROLLBACK TRANSACTION -- CLOSE XmlData; -- DEALLOCATE XmlData; -- SELECT '1' AS Result, '' OUT_ID, N'Danh sách đơn vị/ khách hàng nhận, Dòng ' + CONVERT(VARCHAR(5), @INDEX) + N': Tài khoản chi phí không được để trống' ErrorDesc -- RETURN '-1' -- END DECLARE @p_OUT_DT_ID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'MW_OUT_DT' ,@p_OUT_DT_ID OUT; IF @p_OUT_DT_ID = '' OR @p_OUT_DT_ID IS NULL GOTO ABORT; INSERT INTO [dbo].[MW_OUT_DT] ([OUT_DT_ID], [OUT_ID], [MAST_BAL_ID], [CUST_NAME], [QTY], [QTY_OLD], [QTY_REAL_OLD], [PRICE], [TOTAL_AMT], [NOTES], [TO_BRN_ID], [TO_DEPT_ID], [EVENT_NAME], [IS_BCT], [COST_ACC], UNIT_RECEIVE, DEP_RECEIVE, UNIT_CHARGE, DEP_CHARGE, UNIT_PAY, CUSTOMER_NAME, CUSTOMER_ID, CUSTOMER_CCCD, CUSTOMER_LOCATION) VALUES ( @p_OUT_DT_ID ,@p_OUT_ID ,@MAST_BAL_ID ,@CUST_NAME ,@QTY ,(SELECT mmb.QTY_BALANCE FROM MW_MAST_BAL mmb WHERE mmb.MAST_BAL_ID = @MAST_BAL_ID) ,(SELECT mmb.QTY_REAL FROM MW_MAST_BAL mmb WHERE mmb.MAST_BAL_ID = @MAST_BAL_ID) ,@PRICE ,@TOTAL_AMT ,@NOTES ,@TO_BRN_ID ,@TO_DEPT_ID ,@EVENT_NAME ,@IS_BCT ,@COST_ACC ,@UNIT_RECEIVE ,@DEP_RECEIVE ,@UNIT_CHARGE ,@DEP_CHARGE ,@UNIT_PAY ,@CUSTOMER_NAME ,@CUSTOMER_ID ,@CUSTOMER_CCCD ,@CUSTOMER_LOCATION) IF @@error <> 0 GOTO ABORT; FETCH NEXT FROM XmlData INTO @OUT_ID, @MAST_BAL_ID, @CUST_NAME, @QTY, @QTY_OLD, @QTY_REAL_OLD, @PRICE, @TOTAL_AMT, @NOTES, @TO_BRN_ID, @TO_DEPT_ID, @EVENT_NAME, @IS_BCT, @COST_ACC, @UNIT_RECEIVE, @DEP_RECEIVE, @UNIT_CHARGE, @DEP_CHARGE, @UNIT_PAY, @CUSTOMER_NAME, @CUSTOMER_ID, @CUSTOMER_CCCD, @CUSTOMER_LOCATION END; CLOSE XmlData; DEALLOCATE XmlData; UPDATE MW_OUT SET TOTAL_AMT = (SELECT SUM(TOTAL_AMT) FROM MW_OUT_DT WHERE OUT_ID = @p_OUT_ID) WHERE OUT_ID = @p_OUT_ID INSERT INTO dbo.MW_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES) VALUES (@p_OUT_ID, -- REQ_ID - varchar(15) 'INSERT', -- PROCESS_ID - varchar(10) @p_MAKER_ID, -- CHECKER_ID - varchar(15) GETDATE(), -- APPROVE_DT - datetime N'Thêm mới phiếu xuất vật liệu thành công', N'Thêm mới phiếu xuất vật liệu' -- PROCESS_DESC - nvarchar(1000) ) ---- CHECK BƯỚC QUY TRÌNH INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],ROLE_USER,[STATUS],BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, CHECKER_ID) VALUES(@p_OUT_ID,'ADDNEW','NVTT','C',@p_BRN_ID,'',@p_DEPT_ID, @p_MAKER_ID) COMMIT TRANSACTION; SELECT '0' AS Result ,@p_OUT_ID OUT_ID ,'' ErrorDesc; RETURN '0'; ABORT: BEGIN CLOSE XmlData; DEALLOCATE XmlData; ROLLBACK TRANSACTION; SELECT '-1' AS Result ,'' OUT_ID ,'' ErrorDesc; RETURN '-1'; END; GO ALTER PROCEDURE dbo.MW_OUT_Upd @p_OUT_ID VARCHAR(15) = NULL, @p_TRN_Date VARCHAR(25) = NULL, @p_TRN_TIME VARCHAR(50) = NULL, @p_BRN_ID VARCHAR(15) = NULL, @p_DEPT_ID VARCHAR(15) = NULL, @p_QTY DECIMAL(18,2) = NULL, @p_PRICE NUMERIC(18, 0) = NULL, @p_TOTAL_AMT NUMERIC(18, 2) = NULL, @p_NOTES NVARCHAR(1000), @p_OUT_DESC NVARCHAR(500) = NULL, @p_AUTH_STATUS VARCHAR(1) = NULL, @p_MAKER_ID VARCHAR(100) = NULL, @p_CREATE_DT VARCHAR(25) = NULL, @p_CHECKER_ID VARCHAR(100) = NULL, @p_APPROVE_DT VARCHAR(25) = NULL, @p_KT_AUTH_STATUS VARCHAR(1) = NULL, @p_KT_MAKER_ID VARCHAR(100) = NULL, @p_KT_CREATE_DT VARCHAR(25) = NULL, @p_KT_CHECKER_ID VARCHAR(100) = NULL, @p_KT_APPROVE_DT VARCHAR(25) = NULL, @p_RECORD_STATUS VARCHAR(1) = NULL, @p_XmlData XML = NULL, @p_CORE_NOTE nvarchar(500) = NULL, @p_WARE_HOUSE VARCHAR(15) = NULL, @p_MW_REQ_ID VARCHAR(50)=NULL, @p_PROMOTION_ID VARCHAR(50) = NULL, @p_WARE_ID VARCHAR(50)=NULL,@p_REQ_ACOUNT VARCHAR(5)=NULL, @p_REQ_USER VARCHAR(150) = NULL AS DECLARE --DVSD @OUT_ID VARCHAR(15) = NULL, @MAST_BAL_ID VARCHAR(15) = NULL, @CUST_NAME NVARCHAR(200) = NULL, @QTY DECIMAL(18,2) = NULL, @QTY_OLD DECIMAL(18,2) = NULL, @QTY_REAL_OLD DECIMAL(18,2)=NULL, @PRICE NUMERIC(18, 0) = NULL, @TOTAL_AMT NUMERIC(18, 2) = NULL, @NOTES NVARCHAR(1000) = NULL, @TO_BRN_ID varchar(15) = NULL, @TO_DEPT_ID varchar(15) = NULL, @EVENT_NAME NVARCHAR(1000) = NULL, --LUCTV BO SUNG 15-02-2019 TEN SU KIEN TREN LUOI DS XUAT VAT LIEU @IS_BCT VARCHAR(1) = 'N',-- LUCTV BO SUNG 05-04-2019 TEN SU KIEN TREN LUOI DS XUAT VAT LIEU @COST_ACC VARCHAR(50) = NULL, @UNIT_RECEIVE VARCHAR(50)= NULL, @DEP_RECEIVE VARCHAR(50)= NULL, @UNIT_CHARGE VARCHAR(50)= NULL, @DEP_CHARGE VARCHAR(50)= NULL, @UNIT_PAY VARCHAR(50)= NULL, @CUSTOMER_NAME NVARCHAR(100)= NULL, @CUSTOMER_ID VARCHAR(100)= NULL, @CUSTOMER_LOCATION NVARCHAR(1000)= NULL DECLARE @INDEX INT =0 DECLARE @PRICE_CODE VARCHAR(30) DECLARE @hdoc INT; DECLARE @QUANTITY_ALLOCATION DECIMAL(18,2), @ALLOCATED DECIMAL(18,2), @MATERIAL_ID VARCHAR(50) EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XmlData; DECLARE XmlData CURSOR FOR SELECT * FROM OPENXML(@hdoc, '/Root/XmlData', 2) WITH ( OUT_ID VARCHAR(15), MAST_BAL_ID VARCHAR(15), CUST_NAME NVARCHAR(200), QTY DECIMAL(18,2), QTY_OLD DECIMAL(18,2), QTY_REAL_OLD DECIMAL(18,2), PRICE NUMERIC(18, 0), TOTAL_AMT NUMERIC(18, 2), NOTES NVARCHAR(1000), TO_BRN_ID varchar(15), TO_DEPT_ID varchar(15), EVENT_NAME NVARCHAR(1000), IS_BCT VARCHAR(1),-- LUCTV BO SUNG 05-04-2019 TEN SU KIEN TREN LUOI DS XUAT VAT LIEU COST_ACC VARCHAR(50), UNIT_RECEIVE VARCHAR(50), DEP_RECEIVE VARCHAR(50), UNIT_CHARGE VARCHAR(50), DEP_CHARGE VARCHAR(50), UNIT_PAY VARCHAR(50), CUSTOMER_NAME VARCHAR(100), CUSTOMER_ID VARCHAR(100), CUSTOMER_LOCATION VARCHAR(1000) ); OPEN XmlData; BEGIN TRANSACTION; IF(@p_WARE_ID IS NULL OR @p_WARE_ID = '') BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' OUT_ID, N'Loại kho đang để trống. Vui lòng chọn và thử lại' ErrorDesc RETURN '-1' END IF(EXISTS(SELECT * FROM MW_OUT mo WHERE OUT_ID = @p_OUT_ID AND mo.STATUS = 'REQ_ACOUNT')) BEGIN UPDATE MW_OUT SET REQ_ACOUNT=@p_REQ_ACOUNT WHERE OUT_ID = @p_OUT_ID DECLARE @CUR_PROCESS VARCHAR(20) = (SELECT TOP 1 MRP.PROCESS_ID FROM MW_REQUEST_PROCESS MRP WHERE MRP.REQ_ID = @p_OUT_ID AND MRP.STATUS = 'C') -- UPDATE MW_REQUEST_PROCESS SET STATUS = 'P' WHERE REQ_ID = @p_OUT_ID AND STATUS = 'C' -- INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, PROCESS_TYPE,RECEPTION_DT,NOTES) -- VALUES(@p_OUT_ID,'OUT_KT',N'Giao dịch viên xử lý','C','GDV',@p_BRN_ID,@CUR_PROCESS,@p_DEPT_ID, 'Approve',GETDATE(), N'Trưởng đơn vị phê duyệt') END ELSE BEGIN -- IF (SELECT mo.AUTH_STATUS FROM MW_OUT mo WHERE OUT_ID = @p_OUT_ID) = 'R' -- BEGIN -- SET @p_AUTH_STATUS = 'E' -- END ELSE SET @p_AUTH_STATUS = 'E' UPDATE [dbo].[MW_OUT] SET [QTY] = @p_QTY, AUTH_STATUS=@p_AUTH_STATUS, [PRICE] = @p_PRICE, [TOTAL_AMT] = @p_TOTAL_AMT, [NOTES] = @p_NOTES, MAKER_ID=@p_MAKER_ID, [CORE_NOTE] = @p_CORE_NOTE, WARE_HOUSE= @p_WARE_HOUSE,DEPT_ID = @p_DEPT_ID,REQ_ACOUNT = @p_REQ_ACOUNT, MW_REQ_ID = @p_MW_REQ_ID, PROMOTION_ID = @p_PROMOTION_ID, WARE_ID = @p_WARE_ID, REQ_USER = @p_REQ_USER WHERE [OUT_ID] = @p_OUT_ID; IF @@Error <> 0 GOTO ABORT; --Insert XmlData DECLARE @l_COST_ACC VARCHAR(50); DELETE FROM MW_OUT_DT WHERE OUT_ID = @p_OUT_ID; FETCH NEXT FROM XmlData INTO @OUT_ID, @MAST_BAL_ID, @CUST_NAME, @QTY, @QTY_OLD,@QTY_REAL_OLD,@PRICE,@TOTAL_AMT, @NOTES,@TO_BRN_ID, @TO_DEPT_ID, @EVENT_NAME,@IS_BCT, @COST_ACC,@UNIT_RECEIVE,@DEP_RECEIVE,@UNIT_CHARGE,@DEP_CHARGE,@UNIT_PAY,@CUSTOMER_NAME,@CUSTOMER_ID,@CUSTOMER_LOCATION WHILE @@FETCH_STATUS = 0 BEGIN --Thuantm TẠO PHIẾU XUẤT KIỂM TRA SỐ LƯỢNG CẤP PHÁT PHIẾU YÊU CẦU NẾU số lượng xuất vượt quá số lượng cấp phát thì chặn. SELECT @MATERIAL_ID=MATERIAL_ID FROM MW_MAST_BAL mmb WHERE mmb.MAST_BAL_ID = @MAST_BAL_ID SELECT @QUANTITY_ALLOCATION=mrd.QUANTITY_ALLOCATION, @ALLOCATED=mrd.ALLOCATED FROM MW_REQ_DT mrd WHERE mrd.REQ_CODE = @p_MW_REQ_ID AND mrd.MATERIAL_ID = @MATERIAL_ID SET @INDEX = @INDEX +1 SET @PRICE_CODE =(SELECT PRICE_CODE FROM MW_MAST_PRICE WHERE PRICE_ID =(SELECT PRICE_ID FROM MW_MAST_BAL WHERE MAST_BAL_ID=@MAST_BAL_ID)) SET @l_COST_ACC = (SELECT TOP 1 COST_ACC = CASE WHEN MT.GROUP_ID = 'MWG000000016073' and LEN(@COST_ACC) > 0 then @COST_ACC WHEN @COST_ACC = null then MT.EXP_ACCTNO WHEN @COST_ACC = '' then MT.EXP_ACCTNO ELSE MT.EXP_ACCTNO END FROM dbo.MW_MAST_BAL A LEFT JOIN MW_MATERIAL MT ON A.MATERIAL_ID = MT.MATERIAL_ID WHERE A.MAST_BAL_ID = @MAST_BAL_ID) ---- CHẶN SỐ LƯỢNG XUẤT LỚN HƠN PYC ---- DECLARE @CUR_QTY_PYC DECIMAL IF(@p_MW_REQ_ID IS NOT NULL AND @p_MW_REQ_ID <> '') BEGIN SELECT @CUR_QTY_PYC = MRD.QUANTITY_REQ FROM MW_REQ_DT MRD WHERE MRD.REQ_ID = @p_MW_REQ_ID AND MRD.BRANCH_USE = @UNIT_RECEIVE AND ISNULL(MRD.DEP_USE, '') = ISNULL(@DEP_RECEIVE, '') AND MRD.MATERIAL_ID = @MATERIAL_ID IF(@CUR_QTY_PYC < @QTY) BEGIN ROLLBACK TRANSACTION CLOSE XmlData; DEALLOCATE XmlData; SELECT '-1' AS Result ,'' OUT_ID ,N'Dòng ' + CONVERT(VARCHAR(5), @INDEX) + N': Số lượng xuất lớn hơn số lượng được yêu cầu.' ErrorDesc RETURN '-1' END END -- IF(@COST_ACC IS NULL OR @COST_ACC = '') -- BEGIN -- ROLLBACK TRANSACTION -- CLOSE XmlData; -- DEALLOCATE XmlData; -- SELECT '-1' as Result, '' OUT_ID, N'Danh sách đơn vị/ khách hàng nhận, Dòng ' + CONVERT(VARCHAR(5),@INDEX)+N': Tài khoản chi phí không được để trống' ErrorDesc -- RETURN '-1' -- END DECLARE @p_OUT_DT_ID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'MW_OUT_DT', @p_OUT_DT_ID OUT; IF @p_OUT_DT_ID = '' OR @p_OUT_DT_ID IS NULL GOTO ABORT; INSERT INTO [dbo].[MW_OUT_DT] ( [OUT_DT_ID], [OUT_ID], [MAST_BAL_ID], [CUST_NAME], [QTY], [QTY_OLD], QTY_REAL_OLD, [PRICE], [TOTAL_AMT], [NOTES], [TO_BRN_ID], [TO_DEPT_ID],[EVENT_NAME],[IS_BCT],[COST_ACC],UNIT_RECEIVE,DEP_RECEIVE,UNIT_CHARGE,DEP_CHARGE,UNIT_PAY,CUSTOMER_NAME,CUSTOMER_ID,CUSTOMER_LOCATION ) SELECT @p_OUT_DT_ID, @p_OUT_ID, @MAST_BAL_ID, @CUST_NAME, @QTY, (SELECT mmb.QTY_BALANCE FROM MW_MAST_BAL mmb WHERE mmb.MAST_BAL_ID = @MAST_BAL_ID), (SELECT mmb.QTY_REAL FROM MW_MAST_BAL mmb WHERE mmb.MAST_BAL_ID = @MAST_BAL_ID), @PRICE, @TOTAL_AMT, @NOTES, @TO_BRN_ID, @TO_DEPT_ID,@EVENT_NAME,@IS_BCT,@COST_ACC,@UNIT_RECEIVE,@DEP_RECEIVE,@UNIT_CHARGE,@DEP_CHARGE,@UNIT_PAY,@CUSTOMER_NAME,@CUSTOMER_ID,@CUSTOMER_LOCATION IF @@Error <> 0 GOTO ABORT; FETCH NEXT FROM XmlData INTO @OUT_ID, @MAST_BAL_ID, @CUST_NAME, @QTY, @QTY_OLD, @QTY_REAL_OLD, @PRICE, @TOTAL_AMT, @NOTES, @TO_BRN_ID, @TO_DEPT_ID,@EVENT_NAME,@IS_BCT,@COST_ACC,@UNIT_RECEIVE,@DEP_RECEIVE,@UNIT_CHARGE,@DEP_CHARGE,@UNIT_PAY,@CUSTOMER_NAME,@CUSTOMER_ID,@CUSTOMER_LOCATION END; CLOSE XmlData; DEALLOCATE XmlData; -- doanptt 07/06/2022: XÓA CÁC PROCESS UPDATE CŨ --DELETE dbo.MW_PROCESS WHERE REQ_ID = @OUT_ID AND PROCESS_ID = 'UPDATE_HC' INSERT INTO dbo.MW_PROCESS ( REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC,NOTES ) VALUES ( @OUT_ID, -- REQ_ID - varchar(15) 'UPD_HC', -- PROCESS_ID - varchar(10) @p_MAKER_ID, -- CHECKER_ID - varchar(15) GETDATE(), -- APPROVE_DT - datetime N'Cập nhật phiếu xuất vật liệu thành công' , N'Cập nhật phiếu xuất vật liệu' -- PROCESS_DESC - nvarchar(1000) ) ---- CHECK BƯỚC QUY TRÌNH -- INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, CHECKER_ID) -- VALUES(@p_OUT_ID,'ADDNEW','C',@p_BRN_ID,'',@p_DEPT_ID, @p_MAKER_ID) --- UPDATE MW_OUT SET TOTAL_AMT = (SELECT SUM(TOTAL_AMT) FROM MW_OUT_DT WHERE OUT_ID =@p_OUT_ID) END COMMIT TRANSACTION; SELECT '0' AS Result,@p_OUT_ID OUT_ID,'' ErrorDesc; RETURN '0'; ABORT: BEGIN CLOSE XmlData; DEALLOCATE XmlData; ROLLBACK TRANSACTION; SELECT '-1' AS Result,'' OUT_ID,'' ErrorDesc; RETURN '-1'; END;