1 PACKAGE PON_BID_DEFAULTING_PKG AUTHID CURRENT_USER AS
2 --$Header: PONBDDFS.pls 120.15.12020000.2 2013/02/09 06:16:29 hvutukur ship $
3
4 -- ======================================================================
5 -- PROCEDURE: IS_BIDDING_ALLOWED PUBLIC
6 -- PARAMETERS:
7 -- p_auc_header_id IN auction header id of negotiation
8 -- p_tpid IN trading partner id of supplier
9 -- p_tpcid IN trading partner contact id of supplier
10 -- p_vensid IN vendor site to place a bid for
11 -- p_venscode IN corresponding vendor site code
12 -- p_buyer_user IN determines if surrogate bid
13 -- p_action_code IN determines if certain validation should be suppressed
14 -- x_return_status OUT 0 for success, 1 for error
15 -- x_return_code OUT returned error code, or SUCCESS
16 --
17 -- COMMENT: Determine if the bidding action specified by action code can
18 -- be completed at this time.
19 -- ======================================================================
20 PROCEDURE is_bidding_allowed
21 (
22 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
23 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
24 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
25 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
26 p_venscode IN pon_bid_headers.vendor_site_code%TYPE,
27 p_buyer_user IN VARCHAR2,
28 ----------- Supplier Management: Supplier Evaluation -----------
29 p_evaluator_id IN pon_bid_headers.evaluator_id%TYPE,
30 p_eval_flag IN VARCHAR2,
31 ----------------------------------------------------------------
32 p_action_code IN VARCHAR2,
33 x_return_status OUT NOCOPY NUMBER,
34 x_return_code OUT NOCOPY VARCHAR2
35 );
36
37 -- ======================================================================
38 -- PROCEDURE: CREATE_DEFAULTED_BID PUBLIC
39 -- PARAMETERS:
40 -- p_auc_header_id IN auction header id of negotiation
41 -- p_source_bid IN the bid to default from
42 -- x_bid_number OUT bid number of draft loaded or created
43 -- x_return_status OUT 0 for success, 1 for error
44 -- x_return_code OUT returned error code, or SUCCESS
45 --
46 -- COMMENT: create a new draft on p_auc_header_id, defaulting from
47 -- p_source_bid
48 -- ======================================================================
49 PROCEDURE create_defaulted_draft
50 (
51 p_new_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
52 p_source_bid IN pon_bid_headers.bid_number%TYPE,
53 x_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE
54 );
55
56 -- ======================================================================
57 -- PROCEDURE: CHECK_AND_LOAD_BID PUBLIC
58 -- PARAMETERS:
59 -- p_auc_header_id IN auction header id of negotiation
60 -- p_draft_number IN non-null if a specific draft is to be loaded
61 -- p_tpid IN trading partner id of supplier
62 -- p_tpcid IN trading partner contact id of supplier
63 -- p_tpname IN trading partner name of supplier
64 -- p_tpcname IN trading partner contact name of supplier
65 -- p_userid IN userid of bid creator
66 -- p_venid IN vendor id
67 -- p_vensid IN vendor site to place a bid for
68 -- p_venscode IN corresponding vendor site code
69 -- p_buyer_user IN determines if surrogate bid
70 -- p_auctpid IN trading partner id of buyer if surrogate bid
71 -- p_auctpcid IN trading partner contact id of buyer if surrogate bid
72
73 -- p_evaluator_id IN evaluator user id
74 -- p_eval_flag IN flag indicating if the response is an evaluation
75
76 -- x_bid_number OUT bid number of draft loaded or created
77 -- x_rebid_flag OUT flag determining if rebid or not
78 -- x_prev_bid_number OUT source bid number
79 -- x_amend_bid_def OUT Y if source bid is on a previous amendment
80 -- x_round_bid_def OUT Y if source bid is on a previous round
81 -- x_prev_bid_disq OUT Y is source bid was disqualified
82 -- x_prev_bid_wthd OUT Y is source bid was Withdrawn
83 -- x_withdraw_reason OUT Y is source bid was Withdrawn
84
85 -- p_action_code IN determine if a special action needs to be taken
86 -- x_return_status OUT 0 for success, 1 for error
87 -- x_return_code OUT returned error code, or SUCCESS
88 --
89 -- COMMENT: Determine if the bidding action specified by action code can
90 -- be completed at this time.
91 -- ======================================================================
92 PROCEDURE check_and_load_bid
93 (
94 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
95 p_draft_number IN pon_bid_headers.bid_number%TYPE,
96 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
97 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
98 p_tpname IN pon_bid_headers.trading_partner_name%TYPE,
99 p_tpcname IN pon_bid_headers.trading_partner_contact_name%TYPE,
100 p_userid IN pon_bid_headers.created_by%TYPE,
101 p_venid IN pon_bid_headers.vendor_id%TYPE,
102 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
103 p_venscode IN pon_bid_headers.vendor_site_code%TYPE,
104 p_buyer_user IN VARCHAR2,
105 p_auctpid IN pon_bid_headers.surrog_bid_created_tp_id%TYPE,
106 p_auctpcid IN pon_bid_headers.surrog_bid_created_contact_id%TYPE,
107
108 ------------ Supplier Management: Supplier Evaluation ------------
109 p_evaluator_id IN pon_bid_headers.evaluator_id%TYPE,
110 p_eval_flag IN pon_bid_headers.evaluation_flag%TYPE,
111 ------------------------------------------------------------------
112
113 x_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE,
114 x_rebid_flag OUT NOCOPY VARCHAR2,
115 x_prev_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE,
116 x_amend_bid_def OUT NOCOPY VARCHAR2,
117 x_round_bid_def OUT NOCOPY VARCHAR2,
118 x_prev_bid_disq OUT NOCOPY VARCHAR2,
119 x_prev_bid_wthd OUT NOCOPY VARCHAR2,
120 x_withdraw_reason OUT NOCOPY VARCHAR2,
121 x_edit_draft OUT NOCOPY VARCHAR2,
122
123 p_action_code IN VARCHAR2,
124 x_return_status OUT NOCOPY NUMBER,
125 x_return_code OUT NOCOPY VARCHAR2
126 );
127
128 -- ======================================================================
129 -- FUNCTION: GET_SOURCE_BID_FOR_SPREADSHEET
130 -- PARAMETERS:
131 -- p_auc_header_id IN auction header id of negotiation
132 -- p_prev_round_auc_header_id IN auction header id of prev round negotiation
133 -- p_tpid IN trading partner id of supplier
134 -- p_tpcid IN trading partner contact id of supplier
135 -- p_auc_header_id_orig_amend IN auction header id of original amendment
136 -- p_amendment_number IN amendment number
137 -- p_vensid IN vendor site to place a bid for
138 --
139 -- COMMENT: This function is only used in spreadsheet export case.
140 -- Determine whether there are any bids existing for the current amendment.
141 -- If not, determines whether there are any bids in previous amendment
142 -- of current round; If still not, check whether there is an active bid
143 -- from previous round
144 -- ======================================================================
145 FUNCTION get_source_bid_for_spreadsheet
146 (
147 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
148 p_prev_round_auc_header_id IN pon_auction_headers_all.auction_header_id_prev_round%TYPE,
149 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
150 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
151 p_auc_header_id_orig_amend IN pon_auction_headers_all.auction_header_id_orig_amend%TYPE,
152 p_amendment_number IN pon_auction_headers_all.amendment_number%TYPE,
153 p_vensid IN pon_bid_headers.vendor_site_id%TYPE
154
155 ) RETURN NUMBER;
156
157
158 --------------------------------------------------------------------------------
159 -- can_supplier_create_payments --
160 --------------------------------------------------------------------------------
161 -- Start of Comments
162 --
163 -- API Name: can_supplier_create_payments
164 --
165 -- Type : Private
166 --
167 -- Pre-reqs: None
168 --
169 -- Function: This API is called by the Response Import Spreadsheet page.
170 -- It determines if there are any lines in the RFQ that can have payments.
171 -- If yes, then the "Pay Items" will be one of the option in the Import
172 -- and Export poplists
173 --
174 --
175 -- Parameters:
176 --
177 -- p_auction_header_id IN NUMBER
178 -- Auction header id - required
179 -- p_bid_number IN NUMBER
180 -- Bid Number - required
181 -- p_po_style_id IN NUMBER
182 -- PO Style Id - required
183 --
184 --
185 -- x_can_create_payments OUT VARCHAR2
186 -- Returns Y if payments can be created for atleast one of the
187 -- line to which supplier has access. Otherwise Returns N
188 --
189 --
190 -- End of Comments
191 --------------------------------------------------------------------------------
192 PROCEDURE can_supplier_create_payments(
193 p_auction_header_id IN NUMBER,
194 p_bid_number IN NUMBER,
195 x_can_create_payments OUT NOCOPY VARCHAR2) ;
196
197
198 /**
199 * This function calculates the total price on a line including the
200 * buyer and the supplier price factors in auction currency.
201 *
202 * This function will be used in view objects to display supplier's
203 * previous round price as the start price for this line instead of the
204 * auction line start price.
205 *
206 * This is as per Cendant requirement to enforce upon suppliers to
207 * bid lower than their bid on the previous round of the negotiation
208 *
209 * Currently anticipated usage of this function are on View Bid Page
210 * (ViewBidItemsVO), Negotiation Summary page (AuctionItemPricesAllVO)
211 * and bid creation page (ResponseAMImpl)
212 *
213 * p_auction_header_id - current round auction header id
214 * p_prev_auc_active_bid_number - bid number on the previous round
215 * p_line_number - current line number
216 * p_unit_price - bid line price in auction currency
217 * p_quantity - bid quantity for the current line
218 */
219
220 FUNCTION apply_price_factors(p_auction_header_id IN NUMBER,
221 p_prev_auc_active_bid_number IN NUMBER,
222 p_line_number IN NUMBER,
223 p_unit_price IN NUMBER,
224 p_quantity IN NUMBER
225 )
226 RETURN NUMBER;
227
228 /*CLM proceudre to default idv line id*/
229
230 PROCEDURE default_idv_line_id (p_po_header_id IN NUMBER,
231 p_auction_header_id IN NUMBER,
232 p_bid_number IN NUMBER);
233
234 /* Bug 10052785 - Start */
235 /* Added the following procedure to default Pricing and UDA data from
236 Solicitation to Offer. */
237
238 PROCEDURE default_line_from_sol (p_auction_header_id IN NUMBER,
239 p_auction_line_number IN NUMBER DEFAULT NULL,
240 p_bid_number IN NUMBER);
241
242
243 PROCEDURE COPY_PO_LINE_TO_BID_LINE(p_bid_header_id IN NUMBER,
244 p_bid_line_number IN NUMBER,
245 p_po_line_id IN NUMBER,
246 x_return_status OUT NOCOPY VARCHAR2,
247 x_msg_count OUT NOCOPY NUMBER,
248 x_msg_data OUT NOCOPY VARCHAR2);
249
250
251 /*======================================================================
252 PROCEDURE : get_max_internal_and_doc_line_num
253 PARAMETERS: 1. p_auction_header_id
254 2. p_bid_number - Bid Number
255 3. x_max_internal_line_num - The maximum internal line
256 number in all the rounds
257 4. x_max_document_line_num - The maximum subline sequence
258 number in all the rounds
259 5. x_result - return status.
260 6. x_error_code - error code
261 7. x_error_message - The actual error message
262 COMMENT : This procedure will return the maximum value of the
263 LINE_NUMBER and SUB_LINE_SEQUENCE_NUMBER columns in all
264 the rounds
265 ======================================================================*/
266
267 PROCEDURE GET_MAX_INTERNAL_AND_DOC_NUM (
268 p_auction_header_id IN NUMBER,
269 p_bid_number IN NUMBER,
270 x_max_internal_line_num OUT NOCOPY NUMBER,
271 x_max_document_line_num OUT NOCOPY NUMBER,
272 x_max_disp_line_num OUT NOCOPY NUMBER,
273 x_result OUT NOCOPY VARCHAR2,
274 x_error_code OUT NOCOPY VARCHAR2,
275 x_error_message OUT NOCOPY VARCHAR2);
276
277
278 -- ======================================================================
279 -- PROCEDURE: CREATE_NEW_DRAFT PRIVATE
280 -- PARAMETERS:
281 -- p_auc_header_id IN auction_header_id of negotiation
282 -- p_source_bid_num IN source_bid to default from
283 -- p_tpid IN trading partner id of supplier
284 -- p_tpcid IN trading partner contact id of supplier
285 -- p_tpname IN trading partner name of supplier
286 -- p_tpcname IN trading partner contact name of supplier
287 -- p_userid IN userid of bid creator
288 -- p_venid IN vendor id
289 -- p_vensid IN vendor site id to place bid for
290 -- p_venscode IN vendor site code to place bid for
291 -- p_auctpid IN buyers trading partner id
292 -- p_auctcpid IN buyers trading partner contact id
293 -- p_buyer_user IN flag indicating surrogate bid or not
294 -- p_evaluator_id IN evaluator user id
295 -- p_eval_flag IN flag indicating if the response is an evaluation
296 -- p_new_round_or_amended IN flag indicating, if bid defualt is happening for new round or amend
297 -- p_rebid_flag IN flag indicating rebid or not
298 -- x_bid_number OUT the bid number of the created bid
299 --
300 -- COMMENT: creates a new draft bid for the specified supplier/site
301 -- combination on the specified site. Inserts values into all bid
302 -- side tables and copies attachments
303 -- ======================================================================
304 PROCEDURE create_new_draft_bid
305 (
306 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
307 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
308 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
309 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
310 p_tpname IN pon_bid_headers.trading_partner_name%TYPE,
311 p_tpcname IN pon_bid_headers.trading_partner_contact_name%TYPE,
312 p_userid IN pon_bid_headers.created_by%TYPE,
313 p_venid IN pon_bid_headers.vendor_id%TYPE,
314 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
315 p_venscode IN pon_bid_headers.vendor_site_code%TYPE,
316 p_auctpid IN pon_bid_headers.surrog_bid_created_tp_id%TYPE,
317 p_auctpcid IN pon_bid_headers.surrog_bid_created_contact_id%TYPE,
318 p_buyer_user IN VARCHAR2,
319 ----------- Supplier Management: Supplier Evaluation -----------
320 p_evaluator_id IN pon_bid_headers.evaluator_id%TYPE,
321 p_eval_flag IN pon_bid_headers.evaluation_flag%TYPE,
322 ----------------------------------------------------------------
323 p_new_round_or_amended IN VARCHAR2,
324 p_rebid_flag IN VARCHAR2,
325 p_prev_bid_disq IN VARCHAR2,
326 p_prev_bid_wthd IN VARCHAR2,
327 x_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE,
328 x_return_status OUT NOCOPY NUMBER,
329 x_return_code OUT NOCOPY VARCHAR2
330 );
331
332 PROCEDURE lock_draft
333 (
334 p_bid_number IN pon_bid_headers.bid_number%TYPE,
335 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
336 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
337 x_return_status OUT NOCOPY NUMBER,
338 x_return_code OUT NOCOPY VARCHAR2
339 );
340
341 PROCEDURE check_amendments_acked
342 (
343 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
344 p_tpid IN pon_auction_headers_all.trading_partner_id%TYPE,
345 p_tpcid IN pon_auction_headers_all.trading_partner_contact_id%TYPE,
346 x_return_status OUT NOCOPY NUMBER,
347 x_return_code OUT NOCOPY VARCHAR2
348 );
349
350 PROCEDURE NULL_OUT_UNSOL_IDV_LINE_ID( p_auction_header_id IN NUMBER,
351 p_bid_number IN NUMBER);
352
353 FUNCTION is_invalid_site_for_bid(p_auction_header_id IN NUMBER, p_tp_id IN NUMBER)
354 RETURN NUMBER;
355
356 FUNCTION is_accepted_terms_cond(p_auction_header_id IN NUMBER,
357 p_auction_header_id_orig_amend IN NUMBER,
358 p_trading_partner_id number,
359 p_trading_partner_contact_id number
360 )
361 RETURN VARCHAR2;
362
363 END PON_BID_DEFAULTING_PKG;