|
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
|
--Table 1 Bên giao
|
|
12
|
SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(D.BRANCH_NAME,'') + ISNULL(' - ' + E.DEP_NAME,'') AS BRANCH_NAME
|
|
13
|
FROM ASS_USE_MULTI_MASTER A
|
|
14
|
LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
|
|
15
|
LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
|
|
16
|
LEFT JOIN CM_BRANCH D ON A.BRANCH_CREATE = D.BRANCH_ID
|
|
17
|
LEFT JOIN CM_DEPARTMENT E ON A.DEPT_CREATE = E.DEP_ID
|
|
18
|
WHERE A.USER_MASTER_ID = @USER_MASTER_ID
|
|
19
|
--Table 2 Bên nhận
|
|
20
|
--Fix tạm UAT
|
|
21
|
IF(EXISTS(SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME
|
|
22
|
FROM ASS_USE_CONFIRM_MASTER A
|
|
23
|
LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
|
|
24
|
LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
|
|
25
|
WHERE A.USER_MASTER_ID = @USER_MASTER_ID))
|
|
26
|
BEGIN
|
|
27
|
SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(D.BRANCH_NAME,'') + ISNULL(' - ' + E.DEP_NAME,'') AS BRANCH_NAME
|
|
28
|
FROM ASS_USE_CONFIRM_MASTER A
|
|
29
|
LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
|
|
30
|
LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
|
|
31
|
LEFT JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID
|
|
32
|
LEFT JOIN CM_DEPARTMENT E ON A.DEP_ID = E.DEP_ID
|
|
33
|
WHERE A.USER_MASTER_ID = @USER_MASTER_ID
|
|
34
|
END
|
|
35
|
ELSE
|
|
36
|
BEGIN
|
|
37
|
SELECT '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME
|
|
38
|
END
|
|
39
|
--TABLE 3 DANH SÁCH TÀI SẢN
|
|
40
|
SELECT CASE
|
|
41
|
WHEN B.ASSET_CODE IS NOT NULL AND B.ASSET_CODE <> '' THEN B.ASSET_CODE
|
|
42
|
ELSE B.ASS_CODE_TMP
|
|
43
|
END AS ASSET_CODE, B.ASSET_NAME, D.UNIT_NAME, 1 AS QUANTITY,
|
|
44
|
E.STATUS_NAME, B.ASSET_SERIAL_NO AS SERIAL, R.REQ_CODE + ISNULL(CHAR(10) + CHAR(10) + B.PL,'') AS REQ_CODE, B.NOTES AS QUY_CACH, A.NOTES, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT
|
|
45
|
,CE.EMP_NAME
|
|
46
|
FROM ASS_USE_MULTI_DT A
|
|
47
|
LEFT JOIN TR_REQUEST_SHOP_DOC R ON A.REQ_ID = R.REQ_ID
|
|
48
|
LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID
|
|
49
|
LEFT JOIN ASS_GROUP C ON B.GROUP_ID = C.GROUP_ID
|
|
50
|
LEFT JOIN CM_UNIT D ON C.UNIT = D.UNIT_ID
|
|
51
|
LEFT JOIN ASS_STATUS E ON B.ASS_STATUS = E.STATUS_ID
|
|
52
|
LEFT JOIN CM_EMPLOYEE CE ON A.EMP_ID = CE.EMP_ID
|
|
53
|
WHERE A.USER_MASTER_ID = @USER_MASTER_ID
|
|
54
|
--TABLE 4 CHỮ KÝ
|
|
55
|
SELECT TOP 1 E.TLFullName AS CHECKER_NAME, --BÊN NHẬN
|
|
56
|
ISNULL(C.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP
|
|
57
|
WHERE PP.REQ_ID = A.USER_MASTER_ID AND PP.PROCESS_ID = 'CONFIRM'
|
|
58
|
AND PP.CHECKER_ID = B.MAKER_ID
|
|
59
|
ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME_2,--BÊN NHẬN
|
|
60
|
D.TLFullName AS MAKER_NAME, --BÊN GIAO
|
|
61
|
--G.TLFullName AS INTERMEDIATE_UNIT_NAME, --ĐƠN VỊ TRUNG GIAN
|
|
62
|
ISNULL(F.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP
|
|
63
|
WHERE PP.REQ_ID = A.USER_MASTER_ID AND PP.PROCESS_ID = 'SEND'
|
|
64
|
AND PP.CHECKER_ID = A.MAKER_ID
|
|
65
|
ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME --BÊN GIAO
|
|
66
|
--H.POS_NAME AS POS_NAME_1 -- ĐƠN VỊ TRUNG GIAN
|
|
67
|
FROM ASS_USE_MULTI_MASTER A
|
|
68
|
LEFT JOIN ASS_USE_CONFIRM_MASTER B ON A.USER_MASTER_ID = B.USER_MASTER_ID
|
|
69
|
LEFT JOIN TL_USER E ON B.MAKER_ID = E.TLNANME --BÊN NHẬN
|
|
70
|
LEFT JOIN CM_EMPLOYEE_LOG C ON B.MAKER_ID = C.USER_DOMAIN --BÊN NHẬN
|
|
71
|
LEFT JOIN TL_USER D ON A.MAKER_ID = D.TLNANME --BÊN GIAO
|
|
72
|
LEFT JOIN CM_EMPLOYEE_LOG F ON D.TLNANME = F.USER_DOMAIN
|
|
73
|
--LEFT JOIN TL_USER G ON A.CHECKER_ID = G.TLNANME -- DVTG
|
|
74
|
--LEFT JOIN CM_EMPLOYEE_LOG H ON A.CHECKER_ID = H.USER_DOMAIN --DVTG
|
|
75
|
WHERE A.USER_MASTER_ID = @USER_MASTER_ID
|
|
76
|
--TABLE 5 SỐ
|
|
77
|
SELECT @USER_MASTER_ID AS NO
|
|
78
|
--TABLE 6 ĐƠN VỊ TRUNG GIAN
|
|
79
|
SELECT '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME
|
|
80
|
--SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(D.BRANCH_NAME,'') + ISNULL(' - ' + E.DEP_NAME,'') AS BRANCH_NAME
|
|
81
|
--FROM ASS_USE_MULTI_MASTER A
|
|
82
|
--LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
|
|
83
|
--LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
|
|
84
|
--LEFT JOIN CM_BRANCH D ON A.BRANCH_CREATE = D.BRANCH_ID
|
|
85
|
--LEFT JOIN CM_DEPARTMENT E ON A.DEPT_CREATE = E.DEP_ID
|
|
86
|
--WHERE A.USER_MASTER_ID = @USER_MASTER_ID
|
|
87
|
END
|
|
88
|
GO
|
|
89
|
|
|
90
|
ALTER PROC dbo.ASS_TRANSFER_MULTI_MASTER_BBBG
|
|
91
|
@TRANS_MULTI_MASTER_ID varchar(15) = null
|
|
92
|
AS
|
|
93
|
DECLARE @REQ_ID VARCHAR(20) = (SELECT REQ_ID FROM ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)
|
|
94
|
DECLARE @REQ_MAKER VARCHAR(110) = (SELECT TRSD.MAKER_ID FROM TR_REQUEST_SHOP_DOC TRSD WHERE TRSD.REQ_ID = @REQ_ID)
|
|
95
|
DECLARE @REQ_MAKER_DT VARCHAR(110) = (SELECT TRSD.CREATE_DT FROM TR_REQUEST_SHOP_DOC TRSD WHERE TRSD.REQ_ID = @REQ_ID)
|
|
96
|
DECLARE @REQ_BRANCH VARCHAR(110) = (SELECT TRSD.BRANCH_ID FROM TR_REQUEST_SHOP_DOC TRSD WHERE TRSD.REQ_ID = @REQ_ID)
|
|
97
|
DECLARE @REQ_DEP VARCHAR(110) = (SELECT TRSD.DEP_ID FROM TR_REQUEST_SHOP_DOC TRSD WHERE TRSD.REQ_ID = @REQ_ID)
|
|
98
|
DECLARE @REQ_DEP_NAME NVARCHAR(510) = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @REQ_DEP)
|
|
99
|
DECLARE @REQ_POS NVARCHAR(510) = (SELECT CEL.POS_NAME FROM CM_EMPLOYEE_LOG CEL WHERE CEL.USER_DOMAIN = @REQ_MAKER)
|
|
100
|
DECLARE @REQ_MAKER_NAME NVARCHAR(500) = (SELECT TU.TLFullName FROM TL_USER TU WHERE TU.TLNANME = @REQ_MAKER)
|
|
101
|
DECLARE @REQ_MAKER_PHONE NVARCHAR(500) = (SELECT TU.PHONE FROM TL_USER TU WHERE TU.TLNANME = @REQ_MAKER)
|
|
102
|
DECLARE @REQ_MAKER_ADDR NVARCHAR(500) = (SELECT TU.ADDRESS FROM TL_USER TU WHERE TU.TLNANME = @REQ_MAKER)
|
|
103
|
--Khiemchg lấy danh sách đơn vị nhận và cho
|
|
104
|
DECLARE @INFO_TRANSFER_RECIEVE TABLE
|
|
105
|
(
|
|
106
|
BRN_SD VARCHAR(50),
|
|
107
|
BRN_SD_NAME NVARCHAR(500),
|
|
108
|
K_SD VARCHAR(50),
|
|
109
|
K_SD_NAME NVARCHAR(500),
|
|
110
|
TT_SD VARCHAR(50),
|
|
111
|
TT_SD_NAME NVARCHAR(500),
|
|
112
|
DEP_SD VARCHAR(50),
|
|
113
|
DEP_SD_NAME NVARCHAR(500),
|
|
114
|
PER_SD VARCHAR(50),
|
|
115
|
BRN_N VARCHAR(50),
|
|
116
|
BRN_N_NAME NVARCHAR(500),
|
|
117
|
K_N VARCHAR(50),
|
|
118
|
K_N_NAME NVARCHAR(500),
|
|
119
|
TT_N VARCHAR(50),
|
|
120
|
TT_N_NAME NVARCHAR(500),
|
|
121
|
DEP_N VARCHAR(50),
|
|
122
|
DEP_N_NAME NVARCHAR(500),
|
|
123
|
PER_N VARCHAR(50)
|
|
124
|
)
|
|
125
|
INSERT INTO @INFO_TRANSFER_RECIEVE
|
|
126
|
SELECT DISTINCT BRANCH_ID_OLD,
|
|
127
|
BB.BRANCH_NAME,
|
|
128
|
KHOI_ID_OLD,
|
|
129
|
BD.DEP_NAME,
|
|
130
|
CENTER_ID_OLD,
|
|
131
|
BF.DEP_NAME,
|
|
132
|
DEPT_ID_OLD,
|
|
133
|
BE.DEP_NAME,
|
|
134
|
EMP_ID_OLD,
|
|
135
|
A.BRANCH_ID,
|
|
136
|
CB.BRANCH_NAME,
|
|
137
|
A.KHOI_ID,
|
|
138
|
CD.DEP_NAME,
|
|
139
|
CENTER_ID,
|
|
140
|
CF.DEP_NAME,
|
|
141
|
DEPT_ID,
|
|
142
|
CE.DEP_NAME,
|
|
143
|
EMP_ID
|
|
144
|
FROM ASS_TRANSFER_MULTI_DT A
|
|
145
|
LEFT JOIN CM_BRANCH BB ON A.BRANCH_ID_OLD = BB.BRANCH_ID
|
|
146
|
LEFT JOIN CM_DEPARTMENT BD ON BD.DEP_ID = A.KHOI_ID_OLD
|
|
147
|
LEFT JOIN CM_DEPARTMENT BE ON BE.DEP_ID = A.DEPT_ID_OLD
|
|
148
|
LEFT JOIN CM_DEPARTMENT BF ON BF.DEP_ID = A.CENTER_ID_OLD
|
|
149
|
|
|
150
|
LEFT JOIN CM_BRANCH CB ON A.BRANCH_ID = CB.BRANCH_ID
|
|
151
|
LEFT JOIN CM_DEPARTMENT CD ON CD.DEP_ID = A.KHOI_ID
|
|
152
|
LEFT JOIN CM_DEPARTMENT CE ON CE.DEP_ID = A.DEPT_ID
|
|
153
|
LEFT JOIN CM_DEPARTMENT CF ON CF.DEP_ID = A.CENTER_ID
|
|
154
|
WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID
|
|
155
|
GROUP BY BRANCH_ID_OLD,
|
|
156
|
BB.BRANCH_NAME,
|
|
157
|
KHOI_ID_OLD,
|
|
158
|
BD.DEP_NAME,
|
|
159
|
CENTER_ID_OLD,
|
|
160
|
BF.DEP_NAME,
|
|
161
|
DEPT_ID_OLD,
|
|
162
|
BE.DEP_NAME,
|
|
163
|
EMP_ID_OLD,
|
|
164
|
A.BRANCH_ID,
|
|
165
|
CB.BRANCH_NAME,
|
|
166
|
A.KHOI_ID,
|
|
167
|
CD.DEP_NAME,
|
|
168
|
CENTER_ID,
|
|
169
|
CF.DEP_NAME,
|
|
170
|
DEPT_ID,
|
|
171
|
CE.DEP_NAME,
|
|
172
|
EMP_ID
|
|
173
|
--DELETE @INFO_TRANSFER_RECIEVE WHERE (BRN_SD = BRN_N AND DEP_SD = DEP_N)
|
|
174
|
|
|
175
|
DECLARE @SIGN_QLTS_NAME NVARCHAR(500) = ''
|
|
176
|
DECLARE @SIGN_QLTS_PHONE NVARCHAR(500) = ''
|
|
177
|
DECLARE @SIGN_QLTS_ADDR NVARCHAR(500) = ''
|
|
178
|
DECLARE @SIGN_QLTS_POS NVARCHAR(500) = ''
|
|
179
|
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)
|
|
180
|
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)
|
|
181
|
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)
|
|
182
|
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)
|
|
183
|
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))
|
|
184
|
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))
|
|
185
|
DECLARE @MAKER_NAME NVARCHAR(500) = (SELECT TU.TLFullName FROM TL_USER TU WHERE TU.TLNANME = @MAKER_ID)
|
|
186
|
DECLARE @MAKER_POS NVARCHAR(500) = (SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@MAKER_ID)
|
|
187
|
|
|
188
|
|
|
189
|
IF(EXISTS (SELECT * FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS', 'DV0001', 'DEP000000000048') FGUBRV WHERE FGUBRV.TLNANME = @SIGN_QLTS))
|
|
190
|
BEGIN
|
|
191
|
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
|
|
192
|
SET @SIGN_QLTS_POS = (SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN = @SIGN_QLTS)
|
|
193
|
END
|
|
194
|
-- Thông tin chữ kí
|
|
195
|
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,
|
|
196
|
@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)
|
|
197
|
|
|
198
|
-- Thông tin bên giao
|
|
199
|
DECLARE @DEP_TRANSFER NVARCHAR(1500) = (SELECT TOP 1 DEP_SD FROM @INFO_TRANSFER_RECIEVE)
|
|
200
|
DECLARE @BRANCH_TRANSFER NVARCHAR(500) = (SELECT TOP 1 BRN_SD FROM @INFO_TRANSFER_RECIEVE)
|
|
201
|
DECLARE @KHOI_TRANSFER NVARCHAR(500) = (SELECT TOP 1 K_SD FROM @INFO_TRANSFER_RECIEVE)
|
|
202
|
DECLARE @TT_TRANSFER NVARCHAR(500) = (SELECT TOP 1 TT_SD FROM @INFO_TRANSFER_RECIEVE)
|
|
203
|
DECLARE @POSITION NVARCHAR(500) --= (SELECT * FROM @INFO_TRANSFER_RECIEVE)
|
|
204
|
DECLARE @TRANSFER_MAKER_ID VARCHAR(100) = (SELECT TOP 1 PER_SD FROM @INFO_TRANSFER_RECIEVE)
|
|
205
|
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)
|
|
206
|
|
|
207
|
DECLARE @TRANSFER_CONFIRM NVARCHAR(500) = (SELECT TOP 1 PRP.CHECKER_ID FROM PL_REQUEST_PROCESS PRP
|
|
208
|
WHERE PRP.PROCESS_ID = 'XNGN' AND PRP.REQ_ID = @TRANS_MULTI_MASTER_ID
|
|
209
|
AND PRP.BRANCH_ID = @BRANCH_TRANSFER AND PRP.DEP_ID = ISNULL(@DEP_TRANSFER, NULL))
|
|
210
|
DECLARE @TRANSFER_CONFIRM_DT NVARCHAR(500) = (SELECT TOP 1 PRP.APPROVE_DT FROM PL_PROCESS PRP
|
|
211
|
WHERE PRP.PROCESS_ID = 'XNGN' AND PRP.REQ_ID = @TRANS_MULTI_MASTER_ID AND PRP.CHECKER_ID = @TRANSFER_CONFIRM ORDER BY PRP.APPROVE_DT DESC)
|
|
212
|
DECLARE @NAME_TRANSFER NVARCHAR(500) = (SELECT TU.TLFullName FROM TL_USER TU WHERE TU.TLNANME = @TRANSFER_CONFIRM)
|
|
213
|
DECLARE @POS_TRANSFER NVARCHAR(500) = (SELECT CEL.POS_NAME FROM CM_EMPLOYEE_LOG CEL WHERE CEL.USER_DOMAIN = @TRANSFER_CONFIRM)
|
|
214
|
DECLARE @PHONE_TRANSFER NVARCHAR(500) = (SELECT TU.PHONE FROM TL_USER TU WHERE TU.TLNANME = @TRANSFER_CONFIRM)
|
|
215
|
DECLARE @ADDR_TRANSFER NVARCHAR(500) = (SELECT TU.ADDRESS FROM TL_USER TU WHERE TU.TLNANME = @TRANSFER_CONFIRM)
|
|
216
|
|
|
217
|
-- Thông tin bên nhận
|
|
218
|
DECLARE @DEP_RECIVE NVARCHAR(1500) = (SELECT TOP 1 DEP_N FROM @INFO_TRANSFER_RECIEVE)
|
|
219
|
DECLARE @BRANCH_RECIVE NVARCHAR(500) = (SELECT TOP 1 BRN_N FROM @INFO_TRANSFER_RECIEVE)
|
|
220
|
DECLARE @KHOI_RECIVE NVARCHAR(500) = (SELECT TOP 1 K_N FROM @INFO_TRANSFER_RECIEVE)
|
|
221
|
DECLARE @TT_RECIVE NVARCHAR(500) = (SELECT TOP 1 TT_N FROM @INFO_TRANSFER_RECIEVE)
|
|
222
|
DECLARE @POSITION_RECIVE NVARCHAR(500)
|
|
223
|
DECLARE @RECIVE_MAKER_ID VARCHAR(100) = (SELECT TOP 1 PER_N FROM @INFO_TRANSFER_RECIEVE)
|
|
224
|
|
|
225
|
DECLARE @RECEIVE_CONFIRM NVARCHAR(500) = (SELECT TOP 1 PRP.CHECKER_ID FROM PL_REQUEST_PROCESS PRP
|
|
226
|
WHERE PRP.PROCESS_ID = 'XNGN' AND PRP.REQ_ID = @TRANS_MULTI_MASTER_ID
|
|
227
|
AND PRP.BRANCH_ID = @BRANCH_RECIVE AND PRP.DEP_ID = ISNULL(@DEP_RECIVE, NULL))
|
|
228
|
DECLARE @RECEIVE_CONFIRM_DT DATETIME = (SELECT TOP 1 PRP.APPROVE_DT FROM PL_PROCESS PRP
|
|
229
|
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)
|
|
230
|
DECLARE @NAME_RECIVE NVARCHAR(500) = (SELECT TU.TLFullName FROM TL_USER TU WHERE TU.TLNANME = @RECEIVE_CONFIRM)
|
|
231
|
DECLARE @POS_RECIVE NVARCHAR(500) = (SELECT CEL.POS_NAME FROM CM_EMPLOYEE_LOG CEL WHERE CEL.USER_DOMAIN = @RECEIVE_CONFIRM)
|
|
232
|
DECLARE @PHONE_RECIVE NVARCHAR(500) = (SELECT TU.PHONE FROM TL_USER TU WHERE TU.TLNANME = @RECEIVE_CONFIRM)
|
|
233
|
DECLARE @ADDR_RECIVE NVARCHAR(500) = (SELECT TU.ADDRESS FROM TL_USER TU WHERE TU.TLNANME = @RECEIVE_CONFIRM)
|
|
234
|
|
|
235
|
-- Thông tin Ngày điều chuyển
|
|
236
|
DECLARE @TRANSFER_DT DATETIME, @CREATE_DT_TRANSFER DATETIME
|
|
237
|
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
|
|
238
|
|
|
239
|
-- BÊN GIAO
|
|
240
|
-- Thông tin chữ kí
|
|
241
|
SET @SIGN_MAKER_ID = ( SELECT MAKER_ID_BG FROM ASS_TRANSFER_CONFIRM_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)
|
|
242
|
SET @SIGN_CREATE_DT = ( SELECT CONFIRM_DT_BG FROM ASS_TRANSFER_CONFIRM_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)
|
|
243
|
SET @SIGN_APPROVE_ID = (SELECT CHECKER_ID FROM ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)
|
|
244
|
SET @SIGN_APPROVE_DT = (SELECT APPROVE_DT FROM ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)
|
|
245
|
|
|
246
|
-- Thông tin bên giao
|
|
247
|
--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), '')
|
|
248
|
-- + 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), '')
|
|
249
|
--SET @NAME_TRANSFER =(SELECT TLFullName FROM TL_USER WHERE TLNANME =@TRANSFER_MAKER_ID)
|
|
250
|
SET @POSITION = (SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@TRANSFER_CONFIRM)
|
|
251
|
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))
|
|
252
|
+ 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))
|
|
253
|
+ 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))
|
|
254
|
+ 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)
|
|
255
|
|
|
256
|
-- BÊN NHẬN
|
|
257
|
-- Thông tin chữ kí
|
|
258
|
SET @SIGN_RECEVI_MAKER_ID = ( SELECT MAKER_ID_BN FROM ASS_TRANSFER_CONFIRM_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)
|
|
259
|
SET @SIGN_RECEVI_DT = ( SELECT CONFIRM_DT_BN FROM ASS_TRANSFER_CONFIRM_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)
|
|
260
|
-- Thông tin bên nhận
|
|
261
|
SET @RECIVE_MAKER_ID = (SELECT TOP 1 MAKER_ID_BN FROM ASS_TRANSFER_CONFIRM_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)
|
|
262
|
--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), '')
|
|
263
|
-- + 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), '')
|
|
264
|
--SET @DEP_RECIVE = (SELECT TOP 1 DEPT_ID FROM ASS_TRANSFER_MULTI_DT WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)
|
|
265
|
--SET @NAME_RECIVE= (SELECT TLFullName FROM TL_USER WHERE TLNANME = @RECIVE_MAKER_ID)
|
|
266
|
SET @POSITION_RECIVE = (SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN = @RECEIVE_CONFIRM)
|
|
267
|
--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
|
|
268
|
-- WHERE DEP_ID =@DEP_RECIVE)) AND IS_KHOI ='1')
|
|
269
|
|
|
270
|
|
|
271
|
-- KẾ TOÁN
|
|
272
|
|
|
273
|
SET @SIGN_MAKER_ID_CREATE = ( SELECT A.MAKER_ID FROM ASS_TRANSFER_MULTI_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)
|
|
274
|
SET @SIGN_CHECKER_ID_KT = ( SELECT CHECKER_ID_KT FROM ASS_TRANSFER_MULTI_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)
|
|
275
|
SET @SIGN_APPROVE_DT_KT = ( SELECT APPROVE_DT_KT FROM ASS_TRANSFER_MULTI_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)
|
|
276
|
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)
|
|
277
|
|
|
278
|
-- DANH SÁCH TÀI SẢN
|
|
279
|
SELECT ROW_NUMBER()OVER(ORDER BY A.ASSET_ID) AS STT,
|
|
280
|
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,
|
|
281
|
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,
|
|
282
|
H.DEP_NAME, A.EMP_ID,EM_R.EMP_CODE,EM_R.EMP_NAME,
|
|
283
|
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,
|
|
284
|
A.FIRST_AMORT_AMT,
|
|
285
|
--CAST(A.AMORTIZED_MONTH AS INT) AS AMORTIZED_MONTH,
|
|
286
|
CAST((A.AMORT_MONTH - A.AMORTIZED_MONTH) AS INT) AS REMAIN_MONTH,
|
|
287
|
-- 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,
|
|
288
|
--CAST(DATEDIFF(month,A.AMORT_START_DATE,tmp2.AMORT_DT)AS INT) AS AMORTIZED_MONTH,
|
|
289
|
ISNULL(CAST(tmp2.AMORTIZED_MONTH AS INT),0) AS AMORTIZED_MONTH,
|
|
290
|
A.AMORTIZED_AMT,
|
|
291
|
FORMAT(tmp.BUY_PRICE - ISNULL(tmp1.AMORTIZED_AMT,0) , '#,#') AS GT_CL, --So tien khau hao con lai
|
|
292
|
A.PO_ID, A.WAREHOUSE_ID, A.LOCATION, A.REF_ASSET_ID,
|
|
293
|
A.REF_AMORTIZED_AMT,
|
|
294
|
A.WARRANTY_MONTHS,
|
|
295
|
A.AMORT_STATUS,
|
|
296
|
D.STATUS_NAME AMORT_STATUS_NAME,
|
|
297
|
A.ASS_STATUS, CU.UNIT_NAME,
|
|
298
|
E.STATUS_NAME ASS_STATUS_NAME, --Tinh trang tai san
|
|
299
|
A.ASS_STATUS_DESC,
|
|
300
|
A.RECORD_STATUS,
|
|
301
|
A.AUTH_STATUS,
|
|
302
|
ZZ.AUTH_STATUS_NAME,
|
|
303
|
A.MAKER_ID,
|
|
304
|
convert(varchar, A.CREATE_DT, 103) as CREATE_DT_ASS,
|
|
305
|
convert(varchar, TR.CREATE_DT, 103) as CREATE_DT,
|
|
306
|
A.CHECKER_ID,
|
|
307
|
A.APPROVE_DT,
|
|
308
|
TR.[DESCRIPTION], TR.BRANCH_CREATE,TR.LOCATION,TR.[DESCRIPTION],TR.TRANSFER_MULTI_ID,
|
|
309
|
A.AMORT_AMT-A.AMORTIZED_AMT ASREMAIN_AMT, convert(varchar, B.CREATE_DT, 103) as CREATE_DT_ASS,
|
|
310
|
1 AS SOLUONG,
|
|
311
|
TR.DESCRIPTION AS NOTES,
|
|
312
|
A.ASSET_CODE,
|
|
313
|
-- CASE
|
|
314
|
-- WHEN A.REQ_CODE IS NOT NULL AND A.PL IS NOT NULL THEN A.REQ_CODE + '/' + A.PL
|
|
315
|
-- WHEN A.REQ_CODE IS NOT NULL AND A.PL IS NULL THEN A.REQ_CODE
|
|
316
|
-- WHEN A.REQ_CODE IS NULL AND A.PL IS NOT NULL THEN A.PL ELSE NULL
|
|
317
|
-- END AS PYC_PL
|
|
318
|
ATMM.REQ_CODE + ISNULL(CHAR(10) + CHAR(10) + A.PL,'') AS PYC_PL
|
|
319
|
FROM [dbo].[ASS_TRANSFER_MULTI_DT] TR
|
|
320
|
LEFT JOIN [ASS_TRANSFER_MULTI_MASTER] ATMM ON TR.TRANS_MULTI_MASTER_ID = ATMM.TRANS_MULTI_MASTER_ID
|
|
321
|
LEFT JOIN ASS_MASTER A ON A.ASSET_ID = TR.ASSET_ID
|
|
322
|
LEFT JOIN ASS_GROUP B ON A.GROUP_ID = B.GROUP_ID
|
|
323
|
LEFT JOIN ASS_TYPE C ON A.[TYPE_ID] = C.[TYPE_ID]
|
|
324
|
LEFT JOIN CM_UNIT CU ON B.UNIT = CU.UNIT_ID
|
|
325
|
LEFT JOIN ASS_AMORT_STATUS D ON A.AMORT_STATUS = D.STATUS_CODE
|
|
326
|
LEFT JOIN ASS_STATUS E ON A.ASS_STATUS = E.STATUS_ID
|
|
327
|
LEFT JOIN CM_AUTH_STATUS ZZ ON ZZ.AUTH_STATUS = TR.AUTH_STATUS
|
|
328
|
LEFT JOIN CM_BRANCH BR_R ON BR_R.BRANCH_ID=TR.BRANCH_ID
|
|
329
|
LEFT JOIN CM_EMPLOYEE EM_R ON EM_R.EMP_ID=TR.EMP_ID
|
|
330
|
LEFT JOIN CM_DEPARTMENT H ON H.DEP_ID = TR.DEPT_ID
|
|
331
|
-- 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
|
|
332
|
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
|
|
333
|
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
|
|
334
|
-- 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
|
|
335
|
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
|
|
336
|
-- END
|
|
337
|
WHERE 1=1
|
|
338
|
AND TR.TRANS_MULTI_MASTER_ID= @TRANS_MULTI_MASTER_ID
|
|
339
|
|
|
340
|
IF(CONVERT(VARCHAR,@CREATE_DT_TRANSFER,103)<CONVERT(VARCHAR,@TRANSFER_DT,103))
|
|
341
|
BEGIN
|
|
342
|
IF(@SIGN_APPROVE_DT_KT IS NOT NULL)
|
|
343
|
BEGIN
|
|
344
|
SET @SIGN_APPROVE_DT = @TRANSFER_DT
|
|
345
|
SET @SIGN_APPROVE_DT_KT = @TRANSFER_DT
|
|
346
|
SET @SIGN_RECEVI_DT = @TRANSFER_DT
|
|
347
|
SET @SIGN_CREATE_DT = @TRANSFER_DT
|
|
348
|
END
|
|
349
|
END
|
|
350
|
SET @CREATE_DT = N'Ngày ' + CAST(DAY(@TRANSFER_DT) AS VARCHAR(500))
|
|
351
|
+ N' tháng ' + CAST(MONTH(@TRANSFER_DT) AS VARCHAR(500))
|
|
352
|
+ N' năm ' + CAST(YEAR(@TRANSFER_DT) AS VARCHAR(500))
|
|
353
|
+ 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)
|
|
354
|
|
|
355
|
--CHECK XEM CÓ K/PB/TT ĐIỀU CHUYỂN không, không có thì lấy BRANCH
|
|
356
|
DECLARE @GIAO NVARCHAR(500)
|
|
357
|
IF(@KHOI_TRANSFER IS NOT NULL AND @DEP_TRANSFER IS NOT NULL AND @TT_TRANSFER IS NOT NULL)
|
|
358
|
BEGIN
|
|
359
|
SET @GIAO = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @DEP_TRANSFER)
|
|
360
|
END
|
|
361
|
ELSE IF(@KHOI_TRANSFER IS NULL AND @DEP_TRANSFER IS NULL AND @TT_TRANSFER IS NULL)
|
|
362
|
BEGIN
|
|
363
|
SET @GIAO = (SELECT CD.BRANCH_NAME FROM CM_BRANCH CD WHERE CD.BRANCH_ID = @BRANCH_TRANSFER)
|
|
364
|
END
|
|
365
|
ELSE IF(@KHOI_TRANSFER IS NULL AND @DEP_TRANSFER IS NULL AND @TT_TRANSFER IS NOT NULL)
|
|
366
|
BEGIN
|
|
367
|
SET @GIAO = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @TT_TRANSFER)
|
|
368
|
END
|
|
369
|
ELSE IF(@KHOI_TRANSFER IS NULL AND @DEP_TRANSFER IS NOT NULL AND @TT_TRANSFER IS NULL)
|
|
370
|
BEGIN
|
|
371
|
SET @GIAO = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @DEP_TRANSFER)
|
|
372
|
END
|
|
373
|
ELSE IF(@KHOI_TRANSFER IS NOT NULL AND @DEP_TRANSFER IS NULL AND @TT_TRANSFER IS NULL)
|
|
374
|
BEGIN
|
|
375
|
SET @GIAO = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @KHOI_TRANSFER)
|
|
376
|
END
|
|
377
|
-- THÔNG TIN BÊN GIAO Table1
|
|
378
|
--IF(@REQ_ID IS NOT NULL OR @REQ_ID <> '')
|
|
379
|
--BEGIN
|
|
380
|
-- SELECT
|
|
381
|
-- 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))
|
|
382
|
-- 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
|
|
383
|
--
|
|
384
|
--END
|
|
385
|
--ELSE
|
|
386
|
BEGIN
|
|
387
|
SELECT
|
|
388
|
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))
|
|
389
|
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
|
|
390
|
END
|
|
391
|
|
|
392
|
|
|
393
|
--CHECK XEM CÓ K/PB/TT NHẬN không, không có thì lấy BRANCH
|
|
394
|
DECLARE @NHAN NVARCHAR(500)
|
|
395
|
IF(@KHOI_RECIVE IS NOT NULL AND @DEP_RECIVE IS NOT NULL AND @TT_RECIVE IS NOT NULL)
|
|
396
|
BEGIN
|
|
397
|
SET @NHAN = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @DEP_RECIVE)
|
|
398
|
END
|
|
399
|
ELSE IF(@KHOI_RECIVE IS NULL AND @DEP_RECIVE IS NULL AND @TT_RECIVE IS NULL)
|
|
400
|
BEGIN
|
|
401
|
SET @NHAN = (SELECT CD.BRANCH_NAME FROM CM_BRANCH CD WHERE CD.BRANCH_ID = @BRANCH_RECIVE)
|
|
402
|
END
|
|
403
|
ELSE IF(@KHOI_RECIVE IS NULL AND @DEP_RECIVE IS NULL AND @TT_RECIVE IS NOT NULL)
|
|
404
|
BEGIN
|
|
405
|
SET @NHAN = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @TT_RECIVE)
|
|
406
|
END
|
|
407
|
ELSE IF(@KHOI_RECIVE IS NULL AND @DEP_RECIVE IS NOT NULL AND @TT_RECIVE IS NULL)
|
|
408
|
BEGIN
|
|
409
|
SET @NHAN = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @DEP_RECIVE)
|
|
410
|
END
|
|
411
|
ELSE IF(@KHOI_RECIVE IS NOT NULL AND @DEP_RECIVE IS NULL AND @TT_RECIVE IS NULL)
|
|
412
|
BEGIN
|
|
413
|
SET @NHAN = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @KHOI_RECIVE)
|
|
414
|
END
|
|
415
|
-- THÔNG TIN BÊN NHẬN Table2
|
|
416
|
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))
|
|
417
|
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
|
|
418
|
|
|
419
|
-- CHỮ KÍ
|
|
420
|
DECLARE @S1 NVARCHAR(500),@S2 NVARCHAR(500),@S3 NVARCHAR(500),@S4 NVARCHAR(500),@S5 NVARCHAR(500),@S6 NVARCHAR(500)
|
|
421
|
|
|
422
|
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'),'')
|
|
423
|
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'),'')
|
|
424
|
--PHONGNT bổ sung người nhập KT
|
|
425
|
SET @S3 =N'Người nhập'+ CHAR(10) + (SELECT TLFullName FROM TL_USER WHERE TLNANME = @SIGN_MAKER_ID_KT) + CHAR(10)
|
|
426
|
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'),'')
|
|
427
|
|
|
428
|
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'),'')
|
|
429
|
--PHONGNT bổ sung người nhập
|
|
430
|
SET @S5 =N'Người nhập'+ CHAR(10) + (SELECT TLFullName FROM TL_USER WHERE TLNANME = @SIGN_MAKER_ID_CREATE) + CHAR(10) ;
|
|
431
|
|
|
432
|
-- Table-3 Chữ ký
|
|
433
|
SELECT
|
|
434
|
@MAKER_NAME AS S1,
|
|
435
|
ISNULL(@MAKER_POS, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT(@MAKER_ID_DT, 'dd/MM/yyyy HH:mm:ss') AS S1_POS,
|
|
436
|
@SIGN_QLTS_NAME AS S2,
|
|
437
|
ISNULL(@SIGN_QLTS_POS, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT(@SIGN_QLTS_DT, 'dd/MM/yyyy HH:mm:ss') AS S2_POS,
|
|
438
|
--'' AS S2,
|
|
439
|
--'' AS S2_POS,
|
|
440
|
@NAME_RECIVE AS S3,@S4 AS S4,
|
|
441
|
ISNULL(@POS_RECIVE,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT(@RECEIVE_CONFIRM_DT, 'dd/MM/yyyy HH:mm:ss') AS S3_POS,
|
|
442
|
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,
|
|
443
|
@S6 AS S6
|
|
444
|
|
|
445
|
-- TABLE4 thông tin trung gian *(nếu có)
|
|
446
|
SELECT @SIGN_QLTS_BR+ ' - ' + ISNULL(@SIGN_QLTS_DEP,'') AS KHOI_MID, @SIGN_QLTS_BR AS BRANCH_MID,
|
|
447
|
@SIGN_QLTS_NAME AS MID_NAME, @SIGN_QLTS_ADDR AS MID_ADDR, @SIGN_QLTS_PHONE AS MID_PHONE, @SIGN_QLTS_POS AS MID_POS
|
|
448
|
--SELECT '' AS MID_NAME, '' AS MID_ADDR, '' AS MID_PHONE, '' AS MID_POS
|
|
449
|
|
|
450
|
--TABLE 5 NỘI DUNG BIÊN BẢN
|
|
451
|
SELECT TRANSFER_CONTENT AS TITLE FROM ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID
|
|
452
|
|
|
453
|
--TABLE 6 SỐ
|
|
454
|
SELECT @TRANS_MULTI_MASTER_ID AS NO
|
|
455
|
SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_RECIVE
|
|
456
|
GO
|
|
457
|
|
|
458
|
ALTER PROC dbo.ASS_COL_MULTI_HANDOVER_RECORD
|
|
459
|
@COL_MULTI_MASTER_ID VARCHAR(15) = NULL
|
|
460
|
AS
|
|
461
|
BEGIN
|
|
462
|
--Table 0 Nội dung && BRANCH
|
|
463
|
SELECT TOP 1 B.BRANCH_NAME + ISNULL(' - ' + C.DEP_NAME,'') AS BRANCH_NAME,
|
|
464
|
D.CONTENT AS TITLE
|
|
465
|
,(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
|
|
466
|
FROM ASS_COLLECT_MULTI_DT A
|
|
467
|
LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
|
|
468
|
LEFT JOIN CM_DEPARTMENT C ON A.DEPT_ID_USE = C.DEP_ID
|
|
469
|
LEFT JOIN ASS_COLLECT_MULTI_MASTER D ON A.COL_MULTI_MASTER_ID = D.COL_MULTI_MASTER_ID
|
|
470
|
WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID
|
|
471
|
--Table 1 Bên giao
|
|
472
|
--Fix tạm UAT
|
|
473
|
IF(EXISTS(SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME
|
|
474
|
FROM ASS_COLLECT_CONFIRM_MASTER A
|
|
475
|
LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
|
|
476
|
LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
|
|
477
|
WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID))
|
|
478
|
BEGIN
|
|
479
|
-- 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 <> ''))
|
|
480
|
-- BEGIN
|
|
481
|
-- SELECT TOP 1 C.TLFullName, C.ADDRESS, C.PHONE, D.POS_NAME, ISNULL(E.BRANCH_NAME,'') + ISNULL(' - ' + G.DEP_NAME,'') AS BRANCH_NAME
|
|
482
|
-- FROM ASS_COLLECT_MULTI_MASTER A
|
|
483
|
-- LEFT JOIN TR_REQUEST_SHOP_DOC B ON A.REQ_ID = B.REQ_ID
|
|
484
|
-- LEFT JOIN TL_USER C ON B.MAKER_ID = C.TLNANME
|
|
485
|
-- LEFT JOIN CM_EMPLOYEE_LOG D ON B.MAKER_ID = D.USER_DOMAIN
|
|
486
|
-- LEFT JOIN CM_BRANCH E ON C.TLSUBBRID = E.BRANCH_ID
|
|
487
|
-- LEFT JOIN CM_DEPARTMENT G ON C.DEP_ID = G.DEP_ID
|
|
488
|
-- WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID
|
|
489
|
-- END
|
|
490
|
-- ELSE
|
|
491
|
-- BEGIN
|
|
492
|
SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(E.BRANCH_NAME,'') + ISNULL(' - ' + G.DEP_NAME,'') AS BRANCH_NAME
|
|
493
|
FROM ASS_COLLECT_CONFIRM_MASTER A
|
|
494
|
LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
|
|
495
|
LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
|
|
496
|
LEFT JOIN CM_BRANCH E ON B.TLSUBBRID = E.BRANCH_ID
|
|
497
|
LEFT JOIN CM_DEPARTMENT G ON B.DEP_ID = G.DEP_ID
|
|
498
|
WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID
|
|
499
|
-- END
|
|
500
|
|
|
501
|
END
|
|
502
|
ELSE
|
|
503
|
BEGIN
|
|
504
|
SELECT '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME
|
|
505
|
END
|
|
506
|
|
|
507
|
|
|
508
|
--Table 2 Bên nhận
|
|
509
|
SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(D.BRANCH_NAME,'') + ISNULL(' - ' + E.DEP_NAME,'') AS BRANCH_NAME
|
|
510
|
FROM ASS_COLLECT_MULTI_MASTER A
|
|
511
|
LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
|
|
512
|
LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
|
|
513
|
LEFT JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID
|
|
514
|
LEFT JOIN CM_DEPARTMENT E ON A.DEPT_CREATE = E.DEP_ID
|
|
515
|
WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID
|
|
516
|
|
|
517
|
|
|
518
|
|
|
519
|
--TABLE 3 DANH SÁCH TÀI SẢN
|
|
520
|
SELECT CASE
|
|
521
|
WHEN B.ASSET_CODE IS NOT NULL AND B.ASSET_CODE <> '' THEN B.ASSET_CODE
|
|
522
|
ELSE B.ASS_CODE_TMP
|
|
523
|
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 + ISNULL(CHAR(10) + CHAR(10) + B.PL,'') AS REQ_CODE,B.NOTES AS QUY_CACH, A.NOTES,
|
|
524
|
--ROW_NUMBER() OVER (PARTITION BY B.ASSET_NAME ORDER BY B.ASSET_NAME) AS STT,
|
|
525
|
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT,
|
|
526
|
CA.CONTENT AS COLLECT_REASON
|
|
527
|
FROM ASS_COLLECT_MULTI_DT A
|
|
528
|
LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID
|
|
529
|
LEFT JOIN ASS_GROUP C ON B.GROUP_ID = C.GROUP_ID
|
|
530
|
LEFT JOIN CM_UNIT D ON C.UNIT = D.UNIT_ID
|
|
531
|
LEFT JOIN ASS_STATUS E ON B.ASS_STATUS = E.STATUS_ID
|
|
532
|
LEFT JOIN ASS_COLLECT_MULTI_MASTER F ON A.COL_MULTI_MASTER_ID = F.COL_MULTI_MASTER_ID
|
|
533
|
LEFT JOIN TR_REQUEST_SHOP_DOC REQ ON F.REQ_ID = REQ.REQ_ID
|
|
534
|
LEFT JOIN CM_ALLCODE CA ON A.PURPOSE_ID = CA.CDVAL AND CA.CDNAME = 'REASON_ASS_REQ' AND CA.CDTYPE = 'REQ_ASSET'
|
|
535
|
WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID
|
|
536
|
|
|
537
|
--TABLE 4 CHỮ KÝ
|
|
538
|
--SELECT ''
|
|
539
|
SELECT TOP 1 E.TLFullName AS MAKER_NAME, --BÊN GIAO
|
|
540
|
ISNULL(C.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP
|
|
541
|
WHERE PP.REQ_ID = A.COL_MULTI_MASTER_ID AND PP.PROCESS_ID = 'CONFIRM'
|
|
542
|
AND PP.CHECKER_ID = B.MAKER_ID
|
|
543
|
ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME,--BÊN GIAO
|
|
544
|
D.TLFullName AS CHECKER_NAME, --BÊN NHẬN
|
|
545
|
--D.TLFullName AS INTERMEDIATE_UNIT_NAME, --ĐƠN VỊ TRUNG GIAN
|
|
546
|
--F.POS_NAME AS POS_NAME_1, --ĐƠN VỊ TRUNG GIAN
|
|
547
|
ISNULL(F.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP
|
|
548
|
WHERE PP.REQ_ID = A.COL_MULTI_MASTER_ID AND PP.PROCESS_ID = 'APPROVE'
|
|
549
|
AND PP.CHECKER_ID = A.CHECKER_ID
|
|
550
|
ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME_2 --BÊN NHẬN
|
|
551
|
FROM ASS_COLLECT_MULTI_MASTER A
|
|
552
|
LEFT JOIN ASS_COLLECT_CONFIRM_MASTER B ON A.COL_MULTI_MASTER_ID = B.COL_MULTI_MASTER_ID
|
|
553
|
LEFT JOIN TL_USER E ON B.MAKER_ID = E.TLNANME --BÊN GIAO
|
|
554
|
LEFT JOIN CM_EMPLOYEE_LOG C ON B.MAKER_ID = C.USER_DOMAIN --BÊN GIAO
|
|
555
|
LEFT JOIN TL_USER D ON A.CHECKER_ID = D.TLNANME --BÊN NHẬN + ĐƠN VỊ TRUNG GIAN
|
|
556
|
LEFT JOIN CM_EMPLOYEE_LOG F ON A.CHECKER_ID = F.USER_DOMAIN --BÊN NHẬN + ĐƠN VỊ TRUNG GIAN
|
|
557
|
WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID
|
|
558
|
|
|
559
|
--TABLE 5 SỐ
|
|
560
|
SELECT @COL_MULTI_MASTER_ID AS NO
|
|
561
|
|
|
562
|
--TABLE 6 ĐƠN VỊ TRUNG GIAN
|
|
563
|
SELECT '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME
|
|
564
|
--SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME,ISNULL(D.BRANCH_NAME,'') + ISNULL(' - ' + E.DEP_NAME,'') AS BRANCH_NAME
|
|
565
|
--FROM ASS_COLLECT_MULTI_MASTER A
|
|
566
|
--LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
|
|
567
|
--LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
|
|
568
|
--LEFT JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID
|
|
569
|
--LEFT JOIN CM_DEPARTMENT E ON A.DEPT_CREATE = E.DEP_ID
|
|
570
|
--WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID
|
|
571
|
|
|
572
|
END
|