1 PACKAGE PON_AUCTION_PKG as
2 /* $Header: PONAUCTS.pls 120.21 2007/09/04 12:01:40 adsahay ship $ */
3
4
5 PROCEDURE START_AUCTION(p_auction_header_id_encrypted VARCHAR2, -- 1
6 p_auction_header_id NUMBER, -- 2
7 p_trading_partner_contact_name VARCHAR2, -- 3
8 p_trading_partner_contact_id NUMBER, -- 4
9 p_trading_partner_name VARCHAR2, -- 5
10 p_trading_partner_id NUMBER, -- 6
11 p_open_bidding_date DATE, -- 7
12 p_close_bidding_date DATE, -- 8
13 p_award_by_date DATE, -- 9
14 p_reminder_date DATE, -- 10
15 p_bid_list_type VARCHAR2, -- 11
16 p_note_to_bidders VARCHAR2, -- 12
17 p_number_of_items NUMBER, -- 13
18 p_auction_title VARCHAR2, -- 14
19 p_event_id NUMBER); -- 15
20
21
22
23 PROCEDURE START_BID(p_bid_id NUMBER, -- 1
24 p_auction_header_id NUMBER, -- 2
25 p_bid_tp_contact_name VARCHAR2, -- 3
26 p_auction_tp_name VARCHAR2, -- 4
27 p_auction_open_bidding_date DATE, -- 5
28 p_auction_close_bidding_date DATE, -- 6
29 p_visibility_code VARCHAR2, -- 7
30 p_item_description VARCHAR2, -- 8
31 p_old_price NUMBER, -- 9
32 p_new_price NUMBER, -- 10
33 p_auction_title VARCHAR2, -- 11
34 p_oex_operation VARCHAR2, -- 12
35 p_oex_operation_url VARCHAR2); -- 13
36
37
38 PROCEDURE DISQUALIFY_BID(p_auction_header_id_encrypted VARCHAR2, -- 1
39 p_bid_id NUMBER, -- 2
40 p_auction_header_id NUMBER, -- 3
41 p_bid_tp_contact_name VARCHAR2, -- 4
42 p_auction_tp_name VARCHAR2, -- 5
43 p_auction_title VARCHAR2, -- 6
44 p_disqualify_date DATE, -- 7
45 p_disqualify_reason VARCHAR2 -- 8
46 );
47
48
49 PROCEDURE RETRACT_BID(p_bid_id NUMBER, -- 1
50 p_auction_header_id NUMBER, -- 2
51 p_bid_tp_contact_name VARCHAR2, -- 3
52 p_bid_tp_contact_id NUMBER, -- 4
53 p_auction_tp_contact_name VARCHAR2, -- 5
54 p_auction_tp_contact_id NUMBER, -- 6
55 p_auction_open_bidding_date DATE, -- 7
56 p_auction_close_bidding_date DATE, -- 8
57 p_oex_operation_url VARCHAR2); -- 9
58
59
60
61 PROCEDURE AWARD_BID(p_bid_id NUMBER, -- 1
62 p_auction_header_id NUMBER, -- 2
63 p_bid_tp_contact_name VARCHAR2, -- 3
64 p_auction_tp_name VARCHAR2, -- 4
65 p_auction_title VARCHAR2, -- 5
66 p_auction_header_id_encrypted VARCHAR2 -- 6
67 );
68
69
70
71 PROCEDURE UNREGISTERED_BIDDERS(itemtype in varchar2,
72 itemkey in varchar2,
73 actid in number,
74 uncmode in varchar2,
75 resultout out NOCOPY varchar2);
76
77 PROCEDURE REGISTERED_BIDDER(itemtype in varchar2,
78 itemkey in varchar2,
79 actid in number,
80 uncmode in varchar2,
81 resultout out NOCOPY varchar2);
82
83 PROCEDURE BIDDERS_LIST(itemtype in varchar2,
84 itemkey in varchar2,
85 actid in number,
86 uncmode in varchar2,
87 resultout out NOCOPY varchar2);
88
89
90 PROCEDURE CREATE_LOCAL_ROLES(itemtype IN VARCHAR2,
91 itemkey IN VARCHAR2,
92 actid IN NUMBER,
93 uncmode IN VARCHAR2,
94 resultout OUT NOCOPY VARCHAR2);
95
96 PROCEDURE POPULATE_ROLE_WITH_INVITEES (itemtype IN VARCHAR2,
97 itemkey IN VARCHAR2,
98 actid IN NUMBER,
99 uncmode IN VARCHAR2,
100 resultout OUT NOCOPY VARCHAR2);
101
102 PROCEDURE REACHED_AUCTION_START_DATE(itemtype IN VARCHAR2,
103 itemkey IN VARCHAR2,
104 actid IN NUMBER,
105 uncmode IN VARCHAR2,
106 resultout OUT NOCOPY VARCHAR2);
107
108 PROCEDURE REACHED_AUCTION_END_DATE(itemtype IN VARCHAR2,
109 itemkey IN VARCHAR2,
110 actid IN NUMBER,
111 uncmode IN VARCHAR2,
112 resultout OUT NOCOPY VARCHAR2);
113
114
115 PROCEDURE DOES_BIDDER_LIST_EXIT(itemtype IN VARCHAR2,
116 itemkey IN VARCHAR2,
117 actid IN NUMBER,
118 uncmode in varchar2,
119 resultout out NOCOPY varchar2);
120
121 PROCEDURE NON_BID_LIST_BIDDERS(itemtype in varchar2,
122 itemkey in varchar2,
123 actid in number,
124 uncmode in varchar2,
125 resultout out NOCOPY varchar2);
126
127
128 PROCEDURE NOTIFY_BIDDER_LIST_START(itemtype in varchar2,
129 itemkey in varchar2,
130 actid in number,
131 uncmode in varchar2,
132 resultout out NOCOPY varchar2);
133
134 PROCEDURE NOTIFY_BIDDER_LIST_CANCEL(itemtype in varchar2,
135 itemkey in varchar2,
136 actid in number,
137 uncmode in varchar2,
138 resultout out NOCOPY varchar2);
139
140 PROCEDURE NOTIFY_NON_BIDDER_LIST_CANCEL(itemtype in varchar2,
141 itemkey in varchar2,
142 actid in number,
143 uncmode in varchar2,
144 resultout out NOCOPY varchar2);
145
146 PROCEDURE NOTIFY_BIDDER_LIST_END(itemtype in varchar2,
147 itemkey in varchar2,
148 actid in number,
149 uncmode in varchar2,
150 resultout out NOCOPY varchar2);
151
152 PROCEDURE NOTIFY_NON_BIDDER_LIST_END(itemtype in varchar2,
153 itemkey in varchar2,
154 actid in number,
155 uncmode in varchar2,
156 resultout out NOCOPY varchar2);
157
158 PROCEDURE CHECK_AUCTION_BIDDER
159 (p_trading_partner_contact_id IN NUMBER,
160 p_auction_header_id IN NUMBER,
161 x_return_status OUT NOCOPY NUMBER);
162
163 PROCEDURE SEALED_BIDS(itemtype in varchar2,
164 itemkey in varchar2,
165 actid in number,
166 uncmode in varchar2,
167 resultout out NOCOPY varchar2);
168
169 PROCEDURE BIDDER_IN_LIST(itemtype in varchar2,
170 itemkey in varchar2,
171 actid in number,
172 uncmode in varchar2,
173 resultout out NOCOPY varchar2);
174
175 PROCEDURE CANCEL_AUCTION(p_auction_header_id IN NUMBER);
176
177 PROCEDURE COMPLETE_AUCTION(p_auction_header_id IN NUMBER);
178
179 -- FPK: CPA Function to check if negotiation has lines or not
180 FUNCTION NEG_HAS_LINES (p_auction_number IN NUMBER) RETURN VARCHAR2;
181
182 FUNCTION Get_Oex_Time_Zone return varchar2;
183 FUNCTION Get_Time_Zone(contact_id number) return varchar2;
184 FUNCTION Get_Time_Zone(contact_name varchar2) return varchar2;
185 FUNCTION Get_TimeZone_Description(p_timezone_id varchar2, lang varchar2) return varchar2;
186
187 FUNCTION GET_CLOSE_BIDDING_DATE(p_auction_header_id IN NUMBER) RETURN DATE;
188
189 FUNCTION TIME_REMAINING_ORDER(p_auction_header_id IN NUMBER) RETURN NUMBER;
190
191 FUNCTION TIME_REMAINING_ORDER( p_auction_status IN VARCHAR2,
192 p_creation_date IN DATE,
193 p_close_bidding_date IN DATE,
194 p_is_paused IN VARCHAR2,
195 p_last_pause_date IN DATE,
196 p_auction_header_id_orig_round IN NUMBER,
197 p_auction_round_number IN NUMBER,
198 p_amendment_number IN NUMBER) RETURN NUMBER;
199
200 FUNCTION TIME_REMAINING(p_auction_header_id IN NUMBER) RETURN VARCHAR2;
201
202 FUNCTION TIME_REMAINING(p_auction_header_id IN NUMBER, p_line_number IN NUMBER) RETURN VARCHAR2;
203
204 FUNCTION TIME_REMAINING( p_auction_status IN VARCHAR2,
205 p_open_bidding_date IN DATE,
206 p_close_bidding_date IN DATE,
207 p_is_paused IN VARCHAR2,
208 p_last_pause_date IN DATE,
209 p_staggered_closing_interval IN NUMBER ) RETURN VARCHAR2;
210
211 PROCEDURE AUCTION_OPEN(itemtype IN VARCHAR2,
212 itemkey IN VARCHAR2,
213 actid IN NUMBER,
214 uncmode IN VARCHAR2,
215 resultout OUT NOCOPY VARCHAR2);
216
217 PROCEDURE AUCTION_CLOSED(itemtype IN VARCHAR2,
218 itemkey IN VARCHAR2,
219 actid IN NUMBER,
220 uncmode IN VARCHAR2,
221 resultout OUT NOCOPY VARCHAR2);
222
223 Function getLookupMeaning(lookupType in varchar2,
224 langCode in varchar2,
225 lookupCode in varchar2) return varchar2;
226
227 Function GetPOTotal(p_po_id IN number) return Number;
228
229 Function getNeedByDatesToPrint(auctionID IN number,lineNumber IN number,userDateFormat IN varchar2) return varchar2;
230
231 PROCEDURE AUCTION_PO_SEND (
232 transaction_code IN VARCHAR2,
233 document_id IN NUMBER,
234 party_id IN NUMBER DEFAULT NULL,
235 debug_mode IN PLS_INTEGER DEFAULT 0,
236 trigger_id OUT NOCOPY PLS_INTEGER,
237 retcode OUT NOCOPY PLS_INTEGER,
238 errmsg OUT NOCOPY VARCHAR2
239 );
240
241 PROCEDURE SET_NEW_ITEM_KEY( itemtype in varchar2,
242 itemkey in varchar2,
243 actid in number,
244 funcmode in varchar2,
245 resultout out NOCOPY varchar2 );
246
247
248 PROCEDURE EVENT_AUCTION(itemtype in varchar2,
249 itemkey in varchar2,
250 actid in number,
251 uncmode in varchar2,
252 resultout out NOCOPY varchar2);
253
254 PROCEDURE EVENT_AUCTION_ID(itemtype in varchar2,
255 itemkey in varchar2,
256 actid in number,
257 uncmode in varchar2,
258 resultout out NOCOPY varchar2);
259
260 PROCEDURE EMPTY_CANCEL_REASON(itemtype in varchar2,
261 itemkey in varchar2,
262 actid in number,
263 uncmode in varchar2,
264 resultout out NOCOPY varchar2);
265
266 PROCEDURE EMPTY_DISQUALIFY_REASON(itemtype in varchar2,
267 itemkey in varchar2,
268 actid in number,
269 uncmode in varchar2,
270 resultout out NOCOPY varchar2);
271
272 PROCEDURE EMPTY_CLOSECHANGED_REASON(itemtype in varchar2,
273 itemkey in varchar2,
274 actid in number,
275 uncmode in varchar2,
276 resultout out NOCOPY varchar2);
277
278 FUNCTION getEventTitle (p_auction_number IN NUMBER) RETURN VARCHAR2;
279
280 PROCEDURE NEW_ROUND_BIDDERS_NOT_INVITED( p_itemtype in varchar2,
281 p_itemkey in varchar2,
282 actid in number,
283 uncmode in varchar2,
284 resultout out NOCOPY varchar2);
285
286
287 PROCEDURE CLOSEEARLY_AUCTION (p_auction_header_id IN NUMBER,
288 p_new_close_date IN DATE,
289 p_closeearly_reason IN VARCHAR2);
290
291 PROCEDURE CLOSECHANGED_AUCTION (p_auction_header_id IN NUMBER,
292 p_change_type IN NUMBER,
293 p_new_close_date IN DATE,
294 p_closechanged_reason IN VARCHAR2);
295
296 PROCEDURE NOTIFY_OTHER_BIDDERS_OF_DISQ(itemtype in varchar2,
297 itemkey in varchar2,
298 actid in number,
299 uncmode in varchar2,
300 resultout out NOCOPY varchar2);
301
302 PROCEDURE NOTIFY_BIDDERS_AUC_CHANGED(itemtype in varchar2,
303 itemkey in varchar2,
304 actid in number,
305 uncmode in varchar2,
306 action_code in varchar2);
307
308 PROCEDURE NOTIFY_BIDDERS_OF_CANCEL (itemtype in varchar2,
309 itemkey in varchar2,
310 actid in number,
311 uncmode in varchar2,
312 resultout out NOCOPY varchar2);
313
314 PROCEDURE NOTIFY_BIDDERS_OF_CLOSEEARLY (itemtype in varchar2,
315 itemkey in varchar2,
316 actid in number,
317 uncmode in varchar2,
318 resultout out NOCOPY varchar2);
319
320 PROCEDURE NOTIFY_BIDDERS_OF_CLOSECHANGED (itemtype in varchar2,
321 itemkey in varchar2,
322 actid in number,
323 uncmode in varchar2,
324 resultout out nocopy varchar2);
325
326 PROCEDURE COMPLETE_PREV_ROUND_WF(p_itemtype in varchar2,
327 p_itemkey in varchar2,
328 actid in number,
329 uncmode in varchar2,
330 resultout out NOCOPY varchar2);
331
332 PROCEDURE COMPLETE_PREV_DOC_WF(p_itemtype in varchar2,
333 p_itemkey in varchar2,
334 actid in number,
335 uncmode in varchar2,
336 resultout out NOCOPY varchar2);
337
338 PROCEDURE COMPLETE_PREV_SUPPL_NOTIFS(p_prev_doc_header_id IN NUMBER);
339
340 PROCEDURE POPULATE_ROLE_WITH_SUPPLIERS (itemtype IN VARCHAR2,
341 itemkey IN VARCHAR2,
342 actid IN NUMBER,
343 uncmode IN VARCHAR2,
344 resultout OUT NOCOPY VARCHAR2);
345
346 FUNCTION getMessage (msg VARCHAR2) RETURN VARCHAR2;
347
348 FUNCTION getMessage (msg VARCHAR2, msg_suffix VARCHAR2) RETURN VARCHAR2;
349
350 -- FUNCTION getMessage (msg VARCHAR2, token VARCHAR2, token_value VARCHAR2) RETURN VARCHAR2;
351
352 FUNCTION getMessage (msg VARCHAR2, msg_suffix VARCHAR2, token VARCHAR2, token_value VARCHAR2) RETURN VARCHAR2;
353
354 FUNCTION getMessage (msg VARCHAR2, msg_suffix VARCHAR2, token1 VARCHAR2, token1_value VARCHAR2,
355 token2 VARCHAR2, token2_value VARCHAR2) RETURN VARCHAR2;
356
357 FUNCTION getMessage (msg VARCHAR2, msg_suffix VARCHAR2, token1 VARCHAR2, token1_value VARCHAR2,
358 token2 VARCHAR2, token2_value VARCHAR2,
359 token3 VARCHAR2, token3_value VARCHAR2) RETURN VARCHAR2;
360
361 FUNCTION getMessage (msg VARCHAR2, msg_suffix VARCHAR2, token1 VARCHAR2, token1_value VARCHAR2,
362 token2 VARCHAR2, token2_value VARCHAR2, token3 VARCHAR2,
363 token3_value VARCHAR2, token4 VARCHAR2, token4_value VARCHAR2) RETURN VARCHAR2;
364
365 FUNCTION getMessage (msg VARCHAR2, msg_suffix VARCHAR2, token1 VARCHAR2, token1_value VARCHAR2,
366 token2 VARCHAR2, token2_value VARCHAR2, token3 VARCHAR2,
367 token3_value VARCHAR2, token4 VARCHAR2, token4_value VARCHAR2,
368 token5 VARCHAR2, token5_value VARCHAR2) RETURN VARCHAR2;
369
370 FUNCTION GET_MESSAGE_SUFFIX (x_doctype_group_name VARCHAR2) RETURN VARCHAR2;
371
372 FUNCTION EMPTY_REASON (p_reason IN VARCHAR2) RETURN VARCHAR2;
373
374 PROCEDURE NOTIFY_NEW_INVITEES (p_auction_id NUMBER); -- 1
375
376 PROCEDURE NOTIFY_ADDED_INVITEES( x_itemtype in varchar2,
377 x_itemkey in varchar2,
378 actid in number,
379 uncmode in varchar2,
383 PROCEDURE NOTIFY_BIDDER_LIST_REMINDER(itemtype in varchar2,
380 resultout out NOCOPY varchar2);
381
382
384 itemkey in varchar2,
385 actid in number,
386 uncmode in varchar2,
387 resultout out nocopy varchar2);
388
389 PROCEDURE CLOSEDATE_EARLIER_REMINDERDATE( itemtype in varchar2,
390 itemkey in varchar2,
391 actid in number,
392 uncmode in varchar2,
393 resultout out nocopy varchar2);
394
395 PROCEDURE UPDATE_ACK_TO_YES( itemtype in varchar2,
396 itemkey in varchar2,
397 actid in number,
398 uncmode in varchar2,
399 resultout out nocopy varchar2);
400
401 PROCEDURE UPDATE_ACK_TO_NO( itemtype in varchar2,
402 itemkey in varchar2,
403 actid in number,
404 uncmode in varchar2,
405 resultout out nocopy varchar2);
406
407 PROCEDURE launch_init_notif_proc(itemtype IN VARCHAR2,
408 itemkey IN VARCHAR2,
409 actid IN NUMBER,
410 uncmode IN VARCHAR2,
411 resultout OUT NOCOPY VARCHAR2);
412
413 PROCEDURE launch_init_notif_p_add(itemtype IN VARCHAR2,
414 itemkey IN VARCHAR2,
415 actid IN NUMBER,
416 uncmode IN VARCHAR2,
417 resultout OUT NOCOPY VARCHAR2);
418
419 PROCEDURE launch_added_notif_proc(itemtype IN VARCHAR2,
420 itemkey IN VARCHAR2,
421 actid IN NUMBER,
422 uncmode IN VARCHAR2,
423 resultout OUT NOCOPY VARCHAR2);
424
425 PROCEDURE launch_new_round_notif(itemtype IN VARCHAR2,
426 itemkey IN VARCHAR2,
427 actid IN NUMBER,
428 uncmode IN VARCHAR2,
429 resultout OUT NOCOPY VARCHAR2);
430
431 PROCEDURE launch_new_round_notif_add(itemtype IN VARCHAR2,
432 itemkey IN VARCHAR2,
433 actid IN NUMBER,
434 uncmode IN VARCHAR2,
435 resultout OUT NOCOPY VARCHAR2);
436
437 PROCEDURE SET_INVITATION_LIST_FLAG(p_auction_header_id NUMBER);
438
439 procedure retrieve_user_info(param1 varchar2);
440 function getPhoneNumber(p_user_name varchar2) return varchar2;
441 function getFaxNumber(p_user_name varchar2) return varchar2;
442 function getEMail(p_user_name varchar2) return varchar2;
443
444 function replaceHtmlChars(html_in varchar2) return varchar2;
445
446 /*======================================================================
447 PROCEDURE : DELETE_NEGOTIATION_LINE_REF PUBLIC
448 PARAMETERS:
449 x_negotiation_id in auction header id
450 x_negotiation_line_num in negotiation line number
451 x_org_id in organization id
452 x_error_code out internal code for error
453
454 COMMENT : delete negotiation line references
455 ======================================================================*/
456 PROCEDURE DELETE_NEGOTIATION_LINE_REF(x_negotiation_id in number,
457 x_negotiation_line_num in number,
458 x_org_id in number,
459 x_error_code out NOCOPY varchar2);
460 /*======================================================================
461 PROCEDURE : DELETE_NEGOTIATION_REF PUBLIC
462 PARAMETERS:
463 x_negotiation_id in auction header id
464 x_error_code out internal code for error
465
466 COMMENT : delete negotiation references
467 ======================================================================*/
468 PROCEDURE DELETE_NEGOTIATION_REF(x_negotiation_id in number,
469 x_error_code out NOCOPY varchar2);
470 /*======================================================================
471 PROCEDURE : CANCEL_NEGOTIATION_REF PUBLIC
472 PARAMETERS:
473 x_negotiation_id in auction header id
474 x_error_code out internal code for error
475
476 COMMENT : cancel negotiation references
477 ======================================================================*/
478 PROCEDURE CANCEL_NEGOTIATION_REF(x_negotiation_id in number,
479 x_error_code out NOCOPY varchar2);
480 /*======================================================================
481 PROCEDURE : UPDATE_NEGOTIATION_REF PUBLIC
482 PARAMETERS:
483 x_old_negotiation_id in old auction header id
484 x_old_negotiation_num in old auction display number
485 x_new_negotiation_id in new auction header id
486 x_new_negotiation_num in new auction display number
487 x_error_code out internal code for error
488 x_error_message out error message
489
490 COMMENT : update negotiation references
491 ======================================================================*/
492 PROCEDURE UPDATE_NEGOTIATION_REF(
493 x_old_negotiation_id in number,
494 x_old_negotiation_num in varchar2,
495 x_new_negotiation_id in number,
496 x_new_negotiation_num in varchar2,
497 x_error_code out NOCOPY varchar2,
498 x_error_message out NOCOPY varchar2);
499 /*======================================================================
500 PROCEDURE : COPY_BACKING_REQ PUBLIC
501 PARAMETERS:
502 x_old_negotiation_id in old auction header id
503 x_new_negotiation_id in new auction header id
504 x_error_code out internal code for error
505
506 COMMENT : update negotiation references
507 ======================================================================*/
508 /*======================================================================
509 PROCEDURE : CANCEL_NEGOTIATION_REF_BY_LINE PUBLIC
510 PARAMETERS:
511 x_negotiation_id in auction header id
512 x_negotiation_line_id in line number
513 x_error_code out internal code for error
514
515 COMMENT : cancel negotiation references
516 ======================================================================*/
517 PROCEDURE CANCEL_NEGOTIATION_REF_BY_LINE(x_negotiation_id in number,
518 x_negotiation_line_id in number,
519 x_error_code out NOCOPY varchar2);
520
521 PROCEDURE COPY_BACKING_REQ(x_old_negotiation_id in number,
522 x_new_negotiation_id in number,
523 x_error_code out NOCOPY varchar2);
524
525 PROCEDURE Check_Unique_Wrapper(X_Segment1 In VARCHAR2,
526 X_rowid IN VARCHAR2,
527 X_Type_lookup_code IN VARCHAR2,
528 X_Unique OUT NOCOPY VARCHAR2);
529
530 FUNCTION CHECK_UNIQUE_ORDER_NUMBER (p_auction_id IN NUMBER,
531 p_order_number IN VARCHAR2)
532 RETURN VARCHAR2;
533
534
535 PROCEDURE ACK_NOTIF_RESPONSE(p_wf_item_key VARCHAR2,
536 p_user_name VARCHAR2,
537 p_supp_ack VARCHAR2,
538 p_ack_note VARCHAR2);
539
540 PROCEDURE ACK_NOTIF_RESPONSE(p_wf_item_key VARCHAR2,
541 p_user_name VARCHAR2,
542 p_supp_ack VARCHAR2,
543 p_ack_note VARCHAR2,
544 x_return_status OUT NOCOPY NUMBER);
545
546 PROCEDURE GET_TIME_REMAINING(p_auction_header_id IN NUMBER, p_time_remaining OUT NOCOPY FLOAT);
547
548 function get_product_install_status ( x_product_name in varchar2) RETURN VARCHAR2;
549
550 SessionLanguage VARCHAR2(255);
551
552 PROCEDURE SET_SESSION_LANGUAGE(p_language VARCHAR2, p_language_code VARCHAR2);
553
554 PROCEDURE UNSET_SESSION_LANGUAGE;
555
556 FUNCTION GET_TRANSACTION_TYPE (p_doctype_group_name PON_AUC_DOCTYPES.INTERNAL_NAME%TYPE)
557 RETURN PON_AUC_DOCTYPES.TRANSACTION_TYPE%TYPE;
558
559 procedure getTriangulationRate(toCurrency varchar2,
560 fromCurrency varchar2,
561 rateDate date,
562 rateType varchar2,
563 rollDays number,
564 rate out nocopy number);
565
566 function getClosestRate(fromCurrency varchar2,toCurrency varchar2, conversionDate date, conversionType varchar2, maxRollDays number) return varchar2;
567
568 PROCEDURE DELETE_NEGOTIATION_AMENDMENTS (
569 x_negotiation_id in number,
570 x_error_code out NOCOPY varchar2);
571
572
573 FUNCTION GET_MOST_RECENT_AMENDMENT(p_auction_header_id IN NUMBER) RETURN NUMBER;
574
575 FUNCTION GET_MEMBER_TYPE(p_auction_header_id IN NUMBER,p_user_id IN NUMBER) RETURN VARCHAR2;
576
577 PROCEDURE get_default_hdr_pb_settings (p_doctype_id IN NUMBER,
578 p_tp_id IN NUMBER,
579 x_price_break_response OUT NOCOPY VARCHAR2);
580
581 PROCEDURE get_default_pb_settings (p_auction_header_id IN NUMBER,
582 x_price_break_type OUT NOCOPY VARCHAR2,
583 x_price_break_neg_flag OUT NOCOPY VARCHAR2);
584
585 FUNCTION getPAOUInstalled (p_orgId IN NUMBER) RETURN VARCHAR2;
586
587 FUNCTION getGMSOUInstalled ( p_orgId IN NUMBER) RETURN VARCHAR2;
588
589 PROCEDURE IS_NEGOTIATION_REQ_BACKED(
590 p_auction_header_id IN NUMBER,
591 x_req_backed OUT NOCOPY VARCHAR2) ;
592
593 /*=======================================================================+
594 -- 12.0 Enhancement
595 -- SEND_TASK_ASSIGN_NOTIF procedure will be responsible to send
596 -- Notification to the given Collaboration Team Member
597 -- as requested by Negotiation Creator.
598 -- Parameter :
599 --
600 -- p_auction_header_id IN NUMBER,
601 -- p_user_id IN NUMBER,
602 -- x_return_status OUT NOCOPY VARCHAR2
606 p_user_id IN NUMBER,
603 +=========================================================================*/
604
605 PROCEDURE SEND_TASK_ASSIGN_NOTIF (p_auction_header_id IN NUMBER,
607 x_return_status OUT NOCOPY VARCHAR2);
608
609 /*=======================================================================+
610 -- 12.0 Enhancement
611 -- SEND_RESP_NOTIF procedure will be responsible for
612 -- sending notification to the Buyer when a Seller
613 -- submits a Response.
614 -- Parameter :
615 -- p_bid_number IN NUMBER,
616 -- x_return_status OUT NOCOPY VARCHAR2
617 --
618 +=========================================================================*/
619
620 PROCEDURE SEND_RESP_NOTIF ( p_bid_number IN NUMBER,
621 x_return_status OUT NOCOPY VARCHAR2);
622
623
624 /*=========================================================================+
625 -- 12.0 Enhancement
626 -- SEND_MSG_SENT_NOTIF procedure will be responsible for
627 -- sending notification to the Buyer when a Seller sends
628 -- a message to Buyer or a Buyer sends an internal message
629 -- to other Collaboration Team Members
630 -- Parameter :
631 -- p_toFirstName IN VARCHAR2
632 -- p_toLastName IN VARCHAR2
633 -- p_toCompanyName IN VARCHAR2
634 -- p_toCompanyId IN NUMBER
635 -- p_fromFirstName IN VARCHAR2
636 -- p_fromLastName IN VARCHAR2
637 -- p_fromCompanyName IN VARCHAR2
638 -- p_fromCompanyId IN NUMBER
639 -- p_creatorCompanyId IN NUMBER
640 -- p_userPartyId IN NUMBER
641 -- p_entryid IN NUMBER
642 -- p_message_type IN VARCHAR2
643 -- x_return_status OUT NOCOPY VARCHAR2
644 --
645 +=========================================================================*/
646
647 PROCEDURE SEND_MSG_SENT_NOTIF(
648 p_toFirstName IN VARCHAR2,
649 p_toLastName IN VARCHAR2,
650 p_toCompanyName IN VARCHAR2,
651 p_toCompanyId IN NUMBER,
652 p_fromFirstName IN VARCHAR2,
653 p_fromLastName IN VARCHAR2,
654 p_fromCompanyName IN VARCHAR2,
655 p_fromCompanyId IN NUMBER,
656 p_creatorCompanyId IN NUMBER,
657 p_userPartyId IN NUMBER,
658 p_entryid IN NUMBER,
659 p_message_type IN VARCHAR2,
660 x_return_status OUT NOCOPY VARCHAR2
661 );
662
663 /*=========================================================================+
664 -- 12.0 Enhancement
665 -- SEND_TASK_COMPL_NOTIF procedure will be responsible
666 -- for sending notification from the Buyer user to the
667 -- Negotiation Creator when the former completes a given
668 -- task for a Collaboration Team Member.
669 -- Parameter :
670 -- p_auction_header_id IN NUMBER,
671 -- p_user_id IN NUMBER,
672 -- x_return_status OUT NOCOPY VARCHAR2)
673 +=========================================================================*/
674
675 PROCEDURE SEND_TASK_COMPL_NOTIF ( p_auction_header_id IN NUMBER,
676 p_user_id IN NUMBER,
677 x_return_status OUT NOCOPY VARCHAR2);
678
679 /*=========================================================================+
680 --
681 -- 12.0 Enhancement
682 -- IS_NOTIF_SUBSCRIBED is a wrapper over the GET_NOTIF_PREFERENCE
683 -- of PON_WF_UTL_PKG. It will call the procedure GET_NOTIF_PREFERENCE with
684 -- appropriate message type and auction header id.
685 --
686 -- Parameter :
687 -- itemtype IN VARCHAR2
688 -- itemkey IN VARCHAR2
689 -- actid IN NUMBER
690 -- funcmode IN VARCHAR2
691 -- resultout OUT NOCOPY VARCHAR2
692 --
693 +=========================================================================*/
694
695
696 PROCEDURE IS_NOTIF_SUBSCRIBED(itemtype in varchar2,
697 itemkey in varchar2,
698 actid in number,
699 funcmode in varchar2,
700 resultout out NOCOPY varchar2);
701
702 /*=========================================================================+
703 --
704 -- 12.0 Enhancement
705 -- GET_MAPPED_IP_CATEGORY takes in a po category id as a parameter and
706 -- returns an ip category if mapping exists else returns -2
707 --
708 --
709 -- Parameter :
710 -- p_po_category_id IN NUMBER
711 --
712 +=========================================================================*/
713
714
715 FUNCTION GET_MAPPED_IP_CATEGORY(p_po_category_id IN NUMBER) return NUMBER;
716
717 /*=========================================================================+
718 --
719 -- 12.0 Enhancement
720 -- GET_MAPPED_PO_CATEGORY takes in an ip category id as a parameter and
721 -- returns a po category if mapping exists else returns -2
722 --
723 --
724 -- Parameter :
725 -- p_ip_category_id IN NUMBER
726 --
727 +=========================================================================*/
728
729
730 FUNCTION GET_MAPPED_PO_CATEGORY(p_ip_category_id IN NUMBER) return NUMBER;
731
732
733
734 PROCEDURE GET_NEGOTIATION_DETAILS( p_auction_header_id NUMBER,
735 p_user_trading_partner_id NUMBER,
736 x_time_left OUT NOCOPY VARCHAR2,
737 x_buyer_display OUT NOCOPY VARCHAR2,
738 x_carrier OUT NOCOPY VARCHAR2,
739 x_unlocked_by_display OUT NOCOPY VARCHAR2,
740 x_unsealed_by_display OUT NOCOPY VARCHAR2,
741 x_has_active_company_bid OUT NOCOPY VARCHAR2,
742 x_is_multi_site OUT NOCOPY VARCHAR2,
743 x_all_site_bid_on OUT NOCOPY VARCHAR2,
744 x_is_paused OUT NOCOPY VARCHAR2,
745 x_outcome_display OUT NOCOPY VARCHAR2,
746 x_advances_flag OUT NOCOPY VARCHAR2,
747 x_retainage_flag OUT NOCOPY VARCHAR2,
748 x_payment_rate_rype_enabled OUT NOCOPY VARCHAR2
749 );
750
751 ---------------------------------------------------------------------------------------
752 -- R12 Rollup1 Enhancement - Countdown Clock Project (adsahay)
753 --
754 -- Start of comments
755 -- API Name: SHOW_COUNTDOWN
756 -- Function: Given an auction id, returns "Y" if the auction is active or paused and
757 -- closing within next 24 hours. Auctions that are in preview mode,
758 -- cancelled or amended, or closing in more than 24 hours return "N".
759 -- Parameters:
760 -- IN: p_auction_header_id IN NUMBER - Auction header id
761 -- OUT: x_return_status OUT NOCOPY VARCHAR2 - Return status
762 -- x_error_code OUT NOCOPY VARCHAR2 - Error code
763 -- x_error_message OUT NOCOPY VARCHAR2 - Error message
764 --
765 -- End of Comments
766 -- Return : l_show_countdown VARCHAR2
767 ----------------------------------------------------------------------------------------
768
769 FUNCTION SHOW_COUNTDOWN(x_result OUT NOCOPY VARCHAR2,
770 x_error_code OUT NOCOPY VARCHAR2,
771 x_error_message OUT NOCOPY VARCHAR2,
772 p_auction_header_id in NUMBER) return VARCHAR2;
773
774 -----------------------------------------------------------------------------------
775 -- R12 Rollup1 Enhancement - Countdown Clock Project (adsahay)
776 --
777 -- Start of comments
778 -- API Name: HAS_DISTINCT_CLOSING_LINES
779 -- Function: Given an auction id, Returns 'Y' if the auction has lines
780 -- closing in different times, else 'N'. This means that either the auction is
781 -- staggered or has "auto extend" feature enabled such that it extends one line
782 -- instead of all lines.
783 --
784 -- Parameters:
785 -- IN: p_auction_header_id IN NUMBER - The auction header id
786 -- OUT: x_return_status OUT NOCOPY VARCHAR2 - Return status
787 -- x_error_code OUT NOCOPY VARCHAR2 - Error code
788 -- x_error_message OUT NOCOPY VARCHAR2 - Error message
789 --
790 -- End of Comments
791 --
792 -- Return : l_flag VARCHAR2
793 ------------------------------------------------------------------------------------
794
795 FUNCTION HAS_DISTINCT_CLOSING_DATES(x_result OUT NOCOPY VARCHAR2,
796 x_error_code OUT NOCOPY VARCHAR2,
797 x_error_message OUT NOCOPY VARCHAR2,
798 p_auction_header_id in NUMBER) return VARCHAR2;
799
800 -------------------------------------------------------------------------------------
801 -- R12 Rollup1 Enhancement - Two Part RFQ project (adsahay)
802 --
803 -- Two global variables two allow caching of the meanings of TECHNICAL
804 -- and COMMERCIAL, along with their getters.
805 -- Three global variables to store supplier attachment category names.
806 -------------------------------------------------------------------------------------
807
808 g_technical_attachment fnd_document_categories.name%TYPE := 'FromSupplierTechnical';
809 g_commercial_attachment fnd_document_categories.name%TYPE := 'FromSupplierCommercial';
810 g_supplier_attachment fnd_document_categories.name%TYPE := 'FromSupplier';
811
812 -- bug 6374353
813 -- create temporary table to store language and meanings of TECHNICAL and COMMERCIAL.
814 type two_part_cache_rec is record(
815 language fnd_lookup_values.language%TYPE,
816 technical_meaning fnd_lookups.meaning%TYPE,
817 commercial_meaning fnd_lookups.meaning%TYPE
818 );
819 type g_tp_cache_type is table of two_part_cache_rec index by BINARY_INTEGER;
820 g_two_part_cache g_tp_cache_type;
821
822 -----------------------------------------------------------------------------------
823 -- R12 Rollup1 Enhancement - Two Part RFQ Project (adsahay)
824 --
825 -- Start of comments
826 -- API Name: GET_TECHNICAL_MEANING
827 -- Function: Returns meaning of 'TECHNICAL' from lookups.
828 --
829 -- Parameters:
830 -- IN:
831 -- OUT:
832 --
833 -- End of Comments
834 --
838
835 -- Return : g_technical_meaning VARCHAR2
836 ------------------------------------------------------------------------------------
837 FUNCTION get_technical_meaning RETURN VARCHAR2;
839
840 -----------------------------------------------------------------------------------
841 -- R12 Rollup1 Enhancement - Two Part RFQ Project (adsahay)
842 --
843 -- Start of comments
844 -- API Name: GET_COMMERCIAL_MEANING
845 -- Function: Returns meaning of 'COMMERCIAL' from lookups.
846 --
847 -- Parameters:
848 -- IN:
849 -- OUT:
850 --
851 -- End of Comments
852 --
853 -- Return : g_commercial_meaning VARCHAR2
854 ------------------------------------------------------------------------------------
855 FUNCTION get_commercial_meaning RETURN VARCHAR2;
856
857 -----------------------------------------------------------------------------------
858 -- R12 Rollup1 Enhancement - Two Part RFQ Project (adsahay)
859 --
860 -- Start of comments
861 -- API Name: NOTIFY_BIDDERS_TECH_COMPLETE
862 -- Procedure: Notify bidders that their bids have/have not been short listed
863 -- in technical evaluation.
864 --
865 -- Parameters:
866 -- IN: p_auction_header_id IN NUMBER - The auction header id.
867 -- OUT: x_return_status OUT NOCOPY VARCHAR2 - Return status
868 -- x_error_code OUT NOCOPY VARCHAR2 - Error code
869 -- x_error_message OUT NOCOPY VARCHAR2 - Error message
870 --
871 -- End of Comments
872 ------------------------------------------------------------------------------------
873 PROCEDURE notify_bidders_tech_complete(x_return_status OUT NOCOPY VARCHAR2,
874 x_error_code OUT NOCOPY VARCHAR2 ,
875 x_error_message OUT NOCOPY VARCHAR2,
876 p_auction_header_id IN NUMBER);
877
878 /*======================================================================
879 FUNCTION : GET_AUCTION_STATUS_DISPLAY PUBLIC
880 PARAMETERS:
881 p_auction_header_id in auction header id
882 p_user_trading_partner_id in trading partner id of the user
883 currently running the application
884 COMMENT : Returns the negotiation status that has to be displayed to the user.
885 This function will be used in the select lists of various VOs that need
886 to query auction status
887 ======================================================================*/
888 FUNCTION GET_AUCTION_STATUS_DISPLAY(
889 p_auction_header_id IN pon_auction_headers_all.AUCTION_HEADER_ID%TYPE,
890 p_user_trading_partner_id IN pon_auction_headers_all.TRADING_PARTNER_ID%TYPE) RETURN VARCHAR2;
891
892 /*======================================================================
893 FUNCTION : GET_MONITOR_IMAGE_AND_STATUS PUBLIC
894 PARAMETERS:
895
896 p_auction_header_id IN header id of teh auction
897 p_doctype_id IN the document type id of the negotiation
898 p_bid_visibility IN bid visibility
899 p_sealed_auction_status IN sealed status of the negotiation
900 p_auctioneer_id IN id of the negotiation creator
901 p_viewer_id IN id of the person view the negotiation
902 p_has_items IN flag to indicate the existence of lines
903 p_doc_type IN document type of the negotiation
904 p_auction_status IN the auction status
905 p_view_by_date IN
906 p_open_bidding_date IN The date on which bidding starts
907 p_has_scoring_teams_flag IN flag to indicate existence of scoring teams
908 p_user_trading_partner_id IN the trading partner id of the user
909 x_buyer_monitor_image OUT the image that should be used for monitor auction icons
910 x_auction_status_display OUT the auction status for display
911
912 COMMENT : This procedure will be used in the getters of the Monitor Image attributs
913 of various VOs. The procedures calls the existing PON_OA_UTIL_PKG.BUYER_MONITOR_IMAGE
914 and GET_AUCTION_STATUS_DISPLAY at one go and returns the image and status together to the middle
915 tier. this is done to improve the efficiency of the code and avoid multiple jdbc calls,
916 one for the image and the other for the auction status.
917 ======================================================================*/
918 PROCEDURE GET_MONITOR_IMAGE_AND_STATUS(
919 p_auction_header_id IN NUMBER,
920 p_doctype_id IN NUMBER,
921 p_bid_visibility IN VARCHAR2,
922 p_sealed_auction_status IN VARCHAR2,
923 p_auctioneer_id IN NUMBER,
924 p_viewer_id IN NUMBER,
925 p_has_items IN VARCHAR2,
926 p_doc_type IN VARCHAR2,
927 p_auction_status IN VARCHAR2,
928 p_view_by_date IN DATE,
929 p_open_bidding_date IN DATE,
930 p_has_scoring_teams_flag IN VARCHAR2,
931 p_user_trading_partner_id IN NUMBER,
932 x_buyer_monitor_image OUT NOCOPY VARCHAR2,
933 x_auction_status_display OUT NOCOPY VARCHAR2);
934
935 --========================================================================
936 -- PROCEDURE : GET_NEGOTIATION_STATUS
937 -- PARAMETERS:
938 -- p_auction_status - The auction_status column
939 -- p_is_paused - is_paused column
940 -- p_view_by_date - view_by_date column
941 -- p_open_bidding_date - open_bidding_date column
942 -- p_close_bidding_date - close_bidding_date column
943 -- p_award_status - award_status column
944 -- p_award_approval_status - award_approval_status column
945 -- p_outcome_status - outcome_status column
946 --
947 -- COMMENT : This procedure will be used in the pon_auction_headers_all_v
948 -- view to get the value for the negotiation_status
949 -- column in the view. Prior to the use of this function the
950 -- same code existed as decodes in the view itself.
951 --========================================================================
952 FUNCTION GET_NEGOTIATION_STATUS (
953 p_auction_status VARCHAR2,
954 p_is_paused VARCHAR2,
955 p_view_by_date DATE,
956 p_open_bidding_date DATE,
957 p_close_bidding_date DATE,
958 p_award_status VARCHAR2,
959 p_award_approval_status VARCHAR2,
960 p_outcome_status VARCHAR2
961 ) RETURN VARCHAR2;
962
963 --========================================================================
964 -- PROCEDURE : GET_SUPPL_NEGOTIATION_STATUS
965 -- PARAMETERS:
966 -- p_auction_status - The auction_status column
970 -- p_close_bidding_date - close_bidding_date column
967 -- p_is_paused - is_paused column
968 -- p_view_by_date - view_by_date column
969 -- p_open_bidding_date - open_bidding_date column
971 --
975 -- same code existed as decodes in the view itself.
972 -- COMMENT : This procedure will be used in the pon_auction_headers_all_v
973 -- view to get the value for the suppl_negotiation_status
974 -- column in the view. Prior to the use of this function the
976 --========================================================================
977 FUNCTION GET_SUPPL_NEGOTIATION_STATUS (
978 p_auction_status IN VARCHAR2,
979 p_is_paused IN VARCHAR2,
980 p_view_by_date IN DATE,
981 p_open_bidding_date IN DATE,
982 p_close_bidding_date IN DATE
983 ) RETURN VARCHAR2;
984
985 /*============================================================================================================*
986 * PROCEDURE : GET_DEFAULT_TIERS_INDICATOR *
987 * PARAMETERS: *
988 * p_contract_type - outcome of the negotiation *
989 * p_price_breaks_enabled - to indicate if price breaks are applicable as per po style *
990 * p_qty_price_tiers_enabled - to indicate if price tiers are applicable as per neg style *
991 * p_doctype_id - document type id of the negotiation *
992 * x_price_tiers_indicator - default price tiers indicator value. *
993 * *
994 * COMMENT : This procedure will be used in getting the default price tier indicator value. *
995 * It's used in plsql routines where new negotiation created from autocreation and renegotiation. *
996 * The logic is same as AuctionHeadersAllEO.getPriceTiersPoplist. Only difference is that we *
997 * don't have to return the poplist here. So few conditions where default values is same can be *
998 * clubbeb together. *
999 * ===========================================================================================================*/
1000 PROCEDURE GET_DEFAULT_TIERS_INDICATOR (
1001 p_contract_type IN VARCHAR2,
1002 p_price_breaks_enabled IN VARCHAR2,
1003 p_qty_price_tiers_enabled IN VARCHAR2,
1004 p_doctype_id IN NUMBER,
1005 x_price_tiers_indicator OUT NOCOPY VARCHAR2
1006 ) ;
1007
1008 END PON_AUCTION_PKG;
[Home] [Help]