USE [gAMSPro_VietcapitalBank_v2]
GO
/****** Object:  StoredProcedure [dbo].[PL_REQUEST_DOC_App]    Script Date: 12-May-20 11:57:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PL_REQUEST_DOC_App]
    @p_REQ_ID VARCHAR(15) = NULL,
    @p_AUTH_STATUS VARCHAR(1) = NULL,
    @p_CHECKER_ID VARCHAR(15) = NULL,
    @p_APPROVE_DT DATETIME = NULL,
    @p_ROLE_LOGIN VARCHAR(50) = NULL,
    @p_BRANCH_LOGIN VARCHAR(15),
    @p_PROCESS_DES NVARCHAR(500)
AS

BEGIN TRANSACTION;
--SET @p_APPROVE_DT = @p_APPROVE_DT 

--Validation is here
DECLARE @ERRORSYS NVARCHAR(15) = '';
IF (NOT EXISTS (SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID))
    SET @ERRORSYS = 'REQ-00002';
IF @ERRORSYS <> ''
BEGIN
    ROLLBACK TRANSACTION;
    SELECT ErrorCode Result,
           ErrorDesc ErrorDesc
    FROM SYS_ERROR
    WHERE ErrorCode = @ERRORSYS;
    RETURN '0';
END;

DECLARE  @ERROR BIT ,@EROOR_DES NVARCHAR(500)

SELECT @ERROR=ERROR,
       @EROOR_DES=ERROR_DES FROM dbo.FN_CHECK_VALIDATE_APP(@p_REQ_ID,'APPNEW','PL_REQUEST_DOC',@p_CHECKER_ID,'APPNEW')
IF(@ERROR=1)
BEGIN
	 ROLLBACK TRANSACTION;
    SELECT '-1'  Result,
           @EROOR_DES ErrorDesc
   
    RETURN '0';
END








--UPDATE dbo.PL_REQUEST_TRANSFER SET AUTH_STATUS = @P_AUTH_STATUS, CHECKER_ID = @P_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@P_APPROVE_DT,103)
--WHERE REQ_DOC_ID = @p_REQ_ID AND FR_BRN_ID IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_BRANCH_LOGIN))

DECLARE @Result VARCHAR(5),
        @TOTAL_TRANSFER DECIMAL(18, 0),
        @TOTAL_AMT DECIMAL(18, 0),
        @ROLE_USER_NOTIFI VARCHAR(50),
        @ROLE_ID VARCHAR(20),
        @ROLE_TF VARCHAR(20),
        @LIMIT_VALUE DECIMAL(18, 0),
        @STEP_CURR VARCHAR(20),
        @STEP_PARENT VARCHAR(20),
        @COST_ID VARCHAR(20),
        @FR_BRANCH_ID VARCHAR(20),
        @FR_DEP_ID VARCHAR(20),
        @DVDM_ID VARCHAR(20),
        @IS_NEXT BIT = 0,
        @TOTAL_AMT_GD DECIMAL(12, 0),
        @STOP BIT,
        @NOTES NVARCHAR(100);
DECLARE @ROLE_CDT VARCHAR(20),
        @DVDM_CDT VARCHAR(20),
        @LIMIT_VALUE_CDT VARCHAR(20),
        @NOTES_CDT VARCHAR(20);


DECLARE @DATA_DVDM TABLE
(
    DVDM_ID VARCHAR(20),
    TOTAL_AMT DECIMAL(12, 0),
	IS_PTGD BIT
);


--UPDATE dbo.PL_REQUEST_COSTCENTER 
--SET DVMD_ID=(SELECT DVDM_ID FROM dbo.PL_COSTCENTER WHERE PL_COSTCENTER.COST_ID=PL_REQUEST_COSTCENTER.COST_ID),
--TOTAL_AMT_GD=(SELECT SUM(PM.TOTAL_AMT) AS AMT FROM
--(SELECT PLAN_ID,GOODS_ID,TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT  WHERE REQDT_TYPE='I' AND REQ_ID=@p_REQ_ID) PR
--LEFT JOIN dbo.PL_MASTER PM ON PR.PLAN_ID=PM.PLAN_ID
--WHERE PM.COST_ID=PL_REQUEST_COSTCENTER.COST_ID)
--WHERE REQ_ID=@p_REQ_ID


INSERT INTO @DATA_DVDM
SELECT KHOI_ID,
       SUM(TOTAL_AMT) AS TOTAL_AMT,DM.IS_PTGD
FROM dbo.PL_REQUEST_DOC_DT DT
LEFT JOIN CM_DVDM DM ON DM.DVDM_ID=DT.KHOI_ID AND DM.IS_KHOI=1
WHERE REQ_ID = @p_REQ_ID
GROUP BY KHOI_ID,DM.IS_PTGD;





DELETE FROM dbo.PL_REQUEST_PROCESS
WHERE REQ_ID = @p_REQ_ID;




	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_ID =BRANCH_ID,@DEP_ID=DEP_ID,@BRANCH_CREATE=BRANCH_CREATE,@DEP_CREATE=DEP_CREATE FROM dbo.PL_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)



IF(@BRANCH_ID <> @BRANCH_CREATE)
	BEGIN
		IF(@BRANCH_CREATE_TYPE='HS')
		BEGIN
			IF(@BRANCH_TYPE='PGD')
				SET @BRANCH_ID=(SELECT FATHER_ID  FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_ID)

		END
		ELSE
		IF(@BRANCH_CREATE='CN')
			IF(@BRANCH_TYPE='PGD')
				SET @BRANCH_ID=(SELECT FATHER_ID  FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_ID)
	END


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,
    NOTES
)
VALUES
(   @p_REQ_ID,               -- REQ_ID - varchar(15)
    'APPNEW',                  -- PROCESS_ID - varchar(10)
    'P',                     -- STATUS - varchar(5)
    'GDDV',                      -- ROLE_USER - varchar(50)
    @BRANCH_ID,
	@DEP_ID,                      -- BRANCH_ID - varchar(15)
    @p_CHECKER_ID,           -- CHECKER_ID - varchar(15)
    GETDATE() , -- APPROVE_DT - datetime
    NULL, 'N', N'Trưởng đơn vị phê duyệt');
SET @STEP_CURR = 'APPNEW';
SET @STEP_PARENT = 'APPNEW';






IF (EXISTS
(
    SELECT REQ_COST_ID
    FROM dbo.PL_REQUEST_COSTCENTER
    WHERE REQ_ID = @p_REQ_ID
)
   )
BEGIN


    DECLARE lstCostCenter CURSOR FOR
    SELECT COST_ID
    FROM dbo.PL_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
            @STEP_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;
    SET @STEP_PARENT = 'DVCM';
END;





SET @TOTAL_AMT =
(
    SELECT SUM(TOTAL_AMT) AS TOTAL_AMT
    FROM dbo.PL_REQUEST_DOC_DT
    WHERE REQ_ID = @p_REQ_ID
);


IF (EXISTS
(
    SELECT REQ_TRANSFER_ID
    FROM dbo.PL_REQUEST_TRANSFER
    WHERE REQ_DOC_ID = @p_REQ_ID
)
   )
BEGIN


    IF (EXISTS
    (
        SELECT FR_BRN_ID
        FROM dbo.PL_REQUEST_TRANSFER
        WHERE REQ_DOC_ID = @p_REQ_ID
              AND
              (
                  FR_BRN_ID <> @BRANCH_CREATE
                  OR FR_DEP_ID <> @DEP_CREATE
              )
    )
       )
    BEGIN
        DECLARE lstTransfer CURSOR FOR
        SELECT FR_BRN_ID,
               FR_DEP_ID
        FROM dbo.PL_REQUEST_TRANSFER
        WHERE REQ_DOC_ID = @p_REQ_ID
              AND
              (
                  FR_BRN_ID <> @BRANCH_CREATE
                  OR FR_DEP_ID <> @DEP_CREATE
              )
        GROUP BY FR_BRN_ID,
                 FR_DEP_ID;
        OPEN lstTransfer;
        FETCH NEXT FROM lstTransfer
        INTO @FR_BRANCH_ID,
             @FR_DEP_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,
                DEP_ID
            )
            VALUES
            (   @p_REQ_ID,     -- REQ_ID - varchar(15)
                'DVDC',        -- PROCESS_ID - varchar(10)
                'U',           -- STATUS - varchar(5)
                'GDDV',        -- ROLE_USER - varchar(50)
                @FR_BRANCH_ID, -- BRANCH_ID - varchar(15)
                '',            -- CHECKER_ID - varchar(15)
                NULL,          -- APPROVE_DT - datetime
                @STEP_PARENT,  -- PARENT_PROCESS_ID - varchar(10)
                'N',           -- IS_LEAF - varchar(1)
                '',            -- COST_ID - varchar(15)
                '',            -- DVDM_ID - varchar(15)
                N'Chờ đơn vị điều chuyển xác nhận', 1, @FR_DEP_ID);
            FETCH NEXT FROM lstTransfer
            INTO @FR_BRANCH_ID,
                 @FR_DEP_ID;
        END;
        CLOSE lstTransfer;
        DEALLOCATE lstTransfer;
        SET @STEP_PARENT = 'DVDC';
    END;

    -- Đầu mối nhận
    DECLARE lstTransfer CURSOR FOR
    SELECT TO_DVDM_ID
    FROM dbo.PL_REQUEST_TRANSFER
    WHERE REQ_DOC_ID = @p_REQ_ID AND TO_DVDM_ID IS NOT NULL AND TO_DVDM_ID <>''
    GROUP BY TO_DVDM_ID;
    OPEN lstTransfer;
    FETCH NEXT FROM lstTransfer
    INTO @DVDM_ID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
		IF(NOT EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND DVDM_ID=@DVDM_ID AND ROLE_USER='GDDV'))
		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)
            'DVDM_DC',    -- 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
            @STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
            'N',          -- IS_LEAF - varchar(1)
            '',           -- COST_ID - varchar(15)
            @DVDM_ID,     -- DVDM_ID - varchar(15)
            N'Chờ đơn vị đầu mối xác nhận', 0);
		END
        FETCH NEXT FROM lstTransfer
        INTO @DVDM_ID;
    END;
    CLOSE lstTransfer;
    DEALLOCATE lstTransfer;


    IF (EXISTS
    (
        SELECT FR_BRN_ID
        FROM dbo.PL_REQUEST_TRANSFER
        WHERE REQ_DOC_ID = @p_REQ_ID
              AND FR_BRN_ID = @BRANCH_CREATE
              AND FR_DEP_ID = @DEP_CREATE
    )
       )
    BEGIN
        -- Đầu mối cho
        DECLARE lstTransfer CURSOR FOR
        SELECT FR_DVDM_ID
        FROM dbo.PL_REQUEST_TRANSFER
        WHERE REQ_DOC_ID = @p_REQ_ID
              AND FR_BRN_ID = @BRANCH_CREATE
              AND FR_DEP_ID = @DEP_CREATE
			  AND FR_DVDM_ID IS NOT NULL AND FR_DVDM_ID <>''
              AND NOT EXISTS
        (
            SELECT *
            FROM dbo.PL_REQUEST_PROCESS
            WHERE REQ_ID = @p_REQ_ID
                  AND PROCESS_ID = 'DVDM_DC'
                  AND DVDM_ID = FR_DVDM_ID
        )
        GROUP BY FR_DVDM_ID;
        OPEN lstTransfer;
        FETCH NEXT FROM lstTransfer
        INTO @DVDM_ID;
        WHILE @@FETCH_STATUS = 0
        BEGIN
		IF(NOT EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND DVDM_ID=@DVDM_ID AND ROLE_USER='GDDV'))
		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)
                'DVDM_DC',    -- 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
                @STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
                'N',          -- IS_LEAF - varchar(1)
                '',           -- COST_ID - varchar(15)
                @DVDM_ID,     -- DVDM_ID - varchar(15)
                N'Chờ đơn vị đầu mối xác nhận', 0);
		END
            FETCH NEXT FROM lstTransfer
            INTO @DVDM_ID;
        END;
        CLOSE lstTransfer;
        DEALLOCATE lstTransfer;




       IF(EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DVDM_DC'))
			SET @STEP_PARENT='DVDM_DC'


    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)
        'TC',         -- PROCESS_ID - varchar(10)
        'U',          -- STATUS - varchar(5)
        'TC',         -- ROLE_USER - varchar(50)
        '',           -- BRANCH_ID - varchar(15)
        '',           -- CHECKER_ID - varchar(15)
        NULL,         -- APPROVE_DT - datetime
        @STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
        'N',          -- IS_LEAF - varchar(1)
        '',           -- COST_ID - varchar(15)
        '',           -- DVDM_ID - varchar(15)
        N'Chờ đơn vị Tài chính xác nhận',1);
    SET @STEP_PARENT = 'TC';


    IF (NOT EXISTS
    (
        SELECT REQ_TRANSFER_ID
        FROM dbo.PL_REQUEST_TRANSFER
        WHERE REQ_DOC_ID = @p_REQ_ID
              AND
              (
                  FR_BRN_ID <> @BRANCH_CREATE
                  OR FR_DEP_ID <> @DEP_CREATE
              )
    )
       )
    BEGIN
        ---Duyệt DC
        IF (
           (
               SELECT COUNT(T.FR_KHOI_ID) AS COUNT_ROW
               FROM
               (
                   SELECT FR_KHOI_ID
                   FROM dbo.PL_REQUEST_TRANSFER
                   WHERE REQ_DOC_ID = @p_REQ_ID
                   GROUP BY FR_KHOI_ID
               ) T
           ) > 1
           )
        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)
                'TGD_DC',                       -- PROCESS_ID - varchar(10)
                'U',                            -- STATUS - varchar(5)
                'TGD',                          -- ROLE_USER - varchar(50)
                '',                             -- BRANCH_ID - varchar(15)
                '',                             -- CHECKER_ID - varchar(15)
                NULL,                           -- APPROVE_DT - datetime
                @STEP_PARENT,                   -- PARENT_PROCESS_ID - varchar(10)
                '',                             -- IS_LEAF - varchar(1)
                '',                             -- COST_ID - varchar(15)
                '',                             -- DVDM_ID - varchar(15)
                N'Chờ tổng giám đốc phê duyệt', -- NOTES - nvarchar(500)
                NULL                            -- IS_HAS_CHILD - bit
                );
            SET @STEP_PARENT = 'TGD_DC';
        END;
        ELSE
        BEGIN
            DECLARE @LIMTT_MAX DECIMAL(18, 2),
                    @LIMIT_APP DECIMAL(18, 2),
                    @KHOI_ID_TF VARCHAR(20);
            SET @KHOI_ID_TF =
            (
                SELECT TOP 1
                       FR_KHOI_ID
                FROM dbo.PL_REQUEST_TRANSFER
                WHERE REQ_DOC_ID = @p_REQ_ID
            );
            SET @LIMIT_APP =
            (
                SELECT ISNULL(MAX_AMT, 0) - ISNULL(TOTAL_APP_AMT, 0) AS LIMIT_APP
                FROM dbo.LIMIT_ACCUMULATE
                WHERE ROLE_ID = 'GDK'
                      AND DVDM_ID = @KHOI_ID_TF
            );
            SET @TOTAL_TRANSFER =
            (
                SELECT SUM(TOTAL_AMT) AS TOTAL
                FROM dbo.PL_REQUEST_TRANSFER
                WHERE REQ_DOC_ID = @p_REQ_ID
            );
            SET @LIMTT_MAX =
            (
                SELECT LIMIT_VALUE
                FROM dbo.TL_SYSROLE_LIMIT
                WHERE ROLE_ID = 'GDK'
                      AND LIMIT_TYPE = 'DCNS'
            );

            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_DC',                      -- PROCESS_ID - varchar(10)
                'U',                           -- STATUS - varchar(5)
                'GDK',                         -- ROLE_USER - varchar(50)
                '',                            -- BRANCH_ID - varchar(15)
                '',                            -- CHECKER_ID - varchar(15)
                GETDATE(),                     -- APPROVE_DT - datetime
                @STEP_PARENT,                  -- PARENT_PROCESS_ID - varchar(10)
                'N',                           -- IS_LEAF - varchar(1)
                '',                            -- COST_ID - varchar(15)
                @KHOI_ID_TF,                   -- DVDM_ID - varchar(15)
                N'Chờ giám đốc khối xác nhận', -- NOTES - nvarchar(500)
                NULL                           -- IS_HAS_CHILD - bit
                );
            SET @STEP_PARENT = 'GDK_DC';
            IF (@TOTAL_TRANSFER > @LIMTT_MAX OR @TOTAL_TRANSFER > @LIMIT_APP)
            BEGIN
			IF(EXISTS(SELECT DVDM_ID FROM dbo.CM_DVDM WHERE DVDM_ID=@KHOI_ID_TF AND IS_KHOI=1 AND IS_PTGD=1))
			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)
                    'PTGD_DC',                     -- PROCESS_ID - varchar(10)
                    'U',                           -- STATUS - varchar(5)
                    'PTGD',                        -- ROLE_USER - varchar(50)
                    '',                            -- BRANCH_ID - varchar(15)
                    '',                            -- CHECKER_ID - varchar(15)
                    GETDATE(),                     -- APPROVE_DT - datetime
                    @STEP_PARENT,                  -- PARENT_PROCESS_ID - varchar(10)
                    'N',                           -- IS_LEAF - varchar(1)
                    '',                            -- COST_ID - varchar(15)
                    @KHOI_ID_TF,                   -- DVDM_ID - varchar(15)
                    N'Chờ giám đốc khối xác nhận', -- NOTES - nvarchar(500)
                    NULL                           -- IS_HAS_CHILD - bit
                    );
                SET @STEP_PARENT = 'PTGD_DC';
                SET @LIMTT_MAX =
                (
                    SELECT LIMIT_VALUE
                    FROM dbo.TL_SYSROLE_LIMIT
                    WHERE ROLE_ID = 'PTGD'
                          AND LIMIT_TYPE = 'DCNS'
                );
                SET @LIMIT_APP =
                (
                    SELECT ISNULL(MAX_AMT, 0) - ISNULL(TOTAL_APP_AMT, 0) AS LIMIT_APP
                    FROM dbo.LIMIT_ACCUMULATE
                    WHERE ROLE_ID = 'PTGD'
                          AND DVDM_ID = @KHOI_ID_TF
                );

			END
                IF (@TOTAL_TRANSFER > @LIMTT_MAX OR @TOTAL_TRANSFER > @LIMIT_APP OR EXISTS(SELECT DVDM_ID FROM dbo.CM_DVDM WHERE DVDM_ID=@KHOI_ID_TF AND IS_KHOI=1 AND IS_PTGD=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)
                        'TGD_DC',                       -- PROCESS_ID - varchar(10)
                        'U',                            -- STATUS - varchar(5)
                        'TGD',                          -- ROLE_USER - varchar(50)
                        '',                             -- BRANCH_ID - varchar(15)
                        '',                             -- CHECKER_ID - varchar(15)
                        NULL,                           -- APPROVE_DT - datetime
                        @STEP_PARENT,                   -- PARENT_PROCESS_ID - varchar(10)
                        '',                             -- IS_LEAF - varchar(1)
                        '',                             -- COST_ID - varchar(15)
                        '',                             -- DVDM_ID - varchar(15)
                        N'Chờ tổng giám đốc phê duyệt', -- NOTES - nvarchar(500)
                        NULL                            -- IS_HAS_CHILD - bit
                        );
                    SET @STEP_PARENT = 'TGD_DC';
                END;

            END;




        END;
       
     
    END;




END;
ELSE
BEGIN
	
IF(@BRANCH_CREATE_TYPE='PGD')
BEGIN
	DECLARE @BRANCH_PARENT VARCHAR(15)
	SET @BRANCH_PARENT=(SELECT FATHER_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE)
	IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND BRANCH_ID=@BRANCH_PARENT AND (DEP_ID IS NULL OR DEP_ID='')))
	BEGIN
		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,
		NOTES
		)
		VALUES
		(   
		@p_REQ_ID,               -- REQ_ID - varchar(15)
		'DVC',                  -- PROCESS_ID - varchar(10)
		'U',                     -- STATUS - varchar(5)
		'GDDV',                      -- ROLE_USER - varchar(50)
		@BRANCH_PARENT,
		NULL,                      -- BRANCH_ID - varchar(15)
		NULL,           -- CHECKER_ID - varchar(15)
		NULL , -- APPROVE_DT - datetime
		@STEP_PARENT, 'N', N'Trưởng đơn vị phê duyệt');

		SET @STEP_CURR = 'DVC';
		SET @STEP_PARENT = 'DVC';
	END
	
END
ELSE 
IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND ROLE_USER='GDDV'
AND ((
	BRANCH_ID=@BRANCH_CREATE 
	AND ((DEP_ID =@DEP_CREATE) OR ((@DEP_CREATE IS NULL OR @DEP_CREATE='') 
	AND (DEP_ID IS NULL OR DEP_ID='')))
	)
	OR EXISTS(SELECT PC.COST_ID FROM dbo.PL_COSTCENTER PC 
	LEFT JOIN dbo.PL_COSTCENTER_DT PCDT ON PCDT.COST_ID = PC.COST_ID WHERE PL_REQUEST_PROCESS.DVDM_ID=PC.DVDM_ID AND DEP_ID=@DEP_CREATE AND BRANCH_ID=@BRANCH_CREATE)
	)
))
BEGIN
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,
		NOTES
		)
		VALUES
		(   
		@p_REQ_ID,               -- REQ_ID - varchar(15)
		'DVC',                  -- PROCESS_ID - varchar(10)
		'U',                     -- STATUS - varchar(5)
		'GDDV',                      -- ROLE_USER - varchar(50)
		@BRANCH_CREATE,
		@DEP_CREATE,                      -- BRANCH_ID - varchar(15)
		NULL,           -- CHECKER_ID - varchar(15)
		NULL , -- APPROVE_DT - datetime
		@STEP_PARENT, 'N', N'Trưởng đơn vị phê duyệt');

SET @STEP_CURR = 'DVC';
SET @STEP_PARENT = 'DVC';
END





SET @IS_NEXT =
(
    SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID, 'GDDV')
);




IF (@IS_NEXT = 1)
BEGIN

    DECLARE lstCostCenter CURSOR FOR
    SELECT DVDM_ID,
           TOTAL_AMT
    FROM @DATA_DVDM;
    OPEN lstCostCenter;
    FETCH NEXT FROM lstCostCenter
    INTO @DVDM_ID,
         @TOTAL_AMT_GD;
    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)
            'GDK_TT',                                  -- PROCESS_ID - varchar(10)
            'U',                                       -- STATUS - varchar(5)
            'GDK',                                     -- ROLE_USER - varchar(50)
            '',                                        -- BRANCH_ID - varchar(15)
            '',                                        -- CHECKER_ID - varchar(15)
            NULL,                                      -- APPROVE_DT - datetime
            @STEP_PARENT,                              -- PARENT_PROCESS_ID - varchar(10)
            'N',                                       -- IS_LEAF - varchar(1)
            '',                                        -- COST_ID - varchar(15)
            @DVDM_ID, N'Chờ giám đốc khối xác nhận', 0 -- DVDM_ID - varchar(15)
            );
        FETCH NEXT FROM lstCostCenter
        INTO @DVDM_ID,
             @TOTAL_AMT_GD;
    END;
    CLOSE lstCostCenter;
    DEALLOCATE lstCostCenter;

    SET @IS_NEXT =
    (
        SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID, 'GDK')
    );

    SET @STEP_PARENT = 'GDK_TT';
    IF (@IS_NEXT = 1)
    BEGIN

		IF( EXISTS (SELECT DVDM_ID FROM @DATA_DVDM WHERE IS_PTGD=1) )
		BEGIN
        DECLARE lstCostCenter CURSOR FOR
        SELECT DVDM_ID,
               TOTAL_AMT
        FROM @DATA_DVDM WHERE IS_PTGD=1;
        OPEN lstCostCenter;
        FETCH NEXT FROM lstCostCenter
        INTO @DVDM_ID,
             @TOTAL_AMT_GD;
        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)
                'PTGDK_TT',                                         -- PROCESS_ID - varchar(10)
                'U',                                                -- STATUS - varchar(5)
                'PTGD',                                             -- ROLE_USER - varchar(50)
                '',                                                 -- BRANCH_ID - varchar(15)
                '',                                                 -- CHECKER_ID - varchar(15)
                NULL,                                               -- APPROVE_DT - datetime
                @STEP_PARENT,                                       -- PARENT_PROCESS_ID - varchar(10)
                'N',                                                -- IS_LEAF - varchar(1)
                '',                                                 -- COST_ID - varchar(15)
                @DVDM_ID, N'Chờ phó tổng giám đốc khối xác nhận', 0 -- DVDM_ID - varchar(15)
                );
            FETCH NEXT FROM lstCostCenter
            INTO @DVDM_ID,
                 @TOTAL_AMT_GD;
        END;
        CLOSE lstCostCenter;
        DEALLOCATE lstCostCenter;

        SET @IS_NEXT =
        (
            SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID, 'PTGD')
        );

        SET @STEP_PARENT = 'PTGDK_TT';
		END
		IF(EXISTS (SELECT DVDM_ID FROM @DATA_DVDM WHERE IS_PTGD=0))
		BEGIN
		SET @IS_NEXT=1
		END
        IF (@IS_NEXT = 1)
        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)
                'TGD',                               -- PROCESS_ID - varchar(10)
                'U',                                 -- STATUS - varchar(5)
                'TGD',                               -- ROLE_USER - varchar(50)
                '',                                  -- BRANCH_ID - varchar(15)
                '',                                  -- CHECKER_ID - varchar(15)
                NULL,                                -- APPROVE_DT - datetime
                @STEP_PARENT,                        -- PARENT_PROCESS_ID - varchar(10)
                'N',                                 -- IS_LEAF - varchar(1)
                '',                                  -- COST_ID - varchar(15)
                '', N'Chờ tổng giám đốc xác nhận', 0 -- DVDM_ID - varchar(15)
                );
            SET @STEP_PARENT = 'TGD';

			IF((SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID) > (SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='TCTT-HDQT'))
			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)
                'HDQT',                               -- PROCESS_ID - varchar(10)
                'U',                                 -- STATUS - varchar(5)
                'HDQT',                               -- ROLE_USER - varchar(50)
                '',                                  -- BRANCH_ID - varchar(15)
                '',                                  -- CHECKER_ID - varchar(15)
                NULL,                                -- APPROVE_DT - datetime
                @STEP_PARENT,                        -- PARENT_PROCESS_ID - varchar(10)
                'N',                                 -- IS_LEAF - varchar(1)
                '',                                  -- COST_ID - varchar(15)
                '', N'Chờ HDQT xác nhận', 0 -- DVDM_ID - varchar(15)
                );
            SET @STEP_PARENT = 'HDQT';
			END
        END;



	
    --ELSE
    --BEGIN

    --END
		
    END;

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,
    NOTES
)
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
    @STEP_PARENT, 'Y', N'Hoàn tất');



IF @@Error <> 0
    GOTO ABORT;



DECLARE @PROCESS_ID_CURR VARCHAR(10);
SET @PROCESS_ID_CURR =
(
    SELECT TOP 1
           PROCESS_ID
    FROM dbo.PL_REQUEST_PROCESS
    WHERE REQ_ID = @p_REQ_ID
          AND PARENT_PROCESS_ID = 'APPNEW'
);

UPDATE dbo.PL_REQUEST_PROCESS
SET STATUS = 'C'
WHERE PARENT_PROCESS_ID = 'APPNEW'
      AND REQ_ID = @p_REQ_ID;
UPDATE dbo.PL_REQUEST_DOC
SET AUTH_STATUS = @p_AUTH_STATUS,
    APPROVE_DT = @p_APPROVE_DT,
    CHECKER_ID = @p_CHECKER_ID,
    PROCESS_ID = @PROCESS_ID_CURR
WHERE REQ_ID = @p_REQ_ID;

UPDATE dbo.PL_REQUEST_DOC_DT
SET CHECKER_ID=@p_CHECKER_ID,
APPROVE_DT=@p_APPROVE_DT
WHERE REQ_ID = @p_REQ_ID;

INSERT INTO dbo.PL_PROCESS
(
    REQ_ID,
    PROCESS_ID,
    CHECKER_ID,
    APPROVE_DT,
    PROCESS_DESC,
    NOTES
)
VALUES
(   @p_REQ_ID,                                            -- REQ_ID - varchar(15)
    'APPNEW',                                               -- PROCESS_ID - varchar(10)
    @p_CHECKER_ID,                                        -- CHECKER_ID - varchar(15)
    @p_APPROVE_DT,                                        -- APPROVE_DT - datetime
    @p_PROCESS_DES, N'Trưởng đơn vị phê duyệt' -- PROCESS_DESC - nvarchar(1000)
    );




IF (EXISTS
(
    SELECT REQ_ID
    FROM dbo.PL_REQUEST_DOC
    WHERE REQ_ID = @p_REQ_ID
          AND PROCESS_ID = 'APPROVE'
)
   )
BEGIN
    EXEC dbo.PL_REQ_DOC_UPDATE_AFTER_APPROVE @p_REQ_ID = @p_REQ_ID;
    EXEC dbo.PL_REQ_DOC_Ins_To_TR_REQ_DOC @p_PL_REQ_ID = @p_REQ_ID;
    SET @Result = '0';
END;


SET @Result = '1';


COMMIT TRANSACTION;
SELECT @Result AS Result,
       @ROLE_USER_NOTIFI AS ROLE_NOTIFI,
       '' ErrorDesc;
RETURN '0';
ABORT:
BEGIN

    ROLLBACK TRANSACTION;
    SELECT '-1' AS Result,
           '' ROLE_NOTIFI,
           '' ErrorDesc;
    RETURN '-1';
END;





USE [gAMSPro_VietcapitalBank_v2]
GO
/****** Object:  StoredProcedure [dbo].[PL_REQ_PROCESS_CHILD_App]    Script Date: 12-May-20 12:00:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PL_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(20),
@p_REF_ID INT
AS
BEGIN TRANSACTION



DECLARE  @ERROR BIT ,@EROOR_DES NVARCHAR(500)

SELECT @ERROR=ERROR,
       @EROOR_DES=ERROR_DES FROM dbo.FN_CHECK_VALIDATE_APP(@p_REQ_ID,'PDDC','PL_REQUEST_DOC',@p_MAKER_ID,'DVKD')
IF(@ERROR=1)
BEGIN
	 ROLLBACK TRANSACTION;
    SELECT -1  Result,
           @EROOR_DES ErrorDesc
   
    RETURN 0;
END



DECLARE @LEVEL INT,@DEP_ID VARCHAR(20),@BRANCH_ID VARCHAR(20),@BRANCH_CREATE VARCHAR(20),@DEP_CREATE VARCHAR(20)
DECLARE @ROLE_ID VARCHAR(20),@NOTES NVARCHAR(500),
				@PROCESS_CURR VARCHAR(10),
				@STEP_CURR INT,
				@STEP_NEXT INT,
				@PROCESS_NEXT VARCHAR(10),
				@TYPE_JOB_NAME NVARCHAR(100), @FULLNAME NVARCHAR(100)

SELECT @BRANCH_ID=  TLSUBBRID,@DEP_ID=SECUR_CODE ,@ROLE_ID=RoleName FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID


SELECT @BRANCH_CREATE=BRANCH_CREATE,@DEP_CREATE=DEP_CREATE FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID


DECLARE
@COST_ID_TABLE TABLE (
	COST_ID VARCHAR(15)
)

DECLARE @DVDM_ID_TABLE TABLE (
	DVDM_ID VARCHAR(15)
)

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

	INSERT INTO @DVDM_ID_TABLE
	SELECT DVDM_ID FROM dbo.PL_COSTCENTER WHERE COST_ID IN (SELECT COST_ID FROM @COST_ID_TABLE) GROUP BY DVDM_ID

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

		


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


				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)
				IF(@PROCESS_CURR='TC')
				BEGIN
						SELECT @ERROR=ERROR,
						   @EROOR_DES=ERROR_DES FROM dbo.FN_CHECK_VALIDATE_APP(@p_REQ_ID,'PDDC','PL_REQUEST_DOC',@p_MAKER_ID,'TC')
					IF(@ERROR=1)
					BEGIN
						 ROLLBACK TRANSACTION;
						SELECT '-1'  Result,
							   @EROOR_DES ErrorDesc
   
						RETURN '0';
					END


						SET @NOTES=(SELECT CONTENT FROM dbo.CM_ALLCODE WHERE (@PROCESS_CURR LIKE CDVAL + '%') AND CDNAME='PROCESS_ID' AND CDTYPE='REQ')   
						UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='P',NOTES=@NOTES+ N' đã phê duyệt', CHECKER_ID=@p_MAKER_ID,APPROVE_DT=GETDATE()  WHERE REQ_ID=@p_REQ_ID AND ROLE_USER=@ROLE_ID AND PROCESS_ID=@PROCESS_CURR
						UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='C' WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@PROCESS_CURR
						UPDATE dbo.PL_REQUEST_DOC SET PROCESS_ID=@PROCESS_NEXT WHERE REQ_ID=@p_REQ_ID
				END
				ELSE
				BEGIN
					
				

			
			

				SET @NOTES=(SELECT CONTENT FROM dbo.CM_ALLCODE WHERE (@PROCESS_CURR LIKE CDVAL + '%') AND CDNAME='PROCESS_ID' AND CDTYPE='REQ')   


				UPDATE dbo.PL_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 DVDM_ID FROM @DVDM_ID_TABLE)
				UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='P',NOTES=@NOTES+ N' đã phê duyệt', CHECKER_ID=@p_MAKER_ID,APPROVE_DT=GETDATE() WHERE REQ_ID=@p_REQ_ID AND ROLE_USER=@ROLE_ID AND DVDM_ID IN (SELECT DVDM_ID FROM @DVDM_ID_TABLE)
							


				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

				UPDATE dbo.PL_REQUEST_TRANSFER SET AUTH_STATUS='A',CHECKER_ID=@p_MAKER_ID,APPROVE_DT=GETDATE()  WHERE REQ_DOC_ID=@p_REQ_ID AND FR_BRN_ID=@BRANCH_ID AND (FR_DEP_ID=@DEP_ID OR FR_DEP_ID IS NULL OR FR_DEP_ID='')
				UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='P',NOTES=@NOTES+ N' đã phê duyệt', CHECKER_ID=@p_MAKER_ID,APPROVE_DT=GETDATE()  WHERE REQ_ID=@p_REQ_ID AND ROLE_USER=@ROLE_ID AND BRANCH_ID=@BRANCH_ID AND (DEP_ID=@DEP_ID OR DEP_ID IS NULL OR DEP_ID='')


		IF(EXISTS(SELECT REQ_TRANSFER_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID AND (FR_BRN_ID <> @BRANCH_CREATE OR FR_DEP_ID<> @DEP_CREATE)))
			BEGIN
			IF(NOT EXISTS(SELECT REQ_COST_ID FROM dbo.PL_REQUEST_COSTCENTER WHERE REQ_ID=@p_REQ_ID AND AUTH_STATUS<>'A'))
			BEGIN
				    IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID AND AUTH_STATUS <>'A'))
					BEGIN
							DECLARE 	@LIMIT_VALUE DECIMAL(18,0),@TOTAL_TRANSFER DECIMAL(18,2),
							@IS_NEXT BIT=0,@STEP_PARENT VARCHAR(20),@TOTAL_AMT_GD DECIMAL(12,0),@STOP BIT,@ROLE_TF VARCHAR(20),@DVDM_ID VARCHAR(20),@NOTE NVARCHAR(100)
							DECLARE @ROLE_CDT VARCHAR(20),@DVDM_CDT VARCHAR(20),@LIMIT_VALUE_CDT VARCHAR(20),@NOTES_CDT VARCHAR(20),@TOTAL_AMT DECIMAL(18,2)	
						

							SET @STEP_PARENT=(SELECT PARENT_PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID='TC' AND REQ_ID=@p_REQ_ID)
						
							SET @NOTE=   (SELECT CONTENT FROM dbo.CM_ALLCODE WHERE CDVAL='DVDM' AND CDNAME='PROCESS_ID' AND CDTYPE='REQ')
							

							DECLARE lstTransfer CURSOR FOR
							SELECT FR_DVDM_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID AND   FR_DVDM_ID IS NOT NULL AND FR_DVDM_ID <>'' AND (FR_BRN_ID <> @BRANCH_CREATE OR FR_DEP_ID<> @DEP_CREATE)
							AND NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND ROLE_USER='GDDV' AND DVDM_ID=FR_DVDM_ID	)
							GROUP BY FR_DVDM_ID
							OPEN lstTransfer
							FETCH NEXT FROM lstTransfer INTO @DVDM_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)
									'DVDM_DC',        -- 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
									@STEP_PARENT,        -- PARENT_PROCESS_ID - varchar(10)
									'N',        -- IS_LEAF - varchar(1)
									'',        -- COST_ID - varchar(15)
									@DVDM_ID ,        -- DVDM_ID - varchar(15)
									N'Chờ '+@NOTE+N' xác nhận'
									,0)

							FETCH NEXT FROM lstTransfer INTO @DVDM_ID
							END
							CLOSE lstTransfer
							DEALLOCATE lstTransfer


							 IF(EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DVDM_DC'))
								SET @STEP_PARENT='DVDM_DC'
							

						UPDATE dbo.PL_REQUEST_PROCESS SET PARENT_PROCESS_ID=@STEP_PARENT,STATUS='U' WHERE  PROCESS_ID='TC' AND REQ_ID=@p_REQ_ID
						SET @STEP_PARENT='TC'

						---Duyệt DC

						IF( (SELECT COUNT(T.FR_KHOI_ID) AS COUNT_ROW FROM (SELECT FR_KHOI_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID 
						GROUP BY FR_KHOI_ID)T
						) > 1)
						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)
							    'TGD_DC',        -- PROCESS_ID - varchar(10)
							    'U',        -- STATUS - varchar(5)
							    'TGD',        -- ROLE_USER - varchar(50)
							    '',        -- BRANCH_ID - varchar(15)
							    '',        -- CHECKER_ID - varchar(15)
							    NULL, -- APPROVE_DT - datetime
							    @STEP_PARENT,        -- PARENT_PROCESS_ID - varchar(10)
							    '',        -- IS_LEAF - varchar(1)
							    '',        -- COST_ID - varchar(15)
							    '',        -- DVDM_ID - varchar(15)
							    N'Chờ tổng giám đốc phê duyệt',       -- NOTES - nvarchar(500)
							    NULL       -- IS_HAS_CHILD - bit
							    )
							SET	@STEP_PARENT='TGD_DC'
						END
						ELSE
						BEGIN
							DECLARE @LIMTT_MAX  DECIMAL(18,2),@LIMIT_APP DECIMAL(18,2) ,@KHOI_ID_TF VARCHAR(20)
							SET @KHOI_ID_TF=(SELECT TOP 1 FR_KHOI_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID)
							SET @LIMIT_APP=(SELECT ISNULL(MAX_AMT,0)- ISNULL(TOTAL_APP_AMT,0) AS LIMIT_APP FROM dbo.LIMIT_ACCUMULATE WHERE ROLE_ID='GDK' AND DVDM_ID=@KHOI_ID_TF)
							SET @TOTAL_TRANSFER=(SELECT SUM(TOTAL_AMT) AS TOTAL FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID)
							SET @LIMTT_MAX=(SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE ROLE_ID='GDK' AND LIMIT_TYPE='DCNS')

							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_DC',        -- PROCESS_ID - varchar(10)
							    'U',        -- STATUS - varchar(5)
							    'GDK',        -- ROLE_USER - varchar(50)
							    '',        -- BRANCH_ID - varchar(15)
							    '',        -- CHECKER_ID - varchar(15)
							    GETDATE(), -- APPROVE_DT - datetime
							    @STEP_PARENT,        -- PARENT_PROCESS_ID - varchar(10)
							    'N',        -- IS_LEAF - varchar(1)
							    '',        -- COST_ID - varchar(15)
							    @KHOI_ID_TF,        -- DVDM_ID - varchar(15)
							    N'Chờ giám đốc khối xác nhận',       -- NOTES - nvarchar(500)
							    NULL       -- IS_HAS_CHILD - bit
							 )
							 SET @STEP_PARENT='GDK_DC';
							 IF(@TOTAL_TRANSFER>@LIMTT_MAX OR @TOTAL_TRANSFER>@LIMIT_APP)
							 BEGIN
								IF(EXISTS(SELECT DVDM_ID FROM dbo.CM_DVDM WHERE DVDM_ID=@KHOI_ID_TF AND IS_KHOI=1 AND IS_PTGD=1))
									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)
							    'PTGD_DC',        -- PROCESS_ID - varchar(10)
							    'U',        -- STATUS - varchar(5)
							    'PTGD',        -- ROLE_USER - varchar(50)
							    '',        -- BRANCH_ID - varchar(15)
							    '',        -- CHECKER_ID - varchar(15)
							    GETDATE(), -- APPROVE_DT - datetime
							    @STEP_PARENT,        -- PARENT_PROCESS_ID - varchar(10)
							    'N',        -- IS_LEAF - varchar(1)
							    '',        -- COST_ID - varchar(15)
							    @KHOI_ID_TF,        -- DVDM_ID - varchar(15)
							    N'Chờ giám đốc khối xác nhận',       -- NOTES - nvarchar(500)
							    NULL       -- IS_HAS_CHILD - bit
							 )
									SET @STEP_PARENT='PTGD_DC'
									SET @LIMTT_MAX=(SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE ROLE_ID='GDK' AND LIMIT_TYPE='DCNS')
									SET @LIMIT_APP=(SELECT ISNULL(MAX_AMT,0)- ISNULL(TOTAL_APP_AMT,0) AS LIMIT_APP FROM dbo.LIMIT_ACCUMULATE WHERE ROLE_ID='GDK' AND DVDM_ID=@KHOI_ID_TF)
									
									END
									IF(@TOTAL_TRANSFER>@LIMTT_MAX OR @TOTAL_TRANSFER>@LIMIT_APP OR EXISTS(SELECT DVDM_ID FROM dbo.CM_DVDM WHERE DVDM_ID=@KHOI_ID_TF AND IS_KHOI=1 AND IS_PTGD=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)
										'TGD_DC',        -- PROCESS_ID - varchar(10)
										'U',        -- STATUS - varchar(5)
										'TGD',        -- ROLE_USER - varchar(50)
										'',        -- BRANCH_ID - varchar(15)
										'',        -- CHECKER_ID - varchar(15)
										NULL, -- APPROVE_DT - datetime
										@STEP_PARENT,        -- PARENT_PROCESS_ID - varchar(10)
										'',        -- IS_LEAF - varchar(1)
										'',        -- COST_ID - varchar(15)
										'',        -- DVDM_ID - varchar(15)
										N'Chờ tổng giám đốc phê duyệt',       -- NOTES - nvarchar(500)
										NULL       -- IS_HAS_CHILD - bit
									)
									SET	@STEP_PARENT='TGD_DC'
								 END
							
							 END




						END
						--- Duyệt TT

					
						
						UPDATE dbo.PL_REQUEST_PROCESS SET [STATUS]='C' WHERE PROCESS_ID='DVDM_DC' AND REQ_ID=@p_REQ_ID
						UPDATE dbo.PL_REQUEST_PROCESS SET PARENT_PROCESS_ID=@STEP_PARENT,STATUS='U' WHERE  PROCESS_ID='APPROVE' AND REQ_ID=@p_REQ_ID
						UPDATE dbo.PL_REQUEST_DOC SET PROCESS_ID='DVDM_DC' WHERE REQ_ID=@p_REQ_ID

				END	
			END
		END


	
		
		
			

				IF(EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='APPROVE'))
			BEGIN
				
					
					EXEC dbo.PL_REQ_DOC_UPDATE_AFTER_APPROVE @p_REQ_ID = @p_REQ_ID 
					EXEC dbo.PL_REQ_DOC_Ins_To_TR_REQ_DOC @p_PL_REQ_ID = @p_REQ_ID



				
			END
	

				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