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

USE [gAMSPro_VCCB_v2]

GO

/****** Object:  StoredProcedure [dbo].[rpt_THONG_KE_THIET_BI_CNTT_2018_TONGHOP_BanViet]    Script Date: 10/26/2020 2:26:47 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[rpt_THONG_KE_THIET_BI_CNTT_2018_TONGHOP_BanViet] @p_Fromdate VARCHAR(10) =NULL, @p_Todate VARCHAR(10) =NULL,

    @p_Branch_ID VARCHAR(15) =NULL, @p_Level VARCHAR(10) =NULL, @p_BRANCH_LOGIN VARCHAR(15) =NULL, @p_Div_id VARCHAR(15) =NULL,

    @p_User_ID VARCHAR(15) =NULL, @p_AssType VARCHAR(15) =NULL

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

AS

DECLARE @tmp_branch TABLE(BRANCH_ID VARCHAR(15));

INSERT INTO @tmp_branch

SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_Branch_ID);

DECLARE @tmp_login TABLE(BRANCH_ID VARCHAR(15));

INSERT INTO @tmp_login

SELECT BRANCH_ID FROM CM_BRANCH WHERE BRANCH_ID=@p_BRANCH_LOGIN;

DECLARE @returnTable TABLE

(

DEP_ID VARCHAR(15),

DEP_CODE VARCHAR(15),

DEP_LV1 VARCHAR(15),

DEP_LV2 VARCHAR(15),

BRANCH_CODE VARCHAR(15),

DEP_NAME NVARCHAR(200),

QTY INT,

SL_LAPTOP INT,

SL_PC INT,

SL_PC_RAP INT,

SL_MAYIN INT,

SL_MAY_SCAN INT,

SL_UPS INT,

SL_LAPTOP_0_3 INT,

SL_PC_0_3 INT,

SL_PC_RAP_0_3 INT,

SL_MAYIN_0_3 INT,

SL_MAY_SCAN_0_3 INT,

SL_UPS_0_3 INT,

SL_LAPTOP_3_5 INT,

SL_PC_3_5 INT,

SL_PC_RAP_3_5 INT,

SL_MAYIN_3_5 INT,

SL_MAY_SCAN_3_5 INT,

SL_UPS_3_5 INT,

SL_LAPTOP_5_ INT,

SL_PC_5_ INT,

SL_PC_RAP_5_ INT,

SL_MAYIN_5_ INT,

SL_MAY_SCAN_5_ INT,

SL_UPS_5_ INT,

-- luctv 15 10 2019 bo sung them cac so lieu > 7 nam

SL_LAPTOP_7_ INT,

SL_PC_7_ INT,

SL_PC_RAP_7_ INT,

SL_MAYIN_7_ INT,

SL_MAY_SCAN_7_ INT,

SL_UPS_7_ INT

);

--LUCTV 29 08 2019 BO SUNG THEM BIEN BRANCH_CODE_FAT DE LAY MA DON VI CHA CUA PHONG BAN, DON VI

DECLARE @TMP TABLE(ID VARCHAR(15), BRANCH_CODE VARCHAR(15), DEP_NAME NVARCHAR(200), BRANCH_ID VARCHAR(15), BRANCH_CODE_FAT VARCHAR(15));

-- LUCTV 28-08-2019 INSERT BANG TEMP LAN THU NHAT BAO GOM DANH SACH CAC PHONG BAN CUA DON VI, DU CHON ALL HAY UNIT

INSERT INTO @TMP

SELECT

    A.DEP_CODE, B.BRANCH_CODE, MAX(A.DEP_NAME) AS DEP_NAME, A.BRANCH_ID, BF.BRANCH_CODE

  FROM CM_DEPARTMENT A

	   LEFT JOIN CM_BRANCH B ON A.BRANCH_ID =B.BRANCH_ID

	   LEFT JOIN CM_BRANCH BF ON B.FATHER_ID = BF.BRANCH_ID

 WHERE(A.DEP_ID = @p_Div_id OR @p_Div_id IS NULL OR @p_Div_id ='')

	 AND (A.BRANCH_ID =@p_Branch_ID)

  --((@P_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_branch))

 	--OR (@P_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_ID) OR @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)

 	--AND ((@P_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_login))

 	--OR (@P_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_LOGIN) OR @p_BRANCH_LOGIN = '' OR @p_BRANCH_LOGIN IS NULL 

 	--OR @p_BRANCH_ID IS NOT NULL OR @p_BRANCH_ID <> '')

 GROUP BY A.DEP_CODE, B.BRANCH_CODE, A.BRANCH_ID,BF.BRANCH_CODE

 --- LUCTV 28-08-2019 INSERT BANG TEMP LAN THU 2 BAO GOM DANH SACH CAC DON VI TRUC THUOC CUA DON VI DC CHON (MA PHONG BAN SE = NULL, TEN PHONG BAN = TEN DON VI)

 INSERT INTO @TMP

SELECT

    NULL DEP_CODE, B.BRANCH_CODE, B.BRANCH_NAME DEP_NAME,B.BRANCH_ID, BF.BRANCH_CODE

    FROM CM_BRANCH B

	LEFT JOIN CM_BRANCH BF ON B.FATHER_ID = BF.BRANCH_ID

    --LEFT JOIN dbo.CM_DEPARTMENT CT ON A.DEPT_ID=CT.DEP_ID

 WHERE B.BRANCH_ID <> @p_Branch_ID AND B.BRANCH_ID <> @p_BRANCH_LOGIN AND B.BRANCH_CODE <>'000'

 AND 

  ((@P_LEVEL = 'ALL' AND B.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_branch))

 	OR (@P_LEVEL = 'UNIT' AND B.BRANCH_ID = @p_BRANCH_ID) OR @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)

 	--AND ((@P_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_login))

 	--OR (@P_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_LOGIN) OR @p_BRANCH_LOGIN = '' OR @p_BRANCH_LOGIN IS NULL 

 	---OR @p_BRANCH_ID IS NOT NULL OR @p_BRANCH_ID <> ''

 ORDER BY B.BRANCH_CODE, BF.BRANCH_CODE DESC

--PRINT [@TMP]

DECLARE @COUNT INT=0;

DECLARE @DEP_CODE VARCHAR(15) =NULL;

DECLARE @BRANCH_CODE VARCHAR(15) =NULL;

DECLARE @DEP_NAME NVARCHAR(200) =NULL;

DECLARE @BRANCH_ID NVARCHAR(200) =NULL;

DECLARE @BRANCH_CODE_FAT VARCHAR(20) =NULL;

DECLARE @SL INT=0;

DECLARE @GROUP_GENERAL_ID VARCHAR(15) =NULL;

WHILE((SELECT COUNT(*)FROM @TMP)>0)BEGIN

    SET @COUNT=@COUNT+1;

    SET @DEP_CODE=(SELECT TOP 1. ID FROM @TMP);

    SET @BRANCH_CODE=(SELECT TOP 1. BRANCH_CODE FROM @TMP);

    SET @DEP_NAME=(SELECT TOP 1. DEP_NAME FROM @TMP);

	SET @BRANCH_ID = (SELECT TOP 1. BRANCH_ID FROM @TMP);

	SET @BRANCH_CODE_FAT =(SELECT TOP 1. BRANCH_CODE_FAT FROM @TMP);

	PRINT @BRANCH_CODE

    DECLARE @TMP_GROUP_GENERAL TABLE(GROUP_GENERAL_ID VARCHAR(15), SOLUONG INT);

    DECLARE @SL_LAPTOP INT=0;

    DECLARE @SL_PC INT=0;

    DECLARE @SL_PC_RAP INT=0;

    DECLARE @SL_MAYIN INT=0;

    DECLARE @SL_MAY_SCAN INT=0;

    DECLARE @SL_UPS INT=0;

    --0 đến 3 năm -- LUCTV 28082019 BO SUNG THEM DIEU KIEN NEU MA PHONG = NULL THI TIEN HANH GROUP THEO MA DON VI

    DECLARE @TMP_GROUP_GENERAL_0_3 TABLE(GROUP_GENERAL_ID VARCHAR(15), SOLUONG INT);

    INSERT INTO @TMP_GROUP_GENERAL_0_3

    SELECT

        B.GROUP_GENERAL_ID, COUNT(B.GROUP_GENERAL_ID) AS SL

      FROM dbo.ASS_MASTER A

           INNER JOIN dbo.ASS_GROUP_GENERAL B ON B.GROUP_ID=A.GROUP_ID

           LEFT JOIN dbo.CM_DEPARTMENT CT ON A.DEPT_ID=CT.DEP_ID

		   LEFT JOIN ASS_STATUS ST ON ST.STATUS_ID = A.ASS_STATUS

     WHERE 1= 1 AND ((@DEP_CODE IS NOT NULL AND CT.DEP_CODE=@DEP_CODE AND A.BRANCH_ID = @BRANCH_ID) OR @DEP_CODE IS NULL AND A.BRANCH_ID = @BRANCH_ID)

	 AND DATEDIFF(YEAR, A.USE_DATE, GETDATE())<=3

	 AND A.AMORT_STATUS NOT IN ( 'DTL', 'VNM')

	AND ((@DEP_CODE IS NULL 

	AND ((@p_Level = 'ALL' AND   A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_branch))

 	OR (@p_Level = 'UNIT'  AND  A.BRANCH_ID = @p_BRANCH_ID) OR @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)

 	--AND ((@p_Level = 'ALL'  AND  A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_login))

 	--OR (@p_Level = 'UNIT' AND  A.BRANCH_ID = @p_BRANCH_LOGIN) OR @p_BRANCH_LOGIN = '' OR @p_BRANCH_LOGIN IS NULL 

 	--OR @p_BRANCH_ID IS NOT NULL OR @p_BRANCH_ID <> '')

	) OR A.BRANCH_ID = @BRANCH_ID)

    GROUP BY B.GROUP_GENERAL_ID;

    DECLARE @SL_LAPTOP_0_3 INT=0;

    DECLARE @SL_PC_0_3 INT=0;

    DECLARE @SL_PC_RAP_0_3 INT=0;

    DECLARE @SL_MAYIN_0_3 INT=0;

    DECLARE @SL_MAY_SCAN_0_3 INT=0;

    DECLARE @SL_UPS_0_3 INT=0;

    SELECT

        @SL_LAPTOP_0_3=SOLUONG

      FROM @TMP_GROUP_GENERAL_0_3

     WHERE GROUP_GENERAL_ID='ASGG00000000001';

    SELECT

        @SL_PC_0_3=SOLUONG

      FROM @TMP_GROUP_GENERAL_0_3

     WHERE GROUP_GENERAL_ID='ASGG00000000002';

    SELECT

        @SL_PC_RAP_0_3=SOLUONG

      FROM @TMP_GROUP_GENERAL_0_3

     WHERE GROUP_GENERAL_ID='ASGG00000000004';

    SELECT

        @SL_MAYIN_0_3=SOLUONG

      FROM @TMP_GROUP_GENERAL_0_3

     WHERE GROUP_GENERAL_ID='ASGG00000000003';

    SELECT

        @SL_MAY_SCAN_0_3=SOLUONG

      FROM @TMP_GROUP_GENERAL_0_3

     WHERE GROUP_GENERAL_ID='ASGG00000000005';

    SELECT

        @SL_UPS_0_3=SOLUONG

      FROM @TMP_GROUP_GENERAL_0_3

     WHERE GROUP_GENERAL_ID='ASGG00000000006';

    DELETE FROM @TMP_GROUP_GENERAL_0_3;

    --3 đến 5 năm - LUCTV 28082019 BO SUNG THEM DIEU KIEN NEU MA PHONG = NULL THI TIEN HANH GROUP THEO MA DON VI

    DECLARE @TMP_GROUP_GENERAL_3_5 TABLE(GROUP_GENERAL_ID VARCHAR(15), SOLUONG INT);

    INSERT INTO @TMP_GROUP_GENERAL_3_5

    SELECT

        B.GROUP_GENERAL_ID, COUNT(B.GROUP_GENERAL_ID) AS SL

      FROM dbo.ASS_MASTER A

           INNER JOIN dbo.ASS_GROUP_GENERAL B ON B.GROUP_ID=A.GROUP_ID

           LEFT JOIN dbo.CM_DEPARTMENT CT ON A.DEPT_ID=CT.DEP_ID

		   LEFT JOIN ASS_STATUS ST ON ST.STATUS_ID = A.ASS_STATUS

     WHERE 1= 1 AND ((@DEP_CODE IS NOT NULL AND CT.DEP_CODE=@DEP_CODE AND A.BRANCH_ID = @BRANCH_ID) OR @DEP_CODE IS NULL AND A.BRANCH_ID = @BRANCH_ID)

	 AND DATEDIFF(YEAR, A.USE_DATE, GETDATE()) >3  AND DATEDIFF(YEAR, A.USE_DATE, GETDATE())<=5

	 AND A.AMORT_STATUS NOT IN ( 'DTL', 'VNM' )

	AND ((@DEP_CODE IS NULL 

	AND ((@p_Level = 'ALL' AND   A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_branch))

 	OR (@p_Level = 'UNIT'  AND  A.BRANCH_ID = @p_BRANCH_ID) OR @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)

 	--AND ((@p_Level = 'ALL'  AND  A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_login))

 	--OR (@p_Level = 'UNIT' AND  A.BRANCH_ID = @p_BRANCH_LOGIN) OR @p_BRANCH_LOGIN = '' OR @p_BRANCH_LOGIN IS NULL 

 	--OR @p_BRANCH_ID IS NOT NULL OR @p_BRANCH_ID <> '')

	) OR A.BRANCH_ID = @BRANCH_ID)

    GROUP BY B.GROUP_GENERAL_ID;

    DECLARE @SL_LAPTOP_3_5 INT=0;

    DECLARE @SL_PC_3_5 INT=0;

    DECLARE @SL_PC_RAP_3_5 INT=0;

    DECLARE @SL_MAYIN_3_5 INT=0;

    DECLARE @SL_MAY_SCAN_3_5 INT=0;

    DECLARE @SL_UPS_3_5 INT=0;

    SELECT

        @SL_LAPTOP_3_5=SOLUONG

      FROM @TMP_GROUP_GENERAL_3_5

     WHERE GROUP_GENERAL_ID='ASGG00000000001';

    SELECT

        @SL_PC_3_5=SOLUONG

      FROM @TMP_GROUP_GENERAL_3_5

     WHERE GROUP_GENERAL_ID='ASGG00000000002';

    SELECT

        @SL_PC_RAP_3_5=SOLUONG

      FROM @TMP_GROUP_GENERAL_3_5

     WHERE GROUP_GENERAL_ID='ASGG00000000004';

    SELECT

        @SL_MAYIN_3_5=SOLUONG

      FROM @TMP_GROUP_GENERAL_3_5

     WHERE GROUP_GENERAL_ID='ASGG00000000003';

    SELECT

        @SL_MAY_SCAN_3_5=SOLUONG

      FROM @TMP_GROUP_GENERAL_3_5

     WHERE GROUP_GENERAL_ID='ASGG00000000005';

    SELECT

        @SL_UPS_3_5=SOLUONG

      FROM @TMP_GROUP_GENERAL_3_5

     WHERE GROUP_GENERAL_ID='ASGG00000000006';

    DELETE FROM @TMP_GROUP_GENERAL_3_5;

    --   >5 - 7  năm - LUCTV 28082019 BO SUNG THEM DIEU KIEN NEU MA PHONG = NULL THI TIEN HANH GROUP THEO MA DON VI

    DECLARE @TMP_GROUP_GENERAL_5 TABLE(GROUP_GENERAL_ID VARCHAR(15), SOLUONG INT);

    INSERT INTO @TMP_GROUP_GENERAL_5

    SELECT

        B.GROUP_GENERAL_ID, COUNT(B.GROUP_GENERAL_ID) AS SL

      FROM dbo.ASS_MASTER A

           INNER JOIN dbo.ASS_GROUP_GENERAL B ON B.GROUP_ID=A.GROUP_ID

           LEFT JOIN dbo.CM_DEPARTMENT CT ON A.DEPT_ID=CT.DEP_ID

		   LEFT JOIN ASS_STATUS ST ON ST.STATUS_ID = A.ASS_STATUS

     WHERE 1= 1 AND ((@DEP_CODE IS NOT NULL AND CT.DEP_CODE=@DEP_CODE AND A.BRANCH_ID = @BRANCH_ID) OR @DEP_CODE IS NULL AND A.BRANCH_ID = @BRANCH_ID)

	 AND DATEDIFF(YEAR, A.USE_DATE, GETDATE())>5 AND  DATEDIFF(YEAR, A.USE_DATE, GETDATE()) <=7

     --AND A.AMORTIZED_MONTH>(5 * 12)

	 AND A.AMORT_STATUS NOT IN ( 'DTL', 'VNM' )

		AND ((@DEP_CODE IS NULL 

	AND ((@p_Level = 'ALL'  AND  A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_branch))

 	OR (@p_Level = 'UNIT'   AND  A.BRANCH_ID = @p_BRANCH_ID) OR @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)

 	--AND ((@p_Level = 'ALL'  AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_login))

 	--OR (@p_Level = 'UNIT'   AND A.BRANCH_ID = @p_BRANCH_LOGIN) OR @p_BRANCH_LOGIN = '' OR @p_BRANCH_LOGIN IS NULL 

 	--OR @p_BRANCH_ID IS NOT NULL OR @p_BRANCH_ID <> '')  

	) OR A.BRANCH_ID = @BRANCH_ID)

     GROUP BY B.GROUP_GENERAL_ID;

    DECLARE @SL_LAPTOP_5 INT=0;

    DECLARE @SL_PC_5 INT=0;

    DECLARE @SL_PC_RAP_5 INT=0;

    DECLARE @SL_MAYIN_5 INT=0;

    DECLARE @SL_MAY_SCAN_5 INT=0;

    DECLARE @SL_UPS_5 INT=0;

    SELECT

        @SL_LAPTOP_5=SOLUONG

      FROM @TMP_GROUP_GENERAL_5

     WHERE GROUP_GENERAL_ID='ASGG00000000001';

    SELECT

        @SL_PC_5=SOLUONG

      FROM @TMP_GROUP_GENERAL_5

     WHERE GROUP_GENERAL_ID='ASGG00000000002';

    SELECT

        @SL_PC_RAP_5=SOLUONG

      FROM @TMP_GROUP_GENERAL_5

     WHERE GROUP_GENERAL_ID='ASGG00000000004';

    SELECT

        @SL_MAYIN_5=SOLUONG

      FROM @TMP_GROUP_GENERAL_5

     WHERE GROUP_GENERAL_ID='ASGG00000000003';

    SELECT

        @SL_MAY_SCAN_5=SOLUONG

      FROM @TMP_GROUP_GENERAL_5

     WHERE GROUP_GENERAL_ID='ASGG00000000005';

    SELECT

        @SL_UPS_5=SOLUONG

      FROM @TMP_GROUP_GENERAL_5

     WHERE GROUP_GENERAL_ID='ASGG00000000006';

	 DELETE FROM @TMP_GROUP_GENERAL_5;

	 -- luctv 15 10 2019 bo sung cac so lieu >7 nam

	 -->7 năm - LUCTV 28082019 BO SUNG THEM DIEU KIEN NEU MA PHONG = NULL THI TIEN HANH GROUP THEO MA DON VI

		DECLARE @TMP_GROUP_GENERAL_7 TABLE(GROUP_GENERAL_ID VARCHAR(15), SOLUONG INT);

		INSERT INTO @TMP_GROUP_GENERAL_7

		SELECT

			B.GROUP_GENERAL_ID, COUNT(B.GROUP_GENERAL_ID) AS SL

		  FROM dbo.ASS_MASTER A

			   INNER JOIN dbo.ASS_GROUP_GENERAL B ON B.GROUP_ID=A.GROUP_ID

			   LEFT JOIN dbo.CM_DEPARTMENT CT ON A.DEPT_ID=CT.DEP_ID

			   LEFT JOIN ASS_STATUS ST ON ST.STATUS_ID = A.ASS_STATUS

		WHERE 1= 1 AND ((@DEP_CODE IS NOT NULL AND CT.DEP_CODE=@DEP_CODE AND A.BRANCH_ID = @BRANCH_ID) OR @DEP_CODE IS NULL AND A.BRANCH_ID = @BRANCH_ID)

	  AND DATEDIFF(YEAR, A.USE_DATE, GETDATE()) >7

     --AND A.AMORTIZED_MONTH>(5 * 12)

	 AND A.AMORT_STATUS NOT IN ( 'DTL', 'VNM' )

		AND ((@DEP_CODE IS NULL 

	AND ((@p_Level = 'ALL'  AND  A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_branch))

 	OR (@p_Level = 'UNIT'   AND  A.BRANCH_ID = @p_BRANCH_ID) OR @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)

 	--AND ((@p_Level = 'ALL'  AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_login))

 	--OR (@p_Level = 'UNIT'   AND A.BRANCH_ID = @p_BRANCH_LOGIN) OR @p_BRANCH_LOGIN = '' OR @p_BRANCH_LOGIN IS NULL 

 	--OR @p_BRANCH_ID IS NOT NULL OR @p_BRANCH_ID <> '')  

	) OR A.BRANCH_ID = @BRANCH_ID)

     GROUP BY B.GROUP_GENERAL_ID;

	DECLARE @SL_LAPTOP_7 INT=0;

    DECLARE @SL_PC_7 INT=0;

    DECLARE @SL_PC_RAP_7 INT=0;

    DECLARE @SL_MAYIN_7 INT=0;

    DECLARE @SL_MAY_SCAN_7 INT=0;

    DECLARE @SL_UPS_7 INT=0;

    SELECT

        @SL_LAPTOP_7=SOLUONG

      FROM @TMP_GROUP_GENERAL_7

     WHERE GROUP_GENERAL_ID='ASGG00000000001';

    SELECT

        @SL_PC_7=SOLUONG

      FROM @TMP_GROUP_GENERAL_7

     WHERE GROUP_GENERAL_ID='ASGG00000000002';

    SELECT

        @SL_PC_RAP_7=SOLUONG

      FROM @TMP_GROUP_GENERAL_7

     WHERE GROUP_GENERAL_ID='ASGG00000000004';

    SELECT

        @SL_MAYIN_7=SOLUONG

      FROM @TMP_GROUP_GENERAL_7

     WHERE GROUP_GENERAL_ID='ASGG00000000003';

    SELECT

        @SL_MAY_SCAN_7=SOLUONG

      FROM @TMP_GROUP_GENERAL_7

     WHERE GROUP_GENERAL_ID='ASGG00000000005';

    SELECT

        @SL_UPS_7=SOLUONG

      FROM @TMP_GROUP_GENERAL_7

     WHERE GROUP_GENERAL_ID='ASGG00000000006';

	 --end luctv

    DELETE FROM @TMP_GROUP_GENERAL_7;

    SET @SL_LAPTOP=@SL_LAPTOP_0_3+@SL_LAPTOP_3_5+@SL_LAPTOP_5+@SL_LAPTOP_7;

    SET @SL_PC=@SL_PC_0_3+@SL_PC_3_5+@SL_PC_5+@SL_PC_7;

    SET @SL_PC_RAP=@SL_PC_RAP_0_3+@SL_PC_RAP_3_5+@SL_PC_RAP_5+@SL_PC_7;

    SET @SL_MAYIN=@SL_MAYIN_0_3+@SL_MAYIN_3_5+@SL_MAYIN_5+@SL_MAYIN_7;

    SET @SL_MAY_SCAN=@SL_MAY_SCAN_0_3+@SL_MAY_SCAN_3_5+@SL_MAY_SCAN_5+@SL_MAY_SCAN_7;

    SET @SL_UPS=@SL_UPS_0_3+@SL_UPS_3_5+@SL_UPS_5+@SL_UPS_7;

    INSERT INTO @returnTable(DEP_ID, DEP_CODE, DEP_LV1,DEP_LV2, BRANCH_CODE, DEP_NAME, QTY, SL_LAPTOP, SL_PC, SL_PC_RAP, SL_MAYIN, SL_MAY_SCAN, SL_UPS,

    SL_LAPTOP_0_3, SL_PC_0_3, SL_PC_RAP_0_3, SL_MAYIN_0_3, SL_MAY_SCAN_0_3, SL_UPS_0_3, SL_LAPTOP_3_5, SL_PC_3_5, SL_PC_RAP_3_5,

    SL_MAYIN_3_5, SL_MAY_SCAN_3_5, SL_UPS_3_5, SL_LAPTOP_5_, SL_PC_5_, SL_PC_RAP_5_, SL_MAYIN_5_, SL_MAY_SCAN_5_, SL_UPS_5_,

	SL_LAPTOP_7_, SL_PC_7_, SL_PC_RAP_7_, SL_MAYIN_7_, SL_MAY_SCAN_7_, SL_UPS_7_)

    VALUES(@DEP_CODE, ISNULL(@DEP_CODE,@BRANCH_CODE),ISNULL(@DEP_CODE,@BRANCH_CODE),@DEP_CODE,ISNULL(@BRANCH_CODE_FAT,@BRANCH_CODE), @DEP_NAME, 0, -- QTY - int

    @SL_LAPTOP, -- SL_LAPTOP - int

    @SL_PC, @SL_PC_RAP, @SL_MAYIN, @SL_MAY_SCAN, @SL_UPS, @SL_LAPTOP_0_3, @SL_PC_0_3, @SL_PC_RAP_0_3, @SL_MAYIN_0_3, @SL_MAY_SCAN_0_3,

    @SL_UPS_0_3, @SL_LAPTOP_3_5, @SL_PC_3_5, @SL_PC_RAP_3_5, @SL_MAYIN_3_5, @SL_MAY_SCAN_3_5, @SL_UPS_3_5, @SL_LAPTOP_5, @SL_PC_5,

    @SL_PC_RAP_5, @SL_MAYIN_5, @SL_MAY_SCAN_5, @SL_UPS_5,

	@SL_LAPTOP_7, @SL_PC_7,@SL_PC_RAP_7, @SL_MAYIN_7, @SL_MAY_SCAN_7, @SL_UPS_7);

	-- LUCTV 28082019 BO SUNG THEM DIEU KIEN NEU MA PHONG = NULL THI XOA 1 DONG WHERE THEO BRANCH_ID

	IF(@DEP_CODE IS NOT NULL)

	BEGIN

		DELETE FROM @TMP WHERE ID=@DEP_CODE;

	END

	ELSE

	BEGIN

	 DELETE FROM @TMP WHERE BRANCH_ID = @BRANCH_ID;

	END

END;

SELECT * FROM @returnTable ORDER BY BRANCH_CODE DESC,DEP_CODE DESC;