PHE DUYET PYCMS GO KE TOAN KHOI DVCM.txt

Luc Tran Van, 10/13/2020 10:14 AM

       ??
       ALTER PROCEDURE [dbo].[TR_REQ_PROCESS_CHILD_App]
       @p_REQ_ID VARCHAR(20),
       @p_PROCESS_ID VARCHAR(20),
       @p_TLNAME VARCHAR(20),
       @p_MAKER_ID VARCHAR(20),
       @p_TYPE_JOB VARCHAR(20),
       @p_PROCESS_DES NVARCHAR(500),
       @p_REF_ID INT,
       @p_XMLDATA XML
       AS
       BEGIN TRANSACTION
       -- LUCTV 20052020 BO SUNG CHECK NEU TRA VE THI KHONG DUYET DUOC
       IF(NOT EXISTS(SELECT *FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C' AND PROCESS_ID=@p_PROCESS_ID))
       BEGIN
       SELECT 1 as Result, '' ErrorDesc
       RETURN 0
       END
       IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_ID AND STATUS_JOB ='R') OR (EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND AUTH_STATUS ='R'))
       OR(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='R')))
       BEGIN
       ROLLBACK TRANSACTION
       SELECT -1 Result, N'Phi?u y?u c?u mua s?m s?: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' ang b? t? ch?i. Vui l?ng ?i nh?n vi?n x? l? phi?u v? g?i ph? duy?t l?i!' ErrorDesc
       RETURN -1
       END
       Declare @hdoc INT
       EXEC sp_xml_preparedocument @hdoc Output,@p_XMLDATA
       DECLARE @lstFILE TABLE(
       ATTACH_ID VARCHAR(20),
       IS_READ BIT
+      )
       INSERT INTO @lstFILE
       SELECT *
       FROM OPENXML(@hDoc,'/Root/ATTACH_FILE',2)
       WITH
+      (
       ATTACH_ID VARCHAR(20),
       IS_READ BIT
+      )
       IF(EXISTS(SELECT TR_REQUEST_DOC_FILE_ID FROM dbo.TR_REQUEST_DOC_FILE WHERE REQ_ID=@p_REQ_ID AND IS_VIEW=1
       AND EXISTS(SELECT ATTACH_ID FROM @lstFILE WHERE [@lstFILE].ATTACH_ID=TR_REQUEST_DOC_FILE.ATTACH_ID AND IS_READ=0)))
       BEGIN
       ROLLBACK TRANSACTION
       SELECT -1 Result, N'File inh k?m b?t bu?c ?c' ErrorDesc
       RETURN 0
       END
       DECLARE @LEVEL INT, @TYPE_JOB_NAME NVARCHAR(100), @FULLNAME NVARCHAR(100)
       UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='P'
       WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@p_REF_ID AND TLNAME=@p_TLNAME AND TYPE_JOB=@p_TYPE_JOB
       SET @LEVEL=ISNULL((SELECT TOP 1 LEVEL_JOB
       FROM dbo.PL_REQUEST_PROCESS_CHILD
       WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@p_REF_ID AND TLNAME=@p_TLNAME AND TYPE_JOB=@p_TYPE_JOB
       ORDER BY LEVEL_JOB DESC),0)
       UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='C'
       WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@p_REF_ID AND LEVEL_JOB=(@LEVEL-1)
       SET @TYPE_JOB_NAME =(SELECT CONTENT FROM dbo.CM_ALLCODE WHERE CDNAME = 'JOB_TYPE' AND CDTYPE='REQ' AND CDVAL=@p_TYPE_JOB)
       SET @FULLNAME =(SELECT TLFullName FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME )
       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)
       @p_MAKER_ID, -- CHECKER_ID - varchar(15)
       GETDATE() , -- APPROVE_DT - datetime
       @p_PROCESS_DES ,
       @TYPE_JOB_NAME+ N' ? ph? duy?t' -- PROCESS_DESC - nvarchar(1000)
+      )
       IF(NOT EXISTS (SELECT * FROM dbo.PL_REQUEST_PROCESS_CHILD
       WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@p_REF_ID AND STATUS_JOB <> 'P'))
       BEGIN
       IF(@p_PROCESS_ID='DMMS')
       BEGIN
       DECLARE @PROCESS_PARENT VARCHAR(20)
       SET @PROCESS_PARENT='DMMS'
       UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='P',CHECKER_ID=@p_MAKER_ID,APPROVE_DT=GETDATE(),NOTES=N'?u m?i mua s?m ? ph? duy?t' WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@p_PROCESS_ID
       DECLARE @LIMIT_VALUE_KT DECIMAL(18,0),@ROLE_KT VARCHAR(20),@DVDM_KT VARCHAR(20),@NOTES_KT NVARCHAR(200),@TOTAL_AMT_REQ DECIMAL(18,0)
       SET @LIMIT_VALUE_KT = (SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='KT')
       SET @ROLE_KT=(SELECT ROLE_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='KT')
       SET @DVDM_KT=(SELECT DVDM_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='KT')
       SET @TOTAL_AMT_REQ =(SELECT SUM(TOTAL_AMT) AS TOTAL_AMT FROM dbo.TR_REQUEST_DOC_DT
       WHERE REQ_DOC_ID=@p_REQ_ID AND TRAN_TYPE_ID NOT IN (SELECT TRN_TYPE FROM dbo.CM_TRAN_TYPE WHERE NOTES='XK' OR NOTES ='CDT'))
       IF(@TOTAL_AMT_REQ > @LIMIT_VALUE_KT)
       BEGIN
       DECLARE @l_REQ_COST_ID VARCHAR(15)
       EXEC SYS_CodeMasters_Gen 'TR_REQUEST_COSTCENTER', @l_REQ_COST_ID out
       INSERT dbo.TR_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)
       @DVDM_KT, -- COST_ID - varchar(15)
       @p_REQ_ID, -- REQ_ID - varchar(15)
       N'', -- NOTES - nvarchar(500)
       'U', -- AUTH_STATUS - varchar(1)
       @p_MAKER_ID, -- MAKER_ID - varchar(15)
       GETDATE(), -- CREATE_DT - datetime
       '', -- CHECKER_ID - varchar(15)
       NULL -- APPROVE_DT - datetime
+      )
       END
       IF(EXISTS(SELECT * FROM dbo.TR_REQUEST_COSTCENTER WHERE REQ_ID=@p_REQ_ID))
       BEGIN
       DECLARE @COST_ID VARCHAR(20)
       DECLARE lstCostCenter CURSOR FOR
       SELECT COST_ID FROM dbo.TR_REQUEST_COSTCENTER
       WHERE REQ_ID=@p_REQ_ID
       OPEN lstCostCenter
       FETCH NEXT FROM lstCostCenter INTO @COST_ID
       WHILE @@FETCH_STATUS = 0
       BEGIN
       IF(@COST_ID=@DVDM_KT)
       BEGIN
       INSERT INTO dbo.PL_REQUEST_PROCESS
+      (
       REQ_ID,
       PROCESS_ID,
       STATUS,
       ROLE_USER,
       BRANCH_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)
       'DVCM', -- PROCESS_ID - varchar(10)
       'U', -- STATUS - varchar(5)
       'KSV', -- ROLE_USER - varchar(50)
       '', -- BRANCH_ID - varchar(15)
       '', -- CHECKER_ID - varchar(15)
       NULL, -- APPROVE_DT - datetime
       @PROCESS_PARENT, -- PARENT_PROCESS_ID - varchar(10)
       'N', -- IS_LEAF - varchar(1)
       '', -- COST_ID - varchar(15)
       @COST_ID ,
       N'Ch? ?n v? chuy?n m?n x?c nh?n' ,
 -- DVDM_ID - varchar(15)
+      )
       END
       ELSE
       BEGIN
       INSERT INTO dbo.PL_REQUEST_PROCESS
+      (
       REQ_ID,
       PROCESS_ID,
       STATUS,
       ROLE_USER,
       BRANCH_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)
       'DVCM', -- PROCESS_ID - varchar(10)
       'U', -- STATUS - varchar(5)
       'GDDV', -- ROLE_USER - varchar(50)
       '', -- BRANCH_ID - varchar(15)
       '', -- CHECKER_ID - varchar(15)
       NULL, -- APPROVE_DT - datetime
       @PROCESS_PARENT, -- PARENT_PROCESS_ID - varchar(10)
       'N', -- IS_LEAF - varchar(1)
       '', -- COST_ID - varchar(15)
       @COST_ID ,
       N'Ch? ?n v? chuy?n m?n x?c nh?n' ,
 -- DVDM_ID - varchar(15)
+      )
       END
       FETCH NEXT FROM lstCostCenter INTO @COST_ID
       END
       CLOSE lstCostCenter
       DEALLOCATE lstCostCenter
       END
       DECLARE @TOTAL_AMT DECIMAL(18,2),@ROLE_PDTH VARCHAR(20) ,@LIMIT_VALUE DECIMAL(18,2) ,@IS_NEXT BIT,@PROCESS_ID VARCHAR(5),@ROLE_ID VARCHAR(20),@DVDM_ID VARCHAR(20),@NOTES NVARCHAR(50),@DVDM_NAME NVARCHAR(200)
       IF(EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DVCM'))
       SET @PROCESS_PARENT='DVCM'
       SET @IS_NEXT=(SELECT dbo.FN_CHECK_LIMIT_TR_REQ(@p_REQ_ID,'GDDV'))
       --SET @IS_NEXT=1
       IF(@IS_NEXT =1)
       BEGIN
       SET @DVDM_ID=(SELECT DVDM_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='PDPYC')
       SET @ROLE_ID=(SELECT ROLE_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='PDPYC')
       SET @NOTES =(SELECT ROLE_DESC FROM dbo.TL_SYSROLE WHERE ROLE_ID=@ROLE_ID)
       SET @DVDM_NAME= (SELECT DVDM_NAME FROM dbo.CM_DVDM WHERE DVDM_ID=@DVDM_ID)
       SET @NOTES = @NOTES + ' ' + @DVDM_NAME
       INSERT INTO dbo.PL_REQUEST_PROCESS
+      (
       REQ_ID,
       PROCESS_ID,
       STATUS,
       ROLE_USER,
       BRANCH_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)
       'GDK_PYC', -- PROCESS_ID - varchar(10)
       'U', -- STATUS - varchar(5)
       @ROLE_ID, -- ROLE_USER - varchar(50)
       '', -- BRANCH_ID - varchar(15)
       '', -- CHECKER_ID - varchar(15)
       NULL, -- APPROVE_DT - datetime
       @PROCESS_PARENT, -- PARENT_PROCESS_ID - varchar(10)
       '', -- IS_LEAF - varchar(1)
       '', -- COST_ID - varchar(15)
       @DVDM_ID, -- DVDM_ID - varchar(15)
       N'Ch? ' +@NOTES+N' ph? duy?t', -- NOTES - nvarchar(500)
 -- IS_HAS_CHILD - bit
+      )
       SET @PROCESS_PARENT= 'GDK_PYC'
       END
       INSERT INTO dbo.PL_REQUEST_PROCESS
+      (
       REQ_ID,
       PROCESS_ID,
       STATUS,
       ROLE_USER,
       BRANCH_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)
       'APPROVE', -- PROCESS_ID - varchar(10)
       'U', -- STATUS - varchar(5)
       '', -- ROLE_USER - varchar(50)
       '', -- BRANCH_ID - varchar(15)
       '', -- CHECKER_ID - varchar(15)
       NULL, -- APPROVE_DT - datetime
       @PROCESS_PARENT, -- PARENT_PROCESS_ID - varchar(10)
       'N', -- IS_LEAF - varchar(1)
       '', -- COST_ID - varchar(15)
       '', -- DVDM_ID - varchar(15)
       N'Ho?n t?t', -- NOTES - nvarchar(500)
       NULL -- IS_HAS_CHILD - bit
+      )
       DECLARE @PROCESS_NEXT_ID VARCHAR(10)
       SET @PROCESS_NEXT_ID = (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@p_PROCESS_ID)
       UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='C' WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@p_PROCESS_ID
       UPDATE dbo.TR_REQUEST_DOC SET PROCESS_ID=@PROCESS_NEXT_ID WHERE REQ_ID=@p_REQ_ID
       END
       ELSE IF(@p_PROCESS_ID='DVCM')
       BEGIN
       DECLARE @DEP_ID VARCHAR(15),@BRANCH_ID VARCHAR(15),@PROCESS_NEXT VARCHAR(10)
       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)
       -- LUCTV 07082020 KIEM TRA NEU ROLE KHAC ROLE GDDV THI PHAI CHUYEN ROLE THANH GDDV
       --IF(@ROLE_ID ='KTT')
       --BEGIN
       -- SET @ROLE_ID ='GDDV'
       --END
       SET @PROCESS_NEXT = (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@p_PROCESS_ID)
       UPDATE dbo.TR_REQUEST_COSTCENTER SET AUTH_STATUS='A',APPROVE_DT=GETDATE(),CHECKER_ID=@p_MAKER_ID WHERE REQ_ID=@p_REQ_ID AND COST_ID IN (SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC LEFT JOIN
       dbo.PL_COSTCENTER_DT PT ON PT.COST_ID=PC.COST_ID WHERE BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID)
       UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='P',CHECKER_ID=@p_MAKER_ID,
       APPROVE_DT=GETDATE() ,NOTES=N'?n v? chuy?n m?n x?c nh?n'
       WHERE REQ_ID=@p_REQ_ID AND( ROLE_USER=@ROLE_ID OR ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@ROLE_ID))AND DVDM_ID IN (SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC LEFT JOIN
       dbo.PL_COSTCENTER_DT PT ON PT.COST_ID=PC.COST_ID
       WHERE BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID)
       IF(NOT EXISTS( SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@p_PROCESS_ID 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.TR_REQUEST_DOC SET PROCESS_ID=@PROCESS_NEXT WHERE REQ_ID=@p_REQ_ID
       END
       END
       --IF(@PROCESS_NEXT='APPROVE')
       IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE PROCESS_ID ='APPROVE' AND REQ_ID =@p_REQ_ID))
       BEGIN
       DECLARE @TempTB TABLE
+      (
       TOTAL_AMT DECIMAL(18,2),
       TRADE_ID VARCHAR(20),
       PLAN_ID VARCHAR(20)
+      )
       INSERT INTO @TempTB
       SELECT SUM(DT.TOTAL_AMT) TOTAL_AMT,PLDT.TRADE_ID,PLDT.PLAN_ID FROM dbo.TR_REQUEST_DOC_DT DT
       LEFT JOIN dbo.PL_REQUEST_DOC_DT PLDT ON DT.PL_REQDT_ID=PLDT.REQDT_ID
       WHERE TRAN_TYPE_ID IN (SELECT TRAN_TYPE_ID FROM dbo.CM_TRAN_TYPE WHERE NOTES='XK') AND DT.REQ_DOC_ID=@p_REQ_ID
       GROUP BY PLDT.TRADE_ID,PLDT.PLAN_ID
       UPDATE dbo.PL_TRADEDETAIL SET AMT_EXE =AMT_EXE + (SELECT TOTAL_AMT FROM @TempTB WHERE [@TempTB].TRADE_ID=PL_TRADEDETAIL.TRADE_ID AND PL_TRADEDETAIL.PLAN_ID=[@TempTB].PLAN_ID) WHERE PL_TRADEDETAIL.TRADE_ID IN (SELECT TRADE_ID FROM @TempTB)
       IF(EXISTS(SELECT * FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID AND TRAN_TYPE_ID NOT IN (SELECT TRN_TYPE FROM dbo.CM_TRAN_TYPE WHERE NOTES='XK')))
       EXEC dbo.TR_REQ_DOC_Ins_To_PO @p_TR_REQ_ID = @p_REQ_ID -- varchar(15)
       END
       END
       IF @@Error <> 0 GOTO ABORT
       COMMIT TRANSACTION
       SELECT 0 as Result, '' ErrorDesc
       RETURN 0
       ABORT:
       BEGIN
       ROLLBACK TRANSACTION
       SELECT -1 as Result, '' 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

PHE DUYET PYCMS GO KE TOAN KHOI DVCM.txt