AMS - VB

ALTER     PROC dbo.ASS_USE_MULTI_HANDOVER_RECORD

@USER_MASTER_ID VARCHAR(15) = NULL

AS

BEGIN

--Table 0 Nội dung && BRANCH

SELECT N'Hội sở - PHÒNG QUẢN LÝ TÀI SẢN' AS BRANCH_NAME,

(N'Hôm nay, ngày ' + CONVERT(VARCHAR(5),DAY(GETDATE())) + N' tháng ' + CONVERT(VARCHAR(5),MONTH(GETDATE())) + N' năm ' + CONVERT(VARCHAR(5),YEAR(GETDATE())) + N' tại ') AS DD_MM_YY,

A.CONTENT AS TITLE

FROM ASS_USE_MULTI_MASTER A

WHERE A.USER_MASTER_ID = @USER_MASTER_ID

--Table 1 Bên giao

SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(D.BRANCH_NAME,'') + ISNULL(' - ' + E.DEP_NAME,'') AS BRANCH_NAME

FROM  ASS_USE_MULTI_MASTER A

LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME

LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN

LEFT JOIN CM_BRANCH D ON A.BRANCH_CREATE = D.BRANCH_ID

LEFT JOIN CM_DEPARTMENT E ON A.DEPT_CREATE = E.DEP_ID

WHERE A.USER_MASTER_ID = @USER_MASTER_ID

--Table 2 Bên nhận

--Fix tạm UAT

IF(EXISTS(SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME

    FROM  ASS_USE_CONFIRM_MASTER A

    LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME

    LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN

    WHERE A.USER_MASTER_ID = @USER_MASTER_ID))

BEGIN

SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(D.BRANCH_NAME,'') + ISNULL(' - ' + E.DEP_NAME,'') AS BRANCH_NAME

FROM  ASS_USE_CONFIRM_MASTER A

LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME

LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN

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

LEFT JOIN CM_DEPARTMENT E ON A.DEP_ID = E.DEP_ID

WHERE A.USER_MASTER_ID = @USER_MASTER_ID

END

ELSE

BEGIN

SELECT '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME

END

--TABLE 3 DANH SÁCH TÀI SẢN

SELECT CASE 

       	WHEN B.ASSET_CODE IS NOT NULL AND B.ASSET_CODE <> '' THEN B.ASSET_CODE

       	ELSE B.ASS_CODE_TMP

       END AS ASSET_CODE, B.ASSET_NAME, D.UNIT_NAME, 1 AS QUANTITY, 

       E.STATUS_NAME, B.ASSET_SERIAL_NO AS SERIAL, R.REQ_CODE AS REQ_CODE, B.NOTES AS QUY_CACH, A.NOTES, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT

       ,CE.EMP_NAME

FROM ASS_USE_MULTI_DT A

LEFT JOIN TR_REQUEST_SHOP_DOC R ON A.REQ_ID = R.REQ_ID

LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID

LEFT JOIN ASS_GROUP C ON B.GROUP_ID = C.GROUP_ID

LEFT JOIN CM_UNIT D ON C.UNIT = D.UNIT_ID 

LEFT JOIN ASS_STATUS E ON B.ASS_STATUS = E.STATUS_ID

LEFT JOIN CM_EMPLOYEE CE ON A.EMP_ID = CE.EMP_ID

WHERE A.USER_MASTER_ID = @USER_MASTER_ID

--TABLE 4 CHỮ KÝ

SELECT TOP 1 E.TLFullName AS CHECKER_NAME, --BÊN NHẬN

ISNULL(C.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP 

                                                                  WHERE PP.REQ_ID = A.USER_MASTER_ID AND PP.PROCESS_ID = 'CONFIRM'

                                                                  AND PP.CHECKER_ID = B.MAKER_ID

                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME_2,--BÊN NHẬN

D.TLFullName AS MAKER_NAME, --BÊN GIAO 

--G.TLFullName AS INTERMEDIATE_UNIT_NAME, --ĐƠN VỊ TRUNG GIAN

ISNULL(F.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP 

                                                                  WHERE PP.REQ_ID = A.USER_MASTER_ID AND PP.PROCESS_ID = 'SEND'

                                                                  AND PP.CHECKER_ID = A.MAKER_ID

                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME --BÊN GIAO 

--H.POS_NAME AS POS_NAME_1  -- ĐƠN VỊ TRUNG GIAN

FROM ASS_USE_MULTI_MASTER A

LEFT JOIN ASS_USE_CONFIRM_MASTER B ON A.USER_MASTER_ID = B.USER_MASTER_ID

LEFT JOIN TL_USER E ON B.MAKER_ID = E.TLNANME --BÊN NHẬN

LEFT JOIN CM_EMPLOYEE_LOG C ON B.MAKER_ID = C.USER_DOMAIN --BÊN NHẬN

LEFT JOIN TL_USER D ON A.MAKER_ID = D.TLNANME --BÊN GIAO 

LEFT JOIN CM_EMPLOYEE_LOG F ON D.TLNANME = F.USER_DOMAIN

--LEFT JOIN TL_USER G ON A.CHECKER_ID = G.TLNANME -- DVTG

--LEFT JOIN CM_EMPLOYEE_LOG H ON A.CHECKER_ID = H.USER_DOMAIN --DVTG

WHERE A.USER_MASTER_ID = @USER_MASTER_ID

--TABLE 5 SỐ

SELECT @USER_MASTER_ID AS NO

--TABLE 6 ĐƠN VỊ TRUNG GIAN

SELECT '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME

--SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(D.BRANCH_NAME,'') + ISNULL(' - ' + E.DEP_NAME,'') AS BRANCH_NAME

--FROM  ASS_USE_MULTI_MASTER A

--LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME

--LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN

--LEFT JOIN CM_BRANCH D ON A.BRANCH_CREATE = D.BRANCH_ID

--LEFT JOIN CM_DEPARTMENT E ON A.DEPT_CREATE = E.DEP_ID

--WHERE A.USER_MASTER_ID = @USER_MASTER_ID

END

GO

ALTER PROC dbo.ASS_COL_MULTI_HANDOVER_RECORD

@COL_MULTI_MASTER_ID VARCHAR(15) = NULL

AS

BEGIN

--Table 0 Nội dung && BRANCH

SELECT TOP 1 B.BRANCH_NAME  + ISNULL(' - ' + C.DEP_NAME,'') AS BRANCH_NAME,

D.CONTENT AS TITLE

,(N'Hôm nay, ngày ' + CONVERT(VARCHAR(5),DAY(GETDATE())) + N' tháng ' + CONVERT(VARCHAR(5),MONTH(GETDATE())) + N' năm ' + CONVERT(VARCHAR(5),YEAR(GETDATE())) + N' tại ') AS DD_MM_YY

FROM ASS_COLLECT_MULTI_DT A

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

LEFT JOIN CM_DEPARTMENT C ON A.DEPT_ID_USE = C.DEP_ID

LEFT JOIN ASS_COLLECT_MULTI_MASTER D ON A.COL_MULTI_MASTER_ID = D.COL_MULTI_MASTER_ID

WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID

--Table 1 Bên giao

--Fix tạm UAT

IF(EXISTS(SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME

          FROM  ASS_COLLECT_CONFIRM_MASTER A

          LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME

          LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN

          WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID))

BEGIN

--    IF(EXISTS(SELECT 1 FROM ASS_COLLECT_MULTI_MASTER acmm WHERE acmm.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID AND acmm.REQ_ID IS NOT NULL AND acmm.REQ_ID <> ''))

--    BEGIN

--        SELECT TOP 1  C.TLFullName, C.ADDRESS, C.PHONE, D.POS_NAME, ISNULL(E.BRANCH_NAME,'') + ISNULL(' - ' + G.DEP_NAME,'') AS BRANCH_NAME

--        FROM ASS_COLLECT_MULTI_MASTER A

--        LEFT JOIN TR_REQUEST_SHOP_DOC B ON A.REQ_ID = B.REQ_ID

--        LEFT JOIN TL_USER C ON B.MAKER_ID = C.TLNANME

--        LEFT JOIN CM_EMPLOYEE_LOG D ON B.MAKER_ID = D.USER_DOMAIN

--        LEFT JOIN CM_BRANCH E ON C.TLSUBBRID = E.BRANCH_ID

--        LEFT JOIN CM_DEPARTMENT G ON C.DEP_ID = G.DEP_ID

--        WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID

--    END

--    ELSE

--    BEGIN

        SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(E.BRANCH_NAME,'') + ISNULL(' - ' + G.DEP_NAME,'') AS BRANCH_NAME

        FROM  ASS_COLLECT_CONFIRM_MASTER A

        LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME

        LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN

        LEFT JOIN CM_BRANCH E ON B.TLSUBBRID = E.BRANCH_ID

        LEFT JOIN CM_DEPARTMENT G ON B.DEP_ID = G.DEP_ID

        WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID

--    END

END

ELSE

BEGIN

SELECT '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME

END

--Table 2 Bên nhận

SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(D.BRANCH_NAME,'') + ISNULL(' - ' + E.DEP_NAME,'') AS BRANCH_NAME

FROM  ASS_COLLECT_MULTI_MASTER A

LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME

LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN

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

LEFT JOIN CM_DEPARTMENT E ON A.DEPT_CREATE = E.DEP_ID

WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID

--TABLE 3 DANH SÁCH TÀI SẢN

SELECT CASE 

       	WHEN B.ASSET_CODE IS NOT NULL AND B.ASSET_CODE <> '' THEN B.ASSET_CODE

       	ELSE B.ASS_CODE_TMP

       END AS ASSET_CODE, B.ASSET_NAME, D.UNIT_NAME, 1 AS QUANTITY, E.STATUS_NAME, B.ASSET_SERIAL_NO AS SERIAL, REQ.REQ_CODE AS REQ_CODE,B.NOTES AS QUY_CACH, A.NOTES,

       --ROW_NUMBER() OVER (PARTITION BY B.ASSET_NAME ORDER BY B.ASSET_NAME) AS STT,

       ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT,

       CA.CONTENT AS COLLECT_REASON

FROM ASS_COLLECT_MULTI_DT A

LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID

LEFT JOIN ASS_GROUP C ON B.GROUP_ID = C.GROUP_ID

LEFT JOIN CM_UNIT D ON C.UNIT = D.UNIT_ID 

LEFT JOIN ASS_STATUS E ON B.ASS_STATUS = E.STATUS_ID

LEFT JOIN ASS_COLLECT_MULTI_MASTER  F ON A.COL_MULTI_MASTER_ID = F.COL_MULTI_MASTER_ID

LEFT JOIN TR_REQUEST_SHOP_DOC REQ ON F.REQ_ID = REQ.REQ_ID

LEFT JOIN CM_ALLCODE CA ON A.PURPOSE_ID = CA.CDVAL AND CA.CDNAME = 'REASON_ASS_REQ' AND CA.CDTYPE = 'REQ_ASSET'

WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID

--TABLE 4 CHỮ KÝ

--SELECT ''

SELECT TOP 1 E.TLFullName AS MAKER_NAME, --BÊN GIAO

ISNULL(C.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP 

                                                                  WHERE PP.REQ_ID = A.COL_MULTI_MASTER_ID AND PP.PROCESS_ID = 'CONFIRM'

                                                                  AND PP.CHECKER_ID = B.MAKER_ID

                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME,--BÊN GIAO

D.TLFullName AS CHECKER_NAME, --BÊN NHẬN

--D.TLFullName AS INTERMEDIATE_UNIT_NAME, --ĐƠN VỊ TRUNG GIAN

--F.POS_NAME AS POS_NAME_1, --ĐƠN VỊ TRUNG GIAN

ISNULL(F.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP 

                                                                  WHERE PP.REQ_ID = A.COL_MULTI_MASTER_ID AND PP.PROCESS_ID = 'APPROVE'

                                                                  AND PP.CHECKER_ID = A.CHECKER_ID

                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME_2  --BÊN NHẬN 

FROM ASS_COLLECT_MULTI_MASTER A

LEFT JOIN ASS_COLLECT_CONFIRM_MASTER B ON A.COL_MULTI_MASTER_ID = B.COL_MULTI_MASTER_ID

LEFT JOIN TL_USER E ON B.MAKER_ID = E.TLNANME --BÊN GIAO

LEFT JOIN CM_EMPLOYEE_LOG C ON B.MAKER_ID = C.USER_DOMAIN --BÊN GIAO

LEFT JOIN TL_USER D ON A.CHECKER_ID = D.TLNANME --BÊN NHẬN + ĐƠN VỊ TRUNG GIAN

LEFT JOIN CM_EMPLOYEE_LOG F ON A.CHECKER_ID = F.USER_DOMAIN --BÊN NHẬN + ĐƠN VỊ TRUNG GIAN

WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID

--TABLE 5 SỐ

SELECT @COL_MULTI_MASTER_ID AS NO

--TABLE 6 ĐƠN VỊ TRUNG GIAN

SELECT '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME

--SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME,ISNULL(D.BRANCH_NAME,'') + ISNULL(' - ' + E.DEP_NAME,'') AS BRANCH_NAME

--FROM  ASS_COLLECT_MULTI_MASTER A

--LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME

--LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN

--LEFT JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID

--LEFT JOIN CM_DEPARTMENT E ON A.DEPT_CREATE = E.DEP_ID

--WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID

END

GO

ALTER PROC dbo.ASS_PRIVATE_TRANSFER_MULTI_MASTER_BBBG

@TRANS_MULTI_MASTER_ID	varchar(15) = null

AS 

BEGIN

DECLARE @EMP_RECEIVER VARCHAR(1) = NULL

DECLARE @EMP_HANDOVER VARCHAR(1) = NULL

--Table 0 Nội dung && BRANCH

SELECT TOP 1 B.BRANCH_NAME + ISNULL(' - ' + C.DEP_NAME,'') BRANCH_NAME, 

(N'Hôm nay, ngày ' + CONVERT(VARCHAR(5),DAY(GETDATE())) + N' tháng ' + CONVERT(VARCHAR(5),MONTH(GETDATE())) + N' năm ' + CONVERT(VARCHAR(5),YEAR(GETDATE())) + N' tại ') AS DD_MM_YY

FROM ASS_PRIVATE_TRANSFER_DT A

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

LEFT JOIN CM_DEPARTMENT C ON A.DEPT_ID = C.DEP_ID

WHERE A.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID

--Table 1 Bên giao

SELECT TOP 1 D.EMP_NAME TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(E.BRANCH_NAME,'') + ISNULL(' - ' + F.DEP_NAME,'') AS BRANCH_NAME

FROM ASS_PRIVATE_TRANSFER_DT A 

LEFT JOIN CM_EMPLOYEE D ON A.EMP_ID_OLD = D.EMP_ID

LEFT JOIN CM_EMPLOYEE_LOG C ON D.EMP_CODE = C.EMP_CODE

LEFT JOIN TL_USER B ON C.USER_DOMAIN = B.TLNANME

LEFT JOIN CM_BRANCH E ON B.TLSUBBRID = E.BRANCH_ID

LEFT JOIN CM_DEPARTMENT F ON B.DEP_ID = F.DEP_ID

WHERE A.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID 

ORDER BY A.EMP_ID_OLD DESC

--Table 2 Bên nhận

SELECT TOP 1 D.EMP_NAME TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(E.BRANCH_NAME,'') + ISNULL(' - ' + F.DEP_NAME,'') AS BRANCH_NAME

FROM ASS_PRIVATE_TRANSFER_DT A 

LEFT JOIN CM_EMPLOYEE D ON A.EMP_ID = D.EMP_ID

LEFT JOIN CM_EMPLOYEE_LOG C ON D.EMP_CODE = C.EMP_CODE

LEFT JOIN TL_USER B ON C.USER_DOMAIN = B.TLNANME

LEFT JOIN CM_BRANCH E ON B.TLSUBBRID = E.BRANCH_ID

LEFT JOIN CM_DEPARTMENT F ON B.DEP_ID = F.DEP_ID

WHERE A.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID 

ORDER BY A.EMP_ID DESC

--TABLE 3 DANH SÁCH TÀI SẢN

SELECT 

CASE 

 	WHEN B.ASSET_CODE IS NOT NULL AND B.ASSET_CODE <> '' THEN B.ASSET_CODE

 	ELSE B.ASS_CODE_TMP

 END AS ASSET_CODE, B.ASSET_NAME, D.UNIT_NAME, 1 AS QUANTITY, A.DESCRIPTION AS NOTES, 

 E.STATUS_NAME, B.ASSET_SERIAL_NO AS SERIAL, '' REQ_CODE, B.NOTES AS QUY_CACH, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT

FROM ASS_PRIVATE_TRANSFER_DT A

LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID

LEFT JOIN ASS_GROUP C ON B.GROUP_ID = C.GROUP_ID

LEFT JOIN CM_UNIT D ON C.UNIT = D.UNIT_ID 

LEFT JOIN ASS_STATUS E ON B.ASS_STATUS = E.STATUS_ID

WHERE A.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID

--TABLE 4 CHỮ KÝ

SELECT TOP 1 B.EMP_NAME AS MAKER_NAME, --BÊN GIAO

ISNULL(B.POS_NAME + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1   PP.APPROVE_DT FROM PL_PROCESS PP 

                                                                  WHERE PP.REQ_ID = D.TRANS_MULTI_MASTER_ID AND PP.PROCESS_ID = 'SEND'

                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss'),'')  AS POS_NAME,--BÊN GIAO

C.EMP_NAME AS CHECKER_NAME, --BÊN NHẬN

ISNULL(C.POS_NAME + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1   PP.APPROVE_DT FROM PL_PROCESS PP 

                                                                  WHERE PP.REQ_ID = D.TRANS_MULTI_MASTER_ID AND PP.PROCESS_ID = 'APPROVE'

                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss'),'')  AS POS_NAME_2  --BÊN NHẬN

FROM ASS_PRIVATE_TRANSFER_DT A

LEFT JOIN ASS_PRIVATE_TRANSFER_MASTER D ON A.TRANS_MULTI_MASTER_ID = D.TRANS_MULTI_MASTER_ID

LEFT JOIN CM_EMPLOYEE B ON A.EMP_ID_OLD = B.EMP_ID --BÊN GIAO

LEFT JOIN CM_EMPLOYEE C ON A.EMP_ID = C.EMP_ID -- BÊN NHẬN

WHERE A.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID

--TABLE 5 SỐ

SELECT @TRANS_MULTI_MASTER_ID AS NO

--TABLE 6 ĐƠN VỊ TRUNG GIAN

SELECT '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME

--SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME

--FROM  ASS_PRIVATE_TRANSFER_MASTER A

--LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME

--LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN

--WHERE A.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID AND A.DEPT_CREATE = 'DEP000000000048'

END

GO

ALTER PROC dbo.ASS_TRANSFER_MULTI_MASTER_BBBG

@TRANS_MULTI_MASTER_ID	varchar(15) = null

AS 

DECLARE @REQ_ID VARCHAR(20) = (SELECT REQ_ID FROM ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

DECLARE @REQ_MAKER VARCHAR(110) = (SELECT TRSD.MAKER_ID FROM TR_REQUEST_SHOP_DOC TRSD WHERE TRSD.REQ_ID = @REQ_ID)

DECLARE @REQ_MAKER_DT VARCHAR(110) = (SELECT TRSD.CREATE_DT FROM TR_REQUEST_SHOP_DOC TRSD WHERE TRSD.REQ_ID = @REQ_ID)

DECLARE @REQ_BRANCH VARCHAR(110) = (SELECT TRSD.BRANCH_ID FROM TR_REQUEST_SHOP_DOC TRSD WHERE TRSD.REQ_ID = @REQ_ID)

DECLARE @REQ_DEP VARCHAR(110) = (SELECT TRSD.DEP_ID FROM TR_REQUEST_SHOP_DOC TRSD WHERE TRSD.REQ_ID = @REQ_ID)

DECLARE @REQ_DEP_NAME NVARCHAR(510) = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @REQ_DEP)

DECLARE @REQ_POS NVARCHAR(510) = (SELECT CEL.POS_NAME FROM CM_EMPLOYEE_LOG CEL WHERE CEL.USER_DOMAIN = @REQ_MAKER)

DECLARE @REQ_MAKER_NAME NVARCHAR(500)      = (SELECT TU.TLFullName FROM TL_USER TU WHERE TU.TLNANME = @REQ_MAKER)

DECLARE @REQ_MAKER_PHONE NVARCHAR(500)    = (SELECT TU.PHONE FROM TL_USER TU WHERE TU.TLNANME = @REQ_MAKER)

DECLARE @REQ_MAKER_ADDR NVARCHAR(500)    = (SELECT TU.ADDRESS FROM TL_USER TU WHERE TU.TLNANME = @REQ_MAKER)

--Khiemchg lấy danh sách đơn vị nhận và cho

            DECLARE @INFO_TRANSFER_RECIEVE TABLE

            (

            	BRN_SD VARCHAR(50),

            	BRN_SD_NAME NVARCHAR(500),

            	K_SD VARCHAR(50),

            	K_SD_NAME NVARCHAR(500),

            	TT_SD VARCHAR(50),

            	TT_SD_NAME NVARCHAR(500),

            	DEP_SD VARCHAR(50),

            	DEP_SD_NAME NVARCHAR(500),

            	PER_SD VARCHAR(50),

            	BRN_N VARCHAR(50),

            	BRN_N_NAME NVARCHAR(500),

            	K_N VARCHAR(50),

            	K_N_NAME NVARCHAR(500),

            	TT_N VARCHAR(50),

            	TT_N_NAME NVARCHAR(500),

            	DEP_N VARCHAR(50),

            	DEP_N_NAME NVARCHAR(500),

            	PER_N VARCHAR(50)

            )

            INSERT INTO @INFO_TRANSFER_RECIEVE 

            SELECT DISTINCT BRANCH_ID_OLD, 

                            BB.BRANCH_NAME,

                            KHOI_ID_OLD,

                            BD.DEP_NAME,

                            CENTER_ID_OLD,

                            BF.DEP_NAME,

                            DEPT_ID_OLD,

                            BE.DEP_NAME,

                            EMP_ID_OLD,

                            A.BRANCH_ID,

                            CB.BRANCH_NAME,

                            A.KHOI_ID,

                            CD.DEP_NAME,

                            CENTER_ID,

                            CF.DEP_NAME,

                            DEPT_ID,

                            CE.DEP_NAME,

                            EMP_ID

            FROM ASS_TRANSFER_MULTI_DT A

            LEFT JOIN CM_BRANCH BB ON A.BRANCH_ID_OLD = BB.BRANCH_ID

            LEFT JOIN CM_DEPARTMENT BD ON BD.DEP_ID = A.KHOI_ID_OLD

            LEFT JOIN CM_DEPARTMENT BE ON BE.DEP_ID = A.DEPT_ID_OLD

            LEFT JOIN CM_DEPARTMENT BF ON BF.DEP_ID = A.CENTER_ID_OLD

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

            LEFT JOIN CM_DEPARTMENT CD ON CD.DEP_ID = A.KHOI_ID

            LEFT JOIN CM_DEPARTMENT CE ON CE.DEP_ID = A.DEPT_ID

            LEFT JOIN CM_DEPARTMENT CF ON CF.DEP_ID = A.CENTER_ID

            WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID

            GROUP BY BRANCH_ID_OLD, 

                            BB.BRANCH_NAME,

                            KHOI_ID_OLD,

                            BD.DEP_NAME,

                            CENTER_ID_OLD,

                            BF.DEP_NAME,

                            DEPT_ID_OLD,

                            BE.DEP_NAME,

                            EMP_ID_OLD,

                            A.BRANCH_ID,

                            CB.BRANCH_NAME,

                            A.KHOI_ID,

                            CD.DEP_NAME,

                            CENTER_ID,

                            CF.DEP_NAME,

                            DEPT_ID,

                            CE.DEP_NAME,

                            EMP_ID

            --DELETE @INFO_TRANSFER_RECIEVE WHERE (BRN_SD = BRN_N AND DEP_SD = DEP_N)

DECLARE @SIGN_QLTS_NAME NVARCHAR(500) = ''

DECLARE @SIGN_QLTS_PHONE NVARCHAR(500) = ''

DECLARE @SIGN_QLTS_ADDR NVARCHAR(500) = ''

DECLARE @SIGN_QLTS_POS NVARCHAR(500) = ''

DECLARE @SIGN_QLTS NVARCHAR(100) = (SELECT ATMM.CHECKER_ID FROM ASS_TRANSFER_MULTI_MASTER ATMM WHERE ATMM.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

DECLARE @SIGN_QLTS_DT DATETIME = (SELECT TOP 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = @TRANS_MULTI_MASTER_ID AND PP.PROCESS_ID = 'QLTS_D' ORDER BY PP.APPROVE_DT DESC)

DECLARE @MAKER_ID NVARCHAR(100) = (SELECT ATMM.MAKER_ID FROM ASS_TRANSFER_MULTI_MASTER ATMM WHERE ATMM.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

DECLARE @MAKER_ID_DT DATETIME = (SELECT TOP 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = @TRANS_MULTI_MASTER_ID AND PP.PROCESS_ID = 'SEND' ORDER BY PP.APPROVE_DT DESC)

DECLARE @SIGN_QLTS_BR NVARCHAR(500) = (SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = (SELECT TU.TLSUBBRID FROM TL_USER TU WHERE TU.TLNANME = @SIGN_QLTS))

DECLARE @SIGN_QLTS_DEP NVARCHAR(500) = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = (SELECT TU.SECUR_CODE FROM TL_USER TU WHERE TU.TLNANME = @SIGN_QLTS))

DECLARE @MAKER_NAME NVARCHAR(500) = (SELECT TU.TLFullName FROM TL_USER TU WHERE TU.TLNANME = @MAKER_ID)

DECLARE @MAKER_POS NVARCHAR(500) = (SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@MAKER_ID)

IF(EXISTS (SELECT * FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS', 'DV0001', 'DEP000000000048') FGUBRV WHERE FGUBRV.TLNANME = @SIGN_QLTS))

BEGIN 

	SELECT @SIGN_QLTS_NAME = TU.TLFullName, @SIGN_QLTS_PHONE = TU.PHONE, @SIGN_QLTS_ADDR = TU.ADDRESS FROM TL_USER TU WHERE TU.TLNANME = @SIGN_QLTS

    SET @SIGN_QLTS_POS =  (SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN = @SIGN_QLTS)

END

-- Thông tin chữ kí

DECLARE @SIGN_CHECKER_ID_KT VARCHAR(100),@SIGN_MAKER_ID VARCHAR(100),@SIGN_APPROVE_DT_KT DATETIME,@SIGN_CREATE_DT DATETIME,@SIGN_APPROVE_ID VARCHAR(20),@SIGN_APPROVE_DT DATETIME,

		@SIGN_RECEVI_MAKER_ID VARCHAR(100),@SIGN_RECEVI_DT DATETIME,@SIGN_MAKER_ID_KT VARCHAR(100),@SIGN_MAKER_ID_CREATE VARCHAR(50),@AUTH_STATUS VARCHAR(50)

-- Thông tin bên giao

DECLARE @DEP_TRANSFER NVARCHAR(1500)       = (SELECT TOP 1 DEP_SD FROM @INFO_TRANSFER_RECIEVE)

DECLARE @BRANCH_TRANSFER NVARCHAR(500)  = (SELECT TOP 1 BRN_SD FROM @INFO_TRANSFER_RECIEVE)

DECLARE @KHOI_TRANSFER NVARCHAR(500)    = (SELECT TOP 1 K_SD FROM @INFO_TRANSFER_RECIEVE)

DECLARE	@TT_TRANSFER NVARCHAR(500)      = (SELECT TOP 1 TT_SD FROM @INFO_TRANSFER_RECIEVE)

DECLARE @POSITION NVARCHAR(500)         --= (SELECT * FROM @INFO_TRANSFER_RECIEVE)

DECLARE @TRANSFER_MAKER_ID VARCHAR(100)  = (SELECT TOP 1 PER_SD FROM @INFO_TRANSFER_RECIEVE)

DECLARE @CREATE_DT NVARCHAR(500)        = (SELECT FORMAT(ATMM.CREATE_DT, 'dd/MM/yyyy') FROM ASS_TRANSFER_MULTI_MASTER ATMM WHERE ATMM.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

DECLARE @TRANSFER_CONFIRM NVARCHAR(500) =  (SELECT TOP 1 PRP.CHECKER_ID FROM PL_REQUEST_PROCESS PRP 

                                            WHERE PRP.PROCESS_ID = 'XNGN' AND PRP.REQ_ID = @TRANS_MULTI_MASTER_ID 

                                                AND PRP.BRANCH_ID = @BRANCH_TRANSFER AND ISNULL(PRP.DEP_ID,'') = ISNULL(@DEP_TRANSFER, ''))

DECLARE @TRANSFER_CONFIRM_DT DATETIME =  (SELECT TOP 1 PRP.APPROVE_DT FROM PL_PROCESS PRP 

                                            WHERE PRP.PROCESS_ID = 'CONFIRM' AND PRP.REQ_ID = @TRANS_MULTI_MASTER_ID AND PRP.CHECKER_ID = @TRANSFER_CONFIRM ORDER BY PRP.APPROVE_DT DESC)

DECLARE @NAME_TRANSFER NVARCHAR(500)    = (SELECT TU.TLFullName FROM TL_USER TU WHERE TU.TLNANME = @TRANSFER_CONFIRM)

DECLARE @POS_TRANSFER NVARCHAR(500)    = (SELECT CEL.POS_NAME FROM CM_EMPLOYEE_LOG CEL WHERE CEL.USER_DOMAIN = @TRANSFER_CONFIRM)

DECLARE @PHONE_TRANSFER NVARCHAR(500)    = (SELECT TU.PHONE FROM TL_USER TU WHERE TU.TLNANME = @TRANSFER_CONFIRM)

DECLARE @ADDR_TRANSFER NVARCHAR(500)    = (SELECT TU.ADDRESS FROM TL_USER TU WHERE TU.TLNANME = @TRANSFER_CONFIRM)

-- Thông tin bên nhận

DECLARE @DEP_RECIVE NVARCHAR(1500)         = (SELECT TOP 1 DEP_N FROM @INFO_TRANSFER_RECIEVE)

DECLARE @BRANCH_RECIVE NVARCHAR(500)    = (SELECT TOP 1 BRN_N FROM @INFO_TRANSFER_RECIEVE)

DECLARE @KHOI_RECIVE NVARCHAR(500)      = (SELECT TOP 1 K_N FROM @INFO_TRANSFER_RECIEVE)

DECLARE @TT_RECIVE NVARCHAR(500)        = (SELECT TOP 1 TT_N FROM @INFO_TRANSFER_RECIEVE)

DECLARE @POSITION_RECIVE NVARCHAR(500)

DECLARE @RECIVE_MAKER_ID VARCHAR(100)    = (SELECT TOP 1 PER_N FROM @INFO_TRANSFER_RECIEVE)

DECLARE @RECEIVE_CONFIRM NVARCHAR(500) =  (SELECT TOP 1 PRP.CHECKER_ID FROM PL_REQUEST_PROCESS PRP 

                                            WHERE PRP.PROCESS_ID = 'XNGN' AND PRP.REQ_ID = @TRANS_MULTI_MASTER_ID 

                                                AND PRP.BRANCH_ID = @BRANCH_RECIVE AND ISNULL(PRP.DEP_ID,'') = ISNULL(@DEP_RECIVE, ''))

DECLARE @RECEIVE_CONFIRM_DT DATETIME =  (SELECT TOP 1 PRP.APPROVE_DT FROM PL_PROCESS PRP 

                                            WHERE PRP.PROCESS_ID = 'CONFIRM' AND PRP.REQ_ID = @TRANS_MULTI_MASTER_ID AND PRP.CHECKER_ID = @RECEIVE_CONFIRM ORDER BY PRP.APPROVE_DT DESC)

DECLARE @NAME_RECIVE NVARCHAR(500)      = (SELECT TU.TLFullName FROM TL_USER TU WHERE TU.TLNANME = @RECEIVE_CONFIRM)

DECLARE @POS_RECIVE NVARCHAR(500)    = (SELECT CEL.POS_NAME FROM CM_EMPLOYEE_LOG CEL WHERE CEL.USER_DOMAIN = @RECEIVE_CONFIRM)

DECLARE @PHONE_RECIVE NVARCHAR(500)    = (SELECT TU.PHONE FROM TL_USER TU WHERE TU.TLNANME = @RECEIVE_CONFIRM)

DECLARE @ADDR_RECIVE NVARCHAR(500)    = (SELECT TU.ADDRESS FROM TL_USER TU WHERE TU.TLNANME = @RECEIVE_CONFIRM)

-- Thông tin Ngày điều chuyển

DECLARE @TRANSFER_DT DATETIME, @CREATE_DT_TRANSFER DATETIME

	SELECT  @TRANSFER_DT=TRANSFER_DT,@CREATE_DT_TRANSFER =CREATE_DT FROM dbo.ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID

-- BÊN GIAO

	-- Thông tin chữ kí

	SET  @SIGN_MAKER_ID =  ( SELECT MAKER_ID_BG  FROM ASS_TRANSFER_CONFIRM_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

	SET  @SIGN_CREATE_DT = ( SELECT CONFIRM_DT_BG  FROM ASS_TRANSFER_CONFIRM_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

	SET @SIGN_APPROVE_ID = (SELECT CHECKER_ID FROM ASS_TRANSFER_MULTI_MASTER WHERE  TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

	SET @SIGN_APPROVE_DT = (SELECT APPROVE_DT FROM ASS_TRANSFER_MULTI_MASTER WHERE  TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

	-- Thông tin bên giao

	--SET @BRANCH_TRANSFER = ISNULL((SELECT TOP 1 A.BRANCH_NAME FROM dbo.CM_BRANCH A JOIN dbo.ASS_TRANSFER_MULTI_DT B ON A.BRANCH_ID = B.BRANCH_ID_OLD WHERE B.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID ORDER BY B.TRANSFER_MULTI_ID ASC), '')

	--	+ ISNULL((SELECT TOP 1 ' - ' + A.DEP_NAME FROM dbo.CM_DEPARTMENT A JOIN dbo.ASS_TRANSFER_MULTI_DT B ON A.DEP_ID = B.DEPT_ID_OLD WHERE B.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID ORDER BY B.TRANSFER_MULTI_ID ASC), '')

	--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_CONFIRM)

	SET @CREATE_DT = N'Ngày ' + CAST((SELECT DAY(APPROVE_DT_KT) FROM dbo.ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) AS VARCHAR(500))

				+ N' tháng ' + CAST((SELECT MONTH(APPROVE_DT_KT) FROM dbo.ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) AS VARCHAR(500))

				+ N' năm ' + CAST((SELECT YEAR(APPROVE_DT_KT) FROM dbo.ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) AS VARCHAR(500))

				+ N' tại ' + (SELECT B.BRANCH_NAME FROM dbo.ASS_TRANSFER_MULTI_MASTER A JOIN dbo.CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

-- BÊN NHẬN

	-- Thông tin chữ kí

	SET  @SIGN_RECEVI_MAKER_ID =  ( SELECT MAKER_ID_BN  FROM ASS_TRANSFER_CONFIRM_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

	SET  @SIGN_RECEVI_DT =  ( SELECT CONFIRM_DT_BN  FROM ASS_TRANSFER_CONFIRM_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

	-- Thông tin bên nhận

	SET @RECIVE_MAKER_ID = (SELECT TOP 1 MAKER_ID_BN FROM ASS_TRANSFER_CONFIRM_MASTER WHERE  TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

	--SET @BRANCH_RECIVE = ISNULL((SELECT TOP 1 A.BRANCH_NAME FROM dbo.CM_BRANCH A JOIN dbo.ASS_TRANSFER_MULTI_DT B ON A.BRANCH_ID = B.BRANCH_ID WHERE B.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID ORDER BY B.TRANSFER_MULTI_ID ASC), '')

	--	+ ISNULL((SELECT TOP 1 ' - ' + A.DEP_NAME FROM dbo.CM_DEPARTMENT A JOIN dbo.ASS_TRANSFER_MULTI_DT B ON A.DEP_ID = B.DEPT_ID WHERE B.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID ORDER BY B.TRANSFER_MULTI_ID ASC), '')

	--SET @DEP_RECIVE = (SELECT TOP 1 DEPT_ID FROM ASS_TRANSFER_MULTI_DT WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

	--SET @NAME_RECIVE=  (SELECT TLFullName FROM TL_USER WHERE TLNANME = @RECIVE_MAKER_ID)

	SET @POSITION_RECIVE =  (SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN = @RECEIVE_CONFIRM)

	--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_MAKER_ID_CREATE =  ( SELECT A.MAKER_ID  FROM ASS_TRANSFER_MULTI_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

SET  @SIGN_CHECKER_ID_KT =  ( SELECT CHECKER_ID_KT  FROM ASS_TRANSFER_MULTI_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

SET  @SIGN_APPROVE_DT_KT =  ( SELECT APPROVE_DT_KT  FROM ASS_TRANSFER_MULTI_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

SET  @SIGN_MAKER_ID_KT	 =  ( SELECT A.MAKER_ID_KT  FROM ASS_TRANSFER_MULTI_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

-- DANH SÁCH TÀI SẢN

SELECT	ROW_NUMBER()OVER(ORDER BY A.ASSET_ID) AS STT,

				A.ASSET_ID, A.[TYPE_ID],C.[TYPE_CODE],  C.[TYPE_NAME], A.GROUP_ID,B.GROUP_CODE, B.GROUP_NAME, A.ASSET_NAME, A.ASSET_DESC,

				A.SUP_ID, FORMAT(tmp.BUY_PRICE, '#,#') AS BUY_PRICE, A.AMORT_AMT, A.ASS_TYPE, TR.BRANCH_ID,BR_R.BRANCH_CODE,BR_R.BRANCH_NAME,H.DEP_ID, H.DEP_CODE,

				H.DEP_NAME, A.EMP_ID,EM_R.EMP_CODE,EM_R.EMP_NAME,

				A.BUY_DATE, A.USE_DATE, A.SPECIAL_ASS, CAST(A.AMORT_MONTH AS INT) AS AMORT_MONTH, A.AMORT_RATE, A.AMORT_START_DATE, A.AMORT_END_DATE,

				A.FIRST_AMORT_AMT, 

				--CAST(A.AMORTIZED_MONTH AS INT) AS AMORTIZED_MONTH, 

				CAST((A.AMORT_MONTH - A.AMORTIZED_MONTH) AS INT) AS REMAIN_MONTH,

--				CASE WHEN DATEDIFF(month,A.AMORT_START_DATE,@TRANSFER_DT) > A.AMORT_MONTH THEN  CAST(A.AMORT_MONTH AS INT) ELSE CAST(DATEDIFF(month,A.AMORT_START_DATE,@TRANSFER_DT)AS INT) END AS AMORTIZED_MONTH,

				--CAST(DATEDIFF(month,A.AMORT_START_DATE,tmp2.AMORT_DT)AS INT)  AS AMORTIZED_MONTH,

        ISNULL(CAST(tmp2.AMORTIZED_MONTH AS INT),0) AS AMORTIZED_MONTH,

				A.AMORTIZED_AMT, 

				FORMAT(tmp.BUY_PRICE - ISNULL(tmp1.AMORTIZED_AMT,0) , '#,#') AS GT_CL,  --So tien khau hao con lai

				A.PO_ID, A.WAREHOUSE_ID, A.LOCATION, A.REF_ASSET_ID,

				A.REF_AMORTIZED_AMT,

				A.WARRANTY_MONTHS, 				

				A.AMORT_STATUS, 

				D.STATUS_NAME AMORT_STATUS_NAME, 

				A.ASS_STATUS, CU.UNIT_NAME,

				E.STATUS_NAME ASS_STATUS_NAME, --Tinh trang tai san

				A.ASS_STATUS_DESC,

				A.RECORD_STATUS, 

				A.AUTH_STATUS, 

				ZZ.AUTH_STATUS_NAME,

				A.MAKER_ID, 

				convert(varchar, A.CREATE_DT, 103) as CREATE_DT_ASS,

				convert(varchar, TR.CREATE_DT, 103) as CREATE_DT, 

				A.CHECKER_ID, 

				A.APPROVE_DT,

				TR.[DESCRIPTION], TR.BRANCH_CREATE,TR.LOCATION,TR.[DESCRIPTION],TR.TRANSFER_MULTI_ID,

				A.AMORT_AMT-A.AMORTIZED_AMT ASREMAIN_AMT, convert(varchar, B.CREATE_DT, 103) as CREATE_DT_ASS, 

				1 AS SOLUONG,

				TR.DESCRIPTION AS NOTES,

				A.ASSET_CODE,

--                CASE

--                	WHEN A.REQ_CODE IS NOT NULL AND A.PL IS NOT NULL THEN A.REQ_CODE + '/' + A.PL 

--                	WHEN A.REQ_CODE IS NOT NULL AND A.PL IS NULL  THEN A.REQ_CODE

--                	WHEN A.REQ_CODE IS NULL AND A.PL IS NOT NULL  THEN A.PL ELSE NULL

--                END AS PYC_PL

                ATMM.REQ_CODE AS PYC_PL

		FROM [dbo].[ASS_TRANSFER_MULTI_DT] TR

        LEFT JOIN [ASS_TRANSFER_MULTI_MASTER] ATMM ON TR.TRANS_MULTI_MASTER_ID = ATMM.TRANS_MULTI_MASTER_ID

		LEFT JOIN ASS_MASTER A ON A.ASSET_ID = TR.ASSET_ID

		LEFT JOIN ASS_GROUP B ON A.GROUP_ID = B.GROUP_ID

		LEFT JOIN ASS_TYPE C ON A.[TYPE_ID] = C.[TYPE_ID]

        LEFT JOIN CM_UNIT CU ON B.UNIT = CU.UNIT_ID

		LEFT JOIN ASS_AMORT_STATUS D ON A.AMORT_STATUS = D.STATUS_CODE

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

		LEFT JOIN CM_AUTH_STATUS ZZ ON ZZ.AUTH_STATUS = TR.AUTH_STATUS

		LEFT JOIN CM_BRANCH BR_R ON BR_R.BRANCH_ID=TR.BRANCH_ID

		LEFT JOIN CM_EMPLOYEE EM_R ON EM_R.EMP_ID=TR.EMP_ID

		LEFT JOIN CM_DEPARTMENT H ON H.DEP_ID = TR.DEPT_ID

    --  PHONGNT 4/9/22 LẤY GIÁ TRỊ NGUYÊN GIÁ, KHẤU HAO TẠI THỜI ĐIỂM NGÀY ĐIỀU CHUYỂN

		LEFT JOIN (SELECT ASSET_ID,(SUM(CASE WHEN CRDR='C' AND TRN_DT < @TRANSFER_DT AND  ASSET_AMT IS NOT NULL   THEN ASSET_AMT END) - ISNULL(SUM(CASE WHEN CRDR='D' AND TRN_DT < @TRANSFER_DT AND  ASSET_AMT IS NOT NULL  THEN ASSET_AMT END),0)) AS BUY_PRICE FROM dbo.ASS_VALUES GROUP BY ASSET_ID) tmp ON A.ASSET_ID=tmp.ASSET_ID

		LEFT JOIN (SELECT ASSET_ID,(SUM(CASE WHEN CRDR='C' AND AMORT_DT < @TRANSFER_DT AND AMORT_AMT IS NOT NULL THEN AMORT_AMT END) - ISNULL(SUM(CASE WHEN CRDR='D' AND AMORT_DT < @TRANSFER_DT AND AMORT_AMT IS NOT NULL THEN AMORT_AMT END),0)) AS AMORTIZED_AMT FROM dbo.ASS_AMORT_DT GROUP BY ASSET_ID) tmp1 ON A.ASSET_ID=tmp1.ASSET_ID

--    LEFT JOIN (SELECT ASSET_ID,max(AMORT_DT) AS AMORT_DT FROM ASS_AMORT_DT WHERE CRDR ='C' AND AMORT_DT<@TRANSFER_DT GROUP BY ASSET_ID) tmp2  ON tmp2.ASSET_ID = A.ASSET_ID

    LEFT JOIN (SELECT ASSET_ID,COUNT(*) AS AMORTIZED_MONTH FROM ASS_AMORT_DT WHERE CRDR ='C' AND AMORT_DT<@TRANSFER_DT GROUP BY ASSET_ID) tmp2  ON tmp2.ASSET_ID = A.ASSET_ID

    --    END

WHERE 1=1 

AND TR.TRANS_MULTI_MASTER_ID= @TRANS_MULTI_MASTER_ID

IF(CONVERT(VARCHAR,@CREATE_DT_TRANSFER,103)<CONVERT(VARCHAR,@TRANSFER_DT,103))

BEGIN

    IF(@SIGN_APPROVE_DT_KT IS NOT NULL)

  BEGIN

  	SET @SIGN_APPROVE_DT = @TRANSFER_DT

  	SET @SIGN_APPROVE_DT_KT = @TRANSFER_DT

  	SET @SIGN_RECEVI_DT = @TRANSFER_DT

    SET @SIGN_CREATE_DT = @TRANSFER_DT

  END

END

SET @CREATE_DT = N'Ngày ' + CAST(DAY(@TRANSFER_DT) AS VARCHAR(500))

				+ N' tháng ' + CAST(MONTH(@TRANSFER_DT) AS VARCHAR(500))

				+ N' năm ' + CAST(YEAR(@TRANSFER_DT) AS VARCHAR(500))

				+ N' tại ' + (SELECT B.BRANCH_NAME FROM dbo.ASS_TRANSFER_MULTI_MASTER A JOIN dbo.CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

--CHECK XEM CÓ K/PB/TT ĐIỀU CHUYỂN không, không có thì lấy BRANCH

DECLARE @GIAO NVARCHAR(500)

IF(@KHOI_TRANSFER IS NOT NULL AND @DEP_TRANSFER IS NOT NULL AND @TT_TRANSFER IS NOT NULL)

BEGIN

	SET @GIAO = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @DEP_TRANSFER)

END

ELSE IF(@KHOI_TRANSFER IS NULL AND @DEP_TRANSFER IS NULL AND @TT_TRANSFER IS NULL)

BEGIN

	SET @GIAO = (SELECT CD.BRANCH_NAME FROM CM_BRANCH CD WHERE CD.BRANCH_ID = @BRANCH_TRANSFER)

END

ELSE IF(@KHOI_TRANSFER IS NULL AND @DEP_TRANSFER IS NULL AND @TT_TRANSFER IS NOT NULL)

BEGIN

	SET @GIAO = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @TT_TRANSFER) 

END

ELSE IF(@KHOI_TRANSFER IS NULL AND @DEP_TRANSFER IS NOT NULL AND @TT_TRANSFER IS NULL)

BEGIN

	SET @GIAO = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @DEP_TRANSFER) 

END

ELSE IF(@KHOI_TRANSFER IS NOT NULL AND @DEP_TRANSFER IS NULL AND @TT_TRANSFER IS NULL)

BEGIN

	SET @GIAO = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @KHOI_TRANSFER) 

END

-- THÔNG TIN BÊN GIAO Table1

--IF(@REQ_ID IS NOT NULL OR @REQ_ID <> '')

--BEGIN

--	SELECT 

--    IIF((SELECT CB.BRANCH_TYPE FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @REQ_BRANCH) = 'HS', (SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @REQ_BRANCH) + ' - ' + ISNULL(@REQ_DEP_NAME,''), (SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @REQ_BRANCH)) 

--    AS KHOI_TRANSFER, (SELECT CD.BRANCH_NAME FROM CM_BRANCH CD WHERE CD.BRANCH_ID = @REQ_BRANCH) AS BRANCH_TRANSFER, @REQ_MAKER_NAME AS NAME_TRANSFER, @REQ_MAKER_ADDR AS ADDR_TRANSFER, @REQ_MAKER_PHONE AS PHONE_TRANSFER, @REQ_POS AS POSITION_SEND, @CREATE_DT AS A1

--

--END

--ELSE

BEGIN

--    SELECT 

--    IIF((SELECT CB.BRANCH_TYPE FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_TRANSFER) = 'HS', (SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_TRANSFER) + ' - ' + ISNULL(@GIAO,''), (SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_TRANSFER)) 

--    AS KHOI_TRANSFER, (SELECT CD.BRANCH_NAME FROM CM_BRANCH CD WHERE CD.BRANCH_ID = @BRANCH_TRANSFER) AS BRANCH_TRANSFER, @MAKER_NAME AS NAME_TRANSFER, @ADDR_TRANSFER AS ADDR_TRANSFER, @PHONE_TRANSFER AS PHONE_TRANSFER, @MAKER_POS AS POSITION_SEND, @CREATE_DT AS A1

      SELECT 

      IIF((SELECT CB.BRANCH_TYPE FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_TRANSFER) = 'HS', (SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_TRANSFER) + ' - ' + ISNULL(@GIAO,''), (SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_TRANSFER)) 

      AS KHOI_TRANSFER, (SELECT CD.BRANCH_NAME FROM CM_BRANCH CD WHERE CD.BRANCH_ID = @BRANCH_TRANSFER) AS BRANCH_TRANSFER, @NAME_TRANSFER AS NAME_TRANSFER, @ADDR_TRANSFER AS ADDR_TRANSFER, @PHONE_TRANSFER AS PHONE_TRANSFER, @POS_TRANSFER AS POSITION_SEND, @CREATE_DT AS A1

END

--CHECK XEM CÓ K/PB/TT NHẬN không, không có thì lấy BRANCH

DECLARE @NHAN NVARCHAR(500)

IF(@KHOI_RECIVE IS NOT NULL AND @DEP_RECIVE IS NOT NULL AND @TT_RECIVE IS NOT NULL)

BEGIN

	SET @NHAN = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @DEP_RECIVE) 

END

ELSE IF(@KHOI_RECIVE IS NULL AND @DEP_RECIVE IS NULL AND @TT_RECIVE IS NULL)

BEGIN

	SET @NHAN = (SELECT CD.BRANCH_NAME FROM CM_BRANCH CD WHERE CD.BRANCH_ID = @BRANCH_RECIVE) 

END

ELSE IF(@KHOI_RECIVE IS NULL AND @DEP_RECIVE IS NULL AND @TT_RECIVE IS NOT NULL)

BEGIN

	SET @NHAN = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @TT_RECIVE)  

END

ELSE IF(@KHOI_RECIVE IS NULL AND @DEP_RECIVE IS NOT NULL AND @TT_RECIVE IS NULL)

BEGIN

	SET @NHAN = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @DEP_RECIVE)  

END

ELSE IF(@KHOI_RECIVE IS NOT NULL AND @DEP_RECIVE IS NULL AND @TT_RECIVE IS NULL)

BEGIN

	SET @NHAN = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @KHOI_RECIVE)  

END

-- THÔNG TIN BÊN NHẬN Table2

SELECT IIF((SELECT CB.BRANCH_TYPE FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_RECIVE) = 'HS', (SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_RECIVE) + ' - ' + ISNULL(@NHAN,''), (SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_RECIVE))

 AS KHOI_RECEIVE, (SELECT CD.BRANCH_NAME FROM CM_BRANCH CD WHERE CD.BRANCH_ID = @BRANCH_RECIVE) AS BRANCH_RECIVE, @NAME_RECIVE AS NAME_RECIVE, @ADDR_RECIVE AS ADDR_RECIVE, @PHONE_RECIVE AS PHONE_RECIVE, @POSITION_RECIVE AS POSTION_RECIVE

-- CHỮ KÍ

DECLARE @S1 NVARCHAR(500),@S2 NVARCHAR(500),@S3 NVARCHAR(500),@S4 NVARCHAR(500),@S5 NVARCHAR(500),@S6 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 @S2 = N'Người duyệt'+ CHAR(10) + (SELECT TLFullName FROM TL_USER WHERE TLNANME = @SIGN_APPROVE_ID) + CHAR(10) + N' Đã xác nhận vào lúc ' + ISNULL(FORMAT(@SIGN_APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'')

--PHONGNT bổ sung người nhập KT

SET @S3 =N'Người nhập'+ CHAR(10) + (SELECT TLFullName FROM TL_USER WHERE TLNANME = @SIGN_MAKER_ID_KT) + CHAR(10) 

SET @S6 =N'Người duyệt'+ CHAR(10) + (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'),'')

--PHONGNT bổ sung người nhập 

SET @S5 =N'Người nhập'+ CHAR(10) + (SELECT TLFullName FROM TL_USER WHERE TLNANME = @SIGN_MAKER_ID_CREATE) + CHAR(10) ;

-- Table-3 Chữ ký

SELECT 

@NAME_TRANSFER AS S1,

ISNULL(@POS_TRANSFER, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT(@TRANSFER_CONFIRM_DT, 'dd/MM/yyyy HH:mm:ss')  AS S1_POS,

@SIGN_QLTS_NAME AS S2,

ISNULL(@SIGN_QLTS_POS, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT(@SIGN_QLTS_DT, 'dd/MM/yyyy HH:mm:ss') AS S2_POS,

--'' AS S2,

--'' AS S2_POS,

@NAME_RECIVE AS S3,@S4 AS S4,

ISNULL(@POS_RECIVE,'')  + CHAR(13) + N'Đã ký ngày ' + FORMAT(@RECEIVE_CONFIRM_DT, 'dd/MM/yyyy HH:mm:ss') AS S3_POS,

IIF(NOT EXISTS(SELECT 1 FROM ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID=@TRANS_MULTI_MASTER_ID AND AUTH_STATUS ='E'),@S5,NULL) AS S5,

@S6 AS S6

-- TABLE4 thông tin trung gian *(nếu có)

SELECT @SIGN_QLTS_BR+ ' - ' + ISNULL(@SIGN_QLTS_DEP,'') AS KHOI_MID, @SIGN_QLTS_BR AS BRANCH_MID,

@SIGN_QLTS_NAME AS MID_NAME, @SIGN_QLTS_ADDR AS MID_ADDR, @SIGN_QLTS_PHONE AS MID_PHONE, @SIGN_QLTS_POS AS MID_POS

--SELECT '' AS MID_NAME, '' AS MID_ADDR, '' AS MID_PHONE, '' AS MID_POS

--TABLE 5 NỘI DUNG BIÊN BẢN

SELECT TRANSFER_CONTENT AS TITLE FROM ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID

--TABLE 6 SỐ

SELECT @TRANS_MULTI_MASTER_ID AS NO

SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_RECIVE