[Home] [Help]
PACKAGE BODY: APPS.FND_REQUEST_SET
Source
1 package body FND_REQUEST_SET as
2 /* $Header: AFRSSUBB.pls 120.13.12000000.2 2007/10/05 13:13:01 ggupta ship $ */
3
4
5 -- Used to get stage function values
6 g_set_id number;
7 g_set_appl_id number;
8 g_stage_id number;
9 g_function_id number;
10 g_function_appl_id number;
11
12
13 /*
14 ** GEN_ERROR (Internal)
15 **
16 ** Return error message for unexpected sql errors
17 */
18 function GEN_ERROR(routine in varchar2,
19 errcode in number,
20 errmsg in varchar2) return varchar2 is
21 begin
22 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
23 fnd_message.set_token('ROUTINE', routine);
24 fnd_message.set_token('ERRNO', errcode);
25 fnd_message.set_token('REASON', errmsg);
26 return substr( fnd_message.get, 1, 240);
27 end;
28
29
30 /*
31 ** FNDRSSUB
32 **
33 ** Request set master program.
34 **
35 */
36 procedure FNDRSSUB (errbuf out nocopy varchar2,
37 retcode out nocopy number,
38 appl_id in number,
39 set_id in number) is
40 stage_id number; /* ID of current stage. */
41 req_id number; /* Request_ID for current stage. */
42 req_data varchar2(240); /* State of last FNDRSSUB run. */
43 pos number; /* Counter for parsing req_data. */
44 pos2 number; /* Counter for parsing req_data. */
45 critical_outcome varchar2(240); /* Outcome and name of last */
46 /* critical stage. */
47 previous_outcome varchar2(1);
48 next_stage number; /* Next stage to run. */
49 current_outcome varchar2(1); /* Outcome of current stage. */
50 /* 'S' = Success */
51 /* 'W' = Warning */
52 /* 'E' = Error */
53 is_critical varchar2(1); /* Is the current stage critical? */
54 user_stage_name varchar2(240); /* Name of the current stage. */
55 outcome_meaning varchar2(80); /* Translated outcome meaning. */
56 critical_stages boolean; /* Were any critical stages */
57 /* executed in this set? */
58 request_desc varchar2(240); /* Description for submit_request */
59 conc_req_id number; /* Request Id for the Concurrent Program */
60 runinfo_id varchar2(2); /* Flag to find whether request is started or restarted*/
61 error_stage_id number; /*Stage id of failed stage in the last run.*/
62 restart_flag number; /* represents whether stage needs to be restarted or not */
63 req_request_date date;
64 rset_last_updated_date date;
65 tmpDate date;
66 tmp_number number;
67 current_run_number number;
68 /*Bug 5680669*/
69 t_app_name varchar2(50);
70 req_set_name varchar2(30);
71 warn_flag varchar2(1) default 'N';
72 tmp_buf varchar2(240);
73
74 begin
75 /* Get state from last run if any. */
76 req_data := fnd_conc_global.request_data;
77 conc_req_id := fnd_global.conc_request_id();
78 restart_flag := 0;
79
80 /*Bug 5680669 -START-*/
81 select fa.application_short_name,frs.request_set_name
82 into t_app_name, req_set_name
83 from fnd_request_sets_vl frs, fnd_application fa
84 where frs.application_id=appl_id
85 and frs.request_set_id=set_id
86 and frs.application_id=fa.application_id;
87
88 tmp_buf := fnd_submit.justify_program(t_app_name,req_set_name);
89 if tmp_buf is not null then
90 if substr(tmp_buf,1,1) = 'E' then
91 fnd_message.set_name('FND','CONC-RS-CRITICAL DISABLED');
92 fnd_message.set_token('PROGRAM', substr(tmp_buf,3,240));
93 errbuf := substr(fnd_message.get, 1, 240);
94 retcode := 2;
95 return;
96 elsif substr(tmp_buf,1,1) = 'W' then
97 warn_flag := 'Y';
98 fnd_message.set_name('FND','CONC-RS-NONCRITICAL DISABLED');
99 fnd_message.set_token('PROGRAM', substr(tmp_buf,3,240));
100 tmp_buf := substr(fnd_message.get, 1, 240);
101 end if;
102 end if;
103 /*Bug 5680669 -END-*/
104
105 /* Is this the first run? */
106 if (req_data is null) then
107 update FND_CONCURRENT_REQUESTS set RUN_NUMBER = 1 where request_id = conc_req_id;
108 /* Get info for first stage. */
109 begin
110 select request_set_stage_id, request_set_stage_id, user_stage_name
111 into stage_id, next_stage, request_desc
112 from fnd_request_sets sets,
113 fnd_request_set_stages_vl stages
114 where sets.application_id = appl_id
115 and sets.request_set_id = set_id
116 and stages.set_application_id = sets.application_id
117 and stages.request_set_id = sets.request_set_id
118 and sets.start_stage = stages.request_set_stage_id;
119 exception
120 when NO_DATA_FOUND then
121 fnd_message.set_name('FND','CONC-Missing first stage');
122 errbuf := substr(fnd_message.get, 1, 240);
123 retcode := 2;
124 return;
125 end;
126 /* Initialize critical outcome */
127 critical_outcome := 'NONE';
128 runinfo_id := 'F';
129 else /* Program was restarted */
130 /* Parse Request data: "runinfo_id, Error Stage, stage_id, request_id, critical_outcome" */
131 /* runinfo_id can take Following values
132 (null) - Request Set was running for the first time, this module is getting invoked first time.
133 F - Request Set was running for the first time, this module is invoked more than once
134 R - Request Set was restarted.
135 C - Request Set completed it's Execution.
136 */
137 pos := instr(req_data, ',', 1, 1);
138 runinfo_id := substr(req_data, 1, pos - 1);
139 pos2 := instr(req_data, ',', pos + 1, 1);
140 error_stage_id := to_number(substr(req_data, pos + 1, pos2 - pos -1));
141 pos := pos2;
142 pos2 := instr(req_data, ',', pos + 1, 1);
143 stage_id := to_number(substr(req_data, pos + 1, pos2 - pos -1));
144 pos := pos2;
145 pos2 := instr(req_data, ',', pos + 1, 1);
146 req_id := to_number(substr(req_data, pos + 1, pos2 - pos -1));
147 /* bug 1961715 Removed null as the 3rd parameter */
148 critical_outcome := substr(req_data, pos2 + 1);
149 if (runinfo_id = 'C') then
150 begin
151 select REQUEST_DATE into req_request_date from FND_CONCURRENT_REQUESTS where REQUEST_ID = conc_req_id;
152 select LAST_UPDATE_DATE into rset_last_updated_date from FND_REQUEST_SETS sets
153 where REQUEST_SET_ID = set_id AND application_id = appl_id;
154 SELECT max(last_update_date) INTO tmpDate FROM fnd_request_set_stages
155 WHERE request_set_id = set_id
156 AND SET_APPLICATION_ID = appl_id;
157 IF( tmpDate > rset_last_updated_date) THEN
158 rset_last_updated_date := tmpDate;
159 END IF;
160 SELECT max(last_update_date) INTO tmpDate FROM fnd_request_set_programs
161 WHERE request_set_id = set_id AND set_application_id = appl_id;
162 IF( tmpDate > rset_last_updated_date) THEN
163 rset_last_updated_date := tmpDate;
164 END IF;
165 if( rset_last_updated_date > req_request_date ) then
166 errbuf := gen_error('FNDRSSUB', SQLCODE, 'Request Set Definition Changed');
167 retcode := 2;
168 return;
169 end if;
170 exception
171 when no_data_found then
172 NULL;
173 end;
174 SELECT run_number INTO current_run_number FROM fnd_concurrent_requests WHERE request_id = conc_req_id;
175 select count(r.request_set_program_id) INTO tmp_number
176 from fnd_run_requests r,
177 fnd_concurrent_requests fcr1, fnd_concurrent_requests fcr2
178 where r.parent_request_id = conc_req_id
179 and fcr1.parent_request_id = fcr2.request_id
180 and fcr1.concurrent_program_id = r.concurrent_program_id
181 and r.request_id = fcr1.request_id
182 and fcr1.status_code = 'E'
183 and fcr2.parent_request_id = conc_req_id
184 and fcr2.run_number = current_run_number
185 and error_stage_id = to_number(fcr2.argument3)
186 AND r.request_set_program_id IS NOT NULL
187 AND r.request_set_program_id NOT IN
188 (
189 SELECT REQUEST_SET_PROGRAM_ID FROM FND_REQUEST_SET_PROGRAMS WHERE SET_APPLICATION_ID = appl_id
190 AND REQUEST_SET_ID = set_id AND REQUEST_SET_STAGE_ID = error_stage_id
191 );
192 if( tmp_number <> 0)THEN
193 errbuf := gen_error('FNDRSSUB', SQLCODE, 'Request Set Definition Changed');
194 retcode := 2;
195 return;
196 END IF;
197
198
199 if ( error_stage_id IS null ) then
200 errbuf := gen_error('FNDRSSUB', SQLCODE, 'Already Succeeded or Last Error Stage id not available');
201 retcode := 2;
202 return;
203 else
204 begin
205 update FND_CONCURRENT_REQUESTS set RUN_NUMBER = RUN_NUMBER + 1, COMPLETION_TEXT = null where request_id = conc_req_id;
206 critical_outcome := 'NONE';
207 restart_flag := 1;
208 runinfo_id := 'R';
209 stage_id := error_stage_id;
210 error_stage_id := null;
211 select request_set_stage_id, user_stage_name
212 into next_stage, request_desc
213 from fnd_request_set_stages_vl stages
214 where stages.set_application_id = appl_id
215 and stages.request_set_id = set_id
216 and stages.request_set_stage_id = stage_id;
217 exception
218 when NO_DATA_FOUND then
219 fnd_message.set_name('FND','CONC-Missing first stage');
220 errbuf := substr(fnd_message.get, 1, 240);
221 retcode := 2;
222 return;
223 end;
224 end if;
225 else
226 /* Get status for current stage */
227 begin
228 select decode(status_code, 'C', 'S', 'G', 'W', 'E')
229 into current_outcome
230 from fnd_concurrent_requests
231 where request_id = req_id;
232 exception
233 when NO_DATA_FOUND then
234 fnd_message.set_name('FND','CONC-Missing Request');
235 fnd_message.set_token('ROUTINE', 'FND_REQUEST_SET.FNDRSSUB');
236 fnd_message.set_token('REQUEST', to_char(req_id));
237 errbuf := fnd_message.get;
238 retcode := 2;
239 return;
240 end;
241 /* Get Next Stage and Critical info*/
242 begin
243 select decode(current_outcome, 'S', success_link, 'W', warning_link, error_link), critical, user_stage_name
244 into next_stage, is_critical, user_stage_name
245 from fnd_request_set_stages_vl
246 where request_set_id = set_id
247 and set_application_id = appl_id
248 and request_set_stage_id = stage_id;
249 exception
250 when NO_DATA_FOUND then
251 fnd_message.set_name('FND','CONC-Missing stage');
252 errbuf := substr(fnd_message.get,1, 240);
253 retcode := 2;
254 return;
255 end;
256 /* update the error stage id in this run */
257 if( current_outcome = 'E' and error_stage_id is null ) then
258 error_stage_id := stage_id;
259 end if;
260 /* Update critical_outcome if necessary */
261 /* new code added to check the outcome of all the stages
262 if we have more than one critical stages then
263 considering the 'worst' critical stage outcome as set outcome
264 bug 3785411
265 */
266 if (is_critical = 'Y') then
267 previous_outcome := substr(critical_outcome, 1, 1);
268 if (previous_outcome <> 'E') then
269 if(current_outcome = 'E') then
270 critical_outcome := substrb(current_outcome || user_stage_name, 1, 240);
271 elsif(current_outcome = 'W' AND previous_outcome <> 'W') then
272 critical_outcome := substrb(current_outcome || user_stage_name, 1, 240);
273 elsif(previous_outcome <> 'S') then
274 critical_outcome := substrb(current_outcome || user_stage_name, 1, 240);
275 end if;
276 end if;
277 end if;
278
279 /* Is the set complete? */
280 if (next_stage is null) then
281 /* Were there any critical stages? */
282 if (critical_outcome <> 'NONE') then
283 critical_stages := TRUE;
284 current_outcome := substr(critical_outcome, 1, 1);
285 /* bug 1961715 Removed null as the 3rd parameter */
286 user_stage_name := substr(critical_outcome, 2);
287 else
288 critical_stages := FALSE;
289 end if;
290 /* Get final outcome meaning */
291 select meaning
292 into outcome_meaning
293 from fnd_lookups
294 where lookup_type = 'CP_SET_OUTCOME'
295 and lookup_code = current_outcome;
296 retcode := to_number(translate(current_outcome, 'SWE', '012'));
297 if (critical_stages) then
298 fnd_message.set_name('FND','CONC-Set Completed Critical');
299 else
300 fnd_message.set_name('FND', 'CONC-Set Completed');
301 end if;
302 fnd_message.set_token('OUTCOME', outcome_meaning);
303 fnd_message.set_token('STAGE', user_stage_name);
304 errbuf := substr(fnd_message.get, 1, 240);
305 fnd_conc_global.set_req_globals( request_data =>
306 substrb( 'C,' || to_char(error_stage_id) || ',' || to_char(stage_id) || ',' ||
307 to_char(req_id) || ',' ||
308 critical_outcome, 1, 240));
309
310 /*Bug 5680669 */
311 if warn_flag = 'Y' then
312 retcode := 1;
313 errbuf := tmp_buf;
314 end if;
315 return;
316 end if;
317 /* Get next stage */
318 begin
319 Select user_stage_name
320 into request_desc
321 from fnd_request_set_stages_vl
322 where request_set_id = set_id
323 and set_application_id = appl_id
324 and request_set_stage_id = next_stage;
325 exception
326 when NO_DATA_FOUND then
327 if (current_outcome = 'S') then
328 fnd_message.set_name('FND','CONC-BAD SUCCESS LINK');
329 elsif (current_outcome = 'W') then
330 fnd_message.set_name('FND','CONC-BAD WARNING LINK');
331 else
332 fnd_message.set_name('FND','CONC-BAD ERROR LINK');
333 end if;
334 errbuf := substr(fnd_message.get,1, 240);
335 retcode := 2;
336 return;
337 end;
338 end if;
339 end if;
340 /* Submit Request for the stage. */
341 fnd_request.internal(type=>'S');
342 req_id := fnd_request.submit_request('FND', 'FNDRSSTG',
343 request_desc, NULL, TRUE,
344 to_char(appl_id), to_char(set_Id),
345 to_char(next_stage),
346 to_char(fnd_global.conc_request_id),to_char(restart_flag));
347 if (req_id = 0) then
348 errbuf := substr(fnd_message.get,1, 240);
349 retcode := 2;
350 return;
351 else
352 update fnd_concurrent_requests set RUN_NUMBER =
353 (select RUN_NUMBER from fnd_concurrent_requests where request_id = conc_req_id)
354 where request_id = req_id;
355 fnd_conc_global.set_req_globals(
356 conc_status => 'PAUSED',
357 request_data => runinfo_id||','||to_char(error_stage_id)||','||substrb( to_char(next_stage) || ',' ||
358 to_char(req_id) || ',' ||
359 critical_outcome, 1, 240));
360 fnd_message.set_name('FND','CONC-Stage Submitted');
361 fnd_message.set_token('STAGE', request_desc);
362 errbuf := substr(fnd_message.get,1, 240);
363 retcode := 0;
364 return;
365 end if;
366
367 exception
368 when OTHERS then
369 errbuf := gen_error('FNDRSSUB', SQLCODE, SQLERRM);
370 retcode := 2;
371 return;
372 end FNDRSSUB;
373
374
375 /*
376 ** FNDRSSTG
377 **
378 ** Request set stage master program.
379 **
380 */
381 procedure FNDRSSTG (errbuf out nocopy varchar2,
382 retcode out nocopy number,
383 appl_id in number,
384 set_id in number,
385 stage_Id in number,
386 parent_id in number,
387 restart_flag in number default 0) is
388 cursor stage_requests(appl_id number, set_id number,
389 stage_id number, parent_id number) is
390 select sp.critical,
391 sp.sequence,
392 a.application_short_name,
393 cp.concurrent_program_name,
394 r.request_set_program_id,
395 r.application_id,
396 r.concurrent_program_id,
397 r.number_of_copies,
398 r.printer,
399 r.print_style,
400 r.save_output_flag,
401 r.nls_language,
402 r.nls_territory,
403 /* NLS Project */
404 r.numeric_characters,
405 argument1, argument2, argument3, argument4, argument5,
406 argument6, argument7, argument8, argument9, argument10,
407 argument11, argument12, argument13, argument14, argument15,
408 argument16, argument17, argument18, argument19, argument20,
409 argument21, argument22, argument23, argument24, argument25,
410 argument26, argument27, argument28, argument29, argument30,
411 argument31, argument32, argument33, argument34, argument35,
412 argument36, argument37, argument38, argument39, argument40,
413 argument41, argument42, argument43, argument44, argument45,
414 argument46, argument47, argument48, argument49, argument50,
415 argument51, argument52, argument53, argument54, argument55,
416 argument56, argument57, argument58, argument59, argument60,
417 argument61, argument62, argument63, argument64, argument65,
418 argument66, argument67, argument68, argument69, argument70,
419 argument71, argument72, argument73, argument74, argument75,
420 argument76, argument77, argument78, argument79, argument80,
421 argument81, argument82, argument83, argument84, argument85,
422 argument86, argument87, argument88, argument89, argument90,
423 argument91, argument92, argument93, argument94, argument95,
424 argument96, argument97, argument98, argument99, argument100, r.org_id
425 from fnd_request_set_programs sp, fnd_run_requests r,
426 fnd_concurrent_programs cp, fnd_application a
427 where sp.set_application_id = appl_id
428 and sp.request_set_id = set_id
429 and sp.request_set_stage_id = stage_id
430 and sp.request_set_program_id = r.request_set_program_id
431 and sp.set_application_id = r.set_application_id
432 and sp.request_set_id = r.request_set_id
433 and r.parent_request_id = parent_id
434 and a.application_id = r.application_id
435 and cp.application_id = r.application_id
436 and cp.concurrent_program_id = r.concurrent_program_id
437 order by sp.sequence;
438
439 cursor stage_requests_restart( appl_id number, set_id number,
440 stage_id number, parent_id number,
441 current_run_number number) is
442 select sp.critical,
443 sp.sequence,
444 a.application_short_name,
445 cp.concurrent_program_name,
446 r.request_set_program_id,
447 r.application_id,
448 r.concurrent_program_id,
449 r.number_of_copies,
450 r.printer,
451 r.print_style,
452 r.save_output_flag,
453 r.nls_language,
454 r.nls_territory,
455 /* NLS Project */
456 r.numeric_characters,
457 r.argument1, r.argument2, r.argument3, r.argument4, r.argument5,
458 r.argument6, r.argument7, r.argument8, r.argument9, r.argument10,
459 r.argument11, r.argument12, r.argument13, r.argument14, r.argument15,
460 r.argument16, r.argument17, r.argument18, r.argument19, r.argument20,
461 r.argument21, r.argument22, r.argument23, r.argument24, r.argument25,
462 r.argument26, r.argument27, r.argument28, r.argument29, r.argument30,
463 r.argument31, r.argument32, r.argument33, r.argument34, r.argument35,
464 r.argument36, r.argument37, r.argument38, r.argument39, r.argument40,
465 r.argument41, r.argument42, r.argument43, r.argument44, r.argument45,
466 r.argument46, r.argument47, r.argument48, r.argument49, r.argument50,
467 r.argument51, r.argument52, r.argument53, r.argument54, r.argument55,
468 r.argument56, r.argument57, r.argument58, r.argument59, r.argument60,
469 r.argument61, r.argument62, r.argument63, r.argument64, r.argument65,
470 r.argument66, r.argument67, r.argument68, r.argument69, r.argument70,
471 r.argument71, r.argument72, r.argument73, r.argument74, r.argument75,
472 r.argument76, r.argument77, r.argument78, r.argument79, r.argument80,
473 r.argument81, r.argument82, r.argument83, r.argument84, r.argument85,
474 r.argument86, r.argument87, r.argument88, r.argument89, r.argument90,
475 r.argument91, r.argument92, r.argument93, r.argument94, r.argument95,
476 r.argument96, r.argument97, r.argument98, r.argument99, r.argument100, r.org_id
477 from fnd_request_set_programs sp, fnd_run_requests r,
478 fnd_concurrent_programs cp, fnd_application a, fnd_concurrent_requests fcr1, fnd_concurrent_requests fcr2
479 where sp.set_application_id = appl_id
480 and sp.request_set_id = set_id
481 and sp.request_set_stage_id = stage_id
482 and sp.request_set_program_id = r.request_set_program_id
483 and sp.set_application_id = r.set_application_id
484 and sp.request_set_id = r.request_set_id
485 and r.parent_request_id = parent_id
486 and a.application_id = r.application_id
487 and cp.application_id = r.application_id
488 and cp.concurrent_program_id = r.concurrent_program_id
489 and fcr1.parent_request_id = fcr2.request_id
490 and fcr1.concurrent_program_id = r.concurrent_program_id
491 and r.request_id = fcr1.request_id
492 and fcr1.status_code = 'E'
493 and fcr2.parent_request_id = parent_id
494 and fcr2.run_number = current_run_number - 1
495 and stage_id = to_number(fcr2.argument3)
496 order by sp.sequence;
497
498 cursor critical_outcomes (req_id number) is
499 select decode(status_code, 'C', 'S', 'G', 'W', 'E') outcome
500 from fnd_concurrent_requests
501 where parent_request_id = req_id
502 and critical = 'Y';
503
504 cursor stage_req_printers(parent_req_id number, set_program_id number) is
505 select arguments printer, number_of_copies
506 from fnd_run_req_pp_actions
507 where parent_request_id = parent_req_id
508 and request_set_program_id = set_program_id
509 and action_type = 1
510 order by sequence;
511
512 cursor stage_req_notifications(parent_req_id number,
513 set_program_id number) is
514 select arguments notify
515 from fnd_run_req_pp_actions
516 where parent_request_id = parent_req_id
517 and request_set_program_id = set_program_id
518 and action_type = 2
519 order by sequence;
520
521 cursor stage_req_layouts(parent_req_id number,
522 set_program_id number) is
523 select argument1, argument2, argument3, argument4, argument5
524 from fnd_run_req_pp_actions
525 where parent_request_id = parent_req_id
526 and request_set_program_id = set_program_id
527 and action_type = 6
528 order by sequence;
529
530
531 req_id number;
532 critical_request varchar2(1) default 'N';
533 hardwired_outcome varchar2(1);
534 current_outcome varchar2(1) default 'S';
535 warning boolean default FALSE;
536 error boolean default FALSE;
537 outcome_meaning varchar2(80); /* Translated outcome meaning. */
538 req_data varchar2(10); /* State of last FNDRSSUB run. */
539 has_reqs boolean default FALSE;
540 funct varchar2(61); /* Function string */
541 fcursor varchar2(75); /* Cursor sting for dbms_sql */
542 cid number; /* Cursor ID for dbms_sql */
543 dummy number;
544 printer varchar2(30);
545 copies number;
546 /* xml project */
547 t_app_name varchar2(50);
548 t_code varchar2(80);
549 t_language varchar2(2);
550 t_territory varchar2(2);
551 t_format varchar2(6);
552 req stage_requests%ROWTYPE;
553 old_reqid number;
554 run_number_var number;
555 begin
556 /* Get outcome and function for stage if any.
557 * Also, set up function globals. */
558 begin
559 errbuf := null;
560 select outcome, execution_file_name,
561 s.set_application_id, s.request_set_id, s.request_set_stage_id,
562 s.function_id, s.function_application_id
563 into hardwired_outcome, funct,
564 g_set_appl_id, g_set_id, g_stage_id,
565 g_function_id, g_function_appl_id
566 from fnd_request_set_stages s, fnd_executables e
567 where s.set_application_id = appl_id
568 and s.request_set_id = set_id
569 and s.request_set_stage_id = stage_id
570 and e.executable_id(+) = s.function_id
571 and e.application_id(+) = s.function_application_id;
572 exception
573 when NO_DATA_FOUND then
574 fnd_message.set_name('FND','CONC-Missing stage');
575 errbuf := substr(fnd_message.get,1, 240);
576 retcode := 2;
577 return;
578 end;
579
580 /* Get state from last run if any. */
581 req_data := fnd_conc_global.request_data;
582
583 /* Is this the first run? */
584 if (req_data is null) then
585 begin
586 select run_number into run_number_var from fnd_concurrent_requests where request_id = parent_id;
587 if( restart_flag = 0) then
588 open stage_requests( appl_id, set_id, stage_id, parent_id);
589 else
590 open stage_requests_restart( appl_id, set_id, stage_id, parent_id,
591 run_number_var);
592 end if;
593 loop
594 if( restart_flag = 0 ) then
595 fetch stage_requests into req;
596 exit when stage_requests%NOTFOUND;
597 else
598 fetch stage_requests_restart into req;
599 exit when stage_requests_restart%NOTFOUND;
600 end if;
601 -- for req in stage_requests(appl_id, set_id, stage_id, parent_id) loop
602 if (req.critical = 'Y') then
603 critical_request := 'Y';
604 end if;
605
606 open stage_req_printers(parent_id, req.request_set_program_id);
607 fetch stage_req_printers into printer, copies;
608 if (stage_req_printers%found) then
609 if (not fnd_request.set_print_options(
610 printer => printer,
611 style => req.print_style,
612 copies => copies,
613 save_output => (req.save_output_flag = 'Y'),
614 print_together => NULL))
615 then
616 errbuf := substr(fnd_message.get, 1, 240);
617 retcode := 2;
618 close stage_req_printers;
619 rollback;
620 return;
621 end if;
622
623 fetch stage_req_printers into printer, copies;
624 while (stage_req_printers%found) loop
625 if (not fnd_request.add_printer(
626 printer => printer,
627 copies => copies)) then
628 errbuf := substr(fnd_message.get, 1, 240);
629 retcode := 2;
630 close stage_req_printers;
631 rollback;
632 return;
633 end if;
634 fetch stage_req_printers into printer, copies;
635 end loop;
636 else
637 if (not fnd_request.set_print_options(
638 printer => null,
639 style => req.print_style,
640 copies => 0,
641 save_output => (req.save_output_flag = 'Y'),
642 print_together => NULL))
643 then
644 errbuf := substr(fnd_message.get, 1, 240);
645 retcode := 2;
646 close stage_req_printers;
647 rollback;
648 return;
649 end if;
650 end if;
651 close stage_req_printers;
652
653 for notify_rec in stage_req_notifications
654 (parent_id, req.request_set_program_id) loop
655 if (not fnd_request.add_notification(
656 user=>notify_rec.notify)) then
657 /* 3900886: User not found in wf_roles, continue with warning */
658 if (errbuf is NULL) then
659 errbuf := substr(fnd_message.get||
660 ': '|| notify_rec.notify, 1, 240);
661 else
662 if (instr(errbuf, notify_rec.notify, -1, 1) = 0) then
663 errbuf := substr(errbuf ||', '|| notify_rec.notify, 1, 240);
664 end if;
665 end if;
666 retcode := 1;
667 end if;
668 end loop;
669
670 -- XML Project
671 open stage_req_layouts(parent_id, req.request_set_program_id);
672
673 fetch stage_req_layouts into t_app_name,
674 t_code,
675 t_language,
676 t_territory,
677 t_format;
678 while (stage_req_layouts%found) loop
679 if (not fnd_request.add_layout(
680 t_app_name,
681 t_code,
682 t_language,
683 t_territory,
684 t_format)) then
685 errbuf := substr(fnd_message.get, 1, 240);
686 retcode := 2;
687 close stage_req_printers;
688 rollback;
689 return;
690 end if;
691 fetch stage_req_layouts into t_app_name,
692 t_code,
693 t_language,
694 t_territory,
695 t_format;
696 end loop;
697 close stage_req_layouts;
698
699
700 /* NLS Project - Added Numeric Character to set_options */
701 if (not fnd_request.set_options(language=>req.nls_language,
702 territory=>req.nls_territory,
703 datagroup=>'',
704 numeric_characters=>req.numeric_characters)) then
705 errbuf := substr(fnd_message.get, 1, 240);
706 retcode := 2;
707 rollback;
708 return;
709 end if;
710
711 fnd_request.internal(critical => req.critical, type=>'P');
712
713 /* MOAC */
714 fnd_request.set_org_id(req.org_id);
715
716 req_id := fnd_request.submit_request(
717 req.application_short_name, req.concurrent_program_name,
718 Null, NULL, TRUE,
719 req.argument1, req.argument2, req.argument3,
720 req.argument4, req.argument5, req.argument6,
721 req.argument7, req.argument8, req.argument9,
722 req.argument10, req.argument11, req.argument12,
723 req.argument13, req.argument14, req.argument15,
724 req.argument16, req.argument17, req.argument18,
725 req.argument19, req.argument20, req.argument21,
726 req.argument22, req.argument23, req.argument24,
727 req.argument25, req.argument26, req.argument27,
728 req.argument28, req.argument29, req.argument30,
729 req.argument31, req.argument32, req.argument33,
730 req.argument34, req.argument35, req.argument36,
731 req.argument37, req.argument38, req.argument39,
732 req.argument40, req.argument41, req.argument42,
733 req.argument43, req.argument44, req.argument45,
734 req.argument46, req.argument47, req.argument48,
735 req.argument49, req.argument50, req.argument51,
736 req.argument52, req.argument53, req.argument54,
737 req.argument55, req.argument56, req.argument57,
738 req.argument58, req.argument59, req.argument60,
739 req.argument61, req.argument62, req.argument63,
740 req.argument64, req.argument65, req.argument66,
741 req.argument67, req.argument68, req.argument69,
742 req.argument70, req.argument71, req.argument72,
743 req.argument73, req.argument74, req.argument75,
744 req.argument76, req.argument77, req.argument78,
745 req.argument79, req.argument80, req.argument81,
746 req.argument82, req.argument83, req.argument84,
747 req.argument85, req.argument86, req.argument87,
748 req.argument88, req.argument89, req.argument90,
749 req.argument91, req.argument92, req.argument93,
750 req.argument94, req.argument95, req.argument96,
751 req.argument97, req.argument98, req.argument99,
752 req.argument100);
753 if (req_id = 0) then
754 errbuf := substr(fnd_message.get, 1, 240);
755 retcode := 2;
756 return;
757 end if;
758
759 -- set the request_id in fnd_run_requests so that
760 -- we can identify the failed request later if we
761 -- restart this stage.
762 UPDATE fnd_run_requests
763 SET request_id = req_id
764 WHERE request_set_program_id = req.request_set_program_id
765 AND application_id = req.application_id
766 AND concurrent_program_id = req.concurrent_program_id
767 AND parent_request_id = parent_id
768 AND set_application_id = appl_id
769 AND request_set_id = set_id;
770
771 UPDATE fnd_concurrent_requests
772 SET RUN_NUMBER = run_number_var
773 WHERE request_id = req_id;
774
775 has_reqs := TRUE;
776 end loop;
777 /* close the cursor open */
778 if( restart_flag = 0) then
779 close stage_requests;
780 else
781 close stage_requests_restart;
782 end if;
783
784 if (has_reqs = FALSE) then
785 fnd_message.set_name('FND','CONC-Stage has no requests');
786 errbuf := substr(fnd_message.get,1, 240);
787 /* Exit with error unless we have a hardwired outcome. */
788 if (hardwired_outcome = 'C') then
789 retcode := 2;
790 else
791 retcode := to_number(translate(hardwired_outcome, 'SWE', '012'));
792 end if;
793 return;
794 end if;
795 end;
796
797 fnd_conc_global.set_req_globals(
798 conc_status => 'PAUSED',
799 request_data => critical_request);
800 if (retcode = 1) then /* submission with warnings, message set */
801 fnd_message.set_name('FND','CONC-Stage Reqs Submitted Warn');
802 fnd_message.set_token('WARNING', substr (errbuf, 1, 240), FALSE);
803 errbuf := substr(fnd_message.get,1, 240);
804 return;
805 else
806 fnd_message.set_name('FND','CONC-Stage Reqs Submitted');
807 errbuf := substr(fnd_message.get,1, 240);
808 retcode := 0;
809 return;
810 end if;
811
812 else /* FNDRSSTG has been restarted. */
813
814 /* Compute stage exit code. */
815
816 /* Do we have a hardwired outcome? */
817 if (hardwired_outcome <>'C') then
818 fnd_message.set_name('FND','CONC-Stage outcome hardwired');
819 current_outcome := hardwired_outcome;
820
821 else /* Call evaluation function */
822 if (funct is null) then
823 fnd_message.set_name('FND','CONC-Invalid Stage Function');
824 errbuf := substr(fnd_message.get,1, 240);
825 retcode := 2;
826 return;
827 end if;
828
829
830 fcursor := 'begin :r := '||funct||'; end;';
831 begin
832 cid := dbms_sql.open_cursor;
833 dbms_sql.parse(cid, fcursor, dbms_sql.v7);
834 dbms_sql.bind_variable(cid, ':r', 'a');
835 dummy := dbms_sql.execute(cid);
836 dbms_sql.variable_value(cid, ':r', current_outcome);
837 dbms_sql.close_cursor(cid);
838 exception
839 when others then
840 errbuf := gen_error(funct, SQLCODE, SQLERRM);
841 retcode := 2;
842 return;
843 end;
844
845 end if;
846
847 select meaning
848 into outcome_meaning
849 from fnd_lookups
850 where lookup_type = 'CP_SET_OUTCOME'
851 and lookup_code = current_outcome;
852
853 fnd_message.set_name('FND', 'CONC-Stage outcome computed');
854 fnd_message.set_token('OUTCOME', outcome_meaning);
855 errbuf := substr(fnd_message.get, 1, 240);
856 retcode := to_number(translate(current_outcome, 'SWE', '012'));
857
858 return;
859 end if;
860 exception
861 when OTHERS then
862 errbuf := gen_error('FNDRSSTG', SQLCODE, SQLERRM);
863 retcode := 2;
864 return;
865 end FNDRSSTG;
866
867
868
869 /* 1310211 - Need to handle the case where the child request has restarted the
870 parent but has not yet finished the post-processing steps. In this case the child's
871 status will still be 'R'.
872 If we find a child still running, we will wait a small amount of time, then try the
873 query again. After 5 repetitions if the child is still running we will report it as an
874 error condition
875 */
876
877 function standard_stage_evaluation return varchar2 is
878 warning boolean := FALSE;
879 stillrunning boolean := FALSE;
880 i integer;
881 begin
882
883 <<outer>>
884 for i in 1 .. stage_looptimes loop
885 stillrunning := FALSE;
886 for request in fnd_request_set.stage_request_info loop
887 if (request.exit_status = 'E') then
888 return 'E';
889 elsif (request.exit_status = 'R') then
890 stillrunning := TRUE;
891 elsif (request.exit_status = 'W') then
892 warning := TRUE;
893 end if;
894 end loop;
895
896
897 exit outer when stillrunning = FALSE;
898 dbms_lock.sleep(stage_sleeptime);
899
900 end loop;
901
902 if (warning) then
903 return 'W';
904 elsif (stillrunning) then
905 return 'E';
906 else
907 return 'S';
908 end if;
909 end;
910
911
912 -- Name
913 -- GET_STAGE_PARAMETER
914 --
915 -- Purpose
916 -- Used by stage functions to retrieve parameter
917 -- values for the current stage.
918 --
919 function get_stage_parameter(name in varchar2) return varchar2 is
920 val varchar2(240);
921 begin
922 select value into val
923 from fnd_stage_fn_parameters_vl p, fnd_stage_fn_parameter_values v
924 where v.set_application_id = g_set_appl_id
925 and v.request_set_id = g_set_id
926 and v.request_set_stage_id = g_stage_id
927 and v.function_id = g_function_id
928 and v.function_application_id =g_function_appl_id
929 and v.parameter_id = p.parameter_id
930 and p.parameter_name = name
931 and p.function_id = v.function_id
932 and p.application_id = v.function_application_id;
933
934 return val;
935 exception
936 when no_data_found then
937 return null;
938 end;
939
940
941 end FND_REQUEST_SET;