1 PACKAGE BODY PON_OA_UTIL_PKG as
2 /* $Header: PONOAUTB.pls 120.9.12010000.2 2008/10/30 10:02:21 jianliu ship $ */
3
4 g_monitor_rules bizrules;
5 g_offer_rules bizrules;
6 g_fetched_doctype_rules boolean := FALSE;
7
8
9 /*======================================================================
10 PROCEDURE : LOAD_DOCTYPE_RULES PRIVATE
11 PARAMETERS:
12 none
13 COMMENT : load doc type rules into package variables
14 ======================================================================*/
15
16 PROCEDURE LOAD_DOCTYPE_RULES IS
17
18 CURSOR doctypes IS
19 SELECT doctype_id
20 FROM pon_auc_doctypes;
21
22 BEGIN
23
24 IF (not g_fetched_doctype_rules) THEN
25
26 FOR doctype IN doctypes LOOP
27
28 BEGIN
29 select nvl(r.validity_flag,'N')
30 into g_monitor_rules(doctype.doctype_id)
31 from pon_auc_doctype_rules r, pon_auc_bizrules biz
32 where biz.name = 'USE_AUCTION_MONITOR'
33 and r.bizrule_id = biz.bizrule_id
34 and r.doctype_id = doctype.doctype_id;
35 EXCEPTION WHEN NO_DATA_FOUND THEN
36 g_monitor_rules(doctype.doctype_id) := 'N';
37 END;
38
39 BEGIN
40 select decode(nvl(r.fixed_value,'NONE'),'COMMIT','Y','N')
41 into g_offer_rules(doctype.doctype_id)
42 from pon_auc_doctype_rules r, pon_auc_bizrules biz
43 where biz.name = 'AWARD_TYPE'
44 and r.bizrule_id = biz.bizrule_id
45 and r.doctype_id = doctype.doctype_id;
46 EXCEPTION WHEN NO_DATA_FOUND THEN
47 g_offer_rules(doctype.doctype_id) := 'N';
48 END;
49
50 END LOOP;
51
52 g_fetched_doctype_rules := TRUE;
53
54 END IF;
55
56 END LOAD_DOCTYPE_RULES;
57
58 /*======================================================================
59 FUNCTION : MONITOR PUBLIC
60 PARAMETERS:
61 p_doctype_id IN document type id
62 p_bid_visibility IN bid visibility
63 p_sealed_auction_status IN sealed auction status
64 p_auctioneer_id IN auctioneer trading partner id
65 p_viewer_id IN viewer trading partner id
66 p_startdate IN auction open bidding date
67 p_has_items IN has Items Flag for negotiation
68
69 COMMENT : check if monitor icon should be active or not
70 ======================================================================*/
71
72 FUNCTION MONITOR (p_doctype_id IN NUMBER,
73 p_bid_visibility IN VARCHAR2,
74 p_sealed_auction_status IN VARCHAR2,
75 p_auctioneer_id IN NUMBER,
76 p_viewer_id IN NUMBER,
77 p_startdate IN DATE,
78 p_has_items IN VARCHAR2) RETURN VARCHAR2
79 IS
80
81
82 v_is_auctioneer boolean := FALSE;
83
84 BEGIN
85
86
87 IF (p_auctioneer_id is NULL) THEN
88 RETURN 'N';
89 END IF;
90
91 IF (p_startdate IS NULL OR p_startdate > SYSDATE) THEN
92 RETURN 'N';
93 END IF;
94
95 IF (p_auctioneer_id = p_viewer_id) THEN
96 v_is_auctioneer := TRUE;
97 ELSE
98 v_is_auctioneer := FALSE;
99 END IF;
100
101 load_doctype_rules;
102
103 IF (g_monitor_rules(p_doctype_id) <> 'Y') THEN
104 RETURN 'N';
105 END IF;
106
107 -- if negotiation does not have lines, return N
108 IF (p_has_items = 'N') THEN
109 RETURN 'N';
110 END IF;
111
112 IF (p_bid_visibility = 'OPEN_BIDDING') THEN
113 RETURN 'Y';
114 ELSE
115 IF (p_bid_visibility = 'SEALED_BIDDING') THEN
116 -- blind negotiation
117 IF (v_is_auctioneer) THEN
118 RETURN 'Y';
119 ELSE
120 RETURN 'N';
121 END IF;
122 ELSE -- sealed negotiation
123 IF (p_sealed_auction_status = 'LOCKED') THEN
124 -- bids locked
125 RETURN 'N';
126 ELSE
127 IF (p_sealed_auction_status = 'UNLOCKED') THEN
128 -- bids unlocked
129 IF (v_is_auctioneer) THEN
130 RETURN 'Y';
131 ELSE
132 RETURN 'N';
133 END IF;
134 ELSE -- bids open
135 RETURN 'Y';
136 END IF;
137 END IF;
138 END IF;
139 END IF;
140
141 END MONITOR;
142
143
144 /*======================================================================
145 FUNCTION : BUYER_MONITOR PUBLIC
146 PARAMETERS:
147 p_doctype_id IN document type id
148 p_bid_visibility IN bid visibility
149 p_sealed_auction_status IN sealed auction status
150 p_auctioneer_id IN auctioneer trading partner id
151 p_viewer_id IN viewer trading partner id
152 p_has_items IN has Items Flag for negotiation
153 p_doc_type IN Type of the negotiation(RFI,RFQ,AUCTION)
154 p_auction_status IN Status of the auction
155 p_view_by_date IN Preview date of the auction
156 p_open_bidding_date IN Open bidding date of the negotiation
157 p_auction_header_id IN Auction Header Id
158 p_has_scoring_teams_flag IN If auction has scoring teams
159
160 COMMENT : check if monitor icon should be active or not for buyer homepage and search page
161 ======================================================================*/
162 FUNCTION BUYER_MONITOR (p_doctype_id IN NUMBER,
163 p_bid_visibility IN VARCHAR2,
164 p_sealed_auction_status IN VARCHAR2,
165 p_auctioneer_id IN NUMBER,
166 p_viewer_id IN NUMBER,
167 p_has_items IN VARCHAR2,
168 p_doc_type IN VARCHAR2,
169 p_auction_status IN VARCHAR2,
170 p_view_by_date IN DATE,
171 p_open_bidding_date IN DATE,
172 p_auction_header_id IN NUMBER,
173 p_has_scoring_teams_flag IN VARCHAR2
174 ) RETURN VARCHAR2
175 IS
176
177
178 v_is_auctioneer boolean := FALSE;
179 l_doc_type PON_AUC_DOCTYPES.internal_name%TYPE;
180
181 CURSOR c_price_visibility (p_cur_auction_header_id NUMBER) IS
182 SELECT pst.team_id
183 FROM pon_scoring_teams pst,
184 pon_scoring_team_members pstm
185 WHERE pst.auction_header_id = pstm.auction_header_id
186 AND pst.team_id = pstm.team_id
187 AND pst.price_visible_flag = 'Y'
188 AND pstm.auction_header_id = p_cur_auction_header_id
189 AND pstm.user_id = FND_GLOBAL.user_id;
190
191 l_team_id pon_scoring_teams.team_id%TYPE;
192 l_menu_name pon_neg_team_members.menu_name%TYPE;
193
194 BEGIN
195
196 -- Teams Scoring
197 -- If the current user does not have price visibility return N
198 -- User has price visibility on THIS auction when
199 -- 1. user is a scorer
200 -- 2. AND belongs to a team that has price visiblity
201 -- We check only if user does not have price visibility
202 l_team_id := -1;
203 IF (p_has_scoring_teams_flag = 'Y') THEN
204 BEGIN
205 -- check if user is a scorer first
206 SELECT menu_name INTO l_menu_name
207 FROM pon_neg_team_members
208 WHERE auction_header_id = p_auction_header_id
209 AND list_id = -1
210 AND user_id = FND_GLOBAL.user_id;
211
212 IF ((l_menu_name IS NOT NULL) AND (l_menu_name = 'PON_SOURCING_SCORENEG')) THEN
213 -- if user is a scorer check if on a team that has price visibility
214 OPEN c_price_visibility(p_auction_header_id);
215 FETCH c_price_visibility INTO l_team_id;
216 CLOSE c_price_visibility;
217
218 IF (l_team_id = -1) THEN
219 RETURN 'N';
220 END IF;
221 END IF;
222 EXCEPTION
223 WHEN NO_DATA_FOUND THEN
224 null;
225 WHEN OTHERS THEN
226 raise;
227 END;
228 END IF;
229
230 l_doc_type := p_doc_type;
231
232 IF (p_auctioneer_id is NULL) THEN
233 RETURN 'N';
234 END IF;
235
236 -- if negotiation does not have lines, return N
237 IF (p_has_items = 'N') THEN
238 RETURN 'N';
239 END IF;
240
241 -- if negotiation is not open for preview, return N
242 IF (p_view_by_date IS NOT NULL AND p_view_by_date > SYSDATE) THEN
243 RETURN 'N';
244 ELSIF (p_view_by_date IS NULL AND p_open_bidding_date > SYSDATE) THEN
245 RETURN 'N';
246 END IF;
247
248 IF l_doc_type IS NULL THEN
249 SELECT internal_name
250 INTO l_doc_type
251 FROM PON_AUC_DOCTYPES
252 WHERE doctype_id = p_doctype_id;
253 END IF;
254
255 IF (l_doc_type= 'REQUEST_FOR_INFORMATION') THEN
256 RETURN 'N';
257 END IF;
258
259 IF (p_auction_status IN ('DRAFT','DELETED','AMENDED')) THEN
260 RETURN 'N';
261 END IF;
262
263 IF (p_auctioneer_id = p_viewer_id) THEN
264 v_is_auctioneer := TRUE;
265 ELSE
266 v_is_auctioneer := FALSE;
267 END IF;
268
269 IF (p_bid_visibility = 'OPEN_BIDDING') THEN
270 RETURN 'Y';
271 ELSE
272 IF (p_bid_visibility = 'SEALED_BIDDING') THEN
273 -- blind negotiation
274 IF (v_is_auctioneer) THEN
275 RETURN 'Y';
276 ELSE
277 RETURN 'N';
278 END IF;
279 ELSE -- sealed negotiation
280 IF (p_sealed_auction_status = 'LOCKED') THEN
281 -- bids locked
282 RETURN 'N';
283 ELSE
284 IF (p_sealed_auction_status = 'UNLOCKED') THEN
285 -- bids unlocked
286 IF (v_is_auctioneer) THEN
287 RETURN 'Y';
288 ELSE
289 RETURN 'N';
290 END IF;
291 ELSE -- bids open
292 RETURN 'Y';
293 END IF;
294 END IF;
295 END IF;
296 END IF;
297
298 END BUYER_MONITOR;
299
300
301 /*======================================================================
302 FUNCTION : MONITOR_IMAGE PUBLIC
303 PARAMETERS:
304 p_doctype_id IN document type id
305 p_bid_visibility IN bid visibility
306 p_sealed_auction_status IN sealed auction status
307 p_auctioneer_id IN auctioneer trading partner id
308 p_viewer_id IN viewer trading partner id
309 p_publishdate IN auction open bidding date
310 p_contract_type IN auction outcome(STANDARD, BLANKET, CONTRACT)
311 p_has_items IN has Items Flag for negotiation
312 p_doc_type IN Type of the negotiation(RFI,RFQ,AUCTION)
313 p_auction_status IN Status of the auction
314
315 COMMENT : return the monitor image name, either 'MonitorActive'
316 or 'MonitorInactive'
317 ======================================================================*/
318
319 FUNCTION MONITOR_IMAGE (p_doctype_id IN NUMBER,
320 p_bid_visibility IN VARCHAR2,
321 p_sealed_auction_status IN VARCHAR2,
322 p_auctioneer_id IN NUMBER,
323 p_viewer_id IN NUMBER,
324 p_startdate IN DATE,
325 p_has_items IN VARCHAR2) RETURN VARCHAR2
326 IS
327
328 BEGIN
329
330 IF(MONITOR(p_doctype_id, p_bid_visibility, p_sealed_auction_status, p_auctioneer_id, p_viewer_id, p_startdate,p_has_items) = 'Y') THEN
331 RETURN 'MonitorActive';
332 ELSE
333 RETURN 'MonitorInactive';
334 END IF;
335
336 END MONITOR_IMAGE;
337
338 /*======================================================================
339 FUNCTION : BUYER_MONITOR_IMAGE PUBLIC
340 PARAMETERS:
341 p_doctype_id IN document type id
342 p_bid_visibility IN bid visibility
343 p_sealed_auction_status IN sealed auction status
344 p_auctioneer_id IN auctioneer trading partner id
345 p_viewer_id IN viewer trading partner id
346 p_has_items IN has Items Flag for negotiation
347 p_doc_type IN Type of the negotiation(RFI,RFQ,AUCTION)
348 p_auction_status IN Status of the auction
349 p_view_by_date IN Preview date of the auction
350 p_open_bidding_date IN Open bidding date of the negotiation
351 p_auction_header_id IN Auction Header Id
352 p_has_scoring_teams_flag IN If auction has scoring teams
353
354
355 COMMENT : return the monitor image name, either 'MonitorActive'
356 or 'MonitorInactive'
357 In OA 5.6, we use switcher bean to implement monitor column.
358 In OA 5.7, we can just use a simple region item.
359 ======================================================================*/
360 FUNCTION BUYER_MONITOR_IMAGE (p_doctype_id IN NUMBER,
361 p_bid_visibility IN VARCHAR2,
362 p_sealed_auction_status IN VARCHAR2,
363 p_auctioneer_id IN NUMBER,
364 p_viewer_id IN NUMBER,
365 p_has_items IN VARCHAR2,
366 p_doc_type IN VARCHAR2,
367 p_auction_status IN VARCHAR2,
368 p_view_by_date IN DATE,
369 p_open_bidding_date IN DATE,
370 p_auction_header_id IN NUMBER,
371 p_has_scoring_teams_flag IN VARCHAR2
372 ) RETURN VARCHAR2
373 IS
374
375 BEGIN
376
377 IF(BUYER_MONITOR(p_doctype_id, p_bid_visibility, p_sealed_auction_status,
378 p_auctioneer_id, p_viewer_id, p_has_items, p_doc_type, p_auction_status,
379 p_view_by_date, p_open_bidding_date, p_auction_header_id,
380 p_has_scoring_teams_flag) = 'Y') THEN
381 RETURN 'MonitorActive';
382 ELSE
383 RETURN 'MonitorInactive';
384 END IF;
385
386 END BUYER_MONITOR_IMAGE;
387
388
389 /*======================================================================
390 FUNCTION : DICUSSION_URL PUBLIC
391 PARAMETERS:
392 p_auction_id IN auction header id
393 p_viewer_party_id IN viewer's trading partner id
394 p_app IN app name
395 p_subtab_pos IN subtab position
396
397 COMMENT : returns javascript for discussion icon
398 OAPageContext should add corresponding javascript definitions.
399 This assumes the threaded discussion pages are still in JSP.
400 Once they are moved to OAF, this function needs to be updated.
401 ======================================================================*/
402
403 FUNCTION DISCUSSION_URL (p_auction_id IN NUMBER,
404 p_viewer_party_id IN NUMBER,
405 p_app IN VARCHAR2,
406 p_subtab_pos IN VARCHAR2) RETURN VARCHAR2
407 IS
408
409 v_discussion_id NUMBER;
410
411 CURSOR discussion_id IS
412 select discussion_id
413 from pon_discussions
414 where entity_name = 'PON_AUCTION_HEADERS_ALL' and
415 pk1_value = to_char(p_auction_id);
416
417 BEGIN
418
419 open discussion_id;
420 fetch discussion_id
421 into v_discussion_id;
422 close discussion_id;
423
424 -- error handling if discussin id is null?
425
426 RETURN 'javascript:openDiscussionWindow(''/OA_HTML/jsp/pon/discussions/ThreadedNegotiationSummary.jsp?app=' || p_app || '&'
427 || 'SubTab=' || p_subtab_pos || '&' || 'discussion_id=' || v_discussion_id || ''',''discussion_' || v_discussion_id || '_user_' || p_viewer_party_id || '_depth_'' + (parseInt(getDiscussionWindowDepth()) + 1))';
428
429 END DISCUSSION_URL;
430
431
432
433 /*======================================================================
434 FUNCTION : GET_TIMEZONE_DISP PUBLIC
435 PARAMETERS:
436 p_client_timezone_id IN client (viewer) time zone id
437 p_server_timezone_id IN server time zone id
438
439
440 COMMENT : returns timezone to display
441 ======================================================================*/
442
443 FUNCTION GET_TIMEZONE_DISP(p_client_timezone_id IN VARCHAR2,
444 p_server_timezone_id IN VARCHAR2) RETURN VARCHAR2
445 IS
446
447
448
449 v_client_timezone VARCHAR2(50);
450 v_time_zone_name FND_TIMEZONES_VL.NAME%TYPE := NULL;
451
452
453 BEGIN
454
455 -- get client time zone, if null use server time zone
456 v_client_timezone := p_client_timezone_id;
457 if (v_client_timezone is null or v_client_timezone = '') then
458 v_client_timezone := p_server_timezone_id;
459 end if;
460
461
462 -- it's better to get time zone name in the middle tier once
463 -- instead of firing a sql to get the name for each table row
464 begin
465 select name
466 into v_time_zone_name
467 from fnd_timezones_vl
468 where upgrade_tz_id = to_number(v_client_timezone);
469 exception
470 WHEN NO_DATA_FOUND THEN
471 v_time_zone_name := 'Unknown Timezone';
472 end;
473
474 return v_time_zone_name;
475
476 END GET_TIMEZONE_DISP;
477
478
479 /*======================================================================
480 FUNCTION : TIME_REMAINING_SLASH_CLOSE PUBLIC
481 PARAMETERS:
482 p_startdate IN auction start date
483 p_enddate IN auction end date
484 p_client_timezone_id IN client (viewer) time zone id
485 p_server_timezone_id IN server time zone id
486 p_date_format IN date format
487
488 COMMENT : returns html formatted string of time remaining and close date
489 ======================================================================*/
490
491 /*
492 FUNCTION TIME_REMAINING_SLASH_CLOSE(
493 p_startdate IN DATE ,
494 p_enddate IN DATE ,
495 p_client_timezone_id IN VARCHAR2,
496 p_server_timezone_id IN VARCHAR2,
497 p_date_format IN VARCHAR2) RETURN VARCHAR2
498 IS
499
500 v_time_remaining VARCHAR2(100) := NULL;
501 v_close_date VARCHAR2(100) := NULL;
502
503 BEGIN
504 v_time_remaining := PON_AUCTION_PKG.TIME_REMAINING(
505 p_startdate,
506 p_enddate,
507 p_client_timezone_id,
508 p_server_timezone_id,
509 p_date_format);
510
511 --
512 -- Convert the dates to the user's timezone.
513 --
514 v_close_date := DISPLAY_DATE_TIME(p_enddate,
515 p_client_timezone_id,
516 p_server_timezone_id,
517 p_date_format);
518
519 return v_time_remaining || '/' || v_close_date;
520 END;
521 */
522
523 /*======================================================================
524 FUNCTION : TIME_REMAINING_SLASH_CLOSE PUBLIC
525 PARAMETERS:
526 p_startdate IN auction start date
527 p_enddate IN auction end date
528 p_client_timezone_id IN client (viewer) time zone id
529 p_server_timezone_id IN server time zone id
530 p_date_format IN date format
531 p_days_string IN translated 'PON_AUCTION_DAYS'
532 p_day_string IN translated 'PON_AUCTION_DAY'
533 p_hours_string IN translated 'PON_AUCTION_HOURS'
534 p_hour_string IN translated 'PON_AUCTION_HOUR'
535 p_minutes_string IN translated 'PON_AUCTION_MINUTES'
536 p_minute_string IN translated 'PON_AUCTION_MINUTE'
537
538 COMMENT : returns html formatted string of time remaining and close date
539 ======================================================================*/
540
541 /*
542 FUNCTION TIME_REMAINING_SLASH_CLOSE(
543 p_startdate IN DATE ,
544 p_enddate IN DATE ,
545 p_client_timezone_id IN VARCHAR2,
546 p_server_timezone_id IN VARCHAR2,
547 p_date_format IN VARCHAR2,
548 p_days_string IN VARCHAR2,
549 p_day_string IN VARCHAR2,
550 p_hours_string IN VARCHAR2,
551 p_hour_string IN VARCHAR2,
552 p_minutes_string IN VARCHAR2,
553 p_minute_string IN VARCHAR2) RETURN VARCHAR2
554 IS
555
556 v_time_remaining VARCHAR2(100) := NULL;
557 v_close_date VARCHAR2(100) := NULL;
558
559 BEGIN
560 v_time_remaining := PON_AUCTION_PKG.TIME_REMAINING(
561 p_startdate,
562 p_enddate,
563 p_client_timezone_id,
564 p_server_timezone_id,
565 p_date_format,
566 p_days_string,
567 p_day_string,
568 p_hours_string,
569 p_hour_string,
570 p_minutes_string,
571 p_minute_string);
572
573 --
574 -- Convert the dates to the user's timezone.
575 --
576 v_close_date := DISPLAY_DATE_TIME(p_enddate,
577 p_client_timezone_id,
578 p_server_timezone_id,
579 p_date_format);
580
581 return v_time_remaining || '/' || v_close_date;
582 END;
583 */
584
585 /*======================================================================
586 FUNCTION : TIME_REMAINING_CLOSE_DATE PUBLIC
587 PARAMETERS:
588 p_startdate IN auction start date
589 p_enddate IN auction end date
590 p_client_timezone_id IN client (viewer) time zone id
591 p_server_timezone_id IN server time zone id
592 p_date_format IN date format
593
594 COMMENT : returns html formatted string of time remaining and close date
595 ======================================================================*/
596
597 /*
598 FUNCTION TIME_REMAINING_CLOSE_DATE(p_startdate IN DATE ,
599 p_enddate IN DATE ,
600 p_client_timezone_id IN VARCHAR2,
601 p_server_timezone_id IN VARCHAR2,
602 p_date_format IN VARCHAR2) RETURN VARCHAR2
603 IS
604
605 v_time_remaining VARCHAR2(100) := NULL;
606 v_close_date VARCHAR2(100) := NULL;
607
608 BEGIN
609 v_time_remaining := PON_AUCTION_PKG.TIME_REMAINING(p_startdate, p_enddate,
610 p_client_timezone_id, p_server_timezone_id, p_date_format);
611
612
613 --
614 -- Convert the dates to the user's timezone.
615 --
616 v_close_date := DISPLAY_DATE_TIME(p_enddate,
617 p_client_timezone_id,
618 p_server_timezone_id,
619 p_date_format);
620
621
622 return '<b>' || v_time_remaining || '</b> <br>' || v_close_date;
623
624 END TIME_REMAINING_CLOSE_DATE;
625 */
626
627
628
629 /*======================================================================
630 FUNCTION : TIME_REMAINING_CLOSE_DATE_NOTZ PUBLIC
631 PARAMETERS:
632 p_startdate IN auction start date
633 p_enddate IN auction end date
634 p_client_timezone_id IN client (viewer) time zone id
635 p_server_timezone_id IN server time zone id
636 p_date_format IN date format
637
638
639 COMMENT : returns html formatted string of time remaining and close date
640 without the timezone at the end
641 ======================================================================*/
642
643 /*
644 FUNCTION TIME_REMAINING_CLOSE_DATE_NOTZ(p_startdate IN DATE ,
645 p_enddate IN DATE ,
646 p_client_timezone_id IN VARCHAR2,
647 p_server_timezone_id IN VARCHAR2,
648 p_date_format IN VARCHAR2) RETURN VARCHAR2
649 IS
650
651 v_time_remaining VARCHAR2(100) := NULL;
652 v_close_date VARCHAR2(100) := NULL;
653 v_difference NUMBER := 0;
654
655 BEGIN
656 v_difference := to_number(p_enddate-sysdate);
657
658 -- bug fix 2835097
659 IF (v_difference <= 31) THEN
660
661 v_time_remaining := PON_AUCTION_PKG.TIME_REMAINING(p_startdate, p_enddate,
662 p_client_timezone_id, p_server_timezone_id, p_date_format);
663
664 END IF;
665
666
667 --
668 -- Convert the dates to the user's timezone.
669 --
670 v_close_date := DISPLAY_DATE_TIME(p_enddate,
671 p_client_timezone_id,
672 p_server_timezone_id,
673 p_date_format,
674 'N');
675
676 IF (v_difference > 31) THEN
677 return v_close_date;
678 ELSE
679 return v_time_remaining || ' ' || v_close_date;
680 END IF;
681
682 END TIME_REMAINING_CLOSE_DATE_NOTZ;
683 */
684
685
686
687 /*======================================================================
688 FUNCTION : DISPLAY_DATE_TIME PUBLIC
689 PARAMETERS:
690 p_date IN a date value
691 p_client_timezone_id IN client (viewer) time zone id
692 p_server_timezone_id IN server time zone id
693 p_date_format IN date format
694 p_display_timezone IN whether or not to display timezone
695
696 COMMENT : returns date and time converted to client time zone
697 assumes the passed in date is in server time zone
698 ======================================================================*/
699
700 FUNCTION DISPLAY_DATE_TIME(p_date IN DATE ,
701 p_client_timezone_id IN VARCHAR2,
702 p_server_timezone_id IN VARCHAR2,
703 p_date_format IN VARCHAR2,
704 p_display_timezone IN VARCHAR2 )
705 RETURN VARCHAR2
706 IS
707
708 v_time_format VARCHAR2(20) := ' HH24:MI:SS'; -- time format is fixed
709 v_client_timezone VARCHAR2(50);
710 v_new_date DATE;
711 v_time_zone_name FND_TIMEZONES_VL.NAME%TYPE := NULL;
712 v_return_string VARCHAR2(1000);
713
714 BEGIN
715
716 -- get client time zone, if null use server time zone
717 v_client_timezone := p_client_timezone_id;
718 if (v_client_timezone is null or v_client_timezone = '') then
719 v_client_timezone := p_server_timezone_id;
720 end if;
721
722 --
723 -- Convert the dates to the user's timezone.
724 --
725
726 IF (PON_OEX_TIMEZONE_PKG.VALID_ZONE(v_client_timezone) = 1) THEN
727 v_new_date := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(p_date,p_server_timezone_id,v_client_timezone);
728 ELSE
729 v_new_date := p_date;
730 v_client_timezone := p_server_timezone_id;
731 END IF;
732
733 IF (p_display_timezone = 'Y') THEN
734 v_time_zone_name := GET_TIMEZONE_DISP(p_client_timezone_id => v_client_timezone,
735 p_server_timezone_id => p_server_timezone_id);
736 END IF;
737
738 v_return_string := to_char(v_new_date, p_date_format || v_time_format);
739 IF (p_display_timezone = 'Y') THEN
740 v_return_string := v_return_string || ' ' || v_time_zone_name;
741 END IF;
742
743 return v_return_string;
744
745 END DISPLAY_DATE_TIME;
746 /*======================================================================
747 FUNCTION : CONVERT_DATE PUBLIC
748 PARAMETERS:
749 p_date IN a date value
750 p_client_timezone_id IN client (viewer) time zone id
751 p_server_timezone_id IN server time zone id
752
753 COMMENT : returns date converted to client's time zone
754 assumes the passed in date is in server time zone
755 ======================================================================*/
756
757 FUNCTION CONVERT_DATE(p_date IN DATE DEFAULT NULL,
758 p_client_timezone_id IN VARCHAR2,
759 p_server_timezone_id IN VARCHAR2)
760 RETURN DATE
761 IS
762 v_client_timezone VARCHAR2(50);
763 v_new_date DATE;
764 BEGIN
765 -- get client time zone, if null use server time zone
766 v_client_timezone := p_client_timezone_id;
767 if (v_client_timezone is null or v_client_timezone = '') then
768 v_client_timezone := p_server_timezone_id;
769 end if;
770
771 --
772 -- Convert the dates to the user's timezone.
773 --
774
775 IF (PON_OEX_TIMEZONE_PKG.VALID_ZONE(v_client_timezone) = 1) THEN
776 v_new_date := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(p_date,p_server_timezone_id,v_client_timezone);
777 ELSE
778 v_new_date := p_date;
779 END IF;
780
781 RETURN v_new_date;
782 END CONVERT_DATE;
783
784 /*======================================================================
785 FUNCTION : DISPLAY_DATE PUBLIC
786 PARAMETERS:
787 p_date IN a date value
788 p_client_timezone_id IN client (viewer) time zone id
789 p_server_timezone_id IN server time zone id
790 p_date_format IN date format
791 p_display_timzezone IN whether to display time zone
792
793 COMMENT : returns date converted to client time zone
794 assumes the passed in date is in server time zone
795 ======================================================================*/
796
797 FUNCTION DISPLAY_DATE (p_date IN DATE ,
798 p_client_timezone_id IN VARCHAR2,
799 p_server_timezone_id IN VARCHAR2,
800 p_date_format IN VARCHAR2,
801 p_display_timezone IN VARCHAR2 )
802 RETURN VARCHAR2
803 IS
804 v_time_format VARCHAR2(20) := ' HH24:MI:SS'; -- time format is fixed
805 return_date VARCHAR2(20) := null;
806 BEGIN
807
808 return_date := DISPLAY_DATE_TIME(p_date, p_client_timezone_id,
809 p_server_timezone_id,
810 p_date_format,
811 p_display_timezone);
812
813 return to_char(to_date(return_date,p_date_format || v_time_format), p_date_format);
814 END DISPLAY_DATE;
815
816 /*======================================================================
817 FUNCTION : RESPONSE_VIEWMORENEGS PUBLIC
818 PARAMETERS:
819 p_auctioneer_id IN auctioneer trading partner id
820 p_viewer_tp_id IN viewer trading partner id,
821 p_query_type IN query type
822 p_number_of_bids IN number of bids
823 p_bid_visibility IN bid visibility code
824 p_sealed_auction_status IN sealed auction status
825
826 COMMENT : calculates value for response column in view more negotiations
827 page
828 ======================================================================*/
829
830 FUNCTION RESPONSE_VIEWMORENEGS (p_auctioneer_id IN NUMBER,
831 p_viewer_tp_id IN NUMBER,
832 p_query_type IN VARCHAR2,
833 p_number_of_bids IN NUMBER,
834 p_bid_visibility IN VARCHAR2,
835 p_sealed_auction_status IN VARCHAR2) RETURN VARCHAR2
836 IS
837
838 v_is_auctioneer boolean := FALSE;
839 v_is_auction_sealed boolean := FALSE;
840
841 BEGIN
842
843 IF (p_auctioneer_id = p_viewer_tp_id) THEN
844 v_is_auctioneer := TRUE;
845 ELSE
846 v_is_auctioneer := FALSE;
847 END IF;
848
849 IF (p_bid_visibility = 'SEALED_AUCTION') THEN
850 v_is_auction_sealed := TRUE;
851 ELSE
852 v_is_auction_sealed := FALSE;
853 END IF;
854
855 IF (p_query_type = 'Glance') THEN
856 -- for glance query, for auctioneers, show number of bids,
857 -- for bidders, show the bid number
858 IF(v_is_auctioneer) THEN
859 IF(p_number_of_bids > 0) THEN
860 RETURN 'BidHist';
861 ELSE
862 RETURN 'NumOfBids';
863 END IF;
864 ELSE -- bidder
865 IF( (not v_is_auction_sealed) or
866 (v_is_auction_sealed and p_sealed_auction_status = 'ACTIVE')) THEN
867 RETURN 'ViewBid';
868 ELSE
869 -- bidder can't see even the number of bids if this auction is not active
870 RETURN 'Sealed';
871 END IF;
872 END IF;
873 ELSE
874 IF (p_query_type = 'Invite') THEN
875 -- for invites, always show number of bids
876 IF(v_is_auctioneer) THEN
877 IF( (not v_is_auction_sealed) or
878 (v_is_auction_sealed and (not p_sealed_auction_status = 'LOCKED'))) THEN
879 IF(p_number_of_bids > 0) THEN
880 RETURN 'BidHist';
881 ELSE
882 RETURN 'NumOfBids';
883 END IF;
884 ELSE -- the auctioneer can't see number of bids at that time
885 RETURN 'Sealed';
886 END IF;
887 ELSE -- bidder
888 IF ( (not v_is_auction_sealed) and (not p_bid_visibility = 'SEALED_BIDDING') or (v_is_auction_sealed and p_sealed_auction_status = 'ACTIVE') ) THEN
889 IF(p_number_of_bids > 0) THEN
890 RETURN 'BidHist';
891 ELSE
892 RETURN 'NumOfBids';
893 END IF;
894 ELSE -- bidder can't see even the number of bids if this auction is not active
895 RETURN 'NegStyle';
896 END IF;
897 END IF;
898 END IF;
899 END IF;
900
901 END RESPONSE_VIEWMORENEGS;
902
903 /*======================================================================
904 FUNCTION : RESPONSE_VIEWAUCTIONS PUBLIC
905 PARAMETERS:
906 p_auctioneer_id IN auctioneer trading partner id
907 p_viewer_tp_id IN viewer trading partner id,
908 p_number_of_bids IN number of bids
909 p_bid_visibility IN bid visibility code
910 p_sealed_auction_status IN sealed auction status
911
912 COMMENT : calculates value for response column in view auctions page
913 ======================================================================*/
914
915 FUNCTION RESPONSE_VIEWAUCTIONS (p_auctioneer_id IN NUMBER,
916 p_viewer_tp_id IN NUMBER,
917 p_number_of_bids IN NUMBER,
918 p_bid_visibility IN VARCHAR2,
919 p_sealed_auction_status IN VARCHAR2) RETURN VARCHAR2
920 IS
921
922 v_is_auctioneer boolean := FALSE;
923 v_is_auction_sealed boolean := FALSE;
924 v_is_auction_blind boolean := FALSE;
925
926 BEGIN
927
928 IF (p_auctioneer_id = p_viewer_tp_id) THEN
929 v_is_auctioneer := TRUE;
930 ELSE
931 v_is_auctioneer := FALSE;
932 END IF;
933
934 IF (p_bid_visibility = 'SEALED_AUCTION') THEN
935 v_is_auction_sealed := TRUE;
936 ELSE
937 v_is_auction_sealed := FALSE;
938 END IF;
939
940 IF (p_bid_visibility = 'SEALED_BIDDING') THEN
941 v_is_auction_blind := TRUE;
942 ELSE
943 v_is_auction_blind := FALSE;
944 END IF;
945
946 IF (((not v_is_auction_sealed) or
947 (v_is_auction_sealed and p_sealed_auction_status = 'ACTIVE')) and
948 (( not v_is_auction_blind ) or
949 (v_is_auction_blind and v_is_auctioneer)) ) THEN
950
951 IF(p_number_of_bids > 0) THEN
952 RETURN 'BidHist';
953 ELSE
954 RETURN 'NumOfBids';
955 END IF;
956 END IF;
957
958 IF (v_is_auction_sealed and (not p_sealed_auction_status = 'ACTIVE')) THEN
959 IF (v_is_auctioneer) THEN
960 IF (p_sealed_auction_status = 'UNLOCKED') THEN
961 -- auctioneer can see all the bids if this auction is unlocked
962
963 IF(p_number_of_bids > 0) THEN
964 RETURN 'BidHist';
965 ELSE
966 RETURN 'NumOfBids';
967 END IF;
968 ELSE
969 IF (p_sealed_auction_status = 'LOCKED') THEN
970 -- now auctioneer can see the number of bids but not the bid detail
971 RETURN 'NumOfBids';
972 END IF;
973 END IF;
974 ELSE
975 -- bidder can't see even the number of bids if this auction is not active
976 RETURN 'Sealed';
977 END IF;
978 END IF;
979
980 IF (v_is_auction_blind and not v_is_auctioneer) THEN
981 RETURN 'Blind';
982 END IF;
983
984 END RESPONSE_VIEWAUCTIONS;
985
986
987 /*======================================================================
988 FUNCTION : TRUNCATE PUBLIC
989 PARAMETERS:
990 p_string IN input string
991
992 COMMENT : Truncate a large string to 30 chars appended by ...
993 This is equivalent to SummaryPages.truncate
994 ======================================================================*/
995
996 FUNCTION TRUNCATE (p_string IN VARCHAR2) RETURN VARCHAR2
997 IS
998
999 BEGIN
1000
1001 return truncate(p_string, 30);
1002
1003 END TRUNCATE;
1004
1005 /*======================================================================
1006 FUNCTION : TRUNCATE_DISPLAY_STRING PUBLIC
1007 PARAMETERS:
1008 p_string IN input string
1009
1010 COMMENT : Truncate a large string to 240 chars (default) appended by ...
1011 This is equivalent to AuctionUtil.truncateDisplayString
1012 ======================================================================*/
1013
1014 FUNCTION TRUNCATE_DISPLAY_STRING (p_string IN VARCHAR2) RETURN VARCHAR2
1015 IS
1016
1017 BEGIN
1018
1019 return truncate(p_string, 240);
1020
1021 END TRUNCATE_DISPLAY_STRING;
1022
1023 /*======================================================================
1024 FUNCTION : TRUNCATE PUBLIC
1025 PARAMETERS:
1026 p_string IN input string
1027 p_length IN truncation length
1028
1029 COMMENT : Truncate a large string appended by ...
1030 ======================================================================*/
1031
1032 FUNCTION TRUNCATE (p_string IN VARCHAR2,
1033 p_length IN NUMBER) RETURN VARCHAR2
1034 IS
1035
1036 BEGIN
1037 IF (p_length = null or p_length < 1) THEN
1038 return p_string;
1039 END IF;
1040
1041 IF (length(p_string) > p_length) THEN
1042 return substr(p_string, 1, p_length) || '...';
1043 ELSE
1044 return p_string;
1045 END IF;
1046
1047 END TRUNCATE;
1048
1049 /*======================================================================
1050 FUNCTION : RESPONSE_VIEWACTIVEBIDS PUBLIC
1051 PARAMETERS:
1052 p_auction_id IN auction header id
1053 p_auction_status IN auction status
1054 p_auctioneer_id IN auctioneer trading partner id
1055 p_viewer_tp_id IN viewer trading partner id,
1056 p_viewer_tpc_id IN viewer trading partner contact id,
1057 p_bid_visibility IN bid visibility code
1058 p_sealed_auction_status IN sealed auction status
1059 p_bidStatus IN bid status
1060 p_bidder_tpc_id IN bidder trading partner contact id
1061
1062 COMMENT : calculates value for number of bids column in the ViewActiveBids page
1063 ======================================================================*/
1064
1065 FUNCTION RESPONSE_VIEWACTIVEBIDS (p_auction_id NUMBER,
1066 p_auction_status IN VARCHAR2,
1067 p_auctioneer_id IN NUMBER,
1068 p_viewer_tp_id IN NUMBER,
1069 p_viewer_tpc_id IN NUMBER,
1070 p_bid_visibility IN VARCHAR2,
1071 p_sealed_auction_status IN VARCHAR2,
1072 p_bidStatus IN VARCHAR2,
1073 p_bidder_tpc_id IN NUMBER) RETURN VARCHAR2
1074 IS
1075
1076 v_is_auctioneer boolean := FALSE;
1077 v_is_viewer_the_bidder boolean := FALSE;
1078 v_is_auction_sealed boolean := FALSE;
1079 v_is_auction_blind boolean := FALSE;
1080 v_is_draft boolean := FALSE;
1081 v_most_recent_auction_id NUMBER;
1082 v_auction_status VARCHAR2(25);
1083 v_bidding_status VARCHAR2(25);
1084 v_two_part_flag pon_auction_headers_all.two_part_flag%TYPE;
1085 v_technical_lock_status pon_auction_headers_all.technical_lock_status%TYPE;
1086
1087 BEGIN
1088
1089 IF (p_bidStatus IS NOT NULL AND p_bidStatus = 'DRAFT') THEN
1090 v_is_draft := TRUE;
1091 END IF;
1092 --
1093 IF (p_viewer_tpc_id = p_bidder_tpc_id) THEN
1094 v_is_viewer_the_bidder := TRUE;
1095 END IF;
1096 --
1097 IF (p_auctioneer_id = p_viewer_tp_id) THEN
1098 v_is_auctioneer := TRUE;
1099 ELSE
1100 v_is_auctioneer := FALSE;
1101 END IF;
1102
1103 IF (p_bid_visibility = 'SEALED_AUCTION') THEN
1104 v_is_auction_sealed := TRUE;
1105 ELSE
1106 v_is_auction_sealed := FALSE;
1107 END IF;
1108
1109 IF (p_bid_visibility = 'SEALED_BIDDING') THEN
1110 v_is_auction_blind := TRUE;
1111 ELSE
1112 v_is_auction_blind := FALSE;
1113 END IF;
1114
1115 SELECT nvl(two_part_flag, 'N'), nvl(technical_lock_status, 'N')
1116 INTO v_two_part_flag, v_technical_lock_status
1117 FROM pon_auction_headers_all
1118 WHERE auction_header_id = p_auction_id;
1119
1120 IF ((v_is_auction_sealed) AND
1121 ((v_two_part_flag = 'N' AND NOT p_sealed_auction_status = 'ACTIVE') OR (v_two_part_flag = 'Y' AND NOT v_technical_lock_status = 'ACTIVE')) AND
1122 (NOT v_is_auctioneer) AND
1123 (NOT v_is_viewer_the_bidder) AND
1124 (NOT v_is_draft)) THEN
1125
1126 RETURN 'SealedAuction';
1127
1128 ELSIF ((v_is_auction_blind) AND (NOT v_is_auctioneer) AND (NOT v_is_viewer_the_bidder) AND (NOT v_is_draft)) THEN
1129 RETURN 'BlindAuction';
1130
1131 ELSE
1132 -- Now check if there are any amendments pending
1133 IF ((p_bidStatus IS NOT NULL AND p_bidStatus = 'RESUBMISSION') OR
1134 (p_auction_status = 'AMENDED' and v_is_draft)) THEN
1135
1136 -- check whether the most recent amendment has been closed or cancelled
1137 -- if yes, then don't need to show warning icon on seller home page
1138 -- and active/draft responses page
1139
1140 select max(ah.auction_header_id)
1141 into v_most_recent_auction_id
1142 from pon_auction_headers_all ah,
1143 pon_auction_headers_all ah2
1144 where ah.auction_status <> 'DRAFT'
1145 and ah.auction_header_id_orig_amend = ah2.auction_header_id_orig_amend
1146 and ah2.auction_header_id = p_auction_id;
1147
1148 select auction_status,
1149 decode(sign(close_bidding_date - sysdate), 1, 'NOT_CLOSED', 'CLOSED')
1150 into v_auction_status,
1151 v_bidding_status
1152 from pon_auction_headers_all
1153 where auction_header_id = v_most_recent_auction_id;
1154
1155 IF ((v_auction_status IS NOT NULL AND v_auction_status = 'CANCELLED') OR
1156 (v_bidding_status = 'CLOSED')) THEN
1157 IF (v_is_draft) THEN
1158 RETURN 'DraftBid';
1159 ELSE
1160 RETURN 'BidNumber';
1161 END IF;
1162 ELSE
1163 IF (v_is_draft) THEN
1164 RETURN 'ResubmitDraft';
1165 ELSE
1166 RETURN 'Resubmit';
1167 END IF;
1168 END IF;
1169
1170 ELSE
1171
1172 IF v_is_draft THEN
1173 RETURN 'DraftBid';
1174 ELSE
1175 RETURN 'BidNumber';
1176 END IF;
1177
1178 END IF;
1179
1180 END IF;
1181
1182 END RESPONSE_VIEWACTIVEBIDS;
1183
1184
1185 /*======================================================================
1186 FUNCTION : NUMBIDS_VIEWACTIVEBIDS PUBLIC
1187 PARAMETERS:
1188 p_number_of_bids IN number of bids
1189 p_bid_visibility IN bid visibility code
1190 p_sealed_auction_status IN sealed auction status
1191
1192 COMMENT : calculates value for number of bids column in the ViewActiveBids page
1193 ======================================================================*/
1194
1195 FUNCTION NUMBIDS_VIEWACTIVEBIDS (p_number_of_bids IN NUMBER,
1196 p_bid_visibility IN VARCHAR2,
1197 p_sealed_auction_status IN VARCHAR2) RETURN VARCHAR2
1198 IS
1199
1200 v_is_auction_sealed boolean := FALSE;
1201 v_is_auction_blind boolean := FALSE;
1202
1203 BEGIN
1204
1205 IF (p_bid_visibility = 'SEALED_AUCTION') THEN
1206 v_is_auction_sealed := TRUE;
1207 ELSE
1208 v_is_auction_sealed := FALSE;
1209 END IF;
1210
1211 IF (p_bid_visibility = 'SEALED_BIDDING') THEN
1212 v_is_auction_blind := TRUE;
1213 ELSE
1214 v_is_auction_blind := FALSE;
1215 END IF;
1216
1217 IF ( (v_is_auction_sealed) AND
1218 (NOT p_sealed_auction_status = 'ACTIVE')) THEN
1219
1220 RETURN 'SealedAuction';
1221
1222 ELSIF (v_is_auction_blind) THEN
1223 RETURN 'BlindAuction';
1224
1225 ELSIF (p_number_of_bids > 0) THEN
1226 RETURN 'NumOfBidsURL';
1227 ELSE
1228 RETURN 'NumOfBidsText';
1229 END IF;
1230
1231 END NUMBIDS_VIEWACTIVEBIDS;
1232
1233 /*======================================================================
1234 FUNCTION : BID_NUMBER_SORT PUBLIC
1235 PARAMETERS:
1236 p_auction_id IN auction header id
1237 p_auction_status IN auction status
1238 p_auctioneer_id IN auctioneer trading partner id
1239 p_viewer_tp_id IN viewer trading partner id,
1240 p_viewer_tpc_id IN viewer trading partner contact id,
1241 p_bid_visibility IN bid visibility code
1242 p_sealed_auction_status IN sealed auction status
1243 p_bidStatus IN bid status
1244 p_bidder_tpc_id IN bidder trading partner contact id
1245 p_bid_number IN bid Number
1246
1247 COMMENT : This function is to determine sorting column of bid number.
1248 The sorting order is :
1249 Bids whose number we can display
1250 Blind
1251 Sealed
1252 ======================================================================*/
1253
1254 FUNCTION BID_NUMBER_SORT (p_auction_id NUMBER,
1255 p_auction_status IN VARCHAR2,
1256 p_auctioneer_id IN NUMBER,
1257 p_viewer_tp_id IN NUMBER,
1258 p_viewer_tpc_id IN NUMBER,
1259 p_bid_visibility IN VARCHAR2,
1260 p_sealed_auction_status IN VARCHAR2,
1261 p_bidStatus IN VARCHAR2,
1262 p_bidder_tpc_id IN NUMBER,
1263 p_bid_number IN NUMBER) RETURN NUMBER
1264 IS
1265
1266 v_is_auctioneer boolean := FALSE;
1267 v_is_viewer_the_bidder boolean := FALSE;
1268 v_is_auction_sealed boolean := FALSE;
1269 v_is_auction_blind boolean := FALSE;
1270 v_is_draft boolean := FALSE;
1271 v_most_recent_auction_id NUMBER;
1272 v_auction_status VARCHAR2(25);
1273 v_bidding_status VARCHAR2(25);
1274
1275 BEGIN
1276
1277 IF (p_bidStatus IS NOT NULL AND p_bidStatus = 'DRAFT') THEN
1278 v_is_draft := TRUE;
1279 END IF;
1280 --
1281 IF (p_viewer_tpc_id = p_bidder_tpc_id) THEN
1282 v_is_viewer_the_bidder := TRUE;
1283 END IF;
1284 --
1285 IF (p_auctioneer_id = p_viewer_tp_id) THEN
1286 v_is_auctioneer := TRUE;
1287 ELSE
1288 v_is_auctioneer := FALSE;
1289 END IF;
1290
1291 IF (p_bid_visibility = 'SEALED_AUCTION') THEN
1292 v_is_auction_sealed := TRUE;
1293 ELSE
1294 v_is_auction_sealed := FALSE;
1295 END IF;
1296
1297 IF (p_bid_visibility = 'SEALED_BIDDING') THEN
1298 v_is_auction_blind := TRUE;
1299 ELSE
1300 v_is_auction_blind := FALSE;
1301 END IF;
1302
1303 IF ((v_is_auction_sealed) AND
1304 (NOT p_sealed_auction_status = 'ACTIVE') AND
1305 (NOT v_is_auctioneer) AND
1306 (NOT v_is_viewer_the_bidder) AND
1307 (NOT v_is_draft)) THEN
1308
1309 RETURN p_bid_number+2000000;
1310
1311 ELSIF ((v_is_auction_blind) AND (NOT v_is_auctioneer) AND (NOT v_is_viewer_the_bidder) AND (NOT v_is_draft)) THEN
1312 RETURN p_bid_number+1000000;
1313 ELSE
1314 RETURN p_bid_number;
1315 END IF;
1316
1317 END BID_NUMBER_SORT;
1318
1319 /*======================================================================
1320 FUNCTION : HTML_FORMATTED_HR_ADDRESS PUBLIC
1321 PARAMETERS:
1322 p_location_id IN location id for the address
1323 p_language IN language
1324 COMMENT : Returns the html formatted address for the given location
1325 ======================================================================*/
1326 FUNCTION HTML_FORMATTED_HR_ADDRESS(p_location_id IN NUMBER,
1327 p_language IN VARCHAR2) RETURN VARCHAR2
1328
1329 IS
1330
1331 v_address_name hr_locations.location_code%TYPE;
1332 v_address_1 hr_locations.address_line_1%TYPE;
1333 v_address_2 hr_locations.address_line_2%TYPE;
1334 v_address_3 hr_locations.address_line_3%TYPE;
1335 v_city hr_locations.town_or_city%TYPE;
1336 v_state hr_locations.region_2%TYPE;
1337 v_province_or_region hr_locations.region_3%TYPE;
1338 v_zip_code hr_locations.postal_code%TYPE;
1339 v_postal_code hr_locations.postal_code%TYPE;
1340 v_country hr_locations.country%TYPE;
1341 v_county hr_locations.region_1%TYPE;
1342 v_territory_name fnd_territories_tl.territory_short_name%TYPE;
1343 v_return_string VARCHAR2(1000);
1344
1345 BEGIN
1346
1347 SELECT
1348 hl.location_code,
1349 hl.address_line_1,
1350 hl.address_line_2,
1351 hl.address_line_3,
1352 hl.town_or_city,
1353 hl.region_2,
1354 hl.region_3,
1355 hl.postal_code,
1356 hl.postal_code,
1357 hl.country,
1358 hl.region_1
1359 INTO
1360 v_address_name,
1361 v_address_1,
1362 v_address_2,
1363 v_address_3,
1364 v_city,
1365 v_state,
1366 v_province_or_region,
1367 v_zip_code,
1368 v_postal_code,
1369 v_country,
1370 v_county
1371 FROM hr_locations hl
1372 WHERE hl.location_id = p_location_id;
1373
1374 BEGIN
1375 SELECT
1376 tl.territory_short_name
1377 INTO v_territory_name
1378 FROM fnd_territories_tl tl
1379 WHERE tl.territory_code = v_country
1380 AND tl.territory_code NOT IN ('ZR','FX','LX')
1381 AND tl.language = p_language;
1382 EXCEPTION
1383 WHEN no_data_found THEN
1384 v_territory_name := v_country;
1385 END;
1386
1387 IF (v_country = 'JP') THEN
1388 v_return_string := v_territory_name || '<br>';
1389
1390 -- zip code
1391 IF (v_postal_code IS NOT null) THEN
1392 v_return_string := v_return_string || v_postal_code || '<br>';
1393 ELSE
1394 IF (v_zip_code IS NOT null) THEN
1395 v_return_string := v_return_string || v_zip_code || '<br>';
1396 END IF;
1397 END IF;
1398
1399 -- province
1400 IF (v_province_or_region IS NOT null) THEN
1401 v_return_string := v_return_string || v_province_or_region || '<br>';
1402 END IF;
1403
1404 -- city
1405 v_return_string := v_return_string || v_city || '<br>';
1406
1407 -- address
1408 v_return_string := v_return_string || v_address_1 || '<br>';
1409 IF (v_address_2 IS NOT null) THEN
1410 v_return_string := v_return_string || v_address_2 || '<br>';
1411 END IF;
1412 IF (v_address_3 IS NOT null) THEN
1413 v_return_string := v_return_string || v_address_3 || '<br>';
1414 END IF;
1415
1416 ELSE -- not a japanese address
1417
1418 -- address
1419 v_return_string := v_address_1 || '<br>';
1420 IF (v_address_2 IS NOT null) THEN
1421 v_return_string := v_return_string || v_address_2 || '<br>';
1422 END IF;
1423 IF (v_address_3 IS NOT null) THEN
1424 v_return_string := v_return_string || v_address_3 || '<br>';
1425 END IF;
1426
1427 -- city
1428 v_return_string := v_return_string || v_city;
1429
1430 -- state
1431 IF (v_country = 'US') THEN -- US address
1432 IF (v_state is NOT null) THEN
1433 v_return_string := v_return_string || ',' || v_state;
1434 END IF;
1435 ELSE -- not a US address
1436 IF (v_province_or_region IS NOT null) THEN
1437 v_return_string := v_return_string || '<br>' || v_province_or_region;
1438 END IF;
1439 END IF;
1440
1441 -- postal or zip code
1442 IF (v_postal_code IS NOT null) THEN
1443 v_return_string := v_return_string || ' ' || v_postal_code;
1444 ELSE
1445 IF (v_zip_code IS NOT null) THEN
1446 v_return_string := v_return_string || ' ' || v_zip_code;
1447 END IF;
1448 END IF;
1449
1450 v_return_string := v_return_string || '<br>' || v_territory_name;
1451
1452 END IF;
1453 RETURN v_return_string;
1454
1455 END html_formatted_hr_address;
1456
1457
1458 /*======================================================================
1459 FUNCTION : HTML_FORMATTED_EMAIL_STRING PUBLIC
1460 PARAMETERS:
1461 p_email1 IN input string
1462 p_email2 IN input string
1463
1464 COMMENT : returns html formatted string of up to 2 email address.
1465 If both emails are specified, they are separated by a slash.
1466 This is currently used in ViewBiddersList to display
1467 contact and additional contact emails.
1468 ======================================================================*/
1469
1470 FUNCTION HTML_FORMATTED_EMAIL_STRING (p_email1 IN VARCHAR2,
1471 p_email2 IN VARCHAR2) RETURN VARCHAR2
1472 IS
1473
1474 v_email_1 VARCHAR2(400) := NULL;
1475 v_email_2 VARCHAR2(400) := NULL;
1476 v_separator VARCHAR2(10) := NULL;
1477
1478 BEGIN
1479
1480 IF (p_email1 is not null) THEN
1481 v_email_1 := '<a href="mailto:' || p_email1 || '">' || p_email1 || '</a>';
1482 END IF;
1483
1484 IF (p_email2 is not null) THEN
1485 v_email_2 := '<a href="mailto:' || p_email2 || '">' || p_email2 || '</a>';
1486 END IF;
1487
1488 IF (p_email1 is not null and p_email2 is not null) THEN
1489 v_separator := ' / ';
1490 END IF;
1491
1492 return v_email_1 || v_separator || v_email_2;
1493
1494 END HTML_FORMATTED_EMAIL_STRING;
1495
1496 /*======================================================================
1497 FUNCTION : GET_HTML_FORMATTED_BID_STRING PUBLIC
1498 PARAMETERS:
1499 p_doctype_id IN doc type id
1500 p_auction_header_id IN auction id
1501 p_trading_partner_id IN supplier trading partner id
1502 p_app_name IN application name
1503
1504 COMMENT : returns html formatted string of all active responses placed by
1505 a supplier on a negotiation.
1506 This is currently used in ViewBiddersList to display
1507 the response column.
1508 ======================================================================*/
1509
1510 FUNCTION GET_HTML_FORMATTED_BID_STRING (p_doctype_id IN NUMBER,
1511 p_auction_header_id IN NUMBER,
1512 p_trading_partner_id IN NUMBER,
1513 p_app_name IN VARCHAR2) RETURN VARCHAR2
1514 IS
1515
1516 v_return_string VARCHAR2(1000) := NULL;
1517
1518 CURSOR bids IS
1519 select bid_number
1520 from pon_bid_headers
1521 where auction_header_id = p_auction_header_id and
1522 trading_partner_id = p_trading_partner_id and
1523 bid_status = 'ACTIVE';
1524
1525 CURSOR counteroffers IS
1526 select substrb(to_char(bid_number)||(decode (bid_revision_number,null,'','-'||to_char(bid_revision_number))),0,10) bid_number_display,
1527 bid_number
1528 from pon_bid_headers
1529 where auction_header_id = p_auction_header_id and
1530 trading_partner_id = p_trading_partner_id and
1531 bid_status = 'ACTIVE' and
1532 nvl(award_status,'NONE') <> 'COMMITTED';
1533
1534 BEGIN
1535
1536 load_doctype_rules;
1537
1538 IF (g_offer_rules(p_doctype_id) <> 'Y') THEN
1539 -- for RFQs, Auctions, use bids cursor
1540 FOR bid IN bids LOOP
1541 v_return_string := v_return_string || '<a href="jsp/pon/auctions/ViewBid.jsp?AUCTION_HEADER_ID=' || p_auction_header_id || '&' || 'BID_NUMBER=' || bid.bid_number || '&' || 'app=' || p_app_name || '">' || bid.bid_number || '</a> <BR>';
1542 END LOOP;
1543 ELSE
1544 -- for offers, use counteroffers and commitments
1545 FOR counteroffer IN counteroffers LOOP
1546 v_return_string := v_return_string || '<a href="jsp/pon/auctions/viewCounterDetails.jsp?doc_id=' || p_auction_header_id || '&' ||
1547 'resp_id=' || counteroffer.bid_number || '&' || 'app=' || p_app_name || '">' || counteroffer.bid_number_display || '</a> <BR>';
1548 END LOOP;
1549
1550 END IF;
1551
1552 return v_return_string;
1553
1554 END GET_HTML_FORMATTED_BID_STRING;
1555
1556
1557 /*======================================================================
1558 FUNCTION : RESPONSE_VIEWBIDDERSLIST PUBLIC
1559 PARAMETERS:
1560 p_doctype_id IN doc type id
1561 p_auction_header_id IN auction id
1562 p_auctioneer_id IN auctioneer trading partner id
1563 p_viewer_tp_id IN viewer trading partner id,
1564 p_trading_partner_id IN supplier trading partner id
1565 p_bid_visibility IN bid visibility code
1566 p_sealed_auction_status IN sealed auction status
1567
1568 COMMENT : calculates value for response column in view invitation list page
1569 ======================================================================*/
1570
1571 FUNCTION RESPONSE_VIEWBIDDERSLIST (p_doctype_id IN NUMBER,
1572 p_auction_header_id IN NUMBER,
1573 p_auctioneer_id IN NUMBER,
1574 p_viewer_tp_id IN NUMBER,
1575 p_trading_partner_id IN NUMBER,
1576 p_bid_visibility IN VARCHAR2,
1577 p_sealed_auction_status IN VARCHAR2) RETURN VARCHAR2
1578 IS
1579
1580 v_is_auctioneer boolean := FALSE;
1581 v_is_auction_sealed boolean := FALSE;
1582 v_is_auction_blind boolean := FALSE;
1583 v_number_of_bids NUMBER;
1584
1585 BEGIN
1586
1587 IF (p_auctioneer_id = p_viewer_tp_id) THEN
1588 v_is_auctioneer := TRUE;
1589 ELSE
1590 v_is_auctioneer := FALSE;
1591 END IF;
1592
1593 IF (p_bid_visibility = 'SEALED_AUCTION') THEN
1594 v_is_auction_sealed := TRUE;
1595 ELSE
1596 v_is_auction_sealed := FALSE;
1597 END IF;
1598
1599 IF (p_bid_visibility = 'SEALED_BIDDING') THEN
1600 v_is_auction_blind := TRUE;
1601 ELSE
1602 v_is_auction_blind := FALSE;
1603 END IF;
1604
1605
1606 load_doctype_rules;
1607
1608 IF (g_offer_rules(p_doctype_id) = 'Y') THEN
1609 SELECT count(*)
1610 INTO v_number_of_bids
1611 FROM pon_bid_headers
1612 WHERE auction_header_id = p_auction_header_id and
1613 trading_partner_id = p_trading_partner_id and
1614 bid_status = 'ACTIVE' and
1615 nvl(award_status,'NONE') <> 'COMMITTED';
1616
1617 IF(v_number_of_bids > 0) THEN
1618 RETURN 'BidNumberString';
1619 ELSE
1620 RETURN 'NoBid';
1621 END IF;
1622 ELSE
1623 SELECT count(*)
1624 INTO v_number_of_bids
1625 FROM pon_bid_headers
1626 WHERE auction_header_id = p_auction_header_id and
1627 trading_partner_id = p_trading_partner_id and
1628 bid_status = 'ACTIVE';
1629 END IF;
1630
1631
1632 IF (((not v_is_auction_sealed) or
1633 (v_is_auction_sealed and (not v_is_auctioneer) and p_sealed_auction_status = 'ACTIVE') or
1634 (v_is_auction_sealed and v_is_auctioneer and (not p_sealed_auction_status = 'LOCKED')) ) ) THEN
1635
1636 IF(v_number_of_bids > 0) THEN
1637 RETURN 'BidNumberString';
1638 ELSE
1639 RETURN 'NoBid';
1640 END IF;
1641 ELSE -- can't see bid detail
1642 IF(v_number_of_bids > 0) THEN
1643 RETURN 'Sealed';
1644 ELSE
1645 IF(v_is_auctioneer) THEN
1646 RETURN 'NoBid';
1647 ELSE
1648 RETURN null;
1649 END IF;
1650 END IF;
1651 END IF;
1652
1653 END RESPONSE_VIEWBIDDERSLIST;
1654
1655 /*======================================================================
1656 FUNCTION : GET_ACTIVE_BID_COUNT PUBLIC
1657 PARAMETERS:
1658 p_auction_header_id IN auction id
1659 p_line_number IN line number
1660
1661 COMMENT : Returns the number of active bids for the given auction's line
1662 number.
1663 ======================================================================*/
1664 FUNCTION GET_ACTIVE_BID_COUNT (p_auction_header_id IN NUMBER,
1665 p_line_number IN NUMBER)
1666 RETURN NUMBER
1667 IS
1668 v_active_bids_count NUMBER := 0;
1669 BEGIN
1670 SELECT count(*)
1671 INTO v_active_bids_count
1672 FROM pon_bid_headers bh
1673 , pon_bid_item_prices bl
1674 WHERE bh.auction_header_id = p_auction_header_id
1675 and bh.bid_status = 'ACTIVE'
1676 --added by Allen Yang for Surrogate Bid 2008/09/26
1677 -------------------------------------------------------
1678 and (bh.submit_stage is null or bh.submit_stage <> 'TECHNICAL')
1679 -------------------------------------------------------
1680 and bl.bid_number = bh.bid_number
1681 and bl.line_number = p_line_number;
1682
1683 RETURN v_active_bids_count;
1684 EXCEPTION
1685 WHEN OTHERS THEN
1686 RETURN 0;
1687 END GET_ACTIVE_BID_COUNT;
1688
1689
1690 /*======================================================================
1691 PROCEDURE: GET_DATABASE_VERSION PUBLIC
1692 PARAMETERS:
1693 p_version OUT A string which represents the internal software version
1694 of the database (e.g., 7.1.0.0.0).
1695 p_compatibility OUT The compatibility setting of the database determined
1696 by the "compatible" init.ora parameter.
1697
1698 COMMENT : Returns the database version and compatibility setting
1699 ======================================================================*/
1700 procedure GET_DATABASE_VERSION (p_version OUT NOCOPY VARCHAR2,
1701 p_compatibility OUT NOCOPY VARCHAR2)
1702 IS
1703 BEGIN
1704
1705 DBMS_UTILITY.DB_VERSION(p_version, p_compatibility);
1706
1707 END GET_DATABASE_VERSION;
1708
1709
1710 /*======================================================================
1711 FUNCTION : TIME_REMAINING_ONLY_NOTZ PUBLIC
1712 PARAMETERS:
1713 p_startdate IN auction start date
1714 p_enddate IN auction end date
1715 p_client_timezone_id IN client (viewer) time zone id
1716 p_server_timezone_id IN server time zone id
1717 p_date_format IN date format
1718
1719
1720 COMMENT : returns the time remaining if the close date is more than
1721 31 days after today; else returns the actual close date
1722 ======================================================================*/
1723
1724 /*
1725 FUNCTION TIME_REMAINING_ONLY_NOTZ(p_startdate IN DATE ,
1726 p_enddate IN DATE ,
1727 p_client_timezone_id IN VARCHAR2,
1728 p_server_timezone_id IN VARCHAR2,
1729 p_date_format IN VARCHAR2) RETURN VARCHAR2
1730 IS
1731
1732 v_time_remaining VARCHAR2(100) := NULL;
1733 v_close_date VARCHAR2(100) := NULL;
1734 v_difference NUMBER := 0;
1735
1736 BEGIN
1737 v_difference := to_number(p_enddate-sysdate);
1738
1739 IF (v_difference <= 31) THEN
1740
1741 v_time_remaining := PON_AUCTION_PKG.TIME_REMAINING(p_startdate, p_enddate,
1742 p_client_timezone_id, p_server_timezone_id, p_date_format);
1743
1744 END IF;
1745 --
1746 -- Convert the dates to the user's timezone.
1747 --
1748 v_close_date := DISPLAY_DATE_TIME(p_enddate,
1749 p_client_timezone_id,
1750 p_server_timezone_id,
1751 p_date_format,
1752 'N');
1753
1754 IF (v_difference > 31) THEN
1755 return v_close_date;
1756 ELSE
1757 return v_time_remaining;
1758 END IF;
1759
1760 END TIME_REMAINING_ONLY_NOTZ;
1761 */
1762
1763 /*======================================================================
1764 PROCEDURE: CREATE_URL_ATTACHMENT PUBLIC
1765 PARAMETERS:
1766 COMMENT : This procedure is used to create url attachments
1767 during spreadsheet upload
1768 ======================================================================*/
1769
1770 PROCEDURE create_url_attachment(
1771 p_seq_num in NUMBER,
1772 p_category_name in VARCHAR2,
1773 p_document_description in VARCHAR2,
1774 p_datatype_id in NUMBER,
1775 p_url in VARCHAR2,
1776 p_entity_name in VARCHAR2,
1777 p_pk1_value in VARCHAR2,
1778 p_pk2_value in VARCHAR2,
1779 p_pk3_value in VARCHAR2,
1780 p_pk4_value in VARCHAR2,
1781 p_pk5_value in VARCHAR2
1782 ) IS
1783 l_rowid varchar2(30);
1784 l_attached_document_id number;
1785 l_document_id number;
1786 l_media_id number := NULL;
1787 l_lang varchar2(40);
1788 l_category_id NUMBER;
1789 BEGIN
1790 SELECT category_id, fnd_attached_documents_s.nextval
1791 INTO l_category_id, l_attached_document_id
1792 FROM fnd_document_categories
1793 WHERE name = p_category_name;
1794
1795 fnd_attached_documents_pkg.insert_row (
1796 x_rowid => l_rowid ,
1797 x_attached_document_id =>l_attached_document_id,
1798 x_document_id => l_document_id ,
1799 x_creation_date => SYSDATE ,
1800 x_created_by => fnd_global.user_id ,
1801 x_last_update_date => SYSDATE ,
1802 x_last_updated_by => fnd_global.user_id ,
1803 x_last_update_login => fnd_global.login_id ,
1804 x_seq_num => p_seq_num ,
1805 x_entity_name => p_entity_name ,
1806 x_column1 => NULL ,
1807 x_pk1_value => p_pk1_value ,
1808 x_pk2_value => p_pk2_value ,
1809 x_pk3_value => p_pk3_value ,
1810 x_pk4_value => p_pk4_value ,
1811 x_pk5_value => p_pk5_value ,
1812 x_automatically_added_flag => 'N' ,
1813 x_request_id => NULL ,
1814 x_program_application_id =>NULL ,
1815 x_program_id => NULL ,
1816 x_program_update_date => NULL ,
1817 x_attribute_category => NULL ,
1818 x_attribute1 => NULL ,
1819 x_attribute2 => NULL ,
1820 x_attribute3 => NULL ,
1821 x_attribute4 => NULL ,
1822 x_attribute5 => NULL ,
1823 x_attribute6 => NULL ,
1824 x_attribute7 => NULL ,
1825 x_attribute8 => NULL ,
1826 x_attribute9 => NULL ,
1827 x_attribute10 => NULL ,
1828 x_attribute11 => NULL ,
1829 x_attribute12 => NULL ,
1830 x_attribute13 => NULL ,
1831 x_attribute14 => NULL ,
1832 x_attribute15 => NULL ,
1833 x_datatype_id => p_datatype_id ,
1834 x_category_id => l_category_id ,
1835 x_security_type => 4 ,
1836 x_security_id => NULL ,
1837 x_publish_flag => 'Y' ,
1838 x_image_type => NULL ,
1839 x_storage_type => NULL ,
1840 x_usage_type => 'O' ,
1841 x_language => USERENV('LANG') ,
1842 x_description => p_document_description ,
1843 x_url => p_url ,
1844 x_media_id => l_media_id ,
1845 x_doc_attribute_category => NULL ,
1846 x_doc_attribute1 => NULL ,
1847 x_doc_attribute2 => NULL ,
1848 x_doc_attribute3 => NULL ,
1849 x_doc_attribute4 => NULL ,
1850 x_doc_attribute5 => NULL ,
1851 x_doc_attribute6 => NULL ,
1852 x_doc_attribute7 => NULL ,
1853 x_doc_attribute8 => NULL ,
1854 x_doc_attribute9 => NULL ,
1855 x_doc_attribute10 => NULL ,
1856 x_doc_attribute11 => NULL ,
1857 x_doc_attribute12 => NULL ,
1858 x_doc_attribute13 => NULL ,
1859 x_doc_attribute14 => NULL ,
1860 x_doc_attribute15 => NULL
1861 );
1862 END create_url_attachment;
1863
1864
1865 /*=========================================================================+
1866 --
1867 -- 12.0 ECO 4749273 - SEND TO LIST BEHAVIOR CHANGE IN ONLINE DISCUSSION
1868 --
1869 -- GET_TEAM_MEMBER_CNT takes AUCTION_HEADER_ID,DISCUSSION_ID,
1870 -- USER_ID and TRADING_PARTNER_CONTACT_ID as parameters and
1871 --
1872 -- Returns the number of team members for given negotiation.
1873 --
1874 --
1875 -- Parameter :
1876 -- p_auction_header_id IN NUMBER
1877 -- p_discussion_id IN NUMBER
1878 -- p_user_id IN NUMBER,
1879 -- p_trading_partner_contact_id IN NUMBER
1880 --
1881 +=========================================================================*/
1882
1883 FUNCTION GET_TEAM_MEMBER_CNT(p_auction_header_id IN NUMBER,
1884 p_discussion_id IN NUMBER,
1885 p_user_id IN NUMBER,
1886 p_trading_partner_contact_id IN NUMBER) return NUMBER
1887 AS
1888 l_member_cnt NUMBER := 0;
1889 BEGIN
1890 BEGIN
1891 SELECT COUNT(1)
1892 INTO l_member_cnt
1893 FROM pon_neg_team_members pntm
1894 WHERE pntm.auction_header_id = p_auction_header_id
1895 AND (pntm.user_id <> p_user_id
1896 OR EXISTS(SELECT 1
1897 FROM pon_thread_entries pte
1898 WHERE pte.discussion_id = p_discussion_id
1899 AND pte.from_id <> p_trading_partner_contact_id
1900 AND pte.vendor_id IS NULL
1901 )
1902 );
1903
1904 EXCEPTION
1905 WHEN OTHERS THEN
1906 l_member_cnt := 0;
1907 END;
1908
1909 RETURN l_member_cnt;
1910
1911 END GET_TEAM_MEMBER_CNT;
1912
1913 /*======================================================================
1914 * FUNCTION : APPROVAL_CONDITION PUBLIC
1915 * PARAMETERS:
1916 * p_user_id IN User Id of the Buyer
1917 *
1918 * COMMENT : Returns whether the Negotiation requires approval from
1919 * the manager of the buyer if present
1920 *======================================================================*/
1921
1922 FUNCTION APPROVAL_CONDITION(p_user_id IN NUMBER) RETURN VARCHAR2
1923 IS
1924 v_is_manager_present NUMBER := 0;
1925 v_approval_status VARCHAR2(50) := NULL;
1926 v_display_approval_status fnd_new_messages.message_text%TYPE := NULL;
1927 BEGIN
1928
1929 SELECT count(1)
1930 INTO v_is_manager_present
1931 FROM per_all_assignments_f ass,
1932 fnd_user sup,
1933 fnd_user emp,
1934 per_all_people_f per
1935 WHERE ass.person_id = emp.employee_id
1936 AND ass.supervisor_id = sup.employee_id
1937 AND ass.primary_flag = 'Y'
1938 AND ass.assignment_type = 'E'
1939 AND TRUNC(sysdate) BETWEEN ass.effective_start_date
1940 AND ass.effective_end_date
1941 AND sup.start_date <= sysdate
1942 AND nvl(sup.end_date, sysdate) >= sysdate
1943 AND per.person_id = sup.employee_id
1944 AND emp.user_id = p_user_id
1945 AND TRUNC(SYSDATE) BETWEEN per.effective_start_date AND per.effective_end_date
1946 AND rownum = 1;
1947
1948 IF v_is_manager_present > 0 THEN
1949 v_approval_status := 'PON_AUC_APPROVAL_REQUIRED';
1950 ELSE
1951 v_approval_status := 'PON_AUC_APPROVAL_NOT_REQUIRED';
1952 END IF;
1953
1954 SELECT message_text
1955 INTO v_display_approval_status
1956 FROM fnd_new_messages
1957 WHERE application_id = 396
1958 AND language_code = userenv('LANG')
1959 AND message_name = v_approval_status;
1960
1961 RETURN v_display_approval_status;
1962
1963 END APPROVAL_CONDITION;
1964
1965 END PON_OA_UTIL_PKG;
[Home] [Help]