4 -- Placing the cursor outside the function will
1 package body FND_CONC_REQUEST_PKG as
2 /* $Header: AFCPFCRB.pls 120.7.12020000.2 2012/07/16 21:27:51 jtoruno ship $ */
3
5 -- prevent reparsing on each call. (I think.)
6
7 /* Globals */
8 FNDCPQCR_ACTIVE boolean default FALSE;
9 FNDCPQCR_SYS_MODE boolean default FALSE;
10 FNDCPQCR_RESP_ACCESS boolean default FALSE;
11 FNDCPQCR_USER_ID number default NULL;
12 FNDCPQCR_RESP_APPL_ID number default NULL;
13 FNDCPQCR_RESP_ID number default NULL;
14
15
16 PHASE_LOOKUP_TYPE CONSTANT VARCHAR2(16) := 'CP_PHASE_CODE';
17 STATUS_LOOKUP_TYPE CONSTANT VARCHAR2(16) := 'CP_STATUS_CODE';
18
19 /* Will placing the cursor outside of the function keep it from
20 * reparsing with each call? I hope so.
21 */
22 cursor attribute_numbers(appl_id number, prog_name varchar2) is
23 select to_number(substr(application_column_name, 10)) num
24 from fnd_descr_flex_column_usages
25 where application_id = appl_id
26 and descriptive_flexfield_name = '$SRS$.'||prog_name
27 and descriptive_flex_context_code = 'Global Data Elements'
28 and enabled_flag = 'Y'
29 order by column_seq_num;
30
31
32 procedure get_program_info (cpid in number,
33 appid in number,
34 pname out nocopy varchar2,
35 sname out nocopy varchar2,
36 srs in out nocopy varchar2,
37 eflag in out nocopy varchar2,
38 rflag in out nocopy varchar2,
39 qcode in out nocopy varchar2,
40 eopts in out nocopy varchar2,
41 prntr in out nocopy varchar2,
42 pstyl in out nocopy varchar2,
43 rstyl in out nocopy varchar2) is
44
45 begin
46 select user_concurrent_program_name,
47 concurrent_program_name,
48 srs_flag,
49 enabled_flag,
50 run_alone_flag,
51 queue_method_code,
52 execution_options,
53 printer_name,
54 output_print_style,
55 required_style
56 into pname,
57 sname,
58 srs,
59 eflag,
60 rflag,
61 qcode,
62 eopts,
63 prntr,
64 pstyl,
65 rstyl
66 from fnd_concurrent_programs_vl
67 where concurrent_program_id = cpid
68 and application_id = appid;
69
70 exception
71 when no_data_found then
72 pname := NULL;
73 eflag := 'N';
74 end get_program_info;
75
76
77 function get_user_name (uid in number)
78 return varchar2 is
79
80 uname fnd_user.user_name%TYPE;
81
82 begin
83 if (uid is NULL) then
84 return (NULL);
85 end if;
86
87 select user_name
88 into uname
89 from fnd_user
90 where user_id = uid;
91
92 return (uname);
93
94 exception
95 when no_data_found then
96 return (NULL);
97 end get_user_name;
98
99
100 function get_user_print_style (pstyl in varchar2)
101 return varchar2 is
102
103 ustyl fnd_printer_styles_vl.user_printer_style_name%TYPE;
104
105 begin
106 if (pstyl is NULL) then
107 return (NULL);
108 end if;
109
110 select user_printer_style_name
111 into ustyl
112 from fnd_printer_styles_vl
113 where printer_style_name = pstyl;
114
115 return (ustyl);
116
117 exception
118 when no_data_found then
119 return (NULL);
120 end get_user_print_style;
121
122
123 procedure get_phase_status (pcode in char,
124 scode in char,
125 hold in char,
126 enbld in char,
127 cancel in char,
128 stdate in date,
129 rid in number,
130 phase out nocopy varchar2,
131 status out nocopy varchar2) is
132 upcode char;
133 uscode char;
134 begin
135 get_phase_status(pcode, scode, hold, enbld, cancel, stdate,
136 rid, phase, status, upcode, uscode);
137
138 end get_phase_status;
139
140
141
142 procedure get_phase_status (pcode in char,
143 scode in char,
144 hold in char,
145 enbld in char,
146 cancel in char,
147 stdate in date,
148 rid in number,
149 phase out nocopy varchar2,
150 status out nocopy varchar2,
151 upcode out nocopy varchar2,
152 uscode out nocopy varchar2) is
153
154 edname varchar2(30);
155 default_edition varchar2(30);
156
157 begin
158 if (pcode is NULL) then
159 phase := NULL;
160 return;
161 end if;
162
163 upcode := pcode;
164 uscode := scode;
165
166 if ((pcode = 'P') and (hold = 'Y')) then
167 upcode := 'I';
168 uscode := 'H';
169 elsif ((pcode = 'P') and (enbld = 'N')) then
170 upcode := 'I';
171 uscode := 'U';
172 elsif ((pcode = 'P') and (scode = 'A')) then
173 upcode := 'P';
174 uscode := 'A';
175 elsif (pcode = 'P') then
176 if ((scode = 'P') or (stdate > sysdate)) then
177 upcode := 'P';
178 uscode := 'P';
179 else
180 -- If request not submitted from the RUN edition, show as Inactive/No Manager
181 -- default_edition should be the edition the managers are running on
182 select property_value
183 into default_edition
184 from database_properties
188 into edname
185 where property_name = 'DEFAULT_EDITION';
186
187 select edition_name
189 from fnd_concurrent_requests
190 where request_id = rid;
191
192 -- If the request's edition does not match the manager's it will not run
193 if edname <> default_edition then
194 upcode := 'I';
195 uscode := 'M';
196
197 else
198
199 select 'I',
200 'M'
201 into upcode,
202 uscode
203 from sys.dual
204 where not exists (select null
205 from fnd_concurrent_worker_requests
206 where request_id = rid
207 and running_processes > 0
208 and (not (queue_application_id = 0
209 and concurrent_queue_id in (1,4))
210 or queue_control_flag = 'Y'));
211
212
213 end if;
214
215 end if;
216 end if;
217
218 raise no_data_found;
219
220 exception
221
222 when no_data_found then
223 /*changed query for BUG#5007915 SQLID#14602738 */
224 select ph.meaning into phase
225 from fnd_lookups ph
226 where ph.lookup_type = PHASE_LOOKUP_TYPE
227 and ph.lookup_code = upcode;
228
229 select st.meaning into status
230 from fnd_lookups st
231 where st.lookup_type = STATUS_LOOKUP_TYPE
232 and st.lookup_code = uscode;
233 end get_phase_status;
234
235
236 function get_user_phase_code (phase in varchar2)
237 return varchar2 is
238
239 upcode fnd_lookups.lookup_code%TYPE;
240
241 begin
242 if (phase is null) then
243 return (NULL);
244 end if;
245
246 select lookup_code
247 into upcode
248 from fnd_lookups
249 where lookup_type = PHASE_LOOKUP_TYPE
250 and meaning like phase
251 order by lookup_code;
252
253 return (upcode);
254
255 exception
256 when no_data_found then
257 return ('0'); -- A non-null non-phase code
258 end get_user_phase_code;
259
260
261 function get_user_status_code (status in varchar2)
262 return varchar2 is
263
264 uscode fnd_lookups.lookup_code%TYPE;
265
266 begin
267 if (status is null) then
268 return (NULL);
269 end if;
270
271 select lookup_code
272 into uscode
273 from fnd_lookups
274 where lookup_type = STATUS_LOOKUP_TYPE
275 and meaning like status
276 order by lookup_code;
277
278 return (uscode);
279
280 exception
281 when no_data_found then
282 return ('0'); -- A non-null non-status code
283 end get_user_status_code;
284
285
286 function lock_parent (rid in number)
287 return boolean is
288
289 dummy char;
290
291 begin
292 select status_code
293 into dummy
294 from fnd_concurrent_requests
295 where request_id = rid
296 and has_sub_request = 'Y'
297 and status_code = 'W'
298 for update of status_code;
299
300 return (TRUE);
301
302 exception
303 when no_data_found then
304 return (FALSE);
305 end lock_parent;
306
307
308 function restart_parent (rid in number,
309 prid in number,
310 uid in number)
311 return boolean is
312
313 begin
314 update fnd_concurrent_requests
315 set status_code = 'I',
316 last_update_date = sysdate,
317 last_updated_by = uid
318 where request_id = prid
319 and has_sub_request = 'Y'
320 and status_code = 'W'
321 and not exists (select null
322 from fnd_concurrent_requests
323 where parent_request_id = prid
324 and request_id <> rid
325 and is_sub_request = 'Y'
326 and status_code between 'I' and 'T');
327
328 return (not (SQL%NOTFOUND));
329 end restart_parent;
330
331
332 procedure delete_children (rid in number,
333 uid in number) is
334
335 begin
336 update fnd_concurrent_requests
337 set phase_code = decode (status_code,
338 'R', 'R',
339 'C'),
340 status_code = decode (phase_code,
341 'R', decode (status_code,
342 'R', 'T',
343 'X'),
344 'D'),
345 last_update_date = sysdate,
346 last_updated_by = uid
347 where request_id in (select request_id
348 from fnd_concurrent_requests
349 where phase_code <> 'C' and status_code <> 'T'
350 connect by prior request_id = parent_request_id
351 start with request_id = rid);
352 end delete_children;
353
354
355 function request_position (rid in number,
356 pri in number,
357 stdate in date,
358 qname in varchar2,
359 qappid in number)
360 return number is
361
362 reqpos number;
363
364 begin
365 select count (*) + 1
366 into reqpos
367 from fnd_concurrent_worker_requests b
368 where (b.priority < pri
369 or (b.priority = pri
370 and b.requested_start_date < stdate)
371 or (b.priority = pri
372 and b.requested_start_date = stdate)
373 and b.request_id <= rid)
374 and b.concurrent_queue_name = qname
375 and b.queue_application_id = qappid
376 and b.phase_code = 'P'
377 and b.hold_flag <> 'Y'
378 and b.requested_start_date <= sysdate;
379
380 return (reqpos);
381
382 exception
386 end request_position;
383 when no_data_found then
384 return (NULL);
385
387
388
389 function running_requests (qname in varchar2,
390 qappid in number)
391 return number is
392
393 runreqs number;
394
395 begin
396 select count (*)
397 into runreqs
398 from fnd_concurrent_worker_requests
399 where concurrent_queue_name = qname
400 and queue_application_id = qappid
401 and phase_code = 'R';
402
403 return (runreqs);
404
405 exception
406 when no_data_found then
407 return (NULL);
408
409 end running_requests;
410
411
412 function pending_requests (qname in varchar2,
413 qappid in number)
414 return number is
415
416 pendreqs number;
417
418 begin
419 select count (*)
420 into pendreqs
421 from fnd_concurrent_worker_requests
422 where concurrent_queue_name = qname
423 and queue_application_id = qappid
424 and phase_code = 'P'
425 and hold_flag <> 'Y'
426 and requested_start_date <= sysdate;
427
428 return (pendreqs);
429
430 exception
431 when no_data_found then
432 return (NULL);
433
434 end pending_requests;
435
436
437 function encode_attribute_order (srs_flag in varchar2,
438 requested_by in number,
439 req_resp_id in number,
440 req_resp_appl_id in number,
441 prog_appl_id in number,
442 prog_name in varchar2)
443 return varchar2 is
444 encoded_string varchar2(100);
445
446 begin
447 /* Are we being called from within a QCR session? */
448 if ((not FNDCPQCR_ACTIVE) or (srs_flag = 'N')) then
449 return Null;
450 end if;
451
452 /* Do we (possibly) have access to the report? */
453 if ((not FNDCPQCR_SYS_MODE)
454 and (requested_by <> FNDCPQCR_USER_ID)
455 and (not FNDCPQCR_RESP_ACCESS)) then
456 return Null;
460 /* 12821152 Allow access to System Admin and user-owned request. */
457 end if;
458
459 /* The resp must match for the flex to be active. */
461 if (((req_resp_id <> FNDCPQCR_RESP_ID) or
462 (req_resp_appl_id <> FNDCPQCR_RESP_APPL_ID)) and
463 ((not FNDCPQCR_SYS_MODE) and (not (requested_by = FNDCPQCR_USER_ID)))) then
464 return Null;
465 end if;
466
467 encoded_string := NULL;
468
469 for rec in attribute_numbers(prog_appl_id, prog_name) loop
470 encoded_string := encoded_string || fnd_global.local_chr(rec.num);
471 end loop;
472
473 return encoded_string;
474
475 exception
476 when others then
477 return NULL;
478 end;
479
480
481 procedure fndcpqcr_init(sys_mode boolean, resp_access boolean) is
482 begin
483 FNDCPQCR_ACTIVE := TRUE;
484 FNDCPQCR_SYS_MODE := sys_mode;
485 FNDCPQCR_RESP_ACCESS := resp_access;
486 FNDCPQCR_USER_ID := fnd_global.user_id;
487 FNDCPQCR_RESP_APPL_ID := fnd_global.resp_appl_id;
488 FNDCPQCR_RESP_ID := fnd_global.resp_id;
489 end;
490
491 function role_info( in_name in varchar2,
492 in_system in varchar2,
493 in_system_id in number)
494 return varchar2 is
495 disp_name varchar2(80);
496 begin
497 -- get the display_name from wf_roles with constants in
498 -- in where condition.
499 select display_name
500 into disp_name
501 from wf_roles
502 where orig_system_id = in_system_id
503 and orig_system = in_system
504 and name = in_name;
505
506 return disp_name;
507
508 exception
509 when no_data_found then
510 disp_name := null;
511 return disp_name;
512 when others then
513 disp_name := null;
514 return disp_name;
515 end;
516
517 end FND_CONC_REQUEST_PKG;
[Home] [Help]