ALTER PROCEDURE [dbo].[PL_REQUEST_DOC_PROCESS_Upd] @p_REQ_ID VARCHAR(15)=NULL, @p_MAKER_ID varchar(20) = NULL, @p_BRANCH_ID VARCHAR(15)=NULL, @p_TC_NOTES NVARCHAR(4000), @p_KT_NOTES NVARCHAR(4000), @p_TK_TGD_NOTES NVARCHAR(4000), @p_TK_HDQT_NOTES NVARCHAR(4000), @p_ORTHER_NOTES NVARCHAR(4000), @p_ListCostCenter XML, @p_ListTransfer XML, @p_IS_AUTHORITY bit AS DECLARE @IS_AUTHORITY_HDQT VARCHAR(1) DECLARE @DEP_ID VARCHAR(15),@BRANCH_ID VARCHAR(15),@ROLE_ID VARCHAR(20), @PROCESS_CURR VARCHAR(10), @STEP_CURR INT, @STEP_NEXT INT, @PROCESS_NEXT VARCHAR(10) SET @PROCESS_CURR = (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C') SET @PROCESS_NEXT = (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@PROCESS_CURR) SET @DEP_ID=(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID) SET @BRANCH_ID=(SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID) SET @ROLE_ID=(SELECT RoleName FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID) BEGIN TRANSACTION --LUCTV 13042021 - BO SUNG CAP NHAT Y KIEN TK TGD, TK HDQT IF(@PROCESS_CURR='TKTGD') BEGIN UPDATE dbo.PL_REQUEST_DOC SET TK_TGD_NOTES=@p_TK_TGD_NOTES WHERE REQ_ID=@p_REQ_ID END ELSE IF(@PROCESS_CURR='TKHDQT') BEGIN UPDATE dbo.PL_REQUEST_DOC SET TK_HDQT_NOTES=@p_TK_HDQT_NOTES, OTHER_NOTES = @p_ORTHER_NOTES, IS_AUTHORITY = @p_IS_AUTHORITY WHERE REQ_ID=@p_REQ_ID END -- END LUCTV ELSE BEGIN IF(@PROCESS_CURR='KT') BEGIN UPDATE dbo.PL_REQUEST_DOC SET KT_NOTES=@p_KT_NOTES WHERE REQ_ID=@p_REQ_ID END ELSE IF(@PROCESS_CURR='TC') BEGIN UPDATE dbo.PL_REQUEST_DOC SET TC_NOTES=@p_TC_NOTES WHERE REQ_ID=@p_REQ_ID END IF @@Error <> 0 GOTO ABORT Declare @hdoc INT Declare @REQ_TRANSFER_ID varchar(15), @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), @TOTAL_AMT DECIMAL(18,0), @NOTES NVARCHAR(500), @AUTH_STATUS VARCHAR(5), @COST_ID varchar(15), @REQ_COST_ID varchar(15), @DVDM_ID VARCHAR(20), @FR_AMT_APP decimal(18), @FR_AMT_TF DECIMAL(18), @FR_AMT_RECEIVE_TF DECIMAL(18), @FR_AMT_EXE DECIMAL(18), @FR_AMT_ETM DECIMAL(18), @FR_AMT_ETM_TMP decimal(18) IF(EXISTS(SELECT REQ_COST_ID FROM dbo.PL_REQUEST_COSTCENTER WHERE REQ_ID=@p_REQ_ID AND AUTH_STATUS <>'A')) BEGIN --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 ( REQ_COST_ID VARCHAR(15), COST_ID varchar(15), AUTH_STATUS VARCHAR(5), NOTES nvarchar(1000) ) OPEN ListCostCenters FETCH NEXT FROM ListCostCenters INTO @REQ_COST_ID,@COST_ID,@AUTH_STATUS, @NOTES WHILE @@FETCH_STATUS = 0 BEGIN IF(EXISTS(SELECT REQ_COST_ID FROM dbo.PL_REQUEST_COSTCENTER WHERE COST_ID=@COST_ID AND REQ_ID=@p_REQ_ID AND AUTH_STATUS <> 'A')) BEGIN UPDATE dbo.PL_REQUEST_COSTCENTER SET NOTES=@NOTES WHERE REQ_COST_ID=@REQ_COST_ID AND COST_ID=@COST_ID END IF @@ERROR <> 0 GOTO ABORT1 -- next Group_Id FETCH NEXT FROM ListCostCenters INTO @REQ_COST_ID,@COST_ID,@AUTH_STATUS, @NOTES END CLOSE ListCostCenters DEALLOCATE ListCostCenters --IF(NOT EXISTS( SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@PROCESS_CURR AND REQ_ID=@p_REQ_ID AND STATUS='C')) --BEGIN -- UPDATE dbo.PL_REQUEST_PROCESS SET [STATUS]='C' WHERE PROCESS_ID=@PROCESS_NEXT AND REQ_ID=@p_REQ_ID -- UPDATE dbo.PL_REQUEST_DOC SET PROCESS_ID=@PROCESS_NEXT WHERE REQ_ID=@p_REQ_ID --END END IF(EXISTS(SELECT REQ_TRANSFER_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID AND AUTH_STATUS <>'A' AND (FR_BRN_ID=@BRANCH_ID AND (FR_DEP_ID IS NULL OR FR_DEP_ID='' OR FR_DEP_ID=@DEP_ID OR FR_DEP_ID IN (SELECT FATHER_ID FROM CM_DEPARTMENT WHERE DEP_ID =@DEP_ID))))) --- LUCTV 26-04-2023 FIX BỔ SUNG KIỂM TRA THÊM PHÒNG BAN CON VẪN CHỌN NS PHÒNG BAN CHA & GỬI DUYỆT ĐƯỢC 27042023_secretkey BEGIN DECLARE @TABLE TABLE( REQ_TRANSFER_ID VARCHAR(15), 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), AUTH_STATUS VARCHAR(10), 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_ID VARCHAR(20), TO_GD_TYPE_ID VARCHAR(20), FR_AMT_APP decimal(18), FR_AMT_TF DECIMAL(18), FR_AMT_RECEIVE_TF DECIMAL(18), FR_AMT_EXE DECIMAL(18), FR_AMT_ETM DECIMAL(18), FR_AMT_ETM_TMP decimal(18) ) Exec sp_xml_preparedocument @hdoc Output,@p_ListTransfer INSERT INTO @TABLE SELECT * FROM OPENXML(@hDoc,'/Root/ListTransfer',2) WITH ( REQ_TRANSFER_ID VARCHAR(15), 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), AUTH_STATUS VARCHAR(10), 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_ID VARCHAR(20), TO_GD_TYPE_ID VARCHAR(20), FR_AMT_APP decimal(18), FR_AMT_TF DECIMAL(18), FR_AMT_RECEIVE_TF DECIMAL(18), FR_AMT_EXE DECIMAL(18), FR_AMT_ETM DECIMAL(18), FR_AMT_ETM_TMP decimal(18) ) --IF(EXISTS(SELECT * FROM( --SELECT TB.FR_TRADE_ID, SUM(TB.TOTAL_AMT) AS TOTAL_TRANSFER,SUM (ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0)- ISNULL(PT.AMT_TF,0)- ISNULL(PT.AMT_ETM,0) ) AS TOTAL_AMT FROM @TABLE TB --LEFT JOIN dbo.PL_TRADEDETAIL PT ON PT.TRADE_ID=TB.FR_TRADE_ID --GROUP BY TB.FR_TRADE_ID --) TEMP --WHERE TEMP.TOTAL_TRANSFER > TEMP.TOTAL_AMT --)) --BEGIN -- SELECT '-1' Result, '' REQ_ID, N'Số tiền điều chuyển lớn hơn số tiền dự kiến còn lại của hạng mục ngân sách' ErrorDesc -- ROLLBACK TRANSACTION -- RETURN '-1' --END DECLARE ListTransfers CURSOR FOR SELECT REQ_TRANSFER_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, AUTH_STATUS, NOTES, FR_DEP_ID, TO_DEP_ID, FR_DVDM_ID, TO_DVDM_ID, FR_KHOI_ID, TO_KHOI_ID, FR_GD_TYPE_ID, TO_GD_TYPE_ID, FR_AMT_APP, FR_AMT_TF, FR_AMT_RECEIVE_TF, FR_AMT_EXE, FR_AMT_ETM, FR_AMT_ETM_TMP FROM @TABLE OPEN ListTransfers FETCH NEXT FROM ListTransfers INTO @REQ_TRANSFER_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,@AUTH_STATUS, @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,@FR_AMT_APP,@FR_AMT_TF,@FR_AMT_RECEIVE_TF,@FR_AMT_EXE,@FR_AMT_ETM,@FR_AMT_ETM_TMP WHILE @@FETCH_STATUS = 0 BEGIN --- KIEM TRA NGAN SACH DIEU CHUYEN TAI DVDC PHAI BAT BUOC NHAP IF(@FR_GOOD_ID IS NULL OR @FR_GOOD_ID ='') BEGIN ROLLBACK TRANSACTION SELECT '-1' Result, '' REQ_ID, N'Lưới điều chuyển ngân sách: Bạn là đơn vị điều chuyển ngân sách. Vui lòng chọn hạng mục ngân sách chuyển!' ErrorDesc RETURN '-1' END ---- DECLARE @FR_GD_TYPE_NAME NVARCHAR(100),@TO_GD_TYPE_NAME NVARCHAR(100) IF(@FR_GOOD_ID IS NOT NULL AND @FR_GOOD_ID<>'') IF(NOT EXISTS(SELECT * FROM dbo.CM_GOODSTYPE WHERE GD_TYPE_ID= @FR_GD_TYPE AND @TO_GD_TYPE IN (SELECT value FROM dbo.wsiSplit(NOTES,';')) ) ) BEGIN SET @FR_GD_TYPE_NAME=(SELECT GD_TYPE_NAME FROM dbo.CM_GOODSTYPE WHERE GD_TYPE_ID=@FR_GD_TYPE) SET @TO_GD_TYPE_NAME=(SELECT GD_TYPE_NAME FROM dbo.CM_GOODSTYPE WHERE GD_TYPE_ID=@TO_GD_TYPE) ROLLBACK TRANSACTION SELECT '-1' AS Result , '' REQ_ID, N'Không được điều chuyển từ ' + ISNULL(@FR_GD_TYPE_NAME,'') +N' sang ' + ISNULL(@TO_GD_TYPE_NAME,'') ErrorDesc RETURN '-1' CLOSE ListTransfers DEALLOCATE ListTransfers END --- KIEM TRA HAN MUC CO HOP LE HAY KHONG IF(EXISTS(SELECT Temp.FR_TRADE_ID FROM ( SELECT TB.FR_TRADE_ID ,SUM(TB.TOTAL_AMT) AS TOTAL_AMT_EXE, SUM(ROUND(ISNULL(PT.AMT_APP,0),0) + ROUND(ISNULL(PT.AMT_RECEIVE_TF,0),0) - ROUND(ISNULL(PT.AMT_TF,0),0) - ROUND(ISNULL(PT.AMT_ETM,0),0))/COUNT(TB.FR_TRADE_ID) - (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0) FROM dbo.PL_REQUEST_DOC_DT DDT LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_ID = DOC.REQ_ID WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT') AND doc.REQ_ID <> @p_REQ_ID AND DDT.TRADE_ID = TB.FR_TRADE_ID) - (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0) FROM dbo.PL_REQUEST_TRANSFER DDT LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT') AND doc.REQ_ID <> @p_REQ_ID AND DDT.FR_TRADE_ID = TB.FR_TRADE_ID) AS TOTAL_AMT_REMAIN FROM @TABLE TB LEFT JOIN dbo.PL_TRADEDETAIL PT ON PT.TRADE_ID=TB.FR_TRADE_ID GROUP BY TB.FR_TRADE_ID )Temp WHERE (Temp.TOTAL_AMT_EXE) > Temp.TOTAL_AMT_REMAIN)) BEGIN ROLLBACK TRANSACTION SELECT '-1' Result, '' REQ_ID, N'Số tiền điều chuyển lớn hơn số tiền dự kiến còn lại của hạng mục ngân sách' ErrorDesc RETURN '-1' END ---- IF(EXISTS(SELECT REQ_TRANSFER_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_TRANSFER_ID=@REQ_TRANSFER_ID AND AUTH_STATUS <> 'A' AND (FR_BRN_ID=@BRANCH_ID AND (FR_DEP_ID IS NULL OR FR_DEP_ID='' OR (FR_DEP_ID=@DEP_ID OR FR_DEP_ID IN (SELECT FATHER_ID FROM CM_DEPARTMENT WHERE DEP_ID =@DEP_ID)))))) --- LUCTV 26-04-2023 FIX BỔ SUNG KIỂM TRA THÊM PHÒNG BAN CON VẪN CHỌN NS PHÒNG BAN CHA & GỬI DUYỆT ĐƯỢC 26042023_secretkey BEGIN UPDATE dbo.PL_REQUEST_TRANSFER SET FR_GOOD_ID=@FR_GOOD_ID,FR_PLAN_ID=@FR_PLAN_ID,FR_TRADE_ID=@FR_TRADE_ID, NOTES=@NOTES,TOTAL_AMT=@TOTAL_AMT ,FR_DVDM_ID=@FR_DVDM_ID,FR_KHOI_ID=@FR_KHOI_ID, FR_AMT_APP = @FR_AMT_APP, FR_AMT_TF = @FR_AMT_TF, FR_AMT_RECEIVE_TF = @FR_AMT_RECEIVE_TF, FR_AMT_EXE = @FR_AMT_EXE, FR_AMT_ETM = @FR_AMT_ETM, FR_AMT_ETM_TMP = @FR_AMT_ETM_TMP WHERE REQ_TRANSFER_ID=@REQ_TRANSFER_ID UPDATE prdd SET prdd.FR_AMT_APP = ISNULL(PL.AMT_APP,0), prdd.FR_AMT_EXE = ISNULL(PL.AMT_EXE,0), prdd.FR_AMT_ETM = ISNULL(PL.AMT_ETM,0), prdd.FR_AMT_TF = ISNULL(PL.AMT_TF,0), prdd.FR_AMT_RECEIVE_TF = ISNULL(PL.AMT_RECEIVE_TF,0), prdd.FR_AMT_ETM_TMP = (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0) FROM dbo.PL_REQUEST_DOC_DT DDT LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_ID = DOC.REQ_ID WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT') AND DDT.TRADE_ID = PL.TRADE_ID AND DOC.REQ_ID <> prdd.REQ_DOC_ID) + (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0) FROM dbo.PL_REQUEST_TRANSFER DDT LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT') AND DDT.FR_TRADE_ID = PL.TRADE_ID AND DOC.REQ_ID <> prdd.REQ_DOC_ID) FROM PL_REQUEST_TRANSFER prdd LEFT JOIN PL_TRADEDETAIL PL ON PL.TRADE_ID = prdd.FR_TRADE_ID WHERE prdd.REQ_TRANSFER_ID=@REQ_TRANSFER_ID END IF @@ERROR <> 0 GOTO ABORT1 -- next Group_Id FETCH NEXT FROM ListTransfers INTO @REQ_TRANSFER_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,@AUTH_STATUS, @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,@FR_AMT_APP,@FR_AMT_TF,@FR_AMT_RECEIVE_TF,@FR_AMT_EXE,@FR_AMT_ETM,@FR_AMT_ETM_TMP END CLOSE ListTransfers DEALLOCATE ListTransfers IF @@Error <> 0 GOTO ABORT END END 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 ListCostCenters DEALLOCATE ListCostCenters CLOSE ListTransfers DEALLOCATE ListTransfers ROLLBACK TRANSACTION SELECT '-1' AS Result ,'' REQ_ID, '' ErrorDesc RETURN '-1' End