USE [gAMSPro_VietcapitalBank_v2]
GO
/****** Object:  StoredProcedure [dbo].[PL_REQUEST_DOC_App]    Script Date: 15-May-20 09:59:03 ******/
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 AND COST_ID IS NOT NULL AND COST_ID <>'';
    OPEN lstCostCenter;
    FETCH NEXT FROM lstCostCenter
    INTO @COST_ID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
	IF(NOT EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID  AND ROLE_USER='GDDV' AND ( DVDM_ID=@COST_ID OR @COST_ID IN ((SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC
		LEFT JOIN dbo.PL_COSTCENTER_DT PCD ON PCD.COST_ID = PC.COST_ID
		WHERE PCD.BRANCH_ID=PL_REQUEST_PROCESS.BRANCH_ID AND PCD.DEP_ID=PL_REQUEST_PROCESS.DEP_ID)) ) ))
		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)
            );
		END

        FETCH NEXT FROM lstCostCenter
        INTO @COST_ID;
    END;
    CLOSE lstCostCenter;
    DEALLOCATE lstCostCenter;
	IF(EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DVCM'))
		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 ROLE_USER='GDDV' AND ( DVDM_ID=@DVDM_ID OR @DVDM_ID IN ((SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC
		LEFT JOIN dbo.PL_COSTCENTER_DT PCD ON PCD.COST_ID = PC.COST_ID
		WHERE PCD.BRANCH_ID=PL_REQUEST_PROCESS.BRANCH_ID AND PCD.DEP_ID=PL_REQUEST_PROCESS.DEP_ID)) ) ))
		
		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 ROLE_USER='GDDV' AND ( DVDM_ID=@DVDM_ID OR @DVDM_ID IN ((SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC
		LEFT JOIN dbo.PL_COSTCENTER_DT PCD ON PCD.COST_ID = PC.COST_ID
		WHERE PCD.BRANCH_ID=PL_REQUEST_PROCESS.BRANCH_ID AND PCD.DEP_ID=PL_REQUEST_PROCESS.DEP_ID)) ) ))
		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;