[Home] [Help]
PACKAGE BODY: APPS.PON_MULTIPLE_RESPONSE_PKG
Source
1 PACKAGE BODY PON_MULTIPLE_RESPONSE_PKG AS
2 --$Header: PON_MULTIPLE_RESPONSE_PKG.plb 120.0.12020000.3 2013/02/09 06:08:57 hvutukur ship $
3
4 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.PON_MULTIPLE_RESPONSE_PKG.';
5
6 g_pkg_name CONSTANT VARCHAR2(30) := 'PON_MULTIPLE_RESPONSE_PKG';
7 g_debug_mode CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
8
9 -- ======================================================================
10 -- PROCEDURE: LOG_MESSAGE PRIVATE
11 -- PARAMETERS:
12 -- p_module IN Pass the module name
13 -- p_message IN the string to be logged
14 --
15 -- COMMENT: Common procedure to log messages in FND_LOG.
16 -- ======================================================================
17 PROCEDURE log_message
18 (
19 p_module IN VARCHAR2,
20 p_message IN VARCHAR2
21 ) IS
22 BEGIN
23 IF (g_debug_mode = 'Y') THEN
24 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
25 FND_LOG.string(log_level => FND_LOG.level_statement,
26 module => g_module_prefix || p_module,
27 message => p_message);
28 END IF;
29 END IF;
30 END log_message;
31
32
33
34 /*PROCEDURE insert_into_vhk_dummy(message VARCHAR2)
35 AS PRAGMA autonomous_transaction;
36 BEGIN
37 INSERT INTO vhk_dummy values(message);
38 COMMIT;
39 END;*/
40
41 /* This procedure returns 'Y' if multiple responses are enabled otherwise 'N'.
42 * Returns 'Y' : when ALLOW_MULTIPLE_ACTIVE_OFFERS control is enabled and :
43 if buyer, neg should not two stage enabled
44 if supplier
45 */
46 FUNCTION multiple_resp_enabled(p_auction_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
47 p_buyer_user IN VARCHAR2)
48 RETURN VARCHAR2 IS
49
50 l_multi_resp_enabled VARCHAR2(1);
51 BEGIN
52
53 SELECT Decode(Nvl(ALLOW_MULTIPLE_ACTIVE_OFFERS,'N'),'Y',
54 decode(p_buyer_user,'Y',
55 Decode(Nvl(two_part_flag,'N'),'Y','N','Y'),'Y'),'N')
56 INTO l_multi_resp_enabled
57 FROM pon_auction_headers_all
58 WHERE auction_header_id = p_auction_header_id;
59
60 RETURN l_multi_resp_enabled;
61
62 END multiple_resp_enabled;
63
64 /* This procedure gives auction_header_id of document in this round and on previous round
65 * which has offers. Draft offers on previous round are not considered.
66 * If offers exist on multiple documents, latest submitted document with offers will be returned.
67 */
68 PROCEDURE get_latest_neg_with_resp(p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
69 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
70 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
71 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
72 p_evaluator_id IN pon_bid_headers.evaluator_id%TYPE,
73 p_eval_flag IN VARCHAR2,
74 p_buyer_user IN VARCHAR2,
75 x_auc_id_with_resp OUT NOCOPY pon_auction_headers_all.auction_header_id%TYPE,
76 x_is_prev_amend OUT NOCOPY VARCHAR2) IS
77
78 l_prev_round_id pon_auction_headers_all.auction_header_id%TYPE;
79 l_orig_amend_id pon_auction_headers_all.auction_header_id%TYPE;
80
81 CURSOR c_get_neg_with_resp IS
82 SELECT ah.auction_header_id,
83 Decode(ah.auction_header_id_orig_amend,l_orig_amend_id,'Y','N')
84 FROM pon_auction_headers_all ah,
85 pon_bid_headers bh
86 WHERE (ah.auction_header_id_orig_amend = l_orig_amend_id
87 OR ah.auction_header_id_orig_amend = l_prev_round_id)
88 AND ah.auction_header_id <> p_auc_header_id
89 AND EXISTS
90 (SELECT 1 FROM pon_bid_headers bh
91 WHERE bh.auction_header_id = ah.auction_header_id
92 AND Nvl(bh.surrog_bid_flag,'N') = p_buyer_user
93 AND bh.trading_partner_id = p_tpid
94 AND ((p_eval_flag = 'N' AND bh.trading_partner_contact_id = p_tpcid) OR
95 (p_eval_flag = 'Y' AND bh.evaluator_id = p_evaluator_id))
96 AND nvl(bh.vendor_site_id, -1) = p_vensid
97 AND ((bh.bid_status = 'DRAFT' AND ah.auction_header_id_orig_amend = l_orig_amend_id)
98 OR bh.bid_status IN ('ACTIVE', 'RESUBMISSION', 'DISQUALIFIED')
99 OR (bh.bid_status = 'ARCHIVED' AND bh.withdraw_reason is not null))
100 AND nvl(bh.evaluation_flag, 'N') = p_eval_flag)
101 ORDER BY ah.publish_date desc;
102
103
104 BEGIN
105 SELECT ah.auction_header_id_orig_amend, ah2.auction_header_id_orig_amend
106 INTO l_orig_amend_id, l_prev_round_id
107 FROM pon_auction_headers_all ah, pon_auction_headers_all ah2
108 WHERE ah.auction_header_id = p_auc_header_id
109 and ah2.auction_header_id (+) = ah.auction_header_id_prev_round;
110
111 OPEN c_get_neg_with_resp;
112 FETCH c_get_neg_with_resp INTO x_auc_id_with_resp, x_is_prev_amend;
113 CLOSE c_get_neg_with_resp;
114
115 EXCEPTION
116 WHEN No_Data_Found THEN
117 x_auc_id_with_resp := NULL;
118 x_is_prev_amend := NULL;
119
120 END get_latest_neg_with_resp;
121
122 PROCEDURE check_resp_exists(p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
123 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
124 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
125 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
126 p_evaluator_id IN pon_bid_headers.evaluator_id%TYPE,
127 p_eval_flag IN VARCHAR2,
128 p_buyer_user IN VARCHAR2,
129 x_return_status OUT NOCOPY NUMBER,
130 x_return_code OUT NOCOPY VARCHAR2) IS
131
132 l_prev_round_id pon_auction_headers_all.auction_header_id%TYPE;
133 l_orig_amend_id pon_auction_headers_all.auction_header_id%TYPE;
134 l_terms_cond_apply VARCHAR2(1);
135 l_bid_count NUMBER;
136 -- for the bug 13984944
137 l_org_contract_status pon_contracts.contract_status%TYPE;
138 l_enabled_flag pon_contracts.enabled_flag%TYPE;
139 l_org_id pon_auction_headers_all.org_id%TYPE;
140 l_count_org NUMBER;
141
142 -- select bids on all amendments/previous round by the current user for the current site
143 CURSOR c_check_bids_exist IS
144 SELECT Count(*)
145 FROM pon_bid_headers bh, pon_auction_headers_all ah
146 WHERE
147 -- look at all amendments on the current round
148 (ah.auction_header_id_orig_amend = l_orig_amend_id
149 -- look at all amendments on the previous round
150 OR ah.auction_header_id_orig_amend = l_prev_round_id)
151 AND bh.auction_header_id = ah.auction_header_id
152 AND ( (Nvl(p_buyer_user,'N') = 'Y' AND nvl(bh.surrog_bid_flag,'N')='Y')
153 OR (Nvl(p_buyer_user,'N') = 'N' AND nvl(bh.surrog_bid_flag,'N')='N'))
154 AND bh.trading_partner_id = p_tpid
155 AND ((p_eval_flag = 'N' AND bh.trading_partner_contact_id = p_tpcid) OR
156 (p_eval_flag = 'Y' AND bh.evaluator_id = p_evaluator_id))
157 AND nvl(bh.vendor_site_id, -1) = p_vensid
158 -- we ignore DRAFT bids on previous rounds
159 AND ((bh.bid_status = 'DRAFT'
160 AND ah.auction_header_id_orig_amend = l_orig_amend_id)
161 OR bh.bid_status IN ('ACTIVE', 'RESUBMISSION', 'DISQUALIFIED')
162 OR (bh.bid_status = 'ARCHIVED' AND bh.withdraw_reason is not null))
163 AND nvl(bh.evaluation_flag, 'N') = p_eval_flag;
164
165 BEGIN
166
167 log_message ('check_resp_exists', 'Start');
168 -- Get the original amendment id's for the current and prev rounds.
169 -- Also check if contracts have been installed
170 SELECT ah.auction_header_id_orig_amend, ah2.auction_header_id_orig_amend,
171 nvl2(ah.contract_id, 'Y', 'N'), ah.org_id
172 INTO l_orig_amend_id, l_prev_round_id, l_terms_cond_apply, l_org_id
173 FROM pon_auction_headers_all ah, pon_auction_headers_all ah2
174 WHERE ah.auction_header_id = p_auc_header_id
175 and ah2.auction_header_id (+) = ah.auction_header_id_prev_round;
176
177 log_message ('check_resp_exists', 'l_orig_amend_id = ' || l_orig_amend_id ||
178 ' ,l_prev_round_id = ' || l_prev_round_id ||
179 ' ,l_terms_cond_apply = ' || l_terms_cond_apply);
180
181 l_bid_count := 0;
182
183 OPEN c_check_bids_exist;
184 FETCH c_check_bids_exist INTO l_bid_count;
185 CLOSE c_check_bids_exist;
186
187 log_message ('check_resp_exists', 'l_bid_count = ' || l_bid_count);
188
189 IF l_bid_count > 0 THEN
190 x_return_status := 1;
191 x_return_code := 'TO_RESP_EXIST_WARNING';
192 ELSE
193 -- for the bug 13984944
194 SELECT Count(*)
195 INTO l_count_org
196 FROM pon_contracts
197 WHERE NVL(org_id,-1) = l_org_id ;
198
199 -- if terms and conditions not defined for the current operating unit set l_org_id to global.
200 IF(l_count_org = 0) THEN l_org_id := -1;
201 ELSE
202 -- get the contract status for the current Operating Unit.
203 SELECT NVL(contract_status,'ACTIVE')
204 INTO l_org_contract_status
205 FROM pon_contracts
206 WHERE NVL(org_id,-1) = l_org_id
207 AND version_num = (select max(version_num) from pon_contracts where NVL(org_id,-1) = l_org_id);
208
209 -- If the status is not Active set l_org_id global
210 IF(l_org_contract_status <> 'ACTIVE') THEN
211 l_org_id := -1;
212 END IF;
213 END IF;
214
215 -- check for any rows existance in pon_contracts table for l_org_id
216
217 SELECT Count(*)
218 INTO l_count_org
219 FROM pon_contracts
220 WHERE NVL(org_id,-1) = l_org_id ;
221
222 IF(l_count_org = 0) THEN l_enabled_flag := 'N';
223 ELSE
224 -- Check the enbled_flag for the Max Version Num of l_org_id
225 SELECT enabled_flag
226 INTO l_enabled_flag
227 FROM pon_contracts pc
228 WHERE NVL(org_id,-1) = l_org_id
229 AND version_num = (select max(version_num) from pon_contracts where NVL(org_id,-1) = l_org_id);
230 END IF;
231
232 -- Creating fresh bid.
233 -- User must accept terms and conditions if contracts installed.
234 IF ((l_enabled_flag = 'Y' OR l_enabled_flag = 'X') AND (p_eval_flag='Y' OR pon_bid_defaulting_pkg.is_accepted_terms_cond(p_auc_header_id,l_orig_amend_id,p_tpid,p_tpcid)='N')) THEN
235 x_return_status := 1;
236 x_return_code := 'TO_TERMS_COND';
237 ELSE
238 x_return_status := 0;
239 x_return_code := 'CREATE_NEW_DRAFT';
240 END IF;
241
242 END IF;
243
244 log_message ('check_resp_exists', 'x_return_status = ' || x_return_status ||
245 ', x_return_code = ' || x_return_code);
246
247 END check_resp_exists;
248
249 -- ======================================================================
250 -- PROCEDURE: HANDLE_PROXY PRIVATE
251 -- PARAMETERS:
252 -- p_auc_header_id IN the auction header id
253 -- p_draft_bid_num IN bid number to update proxy for
254 -- p_tpid IN trading partner id of supplier
255 -- p_tpcid IN trading partner contact id of supplier
256 -- p_vensid IN vendor site bid is placed on
257 -- p_evaluator_id IN evaluator user id
258 -- p_eval_flag IN flag indicating if the response is an evaluation
259 -- x_prev_bid_number OUT returned backing bid number
260 -- x_rebid_flag OUT Y/N if the current bid is a rebid/not a rebid
261 --
262 -- COMMENT: This procedure is a version of pon_bid_defaulting_pkg.handle_proxy
263 --
264 -- ======================================================================
265 PROCEDURE handle_proxy
266 (
267 p_auc_header_id IN pon_bid_headers.auction_header_id%TYPE,
268 p_draft_bid_num IN pon_bid_headers.bid_number%TYPE,
269 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
270 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
271 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
272 ------------ Supplier Management: Supplier Evaluation ------------
273 p_evaluator_id IN pon_bid_headers.evaluator_id%TYPE,
274 p_eval_flag IN pon_bid_headers.evaluation_flag%TYPE,
275 ------------------------------------------------------------------
276 x_prev_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE,
277 x_rebid_flag OUT NOCOPY VARCHAR2
278 ) IS
279 l_max_line_number pon_bid_item_prices.line_number%TYPE;
280 l_batch_start pon_bid_item_prices.line_number%TYPE;
281 l_batch_end pon_bid_item_prices.line_number%TYPE;
282 BEGIN
283
284 SELECT bh.old_bid_number
285 INTO x_prev_bid_number
286 FROM pon_bid_headers bh
287 WHERE bh.bid_number = p_draft_bid_num;
288
289 SELECT bh.bid_number, Decode(bh.bid_status,'ACTIVE','Y','N')
290 INTO x_prev_bid_number, x_rebid_flag
291 FROM pon_bid_headers bh
292 WHERE bh.bid_number = x_prev_bid_number;
293
294
295 IF x_rebid_flag = 'Y' THEN
296
297 -- Determine the maximum line number for the negotiation
298 SELECT ah.max_internal_line_num
299 INTO l_max_line_number
300 FROM pon_auction_headers_all ah
301 WHERE ah.auction_header_id = p_auc_header_id;
302
303 -- Define the initial batch range (line numbers are indexed from 1)
304 l_batch_start := 1;
305 IF (l_max_line_number < PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE) THEN
306 l_batch_end := l_max_line_number;
307 ELSE
308 l_batch_end := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
309 END IF;
310
311 WHILE (l_batch_start <= l_max_line_number) LOOP
312
313 -- Copy over price columns and set copy_price_for_proxy_flag
314 -- If the proxy has been exhausted, copy_price_for_proxy flag changed to N
315 /*UPDATE pon_bid_item_prices bl
316 SET (bl.price, bl.proxy_bid_limit_price, bl.bid_currency_price,
317 bl.bid_currency_limit_price, bl.bid_currency_trans_price,
318 bl.unit_price, bl.bid_currency_unit_price, bl.copy_price_for_proxy_flag,
319 bl.old_price, bl.old_bid_currency_unit_price, bl.old_bid_currency_price,
320 bl.old_bid_currency_limit_price) =
321 (SELECT old_bl.price, old_bl.proxy_bid_limit_price, old_bl.bid_currency_price,
322 old_bl.bid_currency_limit_price, old_bl.bid_currency_trans_price,
323 old_bl.unit_price, old_bl.bid_currency_unit_price,
324 decode(sign(old_bl.proxy_bid_limit_price - old_bl.price),
325 0, 'N', 'Y'),
326 old_bl.price, old_bl.bid_currency_unit_price, old_bl.bid_currency_price, old_bl.bid_currency_limit_price
327 FROM pon_bid_item_prices old_bl
328 WHERE old_bl.bid_number = x_prev_bid_number
329 AND old_bl.line_number = bl.line_number)
330 WHERE bl.bid_number = p_draft_bid_num
331 AND bl.copy_price_for_proxy_flag = 'Y'
332 AND bl.line_number BETWEEN l_batch_start AND l_batch_end; */
333
334 -- Copy over the rank for all lines
335 UPDATE pon_bid_item_prices bl
336 SET rank =
337 (SELECT old_bl.rank
338 FROM pon_bid_item_prices old_bl
339 WHERE old_bl.bid_number = x_prev_bid_number
340 AND old_bl.line_number = bl.line_number)
341 WHERE bl.bid_number = p_draft_bid_num
342 AND bl.line_number BETWEEN l_batch_start AND l_batch_end
343 AND bl.auction_line_number <> -1; --Donot update unsolicited lines.
344
345 -- Find the new batch range
346 l_batch_start := l_batch_end + 1;
347 IF (l_batch_end + PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE > l_max_line_number) THEN
348 l_batch_end := l_max_line_number;
349 ELSE
350 l_batch_end := l_batch_end + PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
351 END IF;
352
353 END LOOP;
354 -- END BATCHING
355 END IF; -- x_rebid_flag = 'Y'
356
357 EXCEPTION
358 -- No ACTIVE bids on the current amendment
359 WHEN NO_DATA_FOUND THEN
360 x_prev_bid_number := NULL;
361 x_rebid_flag := 'N';
362
363 END handle_proxy;
364
365
366 PROCEDURE check_draft_exists(p_auc_header_id IN pon_auction_headers_all.AUCTION_HEADER_ID%TYPE,
367 p_source_bid_num IN pon_bid_headers.BID_NUMBER%TYPE,
368 p_draft_bid_num OUT NOCOPY pon_bid_headers.BID_NUMBER%TYPE)
369 IS
370
371 BEGIN
372
373 SELECT bid_number
374 INTO p_draft_bid_num
375 FROM pon_bid_headers
376 WHERE auction_header_id = p_auc_header_id
377 AND old_bid_number = p_source_bid_num;
378
379 EXCEPTION
380 WHEN No_Data_Found THEN
381 p_draft_bid_num := NULL;
382
383 END check_draft_exists;
384
385 /* This is a overloaded procedure for pon_bid_defaulting_pkg.lock_draft */
386 PROCEDURE lock_draft_bid(p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
387 p_draft_number IN pon_bid_headers.bid_number%TYPE,
388 p_buyer_user IN VARCHAR2,
389 p_auctpid IN pon_bid_headers.surrog_bid_created_tp_id%TYPE,
390 p_auctpcid IN pon_bid_headers.surrog_bid_created_contact_id%TYPE,
391 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
392 p_evaluator_id IN pon_bid_headers.evaluator_id%TYPE,
393 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
394 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
395 p_eval_flag IN pon_bid_headers.evaluation_flag%TYPE,
396 x_rebid_flag OUT NOCOPY VARCHAR2,
397 x_prev_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE,
398 x_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE,
399 x_amend_bid_def OUT NOCOPY VARCHAR2,
400 x_round_bid_def OUT NOCOPY VARCHAR2,
401 x_prev_bid_disq OUT NOCOPY VARCHAR2,
402 x_prev_bid_wthd OUT NOCOPY VARCHAR2,
403 x_withdraw_reason OUT NOCOPY VARCHAR2,
404 x_edit_draft OUT NOCOPY VARCHAR2,
405 x_return_status OUT NOCOPY NUMBER,
406 x_return_code OUT NOCOPY VARCHAR2 )
407 IS
408
409 BEGIN
410 --insert_into_vhk_dummy('procedure lock_draft_bid : p_draft_number - '||p_draft_number);
411
412 log_message('lock_draft_bid','p_auc_header_id = '||p_auc_header_id||
413 ',p_draft_number = '||p_draft_number||
414 ',p_buyer_user = '||p_buyer_user||
415 ',p_auctpid = '||p_auctpid||
416 ',p_auctpcid = '||p_auctpcid||
417 ',p_tpid = '||p_tpid||
418 ',p_evaluator_id = '||p_evaluator_id||
419 ',p_tpcid = '||p_tpcid||
420 ',p_vensid = '||p_vensid||
421 ',p_eval_flag = '||p_eval_flag);
422
423 -- Check that the draft is not locked by another user and lock it
424 IF (p_buyer_user = 'Y') THEN
425 pon_bid_defaulting_pkg.lock_draft
426 (p_draft_number,
427 p_auctpid,
428 p_auctpcid,
429 x_return_status,
430 x_return_code);
431
432 -- Begin Supplier Management: Supplier Evaluation
433 ELSIF (p_eval_flag = 'Y') THEN
434 pon_bid_defaulting_pkg.lock_draft
435 (p_draft_number,
436 p_tpid,
437 p_evaluator_id,
438 x_return_status,
439 x_return_code);
440 -- End Supplier Management: Supplier Evaluation
441 ELSE
442 pon_bid_defaulting_pkg.lock_draft
443 (p_draft_number,
444 p_tpid,
445 p_tpcid,
446 x_return_status,
447 x_return_code);
448 END IF;
449
450 log_message('lock_draft_bid','after pon_bid_defaulting_pkg.lock_draft : '||
451 ' x_return_status = ' || x_return_status ||
452 ', x_return_code = ' || x_return_code);
453
454 IF (x_return_status = 1) THEN
455 RETURN;
456 END IF;
457
458 -- Finally, handle proxy bidding and copy rank
459 --Check if this is need for multiple active offers.
460 handle_proxy
461 (p_auc_header_id,
462 p_draft_number,
463 p_tpid,
464 p_tpcid,
465 p_vensid,
466 ---- Supplier Management: Supplier Evaluation ----
467 p_evaluator_id,
468 p_eval_flag,
469 --------------------------------------------------
470 x_prev_bid_number,
471 x_rebid_flag);
472
473 log_message('lock_draft_bid','after handle_proxy : '||
474 ' x_prev_bid_number = ' || x_prev_bid_number ||
475 ', x_rebid_flag = ' || x_rebid_flag);
476
477 -- set flags and return values before returning
478 x_bid_number := p_draft_number;
479 x_amend_bid_def := 'N';
480 x_round_bid_def := 'N';
481 x_prev_bid_disq := 'N';
482 x_edit_draft := 'Y';
483 x_return_status := 0;
484 x_return_code := 'SUCCESS';
485 RETURN;
486
487 END lock_draft_bid;
488
489
490 PROCEDURE get_source_bid_details(p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
491 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
492 x_rebid_flag OUT NOCOPY VARCHAR2,
493 x_amend_bid_def OUT NOCOPY VARCHAR2,
494 x_round_bid_def OUT NOCOPY VARCHAR2,
495 x_prev_bid_disq OUT NOCOPY VARCHAR2,
496 x_prev_bid_wthd OUT NOCOPY VARCHAR2,
497 x_withdraw_reason OUT NOCOPY VARCHAR2)
498 IS
499 l_orig_amend_id pon_auction_headers_all.auction_header_id%TYPE;
500 l_bid_status pon_bid_headers.bid_status%TYPE;
501
502 BEGIN
503 SELECT ah.auction_header_id_orig_amend
504 INTO l_orig_amend_id
505 FROM pon_auction_headers_all ah
506 WHERE ah.auction_header_id = p_auc_header_id;
507
508 SELECT
509 bh.bid_status,
510 decode(bh.bid_status, 'ACTIVE', decode(bh.auction_header_id, p_auc_header_id, 'Y', 'N'), 'N') rebid_flag,
511 decode(ah.auction_header_id_orig_amend, l_orig_amend_id, 'N', 'Y') prev_round_def,
512 decode(ah.auction_header_id, p_auc_header_id, 'N',
513 decode(ah.auction_header_id_orig_amend, l_orig_amend_id, 'Y', 'N')) prev_amend_def,
514 decode(bh.bid_status, 'DISQUALIFIED', 'Y', 'N') prev_bid_disq,
515 decode(bh.bid_status,'ARCHIVED',decode(bh.withdraw_reason,null,'N','Y'),'N') prev_bid_withdrawn,
516 bh.withdraw_reason
517 INTO
518 l_bid_status,
519 x_rebid_flag,
520 x_round_bid_def,
521 x_amend_bid_def,
522 x_prev_bid_disq,
523 x_prev_bid_wthd,
524 x_withdraw_reason
525 FROM pon_bid_headers bh,
526 pon_auction_headers_all ah
527 WHERE bid_number = p_source_bid_num
528 AND ah.auction_header_id = bh.auction_header_id;
529
530 -- If source bid is draft on previous amendment or new round, then
531 -- mark it as archived_draft
532 IF l_bid_status = 'DRAFT'
533 AND (x_round_bid_def = 'Y' OR x_amend_bid_def = 'Y') THEN
534
535 UPDATE pon_bid_headers
536 SET bid_status = 'ARCHIVED_DRAFT',
537 last_update_date = SYSDATE
538 WHERE bid_number = p_source_bid_num;
539
540 END IF;
541
542 EXCEPTION
543 WHEN No_Data_Found THEN
544 NULL;
545 END get_source_bid_details;
546
547
548 -- ======================================================================
549 -- PROCEDURE: CHECK_AND_LOAD_BID PUBLIC
550 -- PARAMETERS:
551 -- p_auc_header_id IN auction header id of negotiation
552 -- p_draft_number IN non-null if a specific draft is to be loaded
553 -- or if the action code is CREATE_NEW_AMEND_DRAFT
554 -- p_tpid IN trading partner id of supplier
555 -- p_tpcid IN trading partner contact id of supplier
556 -- p_tpname IN trading partner name of supplier
557 -- p_tpcname IN trading partner contact name of supplier
558 -- p_userid IN userid of bid creator
559 -- p_venid IN vendor id
560 -- p_vensid IN vendor site to place a bid for
561 -- p_venscode IN corresponding vendor site code
562 -- p_buyer_user IN determines if surrogate bid
563 -- p_auctpid IN trading partner id of buyer if surrogate bid
564 -- p_auctpcid IN trading partner contact id of buyer if surrogate bid
565
566 -- p_evaluator_id IN evaluator user id
567 -- p_eval_flag IN flag indicating if the response is an evaluation
568
569 -- x_bid_number OUT bid number of draft loaded or created
570 -- x_rebid_flag OUT flag determining if rebid or not
571 -- x_prev_bid_number OUT source bid number
572 -- x_amend_bid_def OUT Y if source bid is on a previous amendment
573 -- x_round_bid_def OUT Y if source bid is on a previous round
574 -- x_prev_bid_disq OUT Y if source bid was disqualified
575 -- x_prev_bid_wthd OUT Y if source bid was Withdrawn
576 -- x_withdraw_reason source bid Withdraw Reason
577 -- x_edit_draft OUT Y if we loaded an existing draft
578
579 -- p_action_code IN determine if a special action needs to be taken
580 -- x_return_status OUT 0 for success, 1 for error
581 -- x_return_code OUT returned error code, or SUCCESS
582 --
583 -- COMMENT: This procedure should be called only when multiple offers
584 -- are enabled.
585 -- Main procedure which determines whether a new or defaulted bid
586 -- is to be created. Or whether a draft already exists
587 -- ======================================================================
588 PROCEDURE check_and_load_bid
589 (
590 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
591 p_draft_number IN pon_bid_headers.bid_number%TYPE,
592 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
593 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
594 p_tpname IN pon_bid_headers.trading_partner_name%TYPE,
595 p_tpcname IN pon_bid_headers.trading_partner_contact_name%TYPE,
596 p_userid IN pon_bid_headers.created_by%TYPE,
597 p_venid IN pon_bid_headers.vendor_id%TYPE,
598 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
599 p_venscode IN pon_bid_headers.vendor_site_code%TYPE,
600 p_buyer_user IN VARCHAR2,
601 p_auctpid IN pon_bid_headers.surrog_bid_created_tp_id%TYPE,
602 p_auctpcid IN pon_bid_headers.surrog_bid_created_contact_id%TYPE,
603
604 ----------- Supplier Management: Supplier Evaluation -----------
605 p_evaluator_id IN pon_bid_headers.evaluator_id%TYPE,
606 p_eval_flag IN pon_bid_headers.evaluation_flag%TYPE,
607 ----------------------------------------------------------------
608
609 x_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE,
610 x_rebid_flag OUT NOCOPY VARCHAR2,
611 x_prev_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE,
612 x_amend_bid_def OUT NOCOPY VARCHAR2,
613 x_round_bid_def OUT NOCOPY VARCHAR2,
614 x_prev_bid_disq OUT NOCOPY VARCHAR2,
615 x_prev_bid_wthd OUT NOCOPY VARCHAR2,
616 x_withdraw_reason OUT NOCOPY VARCHAR2,
617 x_edit_draft OUT NOCOPY VARCHAR2,
618
619 p_action_code IN VARCHAR2,
620 p_rev_bid_number IN pon_bid_headers.bid_number%TYPE,
621 x_return_status OUT NOCOPY NUMBER,
622 x_return_code OUT NOCOPY VARCHAR2
623 ) IS
624 l_new_round_or_amended VARCHAR2(1);
625 l_existing_draft_bid pon_bid_headers.bid_number%TYPE;
626
627 BEGIN
628 --insert_into_vhk_dummy('Start of procedure check_and_load_bid p_rev_bid_number : '||p_rev_bid_number);
629
630 log_message ('check_and_load_bid', 'p_auc_header_id = ' || p_auc_header_id ||
631 ', p_draft_number = ' || p_draft_number ||
632 ', p_tpid = ' || p_tpid ||
633 ', p_tpcid = ' || p_tpcid ||
634 ', p_tpname = ' || p_tpname ||
635 ', p_userid = ' || p_userid ||
636 ', p_venid = ' || p_venid ||
637 ', p_vensid = ' || p_vensid ||
638 ', p_venscode = ' || p_venscode ||
639 ', p_buyer_user = ' || p_buyer_user ||
640 ', p_auctpid = ' || p_auctpid ||
641 ', p_auctpcid = ' || p_auctpcid ||
642 ', p_evaluator_id = ' || p_evaluator_id ||
643 ', p_eval_flag = ' || p_eval_flag);
644
645 log_message ('check_and_load_bid', 'p_action_code = ' || p_action_code ||
646 ', p_rev_bid_number = ' || p_rev_bid_number);
647
648 --First check if all amendments are acknowledged by this user.
649 -- Bug 10027124 - only do the check if it's not evaluation
650 IF (p_eval_flag = 'N') THEN
651 pon_bid_defaulting_pkg.check_amendments_acked(p_auc_header_id, p_tpid, p_tpcid,
652 x_return_status, x_return_code);
653 log_message ('check_and_load_bid', 'after pon_bid_defaulting_pkg.check_amendments_acked : x_return_status = ' || x_return_status);
654 END IF;
655 IF (x_return_status = 1) THEN
656 RETURN;
657 END IF;
658
659
660 /* If p_draft_number is null and p_action_code is null, then check
661 * if any response exist on current round or on previous round.
662 * If response exists, then return with appropriate error message.
663 * If no response exists, then return with terms and conditions message.
664 */
665 IF p_draft_number is NULL
666 AND p_action_code is NULL
667 AND p_rev_bid_number IS NULL THEN
668
669 check_resp_exists(p_auc_header_id,
670 p_tpid,
671 p_tpcid,
672 p_vensid,
673 p_evaluator_id,
674 p_eval_flag,
675 p_buyer_user,
676 x_return_status,
677 x_return_code);
678
679 log_message ('check_and_load_bid', 'after check_resp_exists : x_return_status = ' || x_return_status);
680
681 IF x_return_status = 1 THEN
682 RETURN;
683 ELSIF (x_return_code = 'CREATE_NEW_DRAFT') THEN
684
685 x_rebid_flag := 'N';
686 x_prev_bid_number := NULL;
687 x_amend_bid_def := 'N';
688 x_round_bid_def := 'N';
689 x_prev_bid_disq := 'N';
690 x_prev_bid_wthd := 'N';
691 x_edit_draft := 'N';
692
693 -- The following flag is needed for payments copy
694 -- This sets to y if defaulting is happening because the
695 -- negotiation being amended or new round started
696 l_new_round_or_amended := 'N';
697
698 -- Create the new bid
699 pon_bid_defaulting_pkg.create_new_draft_bid
700 (p_auc_header_id,
701 x_prev_bid_number,
702 p_tpid,
703 p_tpcid,
704 p_tpname,
705 p_tpcname,
706 p_userid,
707 p_venid,
708 p_vensid,
709 p_venscode,
710 p_auctpid,
711 p_auctpcid,
712 p_buyer_user,
713 ---- Supplier Management: Supplier Evaluation ----
714 p_evaluator_id,
715 p_eval_flag,
716 --------------------------------------------------
717 l_new_round_or_amended,
718 x_rebid_flag,
719 x_prev_bid_disq,
720 x_prev_bid_wthd,
721 x_bid_number,
722 x_return_status,
723 x_return_code);
724
725 log_message ('check_and_load_bid', 'after pon_bid_defaulting_pkg.create_new_draft_bid : x_return_status = ' || x_return_status ||
726 ', x_return_code = ' || x_return_code ||
727 ', l_new_round_or_amended = '|| l_new_round_or_amended ||
728 ', x_rebid_flag = '|| x_rebid_flag ||
729 ', x_prev_bid_disq = '|| x_prev_bid_disq ||
730 ', x_prev_bid_wthd = '|| x_prev_bid_wthd ||
731 ', x_bid_number = '|| x_bid_number);
732
733 RETURN;
734
735 END IF; -- End if x_return_status = 1
736 END IF; --End if p_draft_number is NULL AND p_action_code is NULL
737
738 IF p_action_code = 'CREATE_NEW_DRAFT' THEN
739 x_rebid_flag := 'N';
740 x_prev_bid_number := NULL;
741 x_amend_bid_def := 'N';
742 x_round_bid_def := 'N';
743 x_prev_bid_disq := 'N';
744 x_prev_bid_wthd := 'N';
745 x_edit_draft := 'N';
746
747 -- The following flag is needed for payments copy
748 -- This sets to y if defaulting is happening because the
749 -- negotiation being amended or new round started
750 l_new_round_or_amended := 'N';
751
752 log_message ('check_and_load_bid', 'before pon_bid_defaulting_pkg.create_new_draft_bid');
753
754 -- Create the new bid
755 pon_bid_defaulting_pkg.create_new_draft_bid
756 (p_auc_header_id,
757 x_prev_bid_number,
758 p_tpid,
759 p_tpcid,
760 p_tpname,
761 p_tpcname,
762 p_userid,
763 p_venid,
764 p_vensid,
765 p_venscode,
766 p_auctpid,
767 p_auctpcid,
768 p_buyer_user,
769 ---- Supplier Management: Supplier Evaluation ----
770 p_evaluator_id,
771 p_eval_flag,
772 --------------------------------------------------
773 l_new_round_or_amended,
774 x_rebid_flag,
775 x_prev_bid_disq,
776 x_prev_bid_wthd,
777 x_bid_number,
778 x_return_status,
779 x_return_code);
780
781 log_message ('check_and_load_bid', 'after pon_bid_defaulting_pkg.create_new_draft_bid : x_return_status = ' || x_return_status ||
782 ', x_return_code = ' || x_return_code ||
783 ', l_new_round_or_amended = '|| l_new_round_or_amended ||
784 ', x_rebid_flag = '|| x_rebid_flag ||
785 ', x_prev_bid_disq = '|| x_prev_bid_disq ||
786 ', x_prev_bid_wthd = '|| x_prev_bid_wthd ||
787 ', x_bid_number = '|| x_bid_number);
788
789 RETURN;
790 END IF; --End if p_action_code = 'CREATE_NEW_DRAFT'
791
792 /* p_draft_number is not null, then lock the draft if it is not locked already.
793 * If draft is locked by some other user, then throw error message.
794 * check handle_proxy method.
795 */
796 IF p_draft_number IS NOT NULL THEN
797
798 lock_draft_bid(p_auc_header_id,
799 p_draft_number,
800 p_buyer_user,
801 p_auctpid,
802 p_auctpcid,
803 p_tpid,
804 p_evaluator_id,
805 p_tpcid,
806 p_vensid,
807 p_eval_flag,
808 x_rebid_flag,
809 x_prev_bid_number,
810 x_bid_number,
811 x_amend_bid_def,
812 x_round_bid_def,
813 x_prev_bid_disq,
814 x_prev_bid_wthd,
815 x_withdraw_reason,
816 x_edit_draft,
817 x_return_status,
818 x_return_code);
819
820 log_message ('check_and_load_bid', 'after lock_draft_bid : x_return_status = ' || x_return_status ||
821 ', x_return_code = ' || x_return_code ||
822 ', x_rebid_flag = '|| x_rebid_flag ||
823 ', x_bid_number = '|| x_bid_number ||
824 ', x_amend_bid_def = '|| x_amend_bid_def ||
825 ', x_round_bid_def = '|| x_round_bid_def ||
826 ', x_prev_bid_disq = '|| x_prev_bid_disq ||
827 ', x_prev_bid_wthd = '|| x_prev_bid_wthd ||
828 ', x_withdraw_reason = '|| x_withdraw_reason ||
829 ', x_edit_draft = '|| x_edit_draft);
830
831 IF (x_return_status = 1) THEN
832 RETURN;
833 END IF;
834
835 END IF; -- p_draft_number IS NOT NULL
836
837 /* If p_rev_bid_number is not null then, this bid is for defaulting.
838 * Before defaulting, if this bid is on the current document :
839 check if draft bid already exists with this bid as old_bid_number.
840 If draft bid already exists, lock and open the draft bid.
841
842 If no draft bid exists with this bid as old_bid_number, then
843 create new draft by defaulting this bid.
844
845 * Before defaulting, if this bid is on the old document :
846 If this is draft bid : create a new draft defaulting this bid.
847 Mark this bid as ARCHIVED_DRAFT.
848
849 If this is not a draft bid :
850 check if any draft bid exists on the current
851 document with this bid as old_bid_number. If draft exists,
852 lock and open the draft for update.
853
854 If no draft exists, create a new draft by defaulting this bid.
855 */
856 IF p_rev_bid_number IS NOT NULL THEN
857
858 --insert_into_vhk_dummy('p_rev_bid_number is not null, calling check_draft_exists');
859
860 log_message ('check_and_load_bid', 'before check_draft_exists : p_rev_bid_number = ' || p_rev_bid_number);
861
862 check_draft_exists(p_auc_header_id, p_rev_bid_number, x_bid_number);
863
864 log_message ('check_and_load_bid', 'after check_draft_exists : x_bid_number = ' || x_bid_number);
865
866 --insert_into_vhk_dummy('Draft Bid Num : '||x_bid_number);
867
868 IF x_bid_number IS NOT NULL THEN
869
870 --insert_into_vhk_dummy('Calling procedure lock_draft_bid');
871
872 log_message ('check_and_load_bid', 'Draft Bid already created and hence locking the draft.');
873
874 l_existing_draft_bid := x_bid_number;
875 --Draft Bid already exists with this as old_bid_number.
876 lock_draft_bid(p_auc_header_id,
877 l_existing_draft_bid,
878 p_buyer_user,
879 p_auctpid,
880 p_auctpcid,
881 p_tpid,
882 p_evaluator_id,
883 p_tpcid,
884 p_vensid,
885 p_eval_flag,
886 x_rebid_flag,
887 x_prev_bid_number,
888 x_bid_number,
889 x_amend_bid_def,
890 x_round_bid_def,
891 x_prev_bid_disq,
892 x_prev_bid_wthd,
893 x_withdraw_reason,
894 x_edit_draft,
895 x_return_status,
896 x_return_code);
897
898 log_message ('check_and_load_bid', 'after lock_draft_bid : x_return_status = ' || x_return_status ||
899 ', x_return_code = ' || x_return_code ||
900 ', x_rebid_flag = '|| x_rebid_flag ||
901 ', x_bid_number = '|| x_bid_number ||
902 ', x_amend_bid_def = '|| x_amend_bid_def ||
903 ', x_round_bid_def = '|| x_round_bid_def ||
904 ', x_prev_bid_disq = '|| x_prev_bid_disq ||
905 ', x_prev_bid_wthd = '|| x_prev_bid_wthd ||
906 ', x_withdraw_reason = '|| x_withdraw_reason ||
907 ', x_edit_draft = '|| x_edit_draft);
908
909 --insert_into_vhk_dummy('After procedure lock_draft_bid');
910
911 RETURN;
912 ELSE
913 log_message ('check_and_load_bid', 'Draft Bid not created, call procedure get_source_bid_details');
914
915 -- There is no draft bid existing, we need to create a new bid by defaulting p_rev_bid_number.
916 get_source_bid_details(p_auc_header_id,
917 p_rev_bid_number,
918 x_rebid_flag,
919 x_amend_bid_def,
920 x_round_bid_def,
921 x_prev_bid_disq,
922 x_prev_bid_wthd,
923 x_withdraw_reason);
924
925 log_message ('check_and_load_bid', 'after get_source_bid_details.' ||
926 ' x_rebid_flag = ' || x_rebid_flag ||
927 ', x_amend_bid_def = ' || x_amend_bid_def ||
928 ', x_round_bid_def = ' || x_round_bid_def ||
929 ', x_prev_bid_disq = ' || x_prev_bid_disq ||
930 ', x_amend_bid_def = ' || x_amend_bid_def ||
931 ', x_prev_bid_wthd = ' || x_prev_bid_wthd ||
932 ', x_withdraw_reason = ' || x_withdraw_reason);
933
934 IF (x_amend_bid_def = 'Y' OR x_round_bid_def = 'Y') THEN
935 l_new_round_or_amended := 'Y';
936 ELSE
937 l_new_round_or_amended := 'N';
938 END IF;
939 -- Create the new bid
940 x_prev_bid_number := p_rev_bid_number;
941
942 log_message ('check_and_load_bid', 'Create new draft bid by defaulting bid : '||p_rev_bid_number);
943
944 pon_bid_defaulting_pkg.create_new_draft_bid
945 (p_auc_header_id,
946 p_rev_bid_number,
947 p_tpid,
948 p_tpcid,
949 p_tpname,
950 p_tpcname,
951 p_userid,
952 p_venid,
953 p_vensid,
954 p_venscode,
955 p_auctpid,
956 p_auctpcid,
957 p_buyer_user,
958 ---- Supplier Management: Supplier Evaluation ----
959 p_evaluator_id,
960 p_eval_flag,
961 --------------------------------------------------
962 l_new_round_or_amended,
963 x_rebid_flag,
964 x_prev_bid_disq,
965 x_prev_bid_wthd,
966 x_bid_number,
967 x_return_status,
968 x_return_code);
969
970 log_message ('check_and_load_bid', 'after pon_bid_defaulting_pkg.create_new_draft_bid : x_return_status = ' || x_return_status ||
971 ', x_return_code = ' || x_return_code ||
972 ', l_new_round_or_amended = '|| l_new_round_or_amended ||
973 ', x_rebid_flag = '|| x_rebid_flag ||
974 ', x_prev_bid_disq = '|| x_prev_bid_disq ||
975 ', x_prev_bid_wthd = '|| x_prev_bid_wthd ||
976 ', x_bid_number = '|| x_bid_number);
977
978 RETURN;
979
980 END IF; -- x_bid_number IS NOT NULL
981 END IF; -- p_rev_bid_number IS NOT NULL
982
983 END check_and_load_bid;
984
985
986 END PON_MULTIPLE_RESPONSE_PKG;