[Home] [Help]
PACKAGE BODY: APPS.PON_PROFILE_UTIL_PKG
Source
1 PACKAGE BODY PON_PROFILE_UTIL_PKG as
2 /*$Header: PONPRUTB.pls 120.7 2006/03/31 05:49:41 rpatel noship $ */
3
4 PROCEDURE update_organization_start_date(
5 p_party_id IN NUMBER
6 , x_status OUT NOCOPY VARCHAR2
7 , x_exception_msg OUT NOCOPY VARCHAR2
8 )
9 IS
10 BEGIN
11 UPDATE hz_organization_profiles
12 SET effective_start_date=trunc(SYSDATE)
13 WHERE party_id=p_party_id;
14
15 x_exception_msg :=NULL;
16 EXCEPTION
17
18 WHEN OTHERS THEN
19 --dbms_output.put_line('Other failure -- '||x_exception_msg);
20 raise;
21
22 END update_organization_start_date;
23
24 FUNCTION get_update_date_from_party (
25 p_party_id IN NUMBER
26 ) RETURN DATE
27 IS
28 l_date DATE;
29 BEGIN
30 SELECT last_update_date
31 INTO l_date
32 FROM hz_parties
33 WHERE party_id = p_party_id;
34 return l_date;
35 END get_update_date_from_party;
36
37
38 FUNCTION get_update_date_from_location (
39 p_location_id IN NUMBER
40 ) RETURN DATE
41 IS
42 l_date DATE;
43 BEGIN
44 SELECT last_update_date
45 INTO l_date
46 FROM hz_locations
47 WHERE location_id = p_location_id;
48 return l_date;
49 END get_update_date_from_location;
50
51 FUNCTION get_update_date_from_contact (
52 p_contact_id IN NUMBER
53 ) RETURN DATE
54 IS
55 l_date DATE;
56 BEGIN
57 SELECT last_update_date
58 INTO l_date
59 FROM hz_contact_points
60 WHERE contact_point_id = p_contact_id;
61 return l_date;
62 END get_update_date_from_contact;
63
64 PROCEDURE update_ins_party_pref_cover(
65 p_party_id in NUMBER
66 , p_app_short_name in VARCHAR2
67 , p_pref_name in VARCHAR2
68 , p_pref_value in VARCHAR2
69 , p_pref_meaning in VARCHAR2
70 , x_status out nocopy VARCHAR2
71 , x_exception_msg out nocopy VARCHAR2
72 )
73 IS
74 att1 VARCHAR2(150):= NULL;
75 att2 VARCHAR2(150):= NULL;
76 att3 VARCHAR2(150):= NULL;
77 att4 VARCHAR2(150):= NULL;
78 att5 VARCHAR2(150):= NULL;
79 BEGIN
80 update_or_insert_party_pref(
81 p_party_id
82 , p_app_short_name
83 , p_pref_name
84 , p_pref_value
85 , p_pref_meaning
86 , att1, att2, att3, att4, att5
87 , x_status
88 , x_exception_msg
89 );
90 x_status := 'S';
91 EXCEPTION
92 WHEN OTHERS THEN
93 x_status := 'E';
94 END;
95
96 PROCEDURE UPDATE_OR_INSERT_PARTY_PREF(
97 p_party_id in NUMBER
98 , p_app_short_name in VARCHAR2
99 , p_pref_name in VARCHAR2
100 , p_pref_value in VARCHAR2
101 , p_pref_meaning in VARCHAR2
102 , p_attribute1 in VARCHAR2
103 , p_attribute2 in VARCHAR2
104 , p_attribute3 in VARCHAR2
105 , p_attribute4 in VARCHAR2
106 , p_attribute5 in VARCHAR2
107 , x_status out nocopy VARCHAR2
108 , x_exception_msg out nocopy VARCHAR2
109 )
110 IS
111 l_count NUMBER;
112 BEGIN
113 x_exception_msg :='entering update_or_insert_party_perference';
114 select count(*)
115 into l_count
116 from PON_PARTY_PREFERENCES
117 where party_id = p_party_id
118 AND preference_name = p_pref_name
119 AND APP_SHORT_NAME = p_app_short_name;
120
121 if l_count > 0 then
122 -- do an update because row exists
123 x_exception_msg :='updating party_perference';
124 UPDATE PON_PARTY_PREFERENCES
125 SET PREFERENCE_VALUE = p_pref_value
126 , PREFERENCE_MEANING = p_pref_meaning
127 , ATTRIBUTE1 = p_attribute1
128 , ATTRIBUTE2 = p_attribute2
129 , ATTRIBUTE3 = p_attribute3
130 , ATTRIBUTE4 = p_attribute4
131 , ATTRIBUTE5 = p_attribute5
132 WHERE party_id = p_party_id
133 AND preference_name = p_pref_name
134 AND app_short_name = p_app_short_name;
135 ELSE
136 x_exception_msg :='inserting party_perference';
137 -- insert a new row because it doesn't exist
138 insert into PON_PARTY_PREFERENCES
139 (
140 party_id
141 , APP_SHORT_NAME
142 , preference_name
143 , PREFERENCE_VALUE
144 , PREFERENCE_MEANING
145 , ATTRIBUTE1
146 , ATTRIBUTE2
147 , ATTRIBUTE3
148 , ATTRIBUTE4
149 , ATTRIBUTE5
150 )
151 VALUES
152 (
153 p_party_id
154 , p_app_short_name
155 , p_pref_name
156 , p_pref_value
157 , p_pref_meaning
158 , p_attribute1
159 , p_attribute2
160 , p_attribute3
161 , p_attribute4
162 , p_attribute5
163 );
164 END IF;
165
166 x_exception_msg :='';
167 x_status := 'S';
168 EXCEPTION
169 WHEN OTHERS THEN
170 x_status := 'E';
171
172 END UPDATE_OR_INSERT_PARTY_PREF;
173
174 -- hzheng
175 PROCEDURE DELETE_PARTY_PREF(
176 p_party_id in NUMBER
177 , p_app_short_name in VARCHAR2
178 , p_pref_name in VARCHAR2
179 , x_status out nocopy VARCHAR2
180 , x_exception_msg out nocopy VARCHAR2
181 )
182 IS
183
184 l_count NUMBER;
185 BEGIN
186 x_exception_msg :='entering DELETE_PARTY_PREF';
187
188 SELECT count(*)
189 INTO l_count
190 FROM PON_PARTY_PREFERENCES
191 WHERE party_id = p_party_id
192 AND preference_name = p_pref_name
193 AND APP_SHORT_NAME = p_app_short_name;
194
195 IF l_count > 0 THEN
196
197 DELETE FROM PON_PARTY_PREFERENCES
198 WHERE party_id = p_party_id
199 AND preference_name = p_pref_name
200 AND APP_SHORT_NAME = p_app_short_name;
201
202 END IF;
203
204 x_exception_msg :='';
205 x_status := 'S';
206
207 EXCEPTION
208
209 WHEN OTHERS THEN
210 ROLLBACK;
211 x_status := 'E';
212 x_exception_msg := x_exception_msg||' ' ||SQLERRM;
213
214 END DELETE_PARTY_PREF;
215
216
217
218 PROCEDURE retrieve_party_pref_cover(
219 p_party_id IN NUMBER
220 , p_app_short_name IN VARCHAR2
221 , p_pref_name IN VARCHAR2
222 , x_pref_value OUT NOCOPY VARCHAR2
223 , x_pref_meaning OUT NOCOPY VARCHAR2
224 , x_status OUT NOCOPY VARCHAR2
225 , x_exception_msg OUT NOCOPY VARCHAR2
226 )
227 IS
228 meaning VARCHAR2(150);
229 att1 VARCHAR2(150);
230 att2 VARCHAR2(150);
231 att3 VARCHAR2(150);
232 att4 VARCHAR2(150);
233 att5 VARCHAR2(150);
234 BEGIN
235 retrieve_party_preference(p_party_id, p_app_short_name, p_pref_name,
236 x_pref_value, x_pref_meaning, att1,att2,att3,att4,att5, x_status,
237 x_exception_msg);
238
239 END;
240
241
242 PROCEDURE RETRIEVE_PARTY_PREFERENCE(
243 p_party_id in NUMBER
244 , p_app_short_name in VARCHAR2
245 , p_pref_name in VARCHAR2
246 , x_pref_value out nocopy VARCHAR2
247 , x_pref_meaning out nocopy VARCHAR2
248 , x_attribute1 out nocopy VARCHAR2
249 , x_attribute2 out nocopy VARCHAR2
250 , x_attribute3 out nocopy VARCHAR2
251 , x_attribute4 out nocopy VARCHAR2
252 , x_attribute5 out nocopy VARCHAR2
253 , x_status out nocopy VARCHAR2
254 , x_exception_msg out nocopy VARCHAR2
255 )
256 IS
257 BEGIN
258 x_exception_msg :='entering retrieve_party_perference';
259 x_status := 'S';
260
261 -- Since now sourcing is hosted for one buyer company, no need to
262 -- check for party id. This will simply code so that when supplier
263 -- tries to retrieve a party preference, he doesn't need to pass
264 -- buyer company's party id
265 SELECT preference_value, preference_meaning, attribute1, attribute2,
266 attribute3, attribute4, attribute5
267 INTO x_pref_value,x_pref_meaning,x_attribute1,x_attribute2,x_attribute3,
268 x_attribute4, x_attribute5
269 FROM PON_PARTY_PREFERENCES
270 WHERE app_short_name= p_app_short_name
271 AND preference_name=p_pref_name
272 AND rownum = 1;
273
274 EXCEPTION
275 WHEN NO_DATA_FOUND THEN
276 x_status := 'E';
277 -- default value of N for BEST_PRICE_VISIBLE_BLIND flag
278 IF (p_pref_name = 'BEST_PRICE_VISIBLE_BLIND') THEN
279 x_pref_value := 'N';
280 x_status := 'S';
281 END IF;
282
283 WHEN OTHERS THEN
284 x_status := 'U';
285 x_exception_msg := 'unexpected error retrieving preference : ' || p_pref_name || ' party id: ' ||p_party_id;
286
287
288 END RETRIEVE_PARTY_PREFERENCE;
289
290 PROCEDURE get_party_url(party_id IN NUMBER
291 , url OUT NOCOPY VARCHAR2
292 , x_status OUT NOCOPY VARCHAR2
293 , x_exception_msg OUT NOCOPY VARCHAR2)
294 IS
295 attribute1 VARCHAR2(150);
296 attribute2 VARCHAR2(150);
297 attribute3 VARCHAR2(150);
298 attribute4 VARCHAR2(150);
299 attribute5 VARCHAR2(150);
300 meaning VARCHAR2(150);
301 BEGIN
302 retrieve_party_preference(p_party_id => party_id,
303 p_app_short_name => 'PON',
304 p_pref_name => 'PON_URL',
305 x_pref_value => url,
306 x_pref_meaning => meaning,
307 x_attribute1 => attribute1,
308 x_attribute2 => attribute2,
309 x_attribute3 => attribute3,
310 x_attribute4 => attribute4,
311 x_attribute5 => attribute5,
312 x_status => x_status,
313 x_exception_msg => x_exception_msg);
314 END GET_PARTY_URL;
315
316 PROCEDURE get_party_slogan(party_id IN NUMBER
317 , slogan OUT NOCOPY VARCHAR2
318 , x_status OUT NOCOPY VARCHAR2
319 , x_exception_msg OUT NOCOPY VARCHAR2)
320 IS
321 attribute1 VARCHAR2(150);
322 attribute2 VARCHAR2(150);
323 attribute3 VARCHAR2(150);
324 attribute4 VARCHAR2(150);
325 attribute5 VARCHAR2(150);
326 meaning VARCHAR2(150);
327 BEGIN
328 retrieve_party_preference(p_party_id => party_id,
329 p_app_short_name => 'PON',
330 p_pref_name => 'PON_SLOGAN',
331 x_pref_value => slogan,
332 x_pref_meaning => meaning,
333 x_attribute1 => attribute1,
334 x_attribute2 => attribute2,
335 x_attribute3 => attribute3,
336 x_attribute4 => attribute4,
337 x_attribute5 => attribute5,
338 x_status => x_status,
339 x_exception_msg => x_exception_msg);
340 END GET_PARTY_SLOGAN;
341
342 PROCEDURE get_party_port(party_id IN NUMBER
343 , port OUT NOCOPY VARCHAR2
344 , x_status OUT NOCOPY VARCHAR2
345 , x_exception_msg OUT NOCOPY VARCHAR2)
346 IS
347 attribute1 VARCHAR2(150);
348 attribute2 VARCHAR2(150);
349 attribute3 VARCHAR2(150);
350 attribute4 VARCHAR2(150);
351 attribute5 VARCHAR2(150);
352 meaning VARCHAR2(150);
353 BEGIN
354 retrieve_party_preference(p_party_id => party_id,
355 p_app_short_name => 'PON',
356 p_pref_name => 'PON_PORT',
357 x_pref_value => port,
358 x_pref_meaning => meaning,
359 x_attribute1 => attribute1,
360 x_attribute2 => attribute2,
361 x_attribute3 => attribute3,
362 x_attribute4 => attribute4,
363 x_attribute5 => attribute5,
364 x_status => x_status,
365 x_exception_msg => x_exception_msg);
366 END GET_PARTY_PORT;
367
368 --
369 -- Obsoleted. Please do not use.
370 --
371 PROCEDURE SET_WF_LANGUAGE(p_user_name IN VARCHAR2,p_language_code IN VARCHAR2)
372 IS
373 l_language VARCHAR2(150);
374 BEGIN
375 SELECT NLS_LANGUAGE
376 INTO l_language
377 FROM FND_LANGUAGES
378 WHERE LANGUAGE_CODE = p_language_code;
379
380 fnd_preference.put(upper(p_user_name), 'WF', 'LANGUAGE', l_language);
381 END SET_WF_LANGUAGE;
382
383 --
384 -- don't be confused with the procedure name (it's a legacy)
385 -- this procedure actually get the ICX_LANGUAGE of the user.
386 -- a temporary workaround for bug 2354113.
387 --
388 PROCEDURE GET_WF_LANGUAGE(p_user_name IN VARCHAR2,x_language_code OUT NOCOPY VARCHAR2)
389 IS
390 l_language VARCHAR2(150);
391 ln_user_id NUMBER;
392 BEGIN
393 BEGIN
394 SELECT user_id
395 INTO ln_user_id
396 FROM fnd_user
397 WHERE user_name = upper(p_user_name);
398 EXCEPTION
399 WHEN NO_DATA_FOUND THEN
400 ln_user_id := null;
401 END;
402
403 l_language := fnd_profile.value_specific('ICX_LANGUAGE', ln_user_id, NULL, NULL);
404 SELECT LANGUAGE_CODE
405 INTO x_language_code
406 FROM FND_LANGUAGES
407 WHERE NLS_LANGUAGE = l_language;
408 END GET_WF_LANGUAGE;
409
410 --
411 -- don't be confused with the procedure name (it's a legacy)
412 -- this procedure actually get the ICX_LANGUAGE of the user.
413 -- a temporary workaround for bug 2354113.
414 --
415 PROCEDURE GET_WF_LANGUAGE(p_user_id IN NUMBER,x_language_code OUT NOCOPY VARCHAR2)
416 IS
417 l_language VARCHAR2(150);
418 BEGIN
419
420 l_language := fnd_profile.value_specific('ICX_LANGUAGE', p_user_id, NULL, NULL);
421 SELECT LANGUAGE_CODE
422 INTO x_language_code
423 FROM FND_LANGUAGES
424 WHERE NLS_LANGUAGE = l_language;
425 END GET_WF_LANGUAGE;
426
427 --
428 -- obsolete. no more valid. please do not use.
429 -- use ICX preferences instead
430 --
431 PROCEDURE SET_WF_TERRITORY(p_user_name IN VARCHAR2,p_territory_code IN VARCHAR2)
432 IS
433 l_territory VARCHAR2(150);
434 BEGIN
435
436 SELECT NLS_TERRITORY
437 INTO l_territory
438 FROM FND_TERRITORIES
439 WHERE TERRITORY_CODE = p_territory_code;
440
441 fnd_preference.put(upper(p_user_name), 'WF', 'TERRITORY', l_territory);
442
443 END SET_WF_TERRITORY;
444
445 --
446 -- bug fix for 2668483
447 --
448 PROCEDURE GET_WF_TERRITORY(p_user_name IN VARCHAR2,x_territory_code OUT NOCOPY VARCHAR2)
449 IS
450 l_territory VARCHAR2(150);
451 ln_user_id fnd_user.user_id%TYPE;
452 BEGIN
453
454 SELECT user_id
455 INTO ln_user_id
456 FROM fnd_user
457 WHERE user_name = upper(p_user_name);
458
459 l_territory := fnd_profile.value_specific('ICX_TERRITORY', ln_user_id, NULL, NULL);
460
461 SELECT TERRITORY_CODE
462 INTO x_territory_code
463 FROM FND_TERRITORIES
464 WHERE NLS_TERRITORY = l_territory;
465
466 END GET_WF_TERRITORY;
467
468 --
469 -- obsolete. no more valid. please do not use.
470 -- use ICX preferences instead
471 --
472 PROCEDURE SET_WF_PREFERENCES( p_user_name IN VARCHAR2,
473 p_language_code IN VARCHAR2,
474 p_territory_code IN VARCHAR2)
475 IS
476 l_language VARCHAR2(150);
477 l_territory VARCHAR2(150);
478 BEGIN
479
480 SELECT NLS_LANGUAGE
481 INTO l_language
482 FROM FND_LANGUAGES
483 WHERE LANGUAGE_CODE = p_language_code;
484
485 SELECT NLS_TERRITORY
486 INTO l_territory
487 FROM FND_TERRITORIES
488 WHERE TERRITORY_CODE = p_territory_code;
489
490 fnd_preference.put(upper(p_user_name), 'WF', 'LANGUAGE', l_language);
491 fnd_preference.put(upper(p_user_name), 'WF', 'TERRITORY', l_territory);
492
493 END SET_WF_PREFERENCES;
494
495 --
496 -- bug fix for 2668483
497 --
498 PROCEDURE GET_WF_PREFERENCES( p_user_name IN VARCHAR2,
499 x_language_code OUT NOCOPY VARCHAR2,
500 x_territory_code OUT NOCOPY VARCHAR2)
501 IS
502 BEGIN
503 get_wf_language(p_user_name, x_language_code);
504 get_wf_territory(p_user_name, x_territory_code);
505
506 END GET_WF_PREFERENCES;
507
508 -- GET_STRING- get a particular translated message
509 -- from the message dictionary database.
510 -- This is a one-call interface for when you just want to get a
511 -- message without doing any token substitution.
512 -- Returns NAMEIN (Msg name) if the message cannot be found.
513 FUNCTION get_string(appin IN VARCHAR2,
514 namein IN VARCHAR2,
515 langin IN VARCHAR2)
516 RETURN VARCHAR2
517 IS
518 MSG varchar2(2000) := NULL;
519
520
521 CURSOR c1(name_arg VARCHAR2) IS SELECT message_text
522 FROM fnd_new_messages m, fnd_application a
523 WHERE name_arg = m.message_name
524 AND m.language_code = langin
525 AND appin = a.application_short_name
526 AND m.application_id = a.application_id;
527
528 CURSOR c2(name_arg VARCHAR2) IS SELECT message_text
529 FROM fnd_new_messages m, fnd_application a
530 WHERE name_arg = m.message_name
531 AND 'US' = m.language_code
532 AND appin = a.application_short_name
533 AND m.application_id = a.application_id;
534 BEGIN
535 /* get the message text out of the table */
536 OPEN c1(upper(namein));
537 FETCH c1 INTO msg;
538 IF c1%notfound THEN
539 OPEN c2(Upper(namein));
540 FETCH c2 INTO msg;
541 IF c2%notfound THEN
542 msg := namein;
543 END IF;
544 CLOSE c2;
545 END IF;
546 CLOSE c1;
547 /* double ampersands don't have anything to do with tokens, they */
548 /* represent access keys. So we translate them to single ampersands*/
549 /* so that the access key code will recognize them. */
550 msg := substrb(REPLACE(msg, '&&', '&'),1,2000);
551 RETURN msg;
552 END get_string;
553
554 FUNCTION SET_PRINT_OPTIONS RETURN VARCHAR2
555 IS
556
557 l_printer_state boolean;
558 l_status VARCHAR2(3);
559
560 BEGIN
561
562 /* Change printer options, as work around to
563 concurrent manager bug 1880369 */
564
565 l_printer_state := FND_REQUEST.SET_PRINT_OPTIONS(printer=>'noprint',copies => 0);
566
567 IF l_printer_state = TRUE THEN
568 l_status := 'Y';
569 ELSE
570 l_status := 'N';
571 END IF;
572
573 RETURN l_status;
574
575 END set_print_options;
576
577 FUNCTION save_profile_option(p_option_name IN VARCHAR2,
578 p_option_value IN VARCHAR2,
579 p_level_name IN VARCHAR2) RETURN VARCHAR2
580 IS
581
582 l_saved boolean;
583 l_status VARCHAR2(3);
584
585 BEGIN
586
587 l_saved := FND_PROFILE.SAVE(p_option_name, p_option_value, p_level_name, null, null);
588
589
590 IF l_saved = TRUE THEN
591 l_status := 'Y';
592 ELSE
593 l_status := 'N';
594 END IF;
595
596 RETURN l_status;
597
598 END save_profile_option;
599
600
601 FUNCTION relationship_exists(
602 p_subject_id IN NUMBER
603 , p_object_id IN NUMBER
604 , p_relationship_type IN VARCHAR2
605 , p_relationship_code IN VARCHAR2
606 ) RETURN VARCHAR2
607 IS
608
609 l_count NUMBER;
610
611 BEGIN
612
613 SELECT count(*)
614 INTO l_count
615 FROM hz_relationships
616 WHERE subject_id = p_subject_id
617 AND object_id = p_object_id
618 AND relationship_type = p_relationship_type
619 AND relationship_code = p_relationship_code
620 AND status = 'A'
621 AND start_date <= sysdate
622 AND end_date >= sysdate
623 AND ROWNUM < 2;
624
625 IF l_count > 0 THEN
626 return 'Y';
627 ELSE
628 return 'N';
629 END IF;
630
631 EXCEPTION
632 WHEN OTHERS THEN
633 raise;
634 END relationship_exists;
635
636
637 PROCEDURE lines_more_than_threshold(
638 p_number_of_lines IN NUMBER,
639 p_party_id IN NUMBER,
640 x_is_super_large_neg OUT NOCOPY VARCHAR2)
641 is
642 l_pref_value VARCHAR2(240);
643 l_pref_meaning VARCHAR2(240);
644 l_status VARCHAR2(240);
645 l_exception_msg VARCHAR2(240);
646 l_attribute VARCHAR2(240);
647 l_super_large_lines_threshold VARCHAR2(30);
648 l_meaning VARCHAR2(240);
649 BEGIN
650 RETRIEVE_PARTY_PREFERENCE (
651 p_party_id => p_party_id,
652 p_app_short_name => 'PON',
653 p_pref_name => 'CONCURRENT_PROCESS_LINE_START',
654 x_pref_value => l_super_large_lines_threshold,
655 x_pref_meaning => l_meaning,
656 x_attribute1 => l_attribute,
657 x_attribute2 =>l_attribute,
658 x_attribute3 => l_attribute,
659 x_attribute4 => l_attribute,
660 x_attribute5 => l_attribute,
661 x_status => l_status,
662 x_exception_msg => l_exception_msg);
663
664 IF (l_super_large_lines_threshold IS NULL) THEN
665 l_super_large_lines_threshold := PON_LARGE_AUCTION_UTIL_PKG.g_default_lines_threshold;
666 END IF;
667
668 IF (p_number_of_lines > l_super_large_lines_threshold) THEN
669 x_is_super_large_neg := 'Y';
670 ELSE
671 x_is_super_large_neg := 'N';
672 END IF;
673
674
675 END lines_more_than_threshold;
676
677 END PON_PROFILE_UTIL_PKG;