[Home] [Help]
PACKAGE BODY: APPS.PO_LINES_DRAFT_PVT
Source
1 PACKAGE BODY PO_LINES_DRAFT_PVT AS
2 /* $Header: PO_LINES_DRAFT_PVT.plb 120.9.12020000.5 2013/04/19 06:45:04 pamandav ship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_LINES_DRAFT_PVT');
6
7 -------------------------------------------------------
8 ----------- PRIVATE PROCEDURES PROTOTYPE --------------
9 -------------------------------------------------------
10
11 PROCEDURE group_records_by_dml_type
12 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE
13 , x_delete_list OUT NOCOPY PO_TBL_NUMBER
14 , x_insert_list OUT NOCOPY PO_TBL_NUMBER
15 , x_update_list OUT NOCOPY PO_TBL_NUMBER
16 );
17
18
19 -------------------------------------------------------
20 -------------- PUBLIC PROCEDURES ----------------------
21 -------------------------------------------------------
22
23 -----------------------------------------------------------------------
24 --Start of Comments
25 --Name: draft_changes_exist
26 --Pre-reqs: None
27 --Modifies:
28 --Locks:
29 -- None
30 --Function:
31 -- Checks whether there is any draft changes in the draft table
32 -- given the draft_id or draft_id + po_line_id
33 -- If only draft_id is provided, this program returns FND_API.G_TRUE for
34 -- any draft changes in this table for the draft
35 -- If the whole primary key is provided (draft_id + line id), then
36 -- it return true if there is draft for this particular record in
37 -- the draft table
38 --Parameters:
39 --IN:
40 --p_draft_id_tbl
41 -- draft unique identifier
42 --p_po_line_id_tbl
43 -- po line unique identifier
44 --IN OUT:
45 --OUT:
46 --Returns:
47 -- Array of flags indicating whether draft changes exist for the corresponding
48 -- entry in the input parameter. For each entry in the returning array:
49 -- FND_API.G_TRUE if there are draft changes
50 -- FND_API.G_FALSE if there aren't draft changes
51 --Notes:
52 --Testing:
53 --End of Comments
54 ------------------------------------------------------------------------
55 FUNCTION draft_changes_exist
56 ( p_draft_id_tbl IN PO_TBL_NUMBER,
57 p_po_line_id_tbl IN PO_TBL_NUMBER
58 ) RETURN PO_TBL_VARCHAR1
59 IS
60 d_api_name CONSTANT VARCHAR2(30) := 'draft_changes_exist';
61 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
62 d_position NUMBER;
63
64 l_key NUMBER;
65 l_index_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
66 l_dft_exists_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
67 l_dft_exists_index_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
68
69 BEGIN
70 d_position := 0;
71 IF (PO_LOG.d_proc) THEN
72 PO_LOG.proc_begin(d_module);
73 END IF;
74
75 l_index_tbl.extend(p_draft_id_tbl.COUNT);
76 l_dft_exists_tbl.extend(p_draft_id_tbl.COUNT);
77
78 FOR i IN 1..l_index_tbl.COUNT LOOP
79 l_index_tbl(i) := i;
80 l_dft_exists_tbl(i) := FND_API.G_FALSE;
81 END LOOP;
82
83 d_position := 10;
84
85 l_key := PO_CORE_S.get_session_gt_nextval;
86
87 d_position := 20;
88
89 FORALL i IN 1..p_draft_id_tbl.COUNT
90 INSERT INTO po_session_gt
91 ( key,
92 num1
93 )
94 SELECT l_key,
95 l_index_tbl(i)
96 FROM DUAL
97 WHERE EXISTS (SELECT 1
98 FROM po_lines_draft_all PLD
99 WHERE PLD.draft_id = p_draft_id_tbl(i)
100 AND PLD.po_line_id = NVL(p_po_line_id_tbl(i),
101 PLD.po_line_id)
102 AND NVL(PLD.change_accepted_flag, 'Y') = 'Y');
103
104
105 d_position := 30;
106
107 -- All the num1 returned from this DELETE statement are indexes for
108 -- records that contain draft changes
109 DELETE FROM po_session_gt
110 WHERE key = l_key
111 RETURNING num1
112 BULK COLLECT INTO l_dft_exists_index_tbl;
113
114 d_position := 40;
115
116 FOR i IN 1..l_dft_exists_index_tbl.COUNT LOOP
117 l_dft_exists_tbl(l_dft_exists_index_tbl(i)) := FND_API.G_TRUE;
118 END LOOP;
119
120 IF (PO_LOG.d_stmt) THEN
121 PO_LOG.stmt(d_module, d_position, '# of records that have dft changes',
122 l_dft_exists_index_tbl.COUNT);
123 END IF;
124
125 RETURN l_dft_exists_tbl;
126
127 EXCEPTION
128 WHEN OTHERS THEN
129 PO_MESSAGE_S.add_exc_msg
130 ( p_pkg_name => d_pkg_name,
131 p_procedure_name => d_api_name || '.' || d_position
132 );
133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
134 END draft_changes_exist;
135
136
137 -----------------------------------------------------------------------
138 --Start of Comments
139 --Name: draft_changes_exist
140 --Pre-reqs: None
141 --Modifies:
142 --Locks:
143 -- None
144 --Function:
145 -- Same functionality as the bulk version of draft_changes_exist
146 --Parameters:
147 --IN:
148 --p_draft_id
149 -- draft unique identifier
150 --p_po_line_id
151 -- po line unique identifier
152 --IN OUT:
153 --OUT:
154 --Returns:
155 -- FND_API.G_TRUE if there are draft changes
156 -- FND_API.G_FALSE if there aren't draft changes
157 --Notes:
158 --Testing:
159 --End of Comments
160 ------------------------------------------------------------------------
161 FUNCTION draft_changes_exist
162 ( p_draft_id IN NUMBER,
163 p_po_line_id IN NUMBER
164 ) RETURN VARCHAR2
165 IS
166 d_api_name CONSTANT VARCHAR2(30) := 'draft_changes_exist';
167 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
168 d_position NUMBER;
169
170 l_exists_tbl PO_TBL_VARCHAR1;
171 BEGIN
172 d_position := 0;
173 IF (PO_LOG.d_proc) THEN
174 PO_LOG.proc_begin(d_module);
175 END IF;
176
177 l_exists_tbl :=
178 draft_changes_exist
179 ( p_draft_id_tbl => PO_TBL_NUMBER(p_draft_id),
180 p_po_line_id_tbl => PO_TBL_NUMBER(p_po_line_id)
181 );
182
183 IF (PO_LOG.d_stmt) THEN
184 PO_LOG.stmt(d_module, d_position, 'exists', l_exists_tbl(1));
185 END IF;
186
187 RETURN l_exists_tbl(1);
188
189 EXCEPTION
190 WHEN OTHERS THEN
191 PO_MESSAGE_S.add_exc_msg
192 ( p_pkg_name => d_pkg_name,
193 p_procedure_name => d_api_name || '.' || d_position
194 );
195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
196 END draft_changes_exist;
197
198
199 -----------------------------------------------------------------------
200 --Start of Comments
201 --Name: apply_changes
202 --Pre-reqs: None
203 --Modifies:
204 --Locks:
205 -- None
206 --Function:
207 -- Process line draft records and merge them to transaction table. It
208 -- also performs all additional work related specifically to the merge
209 -- action
210 --Parameters:
211 --IN:
212 --p_draft_info
213 -- data structure storing draft information
214 --IN OUT:
215 --OUT:
216 --Returns:
217 --Notes:
218 --Testing:
219 --End of Comments
220 ------------------------------------------------------------------------
221 PROCEDURE apply_changes
222 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE
223 ) IS
224 d_api_name CONSTANT VARCHAR2(30) := 'apply_changes';
225 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
226 d_position NUMBER;
227
228 l_delete_list PO_TBL_NUMBER;
229 l_insert_list PO_TBL_NUMBER;
230 l_update_list PO_TBL_NUMBER;
231
232 l_return_status varchar2(1);
233 l_msg_count number;
234 l_msg_data varchar2(200);
235 l_info_flag VARCHAR2(1);
236
237 l_clm_document varchar2(1);
238
239 BEGIN
240 d_position := 0;
241 IF (PO_LOG.d_proc) THEN
242 PO_LOG.proc_begin(d_module);
243 END IF;
244
245 IF (p_draft_info.lines_changed = FND_API.G_FALSE) THEN
246 IF (PO_LOG.d_stmt) THEN
247 PO_LOG.stmt(d_module, d_position, 'no change-no need to apply');
248 END IF;
249
250 RETURN;
251 END IF;
252
253 d_position := 10;
254 group_records_by_dml_type
255 ( p_draft_info => p_draft_info
256 , x_delete_list => l_delete_list
257 , x_insert_list => l_insert_list
258 , x_update_list => l_update_list
259 );
260
261 d_position := 20;
262 IF (l_delete_list.COUNT > 0) THEN
263
264 IF (PO_LOG.d_stmt) THEN
265 PO_LOG.stmt(d_module, d_position, 'Deleted Lines Found');
266 END IF;
267
268 BEGIN
269 SELECT 'Y'
270 INTO l_clm_document
271 FROM po_headers_all h,
272 po_doc_style_headers pdsh,
273 po_lines_all pl
274 WHERE h.style_id = pdsh.style_id
275 AND h.po_header_id = pl.po_header_id
276 AND pl.po_line_id = l_delete_list(1)
277 AND NVL(pdsh.clm_flag,'N') = 'Y'
278 AND ROWNUM = 1;
279
280 EXCEPTION WHEN No_Data_Found THEN
281 l_clm_document := 'N';
282 END;
283
284 d_position := 20;
285 IF (PO_LOG.d_stmt) THEN
286 PO_LOG.stmt(d_module, d_position, 'l_clm_document:'||l_clm_document);
287 END IF;
288
289 -- Bug: 13948625
290 -- Call update_req_for_linked_po_count, where the linked_po_count is updated for unlinked requisitions
291 -- and those reqs CLINs(Autocreated/SoftLinked) and PSlins(Softlinked) inserted into GT table for
292 -- further processing
293 -- This should be called only for CLM Standard document.
294
295 IF (p_draft_info.doc_subtype = 'STANDARD' AND l_clm_document = 'Y') THEN
296
297 d_position := 30;
298 IF (PO_LOG.d_stmt) THEN
299 PO_LOG.stmt(d_module, d_position, 'Calling update_req_for_linked_po_count');
300 END IF;
301
302 PO_REQ_LINES_SV.update_req_for_linked_po_count (l_delete_list, 'LINE');
303
304 END IF;
305
306 -- CLM Phase 4 - Elins project
307 BEGIN
308 FORALL i IN 1..l_delete_list.Count SAVE EXCEPTIONS
309
310 UPDATE po_exhibit_details_draft
311 SET REFERENCE_LINE_ID = NULL
312 WHERE REFERENCE_LINE_ID = l_delete_list(i);
313 EXCEPTION
314 WHEN No_Data_Found THEN
315 NULL;
316 END ;
317
318
319 d_position := 40;
320 IF (PO_LOG.d_stmt) THEN
321 PO_LOG.stmt(d_module, d_position, 'Calling send_line_deleted_notif');
322 END IF;
323
324 -- Options Enhancement:
325 -- Sending the notification to Requseter , if the priced slin/option line is deleted
326 -- but the parent/base clin is not yet deleted
327 -- In case of PO, document_id is draft_id
328 pon_clm_clo_util_pkg.SEND_LINE_DELETED_NOTIF( p_caller=>'PO'
329 ,p_document_id => p_draft_info.draft_id
330 ,p_document_line_id => null
331 ,x_return_status=>l_return_status
332 ,x_msg_count=>l_msg_count
333 ,x_msg_data =>l_msg_data);
334
335 d_position := 30;
336 FOR i IN 1..l_delete_list.COUNT LOOP
337
338 -- Bug: 13948625, Same old proc will be called for non clm documents deleted from BWC.
339 IF (l_clm_document <> 'Y') THEN
340
341 SELECT clm_info_flag
342 INTO l_info_flag
343 FROM po_lines_all
344 WHERE po_line_id = l_delete_list(i) ;
345
346 d_position := 40;
347 IF (PO_LOG.d_stmt) THEN
348 PO_LOG.stmt(d_module, d_position, 'l_info_flag:'||l_info_flag);
349 END IF ;
350
351 IF(l_info_flag LIKE 'Y') THEN --Call the remove req from po only info lines.
352 PO_REQ_LINES_SV.remove_req_from_po (l_delete_list(i), 'LINE');
353 END IF;
354
355 END IF;
356
357 d_position := 40;
358 IF (PO_LOG.d_stmt) THEN
359 PO_LOG.stmt(d_module, d_position, 'Calling delete_attachments');
360 END IF;
361
362 --Bug 13938456 Conc Mod attachments
363 --Passing null for automatically_added_flag instead of Y
364 --as all attachments have to be deleted
365 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
366 ( 'PO_LINES',
367 l_delete_list(i),
368 '','','','','', ''
369 );
370 END LOOP;
371 END IF;
372
373 d_position := 40;
374 -- Merge Changes
375 PO_LINES_DRAFT_PKG.merge_changes
376 ( p_draft_id => p_draft_info.draft_id,
377 p_draft_type => p_draft_info.draft_type -- UCA Changes
378 );
379
380 EXCEPTION
381 WHEN OTHERS THEN
382 PO_MESSAGE_S.add_exc_msg
383 ( p_pkg_name => d_pkg_name,
384 p_procedure_name => d_api_name || '.' || d_position
385 );
386 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
387
388 END apply_changes;
389
390 -- bug4176111
391 -----------------------------------------------------------------------
392 --Start of Comments
393 --Name: maintain_retroactive_change
394 --Modifies:
395 --Locks:
396 -- None
397 --Function:
398 -- Updates retroactive date at line level for blanket and SPO if there's
399 -- price change
400 --Parameters:
401 --IN:
402 --p_draft_info
403 -- data structure storing draft information
404 --IN OUT:
405 --OUT:
406 --Returns:
407 --Notes:
408 --Testing:
409 --End of Comments
410 ------------------------------------------------------------------------
411 PROCEDURE maintain_retroactive_change
412 ( p_draft_info IN PO_DRAFTS_PVT.draft_info_rec_type
413 ) IS
414 d_api_name CONSTANT VARCHAR2(30) := 'maintain_retroactive_change';
415 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
416 d_position NUMBER;
417
418 l_po_line_id_tbl PO_TBL_NUMBER;
419 l_price_break_lookup_code_tbl PO_TBL_VARCHAR30;
420
421 l_update_retro_date_flag_tbl PO_TBL_VARCHAR1;
422
423 BEGIN
424 d_position := 0;
425 IF (PO_LOG.d_proc) THEN
426 PO_LOG.proc_begin(d_module);
427 END IF;
428
429 IF ((NOT (p_draft_info.doc_type = 'PO' AND
430 p_draft_info.doc_subtype = 'STANDARD') AND
431 NOT (p_draft_info.doc_type = 'PA' AND
432 p_draft_info.doc_subtype = 'BLANKET')) OR
433 p_draft_info.lines_changed = FND_API.G_FALSE) THEN
434
435 RETURN;
436 END IF;
437
438 SELECT POLD.po_line_id,
439 NVL(POLD.price_break_lookup_code, 'NON CUMULATIVE'),
440 'N'
441 BULK COLLECT
442 INTO l_po_line_id_tbl,
443 l_price_break_lookup_code_tbl,
444 l_update_retro_date_flag_tbl
445 FROM po_lines_all POL,
446 po_lines_draft_all POLD
447 WHERE POLD.draft_id = p_draft_info.draft_id
448 AND POLD.po_line_id = POL.po_line_Id
449 AND NVL(POLD.delete_flag, 'N') = 'N'
450 AND NVL(POLD.change_accepted_flag, 'Y') = 'Y'
451 AND DECODE(POLD.unit_price, POL.unit_price, 'Y', 'N') = 'N' ;
452
453 FOR i IN 1..l_po_line_id_tbl.COUNT LOOP
454 IF (p_draft_info.doc_subtype = 'STANDARD') THEN
455 l_update_retro_date_flag_tbl(i) := 'Y';
456 ELSIF (p_draft_info.doc_subtype = 'BLANKET' AND
457 l_price_break_lookup_code_tbl(i) = 'NON CUMULATIVE') THEN
458 l_update_retro_date_flag_tbl(i) := 'Y';
459 END IF;
460 END LOOP;
461
462 FORALL i IN 1..l_po_line_id_tbl.COUNT
463 UPDATE po_lines_draft_all
464 SET retroactive_date = SYSDATE
465 WHERE draft_id = p_draft_info.draft_id
466 AND po_line_id = l_po_line_id_tbl(i)
467 AND l_update_retro_date_flag_tbl(i) = 'Y';
468
469
470 IF (PO_LOG.d_proc) THEN
471 PO_LOG.proc_end(d_module);
472 END IF;
473
474 EXCEPTION
475 WHEN OTHERS THEN
476 PO_MESSAGE_S.add_exc_msg
477 ( p_pkg_name => d_pkg_name,
478 p_procedure_name => d_api_name || '.' || d_position
479 );
480 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
481
482 END maintain_retroactive_change;
483
484 -------------------------------------------------------
485 -------------- PRIVATE PROCEDURES ---------------------
486 -------------------------------------------------------
487
488 -----------------------------------------------------------------------
489 --Start of Comments
490 --Name: group_records_by_dml_type
491 --Pre-reqs: None
492 --Modifies:
493 --Locks:
494 -- None
495 --Function:
496 -- Get all the draft records and separate them into three categories:
497 -- records to be deleted, inserted, and updated. The lists are returned
498 -- as arrays of numbers
499 --Parameters:
500 --IN:
501 --p_draft_info
502 -- record structure to hold draft information
503 --IN OUT:
504 --OUT:
505 --x_delete_list
506 -- IDs to be deleted from transaction table
507 --x_insert_list
508 -- IDs to be inserted in transaction table
509 --x_update_list
510 -- IDs to be updated in transaction table
511 --Returns:
512 --Notes:
513 --Testing:
514 --End of Comments
515 ------------------------------------------------------------------------
516 PROCEDURE group_records_by_dml_type
517 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE
518 , x_delete_list OUT NOCOPY PO_TBL_NUMBER
519 , x_insert_list OUT NOCOPY PO_TBL_NUMBER
520 , x_update_list OUT NOCOPY PO_TBL_NUMBER
521 ) IS
522 d_api_name CONSTANT VARCHAR2(30) := 'group_records_by_dml_type';
523 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
524 d_position NUMBER;
525
526 l_id_list PO_TBL_NUMBER;
527 l_del_flag_list PO_TBL_VARCHAR1;
528 l_txn_exists_list PO_TBL_VARCHAR1;
529
530 BEGIN
531 d_position := 0;
532 IF (PO_LOG.d_proc) THEN
533 PO_LOG.proc_begin(d_module);
534 END IF;
535
536 x_delete_list := PO_TBL_NUMBER();
537 x_insert_list := PO_TBL_NUMBER();
538 x_update_list := PO_TBL_NUMBER();
539
540 d_position := 10;
541 SELECT PLD.po_line_id,
542 NVL(PLD.delete_flag, 'N'),
543 DECODE(PL.po_line_id, NULL, 'N', 'Y')
544 BULK COLLECT
545 INTO l_id_list,
546 l_del_flag_list,
547 l_txn_exists_list
548 FROM po_lines_draft_all PLD,
549 po_lines_all PL
550 WHERE PLD.draft_id = p_draft_info.draft_id
551 AND NVL(PLD.change_accepted_flag, 'Y') = 'Y'
552 AND PLD.po_line_id = PL.po_line_id(+);
553
554 d_position := 20;
555 FOR i IN 1..l_id_list.COUNT LOOP
556 IF (l_del_flag_list(i) = 'Y') THEN
557 IF (l_txn_exists_list(i) = 'Y') THEN
558 x_delete_list.extend;
559 x_delete_list(x_delete_list.LAST) := l_id_list(i);
560 END IF;
561 ELSE
562 IF (l_txn_exists_list(i) = 'Y') THEN
563 x_update_list.extend;
564 x_update_list(x_update_list.LAST) := l_id_list(i);
565 ELSE
566 x_insert_list.extend;
567 x_insert_list(x_insert_list.LAST) := l_id_list(i);
568 END IF;
569 END IF;
570 END LOOP;
571
572 d_position := 30;
573 EXCEPTION
574 WHEN OTHERS THEN
575 PO_MESSAGE_S.add_exc_msg
576 ( p_pkg_name => d_pkg_name,
577 p_procedure_name => d_api_name || '.' || d_position
578 );
579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
580 END group_records_by_dml_type;
581
582 --<Conc Mod Project>
583 -----------------------------------------------------------------------
584 --Start of Comments
585 --Name: group_records_by_dml_type
586 --Pre-reqs: None
587 --Modifies:
588 --Locks:
589 -- None
590 --Function:
591 -- computes the next available clin number
592 --Parameters:
593 --IN:
594 --p_header_id
595 --p_line_num_arr:current Mod Line Nums
596 -- record structure to hold draft information
597 --Returns:
598 --Notes:
599 --Testing:
600 --End of Comments
601 ------------------------------------------------------------------------
602 FUNCTION get_next_clin_num
603 ( p_po_header_id IN NUMBER,
604 p_line_num_arr IN PO_TBL_VARCHAR100,
605 p_par_draft_id IN NUMBER DEFAULT NULL -- Bug 16584685
606 ) RETURN VARCHAR2
607 IS
608 l_oth_line_num_arr PO_TBL_VARCHAR100 := PO_TBL_VARCHAR100();
609 l_merged_line_num_arr PO_TBL_VARCHAR100 := PO_TBL_VARCHAR100();
610 d_api_name CONSTANT VARCHAR2(30) := 'get_next_clin_num';
611 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
612 d_position NUMBER;
613 BEGIN
614
615 d_position := 0;
616 IF (PO_LOG.d_proc) THEN
617 PO_LOG.proc_begin(d_module);
618 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
619 PO_LOG.proc_begin(d_module, 'p_par_draft_id', p_par_draft_id);
620 END IF;
621
622 -- Select Line Nums which have been added by other Modifications
623 -- <PAR Project>
624 -- collecting all line_num_display from po_lines_merge_v for the the given po_header_id.
625 -- this includes line_num_displa from base DOcument, Modification, PAR
626 SELECT DISTINCT line_num_display
627 BULK COLLECT INTO l_oth_line_num_arr
628 FROM po_lines_merge_v
629 WHERE po_header_id = p_po_header_id
630 AND group_line_id IS NULL
631 AND (status in ('DRAFT','REJECTED','IN PROCESS','PRE-APPROVED','COMPLETED',
632 'WITHDRAWN','SUPPLIER SIGN') --<Bug 16465675>
633 OR status IS NULL)
634 --Bug 16584685
635 --While considering line nums dont consider the par from which the mod is getting autocreated
636 --This condition applies only for PAR autocreate
637 AND draft_id <> Nvl(p_par_draft_id, -999);
638
639 d_position := 10;
640 IF (PO_LOG.d_stmt) THEN
641 PO_LOG.stmt(d_module, d_position, 'l_oth_line_num_arr', l_oth_line_num_arr);
642 END IF;
643
644 -- Get a distinct union of current Mod Line Nums and other Mods Line Nums
645 l_merged_line_num_arr := p_line_num_arr MULTISET UNION DISTINCT
646 l_oth_line_num_arr;
647
648 d_position := 20;
649 IF (PO_LOG.d_stmt) THEN
650 PO_LOG.stmt(d_module, d_position, 'l_merged_line_num_arr', l_merged_line_num_arr);
651 END IF;
652 IF (PO_LOG.d_proc) THEN
653 PO_LOG.proc_end(d_module);
654 END IF;
655
656 RETURN PON_CLO_RENUMBER_PKG.next_clin_num(l_merged_line_num_arr);
657 END get_next_clin_num;
658
659
660 --<Conc Mod Project>
661 -----------------------------------------------------------------------
662 --Start of Comments
663 --Name: group_records_by_dml_type
664 --Pre-reqs: None
665 --Modifies:
666 --Locks:
667 -- None
668 --Function:
669 -- computes the next available clin number
670 --Parameters:
671 --IN:
672 --p_po_line_id :-base line's po_line_id
673 --p_max_slin_num:max SLin number
674 --p_info_flag: info slin-'y' priced slin -'N'
675 -- record structure to hold draft information
676 --Returns:
677 --Notes:
678 --Testing:
679 --End of Comments
680 ------------------------------------------------------------------------
681 FUNCTION get_next_slin_num
682 ( p_po_line_id IN NUMBER,
683 p_max_slin_num IN VARCHAR2,
684 p_clin_num IN VARCHAR2,
685 p_info_flag IN VARCHAR2,
686 p_draft_id IN NUMBER
687 ) RETURN VARCHAR2
688 IS
689 l_max_slin_num VARCHAR2(100);
690 l_max_clin_num VARCHAR2(100);
691 l_next_slin_num VARCHAR2(100);
692 d_api_name CONSTANT VARCHAR2(30) := 'get_next_slin_num';
693 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
694 d_position NUMBER;
695 BEGIN
696 d_position := 0;
697 IF (PO_LOG.d_proc) THEN
698 PO_LOG.proc_begin(d_module);
699 END IF;
700
701 -- Get the max line num display for the line id p_po_line_id from
702 -- po_lines_merge_v. If this is greater than p_max_slin_num use it,
703 -- else use p_max_slin_num itself.
704 SELECT max(line_num_display)
705 INTO l_max_slin_num
706 FROM po_lines_merge_v
707 WHERE group_line_id = p_po_line_id
708 AND NVL(status,'Base Document') <> 'WITHDRAWN'
709 AND draft_id <>p_draft_id
710 AND NVL(clm_info_flag, 'N') = p_info_flag;
711
712 d_position := 10;
713 IF (PO_LOG.d_stmt) THEN
714 PO_LOG.stmt(d_module, d_position, 'l_max_slin_num', l_max_slin_num);
715 END IF;
716
717 IF NVL(l_max_slin_num,'-1') < NVL(p_max_slin_num,'-1') THEN
718 l_max_slin_num := p_max_slin_num;
719 END IF;
720 d_position := 20;
721 IF (PO_LOG.d_stmt) THEN
722 PO_LOG.stmt(d_module, d_position, 'l_max_slin_num', l_max_slin_num);
723 END IF;
724
725 IF l_max_slin_num is null then
726
727 IF p_info_flag = 'Y' THEN
728 RETURN (p_clin_num||'01');
729 ELSE
730 RETURN (p_clin_num||'AA');
731 END IF;
732
733 END IF;
734
735 IF p_info_flag = 'Y' THEN
736 l_next_slin_num :=
737 PON_CLO_RENUMBER_PKG.increment_info_slin_number(l_max_slin_num);
738 ELSE
739 l_next_slin_num :=
740 PON_CLO_RENUMBER_PKG.increment_priced_slin_number(l_max_slin_num);
741 END IF;
742
743 d_position := 40;
744 IF (PO_LOG.d_stmt) THEN
745 PO_LOG.stmt(d_module, d_position, 'l_next_slin_num', l_next_slin_num);
746 END IF;
747 IF (PO_LOG.d_proc) THEN
748 PO_LOG.proc_end(d_module);
749 END IF;
750 RETURN l_next_slin_num;
751
752 END;
753
754 END PO_LINES_DRAFT_PVT;