|
1
|
|
|
2
|
ALTER VIEW dbo.ASS_PENDING_ITEM
|
|
3
|
AS
|
|
4
|
SELECT
|
|
5
|
A.ADDNEW_ID ID
|
|
6
|
,A.AUTH_STATUS
|
|
7
|
,AA.AUTH_STATUS_NAME
|
|
8
|
,A.MAKER_ID
|
|
9
|
,'ASS_ADDNEW' TRAN_TYPE
|
|
10
|
,N'Nhập mới TSCD/CCLD' TRAN_TYPE_NAME
|
|
11
|
,A.BRANCH_CREATE
|
|
12
|
,A.TYPE_ID
|
|
13
|
,A.BUY_PRICE AMT
|
|
14
|
,A.ASSET_DESC DIENGIAI
|
|
15
|
,A.CREATE_DT CREATE_DT
|
|
16
|
,A.MAKER_ID_KT
|
|
17
|
,A.AUTH_STATUS_KT
|
|
18
|
,A.CREATE_DT_KT
|
|
19
|
FROM ASS_ADDNEW A
|
|
20
|
INNER JOIN CM_AUTH_STATUS AA
|
|
21
|
ON AA.AUTH_STATUS = A.AUTH_STATUS
|
|
22
|
|
|
23
|
UNION ALL
|
|
24
|
|
|
25
|
SELECT
|
|
26
|
A.USE_ID ID
|
|
27
|
,A.AUTH_STATUS
|
|
28
|
,AA.AUTH_STATUS_NAME
|
|
29
|
,A.MAKER_ID
|
|
30
|
,'ASS_USE' TRAN_TYPE
|
|
31
|
,N'Xuất sử dụng' TRAN_TYPE_NAME
|
|
32
|
,A.BRANCH_CREATE
|
|
33
|
,CC.TYPE_ID
|
|
34
|
,CC.BUY_PRICE AMT
|
|
35
|
,A.NOTES DIENGIAI
|
|
36
|
,A.CREATE_DT CREATE_DT
|
|
37
|
,'' MAKER_ID_KT
|
|
38
|
,'' AUTH_STATUS_KT
|
|
39
|
,'' CREATE_DT_KT
|
|
40
|
--FROM ASS_USE A
|
|
41
|
FROM ASS_USE A
|
|
42
|
INNER JOIN CM_AUTH_STATUS AA
|
|
43
|
ON AA.AUTH_STATUS = A.AUTH_STATUS
|
|
44
|
INNER JOIN ASS_MASTER CC
|
|
45
|
ON CC.ASSET_ID = A.ASSET_ID
|
|
46
|
|
|
47
|
UNION ALL
|
|
48
|
|
|
49
|
|
|
50
|
SELECT
|
|
51
|
A.TRANSFER_ID ID
|
|
52
|
,A.AUTH_STATUS
|
|
53
|
,AA.AUTH_STATUS_NAME
|
|
54
|
,A.MAKER_ID
|
|
55
|
,'ASS_TRANSFER' TRAN_TYPE
|
|
56
|
,N'Giao dịch điều chuyển' TRAN_TYPE_NAME
|
|
57
|
,A.BRANCH_CREATE
|
|
58
|
,CC.TYPE_ID
|
|
59
|
,CC.BUY_PRICE AMT
|
|
60
|
,A.[DESCRIPTION] DIENGIAI
|
|
61
|
,A.CREATE_DT CREATE_DT
|
|
62
|
,'' MAKER_ID_KT
|
|
63
|
,'' AUTH_STATUS_KT
|
|
64
|
,'' CREATE_DT_KT
|
|
65
|
FROM ASS_TRANSFER A
|
|
66
|
INNER JOIN CM_AUTH_STATUS AA
|
|
67
|
ON AA.AUTH_STATUS = A.AUTH_STATUS
|
|
68
|
INNER JOIN ASS_MASTER CC
|
|
69
|
ON CC.ASSET_ID = A.ASSET_ID
|
|
70
|
|
|
71
|
--UNION ALL
|
|
72
|
|
|
73
|
--SELECT A.ASSET_ID ID, A.AUTH_STATUS, AA.AUTH_STATUS_NAME, A.MAKER_ID, 'ASS_MASTER' TRAN_TYPE, N'Bảng TSCD/CCLD' TRAN_TYPE_NAME,
|
|
74
|
--'' BRANCH_CREATE, A.TYPE_ID
|
|
75
|
--FROM ASS_MASTER A
|
|
76
|
--INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = A.AUTH_STATUS
|
|
77
|
|
|
78
|
UNION ALL
|
|
79
|
|
|
80
|
SELECT
|
|
81
|
A.COLLECT_ID ID
|
|
82
|
,A.AUTH_STATUS
|
|
83
|
,AA.AUTH_STATUS_NAME
|
|
84
|
,A.MAKER_ID
|
|
85
|
,'ASS_COLLECT' TRAN_TYPE
|
|
86
|
,N'Giao dịch thu hồi' TRAN_TYPE_NAME
|
|
87
|
,A.BRANCH_CREATE
|
|
88
|
,CC.TYPE_ID
|
|
89
|
,CC.BUY_PRICE AMT
|
|
90
|
,A.COLLECT_NOTE DIENGIAI
|
|
91
|
,A.CREATE_DT CREATE_DT
|
|
92
|
,'' MAKER_ID_KT
|
|
93
|
,'' AUTH_STATUS_KT
|
|
94
|
,'' CREATE_DT_KT
|
|
95
|
FROM ASS_COLLECT A
|
|
96
|
INNER JOIN CM_AUTH_STATUS AA
|
|
97
|
ON AA.AUTH_STATUS = A.AUTH_STATUS
|
|
98
|
INNER JOIN ASS_MASTER CC
|
|
99
|
ON CC.ASSET_ID = A.ASSET_ID
|
|
100
|
|
|
101
|
UNION ALL
|
|
102
|
|
|
103
|
SELECT
|
|
104
|
A.GROUP_ID ID
|
|
105
|
,A.AUTH_STATUS
|
|
106
|
,AA.AUTH_STATUS_NAME
|
|
107
|
,A.MAKER_ID
|
|
108
|
,'ASS_GROUP' TRAN_TYPE
|
|
109
|
,N'Nhóm tài sản' TRAN_TYPE_NAME
|
|
110
|
,'' BRANCH_CREATE
|
|
111
|
,A.TYPE_ID
|
|
112
|
,0 AMT
|
|
113
|
,A.NOTES DIENGIAI
|
|
114
|
,A.CREATE_DT CREATE_DT
|
|
115
|
,'' MAKER_ID_KT
|
|
116
|
,'' AUTH_STATUS_KT
|
|
117
|
,'' CREATE_DT_KT
|
|
118
|
FROM ASS_GROUP A
|
|
119
|
INNER JOIN CM_AUTH_STATUS AA
|
|
120
|
ON AA.AUTH_STATUS = A.AUTH_STATUS
|
|
121
|
|
|
122
|
|
|
123
|
UNION ALL
|
|
124
|
|
|
125
|
SELECT
|
|
126
|
A.AMORT_ID ID
|
|
127
|
,CASE
|
|
128
|
WHEN A.PROCESS_STATUS = 'P' THEN 'U'
|
|
129
|
ELSE 'A'
|
|
130
|
END AUTH_STATUS
|
|
131
|
,CASE
|
|
132
|
WHEN A.PROCESS_STATUS = 'P' THEN N'Chưa thực thi'
|
|
133
|
ELSE N'Đã thực thi'
|
|
134
|
END AUTH_STATUS_NAME
|
|
135
|
,A.MAKER_ID
|
|
136
|
,'ASS_AMORT' TRAN_TYPE
|
|
137
|
,A.NOTES TRAN_TYPE_NAME
|
|
138
|
,--N'Khấu hao'
|
|
139
|
A.BRANCH_ID BRANCH_CREATE
|
|
140
|
,A.ASSET_TYPE AS [TYPE_ID]
|
|
141
|
,A.TOTAL_AMT AMT
|
|
142
|
,A.NOTES DIENGIAI
|
|
143
|
,A.CREATE_DT CREATE_DT
|
|
144
|
,'' MAKER_ID_KT
|
|
145
|
,'' AUTH_STATUS_KT
|
|
146
|
,'' CREATE_DT_KT
|
|
147
|
FROM ASS_AMORT A
|
|
148
|
|
|
149
|
|
|
150
|
UNION ALL
|
|
151
|
|
|
152
|
SELECT
|
|
153
|
A.ASSET_ID ID
|
|
154
|
,A.AUTH_STATUS
|
|
155
|
,AA.AUTH_STATUS_NAME
|
|
156
|
,A.MAKER_ID
|
|
157
|
,'ASS_UPDATE' TRAN_TYPE
|
|
158
|
,N'Cập nhật thông tin tài sản' TRAN_TYPE_NAME
|
|
159
|
,A.BRANCH_ID BRANCH_CREATE
|
|
160
|
,CC.TYPE_ID
|
|
161
|
,CC.BUY_PRICE AMT
|
|
162
|
,A.NOTES DIENGIAI
|
|
163
|
,A.CREATE_DT CREATE_DT
|
|
164
|
,A.MAKER_ID MAKER_ID_KT
|
|
165
|
,A.AUTH_STATUS AUTH_STATUS_KT
|
|
166
|
,A.CREATE_DT CREATE_DT_KT
|
|
167
|
FROM ASS_UPDATE A
|
|
168
|
INNER JOIN CM_AUTH_STATUS AA
|
|
169
|
ON AA.AUTH_STATUS = A.AUTH_STATUS
|
|
170
|
INNER JOIN ASS_MASTER CC
|
|
171
|
ON CC.ASSET_ID = A.ASSET_ID
|
|
172
|
|
|
173
|
-----thieuvq 5102016 - them dieu kien kiem tra danh sach dieu chuyen nhieu tai san chua duyet
|
|
174
|
UNION ALL
|
|
175
|
|
|
176
|
SELECT
|
|
177
|
A.ASSET_ID ID
|
|
178
|
,BB.AUTH_STATUS
|
|
179
|
,AA.AUTH_STATUS_NAME
|
|
180
|
,BB.MAKER_ID
|
|
181
|
,'ASS_TRANSFER_MULTI_MASTER' TRAN_TYPE
|
|
182
|
,N'Điều chuyển nhiều tài sản.' TRAN_TYPE_NAME
|
|
183
|
,A.BRANCH_ID BRANCH_CREATE
|
|
184
|
,CC.TYPE_ID
|
|
185
|
,CC.BUY_PRICE AMT
|
|
186
|
,BB.NOTES DIENGIAI
|
|
187
|
,BB.CREATE_DT CREATE_DT
|
|
188
|
,BB.MAKER_ID_KT
|
|
189
|
,BB.AUTH_STATUS_KT
|
|
190
|
,BB.CREATE_DT_KT
|
|
191
|
FROM ASS_TRANSFER_MULTI_DT A
|
|
192
|
INNER JOIN ASS_TRANSFER_MULTI_MASTER BB
|
|
193
|
ON A.TRANS_MULTI_MASTER_ID = BB.TRANS_MULTI_MASTER_ID
|
|
194
|
INNER JOIN CM_AUTH_STATUS AA
|
|
195
|
ON AA.AUTH_STATUS = BB.AUTH_STATUS
|
|
196
|
INNER JOIN ASS_MASTER CC
|
|
197
|
ON CC.ASSET_ID = A.ASSET_ID
|
|
198
|
|
|
199
|
UNION ALL
|
|
200
|
|
|
201
|
-----thieuvq 5102016 - them dieu kien kiem tra danh sach xuat nhieu tai san chua duyet
|
|
202
|
SELECT
|
|
203
|
A.ASSET_ID ID
|
|
204
|
,BB.AUTH_STATUS
|
|
205
|
,AA.AUTH_STATUS_NAME
|
|
206
|
,BB.MAKER_ID
|
|
207
|
,'ASS_USE_MULTI_MASTER' TRAN_TYPE
|
|
208
|
,N'Xuất nhiều tài sản.' TRAN_TYPE_NAME
|
|
209
|
,A.BRANCH_ID BRANCH_CREATE
|
|
210
|
,CC.TYPE_ID
|
|
211
|
,CC.BUY_PRICE AMT
|
|
212
|
,BB.NOTES DIENGIAI
|
|
213
|
,BB.CREATE_DT CREATE_DT
|
|
214
|
,BB.MAKER_ID_KT
|
|
215
|
,BB.AUTH_STATUS_KT
|
|
216
|
,BB.CREATE_DT_KT
|
|
217
|
FROM ASS_USE_MULTI_DT A
|
|
218
|
INNER JOIN ASS_USE_MULTI_MASTER BB
|
|
219
|
ON A.USER_MASTER_ID = BB.USER_MASTER_ID
|
|
220
|
INNER JOIN CM_AUTH_STATUS AA
|
|
221
|
ON AA.AUTH_STATUS = BB.AUTH_STATUS
|
|
222
|
INNER JOIN ASS_MASTER CC
|
|
223
|
ON CC.ASSET_ID = A.ASSET_ID
|
|
224
|
|
|
225
|
UNION ALL
|
|
226
|
|
|
227
|
-----thieuvq 5102016 - them dieu kien kiem tra danh sach xuat nhieu tai san chua duyet
|
|
228
|
SELECT
|
|
229
|
A.ASSET_ID ID
|
|
230
|
,BB.AUTH_STATUS
|
|
231
|
,AA.AUTH_STATUS_NAME
|
|
232
|
,BB.MAKER_ID
|
|
233
|
,'ASS_COLLECT_MULTI_MASTER' TRAN_TYPE
|
|
234
|
,N'Thu hồi nhiều tài sản.' TRAN_TYPE_NAME
|
|
235
|
,A.BRANCH_ID BRANCH_CREATE
|
|
236
|
,CC.TYPE_ID
|
|
237
|
,CC.BUY_PRICE AMT
|
|
238
|
,BB.NOTES DIENGIAI
|
|
239
|
,BB.CREATE_DT CREATE_DT
|
|
240
|
,BB.MAKER_ID_KT
|
|
241
|
,BB.AUTH_STATUS_KT
|
|
242
|
,BB.CREATE_DT_KT
|
|
243
|
FROM ASS_COLLECT_MULTI_DT A
|
|
244
|
INNER JOIN ASS_COLLECT_MULTI_MASTER BB
|
|
245
|
ON A.COL_MULTI_MASTER_ID = BB.COL_MULTI_MASTER_ID
|
|
246
|
INNER JOIN CM_AUTH_STATUS AA
|
|
247
|
ON AA.AUTH_STATUS = BB.AUTH_STATUS
|
|
248
|
INNER JOIN ASS_MASTER CC
|
|
249
|
ON CC.ASSET_ID = A.ASSET_ID
|
|
250
|
|
|
251
|
|
|
252
|
UNION ALL
|
|
253
|
|
|
254
|
-----luctv 07012018 - them dieu kien kiem tra danh sach thanh ly nhieu tai san chua duyet
|
|
255
|
SELECT
|
|
256
|
A.ASSET_ID ID
|
|
257
|
,BB.AUTH_STATUS
|
|
258
|
,AA.AUTH_STATUS_NAME
|
|
259
|
,BB.MAKER_ID
|
|
260
|
,'ASS_LIQ_MULTI_MASTER' TRAN_TYPE
|
|
261
|
,N'Thanh lý nhiều tài sản.' TRAN_TYPE_NAME
|
|
262
|
,BB.BRANCH_ID BRANCH_CREATE
|
|
263
|
,CC.TYPE_ID
|
|
264
|
,CC.BUY_PRICE AMT
|
|
265
|
,BB.NOTES DIENGIAI
|
|
266
|
,BB.CREATE_DT CREATE_DT
|
|
267
|
,BB.MAKER_ID_KT
|
|
268
|
,BB.AUTH_STATUS_KT
|
|
269
|
,BB.CREATE_DT_KT
|
|
270
|
FROM ASS_LIQUIDATION_DT A
|
|
271
|
INNER JOIN ASS_LIQUIDATION BB
|
|
272
|
ON A.LIQ_ID = BB.LIQ_ID
|
|
273
|
INNER JOIN CM_AUTH_STATUS AA
|
|
274
|
ON AA.AUTH_STATUS = BB.AUTH_STATUS
|
|
275
|
INNER JOIN ASS_MASTER CC
|
|
276
|
ON CC.ASSET_ID = A.ASSET_ID
|
|
277
|
|
|
278
|
GO
|
|
279
|
|
|
280
|
|
|
281
|
ALTER PROCEDURE dbo.ASS_PENDING_ITEM_Searchnew
|
|
282
|
@p_CREATE_DT varchar(15) = NULL,
|
|
283
|
@p_BRANCH_ID VARCHAR(15) = NULL,
|
|
284
|
@p_BRANCH_LOGIN NVARCHAR(100) = NULL,
|
|
285
|
@p_ASSET_TYPE VARCHAR(15) = NULL,
|
|
286
|
@p_MAKER_ID VARCHAR(100) = NULL,
|
|
287
|
@p_AUTH_STATUS VARCHAR(15) = NULL,
|
|
288
|
@p_MODULE NVARCHAR(500) = NULL,
|
|
289
|
@p_USER_LOGIN varchar(20) = NULL
|
|
290
|
AS
|
|
291
|
DECLARE @l_BRANCH_TYPE VARCHAR(15)
|
|
292
|
SET @l_BRANCH_TYPE = (SELECT A.BRANCH_TYPE FROM CM_BRANCH A WHERE A.BRANCH_ID = @p_BRANCH_LOGIN)
|
|
293
|
declare @tmp table(BRANCH_ID varchar(15))
|
|
294
|
insert into @tmp SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)
|
|
295
|
|
|
296
|
DECLARE @LISTTYPE TABLE(TRN_TYPE VARCHAR(50), DUONGDAN VARCHAR(500), PHANHE NVARCHAR(500))
|
|
297
|
INSERT INTO @LISTTYPE values ('ASS_ADDNEW', '/AssetManager/ASSAddNewList',N'Quản lý TSCĐ/CCLĐ')
|
|
298
|
INSERT INTO @LISTTYPE values('ASS_USE', '/AssetManager/ASSUseList',N'Quản lý TSCĐ/CCLĐ')
|
|
299
|
INSERT INTO @LISTTYPE values('ASS_TRANSFER', '/AssetManager/ASSTransferList',N'Quản lý TSCĐ/CCLĐ')
|
|
300
|
INSERT INTO @LISTTYPE values('ASS_MASTER', '/AssetManager/ASSMasterList',N'Quản lý TSCĐ/CCLĐ')
|
|
301
|
INSERT INTO @LISTTYPE values('ASS_COLLECT', '/AssetManager/ASSCollectList',N'Quản lý TSCĐ/CCLĐ')
|
|
302
|
INSERT INTO @LISTTYPE values('ASS_GROUP', '/AssCommon/AssetGroupList',N'Quản lý danh mục')
|
|
303
|
INSERT INTO @LISTTYPE values('ASS_AMORT', '/AssetManager/ASSAmort',N'Quản lý TSCĐ/CCLĐ')
|
|
304
|
INSERT INTO @LISTTYPE values('ASS_UPDATE', '/AssetManager/ASSUpdateList',N'Quản lý TSCĐ/CCLĐ')
|
|
305
|
BEGIN -- PAGING
|
|
306
|
select A.*, BB.DUONGDAN DUONGDAN, BB.PHANHE PHANHE, CM.BRANCH_NAME
|
|
307
|
-- SELECT END
|
|
308
|
from ASS_PENDING_ITEM A
|
|
309
|
LEFT JOIN CM_BRANCH CM ON A.BRANCH_CREATE= CM.BRANCH_ID
|
|
310
|
LEFT JOIN @LISTTYPE BB ON A.TRAN_TYPE = BB.TRN_TYPE
|
|
311
|
where
|
|
312
|
--A.BRANCH_CREATE LIKE '%'+ @p_BRANCH_CREATE +'%' OR @p_BRANCH_CREATE = '' OR @p_BRANCH_CREATE IS NULL
|
|
313
|
(A.MAKER_ID_KT LIKE '%'+ @p_MAKER_ID +'%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
314
|
--AND(CM.BRANCH_NAME LIKE '%'+@p_BRANCH_NAME +'%' OR @p_BRANCH_NAME IS NULL OR @p_BRANCH_NAME='')
|
|
315
|
AND (CONVERT(DATE,A.CREATE_DT_KT)= CONVERT(DATE,@p_CREATE_DT,103) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT='')
|
|
316
|
AND (A.AUTH_STATUS_KT =@p_AUTH_STATUS OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS ='')
|
|
317
|
AND (BB.PHANHE = @p_MODULE OR @p_MODULE IS NULL OR @p_MODULE = '' OR @p_MODULE = N'---Tất cả---')
|
|
318
|
AND ((A.BRANCH_CREATE IN (SELECT * FROM @tmp) OR @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)
|
|
319
|
OR ((A.BRANCH_CREATE IS NULL OR A.BRANCH_CREATE = '') AND @l_BRANCH_TYPE = 'HS'))
|
|
320
|
-- PAGING END
|
|
321
|
END -- PAGING
|