1 PACKAGE BODY PON_NEGOTIATION_HELPER_PVT AS
2 /* $Header: PONNEGHB.pls 120.30 2007/07/24 08:31:08 tarkumar ship $ */
3
4 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.PON_NEGOTIATION_HELPER_PVT.';
5
6 /*======================================================================
7 PROCEDURE : get_search_min_disp_line_num
8 PARAMETERS: 1. p_auction_header_id - The auction header id
9 2. p_value - The value entered by the user for search
10 3. x_min_disp_line_num - Out parameter to indicate at which
11 line to start displaying
12 COMMENT : This procedure is invoked when the user searches on the
13 lines region with line number as the search criteria
14 and greater than as the search condition.
15 Given the value entered by the user (p_value) this
16 procedure will return the disp_line_number above which
17 all lines should be shown.
18 ======================================================================*/
19
20 PROCEDURE get_search_min_disp_line_num (
21 p_auction_header_id IN NUMBER,
22 p_value IN NUMBER,
23 x_min_disp_line_num OUT NOCOPY NUMBER
24 ) IS
25 BEGIN
26
27 IF (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 || 'GET_SEARCH_MIN_DISP_LINE_NUM',
30 message => 'Entering PON_NEGOTIATION_HELPER_PVT.GET_SEARCH_MIN_DISP_LINE_NUM '
31 || ', p_auction_header_id = ' || p_auction_header_id
32 || ', p_value = ' || p_value);
33 END IF;
34
35 --Retrieve the minimum disp_line_number of all the LOT/GROUP/LINES
36 --that have SUB_LINE_SEQUENCE_NUMBER > p_value
37
38 SELECT MIN(disp_line_number)
39 INTO x_min_disp_line_num
40 FROM PON_AUCTION_ITEM_PRICES_ALL
41 WHERE AUCTION_HEADER_ID = p_auction_header_id
42 AND GROUP_TYPE IN ('LOT', 'GROUP', 'LINE')
43 AND SUB_LINE_SEQUENCE_NUMBER > p_value;
44
45 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
46 FND_LOG.string(log_level => FND_LOG.level_statement,
47 module => g_module_prefix || 'GET_SEARCH_MIN_DISP_LINE_NUM',
48 message => 'Leaving PON_NEGOTIATION_HELPER_PVT.GET_SEARCH_MIN_DISP_LINE_NUM'
49 || 'x_min_disp_line_num = ' || x_min_disp_line_num);
50 END IF;
51
52 END GET_SEARCH_MIN_DISP_LINE_NUM;
53
54 /*======================================================================
55 PROCEDURE : get_search_max_disp_line_num
56 PARAMETERS: 1. p_auction_header_id - The auction header id
57 2. p_value - The value entered by the user for search
58 3. x_max_disp_line_num - Out parameter to indicate at which
59 line to stop displaying
60 COMMENT : This procedure is invoked when the user searches on the
61 lines region with line number as the search criteria
62 and less than as the search condition.
63 Given the value entered by the user (p_value) this
64 procedure will return the disp_line_number below which
65 all lines should be shown.
66 ======================================================================*/
67
68 PROCEDURE GET_SEARCH_MAX_DISP_LINE_NUM (
69 p_auction_header_id IN NUMBER,
70 p_value IN NUMBER,
71 x_max_disp_line_num OUT NOCOPY NUMBER
72 ) IS
73
74 l_line_number PON_AUCTION_ITEM_PRICES_ALL.LINE_NUMBER%TYPE;
75 l_group_type PON_AUCTION_ITEM_PRICES_ALL.GROUP_TYPE%TYPE;
76 BEGIN
77
78 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
79 FND_LOG.string(log_level => FND_LOG.level_statement,
80 module => g_module_prefix || 'GET_SEARCH_MAX_DISP_LINE_NUM',
81 message => 'Entering PON_NEGOTIATION_HELPER_PVT.GET_SEARCH_MAX_DISP_LINE_NUM '
82 || ', p_auction_header_id = ' || p_auction_header_id
83 || ', p_value = ' || p_value);
84 END IF;
85
86 --Retrieve the maximum disp_line_number of all the LOT/GROUP/LINES
87 --that have SUB_LINE_SEQUENCE_NUMBER < p_value
88
89 SELECT MAX(DISP_LINE_NUMBER)
90 INTO x_max_disp_line_num
91 FROM PON_AUCTION_ITEM_PRICES_ALL
92 WHERE AUCTION_HEADER_ID = p_auction_header_id
93 AND GROUP_TYPE IN ('LOT', 'LINE', 'GROUP')
94 AND SUB_LINE_SEQUENCE_NUMBER < p_value;
95
96 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
97 FND_LOG.string(log_level => FND_LOG.level_statement,
98 module => g_module_prefix || 'GET_SEARCH_MAX_DISP_LINE_NUM',
99 message => 'After the first query x_max_disp_line_num = ' ||
100 x_max_disp_line_num);
101 END IF;
102
103 IF (x_max_disp_line_num IS NULL) THEN
104
105 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
106 FND_LOG.string(log_level => FND_LOG.level_statement,
107 module => g_module_prefix || 'GET_SEARCH_MAX_DISP_LINE_NUM',
108 message => 'There are no lines so returning null');
109 END IF;
110
111 RETURN;
112 END IF;
113
114 SELECT GROUP_TYPE, LINE_NUMBER
115 INTO l_group_type, l_line_number
116 FROM PON_AUCTION_ITEM_PRICES_ALL
117 WHERE AUCTION_HEADER_ID = p_auction_header_id
118 AND DISP_LINE_NUMBER = x_max_disp_line_num;
119
120 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
121 FND_LOG.string(log_level => FND_LOG.level_statement,
122 module => g_module_prefix || 'GET_SEARCH_MAX_DISP_LINE_NUM',
123 message => 'l_group_type = ' || l_group_type
124 || ', l_line_number = ' || l_line_number);
125 END IF;
126
127 --If the selected line is a LOT/GROUP then get the maximum
128 --disp_line_number within that LOT/GROUP
129
130 IF (l_group_type <> 'LINE') THEN
131 SELECT NVL (MAX(DISP_LINE_NUMBER), x_max_disp_line_num)
132 INTO x_max_disp_line_num
133 FROM PON_AUCTION_ITEM_PRICES_ALL
134 WHERE AUCTION_HEADER_ID = p_auction_header_id
135 AND PARENT_LINE_NUMBER = l_line_number;
136 END IF;
137
138 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
139 FND_LOG.string(log_level => FND_LOG.level_statement,
140 module => g_module_prefix || 'GET_SEARCH_MAX_DISP_LINE_NUM',
141 message => 'Leaving PON_NEGOTIATION_HELPER_PVT.GET_AUCTION_REQUEST_ID '
142 || ', x_max_disp_line_num = ' || x_max_disp_line_num);
143 END IF;
144
145 END GET_SEARCH_MAX_DISP_LINE_NUM;
146
147 PROCEDURE GET_AUCTION_REQUEST_ID (
148 p_auction_header_id IN NUMBER,
149 x_request_id OUT NOCOPY NUMBER
150 ) IS
151 BEGIN
152
153 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
154 FND_LOG.string(log_level => FND_LOG.level_statement,
155 module => g_module_prefix || 'GET_AUCTION_REQUEST_ID',
156 message => 'Entering PON_NEGOTIATION_HELPER_PVT.GET_AUCTION_REQUEST_ID '
157 || ', p_auction_header_id = ' || p_auction_header_id);
158 END IF;
159
160 SELECT REQUEST_ID
161 INTO x_request_id
162 FROM PON_AUCTION_HEADERS_ALL
163 WHERE AUCTION_HEADER_ID = p_auction_header_id;
164
165 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
166 FND_LOG.string(log_level => FND_LOG.level_statement,
167 module => g_module_prefix || 'GET_AUCTION_REQUEST_ID',
168 message => 'Leaving PON_NEGOTIATION_HELPER_PVT.GET_AUCTION_REQUEST_ID '
169 || ', x_request_id = ' || x_request_id);
170 END IF;
171
172 EXCEPTION WHEN NO_DATA_FOUND THEN
173 x_request_id := NULL;
174
175 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
176 FND_LOG.string(log_level => FND_LOG.level_exception,
177 module => g_module_prefix || 'GET_AUCTION_REQUEST_ID',
178 message => 'Exception in PON_NEGOTIATION_HELPER_PVT.GET_AUCTION_REQUEST_ID '
179 || 'errnum = ' || SQLCODE || ', errmsg = ' || SUBSTR (SQLERRM, 1, 200));
180 END IF;
181
182 END GET_AUCTION_REQUEST_ID;
183
184 /*======================================================================
185 PROCEDURE : has_fixed_amt_or_per_unit_pe
186 PARAMETERS: 1. p_auction_header_id - The auction header id
187 2. x_has_fixed_amt_or_per_unit_pe - return value - Y if there
188 are fixed amount or per unit price elements else N.
189 3. x_result - return status.
190 4. x_error_code - error code
191 5. x_error_message - The actual error message
192 COMMENT : This procedure will return Y if there are any
193 fixed amount or per unit price elements
194 ======================================================================*/
195
196 PROCEDURE HAS_FIXED_AMT_OR_PER_UNIT_PE(
197 p_auction_header_id IN NUMBER,
198 x_has_fixed_amt_or_per_unit_pe OUT NOCOPY VARCHAR2,
199 x_result OUT NOCOPY VARCHAR2,
200 x_error_code OUT NOCOPY VARCHAR2,
201 x_error_message OUT NOCOPY VARCHAR2
202 ) IS
203
204 l_line_number NUMBER;
205 BEGIN
206
207 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.g_current_runtime_level) THEN
208 FND_LOG.string (log_level => FND_LOG.level_statement,
209 module => g_module_prefix || 'HAS_FIXED_AMT_OR_PER_UNIT_PE',
210 message => 'Entered procedure with p_auction_header_id = ' || p_auction_header_id);
211 END IF;
212
213 x_result := FND_API.G_RET_STS_SUCCESS;
214
215 SELECT
216 LINE_NUMBER
217 INTO
218 l_line_number
219 FROM
220 PON_PRICE_ELEMENTS
221 WHERE
222 AUCTION_HEADER_ID = p_auction_header_id AND
223 (PRICING_BASIS = 'FIXED_AMOUNT' OR PRICING_BASIS = 'PER_UNIT') AND
224 ROWNUM = 1;
225
226 x_has_fixed_amt_or_per_unit_pe := 'Y';
227
228 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.g_current_runtime_level) THEN
229 FND_LOG.string (log_level => FND_LOG.level_statement,
230 module => g_module_prefix || 'HAS_FIXED_AMT_OR_PER_UNIT_PE',
231 message => 'Returning x_has_fixed_amt_or_per_unit_pe = ' || x_has_fixed_amt_or_per_unit_pe);
232 END IF;
233
234 EXCEPTION
235 WHEN NO_DATA_FOUND THEN --{
236 x_has_fixed_amt_or_per_unit_pe := 'N';
237
238 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.g_current_runtime_level) THEN
239 FND_LOG.string (log_level => FND_LOG.level_statement,
240 module => g_module_prefix || 'HAS_FIXED_AMT_OR_PER_UNIT_PE',
241 message => 'Returning x_has_fixed_amt_or_per_unit_pe = ' || x_has_fixed_amt_or_per_unit_pe);
242 END IF;
243
244 WHEN OTHERS THEN
245 x_result := FND_API.G_RET_STS_UNEXP_ERROR;
246 x_error_code := SQLCODE;
247 x_error_message := SUBSTR(SQLERRM, 1, 100);
248
249 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.g_current_runtime_level) THEN
250 FND_LOG.string (log_level => FND_LOG.level_exception,
251 module => g_module_prefix || 'HAS_FIXED_AMT_OR_PER_UNIT_PE',
252 message => 'Exception occured while checking for fixed amount or per unit price elements'
253 || ' Error Number :' || SQLCODE || ' Exception Message :' || SUBSTR(SQLERRM, 1, 200) );
254 END IF;
255
256 END HAS_FIXED_AMT_OR_PER_UNIT_PE;
257
258 /*======================================================================
259 PROCEDURE : has_goods_line_fixed_amount_pe
260 PARAMETERS: 1. p_auction_header_id - The auction header id
261 2. x_has_goods_line_fixed_amount_pe - return value - Y if there
262 are goods lines with fixed amt price elements
263 3. x_result - return status.
264 4. x_error_code - error code
265 5. x_error_message - The actual error message
266 COMMENT : This procedure will return Y if there are any goods lines
267 with fixed amount price elements
268 ======================================================================*/
269
270 PROCEDURE HAS_GOODS_LINE_FIXED_AMOUNT_PE(
271 p_auction_header_id IN NUMBER,
272 x_has_goods_line_fixed_amt_pe OUT NOCOPY VARCHAR2,
273 x_result OUT NOCOPY VARCHAR2,
274 x_error_code OUT NOCOPY VARCHAR2,
275 x_error_message OUT NOCOPY VARCHAR2
276 ) IS
277
278 l_line_number NUMBER;
279 BEGIN
280
281 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.g_current_runtime_level) THEN
282 FND_LOG.string (log_level => FND_LOG.level_statement,
283 module => g_module_prefix || 'HAS_GOODS_LINE_FIXED_AMOUNT_PE',
284 message => 'Entered procedure with p_auction_header_id = ' || p_auction_header_id);
285 END IF;
286
287 x_result := FND_API.G_RET_STS_SUCCESS;
288
289 SELECT
290 PAIP.LINE_NUMBER
291 INTO
292 l_line_number
293 FROM
294 PON_AUCTION_ITEM_PRICES_ALL PAIP,
295 PON_PRICE_ELEMENTS PPE
296 WHERE
297 PAIP.AUCTION_HEADER_ID = p_auction_header_id AND
298 PPE.AUCTION_HEADER_ID = p_auction_header_id AND
299 PAIP.LINE_NUMBER = PPE.LINE_NUMBER AND
300 PAIP.PURCHASE_BASIS = 'GOODS' AND
301 PPE.PRICING_BASIS = 'FIXED_AMOUNT' AND
302 ROWNUM = 1;
303
304 x_has_goods_line_fixed_amt_pe := 'Y';
305
306 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.g_current_runtime_level) THEN
307 FND_LOG.string (log_level => FND_LOG.level_statement,
308 module => g_module_prefix || 'HAS_GOODS_LINE_FIXED_AMOUNT_PE',
309 message => 'Returning x_has_goods_line_fixed_amt_pe = ' || x_has_goods_line_fixed_amt_pe);
310 END IF;
311 EXCEPTION
312 WHEN NO_DATA_FOUND THEN --{
313 x_has_goods_line_fixed_amt_pe := 'N';
314
315 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.g_current_runtime_level) THEN
316 FND_LOG.string (log_level => FND_LOG.level_statement,
317 module => g_module_prefix || 'HAS_GOODS_LINE_FIXED_AMOUNT_PE',
318 message => 'Returning x_has_goods_line_fixed_amt_pe = ' || x_has_goods_line_fixed_amt_pe);
319 END IF;
320
321 WHEN OTHERS THEN
322 x_result := FND_API.G_RET_STS_UNEXP_ERROR;
323 x_error_code := SQLCODE;
324 x_error_message := SUBSTR(SQLERRM, 1, 100);
325
326 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.g_current_runtime_level) THEN
327 FND_LOG.string (log_level => FND_LOG.level_exception,
328 module => g_module_prefix || 'HAS_GOODS_LINE_FIXED_AMOUNT_PE',
329 message => 'Exception occured while checking for goods lines with fixed amount price elements'
330 || ' Error Number :' || SQLCODE || ' Exception Message :' || SUBSTR(SQLERRM, 1, 200) );
331 END IF;
332
333 END HAS_GOODS_LINE_FIXED_AMOUNT_PE;
334
335 /*======================================================================
336 PROCEDURE : get_max_internal_and_doc_line_num
337 PARAMETERS: 1. p_auction_header_id - The auction header id
338 2. x_max_internal_line_num - The maximum internal line
339 number in all the rounds
340 3. x_max_document_line_num - The maximum subline sequence
341 number in all the rounds
342 4. x_result - return status.
343 5. x_error_code - error code
344 6. x_error_message - The actual error message
345 COMMENT : This procedure will return the maximum value of the
346 LINE_NUMBER and SUB_LINE_SEQUENCE_NUMBER columns in all
347 the rounds
348 ======================================================================*/
349
350 PROCEDURE GET_MAX_INTERNAL_AND_DOC_NUM (
351 p_auction_header_id IN NUMBER,
352 x_max_internal_line_num OUT NOCOPY NUMBER,
353 x_max_document_line_num OUT NOCOPY NUMBER,
354 x_max_disp_line_num OUT NOCOPY NUMBER,
355 x_result OUT NOCOPY VARCHAR2,
356 x_error_code OUT NOCOPY VARCHAR2,
357 x_error_message OUT NOCOPY VARCHAR2
358 ) IS
359
360 l_number_of_lines NUMBER;
361 BEGIN
362
363 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.g_current_runtime_level) THEN
364 FND_LOG.string (log_level => FND_LOG.level_statement,
365 module => g_module_prefix || 'GET_MAX_INTERNAL_AND_DOC_NUM',
366 message => 'Entered procedure with p_auction_header_id = ' || p_auction_header_id);
367 END IF;
368
369 x_result := FND_API.G_RET_STS_SUCCESS;
370
371
372 --We are not performing an outer join between header and lines here as it might
373 --be costly in case of a super large negotiation
374 x_max_disp_line_num := 0;
375
376 SELECT
377 NVL (number_of_lines, 0),
378 NVL (max_internal_line_num, 0),
379 NVL (max_document_line_num, 0)
380 INTO
381 l_number_of_lines,
382 x_max_internal_line_num,
383 x_max_document_line_num
384 FROM
385 pon_auction_headers_all
386 WHERE
387 auction_header_id = p_auction_header_id;
388
389 IF (l_number_of_lines > 0) THEN
390
391 SELECT
392 GREATEST (x_max_internal_line_num, NVL(MAX(items.line_number),0)),
393 GREATEST (x_max_document_line_num, NVL(MAX(DECODE (items.group_type, 'LOT_LINE', 0, 'GROUP_LINE', 0, items.sub_line_sequence_number)),0)),
394 NVL (MAX(items.disp_line_number), 0)
395 INTO
396 x_max_internal_line_num,
397 x_max_document_line_num,
398 x_max_disp_line_num
399 FROM
400 pon_auction_item_prices_all items
401 WHERE
402 items.auction_header_id = p_auction_header_id;
403
404 END IF;
405
406 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.g_current_runtime_level) THEN
407 FND_LOG.string (log_level => FND_LOG.level_statement,
408 module => g_module_prefix || 'GET_MAX_INTERNAL_AND_DOC_NUM',
409 message => 'Returning x_max_internal_line_num = ' || x_max_internal_line_num ||
410 ', x_max_document_line_num = ' || x_max_document_line_num ||
411 ', x_max_disp_line_num = ' || x_max_disp_line_num);
412 END IF;
413
414 EXCEPTION
415 WHEN NO_DATA_FOUND THEN
416 x_max_disp_line_num := 0;
417 x_max_document_line_num := 0;
418 x_max_internal_line_num := 0;
419
420 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.g_current_runtime_level) THEN
421 FND_LOG.string (log_level => FND_LOG.level_statement,
422 module => g_module_prefix || 'GET_MAX_INTERNAL_AND_DOC_NUM',
423 message => 'Returning x_max_internal_line_num = ' || x_max_internal_line_num ||
424 ', x_max_document_line_num = ' || x_max_document_line_num ||
425 ', x_max_disp_line_num = ' || x_max_disp_line_num);
426 END IF;
427 WHEN OTHERS THEN
428 x_result := FND_API.G_RET_STS_UNEXP_ERROR;
429 x_error_code := SQLCODE;
430 x_error_message := SUBSTR(SQLERRM, 1, 100);
431
432 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.g_current_runtime_level) THEN
433 FND_LOG.string (log_level => FND_LOG.level_exception,
434 module => g_module_prefix || 'GET_MAX_INTERNAL_AND_DOC_NUM',
435 message => 'Exception occured while getting the sequences'
436 || ' Error Number :' || SQLCODE || ' Exception Message :' || SUBSTR(SQLERRM, 1, 200) );
437 END IF;
438 END GET_MAX_INTERNAL_AND_DOC_NUM;
439
440 /*======================================================================
441 PROCEDURE : get_number_of_lines
442 PARAMETERS: 1. p_auction_header_id - The auction header id
443 2. x_number_of_lines - Return value containing
444 the number of lines.
445 3. x_result - return status.
446 4. x_error_code - error code
447 5. x_error_message - The actual error message
448 COMMENT : This procedure will return the number of lines in the
449 negotiation.
450 ======================================================================*/
451
452 PROCEDURE GET_NUMBER_OF_LINES (
453 p_auction_header_id IN NUMBER,
454 x_number_of_lines OUT NOCOPY NUMBER,
455 x_result OUT NOCOPY VARCHAR2,
456 x_error_code OUT NOCOPY VARCHAR2,
457 x_error_message OUT NOCOPY VARCHAR2
458 ) IS
459 BEGIN
460
461 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.g_current_runtime_level) THEN
462 FND_LOG.string (log_level => FND_LOG.level_statement,
463 module => g_module_prefix || 'GET_NUMBER_OF_LINES',
464 message => 'Entered procedure with p_auction_header_id = ' || p_auction_header_id);
465 END IF;
466
467 x_result := FND_API.G_RET_STS_SUCCESS;
468
469 SELECT
470 COUNT(LINE_NUMBER)
471 INTO
472 x_number_of_lines
473 FROM
474 PON_AUCTION_ITEM_PRICES_ALL
475 WHERE
476 AUCTION_HEADER_ID = p_auction_header_id;
477
478 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.g_current_runtime_level) THEN
479 FND_LOG.string (log_level => FND_LOG.level_statement,
480 module => g_module_prefix || 'GET_NUMBER_OF_LINES',
481 message => 'Returning x_number_of_lines = ' || x_number_of_lines);
482 END IF;
483
484 EXCEPTION WHEN OTHERS THEN
485 x_result := FND_API.G_RET_STS_UNEXP_ERROR;
486 x_error_code := SQLCODE;
487 x_error_message := SUBSTR(SQLERRM, 1, 100);
488
489 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.g_current_runtime_level) THEN
490 FND_LOG.string (log_level => FND_LOG.level_exception,
491 module => g_module_prefix || 'GET_NUMBER_OF_LINES',
492 message => 'Exception occured while getting the number of lines'
493 || ' Error Number :' || SQLCODE || ' Exception Message :' || SUBSTR(SQLERRM, 1, 200) );
494 END IF;
495
496 END GET_NUMBER_OF_LINES;
497
498 /*======================================================================
499 PROCEDURE : has_items
500 PARAMETERS: 1. p_auction_header_id - The auction header id
501 2. x_has_items - return value Y if there
502 items else N.
503 3. x_result - return status.
504 4. x_error_code - error code
505 5. x_error_message - The actual error message
506 COMMENT : This method returns Y if there are any items present
507 in the negotiation. else it will return N
508 ======================================================================*/
509
510 PROCEDURE HAS_ITEMS (
511 p_auction_header_id IN NUMBER,
512 x_has_items OUT NOCOPY VARCHAR2,
513 x_result OUT NOCOPY VARCHAR2,
514 x_error_code OUT NOCOPY VARCHAR2,
515 x_error_message OUT NOCOPY VARCHAR2
516 ) IS
517
518 l_line_number NUMBER;
519 BEGIN
520
521 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.g_current_runtime_level) THEN
522 FND_LOG.string (log_level => FND_LOG.level_statement,
523 module => g_module_prefix || 'HAS_ITEMS',
524 message => 'Entered procedure with p_auction_header_id = ' || p_auction_header_id);
525 END IF;
526
527 x_result := FND_API.G_RET_STS_SUCCESS;
528
529 SELECT
530 LINE_NUMBER
531 INTO
532 l_line_number
533 FROM
534 PON_AUCTION_ITEM_PRICES_ALL
535 WHERE
536 AUCTION_HEADER_ID = p_auction_header_id AND
537 ROWNUM = 1;
538
539 x_has_items := 'Y';
540
541 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.g_current_runtime_level) THEN
542 FND_LOG.string (log_level => FND_LOG.level_statement,
543 module => g_module_prefix || 'HAS_ITEMS',
544 message => 'Returning x_has_items = ' || x_has_items);
545 END IF;
546 EXCEPTION
547 WHEN NO_DATA_FOUND THEN --{
548
549 x_has_items := 'N';
550
551 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.g_current_runtime_level) THEN
552 FND_LOG.string (log_level => FND_LOG.level_statement,
553 module => g_module_prefix || 'HAS_ITEMS',
554 message => 'Returning x_has_items = ' || x_has_items);
555 END IF;
556
557 WHEN OTHERS THEN
558 x_result := FND_API.G_RET_STS_UNEXP_ERROR;
559 x_error_code := SQLCODE;
560 x_error_message := SUBSTR(SQLERRM, 1, 100);
561
562 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.g_current_runtime_level) THEN
563 FND_LOG.string (log_level => FND_LOG.level_exception,
564 module => g_module_prefix || 'HAS_ITEMS',
565 message => 'Exception occured while checking if items are present'
566 || ' Error Number :' || SQLCODE || ' Exception Message :' || SUBSTR(SQLERRM, 1, 200) );
567 END IF;
568 END HAS_ITEMS;
569
570 /*======================================================================
571 PROCEDURE : remove_score
572 PARAMETERS: 1. x_result - return status.
573 2. x_error_code - error code
574 3. x_error_message - The actual error message
575 4. p_auction_header_id - The auction header id
576 COMMENT : This procedure will remove the scoring information from
577 the given negotiation.
578 ======================================================================*/
579
580 PROCEDURE remove_score (
581 x_result OUT NOCOPY VARCHAR2,
582 x_error_code OUT NOCOPY VARCHAR2,
583 x_error_message OUT NOCOPY VARCHAR2,
584 p_auction_header_id IN NUMBER
585 ) IS
586
587 l_module_name VARCHAR2 (30);
588
589 l_max_line_number NUMBER;
590 l_batch_size NUMBER;
591
592 l_batch_start NUMBER;
593 l_batch_end NUMBER;
594
595 BEGIN
596
597 l_module_name := 'remove_score';
598 x_result := FND_API.g_ret_sts_success;
599
600 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
601 FND_LOG.string (log_level => FND_LOG.level_procedure,
602 module => g_module_prefix || l_module_name,
603 message => 'Entered procedure = ' || l_module_name ||
604 'p_auction_header_id = ' || p_auction_header_id);
605 END IF;
606
607 SELECT MAX(LINE_NUMBER)
608 INTO l_max_line_number
609 FROM PON_AUCTION_ITEM_PRICES_ALL
610 WHERE AUCTION_HEADER_ID=p_auction_header_id;
611
612 -- Get the batch size
613 l_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
614
615 -- Draft with no lines, or RFI,CPA with no lines we need to skip batching
616 -- its build into the loop logic but just to be explicit about this condition
617
618 IF (l_max_line_number > 0) THEN --{
619
620 -- Define the initial batch range (line numbers are indexed from 1)
621 l_batch_start := 1;
622
623 IF (l_max_line_number <= l_batch_size) THEN
624 l_batch_end := l_max_line_number;
625 ELSE
626 l_batch_end := l_batch_size;
627 END IF;
628
629 WHILE (l_batch_start <= l_max_line_number) LOOP
630
631 -- Delete the entries from the attribute scores table for this auction
632 DELETE FROM
633 pon_attribute_scores
634 WHERE
635 auction_header_id = p_auction_header_id AND
636 line_number >= l_batch_start AND
637 line_number <= l_batch_end;
638
639 -- Delete the special attributes (quantity and need by date)
640 DELETE FROM
641 pon_auction_attributes
642 WHERE
643 auction_header_id = p_auction_header_id AND
644 sequence_number < 0 AND
645 line_number >= l_batch_start AND
646 line_number <= l_batch_end;
647
648 -- Set the scoring type as null and the weight as null for all the attributes
649 UPDATE
650 pon_auction_attributes
651 SET
652 scoring_type = 'NONE',
653 weight = 0,
654 last_update_date = sysdate,
655 last_updated_by = FND_GLOBAL.user_id
656 WHERE
657 auction_header_id = p_auction_header_id AND
658 line_number >= l_batch_start AND
659 line_number <= l_batch_end;
660 -- Find the new batch range
661 l_batch_start := l_batch_end + 1;
662 IF (l_batch_end + l_batch_size > l_max_line_number) THEN
663 l_batch_end := l_max_line_number;
664 ELSE
665 l_batch_end := l_batch_end + l_batch_size;
666 END IF;
667
668 -- Issue a commit to push in all changes
669 COMMIT;
670 END LOOP;
671
672 END IF; --}
673
674 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
675 FND_LOG.string (log_level => FND_LOG.level_procedure,
676 module => g_module_prefix || l_module_name,
677 message => 'Leaving procedure = ' || l_module_name);
678 END IF;
679
680 EXCEPTION
681 WHEN OTHERS THEN
682 x_result := FND_API.g_ret_sts_unexp_error;
683 x_error_code := SQLCODE;
684 x_error_message := SUBSTR(SQLERRM, 1, 100);
685
686 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
687 FND_LOG.string (log_level => FND_LOG.level_exception,
688 module => g_module_prefix || l_module_name,
689 message => 'Unexpected exception occured error_code = ' ||
690 x_error_code || ', error_message = ' || x_error_message);
691 END IF;
692
693 END remove_score;
694
695 /*======================================================================
696 PROCEDURE : has_price_elements
697 PARAMETERS: 1. x_result - return status.
698 2. x_error_code - error code
699 3. x_error_message - The actual error message
700 4. p_auction_header_id - The auction header id
701 COMMENT : This procedure will return Y if there are price elements
702 on the negotiation else it will return N
703 ======================================================================*/
704
705 PROCEDURE has_price_elements (
706 x_result OUT NOCOPY VARCHAR2,
707 x_error_code OUT NOCOPY VARCHAR2,
708 x_error_message OUT NOCOPY VARCHAR2,
709 p_auction_header_id IN NUMBER,
710 x_has_price_elements OUT NOCOPY VARCHAR2
711 ) IS
712
713 l_module_name VARCHAR2 (30);
714 BEGIN
715
716 l_module_name := 'has_price_elements';
717 x_result := FND_API.g_ret_sts_success;
718
719 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
720 FND_LOG.string (log_level => FND_LOG.level_procedure,
721 module => g_module_prefix || l_module_name,
722 message => 'Entered procedure = ' || l_module_name ||
723 ' p_auction_header_id = ' || p_auction_header_id);
724 END IF;
725
726 BEGIN
727
728 SELECT
729 'Y'
730 INTO
731 x_has_price_elements
732 FROM
733 pon_price_elements
734 WHERE
735 auction_header_id = p_auction_header_id AND
736 rownum = 1;
737
738 EXCEPTION
739 WHEN NO_DATA_FOUND THEN
740 x_has_price_elements := 'N';
741 END;
742
743 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
744 FND_LOG.string (log_level => FND_LOG.level_procedure,
745 module => g_module_prefix || l_module_name,
746 message => 'Leaving procedure = ' || l_module_name ||
747 ' x_has_price_elements = ' || x_has_price_elements);
748 END IF;
749
750 EXCEPTION
751 WHEN OTHERS THEN
752 x_result := FND_API.g_ret_sts_unexp_error;
753 x_error_code := SQLCODE;
754 x_error_message := SUBSTR(SQLERRM, 1, 100);
755
756 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
757 FND_LOG.string (log_level => FND_LOG.level_exception,
758 module => g_module_prefix || l_module_name,
759 message => 'Unexpected exception occured error_code = ' ||
760 x_error_code || ', error_message = ' || x_error_message);
761 END IF;
762
763 END has_price_elements;
764
765 /*======================================================================
766 PROCEDURE : has_supplier_price_elements
767 PARAMETERS: 1. x_result - return status.
768 2. x_error_code - error code
769 3. x_error_message - The actual error message
770 4. p_auction_header_id - The auction header id
771 COMMENT : This procedure will return Y if there are supplier price
772 elements on the negotiation else it will return N
773 ======================================================================*/
774
775 PROCEDURE has_supplier_price_elements (
776 x_result OUT NOCOPY VARCHAR2,
777 x_error_code OUT NOCOPY VARCHAR2,
778 x_error_message OUT NOCOPY VARCHAR2,
779 p_auction_header_id IN NUMBER,
780 x_has_supplier_price_elements OUT NOCOPY VARCHAR2
781 ) IS
782
783 l_module_name VARCHAR2 (30);
784 BEGIN
785
786 l_module_name := 'has_supplier_price_elements';
787 x_result := FND_API.g_ret_sts_success;
788
789 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
790 FND_LOG.string (log_level => FND_LOG.level_procedure,
791 module => g_module_prefix || l_module_name,
792 message => 'Entered procedure = ' || l_module_name ||
793 ' p_auction_header_id = ' || p_auction_header_id);
794 END IF;
795
796 BEGIN
797
798 SELECT
799 'Y'
800 INTO
801 x_has_supplier_price_elements
802 FROM
803 pon_price_elements
804 WHERE
805 auction_header_id = p_auction_header_id AND
806 pf_type = 'SUPPLIER' AND
807 rownum = 1;
808
809 EXCEPTION
810 WHEN NO_DATA_FOUND THEN
811 x_has_supplier_price_elements := 'N';
812 END;
813
814 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
815 FND_LOG.string (log_level => FND_LOG.level_procedure,
816 module => g_module_prefix || l_module_name,
817 message => 'Leaving procedure = ' || l_module_name ||
818 ' x_has_supplier_price_elements = ' || x_has_supplier_price_elements);
819 END IF;
820
821 EXCEPTION
822 WHEN OTHERS THEN
823 x_result := FND_API.g_ret_sts_unexp_error;
824 x_error_code := SQLCODE;
825 x_error_message := SUBSTR(SQLERRM, 1, 100);
826
827 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
828 FND_LOG.string (log_level => FND_LOG.level_exception,
829 module => g_module_prefix || l_module_name,
830 message => 'Unexpected exception occured error_code = ' ||
831 x_error_code || ', error_message = ' || x_error_message);
832 END IF;
833
834 END has_supplier_price_elements;
835
836 /*======================================================================
837 PROCEDURE : has_buyer_price_elements
838 PARAMETERS: 1. x_result - return status.
839 2. x_error_code - error code
840 3. x_error_message - The actual error message
841 4. p_auction_header_id - The auction header id
842 COMMENT : This procedure will return Y if there are buyer price
843 elements on the negotiation else it will return N
844 ======================================================================*/
845
846 PROCEDURE has_buyer_price_elements (
847 x_result OUT NOCOPY VARCHAR2,
848 x_error_code OUT NOCOPY VARCHAR2,
849 x_error_message OUT NOCOPY VARCHAR2,
850 p_auction_header_id IN NUMBER,
851 x_has_buyer_price_elements OUT NOCOPY VARCHAR2
852 ) IS
853
854 l_module_name VARCHAR2 (30);
855 BEGIN
856
857 l_module_name := 'has_buyer_price_elements';
858 x_result := FND_API.g_ret_sts_success;
859
860 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
861 FND_LOG.string (log_level => FND_LOG.level_procedure,
862 module => g_module_prefix || l_module_name,
863 message => 'Entered procedure = ' || l_module_name ||
864 ' p_auction_header_id = ' || p_auction_header_id);
865 END IF;
866
867 BEGIN
868
869 SELECT
870 'Y'
871 INTO
872 x_has_buyer_price_elements
873 FROM
874 pon_price_elements
875 WHERE
876 auction_header_id = p_auction_header_id AND
877 pf_type = 'BUYER' AND
878 rownum = 1;
879
880 EXCEPTION
881 WHEN NO_DATA_FOUND THEN
882 x_has_buyer_price_elements := 'N';
883 END;
884
885 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
886 FND_LOG.string (log_level => FND_LOG.level_procedure,
887 module => g_module_prefix || l_module_name,
888 message => 'Leaving procedure = ' || l_module_name ||
889 ' x_has_buyer_price_elements = ' || x_has_buyer_price_elements);
890 END IF;
891
892 EXCEPTION
893 WHEN OTHERS THEN
894 x_result := FND_API.g_ret_sts_unexp_error;
895 x_error_code := SQLCODE;
896 x_error_message := SUBSTR(SQLERRM, 1, 100);
897
898 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
899 FND_LOG.string (log_level => FND_LOG.level_exception,
900 module => g_module_prefix || l_module_name,
901 message => 'Unexpected exception occured error_code = ' ||
902 x_error_code || ', error_message = ' || x_error_message);
903 END IF;
904
905 END has_buyer_price_elements;
906
907 --The procedures for synchrinization of price factor values for large auction
908 --start here
909 /*====================================================================================
910 PROCEDURE : SYNC_PF_VALUES_ITEM_PRICES
911 DESCRIPTION: Procedure to synchronize the price factor values due to modification
912 in the lines or their price factors
913 PARAMETERS: 1. p_auction_header_id - The auction header id
914 2. p_line_number - The line_number of that line which is, or the
915 price factors of which, are modified.
916 This parameter is not required in case a line is
917 being deleted. This method can be called once
918 after a set of lines have been deleted and it will do
919 the sync for all.
920 3. p_add_pf - 'Y' implies the new price factors have to be added
921 else it is 'N'
922 4. p_del_pf - 'Y' implies the deleted price factors have to be removed
923 else it is 'N'
924 5. x_result - return status.
925 6. x_error_code - error code
926 7. x_error_message - The actual error message
927 COMMENT : This procedure will synchronise the price factor
928 values table when the price factors of a line is added/deleted/modified
929 ====================================================================================*/
930
931 PROCEDURE SYNC_PF_VALUES_ITEM_PRICES(
932 p_auction_header_id IN NUMBER,
933 p_line_number IN NUMBER,
934 p_add_pf IN VARCHAR2,
935 p_del_pf IN VARCHAR2,
936 x_result OUT NOCOPY VARCHAR2,
937 x_error_code OUT NOCOPY VARCHAR2,
938 x_error_message OUT NOCOPY VARCHAR2)
939 is
940 l_module_name VARCHAR2 (30);
941 BEGIN
942 l_module_name := 'SYNC_PF_VALUES_ITEM_PRICES';
943 x_result := FND_API.G_RET_STS_SUCCESS;
944 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
945 FND_LOG.string (log_level => FND_LOG.level_statement,
946 module => g_module_prefix || l_module_name,
947 message => 'Entered the procedure; p_auction_header_id : '||p_auction_header_id ||
948 ' p_line_number : '||p_line_number||
949 ' p_add_pf : '||p_add_pf||
950 ' p_del_pf : '||p_del_pf
951 );
952 END IF;
953
954 if p_add_pf = 'Y' then
955
956 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
957 FND_LOG.string (log_level => FND_LOG.level_statement,
958 module => g_module_prefix || l_module_name,
959 message => 'Inserting newly added/modified price factors...'
960 );
961 END IF;
962
963 insert into PON_LARGE_NEG_PF_VALUES (auction_header_id,price_element_type_id,pricing_basis,
964 supplier_seq_number,value,creation_date,created_by,last_update_date,last_updated_by,last_update_login)
965 select distinct PPE.auction_header_id,PPE.price_element_type_id,PPE.pricing_basis,
966 PBP.sequence,null,sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.login_id
967 from
968 PON_PRICE_ELEMENTS PPE, PON_BIDDING_PARTIES PBP
969 where
970 PPE.auction_header_id = p_auction_header_id and
971 PBP.auction_header_id = p_auction_header_id and
972 PPE.line_number = p_line_number and
973 PPE.pf_type = 'BUYER' and
974 not exists (
975 select pf_values.price_element_type_id,pf_values.pricing_basis
976 from
977 PON_LARGE_NEG_PF_VALUES pf_values
978 where auction_header_id = p_auction_header_id
979 and PPE.price_element_type_id = pf_values.price_element_type_id
980 and PPE.pricing_basis = pf_values.pricing_basis
981 and rownum = 1);
982 end if;
983
984 if p_del_pf = 'Y' then
985
986 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
987 FND_LOG.string (log_level => FND_LOG.level_statement,
988 module => g_module_prefix || l_module_name,
989 message => 'Removing the deleted/modified price factors...'
990 );
991 END IF;
992
993 delete from PON_LARGE_NEG_PF_VALUES pf_values
994 where
995 auction_header_id = p_auction_header_id and
996 not exists (
997 select PPE.price_element_type_id,PPE.pricing_basis
998 from
999 PON_PRICE_ELEMENTS PPE
1000 where auction_header_id = p_auction_header_id
1001 and PPE.price_element_type_id = pf_values.price_element_type_id
1002 and PPE.pricing_basis = pf_values.pricing_basis
1003 and PPE.pf_type = 'BUYER'
1004 and rownum = 1);
1005
1006 end if;
1007 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1008 FND_LOG.string (log_level => FND_LOG.level_statement,
1009 module => g_module_prefix || l_module_name,
1010 message => 'Returning from the procedure with status : '||x_result
1011 );
1012 END IF;
1013
1014 EXCEPTION
1015 WHEN OTHERS THEN
1016 x_result := FND_API.g_ret_sts_unexp_error;
1017 x_error_code := SQLCODE;
1018 x_error_message := SUBSTR(SQLERRM, 1, 100);
1019
1020 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
1021 FND_LOG.string (log_level => FND_LOG.level_exception,
1022 module => g_module_prefix || l_module_name,
1023 message => 'Unexpected exception occured error_code = ' ||
1024 x_error_code || ', error_message = ' || x_error_message);
1025 END IF;
1026
1027 END SYNC_PF_VALUES_ITEM_PRICES;
1028
1029 /*====================================================================================
1030 PROCEDURE : SYNC_PF_VALUES_BIDDING_PARTIES
1031 DESCRIPTION: Procedure to synchronize the price factor values due to modification
1032 in the supplier invited
1033 PARAMETERS: 1. p_auction_header_id - The auction header id
1034 2. p_supplier_seq_num - The sequence_number of the supplier who
1035 is added
1036 3. p_action - The task to be performed. The possible values it takes is
1037 ADD_SUPPLIER => Add price factor values for a new supplier
1038 DELETE_SUPPLIER => Delete the price factor values for a supplier
1039 who is deleted
1040 4. x_result - return status.
1041 5. x_error_code - error code
1042 6. x_error_message - The actual error message
1043 COMMENT : This procedure will synchronise the price factor
1044 values when a supplier is added/deleted
1045 ====================================================================================*/
1046
1047 PROCEDURE SYNC_PF_VALUES_BIDDING_PARTIES(
1048 p_auction_header_id IN NUMBER,
1049 p_supplier_seq_num IN NUMBER,
1050 p_action IN VARCHAR2,
1051 x_result OUT NOCOPY VARCHAR2,
1052 x_error_code OUT NOCOPY VARCHAR2,
1053 x_error_message OUT NOCOPY VARCHAR2)
1054 is
1055 l_supplier_seq_num NUMBER := null;
1056 l_module_name VARCHAR2 (30);
1057 l_supplier_exists VARCHAR2(1);
1058 BEGIN
1059 l_module_name := 'SYNC_PF_VALUES_BIDDING_PARTIES';
1060 x_result := FND_API.G_RET_STS_SUCCESS;
1061 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1062 FND_LOG.string (log_level => FND_LOG.level_statement,
1063 module => g_module_prefix || l_module_name,
1064 message => 'Entered the procedure ; p_auction_header_id : '||p_auction_header_id ||
1065 ' p_line_number : '||p_supplier_seq_num||
1066 ' p_action : '||p_action
1067 );
1068 END IF;
1069
1070 if p_action = 'ADD_SUPPLIER' then
1071 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1072 FND_LOG.string (log_level => FND_LOG.level_statement,
1073 module => g_module_prefix || l_module_name,
1074 message => 'Adding price factors for the new supplier with sequence number: '||p_supplier_seq_num
1075 );
1076 END IF;
1077
1078 --Please be CAREFUL here
1079 --In auto create the rows in PON_LARGE_NEG_PF_VALUES will get inserted by
1080 --SYNC_PF_VALUES_ITEM_PRICES (via the beforeCommit of AuctionItemPricesAllVO
1081 --So check here if the rows for this supplier sequence number exist. If yes,
1082 --then return with Success. We can be sure that if a single record for the
1083 --supplier exists, then all the distinct price factor and pricing basis
1084 --combinations exist for that supplier
1085 BEGIN
1086 SELECT 'Y'
1087 INTO l_supplier_exists
1088 FROM pon_large_neg_pf_values
1089 WHERE auction_header_id = p_auction_header_id and supplier_seq_number = p_supplier_seq_num and rownum = 1;
1090 EXCEPTION
1091 when NO_DATA_FOUND then
1092 l_supplier_exists := 'N';
1093 END;
1094
1095
1096 IF (l_supplier_exists = 'Y') THEN
1097 RETURN;
1098 END IF;
1099
1100 BEGIN
1101 select supplier_seq_number into l_supplier_seq_num from PON_LARGE_NEG_PF_VALUES
1102 where auction_header_id = p_auction_header_id
1103 and rownum = 1;
1104 EXCEPTION
1105 when NO_DATA_FOUND then
1106 l_supplier_seq_num := null;
1107 END;
1108
1109 if l_supplier_seq_num >= 0 then
1110
1111 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1112 FND_LOG.string (log_level => FND_LOG.level_statement,
1113 module => g_module_prefix || l_module_name,
1114 message => 'adding the price factors with the help of PON_LARGE_NEG_PF_VALUES table'
1115 );
1116 END IF;
1117
1118 insert into PON_LARGE_NEG_PF_VALUES (auction_header_id,price_element_type_id,pricing_basis,
1119 supplier_seq_number,value,creation_date,created_by,last_update_date,last_updated_by,last_update_login)
1120 select auction_header_id,price_element_type_id,pricing_basis,
1121 p_supplier_seq_num,null,sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.login_id
1122 from
1123 PON_LARGE_NEG_PF_VALUES
1124 where
1125 auction_header_id = p_auction_header_id and
1126 supplier_seq_number = l_supplier_seq_num;
1127 elsif l_supplier_seq_num is null then
1128
1129 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1130 FND_LOG.string (log_level => FND_LOG.level_statement,
1131 module => g_module_prefix || l_module_name,
1132 message => 'adding the price factors with the help of PON_PRICE_ELEMENTS'
1133 );
1134 END IF;
1135
1136 insert into PON_LARGE_NEG_PF_VALUES (auction_header_id,price_element_type_id,pricing_basis,
1137 supplier_seq_number,value,creation_date,created_by,last_update_date,last_updated_by,last_update_login)
1138 select distinct auction_header_id,price_element_type_id,pricing_basis,
1139 p_supplier_seq_num,null,sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.login_id
1140 from
1141 PON_PRICE_ELEMENTS
1142 where
1143 auction_header_id = p_auction_header_id and
1144 pf_type = 'BUYER';
1145
1146 else
1147 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1148 FND_LOG.string (log_level => FND_LOG.level_statement,
1149 module => g_module_prefix || l_module_name,
1150 message => 'Addition of price factor values failed'
1151 );
1152 END IF;
1153
1154 end if;
1155 elsif p_action = 'DELETE_SUPPLIER' then
1156 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1157 FND_LOG.string (log_level => FND_LOG.level_statement,
1158 module => g_module_prefix || l_module_name,
1159 message => 'deleting price factors for the deleted supplier with sequence number: '||p_supplier_seq_num
1160 );
1161 END IF;
1162
1163 delete from PON_LARGE_NEG_PF_VALUES
1164 where
1165 auction_header_id = p_auction_header_id AND
1166 supplier_seq_number = p_supplier_seq_num;
1167 end if;
1168
1169 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1170 FND_LOG.string (log_level => FND_LOG.level_statement,
1171 module => g_module_prefix || l_module_name,
1172 message => 'Exitting with return status of ' ||x_result
1173 );
1174 END IF;
1175
1176 EXCEPTION
1177 when OTHERS then
1178 x_error_code := SQLCODE;
1179 x_error_message := substr(SQLERRM,1,200);
1180 x_result := FND_API.G_RET_STS_UNEXP_ERROR;
1181 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
1182 FND_LOG.string (log_level => FND_LOG.level_exception,
1183 module => g_module_prefix || l_module_name,
1184 message => 'Exception in processing auction ' || p_auction_header_id
1185 );
1186 END IF;
1187
1188 END SYNC_PF_VALUES_BIDDING_PARTIES;
1189
1190 --The procedures for synchrinization of price factor values for large auction
1191 --end here
1192
1193 --Complex work
1194 --This procedure will delete the attachments for all the payments for lines in range
1195 PROCEDURE Delete_Payment_Attachments (
1196 p_auction_header_id IN NUMBER,
1197 p_curr_from_line_number IN NUMBER,
1198 p_curr_to_line_number IN NUMBER
1199 ) IS
1200
1201 l_module_name VARCHAR2 (30);
1202
1203 CURSOR delete_attachments IS
1204 SELECT distinct (TO_NUMBER(pk2_value)) line_number
1205 FROM FND_ATTACHED_DOCUMENTS fnd
1206 WHERE
1207 fnd.pk1_value = p_auction_header_id
1208 AND fnd.pk2_value between to_char(p_curr_from_line_number) and to_char(p_curr_to_line_number)
1209 AND fnd.entity_name = 'PON_AUC_PAYMENTS_SHIPMENTS';
1210 BEGIN
1211
1212 l_module_name := 'Delete_Payment_Attachments';
1213
1214 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1215 FND_LOG.string (log_level => FND_LOG.level_procedure,
1216 module => g_module_prefix || l_module_name,
1217 message => 'Entered procedure = ' || l_module_name);
1218 END IF;
1219
1220 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1221 FND_LOG.string (log_level => FND_LOG.level_procedure,
1222 module => g_module_prefix || l_module_name,
1223 message => 'before Call FND_ATTACHED_DOCUMENTS2_PKG.DELETE_ATTACHMENTS = ' || l_module_name);
1224 END IF;
1225
1226 -- delete attachments for the payments if any
1227 FOR delete_attachments_rec IN delete_attachments LOOP
1228 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1229 FND_LOG.string (log_level => FND_LOG.level_procedure,
1230 module => g_module_prefix || l_module_name,
1231 message => 'Deleting fnd attachments for all the payments for line ' ||delete_attachments_rec.line_number||'='|| l_module_name);
1232 END IF;
1233 --delete the attachments for a payment
1234 FND_ATTACHED_DOCUMENTS2_PKG.DELETE_ATTACHMENTS
1235 (x_entity_name => 'PON_AUC_PAYMENTS_SHIPMENTS',
1236 x_pk1_value => p_auction_header_id,
1237 x_pk2_value => delete_attachments_rec.line_number,
1238 x_delete_document_flag => 'Y');
1239 END LOOP;
1240
1241 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1242 FND_LOG.string (log_level => FND_LOG.level_procedure,
1243 module => g_module_prefix || l_module_name,
1244 message => 'After Call FND_ATTACHED_DOCUMENTS2_PKG.DELETE_ATTACHMENTS = ' || l_module_name);
1245 END IF;
1246
1247 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1248 FND_LOG.string (log_level => FND_LOG.level_procedure,
1249 module => g_module_prefix || l_module_name,
1250 message => 'Leaving procedure = ' || l_module_name);
1251 END IF;
1252
1253 END Delete_Payment_Attachments;
1254
1255 /*======================================================================
1256 PROCEDURE : delete_all_lines
1257 PARAMETERS: 1. x_result - return status.
1258 2. x_error_code - error code
1259 3. x_error_message - The actual error message
1260 4. p_auction_header_id - The auction header id
1261 COMMENT : This procedure deletes all the lines in the negotiation
1262 and also its children
1263 ======================================================================*/
1264
1265 PROCEDURE delete_all_lines (
1266 x_result OUT NOCOPY VARCHAR2,
1267 x_error_code OUT NOCOPY VARCHAR2,
1268 x_error_message OUT NOCOPY VARCHAR2,
1269 p_auction_header_id IN NUMBER
1270 ) IS
1271
1272 --Cursor to find out lines that have attachments
1273 CURSOR lines_with_attachements IS
1274 SELECT
1275 DISTINCT (TO_NUMBER(pk2_value)) line_number
1276 FROM
1277 fnd_attached_documents
1278 WHERE
1279 entity_name = 'PON_AUCTION_ITEM_PRICES_ALL' AND
1280 pk1_value = to_char(p_auction_header_id) AND
1281 pk2_value IS NOT NULL;
1282
1283 --Cursor to find out lines that have backing requisitions
1284 CURSOR lines_with_backing_requisition (t_auction_header_id NUMBER) IS
1285 SELECT
1286 line_number, org_id
1287 FROM
1288 pon_auction_item_prices_all
1289 WHERE
1290 auction_header_id = t_auction_header_id AND
1291 requisition_number IS NOT NULL;
1292
1293 l_module_name VARCHAR2 (30);
1294 l_line_number NUMBER;
1295
1296 l_max_line_number NUMBER;
1297 l_batch_size NUMBER;
1298
1299 l_batch_start NUMBER;
1300 l_batch_end NUMBER;
1301
1302 -- Auction Header Information
1303 l_bid_ranking PON_AUCTION_HEADERS_ALL.BID_RANKING%TYPE;
1304 l_line_attribute_enabled_flag PON_AUCTION_HEADERS_ALL.LINE_ATTRIBUTE_ENABLED_FLAG%TYPE;
1305 l_doctype_group_name PON_AUC_DOCTYPES.DOCTYPE_GROUP_NAME%TYPE;
1306 l_rfi_line_enabled_flag PON_AUCTION_HEADERS_ALL.RFI_LINE_ENABLED_FLAG%TYPE;
1307 l_pf_type_allowed PON_AUCTION_HEADERS_ALL.PF_TYPE_ALLOWED%TYPE;
1308 l_contract_type PON_AUCTION_HEADERS_ALL.CONTRACT_TYPE%TYPE;
1309 l_global_agreement_flag PON_AUCTION_HEADERS_ALL.GLOBAL_AGREEMENT_FLAG%TYPE;
1310 l_large_neg_enabled_flag PON_AUCTION_HEADERS_ALL.LARGE_NEG_ENABLED_FLAG%TYPE;
1311 l_auction_origination_code PON_AUCTION_HEADERS_ALL.AUCTION_ORIGINATION_CODE%TYPE;
1312 l_progress_payment_type PON_AUCTION_HEADERS_ALL.PROGRESS_PAYMENT_TYPE%TYPE;
1313 l_price_tiers_indicator PON_AUCTION_HEADERS_ALL.PRICE_TIERS_INDICATOR%TYPE;
1314
1315 BEGIN
1316
1317 l_module_name := 'delete_all_lines';
1318 x_result := FND_API.g_ret_sts_success;
1319
1320 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1321 FND_LOG.string (log_level => FND_LOG.level_procedure,
1322 module => g_module_prefix || l_module_name,
1323 message => 'Entered procedure = ' || l_module_name ||
1324 ' p_auction_header_id = ' || p_auction_header_id);
1325 END IF;
1326
1327 -- Collect auction information that is needed
1328 -- This information is being collected from the database instead
1329 -- of relying on the middle tier data as it is possible that the
1330 -- user has change the attribute but not yet saved it. Eg: User
1331 -- has changed from Price Only to MAS but not saved, so surely
1332 -- now there are no scores on the auction. The middle tier data
1333 -- now has MAS but the saved db data has Price Only which is
1334 -- what we look at.
1335 -- Reason we are having this outside the check l_max_line_number > 0
1336 -- is that we need the auction_origination_code before deleting
1337 -- references to requisitions
1338 SELECT
1339 paha.bid_ranking,
1340 paha.line_attribute_enabled_flag,
1341 pad.doctype_group_name,
1342 paha.rfi_line_enabled_flag,
1343 paha.pf_type_allowed,
1344 paha.contract_type,
1345 paha.global_agreement_flag,
1346 paha.large_neg_enabled_flag,
1347 paha.auction_origination_code,
1348 paha.progress_payment_type,
1349 paha.price_tiers_indicator
1350 INTO
1351 l_bid_ranking,
1352 l_line_attribute_enabled_flag,
1353 l_doctype_group_name,
1354 l_rfi_line_enabled_flag,
1355 l_pf_type_allowed,
1356 l_contract_type,
1357 l_global_agreement_flag,
1358 l_large_neg_enabled_flag,
1359 l_auction_origination_code,
1360 l_progress_payment_type,
1361 l_price_tiers_indicator
1362 FROM
1363 pon_auction_headers_all paha,
1364 pon_auc_doctypes pad
1365 WHERE
1366 paha.auction_header_id = p_auction_header_id AND
1367 paha.doctype_id = pad.doctype_id;
1368
1369 SELECT NVL (MAX (line_number), 0)
1370 INTO l_max_line_number
1371 FROM pon_auction_item_prices_all
1372 where auction_header_id = p_auction_header_id;
1373
1374 -- Get the batch size
1375 l_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
1376
1377 -- Call the delete only if the origination code of the auction is
1378 -- REQUISITION
1379 IF (l_auction_origination_code = 'REQUISITION') THEN
1380
1381 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1382 FND_LOG.string (log_level => FND_LOG.level_statement,
1383 module => g_module_prefix || l_module_name,
1384 message => 'Found that this auction is from a requisition');
1385 END IF;
1386
1387 --Delete Backing requisition references
1388 FOR backing_req_line IN lines_with_backing_requisition (p_auction_header_id) LOOP
1389
1390 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1391 FND_LOG.string (log_level => FND_LOG.level_statement,
1392 module => g_module_prefix || l_module_name,
1393 message => 'Deleting backing req references for line = ' || backing_req_line.line_number);
1394 END IF;
1395
1396 PON_AUCTION_PKG.delete_negotiation_line_ref(
1397 x_negotiation_id => p_auction_header_id,
1398 x_negotiation_line_num => backing_req_line.line_number,
1399 x_org_id => backing_req_line.org_id,
1400 x_error_code => x_error_code);
1401
1402 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1403 FND_LOG.string (log_level => FND_LOG.level_statement,
1404 module => g_module_prefix || l_module_name,
1405 message => 'Deletion of req reference done, x_error_code = ' || x_error_code);
1406 END IF;
1407
1408 IF (x_error_code <> 'SUCCESS') THEN
1409 x_result := FND_API.g_ret_sts_unexp_error;
1410 RETURN;
1411 END IF;
1412 END LOOP;
1413 END IF;
1414
1415 --Delete Attachments
1416 FOR attachment_line IN lines_with_attachements LOOP
1417
1418 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments (
1419 x_entity_name =>'PON_AUCTION_ITEM_PRICES_ALL',
1420 x_pk1_value => p_auction_header_id,
1421 x_pk2_value => attachment_line.line_number,
1422 x_pk3_value => NULL,
1423 x_pk4_value => NULL,
1424 x_pk5_value => NULL);
1425
1426 END LOOP;
1427
1428 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1429 FND_LOG.string (log_level => FND_LOG.level_statement,
1430 module => g_module_prefix || l_module_name,
1431 message => 'Attachments deletion complete');
1432 END IF;
1433
1434 -- Draft with no lines, or RFI,CPA with no lines we need to skip batching
1435 -- its build into the loop logic but just to be explicit about this condition
1436
1437 IF (l_max_line_number > 0) THEN
1438
1439 -- Define the initial batch range (line numbers are indexed from 1)
1440 l_batch_start := 1;
1441
1442 IF (l_max_line_number <l_batch_size) THEN
1443 l_batch_end := l_max_line_number;
1444 ELSE
1445 l_batch_end := l_batch_size;
1446 END IF;
1447
1448 WHILE (l_batch_start <= l_max_line_number) LOOP
1449
1450 IF ('STANDARD' = l_contract_type AND l_progress_payment_type <> 'NONE') THEN
1451 --complex work-delete fnd_attachments for payments
1452 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1453 FND_LOG.string (log_level => FND_LOG.level_procedure,
1454 module => g_module_prefix || l_module_name,
1455 message => 'Before call Delete_Payment_Attachments = ' || l_module_name);
1456 END IF;
1457
1458 Delete_Payment_Attachments(
1459 p_auction_header_id => p_auction_header_id,
1460 p_curr_from_line_number => l_batch_start,
1461 p_curr_to_line_number => l_batch_end);
1462
1463 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1464 FND_LOG.string (log_level => FND_LOG.level_statement,
1465 module => g_module_prefix || l_module_name,
1466 message => 'Payments Attachments deletion complete');
1467 END IF;
1468
1469 --complex work -delete payments
1470 DELETE FROM
1471 pon_auc_payments_shipments
1472 WHERE
1473 auction_header_id = p_auction_header_id AND
1474 line_number >= l_batch_start AND
1475 line_number <= l_batch_end;
1476
1477 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1478 FND_LOG.string (log_level => FND_LOG.level_statement,
1479 module => g_module_prefix || l_module_name,
1480 message => 'Payments deletion complete');
1481 END IF;
1482 END IF; --if complex work
1483 -- Call the delete scores only if negotiation has
1484 -- BID_RANKING as 'MULTI_ATTRIBUTE_SCORING'
1485 IF (l_bid_ranking = 'MULTI_ATTRIBUTE_SCORING') THEN
1486
1487 -- Delete the entries for attribute scores. To
1488 -- avoid deleting the attributes corresponding
1489 -- to the header the condition LINE_NUMBER <> -1
1490 -- is added
1491 -- Above condition is built into the batching
1492 -- condition as we start from 1
1493 DELETE FROM
1494 pon_attribute_scores
1495 WHERE
1496 auction_header_id = p_auction_header_id AND
1497 line_number >= l_batch_start AND
1498 line_number <= l_batch_end;
1499
1500 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1501 FND_LOG.string (log_level => FND_LOG.level_statement,
1502 module => g_module_prefix || l_module_name,
1503 message => 'Scores deletion complete');
1504 END IF;
1505
1506 END IF;
1507
1508 -- Call the delete attributes only if the negotiation has
1509 -- LINE_ATTRIBUTE_ENABLED_FLAG set to Y
1510 IF (NVL (l_line_attribute_enabled_flag, 'Y') = 'Y' AND
1511 (l_doctype_group_name <> PON_CONTERMS_UTL_PVT.SRC_REQUEST_FOR_INFORMATION OR
1512 NVL (l_rfi_line_enabled_flag, 'Y') = 'Y')) THEN
1513
1514 -- Delete the entries for attributes. To
1515 -- avoid deleting the attributes corresponding
1516 -- to the header the condition LINE_NUMBER <> -1
1517 -- is added
1518 -- Above condition is built into the batching
1519 -- condition as we start from 1
1520 DELETE FROM
1521 pon_auction_attributes
1522 WHERE
1523 auction_header_id = p_auction_header_id AND
1524 line_number >= l_batch_start AND
1525 line_number <= l_batch_end;
1526
1527 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1528 FND_LOG.string (log_level => FND_LOG.level_statement,
1529 module => g_module_prefix || l_module_name,
1530 message => 'Attributes deletion complete');
1531 END IF;
1532 END IF;
1533
1534 -- Call the delete price elements only if
1535 -- PF_TYPE_ALLOWED is set to other than NONE
1536 IF (l_pf_type_allowed <> 'NONE') THEN --{
1537
1538 -- Delete the price elements
1539 DELETE FROM
1540 pon_price_elements
1541 WHERE
1542 auction_header_id = p_auction_header_id AND
1543 line_number >= l_batch_start AND
1544 line_number <= l_batch_end;
1545
1546 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1547 FND_LOG.string (log_level => FND_LOG.level_statement,
1548 module => g_module_prefix || l_module_name,
1549 message => 'PF deletion complete');
1550 END IF;
1551
1552 -- Delete the supplier pf values
1553 DELETE FROM
1554 pon_pf_supplier_values
1555 WHERE
1556 auction_header_id = p_auction_header_id AND
1557 line_number >= l_batch_start AND
1558 line_number <= l_batch_end;
1559
1560 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1561 FND_LOG.string (log_level => FND_LOG.level_statement,
1562 module => g_module_prefix || l_module_name,
1563 message => 'PF Supplier values deletion complete');
1564 END IF;
1565
1566 END IF; --}
1567
1568 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1569 FND_LOG.string (log_level => FND_LOG.level_statement,
1570 module => g_module_prefix || l_module_name,
1571 message => 'price_tiers_indicator for auction ' || p_auction_header_id || ' is ' || l_price_tiers_indicator);
1572 END IF;
1573
1574 -- Call the delete shipments only if price tiers indicator is not 'NONE'
1575
1576 IF ( NVl(l_price_tiers_indicator, 'NONE') <> 'NONE') THEN
1577
1578 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1579 FND_LOG.string (log_level => FND_LOG.level_statement,
1580 module => g_module_prefix || l_module_name,
1581 message => 'Price tiers indicator is not none , so need to delete Price tiers.');
1582 END IF;
1583
1584 -- Delete the price breaks/shipments
1585 DELETE FROM
1586 pon_auction_shipments_all
1587 WHERE
1588 auction_header_id = p_auction_header_id AND
1589 line_number >= l_batch_start AND
1590 line_number <= l_batch_end;
1591
1592 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1593 FND_LOG.string (log_level => FND_LOG.level_statement,
1594 module => g_module_prefix || l_module_name,
1595 message => 'shipments deletion complete');
1596 END IF;
1597
1598 END IF;
1599
1600 -- Call the delete price differentials only if this is an RFI
1601 -- or this is a global agreement
1602 IF (l_doctype_group_name <> PON_CONTERMS_UTL_PVT.SRC_REQUEST_FOR_INFORMATION OR
1603 NVL (l_global_agreement_flag, 'Y') = 'Y') THEN
1604 -- Delete the price differentials
1605 DELETE FROM
1606 pon_price_differentials
1607 WHERE
1608 auction_header_id = p_auction_header_id AND
1609 line_number >= l_batch_start AND
1610 line_number <= l_batch_end;
1611
1612 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1613 FND_LOG.string (log_level => FND_LOG.level_statement,
1614 module => g_module_prefix || l_module_name,
1615 message => 'PD deletion complete');
1616 END IF;
1617 END IF;
1618
1619 -- Call party line exclusion deletion only if this is
1620 -- not a large negotiation
1621 IF (nvl (l_large_neg_enabled_flag, 'N') = 'N') THEN
1622
1623 -- Delete the party line exclusions
1624 DELETE FROM
1625 pon_party_line_exclusions
1626 WHERE
1627 auction_header_id = p_auction_header_id AND
1628 line_number >= l_batch_start AND
1629 line_number <= l_batch_end;
1630
1631 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1632 FND_LOG.string (log_level => FND_LOG.level_statement,
1633 module => g_module_prefix || l_module_name,
1634 message => 'party exclusions deletion complete');
1635 END IF;
1636
1637 END IF;
1638
1639 -- Delete the entries for lines
1640 DELETE FROM
1641 pon_auction_item_prices_all
1642 WHERE
1643 auction_header_id = p_auction_header_id AND
1644 line_number >= l_batch_start AND
1645 line_number <= l_batch_end;
1646
1647 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1648 FND_LOG.string (log_level => FND_LOG.level_statement,
1649 module => g_module_prefix || l_module_name,
1650 message => 'Lines deletion complete');
1651 END IF;
1652
1653 -- Find the new batch range
1654 l_batch_start := l_batch_end + 1;
1655 IF (l_batch_end + l_batch_size > l_max_line_number) THEN
1656 l_batch_end := l_max_line_number;
1657 ELSE
1658 l_batch_end := l_batch_end + l_batch_size;
1659 END IF;
1660
1661 -- Issue a commit to push in all changes
1662 COMMIT;
1663 END LOOP;
1664
1665 END IF;
1666
1667 -- Call delete from pon_large_neg_pf_values only if this is
1668 -- a large negotiation
1669 IF (l_large_neg_enabled_flag = 'Y') THEN
1670
1671 -- Delete the large neg pf values
1672 DELETE FROM
1673 pon_large_neg_pf_values
1674 WHERE
1675 auction_header_id = p_auction_header_id;
1676
1677 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1678 FND_LOG.string (log_level => FND_LOG.level_statement,
1679 module => g_module_prefix || l_module_name,
1680 message => 'Large neg pf values deletion complete');
1681 END IF;
1682
1683 END IF;
1684
1685 -- Call update on pon_bidding_parties only if this is
1686 -- not a large negotiation
1687 IF (nvl (l_large_neg_enabled_flag, 'N') = 'N') THEN
1688
1689 --Need to update pon_bidding_parties about the access_type
1690 --Any supplier who was restricted on the deleted lines
1691 --should now have access_type set to FULL
1692 UPDATE
1693 pon_bidding_parties
1694 SET
1695 access_type = 'FULL'
1696 WHERE
1697 auction_header_id = p_auction_header_id AND
1698 access_type = 'RESTRICTED' AND
1699 (trading_partner_id, vendor_site_id) NOT IN
1700 (SELECT trading_partner_id, vendor_site_id
1701 FROM pon_party_line_exclusions
1702 WHERE auction_header_id = p_auction_header_id);
1703
1704 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1705 FND_LOG.string (log_level => FND_LOG.level_statement,
1706 module => g_module_prefix || l_module_name,
1707 message => 'Updating bidding parties done');
1708 END IF;
1709
1710 END IF;
1711
1712 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1713 FND_LOG.string (log_level => FND_LOG.level_procedure,
1714 module => g_module_prefix || l_module_name,
1715 message => 'Leaving procedure = ' || l_module_name);
1716 END IF;
1717
1718 EXCEPTION
1719 WHEN OTHERS THEN
1720 x_result := FND_API.g_ret_sts_unexp_error;
1721 x_error_code := SQLCODE;
1722 x_error_message := SUBSTR(SQLERRM, 1, 100);
1723
1724 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
1725 FND_LOG.string (log_level => FND_LOG.level_exception,
1726 module => g_module_prefix || l_module_name,
1727 message => 'Unexpected exception occured error_code = ' ||
1728 x_error_code || ', error_message = ' || x_error_message);
1729 END IF;
1730 END delete_all_lines;
1731
1732 /*======================================================================
1733 PROCEDURE : delete_single_line
1734 PARAMETERS: 1. x_result - return status.
1735 2. x_error_code - error code
1736 3. x_error_message - The actual error message
1737 4. p_auction_header_id - The auction header id
1738 5. p_line_number - The line to be deleted
1739 6. p_group_type - The group type of the line to be
1740 deleted.
1741 7. p_origination_code - The origination code for this line
1742 8. p_org_id - The org id for this line
1743 9. p_parent_line_number - The parent line number for
1744 this line
1745 10. p_sub_line_sequence_number - The sub line sequence
1746 number for this line
1747 COMMENT : This procedure will delete the given line. If it is a lot
1748 or a group then all the lot line and group lines will
1749 also be deleted.
1750 ======================================================================*/
1751
1752 PROCEDURE delete_single_line (
1753 x_result OUT NOCOPY VARCHAR2, --1
1754 x_error_code OUT NOCOPY VARCHAR2, --2
1755 x_error_message OUT NOCOPY VARCHAR2, --3
1756 p_auction_header_id IN NUMBER, --4
1757 p_line_number IN NUMBER, --5
1758 p_group_type IN VARCHAR2, --6
1759 p_origination_code IN VARCHAR2, --7
1760 p_org_id IN NUMBER, --8
1761 p_parent_line_number IN NUMBER, --9
1762 p_sub_line_sequence_number IN NUMBER, --10
1763 x_number_of_lines_deleted IN OUT NOCOPY NUMBER --11
1764 ) IS
1765
1766 l_module_name VARCHAR2 (30);
1767
1768 l_header_max_document_line_num NUMBER;
1769 l_line_number NUMBER;
1770
1771 -- Auction Header Information
1772 l_bid_ranking PON_AUCTION_HEADERS_ALL.BID_RANKING%TYPE;
1773 l_line_attribute_enabled_flag PON_AUCTION_HEADERS_ALL.LINE_ATTRIBUTE_ENABLED_FLAG%TYPE;
1774 l_doctype_group_name PON_AUC_DOCTYPES.DOCTYPE_GROUP_NAME%TYPE;
1775 l_rfi_line_enabled_flag PON_AUCTION_HEADERS_ALL.RFI_LINE_ENABLED_FLAG%TYPE;
1776 l_pf_type_allowed PON_AUCTION_HEADERS_ALL.PF_TYPE_ALLOWED%TYPE;
1777 l_contract_type PON_AUCTION_HEADERS_ALL.CONTRACT_TYPE%TYPE;
1778 l_global_agreement_flag PON_AUCTION_HEADERS_ALL.GLOBAL_AGREEMENT_FLAG%TYPE;
1779 l_large_neg_enabled_flag PON_AUCTION_HEADERS_ALL.LARGE_NEG_ENABLED_FLAG%TYPE;
1780 l_auction_origination_code PON_AUCTION_HEADERS_ALL.AUCTION_ORIGINATION_CODE%TYPE;
1781 l_amendment_number PON_AUCTION_HEADERS_ALL.AMENDMENT_NUMBER%TYPE;
1782 l_auction_round_number PON_AUCTION_HEADERS_ALL.AUCTION_ROUND_NUMBER%TYPE;
1783 l_is_multi_round VARCHAR2(2);
1784 l_is_amendment VARCHAR2(2);
1785 l_progress_payment_type PON_AUCTION_HEADERS_ALL.PROGRESS_PAYMENT_TYPE%TYPE;
1786 l_price_tiers_indicator PON_AUCTION_HEADERS_ALL.PRICE_TIERS_INDICATOR%TYPE;
1787
1788 -- Cursor to find out lines that have attachments
1789 -- within a lot/group
1790 CURSOR lines_with_attachements IS
1791 SELECT
1792 DISTINCT (TO_NUMBER(fad.pk2_value)) line_number
1793 FROM
1794 fnd_attached_documents fad,
1795 pon_auction_item_prices_all paip
1796 WHERE
1797 fad.entity_name = 'PON_AUCTION_ITEM_PRICES_ALL' AND
1798 fad.pk1_value = TO_CHAR(p_auction_header_id) AND
1799 paip.auction_header_id = p_auction_header_id AND
1800 fad.pk2_value = paip.line_number AND
1801 (paip.line_number = p_line_number OR paip.parent_line_number = p_line_number);
1802
1803 -- Cursor to find out lines that have backing requisitions
1804 -- within a lot/group
1805 CURSOR lines_with_backing_requisition IS
1806 SELECT
1807 line_number, org_id
1808 FROM
1809 pon_auction_item_prices_all
1810 WHERE
1811 auction_header_id = p_auction_header_id AND
1812 (line_number = p_line_number OR parent_line_number = p_line_number) AND
1813 requisition_number IS NOT NULL;
1814
1815
1816 --cursor to delete payment attachemnts for whole lot or group
1817 CURSOR delete_payments_attachments IS
1818 SELECT
1819 DISTINCT (TO_NUMBER(fad.pk2_value)) line_number
1820 FROM
1821 fnd_attached_documents fad,
1822 pon_auction_item_prices_all paip
1823 WHERE
1824 fad.entity_name = 'PON_AUC_PAYMENTS_SHIPMENTS' AND
1825 fad.pk1_value = TO_CHAR(p_auction_header_id) AND
1826 paip.auction_header_id = p_auction_header_id AND
1827 fad.pk2_value = paip.line_number AND
1828 (paip.line_number = p_line_number OR paip.parent_line_number = p_line_number);
1829
1830 BEGIN
1831
1832 l_module_name := 'delete_single_line';
1833 x_result := FND_API.g_ret_sts_success;
1834
1835 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1836 FND_LOG.string (log_level => FND_LOG.level_procedure,
1837 module => g_module_prefix || l_module_name,
1838 message => 'Entered procedure = ' || l_module_name ||
1839 ', p_auction_header_id = ' || p_auction_header_id ||
1840 ', p_line_number = ' || p_line_number ||
1841 ', p_group_type = ' || p_group_type ||
1842 ', p_origination_code = ' || p_origination_code ||
1843 ', p_org_id = ' || p_org_id ||
1844 ', p_parent_line_number = ' || p_parent_line_number ||
1845 ', p_sub_line_sequence_number = ' || p_sub_line_sequence_number);
1846 END IF;
1847
1848 -- Collect auction information that is needed
1849 -- This information is being collected from the database instead
1850 -- of relying on the middle tier data as it is possible that the
1851 -- user has change the attribute but not yet saved it. Eg: User
1852 -- has changed from Price Only to MAS but not saved, so surely
1853 -- now there are no scores on the auction. The middle tier data
1854 -- now has MAS but the saved db data has Price Only which is
1855 -- what we look at.
1856 -- Reason we are having this outside the check l_max_line_number > 0
1857 -- is that we need the auction_origination_code before deleting
1858 -- references to requisitions
1859 SELECT
1860 paha.bid_ranking,
1861 paha.line_attribute_enabled_flag,
1862 pad.doctype_group_name,
1863 paha.rfi_line_enabled_flag,
1864 paha.pf_type_allowed,
1865 paha.contract_type,
1866 paha.global_agreement_flag,
1867 paha.large_neg_enabled_flag,
1868 paha.auction_origination_code,
1869 paha.amendment_number,
1870 paha.auction_round_number,
1871 paha.progress_payment_type,
1872 paha.price_tiers_indicator
1873 INTO
1874 l_bid_ranking,
1875 l_line_attribute_enabled_flag,
1876 l_doctype_group_name,
1877 l_rfi_line_enabled_flag,
1878 l_pf_type_allowed,
1879 l_contract_type,
1880 l_global_agreement_flag,
1881 l_large_neg_enabled_flag,
1882 l_auction_origination_code,
1883 l_amendment_number,
1884 l_auction_round_number,
1885 l_progress_payment_type,
1886 l_price_tiers_indicator
1887 FROM
1888 pon_auction_headers_all paha,
1889 pon_auc_doctypes pad
1890 WHERE
1891 paha.auction_header_id = p_auction_header_id AND
1892 paha.doctype_id = pad.doctype_id;
1893
1894 -- If this is a line/lot_line or a group_line then need to simply
1895 -- remove the entries corresponding to this one line
1896 IF (p_group_type IN ('LINE', 'LOT_LINE', 'GROUP_LINE')) THEN -- {
1897
1898 SELECT
1899 max_document_line_num
1900 INTO
1901 l_header_max_document_line_num
1902 FROM
1903 pon_auction_headers_all
1904 WHERE
1905 auction_header_id = p_auction_header_id;
1906
1907 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1908 FND_LOG.string (log_level => FND_LOG.level_statement,
1909 module => g_module_prefix || l_module_name,
1910 message => 'The selected row is of type LINE/LOT_LINE/GROUP_LINE');
1911 END IF;
1912
1913 BEGIN
1914
1915 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1916 FND_LOG.string (log_level => FND_LOG.level_statement,
1917 module => g_module_prefix || l_module_name,
1918 message => 'Checking if the line still exists');
1919 END IF;
1920
1921 --Checking if the line still exists in the database
1922 --We are doing this because the user might have selected a LOT and its
1923 --LOT_LINE for deletion and the LOT_LINE has already been deleted as
1924 --part of the LOT deletion. Simply return with number of lines deleted
1925 --set to zero
1926
1927 SELECT
1928 line_number
1929 INTO
1930 l_line_number
1931 FROM
1932 pon_auction_item_prices_all
1933 WHERE
1934 auction_header_id = p_auction_header_id and
1935 line_number = p_line_number;
1936
1937 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1938 FND_LOG.string (log_level => FND_LOG.level_statement,
1939 module => g_module_prefix || l_module_name,
1940 message => 'Line exists');
1941 END IF;
1942 EXCEPTION
1943 WHEN NO_DATA_FOUND THEN
1944 x_number_of_lines_deleted := 0;
1945 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1946 FND_LOG.string (log_level => FND_LOG.level_statement,
1947 module => g_module_prefix || l_module_name,
1948 message => 'Line no longer exists');
1949 END IF;
1950 RETURN;
1951 END;
1952
1953 DELETE FROM
1954 pon_attribute_scores
1955 WHERE
1956 auction_header_id = p_auction_header_id AND
1957 line_number = p_line_number;
1958
1959 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1960 FND_LOG.string (log_level => FND_LOG.level_statement,
1961 module => g_module_prefix || l_module_name,
1962 message => 'Deleted the entry in pon_attribute_scores');
1963 END IF;
1964
1965 DELETE FROM
1966 pon_auction_attributes
1967 WHERE
1968 auction_header_id = p_auction_header_id and
1969 line_number = p_line_number;
1970
1971 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1972 FND_LOG.string (log_level => FND_LOG.level_statement,
1973 module => g_module_prefix || l_module_name,
1974 message => 'Entry in pon_auction_attributes deleted');
1975 END IF;
1976
1977 DELETE FROM
1978 pon_pf_supplier_values
1979 WHERE
1980 auction_header_id = p_auction_header_id AND
1981 line_number = p_line_number;
1982
1983 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1984 FND_LOG.string (log_level => FND_LOG.level_statement,
1985 module => g_module_prefix || l_module_name,
1986 message => 'Deleted the entry in pon_pf_supplier_values');
1987 END IF;
1988
1989 DELETE FROM
1990 pon_price_elements
1991 WHERE
1992 auction_header_id = p_auction_header_id AND
1993 line_number = p_line_number;
1994
1995 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1996 FND_LOG.string (log_level => FND_LOG.level_statement,
1997 module => g_module_prefix || l_module_name,
1998 message => 'Deleted the entry in pon_price_elements');
1999 END IF;
2000
2001 DELETE FROM
2002 pon_price_differentials
2003 WHERE
2004 auction_header_id = p_auction_header_id AND
2005 line_number = p_line_number;
2006
2007 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2008 FND_LOG.string (log_level => FND_LOG.level_statement,
2009 module => g_module_prefix || l_module_name,
2010 message => 'Deleted the entry in pon_price_differentials');
2011 END IF;
2012
2013 DELETE FROM
2014 pon_auction_shipments_all
2015 WHERE
2016 auction_header_id = p_auction_header_id AND
2017 line_number = p_line_number;
2018
2019 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2020 FND_LOG.string (log_level => FND_LOG.level_statement,
2021 module => g_module_prefix || l_module_name,
2022 message => 'Deleted the entry in pon_auction_shipments_all');
2023 END IF;
2024
2025 IF ( 'STANDARD' = l_contract_type AND 'NONE' <> l_progress_payment_type) --{
2026 THEN
2027
2028 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2029 FND_LOG.string (log_level => FND_LOG.level_statement,
2030 module => g_module_prefix || l_module_name,
2031 message => 'Delete attachments for pon_auc_payments_shipments');
2032 END IF;
2033
2034 --delete payment attachments
2035
2036 Delete_Payment_Attachments(
2037 p_auction_header_id => p_auction_header_id,
2038 p_curr_from_line_number => p_line_number,
2039 p_curr_to_line_number => p_line_number);
2040
2041
2042 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2043 FND_LOG.string (log_level => FND_LOG.level_statement,
2044 module => g_module_prefix || l_module_name,
2045 message => 'Deleted the attachments for pon_auc_payments_shipments');
2046 END IF;
2047
2048 DELETE FROM
2049 pon_auc_payments_shipments
2050 WHERE
2051 auction_header_id = p_auction_header_id AND
2052 line_number = p_line_number;
2053
2054 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2055 FND_LOG.string (log_level => FND_LOG.level_statement,
2056 module => g_module_prefix || l_module_name,
2057 message => 'Deleted the entry in pon_auc_payments_shipments');
2058 END IF;
2059 END IF;--if neg has payments }
2060
2061 DELETE FROM
2062 pon_party_line_exclusions
2063 WHERE
2064 auction_header_id = p_auction_header_id AND
2065 line_number = p_line_number;
2066
2067 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2068 FND_LOG.string (log_level => FND_LOG.level_statement,
2069 module => g_module_prefix || l_module_name,
2070 message => 'Deleted the entry in PON_PARTY_LINE_EXCLUSIONS');
2071 END IF;
2072
2073 DELETE FROM
2074 pon_auction_item_prices_all
2075 WHERE
2076 auction_header_id = p_auction_header_id AND
2077 line_number = p_line_number;
2078
2079 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2080 FND_LOG.string (log_level => FND_LOG.level_statement,
2081 module => g_module_prefix || l_module_name,
2082 message => 'Deleted the entry in pon_auction_headers_all');
2083 END IF;
2084
2085 /*
2086 * The sync procedure should be called after the line deletion
2087 * It should also be called only for large negotiations
2088 **/
2089
2090 IF (l_large_neg_enabled_flag = 'Y') THEN
2091 sync_pf_values_item_prices (
2092 p_auction_header_id => p_auction_header_id,
2093 p_line_number => p_line_number,
2094 p_add_pf => 'N',
2095 p_del_pf => 'Y',
2096 x_result => x_result,
2097 x_error_code => x_error_code,
2098 x_error_message => x_error_message);
2099
2100 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2101 FND_LOG.string (log_level => FND_LOG.level_statement,
2102 module => g_module_prefix || l_module_name,
2103 message => 'Sync pf values procedure called. x_result = ' || x_result ||
2104 ', x_error_code = ' || x_error_code ||
2105 ', x_error_message = ' || x_error_message);
2106 END IF;
2107
2108 IF (x_result <> FND_API.g_ret_sts_success) THEN
2109 RETURN;
2110 END IF;
2111
2112 END IF;
2113
2114 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments (
2115 x_entity_name => 'PON_AUCTION_ITEM_PRICES_ALL',
2116 x_pk1_value => p_auction_header_id,
2117 x_pk2_value => p_line_number);
2118
2119 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2120 FND_LOG.string (log_level => FND_LOG.level_statement,
2121 module => g_module_prefix || l_module_name,
2122 message => 'Deleted the line attachments');
2123 END IF;
2124
2125 IF (p_origination_code = 'REQUISITION') THEN
2126 PON_AUCTION_PKG.delete_negotiation_line_ref(
2127 x_negotiation_id => p_auction_header_id,
2128 x_negotiation_line_num => p_line_number,
2129 x_org_id => p_org_id,
2130 x_error_code => x_error_code);
2131
2132 IF (x_error_code <> 'SUCCESS') THEN
2133 x_result := FND_API.g_ret_sts_unexp_error;
2134 RETURN;
2135 END IF;
2136 END IF;
2137
2138 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2139 FND_LOG.string (log_level => FND_LOG.level_statement,
2140 module => g_module_prefix || l_module_name,
2141 message => 'Deleted the line backing requisitions if any');
2142 END IF;
2143
2144 -- In case this is an amendment and we are deleting a LOT_LINE/GROUP_LINE
2145 -- then on the parent line need to set the MODIFIED_FLAG, MODIFIED_DATE and
2146 -- LAST_AMENDMENT_UPDATE if the line is coming from the previous ROUND
2147 IF (p_group_type IN ('LOT_LINE', 'GROUP_LINE') AND
2148 l_amendment_number > 0 AND
2149 p_sub_line_sequence_number <= l_header_max_document_line_num) THEN
2150 UPDATE
2151 pon_auction_item_prices_all
2152 SET
2153 modified_flag = 'Y',
2154 modified_date = sysdate,
2155 last_amendment_update = l_amendment_number
2156 WHERE
2157 auction_header_id = p_auction_header_id AND
2158 line_number = p_parent_line_number;
2159 END IF;
2160
2161 -- In case this is a multi round then on the parent line need to set the MODIFIED_FLAG, MODIFIED fields
2162 IF (p_group_type IN ('LOT_LINE', 'GROUP_LINE') AND
2163 l_auction_round_number > 1 AND
2164 p_sub_line_sequence_number <= l_header_max_document_line_num) THEN
2165 UPDATE
2166 PON_AUCTION_ITEM_PRICES_ALL
2167 SET
2168 MODIFIED_FLAG = 'Y',
2169 MODIFIED_DATE = SYSDATE
2170 WHERE
2171 AUCTION_HEADER_ID = p_auction_header_id AND
2172 LINE_NUMBER = p_parent_line_number;
2173 END IF;
2174
2175 --The number of lines deleted is only 1 in this case
2176 x_number_of_lines_deleted := 1;
2177
2178 ELSE --} { This is the case for LOT or GROUP
2179
2180 -- Keep track of how many lines are being deleted
2181 SELECT
2182 count(line_number)
2183 INTO
2184 x_number_of_lines_deleted
2185 FROM
2186 pon_auction_item_prices_all
2187 WHERE
2188 auction_header_id = p_auction_header_id AND
2189 (line_number = p_line_number OR parent_line_number = p_line_number);
2190
2191 -- Call deletion of attributes only if line_attribute_enabled_flag is Y
2192 IF (NVL (l_line_attribute_enabled_flag, 'Y') = 'Y') THEN --{
2193
2194 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2195 FND_LOG.string (log_level => FND_LOG.level_statement,
2196 module => g_module_prefix || l_module_name,
2197 message => 'line attributes are enabled. l_line_attribute_enabled_flag = ' || l_line_attribute_enabled_flag);
2198 END IF;
2199
2200 -- Call deletion of scores only if this is an MAS
2201 IF (l_bid_ranking = 'MULTI_ATTRIBUTE_SCORING') THEN --{
2202
2203 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2204 FND_LOG.string (log_level => FND_LOG.level_statement,
2205 module => g_module_prefix || l_module_name,
2206 message => 'This is an MAS auction so need to delete scores');
2207 END IF;
2208
2209 -- Delete the scores that belong to this lot/group
2210 -- and also its children
2211 DELETE FROM
2212 pon_attribute_scores pas
2213 WHERE
2214 pas.auction_header_id = p_auction_header_id AND
2215 (
2216 pas.line_number = p_line_number OR
2217 EXISTS
2218 (
2219 SELECT
2220 paip.line_number
2221 FROM
2222 pon_auction_item_prices_all paip
2223 WHERE
2224 paip.parent_line_number = p_line_number AND
2225 paip.auction_header_id = p_auction_header_id AND
2226 paip.line_number = pas.line_number
2227 )
2228 );
2229
2230 END IF; --}
2231
2232 -- Delete the attributes that belong to this lot/group
2233 -- and also its children
2234 DELETE FROM
2235 pon_auction_attributes paa
2236 WHERE
2237 paa.auction_header_id = p_auction_header_id AND
2238 (
2239 paa.line_number = p_line_number OR
2240 EXISTS
2241 (
2242 SELECT
2243 paip.line_number
2244 FROM
2245 pon_auction_item_prices_all paip
2246 WHERE
2247 paip.parent_line_number = p_line_number AND
2248 paip.auction_header_id = p_auction_header_id AND
2249 paip.line_number = paa.line_number
2250 )
2251 );
2252
2253 END IF; --}
2254
2255 -- Call the deletion of pf supplier values and price elements
2256 -- only if the pf_type_allowed is not NONE
2257
2258 IF (l_pf_type_allowed <> 'NONE') THEN --{
2259
2260 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2261 FND_LOG.string (log_level => FND_LOG.level_statement,
2262 module => g_module_prefix || l_module_name,
2263 message => 'There are price factors. l_pf_type_allowed = ' || l_pf_type_allowed);
2264 END IF;
2265
2266 -- Delete the pf supplier values that belong to this
2267 -- lot/group and also its children
2268 DELETE FROM
2269 pon_pf_supplier_values ppsv
2270 WHERE
2271 ppsv.auction_header_id = p_auction_header_id AND
2272 (
2273 ppsv.line_number = p_line_number OR
2274 EXISTS
2275 (
2276 SELECT
2277 paip.line_number
2278 FROM
2279 pon_auction_item_prices_all paip
2280 WHERE
2281 paip.parent_line_number = p_line_number AND
2282 paip.auction_header_id = p_auction_header_id AND
2283 paip.line_number = ppsv.line_number
2284 )
2285 );
2286
2287 -- Delete the cost factors that belong to this
2288 -- lot/group and also its children
2289 DELETE FROM
2290 pon_price_elements ppe
2291 WHERE
2292 ppe.auction_header_id = p_auction_header_id AND
2293 (
2294 ppe.line_number = p_line_number OR
2295 EXISTS
2296 (
2297 SELECT
2298 paip.line_number
2299 FROM
2300 pon_auction_item_prices_all paip
2301 WHERE
2302 paip.parent_line_number = p_line_number AND
2303 paip.auction_header_id = p_auction_header_id AND
2304 paip.line_number = ppe.line_number
2305 )
2306 );
2307
2308 END IF; --}
2309
2310 IF ( 'STANDARD' = l_contract_type AND 'NONE' <> l_progress_payment_type)
2311 THEN
2312
2313 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2314 FND_LOG.string (log_level => FND_LOG.level_statement,
2315 module => g_module_prefix || l_module_name,
2316 message => 'Delete attachments for pon_auc_payments_shipments');
2317 END IF;
2318
2319 --delete payment attachments
2320 FOR delete_attachments_rec IN delete_payments_attachments LOOP
2321 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
2322 FND_LOG.string (log_level => FND_LOG.level_procedure,
2323 module => g_module_prefix || l_module_name,
2324 message => 'Deleting fnd attachments for payments for line number ' ||delete_attachments_rec.line_number||'='|| l_module_name);
2325 END IF;
2326 --delete the attachments for a payment
2327 FND_ATTACHED_DOCUMENTS2_PKG.DELETE_ATTACHMENTS
2328 (x_entity_name => 'PON_AUC_PAYMENTS_SHIPMENTS',
2329 x_pk1_value => p_auction_header_id,
2330 x_pk2_value => delete_attachments_rec.line_number,
2331 x_delete_document_flag => 'Y');
2332 END LOOP;
2333
2334 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2335 FND_LOG.string (log_level => FND_LOG.level_statement,
2336 module => g_module_prefix || l_module_name,
2337 message => 'Deleted the attachments for pon_auc_payments_shipments');
2338 END IF;
2339
2340 DELETE FROM
2341 pon_auc_payments_shipments paps
2342 WHERE
2343 paps.auction_header_id = p_auction_header_id AND (
2344 paps.line_number = p_line_number OR
2345 EXISTS
2346 (
2347 SELECT
2348 paip.line_number
2349 FROM
2350 pon_auction_item_prices_all paip
2351 WHERE
2352 paip.parent_line_number = p_line_number AND
2353 paip.auction_header_id = p_auction_header_id AND
2354 paip.line_number = paps.line_number
2355 )
2356 );
2357
2358 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2359 FND_LOG.string (log_level => FND_LOG.level_statement,
2360 module => g_module_prefix || l_module_name,
2361 message => 'Deleted the entry in pon_auc_payments_shipments');
2362 END IF;
2363 END IF;--if neg has payments
2364
2365 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2366 FND_LOG.string (log_level => FND_LOG.level_statement,
2367 module => g_module_prefix || l_module_name,
2368 message => 'price_tiers_indicator for auction ' || p_auction_header_id || ' is ' || l_price_tiers_indicator);
2369 END IF;
2370
2371
2372 -- Call the delete shipments only if price tiers indicator is not 'NONE'
2373
2374 IF ( NVl(l_price_tiers_indicator, 'NONE') <> 'NONE') THEN --{
2375
2376 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2377 FND_LOG.string (log_level => FND_LOG.level_statement,
2378 module => g_module_prefix || l_module_name,
2379 message => 'Price tiers indicator is not none , so need to delete Price tiers.');
2380 END IF;
2381
2382 -- Delete the price breaks that belong to this
2383 -- lot/group and also its children
2384 DELETE FROM
2385 pon_auction_shipments_all pasa
2386 WHERE
2387 pasa.auction_header_id = p_auction_header_id AND
2388 (
2389 pasa.line_number = p_line_number OR
2390 EXISTS
2391 (
2392 SELECT
2393 paip.line_number
2394 FROM
2395 pon_auction_item_prices_all paip
2396 WHERE
2397 paip.parent_line_number = p_line_number AND
2398 paip.auction_header_id = p_auction_header_id AND
2399 paip.line_number = pasa.line_number
2400 )
2401 );
2402
2403 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
2404 FND_LOG.string (log_level => FND_LOG.level_statement,
2405 module => g_module_prefix || l_module_name,
2406 message => 'shipments deletion complete');
2407 END IF; --}
2408
2409 END IF;--}
2410
2411 -- Call delete on price differentials only if this is a global agreement or
2412 -- this is an RFI
2413 IF (l_doctype_group_name = PON_CONTERMS_UTL_PVT.SRC_REQUEST_FOR_INFORMATION OR
2414 nvl (l_global_agreement_flag, 'Y') = 'Y') THEN --{
2415
2416 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2417 FND_LOG.string (log_level => FND_LOG.level_statement,
2418 module => g_module_prefix || l_module_name,
2419 message => 'This is an RFI or Global Agreement. Need to delete price diffs.');
2420 END IF;
2421
2422 -- Delete the price differentials that belong to this
2423 -- lot/group and also its children
2424 DELETE FROM
2425 pon_price_differentials ppd
2426 WHERE
2427 ppd.auction_header_id = p_auction_header_id AND
2428 (
2429 ppd.line_number = p_line_number OR
2430 EXISTS
2431 (
2432 SELECT
2433 paip.line_number
2434 FROM
2435 pon_auction_item_prices_all paip
2436 WHERE
2437 paip.parent_line_number = p_line_number AND
2438 paip.auction_header_id = p_auction_header_id AND
2439 paip.line_number = ppd.line_number
2440 )
2441 );
2442
2443 END IF; --}
2444
2445 -- Call delete on party line exclusions only if this is not a large negotiation
2446 IF (nvl (l_large_neg_enabled_flag, 'N') = 'N') THEN --{
2447 -- Delete the exclusions that belong to this
2448 -- lot/group and also its children
2449
2450 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2451 FND_LOG.string (log_level => FND_LOG.level_statement,
2452 module => g_module_prefix || l_module_name,
2453 message => 'This is not large so deleting the exclusion entries.');
2454 END IF;
2455
2456 DELETE FROM
2457 pon_party_line_exclusions pple
2458 WHERE
2459 pple.auction_header_id = p_auction_header_id AND
2460 (
2461 pple.line_number = p_line_number OR
2462 EXISTS
2463 (
2464 SELECT
2465 paip.line_number
2466 FROM
2467 pon_auction_item_prices_all paip
2468 WHERE
2469 paip.parent_line_number = p_line_number AND
2470 paip.auction_header_id = p_auction_header_id AND
2471 paip.line_number = pple.line_number
2472 )
2473 );
2474 END IF;
2475
2476 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2477 FND_LOG.string (log_level => FND_LOG.level_statement,
2478 module => g_module_prefix || l_module_name,
2479 message => 'Deleting attachments.');
2480 END IF;
2481
2482 --Delete Attachments
2483 FOR attachment_line IN lines_with_attachements LOOP
2484
2485 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments (
2486 x_entity_name =>'PON_AUCTION_ITEM_PRICES_ALL',
2487 x_pk1_value => p_auction_header_id,
2488 x_pk2_value => attachment_line.line_number,
2489 x_pk3_value => NULL,
2490 x_pk4_value => NULL,
2491 x_pk5_value => NULL);
2492
2493 END LOOP;
2494
2495 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2496 FND_LOG.string (log_level => FND_LOG.level_statement,
2497 module => g_module_prefix || l_module_name,
2498 message => 'Deleting backing requisition line references.');
2499 END IF;
2500
2501 -- Call delete references to backing reqs only if the auction origination
2502 -- code is REQUISITION
2503 IF (l_auction_origination_code = 'REQUISITION') THEN
2504
2505 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2506 FND_LOG.string (log_level => FND_LOG.level_statement,
2507 module => g_module_prefix || l_module_name,
2508 message => 'This auction comes from a requisition.');
2509 END IF;
2510
2511 -- Delete references to the backing req
2512 FOR backing_req_line IN lines_with_backing_requisition LOOP
2513
2514 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2515 FND_LOG.string (log_level => FND_LOG.level_statement,
2516 module => g_module_prefix || l_module_name,
2517 message => 'Deleting backing reqs for line_number = ' || backing_req_line.line_number);
2518 END IF;
2519
2520 PON_AUCTION_PKG.delete_negotiation_line_ref(
2521 x_negotiation_id => p_auction_header_id,
2522 x_negotiation_line_num => backing_req_line.line_number,
2523 x_org_id => backing_req_line.org_id,
2524 x_error_code => x_error_code);
2525
2526 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2527 FND_LOG.string (log_level => FND_LOG.level_statement,
2528 module => g_module_prefix || l_module_name,
2529 message => 'Done deleting reference. x_error_code = ' || x_error_code);
2530 END IF;
2531
2532 IF (x_error_code <> 'SUCCESS') THEN
2533 x_result := FND_API.g_ret_sts_unexp_error;
2534 RETURN;
2535 END IF;
2536
2537 END LOOP;
2538
2539 END IF;
2540
2541 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2542 FND_LOG.string (log_level => FND_LOG.level_statement,
2543 module => g_module_prefix || l_module_name,
2544 message => 'Deleting the lines');
2545 END IF;
2546
2547 -- Finally delete the lines
2548 DELETE FROM
2549 pon_auction_item_prices_all
2550 WHERE
2551 auction_header_id = p_auction_header_id AND
2552 (line_number = p_line_number OR parent_line_number = p_line_number);
2553
2554 /*
2555 * The sync procedure should be called after the line deletion
2556 * It should also be called only for large negotiations
2557 **/
2558
2559 IF (l_large_neg_enabled_flag = 'Y') THEN
2560 sync_pf_values_item_prices (
2561 p_auction_header_id => p_auction_header_id,
2562 p_line_number => null,
2563 p_add_pf => 'N',
2564 p_del_pf => 'Y',
2565 x_result => x_result,
2566 x_error_code => x_error_code,
2567 x_error_message => x_error_message);
2568
2569 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2570 FND_LOG.string (log_level => FND_LOG.level_statement,
2571 module => g_module_prefix || l_module_name,
2572 message => 'Sync pf values procedure called. x_result = ' || x_result ||
2573 ', x_error_code = ' || x_error_code ||
2574 ', x_error_message = ' || x_error_message);
2575 END IF;
2576
2577 IF (x_result <> FND_API.g_ret_sts_success) THEN
2578 RETURN;
2579 END IF;
2580 END IF;
2581
2582 END IF; --}
2583
2584 -- Need to update pon_bidding_parties about the access_type
2585 -- Any supplier who was restricted on the deleted lines
2586 -- should now have access_type set to FULL
2587 UPDATE
2588 pon_bidding_parties
2589 SET
2590 access_type = 'FULL'
2591 WHERE
2592 auction_header_id = p_auction_header_id AND
2593 access_type = 'RESTRICTED' AND
2594 (trading_partner_id, vendor_site_id) NOT IN
2595 (SELECT distinct trading_partner_id, vendor_site_id
2596 FROM pon_party_line_exclusions
2597 WHERE auction_header_id = p_auction_header_id);
2598
2599 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
2600 FND_LOG.string (log_level => FND_LOG.level_procedure,
2601 module => g_module_prefix || l_module_name,
2602 message => 'Leaving procedure = ' || l_module_name);
2603 END IF;
2604
2605 EXCEPTION
2606 WHEN OTHERS THEN
2607 x_result := FND_API.g_ret_sts_unexp_error;
2608 x_error_code := SQLCODE;
2609 x_error_message := SUBSTR(SQLERRM, 1, 100);
2610
2611 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
2612 FND_LOG.string (log_level => FND_LOG.level_exception,
2613 module => g_module_prefix || l_module_name,
2614 message => 'Unexpected exception occured error_code = ' ||
2615 x_error_code || ', error_message = ' || x_error_message);
2616 END IF;
2617
2618 END delete_single_line;
2619
2620 /*======================================================================
2621 PROCEDURE : RENUMBER_LINES
2622 PARAMETERS: 1. p_auction_header_id - The auction header id
2623 2. p_min_disp_line_number_parent - The disp line number
2624 of the minimum LINE/GROUP/LOT from where to correct
2625 the sequences
2626 3. p_min_disp_line_number_child - The disp line number of
2627 the minimum LOT_LINE/GROUP_LINE from where to correct
2628 the sequences.
2629 4. p_min_child_parent_line_num - The parent line number
2630 of the line given in step 3.
2631 5. x_last_line_number - The sub_line_sequence of the last
2632 row that is a lot/line/group.
2633 COMMENT : This procedure will correct the sequence numbers -
2634 SUB_LINE_SEQUENCE_NUMBER, DISP_LINE_NUMBER and
2635 DOCUMENT_DISP_LINE_NUMBER
2636 ======================================================================*/
2637
2638 PROCEDURE RENUMBER_LINES (
2639 x_result OUT NOCOPY VARCHAR2,
2640 x_error_code OUT NOCOPY VARCHAR2,
2641 x_error_message OUT NOCOPY VARCHAR2,
2642 p_auction_header_id IN NUMBER,
2643 p_min_disp_line_number_parent IN NUMBER,
2644 p_min_disp_line_number_child IN NUMBER,
2645 p_min_child_parent_line_num IN NUMBER,
2646 x_last_line_number OUT NOCOPY NUMBER
2647 ) IS
2648
2649 l_new_disp_line_number PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2650 l_sub_line_seq_number PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2651 l_document_disp_line_number PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
2652 l_line_number PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2653 l_parent_line_number PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2654 l_parent_doc_disp_line_number PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
2655 l_parent_max_sub_line_seq_num PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2656
2657 l_max_sub_line_sequence_number NUMBER;
2658 l_max_document_line_num NUMBER;
2659 l_current_parent_line_number NUMBER;
2660 l_current_max_sub_line_seq NUMBER;
2661 l_min_disp_line_number NUMBER;
2662
2663 l_login_id NUMBER;
2664 l_user_id NUMBER;
2665 l_temp NUMBER;
2666 l_module_name VARCHAR2 (30);
2667
2668 l_temp_char VARCHAR2(100);
2669 BEGIN
2670
2671 l_module_name := 'renumber_lines';
2672 x_result := FND_API.g_ret_sts_success;
2673 x_last_line_number := -1;
2674
2675 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
2676
2677 FND_LOG.string(log_level => FND_LOG.level_procedure,
2678 module => g_module_prefix || 'RENUMBER_LINES',
2679 message => 'Entering PON_NEGOTIATION_HELPER_PVT.RENUMBER_LINES'
2680 || ', p_auction_header_id = ' || p_auction_header_id
2681 || ', p_min_disp_line_number_parent = ' || p_min_disp_line_number_parent
2682 || ', p_min_disp_line_number_child = ' || p_min_disp_line_number_child
2683 || ', p_min_child_parent_line_num = ' || p_min_child_parent_line_num);
2684 END IF;
2685
2686 --START: CORRECT_SUB_LINE_SEQUENCE_NUMBER {
2687 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2688
2689 FND_LOG.string(log_level => FND_LOG.level_statement,
2690 module => g_module_prefix || 'RENUMBER_LINES',
2691 message => 'Selecting the maximum sub_line_sequence_number from the header');
2692 END IF;
2693
2694 --GET THE MAX_DOCUMENT_LINE_NUM (This is the maximum sub_line_sequence_number
2695 --from the previous neg) FROM THE HEADER
2696 SELECT
2697 NVL(MAX_DOCUMENT_LINE_NUM,0)
2698 INTO
2699 l_max_document_line_num
2700 FROM
2701 PON_AUCTION_HEADERS_ALL
2702 WHERE
2703 AUCTION_HEADER_ID = p_auction_header_id;
2704
2705 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2706
2707 FND_LOG.string(log_level => FND_LOG.level_statement,
2708 module => g_module_prefix || 'RENUMBER_LINES',
2709 message => 'The maximum sub_line_sequence_number from the header = ' || l_max_document_line_num);
2710 END IF;
2711
2712 --START: CORRECT FOR LINES, LOTS, GROUPS
2713 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2714
2715 FND_LOG.string(log_level => FND_LOG.level_statement,
2716 module => g_module_prefix || 'RENUMBER_LINES',
2717 message => 'Retrieving the line_numbers into a table of numbers');
2718 END IF;
2719
2720 --GET THE LINES, LOTS AND GROUPS FIRST
2721 SELECT
2722 LINE_NUMBER
2723 BULK COLLECT INTO
2724 l_line_number
2725 FROM
2726 PON_AUCTION_ITEM_PRICES_ALL
2727 WHERE
2728 AUCTION_HEADER_ID = p_auction_header_id AND
2729 GROUP_TYPE IN ('LOT', 'GROUP', 'LINE') AND
2730 SUB_LINE_SEQUENCE_NUMBER > l_max_document_line_num AND
2731 DISP_LINE_NUMBER > p_min_disp_line_number_parent
2732 ORDER BY
2733 DISP_LINE_NUMBER;
2734
2735 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2736
2737 FND_LOG.string(log_level => FND_LOG.level_statement,
2738 module => g_module_prefix || 'RENUMBER_LINES',
2739 message => 'Number of lines collected = ' || l_line_number.COUNT);
2740 END IF;
2741
2742 l_login_id := FND_GLOBAL.LOGIN_ID;
2743 l_user_id := FND_GLOBAL.USER_ID;
2744
2745 --CHECK IF ANY LINES EXIST AFTER THE MIN DISP_LINE_NUMBER
2746 --IF NOT THEN NO NEED TO RENUMBER ANY PARENT TYPE LINES
2747 IF (l_line_number.COUNT > 0) THEN --{
2748
2749 --GET THE GREATEST SUB_LINE_SEQUENCE_NUMBER WHOSE
2750 --DISP_LINE_NUMBER IS LESS THAN p_min_disp_line_number_parent
2751
2752 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2753
2754 FND_LOG.string(log_level => FND_LOG.level_statement,
2755 module => g_module_prefix || 'RENUMBER_LINES',
2756 message => 'Getting the maximum sub_line_sequence_number from the items table');
2757 END IF;
2758
2759 SELECT
2760 NVL (MAX (SUB_LINE_SEQUENCE_NUMBER), 0)
2761 INTO
2762 l_max_sub_line_sequence_number
2763 FROM
2764 PON_AUCTION_ITEM_PRICES_ALL
2765 WHERE
2766 AUCTION_HEADER_ID = p_auction_header_id AND
2767 DISP_LINE_NUMBER < p_min_disp_line_number_parent AND
2768 GROUP_TYPE IN ('LINE','LOT', 'GROUP');
2769
2770 --IN GENERAL THE l_max_sub_line_sequence_number WILL BE GREATER
2771 --SO CHECK FOR THE RARER CONDITION
2772
2773 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2774
2775 FND_LOG.string(log_level => FND_LOG.level_statement,
2776 module => g_module_prefix || 'RENUMBER_LINES',
2777 message => 'Checking where to start the sequencing');
2778 END IF;
2779
2780 IF (l_max_sub_line_sequence_number < l_max_document_line_num) THEN
2781
2782 l_max_sub_line_sequence_number := l_max_document_line_num;
2783 END IF;
2784
2785 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2786
2787 FND_LOG.string(log_level => FND_LOG.level_statement,
2788 module => g_module_prefix || 'RENUMBER_LINES',
2789 message => 'Sequencing will start at l_max_document_line_num = ' || l_max_document_line_num);
2790 END IF;
2791
2792 --CORRECT THE SUB_LINE_SEQUENCE_NUMBER (Same as DOCUMENT_DISP_LINE_NUMBER)
2793 FOR x IN 1..l_line_number.COUNT
2794 LOOP
2795
2796 l_max_sub_line_sequence_number := l_max_sub_line_sequence_number + 1;
2797 l_sub_line_seq_number (x) := l_max_sub_line_sequence_number;
2798 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2799
2800 FND_LOG.string(log_level => FND_LOG.level_statement,
2801 module => g_module_prefix || 'RENUMBER_LINES',
2802 message => 'Calculating the sub_line_sequence_number for line_number (' || l_line_number(x) || ' as ' || l_sub_line_seq_number (x));
2803 END IF;
2804 END LOOP;
2805
2806 --UPDATE THE LINES, LOTS AND GROUPS WITH THE NEW VALUES
2807 FORALL x in 1..l_line_number.COUNT
2808 UPDATE
2809 PON_AUCTION_ITEM_PRICES_ALL
2810 SET
2811 SUB_LINE_SEQUENCE_NUMBER = l_sub_line_seq_number (x),
2812 DOCUMENT_DISP_LINE_NUMBER = l_sub_line_seq_number (x),
2813 LAST_UPDATE_DATE = SYSDATE,
2814 LAST_UPDATE_LOGIN = l_login_id,
2815 LAST_UPDATED_BY = l_user_id
2816 WHERE
2817 AUCTION_HEADER_ID = p_auction_header_id AND
2818 LINE_NUMBER = l_line_number (x);
2819
2820 END IF; --}
2821
2822 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2823
2824 FND_LOG.string(log_level => FND_LOG.level_statement,
2825 module => g_module_prefix || 'RENUMBER_LINES',
2826 message => 'Starting correction of sub_line_sequence_number for lot_lines and group_lines');
2827 END IF;
2828
2829 --START: CORRECT THE SUB_LINE_SEQUENCE NUMBER FOR LOT_LINES AND GROUP_LINES
2830 --GET THE LOT LINES AND GROUP LINES
2831 SELECT
2832 CHILDREN.LINE_NUMBER,
2833 NVL (PARENT.MAX_SUB_LINE_SEQUENCE_NUMBER, 0),
2834 CHILDREN.PARENT_LINE_NUMBER,
2835 PARENT.DOCUMENT_DISP_LINE_NUMBER
2836 BULK COLLECT INTO
2837 l_line_number,
2838 l_parent_max_sub_line_seq_num,
2839 l_parent_line_number,
2840 l_parent_doc_disp_line_number
2841 FROM
2842 PON_AUCTION_ITEM_PRICES_ALL CHILDREN,
2843 PON_AUCTION_ITEM_PRICES_ALL PARENT
2844 WHERE
2845 CHILDREN.AUCTION_HEADER_ID = p_auction_header_id AND
2846 PARENT.AUCTION_HEADER_ID = p_auction_header_id AND
2847 PARENT.LINE_NUMBER = CHILDREN.PARENT_LINE_NUMBER AND
2848 CHILDREN.GROUP_TYPE IN ('LOT_LINE', 'GROUP_LINE') AND
2849 CHILDREN.SUB_LINE_SEQUENCE_NUMBER > NVL(PARENT.MAX_SUB_LINE_SEQUENCE_NUMBER,0) AND
2850 (CHILDREN.DISP_LINE_NUMBER > p_min_disp_line_number_child OR
2851 CHILDREN.DISP_LINE_NUMBER > p_min_disp_line_number_parent)
2852 ORDER BY
2853 CHILDREN.DISP_LINE_NUMBER;
2854
2855 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2856
2857 FND_LOG.string(log_level => FND_LOG.level_statement,
2858 module => g_module_prefix || 'RENUMBER_LINES',
2859 message => 'Number of lines to be corrected = ' || l_line_number.COUNT);
2860 END IF;
2861
2862 IF (l_line_number.COUNT > 0) THEN --{
2863
2864 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2865 FND_LOG.string(log_level => FND_LOG.level_statement,
2866 module => g_module_prefix || 'RENUMBER_LINES',
2867 message => 'p_min_disp_line_number_child = ' || p_min_disp_line_number_child ||
2868 ', p_min_disp_line_number_parent = ' || p_min_disp_line_number_parent);
2869 END IF;
2870
2871 IF (p_min_disp_line_number_child <> 0 AND
2872 p_min_disp_line_number_child < nvl (p_min_disp_line_number_parent, p_min_disp_line_number_child + 1)) THEN -- {
2873
2874 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2875 FND_LOG.string(log_level => FND_LOG.level_statement,
2876 module => g_module_prefix || 'RENUMBER_LINES',
2877 message => 'The min disp line number child is not zero');
2878 END IF;
2879
2880 SELECT
2881 NVL (MAX (SUB_LINE_SEQUENCE_NUMBER), 0)
2882 INTO
2883 l_current_max_sub_line_seq
2884 FROM
2885 PON_AUCTION_ITEM_PRICES_ALL
2886 WHERE
2887 AUCTION_HEADER_ID = p_auction_header_id AND
2888 PARENT_LINE_NUMBER = p_min_child_parent_line_num AND
2889 DISP_LINE_NUMBER < p_min_disp_line_number_child;
2890
2891 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2892 FND_LOG.string(log_level => FND_LOG.level_statement,
2893 module => g_module_prefix || 'RENUMBER_LINES',
2894 message => 'l_current_max_sub_line_seq = ' || l_current_max_sub_line_seq ||
2895 'l_parent_max_sub_line_seq_num(1) = ' || l_parent_max_sub_line_seq_num(1));
2896 END IF;
2897
2898 l_current_parent_line_number := p_min_child_parent_line_num;
2899
2900 IF (l_current_max_sub_line_seq < l_parent_max_sub_line_seq_num (1)) THEN
2901 l_current_max_sub_line_seq := l_parent_max_sub_line_seq_num (1);
2902 END IF;
2903
2904 ELSE
2905
2906 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2907 FND_LOG.string(log_level => FND_LOG.level_statement,
2908 module => g_module_prefix || 'RENUMBER_LINES',
2909 message => 'is zero l_current_max_sub_line_seq = ' || l_current_max_sub_line_seq ||
2910 'l_parent_max_sub_line_seq_num(1) = ' || l_parent_max_sub_line_seq_num(1));
2911 END IF;
2912
2913 l_current_parent_line_number := l_parent_line_number (1);
2914 l_current_max_sub_line_seq := l_parent_max_sub_line_seq_num (1);
2915 END IF; -- }
2916
2917 --CORRECT THE SUB_LINE_SEQUENCE_NUMBER AND DOCUMENT_DISP_LINE_NUMBER
2918 FOR x IN 1..l_line_number.COUNT
2919 LOOP
2920
2921 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2922 FND_LOG.string(log_level => FND_LOG.level_statement,
2923 module => g_module_prefix || 'RENUMBER_LINES',
2924 message => 'Determining the display for ' || l_line_number (x) ||
2925 ', l_current_parent_line_number = ' || l_current_parent_line_number ||
2926 ', l_parent_line_number (x) ' || l_parent_line_number(x));
2927 END IF;
2928
2929 --WITHIN THE SAME PARENT
2930 IF (l_current_parent_line_number = l_parent_line_number(x)) THEN
2931 l_current_max_sub_line_seq := l_current_max_sub_line_seq + 1;
2932
2933 --NEW PARENT
2934 ELSE
2935 l_current_max_sub_line_seq := l_parent_max_sub_line_seq_num (x) + 1;
2936 l_current_parent_line_number := l_parent_line_number(x);
2937
2938 END IF;
2939
2940 -- CORRECT THE SUB_LINE_SEQUENCE_NUMBER AND DOCUMENT_DISP_LINE_NUMBER
2941 l_sub_line_seq_number (x) := l_current_max_sub_line_seq;
2942 l_document_disp_line_number (x) := l_parent_doc_disp_line_number(x) || '.' || l_current_max_sub_line_seq;
2943
2944 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2945 FND_LOG.string(log_level => FND_LOG.level_statement,
2946 module => g_module_prefix || 'RENUMBER_LINES',
2947 message => 'Determined ' ||
2948 ', l_sub_line_seq_number(x) = ' || l_sub_line_seq_number (x)||
2949 ', l_document_disp_line_number(x) ' || l_document_disp_line_number(x));
2950 END IF;
2951 END LOOP;
2952
2953 --UPDATE THE LOT_LINES AND GROUP_LINES WITH THE NEW VALUES
2954 FORALL x in 1..l_line_number.COUNT
2955 UPDATE PON_AUCTION_ITEM_PRICES_ALL
2956 SET
2957 SUB_LINE_SEQUENCE_NUMBER = l_sub_line_seq_number (x),
2958 DOCUMENT_DISP_LINE_NUMBER = l_document_disp_line_number (x),
2959 LAST_UPDATE_DATE = SYSDATE,
2960 LAST_UPDATE_LOGIN = l_login_id,
2961 LAST_UPDATED_BY = l_user_id
2962 WHERE
2963 AUCTION_HEADER_ID = p_auction_header_id AND
2964 LINE_NUMBER = l_line_number (x);
2965 END IF; --}
2966
2967 --END: CORRECTING SUB_LINE_SEQUENCE_NUMBER --}
2968
2969 --START: CORRECT_DISP_LINE_NUMBER {
2970 --COLLECT THE LINE_NUMBER VALUES INTO AN
2971 --ARRAY ORDERED BY THE DISP_LINE_NUMBER
2972
2973 IF (p_min_disp_line_number_child IS NULL) THEN
2974 l_min_disp_line_number := p_min_disp_line_number_parent;
2975
2976 ELSIF (p_min_disp_line_number_parent IS NULL) THEN
2977 l_min_disp_line_number := p_min_disp_line_number_child;
2978
2979 ELSIF (p_min_disp_line_number_child < p_min_disp_line_number_parent) THEN
2980 l_min_disp_line_number := p_min_disp_line_number_child;
2981
2982 ELSE
2983 l_min_disp_line_number := p_min_disp_line_number_parent;
2984
2985 END IF;
2986
2987 SELECT
2988 LINE_NUMBER
2989 BULK COLLECT INTO
2990 l_line_number
2991 FROM
2992 PON_AUCTION_ITEM_PRICES_ALL
2993 WHERE
2994 AUCTION_HEADER_ID = p_auction_header_id AND
2995 DISP_LINE_NUMBER > l_min_disp_line_number
2996 ORDER BY
2997 DISP_LINE_NUMBER;
2998
2999 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3000 FND_LOG.string(log_level => FND_LOG.level_statement,
3001 module => g_module_prefix || 'RENUMBER_LINES',
3002 message => 'Obtained ' || l_line_number.COUNT || ' lines to correct');
3003 END IF;
3004
3005 --IF THERE ARE NO LINES BELOW THIS LINE
3006 --THEN NO RENUMBERING IS REQUIRED
3007 IF (l_line_number.COUNT = 0) THEN
3008 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3009 FND_LOG.string(log_level => FND_LOG.level_statement,
3010 module => g_module_prefix || 'RENUMBER_LINES',
3011 message => 'Returning without doing anything as there are no lines to renumber');
3012 END IF;
3013
3014 SELECT
3015 MAX(sub_line_sequence_number)
3016 INTO
3017 l_temp_char
3018 FROM
3019 pon_auction_item_prices_all
3020 WHERE
3021 auction_header_id = p_auction_header_id and
3022 group_type IN ('LOT', 'LINE', 'GROUP');
3023
3024 IF l_temp_char IS NULL THEN
3025 x_last_line_number := -1;
3026 ELSE
3027 x_last_line_number := to_number (l_temp_char);
3028 END IF;
3029
3030 RETURN;
3031 END IF;
3032
3033 --DETERMINE THE NEXT INTEGER TO START WITH
3034 l_temp := floor (l_min_disp_line_number);
3035 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3036 FND_LOG.string(log_level => FND_LOG.level_statement,
3037 module => g_module_prefix || 'RENUMBER_LINES',
3038 message => 'The new disp line number should start after ' || l_temp);
3039 END IF;
3040
3041 --DETERMINE THE NEW DISP_LINE_NUMBER
3042 FOR y IN 1..l_line_number.COUNT --{
3043 LOOP
3044
3045 l_new_disp_line_number (y) := y + l_temp;
3046
3047 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3048 FND_LOG.string(log_level => FND_LOG.level_statement,
3049 module => g_module_prefix || 'RENUMBER_LINES',
3050 message => 'Determined that ' || l_new_disp_line_number (y)
3051 || ' is the new disp_line_number of ' || l_line_number (y));
3052 END IF;
3053 END LOOP; --}
3054
3055 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3056 FND_LOG.string(log_level => FND_LOG.level_statement,
3057 module => g_module_prefix || 'RENUMBER_LINES',
3058 message => 'Calling bulk update to set the new disp_line_number');
3059 END IF;
3060
3061 --BULK UPDATE TO SET THE NEW DISP_LINE_NUMBER
3062 FORALL x IN 1..l_line_number.COUNT
3063 UPDATE PON_AUCTION_ITEM_PRICES_ALL
3064 SET DISP_LINE_NUMBER = l_new_disp_line_number (x)
3065 WHERE LINE_NUMBER = l_line_number(x)
3066 AND AUCTION_HEADER_ID = p_auction_header_id;
3067
3068 --}END: CORRECT_DISP_LINE_NUMBER
3069
3070 SELECT
3071 MAX(sub_line_sequence_number)
3072 INTO
3073 x_last_line_number
3074 FROM
3075 pon_auction_item_prices_all
3076 WHERE
3077 auction_header_id = p_auction_header_id and
3078 group_type IN ('LOT', 'LINE', 'GROUP');
3079
3080 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
3081 FND_LOG.string(log_level => FND_LOG.level_procedure,
3082 module => g_module_prefix || 'RENUMBER_LINES',
3083 message => 'Leaving PON_NEGOTIATION_HELPER_PVT.RENUMBER_LINES');
3084 END IF;
3085
3086 EXCEPTION
3087 WHEN OTHERS THEN
3088 x_result := FND_API.g_ret_sts_unexp_error;
3089 x_error_code := SQLCODE;
3090 x_error_message := SUBSTR(SQLERRM, 1, 100);
3091
3092 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
3093 FND_LOG.string (log_level => FND_LOG.level_exception,
3094 module => g_module_prefix || l_module_name,
3095 message => 'Unexpected exception occured error_code = ' ||
3096 x_error_code || ', error_message = ' || x_error_message);
3097 END IF;
3098
3099 END RENUMBER_LINES;
3100
3101
3102 /*======================================================================
3103 PROCEDURE : get_srch_min_disp_line_numbers
3104 PARAMETERS: 1. p_curr_auction_header_id - The current auction header id
3105 2. p_prev_auction_header_id - The previous auction header id
3106 3. p_value - The value entered by the user for search
3107 4. x_curr_min_disp_line_num - Out parameter to indicate at which
3108 line to start displaying for current auction
3109 5. x_prev_min_disp_line_num - Out parameter to indicate at which
3110 line to start displaying for previous auction
3111 COMMENT : This procedure is invoked when the user searches on the
3112 lines region with line number as the search criteria
3113 and greater than as the search condition.
3114 Given the value entered by the user (p_value) this
3115 procedure will return the disp_line_number above which
3116 all lines should be shown.
3117 ======================================================================*/
3118
3119 PROCEDURE get_srch_min_disp_line_numbers(
3120 p_curr_auction_header_id IN NUMBER,
3121 p_prev_auction_header_id IN NUMBER,
3122 p_value IN NUMBER,
3123 x_curr_min_disp_line_num OUT NOCOPY NUMBER,
3124 x_prev_min_disp_line_num OUT NOCOPY NUMBER
3125 ) IS
3126 BEGIN
3127
3128 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3129 FND_LOG.string(log_level => FND_LOG.level_statement,
3130 module => g_module_prefix || 'GET_SRCH_MIN_DISP_LINE_NUMBERS',
3131 message => 'Entering PON_NEGOTIATION_HELPER_PVT.GET_SRCH_MIN_DISP_LINE_NUMBERS'
3132 || ', p_curr_auction_header_id = ' || p_curr_auction_header_id
3133 || ', p_prev_auction_header_id = ' || p_prev_auction_header_id
3134 || ', p_value = ' || p_value);
3135 END IF;
3136
3137 --Retrieve the minimum disp_line_number of all the LOT/GROUP/LINES
3138 --that have SUB_LINE_SEQUENCE_NUMBER > p_value
3139
3140 SELECT MIN(disp_line_number)
3141 INTO x_curr_min_disp_line_num
3142 FROM PON_AUCTION_ITEM_PRICES_ALL
3143 WHERE AUCTION_HEADER_ID = p_curr_auction_header_id
3144 AND GROUP_TYPE IN ('LOT', 'GROUP', 'LINE')
3145 AND SUB_LINE_SEQUENCE_NUMBER > p_value;
3146
3147 SELECT MIN(disp_line_number)
3148 INTO x_prev_min_disp_line_num
3149 FROM PON_AUCTION_ITEM_PRICES_ALL
3150 WHERE AUCTION_HEADER_ID = p_prev_auction_header_id
3151 AND GROUP_TYPE IN ('LOT', 'GROUP', 'LINE')
3152 AND SUB_LINE_SEQUENCE_NUMBER > p_value;
3153
3154 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3155 FND_LOG.string(log_level => FND_LOG.level_statement,
3156 module => g_module_prefix || 'GET_SRCH_MIN_DISP_LINE_NUMBERS',
3157 message => 'Leaving PON_NEGOTIATION_HELPER_PVT.GET_SRCH_MIN_DISP_LINE_NUMBERS'
3158 || 'x_curr_min_disp_line_num = ' || x_curr_min_disp_line_num
3159 || 'x_prev_min_disp_line_num = ' || x_prev_min_disp_line_num
3160 );
3161 END IF;
3162
3163 END GET_SRCH_MIN_DISP_LINE_NUMBERS;
3164
3165 /*======================================================================
3166 PROCEDURE : get_srch_max_disp_line_numbers
3167 PARAMETERS: 1. p_curr_auction_header_id - The current auction header id
3168 2. p_prev_auction_header_id - The previous auction header id
3169 3. p_value - The value entered by the user for search
3170 4. x_curr_max_disp_line_num - Out parameter to indicate at which
3171 line to stop displaying
3172 5. x_prev_max_disp_line_num - Out parameter to indicate at which
3173 line to stop displaying
3174 COMMENT : This procedure is invoked when the user searches on the
3175 lines region with line number as the search criteria
3176 and less than as the search condition.
3177 Given the value entered by the user (p_value) this
3178 procedure will return the disp_line_number below which
3179 all lines should be shown.
3180 ======================================================================*/
3181
3182 PROCEDURE get_srch_max_disp_line_numbers (
3183 p_curr_auction_header_id IN NUMBER,
3184 p_prev_auction_header_id IN NUMBER,
3185 p_value IN NUMBER,
3186 x_curr_max_disp_line_num OUT NOCOPY NUMBER,
3187 x_prev_max_disp_line_num OUT NOCOPY NUMBER
3188 ) IS
3189
3190 l_curr_line_number PON_AUCTION_ITEM_PRICES_ALL.LINE_NUMBER%TYPE;
3191 l_curr_group_type PON_AUCTION_ITEM_PRICES_ALL.GROUP_TYPE%TYPE;
3192 l_prev_line_number PON_AUCTION_ITEM_PRICES_ALL.LINE_NUMBER%TYPE;
3193 l_prev_group_type PON_AUCTION_ITEM_PRICES_ALL.GROUP_TYPE%TYPE;
3194
3195 BEGIN
3196
3197 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3198 FND_LOG.string(log_level => FND_LOG.level_statement,
3199 module => g_module_prefix || 'GET_SRCH_MAX_DISP_LINE_NUMBERS',
3200 message => 'Entering PON_NEGOTIATION_HELPER_PVT.GET_SRCH_MAX_DISP_LINE_NUMBERS '
3201 || ', p_curr_auction_header_id = ' || p_curr_auction_header_id
3202 || ', p_prev_auction_header_id = ' || p_prev_auction_header_id
3203 || ', p_value = ' || p_value);
3204 END IF;
3205
3206 --Retrieve the maximum disp_line_number of all the LOT/GROUP/LINES
3207 --that have SUB_LINE_SEQUENCE_NUMBER < p_value
3208
3209 SELECT MAX(DISP_LINE_NUMBER)
3210 INTO x_curr_max_disp_line_num
3211 FROM PON_AUCTION_ITEM_PRICES_ALL
3212 WHERE AUCTION_HEADER_ID = p_curr_auction_header_id
3213 AND GROUP_TYPE IN ('LOT', 'LINE', 'GROUP')
3214 AND SUB_LINE_SEQUENCE_NUMBER < p_value;
3215
3216 SELECT MAX(DISP_LINE_NUMBER)
3217 INTO x_prev_max_disp_line_num
3218 FROM PON_AUCTION_ITEM_PRICES_ALL
3219 WHERE AUCTION_HEADER_ID = p_prev_auction_header_id
3220 AND GROUP_TYPE IN ('LOT', 'LINE', 'GROUP')
3221 AND SUB_LINE_SEQUENCE_NUMBER < p_value;
3222
3223 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3224 FND_LOG.string(log_level => FND_LOG.level_statement,
3225 module => g_module_prefix || 'GET_SRCH_MAX_DISP_LINE_NUMBERS',
3226 message => 'After the first query x_curr_max_disp_line_num = ' ||
3227 x_curr_max_disp_line_num || ' and x_prev_max_disp_line_num = ' || x_prev_max_disp_line_num);
3228 END IF;
3229
3230 IF (x_curr_max_disp_line_num IS NULL AND x_prev_max_disp_line_num IS NULL) THEN
3231
3232 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3233 FND_LOG.string(log_level => FND_LOG.level_statement,
3234 module => g_module_prefix || 'GET_SRCH_MAX_DISP_LINE_NUMBERS',
3235 message => 'There are no lines in both the auctions so returning null');
3236 END IF;
3237
3238 RETURN;
3239 END IF;
3240
3241 SELECT GROUP_TYPE, LINE_NUMBER
3242 INTO l_curr_group_type, l_curr_line_number
3243 FROM PON_AUCTION_ITEM_PRICES_ALL
3244 WHERE AUCTION_HEADER_ID = p_curr_auction_header_id
3245 AND DISP_LINE_NUMBER = x_curr_max_disp_line_num;
3246
3247 SELECT GROUP_TYPE, LINE_NUMBER
3248 INTO l_prev_group_type, l_prev_line_number
3249 FROM PON_AUCTION_ITEM_PRICES_ALL
3250 WHERE AUCTION_HEADER_ID = p_prev_auction_header_id
3251 AND DISP_LINE_NUMBER = x_prev_max_disp_line_num;
3252
3253 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3254 FND_LOG.string(log_level => FND_LOG.level_statement,
3255 module => g_module_prefix || 'GET_SEARCH_MAX_DISP_LINE_NUM',
3256 message => 'l_curr_group_type = ' || l_curr_group_type
3257 || ', l_curr_line_number = ' || l_curr_line_number
3258 ||'l_prev_group_type = ' || l_prev_group_type
3259 || ', l_prev_line_number = ' || l_prev_line_number
3260 );
3261 END IF;
3262
3263 --If the selected line is a LOT/GROUP then get the maximum
3264 --disp_line_number within that LOT/GROUP
3265
3266 IF (l_curr_group_type <> 'LINE') THEN
3267 SELECT NVL (MAX(DISP_LINE_NUMBER), x_curr_max_disp_line_num)
3268 INTO x_curr_max_disp_line_num
3269 FROM PON_AUCTION_ITEM_PRICES_ALL
3270 WHERE AUCTION_HEADER_ID = p_curr_auction_header_id
3271 AND PARENT_LINE_NUMBER = l_curr_line_number;
3272 END IF;
3273
3274 IF (l_prev_group_type <> 'LINE') THEN
3275 SELECT NVL (MAX(DISP_LINE_NUMBER), x_prev_max_disp_line_num)
3276 INTO x_prev_max_disp_line_num
3277 FROM PON_AUCTION_ITEM_PRICES_ALL
3278 WHERE AUCTION_HEADER_ID = p_prev_auction_header_id
3279 AND PARENT_LINE_NUMBER = l_prev_line_number;
3280 END IF;
3281
3282 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3283 FND_LOG.string(log_level => FND_LOG.level_statement,
3284 module => g_module_prefix || 'GET_SEARCH_MAX_DISP_LINE_NUM',
3285 message => 'Leaving PON_NEGOTIATION_HELPER_PVT.GET_AUCTION_REQUEST_ID '
3286 || ', x_curr_max_disp_line_num = ' || x_curr_max_disp_line_num
3287 || ', x_prev_max_disp_line_num = ' || x_prev_max_disp_line_num);
3288 END IF;
3289
3290 END GET_SRCH_MAX_DISP_LINE_NUMBERS;
3291
3292
3293 /*======================================================================
3294 PROCEDURE : DELETE_DISCUSSIONS
3295 PARAMETERS: 1. x_result - return status.
3296 2. x_error_code - error code
3297 3. x_error_message - The actual error message
3298 4. p_auction_header_id - The auction header id
3299 COMMENT : This procedure deletes all the discussions in the negotiation
3300 and also its children
3301 ======================================================================*/
3302
3303 PROCEDURE DELETE_DISCUSSIONS (
3304 x_result OUT NOCOPY VARCHAR2,
3305 x_error_code OUT NOCOPY VARCHAR2,
3306 x_error_message OUT NOCOPY VARCHAR2,
3307 p_auction_header_id IN NUMBER
3308 ) IS
3309
3310
3311 l_module_name VARCHAR2 (30);
3312 l_discussion_id PON_DISCUSSIONS.DISCUSSION_ID%TYPE;
3313
3314
3315 BEGIN
3316
3317 l_module_name := 'DELETE_DISCUSSIONS';
3318 x_result := FND_API.g_ret_sts_success;
3319
3320 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
3321 FND_LOG.string (log_level => FND_LOG.level_procedure,
3322 module => g_module_prefix || l_module_name,
3323 message => 'Entered procedure = ' || l_module_name ||
3324 ' p_auction_header_id = ' || p_auction_header_id);
3325 END IF;
3326
3327 BEGIN
3328 SELECT discussion_id
3329 INTO l_discussion_id
3330 FROM pon_discussions
3331 WHERE pk1_value = p_auction_header_id;
3332
3333 EXCEPTION WHEN NO_DATA_FOUND THEN
3334 RETURN;
3335 END;
3336
3337 --delete from PON_TE_RECIPIENTS
3338 DELETE FROM
3339 PON_TE_RECIPIENTS
3340 WHERE
3341 ENTRY_ID IN ( SELECT ENTRY_ID
3342 FROM PON_THREAD_ENTRIES
3343 WHERE DISCUSSION_ID = l_discussion_id);
3344
3345 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3346 FND_LOG.string (log_level => FND_LOG.level_statement,
3347 module => g_module_prefix || l_module_name,
3348 message => 'PON_TE_RECIPIENTS records deleted');
3349 END IF;
3350
3351 --delete from PON_TE_VIEW_AUDIT
3352 DELETE FROM
3353 PON_TE_VIEW_AUDIT
3354 WHERE
3355 ENTRY_ID IN ( SELECT ENTRY_ID
3356 FROM PON_THREAD_ENTRIES
3357 WHERE DISCUSSION_ID = l_discussion_id);
3358
3359 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3360 FND_LOG.string (log_level => FND_LOG.level_statement,
3361 module => g_module_prefix || l_module_name,
3362 message => 'PON_TE_VIEW_AUDIT records deleted');
3363 END IF;
3364
3365 DELETE FROM
3366 PON_THREAD_ENTRIES
3367 WHERE
3368 DISCUSSION_ID = l_discussion_id;
3369
3370 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3371 FND_LOG.string (log_level => FND_LOG.level_statement,
3372 module => g_module_prefix || l_module_name,
3373 message => 'PON_THREAD_ENTRIES records deleted');
3374 END IF;
3375
3376 DELETE FROM
3377 PON_THREADS
3378 WHERE
3379 DISCUSSION_ID = l_discussion_id;
3380
3381 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3382 FND_LOG.string (log_level => FND_LOG.level_statement,
3383 module => g_module_prefix || l_module_name,
3384 message => 'PON_THREADS records deleted');
3385 END IF;
3386
3387 DELETE FROM
3388 PON_DISCUSSIONS
3389 WHERE
3390 DISCUSSION_ID = l_discussion_id;
3391
3392 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3393 FND_LOG.string (log_level => FND_LOG.level_statement,
3394 module => g_module_prefix || l_module_name,
3395 message => 'PON_DISCUSSIONS records deleted');
3396 END IF;
3397
3398 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
3399 FND_LOG.string (log_level => FND_LOG.level_procedure,
3400 module => g_module_prefix || l_module_name,
3401 message => 'Leaving procedure = ' || l_module_name);
3402 END IF;
3403
3404 EXCEPTION
3405 WHEN OTHERS THEN
3406 x_result := FND_API.g_ret_sts_unexp_error;
3407 x_error_code := SQLCODE;
3408 x_error_message := SUBSTR(SQLERRM, 1, 100);
3409
3410 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
3411 FND_LOG.string (log_level => FND_LOG.level_exception,
3412 module => g_module_prefix || l_module_name,
3413 message => 'Unexpected exception occured error_code = ' ||
3414 x_error_code || ', error_message = ' || x_error_message);
3415 END IF;
3416 END DELETE_DISCUSSIONS;
3417
3418 /*======================================================================
3419 PROCEDURE : UPDATE_STAG_LINES_CLOSE_DATES
3420 PARAMETERS: 1. p_auction_header_id - The auction header id
3421 2. p_first_line_close_date - The staggered closing interval
3422 3. p_staggered_closing_interval - The auction header id
3423 4. x_last_line_close_date - The close date of the last line
3424 5. x_result - return status.
3425 6. x_error_code - error code
3426 7. x_error_message - The actual error message
3427 COMMENT : This procedure updates the close dates of the lines when
3428 the draft negotiation is saved
3429 ======================================================================*/
3430
3431 PROCEDURE UPDATE_STAG_LINES_CLOSE_DATES(
3432 x_result OUT NOCOPY VARCHAR2,
3433 x_error_code OUT NOCOPY VARCHAR2,
3434 x_error_message OUT NOCOPY VARCHAR2,
3435 p_auction_header_id in Number,
3436 p_first_line_close_date in date,
3437 p_staggered_closing_interval in number,
3438 p_start_disp_line_number in number,
3439 x_last_line_close_date out nocopy date
3440 )
3441 is
3442 l_line_number PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
3443 l_close_date PON_NEG_COPY_DATATYPES_GRP.SIMPLE_DATE_TYPE;
3444 l_group_type PON_NEG_COPY_DATATYPES_GRP.VARCHAR100_TYPE;
3445 l_batch_start NUMBER ;
3446 l_batch_end NUMBER ;
3447 l_batch_size NUMBER ;
3448 l_max_line_number NUMBER;
3449 l_curr_close_date DATE;
3450 l_stag_interval NUMBER;
3451
3452 BEGIN
3453 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3454 FND_LOG.string(log_level => FND_LOG.level_statement,
3455 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3456 message => 'Entered PON_NEGOTIATION_HELPER_PVT.UPDATE_LINES_CLOSE_DATES'
3457 || ', p_auction_header_id = ' || p_auction_header_id
3458 || ', p_first_line_close_date = ' || p_first_line_close_date
3459 || ', p_staggered_closing_interval = ' || p_staggered_closing_interval
3460 || ', p_start_disp_line_number = ' || p_start_disp_line_number
3461 );
3462 END IF;
3463
3464 x_result := FND_API.G_RET_STS_UNEXP_ERROR;
3465
3466 SELECT max(disp_line_number)
3467 INTO l_max_line_number
3468 FROM pon_auction_item_prices_all WHERE auction_header_id = p_auction_header_id;
3469
3470 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3471 FND_LOG.string(log_level => FND_LOG.level_statement,
3472 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3473 message => 'l_max_line_number : ' || l_max_line_number
3474 );
3475 END IF;
3476
3477 IF (l_max_line_number) > 0 then --{
3478
3479 IF (p_start_disp_line_number > l_max_line_number) THEN --{
3480
3481 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3482 FND_LOG.string(log_level => FND_LOG.level_statement,
3483 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3484 message => 'p_start_disp_line_number > l_max_line_number; so returning'
3485 );
3486 END IF;
3487
3488 select nvl (max(close_bidding_date), p_first_line_close_date)
3489 into x_last_line_close_date
3490 from pon_auction_item_prices_all
3491 where auction_header_id = p_auction_header_id;
3492
3493 return;
3494 END IF; --}
3495
3496 l_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
3497 l_stag_interval := p_staggered_closing_interval/1440;
3498
3499 select nvl (max(close_bidding_date), (p_first_line_close_date - l_stag_interval))
3500 into l_curr_close_date
3501 from pon_auction_item_prices_all
3502 where auction_header_id = p_auction_header_id
3503 and disp_line_number < p_start_disp_line_number;
3504
3505 --we offset the current close date back by the staggered interval
3506 --so that the close date of the first line/lot/group is set the the
3507 --first line close date in the loop
3508
3509 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3510 FND_LOG.string(log_level => FND_LOG.level_statement,
3511 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3512 message => 'l_batch_size : ' || l_batch_size
3513 ||'; l_stag_interval : ' || l_stag_interval
3514 ||'; l_curr_close_date : ' || to_char (l_curr_close_date, 'dd-mon-yyyy hh24:mi:ss')
3515 );
3516 END IF;
3517
3518 -- Define the initial batch range (line numbers are indexed from 1)
3519 l_batch_start := p_start_disp_line_number;
3520
3521 IF (l_max_line_number <l_batch_size) THEN
3522 l_batch_end := l_max_line_number;
3523 ELSE
3524 -- The batch end has to take into account the batch start too
3525 --So we need to translate the batch end based on batch start
3526 l_batch_end := l_batch_start + l_batch_size - 1;
3527 END IF;
3528
3529 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3530 FND_LOG.string(log_level => FND_LOG.level_statement,
3531 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3532 message => 'Finished setting the batching loop limits; l_batch_start : '||l_batch_start
3533 ||'; l_batch_end : ' || l_batch_end
3534 );
3535 END IF;
3536
3537 WHILE (l_batch_start <= l_max_line_number)
3538 LOOP
3539
3540 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3541 FND_LOG.string(log_level => FND_LOG.level_statement,
3542 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3543 message => 'Processing the batch from l_batch_start : ' || l_batch_start
3544 ||' to l_batch_end : ' || l_batch_end || ' ; bulk collecting the records now'
3545 );
3546 END IF;
3547
3548 select line_number, close_bidding_date, group_type
3549 bulk collect into
3550 l_line_number, l_close_date, l_group_type
3551 from pon_auction_item_prices_all
3552 WHERE auction_header_id = p_auction_header_id
3553 AND disp_line_number >= l_batch_start
3554 AND disp_line_number <= l_batch_end
3555 order by disp_line_number;
3556
3557 IF l_line_number.COUNT <> 0 THEN
3558
3559 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3560 FND_LOG.string(log_level => FND_LOG.level_statement,
3561 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3562 message => 'setting up the close dates array'
3563 );
3564 END IF;
3565
3566 FOR x IN 1..l_line_number.COUNT
3567 LOOP
3568 IF l_group_type(x) IN ('LINE','LOT','GROUP') THEN
3569 l_curr_close_date := l_curr_close_date + l_stag_interval;
3570 END IF;
3571 l_close_date(x) := l_curr_close_date;
3572 END LOOP;
3573
3574 x_last_line_close_date := l_close_date(l_line_number.COUNT);
3575
3576 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3577 FND_LOG.string(log_level => FND_LOG.level_statement,
3578 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3579 message => 'Last close date for this batch is x_last_line_close_date : ' || to_char (x_last_line_close_date, 'dd-mon-yyyy hh24:mi:ss')
3580 ||'; now bulk updating the PON_AUCTION_ITEM_PRICES_ALL'
3581 );
3582 END IF;
3583
3584 FORALL x IN 1..l_line_number.COUNT
3585 UPDATE PON_AUCTION_ITEM_PRICES_ALL
3586 set close_bidding_date = l_close_date(x)
3587 WHERE auction_header_id = p_auction_header_id
3588 AND line_number = l_line_number(x);
3589
3590 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3591 FND_LOG.string(log_level => FND_LOG.level_statement,
3592 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3593 message => 'Committing the batch now'
3594 );
3595 END IF;
3596
3597 COMMIT;
3598
3599 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3600 FND_LOG.string(log_level => FND_LOG.level_statement,
3601 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3602 message => 'Updating the batch limits for next iteration'
3603 );
3604 END IF;
3605
3606 l_batch_start := l_batch_end + 1;
3607
3608 IF (l_batch_end + l_batch_size > l_max_line_number) THEN
3609 l_batch_end := l_max_line_number;
3610 ELSE
3611 l_batch_end := l_batch_end + l_batch_size;
3612 END IF;
3613
3614 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3615 FND_LOG.string(log_level => FND_LOG.level_statement,
3616 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3617 message => 'New limits are l_batch_start : ' || l_batch_start
3618 || '; l_batch_end : ' || l_batch_end
3619 );
3620 END IF;
3621
3622 END IF;
3623
3624 END LOOP;
3625
3626 ELSE
3627 x_last_line_close_date := p_first_line_close_date;
3628 END IF; --}
3629 x_result := FND_API.G_RET_STS_SUCCESS;
3630
3631 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3632 FND_LOG.string(log_level => FND_LOG.level_statement,
3633 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3634 message => 'Exitting the method with '
3635 || 'x_last_line_close_date : '||x_last_line_close_date
3636 || '; x_result : '||x_result
3637 || '; x_error_code : '||x_error_code
3638 || '; x_error_message : '||x_error_message
3639 );
3640 END IF;
3641 END;
3642
3643
3644 /*======================================================================
3645 * FUNCTION : COUNT_LINES_LOTS_GROUPS PUBLIC
3646 * PARAMETERS:
3647 * p_auction_header_id IN header id of the auction
3648 *
3649 * COMMENT : returns the count of LINES, LOTS and GROUPS in the
3650 * negotiation
3651 *======================================================================*/
3652
3653 FUNCTION COUNT_LINES_LOTS_GROUPS (p_auction_header_id IN NUMBER) RETURN NUMBER
3654 IS
3655 l_lines_lots_groups_count NUMBER := -1;
3656 BEGIN
3657
3658 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3659 FND_LOG.string(log_level => FND_LOG.level_statement,
3660 module => g_module_prefix || 'COUNT_LINES_LOTS_GROUPS',
3661 message => 'Entered the procedure COUNT_LINES_LOTS_GROUPS; p_auction_header_id : ' || p_auction_header_id );
3662 END IF;
3663
3664
3665 SELECT Count(1) into l_lines_lots_groups_count
3666 FROM pon_auction_item_prices_all
3667 WHERE group_type IN ('LINE', 'LOT', 'GROUP')
3668 AND auction_header_id = p_auction_header_id;
3669
3670 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3671 FND_LOG.string(log_level => FND_LOG.level_statement,
3672 module => g_module_prefix || 'GET_SEARCH_MIN_DISP_LINE_NUM',
3673 message => 'Exitting the procedure COUNT_LINES_LOTS_GROUPS; l_lines_lots_groups_count : ' || l_lines_lots_groups_count );
3674 END IF;
3675
3676 RETURN l_lines_lots_groups_count;
3677
3678 END;
3679
3680
3681 /*======================================================================
3682 * FUNCTION : GET_PO_AUTHORIZATION_STATUS PUBLIC
3683 * PARAMETERS:
3684 * p_document_id IN po header id
3685 * p_document_type IN the PO document type ('PO'/'PA')
3686 * p_document_subtype IN PO subdoctype id
3687 *
3688 * COMMENT : returns the authorization status of PO
3689 *
3690 *======================================================================*/
3691
3692 FUNCTION GET_PO_AUTHORIZATION_STATUS (
3693 p_document_id IN VARCHAR2 ,
3694 p_document_type IN VARCHAR2 ,
3695 p_document_subtype IN VARCHAR2
3696 ) RETURN VARCHAR2
3697 IS
3698 v_return_status VARCHAR2(1);
3699 v_po_auth_status PO_LOOKUP_CODES.displayed_field%TYPE;
3700 BEGIN
3701
3702 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3703 FND_LOG.string(log_level => FND_LOG.level_statement,
3704 module => g_module_prefix || 'GET_PO_AUTHORIZATION_STATUS',
3705 message => 'Entered the procedure GET_PO_AUTHORIZATION_STATUS; p_document_id : ' || p_document_id
3706 || '; p_document_type : ' || p_document_type
3707 || '; p_document_subtype : ' || p_document_subtype);
3708 END IF;
3709
3710 -- The below if block is for safety and gracefule behaviour of the function
3711 -- The p_document_id cannot be null
3712 -- If the p_document_type is null, it means it's an RFI for whihc a PO cannot be created
3713 -- in this case we return null as the status of the PO
3714
3715 IF (p_document_id is null OR --This case cannot happen
3716 p_document_type is null -- this means it's an RFI
3717 ) THEN
3718 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3719 FND_LOG.string(log_level => FND_LOG.level_statement,
3720 module => g_module_prefix || 'GET_PO_AUTHORIZATION_STATUS',
3721 message => 'Returning null because either p_document_id or p_document_type is null');
3722 END IF;
3723 return null;
3724 END IF;
3725
3726 PO_CORE_S.get_document_status(
3727 p_document_id => p_document_id,
3728 p_document_type => p_document_type,
3729 p_document_subtype => p_document_subtype,
3730 x_return_status => v_return_status,
3731 x_document_status => v_po_auth_status
3732 );
3733
3734 IF ( v_return_status <> 'S' ) THEN
3735 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3736 FND_LOG.string(log_level => FND_LOG.level_statement,
3737 module => g_module_prefix || 'GET_PO_AUTHORIZATION_STATUS',
3738 message => 'Returning null because either x_return_status : ' || v_return_status);
3739 END IF;
3740 return null;
3741 END IF;
3742
3743 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3744 FND_LOG.string(log_level => FND_LOG.level_statement,
3745 module => g_module_prefix || 'GET_PO_AUTHORIZATION_STATUS',
3746 message => 'Exitting the procedure GET_PO_AUTHORIZATION_STATUS; v_return_status : ' || v_return_status
3747 || '; v_po_auth_status : ' || v_po_auth_status);
3748 END IF;
3749
3750 RETURN v_po_auth_status;
3751
3752 END GET_PO_AUTHORIZATION_STATUS;
3753
3754 /*======================================================================
3755 * PROCEDURE : HAS_PRICE_TIERS
3756 * PARAMETERS: 1. x_result - return status.
3757 * 2. x_error_code - error code
3758 * 3. x_error_message - The actual error message
3759 * 4. p_auction_header_id - The auction header id
3760 * 5. x_has_price_tiers - flag to indicate if negotiation has price tiers or not
3761 * COMMENT : It takes auction header id as the in parameter and returns Y if there is a line with price
3762 * tier, for this auction,. If there is no such line it returns N.
3763 *======================================================================*/
3764
3765 PROCEDURE HAS_PRICE_TIERS (
3766 x_result OUT NOCOPY VARCHAR2,
3767 x_error_code OUT NOCOPY VARCHAR2,
3768 x_error_message OUT NOCOPY VARCHAR2,
3769 p_auction_header_id IN NUMBER,
3770 x_has_price_tiers OUT NOCOPY VARCHAR2
3771 ) IS
3772
3773 l_module_name VARCHAR2 (30);
3774
3775 BEGIN
3776
3777 l_module_name := 'has_price_tiers';
3778 x_result := FND_API.g_ret_sts_success;
3779 x_has_price_tiers := 'Y';
3780
3781 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3782 FND_LOG.string(log_level => FND_LOG.level_statement,
3783 module => g_module_prefix || l_module_name,
3784 message => 'Entered the procedure HAS_PRICE_TIERS; p_auction_header_id : ' || p_auction_header_id);
3785 END IF;
3786
3787 BEGIN
3788 --
3789 -- Check for the existence of a row in the shipments table, for
3790 -- the given auction header id
3791 --
3792
3793 SELECT
3794 'Y'
3795 INTO
3796 x_has_price_tiers
3797 FROM
3798 pon_auction_shipments_all
3799 WHERE
3800 auction_header_id = p_auction_header_id AND
3801 rownum = 1;
3802
3803 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3804 FND_LOG.string(log_level => FND_LOG.level_statement,
3805 module => g_module_prefix || l_module_name,
3806 message => 'x_has_price_tiers' || x_has_price_tiers);
3807 END IF;
3808
3809 EXCEPTION
3810 WHEN NO_DATA_FOUND THEN
3811
3812 --
3813 --There are no shipments for the auction, so set the return value to N
3814 --
3815
3816 x_has_price_tiers := 'N';
3817 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3818 FND_LOG.string(log_level => FND_LOG.level_statement,
3819 module => g_module_prefix || 'HAS_PRICE_TIERS',
3820 message => 'No shipments for auction; x_has_price_tiers' || x_has_price_tiers);
3821 END IF;
3822 END;
3823
3824 EXCEPTION
3825 WHEN OTHERS THEN
3826 --
3827 --If there are any other exceptions in the code, report them to the caller
3828 --
3829 x_result := FND_API.g_ret_sts_unexp_error;
3830 x_error_code := SQLCODE;
3831 x_error_message := SUBSTR(SQLERRM, 1, 100);
3832
3833 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3834 FND_LOG.string(log_level => FND_LOG.level_statement,
3835 module => g_module_prefix || 'HAS_PRICE_TIERS',
3836 message => 'EXCEPTION ; x_error_code' || x_error_code || ' and x_error_message : ' || x_error_message);
3837 END IF;
3838
3839
3840 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3841 FND_LOG.string(log_level => FND_LOG.level_statement,
3842 module => g_module_prefix || l_module_name,
3843 message => 'Returning to the caller with x_has_price_tiers: '|| x_has_price_tiers );
3844 END IF;
3845
3846 END has_price_tiers;
3847
3848
3849 /*======================================================================
3850 * PROCEDURE : HANDLE_CHANGE_PRICE_TIERS
3851 * PARAMETERS: 1. x_result - return status.
3852 * 2. x_error_code - error code
3853 * 3. x_error_message - The actual error message
3854 * 4. p_auction_header_id - The auction header id
3855 * 5. p_delete_price_tiers -- Flag to indicate if price tiers to be removed or not
3856 * COMMENT : This methods deletes all the lines in the DB table PON_AUCTION_SHIPMENTS_ALL,
3857 * for the given auction header id, sets the modify falg for new round and amendments
3858 * and sets the default price break settings.
3859 *======================================================================*/
3860
3861 PROCEDURE HANDLE_CHANGE_PRICE_TIERS (
3862 x_result OUT NOCOPY VARCHAR2,
3863 x_error_code OUT NOCOPY VARCHAR2,
3864 x_error_message OUT NOCOPY VARCHAR2,
3865 p_auction_header_id IN NUMBER,
3866 p_delete_price_tiers IN VARCHAR2
3867 ) IS
3868
3869 l_module_name VARCHAR2 (30);
3870 l_max_line_number NUMBER;
3871 l_batch_size NUMBER;
3872 l_batch_start NUMBER;
3873 l_batch_end NUMBER;
3874 l_parent_auc_max_line_number NUMBER;
3875
3876 l_prev_price_tiers_indicator PON_AUCTION_HEADERS_ALL.PRICE_TIERS_INDICATOR%TYPE;
3877 l_amendment_number PON_AUCTION_HEADERS_ALL.AMENDMENT_NUMBER%TYPE;
3878 l_round_number PON_AUCTION_HEADERS_ALL.AUCTION_ROUND_NUMBER%TYPE;
3879
3880
3881 l_is_new_amendment BOOLEAN;
3882 l_is_amendment BOOLEAN;
3883 l_is_new_round BOOLEAN;
3884
3885 BEGIN
3886
3887 l_module_name := 'handle_change_price_tiers';
3888 x_result := FND_API.g_ret_sts_success;
3889
3890 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3891 FND_LOG.string(log_level => FND_LOG.level_statement,
3892 module => g_module_prefix || l_module_name,
3893 message => 'Entered the procedure ; p_auction_header_id : ' || p_auction_header_id || ' ; p_delete_price_tiers : '|| p_delete_price_tiers);
3894 END IF;
3895 --
3896 -- retrieve the price tier indicator, amendment number , new round number for the auction
3897 --
3898 SELECT price_tiers_indicator,
3899 amendment_number,
3900 auction_round_number
3901 INTO l_prev_price_tiers_indicator,
3902 l_amendment_number,
3903 l_round_number
3904 FROM pon_auction_headers_all
3905 WHERE auction_header_id = p_auction_header_id;
3906
3907 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3908 FND_LOG.string(log_level => FND_LOG.level_statement,
3909 module => g_module_prefix || l_module_name,
3910 message => 'l_prev_price_tiers_indicator : ' || l_prev_price_tiers_indicator);
3911 END IF;
3912
3913 --
3914 -- retrieve the maximum line number present for the auction
3915 --
3916 SELECT MAX(LINE_NUMBER)
3917 INTO l_max_line_number
3918 FROM PON_AUCTION_ITEM_PRICES_ALL
3919 WHERE AUCTION_HEADER_ID=p_auction_header_id;
3920
3921 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3922 FND_LOG.string(log_level => FND_LOG.level_statement,
3923 module => g_module_prefix || l_module_name,
3924 message => 'l_max_line_number : ' || l_max_line_number || '; l_amendment_number : ' || l_amendment_number
3925 || ' ; l_round_number : ' || l_round_number);
3926 END IF;
3927
3928
3929 IF (p_delete_price_tiers = 'Y') THEN--{
3930
3931 --
3932 --Check if the auction is an amendment or new round.
3933 --If yes, fetch the max line number of the previous round.
3934 --
3935 IF (l_amendment_number > 0) THEN
3936
3937 --this is an amendment
3938 l_is_amendment := true;
3939 SELECT max_internal_line_num
3940 INTO l_parent_auc_max_line_number
3941 FROM pon_auction_headers_all
3942 WHERE auction_header_id =
3943 (SELECT auction_header_id_prev_amend
3944 FROM pon_auction_headers_all
3945 WHERE auction_header_id = p_auction_header_id);
3946
3947 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3948 FND_LOG.string(log_level => FND_LOG.level_statement,
3949 module => g_module_prefix || l_module_name,
3950 message => 'Neg is Amendment ; l_parent_auc_max_line_number : ' || l_parent_auc_max_line_number);
3951 END IF;
3952
3953
3954 ELSIF (l_round_number > 1) THEN
3955 --this is an new round
3956 l_is_new_round := true;
3957 SELECT max_internal_line_num
3958 INTO l_parent_auc_max_line_number
3959 FROM pon_auction_headers_all
3960 WHERE auction_header_id =
3961 (SELECT auction_header_id_prev_round
3962 FROM pon_auction_headers_all
3963 WHERE auction_header_id = p_auction_header_id);
3964
3965 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3966 FND_LOG.string(log_level => FND_LOG.level_statement,
3967 module => g_module_prefix || l_module_name,
3968 message => 'This is new round ' || '; l_parent_auc_max_line_number : '|| l_parent_auc_max_line_number);
3969 END IF;
3970
3971 END IF;
3972 END IF; --}
3973
3974 -- Get the batch size
3975 l_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
3976
3977 -- Draft with no lines, or RFI,CPA with no lines we need to skip batching
3978 -- its build into the loop logic but just to be explicit about this condition
3979
3980 IF (l_max_line_number > 0) THEN --{
3981
3982 -- Define the initial batch range (line numbers are indexed from 1)
3983 l_batch_start := 1;
3984
3985 IF (l_max_line_number <= l_batch_size) THEN
3986 l_batch_end := l_max_line_number;
3987 ELSE
3988 l_batch_end := l_batch_size;
3989 END IF;
3990
3991 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3992 FND_LOG.string(log_level => FND_LOG.level_statement,
3993 module => g_module_prefix || l_module_name,
3994 message => 'l_batch_size : ' || l_batch_size || '; l_batch_end : '
3995 || l_batch_end);
3996 END IF;
3997
3998 WHILE (l_batch_start <= l_max_line_number) LOOP
3999
4000 --
4001 --default the price break settings if price tiers indicator has been changed from price breaks
4002 --
4003 IF (l_prev_price_tiers_indicator = 'PRICE_BREAKS') THEN --{
4004
4005 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4006 FND_LOG.string(log_level => FND_LOG.level_statement,
4007 module => g_module_prefix || l_module_name,
4008 message => 'Price tier indicator has chnaged from ' || l_prev_price_tiers_indicator ||' ; updating the pon_auction_item_prices_all, setting price_break_type to NONE and price_break_neg_flag to Y');
4009 END IF;
4010
4011 UPDATE pon_auction_item_prices_all
4012 SET price_break_type = 'NONE',
4013 price_break_neg_flag = 'Y'
4014 WHERE auction_header_id = p_auction_header_id
4015 AND line_number >= l_batch_start
4016 AND line_number <= l_batch_end;
4017
4018 END IF; --}
4019
4020 IF (p_delete_price_tiers = 'Y') THEN--{
4021 --
4022 -- Delete the entries from the shipments table for this auction
4023 --
4024 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4025 FND_LOG.string(log_level => FND_LOG.level_statement,
4026 module => g_module_prefix || l_module_name,
4027 message => 'Deleting the entries from the shipments table for negotiation ' || p_auction_header_id
4028 || ' and line_number between ' || l_batch_start ||' and ' || l_batch_end );
4029 END IF;
4030
4031 DELETE FROM
4032 pon_auction_shipments_all
4033 WHERE
4034 auction_header_id = p_auction_header_id AND
4035 line_number >= l_batch_start AND
4036 line_number <= l_batch_end;
4037
4038 --
4039 --Delete the child differentials for the above deleted shipments
4040 -- only if they are price breaks. We won't have differential children
4041 --for quantity based tiers
4042 --
4043
4044 IF (l_prev_price_tiers_indicator = 'PRICE_BREAKS') THEN
4045
4046 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4047 FND_LOG.string(log_level => FND_LOG.level_statement,
4048 module => g_module_prefix || l_module_name,
4049 message => 'Deleting the child differentials for negotiation ' || p_auction_header_id
4050 || ' and line_number between ' || l_batch_start ||' and ' || l_batch_end );
4051 END IF;
4052
4053 DELETE FROM
4054 pon_price_differentials
4055 WHERE
4056 auction_header_id = p_auction_header_id AND
4057 shipment_number > -1 AND
4058 line_number >= l_batch_start AND
4059 line_number <= l_batch_end;
4060
4061 END IF;
4062
4063 --
4064 --Check if the auction is a new round or an amendment. in that case,
4065 --we need to mark all the lines with price tiers (having rows in
4066 --PON_AUCTION_SHIPMENTS_ALL table) from the previous/parent auction
4067 --as modified and set the flag has_price_tiers as 'N'.
4068 --
4069 IF (l_is_new_round OR l_is_new_amendment) THEN
4070
4071 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4072 FND_LOG.string(log_level => FND_LOG.level_statement,
4073 module => g_module_prefix || l_module_name,
4074 message => 'Negotiation is a new round or an amendment. Updating flags modified_flag and has_quantity_tiers');
4075 END IF;
4076
4077 --
4078 --Only those lines which were present in previous round or previous
4079 --amendment we need to set the modified_flag.
4080 --
4081
4082 UPDATE pon_auction_item_prices_all
4083 SET has_quantity_tiers = 'N',
4084 has_shipments_flag = 'N',
4085 modified_flag = decode(least(line_number,l_parent_auc_max_line_number),
4086 line_number,'Y', modified_flag),
4087 modified_date = SYSDATE
4088 WHERE
4089 auction_header_id = p_auction_header_id AND
4090 (has_quantity_tiers = 'Y' OR has_shipments_flag = 'Y') AND
4091 line_number >= l_batch_start AND
4092 line_number <= l_batch_end;
4093
4094 ELSE
4095
4096 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4097 FND_LOG.string(log_level => FND_LOG.level_statement,
4098 module => g_module_prefix || l_module_name,
4099 message => 'Negotiation is a not new round or an amendment. Updating has_quantity_tiers flag');
4100 END IF;
4101
4102 UPDATE pon_auction_item_prices_all
4103 SET has_quantity_tiers = 'N',
4104 has_shipments_flag = 'N'
4105 WHERE
4106 auction_header_id = p_auction_header_id AND
4107 (has_quantity_tiers = 'Y' OR has_shipments_flag = 'Y') AND
4108 line_number >= l_batch_start AND
4109 line_number <= l_batch_end;
4110
4111 END IF; --new round or amendment
4112
4113 END IF; --} --p_delete_price_tiers = 'Y'
4114
4115 -- Find the new batch range
4116 l_batch_start := l_batch_end + 1;
4117 IF (l_batch_end + l_batch_size > l_max_line_number) THEN
4118 l_batch_end := l_max_line_number;
4119 ELSE
4120 l_batch_end := l_batch_end + l_batch_size;
4121 END IF;
4122
4123 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4124 FND_LOG.string(log_level => FND_LOG.level_statement,
4125 module => g_module_prefix || l_module_name,
4126 message => 'New Batch with l_batch_start' || l_batch_start || ' and l_batch_end : ' || l_batch_end);
4127 END IF;
4128
4129 -- Issue a commit to push in all changes
4130 COMMIT;
4131 END LOOP;
4132
4133 END IF; --}
4134
4135 EXCEPTION
4136 WHEN OTHERS THEN
4137 x_result := FND_API.g_ret_sts_unexp_error;
4138 x_error_code := SQLCODE;
4139 x_error_message := SUBSTR(SQLERRM, 1, 100);
4140
4141 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4142 FND_LOG.string(log_level => FND_LOG.level_statement,
4143 module => g_module_prefix || l_module_name,
4144 message => 'EXCEPTION ; x_error_code' || x_error_code || ' and x_error_message : ' || x_error_message);
4145 END IF;
4146
4147
4148 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4149 FND_LOG.string(log_level => FND_LOG.level_statement,
4150 module => g_module_prefix || l_module_name,
4151 message => 'Returning to the caller');
4152 END IF;
4153 END HANDLE_CHANGE_PRICE_TIERS;
4154
4155 --Bug 6074506
4156 /*======================================================================
4157 * FUNCTION : GET_ABBR_DOC_TYPE_GRP_NAME PUBLIC
4158 * PARAMETERS:
4159 * p_doctype_id IN document type id of the auction
4160 *
4161 * COMMENT : returns the document froup name in English language
4162 *
4163 *======================================================================*/
4164
4165 FUNCTION GET_ABBR_DOC_TYPE_GRP_NAME (p_doctype_id IN NUMBER) RETURN VARCHAR2
4166 IS
4167 v_doctype_name pon_auc_doctypes_tl.name%TYPE;
4168
4169 BEGIN
4170
4171 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4172 FND_LOG.string(log_level => FND_LOG.level_statement,
4173 module => g_module_prefix || 'GET_ABBR_DOC_TYPE_GRP_NAME',
4174 message => 'Entered the procedure GET_ABBR_DOC_TYPE_GRP_NAME; p_doctype_id : ' || p_doctype_id);
4175 END IF;
4176
4177 SELECT name
4178 INTO
4179 v_doctype_name
4180 FROM
4181 pon_auc_doctypes_tl
4182 WHERE
4183 doctype_id = p_doctype_id and
4184 language = 'US';
4185
4186 return v_doctype_name;
4187
4188 EXCEPTION WHEN NO_DATA_FOUND THEN
4189
4190 v_doctype_name := NULL;
4191
4192 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
4193 FND_LOG.string(log_level => FND_LOG.level_exception,
4194 module => g_module_prefix || 'GET_ABBR_DOC_TYPE_GRP_NAME',
4195 message => 'Exception in PON_NEGOTIATION_HELPER_PVT.GET_ABBR_DOC_TYPE_GRP_NAME '
4196 || 'errnum = ' || SQLCODE || ', errmsg = ' || SUBSTR (SQLERRM, 1, 200));
4197 END IF;
4198
4199 return v_doctype_name;
4200
4201 END GET_ABBR_DOC_TYPE_GRP_NAME;
4202
4203 END PON_NEGOTIATION_HELPER_PVT;
[Home] [Help]