[Home] [Help]
PACKAGE BODY: APPS.PVX_UTILITY_PVT
Source
1 PACKAGE BODY PVX_Utility_PVT AS
2 /* $Header: pvxvutlb.pls 120.7 2008/01/14 23:04:07 rnori ship $ */
3
4 TYPE col_val_rec IS RECORD (
5 col_name VARCHAR2(2000),
6 col_op VARCHAR2(10),
7 col_value VARCHAR2(2000) );
8
9 TYPE col_val_tbl IS TABLE OF col_val_rec INDEX BY BINARY_INTEGER;
10
11 ---------------------------------------------------------------------
12 -- FUNCTION
13 -- bind_parse
14 --
15 -- DESCRIPTION
16 -- Given a string containing the WHERE conditions in a WHERE
17 -- clause, return a tuple of column name and column value.
18 ---------------------------------------------------------------------
19 PROCEDURE bind_parse (
20 p_string IN VARCHAR2,
21 x_col_val_tbl OUT NOCOPY col_val_tbl
22 );
23
24
25 ---------------------------------------------------------------------
26 -- FUNCTION
27 -- check_fk_exists
28 --
29 ---------------------------------------------------------------------
30 FUNCTION check_fk_exists(
31 p_table_name IN VARCHAR2,
32 p_pk_name IN VARCHAR2,
33 p_pk_value IN VARCHAR2,
34 p_pk_data_type IN NUMBER := g_number,
35 p_additional_where_clause IN VARCHAR2 := NULL
36 )
37 RETURN VARCHAR2
38 IS
39
40 l_sql VARCHAR2(4000);
41 l_count NUMBER;
42
43 BEGIN
44 l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || UPPER(p_table_name);
45 l_sql := l_sql || ' WHERE ' || UPPER(p_pk_name) || ' = :b1';
46
47 IF p_additional_where_clause IS NOT NULL THEN
48 -- given time, incorporate bind_parse
49 l_sql := l_sql || ' AND ' || p_additional_where_clause;
50 END IF;
51
52 l_sql := l_sql || ')';
53
54 debug_message('SQL statement: '||l_sql);
55 BEGIN
56 EXECUTE IMMEDIATE l_sql INTO l_count
57 USING p_pk_value;
58 EXCEPTION
59 WHEN NO_DATA_FOUND THEN
60 l_count := 0;
61 END;
62
63 IF l_count = 0 THEN
64 RETURN FND_API.g_false;
65 ELSE
66 RETURN FND_API.g_true;
67 END IF;
68
69 END check_fk_exists;
70
71
72
73 ---------------------------------------------------------------------
74 -- FUNCTION
75 -- is_vendor_admin_user
76 --
77 ---------------------------------------------------------------------
78 FUNCTION is_vendor_admin_user(
79 p_resource_id IN NUMBER
80 )
81 RETURN VARCHAR2
82 IS
83
84 l_temp varchar2(1);
85 is_vendor_admin varchar2(1):=FND_API.g_false;
86
87 CURSOR isvendor_csr(p_res_id NUMBER) IS
88 SELECT 'Y'
89 FROM jtf_rs_role_relations role_reln, jtf_rs_roles_vl rl
90 WHERE rl.ROLE_TYPE_CODE = 'PRM'
91 AND rl.role_code = 'PV_VENDOR_ADMINISTRATOR'
92 AND rl.role_id = role_reln.role_id
93 AND role_reln.role_resource_type = 'RS_INDIVIDUAL'
94 AND role_reln.ROLE_RESOURCE_ID = p_res_id
95 AND delete_flag = 'N'
96 AND TRUNC(SYSDATE) BETWEEN TRUNC(role_reln.start_date_active)
97 AND TRUNC(NVL(role_reln.end_date_active,SYSDATE));
98
99 BEGIN
100 OPEN isvendor_csr(p_resource_id);
101 FETCH isvendor_csr into l_temp;
102 IF isvendor_csr%FOUND THEN
103 is_vendor_admin:=FND_API.g_true;
104 END IF;
105 CLOSE isvendor_csr;
106
107 RETURN is_vendor_admin ;
108
109 END is_vendor_admin_user;
110
111 ---------------------------------------------------------------------
112 -- FUNCTION
113 -- check_lookup_exists
114 --
115 ---------------------------------------------------------------------
116 FUNCTION check_lookup_exists(
117 p_lookup_table_name IN VARCHAR2 := g_pv_lookups,
118 p_lookup_type IN VARCHAR2,
119 p_lookup_code IN VARCHAR2
120 )
121 Return VARCHAR2
122 IS
123
124 l_sql VARCHAR2(4000);
125 l_count NUMBER;
126
127 BEGIN
128
129 l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || p_lookup_table_name;
130 l_sql := l_sql || ' WHERE LOOKUP_TYPE = :b1';
131 l_sql := l_sql || ' AND LOOKUP_CODE = :b2';
132 l_sql := l_sql || ' AND ENABLED_FLAG = ''Y'')';
133
134 debug_message('SQL statement: '||l_sql);
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
143 IF l_count = 0 THEN
144 RETURN FND_API.g_false;
145 ELSE
146 RETURN FND_API.g_true;
147 END IF;
148
149 END check_lookup_exists;
150
151
152 ---------------------------------------------------------------------
153 -- FUNCTION
154 -- check_uniqueness
155 --
156 -- HISTORY
157 -- 05/19/99 cklee Created.
158 -- 25-Apr-2000 choang Use bind_parse to enable use of bind variables.
159 ---------------------------------------------------------------------
160 FUNCTION check_uniqueness(
161 p_table_name IN VARCHAR2,
162 p_where_clause IN VARCHAR2
163 )
164 RETURN VARCHAR2
165 IS
166
167 l_sql VARCHAR2(4000);
168 l_count NUMBER;
169
170 l_bind_tbl col_val_tbl;
171
172 BEGIN
173
174 l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || UPPER(p_table_name);
175 -- l_sql := l_sql || ' WHERE ' || p_where_clause;
176
177 bind_parse (p_where_clause, l_bind_tbl);
178
179 --
180 -- Support up to 4 WHERE conditions for uniqueness. If
181 -- the number of conditions changes, then must also revise
182 -- the execute portion of the code.
183
184 IF l_bind_tbl.COUNT <= 6 THEN
185 l_sql := l_sql || ' WHERE ' || l_bind_tbl(1).col_name || ' ' || l_bind_tbl(1).col_op || ' :b1';
186 FOR i IN 2..l_bind_tbl.COUNT LOOP
187 l_sql := l_sql || ' AND ' || l_bind_tbl(i).col_name || ' ' || l_bind_tbl(i).col_op || ' :b' || i;
188 END LOOP;
189 ELSE
190 -- Exceeded the number of conditions supported
191 -- for bind variables.
192 l_sql := l_sql || ' WHERE ' || p_where_clause;
193 END IF;
194
195 l_sql := l_sql || ')';
196
197 debug_message('SQL statement: '||l_sql);
198 --
199 -- Modify here if number of WHERE conditions
200 -- supported changes.
201 BEGIN
202 IF l_bind_tbl.COUNT = 1 THEN
203 EXECUTE IMMEDIATE l_sql INTO l_count
204 USING l_bind_tbl(1).col_value;
205 ELSIF l_bind_tbl.COUNT = 2 THEN
206 EXECUTE IMMEDIATE l_sql INTO l_count
207 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value;
208 ELSIF l_bind_tbl.COUNT = 3 THEN
209 EXECUTE IMMEDIATE l_sql INTO l_count
210 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value, l_bind_tbl(3).col_value;
211 ELSIF l_bind_tbl.COUNT = 4 THEN
212 EXECUTE IMMEDIATE l_sql INTO l_count
213 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;
214 ELSIF l_bind_tbl.COUNT = 5 THEN
215 EXECUTE IMMEDIATE l_sql INTO l_count
216 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, l_bind_tbl(5).col_value;
217 ELSIF l_bind_tbl.COUNT = 6 THEN
218 EXECUTE IMMEDIATE l_sql INTO l_count
219 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, l_bind_tbl(5).col_value, l_bind_tbl(6).col_value;
220 ELSE
221 EXECUTE IMMEDIATE l_sql INTO l_count;
222 END IF;
223 EXCEPTION
224 WHEN NO_DATA_FOUND THEN
225 l_count := 0;
226 END;
227
228 IF l_count = 0 THEN
229 RETURN FND_API.g_true;
230 ELSE
231 RETURN FND_API.g_false;
232 END IF;
233
234 END check_uniqueness;
235
236
237 ---------------------------------------------------------------------
238 -- FUNCTION
239 -- is_Y_or_N
240 --
241 ---------------------------------------------------------------------
242 FUNCTION is_Y_or_N(
243 p_value IN VARCHAR2
244 )
245 RETURN VARCHAR2
246 IS
247 BEGIN
248 IF p_value = 'Y' or p_value = 'N' THEN
249 RETURN FND_API.g_true;
250 ELSE
251 RETURN FND_API.g_false;
252 END IF;
253 END is_Y_or_N;
254
255
256 ---------------------------------------------------------------------
257 -- PROCEDURE
258 -- debug_message
259 --
260 ---------------------------------------------------------------------
261 PROCEDURE debug_message(
262 p_message_text IN VARCHAR2,
263 p_message_level IN NUMBER := FND_MSG_PUB.g_msg_lvl_debug_high
264 )
265 IS
266 BEGIN
267 IF FND_MSG_PUB.check_msg_level(p_message_level) THEN
268 FND_MESSAGE.set_name('PV', 'PV_DEBUG_MESSAGE');
269 FND_MESSAGE.set_token('TEXT', p_message_text);
270 FND_MSG_PUB.add;
271 END IF;
272 END debug_message;
273
274
275 ---------------------------------------------------------------------
276 -- PROCEDURE
277 -- error_message
278 --
279 ---------------------------------------------------------------------
280 PROCEDURE error_message(
281 p_message_name VARCHAR2,
282 p_token_name VARCHAR2 := NULL,
283 P_token_value VARCHAR2 := NULL
284 )
285 IS
286 BEGIN
287 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
288 FND_MESSAGE.set_name('PV', p_message_name);
289 IF p_token_name IS NOT NULL THEN
290 FND_MESSAGE.set_token(p_token_name, p_token_value);
291 END IF;
292 FND_MSG_PUB.add;
293 END IF;
294 END error_message;
295
296
297 ---------------------------------------------------------------------
298 -- PROCEDURE
299 -- display_messages
300 --
301 ---------------------------------------------------------------------
302 PROCEDURE display_messages
303 IS
304 l_count NUMBER;
305 l_msg VARCHAR2(2000);
306 BEGIN
307 l_count := FND_MSG_PUB.count_msg;
308 FOR i IN 1 .. l_count LOOP
309 l_msg := FND_MSG_PUB.get(i, FND_API.g_false);
310 -- holiu: remove since adchkdrv does not like it
311 -- DBMS_OUTPUT.put_line('(' || i || ') ' || l_msg);
312 END LOOP;
313 END display_messages;
314
315
316 PROCEDURE get_lookup_meaning (
317 p_lookup_type IN VARCHAR2,
318 p_lookup_code IN VARCHAR2,
319 x_return_status OUT NOCOPY VARCHAR2,
320 x_meaning OUT NOCOPY VARCHAR2
321 )
322 IS
323 CURSOR c_meaning IS
324 SELECT meaning
325 FROM pv_lookups
326 WHERE lookup_type = UPPER (p_lookup_type)
327 AND lookup_code = UPPER (p_lookup_code);
328 BEGIN
329 OPEN c_meaning;
330 FETCH c_meaning INTO x_meaning;
331 IF c_meaning%NOTFOUND THEN
332 CLOSE c_meaning;
333 x_return_status := FND_API.G_RET_STS_ERROR;
334 x_meaning:= NULL;
335 ELSE
336 x_return_status := FND_API.G_RET_STS_SUCCESS;
337 END IF;
338 EXCEPTION
339 WHEN OTHERS THEN
340 IF c_meaning%ISOPEN THEN
341 CLOSE c_meaning;
342 END IF;
343 x_return_status := FND_API.G_RET_STS_ERROR;
344 x_meaning := NULL;
345 END get_lookup_meaning;
346
347
348 ---------------------------------------------------------------------
349 -- PROCEDURE
350 -- get_System_Timezone
351 --
352 -- PURPOSE
353 -- This procedure will return the timezone from the System Timezone profile option
354 ---------------------------------------------------------------------
355 PROCEDURE get_System_Timezone(
356
357 x_return_status OUT NOCOPY VARCHAR2,
358 x_sys_time_id OUT NOCOPY NUMBER,
359 x_sys_time_name OUT NOCOPY VARCHAR2
360 ) IS
361
362 l_sys_time_id NUMBER;
363 l_sys_name VARCHAR2(80);
364
365 cursor c_get_name(l_time_id IN NUMBER) is
366 select NAME
367 from FND_TIMEZONES_VL --HZ_TIMEZONES_VL
368 where UPGRADE_TZ_ID = l_time_id;
369 -- where TIMEZONE_ID = l_time_id;
370
371 BEGIN
372 -- Initialize API return status to success
373 x_return_status := FND_API.G_RET_STS_SUCCESS;
374 l_sys_time_id := FND_PROFILE.VALUE('AMS_SYSTEM_TIMEZONE_ID');
375 OPEN c_get_name(l_sys_time_id);
376 FETCH c_get_name into l_sys_name;
377 IF (c_get_name%NOTFOUND) THEN
378 CLOSE c_get_name;
379 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
380 return;
381 END IF;
382 CLOSE c_get_name;
383
384 x_sys_time_id := l_sys_time_id;
385 x_sys_time_name := l_sys_name;
386 EXCEPTION
387 WHEN NO_DATA_FOUND THEN
388 IF (c_get_name%ISOPEN) THEN
389 CLOSE c_get_name;
390 END IF;
391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
392 END get_System_Timezone;
393
394 ---------------------------------------------------------------------
395 -- PROCEDURE
396 -- get_User_Timezone
397 --
398 -- PURPOSE
399 -- This procedure will return the timezone from the User Timezone profile option
400 ---------------------------------------------------------------------
401 PROCEDURE get_User_Timezone(
402
403 x_return_status OUT NOCOPY VARCHAR2,
404 x_user_time_id OUT NOCOPY NUMBER,
405 x_user_time_name OUT NOCOPY VARCHAR2
406 ) IS
407
408 l_user_time_id NUMBER;
409 l_user_time_name VARCHAR2(80);
410
411 cursor get_name(l_time_id IN NUMBER) is
412 select NAME
413 from FND_TIMEZONES_VL --HZ_TIMEZONES_VL
414 where UPGRADE_TZ_ID = l_time_id;
415 -- where TIMEZONE_ID = l_time_id;
416
417 BEGIN
418 -- Initialize API return status to success
419 x_return_status := FND_API.G_RET_STS_SUCCESS;
420 l_user_time_id := FND_PROFILE.VALUE('AMS_USER_TIMEZONE_ID');
421 OPEN get_name(l_user_time_id);
422 FETCH get_name into l_user_time_name;
423 IF (get_name%NOTFOUND) THEN
424 CLOSE get_name;
425 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
426 return;
427 END IF;
428 CLOSE get_name;
429
430 x_user_time_id := l_user_time_id;
431 x_user_time_name := l_user_time_name;
432 EXCEPTION
433 WHEN NO_DATA_FOUND THEN
434 IF (get_name%ISOPEN) THEN
435 CLOSE get_name;
436 END IF;
437 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
438 END get_User_Timezone;
439
440 -------------------------------------------------------------------------------------------------
441 -- PROCEDURE
442 -- Convert_Timezone
443 --
444 -- PURPOSE
445 -- This procedure will take the user timezone and the input time, depending on the parameter
446 -- p_convert_type it will convert the input time to System timezone or sent Usertimezone
447 ---------------------------------------------------------------------------------------------------
448 PROCEDURE Convert_Timezone(
449 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
450 x_return_status OUT NOCOPY VARCHAR2,
451 x_msg_count OUT NOCOPY NUMBER,
452 x_msg_data OUT NOCOPY VARCHAR2,
453
454 p_user_tz_id IN NUMBER ,
455 p_in_time IN DATE , -- required
456 p_convert_type IN VARCHAR2 := 'SYS' , -- (SYS/USER)
457
458 x_out_time OUT NOCOPY DATE
459 ) IS
460
461 l_sys_time_id NUMBER;
462 l_user_tz_id NUMBER := p_user_tz_id ;
463 l_sys_time_name VARCHAR2(80);
464 l_user_time_name VARCHAR2(80);
465 l_return_status VARCHAR2(1); -- Return value from procedures
466
467 l_from_timezone_id NUMBER ;
468 l_to_timezone_id NUMBEr ;
469 BEGIN
470
471 -- Initialize API return status to success
472 x_return_status := FND_API.G_RET_STS_SUCCESS;
473
474 get_System_Timezone(
475 l_return_status,
476 l_sys_time_id,
477 l_sys_time_name);
478
479 IF (l_return_status = FND_API.G_RET_STS_ERROR OR
480 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
481 THEN
482 x_return_status := l_return_status;
483 RETURN;
484 END IF;
485
486 -- If the user timezone is not sent
487 -- get it from profiles
488 IF l_user_tz_id IS NULL THEN
489 Get_User_Timezone(
490 x_return_status => l_return_status,
491 x_user_time_id => l_user_tz_id ,
492 x_user_time_name => l_user_time_name
493 ) ;
494 END IF;
495
496 IF (l_return_status = FND_API.G_RET_STS_ERROR OR
497 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
498 THEN
499 x_return_status := l_return_status;
500 RETURN;
501 END IF;
502
503 IF p_convert_type = 'SYS' THEN
504 l_from_timezone_id := l_user_tz_id ;
505 l_to_timezone_id := l_sys_time_id ;
506 ELSIF p_convert_type = 'USER' THEN
507 l_from_timezone_id := l_sys_time_id ;
508 l_to_timezone_id := l_user_tz_id ;
509 END IF;
510
511 HZ_TIMEZONE_PUB.get_time(
512 p_api_version => 1.0,
513 p_init_msg_list => p_init_msg_list,
514 p_source_tz_id => l_from_timezone_id ,
515 p_dest_tz_id => l_to_timezone_id ,
516 p_source_day_time => p_in_time,
517 x_dest_day_time => x_out_time,
518 x_return_status => x_return_status,
519 x_msg_count => x_msg_count,
520 x_msg_data => x_msg_data
521 );
522
523 END Convert_Timezone ;
524
525
526 ---------------------------------------------------------------------
527 -- PROCEDURE
528 -- bind_parse
529 -- USAGE
530 -- bind_parse (varchar2, col_val_tbl);
531 -- The input string must have a space between the AND and operator clause
532 -- and it must exclude the initial WHERE/AND statement.
533 -- Example: pv_attr_code = 'xyz' and pv_attribute_id <> 1
534 ---------------------------------------------------------------------
535 PROCEDURE bind_parse (
536 p_string IN VARCHAR2,
537 x_col_val_tbl OUT NOCOPY col_val_tbl)
538 IS
539 l_new_str VARCHAR2(4000);
540 l_str VARCHAR2(4000) := p_string;
541 l_curr_pos NUMBER; -- the position index of the operator string
542 l_eq_pos NUMBER;
543 l_not_pos NUMBER;
544 l_and_pos NUMBER;
545 i NUMBER := 1;
546 BEGIN
547 LOOP
548 l_and_pos := INSTR (UPPER (l_str), ' AND ');
549 -- handle condition where no more AND's are
550 -- left -- usually if only one condition or
551 -- the last condition in the WHERE clause.
552 IF l_and_pos = 0 THEN
553 l_new_str := l_str;
554 ELSE
555 l_new_str := SUBSTR (l_str, 1, l_and_pos - 1);
556 END IF;
557
558 --
559 -- The operator should also be passed
560 -- back to the calling program.
561 l_eq_pos := INSTR (l_new_str, '=');
562 l_not_pos := INSTR (l_new_str, '<>');
563 --
564 -----------------------------------
565 -- operator equal not equal
566 -- error 0 0
567 -- = 1 0
568 -- <> 0 1
569 -- = 1 2
570 -- <> 2 1
571 -----------------------------------
572 IF l_eq_pos = 0 AND l_not_pos = 0 THEN
573 -- Could not find either an = or an <>
574 -- operator.
575 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
576 FND_MESSAGE.set_name('AMS', 'AMS_UTIL_NO_WHERE_OPERATOR');
577 FND_MSG_PUB.add;
578 RAISE FND_API.g_exc_unexpected_error;
579 END IF;
580 ELSIF l_eq_pos > 0 AND l_not_pos = 0 THEN
581 l_curr_pos := l_eq_pos;
582 x_col_val_tbl(i).col_op := '=';
583 ELSIF l_not_pos > 0 AND l_eq_pos = 0 THEN
584 l_curr_pos := l_not_pos;
585 x_col_val_tbl(i).col_op := '<>';
586 ELSIF l_eq_pos < l_not_pos THEN
587 l_curr_pos := l_eq_pos;
588 x_col_val_tbl(i).col_op := '=';
589 ELSE
590 l_curr_pos := l_not_pos;
591 x_col_val_tbl(i).col_op := '<>';
592 END IF;
593
594 x_col_val_tbl(i).col_name := UPPER (LTRIM (RTRIM (SUBSTR (l_new_str, 1, l_curr_pos - 1))));
595 -- Add 2 to the current position for '<>'.
596 x_col_val_tbl(i).col_value := LTRIM (RTRIM (SUBSTR (l_new_str, l_curr_pos + 2)));
597 --
598 -- Remove the single quotes from the begin and end of the string value;
599 -- no action if a numeric value.
600 IF INSTR (x_col_val_tbl(i).col_value, '''', 1) = 1 THEN
601 x_col_val_tbl(i).col_value := SUBSTR (x_col_val_tbl(i).col_value,2);
602 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);
603 END IF;
604
605 IF l_and_pos = 0 THEN
606 EXIT; -- no more to parse
607 END IF;
608
609 l_str := SUBSTR (l_str, l_and_pos + 4);
610 i := i + 1;
611 END LOOP;
612 END bind_parse;
613
614
615 ---------------------------------------------------------------------
616 -- FUNCTION
617 -- get_lookup_meaning
618 ---------------------------------------------------------------------
619 FUNCTION get_lookup_meaning (
620 p_lookup_type IN VARCHAR2,
621 p_lookup_code IN VARCHAR2
622 )
623 RETURN VARCHAR2
624 IS
625 l_meaning VARCHAR2(80);
626
627 CURSOR c_meaning IS
628 SELECT meaning
629 FROM pv_lookups
630 WHERE lookup_type = UPPER (p_lookup_type)
631 AND lookup_code = UPPER (p_lookup_code);
632 BEGIN
633 OPEN c_meaning;
634 FETCH c_meaning INTO l_meaning;
635 CLOSE c_meaning;
636
637 RETURN l_meaning;
638 END get_lookup_meaning;
639
640
641 ---------------------------------------------------------------------
642 -- FUNCTION
643 -- get_resource_name
644 -- USAGE
645 ---------------------------------------------------------------------
646 FUNCTION get_resource_name (
647 p_resource_id IN VARCHAR2
648 )
649 RETURN VARCHAR2
650 IS
651 l_resource_name VARCHAR2(240);
652
653 /* CURSOR c_resource_name IS
654 SELECT full_name
655 FROM jtf_rs_res_emp_vl
656 WHERE resource_id = p_resource_id; */
657
658 -- Vanitha - Changes for performance
659 CURSOR c_resource_name IS
660 SELECT full_name
661 FROM jtf_rs_resource_extns rsc,
662 per_all_people_f ppl
663 WHERE rsc.category = 'EMPLOYEE'
664 AND ppl.person_id = rsc.source_id
665 AND resource_id = p_resource_id;
666
667 BEGIN
668 IF p_resource_id IS NULL THEN
669 RETURN NULL;
670 END IF;
671
672 OPEN c_resource_name;
673 FETCH c_resource_name INTO l_resource_name;
674 CLOSE c_resource_name;
675
676 RETURN l_resource_name;
677 END get_resource_name;
678
679
680
681 ---------------------------------------------------------------------
682 -- FUNCTION
683 -- get_contact_account_id
684 --
685 ---------------------------------------------------------------------
686 FUNCTION get_contact_account_id(
687 p_contact_rel_party_id IN NUMBER
688 )
689 RETURN NUMBER
690 IS
691
692 l_account_id NUMBER;
693
694 CURSOR get_primary_account(cv_contact_rel_party_id NUMBER) IS
695 select b.cust_account_id
696 from hz_party_preferences a, hz_cust_accounts b
697 where category = 'PRIMARY_ACCOUNT'
698 and a.preference_code = 'CUSTOMER_ACCOUNT_ID'
699 and a.party_id = cv_contact_rel_party_id
700 and b.cust_account_id=a.value_number and nvl(status,'A')='A';
701
702 CURSOR get_account(cv_contact_rel_party_id NUMBER) IS
703 select cust_account_id
704 from hz_cust_accounts where cust_account_id in
705 (select cust_account_id
706 from hz_cust_account_roles
707 where party_id = cv_contact_rel_party_id
708 and nvl(status,'A')='A'
709 )
710 and nvl(status,'A')='A'
711 order by creation_date asc;
712
713
714 BEGIN
715 OPEN get_primary_account(p_contact_rel_party_id);
716 FETCH get_primary_account into l_account_id;
717 CLOSE get_primary_account;
718
719 IF l_account_id IS NULL THEN
720
721 OPEN get_account(p_contact_rel_party_id);
722 FETCH get_account into l_account_id;
723 CLOSE get_account;
724
725 END IF;
726
727
728 RETURN l_account_id;
729
730 END get_contact_account_id;
731
732
733 -------------------------------------------------------------------------------
734 -- PROCEDURE
735 -- create_history_log
736 -- DESCRIPTION
737 -- Creates a history log
738 -------------------------------------------------------------------------------
739 PROCEDURE create_history_log(
740 p_arc_history_for_entity_code IN VARCHAR2,
741 p_history_for_entity_id IN NUMBER,
742 p_history_category_code IN VARCHAR2 DEFAULT NULL,
743 p_message_code IN VARCHAR2,
744 p_partner_id IN NUMBER,
745 p_access_level_flag IN VARCHAR2 DEFAULT 'V',
746 p_interaction_level IN NUMBER DEFAULT G_INTERACTION_LEVEL_10,
747 p_comments IN VARCHAR2 DEFAULT NULL,
748 p_log_params_tbl IN PVX_UTILITY_PVT.log_params_tbl_type,
749 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
750 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
751 x_return_status OUT NOCOPY VARCHAR2,
752 x_msg_count OUT NOCOPY NUMBER,
753 x_msg_data OUT NOCOPY VARCHAR2
754 )
755 IS
756
757 --PRAGMA AUTONOMOUS_TRANSACTION; commenting out by pukken as per bug no 2907727
758
759 l_lookup_exists VARCHAR2(30);
760 l_history_code VARCHAR2(30);
761 l_entity_history_log_id NUMBER;
762 l_history_log_param_id NUMBER;
763
764 x_object_version_number NUMBER := FND_API.G_MISS_NUM;
765 --x1_entity_history_log_id NUMBER :=1;
766 --x1_object_version_number NUMBER(9) := 1;
767
768 CURSOR c_id IS
769 SELECT PV_GE_HISTORY_LOG_B_S.NEXTVAL
770 FROM dual;
771
772 CURSOR c_message_code (cv_message_code IN VARCHAR2) IS
773 SELECT 'Y'
774 FROM fnd_new_messages
775 WHERE message_name = cv_message_code
776 AND application_id in (691, 682);
777
778 l_exists VARCHAR2(1);
779 l_lookup_type VARCHAR2(30);
780 l_lookup_code VARCHAR2(30);
781
782 CURSOR c_param_id IS
783 SELECT PV_GE_HISTORY_LOG_PARAMS_S.NEXTVAL
784 FROM dual;
785
786 BEGIN
787
788 /* Standard Start of API savepoint */
789 SAVEPOINT history_log_sp;
790
791 -- Initialize message list if p_init_msg_list is set to TRUE.
792 IF FND_API.to_Boolean( p_init_msg_list )
793 THEN
794 FND_MSG_PUB.initialize;
795 END IF;
796
797 x_return_status := FND_API.g_ret_sts_success;
798
799 l_lookup_type := 'PV_INTERACTION_OBJECT_TYPE';--'AMS_SYS_ARC_QUALIFIER';
800 l_lookup_code := p_arc_history_for_entity_code;
801
802 -- Validate histry entity code against lookup table
803 l_lookup_exists := PVX_UTILITY_PVT.check_lookup_exists( p_lookup_table_name => 'PV_LOOKUPS',
804 p_lookup_type => l_lookup_type,
805 p_lookup_code => l_lookup_code );
806
807 IF NOT FND_API.to_boolean(l_lookup_exists) THEN
808 x_return_status := FND_API.g_ret_sts_error;
809 FND_MESSAGE.set_name('PV', 'PV_INVALID_LOOKUP_CODE');
810 FND_MESSAGE.set_token('LOOKUP_TYPE', l_lookup_type );
811 FND_MESSAGE.set_token('LOOKUP_CODE', l_lookup_code );
812 FND_MSG_PUB.add;
813 END IF;
814
815 IF x_return_status = FND_API.g_ret_sts_error THEN
816 RAISE FND_API.g_exc_error;
817 END IF;
818
819 l_lookup_type := 'PV_HISTORY_CATEGORY';
820 l_lookup_code := p_history_category_code;
821
822 -- Validate History Catagory Code
823 l_history_code := PVX_UTILITY_PVT.check_lookup_exists( p_lookup_table_name => 'PV_LOOKUPS',
824 p_lookup_type => l_lookup_type,
825 p_lookup_code => l_lookup_code );
826
827
828
829 IF NOT FND_API.to_boolean(l_history_code) THEN
830 x_return_status := FND_API.g_ret_sts_error;
831 FND_MESSAGE.set_name('PV', 'PV_INVALID_LOOKUP_CODE');
832 FND_MESSAGE.set_token('LOOKUP_TYPE',l_lookup_type );
833 FND_MESSAGE.set_token('LOOKUP_CODE',l_lookup_code );
834 FND_MSG_PUB.add;
835 END IF;
836
837 IF x_return_status = FND_API.g_ret_sts_error THEN
838 RAISE FND_API.g_exc_error;
839 END IF;
840
841
842 OPEN c_message_code(p_message_code);
843 FETCH c_message_code INTO l_exists;
844 IF c_message_code%NOTFOUND THEN
845 CLOSE c_message_code;
846 x_return_status := FND_API.g_ret_sts_error;
847 FND_MESSAGE.set_name('PV', 'PV_INVALID_MESSAGE_CODE');
848 FND_MESSAGE.set_token('MESSAGE_CODE',p_message_code );
849 FND_MSG_PUB.add;
850 RAISE FND_API.g_exc_error;
851 ELSE
852 CLOSE c_message_code;
853 END IF;
854
855
856 OPEN c_id;
857 FETCH c_id INTO l_entity_history_log_id;
858 CLOSE c_id;
859
860 -- Insert a row into the tables
861 PV_Ge_Hist_Log_PKG.Insert_Row (
862 px_entity_history_log_id => l_entity_history_log_id,
863 px_object_version_number => x_object_version_number,
864 p_arc_history_for_entity_code => p_arc_history_for_entity_code ,
865 p_history_for_entity_id => p_history_for_entity_id ,
866 p_message_code => p_message_code,
867 p_history_category_code => p_history_category_code ,
868 p_partner_id => p_partner_id,
869 p_access_level_flag => p_access_level_flag,
870 p_interaction_level => p_interaction_level,
871 p_comments => p_comments,
872 p_created_by => FND_GLOBAL.USER_ID,
873 p_creation_date => SYSDATE,
874 p_last_updated_by => FND_GLOBAL.USER_ID,
875 p_last_update_date => SYSDATE,
876 p_last_update_login => NULL
877 );
878
879
880
881 FOR i IN 1..p_log_params_tbl.count LOOP
882 OPEN c_param_id;
883 FETCH c_param_id INTO l_history_log_param_id;
884 CLOSE c_param_id;
885
886 PV_Ge_Hl_Param_PKG.Insert_Row (
887 px_history_log_param_id => l_history_log_param_id,
888 p_entity_history_log_id => l_entity_history_log_id,
889 p_param_name => p_log_params_tbl(i).param_name,
890 px_object_version_number => x_object_version_number,
891 p_param_value => p_log_params_tbl(i).param_value,
892 p_created_by => FND_GLOBAL.USER_ID,
893 p_creation_date => SYSDATE,
894 p_last_updated_by => FND_GLOBAL.USER_ID,
895 p_last_update_date => SYSDATE,
896 p_last_update_login => NULL,
897 p_param_type => p_log_params_tbl(i).param_type,
898 p_lookup_type => p_log_params_tbl(i).param_lookup_type
899 );
900
901 END LOOP;
902
903
904
905 FND_MSG_PUB.Count_And_Get
906 ( p_encoded => FND_API.G_FALSE,
907 p_count => x_msg_count,
908 p_data => x_msg_data
909 );
910
911 -- Standard check for p_commit
912 IF FND_API.to_Boolean( p_commit ) THEN
913 COMMIT WORK;
914 END IF;
915
916 EXCEPTION
917 WHEN FND_API.g_exc_error THEN
918 ROLLBACK TO history_log_sp;
919 x_return_status := FND_API.g_ret_sts_error;
920 FND_MSG_PUB.count_and_get (
921 p_encoded => FND_API.g_false
922 ,p_count => x_msg_count
923 ,p_data => x_msg_data
924 );
925
926 WHEN FND_API.g_exc_unexpected_error THEN
927 ROLLBACK TO history_log_sp;
928 x_return_status := FND_API.g_ret_sts_unexp_error ;
929 FND_MSG_PUB.count_and_get (
930 p_encoded => FND_API.g_false
931 ,p_count => x_msg_count
932 ,p_data => x_msg_data
933 );
934 WHEN OTHERS THEN
935 ROLLBACK TO history_log_sp;
936 x_return_status := FND_API.g_ret_sts_unexp_error ;
937 FND_MSG_PUB.count_and_get(
938 p_encoded => FND_API.g_false
939 ,p_count => x_msg_count
940 ,p_data => x_msg_data
941 );
942 END create_history_log;
943
944 PROCEDURE get_business_days
945 (
946 p_from_date IN DATE,
947 p_to_date IN DATE,
948 x_bus_days OUT NOCOPY NUMBER
949
950 )
951 IS
952 CURSOR C
953 IS
954 select count(*)
955 from ( select rownum rnum
956 from sys.all_objects
957 where rownum <= p_to_date - p_from_date)
958 where to_char( p_from_date+rnum, 'DY' ,'nls_date_language=english')
959 not in ( 'SAT', 'SUN' );
960 BEGIN
961 IF p_from_date = p_to_date THEN
962
963 IF to_char(p_from_date, 'DY','nls_date_language=english') IN ('SAT','SUN') THEN
964 x_bus_days := 0;
965 ELSE
966 x_bus_days := 1;
967 END IF;
968 ELSE
969 OPEN C;
970 FETCH C into x_bus_days;
971 CLOSE C;
972 END IF;
973 END get_business_days;
974
975 PROCEDURE add_business_days
976 (
977 p_no_of_days IN NUMBER,
978 x_business_date OUT NOCOPY DATE
979
980 )
981 IS
982 l_no_of_wkend number(30);
983 l_date DATE;
984
985 BEGIN
986
987 l_Date := sysdate;
988
989 x_business_date := l_Date;
990
991 -- If this program is run in the weekend the actual day the calculation starts will
992 -- be from the following business day: Monday, 00:00 AM
993
994 IF to_char(l_Date, 'DY','nls_date_language=english') = 'SAT' THEN
995 x_business_date := trunc(x_business_date + 2);
996 ELSIF to_char(l_Date, 'DY','nls_date_language=english') = 'SUN' THEN
997 x_business_date := trunc(x_business_date + 1);
998 END IF;
999
1000 -- If interval specified is 9 or 8 , this program is run on thurdays or fridays
1001 -- then actual weekends will be 2 instead of one .
1002 IF to_char(l_Date,'DY','nls_date_language=english') IN ('THU','FRI') THEN
1003 IF mod(p_no_of_days,5) = 4 OR mod(p_no_of_days,5) = 3 THEN
1004 x_business_date := x_business_date+2;
1005 END IF;
1006 END IF;
1007
1008 -- Now add no of business days
1009 x_business_date := x_business_date + p_no_of_days;
1010
1011 l_no_of_wkend := trunc(p_no_of_days/5);
1012
1013 -- Here the interval means the number of business days excluding weekends
1014 -- If the interval crosses the weekends the number of weekends will be added
1015 -- to the output date
1016
1017 IF l_no_of_wkend <> 0 THEN
1018 x_business_date := x_business_date + l_no_of_wkend*2;
1019 END IF;
1020
1021 -- If the calculated date falls on the weekend, then move it to the following business day
1022 IF to_char(x_business_date, 'DY','nls_date_language=english') IN ('SAT') THEN
1023 x_business_date := x_business_date + 2;
1024 ELSIF to_char(x_business_date, 'DY','nls_date_language=english') IN ('SUN') THEN
1025 x_business_date := x_business_date + 2;
1026 END IF;
1027
1028 END;
1029
1030 END PVX_Utility_PVT;