1 package body FND_WF_STANDARD as
2 /* $Header: AFWFSTDB.pls 120.1.12010000.3 2008/08/27 17:29:00 alepe ship $ */
3
4
5 -------------------------------------------------------------------
6 -- Name: SubmitConcProgram
7 -- Description: submits a concurrent program ONLY.
8 -- returns the request_id to the choosen item attribute
9 -- Notes:
10 -- APPS context must already be set
11 -- use fnd_global.apps_initialize(user_id,resp_id,resp_appl_id);
12 -------------------------------------------------------------------
13
14 Procedure SubmitConcProgram(itemtype in varchar2,
15 itemkey in varchar2,
16 actid in number,
17 funcmode in varchar2,
18 resultout in out nocopy varchar2)
19 is
20 req_id number;
21 BEGIN
22
23 -- Do nothing in cancel or timeout mode
24 if (funcmode <> wf_engine.eng_run) then
25 resultout := wf_engine.eng_null;
26 return;
27 end if;
28
29 fnd_wf_standard.Submit_CP(itemtype, itemkey, actid, req_id);
30
31 resultout := wf_engine.eng_completed;
32
33 exception
34 when others then
35 Wf_Core.Context('FND_WF_STANDARD', 'SubmitConcProgram', itemtype, itemkey);
36 raise;
37 end SubmitConcProgram;
38
39
40 -------------------------------------------------------------------
41 -- Name: ExecuteConcProgram
42 -- Description: Executes a concurrent program.
43 -- This submits the request and waits for it to complete.
44 -- Notes:
45 -- APPS context must already be set
46 -- use fnd_global.apps_initialize(user_id,resp_id,resp_appl_id);
47 -------------------------------------------------------------------
48 Procedure ExecuteConcProgram(itemtype in varchar2,
49 itemkey in varchar2,
50 actid in number,
51 funcmode in varchar2,
52 resultout in out nocopy varchar2)
53 is
54 result boolean;
55 num_val number;
56 req_id number;
57 BEGIN
58 -- Do nothing in cancel or timeout mode
59 if (funcmode <> wf_engine.eng_run) then
60 resultout := wf_engine.eng_null;
61 return;
62 end if;
63
64 fnd_wf_standard.Submit_CP(itemtype, itemkey, actid, req_id);
65
66 -- if we get here, the request must have been succesfully submitted.
67 -- also, it cannot have been run yet because we havent committed
68 -- so seed the callback
69 num_val := fnd_wf_standard.Seed_CB(itemtype, itemkey, actid, req_id);
70
71
72 -- put this activity in wait/notified state
73 resultout := wf_engine.eng_notified||':'||wf_engine.eng_null||
74 ':'||wf_engine.eng_null;
75
76 exception
77 when others then
78 Wf_Core.Context('FND_WF_STANDARD', 'ExecuteConcProgram', itemtype, itemkey);
79 raise;
80
81 end ExecuteConcProgram;
82
83
84 -------------------------------------------------------------------
85 -- Name: WaitForConcProgram
86 -- Description: Waits for a concurrent program to complete.
87 -------------------------------------------------------------------
88 Procedure WaitForConcProgram(itemtype in varchar2,
89 itemkey in varchar2,
90 actid in number,
91 funcmode in varchar2,
92 resultout in out nocopy varchar2)
93 is
94 req_id number;
95 phase varchar2(20);
96 s0 varchar2(200); --dummy
97 devphase varchar2(200); /* Bug 2220527 */
98 complete_status varchar2(10):=null;
99
100 cursor avgCPtime(c_reqID in number) is
101 select avg(fr1.actual_completion_date - fr1.actual_start_date)
102 from fnd_concurrent_requests fr1, fnd_concurrent_requests fr2
103 where fr2.request_id = c_reqID
104 and fr1.concurrent_program_id = fr2.concurrent_program_id
105 and fr1.program_application_id = fr2.program_application_id
106 and fr1.actual_start_date is not null
107 and fr1.actual_completion_date is not null;
108
109 avgTime number;
110 l_minute number;
111
112 BEGIN
113 -- Do nothing in cancel or timeout mode
114 if (funcmode <> wf_engine.eng_run) then
115 resultout := wf_engine.eng_null;
116 else
117 req_id := wf_engine.GetActivityAttrNumber(itemtype,itemkey,actid,
118 'REQUEST_ID');
119
120 if (fnd_concurrent.get_request_status(REQUEST_ID => req_id,
121 PHASE => phase,
122 STATUS => s0,
123 DEV_PHASE => devphase,
124 DEV_STATUS => complete_status,
125 MESSAGE => s0)) then
126 if devphase = 'COMPLETE' then
127 resultout := wf_engine.eng_completed||':'||complete_status;
128 elsif devphase = 'RUNNING' then
129 --Calculate a minute as 1 day / 24 hours / 60 minutes.
130 l_minute := 1/24/60;
131 --The request is running but could be in post-processing so we will
132 --calculate a delay to defer to the background engine.
133 --The assumption here is that the average will give enough of an
134 --approximation to allow only one defer and not wait too long to
135 --recheck. If we find need, we can enhance to calculate from the
136 --start time of this request.
137 open avgCPTime(req_id);
138 fetch avgCPTime into avgTime;
139 close avgCPTime;
140
141 --If avgTime is < 1 minute or null, we will default to 1 minute.
142 resultout := wf_engine.eng_deferred||':'||
143 to_char(sysdate+greatest(nvl(avgTime,0),l_minute),
144 wf_engine.date_format);
145 else
146 if fnd_wf_standard.Seed_CB(itemtype, itemkey, actid, req_id) < 0 then
147 resultout := wf_engine.eng_completed||':'||complete_status;
148 else
149 -- put this activity in wait/notified state
150 resultout := wf_engine.eng_notified||':'||wf_engine.eng_null||
151 ':'||wf_engine.eng_null;
152 end if; --Seed_CB
153 end if; -- devphase
154 end if; -- get_request_status
155 end if; -- funcmode
156 exception
157 when others then
158 if (avgCPTime%ISOPEN) then
159 close avgCPTime;
160 end if;
161
162 Wf_Core.Context('FND_WF_STANDARD', 'WaitForConcProgram', itemtype, itemkey);
163 raise;
164
165 end WaitForConcProgram;
166
167
168
169
170 -------------------------------------------------------------------
171 -- Name: Submit_CP (PRIVATE)
172 -- Description: submits a concurrent program
173 -- Notes:
174 -- APPS context must already be set
175 -- use fnd_global.apps_initialize(user_id,resp_id,resp_appl_id);
176 -------------------------------------------------------------------
177 Procedure Submit_CP(itemtype in varchar2,
178 itemkey in varchar2,
179 actid in number,
180 req_id in out nocopy number)
181 is
182
183 type AttrArrayTyp is table of varchar2(240) index by binary_integer;
184 conc_arg AttrArrayTyp; -- Array of item attributes
185 conc_count pls_integer := 0; -- Array size
186
187 appl_name varchar2(30);
188 prog_name varchar2(30);
189 arg_count number;
190 aname varchar2(30);
191 msg varchar2(2000);
192 i number;
193
194 submission_error exception;
195
196 BEGIN
197
198 --apps content must already be set, see comments above
199 --for testing use something like: fnd_global.apps_initialize( 0,20419,0);
200
201 -- get all arguments.
202 appl_name := wf_engine.GetActivityAttrText(itemtype,itemkey,actid, 'APPLNAME');
203 prog_name := wf_engine.GetActivityAttrText(itemtype,itemkey,actid, 'PROGRAM');
204 arg_count := wf_engine.GetActivityAttrNumber(itemtype,itemkey,actid, 'NUMBEROFARGS');
205
206 if (appl_name is null)
207 or (prog_name is null)
208 or (arg_count is null) then
209 Wf_Core.Raise('WFSQL_ARGS');
210 end if;
211
212 -- assign all 100 arguments for concurrent program
213 i:=0;
214 for i in 1..arg_count loop
215 aname := 'ARG'||to_char(i);
216 conc_arg(i) := wf_engine.GetActivityAttrText(itemtype,itemkey,actid,aname);
217 end loop;
218
219 -- if not all args used then set the last arg to chr(0)
220 -- and all after it to null.
221 if arg_count < 100 then
222 i := arg_count+1;
223 conc_arg(i) := chr(0);
224 i := i+1;
225 while i <= 100 loop
226 aname := 'ARG'||to_char(i);
227 conc_arg(i) := null;
228 i := i+1;
229 end loop;
230 end if;
231
232
233 -- submit the request
234 req_id := fnd_request.submit_request(appl_name,
235 prog_name,
236 null,
237 null,
238 false,
239 conc_arg(1), conc_arg(2), conc_arg(3), conc_arg(4), conc_arg(5),
240 conc_arg(6), conc_arg(7), conc_arg(8), conc_arg(9), conc_arg(10),
241 conc_arg(11),conc_arg(12),conc_arg(13),conc_arg(14),conc_arg(15),
242 conc_arg(16),conc_arg(17),conc_arg(18),conc_arg(19),conc_arg(20),
243 conc_arg(21),conc_arg(22),conc_arg(23),conc_arg(24),conc_arg(25),
244 conc_arg(26),conc_arg(27),conc_arg(28),conc_arg(29),conc_arg(30),
245 conc_arg(31),conc_arg(32),conc_arg(33),conc_arg(34),conc_arg(35),
246 conc_arg(36),conc_arg(37),conc_arg(38),conc_arg(39),conc_arg(40),
247 conc_arg(41),conc_arg(42),conc_arg(43),conc_arg(44),conc_arg(45),
248 conc_arg(46),conc_arg(47),conc_arg(48),conc_arg(49),conc_arg(50),
249 conc_arg(51),conc_arg(52),conc_arg(53),conc_arg(54),conc_arg(55),
250 conc_arg(56),conc_arg(57),conc_arg(58),conc_arg(59),conc_arg(60),
251 conc_arg(61),conc_arg(62),conc_arg(63),conc_arg(64),conc_arg(65),
252 conc_arg(66),conc_arg(67),conc_arg(68),conc_arg(69),conc_arg(70),
253 conc_arg(71),conc_arg(72),conc_arg(73),conc_arg(74),conc_arg(75),
254 conc_arg(76),conc_arg(77),conc_arg(78),conc_arg(79),conc_arg(80),
255 conc_arg(81),conc_arg(82),conc_arg(83),conc_arg(84),conc_arg(85),
256 conc_arg(86),conc_arg(87),conc_arg(88),conc_arg(89),conc_arg(90),
257 conc_arg(91),conc_arg(92),conc_arg(93),conc_arg(94),conc_arg(95),
258 conc_arg(96),conc_arg(97),conc_arg(98),conc_arg(99),conc_arg(100));
259
260 if (req_id <= 0 or req_id is null) then
261 raise submission_error;
262 end if;
263
264
265 -- update the item type, if it exists, with the req_id
266 aname := wf_engine.GetActivityAttrText(itemtype,itemkey,actid, 'REQIDNAME');
267 if aname is not null then
268 begin
269 Wf_Engine.SetItemAttrNumber(itemtype, itemkey, aname, req_id);
270
271 exception when others then
272 -- if item attr doesnt exist then create it now
273 if ( wf_core.error_name = 'WFENG_ITEM_ATTR' ) then
274 wf_engine.AddItemAttr(itemtype, itemkey, aname);
275 Wf_Engine.SetItemAttrNumber(itemtype, itemkey, aname, req_id);
276 else
277 raise;
278 end if;
279 end;
280 end if;
281
282 exception
283 when submission_error then
284 fnd_message.retrieve(msg);
285 Wf_Core.Context('FND_WF_STANDARD', 'Submit_CP', itemtype, itemkey,
286 appl_name||':'||prog_name, msg);
287 raise;
288 when others then
289 Wf_Core.Context('FND_WF_STANDARD', 'Submit_CP', itemtype, itemkey,
290 appl_name||':'||prog_name);
291 raise;
292 END Submit_CP;
293
294 -------------------------------------------------------------------
295 -- Name: Seed_CB (PRIVATE)
296 -- Description: performs the actual submit routine.
297 -------------------------------------------------------------------
298
299 --Name: Seed_CB (PRIVATE)
300 -- This seeds the callback for a the concurrent program
301
302 Function Seed_CB(itemtype in varchar2,
303 itemkey in varchar2,
304 actid in number,
305 req_id in number) RETURN number
306 is
307 result number;
308 BEGIN
309
310 -- seed the callback for success
311 result := fnd_conc_pp.assign(application =>'FND' ,
312 executable_name => 'FND_WFCALLBACK',
313 req_id => req_id,
314 s_flag => 'Y',
315 w_flag => 'N',
316 f_flag => 'N',
317 Arg1 => itemtype||':'||itemkey,
318 arg2 => to_char(actid),
319 arg3 => 'S', arg4 => null, arg5 => null, arg6 => null,
320 arg7 => null, arg8 => null, arg9 => null, arg10 => null);
321
322 -- seed the callback for warning
323 result := fnd_conc_pp.assign(application =>'FND' ,
324 executable_name => 'FND_WFCALLBACK',
325 req_id => req_id,
326 s_flag => 'N',
327 w_flag => 'Y',
328 f_flag => 'N',
329 Arg1 => itemtype||':'||itemkey,
330 arg2 => to_char(actid),
331 arg3 => 'W', arg4 => null, arg5 => null, arg6 => null,
332 arg7 => null, arg8 => null, arg9 => null, arg10 => null);
333
334 -- seed the callback for failure
335 result := fnd_conc_pp.assign(application =>'FND' ,
336 executable_name => 'FND_WFCALLBACK',
337 req_id => req_id,
338 s_flag => 'N',
339 w_flag => 'N',
340 f_flag => 'Y',
341 Arg1 => itemtype||':'||itemkey,
342 arg2 => to_char(actid),
343 arg3 => 'F', arg4 => null, arg5 => null, arg6 => null,
344 arg7 => null, arg8 => null, arg9 => null, arg10 => null);
345
346 if result < 0 then
347 Wf_Core.Raise('WF_CONC_PP_SUBMIT');
348 end if;
349
350
351 return(result);
352
353 exception
354 when others then
355 Wf_Core.Context('FND_WF_STANDARD', 'Seed_CB', itemtype, itemkey,
356 to_char(req_id));
357 raise;
358 end Seed_CB;
359
360
361 ------------------------------------------------------------------
362 -- Name: CALLBACK
363 -- Parameters:
364 -- errbuff - standard error buffer required for conc mgr submission
365 -- retcode - standard return code required for conc mgr submission
366 -- step - handle to cocnurrent program that seeded the call
367 -- Notes:
368 -- this is called by the concurrent program's post-processsor
369 --
370 -- It executes the callback function to Oracle Workflow and
371 -- re-initiates the flow after a call to the conc-manager
372 --
373 -- It MUST be regestered as a concurrent program called WFCALLBACK
374 -- in the AOL application.
375 -------------------------------------------------------------------
376 Procedure CALLBACK (errbuff out nocopy varchar2,
377 retcode out nocopy varchar2,
378 step in number ) is
379
380 args varchar2(255);
381 request_id number;
382 itemtype varchar2(8);
383 itemkey varchar2(240);
384 actid number;
385 result varchar2(30);
386
387 firstcolon number;
388 rid number;
389
390 rslt number;
391 sname varchar2(50);
392 ename varchar2(30);
393 sflag varchar2(1);
394 wflag varchar2(1);
395 fflag varchar2(1);
396 arg2 varchar2(255);
397 arg3 varchar2(255);
398 arg4 varchar2(255);
399 arg5 varchar2(255);
400 arg6 varchar2(255);
401 arg7 varchar2(255);
402 arg8 varchar2(255);
403 arg9 varchar2(255);
404 arg10 varchar2(255);
405
406 phase varchar2(80);
407 stat varchar2(80);
408 devphase varchar2(20);
409 msg varchar2(2000);
410 req_stat boolean;
411 error_text varchar2(2000);
412
413 begin
414
415 -- NOTE: this is executed by the concurrent manager when the initial request is COMPLETED
416 -- from inside conc manager, get the request_id
417 FND_PROFILE.get('CONC_REQUEST_ID',request_id);
418
419 --retrieve the argument list.
420 rslt:=fnd_conc_pp.retrieve(req_id => request_id,
421 step => callback.step,
422 app_short_name =>sname,
423 exec_name=>ename,
424 s_flag => sflag,
425 w_flag => wflag,
426 f_flag => fflag,
427 arg1 => args,
428 arg2 => arg2, arg3 => arg3, arg4 => arg4, arg5 => arg5, arg6 => arg6,
429 arg7 => arg7, arg8 => arg8, arg9 => arg9, arg10 => arg10);
430
431
432 -- args has format itemtype:itemkey:actid
433 firstcolon := instr(args,':');
434 itemtype := substr(args,1, firstcolon-1);
435 itemkey := substr(args,firstcolon+1);
436 actid := to_number(arg2);
437
438 begin
439 if arg3 = 'S' then
440 result:= 'NORMAL';
441 elsif arg3 = 'F' then
442 result:= 'ERROR';
443 elsif arg3 = 'W' then
444 result:= 'WARNING';
445 end if;
446
447 savepoint wf_savepoint;
448 --complete activity inline. If user wants to defer the thread, they
449 --should set cost above theshold.
450 --wf_engine.threshold := -1;
451 Wf_Engine_Util.Complete_Activity(itemtype, itemkey, actid, result, FALSE);
452 exception
453 when others then
454 -- If anything in this process raises an exception:
455 -- 1. rollback any work in this process thread
456 -- 2. set this activity to error status
457 -- 3. execute the error process (if any)
458 -- 4. clear the error to continue with next activity
459 rollback to wf_savepoint;
460 Wf_Core.Context('Fnd_Wf_Standard', 'Callback', itemtype,
461 itemkey, actid, result);
462 Wf_Item_Activity_Status.Set_Error(itemtype,
463 itemkey, actid, wf_engine.eng_exception, FALSE);
464 Wf_Engine_Util.Execute_Error_Process(itemtype,
465 itemkey, actid, wf_engine.eng_exception);
466 Wf_Core.Clear;
467 end;
468
469
470 commit;
471 exception
472 when others then
473 Wf_Core.Context('FND_WF_STANDARD', 'Callback', itemtype, itemkey);
474 raise;
475
476
477 END callback;
478
479 END FND_WF_STANDARD;