TR_SENDAPP.txt

Luc Tran Van, 12/09/2020 11:07 AM

       ??CREATE PROCEDURE [dbo].[TR_REQUEST_DOC_SendApp]
       @p_REQ_ID VARCHAR(20),
       @p_PROCESS_ID VARCHAR(20),
       @p_TLNAME VARCHAR(20),
       @p_MAKER_ID VARCHAR(20)
       AS
       BEGIN TRANSACTION
       IF(EXISTS(SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND (REQ_DT IS NULL OR REQ_DT='')) )
       BEGIN
       ROLLBACK TRANSACTION
       SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Ng?y y?u c?u b?t bu?c nh?p' ErrorDesc
       RETURN '-1'
       END
       IF(EXISTS(SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND (REQ_REASON IS NULL OR REQ_REASON='')) )
       BEGIN
       ROLLBACK TRANSACTION
       SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'M?c ?ch y?u c?u b?t bu?c nh?p' ErrorDesc
       RETURN '-1'
       END
       IF(EXISTS(SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND (PL_REQ_ID IS NULL OR PL_REQ_ID='')) )
       BEGIN
       ROLLBACK TRANSACTION
       SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'T? tr?nh ch? tr??ng b?t bu?c nh?p' ErrorDesc
       RETURN '-1'
       END
       IF(NOT EXISTS(SELECT REQ_DOC_ID FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID ) )
       BEGIN
       ROLLBACK TRANSACTION
       SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Chi ti?t h?ng h?a b?t bu?c nh?p' ErrorDesc
       RETURN '-1'
       END
       --- XOA DATA CU
       DELETE PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID
       -----
       DECLARE @NS_TO_TRINH DECIMAL(18,0), @NS_PYCMS_USE DECIMAL(18,0), @NS_LUY_KE DECIMAL(18,0),@PL_REQ_CODE VARCHAR(50)
       SET @PL_REQ_CODE =(SELECT REQ_CODE FROM PL_REQUEST_DOC WHERE REQ_ID =(SELECT PL_REQ_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID))
       SET @NS_TO_TRINH =(SELECT TOTAL_AMT FROM PL_REQUEST_DOC WHERE REQ_ID =(SELECT PL_REQ_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID))
       SET @NS_PYCMS_USE =(SELECT SUM(TOTAL_AMT) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_REQ_ID)
       SET @NS_LUY_KE =ISNULL((SELECT SUM(TOTAL_AMT) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID IN (SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE PL_REQ_ID=(SELECT PL_REQ_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID) AND PROCESS_ID='APPROVE')),0)
       DECLARE @p_PL_REQ_ID VARCHAR(15),@p_REQ_DATE DATETIME
       SELECT @p_PL_REQ_ID=PL_REQ_ID,@p_REQ_DATE=REQ_DT FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID
       IF( EXISTS(
       SELECT Temp.GD_ID FROM (SELECT GD_ID,SUM(TOTAL_AMT) AS TOTAL_AMT_ETM FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID
       GROUP BY GD_ID) Temp WHERE TOTAL_AMT_ETM >
       ((SELECT SUM(TEMP2.TOTAL_AMT) AS TOTAL_AMT_APP FROM
       (SELECT GOODS_ID,TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT
       WHERE REQ_ID=@p_PL_REQ_ID
       UNION
       SELECT GOODS_ID,TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT
       WHERE REQ_ID=(SELECT REQ_PARENT_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_PL_REQ_ID) ) TEMP2 WHERE TEMP2.GOODS_ID=Temp.GD_ID
       GROUP BY TEMP2.GOODS_ID) - (SELECT ISNULL(SUM(TOTAL_AMT),0) AS TOTAL_AMT_EXE FROM dbo.TR_REQUEST_DOC_DT WHERE GD_ID=Temp.GD_ID AND REQ_DOC_ID IN (SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE PL_REQ_ID=@p_PL_REQ_ID AND PROCESS_ID='APPROVE')
       GROUP BY GD_ID))))
       IF(1=1)
       BEGIN
       ROLLBACK TRANSACTION
       SELECT 'REQ-00001' Result,'' REQ_CODE, '' REQ_ID,
       N'Th?ng tin li?n quan t?i phi?u y?u c?u mua s?m c? s? d?ng t? tr?nh s?: '+ @PL_REQ_CODE
       +CHAR(10)+ N'T?ng s? ti?n liy k? s? d?ng ng?n s?ch t? tr?nh: '+FORMAT(@NS_LUY_KE ,'#,#', 'vi-VN')
       +CHAR(10)+N'T?ng s? ti?n ng?n s?ch c?n l?i c?a t? tr?nh: '+ FORMAT(@NS_TO_TRINH -@NS_LUY_KE ,'#,#', 'vi-VN')
       +CHAR(10)+ N'S? ti?n s? d?ng ng?n s?ch cho phi?u y?u c?u mua s?m hi?n t?i v??t '+FORMAT(@NS_LUY_KE+@NS_PYCMS_USE -@NS_TO_TRINH ,'#,#', 'vi-VN')+N' so v?i t?ng s? ti?n ng?n s?ch c?n l?i c?a t? tr?nh.'
       +CHAR(10) +N'Vui l?ng tra c?u t?ng s? ti?n c?c phi?u y?u c?u mua s?m ? bi?t th?m th?ng tin chi ti?t!' ErrorDesc
       RETURN '0'
       END
       IF(EXISTS(SELECT REQDT_ID FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID AND REQ_DT <= @p_REQ_DATE))
       BEGIN
       ROLLBACK TRANSACTION
       SELECT 'REQ-00001' Result,'' REQ_CODE, '' REQ_ID, N'L??i th?ng tin h?ng h?a theo t? tr?nh: Ng?y c?n kh?ng ??c ph?p ? tr?ng v? ph?i l?n h?n ng?y y?u c?u mua s?m' ErrorDesc
       RETURN '0'
       END
       DECLARE @BRANCH_ID VARCHAR(20), @DEP_ID VARCHAR(20),@BRANCH_CREATE VARCHAR(20) ,@DEP_CREATE VARCHAR(20),@BRANCH_TYPE VARCHAR(10),
       @BRANCH_CREATE_TYPE VARCHAR(10)
       SELECT @BRANCH_CREATE=BRANCH_CREATE,@DEP_CREATE=DEP_CREATE FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID
       SET @BRANCH_TYPE=(SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_ID)
       SET @BRANCH_CREATE_TYPE=(SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE)
       -- KIEM TRA XEM CO CAP PHE DUYET HAY KHONG
       IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND SIGN_USER IS NOT NULL AND SIGN_USER <>''))
       BEGIN
       UPDATE dbo.TR_REQUEST_DOC SET AUTH_STATUS='U',PROCESS_ID='SIGN' WHERE REQ_ID=@p_REQ_ID
       INSERT INTO dbo.PL_REQUEST_PROCESS
+      (
       REQ_ID,
       PROCESS_ID,
       STATUS,
       ROLE_USER,
       BRANCH_ID,
       DEP_ID,
       CHECKER_ID,
       APPROVE_DT,
       PARENT_PROCESS_ID,
       IS_LEAF,
       COST_ID,
       DVDM_ID,
       NOTES,
       IS_HAS_CHILD
+      )
       VALUES
       ( @p_REQ_ID, -- REQ_ID - varchar(15)
       'SIGN', -- PROCESS_ID - varchar(10)
       'C', -- STATUS - varchar(5)
       (SELECT RoleName FROM TL_USER WHERE TLNANME =(SELECT SIGN_USER FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)), -- ROLE_USER - varchar(50)
       @BRANCH_CREATE,
       @DEP_CREATE, -- BRANCH_ID - varchar(15)
       '', -- CHECKER_ID - varchar(15)
       NULL, -- APPROVE_DT - datetime
       '', -- PARENT_PROCESS_ID - varchar(10)
       'N', -- IS_LEAF - varchar(1)
       '', -- COST_ID - varchar(15)
       '', -- DVDM_ID - varchar(15)
       N'Ch? c?p ph? duy?t trung gian x?c nh?n phi?u', -- NOTES - nvarchar(500)
       NULL -- IS_HAS_CHILD - bit
+      )
       END
       ELSE
       BEGIN
       IF(@BRANCH_CREATE_TYPE='PGD')
       SET @BRANCH_CREATE=(SELECT FATHER_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE)
       UPDATE dbo.TR_REQUEST_DOC SET AUTH_STATUS='U',PROCESS_ID='APPNEW' WHERE REQ_ID=@p_REQ_ID
       INSERT INTO dbo.PL_REQUEST_PROCESS
+      (
       REQ_ID,
       PROCESS_ID,
       STATUS,
       ROLE_USER,
       BRANCH_ID,
       DEP_ID,
       CHECKER_ID,
       APPROVE_DT,
       PARENT_PROCESS_ID,
       IS_LEAF,
       COST_ID,
       DVDM_ID,
       NOTES,
       IS_HAS_CHILD
+      )
       VALUES
       ( @p_REQ_ID, -- REQ_ID - varchar(15)
       'APPNEW', -- PROCESS_ID - varchar(10)
       'C', -- STATUS - varchar(5)
       'GDDV', -- ROLE_USER - varchar(50)
       @BRANCH_CREATE,
       @DEP_CREATE, -- BRANCH_ID - varchar(15)
       '', -- CHECKER_ID - varchar(15)
       NULL, -- APPROVE_DT - datetime
       '', -- PARENT_PROCESS_ID - varchar(10)
       'N', -- IS_LEAF - varchar(1)
       '', -- COST_ID - varchar(15)
       '', -- DVDM_ID - varchar(15)
       N'Ch? tr??ng ?n v? ph? duy?t', -- NOTES - nvarchar(500)
       NULL -- IS_HAS_CHILD - bit
+      )
       END
       INSERT INTO dbo.PL_PROCESS
+      (
       REQ_ID,
       PROCESS_ID,
       CHECKER_ID,
       APPROVE_DT,
       PROCESS_DESC,
       NOTES
+      )
       VALUES
       ( @p_REQ_ID, -- REQ_ID - varchar(15)
       --@p_PROCESS_ID, -- PROCESS_ID - varchar(10)
       'SEND',
       @p_TLNAME, -- CHECKER_ID - varchar(15)
       GETDATE(), -- APPROVE_DT - datetime
       N'Nh?n vi?n t?o phi?u v? g?i ph? duy?t th?nh c?ng' , -- PROCESS_DESC - nvarchar(1000)
       N'Nh?n vi?n g?i ph? duy?t ' -- NOTES - nvarchar(1000)
+      )
       IF @@Error <> 0 GOTO ABORT
       COMMIT TRANSACTION
       SELECT '0' as Result,'' REQ_CODE,''REQ_ID, '' ErrorDesc
       RETURN '0'
       ABORT:
       BEGIN
       ROLLBACK TRANSACTION
       SELECT '-1' as Result,'' REQ_CODE,''REQ_ID, '' ErrorDesc
       RETURN '-1'
       End

Project

General

Profile

DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

TR_SENDAPP.txt