[Home] [Help]
PACKAGE BODY: APPS.PON_RESPONSE_PVT
Source
1 PACKAGE BODY PON_RESPONSE_PVT AS
2 -- $Header: PONRESPB.pls 120.45.12020000.2 2013/02/09 10:22:40 hvutukur ship $
3
4
5 g_return_success CONSTANT NUMBER := 0;
6 g_return_error CONSTANT NUMBER := 1;
7 g_return_warning CONSTANT NUMBER := 2;
8
9 g_price_breaks CONSTANT VARCHAR2(30) := 'PRICE_BREAKS';
10 g_quantity_based CONSTANT VARCHAR2(30) := 'QUANTITY_BASED';
11
12 --
13 -- LOGGING FEATURE
14 --
15 -- global variables used for logging
16 --
17 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
18 g_pkg_name CONSTANT VARCHAR2(50) := 'pon_response_pvt';
19 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.' || g_pkg_name || '.';
20 --
21 --private helper procedure for logging
22 PROCEDURE print_log(p_module IN VARCHAR2,
23 p_message IN VARCHAR2)
24 IS
25 BEGIN
26
27 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
28 FND_LOG.string(log_level => FND_LOG.level_statement,
29 module => g_module_prefix || p_module,
30 message => p_message);
31 END IF;
32 END;
33
34
35
36 -- -------------------------------------------------------------------------
37 -- get_header_close_bidding_date
38 -- get_line_close_bidding_date
39 --
40 -- called from BidHeaderVO query, this function will take bid_number and
41 -- auction_header_id and return the adjusted close bidding date, which
42 -- takes into account the pause duration in case the auction was
43 -- paused
44 -- -------------------------------------------------------------------------
45
46 FUNCTION get_header_close_bidding_date
47 (p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE)
48 RETURN DATE
49 IS
50 v_auc_close_bidding_date pon_auction_headers_all.close_bidding_date%TYPE;
51 v_pause_date pon_auction_headers_all.last_pause_date%TYPE;
52 v_is_paused pon_auction_headers_all.is_paused%TYPE;
53
54 l_api_name CONSTANT VARCHAR2(30) := 'get_header_close_bidding_date';
55
56 BEGIN
57
58 SELECT a.close_bidding_date, a.last_pause_date, a.is_paused
59 INTO v_auc_close_bidding_date, v_pause_date, v_is_paused
60 FROM pon_auction_headers_all a
61 WHERE a.auction_header_id = p_auction_header_id;
62
63 IF v_is_paused = 'Y' THEN
64 RETURN (sysdate + (v_auc_close_bidding_date - v_pause_date));
65 ELSE
66 RETURN v_auc_close_bidding_date;
67 END IF;
68
69 END get_header_close_bidding_date;
70
71 FUNCTION get_line_close_bidding_date
72 (p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
73 p_line_number IN pon_bid_item_prices.line_number%TYPE,
74 p_is_paused IN pon_auction_headers_all.is_paused%TYPE,
75 p_pause_date IN pon_auction_headers_all.last_pause_date%TYPE)
76 RETURN DATE
77 IS
78 v_line_close_bidding_date pon_auction_item_prices_all.close_bidding_date%TYPE;
79 l_api_name CONSTANT VARCHAR2(30) := 'get_line_close_bidding_date';
80
81 BEGIN
82
83 SELECT a.close_bidding_date
84 INTO v_line_close_bidding_date
85 FROM pon_auction_item_prices_all a
86 WHERE a.auction_header_id = p_auction_header_id
87 AND a.line_number = p_line_number;
88
89 IF p_is_paused = 'Y' AND v_line_close_bidding_date > p_pause_date THEN
90 RETURN (sysdate + (v_line_close_bidding_date - p_pause_date));
91 ELSE
92 RETURN v_line_close_bidding_date;
93 END IF;
94
95 END get_line_close_bidding_date;
96
97
98
99 -- -------------------------------------------------------------------------
100 -- calculate_group_amounts
101 --
102 -- called from BidItemPricesEO, this function will calculate the group amount.
103 -- we have two variations of this procedure:
104 -- 1) calculate group amount for the given line
105 -- 2) calculate group amount for all lines in the given bid
106 -- -------------------------------------------------------------------------
107 PROCEDURE calculate_group_amounts(p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
108 p_is_supplier IN VARCHAR)
109 IS
110 BEGIN
111 calculate_group_amounts(p_bid_number, p_is_supplier, 'Y', -1);
112 END calculate_group_amounts;
113
114 PROCEDURE calculate_group_amounts(p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
115 p_is_supplier IN VARCHAR,
116 p_do_all_lines IN VARCHAR,
117 p_batch_id IN pon_bid_item_prices.batch_id%TYPE)
118 IS
119
120 l_api_name CONSTANT VARCHAR2(30) := 'calculate_group_amounts';
121 v_supplier_view_type pon_auction_headers_all.supplier_view_type%TYPE;
122 v_contract_type pon_auction_headers_all.contract_type%TYPE;
123 v_doctype VARCHAR2(7);
124
125 BEGIN
126
127 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
128 print_log(l_api_name, p_bid_number || ' - BEGIN calculate_group_amounts');
129 print_log(l_api_name, p_bid_number || ' - p_is_supplier='||p_is_supplier);
130 print_log(l_api_name, p_bid_number || ' - p_do_all_lines='||p_do_all_lines);
131 print_log(l_api_name, p_bid_number || ' - p_batch_id='||p_batch_id);
132 END IF;
133
134 -- select some variables we need
135 SELECT paha.supplier_view_type,
136 paha.contract_type,
137 DECODE(paha.doctype_id, 21, 'RFI', 5, 'RFQ', 1, 'AUCTION') document_type
138 INTO v_supplier_view_type,
139 v_contract_type,
140 v_doctype
141 FROM pon_bid_headers pbh,
142 pon_auction_headers_all paha
143 WHERE pbh.bid_number = p_bid_number
144 AND paha.auction_header_id = pbh.auction_header_id;
145
146 -- if the negotiation is an RFI or is UNtransformed, then
147 -- we do not allow group amounts to be calculated.
148 -- simply return.
149 IF (v_supplier_view_type = 'UNTRANSFORMED' OR
150 v_doctype = 'RFI') THEN
151 RETURN;
152 END IF;
153
154 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
155 print_log(l_api_name, p_bid_number || ' - v_supplier_view_type=' || v_supplier_view_type);
156 print_log(l_api_name, p_bid_number || ' - v_doctype=' || v_doctype);
157 print_log(l_api_name, p_bid_number || ' - v_contract_type=' || v_contract_type);
158 END IF;
159
160 UPDATE pon_bid_item_prices p1
161 SET p1.group_amount =
162 (SELECT SUM(decode(p_is_supplier,
163 'Y', p2.bid_currency_price,
164 p2.price) *
165 decode(a1.order_type_lookup_code,
166 'FIXED PRICE', 1,
167 decode(v_contract_type,
168 'STANDARD', p2.quantity,
169 nvl(p2.quantity, a1.quantity))))
170 FROM pon_bid_item_prices p2,
171 pon_auction_item_prices_all a1
172 WHERE p2.bid_number = p_bid_number
173 AND a1.auction_header_id = p2.auction_header_id
174 AND p2.line_number = a1.line_number
175 AND a1.parent_line_number = p1.line_number)
176 WHERE p1.bid_number = p_bid_number
177 AND (p_do_all_lines = 'Y'
178 OR p1.batch_id = p_batch_id)
179 AND (SELECT a2.group_type
180 FROM pon_auction_item_prices_all a2
181 WHERE a2.auction_header_id = p1.auction_header_id
182 AND a2.line_number = p1.line_number) = 'GROUP';
183
184 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
185 print_log(l_api_name, p_bid_number || ' - END calculate_group_amounts');
186 END IF;
187
188 END calculate_group_amounts;
189
190 PROCEDURE calculate_group_amounts(p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
191 p_line_number IN pon_bid_item_prices.line_number%TYPE,
192 p_is_supplier IN VARCHAR,
193 p_group_amount OUT NOCOPY NUMBER)
194 IS
195
196 l_api_name CONSTANT VARCHAR2(50) := 'calculate_group_amounts (single-line)';
197 v_supplier_view_type pon_auction_headers_all.supplier_view_type%TYPE;
198 v_contract_type pon_auction_headers_all.contract_type%TYPE;
199 v_doctype VARCHAR2(7);
200
201 BEGIN
202
203 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
204 print_log(l_api_name, p_bid_number || ' - BEGIN calculate_group_amounts');
205 print_log(l_api_name, p_bid_number || ' - p_line_number='||p_line_number);
206 print_log(l_api_name, p_bid_number || ' - p_is_supplier='||p_is_supplier);
207 END IF;
208
209 -- select some variables we need
210 SELECT paha.supplier_view_type,
211 paha.contract_type,
212 DECODE(paha.doctype_id, 21, 'RFI', 5, 'RFQ', 1, 'AUCTION')
213 INTO v_supplier_view_type,
214 v_contract_type,
215 v_doctype
216 FROM pon_bid_headers pbh,
217 pon_auction_headers_all paha
218 WHERE pbh.bid_number = p_bid_number
219 AND paha.auction_header_id = pbh.auction_header_id;
220
221 -- if the negotiation is an RFI or is UNtransformed, then
222 -- we do not allow group amounts to be calculated.
223 -- simply return.
224 IF (v_supplier_view_type = 'UNTRANSFORMED' OR
225 v_doctype = 'RFI') THEN
226 RETURN;
227 END IF;
228
229 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
230 print_log(l_api_name, p_bid_number || ' - v_supplier_view_type=' || v_supplier_view_type);
231 print_log(l_api_name, p_bid_number || ' - v_doctype=' || v_doctype);
232 print_log(l_api_name, p_bid_number || ' - v_contract_type=' || v_contract_type);
233 END IF;
234
235
236
237
238 SELECT SUM(decode(p_is_supplier,
239 'Y', group_line.bid_currency_price,
240 group_line.price) *
241 decode(auc_line.order_type_lookup_code,
242 'FIXED PRICE', 1,
243 decode(v_contract_type,
244 'STANDARD', group_line.quantity,
245 nvl(group_line.quantity, auc_line.quantity))))
246 INTO p_group_amount
247 FROM pon_bid_item_prices groups,
248 pon_bid_item_prices group_line,
249 pon_auction_item_prices_all auc_line
250 WHERE groups.bid_number = p_bid_number
251 AND group_line.bid_number = groups.bid_number
252 AND groups.line_number = p_line_number
253 AND group_line.auction_header_id = auc_line.auction_header_id
254 AND group_line.line_number = auc_line.line_number
255 AND auc_line.parent_line_number = groups.line_number;
256
257 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
258 print_log(l_api_name, p_bid_number || ' - END calculate_group_amounts');
259 END IF;
260
261 END calculate_group_amounts;
262
263 PROCEDURE calculate_group_amounts_auto(p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
264 p_is_supplier IN VARCHAR)
265 IS
266 PRAGMA AUTONOMOUS_TRANSACTION;
267
268 l_api_name CONSTANT VARCHAR2(30) := 'calculate_group_amounts_auto';
269
270 BEGIN
271
272 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
273 print_log(l_api_name, p_bid_number || ' - BEGIN calculate_group_amount AUTONOMOUS');
274 END IF;
275
276 calculate_group_amounts(p_bid_number, p_is_supplier);
277 commit;
278
279 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
280 print_log(l_api_name, p_bid_number || ' - calculate_group_amount: committed!');
281 print_log(l_api_name, p_bid_number || ' - END calculate_group_amount AUTONOMOUS');
282 END IF;
283 END calculate_group_amounts_auto;
284
285
286 -- -------------------------------------------------------------------------
287 -- change_bid_by_percentage
288 --
289 -- called from ResponseAMImpl's handler for power bidding
290 -- (changeBidByPercentage), this method will perform power bidding on the
291 -- specified bid by the specified percentage.
292 -- -------------------------------------------------------------------------
293
294 PROCEDURE change_bid_by_percentage
295 (p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
296 p_power_percentage IN NUMBER,
297 p_powerbidlosinglines IN VARCHAR2,
298 p_previous_bid_number IN pon_bid_headers.old_bid_number%TYPE)
299 IS
300
301 v_precision NUMBER;
302 v_rate NUMBER;
303 v_is_paused VARCHAR2(1);
304 v_paused_date DATE;
305 v_surrog_bid_flag VARCHAR2(1);
306 v_auction_header_id pon_auction_headers_all.auction_header_id%TYPE;
307 v_is_blanket VARCHAR2(1);
308 v_source_bid pon_bid_headers.old_bid_number%TYPE;
309 v_price_tiers_indicator PON_AUCTION_HEADERS_ALL.price_tiers_indicator%TYPE;
310
311 v_batch_start NUMBER;
312 v_batch_end NUMBER;
313 v_batch_size NUMBER;
314 v_max_line_number NUMBER;
315
316 l_api_name CONSTANT VARCHAR2(30) := 'change_bid_by_percentage';
317
318 BEGIN
319
320 SELECT number_price_decimals, rate, auction_header_id, surrog_bid_flag, old_bid_number
321 INTO v_precision, v_rate, v_auction_header_id, v_surrog_bid_flag, v_source_bid
322 FROM pon_bid_headers
323 WHERE bid_number=p_bid_number;
324
325 SELECT a.is_paused, a.last_pause_date, a.max_internal_line_num,
326 decode(a.contract_type, 'BLANKET', 'Y', 'CONTRACT', 'Y', 'N'),
327 a.price_tiers_indicator
328 INTO v_is_paused, v_paused_date, v_max_line_number,
329 v_is_blanket,v_price_tiers_indicator
330 FROM pon_auction_headers_all a
331 WHERE a.auction_header_id = v_auction_header_id;
332
333 v_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
334 v_batch_start := 1;
335 IF (v_max_line_number < v_batch_size) THEN
336 v_batch_end := v_max_line_number;
337 ELSE
338 v_batch_end := v_batch_size;
339 END IF;
340
341 WHILE (v_batch_start <= v_max_line_number) LOOP
342
343 UPDATE pon_bid_item_prices pbip
344 SET pbip.bid_currency_price = round(pbip.bid_currency_price * (1 - p_power_percentage / 100), v_precision),
345 pbip.bid_currency_unit_price = round(pbip.bid_currency_price * (1 - p_power_percentage / 100), v_precision),
346 pbip.bid_currency_trans_price = round(pbip.bid_currency_price * (1 - p_power_percentage / 100), v_precision),
347 pbip.price = (round(pbip.bid_currency_price * (1 - p_power_percentage / 100), v_precision)) / v_rate,
348 pbip.unit_price = (round(pbip.bid_currency_price * (1 - p_power_percentage / 100), v_precision)) / v_rate
349 WHERE pbip.bid_number = p_bid_number
350 AND (get_line_close_bidding_date(pbip.auction_header_id, pbip.line_number, v_is_paused, v_paused_date) > sysdate
351 OR v_surrog_bid_flag = 'Y')
352 AND pbip.copy_price_for_proxy_flag = 'N'
353 AND ((nvl(p_powerbidlosinglines,'N') = 'Y'
354 AND pbip.line_number IN (SELECT line_number
355 FROM PON_AUCTION_ITEM_PRICES_ALL paip,
356 PON_AUCTION_HEADERS_ALL pah
357 WHERE pah.auction_header_id = paip.auction_header_id
358 AND DECODE(pah.bid_ranking, 'MULTI_ATTRIBUTE_SCORING',paip.best_bid_bid_number,paip.best_bid_number) <> p_previous_bid_number
359 AND paip.auction_header_id = pbip.auction_header_id
360 AND paip.line_number = pbip.line_number))
361 OR
362 (nvl(p_powerbidlosinglines,'N') = 'N'))
363 AND pbip.line_number >= v_batch_start
364 AND pbip.line_number <= v_batch_end ;
365
366 -- if price tiers indicator is not equal to either price breaks
367 -- or price tiers we don't have to proceed with the shipments
368 IF(v_price_tiers_indicator = g_price_breaks or v_price_tiers_indicator = g_quantity_based) THEN
369
370 UPDATE pon_bid_shipments pbs
371 SET pbs.bid_currency_unit_price =
372 DECODE(pbs.price_type,
373 'PRICE', round(pbs.bid_currency_price * (1 - p_power_percentage/100), v_precision),
374 round((SELECT pbip.bid_currency_unit_price
375 FROM pon_bid_item_prices pbip
376 WHERE pbip.bid_number = pbs.bid_number
377 AND pbip.line_number = pbs.line_number) *
378 (1 - pbs.price_discount/100), v_precision))
379 WHERE pbs.bid_number = p_bid_number
380 AND pbs.line_number IN (SELECT pbip.line_number
381 FROM pon_bid_item_prices pbip
382 WHERE pbip.bid_number = pbs.bid_number
383 AND ((get_line_close_bidding_date(pbip.auction_header_id, pbip.line_number, v_is_paused, v_paused_date) > sysdate) OR (v_surrog_bid_flag = 'Y'))
384 AND pbip.copy_price_for_proxy_flag = 'N')
385 AND ((nvl(p_powerbidlosinglines,'N') = 'Y'
386 AND pbs.line_number IN (SELECT line_number
387 FROM PON_AUCTION_ITEM_PRICES_ALL paip,
388 PON_AUCTION_HEADERS_ALL pah
389 WHERE pah.auction_header_id = paip.auction_header_id
390 AND DECODE(pah.bid_ranking, 'MULTI_ATTRIBUTE_SCORING',paip.best_bid_bid_number,paip.best_bid_number) <> p_previous_bid_number
391 AND paip.auction_header_id = pbs.auction_header_id
392 AND paip.line_number = pbs.line_number))
393 OR
394 (nvl(p_powerbidlosinglines,'N') = 'N'))
395 AND pbs.line_number >= v_batch_start
396 AND pbs.line_number <= v_batch_end;
397 -- AND pbs.shipment_type = 'PRICE BREAK';
398
399 UPDATE pon_bid_shipments pbs
400 SET pbs.bid_currency_price = pbs.bid_currency_unit_price,
401 pbs.unit_price = pbs.bid_currency_unit_price / v_rate,
402 pbs.price = pbs.bid_currency_unit_price / v_rate
403 WHERE pbs.bid_number = p_bid_number
404 AND pbs.line_number IN (SELECT pbip.line_number
405 FROM pon_bid_item_prices pbip
406 WHERE pbip.bid_number = pbs.bid_number
407 AND ((get_line_close_bidding_date(pbip.auction_header_id, pbip.line_number, v_is_paused, v_paused_date) > sysdate) OR (v_surrog_bid_flag = 'Y'))
408 AND pbip.copy_price_for_proxy_flag = 'N')
409 AND ((nvl(p_powerbidlosinglines,'N') = 'Y'
410 AND pbs.line_number IN (SELECT line_number
411 FROM PON_AUCTION_ITEM_PRICES_ALL paip,
412 PON_AUCTION_HEADERS_ALL pah
413 WHERE pah.auction_header_id = paip.auction_header_id
414 AND DECODE(pah.bid_ranking, 'MULTI_ATTRIBUTE_SCORING',paip.best_bid_bid_number,paip.best_bid_number) <> p_previous_bid_number
415 AND paip.auction_header_id = pbs.auction_header_id
416 AND paip.line_number = pbs.line_number))
417 OR
418 (nvl(p_powerbidlosinglines,'N') = 'N'))
419 AND pbs.line_number >= v_batch_start
420 AND pbs.line_number <= v_batch_end;
421 -- AND pbs.shipment_type = 'PRICE BREAK';
422
423 END IF;
424
425 PON_BID_VALIDATIONS_PKG.populate_has_bid_changed_line(
426 v_auction_header_id,
427 p_bid_number,
428 v_source_bid,
429 v_batch_start,
430 v_batch_end,
431 'Y',
432 v_is_blanket,
433 'N');
434
435 v_batch_start := v_batch_end + 1;
436 IF (v_batch_end + v_batch_size > v_max_line_number) THEN
437 v_batch_end := v_max_line_number;
438 ELSE
439 v_batch_end := v_batch_end + v_batch_size;
440 END IF;
441
442 COMMIT;
443 END LOOP;
444
445 END change_bid_by_percentage;
446
447
448
449 -- -------------------------------------------------------------------------
450 -- recalculate_auction_currency_prices
451 --
452 -- called from ResponseAMImpl.recalculateAuctionCurrencyPrices, this
453 -- procedure will recalculate the auction-currency bid prices for all
454 -- lines and children
455 -- -------------------------------------------------------------------------
456
457 PROCEDURE recalculate_auc_curr_prices
458 (
459 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
460 p_curr_changed IN VARCHAR2,
461 p_batch_id IN pon_bid_item_prices.batch_id%TYPE
462 )
463 IS
464
465 v_rate NUMBER;
466 v_precision NUMBER;
467 v_fnd_precision NUMBER;
468 v_display_price_factors_flag pon_bid_item_prices.display_price_factors_flag%TYPE;
469 v_supplier_view_type pon_auction_headers_all.supplier_view_type%TYPE;
470 v_contract_type pon_auction_headers_all.contract_type%TYPE;
471 v_is_spo_transformed VARCHAR(1);
472 v_auction_header_id NUMBER;
473 v_progress_payment_type pon_auction_headers_all.progress_payment_type%TYPE;
474 v_advance_negotiable PON_AUCTION_HEADERS_ALL.ADVANCE_NEGOTIABLE_FLAG%TYPE;
475 v_max_rtng_negotiable PON_AUCTION_HEADERS_ALL.MAX_RETAINAGE_NEGOTIABLE_FLAG%TYPE;
476 v_batch_start NUMBER;
477 v_batch_end NUMBER;
478 v_batch_size NUMBER;
479 v_max_line_number NUMBER;
480 l_api_name CONSTANT VARCHAR2(30) := 'recalculate_auc_curr_prices';
481
482 BEGIN
483
484 -- select some variables we need
485 SELECT pbh.rate,
486 pbh.number_price_decimals,
487 fnd.precision,
488 pbh.display_price_factors_flag,
489 paha.supplier_view_type,
490 paha.contract_type,
491 paha.auction_header_id,
492 paha.max_internal_line_num,
493 nvl(paha.progress_payment_type,'NONE'),
494 nvl(paha.ADVANCE_NEGOTIABLE_FLAG,'N'),
495 nvl(paha.MAX_RETAINAGE_NEGOTIABLE_FLAG,'N')
496
497 INTO v_rate,
498 v_precision,
499 v_fnd_precision,
500 v_display_price_factors_flag,
501 v_supplier_view_type,
502 v_contract_type,
503 v_auction_header_id,
504 v_max_line_number,
505 v_progress_payment_type,
506 v_advance_negotiable,
507 v_max_rtng_negotiable
508 FROM pon_bid_headers pbh,
509 fnd_currencies fnd,
510 pon_auction_headers_all paha
511 WHERE pbh.bid_number = p_bid_number
512 AND paha.auction_header_id = pbh.auction_header_id
513 AND fnd.currency_code = pbh.bid_currency_code;
514
515 v_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
516 v_batch_start := 1;
517 IF (v_max_line_number < v_batch_size) THEN
518 v_batch_end := v_max_line_number;
519 ELSE
520 v_batch_end := v_batch_size;
521 END IF;
522
523 -- If we are recalculating auction currency prices for a subset
524 -- of the lines (only those lines with p_batch_id) then batching
525 -- this procedure is redundant so set the batch_end as the
526 -- max_line_number so that the loop is only executed once
527 IF (p_curr_changed = 'N') THEN
528 v_batch_end := v_max_line_number;
529 END IF;
530
531 WHILE (v_batch_start <= v_max_line_number) LOOP
532
533 -- RECALCULATE SPF/BPF VALUES
534 -- All BPF and SPF values that are stored in the pon_bid_price_elements
535 -- table need to be recalculated. There are two parts to this task:
536 -- (1) recalculate BPF values from auction-currency prices
537 -- to bid-currency
538 -- (2) recalculate SPF values from bid-currency prices to
539 -- auction-currency
540
541 -- (1) For BPF values in the pon_bid_price_elements table,
542 -- we need to recalculate the bid-currency values from
543 -- the auction-currency values.
544
545 -- NOTE: does not apply for spreadsheet upload, only if currency_changed
546 IF ((v_display_price_factors_flag = 'Y' OR v_supplier_view_type = 'UNTRANSFORMED')
547 AND p_curr_changed = 'Y') THEN
548
549 -- recalculate bid-currency BPF values from auction-currency values.
550 -- Bid-currency BPF value should be rounded.
551 UPDATE pon_bid_price_elements pbpe
552 SET pbpe.bid_currency_value =
553 DECODE(pbpe.pricing_basis,
554 'PER_UNIT', round(pbpe.auction_currency_value*v_rate, v_precision),
555 'FIXED_AMOUNT', round(pbpe.auction_currency_value*v_rate, v_fnd_precision),
556 pbpe.auction_currency_value)
557 WHERE pbpe.bid_number = p_bid_number
558 AND pbpe.pf_type = 'BUYER'
559 AND pbpe.line_number >= v_batch_start
560 AND pbpe.line_number <= v_batch_end;
561 END IF;
562
563
564 -- (2) For the SPF values, recalculate auction_currency values from
565 -- bid-currency SPF values. The auction-currency SPF value should not
566 -- be rounded.
567
568 IF(v_display_price_factors_flag = 'Y') THEN
569
570 UPDATE pon_bid_price_elements pbpe
571 SET pbpe.auction_currency_value = pbpe.bid_currency_value / decode(pbpe.pricing_basis, 'PERCENTAGE', 1, v_rate)
572 WHERE pbpe.bid_number = p_bid_number
573 AND pbpe.pf_type = 'SUPPLIER'
574 -- process only batch, or all SUPPLIER price elements if currency changed
575 AND ((p_curr_changed = 'Y' AND
576 pbpe.line_number >= v_batch_start AND
577 pbpe.line_number <= v_batch_end)
578 OR pbpe.batch_id = p_batch_id);
579 END IF;
580
581
582 -- RECALCULATE LINE-LEVEL PRICES
583 -- there are two steps to recalculating auction-currency transformed
584 -- bid price:
585 -- (1) apply BPF values by using pon_pf_supplier_formula
586 -- (2) apply SPF values by summing up all applicable SPF values from
587 -- pon_bid_price_elements table and adding to the result of (1)
588
589 -- first, calculate v_is_spo_transformed flag
590 IF (v_supplier_view_type = 'TRANSFORMED' AND
591 v_contract_type = 'STANDARD') THEN
592 v_is_spo_transformed := 'Y';
593 ELSE
594 v_is_spo_transformed := 'N';
595 END IF;
596
597 -- (1) apply BPF formula
598 UPDATE pon_bid_item_prices pbip
599 SET pbip.bid_currency_trans_price = nvl(
600 (SELECT (pbip.bid_currency_unit_price * ppsf.percentage) +
601 ppsf.unit_price*v_rate +
602 (ppsf.fixed_amount*v_rate / decode(v_is_spo_transformed,
603 'Y', nvl(pbip.quantity, 1),
604 nvl(aip.quantity, 1)))
605 FROM pon_pf_supplier_formula ppsf,
606 pon_bid_headers pbh,
607 pon_auction_item_prices_all aip
608 WHERE ppsf.auction_header_id = pbip.auction_header_id
609 AND ppsf.line_number = pbip.line_number
610 AND pbip.bid_number = pbh.bid_number
611 AND ppsf.trading_partner_id = pbh.trading_partner_id
612 AND ppsf.vendor_site_id = pbh.vendor_site_id
613 AND aip.auction_header_id = pbip.auction_header_id
614 AND aip.line_number = pbip.line_number),
615 pbip.bid_currency_unit_price)
616 WHERE pbip.bid_number = p_bid_number
617 -- process only batch, or all lines if currency change
618 AND ((p_curr_changed = 'Y' AND
619 pbip.line_number >= v_batch_start AND
620 pbip.line_number <= v_batch_end)
621 OR pbip.batch_id = p_batch_id);
622
623 -- (2) apply SPF values
624 UPDATE pon_bid_item_prices pbip
625 SET pbip.bid_currency_trans_price =
626 (SELECT pbip.bid_currency_trans_price +
627 nvl(sum( Decode(Nvl(negative_cost_factor_flag,'N'),'Y',
628 -decode(spf.pricing_basis,
629 'PER_UNIT', spf.bid_currency_value,
630 'PERCENTAGE', spf.bid_currency_value/100 * pbip.bid_currency_unit_price,
631 (spf.bid_currency_value / decode(v_is_spo_transformed,
632 'Y', nvl(pbip.quantity, 1),
633 nvl(aip.quantity, 1)))),
634 decode(spf.pricing_basis,
635 'PER_UNIT', spf.bid_currency_value,
636 'PERCENTAGE', spf.bid_currency_value/100 * pbip.bid_currency_unit_price,
637 (spf.bid_currency_value / decode(v_is_spo_transformed,
638 'Y', nvl(pbip.quantity, 1),
639 nvl(aip.quantity, 1)))))),
640 0)
641 FROM pon_bid_price_elements spf,
642 pon_auction_item_prices_all aip
643 WHERE spf.bid_number = p_bid_number
644 AND spf.line_number = pbip.line_number
645 AND spf.sequence_number <> -10
646 AND aip.auction_header_id = spf.auction_header_id
647 AND aip.line_number = spf.line_number
648 AND spf.pf_type = 'SUPPLIER')
649 WHERE pbip.bid_number = p_bid_number
650 -- process only batch, or all lines if currency change
651 AND ((p_curr_changed = 'Y' AND
652 pbip.line_number >= v_batch_start AND
653 pbip.line_number <= v_batch_end)
654 OR pbip.batch_id = p_batch_id);
655
656 -- once we have the untruncated transformed bid-currency price
657 -- in bid_currency_trans_price column, copy that value over
658 -- to the other columns.
659 -- * unit_price: recalculate from untruncated
660 -- bid_currency_unit_price. (not rounded)
661 -- * bid_currency_unit_price: leave it as-is. (not rounded)
662 -- * price: recalculate from untruncated
663 -- bid_currency_trans_price. (not rounded)
664 -- * bid_currency_trans_price: truncate the untruncated
665 -- bid_currency_trans_price
666 -- * bid_currency_price: TRANSFORMED - bid_currency_trans_price truncated.
667 -- UNTRUNCATED - bid_currency_unit_price untruncated.
668 -- * Complex Work- For details on how bid_currency_advance_amount and Bid_currency_max_retainage
669 -- are calculated please look at ECO#4549930 for details. Here the logic is that bid currency values for
670 -- advance_amount and max_reatinage be converted into Bid currency if these are non negotaible
671 -- or have not been changed from buyer suggested values in negotiation. But if Supplier has touched
672 -- these values, they are left as it is and not converted. CAUTION- The logic for all the 4 complex
673 -- work fields is almost same. So, if you change one, also consider impact on others.
674
675 IF p_curr_changed = 'Y' THEN
676
677 UPDATE pon_bid_item_prices pbip
678 SET pbip.unit_price = pbip.bid_currency_unit_price / v_rate,
679 pbip.price = pbip.bid_currency_trans_price / v_rate,
680 pbip.bid_currency_price = DECODE(v_supplier_view_type,
681 'TRANSFORMED', round(pbip.bid_currency_trans_price, v_precision),
682 'UNTRANSFORMED', pbip.bid_currency_unit_price),
683 pbip.bid_currency_trans_price = round(pbip.bid_currency_trans_price, v_precision),
684 pbip.proxy_bid_limit_price = pbip.bid_currency_limit_price / v_rate,
685 pbip.po_min_rel_amount = pbip.po_bid_min_rel_amount / v_rate,
686 (pbip.bid_curr_advance_amount,
687 pbip.bid_curr_max_retainage_amt,
688 pbip.advance_amount,
689 pbip.max_retainage_amount)
690 = (SELECT nvl2(pbip.bid_curr_advance_amount,
691 decode(v_advance_negotiable, 'Y',
692 decode( pbip.advance_amount-paip.advance_amount, 0, round(pbip.advance_amount* v_rate,v_fnd_precision),pbip.bid_curr_advance_amount),
693 round(pbip.advance_amount* v_rate,v_fnd_precision)
694 ), pbip.bid_curr_advance_amount),
695
696 nvl2(pbip.bid_curr_max_retainage_amt,
697 decode(v_max_rtng_negotiable, 'Y',
698 decode( pbip.max_retainage_amount-paip.max_retainage_amount, 0, round(pbip.max_retainage_amount* v_rate,v_fnd_precision),pbip.bid_curr_max_retainage_amt),
699 round(pbip.max_retainage_amount* v_rate,v_fnd_precision)
700 ), pbip.bid_curr_max_retainage_amt),
701
702 nvl2(pbip.advance_amount,
703 decode(v_advance_negotiable, 'Y',
704 decode( pbip.advance_amount-paip.advance_amount, 0, pbip.advance_amount,pbip.bid_curr_advance_amount/v_rate),
705 pbip.advance_amount
706 ), pbip.advance_amount),
707
708 nvl2(pbip.max_retainage_amount,
709 decode(v_max_rtng_negotiable, 'Y',
710 decode( pbip.max_retainage_amount-paip.max_retainage_amount, 0, pbip.max_retainage_amount,pbip.bid_curr_max_retainage_amt/v_rate),
711 pbip.max_retainage_amount
712 ), pbip.max_retainage_amount)
713 FROM pon_auction_item_prices_all paip
714 WHERE paip.auction_header_id=pbip.auction_header_id
715 AND paip.line_number=pbip.line_number)
716 WHERE pbip.bid_number = p_bid_number
717 -- process only batch, or all lines if currency change
718 AND ((p_curr_changed = 'Y' AND
719 pbip.line_number >= v_batch_start AND
720 pbip.line_number <= v_batch_end)
721 OR pbip.batch_id = p_batch_id);
722 ELSE
723
724 UPDATE pon_bid_item_prices pbip
725 SET pbip.unit_price = pbip.bid_currency_unit_price / v_rate,
726 pbip.price = pbip.bid_currency_trans_price / v_rate,
727 pbip.bid_currency_price = DECODE(v_supplier_view_type,
728 'TRANSFORMED', round(pbip.bid_currency_trans_price, v_precision),
729 'UNTRANSFORMED', pbip.bid_currency_unit_price),
730 pbip.bid_currency_trans_price = round(pbip.bid_currency_trans_price, v_precision),
731 pbip.proxy_bid_limit_price = pbip.bid_currency_limit_price / v_rate,
732 pbip.po_min_rel_amount = pbip.po_bid_min_rel_amount / v_rate,
733 pbip.advance_amount = pbip.bid_curr_advance_amount/ v_rate,
734 pbip.max_retainage_amount = pbip.bid_curr_max_retainage_amt/v_rate
735 WHERE pbip.bid_number = p_bid_number
736 -- process only batch, or all lines if currency change
737 AND ((p_curr_changed = 'Y' AND
738 pbip.line_number >= v_batch_start AND
739 pbip.line_number <= v_batch_end)
740 OR pbip.batch_id = p_batch_id);
741 END IF;
742
743 -- RECALCULATE SHIPMENT-LEVEL PRICES
744 -- there are two steps to recalculating auction-currency transformed
745 -- shipment price:
746 -- (1) apply BPF values by using pon_pf_supplier_formula
747 -- (2) apply SPF values by summing up all applicable SPF values from
748 -- pon_bid_price_elements table and adding to the result of (1)
749
750 -- NOTE: does not apply for spreadsheet upload, only if currency changed
751 --comment this out for price tier: IF (v_contract_type <> 'STANDARD') THEN
752
753 -- (1) apply BPF formula
754 UPDATE pon_bid_shipments pbs
755 SET pbs.bid_currency_price = nvl(
756 (SELECT (pbs.bid_currency_unit_price * ppsf.percentage) +
757 ppsf.unit_price*v_rate +
758 (ppsf.fixed_amount*v_rate / decode(v_is_spo_transformed,
759 'Y', nvl(pbip.quantity, 1),
760 nvl(aip.quantity, 1)))
761 FROM pon_pf_supplier_formula ppsf,
762 pon_bid_headers pbh,
763 pon_auction_item_prices_all aip,
764 pon_bid_item_prices pbip
765 WHERE pbip.bid_number = pbs.bid_number
766 AND pbip.line_number = pbs.line_number
767 AND ppsf.auction_header_id = pbip.auction_header_id
768 AND ppsf.line_number = pbip.line_number
769 AND pbip.bid_number = pbh.bid_number
770 AND ppsf.trading_partner_id = pbh.trading_partner_id
771 AND ppsf.vendor_site_id = pbh.vendor_site_id
772 AND aip.auction_header_id = pbip.auction_header_id
773 AND aip.line_number = pbip.line_number),
774 pbs.bid_currency_unit_price)
775 WHERE pbs.bid_number = p_bid_number
776 AND pbs.line_number >= v_batch_start
777 AND pbs.line_number <= v_batch_end;
778
779
780 -- (2) apply SPF values
781 UPDATE pon_bid_shipments pbs
782 SET pbs.bid_currency_price =
783 (SELECT pbs.bid_currency_price +
784 nvl(sum(decode(spf.pricing_basis,
785 'PER_UNIT', spf.bid_currency_value,
786 'PERCENTAGE', spf.bid_currency_value/100 * pbs.bid_currency_unit_price,
787 (spf.bid_currency_value / decode(v_is_spo_transformed,
788 'Y', nvl(pbip.quantity, 1),
789 nvl(aip.quantity, 1))))),
790 0)
791 FROM pon_bid_price_elements spf,
792 pon_auction_item_prices_all aip,
793 pon_bid_item_prices pbip
794 WHERE pbip.bid_number = pbs.bid_number
795 AND pbip.line_number = pbs.line_number
796 AND spf.bid_number = p_bid_number
797 AND spf.line_number = pbip.line_number
798 AND spf.sequence_number <> -10
799 AND aip.auction_header_id = spf.auction_header_id
800 AND aip.line_number = spf.line_number
801 AND spf.pf_type = 'SUPPLIER')
802 WHERE pbs.bid_number = p_bid_number
803 AND pbs.line_number >= v_batch_start
804 AND pbs.line_number <= v_batch_end;
805
806 -- once we have the untruncated transformed bid-currency price
807 -- in bid_currency_price column, copy that value over
808 -- to the other columns.
809 -- * unit_price: recalculate from untruncated
810 -- bid_currency_unit_price. (not rounded)
811 -- * bid_currency_unit_price: leave it as-is. (not rounded)
812 -- * price: recalculate from untruncated
813 -- bid_currency_price. (not rounded)
814 -- * bid_currency_price: truncate the untruncated
815 -- bid_currency_price
816 UPDATE pon_bid_shipments pbs
817 SET pbs.unit_price = pbs.bid_currency_unit_price / v_rate,
818 pbs.price = pbs.bid_currency_price / v_rate,
819 pbs.bid_currency_price = DECODE(v_supplier_view_type,
820 'TRANSFORMED', round(pbs.bid_currency_price, v_precision),
821 'UNTRANSFORMED', pbs.bid_currency_unit_price)
822 WHERE pbs.bid_number = p_bid_number
823 AND pbs.line_number >= v_batch_start
824 AND pbs.line_number <= v_batch_end;
825
826 --END IF;
827
828 -- RECALCULATE PAYMENT PRICES
829 -- Since Payments values are not tranformed ever, we can directly multiply by rate
830
831 IF (p_curr_changed = 'Y' AND v_contract_type = 'STANDARD' AND v_progress_payment_type <> 'NONE' ) THEN
832
833 UPDATE pon_bid_payments_shipments pbps
834 SET pbps.price = pbps.bid_currency_price / v_rate
835
836 WHERE pbps.bid_number = p_bid_number
837 AND pbps.bid_line_number >= v_batch_start
838 AND pbps.bid_line_number <= v_batch_end;
839
840 END IF;
841
842 v_batch_start := v_batch_end + 1;
843 IF (v_batch_end + v_batch_size > v_max_line_number) THEN
844 v_batch_end := v_max_line_number;
845 ELSE
846 v_batch_end := v_batch_end + v_batch_size;
847 END IF;
848
849 -- If we are recalculating after a currency change then we need
850 -- to commit because the procedure was executed in batches
851 -- In the other case(s), we do NOT want to commit because
852 -- the procedure was executed in a single batch only for lines
853 -- with p_batch_id and the calling procedure will do the commit (or rollback)
854 IF (p_curr_changed = 'Y') THEN
855 COMMIT;
856 END IF;
857
858 END LOOP;
859 END;
860
861 PROCEDURE MARK_UNSOL_INFO_LINES(p_bid_number IN pon_bid_headers.bid_number%TYPE)
862 IS
863 --Unsolicited Lines Project : Mark Unsolicited info lines.
864 Cursor infoCursor Is select auction_header_id,auction_line_number,line_number,group_line_id from pon_bid_item_prices where
865 bid_number = p_bid_number and nvl(clm_info_flag,'N') = 'Y' AND auction_line_number = -1;
866 l_award_flag Varchar2(1);
867 l_bid_line_count Number;
868 BEGIN
869 For infoLine in infoCursor loop
870 if ( infoLine.group_line_id IS NOT null ) THEN
871 --slin info line check parent line
872 select nvl(has_bid_flag,'N') into l_award_flag from pon_bid_item_prices where bid_number = p_bid_number and line_number = infoLine.group_line_id;
873 if ( l_award_flag = 'Y' ) Then
874 update pon_bid_item_prices
875 set has_bid_flag = l_award_flag
876 where bid_number = p_bid_number
877 and line_number = infoLine.line_number;
878 end if;
879 else
880 --info CLIN
881 select count(*) into l_bid_line_count from pon_bid_item_prices where
882 bid_number = p_bid_number and group_line_id = infoLine.line_number and nvl(has_bid_flag,'N') = 'Y';
883
884 if ( l_bid_line_count > 0 ) THEN
885 update pon_bid_item_prices
886 set has_bid_flag = 'Y'
887 where bid_number = p_bid_number
888 and line_number = infoLine.line_number;
889 end if;
890 end if;
891 end loop;
892 END MARK_UNSOL_INFO_LINES;
893
894 /*Unsolicited Lines Project : Populates publish_date,proxy_bid_flag,award_price,
895 * first_bid_price,bid_trading_partner_id for unsolicited lines.
896 */
897 PROCEDURE publish_unsol_lines(p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
898 p_bid_number IN pon_bid_headers.bid_number%TYPE,
899 p_publish_date IN DATE,
900 p_tp_id IN pon_bid_headers.trading_partner_id%TYPE,
901 p_auc_tp_id IN pon_auction_headers_all.trading_partner_id%TYPE,
902 p_surrog_bid_flag IN pon_bid_headers.surrog_bid_flag%TYPE,
903 p_rebid_flag IN VARCHAR)
904 IS
905 l_api_name CONSTANT VARCHAR2(30) := 'publish_unsol_lines';
906 BEGIN
907 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
908 print_log(l_api_name, p_bid_number);
909 END IF;
910
911 IF p_rebid_flag = 'N' THEN
912
913 UPDATE pon_bid_item_prices
914 SET publish_date = p_publish_date,
915 proxy_bid_flag = 'N',
916 award_price = price,
917 first_bid_price = price, --(woojin) do we really need this?
918 bid_trading_partner_id = decode(p_surrog_bid_flag,
919 'Y', p_auc_tp_id, p_tp_id)
920 WHERE bid_number = p_bid_number
921 AND auction_line_number = -1; --Unsolicited Lines Project : Update unsol lines.
922
923 ELSE
924
925 UPDATE pon_bid_item_prices
926 SET publish_date = p_publish_date,
927 proxy_bid_flag = 'N',
928 award_price = price,
929 first_bid_price = nvl(first_bid_price, price), --(woojin) do we really need this?
930 bid_trading_partner_id = decode(p_surrog_bid_flag,
931 'Y', p_auc_tp_id, p_tp_id)
932 WHERE bid_number = p_bid_number
933 AND is_changed_line_flag = 'Y'
934 AND auction_line_number = -1; --Unsolicited Lines Project : Update unsol lines.
935
936 --Added for bug 10169313
937 UPDATE pon_bid_item_prices
938 SET award_price = price
939 WHERE bid_number = p_bid_number
940 AND is_changed_line_flag <> 'Y'
941 AND auction_line_number = -1; --Unsolicited Lines Project : Update unsol lines.
942
943 END IF;
944
945 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
946 print_log(l_api_name, p_bid_number);
947 END IF;
948
949 END publish_unsol_lines;
950
951 -- -------------------------------------------------------------------------
952 -- publish
953 --
954 -- called from BidHeadersEO.publish or the concurrent publish program, this
955 -- procedure will publish the draft
956 -- -------------------------------------------------------------------------
957
958 PROCEDURE publish(p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
959 p_bid_number IN pon_bid_headers.bid_number%TYPE,
960 p_rebid_flag IN VARCHAR,
961 p_publish_date IN pon_bid_headers.publish_date%TYPE,
962 p_tp_id IN pon_bid_headers.trading_partner_id%TYPE,
963 p_tpc_id IN pon_bid_headers.trading_partner_contact_id%TYPE,
964 p_user_id IN NUMBER,
965 p_batch_id IN NUMBER,
966 p_request_id IN NUMBER,
967 p_hdr_validation_failed IN VARCHAR,
968 x_return_status OUT NOCOPY NUMBER,
969 x_return_code OUT NOCOPY VARCHAR)
970 IS
971
972 v_auc_close_bidding_date pon_auction_headers_all.close_bidding_date%TYPE;
973 v_group_enabled_flag pon_auction_headers_all.group_enabled_flag%TYPE;
974 v_is_paused pon_auction_headers_all.is_paused%TYPE;
975 v_surrog_bid_receipt_date pon_bid_headers.surrog_bid_receipt_date%TYPE;
976 v_publish_date DATE;
977 v_auc_tp_id pon_auction_headers_all.trading_partner_id%TYPE;
978 v_two_part_flag pon_auction_headers_all.two_part_flag%TYPE;
979 v_sealed_auction_status pon_auction_headers_all.sealed_auction_status%TYPE;
980 --added by Allen Yang for Surrogate Bid 2008/09/08
981 --------------------------------------------------
982 l_two_part_flag pon_auction_headers_all.TWO_PART_FLAG%TYPE;
983 l_technical_evaluation_status pon_auction_headers_all.TECHNICAL_EVALUATION_STATUS%TYPE;
984 l_surrogate_bid_flag pon_bid_headers.SURROG_BID_FLAG%TYPE;
985 l_submit_stage pon_bid_headers.SUBMIT_STAGE%TYPE;
986 --------------------------------------------------
987 v_vendor_site_id pon_bid_headers.vendor_site_id%TYPE;
988 v_prev_bid_number pon_bid_headers.old_bid_number%TYPE;
989 v_surrog_bid_flag pon_bid_headers.surrog_bid_flag%TYPE;
990 v_evaluation_flag pon_bid_headers.evaluation_flag%TYPE;
991 v_tpc_name pon_bid_headers.trading_partner_contact_name%TYPE;
992
993 v_wf_item_key pon_bidding_parties.wf_item_key%TYPE;
994 v_user_added_to_role VARCHAR2(1);
995 v_price_changed VARCHAR2(1);
996
997 v_subroutine_return_status NUMBER;
998 v_subroutine_return_code VARCHAR2(30);
999
1000 v_biz_return_status VARCHAR2(240);
1001 v_biz_msg_count NUMBER;
1002 v_biz_msg_data_value VARCHAR2(240);
1003
1004 --batching-related variables
1005 v_maxLineNumber NUMBER;
1006 v_batchSize NUMBER;
1007 v_batchingRequired BOOLEAN;
1008
1009 --Unsolicited Lines Project
1010 l_allow_unsol_lines VARCHAR2(1);
1011
1012 l_api_name CONSTANT VARCHAR2(30) := 'publish';
1013
1014 BEGIN
1015
1016 x_return_status := 0;
1017 x_return_code := '';
1018 v_price_changed := 'N';
1019 v_user_added_to_role := 'Y';
1020 v_subroutine_return_status := 0;
1021 v_subroutine_return_code := '';
1022 v_is_paused := 'N';
1023
1024 --added by Allen Yang for Surrogate Bid 2008/09/08
1025 --------------------------------------------------
1026 --get the two-part stage of submitting quote
1027 SELECT
1028 paha.TWO_PART_FLAG
1029 , paha.TECHNICAL_EVALUATION_STATUS
1030 , pbh.SURROG_BID_FLAG
1031 , Nvl(paha.ALLOW_UNSOL_OFFER_LINES,'N') --Unsolicited Lines Project
1032 INTO
1033 l_two_part_flag
1034 , l_technical_evaluation_status
1035 , l_surrogate_bid_flag
1036 , l_allow_unsol_lines --Unsolicited Lines Project
1037 FROM
1038 PON_AUCTION_HEADERS_ALL paha
1039 , PON_BID_HEADERS pbh
1040 WHERE paha.auction_header_id = pbh.auction_header_id
1041 AND pbh.bid_number = p_bid_number;
1042
1043 IF ((nvl(l_two_part_flag, 'N') = 'Y') AND (nvl(l_surrogate_bid_flag, 'N') = 'Y'))
1044 THEN
1045 IF (nvl(l_technical_evaluation_status, 'NOT_COMPLETED') = 'NOT_COMPLETED')
1046 THEN
1047 l_submit_stage := 'TECHNICAL';
1048 ELSIF l_technical_evaluation_status = 'COMPLETED'
1049 THEN
1050 l_submit_stage := 'COMMERCIAL';
1051 END IF;
1052 END IF;
1053 --------------------------------------------------
1054
1055 -- (1) perform line-level validation.
1056 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1057 print_log(l_api_name, p_bid_number || ' - performing pre-publish validation for draft bid ' || p_bid_number);
1058 END IF;
1059
1060 pon_bid_validations_pkg.validate_bid(p_auction_header_id, p_bid_number, 'PUBLISHBID', p_user_id, p_batch_id, p_request_id,
1061 v_subroutine_return_status, v_subroutine_return_code);
1062
1063 -- quit out if we have errors from the validation. return code will be VALIDATION_ERROR
1064 IF v_subroutine_return_status = g_return_error THEN
1065 x_return_status := g_return_error;
1066 x_return_code := 'VALIDATION_ERROR';
1067
1068 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1069 print_log(l_api_name, p_bid_number || ' - validation returned with errors; exiting publish');
1070 END IF;
1071 RETURN;
1072 END IF;
1073
1074 -- if p_hdr_validation_failed, it means that the Java-layer header-level
1075 -- validation of this bid failed. We still want to process this publish
1076 -- to find any line-level validation errors, but we do not want to proceed
1077 -- with the actual publish. x_return_status for the publish routine
1078 -- is set to 0 if no line-level validations are found; set to 1 if found (see above).
1079 IF p_hdr_validation_failed = 'Y' THEN
1080 x_return_status := g_return_success;
1081
1082 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1083 print_log(l_api_name, p_bid_number || ' - validation returned with no errors, but exiting publish because there are header-level validation errors');
1084 END IF;
1085 RETURN;
1086 END IF;
1087
1088
1089 -- lock the auction header
1090 SELECT close_bidding_date, group_enabled_flag,
1091 max_internal_line_num, trading_partner_id, sealed_auction_status, two_part_flag
1092 INTO v_auc_close_bidding_date, v_group_enabled_flag,
1093 v_maxLineNumber, v_auc_tp_id, v_sealed_auction_status, v_two_part_flag
1094 FROM pon_auction_headers_all
1095 WHERE auction_header_id = p_auction_header_id
1096 FOR UPDATE OF CLOSE_BIDDING_DATE;
1097
1098 SELECT vendor_site_id, old_bid_number, surrog_bid_flag, trading_partner_contact_name, evaluation_flag
1099 INTO v_vendor_site_id, v_prev_bid_number, v_surrog_bid_flag, v_tpc_name, v_evaluation_flag
1100 FROM pon_bid_headers
1101 WHERE bid_number = p_bid_number;
1102
1103 -- (woojin) first do the validations to make sure that this
1104 -- bid is allowed to go through.
1105 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1106 print_log(l_api_name, p_bid_number || ' - checking is bid valid ');
1107 END IF;
1108 PON_AUCTION_HEADERS_PKG.check_is_bid_valid(p_auction_header_id,
1109 p_bid_number,
1110 v_vendor_site_id,
1111 v_prev_bid_number,
1112 p_rebid_flag,
1113 'N',
1114 v_surrog_bid_flag,
1115 p_publish_date,
1116 v_subroutine_return_status,
1117 v_subroutine_return_code);
1118
1119 -- if we have errors, then quit out of is_bidding_allowed
1120 -- and in turn, quit out of save draft or publish.
1121 IF v_subroutine_return_status = g_return_error THEN
1122 x_return_status := g_return_error;
1123 x_return_code := v_subroutine_return_code;
1124
1125 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1126 print_log(l_api_name, p_bid_number || ' - check_is_bid_valid failed with error: ' || v_subroutine_return_code || '; exiting publish.');
1127 END IF;
1128 RETURN;
1129 END IF;
1130
1131 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1132 print_log(l_api_name, p_bid_number || ' - after check is bid valid ');
1133 END IF;
1134
1135
1136 --------------------------------------------------------------------------------
1137 -- END OF VALIDATION. BELOW, WE PROCEED WITH PUBLISH
1138 --------------------------------------------------------------------------------
1139
1140 v_batchSize := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
1141 v_batchingRequired := (v_maxLineNumber > v_batchSize);
1142 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1143 print_log(l_api_name, p_bid_number || ' - batching required? batch size=' || v_batchSize || '; numOfLines=' || v_maxLineNumber);
1144 END IF;
1145
1146 --<Sol Project>
1147 --Mark Info Lines as bidded if parent priced line is Bidded
1148 --if SLINs are bidded Parent Info CLIN should be marked as bidded
1149 IF ( PON_CLM_UTIL_PKG.IS_NEG_DOCUMENT_FEDERAL(p_auction_header_id) = 1 ) THEN
1150 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1151 print_log(l_api_name, p_bid_number || ' - CLM Mark Info Lines and option lines ');
1152 END IF;
1153 mark_info_lines(p_bid_number);
1154
1155 --Unsolicited Lines Project
1156 IF l_allow_unsol_lines = 'Y' THEN
1157 mark_unsol_info_lines(p_bid_number);
1158 END IF;
1159
1160 mark_option_lines(p_bid_number);
1161 END IF;
1162
1163
1164 -- (2) remove empty rows (lines, children)
1165 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1166 print_log(l_api_name, p_bid_number || ' - remove empty rows');
1167 END IF;
1168 -- modified by Allen Yang for surrogate bid bug 7702461 2009/01/22
1169 --------------------------------------------------------------------
1170 -- only for those quotes not submitted on technical stage, remove empty rows.
1171 IF ( l_submit_stage IS NULL OR l_submit_stage <> 'TECHNICAL')
1172 THEN
1173 IF (v_batchingRequired) THEN
1174 remove_empty_rows_auto(p_bid_number, v_maxLineNumber, v_batchSize);
1175 ELSE
1176 remove_empty_rows(p_bid_number, 1, v_maxLineNumber);
1177 END IF;
1178 END IF;
1179 /*
1180 IF (v_batchingRequired) THEN
1181 remove_empty_rows_auto(p_bid_number, v_maxLineNumber, v_batchSize);
1182 ELSE
1183 remove_empty_rows(p_bid_number, 1, v_maxLineNumber);
1184 END IF;
1185 */
1186 ---------------------------------------------------------------------
1187
1188 -- (3) calculate group amounts
1189 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1190 print_log(l_api_name, p_bid_number || ' - calculate group amounts? ' || v_group_enabled_flag);
1191 END IF;
1192
1193 -- (woojin) commented out this portion because group amount
1194 -- is calculated in update_auction_info/update_proxy_bids after
1195 -- proxies are calculated. The calculation here is not only
1196 -- useless, but sometimes may yield in wrong results.
1197 --
1198 -- (jingche) Bug6491853: uncomment this part of code, because update_group_amounts() is not called for all cases in update_auction_info(), group_amount becomes null in RFQ
1199 -- use 'N' instead of 'Y' when calling calculate_group_amounts_auto()
1200 -- to use buyer currency in gourp_amount
1201 IF v_group_enabled_flag = 'Y' THEN
1202 IF (v_batchingRequired) THEN
1203 calculate_group_amounts_auto(p_bid_number, 'N');
1204 ELSE
1205 calculate_group_amounts(p_bid_number, 'N');
1206 END IF ;
1207 END IF;
1208
1209 -- (4) set miscellaneous header-level attributes
1210 -- - close_bidding_date = sysdate if auction is still open
1211 -- surrog bid receipt date if closed
1212 -- - shortlist_flag = default to 'Y'
1213 -- - surrog_bid_online_entry_date = sysdate
1214
1215 IF (v_surrog_bid_flag = 'Y') THEN
1216 SELECT surrog_bid_receipt_date
1217 INTO v_surrog_bid_receipt_date
1218 FROM pon_bid_headers
1219 WHERE bid_number = p_bid_number;
1220 v_publish_date := v_surrog_bid_receipt_date;
1221 ELSE
1222 v_publish_date := p_publish_date;
1223 END IF;
1224
1225 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1226 print_log(l_api_name, p_bid_number || ' - set header-level attributes');
1227 END IF;
1228 IF (v_batchingRequired) THEN
1229 update_bid_header_fields_auto(p_bid_number, v_publish_date, p_publish_date, v_surrog_bid_flag, v_two_part_flag, v_sealed_auction_status);
1230 ELSE
1231 update_bid_header_fields(p_bid_number, v_publish_date, p_publish_date, v_surrog_bid_flag, v_two_part_flag, v_sealed_auction_status);
1232 END IF;
1233
1234 -- (5) set miscellaneous line-level attributes
1235 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1236 print_log(l_api_name, p_bid_number || ' - publish lines');
1237 END IF;
1238 IF (v_batchingRequired) THEN
1239 publish_lines_auto(p_auction_header_id, p_bid_number, v_publish_date,
1240 p_tp_id, v_auc_tp_id, v_surrog_bid_flag, p_rebid_flag, v_maxLineNumber, v_batchSize);
1241 ELSE
1242 publish_lines(p_auction_header_id, p_bid_number, v_publish_date,
1243 p_tp_id, v_auc_tp_id, v_surrog_bid_flag, p_rebid_flag, 1, v_maxLineNumber);
1244 END IF;
1245
1246 --Unsolicited Lines Project : Publish unsol lines.
1247 IF l_allow_unsol_lines = 'Y' THEN
1248 publish_unsol_lines(p_auction_header_id, p_bid_number, v_publish_date,
1249 p_tp_id, v_auc_tp_id, v_surrog_bid_flag, p_rebid_flag);
1250 END IF;
1251
1252 -- (7) perform the things that used to be in BidHeadersEO.beforeCommit()
1253 -- check_auction_bidder
1254 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1255 print_log(l_api_name, p_bid_number || ' - check auction bidder');
1256 END IF;
1257 PON_AUCTION_PKG.CHECK_AUCTION_BIDDER(p_tpc_id, p_auction_header_id, v_subroutine_return_status);
1258 IF v_subroutine_return_status = 1 THEN
1259 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1260 print_log(l_api_name, p_bid_number || ' - check auction bidder; user not added to role');
1261 END IF;
1262 v_user_added_to_role := 'N';
1263 END IF;
1264
1265
1266 -- update_auction_info
1267 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1268 print_log(l_api_name, p_bid_number || ' - update auction info');
1269 END IF;
1270 PON_AUCTION_HEADERS_PKG.UPDATE_AUCTION_INFO(p_auction_header_id, p_bid_number, v_vendor_site_id, p_rebid_flag, v_prev_bid_number, 'N', v_surrog_bid_flag, p_user_id, v_subroutine_return_status, v_subroutine_return_code);
1271
1272 IF v_subroutine_return_status = g_return_error THEN
1273 x_return_code := v_subroutine_return_code;
1274 x_return_status := g_return_error;
1275 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1276 print_log(l_api_name, p_bid_number || ' - update auction info; error code=' || x_return_code);
1277 END IF;
1278 RETURN;
1279 ELSIF v_subroutine_return_status = g_return_warning THEN
1280 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1281 print_log(l_api_name, p_bid_number || ' - update auction info; price changed');
1282 END IF;
1283 v_price_changed := 'Y';
1284 END IF;
1285
1286 -- ackResponse stuff -- only perform when this is not a rebid
1287 IF p_rebid_flag = 'N' THEN
1288 BEGIN
1289 SELECT wf_item_key
1290 INTO v_wf_item_key
1291 FROM pon_bidding_parties
1292 WHERE auction_header_id = p_auction_header_id
1293 AND trading_partner_id = p_tp_id
1294 AND nvl(supp_acknowledgement, 'N') = 'N'
1295 AND rownum=1;
1296 EXCEPTION
1297 WHEN NO_DATA_FOUND THEN
1298 v_wf_item_key := NULL;
1299 END;
1300
1301 IF v_wf_item_key IS NOT NULL THEN
1302 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1303 print_log(l_api_name, p_bid_number || ' - ack notif response');
1304 END IF;
1305 PON_AUCTION_PKG.ACK_NOTIF_RESPONSE(v_wf_item_key, v_tpc_name, 'Y','', v_subroutine_return_status);
1306
1307 IF v_subroutine_return_status = 1 THEN
1308 IF (g_fnd_debug = 'Y') THEN
1309 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
1310 FND_LOG.string(log_level => FND_LOG.level_exception,
1311 module => g_module_prefix || l_api_name,
1312 message => 'Response acknowledge notification cannot be sent for bid ' || p_bid_number);
1313 END IF;
1314 END IF;
1315 END IF;
1316 END IF;
1317 END IF;
1318
1319 IF v_user_added_to_role = 'N' THEN
1320 IF v_price_changed = 'Y' THEN
1321 x_return_status := g_return_warning;
1322 x_return_code := 'PRICE_CHANGED_AND_USER_NOT_ADDED_TO_ROLE';
1323 ELSE
1324 x_return_status := g_return_warning;
1325 x_return_code := 'USER_NOT_ADDED_TO_ROLE';
1326 END IF;
1327 ELSIF v_price_changed = 'Y' THEN
1328 x_return_status := g_return_warning;
1329 x_return_code := 'PRICE_CHANGED';
1330 ELSE
1331 x_return_status := g_return_success;
1332 END IF;
1333
1334 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1335 print_log(l_api_name, p_bid_number || ' - return status: ' || x_return_status || '; return code: ' || x_return_code);
1336 print_log(l_api_name, p_bid_number || ' - call send_resp_notif');
1337 END IF;
1338
1339 --Call the Workflow. Don not raise any error even if is not successful
1340 PON_AUCTION_PKG.SEND_RESP_NOTIF(p_bid_number => p_bid_number,
1341 x_return_status =>v_subroutine_return_code);
1342
1343 -- raise response publish business event (uday's code)
1344 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1345 print_log(l_api_name, p_bid_number || ' - call raise response publish business event');
1346 END IF;
1347 PON_BIZ_EVENTS_PVT.RAISE_RESPNSE_PUB_EVENT(1.0, 'F', 'F', p_bid_number, v_biz_return_status, v_biz_msg_count, v_biz_msg_data_value);
1348
1349 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1350 print_log(l_api_name, 'v_biz_return_status=' || v_biz_return_status);
1351 print_log(l_api_name, 'v_biz_msg_count=' || v_biz_msg_count);
1352 print_log(l_api_name, 'v_biz_msg_data_value=' || v_biz_msg_data_value);
1353 print_log(l_api_name, p_bid_number || ' - finally PUBLISH the bid by setting bid_status to ACTIVE!!');
1354 END IF;
1355
1356 -- Begin Supplier Management: Bug 12369949
1357 IF (v_evaluation_flag = 'Y') THEN
1358 PON_EVAL_TEAM_UTIL_PVT.send_eval_update_scorer_notif(p_bid_number);
1359 END IF;
1360 -- End Supplier Management: Bug 12369949
1361
1362 -- unlock and publish
1363 -- if this is a draft bid then need to release lock
1364 UPDATE pon_bid_headers
1365 SET draft_locked = 'N',
1366 draft_unlocked_by = p_tp_id,
1367 draft_unlocked_by_contact_id = p_tpc_id,
1368 draft_unlocked_date = sysdate,
1369 bid_status = 'ACTIVE'
1370 --added by Allen Yang for Surrogate Bid 2008/09/08
1371 --------------------------------------------------
1372 , submit_stage = l_submit_stage
1373 --------------------------------------------------
1374 WHERE bid_number = p_bid_number;
1375
1376 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1377 print_log(l_api_name, p_bid_number || ' - exiting publish successfully');
1378 END IF;
1379
1380
1381 END publish;
1382
1383 PROCEDURE MARK_INFO_LINES(p_bid_number IN pon_bid_headers.bid_number%TYPE)
1384 IS
1385
1386 --Unsolicited Lines Project : Mark only negotiation lines. Unsolicited info lines will be marked seperately.
1387 Cursor infoCursor Is select auction_header_id,auction_line_number,line_number,group_line_id from pon_bid_item_prices where
1388 bid_number = p_bid_number and nvl(clm_info_flag,'N') = 'Y' AND auction_line_number <> -1;
1389 l_award_flag Varchar2(1);
1390 l_bid_line_count Number;
1391 BEGIN
1392 For infoLine in infoCursor loop
1393 if ( pon_clm_util_pkg.is_line_clin(infoLine.auction_header_id,infoLine.auction_line_number) = FALSE ) THEN
1394 --slin info line check parent line
1395 select nvl(has_bid_flag,'N') into l_award_flag from pon_bid_item_prices where bid_number = p_bid_number and line_number = infoLine.group_line_id;
1396 if ( l_award_flag = 'Y' ) Then
1397 update pon_bid_item_prices
1398 set has_bid_flag = l_award_flag
1399 where bid_number = p_bid_number
1400 and line_number = infoLine.line_number;
1401 end if;
1402 else
1403 --info CLIN
1404 select count(*) into l_bid_line_count from pon_bid_item_prices where
1405 bid_number = p_bid_number and group_line_id = infoLine.line_number and nvl(has_bid_flag,'N') = 'Y';
1406
1407 if ( l_bid_line_count > 0 ) THEN
1408 update pon_bid_item_prices
1409 set has_bid_flag = 'Y'
1410 where bid_number = p_bid_number
1411 and line_number = infoLine.line_number;
1412 end if;
1413 end if;
1414 end loop;
1415 END MARK_INFO_LINES;
1416
1417 PROCEDURE MARK_OPTION_LINES(p_bid_number IN pon_bid_headers.bid_number%TYPE)
1418 IS
1419 Cursor optCursor Is select auction_header_id,auction_line_number,line_number,clm_base_line_num from pon_bid_item_prices where
1420 bid_number = p_bid_number and clm_base_line_num is not null and nvl(has_bid_flag,'N') = 'N';
1421 l_award_flag Varchar2(1);
1422 BEGIN
1423 For optLine in optCursor loop
1424 --slin info line check parent line
1425 select nvl(has_bid_flag,'N') into l_award_flag from pon_bid_item_prices where bid_number = p_bid_number and line_number = optLine.clm_base_line_num;
1426 if ( l_award_flag = 'Y' ) Then
1427 update pon_bid_item_prices
1428 set has_bid_flag = l_award_flag
1429 where bid_number = p_bid_number
1430 and line_number = optLine.line_number;
1431 end if;
1432 end loop;
1433 END MARK_OPTION_LINES;
1434
1435
1436 -- -------------------------------------------------------------------------
1437 -- remove_empty_rows
1438 --
1439 -- called from publish_bid above, this method will remove empty lines and
1440 -- children so that we only commit lines with bids to the database upon
1441 -- publish
1442 -- -------------------------------------------------------------------------
1443
1444 PROCEDURE remove_empty_rows
1445 (p_bid_number IN pon_bid_headers.bid_number%TYPE,
1446 p_batch_start IN NUMBER,
1447 p_batch_end IN NUMBER)
1448
1449 IS
1450
1451 l_api_name CONSTANT VARCHAR2(30) := 'remove_empty_rows';
1452
1453 BEGIN
1454
1455 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1456 print_log(l_api_name, p_bid_number || ' - begin remove empty rows for batch range ' || p_batch_start || ' ~ ' || p_batch_end || ' inclusive');
1457 END IF;
1458
1459 -- 1) empty attributes (including header attributes)
1460 -- 2) display only attributes (including header attributes)
1461 -- 3) attributes on lines that have hasBid = N
1462 DELETE FROM pon_bid_attribute_values pbav
1463 WHERE pbav.bid_number = p_bid_number
1464 AND (EXISTS (SELECT null
1465 FROM pon_auction_attributes paa
1466 WHERE pbav.auction_header_id = paa.auction_header_id
1467 AND pbav.line_number = paa.line_number
1468 AND pbav.attribute_name = paa.attribute_name
1469 AND paa.display_only_flag = 'Y')
1470 OR pbav.value IS NULL
1471 OR EXISTS (SELECT null
1472 FROM pon_bid_item_prices pbip
1473 WHERE pbip.bid_number = pbav.bid_number
1474 AND pbip.line_number = pbav.line_number
1475 AND pbip.has_bid_flag = 'N'))
1476 AND pbav.line_number >= p_batch_start
1477 AND pbav.line_number <= p_batch_end;
1478
1479 -- remove:
1480 -- 1) empty price elements
1481 -- 2) price elements for lines with hasBid=N
1482 DELETE FROM pon_bid_price_elements pbpe
1483 WHERE pbpe.bid_number = p_bid_number
1484 AND EXISTS (SELECT null
1485 FROM pon_bid_item_prices pbip
1486 WHERE pbip.bid_number = pbpe.bid_number
1487 AND pbip.line_number = pbpe.line_number
1488 AND pbip.has_bid_flag = 'N')
1489 AND pbpe.line_number >= p_batch_start
1490 AND pbpe.line_number <= p_batch_end;
1491
1492 -- remove:
1493 -- 1) empty price_differentials (line-level, shipment-level)
1494 -- 2) price differentials for lines with hasBid=N (line-level, shipment-level)
1495 DELETE FROM pon_bid_price_differentials pbpd
1496 WHERE pbpd.bid_number = p_bid_number
1497 AND EXISTS (SELECT null
1498 FROM pon_bid_item_prices pbip
1499 WHERE pbip.bid_number = pbpd.bid_number
1500 AND pbip.line_number = pbpd.line_number
1501 AND pbip.has_bid_flag = 'N')
1502 AND pbpd.line_number >= p_batch_start
1503 AND pbpd.line_number <= p_batch_end;
1504
1505
1506 -- remove:
1507 -- 1) shipments for lines with hasBid=N
1508 DELETE FROM pon_bid_shipments pbs
1509 WHERE pbs.bid_number = p_bid_number
1510 AND EXISTS (SELECT null
1511 FROM pon_bid_item_prices pbip
1512 WHERE pbip.bid_number = pbs.bid_number
1513 AND pbip.line_number = pbs.line_number
1514 AND pbip.has_bid_flag = 'N')
1515 AND pbs.line_number >= p_batch_start
1516 AND pbs.line_number <= p_batch_end;
1517
1518 -- remove:
1519 -- 1) Payments for lines with hasBid=N
1520 DELETE FROM pon_bid_payments_shipments pbps
1521 WHERE pbps.bid_number = p_bid_number
1522 AND EXISTS (SELECT null
1523 FROM pon_bid_item_prices pbip
1524 WHERE pbip.bid_number = pbps.bid_number
1525 AND pbip.line_number = pbps.bid_line_number
1526 AND pbip.has_bid_flag = 'N')
1527 AND pbps.bid_line_number >= p_batch_start
1528 AND pbps.bid_line_number <= p_batch_end;
1529
1530 -- remove empty lines finally
1531 DELETE FROM pon_bid_item_prices
1532 WHERE bid_number = p_bid_number
1533 AND has_bid_flag = 'N'
1534 AND line_number >= p_batch_start
1535 AND line_number <= p_batch_end;
1536
1537 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1538 print_log(l_api_name, p_bid_number || ' - end remove empty rows for batch range ' || p_batch_start || ' ~ ' || p_batch_end || ' inclusive');
1539 END IF;
1540 END remove_empty_rows;
1541
1542
1543 PROCEDURE remove_empty_rows_auto
1544 (p_bid_number IN pon_bid_headers.bid_number%TYPE,
1545 p_max_line_number IN NUMBER,
1546 p_batch_size IN NUMBER)
1547 IS
1548 PRAGMA AUTONOMOUS_TRANSACTION;
1549
1550 l_api_name CONSTANT VARCHAR2(30) := 'remove_empty_rows_auto';
1551 v_batch_start NUMBER;
1552 v_batch_end NUMBER;
1553
1554 BEGIN
1555
1556 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1557 print_log(l_api_name, p_bid_number || ' - BEGIN remove empty rows AUTONOMOUS');
1558 END IF;
1559
1560 v_batch_start := 1;
1561 IF (p_max_line_number < p_batch_size) THEN
1562 v_batch_end := p_max_line_number;
1563 ELSE
1564 v_batch_end := p_batch_size;
1565 END IF;
1566
1567 WHILE (v_batch_start <= p_max_line_number) LOOP
1568
1569 remove_empty_rows(p_bid_number, v_batch_start, v_batch_end);
1570 commit;
1571
1572 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1573 print_log(l_api_name, p_bid_number || ' - remove empty rows: batch committed');
1574 END IF;
1575
1576 v_batch_start := v_batch_end + 1;
1577 IF (v_batch_end + p_batch_size > p_max_line_number) THEN
1578 v_batch_end := p_max_line_number;
1579 ELSE
1580 v_batch_end := v_batch_end + p_batch_size;
1581 END IF;
1582 END LOOP;
1583
1584 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1585 print_log(l_api_name, p_bid_number || ' - END remove empty rows AUTONOMOUS');
1586 END IF;
1587 END remove_empty_rows_auto;
1588
1589
1590
1591 -- -------------------------------------------------------------------------
1592 -- update_bid_header_fields
1593 --
1594 -- called from publish above, this method will update a few fields in
1595 -- bid header
1596 -- -------------------------------------------------------------------------
1597
1598 PROCEDURE update_bid_header_fields
1599 (p_bid_number IN pon_bid_headers.bid_number%TYPE,
1600 p_publish_date IN pon_bid_headers.publish_date%TYPE,
1601 p_bid_entry_date IN pon_bid_headers.publish_date%TYPE,
1602 p_surrog_bid_flag IN pon_bid_headers.surrog_bid_flag%TYPE,
1603 p_two_part_flag IN pon_auction_headers_all.two_part_flag%TYPE,
1604 p_sealed_auction_status IN pon_auction_headers_all.sealed_auction_status%TYPE)
1605 IS
1606
1607 l_api_name CONSTANT VARCHAR2(30) := 'update_bid_header_fields';
1608 l_tech_shortlist_flag VARCHAR2(1) := null;
1609
1610 BEGIN
1611
1612 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1613 print_log(l_api_name, p_bid_number || ' - begin update bid header fields');
1614 print_log(l_api_name, p_bid_number || ' - update bid header fields: p_publish_date=' || p_publish_date);
1615 END IF;
1616
1617 IF (p_surrog_bid_flag = 'Y' AND p_two_part_flag = 'Y' AND p_sealed_auction_status <> 'LOCKED') THEN
1618 l_tech_shortlist_flag := 'Y';
1619 END IF;
1620
1621
1622 UPDATE pon_bid_headers
1623 SET publish_date = p_publish_date,
1624 shortlist_flag = 'Y',
1625 surrog_bid_online_entry_date = p_bid_entry_date,
1626 technical_shortlist_flag = l_tech_shortlist_flag
1627 --,bid_status = 'ACTIVE'
1628 WHERE bid_number = p_bid_number;
1629
1630 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1631 print_log(l_api_name, p_bid_number || ' - end update bid header fields');
1632 END IF;
1633 END update_bid_header_fields;
1634
1635
1636 PROCEDURE update_bid_header_fields_auto
1637 (p_bid_number IN pon_bid_headers.bid_number%TYPE,
1638 p_publish_date IN pon_bid_headers.publish_date%TYPE,
1639 p_bid_entry_date IN pon_bid_headers.publish_date%TYPE,
1640 p_surrog_bid_flag IN pon_bid_headers.surrog_bid_flag%TYPE,
1641 p_two_part_flag IN pon_auction_headers_all.two_part_flag%TYPE,
1642 p_sealed_auction_status IN pon_auction_headers_all.sealed_auction_status%TYPE)
1643 IS
1644 PRAGMA AUTONOMOUS_TRANSACTION;
1645
1646 l_api_name CONSTANT VARCHAR2(30) := 'update_bid_header_fields_auto';
1647 BEGIN
1648 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1649 print_log(l_api_name, p_bid_number || ' - BEGIN update bid header fields AUTONOMOUS');
1650 END IF;
1651
1652 update_bid_header_fields(p_bid_number, p_publish_date, p_bid_entry_date, p_surrog_bid_flag, p_two_part_flag, p_sealed_auction_status);
1653 commit;
1654
1655 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1656 print_log(l_api_name, p_bid_number || ' - update bid header fields: committed!');
1657 print_log(l_api_name, p_bid_number || ' - END update bid header fields AUTONOMOUS');
1658 END IF;
1659
1660 END update_bid_header_fields_auto;
1661
1662
1663 -- -------------------------------------------------------------------------
1664 -- publish_lines
1665 --
1666 -- called from publish_bid above, this method will perform line-level
1667 -- publish processing, such as setting line-level publish date.
1668 -- -------------------------------------------------------------------------
1669
1670 PROCEDURE publish_lines
1671 (p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
1672 p_bid_number IN pon_bid_headers.bid_number%TYPE,
1673 p_publish_date IN DATE,
1674 p_tp_id IN pon_bid_headers.trading_partner_id%TYPE,
1675 p_auc_tp_id IN pon_auction_headers_all.trading_partner_id%TYPE,
1676 p_surrog_bid_flag IN pon_bid_headers.surrog_bid_flag%TYPE,
1677 p_rebid_flag IN VARCHAR,
1678 p_batch_start IN NUMBER,
1679 p_batch_end IN NUMBER)
1680
1681 IS
1682
1683 v_full_quantity_bid_code pon_auction_headers_all.full_quantity_bid_code%TYPE;
1684 v_surrog_bid_flag pon_bid_headers.surrog_bid_flag%TYPE;
1685 v_auc_tp_id pon_auction_headers_all.trading_partner_id%TYPE;
1686 v_auction_header_id NUMBER;
1687 l_api_name CONSTANT VARCHAR2(30) := 'publish_lines';
1688
1689 BEGIN
1690
1691 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1692 print_log(l_api_name, p_bid_number || ' - begin publish lines for batch range: ' || p_batch_start || ' ~ ' || p_batch_end || ' inclusive');
1693 END IF;
1694
1695 IF p_rebid_flag = 'N' THEN
1696
1697 UPDATE pon_bid_item_prices
1698 SET publish_date = p_publish_date,
1699 proxy_bid_flag = 'N',
1700 award_price = price,
1701 first_bid_price = price, --(woojin) do we really need this?
1702 bid_trading_partner_id = decode(p_surrog_bid_flag,
1703 'Y', p_auc_tp_id, p_tp_id)
1704 WHERE bid_number = p_bid_number
1705 AND line_number >= p_batch_start
1706 AND line_number <= p_batch_end
1707 AND auction_line_number <> -1; --Unsolicited Lines Project : Update unsol lines seperately.
1708
1709 ELSE
1710
1711 -- then set publish date to current time for isPublishedLines
1712 -- also, set proxy_bid_flag to 'N'
1713 -- Please note that the is_changed_line_flag is used in the procedure
1714 -- PONAUCHB.check_is_bid_valid to determine the newly added/modified
1715 -- lines. In case this logic is changed do ensure that the check_is_bid_valid
1716 -- procedure is also modified
1717 UPDATE pon_bid_item_prices
1718 SET publish_date = p_publish_date,
1719 proxy_bid_flag = 'N',
1720 award_price = price,
1721 first_bid_price = nvl(first_bid_price, price), --(woojin) do we really need this?
1722 bid_trading_partner_id = decode(p_surrog_bid_flag,
1723 'Y', p_auc_tp_id, p_tp_id)
1724 WHERE bid_number = p_bid_number
1725 AND is_changed_line_flag = 'Y'
1726 AND line_number >= p_batch_start
1727 AND line_number <= p_batch_end
1728 AND auction_line_number <> -1; --Unsolicited Lines Project : Update unsol lines seperately.
1729
1730 --Added for bug 10169313
1731 UPDATE pon_bid_item_prices
1732 SET award_price = price
1733 WHERE bid_number = p_bid_number
1734 AND is_changed_line_flag <> 'Y'
1735 AND line_number >= p_batch_start
1736 AND line_number <= p_batch_end
1737 AND auction_line_number <> -1; --Unsolicited Lines Project : Update unsol lines seperately.
1738
1739 END IF;
1740
1741 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1742 print_log(l_api_name, p_bid_number || ' - end publish lines for batch range: ' || p_batch_start || ' ~ ' || p_batch_end || ' inclusive');
1743 END IF;
1744
1745 END;
1746
1747
1748 PROCEDURE publish_lines_auto
1749 (p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
1750 p_bid_number IN pon_bid_headers.bid_number%TYPE,
1751 p_publish_date IN DATE,
1752 p_tp_id IN pon_bid_headers.trading_partner_id%TYPE,
1753 p_auc_tp_id IN pon_auction_headers_all.trading_partner_id%TYPE,
1754 p_surrog_bid_flag IN pon_bid_headers.surrog_bid_flag%TYPE,
1755 p_rebid_flag IN VARCHAR,
1756 p_max_line_number IN NUMBER,
1757 p_batch_size IN NUMBER)
1758 IS
1759 PRAGMA AUTONOMOUS_TRANSACTION;
1760
1761 v_batch_start NUMBER;
1762 v_batch_end NUMBER;
1763 l_api_name CONSTANT VARCHAR2(30) := 'publish_lines_auto';
1764
1765 BEGIN
1766
1767 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1768 print_log(l_api_name, p_bid_number || ' - BEGIN publish lines AUTONOMOUS');
1769 END IF;
1770
1771 v_batch_start := 1;
1772 IF (p_max_line_number < p_batch_size) THEN
1773 v_batch_end := p_max_line_number;
1774 ELSE
1775 v_batch_end := p_batch_size;
1776 END IF;
1777
1778 WHILE (v_batch_start <= p_max_line_number) LOOP
1779
1780 publish_lines(p_auction_header_id, p_bid_number, p_publish_date, p_tp_id,
1781 p_auc_tp_id, p_surrog_bid_flag, p_rebid_flag, v_batch_start, v_batch_end);
1782 commit;
1783
1784 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1785 print_log(l_api_name, p_bid_number || ' - publish lines: batch committed!');
1786 END IF;
1787
1788 v_batch_start := v_batch_end + 1;
1789 IF (v_batch_end + p_batch_size > p_max_line_number) THEN
1790 v_batch_end := p_max_line_number;
1791 ELSE
1792 v_batch_end := v_batch_end + p_batch_size;
1793 END IF;
1794 END LOOP;
1795
1796 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1797 print_log(l_api_name, p_bid_number || ' - END publish lines AUTONOMOUS');
1798 END IF;
1799
1800 END publish_lines_auto;
1801
1802 -- -------------------------------------------------------------------------
1803 -- publish_cp
1804 --
1805 -- this is the concurrent program that will be called for super-large
1806 -- negotiations.
1807 -- -------------------------------------------------------------------------
1808
1809 PROCEDURE publish_cp
1810 (errbuf OUT NOCOPY VARCHAR2,
1811 retcode OUT NOCOPY VARCHAR2,
1812 p_auction_header_id IN NUMBER,
1813 p_bid_number IN NUMBER,
1814 p_rebid_flag IN VARCHAR2,
1815 p_publish_date IN VARCHAR2,
1816 p_date_mask IN VARCHAR2,
1817 p_tp_id IN NUMBER,
1818 p_tpc_id IN NUMBER,
1819 p_user_type IN VARCHAR2,
1820 p_user_id IN NUMBER)
1821 IS
1822 v_request_id NUMBER;
1823 v_error_msg_name VARCHAR2(200);
1824 v_return_status NUMBER;
1825 v_return_code VARCHAR2(50);
1826 v_publish_date DATE;
1827 v_user_name VARCHAR2(50);
1828
1829 l_api_name CONSTANT VARCHAR2(30) := 'publish_cp';
1830
1831 BEGIN
1832
1833 retcode := '0';
1834 errbuf := '';
1835 v_request_id := FND_GLOBAL.CONC_REQUEST_ID();
1836 v_publish_date := to_date(p_publish_date, p_date_mask);
1837
1838 SELECT user_name
1839 INTO v_user_name
1840 FROM fnd_user
1841 WHERE user_id = p_user_id;
1842
1843 publish(p_auction_header_id, p_bid_number, p_rebid_flag,
1844 v_publish_date, p_tp_id, p_tpc_id, p_user_id, null,
1845 v_request_id, 'N', v_return_status, v_return_code);
1846
1847
1848 IF v_return_status = 0 OR v_return_status = 2 THEN
1849 errbuf := 'PUBLISH_CP exited successfully';
1850 retcode := '0';
1851
1852 -- clear out the request_id once this CP exits successfully.
1853 UPDATE pon_bid_headers
1854 SET request_id = null
1855 WHERE bid_number = p_bid_number;
1856
1857 PON_WF_UTL_PKG.ReportConcProgramStatus (
1858 p_request_id => v_request_id,
1859 p_messagetype => 'S',
1860 p_RecepientUsername => v_user_name,
1861 p_recepientType =>p_user_type,
1862 p_auction_header_id => p_auction_header_id,
1863 p_ProgramTypeCode => 'BID_PUBLISH',
1864 p_DestinationPageCode => 'PONRESENQ_VIEWBID',
1865 p_bid_number => p_bid_number);
1866
1867 -- if we have an error, then check
1868 -- what kind of errors they are.
1869 ELSIF v_return_status = g_return_error THEN
1870
1871 -- if they're validation errors, the errors
1872 -- have been put into pon_interface_errors already
1873 IF v_return_code = 'VALIDATION_ERROR' THEN
1874 errbuf := 'PUBLISH_CP exited with validation errors';
1875 retcode := '2';
1876
1877 -- if the error thrown is non-validation, we need to push
1878 -- that error into pon_interface_errors. This error is most
1879 -- likely something updateAuctionInfo-related
1880 ELSE
1881 errbuf := 'PUBLISH_CP exited with publish errors';
1882 retcode := '2';
1883
1884 -- insert the publish error into pon_interface_errors table
1885 get_message_name(v_return_code, p_auction_header_id, v_error_msg_name);
1886
1887 INSERT INTO pon_interface_errors
1888 (bid_number, auction_header_id, interface_type, request_id,
1889 error_message_name, expiration_date)
1890 VALUES
1891 (p_bid_number, p_auction_header_id, 'PUBLISHBID', v_request_id,
1892 v_error_msg_name, sysdate+7);
1893
1894 END IF;
1895
1896 PON_WF_UTL_PKG.ReportConcProgramStatus (
1897 p_request_id => v_request_id,
1898 p_messagetype => 'E',
1899 p_RecepientUsername => v_user_name,
1900 p_recepientType =>p_user_type,
1901 p_auction_header_id => p_auction_header_id,
1902 p_ProgramTypeCode => 'BID_PUBLISH',
1903 p_DestinationPageCode => 'PON_CONCURRENT_ERRORS',
1904 p_bid_number => p_bid_number);
1905
1906 END IF;
1907
1908 commit;
1909
1910 EXCEPTION
1911 WHEN others THEN
1912
1913 -- insert an error into the FND LOG as well
1914 IF (g_fnd_debug = 'Y') THEN
1915 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
1916 FND_LOG.string(log_level => FND_LOG.level_exception,
1917 module => g_module_prefix || l_api_name,
1918 message => 'SQL error code: ' || sqlcode || ', error message: ' || substr(sqlerrm,1,512));
1919 END IF;
1920 END IF;
1921
1922 -- rollback anything that we can
1923 rollback;
1924
1925 -- report error to the user through workflow notifications
1926 PON_WF_UTL_PKG.ReportConcProgramStatus (
1927 p_request_id => v_request_id,
1928 p_messagetype => 'E',
1929 p_RecepientUsername => v_user_name,
1930 p_recepientType =>p_user_type,
1931 p_auction_header_id => p_auction_header_id,
1932 p_ProgramTypeCode => 'BID_PUBLISH',
1933 p_DestinationPageCode => 'PON_CONCURRENT_ERRORS',
1934 p_bid_number => p_bid_number);
1935
1936 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1937 print_log(l_api_name, 'Generating WF notif: ' || v_request_id || ' ' || v_user_name || ' ' || p_user_type || ' '||p_auction_header_id|| ' ' );
1938 END IF;
1939
1940 -- insert an error indicating that a fatal error in the
1941 -- publish CP has occurred.
1942 get_message_name('FATAL_PUBLISH_ERROR', p_auction_header_id, v_error_msg_name);
1943
1944 INSERT INTO pon_interface_errors
1945 (bid_number, auction_header_id, interface_type, request_id,
1946 error_message_name, expiration_date)
1947 VALUES
1948 (p_bid_number, p_auction_header_id, 'PUBLISHBID', v_request_id,
1949 v_error_msg_name, sysdate+7);
1950
1951 -- insert an error into the FND LOG as well
1952 IF (g_fnd_debug = 'Y') THEN
1953 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
1954 FND_LOG.string(log_level => FND_LOG.level_exception,
1955 module => g_module_prefix || l_api_name,
1956 message => 'A fatal error has occurred during the concurrent processing of response publish for response number ' || p_bid_number);
1957 END IF;
1958 END IF;
1959
1960 -- set bid header's bid_status to DRAFT
1961 UPDATE pon_bid_headers
1962 SET bid_status = 'DRAFT'
1963 WHERE bid_number = p_bid_number;
1964
1965 -- commit the changes made here in the exception block
1966 commit;
1967
1968 END publish_cp;
1969
1970
1971
1972 -- -------------------------------------------------------------------------
1973 -- validate_cp
1974 --
1975 -- this is the concurrent program that will be called for super-large
1976 -- negotiations.
1977 -- -------------------------------------------------------------------------
1978
1979
1980 PROCEDURE validate_cp
1981 (errbuf OUT NOCOPY VARCHAR2,
1982 retcode OUT NOCOPY VARCHAR2,
1983 p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
1984 p_bid_number IN pon_bid_headers.bid_number%TYPE,
1985 p_user_type IN VARCHAR2,
1986 p_user_id IN NUMBER)
1987 IS
1988
1989 v_request_id NUMBER;
1990 v_subroutine_return_status NUMBER;
1991 v_subroutine_return_code VARCHAR2(30);
1992 v_user_name VARCHAR2(50);
1993 v_error_msg_name VARCHAR2(200);
1994 v_success_destination VARCHAR2(500);
1995
1996 l_api_name CONSTANT VARCHAR2(30) := 'validate_cp';
1997
1998 BEGIN
1999
2000 retcode := '0';
2001 errbuf := '';
2002 v_request_id := FND_GLOBAL.CONC_REQUEST_ID();
2003
2004 SELECT user_name
2005 INTO v_user_name
2006 FROM fnd_user
2007 WHERE user_id = p_user_id;
2008
2009 SELECT decode (nvl (surrog_bid_flag, 'N'), 'Y', 'PONENQMGDR_MANAGEDRAFT_SURROG', 'PONENQMGDR_MANAGEDRAFT')
2010 INTO v_success_destination
2011 FROM pon_bid_headers
2012 WHERE bid_number = p_bid_number;
2013
2014 -- perform line-level validation.
2015 pon_bid_validations_pkg.validate_bid(p_auction_header_id, p_bid_number, 'VALIDATEBID', p_user_id, null, v_request_id, v_subroutine_return_status, v_subroutine_return_code);
2016
2017 IF v_subroutine_return_status = g_return_success THEN
2018 -- according to ECO 4517992, we don't clear out the request_id
2019 -- except for publish bid
2020
2021 PON_WF_UTL_PKG.ReportConcProgramStatus (
2022 p_request_id => v_request_id,
2023 p_messagetype => 'S',
2024 p_RecepientUsername => v_user_name,
2025 p_recepientType =>p_user_type,
2026 p_auction_header_id => p_auction_header_id,
2027 p_ProgramTypeCode => 'BID_VALIDATE',
2028 p_DestinationPageCode => v_success_destination,
2029 p_bid_number => p_bid_number);
2030
2031 errbuf := 'VALIDATE_CP exited successfully';
2032 retcode := '0';
2033 ELSE
2034
2035 PON_WF_UTL_PKG.ReportConcProgramStatus (
2036 p_request_id => v_request_id,
2037 p_messagetype => 'E',
2038 p_RecepientUsername => v_user_name,
2039 p_recepientType =>p_user_type,
2040 p_auction_header_id => p_auction_header_id,
2041 p_ProgramTypeCode => 'BID_VALIDATE',
2042 p_DestinationPageCode => 'PON_CONCURRENT_ERRORS',
2043 p_bid_number => p_bid_number);
2044
2045 errbuf := 'VALIDATE_CP returned validation errors';
2046 retcode := '2';
2047 END IF;
2048
2049 commit;
2050
2051
2052 EXCEPTION
2053 WHEN others THEN
2054
2055 -- insert an error into the FND LOG as well
2056 IF (g_fnd_debug = 'Y') THEN
2057 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
2058 FND_LOG.string(log_level => FND_LOG.level_exception,
2059 module => g_module_prefix || l_api_name,
2060 message => 'SQL error code: ' || sqlcode || ', error message: ' || substr(sqlerrm,1,512));
2061 END IF;
2062 END IF;
2063
2064
2065 -- rollback anything that we can
2066 rollback;
2067
2068 -- report error to the user through workflow notifications
2069 PON_WF_UTL_PKG.ReportConcProgramStatus (
2070 p_request_id => v_request_id,
2071 p_messagetype => 'E',
2072 p_RecepientUsername => v_user_name,
2073 p_recepientType =>p_user_type,
2074 p_auction_header_id => p_auction_header_id,
2075 p_ProgramTypeCode => 'BID_VALIDATE',
2076 p_DestinationPageCode => 'PON_CONCURRENT_ERRORS',
2077 p_bid_number => p_bid_number);
2078
2079 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
2080 print_log(l_api_name, 'Generating WF notif: ' || v_request_id || ' ' || v_user_name || ' ' || p_user_type || ' '||p_auction_header_id|| ' ' );
2081 END IF;
2082
2083 -- insert an error indicating that a fatal error in the
2084 -- publish CP has occurred.
2085 get_message_name('FATAL_VALIDATE_ERROR', p_auction_header_id, v_error_msg_name);
2086
2087 INSERT INTO pon_interface_errors
2088 (bid_number, auction_header_id, interface_type, request_id,
2089 error_message_name, expiration_date)
2090 VALUES
2091 (p_bid_number, p_auction_header_id, 'VALIDATEBID', v_request_id,
2092 v_error_msg_name, sysdate+7);
2093
2094 -- insert an error into the FND LOG as well
2095 IF (g_fnd_debug = 'Y') THEN
2096 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
2097 FND_LOG.string(log_level => FND_LOG.level_exception,
2098 module => g_module_prefix || l_api_name,
2099 message => 'A fatal error has occurred during the concurrent processing of response validation for response number ' || p_bid_number || '. See above for error details');
2100 END IF;
2101 END IF;
2102
2103 -- commit the changes made here in the exception block
2104 commit;
2105
2106 END validate_cp;
2107
2108
2109 -- -------------------------------------------------------------------------
2110 -- get_message_name
2111 --
2112 -- called by publish_cp, this will return the message name to be inserted
2113 -- into pon_interface_errors table
2114 -- -------------------------------------------------------------------------
2115
2116 PROCEDURE get_message_name(p_msg_code IN VARCHAR2,
2117 p_auction_header_id IN NUMBER,
2118 x_msg_name OUT NOCOPY VARCHAR2)
2119 IS
2120
2121 v_suffix VARCHAR2(1);
2122
2123 l_api_name CONSTANT VARCHAR2(30) := 'get_message_name';
2124
2125 BEGIN
2126
2127 SELECT message_suffix
2128 INTO v_suffix
2129 FROM pon_auc_doctypes
2130 WHERE doctype_id = (SELECT doctype_id
2131 FROM pon_auction_headers_all
2132 WHERE auction_header_id = p_auction_header_id);
2133
2134 IF p_msg_code = 'DISQ_REBID' THEN
2135 x_msg_name := 'PON_BID_DISQUALIFIED_REBID_' || v_suffix;
2136 ELSIF p_msg_code = 'MULTIPLE_REBID' THEN
2137 x_msg_name := 'PON_BID_MULTIPLE_REBID_' || v_suffix;
2138 ELSIF p_msg_code = 'MULTIPLE_DRAFTS' THEN
2139 x_msg_name := 'PON_BID_MULTIPLE_DRAFTS_' || v_suffix;
2140 ELSIF p_msg_code = 'SINGLE_BEST_BID' THEN
2141 x_msg_name := 'PON_AUC_BIDERROR_1_' || v_suffix;
2142 ELSIF p_msg_code = 'SINGLE_BEST_DRAFT' THEN
2143 x_msg_name := 'PON_AUC_BIDERROR_1_' || v_suffix;
2144 ELSIF p_msg_code = 'AUCTION_PAUSED' THEN
2145 x_msg_name := 'PON_AUC_PAUSED_DRAFT_' || v_suffix;
2146 ELSIF p_msg_code = 'BID_ON_CLOSED_LINE' THEN
2147 x_msg_name := 'PON_AUCTION_LINE_CLOSED_ERR_' || v_suffix;
2148 ELSIF p_msg_code = 'FATAL_PUBLISH_ERROR' THEN
2149 x_msg_name := 'PON_AUC_FATAL_BID_CP_PUB_ERR_' || v_suffix;
2150 ELSIF p_msg_code = 'FATAL_VALIDATE_ERROR' THEN
2151 x_msg_name := 'PON_AUC_FATAL_BID_CP_VAL_ERR_' || v_suffix;
2152 ELSE x_msg_name := '';
2153 END IF;
2154
2155 END get_message_name;
2156
2157 PROCEDURE get_user_lang_message (p_tpc_id IN NUMBER,
2158 p_message_name IN VARCHAR2,
2159 p_message_token1_name IN VARCHAR2,
2160 p_message_token1_value IN VARCHAR2,
2161 p_message_token2_name IN VARCHAR2,
2162 p_message_token2_value IN VARCHAR2,
2163 x_message_text OUT NOCOPY VARCHAR2)
2164 IS
2165
2166 l_language_code FND_LANGUAGES.LANGUAGE_CODE%TYPE;
2167 l_api_name CONSTANT VARCHAR2(30) := 'get_user_lang_message';
2168 l_user_id FND_USER.USER_ID%TYPE;
2169 BEGIN
2170
2171 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
2172 FND_LOG.string(log_level => FND_LOG.level_procedure,
2173 module => g_module_prefix || l_api_name,
2174 message => 'Entered with p_tpc_id = ' || p_tpc_id ||
2175 ', p_message_name = ' || p_message_name ||
2176 ', p_message_token1_name = ' || p_message_token1_name ||
2177 ', p_message_token1_value = ' || p_message_token1_value ||
2178 ', p_message_token2_name = ' || p_message_token2_name ||
2179 ', p_message_token2_value = ' || p_message_token2_value);
2180 END IF;
2181
2182 SELECT
2183 FND_USER.user_id
2184 INTO
2185 l_user_id
2186 FROM
2187 FND_USER
2188 WHERE
2189 FND_USER.PERSON_PARTY_ID = p_tpc_id
2190 AND ROWNUM=1;
2191
2192 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2193 FND_LOG.string(log_level => FND_LOG.level_statement,
2194 module => g_module_prefix || l_api_name,
2195 message => 'l_user_id = ' || l_user_id);
2196 END IF;
2197
2198 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE (l_user_id, l_language_code);
2199
2200 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2201 FND_LOG.string(log_level => FND_LOG.level_statement,
2202 module => g_module_prefix || l_api_name,
2203 message => 'l_language_code = ' || l_language_code);
2204 END IF;
2205
2206 PON_AUCTION_PKG.SET_SESSION_LANGUAGE (null, l_language_code);
2207 x_message_text := PON_AUCTION_PKG.getMessage (
2208 msg => p_message_name,
2209 msg_suffix => '',
2210 token1 => p_message_token1_name,
2211 token1_value => p_message_token1_value,
2212 token2 => p_message_token2_name,
2213 token2_value => p_message_token2_value);
2214
2215 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2216 FND_LOG.string(log_level => FND_LOG.level_statement,
2217 module => g_module_prefix || l_api_name,
2218 message => 'x_message_text = ' || x_message_text);
2219 END IF;
2220
2221 PON_AUCTION_PKG.UNSET_SESSION_LANGUAGE;
2222
2223 EXCEPTION WHEN OTHERS THEN
2224 x_message_text := '';
2225 PON_AUCTION_PKG.UNSET_SESSION_LANGUAGE;
2226
2227 END get_user_lang_message;
2228
2229 -- Begin Supplier Management: Supplier Evaluation
2230
2231 PROCEDURE calculate_avg_eval_scores(p_auction_header_id IN pon_auction_headers_all.auction_header_id%TYPE)
2232 IS
2233 BEGIN
2234
2235 IF (p_auction_header_id IS NOT NULL) THEN
2236
2237 -- pbav1 - supplier response scores
2238 -- pbav2 - evaluation scores
2239 -- pbh1 - bid header for supplier response
2240 -- pbh2 - bid header for evaluation
2241
2242 UPDATE pon_bid_attribute_values pbav1
2243 SET (pbav1.score, pbav1.weighted_score) =
2244 (SELECT AVG(pbav2.score) score,
2245 AVG(pbav2.score)*paa2.weight/paa2.attr_max_score weighted_score
2246 FROM pon_bid_attribute_values pbav2,
2247 pon_auction_attributes paa2
2248 WHERE pbav2.auction_header_id = p_auction_header_id
2249 AND pbav2.bid_number IN
2250 (SELECT pbh2.bid_number
2251 FROM pon_bid_headers pbh2, pon_bid_headers pbh3
2252 WHERE NVL(pbh2.evaluation_flag, 'N') = 'Y'
2253 AND pbh2.auction_header_id = p_auction_header_id
2254 AND pbh3.auction_header_id = p_auction_header_id
2255 AND pbh2.trading_partner_id = pbh3.trading_partner_id
2256 AND pbh2.bid_status = 'ACTIVE'
2257 AND pbh3.bid_number = pbav1.bid_number
2258 )
2259 AND pbav2.attribute_name = pbav1.attribute_name
2260 AND pbav2.auction_line_number = -1
2261 AND paa2.auction_header_id = p_auction_header_id
2262 AND paa2.attribute_name = pbav2.attribute_name
2263 AND paa2.internal_attr_flag = 'Y'
2264 AND paa2.line_number = -1
2265 GROUP BY paa2.weight,
2266 paa2.attr_max_score
2267 )
2268 WHERE pbav1.auction_header_id = p_auction_header_id
2269 AND pbav1.bid_number IN
2270 (SELECT pbh1.bid_number
2271 FROM pon_bid_headers pbh1
2272 WHERE pbh1.auction_header_id = p_auction_header_id
2273 AND NVL(pbh1.evaluation_flag, 'N') = 'N'
2274 AND pbh1.bid_status = 'ACTIVE'
2275 AND EXISTS (SELECT NULL
2276 FROM pon_bid_headers pbh4
2277 WHERE NVL(pbh4.evaluation_flag, 'N') = 'Y'
2278 AND pbh4.auction_header_id = p_auction_header_id
2279 AND pbh4.trading_partner_id = pbh1.trading_partner_id
2280 AND pbh4.bid_status = 'ACTIVE'
2281 )
2282 )
2283 AND pbav1.attribute_name IN
2284 (SELECT paa1.attribute_name
2285 FROM pon_auction_attributes paa1
2286 WHERE paa1.auction_header_id = p_auction_header_id
2287 AND paa1.internal_attr_flag = 'Y'
2288 AND paa1.line_number = -1
2289 )
2290 AND pbav1.auction_line_number = -1;
2291
2292 END IF;
2293
2294 END calculate_avg_eval_scores;
2295
2296 -- End Supplier Management: Supplier Evaluation
2297
2298 END PON_RESPONSE_PVT;