[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PO_EXHIBITS_PVT
Source
1 PACKAGE BODY PO_EXHIBITS_PVT AS
2 /* $Header: PO_EXHIBITS_PVT.plb 120.2.12020000.10 2013/04/18 05:02:58 mabaig noship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_EXHIBITS_PVT');
6
7
8
9
10 --------------------------------------------------------------------------------
11 --Start of Comments
12 --Name: ELIN_TO_DECIMAL
13 -- CLM Phase 4 - Elins project
14 --Function:
15 --This function is a internal procedure used for getting the next exhbit line number
16 --IN:
17 -- linNum VARCHAR2
18 --IN OUT:
19 --OUT:
20 --Notes:
21 --End of Comments
22 --------------------------------------------------------------------------------
23 FUNCTION ELIN_TO_DECIMAL(linNum VARCHAR2) RETURN NUMBER
24 IS
25
26 l_asciiValue NUMBER:=0;
27 temp NUMBER;
28 base_size NUMBER;
29 ret_linNum VARCHAR2(100);
30 c_base32_digits CONSTANT VARCHAR2(34) := '0123456789ABCDEFGHJKLMNPQRSTUVWXYZ';
31
32 BEGIN
33
34 base_size := Length(c_base32_digits);
35
36 FOR i IN 1 .. Length(linNum) loop
37 temp := InStr(c_base32_digits,SubStr(linNum, i, 1))-1;
38 l_asciiValue:=l_asciiValue+temp*Power(base_size,(Length(linNum)-i));
39 END LOOP;
40
41 RETURN l_asciiValue;
42
43 END ;
44
45 --------------------------------------------------------------------------------
46 --Start of Comments
47 --Name: DECIMAL_TO_ELIN
48 -- CLM Phase 4 - Elins project
49 --Function:
50 --This function is a internal procedure used for getting the next exhbit line number
51 --Parameters:
52 --IN:
53 -- elin_dec NUMBER
54 --IN OUT:
55 --OUT:
56 --Notes:
57 --End of Comments
58 --------------------------------------------------------------------------------
59 FUNCTION DECIMAL_TO_ELIN(elin_dec NUMBER) RETURN VARCHAR2
60 IS
61 v_modulo INTEGER;
62 v_temp_int INTEGER := elin_dec;
63 v_temp_val VARCHAR2(256);
64 v_temp_char VARCHAR2(1);
65 c_base32_digits CONSTANT VARCHAR2(34) := '0123456789ABCDEFGHJKLMNPQRSTUVWXYZ';
66
67 BEGIN
68
69 IF ( elin_dec = 0 ) THEN
70 v_temp_val := '0';
71 END IF;
72
73 WHILE ( v_temp_int <> 0 ) LOOP
74 v_modulo := v_temp_int MOD 34;
75 v_temp_char := SUBSTR( c_base32_digits, v_modulo + 1, 1 );
76 v_temp_val := v_temp_char || v_temp_val;
77 v_temp_int := floor(v_temp_int / 34);
78 END LOOP;
79
80 RETURN v_temp_val;
81
82 END ;
83
84
85
86 --------------------------------------------------------------------------------
87 --Start of Comments
88 --Name: NEXT_ELIN_NUM
89 -- CLM Phase 4 - Elins project
90 --Function:
91 --This fucntion will return the next eligible elin (exhibit line number) for the give exhibit of that document.
92 --Parameters:
93 --IN:
94 -- p_assigned_num_array PO_TBL_VARCHAR100 Set of line numbers for the given exhibit
95 -- p_header_id Document Identifier
96 -- p_exhibit_name Exhibit name
97
98 --IN OUT:
99 --OUT:
100 --Notes:
101 --End of Comments
102 --------------------------------------------------------------------------------
103 FUNCTION NEXT_ELIN_NUM (p_assigned_num_array PO_TBL_VARCHAR100, p_header_id NUMBER, p_exhibit_name IN VARCHAR2)
104 RETURN VARCHAR2
105
106 IS
107
108 linNumDisplay VARCHAR2(3);
109 lineNumber NUMBER;
110 exhibit_len NUMBER;
111 lines_tbl_name VARCHAR2(50);
112 l_oth_line_num_arr PO_TBL_VARCHAR100 := PO_TBL_VARCHAR100();
113 l_merged_line_num_arr PO_TBL_VARCHAR100 := PO_TBL_VARCHAR100();
114 d_api_name CONSTANT VARCHAR2(30) := 'NEXT_ELIN_NUM';
115 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
116 d_position NUMBER;
117
118 BEGIN
119
120
121 d_position := 0;
122 IF (PO_LOG.d_proc) THEN
123 PO_LOG.proc_begin(d_module);
124 END IF;
125
126 -- Select Line Nums which have been added by other Modifications
127 SELECT DISTINCT line_num_display
128 BULK COLLECT INTO l_oth_line_num_arr
129 FROM po_lines_merge_v
130 WHERE po_header_id = p_header_id
131 AND clm_exhibit_name = p_exhibit_name
132 AND change_status='NEW'
133 AND status in ('DRAFT','REJECTED','IN PROCESS','PRE-APPROVED');
134
135 d_position := 10;
136 IF (PO_LOG.d_stmt) THEN
137 PO_LOG.stmt(d_module, d_position, 'l_oth_line_num_arr', l_oth_line_num_arr);
138 END IF;
139
140 -- Get a distinct union of current Mod Line Nums and other Mods Line Nums
141 l_merged_line_num_arr := p_assigned_num_array MULTISET UNION DISTINCT
142 l_oth_line_num_arr;
143
144 d_position := 20;
145 IF (PO_LOG.d_stmt) THEN
146 PO_LOG.stmt(d_module, d_position, 'l_merged_line_num_arr', l_merged_line_num_arr);
147 END IF;
148
149 exhibit_len:=Length(p_exhibit_name);
150
151 SELECT Nvl(Max(ROWNUM),0)+1
152 INTO lineNumber
153 FROM
154 (SELECT PO_EXHIBITS_PVT.ELIN_TO_DECIMAL(SubStr(a.column_value,2+1,4-2)) elin_decimal
155 FROM Table(l_merged_line_num_arr) a
156 order by elin_decimal)
157 WHERE elin_decimal=ROWNUM ;
158
159 IF((exhibit_len=2 AND lineNumber>1155) OR (exhibit_len=1 AND lineNumber>39303)) THEN
160 RAISE ELIN_NUMBERS_EXHAUSTED;
161 END IF;
162
163 linNumDisplay:=DECIMAL_TO_ELIN(lineNumber);
164
165 IF(exhibit_len=1) THEN
166 IF(Length(linNumDisplay)=1) THEN
167 linNumDisplay:='00'||linNumDisplay;
168 ELSIF(Length(linNumDisplay)=2) then
169 linNumDisplay:='0'||linNumDisplay;
170 END IF;
171 ELSIF(exhibit_len=2) THEN
172 IF(Length(linNumDisplay)=1) THEN
173 linNumDisplay:='0'||linNumDisplay;
174 END IF;
175 END IF;
176
177 d_position:=30;
178 IF (PO_LOG.d_stmt) THEN
179 PO_LOG.stmt(d_module,d_position ,'Next exhibit line number',linNumDisplay);
180 END IF;
181
182
183 IF (PO_LOG.d_proc) THEN
184 PO_LOG.proc_end(d_module);
185 END IF;
186
187
188 RETURN p_exhibit_name||linNumDisplay;
189
190 EXCEPTION
191 WHEN ELIN_NUMBERS_EXHAUSTED THEN
192 Raise_Application_Error (-20900, 'PON_ELIN_NUMBERS_EXHAUSTED');
193 END;
194
195
196 --------------------------------------------------------------------------------
197 --Start of Comments
198 --Name: GET_NEXT_EXHIBIT
199 -- CLM Phase 4 - Elins project
200 --Function:
201 --This fucntion will return the next eligible exhibit for the given document id.
202 --Parameters:
203 --IN:
204 -- p_header_id Document Identifier
205 -- p_draft_id draft id
206
207 --IN OUT:
208 --OUT:
209 --Notes:
210 --End of Comments
211 --------------------------------------------------------------------------------
212 FUNCTION GET_NEXT_EXHIBIT (p_header_id NUMBER, p_draft_id NUMBER )
213 RETURN VARCHAR2
214 IS
215 l_next_exhibit VARCHAR2(10);
216 BEGIN
217
218 SELECT lookup_code
219 INTO l_next_exhibit
220 FROM
221 (SELECT lookup_code
222 FROM fnd_lookup_values lk
223 WHERE lookup_type = 'PO_CLM_EXHIBIT_NUMBER'
224 AND NOT EXISTS (SELECT 1 FROM po_exhibit_details_merge_v pex
225 WHERE pex.po_header_id = p_header_id
226 AND pex.draft_id = p_draft_id
227 AND pex.exhibit_name = lk.lookup_code)
228 ORDER BY LENGTH(lookup_code),lookup_code
229 ) WHERE ROWNUM = 1;
230
231 RETURN l_next_exhibit;
232
233 END GET_NEXT_EXHIBIT;
234
235
236 -----------------------------------------------------------------------
237 --Start of Comments
238 --Name: lock_draft_record
239 --Function:
240 -- Obtain database lock for the record in draft table
241 --Parameters:
242 --IN:
243 --p_po_exhibit_details_id
244 -- id for po exhibit record
245 --p_draft_id
246 -- draft unique identifier
247 --RETURN:
248 --End of Comments
249 ------------------------------------------------------------------------
250 PROCEDURE lock_draft_record
251 ( p_po_exhibit_details_id IN NUMBER,
252 p_draft_id IN NUMBER
253 ) IS
254
255 d_api_name CONSTANT VARCHAR2(30) := 'lock_draft_record';
256 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
257 d_position NUMBER;
258
259 l_dummy NUMBER;
260
261 BEGIN
262 d_position := 0;
263 IF (PO_LOG.d_proc) THEN
264 PO_LOG.proc_begin(d_module);
265 END IF;
266
267 SELECT 1
268 INTO l_dummy
269 FROM po_exhibit_details_draft
270 WHERE po_exhibit_details_id = p_po_exhibit_details_id
271 AND draft_id = p_draft_id
272 FOR UPDATE NOWAIT;
273
274 IF (PO_LOG.d_proc) THEN
275 PO_LOG.proc_end(d_module);
276 END IF;
277
278 EXCEPTION
279 WHEN NO_DATA_FOUND THEN
280 NULL;
281 END lock_draft_record;
282
283 -----------------------------------------------------------------------
284 --Start of Comments
285 --Name: lock_transaction_record
286 --Function:
287 -- Obtain database lock for the record in transaction table
288 --Parameters:
289 --IN:
290 --p_po_exhibit_details_id
291 -- id for po exibit record
292 --RETURN:
293 --End of Comments
294 ------------------------------------------------------------------------
295 PROCEDURE lock_transaction_record
296 ( p_po_exhibit_details_id IN NUMBER
297 ) IS
298
299 d_api_name CONSTANT VARCHAR2(30) := 'lock_transaction_record';
300 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
301 d_position NUMBER;
302
303 l_dummy NUMBER;
304
305 BEGIN
306 d_position := 0;
307 IF (PO_LOG.d_proc) THEN
308 PO_LOG.proc_begin(d_module);
309 END IF;
310
311 SELECT 1
312 INTO l_dummy
313 FROM po_exhibit_details
314 WHERE po_exhibit_details_id = p_po_exhibit_details_id
315 FOR UPDATE NOWAIT;
316
317 IF (PO_LOG.d_proc) THEN
318 PO_LOG.proc_end(d_module);
319 END IF;
320
321 EXCEPTION
322 WHEN NO_DATA_FOUND THEN
323 NULL;
324 END lock_transaction_record;
325
326
327 -----------------------------------------------------------------------
328 --Start of Comments
329 --Name: sync_draft_from_txn
330 --Pre-reqs: None
331 --Modifies:
332 --Locks:
333 -- None
334 --Function:
335 -- Copy data from transaction table to draft table, if the corresponding
336 -- record in draft table does not exist. It also sets the delete flag of
337 -- the draft record according to the parameter.
338 --Parameters:
339 --IN:
340 --p_po_distribution_id_tbl
341 -- table of po distribution unique identifier
342 --p_draft_id_tbl
343 -- table of draft ids this sync up will be done for
344 --p_delete_flag_tbl
345 -- table fo flags to indicate whether the draft record should be maked as
346 -- "to be deleted"
347 --IN OUT:
348 --OUT:
349 --x_record_already_exist_tbl
350 -- Returns whether the record was already in draft table or not
351 --Returns:
352 --Notes:
353 --Testing:
354 --End of Comments
355 ------------------------------------------------------------------------
356 PROCEDURE sync_draft_from_txn
357 ( p_po_exhibit_details_id_tbl IN PO_TBL_NUMBER,
358 p_draft_id_tbl IN PO_TBL_NUMBER,
359 p_delete_flag_tbl IN PO_TBL_VARCHAR1,
360 x_record_already_exist_tbl OUT NOCOPY PO_TBL_VARCHAR1
361 ) IS
362
363 d_api_name CONSTANT VARCHAR2(30) := 'sync_draft_from_txn';
364 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
365 d_position NUMBER;
366
367 l_distinct_id_list DBMS_SQL.NUMBER_TABLE;
368 l_duplicate_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
369
370 BEGIN
371 d_position := 0;
372 IF (PO_LOG.d_proc) THEN
373 PO_LOG.proc_begin(d_module);
374 END IF;
375
376 x_record_already_exist_tbl :=
377 draft_changes_exist
378 ( p_draft_id_tbl => p_draft_id_tbl,
379 p_po_exhibit_details_id_tbl => p_po_exhibit_details_id_tbl
380 );
381
382 -- bug5471513 START
383 -- If there're duplicate entries in the id table,
384 -- we do not want to insert multiple entries
385 -- Created an associative array to store what id has appeared.
386 l_duplicate_flag_tbl.EXTEND(p_po_exhibit_details_id_tbl.COUNT);
387
388 FOR i IN 1..p_po_exhibit_details_id_tbl.COUNT LOOP
389 IF (x_record_already_exist_tbl(i) = FND_API.G_FALSE) THEN
390
391 IF (l_distinct_id_list.EXISTS(p_po_exhibit_details_id_tbl(i))) THEN
392
393 l_duplicate_flag_tbl(i) := FND_API.G_TRUE;
394 ELSE
395 l_duplicate_flag_tbl(i) := FND_API.G_FALSE;
396
397 l_distinct_id_list(p_po_exhibit_details_id_tbl(i)) := 1;
398 END IF;
399
400 ELSE
401
402 l_duplicate_flag_tbl(i) := NULL;
403
404 END IF;
405 END LOOP;
406 -- bug5471513 END
407
408 d_position := 10;
409 IF (PO_LOG.d_stmt) THEN
410 PO_LOG.stmt(d_module, d_position, 'transfer records from txn to dft');
411 END IF;
412
413 FORALL i IN 1..p_po_exhibit_details_id_tbl.Count
414 INSERT INTO po_exhibit_details_draft
415 (
416 po_exhibit_details_id,
417 po_header_id,
418 draft_id,
419 delete_flag,
420 change_accepted_flag,
421 exhibit_name,
422 exhibit_description,
423 is_cdrl,
424 reference_line_id,
425 revision_num,
426 LAST_UPDATE_DATE,
427 LAST_UPDATED_BY,
428 CREATION_DATE,
429 CREATED_BY,
430 LAST_UPDATE_LOGIN
431 )
432 SELECT
433 po_exhibit_details_id,
434 po_header_id,
435 p_draft_id_tbl(i),
436 p_delete_flag_tbl(i),
437 NULL,
438 exhibit_name,
439 exhibit_description,
440 is_cdrl,
441 reference_line_id,
442 revision_num,
443 LAST_UPDATE_DATE,
444 LAST_UPDATED_BY,
445 CREATION_DATE,
446 CREATED_BY,
447 LAST_UPDATE_LOGIN
448 FROM po_exhibit_details
449 WHERE po_exhibit_details_id = p_po_exhibit_details_id_tbl(i)
450 AND x_record_already_exist_tbl(i) = FND_API.G_FALSE
451 AND l_duplicate_flag_tbl(i) = FND_API.G_FALSE;
452
453 d_position := 20;
454 IF (PO_LOG.d_stmt) THEN
455 PO_LOG.stmt(d_module, d_position, 'transfer count = ' || SQL%ROWCOUNT);
456 END IF;
457
458 FORALL i IN 1..p_po_exhibit_details_id_tbl.COUNT
459 UPDATE po_exhibit_details_draft
460 SET delete_flag = p_delete_flag_tbl(i)
461 WHERE po_exhibit_details_id = p_po_exhibit_details_id_tbl(i)
462 AND draft_id = p_draft_id_tbl(i)
463 AND NVL(delete_flag, 'N') <> 'Y' -- bug5570989
464 AND x_record_already_exist_tbl(i) = FND_API.G_TRUE;
465
466 d_position := 30;
467
468 IF (PO_LOG.d_stmt) THEN
469 PO_LOG.stmt(d_module, d_position, 'update draft records that are already' ||
470 ' in draft table. Count = ' || SQL%ROWCOUNT);
471 END IF;
472
473 d_position := 40;
474
475 IF (PO_LOG.d_proc) THEN
476 PO_LOG.proc_end(d_module);
477 END IF;
478
479 EXCEPTION
480 WHEN OTHERS THEN
481 PO_MESSAGE_S.add_exc_msg
482 ( p_pkg_name => d_pkg_name,
483 p_procedure_name => d_api_name || '.' || d_position
484 );
485 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
486 END sync_draft_from_txn;
487
488 -----------------------------------------------------------------------
489 --Start of Comments
490 --Name: sync_draft_from_txn
491 --Pre-reqs: None
492 --Modifies:
493 --Locks:
494 -- None
495 --Function:
496 -- Same functionality as the bulk version of this procedure
497 --Parameters:
498 --IN:
499 --p_distribution_id
500 -- distribution unique identifier
501 --p_draft_id
502 -- the draft this sync up will be done for
503 --p_delete_flag
504 -- flag to indicate whether the draft record should be maked as "to be
505 -- deleted"
506 --IN OUT:
507 --OUT:
508 --x_record_already_exist
509 -- Returns whether the record was already in draft table or not
510 --Returns:
511 --Notes:
512 --Testing:
513 --End of Comments
514 ------------------------------------------------------------------------
515 PROCEDURE sync_draft_from_txn
516 ( p_po_exhibit_details_id IN NUMBER,
517 p_draft_id IN NUMBER,
518 p_delete_flag IN VARCHAR2,
519 x_record_already_exist OUT NOCOPY VARCHAR2
520 ) IS
521
522 d_api_name CONSTANT VARCHAR2(30) := 'sync_draft_from_txn';
523 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
524 d_position NUMBER;
525
526 l_record_already_exist_tbl PO_TBL_VARCHAR1;
527
528 BEGIN
529 d_position := 0;
530 IF (PO_LOG.d_proc) THEN
531 PO_LOG.proc_begin(d_module);
532 PO_LOG.proc_begin(d_module, 'p_po_exhibit_details_id', p_po_exhibit_details_id);
533 END IF;
534
535 sync_draft_from_txn
536 ( p_po_exhibit_details_id_tbl => PO_TBL_NUMBER(p_po_exhibit_details_id),
537 p_draft_id_tbl => PO_TBL_NUMBER(p_draft_id),
538 p_delete_flag_tbl => PO_TBL_VARCHAR1(p_delete_flag),
539 x_record_already_exist_tbl => l_record_already_exist_tbl
540 );
541
542 x_record_already_exist := l_record_already_exist_tbl(1);
543
544 d_position := 10;
545 IF (PO_LOG.d_proc) THEN
546 PO_LOG.proc_end(d_module);
547 PO_LOG.proc_end(d_module, 'x_record_already_exist', x_record_already_exist);
548 END IF;
549
550 EXCEPTION
551 WHEN OTHERS THEN
552 PO_MESSAGE_S.add_exc_msg
553 ( p_pkg_name => d_pkg_name,
554 p_procedure_name => d_api_name || '.' || d_position
555 );
556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
557 END sync_draft_from_txn;
558
559
560 --Start of Comments
561 --Name: draft_changes_exist
562 --Pre-reqs: None
563 --Modifies:
564 --Locks:
565 -- None
566 --Function:
567 -- Checks whether there is any draft changes in the draft table
568 -- given the draft_id or draft_id + po_exhibit_details_id
569 -- If only draft_id is provided, this program returns FND_API.G_TRUE for
570 -- any draft changes in this table for the draft
571 -- If the whole primary key is provided (draft_id + exhibit id), then
572 -- it return true if there is draft for this particular record in
573 -- the draft table
574 --Parameters:
575 --IN:
576 --p_draft_id_tbl
577 -- draft unique identifier
578 --p_po_exhibit_details_id_tbl
579 -- po exhibit unique identifier
580 --IN OUT:
581 --OUT:
582 --Returns:
583 -- Array of flags indicating whether draft changes exist for the corresponding
584 -- entry in the input parameter. For each entry in the returning array:
585 -- FND_API.G_TRUE if there are draft changes
586 -- FND_API.G_FALSE if there aren't draft changes
587 --Notes:
588 --Testing:
589 --End of Comments
590 ------------------------------------------------------------------------
591 FUNCTION draft_changes_exist
592 ( p_draft_id_tbl IN PO_TBL_NUMBER,
593 p_po_exhibit_details_id_tbl IN PO_TBL_NUMBER
594 ) RETURN PO_TBL_VARCHAR1
595 IS
596 d_api_name CONSTANT VARCHAR2(30) := 'draft_changes_exist';
597 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
598 d_position NUMBER;
599
600 l_key NUMBER;
601 l_index_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
602 l_dft_exists_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
603 l_dft_exists_index_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
604
605 BEGIN
606 d_position := 0;
607 IF (PO_LOG.d_proc) THEN
608 PO_LOG.proc_begin(d_module);
609 END IF;
610
611 l_index_tbl.extend(p_draft_id_tbl.COUNT);
612 l_dft_exists_tbl.extend(p_draft_id_tbl.COUNT);
613
614 FOR i IN 1..l_index_tbl.COUNT LOOP
615 l_index_tbl(i) := i;
616 l_dft_exists_tbl(i) := FND_API.G_FALSE;
617 END LOOP;
618
619 d_position := 10;
620
621 l_key := PO_CORE_S.get_session_gt_nextval;
622
623 d_position := 20;
624
625 FORALL i IN 1..p_draft_id_tbl.COUNT
626 INSERT INTO po_session_gt
627 ( key,
628 num1
629 )
630 SELECT l_key,
631 l_index_tbl(i)
632 FROM DUAL
633 WHERE EXISTS (SELECT 1
634 FROM po_exhibit_details_draft PDD
635 WHERE PDD.draft_id = p_draft_id_tbl(i)
636 AND PDD.po_exhibit_details_id =
637 NVL(p_po_exhibit_details_id_tbl(i),
638 PDD.po_exhibit_details_id)
639 AND NVL(PDD.change_accepted_flag, 'Y') = 'Y');
640
641
642 d_position := 30;
643
644 -- All the num1 returned from this DELETE statement are indexes for
645 -- records that contain draft changes
646 DELETE FROM po_session_gt
647 WHERE key = l_key
648 RETURNING num1
649 BULK COLLECT INTO l_dft_exists_index_tbl;
650
651 d_position := 40;
652
653 FOR i IN 1..l_dft_exists_index_tbl.COUNT LOOP
654 l_dft_exists_tbl(l_dft_exists_index_tbl(i)) := FND_API.G_TRUE;
655 END LOOP;
656
657 IF (PO_LOG.d_stmt) THEN
658 PO_LOG.stmt(d_module, d_position, '# of records that have dft changes',
659 l_dft_exists_index_tbl.COUNT);
660 END IF;
661
662 RETURN l_dft_exists_tbl;
663
664 EXCEPTION
665 WHEN OTHERS THEN
666 PO_MESSAGE_S.add_exc_msg
667 ( p_pkg_name => d_pkg_name,
668 p_procedure_name => d_api_name || '.' || d_position
669 );
670 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
671 END draft_changes_exist;
672
673 -----------------------------------------------------------------------
674 --Start of Comments
675 --Name: draft_changes_exist
676 --Pre-reqs: None
677 --Modifies:
678 --Locks:
679 -- None
680 --Function:
681 -- Same functionality as the bulk version of draft_changes_exist
682 --Parameters:
683 --IN:
684 --p_draft_id
685 -- draft unique identifier
686 --p_po_exhibit_details_id
687 -- exhibit unique identifier
688 --IN OUT:
689 --OUT:
690 --Returns:
691 -- FND_API.G_TRUE if there are draft changes
692 -- FND_API.G_FALSE if there aren't draft changes
693 --Notes:
694 --Testing:
695 --End of Comments
696 ------------------------------------------------------------------------
697 FUNCTION draft_changes_exist
698 ( p_draft_id IN NUMBER,
699 p_po_exhibit_details_id IN NUMBER
700 ) RETURN VARCHAR2
701 IS
702 d_api_name CONSTANT VARCHAR2(30) := 'draft_changes_exist';
703 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
704 d_position NUMBER;
705
706 l_exists_tbl PO_TBL_VARCHAR1;
707 BEGIN
708 d_position := 0;
709 IF (PO_LOG.d_proc) THEN
710 PO_LOG.proc_begin(d_module);
711 END IF;
712
713 l_exists_tbl :=
714 draft_changes_exist
715 ( p_draft_id_tbl => PO_TBL_NUMBER(p_draft_id),
716 p_po_exhibit_details_id_tbl => PO_TBL_NUMBER(p_po_exhibit_details_id)
717 );
718
719 IF (PO_LOG.d_stmt) THEN
720 PO_LOG.stmt(d_module, d_position, 'exists', l_exists_tbl(1));
721 END IF;
722
723 RETURN l_exists_tbl(1);
724
725 EXCEPTION
726 WHEN OTHERS THEN
727 PO_MESSAGE_S.add_exc_msg
728 ( p_pkg_name => d_pkg_name,
729 p_procedure_name => d_api_name || '.' || d_position
730 );
731 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
732 END draft_changes_exist;
733
734
735
736 -----------------------------------------------------------------------
737 --Start of Comments
738 --Name: merge_changes
739 --Pre-reqs: None
740 --Modifies:
741 --Locks:
742 -- None
743 --Function:
744 -- Merge the records in draft table to transaction table
745 -- Either insert, update or delete will be performed on top of transaction
746 -- table, depending on the delete_flag on the draft record and whether the
747 -- record already exists in transaction table
748 --
749 --Parameters:
750 --IN:
751 --p_draft_id
752 -- draft unique identifier
753 --IN OUT:
754 --OUT:
755 --Returns:
756 --Notes:
757 --Testing:
758 --End of Comments
759 ------------------------------------------------------------------------
760 PROCEDURE merge_changes
761 ( p_draft_id IN NUMBER
762 ) IS
763
764 d_api_name CONSTANT VARCHAR2(30) := 'merge_changes';
765 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
766 d_position NUMBER;
767
768 BEGIN
769 d_position := 0;
770 IF (PO_LOG.d_proc) THEN
771 PO_LOG.proc_begin(d_module);
772 END IF;
773
774 -- Since putting DELETE within MERGE statement is causing database
775 -- to thrown internal error, for now we just separate the DELETE statement.
776 -- Once this is fixed we'll move the delete statement back to the merge
777 -- statement
778
779 -- bug5187544
780 -- Delete only records that have not been rejected
781
782 DELETE FROM po_exhibit_details pe
783 WHERE pe.po_exhibit_details_id IN
784 ( SELECT ped.po_exhibit_details_id -- Bug 5292573
785 FROM po_exhibit_details_draft ped
786 WHERE ped.draft_id = p_draft_id
787 AND ped.delete_flag = 'Y'
788 AND NVL(ped.change_accepted_flag, 'Y') = 'Y' );
789
790 MERGE INTO po_exhibit_details PE
791 USING (
792 SELECT
793 PED.draft_id,
794 PED.delete_flag,
795 PED.change_accepted_flag,
796 PED.po_exhibit_details_id,
797 PED.po_header_id,
798 PED.exhibit_name,
799 PED.exhibit_description,
800 PED.is_cdrl,
801 PED.reference_line_id,
802 PED.revision_num,
803 PED.last_update_date,
804 PED.last_updated_by,
805 PED.creation_date,
806 PED.created_by,
807 PED.last_update_login
808 FROM po_exhibit_details_draft PED
809 WHERE PED.draft_id = p_draft_id
810 AND NVL(PED.change_accepted_flag, 'Y') = 'Y'
811 ) PEDV
812 ON (PE.po_exhibit_details_id = PEDV.po_exhibit_details_id)
813 WHEN MATCHED THEN
814 UPDATE
815 SET
816 PE.last_update_date = PEDV.last_update_date,
817 PE.last_updated_by = PEDV.last_updated_by,
818 PE.po_header_id = PEDV.po_header_id,
819 PE.last_update_login = PEDV.last_update_login,
820 PE.exhibit_name = PEDV.exhibit_name,
821 PE.exhibit_description = PEDV.exhibit_description,
822 PE.is_cdrl = PEDV.is_cdrl,
823 PE.reference_line_id = PEDV.reference_line_id,
824 PE.revision_num = PEDV.revision_num
825 -- DELETE WHERE PDDV.delete_flag = 'Y'
826 WHEN NOT MATCHED THEN
827 INSERT
828 (
829 PE.po_exhibit_details_id,
830 PE.exhibit_name,
831 PE.exhibit_description,
832 PE.is_cdrl,
833 PE.revision_num,
834 PE.reference_line_id, --16626594
835 PE.last_update_date,
836 PE.last_updated_by,
837 PE.po_header_id,
838 PE.last_update_login,
839 PE.creation_date,
840 PE.created_by
841 )
842 VALUES
843 (
844 PEDV.po_exhibit_details_id,
845 PEDV.exhibit_name,
846 PEDV.exhibit_description,
847 PEDV.is_cdrl,
848 PEDV.revision_num,
849 PEDV.reference_line_id, -- 16626594
850 PEDV.last_update_date,
851 PEDV.last_updated_by,
852 PEDV.po_header_id,
853 PEDV.last_update_login,
854 PEDV.creation_date,
855 PEDV.created_by
856 ) WHERE NVL(PEDV.delete_flag, 'N') <> 'Y';
857
858 d_position := 10;
859 EXCEPTION
860 WHEN OTHERS THEN
861 PO_MESSAGE_S.add_exc_msg
862 ( p_pkg_name => d_pkg_name,
863 p_procedure_name => d_api_name || '.' || d_position
864 );
865 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
866 END merge_changes;
867
868
869 -----------------------------------------------------------------------
870 --Start of Comments
871 --Name: apply_changes
872 --Pre-reqs: None
873 --Modifies:
874 --Locks:
875 -- None
876 --Function:
877 -- Process exhibit draft records and merge them to transaction table. It
878 -- also performs all additional work related specifically to the merge
879 -- action
880 --Parameters:
881 --IN:
882 --p_draft_info
883 -- data structure storing draft information
884 --IN OUT:
885 --OUT:
886 --Returns:
887 --Notes:
888 --Testing:
889 --End of Comments
890 ------------------------------------------------------------------------
891 PROCEDURE apply_changes
892 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE
893 ) IS
894 d_api_name CONSTANT VARCHAR2(30) := 'apply_changes';
895 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
896 d_position NUMBER;
897
898 BEGIN
899 d_position := 0;
900 IF (PO_LOG.d_proc) THEN
901 PO_LOG.proc_begin(d_module);
902 END IF;
903
904 IF (p_draft_info.exhibits_changed = FND_API.G_FALSE) THEN
905 IF (PO_LOG.d_stmt) THEN
906 PO_LOG.stmt(d_module, d_position, 'no change-no need to apply');
907 END IF;
908
909 RETURN;
910 END IF;
911
912 d_position := 20;
913 -- Merge Changes
914 PO_EXHIBITS_PVT.merge_changes
915 ( p_draft_id => p_draft_info.draft_id
916 );
917
918 d_position := 30;
919 EXCEPTION
920 WHEN OTHERS THEN
921 PO_MESSAGE_S.add_exc_msg
922 ( p_pkg_name => d_pkg_name,
923 p_procedure_name => d_api_name || '.' || d_position
924 );
925 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
926 END apply_changes;
927
928
929
930 -----------------------------------------------------------------------
931 --Start of Comments
932 --Name: delete_rows
933 --Pre-reqs: None
934 --Modifies:
935 --Locks:
936 -- None
937 --Function:
938 -- Deletes drafts for exhibits based on the information given
939 -- If only draft_id is provided, then all exhibits for the draft will be
940 -- deleted
941 -- If po_exhibit_details_id is also provided, then the one record that has such
942 -- primary key will be deleted
943 --Parameters:
944 --IN:
945 --p_draft_id
946 -- draft unique identifier
947 --p_po_line_id
948 -- po line unique identifier
949 --IN OUT:
950 --OUT:
951 --Returns:
952 --Notes:
953 --Testing:
954 --End of Comments
955 ------------------------------------------------------------------------
956 PROCEDURE delete_rows
957 ( p_draft_id IN NUMBER,
958 p_po_exhibit_details_id IN NUMBER
959 ) IS
960
961 d_api_name CONSTANT VARCHAR2(30) := 'delete_rows';
962 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
963 d_position NUMBER;
964 po_exhibit_details_ids_tbl PO_TBL_NUMBER;
965
966 BEGIN
967 d_position := 0;
968 IF (PO_LOG.d_proc) THEN
969 PO_LOG.proc_begin(d_module);
970 END IF;
971
972 DELETE FROM po_exhibit_details_draft
973 WHERE draft_id = p_draft_id
974 AND po_exhibit_details_id = NVL(p_po_exhibit_details_id, po_exhibit_details_id);
975
976 d_position := 10;
977 EXCEPTION
978 WHEN OTHERS THEN
979 PO_MESSAGE_S.add_exc_msg
980 ( p_pkg_name => d_pkg_name,
981 p_procedure_name => d_api_name || '.' || d_position
982 );
983 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
984 END delete_rows;
985
986 -----------------------------------------------------------------------
987 --Start of Comments
988 --Name: insert_exhibits
989 --Pre-reqs: None
990 --Modifies:
991 --Locks:
992 -- None
993 --Function:
994 -- Inserts the given exhibits for the given document if it does not exists for that document
995 --Parameters:
996 --IN:
997 -- p_document_type_tbl IN PO_TBL_VARCHAR30;
998 -- p_document_id_tbl IN PO_TBL_NUMBER;
999 -- p_exhibit_name_tbl IN PO_TBL_VARCHAR30;
1000 -- p_exhibit_description_tbl IN PO_TBL_VARCHAR240;
1001 -- p_is_cdrl_tbl IN PO_TBL_VARCHAR1;
1002 --IN OUT:
1003 --OUT:
1004 --Returns:
1005 --Notes:
1006 --Testing:
1007 --End of Comments
1008 ------------------------------------------------------------------------
1009 PROCEDURE insert_exhibits
1010 (
1011 p_document_type_tbl IN PO_TBL_VARCHAR30,
1012 p_document_id_tbl IN PO_TBL_NUMBER,
1013 p_exhibit_name_tbl IN PO_TBL_VARCHAR30,
1014 p_exhibit_description_tbl IN PO_TBL_VARCHAR240,
1015 p_is_cdrl_tbl IN PO_TBL_VARCHAR1,
1016 p_revision_num_tbl IN PO_TBL_NUMBER,
1017 x_return_status OUT NOCOPY VARCHAR2,
1018 x_return_msg OUT NOCOPY VARCHAR2
1019 ) IS
1020
1021 d_api_name CONSTANT VARCHAR2(30) := 'insert_cdrl_exhibits';
1022 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1023 d_position NUMBER;
1024 l_is_valid_exhibit VARCHAR2(1);
1025
1026 BEGIN
1027
1028 d_position := 0;
1029 IF (PO_LOG.d_proc) THEN
1030 PO_LOG.proc_begin(d_module);
1031 PO_LOG.proc_begin(d_module,'Records count',p_document_id_tbl.count);
1032 END IF;
1033
1034 FOR i IN 1..p_document_id_tbl.COUNT LOOP
1035
1036 IF (PO_LOG.d_stmt) THEN
1037 PO_LOG.stmt(d_module, d_position, 'p_document_id_tbl',
1038 p_document_id_tbl(i));
1039 END IF;
1040
1041 IF( p_document_type_tbl(i) IN ('PO_STANDARD', 'PA_CONTRACT', 'PA_BLANKET') ) THEN
1042
1043 BEGIN
1044 SELECT Nvl(is_cdrl,'N')
1045 INTO l_is_valid_exhibit
1046 FROM po_exhibit_details ex
1047 WHERE ex.exhibit_name = p_exhibit_name_tbl(i)
1048 AND ex.po_header_id = p_document_id_tbl(i);
1049 EXCEPTION
1050 WHEN No_Data_Found THEN
1051 l_is_valid_exhibit := 'Y';
1052 END;
1053 -- No records exists for this exhibit for given document
1054 IF( l_is_valid_exhibit = 'Y') THEN
1055
1056 INSERT INTO po_exhibit_details
1057 (
1058 po_exhibit_details_id,
1059 po_header_id,
1060 exhibit_name,
1061 exhibit_description,
1062 is_cdrl,
1063 revision_num,
1064 LAST_UPDATE_DATE,
1065 LAST_UPDATED_BY,
1066 CREATION_DATE,
1067 CREATED_BY,
1068 LAST_UPDATE_LOGIN
1069 )
1070 SELECT
1071 po_exhibit_details_s.nextval,
1072 p_document_id_tbl(i),
1073 p_exhibit_name_tbl(i),
1074 p_exhibit_description_tbl(i),
1075 p_is_cdrl_tbl(i),
1076 p_revision_num_tbl(i),
1077 SYSDATE ,
1078 fnd_global.user_id,
1079 SYSDATE,
1080 fnd_global.user_id,
1081 fnd_global.login_id
1082 FROM dual
1083 WHERE NOT EXISTS (SELECT 1 FROM po_exhibit_details ex
1084 WHERE ex.exhibit_name = p_exhibit_name_tbl(i)
1085 AND ex.po_header_id = p_document_id_tbl(i));
1086
1087 -- Records exists- Given exhibit is already been used by the Exhibit
1088 ELSIF( l_is_valid_exhibit = 'N') THEN
1089
1090 x_return_status := FND_API.G_RET_STS_ERROR;
1091 x_return_msg := 'EXHIBIT_NUM_USED_BY_ELIN';
1092 RETURN;
1093 END IF;
1094
1095 ELSIF( p_document_type_tbl(i) IN ('PO_STANDARD_MOD', 'PA_CONTRACT_MOD', 'PA_BLANKET_MOD') ) THEN
1096
1097 BEGIN
1098 SELECT Nvl(is_cdrl, 'N')
1099 INTO l_is_valid_exhibit
1100 FROM po_exhibit_details_merge_v ex
1101 WHERE ex.exhibit_name = p_exhibit_name_tbl(i)
1102 AND ex.draft_id = p_document_id_tbl(i) ;
1103
1104 EXCEPTION
1105 WHEN No_Data_Found THEN
1106 l_is_valid_exhibit := 'Y';
1107 END;
1108
1109 -- No records exists for this exhibit for given document
1110 IF( l_is_valid_exhibit = 'Y') THEN
1111 INSERT INTO po_exhibit_details_draft
1112 (
1113 po_exhibit_details_id,
1114 po_header_id,
1115 draft_id,
1116 exhibit_name,
1117 exhibit_description,
1118 is_cdrl,
1119 change_status,
1120 revision_num,
1121 LAST_UPDATE_DATE,
1122 LAST_UPDATED_BY,
1123 CREATION_DATE,
1124 CREATED_BY,
1125 LAST_UPDATE_LOGIN
1126 )
1127 SELECT
1128 po_exhibit_details_s.nextval,
1129 dft.DOCUMENT_ID,
1130 p_document_id_tbl(i),
1131 p_exhibit_name_tbl(i),
1132 p_exhibit_description_tbl(i),
1133 p_is_cdrl_tbl(i),
1134 'NEW',
1135 p_revision_num_tbl(i),
1136 SYSDATE ,
1137 fnd_global.user_id,
1138 SYSDATE,
1139 fnd_global.user_id,
1140 fnd_global.login_id
1141 FROM po_drafts dft
1142 WHERE dft.draft_id = p_document_id_tbl(i)
1143 AND NOT EXISTS (SELECT 1 FROM po_exhibit_details_merge_v mex
1144 WHERE mex.exhibit_name = p_exhibit_name_tbl(i)
1145 AND mex.draft_id = p_document_id_tbl(i));
1146
1147 -- Records exists- Given exhibit is already been used by the Exhibit
1148 ELSIF( l_is_valid_exhibit = 'N') THEN
1149
1150 x_return_status := FND_API.G_RET_STS_ERROR;
1151 x_return_msg := 'EXHIBIT_NUM_USED_BY_ELIN';
1152 RETURN;
1153
1154 END IF;
1155
1156 END IF;
1157 END LOOP;
1158
1159 d_position := 40;
1160 IF (PO_LOG.d_proc) THEN
1161 PO_LOG.proc_end(d_module);
1162 END IF;
1163
1164 x_return_status := FND_API.G_RET_STS_SUCCESS;
1165
1166
1167 EXCEPTION
1168 WHEN OTHERS THEN
1169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1170 IF (PO_LOG.d_exc) THEN
1171 PO_LOG.exc(d_module,SQLCODE || SQLERRM);
1172 END IF;
1173 RAISE;
1174 END insert_exhibits;
1175
1176
1177
1178 -----------------------------------------------------------------------
1179 --Start of Comments
1180 --Name: delete_exhibits
1181 --Pre-reqs: None
1182 --Modifies:
1183 --Locks:
1184 -- None
1185 --Function:
1186 -- Inserts the given exhibits for the given document if it does not exists for that document
1187 --Parameters:
1188 --IN:
1189 -- p_document_type_tbl IN PO_TBL_VARCHAR30;
1190 -- p_document_id_tbl IN PO_TBL_NUMBER;
1191 -- p_exhibit_name_tbl IN PO_TBL_VARCHAR30;
1192 -- p_is_cdrl_tbl IN PO_TBL_VARCHAR1;
1193 --IN OUT:
1194 --OUT:
1195 --Returns:
1196 --Notes:
1197 --Testing:
1198 --End of Comments
1199 ------------------------------------------------------------------------
1200 PROCEDURE delete_exhibits
1201 (
1202 p_document_type_tbl IN PO_TBL_VARCHAR30,
1203 p_document_id_tbl IN PO_TBL_NUMBER,
1204 p_exhibit_name_tbl IN PO_TBL_VARCHAR30,
1205 p_is_cdrl_tbl IN PO_TBL_VARCHAR1,
1206 x_return_status OUT NOCOPY VARCHAR2,
1207 x_return_msg OUT NOCOPY VARCHAR2
1208 ) IS
1209
1210 d_api_name CONSTANT VARCHAR2(30) := 'delete_exhibits';
1211 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1212 d_position NUMBER;
1213 l_is_valid_exhibit VARCHAR2(1);
1214
1215 BEGIN
1216
1217 d_position := 0;
1218 IF (PO_LOG.d_proc) THEN
1219 PO_LOG.proc_begin(d_module);
1220 PO_LOG.proc_begin(d_module,'Records count',p_document_id_tbl.count);
1221 END IF;
1222
1223 FOR i IN 1..p_document_id_tbl.COUNT LOOP
1224
1225 IF (PO_LOG.d_stmt) THEN
1226 PO_LOG.stmt(d_module, d_position, 'p_document_id_tbl',
1227 p_document_id_tbl(i));
1228 END IF;
1229
1230 IF( p_document_type_tbl(i) IN ('PO_STANDARD', 'PA_CONTRACT', 'PA_BLANKET') ) THEN
1231
1232 DELETE po_exhibit_details ex
1233 WHERE ex.exhibit_name = p_exhibit_name_tbl(i)
1234 AND ex.po_header_id = p_document_id_tbl(i)
1235 AND ex.is_cdrl = p_is_cdrl_tbl(i);
1236
1237 ELSIF( p_document_type_tbl(i) IN ('PO_STANDARD_MOD', 'PA_CONTRACT_MOD', 'PA_BLANKET_MOD') ) THEN
1238
1239 DELETE po_exhibit_details_draft mex
1240 WHERE mex.exhibit_name = p_exhibit_name_tbl(i)
1241 AND mex.draft_id = p_document_id_tbl(i)
1242 AND mex.is_cdrl = p_is_cdrl_tbl(i);
1243
1244 END IF;
1245 END LOOP;
1246
1247 d_position := 40;
1248 IF (PO_LOG.d_proc) THEN
1249 PO_LOG.proc_end(d_module);
1250 END IF;
1251
1252 x_return_status := FND_API.G_RET_STS_SUCCESS;
1253
1254
1255 EXCEPTION
1256 WHEN OTHERS THEN
1257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1258 IF (PO_LOG.d_exc) THEN
1259 PO_LOG.exc(d_module,SQLCODE || SQLERRM);
1260 END IF;
1261 RAISE;
1262 END delete_exhibits;
1263
1264
1265 -----------------------------------------------------------------------
1266 --Start of Comments
1267 --Name: copy_cdrl_exhibit
1268 --Pre-reqs: None
1269 --Modifies:
1270 --Locks:
1271 -- None
1272 --Function:
1273 -- Copy the deliverables and exhibit for the given CDRL exhibit
1274 --Parameters:
1275 --IN:
1276 -- p_document_type_tbl IN PO_TBL_VARCHAR30;
1277 -- p_document_id_tbl IN PO_TBL_NUMBER;
1278 -- p_exhibit_name_tbl IN PO_TBL_VARCHAR30;
1279 -- p_doc_sub_type IN VARCHAR2
1280 -- p_is_cdrl_tbl IN PO_TBL_VARCHAR1;
1281
1282 --IN OUT:
1283 --OUT:
1284 --Returns:
1285 --Notes:
1286 --Testing:
1287 --End of Comments
1288 ------------------------------------------------------------------------
1289 PROCEDURE copy_cdrl_exhibit
1290 (
1291 p_po_header_id NUMBER,
1292 p_po_draft_id NUMBER,
1293 p_exhibit_name IN VARCHAR2,
1294 p_doc_sub_type IN VARCHAR2,
1295 x_return_status OUT NOCOPY VARCHAR2,
1296 x_return_msg OUT NOCOPY VARCHAR2
1297 ) IS
1298
1299 d_api_name CONSTANT VARCHAR2(30) := 'copy_cdrl_exhibit';
1300 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1301 d_position NUMBER;
1302 l_is_valid_exhibit VARCHAR2(1);
1303 l_errormessage VARCHAR2(2000);
1304
1305 l_msg_data VARCHAR2(240);
1306 l_msg_count NUMBER;
1307 l_return_status VARCHAR2(30);
1308 l_document_type VARCHAR2(30);
1309 l_new_exhibit_name VARCHAR2(30);
1310 l_docid NUMBER ;
1311
1312 BEGIN
1313
1314 d_position := 10;
1315 IF (PO_LOG.d_proc) THEN
1316 PO_LOG.proc_begin(d_module, 'p_po_draft_id', p_po_draft_id);
1317 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
1318 PO_LOG.proc_begin(d_module, 'p_exhibit_name', p_exhibit_name);
1319 PO_LOG.proc_begin(d_module, 'p_doc_sub_type', p_doc_sub_type);
1320 END IF;
1321
1322 l_new_exhibit_name := get_next_exhibit(p_header_id => p_po_header_id,
1323 p_draft_id => p_po_draft_id);
1324 IF (PO_LOG.d_proc) THEN
1325 PO_LOG.proc_begin(d_module, 'l_new_exhibit_name', l_new_exhibit_name);
1326 END IF;
1327
1328 IF( p_po_draft_id = -1 ) THEN
1329
1330 IF p_doc_sub_type = 'STANDARD' THEN
1331 l_document_type := 'PO_STANDARD';
1332 ELSIF p_doc_sub_type = 'BLANKET' THEN
1333 l_document_type := 'PA_BLANKET';
1334 ELSE
1335 l_document_type := 'PA_CONTRACT';
1336 END IF;
1337
1338 l_docid := p_po_header_id;
1339
1340 INSERT INTO po_exhibit_details
1341 (
1342 po_exhibit_details_id,
1343 po_header_id,
1344 exhibit_name,
1345 exhibit_description,
1346 is_cdrl,
1347 revision_num,
1348 LAST_UPDATE_DATE,
1349 LAST_UPDATED_BY,
1350 CREATION_DATE,
1351 CREATED_BY,
1352 LAST_UPDATE_LOGIN
1353 )
1354 SELECT
1355 po_exhibit_details_s.nextval,
1356 p_po_header_id,
1357 l_new_exhibit_name,
1358 exhibit_description,
1359 is_cdrl,
1360 revision_num,
1361 SYSDATE ,
1362 fnd_global.user_id,
1363 SYSDATE,
1364 fnd_global.user_id,
1365 fnd_global.login_id
1366 FROM po_exhibit_details
1367 WHERE po_header_id = p_po_header_id
1368 AND exhibit_name = p_exhibit_name;
1369
1370 ELSE
1371
1372 IF p_doc_sub_type = 'STANDARD' THEN
1373 l_document_type := 'PO_STANDARD_MOD';
1374 ELSIF p_doc_sub_type = 'BLANKET' THEN
1375 l_document_type := 'PA_BLANKET_MOD';
1376 ELSE
1377 l_document_type := 'PA_CONTRACT_MOD';
1378 END IF;
1379
1380 l_docid := p_po_draft_id;
1381
1382 INSERT INTO po_exhibit_details_draft
1383 (
1384 po_exhibit_details_id,
1385 po_header_id,
1386 draft_id,
1387 exhibit_name,
1388 exhibit_description,
1389 is_cdrl,
1390 change_status,
1391 revision_num,
1392 LAST_UPDATE_DATE,
1393 LAST_UPDATED_BY,
1394 CREATION_DATE,
1395 CREATED_BY,
1396 LAST_UPDATE_LOGIN
1397 )
1398 SELECT
1399 po_exhibit_details_s.nextval,
1400 po_header_id,
1401 draft_id,
1402 l_new_exhibit_name,
1403 exhibit_description,
1404 is_cdrl,
1405 'NEW',
1406 revision_num,
1407 SYSDATE ,
1408 fnd_global.user_id,
1409 SYSDATE,
1410 fnd_global.user_id,
1411 fnd_global.login_id
1412 FROM po_exhibit_details_draft dft
1413 WHERE po_header_id = p_po_header_id
1414 AND draft_id = p_po_draft_id
1415 AND exhibit_name = p_exhibit_name;
1416
1417
1418 END IF;
1419
1420 IF (PO_LOG.d_stmt) THEN
1421 PO_LOG.stmt(d_module, d_position, 'l_document_type', l_document_type);
1422 PO_LOG.stmt(d_module, d_position, 'l_docid', l_docid);
1423 PO_LOG.stmt(d_module, d_position, 'l_new_exhibit_name', l_new_exhibit_name);
1424
1425 END IF;
1426
1427 okc_cdrl_pvt.copy_cdrl_for_exhibit (
1428 p_api_version => 1.0,
1429 p_init_msg_list => FND_API.G_TRUE,
1430 p_commit => FND_API.G_FALSE,
1431 p_doc_type => l_document_type,
1432 p_doc_id => l_docid,
1433 p_doc_version => NULL,
1434 p_mode => NULL,
1435 p_src_exhibit => p_exhibit_name,
1436 p_target_exhibit => l_new_exhibit_name ,
1437 x_msg_data => l_msg_data,
1438 x_msg_count => l_msg_count,
1439 x_return_status => l_return_status
1440 );
1441
1442 IF (PO_LOG.d_stmt) THEN
1443 PO_LOG.stmt(d_module, d_position, 'l_msg_data', l_msg_data);
1444 PO_LOG.stmt(d_module, d_position, 'l_msg_count', l_msg_count);
1445 PO_LOG.stmt(d_module, d_position, 'l_return_status', l_return_status);
1446
1447 END IF;
1448
1449 d_position := 40;
1450 IF (PO_LOG.d_proc) THEN
1451 PO_LOG.proc_end(d_module);
1452 END IF;
1453
1454 x_return_status := l_return_status;--FND_API.G_RET_STS_SUCCESS;
1455 x_return_msg := l_msg_data;
1456
1457 EXCEPTION
1458 WHEN OTHERS THEN
1459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1460 IF (PO_LOG.d_exc) THEN
1461 PO_LOG.exc(d_module,SQLCODE || SQLERRM);
1462 END IF;
1463 RAISE;
1464 END copy_cdrl_exhibit;
1465
1466
1467 -----------------------------------------------------------------------
1468 --Start of Comments
1469 --Name: delete_cdrls_for_exhibit
1470 --Pre-reqs: None
1471 --Modifies:
1472 --Locks:
1473 -- None
1474 --Function:
1475 -- Delete the deliverables for the given CDRL exhibit
1476 --Parameters:
1477 --IN:
1478 -- p_document_type_tbl IN PO_TBL_VARCHAR30;
1479 -- p_document_id_tbl IN PO_TBL_NUMBER;
1480 -- p_exhibit_name_tbl IN PO_TBL_VARCHAR30;
1481 -- p_doc_sub_type IN VARCHAR2
1482 -- p_is_cdrl_tbl IN PO_TBL_VARCHAR1;
1483
1484 --IN OUT:
1485 --OUT:
1486 --Returns:
1487 --Notes:
1488 --Testing:
1489 --End of Comments
1490 ------------------------------------------------------------------------
1491 PROCEDURE delete_cdrls_for_exhibit
1492 (
1493 p_po_header_id IN NUMBER,
1494 p_po_draft_id IN NUMBER,
1495 p_exhibit_name IN VARCHAR2,
1496 p_doc_sub_type IN VARCHAR2,
1497 x_return_status OUT NOCOPY VARCHAR2,
1498 x_return_msg OUT NOCOPY VARCHAR2
1499 ) IS
1500
1501 d_api_name CONSTANT VARCHAR2(30) := 'delete_cdrls_for_exhibit';
1502 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1503 d_position NUMBER;
1504 l_errormessage VARCHAR2(2000);
1505
1506 l_msg_data VARCHAR2(240);
1507 l_msg_count NUMBER;
1508 l_return_status VARCHAR2(30);
1509 l_document_type VARCHAR2(30);
1510 l_exhibit_tbl okc_cdrl_pvt.exhibit_tbl_type;
1511 l_docid NUMBER ;
1512
1513 BEGIN
1514
1515 d_position := 10;
1516 IF (PO_LOG.d_proc) THEN
1517 PO_LOG.proc_begin(d_module, 'p_po_draft_id', p_po_draft_id);
1518 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
1519 PO_LOG.proc_begin(d_module, 'p_exhibit_name', p_exhibit_name);
1520 PO_LOG.proc_begin(d_module, 'p_doc_sub_type', p_doc_sub_type);
1521 END IF;
1522
1523 IF( p_po_draft_id = -1 ) THEN
1524
1525 IF p_doc_sub_type = 'STANDARD' THEN
1526 l_document_type := 'PO_STANDARD';
1527 ELSIF p_doc_sub_type = 'BLANKET' THEN
1528 l_document_type := 'PA_BLANKET';
1529 ELSE
1530 l_document_type := 'PA_CONTRACT';
1531 END IF;
1532
1533 l_docid := p_po_header_id;
1534
1535 ELSE
1536
1537 IF p_doc_sub_type = 'STANDARD' THEN
1538 l_document_type := 'PO_STANDARD_MOD';
1539 ELSIF p_doc_sub_type = 'BLANKET' THEN
1540 l_document_type := 'PA_BLANKET_MOD';
1541 ELSE
1542 l_document_type := 'PA_CONTRACT_MOD';
1543 END IF;
1544
1545 l_docid := p_po_draft_id;
1546
1547 END IF;
1548
1549 IF (PO_LOG.d_stmt) THEN
1550 PO_LOG.stmt(d_module, d_position, 'l_document_type', l_document_type);
1551 PO_LOG.stmt(d_module, d_position, 'l_docid', l_docid);
1552
1553 END IF;
1554
1555 l_exhibit_tbl(1) := p_exhibit_name;
1556
1557 okc_cdrl_pvt.delete_cdrl_for_exhibits (
1558 p_api_version => 1.0,
1559 p_init_msg_list => FND_API.G_TRUE,
1560 p_commit => FND_API.G_FALSE,
1561 p_doc_type => l_document_type,
1562 p_doc_id => l_docid,
1563 p_doc_version => NULL,
1564 p_mode => NULL,
1565 p_exhibit_tbl => l_exhibit_tbl,
1566 x_msg_data => l_msg_data,
1567 x_msg_count => l_msg_count,
1568 x_return_status => l_return_status
1569 );
1570
1571 IF (PO_LOG.d_stmt) THEN
1572 PO_LOG.stmt(d_module, d_position, 'l_msg_data', l_msg_data);
1573 PO_LOG.stmt(d_module, d_position, 'l_msg_count', l_msg_count);
1574 PO_LOG.stmt(d_module, d_position, 'l_return_status', l_return_status);
1575
1576 END IF;
1577
1578 d_position := 40;
1579 IF (PO_LOG.d_proc) THEN
1580 PO_LOG.proc_end(d_module);
1581 END IF;
1582
1583 x_return_status := l_return_status;--FND_API.G_RET_STS_SUCCESS;
1584 x_return_msg := l_msg_data;
1585
1586 EXCEPTION
1587 WHEN OTHERS THEN
1588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1589 IF (PO_LOG.d_exc) THEN
1590 PO_LOG.exc(d_module,SQLCODE || SQLERRM);
1591 END IF;
1592 RAISE;
1593 END delete_cdrls_for_exhibit;
1594
1595
1596 END PO_EXHIBITS_PVT;