1 PACKAGE BODY pon_exhibits_pkg AS
2 /* $Header: PON_EXHIBITS_PKG.plb 120.9.12020000.8 2013/03/25 09:52:00 hvutukur noship $ */
3 g_debug_mode VARCHAR2(10) := 'Y';
4 g_module_prefix CONSTANT VARCHAR2(40) := 'pon.plsql.pon_exhibits_pkg.';
5 EXHIBITS_EXHAUSTED EXCEPTION;
6
7 /* This api will SYNC the data in Items table with Exhibit Details table
8 * This will be called only when multiple lines are inserted/Deleted
9 */
10 PROCEDURE HANDLE_AUCTION_EXHIBIT_DETAILS(p_auction_header_id NUMBER)
11 IS
12 p_EXHIBIT_NUMBER VARCHAR2(2);
13 p_CREATED_BY NUMBER;
14 p_LAST_UPDATED_BY NUMBER;
15 BEGIN
16
17 IF(IS_EXHIBITS_ENABLED_FOR_AUC(p_auction_header_id) = 'N') THEN
18 RETURN;
19 END IF;
20
21
22 BEGIN
23
24 DELETE FROM pon_auction_exhibit_details WHERE auction_header_id=p_auction_header_id
25 AND IS_CDRL = 'N'
26 AND exhibit_number in
27 (SELECT exhibits.exhibit_number FROM pon_auction_exhibit_details exhibits WHERE exhibits.auction_header_id=p_auction_header_id
28 AND IS_CDRL='N'
29 AND exhibit_number NOT IN (SELECT exhibit_number FROM pon_auction_item_prices_ALL items
30 WHERE items.auction_header_id=p_auction_header_id AND items.exhibit_number=exhibits.exhibit_number));
31
32 EXCEPTION
33 WHEN NO_DATA_FOUND THEN
34 NULL;
35
36 END;
37
38 SELECT created_by,last_updated_by
39 INTO
40 p_CREATED_BY,p_LAST_UPDATED_BY
41 FROM pon_auction_headers_all
42 WHERE auction_header_id=p_auction_header_id;
43
44 begin
45
46 /* SELECT exhibit_number
47 INTO p_EXHIBIT_NUMBER
48 FROM pon_auction_item_prices_ALL items
49 WHERE items.auction_header_id=p_auction_header_id
50 AND items.exhibit_number IS NOT NULL
51 AND NOT EXISTS
52 (SELECT exhibit_number FROM pon_auction_exhibit_details exhibits
53 WHERE auction_header_id=p_auction_header_id AND IS_CDRL='N' AND items.exhibit_number=exhibits.exhibit_number)
54 AND ROWNUM=1;*/
55
56 INSERT INTO pon_auction_exhibit_details
57 (AUCTION_HEADER_ID,
58 EXHIBIT_NUMBER,
59 IS_CDRL,
60 ASSOCIATED_TO_LINE,
61 EXHIBIT_DESCRIPTION,
62 CREATION_DATE,
63 CREATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATED_BY) (
66 SELECT
67 p_auction_header_id,
68 exhibit_number,
69 'N',
70 NULL,
71 NULL,
72 SYSDATE,
73 p_CREATED_BY,
74 SYSDATE,
75 p_LAST_UPDATED_BY
76 FROM pon_auction_item_prices_ALL items
77 WHERE items.auction_header_id=p_auction_header_id
78 AND items.exhibit_number IS NOT NULL
79 AND NOT EXISTS
80 (SELECT exhibit_number FROM pon_auction_exhibit_details exhibits
81 WHERE auction_header_id=p_auction_header_id AND IS_CDRL='N' AND items.exhibit_number=exhibits.exhibit_number)
82 GROUP BY exhibit_number);
83
84 EXCEPTION
85
86 WHEN NO_DATA_FOUND THEN
87 NULL;
88 END;
89
90
91 END ;
92
93 /* This api will Insert data into Exhibit Details table
94 * This will be called when single exhibit line is created/Modified
95 */
96 PROCEDURE CREATE_EXHIBIT_DETAILS(p_auction_header_id NUMBER,p_old_exhibit_number varchar2,p_exhibit_number varchar2)
97 IS
98 number_of_exhibits NUMBER;
99 number_of_lines NUMBER;
100 p_CREATED_BY NUMBER;
101 p_LAST_UPDATED_BY NUMBER;
102
103 BEGIN
104 IF(IS_EXHIBITS_ENABLED_FOR_AUC(p_auction_header_id) = 'N') THEN
105 RETURN;
106 END IF;
107
108 BEGIN
109
110 SELECT Count(*)
111 INTO number_of_exhibits
112 FROM pon_auction_exhibit_details
113 WHERE auction_header_id=p_auction_header_id
114 AND exhibit_number=p_exhibit_number
115 AND IS_CDRL='N';
116
117 SELECT Count(*)
118 INTO number_of_lines
119 FROM pon_auction_item_prices_all
120 WHERE auction_header_id=p_auction_header_id
121 AND exhibit_number=p_exhibit_number
122 AND ROWNUM=1;
123
124
125 IF(number_of_exhibits=0 AND number_of_lines>0) THEN
126 INSERT INTO pon_auction_exhibit_details
127 (AUCTION_HEADER_ID,
128 EXHIBIT_NUMBER,
129 IS_CDRL,
130 CREATION_DATE,
131 LAST_UPDATE_DATE
132 )
133 values (p_auction_header_id,p_exhibit_number,'N',SYSDATE,SYSDATE);
134
135 END IF;
136
137 EXCEPTION
138 WHEN No_Data_Found THEN
139 NULL;
140 END;
141
142 BEGIN
143 IF(p_old_exhibit_number IS NOT NULL) then
144 DELETE_EXHIBIT_DETAILS(p_auction_header_id ,p_old_exhibit_number);
145 END IF;
146
147 EXCEPTION
148 WHEN No_Data_Found THEN
149 NULL;
150 END;
151
152
153
154 END;
155
156 /* This api will check if exhibits are enabled for org or not?
157 * This will return "Y" : only if federal and exhibits are enabled.
158 */
159 FUNCTION IS_EXHIBITS_ENABLED( p_organization_id IN NUMBER,l_doc_type_id IN NUMBER)
160 RETURN VARCHAR IS
161
162 l_are_exhibits_enabled VARCHAR2(1);
163 BEGIN
164
165 l_are_exhibits_enabled := 'N';
166 IF (PON_CLM_UTIL_PKG.IS_DOCUMENT_FEDERAL(p_doc_type_id => l_doc_type_id)) <> 1 THEN
167 RETURN l_are_exhibits_enabled;
168 END IF;
169 l_are_exhibits_enabled := Nvl(po_core_s.Retrieveoptionvalue(p_organization_id, po_core_s.g_exhibits_lines_col), 'N');
170
171 RETURN l_are_exhibits_enabled;
172
173 EXCEPTION
174 WHEN OTHERS THEN
175 RETURN 'N';
176
177 END IS_EXHIBITS_ENABLED;
178
179 /* This api will Delete data from Exhibit Details table
180 * This will be called when single exhibit line is deleted/Modified
181 */
182 PROCEDURE DELETE_EXHIBIT_DETAILS(p_auction_header_id NUMBER,p_exhibit_number varchar2)
183 IS
184 number_of_lines NUMBER;
185
186 BEGIN
187 IF(IS_EXHIBITS_ENABLED_FOR_AUC(p_auction_header_id) = 'N') THEN
188 RETURN;
189 END IF;
190
191 SELECT Count(*)
192 INTO number_of_lines
193 FROM pon_auction_item_prices_all
194 WHERE auction_header_id=p_auction_header_id
195 AND exhibit_number=p_exhibit_number
196 AND ROWNUM=1;
197
198
199 IF(number_of_lines=0) then
200 DELETE from pon_auction_exhibit_details
201 WHERE auction_header_id=p_auction_header_id
202 AND exhibit_number=p_exhibit_number
203 AND IS_CDRL='N';
204
205 END IF;
206
207 EXCEPTION
208
209 WHEN No_Data_Found THEN
210 NULL;
211
212 END;
213 /* This api is used when CLINs are deleted in a Solicitation
214 * If there are exhibits linked to the deleted CLIN, those links will be removed by this procedure
215 * p_line_number is used based on single clin delete or multiple clin delete
216 */
217 PROCEDURE UPDATE_EXHIBIT_DETAILS(p_auction_header_id NUMBER,p_line_number NUMBER)
218 IS
219 BEGIN
220 IF(IS_EXHIBITS_ENABLED_FOR_AUC(p_auction_header_id) = 'N') THEN
221 RETURN;
222 END IF;
223 IF(p_line_number IS NOT NULL) THEN
224 UPDATE pon_auction_exhibit_details
225 SET ASSOCIATED_TO_LINE=NULL
226 WHERE AUCTION_HEADER_ID=p_auction_header_id
227 AND ASSOCIATED_TO_LINE = p_line_number;
228
229 else
230 UPDATE pon_auction_exhibit_details
231 SET ASSOCIATED_TO_LINE=NULL
232 WHERE AUCTION_HEADER_ID=p_auction_header_id
233 AND ASSOCIATED_TO_LINE
234 NOT IN (SELECT line_number FROM pon_auction_item_prices_all WHERE AUCTION_HEADER_ID=p_auction_header_id);
235 END IF;
236
237
238
239
240 END;
241
242 /* This api is to Copy Exhibits
243 */
244 PROCEDURE COPY_EXHIBITS(
245 p_auction_header_id NUMBER,
246 p_doctype_id NUMBER,
247 p_user_id NUMBER,
248 p_exhibit_number VARCHAR2,
249 p_is_cdrl VARCHAR2)
250 IS
251
252 p_msg_data VARCHAR2(1000);
253 p_msg_count NUMBER;
254 p_return_status VARCHAR2(10);
255 l_exhibit_tbl okc_cdrl_pvt.exhibit_tbl_type;
256 p_new_exhibit_number VARCHAR2(2);
257 --Holds numbers of deliverables in passed in exhibit
258 l_cdrl_count NUMBER;
259 BEGIN
260
261
262 IF(p_is_cdrl = 'N') THEN
263
264 COPY_ELINS(p_auction_header_id,p_doctype_id,p_user_id,p_exhibit_number);
265
266 ELSIF(p_is_cdrl = 'Y') THEN
267
268 --Get number of deliverables in an exhibit and use it to get next exhibit number.
269 select count(1) INTO l_cdrl_count
270 FROM okc_deliverables
271 where business_document_id = p_auction_header_id
272 and business_document_version = -99
273 and del_category_code = 'CDRL'
274 and exhibit_code = p_exhibit_number;
275
276 p_new_exhibit_number := GET_NEXT_EXHIBIT(p_auction_header_id,p_exhibit_number,l_cdrl_count);
277
278 OKC_CDRL_PVT.copy_cdrl_for_exhibit
279 (p_api_version => 1.0,
280 p_init_msg_list => FND_API.G_TRUE,
281 p_commit => FND_API.G_FALSE,
282 --p_doc_class => 'SOURCING',
283 p_doc_type => 'SOLICITATION',
284 p_doc_id => p_auction_header_id,
285 p_doc_version => NULL,
286 p_mode => NULL,
287 p_src_exhibit => p_exhibit_number,
288 p_target_exhibit => p_new_exhibit_number,
289 --p_exhibit_tbl => l_exhibit_tbl,
290 x_msg_data => p_msg_data,
291 x_msg_count => p_msg_count,
292 x_return_status => p_return_status);
293
294 INSERT_CDRL_EXHIBIT_DETAILS(p_auction_header_id,p_new_exhibit_number,p_return_status,p_msg_data);
295
296 END IF;
297
298
299 END;
300
301
302 /* This api is to Delete Exhibits
303 */
304 PROCEDURE DELETE_EXHIBITS(
305 p_auction_header_id NUMBER,
306 p_doctype_id NUMBER,
307 p_user_id NUMBER,
308 p_exhibit_number VARCHAR2,
309 p_is_cdrl VARCHAR2)
310 IS
311
312 p_msg_data VARCHAR2(1000);
313 p_msg_count NUMBER;
314 p_return_status VARCHAR2(10);
315 l_exhibit_tbl okc_cdrl_pvt.exhibit_tbl_type;
316 p_new_exhibit_number VARCHAR2(2);
317 BEGIN
318
319
320 IF(p_is_cdrl = 'N') THEN
321
322 DELETE_ELINS(p_auction_header_id,p_doctype_id,p_user_id,p_exhibit_number);
323
324 ELSIF(p_is_cdrl = 'Y') THEN
325
326 l_exhibit_tbl(1):= p_exhibit_number;
327 OKC_CDRL_PVT.delete_cdrl_for_exhibits
328 (p_api_version => 1.0,
329 p_init_msg_list => FND_API.G_TRUE,
330 p_commit => FND_API.G_FALSE,
331 --p_doc_class => 'SOURCING',
332 p_doc_type => 'SOLICITATION',
333 p_doc_id => p_auction_header_id,
334 p_doc_version => NULL,
335 p_mode => NULL,
336 p_exhibit_tbl => l_exhibit_tbl,
337 x_msg_data => p_msg_data,
338 x_msg_count => p_msg_count,
339 x_return_status => p_return_status);
340
341 DELETE_CDRL_EXHIBIT_DETAILS(p_auction_header_id,p_exhibit_number,p_return_status);
342
343 END IF;
344
345
346 END;
347
348
349 FUNCTION GET_NEXT_EXHIBIT(auction_header_id IN NUMBER,old_exhibit_number IN varchar2, lines IN NUMBER)
350 RETURN varchar
351 IS
352 p_new_exhibit_number VARCHAR2(2);
353 p_source_auction_header_id NUMBER := auction_header_id;
354 Begin
355 IF(lines>1155 OR lines IS null) THEN
356
357 SELECT lookup_code
358 INTO p_new_exhibit_number
359 FROM (select lookup_code
360 from fnd_lookup_values
361 where lookup_type = 'PO_CLM_EXHIBIT_NUMBER'
362 AND Length(lookup_code) = 1
363 AND lookup_code NOT IN
364 (SELECT exhibit_number FROM pon_auction_exhibit_details WHERE auction_header_id=p_source_auction_header_id)
365 ORDER BY lookup_code)
366 WHERE ROWNUM=1;
367
368 ELSE
369 SELECT lookup_code
370 INTO p_new_exhibit_number
371 FROM (select lookup_code
372 from fnd_lookup_values
373 where lookup_type = 'PO_CLM_EXHIBIT_NUMBER'
374 AND lookup_code NOT IN
375 (SELECT exhibit_number FROM pon_auction_exhibit_details WHERE auction_header_id=p_source_auction_header_id)
376 ORDER BY length(lookup_code), lookup_code)
377 WHERE ROWNUM=1;
378 END IF;
379
380 RETURN p_new_exhibit_number;
381
382 EXCEPTION
383 WHEN No_Data_Found THEN
384 RAISE EXHIBITS_EXHAUSTED;
385 RETURN NULL;
386 END GET_NEXT_EXHIBIT;
387
388
389
390
391 PROCEDURE COPY_ELINS(
392 p_auction_header_id NUMBER,
393 p_doctype_id NUMBER,
394 p_user_id NUMBER,
395 p_exhibit_number VARCHAR2)
396 IS
397 -- l_copy_close_bidding_date varchar2(1);
398 p_new_exhibit_number VARCHAR2(2);
399 p_source_auction_header_id NUMBER := p_auction_header_id;
400 P_LINE_NUMBER NUMBER;
401 P_NO_OF_LINES NUMBER;
402 p_lines PO_TBL_NUMBER;
403 p_line_numbers PO_TBL_NUMBER;
404 p_line_num_displays PO_TBL_VARCHAR30;
405 p_initial_line_number NUMBER;
406 p_final_line_number NUMBER;
407 p_max_line_number NUMBER;
408 p_result VARCHAR2(100);
409 p_error_code VARCHAR2(1000);
410 p_error_message VARCHAR2(1000);
411
412 BEGIN
413 IF(IS_EXHIBITS_ENABLED_FOR_AUC(p_auction_header_id) = 'N') THEN
414 RETURN;
415 END IF;
416
417 LOG_MESSAGE('copy_lines','IS_EXHIBITS_ENABLED_FOR_AUC:'||'Y' );
418
419 SELECT line_number
420 BULK COLLECT INTO p_lines
421 FROM pon_auction_item_prices_all
422 WHERE auction_header_id = p_auction_header_id
423 AND exhibit_number = p_exhibit_number;
424
425 LOG_MESSAGE('copy_lines','After Collecting the line number of the exhibit Lines' );
426
427 p_new_exhibit_number:= GET_NEXT_EXHIBIT(p_auction_header_id,p_exhibit_number,p_lines.COUNT);
428
429 LOG_MESSAGE('copy_lines','New Exhibit Generated:'||p_new_exhibit_number );
430
431 SELECT Max(line_number)
432 INTO p_initial_line_number
433 FROM pon_auction_item_prices_all WHERE
434 auction_header_id = p_auction_header_id;
435
436 LOG_MESSAGE('copy_lines','p_initial_line_number:'||p_initial_line_number );
437
438 pon_clm_clo_util_pkg.copy_lines(p_auction_header_id,p_lines,NULL,p_max_line_number,
439 p_result,p_error_code,p_error_message);
440
441 SELECT Max(line_number)
442 INTO p_final_line_number
443 FROM pon_auction_item_prices_all WHERE
444 auction_header_id = p_auction_header_id;
445
446
447 LOG_MESSAGE('copy_lines','p_final_line_number:'||p_final_line_number );
448
449 SELECT X,p_new_exhibit_number||Decode(Length(p_new_exhibit_number)
450 +Length(pon_clo_renumber_pkg.DECIMAL_TO_ELIN(X)),4,'',3,'0',2,'00')
451 ||pon_clo_renumber_pkg.DECIMAL_TO_ELIN(X) AS NEXT_ELIN_SET
452 BULK COLLECT INTO p_line_numbers,p_line_num_displays
453 FROM
454 (SELECT LEVEL AS X
455 FROM dual
456 CONNECT BY LEVEL <=p_final_line_number-p_initial_line_number+1);
457
458
459 FORALL i IN 1..p_line_num_displays.count
460 UPDATE pon_auction_item_prices_all
461 SET exhibit_number = p_new_exhibit_number,
462 line_num_display=p_line_num_displays(i)
463 WHERE auction_header_id = p_auction_header_id
464 AND line_number=p_line_numbers(i)+p_initial_line_number;
465
466
467 LOG_MESSAGE('copy_lines','Create Exhibit Details record' );
468
469 CREATE_EXHIBIT_DETAILS(p_source_auction_header_id,null,p_new_exhibit_number);
470
471 LOG_MESSAGE('copy_lines','Copy Lines Completed' );
472
473 SELECT NVL(MAX(line_number),0)
474 INTO P_LINE_NUMBER
475 FROM pon_auction_item_prices_all
476 WHERE auction_header_id = p_source_auction_header_id;
477
478 SELECT Count(*)
479 INTO P_NO_OF_LINES
480 FROM pon_auction_item_prices_all
481 WHERE auction_header_id = p_source_auction_header_id;
482
483 UPDATE PON_AUCTION_HEADERS_ALL
484 SET NUMBER_OF_LINES = P_NO_OF_LINES,
485 LAST_LINE_NUMBER = P_LINE_NUMBER
486 WHERE AUCTION_HEADER_ID = p_source_auction_header_id;
487
488 EXCEPTION
489
490 WHEN EXHIBITS_EXHAUSTED THEN
491 Raise_Application_Error (-20900, 'PON_EXHIBITS_EXHAUSTED');
492
493 END;
494
495 PROCEDURE DELETE_ELINS(
496 p_auction_header_id NUMBER,
497 p_doctype_id NUMBER,
498 p_user_id NUMBER,
499 p_exhibit_number VARCHAR2)
500 IS
501 p_source_auction_header_id NUMBER := p_auction_header_id;
502 P_LINE_NUMBER NUMBER;
503 P_NO_OF_LINES NUMBER;
504 p_lines PO_TBL_NUMBER;
505 p_max_line_number NUMBER;
506 p_result VARCHAR2(100);
507 p_error_code VARCHAR2(1000);
508 p_error_message VARCHAR2(1000);
509 p_number_of_lines_deleted NUMBER;
510 BEGIN
511 IF(IS_EXHIBITS_ENABLED_FOR_AUC(p_auction_header_id) = 'N') THEN
512 RETURN;
513 END IF;
514
515 LOG_MESSAGE('delete_lines','IS_EXHIBITS_ENABLED_FOR_AUC:'||'Y' );
516
517 SELECT line_number
518 BULK COLLECT INTO p_lines
519 FROM pon_auction_item_prices_all
520 WHERE auction_header_id = p_auction_header_id
521 AND exhibit_number = p_exhibit_number;
522
523 LOG_MESSAGE('delete_lines','After Collecting the line number of the exhibit Lines' );
524
525
526 FOR i IN 1..p_lines.Count LOOP
527 pon_clm_clo_util_pkg.delete_single_line
528 (p_auction_header_id=> p_auction_header_id,
529 p_line_number => p_lines(i),
530 x_result => p_result,
531 x_error_code => p_error_code,
532 x_error_message => p_error_message,
533 x_number_of_lines_deleted => p_number_of_lines_deleted);
534 END LOOP;
535
536 LOG_MESSAGE('delete_lines','Number of Lines Deleted'||p_number_of_lines_deleted);
537
538 LOG_MESSAGE('delete_lines','Remove Exhibit Details record' );
539
540 DELETE_EXHIBIT_DETAILS(p_source_auction_header_id,p_exhibit_number);
541
542 LOG_MESSAGE('delete_lines','Delete Lines Completed' );
543
544 SELECT NVL(MAX(line_number),0)
545 INTO P_LINE_NUMBER
546 FROM pon_auction_item_prices_all
547 WHERE auction_header_id = p_source_auction_header_id;
548
549 SELECT Count(*)
550 INTO P_NO_OF_LINES
551 FROM pon_auction_item_prices_all
552 WHERE auction_header_id = p_source_auction_header_id;
553
554 UPDATE PON_AUCTION_HEADERS_ALL
555 SET NUMBER_OF_LINES = P_NO_OF_LINES,
556 LAST_LINE_NUMBER = P_LINE_NUMBER
557 WHERE AUCTION_HEADER_ID = p_source_auction_header_id;
558
559 EXCEPTION
560
561 WHEN OTHERS THEN
562 LOG_MESSAGE('delete_lines','Exception in Delete Lines' );
563
564 END;
565
566 PROCEDURE LOG_MESSAGE( p_module IN VARCHAR2, p_message IN VARCHAR2)
567 IS
568 BEGIN
569 IF (g_debug_mode = 'Y') THEN
570 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
571 FND_LOG.string(log_level => FND_LOG.level_statement,
572 module => g_module_prefix || p_module,
573 message => p_message);
574 END IF;
575 END IF;
576
577 -- insert into s_temp values(p_module||' , '|| p_message);
578 END LOG_MESSAGE;
579
580 FUNCTION HAS_ELIN_ASSOC_FOR_LINE(p_auction_header_id NUMBER, p_line_number IN NUMBER)
581 RETURN VARCHAR2 IS
582
583 l_elin_count NUMBER;
584
585 BEGIN
586 SELECT Count(*)
587 INTO l_elin_count
588 FROM pon_auction_exhibit_details
589 WHERE auction_header_id = p_auction_header_id
590 AND associated_to_line = p_line_number
591 AND Nvl(is_cdrl, 'N') = 'N';
592
593 IF l_elin_count <> 0 THEN
594 RETURN 'Y';
595 ELSE
596 RETURN 'N';
597 END IF;
598
599 END HAS_ELIN_ASSOC_FOR_LINE;
600
601 FUNCTION IS_LINE_ELIN (p_auction_header_id NUMBER, p_line_number IN NUMBER)
602 RETURN VARCHAR2 IS
603
604 l_is_elin VARCHAR2(1);
605 BEGIN
606
607 SELECT Decode(Nvl(exhibit_number, '-1'), '-1' , 'N', 'Y')
608 INTO l_is_elin
609 FROM pon_auction_item_prices_all
610 WHERE auction_header_id = p_auction_header_id
611 AND line_number = p_line_number;
612
613 RETURN l_is_elin;
614
615 END IS_LINE_ELIN;
616
617 FUNCTION IS_EXHIBITS_ENABLED_FOR_AUC(p_auction_header_id NUMBER)
618 RETURN VARCHAR2 IS
619
620 l_org_id pon_auction_headers_all.org_id%TYPE;
621 l_doctype_id pon_auction_headers_all.doctype_id%TYPE;
622
623 BEGIN
624 SELECT org_id, doctype_id
625 INTO l_org_id,l_doctype_id
626 FROM pon_auction_headers_all
627 WHERE auction_header_id = p_auction_header_id;
628
629 RETURN IS_EXHIBITS_ENABLED(l_org_id, l_doctype_id);
630
631 END IS_EXHIBITS_ENABLED_FOR_AUC;
632
633 /* This api will check if cdrls are enabled for org or not?
634 * This will return "Y" : only if federal and cdrls are enabled.
635 */
636 FUNCTION IS_CDRLS_ENABLED( p_organization_id IN NUMBER,l_doc_type_id IN NUMBER)
637 RETURN VARCHAR IS
638
639 l_are_cdrls_enabled VARCHAR2(1);
640 BEGIN
641
642 l_are_cdrls_enabled := 'N';
643 IF (PON_CLM_UTIL_PKG.IS_DOCUMENT_FEDERAL(p_doc_type_id => l_doc_type_id)) <> 1 THEN
644 RETURN l_are_cdrls_enabled;
645 END IF;
646 l_are_cdrls_enabled := Nvl(po_core_s.Retrieveoptionvalue(p_organization_id, po_core_s.g_CONT_DATA_REQS_COL), 'N');
647
648 RETURN l_are_cdrls_enabled;
649
650 EXCEPTION
651 WHEN OTHERS THEN
652 RETURN 'N';
653
654 END IS_CDRLS_ENABLED;
655
656
657 /* This api will Insert data into Exhibit Details table for cdrls.
658 * This will be called from okc code.
659 * If cdrl record already exists : x_return_status = 'S', x_return_msg := 'CDRL_RECORD_EXISTS'
660 * If p_exhibit_number is used by elins : x_return_status = 'U', x_return_msg := 'EXHIBIT_NUM_USED_BY_ELIN'
661 */
662 PROCEDURE INSERT_CDRL_EXHIBIT_DETAILS(p_auction_header_id IN NUMBER,
663 p_exhibit_number IN VARCHAR2,
664 x_return_status OUT NOCOPY VARCHAR2,
665 x_return_msg OUT NOCOPY VARCHAR2)
666 IS
667 l_cdrl_exists VARCHAR2(1);
668 l_user_id fnd_user.user_id%TYPE;
669
670 BEGIN
671
672 --BEGIN
673 l_cdrl_exists := 'N';
674
675 BEGIN
676 SELECT Nvl(is_cdrl, 'E')
677 INTO l_cdrl_exists
678 FROM pon_auction_exhibit_details
679 WHERE auction_header_id=p_auction_header_id
680 AND exhibit_number=p_exhibit_number;
681 EXCEPTION
682 WHEN No_Data_Found THEN
683 NULL;
684 END;
685
686 IF l_cdrl_exists = 'Y' THEN
687 x_return_status := 'S';
688 x_return_msg := 'CDRL_RECORD_EXISTS';
689 RETURN;
690 ELSIF l_cdrl_exists = 'E' THEN
691 x_return_status := 'U';
692 x_return_msg := 'EXHIBIT_NUM_USED_BY_ELIN';
693 RETURN;
694 END IF;
695
696 l_user_id := fnd_global.user_id();
697
698 INSERT INTO pon_auction_exhibit_details
699 (AUCTION_HEADER_ID,
700 EXHIBIT_NUMBER,
701 IS_CDRL,
702 ASSOCIATED_TO_LINE,
703 EXHIBIT_DESCRIPTION,
704 CREATION_DATE,
705 CREATED_BY,
706 LAST_UPDATE_DATE,
707 LAST_UPDATED_BY)
708 VALUES
709 (p_auction_header_id,p_exhibit_number,'Y', NULL, NULL, SYSDATE,l_user_id,SYSDATE,l_user_id);
710
711 x_return_status := 'S';
712
713 END INSERT_CDRL_EXHIBIT_DETAILS;
714
715 /* This api will delete cdrl exhibit record from pon_auction_exhibit_details table.
716 * This api should be called by okc code.
717 */
718 PROCEDURE DELETE_CDRL_EXHIBIT_DETAILS(p_auction_header_id IN NUMBER,
719 p_exhibit_number IN VARCHAR2,
720 x_return_status OUT NOCOPY VARCHAR2)
721
722 IS
723
724 BEGIN
725
726 DELETE FROM pon_auction_exhibit_details
727 WHERE auction_header_id = p_auction_header_id
728 AND exhibit_number = p_exhibit_number
729 AND is_cdrl = 'Y';
730
731 x_return_status := 'S';
732
733 END DELETE_CDRL_EXHIBIT_DETAILS;
734
735
736
737 END PON_EXHIBITS_PKG;
[Home] [Help]