1 PACKAGE BODY JTF_UM_APPROVAL_REQUESTS_PVT as
2 /* $Header: JTFVAPRB.pls 120.11 2009/11/24 15:36:25 dbowles ship $ */
3 TYPE APPR_REQ_CUR is REF CURSOR;
4 /**
5 * Procedure : PENDING_APPROVAL_SYSADMIN
6 * Type : Private
7 * Pre_reqs : None
8 * Description : Return the pending requests foy sysadmin
9 * Parameters :
10 * input parameters
11 * @param p_sort_order
12 * description: The sort order
13 * required : Y
14 * validation : Must be a valid sort order
15 * p_number_of_records:
16 * description: The number of records to retrieve from a database
17 * required : Y
18 * validation : Must be a valid number
19 * output parameters
20 * x_result: APPROVAL_REQUEST_TABLE_TYPE
21 */
22 procedure PENDING_APPROVAL_SYSADMIN(
23 p_sort_order in varchar2,
24 p_number_of_records in number,
25 x_result out NOCOPY APPROVAL_REQUEST_TABLE_TYPE) IS
26 l_rownum number := 10; -- Default value
27 APPR_REQ APPR_REQ_CUR;
28 qry varchar2(4000) :=
29 'SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
30 ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE, APPROVER, ERROR_ACTIVITY
31 FROM (
32 SELECT sys_requests.REG_ID, sys_requests.REG_LAST_UPDATE_DATE, sys_requests.USER_NAME,
33 sys_requests.PARTY_TYPE, sys_requests.PARTY_ID, sys_requests.ENTITY_SOURCE,
34 sys_requests.ENTITY_NAME, sys_requests.WF_ITEM_TYPE, sys_requests.APPROVER,
35 JTF_UM_APPROVAL_REQUESTS_PVT.getWorkflowActivityStatus(WF_ITEM_TYPE,REG_ID) ERROR_ACTIVITY
36 FROM (
37 SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
38 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
39 ''USERTYPE'' ENTITY_SOURCE, UT.USERTYPE_SHORTNAME ENTITY_NAME,
40 UTREG.WF_ITEM_TYPE WF_ITEM_TYPE, FU2.USER_NAME APPROVER
41 FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B APPR, HZ_PARTIES PARTY,
42 JTF_UM_USERTYPE_REG UTREG, FND_USER FU, FND_USER FU2
43 WHERE UTREG.STATUS_CODE in (''PENDING'', ''UPGRADE_APPROVAL_PENDING'')
44 AND UTREG.USERTYPE_ID = UT.USERTYPE_ID
45 AND UT.APPROVAL_ID = APPR.APPROVAL_ID
46 AND APPR.USE_PENDING_REQ_FLAG = ''Y''
47 AND UTREG.USER_ID = FU.USER_ID
48 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
49 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
50 AND FU2.USER_ID (+) = UTREG.APPROVER_USER_ID
51 UNION ALL
52 SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
53 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
54 ''ENROLLMENT'' ENTITY_SOURCE, SUB.SUBSCRIPTION_NAME ENTITY_NAME,
55 SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE, FU2.USER_NAME APPROVER
56 FROM JTF_UM_SUBSCRIPTIONS_VL SUB,
57 JTF_UM_APPROVALS_B APPR,
58 HZ_PARTIES PARTY,
59 JTF_UM_SUBSCRIPTION_REG SUBREG,
60 FND_USER FU,
61 JTF_UM_USERTYPE_REG UTREG,
62 FND_USER FU2
63 WHERE SUBREG.STATUS_CODE in (''PENDING'', ''UPGRADE_APPROVAL_PENDING'')
64 AND SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
65 AND SUB.APPROVAL_ID = APPR.APPROVAL_ID
66 AND APPR.USE_PENDING_REQ_FLAG = ''Y''
67 AND SUBREG.USER_ID = FU.USER_ID
68 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
69 AND (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
70 AND SUBREG.USER_ID = UTREG.USER_ID
71 AND UTREG.STATUS_CODE not in (''PENDING'', ''UPGRADE_APPROVAL_PENDING'')
72 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
73 AND FU2.USER_ID (+) = SUBREG.APPROVER_USER_ID
74 ) sys_requests ';
75 l_party_id HZ_PARTIES.PARTY_ID%TYPE;
76 CURSOR GET_COMPANY_NAME IS
77 SELECT PARTY.PARTY_NAME FROM HZ_PARTIES PARTY, HZ_RELATIONSHIPS PREL
78 WHERE PARTY.PARTY_ID = PREL.OBJECT_ID
79 AND PREL.PARTY_ID = l_party_id
80 AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
81 AND PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
82 AND PREL.START_DATE < SYSDATE
83 AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
84 AND PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
85 ORDER BY PREL.START_DATE;
86 l_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
87 i NUMBER := 1;
88 BEGIN
89 IF p_number_of_records IS NOT NULL AND p_number_of_records <> 0 THEN
90 l_rownum := p_number_of_records;
91 END IF;
92 IF UPPER(p_sort_order) = 'USER_NAME' THEN
93 qry := qry||' order by sys_requests.USER_NAME ) all_requests where ERROR_ACTIVITY in (0,-1,-2) and rownum < :1';
94 ELSIF UPPER(p_sort_order) = 'ENTITY_SOURCE' THEN
95 qry := qry||' order by sys_requests.ENTITY_SOURCE ) all_requests where ERROR_ACTIVITY in (0,-1,-2) and rownum < :1';
96 ELSIF UPPER(p_sort_order) = 'ENTITY_NAME' THEN
97 qry := qry||' order by sys_requests.ENTITY_NAME ) all_requests where ERROR_ACTIVITY in (0,-1,-2) and rownum < :1';
98 ELSIF UPPER(p_sort_order) = 'ENTITY_NUMBER' THEN
99 qry := qry||' order by ERROR_ACTIVITY , sys_requests.REG_ID ) all_requests where ERROR_ACTIVITY in (0,-1,-2) and rownum < :1';
100 ELSIF UPPER(p_sort_order) = 'APPROVER' THEN
101 qry := qry||' order by sys_requests.APPROVER ) all_requests where ERROR_ACTIVITY in (0,-1,-2) and rownum < :1';
102 ELSE
103 qry := qry||' order by REG_LAST_UPDATE_DATE ) all_requests where ERROR_ACTIVITY in (0,-1,-2) and rownum < :1';
104 END IF;
105 open APPR_REQ for qry using l_rownum;
106 loop
107 fetch APPR_REQ into x_result(i).REG_ID,
108 x_result(i).REG_LAST_UPDATE_DATE,
109 x_result(i).USER_NAME,
110 l_party_type,
111 l_party_id,
112 x_result(i).ENTITY_SOURCE,
113 x_result(i).ENTITY_NAME,
114 x_result(i).WF_ITEM_TYPE,
115 x_result(i).APPROVER,
116 x_result(i).ERROR_ACTIVITY;
117 FOR r in GET_COMPANY_NAME LOOP
118 x_result(i).COMPANY_NAME := r.party_name;
119 END LOOP;
120 exit when APPR_REQ%NOTFOUND;
121 i := i + 1;
122 end loop;
123 END PENDING_APPROVAL_SYSADMIN;
124 /**
125 * Procedure : PENDING_APPROVAL_PRIMARY
126 * Type : Private
127 * Pre_reqs : None
128 * Description : Return the pending requests foy Primary User
129 * Parameters :
130 * input parameters
131 * @param p_sort_order
132 * description: The sort order
133 * required : Y
134 * validation : Must be a valid sort order
135 * p_number_of_records:
136 * description: The number of records to retrieve from a database
137 * required : Y
138 * validation : Must be a valid number
139 * p_approver_user_id
140 * description: The user_id of a logged in user
141 * required : Y
142 * validation : Must be a valid user_id
143 * output parameters
144 * x_result: APPROVAL_REQUEST_TABLE_TYPE
145 */
146 procedure PENDING_APPROVAL_PRIMARY(
147 p_sort_order in varchar2,
148 p_number_of_records in number,
149 p_approver_user_id in number,
150 x_result out NOCOPY APPROVAL_REQUEST_TABLE_TYPE) IS
151 l_rownum number := 10; -- Default value
152 l_dummy_user_id FND_USER.USER_ID%TYPE;
153 l_company_id HZ_PARTIES.PARTY_ID%TYPE;
154 l_party_name HZ_PARTIES.PARTY_NAME%TYPE;
155 APPR_REQ APPR_REQ_CUR;
156 qry varchar2(4000) := 'SELECT * FROM
157 (SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, ENTITY_SOURCE,
158 ENTITY_NAME, WF_ITEM_TYPE,
159 JTF_UM_APPROVAL_REQUESTS_PVT.getWorkflowActivityStatus(WF_ITEM_TYPE,REG_ID) ERROR_ACTIVITY
160 FROM (
161 SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
162 FU.USER_NAME USER_NAME, ''USERTYPE'' ENTITY_SOURCE,
163 UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
164 FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B APPR,
165 HZ_RELATIONSHIPS PREL, JTF_UM_USERTYPE_REG UTREG, FND_USER FU
166 WHERE UTREG.STATUS_CODE in (''PENDING'', ''UPGRADE_APPROVAL_PENDING'')
167 AND UTREG.USERTYPE_ID = UT.USERTYPE_ID
168 AND UT.APPROVAL_ID = APPR.APPROVAL_ID
169 AND APPR.USE_PENDING_REQ_FLAG = ''Y''
170 AND UTREG.USER_ID = FU.USER_ID
171 AND FU.CUSTOMER_ID = PREL.PARTY_ID
172 AND PREL.OBJECT_ID = :l_company_id
173 AND PREL.SUBJECT_TABLE_NAME = ''HZ_PARTIES''
174 AND PREL.OBJECT_TABLE_NAME = ''HZ_PARTIES''
175 AND PREL.START_DATE < SYSDATE
176 AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
177 AND PREL.RELATIONSHIP_CODE in (''EMPLOYEE_OF'', ''CONTACT_OF'')
178 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
179 AND UTREG.APPROVER_USER_ID = :l_dummy_user_id
180 UNION ALL
181 SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
182 FU.USER_NAME USER_NAME, ''ENROLLMENT'' ENTITY_SOURCE,
183 SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
184 FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B APPR,
185 HZ_RELATIONSHIPS PREL, JTF_UM_SUBSCRIPTION_REG SUBREG,
186 FND_USER FU, JTF_UM_USERTYPE_REG UTREG
187 WHERE SUBREG.STATUS_CODE in (''PENDING'', ''UPGRADE_APPROVAL_PENDING'')
188 AND SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
189 AND SUB.APPROVAL_ID = APPR.APPROVAL_ID
190 AND APPR.USE_PENDING_REQ_FLAG = ''Y''
191 AND SUBREG.USER_ID = FU.USER_ID
192 AND FU.CUSTOMER_ID = PREL.PARTY_ID
193 AND PREL.OBJECT_ID = :l_company_id
194 AND PREL.SUBJECT_TABLE_NAME = ''HZ_PARTIES''
195 AND PREL.OBJECT_TABLE_NAME = ''HZ_PARTIES''
196 AND PREL.RELATIONSHIP_CODE in (''EMPLOYEE_OF'', ''CONTACT_OF'')
197 AND PREL.START_DATE < SYSDATE
198 AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
199 AND (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
200 AND SUBREG.USER_ID = UTREG.USER_ID
201 AND UTREG.STATUS_CODE not in (''PENDING'', ''UPGRADE_APPROVAL_PENDING'')
202 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
203 AND SUBREG.APPROVER_USER_ID = :l_dummy_user_id
204 ) pri_requests ';
205 CURSOR GET_COMPANY_NAME IS
206 SELECT PARTY.PARTY_NAME, PARTY.PARTY_ID
207 FROM HZ_PARTIES PARTY, HZ_RELATIONSHIPS PREL, FND_USER FU
208 WHERE PARTY.PARTY_ID = PREL.OBJECT_ID
209 AND PREL.PARTY_ID = FU.CUSTOMER_ID
210 AND FU.USER_ID = p_approver_user_id
211 AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
212 AND PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
213 AND PREL.START_DATE < SYSDATE
214 AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
215 AND PREL.RELATIONSHIP_CODE = 'EMPLOYEE_OF'
216 ORDER BY PREL.START_DATE DESC;
217 i NUMBER := 1;
218 CURSOR FIND_DUMMY_USER IS
219 SELECT USER_ID
220 FROM FND_USER
221 WHERE USER_NAME = FND_PROFILE.VALUE('JTF_PRIMARY_USER');
222 BEGIN
223 IF p_number_of_records IS NOT NULL AND p_number_of_records <> 0 THEN
224 l_rownum := p_number_of_records;
225 END IF;
226 PENDING_APPROVAL_OWNER(
227 p_sort_order => p_sort_order,
228 p_number_of_records => p_number_of_records,
229 p_approver_user_id => p_approver_user_id,
230 x_result => x_result);
231 OPEN FIND_DUMMY_USER;
232 FETCH FIND_DUMMY_USER INTO l_dummy_user_id;
233 CLOSE FIND_DUMMY_USER;
234 IF l_dummy_user_id IS NOT NULL THEN
235 OPEN GET_COMPANY_NAME;
236 FETCH GET_COMPANY_NAME INTO l_party_name, l_company_id;
237 CLOSE GET_COMPANY_NAME;
238 i := nvl(x_result.LAST, 0) + 1;
239 IF UPPER(p_sort_order) = 'USER_NAME' THEN
240 qry := qry||' order by pri_requests.USER_NAME )all_requests where ERROR_ACTIVITY in (0,-1,-2) and rownum < :1';
241 ELSIF UPPER(p_sort_order) = 'ENTITY_SOURCE' THEN
242 qry := qry||' order by pri_requests.ENTITY_SOURCE )all_requests where ERROR_ACTIVITY in (0,-1,-2) and rownum < :1';
243 ELSIF UPPER(p_sort_order) = 'ENTITY_NAME' THEN
244 qry := qry||' order by pri_requests.ENTITY_NAME )all_requests where ERROR_ACTIVITY in (0,-1,-2) and rownum < :1';
245 ELSIF UPPER(p_sort_order) = 'ENTITY_NUMBER' THEN
246 qry := qry||' order by ERROR_ACTIVITY , pri_requests.REG_ID )all_requests where ERROR_ACTIVITY in (0,-1,-2) and rownum < :1';
247 ELSE
248 qry := qry||' order by REG_LAST_UPDATE_DATE )all_requests where ERROR_ACTIVITY in (0,-1,-2) and rownum < :1';
249 END IF;
250 open APPR_REQ for qry using l_company_id, l_dummy_user_id, l_company_id, l_dummy_user_id, l_rownum;
251 loop
252 fetch APPR_REQ into x_result(i).REG_ID,
253 x_result(i).REG_LAST_UPDATE_DATE,
254 x_result(i).USER_NAME,
255 x_result(i).ENTITY_SOURCE,
256 x_result(i).ENTITY_NAME,
257 x_result(i).WF_ITEM_TYPE,
258 x_result(i).ERROR_ACTIVITY;
259 x_result(i).COMPANY_NAME := l_party_name;
260 exit when APPR_REQ%NOTFOUND;
261 i := i + 1;
262 end loop;
263 END IF;
264 END PENDING_APPROVAL_PRIMARY;
265 /**
266 * Procedure : PENDING_APPROVAL_OWNER
267 * Type : Private
268 * Pre_reqs : None
269 * Description : Return the pending requests for the request owner
270 * Parameters :
271 * input parameters
272 * @param p_sort_order
273 * description: The sort order
274 * required : Y
275 * validation : Must be a valid sort order
276 * p_number_of_records:
277 * description: The number of records to retrieve from a database
278 * required : Y
279 * validation : Must be a valid number
280 * p_approver_user_id
281 * description: The user_id of a logged in user
282 * required : Y
283 * validation : Must be a valid user_id
284 * output parameters
285 * x_result: APPROVAL_REQUEST_TABLE_TYPE
286 */
287 procedure PENDING_APPROVAL_OWNER(
288 p_sort_order in varchar2,
289 p_number_of_records in number,
290 p_approver_user_id in number,
291 x_result out NOCOPY APPROVAL_REQUEST_TABLE_TYPE) IS
292 l_rownum number := 10; -- Default value
293 APPR_REQ APPR_REQ_CUR;
294 qry varchar2(4000) := 'SELECT * FROM
295 (SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
296 ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE,
297 JTF_UM_APPROVAL_REQUESTS_PVT.getWorkflowActivityStatus(WF_ITEM_TYPE, REG_ID) ERROR_ACTIVITY
298 FROM (
299 SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE
300 REG_LAST_UPDATE_DATE, FU.USER_NAME USER_NAME,
301 PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
302 ''USERTYPE'' ENTITY_SOURCE, UT.USERTYPE_SHORTNAME ENTITY_NAME,
303 UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
304 FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B APPR, HZ_PARTIES PARTY,
305 JTF_UM_USERTYPE_REG UTREG, FND_USER FU
306 WHERE UTREG.STATUS_CODE in (''PENDING'', ''UPGRADE_APPROVAL_PENDING'')
307 AND UTREG.USERTYPE_ID = UT.USERTYPE_ID
308 AND UT.APPROVAL_ID = APPR.APPROVAL_ID
309 AND APPR.USE_PENDING_REQ_FLAG = ''Y''
310 AND UTREG.USER_ID = FU.USER_ID
311 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
312 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
313 AND UTREG.APPROVER_USER_ID = :p_approver_user_id
314 UNION ALL
315 SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
316 FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
317 PARTY.PARTY_ID PARTY_ID, ''ENROLLMENT'' ENTITY_SOURCE,
318 SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
319 FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B APPR, HZ_PARTIES PARTY,
320 JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG
321 WHERE SUBREG.STATUS_CODE in (''PENDING'', ''UPGRADE_APPROVAL_PENDING'')
322 AND SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
323 AND SUB.APPROVAL_ID = APPR.APPROVAL_ID
324 AND APPR.USE_PENDING_REQ_FLAG = ''Y''
325 AND SUBREG.USER_ID = FU.USER_ID
326 AND FU.CUSTOMER_ID = PARTY.PARTY_ID
327 AND (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
328 AND SUBREG.USER_ID = UTREG.USER_ID
329 AND UTREG.STATUS_CODE not in (''PENDING'', ''UPGRADE_APPROVAL_PENDING'')
330 AND nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
331 AND SUBREG.APPROVER_USER_ID = :p_approver_user_id
332 ) owner_requests ';
333 l_party_id HZ_PARTIES.PARTY_ID%TYPE;
334 CURSOR GET_COMPANY_NAME IS
335 SELECT PARTY.PARTY_NAME
336 FROM HZ_PARTIES PARTY, HZ_RELATIONSHIPS PREL
337 WHERE PARTY.PARTY_ID = PREL.OBJECT_ID
338 AND PREL.PARTY_ID = l_party_id
339 AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
340 AND PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
341 AND PREL.START_DATE < SYSDATE
342 AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
343 AND PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
344 ORDER BY PREL.START_DATE;
345 l_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
346 i NUMBER := 1;
347 BEGIN
348 IF p_number_of_records IS NOT NULL AND p_number_of_records <> 0 THEN
349 l_rownum := p_number_of_records;
350 END IF;
351 IF UPPER(p_sort_order) = 'USER_NAME' THEN
352 qry := qry||' order by owner_requests.USER_NAME )all_requests where ERROR_ACTIVITY in (0,-1,-2) and rownum < :1';
353 ELSIF UPPER(p_sort_order) = 'ENTITY_SOURCE' THEN
354 qry := qry||' order by owner_requests.ENTITY_SOURCE )all_requests where ERROR_ACTIVITY in (0,-1,-2) and rownum < :1';
355 ELSIF UPPER(p_sort_order) = 'ENTITY_NAME' THEN
356 qry := qry||' order by owner_requests.ENTITY_NAME )all_requests where ERROR_ACTIVITY in (0,-1,-2) and rownum < :1';
357 ELSIF UPPER(p_sort_order) = 'ENTITY_NUMBER' THEN
358 qry := qry||' order by ERROR_ACTIVITY , owner_requests.REG_ID )all_requests where ERROR_ACTIVITY in (0,-1,-2) and rownum < :1';
359 ELSE
360 qry := qry||' order by REG_LAST_UPDATE_DATE)all_requests where ERROR_ACTIVITY in (0,-1,-2) and rownum < :1';
361 END IF;
362 open APPR_REQ for qry using p_approver_user_id, p_approver_user_id, l_rownum;
363 loop
364 fetch APPR_REQ into x_result(i).REG_ID,
365 x_result(i).REG_LAST_UPDATE_DATE,
366 x_result(i).USER_NAME,
367 l_party_type,
368 l_party_id,
369 x_result(i).ENTITY_SOURCE,
370 x_result(i).ENTITY_NAME,
371 x_result(i).WF_ITEM_TYPE,
372 x_result(i).ERROR_ACTIVITY;
373 exit when APPR_REQ%NOTFOUND;
374 FOR r in GET_COMPANY_NAME LOOP
375 x_result(i).COMPANY_NAME := r.party_name;
376 END LOOP;
377 i := i + 1;
378 END LOOP;
379 END PENDING_APPROVAL_OWNER;
380
381
382 /**
383 * Function : getWFActivityStatus
384 * Type : Private
385 * Pre_reqs : None
386 * Description : Return the status of the given WF item
387 * Parameters :
388 * input parameters
389 * @param itemType
390 * description: The WF item type
391 * required : Y
392 * validation : Must be a valid WF item type
393 * itemKey:
394 * description: The WF item key
395 * required : Y
396 * validation : Must be a valid WF item key
397 *
398 * Return Value
399 * x_result: -1 => Errored WF
400 * -2 => Cancelled WF
401 * 0 => Active WF
402 */
403 function getWorkflowActivityStatus(itemType varchar2, itemKey varchar2) return number is
404 ret_val number(1) := 0;
405 status_code varchar2(100);
406 begin
407 begin
408 select x.STATUS_CODE into status_code
409 from (SELECT wf_fwkmon.getitemstatus(WorkflowItemEO.ITEM_TYPE, WorkflowItemEO.ITEM_KEY, WorkflowItemEO.END_DATE, WorkflowItemEO.ROOT_ACTIVITY, WorkflowItemEO.ROOT_ACTIVITY_VERSION) STATUS_CODE
410 FROM WF_ITEMS WorkflowItemEO,
411 WF_ITEM_TYPES_VL WorkflowItemTypeEO,
412 WF_ACTIVITIES_VL ActivityEO
413 WHERE WorkflowItemEO.ITEM_TYPE = WorkflowItemTypeEO.NAME AND
414 ActivityEO.ITEM_TYPE = WorkflowItemEO.ITEM_TYPE AND
415 ActivityEO.NAME = WorkflowItemEO.ROOT_ACTIVITY AND
416 ActivityEO.VERSION = WorkflowItemEO.ROOT_ACTIVITY_VERSION AND
417 WorkflowItemEO.ITEM_TYPE=itemtype AND
418 WorkflowItemEO.ITEM_KEY = itemkey) x
419 WHERE STATUS_CODE IN ('ACTIVE','FORCE','ERROR','COMPLETE_WITH_ERRORS');
420
421 if (status_code='ACTIVE') then
422 ret_val := 0;
423 elsif(status_code='ERROR' or status_code='COMPLETE_WITH_ERRORS') then
424 ret_val := -1;
425 elsif(status_code='FORCE') then
426 ret_val := -2;
427 end if;
428 exception
429 when no_data_found then
430 ret_val := -3;
431 when others then
432 ret_val := -3;
433 end;
434 return ret_val;
435 end;
436 end JTF_UM_APPROVAL_REQUESTS_PVT;
[Home] [Help]