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

USE [gAMSPro_VietcapitalBank_v2_TEST]

GO

/****** Object:  StoredProcedure [dbo].[TR_REQ_PROCESS_CHILD_App]    Script Date: 24-Aug-20 09:05:46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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(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)

				IF(@TOTAL_AMT_REQ >5000000)

				BEGIN

				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 

				    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' ,

						1       -- DVDM_ID - varchar(15)

				     )

					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)

					    0       -- IS_HAS_CHILD - bit

					    )

					SET @PROCESS_PARENT= 'GDK_PYC'				

                END

				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)

				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