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