[Home] [Help]
PACKAGE BODY: APPS.PON_LARGE_AUCTION_UTIL_PKG
Source
1 PACKAGE BODY PON_LARGE_AUCTION_UTIL_PKG AS
2 -- $Header: PONLGUTB.pls 120.16 2011/10/04 09:09:58 hvutukur ship $
3
4 g_debug_mode CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 g_module_prefix CONSTANT VARCHAR2(35) := 'pon.plsql.concRequestsPkg.';
6
7 -- ======================================================================
8 -- PROCEDURE: DELETE_LINE_ATTACHMENTS PRIVATE
9 -- PARAMETERS:
10 -- p_auc_header_id IN auction header id of negotiation
11 -- p_bid_number IN bid number to delete attachments on
12 --
13 -- COMMENT: Deletes line level attachments for a bid
14 -- ======================================================================
15 PROCEDURE delete_line_attachments
16 (
17 p_auc_header_id IN pon_bid_headers.auction_header_id%TYPE,
18 p_bid_number IN pon_bid_headers.bid_number%TYPE,
19 p_batch_start IN pon_bid_item_prices.line_number%TYPE,
20 p_batch_end IN pon_bid_item_prices.line_number%TYPE
21 ) IS
22
23 -- Determine which lines have attachments
24 CURSOR bid_lines_with_attachments IS
25 SELECT DISTINCT ad.pk3_value
26 FROM fnd_attached_documents ad
27 WHERE ad.entity_name = 'PON_BID_ITEM_PRICES'
28 AND ad.pk1_value = p_auc_header_id
29 AND ad.pk2_value = p_bid_number
30 AND ad.pk3_value IS NOT null
31 AND to_number(ad.pk3_value) BETWEEN p_batch_start AND p_batch_end;
32
33 BEGIN
34
35 -- Delete all line level attachments
36 FOR line IN bid_lines_with_attachments LOOP
37
38 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
39 (x_entity_name => 'PON_BID_ITEM_PRICES',
40 x_pk1_value => p_auc_header_id,
41 x_pk2_value => p_bid_number,
42 x_pk3_value => line.pk3_value,
43 x_delete_document_flag => 'Y');
44 END LOOP;
45
46 END delete_line_attachments;
47 -- ======================================================================
48 -- PROCEDURE: DELETE_BID_PAYMENT_ATTACHMENTS PRIVATE
49 -- PARAMETERS:
50 -- p_bid_number IN bid number to delete attachments on
51 --
52 -- COMMENT: Deletes payment level attachments for a bid
53 -- ======================================================================
54 PROCEDURE delete_bid_payment_attachments
55 (
56 p_bid_number IN pon_bid_headers.bid_number%TYPE,
57 p_batch_start IN pon_bid_item_prices.line_number%TYPE,
58 p_batch_end IN pon_bid_item_prices.line_number%TYPE
59 ) IS
60
61 l_module_name VARCHAR2 (30);
62
63 CURSOR bid_payments_with_attachments IS
64 SELECT pay.bid_payment_id, pay.bid_number, pay.bid_line_number
65 FROM fnd_attached_documents ad,
66 pon_bid_payments_shipments pay
67 WHERE ad.entity_name = 'PON_BID_PAYMENTS_SHIPMENTS'
68 AND ad.pk1_value = pay.bid_number
69 AND ad.pk2_value = pay.bid_line_number
70 AND ad.pk3_value = pay.bid_payment_id
71 AND pay.bid_number = p_bid_number
72 AND pay.bid_line_number BETWEEN p_batch_start AND p_batch_end;
73 BEGIN
74 l_module_name := 'Delete_bid_Payment_Attachments';
75
76 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
77 FND_LOG.string (log_level => FND_LOG.level_procedure,
78 module => g_module_prefix || l_module_name,
79 message => 'Entered procedure = ' || l_module_name);
80 END IF;
81 -- Delete all attachments for the bid payments
82 FOR payment IN bid_payments_with_attachments LOOP
83 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
84 FND_LOG.string (log_level => FND_LOG.level_procedure,
85 module => g_module_prefix || l_module_name,
86 message => 'Deleting fnd attachments for bid payment id ' ||payment.bid_payment_id||'='|| l_module_name);
87 END IF;
88
89
90 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
91 (x_entity_name => 'PON_BID_PAYMENTS_SHIPMENTS',
92 x_pk1_value => payment.bid_number,
93 x_pk2_value => payment.bid_line_number,
94 x_pk3_value => payment.bid_payment_id,
95 x_delete_document_flag => 'Y');
96 END LOOP;
97
98 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
99 FND_LOG.string (log_level => FND_LOG.level_procedure,
100 module => g_module_prefix || l_module_name,
101 message => 'After Call FND_ATTACHED_DOCUMENTS2_PKG.DELETE_ATTACHMENTS = ' || l_module_name);
102 END IF;
103
104 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
105 FND_LOG.string (log_level => FND_LOG.level_procedure,
106 module => g_module_prefix || l_module_name,
107 message => 'Leaving procedure = ' || l_module_name);
108 END IF;
109
110 END delete_bid_payment_attachments;
111
112 -- ======================================================================
113 -- PROCEDURE: DELETE_BID PUBLIC
114 -- PARAMETERS:
115 -- p_auc_header_id IN auction header id of negotiation
116 -- p_bid_number IN bid number to delete
117 --
118 -- COMMENT: Completely deletes a bid from the database
119 -- ======================================================================
120 PROCEDURE delete_bid
121 (
122 p_auc_header_id IN pon_bid_headers.auction_header_id%TYPE,
123 p_bid_number IN pon_bid_headers.bid_number%TYPE
124 ) IS
125 l_max_line_number pon_bid_item_prices.line_number%TYPE;
126 l_batch_start pon_bid_item_prices.line_number%TYPE;
127 l_batch_end pon_bid_item_prices.line_number%TYPE;
128
129 --Unsolicited Lines Project
130 l_result VARCHAR2(1);
131 l_error_code VARCHAR2(100);
132 l_error_message VARCHAR2(100);
133 BEGIN
134
135 -- START BATCHING
136
137 -- Determine the maximum line number for the negotiation
138 SELECT ah.max_internal_line_num
139 INTO l_max_line_number
140 FROM pon_auction_headers_all ah
141 WHERE ah.auction_header_id = p_auc_header_id;
142
143 -- Define the initial batch range (line numbers are indexed from 1)
144 l_batch_start := 1;
145 IF (l_max_line_number < PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE) THEN
146 l_batch_end := l_max_line_number;
147 ELSE
148 l_batch_end := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
149 END IF;
150
151 -- Unsolicited Lines Project : Delete unsolicited lines
152 PON_UNSOL_UTIL_PKG.DELETE_ALL_LINES(p_auction_header_id => p_auc_header_id,
153 p_bid_number => p_bid_number,
154 x_result => l_result,
155 x_error_code => l_error_code,
156 x_error_message => l_error_message);
157
158 -- Delete header attributes here
159 DELETE FROM pon_bid_attribute_values
160 WHERE bid_number = p_bid_number
161 AND line_number = -1;
162
163 WHILE (l_batch_start <= l_max_line_number) LOOP
164
165 -- Delete price differentials
166 DELETE FROM pon_bid_price_differentials
167 WHERE bid_number = p_bid_number
168 AND line_number BETWEEN l_batch_start AND l_batch_end;
169
170 -- Delete attributes
171 DELETE FROM pon_bid_attribute_values
172 WHERE bid_number = p_bid_number
173 AND line_number BETWEEN l_batch_start AND l_batch_end;
174
175 -- Delete price elements
176 DELETE FROM pon_bid_price_elements
177 WHERE bid_number = p_bid_number
178 AND line_number BETWEEN l_batch_start AND l_batch_end;
179
180 -- Delete shipments
181 DELETE FROM pon_bid_shipments
182 WHERE bid_number = p_bid_number
183 AND line_number BETWEEN l_batch_start AND l_batch_end;
184
185 -- Delete payment attachments
186 delete_bid_payment_attachments
187 ( p_bid_number,
188 l_batch_start,
189 l_batch_end);
190
191 -- Delete Payments
192 DELETE FROM pon_bid_payments_shipments
193 WHERE bid_number = p_bid_number
194 AND bid_line_number BETWEEN l_batch_start AND l_batch_end;
195
196 -- Delete line attachments
197 delete_line_attachments
198 (p_auc_header_id,
199 p_bid_number,
200 l_batch_start,
201 l_batch_end);
202
203 -- Delete lines
204 DELETE FROM pon_bid_item_prices
205 WHERE bid_number = p_bid_number
206 AND line_number BETWEEN l_batch_start AND l_batch_end;
207
208 -- If there is more than one batch, then commit the batch
209 -- or else let the calling program commit the batch
210 IF (l_batch_end < l_max_line_number) THEN
211 commit;
212 END IF;
213
214 -- Find the new batch range
215 l_batch_start := l_batch_end + 1;
216 IF (l_batch_end + PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE > l_max_line_number) THEN
217 l_batch_end := l_max_line_number;
218 ELSE
219 l_batch_end := l_batch_end + PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
220 END IF;
221
222 END LOOP;
223
224 -- END BATCHING
225
226 -- Header and header attachments taken care of in middle tier
227
228 END delete_bid;
229
230 -- ======================================================================
231 -- PROCEDURE : GET_REQUEST_INFO PUBLIC
232 -- PARAMETERS:
233 -- p_request_id IN The request id to check the status of
234 -- x_phase OUT Returned displayable phase
235 -- x_status OUT Returned displayable status
236 -- x_devphase OUT Returned developer phase
237 -- x_dev_status OUT Returned developer status
238 -- x_message OUT Returned message describing extraneous condition
239 --
240 -- COMMENT: Wrapper around call to FND_CONCURRENT.GET_REQUEST_STATUS
241 -- ======================================================================
242 PROCEDURE get_request_info
243 (
244 p_request_id IN fnd_concurrent_requests.request_id%TYPE,
245 x_phase OUT NOCOPY VARCHAR2,
246 x_status OUT NOCOPY VARCHAR2,
247 x_devphase OUT NOCOPY VARCHAR2,
248 x_devstatus OUT NOCOPY VARCHAR2,
249 x_message OUT NOCOPY VARCHAR2
250 ) IS
251 l_request_id fnd_concurrent_requests.request_id%TYPE;
252 l_success BOOLEAN;
253 BEGIN
254
255 l_request_id := p_request_id;
256
257 -- Call FND_CONCURRENT API to get concurrent request status
258 l_success := FND_CONCURRENT.GET_REQUEST_STATUS(request_id => l_request_id,
259 phase => x_phase, status => x_status, dev_phase => x_devphase,
260 dev_status => x_devstatus, message => x_message);
261
262 -- Check if call was unsuccessful
263 IF (NOT l_success) THEN
264 IF (g_debug_mode = 'Y'
265 AND FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
266 FND_LOG.string(log_level => FND_LOG.level_statement,
267 module => g_module_prefix || 'get_request_info',
268 message => x_message);
269 END IF;
270
271 x_phase := null;
272 x_status := null;
273 END IF;
274
275 EXCEPTION
276 WHEN OTHERS THEN
277 IF (g_debug_mode = 'Y'
278 AND FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
279 FND_LOG.string(log_level => FND_LOG.level_statement,
280 module => g_module_prefix || 'get_request_info',
281 message => SQLERRM);
282 END IF;
283
284 x_phase := null;
285 x_status := null;
286
287 END get_request_info;
288
289 -- ======================================================================
290 -- FUNCTION: REQUEST_HAS_ERRORS PUBLIC
291 -- PARAMETERS:
292 -- p_request_id IN The request id to check errors for
293 --
294 -- RETURN: VARCHAR2 Y/N if the request had/didn't have validation errors
295
296 -- COMMENT: Determines if any validation errors associated with p_request_id
297 -- were inserted in pon_interface_errors
298 -- ======================================================================
299 FUNCTION request_has_errors
300 (
301 p_request_id IN pon_interface_errors.request_id%TYPE
302 ) RETURN VARCHAR2 IS
303 l_has_errors VARCHAR2(1);
304 BEGIN
305
306 SELECT 'Y'
307 INTO l_has_errors
308 FROM pon_interface_errors
309 WHERE request_id = p_request_id
310 AND rownum = 1;
311
312 RETURN l_has_errors;
313
314 EXCEPTION
315 WHEN NO_DATA_FOUND THEN
316 RETURN 'N';
317
318 END request_has_errors;
319
320 -- ======================================================================
321 -- FUNCTION: REQUEST_ERROR_COUNT PUBLIC
322 -- PARAMETERS:
323 -- p_request_id IN The request id to count errors for
324 --
325 -- RETURN: NUMBER the number of errors for the request
326
327 -- COMMENT: Counts the number of validation errors for a request
328 -- ======================================================================
329 FUNCTION request_error_count
330 (
331 p_request_id IN pon_interface_errors.request_id%TYPE
332 ) RETURN NUMBER IS
333 l_error_count NUMBER;
334 BEGIN
335
336 SELECT count(request_id)
337 INTO l_error_count
338 FROM pon_interface_errors
339 WHERE request_id = p_request_id;
340
341 RETURN l_error_count;
342
343 EXCEPTION
344 WHEN NO_DATA_FOUND THEN
345 RETURN 0;
346
347 END request_error_count;
348
349 -- ======================================================================
350 -- FUNCTION: GET_REQUEST_INTERNAL_STATUS PUBLIC
351 -- PARAMETERS:
352 -- p_request_id IN The request id to check status of
353 --
354 -- RETURN: VARCHAR2 Internal status for concurrent request
355
356 -- COMMENT: Returns and internal status for the concurrent request
357 -- that can be used for comparisons
358 -- ======================================================================
359 FUNCTION get_request_internal_status
360 (
361 p_request_id IN fnd_concurrent_requests.request_id%TYPE
362 ) RETURN VARCHAR2 IS
363 l_phase VARCHAR2(80);
364 l_status VARCHAR2(80);
365 l_devphase VARCHAR2(30);
366 l_devstatus VARCHAR2(30);
367 l_message VARCHAR2(240);
368 BEGIN
369
370 get_request_info(p_request_id => p_request_id,
371 x_phase => l_phase,
372 x_status => l_status,
373 x_devphase => l_devphase,
374 x_devstatus => l_devstatus,
375 x_message => l_message);
376
377 IF (l_devphase IS null) THEN
378 RETURN 'INVALID';
379 END IF;
380
381 IF (l_devphase = 'COMPLETE') THEN
382 IF (request_has_errors(p_request_id => p_request_id) = 'Y') THEN
383 l_devstatus := 'ERROR';
384 END IF;
385
386 RETURN l_devstatus;
387 ELSE
388 RETURN l_devphase;
389 END IF;
390
391 END get_request_internal_status;
392
393 -- ======================================================================
394 -- FUNCTION: GET_REQUEST_DISPLAY_STATUS PUBLIC
395 -- PARAMETERS:
396 -- p_request_id IN The request id to check status of
397 --
398 -- RETURN: VARCHAR2 Displayable status for concurrent request
399
400 -- COMMENT: Returns a displayable status for the concurrent request
401 -- ======================================================================
402 FUNCTION get_request_display_status
403 (
404 p_request_id IN fnd_concurrent_requests.request_id%TYPE
405 ) RETURN VARCHAR2 IS
406 l_phase VARCHAR2(80);
407 l_status VARCHAR2(80);
408 l_devphase VARCHAR2(30);
409 l_devstatus VARCHAR2(30);
410 l_message VARCHAR2(240);
411 BEGIN
412
413 get_request_info(p_request_id => p_request_id,
414 x_phase => l_phase,
415 x_status => l_status,
416 x_devphase => l_devphase,
417 x_devstatus => l_devstatus,
418 x_message => l_message);
419
420 IF (l_devphase = 'COMPLETE') THEN
421
422 IF (request_has_errors(p_request_id => p_request_id) = 'Y') THEN
423 l_status := fnd_message.get_string('PON', 'PON_AUCTS_ERROR');
424 ELSIF (l_devstatus = 'NORMAL') THEN
425 l_status := fnd_message.get_string('PON', 'PON_REQUEST_NORMAL');
426 ELSIF (l_devstatus = 'WARNING') THEN
427 l_status := fnd_message.get_string('PON', 'PON_REQUEST_WARNINGS');
428 END IF;
429
430 RETURN l_status;
431 ELSE
432 RETURN l_phase;
433 END IF;
434
435 END get_request_display_status;
436
437 -- ======================================================================
438 -- FUNCTION: IS_AUCTION_REQUEST_PENDING PUBLIC
439 -- PARAMETERS:
440 -- p_auc_header_id IN Auction header id for which to check request status
441 --
442 -- RETURN: VARCHAR2 Y/N if the auction has a pending/completed request
443 --
444 -- COMMENT: Determines if a concurrent request associated with the
445 -- the auction is pending or completed
446 -- ======================================================================
447 FUNCTION is_auction_request_pending
448 (
449 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE
450 ) RETURN VARCHAR2 IS
451 l_request_id pon_auction_headers_all.request_id%TYPE;
452 BEGIN
453
454 SELECT request_id
455 INTO l_request_id
456 FROM pon_auction_headers_all
457 WHERE auction_header_id = p_auc_header_id;
458
459 IF (l_request_id IS null) THEN
460 RETURN 'N';
461 ELSIF (get_request_internal_status(p_request_id => l_request_id)
462 IN ('PENDING', 'RUNNING', 'INACTIVE')) THEN
463 RETURN 'Y';
464 ELSE
465 RETURN 'N';
466 END IF;
467
468 EXCEPTION
469 WHEN NO_DATA_FOUND THEN
470 RETURN 'N';
471
472 END is_auction_request_pending;
473
474 -- ======================================================================
475 -- FUNCTION: IS_BID_REQUEST_PENDING PUBLIC
476 -- PARAMETERS:
477 -- p_bid_number IN Bid number for which to check request status
478 --
479 -- RETURN: VARCHAR2 Y/N if the bid has a pending/completed request
480 --
481 -- COMMENT: Determines if a concurrent request associated with the
482 -- the bid is pending or completed
483 -- ======================================================================
484 FUNCTION is_bid_request_pending
485 (
486 p_bid_number IN pon_bid_headers.bid_number%TYPE
487 ) RETURN VARCHAR2 IS
488 l_request_id pon_bid_headers.request_id%TYPE;
489 BEGIN
490
491 SELECT request_id
492 INTO l_request_id
493 FROM pon_bid_headers
494 WHERE bid_number = p_bid_number;
495
496 IF (l_request_id IS null) THEN
497 RETURN 'N';
498 ELSIF (get_request_internal_status(p_request_id => l_request_id)
499 IN ('PENDING', 'RUNNING', 'INACTIVE')) THEN
500 RETURN 'Y';
501 ELSE
502 RETURN 'N';
503 END IF;
504
505 EXCEPTION
506 WHEN NO_DATA_FOUND THEN
507 RETURN 'N';
508
509 END is_bid_request_pending;
510
511 -- ======================================================================
512 -- FUNCTION: CANCEL_CONCURRENT_REQUEST PUBLIC
513 -- PARAMETERS:
514 -- p_request_id IN Request id to cancel
515 --
516 -- RETURN: VARCHAR2 null/error msg if successful/unsuccessful
517 --
518 -- COMMENT: Cancels concurrent request p_request_id using FND API:
519 -- FND_CONCURRENT.CANCEL_REQUEST
520 -- ======================================================================
521 FUNCTION cancel_concurrent_request
522 (
523 p_request_id IN fnd_concurrent_requests.request_id%TYPE
524 ) RETURN VARCHAR2 IS
525 l_success BOOLEAN;
526 l_message VARCHAR2(255);
527 BEGIN
528
529 l_success := FND_CONCURRENT.CANCEL_REQUEST(request_id => p_request_id,
530 message => l_message);
531
532 IF (l_success) THEN
533 RETURN null;
534 ELSE
535 IF (g_debug_mode = 'Y'
536 AND FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
537 FND_LOG.string(log_level => FND_LOG.level_statement,
538 module => g_module_prefix || 'cancel_concurrent_request',
539 message => l_message);
540 END IF;
541 RETURN l_message;
542 END IF;
543
544 END cancel_concurrent_request;
545
546
547 -- ======================================================================
548 -- FUNCTION: GET_DOCTYPE_SUFFIX PUBLIC
549 -- PARAMETERS:
550 -- p_auction_id IN auction-number
551 --
552 -- RETURN: VARCHAR2
553 --
554 -- COMMENT:
555 --
556 -- ======================================================================
557
558 FUNCTION GET_DOCTYPE_SUFFIX(p_auction_id number) RETURN VARCHAR2 IS
559 l_suffix varchar2(2);
560 begin
561
562 SELECT '_' || dt.message_suffix
563 INTO l_suffix
564 FROM pon_auc_doctypes dt,
565 pon_auction_headers_all ah
566 WHERE dt.doctype_id = ah.doctype_id
567 AND ah.auction_header_id = p_auction_id
568 AND rownum =1;
569
570 return l_suffix;
571
572 end GET_DOCTYPE_SUFFIX;
573
574
575 /* ======================================================================
576 * PROCEDURE: UPDATE_AUCTION_IMPORT_COLS PUBLIC
577 * PARAMETERS:
578 * p_auction_id IN auction-number
579 * p_request_id
580 * p_requested_by
581 * p_import_file_name
582 * p_request_date
583 * p_last_update_date
584
585 *
586 * COMMENT: THIS PROCEDURE SHOULD BE INVOKED WHEN A CONCURRENT REQUEST IS
587 * SUCCESSFULLY TRIGERRED FOR IMPORTING A SPEADSHEET IN THE FOLLOWING
588 * SCENARIOS -
589 * ** CREATE A SUPER-LARGE NEGOTIATION VIA SPREADSHEET IMPORT
590 * ** AWARD A SUPER-LARGE NEGOTIATION VIA SPREADSHEET IMPORT
591 *
592 * ====================================================================== */
593
594 PROCEDURE UPDATE_AUCTION_IMPORT_COLS(P_AUCTION_ID IN NUMBER,
595 P_REQUEST_ID IN NUMBER,
596 P_REQUESTED_BY IN NUMBER,
597 P_REQUEST_DATE IN DATE,
598 P_IMPORT_FILE IN VARCHAR2,
599 P_LAST_UPDATE_DATE IN DATE,
600 X_RESULT OUT NOCOPY VARCHAR2,
601 X_ERROR_CODE OUT NOCOPY VARCHAR2,
602 X_ERROR_MESG OUT NOCOPY VARCHAR2)
603
604
605 IS
606
607 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_AUCTION_IMPORT_COLS';
608 l_api_version CONSTANT NUMBER := 1.0;
609 l_last_update_date DATE;
610
611 BEGIN
612
613 x_result := FND_API.g_ret_sts_success;
614
615 IF (g_debug_mode = 'Y'
616 AND FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
617 FND_LOG.string(log_level => FND_LOG.level_statement,
618 module => l_api_name,
619 message => 'BEGIN ::' || p_auction_id || ':::'
620 || p_request_id || ':::' || p_requested_by || ':::'
621 || p_request_date || ':::' || p_import_file || ':::'
622 || p_last_update_date);
623 END IF;
624
625
626 IF( IS_AUCTION_NOT_UPDATED( p_auction_id , p_last_update_date)) THEN
627
628 UPDATE PON_AUCTION_HEADERS_ALL
629 SET
630 REQUEST_ID = p_request_id,
631 REQUESTED_BY = p_requested_by,
632 REQUEST_DATE = p_request_date,
633 IMPORT_FILE_NAME= p_import_file,
634 LAST_UPDATE_DATE= sysdate
635 WHERE
636 AUCTION_HEADER_ID= p_auction_id;
637
638 ELSE
639
640 X_RESULT := FND_API.G_RET_STS_ERROR;
641 X_ERROR_CODE := 'PON_AUCTION_UPDATED_ALREADY';
642 X_ERROR_MESG := 'Auction ' ||p_auction_id ||' has been updated in another session ' ;
643
644 RETURN;
645
646
647 END IF;
648
649 IF (g_debug_mode = 'Y'
650 AND FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
651 FND_LOG.string(log_level => FND_LOG.level_statement,
652 module => l_api_name,
653 message => 'END ::' || p_auction_id || ':::'
654 || p_request_id || ':::' || p_requested_by || ':::'
655 || p_request_date || ':::' || p_import_file || ':::'
656 || p_last_update_date);
657 END IF;
658
659
660
661 EXCEPTION
662
663 WHEN OTHERS THEN
664 IF (g_debug_mode = 'Y'
665 AND FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
666 FND_LOG.string(log_level => FND_LOG.level_statement,
667 module => l_api_name,
668 message => 'EXCEPTION ::' || p_auction_id || ':::'
669 || p_request_id || ':::' || p_requested_by
670 || ':::' || p_request_date || ':::' || p_import_file || ':::'
671 || p_last_update_date);
672 END IF;
673
674 X_RESULT := FND_API.G_RET_STS_ERROR;
675 X_ERROR_CODE := 'UPDATE_AUCTION_IMPORT_COLS_FAILED_COMPLETELY - ' || SQLCODE;
676 X_ERROR_MESG := 'Unable to do anything with this auction '
677 || p_auction_id || ' '
678 || SUBSTR(SQLERRM, 1, 100);
679
680
681 END UPDATE_AUCTION_IMPORT_COLS;
682
683 -- ======================================================================
684 -- PROCEDURE: IS_AUCTION_NOT_UPDATED PUBLIC
685 -- PARAMETERS:
686 --
687 -- RETURN: BOOLEAN
688 --
689 -- COMMENT:
690 --
691 -- ======================================================================
692
693 FUNCTION IS_AUCTION_NOT_UPDATED (p_auction_header_id IN NUMBER,
694 p_last_update_date IN DATE)
695 RETURN BOOLEAN IS
696
697 l_current_update_date DATE;
698 l_return_value BOOLEAN;
699
700 BEGIN
701
702 -- we are being optimistic here, the name of the function is negative though
703 -- more often than not, we shud return true
704
705 l_return_value := TRUE;
706
707 SELECT last_update_date
708 INTO l_current_update_date
709 FROM pon_auction_headers_all
710 WHERE auction_header_id = p_auction_header_id;
711
712 IF (l_current_update_date = p_last_update_date) THEN
713 l_return_value := TRUE;
714 ELSE
715 l_return_value := FALSE;
716 END IF;
717
718 return l_return_value;
719
720 END IS_AUCTION_NOT_UPDATED;
721
722 -- ======================================================================
723 -- PROCEDURE: PURGE_INTERFACE_ERRORS_CP PUBLIC
724 -- PARAMETERS:
725 --
726 -- RETURN: ERRBUF - CONTAINS POSSIBLE ERROR MESSAGES
727 -- RETCODE - RETURN CODE. 0 INDICATES SUCCESS
728 --
729 -- COMMENT: PURGE RECORDS FROM INTERFACE ERROR TABLE IF THE EXPIRATION_DATE
730 -- IS OLDER THAN THE CURRENT DATE
731 --
732 -- ======================================================================
733 PROCEDURE purge_interface_errors_cp
734 (errbuf OUT NOCOPY VARCHAR2,
735 retcode OUT NOCOPY VARCHAR2)
736 IS
737
738 BEGIN
739
740 retcode := '0';
741 errbuf := '';
742
743 BEGIN
744
745 /*
746 Delete all the errors in the interface table that have
747 expiration date marked as less than the current date
748
749 */
750 delete from pon_interface_errors
751 where trunc(expiration_date) <= trunc(sysdate);
752
753 /*
754
755 Delete all the rows in the summary table used to store
756 auto-award recommendation - mapping to auctions that haven't
757 been updated in the past seven days (this is just a catch-all
758 to avoid this temporary table from growing too much)
759
760 please refer to bug 4947500 for further details
761
762 */
763
764 delete from pon_auction_summary
765 where auction_id in (select auction_header_id
766 from pon_auction_headers_all
767 where last_update_date < sysdate - 7);
768
769 retCode := '0';
770 errbuf := 'PURGE_INTERFACE_ERRORS_CP exited successfully';
771
772 EXCEPTION
773 WHEN OTHERS THEN
774 retCode := '2';
775 errbuf := 'PURGE_INTERFACE_ERRORS_CP exited with errors:' || SQLERRM;
776 END;
777
778 COMMIT;
779
780 END purge_interface_errors_cp;
781
782
783
784 -- ======================================================================
785 -- PROCEDURE: is_super_large_neg / is_large_neg
786 -- PARAMETERS: p_auction_header_id
787 --
788 -- RETURN: BOOLEAN
789 --
790 -- COMMENT: returns TRUE/FALSE depending on whether the negotiation is large/
791 -- super-large
792 -- ======================================================================
793
794 FUNCTION is_super_large_neg(p_auction_header_id IN NUMBER)
795 RETURN BOOLEAN
796 IS
797 v_num_lines NUMBER;
798 v_tpid NUMBER;
799 v_threshold NUMBER;
800
801 BEGIN
802
803 SELECT number_of_lines, trading_partner_id
804 INTO v_num_lines, v_tpid
805 FROM pon_auction_headers_all
806 WHERE auction_header_id = p_auction_header_id;
807
808 BEGIN
809 SELECT to_number(preference_value)
810 INTO v_threshold
811 FROM PON_PARTY_PREFERENCES
812 WHERE party_id = v_tpid
813 AND app_short_name= 'PON'
814 AND preference_name='CONCURRENT_PROCESS_LINE_START';
815 EXCEPTION
816 WHEN NO_DATA_FOUND THEN
817 --This is the default value. Data will not be
818 --found when the application is run for the first time.
819 v_threshold := g_default_lines_threshold;
820 END;
821
822 IF (v_num_lines > v_threshold) THEN
823 RETURN TRUE;
824 ELSE
825 RETURN FALSE;
826 END IF;
827
828 RETURN FALSE;
829
830 END is_super_large_neg;
831
832
833 FUNCTION is_large_neg(p_auction_header_id IN NUMBER)
834 RETURN BOOLEAN
835 IS
836 v_large_neg_flag VARCHAR2(1);
837
838 BEGIN
839
840 SELECT large_neg_enabled_flag
841 INTO v_large_neg_flag
842 FROM pon_auction_headers_all
843 WHERE auction_header_id = p_auction_header_id;
844
845 IF (v_large_neg_flag = 'Y') THEN
846 RETURN TRUE;
847 ELSE
848 RETURN FALSE;
849 END IF;
850
851 RETURN FALSE;
852
853 END is_large_neg;
854
855 -- ======================================================================
856 -- FUNCTION: IS_AUCTION_COMPLETE PUBLIC
857 -- PARAMETERS:
858 -- p_auc_header_id IN Auction header id for which to check status
859 --
860 -- RETURN: VARCHAR2 Y/N if the auction is incomplete
861 --
862 -- COMMENT: Determines if an auction is complete or not
863 -- It checks the complete flag
864 -- ======================================================================
865 FUNCTION IS_AUCTION_COMPLETE
866 (
867 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE
868 ) RETURN VARCHAR2
869 IS
870 v_complete_flag VARCHAR2(1) := 'N';
871 BEGIN
872
873 SELECT nvl(complete_flag,'Y')
874 INTO v_complete_flag
875 FROM pon_auction_headers_all
876 WHERE auction_header_id = p_auc_header_id;
877
878 RETURN v_complete_flag;
879
880 END IS_AUCTION_COMPLETE;
881
882 -- ======================================================================
883 -- FUNCTION: IS_REQUEST_COMPLETE PUBLIC
884 -- PARAMETERS:
885 -- p_auc_header_id IN Auction header id for which to check request
886 -- status
887 --
888 -- RETURN: VARCHAR2 Y/N if the request for the auction is incomplete
889 --
890 -- COMMENT: Determines if the request for the auction
891 -- is completed or not
892 -- ======================================================================
893 FUNCTION IS_REQUEST_COMPLETE
894 (
895 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE
896 ) RETURN VARCHAR2
897 IS
898 v_request_complete VARCHAR2(1) := 'Y';
899 l_request_id NUMBER;
900 l_phase VARCHAR2(80);
901 l_status VARCHAR2(80);
902 l_devphase VARCHAR2(30);
903 l_devstatus VARCHAR2(30);
904 l_message VARCHAR2(240);
905 l_success BOOLEAN;
906
907 BEGIN
908 SELECT REQUEST_ID INTO l_request_id
909 FROM PON_AUCTION_HEADERS_ALL
910 WHERE AUCTION_HEADER_ID = p_auc_header_id;
911
912 IF (l_request_id IS NULL) THEN
913 v_request_complete := 'Y';
914 ELSE
915 l_success := FND_CONCURRENT.GET_REQUEST_STATUS (
916 REQUEST_ID => l_request_id,
917 APPL_SHORTNAME => 'PON',
918 PROGRAM => NULL,
919 PHASE => l_phase,
920 STATUS => l_status,
921 DEV_PHASE => l_devphase,
922 DEV_STATUS => l_devstatus,
923 MESSAGE => l_message);
924
925 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
926 FND_LOG.string(log_level => FND_LOG.level_statement,
927
928 module => g_module_prefix || 'IS_REQUEST_COMPLETE',
929
930 message => 'l_request_id : '|| l_request_id ||
931 'l_phase : '||l_phase ||
932 'l_status : '||l_status ||
933 'l_devphase : '||l_devphase ||
934 'l_devstatus : '||l_devstatus ||
935 'l_message : '||l_message);
936 END IF;
937
938 IF (NOT l_success) THEN
939 v_request_complete := 'Y';
940 ELSE
941 IF (l_devphase = 'RUNNING' OR l_devphase = 'COMPLETE') THEN
942 v_request_complete := 'Y';
943 ELSE
944 v_request_complete := 'N';
945 END IF;
946 END IF;
947 END IF;
948
949
950 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
951 FND_LOG.string(log_level => FND_LOG.level_statement,
952
953 module => g_module_prefix || 'IS_REQUEST_COMPLETE',
954
955 message => 'returning '|| v_request_complete);
956 END IF;
957
958
959 return v_request_complete;
960
961 END IS_REQUEST_COMPLETE;
962
963 -- ======================================================================
964 -- PROCEDURE: delete_bid_by_header
965 -- PARAMETERS:
966 -- p_auc_header_id IN auction header id of negotiation
967 -- p_bid_number IN bid number to delete
968 -- P_doc_type IN document type of negotiation
969 --
970 -- COMMENT: Completely deletes a bid from the database including bid_headers
971 -- This procedure is called from online to delete bid complete including
972 -- bid headers.
973 -- ======================================================================
974 PROCEDURE delete_bid_by_header
975 (
976 p_auc_header_id IN pon_bid_headers.auction_header_id%TYPE,
977 p_bid_number IN pon_bid_headers.bid_number%TYPE,
978 P_doc_type IN varchar2,
979 x_msg_count OUT NOCOPY NUMBER,
980 x_return_status OUT NOCOPY VARCHAR2,
981 x_msg_data OUT NOCOPY VARCHAR2
982 ) IS
983 l_conterms_exist_flag varchar2(1) := 'N';
984 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
985
986 PON_FAIL_CALL_DEL_DOC EXCEPTION;
987 BEGIN
988 -- Initialize API return status to SUCCESS
989 x_return_status := FND_API.G_RET_STS_SUCCESS;
990
991 -- Delete related records from tables for this bid
992 pon_large_auction_util_pkg.delete_bid( p_auc_header_id, p_bid_number);
993
994
995 -- Delete contract terms
996 select conterms_exist_flag into l_conterms_exist_flag
997 from pon_auction_headers_all
998 where auction_header_id = p_auc_header_id;
999
1000 if ( ( PON_CONTERMS_UTL_GRP.is_contracts_installed()= FND_API.G_TRUE ) and
1001 l_conterms_exist_flag = 'Y' ) then
1002
1003 OKC_TERMS_UTIL_GRP.delete_doc (
1004 p_api_version => 1.0,
1005 p_init_msg_list => FND_API.G_FALSE,
1006 p_commit => FND_API.G_FALSE,
1007 x_return_status => l_return_status,
1008 x_msg_count => x_msg_count,
1009 x_msg_data => x_msg_data,
1010 p_validate_commit => null,
1011 p_validation_string => null,
1012 p_doc_type => P_doc_type,
1013 p_doc_id => p_bid_number );
1014
1015 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1016 raise PON_FAIL_CALL_DEL_DOC;
1017 end if;
1018 end if;
1019
1020 -- Delete bid header attachment
1021 PON_LARGE_AUCTION_UTIL_PKG.delete_bid_header_attachment
1022 (
1023 p_auc_header_id => p_auc_header_id,
1024 p_bid_number => p_bid_number
1025 );
1026
1027 -- Delete bid header
1028 delete from pon_bid_headers where bid_number = p_bid_number;
1029
1030 EXCEPTION
1031 WHEN PON_FAIL_CALL_DEL_DOC THEN
1032 x_return_status := FND_API.G_RET_STS_ERROR ;
1033
1034 if FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL then
1035 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'PON.PLSQL.PON_LARGE_AUCTION_UTIL_PKG.delete_bid_from_header', 'PON_FAIL_CALL_DEL_DOC');
1036 end if;
1037 WHEN OTHERS THEN
1038
1039 x_return_status := FND_API.G_RET_STS_ERROR;
1040
1041 if FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL then
1042 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'PON.PLSQL.PON_LARGE_AUCTION_UTIL_PKG.delete_bid_from_header', 'Others:' || substr(1, 255, sqlerrm) );
1043 end if;
1044 END delete_bid_by_header;
1045
1046 -- Delete bid header attachment
1047 PROCEDURE delete_bid_header_attachment
1048 (
1049 p_auc_header_id IN pon_bid_headers.auction_header_id%TYPE,
1050 p_bid_number IN pon_bid_headers.bid_number%TYPE
1051
1052 ) IS
1053 BEGIN
1054
1055 -- Delete bid header attachment
1056 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
1057 (x_entity_name => 'PON_BID_HEADERS',
1058 x_pk1_value => p_auc_header_id,
1059 x_pk2_value => p_bid_number,
1060 x_delete_document_flag => 'Y');
1061
1062 EXCEPTION
1063 WHEN OTHERS THEN
1064 null;
1065 END;
1066
1067 END PON_LARGE_AUCTION_UTIL_PKG;