1 PACKAGE BODY fnd_flex_workflow AS
2 /* $Header: AFFFWKFB.pls 120.1.12010000.1 2008/07/25 14:15:06 appldev ship $ */
3
4 -- ==================================================
5 -- CACHING
6 -- ==================================================
7 g_kffcache_item_type VARCHAR2(100);
8 g_kffcache_item_key VARCHAR2(100);
9 g_kffcache_key_flex key_flex_type;
10
11 g_cache_return_code VARCHAR2(30);
12 g_cache_key VARCHAR2(2000);
13 g_cache_value fnd_plsql_cache.generic_cache_value_type;
14
15 -- --------------------------------------------------
16 -- sgc : Segment count cache
17 -- --------------------------------------------------
18 sgc_cache_controller fnd_plsql_cache.cache_1to1_controller_type;
19 sgc_cache_storage fnd_plsql_cache.generic_cache_values_type;
20
21 -- --------------------------------------------------
22 -- wfp : Workflow Process Name cache.
23 -- --------------------------------------------------
24 wfp_cache_controller fnd_plsql_cache.cache_1to1_controller_type;
25 wfp_cache_storage fnd_plsql_cache.generic_cache_values_type;
26
27 -- --------------------------------------------------
28 -- aid : Application id cache.
29 -- --------------------------------------------------
30 aid_cache_controller fnd_plsql_cache.cache_1to1_controller_type;
31 aid_cache_storage fnd_plsql_cache.generic_cache_values_type;
32
33 --
34 -- Global variables
35 --
36 g_debug_fnd_flex_workflow BOOLEAN := FALSE;
37 g_chr_newline VARCHAR2(8);
38 g_wf_not_completed VARCHAR2(100) := 'FLEX_WF_NOT_COMPLETED';
39
40 -- ======================================================================
41 -- DEBUG
42 -- ======================================================================
43 PROCEDURE dbms_debug(p_debug IN VARCHAR2)
44 IS
45 i INTEGER;
46 m INTEGER;
47 c INTEGER := 80; -- line size
48 BEGIN
49 m := Ceil(Length(p_debug)/c); -- number of lines
50 FOR i IN 1..m LOOP
51 execute immediate ('begin dbms' ||
52 '_output' ||
53 '.put_line(''' ||
54 REPLACE(Substr(p_debug, 1+c*(i-1), c), '''', '''''') ||
55 '''); end;');
56 END LOOP;
57 EXCEPTION
58 WHEN OTHERS THEN
59 NULL;
60 END dbms_debug;
61
62 -- ======================================================================
63 PROCEDURE debug(p_debug IN VARCHAR2)
64 IS
65 l_vc2 VARCHAR2(32000) := p_debug || g_chr_newline;
66 l_line_size NUMBER := 75;
67 l_pos NUMBER;
68 BEGIN
69 IF (g_debug_fnd_flex_workflow) THEN
70 WHILE (l_vc2 IS NOT NULL) LOOP
71 l_pos := Instr(l_vc2, g_chr_newline, 1, 1); -- find the first new line
72 IF (l_pos >= l_line_size) THEN
73 l_pos := l_line_size;
74 END IF;
75 dbms_debug(Rtrim(Substr(l_vc2, 1, l_pos), g_chr_newline));
76 l_vc2 := Substr(l_vc2, l_pos + 1);
77 END LOOP;
78 END IF;
79 EXCEPTION
80 WHEN OTHERS THEN
81 NULL;
82 END debug;
83
84 -- ======================================================================
85 PROCEDURE report_wf_error(p_func_name IN VARCHAR2)
86 IS
87 BEGIN
88 IF (g_debug_fnd_flex_workflow) THEN
89 debug('Account Generator failed in ' || p_func_name ||
90 ' with following error.' || g_chr_newline ||
91 'ERROR_NAME : ' || wf_core.error_name || g_chr_newline ||
92 'ERROR_MESSAGE : ' || wf_core.error_message || g_chr_newline ||
93 'ERROR_STACK : ' || wf_core.error_stack || g_chr_newline ||
94 'SQLERRM : ' || Sqlerrm || g_chr_newline ||
95 'DBMS_ERROR_STACK:' || g_chr_newline ||
96 dbms_utility.format_error_stack() || g_chr_newline ||
97 'DBMS_CALL_STACK:' || g_chr_newline ||
98 dbms_utility.format_call_stack());
99 END IF;
100 EXCEPTION
101 WHEN OTHERS THEN
102 NULL;
103 END report_wf_error;
104
105 -- ======================================================================
106 -- bool_to_char
107 --
108 -- A utility function to convert boolean values to char to print in
109 -- debug statements
110 --
111 FUNCTION bool_to_char(value IN BOOLEAN)
112 RETURN VARCHAR2
113 IS
114 BEGIN
115 IF (value) THEN
116 RETURN 'TRUE';
117 ELSIF (NOT value) THEN
118 RETURN 'FALSE';
119 ELSE
120 RETURN 'NULL';
121 END IF;
122 END bool_to_char;
123
124 -- ======================================================================
125 -- Get the current structure specific attributes from WF.
126 --
127 PROCEDURE get_key_flex(p_item_type IN VARCHAR2,
128 p_item_key IN VARCHAR2,
129 px_key_flex IN OUT nocopy key_flex_type)
130 IS
131 BEGIN
132 IF ((g_kffcache_item_type = p_item_type) AND
133 (g_kffcache_item_key = p_item_key)) THEN
134 NULL;
135 ELSE
136 g_kffcache_item_type := p_item_type;
137 g_kffcache_item_key := p_item_key;
138
139 g_kffcache_key_flex.application_id := To_number
140 (wf_engine.GetItemAttrText(p_item_type, p_item_key, 'FND_FLEX_APPLID'));
141
142 g_kffcache_key_flex.application_short_name :=
143 wf_engine.GetItemAttrText(p_item_type, p_item_key, 'FND_FLEX_APPLSNAME');
144
145 g_kffcache_key_flex.id_flex_code :=
146 wf_engine.GetItemAttrText(p_item_type, p_item_key, 'FND_FLEX_CODE');
147
148 g_kffcache_key_flex.id_flex_num := To_number
149 (wf_engine.GetItemAttrText(p_item_type, p_item_key, 'FND_FLEX_NUM'));
150
151 g_kffcache_key_flex.numof_segments := To_number
152 (wf_engine.GetItemAttrText(p_item_type, p_item_key, 'FND_FLEX_NSEGMENTS'));
153 END IF;
154
155 px_key_flex := g_kffcache_key_flex;
156
157 IF (g_debug_fnd_flex_workflow) THEN
158 debug('Account Generator is running for:');
159 debug(' WF ITEM = ' || p_item_type || '/' || p_item_key);
160 debug(' APPLICATION = ' || (To_char(px_key_flex.application_id) || '/' ||
161 px_key_flex.application_short_name));
162 debug(' KEY FLEX = ' || (px_key_flex.id_flex_code || '/' ||
163 To_char(px_key_flex.id_flex_num) || '/' ||
164 px_key_flex.numof_segments));
165 END IF;
166 END get_key_flex;
167
168 -- ======================================================================
169 -- select_process
170 --
171 -- This function selects which process in the given item type
172 -- should be used to generate the combination for the given
173 -- structure. This information is stored in the table
174 -- FND_FLEX_WORKFLOW_PROCESSES
175 --
176 FUNCTION select_process(appl_short_name IN VARCHAR2,
177 code IN VARCHAR2,
178 num IN NUMBER,
179 itemtype IN VARCHAR2)
180 RETURN VARCHAR2
181 IS
182 l_pname VARCHAR2(30);
183 BEGIN
184 g_cache_key := (appl_short_name || '.' || code || '.' ||
185 num || '.' || itemtype);
186 fnd_plsql_cache.generic_1to1_get_value(wfp_cache_controller,
187 wfp_cache_storage,
188 g_cache_key,
189 g_cache_value,
190 g_cache_return_code);
191
192 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
193 l_pname := g_cache_value.varchar2_1;
194 ELSE
195 SELECT
196 wf_process_name
197 INTO l_pname
198 FROM fnd_flex_workflow_processes fwk, fnd_application app
199 WHERE app.application_short_name = appl_short_name
200 AND fwk.application_id = app.application_id
201 AND fwk.id_flex_code = code
202 AND fwk.id_flex_num = num
203 AND fwk.wf_item_type = itemtype;
204
205 fnd_plsql_cache.generic_cache_new_value
206 (x_value => g_cache_value,
207 p_varchar2_1 => l_pname);
208
209 fnd_plsql_cache.generic_1to1_put_value(wfp_cache_controller,
210 wfp_cache_storage,
211 g_cache_key,
212 g_cache_value);
213 END IF;
214 RETURN l_pname;
215 EXCEPTION
216 WHEN OTHERS THEN
217 RETURN 'DEFAULT_ACCOUNT_GENERATION';
218 END select_process;
219
220
221 -- ======================================================================
222 -- get_process_result
223 --
224 -- This procedure returns the status/result of a given process.
225 -- (this will be the value returned
226 -- by the end activity that terminated the top level process.
227 --
228 PROCEDURE get_process_result(p_itemtype IN VARCHAR2,
229 p_itemkey IN VARCHAR2,
230 x_status OUT nocopy VARCHAR2,
231 x_result OUT nocopy VARCHAR2)
232 IS
233 l_actid NUMBER;
234 l_status VARCHAR2(100);
235 l_result VARCHAR2(100);
236 BEGIN
237 --
238 -- Get the result of the last activity executed.
239 -- If the process has completed, this will be the root process.
240 -- Note: to be accurate, this should also check that the actid
241 -- returned really is the root process. If it isn't, the result
242 -- will either be an error or a block activity (null result), so
243 -- it shouldn't really matter.
244 --
245 Wf_Item_Activity_Status.LastResult(p_itemtype, p_itemkey,
246 l_actid, l_status, l_result);
247 --
248 -- Debug
249 --
250 IF (g_debug_fnd_flex_workflow) THEN
251 debug('PROCESS ACTID IS ' || l_actid);
252 debug('PROCESS STATUS IS ' || l_status);
253 debug('PROCESS RESULT IS ' || l_result);
254 END IF;
255
256 x_status := l_status;
257 x_result := l_result;
258
259 EXCEPTION
260 WHEN OTHERS THEN
261 RAISE;
262 END get_process_result;
263
264
265 -- ======================================================================
266 -- INITIALIZE
267 --
268 -- This function generates an item key from the sequence
269 -- FND_FLEX_WORKFLOW_ITEMKEY_S, creates a workflow process
270 -- for the given item, creates/sets values for flexfield
271 -- specific item attributes.
272 --
273 FUNCTION initialize(appl_short_name IN VARCHAR2,
274 code IN VARCHAR2,
275 num IN NUMBER,
276 itemtype IN VARCHAR2)
277 RETURN VARCHAR2
278 IS
279 l_itemkey VARCHAR2(38);
280 l_pname VARCHAR2(30);
281 l_application_id NUMBER;
282 l_nsegments NUMBER;
283 l_profile_debug_mode VARCHAR2(10);
284 BEGIN
285 --
286 -- Debug
287 --
288 IF (g_debug_fnd_flex_workflow) THEN
289 debug('START FND_FLEX_WORKFLOW.INITIALIZE');
290 debug('APPLICATION_SHORT_NAME = ' || appl_short_name);
291 debug('CODE = ' || code);
292 debug('NUM = ' || TO_CHAR(num));
293 debug('ITEMTYPE = ' || itemtype);
294 END IF;
295
296 --
297 -- Get Application ID
298 --
299 g_cache_key := appl_short_name;
300 fnd_plsql_cache.generic_1to1_get_value(aid_cache_controller,
301 aid_cache_storage,
302 g_cache_key,
303 g_cache_value,
304 g_cache_return_code);
305 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
306 l_application_id := g_cache_value.number_1;
307 ELSE
308 SELECT
309 application_id
310 INTO l_application_id
311 FROM fnd_application
312 WHERE application_short_name = appl_short_name;
313
314 fnd_plsql_cache.generic_cache_new_value
315 (x_value => g_cache_value,
316 p_number_1 => l_application_id);
317
318 fnd_plsql_cache.generic_1to1_put_value(aid_cache_controller,
319 aid_cache_storage,
320 g_cache_key,
321 g_cache_value);
322 END IF;
323
324 --
325 -- Get the number of enabled segments for this flexfield structure
326 --
327 g_cache_key := l_application_id || '.' || code || '.' || num;
328 fnd_plsql_cache.generic_1to1_get_value(sgc_cache_controller,
329 sgc_cache_storage,
330 g_cache_key,
331 g_cache_value,
332 g_cache_return_code);
333 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
334 l_nsegments := g_cache_value.number_1;
335 ELSE
336 SELECT
337 COUNT(*)
338 INTO l_nsegments
339 FROM fnd_id_flex_segments
340 WHERE application_id = l_application_id
341 AND id_flex_code = code
342 AND id_flex_num = num
343 AND enabled_flag = 'Y';
344
345 fnd_plsql_cache.generic_cache_new_value
346 (x_value => g_cache_value,
347 p_number_1 => l_nsegments);
348
349 fnd_plsql_cache.generic_1to1_put_value(sgc_cache_controller,
350 sgc_cache_storage,
351 g_cache_key,
352 g_cache_value);
353 END IF;
354
355 --
356 -- Generate a unique itemkey for this process.
357 --
358 --
359 -- Profile option is used to decide whether to create the process in
360 -- synch mode or not.
361 -- In synch mode WF doesn't keep all details about attributes.
362 -- bug735681 and bug742903.
363 --
364 IF (fnd_profile.defined('ACCOUNT_GENERATOR:DEBUG_MODE')) THEN
365 l_profile_debug_mode := fnd_profile.value('ACCOUNT_GENERATOR:DEBUG_MODE');
366 ELSE
367 l_profile_debug_mode := 'N';
368 END IF;
369
370 IF (l_profile_debug_mode = 'Y') THEN
371 SELECT
372 TO_CHAR(FND_FLEX_WORKFLOW_ITEMKEY_S.NEXTVAL)
373 INTO l_itemkey
374 FROM DUAL;
375 ELSE -- Synch Mode, key is '#SYNCH'
376 l_itemkey := wf_engine.eng_synch;
377 END IF;
378
379 --
380 -- Select the process to start for the given structure.
381 --
382 l_pname := fnd_flex_workflow.select_process(appl_short_name, code, num,
383 itemtype);
384
385 --
386 -- Debug
387 --
388 IF (g_debug_fnd_flex_workflow) THEN
389 debug('APPLICATION ID = ' || TO_CHAR(l_application_id));
390 debug('NUMBER OF SEGMENTS = ' || TO_CHAR(l_nsegments));
391 debug('ITEMKEY = ' || l_itemkey);
392 debug('PROCESS = ' || l_pname);
393 END IF;
394
395 --
396 -- Create the workflow process for the given itemtype with the
397 -- generated itemkey and selected process.
398 --
399 wf_engine.CreateProcess(itemtype, l_itemkey, l_pname);
400
401 --
402 -- Create item attributes for the process to store the flexfield
403 -- information.
404 --
405 -- Structure specific: (Set when process is created. initialize())
406 --
407 -- FND_FLEX_APPLSNAME - Flexfield Application Short Name
408 -- FND_FLEX_CODE - Flexfield Code
409 -- FND_FLEX_NUM - Flexfield Structure Number
410 -- FND_FLEX_APPLID - Flexfield Application ID
411 -- FND_FLEX_NSEGMENTS - Number of enabled segments
412 --
413 -- Process Run specific: (Nullify before each run. generate())
414 --
415 -- FND_FLEX_CCID - Code Combination ID
416 -- FND_FLEX_SEGMENTS - Concatenated Segments
417 -- FND_FLEX_DATA - Concatenated IDs
421 -- FND_FLEX_INSERT - Insert new combinations?
418 -- FND_FLEX_DESCRIPTIONS - Concatenated Descriptions
419 -- FND_FLEX_MESSAGE - Error Message
420 -- FND_FLEX_STATUS - Validation Status
422 -- FND_FLEX_NEW - Is this a new code combination?
423 -- FND_FLEX_SEGMENTn - Flexfield Segments
424 --
425 wf_engine.AddItemAttr(itemtype, l_itemkey, 'FND_FLEX_APPLSNAME');
426 wf_engine.AddItemAttr(itemtype, l_itemkey, 'FND_FLEX_CODE');
427 wf_engine.AddItemAttr(itemtype, l_itemkey, 'FND_FLEX_NUM');
428 wf_engine.AddItemAttr(itemtype, l_itemkey, 'FND_FLEX_APPLID');
429 wf_engine.AddItemAttr(itemtype, l_itemkey, 'FND_FLEX_NSEGMENTS');
430
431 wf_engine.AddItemAttr(itemtype, l_itemkey, 'FND_FLEX_CCID');
432 wf_engine.AddItemAttr(itemtype, l_itemkey, 'FND_FLEX_SEGMENTS');
433 wf_engine.AddItemAttr(itemtype, l_itemkey, 'FND_FLEX_DATA');
434 wf_engine.AddItemAttr(itemtype, l_itemkey, 'FND_FLEX_DESCRIPTIONS');
435 wf_engine.AddItemAttr(itemtype, l_itemkey, 'FND_FLEX_MESSAGE');
436 wf_engine.AddItemAttr(itemtype, l_itemkey, 'FND_FLEX_STATUS');
437 wf_engine.AddItemAttr(itemtype, l_itemkey, 'FND_FLEX_INSERT');
438 wf_engine.AddItemAttr(itemtype, l_itemkey, 'FND_FLEX_NEW');
439 FOR i IN 1..l_nsegments LOOP
440 wf_engine.AddItemAttr(itemtype, l_itemkey,
441 'FND_FLEX_SEGMENT' || TO_CHAR(i));
442 END LOOP;
443
444 --
445 -- Set structure specific Item Attributes.
446 --
447 wf_engine.SetItemAttrText(itemtype, l_itemkey,
448 'FND_FLEX_APPLSNAME', appl_short_name);
449 wf_engine.SetItemAttrText(itemtype, l_itemkey,
450 'FND_FLEX_CODE', code);
451 wf_engine.SetItemAttrText(itemtype, l_itemkey,
452 'FND_FLEX_NUM', TO_CHAR(num));
453 wf_engine.SetItemAttrText(itemtype, l_itemkey,
454 'FND_FLEX_APPLID', TO_CHAR(l_application_id));
455 wf_engine.SetItemAttrText(itemtype, l_itemkey,
456 'FND_FLEX_NSEGMENTS', TO_CHAR(l_nsegments));
457 --
458 -- Set the kffcache.
459 --
460 g_kffcache_item_type := itemtype;
461 g_kffcache_item_key := l_itemkey;
462
463 g_kffcache_key_flex.application_id := l_application_id;
464 g_kffcache_key_flex.application_short_name := appl_short_name;
465 g_kffcache_key_flex.id_flex_code := code;
466 g_kffcache_key_flex.id_flex_num := num;
467 g_kffcache_key_flex.numof_segments := l_nsegments;
468
469 --
470 -- Return the itemkey
471 --
472 IF (g_debug_fnd_flex_workflow) THEN
473 debug('END FND_FLEX_WORKFLOW.INITIALIZE');
474 END IF;
475 RETURN l_itemkey;
476
477 EXCEPTION
478 WHEN OTHERS THEN
479 wf_core.context('FND_FLEX_WORKFLOW', 'INITIALIZE',
480 itemtype, l_itemkey);
481 report_wf_error('FND_FLEX_WORKFLOW.INITIALIZE');
482 RAISE;
483 END initialize;
484
485 -- ======================================================================
486 -- GENERATE
487 --
488 -- This function starts the workflow process and retrieves the results.
489 -- If the process ended in a failure mode, or if the flex validation
490 -- showed an invalid status then the error message is also retrieved
491 -- and the function returns a FALSE, otherwise it returns TRUE.
492 --
493 -- This function exists in two forms. In the first form, (which should
494 -- be used when calling from forms client) if new code combinations are
495 -- generated the combination is not inserted into the database. The
496 -- second form of this function allows the developer to specify if new
497 -- code combinations can be inserted (in which case it is the developers
498 -- responsibility to do a commit after this call if such a combination
499 -- was inserted. This form of this function is for calling account
500 -- generator from batch programs).
501 -- NOTE: FAILURE TO DO A COMMIT AFTER INSERTING A NEW CODE COMBINATION
502 -- WILL MAINTAIN A TABLE LOCK ON THE CODE COMBINATIONS TABLE!!!
503 --
504 FUNCTION generate(itemtype IN VARCHAR2,
505 itemkey IN VARCHAR2,
506 ccid IN OUT nocopy NUMBER,
507 concat_segs IN OUT nocopy VARCHAR2,
508 concat_ids IN OUT nocopy VARCHAR2,
509 concat_descrs IN OUT nocopy VARCHAR2,
510 error_message IN OUT nocopy VARCHAR2)
511 RETURN BOOLEAN
512 IS
513 l_insert_if_new BOOLEAN := FALSE;
514 l_new_combination BOOLEAN;
515 BEGIN
516 RETURN generate(itemtype, itemkey, l_insert_if_new, ccid, concat_segs,
517 concat_ids, concat_descrs, error_message,
518 l_new_combination);
519 END generate;
520
521 FUNCTION generate(itemtype IN VARCHAR2,
522 itemkey IN VARCHAR2,
523 insert_if_new IN BOOLEAN,
524 ccid IN OUT nocopy NUMBER,
525 concat_segs IN OUT nocopy VARCHAR2,
526 concat_ids IN OUT nocopy VARCHAR2,
527 concat_descrs IN OUT nocopy VARCHAR2,
528 error_message IN OUT nocopy VARCHAR2,
529 new_combination IN OUT nocopy BOOLEAN) RETURN BOOLEAN
530 IS
531 l_process_status VARCHAR2(100);
532 l_process_result VARCHAR2(100);
533 l_validation_status VARCHAR2(30);
534 l_wf_success BOOLEAN := FALSE;
535 l_nsegments NUMBER;
536 BEGIN
537 --
538 -- Debug
539 --
540 IF (g_debug_fnd_flex_workflow) THEN
541 debug('START FND_FLEX_WORKFLOW.GENERATE');
542 debug('ITEMTYPE = ' || itemtype);
543 debug('ITEMKEY = ' || itemkey);
544 debug('INSERT_IF_NEW = ' || bool_to_char(insert_if_new));
545 END IF;
546
547 --
548 -- Reset Process Run specific Item Attributes. (Init them)
549 --
550 wf_engine.SetItemAttrText(itemtype, itemkey, 'FND_FLEX_CCID', NULL);
554 wf_engine.SetItemAttrText(itemtype, itemkey, 'FND_FLEX_MESSAGE', NULL);
551 wf_engine.SetItemAttrText(itemtype, itemkey, 'FND_FLEX_SEGMENTS', NULL);
552 wf_engine.SetItemAttrText(itemtype, itemkey, 'FND_FLEX_DATA', NULL);
553 wf_engine.SetItemAttrText(itemtype, itemkey, 'FND_FLEX_DESCRIPTIONS', NULL);
555 wf_engine.SetItemAttrText(itemtype, itemkey, 'FND_FLEX_STATUS', NULL);
556 IF (insert_if_new) THEN
557 wf_engine.SetItemAttrText(itemtype, itemkey, 'FND_FLEX_INSERT', 'Y');
558 ELSE
559 wf_engine.SetItemAttrText(itemtype, itemkey, 'FND_FLEX_INSERT', 'N');
560 END IF;
561 wf_engine.SetItemAttrText(itemtype, itemkey, 'FND_FLEX_NEW', NULL);
562
563 l_nsegments := To_number
564 (wf_engine.GetItemAttrText(itemtype, itemkey, 'FND_FLEX_NSEGMENTS'));
565
566 FOR i IN 1..l_nsegments LOOP
567 wf_engine.SetItemAttrText(itemtype, itemkey,
568 'FND_FLEX_SEGMENT' || TO_CHAR(i), NULL);
569 END LOOP;
570
571 --
572 -- Start the process.
573 --
574 DECLARE
575 l_threshold NUMBER;
576 BEGIN
577 --
578 -- Set the cost threshold to a high value so that none of the
579 -- functions will ever be run in the background.
580 --
581 l_threshold := wf_engine.threshold;
582 wf_engine.threshold := 999999;
583
584 wf_engine.StartProcess(itemtype, itemkey);
585
586 wf_engine.threshold := l_threshold;
587 EXCEPTION
588 WHEN OTHERS THEN
589 wf_engine.threshold := l_threshold;
590 RAISE;
591 END;
592
593 --
594 -- Get the result of the process.
595 --
596 get_process_result(itemtype, itemkey, l_process_status, l_process_result);
597
598 --
599 -- Check the success first.
600 --
601 IF ((l_process_result = 'SUCCESS') AND
602 (l_process_status = wf_engine.eng_completed)) THEN
603 l_wf_success := TRUE;
604 --
605 -- The process completed successfully. Get the results of the
606 -- flex valiation. Return error message and fail if the
607 -- validation failed.
608 --
609
610 ccid := To_number(wf_engine.GetItemAttrText(itemtype, itemkey,
611 'FND_FLEX_CCID'));
612 concat_segs := wf_engine.GetItemAttrText(itemtype, itemkey,
613 'FND_FLEX_SEGMENTS');
614 concat_ids := wf_engine.GetItemAttrText(itemtype, itemkey,
615 'FND_FLEX_DATA');
616 concat_descrs := wf_engine.GetItemAttrText(itemtype, itemkey,
617 'FND_FLEX_DESCRIPTIONS');
618 l_validation_status := wf_engine.GetItemAttrText(itemtype, itemkey,
619 'FND_FLEX_STATUS');
620 IF (wf_engine.GetItemAttrText(itemtype, itemkey, 'FND_FLEX_NEW')
621 = 'Y') THEN
622 new_combination := TRUE;
623 ELSE
624 new_combination := FALSE;
625 END IF;
626
627 --
628 -- Debug
629 --
630 IF (g_debug_fnd_flex_workflow) THEN
631 debug('CCID IS ' || TO_CHAR(ccid));
632 debug('CONCATENATED SEGMENTS IS ' || concat_segs);
633 debug('CONCATENATED IDS IS ' || concat_ids);
634 debug('CONCATENATED DESCRIPTIONS IS ' || concat_descrs);
635 debug('NEW COMBINATION IS ' || bool_to_char(new_combination));
636 debug('VALIDATION_STATUS IS ' ||
637 Nvl(l_validation_status, 'NULL, set to INVALID.'));
638 END IF;
639
640 --
641 -- Generate should not return NULL validation status.
642 --
643 l_validation_status := Nvl(l_validation_status, 'INVALID');
644 IF (l_validation_status <> 'VALID') THEN
645 error_message := wf_engine.GetItemAttrText(itemtype, itemkey,
646 'FND_FLEX_MESSAGE');
647 --
648 -- Debug
649 --
650 IF (g_debug_fnd_flex_workflow) THEN
651 debug('ERROR MESSAGE IS ' || error_message);
652 END IF;
653 END IF;
654
655 ELSE
656 l_wf_success := FALSE;
657
658 --
659 -- This indicates a fatal error. The code combination generation
660 -- and validation has not been completed successfully.
661 -- Retrieve the error message and set the output values to null
662 --
663 error_message := wf_engine.GetItemAttrText(itemtype, itemkey,
664 'FND_FLEX_MESSAGE');
665 ccid := 0;
666
667 --
668 -- Still return whatever is generated.
669 --
670 concat_segs := wf_engine.GetItemAttrText(itemtype, itemkey,
671 'FND_FLEX_SEGMENTS');
672 concat_ids := wf_engine.GetItemAttrText(itemtype, itemkey,
673 'FND_FLEX_DATA');
674 concat_descrs := wf_engine.GetItemAttrText(itemtype, itemkey,
675 'FND_FLEX_DESCRIPTIONS');
676 new_combination := FALSE;
677
678 --
679 -- Debug
680 --
681 IF (g_debug_fnd_flex_workflow) THEN
682 IF (l_process_status = 'ERROR') THEN
683 debug('ERROR FUNCTION : '|| l_process_result);
684 END IF;
685 debug('ERROR MESSAGE IS ' || error_message);
686 END IF;
687
688 END IF;
689
690 IF (g_debug_fnd_flex_workflow) THEN
691 debug('END FND_FLEX_WORKFLOW.GENERATE');
692 END IF;
693 --
694 -- If the process resulted in FAILURE or the combination is invalid,
695 -- return FALSE, else return TRUE
696 --
697 IF ((l_wf_success) AND
698 (l_validation_status = 'VALID')) THEN
699 return TRUE;
700 ELSE
701 return FALSE;
702 END IF;
703
704 EXCEPTION
705 WHEN OTHERS THEN
706 wf_core.context('FND_FLEX_WORKFLOW', 'GENERATE',
707 itemtype, itemkey);
708 report_wf_error('FND_FLEX_WORKFLOW.GENERATE');
712 END IF;
709
710 IF (error_message IS NULL) THEN
711 error_message := Substr(dbms_utility.format_error_stack(),1,200);
713
714 RETURN FALSE;
715 END generate;
716
717
718 -- ======================================================================
719 -- GENERATE_PARTIAL
720 --
721 -- This is a special case of the GENERATE function. This was
722 -- added for PO to combine mutiple account generation into one item
723 -- type. The runnable processes in this item type have sub-processes
724 -- that generate code combinations separated by block activities.
725 -- This function can be used to start these sub-processes from a given
726 -- block activity. When a block activity is not specified it is started
727 -- from the start activity of the topmost process. When the sub-process
728 -- completes its results are returned similiar to the GENERATE function.
729 -- This function can be called multiple times to complete the
730 -- runnable process. This allows more than one code combination to
731 -- be built from a runnable process.
732 --
733 -- This function exists in two forms. In the first form, (which should
734 -- be used when calling from forms client) if new code combinations are
735 -- generated the combination is not inserted into the database. The
736 -- second form of this function allows the developer to specify if new
737 -- code combinations can be inserted (in which case it is the developers
738 -- responsibility to do a commit after this call if such a combination
739 -- was inserted. This form of this function is for calling account
740 -- generator from batch programs).
741 -- NOTE: FAILURE TO DO A COMMIT AFTER INSERTING A NEW CODE COMBINATION
742 -- WILL MAINTAIN A TABLE LOCK ON THE CODE COMBINATIONS TABLE!!!
743 --
744 FUNCTION generate_partial(itemtype IN VARCHAR2,
745 itemkey IN VARCHAR2,
746 subprocess IN VARCHAR2,
747 block_activity IN VARCHAR2,
748 ccid IN OUT nocopy NUMBER,
749 concat_segs IN OUT nocopy VARCHAR2,
750 concat_ids IN OUT nocopy VARCHAR2,
751 concat_descrs IN OUT nocopy VARCHAR2,
752 error_message IN OUT nocopy VARCHAR2) RETURN BOOLEAN
753 IS
754 l_insert_if_new BOOLEAN := FALSE;
755 l_new_combination BOOLEAN;
756 BEGIN
757 RETURN generate_partial(itemtype, itemkey, subprocess, block_activity,
758 l_insert_if_new, ccid, concat_segs, concat_ids,
759 concat_descrs, error_message, l_new_combination);
760 END generate_partial;
761
762 FUNCTION generate_partial(itemtype IN VARCHAR2,
763 itemkey IN VARCHAR2,
764 subprocess IN VARCHAR2,
765 block_activity IN VARCHAR2,
766 insert_if_new IN BOOLEAN,
767 ccid IN OUT nocopy NUMBER,
768 concat_segs IN OUT nocopy VARCHAR2,
769 concat_ids IN OUT nocopy VARCHAR2,
770 concat_descrs IN OUT nocopy VARCHAR2,
771 error_message IN OUT nocopy VARCHAR2,
772 new_combination IN OUT nocopy BOOLEAN)
773 RETURN BOOLEAN
774 IS
775 l_process_status VARCHAR2(100);
776 l_process_result VARCHAR2(100);
777 l_validation_status VARCHAR2(30);
778 nsegments NUMBER;
779 l_wf_success BOOLEAN := FALSE;
780 BEGIN
781 --
782 -- Debug
783 --
784 IF (g_debug_fnd_flex_workflow) THEN
785 debug('START FND_FLEX_WORKFLOW.GENERATE_PARTIAL');
786 debug('ITEMTYPE = ' || itemtype);
787 debug('ITEMKEY = ' || itemkey);
788 debug('SUBPROCESS = ' || subprocess);
789 debug('BLOCK_ACTIVITY = ' || block_activity);
790 debug('INSERT_IF_NEW = ' || bool_to_char(insert_if_new));
791 END IF;
792
793 --
794 -- Set value for attribute FND_FLEX_INSERT to whether the code
795 -- combination should be inserted into the database if it is
796 -- new.
797 --
798 IF (insert_if_new) THEN
799 wf_engine.SetItemAttrText(itemtype, itemkey,
800 'FND_FLEX_INSERT', 'Y');
801 ELSE
802 wf_engine.SetItemAttrText(itemtype, itemkey,
803 'FND_FLEX_INSERT', 'N');
804 END IF;
805
806 --
807 -- Start the process.
808 --
809 DECLARE
810 l_threshold NUMBER;
811 BEGIN
812 --
813 -- Set the cost threshold to a high value so that none of the
814 -- functions will ever be run in the background.
815 --
816 l_threshold := wf_engine.threshold;
817 wf_engine.threshold := 999999;
818
819 IF (block_activity IS NULL) THEN
820 wf_engine.StartProcess(itemtype, itemkey);
821 ELSE
822 wf_engine.CompleteActivity(itemtype, itemkey, block_activity, '');
823 END IF;
824
825 wf_engine.threshold := l_threshold;
826 EXCEPTION
827 WHEN OTHERS THEN
828 wf_engine.threshold := l_threshold;
829 RAISE;
830 END;
831
832 --
833 -- Get the result of the process.
834 --
835 get_process_result(itemtype, itemkey, l_process_status, l_process_result);
836
837 --
838 -- Check the success first.
839 --
840 IF (((l_process_result = 'SUCCESS') AND
841 (l_process_status = wf_engine.eng_completed)) OR
842 ((l_process_result IS NULL) AND
843 (l_process_status = wf_engine.eng_notified))) THEN
844 l_wf_success := TRUE;
845 --
846 -- The process completed successfully. Get the results of the
847 -- flex valiation. Return error message and fail if the
848 -- validation failed.
849 --
850
851 ccid := TO_NUMBER(wf_engine.GetItemAttrText(itemtype, itemkey,
852 'FND_FLEX_CCID'));
853 concat_segs := wf_engine.GetItemAttrText(itemtype, itemkey,
857 concat_descrs := wf_engine.GetItemAttrText(itemtype, itemkey,
854 'FND_FLEX_SEGMENTS');
855 concat_ids := wf_engine.GetItemAttrText(itemtype, itemkey,
856 'FND_FLEX_DATA');
858 'FND_FLEX_DESCRIPTIONS');
859 l_validation_status := wf_engine.GetItemAttrText(itemtype, itemkey,
860 'FND_FLEX_STATUS');
861 IF (wf_engine.GetItemAttrText(itemtype, itemkey, 'FND_FLEX_NEW')
862 = 'Y') THEN
863 new_combination := TRUE;
864 ELSE
865 new_combination := FALSE;
866 END IF;
867
868 --
869 -- Debug
870 --
871 IF (g_debug_fnd_flex_workflow) THEN
872 debug('CCID IS ' || TO_CHAR(ccid));
873 debug('CONCATENATED SEGMENTS IS ' || concat_segs);
874 debug('CONCATENATED ID IS ' || concat_ids);
875 debug('CONCATENATED DESCRIPTIONS IS ' || concat_descrs);
876 debug('NEW COMBINATION IS ' || bool_to_char(new_combination));
877 debug('VALIDATION_STATUS IS ' ||
878 Nvl(l_validation_status, 'NULL, set to INVALID.'));
879 END IF;
880
881 --
882 -- Generate_partial should not return NULL validation status.
883 --
884 l_validation_status := Nvl(l_validation_status, 'INVALID');
885 IF (l_validation_status <> 'VALID') THEN
886 error_message := wf_engine.GetItemAttrText(itemtype, itemkey,
887 'FND_FLEX_MESSAGE');
888 --
889 -- Debug
890 --
891 IF (g_debug_fnd_flex_workflow) THEN
892 debug('ERROR MESSAGE IS ' || error_message);
893 END IF;
894 END IF;
895
896 ELSE
897 l_wf_success := FALSE;
898 --
899 -- This indicates a fatal error. The code combination generation
900 -- and validation has not been completed successfully.
901 -- Retrieve the error message and set the output values to null
902 --
903 IF (l_process_status = 'ERROR') THEN
904 error_message := wf_engine.GetItemAttrText(itemtype, itemkey,
905 'FND_FLEX_MESSAGE');
906 ELSIF (l_process_result = 'FAILURE') THEN
907 error_message := wf_engine.GetItemAttrText(itemtype, itemkey,
908 'FND_FLEX_MESSAGE');
909 ELSE
910 error_message := 'Error: Process ' || subprocess ||
911 ' not completed';
912 END IF;
913
914 ccid := 0;
915
916 --
917 -- Still return whatever is generated.
918 --
919 concat_segs := wf_engine.GetItemAttrText(itemtype, itemkey,
920 'FND_FLEX_SEGMENTS');
921 concat_ids := wf_engine.GetItemAttrText(itemtype, itemkey,
922 'FND_FLEX_DATA');
923 concat_descrs := wf_engine.GetItemAttrText(itemtype, itemkey,
924 'FND_FLEX_DESCRIPTIONS');
925 new_combination := FALSE;
926
927 --
928 -- Debug
929 --
930 IF (g_debug_fnd_flex_workflow) THEN
931 IF (l_process_status = 'ERROR') THEN
932 debug('ERROR FUNCTION : '|| l_process_result);
933 END IF;
934 debug('ERROR MESSAGE IS ' || error_message);
935 END IF;
936
937 END IF;
938
939 --
940 -- Since this is a partial generate just clear the
941 -- flexfield attributes for the next run
942 --
943 wf_engine.SetItemAttrText(itemtype, itemkey, 'FND_FLEX_CCID', NULL);
944 wf_engine.SetItemAttrText(itemtype, itemkey, 'FND_FLEX_SEGMENTS', NULL);
945 wf_engine.SetItemAttrText(itemtype, itemkey, 'FND_FLEX_DATA', NULL);
946 wf_engine.SetItemAttrText(itemtype, itemkey, 'FND_FLEX_DESCRIPTIONS', NULL);
947 wf_engine.SetItemAttrText(itemtype, itemkey, 'FND_FLEX_MESSAGE', NULL);
948 wf_engine.SetItemAttrText(itemtype, itemkey, 'FND_FLEX_STATUS', NULL);
949 wf_engine.SetItemAttrText(itemtype, itemkey, 'FND_FLEX_INSERT', NULL);
950 wf_engine.SetItemAttrText(itemtype, itemkey, 'FND_FLEX_NEW', NULL);
951 nsegments := TO_NUMBER(wf_engine.GetItemAttrText(itemtype, itemkey,
952 'FND_FLEX_NSEGMENTS'));
953 FOR i IN 1..nsegments LOOP
954 wf_engine.SetItemAttrText(itemtype, itemkey,
955 'FND_FLEX_SEGMENT' || TO_CHAR(i), NULL);
956 END LOOP;
957
958 --
959 -- If the process resulted in FAILURE or the combination is invalid,
960 -- return FALSE, else return TRUE
961 --
962 IF ((l_wf_success) AND
963 (l_validation_status = 'VALID')) THEN
964 return TRUE;
965 ELSE
966 return FALSE;
967 END IF;
968
969 EXCEPTION
970 WHEN OTHERS THEN
971 wf_core.context('FND_FLEX_WORKFLOW', 'GENERATE_PARTIAL',
972 itemtype, itemkey);
973 report_wf_error('FND_FLEX_WORKFLOW.GENERATE_PARTIAL');
974
975 IF (error_message IS NULL) THEN
976 error_message := Substr(dbms_utility.format_error_stack(),1,200);
977 END IF;
978 RETURN FALSE;
979 END generate_partial;
980
981 -- ======================================================================
982 -- LOAD_CONCATENATED_SEGMENTS
983 -- This is a special procedure for loading values returned by the
984 -- flexbuilder upgrade plsql function to the workflow process.
985 -- This function should ONLY be called for this case.
986 --
987
988 PROCEDURE load_concatenated_segments(itemtype IN VARCHAR2,
989 itemkey IN VARCHAR2,
990 concat_segs IN VARCHAR2)
991 IS
992 nsegments NUMBER;
993 delim VARCHAR2(10);
994 segment_array FND_FLEX_EXT.SegmentArray;
995 l_key_flex key_flex_type;
996 BEGIN
997 --
998 -- Debug
999 --
1000 IF (g_debug_fnd_flex_workflow) THEN
1001 debug('START FND_FLEX_WORKFLOW.LOAD_CONCATENATED_SEGMENTS');
1002 debug('CONCATENATED SEGMENTS = ' || concat_segs);
1003 END IF;
1004
1005 --
1006 -- Get the required item attributes
1007 --
1008 get_key_flex(itemtype, itemkey, l_key_flex);
1009
1010 --
1011 -- Use the FND_FLEX_EXT pacakge to break up the concatenated segments
1012 --
1013 delim := fnd_flex_ext.get_delimiter(l_key_flex.application_short_name,
1014 l_key_flex.id_flex_code,
1015 l_key_flex.id_flex_num);
1016
1017 --
1018 -- Debug
1019 --
1020 IF (g_debug_fnd_flex_workflow) THEN
1021 debug('SEGMENT DELIMITER IS ' || delim);
1022 END IF;
1023
1024 nsegments := fnd_flex_ext.breakup_segments(concat_segs, delim,
1025 segment_array);
1026
1027 --
1028 -- Got the values, now assign them to the segment attributes.
1029 --
1030 FOR i IN 1..nsegments LOOP
1031 wf_engine.SetItemAttrText(itemtype, itemkey,
1032 'FND_FLEX_SEGMENT' || TO_CHAR(i),
1033 segment_array(i));
1034 --
1035 -- Debug
1036 --
1037 IF (g_debug_fnd_flex_workflow) THEN
1038 debug('VALUE ASSIGNED TO SEGMENT ' || TO_CHAR(i) || ' IS ' ||
1039 segment_array(i));
1040 END IF;
1041 END LOOP;
1042 END load_concatenated_segments;
1043
1044 -- ======================================================================
1045 -- PURGE
1046 -- Purges the workflow tables of data from a given itemtype, itemkey.
1047 -- This is automatically done within the GENERATE function. This
1048 -- function was added to support GENERATE_PARTIAL.
1049 --
1050 PROCEDURE purge(itemtype IN VARCHAR2,
1051 itemkey IN VARCHAR2)
1052 IS
1053 BEGIN
1054 return;
1055 --
1056 -- Purge profile is obsolete. Synch processes have
1057 -- nothing to purge, and processes being run normally for
1058 -- debugging should not be purged. - sdstratt
1059 --
1060 -- If the profile option ACCOUNT_GENERATOR:PURGE_DATA is set to
1061 -- 'Y', then purge the workflow runtime data.
1062 --
1063 -- IF (fnd_profile.defined('ACCOUNT_GENERATOR:PURGE_DATA')) THEN
1064 -- purge_flag := fnd_profile.value('ACCOUNT_GENERATOR:PURGE_DATA');
1065 -- ELSE
1066 -- purge_flag := 'Y';
1067 -- END IF;
1068 -- IF (purge_flag = 'Y') THEN
1069 -- wf_purge.total(itemtype, itemkey);
1070 -- END IF;
1071 --
1072 EXCEPTION
1073 WHEN OTHERS THEN
1074 wf_core.context('FND_FLEX_WORKFLOW', 'PURGE',
1075 itemtype, itemkey);
1076 report_wf_error('FND_FLEX_WORKFLOW.PURGE');
1077 RAISE;
1078 END purge;
1079
1080 -- ======================================================================
1081 -- Set the debug mode on
1082 --
1083 PROCEDURE debug_on IS
1084 BEGIN
1085 execute immediate ('begin dbms' ||
1086 '_output' ||
1087 '.enable(1000000); end;');
1088 g_debug_fnd_flex_workflow := TRUE;
1089 fnd_flex_workflow_apis.debug_on();
1090 END debug_on;
1091
1092 --
1093 -- Set the debug mode off
1094 --
1095 PROCEDURE debug_off IS
1096 BEGIN
1097 g_debug_fnd_flex_workflow := FALSE;
1098 fnd_flex_workflow_apis.debug_off();
1099 END debug_off;
1100
1101 BEGIN
1102 g_chr_newline := fnd_global.newline;
1103
1104 g_kffcache_item_type := '$FLEX$';
1105 g_kffcache_item_key := '$FLEX$';
1106
1107 fnd_plsql_cache.generic_1to1_init('WKF.SGC',
1108 sgc_cache_controller,
1109 sgc_cache_storage);
1110
1111 fnd_plsql_cache.generic_1to1_init('WKF.WFP',
1112 wfp_cache_controller,
1113 wfp_cache_storage);
1114
1115 fnd_plsql_cache.generic_1to1_init('WKF.AID',
1116 aid_cache_controller,
1117 aid_cache_storage);
1118 END fnd_flex_workflow;
[Home] [Help]