1 PACKAGE BODY AHL_UTILITY_PVT AS
2 /* $Header: AHLVUTLB.pls 120.3 2006/06/30 09:11:54 sathapli noship $ */
3
4 -- Added for use by bind_parse.
5 TYPE col_val_rec IS RECORD (
6 col_name VARCHAR2(2000),
7 col_op VARCHAR2(10),
8 col_value VARCHAR2(2000) );
9
10 TYPE col_val_tbl IS TABLE OF col_val_rec INDEX BY BINARY_INTEGER;
11
12
13 ---------------------------------------------------------------------
14 -- PROCEDURE
15 -- bind_parse
16 -- USAGE
17 -- bind_parse (varchar2, col_val_tbl);
18 -- The input string must have a space between the AND and operator clause
19 -- and it must exclude the initial WHERE/AND statement.
20 -- Example: source_code = 'xyz' and campaign_id <> 1
21 ---------------------------------------------------------------------
22 PROCEDURE bind_parse (
23 p_string IN VARCHAR2,
24 x_col_val_tbl OUT NOCOPY col_val_tbl)
25 IS
26 l_new_str VARCHAR2(4000);
27 l_str VARCHAR2(4000) := p_string;
28 l_curr_pos NUMBER; -- the position index of the operator string
29 l_eq_pos NUMBER;
30 l_not_pos NUMBER;
31 l_and_pos NUMBER;
32 i NUMBER := 1;
33 BEGIN
34 LOOP
35 l_and_pos := INSTR (UPPER (l_str), ' AND ');
36 -- handle condition where no more AND's are
37 -- left -- usually if only one condition or
38 -- the last condition in the WHERE clause.
39 IF l_and_pos = 0 THEN
40 l_new_str := l_str;
41 ELSE
42 l_new_str := SUBSTR (l_str, 1, l_and_pos - 1);
43 END IF;
44
45 --
46 -- The operator should also be passed
47 -- back to the calling program.
48 l_eq_pos := INSTR (l_new_str, '=');
49 l_not_pos := INSTR (l_new_str, '<>');
50 --
51 -----------------------------------
52 -- operator equal not equal
53 -- error 0 0
54 -- = 1 0
55 -- <> 0 1
56 -- = 1 2
57 -- <> 2 1
58 -----------------------------------
59 IF l_eq_pos = 0 AND l_not_pos = 0 THEN
60 -- Could not find either an = or an <>
61 -- operator.
62 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_unexp_error) THEN
63 Fnd_Message.set_name('AHL', 'AHL_UTIL_NO_WHERE_OPERATOR');
64 Fnd_Msg_Pub.ADD;
65 RAISE Fnd_Api.g_exc_unexpected_error;
66 END IF;
67 ELSIF l_eq_pos > 0 AND l_not_pos = 0 THEN
68 l_curr_pos := l_eq_pos;
69 x_col_val_tbl(i).col_op := '=';
70 ELSIF l_not_pos > 0 AND l_eq_pos = 0 THEN
71 l_curr_pos := l_not_pos;
72 x_col_val_tbl(i).col_op := '<>';
73 ELSIF l_eq_pos < l_not_pos THEN
74 l_curr_pos := l_eq_pos;
75 x_col_val_tbl(i).col_op := '=';
76 ELSE
77 l_curr_pos := l_not_pos;
78 x_col_val_tbl(i).col_op := '<>';
79 END IF;
80
81 x_col_val_tbl(i).col_name := UPPER (LTRIM (RTRIM (SUBSTR (l_new_str, 1, l_curr_pos - 1))));
82 -- Add 2 to the current position for '<>'.
83 x_col_val_tbl(i).col_value := LTRIM (RTRIM (SUBSTR (l_new_str, l_curr_pos + 2)));
84 --
85 -- Remove the single quotes from the begin and end of the string value;
86 -- no action if a numeric value.
87 IF INSTR (x_col_val_tbl(i).col_value, '''', 1) = 1 THEN
88 x_col_val_tbl(i).col_value := SUBSTR (x_col_val_tbl(i).col_value,2);
89 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);
90 END IF;
91
92 IF l_and_pos = 0 THEN
93 EXIT; -- no more to parse
94 END IF;
95
96 l_str := SUBSTR (l_str, l_and_pos + 4);
97 i := i + 1;
98 END LOOP;
99 END bind_parse;
100
101 ---------------------------------------------------------------------
102 -- FUNCTION
103 -- check_fk_exists
104 --
105 -- HISTORY
106 ---------------------------------------------------------------------
107 FUNCTION check_fk_exists(
108 p_table_name IN VARCHAR2,
109 p_pk_name IN VARCHAR2,
110 p_pk_value IN VARCHAR2,
111 p_pk_data_type IN NUMBER := g_number,
112 p_additional_where_clause IN VARCHAR2 := NULL
113 )
114 RETURN VARCHAR2
115 IS
116
117 l_sql VARCHAR2(4000);
118 l_count NUMBER;
119
120 BEGIN
121 l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || UPPER(p_table_name);
122 l_sql := l_sql || ' WHERE ' || UPPER(p_pk_name) || ' = :b1';
123
124 IF p_additional_where_clause IS NOT NULL THEN
125 -- given time, incorporate bind_parse
126 l_sql := l_sql || ' AND ' || p_additional_where_clause;
127 END IF;
128
129 l_sql := l_sql || ')';
130
131 debug_message('SQL statement: '||l_sql);
132 BEGIN
133 EXECUTE IMMEDIATE l_sql INTO l_count
134 USING p_pk_value;
135 EXCEPTION
136 WHEN NO_DATA_FOUND THEN
137 l_count := 0;
138 END;
139
140 IF l_count = 0 THEN
141 RETURN Fnd_Api.g_false;
142 ELSE
143 RETURN Fnd_Api.g_true;
144 END IF;
145
146 END check_fk_exists;
147
148
149 ---------------------------------------------------------------------
150 -- FUNCTION
151 -- check_lookup_exists
152 --
153 -- HISTORY
154 ---------------------------------------------------------------------
155 FUNCTION check_lookup_exists(
156 p_lookup_table_name IN VARCHAR2 := g_ahl_lookups,
157 p_lookup_type IN VARCHAR2,
158 p_lookup_code IN VARCHAR2
159 )
160 RETURN VARCHAR2
161 IS
162
163 l_sql VARCHAR2(4000);
164 l_count NUMBER;
165
166 BEGIN
167
168 l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || p_lookup_table_name;
169 l_sql := l_sql || ' WHERE LOOKUP_TYPE = :b1';
170 l_sql := l_sql || ' AND LOOKUP_CODE = :b2';
171 l_sql := l_sql || ' AND ENABLED_FLAG = ''Y'')';
172
173 IF Ahl_Debug_Pub.G_FILE_DEBUG THEN
174 Ahl_Debug_Pub.debug( 'SQL statement'||l_sql);
175 END IF;
176 debug_message('SQL statement: '||l_sql);
177 BEGIN
178 EXECUTE IMMEDIATE l_sql INTO l_count
179 USING p_lookup_type, p_lookup_code;
180 EXCEPTION
181 WHEN NO_DATA_FOUND THEN
182 l_count := 0;
183 END;
184
185 IF l_count = 0 THEN
186 RETURN Fnd_Api.g_false;
187 ELSE
188 RETURN Fnd_Api.g_true;
189 END IF;
190
191 END check_lookup_exists;
192
193
194 ---------------------------------------------------------------------
195 -- FUNCTION
196 -- check_uniqueness
197 --
198 -- HISTORY
199 -- Use bind_parse to enable use of bind variables.
200 ---------------------------------------------------------------------
201 FUNCTION check_uniqueness(
202 p_table_name IN VARCHAR2,
203 p_where_clause IN VARCHAR2
204 )
205 RETURN VARCHAR2
206 IS
207
208 l_sql VARCHAR2(4000);
209 l_count NUMBER;
210
211 l_bind_tbl col_val_tbl;
212
213 BEGIN
214
215 l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || UPPER(p_table_name);
216 bind_parse (p_where_clause, l_bind_tbl);
217 --
218 -- Support up to 4 WHERE conditions for uniqueness. If
219 -- the number of conditions changes, then must also revise
220 -- the execute portion of the code.
221 IF l_bind_tbl.COUNT <= 4 THEN
222 l_sql := l_sql || ' WHERE ' || l_bind_tbl(1).col_name || ' ' || l_bind_tbl(1).col_op || ' :b1';
223 FOR i IN 2..l_bind_tbl.COUNT LOOP
224 l_sql := l_sql || ' AND ' || l_bind_tbl(i).col_name || ' ' || l_bind_tbl(i).col_op || ' :b' || i;
225 END LOOP;
226 ELSE
227 -- Exceeded the number of conditions supported
228 -- for bind variables.
229 l_sql := l_sql || ' WHERE ' || p_where_clause;
230 END IF;
231
232 l_sql := l_sql || ')';
233
234 debug_message('SQL statement: '||l_sql);
235 --
236 -- Modify here if number of WHERE conditions
237 -- supported changes.
238 BEGIN
239 IF l_bind_tbl.COUNT = 1 THEN
240 EXECUTE IMMEDIATE l_sql INTO l_count
241 USING l_bind_tbl(1).col_value;
242 ELSIF l_bind_tbl.COUNT = 2 THEN
243 EXECUTE IMMEDIATE l_sql INTO l_count
244 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value;
245 ELSIF l_bind_tbl.COUNT = 3 THEN
246 EXECUTE IMMEDIATE l_sql INTO l_count
247 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value, l_bind_tbl(3).col_value;
248 ELSIF l_bind_tbl.COUNT = 4 THEN
249 EXECUTE IMMEDIATE l_sql INTO l_count
250 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;
251 ELSE
252 EXECUTE IMMEDIATE l_sql INTO l_count;
253 END IF;
254 EXCEPTION
255 WHEN NO_DATA_FOUND THEN
256 l_count := 0;
257 END;
258
259 IF l_count = 0 THEN
260 RETURN Fnd_Api.g_true;
261 ELSE
262 RETURN Fnd_Api.g_false;
263 END IF;
264
265 END check_uniqueness;
266
267 ---------------------------------------------------------------------
268 -- FUNCTION
269 -- is_Y_or_N
270 --
271 -- HISTORY
272 --
273 ---------------------------------------------------------------------
274 FUNCTION is_Y_or_N(
275 p_value IN VARCHAR2
276 )
277 RETURN VARCHAR2
278 IS
279 BEGIN
280 IF p_value = 'Y' OR p_value = 'N' THEN
281 RETURN Fnd_Api.g_true;
282 ELSE
283 RETURN Fnd_Api.g_false;
284 END IF;
285 END is_Y_or_N;
286
287
288 ---------------------------------------------------------------------
289 -- PROCEDURE
290 -- debug_message
291 --
292 -- HISTORY
293 -- Modified cxcheng fix bug 3856899
294 --
295 ---------------------------------------------------------------------
296 PROCEDURE debug_message(
297 p_message_text IN VARCHAR2,
298 p_message_level IN NUMBER := Fnd_Msg_Pub.g_msg_lvl_debug_high --Not used
299 )
300 IS
301 BEGIN
302
303 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
304 fnd_log.string(
305 fnd_log.level_statement,
306 'ahl.plsql.AHL_UTILITY_PVT.debug_message',
307 p_message_text);
308 END IF;
309 END debug_message;
310
311 ---------------------------------------------------------------------
312 -- PROCEDURE
313 -- error_message
314 --
315 -- HISTORY
316 --
317 ---------------------------------------------------------------------
318 PROCEDURE error_message(
319 p_message_name VARCHAR2,
320 p_token_name VARCHAR2 := NULL,
321 P_token_value VARCHAR2 := NULL
322 )
323 IS
324 BEGIN
325 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
326 Fnd_Message.set_name('AHL', p_message_name);
327 IF p_token_name IS NOT NULL THEN
328 Fnd_Message.set_token(p_token_name, p_token_value);
329 END IF;
330 Fnd_Msg_Pub.ADD;
331 END IF;
332 END error_message;
333
334 --======================================================================
335 -- FUNCTION
336 -- Check_User_Status
337 --
338 -- PURPOSE
339 -- Created to check if the User has status change permissions
340 -- Returns true, if it is allowed user status
341 --
342 -- HISTORY
343 -- 18-Dec-2001 srini Create.
344 --======================================================================
345 FUNCTION Check_User_Status(
346 p_user_status_id IN VARCHAR2,
347 p_status_type IN VARCHAR2,
348 p_next_status IN VARCHAR2)
349
350 RETURN VARCHAR2
351 IS
352 CURSOR cur_usr_stat_det IS
353 SELECT 1 FROM DUAL
354 WHERE EXISTS (SELECT * FROM AHL_USER_STATUSES_VL
355 WHERE user_status_id = p_user_status_id
356 AND system_status_type = p_status_type
357 AND system_status_code = p_next_status
358 AND active_flag = 'Y');
359
360 l_dummy NUMBER;
361 BEGIN
362 OPEN cur_usr_stat_det;
363 FETCH cur_usr_stat_det INTO l_dummy;
364 CLOSE cur_usr_stat_det;
365
366 IF l_dummy IS NULL THEN
367 RETURN Fnd_Api.G_FALSE;
368 ELSE
369 RETURN Fnd_Api.G_TRUE;
370 END IF;
371 END Check_User_Status;
372
373 --======================================================================
374 -- PROCEDURE
375 -- Check_Status_Change
376 --
377 -- PURPOSE
378 -- Created to check if the status change is valid and allowed or not.
379 -- Returns success, if it is valid allowed status change
380 --
381 -- HISTORY
382 -- 18-Dec-2001 srini Create.
383 --======================================================================
384
385 PROCEDURE Check_status_change (
386 p_object_type IN VARCHAR2,
387 p_user_status_id IN NUMBER,
388 p_status_type IN VARCHAR2,
389 p_current_status IN VARCHAR2,
390 p_next_status IN VARCHAR2,
391 x_return_status OUT NOCOPY VARCHAR2)
392 IS
393 -- Cursor to check next status code is valid
394 CURSOR cur_stat_det IS
395 SELECT 1 FROM DUAL
396 WHERE EXISTS (SELECT * FROM AHL_STATUS_ORDER_RULES
397 WHERE system_status_type = p_status_type
398 AND current_status_code = p_current_status
399 AND next_status_code = p_next_status);
400 -- Cursor to get system status code
401 CURSOR cur_user_stat (c_status_type IN VARCHAR2,
402 c_status_code IN VARCHAR2)
403 IS
404 SELECT system_status_code
405 FROM AHL_USER_STATUSES_VL
406 WHERE system_status_type = c_status_type
407 AND name = c_status_code;
408 l_dummy NUMBER;
409 l_c_system_status VARCHAR2(30);
410 l_n_system_status VARCHAR2(30);
411 BEGIN
412 -- get currant system status code
413 OPEN cur_user_stat(p_status_type,p_current_status);
414 FETCH cur_user_stat INTO l_c_system_status;
415 IF cur_user_stat%NOTFOUND THEN
416 Fnd_Message.set_name('AHL', 'AHL_COM_RECORD_FOUND');
417 Fnd_Msg_Pub.ADD;
418 END IF;
419 CLOSE cur_user_stat;
420 -- get system status code for new status code
421 OPEN cur_user_stat(p_status_type,p_next_status);
422 FETCH cur_user_stat INTO l_n_system_status;
423 IF cur_user_stat%NOTFOUND THEN
424 Fnd_Message.set_name('AHL', 'AHL_COM_RECORD_FOUND');
425 Fnd_Msg_Pub.ADD;
426 END IF;
427 CLOSE cur_user_stat;
428 -- System status is same then return
429 IF l_c_system_status = l_n_system_status THEN
430 RETURN;
431 END IF;
432 --
433 OPEN cur_stat_det;
434 FETCH cur_stat_det INTO l_dummy;
435 CLOSE cur_stat_det;
436 IF l_dummy IS NOT NULL THEN
437 x_return_status:= Fnd_Api.G_RET_STS_SUCCESS;
438 ELSE
439 x_return_status := Fnd_Api.G_RET_STS_ERROR;
440 END IF;
441 EXCEPTION
442 WHEN NO_DATA_FOUND THEN
443 x_return_status := Fnd_Api.G_RET_STS_ERROR;
444 WHEN TOO_MANY_ROWS THEN
445 x_return_status := Fnd_Api.G_RET_STS_ERROR;
446 WHEN OTHERS THEN
447 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
448 RAISE;
449
450 END check_status_change;
451
452 --======================================================================
453 -- PROCEDURE
454 -- Check_Status_Order_Change
455 --
456 -- PURPOSE
457 -- Created to check if the status change is valid and allowed or not.
458 -- Returns success, if it is valid allowed status change
459 --
460 -- HISTORY
461 -- 18-Dec-2001 srini Create.
462 --======================================================================
463
464 PROCEDURE check_status_order_change (
465 p_status_type IN VARCHAR2,
466 p_current_status IN VARCHAR2,
467 p_next_status IN VARCHAR2,
468 x_return_status OUT NOCOPY VARCHAR2)
469 IS
470 -- Cursor to check next status code is valid
471 CURSOR cur_stat_det (c_status_type IN VARCHAR2,
472 c_current_status IN VARCHAR2,
473 c_next_status IN VARCHAR2)
474 IS
475 SELECT '1' FROM AHL_STATUS_ORDER_RULES
476 WHERE system_status_type = c_status_type
477 AND current_status_code = c_current_status
478 AND next_status_code = c_next_status;
479 l_dummy NUMBER;
480
481 BEGIN
482
483 Ahl_Debug_Pub.enable_debug;
484 -- Debug info.
485 IF Ahl_Debug_Pub.G_FILE_DEBUG THEN
486 Ahl_Debug_Pub.debug( 'status TYPE'||p_status_type);
487 Ahl_Debug_Pub.debug( 'status TYPE'||p_status_type);
488 Ahl_Debug_Pub.debug('ccode' ||p_current_status);
489 Ahl_Debug_Pub.debug('ncode:' ||p_next_status);
490 END IF;
491
492 -- get currant system status code
493 OPEN cur_stat_det(p_status_type,
494 p_current_status,
495 p_next_status);
496 FETCH cur_stat_det INTO l_dummy;
497 IF l_dummy IS NULL THEN
498 Fnd_Message.set_name('AHL', 'AHL_INVALID_STATUS');
499 Fnd_Msg_Pub.ADD;
500 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
501 ELSE
502 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
503 END IF;
504 CLOSE cur_stat_det;
505 --
506 EXCEPTION
507 WHEN NO_DATA_FOUND THEN
508 x_return_status := Fnd_Api.G_RET_STS_ERROR;
509 WHEN TOO_MANY_ROWS THEN
510 x_return_status := Fnd_Api.G_RET_STS_ERROR;
511 WHEN OTHERS THEN
512 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
513 RAISE;
514
515 END check_status_order_change;
516
517 --======================================================================
518 -- PROCEDURE
519 -- Get_WF_Process_Name
520 --
521 -- PURPOSE
522 -- Returns workflow process name for the given type object
523 -- Returns 'E' for active if the no workflow is defined.
524 --
525 --======================================================================
526
527 PROCEDURE Get_WF_Process_Name (
528 p_object IN VARCHAR2,
529 p_application_usg_code IN VARCHAR2 DEFAULT 'AHL',
530 x_active OUT NOCOPY VARCHAR2,
531 x_process_name OUT NOCOPY VARCHAR2,
532 x_item_type OUT NOCOPY VARCHAR2,
533 x_return_status OUT NOCOPY VARCHAR2,
534 x_msg_count OUT NOCOPY VARCHAR2,
535 x_msg_data OUT NOCOPY VARCHAR2)
536
537 IS
538 l_active VARCHAR2(1);
539 l_process_name VARCHAR2(30);
540 l_item_type VARCHAR2(8);
541 l_count NUMBER;
542
543 CURSOR c_wf_data (in_object IN VARCHAR2,
544 in_appl_usg_code IN VARCHAR2) IS
545 SELECT wf_process_name, Item_type, Active_flag
546 FROM AHL_WF_MAPPING
547 WHERE Approval_object = in_object
548 AND APPLICATION_USG_CODE = in_appl_usg_code;
549
550 CURSOR c_wf_data_null (in_appl_usg_code IN VARCHAR2) IS
551 SELECT wf_process_name, Item_type, Active_flag
552 FROM AHL_WF_MAPPING
553 WHERE Approval_object IS NULL
554 AND APPLICATION_USG_CODE = in_appl_usg_code;
555
556 CURSOR chk_appl_usg_code IS
557 SELECT 1 FROM FND_LOOKUPS
558 WHERE lookup_code = p_application_usg_code
559 AND LOOKUP_TYPE = 'AHL_APPLICATION_USAGE_CODE';
560 BEGIN
561 OPEN chk_appl_usg_code;
562 FETCH chk_appl_usg_code INTO l_count;
563 IF chk_appl_usg_code%NOTFOUND THEN
564 CLOSE chk_appl_usg_code;
565 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
566 Fnd_Message.set_name ('AHL', 'AHL_APPR_APPUSG_INVALID');
567 Fnd_Msg_Pub.ADD;
568 END IF;
569 x_return_status := Fnd_Api.g_ret_sts_error;
570 RETURN;
571 ELSE
572 CLOSE chk_appl_usg_code;
573 END IF;
574
575 OPEN c_wf_data(p_object, p_application_usg_code);
576 FETCH c_wf_data INTO l_process_name,l_item_type,l_active;
577 IF c_wf_data%FOUND THEN
578 CLOSE c_wf_data;
579
580 x_active := l_active;
581 x_process_name := l_process_name;
582 x_item_type := l_item_type;
583 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
584 RETURN;
585 ELSE
586 CLOSE c_wf_data;
587 OPEN c_wf_data_null(p_application_usg_code);
588 FETCH c_wf_data_null INTO l_process_name,l_item_type,l_active;
589 IF c_wf_data_null%NOTFOUND THEN
590 CLOSE c_wf_data_null;
591 x_active := 'E';
592 x_process_name := NULL;
593 x_item_type := NULL;
594 --should not return error if no wf is found as this condition is not an error, and each module should handle it as they see fit.
595 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
596 RETURN;
597 ELSE
598 CLOSE c_wf_data_null;
599
600 x_active := l_active;
601 x_process_name := l_process_name;
602 x_item_type := l_item_type;
603 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
604 RETURN;
605 END IF;
606 END IF;
607
608 Fnd_Msg_Pub.count_and_get (
609 p_encoded => Fnd_Api.g_false,
610 p_count => x_msg_count,
611 p_data => x_msg_data
612 );
613
614 END Get_WF_Process_Name;
615
616 --This function is to get the highest standalone unit name to which the instance belongs to
617 --This unit name is not the sub unit but the root unit
618 FUNCTION Get_Unit_Name(p_instance_id Number) RETURN VARCHAR2 IS
619 l_unit_name VARCHAR2(80);
620 l_instance_id NUMBER;
621 CURSOR get_uc_instance_id IS
622 SELECT object_id
623 FROM csi_ii_relationships
624 WHERE object_id IN (SELECT csi_item_instance_id
625 FROM ahl_unit_config_headers
626 WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
627 START WITH subject_id = p_instance_id
628 AND relationship_type_code = 'COMPONENT-OF'
629 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
630 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
631 CONNECT BY subject_id = PRIOR object_id
632 AND relationship_type_code = 'COMPONENT-OF'
633 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
634 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
635
636 CURSOR get_uc_header_name(c_instance_id NUMBER) IS
637 SELECT name
638 FROM ahl_unit_config_headers
639 WHERE csi_item_instance_id = c_instance_id
640 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
641
642 BEGIN
643 OPEN get_uc_instance_id;
644 LOOP
645 FETCH get_uc_instance_id INTO l_instance_id;
646 EXIT when get_uc_instance_id%NOTFOUND;
647 END LOOP;
648 CLOSE get_uc_instance_id;
649
650 IF l_instance_id IS NULL THEN
651 l_instance_id := p_instance_id;
652 END IF;
653 OPEN get_uc_header_name(l_instance_id);
654 FETCH get_uc_header_name INTO l_unit_name;
655 CLOSE get_uc_header_name;
656 RETURN l_unit_name;
657 END;
658
659 --======================================================================
660 -- FUNCTION
661 -- Is_Org_In_User_Ou
662 --
663 -- PURPOSE
664 -- Created to check if the Organization is in users operating unit or not.
665 -- Returns FND_API.G_TRUE if the org belongs to user's operating unit
666 -- Returns FND_API.G_FALSE if the org doesnt belong to user's operating unit
667 -- Returns 'X' on error.
668 --======================================================================
669 -- Function added for transit check.
670 FUNCTION IS_ORG_IN_USER_OU
671 (
672 p_org_id IN NUMBER,
673 p_org_name IN VARCHAR2,
674 x_return_status OUT NOCOPY VARCHAR2,
675 x_msg_data OUT NOCOPY VARCHAR2
676 )
677 RETURN VARCHAR2
678 IS
679
680 -- Cursor for getting organization id out of org name
681 CURSOR get_org_id_csr(p_org_name IN VARCHAR2)
682 IS
683 SELECT hou.organization_id
684 FROM HR_ORGANIZATION_UNITS hou
685 WHERE hou.name = p_org_name;
686
687 --Cursor for checking if the given organization belongs to user's OU.
688 -- SATHAPLI::Bug# 5246136 fix
689 -- Changed reference of ORG_ORGANIZATION_DEFINITIONS to INV_ORGANIZATION_INFO_V
690 CURSOR is_user_in_ou_csr(p_org_id IN NUMBER)
691 IS
692 SELECT hou.organization_id
693 FROM HR_ORGANIZATION_UNITS hou,
694 MTL_PARAMETERS mtl
695 WHERE
696 hou.organization_id IN (
697 SELECT organization_id
698 FROM INV_ORGANIZATION_INFO_V
699
700 WHERE NVL(operating_unit, mo_global.get_current_org_id()) =
701 mo_global.get_current_org_id()
702
703 )
704 AND mtl.organization_id = hou.organization_id
705 AND mtl.eam_enabled_flag = 'Y'
706 AND hou.organization_id = p_org_id;
707
708 l_org_id NUMBER;
709 l_ou_org_id NUMBER;
710
711 BEGIN
712 --Initialize return status to success.
713 x_return_status := FND_API.G_RET_STS_SUCCESS;
714 IF p_org_id IS NULL AND
715 p_org_name IS NULL
716 THEN
717 x_return_status := FND_API.G_RET_STS_ERROR;
718 x_msg_data := 'AHL_UA_ORG_INFO_NULL';
719 RETURN 'X';
720 END IF;
721
722 l_org_id := p_org_id;
723 IF p_org_id IS NULL THEN
724 OPEN get_org_id_csr(p_org_name);
725 FETCH get_org_id_csr INTO l_org_id;
726 CLOSE get_org_id_csr;
727 END IF;
728
729 -- operating unit check
730 IF l_org_id IS NOT NULL
731 THEN
732 OPEN is_user_in_ou_csr(l_org_id);
733 FETCH is_user_in_ou_csr INTO l_ou_org_id;
734 CLOSE is_user_in_ou_csr;
735 IF l_ou_org_id IS NOT NULL
736 THEN
737 RETURN FND_API.G_TRUE;
738 ELSE
739 RETURN FND_API.G_FALSE;
740 END IF;
741 ELSE
742 x_return_status := FND_API.G_RET_STS_ERROR;
743 x_msg_data := 'AHL_UA_ORG_INFO_NULL';
744 RETURN 'X';
745 END IF;
746
747 END IS_ORG_IN_USER_OU;
748
749 --======================================================================
750 -- FUNCTION
751 -- GET_LOOKUP_MEANING
752 --
753 -- PURPOSE
754 -- Return fnd_lookup_values_vl.meaning, given lookup_type and lookup_code.
755 -- This function will either return the correct meaning, or return null.
756 -- This function also will not raise any error.
757 --======================================================================
758 FUNCTION GET_LOOKUP_MEANING
759 (
760 p_lookup_type IN VARCHAR2,
761 p_lookup_code IN VARCHAR2
762 )
763 RETURN VARCHAR2
764 IS
765 l_meaning VARCHAR2(80);
766
767 CURSOR get_meaning
768 IS
769 SELECT meaning
770 FROM fnd_lookup_values
771 WHERE lookup_type = p_lookup_type
772 AND lookup_code = p_lookup_code
773 AND language = userenv('LANG');
774 BEGIN
775 OPEN get_meaning;
776 FETCH get_meaning INTO l_meaning;
777 CLOSE get_meaning;
778
779 RETURN l_meaning;
780 END GET_LOOKUP_MEANING;
781
782 END Ahl_Utility_Pvt;
783
[Home] [Help]