1 package body FND_CONC_REQUEST_PKG as
2 /* $Header: AFCPFCRB.pls 120.4 2006/03/14 15:15:05 rckalyan ship $ */
3
4 -- Placing the cursor outside the function will
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 if (pcode is NULL) then
136 phase := NULL;
137 return;
138 end if;
139
140 upcode := pcode;
141 uscode := scode;
142
143 if ((pcode = 'P') and (hold = 'Y')) then
144 upcode := 'I';
145 uscode := 'H';
146 elsif ((pcode = 'P') and (enbld = 'N')) then
147 upcode := 'I';
148 uscode := 'U';
149 elsif ((pcode = 'P') and (scode = 'A')) then
150 upcode := 'P';
151 uscode := 'A';
152 elsif (pcode = 'P') then
153 if ((scode = 'P') or (stdate > sysdate)) then
154 upcode := 'P';
155 uscode := 'P';
156 else
157 select 'I',
158 'M'
159 into upcode,
160 uscode
161 from sys.dual
162 where not exists (select null
163 from fnd_concurrent_worker_requests
164 where request_id = rid
165 and running_processes > 0
166 and (not (queue_application_id = 0
167 and concurrent_queue_id in (1,4))
168 or queue_control_flag = 'Y'));
169 end if;
170 end if;
171
172 raise no_data_found;
173
174 exception
175 when no_data_found then
176 /*changed query for BUG#5007915 SQLID#14602738 */
177 select ph.meaning into phase
178 from fnd_lookups ph
179 where ph.lookup_type = PHASE_LOOKUP_TYPE
180 and ph.lookup_code = upcode;
181
182 select st.meaning into status
183 from fnd_lookups st
184 where st.lookup_type = STATUS_LOOKUP_TYPE
185 and st.lookup_code = uscode;
186 end get_phase_status;
187
188
189 function get_user_phase_code (phase in varchar2)
190 return varchar2 is
191
192 upcode fnd_lookups.lookup_code%TYPE;
193
194 begin
195 if (phase is null) then
196 return (NULL);
197 end if;
198
199 select lookup_code
200 into upcode
201 from fnd_lookups
202 where lookup_type = PHASE_LOOKUP_TYPE
203 and meaning like phase
204 order by lookup_code;
205
206 return (upcode);
207
208 exception
209 when no_data_found then
210 return ('0'); -- A non-null non-phase code
211 end get_user_phase_code;
212
213
214 function get_user_status_code (status in varchar2)
215 return varchar2 is
216
217 uscode fnd_lookups.lookup_code%TYPE;
218
219 begin
220 if (status is null) then
221 return (NULL);
222 end if;
223
224 select lookup_code
225 into uscode
226 from fnd_lookups
227 where lookup_type = STATUS_LOOKUP_TYPE
228 and meaning like status
229 order by lookup_code;
230
231 return (uscode);
232
233 exception
234 when no_data_found then
235 return ('0'); -- A non-null non-status code
236 end get_user_status_code;
237
238
239 function lock_parent (rid in number)
240 return boolean is
241
242 dummy char;
243
244 begin
245 select status_code
246 into dummy
247 from fnd_concurrent_requests
248 where request_id = rid
249 and has_sub_request = 'Y'
250 and status_code = 'W'
251 for update of status_code;
252
253 return (TRUE);
254
255 exception
256 when no_data_found then
257 return (FALSE);
258 end lock_parent;
259
260
261 function restart_parent (rid in number,
262 prid in number,
263 uid in number)
264 return boolean is
265
266 begin
267 update fnd_concurrent_requests
268 set status_code = 'I',
269 last_update_date = sysdate,
270 last_updated_by = uid
271 where request_id = prid
272 and has_sub_request = 'Y'
273 and status_code = 'W'
274 and not exists (select null
275 from fnd_concurrent_requests
276 where parent_request_id = prid
277 and request_id <> rid
278 and is_sub_request = 'Y'
279 and status_code between 'I' and 'T');
280
281 return (not (SQL%NOTFOUND));
282 end restart_parent;
283
284
285 procedure delete_children (rid in number,
286 uid in number) is
287
288 begin
289 update fnd_concurrent_requests
290 set phase_code = decode (status_code,
291 'R', 'R',
292 'C'),
293 status_code = decode (phase_code,
294 'R', decode (status_code,
295 'R', 'T',
296 'X'),
297 'D'),
298 last_update_date = sysdate,
299 last_updated_by = uid
300 where request_id in (select request_id
301 from fnd_concurrent_requests
302 where phase_code <> 'C' and status_code <> 'T'
303 connect by prior request_id = parent_request_id
304 start with request_id = rid);
305 end delete_children;
306
307
308 function request_position (rid in number,
309 pri in number,
310 stdate in date,
311 qname in varchar2,
312 qappid in number)
313 return number is
314
315 reqpos number;
316
317 begin
318 select count (*) + 1
319 into reqpos
320 from fnd_concurrent_worker_requests b
321 where (b.priority < pri
322 or (b.priority = pri
323 and b.requested_start_date < stdate)
324 or (b.priority = pri
325 and b.requested_start_date = stdate)
326 and b.request_id <= rid)
327 and b.concurrent_queue_name = qname
328 and b.queue_application_id = qappid
329 and b.phase_code = 'P'
330 and b.hold_flag <> 'Y'
331 and b.requested_start_date <= sysdate;
332
333 return (reqpos);
334
335 exception
336 when no_data_found then
337 return (NULL);
338
339 end request_position;
340
341
342 function running_requests (qname in varchar2,
343 qappid in number)
344 return number is
345
346 runreqs number;
347
348 begin
349 select count (*)
350 into runreqs
351 from fnd_concurrent_worker_requests
352 where concurrent_queue_name = qname
353 and queue_application_id = qappid
354 and phase_code = 'R';
355
356 return (runreqs);
357
358 exception
359 when no_data_found then
360 return (NULL);
361
362 end running_requests;
363
364
365 function pending_requests (qname in varchar2,
366 qappid in number)
367 return number is
368
369 pendreqs number;
370
371 begin
372 select count (*)
373 into pendreqs
374 from fnd_concurrent_worker_requests
375 where concurrent_queue_name = qname
376 and queue_application_id = qappid
377 and phase_code = 'P'
378 and hold_flag <> 'Y'
379 and requested_start_date <= sysdate;
380
381 return (pendreqs);
382
383 exception
384 when no_data_found then
385 return (NULL);
386
387 end pending_requests;
388
389
390 function encode_attribute_order (srs_flag in varchar2,
391 requested_by in number,
392 req_resp_id in number,
393 req_resp_appl_id in number,
394 prog_appl_id in number,
395 prog_name in varchar2)
396 return varchar2 is
397 attribute number;
398 encoded_string varchar2(100);
399
400 begin
401 /* Are we being called from within a QCR session? */
402 if ((not FNDCPQCR_ACTIVE) or (srs_flag = 'N')) then
403 return Null;
404 end if;
405
406 /* Do we (possibly) have access to the report? */
407 if ((not FNDCPQCR_SYS_MODE)
408 and (requested_by <> FNDCPQCR_USER_ID)
409 and (not FNDCPQCR_RESP_ACCESS)) then
410 return Null;
411 end if;
412
413 /* The resp must match for the flex to be active. */
414 if ((req_resp_id <> FNDCPQCR_RESP_ID) or
415 (req_resp_appl_id <> FNDCPQCR_RESP_APPL_ID)) then
416 return Null;
417 end if;
418
419 encoded_string := NULL;
420
421 for rec in attribute_numbers(prog_appl_id, prog_name) loop
422 encoded_string := encoded_string || fnd_global.local_chr(rec.num);
423 end loop;
424
425 return encoded_string;
426
427 exception
428 when others then
429 return NULL;
430 end;
431
432
433 procedure fndcpqcr_init(sys_mode boolean, resp_access boolean) is
434 begin
435 FNDCPQCR_ACTIVE := TRUE;
436 FNDCPQCR_SYS_MODE := sys_mode;
437 FNDCPQCR_RESP_ACCESS := resp_access;
438 FNDCPQCR_USER_ID := fnd_global.user_id;
439 FNDCPQCR_RESP_APPL_ID := fnd_global.resp_appl_id;
440 FNDCPQCR_RESP_ID := fnd_global.resp_id;
441 end;
442
443 function role_info( in_name in varchar2,
444 in_system in varchar2,
445 in_system_id in number)
446 return varchar2 is
447 disp_name varchar2(80);
448 begin
449 -- get the display_name from wf_roles with constants in
450 -- in where condition.
451 select display_name
452 into disp_name
453 from wf_roles
454 where orig_system_id = in_system_id
455 and orig_system = in_system
456 and name = in_name;
457
458 return disp_name;
459
460 exception
461 when no_data_found then
462 disp_name := null;
463 return disp_name;
464 when others then
465 disp_name := null;
466 return disp_name;
467 end;
468
469 end FND_CONC_REQUEST_PKG;
[Home] [Help]