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