1 PACKAGE BODY PON_NEGOTIATION_HELPER_PVT AS
2 /* $Header: PONNEGHB.pls 120.33.12020000.2 2013/02/09 05:51:00 hvutukur 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 -- Federal Fields project
1316 l_doctype_id PON_AUCTION_HEADERS_ALL.DOCTYPE_ID%TYPE;
1317
1318 BEGIN
1319
1320 l_module_name := 'delete_all_lines';
1321 x_result := FND_API.g_ret_sts_success;
1322
1323 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1324 FND_LOG.string (log_level => FND_LOG.level_procedure,
1325 module => g_module_prefix || l_module_name,
1326 message => 'Entered procedure = ' || l_module_name ||
1327 ' p_auction_header_id = ' || p_auction_header_id);
1328 END IF;
1329
1330 -- Collect auction information that is needed
1331 -- This information is being collected from the database instead
1332 -- of relying on the middle tier data as it is possible that the
1333 -- user has change the attribute but not yet saved it. Eg: User
1334 -- has changed from Price Only to MAS but not saved, so surely
1335 -- now there are no scores on the auction. The middle tier data
1336 -- now has MAS but the saved db data has Price Only which is
1337 -- what we look at.
1338 -- Reason we are having this outside the check l_max_line_number > 0
1339 -- is that we need the auction_origination_code before deleting
1340 -- references to requisitions
1341 SELECT
1342 paha.bid_ranking,
1343 paha.line_attribute_enabled_flag,
1344 pad.doctype_group_name,
1345 paha.rfi_line_enabled_flag,
1346 paha.pf_type_allowed,
1347 paha.contract_type,
1348 paha.global_agreement_flag,
1349 paha.large_neg_enabled_flag,
1350 paha.auction_origination_code,
1351 paha.progress_payment_type,
1352 paha.price_tiers_indicator,
1353 paha.doctype_Id -- Federal Fields Project
1354 INTO
1355 l_bid_ranking,
1356 l_line_attribute_enabled_flag,
1357 l_doctype_group_name,
1358 l_rfi_line_enabled_flag,
1359 l_pf_type_allowed,
1360 l_contract_type,
1361 l_global_agreement_flag,
1362 l_large_neg_enabled_flag,
1363 l_auction_origination_code,
1364 l_progress_payment_type,
1365 l_price_tiers_indicator,
1366 l_doctype_id
1367 FROM
1368 pon_auction_headers_all paha,
1369 pon_auc_doctypes pad
1370 WHERE
1371 paha.auction_header_id = p_auction_header_id AND
1372 paha.doctype_id = pad.doctype_id;
1373
1374 SELECT NVL (MAX (line_number), 0)
1375 INTO l_max_line_number
1376 FROM pon_auction_item_prices_all
1377 where auction_header_id = p_auction_header_id;
1378
1379 -- Get the batch size
1380 l_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
1381
1382 --Line type and structure changes project
1383 IF(pon_clm_util_pkg.IS_NEG_DOCUMENT_FEDERAL(p_auction_header_id) = 1) THEN
1384
1385 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1386 FND_LOG.string (log_level => FND_LOG.level_statement,
1387 module => g_module_prefix || l_module_name,
1388 message => 'Found that this auction is a federal auction. Call delete all lines APi for Federal docs');
1389 END IF;
1390 -- Delete pon_backing requisitions when Linked pr references are enabled.
1391 --Also update the sol references in req lines.
1392 po_negotiations_sv1.update_sol_ref_delete_all(p_auction_header_id => p_auction_header_id,
1393 x_return_status => x_result,
1394 x_error_msg => x_error_message,
1395 x_error_code => x_error_code);
1396
1397 -- Call the delete only if the origination code of the auction is
1398 -- REQUISITION
1399 ELSIF (l_auction_origination_code = 'REQUISITION') THEN
1400
1401 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1402 FND_LOG.string (log_level => FND_LOG.level_statement,
1403 module => g_module_prefix || l_module_name,
1404 message => 'Found that this auction is from a requisition');
1405 END IF;
1406
1407 --Delete Backing requisition references
1408 FOR backing_req_line IN lines_with_backing_requisition (p_auction_header_id) LOOP
1409
1410 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1411 FND_LOG.string (log_level => FND_LOG.level_statement,
1412 module => g_module_prefix || l_module_name,
1413 message => 'Deleting backing req references for line = ' || backing_req_line.line_number);
1414 END IF;
1415
1416 PON_AUCTION_PKG.delete_negotiation_line_ref(
1417 x_negotiation_id => p_auction_header_id,
1418 x_negotiation_line_num => backing_req_line.line_number,
1419 x_org_id => backing_req_line.org_id,
1420 x_error_code => x_error_code);
1421
1422 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1423 FND_LOG.string (log_level => FND_LOG.level_statement,
1424 module => g_module_prefix || l_module_name,
1425 message => 'Deletion of req reference done, x_error_code = ' || x_error_code);
1426 END IF;
1427
1428 IF (x_error_code <> 'SUCCESS') THEN
1429 x_result := FND_API.g_ret_sts_unexp_error;
1430 RETURN;
1431 END IF;
1432 END LOOP;
1433 END IF;
1434
1435 --Delete Attachments
1436 FOR attachment_line IN lines_with_attachements LOOP
1437
1438 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments (
1439 x_entity_name =>'PON_AUCTION_ITEM_PRICES_ALL',
1440 x_pk1_value => p_auction_header_id,
1441 x_pk2_value => attachment_line.line_number,
1442 x_pk3_value => NULL,
1443 x_pk4_value => NULL,
1444 x_pk5_value => NULL);
1445
1446 END LOOP;
1447
1448 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1449 FND_LOG.string (log_level => FND_LOG.level_statement,
1450 module => g_module_prefix || l_module_name,
1451 message => 'Attachments deletion complete');
1452 END IF;
1453
1454 -- Draft with no lines, or RFI,CPA with no lines we need to skip batching
1455 -- its build into the loop logic but just to be explicit about this condition
1456
1457 IF (l_max_line_number > 0) THEN
1458
1459 -- Define the initial batch range (line numbers are indexed from 1)
1460 l_batch_start := 1;
1461
1462 IF (l_max_line_number <l_batch_size) THEN
1463 l_batch_end := l_max_line_number;
1464 ELSE
1465 l_batch_end := l_batch_size;
1466 END IF;
1467
1468 WHILE (l_batch_start <= l_max_line_number) LOOP
1469
1470 IF ('STANDARD' = l_contract_type AND l_progress_payment_type <> 'NONE') THEN
1471 --complex work-delete fnd_attachments for payments
1472 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1473 FND_LOG.string (log_level => FND_LOG.level_procedure,
1474 module => g_module_prefix || l_module_name,
1475 message => 'Before call Delete_Payment_Attachments = ' || l_module_name);
1476 END IF;
1477
1478 Delete_Payment_Attachments(
1479 p_auction_header_id => p_auction_header_id,
1480 p_curr_from_line_number => l_batch_start,
1481 p_curr_to_line_number => l_batch_end);
1482
1483 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1484 FND_LOG.string (log_level => FND_LOG.level_statement,
1485 module => g_module_prefix || l_module_name,
1486 message => 'Payments Attachments deletion complete');
1487 END IF;
1488
1489 --complex work -delete payments
1490 DELETE FROM
1491 pon_auc_payments_shipments
1492 WHERE
1493 auction_header_id = p_auction_header_id AND
1494 line_number >= l_batch_start AND
1495 line_number <= l_batch_end;
1496
1497 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1498 FND_LOG.string (log_level => FND_LOG.level_statement,
1499 module => g_module_prefix || l_module_name,
1500 message => 'Payments deletion complete');
1501 END IF;
1502 END IF; --if complex work
1503 -- Call the delete scores only if negotiation has
1504 -- BID_RANKING as 'MULTI_ATTRIBUTE_SCORING'
1505 IF (l_bid_ranking = 'MULTI_ATTRIBUTE_SCORING') THEN
1506
1507 -- Delete the entries for attribute scores. To
1508 -- avoid deleting the attributes corresponding
1509 -- to the header the condition LINE_NUMBER <> -1
1510 -- is added
1511 -- Above condition is built into the batching
1512 -- condition as we start from 1
1513 DELETE FROM
1514 pon_attribute_scores
1515 WHERE
1516 auction_header_id = p_auction_header_id AND
1517 line_number >= l_batch_start AND
1518 line_number <= l_batch_end;
1519
1520 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1521 FND_LOG.string (log_level => FND_LOG.level_statement,
1522 module => g_module_prefix || l_module_name,
1523 message => 'Scores deletion complete');
1524 END IF;
1525
1526 END IF;
1527
1528 -- Call the delete attributes only if the negotiation has
1529 -- LINE_ATTRIBUTE_ENABLED_FLAG set to Y
1530 IF (NVL (l_line_attribute_enabled_flag, 'Y') = 'Y' AND
1531 (l_doctype_group_name <> PON_CONTERMS_UTL_PVT.SRC_REQUEST_FOR_INFORMATION OR
1532 NVL (l_rfi_line_enabled_flag, 'Y') = 'Y')) THEN
1533
1534 -- Delete the entries for attributes. To
1535 -- avoid deleting the attributes corresponding
1536 -- to the header the condition LINE_NUMBER <> -1
1537 -- is added
1538 -- Above condition is built into the batching
1539 -- condition as we start from 1
1540 DELETE FROM
1541 pon_auction_attributes
1542 WHERE
1543 auction_header_id = p_auction_header_id AND
1544 line_number >= l_batch_start AND
1545 line_number <= l_batch_end;
1546
1547 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1548 FND_LOG.string (log_level => FND_LOG.level_statement,
1549 module => g_module_prefix || l_module_name,
1550 message => 'Attributes deletion complete');
1551 END IF;
1552 END IF;
1553
1554 -- Call the delete price elements only if
1555 -- PF_TYPE_ALLOWED is set to other than NONE
1556 IF (l_pf_type_allowed <> 'NONE') THEN --{
1557
1558 -- Delete the price elements
1559 DELETE FROM
1560 pon_price_elements
1561 WHERE
1562 auction_header_id = p_auction_header_id AND
1563 line_number >= l_batch_start AND
1564 line_number <= l_batch_end;
1565
1566 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1567 FND_LOG.string (log_level => FND_LOG.level_statement,
1568 module => g_module_prefix || l_module_name,
1569 message => 'PF deletion complete');
1570 END IF;
1571
1572 -- Delete the supplier pf values
1573 DELETE FROM
1574 pon_pf_supplier_values
1575 WHERE
1576 auction_header_id = p_auction_header_id AND
1577 line_number >= l_batch_start AND
1578 line_number <= l_batch_end;
1579
1580 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1581 FND_LOG.string (log_level => FND_LOG.level_statement,
1582 module => g_module_prefix || l_module_name,
1583 message => 'PF Supplier values deletion complete');
1584 END IF;
1585
1586 END IF; --}
1587
1588 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1589 FND_LOG.string (log_level => FND_LOG.level_statement,
1590 module => g_module_prefix || l_module_name,
1591 message => 'price_tiers_indicator for auction ' || p_auction_header_id || ' is ' || l_price_tiers_indicator);
1592 END IF;
1593
1594 -- Call the delete shipments only if price tiers indicator is not 'NONE'
1595
1596 IF ( NVl(l_price_tiers_indicator, 'NONE') <> 'NONE') THEN
1597
1598 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1599 FND_LOG.string (log_level => FND_LOG.level_statement,
1600 module => g_module_prefix || l_module_name,
1601 message => 'Price tiers indicator is not none , so need to delete Price tiers.');
1602 END IF;
1603
1604 -- Delete the price breaks/shipments
1605 DELETE FROM
1606 pon_auction_shipments_all
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 => 'shipments deletion complete');
1616 END IF;
1617
1618 END IF;
1619
1620 -- Call the delete price differentials only if this is an RFI
1621 -- or this is a global agreement
1622 IF (l_doctype_group_name <> PON_CONTERMS_UTL_PVT.SRC_REQUEST_FOR_INFORMATION OR
1623 NVL (l_global_agreement_flag, 'Y') = 'Y') THEN
1624 -- Delete the price differentials
1625 DELETE FROM
1626 pon_price_differentials
1627 WHERE
1628 auction_header_id = p_auction_header_id AND
1629 line_number >= l_batch_start AND
1630 line_number <= l_batch_end;
1631
1632 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1633 FND_LOG.string (log_level => FND_LOG.level_statement,
1634 module => g_module_prefix || l_module_name,
1635 message => 'PD deletion complete');
1636 END IF;
1637 END IF;
1638
1639 -- Call party line exclusion deletion only if this is
1640 -- not a large negotiation
1641 IF (nvl (l_large_neg_enabled_flag, 'N') = 'N') THEN
1642
1643 -- Delete the party line exclusions
1644 DELETE FROM
1645 pon_party_line_exclusions
1646 WHERE
1647 auction_header_id = p_auction_header_id AND
1648 line_number >= l_batch_start AND
1649 line_number <= l_batch_end;
1650
1651 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1652 FND_LOG.string (log_level => FND_LOG.level_statement,
1653 module => g_module_prefix || l_module_name,
1654 message => 'party exclusions deletion complete');
1655 END IF;
1656
1657 END IF;
1658
1659 -- Federal Fields project : Delete line uda's
1660 IF PON_CLM_UTIL_PKG.IS_UDA_ENABLED(l_doctype_id) = 1 THEN
1661 DELETE FROM
1662 pon_auction_item_prices_ext_b
1663 WHERE
1664 auction_header_id = p_auction_header_id AND
1665 line_number >= l_batch_start AND
1666 line_number <= l_batch_end;
1667
1668 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1669 FND_LOG.string (log_level => FND_LOG.level_statement,
1670 module => g_module_prefix || l_module_name,
1671 message => 'UDA deletion complete');
1672 END IF;
1673 END IF;
1674
1675 /* Begin Supplier Management: Mapping */
1676 IF (FND_PROFILE.VALUE('POS_SM_ENABLE_SPM_EXTENSION') = 'Y') THEN
1677 DELETE FROM pon_auction_attr_mapping_b
1678 WHERE auction_header_id = p_auction_header_id
1679 AND line_number >= l_batch_start
1680 AND line_number <= l_batch_end
1681 AND mapping_type IN ('ITEM_LINE', 'CAT_LINE');
1682 END IF;
1683 /* End Supplier Management: Mapping */
1684 -- Delete the entries for lines
1685 DELETE FROM
1686 pon_auction_item_prices_all
1687 WHERE
1688 auction_header_id = p_auction_header_id AND
1689 line_number >= l_batch_start AND
1690 line_number <= l_batch_end;
1691
1692 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1693 FND_LOG.string (log_level => FND_LOG.level_statement,
1694 module => g_module_prefix || l_module_name,
1695 message => 'Lines deletion complete');
1696 END IF;
1697
1698 -- Find the new batch range
1699 l_batch_start := l_batch_end + 1;
1700 IF (l_batch_end + l_batch_size > l_max_line_number) THEN
1701 l_batch_end := l_max_line_number;
1702 ELSE
1703 l_batch_end := l_batch_end + l_batch_size;
1704 END IF;
1705
1706 -- Issue a commit to push in all changes
1707 COMMIT;
1708 END LOOP;
1709
1710 END IF;
1711
1712 -- Call delete from pon_large_neg_pf_values only if this is
1713 -- a large negotiation
1714 IF (l_large_neg_enabled_flag = 'Y') THEN
1715
1716 -- Delete the large neg pf values
1717 DELETE FROM
1718 pon_large_neg_pf_values
1719 WHERE
1720 auction_header_id = p_auction_header_id;
1721
1722 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1723 FND_LOG.string (log_level => FND_LOG.level_statement,
1724 module => g_module_prefix || l_module_name,
1725 message => 'Large neg pf values deletion complete');
1726 END IF;
1727
1728 END IF;
1729
1730 IF(pon_clm_util_pkg.IS_NEG_DOCUMENT_FEDERAL(p_auction_header_id) = 1) THEN
1731
1732 -- Delete the large neg pf values
1733 DELETE FROM
1734 pon_auction_exhibit_details
1735 WHERE auction_header_id = p_auction_header_id
1736 AND IS_CDRL='N';
1737
1738 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1739 FND_LOG.string (log_level => FND_LOG.level_statement,
1740 module => g_module_prefix || l_module_name,
1741 message => 'Exhibit Details deletion complete');
1742 END IF;
1743
1744 END IF;
1745
1746 -- Call update on pon_bidding_parties only if this is
1747 -- not a large negotiation
1748 IF (nvl (l_large_neg_enabled_flag, 'N') = 'N') THEN
1749
1750 --Need to update pon_bidding_parties about the access_type
1751 --Any supplier who was restricted on the deleted lines
1752 --should now have access_type set to FULL
1753 UPDATE
1754 pon_bidding_parties
1755 SET
1756 access_type = 'FULL'
1757 WHERE
1758 auction_header_id = p_auction_header_id AND
1759 access_type = 'RESTRICTED' AND
1760 (trading_partner_id, vendor_site_id) NOT IN
1761 (SELECT trading_partner_id, vendor_site_id
1762 FROM pon_party_line_exclusions
1763 WHERE auction_header_id = p_auction_header_id);
1764
1765 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1766 FND_LOG.string (log_level => FND_LOG.level_statement,
1767 module => g_module_prefix || l_module_name,
1768 message => 'Updating bidding parties done');
1769 END IF;
1770
1771 END IF;
1772
1773 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1774 FND_LOG.string (log_level => FND_LOG.level_procedure,
1775 module => g_module_prefix || l_module_name,
1776 message => 'Leaving procedure = ' || l_module_name);
1777 END IF;
1778
1779 EXCEPTION
1780 WHEN OTHERS THEN
1781 x_result := FND_API.g_ret_sts_unexp_error;
1782 x_error_code := SQLCODE;
1783 x_error_message := SUBSTR(SQLERRM, 1, 100);
1784
1785 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
1786 FND_LOG.string (log_level => FND_LOG.level_exception,
1787 module => g_module_prefix || l_module_name,
1788 message => 'Unexpected exception occured error_code = ' ||
1789 x_error_code || ', error_message = ' || x_error_message);
1790 END IF;
1791 END delete_all_lines;
1792
1793 /*======================================================================
1794 PROCEDURE : delete_single_line
1795 PARAMETERS: 1. x_result - return status.
1796 2. x_error_code - error code
1797 3. x_error_message - The actual error message
1798 4. p_auction_header_id - The auction header id
1799 5. p_line_number - The line to be deleted
1800 6. p_group_type - The group type of the line to be
1801 deleted.
1802 7. p_origination_code - The origination code for this line
1803 8. p_org_id - The org id for this line
1804 9. p_parent_line_number - The parent line number for
1805 this line
1806 10. p_sub_line_sequence_number - The sub line sequence
1807 number for this line
1808 COMMENT : This procedure will delete the given line. If it is a lot
1809 or a group then all the lot line and group lines will
1810 also be deleted.
1811 ======================================================================*/
1812
1813 PROCEDURE delete_single_line (
1814 x_result OUT NOCOPY VARCHAR2, --1
1815 x_error_code OUT NOCOPY VARCHAR2, --2
1816 x_error_message OUT NOCOPY VARCHAR2, --3
1817 p_auction_header_id IN NUMBER, --4
1818 p_line_number IN NUMBER, --5
1819 p_group_type IN VARCHAR2, --6
1820 p_origination_code IN VARCHAR2, --7
1821 p_org_id IN NUMBER, --8
1822 p_parent_line_number IN NUMBER, --9
1823 p_sub_line_sequence_number IN NUMBER, --10
1824 x_number_of_lines_deleted IN OUT NOCOPY NUMBER --11
1825 ) IS
1826
1827 l_module_name VARCHAR2 (30);
1828
1829 l_header_max_document_line_num NUMBER;
1830 l_line_number NUMBER;
1831
1832 -- Auction Header Information
1833 l_bid_ranking PON_AUCTION_HEADERS_ALL.BID_RANKING%TYPE;
1834 l_line_attribute_enabled_flag PON_AUCTION_HEADERS_ALL.LINE_ATTRIBUTE_ENABLED_FLAG%TYPE;
1835 l_doctype_group_name PON_AUC_DOCTYPES.DOCTYPE_GROUP_NAME%TYPE;
1836 l_rfi_line_enabled_flag PON_AUCTION_HEADERS_ALL.RFI_LINE_ENABLED_FLAG%TYPE;
1837 l_pf_type_allowed PON_AUCTION_HEADERS_ALL.PF_TYPE_ALLOWED%TYPE;
1838 l_contract_type PON_AUCTION_HEADERS_ALL.CONTRACT_TYPE%TYPE;
1839 l_global_agreement_flag PON_AUCTION_HEADERS_ALL.GLOBAL_AGREEMENT_FLAG%TYPE;
1840 l_large_neg_enabled_flag PON_AUCTION_HEADERS_ALL.LARGE_NEG_ENABLED_FLAG%TYPE;
1841 l_auction_origination_code PON_AUCTION_HEADERS_ALL.AUCTION_ORIGINATION_CODE%TYPE;
1842 l_amendment_number PON_AUCTION_HEADERS_ALL.AMENDMENT_NUMBER%TYPE;
1843 l_auction_round_number PON_AUCTION_HEADERS_ALL.AUCTION_ROUND_NUMBER%TYPE;
1844 l_is_multi_round VARCHAR2(2);
1845 l_is_amendment VARCHAR2(2);
1846 l_progress_payment_type PON_AUCTION_HEADERS_ALL.PROGRESS_PAYMENT_TYPE%TYPE;
1847 l_price_tiers_indicator PON_AUCTION_HEADERS_ALL.PRICE_TIERS_INDICATOR%TYPE;
1848
1849 -- Federal Fields Project
1850 l_doctype_id PON_AUCTION_HEADERS_ALL.DOCTYPE_ID%TYPE;
1851 l_exhibit_number PON_AUCTION_ITEM_PRICES_ALL.EXHIBIT_NUMBER%TYPE;
1852 l_exhibits NUMBER;
1853 -- Cursor to find out lines that have attachments
1854 -- within a lot/group
1855 CURSOR lines_with_attachements IS
1856 SELECT
1857 DISTINCT (TO_NUMBER(fad.pk2_value)) line_number
1858 FROM
1859 fnd_attached_documents fad,
1860 pon_auction_item_prices_all paip
1861 WHERE
1862 fad.entity_name = 'PON_AUCTION_ITEM_PRICES_ALL' AND
1863 fad.pk1_value = TO_CHAR(p_auction_header_id) AND
1864 paip.auction_header_id = p_auction_header_id AND
1865 fad.pk2_value = paip.line_number AND
1866 (paip.line_number = p_line_number OR paip.parent_line_number = p_line_number);
1867
1868 -- Cursor to find out lines that have backing requisitions
1869 -- within a lot/group
1870 CURSOR lines_with_backing_requisition IS
1871 SELECT
1872 line_number, org_id
1873 FROM
1874 pon_auction_item_prices_all
1875 WHERE
1876 auction_header_id = p_auction_header_id AND
1877 (line_number = p_line_number OR parent_line_number = p_line_number) AND
1878 requisition_number IS NOT NULL;
1879
1880
1881 --cursor to delete payment attachemnts for whole lot or group
1882 CURSOR delete_payments_attachments IS
1883 SELECT
1884 DISTINCT (TO_NUMBER(fad.pk2_value)) line_number
1885 FROM
1886 fnd_attached_documents fad,
1887 pon_auction_item_prices_all paip
1888 WHERE
1889 fad.entity_name = 'PON_AUC_PAYMENTS_SHIPMENTS' AND
1890 fad.pk1_value = TO_CHAR(p_auction_header_id) AND
1891 paip.auction_header_id = p_auction_header_id AND
1892 fad.pk2_value = paip.line_number AND
1893 (paip.line_number = p_line_number OR paip.parent_line_number = p_line_number);
1894
1895 BEGIN
1896
1897 l_module_name := 'delete_single_line';
1898 x_result := FND_API.g_ret_sts_success;
1899
1900 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1901 FND_LOG.string (log_level => FND_LOG.level_procedure,
1902 module => g_module_prefix || l_module_name,
1903 message => 'Entered procedure = ' || l_module_name ||
1904 ', p_auction_header_id = ' || p_auction_header_id ||
1905 ', p_line_number = ' || p_line_number ||
1906 ', p_group_type = ' || p_group_type ||
1907 ', p_origination_code = ' || p_origination_code ||
1908 ', p_org_id = ' || p_org_id ||
1909 ', p_parent_line_number = ' || p_parent_line_number ||
1910 ', p_sub_line_sequence_number = ' || p_sub_line_sequence_number);
1911 END IF;
1912
1913 -- Collect auction information that is needed
1914 -- This information is being collected from the database instead
1915 -- of relying on the middle tier data as it is possible that the
1916 -- user has change the attribute but not yet saved it. Eg: User
1917 -- has changed from Price Only to MAS but not saved, so surely
1918 -- now there are no scores on the auction. The middle tier data
1919 -- now has MAS but the saved db data has Price Only which is
1920 -- what we look at.
1921 -- Reason we are having this outside the check l_max_line_number > 0
1922 -- is that we need the auction_origination_code before deleting
1923 -- references to requisitions
1924 SELECT
1925 paha.bid_ranking,
1926 paha.line_attribute_enabled_flag,
1927 pad.doctype_group_name,
1928 paha.rfi_line_enabled_flag,
1929 paha.pf_type_allowed,
1930 paha.contract_type,
1931 paha.global_agreement_flag,
1932 paha.large_neg_enabled_flag,
1933 paha.auction_origination_code,
1934 paha.amendment_number,
1935 paha.auction_round_number,
1936 paha.progress_payment_type,
1937 paha.price_tiers_indicator,
1938 paha.doctype_id
1939 INTO
1940 l_bid_ranking,
1941 l_line_attribute_enabled_flag,
1942 l_doctype_group_name,
1943 l_rfi_line_enabled_flag,
1944 l_pf_type_allowed,
1945 l_contract_type,
1946 l_global_agreement_flag,
1947 l_large_neg_enabled_flag,
1948 l_auction_origination_code,
1949 l_amendment_number,
1950 l_auction_round_number,
1951 l_progress_payment_type,
1952 l_price_tiers_indicator,
1953 l_doctype_id
1954 FROM
1955 pon_auction_headers_all paha,
1956 pon_auc_doctypes pad
1957 WHERE
1958 paha.auction_header_id = p_auction_header_id AND
1959 paha.doctype_id = pad.doctype_id;
1960
1961 -- If this is a line/lot_line or a group_line then need to simply
1962 -- remove the entries corresponding to this one line
1963 IF (p_group_type IN ('LINE', 'LOT_LINE', 'GROUP_LINE')) THEN -- {
1964
1965 SELECT
1966 max_document_line_num
1967 INTO
1968 l_header_max_document_line_num
1969 FROM
1970 pon_auction_headers_all
1971 WHERE
1972 auction_header_id = p_auction_header_id;
1973
1974 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1975 FND_LOG.string (log_level => FND_LOG.level_statement,
1976 module => g_module_prefix || l_module_name,
1977 message => 'The selected row is of type LINE/LOT_LINE/GROUP_LINE');
1978 END IF;
1979
1980 BEGIN
1981
1982 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1983 FND_LOG.string (log_level => FND_LOG.level_statement,
1984 module => g_module_prefix || l_module_name,
1985 message => 'Checking if the line still exists');
1986 END IF;
1987
1988 --Checking if the line still exists in the database
1989 --We are doing this because the user might have selected a LOT and its
1990 --LOT_LINE for deletion and the LOT_LINE has already been deleted as
1991 --part of the LOT deletion. Simply return with number of lines deleted
1992 --set to zero
1993
1994 SELECT
1995 line_number
1996 INTO
1997 l_line_number
1998 FROM
1999 pon_auction_item_prices_all
2000 WHERE
2001 auction_header_id = p_auction_header_id and
2002 line_number = p_line_number;
2003
2004 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2005 FND_LOG.string (log_level => FND_LOG.level_statement,
2006 module => g_module_prefix || l_module_name,
2007 message => 'Line exists');
2008 END IF;
2009 EXCEPTION
2010 WHEN NO_DATA_FOUND THEN
2011 x_number_of_lines_deleted := 0;
2012 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2013 FND_LOG.string (log_level => FND_LOG.level_statement,
2014 module => g_module_prefix || l_module_name,
2015 message => 'Line no longer exists');
2016 END IF;
2017 RETURN;
2018 END;
2019
2020 DELETE FROM
2021 pon_attribute_scores
2022 WHERE
2023 auction_header_id = p_auction_header_id AND
2024 line_number = p_line_number;
2025
2026 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2027 FND_LOG.string (log_level => FND_LOG.level_statement,
2028 module => g_module_prefix || l_module_name,
2029 message => 'Deleted the entry in pon_attribute_scores');
2030 END IF;
2031
2032 DELETE FROM
2033 pon_auction_attributes
2034 WHERE
2035 auction_header_id = p_auction_header_id and
2036 line_number = p_line_number;
2037
2038 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2039 FND_LOG.string (log_level => FND_LOG.level_statement,
2040 module => g_module_prefix || l_module_name,
2041 message => 'Entry in pon_auction_attributes deleted');
2042 END IF;
2043
2044 DELETE FROM
2045 pon_pf_supplier_values
2046 WHERE
2047 auction_header_id = p_auction_header_id AND
2048 line_number = p_line_number;
2049
2050 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2051 FND_LOG.string (log_level => FND_LOG.level_statement,
2052 module => g_module_prefix || l_module_name,
2053 message => 'Deleted the entry in pon_pf_supplier_values');
2054 END IF;
2055
2056 DELETE FROM
2057 pon_price_elements
2058 WHERE
2059 auction_header_id = p_auction_header_id AND
2060 line_number = p_line_number;
2061
2062 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2063 FND_LOG.string (log_level => FND_LOG.level_statement,
2064 module => g_module_prefix || l_module_name,
2065 message => 'Deleted the entry in pon_price_elements');
2066 END IF;
2067
2068 DELETE FROM
2069 pon_price_differentials
2070 WHERE
2071 auction_header_id = p_auction_header_id AND
2072 line_number = p_line_number;
2073
2074 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2075 FND_LOG.string (log_level => FND_LOG.level_statement,
2076 module => g_module_prefix || l_module_name,
2077 message => 'Deleted the entry in pon_price_differentials');
2078 END IF;
2079
2080 DELETE FROM
2081 pon_auction_shipments_all
2082 WHERE
2083 auction_header_id = p_auction_header_id AND
2084 line_number = p_line_number;
2085
2086 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2087 FND_LOG.string (log_level => FND_LOG.level_statement,
2088 module => g_module_prefix || l_module_name,
2089 message => 'Deleted the entry in pon_auction_shipments_all');
2090 END IF;
2091
2092 IF ( 'STANDARD' = l_contract_type AND 'NONE' <> l_progress_payment_type) --{
2093 THEN
2094
2095 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2096 FND_LOG.string (log_level => FND_LOG.level_statement,
2097 module => g_module_prefix || l_module_name,
2098 message => 'Delete attachments for pon_auc_payments_shipments');
2099 END IF;
2100
2101 --delete payment attachments
2102
2103 Delete_Payment_Attachments(
2104 p_auction_header_id => p_auction_header_id,
2105 p_curr_from_line_number => p_line_number,
2106 p_curr_to_line_number => p_line_number);
2107
2108
2109 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2110 FND_LOG.string (log_level => FND_LOG.level_statement,
2111 module => g_module_prefix || l_module_name,
2112 message => 'Deleted the attachments for pon_auc_payments_shipments');
2113 END IF;
2114
2115 DELETE FROM
2116 pon_auc_payments_shipments
2117 WHERE
2118 auction_header_id = p_auction_header_id AND
2119 line_number = p_line_number;
2120
2121 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2122 FND_LOG.string (log_level => FND_LOG.level_statement,
2123 module => g_module_prefix || l_module_name,
2124 message => 'Deleted the entry in pon_auc_payments_shipments');
2125 END IF;
2126 END IF;--if neg has payments }
2127
2128 DELETE FROM
2129 pon_party_line_exclusions
2130 WHERE
2131 auction_header_id = p_auction_header_id AND
2132 line_number = p_line_number;
2133
2134 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2135 FND_LOG.string (log_level => FND_LOG.level_statement,
2136 module => g_module_prefix || l_module_name,
2137 message => 'Deleted the entry in PON_PARTY_LINE_EXCLUSIONS');
2138 END IF;
2139
2140 -- Federal Fields project : Delete line uda's
2141 IF PON_CLM_UTIL_PKG.IS_UDA_ENABLED(l_doctype_id) = 1 THEN
2142 DELETE FROM
2143 pon_auction_item_prices_ext_b
2144 WHERE
2145 auction_header_id = p_auction_header_id AND
2146 line_number = p_line_number;
2147
2148 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2149 FND_LOG.string (log_level => FND_LOG.level_statement,
2150 module => g_module_prefix || l_module_name,
2151 message => 'UDA deletion complete');
2152 END IF;
2153 END IF;
2154
2155 /* Begin Supplier Management: Mapping */
2156 IF (FND_PROFILE.VALUE('POS_SM_ENABLE_SPM_EXTENSION') = 'Y') THEN
2157 DELETE FROM pon_auction_attr_mapping_b
2158 WHERE auction_header_id = p_auction_header_id
2159 AND line_number = p_line_number
2160 AND mapping_type IN ('ITEM_LINE', 'CAT_LINE');
2161 END IF;
2162 /* End Supplier Management: Mapping */
2163
2164 SELECT exhibit_number
2165 INTO l_exhibit_number
2166 FROM pon_auction_item_prices_all
2167 WHERE auction_header_id = p_auction_header_id AND
2168 line_number = p_line_number;
2169
2170 DELETE FROM
2171 pon_auction_item_prices_all
2172 WHERE
2173 auction_header_id = p_auction_header_id AND
2174 line_number = p_line_number;
2175
2176 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2177 FND_LOG.string (log_level => FND_LOG.level_statement,
2178 module => g_module_prefix || l_module_name,
2179 message => 'Deleted the entry in pon_auction_headers_all');
2180 END IF;
2181
2182 /*
2183 Deleting Exhibit Details whenever an ELIN is deleted.
2184 The API will handle whether Exhibit Details should be deleted or not.
2185 */
2186 IF(l_exhibit_number IS NOT NULL) THEN
2187 BEGIN
2188 PON_EXHIBITS_PKG.DELETE_EXHIBIT_DETAILS(p_auction_header_id,l_exhibit_number);
2189 END;
2190 END IF;
2191 /*
2192 Need to set Associated Line Column to null in Exhibit Details table
2193 whenever a line is deleted
2194 */
2195 IF(l_exhibit_number IS NULL) THEN
2196 BEGIN
2197 PON_EXHIBITS_PKG.UPDATE_EXHIBIT_DETAILS(p_auction_header_id,p_line_number);
2198 END;
2199 END IF;
2200 /*
2201 * The sync procedure should be called after the line deletion
2202 * It should also be called only for large negotiations
2203 **/
2204
2205 IF (l_large_neg_enabled_flag = 'Y') THEN
2206 sync_pf_values_item_prices (
2207 p_auction_header_id => p_auction_header_id,
2208 p_line_number => p_line_number,
2209 p_add_pf => 'N',
2210 p_del_pf => 'Y',
2211 x_result => x_result,
2212 x_error_code => x_error_code,
2213 x_error_message => x_error_message);
2214
2215 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2216 FND_LOG.string (log_level => FND_LOG.level_statement,
2217 module => g_module_prefix || l_module_name,
2218 message => 'Sync pf values procedure called. x_result = ' || x_result ||
2219 ', x_error_code = ' || x_error_code ||
2220 ', x_error_message = ' || x_error_message);
2221 END IF;
2222
2223 IF (x_result <> FND_API.g_ret_sts_success) THEN
2224 RETURN;
2225 END IF;
2226
2227 END IF;
2228
2229 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments (
2230 x_entity_name => 'PON_AUCTION_ITEM_PRICES_ALL',
2231 x_pk1_value => p_auction_header_id,
2232 x_pk2_value => p_line_number);
2233
2234 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2235 FND_LOG.string (log_level => FND_LOG.level_statement,
2236 module => g_module_prefix || l_module_name,
2237 message => 'Deleted the line attachments');
2238 END IF;
2239
2240 --Line type and structure changes project
2241 IF(pon_clm_util_pkg.IS_NEG_DOCUMENT_FEDERAL(p_auction_header_id) = 1) THEN
2242
2243 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2244 FND_LOG.string (log_level => FND_LOG.level_statement,
2245 module => g_module_prefix || l_module_name,
2246 message => 'Found that this auction is a federal auction. Call delete all lines APi for Federal docs');
2247 END IF;
2248
2249 -- Delete pon_backing requisitions when Linked pr references are enabled.
2250 --Also update the sol references in req lines.
2251 po_negotiations_sv1.update_sol_ref_delete_line(p_auction_header_id => p_auction_header_id,
2252 p_auction_line_number => p_line_number,
2253 x_return_status => x_result,
2254 x_error_msg => x_error_message,
2255 x_error_code => x_error_code);
2256
2257 ELSIF (p_origination_code = 'REQUISITION') THEN
2258 PON_AUCTION_PKG.delete_negotiation_line_ref(
2259 x_negotiation_id => p_auction_header_id,
2260 x_negotiation_line_num => p_line_number,
2261 x_org_id => p_org_id,
2262 x_error_code => x_error_code);
2263
2264 IF (x_error_code <> 'SUCCESS') THEN
2265 x_result := FND_API.g_ret_sts_unexp_error;
2266 RETURN;
2267 END IF;
2268 END IF;
2269
2270 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2271 FND_LOG.string (log_level => FND_LOG.level_statement,
2272 module => g_module_prefix || l_module_name,
2273 message => 'Deleted the line backing requisitions if any');
2274 END IF;
2275
2276 -- In case this is an amendment and we are deleting a LOT_LINE/GROUP_LINE
2277 -- then on the parent line need to set the MODIFIED_FLAG, MODIFIED_DATE and
2278 -- LAST_AMENDMENT_UPDATE if the line is coming from the previous ROUND
2279 IF (p_group_type IN ('LOT_LINE', 'GROUP_LINE') AND
2280 l_amendment_number > 0 AND
2281 p_sub_line_sequence_number <= l_header_max_document_line_num) THEN
2282 UPDATE
2283 pon_auction_item_prices_all
2284 SET
2285 modified_flag = 'Y',
2286 modified_date = sysdate,
2287 last_amendment_update = l_amendment_number
2288 WHERE
2289 auction_header_id = p_auction_header_id AND
2290 line_number = p_parent_line_number;
2291 END IF;
2292
2293 -- In case this is a multi round then on the parent line need to set the MODIFIED_FLAG, MODIFIED fields
2294 IF (p_group_type IN ('LOT_LINE', 'GROUP_LINE') AND
2295 l_auction_round_number > 1 AND
2296 p_sub_line_sequence_number <= l_header_max_document_line_num) THEN
2297 UPDATE
2298 PON_AUCTION_ITEM_PRICES_ALL
2299 SET
2300 MODIFIED_FLAG = 'Y',
2301 MODIFIED_DATE = SYSDATE
2302 WHERE
2303 AUCTION_HEADER_ID = p_auction_header_id AND
2304 LINE_NUMBER = p_parent_line_number;
2305 END IF;
2306
2307 --The number of lines deleted is only 1 in this case
2308 x_number_of_lines_deleted := 1;
2309
2310 ELSE --} { This is the case for LOT or GROUP
2311
2312 -- Keep track of how many lines are being deleted
2313 SELECT
2314 count(line_number)
2315 INTO
2316 x_number_of_lines_deleted
2317 FROM
2318 pon_auction_item_prices_all
2319 WHERE
2320 auction_header_id = p_auction_header_id AND
2321 (line_number = p_line_number OR parent_line_number = p_line_number);
2322
2323 -- Call deletion of attributes only if line_attribute_enabled_flag is Y
2324 IF (NVL (l_line_attribute_enabled_flag, 'Y') = 'Y') THEN --{
2325
2326 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2327 FND_LOG.string (log_level => FND_LOG.level_statement,
2328 module => g_module_prefix || l_module_name,
2329 message => 'line attributes are enabled. l_line_attribute_enabled_flag = ' || l_line_attribute_enabled_flag);
2330 END IF;
2331
2332 -- Call deletion of scores only if this is an MAS
2333 IF (l_bid_ranking = 'MULTI_ATTRIBUTE_SCORING') THEN --{
2334
2335 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2336 FND_LOG.string (log_level => FND_LOG.level_statement,
2337 module => g_module_prefix || l_module_name,
2338 message => 'This is an MAS auction so need to delete scores');
2339 END IF;
2340
2341 -- Delete the scores that belong to this lot/group
2342 -- and also its children
2343 DELETE FROM
2344 pon_attribute_scores pas
2345 WHERE
2346 pas.auction_header_id = p_auction_header_id AND
2347 (
2348 pas.line_number = p_line_number OR
2349 EXISTS
2350 (
2351 SELECT
2352 paip.line_number
2353 FROM
2354 pon_auction_item_prices_all paip
2355 WHERE
2356 paip.parent_line_number = p_line_number AND
2357 paip.auction_header_id = p_auction_header_id AND
2358 paip.line_number = pas.line_number
2359 )
2360 );
2361
2362 END IF; --}
2363
2364 -- Delete the attributes that belong to this lot/group
2365 -- and also its children
2366 DELETE FROM
2367 pon_auction_attributes paa
2368 WHERE
2369 paa.auction_header_id = p_auction_header_id AND
2370 (
2371 paa.line_number = p_line_number OR
2372 EXISTS
2373 (
2374 SELECT
2375 paip.line_number
2376 FROM
2377 pon_auction_item_prices_all paip
2378 WHERE
2379 paip.parent_line_number = p_line_number AND
2380 paip.auction_header_id = p_auction_header_id AND
2381 paip.line_number = paa.line_number
2382 )
2383 );
2384
2385 END IF; --}
2386
2387 -- Call the deletion of pf supplier values and price elements
2388 -- only if the pf_type_allowed is not NONE
2389
2390 IF (l_pf_type_allowed <> 'NONE') THEN --{
2391
2392 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2393 FND_LOG.string (log_level => FND_LOG.level_statement,
2394 module => g_module_prefix || l_module_name,
2395 message => 'There are price factors. l_pf_type_allowed = ' || l_pf_type_allowed);
2396 END IF;
2397
2398 -- Delete the pf supplier values that belong to this
2399 -- lot/group and also its children
2400 DELETE FROM
2401 pon_pf_supplier_values ppsv
2402 WHERE
2403 ppsv.auction_header_id = p_auction_header_id AND
2404 (
2405 ppsv.line_number = p_line_number OR
2406 EXISTS
2407 (
2408 SELECT
2409 paip.line_number
2410 FROM
2411 pon_auction_item_prices_all paip
2412 WHERE
2413 paip.parent_line_number = p_line_number AND
2414 paip.auction_header_id = p_auction_header_id AND
2415 paip.line_number = ppsv.line_number
2416 )
2417 );
2418
2419 -- Delete the cost factors that belong to this
2420 -- lot/group and also its children
2421 DELETE FROM
2422 pon_price_elements ppe
2423 WHERE
2424 ppe.auction_header_id = p_auction_header_id AND
2425 (
2426 ppe.line_number = p_line_number OR
2427 EXISTS
2428 (
2429 SELECT
2430 paip.line_number
2431 FROM
2432 pon_auction_item_prices_all paip
2433 WHERE
2434 paip.parent_line_number = p_line_number AND
2435 paip.auction_header_id = p_auction_header_id AND
2436 paip.line_number = ppe.line_number
2437 )
2438 );
2439
2440 END IF; --}
2441
2442 IF ( 'STANDARD' = l_contract_type AND 'NONE' <> l_progress_payment_type)
2443 THEN
2444
2445 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2446 FND_LOG.string (log_level => FND_LOG.level_statement,
2447 module => g_module_prefix || l_module_name,
2448 message => 'Delete attachments for pon_auc_payments_shipments');
2449 END IF;
2450
2451 --delete payment attachments
2452 FOR delete_attachments_rec IN delete_payments_attachments LOOP
2453 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
2454 FND_LOG.string (log_level => FND_LOG.level_procedure,
2455 module => g_module_prefix || l_module_name,
2456 message => 'Deleting fnd attachments for payments for line number ' ||delete_attachments_rec.line_number||'='|| l_module_name);
2457 END IF;
2458 --delete the attachments for a payment
2459 FND_ATTACHED_DOCUMENTS2_PKG.DELETE_ATTACHMENTS
2460 (x_entity_name => 'PON_AUC_PAYMENTS_SHIPMENTS',
2461 x_pk1_value => p_auction_header_id,
2462 x_pk2_value => delete_attachments_rec.line_number,
2463 x_delete_document_flag => 'Y');
2464 END LOOP;
2465
2466 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2467 FND_LOG.string (log_level => FND_LOG.level_statement,
2468 module => g_module_prefix || l_module_name,
2469 message => 'Deleted the attachments for pon_auc_payments_shipments');
2470 END IF;
2471
2472 DELETE FROM
2473 pon_auc_payments_shipments paps
2474 WHERE
2475 paps.auction_header_id = p_auction_header_id AND (
2476 paps.line_number = p_line_number OR
2477 EXISTS
2478 (
2479 SELECT
2480 paip.line_number
2481 FROM
2482 pon_auction_item_prices_all paip
2483 WHERE
2484 paip.parent_line_number = p_line_number AND
2485 paip.auction_header_id = p_auction_header_id AND
2486 paip.line_number = paps.line_number
2487 )
2488 );
2489
2490 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2491 FND_LOG.string (log_level => FND_LOG.level_statement,
2492 module => g_module_prefix || l_module_name,
2493 message => 'Deleted the entry in pon_auc_payments_shipments');
2494 END IF;
2495 END IF;--if neg has payments
2496
2497 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2498 FND_LOG.string (log_level => FND_LOG.level_statement,
2499 module => g_module_prefix || l_module_name,
2500 message => 'price_tiers_indicator for auction ' || p_auction_header_id || ' is ' || l_price_tiers_indicator);
2501 END IF;
2502
2503
2504 -- Call the delete shipments only if price tiers indicator is not 'NONE'
2505
2506 IF ( NVl(l_price_tiers_indicator, 'NONE') <> 'NONE') THEN --{
2507
2508 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2509 FND_LOG.string (log_level => FND_LOG.level_statement,
2510 module => g_module_prefix || l_module_name,
2511 message => 'Price tiers indicator is not none , so need to delete Price tiers.');
2512 END IF;
2513
2514 -- Delete the price breaks that belong to this
2515 -- lot/group and also its children
2516 DELETE FROM
2517 pon_auction_shipments_all pasa
2518 WHERE
2519 pasa.auction_header_id = p_auction_header_id AND
2520 (
2521 pasa.line_number = p_line_number OR
2522 EXISTS
2523 (
2524 SELECT
2525 paip.line_number
2526 FROM
2527 pon_auction_item_prices_all paip
2528 WHERE
2529 paip.parent_line_number = p_line_number AND
2530 paip.auction_header_id = p_auction_header_id AND
2531 paip.line_number = pasa.line_number
2532 )
2533 );
2534
2535 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN --{
2536 FND_LOG.string (log_level => FND_LOG.level_statement,
2537 module => g_module_prefix || l_module_name,
2538 message => 'shipments deletion complete');
2539 END IF; --}
2540
2541 END IF;--}
2542
2543 -- Call delete on price differentials only if this is a global agreement or
2544 -- this is an RFI
2545 IF (l_doctype_group_name = PON_CONTERMS_UTL_PVT.SRC_REQUEST_FOR_INFORMATION OR
2546 nvl (l_global_agreement_flag, 'Y') = 'Y') THEN --{
2547
2548 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2549 FND_LOG.string (log_level => FND_LOG.level_statement,
2550 module => g_module_prefix || l_module_name,
2551 message => 'This is an RFI or Global Agreement. Need to delete price diffs.');
2552 END IF;
2553
2554 -- Delete the price differentials that belong to this
2555 -- lot/group and also its children
2556 DELETE FROM
2557 pon_price_differentials ppd
2558 WHERE
2559 ppd.auction_header_id = p_auction_header_id AND
2560 (
2561 ppd.line_number = p_line_number OR
2562 EXISTS
2563 (
2564 SELECT
2565 paip.line_number
2566 FROM
2567 pon_auction_item_prices_all paip
2568 WHERE
2569 paip.parent_line_number = p_line_number AND
2570 paip.auction_header_id = p_auction_header_id AND
2571 paip.line_number = ppd.line_number
2572 )
2573 );
2574
2575 END IF; --}
2576
2577 -- Call delete on party line exclusions only if this is not a large negotiation
2578 IF (nvl (l_large_neg_enabled_flag, 'N') = 'N') THEN --{
2579 -- Delete the exclusions that belong to this
2580 -- lot/group and also its children
2581
2582 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2583 FND_LOG.string (log_level => FND_LOG.level_statement,
2584 module => g_module_prefix || l_module_name,
2585 message => 'This is not large so deleting the exclusion entries.');
2586 END IF;
2587
2588 DELETE FROM
2589 pon_party_line_exclusions pple
2590 WHERE
2591 pple.auction_header_id = p_auction_header_id AND
2592 (
2593 pple.line_number = p_line_number OR
2594 EXISTS
2595 (
2596 SELECT
2597 paip.line_number
2598 FROM
2599 pon_auction_item_prices_all paip
2600 WHERE
2601 paip.parent_line_number = p_line_number AND
2602 paip.auction_header_id = p_auction_header_id AND
2603 paip.line_number = pple.line_number
2604 )
2605 );
2606 END IF;
2607
2608 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2609 FND_LOG.string (log_level => FND_LOG.level_statement,
2610 module => g_module_prefix || l_module_name,
2611 message => 'Deleting attachments.');
2612 END IF;
2613
2614 --Delete Attachments
2615 FOR attachment_line IN lines_with_attachements LOOP
2616
2617 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments (
2618 x_entity_name =>'PON_AUCTION_ITEM_PRICES_ALL',
2619 x_pk1_value => p_auction_header_id,
2620 x_pk2_value => attachment_line.line_number,
2621 x_pk3_value => NULL,
2622 x_pk4_value => NULL,
2623 x_pk5_value => NULL);
2624
2625 END LOOP;
2626
2627 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2628 FND_LOG.string (log_level => FND_LOG.level_statement,
2629 module => g_module_prefix || l_module_name,
2630 message => 'Deleting backing requisition line references.');
2631 END IF;
2632
2633 -- Call delete references to backing reqs only if the auction origination
2634 -- code is REQUISITION
2635 IF (l_auction_origination_code = 'REQUISITION') THEN
2636
2637 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2638 FND_LOG.string (log_level => FND_LOG.level_statement,
2639 module => g_module_prefix || l_module_name,
2640 message => 'This auction comes from a requisition.');
2641 END IF;
2642
2643 -- Delete references to the backing req
2644 FOR backing_req_line IN lines_with_backing_requisition LOOP
2645
2646 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2647 FND_LOG.string (log_level => FND_LOG.level_statement,
2648 module => g_module_prefix || l_module_name,
2649 message => 'Deleting backing reqs for line_number = ' || backing_req_line.line_number);
2650 END IF;
2651
2652 PON_AUCTION_PKG.delete_negotiation_line_ref(
2653 x_negotiation_id => p_auction_header_id,
2654 x_negotiation_line_num => backing_req_line.line_number,
2655 x_org_id => backing_req_line.org_id,
2656 x_error_code => x_error_code);
2657
2658 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2659 FND_LOG.string (log_level => FND_LOG.level_statement,
2660 module => g_module_prefix || l_module_name,
2661 message => 'Done deleting reference. x_error_code = ' || x_error_code);
2662 END IF;
2663
2664 IF (x_error_code <> 'SUCCESS') THEN
2665 x_result := FND_API.g_ret_sts_unexp_error;
2666 RETURN;
2667 END IF;
2668
2669 END LOOP;
2670
2671 END IF;
2672
2673 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2674 FND_LOG.string (log_level => FND_LOG.level_statement,
2675 module => g_module_prefix || l_module_name,
2676 message => 'Deleting the lines');
2677 END IF;
2678
2679 /* Begin Supplier Management: Mapping */
2680 IF (FND_PROFILE.VALUE('POS_SM_ENABLE_SPM_EXTENSION') = 'Y') THEN
2681 DELETE FROM pon_auction_attr_mapping_b
2682 WHERE auction_header_id = p_auction_header_id
2683 AND (line_number = p_line_number OR
2684 ( line_number IN ( SELECT line_number
2685 FROM pon_auction_item_prices_all
2686 WHERE auction_header_id = p_auction_header_id
2687 AND parent_line_number = p_line_number) ) )
2688 AND mapping_type IN ('ITEM_LINE', 'CAT_LINE');
2689 END IF;
2690 /* End Supplier Management: Mapping */
2691 -- Finally delete the lines
2692 DELETE FROM
2693 pon_auction_item_prices_all
2694 WHERE
2695 auction_header_id = p_auction_header_id AND
2696 (line_number = p_line_number OR parent_line_number = p_line_number);
2697
2698 /*
2699 * The sync procedure should be called after the line deletion
2700 * It should also be called only for large negotiations
2701 **/
2702
2703 IF (l_large_neg_enabled_flag = 'Y') THEN
2704 sync_pf_values_item_prices (
2705 p_auction_header_id => p_auction_header_id,
2706 p_line_number => null,
2707 p_add_pf => 'N',
2708 p_del_pf => 'Y',
2709 x_result => x_result,
2710 x_error_code => x_error_code,
2711 x_error_message => x_error_message);
2712
2713 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2714 FND_LOG.string (log_level => FND_LOG.level_statement,
2715 module => g_module_prefix || l_module_name,
2716 message => 'Sync pf values procedure called. x_result = ' || x_result ||
2717 ', x_error_code = ' || x_error_code ||
2718 ', x_error_message = ' || x_error_message);
2719 END IF;
2720
2721 IF (x_result <> FND_API.g_ret_sts_success) THEN
2722 RETURN;
2723 END IF;
2724 END IF;
2725
2726 END IF; --}
2727
2728 -- Need to update pon_bidding_parties about the access_type
2729 -- Any supplier who was restricted on the deleted lines
2730 -- should now have access_type set to FULL
2731 UPDATE
2732 pon_bidding_parties
2733 SET
2734 access_type = 'FULL'
2735 WHERE
2736 auction_header_id = p_auction_header_id AND
2737 access_type = 'RESTRICTED' AND
2738 (trading_partner_id, vendor_site_id) NOT IN
2739 (SELECT distinct trading_partner_id, vendor_site_id
2740 FROM pon_party_line_exclusions
2741 WHERE auction_header_id = p_auction_header_id);
2742
2743 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
2744 FND_LOG.string (log_level => FND_LOG.level_procedure,
2745 module => g_module_prefix || l_module_name,
2746 message => 'Leaving procedure = ' || l_module_name);
2747 END IF;
2748
2749 EXCEPTION
2750 WHEN OTHERS THEN
2751 x_result := FND_API.g_ret_sts_unexp_error;
2752 x_error_code := SQLCODE;
2753 x_error_message := SUBSTR(SQLERRM, 1, 100);
2754
2755 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
2756 FND_LOG.string (log_level => FND_LOG.level_exception,
2757 module => g_module_prefix || l_module_name,
2758 message => 'Unexpected exception occured error_code = ' ||
2759 x_error_code || ', error_message = ' || x_error_message);
2760 END IF;
2761
2762 END delete_single_line;
2763
2764 /*======================================================================
2765 PROCEDURE : RENUMBER_LINES
2766 PARAMETERS: 1. p_auction_header_id - The auction header id
2767 2. p_min_disp_line_number_parent - The disp line number
2768 of the minimum LINE/GROUP/LOT from where to correct
2769 the sequences
2770 3. p_min_disp_line_number_child - The disp line number of
2771 the minimum LOT_LINE/GROUP_LINE from where to correct
2772 the sequences.
2773 4. p_min_child_parent_line_num - The parent line number
2774 of the line given in step 3.
2775 5. x_last_line_number - The sub_line_sequence of the last
2776 row that is a lot/line/group.
2777 COMMENT : This procedure will correct the sequence numbers -
2778 SUB_LINE_SEQUENCE_NUMBER, DISP_LINE_NUMBER and
2779 DOCUMENT_DISP_LINE_NUMBER
2780 ======================================================================*/
2781
2782 PROCEDURE RENUMBER_LINES (
2783 x_result OUT NOCOPY VARCHAR2,
2784 x_error_code OUT NOCOPY VARCHAR2,
2785 x_error_message OUT NOCOPY VARCHAR2,
2786 p_auction_header_id IN NUMBER,
2787 p_min_disp_line_number_parent IN NUMBER,
2788 p_min_disp_line_number_child IN NUMBER,
2789 p_min_child_parent_line_num IN NUMBER,
2790 x_last_line_number OUT NOCOPY NUMBER
2791 ) IS
2792
2793 l_new_disp_line_number PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2794 l_sub_line_seq_number PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2795 l_document_disp_line_number PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
2796 l_line_number PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2797 l_parent_line_number PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2798 l_parent_doc_disp_line_number PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
2799 l_parent_max_sub_line_seq_num PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2800
2801 l_max_sub_line_sequence_number NUMBER;
2802 l_max_document_line_num NUMBER;
2803 l_current_parent_line_number NUMBER;
2804 l_current_max_sub_line_seq NUMBER;
2805 l_min_disp_line_number NUMBER;
2806
2807 l_login_id NUMBER;
2808 l_user_id NUMBER;
2809 l_temp NUMBER;
2810 l_module_name VARCHAR2 (30);
2811
2812 l_temp_char VARCHAR2(100);
2813 BEGIN
2814
2815 l_module_name := 'renumber_lines';
2816 x_result := FND_API.g_ret_sts_success;
2817 x_last_line_number := -1;
2818
2819 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
2820
2821 FND_LOG.string(log_level => FND_LOG.level_procedure,
2822 module => g_module_prefix || 'RENUMBER_LINES',
2823 message => 'Entering PON_NEGOTIATION_HELPER_PVT.RENUMBER_LINES'
2824 || ', p_auction_header_id = ' || p_auction_header_id
2825 || ', p_min_disp_line_number_parent = ' || p_min_disp_line_number_parent
2826 || ', p_min_disp_line_number_child = ' || p_min_disp_line_number_child
2827 || ', p_min_child_parent_line_num = ' || p_min_child_parent_line_num);
2828 END IF;
2829
2830 --START: CORRECT_SUB_LINE_SEQUENCE_NUMBER {
2831 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2832
2833 FND_LOG.string(log_level => FND_LOG.level_statement,
2834 module => g_module_prefix || 'RENUMBER_LINES',
2835 message => 'Selecting the maximum sub_line_sequence_number from the header');
2836 END IF;
2837
2838 --GET THE MAX_DOCUMENT_LINE_NUM (This is the maximum sub_line_sequence_number
2839 --from the previous neg) FROM THE HEADER
2840 SELECT
2841 NVL(MAX_DOCUMENT_LINE_NUM,0)
2842 INTO
2843 l_max_document_line_num
2844 FROM
2845 PON_AUCTION_HEADERS_ALL
2846 WHERE
2847 AUCTION_HEADER_ID = p_auction_header_id;
2848
2849 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2850
2851 FND_LOG.string(log_level => FND_LOG.level_statement,
2852 module => g_module_prefix || 'RENUMBER_LINES',
2853 message => 'The maximum sub_line_sequence_number from the header = ' || l_max_document_line_num);
2854 END IF;
2855
2856 --START: CORRECT FOR LINES, LOTS, GROUPS
2857 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2858
2859 FND_LOG.string(log_level => FND_LOG.level_statement,
2860 module => g_module_prefix || 'RENUMBER_LINES',
2861 message => 'Retrieving the line_numbers into a table of numbers');
2862 END IF;
2863
2864 --GET THE LINES, LOTS AND GROUPS FIRST
2865 SELECT
2866 LINE_NUMBER
2867 BULK COLLECT INTO
2868 l_line_number
2869 FROM
2870 PON_AUCTION_ITEM_PRICES_ALL
2871 WHERE
2872 AUCTION_HEADER_ID = p_auction_header_id AND
2873 GROUP_TYPE IN ('LOT', 'GROUP', 'LINE') AND
2874 SUB_LINE_SEQUENCE_NUMBER > l_max_document_line_num AND
2875 DISP_LINE_NUMBER > p_min_disp_line_number_parent
2876 ORDER BY
2877 DISP_LINE_NUMBER;
2878
2879 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2880
2881 FND_LOG.string(log_level => FND_LOG.level_statement,
2882 module => g_module_prefix || 'RENUMBER_LINES',
2883 message => 'Number of lines collected = ' || l_line_number.COUNT);
2884 END IF;
2885
2886 l_login_id := FND_GLOBAL.LOGIN_ID;
2887 l_user_id := FND_GLOBAL.USER_ID;
2888
2889 --CHECK IF ANY LINES EXIST AFTER THE MIN DISP_LINE_NUMBER
2890 --IF NOT THEN NO NEED TO RENUMBER ANY PARENT TYPE LINES
2891 IF (l_line_number.COUNT > 0) THEN --{
2892
2893 --GET THE GREATEST SUB_LINE_SEQUENCE_NUMBER WHOSE
2894 --DISP_LINE_NUMBER IS LESS THAN p_min_disp_line_number_parent
2895
2896 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2897
2898 FND_LOG.string(log_level => FND_LOG.level_statement,
2899 module => g_module_prefix || 'RENUMBER_LINES',
2900 message => 'Getting the maximum sub_line_sequence_number from the items table');
2901 END IF;
2902
2903 SELECT
2904 NVL (MAX (SUB_LINE_SEQUENCE_NUMBER), 0)
2905 INTO
2906 l_max_sub_line_sequence_number
2907 FROM
2908 PON_AUCTION_ITEM_PRICES_ALL
2909 WHERE
2910 AUCTION_HEADER_ID = p_auction_header_id AND
2911 DISP_LINE_NUMBER < p_min_disp_line_number_parent AND
2912 GROUP_TYPE IN ('LINE','LOT', 'GROUP');
2913
2914 --IN GENERAL THE l_max_sub_line_sequence_number WILL BE GREATER
2915 --SO CHECK FOR THE RARER CONDITION
2916
2917 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2918
2919 FND_LOG.string(log_level => FND_LOG.level_statement,
2920 module => g_module_prefix || 'RENUMBER_LINES',
2921 message => 'Checking where to start the sequencing');
2922 END IF;
2923
2924 IF (l_max_sub_line_sequence_number < l_max_document_line_num) THEN
2925
2926 l_max_sub_line_sequence_number := l_max_document_line_num;
2927 END IF;
2928
2929 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2930
2931 FND_LOG.string(log_level => FND_LOG.level_statement,
2932 module => g_module_prefix || 'RENUMBER_LINES',
2933 message => 'Sequencing will start at l_max_document_line_num = ' || l_max_document_line_num);
2934 END IF;
2935
2936 --CORRECT THE SUB_LINE_SEQUENCE_NUMBER (Same as DOCUMENT_DISP_LINE_NUMBER)
2937 FOR x IN 1..l_line_number.COUNT
2938 LOOP
2939
2940 l_max_sub_line_sequence_number := l_max_sub_line_sequence_number + 1;
2941 l_sub_line_seq_number (x) := l_max_sub_line_sequence_number;
2942 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2943
2944 FND_LOG.string(log_level => FND_LOG.level_statement,
2945 module => g_module_prefix || 'RENUMBER_LINES',
2946 message => 'Calculating the sub_line_sequence_number for line_number (' || l_line_number(x) || ' as ' || l_sub_line_seq_number (x));
2947 END IF;
2948 END LOOP;
2949
2950 --UPDATE THE LINES, LOTS AND GROUPS WITH THE NEW VALUES
2951 FORALL x in 1..l_line_number.COUNT
2952 UPDATE
2953 PON_AUCTION_ITEM_PRICES_ALL
2954 SET
2955 SUB_LINE_SEQUENCE_NUMBER = l_sub_line_seq_number (x),
2956 DOCUMENT_DISP_LINE_NUMBER = l_sub_line_seq_number (x),
2957 LAST_UPDATE_DATE = SYSDATE,
2958 LAST_UPDATE_LOGIN = l_login_id,
2959 LAST_UPDATED_BY = l_user_id
2960 WHERE
2961 AUCTION_HEADER_ID = p_auction_header_id AND
2962 LINE_NUMBER = l_line_number (x);
2963
2964 END IF; --}
2965
2966 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2967
2968 FND_LOG.string(log_level => FND_LOG.level_statement,
2969 module => g_module_prefix || 'RENUMBER_LINES',
2970 message => 'Starting correction of sub_line_sequence_number for lot_lines and group_lines');
2971 END IF;
2972
2973 --START: CORRECT THE SUB_LINE_SEQUENCE NUMBER FOR LOT_LINES AND GROUP_LINES
2974 --GET THE LOT LINES AND GROUP LINES
2975 SELECT
2976 CHILDREN.LINE_NUMBER,
2977 NVL (PARENT.MAX_SUB_LINE_SEQUENCE_NUMBER, 0),
2978 CHILDREN.PARENT_LINE_NUMBER,
2979 PARENT.DOCUMENT_DISP_LINE_NUMBER
2980 BULK COLLECT INTO
2981 l_line_number,
2982 l_parent_max_sub_line_seq_num,
2983 l_parent_line_number,
2984 l_parent_doc_disp_line_number
2985 FROM
2986 PON_AUCTION_ITEM_PRICES_ALL CHILDREN,
2987 PON_AUCTION_ITEM_PRICES_ALL PARENT
2988 WHERE
2989 CHILDREN.AUCTION_HEADER_ID = p_auction_header_id AND
2990 PARENT.AUCTION_HEADER_ID = p_auction_header_id AND
2991 PARENT.LINE_NUMBER = CHILDREN.PARENT_LINE_NUMBER AND
2992 CHILDREN.GROUP_TYPE IN ('LOT_LINE', 'GROUP_LINE') AND
2993 CHILDREN.SUB_LINE_SEQUENCE_NUMBER > NVL(PARENT.MAX_SUB_LINE_SEQUENCE_NUMBER,0) AND
2994 (CHILDREN.DISP_LINE_NUMBER > p_min_disp_line_number_child OR
2995 CHILDREN.DISP_LINE_NUMBER > p_min_disp_line_number_parent)
2996 ORDER BY
2997 CHILDREN.DISP_LINE_NUMBER;
2998
2999 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3000
3001 FND_LOG.string(log_level => FND_LOG.level_statement,
3002 module => g_module_prefix || 'RENUMBER_LINES',
3003 message => 'Number of lines to be corrected = ' || l_line_number.COUNT);
3004 END IF;
3005
3006 IF (l_line_number.COUNT > 0) THEN --{
3007
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 => 'p_min_disp_line_number_child = ' || p_min_disp_line_number_child ||
3012 ', p_min_disp_line_number_parent = ' || p_min_disp_line_number_parent);
3013 END IF;
3014
3015 IF (p_min_disp_line_number_child <> 0 AND
3016 p_min_disp_line_number_child < nvl (p_min_disp_line_number_parent, p_min_disp_line_number_child + 1)) THEN -- {
3017
3018 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3019 FND_LOG.string(log_level => FND_LOG.level_statement,
3020 module => g_module_prefix || 'RENUMBER_LINES',
3021 message => 'The min disp line number child is not zero');
3022 END IF;
3023
3024 SELECT
3025 NVL (MAX (SUB_LINE_SEQUENCE_NUMBER), 0)
3026 INTO
3027 l_current_max_sub_line_seq
3028 FROM
3029 PON_AUCTION_ITEM_PRICES_ALL
3030 WHERE
3031 AUCTION_HEADER_ID = p_auction_header_id AND
3032 PARENT_LINE_NUMBER = p_min_child_parent_line_num AND
3033 DISP_LINE_NUMBER < p_min_disp_line_number_child;
3034
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 => 'l_current_max_sub_line_seq = ' || l_current_max_sub_line_seq ||
3039 'l_parent_max_sub_line_seq_num(1) = ' || l_parent_max_sub_line_seq_num(1));
3040 END IF;
3041
3042 l_current_parent_line_number := p_min_child_parent_line_num;
3043
3044 IF (l_current_max_sub_line_seq < l_parent_max_sub_line_seq_num (1)) THEN
3045 l_current_max_sub_line_seq := l_parent_max_sub_line_seq_num (1);
3046 END IF;
3047
3048 ELSE
3049
3050 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3051 FND_LOG.string(log_level => FND_LOG.level_statement,
3052 module => g_module_prefix || 'RENUMBER_LINES',
3053 message => 'is zero l_current_max_sub_line_seq = ' || l_current_max_sub_line_seq ||
3054 'l_parent_max_sub_line_seq_num(1) = ' || l_parent_max_sub_line_seq_num(1));
3055 END IF;
3056
3057 l_current_parent_line_number := l_parent_line_number (1);
3058 l_current_max_sub_line_seq := l_parent_max_sub_line_seq_num (1);
3059 END IF; -- }
3060
3061 --CORRECT THE SUB_LINE_SEQUENCE_NUMBER AND DOCUMENT_DISP_LINE_NUMBER
3062 FOR x IN 1..l_line_number.COUNT
3063 LOOP
3064
3065 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3066 FND_LOG.string(log_level => FND_LOG.level_statement,
3067 module => g_module_prefix || 'RENUMBER_LINES',
3068 message => 'Determining the display for ' || l_line_number (x) ||
3069 ', l_current_parent_line_number = ' || l_current_parent_line_number ||
3070 ', l_parent_line_number (x) ' || l_parent_line_number(x));
3071 END IF;
3072
3073 --WITHIN THE SAME PARENT
3074 IF (l_current_parent_line_number = l_parent_line_number(x)) THEN
3075 l_current_max_sub_line_seq := l_current_max_sub_line_seq + 1;
3076
3077 --NEW PARENT
3078 ELSE
3079 l_current_max_sub_line_seq := l_parent_max_sub_line_seq_num (x) + 1;
3080 l_current_parent_line_number := l_parent_line_number(x);
3081
3082 END IF;
3083
3084 -- CORRECT THE SUB_LINE_SEQUENCE_NUMBER AND DOCUMENT_DISP_LINE_NUMBER
3085 l_sub_line_seq_number (x) := l_current_max_sub_line_seq;
3086 l_document_disp_line_number (x) := l_parent_doc_disp_line_number(x) || '.' || l_current_max_sub_line_seq;
3087
3088 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3089 FND_LOG.string(log_level => FND_LOG.level_statement,
3090 module => g_module_prefix || 'RENUMBER_LINES',
3091 message => 'Determined ' ||
3092 ', l_sub_line_seq_number(x) = ' || l_sub_line_seq_number (x)||
3093 ', l_document_disp_line_number(x) ' || l_document_disp_line_number(x));
3094 END IF;
3095 END LOOP;
3096
3097 --UPDATE THE LOT_LINES AND GROUP_LINES WITH THE NEW VALUES
3098 FORALL x in 1..l_line_number.COUNT
3099 UPDATE PON_AUCTION_ITEM_PRICES_ALL
3100 SET
3101 SUB_LINE_SEQUENCE_NUMBER = l_sub_line_seq_number (x),
3102 DOCUMENT_DISP_LINE_NUMBER = l_document_disp_line_number (x),
3103 LAST_UPDATE_DATE = SYSDATE,
3104 LAST_UPDATE_LOGIN = l_login_id,
3105 LAST_UPDATED_BY = l_user_id
3106 WHERE
3107 AUCTION_HEADER_ID = p_auction_header_id AND
3108 LINE_NUMBER = l_line_number (x);
3109 END IF; --}
3110
3111 --END: CORRECTING SUB_LINE_SEQUENCE_NUMBER --}
3112
3113 --START: CORRECT_DISP_LINE_NUMBER {
3114 --COLLECT THE LINE_NUMBER VALUES INTO AN
3115 --ARRAY ORDERED BY THE DISP_LINE_NUMBER
3116
3117 IF (p_min_disp_line_number_child IS NULL) THEN
3118 l_min_disp_line_number := p_min_disp_line_number_parent;
3119
3120 ELSIF (p_min_disp_line_number_parent IS NULL) THEN
3121 l_min_disp_line_number := p_min_disp_line_number_child;
3122
3123 ELSIF (p_min_disp_line_number_child < p_min_disp_line_number_parent) THEN
3124 l_min_disp_line_number := p_min_disp_line_number_child;
3125
3126 ELSE
3127 l_min_disp_line_number := p_min_disp_line_number_parent;
3128
3129 END IF;
3130
3131 SELECT
3132 LINE_NUMBER
3133 BULK COLLECT INTO
3134 l_line_number
3135 FROM
3136 PON_AUCTION_ITEM_PRICES_ALL
3137 WHERE
3138 AUCTION_HEADER_ID = p_auction_header_id AND
3139 DISP_LINE_NUMBER > l_min_disp_line_number
3140 ORDER BY
3141 DISP_LINE_NUMBER;
3142
3143 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3144 FND_LOG.string(log_level => FND_LOG.level_statement,
3145 module => g_module_prefix || 'RENUMBER_LINES',
3146 message => 'Obtained ' || l_line_number.COUNT || ' lines to correct');
3147 END IF;
3148
3149 --IF THERE ARE NO LINES BELOW THIS LINE
3150 --THEN NO RENUMBERING IS REQUIRED
3151 IF (l_line_number.COUNT = 0) THEN
3152 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3153 FND_LOG.string(log_level => FND_LOG.level_statement,
3154 module => g_module_prefix || 'RENUMBER_LINES',
3155 message => 'Returning without doing anything as there are no lines to renumber');
3156 END IF;
3157
3158 SELECT
3159 MAX(sub_line_sequence_number)
3160 INTO
3161 l_temp_char
3162 FROM
3163 pon_auction_item_prices_all
3164 WHERE
3165 auction_header_id = p_auction_header_id and
3166 group_type IN ('LOT', 'LINE', 'GROUP');
3167
3168 IF l_temp_char IS NULL THEN
3169 x_last_line_number := -1;
3170 ELSE
3171 x_last_line_number := to_number (l_temp_char);
3172 END IF;
3173
3174 RETURN;
3175 END IF;
3176
3177 --DETERMINE THE NEXT INTEGER TO START WITH
3178 l_temp := floor (l_min_disp_line_number);
3179 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3180 FND_LOG.string(log_level => FND_LOG.level_statement,
3181 module => g_module_prefix || 'RENUMBER_LINES',
3182 message => 'The new disp line number should start after ' || l_temp);
3183 END IF;
3184
3185 --DETERMINE THE NEW DISP_LINE_NUMBER
3186 FOR y IN 1..l_line_number.COUNT --{
3187 LOOP
3188
3189 l_new_disp_line_number (y) := y + l_temp;
3190
3191 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3192 FND_LOG.string(log_level => FND_LOG.level_statement,
3193 module => g_module_prefix || 'RENUMBER_LINES',
3194 message => 'Determined that ' || l_new_disp_line_number (y)
3195 || ' is the new disp_line_number of ' || l_line_number (y));
3196 END IF;
3197 END LOOP; --}
3198
3199 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3200 FND_LOG.string(log_level => FND_LOG.level_statement,
3201 module => g_module_prefix || 'RENUMBER_LINES',
3202 message => 'Calling bulk update to set the new disp_line_number');
3203 END IF;
3204
3205 --BULK UPDATE TO SET THE NEW DISP_LINE_NUMBER
3206 FORALL x IN 1..l_line_number.COUNT
3207 UPDATE PON_AUCTION_ITEM_PRICES_ALL
3208 SET DISP_LINE_NUMBER = l_new_disp_line_number (x)
3209 WHERE LINE_NUMBER = l_line_number(x)
3210 AND AUCTION_HEADER_ID = p_auction_header_id;
3211
3212 --}END: CORRECT_DISP_LINE_NUMBER
3213
3214 SELECT
3215 MAX(sub_line_sequence_number)
3216 INTO
3217 x_last_line_number
3218 FROM
3219 pon_auction_item_prices_all
3220 WHERE
3221 auction_header_id = p_auction_header_id and
3222 group_type IN ('LOT', 'LINE', 'GROUP');
3223
3224 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
3225 FND_LOG.string(log_level => FND_LOG.level_procedure,
3226 module => g_module_prefix || 'RENUMBER_LINES',
3227 message => 'Leaving PON_NEGOTIATION_HELPER_PVT.RENUMBER_LINES');
3228 END IF;
3229
3230 EXCEPTION
3231 WHEN OTHERS THEN
3232 x_result := FND_API.g_ret_sts_unexp_error;
3233 x_error_code := SQLCODE;
3234 x_error_message := SUBSTR(SQLERRM, 1, 100);
3235
3236 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
3237 FND_LOG.string (log_level => FND_LOG.level_exception,
3238 module => g_module_prefix || l_module_name,
3239 message => 'Unexpected exception occured error_code = ' ||
3240 x_error_code || ', error_message = ' || x_error_message);
3241 END IF;
3242
3243 END RENUMBER_LINES;
3244
3245
3246 /*======================================================================
3247 PROCEDURE : get_srch_min_disp_line_numbers
3248 PARAMETERS: 1. p_curr_auction_header_id - The current auction header id
3249 2. p_prev_auction_header_id - The previous auction header id
3250 3. p_value - The value entered by the user for search
3251 4. x_curr_min_disp_line_num - Out parameter to indicate at which
3252 line to start displaying for current auction
3253 5. x_prev_min_disp_line_num - Out parameter to indicate at which
3254 line to start displaying for previous auction
3255 COMMENT : This procedure is invoked when the user searches on the
3256 lines region with line number as the search criteria
3257 and greater than as the search condition.
3258 Given the value entered by the user (p_value) this
3259 procedure will return the disp_line_number above which
3260 all lines should be shown.
3261 ======================================================================*/
3262
3263 PROCEDURE get_srch_min_disp_line_numbers(
3264 p_curr_auction_header_id IN NUMBER,
3265 p_prev_auction_header_id IN NUMBER,
3266 p_value IN NUMBER,
3267 x_curr_min_disp_line_num OUT NOCOPY NUMBER,
3268 x_prev_min_disp_line_num OUT NOCOPY NUMBER
3269 ) IS
3270 BEGIN
3271
3272 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3273 FND_LOG.string(log_level => FND_LOG.level_statement,
3274 module => g_module_prefix || 'GET_SRCH_MIN_DISP_LINE_NUMBERS',
3275 message => 'Entering PON_NEGOTIATION_HELPER_PVT.GET_SRCH_MIN_DISP_LINE_NUMBERS'
3276 || ', p_curr_auction_header_id = ' || p_curr_auction_header_id
3277 || ', p_prev_auction_header_id = ' || p_prev_auction_header_id
3278 || ', p_value = ' || p_value);
3279 END IF;
3280
3281 --Retrieve the minimum disp_line_number of all the LOT/GROUP/LINES
3282 --that have SUB_LINE_SEQUENCE_NUMBER > p_value
3283
3284 SELECT MIN(disp_line_number)
3285 INTO x_curr_min_disp_line_num
3286 FROM PON_AUCTION_ITEM_PRICES_ALL
3287 WHERE AUCTION_HEADER_ID = p_curr_auction_header_id
3288 AND GROUP_TYPE IN ('LOT', 'GROUP', 'LINE')
3289 AND SUB_LINE_SEQUENCE_NUMBER > p_value;
3290
3291 SELECT MIN(disp_line_number)
3292 INTO x_prev_min_disp_line_num
3293 FROM PON_AUCTION_ITEM_PRICES_ALL
3294 WHERE AUCTION_HEADER_ID = p_prev_auction_header_id
3295 AND GROUP_TYPE IN ('LOT', 'GROUP', 'LINE')
3296 AND SUB_LINE_SEQUENCE_NUMBER > p_value;
3297
3298 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3299 FND_LOG.string(log_level => FND_LOG.level_statement,
3300 module => g_module_prefix || 'GET_SRCH_MIN_DISP_LINE_NUMBERS',
3301 message => 'Leaving PON_NEGOTIATION_HELPER_PVT.GET_SRCH_MIN_DISP_LINE_NUMBERS'
3302 || 'x_curr_min_disp_line_num = ' || x_curr_min_disp_line_num
3303 || 'x_prev_min_disp_line_num = ' || x_prev_min_disp_line_num
3304 );
3305 END IF;
3306
3307 END GET_SRCH_MIN_DISP_LINE_NUMBERS;
3308
3309 /*======================================================================
3310 PROCEDURE : get_srch_max_disp_line_numbers
3311 PARAMETERS: 1. p_curr_auction_header_id - The current auction header id
3312 2. p_prev_auction_header_id - The previous auction header id
3313 3. p_value - The value entered by the user for search
3314 4. x_curr_max_disp_line_num - Out parameter to indicate at which
3315 line to stop displaying
3316 5. x_prev_max_disp_line_num - Out parameter to indicate at which
3317 line to stop displaying
3318 COMMENT : This procedure is invoked when the user searches on the
3319 lines region with line number as the search criteria
3320 and less than as the search condition.
3321 Given the value entered by the user (p_value) this
3322 procedure will return the disp_line_number below which
3323 all lines should be shown.
3324 ======================================================================*/
3325
3326 PROCEDURE get_srch_max_disp_line_numbers (
3327 p_curr_auction_header_id IN NUMBER,
3328 p_prev_auction_header_id IN NUMBER,
3329 p_value IN NUMBER,
3330 x_curr_max_disp_line_num OUT NOCOPY NUMBER,
3331 x_prev_max_disp_line_num OUT NOCOPY NUMBER
3332 ) IS
3333
3334 l_curr_line_number PON_AUCTION_ITEM_PRICES_ALL.LINE_NUMBER%TYPE;
3335 l_curr_group_type PON_AUCTION_ITEM_PRICES_ALL.GROUP_TYPE%TYPE;
3336 l_prev_line_number PON_AUCTION_ITEM_PRICES_ALL.LINE_NUMBER%TYPE;
3337 l_prev_group_type PON_AUCTION_ITEM_PRICES_ALL.GROUP_TYPE%TYPE;
3338
3339 BEGIN
3340
3341 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3342 FND_LOG.string(log_level => FND_LOG.level_statement,
3343 module => g_module_prefix || 'GET_SRCH_MAX_DISP_LINE_NUMBERS',
3344 message => 'Entering PON_NEGOTIATION_HELPER_PVT.GET_SRCH_MAX_DISP_LINE_NUMBERS '
3345 || ', p_curr_auction_header_id = ' || p_curr_auction_header_id
3346 || ', p_prev_auction_header_id = ' || p_prev_auction_header_id
3347 || ', p_value = ' || p_value);
3348 END IF;
3349
3350 --Retrieve the maximum disp_line_number of all the LOT/GROUP/LINES
3351 --that have SUB_LINE_SEQUENCE_NUMBER < p_value
3352
3353 SELECT MAX(DISP_LINE_NUMBER)
3354 INTO x_curr_max_disp_line_num
3355 FROM PON_AUCTION_ITEM_PRICES_ALL
3356 WHERE AUCTION_HEADER_ID = p_curr_auction_header_id
3357 AND GROUP_TYPE IN ('LOT', 'LINE', 'GROUP')
3358 AND SUB_LINE_SEQUENCE_NUMBER < p_value;
3359
3360 SELECT MAX(DISP_LINE_NUMBER)
3361 INTO x_prev_max_disp_line_num
3362 FROM PON_AUCTION_ITEM_PRICES_ALL
3363 WHERE AUCTION_HEADER_ID = p_prev_auction_header_id
3364 AND GROUP_TYPE IN ('LOT', 'LINE', 'GROUP')
3365 AND SUB_LINE_SEQUENCE_NUMBER < p_value;
3366
3367 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3368 FND_LOG.string(log_level => FND_LOG.level_statement,
3369 module => g_module_prefix || 'GET_SRCH_MAX_DISP_LINE_NUMBERS',
3370 message => 'After the first query x_curr_max_disp_line_num = ' ||
3371 x_curr_max_disp_line_num || ' and x_prev_max_disp_line_num = ' || x_prev_max_disp_line_num);
3372 END IF;
3373
3374 IF (x_curr_max_disp_line_num IS NULL AND x_prev_max_disp_line_num IS NULL) THEN
3375
3376 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3377 FND_LOG.string(log_level => FND_LOG.level_statement,
3378 module => g_module_prefix || 'GET_SRCH_MAX_DISP_LINE_NUMBERS',
3379 message => 'There are no lines in both the auctions so returning null');
3380 END IF;
3381
3382 RETURN;
3383 END IF;
3384
3385 SELECT GROUP_TYPE, LINE_NUMBER
3386 INTO l_curr_group_type, l_curr_line_number
3387 FROM PON_AUCTION_ITEM_PRICES_ALL
3388 WHERE AUCTION_HEADER_ID = p_curr_auction_header_id
3389 AND DISP_LINE_NUMBER = x_curr_max_disp_line_num;
3390
3391 SELECT GROUP_TYPE, LINE_NUMBER
3392 INTO l_prev_group_type, l_prev_line_number
3393 FROM PON_AUCTION_ITEM_PRICES_ALL
3394 WHERE AUCTION_HEADER_ID = p_prev_auction_header_id
3395 AND DISP_LINE_NUMBER = x_prev_max_disp_line_num;
3396
3397 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3398 FND_LOG.string(log_level => FND_LOG.level_statement,
3399 module => g_module_prefix || 'GET_SEARCH_MAX_DISP_LINE_NUM',
3400 message => 'l_curr_group_type = ' || l_curr_group_type
3401 || ', l_curr_line_number = ' || l_curr_line_number
3402 ||'l_prev_group_type = ' || l_prev_group_type
3403 || ', l_prev_line_number = ' || l_prev_line_number
3404 );
3405 END IF;
3406
3407 --If the selected line is a LOT/GROUP then get the maximum
3408 --disp_line_number within that LOT/GROUP
3409
3410 IF (l_curr_group_type <> 'LINE') THEN
3411 SELECT NVL (MAX(DISP_LINE_NUMBER), x_curr_max_disp_line_num)
3412 INTO x_curr_max_disp_line_num
3413 FROM PON_AUCTION_ITEM_PRICES_ALL
3414 WHERE AUCTION_HEADER_ID = p_curr_auction_header_id
3415 AND PARENT_LINE_NUMBER = l_curr_line_number;
3416 END IF;
3417
3418 IF (l_prev_group_type <> 'LINE') THEN
3419 SELECT NVL (MAX(DISP_LINE_NUMBER), x_prev_max_disp_line_num)
3420 INTO x_prev_max_disp_line_num
3421 FROM PON_AUCTION_ITEM_PRICES_ALL
3422 WHERE AUCTION_HEADER_ID = p_prev_auction_header_id
3423 AND PARENT_LINE_NUMBER = l_prev_line_number;
3424 END IF;
3425
3426 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3427 FND_LOG.string(log_level => FND_LOG.level_statement,
3428 module => g_module_prefix || 'GET_SEARCH_MAX_DISP_LINE_NUM',
3429 message => 'Leaving PON_NEGOTIATION_HELPER_PVT.GET_AUCTION_REQUEST_ID '
3430 || ', x_curr_max_disp_line_num = ' || x_curr_max_disp_line_num
3431 || ', x_prev_max_disp_line_num = ' || x_prev_max_disp_line_num);
3432 END IF;
3433
3434 END GET_SRCH_MAX_DISP_LINE_NUMBERS;
3435
3436
3437 /*======================================================================
3438 PROCEDURE : DELETE_DISCUSSIONS
3439 PARAMETERS: 1. x_result - return status.
3440 2. x_error_code - error code
3441 3. x_error_message - The actual error message
3442 4. p_auction_header_id - The auction header id
3443 COMMENT : This procedure deletes all the discussions in the negotiation
3444 and also its children
3445 ======================================================================*/
3446
3447 PROCEDURE DELETE_DISCUSSIONS (
3448 x_result OUT NOCOPY VARCHAR2,
3449 x_error_code OUT NOCOPY VARCHAR2,
3450 x_error_message OUT NOCOPY VARCHAR2,
3451 p_auction_header_id IN NUMBER
3452 ) IS
3453
3454
3455 l_module_name VARCHAR2 (30);
3456 l_discussion_id PON_DISCUSSIONS.DISCUSSION_ID%TYPE;
3457
3458
3459 BEGIN
3460
3461 l_module_name := 'DELETE_DISCUSSIONS';
3462 x_result := FND_API.g_ret_sts_success;
3463
3464 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
3465 FND_LOG.string (log_level => FND_LOG.level_procedure,
3466 module => g_module_prefix || l_module_name,
3467 message => 'Entered procedure = ' || l_module_name ||
3468 ' p_auction_header_id = ' || p_auction_header_id);
3469 END IF;
3470
3471 BEGIN
3472 SELECT discussion_id
3473 INTO l_discussion_id
3474 FROM pon_discussions
3475 WHERE pk1_value = p_auction_header_id;
3476
3477 EXCEPTION WHEN NO_DATA_FOUND THEN
3478 RETURN;
3479 END;
3480
3481 --delete from PON_TE_RECIPIENTS
3482 DELETE FROM
3483 PON_TE_RECIPIENTS
3484 WHERE
3485 ENTRY_ID IN ( SELECT ENTRY_ID
3486 FROM PON_THREAD_ENTRIES
3487 WHERE DISCUSSION_ID = l_discussion_id);
3488
3489 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3490 FND_LOG.string (log_level => FND_LOG.level_statement,
3491 module => g_module_prefix || l_module_name,
3492 message => 'PON_TE_RECIPIENTS records deleted');
3493 END IF;
3494
3495 --delete from PON_TE_VIEW_AUDIT
3496 DELETE FROM
3497 PON_TE_VIEW_AUDIT
3498 WHERE
3499 ENTRY_ID IN ( SELECT ENTRY_ID
3500 FROM PON_THREAD_ENTRIES
3501 WHERE DISCUSSION_ID = l_discussion_id);
3502
3503 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3504 FND_LOG.string (log_level => FND_LOG.level_statement,
3505 module => g_module_prefix || l_module_name,
3506 message => 'PON_TE_VIEW_AUDIT records deleted');
3507 END IF;
3508
3509 DELETE FROM
3510 PON_THREAD_ENTRIES
3511 WHERE
3512 DISCUSSION_ID = l_discussion_id;
3513
3514 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3515 FND_LOG.string (log_level => FND_LOG.level_statement,
3516 module => g_module_prefix || l_module_name,
3517 message => 'PON_THREAD_ENTRIES records deleted');
3518 END IF;
3519
3520 DELETE FROM
3521 PON_THREADS
3522 WHERE
3523 DISCUSSION_ID = l_discussion_id;
3524
3525 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3526 FND_LOG.string (log_level => FND_LOG.level_statement,
3527 module => g_module_prefix || l_module_name,
3528 message => 'PON_THREADS records deleted');
3529 END IF;
3530
3531 DELETE FROM
3532 PON_DISCUSSIONS
3533 WHERE
3534 DISCUSSION_ID = l_discussion_id;
3535
3536 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3537 FND_LOG.string (log_level => FND_LOG.level_statement,
3538 module => g_module_prefix || l_module_name,
3539 message => 'PON_DISCUSSIONS records deleted');
3540 END IF;
3541
3542 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
3543 FND_LOG.string (log_level => FND_LOG.level_procedure,
3544 module => g_module_prefix || l_module_name,
3545 message => 'Leaving procedure = ' || l_module_name);
3546 END IF;
3547
3548 EXCEPTION
3549 WHEN OTHERS THEN
3550 x_result := FND_API.g_ret_sts_unexp_error;
3551 x_error_code := SQLCODE;
3552 x_error_message := SUBSTR(SQLERRM, 1, 100);
3553
3554 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
3555 FND_LOG.string (log_level => FND_LOG.level_exception,
3556 module => g_module_prefix || l_module_name,
3557 message => 'Unexpected exception occured error_code = ' ||
3558 x_error_code || ', error_message = ' || x_error_message);
3559 END IF;
3560 END DELETE_DISCUSSIONS;
3561
3562 /*======================================================================
3563 PROCEDURE : UPDATE_STAG_LINES_CLOSE_DATES
3564 PARAMETERS: 1. p_auction_header_id - The auction header id
3565 2. p_first_line_close_date - The staggered closing interval
3566 3. p_staggered_closing_interval - The auction header id
3567 4. x_last_line_close_date - The close date of the last line
3568 5. x_result - return status.
3569 6. x_error_code - error code
3570 7. x_error_message - The actual error message
3571 COMMENT : This procedure updates the close dates of the lines when
3572 the draft negotiation is saved
3573 ======================================================================*/
3574
3575 PROCEDURE UPDATE_STAG_LINES_CLOSE_DATES(
3576 x_result OUT NOCOPY VARCHAR2,
3577 x_error_code OUT NOCOPY VARCHAR2,
3578 x_error_message OUT NOCOPY VARCHAR2,
3579 p_auction_header_id in Number,
3580 p_first_line_close_date in date,
3581 p_staggered_closing_interval in number,
3582 p_start_disp_line_number in number,
3583 x_last_line_close_date out nocopy date
3584 )
3585 is
3586 l_line_number PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
3587 l_close_date PON_NEG_COPY_DATATYPES_GRP.SIMPLE_DATE_TYPE;
3588 l_group_type PON_NEG_COPY_DATATYPES_GRP.VARCHAR100_TYPE;
3589 l_batch_start NUMBER ;
3590 l_batch_end NUMBER ;
3591 l_batch_size NUMBER ;
3592 l_max_line_number NUMBER;
3593 l_curr_close_date DATE;
3594 l_stag_interval NUMBER;
3595
3596 BEGIN
3597 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3598 FND_LOG.string(log_level => FND_LOG.level_statement,
3599 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3600 message => 'Entered PON_NEGOTIATION_HELPER_PVT.UPDATE_LINES_CLOSE_DATES'
3601 || ', p_auction_header_id = ' || p_auction_header_id
3602 || ', p_first_line_close_date = ' || p_first_line_close_date
3603 || ', p_staggered_closing_interval = ' || p_staggered_closing_interval
3604 || ', p_start_disp_line_number = ' || p_start_disp_line_number
3605 );
3606 END IF;
3607
3608 x_result := FND_API.G_RET_STS_UNEXP_ERROR;
3609
3610 SELECT max(disp_line_number)
3611 INTO l_max_line_number
3612 FROM pon_auction_item_prices_all WHERE auction_header_id = p_auction_header_id;
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 => 'l_max_line_number : ' || l_max_line_number
3618 );
3619 END IF;
3620
3621 IF (l_max_line_number) > 0 then --{
3622
3623 IF (p_start_disp_line_number > l_max_line_number) THEN --{
3624
3625 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3626 FND_LOG.string(log_level => FND_LOG.level_statement,
3627 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3628 message => 'p_start_disp_line_number > l_max_line_number; so returning'
3629 );
3630 END IF;
3631
3632 select nvl (max(close_bidding_date), p_first_line_close_date)
3633 into x_last_line_close_date
3634 from pon_auction_item_prices_all
3635 where auction_header_id = p_auction_header_id;
3636
3637 return;
3638 END IF; --}
3639
3640 l_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
3641 l_stag_interval := p_staggered_closing_interval/1440;
3642
3643 select nvl (max(close_bidding_date), (p_first_line_close_date - l_stag_interval))
3644 into l_curr_close_date
3645 from pon_auction_item_prices_all
3646 where auction_header_id = p_auction_header_id
3647 and disp_line_number < p_start_disp_line_number;
3648
3649 --we offset the current close date back by the staggered interval
3650 --so that the close date of the first line/lot/group is set the the
3651 --first line close date in the loop
3652
3653 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3654 FND_LOG.string(log_level => FND_LOG.level_statement,
3655 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3656 message => 'l_batch_size : ' || l_batch_size
3657 ||'; l_stag_interval : ' || l_stag_interval
3658 ||'; l_curr_close_date : ' || to_char (l_curr_close_date, 'dd-mon-yyyy hh24:mi:ss')
3659 );
3660 END IF;
3661
3662 -- Define the initial batch range (line numbers are indexed from 1)
3663 l_batch_start := p_start_disp_line_number;
3664
3665 IF (l_max_line_number <l_batch_size) THEN
3666 l_batch_end := l_max_line_number;
3667 ELSE
3668 -- The batch end has to take into account the batch start too
3669 --So we need to translate the batch end based on batch start
3670 l_batch_end := l_batch_start + l_batch_size - 1;
3671 END IF;
3672
3673 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3674 FND_LOG.string(log_level => FND_LOG.level_statement,
3675 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3676 message => 'Finished setting the batching loop limits; l_batch_start : '||l_batch_start
3677 ||'; l_batch_end : ' || l_batch_end
3678 );
3679 END IF;
3680
3681 WHILE (l_batch_start <= l_max_line_number)
3682 LOOP
3683
3684 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3685 FND_LOG.string(log_level => FND_LOG.level_statement,
3686 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3687 message => 'Processing the batch from l_batch_start : ' || l_batch_start
3688 ||' to l_batch_end : ' || l_batch_end || ' ; bulk collecting the records now'
3689 );
3690 END IF;
3691
3692 select line_number, close_bidding_date, group_type
3693 bulk collect into
3694 l_line_number, l_close_date, l_group_type
3695 from pon_auction_item_prices_all
3696 WHERE auction_header_id = p_auction_header_id
3697 AND disp_line_number >= l_batch_start
3698 AND disp_line_number <= l_batch_end
3699 order by disp_line_number;
3700
3701 IF l_line_number.COUNT <> 0 THEN
3702
3703 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3704 FND_LOG.string(log_level => FND_LOG.level_statement,
3705 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3706 message => 'setting up the close dates array'
3707 );
3708 END IF;
3709
3710 FOR x IN 1..l_line_number.COUNT
3711 LOOP
3712 IF l_group_type(x) IN ('LINE','LOT','GROUP') THEN
3713 l_curr_close_date := l_curr_close_date + l_stag_interval;
3714 END IF;
3715 l_close_date(x) := l_curr_close_date;
3716 END LOOP;
3717
3718 x_last_line_close_date := l_close_date(l_line_number.COUNT);
3719
3720 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3721 FND_LOG.string(log_level => FND_LOG.level_statement,
3722 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3723 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')
3724 ||'; now bulk updating the PON_AUCTION_ITEM_PRICES_ALL'
3725 );
3726 END IF;
3727
3728 FORALL x IN 1..l_line_number.COUNT
3729 UPDATE PON_AUCTION_ITEM_PRICES_ALL
3730 set close_bidding_date = l_close_date(x)
3731 WHERE auction_header_id = p_auction_header_id
3732 AND line_number = l_line_number(x);
3733
3734 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3735 FND_LOG.string(log_level => FND_LOG.level_statement,
3736 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3737 message => 'Committing the batch now'
3738 );
3739 END IF;
3740
3741 COMMIT;
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 || 'UPDATE_STAG_LINES_CLOSE_DATES',
3746 message => 'Updating the batch limits for next iteration'
3747 );
3748 END IF;
3749
3750 l_batch_start := l_batch_end + 1;
3751
3752 IF (l_batch_end + l_batch_size > l_max_line_number) THEN
3753 l_batch_end := l_max_line_number;
3754 ELSE
3755 l_batch_end := l_batch_end + l_batch_size;
3756 END IF;
3757
3758 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3759 FND_LOG.string(log_level => FND_LOG.level_statement,
3760 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3761 message => 'New limits are l_batch_start : ' || l_batch_start
3762 || '; l_batch_end : ' || l_batch_end
3763 );
3764 END IF;
3765
3766 END IF;
3767
3768 END LOOP;
3769
3770 ELSE
3771 x_last_line_close_date := p_first_line_close_date;
3772 END IF; --}
3773 x_result := FND_API.G_RET_STS_SUCCESS;
3774
3775 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3776 FND_LOG.string(log_level => FND_LOG.level_statement,
3777 module => g_module_prefix || 'UPDATE_STAG_LINES_CLOSE_DATES',
3778 message => 'Exitting the method with '
3779 || 'x_last_line_close_date : '||x_last_line_close_date
3780 || '; x_result : '||x_result
3781 || '; x_error_code : '||x_error_code
3782 || '; x_error_message : '||x_error_message
3783 );
3784 END IF;
3785 END;
3786
3787
3788 /*======================================================================
3789 * FUNCTION : COUNT_LINES_LOTS_GROUPS PUBLIC
3790 * PARAMETERS:
3791 * p_auction_header_id IN header id of the auction
3792 *
3793 * COMMENT : returns the count of LINES, LOTS and GROUPS in the
3794 * negotiation
3795 *======================================================================*/
3796
3797 FUNCTION COUNT_LINES_LOTS_GROUPS (p_auction_header_id IN NUMBER) RETURN NUMBER
3798 IS
3799 l_lines_lots_groups_count NUMBER := -1;
3800 BEGIN
3801
3802 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3803 FND_LOG.string(log_level => FND_LOG.level_statement,
3804 module => g_module_prefix || 'COUNT_LINES_LOTS_GROUPS',
3805 message => 'Entered the procedure COUNT_LINES_LOTS_GROUPS; p_auction_header_id : ' || p_auction_header_id );
3806 END IF;
3807
3808
3809 SELECT Count(1) into l_lines_lots_groups_count
3810 FROM pon_auction_item_prices_all
3811 WHERE group_type IN ('LINE', 'LOT', 'GROUP')
3812 AND auction_header_id = p_auction_header_id;
3813
3814 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3815 FND_LOG.string(log_level => FND_LOG.level_statement,
3816 module => g_module_prefix || 'GET_SEARCH_MIN_DISP_LINE_NUM',
3817 message => 'Exitting the procedure COUNT_LINES_LOTS_GROUPS; l_lines_lots_groups_count : ' || l_lines_lots_groups_count );
3818 END IF;
3819
3820 RETURN l_lines_lots_groups_count;
3821
3822 END;
3823
3824
3825 /*======================================================================
3826 * FUNCTION : GET_PO_AUTHORIZATION_STATUS PUBLIC
3827 * PARAMETERS:
3828 * p_document_id IN po header id
3829 * p_document_type IN the PO document type ('PO'/'PA')
3830 * p_document_subtype IN PO subdoctype id
3831 *
3832 * COMMENT : returns the authorization status of PO
3833 *
3834 *======================================================================*/
3835
3836 FUNCTION GET_PO_AUTHORIZATION_STATUS (
3837 p_document_id IN VARCHAR2 ,
3838 p_document_type IN VARCHAR2 ,
3839 p_document_subtype IN VARCHAR2
3840 ) RETURN VARCHAR2
3841 IS
3842 v_return_status VARCHAR2(1);
3843 v_po_auth_status PO_LOOKUP_CODES.displayed_field%TYPE;
3844 BEGIN
3845
3846 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3847 FND_LOG.string(log_level => FND_LOG.level_statement,
3848 module => g_module_prefix || 'GET_PO_AUTHORIZATION_STATUS',
3849 message => 'Entered the procedure GET_PO_AUTHORIZATION_STATUS; p_document_id : ' || p_document_id
3850 || '; p_document_type : ' || p_document_type
3851 || '; p_document_subtype : ' || p_document_subtype);
3852 END IF;
3853
3854 -- The below if block is for safety and gracefule behaviour of the function
3855 -- The p_document_id cannot be null
3856 -- If the p_document_type is null, it means it's an RFI for whihc a PO cannot be created
3857 -- in this case we return null as the status of the PO
3858
3859 IF (p_document_id is null OR --This case cannot happen
3860 p_document_type is null -- this means it's an RFI
3861 ) THEN
3862 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3863 FND_LOG.string(log_level => FND_LOG.level_statement,
3864 module => g_module_prefix || 'GET_PO_AUTHORIZATION_STATUS',
3865 message => 'Returning null because either p_document_id or p_document_type is null');
3866 END IF;
3867 return null;
3868 END IF;
3869
3870 PO_CORE_S.get_document_status(
3871 p_document_id => p_document_id,
3872 p_document_type => p_document_type,
3873 p_document_subtype => p_document_subtype,
3874 x_return_status => v_return_status,
3875 x_document_status => v_po_auth_status
3876 );
3877
3878 IF ( v_return_status <> 'S' ) THEN
3879 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3880 FND_LOG.string(log_level => FND_LOG.level_statement,
3881 module => g_module_prefix || 'GET_PO_AUTHORIZATION_STATUS',
3882 message => 'Returning null because either x_return_status : ' || v_return_status);
3883 END IF;
3884 return null;
3885 END IF;
3886
3887 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3888 FND_LOG.string(log_level => FND_LOG.level_statement,
3889 module => g_module_prefix || 'GET_PO_AUTHORIZATION_STATUS',
3890 message => 'Exitting the procedure GET_PO_AUTHORIZATION_STATUS; v_return_status : ' || v_return_status
3891 || '; v_po_auth_status : ' || v_po_auth_status);
3892 END IF;
3893
3894 RETURN v_po_auth_status;
3895
3896 END GET_PO_AUTHORIZATION_STATUS;
3897
3898 /*======================================================================
3899 * PROCEDURE : HAS_PRICE_TIERS
3900 * PARAMETERS: 1. x_result - return status.
3901 * 2. x_error_code - error code
3902 * 3. x_error_message - The actual error message
3903 * 4. p_auction_header_id - The auction header id
3904 * 5. x_has_price_tiers - flag to indicate if negotiation has price tiers or not
3905 * COMMENT : It takes auction header id as the in parameter and returns Y if there is a line with price
3906 * tier, for this auction,. If there is no such line it returns N.
3907 *======================================================================*/
3908
3909 PROCEDURE HAS_PRICE_TIERS (
3910 x_result OUT NOCOPY VARCHAR2,
3911 x_error_code OUT NOCOPY VARCHAR2,
3912 x_error_message OUT NOCOPY VARCHAR2,
3913 p_auction_header_id IN NUMBER,
3914 x_has_price_tiers OUT NOCOPY VARCHAR2
3915 ) IS
3916
3917 l_module_name VARCHAR2 (30);
3918
3919 BEGIN
3920
3921 l_module_name := 'has_price_tiers';
3922 x_result := FND_API.g_ret_sts_success;
3923 x_has_price_tiers := 'Y';
3924
3925 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3926 FND_LOG.string(log_level => FND_LOG.level_statement,
3927 module => g_module_prefix || l_module_name,
3928 message => 'Entered the procedure HAS_PRICE_TIERS; p_auction_header_id : ' || p_auction_header_id);
3929 END IF;
3930
3931 BEGIN
3932 --
3933 -- Check for the existence of a row in the shipments table, for
3934 -- the given auction header id
3935 --
3936
3937 SELECT
3938 'Y'
3939 INTO
3940 x_has_price_tiers
3941 FROM
3942 pon_auction_shipments_all
3943 WHERE
3944 auction_header_id = p_auction_header_id AND
3945 rownum = 1;
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 => 'x_has_price_tiers' || x_has_price_tiers);
3951 END IF;
3952
3953 EXCEPTION
3954 WHEN NO_DATA_FOUND THEN
3955
3956 --
3957 --There are no shipments for the auction, so set the return value to N
3958 --
3959
3960 x_has_price_tiers := 'N';
3961 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3962 FND_LOG.string(log_level => FND_LOG.level_statement,
3963 module => g_module_prefix || 'HAS_PRICE_TIERS',
3964 message => 'No shipments for auction; x_has_price_tiers' || x_has_price_tiers);
3965 END IF;
3966 END;
3967
3968 EXCEPTION
3969 WHEN OTHERS THEN
3970 --
3971 --If there are any other exceptions in the code, report them to the caller
3972 --
3973 x_result := FND_API.g_ret_sts_unexp_error;
3974 x_error_code := SQLCODE;
3975 x_error_message := SUBSTR(SQLERRM, 1, 100);
3976
3977 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3978 FND_LOG.string(log_level => FND_LOG.level_statement,
3979 module => g_module_prefix || 'HAS_PRICE_TIERS',
3980 message => 'EXCEPTION ; x_error_code' || x_error_code || ' and x_error_message : ' || x_error_message);
3981 END IF;
3982
3983
3984 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
3985 FND_LOG.string(log_level => FND_LOG.level_statement,
3986 module => g_module_prefix || l_module_name,
3987 message => 'Returning to the caller with x_has_price_tiers: '|| x_has_price_tiers );
3988 END IF;
3989
3990 END has_price_tiers;
3991
3992
3993 /*======================================================================
3994 * PROCEDURE : HANDLE_CHANGE_PRICE_TIERS
3995 * PARAMETERS: 1. x_result - return status.
3996 * 2. x_error_code - error code
3997 * 3. x_error_message - The actual error message
3998 * 4. p_auction_header_id - The auction header id
3999 * 5. p_delete_price_tiers -- Flag to indicate if price tiers to be removed or not
4000 * COMMENT : This methods deletes all the lines in the DB table PON_AUCTION_SHIPMENTS_ALL,
4001 * for the given auction header id, sets the modify falg for new round and amendments
4002 * and sets the default price break settings.
4003 *======================================================================*/
4004
4005 PROCEDURE HANDLE_CHANGE_PRICE_TIERS (
4006 x_result OUT NOCOPY VARCHAR2,
4007 x_error_code OUT NOCOPY VARCHAR2,
4008 x_error_message OUT NOCOPY VARCHAR2,
4009 p_auction_header_id IN NUMBER,
4010 p_delete_price_tiers IN VARCHAR2
4011 ) IS
4012
4013 l_module_name VARCHAR2 (30);
4014 l_max_line_number NUMBER;
4015 l_batch_size NUMBER;
4016 l_batch_start NUMBER;
4017 l_batch_end NUMBER;
4018 l_parent_auc_max_line_number NUMBER;
4019
4020 l_prev_price_tiers_indicator PON_AUCTION_HEADERS_ALL.PRICE_TIERS_INDICATOR%TYPE;
4021 l_amendment_number PON_AUCTION_HEADERS_ALL.AMENDMENT_NUMBER%TYPE;
4022 l_round_number PON_AUCTION_HEADERS_ALL.AUCTION_ROUND_NUMBER%TYPE;
4023
4024
4025 l_is_new_amendment BOOLEAN;
4026 l_is_amendment BOOLEAN;
4027 l_is_new_round BOOLEAN;
4028
4029 BEGIN
4030
4031 l_module_name := 'handle_change_price_tiers';
4032 x_result := FND_API.g_ret_sts_success;
4033
4034 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4035 FND_LOG.string(log_level => FND_LOG.level_statement,
4036 module => g_module_prefix || l_module_name,
4037 message => 'Entered the procedure ; p_auction_header_id : ' || p_auction_header_id || ' ; p_delete_price_tiers : '|| p_delete_price_tiers);
4038 END IF;
4039 --
4040 -- retrieve the price tier indicator, amendment number , new round number for the auction
4041 --
4042 SELECT price_tiers_indicator,
4043 amendment_number,
4044 auction_round_number
4045 INTO l_prev_price_tiers_indicator,
4046 l_amendment_number,
4047 l_round_number
4048 FROM pon_auction_headers_all
4049 WHERE auction_header_id = p_auction_header_id;
4050
4051 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4052 FND_LOG.string(log_level => FND_LOG.level_statement,
4053 module => g_module_prefix || l_module_name,
4054 message => 'l_prev_price_tiers_indicator : ' || l_prev_price_tiers_indicator);
4055 END IF;
4056
4057 --
4058 -- retrieve the maximum line number present for the auction
4059 --
4060 SELECT MAX(LINE_NUMBER)
4061 INTO l_max_line_number
4062 FROM PON_AUCTION_ITEM_PRICES_ALL
4063 WHERE AUCTION_HEADER_ID=p_auction_header_id;
4064
4065 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4066 FND_LOG.string(log_level => FND_LOG.level_statement,
4067 module => g_module_prefix || l_module_name,
4068 message => 'l_max_line_number : ' || l_max_line_number || '; l_amendment_number : ' || l_amendment_number
4069 || ' ; l_round_number : ' || l_round_number);
4070 END IF;
4071
4072
4073 IF (p_delete_price_tiers = 'Y') THEN--{
4074
4075 --
4076 --Check if the auction is an amendment or new round.
4077 --If yes, fetch the max line number of the previous round.
4078 --
4079 IF (l_amendment_number > 0) THEN
4080
4081 --this is an amendment
4082 l_is_amendment := true;
4083 SELECT max_internal_line_num
4084 INTO l_parent_auc_max_line_number
4085 FROM pon_auction_headers_all
4086 WHERE auction_header_id =
4087 (SELECT auction_header_id_prev_amend
4088 FROM pon_auction_headers_all
4089 WHERE auction_header_id = p_auction_header_id);
4090
4091 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4092 FND_LOG.string(log_level => FND_LOG.level_statement,
4093 module => g_module_prefix || l_module_name,
4094 message => 'Neg is Amendment ; l_parent_auc_max_line_number : ' || l_parent_auc_max_line_number);
4095 END IF;
4096
4097
4098 ELSIF (l_round_number > 1) THEN
4099 --this is an new round
4100 l_is_new_round := true;
4101 SELECT max_internal_line_num
4102 INTO l_parent_auc_max_line_number
4103 FROM pon_auction_headers_all
4104 WHERE auction_header_id =
4105 (SELECT auction_header_id_prev_round
4106 FROM pon_auction_headers_all
4107 WHERE auction_header_id = p_auction_header_id);
4108
4109 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4110 FND_LOG.string(log_level => FND_LOG.level_statement,
4111 module => g_module_prefix || l_module_name,
4112 message => 'This is new round ' || '; l_parent_auc_max_line_number : '|| l_parent_auc_max_line_number);
4113 END IF;
4114
4115 END IF;
4116 END IF; --}
4117
4118 -- Get the batch size
4119 l_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
4120
4121 -- Draft with no lines, or RFI,CPA with no lines we need to skip batching
4122 -- its build into the loop logic but just to be explicit about this condition
4123
4124 IF (l_max_line_number > 0) THEN --{
4125
4126 -- Define the initial batch range (line numbers are indexed from 1)
4127 l_batch_start := 1;
4128
4129 IF (l_max_line_number <= l_batch_size) THEN
4130 l_batch_end := l_max_line_number;
4131 ELSE
4132 l_batch_end := l_batch_size;
4133 END IF;
4134
4135 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4136 FND_LOG.string(log_level => FND_LOG.level_statement,
4137 module => g_module_prefix || l_module_name,
4138 message => 'l_batch_size : ' || l_batch_size || '; l_batch_end : '
4139 || l_batch_end);
4140 END IF;
4141
4142 WHILE (l_batch_start <= l_max_line_number) LOOP
4143
4144 --
4145 --default the price break settings if price tiers indicator has been changed from price breaks
4146 --
4147 IF (l_prev_price_tiers_indicator = 'PRICE_BREAKS') THEN --{
4148
4149 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4150 FND_LOG.string(log_level => FND_LOG.level_statement,
4151 module => g_module_prefix || l_module_name,
4152 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');
4153 END IF;
4154
4155 UPDATE pon_auction_item_prices_all
4156 SET price_break_type = 'NONE',
4157 price_break_neg_flag = 'Y'
4158 WHERE auction_header_id = p_auction_header_id
4159 AND line_number >= l_batch_start
4160 AND line_number <= l_batch_end;
4161
4162 END IF; --}
4163
4164 IF (p_delete_price_tiers = 'Y') THEN--{
4165 --
4166 -- Delete the entries from the shipments table for this auction
4167 --
4168 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4169 FND_LOG.string(log_level => FND_LOG.level_statement,
4170 module => g_module_prefix || l_module_name,
4171 message => 'Deleting the entries from the shipments table for negotiation ' || p_auction_header_id
4172 || ' and line_number between ' || l_batch_start ||' and ' || l_batch_end );
4173 END IF;
4174
4175 DELETE FROM
4176 pon_auction_shipments_all
4177 WHERE
4178 auction_header_id = p_auction_header_id AND
4179 line_number >= l_batch_start AND
4180 line_number <= l_batch_end;
4181
4182 --
4183 --Delete the child differentials for the above deleted shipments
4184 -- only if they are price breaks. We won't have differential children
4185 --for quantity based tiers
4186 --
4187
4188 IF (l_prev_price_tiers_indicator = 'PRICE_BREAKS') THEN
4189
4190 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4191 FND_LOG.string(log_level => FND_LOG.level_statement,
4192 module => g_module_prefix || l_module_name,
4193 message => 'Deleting the child differentials for negotiation ' || p_auction_header_id
4194 || ' and line_number between ' || l_batch_start ||' and ' || l_batch_end );
4195 END IF;
4196
4197 DELETE FROM
4198 pon_price_differentials
4199 WHERE
4200 auction_header_id = p_auction_header_id AND
4201 shipment_number > -1 AND
4202 line_number >= l_batch_start AND
4203 line_number <= l_batch_end;
4204
4205 END IF;
4206
4207 --
4208 --Check if the auction is a new round or an amendment. in that case,
4209 --we need to mark all the lines with price tiers (having rows in
4210 --PON_AUCTION_SHIPMENTS_ALL table) from the previous/parent auction
4211 --as modified and set the flag has_price_tiers as 'N'.
4212 --
4213 IF (l_is_new_round OR l_is_new_amendment) THEN
4214
4215 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4216 FND_LOG.string(log_level => FND_LOG.level_statement,
4217 module => g_module_prefix || l_module_name,
4218 message => 'Negotiation is a new round or an amendment. Updating flags modified_flag and has_quantity_tiers');
4219 END IF;
4220
4221 --
4222 --Only those lines which were present in previous round or previous
4223 --amendment we need to set the modified_flag.
4224 --
4225
4226 UPDATE pon_auction_item_prices_all
4227 SET has_quantity_tiers = 'N',
4228 has_shipments_flag = 'N',
4229 modified_flag = decode(least(line_number,l_parent_auc_max_line_number),
4230 line_number,'Y', modified_flag),
4231 modified_date = SYSDATE
4232 WHERE
4233 auction_header_id = p_auction_header_id AND
4234 (has_quantity_tiers = 'Y' OR has_shipments_flag = 'Y') AND
4235 line_number >= l_batch_start AND
4236 line_number <= l_batch_end;
4237
4238 ELSE
4239
4240 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4241 FND_LOG.string(log_level => FND_LOG.level_statement,
4242 module => g_module_prefix || l_module_name,
4243 message => 'Negotiation is a not new round or an amendment. Updating has_quantity_tiers flag');
4244 END IF;
4245
4246 UPDATE pon_auction_item_prices_all
4247 SET has_quantity_tiers = 'N',
4248 has_shipments_flag = 'N'
4249 WHERE
4250 auction_header_id = p_auction_header_id AND
4251 (has_quantity_tiers = 'Y' OR has_shipments_flag = 'Y') AND
4252 line_number >= l_batch_start AND
4253 line_number <= l_batch_end;
4254
4255 END IF; --new round or amendment
4256
4257 END IF; --} --p_delete_price_tiers = 'Y'
4258
4259 -- Find the new batch range
4260 l_batch_start := l_batch_end + 1;
4261 IF (l_batch_end + l_batch_size > l_max_line_number) THEN
4262 l_batch_end := l_max_line_number;
4263 ELSE
4264 l_batch_end := l_batch_end + l_batch_size;
4265 END IF;
4266
4267 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4268 FND_LOG.string(log_level => FND_LOG.level_statement,
4269 module => g_module_prefix || l_module_name,
4270 message => 'New Batch with l_batch_start' || l_batch_start || ' and l_batch_end : ' || l_batch_end);
4271 END IF;
4272
4273 -- Issue a commit to push in all changes
4274 COMMIT;
4275 END LOOP;
4276
4277 END IF; --}
4278
4279 EXCEPTION
4280 WHEN OTHERS THEN
4281 x_result := FND_API.g_ret_sts_unexp_error;
4282 x_error_code := SQLCODE;
4283 x_error_message := SUBSTR(SQLERRM, 1, 100);
4284
4285 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4286 FND_LOG.string(log_level => FND_LOG.level_statement,
4287 module => g_module_prefix || l_module_name,
4288 message => 'EXCEPTION ; x_error_code' || x_error_code || ' and x_error_message : ' || x_error_message);
4289 END IF;
4290
4291
4292 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4293 FND_LOG.string(log_level => FND_LOG.level_statement,
4294 module => g_module_prefix || l_module_name,
4295 message => 'Returning to the caller');
4296 END IF;
4297 END HANDLE_CHANGE_PRICE_TIERS;
4298
4299 --Bug 6074506
4300 /*======================================================================
4301 * FUNCTION : GET_ABBR_DOC_TYPE_GRP_NAME PUBLIC
4302 * PARAMETERS:
4303 * p_doctype_id IN document type id of the auction
4304 *
4305 * COMMENT : returns the document froup name in English language
4306 *
4307 *======================================================================*/
4308
4309 FUNCTION GET_ABBR_DOC_TYPE_GRP_NAME (p_doctype_id IN NUMBER) RETURN VARCHAR2
4310 IS
4311 v_doctype_name pon_auc_doctypes_tl.name%TYPE;
4312
4313 BEGIN
4314
4315 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
4316 FND_LOG.string(log_level => FND_LOG.level_statement,
4317 module => g_module_prefix || 'GET_ABBR_DOC_TYPE_GRP_NAME',
4318 message => 'Entered the procedure GET_ABBR_DOC_TYPE_GRP_NAME; p_doctype_id : ' || p_doctype_id);
4319 END IF;
4320
4321 SELECT name
4322 INTO
4323 v_doctype_name
4324 FROM
4325 pon_auc_doctypes_tl
4326 WHERE
4327 doctype_id = p_doctype_id and
4328 language = 'US';
4329
4330 return v_doctype_name;
4331
4332 EXCEPTION WHEN NO_DATA_FOUND THEN
4333
4334 v_doctype_name := NULL;
4335
4336 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
4337 FND_LOG.string(log_level => FND_LOG.level_exception,
4338 module => g_module_prefix || 'GET_ABBR_DOC_TYPE_GRP_NAME',
4339 message => 'Exception in PON_NEGOTIATION_HELPER_PVT.GET_ABBR_DOC_TYPE_GRP_NAME '
4340 || 'errnum = ' || SQLCODE || ', errmsg = ' || SUBSTR (SQLERRM, 1, 200));
4341 END IF;
4342
4343 return v_doctype_name;
4344
4345 END GET_ABBR_DOC_TYPE_GRP_NAME;
4346
4347 END PON_NEGOTIATION_HELPER_PVT;
[Home] [Help]