1 PACKAGE BODY AMW_Utility_PVT AS
2 /* $Header: amwvutlb.pls 120.6.12000000.4 2007/08/02 14:32:51 shelango ship $ */
3 -- HISTORY
4 -- 4/20/2003 mpande Creates
5 ---------------------------------------------------------------------
6
7 /* abedajna add begin */
8 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMW_Utility_PVT';
9 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amwvutlb.pls';
10 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
11 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
12 /* abedajna add end */
13
14 /** dliao add **/
15 g_initialize_log BOOLEAN :=FALSE;
16 g_session_id NUMBER;
17 /** dliao add end **/
18
19 -- Added for use by bind_parse.
20 TYPE col_val_rec IS RECORD (
21 col_name VARCHAR2(2000),
22 col_op VARCHAR2(10),
23 col_value VARCHAR2(2000) );
24
25 TYPE col_val_tbl IS TABLE OF col_val_rec INDEX BY BINARY_INTEGER;
26
27 ---------------------------------------------------------------------
28 -- FUNCTION
29 -- bind_parse
30 --
31 -- DESCRIPTION
32 -- Given a string containing the WHERE conditions in a WHERE
33 -- clause, return a tuple of column name and column value.
34 -- HISTORY
35 -- 4/20/2003 mpande Created.
36 ---------------------------------------------------------------------
37 AMW_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
38 AMW_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
39 AMW_DEBUG_MEDIUM_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
40
41 PROCEDURE bind_parse (
42 p_string IN VARCHAR2,
43 x_col_val_tbl OUT NOCOPY col_val_tbl
44 );
45
46
47 ---------------------------------------------------------------------
48 -- FUNCTION
49 -- check_fk_exists
50 --
51 -- HISTORY
52 -- 4/20/2003 mpande Created.
53 ---------------------------------------------------------------------
54 FUNCTION check_fk_exists(
55 p_table_name IN VARCHAR2,
56 p_pk_name IN VARCHAR2,
57 p_pk_value IN VARCHAR2,
58 p_pk_data_type IN NUMBER := g_number,
59 p_additional_where_clause IN VARCHAR2 := NULL
60 )
61 RETURN VARCHAR2
62 IS
63
64 l_sql VARCHAR2(4000);
65 l_count NUMBER;
66
67 BEGIN
68 l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || UPPER(p_table_name);
69 l_sql := l_sql || ' WHERE ' || UPPER(p_pk_name) || ' = :b1';
70
71 IF p_additional_where_clause IS NOT NULL THEN
72 -- given time, incorporate bind_parse
73 l_sql := l_sql || ' AND ' || p_additional_where_clause;
74 END IF;
75
76 l_sql := l_sql || ')';
77
78 IF (AMW_DEBUG_HIGH_ON) THEN
79 debug_message('SQL statement: '||l_sql);
80 END IF;
81
82 BEGIN
83 EXECUTE IMMEDIATE l_sql INTO l_count
84 USING p_pk_value;
85 EXCEPTION
86 WHEN NO_DATA_FOUND THEN
87 l_count := 0;
88 END;
89
90 IF l_count = 0 THEN
91 RETURN FND_API.g_false;
92 ELSE
93 RETURN FND_API.g_true;
94 END IF;
95
96 END check_fk_exists;
97
98
99 ---------------------------------------------------------------------
100 -- FUNCTION
101 -- check_lookup_exists
102 --
103 -- HISTORY
104 -- 4/20/2003 mpande Created.
105 ---------------------------------------------------------------------
106 FUNCTION check_lookup_exists(
107 p_lookup_table_name IN VARCHAR2 := g_amw_lookups,
108 p_lookup_type IN VARCHAR2,
109 p_lookup_code IN VARCHAR2
110 )
111 Return VARCHAR2
112 IS
113
114 l_sql VARCHAR2(4000);
115 l_count NUMBER;
116
117 BEGIN
118
119 IF p_lookup_table_name = g_amw_lookups THEN
120 return check_lookup_exists (
121 p_lookup_type => p_lookup_type
122 , p_lookup_code => p_lookup_code
123 , p_view_application_id => 242
124 );
125 ELSE
126 l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || p_lookup_table_name;
127 l_sql := l_sql || ' WHERE LOOKUP_TYPE = :b1';
128 l_sql := l_sql || ' AND LOOKUP_CODE = :b2';
129 l_sql := l_sql || ' AND ENABLED_FLAG = ''Y'')';
130
131 IF (AMW_DEBUG_HIGH_ON) THEN
132 debug_message('SQL statement: '||l_sql);
133 END IF;
134
135 BEGIN
136 EXECUTE IMMEDIATE l_sql INTO l_count
137 USING p_lookup_type, p_lookup_code;
138 EXCEPTION
139 WHEN NO_DATA_FOUND THEN
140 l_count := 0;
141 END;
142 IF l_count = 0 THEN
143 RETURN FND_API.g_false;
144 ELSE
145 RETURN FND_API.g_true;
146 END IF;
147 END IF;
148
149 END check_lookup_exists;
150
151
152
153 ---------------------------------------------------------------------
154 -- FUNCTION
155 -- overloaded check_lookup_exists
156 -- PURPOSE
157 -- This function checks if a lookup_code is valid from fnd_lookups when
158 -- view_application_id is passed in.
159 -- HISTORY
160 -- 4/20/2003 mpande created.
161 ---------------------------------------------------------------------
162 FUNCTION check_lookup_exists(
163 p_lookup_type IN VARCHAR2,
164 p_lookup_code IN VARCHAR2,
165 p_view_application_id IN NUMBER
166 )
167 Return VARCHAR2
168 IS
169 CURSOR cur_check_lookup_exists( p_lookup_type VARCHAR2
170 , p_lookup_code VARCHAR2
171 , p_view_app_id NUMBER) IS
172 SELECT 1 FROM fnd_lookup_values lkup
173 WHERE lkup.LOOKUP_TYPE = p_lookup_type
174 AND lkup.LOOKUP_CODE = p_lookup_code
175 AND lkup.view_application_id = p_view_app_id
176 AND lkup.ENABLED_FLAG = 'Y'
177 AND lkup.language = USERENV('LANG')
178 AND lkup.security_group_id = to_number(decode(substrb(userenv('CLIENT_INFO'),55,1
179 ), ' ', '0'
180 , NULL, '0'
181 , substrb(userenv('CLIENT_INFO'),55,10
182 )
183 )
184 );
185 l_count NUMBER := 0;
186
187 BEGIN
188
189 OPEN cur_check_lookup_exists( p_lookup_type
190 , p_lookup_code
191 , p_view_application_id);
192 FETCH cur_check_lookup_exists INTO l_count;
193 CLOSE cur_check_lookup_exists;
194
195 IF l_count = 0 THEN
196 RETURN FND_API.g_false;
197 ELSE
198 RETURN FND_API.g_true;
199 END IF;
200
201 END check_lookup_exists;
202
203
204 ---------------------------------------------------------------------
205 -- FUNCTION
206 -- check_uniqueness
207 --
208 -- HISTORY
209 -- 4/20/2003 mpande Created.
210 ---------------------------------------------------------------------
211 FUNCTION check_uniqueness(
212 p_table_name IN VARCHAR2,
213 p_where_clause IN VARCHAR2
214 )
215 RETURN VARCHAR2
216 IS
217
218 l_sql VARCHAR2(4000);
219 l_count NUMBER;
220
221 l_bind_tbl col_val_tbl;
222
223 BEGIN
224
225 l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || UPPER(p_table_name);
226 -- l_sql := l_sql || ' WHERE ' || p_where_clause;
227
228 bind_parse (p_where_clause, l_bind_tbl);
229
230 --
231 -- Support up to 4 WHERE conditions for uniqueness. If
232 -- the number of conditions changes, then must also revise
233 -- the execute portion of the code.
234 IF l_bind_tbl.COUNT <= 4 THEN
235 l_sql := l_sql || ' WHERE ' || l_bind_tbl(1).col_name || ' ' || l_bind_tbl(1).col_op || ' :b1';
236 FOR i IN 2..l_bind_tbl.COUNT LOOP
237 l_sql := l_sql || ' AND ' || l_bind_tbl(i).col_name || ' ' || l_bind_tbl(i).col_op || ' :b' || i;
238 END LOOP;
239 ELSE
240 -- Exceeded the number of conditions supported
241 -- for bind variables.
242 l_sql := l_sql || ' WHERE ' || p_where_clause;
243 END IF;
244
245 l_sql := l_sql || ')';
246
247 IF (AMW_DEBUG_HIGH_ON) THEN
248 debug_message('SQL statement: '||l_sql);
249 END IF;
250
251
252 BEGIN
253 IF l_bind_tbl.COUNT = 1 THEN
254 EXECUTE IMMEDIATE l_sql INTO l_count
255 USING l_bind_tbl(1).col_value;
256 ELSIF l_bind_tbl.COUNT = 2 THEN
257 EXECUTE IMMEDIATE l_sql INTO l_count
258 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value;
259 ELSIF l_bind_tbl.COUNT = 3 THEN
260 EXECUTE IMMEDIATE l_sql INTO l_count
261 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value, l_bind_tbl(3).col_value;
262 ELSIF l_bind_tbl.COUNT = 4 THEN
263 EXECUTE IMMEDIATE l_sql INTO l_count
264 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value, l_bind_tbl(3).col_value, l_bind_tbl(4).col_value;
265 ELSE
266 EXECUTE IMMEDIATE l_sql INTO l_count;
267 END IF;
268 EXCEPTION
269 WHEN NO_DATA_FOUND THEN
270 l_count := 0;
271 END;
272
273 IF l_count = 0 THEN
274 RETURN FND_API.g_true;
275 ELSE
276 RETURN FND_API.g_false;
277 END IF;
278
279 END check_uniqueness;
280
281
282 ---------------------------------------------------------------------
283 -- FUNCTION
284 -- is_Y_or_N
285 --
286 -- HISTORY
287 -- 4/20/2003 mpande Created.
288 ---------------------------------------------------------------------
289 FUNCTION is_Y_or_N(
290 p_value IN VARCHAR2
291 )
292 RETURN VARCHAR2
293 IS
294 BEGIN
295 IF p_value = 'Y' or p_value = 'N' THEN
296 RETURN FND_API.g_true;
297 ELSE
298 RETURN FND_API.g_false;
299 END IF;
300 END is_Y_or_N;
301
302
303 ---------------------------------------------------------------------
304 -- PROCEDURE
305 -- debug_message
306 --
307 -- HISTORY
308 -- 4/20/2003 mpande Created.
309 ---------------------------------------------------------------------
310 PROCEDURE debug_message(
311 p_message_text IN VARCHAR2,
312 p_message_level IN NUMBER := NULL
313 )
314 IS
315 BEGIN
316 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH) THEN
317 FND_MESSAGE.set_name('AMW', 'AMW_API_DEBUG_MESSAGE');
318 FND_MESSAGE.set_token('TEXT', REPLACE (p_message_text, FND_API.G_MISS_CHAR, 'G_MISS_CHAR'));
319 FND_MSG_PUB.add;
320 END IF;
321 END debug_message;
322
323
324 ---------------------------------------------------------------------
325 -- PROCEDURE
326 -- error_message
327 --
328 -- HISTORY
329 -- 4/20/2003 mpande Created.
330 ---------------------------------------------------------------------
331 PROCEDURE error_message(
332 p_message_name VARCHAR2,
333 p_token_name VARCHAR2 := NULL,
334 P_token_value VARCHAR2 := NULL
335 )
336 IS
337 BEGIN
338 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
339 FND_MESSAGE.set_name('AMW', p_message_name);
340 IF p_token_name IS NOT NULL THEN
341 FND_MESSAGE.set_token(p_token_name, p_token_value, TRUE);
342 END IF;
343 FND_MSG_PUB.add;
344 END IF;
345 END error_message;
346
347
348 ---------------------------------------------------------------------
349 -- PROCEDURE
350 -- display_messages
351 --
352 -- HISTORY
353 -- 4/20/2003 mpande Created.
354 ---------------------------------------------------------------------
355 PROCEDURE display_messages
356 IS
357 l_count NUMBER;
358 l_msg VARCHAR2(2000);
359 BEGIN
360 l_count := FND_MSG_PUB.count_msg;
361 FOR i IN 1 .. l_count LOOP
362 l_msg := FND_MSG_PUB.get(i, FND_API.g_false);
363 -- DBMS_OUTPUT.put_line('(' || i || ') ' || l_msg);
364 END LOOP;
365 END display_messages;
366
367
368
369 ---------------------------------------------------------------------
370 -- PROCEDURE
371 -- bind_parse
372 -- USAGE
373 -- bind_parse (varchar2, col_val_tbl);
374 -- The input string must have a space between the AND and operator clause
375 -- and it must exclude the initial WHERE/AND statement.
376 -- Example: source_code = 'xyz' and campaign_id <> 1
377 ---------------------------------------------------------------------
378 PROCEDURE bind_parse (
379 p_string IN VARCHAR2,
380 x_col_val_tbl OUT NOCOPY col_val_tbl)
381 IS
382 l_new_str VARCHAR2(4000);
383 l_str VARCHAR2(4000) := p_string;
384 l_curr_pos NUMBER; -- the position index of the operator string
385 l_eq_pos NUMBER;
386 l_not_pos NUMBER;
387 l_and_pos NUMBER;
388 i NUMBER := 1;
389 BEGIN
390 LOOP
391 l_and_pos := INSTR (UPPER (l_str), ' AND ');
392 -- handle condition where no more AND's are
393 -- left -- usually if only one condition or
394 -- the last condition in the WHERE clause.
395 IF l_and_pos = 0 THEN
396 l_new_str := l_str;
397 ELSE
398 l_new_str := SUBSTR (l_str, 1, l_and_pos - 1);
399 END IF;
400
401 --
402 -- The operator should also be passed
406 --
403 -- back to the calling program.
404 l_eq_pos := INSTR (l_new_str, '=');
405 l_not_pos := INSTR (l_new_str, '<>');
407 -----------------------------------
408 -- operator equal not equal
409 -- error 0 0
410 -- = 1 0
411 -- <> 0 1
412 -- = 1 2
413 -- <> 2 1
414 -----------------------------------
415 IF l_eq_pos = 0 AND l_not_pos = 0 THEN
416 -- Could not find either an = or an <>
417 -- operator.
418 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
419 FND_MESSAGE.set_name('AMW', 'AMW_UTIL_NO_WHERE_OPERATOR');
420 FND_MSG_PUB.add;
421 RAISE FND_API.g_exc_unexpected_error;
422 END IF;
423 ELSIF l_eq_pos > 0 AND l_not_pos = 0 THEN
424 l_curr_pos := l_eq_pos;
425 x_col_val_tbl(i).col_op := '=';
426 ELSIF l_not_pos > 0 AND l_eq_pos = 0 THEN
427 l_curr_pos := l_not_pos;
428 x_col_val_tbl(i).col_op := '<>';
429 ELSIF l_eq_pos < l_not_pos THEN
430 l_curr_pos := l_eq_pos;
431 x_col_val_tbl(i).col_op := '=';
432 ELSE
433 l_curr_pos := l_not_pos;
434 x_col_val_tbl(i).col_op := '<>';
435 END IF;
436
437 x_col_val_tbl(i).col_name := UPPER (LTRIM (RTRIM (SUBSTR (l_new_str, 1, l_curr_pos - 1))));
438 -- Add 2 to the current position for '<>'.
439 x_col_val_tbl(i).col_value := LTRIM (RTRIM (SUBSTR (l_new_str, l_curr_pos + 2)));
440 --
441 -- Remove the single quotes from the begin and end of the string value;
442 -- no action if a numeric value.
443 IF INSTR (x_col_val_tbl(i).col_value, '''', 1) = 1 THEN
444 x_col_val_tbl(i).col_value := SUBSTR (x_col_val_tbl(i).col_value,2);
445 x_col_val_tbl(i).col_value := SUBSTR (x_col_val_tbl(i).col_value, 1, LENGTH(x_col_val_tbl(i).col_value) - 1);
446 END IF;
447
448 IF l_and_pos = 0 THEN
449 EXIT; -- no more to parse
450 END IF;
451
452 l_str := SUBSTR (l_str, l_and_pos + 4);
453 i := i + 1;
454 END LOOP;
455 END bind_parse;
456
457 -------------------------------------------------------------------------------
458 -- Start of Comments
459 -- NAME
460 -- Get_Person_Role
461 --
462 -- PURPOSE
463 -- This Procedure will be return the User role for
464 -- the userid sent
465 -- Called By
466 -- NOTES
467 -- HISTORY
468 -- 11/17/2003 MUMU PANDE CREATION
469 -- End of Comments
470 -------------------------------------------------------------------------------
471 PROCEDURE Get_person_Role(
472 p_person_id IN NUMBER,
473 x_role_name OUT NOCOPY VARCHAR2,
474 x_role_display_name OUT NOCOPY VARCHAR2 ,
475 x_return_status OUT NOCOPY VARCHAR2) IS
476 l_employee_id FND_USER.EMPLOYEE_ID%TYPE ;
477 BEGIN
478 x_return_status := FND_API.G_RET_STS_SUCCESS;
479
480
481 -- Pass the Employee Id (which is Person Id) to get the Role
482 WF_DIRECTORY.getrolename(
483 p_orig_system => 'PER',
484 p_orig_system_id => p_person_id ,
485 p_name => x_role_name,
486 p_display_name => x_role_display_name );
487
488 IF x_role_name is null then
489 x_return_status := FND_API.G_RET_STS_ERROR;
490 FND_MESSAGE.Set_Name('AMW','AMW_APPR_INVALID_ROLE');
491 FND_MSG_PUB.Add;
492 END IF;
493 END Get_person_Role;
494
495
496
497 --======================================================================
498 -- Procedure Name: send_wf_standalone_message
499 -- Type : Generic utility
500 -- Pre-Req :
501 -- Notes:
502 -- Common utility to send standalone message without initiating
503 -- process using workflow.
504 -- Parameters:
505 -- IN:
506 -- p_item_type IN VARCHAR2 Required Default = "AMWGUTIL"
507 -- item type for the workflow utility.
508 -- p_message_name IN VARCHAR2 Required Default = "GEN_STDLN_MESG"
509 -- Internal name for standalone message name
510 -- p_subject IN VARCHAR2 Required
511 -- Subject for the message
512 -- p_body IN VARCHAR2 Optional
513 -- Body for the message
514 -- p_send_to_role_name IN VARCHAR2 Optional
515 -- Role name to whom message is to be sent.
516 -- Instead of this, one can send even p_send_to_res_id
517 -- p_send_to_person_id IN NUMBER Optional
518 -- Person Id that will be used to get role name from WF_DIRECTORY.
519 -- This is required if role name is not passed.
520
521 -- OUT:
522 -- x_notif_id OUT NUMBER
523 -- Notification Id created that is being sent to recipient.
524 -- x_return_status OUT VARCHAR2
525 -- Return status. If it is error, messages will be put in mesg pub.
526 -- History:
527 -- 4/20/2003 mpande Created.
531 p_item_type IN VARCHAR2 := 'AMWGUTIL'
528 --======================================================================
529
530 PROCEDURE send_wf_standalone_message(
532 ,p_message_name IN VARCHAR2 := 'GEN_STDLN_MESG'
533 ,p_subject IN VARCHAR2
534 ,p_body IN VARCHAR2 := NULL
535 ,p_send_to_role_name IN VARCHAR2 := NULL
536 ,p_send_to_person_id IN NUMBER := NULL
537 ,x_notif_id OUT NOCOPY NUMBER
538 ,x_return_status OUT NOCOPY VARCHAR2
539 )
540 IS
541 l_role_name VARCHAR2(100) := p_send_to_role_name;
542 l_display_role_name VARCHAR2(240);
543 l_notif_id NUMBER;
544
545 BEGIN
546 x_return_status := FND_API.G_RET_STS_SUCCESS;
547 IF p_send_to_role_name IS NULL THEN
548
549 AMW_UTILITY_PVT.get_person_role
550 ( p_person_id => p_send_to_person_id,
551 x_role_name => l_role_name,
552 x_role_display_name => l_display_role_name,
553 x_return_status => x_return_status
554 );
555
556 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
557 return;
558 END IF;
559 END IF;
560 l_notif_id := WF_NOTIFICATION.Send
561 ( role => l_role_name
562 , msg_type => p_item_type
563 , msg_name => p_message_name
564 );
565 WF_NOTIFICATION.SetAttrText( l_notif_id
566 , 'GEN_MSG_SUBJECT'
567 , p_subject
568 );
569 WF_NOTIFICATION.SetAttrText( l_notif_id
570 , 'GEN_MSG_BODY'
571 , p_body
572 );
573 WF_NOTIFICATION.SetAttrText( l_notif_id
574 , 'GEN_MSG_SEND_TO'
575 , l_role_name
576 );
577 WF_NOTIFICATION.Denormalize_Notification(l_notif_id);
578 x_notif_id := l_notif_id;
579 END send_wf_standalone_message;
580
581
582 ---------------------------------------------------------------------
583 -- FUNCTION
584 -- Find_Hierarchy_Level
585 -- PURPOSE
586 -- This function returns the level in hierarchy of an entity
587 -- to be displayed on the HGrid
588 -- HISTORY
589 -- 4/23/2003 abedajna created.
590 ---------------------------------------------------------------------
591 FUNCTION Find_Hierarchy_Level(
592 entity_name IN VARCHAR2
593 )
594 Return number
595 IS
596 Hier_Level number;
597 BEGIN
598 begin
599 /* mpande commented
600 select HLevel
601 into Hier_Level
602 from AMW_HIERARCHY_LEVELS_V
603 where Process_Name = entity_name;
604 exception
605 when others then
606 Hier_Level := null;
607 */
608 NULL;
609 end;
610
611 return Hier_Level;
612
613 END Find_Hierarchy_Level;
614
615 ---------------------------------------------------------------------
616 -- FUNCTION
617 -- get_lookup_meaning
618 -- USAGE
619 -- Example:
620 -- SELECT AMw_Utility_PVT.get_lookup_meaning ('AMS_RISK_STATUS', status_code)
621 -- FROM amw ....;
622 -- HISTORY
623 -- 6/4/2003 mpande Created.
624 ---------------------------------------------------------------------
625 FUNCTION get_lookup_meaning (
626 p_lookup_type IN VARCHAR2,
627 p_lookup_code IN VARCHAR2
628 )
629 RETURN VARCHAR2
630 IS
631 l_meaning VARCHAR2(80);
632
633 CURSOR c_meaning IS
634 SELECT meaning
635 FROM amw_lookups
636 WHERE lookup_type = UPPER (p_lookup_type)
637 AND lookup_code = UPPER (p_lookup_code);
638 BEGIN
639 OPEN c_meaning;
640 FETCH c_meaning INTO l_meaning;
641 CLOSE c_meaning;
642
643 RETURN l_meaning;
644 END get_lookup_meaning;
645
646
647 ---------------------------------------------------------------------
648 -- FUNCTION
649 -- get_employess_name
650 -- USAGE
651 -- Example:
652 -- SELECT AMW_Utility_PVT.get_employee_name (party_id)
653 -- FROM dual
654 -- HISTORY
655 -- 6/19/2003 mpande Created.
656 ---------------------------------------------------------------------
657 FUNCTION get_employee_name (
658 p_party_id IN VARCHAR2
659 )
660 RETURN VARCHAR2
661 IS
662 l_name VARCHAR2(360);
663
664 CURSOR c_employee_name IS
665 SELECT full_name
666 FROM amw_employees_current_v
667 WHERE party_id = p_party_id;
668 BEGIN
669 IF p_party_id IS NULL THEN
670 RETURN NULL;
671 END IF;
672
673 OPEN c_employee_name;
674 FETCH c_employee_name INTO l_name;
675 CLOSE c_employee_name;
676
677 RETURN l_name;
678 END get_employee_name;
679
680
681 FUNCTION GET_LOOKUP_VALUE(p_lookup_type in varchar2,
682 p_lookup_code in varchar2) return varchar2 is
686 into l_meaning
683 l_meaning varchar2(80);
684 begin
685 select meaning
687 from amw_lookups
688 where lookup_type = p_lookup_type
689 and lookup_code = p_lookup_code;
690
691 return l_meaning;
692 exception
693 when no_data_found then
694 return null;
695 when others then
696 return null;
697 end;
698
699
700
701 ---------------------------------------------------------------------
702 -- PROCEDURE
703 -- wait_for_req
704 -- USAGE
705 -- PL/SQL wrapper package over FND_CONCURRENT.WAIT_FOR_REQUEST that
706 -- follows api calling from java standards
707 -- HISTORY
708 -- 8/22/2003 ABEDAJNA Created.
709 ---------------------------------------------------------------------
710
711 procedure wait_for_req (
712 p_request_id IN NUMBER,
713 p_interval IN number,
714 p_max_wait IN number,
715 p_phase OUT nocopy varchar2,
716 p_status OUT nocopy varchar2,
717 p_dev_phase OUT nocopy varchar2,
718 p_dev_status OUT nocopy varchar2,
719 p_message OUT nocopy varchar2,
720 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
721 x_return_status out nocopy varchar2,
722 x_msg_count out nocopy number,
723 x_msg_data out nocopy varchar2
724 ) is
725
726 L_API_NAME CONSTANT VARCHAR2(30) := 'wait_for_req';
727 wait boolean;
728
729 begin
730
731 x_return_status := FND_API.G_RET_STS_SUCCESS;
732 IF FND_API.to_Boolean( p_init_msg_list ) THEN
733 FND_MSG_PUB.initialize;
734 END IF;
735 IF FND_GLOBAL.User_Id IS NULL THEN
736 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
737 RAISE FND_API.G_EXC_ERROR;
738 END IF;
739
740 wait := FND_CONCURRENT.WAIT_FOR_REQUEST(p_request_id, p_interval, p_max_wait, p_phase, p_status, p_dev_phase, p_dev_status, p_message);
741
742 exception
743
744 WHEN FND_API.G_EXC_ERROR THEN
745 ROLLBACK;
746 x_return_status := FND_API.G_RET_STS_ERROR;
747 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
748
749 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
750 ROLLBACK;
751 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
752 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
753
754 WHEN OTHERS THEN
755 ROLLBACK;
756 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
757 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
758 THEN
759 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
760 END IF;
761 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
762
763 end wait_for_req;
764
765
766
767 ---------------------------------------------------------------------
768 -- PROCEDURE
769 -- get_lob_meaning
770 -- HISTORY
771 -- 9/16/2003 ABEDAJNA Created.
772 ---------------------------------------------------------------------
773
774 FUNCTION get_lob_meaning(p_lob_name in varchar2) return varchar2 is
775 l_lob_desc varchar2(240);
776 begin
777 select ffvt.description
778 into l_lob_desc
779 from
780 fnd_id_flex_structures fft,
781 fnd_segment_attribute_values fsav,
782 fnd_id_flex_segments seg,
783 fnd_flex_values ffv,
784 fnd_flex_values_tl ffvt
785 where fft.application_id = 450
786 and fft.id_flex_code = 'FII#'
787 and fft.id_flex_structure_code = 'DBI_DEFAULT_STRUCTURE'
788 and fsav.application_id = 450
789 and fsav.id_flex_code = 'FII#'
790 and fsav.id_flex_num = fft.id_flex_num
791 and fsav.segment_attribute_type = 'FII_LOB'
792 and fsav.attribute_value = 'Y'
793 and seg.application_id=450
794 and seg.id_flex_code='FII#'
795 and seg.id_flex_num = fft.id_flex_num
796 and seg.application_column_name=fsav.application_column_name
797 and seg.flex_value_set_id = ffv.flex_value_set_id
798 and ffv.summary_flag = 'N'
799 and ffv.flex_value_id = ffvt.flex_value_id
800 and ffvt.language=userenv('LANG')
801 and ffvt.flex_value_meaning = p_lob_name;
802
803 return l_lob_desc;
804 exception
805 when no_data_found then
806 return null;
807 when others then
808 return null;
809 end;
810
811
812
813
814
815 ---------------------------------------------------------------------
816 -- PROCEDURE
817 -- get_process_name
818 -- HISTORY
819 -- 11/25/2003 ABEDAJNA Created.
820 ---------------------------------------------------------------------
821
822
823 FUNCTION get_process_name(p_process_id in number) return varchar2 is
824 l_process_name varchar2(240);
825 begin
826 select watl.display_name into l_process_name
827 from wf_activities_tl watl, wf_activities wa, amw_process ap
828 where ap.process_id = p_process_id
829 and ap.name = wa.name
830 and wa.item_type = 'AUDITMGR'
831 and wa.end_date is null
832 and watl.name = wa.name
833 and watl.item_type = 'AUDITMGR'
834 and watl.version = wa.version
838 exception
835 and watl.language = userenv('LANG');
836
837 return l_process_name;
839 when no_data_found then
840 return null;
841 when others then
842 return null;
843 end;
844
845
846
847
848 FUNCTION get_message_text(p_message_name in varchar2) return varchar2 is
849 l_message_text varchar2(4000);
850 begin
851 fnd_message.set_name('AMW',p_message_name);
852 l_message_text := fnd_message.get;
853 return l_message_text;
854 end get_message_text;
855
856 FUNCTION get_risk_name(p_risk_id in number) return varchar2 is
857 l_risk_name varchar2(240);
858
859 begin
860 select rt.name into l_risk_name
861 from amw_risks_all_vl rt
862 where
863 rt.risk_id = p_risk_id and
864 rt.LATEST_REVISION_FLAG = 'Y';
865
866 return l_risk_name;
867
868 exception
869 when others then
870 return null;
871 end get_risk_name;
872
873 FUNCTION get_control_name(p_control_id in number) return varchar2 is
874 l_control_name varchar2(240);
875
876 begin
877 select ct.name into l_control_name
878 from amw_controls_all_vl ct
879 where
880 ct.control_id = p_control_id and
881 ct.LATEST_REVISION_FLAG = 'Y';
882
883 return l_control_name;
884
885 exception
886 when others then
887 return null;
888 end get_control_name;
889
890 FUNCTION get_organization_name(p_organization_id in number) return varchar2 is
891 l_organization_name varchar2(240);
892
893 begin
894 select ot.name into l_organization_name
895 from amw_audit_units_v ot
896 where
897 ot.organization_id = p_organization_id;
898
899 return l_organization_name;
900
901 exception
902 when others then
903 return null;
904 end get_organization_name;
905
906
907
908 ---------------------------------------------------------------------
909 -- PROCEDURE
910 -- get_proc_org_cert_status
911 -- HISTORY
912 -- 11/25/2003 ABEDAJNA Created.
913 ---------------------------------------------------------------------
914
915
916 FUNCTION get_proc_org_opinion_status(p_process_id in number, p_org_id in number, p_mode in varchar2) return varchar2 is
917
918 l_last_audit_status varchar2(240);
919
920 begin
921
922 select audit_result
923 into l_last_audit_status
924 from amw_opinions_v
925 where pk1_value = p_process_id and pk3_value = p_org_id
926 and object_opinion_type_id =
927 (select object_opinion_type_id from AMW_OBJECT_OPINION_TYPES
928 where opinion_type_id = (select opinion_type_id from amw_opinion_types_b where opinion_type_code = p_mode)
929 and object_id = (select object_id from fnd_objects where obj_name = 'AMW_ORG_PROCESS') )
930 and last_update_date =
931 (select max(last_update_date) from amw_opinions_v
932 where pk1_value = p_process_id and pk3_value = p_org_id
933 and object_opinion_type_id =
934 (select object_opinion_type_id from AMW_OBJECT_OPINION_TYPES
935 where opinion_type_id = (select opinion_type_id from amw_opinion_types_b where opinion_type_code = p_mode)
936 and object_id = (select object_id from fnd_objects where obj_name = 'AMW_ORG_PROCESS') ) );
937
938 return l_last_audit_status;
939
940 exception
941 when no_data_found then
942 return null;
943 when others then
944 return null;
945
946 end get_proc_org_opinion_status;
947
948
949
950 FUNCTION get_proc_org_opinion_date(p_process_id in number, p_org_id in number, p_mode in varchar2) return varchar2 is
951
952 l_last_update_date date;
953
954 begin
955
956 select max(last_update_date) into l_last_update_date from amw_opinions_v
957 where pk1_value = p_process_id and pk3_value = p_org_id
958 and object_opinion_type_id =
959 (select object_opinion_type_id from AMW_OBJECT_OPINION_TYPES
960 where opinion_type_id = (select opinion_type_id from amw_opinion_types_b where opinion_type_code = p_mode)
961 and object_id = (select object_id from fnd_objects where obj_name = 'AMW_ORG_PROCESS') );
962
963 return l_last_update_date;
964
965 exception
966 when no_data_found then
967 return null;
968 when others then
969 return null;
970
971 end get_proc_org_opinion_date;
972
973
974 FUNCTION get_exception_name(p_type in varchar2, p_exception_id in number) return varchar2
975 is
976 l_process_name varchar2(240);
977 begin
978 if p_type = 'A' then
979 select new_process_name into l_process_name
980 from amw_exceptions_tl aetl
981 where
982 aetl.exception_id = p_exception_id
983 and aetl.language = userenv('LANG');
984 elsif p_type='D' then
985 select old_process_name into l_process_name
986 from amw_exceptions_tl aetl
987 where
988 aetl.exception_id = p_exception_id
989 and aetl.language = userenv('LANG');
990 else
991 return null;
992 end if;
993 return l_process_name;
997 when others then
994 exception
995 when no_data_found then
996 return null;
998 return null;
999 end;
1000
1001
1002
1003 procedure isUserProcessOwner (
1004 p_pk IN number,
1005 p_userid IN number,
1006 p_objectContext IN varchar2,
1007 p_retval OUT nocopy varchar2,
1008 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1009 x_return_status out nocopy varchar2,
1010 x_msg_count out nocopy number,
1011 x_msg_data out nocopy varchar2
1012 ) is
1013
1014 L_API_NAME CONSTANT VARCHAR2(30) := 'isUserProcessOwner';
1015 process_owner_id number;
1016 f_party number;
1017
1018 begin
1019
1020 x_return_status := FND_API.G_RET_STS_SUCCESS;
1021 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1022 FND_MSG_PUB.initialize;
1023 END IF;
1024 IF FND_GLOBAL.User_Id IS NULL THEN
1025 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
1026 RAISE FND_API.G_EXC_ERROR;
1027 END IF;
1028
1029 if (p_pk is null) or (p_userid is null) or (p_objectContext is null) then
1030 p_retval := 'N';
1031 else
1032 if p_objectContext = 'PROCESS' then
1033 select Process_Owner_Id
1034 into process_owner_id
1035 from amw_process
1036 where process_id = p_pk;
1037 elsif p_objectContext = 'PROCESS_ORG' then
1038 select Process_Owner_Id
1039 into process_owner_id
1040 from amw_process_organization
1041 where process_organization_id = p_pk;
1042 end if;
1043
1044 select person_party_id
1045 into f_party
1046 from fnd_user
1047 where user_id = p_userid;
1048
1049 if f_party = process_owner_id then
1050 p_retval := 'Y';
1051 else
1052 p_retval := 'N';
1053 end if;
1054 end if;
1055
1056
1057 exception
1058
1059 WHEN FND_API.G_EXC_ERROR THEN
1060 ROLLBACK;
1061 x_return_status := FND_API.G_RET_STS_ERROR;
1062 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1063
1064 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1065 ROLLBACK;
1066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1067 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1068
1069 WHEN OTHERS THEN
1070 ROLLBACK;
1071 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1072 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1073 THEN
1074 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1075 END IF;
1076 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1077
1078 end isUserProcessOwner;
1079
1080 ---------------------------------------------------------------------
1081 --FUNCTION
1082 -- get_risktype_text
1083 -- Notes :
1084 -- gives the Risk Type heading for webadi columns
1085 -- Parameters :
1086 -- p_type :- p_risktype_token , The risk type name
1087 -- HISTORY
1088 --07/30/2004 KOSRINIV Created
1089 ---------------------------------------------------------------------
1090
1091 FUNCTION get_risktype_text(p_risktype_token in varchar2) return varchar2 is
1092 l_message_text varchar2(4000);
1093 begin
1094 fnd_message.set_name('AMW','AMW_WEBADI_RISK_TYPE_TEXT');
1095 fnd_message.set_token('RISK_TYPE_NAME',p_risktype_token);
1096 l_message_text := fnd_message.get;
1097 return l_message_text;
1098 end get_risktype_text;
1099
1100
1101 ---------------------------------------------------------------------
1102 --FUNCTION
1103 -- get_parameter_type
1104 -- Notes :
1105 -- gives the value of the parameter given the column name of the
1106 -- pareameter name for an organization from amw_library_parameter
1107 -- Parameters :
1108 -- p_org_id :- Organization_id ( -1 if for RiskLibrary)
1109 -- p_param_name :- column name of the parameter
1110 -- EXAMPLE:- get_parameter(3970,'PROCESS_APPROVAL_OPTION')
1111 -- gives the value of parameter 'PROCESS_APPROVAL_OPTION' for organization with id 3970.
1112 -- HISTORY
1113 --11/05/2004 KOSRINIV Created
1114 ---------------------------------------------------------------------
1115
1116 FUNCTION get_parameter(p_org_id in number, p_param_name in varchar2) return varchar2 is
1117
1118 l_value VARCHAR2(80);
1119
1120 BEGIN
1121
1122 if p_param_name = 'PROCESS_APPROVAL_OPTION' then
1123 if g_appr_values_cached then
1124 if g_appr_opt_val.exists(p_org_id) then
1125 return g_appr_opt_val(p_org_id);
1126 else
1127 null;
1128 end if;
1129 end if;
1130 end if;
1131
1132 SELECT PARAMETER_VALUE INTO l_value
1133 FROM AMW_PARAMETERS
1134 WHERE PARAMETER_NAME = p_param_name
1135 AND PK1 = p_org_id;
1136 return l_value;
1137 EXCEPTION
1138 WHEN NO_DATA_FOUND THEN
1139 return null;
1140 WHEN OTHERS THEN
1141 return null;
1142 END get_parameter;
1143
1144
1145 FUNCTION get_process_name_approved (p_process_id in number) return varchar2
1146 is
1147 l_display_name amw_process_names_tl.display_name%type;
1148 begin
1149 select display_name into l_display_name
1150 from amw_process amwp,
1151 amw_process_names_tl amwp_tl
1152 where amwp.process_rev_id = amwp_tl.process_rev_id and
1153 amwp.process_id = p_process_id and
1154 amwp.approval_date is not null and
1155 amwp.approval_end_date is null and
1156 amwp_tl.language=userenv('LANG');
1157
1158 return l_display_name;
1159
1160 exception
1161 when no_data_found then
1162 return null;
1163 when others then
1164 return null;
1165
1166
1167 end get_process_name_approved;
1168
1169 FUNCTION get_process_name_by_status(p_process_id in number,
1170 p_status in varchar2) return varchar2
1171
1172 is
1173 l_display_name amw_process_names_tl.display_name%type;
1174 begin
1175
1176 if(p_status = 'L')
1177 then
1178 select display_name into l_display_name
1179 from amw_process amwp,
1180 amw_process_names_tl amwp_tl
1181 where amwp.process_rev_id = amwp_tl.process_rev_id and
1182 amwp.process_id = p_process_id and
1183 amwp.end_date is null and
1184 amwp_tl.language=userenv('LANG');
1185
1186 return l_display_name;
1187 end if;
1188
1189 if(p_status = 'A')
1190 then
1191 select display_name into l_display_name
1192 from amw_process amwp,
1193 amw_process_names_tl amwp_tl
1194 where amwp.process_rev_id = amwp_tl.process_rev_id and
1195 amwp.process_id = p_process_id and
1196 amwp.approval_date is not null and
1197 amwp.approval_end_date is null and
1198 amwp_tl.language=userenv('LANG');
1199
1200 return l_display_name;
1201 end if;
1202
1203 exception
1204 when no_data_found then
1205 return null;
1206 when others then
1207 return null;
1208
1209 end get_process_name_by_status;
1210
1211 ---------------------------------------------------------------------
1212 --FUNCTION
1213 -- get_approved_org_process_name
1214 -- Notes :
1215 -- gives the display name of the approved org process revision
1216 -- Parameters :
1217 -- p_process_id :- process Id ,p_org_id :- org Id
1218 -- HISTORY
1219 --11/06/2004 KOSRINIV Created
1220 ---------------------------------------------------------------------
1221 FUNCTION get_approved_org_process_name (p_process_id in number, p_org_id in number) return varchar2
1222 is
1223 l_display_name amw_process_names_tl.display_name%type;
1224 begin
1225 select display_name into l_display_name
1226 from amw_process_organization apo,
1227 amw_process_names_tl amwp_tl
1228 where apo.approval_date is not null
1229 and apo.approval_end_date is null
1230 and apo.rl_process_rev_id = amwp_tl.process_rev_id
1231 and apo.process_id = p_process_id
1232 and apo.organization_id = p_org_id
1233 and amwp_tl.language=userenv('LANG');
1234 return l_display_name;
1235
1236 exception
1237 when no_data_found then
1238 return null;
1239 when others then
1240 return null;
1241 end get_approved_org_process_name;
1242
1243 FUNCTION is_process_locked(p_process_id in number, p_org_id in number) return varchar2
1244 is
1245 l_dummy number;
1246 begin
1247
1248 if p_process_id IS null then
1249 select 1 INTO l_dummy
1250 from amw_process_locks
1251 where organization_id = p_org_id;
1252 else
1253 select 1
1254 into l_dummy
1255 from amw_process_locks
1256 where locked_process_id = p_process_id
1257 and organization_id = p_org_id;
1258 end if;
1259
1260 return 'Y';
1261 exception
1262 when no_data_found then
1263 return 'N';
1264
1265 when too_many_rows then
1266 return 'Y';
1267 end is_process_locked;
1268
1269 --===================================================================================
1270 FUNCTION get_project_count( p_org_id in number) return number
1271 is
1272 l_dummy number:=0;
1273 begin
1274
1275 SELECT COUNT(AP.AUDIT_PROJECT_ID) into l_dummy
1276 FROM AMW_AUDIT_PROJECTS AP,
1277 AMW_EXECUTION_SCOPE AES
1278 WHERE
1279 AP.AUDIT_PROJECT_ID = AES.ENTITY_ID
1280 AND AES.ENTITY_TYPE = 'PROJECT'
1281 AND AES.LEVEL_ID = 3
1282 AND AP.AUDIT_PROJECT_STATUS = 'ACTI'
1283 AND AES.ORGANIZATION_ID = p_org_id;
1284 /* Commenting the code which returning the wrong count..
1285 SELECT COUNT(APV.AUDIT_PROJECT_ID) into l_dummy
1286 FROM AMW_AUDIT_PROJECTS_V APV,
1287 AMW_ENTITY_HIERARCHIES AEH
1288 WHERE
1289 APV.AUDIT_PROJECT_ID = AEH.ENTITY_ID
1290 AND AEH.ENTITY_TYPE = 'PROJECT'
1291 AND AEH.OBJECT_TYPE = 'ORGANIZATION'
1292 AND APV.PROJECT_STATUS_CODE = 'ACTI'
1293 AND AEH.OBJECT_ID = p_org_id;
1294 */
1298 return 0;
1295 return l_dummy;
1296 EXCEPTION
1297 WHEN OTHERS THEN
1299 end get_project_count;
1300 --===================================================================================
1301 FUNCTION get_contrlol_objective_name(p_org_id in number,p_proc_id in number,p_risk_id in number,p_control_id in number)
1302 return varchar2
1303 is
1304 l_dummy varchar2(80):=null;
1305 begin
1306 SELECT APV.name into l_dummy
1307 FROM AMW_PROCESS_OBJECTIVES_VL APV,
1308 AMW_OBJECTIVE_ASSOCIATIONS AOA
1309 WHERE
1310 APV.PROCESS_OBJECTIVE_ID =AOA.PROCESS_OBJECTIVE_ID
1311 AND AOA.PK1 = p_org_id
1312 AND AOA.PK2 = p_proc_id
1313 AND AOA.PK3 = p_risk_id
1314 AND AOA.PK4 = p_control_id
1315 AND AOA.DELETION_DATE IS NULL
1316 AND AOA.OBJECT_TYPE = 'CONTROL_ORG';
1317 return l_dummy;
1318 EXCEPTION
1319 WHEN OTHERS THEN
1320 return NULL;
1321 end get_contrlol_objective_name;
1322 --===================================================================================
1323 FUNCTION get_contrlol_objective_id(p_org_id in number,p_proc_id in number,p_risk_id in number,p_control_id in number)
1324 return NUMBER
1325 is
1326 l_dummy NUMBER :=null;
1327 begin
1328 SELECT AOA.PROCESS_OBJECTIVE_ID into l_dummy
1329 FROM AMW_OBJECTIVE_ASSOCIATIONS AOA
1330 WHERE
1331 AOA.PK1 = p_org_id
1332 AND AOA.PK2 = p_proc_id
1333 AND AOA.PK3 = p_risk_id
1334 AND AOA.PK4 = p_control_id
1335 AND AOA.DELETION_DATE IS NULL
1336 AND AOA.OBJECT_TYPE = 'CONTROL_ORG';
1337 return l_dummy;
1338 EXCEPTION
1339 WHEN OTHERS THEN
1340 return NULL;
1341 end get_contrlol_objective_id;
1342 --===================================================================================
1343 FUNCTION is_contrlol_objective_approved(p_org_id in number,p_proc_id in number,p_risk_id in number,p_control_id in number)
1344 return varchar2
1345 is
1346 l_dummy DATE:=null;
1347 begin
1348 SELECT AOA.approval_date into l_dummy
1349 FROM AMW_OBJECTIVE_ASSOCIATIONS AOA
1350 WHERE AOA.PK1 = p_org_id
1351 AND AOA.PK2 = p_proc_id
1352 AND AOA.PK3 = p_risk_id
1353 AND AOA.PK4 = p_control_id
1354 AND AOA.DELETION_DATE IS NULL
1355 AND AOA.OBJECT_TYPE = 'CONTROL_ORG';
1356 if l_dummy is null then
1357 return 'N';
1358 END IF;
1359 RETURN 'Y';
1360 EXCEPTION
1361 WHEN OTHERS THEN
1362 return 'N';
1363 end is_contrlol_objective_approved;
1364 --===================================================================================================
1365 FUNCTION get_cobj_name_approved(p_org_id in number,p_proc_id in number,p_risk_id in number,p_control_id in number)
1366 return varchar2
1367 is
1368 l_dummy varchar2(80):=null;
1369 begin
1370 SELECT APV.name into l_dummy
1371 FROM AMW_PROCESS_OBJECTIVES_VL APV,
1372 AMW_OBJECTIVE_ASSOCIATIONS AOA
1373 WHERE
1374 APV.PROCESS_OBJECTIVE_ID =AOA.PROCESS_OBJECTIVE_ID
1375 AND AOA.PK1 = p_org_id
1376 AND AOA.PK2 = p_proc_id
1377 AND AOA.PK3 = p_risk_id
1378 AND AOA.PK4 = p_control_id
1379 AND AOA.DELETION_DATE IS NULL
1380 AND AOA.APPROVAL_DATE IS NOT NULL
1381 AND AOA.OBJECT_TYPE = 'CONTROL_ORG';
1382 return l_dummy;
1383 EXCEPTION
1384 WHEN OTHERS THEN
1385 return NULL;
1386 end get_cobj_name_approved;
1387 --======================================================================================================
1388 FUNCTION exist_in_latest_hier(p_org_id in number,p_proc_id in number) return varchar2
1389 is
1390 l_dummy varchar2(1) := 'N';
1391 BEGIN
1392 SELECT 'Y' INTO l_dummy from dual
1393 where exists ( select 1 from amw_latest_hierarchies
1394 where organization_id = p_org_id
1395 and (parent_id = p_proc_id or child_id = p_proc_id));
1396 return l_dummy;
1397 EXCEPTION
1398 WHEN OTHERS THEN
1399 return l_dummy;
1400 end exist_in_latest_hier;
1401 --======================================================================================================
1402
1403 FUNCTION get_proc_change_id(p_org_id in number,p_proc_id in number, p_rev_num in number) return NUMBER
1404 is
1405 l_dummy NUMBER;
1406 BEGIN
1407
1408 if p_org_id = -1 then
1409 select max(ecs.change_id) INTO l_dummy
1410 from eng_change_subjects ecs
1411 ,eng_engineering_changes eec
1412 where ecs.entity_name='AMW_REVISION_ETTY'
1413 and ecs.pk1_value=p_proc_id
1414 and ecs.pk2_value=p_rev_num
1415 and ecs.subject_level=1
1416 and ecs.change_id=eec.change_id
1417 and eec.status_type <> 5;
1418 else
1419 select max(ecs.change_id) INTO l_dummy
1420 from eng_change_subjects ecs, eng_engineering_changes eec
1421 where ecs.entity_name='AMW_ORG_REV_ETTY'
1422 and ecs.pk1_value=p_org_id
1423 and ecs.pk2_value=p_proc_id
1424 and ecs.pk3_value=p_rev_num
1425 and ecs.subject_level=1
1426 and eec.change_id=ecs.change_id
1427 and eec.status_type <> 5;
1428 end if;
1429 return l_dummy;
1430 EXCEPTION
1431 WHEN OTHERS THEN
1432 return l_dummy;
1433 end get_proc_change_id;
1434
1435 --======================================================================================================
1436
1437 FUNCTION has_child_morethan_two(p_proc_id in number,p_org_id in number) return VARCHAR
1438 is
1439 l_dummy NUMBER;
1440 BEGIN
1441
1442 select 1 INTO l_dummy
1443 from amw_latest_hierarchies
1444 where parent_id = p_proc_id
1445 and organization_id = p_org_id;
1446 return 'N';
1447 EXCEPTION
1448 WHEN too_many_rows THEN
1449 return 'Y';
1450 WHEN OTHERS THEN
1451 return 'N';
1452 end has_child_morethan_two;
1453
1454 --=====================================================================================================
1455 FUNCTION is_control_associated_to_risk(p_process_id in number, p_revision_number in number, p_risk_id in number) return varchar2
1456 is
1457 dummy_count NUMBER;
1458 BEGIN
1459 select
1460 count(1) into dummy_count
1461 from
1462 amw_control_associations aca,
1463 amw_process ap
1464 where
1465 aca.pk1 = ap.process_id
1466 and aca.object_type = 'RISK'
1467 and aca.pk1 = ap.process_id
1468 and ap.process_id = p_process_id and ap.revision_number = p_revision_number and aca.pk2= p_risk_id
1469 and ((ap.approval_date is null and ap.end_date is null and aca.deletion_date is null ) OR
1470 (ap.approval_date is not null and aca.approval_date <= ap.approval_date and
1471 (aca.deletion_approval_date is null or aca.deletion_approval_date >= ap.approval_end_date)) );
1472
1473 if dummy_count > 0 then
1474 return GET_LOOKUP_MEANING('AMW_YES_NO','Y');
1475 else
1476 return GET_LOOKUP_MEANING('AMW_YES_NO','N');
1477 end if;
1478
1479 EXCEPTION
1480 WHEN OTHERS THEN
1481 return GET_LOOKUP_MEANING('AMW_YES_NO','N');
1482 end is_control_associated_to_risk;
1483
1484 --=====================================================================================================
1485 FUNCTION is_ctrl_assotd_to_all_risks(p_process_id in number, p_revision_number in number, p_risk_id in number) return varchar2
1486 is
1487 dummy_count NUMBER;
1488 BEGIN
1489 select
1490 ((select count(1)
1491 from
1492 amw_control_associations aca,
1493 amw_process_vl apvl
1494 where
1495 aca.object_type = 'RISK'
1496 and aca.pk1 = apvl.process_id
1497 and aca.pk2 = p_risk_id
1498 and ((apvl.approval_date is null and apvl.end_date is null and aca.deletion_date is null) OR
1499 (apvl.approval_date is not null and apvl.approval_end_date is null and aca.approval_date is not null and aca.deletion_approval_date is null))
1500 and apvl.process_id = p_process_id and apvl.revision_number = p_revision_number)
1501 +
1502 (select count(1)
1503 from
1504 amw_control_associations aca,
1505 Amw_Proc_Hierarchy_Denorm aphd,
1506 amw_process ap
1507 where aphd.process_id = ap.process_id
1508 and aphd.up_down_ind = 'D'
1509 and ap.process_id = aphd.parent_child_id
1510 and aca.object_type = 'RISK'
1511 and aca.pk1 = aphd.parent_child_id
1512 and aca.pk2 = p_risk_id
1513 and ((ap.approval_date is null and ap.end_date is null and aphd.hierarchy_type = 'L' and aca.deletion_date is null) OR
1514 (ap.approval_date is not null and ap.approval_end_date is null and aphd.hierarchy_type = 'A' and aca.approval_date is not null and aca.deletion_approval_date is null)
1515 )
1516 and ap.process_id = aphd.parent_child_id
1517 and ap.process_id = p_process_id and ap.revision_number = p_revision_number))
1518 into dummy_count from dual;
1519
1520 if dummy_count > 0 then
1521 return GET_LOOKUP_MEANING('AMW_YES_NO','Y');
1522 else
1523 return GET_LOOKUP_MEANING('AMW_YES_NO','N');
1524 end if;
1525
1526 EXCEPTION
1527 WHEN OTHERS THEN
1528 return GET_LOOKUP_MEANING('AMW_YES_NO','N');
1529 end is_ctrl_assotd_to_all_risks;
1530 --===================================================================================
1531 FUNCTION is_control_objective_approved(p_process_id in number, p_risk_id in number, p_control_id in number)
1532 return varchar2
1533 is
1534 l_dummy DATE:=null;
1535 begin
1536 SELECT AOA.approval_date into l_dummy
1537 FROM AMW_OBJECTIVE_ASSOCIATIONS AOA
1538 WHERE AOA.PK1 = p_process_id
1539 AND AOA.PK2 = p_risk_id
1540 AND AOA.PK3 = p_control_id
1541 AND AOA.DELETION_DATE IS NULL
1542 AND AOA.OBJECT_TYPE = 'CONTROL';
1543 if l_dummy is null then
1544 return 'N';
1545 END IF;
1546 RETURN 'Y';
1547 EXCEPTION
1548 WHEN OTHERS THEN
1549 return 'N';
1550 end is_control_objective_approved;
1551
1552 --04.01.2005 npanandi: added below method to return
1553 --display value for Ineff Ctrls / Evaluated Ctrls / Total Ctrls
1554 --bug 4201078 fix
1555 function get_display_value(
1556 p_ineff_ctrl in number
1557 ,p_eval_ctrl in number
1558 ,p_total_ctrl in number)
1559 return varchar2
1560 is
1561 l_profile_value varchar2(240) := null;
1562 l_display_value varchar2(10) := '';
1563
1564 l_ineff_ctrl number;
1565 l_eval_ctrl number;
1566 l_total_ctrl number;
1567 begin
1568 l_ineff_ctrl := nvl(p_ineff_ctrl,0);
1569 l_eval_ctrl := nvl(p_eval_ctrl,0);
1570 l_total_ctrl := nvl(p_total_ctrl,0);
1571
1572 l_profile_value := nvl(fnd_profile.VALUE('AMW_OPINION_NUMBERS_OPTION'),'PERCENTAGE');
1573 /*************************************************************************/
1574 /** 'INEFF' --> Only Not Effective **/
1575 /** 'INEFF_TOTAL' --> Not Effective and Total **/
1576 /** 'INEFF_VERFIED' --> Not Effective and Total Verified **/
1577 /** 'INEFF_VERIFIED_TOTAL' --> Not Effective, Total Verified and Total **/
1578 /** 'PERCENTAGE' --> Percentage of Not Effective over Total **/
1579 /*************************************************************************/
1580 if(l_total_ctrl = 0)then
1581 return l_display_value;
1582 end if;
1583
1584 if(l_profile_value = 'INEFF')then
1585 ---dbms_output.put_line( 'INEFF' );
1586 l_display_value := to_char(l_ineff_ctrl);
1587 elsif(l_profile_value = 'INEFF_TOTAL')then
1588 ---dbms_output.put_line( 'INEFF_TOTAL' );
1589 l_display_value := to_char(l_ineff_ctrl)||'/'||to_char(l_total_ctrl);
1590 elsif(l_profile_value = 'INEFF_VERIFIED')then
1591 l_display_value := to_char(l_ineff_ctrl)||'/'||to_char(l_eval_ctrl);
1592 elsif(l_profile_value = 'INEFF_VERIFIED_TOTAL')then
1593 ---dbms_output.put_line( 'INEFF_VERIFIED_TOTAL' );
1594 l_display_value := to_char(l_ineff_ctrl)||'/'||to_char(l_eval_ctrl)||'/'||to_char(l_total_ctrl);
1595 elsif(l_profile_value = 'PERCENTAGE')then
1596 ---dbms_output.put_line( 'PERCENTAGE' );
1597 l_display_value := to_char(round(((l_ineff_ctrl*100)/l_total_ctrl),2))||'%';
1598 end if;
1599
1600 /*if(l_display_value = '0%' or l_display_value = '0')then
1601 l_display_value := '';
1602 end if;*/
1603 return l_display_value;
1604 exception
1605 when others then
1606 return '';
1607 end get_display_value;
1608 --04.01.2005 npanandi: ends
1609
1610 --04.01.2005 npanandi: added below method to return
1611 --display value for Process/Org Certified vs. Total Processes/Orgs
1612 --bug 4201078 fix
1613 function get_display_proc_cert(
1614 p_sub_process_cert in number
1615 ,p_total_sub_process_cert in number) return varchar2
1616 is
1617 l_profile_value varchar2(240) := null;
1618 l_display_value varchar2(10) := '';
1619
1620 l_sub_process_cert number;
1621 l_total_sub_process_cert number;
1622 begin
1623 /*************************************************************************/
1624 /** 'INEFF' --> Only Not Effective **/
1625 /** 'INEFF_TOTAL' --> Not Effective and Total **/
1626 /** 'INEFF_VERFIED' --> Not Effective and Total Verified **/
1627 /** 'INEFF_VERIFIED_TOTAL' --> Not Effective, Total Verified and Total **/
1628 /** 'PERCENTAGE' --> Percentage of Not Effective over Total **/
1629 /*************************************************************************/
1630 l_profile_value := nvl(fnd_profile.VALUE('AMW_OPINION_NUMBERS_OPTION'),'PERCENTAGE');
1631
1632 l_sub_process_cert := nvl(p_sub_process_cert,0);
1633 l_total_sub_process_cert := nvl(p_total_sub_process_cert,0);
1634
1635 /*
1636 if(l_sub_process_cert = 0 or l_total_sub_process_cert = 0)then
1637 return l_display_value;
1638 end if;
1639 */
1640 ---04.08.05 npanandi: return null only if total = 0
1641 if(l_total_sub_process_cert = 0)then
1642 return l_display_value;
1643 end if;
1644
1645 if(l_profile_value = 'INEFF')then
1646 ---dbms_output.put_line( 'INEFF' );
1647 l_display_value := to_char(l_sub_process_cert);
1648 elsif(l_profile_value='INEFF_TOTAL' or l_profile_value='INEFF_VERIFIED' or l_profile_value='INEFF_VERIFIED_TOTAL') then
1649 l_display_value := to_char(l_sub_process_cert)||'/'||to_char(l_total_sub_process_cert);
1650 elsif(l_profile_value='PERCENTAGE') then
1651 l_display_value := to_char(round(((l_sub_process_cert*100)/l_total_sub_process_cert),2))||'%';
1652 end if;
1653
1654 return l_display_value;
1655
1656 exception
1657 when others then
1658 return '';
1659 end get_display_proc_cert;
1660
1661 FUNCTION GET_EX_REASONS(p_action IN VARCHAR2,
1662 p_object IN VARCHAR2,
1663 p_pk1 IN VARCHAR2,
1664 p_pk2 IN VARCHAR2,
1665 p_pk3 IN VARCHAR2,
1666 p_pk4 IN VARCHAR2 := NULL,
1667 p_pk5 IN VARCHAR2 := NULL) RETURN VARCHAR2
1668 IS
1669
1670 CURSOR DEL_EXCEPTION IS
1671 SELECT EXCEPTION_ID
1672 FROM AMW_EXCEPTIONS_B
1673 WHERE OBJECT_TYPE = p_object
1674 AND OLD_PK1 = p_pk1
1675 AND OLD_PK2 = p_pk2
1676 AND OLD_PK3 = p_pk3
1677 AND NVL(OLD_PK4, -99) = NVL(p_pk4, -99)
1678 AND NVL(OLD_PK5, -99) = NVL(p_pk5, -99)
1679 AND APPROVED_FLAG = 'Y'
1680 AND END_DATE IS NULL;
1681
1682 CURSOR ADD_EXCEPTION IS
1683 SELECT EXCEPTION_ID
1684 FROM AMW_EXCEPTIONS_B
1685 WHERE OBJECT_TYPE = p_object
1686 AND NEW_PK1 = p_pk1
1687 AND NEW_PK2 = p_pk2
1688 AND NEW_PK3 = p_pk3
1689 AND NVL(NEW_PK4, -99) = NVL(p_pk4, -99)
1690 AND NVL(NEW_PK5, -99) = NVL(p_pk5, -99)
1691 AND APPROVED_FLAG = 'Y'
1692 AND END_DATE IS NULL;
1693
1694 CURSOR EX_REASONS(p_exception_id in NUMBER) IS
1695 SELECT MEANING
1696 FROM AMW_EXCEPTIONS_REASONS,
1697 AMW_LOOKUPS
1698 WHERE LOOKUP_TYPE = 'AMW_EXCEPTION_REASONS'
1699 AND LOOKUP_CODE = REASON_CODE
1700 AND EXCEPTION_ID = p_exception_id;
1701
1702 l_reasons_string VARCHAR2(4000) :=NULL;
1703 l_exception_id AMW_EXCEPTIONS_B.EXCEPTION_ID%TYPE;
1704 BEGIN
1705
1706 IF p_action = 'DEL' THEN
1707 OPEN DEL_EXCEPTION;
1708 FETCH DEL_EXCEPTION INTO l_exception_id;
1709 IF DEL_EXCEPTION%NOTFOUND THEN
1710 CLOSE DEL_EXCEPTION;
1711 RETURN NULL;
1712 END IF;
1713 CLOSE DEL_EXCEPTION;
1714 ELSIF p_action = 'ADD' THEN
1715 OPEN ADD_EXCEPTION;
1716 FETCH ADD_EXCEPTION INTO l_exception_id;
1717 IF ADD_EXCEPTION%NOTFOUND THEN
1718 CLOSE ADD_EXCEPTION;
1719 RETURN NULL;
1720 END IF;
1721 CLOSE ADD_EXCEPTION;
1722 END IF;
1723 FOR REASONS IN EX_REASONS(l_exception_id) LOOP
1724 EXIT WHEN EX_REASONS%NOTFOUND;
1725 IF l_reasons_string is null then
1726 l_reasons_string := REASONS.MEANING;
1727 else
1728 l_reasons_string := l_reasons_string || ',' || REASONS.MEANING;
1729 end if;
1730 END LOOP;
1731 return l_reasons_string;
1732 EXCEPTION
1733 WHEN OTHERS THEN
1734 RETURN null;
1735 END;
1736
1737 FUNCTION GET_EX_COMMENTS(p_action IN VARCHAR2,
1738 p_object IN VARCHAR2,
1739 p_pk1 IN VARCHAR2,
1740 p_pk2 IN VARCHAR2,
1741 p_pk3 IN VARCHAR2,
1742 p_pk4 IN VARCHAR2 := NULL,
1743 p_pk5 IN VARCHAR2 := NULL) RETURN VARCHAR2
1744 IS
1745
1746 CURSOR DEL_EXCEPTION IS
1747 SELECT EXCEPTION_ID
1748 FROM AMW_EXCEPTIONS_B
1749 WHERE OBJECT_TYPE = p_object
1750 AND OLD_PK1 = p_pk1
1751 AND OLD_PK2 = p_pk2
1752 AND OLD_PK3 = p_pk3
1753 AND NVL(OLD_PK4, -99) = NVL(p_pk4, -99)
1754 AND NVL(OLD_PK5, -99) = NVL(p_pk5, -99)
1755 AND APPROVED_FLAG = 'Y'
1756 AND END_DATE IS NULL;
1757
1758 CURSOR ADD_EXCEPTION IS
1759 SELECT EXCEPTION_ID
1760 FROM AMW_EXCEPTIONS_B
1761 WHERE OBJECT_TYPE = p_object
1762 AND NEW_PK1 = p_pk1
1763 AND NEW_PK2 = p_pk2
1764 AND NEW_PK3 = p_pk3
1765 AND NVL(NEW_PK4, -99) = NVL(p_pk4, -99)
1766 AND NVL(NEW_PK5, -99) = NVL(p_pk5, -99)
1767 AND APPROVED_FLAG = 'Y'
1768 AND END_DATE IS NULL;
1769
1770 CURSOR EX_COMMENTS(p_exception_id in NUMBER) IS
1771 SELECT JUSTIFICATION
1772 FROM AMW_EXCEPTIONS_TL
1773 WHERE EXCEPTION_ID = p_exception_id
1774 AND LANGUAGE = USERENV('LANG');
1775
1776 l_comments VARCHAR2(4000) :=NULL;
1777 l_exception_id AMW_EXCEPTIONS_B.EXCEPTION_ID%TYPE;
1778 BEGIN
1779
1780 IF p_action = 'DEL' THEN
1781 OPEN DEL_EXCEPTION;
1782 FETCH DEL_EXCEPTION INTO l_exception_id;
1783 IF DEL_EXCEPTION%NOTFOUND THEN
1784 CLOSE DEL_EXCEPTION;
1785 RETURN NULL;
1786 END IF;
1787 CLOSE DEL_EXCEPTION;
1788 ELSIF p_action = 'ADD' THEN
1789 OPEN ADD_EXCEPTION;
1790 FETCH ADD_EXCEPTION INTO l_exception_id;
1791 IF ADD_EXCEPTION%NOTFOUND THEN
1792 CLOSE ADD_EXCEPTION;
1793 RETURN NULL;
1794 END IF;
1795 CLOSE ADD_EXCEPTION;
1796 END IF;
1797 OPEN EX_COMMENTS(l_exception_id);
1798 FETCH EX_COMMENTS INTO l_comments;
1799 IF EX_COMMENTS%NOTFOUND THEN
1803 CLOSE EX_COMMENTS;
1800 CLOSE EX_COMMENTS;
1801 RETURN NULL;
1802 END IF;
1804 RETURN l_comments;
1805 EXCEPTION
1806 WHEN OTHERS THEN
1807 RETURN null;
1808 END;
1809
1810 /* This procedure inserts a record into the FND_LOG_MESSAGES table
1811 FND uses an autonomous transaction so even when the hookinsert is
1812 rolled back because of an error the log messages still exists
1813 */
1814 PROCEDURE LOG_MSG( v_object_id IN VARCHAR2
1815 , v_object_name IN VARCHAR2
1816 , v_message IN VARCHAR2
1817 -- , v_level_id IN NUMBER := -1
1818 , v_module IN VARCHAR2)
1819 IS
1820 l_log_level NUMBER;
1821 l_module VARCHAR2(64);
1822 l_message VARCHAR2(4000);
1823
1824 BEGIN
1825
1826 IF (FND_PROFILE.VALUE('AMW_DEBUG') = 'N') THEN
1827 RETURN;
1828 END IF;
1829
1830 l_module := v_module;
1831 -- Convert to the FND_LOG LEVEL
1832 l_log_level := 5 - FND_PROFILE.VALUE('AMW_DEBUG_LEVEL');
1833 --Create the message text
1834 l_message := 'Object '||v_object_name||'-'||v_object_id||' : '||v_message;
1835
1836 /*always log message to fnd_file because it will periodically be purged
1837 log file name can be found in fnd_temp_files. (fnd_temp_file_parameters)*/
1838 fnd_file.put_line (fnd_file.LOG, l_message);
1839 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1840 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_module,l_message);
1841 end if;
1842 ---comment the following for bug5532010 --start
1843 /*
1844
1845 IF g_initialize_log = TRUE THEN
1846 IF FND_LOG_REPOSITORY.CHECK_ACCESS_INTERNAL (l_module, l_log_level) THEN
1847 FND_LOG_REPOSITORY.STRING_UNCHECKED_INTERNAL(l_log_level,
1848 l_module,
1849 l_message,
1850 g_session_id );
1851 END IF;
1852 ELSE */
1853 /* 1. Talk with IBE folks when they encounter the similar problem.
1854 they recommend that to user this user and responsibility,
1855 instead of the user from fnd_global. I will double confirm with FND.
1856 2. Why always forced to use user (5), we will only use it
1857 when there is no valid user
1858 */
1859 /*IF (FND_GLOBAL.USER_ID() is null or
1860 FND_GLOBAL.USER_ID() < -1) THEN
1861 FND_GLOBAL.APPS_INITIALIZE( 5, 20420, 1);
1862 ELSE
1863 FND_GLOBAL.APPS_INITIALIZE( FND_GLOBAL.USER_ID(),
1864 FND_GLOBAL.RESP_ID(),
1865 FND_GLOBAL.RESP_APPL_ID());
1866 END IF;
1867
1868 SELECT amw_debug_log_s.nextval INTO g_session_id FROM DUAL;
1869
1870 FND_LOG_REPOSITORY.INIT( SESSION_ID=> g_session_id);
1871 g_initialize_log := TRUE;
1875 l_message,
1872 IF FND_LOG_REPOSITORY.CHECK_ACCESS_INTERNAL (l_module, l_log_level) THEN
1873 FND_LOG_REPOSITORY.STRING_UNCHECKED_INTERNAL(l_log_level,
1874 l_module,
1876 g_session_id);
1877 END IF;
1878 END IF;*/
1879 EXCEPTION WHEN OTHERS THEN
1880 NULL;
1881 END LOG_MSG;
1882
1883 FUNCTION GET_RISK_CONTROLS_EXIST(p_org_id IN NUMBER, p_process_id IN NUMBER, p_risk_id IN NUMBER, p_appr_date IN DATE) RETURN VARCHAR2
1884 IS
1885 l_dummy varchar2(1):='N';
1886 BEGIN
1887 IF p_appr_date is not null THEN
1888 select 'Y' into l_dummy
1889 from amw_control_associations
1890 where object_type = 'RISK_ORG'
1891 and pk1 = p_org_id
1892 and pk2 = p_process_id
1893 and pk3 = p_risk_id
1894 and approval_date is not null and deletion_approval_date is null;
1895 ELSE
1896 select 'Y' into l_dummy
1897 from amw_control_associations
1898 where object_type = 'RISK_ORG'
1899 and pk1 = p_org_id
1900 and pk2 = p_process_id
1901 and pk3 = p_risk_id
1902 and deletion_date is null;
1903 end if ;
1904 return l_dummy;
1905 EXCEPTION
1906 when no_data_found then
1907 return 'N';
1908 when too_many_rows then
1909 return 'Y';
1910 WHEN OTHERS THEN
1911 RETURN 'N';
1912 END;
1913
1914 FUNCTION IS_ORG_REGISTERED(p_org_id IN NUMBER) RETURN VARCHAR2
1915 IS
1916 l_dummy varchar2(1):='N';
1917 BEGIN
1918 -- for the time being we just check for the existance of -2 row in org.if it present then the org is usable to assign processes.
1919 select 'Y' into l_dummy
1920 from amw_process_organization
1921 where organization_id = p_org_id
1922 and process_id = -2;
1923 return l_dummy;
1924 EXCEPTION
1925 when no_data_found then
1926 return 'N';
1927 when too_many_rows then
1928 return 'Y';
1929 WHEN OTHERS THEN
1930 RETURN 'N';
1931 END;
1932
1933 /* 03-APR-2007 rjohnson 5686374 start-1 added lang and terr */
1934 PROCEDURE submit_conc_request(p_template_code IN VARCHAR2,
1935 p_template_lang IN VARCHAR2 default NULL,
1936 p_template_territory IN VARCHAR2 default NULL,
1937 p_certification_id IN NUMBER default NULL,
1938 p_organization_id IN NUMBER default NULL,
1939 p_process_id IN NUMBER default NULL,
1940 p_from_date IN DATE default NULL,
1941 p_to_date IN DATE default NULL,
1942 p_include_orgs_with_issues IN VARCHAR2 default NULL,
1943 p_key_controls IN VARCHAR2 default NULL,
1944 p_material_risks IN VARCHAR2 default NULL,
1945 p_significant_process IN VARCHAR2 default NULL,
1946 p_request_id OUT nocopy NUMBER)
1947 IS
1948
1949 l_request_id NUMBER;
1950 l_msg VARCHAR2(2000);
1951 l_reqdata VARCHAR2(240);
1952 xml_layout boolean;
1953 l_data_source_code VARCHAR2(80);
1954
1955 BEGIN
1956
1957 select data_source_code into l_data_source_code
1958 from xdo_templates_b
1959 where template_code = p_template_code
1960 and application_short_name = 'AMW';
1961
1962 /* 03-APR-2007 rjohnson 5686374 start-2 */
1963 /*xml_layout := FND_REQUEST.ADD_LAYOUT('AMW',p_template_code,'en','US','PDF');*/
1964 xml_layout := FND_REQUEST.ADD_LAYOUT('AMW',p_template_code,p_template_lang,p_template_territory,'PDF');
1965 /* 03-APR-2007 rjohnson 5686374 end-2 */
1966
1967 /* Submit the request with relevant params depending on the template */
1968 IF p_template_code = 'AMWBUSPROCRPT' THEN
1969
1970 l_request_id := FND_REQUEST.SUBMIT_REQUEST('AMW', l_data_source_code, null, null, FALSE,
1971 p_organization_id, p_process_id,
1972 p_from_date, p_to_date, p_key_controls, p_material_risks);
1973 ELSIF p_template_code = 'AMWSUBCERTRPT' THEN
1974
1975 l_request_id := FND_REQUEST.SUBMIT_REQUEST('AMW', l_data_source_code, null, null, FALSE,
1976 p_certification_id, p_organization_id,
1977 p_include_orgs_with_issues, p_significant_process,
1978 p_key_controls, p_material_risks);
1979 ELSIF p_template_code = 'AMWORGDOCRPT' THEN
1980
1981 l_request_id := FND_REQUEST.SUBMIT_REQUEST('AMW', l_data_source_code, null, null, FALSE,
1982 p_organization_id, p_from_date, p_to_date,
1983 p_significant_process, p_key_controls, p_material_risks);
1984 ELSIF p_template_code = 'AMWFSCTRLDEFRPT' THEN
1985
1986 l_request_id := FND_REQUEST.SUBMIT_REQUEST('AMW', l_data_source_code, null, null, FALSE,
1987 p_certification_id, p_organization_id, p_significant_process,
1988 p_material_risks,p_key_controls, p_from_date, p_to_date);
1989
1990 END IF;
1991
1992 IF l_request_id = 0 THEN
1993 l_msg:=FND_MESSAGE.GET;
1994 fnd_file.put_line (fnd_file.LOG,l_msg);
1995 ELSE
1996 fnd_file.put_line (fnd_file.LOG,'Test XML report :' || l_request_id );
1997 END IF;
1998
1999 COMMIT;
2000
2001 p_request_id := l_request_id;
2002
2003 END submit_conc_request;
2004
2005
2006 FUNCTION get_org_opinion_status(p_org_id in number, p_mode in varchar2) return
2007 varchar2 is
2008
2009 l_last_audit_status varchar2(240);
2010
2011 begin
2012
2013 select audit_result
2014 into l_last_audit_status
2015 from amw_opinions_v
2016 where pk1_value = p_org_id
2017 and object_opinion_type_id =
2018 (select object_opinion_type_id from AMW_OBJECT_OPINION_TYPES
2019 where opinion_type_id = (select opinion_type_id from amw_opinion_types_b
2020 where opinion_type_code = p_mode)
2021 and object_id = (select object_id from fnd_objects where obj_name =
2022 'AMW_ORGANIZATION') )
2023 and last_update_date =
2024 (select max(last_update_date) from amw_opinions_v
2025 where pk1_value = p_org_id
2026 and object_opinion_type_id =
2027 (select object_opinion_type_id from AMW_OBJECT_OPINION_TYPES
2028 where opinion_type_id = (select opinion_type_id from
2029 amw_opinion_types_b where opinion_type_code = p_mode)
2030 and object_id = (select object_id from fnd_objects where obj_name =
2031 'AMW_ORGANIZATION') ) );
2032
2033 return l_last_audit_status;
2034
2035 exception
2036 when no_data_found then
2037 return null;
2038 when others then
2039 return null;
2040
2041 end get_org_opinion_status;
2042
2043 procedure cache_appr_options
2044 is
2045 type tn is table of number;
2046 type tvalues is table of varchar2(1);
2047 l_param_values tvalues;
2048 l_pk_values tn;
2049 begin
2050 select pk1, parameter_value bulk collect into l_pk_values,l_param_values
2051 from amw_parameters
2052 where parameter_name = 'PROCESS_APPROVAL_OPTION';
2053
2054 for i in l_pk_values.first .. l_pk_values.last loop
2055 g_appr_opt_val(l_pk_values(i)) := l_param_values(i);
2056 end loop;
2057
2058 g_appr_values_cached := true;
2059
2060 exception
2061 when others then
2062 null;
2063 end;
2064
2065 procedure unset_appr_cache
2066 is
2067 begin
2068 g_appr_values_cached := false;
2069 end;
2070
2071 END AMW_Utility_PVT;
[Home] [Help]