1 PACKAGE BODY PO_PDOI_MAINPROC_UTL_PVT AS
2 /* $Header: PO_PDOI_MAINPROC_UTL_PVT.plb 120.16.12020000.2 2013/02/10 16:28:43 vegajula ship $ */
3
4 d_pkg_name CONSTANT VARCHAR2(50) :=
5 PO_LOG.get_package_base('PO_PDOI_MAINPROC_UTL_PVT');
6
7 -- max line number for each document
8 g_max_line_num_tbl DBMS_SQL.number_table;
9
10 -- max shipment number for each line
11 g_max_shipment_num_tbl DBMS_SQL.number_table;
12
13 -- max distribution number for each shipment
14 g_max_dist_num_tbl DBMS_SQL.number_table;
15
16 -- max price differential number for each entity_type plus entity_id combination
17 TYPE max_price_diff_num_type IS TABLE OF DBMS_SQL.number_table INDEX BY VARCHAR2(30);
18 g_max_price_diff_num_tbl max_price_diff_num_type;
19
20 -- cache for quotation_class_code based on document subtype
21 TYPE quotation_class_code_type IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);
22 g_quotation_class_code_tbl quotation_class_code_type;
23
24 --------------------------------------------------------------------------
25 ---------------------- PUBLIC PROCEDURES ---------------------------------
26 --------------------------------------------------------------------------
27 --------------------------------------------------------------------------
28 --Start of Comments
29 --Name: get_quotation_class_code
30 --Pre-reqs: None
31 --Modifies:
32 --Locks:
33 -- None
34 --Function:
35 -- set up cache for quotation_class_code based on subtype;
36 -- the cache is set up on demand and will persist per group
37 --Parameters:
38 --IN:
39 -- p_subtype
40 -- the subtype of the document
41 --IN OUT: None
42 --OUT: None
43 --Returns:
44 -- the quotation_class_code value for a particular subtype
45 --Notes:
46 --Testing:
47 --End of Comments
48 --------------------------------------------------------------------------
49 PROCEDURE cleanup IS
50
51 d_api_name CONSTANT VARCHAR2(30) := 'cleanup';
52 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
53 d_position NUMBER;
54
55 BEGIN
56 d_position := 0;
57
58 IF (PO_LOG.d_proc) THEN
59 PO_LOG.proc_begin(d_module);
60 END IF;
61
62 g_max_line_num_tbl.DELETE;
63
64 g_max_shipment_num_tbl.DELETE;
65
66 g_max_dist_num_tbl.DELETE;
67
68 g_max_price_diff_num_tbl.DELETE;
69
70 IF (PO_LOG.d_proc) THEN
71 PO_LOG.proc_end (d_module);
72 END IF;
73
74 EXCEPTION
75 WHEN OTHERS THEN
76 PO_MESSAGE_S.add_exc_msg
77 (
78 p_pkg_name => d_pkg_name,
79 p_procedure_name => d_api_name || '.' || d_position
80 );
81 RAISE;
82 END cleanup;
83 --------------------------------------------------------------------------
84 --Start of Comments
85 --Name: get_quotation_class_code
86 --Pre-reqs: None
87 --Modifies:
88 --Locks:
89 -- None
90 --Function:
91 -- set up cache for quotation_class_code based on subtype;
92 -- the cache is set up on demand and will persist per group
93 --Parameters:
94 --IN:
95 -- p_subtype
96 -- the subtype of the document
97 --IN OUT: None
98 --OUT: None
99 --Returns:
100 -- the quotation_class_code value for a particular subtype
101 --Notes:
102 --Testing:
103 --End of Comments
104 --------------------------------------------------------------------------
105 FUNCTION get_quotation_class_code
106 (
107 p_doc_subtype IN VARCHAR2
108 )RETURN VARCHAR2
109 IS
110
111 d_api_name CONSTANT VARCHAR2(30) := 'get_quotation_class_code';
112 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
113 d_position NUMBER;
114
115 -- variable to hold query result
116 l_quotation_class_code VARCHAR2(25);
117
118 BEGIN
119 d_position := 0;
120
121 IF (PO_LOG.d_proc) THEN
122 PO_LOG.proc_begin(d_module, 'p_doc_subtype', p_doc_subtype);
123 END IF;
124
125 IF (g_quotation_class_code_tbl.COUNT = 0 OR
126 g_quotation_class_code_tbl.EXISTS(p_doc_subtype) = FALSE) THEN
127 d_position := 10;
128
129 -- query database if requested value is not cached
130 SELECT quotation_class_code
131 INTO l_quotation_class_code
132 FROM po_document_types
133 WHERE document_type_code = PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION
134 AND document_subtype = p_doc_subtype;
135
136 IF (PO_LOG.d_stmt) THEN
137 PO_LOG.stmt(d_module, d_position, 'l_quotation_class_code',
138 l_quotation_class_code);
139 END IF;
140
141 d_position := 20;
142
143 -- save the result in cache
144 g_quotation_class_code_tbl(p_doc_subtype) := l_quotation_class_code;
145 END IF;
146
147 IF (PO_LOG.d_proc) THEN
148 PO_LOG.proc_return (d_module, g_quotation_class_code_tbl(p_doc_subtype));
149 END IF;
150
151 RETURN g_quotation_class_code_tbl(p_doc_subtype);
152
153 EXCEPTION
154 WHEN OTHERS THEN
155 PO_MESSAGE_S.add_exc_msg
156 (
157 p_pkg_name => d_pkg_name,
158 p_procedure_name => d_api_name || '.' || d_position
159 );
160 RAISE;
161 END get_quotation_class_code;
162
163 --------------------------------------------------------------------------
164 --Start of Comments
165 --Name: calculate_max_line_num
166 --Pre-reqs: None
167 --Modifies:
168 --Locks:
169 -- None
170 --Function:
171 -- calculate current maximal line number for each document;
172 -- the cache is set up in each batch and will persist per group
173 --Parameters:
174 --IN:
175 -- p_po_header_id_tbl
176 -- list of document header id for which max line number need
177 -- ro be calculated
178 -- p_draft_id_tbl
179 -- draft id value for each po_header_id in the p_po_header_id_tbl
180 --IN OUT: None
181 --OUT: None
182 --Returns: none
183 --Notes:
184 --Testing:
185 --End of Comments
186 --------------------------------------------------------------------------
187 PROCEDURE calculate_max_line_num
188 (
189 p_po_header_id_tbl IN PO_TBL_NUMBER,
190 p_draft_id_tbl IN PO_TBL_NUMBER
191 ) IS
192
193 d_api_name CONSTANT VARCHAR2(30) := 'calculate_max_line_num';
194 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
195 d_position NUMBER;
196
197 l_key po_session_gt.key%TYPE;
198 l_po_header_id_tbl PO_TBL_NUMBER;
199 l_max_line_num_tbl PO_TBL_NUMBER;
200
201 l_processing_row_tbl DBMS_SQL.number_table;
202 l_po_header_id_no_dup_tbl DBMS_SQL.number_table;
203 BEGIN
204 d_position := 0;
205
206 IF (PO_LOG.d_proc) THEN
207 PO_LOG.proc_begin(d_module, 'p_po_header_id_tbl', p_po_header_id_tbl);
208 PO_LOG.proc_begin(d_module, 'p_draft_id_tbl', p_draft_id_tbl);
209 END IF;
210
211 -- No need to calculate max line number for following two cases:
212 -- 1. the max line number has already been cached before;
213 -- 2. same po_header_id appears more than once, then the max line
214 -- number only needs to be calculated once
215 FOR i IN 1..p_po_header_id_tbl.COUNT
216 LOOP
217 IF (PO_LOG.d_stmt) THEN
218 PO_LOG.stmt(d_module, d_position, 'index', i);
219 PO_LOG.stmt(d_module, d_position, 'calculated already?',
220 g_max_line_num_tbl.EXISTS(p_po_header_id_tbl(i)));
221 PO_LOG.stmt(d_module, d_position, 'duplicate id?',
222 l_po_header_id_no_dup_tbl.EXISTS(p_po_header_id_tbl(i)));
223 END IF;
224
225 IF (g_max_line_num_tbl.EXISTS(p_po_header_id_tbl(i))) THEN
226 NULL;
227 ELSIF (l_po_header_id_no_dup_tbl.EXISTS(p_po_header_id_tbl(i))) THEN
228 NULL;
229 ELSE
230 -- register the po_header_id in hash table
231 l_po_header_id_no_dup_tbl(p_po_header_id_tbl(i)) := i;
232
233 -- need to calculate max line number for this po_header_id
234 l_processing_row_tbl(i) := i;
235 END IF;
236 END LOOP;
237
238 d_position := 10;
239
240 -- pick a new key for temp table
241 l_key := PO_CORE_S.get_session_gt_nextval;
242
243 -- search in txn table
244 FORALL i IN INDICES OF l_processing_row_tbl
245 INSERT INTO po_session_gt(key, num1, num2)
246 SELECT l_key,
247 p_po_header_id_tbl(i),
248 v.max_line_num
249 FROM (SELECT max(line_num) AS max_line_num
250 FROM po_lines_all
251 WHERE po_header_id = p_po_header_id_tbl(i)) v
252 WHERE v.max_line_num IS NOT NULL;
253
254 d_position := 20;
255
256 -- search in draft table
257 FORALL i IN INDICES OF l_processing_row_tbl
258 INSERT INTO po_session_gt(key, num1, num2)
259 SELECT l_key,
260 p_po_header_id_tbl(i),
261 v.max_line_num
262 FROM (SELECT max(line_num) AS max_line_num
263 FROM po_lines_draft_all draft_lines
264 WHERE draft_id = p_draft_id_tbl(i)
265 AND po_header_id = p_po_header_id_tbl(i)) v
266 WHERE v.max_line_num IS NOT NULL;
267
268 d_position := 30;
269
270 -- search interface table
271 FORALL i IN INDICES OF l_processing_row_tbl
272 INSERT INTO po_session_gt(key, num1, num2)
273 SELECT l_key,
274 p_po_header_id_tbl(i),
275 v.max_line_num
276 FROM (SELECT /*+ INDEX(intf_headers PO_HEADERS_INTERFACE_N5) */ -- Added as 9799280 fix
277 max(intf_lines.line_num) AS max_line_num
278 FROM po_lines_interface intf_lines,
279 po_headers_interface intf_headers
280 WHERE intf_lines.interface_header_id = intf_headers.interface_header_id
281 AND intf_lines.processing_id = PO_PDOI_PARAMS.g_processing_id
282 AND intf_headers.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
283 AND intf_headers.processing_id = PO_PDOI_PARAMS.g_processing_id
284 AND intf_headers.po_header_id IS NOT NULL AND p_po_header_id_tbl(i) IS NOT NULL -- Added as part of 9799280 to handle & avoid NULL cases
285 AND intf_headers.po_header_id = p_po_header_id_tbl(i)) v
286 WHERE v.max_line_num IS NOT NULL;
287
288 d_position := 40;
289
290 -- set max_line_num in cache
291 DELETE FROM po_session_gt
292 WHERE key = l_key
293 RETURNING num1, num2 BULK COLLECT INTO l_po_header_id_tbl, l_max_line_num_tbl;
294
295 IF (PO_LOG.d_stmt) THEN
296 PO_LOG.stmt(d_module, d_position, 'l_po_header_id_tbl', l_po_header_id_tbl);
297 PO_LOG.stmt(d_module, d_position, 'l_max_line_num_tbl', l_max_line_num_tbl);
298 END IF;
299
300 FOR i IN 1..l_po_header_id_tbl.COUNT
301 LOOP
302 IF (PO_LOG.d_stmt) THEN
303 PO_LOG.stmt(d_module, d_position, 'index', i);
304 END IF;
305
306 IF (g_max_line_num_tbl.EXISTS(l_po_header_id_tbl(i))) THEN
307 IF (l_max_line_num_tbl(i) > g_max_line_num_tbl(l_po_header_id_tbl(i))) THEN
308 g_max_line_num_tbl(l_po_header_id_tbl(i)) := l_max_line_num_tbl(i);
309 END IF;
310 ELSE
311 g_max_line_num_tbl(l_po_header_id_tbl(i)) := l_max_line_num_tbl(i);
312 END IF;
313 END LOOP;
314
315 IF (PO_LOG.d_proc) THEN
316 PO_LOG.proc_end (d_module);
317 END IF;
318
319 EXCEPTION
320 WHEN OTHERS THEN
321 PO_MESSAGE_S.add_exc_msg
322 (
323 p_pkg_name => d_pkg_name,
324 p_procedure_name => d_api_name || '.' || d_position
325 );
326 RAISE;
327 END calculate_max_line_num;
328
329 --------------------------------------------------------------------------
330 --Start of Comments
331 --Name: get_next_line_num
332 --Pre-reqs: None
333 --Modifies:
334 --Locks:
335 -- None
336 --Function:
337 -- this procedure is called when customer does not assign line number
338 -- or they assign duplicate line numbers to po lines;
339 --Parameters:
340 --IN:
341 -- p_po_header_id
342 -- document header id for which new line number needs to be assigned
343 --IN OUT: None
344 --OUT: None
345 --Returns:
346 -- unique line number that can be assigned to a new po line
347 --Notes:
348 --Testing:
349 --End of Comments
350 --------------------------------------------------------------------------
351 FUNCTION get_next_line_num
352 (
353 p_po_header_id IN NUMBER
354 )
355 RETURN NUMBER
356 IS
357
358 d_api_name CONSTANT VARCHAR2(30) := 'get_next_line_num';
359 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
360 d_position NUMBER;
361
362 BEGIN
363 d_position := 0;
364
365 IF (PO_LOG.d_proc) THEN
366 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
367 END IF;
368
369 IF (g_max_line_num_tbl.EXISTS(p_po_header_id)) THEN
370 g_max_line_num_tbl(p_po_header_id) := g_max_line_num_tbl(p_po_header_id) + 1;
371 ELSE
372 g_max_line_num_tbl(p_po_header_id) := 1;
373 END IF;
374
375 IF (PO_LOG.d_proc) THEN
376 PO_LOG.proc_return (d_module, g_max_line_num_tbl(p_po_header_id));
377 END IF;
378
379 RETURN g_max_line_num_tbl(p_po_header_id);
380 EXCEPTION
381 WHEN OTHERS THEN
382 PO_MESSAGE_S.add_exc_msg
383 (
384 p_pkg_name => d_pkg_name,
385 p_procedure_name => d_api_name || '.' || d_position
386 );
387 RAISE;
388 END get_next_line_num;
389
390 --------------------------------------------------------------------------
391 --Start of Comments
392 --Name: get_next_po_line_id
393 --Pre-reqs: None
394 --Modifies:
395 --Locks:
396 -- None
397 --Function:
398 -- get a new po line id from sequence
399 --Parameters:
400 --IN: None
401 --IN OUT: None
402 --OUT: None
403 --Returns:
404 -- unique po line id from sequence po_lines_s
405 --Notes:
406 --Testing:
407 --End of Comments
408 --------------------------------------------------------------------------
409 FUNCTION get_next_po_line_id
410 RETURN NUMBER
411 IS
412
413 d_api_name CONSTANT VARCHAR2(30) := 'get_next_po_line_id';
414 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
415 d_position NUMBER;
416
417 l_next_po_line_id NUMBER;
418 BEGIN
419 d_position := 0;
420
421 IF (PO_LOG.d_proc) THEN
422 PO_LOG.proc_begin(d_module);
423 END IF;
424
425 SELECT po_lines_s.nextval
426 INTO l_next_po_line_id
427 FROM DUAL;
428
429 IF (PO_LOG.d_proc) THEN
430 PO_LOG.proc_return(d_module, l_next_po_line_id);
431 END IF;
432
433 RETURN l_next_po_line_id;
434 END get_next_po_line_id;
435
436 --------------------------------------------------------------------------
437 --Start of Comments
438 --Name: check_line_num_unique
439 --Pre-reqs: None
440 --Modifies:
441 --Locks:
442 -- None
443 --Function:
444 -- check whether the provided line numbers are unique accross the document
445 --Parameters:
446 --IN:
447 -- p_po_header_id_tbl
448 -- list of po_header_ids within the batch
449 -- p_draft_id_tbl
450 -- list of draft_ids within the batch
451 -- p_intf_line_id_tbl
452 -- list of interface line ids within the batch
453 -- p_line_num_tbl
454 -- list of line numbers within the batch
455 --IN OUT:
456 -- x_line_num_unique_tbl
457 -- boolean table to mark whether the provided line number is unique
458 --OUT: None
459 --Returns: None
460 --Notes:
461 --Testing:
462 --End of Comments
463 --------------------------------------------------------------------------
464 PROCEDURE check_line_num_unique
465 (
466 p_po_header_id_tbl IN PO_TBL_NUMBER,
467 p_draft_id_tbl IN PO_TBL_NUMBER,
468 p_intf_line_id_tbl IN PO_TBL_NUMBER,
469 p_line_num_tbl IN PO_TBL_NUMBER,
470 x_line_num_unique_tbl OUT NOCOPY PO_TBL_VARCHAR1
471 ) IS
472
473 d_api_name CONSTANT VARCHAR2(30) := 'check_line_num_unique';
474 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
475 d_position NUMBER;
476
477 l_index_tbl DBMS_SQL.number_table;
478
479 l_key po_session_gt.key%TYPE;
480 BEGIN
481 d_position := 0;
482
483 IF (PO_LOG.d_proc) THEN
484 PO_LOG.proc_begin(d_module, 'p_po_header_id_tbl', p_po_header_id_tbl);
485 PO_LOG.proc_begin(d_module, 'p_draft_id_tbl', p_draft_id_tbl);
486 PO_LOG.proc_begin(d_module, 'p_intf_line_id_tbl', p_intf_line_id_tbl);
487 PO_LOG.proc_begin(d_module, 'p_line_num_tbl', p_line_num_tbl);
488 END IF;
489
490 x_line_num_unique_tbl := PO_TBL_VARCHAR1();
491 x_line_num_unique_tbl.EXTEND(p_line_num_tbl.COUNT);
492
493 -- pick a new key for temp table
494 l_key := PO_CORE_S.get_session_gt_nextval;
495
496 -- initialize index table
497 PO_PDOI_UTL.generate_ordered_num_list
498 (
499 p_size => p_po_header_id_tbl.COUNT,
500 x_num_list => l_index_tbl
501 );
502
503 -- check draft table
504 FORALL i IN 1..l_index_tbl.COUNT
505 INSERT INTO po_session_gt(key, num1)
506 SELECT l_key,
507 l_index_tbl(i)
508 FROM po_lines_draft_all
509 WHERE po_header_id = p_po_header_id_tbl(i)
510 AND draft_id = p_draft_id_tbl(i)
511 AND line_num = p_line_num_tbl(i);
512
513 d_position := 10;
514
515 -- check txn table
516 FORALL i IN 1..l_index_tbl.COUNT
517 INSERT INTO po_session_gt(key, num1)
518 SELECT l_key,
519 l_index_tbl(i)
520 FROM po_lines_all
521 WHERE po_header_id = p_po_header_id_tbl(i)
522 AND line_num = p_line_num_tbl(i);
523
524 d_position := 20;
525
526 -- check interface table records which is before current records
527 FORALL i IN 1..l_index_tbl.COUNT
528 INSERT INTO po_session_gt(key, num1)
529 SELECT l_key,
530 l_index_tbl(i)
531 FROM po_lines_interface intf_lines,
532 po_headers_interface intf_headers
533 WHERE intf_lines.interface_header_id = intf_headers.interface_header_id
534 AND intf_lines.processing_id = PO_PDOI_PARAMS.g_processing_id
535 AND intf_headers.processing_round_num =
536 PO_PDOI_PARAMS.g_current_round_num
537 AND intf_headers.po_header_id = p_po_header_id_tbl(i)
538 AND intf_lines.interface_line_id < p_intf_line_id_tbl(i)
539 AND intf_lines.interface_line_id >= p_intf_line_id_tbl(1)
540 AND intf_lines.line_num = p_line_num_tbl(i)
541 AND intf_headers.po_header_id IS NOT NULL AND p_po_header_id_tbl(i) IS NOT NULL; -- Added as 9799280 fix to handle & avoid NULL cases.
542
543 d_position := 30;
544
545 DELETE FROM po_session_gt
546 WHERE key = l_key
547 RETURNING num1 BULK COLLECT INTO l_index_tbl;
548
549 FOR i IN 1..l_index_tbl.COUNT
550 LOOP
551 IF (PO_LOG.d_stmt) THEN
552 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
553 PO_LOG.stmt(d_module, d_position, 'line num is not unique',
554 p_line_num_tbl(l_index_tbl(i)));
555 END IF;
556
557 x_line_num_unique_tbl(l_index_tbl(i)) := FND_API.g_FALSE;
558 END LOOP;
559
560 IF (PO_LOG.d_proc) THEN
561 PO_LOG.proc_end (d_module);
562 END IF;
563
564 EXCEPTION
565 WHEN OTHERS THEN
566 PO_MESSAGE_S.add_exc_msg
567 (
568 p_pkg_name => d_pkg_name,
569 p_procedure_name => d_api_name || '.' || d_position
570 );
571 RAISE;
572 END check_line_num_unique;
573
574 --------------------------------------------------------------------------
575 --Start of Comments
576 --Name: calculate_max_shipment_num
577 --Pre-reqs: None
578 --Modifies:
579 --Locks:
580 -- None
581 --Function:
582 -- calculate maximal shipment number for each line and cache the result
583 --Parameters:
584 --IN:
585 -- p_po_line_id_tbl
586 -- list of po line ids for which maximal shipment number need to
587 -- be calculated
588 -- p_draft_id_tbl
589 -- draft id value for each po_line_id in p_po_line_id_tbl;
590 -- used to query draft table
591 --IN OUT: None
592 --OUT: None
593 --Returns: None
594 --Notes:
595 --Testing:
596 --End of Comments
597 --------------------------------------------------------------------------
598 PROCEDURE calculate_max_shipment_num
599 (
600 p_po_line_id_tbl IN PO_TBL_NUMBER,
601 p_draft_id_tbl IN PO_TBL_NUMBER
602 ) IS
603
604 d_api_name CONSTANT VARCHAR2(30) := 'calculate_max_shipment_num';
605 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
606 d_position NUMBER;
607
608 l_key po_session_gt.key%TYPE;
609 l_po_line_id_tbl PO_TBL_NUMBER;
610 l_max_shipment_num_tbl PO_TBL_NUMBER;
611
612 l_processing_row_tbl DBMS_SQL.number_table;
613 l_po_line_id_no_dup_tbl DBMS_SQL.number_table;
614 BEGIN
615 d_position := 0;
616
617 IF (PO_LOG.d_proc) THEN
618 PO_LOG.proc_begin(d_module, 'p_po_line_id_tbl', p_po_line_id_tbl);
619 PO_LOG.proc_begin(d_module, 'p_draft_id_tbl', p_draft_id_tbl);
620 END IF;
621
622 -- No need to calculate max shipment number for following two cases:
623 -- 1. the max shipment number has already been cached before;
624 -- 2. same po_line_id appears more than once, then the max shipment
625 -- number only needs to be calculated once
626 FOR i IN 1..p_po_line_id_tbl.COUNT
627 LOOP
628 IF (PO_LOG.d_stmt) THEN
629 PO_LOG.stmt(d_module, d_position, 'index', i);
630 PO_LOG.stmt(d_module, d_position, 'already calculated?',
631 g_max_shipment_num_tbl.EXISTS(p_po_line_id_tbl(i)));
632 PO_LOG.stmt(d_module, d_position, 'duplicate id?',
633 l_po_line_id_no_dup_tbl.EXISTS(p_po_line_id_tbl(i)));
634 END IF;
635
636 IF (g_max_shipment_num_tbl.EXISTS(p_po_line_id_tbl(i))) THEN
637 NULL;
638 ELSIF (l_po_line_id_no_dup_tbl.EXISTS(p_po_line_id_tbl(i))) THEN
639 NULL;
640 ELSE
641 -- register the po_line_id in hash table
642 l_po_line_id_no_dup_tbl(p_po_line_id_tbl(i)) := i;
643
644 -- need to calculate max line number for this po_line_id
645 l_processing_row_tbl(i) := i;
646 END IF;
647 END LOOP;
648
649 -- pick a new key for temp table
650 l_key := PO_CORE_S.get_session_gt_nextval;
651
652 -- bug 4642348:
653 -- if document type is QUOTATION, there is no need to
654 -- search the txn table since existing price breaks
655 -- will always be removed.
656 -- Remove the QUOTATION constant from the following
657 -- 'IF' statement.
658
659 -- search in txn table only for blanket
660 -- for SPO, there is never existing shipments
661 -- for quotation, existing price breaks will be removed
662 IF (PO_PDOI_PARAMS.g_request.document_type =
663 PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET) THEN
664 d_position := 10;
665
666 FORALL i IN INDICES OF l_processing_row_tbl
667 INSERT INTO po_session_gt(key, num1, num2)
668 SELECT l_key,
669 p_po_line_id_tbl(i),
670 v.max_shipment_num
671 FROM (SELECT max(shipment_num) AS max_shipment_num
672 FROM po_line_locations_all
673 WHERE po_line_id = p_po_line_id_tbl(i)) v
674 WHERE v.max_shipment_num IS NOT NULL;
675 END IF;
676
677 d_position := 20;
678
679 -- bug 4642348:
680 -- add filter on delete_flag since records with flag
681 -- equal to 'Y' would be deleted eventually and should
682 -- not be counted here
683
684 -- search in draft table
685 FORALL i IN INDICES OF l_processing_row_tbl
686 INSERT INTO po_session_gt(key, num1, num2)
687 SELECT l_key,
688 p_po_line_id_tbl(i),
689 v.max_shipment_num
690 FROM (SELECT max(shipment_num) AS max_shipment_num
691 FROM po_line_locations_draft_all
692 WHERE draft_id = p_draft_id_tbl(i)
693 AND po_line_id = p_po_line_id_tbl(i)
694 AND NVL(delete_flag, 'N') = 'N') v
695 WHERE v.max_shipment_num IS NOT NULL;
696
697 d_position := 30;
698
699 -- bug4703480
700 -- Add optimizer hint to ensure execution sequence
701
702 /* Bug 6940325 Added the clause "AND intf_lines.po_line_id IS ....." to ensure the index PO_LINES_INTERFACE_N8 is picked.
703 This fix is done as the CBO was starting from the po_line_locations_interface index.*/
704
705 -- search interface table
706 FORALL i IN INDICES OF l_processing_row_tbl
707 INSERT INTO po_session_gt(key, num1, num2)
708 SELECT l_key,
709 p_po_line_id_tbl(i),
710 v.max_shipment_num
711 FROM (SELECT /*+ INDEX(intf_lines PO_LINES_INTERFACE_N8) */
712 max(intf_locs.shipment_num) AS max_shipment_num
713 FROM po_line_locations_interface intf_locs,
714 po_lines_interface intf_lines,
715 po_headers_interface intf_headers
716 WHERE intf_locs.interface_line_id = intf_lines.interface_line_id
717 AND intf_lines.interface_header_id = intf_headers.interface_header_id
718 AND intf_locs.processing_id = PO_PDOI_PARAMS.g_processing_id
719 AND intf_headers.processing_round_num =
720 PO_PDOI_PARAMS.g_current_round_num
721 AND intf_lines.po_line_id = p_po_line_id_tbl(i)
722 AND intf_lines.po_line_id IS NOT NULL AND p_po_line_id_tbl(i) IS NOT NULL ) v
723 WHERE v.max_shipment_num IS NOT NULL;
724
725 d_position := 40;
726
727 -- set max_shipment_num in cache
728 DELETE FROM po_session_gt
729 WHERE key = l_key
730 RETURNING num1, num2 BULK COLLECT INTO l_po_line_id_tbl, l_max_shipment_num_tbl;
731
732 FOR i IN 1..l_po_line_id_tbl.COUNT
733 LOOP
734 IF (PO_LOG.d_stmt) THEN
735 PO_LOG.stmt(d_module, d_position, 'index', i);
736 IF (g_max_shipment_num_tbl.EXISTS(l_po_line_id_tbl(i))) THEN
737 PO_LOG.stmt(d_module, d_position, 'current max shipment num',
738 g_max_shipment_num_tbl(l_po_line_id_tbl(i)));
739 END IF;
740 PO_LOG.stmt(d_module, d_position, 'max shipment num',
741 l_max_shipment_num_tbl(i));
742 END IF;
743
744 IF (g_max_shipment_num_tbl.EXISTS(l_po_line_id_tbl(i))) THEN
745 IF (l_max_shipment_num_tbl(i) > g_max_shipment_num_tbl(l_po_line_id_tbl(i))) THEN
746 g_max_shipment_num_tbl(l_po_line_id_tbl(i)) := l_max_shipment_num_tbl(i);
747 END IF;
748 ELSE
749 g_max_shipment_num_tbl(l_po_line_id_tbl(i)) := l_max_shipment_num_tbl(i);
750 END IF;
751 END LOOP;
752
753 IF (PO_LOG.d_proc) THEN
754 PO_LOG.proc_end (d_module);
755 END IF;
756
757 EXCEPTION
758 WHEN OTHERS THEN
759 PO_MESSAGE_S.add_exc_msg
760 (
761 p_pkg_name => d_pkg_name,
762 p_procedure_name => d_api_name || '.' || d_position
763 );
764 RAISE;
765 END calculate_max_shipment_num;
766
767 --------------------------------------------------------------------------
768 --Start of Comments
769 --Name: get_next_shipment_num
770 --Pre-reqs: None
771 --Modifies:
772 --Locks:
773 -- None
774 --Function:
775 -- this procedure is called when customer does not assign shipment number
776 -- or they assign duplicate shipment numbers to po lines;
777 --Parameters:
778 --IN:
779 -- p_po_line_id
780 -- line identifier for which new shipment number needs to be assigned
781 --IN OUT: None
782 --OUT: None
783 --Returns:
784 -- a new shipment number which is unique for the po line
785 --Notes:
786 --Testing:
787 --End of Comments
788 --------------------------------------------------------------------------
789 FUNCTION get_next_shipment_num
790 (
791 p_po_line_id IN NUMBER
792 )
793 RETURN NUMBER
794 IS
795
796 d_api_name CONSTANT VARCHAR2(30) := 'get_next_shipment_num';
797 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
798 d_position NUMBER;
799
800 BEGIN
801 d_position := 0;
802
803 IF (PO_LOG.d_proc) THEN
804 PO_LOG.proc_begin(d_module, 'p_po_line_id', p_po_line_id);
805 END IF;
806
807 IF (g_max_shipment_num_tbl.EXISTS(p_po_line_id)) THEN
808 g_max_shipment_num_tbl(p_po_line_id) := g_max_shipment_num_tbl(p_po_line_id) + 1;
809 ELSE
810 g_max_shipment_num_tbl(p_po_line_id) := 1;
811 END IF;
812
813 IF (PO_LOG.d_proc) THEN
814 PO_LOG.proc_return(d_module, g_max_shipment_num_tbl(p_po_line_id));
815 END IF;
816
817 RETURN g_max_shipment_num_tbl(p_po_line_id);
818
819 END get_next_shipment_num;
820
821 --------------------------------------------------------------------------
822 --Start of Comments
823 --Name: get_next_line_loc_id
824 --Pre-reqs: None
825 --Modifies:
826 --Locks:
827 -- None
828 --Function:
829 -- get new line location id from sequence
830 --Parameters:
831 --IN: None
832 --IN OUT: None
833 --OUT: None
834 --Returns:
835 -- new line location id from sequence po_line_locations_s
836 --Notes:
837 --Testing:
838 --End of Comments
839 --------------------------------------------------------------------------
840 FUNCTION get_next_line_loc_id
841 RETURN NUMBER
842 IS
843
844 d_api_name CONSTANT VARCHAR2(30) := 'get_next_line_loc_id';
845 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
846 d_position NUMBER;
847
848 l_next_line_loc_id NUMBER;
849 BEGIN
850 d_position := 0;
851
852 IF (PO_LOG.d_proc) THEN
853 PO_LOG.proc_begin(d_module);
854 END IF;
855
856 SELECT po_line_locations_s.nextval
857 INTO l_next_line_loc_id
858 FROM DUAL;
859
860 IF (PO_LOG.d_proc) THEN
861 PO_LOG.proc_return(d_module, l_next_line_loc_id);
862 END IF;
863
864 RETURN l_next_line_loc_id;
865 END get_next_line_loc_id;
866
867 --------------------------------------------------------------------------
868 --Start of Comments
869 --Name: check_shipment_num_unique
870 --Pre-reqs: None
871 --Modifies:
872 --Locks:
873 -- None
874 --Function:
875 -- check whether the provided shipment numbers are unique accross the line
876 --Parameters:
877 --IN:
878 -- p_po_line_id_tbl
879 -- list of po_line_ids within the batch
880 -- p_intf_line_loc_id_tbl
881 -- list of interface line location ids within the batch
882 -- p_shipment_num_tbl
883 -- list of shipment numbers within the batch
884 --IN OUT:
885 -- x_shipment_num_unique_tbl
886 -- boolean table to mark whether the provided shipment number is unique
887 --OUT: None
888 --Returns: None
889 --Notes:
890 --Testing:
891 --End of Comments
892 --------------------------------------------------------------------------
893 PROCEDURE check_shipment_num_unique
894 (
895 p_po_line_id_tbl IN PO_TBL_NUMBER,
896 p_draft_id_tbl IN PO_TBL_NUMBER,
897 p_intf_line_loc_id_tbl IN PO_TBL_NUMBER,
898 p_shipment_num_tbl IN PO_TBL_NUMBER,
899 x_shipment_num_unique_tbl OUT NOCOPY PO_TBL_VARCHAR1
900 ) IS
901
902 d_api_name CONSTANT VARCHAR2(30) := 'check_shipment_num_unique';
903 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
904 d_position NUMBER;
905
906 l_index_tbl DBMS_SQL.number_table;
907
908 l_key po_session_gt.key%TYPE;
909 BEGIN
910 d_position := 0;
911
912 IF (PO_LOG.d_proc) THEN
913 PO_LOG.proc_begin(d_module, 'p_po_line_id_tbl', p_po_line_id_tbl);
914 PO_LOG.proc_begin(d_module, 'p_draft_id_tbl', p_draft_id_tbl);
915 PO_LOG.proc_begin(d_module, 'p_intf_line_loc_id_tbl', p_intf_line_loc_id_tbl);
916 PO_LOG.proc_begin(d_module, 'p_shipment_num_tbl', p_shipment_num_tbl);
917 END IF;
918
919 x_shipment_num_unique_tbl := PO_TBL_VARCHAR1();
920 x_shipment_num_unique_tbl.EXTEND(p_shipment_num_tbl.COUNT);
921
922 -- pick a new key for temp table
923 l_key := PO_CORE_S.get_session_gt_nextval;
924
925 -- initialize index table
926 PO_PDOI_UTL.generate_ordered_num_list
927 (
928 p_size => p_po_line_id_tbl.COUNT,
929 x_num_list => l_index_tbl
930 );
931
932 -- check draft table
933 FORALL i IN 1..l_index_tbl.COUNT
934 INSERT INTO po_session_gt(key, num1)
935 SELECT l_key,
936 l_index_tbl(i)
937 FROM po_line_locations_draft_all
938 WHERE po_line_id = p_po_line_id_tbl(i)
939 AND draft_id = p_draft_id_tbl(i)
940 AND shipment_type = 'STANDARD'
941 AND shipment_num = p_shipment_num_tbl(i);
942
943 d_position := 10;
944
945 /* Bug 6940325 Added the clause "AND intf_lines.po_line_id IS ....." to improve the performance.
946 This fix is done as the CBO was starting from the po_line_locations_interface index.
947 Ideally search should start from po_lines_interface.*/
948
949 -- check interface table records which is before current records
950 FORALL i IN 1..l_index_tbl.COUNT
951 INSERT INTO po_session_gt(key, num1)
952 SELECT l_key,
953 l_index_tbl(i)
954 FROM po_line_locations_interface intf_locs,
955 po_lines_interface intf_lines,
956 po_headers_interface intf_headers
957 WHERE intf_locs.interface_line_id = intf_lines.interface_line_id
958 AND intf_lines.interface_header_id = intf_headers.interface_header_id
959 AND intf_locs.processing_id = PO_PDOI_PARAMS.g_processing_id
960 AND intf_headers.processing_round_num =
961 PO_PDOI_PARAMS.g_current_round_num
962 AND intf_lines.po_line_id = p_po_line_id_tbl(i)
963 AND intf_locs.interface_line_location_id < p_intf_line_loc_id_tbl(i)
964 AND intf_locs.interface_line_location_id >= p_intf_line_loc_id_tbl(1)
965 AND intf_locs.shipment_num = p_shipment_num_tbl(i)
966 AND NVL(intf_locs.shipment_type, 'STANDARD') = 'STANDARD'
967 AND intf_lines.po_line_id IS NOT NULL AND p_po_line_id_tbl(i) IS NOT NULL;
968
969 d_position := 20;
970
971 DELETE FROM po_session_gt
972 WHERE key = l_key
973 RETURNING num1 BULK COLLECT INTO l_index_tbl;
974
975 FOR i IN 1..l_index_tbl.COUNT
976 LOOP
977 IF (PO_LOG.d_stmt) THEN
978 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
979 PO_LOG.stmt(d_module, d_position, 'shipment num is not unique',
980 p_shipment_num_tbl(l_index_tbl(i)));
981 END IF;
982
983 x_shipment_num_unique_tbl(l_index_tbl(i)) := FND_API.g_FALSE;
984 END LOOP;
985
986 IF (PO_LOG.d_proc) THEN
987 PO_LOG.proc_end (d_module);
988 END IF;
989
990 EXCEPTION
991 WHEN OTHERS THEN
992 PO_MESSAGE_S.add_exc_msg
993 (
994 p_pkg_name => d_pkg_name,
995 p_procedure_name => d_api_name || '.' || d_position
996 );
997 RAISE;
998 END check_shipment_num_unique;
999
1000 --------------------------------------------------------------------------
1001 --Start of Comments
1002 --Name: calculate_max_dist_num
1003 --Pre-reqs: None
1004 --Modifies:
1005 --Locks:
1006 -- None
1007 --Function:
1008 -- calculate maximal distribution number for each line location
1009 --Parameters:
1010 --IN:
1011 -- p_line_loc_id_tbl
1012 -- list of line location id for which maximal shipment number
1013 -- needs to be calculated
1014 -- p_draft_id_tbl
1015 -- list of corresponding draft id value for each line location
1016 -- id in p_line_loc_id_tbl
1017 --IN OUT: None
1018 --OUT: None
1019 --Returns: None
1020 --Notes:
1021 --Testing:
1022 --End of Comments
1023 --------------------------------------------------------------------------
1024 PROCEDURE calculate_max_dist_num
1025 (
1026 p_line_loc_id_tbl IN PO_TBL_NUMBER,
1027 p_draft_id_tbl IN PO_TBL_NUMBER
1028 ) IS
1029
1030 d_api_name CONSTANT VARCHAR2(30) := 'calculate_max_dist_num';
1031 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1032 d_position NUMBER;
1033
1034 l_key po_session_gt.key%TYPE;
1035 l_line_loc_id_tbl PO_TBL_NUMBER;
1036 l_max_dist_num_tbl PO_TBL_NUMBER;
1037
1038 l_processing_row_tbl DBMS_SQL.number_table;
1039 l_line_loc_id_no_dup_tbl DBMS_SQL.number_table;
1040 BEGIN
1041 d_position := 0;
1042
1043 IF (PO_LOG.d_proc) THEN
1044 PO_LOG.proc_begin(d_module, 'p_line_loc_id_tbl', p_line_loc_id_tbl);
1045 PO_LOG.proc_begin(d_module, 'p_draft_id_tbl', p_draft_id_tbl);
1046 END IF;
1047
1048 -- No need to calculate max dist number for following two cases:
1049 -- 1. the max dist number has already been cached before;
1050 -- 2. same line_loc_id appears more than once, then the max dist
1051 -- number only needs to be calculated once
1052 FOR i IN 1..p_line_loc_id_tbl.COUNT
1053 LOOP
1054 IF (PO_LOG.d_stmt) THEN
1055 PO_LOG.stmt(d_module, d_position, 'index', i);
1056 PO_LOG.stmt(d_module, d_position, 'already calculated?',
1057 g_max_dist_num_tbl.EXISTS(p_line_loc_id_tbl(i)));
1058 PO_LOG.stmt(d_module, d_position, 'duplicate id?',
1059 l_line_loc_id_no_dup_tbl.EXISTS(p_line_loc_id_tbl(i)));
1060 END IF;
1061
1062 IF (g_max_dist_num_tbl.EXISTS(p_line_loc_id_tbl(i))) THEN
1063 NULL;
1064 ELSIF (l_line_loc_id_no_dup_tbl.EXISTS(p_line_loc_id_tbl(i))) THEN
1065 NULL;
1066 ELSE
1067 -- register the po_line_id in hash table
1068 l_line_loc_id_no_dup_tbl(p_line_loc_id_tbl(i)) := i;
1069
1070 -- need to calculate max dist number for this line_loc_id
1071 l_processing_row_tbl(i) := i;
1072 END IF;
1073 END LOOP;
1074
1075 -- pick a new key for temp table
1076 l_key := PO_CORE_S.get_session_gt_nextval;
1077
1078 d_position := 10;
1079
1080 -- search in draft table
1081 FORALL i IN INDICES OF l_processing_row_tbl
1082 INSERT INTO po_session_gt(key, num1, num2)
1083 SELECT l_key,
1084 p_line_loc_id_tbl(i),
1085 v.max_dist_num
1086 FROM (SELECT max(distribution_num) AS max_dist_num
1087 FROM po_distributions_draft_all
1088 WHERE draft_id = p_draft_id_tbl(i)
1089 AND line_location_id = p_line_loc_id_tbl(i)) v
1090 WHERE v.max_dist_num IS NOT NULL;
1091
1092 d_position := 20;
1093
1094 -- bug4703480
1095 -- Add optimizer hint to ensure execution sequence
1096
1097 -- Bug6009113
1098 -- Adding the Leading Hint as per Apps Performance Team Suggestion
1099
1100 -- search interface table
1101 FORALL i IN INDICES OF l_processing_row_tbl
1102 INSERT INTO po_session_gt(key, num1, num2)
1103 SELECT l_key,
1104 p_line_loc_id_tbl(i),
1105 v.max_dist_num
1106 FROM (SELECT /*+ leading(intf_locs) INDEX(intf_locs PO_LINE_LOCATIONS_INTERFACE_N4) */
1107 max(intf_dists.distribution_num) AS max_dist_num
1108 FROM po_distributions_interface intf_dists,
1109 po_line_locations_interface intf_locs,
1110 po_headers_interface intf_headers
1111 WHERE intf_dists.interface_line_location_id =
1112 intf_locs.interface_line_location_id
1113 AND intf_locs.interface_header_id = intf_headers.interface_header_id
1114 AND intf_dists.processing_id = PO_PDOI_PARAMS.g_processing_id
1115 AND intf_headers.processing_round_num =
1116 PO_PDOI_PARAMS.g_current_round_num
1117 AND intf_locs.line_location_id = p_line_loc_id_tbl(i)) v
1118 WHERE v.max_dist_num IS NOT NULL;
1119
1120 d_position := 30;
1121
1122 -- set max_distribution_num in cache
1123 DELETE FROM po_session_gt
1124 WHERE key = l_key
1125 RETURNING num1, num2 BULK COLLECT INTO l_line_loc_id_tbl, l_max_dist_num_tbl;
1126
1127 FOR i IN 1..l_line_loc_id_tbl.COUNT
1128 LOOP
1129 IF (PO_LOG.d_stmt) THEN
1130 PO_LOG.stmt(d_module, d_position, 'index', i);
1131 IF (g_max_dist_num_tbl.EXISTS(l_line_loc_id_tbl(i))) THEN
1132 PO_LOG.stmt(d_module, d_position, 'current max dist num',
1133 g_max_dist_num_tbl(l_line_loc_id_tbl(i)));
1134 END IF;
1135 PO_LOG.stmt(d_module, d_position, 'max dist num',
1136 l_max_dist_num_tbl(i));
1137 END IF;
1138
1139 IF (g_max_dist_num_tbl.EXISTS(l_line_loc_id_tbl(i))) THEN
1140 IF (l_max_dist_num_tbl(i) > g_max_dist_num_tbl(l_line_loc_id_tbl(i))) THEN
1141 g_max_dist_num_tbl(l_line_loc_id_tbl(i)) := l_max_dist_num_tbl(i);
1142 END IF;
1143 ELSE
1144 g_max_dist_num_tbl(l_line_loc_id_tbl(i)) := l_max_dist_num_tbl(i);
1145 END IF;
1146 END LOOP;
1147
1148 IF (PO_LOG.d_proc) THEN
1149 PO_LOG.proc_end (d_module);
1150 END IF;
1151
1152 EXCEPTION
1153 WHEN OTHERS THEN
1154 PO_MESSAGE_S.add_exc_msg
1155 (
1156 p_pkg_name => d_pkg_name,
1157 p_procedure_name => d_api_name || '.' || d_position
1158 );
1159 RAISE;
1160 END calculate_max_dist_num;
1161
1162 --------------------------------------------------------------------------
1163 --Start of Comments
1164 --Name: get_next_dist_num
1165 --Pre-reqs: None
1166 --Modifies:
1167 --Locks:
1168 -- None
1169 --Function:
1170 -- This procedure is to get a unique distribution number if
1171 -- customer does not assign distribution number or assign
1172 -- duplicate distribution number for the line location
1173 --Parameters:
1174 --IN:
1175 -- p_line_loc_id
1176 -- line location id for which a new dist number needs
1177 -- to be assigned
1178 --IN OUT: None
1179 --OUT: None
1180 --Returns:
1181 -- unique distribution number across the line location
1182 --Notes:
1183 --Testing:
1184 --End of Comments
1185 --------------------------------------------------------------------------
1186 FUNCTION get_next_dist_num
1187 (
1188 p_line_loc_id IN NUMBER
1189 )
1190 RETURN NUMBER
1191 IS
1192
1193 d_api_name CONSTANT VARCHAR2(30) := 'get_next_dist_num';
1194 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1195 d_position NUMBER;
1196
1197 BEGIN
1198 d_position := 0;
1199
1200 IF (PO_LOG.d_proc) THEN
1201 PO_LOG.proc_begin(d_module, 'p_line_loc_id', p_line_loc_id);
1202 END IF;
1203
1204 IF (g_max_dist_num_tbl.EXISTS(p_line_loc_id)) THEN
1205 g_max_dist_num_tbl(p_line_loc_id) := g_max_dist_num_tbl(p_line_loc_id) + 1;
1206 ELSE
1207 g_max_dist_num_tbl(p_line_loc_id) := 1;
1208 END IF;
1209
1210 IF (PO_LOG.d_proc) THEN
1211 PO_LOG.proc_return(d_module, g_max_dist_num_tbl(p_line_loc_id));
1212 END IF;
1213
1214 RETURN g_max_dist_num_tbl(p_line_loc_id);
1215 END get_next_dist_num;
1216
1217 --------------------------------------------------------------------------
1218 --Start of Comments
1219 --Name: get_next_dist_id
1220 --Pre-reqs: None
1221 --Modifies:
1222 --Locks:
1223 -- None
1224 --Function:
1225 -- get next distribution id from sequence
1226 --Parameters:
1227 --IN: None
1228 --IN OUT: None
1229 --OUT: None
1230 --Returns:
1231 -- new distribution id from sequence po_distributions_s
1232 --Notes:
1233 --Testing:
1234 --End of Comments
1235 --------------------------------------------------------------------------
1236 FUNCTION get_next_dist_id
1237 RETURN NUMBER
1238 IS
1239
1240 d_api_name CONSTANT VARCHAR2(30) := 'get_next_dist_id';
1241 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1242 d_position NUMBER;
1243
1244 l_next_dist_id NUMBER;
1245 BEGIN
1246 d_position := 0;
1247
1248 IF (PO_LOG.d_proc) THEN
1249 PO_LOG.proc_begin(d_module);
1250 END IF;
1251
1252 SELECT po_distributions_s.nextval
1253 INTO l_next_dist_id
1254 FROM DUAL;
1255
1256 IF (PO_LOG.d_proc) THEN
1257 PO_LOG.proc_return(d_module, l_next_dist_id);
1258 END IF;
1259
1260 RETURN l_next_dist_id;
1261 EXCEPTION
1262 WHEN OTHERS THEN
1263 PO_MESSAGE_S.add_exc_msg
1264 (
1265 p_pkg_name => d_pkg_name,
1266 p_procedure_name => d_api_name || '.' || d_position
1267 );
1268 RAISE;
1269 END get_next_dist_id;
1270
1271 --------------------------------------------------------------------------
1272 --Start of Comments
1273 --Name: check_dist_num_unique
1274 --Pre-reqs: None
1275 --Modifies:
1276 --Locks:
1277 -- None
1278 --Function:
1279 -- procedure to check whether the provided distribution number is
1280 -- unique within the shipment
1281 --Parameters:
1282 --IN:
1283 -- p_line_loc_id_tbl
1284 -- list of line location id within the batch
1285 -- p_draft_id_tbl
1286 -- list of draft ids within the batch
1287 -- p_intf_dist_id_tbl
1288 -- list of interface distribution id within the batch
1289 -- p_dist_num_tbl
1290 -- list of distribution number within the batch
1291 --IN OUT:
1292 -- x_dist_num_unique_tbl
1293 -- boolean table to mark whether the provided distribution
1294 -- number is unique within the shipment
1295 --OUT: None
1296 --Returns: None
1297 --Notes:
1298 --Testing:
1299 --End of Comments
1300 --------------------------------------------------------------------------
1301 PROCEDURE check_dist_num_unique
1302 (
1303 p_line_loc_id_tbl IN PO_TBL_NUMBER,
1304 p_draft_id_tbl IN PO_TBL_NUMBER,
1305 p_intf_dist_id_tbl IN PO_TBL_NUMBER,
1306 p_dist_num_tbl IN PO_TBL_NUMBER,
1307 x_dist_num_unique_tbl OUT NOCOPY PO_TBL_VARCHAR1
1308 ) IS
1309
1310 d_api_name CONSTANT VARCHAR2(30) := 'check_dist_num_unique';
1311 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1312 d_position NUMBER;
1313
1314 l_key po_session_gt.key%TYPE;
1315
1316 l_index_tbl DBMS_SQL.NUMBER_TABLE;
1317 BEGIN
1318 d_position := 0;
1319
1320 IF (PO_LOG.d_proc) THEN
1321 PO_LOG.proc_begin(d_module, 'p_line_loc_id_tbl', p_line_loc_id_tbl);
1322 PO_LOG.proc_begin(d_module, 'p_draft_id_tbl', p_draft_id_tbl);
1323 PO_LOG.proc_begin(d_module, 'p_intf_dist_id_tbl', p_intf_dist_id_tbl);
1324 PO_LOG.proc_begin(d_module, 'p_dist_num_tbl', p_dist_num_tbl);
1325 END IF;
1326
1327 x_dist_num_unique_tbl := PO_TBL_VARCHAR1();
1328 x_dist_num_unique_tbl.EXTEND(p_dist_num_tbl.COUNT);
1329
1330 -- pick a new key from temp table
1331 l_key := PO_CORE_S.get_session_gt_nextval;
1332
1333 -- initialize index table
1334 PO_PDOI_UTL.generate_ordered_num_list
1335 (
1336 p_size => p_line_loc_id_tbl.COUNT,
1337 x_num_list => l_index_tbl
1338 );
1339
1340 -- check draft table
1341 FORALL i IN 1..l_index_tbl.COUNT
1342 INSERT INTO po_session_gt(key, num1)
1343 SELECT l_key,
1344 l_index_tbl(i)
1345 FROM po_distributions_draft_all
1346 WHERE line_location_id = p_line_loc_id_tbl(i)
1347 AND draft_id = p_draft_id_tbl(i)
1348 AND distribution_num = p_dist_num_tbl(i);
1349
1350 d_position := 10;
1351
1352 -- Bug6009113
1353 -- Adding the Leading Hint as per Apps Performance Team Suggestion
1354
1355 -- check interface table records which is before current records
1356 FORALL i IN 1..l_index_tbl.COUNT
1357 INSERT INTO po_session_gt(key, num1)
1358 SELECT /*+ LEADING(intf_locs) */ l_key,
1359 l_index_tbl(i)
1360 FROM po_distributions_interface intf_dists,
1361 po_line_locations_interface intf_locs,
1362 po_headers_interface intf_headers
1363 WHERE intf_dists.interface_line_location_id =
1364 intf_locs.interface_line_location_id
1365 AND intf_locs.interface_header_id = intf_headers.interface_header_id
1366 AND intf_dists.processing_id = PO_PDOI_PARAMS.g_processing_id
1367 AND intf_headers.processing_round_num =
1368 PO_PDOI_PARAMS.g_current_round_num
1369 AND intf_locs.line_location_id = p_line_loc_id_tbl(i)
1370 AND intf_dists.interface_distribution_id < p_intf_dist_id_tbl(i)
1371 AND intf_dists.interface_distribution_id >= p_intf_dist_id_tbl(1)
1372 AND intf_dists.distribution_num = p_dist_num_tbl(i);
1373
1374 d_position := 20;
1375
1376 DELETE FROM po_session_gt
1377 WHERE key = l_key
1378 RETURNING num1 BULK COLLECT INTO l_index_tbl;
1379
1380 FOR i IN 1..l_index_tbl.COUNT
1381 LOOP
1382 IF (PO_LOG.d_stmt) THEN
1383 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
1384 PO_LOG.stmt(d_module, d_position, 'dist number is not unique',
1385 p_dist_num_tbl(l_index_tbl(i)));
1386 END IF;
1387
1388 x_dist_num_unique_tbl(l_index_tbl(i)) := FND_API.g_FALSE;
1389 END LOOP;
1390
1391 IF (PO_LOG.d_proc) THEN
1392 PO_LOG.proc_end (d_module);
1393 END IF;
1394
1395 EXCEPTION
1396 WHEN OTHERS THEN
1397 PO_MESSAGE_S.add_exc_msg
1398 (
1399 p_pkg_name => d_pkg_name,
1400 p_procedure_name => d_api_name || '.' || d_position
1401 );
1402 RAISE;
1403 END check_dist_num_unique;
1404
1405 --------------------------------------------------------------------------
1406 --Start of Comments
1407 --Name: calculate_max_price_diff_num
1408 --Pre-reqs: None
1409 --Modifies:
1410 --Locks:
1411 -- None
1412 --Function:
1413 -- calculate maximal price differential number for each entity_type plus
1414 -- entity_id combination
1415 --Parameters:
1416 --IN:
1417 -- p_entity_type_tbl
1418 -- list of entity types on which max price diff num needs to be
1419 -- calculated
1420 -- p_entity_id_tbl
1421 -- list of entity ids on which maximal price diff number
1422 -- needs to be calculated
1423 -- p_draft_id_tbl
1424 -- list of corresponding draft id value for each price differential
1425 -- line
1426 -- p_price_diff_num_tbl
1427 -- list of original price diff nums provided by user
1428 --IN OUT: None
1429 --OUT: None
1430 --Returns: None
1431 --Notes:
1432 --Testing:
1433 --End of Comments
1434 --------------------------------------------------------------------------
1435 PROCEDURE calculate_max_price_diff_num
1436 (
1437 p_entity_type_tbl IN PO_TBL_VARCHAR30,
1438 p_entity_id_tbl IN PO_TBL_NUMBER,
1439 p_draft_id_tbl IN PO_TBL_NUMBER,
1440 p_price_diff_num_tbl IN PO_TBL_NUMBER
1441 ) IS
1442
1443 d_api_name CONSTANT VARCHAR2(30) := 'calculate_max_price_diff_num';
1444 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1445 d_position NUMBER;
1446
1447 l_key po_session_gt.key%TYPE;
1448 l_entity_type_tbl PO_TBL_VARCHAR30;
1449 l_entity_id_tbl PO_TBL_NUMBER;
1450 l_max_price_diff_num_tbl PO_TBL_NUMBER;
1451
1452 BEGIN
1453 d_position := 0;
1454
1455 IF (PO_LOG.d_proc) THEN
1456 PO_LOG.proc_begin(d_module, 'p_entity_type_tbl', p_entity_type_tbl);
1457 PO_LOG.proc_begin(d_module, 'p_entity_id_tbl', p_entity_id_tbl);
1458 PO_LOG.proc_begin(d_module, 'p_draft_id_tbl', p_draft_id_tbl);
1459 END IF;
1460
1461 -- pick a new key for temp table
1462 l_key := PO_CORE_S.get_session_gt_nextval;
1463
1464 d_position := 10;
1465
1466 -- first, search in draft table for max price diff num
1467 FORALL i IN 1..p_entity_id_tbl.COUNT
1468 INSERT INTO po_session_gt(key, char1, num1, num2)
1469 SELECT l_key,
1470 p_entity_type_tbl(i),
1471 p_entity_id_tbl(i),
1472 v.max_price_diff_num
1473 FROM (SELECT max(price_differential_num) AS max_price_diff_num
1474 FROM po_price_diff_draft
1475 WHERE draft_id = p_draft_id_tbl(i)
1476 AND entity_type = p_entity_type_tbl(i)
1477 AND entity_id = p_entity_id_tbl(i)) v
1478 WHERE v.max_price_diff_num IS NOT NULL;
1479
1480 d_position := 20;
1481
1482 -- second, search in txn table for max price diff num
1483 FORALL i IN 1..p_entity_id_tbl.COUNT
1484 INSERT INTO po_session_gt(key, char1, num1, num2)
1485 SELECT l_key,
1486 p_entity_type_tbl(i),
1487 p_entity_id_tbl(i),
1488 v.max_price_diff_num
1489 FROM (SELECT max(price_differential_num) AS max_price_diff_num
1490 FROM po_price_differentials
1491 WHERE entity_type = p_entity_type_tbl(i)
1492 AND entity_id = p_entity_id_tbl(i)) v
1493 WHERE v.max_price_diff_num IS NOT NULL;
1494
1495 d_position := 30;
1496
1497 -- set max price diff num in cache
1498 DELETE FROM po_session_gt
1499 WHERE key = l_key
1500 RETURNING char1, num1, num2 BULK COLLECT INTO
1501 l_entity_type_tbl, l_entity_id_tbl, l_max_price_diff_num_tbl;
1502
1503 IF (PO_LOG.d_stmt) THEN
1504 PO_LOG.stmt(d_module, d_position, 'l_entity_type_tbl', l_entity_type_tbl);
1505 PO_LOG.stmt(d_module, d_position, 'l_entity_id_tbl', l_entity_id_tbl);
1506 PO_LOG.stmt(d_module, d_position, 'l_max_price_diff_num_tbl',
1507 l_max_price_diff_num_tbl);
1508 END IF;
1509
1510 d_position := 40;
1511
1512 FOR i IN 1..l_entity_type_tbl.COUNT
1513 LOOP
1514 IF (g_max_price_diff_num_tbl.EXISTS(l_entity_type_tbl(i)) AND
1515 g_max_price_diff_num_tbl(l_entity_type_tbl(i)).EXISTS(l_entity_id_tbl(i))) THEN
1516 IF (l_max_price_diff_num_tbl(i) >
1517 g_max_price_diff_num_tbl(l_entity_type_tbl(i))(l_entity_id_tbl(i))) THEN
1518 g_max_price_diff_num_tbl(l_entity_type_tbl(i))(l_entity_id_tbl(i)) :=
1519 l_max_price_diff_num_tbl(i);
1520 END IF;
1521 ELSE
1522 g_max_price_diff_num_tbl(l_entity_type_tbl(i))(l_entity_id_tbl(i)) :=
1523 l_max_price_diff_num_tbl(i);
1524 END IF;
1525 END LOOP;
1526
1527 d_position := 50;
1528
1529 -- last, search inside current batch
1530 FOR i IN 1..p_entity_type_tbl.COUNT
1531 LOOP
1532 IF (p_price_diff_num_tbl(i) IS NOT NULL AND
1533 p_entity_type_tbl(i) IS NOT NULL AND
1534 p_entity_id_tbl(i) IS NOT NULL) THEN
1535 IF (g_max_price_diff_num_tbl.EXISTS(p_entity_type_tbl(i)) AND
1536 g_max_price_diff_num_tbl(p_entity_type_tbl(i)).EXISTS(p_entity_id_tbl(i))) THEN
1537 IF (p_price_diff_num_tbl(i) >
1538 g_max_price_diff_num_tbl(p_entity_type_tbl(i))(p_entity_id_tbl(i))) THEN
1539 g_max_price_diff_num_tbl(p_entity_type_tbl(i))(p_entity_id_tbl(i)) :=
1540 p_price_diff_num_tbl(i);
1541 END IF;
1542 ELSE
1543 g_max_price_diff_num_tbl(p_entity_type_tbl(i))(p_entity_id_tbl(i)) :=
1544 p_price_diff_num_tbl(i);
1545 END IF;
1546 END IF;
1547 END LOOP;
1548
1549 d_position := 60;
1550
1551 IF (PO_LOG.d_proc) THEN
1552 PO_LOG.proc_end (d_module);
1553 END IF;
1554
1555 EXCEPTION
1556 WHEN OTHERS THEN
1557 PO_MESSAGE_S.add_exc_msg
1558 (
1559 p_pkg_name => d_pkg_name,
1560 p_procedure_name => d_api_name || '.' || d_position
1561 );
1562 RAISE;
1563 END calculate_max_price_diff_num;
1564
1565 --------------------------------------------------------------------------
1566 --Start of Comments
1567 --Name: get_next_price_diff_num
1568 --Pre-reqs: None
1569 --Modifies:
1570 --Locks:
1571 -- None
1572 --Function:
1573 -- This procedure is to get a unique price differential number if
1574 -- customer does not assign price differential number or assign
1575 -- duplicate price differential number for the entity_type plus
1576 -- entity_id combination
1577 --Parameters:
1578 --IN:
1579 -- p_entity_id
1580 -- entity id for which a new price differential number needs
1581 -- to be assigned; This id can be either a po_line_id or
1582 -- line_location_id depending on entity_type
1583 -- p_entity_type
1584 -- The value can be 'PO LINE', 'BLANKET LINE' or 'PRICE BREAK'
1585 --IN OUT: None
1586 --OUT: None
1587 --Returns:
1588 -- unique price differential number across the entity_type plus entity_id
1589 --Notes:
1590 --Testing:
1591 --End of Comments
1592 --------------------------------------------------------------------------
1593 FUNCTION get_next_price_diff_num
1594 (
1595 p_entity_type IN VARCHAR2,
1596 p_entity_id IN NUMBER
1597 )
1598 RETURN NUMBER
1599 IS
1600
1601 d_api_name CONSTANT VARCHAR2(30) := 'get_next_price_diff_num';
1602 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1603 d_position NUMBER;
1604
1605 BEGIN
1606 d_position := 0;
1607
1608 IF (PO_LOG.d_proc) THEN
1609 PO_LOG.proc_begin(d_module, 'p_entity_id', p_entity_id);
1610 PO_LOG.proc_begin(d_module, 'p_entity_type', p_entity_type);
1611 END IF;
1612
1613 IF (p_entity_type IS NOT NULL AND p_entity_id IS NOT NULL) THEN
1614 IF (g_max_price_diff_num_tbl.EXISTS(p_entity_type) AND
1615 g_max_price_diff_num_tbl(p_entity_type).EXISTS(p_entity_id)) THEN
1616 g_max_price_diff_num_tbl(p_entity_type)(p_entity_id) :=
1617 g_max_price_diff_num_tbl(p_entity_type)(p_entity_id) + 1;
1618 ELSE
1619 g_max_price_diff_num_tbl(p_entity_type)(p_entity_id) := 1;
1620 END IF;
1621
1622 IF (PO_LOG.d_proc) THEN
1623 PO_LOG.proc_return(d_module, g_max_price_diff_num_tbl(p_entity_type)(p_entity_id));
1624 END IF;
1625
1626 RETURN g_max_price_diff_num_tbl(p_entity_type)(p_entity_id);
1627 ELSE
1628 IF (PO_LOG.d_proc) THEN
1629 PO_LOG.proc_return(d_module, 'NULL');
1630 END IF;
1631
1632 RETURN NULL;
1633 END IF;
1634 END get_next_price_diff_num;
1635
1636 --------------------------------------------------------------------------
1637 --Start of Comments
1638 --Name: check_price_diff_num_unique
1639 --Pre-reqs: None
1640 --Modifies:
1641 --Locks:
1642 -- None
1643 --Function:
1644 -- procedure to check whether the provided price differential number is
1645 -- unique across entity_type plus entity_id
1646 --Parameters:
1647 --IN:
1648 -- p_entity_type_tbl
1649 -- list of entity_type values within the batch
1650 -- p_entity_id_tbl
1651 -- list of entity ids(po_line_id or line_location_id) within the batch
1652 -- p_draft_id_tbl
1653 -- list of draft ids within the batch
1654 -- p_intf_price_diff_id_tbl
1655 -- list of interface price differential id within the batch
1656 -- p_price_diff_num_tbl
1657 -- list of price differential number within the batch
1658 --IN OUT:
1659 -- x_price_diff_num_unique_tbl
1660 -- boolean table to mark whether the provided price differential
1661 -- number is unique within the shipment
1662 --OUT: None
1663 --Returns: None
1664 --Notes:
1665 --Testing:
1666 --End of Comments
1667 --------------------------------------------------------------------------
1668 PROCEDURE check_price_diff_num_unique
1669 (
1670 p_entity_type_tbl IN PO_TBL_VARCHAR30,
1671 p_entity_id_tbl IN PO_TBL_NUMBER,
1672 p_draft_id_tbl IN PO_TBL_NUMBER,
1673 p_intf_price_diff_id_tbl IN PO_TBL_NUMBER,
1674 p_price_diff_num_tbl IN PO_TBL_NUMBER,
1675 x_price_diff_num_unique_tbl OUT NOCOPY PO_TBL_VARCHAR1
1676 ) IS
1677
1678 d_api_name CONSTANT VARCHAR2(30) := 'check_price_diff_num_unique';
1679 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1680 d_position NUMBER;
1681
1682 l_key po_session_gt.key%TYPE;
1683
1684 l_index_tbl DBMS_SQL.NUMBER_TABLE;
1685
1686 TYPE two_dimension_table_type IS TABLE OF DBMS_SQL.VARCHAR2_TABLE INDEX BY PLS_INTEGER;
1687 TYPE three_dimension_table_type IS TABLE OF two_dimension_table_type INDEX BY VARCHAR2(30);
1688 l_price_diff_num_exist_tbl three_dimension_table_type;
1689 BEGIN
1690 d_position := 0;
1691
1692 IF (PO_LOG.d_proc) THEN
1693 PO_LOG.proc_begin(d_module, 'p_entity_type_tbl', p_entity_type_tbl);
1694 PO_LOG.proc_begin(d_module, 'p_entity_id_tbl', p_entity_id_tbl);
1695 PO_LOG.proc_begin(d_module, 'p_draft_id_tbl', p_draft_id_tbl);
1696 PO_LOG.proc_begin(d_module, 'p_intf_price_diff_id_tbl',
1697 p_intf_price_diff_id_tbl);
1698 PO_LOG.proc_begin(d_module, 'p_price_diff_num_tbl', p_price_diff_num_tbl);
1699 END IF;
1700
1701 x_price_diff_num_unique_tbl := PO_TBL_VARCHAR1();
1702 x_price_diff_num_unique_tbl.EXTEND(p_price_diff_num_tbl.COUNT);
1703
1704 -- pick a new key from temp table
1705 l_key := PO_CORE_S.get_session_gt_nextval;
1706
1707 -- initialize index table
1708 PO_PDOI_UTL.generate_ordered_num_list
1709 (
1710 p_size => p_entity_id_tbl.COUNT,
1711 x_num_list => l_index_tbl
1712 );
1713
1714 -- first, check draft table to see whether the provided price diff nums exist
1715 FORALL i IN 1..l_index_tbl.COUNT
1716 INSERT INTO po_session_gt(key, num1)
1717 SELECT l_key,
1718 l_index_tbl(i)
1719 FROM po_price_diff_draft
1720 WHERE entity_type = p_entity_type_tbl(i)
1721 AND entity_id = p_entity_id_tbl(i)
1722 AND draft_id = p_draft_id_tbl(i)
1723 AND price_differential_num = p_price_diff_num_tbl(i);
1724
1725 d_position := 10;
1726
1727 -- second, check txn table to see whether the provided price diff nums exist
1728 FORALL i IN 1..l_index_tbl.COUNT
1729 INSERT INTO po_session_gt(key, num1)
1730 SELECT l_key,
1731 l_index_tbl(i)
1732 FROM po_price_differentials
1733 WHERE entity_type = p_entity_type_tbl(i)
1734 AND entity_id = p_entity_id_tbl(i)
1735 AND price_differential_num = p_price_diff_num_tbl(i);
1736
1737 d_position := 20;
1738
1739 DELETE FROM po_session_gt
1740 WHERE key = l_key
1741 RETURNING num1 BULK COLLECT INTO l_index_tbl;
1742
1743 d_position := 30;
1744
1745 FOR i IN 1..l_index_tbl.COUNT
1746 LOOP
1747 IF (PO_LOG.d_stmt) THEN
1748 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
1749 PO_LOG.stmt(d_module, d_position, 'price diff number is not unique',
1750 p_price_diff_num_tbl(l_index_tbl(i)));
1751 END IF;
1752
1753 x_price_diff_num_unique_tbl(l_index_tbl(i)) := FND_API.g_FALSE;
1754 END LOOP;
1755
1756 d_position := 40;
1757
1758 -- last, check interface table records that are before current records
1759 FOR i IN 1..p_price_diff_num_tbl.COUNT
1760 LOOP
1761 IF (p_price_diff_num_tbl(i) IS NOT NULL AND p_entity_type_tbl(i) IS NOT NULL AND
1762 p_entity_id_tbl(i) IS NOT NULL) THEN
1763 -- check whether same combination of entity_type, entity_id and
1764 -- price_diff_num appears in previous records
1765 IF (l_price_diff_num_exist_tbl.EXISTS(p_entity_type_tbl(i)) AND
1766 l_price_diff_num_exist_tbl(p_entity_type_tbl(i)).EXISTS(p_entity_id_tbl(i)) AND
1767 l_price_diff_num_exist_tbl(p_entity_type_tbl(i))(p_entity_id_tbl(i)).EXISTS
1768 (p_price_diff_num_tbl(i))) THEN
1769 IF (PO_LOG.d_stmt) THEN
1770 PO_LOG.stmt(d_module, d_position, 'index', i);
1771 PO_LOG.stmt(d_module, d_position, 'price diff number is not unique',
1772 p_price_diff_num_tbl(i));
1773 END IF;
1774
1775 x_price_diff_num_unique_tbl(i) := FND_API.g_FALSE;
1776 END IF;
1777
1778 -- set current combination in the hashtable
1779 l_price_diff_num_exist_tbl(p_entity_type_tbl(i))
1780 (p_entity_id_tbl(i))(p_price_diff_num_tbl(i)) := FND_API.g_TRUE;
1781 END IF;
1782 END LOOP;
1783
1784 d_position := 50;
1785
1786 IF (PO_LOG.d_proc) THEN
1787 PO_LOG.proc_end (d_module);
1788 END IF;
1789
1790 EXCEPTION
1791 WHEN OTHERS THEN
1792 PO_MESSAGE_S.add_exc_msg
1793 (
1794 p_pkg_name => d_pkg_name,
1795 p_procedure_name => d_api_name || '.' || d_position
1796 );
1797 RAISE;
1798 END check_price_diff_num_unique;
1799 --------------------------------------------------------------------------
1800 --Start of Comments
1801 --Name: default_who_columns
1802 --Pre-reqs: None
1803 --Modifies:
1804 --Locks:
1805 -- None
1806 --Function:
1807 -- default standard who columns;
1808 -- this procedure is shared by all entities
1809 --Parameters:
1810 --IN: None
1811 --IN OUT:
1812 -- x_last_update_date_tbl
1813 -- list of last_update_date values within the batch
1814 -- x_last_updated_by_tbl
1815 -- list of last_updated_by values within the batch
1816 -- x_last_update_login_tbl
1817 -- list of last_update_login values within the batch
1818 -- x_creation_date_tbl
1819 -- list of creation_date values within the batch
1820 -- x_created_by_tbl
1821 -- list of created_by values within the batch
1822 -- x_request_id_tbl
1823 -- list of request_id values within the batch
1824 -- x_program_application_id_tbl
1825 -- list of program_application_id values within the batch
1826 -- x_program_id_tbl
1827 -- list of program_id values within the batch
1828 -- x_program_update_date_tbl
1829 -- list of program_update_date values within the batch
1830 --OUT: None
1831 --Returns: None
1832 --Notes:
1833 --Testing:
1834 --End of Comments
1835 --------------------------------------------------------------------------
1836 PROCEDURE default_who_columns(
1837 x_last_update_date_tbl IN OUT NOCOPY PO_TBL_DATE,
1838 x_last_updated_by_tbl IN OUT NOCOPY PO_TBL_NUMBER,
1839 x_last_update_login_tbl IN OUT NOCOPY PO_TBL_NUMBER,
1840 x_creation_date_tbl IN OUT NOCOPY PO_TBL_DATE,
1841 x_created_by_tbl IN OUT NOCOPY PO_TBL_NUMBER,
1842 x_request_id_tbl IN OUT NOCOPY PO_TBL_NUMBER,
1843 x_program_application_id_tbl IN OUT NOCOPY PO_TBL_NUMBER,
1844 x_program_id_tbl IN OUT NOCOPY PO_TBL_NUMBER,
1845 x_program_update_date_tbl IN OUT NOCOPY PO_TBL_DATE
1846 ) IS
1847
1848 d_api_name CONSTANT VARCHAR2(30) := 'default_who_columns';
1849 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1850 d_position NUMBER;
1851
1852 BEGIN
1853 d_position := 0;
1854
1855 IF (PO_LOG.d_proc) THEN
1856 PO_LOG.proc_begin(d_module);
1857 END IF;
1858
1859 FOR i IN 1..x_last_update_date_tbl.COUNT
1860 LOOP
1861 x_last_update_date_tbl(i) := NVL(x_last_update_date_tbl(i), sysdate);
1862 x_last_updated_by_tbl(i) := NVL(x_last_updated_by_tbl(i), FND_GLOBAL.user_id);
1863 x_last_update_login_tbl(i) := NVL(x_last_update_login_tbl(i), FND_GLOBAL.login_id);
1864 x_creation_date_tbl(i) := NVL(x_creation_date_tbl(i), sysdate);
1865 x_created_by_tbl(i) := NVL(x_created_by_tbl(i), FND_GLOBAL.user_id);
1866 x_request_id_tbl(i) := NVL(x_request_id_tbl(i), FND_GLOBAL.conc_request_id);
1867 x_program_application_id_tbl(i) := NVL(x_program_application_id_tbl(i), FND_GLOBAL.prog_appl_id);
1868 x_program_id_tbl(i) := NVL(x_program_id_tbl(i), FND_GLOBAL.conc_program_id);
1869 x_program_update_date_tbl(i) := NVL(x_program_update_date_tbl(i), sysdate);
1870 END LOOP;
1871
1872 IF (PO_LOG.d_proc) THEN
1873 PO_LOG.proc_end (d_module);
1874 END IF;
1875
1876 EXCEPTION
1877 WHEN OTHERS THEN
1878 PO_MESSAGE_S.add_exc_msg
1879 (
1880 p_pkg_name => d_pkg_name,
1881 p_procedure_name => d_api_name || '.' || d_position
1882 );
1883 RAISE;
1884 END default_who_columns;
1885
1886 --------------------------------------------------------------------------
1887 --Start of Comments
1888 --Name: get_next_set_process_id
1889 --Pre-reqs: None
1890 --Modifies:
1891 --Locks:
1892 -- None
1893 --Function:
1894 -- Get new set process id to insert records into item interface table;
1895 -- This is called in item creation
1896 --Parameters:
1897 --IN: None
1898 --IN OUT: None
1899 --OUT: None
1900 --Returns:
1901 -- new set process id from sequence po_items_interface_sets_s
1902 --Notes:
1903 --Testing:
1904 --End of Comments
1905 --------------------------------------------------------------------------
1906 FUNCTION get_next_set_process_id
1907 RETURN NUMBER
1908 IS
1909
1910 d_api_name CONSTANT VARCHAR2(30) := 'get_next_set_process_idd';
1911 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1912 d_position NUMBER;
1913
1914 l_next_set_process_id NUMBER;
1915 BEGIN
1916 d_position := 0;
1917
1918 IF (PO_LOG.d_proc) THEN
1919 PO_LOG.proc_begin(d_module);
1920 END IF;
1921
1922 SELECT po_items_interface_sets_s.nextval
1923 INTO l_next_set_process_id
1924 FROM DUAL;
1925
1926 IF (PO_LOG.d_proc) THEN
1927 PO_LOG.proc_return(d_module, l_next_set_process_id);
1928 END IF;
1929
1930 RETURN l_next_set_process_id;
1931 END get_next_set_process_id;
1932
1933 -----------------------------------------------------------------------
1934 --Start of Comments
1935 --Name: get_currency_precision
1936 --Function: get precision of the currency code
1937 --Parameters:
1938 --IN:
1939 -- p_currency_code
1940 -- currency for which we get the precision value
1941 --IN OUT:
1942 -- x_precision_tbl
1943 -- hashtable of precisions based on currency code
1944 --OUT:
1945 --End of Comments
1946 ------------------------------------------------------------------------
1947 FUNCTION get_currency_precision
1948 (
1949 p_currency_code IN VARCHAR2,
1950 x_precision_tbl IN OUT NOCOPY PO_PDOI_TYPES.varchar_index_tbl_type
1951 ) RETURN NUMBER
1952 IS
1953
1954 d_api_name CONSTANT VARCHAR2(30) := 'get_currency_precision';
1955 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1956 d_position NUMBER;
1957
1958 l_precision NUMBER;
1959 BEGIN
1960 d_position := 0;
1961
1962 IF (PO_LOG.d_proc) THEN
1963 PO_LOG.proc_begin(d_module, 'p_currency_code', p_currency_code);
1964 END IF;
1965
1966 IF (x_precision_tbl.EXISTS(p_currency_code)) THEN
1967 d_position := 10;
1968
1969 IF (PO_LOG.d_proc) THEN
1970 PO_LOG.proc_return(d_module, x_precision_tbl(p_currency_code));
1971 END IF;
1972
1973 return x_precision_tbl(p_currency_code);
1974 ELSE
1975 d_position := 20;
1976
1977 SELECT precision
1978 INTO l_precision
1979 FROM fnd_currencies
1980 WHERE currency_code = p_currency_code;
1981
1982 -- set new value in hashtable
1983 x_precision_tbl(p_currency_code) := l_precision;
1984
1985 IF (PO_LOG.d_proc) THEN
1986 PO_LOG.proc_return(d_module, l_precision);
1987 END IF;
1988
1989 return l_precision;
1990 END IF;
1991 EXCEPTION
1992 WHEN NO_DATA_FOUND THEN
1993 return 0;
1994 WHEN OTHERS THEN
1995 PO_MESSAGE_S.add_exc_msg
1996 (
1997 p_pkg_name => d_pkg_name,
1998 p_procedure_name => d_api_name || '.' || d_position
1999 );
2000 RAISE;
2001 END get_currency_precision;
2002
2003 END PO_PDOI_MAINPROC_UTL_PVT;
[Home] [Help]