[Home] [Help]
PACKAGE BODY: APPS.PON_RESPONSE_PVT
Source
1 PACKAGE BODY PON_RESPONSE_PVT AS
2 -- $Header: PONRESPB.pls 120.34.12010000.3 2009/01/21 18:29:04 haoyang 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
367 -- or price tiers we don't have to proceed with the shipments
364 AND pbip.line_number <= v_batch_end ;
365
366 -- if price tiers indicator is not equal to either price breaks
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;
472 v_auction_header_id NUMBER;
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);
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;
599 SET pbip.bid_currency_trans_price = nvl(
596
597 -- (1) apply BPF formula
598 UPDATE pon_bid_item_prices pbip
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(spf.pricing_basis,
628 'PER_UNIT', spf.bid_currency_value,
629 'PERCENTAGE', spf.bid_currency_value/100 * pbip.bid_currency_unit_price,
630 (spf.bid_currency_value / decode(v_is_spo_transformed,
631 'Y', nvl(pbip.quantity, 1),
632 nvl(aip.quantity, 1))))),
633 0)
634 FROM pon_bid_price_elements spf,
635 pon_auction_item_prices_all aip
636 WHERE spf.bid_number = p_bid_number
637 AND spf.line_number = pbip.line_number
638 AND spf.sequence_number <> -10
639 AND aip.auction_header_id = spf.auction_header_id
640 AND aip.line_number = spf.line_number
641 AND spf.pf_type = 'SUPPLIER')
642 WHERE pbip.bid_number = p_bid_number
643 -- process only batch, or all lines if currency change
644 AND ((p_curr_changed = 'Y' AND
645 pbip.line_number >= v_batch_start AND
646 pbip.line_number <= v_batch_end)
647 OR pbip.batch_id = p_batch_id);
648
649 -- once we have the untruncated transformed bid-currency price
650 -- in bid_currency_trans_price column, copy that value over
651 -- to the other columns.
652 -- * unit_price: recalculate from untruncated
653 -- bid_currency_unit_price. (not rounded)
654 -- * bid_currency_unit_price: leave it as-is. (not rounded)
655 -- * price: recalculate from untruncated
656 -- bid_currency_trans_price. (not rounded)
657 -- * bid_currency_trans_price: truncate the untruncated
658 -- bid_currency_trans_price
659 -- * bid_currency_price: TRANSFORMED - bid_currency_trans_price truncated.
660 -- UNTRUNCATED - bid_currency_unit_price untruncated.
661 -- * Complex Work- For details on how bid_currency_advance_amount and Bid_currency_max_retainage
662 -- are calculated please look at ECO#4549930 for details. Here the logic is that bid currency values for
663 -- advance_amount and max_reatinage be converted into Bid currency if these are non negotaible
664 -- or have not been changed from buyer suggested values in negotiation. But if Supplier has touched
665 -- these values, they are left as it is and not converted. CAUTION- The logic for all the 4 complex
666 -- work fields is almost same. So, if you change one, also consider impact on others.
667
668 IF p_curr_changed = 'Y' THEN
669
670 UPDATE pon_bid_item_prices pbip
671 SET pbip.unit_price = pbip.bid_currency_unit_price / v_rate,
672 pbip.price = pbip.bid_currency_trans_price / v_rate,
673 pbip.bid_currency_price = DECODE(v_supplier_view_type,
674 'TRANSFORMED', round(pbip.bid_currency_trans_price, v_precision),
675 'UNTRANSFORMED', pbip.bid_currency_unit_price),
676 pbip.bid_currency_trans_price = round(pbip.bid_currency_trans_price, v_precision),
677 pbip.proxy_bid_limit_price = pbip.bid_currency_limit_price / v_rate,
678 pbip.po_min_rel_amount = pbip.po_bid_min_rel_amount / v_rate,
679 (pbip.bid_curr_advance_amount,
680 pbip.bid_curr_max_retainage_amt,
681 pbip.advance_amount,
682 pbip.max_retainage_amount)
683 = (SELECT nvl2(pbip.bid_curr_advance_amount,
684 decode(v_advance_negotiable, 'Y',
685 decode( pbip.advance_amount-paip.advance_amount, 0, round(pbip.advance_amount* v_rate,v_fnd_precision),pbip.bid_curr_advance_amount),
686 round(pbip.advance_amount* v_rate,v_fnd_precision)
687 ), pbip.bid_curr_advance_amount),
688
689 nvl2(pbip.bid_curr_max_retainage_amt,
690 decode(v_max_rtng_negotiable, 'Y',
694
691 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),
692 round(pbip.max_retainage_amount* v_rate,v_fnd_precision)
693 ), pbip.bid_curr_max_retainage_amt),
695 nvl2(pbip.advance_amount,
696 decode(v_advance_negotiable, 'Y',
697 decode( pbip.advance_amount-paip.advance_amount, 0, pbip.advance_amount,pbip.bid_curr_advance_amount/v_rate),
698 pbip.advance_amount
699 ), pbip.advance_amount),
700
701 nvl2(pbip.max_retainage_amount,
702 decode(v_max_rtng_negotiable, 'Y',
703 decode( pbip.max_retainage_amount-paip.max_retainage_amount, 0, pbip.max_retainage_amount,pbip.bid_curr_max_retainage_amt/v_rate),
704 pbip.max_retainage_amount
705 ), pbip.max_retainage_amount)
706 FROM pon_auction_item_prices_all paip
707 WHERE paip.auction_header_id=pbip.auction_header_id
708 AND paip.line_number=pbip.line_number)
709 WHERE pbip.bid_number = p_bid_number
710 -- process only batch, or all lines if currency change
711 AND ((p_curr_changed = 'Y' AND
712 pbip.line_number >= v_batch_start AND
713 pbip.line_number <= v_batch_end)
714 OR pbip.batch_id = p_batch_id);
715 ELSE
716
717 UPDATE pon_bid_item_prices pbip
718 SET pbip.unit_price = pbip.bid_currency_unit_price / v_rate,
719 pbip.price = pbip.bid_currency_trans_price / v_rate,
720 pbip.bid_currency_price = DECODE(v_supplier_view_type,
721 'TRANSFORMED', round(pbip.bid_currency_trans_price, v_precision),
722 'UNTRANSFORMED', pbip.bid_currency_unit_price),
723 pbip.bid_currency_trans_price = round(pbip.bid_currency_trans_price, v_precision),
724 pbip.proxy_bid_limit_price = pbip.bid_currency_limit_price / v_rate,
725 pbip.po_min_rel_amount = pbip.po_bid_min_rel_amount / v_rate,
726 pbip.advance_amount = pbip.bid_curr_advance_amount/ v_rate,
727 pbip.max_retainage_amount = pbip.bid_curr_max_retainage_amt/v_rate
728 WHERE pbip.bid_number = p_bid_number
729 -- process only batch, or all lines if currency change
730 AND ((p_curr_changed = 'Y' AND
731 pbip.line_number >= v_batch_start AND
732 pbip.line_number <= v_batch_end)
733 OR pbip.batch_id = p_batch_id);
734 END IF;
735
736 -- RECALCULATE SHIPMENT-LEVEL PRICES
737 -- there are two steps to recalculating auction-currency transformed
738 -- shipment price:
739 -- (1) apply BPF values by using pon_pf_supplier_formula
740 -- (2) apply SPF values by summing up all applicable SPF values from
741 -- pon_bid_price_elements table and adding to the result of (1)
742
743 -- NOTE: does not apply for spreadsheet upload, only if currency changed
744 --comment this out for price tier: IF (v_contract_type <> 'STANDARD') THEN
745
746 -- (1) apply BPF formula
747 UPDATE pon_bid_shipments pbs
748 SET pbs.bid_currency_price = nvl(
749 (SELECT (pbs.bid_currency_unit_price * ppsf.percentage) +
750 ppsf.unit_price*v_rate +
751 (ppsf.fixed_amount*v_rate / decode(v_is_spo_transformed,
752 'Y', nvl(pbip.quantity, 1),
753 nvl(aip.quantity, 1)))
754 FROM pon_pf_supplier_formula ppsf,
755 pon_bid_headers pbh,
756 pon_auction_item_prices_all aip,
757 pon_bid_item_prices pbip
758 WHERE pbip.bid_number = pbs.bid_number
759 AND pbip.line_number = pbs.line_number
760 AND ppsf.auction_header_id = pbip.auction_header_id
761 AND ppsf.line_number = pbip.line_number
762 AND pbip.bid_number = pbh.bid_number
763 AND ppsf.trading_partner_id = pbh.trading_partner_id
764 AND ppsf.vendor_site_id = pbh.vendor_site_id
765 AND aip.auction_header_id = pbip.auction_header_id
766 AND aip.line_number = pbip.line_number),
767 pbs.bid_currency_unit_price)
768 WHERE pbs.bid_number = p_bid_number
769 AND pbs.line_number >= v_batch_start
770 AND pbs.line_number <= v_batch_end;
771
772
773 -- (2) apply SPF values
774 UPDATE pon_bid_shipments pbs
775 SET pbs.bid_currency_price =
776 (SELECT pbs.bid_currency_price +
777 nvl(sum(decode(spf.pricing_basis,
778 'PER_UNIT', spf.bid_currency_value,
779 'PERCENTAGE', spf.bid_currency_value/100 * pbs.bid_currency_unit_price,
780 (spf.bid_currency_value / decode(v_is_spo_transformed,
781 'Y', nvl(pbip.quantity, 1),
782 nvl(aip.quantity, 1))))),
783 0)
784 FROM pon_bid_price_elements spf,
785 pon_auction_item_prices_all aip,
786 pon_bid_item_prices pbip
787 WHERE pbip.bid_number = pbs.bid_number
788 AND pbip.line_number = pbs.line_number
792 AND aip.auction_header_id = spf.auction_header_id
789 AND spf.bid_number = p_bid_number
790 AND spf.line_number = pbip.line_number
791 AND spf.sequence_number <> -10
793 AND aip.line_number = spf.line_number
794 AND spf.pf_type = 'SUPPLIER')
795 WHERE pbs.bid_number = p_bid_number
796 AND pbs.line_number >= v_batch_start
797 AND pbs.line_number <= v_batch_end;
798
799 -- once we have the untruncated transformed bid-currency price
800 -- in bid_currency_price column, copy that value over
801 -- to the other columns.
802 -- * unit_price: recalculate from untruncated
803 -- bid_currency_unit_price. (not rounded)
804 -- * bid_currency_unit_price: leave it as-is. (not rounded)
805 -- * price: recalculate from untruncated
806 -- bid_currency_price. (not rounded)
807 -- * bid_currency_price: truncate the untruncated
808 -- bid_currency_price
809 UPDATE pon_bid_shipments pbs
810 SET pbs.unit_price = pbs.bid_currency_unit_price / v_rate,
811 pbs.price = pbs.bid_currency_price / v_rate,
812 pbs.bid_currency_price = DECODE(v_supplier_view_type,
813 'TRANSFORMED', round(pbs.bid_currency_price, v_precision),
814 'UNTRANSFORMED', pbs.bid_currency_unit_price)
815 WHERE pbs.bid_number = p_bid_number
816 AND pbs.line_number >= v_batch_start
817 AND pbs.line_number <= v_batch_end;
818
819 --END IF;
820
821 -- RECALCULATE PAYMENT PRICES
822 -- Since Payments values are not tranformed ever, we can directly multiply by rate
823
824 IF (p_curr_changed = 'Y' AND v_contract_type = 'STANDARD' AND v_progress_payment_type <> 'NONE' ) THEN
825
826 UPDATE pon_bid_payments_shipments pbps
827 SET pbps.price = pbps.bid_currency_price / v_rate
828
829 WHERE pbps.bid_number = p_bid_number
830 AND pbps.bid_line_number >= v_batch_start
831 AND pbps.bid_line_number <= v_batch_end;
832
833 END IF;
834
835 v_batch_start := v_batch_end + 1;
836 IF (v_batch_end + v_batch_size > v_max_line_number) THEN
837 v_batch_end := v_max_line_number;
838 ELSE
839 v_batch_end := v_batch_end + v_batch_size;
840 END IF;
841
842 -- If we are recalculating after a currency change then we need
843 -- to commit because the procedure was executed in batches
844 -- In the other case(s), we do NOT want to commit because
845 -- the procedure was executed in a single batch only for lines
846 -- with p_batch_id and the calling procedure will do the commit (or rollback)
847 IF (p_curr_changed = 'Y') THEN
848 COMMIT;
849 END IF;
850
851 END LOOP;
852 END;
853
854
855
856 -- -------------------------------------------------------------------------
857 -- publish
858 --
859 -- called from BidHeadersEO.publish or the concurrent publish program, this
860 -- procedure will publish the draft
861 -- -------------------------------------------------------------------------
862
863 PROCEDURE publish(p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
864 p_bid_number IN pon_bid_headers.bid_number%TYPE,
865 p_rebid_flag IN VARCHAR,
866 p_publish_date IN pon_bid_headers.publish_date%TYPE,
867 p_tp_id IN pon_bid_headers.trading_partner_id%TYPE,
868 p_tpc_id IN pon_bid_headers.trading_partner_contact_id%TYPE,
869 p_user_id IN NUMBER,
870 p_batch_id IN NUMBER,
871 p_request_id IN NUMBER,
872 p_hdr_validation_failed IN VARCHAR,
873 x_return_status OUT NOCOPY NUMBER,
874 x_return_code OUT NOCOPY VARCHAR)
875 IS
876
877 v_auc_close_bidding_date pon_auction_headers_all.close_bidding_date%TYPE;
878 v_group_enabled_flag pon_auction_headers_all.group_enabled_flag%TYPE;
879 v_is_paused pon_auction_headers_all.is_paused%TYPE;
880 v_surrog_bid_receipt_date pon_bid_headers.surrog_bid_receipt_date%TYPE;
881 v_publish_date DATE;
882 v_auc_tp_id pon_auction_headers_all.trading_partner_id%TYPE;
883 v_two_part_flag pon_auction_headers_all.two_part_flag%TYPE;
884 v_sealed_auction_status pon_auction_headers_all.sealed_auction_status%TYPE;
885 --added by Allen Yang for Surrogate Bid 2008/09/08
886 --------------------------------------------------
887 l_two_part_flag pon_auction_headers_all.TWO_PART_FLAG%TYPE;
888 l_technical_evaluation_status pon_auction_headers_all.TECHNICAL_EVALUATION_STATUS%TYPE;
889 l_surrogate_bid_flag pon_bid_headers.SURROG_BID_FLAG%TYPE;
890 l_submit_stage pon_bid_headers.SUBMIT_STAGE%TYPE;
891 --------------------------------------------------
892 v_vendor_site_id pon_bid_headers.vendor_site_id%TYPE;
893 v_prev_bid_number pon_bid_headers.old_bid_number%TYPE;
894 v_surrog_bid_flag pon_bid_headers.surrog_bid_flag%TYPE;
895 v_tpc_name pon_bid_headers.trading_partner_contact_name%TYPE;
896
897 v_wf_item_key pon_bidding_parties.wf_item_key%TYPE;
898 v_user_added_to_role VARCHAR2(1);
899 v_price_changed VARCHAR2(1);
900
901 v_subroutine_return_status NUMBER;
902 v_subroutine_return_code VARCHAR2(30);
903
904 v_biz_return_status VARCHAR2(240);
905 v_biz_msg_count NUMBER;
909 v_maxLineNumber NUMBER;
906 v_biz_msg_data_value VARCHAR2(240);
907
908 --batching-related variables
910 v_batchSize NUMBER;
911 v_batchingRequired BOOLEAN;
912
913 l_api_name CONSTANT VARCHAR2(30) := 'publish';
914
915 BEGIN
916
917 x_return_status := 0;
918 x_return_code := '';
919 v_price_changed := 'N';
920 v_user_added_to_role := 'Y';
921 v_subroutine_return_status := 0;
922 v_subroutine_return_code := '';
923 v_is_paused := 'N';
924
925 --added by Allen Yang for Surrogate Bid 2008/09/08
926 --------------------------------------------------
927 --get the two-part stage of submitting quote
928 SELECT
929 paha.TWO_PART_FLAG
930 , paha.TECHNICAL_EVALUATION_STATUS
931 , pbh.SURROG_BID_FLAG
932 INTO
933 l_two_part_flag
934 , l_technical_evaluation_status
935 , l_surrogate_bid_flag
936 FROM
937 PON_AUCTION_HEADERS_ALL paha
938 , PON_BID_HEADERS pbh
939 WHERE paha.auction_header_id = pbh.auction_header_id
940 AND pbh.bid_number = p_bid_number;
941
942 IF ((nvl(l_two_part_flag, 'N') = 'Y') AND (nvl(l_surrogate_bid_flag, 'N') = 'Y'))
943 THEN
944 IF (nvl(l_technical_evaluation_status, 'NOT_COMPLETED') = 'NOT_COMPLETED')
945 THEN
946 l_submit_stage := 'TECHNICAL';
947 ELSIF l_technical_evaluation_status = 'COMPLETED'
948 THEN
949 l_submit_stage := 'COMMERCIAL';
950 END IF;
951 END IF;
952 --------------------------------------------------
953
954 -- (1) perform line-level validation.
955 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
956 print_log(l_api_name, p_bid_number || ' - performing pre-publish validation for draft bid ' || p_bid_number);
957 END IF;
958
959 pon_bid_validations_pkg.validate_bid(p_auction_header_id, p_bid_number, 'PUBLISHBID', p_user_id, p_batch_id, p_request_id,
960 v_subroutine_return_status, v_subroutine_return_code);
961
962 -- quit out if we have errors from the validation. return code will be VALIDATION_ERROR
963 IF v_subroutine_return_status = g_return_error THEN
964 x_return_status := g_return_error;
965 x_return_code := 'VALIDATION_ERROR';
966
967 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
968 print_log(l_api_name, p_bid_number || ' - validation returned with errors; exiting publish');
969 END IF;
970 RETURN;
971 END IF;
972
973 -- if p_hdr_validation_failed, it means that the Java-layer header-level
974 -- validation of this bid failed. We still want to process this publish
975 -- to find any line-level validation errors, but we do not want to proceed
976 -- with the actual publish. x_return_status for the publish routine
977 -- is set to 0 if no line-level validations are found; set to 1 if found (see above).
978 IF p_hdr_validation_failed = 'Y' THEN
979 x_return_status := g_return_success;
980
981 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
982 print_log(l_api_name, p_bid_number || ' - validation returned with no errors, but exiting publish because there are header-level validation errors');
983 END IF;
984 RETURN;
985 END IF;
986
987
988 -- lock the auction header
989 SELECT close_bidding_date, group_enabled_flag,
990 max_internal_line_num, trading_partner_id, sealed_auction_status, two_part_flag
991 INTO v_auc_close_bidding_date, v_group_enabled_flag,
992 v_maxLineNumber, v_auc_tp_id, v_sealed_auction_status, v_two_part_flag
993 FROM pon_auction_headers_all
994 WHERE auction_header_id = p_auction_header_id
995 FOR UPDATE OF CLOSE_BIDDING_DATE;
996
997 SELECT vendor_site_id, old_bid_number, surrog_bid_flag, trading_partner_contact_name
998 INTO v_vendor_site_id, v_prev_bid_number, v_surrog_bid_flag, v_tpc_name
999 FROM pon_bid_headers
1000 WHERE bid_number = p_bid_number;
1001
1002 -- (woojin) first do the validations to make sure that this
1003 -- bid is allowed to go through.
1004 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1005 print_log(l_api_name, p_bid_number || ' - checking is bid valid ');
1006 END IF;
1007 PON_AUCTION_HEADERS_PKG.check_is_bid_valid(p_auction_header_id,
1008 p_bid_number,
1009 v_vendor_site_id,
1010 v_prev_bid_number,
1011 p_rebid_flag,
1012 'N',
1013 v_surrog_bid_flag,
1014 p_publish_date,
1015 v_subroutine_return_status,
1016 v_subroutine_return_code);
1017
1018 -- if we have errors, then quit out of is_bidding_allowed
1019 -- and in turn, quit out of save draft or publish.
1020 IF v_subroutine_return_status = g_return_error THEN
1021 x_return_status := g_return_error;
1022 x_return_code := v_subroutine_return_code;
1023
1027 RETURN;
1024 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1025 print_log(l_api_name, p_bid_number || ' - check_is_bid_valid failed with error: ' || v_subroutine_return_code || '; exiting publish.');
1026 END IF;
1028 END IF;
1029
1030 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1031 print_log(l_api_name, p_bid_number || ' - after check is bid valid ');
1032 END IF;
1033
1034
1035 --------------------------------------------------------------------------------
1036 -- END OF VALIDATION. BELOW, WE PROCEED WITH PUBLISH
1037 --------------------------------------------------------------------------------
1038
1039 v_batchSize := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
1040 v_batchingRequired := (v_maxLineNumber > v_batchSize);
1041 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1042 print_log(l_api_name, p_bid_number || ' - batching required? batch size=' || v_batchSize || '; numOfLines=' || v_maxLineNumber);
1043 END IF;
1044
1045 -- (2) remove empty rows (lines, children)
1046 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1047 print_log(l_api_name, p_bid_number || ' - remove empty rows');
1048 END IF;
1049 -- modified by Allen Yang for surrogate bid bug 7702461 2009/01/22
1050 --------------------------------------------------------------------
1051 -- only for those quotes not submitted on technical stage, remove empty rows.
1052 IF ( l_submit_stage IS NULL OR l_submit_stage <> 'TECHNICAL')
1053 THEN
1054 IF (v_batchingRequired) THEN
1055 remove_empty_rows_auto(p_bid_number, v_maxLineNumber, v_batchSize);
1056 ELSE
1057 remove_empty_rows(p_bid_number, 1, v_maxLineNumber);
1058 END IF;
1059 END IF;
1060 /*
1061 IF (v_batchingRequired) THEN
1062 remove_empty_rows_auto(p_bid_number, v_maxLineNumber, v_batchSize);
1063 ELSE
1064 remove_empty_rows(p_bid_number, 1, v_maxLineNumber);
1065 END IF;
1066 */
1067 ---------------------------------------------------------------------
1068
1069 -- (3) calculate group amounts
1070 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1071 print_log(l_api_name, p_bid_number || ' - calculate group amounts? ' || v_group_enabled_flag);
1072 END IF;
1073
1074 -- (woojin) commented out this portion because group amount
1075 -- is calculated in update_auction_info/update_proxy_bids after
1076 -- proxies are calculated. The calculation here is not only
1077 -- useless, but sometimes may yield in wrong results.
1078 --
1079 -- (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
1080 -- use 'N' instead of 'Y' when calling calculate_group_amounts_auto()
1081 -- to use buyer currency in gourp_amount
1082 IF v_group_enabled_flag = 'Y' THEN
1083 IF (v_batchingRequired) THEN
1084 calculate_group_amounts_auto(p_bid_number, 'N');
1085 ELSE
1086 calculate_group_amounts(p_bid_number, 'N');
1087 END IF ;
1088 END IF;
1089
1090 -- (4) set miscellaneous header-level attributes
1091 -- - close_bidding_date = sysdate if auction is still open
1092 -- surrog bid receipt date if closed
1093 -- - shortlist_flag = default to 'Y'
1094 -- - surrog_bid_online_entry_date = sysdate
1095
1096 IF (v_surrog_bid_flag = 'Y') THEN
1097 SELECT surrog_bid_receipt_date
1098 INTO v_surrog_bid_receipt_date
1099 FROM pon_bid_headers
1100 WHERE bid_number = p_bid_number;
1101 v_publish_date := v_surrog_bid_receipt_date;
1102 ELSE
1103 v_publish_date := p_publish_date;
1104 END IF;
1105
1106 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1107 print_log(l_api_name, p_bid_number || ' - set header-level attributes');
1108 END IF;
1109 IF (v_batchingRequired) THEN
1110 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);
1111 ELSE
1112 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);
1113 END IF;
1114
1115 -- (5) set miscellaneous line-level attributes
1116 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1117 print_log(l_api_name, p_bid_number || ' - publish lines');
1118 END IF;
1119 IF (v_batchingRequired) THEN
1120 publish_lines_auto(p_auction_header_id, p_bid_number, v_publish_date,
1121 p_tp_id, v_auc_tp_id, v_surrog_bid_flag, p_rebid_flag, v_maxLineNumber, v_batchSize);
1122 ELSE
1123 publish_lines(p_auction_header_id, p_bid_number, v_publish_date,
1124 p_tp_id, v_auc_tp_id, v_surrog_bid_flag, p_rebid_flag, 1, v_maxLineNumber);
1125 END IF;
1126
1127 -- (7) perform the things that used to be in BidHeadersEO.beforeCommit()
1128 -- check_auction_bidder
1129 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1130 print_log(l_api_name, p_bid_number || ' - check auction bidder');
1131 END IF;
1135 print_log(l_api_name, p_bid_number || ' - check auction bidder; user not added to role');
1132 PON_AUCTION_PKG.CHECK_AUCTION_BIDDER(p_tpc_id, p_auction_header_id, v_subroutine_return_status);
1133 IF v_subroutine_return_status = 1 THEN
1134 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1136 END IF;
1137 v_user_added_to_role := 'N';
1138 END IF;
1139
1140
1141 -- update_auction_info
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 || ' - update auction info');
1144 END IF;
1145 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);
1146
1147 IF v_subroutine_return_status = g_return_error THEN
1148 x_return_code := v_subroutine_return_code;
1149 x_return_status := g_return_error;
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 || ' - update auction info; error code=' || x_return_code);
1152 END IF;
1153 RETURN;
1154 ELSIF v_subroutine_return_status = g_return_warning THEN
1155 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1156 print_log(l_api_name, p_bid_number || ' - update auction info; price changed');
1157 END IF;
1158 v_price_changed := 'Y';
1159 END IF;
1160
1161 -- ackResponse stuff -- only perform when this is not a rebid
1162 IF p_rebid_flag = 'N' THEN
1163 BEGIN
1164 SELECT wf_item_key
1165 INTO v_wf_item_key
1166 FROM pon_bidding_parties
1167 WHERE auction_header_id = p_auction_header_id
1168 AND trading_partner_id = p_tp_id
1169 AND nvl(supp_acknowledgement, 'N') = 'N'
1170 AND rownum=1;
1171 EXCEPTION
1172 WHEN NO_DATA_FOUND THEN
1173 v_wf_item_key := NULL;
1174 END;
1175
1176 IF v_wf_item_key IS NOT NULL THEN
1177 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1178 print_log(l_api_name, p_bid_number || ' - ack notif response');
1179 END IF;
1180 PON_AUCTION_PKG.ACK_NOTIF_RESPONSE(v_wf_item_key, v_tpc_name, 'Y','', v_subroutine_return_status);
1181
1182 IF v_subroutine_return_status = 1 THEN
1183 IF (g_fnd_debug = 'Y') THEN
1184 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
1185 FND_LOG.string(log_level => FND_LOG.level_exception,
1186 module => g_module_prefix || l_api_name,
1187 message => 'Response acknowledge notification cannot be sent for bid ' || p_bid_number);
1188 END IF;
1189 END IF;
1190 END IF;
1191 END IF;
1192 END IF;
1193
1194 IF v_user_added_to_role = 'N' THEN
1195 IF v_price_changed = 'Y' THEN
1196 x_return_status := g_return_warning;
1197 x_return_code := 'PRICE_CHANGED_AND_USER_NOT_ADDED_TO_ROLE';
1198 ELSE
1199 x_return_status := g_return_warning;
1200 x_return_code := 'USER_NOT_ADDED_TO_ROLE';
1201 END IF;
1202 ELSIF v_price_changed = 'Y' THEN
1203 x_return_status := g_return_warning;
1204 x_return_code := 'PRICE_CHANGED';
1205 ELSE
1206 x_return_status := g_return_success;
1207 END IF;
1208
1209 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1210 print_log(l_api_name, p_bid_number || ' - return status: ' || x_return_status || '; return code: ' || x_return_code);
1211 print_log(l_api_name, p_bid_number || ' - call send_resp_notif');
1212 END IF;
1213
1214 --Call the Workflow. Don not raise any error even if is not successful
1215 PON_AUCTION_PKG.SEND_RESP_NOTIF(p_bid_number => p_bid_number,
1216 x_return_status =>v_subroutine_return_code);
1217
1218 -- raise response publish business event (uday's code)
1219 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1220 print_log(l_api_name, p_bid_number || ' - call raise response publish business event');
1221 END IF;
1222 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);
1223
1224 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1225 print_log(l_api_name, 'v_biz_return_status=' || v_biz_return_status);
1226 print_log(l_api_name, 'v_biz_msg_count=' || v_biz_msg_count);
1227 print_log(l_api_name, 'v_biz_msg_data_value=' || v_biz_msg_data_value);
1228 print_log(l_api_name, p_bid_number || ' - finally PUBLISH the bid by setting bid_status to ACTIVE!!');
1229 END IF;
1230
1231 -- unlock and publish
1232 -- if this is a draft bid then need to release lock
1233 UPDATE pon_bid_headers
1234 SET draft_locked = 'N',
1235 draft_unlocked_by = p_tp_id,
1236 draft_unlocked_by_contact_id = p_tpc_id,
1237 draft_unlocked_date = sysdate,
1238 bid_status = 'ACTIVE'
1239 --added by Allen Yang for Surrogate Bid 2008/09/08
1240 --------------------------------------------------
1241 , submit_stage = l_submit_stage
1245 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1242 --------------------------------------------------
1243 WHERE bid_number = p_bid_number;
1244
1246 print_log(l_api_name, p_bid_number || ' - exiting publish successfully');
1247 END IF;
1248
1249
1250 END publish;
1251
1252
1253
1254
1255 -- -------------------------------------------------------------------------
1256 -- remove_empty_rows
1257 --
1258 -- called from publish_bid above, this method will remove empty lines and
1259 -- children so that we only commit lines with bids to the database upon
1260 -- publish
1261 -- -------------------------------------------------------------------------
1262
1263 PROCEDURE remove_empty_rows
1264 (p_bid_number IN pon_bid_headers.bid_number%TYPE,
1265 p_batch_start IN NUMBER,
1266 p_batch_end IN NUMBER)
1267
1268 IS
1269
1270 l_api_name CONSTANT VARCHAR2(30) := 'remove_empty_rows';
1271
1272 BEGIN
1273
1274 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1275 print_log(l_api_name, p_bid_number || ' - begin remove empty rows for batch range ' || p_batch_start || ' ~ ' || p_batch_end || ' inclusive');
1276 END IF;
1277
1278 -- 1) empty attributes (including header attributes)
1279 -- 2) display only attributes (including header attributes)
1280 -- 3) attributes on lines that have hasBid = N
1281 DELETE FROM pon_bid_attribute_values pbav
1282 WHERE pbav.bid_number = p_bid_number
1283 AND (EXISTS (SELECT null
1284 FROM pon_auction_attributes paa
1285 WHERE pbav.auction_header_id = paa.auction_header_id
1286 AND pbav.line_number = paa.line_number
1287 AND pbav.attribute_name = paa.attribute_name
1288 AND paa.display_only_flag = 'Y')
1289 OR pbav.value IS NULL
1290 OR EXISTS (SELECT null
1291 FROM pon_bid_item_prices pbip
1292 WHERE pbip.bid_number = pbav.bid_number
1293 AND pbip.line_number = pbav.line_number
1294 AND pbip.has_bid_flag = 'N'))
1295 AND pbav.line_number >= p_batch_start
1296 AND pbav.line_number <= p_batch_end;
1297
1298 -- remove:
1299 -- 1) empty price elements
1300 -- 2) price elements for lines with hasBid=N
1301 DELETE FROM pon_bid_price_elements pbpe
1302 WHERE pbpe.bid_number = p_bid_number
1303 AND EXISTS (SELECT null
1304 FROM pon_bid_item_prices pbip
1305 WHERE pbip.bid_number = pbpe.bid_number
1306 AND pbip.line_number = pbpe.line_number
1307 AND pbip.has_bid_flag = 'N')
1308 AND pbpe.line_number >= p_batch_start
1309 AND pbpe.line_number <= p_batch_end;
1310
1311 -- remove:
1312 -- 1) empty price_differentials (line-level, shipment-level)
1313 -- 2) price differentials for lines with hasBid=N (line-level, shipment-level)
1314 DELETE FROM pon_bid_price_differentials pbpd
1315 WHERE pbpd.bid_number = p_bid_number
1316 AND EXISTS (SELECT null
1317 FROM pon_bid_item_prices pbip
1318 WHERE pbip.bid_number = pbpd.bid_number
1319 AND pbip.line_number = pbpd.line_number
1320 AND pbip.has_bid_flag = 'N')
1321 AND pbpd.line_number >= p_batch_start
1322 AND pbpd.line_number <= p_batch_end;
1323
1324
1325 -- remove:
1326 -- 1) shipments for lines with hasBid=N
1327 DELETE FROM pon_bid_shipments pbs
1328 WHERE pbs.bid_number = p_bid_number
1329 AND EXISTS (SELECT null
1330 FROM pon_bid_item_prices pbip
1331 WHERE pbip.bid_number = pbs.bid_number
1332 AND pbip.line_number = pbs.line_number
1333 AND pbip.has_bid_flag = 'N')
1334 AND pbs.line_number >= p_batch_start
1335 AND pbs.line_number <= p_batch_end;
1336
1337 -- remove:
1338 -- 1) Payments for lines with hasBid=N
1339 DELETE FROM pon_bid_payments_shipments pbps
1340 WHERE pbps.bid_number = p_bid_number
1341 AND EXISTS (SELECT null
1342 FROM pon_bid_item_prices pbip
1343 WHERE pbip.bid_number = pbps.bid_number
1344 AND pbip.line_number = pbps.bid_line_number
1345 AND pbip.has_bid_flag = 'N')
1346 AND pbps.bid_line_number >= p_batch_start
1347 AND pbps.bid_line_number <= p_batch_end;
1348
1349 -- remove empty lines finally
1350 DELETE FROM pon_bid_item_prices
1351 WHERE bid_number = p_bid_number
1352 AND has_bid_flag = 'N'
1353 AND line_number >= p_batch_start
1354 AND line_number <= p_batch_end;
1355
1356 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1357 print_log(l_api_name, p_bid_number || ' - end remove empty rows for batch range ' || p_batch_start || ' ~ ' || p_batch_end || ' inclusive');
1358 END IF;
1359 END remove_empty_rows;
1360
1361
1362 PROCEDURE remove_empty_rows_auto
1363 (p_bid_number IN pon_bid_headers.bid_number%TYPE,
1364 p_max_line_number IN NUMBER,
1365 p_batch_size IN NUMBER)
1366 IS
1367 PRAGMA AUTONOMOUS_TRANSACTION;
1368
1369 l_api_name CONSTANT VARCHAR2(30) := 'remove_empty_rows_auto';
1373 BEGIN
1370 v_batch_start NUMBER;
1371 v_batch_end NUMBER;
1372
1374
1375 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1376 print_log(l_api_name, p_bid_number || ' - BEGIN remove empty rows AUTONOMOUS');
1377 END IF;
1378
1379 v_batch_start := 1;
1380 IF (p_max_line_number < p_batch_size) THEN
1381 v_batch_end := p_max_line_number;
1382 ELSE
1383 v_batch_end := p_batch_size;
1384 END IF;
1385
1386 WHILE (v_batch_start <= p_max_line_number) LOOP
1387
1388 remove_empty_rows(p_bid_number, v_batch_start, v_batch_end);
1389 commit;
1390
1391 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1392 print_log(l_api_name, p_bid_number || ' - remove empty rows: batch committed');
1393 END IF;
1394
1395 v_batch_start := v_batch_end + 1;
1396 IF (v_batch_end + p_batch_size > p_max_line_number) THEN
1397 v_batch_end := p_max_line_number;
1398 ELSE
1399 v_batch_end := v_batch_end + p_batch_size;
1400 END IF;
1401 END LOOP;
1402
1403 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1404 print_log(l_api_name, p_bid_number || ' - END remove empty rows AUTONOMOUS');
1405 END IF;
1406 END remove_empty_rows_auto;
1407
1408
1409
1410 -- -------------------------------------------------------------------------
1411 -- update_bid_header_fields
1412 --
1413 -- called from publish above, this method will update a few fields in
1414 -- bid header
1415 -- -------------------------------------------------------------------------
1416
1417 PROCEDURE update_bid_header_fields
1418 (p_bid_number IN pon_bid_headers.bid_number%TYPE,
1419 p_publish_date IN pon_bid_headers.publish_date%TYPE,
1420 p_bid_entry_date IN pon_bid_headers.publish_date%TYPE,
1421 p_surrog_bid_flag IN pon_bid_headers.surrog_bid_flag%TYPE,
1422 p_two_part_flag IN pon_auction_headers_all.two_part_flag%TYPE,
1423 p_sealed_auction_status IN pon_auction_headers_all.sealed_auction_status%TYPE)
1424 IS
1425
1426 l_api_name CONSTANT VARCHAR2(30) := 'update_bid_header_fields';
1427 l_tech_shortlist_flag VARCHAR2(1) := null;
1428
1429 BEGIN
1430
1431 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1432 print_log(l_api_name, p_bid_number || ' - begin update bid header fields');
1433 print_log(l_api_name, p_bid_number || ' - update bid header fields: p_publish_date=' || p_publish_date);
1434 END IF;
1435
1436 IF (p_surrog_bid_flag = 'Y' AND p_two_part_flag = 'Y' AND p_sealed_auction_status <> 'LOCKED') THEN
1437 l_tech_shortlist_flag := 'Y';
1438 END IF;
1439
1440
1441 UPDATE pon_bid_headers
1442 SET publish_date = p_publish_date,
1443 shortlist_flag = 'Y',
1444 surrog_bid_online_entry_date = p_bid_entry_date,
1445 technical_shortlist_flag = l_tech_shortlist_flag
1446 --,bid_status = 'ACTIVE'
1447 WHERE bid_number = p_bid_number;
1448
1449 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1450 print_log(l_api_name, p_bid_number || ' - end update bid header fields');
1451 END IF;
1452 END update_bid_header_fields;
1453
1454
1455 PROCEDURE update_bid_header_fields_auto
1456 (p_bid_number IN pon_bid_headers.bid_number%TYPE,
1457 p_publish_date IN pon_bid_headers.publish_date%TYPE,
1458 p_bid_entry_date IN pon_bid_headers.publish_date%TYPE,
1459 p_surrog_bid_flag IN pon_bid_headers.surrog_bid_flag%TYPE,
1460 p_two_part_flag IN pon_auction_headers_all.two_part_flag%TYPE,
1461 p_sealed_auction_status IN pon_auction_headers_all.sealed_auction_status%TYPE)
1462 IS
1463 PRAGMA AUTONOMOUS_TRANSACTION;
1464
1465 l_api_name CONSTANT VARCHAR2(30) := 'update_bid_header_fields_auto';
1466 BEGIN
1467 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1468 print_log(l_api_name, p_bid_number || ' - BEGIN update bid header fields AUTONOMOUS');
1469 END IF;
1470
1471 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);
1472 commit;
1473
1474 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1475 print_log(l_api_name, p_bid_number || ' - update bid header fields: committed!');
1476 print_log(l_api_name, p_bid_number || ' - END update bid header fields AUTONOMOUS');
1477 END IF;
1478
1479 END update_bid_header_fields_auto;
1480
1481
1482 -- -------------------------------------------------------------------------
1483 -- publish_lines
1484 --
1485 -- called from publish_bid above, this method will perform line-level
1486 -- publish processing, such as setting line-level publish date.
1487 -- -------------------------------------------------------------------------
1488
1489 PROCEDURE publish_lines
1490 (p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
1491 p_bid_number IN pon_bid_headers.bid_number%TYPE,
1492 p_publish_date IN DATE,
1493 p_tp_id IN pon_bid_headers.trading_partner_id%TYPE,
1494 p_auc_tp_id IN pon_auction_headers_all.trading_partner_id%TYPE,
1495 p_surrog_bid_flag IN pon_bid_headers.surrog_bid_flag%TYPE,
1496 p_rebid_flag IN VARCHAR,
1500 IS
1497 p_batch_start IN NUMBER,
1498 p_batch_end IN NUMBER)
1499
1501
1502 v_full_quantity_bid_code pon_auction_headers_all.full_quantity_bid_code%TYPE;
1503 v_surrog_bid_flag pon_bid_headers.surrog_bid_flag%TYPE;
1504 v_auc_tp_id pon_auction_headers_all.trading_partner_id%TYPE;
1505 v_auction_header_id NUMBER;
1506 l_api_name CONSTANT VARCHAR2(30) := 'publish_lines';
1507
1508 BEGIN
1509
1510 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1511 print_log(l_api_name, p_bid_number || ' - begin publish lines for batch range: ' || p_batch_start || ' ~ ' || p_batch_end || ' inclusive');
1512 END IF;
1513
1514 IF p_rebid_flag = 'N' THEN
1515
1516 UPDATE pon_bid_item_prices
1517 SET publish_date = p_publish_date,
1518 proxy_bid_flag = 'N',
1519 award_price = price,
1520 first_bid_price = price, --(woojin) do we really need this?
1521 bid_trading_partner_id = decode(p_surrog_bid_flag,
1522 'Y', p_auc_tp_id, p_tp_id)
1523 WHERE bid_number = p_bid_number
1524 AND line_number >= p_batch_start
1525 AND line_number <= p_batch_end;
1526
1527 ELSE
1528
1529 -- then set publish date to current time for isPublishedLines
1530 -- also, set proxy_bid_flag to 'N'
1531 -- Please note that the is_changed_line_flag is used in the procedure
1532 -- PONAUCHB.check_is_bid_valid to determine the newly added/modified
1533 -- lines. In case this logic is changed do ensure that the check_is_bid_valid
1534 -- procedure is also modified
1535 UPDATE pon_bid_item_prices
1536 SET publish_date = p_publish_date,
1537 proxy_bid_flag = 'N',
1538 award_price = price,
1539 first_bid_price = nvl(first_bid_price, price), --(woojin) do we really need this?
1540 bid_trading_partner_id = decode(p_surrog_bid_flag,
1541 'Y', p_auc_tp_id, p_tp_id)
1542 WHERE bid_number = p_bid_number
1543 AND is_changed_line_flag = 'Y'
1544 AND line_number >= p_batch_start
1545 AND line_number <= p_batch_end;
1546
1547 END IF;
1548
1549 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1550 print_log(l_api_name, p_bid_number || ' - end publish lines for batch range: ' || p_batch_start || ' ~ ' || p_batch_end || ' inclusive');
1551 END IF;
1552
1553 END;
1554
1555
1556 PROCEDURE publish_lines_auto
1557 (p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
1558 p_bid_number IN pon_bid_headers.bid_number%TYPE,
1559 p_publish_date IN DATE,
1560 p_tp_id IN pon_bid_headers.trading_partner_id%TYPE,
1561 p_auc_tp_id IN pon_auction_headers_all.trading_partner_id%TYPE,
1562 p_surrog_bid_flag IN pon_bid_headers.surrog_bid_flag%TYPE,
1563 p_rebid_flag IN VARCHAR,
1564 p_max_line_number IN NUMBER,
1565 p_batch_size IN NUMBER)
1566 IS
1567 PRAGMA AUTONOMOUS_TRANSACTION;
1568
1569 v_batch_start NUMBER;
1570 v_batch_end NUMBER;
1571 l_api_name CONSTANT VARCHAR2(30) := 'publish_lines_auto';
1572
1573 BEGIN
1574
1575 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1576 print_log(l_api_name, p_bid_number || ' - BEGIN publish lines AUTONOMOUS');
1577 END IF;
1578
1579 v_batch_start := 1;
1580 IF (p_max_line_number < p_batch_size) THEN
1581 v_batch_end := p_max_line_number;
1582 ELSE
1583 v_batch_end := p_batch_size;
1584 END IF;
1585
1586 WHILE (v_batch_start <= p_max_line_number) LOOP
1587
1588 publish_lines(p_auction_header_id, p_bid_number, p_publish_date, p_tp_id,
1589 p_auc_tp_id, p_surrog_bid_flag, p_rebid_flag, v_batch_start, v_batch_end);
1590 commit;
1591
1592 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1593 print_log(l_api_name, p_bid_number || ' - publish lines: batch committed!');
1594 END IF;
1595
1596 v_batch_start := v_batch_end + 1;
1597 IF (v_batch_end + p_batch_size > p_max_line_number) THEN
1598 v_batch_end := p_max_line_number;
1599 ELSE
1600 v_batch_end := v_batch_end + p_batch_size;
1601 END IF;
1602 END LOOP;
1603
1604 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1605 print_log(l_api_name, p_bid_number || ' - END publish lines AUTONOMOUS');
1606 END IF;
1607
1608 END publish_lines_auto;
1609
1610 -- -------------------------------------------------------------------------
1611 -- publish_cp
1612 --
1613 -- this is the concurrent program that will be called for super-large
1614 -- negotiations.
1615 -- -------------------------------------------------------------------------
1616
1617 PROCEDURE publish_cp
1618 (errbuf OUT NOCOPY VARCHAR2,
1619 retcode OUT NOCOPY VARCHAR2,
1620 p_auction_header_id IN NUMBER,
1621 p_bid_number IN NUMBER,
1622 p_rebid_flag IN VARCHAR2,
1623 p_publish_date IN VARCHAR2,
1624 p_date_mask IN VARCHAR2,
1625 p_tp_id IN NUMBER,
1626 p_tpc_id IN NUMBER,
1627 p_user_type IN VARCHAR2,
1628 p_user_id IN NUMBER)
1629 IS
1630 v_request_id NUMBER;
1634 v_publish_date DATE;
1631 v_error_msg_name VARCHAR2(200);
1632 v_return_status NUMBER;
1633 v_return_code VARCHAR2(50);
1635 v_user_name VARCHAR2(50);
1636
1637 l_api_name CONSTANT VARCHAR2(30) := 'publish_cp';
1638
1639 BEGIN
1640
1641 retcode := '0';
1642 errbuf := '';
1643 v_request_id := FND_GLOBAL.CONC_REQUEST_ID();
1644 v_publish_date := to_date(p_publish_date, p_date_mask);
1645
1646 SELECT user_name
1647 INTO v_user_name
1648 FROM fnd_user
1649 WHERE user_id = p_user_id;
1650
1651 publish(p_auction_header_id, p_bid_number, p_rebid_flag,
1652 v_publish_date, p_tp_id, p_tpc_id, p_user_id, null,
1653 v_request_id, 'N', v_return_status, v_return_code);
1654
1655
1656 IF v_return_status = 0 OR v_return_status = 2 THEN
1657 errbuf := 'PUBLISH_CP exited successfully';
1658 retcode := '0';
1659
1660 -- clear out the request_id once this CP exits successfully.
1661 UPDATE pon_bid_headers
1662 SET request_id = null
1663 WHERE bid_number = p_bid_number;
1664
1665 PON_WF_UTL_PKG.ReportConcProgramStatus (
1666 p_request_id => v_request_id,
1667 p_messagetype => 'S',
1668 p_RecepientUsername => v_user_name,
1669 p_recepientType =>p_user_type,
1670 p_auction_header_id => p_auction_header_id,
1671 p_ProgramTypeCode => 'BID_PUBLISH',
1672 p_DestinationPageCode => 'PONRESENQ_VIEWBID',
1673 p_bid_number => p_bid_number);
1674
1675 -- if we have an error, then check
1676 -- what kind of errors they are.
1677 ELSIF v_return_status = g_return_error THEN
1678
1679 -- if they're validation errors, the errors
1680 -- have been put into pon_interface_errors already
1681 IF v_return_code = 'VALIDATION_ERROR' THEN
1682 errbuf := 'PUBLISH_CP exited with validation errors';
1683 retcode := '2';
1684
1685 -- if the error thrown is non-validation, we need to push
1686 -- that error into pon_interface_errors. This error is most
1687 -- likely something updateAuctionInfo-related
1688 ELSE
1689 errbuf := 'PUBLISH_CP exited with publish errors';
1690 retcode := '2';
1691
1692 -- insert the publish error into pon_interface_errors table
1693 get_message_name(v_return_code, p_auction_header_id, v_error_msg_name);
1694
1695 INSERT INTO pon_interface_errors
1696 (bid_number, auction_header_id, interface_type, request_id,
1697 error_message_name, expiration_date)
1698 VALUES
1699 (p_bid_number, p_auction_header_id, 'PUBLISHBID', v_request_id,
1700 v_error_msg_name, sysdate+7);
1701
1702 END IF;
1703
1704 PON_WF_UTL_PKG.ReportConcProgramStatus (
1705 p_request_id => v_request_id,
1706 p_messagetype => 'E',
1707 p_RecepientUsername => v_user_name,
1708 p_recepientType =>p_user_type,
1709 p_auction_header_id => p_auction_header_id,
1710 p_ProgramTypeCode => 'BID_PUBLISH',
1711 p_DestinationPageCode => 'PON_CONCURRENT_ERRORS',
1712 p_bid_number => p_bid_number);
1713
1714 END IF;
1715
1716 commit;
1717
1718 EXCEPTION
1719 WHEN others THEN
1720
1721 -- insert an error into the FND LOG as well
1722 IF (g_fnd_debug = 'Y') THEN
1723 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
1724 FND_LOG.string(log_level => FND_LOG.level_exception,
1725 module => g_module_prefix || l_api_name,
1726 message => 'SQL error code: ' || sqlcode || ', error message: ' || substr(sqlerrm,1,512));
1727 END IF;
1728 END IF;
1729
1730 -- rollback anything that we can
1731 rollback;
1732
1733 -- report error to the user through workflow notifications
1734 PON_WF_UTL_PKG.ReportConcProgramStatus (
1735 p_request_id => v_request_id,
1736 p_messagetype => 'E',
1737 p_RecepientUsername => v_user_name,
1738 p_recepientType =>p_user_type,
1739 p_auction_header_id => p_auction_header_id,
1740 p_ProgramTypeCode => 'BID_PUBLISH',
1741 p_DestinationPageCode => 'PON_CONCURRENT_ERRORS',
1742 p_bid_number => p_bid_number);
1743
1744 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1745 print_log(l_api_name, 'Generating WF notif: ' || v_request_id || ' ' || v_user_name || ' ' || p_user_type || ' '||p_auction_header_id|| ' ' );
1746 END IF;
1747
1748 -- insert an error indicating that a fatal error in the
1749 -- publish CP has occurred.
1750 get_message_name('FATAL_PUBLISH_ERROR', p_auction_header_id, v_error_msg_name);
1751
1752 INSERT INTO pon_interface_errors
1753 (bid_number, auction_header_id, interface_type, request_id,
1754 error_message_name, expiration_date)
1755 VALUES
1756 (p_bid_number, p_auction_header_id, 'PUBLISHBID', v_request_id,
1757 v_error_msg_name, sysdate+7);
1758
1759 -- insert an error into the FND LOG as well
1760 IF (g_fnd_debug = 'Y') THEN
1764 message => 'A fatal error has occurred during the concurrent processing of response publish for response number ' || p_bid_number);
1761 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
1762 FND_LOG.string(log_level => FND_LOG.level_exception,
1763 module => g_module_prefix || l_api_name,
1765 END IF;
1766 END IF;
1767
1768 -- set bid header's bid_status to DRAFT
1769 UPDATE pon_bid_headers
1770 SET bid_status = 'DRAFT'
1771 WHERE bid_number = p_bid_number;
1772
1773 -- commit the changes made here in the exception block
1774 commit;
1775
1776 END publish_cp;
1777
1778
1779
1780 -- -------------------------------------------------------------------------
1781 -- validate_cp
1782 --
1783 -- this is the concurrent program that will be called for super-large
1784 -- negotiations.
1785 -- -------------------------------------------------------------------------
1786
1787
1788 PROCEDURE validate_cp
1789 (errbuf OUT NOCOPY VARCHAR2,
1790 retcode OUT NOCOPY VARCHAR2,
1791 p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
1792 p_bid_number IN pon_bid_headers.bid_number%TYPE,
1793 p_user_type IN VARCHAR2,
1794 p_user_id IN NUMBER)
1795 IS
1796
1797 v_request_id NUMBER;
1798 v_subroutine_return_status NUMBER;
1799 v_subroutine_return_code VARCHAR2(30);
1800 v_user_name VARCHAR2(50);
1801 v_error_msg_name VARCHAR2(200);
1802 v_success_destination VARCHAR2(500);
1803
1804 l_api_name CONSTANT VARCHAR2(30) := 'validate_cp';
1805
1806 BEGIN
1807
1808 retcode := '0';
1809 errbuf := '';
1810 v_request_id := FND_GLOBAL.CONC_REQUEST_ID();
1811
1812 SELECT user_name
1813 INTO v_user_name
1814 FROM fnd_user
1815 WHERE user_id = p_user_id;
1816
1817 SELECT decode (nvl (surrog_bid_flag, 'N'), 'Y', 'PONENQMGDR_MANAGEDRAFT_SURROG', 'PONENQMGDR_MANAGEDRAFT')
1818 INTO v_success_destination
1819 FROM pon_bid_headers
1820 WHERE bid_number = p_bid_number;
1821
1822 -- perform line-level validation.
1823 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);
1824
1825 IF v_subroutine_return_status = g_return_success THEN
1826 -- according to ECO 4517992, we don't clear out the request_id
1827 -- except for publish bid
1828
1829 PON_WF_UTL_PKG.ReportConcProgramStatus (
1830 p_request_id => v_request_id,
1831 p_messagetype => 'S',
1832 p_RecepientUsername => v_user_name,
1833 p_recepientType =>p_user_type,
1834 p_auction_header_id => p_auction_header_id,
1835 p_ProgramTypeCode => 'BID_VALIDATE',
1836 p_DestinationPageCode => v_success_destination,
1837 p_bid_number => p_bid_number);
1838
1839 errbuf := 'VALIDATE_CP exited successfully';
1840 retcode := '0';
1841 ELSE
1842
1843 PON_WF_UTL_PKG.ReportConcProgramStatus (
1844 p_request_id => v_request_id,
1845 p_messagetype => 'E',
1846 p_RecepientUsername => v_user_name,
1847 p_recepientType =>p_user_type,
1848 p_auction_header_id => p_auction_header_id,
1849 p_ProgramTypeCode => 'BID_VALIDATE',
1850 p_DestinationPageCode => 'PON_CONCURRENT_ERRORS',
1851 p_bid_number => p_bid_number);
1852
1853 errbuf := 'VALIDATE_CP returned validation errors';
1854 retcode := '2';
1855 END IF;
1856
1857 commit;
1858
1859
1860 EXCEPTION
1861 WHEN others THEN
1862
1863 -- insert an error into the FND LOG as well
1864 IF (g_fnd_debug = 'Y') THEN
1865 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
1866 FND_LOG.string(log_level => FND_LOG.level_exception,
1867 module => g_module_prefix || l_api_name,
1868 message => 'SQL error code: ' || sqlcode || ', error message: ' || substr(sqlerrm,1,512));
1869 END IF;
1870 END IF;
1871
1872
1873 -- rollback anything that we can
1874 rollback;
1875
1876 -- report error to the user through workflow notifications
1877 PON_WF_UTL_PKG.ReportConcProgramStatus (
1878 p_request_id => v_request_id,
1879 p_messagetype => 'E',
1880 p_RecepientUsername => v_user_name,
1881 p_recepientType =>p_user_type,
1882 p_auction_header_id => p_auction_header_id,
1883 p_ProgramTypeCode => 'BID_VALIDATE',
1884 p_DestinationPageCode => 'PON_CONCURRENT_ERRORS',
1885 p_bid_number => p_bid_number);
1886
1887 IF ((g_fnd_debug = 'Y') AND (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)) THEN
1888 print_log(l_api_name, 'Generating WF notif: ' || v_request_id || ' ' || v_user_name || ' ' || p_user_type || ' '||p_auction_header_id|| ' ' );
1889 END IF;
1890
1891 -- insert an error indicating that a fatal error in the
1892 -- publish CP has occurred.
1893 get_message_name('FATAL_VALIDATE_ERROR', p_auction_header_id, v_error_msg_name);
1894
1895 INSERT INTO pon_interface_errors
1896 (bid_number, auction_header_id, interface_type, request_id,
1897 error_message_name, expiration_date)
1898 VALUES
1902 -- insert an error into the FND LOG as well
1899 (p_bid_number, p_auction_header_id, 'VALIDATEBID', v_request_id,
1900 v_error_msg_name, sysdate+7);
1901
1903 IF (g_fnd_debug = 'Y') THEN
1904 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
1905 FND_LOG.string(log_level => FND_LOG.level_exception,
1906 module => g_module_prefix || l_api_name,
1907 message => 'A fatal error has occurred during the concurrent processing of response validation for response number ' || p_bid_number || '. See above for error details');
1908 END IF;
1909 END IF;
1910
1911 -- commit the changes made here in the exception block
1912 commit;
1913
1914 END validate_cp;
1915
1916
1917 -- -------------------------------------------------------------------------
1918 -- get_message_name
1919 --
1920 -- called by publish_cp, this will return the message name to be inserted
1921 -- into pon_interface_errors table
1922 -- -------------------------------------------------------------------------
1923
1924 PROCEDURE get_message_name(p_msg_code IN VARCHAR2,
1925 p_auction_header_id IN NUMBER,
1926 x_msg_name OUT NOCOPY VARCHAR2)
1927 IS
1928
1929 v_suffix VARCHAR2(1);
1930
1931 l_api_name CONSTANT VARCHAR2(30) := 'get_message_name';
1932
1933 BEGIN
1934
1935 SELECT message_suffix
1936 INTO v_suffix
1937 FROM pon_auc_doctypes
1938 WHERE doctype_id = (SELECT doctype_id
1939 FROM pon_auction_headers_all
1940 WHERE auction_header_id = p_auction_header_id);
1941
1942 IF p_msg_code = 'DISQ_REBID' THEN
1943 x_msg_name := 'PON_BID_DISQUALIFIED_REBID_' || v_suffix;
1944 ELSIF p_msg_code = 'MULTIPLE_REBID' THEN
1945 x_msg_name := 'PON_BID_MULTIPLE_REBID_' || v_suffix;
1946 ELSIF p_msg_code = 'MULTIPLE_DRAFTS' THEN
1947 x_msg_name := 'PON_BID_MULTIPLE_DRAFTS_' || v_suffix;
1948 ELSIF p_msg_code = 'SINGLE_BEST_BID' THEN
1949 x_msg_name := 'PON_AUC_BIDERROR_1_' || v_suffix;
1950 ELSIF p_msg_code = 'SINGLE_BEST_DRAFT' THEN
1951 x_msg_name := 'PON_AUC_BIDERROR_1_' || v_suffix;
1952 ELSIF p_msg_code = 'AUCTION_PAUSED' THEN
1953 x_msg_name := 'PON_AUC_PAUSED_DRAFT_' || v_suffix;
1954 ELSIF p_msg_code = 'BID_ON_CLOSED_LINE' THEN
1955 x_msg_name := 'PON_AUCTION_LINE_CLOSED_ERR_' || v_suffix;
1956 ELSIF p_msg_code = 'FATAL_PUBLISH_ERROR' THEN
1957 x_msg_name := 'PON_AUC_FATAL_BID_CP_PUB_ERR_' || v_suffix;
1958 ELSIF p_msg_code = 'FATAL_VALIDATE_ERROR' THEN
1959 x_msg_name := 'PON_AUC_FATAL_BID_CP_VAL_ERR_' || v_suffix;
1960 ELSE x_msg_name := '';
1961 END IF;
1962
1963 END get_message_name;
1964
1965 PROCEDURE get_user_lang_message (p_tpc_id IN NUMBER,
1966 p_message_name IN VARCHAR2,
1967 p_message_token1_name IN VARCHAR2,
1968 p_message_token1_value IN VARCHAR2,
1969 p_message_token2_name IN VARCHAR2,
1970 p_message_token2_value IN VARCHAR2,
1971 x_message_text OUT NOCOPY VARCHAR2)
1972 IS
1973
1974 l_language_code FND_LANGUAGES.LANGUAGE_CODE%TYPE;
1975 l_api_name CONSTANT VARCHAR2(30) := 'get_user_lang_message';
1976 l_user_id FND_USER.USER_ID%TYPE;
1977 BEGIN
1978
1979 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1980 FND_LOG.string(log_level => FND_LOG.level_procedure,
1981 module => g_module_prefix || l_api_name,
1982 message => 'Entered with p_tpc_id = ' || p_tpc_id ||
1983 ', p_message_name = ' || p_message_name ||
1984 ', p_message_token1_name = ' || p_message_token1_name ||
1985 ', p_message_token1_value = ' || p_message_token1_value ||
1986 ', p_message_token2_name = ' || p_message_token2_name ||
1987 ', p_message_token2_value = ' || p_message_token2_value);
1988 END IF;
1989
1990 SELECT
1991 FND_USER.user_id
1992 INTO
1993 l_user_id
1994 FROM
1995 FND_USER
1996 WHERE
1997 FND_USER.PERSON_PARTY_ID = p_tpc_id
1998 AND ROWNUM=1;
1999
2000 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2001 FND_LOG.string(log_level => FND_LOG.level_statement,
2002 module => g_module_prefix || l_api_name,
2003 message => 'l_user_id = ' || l_user_id);
2004 END IF;
2005
2006 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE (l_user_id, l_language_code);
2007
2008 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2009 FND_LOG.string(log_level => FND_LOG.level_statement,
2010 module => g_module_prefix || l_api_name,
2011 message => 'l_language_code = ' || l_language_code);
2012 END IF;
2013
2014 PON_AUCTION_PKG.SET_SESSION_LANGUAGE (null, l_language_code);
2015 x_message_text := PON_AUCTION_PKG.getMessage (
2016 msg => p_message_name,
2017 msg_suffix => '',
2018 token1 => p_message_token1_name,
2019 token1_value => p_message_token1_value,
2020 token2 => p_message_token2_name,
2021 token2_value => p_message_token2_value);
2022
2023 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2024 FND_LOG.string(log_level => FND_LOG.level_statement,
2025 module => g_module_prefix || l_api_name,
2026 message => 'x_message_text = ' || x_message_text);
2027 END IF;
2028
2029 PON_AUCTION_PKG.UNSET_SESSION_LANGUAGE;
2030
2031 EXCEPTION WHEN OTHERS THEN
2032 x_message_text := '';
2033 PON_AUCTION_PKG.UNSET_SESSION_LANGUAGE;
2034
2035 END get_user_lang_message;
2036
2037 END PON_RESPONSE_PVT;