[Home] [Help]
PACKAGE BODY: APPS.JTF_UM_APPROVAL_REQUESTS_PVT
Source
1 PACKAGE BODY JTF_UM_APPROVAL_REQUESTS_PVT as
2 /* $Header: JTFVAPRB.pls 120.2 2006/03/10 03:51:11 vimohan ship $ */
3
4
5 /**
6 * Procedure : PENDING_APPROVAL_SYSADMIN
7 * Type : Private
8 * Pre_reqs : None
9 * Description : Return the pending requests foy sysadmin
10 * Parameters :
11 * input parameters
12 * @param p_sort_order
13 * description: The sort order
14 * required : Y
15 * validation : Must be a valid sort order
16 * p_number_of_records:
17 * description: The number of records to retrieve from a database
18 * required : Y
19 * validation : Must be a valid number
20 * output parameters
21 * x_result: APPROVAL_REQUEST_TABLE_TYPE
22 */
23 procedure PENDING_APPROVAL_SYSADMIN(
24 p_sort_order in varchar2,
25 p_number_of_records in number,
26 x_result out NOCOPY APPROVAL_REQUEST_TABLE_TYPE) IS
27
28 l_rownum number := 10; -- Default value
29
30 CURSOR APPR_REQ_LAST_UPDATE_DATE IS
31
32 SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
33 ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE, APPROVER FROM (
34
35 SELECT sys_requests.REG_ID, sys_requests.REG_LAST_UPDATE_DATE, sys_requests.USER_NAME,
36 sys_requests.PARTY_TYPE, sys_requests.PARTY_ID, sys_requests.ENTITY_SOURCE,
37 sys_requests.ENTITY_NAME, sys_requests.WF_ITEM_TYPE, sys_requests.APPROVER FROM (
38
39 SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
40 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
41 'USERTYPE' ENTITY_SOURCE, UT.USERTYPE_SHORTNAME ENTITY_NAME,
42 UTREG.WF_ITEM_TYPE WF_ITEM_TYPE, FU2.USER_NAME APPROVER
43 FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
44 JTF_UM_USERTYPE_REG UTREG, FND_USER FU, FND_USER FU2
45 WHERE UTREG.STATUS_CODE = 'PENDING'
46 AND UTREG.USERTYPE_ID = UT.USERTYPE_ID
47 AND UT.APPROVAL_ID = AP.APPROVAL_ID
48 AND AP.USE_PENDING_REQ_FLAG = 'Y'
49 AND UTREG.USER_ID = FU.USER_ID
50 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
51 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
52 AND FU2.USER_ID (+) = UTREG.APPROVER_USER_ID
53
54 UNION ALL
55
56 SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
57 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
58 'ENROLLMENT' ENTITY_SOURCE, SUB.SUBSCRIPTION_NAME ENTITY_NAME,
59 SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE, FU2.USER_NAME APPROVER
60 FROM JTF_UM_SUBSCRIPTIONS_VL SUB,
61 JTF_UM_APPROVALS_B AP,
62 HZ_PARTIES PARTY,
63 JTF_UM_SUBSCRIPTION_REG SUBREG,
64 FND_USER FU,
65 JTF_UM_USERTYPE_REG UTREG,
66 FND_USER FU2
67 WHERE SUBREG.STATUS_CODE = 'PENDING'
68 AND SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
69 AND SUB.APPROVAL_ID = AP.APPROVAL_ID
70 AND AP.USE_PENDING_REQ_FLAG = 'Y'
71 AND SUBREG.USER_ID = FU.USER_ID
72 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
73 AND (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
74 AND SUBREG.USER_ID = UTREG.USER_ID
75 AND UTREG.STATUS_CODE <> 'PENDING'
76 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
77 AND FU2.USER_ID (+) = SUBREG.APPROVER_USER_ID
78
79 ) sys_requests order by sys_requests.REG_LAST_UPDATE_DATE
80
81 ) all_requests where rownum < l_rownum;
82
83 CURSOR APPR_REQ_USER_NAME IS
84
85 SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID, ENTITY_SOURCE,
86 ENTITY_NAME, WF_ITEM_TYPE, APPROVER FROM (
87
88 SELECT sys_requests.REG_ID, sys_requests.REG_LAST_UPDATE_DATE, sys_requests.USER_NAME,
89 sys_requests.PARTY_TYPE, sys_requests.PARTY_ID, sys_requests.ENTITY_SOURCE,
90 sys_requests.ENTITY_NAME, sys_requests.WF_ITEM_TYPE, sys_requests.APPROVER FROM (
91
92 SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
93 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
94 'USERTYPE' ENTITY_SOURCE, UT.USERTYPE_SHORTNAME ENTITY_NAME,
95 UTREG.WF_ITEM_TYPE WF_ITEM_TYPE, FU2.USER_NAME APPROVER
96 FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
97 JTF_UM_USERTYPE_REG UTREG, FND_USER FU, FND_USER FU2
98 WHERE UTREG.STATUS_CODE = 'PENDING'
99 AND UTREG.USERTYPE_ID = UT.USERTYPE_ID
100 AND UT.APPROVAL_ID = AP.APPROVAL_ID
101 AND AP.USE_PENDING_REQ_FLAG = 'Y'
102 AND UTREG.USER_ID = FU.USER_ID
103 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
104 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
105 AND FU2.USER_ID (+) = UTREG.APPROVER_USER_ID
106
107 UNION ALL
108
109 SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
110 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
111 'ENROLLMENT' ENTITY_SOURCE, SUB.SUBSCRIPTION_NAME ENTITY_NAME,
112 SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE, FU2.USER_NAME APPROVER
113 FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
114 JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG,
115 FND_USER FU2
116 WHERE SUBREG.STATUS_CODE = 'PENDING'
117 AND SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
118 AND SUB.APPROVAL_ID = AP.APPROVAL_ID
119 AND AP.USE_PENDING_REQ_FLAG = 'Y'
120 AND SUBREG.USER_ID = FU.USER_ID
121 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
122 AND (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
123 AND SUBREG.USER_ID = UTREG.USER_ID
124 AND UTREG.STATUS_CODE <> 'PENDING'
125 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
126 AND FU2.USER_ID (+) = SUBREG.APPROVER_USER_ID
127
128 ) sys_requests order by sys_requests.USER_NAME
129
130 ) all_requests where rownum < l_rownum;
131
132 CURSOR APPR_REQ_ENTITY_SOURCE IS
133
134 SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
135 ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE, APPROVER FROM (
136
137 SELECT sys_requests.REG_ID, sys_requests.REG_LAST_UPDATE_DATE,
138 sys_requests.USER_NAME, sys_requests.PARTY_TYPE, sys_requests.PARTY_ID,
139 sys_requests.ENTITY_SOURCE, sys_requests.ENTITY_NAME,
140 sys_requests.WF_ITEM_TYPE, sys_requests.APPROVER FROM (
141
142 SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
143 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
144 'USERTYPE' ENTITY_SOURCE, UT.USERTYPE_SHORTNAME ENTITY_NAME,
145 UTREG.WF_ITEM_TYPE WF_ITEM_TYPE, FU2.USER_NAME APPROVER
146 FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
147 JTF_UM_USERTYPE_REG UTREG, FND_USER FU, FND_USER FU2
148 WHERE UTREG.STATUS_CODE = 'PENDING'
149 AND UTREG.USERTYPE_ID = UT.USERTYPE_ID
150 AND UT.APPROVAL_ID = AP.APPROVAL_ID
151 AND AP.USE_PENDING_REQ_FLAG = 'Y'
152 AND UTREG.USER_ID = FU.USER_ID
153 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
154 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
155 AND FU2.USER_ID (+) = UTREG.APPROVER_USER_ID
156
157 UNION ALL
158
159 SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE
160 REG_LAST_UPDATE_DATE, FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE
161 PARTY_TYPE, PARTY.PARTY_ID PARTY_ID, 'ENROLLMENT' ENTITY_SOURCE,
162 SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE,
163 FU2.USER_NAME APPROVER
164 FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
165 JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG,
166 FND_USER FU2
167 WHERE SUBREG.STATUS_CODE = 'PENDING'
168 AND SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
169 AND SUB.APPROVAL_ID = AP.APPROVAL_ID
170 AND AP.USE_PENDING_REQ_FLAG = 'Y'
171 AND SUBREG.USER_ID = FU.USER_ID
172 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
173 AND (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
174 AND SUBREG.USER_ID = UTREG.USER_ID
175 AND UTREG.STATUS_CODE <> 'PENDING'
176 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
177 AND FU2.USER_ID (+) = SUBREG.APPROVER_USER_ID
178
179 ) sys_requests order by sys_requests.ENTITY_SOURCE
180
181 ) all_requests where rownum < l_rownum ;
182
183 CURSOR APPR_REQ_ENTITY_NAME IS
184
185 SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
186 ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE, APPROVER FROM (
187
188 SELECT sys_requests.REG_ID, sys_requests.REG_LAST_UPDATE_DATE,
189 sys_requests.USER_NAME, sys_requests.PARTY_TYPE, sys_requests.PARTY_ID,
190 sys_requests.ENTITY_SOURCE, sys_requests.ENTITY_NAME,
191 sys_requests.WF_ITEM_TYPE, sys_requests.APPROVER FROM (
192
193 SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
194 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
195 PARTY.PARTY_ID PARTY_ID, 'USERTYPE' ENTITY_SOURCE,
196 UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE,
197 FU2.USER_NAME APPROVER
198 FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
199 JTF_UM_USERTYPE_REG UTREG, FND_USER FU, FND_USER FU2
200 WHERE UTREG.STATUS_CODE = 'PENDING'
201 AND UTREG.USERTYPE_ID = UT.USERTYPE_ID
202 AND UT.APPROVAL_ID = AP.APPROVAL_ID
203 AND AP.USE_PENDING_REQ_FLAG = 'Y'
204 AND UTREG.USER_ID = FU.USER_ID
205 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
206 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
207 AND FU2.USER_ID (+) = UTREG.APPROVER_USER_ID
208
209 UNION ALL
210
211 SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
212 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
213 'ENROLLMENT' ENTITY_SOURCE, SUB.SUBSCRIPTION_NAME ENTITY_NAME,
214 SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE, FU2.USER_NAME APPROVER
215 FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
216 JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG,
217 FND_USER FU2
218 WHERE SUBREG.STATUS_CODE = 'PENDING'
219 AND SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
220 AND SUB.APPROVAL_ID = AP.APPROVAL_ID
221 AND AP.USE_PENDING_REQ_FLAG = 'Y'
222 AND SUBREG.USER_ID = FU.USER_ID
223 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
224 AND (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
225 AND SUBREG.USER_ID = UTREG.USER_ID
226 AND UTREG.STATUS_CODE <> 'PENDING'
227 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
228 AND FU2.USER_ID (+) = SUBREG.APPROVER_USER_ID
229
230 ) sys_requests order by sys_requests.ENTITY_NAME
231
232 ) all_requests where rownum < l_rownum;
233
234 CURSOR APPR_REQ_ENTITY_NUMBER IS
235
236 SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
237 ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE, APPROVER FROM (
238
239 SELECT sys_requests.REG_ID, sys_requests.REG_LAST_UPDATE_DATE,
240 sys_requests.USER_NAME, sys_requests.PARTY_TYPE, sys_requests.PARTY_ID,
241 sys_requests.ENTITY_SOURCE, sys_requests.ENTITY_NAME,
242 sys_requests.WF_ITEM_TYPE, sys_requests.APPROVER FROM (
243
244 SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
245 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
246 PARTY.PARTY_ID PARTY_ID, 'USERTYPE' ENTITY_SOURCE,
247 UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE,
248 FU2.USER_NAME APPROVER
249 FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
250 JTF_UM_USERTYPE_REG UTREG, FND_USER FU, FND_USER FU2
251 WHERE UTREG.STATUS_CODE = 'PENDING'
252 AND UTREG.USERTYPE_ID = UT.USERTYPE_ID
253 AND UT.APPROVAL_ID = AP.APPROVAL_ID
254 AND AP.USE_PENDING_REQ_FLAG = 'Y'
255 AND UTREG.USER_ID = FU.USER_ID
256 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
257 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
258 AND FU2.USER_ID (+) = UTREG.APPROVER_USER_ID
259
260 UNION ALL
261
262 SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
263 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
264 'ENROLLMENT' ENTITY_SOURCE, SUB.SUBSCRIPTION_NAME ENTITY_NAME,
265 SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE, FU2.USER_NAME APPROVER
266 FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
267 JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG,
268 FND_USER FU2
269 WHERE SUBREG.STATUS_CODE = 'PENDING'
270 AND SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
271 AND SUB.APPROVAL_ID = AP.APPROVAL_ID
272 AND AP.USE_PENDING_REQ_FLAG = 'Y'
273 AND SUBREG.USER_ID = FU.USER_ID
274 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
275 AND (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
276 AND SUBREG.USER_ID = UTREG.USER_ID
277 AND UTREG.STATUS_CODE <> 'PENDING'
278 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
279 AND FU2.USER_ID (+) = SUBREG.APPROVER_USER_ID
280
281 ) sys_requests order by sys_requests.REG_ID
282
283 ) all_requests where rownum < l_rownum;
284
285 CURSOR APPR_REQ_APPROVER IS
286
287 SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
288 ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE, APPROVER FROM (
289
290 SELECT sys_requests.REG_ID, sys_requests.REG_LAST_UPDATE_DATE,
291 sys_requests.USER_NAME, sys_requests.PARTY_TYPE, sys_requests.PARTY_ID,
292 sys_requests.ENTITY_SOURCE, sys_requests.ENTITY_NAME,
293 sys_requests.WF_ITEM_TYPE, sys_requests.APPROVER FROM (
294
295 SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
296 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
297 PARTY.PARTY_ID PARTY_ID, 'USERTYPE' ENTITY_SOURCE,
298 UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE,
299 FU2.USER_NAME APPROVER
300 FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
301 JTF_UM_USERTYPE_REG UTREG, FND_USER FU, FND_USER FU2
302 WHERE UTREG.STATUS_CODE = 'PENDING'
303 AND UTREG.USERTYPE_ID = UT.USERTYPE_ID
304 AND UT.APPROVAL_ID = AP.APPROVAL_ID
305 AND AP.USE_PENDING_REQ_FLAG = 'Y'
306 AND UTREG.USER_ID = FU.USER_ID
307 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
308 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
309 AND FU2.USER_ID (+) = UTREG.APPROVER_USER_ID
310
311 UNION ALL
312
313 SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
314 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
315 'ENROLLMENT' ENTITY_SOURCE, SUB.SUBSCRIPTION_NAME ENTITY_NAME,
316 SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE, FU2.USER_NAME APPROVER
317 FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
318 JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG,
319 FND_USER FU2
320 WHERE SUBREG.STATUS_CODE = 'PENDING'
321 AND SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
322 AND SUB.APPROVAL_ID = AP.APPROVAL_ID
323 AND AP.USE_PENDING_REQ_FLAG = 'Y'
324 AND SUBREG.USER_ID = FU.USER_ID
325 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
326 AND (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
327 AND SUBREG.USER_ID = UTREG.USER_ID
328 AND UTREG.STATUS_CODE <> 'PENDING'
329 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
330 AND FU2.USER_ID (+) = SUBREG.APPROVER_USER_ID
331
332 ) sys_requests order by sys_requests.APPROVER
333
334 ) all_requests where rownum < l_rownum;
335
336 l_party_id HZ_PARTIES.PARTY_ID%TYPE;
337
338 CURSOR GET_COMPANY_NAME IS
339
340 SELECT PARTY.PARTY_NAME FROM HZ_PARTIES PARTY, HZ_RELATIONSHIPS PREL
341 WHERE PARTY.PARTY_ID = PREL.OBJECT_ID
342 AND PREL.PARTY_ID = l_party_id
343 AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
344 AND PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
345 AND PREL.START_DATE < SYSDATE
346 AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
347 AND PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF');
348
349
350 l_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
351
352 i NUMBER := 1;
353
354 BEGIN
355
356 IF p_number_of_records IS NOT NULL AND p_number_of_records <> 0 THEN
357 l_rownum := p_number_of_records;
358 END IF;
359
360 IF UPPER(p_sort_order) = 'USER_NAME' THEN
361 FOR j in APPR_REQ_USER_NAME LOOP
362 x_result(i).USER_NAME := j.USER_NAME;
363 x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;
364 x_result(i).ENTITY_SOURCE := j.ENTITY_SOURCE;
365 x_result(i).ENTITY_NAME := j.ENTITY_NAME;
366 x_result(i).WF_ITEM_TYPE := j.WF_ITEM_TYPE;
367 x_result(i).REG_ID := j.REG_ID;
368 x_result(i).APPROVER := j.APPROVER;
369 l_party_type := j.PARTY_TYPE;
370 l_party_id := j.PARTY_ID;
371
372 FOR r in GET_COMPANY_NAME LOOP
373
374 x_result(i).COMPANY_NAME := r.party_name;
375
376 END LOOP;
377
378 i := i + 1;
379
380 END LOOP;
381
382
383 ELSIF UPPER(p_sort_order) = 'ENTITY_SOURCE' THEN
384 FOR j in APPR_REQ_ENTITY_SOURCE LOOP
385 x_result(i).USER_NAME := j.USER_NAME;
386 x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;
387 x_result(i).ENTITY_SOURCE := j.ENTITY_SOURCE;
388 x_result(i).ENTITY_NAME := j.ENTITY_NAME;
389 x_result(i).WF_ITEM_TYPE := j.WF_ITEM_TYPE;
390 x_result(i).REG_ID := j.REG_ID;
391 x_result(i).APPROVER := j.APPROVER;
392 l_party_type := j.PARTY_TYPE;
393 l_party_id := j.PARTY_ID;
394
395 FOR r in GET_COMPANY_NAME LOOP
396
397 x_result(i).COMPANY_NAME := r.party_name;
398
399 END LOOP;
400
401 i := i + 1;
402
403 END LOOP;
404
405 ELSIF UPPER(p_sort_order) = 'ENTITY_NAME' THEN
406 FOR j in APPR_REQ_ENTITY_NAME LOOP
407 x_result(i).USER_NAME := j.USER_NAME;
408 x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;
409 x_result(i).ENTITY_SOURCE := j.ENTITY_SOURCE;
410 x_result(i).ENTITY_NAME := j.ENTITY_NAME;
411 x_result(i).WF_ITEM_TYPE := j.WF_ITEM_TYPE;
412 x_result(i).REG_ID := j.REG_ID;
413 x_result(i).APPROVER := j.APPROVER;
414 l_party_type := j.PARTY_TYPE;
415 l_party_id := j.PARTY_ID;
416
417 FOR r in GET_COMPANY_NAME LOOP
418
419 x_result(i).COMPANY_NAME := r.party_name;
420
421 END LOOP;
422
423 i := i + 1;
424
425 END LOOP;
426
427 ELSIF UPPER(p_sort_order) = 'ENTITY_NUMBER' THEN
428 FOR j in APPR_REQ_ENTITY_NUMBER LOOP
429 x_result(i).USER_NAME := j.USER_NAME;
430 x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;
431 x_result(i).ENTITY_SOURCE := j.ENTITY_SOURCE;
432 x_result(i).ENTITY_NAME := j.ENTITY_NAME;
433 x_result(i).WF_ITEM_TYPE := j.WF_ITEM_TYPE;
434 x_result(i).REG_ID := j.REG_ID;
435 x_result(i).APPROVER := j.APPROVER;
436 l_party_type := j.PARTY_TYPE;
437 l_party_id := j.PARTY_ID;
438
439 FOR r in GET_COMPANY_NAME LOOP
440
441 x_result(i).COMPANY_NAME := r.party_name;
442
443 END LOOP;
444
445 i := i + 1;
446
447 END LOOP;
448
449 ELSIF UPPER(p_sort_order) = 'APPROVER' THEN
450 FOR j in APPR_REQ_APPROVER LOOP
451 x_result(i).USER_NAME := j.USER_NAME;
452 x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;
453 x_result(i).ENTITY_SOURCE := j.ENTITY_SOURCE;
454 x_result(i).ENTITY_NAME := j.ENTITY_NAME;
455 x_result(i).WF_ITEM_TYPE := j.WF_ITEM_TYPE;
456 x_result(i).REG_ID := j.REG_ID;
457 x_result(i).APPROVER := j.APPROVER;
458 l_party_type := j.PARTY_TYPE;
459 l_party_id := j.PARTY_ID;
460
461 FOR r in GET_COMPANY_NAME LOOP
462
463 x_result(i).COMPANY_NAME := r.party_name;
464
465 END LOOP;
466
467 i := i + 1;
468
469 END LOOP;
470
471 ELSE
472
473 FOR j in APPR_REQ_LAST_UPDATE_DATE LOOP
474 x_result(i).USER_NAME := j.USER_NAME;
475 x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;
476 x_result(i).ENTITY_SOURCE := j.ENTITY_SOURCE;
477 x_result(i).ENTITY_NAME := j.ENTITY_NAME;
478 x_result(i).WF_ITEM_TYPE := j.WF_ITEM_TYPE;
479 x_result(i).REG_ID := j.REG_ID;
480 x_result(i).APPROVER := j.APPROVER;
481 l_party_type := j.PARTY_TYPE;
482 l_party_id := j.PARTY_ID;
483
484 FOR r in GET_COMPANY_NAME LOOP
485
486 x_result(i).COMPANY_NAME := r.party_name;
487
488 END LOOP;
489
490 i := i + 1;
491
492 END LOOP;
493
494 END IF;
495
496 END PENDING_APPROVAL_SYSADMIN;
497
498
499 /**
500 * Procedure : PENDING_APPROVAL_PRIMARY
501 * Type : Private
502 * Pre_reqs : None
503 * Description : Return the pending requests foy Primary User
504 * Parameters :
505 * input parameters
506 * @param p_sort_order
507 * description: The sort order
508 * required : Y
509 * validation : Must be a valid sort order
510 * p_number_of_records:
511 * description: The number of records to retrieve from a database
512 * required : Y
513 * validation : Must be a valid number
514 * p_approver_user_id
515 * description: The user_id of a logged in user
516 * required : Y
517 * validation : Must be a valid user_id
518 * output parameters
519 * x_result: APPROVAL_REQUEST_TABLE_TYPE
520 */
521 procedure PENDING_APPROVAL_PRIMARY(
522 p_sort_order in varchar2,
523 p_number_of_records in number,
524 p_approver_user_id in number,
525 x_result out NOCOPY APPROVAL_REQUEST_TABLE_TYPE) IS
526
527 l_rownum number := 10; -- Default value
528 l_dummy_user_id FND_USER.USER_ID%TYPE;
529 l_company_id HZ_PARTIES.PARTY_ID%TYPE;
530 l_party_name HZ_PARTIES.PARTY_NAME%TYPE;
531
532 CURSOR APPR_REQ_USER_NAME IS
533
534 SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, ENTITY_SOURCE, ENTITY_NAME,
535 WF_ITEM_TYPE FROM (
536
537 SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
538 FU.USER_NAME USER_NAME, 'USERTYPE' ENTITY_SOURCE,
539 UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
540 FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP,
541 HZ_RELATIONSHIPS PREL, JTF_UM_USERTYPE_REG UTREG, FND_USER FU
542
543 WHERE UTREG.STATUS_CODE = 'PENDING'
544 AND UTREG.USERTYPE_ID = UT.USERTYPE_ID
545 AND UT.APPROVAL_ID = AP.APPROVAL_ID
546 AND AP.USE_PENDING_REQ_FLAG = 'Y'
547 AND UTREG.USER_ID = FU.USER_ID
548 AND FU.CUSTOMER_ID = PREL.PARTY_ID
549 AND PREL.OBJECT_ID = l_company_id
550 AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
551 AND PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
552 AND PREL.START_DATE < SYSDATE
553 AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
554 AND PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
555 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
556 AND UTREG.APPROVER_USER_ID = l_dummy_user_id
557
558 UNION ALL
559
560 SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
561 FU.USER_NAME USER_NAME, 'ENROLLMENT' ENTITY_SOURCE,
562 SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
563 FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP,
564 HZ_RELATIONSHIPS PREL, JTF_UM_SUBSCRIPTION_REG SUBREG,
565 FND_USER FU, JTF_UM_USERTYPE_REG UTREG
566 WHERE SUBREG.STATUS_CODE = 'PENDING'
567 AND SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
568 AND SUB.APPROVAL_ID = AP.APPROVAL_ID
569 AND AP.USE_PENDING_REQ_FLAG = 'Y'
570 AND SUBREG.USER_ID = FU.USER_ID
571 AND FU.CUSTOMER_ID = PREL.PARTY_ID
572 AND PREL.OBJECT_ID = l_company_id
573 AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
574 AND PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
575 AND PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
576 AND PREL.START_DATE < SYSDATE
577 AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
578 AND (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
579 AND SUBREG.USER_ID = UTREG.USER_ID
580 AND UTREG.STATUS_CODE <> 'PENDING'
581 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
582 AND SUBREG.APPROVER_USER_ID = l_dummy_user_id
583
584 ) pri_requests where rownum < l_rownum order by USER_NAME;
585
586 CURSOR APPR_REQ_LAST_UPDATE_DATE IS
587
588 SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, ENTITY_SOURCE, ENTITY_NAME,
589 WF_ITEM_TYPE FROM (
590
591 SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
592 FU.USER_NAME USER_NAME, 'USERTYPE' ENTITY_SOURCE,
593 UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
594 FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP,
595 HZ_RELATIONSHIPS PREL, JTF_UM_USERTYPE_REG UTREG, FND_USER FU
596 WHERE UTREG.STATUS_CODE = 'PENDING'
597 AND UTREG.USERTYPE_ID = UT.USERTYPE_ID
598 AND UT.APPROVAL_ID = AP.APPROVAL_ID
599 AND AP.USE_PENDING_REQ_FLAG = 'Y'
600 AND UTREG.USER_ID = FU.USER_ID
601 AND FU.CUSTOMER_ID = PREL.PARTY_ID
602 AND PREL.OBJECT_ID = l_company_id
603 AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
604 AND PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
605 AND PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
606 AND PREL.START_DATE < SYSDATE
607 AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
608 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
609 AND UTREG.APPROVER_USER_ID = l_dummy_user_id
610
611 UNION ALL
612
613 SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
614 FU.USER_NAME USER_NAME, 'ENROLLMENT' ENTITY_SOURCE,
615 SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
616 FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP,
617 HZ_RELATIONSHIPS PREL, JTF_UM_SUBSCRIPTION_REG SUBREG,
618 FND_USER FU, JTF_UM_USERTYPE_REG UTREG
619 WHERE SUBREG.STATUS_CODE = 'PENDING'
620 AND SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
621 AND SUB.APPROVAL_ID = AP.APPROVAL_ID
622 AND AP.USE_PENDING_REQ_FLAG = 'Y'
623 AND SUBREG.USER_ID = FU.USER_ID
624 AND FU.CUSTOMER_ID = PREL.PARTY_ID
625 AND PREL.OBJECT_ID = l_company_id
626 AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
627 AND PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
628 AND PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
629 AND PREL.START_DATE < SYSDATE
630 AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
631 AND (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
632 AND SUBREG.USER_ID = UTREG.USER_ID
633 AND UTREG.STATUS_CODE <> 'PENDING'
634 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
635 AND SUBREG.APPROVER_USER_ID = l_dummy_user_id
636
637 ) pri_requests where rownum < l_rownum order by REG_LAST_UPDATE_DATE;
638
639 CURSOR APPR_REQ_ENTITY_SOURCE IS
640
641 SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, ENTITY_SOURCE, ENTITY_NAME,
642 WF_ITEM_TYPE FROM (
643
644 SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
645 FU.USER_NAME USER_NAME, 'USERTYPE' ENTITY_SOURCE,
646 UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
647 FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP,
648 HZ_RELATIONSHIPS PREL, JTF_UM_USERTYPE_REG UTREG, FND_USER FU
649 WHERE UTREG.STATUS_CODE = 'PENDING'
650 AND UTREG.USERTYPE_ID = UT.USERTYPE_ID
651 AND UT.APPROVAL_ID = AP.APPROVAL_ID
652 AND AP.USE_PENDING_REQ_FLAG = 'Y'
653 AND UTREG.USER_ID = FU.USER_ID
654 AND FU.CUSTOMER_ID = PREL.PARTY_ID
655 AND PREL.OBJECT_ID = l_company_id
656 AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
657 AND PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
658 AND PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
659 AND PREL.START_DATE < SYSDATE
660 AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
661 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
662 AND UTREG.APPROVER_USER_ID = l_dummy_user_id
663
664 UNION ALL
665
666 SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
667 FU.USER_NAME USER_NAME,'ENROLLMENT' ENTITY_SOURCE,
668 SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
669 FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP,
670 HZ_RELATIONSHIPS PREL, JTF_UM_SUBSCRIPTION_REG SUBREG,
671 FND_USER FU, JTF_UM_USERTYPE_REG UTREG
672 WHERE SUBREG.STATUS_CODE = 'PENDING'
673 AND SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
674 AND SUB.APPROVAL_ID = AP.APPROVAL_ID
675 AND AP.USE_PENDING_REQ_FLAG = 'Y'
676 AND SUBREG.USER_ID = FU.USER_ID
677 AND FU.CUSTOMER_ID = PREL.PARTY_ID
678 AND PREL.OBJECT_ID = l_company_id
679 AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
680 AND PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
681 AND PREL.START_DATE < SYSDATE
682 AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
683 AND PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
684 AND (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
685 AND SUBREG.USER_ID = UTREG.USER_ID
686 AND UTREG.STATUS_CODE <> 'PENDING'
687 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
688 AND SUBREG.APPROVER_USER_ID = l_dummy_user_id
689
690 ) pri_requests where rownum < l_rownum order by ENTITY_SOURCE;
691
692 CURSOR APPR_REQ_ENTITY_NAME IS
693
694 SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, ENTITY_SOURCE, ENTITY_NAME,
695 WF_ITEM_TYPE FROM (
696
697 SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE, FU.USER_NAME USER_NAME, 'USERTYPE' ENTITY_SOURCE, UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
698 FROM JTF_UM_USERTYPES_VL UT,
699 JTF_UM_APPROVALS_B AP,
700 HZ_RELATIONSHIPS PREL,
701 JTF_UM_USERTYPE_REG UTREG,
702 FND_USER FU
703
704 WHERE UTREG.STATUS_CODE = 'PENDING'
705 AND UTREG.USERTYPE_ID = UT.USERTYPE_ID
706 AND UT.APPROVAL_ID = AP.APPROVAL_ID
707 AND AP.USE_PENDING_REQ_FLAG = 'Y'
708 AND UTREG.USER_ID = FU.USER_ID
709 AND FU.CUSTOMER_ID = PREL.PARTY_ID
710 AND PREL.OBJECT_ID = l_company_id
711 AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
712 AND PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
713 AND PREL.START_DATE < SYSDATE
714 AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
715 AND PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
716 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
717 AND UTREG.APPROVER_USER_ID = l_dummy_user_id
718
719 UNION ALL
720
721 SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
722 FU.USER_NAME USER_NAME,'ENROLLMENT' ENTITY_SOURCE,
723 SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
724 FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP,
725 HZ_RELATIONSHIPS PREL, JTF_UM_SUBSCRIPTION_REG SUBREG,
726 FND_USER FU, JTF_UM_USERTYPE_REG UTREG
727 WHERE SUBREG.STATUS_CODE = 'PENDING'
728 AND SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
729 AND SUB.APPROVAL_ID = AP.APPROVAL_ID
730 AND AP.USE_PENDING_REQ_FLAG = 'Y'
731 AND SUBREG.USER_ID = FU.USER_ID
732 AND FU.CUSTOMER_ID = PREL.PARTY_ID
733 AND PREL.OBJECT_ID = l_company_id
734 AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
735 AND PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
736 AND PREL.START_DATE < SYSDATE
737 AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
738 AND PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
739 AND (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
740 AND SUBREG.USER_ID = UTREG.USER_ID
741 AND UTREG.STATUS_CODE <> 'PENDING'
742 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
743 AND SUBREG.APPROVER_USER_ID = l_dummy_user_id
744
745 ) pri_requests where rownum < l_rownum order by ENTITY_NAME;
746
747 CURSOR APPR_REQ_ENTITY_NUMBER IS
748
749 SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, ENTITY_SOURCE, ENTITY_NAME,
750 WF_ITEM_TYPE FROM (
751
752 SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE, FU.USER_NAME USER_NAME, 'USERTYPE' ENTITY_SOURCE, UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
753 FROM JTF_UM_USERTYPES_VL UT,
754 JTF_UM_APPROVALS_B AP,
755 HZ_RELATIONSHIPS PREL,
756 JTF_UM_USERTYPE_REG UTREG,
757 FND_USER FU
758
759 WHERE UTREG.STATUS_CODE = 'PENDING'
760 AND UTREG.USERTYPE_ID = UT.USERTYPE_ID
761 AND UT.APPROVAL_ID = AP.APPROVAL_ID
762 AND AP.USE_PENDING_REQ_FLAG = 'Y'
763 AND UTREG.USER_ID = FU.USER_ID
764 AND FU.CUSTOMER_ID = PREL.PARTY_ID
765 AND PREL.OBJECT_ID = l_company_id
766 AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
767 AND PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
768 AND PREL.START_DATE < SYSDATE
769 AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
770 AND PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
771 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
772 AND UTREG.APPROVER_USER_ID = l_dummy_user_id
773
774 UNION ALL
775
776 SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
777 FU.USER_NAME USER_NAME,'ENROLLMENT' ENTITY_SOURCE,
778 SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
779 FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP,
780 HZ_RELATIONSHIPS PREL, JTF_UM_SUBSCRIPTION_REG SUBREG,
781 FND_USER FU, JTF_UM_USERTYPE_REG UTREG
782 WHERE SUBREG.STATUS_CODE = 'PENDING'
783 AND SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
784 AND SUB.APPROVAL_ID = AP.APPROVAL_ID
785 AND AP.USE_PENDING_REQ_FLAG = 'Y'
786 AND SUBREG.USER_ID = FU.USER_ID
787 AND FU.CUSTOMER_ID = PREL.PARTY_ID
788 AND PREL.OBJECT_ID = l_company_id
789 AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
790 AND PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
791 AND PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
792 AND PREL.START_DATE < SYSDATE
793 AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
794 AND (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
795 AND SUBREG.USER_ID = UTREG.USER_ID
796 AND UTREG.STATUS_CODE <> 'PENDING'
797 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
798 AND SUBREG.APPROVER_USER_ID = l_dummy_user_id
799
800 ) pri_requests where rownum < l_rownum order by REG_ID;
801
802 CURSOR GET_COMPANY_NAME IS
803 SELECT PARTY.PARTY_NAME, PARTY.PARTY_ID
804 FROM HZ_PARTIES PARTY, HZ_RELATIONSHIPS PREL, FND_USER FU
805 WHERE PARTY.PARTY_ID = PREL.OBJECT_ID
806 AND PREL.PARTY_ID = FU.CUSTOMER_ID
807 AND FU.USER_ID = p_approver_user_id
808 AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
809 AND PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
810 AND PREL.START_DATE < SYSDATE
811 AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
812 AND PREL.RELATIONSHIP_CODE = 'EMPLOYEE_OF';
813
814 i NUMBER;
815
816 CURSOR FIND_DUMMY_USER IS
817 SELECT USER_ID
818 FROM FND_USER
819 WHERE USER_NAME = FND_PROFILE.VALUE('JTF_PRIMARY_USER');
820
821 BEGIN
822
823 IF p_number_of_records IS NOT NULL AND p_number_of_records <> 0 THEN
824 l_rownum := p_number_of_records;
825 END IF;
826
827 PENDING_APPROVAL_OWNER(
828 p_sort_order => p_sort_order,
829 p_number_of_records => p_number_of_records,
830 p_approver_user_id => p_approver_user_id,
831 x_result => x_result);
832
833 OPEN FIND_DUMMY_USER;
834 FETCH FIND_DUMMY_USER INTO l_dummy_user_id;
835 CLOSE FIND_DUMMY_USER;
836
837 IF l_dummy_user_id IS NOT NULL THEN
838
839 OPEN GET_COMPANY_NAME;
840 FETCH GET_COMPANY_NAME INTO l_party_name, l_company_id;
841 CLOSE GET_COMPANY_NAME;
842
843 i := nvl(x_result.LAST, 0) + 1;
844
845 IF UPPER(p_sort_order) = 'USER_NAME' THEN
846
847 FOR j in APPR_REQ_USER_NAME LOOP
848 x_result(i).USER_NAME := j.USER_NAME;
849 x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;
850 x_result(i).ENTITY_SOURCE := j.ENTITY_SOURCE;
851 x_result(i).ENTITY_NAME := j.ENTITY_NAME;
852 x_result(i).WF_ITEM_TYPE := j.WF_ITEM_TYPE;
853 x_result(i).REG_ID := j.REG_ID;
854 x_result(i).COMPANY_NAME := l_party_name;
855
856 i := i+1;
857 END LOOP;
858
859 ELSIF UPPER(p_sort_order) = 'ENTITY_SOURCE' THEN
860
861 FOR j in APPR_REQ_ENTITY_SOURCE LOOP
862 x_result(i).USER_NAME := j.USER_NAME;
863 x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;
864 x_result(i).ENTITY_SOURCE := j.ENTITY_SOURCE;
865 x_result(i).ENTITY_NAME := j.ENTITY_NAME;
866 x_result(i).WF_ITEM_TYPE := j.WF_ITEM_TYPE;
867 x_result(i).REG_ID := j.REG_ID;
868 x_result(i).COMPANY_NAME := l_party_name;
869
870 i := i+1;
871 END LOOP;
872
873 ELSIF UPPER(p_sort_order) = 'ENTITY_NAME' THEN
874
875 FOR j in APPR_REQ_ENTITY_NAME LOOP
876 x_result(i).USER_NAME := j.USER_NAME;
877 x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;
878 x_result(i).ENTITY_SOURCE := j.ENTITY_SOURCE;
879 x_result(i).ENTITY_NAME := j.ENTITY_NAME;
880 x_result(i).WF_ITEM_TYPE := j.WF_ITEM_TYPE;
881 x_result(i).REG_ID := j.REG_ID;
882 x_result(i).COMPANY_NAME := l_party_name;
883
884 i := i+1;
885 END LOOP;
886
887 ELSIF UPPER(p_sort_order) = 'ENTITY_NUMBER' THEN
888
889 FOR j in APPR_REQ_ENTITY_NUMBER LOOP
890 x_result(i).USER_NAME := j.USER_NAME;
891 x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;
892 x_result(i).ENTITY_SOURCE := j.ENTITY_SOURCE;
893 x_result(i).ENTITY_NAME := j.ENTITY_NAME;
894 x_result(i).WF_ITEM_TYPE := j.WF_ITEM_TYPE;
895 x_result(i).REG_ID := j.REG_ID;
896 x_result(i).COMPANY_NAME := l_party_name;
897
898 i := i+1;
899 END LOOP;
900
901 ELSE
902
903 FOR j in APPR_REQ_LAST_UPDATE_DATE LOOP
904 x_result(i).USER_NAME := j.USER_NAME;
905 x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;
906 x_result(i).ENTITY_SOURCE := j.ENTITY_SOURCE;
907 x_result(i).ENTITY_NAME := j.ENTITY_NAME;
908 x_result(i).WF_ITEM_TYPE := j.WF_ITEM_TYPE;
909 x_result(i).REG_ID := j.REG_ID;
910 x_result(i).COMPANY_NAME := l_party_name;
911
912 i := i+1;
913 END LOOP;
914
915 END IF;
916
917 END IF;
918
919 END PENDING_APPROVAL_PRIMARY;
920
921
922 /**
923 * Procedure : PENDING_APPROVAL_OWNER
924 * Type : Private
925 * Pre_reqs : None
926 * Description : Return the pending requests for the request owner
927 * Parameters :
928 * input parameters
929 * @param p_sort_order
930 * description: The sort order
931 * required : Y
932 * validation : Must be a valid sort order
933 * p_number_of_records:
934 * description: The number of records to retrieve from a database
935 * required : Y
936 * validation : Must be a valid number
937 * p_approver_user_id
938 * description: The user_id of a logged in user
939 * required : Y
940 * validation : Must be a valid user_id
941 * output parameters
942 * x_result: APPROVAL_REQUEST_TABLE_TYPE
943 */
944 procedure PENDING_APPROVAL_OWNER(
945 p_sort_order in varchar2,
946 p_number_of_records in number,
947 p_approver_user_id in number,
948 x_result out NOCOPY APPROVAL_REQUEST_TABLE_TYPE) IS
949
950 l_rownum number := 10; -- Default value
951
952 CURSOR APPR_REQ_LAST_UPDATE_DATE IS
953
954 SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
955 ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE FROM (
956
957 SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE
958 REG_LAST_UPDATE_DATE, FU.USER_NAME USER_NAME,
959 PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
960 'USERTYPE' ENTITY_SOURCE, UT.USERTYPE_SHORTNAME ENTITY_NAME,
961 UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
962 FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
963 JTF_UM_USERTYPE_REG UTREG, FND_USER FU
964 WHERE UTREG.STATUS_CODE = 'PENDING'
965 AND UTREG.USERTYPE_ID = UT.USERTYPE_ID
966 AND UT.APPROVAL_ID = AP.APPROVAL_ID
967 AND AP.USE_PENDING_REQ_FLAG = 'Y'
968 AND UTREG.USER_ID = FU.USER_ID
969 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
970 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
971 AND UTREG.APPROVER_USER_ID = p_approver_user_id
972
973 UNION ALL
974
975 SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
976 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
977 PARTY.PARTY_ID PARTY_ID, 'ENROLLMENT' ENTITY_SOURCE,
978 SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
979 FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
980 JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG
981 WHERE SUBREG.STATUS_CODE = 'PENDING'
982 AND SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
983 AND SUB.APPROVAL_ID = AP.APPROVAL_ID
984 AND AP.USE_PENDING_REQ_FLAG = 'Y'
985 AND SUBREG.USER_ID = FU.USER_ID
986 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
987 AND (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
988 AND SUBREG.USER_ID = UTREG.USER_ID
989 AND UTREG.STATUS_CODE <> 'PENDING'
990 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
991 AND SUBREG.APPROVER_USER_ID = p_approver_user_id
992 ) owner_requests where rownum < l_rownum order by REG_LAST_UPDATE_DATE;
993
994 CURSOR APPR_REQ_USER_NAME IS
995
996 SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
997 ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE FROM (
998 SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
999 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
1000 PARTY.PARTY_ID PARTY_ID, 'USERTYPE' ENTITY_SOURCE,
1001 UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
1002 FROM JTF_UM_USERTYPES_VL UT,
1003 JTF_UM_APPROVALS_B AP,
1004 HZ_PARTIES PARTY,
1005 JTF_UM_USERTYPE_REG UTREG,
1006 FND_USER FU
1007 WHERE UTREG.STATUS_CODE = 'PENDING'
1008 AND UTREG.USERTYPE_ID = UT.USERTYPE_ID
1009 AND UT.APPROVAL_ID = AP.APPROVAL_ID
1010 AND AP.USE_PENDING_REQ_FLAG = 'Y'
1011 AND UTREG.USER_ID = FU.USER_ID
1012 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
1013 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
1014 AND UTREG.APPROVER_USER_ID = p_approver_user_id
1015
1016 UNION ALL
1017
1018 SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
1019 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
1020 PARTY.PARTY_ID PARTY_ID, 'ENROLLMENT' ENTITY_SOURCE,
1021 SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
1022 FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
1023 JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG
1024 WHERE SUBREG.STATUS_CODE = 'PENDING'
1025 AND SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
1026 AND SUB.APPROVAL_ID = AP.APPROVAL_ID
1027 AND AP.USE_PENDING_REQ_FLAG = 'Y'
1028 AND SUBREG.USER_ID = FU.USER_ID
1029 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
1030 AND (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
1031 AND SUBREG.USER_ID = UTREG.USER_ID
1032 AND UTREG.STATUS_CODE <> 'PENDING'
1033 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
1034 AND SUBREG.APPROVER_USER_ID = p_approver_user_id
1035 ) owner_requests where rownum < l_rownum order by USER_NAME;
1036
1037
1038 CURSOR APPR_REQ_ENTITY_SOURCE IS
1039
1040 SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
1041 ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE FROM (
1042 SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
1043 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
1044 PARTY.PARTY_ID PARTY_ID, 'USERTYPE' ENTITY_SOURCE,
1045 UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
1046 FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
1047 JTF_UM_USERTYPE_REG UTREG, FND_USER FU
1048 WHERE UTREG.STATUS_CODE = 'PENDING'
1049 AND UTREG.USERTYPE_ID = UT.USERTYPE_ID
1050 AND UT.APPROVAL_ID = AP.APPROVAL_ID
1051 AND AP.USE_PENDING_REQ_FLAG = 'Y'
1052 AND UTREG.USER_ID = FU.USER_ID
1053 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
1054 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
1055 AND UTREG.APPROVER_USER_ID = p_approver_user_id
1056
1057 UNION ALL
1058
1059 SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
1060 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
1061 PARTY.PARTY_ID PARTY_ID, 'ENROLLMENT' ENTITY_SOURCE,
1062 SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
1063 FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
1064 JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG
1065 WHERE SUBREG.STATUS_CODE = 'PENDING'
1066 AND SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
1067 AND SUB.APPROVAL_ID = AP.APPROVAL_ID
1068 AND AP.USE_PENDING_REQ_FLAG = 'Y'
1069 AND SUBREG.USER_ID = FU.USER_ID
1070 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
1071 AND (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
1072 AND SUBREG.USER_ID = UTREG.USER_ID
1073 AND UTREG.STATUS_CODE <> 'PENDING'
1074 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
1075 AND SUBREG.APPROVER_USER_ID = p_approver_user_id
1076 ) owner_requests where rownum < l_rownum order by ENTITY_SOURCE;
1077
1078
1079 CURSOR APPR_REQ_ENTITY_NAME IS
1080
1081 SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
1082 ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE FROM (
1083 SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
1084 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
1085 PARTY.PARTY_ID PARTY_ID, 'USERTYPE' ENTITY_SOURCE,
1086 UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
1087 FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
1088 JTF_UM_USERTYPE_REG UTREG, FND_USER FU
1089 WHERE UTREG.STATUS_CODE = 'PENDING'
1090 AND UTREG.USERTYPE_ID = UT.USERTYPE_ID
1091 AND UT.APPROVAL_ID = AP.APPROVAL_ID
1092 AND AP.USE_PENDING_REQ_FLAG = 'Y'
1093 AND UTREG.USER_ID = FU.USER_ID
1094 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
1095 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
1096 AND UTREG.APPROVER_USER_ID = p_approver_user_id
1097
1098 UNION ALL
1099
1100 SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
1101 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
1102 PARTY.PARTY_ID PARTY_ID, 'ENROLLMENT' ENTITY_SOURCE,
1103 SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
1104 FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
1105 JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG
1106 WHERE SUBREG.STATUS_CODE = 'PENDING'
1107 AND SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
1108 AND SUB.APPROVAL_ID = AP.APPROVAL_ID
1109 AND AP.USE_PENDING_REQ_FLAG = 'Y'
1110 AND SUBREG.USER_ID = FU.USER_ID
1111 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
1112 AND (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
1113 AND SUBREG.USER_ID = UTREG.USER_ID
1114 AND UTREG.STATUS_CODE <> 'PENDING'
1115 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
1116 AND SUBREG.APPROVER_USER_ID = p_approver_user_id
1117 ) owner_requests where rownum < l_rownum order by ENTITY_NAME;
1118
1119 CURSOR APPR_REQ_ENTITY_NUMBER IS
1120
1121 SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
1122 ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE FROM (
1123 SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
1124 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
1125 PARTY.PARTY_ID PARTY_ID, 'USERTYPE' ENTITY_SOURCE,
1126 UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
1127 FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
1128 JTF_UM_USERTYPE_REG UTREG, FND_USER FU
1129 WHERE UTREG.STATUS_CODE = 'PENDING'
1130 AND UTREG.USERTYPE_ID = UT.USERTYPE_ID
1131 AND UT.APPROVAL_ID = AP.APPROVAL_ID
1132 AND AP.USE_PENDING_REQ_FLAG = 'Y'
1133 AND UTREG.USER_ID = FU.USER_ID
1134 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
1135 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
1136 AND UTREG.APPROVER_USER_ID = p_approver_user_id
1137
1138 UNION ALL
1139
1140 SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
1141 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
1142 PARTY.PARTY_ID PARTY_ID, 'ENROLLMENT' ENTITY_SOURCE,
1143 SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
1144 FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
1145 JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG
1146 WHERE SUBREG.STATUS_CODE = 'PENDING'
1147 AND SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
1148 AND SUB.APPROVAL_ID = AP.APPROVAL_ID
1149 AND AP.USE_PENDING_REQ_FLAG = 'Y'
1150 AND SUBREG.USER_ID = FU.USER_ID
1151 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
1152 AND (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
1153 AND SUBREG.USER_ID = UTREG.USER_ID
1154 AND UTREG.STATUS_CODE <> 'PENDING'
1155 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
1156 AND SUBREG.APPROVER_USER_ID = p_approver_user_id
1157 ) owner_requests where rownum < l_rownum order by REG_ID;
1158
1159 l_party_id HZ_PARTIES.PARTY_ID%TYPE;
1160
1161 CURSOR GET_COMPANY_NAME IS
1162
1163 SELECT PARTY.PARTY_NAME
1164 FROM HZ_PARTIES PARTY, HZ_RELATIONSHIPS PREL
1165 WHERE PARTY.PARTY_ID = PREL.OBJECT_ID
1166 AND PREL.PARTY_ID = l_party_id
1167 AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1168 AND PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1169 AND PREL.START_DATE < SYSDATE
1170 AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
1171 AND PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF');
1172
1173
1174 l_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
1175 i NUMBER := 1;
1176
1177 BEGIN
1178
1179 IF p_number_of_records IS NOT NULL AND p_number_of_records <> 0 THEN
1180 l_rownum := p_number_of_records;
1181 END IF;
1182
1183 IF UPPER(p_sort_order) = 'USER_NAME' THEN
1184 FOR j in APPR_REQ_USER_NAME LOOP
1185 x_result(i).USER_NAME := j.USER_NAME;
1186 x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;
1187 x_result(i).ENTITY_SOURCE := j.ENTITY_SOURCE;
1188 x_result(i).ENTITY_NAME := j.ENTITY_NAME;
1189 x_result(i).WF_ITEM_TYPE := j.WF_ITEM_TYPE;
1190 x_result(i).REG_ID := j.REG_ID;
1191 l_party_type := j.PARTY_TYPE;
1192 l_party_id := j.PARTY_ID;
1193
1194 FOR r in GET_COMPANY_NAME LOOP
1195
1196 x_result(i).COMPANY_NAME := r.party_name;
1197
1198 END LOOP;
1199
1200 i := i + 1;
1201
1202 END LOOP;
1203
1204 ELSIF UPPER(p_sort_order) = 'ENTITY_SOURCE' THEN
1205 FOR j in APPR_REQ_ENTITY_SOURCE LOOP
1206 x_result(i).USER_NAME := j.USER_NAME;
1207 x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;
1208 x_result(i).ENTITY_SOURCE := j.ENTITY_SOURCE;
1209 x_result(i).ENTITY_NAME := j.ENTITY_NAME;
1210 x_result(i).WF_ITEM_TYPE := j.WF_ITEM_TYPE;
1211 x_result(i).REG_ID := j.REG_ID;
1212 l_party_type := j.PARTY_TYPE;
1213 l_party_id := j.PARTY_ID;
1214
1215 FOR r in GET_COMPANY_NAME LOOP
1216
1217 x_result(i).COMPANY_NAME := r.party_name;
1218
1219 END LOOP;
1220
1221 i := i + 1;
1222
1223 END LOOP;
1224
1225 ELSIF UPPER(p_sort_order) = 'ENTITY_NAME' THEN
1226 FOR j in APPR_REQ_ENTITY_NAME LOOP
1227 x_result(i).USER_NAME := j.USER_NAME;
1228 x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;
1229 x_result(i).ENTITY_SOURCE := j.ENTITY_SOURCE;
1230 x_result(i).ENTITY_NAME := j.ENTITY_NAME;
1231 x_result(i).WF_ITEM_TYPE := j.WF_ITEM_TYPE;
1232 x_result(i).REG_ID := j.REG_ID;
1233 l_party_type := j.PARTY_TYPE;
1234 l_party_id := j.PARTY_ID;
1235
1236 FOR r in GET_COMPANY_NAME LOOP
1237
1238 x_result(i).COMPANY_NAME := r.party_name;
1239
1240 END LOOP;
1241
1242 i := i + 1;
1243
1244 END LOOP;
1245
1246 ELSIF UPPER(p_sort_order) = 'ENTITY_NUMBER' THEN
1247 FOR j in APPR_REQ_ENTITY_NUMBER LOOP
1248 x_result(i).USER_NAME := j.USER_NAME;
1249 x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;
1250 x_result(i).ENTITY_SOURCE := j.ENTITY_SOURCE;
1251 x_result(i).ENTITY_NAME := j.ENTITY_NAME;
1252 x_result(i).WF_ITEM_TYPE := j.WF_ITEM_TYPE;
1253 x_result(i).REG_ID := j.REG_ID;
1254 l_party_type := j.PARTY_TYPE;
1255 l_party_id := j.PARTY_ID;
1256
1257 FOR r in GET_COMPANY_NAME LOOP
1258
1259 x_result(i).COMPANY_NAME := r.party_name;
1260
1261 END LOOP;
1262
1263 i := i + 1;
1264
1265 END LOOP;
1266
1267 ELSE
1268
1269 FOR j in APPR_REQ_LAST_UPDATE_DATE LOOP
1270 x_result(i).USER_NAME := j.USER_NAME;
1271 x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;
1272 x_result(i).ENTITY_SOURCE := j.ENTITY_SOURCE;
1273 x_result(i).ENTITY_NAME := j.ENTITY_NAME;
1274 x_result(i).WF_ITEM_TYPE := j.WF_ITEM_TYPE;
1275 x_result(i).REG_ID := j.REG_ID;
1276 l_party_type := j.PARTY_TYPE;
1277 l_party_id := j.PARTY_ID;
1278
1279 FOR r in GET_COMPANY_NAME LOOP
1280
1281 x_result(i).COMPANY_NAME := r.party_name;
1282
1283 END LOOP;
1284
1285 i := i + 1;
1286
1287 END LOOP;
1288
1289 END IF;
1290
1291 END PENDING_APPROVAL_OWNER;
1292
1293 end JTF_UM_APPROVAL_REQUESTS_PVT;