ALTER FUNCTION dbo.fn_TON_KHO(
	@sp_Todate VARCHAR(20) = NULL,
	@sp_DVSD VARCHAR(15) = NULL,
	@sp_CCLD_Type varchar(15) = NULL,
	@sp_Supplier varchar(15) = NULL,
	@sp_BRANCH_ID VARCHAR(15) = NULL,
	@sp_BRANCH_LOGIN VARCHAR(15) = NULL,
	@sp_LEVEL VARCHAR(10) = 'ALL',
	@sp_PriceFrom varchar(15) = Null,
	@sp_PriceTo varchar(15) = null,
	@sp_Dep_ID VARCHAR(15)= NULL
	)

RETURNS  @returnTable TABLE 
                     (
						 STT DECIMAL(18,0),
						 ASSET_ID VARCHAR(15),
						 MA_CCLD VARCHAR(150), 
						 TEN_CCLD nvarchar(200), 
						 NGAY_NHAPKHO DATETIME,
					     NGAY_HACHTOAN DATETIME,
						 GIATRI NUMERIC(18,0),
						 SERIAL_NO nvarchar(1000),
						  GHI_CHU nvarchar(1000),
						 NHOM_CCLD_1 nvarchar(200), 
						 NHOM_CCLD_2 nvarchar(200), 
						 NHOM_CCLD_3 nvarchar(200), 
						 NHOM_CCLD_4 nvarchar(200), 
						 MA_NHOM_CCLD_1 VARCHAR(15), 
						 MA_NHOM_CCLD_2 VARCHAR(15),
             MA_NHOM_CCLD_3 VARCHAR(15),
						 MA_CHINHANH VARCHAR(15),
						 TEN_CHINHANH nvarchar(200),
						 BUY_DATE_KT DATETIME,
						  MA_CHI_NHANH_KHO VARCHAR(15),
						 TEN_CHI_NHANH_KHO nvarchar(200),
						 TEN_DON_VI_SD nvarchar(200),
             UNIT_NAME_CCLD_3 NVARCHAR(100)
					 )
AS
BEGIN
declare @tmp table(BRANCH_ID varchar(15))
insert into @tmp  SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@sp_BRANCH_ID)
declare @tmp_login table(BRANCH_ID varchar(15))
insert into @tmp_login  SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@sp_BRANCH_LOGIN)
--THIEUVQ 07/1/2014 THAY DOI LAY DS TS TON KHO
--declare @tmp table(MAKER_ID varchar(15))
--insert into @tmp  SELECT TLNANME FROM TL_USER WHERE TLSUBBRID = @sp_BRANCH_ID
--declare @tmp_login table(MAKER_ID varchar(15))
--insert into @tmp_login SELECT TLNANME FROM TL_USER WHERE TLSUBBRID = @sp_BRANCH_LOGIN

declare @tmp_ALL table(MAKER_ID varchar(100))
insert into @tmp_ALL  SELECT TLNANME FROM TL_USER WHERE TLSUBBRID IN (SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@sp_BRANCH_ID))
declare @tmp_login_ALL table(MAKER_ID varchar(100))
insert into @tmp_login_ALL SELECT TLNANME FROM TL_USER WHERE TLSUBBRID IN (SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@sp_BRANCH_LOGIN))
INSERT INTO @returnTable
	SELECT 
	Row_number() over(order by A.ASSET_ID) AS STT,A.ASSET_ID,
	A.ASSET_CODE MA_CCLD,
	A.ASSET_NAME TEN_CCLD,
	A.BUY_DATE NGAY_NHAPKHO,
	A.BUY_DATE_KT NGAY_HACHTOAN,
	A.BUY_PRICE GIATRI,
	A.ASSET_SERIAL_NO SERIAL_NO,
	--''  MA_CHINHANH,
	A.NOTES GHI_CHU,
	[dbo].[FN_GET_GROUPNAME](A.GROUP_ID,'1') AS NHOM_CCLD_1,
	[dbo].[FN_GET_GROUPNAME](A.GROUP_ID,'2') AS NHOM_CCLD_2,
	[dbo].[FN_GET_GROUPNAME](A.GROUP_ID,'3') AS NHOM_CCLD_3,
	[dbo].[FN_GET_GROUPNAME](A.GROUP_ID,'4') AS NHOM_CCLD_4,
	
	[dbo].[FN_GET_GROUPCODE](A.GROUP_ID,'1') AS MA_NHOM_CCLD_1,
	[dbo].[FN_GET_GROUPCODE](A.GROUP_ID,'2') AS MA_NHOM_CCLD_2,
  [dbo].[FN_GET_GROUPCODE](A.GROUP_ID,'3') AS MA_NHOM_CCLD_3,
	CASE WHEN D.BRANCH_CODE IS NULL  THEN
	--(SELECT TOP 1 DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID = A.DEPT_ID  )
	C.BRANCH_CODE
	WHEN C.BRANCH_TYPE = 'CN' THEN C.BRANCH_CODE
		 ELSE 
	D.BRANCH_CODE END AS MA_CHINHANH,
	CASE WHEN D.BRANCH_CODE IS NULL --OR (C.BRANCH_TYPE <> 'PGD' AND C.IS_POTENTIAL = 'N') 
	THEN
	--(SELECT TOP 1 DEP_NAME FROM CM_DEPARTMENT WHERE DEP_ID = A.DEPT_ID  )
	C.BRANCH_NAME
	WHEN C.BRANCH_TYPE = 'CN' THEN C.BRANCH_NAME
		 ELSE 
	D.BRANCH_NAME END AS TEN_CHINHANH,
	A.BUY_DATE_KT,
	F.BRANCH_CODE AS MA_CHI_NHANH_KHO,
	F.BRANCH_NAME AS TEN_CHI_NHANH_KHO,
	F.BRANCH_NAME AS TEN_DON_VI_SD,
  G.UNIT_NAME AS UNIT_NAME_CCLD_3
	FROM ASS_MASTER  A
	LEFT JOIN ASS_GROUP B ON A.GROUP_ID = B.GROUP_ID
  LEFT JOIN CM_UNIT G ON B.UNIT = G.UNIT_ID
	--LEFT JOIN TL_USER MK ON MK.TLNANME = A.MAKER_ID
	--LEFT JOIN CM_BRANCH C ON MK.TLSUBBRID = C.BRANCH_ID
	LEFT JOIN CM_BRANCH C ON A.BRANCH_CREATE = C.BRANCH_ID
	--LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
	LEFT JOIN CM_BRANCH D ON D.BRANCH_ID = C.FATHER_ID
	LEFT JOIN CM_EMPLOYEE E ON A.EMP_ID = E.EMP_ID	
	LEFT JOIN CM_BRANCH F ON A.BRANCH_CREATE=F.BRANCH_ID
	LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID = BR.BRANCH_ID

	WHERE  A.RECORD_STATUS = '1' AND A.AUTH_STATUS = 'A'
	AND (A.TYPE_ID =  'CCLD')
	AND A.ENTRY_BOOKED = 'Y'
    
	AND ( A.BUY_DATE_KT <= CONVERT(datetime, @sp_Todate, 103) OR @sp_Todate IS NULL OR @sp_Todate = '')
	AND ( A.USE_DATE_KT > CONVERT(datetime, @sp_Todate, 103) OR @sp_Todate IS NULL OR @sp_Todate = '' 
	OR (A.USE_DATE_KT IS NULL AND A.AMORT_STATUS <> 'DTL'))
	
	---- NGAY NHAP MOI 
	--AND (A.AMORT_STATUS  = 'VNM' OR A.USE_STATUS = 'TH') --THIEUVQ 23062015
	--AND	((A.BUY_DATE_KT <= CONVERT(datetime, @sp_Todate, 103) OR @sp_Todate IS NULL OR @sp_Todate = '')
	--OR (A.BUY_DATE_KT IS NULL AND A.BUY_DATE<= CONVERT(datetime, @sp_Todate, 103) OR @sp_Todate IS NULL OR @sp_Todate = ''))
	
	--AND A.ENTRY_BOOKED = 'Y'
	-- DVSD
	--AND (A.BRANCH_ID = @sp_DVSD OR @sp_DVSD IS NULL OR @sp_DVSD = '')
	-- NHOM TAI SAN
	AND (A.GROUP_ID = @sp_CCLD_Type OR @sp_CCLD_Type IS NULL OR @sp_CCLD_Type = '')
	-- NHA CUNG CAP
	AND (A.SUP_ID = @sp_Supplier OR @sp_Supplier IS NULL OR @sp_Supplier = '')
	-- NGUYEN GIA
	AND (A.BUY_PRICE >= CONVERT(decimal(18), @sp_PriceFrom) OR @sp_PriceFrom IS NULL OR @sp_PriceFrom = '0' )
	AND (A.BUY_PRICE <= CONVERT(decimal(18), @sp_PriceTo)  OR @sp_PriceTo IS NULL OR @sp_PriceTo = '0' )
	
	-- PHONG BAN
	AND (A.DEPT_ID = @sp_Dep_ID OR @sp_Dep_ID IS NULL OR @sp_Dep_ID = '')
	
	-- CCLD TON KHO
	--AND (A.USE_DATE IS NULL OR A.USE_DATE = '')
	--AND (A.AMORT_STATUS  <> 'DTL' )

	--AND ( 
	--((A.AMORT_STATUS = 'VNM' AND ((@sp_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp))
	--							OR (@sp_LEVEL = 'UNIT' AND A.BRANCH_ID = @sp_BRANCH_ID)))
	--	OR
	--(A.AMORT_STATUS = 'VNM' AND ((@sp_LEVEL = 'ALL' AND MK.TLSUBBRID IN (SELECT BRANCH_ID from @tmp))
	--							OR (@sp_LEVEL = 'UNIT' AND MK.TLSUBBRID = @sp_BRANCH_ID)))) OR @sp_BRANCH_ID IS NULL OR @sp_BRANCH_ID = ''
	--)			
	--AND ( 
	--((A.AMORT_STATUS = 'VNM' AND ((@sp_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp))
	--							OR (@sp_LEVEL = 'UNIT' AND A.BRANCH_ID = @sp_BRANCH_LOGIN)))
	--	OR
	--(A.AMORT_STATUS = 'VNM' AND ((@sp_LEVEL = 'ALL' AND MK.TLSUBBRID IN (SELECT BRANCH_ID from @tmp))
	--							OR (@sp_LEVEL = 'UNIT' AND MK.TLSUBBRID = @sp_BRANCH_LOGIN)))) OR @sp_BRANCH_LOGIN IS NULL OR @sp_BRANCH_LOGIN = ''
	--)

	--AND (A.AMORT_STATUS  = 'VNM' )
	--AND ((@sp_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp))
	--OR (@sp_LEVEL = 'UNIT' AND A.BRANCH_ID = @sp_BRANCH_ID) OR @sp_BRANCH_ID = '' OR @sp_BRANCH_ID IS NULL)
	--AND ((@sp_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_login))
	--OR (@sp_LEVEL = 'UNIT' AND A.BRANCH_ID = @sp_BRANCH_LOGIN) OR @sp_BRANCH_LOGIN = '' OR @sp_BRANCH_LOGIN IS NULL 
	--OR @sp_BRANCH_ID IS NOT NULL OR @sp_BRANCH_ID <> '')
		AND ((@sp_LEVEL = 'ALL' AND A.BRANCH_CREATE IN (SELECT BRANCH_ID from @tmp))
	OR (@sp_LEVEL = 'UNIT' AND A.BRANCH_CREATE = @sp_BRANCH_ID) OR @sp_BRANCH_ID = '' OR @sp_BRANCH_ID IS NULL)
	AND ((@sp_LEVEL = 'ALL' AND A.BRANCH_CREATE IN (SELECT BRANCH_ID from @tmp_login))
	OR (@sp_LEVEL = 'UNIT' AND A.BRANCH_CREATE = @sp_BRANCH_LOGIN) OR @sp_BRANCH_LOGIN = '' OR @sp_BRANCH_LOGIN IS NULL 
	OR @sp_BRANCH_ID IS NOT NULL OR @sp_BRANCH_ID <> '')

	--AND ((@sp_LEVEL = 'ALL' AND A.MAKER_ID IN (SELECT MAKER_ID from @tmp_ALL))
	--OR (@sp_LEVEL = 'UNIT' AND A.MAKER_ID IN (SELECT MAKER_ID from @tmp)) OR @sp_BRANCH_ID = '' OR @sp_BRANCH_ID IS NULL)
	--AND ((@sp_LEVEL = 'ALL' AND A.MAKER_ID IN (SELECT MAKER_ID from @tmp_login_ALL))
	--OR (@sp_LEVEL = 'UNIT' AND A.MAKER_ID IN (SELECT MAKER_ID from @tmp_login_ALL)) OR @sp_BRANCH_LOGIN = '' OR @sp_BRANCH_LOGIN IS NULL )
	ORDER BY STT
	RETURN;
END




GO

ALTER FUNCTION dbo.fn_XUAT_KHO(
	@sp_Fromdate VARCHAR(20) = NULL,
	@sp_Todate VARCHAR(20) = NULL,
	@sp_DVSD VARCHAR(15) = NULL,
	@sp_NSD VARCHAR(15) = NULL,
	@sp_CCLD_Type varchar(15) = NULL,
	@sp_Supplier varchar(15) = NULL,
	@sp_BRANCH_ID VARCHAR(15) = NULL,
	@sp_BRANCH_LOGIN VARCHAR(15) = NULL,
	@sp_LEVEL VARCHAR(10) = 'ALL',
	@sp_PriceFrom varchar(15) = Null,
	@sp_PriceTo varchar(15) = null,
	@sp_Dep_ID VARCHAR(15)= NULL
	)

RETURNS  @returnTable TABLE 
                     (
						 STT DECIMAL(18,0),
						 MA_CCLD VARCHAR(15), 
						 TEN_CCLD nvarchar(200), 
						 NGAYNHAP_KHO DATETIME,
						 NGAYXUAT_KHO  DATETIME,
					     NGAY_HACH_TOAN DATETIME,
						 GIATRI NUMERIC(18,0),
						 DVSD VARCHAR(15),
						 TEN_CHINHANH nvarchar(200),
						 MA_CHINHANH nvarchar(200), 
						 GHI_CHU nvarchar(1000),
						 NHOM_CCLD_1 nvarchar(200), 
						 NHOM_CCLD_2 nvarchar(200), 
						 NHOM_CCLD_3 nvarchar(200), 
						 NHOM_CCLD_4 nvarchar(200), 
						 MA_NHOM_CCLD_1 VARCHAR(15), 
						 MA_NHOM_CCLD_2 VARCHAR(15), 
             MA_NHOM_CCLD_3 VARCHAR(15), 
						 MA_DVSD VARCHAR(15), 
						 SERIAL_NO nvarchar(1000),
						 BUY_DATE_KT DATETIME,
						  MA_CHI_NHANH_KHO VARCHAR(15),
						 TEN_CHI_NHANH_KHO nvarchar(200),
             UNIT_NAME_CCLD_3 NVARCHAR(100)
					 )
AS
BEGIN
declare @tmp table(BRANCH_ID varchar(15))
insert into @tmp  SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@sp_BRANCH_ID)
declare @tmp_login table(BRANCH_ID varchar(15))
insert into @tmp_login  SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@sp_BRANCH_LOGIN)
	INSERT INTO @returnTable
	SELECT 
	Row_number() over(order by A.ASSET_ID) AS STT,
	A.ASSET_CODE MA_CCLD,
	A.ASSET_NAME TEN_CCLD,
	A.BUY_DATE NGAYNHAP_KHO,
	A.USE_DATE NGAYXUAT_KHO,
	A.USE_DATE_KT NGAY_HACH_TOAN,
	A.BUY_PRICE GIATRI,
	CASE WHEN D.BRANCH_CODE IS NULL  THEN
	(SELECT TOP 1 DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID = A.DEPT_ID  )
	WHEN C.BRANCH_TYPE = 'CN' THEN C.BRANCH_CODE
		 ELSE 
	D.BRANCH_CODE END AS  DVSD,
	CASE WHEN D.BRANCH_CODE IS NULL --OR (C.BRANCH_TYPE <> 'PGD' AND C.IS_POTENTIAL = 'N') 
	THEN
	(SELECT TOP 1 DEP_NAME FROM CM_DEPARTMENT WHERE DEP_ID = A.DEPT_ID  )
	WHEN C.BRANCH_TYPE = 'CN' THEN C.BRANCH_NAME
		 ELSE 
	D.BRANCH_NAME END AS TEN_CHINHANH,
--	'' TEN_CHINHANH,
	C.BRANCH_NAME MA_CHINHANH,
	''  GHI_CHU,
	[dbo].[FN_GET_GROUPNAME](A.GROUP_ID,'1') AS NHOM_CCLD_1,
	[dbo].[FN_GET_GROUPNAME](A.GROUP_ID,'2') AS NHOM_CCLD_2,
	[dbo].[FN_GET_GROUPNAME](A.GROUP_ID,'3') AS NHOM_CCLD_3,
	[dbo].[FN_GET_GROUPNAME](A.GROUP_ID,'4') AS NHOM_CCLD_4,
	[dbo].[FN_GET_GROUPCODE](A.GROUP_ID,'1') AS MA_NHOM_CCLD_1,
	[dbo].[FN_GET_GROUPCODE](A.GROUP_ID,'2') AS MA_NHOM_CCLD_2,
  [dbo].[FN_GET_GROUPCODE](A.GROUP_ID,'3') AS MA_NHOM_CCLD_3,
	--'' MA_CHINHANH,
	C.BRANCH_CODE MA_DVSD,
	A.ASSET_SERIAL_NO AS SERIAL_NO,
	A.BUY_DATE_KT,
	F.BRANCH_CODE AS MA_CHI_NHANH_KHO,
	F.BRANCH_NAME AS TEN_CHI_NHANH_KHO,
  G.UNIT_NAME AS UNIT_NAME_CCLD_3
	FROM ASS_MASTER  A
	INNER JOIN ASS_GROUP B ON A.GROUP_ID = B.GROUP_ID
  LEFT JOIN CM_UNIT G ON B.UNIT = G.UNIT_ID
	LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
	LEFT JOIN CM_BRANCH D ON D.BRANCH_ID = C.FATHER_ID
	LEFT JOIN CM_EMPLOYEE E ON A.EMP_ID = E.EMP_ID
	LEFT JOIN CM_BRANCH F ON A.BRANCH_CREATE=F.BRANCH_ID
	WHERE  A.RECORD_STATUS = '1' AND A.AUTH_STATUS = 'A'
	AND	(A.USE_DATE_KT <= CONVERT(datetime, @sp_Todate, 103) OR @sp_Todate IS NULL OR @sp_Todate = '')
	AND	(A.USE_DATE_KT >= CONVERT(datetime, @sp_Fromdate, 103) OR @sp_Fromdate IS NULL OR @sp_Fromdate = '')
	
	AND A.ENTRY_BOOKED = 'Y'
	-- DVSD
	AND (A.BRANCH_ID = @sp_DVSD OR @sp_DVSD IS NULL OR @sp_DVSD = '')
	-- NHOM TAI SAN
	AND (A.GROUP_ID = @sp_CCLD_Type OR @sp_CCLD_Type IS NULL OR @sp_CCLD_Type = '')
	-- NHA CUNG CAP
	AND (A.SUP_ID = @sp_Supplier OR @sp_Supplier IS NULL OR @sp_Supplier = '')
	-- NGUYEN GIA
	AND (A.BUY_PRICE >= CONVERT(decimal(18), @sp_PriceFrom) OR @sp_PriceFrom IS NULL OR @sp_PriceFrom = '0' )
	AND (A.BUY_PRICE <= CONVERT(decimal(18), @sp_PriceTo)  OR @sp_PriceTo IS NULL OR @sp_PriceTo = '0' )
	-- PHONG BAN
	AND A.AMORT_STATUS <> 'VNM'
	AND (A.DEPT_ID = @sp_Dep_ID OR @sp_Dep_ID IS NULL OR @sp_Dep_ID = '')
	AND (A.TYPE_ID = 'CCLD')
	AND ((@sp_LEVEL = 'ALL' AND A.BRANCH_CREATE IN (SELECT BRANCH_ID from @tmp))
	OR (@sp_LEVEL = 'UNIT' AND A.BRANCH_CREATE = @sp_BRANCH_ID) OR @sp_BRANCH_ID = '' OR @sp_BRANCH_ID IS NULL)
	AND ((@sp_LEVEL = 'ALL' AND A.BRANCH_CREATE IN (SELECT BRANCH_ID from @tmp_login))
	OR (@sp_LEVEL = 'UNIT' AND A.BRANCH_CREATE = @sp_BRANCH_LOGIN) OR @sp_BRANCH_LOGIN = '' OR @sp_BRANCH_LOGIN IS NULL 
	OR @sp_BRANCH_ID IS NOT NULL OR @sp_BRANCH_ID <> '')
	ORDER BY STT
	RETURN;
END





GO

ALTER FUNCTION dbo.fn_NHAP_KHO(
	@sp_Fromdate VARCHAR(20) = NULL,
	@sp_Todate VARCHAR(20) = NULL,
	@sp_DVSD VARCHAR(15) = NULL,
	@sp_CCLD_Type varchar(15) = NULL,
	@sp_Supplier varchar(15) = NULL,
	@sp_BRANCH_ID VARCHAR(15) = NULL,
	@sp_BRANCH_LOGIN VARCHAR(15) = NULL,
	@sp_LEVEL VARCHAR(10) = 'ALL',
	@sp_PriceFrom varchar(15) = Null,
	@sp_PriceTo varchar(15) = null,
	@sp_Dep_ID VARCHAR(15)= NULL)

RETURNS  @returnTable TABLE 
                     (
						 STT DECIMAL(18,0),
						 MA_CCLD VARCHAR(15), 
						 TEN_CCLD nvarchar(200), 
						 NGAY_NHAPKHO DATETIME,
					     NGAY_HACHTOAN DATETIME,
						 GIATRI NUMERIC(18,0),
						 MA_CHI_NHANH VARCHAR(15),
						  TEN_CHINHANH nvarchar(200), 
						 GHI_CHU nvarchar(1000),
						 NHOM_CCLD_1 nvarchar(200), 
						 NHOM_CCLD_2 nvarchar(200), 
						 NHOM_CCLD_3 nvarchar(200), 
						 NHOM_CCLD_4 nvarchar(200), 
						 MA_NHOM_CCLD_1 VARCHAR(15), 
						 MA_NHOM_CCLD_2 VARCHAR(15), 
             MA_NHOM_CCLD_3 VARCHAR(15), 
						 SERIAL_NO nvarchar(1000),
						 MA_CHI_NHANH_KHO VARCHAR(15),
						 TEN_CHI_NHANH_KHO nvarchar(200),
             UNIT_NAME_CCLD_3 NVARCHAR(100)
					 )
AS
BEGIN
declare @tmp table(BRANCH_ID varchar(15))
INSERT INTO @tmp  SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@sp_BRANCH_ID)
declare @tmp_login table(BRANCH_ID varchar(15))
INSERT INTO @tmp_login  SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@sp_BRANCH_LOGIN)
INSERT INTO @returnTable
	SELECT 
	Row_number() over(order by A.ASSET_ID) AS STT,
	A.ASSET_CODE MA_CCLD,
	A.ASSET_NAME TEN_CCLD,
	A.BUY_DATE NGAY_NHAPKHO,
	A.BUY_DATE_KT NGAY_HACHTOAN,
	A.BUY_PRICE GIATRI,
	CASE WHEN D.BRANCH_CODE IS NULL  THEN
	C.BRANCH_CODE
	WHEN C.BRANCH_TYPE = 'CN' THEN C.BRANCH_CODE
		 ELSE 
	D.BRANCH_CODE END AS MA_CHI_NHANH,
	CASE WHEN D.BRANCH_CODE IS NULL --OR (C.BRANCH_TYPE <> 'PGD' AND C.IS_POTENTIAL = 'N') 
	THEN
	--(SELECT TOP 1 DEP_NAME FROM CM_DEPARTMENT WHERE DEP_ID = A.DEPT_ID  )
	C.BRANCH_NAME
	WHEN C.BRANCH_TYPE = 'CN' THEN C.BRANCH_NAME
		 ELSE 
	D.BRANCH_NAME END AS TEN_CHINHANH,
	--''  TEN_CHINHANH,
	'' GHI_CHU,
	[dbo].[FN_GET_GROUPNAME](A.GROUP_ID,'1') AS NHOM_CCLD_1,
	[dbo].[FN_GET_GROUPNAME](A.GROUP_ID,'2') AS NHOM_CCLD_2,
	[dbo].[FN_GET_GROUPNAME](A.GROUP_ID,'3') AS NHOM_CCLD_3,
	[dbo].[FN_GET_GROUPNAME](A.GROUP_ID,'4') AS NHOM_CCLD_4,
	[dbo].[FN_GET_GROUPCODE](A.GROUP_ID,'1') AS MA_NHOM_CCLD_1,
	[dbo].[FN_GET_GROUPCODE](A.GROUP_ID,'2') AS MA_NHOM_CCLD_2,
  [dbo].[FN_GET_GROUPCODE](A.GROUP_ID,'3') AS MA_NHOM_CCLD_3,
	--,'' MA_CHI_NHANH
	A.ASSET_SERIAL_NO AS SERIAL_NO,
	F.BRANCH_CODE AS MA_CHI_NHANH_KHO,
	F.BRANCH_NAME AS TEN_CHI_NHANH_KHO,
  G.UNIT_NAME AS UNIT_NAME_CCLD_3
	FROM ASS_MASTER A
	INNER JOIN ASS_GROUP B ON A.GROUP_ID = B.GROUP_ID
  LEFT JOIN CM_UNIT G ON B.UNIT = G.UNIT_ID
	--LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
	--LEFT JOIN TL_USER MK ON MK.TLNANME = A.MAKER_ID
	--LEFT JOIN CM_BRANCH C ON MK.TLSUBBRID = C.BRANCH_ID
	LEFT JOIN CM_BRANCH C ON A.BRANCH_CREATE = C.BRANCH_ID
	LEFT JOIN CM_EMPLOYEE E ON A.EMP_ID = E.EMP_ID
	LEFT JOIN CM_BRANCH D ON D.BRANCH_ID = C.FATHER_ID
	LEFT JOIN CM_BRANCH F ON A.BRANCH_CREATE=F.BRANCH_ID
	WHERE 
	 A.RECORD_STATUS = '1' AND A.AUTH_STATUS = 'A'
	AND	(A.BUY_DATE_KT <= CONVERT(datetime, @sp_Todate, 103) OR @sp_Todate IS NULL OR @sp_Todate = '')
	AND	(A.BUY_DATE_KT >= CONVERT(datetime, @sp_Fromdate, 103) OR @sp_Fromdate IS NULL OR @sp_Fromdate = '')	
	--AND A.ENTRY_BOOKED = 'Y'
	-- DVSD
	AND (A.BRANCH_ID = @sp_DVSD OR @sp_DVSD IS NULL OR @sp_DVSD = '')
	-- NHOM TAI SAN
	AND (A.GROUP_ID = @sp_CCLD_Type OR @sp_CCLD_Type IS NULL OR @sp_CCLD_Type = '')
	-- NHA CUNG CAP
	AND (A.SUP_ID = @sp_Supplier OR @sp_Supplier IS NULL OR @sp_Supplier = '')
	-- NGUYEN GIA
	AND (A.BUY_PRICE >= CONVERT(decimal(18), @sp_PriceFrom) OR @sp_PriceFrom IS NULL OR @sp_PriceFrom = '0' )
	AND (A.BUY_PRICE <= CONVERT(decimal(18), @sp_PriceTo)  OR @sp_PriceTo IS NULL OR @sp_PriceTo = '0' )
	-- PHONG BAN
	AND (A.DEPT_ID = @sp_Dep_ID OR @sp_Dep_ID IS NULL OR @sp_Dep_ID = '')
	AND (A.TYPE_ID = 'CCLD')
	-- CHUA XUAT SU DUNG
	--AND (A.USE_DATE IS NULL OR A.USE_DATE = '')
	-- KHONG BAO GOM TAI SAN DA THANH LY
	--AND (A.AMORT_STATUS  = 'VNM' )
	--AND ((@sp_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp))
	--OR (@sp_LEVEL = 'UNIT' AND A.BRANCH_ID = @sp_BRANCH_ID) OR @sp_BRANCH_ID = '' OR @sp_BRANCH_ID IS NULL)
	--AND ((@sp_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_login))
	--OR (@sp_LEVEL = 'UNIT' AND A.BRANCH_ID = @sp_BRANCH_LOGIN) OR @sp_BRANCH_LOGIN = '' OR @sp_BRANCH_LOGIN IS NULL 
	--OR @sp_BRANCH_ID IS NOT NULL OR @sp_BRANCH_ID <> '')
		AND ((@sp_LEVEL = 'ALL' AND A.BRANCH_CREATE IN (SELECT BRANCH_ID from @tmp))
	OR (@sp_LEVEL = 'UNIT' AND A.BRANCH_CREATE = @sp_BRANCH_ID) OR @sp_BRANCH_ID = '' OR @sp_BRANCH_ID IS NULL)
	AND ((@sp_LEVEL = 'ALL' AND A.BRANCH_CREATE IN (SELECT BRANCH_ID from @tmp_login))
	OR (@sp_LEVEL = 'UNIT' AND A.BRANCH_CREATE = @sp_BRANCH_LOGIN) OR @sp_BRANCH_LOGIN = '' OR @sp_BRANCH_LOGIN IS NULL 
	OR @sp_BRANCH_ID IS NOT NULL OR @sp_BRANCH_ID <> '')

	--AND ( 
	--((A.AMORT_STATUS <>'VNM' AND ((@sp_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp))
	--							OR (@sp_LEVEL = 'UNIT' AND A.BRANCH_ID = @sp_BRANCH_ID)))
	--	OR
	--(A.AMORT_STATUS = 'VNM' AND ((@sp_LEVEL = 'ALL' AND A.BRANCH_CREATE IN (SELECT BRANCH_ID from @tmp))
	--							OR (@sp_LEVEL = 'UNIT' AND A.BRANCH_CREATE = @sp_BRANCH_ID)))) OR @sp_BRANCH_ID IS NULL OR @sp_BRANCH_ID = ''
	--)			
	--AND ( 
	--((A.AMORT_STATUS <>'VNM' AND ((@sp_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp))
	--							OR (@sp_LEVEL = 'UNIT' AND A.BRANCH_ID = @sp_BRANCH_LOGIN)))
	--	OR
	--(A.AMORT_STATUS = 'VNM' AND ((@sp_LEVEL = 'ALL' AND A.BRANCH_CREATE IN (SELECT BRANCH_ID from @tmp))
	--							OR (@sp_LEVEL = 'UNIT' AND A.BRANCH_CREATE = @sp_BRANCH_LOGIN)))) OR @sp_BRANCH_LOGIN IS NULL OR @sp_BRANCH_LOGIN = ''
	--)
	ORDER BY STT
	RETURN;
END





GO

ALTER PROCEDURE dbo.rpt_CCLD_BC8_2
	@sp_Fromdate VARCHAR(20) = NULL,
	@sp_Todate VARCHAR(20) = NULL,
	@sp_DVSD VARCHAR(15) = NULL,
	@sp_CCLD_Type varchar(15) = NULL,
	@sp_Supplier varchar(15) = NULL,
	@sp_BRANCH_ID VARCHAR(15) = NULL,
	@sp_BRANCH_LOGIN VARCHAR(15) = NULL,
	@sp_LEVEL VARCHAR(10) = 'ALL',
	@sp_PriceFrom varchar(15) = Null,
	@sp_PriceTo varchar(15) = null,
	@sp_Dep_ID VARCHAR(15)= NULL

AS
BEGIN
--thieuvq 24/8/2017 - DOC DU LIEU BO QUA COMMIT TRANSACTION 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

declare @tmp table(BRANCH_ID varchar(15))
insert into @tmp  SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@sp_BRANCH_ID)
declare @tmp_login table(BRANCH_ID varchar(15))
insert into @tmp_login  SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@sp_BRANCH_LOGIN)


SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT,
--F.MA_CCLD,
--F.TEN_CCLD,
SUM(F.SL_GIATRI_TONDAUKY) AS SL_GIATRI_TONDAUKY,
SUM(F.SL_GIATRI_NHAPTRONGKY) AS SL_GIATRI_NHAPTRONGKY,
SUM(F.SL_GIATRI_XUATTRONGKY) AS SL_GIATRI_XUATTRONGKY,
SUM(F.SL_GIATRI_TONCUOIKY) AS SL_GIATRI_TONCUOIKY,
SUM(F.GIATRI_TONDAUKY) AS GIATRI_TONDAUKY, 
SUM(F.GIATRI_NHAPTRONGKY) AS GIATRI_NHAPTRONGKY,
SUM(F.GIATRI_XUATTRONGKY) AS GIATRI_XUATTRONGKY,
SUM(F.GIATRI_TONCUOIKY) AS GIATRI_TONCUOIKY,
F.MA_CHI_NHANH_KHO AS MA_CHI_NHANH,
--F.TEN_CHINHANH_KHO,
'' GHI_CHU,
F.MA_NHOM_CCLD_1,
F.MA_NHOM_CCLD_2,
F.MA_NHOM_CCLD_3,
F.NHOM_CCLD_1,
F.NHOM_CCLD_2,
F.NHOM_CCLD_3,
F.NHOM_CCLD_4,
F.UNIT_NAME_CCLD_3
FROM
(	--NHAP KHO
	SELECT 
		STT,
		MA_CCLD,
		TEN_CCLD,
		0 GIATRI_TONDAUKY, 
		GIATRI AS GIATRI_NHAPTRONGKY,
		0 GIATRI_XUATTRONGKY,
		GIATRI AS GIATRI_TONCUOIKY,

    0 AS SL_GIATRI_TONDAUKY,
    1 AS SL_GIATRI_NHAPTRONGKY,
    0 AS SL_GIATRI_XUATTRONGKY,
    1 AS SL_GIATRI_TONCUOIKY,

		MA_CHI_NHANH,
		TEN_CHINHANH,
		GHI_CHU,
		NHOM_CCLD_1,
		NHOM_CCLD_2,
		NHOM_CCLD_3,
		NHOM_CCLD_4,
		MA_NHOM_CCLD_1,
		MA_NHOM_CCLD_2,    
    MA_NHOM_CCLD_3,
		MA_CHI_NHANH_KHO,
		TEN_CHI_NHANH_KHO,
    UNIT_NAME_CCLD_3
	FROM [dbo].[fn_NHAP_KHO](@sp_Fromdate,@sp_Todate,@sp_DVSD,
	@sp_CCLD_Type,@sp_Supplier,@sp_BRANCH_ID,@sp_BRANCH_LOGIN,@sp_LEVEL,@sp_PriceFrom,@sp_PriceTo,@sp_Dep_ID)
	UNION ALL
	--XUAT KHO
	SELECT 
		STT,
		MA_CCLD,
		TEN_CCLD,
		CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN GIATRI
		ELSE 0
		END AS GIATRI_TONDAUKY,
		0 GIATRI_NHAPTRONGKY,

		GIATRI AS GIATRI_XUATTRONGKY,
		 
		CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN 0
		ELSE -GIATRI
		END AS GIATRI_TONCUOIKY,

    CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN 1	ELSE 0 END AS SL_GIATRI_TONDAUKY,
    0 SL_GIATRI_NHAPTRONGKY,
    1 AS SL_GIATRI_XUATTRONGKY,
    CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN 0 ELSE -1 END AS SL_GIATRI_TONCUOIKY,

		DVSD AS MA_CHI_NHANH,
		TEN_CHINHANH,
		GHI_CHU,
		NHOM_CCLD_1,
		NHOM_CCLD_2,
		NHOM_CCLD_3,
		NHOM_CCLD_4,
		MA_NHOM_CCLD_1,
		MA_NHOM_CCLD_2,
    MA_NHOM_CCLD_3,
		MA_CHI_NHANH_KHO,
		TEN_CHI_NHANH_KHO,
    UNIT_NAME_CCLD_3
	FROM [dbo].[fn_XUAT_KHO](@sp_Fromdate,@sp_Todate,@sp_DVSD,'',@sp_CCLD_Type,@sp_Supplier,
	@sp_BRANCH_ID,@sp_BRANCH_LOGIN,@sp_LEVEL,@sp_PriceFrom,@sp_PriceTo,@sp_Dep_ID)
	UNION ALL 
	--TON KHO
	SELECT 
		STT,
		MA_CCLD,
		TEN_CCLD,
		CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN GIATRI
		ELSE 0
		END AS GIATRI_TONDAUKY,

		0 GIATRI_NHAPTRONGKY,

		0 AS GIATRI_XUATTRONGKY,

		CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN GIATRI
		ELSE 0
		END AS GIATRI_TONCUOIKY,



    CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN 1 ELSE 0 END AS SL_GIATRI_TONDAUKY,
		0 AS SL_GIATRI_NHAPTRONGKY,
		0 AS SL_GIATRI_XUATTRONGKY,
		CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN 1 ELSE 0 END AS SL_GIATRI_TONCUOIKY,

		MA_CHINHANH,
		TEN_CHINHANH,
		GHI_CHU,
		NHOM_CCLD_1,
		NHOM_CCLD_2,
		NHOM_CCLD_3,
		NHOM_CCLD_4,
		MA_NHOM_CCLD_1,
		MA_NHOM_CCLD_2,
    MA_NHOM_CCLD_3,
		MA_CHI_NHANH_KHO,
		TEN_CHI_NHANH_KHO,
    UNIT_NAME_CCLD_3
	FROM [dbo].[fn_TON_KHO](@sp_Todate,@sp_DVSD,@sp_CCLD_Type,@sp_Supplier,
	@sp_BRANCH_ID,@sp_BRANCH_LOGIN,@sp_LEVEL,@sp_PriceFrom,@sp_PriceTo,@sp_Dep_ID)
)F
--ORDER BY F.MA_CCLD
GROUP BY --F.MA_CCLD,F.TEN_CCLD,
F.MA_NHOM_CCLD_1,F.MA_NHOM_CCLD_2,F.MA_NHOM_CCLD_3,F.MA_CHI_NHANH_KHO,F.NHOM_CCLD_1,F.NHOM_CCLD_2,NHOM_CCLD_3,NHOM_CCLD_4,F.UNIT_NAME_CCLD_3
END