[Home] [Help]
PACKAGE BODY: APPS.OE_VALIDATE_WF
Source
1 PACKAGE BODY OE_VALIDATE_WF AS
2 /* $Header: OEXVVWFB.pls 120.4 2008/02/15 15:03:50 vbkapoor ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_VALIDATE_WF';
6
7 -- Global variables used in the package
8 G_all_activity_tbl OE_VALIDATE_WF.Activities_Tbl_Type;
9 G_loop_tbl NumberTable;
10 G_exit_from_loop VARCHAR2(3) := 'NO';
11 /*----------------------------------------------------------------
12 Function Display_Name
13 A function returning Display name of a process name.
14
15 This program is called by:
16 1. OE_VALIDATE_WF.Validate_Order_Flow() API
17 2. OE_VALIDATE_WF.Validate_Line_Flow() API
18 3. OE_VALIDATE_WF.Out_Transitions() API
19 4. OE_VALIDATE_WF.Check_Sync() API
20 5. OE_VALIDATE_WF.Line_Flow_Assignment() API
21 6. OE_VALIDATE_WF.Wait_And_Loops() API
22 ------------------------------------------------------------------*/
23 FUNCTION Display_Name
24 ( P_process IN OUT NOCOPY VARCHAR2
25 , P_item_type IN VARCHAR2
26 ) RETURN VARCHAR2
27 IS
28
29 -- Local Variable Decleration
30 l_process VARCHAR2(150); -- Bug#4600129
31 l_item_type VARCHAR2(30); -- #4617652
32 l_process_name VARCHAR2(110);
33 l_display_name VARCHAR2(80);
34
35 BEGIN
36 -- Copying passed into locals
37 l_process := P_process;
38 l_item_type := P_item_type;
39
40 BEGIN
41
42 oe_debug_pub.add('Entering Display_Name for process : '||l_process,5);
43
44 SELECT DISTINCT wa.name, wa.display_name
45 INTO l_process_name, l_display_name
46 FROM wf_activities_tl wa
47 WHERE wa.item_type = l_item_type
48 AND wa.name = l_process
49 AND wa.language = userenv('LANG')
50 AND wa.version =
51 ( SELECT MAX(p1.version)
52 FROM wf_activities_tl p1
53 WHERE p1.item_type = wa.item_type
54 AND p1.name = wa.name
55 AND p1.language = wa.language
56 );
57 EXCEPTION
58 WHEN NO_DATA_FOUND THEN
59 oe_debug_pub.add('NO_DATA_FOUND in Display_Name',1);
60 l_process_name := NULL;
61 l_display_name := NULL;
62 END;
63
64 IF l_process_name IS NOT NULL AND l_display_name IS NOT NULL THEN
65 l_process := l_process_name||' ( '||l_display_name||' )';
66 END IF;
67
68 oe_debug_pub.add('Exiting Display_Name for process : '||l_process,5);
69 RETURN l_process;
70
71 EXCEPTION
72 WHEN FND_API.G_EXC_ERROR THEN
73 RAISE FND_API.G_EXC_ERROR;
74 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
75 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
76 WHEN OTHERS THEN
77 oe_debug_pub.add('Error in Display_Name : '||Sqlerrm,5);
78 IF OE_MSG_PUB.CHECK_MSG_LEVEL(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
79 OE_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME, 'Validate');
80 END IF;
81 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
82 END Display_Name;
83
84 /*----------------------------------------------------------------
85 Function In_Loop
86 A function returning Boolean.
87
88 This program is called by:
89 1. OE_VALIDATE_WF.Wait_And_Loops() API
90 ------------------------------------------------------------------*/
91 FUNCTION In_Loop
92 ( activity1 IN NUMBER
93 , activity2 IN NUMBER
94 ) RETURN BOOLEAN
95 IS
96
97 -- Cursor Decleration
98 CURSOR c_process_activity(c_activity_id NUMBER) IS
99 SELECT to_process_activity
100 FROM wf_activity_transitions
101 WHERE from_process_activity = c_activity_id;
102
103 -- Local Variable Decleration
104 l_return_status BOOLEAN := FALSE;
105 l_act_in_table BOOLEAN;
106 l_activity_id1 NUMBER;
107 l_activity_id2 NUMBER;
108
109 BEGIN
110
111 oe_debug_pub.add('Entering Function OE_VALIDATE_WF.In_Loop '||to_char(activity1)||' '||to_char(activity2), 1);
112
113 -- Copying passed into locals
114 l_activity_id1 := activity1;
115 l_activity_id2 := activity2;
116
117 IF l_activity_id1 = l_activity_id2 THEN
118 G_loop_tbl.DELETE;
119 END IF;
120
121 FOR processing_act in c_process_activity(l_activity_id2) LOOP
122 oe_debug_pub.add('processing_act.to_process_activity, activity1 '||to_char(processing_act.to_process_activity) ||' '||to_char(activity1), 5);
123
124 IF processing_act.to_process_activity = l_activity_id1 THEN
125 l_return_status := TRUE;
126 EXIT;
127 ELSE
128 IF G_loop_tbl.COUNT > 0 THEN
129 l_act_in_table := FALSE;
130 FOR i in G_loop_tbl.FIRST .. G_loop_tbl.LAST LOOP
131 IF G_loop_tbl(i) = processing_act.to_process_activity THEN
132 l_act_in_table := TRUE;
133 EXIT;
134 END IF;
135 END LOOP;
136 ELSE
137 l_act_in_table := FALSE;
138 END IF;
139
140 IF NOT l_act_in_table THEN
141 -- processing_act.to_process_activity is encountered
142 -- for the first time
143 G_loop_tbl(G_loop_tbl.count + 1) := processing_act.to_process_activity;
144 l_return_status :=
145 In_Loop
146 ( activity1 => l_activity_id1
147 , activity2 => processing_act.to_process_activity
148 );
149 END IF;
150 END IF; -- processing_act.to_process_activity
151 END LOOP; -- processing_act
152
153 IF l_return_status THEN
154 oe_debug_pub.add('Exiting Function OE_VALIDATE_WF.In_Loop TRUE', 1);
155 ELSE
156 oe_debug_pub.add('Exiting Function OE_VALIDATE_WF.In_Loop FALSE', 1);
157 END IF;
158
159 RETURN l_return_status;
160
161 EXCEPTION
162 WHEN FND_API.G_EXC_ERROR THEN
163 RAISE FND_API.G_EXC_ERROR;
164 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
166 WHEN OTHERS THEN
167 oe_debug_pub.add('Error in In_Loop : '||Sqlerrm,5);
168 IF OE_MSG_PUB.CHECK_MSG_LEVEL(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
169 OE_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME, 'Validate');
170 END IF;
171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
172
173 END In_Loop;
174
175 /*----------------------------------------------------------------
176 Function Has_Activity
177 Determines whether a workflow process contains a particular
178 activity (or subprocess) at any level. A function returning
179 Boolean.
180
181 This program is called by:
182 1. OE_VALIDATE_WF.Validate_Order_Flow() API
183 2. OE_VALIDATE_WF.Validate_Line_Flow() API
184 ------------------------------------------------------------------*/
185 FUNCTION Has_Activity
186 ( P_process IN VARCHAR2
187 , P_process_item_type IN VARCHAR2
188 , P_activity IN VARCHAR2
189 , P_activity_item_type IN VARCHAR2
190 ) RETURN BOOLEAN
191 IS
192 -- Cursor Decleration
193 CURSOR c_direct( l_process_item_type VARCHAR2
194 , l_process VARCHAR2
195 , l_activity_item_type VARCHAR2
196 , l_activity VARCHAR2) IS
197 SELECT p.instance_id
198 FROM wf_process_activities p
199 WHERE p.process_item_type = l_process_item_type
200 AND p.process_name = l_process
201 AND p.activity_item_type = l_activity_item_type
202 AND p.activity_name = l_activity
203 AND p.process_version =
204 ( SELECT MAX(p1.process_version)
205 FROM wf_process_activities p1
206 WHERE p1.process_item_type = p.process_item_type
207 AND p1.process_name = p.process_name
208 );
209
210 CURSOR c_recursion( l_process_item_type VARCHAR2
211 , l_process VARCHAR2) IS
212 SELECT p.activity_name
213 , p.activity_item_type
214 FROM wf_process_activities p
215 , wf_activities wa
216 WHERE p.process_item_type = l_process_item_type
217 AND p.process_name = l_process
218 AND p.activity_item_type = wa.item_type
219 AND p.activity_name = wa.name
220 AND wa.type = 'PROCESS'
221 AND p.process_version =
222 ( SELECT MAX(p1.process_version)
223 FROM wf_process_activities p1
224 WHERE p1.process_item_type = p.process_item_type
225 AND p1.process_name = p.process_name
226 )
227 AND wa.version = ( SELECT MAX(wa1.version)
228 FROM wf_activities wa1
229 WHERE wa1.item_type = wa.item_type
230 AND wa1.name = wa.name
231 );
232
233 -- Local Variable Decleration
234 l_recursion_rec c_recursion%ROWTYPE;
235 l_process VARCHAR2(30);
236 l_process_item_type VARCHAR2(8);
237 l_activity VARCHAR2(200);
238 l_activity_item_type VARCHAR2(8);
239 l_instance_id NUMBER;
240 l_return_status BOOLEAN;
241 l_start_time NUMBER;
242 l_end_time NUMBER;
243
244 BEGIN
245 oe_debug_pub.add('Entering Function OE_VALIDATE_WF.Has_Activity', 1);
246 -- l_start_time := dbms_utility.get_time;
247
248 -- Copying passed into locals
249 l_process := P_process;
250 l_process_item_type := P_process_item_type;
251 l_activity := P_activity;
252 l_activity_item_type := P_activity_item_type;
253
254 oe_debug_pub.add('In H.A. Process '||l_process, 5);
255 oe_debug_pub.add('In H.A. Activity '||l_activity, 5);
256
257 OPEN c_direct( l_process_item_type, l_process
258 , l_activity_item_type, l_activity) ;
259 FETCH c_direct INTO l_instance_id;
260
261 IF c_direct%FOUND THEN
262 CLOSE c_direct;
263 oe_debug_pub.add('Return Status TRUE', 1);
264 oe_debug_pub.add('Exiting Function OE_VALIDATE_WF.Has_Activity', 1);
265 -- l_end_time := dbms_utility.get_time;
266 -- oe_debug_pub.add(' Time taken = '||l_end_time- l_start_time);
267 RETURN TRUE;
268 ELSE
269 CLOSE c_direct;
270 END IF;
271
272 l_return_status := FALSE;
273
274 FOR l_recursion_rec in c_recursion( l_process_item_type
275 , l_process) LOOP
276 IF HAS_ACTIVITY
277 ( P_process => l_recursion_rec.activity_name
278 , P_process_item_type => l_recursion_rec.activity_item_type
279 , P_activity => l_activity -- act
280 , P_activity_item_type => l_activity_item_type -- acttype
281 ) THEN
282
283 l_return_status := TRUE;
284 EXIT;
285 END IF;
286 END LOOP;
287
288 oe_debug_pub.add('Exiting Function OE_VALIDATE_WF.Has_Activity', 1);
289 -- l_end_time := dbms_utility.get_time;
290 -- oe_debug_pub.add(' Time taken = '||l_end_time- l_start_time);
291 RETURN l_return_status;
292
293 EXCEPTION
294 WHEN FND_API.G_EXC_ERROR THEN
295 RAISE FND_API.G_EXC_ERROR;
296 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
298 WHEN OTHERS THEN
299 oe_debug_pub.add('Error in Has_Activity : '||Sqlerrm,5);
300 IF OE_MSG_PUB.CHECK_MSG_LEVEL(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
301 OE_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME, 'Validate');
302 END IF;
303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
304
305 END Has_Activity;
306
307 /*----------------------------------------------------------------
308 Procedure Get_Activities
309 Determines all the activities or a particular activity in a
310 workflow process (and subprocess) at any level.
311
312 This program is called by:
313 1. OE_VALIDATE_WF.Check_Sync() API
314 2. OE_VALIDATE_WF.Out_Transitions() API
315 3. OE_VALIDATE_WF.Validate_Line_Flow() API
316 4. OE_VALIDATE_WF.Validate_Order_Flow() API
317 ------------------------------------------------------------------*/
318 PROCEDURE Get_Activities
319 ( P_process IN VARCHAR2
320 , P_process_item_type IN VARCHAR2
321 , P_instance_label IN VARCHAR2 DEFAULT NULL
322 , P_activity_item_type IN VARCHAR2 DEFAULT NULL
323 )
324 IS
325 -- Cursor Decleration
326 CURSOR c_direct_act1( l_process_item_type VARCHAR2
327 , l_process VARCHAR2
328 , l_activity_item_type VARCHAR2
329 , l_instance_label VARCHAR2
330 , l_process_act_version NUMBER) IS
331 SELECT p.activity_name
332 , p.process_name
333 , p.activity_item_type
334 , p.instance_id
335 , w.type
336 , w.function
337 , p.instance_label
338 , p.start_end
339 FROM wf_process_activities p , wf_activities w
340 WHERE p.process_item_type = l_process_item_type
341 AND p.process_name = l_process
342 AND p.activity_item_type = w.item_type
343 AND p.activity_name = w.name
344 AND p.activity_item_type = l_activity_item_type
345 AND p.activity_name = l_instance_label
346 AND p.process_version = l_process_act_version
347 AND SYSDATE >= w.begin_date
348 AND SYSDATE <= nvl(w.end_date, SYSDATE);
349
350 CURSOR c_direct_act2( l_process_item_type VARCHAR2
351 , l_process VARCHAR2
352 , l_process_act_version NUMBER) IS
353 SELECT p.activity_name
354 , p.process_name
355 , p.activity_item_type
356 , p.instance_id
357 , w.type
358 , w.function
359 , p.instance_label
360 , p.start_end
361 FROM wf_process_activities p , wf_activities w
362 WHERE p.process_item_type = l_process_item_type
363 AND p.process_name = l_process
364 AND p.activity_item_type = w.item_type
365 AND p.activity_name = w.name
366 AND p.process_version = l_process_act_version
367 AND SYSDATE >= w.begin_date
368 AND SYSDATE <= nvl(w.end_date, SYSDATE);
369
370 CURSOR c_recursion_act( l_process_item_type VARCHAR2
371 , l_process VARCHAR2
372 , l_process_act_version NUMBER) IS
373 SELECT p.activity_name
374 , p.activity_item_type
375 FROM wf_process_activities p
376 , wf_activities wa
377 WHERE p.process_item_type = l_process_item_type
378 AND p.process_name = l_process
379 AND p.activity_item_type = wa.item_type
380 AND p.activity_name = wa.name
381 AND wa.type = 'PROCESS'
382 AND p.process_version = l_process_act_version
383 AND SYSDATE >= wa.begin_date
384 AND SYSDATE <= nvl(wa.end_date, SYSDATE);
385
386 -- Local Variable Decleration
387 l_recursion_rec c_recursion_act%ROWTYPE;
388 l_process VARCHAR2(30);
389 l_process_item_type VARCHAR2(8);
390 l_instance_label VARCHAR2(200);
391 l_activity_item_type VARCHAR2(8);
392 l_instance_id NUMBER;
393 l_return_status BOOLEAN;
394 l_start_time NUMBER;
395 l_end_time NUMBER;
396 l_process_act_version NUMBER;
397
398 BEGIN
399
400 oe_debug_pub.add('Entering Procedure OE_VALIDATE_WF.Get_Activities', 5);
401 -- l_start_time := dbms_utility.get_time;
402
403 -- Copying passed into locals
404 l_process := P_process;
405 l_process_item_type := P_process_item_type;
406 l_instance_label := P_instance_label;
407 l_activity_item_type := P_activity_item_type;
408
409 oe_debug_pub.add('In G.A. process '||l_process, 5);
410 oe_debug_pub.add('In G.A. Instance_Label '||l_instance_label, 5);
411
412 SELECT MAX(process_version)
413 INTO l_process_act_version
414 FROM wf_process_activities p1
415 WHERE process_item_type = l_process_item_type
416 AND process_name = l_process;
417
418 IF (l_instance_label IS NOT NULL) AND (l_activity_item_type IS NOT NULL) THEN
419
420 OPEN c_direct_act1( l_process_item_type, l_process
421 , l_activity_item_type, l_instance_label, l_process_act_version) ;
422 FETCH c_direct_act1
423 INTO G_all_activity_tbl(G_all_activity_tbl.COUNT + 1);
424 IF c_direct_act1%FOUND THEN
425 G_exit_from_loop := 'YES';
426 -- Setting the variable to not to execute the recursion loop further
427 END IF;
428 CLOSE c_direct_act1;
429
430 ELSIF (l_instance_label IS NULL) AND (l_activity_item_type IS NULL) THEN
431
432 OPEN c_direct_act2( l_process_item_type, l_process, l_process_act_version);
433 LOOP
434 FETCH c_direct_act2
435 INTO G_all_activity_tbl(G_all_activity_tbl.COUNT + 1);
436 EXIT WHEN c_direct_act2%NOTFOUND;
437 END LOOP;
438 CLOSE c_direct_act2;
439
440 END IF;
441
442 IF G_exit_from_loop <> 'YES' THEN
443 FOR l_recursion_rec in c_recursion_act( l_process_item_type, l_process, l_process_act_version) LOOP
444 Get_Activities
445 ( P_process => l_recursion_rec.activity_name
446 , P_process_item_type => l_recursion_rec.activity_item_type
447 , P_instance_label => l_instance_label -- act
448 , P_activity_item_type => l_activity_item_type -- acttype
449 );
450
451 IF G_exit_from_loop = 'YES' THEN
452 oe_debug_pub.add('In G.A. Exiting from sub-recursion',5);
453 EXIT;
454 END IF;
455
456 END LOOP;
457 ELSE
458 oe_debug_pub.add('In G.A. Calling NO recursion',5);
459 END IF;
460 oe_debug_pub.add('Exiting Procedure OE_VALIDATE_WF.Get_Activities', 5);
461 -- l_end_time := dbms_utility.get_time;
462 -- oe_debug_pub.add(' Time taken = '||l_end_time- l_start_time);
463
464 EXCEPTION
465 WHEN FND_API.G_EXC_ERROR THEN
466 RAISE FND_API.G_EXC_ERROR;
467 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
468 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
469 WHEN OTHERS THEN
470 oe_debug_pub.add('Error in Get_Activities : '||Sqlerrm,5);
471 IF OE_MSG_PUB.CHECK_MSG_LEVEL(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
472 OE_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME, 'Validate');
473 END IF;
474 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
475
476 END Get_Activities;
477
478 /*----------------------------------------------------------------
479 Procedure Wait_And_Loops
480
481 This program is called by:
482 1. OE_VALIDATE_WF.Validate_Order_Flow() API
483 2. OE_VALIDATE_WF.Validate_Line_Flow() API
484 ------------------------------------------------------------------*/
485 PROCEDURE Wait_And_Loops
486 ( P_process IN VARCHAR2
487 , P_process_item_type IN VARCHAR2
488 , P_activity_id IN NUMBER
489 , P_activity_label IN VARCHAR2
490 , P_api IN VARCHAR2
491 , X_return_status OUT NOCOPY VARCHAR2
492 )
493 IS
494
495 -- Local Variable Decleration
496 l_api VARCHAR2(100);
497 l_process VARCHAR2(30);
498 l_activity_id NUMBER;
499 l_activity_label VARCHAR2(30);
500 l_process_item_type VARCHAR2(8);
501 l_text_value VARCHAR2(100);
502 l_number_value NUMBER := 0;
503 l_text_value_relative VARCHAR2(100);
504 l_start_time NUMBER;
505 l_end_time NUMBER;
506 l_wait_mode_name VARCHAR2(30);
507 l_mode_name VARCHAR2(30);
508 l_mode_text_value VARCHAR2(100);
509
510 BEGIN
511 oe_debug_pub.add('Entering Procedure OE_VALIDATE_WF.Wait_And_Loops', 1);
512 -- l_start_time := dbms_utility.get_time;
513
514 X_return_status := FND_API.G_RET_STS_SUCCESS;
515
516 -- Copying passed into locals
517 l_api := p_api;
518 l_process := P_process;
519 l_activity_id := P_activity_id;
520 l_activity_label := P_activity_label;
521 l_process_item_type := P_process_item_type;
522
523 IF l_api = 'WF_STANDARD.WAIT' THEN
524
525 -- Getting the attribute Wait Mode of activity p_activity_id
526 -- and the attributes of Wait Mode
527 BEGIN
528 SELECT text_value, name
529 INTO l_text_value, l_wait_mode_name
530 FROM wf_activity_attr_values
531 WHERE process_activity_id = l_activity_id
532 AND name = 'WAIT_MODE';
533 EXCEPTION
534 WHEN NO_DATA_FOUND THEN
535 l_text_value := NULL;
536 l_wait_mode_name := NULL;
537 oe_debug_pub.add('NO_DATA_FOUND in Wait_And_Loops for WAIT_MODE', 1);
538 END;
539
540 IF l_text_value = 'ABSOLUTE' THEN
541 BEGIN
542 SELECT text_value, name
543 INTO l_mode_text_value, l_mode_name
544 FROM wf_activity_attr_values
545 WHERE process_activity_id = l_activity_id
546 AND value_type = 'CONSTANT'
547 AND name = 'WAIT_ABSOLUTE_DATE';
548 EXCEPTION
549 WHEN NO_DATA_FOUND THEN
550 l_mode_text_value := NULL;
551 oe_debug_pub.add('NO_DATA_FOUND in Wait_And_Loops for ABSOLUTE DATE', 1);
552 END;
553
554 ELSIF l_text_value = 'DAY_OF_MONTH' THEN
555 BEGIN
556 SELECT text_value, name
557 INTO l_mode_text_value, l_mode_name
558 FROM wf_activity_attr_values
559 WHERE process_activity_id = l_activity_id
560 AND value_type = 'CONSTANT'
561 AND name = 'WAIT_DAY_OF_MONTH';
562 EXCEPTION
563 WHEN NO_DATA_FOUND THEN
564 l_mode_text_value := NULL;
565 oe_debug_pub.add('NO_DATA_FOUND in Wait_And_Loops for DAY OF MONTH', 1);
566 END;
567
568 ELSIF l_text_value = 'DAY_OF_WEEK' THEN
569 BEGIN
570 SELECT text_value, name
571 INTO l_mode_text_value, l_mode_name
572 FROM wf_activity_attr_values
573 WHERE process_activity_id = l_activity_id
574 AND value_type = 'CONSTANT'
575 AND name = 'WAIT_DAY_OF_WEEK';
576 EXCEPTION
577 WHEN NO_DATA_FOUND THEN
578 l_mode_text_value := NULL;
579 oe_debug_pub.add('NO_DATA_FOUND in Wait_And_Loops for DAY OF WEEK', 1);
580 END;
581
582 ELSIF l_text_value = 'RELATIVE' THEN
583 BEGIN
584 SELECT text_value, name
585 INTO l_mode_text_value, l_mode_name
586 FROM wf_activity_attr_values
587 WHERE process_activity_id = l_activity_id
588 AND value_type = 'CONSTANT'
589 AND name = 'WAIT_RELATIVE_TIME';
590 EXCEPTION
591 WHEN NO_DATA_FOUND THEN
592 l_mode_text_value := NULL;
593 oe_debug_pub.add('NO_DATA_FOUND in Wait_And_Loops for RELATIVE TIME', 1);
594 END;
595
596 ELSIF l_text_value = 'TIME' THEN
597 BEGIN
598 SELECT text_value, name
599 INTO l_mode_text_value, l_mode_name
600 FROM wf_activity_attr_values
601 WHERE process_activity_id = l_activity_id
602 AND value_type = 'CONSTANT'
603 AND name = 'WAIT_TIME';
604 EXCEPTION
605 WHEN NO_DATA_FOUND THEN
606 l_mode_text_value := NULL;
607 oe_debug_pub.add('NO_DATA_FOUND in Wait_And_Loops for WAIT TIME', 1);
608 END;
609 END IF;
610
611 IF l_wait_mode_name = 'WAIT_MODE' AND l_text_value = 'ABSOLUTE' THEN
612 -- Warning the user to check the value for absolute date
613 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_WAIT_ABS');
614 FND_MESSAGE.SET_TOKEN('ACTIVITY',l_activity_label);
615 FND_MESSAGE.SET_TOKEN('PROCESS_NAME',Display_Name(l_process,l_process_item_type));
616 OE_MSG_PUB.Add;
617 oe_debug_pub.add('LOG 1 : Added OE_WFVAL_WAIT_ABS',5);
618 oe_debug_pub.add('Exiting Procedure OE_VALIDATE_WF.Wait_And_Loops', 1);
619 -- l_end_time := dbms_utility.get_time;
620 -- oe_debug_pub.add(' Time taken = '||l_end_time- l_start_time);
621 RETURN;
622
623 ELSIF (l_text_value = 'ABSOLUTE' AND l_mode_text_value > SYSDATE) OR
624 (l_text_value = 'DAY_OF_MONTH' AND l_mode_text_value IS NOT NULL) OR
625 (l_text_value = 'DAY_OF_WEEK' AND l_mode_text_value IS NOT NULL) OR
626 l_text_value NOT IN ('ABSOLUTE', 'DAY_OF_MONTH', 'DAY_OF_WEEK', 'RELATIVE') THEN -- not applicable
627
628 -- Presently it is Not Applicable
629 oe_debug_pub.add('Presently not applicable');
630 oe_debug_pub.add('Exiting Procedure OE_VALIDATE_WF.Wait_And_Loops', 1);
631 -- l_end_time := dbms_utility.get_time;
632 -- oe_debug_pub.add(' Time taken = '||l_end_time- l_start_time);
633 RETURN;
634 END IF;
635 END IF; -- IF l_api
636
637 -- Here, it is assumed that the assigned api is [DEFER] or
638 -- [WAIT WITH RELATIVE TIME].
639
640 oe_debug_pub.add('In W.A.L. : l_text_value '||l_text_value,5);
641 oe_debug_pub.add('In W.A.L. : l_wait_mode_name '||l_wait_mode_name,5);
642
643 IF IN_LOOP
644 ( activity1 => l_activity_id
645 , activity2 => l_activity_id
646 ) THEN
647
648 IF l_api = 'WF_STANDARD.WAIT' THEN
649 BEGIN
650 SELECT text_value
651 INTO l_text_value_relative
652 FROM wf_activity_attr_values
653 WHERE process_activity_id = l_activity_id
654 AND name = 'WAIT_MODE';
655 EXCEPTION
656 WHEN NO_DATA_FOUND THEN
657 l_text_value_relative := NULL;
658 oe_debug_pub.add('NO_DATA_FOUND in Wait_And_Loops for WAIT_MODE with activity_id '||l_activity_id, 1);
659 END;
660
661
662 IF l_text_value_relative = 'RELATIVE' THEN
663 BEGIN
664 SELECT number_value
665 INTO l_number_value
666 FROM wf_activity_attr_values
667 WHERE process_activity_id = l_activity_id
668 AND name = 'WAIT_RELATIVE_TIME';
669 EXCEPTION
670 WHEN NO_DATA_FOUND THEN
671 l_number_value := NULL;
672 oe_debug_pub.add('NO_DATA_FOUND in Wait_And_Loops for RELATIVE TIME with activity_id '||l_activity_id, 1);
673 END;
674 END IF; -- l_text_value_relative
675 END IF; -- l_api
676
677 oe_debug_pub.add('In W.A.L. : l_api '||l_api,5);
678 oe_debug_pub.add('In W.A.L. : l_test_value_relative '||l_text_value_relative,5);
679 oe_debug_pub.add('In W.A.L. : l_number_value '||l_number_value,5);
680 oe_debug_pub.add('In W.A.L. : l_text_value '||l_text_value,5);
681 oe_debug_pub.add('In W.A.L. : l_wait_mode_name '||l_wait_mode_name,5);
682 -- l_api = 'WF_STANDARD.WAIT' AND Relative Time is > 0
683
684 IF (l_api = 'WF_STANDARD.WAIT')
685 AND (l_text_value_relative = 'RELATIVE')
686 AND (l_number_value > 0) THEN
687
688 -- Warning user that wait time should be >= average
689 -- WorkFlow BackGround Engine duration
690 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_LOOP_WNG');
691 FND_MESSAGE.SET_TOKEN('ACTIVITY',l_activity_label);
692 FND_MESSAGE.SET_TOKEN('PROCESS',Display_Name(l_process,l_process_item_type));
693 OE_MSG_PUB.Add;
694 oe_debug_pub.add('LOG 2 : Added OE_WFVAL_LOOP_WNG' ,1);
695
696 ELSE
697
698 -- The same code will get called even if the process is in
699 -- loop with DEFER type having no sigificance for relative
700 -- time.
701 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_LOOP_ERR');
702 FND_MESSAGE.SET_TOKEN('ACTIVITY',l_activity_label);
703 FND_MESSAGE.SET_TOKEN('PROCESS',Display_Name(l_process,l_process_item_type));
704 OE_MSG_PUB.Add;
705 oe_debug_pub.add('LOG 3 : Added OE_WFVAL_LOOP_ERR' ,1);
706 -- Error for WAIT with Relative Time = 0 or DEFER
707 X_return_status := FND_API.G_RET_STS_ERROR;
708
709 END IF; -- l_number_value
710
711 oe_debug_pub.add('In W.A.L. : Not in Loop ',5);
712 END IF; -- in_loop
713
714 oe_debug_pub.add('Exiting Procedure OE_VALIDATE_WF.Wait_And_Loops', 1);
715 -- l_end_time := dbms_utility.get_time;
716 -- oe_debug_pub.add(' Time taken = '||l_end_time- l_start_time);
717
718 EXCEPTION
719 WHEN FND_API.G_EXC_ERROR THEN
720 RAISE FND_API.G_EXC_ERROR;
721 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
722 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
723 WHEN OTHERS THEN
724 oe_debug_pub.add('Error in Wait_And_Loops : '||Sqlerrm,5);
725 IF OE_MSG_PUB.CHECK_MSG_LEVEL(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
726 OE_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME, 'Validate');
727 END IF;
728 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
729
730 END Wait_And_Loops;
731
732 /*----------------------------------------------------------------
733 Procedure Line_Flow_Assignment
734 Checks if a particular seeded workflow is incompatible with the
735 OM item type to which it is assigned. If so, returns error and
736 puts a message on the error stack. Determines if a customized
737 flow might be incompatible with the OM item type to which it is
738 assigned. If yes, returns a warning message and puts it on the
739 message stack.
740
741 This program is called by:
742 1. OE_VALIDATE_WF.Validate() API
743 2. Transaction Types form (OEXDTTYP.fmb, OEXTRTYP.pld)
744 ------------------------------------------------------------------*/
745 PROCEDURE Line_Flow_Assignment
746 ( P_name IN VARCHAR2
747 , P_item_type IN VARCHAR2
748 , X_return_status OUT NOCOPY VARCHAR2
749 , X_msg_count OUT NOCOPY NUMBER
750 )
751 IS
752 -- Local Variable Decleration
753 l_return_status VARCHAR2(1);
754 l_name VARCHAR2(30);
755 l_item_type VARCHAR2(30);
756 l_msg_count NUMBER;
757 l_start_time NUMBER;
758 l_end_time NUMBER;
759
760 BEGIN
761 oe_debug_pub.add('Entering Procedure OE_VALIDATE_WF.Line_Flow_Assignment', 1);
762 --l_start_time := dbms_utility.get_time;
763
764 -- Copying passed into locals
765 l_return_status := FND_API.G_RET_STS_SUCCESS;
766 l_msg_count := 0;
767 l_name := P_name;
768 l_item_type := P_item_type;
769
770 oe_debug_pub.add(' In L.F.A. : l_name: '||l_name,5);
771 oe_debug_pub.add(' In L.F.A. : l_item_type: '||l_item_type,5);
772
773 IF ( l_name = 'R_ATO_ITEM_LINE' )
774 AND ( l_item_type IS NULL OR l_item_type <> 'ATO_ITEM') THEN
775 l_return_status := FND_API.G_RET_STS_ERROR;
776 l_msg_count := l_msg_count + 1;
777
778 ELSIF ( l_name like '%ATO_ITEM%' )
779 AND ( l_item_type IS NULL OR l_item_type <> 'ATO_ITEM') THEN
780 l_msg_count := l_msg_count + 1;
781
782 ELSIF ( l_name = 'R_ATO_MODEL_LINE' )
783 AND ( l_item_type IS NULL OR l_item_type <> 'ATO_MODEL') THEN
784 l_return_status := FND_API.G_RET_STS_ERROR;
785 l_msg_count := l_msg_count + 1;
786
787 ELSIF ( l_name like '%ATO_MODEL%' )
788 AND ( l_item_type IS NULL OR l_item_type <> 'ATO_MODEL') THEN
789 l_msg_count := l_msg_count + 1;
790
791 ELSIF ( l_name = 'R_CONFIGURATION_LINE' )
792 AND ( l_item_type IS NULL
793 OR l_item_type <> 'CONFIGURATION') THEN
794 l_return_status := FND_API.G_RET_STS_ERROR;
795 l_msg_count := l_msg_count + 1;
796
797 ELSIF ( l_name like '%CONFIG%' )
798 AND ( l_item_type IS NULL
799 OR l_item_type <> 'CONFIGURATION') THEN
800 l_msg_count := l_msg_count + 1;
801
802 ELSIF ( l_name = 'R_OTA_LINE' )
803 AND ( l_item_type IS NULL
804 OR l_item_type <> 'EDUCATION_ITEM') THEN
805 l_return_status := FND_API.G_RET_STS_ERROR;
806 l_msg_count := l_msg_count + 1;
807
808 ELSIF ( l_name like '%OTA%' )
809 AND ( l_item_type IS NULL
810 OR l_item_type <> 'EDUCATION_ITEM') THEN
811 l_msg_count := l_msg_count + 1;
812
813 END IF;
814
815 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
816 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_ASSGN_ERR');
817 FND_MESSAGE.SET_TOKEN('PROCESS', Display_Name(l_name,OE_GLOBALS.G_WFI_LIN));
818 FND_MESSAGE.SET_TOKEN( 'ITEM_TYPE', NVL(l_item_type, 'STANDARD'));
819 OE_MSG_PUB.Add;
820 oe_debug_pub.add('LOG 4 : Added OE_WFVAL_ASSGN_ERR' ,1);
821
822 ELSIF l_msg_count > 0 THEN
823 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_ASSGN_WNG');
824 FND_MESSAGE.SET_TOKEN('PROCESS',Display_Name(l_name,OE_GLOBALS.G_WFI_LIN));
825 -- FND_MESSAGE.SET_TOKEN('PROCESS',Display_Name(l_name,l_item_type)); #4617652
826 FND_MESSAGE.SET_TOKEN( 'ITEM_TYPE', NVL(l_item_type, 'STANDARD'));
827 OE_MSG_PUB.Add;
828 oe_debug_pub.add('LOG 5 : Added OE_WFVAL_ASSGN_WNG', 1);
829
830 END IF;
831
832 X_return_status := l_return_status;
833 X_msg_count := l_msg_count;
834 oe_debug_pub.add('Exiting Procedure OE_VALIDATE_WF.Line_Flow_Assignment', 1);
835 -- l_end_time := dbms_utility.get_time;
836 --oe_debug_pub.add(' Time taken = '||l_end_time- l_start_time);
837
838 EXCEPTION
839 WHEN FND_API.G_EXC_ERROR THEN
840 RAISE FND_API.G_EXC_ERROR;
841 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
842 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
843 WHEN OTHERS THEN
844 oe_debug_pub.add('Error in Line_Flow_Assignment : '||Sqlerrm,5);
845 IF OE_MSG_PUB.CHECK_MSG_LEVEL(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
846 OE_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME, 'Validate');
847 END IF;
848 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
849
850 END Line_Flow_Assignment;
851
852
853 /*----------------------------------------------------------------
854 Procedure Check_Sync
855 Checks if continue/wait p_activity in OEOH/OEOL process
856 p_process has a corresponding wait/continue activity in the
857 OEOL/OEOH flow(s) assigned to p_order_type.
858
859 This program is called by:
860 1. OE_VALIDATE_WF.Validate_Order_Flow() API
861 2. OE_VALIDATE_WF.Validate_Line_Flow() API
862 ------------------------------------------------------------------*/
863 PROCEDURE Check_Sync
864 ( P_process IN VARCHAR2
865 , P_process_item_type IN VARCHAR2
866 , P_order_type_id IN NUMBER
867 , P_order_flow IN VARCHAR2 DEFAULT NULL
868 , P_instance_label IN VARCHAR2
869 , P_act_item_type IN VARCHAR2
870 , P_function IN VARCHAR2 --Vaibhav
871 , P_type IN VARCHAR2 --Vaibhav
872 , P_instance_id IN NUMBER --Vaibhav
873 , X_return_status OUT NOCOPY VARCHAR2
874 )
875 IS
876 -- Cursor Decleration
877 CURSOR c_all_line_flows(c_type_id NUMBER) IS
878 SELECT process_name,
879 item_type_code /* Bug # 4908592 */
880 FROM oe_workflow_assignments
881 WHERE order_type_id = c_type_id
882 AND line_type_id IS NOT NULL
883 AND NVL(wf_item_type,'OEOL') = 'OEOL'
884 AND SYSDATE >= start_date_active
885 AND TRUNC(SYSDATE) <= nvl(end_date_active, SYSDATE);
886
887 -- Local Variable Decleration
888 l_process VARCHAR2(30);
889 l_instance_label VARCHAR2(30);
890 l_act_item_type VARCHAR2(8);
891 l_process_item_type VARCHAR2(8);
892 l_order_type_id NUMBER;
893 l_instance NUMBER;
894 l_line_instance NUMBER;
895 l_hdr_instance NUMBER;
896 l_line_process VARCHAR2(30);
897 l_item_type VARCHAR2(30); /* Bug # 4908592 */
898 l_flow_name VARCHAR2(30);
899 l_wait_text_default VARCHAR2(30);
900 l_cont_text_default VARCHAR2(30);
901 l_matching_activity VARCHAR2(30);
902 l_w_c VARCHAR2(30);
903 l_coresp_wait_act VARCHAR2(5);
904 l_coresp_cont_act VARCHAR2(5);
905 l_wait_flow_type VARCHAR2(30);
906 l_coresp_continue_act VARCHAR2(30);
907 l_order_flow VARCHAR2(30);
908 l_activity_name VARCHAR2(30);
909 l_wfval_out_of_sync VARCHAR2(4);
910
911 l_all_activity_tbl OE_VALIDATE_WF.Activities_Tbl_Type;
912 l_line_activity_tbl OE_VALIDATE_WF.Activities_Tbl_Type;
913 l_header_activity_tbl OE_VALIDATE_WF.Activities_Tbl_Type;
914
915 l_start_time NUMBER;
916 l_end_time NUMBER;
917
918 l_function VARCHAR2(240);
919 l_type VARCHAR2(8);
920 l_instance_id NUMBER;
921
922 BEGIN
923 oe_debug_pub.add('Entering Procedure OE_VALIDATE_WF.Check_Sync', 1);
924 -- l_start_time := dbms_utility.get_time;
925
926 X_return_status := FND_API.G_RET_STS_SUCCESS;
927
928 -- Copying passed into locals
929 l_process := P_process;
930 l_instance_label := p_instance_label;
931 l_act_item_type := P_act_item_type;
932 l_order_flow := P_order_flow;
933 l_process_item_type := P_process_item_type;
934 l_order_type_id := P_order_type_id;
935 l_coresp_wait_act := 'NO';
936 l_coresp_cont_act := 'NO';
937 l_wfval_out_of_sync := NULL;
938 l_coresp_continue_act := 'NO';
939 l_function := P_function; --Vaibhav
940 l_type := P_type; --Vaibhav
941 l_instance_id := P_instance_id; --Vaibhav
942 l_matching_activity := NULL;
943
944 IF l_function = 'WF_STANDARD.CONTINUEFLOW' THEN
945
946 BEGIN
947 SELECT text_value
948 INTO l_wait_text_default
949 FROM wf_activity_attr_values waa
950 WHERE waa.name = 'WAITING_ACTIVITY'
951 AND process_activity_id = l_instance_id;
952 -- l_all_activity_tbl(l_instance).instance_id;
953 -- The above sql determines the WAITING_ACTIVITY of the
954 -- passed activity with CONTINUEFLOW function
955 EXCEPTION
956 WHEN NO_DATA_FOUND THEN
957 l_wait_text_default := NULL;
958 oe_debug_pub.add('NO_DATA_FOUND in Check Sync for Waiting Activity',1);
959 NULL;
960 WHEN OTHERS THEN
961 oe_debug_pub.add('Check Sync WAITING ACTIVITY',1);
962 oe_debug_pub.add('Check Sync : Line_process : '||l_process,1);
963 oe_debug_pub.add('Check Sync : Header_process : '||l_order_flow,1);
964 oe_debug_pub.add('Check Sync : Activity Name : '||l_instance_label,1);
965 oe_debug_pub.add('Check Sync : Error : '||sqlerrm,1);
966 NULL;
967 END;
968
969 BEGIN
970 SELECT 'YES'
971 INTO l_coresp_cont_act
972 FROM wf_activities wa
973 WHERE wa.function = 'WF_STANDARD.WAITFORFLOW'
974 AND wa.name = l_wait_text_default
975 AND wa.item_type IN ('OEOH','OEOL')
976 AND wa.version = ( SELECT MAX(version)
977 FROM wf_activities wa1
978 WHERE wa1.item_type = wa.item_type
979 AND wa1.name = wa.name );
980 -- The above sql ensures that the WAITING_ACTIVITY given by
981 -- the previous sql has a function of type WAITFORFLOW
982 EXCEPTION
983 WHEN NO_DATA_FOUND THEN
984 l_coresp_cont_act := 'NO';
985 oe_debug_pub.add('NO_DATA_FOUND in Check Sync for l_coresp_cont_act',1);
986 NULL;
987 WHEN OTHERS THEN
988 oe_debug_pub.add('Check Sync l_coresp_cont_act',1);
989 oe_debug_pub.add('Check Sync : Line_process : '||l_process,1);
990 oe_debug_pub.add('Check Sync : Header_process : '||l_order_flow,1);
991 oe_debug_pub.add('Check Sync : l_wait_text_default : '||l_wait_text_default,1);
992 oe_debug_pub.add('Check Sync : Error : '||sqlerrm,1);
993 NULL;
994 END;
995
996 BEGIN
997 SELECT text_value
998 INTO l_flow_name
999 FROM wf_activity_attr_values waa
1000 WHERE waa.name = 'WAITING_FLOW'
1001 AND process_activity_id = l_instance_id;
1002 -- l_all_activity_tbl(l_instance).instance_id
1003 -- This sql determines the corresponding waiting flow type
1004 -- [MASTER/DETAIL]
1005 EXCEPTION
1006 WHEN NO_DATA_FOUND THEN
1007 l_flow_name := NULL;
1008 oe_debug_pub.add('NO_DATA_FOUND in Check Sync for Waiting Flow',1);
1009 NULL;
1010 WHEN OTHERS THEN
1011 oe_debug_pub.add('Check Sync WAITING_FLOW',1);
1012 oe_debug_pub.add('Check Sync : Line_process : '||l_process,1);
1013 oe_debug_pub.add('Check Sync : Header_process : '||l_order_flow,1);
1014 oe_debug_pub.add('Check Sync : Activity Name : '||l_instance_label,1);
1015 oe_debug_pub.add('Check Sync : Error : '||sqlerrm,1);
1016 NULL;
1017 END;
1018 -- Assigning Act attributes WAITING_ACTIVITY to matching
1019 -- activity. Assigning 'WAIT' to l_w_c, to determine the
1020 -- corresponding activity is WAITFORFLOW
1021 l_matching_activity := l_wait_text_default;
1022 l_w_c := 'WAIT';
1023 -- determines the corresponding activity is WAITFORFLOW
1024 IF l_process_item_type = OE_GLOBALS.G_WFI_HDR THEN
1025 IF (NVL(l_flow_name,'MASTER') <> 'DETAIL') THEN
1026 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_SYNC_DEF');
1027 FND_MESSAGE.SET_TOKEN('ACTIVITY_LABEL', l_instance_label);
1028 FND_MESSAGE.SET_TOKEN('PROCESS_NAME',Display_Name(l_process,l_process_item_type));
1029 OE_MSG_PUB.Add;
1030 oe_debug_pub.add('LOG 6 : Added OE_WFVAL_SYNC_DEF' ,1);
1031 -- incorrect synchronization activity definition
1032 X_return_status := FND_API.G_RET_STS_ERROR;
1033 END IF;
1034
1035 ELSIF l_process_item_type = OE_GLOBALS.G_WFI_LIN THEN
1036 IF (NVL(l_flow_name,'DETAIL') <> 'MASTER') THEN
1037 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_SYNC_DEF');
1038 FND_MESSAGE.SET_TOKEN('ACTIVITY_LABEL', l_instance_label);
1039 --l_all_activity_tbl(l_instance).instance_label
1040 FND_MESSAGE.SET_TOKEN('PROCESS_NAME',Display_Name(l_process,l_process_item_type));
1041 OE_MSG_PUB.Add;
1042 oe_debug_pub.add('LOG 7 : Added OE_WFVAL_SYNC_DEF', 1);
1043 -- Added message OE_WFVAL_SYNC_DEF;
1044 X_return_status := FND_API.G_RET_STS_ERROR;
1045 END IF;
1046 END IF; -- l_process_item_type
1047
1048 ELSIF l_function = 'WF_STANDARD.WAITFORFLOW' THEN
1049 -- l_all_activity_tbl(l_instance).function
1050 BEGIN
1051 SELECT text_value
1052 INTO l_cont_text_default
1053 FROM wf_activity_attr_values waa
1054 WHERE waa.name = 'CONTINUATION_ACTIVITY'
1055 AND process_activity_id = l_instance_id;
1056 -- The above sql determines the CONTINUATION_ACTIVITY of the
1057 -- passed activity with WAITFORFLOW function
1058 EXCEPTION
1059 WHEN NO_DATA_FOUND THEN
1060 l_cont_text_default := NULL;
1061 oe_debug_pub.add('NO_DATA_FOUND in Check Sync for Continue Flow',1);
1062 NULL;
1063 WHEN OTHERS THEN
1064 oe_debug_pub.add('Check Sync CONTINUATION_ACTIVITY ',1);
1065 oe_debug_pub.add('Check Sync : Line_process : '||l_process,1);
1066 oe_debug_pub.add('Check Sync : Header_process : '||l_order_flow,1);
1067 oe_debug_pub.add('Check Sync : Activity Name : '||l_instance_label,1);
1068 oe_debug_pub.add('Check Sync : Error : '||sqlerrm,1);
1069 NULL;
1070 END;
1071
1072 BEGIN
1073 SELECT 'YES'
1074 INTO l_coresp_wait_act
1075 FROM wf_activities wa
1076 WHERE wa.function = 'WF_STANDARD.CONTINUEFLOW'
1077 AND wa.name = l_cont_text_default
1078 AND wa.item_type IN ('OEOH','OEOL')
1079 AND wa.version = ( SELECT MAX(version)
1080 FROM wf_activities wa1
1081 WHERE wa1.item_type = wa.item_type
1082 AND wa1.name = wa.name );
1083 -- The above sql ensures that the CONTINUATION_ACTIVITY
1084 -- given by the previous sql has a function of type CONTINUEFLOW
1085 EXCEPTION
1086 WHEN NO_DATA_FOUND THEN
1087 l_coresp_wait_act := 'NO';
1088 oe_debug_pub.add('NO_DATA_FOUND in Check Sync for l_coresp_wait_act',1);
1089 NULL;
1090 WHEN OTHERS THEN
1091 oe_debug_pub.add('Check Sync l_coresp_wait_act',1);
1092 oe_debug_pub.add('Check Sync : Line_process : '||l_process,1);
1093 oe_debug_pub.add('Check Sync : Header_process : '||l_order_flow,1);
1094 oe_debug_pub.add('Check Sync : l_cont_text_default : '||l_cont_text_default,1);
1095 oe_debug_pub.add('Check Sync : Error : '||sqlerrm,1);
1096 NULL;
1097 END;
1098
1099 BEGIN
1100 SELECT text_value
1101 INTO l_flow_name
1102 FROM wf_activity_attr_values waa
1103 WHERE waa.name = 'CONTINUATION_FLOW'
1104 AND process_activity_id = l_instance_id;
1105 -- This sql determines the corresponding continue flow type
1106 -- [MASTER/DETAIL]
1107 EXCEPTION
1108 WHEN NO_DATA_FOUND THEN
1109 l_flow_name := NULL;
1110 oe_debug_pub.add('NO_DATA_FOUND in Check Sync for Continue Flow',1);
1111 NULL;
1112 WHEN OTHERS THEN
1113 oe_debug_pub.add('Check Sync CONTINUATION_FLOW',1);
1114 oe_debug_pub.add('Check Sync : Line_process : '||l_process,1);
1115 oe_debug_pub.add('Check Sync : Header_process : '||l_order_flow,1);
1116 oe_debug_pub.add('Check Sync : Activity Name : '||l_instance_label,1);
1117 oe_debug_pub.add('Check Sync : Error : '||sqlerrm,1);
1118 NULL;
1119 END;
1120
1121 -- Assigning Act attributes CONTINUATION_ACTIVITY to matching
1122 -- activity. Assigning 'CONTINUE' to l_w_c, to determine the
1123 -- corresponding activity is CONTINUEFLOW
1124 l_matching_activity := l_cont_text_default;
1125 l_w_c := 'CONTINUE';
1126
1127 IF l_process_item_type = OE_GLOBALS.G_WFI_HDR THEN
1128 IF (NVL(l_flow_name,'MASTER') <> 'DETAIL') THEN
1129 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_SYNC_DEF');
1130 FND_MESSAGE.SET_TOKEN('ACTIVITY_LABEL', l_instance_label);
1131 FND_MESSAGE.SET_TOKEN('PROCESS_NAME',Display_Name(l_process,l_process_item_type));
1132 OE_MSG_PUB.Add;
1133 oe_debug_pub.add('LOG 8 : Added OE_WFVAL_SYNC_DEF',1);
1134 X_return_status := FND_API.G_RET_STS_ERROR;
1135 END IF;
1136
1137 ELSIF l_process_item_type = OE_GLOBALS.G_WFI_LIN THEN
1138 IF (NVL(l_flow_name,'DETAIL') <> 'MASTER') THEN
1139 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_SYNC_DEF');
1140 FND_MESSAGE.SET_TOKEN('ACTIVITY_LABEL', l_instance_label);
1141 FND_MESSAGE.SET_TOKEN('PROCESS_NAME',Display_Name(l_process,l_process_item_type));
1142 OE_MSG_PUB.Add;
1143 oe_debug_pub.add('LOG 9 : Added OE_WFVAL_SYNC_DEF' ,1);
1144 X_return_status := FND_API.G_RET_STS_ERROR;
1145 END IF;
1146 END IF; -- l_process_item_type
1147
1148 END IF; -- l_all_activity_tbl(l_instance).API assignment
1149
1150 IF l_matching_activity IS NOT NULL THEN
1151
1152 oe_debug_pub.add('In C.S. Matching Activity is not null',1);
1153 IF l_process_item_type = OE_GLOBALS.G_WFI_HDR THEN
1154 -- Selecting distinct order line workflow processes assigned
1155 -- to p_order_type
1156 OPEN c_all_line_flows(l_order_type_id);
1157 LOOP
1158 FETCH c_all_line_flows
1159 INTO l_line_process,
1160 l_item_type; /* Bug # 4908592 */
1161 EXIT WHEN c_all_line_flows%NOTFOUND;
1162
1163 /* The following IF added for 4908592. Do not enforce OEOH/OEOL sync on booking for configuration items */
1164 IF l_matching_activity = 'BOOK_WAIT_FOR_H' AND
1165 l_item_type = 'CONFIGURATION' THEN
1166 GOTO c_all_line_flows_end;
1167 END IF;
1168
1169 G_all_activity_tbl.DELETE;
1170 G_exit_from_loop := 'NO';
1171
1172 Get_Activities
1173 ( P_process => l_line_process
1174 , P_process_item_type => OE_GLOBALS.G_WFI_LIN
1175 , P_instance_label => l_matching_activity
1176 , P_activity_item_type => OE_GLOBALS.G_WFI_LIN
1177 );
1178
1179 l_line_activity_tbl := G_all_activity_tbl;
1180
1181 oe_debug_pub.add('C.S. Line Act tbl COUNT : '||l_line_activity_tbl.COUNT,1);
1182
1183 IF l_line_activity_tbl.COUNT = 0 THEN
1184 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_SYNC_MISS');
1185 FND_MESSAGE.SET_TOKEN('PROCESS1',Display_Name(l_line_process,OE_GLOBALS.G_WFI_LIN));
1186 FND_MESSAGE.SET_TOKEN('ACTIVITY1',l_matching_activity);
1187 FND_MESSAGE.SET_TOKEN('ACTIVITY2', l_instance_label);
1188 FND_MESSAGE.SET_TOKEN('PROCESS2',Display_Name(l_process,l_process_item_type));
1189 OE_MSG_PUB.Add;
1190 oe_debug_pub.add('Check_Sync : LOG 10i : Added OE_WFVAL_SYNC_MISS',1);
1191 X_return_status := FND_API.G_RET_STS_ERROR;
1192
1193 ELSE
1194 -- Important: If with the above cases, there exists no activity in a
1195 -- line process then the above count=0 can still display the correct
1196 -- message
1197
1198 l_wfval_out_of_sync := NULL;
1199 FOR l_line_instance IN l_line_activity_tbl.FIRST .. l_line_activity_tbl.LAST
1200 LOOP
1201 IF (l_line_activity_tbl(l_line_instance).instance_label
1202 <> l_matching_activity) THEN
1203 IF l_wfval_out_of_sync IS NULL THEN
1204 l_wfval_out_of_sync := 'YES';
1205 END IF;
1206 ELSE
1207 l_wfval_out_of_sync := 'NO';
1208 END IF;
1209 END LOOP;
1210
1211 oe_debug_pub.add('In C.S. l_wfval_out_of_sync is : '||l_wfval_out_of_sync,5);
1212 IF l_wfval_out_of_sync = 'YES' THEN
1213 -- IF NOT Each selected process contains, on some level,
1214 -- instance_label = l_matching_activity THEN
1215 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_SYNC_MISS');
1216 FND_MESSAGE.SET_TOKEN('PROCESS1',Display_Name(l_line_process,OE_GLOBALS.G_WFI_LIN));
1217 FND_MESSAGE.SET_TOKEN('ACTIVITY1',l_matching_activity);
1218 FND_MESSAGE.SET_TOKEN('ACTIVITY2', l_instance_label);
1219 FND_MESSAGE.SET_TOKEN('PROCESS2',Display_Name(l_process,l_process_item_type));
1220 OE_MSG_PUB.Add;
1221 oe_debug_pub.add('LOG 10 : Added OE_WFVAL_SYNC_MISS',1);
1222 -- synchronization activity missing its counterpart
1223 -- activity
1224 X_return_status := FND_API.G_RET_STS_ERROR;
1225 END IF;
1226
1227 -- Important : The above changes are done keeping in mind that for
1228 -- one activity name, we can have many instance labels, with
1229 -- atleast one correct. In such a case even, error message is not
1230 -- liable. Moreover if the COUNT = 0 then it means there is no
1231 -- activity defined, where message is liable.
1232 FOR l_line_instance IN l_line_activity_tbl.FIRST .. l_line_activity_tbl.LAST
1233 LOOP
1234 IF (l_line_activity_tbl(l_line_instance).instance_label
1235 = l_matching_activity) THEN
1236 IF l_w_c = 'CONTINUE' THEN
1237 IF NOT( l_coresp_wait_act = 'YES'
1238 AND l_matching_activity = l_line_activity_tbl(l_line_instance).instance_label)
1239 AND (NVL(l_flow_name,'MASTER') <> 'DETAIL') THEN
1240 -- Matching activity must be assigned API
1241 -- WF_STANDARD.CONTINUEFLOW, matching activity attr
1242 -- WAITING_ACTIVITY must equal to Act.instance_label,
1243 -- and matching activity attribute WAITING_FLOW
1244 -- must be 'MASTER', else it will be an error.
1245
1246 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_SYNC_DEF');
1247 FND_MESSAGE.SET_TOKEN('ACTIVITY_LABEL', l_matching_activity);
1248 FND_MESSAGE.SET_TOKEN('PROCESS_NAME',Display_Name(l_line_process,OE_GLOBALS.G_WFI_LIN));
1249 OE_MSG_PUB.Add;
1250 oe_debug_pub.add('LOG 11 : Added OE_WFVAL_SYNC_DEF',1);
1251 X_return_status := FND_API.G_RET_STS_ERROR;
1252 END IF;
1253
1254 ELSIF l_w_c = 'WAIT' THEN
1255 IF NOT( l_coresp_cont_act = 'YES'
1256 AND l_matching_activity = l_line_activity_tbl(l_line_instance).instance_label)
1257 AND (NVL(l_flow_name,'MASTER') <> 'DETAIL' ) THEN
1258 -- Matching activity must be assigned API
1259 -- WF_STANDARD.WAITFORFLOW, matching activity attr
1260 -- CONTINUATION_FLOW must equal to Act.instance_label,
1261 -- and matching activity attribute CONTINUATION_FLOW
1262 -- must be 'MASTER', else it will be an error.
1263 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_SYNC_DEF');
1264 FND_MESSAGE.SET_TOKEN('ACTIVITY_LABEL', l_matching_activity);
1265 FND_MESSAGE.SET_TOKEN('PROCESS_NAME',Display_Name(l_line_process,OE_GLOBALS.G_WFI_LIN));
1266 OE_MSG_PUB.Add;
1267 oe_debug_pub.add('LOG 12 : Added OE_WFVAL_SYNC_DEF',1 );
1268 X_return_status := FND_API.G_RET_STS_ERROR;
1269 END IF;
1270 END IF; -- l_w_c
1271 END IF; -- l_matching_activity
1272 END LOOP; -- l_line_instance
1273 END IF; -- IF COUNT = 0
1274 <<c_all_line_flows_end>> /* Bug # 4908592 */
1275 NULL; /* Bug # 4908592 */
1276 END LOOP; -- c_all_line_flows
1277 CLOSE c_all_line_flows;
1278
1279 ELSIF l_process_item_type = OE_GLOBALS.G_WFI_LIN THEN
1280 IF l_order_flow IS NOT NULL THEN
1281
1282 G_all_activity_tbl.DELETE;
1283 G_exit_from_loop := 'NO';
1284
1285 Get_Activities
1286 ( P_process => l_order_flow
1287 , P_process_item_type => OE_GLOBALS.G_WFI_HDR
1288 , P_instance_label => l_matching_activity
1289 , P_activity_item_type => OE_GLOBALS.G_WFI_HDR
1290 );
1291
1292 l_header_activity_tbl := G_all_activity_tbl;
1293 oe_debug_pub.add('C.S. Hdr Act tbl COUNT : '||l_header_activity_tbl.COUNT,1);
1294 l_wfval_out_of_sync := NULL;
1295
1296 IF l_header_activity_tbl.COUNT = 0 THEN
1297 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_SYNC_MISS');
1298 FND_MESSAGE.SET_TOKEN('PROCESS1',Display_Name(l_order_flow,OE_GLOBALS.G_WFI_HDR));
1299 FND_MESSAGE.SET_TOKEN('ACTIVITY1',l_matching_activity);
1300 FND_MESSAGE.SET_TOKEN('ACTIVITY2', l_instance_label);
1301 FND_MESSAGE.SET_TOKEN('PROCESS2',Display_Name(l_process,l_process_item_type));
1302 OE_MSG_PUB.Add;
1303 oe_debug_pub.add('Check_Sync : LOG 13i : Added OE_WFVAL_SYNC_MISS',1);
1304 -- synchronization activity missing its counterpart activity;
1305 X_return_status := FND_API.G_RET_STS_ERROR;
1306 ELSIF l_header_activity_tbl.COUNT > 0 THEN
1307
1308 FOR l_hdr_instance IN l_header_activity_tbl.FIRST .. l_header_activity_tbl.LAST
1309 LOOP
1310 IF NOT(l_header_activity_tbl(l_hdr_instance).instance_label
1311 = l_matching_activity) THEN
1312 IF l_wfval_out_of_sync IS NULL THEN
1313 l_wfval_out_of_sync := 'YES';
1314 END IF;
1315 ELSE
1316 l_wfval_out_of_sync := 'NO';
1317 END IF;
1318 END LOOP;
1319 END IF; -- l_header_activity_tbl
1320
1321 oe_debug_pub.add('In C.S. l_wfval_out_of_sync is : '||l_wfval_out_of_sync,5);
1322 IF l_wfval_out_of_sync = 'YES' THEN
1323 -- IF NOT l_order_flow contains instance_label =
1324 -- l_matching_activity THEN
1325 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_SYNC_MISS');
1326 FND_MESSAGE.SET_TOKEN('PROCESS1',Display_Name(l_order_flow,OE_GLOBALS.G_WFI_HDR));
1327 FND_MESSAGE.SET_TOKEN('ACTIVITY1',l_matching_activity);
1328 FND_MESSAGE.SET_TOKEN('ACTIVITY2', l_instance_label);
1329 FND_MESSAGE.SET_TOKEN('PROCESS2',Display_Name(l_process,l_process_item_type));
1330 OE_MSG_PUB.Add;
1331 oe_debug_pub.add('LOG 13 : Added OE_WFVAL_SYNC_MISS' ,1);
1332 -- synchronization activity missing its counterpart activity;
1333 X_return_status := FND_API.G_RET_STS_ERROR;
1334 END IF;
1335
1336 IF l_header_activity_tbl.COUNT > 0 THEN
1337 FOR l_hdr_instance IN l_header_activity_tbl.FIRST .. l_header_activity_tbl.LAST
1338 LOOP
1339 IF (l_header_activity_tbl(l_hdr_instance).instance_label
1340 = l_matching_activity) THEN
1341 IF l_w_c = 'CONTINUE' THEN
1342 IF NOT( l_coresp_wait_act = 'YES'
1343 AND l_matching_activity = l_header_activity_tbl(l_hdr_instance).instance_label)
1344 AND (NVL(l_flow_name,'DETAIL') <> 'MASTER') THEN
1345 -- Matching activity must be assigned API
1346 -- WF_STANDARD.CONTINUEFLOW, matching activity attr
1347 -- WAITING_ACTIVITY must equal to Act.instance_label,
1348 -- and matching activity attribute WAITING_FLOW must
1349 -- be 'DETAIL', else it will be an error.
1350 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_SYNC_DEF');
1351 FND_MESSAGE.SET_TOKEN('ACTIVITY_LABEL',l_matching_activity);
1352 FND_MESSAGE.SET_TOKEN('PROCESS_NAME',Display_Name(l_order_flow,OE_GLOBALS.G_WFI_HDR));
1353 OE_MSG_PUB.Add;
1354 oe_debug_pub.add('LOG 14 : Added OE_WFVAL_SYNC_DEF' ,1);
1355 X_return_status := FND_API.G_RET_STS_ERROR;
1356 END IF;
1357
1358 ELSIF l_w_c = 'WAIT' THEN
1359 IF NOT( l_coresp_cont_act = 'YES'
1360 AND l_matching_activity = l_header_activity_tbl(l_hdr_instance).instance_label)
1361 AND (NVL(l_flow_name,'DETAIL') <> 'MASTER') THEN
1362 -- Matching activity must be assigned API
1363 -- WF_STANDARD.WAITFORFLOW, matching activity attr
1364 -- CONTINUATION_FLOW must equal to Act.instance_label,
1365 -- and matching activity attribute CONTINUATION_FLOW
1366 -- must be 'DETAIL', else it will be an error. */
1367 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_SYNC_DEF');
1368 FND_MESSAGE.SET_TOKEN('ACTIVITY_LABEL', l_matching_activity);
1369 FND_MESSAGE.SET_TOKEN('PROCESS_NAME',Display_Name(l_order_flow,OE_GLOBALS.G_WFI_HDR));
1370 OE_MSG_PUB.Add;
1371 oe_debug_pub.add('LOG 15 : Added OE_WFVAL_SYNC_DEF',1 );
1372 X_return_status := FND_API.G_RET_STS_ERROR;
1373 END IF;
1374 END IF; -- l_w_c
1375
1376 END IF; -- instance_label = l_matching_activity
1377 END LOOP; -- l_hdr_instance
1378 END IF; -- l_header_activity_tbl.COUNT
1379 END IF; -- l_order_flow
1380 END IF; -- l_process_item_type
1381 END IF; -- l_matching_activity
1382
1383 oe_debug_pub.add('Exiting Procedure OE_VALIDATE_WF.Check_Sync', 1);
1384 -- l_end_time := dbms_utility.get_time;
1385 -- oe_debug_pub.add(' Time taken = '||l_end_time- l_start_time);
1386
1387 EXCEPTION
1388 WHEN FND_API.G_EXC_ERROR THEN
1389 RAISE FND_API.G_EXC_ERROR;
1390 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1391 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1392 WHEN OTHERS THEN
1393 oe_debug_pub.add('Error in Check Sync : '||Sqlerrm,5);
1394 IF OE_MSG_PUB.CHECK_MSG_LEVEL(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1395 OE_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME, 'Validate');
1396 END IF;
1397 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1398 END Check_Sync;
1399
1400 /*----------------------------------------------------------------
1401 Procedure Out_Transitions
1402 Looks for any activity or subprocess in process p_name that has
1403 no OUT transition defined. If any are found, error status is
1404 returned and appropriate error messages logged.
1405
1406 This program is called by:
1407 1. OE_VALIDATE_WF.Validate_Order_Flow() API
1408 2. OE_VALIDATE_WF.Validate_Line_Flow() API
1409 ------------------------------------------------------------------*/
1410 PROCEDURE Out_Transitions
1411 ( P_name IN VARCHAR2
1412 , P_type IN VARCHAR2
1413 , X_return_status OUT NOCOPY VARCHAR2
1414 )
1415 IS
1416 -- Local Variable Decleration
1417 l_instance NUMBER;
1418 l_name VARCHAR2(30);
1419 l_type VARCHAR2(8);
1420 l_from_process_activity_exists VARCHAR2(1);
1421 l_all_activity_tbl OE_VALIDATE_WF.Activities_Tbl_Type;
1422 l_start_time NUMBER;
1423 l_end_time NUMBER;
1424 -- l_start_end VARCHAR2(8);
1425
1426 BEGIN
1427 oe_debug_pub.add('Entering Procedure OE_VALIDATE_WF.Out_Transitions', 1);
1428 -- l_start_time := dbms_utility.get_time;
1429 X_return_status := FND_API.G_RET_STS_SUCCESS;
1430
1431 -- Copying passed into locals
1432 l_name := P_name;
1433 l_type := P_type;
1434 l_from_process_activity_exists := 'N';
1435 -- For every activity/subprocesses Act in process p_name
1436 -- FOR all_activities IN c_all_activities(l_name, l_type) LOOP
1437 -- Commented for the performance reasons.
1438 G_all_activity_tbl.DELETE;
1439 G_exit_from_loop := 'NO';
1440
1441 Get_Activities
1442 ( P_process => l_name
1443 , P_process_item_type => l_type
1444 , P_instance_label => NULL
1445 , P_activity_item_type => NULL
1446 );
1447 l_all_activity_tbl := G_all_activity_tbl;
1448
1449 oe_debug_pub.add(' In O.T. l_all_activity_tbl.COUNT is : '||l_all_activity_tbl.COUNT ,5);
1450 IF l_all_activity_tbl.COUNT > 0 THEN
1451 FOR l_instance IN l_all_activity_tbl.FIRST .. l_all_activity_tbl.LAST LOOP
1452 IF NOT (NVL(l_all_activity_tbl(l_instance).start_end, 'N') = WF_ENGINE.ENG_END) THEN
1453 oe_debug_pub.add(' In O.T. Instance_Label is : '||l_all_activity_tbl(l_instance).instance_label,5);
1454 oe_debug_pub.add(' In O.T. Activity start_end : '||l_all_activity_tbl(l_instance).start_end,5);
1455 BEGIN
1456 SELECT 'Y'
1457 INTO l_from_process_activity_exists
1458 FROM wf_activity_transitions
1459 WHERE from_process_activity =
1460 l_all_activity_tbl(l_instance).instance_id
1461 AND ROWNUM = 1;
1462 EXCEPTION
1463 WHEN NO_DATA_FOUND THEN
1464 l_from_process_activity_exists := 'N';
1465 oe_debug_pub.add('O.T. Process Activity do not exists',1);
1466 oe_debug_pub.add('O.T. Activity Name : '||l_all_activity_tbl(l_instance).activity_name,1);
1467 oe_debug_pub.add('O.T. From instance_id : '||l_all_activity_tbl(l_instance).instance_id,1);
1468 NULL;
1469 WHEN OTHERS THEN
1470 oe_debug_pub.add('Error O.T. '||sqlerrm,1);
1471 NULL;
1472 END;
1473
1474 IF (l_from_process_activity_exists = 'N') THEN
1475 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_NO_OUT_TRANS');
1476 FND_MESSAGE.SET_TOKEN('ACTIVITY_LABEL', l_all_activity_tbl(l_instance).instance_label);
1477 FND_MESSAGE.SET_TOKEN('PROCESS_NAME',Display_Name(l_name,l_type));
1478 OE_MSG_PUB.Add;
1479 oe_debug_pub.add('LOG 16 : Added OE_WFVAL_NO_OUT_TRANS',1 );
1480 X_return_status := FND_API.G_RET_STS_ERROR;
1481 END IF;
1482 END IF; -- IF start_end <> WF_ENGINE.ENG_END
1483 END LOOP;
1484 END IF; -- COUNT > 0
1485
1486 oe_debug_pub.add('Exiting Procedure OE_VALIDATE_WF.Out_Transitions', 1);
1487 -- l_end_time := dbms_utility.get_time;
1488 -- oe_debug_pub.add(' Time taken = '||l_end_time- l_start_time);
1489
1490 EXCEPTION
1491 WHEN FND_API.G_EXC_ERROR THEN
1492 RAISE FND_API.G_EXC_ERROR;
1493 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1494 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1495 WHEN OTHERS THEN
1496 oe_debug_pub.add('Error in Out_Transitions : '||Sqlerrm);
1497 IF OE_MSG_PUB.CHECK_MSG_LEVEL(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1498 OE_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME, 'Validate');
1499 END IF;
1500 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1501
1502 END Out_Transitions;
1503
1504 /*----------------------------------------------------------------
1505 Procedure Validate_Line_Flow
1506 << Description >>
1507
1508 This program is called by:
1509 1. OE_VALIDATE_WF.Validate() API
1510 2. Transaction Types form (OEXDTTYP.fmb, OEXTRTYP.pld)
1511 ------------------------------------------------------------------*/
1512 PROCEDURE Validate_Line_Flow
1513 ( P_name IN VARCHAR2
1514 , P_order_flow IN VARCHAR2
1515 , p_quick_val IN BOOLEAN DEFAULT TRUE
1516 , X_return_status OUT NOCOPY VARCHAR2
1517 , X_msg_count OUT NOCOPY NUMBER
1518 , X_msg_data OUT NOCOPY VARCHAR2
1519 , p_item_type IN VARCHAR2 /* Bug # 4908592 */
1520 )
1521 IS
1522 -- Cursor Decleration
1523 CURSOR c_fulfill_attributes(c_instance_id NUMBER) IS
1524 select TEXT_VALUE FulfillAttr
1525 from wf_activity_attr_values
1526 where process_activity_id = c_instance_id
1527 and NAME IN ( 'FULFILLMENT_ACTIVITY'
1528 , 'INBOUND_FULFILLMENT_ACTIVITY');
1529
1530 -- Local Variable Decleration
1531 l_instance NUMBER;
1532 l_all_instance NUMBER;
1533 l_return_status VARCHAR2(1);
1534 l_name VARCHAR2(30);
1535 l_order_flow VARCHAR2(30);
1536 l_msg_data VARCHAR2(2000);
1537 l_errors_only BOOLEAN := TRUE;
1538 l_continue_further BOOLEAN := FALSE;
1539 l_msg_count NUMBER;
1540 l_fulfill_act_exists VARCHAR2(1);
1541 l_type VARCHAR2(8);
1542 l_attr_first_time VARCHAR2(2000);
1543 l_line_process_name VARCHAR2(30);
1544 l_hdr_activity_tbl OE_VALIDATE_WF.Activities_Tbl_Type;
1545 l_all_activity_tbl OE_VALIDATE_WF.Activities_Tbl_Type;
1546 l_start_time NUMBER;
1547 l_end_time NUMBER;
1548 testing_instance NUMBER;
1549 hdr_instance NUMBER;
1550 l_activity VARCHAR2(30);
1551 matching_activity_exists BOOLEAN := FALSE;
1552
1553 BEGIN
1554 oe_debug_pub.add('Entering Procedure OE_VALIDATE_WF.Validate_Line_Flow', 1);
1555 -- l_start_time := dbms_utility.get_time;
1556 X_return_status := FND_API.G_RET_STS_SUCCESS;
1557 l_msg_count := OE_MSG_PUB.count_msg;
1558
1559 -- Copying passed into locals
1560 l_name := P_name;
1561 l_type := OE_GLOBALS.G_WFI_LIN;
1562 l_order_flow := P_order_flow;
1563 l_errors_only := p_quick_val;
1564 l_fulfill_act_exists := 'N';
1565
1566 oe_debug_pub.add('Calling Out Transitions from Validate Line Flow',5);
1567 OE_VALIDATE_WF.OUT_TRANSITIONS
1568 ( P_name => l_name
1569 , P_type => OE_GLOBALS.G_WFI_LIN
1570 , X_return_status => l_return_status
1571 );
1572
1573 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1574 X_return_status := l_return_status;
1575 END IF;
1576 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1577 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1578 END IF;
1579
1580 G_all_activity_tbl.DELETE;
1581 G_exit_from_loop := 'NO';
1582 l_attr_first_time := NULL;
1583
1584 oe_debug_pub.add('Calling Get Activities for Lines from Validate Line Flow',5);
1585 Get_Activities
1586 ( P_process => l_name
1587 , P_process_item_type => l_type
1588 , P_instance_label => NULL
1589 , P_activity_item_type => NULL
1590 );
1591
1592 l_all_activity_tbl := G_all_activity_tbl;
1593 IF l_all_activity_tbl.COUNT > 0 THEN
1594 FOR l_instance IN l_all_activity_tbl.FIRST .. l_all_activity_tbl.LAST LOOP
1595 IF l_all_activity_tbl(l_instance).activity_name = 'FULFILL_LINE' THEN
1596 l_fulfill_act_exists := 'Y';
1597 -- Setting Fulfill activity exists.
1598 FOR fulfill_attributes IN c_fulfill_attributes(l_all_activity_tbl(l_instance).instance_id)
1599 LOOP
1600 -- Getting the value (FulfillAttr) of attributes
1601 -- FULFILLMENT_ACTIVITY and INBOUND_FULFILLMENT_ACTIVITY.
1602 -- AND is selected for the first time - using variable
1603 -- 'l_attr_first_time' and 'l_continue_further'
1604 IF fulfill_attributes.FulfillAttr IS NOT NULL THEN
1605 IF l_attr_first_time IS NULL THEN
1606 l_attr_first_time := ''''||fulfill_attributes.FulfillAttr||'''';
1607 l_continue_further := TRUE;
1608 ELSE -- l_attr_first_time IS NOT NULL
1609 IF fulfill_attributes.FulfillAttr NOT IN (l_attr_first_time) THEN
1610 l_attr_first_time := l_attr_first_time||','||''''||fulfill_attributes.FulfillAttr||'''';
1611 l_continue_further := TRUE;
1612 ELSE
1613 l_continue_further := FALSE;
1614 END IF;
1615 END IF; -- l_attr_first_time
1616
1617 IF l_continue_further THEN
1618 IF NOT OE_VALIDATE_WF.HAS_ACTIVITY
1619 ( P_process => l_name
1620 , P_process_item_type => OE_GLOBALS.G_WFI_LIN
1621 , P_activity => fulfill_attributes.FulfillAttr
1622 , P_activity_item_type => OE_GLOBALS.G_WFI_LIN
1623 ) THEN
1624
1625 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_NO_FULFILL_ACT');
1626 FND_MESSAGE.SET_TOKEN('PROCESS_NAME',Display_Name(l_name,l_type));
1627 FND_MESSAGE.SET_TOKEN('FULFILLMENT_ACTIVITY'
1628 , fulfill_attributes.FulfillAttr);
1629 FND_MESSAGE.SET_TOKEN('FULFILL_LINE'
1630 , l_all_activity_tbl(l_instance).instance_label);
1631 OE_MSG_PUB.Add;
1632 oe_debug_pub.add('LOG 17: Add OE_WFVAL_NO_FULFILL_ACT',1);
1633 X_return_status := FND_API.G_RET_STS_ERROR;
1634
1635 ELSIF NOT l_errors_only THEN
1636 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_ACT_ORDER');
1637 FND_MESSAGE.SET_TOKEN('PROCESS_NAME',Display_Name(l_name,l_type));
1638 FND_MESSAGE.SET_TOKEN('ACTIVITY1'
1639 , fulfill_attributes.FulfillAttr);
1640 FND_MESSAGE.SET_TOKEN('ACTIVITY2'
1641 , l_all_activity_tbl(l_instance).instance_label);
1642 OE_MSG_PUB.Add;
1643 oe_debug_pub.add('LOG 18 : Added OE_WFVAL_ACT_ORDER' ,1);
1644 END IF; -- HAS_ACTIVITY
1645 END IF; -- l_continue_further
1646 END IF; -- fulfill_attributes.FulfillAttr
1647
1648 END LOOP; -- fulfill_attributes
1649 oe_debug_pub.add('Fulfill attributes are : '||l_attr_first_time,5);
1650
1651 END IF; -- l_all_activity_tbl = FULFILL_LINE
1652 END LOOP; -- l_instance
1653 END IF; -- COUNT > 0
1654 -- IF no FULFILL_LINE activity was detected in the above step AND
1655 -- NOT l_errors_only then adding message OE_WFVAL_MISSING_ACTIVITY
1656
1657 oe_debug_pub.add('Fulfill Activity Exists (Y/N) : '||l_fulfill_act_exists,5);
1658
1659 IF l_fulfill_act_exists = 'N' AND NOT l_errors_only THEN
1660 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_MISSING_ACTIVITY');
1661 FND_MESSAGE.SET_TOKEN('PROCESS_NAME',Display_Name(l_name,l_type));
1662 FND_MESSAGE.SET_TOKEN('ACTIVITY_NAME','FULFILL_LINE');
1663 OE_MSG_PUB.Add;
1664 oe_debug_pub.add('LOG 19 : Added OE_WFVAL_MISSING_ACTIVITY',1);
1665 END IF;
1666
1667 IF l_errors_only THEN
1668
1669 BEGIN
1670
1671 G_all_activity_tbl.DELETE;
1672 G_exit_from_loop := 'NO';
1673 oe_debug_pub.add('Calling Get Activities for Header in Validate Order Line',5);
1674
1675 Get_Activities
1676 ( P_process => l_order_flow
1677 , P_process_item_type => OE_GLOBALS.G_WFI_HDR
1678 , P_instance_label => NULL
1679 , P_activity_item_type => NULL
1680 );
1681
1682 l_hdr_activity_tbl := G_all_activity_tbl;
1683
1684 IF l_hdr_activity_tbl.COUNT > 0 THEN
1685 FOR hdr_instance IN l_hdr_activity_tbl.FIRST .. l_hdr_activity_tbl.LAST LOOP
1686 IF l_hdr_activity_tbl(hdr_instance).activity_name IN ('FULFILLMENT_WAIT_FOR_L'
1687 ,'INVOICING_CONT_L'
1688 /*, Commented for #6818912 'APPROVE_CONT_L' Bug 6411686 */
1689 /* ,'BOOK_CONT_L' Bug # 4908592 */
1690 ,'CLOSE_WAIT_FOR_L') OR
1691 /* Bug # 4908592 Start */
1692 (NVL(p_item_type, 'NULL') <> 'CONFIGURATION' AND
1693 l_hdr_activity_tbl(hdr_instance).activity_name = 'BOOK_CONT_L') THEN
1694 /* Bug # 4908592 End */
1695 oe_debug_pub.add('In VLF : for Act '||l_hdr_activity_tbl(hdr_instance).activity_name,5);
1696 oe_debug_pub.add('In VLF : for function '||l_hdr_activity_tbl(hdr_instance).function,5);
1697 oe_debug_pub.add('In VLF : for instance_id '||l_hdr_activity_tbl(hdr_instance).instance_id,5);
1698
1699 IF l_hdr_activity_tbl(hdr_instance).function = 'WF_STANDARD.CONTINUEFLOW' THEN
1700 BEGIN
1701 SELECT text_value
1702 INTO l_activity
1703 FROM wf_activity_attr_values waa
1704 WHERE waa.name = 'WAITING_ACTIVITY'
1705 AND process_activity_id = l_hdr_activity_tbl(hdr_instance).instance_id;
1706 -- The above sql determines the WAITING_ACTIVITY of the
1707 -- passed activity with CONTINUEFLOW function
1708 EXCEPTION
1709 WHEN NO_DATA_FOUND THEN
1710 l_activity := NULL;
1711 oe_debug_pub.add(' In No Data Found for WAITING_ACTIVITY',1);
1712 NULL;
1713 WHEN OTHERS THEN
1714 oe_debug_pub.add('Validate_Line_Flow WAITING ACTIVITY',1);
1715 oe_debug_pub.add('Validate_Line_Flow : Header_process : '||l_order_flow,1);
1716 oe_debug_pub.add('Validate_Line_Flow : Activity Name : '||l_hdr_activity_tbl(hdr_instance).instance_label,1);
1717 oe_debug_pub.add('Validate_Line_Flow : Error : '||sqlerrm,1);
1718 NULL;
1719 END;
1720
1721 ELSIF l_hdr_activity_tbl(hdr_instance).function = 'WF_STANDARD.WAITFORFLOW' THEN
1722 BEGIN
1723 SELECT text_value
1724 INTO l_activity
1725 FROM wf_activity_attr_values waa
1726 WHERE waa.name = 'CONTINUATION_ACTIVITY'
1727 AND process_activity_id = l_hdr_activity_tbl(hdr_instance).instance_id;
1728 -- The above sql determines the CONTINUATION_ACTIVITY of the
1729 -- passed activity with WAITFORFLOW function
1730 EXCEPTION
1731 WHEN NO_DATA_FOUND THEN
1732 l_activity := NULL;
1733 oe_debug_pub.add(' In No Data Found for CONTINUATION_ACTIVITY',1);
1734 NULL;
1735 WHEN OTHERS THEN
1736 oe_debug_pub.add('Validate_Line_Flow CONTINUATION_ACTIVITY',1);
1737 oe_debug_pub.add('Validate_Line_Flow : Header_process : '||l_order_flow,1);
1738 oe_debug_pub.add('Validate_Line_Flow : Activity Name : '||l_hdr_activity_tbl(hdr_instance).instance_label,1);
1739 oe_debug_pub.add('Validate_Line_Flow : Error : '||sqlerrm,1);
1740 NULL;
1741 END;
1742 END IF;
1743
1744 oe_debug_pub.add('In VLF : Matching Activity is : '||l_activity,5);
1745 matching_activity_exists := FALSE;
1746 IF l_activity IS NOT NULL THEN
1747 IF l_all_activity_tbl.COUNT > 0 THEN
1748 FOR testing_instance IN l_all_activity_tbl.FIRST .. l_all_activity_tbl.LAST LOOP
1749 IF ( l_all_activity_tbl(testing_instance).activity_name = l_activity ) THEN
1750 matching_activity_exists := TRUE;
1751 EXIT;
1752 END IF;
1753 END LOOP;
1754 END IF;
1755 END IF;
1756
1757 IF (matching_activity_exists <> TRUE) THEN
1758
1759 oe_debug_pub.add('In VLF : Matching activity do not exists, logging message',1);
1760
1761 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_SYNC_MISS');
1762 FND_MESSAGE.SET_TOKEN('PROCESS1',Display_Name(l_name,OE_GLOBALS.G_WFI_LIN));
1763 FND_MESSAGE.SET_TOKEN('ACTIVITY1',l_activity);
1764 FND_MESSAGE.SET_TOKEN('ACTIVITY2',l_hdr_activity_tbl(hdr_instance).activity_name);
1765 FND_MESSAGE.SET_TOKEN('PROCESS2',Display_Name(l_order_flow,OE_GLOBALS.G_WFI_HDR));
1766 OE_MSG_PUB.Add;
1767 X_return_status := FND_API.G_RET_STS_ERROR;
1768 END IF;
1769 END IF;
1770 END LOOP;
1771 END IF;
1772
1773 EXCEPTION
1774 WHEN OTHERS THEN
1775 NULL;
1776 END;
1777
1778
1779 oe_debug_pub.add('In VLF : Done with a/c flows check',5);
1780 oe_debug_pub.add('In VLF : Starting check sync for quick validation TRUE',5);
1781
1782 IF l_all_activity_tbl.COUNT > 0 THEN
1783 FOR testing_instance IN l_all_activity_tbl.FIRST .. l_all_activity_tbl.LAST LOOP
1784 IF l_all_activity_tbl(testing_instance).activity_name IN
1785 ('FULFILLMENT_CONT_H', 'INVOICING_WAIT_FOR_H','APPROVE_WAIT_FOR_H' /* Bug 6411686 */
1786 ,'BOOK_WAIT_FOR_H', 'CLOSE_CONT_H') THEN
1787 oe_debug_pub.add('In VLF : For Instance Label '||l_all_activity_tbl(testing_instance).instance_label,5);
1788 oe_debug_pub.add('In VLF : For Instance Id '||l_all_activity_tbl(testing_instance).instance_id,5);
1789 oe_debug_pub.add('In VLF : For function '||l_all_activity_tbl(testing_instance).function,5);
1790
1791 OE_VALIDATE_WF.CHECK_SYNC
1792 ( P_process => l_name
1793 , P_process_item_type => OE_GLOBALS.G_WFI_LIN
1794 , P_order_type_id => NULL
1795 , P_order_flow => l_order_flow
1796 , P_instance_label => l_all_activity_tbl(testing_instance).instance_label
1797 , P_act_item_type => OE_GLOBALS.G_WFI_LIN
1798 , P_function => l_all_activity_tbl(testing_instance).function
1799 , P_type => l_all_activity_tbl(testing_instance).type
1800 , P_instance_id => l_all_activity_tbl(testing_instance).instance_id
1801 , X_return_status => l_return_status
1802 );
1803 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1804 X_return_status := l_return_status;
1805 END IF;
1806 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1807 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1808 END IF;
1809 END IF;
1810 END LOOP; -- testing_instance
1811 END IF; -- l_all_activity_tbl.COUNT
1812 END IF; -- l_errors_only
1813
1814 oe_debug_pub.add('In VLF : Starting check sync for quick validation FALSE',5);
1815 IF NOT l_errors_only THEN
1816 IF l_all_activity_tbl.COUNT > 0 THEN --Vaibhav
1817 FOR testing_instance IN l_all_activity_tbl.FIRST .. l_all_activity_tbl.LAST LOOP
1818 IF l_all_activity_tbl(testing_instance).function IN
1819 ('WF_STANDARD.CONTINUEFLOW', 'WF_STANDARD.WAITFORFLOW') THEN
1820 oe_debug_pub.add('In VLF : For Instance Label '||l_all_activity_tbl(testing_instance).instance_label,5);
1821 oe_debug_pub.add('In VLF : For Instance Id '||l_all_activity_tbl(testing_instance).instance_id,5);
1822 oe_debug_pub.add('In VLF : For function '||l_all_activity_tbl(testing_instance).function,5);
1823
1824 OE_VALIDATE_WF.CHECK_SYNC
1825 ( P_process => l_name
1826 , P_process_item_type => OE_GLOBALS.G_WFI_LIN
1827 , P_order_type_id => NULL
1828 , P_order_flow => l_order_flow
1829 , P_instance_label => l_all_activity_tbl(testing_instance).instance_label
1830 , P_act_item_type => l_all_activity_tbl(testing_instance).activity_item_type
1831 , P_function => l_all_activity_tbl(testing_instance).function
1832 , P_type => l_all_activity_tbl(testing_instance).type
1833 , P_instance_id => l_all_activity_tbl(testing_instance).instance_id
1834 , X_return_status => l_return_status
1835 );
1836
1837 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1838 X_return_status := l_return_status;
1839 END IF;
1840 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1841 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1842 END IF;
1843 END IF;
1844 END LOOP;
1845 END IF;
1846
1847 oe_debug_pub.add('In VLF : Calling Has Activity for CLOSE_LINE in process : '||l_name,5);
1848 IF NOT OE_VALIDATE_WF.HAS_ACTIVITY
1849 ( P_process => l_name
1850 , P_process_item_type => OE_GLOBALS.G_WFI_LIN
1851 , P_activity => 'CLOSE_LINE'
1852 , P_activity_item_type => OE_GLOBALS.G_WFI_LIN
1853 ) THEN
1854
1855 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_MISSING_ACTIVITY');
1856 FND_MESSAGE.SET_TOKEN('PROCESS_NAME',Display_Name(l_name,OE_GLOBALS.G_WFI_LIN));
1857 FND_MESSAGE.SET_TOKEN('ACTIVITY_NAME','CLOSE_LINE');
1858 OE_MSG_PUB.Add;
1859 oe_debug_pub.add('LOG 20 : OE_WFVAL_MISSING_ACTIVITY',1);
1860 END IF;
1861
1862 -- Activities in process p_name assigned APIs
1863 -- wf_standard.defer or wf_standard.wait */
1864 FOR l_all_instance IN l_all_activity_tbl.FIRST .. l_all_activity_tbl.LAST LOOP
1865 IF l_all_activity_tbl(l_all_instance).function= 'WF_STANDARD.DEFER'
1866 OR l_all_activity_tbl(l_all_instance).function = 'WF_STANDARD.WAIT'
1867 THEN
1868
1869 oe_debug_pub.add('In VLF : Calling Wait And Loops for instance_label '||l_all_activity_tbl(l_all_instance).instance_label,5);
1870 oe_debug_pub.add('In VLF : Calling Wait And Loops for instance_id '||l_all_activity_tbl(l_all_instance).instance_id,5);
1871
1872 OE_VALIDATE_WF.WAIT_AND_LOOPS
1873 ( P_process => l_name
1874 , P_process_item_type => OE_GLOBALS.G_WFI_LIN
1875 , P_activity_id => l_all_activity_tbl(l_all_instance).instance_id
1876 , P_activity_label => l_all_activity_tbl(l_all_instance).instance_label
1877 , P_api => l_all_activity_tbl(l_all_instance).function
1878 , X_return_status => l_return_status
1879 );
1880 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1881 X_return_status := l_return_status;
1882 END IF;
1883 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1884 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1885 END IF;
1886 END IF; -- function = DEFER/WAIT
1887 END LOOP; -- l_all_instance
1888
1889 oe_debug_pub.add('In VLF : Calling Has Activity for INVOICE_INTERFACE and SHIP_LINE in process : '||l_name,5);
1890 IF OE_VALIDATE_WF.HAS_ACTIVITY
1891 ( P_process => l_name
1892 , P_process_item_type => OE_GLOBALS.G_WFI_LIN
1893 , P_activity => 'SHIP_LINE'
1894 , P_activity_item_type => OE_GLOBALS.G_WFI_LIN
1895 ) AND
1896
1897 OE_VALIDATE_WF.HAS_ACTIVITY
1898 ( P_process => l_name
1899 , P_process_item_type => OE_GLOBALS.G_WFI_LIN
1900 , P_activity => 'INVOICE_INTERFACE'
1901 , P_activity_item_type => OE_GLOBALS.G_WFI_LIN
1902 ) THEN
1903
1904 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_ACT_ORDER');
1905 FND_MESSAGE.SET_TOKEN('PROCESS_NAME',Display_Name(l_name,l_type));
1906 FND_MESSAGE.SET_TOKEN('ACTIVITY1','SHIP_LINE');
1907 FND_MESSAGE.SET_TOKEN('ACTIVITY2','INVOICE_INTERFACE');
1908 OE_MSG_PUB.Add;
1909 oe_debug_pub.add('LOG 21 : Added OE_WFVAL_ACT_ORDER' ,1) ;
1910 END IF;
1911 END IF; -- NOT l_errors_only
1912
1913 X_msg_count := OE_MSG_PUB.count_msg - l_msg_count;
1914 oe_debug_pub.add('In VLF : msg count : '||X_msg_count,5);
1915
1916 IF x_msg_count > 0 THEN
1917 X_msg_data := OE_MSG_PUB.get(l_msg_count + 1);
1918 END IF;
1919
1920 oe_debug_pub.add('Exiting Procedure OE_VALIDATE_WF.Validate_Line_Flow', 1);
1921 --l_end_time := dbms_utility.get_time;
1922 --oe_debug_pub.add(' Time taken = '||l_end_time- l_start_time);
1923
1924 EXCEPTION
1925 --WHEN FND_API.G_EXC_ERROR THEN
1926 -- RAISE FND_API.G_EXC_ERROR;
1927 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1928 oe_debug_pub.add('Error in Validate_Line_Flow : '||sqlerrm,5);
1929 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1930 WHEN OTHERS THEN
1931 IF OE_MSG_PUB.CHECK_MSG_LEVEL(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1932 OE_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME, 'Validate');
1933 END IF;
1934 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1935
1936 END Validate_Line_Flow;
1937
1938 PROCEDURE Validate_Line_Flow /* Bug # 4908592 */
1939 ( P_name IN VARCHAR2
1940 , P_order_flow IN VARCHAR2
1941 , p_quick_val IN BOOLEAN DEFAULT TRUE
1942 , X_return_status OUT NOCOPY VARCHAR2
1943 , X_msg_count OUT NOCOPY NUMBER
1944 , X_msg_data OUT NOCOPY VARCHAR2
1945 )
1946 IS
1947
1948 BEGIN
1949 Validate_Line_Flow
1950 ( P_name
1951 , P_order_flow
1952 , p_quick_val
1953 , X_return_status
1954 , X_msg_count
1955 , X_msg_data
1956 , NULL);
1957 END Validate_Line_Flow;
1958
1959 /*----------------------------------------------------------------
1960 Procedure Validate_Order_flow
1961 << Description >>
1962
1963 This program is called by:
1964 1. OE_VALIDATE_WF.Validate() API
1965 2. Transaction Types form (OEXDTTYP.fmb, OEXTRTYP.pld)
1966 ------------------------------------------------------------------*/
1967 PROCEDURE Validate_Order_flow
1968 ( P_name IN VARCHAR2
1969 , P_order_type_id IN NUMBER DEFAULT NULL
1970 , P_type IN VARCHAR2
1971 , p_quick_val IN BOOLEAN DEFAULT TRUE
1972 , X_return_status OUT NOCOPY VARCHAR2
1973 , X_msg_count OUT NOCOPY NUMBER
1974 , X_msg_data OUT NOCOPY VARCHAR2
1975 )
1976 IS
1977
1978 -- Local Variable Decleration
1979 l_instance NUMBER;
1980 l_return_status VARCHAR2(1);
1981 l_type VARCHAR2(8);
1982 l_name VARCHAR2(30);
1983 l_line_process_name VARCHAR2(30);
1984 l_header_process_name VARCHAR2(30);
1985 l_msg_data VARCHAR2(2000);
1986 l_quick_val BOOLEAN := TRUE;
1987 l_msg_count NUMBER;
1988 l_order_type_id NUMBER := NULL;
1989 l_all_activity_tbl OE_VALIDATE_WF.Activities_Tbl_Type;
1990 l_lin_activity_tbl OE_VALIDATE_WF.Activities_Tbl_Type;
1991 l_start_time NUMBER;
1992 l_end_time NUMBER;
1993 testing_instance NUMBER;
1994 testing_inst NUMBER;
1995 line_instance NUMBER;
1996 instance_count NUMBER;
1997 l_activity VARCHAR2(30);
1998 -- l_activity_sum VARCHAR2(120);
1999 matching_activity_exists BOOLEAN := FALSE;
2000
2001 BEGIN
2002 oe_debug_pub.add('Entering Procedure OE_VALIDATE_WF.Validate_Order_flow', 1);
2003 -- l_start_time := dbms_utility.get_time;
2004 X_return_status := FND_API.G_RET_STS_SUCCESS;
2005 l_msg_count := OE_MSG_PUB.count_msg;
2006
2007 -- Copying passed into locals
2008 l_name := P_name;
2009 l_type := P_type;
2010 l_order_type_id := P_order_type_id;
2011 l_quick_val := p_quick_val;
2012
2013 oe_debug_pub.add('Calling Out Transition from Validate Order Flow',5);
2014 OE_VALIDATE_WF.OUT_TRANSITIONS
2015 ( p_name => l_name
2016 , p_type => l_type
2017 , x_return_status => l_return_status
2018 );
2019
2020 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2021 X_return_status := l_return_status;
2022 END IF;
2023 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2024 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2025 END IF;
2026
2027 G_all_activity_tbl.DELETE;
2028 G_exit_from_loop := 'NO';
2029 oe_debug_pub.add('Calling Get_activities for Header from Validate Order Flow '||l_name,5);
2030
2031 Get_Activities
2032 ( P_process => l_name
2033 , P_process_item_type => l_type
2034 , P_instance_label => NULL
2035 , P_activity_item_type => NULL
2036 );
2037
2038 l_all_activity_tbl := G_all_activity_tbl;
2039
2040 IF l_quick_val AND l_type = OE_GLOBALS.G_WFI_HDR THEN
2041
2042 BEGIN
2043 SELECT DISTINCT process_name
2044 INTO l_line_process_name
2045 FROM oe_workflow_assignments
2046 WHERE order_type_id = l_order_type_id
2047 AND line_type_id IS NOT NULL
2048 AND NVL(wf_item_type,'OEOL') = 'OEOL'
2049 AND SYSDATE >= start_date_active
2050 AND TRUNC(SYSDATE) <= nvl(end_date_active, SYSDATE)
2051 AND ROWNUM = 1;
2052
2053 G_all_activity_tbl.DELETE;
2054 G_exit_from_loop := 'NO';
2055 oe_debug_pub.add('Calling Get Activities for Lines in Validate Order Flow '||l_line_process_name,5);
2056
2057 Get_Activities
2058 ( P_process => l_line_process_name
2059 , P_process_item_type => OE_GLOBALS.G_WFI_LIN
2060 , P_instance_label => NULL
2061 , P_activity_item_type => NULL
2062 );
2063
2064 l_lin_activity_tbl := G_all_activity_tbl;
2065
2066 IF l_lin_activity_tbl.COUNT > 0 THEN
2067 FOR line_instance IN l_lin_activity_tbl.FIRST .. l_lin_activity_tbl.LAST LOOP
2068 IF l_lin_activity_tbl(line_instance).activity_name IN
2069 ('FULFILLMENT_CONT_H', 'INVOICING_WAIT_FOR_H','APPROVE_WAIT_FOR_H' /* Bug 6411686 */
2070 ,'BOOK_WAIT_FOR_H', 'CLOSE_CONT_H') THEN
2071 oe_debug_pub.add('In VOF : For Act '||l_lin_activity_tbl(line_instance).activity_name,5);
2072 oe_debug_pub.add('In VOF : For function '||l_lin_activity_tbl(line_instance).function,5);
2073 oe_debug_pub.add('In VOF : For Instance_id '||l_lin_activity_tbl(line_instance).instance_id,5);
2074
2075 IF l_lin_activity_tbl(line_instance).function = 'WF_STANDARD.CONTINUEFLOW' THEN
2076 BEGIN
2077 SELECT text_value
2078 INTO l_activity
2079 FROM wf_activity_attr_values waa
2080 WHERE waa.name = 'WAITING_ACTIVITY'
2081 AND process_activity_id = l_lin_activity_tbl(line_instance).instance_id;
2082 -- The above sql determines the WAITING_ACTIVITY of the
2083 -- passed activity with CONTINUEFLOW function
2084 EXCEPTION
2085 WHEN NO_DATA_FOUND THEN
2086 l_activity := NULL;
2087 oe_debug_pub.add(' In No Data Found for WAITING_ACTIVITY',1);
2088 NULL;
2089 WHEN OTHERS THEN
2090 oe_debug_pub.add('Validate_Order_flow WAITING ACTIVITY',1);
2091 oe_debug_pub.add('Validate_Order_flow : Line_process : '||l_line_process_name,1);
2092 oe_debug_pub.add('Validate_Order_flow : Activity Name : '||l_lin_activity_tbl(line_instance).instance_label,1);
2093 oe_debug_pub.add('Validate_Order_flow : Error : '||sqlerrm,1);
2094 NULL;
2095 END;
2096
2097 ELSIF l_lin_activity_tbl(line_instance).function = 'WF_STANDARD.WAITFORFLOW' THEN
2098 BEGIN
2099 SELECT text_value
2100 INTO l_activity
2101 FROM wf_activity_attr_values waa
2102 WHERE waa.name = 'CONTINUATION_ACTIVITY'
2103 AND process_activity_id = l_lin_activity_tbl(line_instance).instance_id;
2104 -- The above sql determines the CONTINUATION_ACTIVITY of the
2105 -- passed activity with WAITFORFLOW function
2106 EXCEPTION
2107 WHEN NO_DATA_FOUND THEN
2108 l_activity := NULL;
2109 oe_debug_pub.add(' In No Data Found for CONTINUATION_ACTIVITY',1);
2110 NULL;
2111 WHEN OTHERS THEN
2112 oe_debug_pub.add('Validate_Order_flow CONTINUATION_ACTIVITY',1);
2113 oe_debug_pub.add('Validate_Order_flow : Line_process : '||l_line_process_name,1);
2114 oe_debug_pub.add('Validate_Order_flow : Activity Name : '||l_lin_activity_tbl(line_instance).instance_label,1);
2115 oe_debug_pub.add('Validate_Order_flow : Error : '||sqlerrm,1);
2116 NULL;
2117 END;
2118 END IF;
2119
2120 oe_debug_pub.add('In VOF : matching activity is '||l_activity,5);
2121 matching_activity_exists := FALSE;
2122 IF l_activity IS NOT NULL THEN
2123 IF l_all_activity_tbl.COUNT > 0 THEN
2124 FOR testing_inst IN l_all_activity_tbl.FIRST .. l_all_activity_tbl.LAST LOOP
2125 IF ( l_all_activity_tbl(testing_inst).activity_name = l_activity ) THEN
2126 oe_debug_pub.add('In VOF : matching activity Exists '||l_all_activity_tbl(testing_inst).activity_name,5);
2127 matching_activity_exists := TRUE;
2128 EXIT;
2129 END IF;
2130 END LOOP;
2131 END IF;
2132 END IF;
2133
2134 IF (matching_activity_exists <> TRUE) THEN
2135
2136 oe_debug_pub.add('In VOF : matching activity do not Exists, logging message',1);
2137 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_SYNC_MISS');
2138 FND_MESSAGE.SET_TOKEN('PROCESS1',Display_Name(l_name,OE_GLOBALS.G_WFI_HDR));
2139 FND_MESSAGE.SET_TOKEN('ACTIVITY1',l_activity);
2140 FND_MESSAGE.SET_TOKEN('ACTIVITY2',l_lin_activity_tbl(line_instance).activity_name);
2141 FND_MESSAGE.SET_TOKEN('PROCESS2',Display_Name(l_line_process_name,OE_GLOBALS.G_WFI_LIN));
2142 OE_MSG_PUB.Add;
2143 X_return_status := FND_API.G_RET_STS_ERROR;
2144 END IF;
2145 END IF;
2146 END LOOP;
2147 END IF;
2148
2149 EXCEPTION
2150 WHEN OTHERS THEN
2151 l_line_process_name := NULL;
2152 NULL;
2153 END;
2154
2155 IF l_all_activity_tbl.COUNT > 0 THEN
2156 FOR testing_instance IN l_all_activity_tbl.FIRST .. l_all_activity_tbl.LAST LOOP
2157 IF l_all_activity_tbl(testing_instance).activity_name IN
2158 ('FULFILLMENT_WAIT_FOR_L', 'INVOICING_CONT_L' /*, Commented for #6818912 'APPROVE_CONT_L' Bug 6411686 */ ,'BOOK_CONT_L', 'CLOSE_WAIT_FOR_L') THEN
2159
2160 oe_debug_pub.add('In VOF : For Instance_Label '||l_all_activity_tbl(testing_instance).instance_label,5);
2161 oe_debug_pub.add('In VOF : For function '||l_all_activity_tbl(testing_instance).function,5);
2162 oe_debug_pub.add('In VOF : For Instance_id '||l_all_activity_tbl(testing_instance).instance_id,5);
2163
2164 OE_VALIDATE_WF.CHECK_SYNC
2165 ( P_process => l_name
2166 , P_process_item_type => l_type
2167 , p_order_type_id => l_order_type_id
2168 , P_instance_label => l_all_activity_tbl(testing_instance).instance_label
2169 , P_act_item_type => OE_GLOBALS.G_WFI_HDR
2170 , P_function => l_all_activity_tbl(testing_instance).function
2171 , P_type => l_all_activity_tbl(testing_instance).type
2172 , P_instance_id => l_all_activity_tbl(testing_instance).instance_id
2173 , x_return_status => l_return_status
2174 );
2175
2176 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2177 X_return_status := l_return_status;
2178 END IF;
2179 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2181 END IF;
2182 END IF;
2183
2184 END LOOP; -- testing_instance
2185 END IF; -- l_all_activity_tbl.COUNT
2186 END IF; -- l_quick_val
2187
2188 IF NOT l_quick_val THEN
2189 IF l_type = OE_GLOBALS.G_WFI_HDR THEN
2190 IF l_all_activity_tbl.COUNT > 0 THEN
2191 FOR instance_count IN l_all_activity_tbl.FIRST .. l_all_activity_tbl.LAST LOOP
2192 IF l_all_activity_tbl(instance_count).function
2193 IN ('WF_STANDARD.CONTINUEFLOW', 'WF_STANDARD.WAITFORFLOW') THEN
2194
2195 oe_debug_pub.add('In VOF : For Instance_Label '||l_all_activity_tbl(instance_count).instance_label,5);
2196 oe_debug_pub.add('In VOF : For function '||l_all_activity_tbl(instance_count).function,5);
2197 oe_debug_pub.add('In VOF : For Instance_id '||l_all_activity_tbl(instance_count).instance_id,5);
2198
2199 OE_VALIDATE_WF.CHECK_SYNC
2200 ( P_process => l_name
2201 , P_process_item_type => l_type
2202 , p_order_type_id => l_order_type_id
2203 , P_instance_label => l_all_activity_tbl(instance_count).instance_label
2204 , P_act_item_type => l_all_activity_tbl(instance_count).activity_item_type
2205 , P_function => l_all_activity_tbl(instance_count).function
2206 , P_type => l_all_activity_tbl(instance_count).type
2207 , P_instance_id => l_all_activity_tbl(instance_count).instance_id
2208 , x_return_status => l_return_status
2209 );
2210
2211 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2212 X_return_status := l_return_status;
2213 END IF;
2214 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2216 END IF;
2217 END IF;
2218 END LOOP; -- instance_count
2219 END IF; -- l_all_activity_tbl.COUNT
2220
2221 oe_debug_pub.add('In VOF : Calling Has_Activity for BOOK_ORDER',5);
2222 IF NOT OE_VALIDATE_WF.HAS_ACTIVITY
2223 ( p_process => l_name
2224 , p_process_item_type => l_type
2225 , p_activity => 'BOOK_ORDER'
2226 , p_activity_item_type => OE_GLOBALS.G_WFI_HDR
2227 ) THEN
2228
2229 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_MISSING_ACTIVITY');
2230 FND_MESSAGE.SET_TOKEN('PROCESS_NAME',Display_Name(l_name,l_type));
2231 FND_MESSAGE.SET_TOKEN('ACTIVITY_NAME','BOOK_ORDER');
2232 OE_MSG_PUB.Add;
2233 oe_debug_pub.add('LOG 22 : Added OE_WFVAL_MISSING_ACTIVITY',1);
2234 END IF;
2235
2236 oe_debug_pub.add('In VOF : Calling Has_Activity for CLOSE_HEADER',5);
2237 IF NOT OE_VALIDATE_WF.HAS_ACTIVITY
2238 ( p_process => l_name
2239 , p_process_item_type => l_type
2240 , p_activity => 'CLOSE_HEADER'
2241 , p_activity_item_type => OE_GLOBALS.G_WFI_HDR
2242 ) THEN
2243
2244 FND_MESSAGE.SET_NAME('ONT','OE_WFVAL_MISSING_ACTIVITY');
2245 FND_MESSAGE.SET_TOKEN('PROCESS_NAME',Display_Name(l_name,l_type));
2246 FND_MESSAGE.SET_TOKEN('ACTIVITY_NAME','CLOSE_HEADER');
2247 OE_MSG_PUB.Add;
2248 oe_debug_pub.add('LOG 23 : Added OE_WFVAL_MISSING_ACTIVITY',1);
2249 END IF;
2250 END IF; -- only for l_type = OE_GLOBALS.G_WFI_HDR
2251
2252 -- All activities in process p_name assigned APIs
2253 -- wf_standard.defer or wf_standard.wait
2254
2255 FOR l_instance IN l_all_activity_tbl.FIRST .. l_all_activity_tbl.LAST LOOP
2256 IF l_all_activity_tbl(l_instance).function= 'WF_STANDARD.DEFER'
2257 OR l_all_activity_tbl(l_instance).function = 'WF_STANDARD.WAIT'
2258 THEN
2259
2260 oe_debug_pub.add('In VOF : Calling Wait And Loops for instance_label '||l_all_activity_tbl(l_instance).instance_label,5);
2261 oe_debug_pub.add('In VOF : Calling Wait And Loops for instance_id '||l_all_activity_tbl(l_instance).instance_id,5);
2262
2263 OE_VALIDATE_WF.WAIT_AND_LOOPS
2264 ( P_process => l_name
2265 , P_process_item_type => l_type
2266 , P_activity_id => l_all_activity_tbl(l_instance).instance_id
2267 , P_activity_label => l_all_activity_tbl(l_instance).instance_label
2268 , P_api => l_all_activity_tbl(l_instance).function
2269 , x_return_status => l_return_status
2270 );
2271
2272 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2273 X_return_status := l_return_status;
2274 END IF;
2275 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2277 END IF;
2278
2279 END IF; -- function = DEFER/WAIT
2280 END LOOP; -- l_instance
2281
2282 END IF; -- NOT l_quick_val
2283
2284 X_msg_count := OE_MSG_PUB.count_msg - l_msg_count;
2285 oe_debug_pub.add('In VOF : msg count : '||X_msg_count,5);
2286 IF x_msg_count > 0 THEN
2287 X_msg_data := OE_MSG_PUB.get(l_msg_count + 1);
2288 END IF;
2289
2290 oe_debug_pub.add('Exiting Procedure OE_VALIDATE_WF.Validate_Order_flow', 1);
2291 -- l_end_time := dbms_utility.get_time;
2292 -- oe_debug_pub.add(' Time taken = '||l_end_time- l_start_time);
2293
2294 EXCEPTION
2295 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2296 oe_debug_pub.add('Error in Validate_Order_Flow : '||sqlerrm,5);
2297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2298 WHEN OTHERS THEN
2299 IF OE_MSG_PUB.CHECK_MSG_LEVEL(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2300 OE_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME, 'Validate');
2301 END IF;
2302 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2303
2304 END Validate_Order_flow;
2305
2306 /*----------------------------------------------------------------
2307 Procedure Validate
2308 Validates all order/blanket header, negotiation and line
2309 workflow processes assigned to order type p_order_type_id.
2310 If p_order_type_id is NULL, runs the validation for all active
2311 order types.
2312
2313 This program is called by:
2314 1. Validate OM Workflow (OEXVWF) concurrent program
2315
2316 The program calls the following other programs:
2317 1. OE_VALIDATE_WF.validate_order_flow()
2318 2. OE_VALIDATE_WF.validate_line_flow()
2319 ------------------------------------------------------------------*/
2320 PROCEDURE Validate
2321 ( Errbuf OUT NOCOPY VARCHAR2 -- AOL standard
2322 , retcode OUT NOCOPY VARCHAR2 -- AOL standard
2323 , P_order_type_id IN NUMBER DEFAULT NULL
2324 )
2325 IS
2326
2327 -- Cursor Decleration
2328 CURSOR c_all_process IS
2329 SELECT sales_document_type_code
2330 , transaction_type_id
2331 , transaction_type_code
2332 , order_category_code
2333 , start_date_active
2334 , end_date_active
2335 FROM oe_transaction_types_vl
2336 WHERE transaction_type_code = 'ORDER'
2337 AND SYSDATE >= start_date_active
2338 AND TRUNC(SYSDATE) <= NVL(end_date_active, SYSDATE);
2339
2340 CURSOR c_line_item_process(c_type_id NUMBER) IS
2341 SELECT DISTINCT process_name, item_type_code
2342 FROM oe_workflow_assignments
2343 WHERE order_type_id = c_type_id
2344 AND line_type_id IS NOT NULL
2345 AND NVL(wf_item_type, 'OEOL') = 'OEOL'
2346 AND SYSDATE >= start_date_active
2347 AND TRUNC(SYSDATE) <= nvl(end_date_active, SYSDATE);
2348
2349 CURSOR c_line_process(c_type_id NUMBER) IS
2350 SELECT DISTINCT process_name
2351 FROM oe_workflow_assignments
2352 WHERE order_type_id = c_type_id
2353 AND line_type_id IS NOT NULL
2354 AND NVL(wf_item_type,'OEOL') = 'OEOL'
2355 AND SYSDATE >= start_date_active
2356 AND TRUNC(SYSDATE) <= nvl(end_date_active, SYSDATE);
2357
2358 CURSOR c_other_process(c_type_id NUMBER, c_item_type VARCHAR2) IS
2359 SELECT DISTINCT process_name
2360 FROM oe_workflow_assignments
2361 WHERE order_type_id = c_type_id
2362 AND wf_item_type = c_item_type
2363 AND SYSDATE >= start_date_active
2364 AND TRUNC(SYSDATE) <= nvl(end_date_active, SYSDATE);
2365
2366 -- Local Variable Decleration
2367 l_return_status VARCHAR2(1);
2368 l_process_name VARCHAR2(30);
2369 l_msg_data VARCHAR2(2000);
2370 l_msg_count NUMBER;
2371 l_header_process_index NUMBER := 0;
2372 l_record_count NUMBER := 0;
2373 l_order_type_id NUMBER := NULL;
2374 l_validating_flow VARCHAR2(100);
2375 l_transaction_rec c_all_process%ROWTYPE;
2376 TYPE l_order_types IS TABLE OF c_all_process%ROWTYPE
2377 INDEX BY BINARY_INTEGER;
2378 l_transaction_tbl l_order_types;
2379 l_start_time NUMBER;
2380 l_end_time NUMBER;
2381 l_msg_total NUMBER;
2382
2383 BEGIN
2384 oe_debug_pub.add('Entering Procedure OE_VALIDATE_WF.Validate', 1);
2385 -- l_start_time := dbms_utility.get_time;
2386 Retcode := 0;
2387 Errbuf := NULL;
2388 l_validating_flow := 'No Name Mentioned';
2389
2390 IF p_order_type_id IS NOT NULL THEN
2391
2392 -- Copying passed into locals
2393 l_order_type_id := p_order_type_id;
2394
2395 SELECT sales_document_type_code
2396 , transaction_type_id
2397 , transaction_type_code
2398 , order_category_code
2399 , start_date_active
2400 , end_date_active
2401 INTO l_transaction_tbl(1).sales_document_type_code
2402 , l_transaction_tbl(1).transaction_type_id
2403 , l_transaction_tbl(1).transaction_type_code
2404 , l_transaction_tbl(1).order_category_code
2405 , l_transaction_tbl(1).start_date_active
2406 , l_transaction_tbl(1).end_date_active
2407 FROM oe_transaction_types_all
2408 WHERE transaction_type_id = l_order_type_id;
2409
2410 ELSE
2411
2412 OPEN c_all_process;
2413 LOOP
2414 FETCH c_all_process
2415 INTO l_transaction_tbl(l_transaction_tbl.COUNT + 1);
2416 EXIT WHEN c_all_process%NOTFOUND;
2417 END LOOP;
2418 CLOSE c_all_process;
2419 -- Getting the entire cursor record in table type.
2420
2421 END IF; -- IF p_order_type_id
2422
2423 FND_FILE.put_line(FND_FILE.output,'Please correct the following reported Errors/Warnings, if any -'); --, in the respective Order Types -'); Changed for bug 4438936
2424 IF l_transaction_tbl.COUNT > 0 THEN
2425 FOR l_record_count IN l_transaction_tbl.First .. l_transaction_tbl.Last LOOP
2426
2427 OE_MSG_PUB.Initialize;
2428 l_msg_total := 0;
2429
2430 BEGIN
2431 SELECT name
2432 INTO l_validating_flow
2433 FROM oe_transaction_types_vl
2434 WHERE TRANSACTION_TYPE_ID = l_transaction_tbl(l_record_count).transaction_type_id;
2435 EXCEPTION
2436 WHEN NO_DATA_FOUND THEN
2437 NULL;
2438 END;
2439
2440 FND_FILE.put_line(FND_FILE.output,' ');
2441 FND_FILE.put_line(FND_FILE.output,'Validating Order Type : '||l_validating_flow||' ('||l_transaction_tbl(l_record_count).transaction_type_id||')');
2442 FND_FILE.put_line(FND_FILE.output,rpad('-',(27+length(l_validating_flow)+length(l_transaction_tbl(l_record_count).transaction_type_id)),'-'));
2443
2444 oe_debug_pub.add(' ');
2445 oe_debug_pub.add('Validating Order Type : '||l_validating_flow||' ('||l_transaction_tbl(l_record_count).transaction_type_id||')');
2446 oe_debug_pub.add(rpad('-',(27+length(l_validating_flow)+length(l_transaction_tbl(l_record_count).transaction_type_id)),'-'));
2447
2448 IF l_transaction_tbl(l_record_count).sales_document_type_code='O' THEN
2449 -- Uunconditionally for pre-11.5.10 releases)
2450 -- Getting associated order header() internal workflow process name
2451 -- from oe_workflow_assignments;
2452
2453 BEGIN
2454 SELECT process_name
2455 INTO l_process_name
2456 FROM oe_workflow_assignments
2457 WHERE order_type_id = l_transaction_tbl(l_record_count).transaction_type_id
2458 AND line_type_id IS NULL
2459 AND wf_item_type = 'OEOH'
2460 AND SYSDATE >= start_date_active
2461 AND TRUNC(SYSDATE) <= NVL(end_date_active, SYSDATE);
2462 EXCEPTION
2463 WHEN NO_DATA_FOUND THEN
2464 l_process_name := NULL;
2465 END;
2466
2467 IF l_process_name IS NOT NULL THEN
2468
2469 OE_VALIDATE_WF.VALIDATE_ORDER_FLOW
2470 ( p_name => l_process_name
2471 , p_order_type_id => l_transaction_tbl(l_record_count).transaction_type_id
2472 , p_type => OE_GLOBALS.G_WFI_HDR
2473 , p_quick_val => FALSE
2474 , x_return_status => l_return_status
2475 , x_msg_count => l_msg_count
2476 , x_msg_data => l_msg_data
2477 );
2478
2479 l_msg_total := l_msg_total + l_msg_count;
2480
2481 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2482 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2483 END IF;
2484 /* Selecting from oe_workflow_assignments distinct order line
2485 (OEOL) internal workflow process names and OM item type
2486 combinations assigned to this order type */
2487
2488 FOR line_item_processes IN c_line_item_process(l_transaction_tbl(l_record_count).transaction_type_id) LOOP
2489
2490 OE_VALIDATE_WF.LINE_FLOW_ASSIGNMENT
2491 ( p_name => line_item_processes.process_name
2492 , p_item_type => line_item_processes.item_type_code
2493 , x_return_status => l_return_status
2494 , x_msg_count => l_msg_count
2495 );
2496
2497 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2498 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2499 END IF;
2500 END LOOP; -- line_item_processes
2501 /* Selecting from oe_workflow_assignments distinct order line
2502 (OEOL) internal workflow process names assigned to this
2503 order type */
2504
2505 FOR line_processes IN c_line_process(l_transaction_tbl(l_record_count).transaction_type_id) LOOP
2506 OE_VALIDATE_WF.VALIDATE_LINE_FLOW
2507 ( p_name => line_processes.process_name
2508 , p_order_flow => l_process_name
2509 , p_quick_val => FALSE
2510 , x_return_status => l_return_status
2511 , x_msg_count => l_msg_count
2512 , x_msg_data => l_msg_data
2513 );
2514
2515 l_msg_total := l_msg_total + l_msg_count;
2516 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2517 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2518 END IF;
2519 END LOOP; -- End Loop line_processes
2520
2521 END IF;
2522
2523 ELSIF l_transaction_tbl(l_record_count).sales_document_type_code='B' THEN
2524 -- not applicable to pre-11.5.10 releases
2525 -- Getting associated blanket header (OEBH) internal workflow
2526 -- process name from oe_workflow_assignments
2527 FOR blanket_processes IN c_other_process(l_transaction_tbl(l_record_count).transaction_type_id, OE_GLOBALS.G_WFI_BKT) LOOP
2528
2529 OE_VALIDATE_WF.VALIDATE_ORDER_FLOW
2530 ( p_name => blanket_processes.process_name
2531 , p_type => OE_GLOBALS.G_WFI_BKT
2532 , p_quick_val => FALSE
2533 , x_return_status => l_return_status
2534 , x_msg_count => l_msg_count
2535 , x_msg_data => l_msg_data
2536 );
2537
2538 l_msg_total := l_msg_total + l_msg_count;
2539 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2540 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2541 END IF;
2542 END LOOP; -- End Loop blanket_processes
2543 END IF; -- IF sales_document_type_code = 'O'/'B'
2544
2545 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
2546 /* Applicable for Release 11.5.10 + only: Getting the associated
2547 negotiation header (OENH) internal workflow process name from
2548 oe_workflow_assignments; */
2549 FOR negotiation_processes IN c_other_process(l_transaction_tbl(l_record_count).transaction_type_id, OE_GLOBALS.G_WFI_NGO) LOOP
2550
2551 OE_VALIDATE_WF.VALIDATE_ORDER_FLOW
2552 ( p_name => negotiation_processes.process_name
2553 , p_type => OE_GLOBALS.G_WFI_NGO
2554 , p_quick_val => FALSE
2555 , x_return_status => l_return_status
2556 , x_msg_count => l_msg_count
2557 , x_msg_data => l_msg_data
2558 );
2559
2560 l_msg_total := l_msg_total + l_msg_count;
2561 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2562 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2563 END IF;
2564 END LOOP; -- End Loop negotiation_processes
2565 END IF; -- End of Applicable for Release 11.5.10 + only
2566
2567 IF NVL(FND_PROFILE.VALUE('CONC_REQUEST_ID'), 0) <> 0 THEN
2568 -- Called from concurrent request */
2569 IF l_msg_total > 0 THEN
2570 FOR I IN 1 .. l_msg_total LOOP
2571 l_msg_data := to_char(I)||'. '||OE_MSG_PUB.Get(I,FND_API.G_FALSE); -- #4617652
2572 FND_FILE.put_line(FND_FILE.output, l_msg_data);
2573 -- Writing validation messages into the concurrent
2574 -- request output file
2575 END LOOP;
2576 ELSE
2577 FND_FILE.put_line(FND_FILE.output,' << No Errors/Warnings Reported >>'); -- For bug 4438936
2578 END IF;
2579 END IF;
2580
2581 END LOOP; -- End Loop l_record_count
2582 END IF;
2583
2584 oe_debug_pub.add('Exiting Procedure OE_VALIDATE_WF.Validate', 1);
2585 -- l_end_time := dbms_utility.get_time;
2586 --oe_debug_pub.add(' Time taken = '||l_end_time- l_start_time);
2587
2588 EXCEPTION
2589 WHEN FND_API.G_EXC_ERROR THEN
2590 retcode := 2;
2591 errbuf := 'Please check the log file for error messages';
2592 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2593 retcode := 2;
2594 errbuf := 'Please check the log file for error messages';
2595 WHEN OTHERS THEN
2596 retcode := 2;
2597 errbuf := sqlerrm;
2598
2599 END Validate;
2600
2601 END OE_VALIDATE_WF; --Package Ends