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

ALTER PROCEDURE [dbo].[PL_REQ_PROCESS_CHILD_Ins]

@p_MAKER_ID VARCHAR(20),

@p_XMLData XML

AS

BEGIN TRANSACTION

		--- KHAI BAO CHUOI ID TRA VÊ PHUC VU VIEC GUI MAI;

		DECLARE @ID_RETURN_FOR_EMAIL VARCHAR(500) =''

		-------------------------------------------------

		Declare @hdoc INT

		Exec sp_xml_preparedocument @hdoc Output,@p_XMLData

		DECLARE ListREQ  CURSOR FOR

		SELECT *

		FROM OPENXML(@hDoc,'/Root/ListREQ',2)

		WITH 

		(

			REQ_ID	varchar(20)  ,

			PROCESS_ID	varchar(20),

			TLNAME VARCHAR(20),

			TYPE_JOB VARCHAR(20),

			REF_ID INT

		)

		OPEN ListREQ

DECLARE @LEVEL INT,@REQ_ID VARCHAR(20),@PROCESS_ID VARCHAR(20),@TYPE_JOB VARCHAR(20),@TLNAME VARCHAR(20),@DEP_ID VARCHAR(20),@BRANCH_ID VARCHAR(20)

DECLARE @REF_ID INT, @TYPE_JOB_NAME NVARCHAR(100), @FULLNAME NVARCHAR(100)

SET @BRANCH_ID=(SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)

SET @DEP_ID=(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)

DECLARE @lstCOST TABLE(

 COST_ID VARCHAR(20)

)

INSERT INTO @lstCOST

SELECT COST_ID FROM dbo.PL_COSTCENTER_DT WHERE BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID

FETCH NEXT FROM ListREQ INTO @REQ_ID,@PROCESS_ID,@TLNAME,@TYPE_JOB,@REF_ID

WHILE @@FETCH_STATUS=0

BEGIN

	IF(@TLNAME IS NULL OR @TLNAME='')

	BEGIN

			SELECT -1 Result, N'Vui lòng chọn người được giao xử lý phiếu số' ErrorDesc 

			ROLLBACK TRANSACTION

			RETURN '-1'

	END

	IF(@TYPE_JOB IS NULL OR @TYPE_JOB='')

	BEGIN

			SELECT -1 Result, N'Vui lòng chọn vai trò của người được giao xử lý phiếu' ErrorDesc 

			ROLLBACK TRANSACTION

			RETURN '-1'

	END

	IF(@TYPE_JOB ='TP')

	BEGIN

			SELECT -1 Result, N'Vui lòng kéo thanh cuộn sang phải để chọn vai trò của người được giao xử lý phiếu. Chọn vai trò kiểm soát nếu nhân viên đóng vai trò phê duyệt kí nháy. Chọn vai trò xử lý phiếu nếu nhân viên trực tiếp xử lý tờ trình' ErrorDesc 

			ROLLBACK TRANSACTION

			RETURN '-1'

	END

		SET @LEVEL=ISNULL((SELECT TOP 1 LEVEL_JOB 

			FROM dbo.PL_REQUEST_PROCESS_CHILD 

			WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID 

			ORDER BY LEVEL_JOB DESC),0)

		-- BAT DAU GAN GIA TRI

		SET @ID_RETURN_FOR_EMAIL = @ID_RETURN_FOR_EMAIL +','+@REQ_ID

		-----------------------------------------------------------

		IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB=@TYPE_JOB ))

		BEGIN

			IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB=@TYPE_JOB AND TLNAME <> @TLNAME ))

				BEGIN

					UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET TLNAME=@TLNAME WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB=@TYPE_JOB

					SET @TYPE_JOB_NAME =(SELECT TOP 1 CONTENT FROM dbo.CM_ALLCODE WHERE CDNAME = 'JOB_TYPE' AND CDTYPE='REQ')

					SET @FULLNAME =(SELECT TOP 1 TLFullName FROM dbo.TL_USER WHERE TLNANME=@TLNAME)

					INSERT INTO dbo.PL_PROCESS

					(

					    REQ_ID,

					    PROCESS_ID,

					    CHECKER_ID,

					    APPROVE_DT,

					    PROCESS_DESC,

					    NOTES

					)

					VALUES

					(   @REQ_ID,        -- REQ_ID - varchar(15)

					    @PROCESS_ID,        -- PROCESS_ID - varchar(10)

					    @p_MAKER_ID,        -- CHECKER_ID - varchar(15)

					    GETDATE(), -- APPROVE_DT - datetime

					    N'Nhân viên tiếp nhận ' + @FULLNAME,       -- PROCESS_DESC - nvarchar(1000)

					    N'Điều phối '+@TYPE_JOB_NAME        -- NOTES - nvarchar(1000)

					    )

				END

		END

		ELSE

		BEGIN

		IF(@LEVEL=0)

		BEGIN

			SET @LEVEL=@LEVEL +1

				INSERT INTO dbo.PL_REQUEST_PROCESS_CHILD

			(

				REQ_ID,

				PROCESS_ID,

				TLNAME,

				TYPE_JOB,

				LEVEL_JOB,

				STATUS_JOB,

				RECORD_STATUS

			)

			VALUES

			(   @REQ_ID, -- REQ_ID - varchar(20)

				@REF_ID, -- PROCESS_ID - varchar(20)

				@p_MAKER_ID, -- TLNAME - varchar(50)

				'TP', -- TYPE_JOB - varchar(50)

				@LEVEL,  -- LEVEL_JOB - int

				'U', -- STATUS_JOB - varchar(10)

				'1'  -- RECORD_STATUS - varchar(15)

			)

		END

		IF(@TYPE_JOB='KS' AND EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB='XL'))

		BEGIN

			UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET LEVEL_JOB=@LEVEL + 1  WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID  AND TYPE_JOB='XL'

			SET @LEVEL=@LEVEL-1

			UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='U' WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND LEVEL_JOB=@LEVEL

			IF (EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB='XL' AND STATUS_JOB='C'))

			BEGIN

			INSERT INTO dbo.PL_REQUEST_PROCESS_CHILD

			(

				REQ_ID,

				PROCESS_ID,

				TLNAME,

				TYPE_JOB,

				LEVEL_JOB,

				STATUS_JOB,

				RECORD_STATUS

			)

			VALUES

			(   @REQ_ID, -- REQ_ID - varchar(20)

				@REF_ID, -- PROCESS_ID - varchar(20)

				@TLNAME, -- TLNAME - varchar(50)

				@TYPE_JOB, -- TYPE_JOB - varchar(50)

				@LEVEL + 1,  -- LEVEL_JOB - int

				'U', -- STATUS_JOB - varchar(10)

				'1'  -- RECORD_STATUS - varchar(15)

				)

			END

			ELSE

			BEGIN

				INSERT INTO dbo.PL_REQUEST_PROCESS_CHILD

				(

					REQ_ID,

					PROCESS_ID,

					TLNAME,

					TYPE_JOB,

					LEVEL_JOB,

					STATUS_JOB,

					RECORD_STATUS

				)

				VALUES

				(   @REQ_ID, -- REQ_ID - varchar(20)

					@REF_ID, -- PROCESS_ID - varchar(20)

					@TLNAME, -- TLNAME - varchar(50)

					@TYPE_JOB, -- TYPE_JOB - varchar(50)

					@LEVEL + 1,  -- LEVEL_JOB - int

					'U', -- STATUS_JOB - varchar(10)

					'1'  -- RECORD_STATUS - varchar(15)

					)

			END

		END

		ELSE

		BEGIN

		UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='U' WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND LEVEL_JOB=@LEVEL

		INSERT INTO dbo.PL_REQUEST_PROCESS_CHILD

		(

		    REQ_ID,

		    PROCESS_ID,

		    TLNAME,

		    TYPE_JOB,

		    LEVEL_JOB,

		    STATUS_JOB,

		    RECORD_STATUS

		)

		VALUES

		(   @REQ_ID, -- REQ_ID - varchar(20)

		    @REF_ID, -- PROCESS_ID - varchar(20)

		    @TLNAME, -- TLNAME - varchar(50)

		    @TYPE_JOB, -- TYPE_JOB - varchar(50)

		    @LEVEL + 1,  -- LEVEL_JOB - int

		    'C', -- STATUS_JOB - varchar(10)

		    '1'  -- RECORD_STATUS - varchar(15)

		    )

		END

		SET @TYPE_JOB_NAME =(SELECT TOP 1 CONTENT FROM dbo.CM_ALLCODE WHERE CDNAME = 'JOB_TYPE' AND CDTYPE='REQ' AND CDVAL=@TYPE_JOB)

		SET @FULLNAME =(SELECT  TOP 1 TLFullName FROM dbo.TL_USER WHERE TLNANME=@TLNAME)

		INSERT INTO dbo.PL_PROCESS

					(

					    REQ_ID,

					    PROCESS_ID,

					    CHECKER_ID,

					    APPROVE_DT,

					    PROCESS_DESC,

					    NOTES

					)

					VALUES

					(   @REQ_ID,        -- REQ_ID - varchar(15)

					    @PROCESS_ID,        -- PROCESS_ID - varchar(10)

					    @p_MAKER_ID,        -- CHECKER_ID - varchar(15)

					    GETDATE(), -- APPROVE_DT - datetime

					    N'Nhân viên tiếp nhận ' + @FULLNAME,       -- PROCESS_DESC - nvarchar(1000)

					    N'Điều phối '+@TYPE_JOB_NAME        -- NOTES - nvarchar(1000)

					    )

		END

		FETCH NEXT FROM ListREQ INTO @REQ_ID,@PROCESS_ID,@TLNAME,@TYPE_JOB,@REF_ID

END

CLOSE ListREQ

DEALLOCATE ListREQ

		IF @@Error <> 0 GOTO ABORT

COMMIT TRANSACTION

SELECT 0 as Result, @ID_RETURN_FOR_EMAIL AS ErrorDesc

RETURN 0

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT -1 as Result, '' ErrorDesc

		RETURN -1

End