|
1
|
-- PROCEDURE NAME: ASS_USE_CONFIRM_MASTER_Search
|
|
2
|
|
|
3
|
DECLARE @p_USER_MASTER_ID varchar(15) = NULL,
|
|
4
|
@p_BRANCH_ID varchar(15) = N'BRN000000000405',
|
|
5
|
@p_USE_EXPORT_DT varchar(20) = NULL,
|
|
6
|
@p_USER_EXPORT nvarchar(200) = NULL,
|
|
7
|
@p_USER_EXPORT_NAME nvarchar(200) = NULL,
|
|
8
|
@p_NOTES nvarchar(1000) = NULL,
|
|
9
|
@p_RECORD_STATUS varchar(1) = NULL,
|
|
10
|
@p_AUTH_STATUS varchar(1) = N'N',
|
|
11
|
@p_MAKER_ID varchar(15) = NULL,
|
|
12
|
@p_CREATE_DT varchar(20) = NULL,
|
|
13
|
@p_CHECKER_ID varchar(15) = NULL,
|
|
14
|
@p_APPROVE_DT varchar(20) = NULL,
|
|
15
|
@p_AUTH_STATUS_KT varchar(15) = NULL,
|
|
16
|
@p_CREATE_DT_KT varchar(20) = NULL,
|
|
17
|
@p_APPROVE_DT_KT varchar(20) = NULL,
|
|
18
|
@p_MAKER_ID_KT varchar(50) = NULL,
|
|
19
|
@p_CHECKER_ID_KT varchar(50) = NULL,
|
|
20
|
@p_REPORT_STATUS varchar(15) = NULL,
|
|
21
|
@p_KT_IS_DO varchar(1) = NULL,
|
|
22
|
@p_LEVEL varchar(50) = N'UNIT',
|
|
23
|
@p_TOP int = NULL,
|
|
24
|
@p_FROMDATE varchar(20) = NULL,
|
|
25
|
@p_TODATE varchar(20) = NULL,
|
|
26
|
@p_USER_LOGIN varchar(15) = N'tuandv',
|
|
27
|
@p_TYPE_SEARCH varchar(10) = NULL
|
|
28
|
|
|
29
|
|
|
30
|
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
|
|
31
|
|
|
32
|
------
|
|
33
|
SET @p_RECORD_STATUS = '1'
|
|
34
|
-------
|
|
35
|
|
|
36
|
DECLARE @BRANCH_LOGIN VARCHAR(15)
|
|
37
|
SET @BRANCH_LOGIN = (SELECT TOP 1 TLSUBBRID FROM TL_USER WHERE TLNANME = @p_USER_LOGIN)
|
|
38
|
|
|
39
|
IF(@p_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)
|
|
40
|
-- PAGING BEGIN
|
|
41
|
BEGIN
|
|
42
|
SELECT COUNT(*) -- SELECT END
|
|
43
|
FROM ASS_USE_MULTI_MASTER A
|
|
44
|
LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
|
|
45
|
LEFT JOIN dbo.CM_ALLCODE F ON F.CDVAL = A.AUTH_STATUS AND F.CDNAME = 'ASS_ADD_AUTH' AND F.CDTYPE = 'ASS_ADDNEW'
|
|
46
|
LEFT JOIN dbo.CM_ALLCODE ATKT ON ATKT.CDVAL = A.AUTH_STATUS_KT AND ATKT.CDNAME = 'ASS_ADD_AUTH' AND ATKT.CDTYPE = 'ASS_ADDNEW'
|
|
47
|
LEFT JOIN (SELECT TOP 1 USER_MASTER_ID,DT.BRANCH_ID,DEPT_ID FROM ASS_USE_MULTI_DT DT ORDER BY USE_MULTI_ID ASC) tmp ON A.USER_MASTER_ID = tmp.USER_MASTER_ID
|
|
48
|
--INNER JOIN (SELECT BRANCH_ID,USER_MASTER_ID,COUNT(*) AS SL FROM ASS_USE_MULTI_DT WHERE BRANCH_ID = @BRANCH_LOGIN GROUP BY BRANCH_ID,USER_MASTER_ID) DTT ON DTT.USER_MASTER_ID = A.USER_MASTER_ID
|
|
49
|
LEFT JOIN ASS_USE_CONFIRM_MASTER B ON A.USER_MASTER_ID = B.USER_MASTER_ID
|
|
50
|
LEFT JOIN TL_USER D ON B.MAKER_ID = D.TLNANME
|
|
51
|
WHERE 1 = 1
|
|
52
|
AND (A.USER_MASTER_ID LIKE '%' + @p_USER_MASTER_ID + '%' OR @p_USER_MASTER_ID IS NULL OR @p_USER_MASTER_ID = '')
|
|
53
|
AND (CONVERT(DATE,ISNULL(A.APPROVE_DT,A.USE_EXPORT_DT),103) >=CONVERT(DATE, @p_FROMDATE, 103) OR @p_FROMDATE IS NULL OR @p_FROMDATE = '')
|
|
54
|
AND (CONVERT(DATE,ISNULL(A.APPROVE_DT, A.USE_EXPORT_DT),103) <=CONVERT(DATETIME, @p_TODATE, 103) OR @p_TODATE IS NULL OR @p_TODATE = '')
|
|
55
|
AND (A.USER_EXPORT LIKE '%' + @p_USER_EXPORT + '%' OR @p_USER_EXPORT IS NULL OR @p_USER_EXPORT = '')
|
|
56
|
AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR @p_NOTES IS NULL OR @p_NOTES = '')
|
|
57
|
AND A.AUTH_STATUS = 'A'
|
|
58
|
AND A.AUTH_STATUS_KT <>'A'
|
|
59
|
AND A.APPROVE_DT>=CONVERT(DATETIME,'2022-17-10',103)
|
|
60
|
--AND ( (@p_AUTH_STATUS = 'Y' AND A.USER_MASTER_ID = B.USER_MASTER_ID)
|
|
61
|
--OR (@p_AUTH_STATUS = 'N' AND A.USER_MASTER_ID <> B.USER_MASTER_ID
|
|
62
|
--OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' )))
|
|
63
|
AND ( (@p_AUTH_STATUS = 'Y' AND A.USER_MASTER_ID IN (SELECT G.USER_MASTER_ID FROM ASS_USE_CONFIRM_MASTER G))
|
|
64
|
OR (@p_AUTH_STATUS = 'N' AND A.USER_MASTER_ID NOT IN (SELECT G.USER_MASTER_ID FROM ASS_USE_CONFIRM_MASTER G)
|
|
65
|
OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' )))
|
|
66
|
--TIM KIEM THEO TRUONG DON VI NHAN TAI SAN
|
|
67
|
AND (EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE('GDDV',
|
|
68
|
tmp.BRANCH_ID,
|
|
69
|
tmp.DEPT_ID) US
|
|
70
|
WHERE US.TLNANME = @p_USER_LOGIN))
|
|
71
|
-- AND (EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE('GDDV',
|
|
72
|
-- (SELECT TOP 1 DT.BRANCH_ID FROM ASS_USE_MULTI_DT DT WHERE DT.USER_MASTER_ID = A.USER_MASTER_ID ORDER BY USE_MULTI_ID ASC),
|
|
73
|
-- (SELECT TOP 1 DT.DEPT_ID FROM ASS_USE_MULTI_DT DT WHERE DT.USER_MASTER_ID = A.USER_MASTER_ID ORDER BY USE_MULTI_ID ASC)) US
|
|
74
|
-- WHERE US.TLNANME = @p_USER_LOGIN))
|
|
75
|
|
|
76
|
|
|
77
|
|
|
78
|
|
|
79
|
;WITH QUERY_DATA AS (
|
|
80
|
SELECT A.*,C.BRANCH_CODE, C.BRANCH_NAME, F.CONTENT AS AUTH_STATUS_NAME,C.BRANCH_CODE+' - '+ C.BRANCH_NAME AS FULL_BRANCH_NAME,
|
|
81
|
|
|
82
|
ATKT.CONTENT AS AUTH_STATUS_NAME_KT,
|
|
83
|
@p_USER_LOGIN AS USER_LOGIN,@p_TYPE_SEARCH AS TYPE_SEARCH,
|
|
84
|
B.CONFIRM_STATUS, dbo.FN_ASS_USE_MULTI_MASTER_GET_TOTAL_VALUE(A.USER_MASTER_ID) AS TOTAL_USE_VALUE,D.TLFullName CONFIRM_USER_NAME,B.CONFIRM_DT,
|
|
85
|
CASE WHEN B.CONFIRM_STATUS = 'Y'
|
|
86
|
THEN N'Đã xác nhận'
|
|
87
|
ELSE N'Chưa xác nhận' END AS CONFIRM_STATUS_NAME
|
|
88
|
, ROW_NUMBER() OVER (ORDER BY A.CREATE_DT DESC
|
|
89
|
) AS __ROWNUM-- SELECT END
|
|
90
|
FROM ASS_USE_MULTI_MASTER A
|
|
91
|
LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
|
|
92
|
LEFT JOIN dbo.CM_ALLCODE F ON F.CDVAL = A.AUTH_STATUS AND F.CDNAME = 'ASS_ADD_AUTH' AND F.CDTYPE = 'ASS_ADDNEW'
|
|
93
|
LEFT JOIN dbo.CM_ALLCODE ATKT ON ATKT.CDVAL = A.AUTH_STATUS_KT AND ATKT.CDNAME = 'ASS_ADD_AUTH' AND ATKT.CDTYPE = 'ASS_ADDNEW'
|
|
94
|
LEFT JOIN (SELECT TOP 1 USER_MASTER_ID,DT.BRANCH_ID,DEPT_ID FROM ASS_USE_MULTI_DT DT ORDER BY USE_MULTI_ID ASC) tmp ON A.USER_MASTER_ID = tmp.USER_MASTER_ID
|
|
95
|
--INNER JOIN (SELECT BRANCH_ID,USER_MASTER_ID,COUNT(*) AS SL FROM ASS_USE_MULTI_DT WHERE BRANCH_ID = @BRANCH_LOGIN GROUP BY BRANCH_ID,USER_MASTER_ID) DTT ON DTT.USER_MASTER_ID = A.USER_MASTER_ID
|
|
96
|
LEFT JOIN ASS_USE_CONFIRM_MASTER B ON A.USER_MASTER_ID = B.USER_MASTER_ID
|
|
97
|
LEFT JOIN TL_USER D ON B.MAKER_ID = D.TLNANME
|
|
98
|
WHERE 1 = 1
|
|
99
|
AND (A.USER_MASTER_ID LIKE '%' + @p_USER_MASTER_ID + '%' OR @p_USER_MASTER_ID IS NULL OR @p_USER_MASTER_ID = '')
|
|
100
|
AND (CONVERT(DATE,ISNULL(A.APPROVE_DT,A.USE_EXPORT_DT),103) >=CONVERT(DATE, @p_FROMDATE, 103) OR @p_FROMDATE IS NULL OR @p_FROMDATE = '')
|
|
101
|
AND (CONVERT(DATE,ISNULL(A.APPROVE_DT, A.USE_EXPORT_DT),103) <=CONVERT(DATETIME, @p_TODATE, 103) OR @p_TODATE IS NULL OR @p_TODATE = '')
|
|
102
|
AND (A.USER_EXPORT LIKE '%' + @p_USER_EXPORT + '%' OR @p_USER_EXPORT IS NULL OR @p_USER_EXPORT = '')
|
|
103
|
AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR @p_NOTES IS NULL OR @p_NOTES = '')
|
|
104
|
AND A.AUTH_STATUS = 'A'
|
|
105
|
AND A.AUTH_STATUS_KT <>'A'
|
|
106
|
AND A.APPROVE_DT>=CONVERT(DATETIME,'2022-17-10',103)
|
|
107
|
--AND ( (@p_AUTH_STATUS = 'Y' AND A.USER_MASTER_ID = B.USER_MASTER_ID)
|
|
108
|
--OR (@p_AUTH_STATUS = 'N' AND A.USER_MASTER_ID <> B.USER_MASTER_ID
|
|
109
|
--OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' )))
|
|
110
|
AND ( (@p_AUTH_STATUS = 'Y' AND A.USER_MASTER_ID IN (SELECT G.USER_MASTER_ID FROM ASS_USE_CONFIRM_MASTER G))
|
|
111
|
OR (@p_AUTH_STATUS = 'N' AND A.USER_MASTER_ID NOT IN (SELECT G.USER_MASTER_ID FROM ASS_USE_CONFIRM_MASTER G)
|
|
112
|
OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' )))
|
|
113
|
--TIM KIEM THEO TRUONG DON VI NHAN TAI SAN
|
|
114
|
AND (EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE('GDDV',
|
|
115
|
tmp.BRANCH_ID,
|
|
116
|
tmp.DEPT_ID) US
|
|
117
|
WHERE US.TLNANME = @p_USER_LOGIN))
|
|
118
|
-- AND (EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE('GDDV',
|
|
119
|
-- (SELECT TOP 1 DT.BRANCH_ID FROM ASS_USE_MULTI_DT DT WHERE DT.USER_MASTER_ID = A.USER_MASTER_ID ORDER BY USE_MULTI_ID ASC),
|
|
120
|
-- (SELECT TOP 1 DT.DEPT_ID FROM ASS_USE_MULTI_DT DT WHERE DT.USER_MASTER_ID = A.USER_MASTER_ID ORDER BY USE_MULTI_ID ASC)) US
|
|
121
|
-- WHERE US.TLNANME = @p_USER_LOGIN))
|
|
122
|
|
|
123
|
|
|
124
|
|
|
125
|
|
|
126
|
) SELECT * FROM QUERY_DATA WHERE __ROWNUM > 0 AND __ROWNUM <= 10
|
|
127
|
END-- PAGING END
|
|
128
|
ELSE
|
|
129
|
-- PAGING BEGIN
|
|
130
|
BEGIN
|
|
131
|
SELECT COUNT(*) FROM(
|
|
132
|
SELECT TOP(CONVERT(INT,@P_TOP))A.*,C.BRANCH_CODE, C.BRANCH_NAME, F.CONTENT AS AUTH_STATUS_NAME, C.BRANCH_CODE+' - '+ C.BRANCH_NAME AS FULL_BRANCH_NAME,
|
|
133
|
ATKT.CONTENT AS AUTH_STATUS_NAME_KT,
|
|
134
|
@p_USER_LOGIN AS USER_LOGIN,@p_TYPE_SEARCH AS TYPE_SEARCH,
|
|
135
|
B.CONFIRM_STATUS, dbo.FN_ASS_USE_MULTI_MASTER_GET_TOTAL_VALUE(A.USER_MASTER_ID) AS TOTAL_USE_VALUE,D.TLFullName CONFIRM_USER_NAME,B.CONFIRM_DT,
|
|
136
|
CASE WHEN B.CONFIRM_STATUS = 'Y'
|
|
137
|
THEN N'Đã xác nhận'
|
|
138
|
ELSE N'Chưa xác nhận' END AS CONFIRM_STATUS_NAME
|
|
139
|
-- SELECT END
|
|
140
|
FROM ASS_USE_MULTI_MASTER A
|
|
141
|
LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
|
|
142
|
LEFT JOIN dbo.CM_ALLCODE F ON F.CDVAL = A.AUTH_STATUS AND F.CDNAME = 'ASS_ADD_AUTH' AND F.CDTYPE = 'ASS_ADDNEW'
|
|
143
|
LEFT JOIN dbo.CM_ALLCODE ATKT ON ATKT.CDVAL = A.AUTH_STATUS_KT AND ATKT.CDNAME = 'ASS_ADD_AUTH' AND ATKT.CDTYPE = 'ASS_ADDNEW'
|
|
144
|
LEFT JOIN (SELECT TOP 1 USER_MASTER_ID,DT.BRANCH_ID,DEPT_ID FROM ASS_USE_MULTI_DT DT ORDER BY USE_MULTI_ID ASC) tmp ON A.USER_MASTER_ID = tmp.USER_MASTER_ID
|
|
145
|
--INNER JOIN (SELECT BRANCH_ID,USER_MASTER_ID,COUNT(*) AS SL FROM ASS_USE_MULTI_DT WHERE BRANCH_ID = @BRANCH_LOGIN GROUP BY BRANCH_ID,USER_MASTER_ID) DTT ON DTT.USER_MASTER_ID = A.USER_MASTER_ID
|
|
146
|
LEFT JOIN ASS_USE_CONFIRM_MASTER B ON A.USER_MASTER_ID = B.USER_MASTER_ID
|
|
147
|
LEFT JOIN TL_USER D ON B.MAKER_ID = D.TLNANME
|
|
148
|
WHERE 1 = 1
|
|
149
|
AND (A.USER_MASTER_ID LIKE '%' + @p_USER_MASTER_ID + '%' OR @p_USER_MASTER_ID IS NULL OR @p_USER_MASTER_ID = '')
|
|
150
|
AND (CONVERT(DATE,ISNULL(A.APPROVE_DT,A.USE_EXPORT_DT),103) >=CONVERT(DATE, @p_FROMDATE, 103) OR @p_FROMDATE IS NULL OR @p_FROMDATE = '')
|
|
151
|
AND (CONVERT(DATE,ISNULL(A.APPROVE_DT,A.USE_EXPORT_DT),103) <=CONVERT(DATETIME, @p_TODATE, 103) OR @p_TODATE IS NULL OR @p_TODATE = '')
|
|
152
|
AND (A.USER_EXPORT LIKE '%' + @p_USER_EXPORT + '%' OR @p_USER_EXPORT IS NULL OR @p_USER_EXPORT = '')
|
|
153
|
AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR @p_NOTES IS NULL OR @p_NOTES = '')
|
|
154
|
AND A.AUTH_STATUS = 'A'
|
|
155
|
AND A.AUTH_STATUS_KT <>'A'
|
|
156
|
AND A.APPROVE_DT>=CONVERT(DATETIME,'2022-17-10',103)
|
|
157
|
AND ( (@p_AUTH_STATUS = 'Y' AND A.USER_MASTER_ID IN (SELECT G.USER_MASTER_ID FROM ASS_USE_CONFIRM_MASTER G))
|
|
158
|
OR (@p_AUTH_STATUS = 'N' AND A.USER_MASTER_ID NOT IN (SELECT G.USER_MASTER_ID FROM ASS_USE_CONFIRM_MASTER G)
|
|
159
|
OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' )))
|
|
160
|
--AND ( (@p_AUTH_STATUS = 'Y' AND A.USER_MASTER_ID = B.USER_MASTER_ID)
|
|
161
|
--OR (@p_AUTH_STATUS = 'N' AND A.USER_MASTER_ID <> B.USER_MASTER_ID
|
|
162
|
--OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' )))
|
|
163
|
|
|
164
|
AND (EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE('GDDV',
|
|
165
|
tmp.BRANCH_ID,
|
|
166
|
tmp.DEPT_ID) US
|
|
167
|
WHERE US.TLNANME = @p_USER_LOGIN))
|
|
168
|
|
|
169
|
ORDER BY A.CREATE_DT DESC
|
|
170
|
) COUNTER_TOP;WITH QUERY_DATA AS (
|
|
171
|
SELECT TOP(CONVERT(INT,@P_TOP))A.*,C.BRANCH_CODE, C.BRANCH_NAME, F.CONTENT AS AUTH_STATUS_NAME, C.BRANCH_CODE+' - '+ C.BRANCH_NAME AS FULL_BRANCH_NAME,
|
|
172
|
ATKT.CONTENT AS AUTH_STATUS_NAME_KT,
|
|
173
|
@p_USER_LOGIN AS USER_LOGIN,@p_TYPE_SEARCH AS TYPE_SEARCH,
|
|
174
|
B.CONFIRM_STATUS, dbo.FN_ASS_USE_MULTI_MASTER_GET_TOTAL_VALUE(A.USER_MASTER_ID) AS TOTAL_USE_VALUE,D.TLFullName CONFIRM_USER_NAME,B.CONFIRM_DT,
|
|
175
|
CASE WHEN B.CONFIRM_STATUS = 'Y'
|
|
176
|
THEN N'Đã xác nhận'
|
|
177
|
ELSE N'Chưa xác nhận' END AS CONFIRM_STATUS_NAME
|
|
178
|
, ROW_NUMBER() OVER (ORDER BY A.CREATE_DT DESC
|
|
179
|
) AS __ROWNUM-- SELECT END
|
|
180
|
FROM ASS_USE_MULTI_MASTER A
|
|
181
|
LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
|
|
182
|
LEFT JOIN dbo.CM_ALLCODE F ON F.CDVAL = A.AUTH_STATUS AND F.CDNAME = 'ASS_ADD_AUTH' AND F.CDTYPE = 'ASS_ADDNEW'
|
|
183
|
LEFT JOIN dbo.CM_ALLCODE ATKT ON ATKT.CDVAL = A.AUTH_STATUS_KT AND ATKT.CDNAME = 'ASS_ADD_AUTH' AND ATKT.CDTYPE = 'ASS_ADDNEW'
|
|
184
|
LEFT JOIN (SELECT TOP 1 USER_MASTER_ID,DT.BRANCH_ID,DEPT_ID FROM ASS_USE_MULTI_DT DT ORDER BY USE_MULTI_ID ASC) tmp ON A.USER_MASTER_ID = tmp.USER_MASTER_ID
|
|
185
|
--INNER JOIN (SELECT BRANCH_ID,USER_MASTER_ID,COUNT(*) AS SL FROM ASS_USE_MULTI_DT WHERE BRANCH_ID = @BRANCH_LOGIN GROUP BY BRANCH_ID,USER_MASTER_ID) DTT ON DTT.USER_MASTER_ID = A.USER_MASTER_ID
|
|
186
|
LEFT JOIN ASS_USE_CONFIRM_MASTER B ON A.USER_MASTER_ID = B.USER_MASTER_ID
|
|
187
|
LEFT JOIN TL_USER D ON B.MAKER_ID = D.TLNANME
|
|
188
|
WHERE 1 = 1
|
|
189
|
AND (A.USER_MASTER_ID LIKE '%' + @p_USER_MASTER_ID + '%' OR @p_USER_MASTER_ID IS NULL OR @p_USER_MASTER_ID = '')
|
|
190
|
AND (CONVERT(DATE,ISNULL(A.APPROVE_DT,A.USE_EXPORT_DT),103) >=CONVERT(DATE, @p_FROMDATE, 103) OR @p_FROMDATE IS NULL OR @p_FROMDATE = '')
|
|
191
|
AND (CONVERT(DATE,ISNULL(A.APPROVE_DT,A.USE_EXPORT_DT),103) <=CONVERT(DATETIME, @p_TODATE, 103) OR @p_TODATE IS NULL OR @p_TODATE = '')
|
|
192
|
AND (A.USER_EXPORT LIKE '%' + @p_USER_EXPORT + '%' OR @p_USER_EXPORT IS NULL OR @p_USER_EXPORT = '')
|
|
193
|
AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR @p_NOTES IS NULL OR @p_NOTES = '')
|
|
194
|
AND A.AUTH_STATUS = 'A'
|
|
195
|
AND A.AUTH_STATUS_KT <>'A'
|
|
196
|
AND A.APPROVE_DT>=CONVERT(DATETIME,'2022-17-10',103)
|
|
197
|
AND ( (@p_AUTH_STATUS = 'Y' AND A.USER_MASTER_ID IN (SELECT G.USER_MASTER_ID FROM ASS_USE_CONFIRM_MASTER G))
|
|
198
|
OR (@p_AUTH_STATUS = 'N' AND A.USER_MASTER_ID NOT IN (SELECT G.USER_MASTER_ID FROM ASS_USE_CONFIRM_MASTER G)
|
|
199
|
OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' )))
|
|
200
|
--AND ( (@p_AUTH_STATUS = 'Y' AND A.USER_MASTER_ID = B.USER_MASTER_ID)
|
|
201
|
--OR (@p_AUTH_STATUS = 'N' AND A.USER_MASTER_ID <> B.USER_MASTER_ID
|
|
202
|
--OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' )))
|
|
203
|
|
|
204
|
AND (EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE('GDDV',
|
|
205
|
tmp.BRANCH_ID,
|
|
206
|
tmp.DEPT_ID) US
|
|
207
|
WHERE US.TLNANME = @p_USER_LOGIN))
|
|
208
|
|
|
209
|
ORDER BY A.CREATE_DT DESC
|
|
210
|
) SELECT * FROM QUERY_DATA WHERE __ROWNUM > 0 AND __ROWNUM <= 10
|
|
211
|
END-- PAGING END
|