|
1
|
ALTER PROC dbo.ASS_USE_MULTI_HANDOVER_RECORD
|
|
2
|
@USER_MASTER_ID VARCHAR(15) = NULL
|
|
3
|
AS
|
|
4
|
BEGIN
|
|
5
|
--Table 0 Nội dung && BRANCH
|
|
6
|
SELECT N'Hội sở - PHÒNG QUẢN LÝ TÀI SẢN' AS BRANCH_NAME,
|
|
7
|
(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,
|
|
8
|
A.CONTENT AS TITLE
|
|
9
|
FROM ASS_USE_MULTI_MASTER A
|
|
10
|
WHERE A.USER_MASTER_ID = @USER_MASTER_ID
|
|
11
|
|
|
12
|
--Table 1 Bên giao
|
|
13
|
SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(D.BRANCH_NAME,'') + ISNULL(' - ' + E.DEP_NAME,'') AS BRANCH_NAME
|
|
14
|
FROM ASS_USE_MULTI_MASTER A
|
|
15
|
LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
|
|
16
|
LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
|
|
17
|
LEFT JOIN CM_BRANCH D ON A.BRANCH_CREATE = D.BRANCH_ID
|
|
18
|
LEFT JOIN CM_DEPARTMENT E ON A.DEPT_CREATE = E.DEP_ID
|
|
19
|
WHERE A.USER_MASTER_ID = @USER_MASTER_ID
|
|
20
|
|
|
21
|
--Table 2 Bên nhận
|
|
22
|
--Fix tạm UAT
|
|
23
|
IF(EXISTS(SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME
|
|
24
|
FROM ASS_USE_CONFIRM_MASTER A
|
|
25
|
LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
|
|
26
|
LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
|
|
27
|
WHERE A.USER_MASTER_ID = @USER_MASTER_ID))
|
|
28
|
BEGIN
|
|
29
|
SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(D.BRANCH_NAME,'') + ISNULL(' - ' + E.DEP_NAME,'') AS BRANCH_NAME
|
|
30
|
FROM ASS_USE_CONFIRM_MASTER A
|
|
31
|
LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
|
|
32
|
LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
|
|
33
|
LEFT JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID
|
|
34
|
LEFT JOIN CM_DEPARTMENT E ON A.DEP_ID = E.DEP_ID
|
|
35
|
WHERE A.USER_MASTER_ID = @USER_MASTER_ID
|
|
36
|
END
|
|
37
|
ELSE
|
|
38
|
BEGIN
|
|
39
|
SELECT '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME
|
|
40
|
END
|
|
41
|
|
|
42
|
--TABLE 3 DANH SÁCH TÀI SẢN
|
|
43
|
SELECT CASE
|
|
44
|
WHEN B.ASSET_CODE IS NOT NULL AND B.ASSET_CODE <> '' THEN B.ASSET_CODE
|
|
45
|
ELSE B.ASS_CODE_TMP
|
|
46
|
END AS ASSET_CODE, B.ASSET_NAME, D.UNIT_NAME, 1 AS QUANTITY,
|
|
47
|
E.STATUS_NAME, B.ASSET_SERIAL_NO AS SERIAL, R.REQ_CODE, B.NOTES AS QUY_CACH, A.NOTES, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT
|
|
48
|
,CE.EMP_NAME
|
|
49
|
FROM ASS_USE_MULTI_DT A
|
|
50
|
LEFT JOIN TR_REQUEST_SHOP_DOC R ON A.REQ_ID = R.REQ_ID
|
|
51
|
LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID
|
|
52
|
LEFT JOIN ASS_GROUP C ON B.GROUP_ID = C.GROUP_ID
|
|
53
|
LEFT JOIN CM_UNIT D ON C.UNIT = D.UNIT_ID
|
|
54
|
LEFT JOIN ASS_STATUS E ON B.ASS_STATUS = E.STATUS_ID
|
|
55
|
LEFT JOIN CM_EMPLOYEE CE ON A.EMP_ID = CE.EMP_ID
|
|
56
|
WHERE A.USER_MASTER_ID = @USER_MASTER_ID
|
|
57
|
|
|
58
|
--TABLE 4 CHỮ KÝ
|
|
59
|
SELECT TOP 1 E.TLFullName AS CHECKER_NAME, --BÊN NHẬN
|
|
60
|
ISNULL(C.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP
|
|
61
|
WHERE PP.REQ_ID = A.USER_MASTER_ID AND PP.PROCESS_ID = 'CONFIRM'
|
|
62
|
AND PP.CHECKER_ID = B.MAKER_ID
|
|
63
|
ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME_2,--BÊN NHẬN
|
|
64
|
D.TLFullName AS MAKER_NAME, --BÊN GIAO
|
|
65
|
--G.TLFullName AS INTERMEDIATE_UNIT_NAME, --ĐƠN VỊ TRUNG GIAN
|
|
66
|
ISNULL(F.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP
|
|
67
|
WHERE PP.REQ_ID = A.USER_MASTER_ID AND PP.PROCESS_ID = 'SEND'
|
|
68
|
AND PP.CHECKER_ID = A.MAKER_ID
|
|
69
|
ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME --BÊN GIAO
|
|
70
|
--H.POS_NAME AS POS_NAME_1 -- ĐƠN VỊ TRUNG GIAN
|
|
71
|
FROM ASS_USE_MULTI_MASTER A
|
|
72
|
LEFT JOIN ASS_USE_CONFIRM_MASTER B ON A.USER_MASTER_ID = B.USER_MASTER_ID
|
|
73
|
LEFT JOIN TL_USER E ON B.MAKER_ID = E.TLNANME --BÊN NHẬN
|
|
74
|
LEFT JOIN CM_EMPLOYEE_LOG C ON B.MAKER_ID = C.USER_DOMAIN --BÊN NHẬN
|
|
75
|
LEFT JOIN TL_USER D ON A.MAKER_ID = D.TLNANME --BÊN GIAO
|
|
76
|
LEFT JOIN CM_EMPLOYEE_LOG F ON D.TLNANME = F.USER_DOMAIN
|
|
77
|
--LEFT JOIN TL_USER G ON A.CHECKER_ID = G.TLNANME -- DVTG
|
|
78
|
--LEFT JOIN CM_EMPLOYEE_LOG H ON A.CHECKER_ID = H.USER_DOMAIN --DVTG
|
|
79
|
WHERE A.USER_MASTER_ID = @USER_MASTER_ID
|
|
80
|
|
|
81
|
--TABLE 5 SỐ
|
|
82
|
SELECT @USER_MASTER_ID AS NO
|
|
83
|
|
|
84
|
--TABLE 6 ĐƠN VỊ TRUNG GIAN
|
|
85
|
SELECT '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME
|
|
86
|
--SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(D.BRANCH_NAME,'') + ISNULL(' - ' + E.DEP_NAME,'') AS BRANCH_NAME
|
|
87
|
--FROM ASS_USE_MULTI_MASTER A
|
|
88
|
--LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
|
|
89
|
--LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
|
|
90
|
--LEFT JOIN CM_BRANCH D ON A.BRANCH_CREATE = D.BRANCH_ID
|
|
91
|
--LEFT JOIN CM_DEPARTMENT E ON A.DEPT_CREATE = E.DEP_ID
|
|
92
|
--WHERE A.USER_MASTER_ID = @USER_MASTER_ID
|
|
93
|
|
|
94
|
END
|