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