ALTER PROC [dbo].[ASS_COLLECT_MULTI_MASTER_BBBG]
@COL_MULTI_MASTER_ID	varchar(15) = null
AS 
-- Thông tin chứ kí
DECLARE @SIGN_CHECKER_ID_KT VARCHAR(20),@SIGN_MAKER_ID VARCHAR(20),@SIGN_APPROVE_DT_KT DATETIME,@SIGN_CREATE_DT DATETIME,
		@SIGN_RECEVI_MAKER_ID VARCHAR(50),@SIGN_RECEVI_DT DATETIME

-- Thông tin bên giao
DECLARE @DEP_TRANSFER VARCHAR(15),  @BRANCH_TRANSFER NVARCHAR(500),@NAME_TRANSFER NVARCHAR(500), @POSITION NVARCHAR(500),@KHOI_TRANSFER NVARCHAR(500),
		@TRANSFER_MAKER_ID VARCHAR(20)

-- Thông tin bên nhận
DECLARE @DEP_RECIVE VARCHAR(15),  @BRANCH_RECIVE NVARCHAR(500),@NAME_RECIVE NVARCHAR(500), @POSITION_RECIVE NVARCHAR(500),@KHOI_RECIVE NVARCHAR(500),
		@RECIVE_MAKER_ID VARCHAR(20)
-- BÊN GIAO

	-- Thông tin chữ kí
	SET  @SIGN_MAKER_ID =  ( SELECT MAKER_ID  FROM ASS_COLLECT_MULTI_MASTER A WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)
	SET  @SIGN_CREATE_DT =  ( SELECT CREATE_DT  FROM ASS_COLLECT_MULTI_MASTER A WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)

	-- Thông tin bên giao (lấy thông tin người xác nhận)
	SET @TRANSFER_MAKER_ID = (SELECT MAKER_ID FROM ASS_COLLECT_CONFIRM_MASTER WHERE  COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)
	SET @BRANCH_TRANSFER = (SELECT BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TLSUBBRID FROM TL_USER WHERE TLNANME =@TRANSFER_MAKER_ID)) 
		+ ' - '+ (SELECT DEP_NAME FROM CM_DEPARTMENT WHERE DEP_ID =(SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@TRANSFER_MAKER_ID))
	SET @NAME_TRANSFER =(SELECT TLFullName FROM TL_USER WHERE TLNANME =@TRANSFER_MAKER_ID)
	SET @POSITION = (SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@TRANSFER_MAKER_ID)
	SET @DEP_TRANSFER =(SELECT TOP 1 SECUR_CODE FROM TL_USER WHERE TLNANME = @TRANSFER_MAKER_ID)
	SET @KHOI_TRANSFER= (SELECT TOP 1 DVDM_NAME FROM CM_DVDM WHERE DVDM_ID IN (SELECT DVDM_ID FROM PL_COSTCENTER WHERE COST_ID IN (SELECT COST_ID FROM PL_COSTCENTER_DT
		WHERE DEP_ID = @DEP_TRANSFER )) AND IS_KHOI ='1')

-- BÊN NHẬN
	-- Thông tin chữ kí
	SET  @SIGN_RECEVI_MAKER_ID =  ( SELECT MAKER_ID  FROM ASS_COLLECT_MULTI_MASTER A WHERE COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)
	SET  @SIGN_RECEVI_DT =  ( SELECT CREATE_DT  FROM ASS_COLLECT_MULTI_MASTER A WHERE COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)
	-- Thông tin bên nhận
	SET @RECIVE_MAKER_ID = (SELECT TOP 1 MAKER_ID FROM ASS_COLLECT_MULTI_MASTER WHERE  COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)
	SET @BRANCH_RECIVE =(SELECT BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID = (SELECT TOP 1 TLSUBBRID FROM TL_USER WHERE TLNANME = @RECIVE_MAKER_ID)) 
	+ ' - '+ (SELECT DEP_NAME FROM CM_DEPARTMENT WHERE DEP_ID =(SELECT TOP 1 SECUR_CODE FROM TL_USER WHERE TLNANME = @RECIVE_MAKER_ID))
	SET @DEP_RECIVE = (SELECT TOP 1 SECUR_CODE FROM TL_USER WHERE TLNANME = @RECIVE_MAKER_ID)
	SET @NAME_RECIVE=  (SELECT EMP_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN = @RECIVE_MAKER_ID)
	SET @POSITION_RECIVE =  (SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN = @RECIVE_MAKER_ID)
	SET @KHOI_RECIVE= (SELECT TOP 1 DVDM_NAME FROM CM_DVDM WHERE DVDM_ID IN (SELECT DVDM_ID FROM PL_COSTCENTER WHERE COST_ID IN (SELECT COST_ID FROM PL_COSTCENTER_DT
		WHERE DEP_ID =@DEP_RECIVE)) AND IS_KHOI ='1')


-- KẾ TOÁN
SET  @SIGN_CHECKER_ID_KT =  ( SELECT CHECKER_ID_KT  FROM ASS_COLLECT_MULTI_MASTER A WHERE COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)
SET  @SIGN_APPROVE_DT_KT =  ( SELECT APPROVE_DT_KT  FROM ASS_COLLECT_MULTI_MASTER A WHERE COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)

-- DANH SÁCH TÀI SẢN
SELECT ROW_NUMBER()OVER(ORDER BY A.ASSET_ID) AS STT, A.COLLECT_NOTE,convert(varchar, A.COLLECT_DT, 103) as COLLECT_DT,B.ASSET_CODE, B.ASSET_NAME, B.ASSET_DESC, B.[TYPE_ID],B.AMORT_START_DATE,B.BRANCH_ID AS BRANCH_USE,
	BRU.BRANCH_CODE AS BRANCH_CODE_USE, (BRU.BRANCH_NAME +' '+ ISNULL(DEP.DEP_NAME,'')) AS BRANCH_NAME_USE,B.EMP_ID, I.TLFullName AS EMP_NAME,
	H.TYPE_CODE, H.TYPE_NAME, 
	B.[GROUP_ID], G.GROUP_CODE, G.GROUP_NAME, 
	B.AMORT_MONTH, B.BUY_PRICE, B.AMORT_AMT,
	C.BRANCH_CODE AS BRANCH_CODE_CREATE, C.BRANCH_NAME AS BRANCH_NAME_CREATE,--D.DEP_CODE,E.DIV_CODE, E.DIV_NAME, E.ADDR AS DIV_ADDR, 
	F.AUTH_STATUS_NAME,CONVERT(VARCHAR(10),A.CREATE_DT,110) AS NGAY_TAO,
	E.STATUS_NAME ASS_STATUS_NAME, --Tinh trang tai san
	D.STATUS_NAME AMORT_STATUS_NAME, 1 SL,
	B.BUY_PRICE,B.ASSET_SERIAL_NO,B.AMORT_AMT-B.AMORTIZED_AMT ASREMAIN_AMT,B.CREATE_DT AS CREATE_DT_ASS,B.USE_DATE
	--I.TLFullName
	FROM ASS_COLLECT_MULTI_DT A
	LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID
	LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
	LEFT JOIN CM_BRANCH BRU ON BRU.BRANCH_ID = B.BRANCH_ID
	LEFT JOIN CM_DEPARTMENT DEP ON B.DEPT_ID = DEP.DEP_ID
	--LEFT JOIN CM_DIVISION E ON A.DIVISION_ID = E.DIV_ID
	LEFT JOIN CM_AUTH_STATUS F ON A.AUTH_STATUS = F.AUTH_STATUS
	LEFT JOIN ASS_GROUP G ON B.GROUP_ID = G.GROUP_ID
	LEFT JOIN ASS_TYPE H ON B.TYPE_ID = H.TYPE_ID
	LEFT JOIN TL_USER I ON I.TLNANME = B.EMP_ID
	LEFT JOIN ASS_STATUS E ON B.ASS_STATUS = E.STATUS_ID
	LEFT JOIN ASS_AMORT_STATUS D ON B.AMORT_STATUS = D.STATUS_CODE
WHERE  COL_MULTI_MASTER_ID= @COL_MULTI_MASTER_ID

-- THÔNG TIN BÊN GIAO
SELECT @KHOI_TRANSFER AS KHOI_TRANSFER, @BRANCH_TRANSFER AS BRANCH_TRANSFER, @NAME_TRANSFER AS NAME_TRANSFER, @POSITION AS POSITION_SEND

-- THÔNG TIN BÊN NHẬN
SELECT @KHOI_RECIVE AS KHOI_RECIVE, @BRANCH_RECIVE AS BRANCH_RECIVE, @NAME_RECIVE AS NAME_RECIVE, @POSITION_RECIVE AS POSITION_RECIVE

-- CHỮ KÍ
DECLARE @S1 NVARCHAR(500),@S2 NVARCHAR(500),@S3 NVARCHAR(500),@S4 NVARCHAR(500)

SET @S1 = (SELECT TLFullName FROM TL_USER WHERE TLNANME = @SIGN_MAKER_ID) + CHAR(10) + N' Đã xác nhận vào lúc ' + ISNULL(FORMAT(@SIGN_CREATE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'')

SET @S3 = (SELECT TLFullName FROM TL_USER WHERE TLNANME = @SIGN_CHECKER_ID_KT) + CHAR(10) + N' Đã xác nhận vào lúc ' + ISNULL(FORMAT(@SIGN_APPROVE_DT_KT,'dd/MM/yyyy,hh:mm:ss tt'),'')

SET @S4 = (SELECT TLFullName FROM TL_USER WHERE TLNANME = @SIGN_RECEVI_MAKER_ID) + CHAR(10) + N' Đã xác nhận vào lúc ' + ISNULL(FORMAT(@SIGN_RECEVI_DT,'dd/MM/yyyy,hh:mm:ss tt'),'')

SELECT @S1 AS S1,
N'Nguyễn Trưởng Gia'  + CHAR(10) + N' Đã xác nhận vào lúc ' + ISNULL(FORMAT(@SIGN_APPROVE_DT_KT,'dd/MM/yyyy,hh:mm:ss tt'),'') AS S2,
@S3 AS S3,@S4 AS S4