DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

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