ALTER PROC	dbo.ASS_ADDNEW_VB_BBBG
@ADDNEW_ID	VARCHAR(15)	=	NULL
AS
BEGIN
DECLARE @BRANCH_ID VARCHAR(50) = (SELECT AA.BRANCH_ID FROM ASS_ADDNEW AA WHERE AA.ADDNEW_ID = @ADDNEW_ID)
DECLARE @REQ_ID VARCHAR(50) = (SELECT AA.REQ_ID FROM ASS_ADDNEW AA WHERE AA.ADDNEW_ID = @ADDNEW_ID)

--TABLE0
IF((@REQ_ID IS NULL OR @REQ_ID = '') AND (@BRANCH_ID IS NULL OR @BRANCH_ID = '')) 
BEGIN
	SELECT '' AS INFO_LIQ, '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME ,
    '' AS BRANCH_NAME
END
ELSE
BEGIN
	SELECT DISTINCT 
    al.FORWARD_CONTENT AS INFO_LIQ,	tu.TLFullName, tu.ADDRESS, tu.PHONE, cel.POS_NAME AS POS_NAME ,
    CASE WHEN cb.BRANCH_TYPE = 'HS' THEN cb.BRANCH_NAME + ' - ' + CD.DEP_NAME ELSE cb.BRANCH_NAME END AS BRANCH_NAME
    FROM	ASS_ADDNEW	al
    LEFT JOIN	TL_USER	tu ON	al.MAKER_ID	=	tu.TLNANME
    LEFT JOIN CM_EMPLOYEE_LOG cel ON al.MAKER_ID = cel.USER_DOMAIN
    LEFT JOIN CM_BRANCH cb ON al.BRANCH_CREATE = cb.BRANCH_ID
    LEFT JOIN CM_DEPARTMENT CD ON CD.DEP_ID = al.DEPT_CREATE
    WHERE	al.ADDNEW_ID	=	@ADDNEW_ID

END

--TABLE1
SELECT DISTINCT	ROW_NUMBER() OVER	(ORDER BY	(SELECT	1))	AS STT,	ald.ADDNEW_ID, am.ASSET_CODE, am.ASSET_NAME,	cu.UNIT_NAME,	
'1' AS SO_LUONG,ams.STATUS_NAME AS ASS_STATUS_DESC, am.ASSET_SERIAL_NO, prd.REQ_CODE AS	TO_TRINH,	ald.NOTES, am.ASSET_DESC AS ASS_DESC, ald.REQ_CODE
FROM  ASS_ADDNEW ald
LEFT JOIN ASS_TRANSACTIONS AT ON ald.ADDNEW_ID= AT.TRN_ID
LEFT JOIN	ASS_MASTER am	ON AT .ASSET_ID	=	am.ASSET_ID
LEFT JOIN	ASS_STATUS ams	ON am.ASS_STATUS	=	ams.STATUS_ID	
LEFT JOIN	ASS_GROUP	ag ON	am.GROUP_ID	=	ag.GROUP_ID
LEFT JOIN	PL_REQUEST_DOC prd ON	am.REQ_ID	=	prd.REQ_ID
LEFT JOIN	CM_UNIT	cu ON	cu.UNIT_ID = ag.UNIT
--WHERE	ald.ADDNEW_ID	=	'ASA000000021002'
WHERE	ald.ADDNEW_ID	=	@ADDNEW_ID
ORDER BY STT ASC

--table2
--SELECT DISTINCT al.LIQ_ID, tu.TLFullName FROM TL_USER tu
--RIGHT JOIN ASS_LIQUIDATION al ON al.MAKER_ID = tu.TLNANME
--WHERE al.LIQ_ID = @LIQ_ID
DECLARE @DT DATETIME = (SELECT APPROVE_DT FROM ASS_ADDNEW AA WHERE AA.ADDNEW_ID = @ADDNEW_ID)
DECLARE @BB_DT NVARCHAR(500)
SET @BB_DT = N'Ngày ' + CAST(DAY(@DT) AS VARCHAR(500))
				+ N' tháng ' + CAST(MONTH(@DT) AS VARCHAR(500))
				+ N' năm ' + CAST(YEAR(@DT) AS VARCHAR(500))
				+ N' tại '
                + (SELECT B.BRANCH_NAME FROM dbo.ASS_ADDNEW A JOIN dbo.CM_BRANCH B ON A.BRANCH_CREATE = B.BRANCH_ID WHERE A.ADDNEW_ID = @ADDNEW_ID)
  

SELECT CASE WHEN cb.BRANCH_TYPE = 'HS' THEN cb.BRANCH_NAME + ' - ' + CD.DEP_NAME ELSE cb.BRANCH_NAME END AS BRANCH_NAME,
CASE WHEN cb.BRANCH_TYPE = 'HS' THEN @BB_DT + ' - ' + CD.DEP_NAME ELSE @BB_DT END AS BBBG_DT,
AL.NOTES AS INFO_LIQ, AL.ADDNEW_ID
FROM ASS_ADDNEW AL
LEFT JOIN CM_BRANCH CB ON AL.BRANCH_CREATE = CB.BRANCH_ID
LEFT JOIN CM_DEPARTMENT CD ON AL.DEPT_CREATE = CD.DEP_ID
WHERE AL.ADDNEW_ID = @ADDNEW_ID

--TABLE3
IF((@REQ_ID IS NULL OR @REQ_ID = '') AND (@BRANCH_ID IS NULL OR @BRANCH_ID = '')) 
BEGIN
	SELECT '' AS INFO_LIQ, '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME ,
    '' AS BRANCH_NAME
END
ELSE
BEGIN
	SELECT DISTINCT 
    al.FORWARD_CONTENT AS INFO_LIQ,	tu.TLFullName, tu.ADDRESS, tu.PHONE, ISNULL(cel.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT(
    ((SELECT TOP 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = @ADDNEW_ID AND PP.PROCESS_ID = 'SEND' ORDER BY PP.APPROVE_DT DESC))
    , 'dd/MM/yyyy HH:mm:ss')  AS POS_NAME ,
    CASE WHEN cb.BRANCH_TYPE = 'HS' THEN cb.BRANCH_NAME + ' - ' + CD.DEP_NAME ELSE cb.BRANCH_NAME END AS BRANCH_NAME
    FROM	ASS_ADDNEW	al
    LEFT JOIN	TL_USER	tu ON	al.MAKER_ID	=	tu.TLNANME
    LEFT JOIN CM_EMPLOYEE_LOG cel ON al.MAKER_ID = cel.USER_DOMAIN
    LEFT JOIN CM_BRANCH cb ON al.BRANCH_CREATE = cb.BRANCH_ID
    LEFT JOIN CM_DEPARTMENT CD ON CD.DEP_ID = al.DEPT_CREATE
    WHERE	al.ADDNEW_ID	=	@ADDNEW_ID

END

END