1 PACKAGE BODY PO_DOCUMENT_TOTALS_PVT AS
2 -- $Header: PO_DOCUMENT_TOTALS_PVT.plb 120.11 2006/07/18 12:17:54 scolvenk noship $
3 -------------------------------------------------------------------------------
4 -- Package private constants
5 -------------------------------------------------------------------------------
6 -- Debug constants
7 D_PACKAGE_BASE CONSTANT VARCHAR2(50) :=
8 PO_LOG.get_package_base('PO_DOCUMENT_TOTALS_PVT');
9
10 -- Shipment type constants
11 C_ship_type_STANDARD CONSTANT
12 PO_LINE_LOCATIONS_ALL.shipment_type%TYPE
13 := PO_CORE_S.g_ship_type_STANDARD;
14 C_ship_type_PLANNED CONSTANT
15 PO_LINE_LOCATIONS_ALL.shipment_type%TYPE
16 := PO_CORE_S.g_ship_type_PLANNED;
17 C_ship_type_SCHEDULED CONSTANT
18 PO_LINE_LOCATIONS_ALL.shipment_type%TYPE
19 := PO_CORE_S.g_ship_type_SCHEDULED;
20 C_ship_type_BLANKET CONSTANT
21 PO_LINE_LOCATIONS_ALL.shipment_type%TYPE
22 := PO_CORE_S.g_ship_type_BLANKET;
23 C_ship_type_PREPAYMENT CONSTANT
24 PO_LINE_LOCATIONS_ALL.shipment_type%TYPE
25 := PO_CORE_S.g_ship_type_PREPAYMENT;
26
27 -- Payment type constants
28 C_payment_type_MILESTONE CONSTANT
29 PO_LINE_LOCATIONS_ALL.payment_type%TYPE
30 := PO_CORE_S.g_payment_type_MILESTONE;
31
32 C_payment_type_RATE CONSTANT
33 PO_LINE_LOCATIONS_ALL.payment_type%TYPE
34 := PO_CORE_S.g_payment_type_RATE;
35
36 C_payment_type_LUMPSUM CONSTANT
37 PO_LINE_LOCATIONS_ALL.payment_type%TYPE
38 := PO_CORE_S.g_payment_type_LUMPSUM;
39
40 -------------------------------------------------------------------------------
41 -- Spec definitions for private procedures
42 -------------------------------------------------------------------------------
43 PROCEDURE do_org_currency_setups(
44 p_doc_level IN VARCHAR2
45 , p_doc_level_id IN NUMBER
46 , x_currency_precision OUT NOCOPY NUMBER
47 , x_min_acct_unit OUT NOCOPY NUMBER
48 );
49
50
51 --TODO: obsolete the following signatures below once impacts to all
52 --callers of the get_order_totals have been handled:
53 -- * get_totals
54 -- * populate_temp_table
55 -- * prepare_temp_table_data
56 -- * calculate_totals
57 -- * clear_temp_table
58 PROCEDURE get_totals(
59 p_doc_type IN VARCHAR2,
60 p_doc_subtype IN VARCHAR2,
61 p_doc_level IN VARCHAR2,
62 p_doc_level_id IN NUMBER,
63 p_data_source IN VARCHAR2,
64 p_doc_revision_num IN NUMBER,
65 x_quantity_total OUT NOCOPY NUMBER,
66 x_amount_total OUT NOCOPY NUMBER,
67 x_quantity_delivered OUT NOCOPY NUMBER,
68 x_amount_delivered OUT NOCOPY NUMBER,
69 x_quantity_received OUT NOCOPY NUMBER,
70 x_amount_received OUT NOCOPY NUMBER,
71 x_quantity_shipped OUT NOCOPY NUMBER,
72 x_amount_shipped OUT NOCOPY NUMBER,
73 x_quantity_billed OUT NOCOPY NUMBER,
74 x_amount_billed OUT NOCOPY NUMBER,
75 x_quantity_financed OUT NOCOPY NUMBER,
76 x_amount_financed OUT NOCOPY NUMBER,
77 x_quantity_recouped OUT NOCOPY NUMBER,
78 x_amount_recouped OUT NOCOPY NUMBER,
79 x_retainage_withheld_amount OUT NOCOPY NUMBER,
80 x_retainage_released_amount OUT NOCOPY NUMBER
81 );
82
83
84 PROCEDURE populate_temp_table(
85 p_doc_type IN VARCHAR2,
86 p_doc_level IN VARCHAR2,
87 p_doc_level_id IN NUMBER,
88 p_data_source IN VARCHAR2,
89 p_doc_revision_num IN NUMBER,
90 x_temp_table_key OUT NOCOPY NUMBER,
91 x_count OUT NOCOPY NUMBER
92 );
93
94
95 PROCEDURE prepare_temp_table_data(
96 p_temp_table_key IN NUMBER,
97 p_document_id IN NUMBER
98 );
99
100 PROCEDURE calculate_totals(
101 p_temp_table_key IN NUMBER,
102 p_document_id IN NUMBER,
103 p_doc_level IN VARCHAR2,
104 x_quantity_total OUT NOCOPY NUMBER,
105 x_amount_total OUT NOCOPY NUMBER,
106 x_quantity_delivered OUT NOCOPY NUMBER,
107 x_amount_delivered OUT NOCOPY NUMBER,
108 x_quantity_received OUT NOCOPY NUMBER,
109 x_amount_received OUT NOCOPY NUMBER,
110 x_quantity_shipped OUT NOCOPY NUMBER,
111 x_amount_shipped OUT NOCOPY NUMBER,
112 x_quantity_billed OUT NOCOPY NUMBER,
113 x_amount_billed OUT NOCOPY NUMBER,
114 x_quantity_financed OUT NOCOPY NUMBER,
115 x_amount_financed OUT NOCOPY NUMBER,
116 x_quantity_recouped OUT NOCOPY NUMBER,
117 x_amount_recouped OUT NOCOPY NUMBER,
118 x_retainage_withheld_amount OUT NOCOPY NUMBER,
119 x_retainage_released_amount OUT NOCOPY NUMBER
120 );
121
122 PROCEDURE clear_temp_table(
123 p_temp_table_key IN NUMBER
124 );
125
126
127
128 -------------------------------------------------------------------------------
129 -- Procedure body definitions
130 -------------------------------------------------------------------------------
131
132
133 -------------------------------------------------------------------------------
134 --Start of Comments
135 --Name: getAmountOrdered
136 --Pre-reqs:
137 -- None
138 --Modifies:
139 -- None
140 --Locks:
141 -- None
142 --Function:
143 -- Based on given doc level and id, calculates the total amount ordered
144 -- for that entity
145 -- The API supports only Standard POs (both non-CWP and CWP)
146 --Parameters:
147 --IN:
148 --p_doc_level
149 -- The type of ids that are being passed. Use g_doc_level_<>
150 -- HEADER
151 -- LINE
152 -- SHIPMENT
153 -- DISTRIBUTION
154 --p_doc_level_id
155 -- Id of the doc level type for which to calculate totals
156 --p_data_source
157 -- Use g_data_source_<> constants
158 -- g_data_source_TRANSACTION: calculate totals based off of
159 -- data values in the main txn tables
160 -- g_data_source_ARCHIVE: calculate totals based off of
161 -- data values in the archive tables
162 --p_doc_revision_num
163 -- This is a DEFAULT NULL paramter
164 -- It is ignored if p_data_source is TRANSACTION
165 -- If p_data_source is ARCHIVE, then
166 -- The revision number of the header in the archive table.
167 -- If this parameter is passed as null, the latest version in the
168 -- archive table is assumed.
169 --Testing:
170 --
171 --End of Comments
172 -------------------------------------------------------------------------------
173 FUNCTION getAmountOrdered(
174 p_doc_level IN VARCHAR2
175 , p_doc_level_id IN NUMBER
176 , p_data_source IN VARCHAR2
177 , p_doc_revision_num IN NUMBER --default null
178 ) RETURN NUMBER
179 IS
180 d_mod CONSTANT VARCHAR2(100) :=
181 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'getAmountOrdered');
182 d_position NUMBER := 0;
183 l_return_val NUMBER := 0;
184 l_precision GL_CURRENCIES.precision%TYPE;
185 l_mau GL_CURRENCIES.minimum_accountable_unit%TYPE;
186 BEGIN
187
188 IF PO_LOG.d_proc THEN
189 PO_LOG.proc_begin(d_mod,'p_doc_level',p_doc_level);
190 PO_LOG.proc_begin(d_mod,'p_doc_level_id',p_doc_level_id);
191 PO_LOG.proc_begin(d_mod,'p_data_source',p_data_source);
192 PO_LOG.proc_begin(d_mod,'p_doc_revision_num',p_doc_revision_num);
193 END IF;
194
195 do_org_currency_setups(
196 p_doc_level => p_doc_level
197 , p_doc_level_id => p_doc_level_id
198 , x_currency_precision => l_precision
199 , x_min_acct_unit => l_mau
200 );
201
202 d_position := 10;
203 IF PO_LOG.d_stmt THEN
204 PO_LOG.stmt(d_mod,d_position,'l_precision:',l_precision);
205 PO_LOG.stmt(d_mod,d_position,'l_mau:',l_mau);
206 END IF;
207
208 IF p_doc_level = g_doc_level_HEADER THEN
209
210 IF p_data_source = g_data_source_TRANSACTION THEN
211
212 d_position := 20;
213
214 SELECT SUM(
215 DECODE(POL.matching_basis
216 , 'AMOUNT', pol.amount
217 , --QUANTITY
218 nvl2(l_mau
219 , round(pol.quantity*pol.unit_price/l_mau) * l_mau
220 , round((pol.quantity*pol.unit_price),l_precision)) )) -- Bug# 5378134
221 INTO l_return_val
222 FROM po_lines_all pol
223 WHERE pol.po_header_id = p_doc_level_id
224 ;
225
226 ELSIF p_data_source = g_data_source_ARCHIVE THEN
227
228 d_position := 30;
229
230 SELECT SUM(
231 DECODE(POL.matching_basis
232 , 'AMOUNT', pol.amount
233 , --QUANTITY
234 nvl2(l_mau
235 , round(pol.quantity*pol.unit_price/l_mau) * l_mau
236 , round((pol.quantity*pol.unit_price),l_precision)) )) -- Bug# 5378134
237 INTO l_return_val
238 FROM po_lines_archive_all pol
239 WHERE pol.po_header_id = p_doc_level_id
240 AND ( (p_doc_revision_num IS NULL and pol.latest_external_flag = 'Y')
241 OR (p_doc_revision_num IS NOT NULL
242 AND POL.revision_num =
243 (SELECT max(POL2.revision_num)
244 FROM po_lines_archive_all pol2
245 WHERE pol2.po_line_id = pol.po_line_id
246 AND pol2.revision_num <= p_doc_revision_num)
247 )
248 )
249 ;
250
251 ELSE
252
253 d_position := 40;
254 IF PO_LOG.d_stmt THEN
255 PO_LOG.stmt(d_mod,d_position,'Invalid data source: ', p_data_source);
256 END IF;
257
258 END IF; --p_data_source check
259
260 ELSIF p_doc_level = g_doc_level_LINE THEN
261
262 IF p_data_source = g_data_source_TRANSACTION THEN
263
264 d_position := 50;
265
266 SELECT DECODE(POL.matching_basis
267 , 'AMOUNT', pol.amount
268 , --QUANTITY
269 nvl2(l_mau
270 , round(pol.quantity*pol.unit_price/l_mau) * l_mau
271 , round((pol.quantity*pol.unit_price),l_precision)) ) -- Bug# 5378134
272 INTO l_return_val
273 FROM po_lines_all pol
274 WHERE pol.po_line_id = p_doc_level_id
275 ;
276
277 ELSIF p_data_source = g_data_source_ARCHIVE THEN
278
279 d_position := 60;
280
281 SELECT DECODE(POL.matching_basis
282 , 'AMOUNT', pol.amount
283 , --QUANTITY
284 nvl2(l_mau
285 , round(pol.quantity*pol.unit_price/l_mau) * l_mau
286 , round((pol.quantity*pol.unit_price),l_precision)) ) -- Bug# 5378134
287 INTO l_return_val
288 FROM po_lines_archive_all pol
289 WHERE pol.po_line_id = p_doc_level_id
290 AND ( (p_doc_revision_num IS NULL and pol.latest_external_flag = 'Y')
291 OR (p_doc_revision_num IS NOT NULL
292 AND POL.revision_num =
293 (SELECT max(POL2.revision_num)
294 FROM po_lines_archive_all pol2
295 WHERE pol2.po_line_id = pol.po_line_id
296 AND pol2.revision_num <= p_doc_revision_num)
297 )
298 )
299 ;
300
301 ELSE
302
303 d_position := 70;
304 IF PO_LOG.d_stmt THEN
305 PO_LOG.stmt(d_mod,d_position,'Invalid data source: ', p_data_source);
306 END IF;
307
308 END IF; --p_data_source check
309
310 ELSIF p_doc_level = g_doc_level_SHIPMENT THEN
311
312 IF p_data_source = g_data_source_TRANSACTION THEN
313
314 d_position := 80;
315
316 SELECT DECODE(POLL.matching_basis
317 , 'AMOUNT', poll.amount - nvl(poll.amount_cancelled,0)
318 , --QUANTITY
319 nvl2(l_mau
320 , round((poll.quantity-nvl(poll.quantity_cancelled,0))
321 *poll.price_override/l_mau) * l_mau
322 , round(((poll.quantity-nvl(poll.quantity_cancelled,0))
323 *poll.price_override),l_precision) )) -- Bug# 5378134
324 INTO l_return_val
325 FROM po_line_locations_all poll
326 WHERE poll.line_location_id = p_doc_level_id
327 ;
328
329 ELSIF p_data_source = g_data_source_ARCHIVE THEN
330
331 d_position := 90;
332
333 SELECT DECODE(POLL.matching_basis
334 , 'AMOUNT', poll.amount - nvl(poll.amount_cancelled,0)
335 , --QUANTITY
336 nvl2(l_mau
337 , round((poll.quantity-nvl(poll.quantity_cancelled,0))
338 *poll.price_override/l_mau) * l_mau
339 , round(((poll.quantity-nvl(poll.quantity_cancelled,0))
340 *poll.price_override),l_precision) )) -- Bug# 5378134
341 INTO l_return_val
342 FROM po_line_locations_archive_all poll
343 WHERE poll.line_location_id = p_doc_level_id
344 AND ( (p_doc_revision_num IS NULL and poll.latest_external_flag = 'Y')
345 OR (p_doc_revision_num IS NOT NULL
346 AND POLL.revision_num =
347 (SELECT max(POLL2.revision_num)
348 FROM po_line_locations_archive_all poll2
349 WHERE poll2.line_location_id = poll.line_location_id
350 AND poll2.revision_num <= p_doc_revision_num)
351 )
352 )
353 ;
354
355 ELSE
356
357 d_position := 100;
358 IF PO_LOG.d_stmt THEN
359 PO_LOG.stmt(d_mod,d_position,'Invalid data source: ', p_data_source);
360 END IF;
361
362 END IF; --p_data_source check
363
364 ELSIF p_doc_level = g_doc_level_DISTRIBUTION THEN
365
366 IF p_data_source = g_data_source_TRANSACTION THEN
367
368 d_position := 110;
369
370 SELECT DECODE(POLL.matching_basis
371 , 'AMOUNT', pod.amount_ordered - nvl(pod.amount_cancelled,0)
372 , --QUANTITY
373 nvl2(l_mau
374 , round((pod.quantity_ordered-nvl(pod.quantity_cancelled,0))
375 *poll.price_override/l_mau) * l_mau
376 , round(((pod.quantity_ordered-nvl(pod.quantity_cancelled,0))
377 *poll.price_override),l_precision) )) -- Bug# 5378134
378 INTO l_return_val
379 FROM po_line_locations_all poll
380 , po_distributions_all pod
381 WHERE pod.po_distribution_id = p_doc_level_id
382 AND poll.line_location_id = pod.line_location_id
383 ;
384
385 ELSIF p_data_source = g_data_source_ARCHIVE THEN
386
387 d_position := 120;
388
389 SELECT DECODE(POLL.matching_basis
390 , 'AMOUNT', pod.amount_ordered - nvl(pod.amount_cancelled,0)
391 , --QUANTITY
392 nvl2(l_mau
393 , round((pod.quantity_ordered-nvl(pod.quantity_cancelled,0))
394 *poll.price_override/l_mau) * l_mau
395 , round(((pod.quantity_ordered-nvl(pod.quantity_cancelled,0))
396 *poll.price_override),l_precision) )) -- Bug# 5378134
397 INTO l_return_val
398 FROM po_line_locations_archive_all poll
399 , po_distributions_archive_all pod
400 WHERE pod.po_distribution_id = p_doc_level_id
401 AND poll.line_location_id = pod.line_location_id
402 AND ( (p_doc_revision_num IS NULL
403 AND pod.latest_external_flag = 'Y'
404 AND poll.latest_external_flag = 'Y')
405 OR (p_doc_revision_num IS NOT NULL
406 AND POD.revision_num =
407 (SELECT max(POD2.revision_num)
408 FROM po_distributions_archive_all pod2
409 WHERE pod2.po_distribution_id = pod.po_distribution_id
410 AND pod2.revision_num <= p_doc_revision_num)
411 AND POLL.revision_num =
412 (SELECT max(POLL2.revision_num)
413 FROM po_line_locations_archive_all poll2
414 WHERE poll2.line_location_id = poll.line_location_id
415 AND poll2.revision_num <= p_doc_revision_num)
416 )
417 )
418 ;
419
420 ELSE
421
422 d_position := 130;
423 IF PO_LOG.d_stmt THEN
424 PO_LOG.stmt(d_mod,d_position,'Invalid data source: ', p_data_source);
425 END IF;
426
427 END IF; --p_data_source check
428
429 ELSE
430
431 d_position := 140;
432 IF PO_LOG.d_stmt THEN
433 PO_LOG.stmt(d_mod,d_position,'Invalid doc level: ', p_doc_level);
434 END IF;
435
436 END IF; --p_doc_level check
437
438 IF PO_LOG.d_proc THEN
439 PO_LOG.proc_end(d_mod, 'l_return_val', l_return_val);
440 END IF;
441
442 RETURN l_return_val;
443
444 EXCEPTION
445 WHEN OTHERS THEN
446 IF PO_LOG.d_exc THEN
447 PO_LOG.exc(d_mod,d_position,NULL);
448 END IF;
449 RAISE;
450 END getAmountOrdered;
451
452
453
454 -------------------------------------------------------------------------------
455 --Start of Comments
456 --Name: getAmountApprovedForLine
457 --Pre-reqs:
458 -- None
459 --Modifies:
460 -- None
461 --Locks:
462 -- None
463 --Function:
464 -- Calculates the Approved Amount for a given PO Line.
465 -- For a Complex Work PO, Approved Amount is the amount of work
466 -- confirmed against the UI-visible Pay Items of the line.
467 -- For a non-Complex Work PO, Approved Amount is always 0.
468 -- The API supports only Standard PO document types.
469 --Parameters:
470 --IN:
471 --p_line_id
472 -- The ID of the line for which to calculate the approved amount
473 --p_data_source
474 -- Use g_data_source_<> constants
475 -- g_data_source_TRANSACTION: calculate totals based off of
476 -- data values in the main txn tables
477 -- g_data_source_ARCHIVE: calculate totals based off of
478 -- data values in the archive tables
479 --p_doc_revision_num
480 -- This is a DEFAULT NULL paramter
481 -- It is ignored if p_data_source is TRANSACTION
482 -- If p_data_source is ARCHIVE, then
483 -- The revision number of the header in the archive table.
484 -- If this parameter is passed as null, the latest version in the
485 -- archive table is assumed.
486 --Testing:
487 --
488 --End of Comments
489 -------------------------------------------------------------------------------
490 FUNCTION getAmountApprovedForLine(
491 p_line_id IN NUMBER
492 , p_data_source IN VARCHAR2
493 , p_doc_revision_num IN NUMBER --default null
494 ) RETURN NUMBER
495 IS
496 d_mod CONSTANT VARCHAR2(100) :=
497 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'getAmountApprovedForLine');
498 d_position NUMBER := 0;
499 l_return_val NUMBER := 0;
500 l_precision GL_CURRENCIES.precision%TYPE;
501 l_mau GL_CURRENCIES.minimum_accountable_unit%TYPE;
502 BEGIN
503
504 IF PO_LOG.d_proc THEN
505 PO_LOG.proc_begin(d_mod,'p_line_id',p_line_id);
506 PO_LOG.proc_begin(d_mod,'p_data_source',p_data_source);
507 PO_LOG.proc_begin(d_mod,'p_doc_revision_num',p_doc_revision_num);
508 END IF;
509
510 do_org_currency_setups(
511 p_doc_level => g_doc_level_LINE
512 , p_doc_level_id => p_line_id
513 , x_currency_precision => l_precision
514 , x_min_acct_unit => l_mau
515 );
516
517 d_position := 5;
518 IF PO_LOG.d_stmt THEN
519 PO_LOG.stmt(d_mod,d_position,'l_precision:',l_precision);
520 PO_LOG.stmt(d_mod,d_position,'l_mau:',l_mau);
521 END IF;
522
523 IF p_data_source = g_data_source_TRANSACTION THEN
524
525 d_position := 10;
526
527 BEGIN
528 SELECT SUM(
529 DECODE(poll.matching_basis
530 , 'AMOUNT', poll.amount_received
531 , --QUANTITY
532 nvl2(l_mau
533 , round(poll.quantity_received*poll.price_override/l_mau) * l_mau
534 , round((poll.quantity_received*poll.price_override),l_precision)) )) --Bug5391045
535 INTO l_return_val
536 FROM po_line_locations_all poll
537 WHERE poll.po_line_id = p_line_id
538 AND nvl(poll.payment_type, 'NULL') IN ('RATE', 'LUMPSUM', 'MILESTONE') --Bug5391045
539 ;
540 EXCEPTION
541 WHEN NO_DATA_FOUND THEN
542 IF PO_LOG.d_stmt THEN
543 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
544 END IF;
545 l_return_val := 0;
546 END;
547
548 ELSIF p_data_source = g_data_source_ARCHIVE THEN
549
550 d_position := 20;
551
552 BEGIN
553 SELECT SUM(
554 DECODE(poll.matching_basis
555 , 'AMOUNT', poll.amount_received
556 , --QUANTITY
557 nvl2(l_mau
558 , round(poll.quantity_received*poll.price_override/l_mau) * l_mau
559 , round((poll.quantity_received*poll.price_override),l_precision)) )) --Bug5391045
560 INTO l_return_val
561 FROM po_line_locations_archive_all poll
562 WHERE poll.po_line_id = p_line_id
563 AND nvl(poll.payment_type, 'NULL') IN ('RATE', 'LUMPSUM', 'MILESTONE') --Bug5391045
564 AND ( (p_doc_revision_num IS NULL and poll.latest_external_flag = 'Y')
565 OR (p_doc_revision_num IS NOT NULL
566 AND poll.revision_num =
567 (SELECT max(POLL2.revision_num)
568 FROM po_line_locations_archive_all poll2
569 WHERE poll2.line_location_id = poll.line_location_id
570 AND poll2.revision_num <= p_doc_revision_num)
571 )
572 )
573 ;
574 EXCEPTION
575 WHEN NO_DATA_FOUND THEN
576 IF PO_LOG.d_stmt THEN
577 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
578 END IF;
579 l_return_val := 0;
580 END;
581
582 ELSE
583
584 d_position := 30;
585 IF PO_LOG.d_stmt THEN
586 PO_LOG.stmt(d_mod,d_position,'Invalid data source: ', p_data_source);
587 END IF;
588
589 END IF;
590
591 IF PO_LOG.d_proc THEN
592 PO_LOG.proc_end(d_mod, 'l_return_val', l_return_val);
593 END IF;
594
595 RETURN l_return_val;
596
597 EXCEPTION
598 WHEN OTHERS THEN
599 IF PO_LOG.d_exc THEN
600 PO_LOG.exc(d_mod,d_position,NULL);
601 END IF;
602 RAISE;
603 END getAmountApprovedForLine;
604
605
606
607 -------------------------------------------------------------------------------
608 --Start of Comments
609 --Name: getAmountApprovedForHeader
610 --Pre-reqs:
611 -- None
612 --Modifies:
613 -- None
614 --Locks:
615 -- None
616 --Function:
617 -- Calculates the Approved Amount for a given PO Header.
618 -- For a Complex Work PO, Approved Amount is the amount of work
619 -- confirmed against the UI-visible Pay Items of the PO lines.
620 -- For a non-Complex Work PO, Approved Amount is always 0.
621 -- The API supports only Standard PO document types.
622 --Parameters:
623 --IN:
624 --p_line_id
625 -- The ID of the SPO Header for which to calculate the approved amount
626 --p_data_source
627 -- Use g_data_source_<> constants
628 -- g_data_source_TRANSACTION: calculate totals based off of
629 -- data values in the main txn tables
630 -- g_data_source_ARCHIVE: calculate totals based off of
631 -- data values in the archive tables
632 --p_doc_revision_num
633 -- This is a DEFAULT NULL paramter
634 -- It is ignored if p_data_source is TRANSACTION
635 -- If p_data_source is ARCHIVE, then
636 -- The revision number of the header in the archive table.
637 -- If this parameter is passed as null, the latest version in the
638 -- archive table is assumed.
639 --Testing:
640 --
641 --End of Comments
642 -------------------------------------------------------------------------------
643 FUNCTION getAmountApprovedForHeader(
644 p_header_id IN NUMBER
645 , p_data_source IN VARCHAR2
646 , p_doc_revision_num IN NUMBER --default null
647 ) RETURN NUMBER
648 IS
649 d_mod CONSTANT VARCHAR2(100) :=
650 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'getAmountApprovedForHeader');
651 d_position NUMBER := 0;
652 l_return_val NUMBER := 0;
653 l_precision GL_CURRENCIES.precision%TYPE;
654 l_mau GL_CURRENCIES.minimum_accountable_unit%TYPE;
655 BEGIN
656
657 IF PO_LOG.d_proc THEN
658 PO_LOG.proc_begin(d_mod,'p_header_id',p_header_id);
659 PO_LOG.proc_begin(d_mod,'p_data_source',p_data_source);
660 PO_LOG.proc_begin(d_mod,'p_doc_revision_num',p_doc_revision_num);
661 END IF;
662
663 do_org_currency_setups(
664 p_doc_level => g_doc_level_HEADER
665 , p_doc_level_id => p_header_id
666 , x_currency_precision => l_precision
667 , x_min_acct_unit => l_mau
668 );
669
670 d_position := 5;
671 IF PO_LOG.d_stmt THEN
672 PO_LOG.stmt(d_mod,d_position,'l_precision:',l_precision);
673 PO_LOG.stmt(d_mod,d_position,'l_mau:',l_mau);
674 END IF;
675
676 IF p_data_source = g_data_source_TRANSACTION THEN
677
678 d_position := 10;
679
680 BEGIN
681 SELECT SUM(
682 DECODE(poll.matching_basis
683 , 'AMOUNT', poll.amount_received
684 , --QUANTITY
685 nvl2(l_mau
686 , round(poll.quantity_received*poll.price_override/l_mau) * l_mau
687 , round((poll.quantity_received*poll.price_override),l_precision)) )) --Bug5391045
688 INTO l_return_val
689 FROM po_line_locations_all poll
690 WHERE poll.po_header_id = p_header_id
691 AND nvl(poll.payment_type, 'NULL') IN ('RATE', 'LUMPSUM', 'MILESTONE') --Bug5391045
692 ;
693 EXCEPTION
694 WHEN NO_DATA_FOUND THEN
695 IF PO_LOG.d_stmt THEN
696 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
697 END IF;
698 l_return_val := 0;
699 END;
700
701 ELSIF p_data_source = g_data_source_ARCHIVE THEN
702
703 d_position := 20;
704
705 BEGIN
706 SELECT SUM(
707 DECODE(poll.matching_basis
708 , 'AMOUNT', poll.amount_received
709 , --QUANTITY
710 nvl2(l_mau
711 , round(poll.quantity_received*poll.price_override/l_mau) * l_mau
712 , round((poll.quantity_received*poll.price_override),l_precision)) )) --Bug5391045
713 INTO l_return_val
714 FROM po_line_locations_archive_all poll
715 WHERE poll.po_header_id = p_header_id
716 AND nvl(poll.payment_type, 'NULL') IN ('RATE', 'LUMPSUM', 'MILESTONE') --Bug5391045
717 AND ( (p_doc_revision_num IS NULL and poll.latest_external_flag = 'Y')
718 OR (p_doc_revision_num IS NOT NULL
719 AND poll.revision_num =
720 (SELECT max(POLL2.revision_num)
721 FROM po_line_locations_archive_all poll2
722 WHERE poll2.line_location_id = poll.line_location_id
723 AND poll2.revision_num <= p_doc_revision_num)
724 )
725 )
726 ;
727 EXCEPTION
728 WHEN NO_DATA_FOUND THEN
729 IF PO_LOG.d_stmt THEN
730 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
731 END IF;
732 l_return_val := 0;
733 END;
734
735 ELSE
736
737 d_position := 30;
738 IF PO_LOG.d_stmt THEN
739 PO_LOG.stmt(d_mod,d_position,'Invalid data source: ', p_data_source);
740 END IF;
741
742 END IF;
743
744 IF PO_LOG.d_proc THEN
745 PO_LOG.proc_end(d_mod, 'l_return_val', l_return_val);
746 END IF;
747
748 RETURN l_return_val;
749
750 EXCEPTION
751 WHEN OTHERS THEN
752 IF PO_LOG.d_exc THEN
753 PO_LOG.exc(d_mod,d_position,NULL);
754 END IF;
755 RAISE;
756 END getAmountApprovedForHeader;
757
758
759
760 -------------------------------------------------------------------------------
761 --Start of Comments
762 --Name: getAmountDeliveredForLine
763 --Pre-reqs:
764 -- None
765 --Modifies:
766 -- None
767 --Locks:
768 -- None
769 --Function:
770 -- Calculates the Delivered Amount for a given PO Line.
771 -- For a Financing Complex Work PO, the Delivered Amount
772 -- is the amount of work confirmed against the final delivery
773 -- of the item.
774 -- For a non-CWP PO or an Actuals CWP PO, the Delivered Amount
775 -- is the amount of work confirmed against all the STANDARD
776 -- type shipments/pay items.
777 -- The API supports only the SPO document type.
778 --Parameters:
779 --IN:
780 --p_line_id
781 -- The ID of the line for which to calculate the delivered amount
782 --p_data_source
783 -- Use g_data_source_<> constants
784 -- g_data_source_TRANSACTION: calculate totals based off of
785 -- data values in the main txn tables
786 -- g_data_source_ARCHIVE: calculate totals based off of
787 -- data values in the archive tables
788 --p_doc_revision_num
789 -- This is a DEFAULT NULL paramter
790 -- It is ignored if p_data_source is TRANSACTION
791 -- If p_data_source is ARCHIVE, then
792 -- The revision number of the header in the archive table.
793 -- If this parameter is passed as null, the latest version in the
794 -- archive table is assumed.
795 --Testing:
796 --
797 --End of Comments
798 -------------------------------------------------------------------------------
799 FUNCTION getAmountDeliveredForLine(
800 p_line_id IN NUMBER
801 , p_data_source IN VARCHAR2
802 , p_doc_revision_num IN NUMBER --default null
803 ) RETURN NUMBER
804 IS
805 d_mod CONSTANT VARCHAR2(100) :=
806 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'getAmountDeliveredForLine');
807 d_position NUMBER := 0;
808 l_return_val NUMBER := 0;
809 l_precision GL_CURRENCIES.precision%TYPE;
810 l_mau GL_CURRENCIES.minimum_accountable_unit%TYPE;
811 BEGIN
812
813 IF PO_LOG.d_proc THEN
814 PO_LOG.proc_begin(d_mod,'p_line_id',p_line_id);
815 PO_LOG.proc_begin(d_mod,'p_data_source',p_data_source);
816 PO_LOG.proc_begin(d_mod,'p_doc_revision_num',p_doc_revision_num);
817 END IF;
818
819 do_org_currency_setups(
820 p_doc_level => g_doc_level_LINE
821 , p_doc_level_id => p_line_id
822 , x_currency_precision => l_precision
823 , x_min_acct_unit => l_mau
824 );
825
826 d_position := 5;
827 IF PO_LOG.d_stmt THEN
828 PO_LOG.stmt(d_mod,d_position,'l_precision:',l_precision);
829 PO_LOG.stmt(d_mod,d_position,'l_mau:',l_mau);
830 END IF;
831
832 IF p_data_source = g_data_source_TRANSACTION THEN
833
834 d_position := 10;
835
836 BEGIN
837 SELECT SUM(
838 DECODE(poll.matching_basis
839 , 'AMOUNT', pod.amount_delivered
840 , --QUANTITY
841 nvl2(l_mau
842 , round(pod.quantity_delivered*poll.price_override/l_mau) * l_mau
843 , round((pod.quantity_delivered*poll.price_override),l_precision)) )) --Bug5391045
844 INTO l_return_val
845 FROM po_line_locations_all poll
846 , po_distributions_all pod
847 WHERE poll.po_line_id = p_line_id
848 AND pod.line_location_id = poll.line_location_id
849 AND pod.distribution_type = 'STANDARD'
850 ;
851 EXCEPTION
852 WHEN NO_DATA_FOUND THEN
853 IF PO_LOG.d_stmt THEN
854 PO_LOG.stmt(d_mod,d_position,'No distributions exist');
855 END IF;
856 l_return_val := 0;
857 END;
858
859 ELSIF p_data_source = g_data_source_ARCHIVE THEN
860
861 d_position := 20;
862
863 BEGIN
864 SELECT SUM(
865 DECODE(poll.matching_basis
866 , 'AMOUNT', pod.amount_delivered
867 , --QUANTITY
868 nvl2(l_mau
869 , round(pod.quantity_delivered*poll.price_override/l_mau) * l_mau
870 , round((pod.quantity_delivered*poll.price_override),l_precision)) )) --Bug5391045
871 INTO l_return_val
872 FROM po_line_locations_archive_all poll
873 , po_distributions_archive_all pod
874 WHERE poll.po_line_id = p_line_id
875 AND pod.line_location_id = poll.line_location_id
876 AND pod.distribution_type = 'STANDARD'
877 AND ( (p_doc_revision_num IS NULL
878 AND poll.latest_external_flag = 'Y'
879 AND pod.latest_external_flag = 'Y')
880 OR (p_doc_revision_num IS NOT NULL
881 AND poll.revision_num =
882 (SELECT max(POLL2.revision_num)
883 FROM po_line_locations_archive_all poll2
884 WHERE poll2.line_location_id = poll.line_location_id
885 AND poll2.revision_num <= p_doc_revision_num)
886 AND pod.revision_num =
887 (SELECT max(POD2.revision_num)
888 FROM po_distributions_archive_all pod2
889 WHERE pod2.po_distribution_id = pod.po_distribution_id
890 AND pod2.revision_num <= p_doc_revision_num)
891 )
892 )
893 ;
894 EXCEPTION
895 WHEN NO_DATA_FOUND THEN
896 IF PO_LOG.d_stmt THEN
897 PO_LOG.stmt(d_mod,d_position,'No distributions exist');
898 END IF;
899 l_return_val := 0;
900 END;
901
902 ELSE
903
904 d_position := 30;
905 IF PO_LOG.d_stmt THEN
906 PO_LOG.stmt(d_mod,d_position,'Invalid data source: ', p_data_source);
907 END IF;
908
909 END IF;
910
911 IF PO_LOG.d_proc THEN
912 PO_LOG.proc_end(d_mod, 'l_return_val', l_return_val);
913 END IF;
914
915 RETURN l_return_val;
916
917 EXCEPTION
918 WHEN OTHERS THEN
919 IF PO_LOG.d_exc THEN
920 PO_LOG.exc(d_mod,d_position,NULL);
921 END IF;
922 RAISE;
923 END getAmountDeliveredForLine;
924
925
926
927 -------------------------------------------------------------------------------
928 --Start of Comments
929 --Name: getAmountDeliveredForHeader
930 --Pre-reqs:
931 -- None
932 --Modifies:
933 -- None
934 --Locks:
935 -- None
936 --Function:
937 -- Calculates the Delivered Amount for a given PO Header.
938 -- For a Financing Complex Work PO, the Delivered Amount
939 -- is the amount of work confirmed against the final delivery
940 -- of the items.
941 -- For a non-CWP PO or an Actuals CWP PO, the Delivered Amount
942 -- is the amount of work confirmed against all the STANDARD
943 -- type shipments/pay items.
944 -- The API supports only the SPO document type.
945 --Parameters:
946 --IN:
947 --p_header_id
948 -- The ID of the header for which to calculate the delivered amount
949 --p_data_source
950 -- Use g_data_source_<> constants
951 -- g_data_source_TRANSACTION: calculate totals based off of
952 -- data values in the main txn tables
953 -- g_data_source_ARCHIVE: calculate totals based off of
954 -- data values in the archive tables
955 --p_doc_revision_num
956 -- This is a DEFAULT NULL paramter
957 -- It is ignored if p_data_source is TRANSACTION
958 -- If p_data_source is ARCHIVE, then
959 -- The revision number of the header in the archive table.
960 -- If this parameter is passed as null, the latest version in the
961 -- archive table is assumed.
962 --Testing:
963 --
964 --End of Comments
965 -------------------------------------------------------------------------------
966 FUNCTION getAmountDeliveredForHeader(
967 p_header_id IN NUMBER
968 , p_data_source IN VARCHAR2
969 , p_doc_revision_num IN NUMBER --default null
970 ) RETURN NUMBER
971 IS
972 d_mod CONSTANT VARCHAR2(100) :=
973 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'getAmountDeliveredForHeader');
974 d_position NUMBER := 0;
975 l_return_val NUMBER := 0;
976 l_precision GL_CURRENCIES.precision%TYPE;
977 l_mau GL_CURRENCIES.minimum_accountable_unit%TYPE;
978 BEGIN
979
980 IF PO_LOG.d_proc THEN
981 PO_LOG.proc_begin(d_mod,'p_header_id',p_header_id);
982 PO_LOG.proc_begin(d_mod,'p_data_source',p_data_source);
983 PO_LOG.proc_begin(d_mod,'p_doc_revision_num',p_doc_revision_num);
984 END IF;
985
986 do_org_currency_setups(
987 p_doc_level => g_doc_level_HEADER
988 , p_doc_level_id => p_header_id
989 , x_currency_precision => l_precision
990 , x_min_acct_unit => l_mau
991 );
992
993 d_position := 5;
994 IF PO_LOG.d_stmt THEN
995 PO_LOG.stmt(d_mod,d_position,'l_precision:',l_precision);
996 PO_LOG.stmt(d_mod,d_position,'l_mau:',l_mau);
997 END IF;
998
999 IF p_data_source = g_data_source_TRANSACTION THEN
1000
1001 d_position := 10;
1002
1003 BEGIN
1004 SELECT SUM(
1005 DECODE(poll.matching_basis
1006 , 'AMOUNT', pod.amount_delivered
1007 , --QUANTITY
1008 nvl2(l_mau
1009 , round(pod.quantity_delivered*poll.price_override/l_mau) * l_mau
1010 , round((pod.quantity_delivered*poll.price_override),l_precision)) )) --Bug5391045
1011 INTO l_return_val
1012 FROM po_line_locations_all poll
1013 , po_distributions_all pod
1014 WHERE poll.po_header_id = p_header_id
1015 AND pod.line_location_id = poll.line_location_id
1016 AND pod.distribution_type = 'STANDARD'
1017 ;
1018 EXCEPTION
1019 WHEN NO_DATA_FOUND THEN
1020 IF PO_LOG.d_stmt THEN
1021 PO_LOG.stmt(d_mod,d_position,'No distributions exist');
1022 END IF;
1023 l_return_val := 0;
1024 END;
1025
1026 ELSIF p_data_source = g_data_source_ARCHIVE THEN
1027
1028 d_position := 20;
1029
1030 BEGIN
1031 SELECT SUM(
1032 DECODE(poll.matching_basis
1033 , 'AMOUNT', pod.amount_delivered
1034 , --QUANTITY
1035 nvl2(l_mau
1036 , round(pod.quantity_delivered*poll.price_override/l_mau) * l_mau
1037 , round((pod.quantity_delivered*poll.price_override),l_precision)) )) --Bug5391045
1038 INTO l_return_val
1039 FROM po_line_locations_archive_all poll
1040 , po_distributions_archive_all pod
1041 WHERE poll.po_header_id = p_header_id
1042 AND pod.line_location_id = poll.line_location_id
1043 AND pod.distribution_type = 'STANDARD'
1044 AND ( (p_doc_revision_num IS NULL
1045 AND poll.latest_external_flag = 'Y'
1046 AND pod.latest_external_flag = 'Y')
1047 OR (p_doc_revision_num IS NOT NULL
1048 AND poll.revision_num =
1049 (SELECT max(POLL2.revision_num)
1050 FROM po_line_locations_archive_all poll2
1051 WHERE poll2.line_location_id = poll.line_location_id
1052 AND poll2.revision_num <= p_doc_revision_num)
1053 AND pod.revision_num =
1054 (SELECT max(POD2.revision_num)
1055 FROM po_distributions_archive_all pod2
1056 WHERE pod2.po_distribution_id = pod.po_distribution_id
1057 AND pod2.revision_num <= p_doc_revision_num)
1058 )
1059 )
1060 ;
1061 EXCEPTION
1062 WHEN NO_DATA_FOUND THEN
1063 IF PO_LOG.d_stmt THEN
1064 PO_LOG.stmt(d_mod,d_position,'No distributions exist');
1065 END IF;
1066 l_return_val := 0;
1067 END;
1068
1069 ELSE
1070
1071 d_position := 30;
1072 IF PO_LOG.d_stmt THEN
1073 PO_LOG.stmt(d_mod,d_position,'Invalid data source: ', p_data_source);
1074 END IF;
1075
1076 END IF;
1077
1078 IF PO_LOG.d_proc THEN
1079 PO_LOG.proc_end(d_mod, 'l_return_val', l_return_val);
1080 END IF;
1081
1082 RETURN l_return_val;
1083
1084 EXCEPTION
1085 WHEN OTHERS THEN
1086 IF PO_LOG.d_exc THEN
1087 PO_LOG.exc(d_mod,d_position,NULL);
1088 END IF;
1089 RAISE;
1090 END getAmountDeliveredForHeader;
1091
1092
1093
1094 -------------------------------------------------------------------------------
1095 --Start of Comments
1096 --Name: getAmountBilledForLine
1097 --Pre-reqs:
1098 -- None
1099 --Modifies:
1100 -- None
1101 --Locks:
1102 -- None
1103 --Function:
1104 -- Calculates the Billed Amount for a given PO Line.
1105 -- Billed Amount is calculated based on the Standard Invoices
1106 -- against the PO Line. Prepayment Invoices are not included
1107 -- in the Billed Amount.
1108 -- The API supports only the SPO document type.
1109 --Parameters:
1110 --IN:
1111 --p_line_id
1112 -- The ID of the line for which to calculate the billed amount
1113 --p_data_source
1114 -- Use g_data_source_<> constants
1115 -- g_data_source_TRANSACTION: calculate totals based off of
1116 -- data values in the main txn tables
1117 -- g_data_source_ARCHIVE: calculate totals based off of
1118 -- data values in the archive tables
1119 --p_doc_revision_num
1120 -- This is a DEFAULT NULL paramter
1121 -- It is ignored if p_data_source is TRANSACTION
1122 -- If p_data_source is ARCHIVE, then
1123 -- The revision number of the header in the archive table.
1124 -- If this parameter is passed as null, the latest version in the
1125 -- archive table is assumed.
1126 --Testing:
1127 --
1128 --End of Comments
1129 -------------------------------------------------------------------------------
1130 FUNCTION getAmountBilledForLine(
1131 p_line_id IN NUMBER
1132 , p_data_source IN VARCHAR2
1133 , p_doc_revision_num IN NUMBER --default null
1134 ) RETURN NUMBER
1135 IS
1136 d_mod CONSTANT VARCHAR2(100) :=
1137 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'getAmountBilledForLine');
1138 d_position NUMBER := 0;
1139 l_return_val NUMBER := 0;
1140 l_org_id PO_LINES_ALL.org_id%type;
1141 BEGIN
1142
1143 IF PO_LOG.d_proc THEN
1144 PO_LOG.proc_begin(d_mod,'p_line_id',p_line_id);
1145 PO_LOG.proc_begin(d_mod,'p_data_source',p_data_source);
1146 PO_LOG.proc_begin(d_mod,'p_doc_revision_num',p_doc_revision_num);
1147 END IF;
1148
1149 d_position := 10;
1150
1151 SELECT pol.org_id
1152 INTO l_org_id
1153 FROM po_lines_all pol
1154 WHERE pol.po_line_id = p_line_id;
1155
1156 d_position := 20;
1157 IF PO_LOG.d_stmt THEN
1158 PO_LOG.stmt(d_mod,d_position,'l_org_id:',l_org_id);
1159 END IF;
1160
1161 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1162
1163 IF p_data_source = g_data_source_TRANSACTION THEN
1164
1165 d_position := 30;
1166
1167 BEGIN
1168 SELECT SUM(nvl(amount_billed,0))
1169 INTO l_return_val
1170 FROM po_line_locations_all poll
1171 WHERE poll.po_line_id = p_line_id
1172 AND poll.shipment_type = 'STANDARD'
1173 ;
1174 EXCEPTION
1175 WHEN NO_DATA_FOUND THEN
1176 IF PO_LOG.d_stmt THEN
1177 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
1178 END IF;
1179 l_return_val := 0;
1180 END;
1181
1182 ELSIF p_data_source = g_data_source_ARCHIVE THEN
1183
1184 d_position := 40;
1185
1186 BEGIN
1187 SELECT SUM(nvl(amount_billed,0))
1188 INTO l_return_val
1189 FROM po_line_locations_archive_all poll
1190 WHERE poll.po_line_id = p_line_id
1191 AND poll.shipment_type='STANDARD'
1192 AND ( (p_doc_revision_num IS NULL AND poll.latest_external_flag = 'Y')
1193 OR (p_doc_revision_num IS NOT NULL
1194 AND poll.revision_num =
1195 (SELECT max(POLL2.revision_num)
1196 FROM po_line_locations_archive_all poll2
1197 WHERE poll2.line_location_id = poll.line_location_id
1198 AND poll2.revision_num <= p_doc_revision_num)
1199 )
1200 )
1201 ;
1202 EXCEPTION
1203 WHEN NO_DATA_FOUND THEN
1204 IF PO_LOG.d_stmt THEN
1205 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
1206 END IF;
1207 l_return_val := 0;
1208 END;
1209
1210 ELSE
1211
1212 d_position := 50;
1213 IF PO_LOG.d_stmt THEN
1214 PO_LOG.stmt(d_mod,d_position,'Invalid data source: ', p_data_source);
1215 END IF;
1216
1217 END IF;
1218
1219 IF PO_LOG.d_proc THEN
1220 PO_LOG.proc_end(d_mod, 'l_return_val', l_return_val);
1221 END IF;
1222
1223 RETURN l_return_val;
1224
1225 EXCEPTION
1226 WHEN OTHERS THEN
1227 IF PO_LOG.d_exc THEN
1228 PO_LOG.exc(d_mod,d_position,NULL);
1229 END IF;
1230 RAISE;
1231 END getAmountBilledForLine;
1232
1233
1234
1235 -------------------------------------------------------------------------------
1236 --Start of Comments
1237 --Name: getAmountBilledForHeader
1238 --Pre-reqs:
1239 -- None
1240 --Modifies:
1241 -- None
1242 --Locks:
1243 -- None
1244 --Function:
1245 -- Calculates the Billed Amount for a given PO Header.
1246 -- Billed Amount is calculated based on the Standard Invoices
1247 -- against the PO Line. Prepayment Invoices are not included
1248 -- in the Billed Amount.
1249 -- The API supports only the SPO document type.
1250 --Parameters:
1251 --IN:
1252 --p_header_id
1253 -- The ID of the header for which to calculate the billed amount
1254 --p_data_source
1255 -- Use g_data_source_<> constants
1256 -- g_data_source_TRANSACTION: calculate totals based off of
1257 -- data values in the main txn tables
1258 -- g_data_source_ARCHIVE: calculate totals based off of
1259 -- data values in the archive tables
1260 --p_doc_revision_num
1261 -- This is a DEFAULT NULL paramter
1262 -- It is ignored if p_data_source is TRANSACTION
1263 -- If p_data_source is ARCHIVE, then
1264 -- The revision number of the header in the archive table.
1265 -- If this parameter is passed as null, the latest version in the
1266 -- archive table is assumed.
1267 --Testing:
1268 --
1269 --End of Comments
1270 -------------------------------------------------------------------------------
1271 FUNCTION getAmountBilledForHeader(
1272 p_header_id IN NUMBER
1273 , p_data_source IN VARCHAR2
1274 , p_doc_revision_num IN NUMBER --default null
1275 ) RETURN NUMBER
1276 IS
1277 d_mod CONSTANT VARCHAR2(100) :=
1278 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'getAmountBilledForHeader');
1279 d_position NUMBER := 0;
1280 l_return_val NUMBER := 0;
1281 l_org_id PO_HEADERS_ALL.org_id%type;
1282 BEGIN
1283
1284 IF PO_LOG.d_proc THEN
1285 PO_LOG.proc_begin(d_mod,'p_header_id',p_header_id);
1286 PO_LOG.proc_begin(d_mod,'p_data_source',p_data_source);
1287 PO_LOG.proc_begin(d_mod,'p_doc_revision_num',p_doc_revision_num);
1288 END IF;
1289
1290 d_position := 10;
1291
1292 SELECT poh.org_id
1293 INTO l_org_id
1294 FROM po_headers_all poh
1295 WHERE poh.po_header_id = p_header_id
1296 ;
1297
1298 d_position := 20;
1299 IF PO_LOG.d_stmt THEN
1300 PO_LOG.stmt(d_mod,d_position,'l_org_id:',l_org_id);
1301 END IF;
1302
1303 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1304
1305 IF p_data_source = g_data_source_TRANSACTION THEN
1306
1307 d_position := 30;
1308
1309 BEGIN
1310 SELECT SUM(nvl(amount_billed,0))
1311 INTO l_return_val
1312 FROM po_line_locations_all poll
1313 WHERE poll.po_header_id = p_header_id
1314 AND poll.shipment_type = 'STANDARD'
1315 ;
1316 EXCEPTION
1317 WHEN NO_DATA_FOUND THEN
1318 IF PO_LOG.d_stmt THEN
1319 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
1320 END IF;
1321 l_return_val := 0;
1322 END;
1323
1324 ELSIF p_data_source = g_data_source_ARCHIVE THEN
1325
1326 d_position := 40;
1327
1328 BEGIN
1329 SELECT SUM(nvl(amount_billed,0))
1330 INTO l_return_val
1331 FROM po_line_locations_archive_all poll
1332 WHERE poll.po_header_id = p_header_id
1333 AND poll.shipment_type='STANDARD'
1334 AND ( (p_doc_revision_num IS NULL AND poll.latest_external_flag = 'Y')
1335 OR (p_doc_revision_num IS NOT NULL
1336 AND poll.revision_num =
1337 (SELECT max(POLL2.revision_num)
1338 FROM po_line_locations_archive_all poll2
1339 WHERE poll2.line_location_id = poll.line_location_id
1340 AND poll2.revision_num <= p_doc_revision_num)
1341 )
1342 )
1343 ;
1344 EXCEPTION
1345 WHEN NO_DATA_FOUND THEN
1346 IF PO_LOG.d_stmt THEN
1347 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
1348 END IF;
1349 l_return_val := 0;
1350 END;
1351
1352 ELSE
1353
1354 d_position := 50;
1355 IF PO_LOG.d_stmt THEN
1356 PO_LOG.stmt(d_mod,d_position,'Invalid data source: ', p_data_source);
1357 END IF;
1358
1359 END IF;
1360
1361 IF PO_LOG.d_proc THEN
1362 PO_LOG.proc_end(d_mod, 'l_return_val', l_return_val);
1363 END IF;
1364
1365 RETURN l_return_val;
1366
1367 EXCEPTION
1368 WHEN OTHERS THEN
1369 IF PO_LOG.d_exc THEN
1370 PO_LOG.exc(d_mod,d_position,NULL);
1371 END IF;
1372 RAISE;
1373 END getAmountBilledForHeader;
1374
1375
1376
1377 -------------------------------------------------------------------------------
1378 --Start of Comments
1379 --Name: getAmountFinancedForLine
1380 --Pre-reqs:
1381 -- None
1382 --Modifies:
1383 -- None
1384 --Locks:
1385 -- None
1386 --Function:
1387 -- Calculates the Financed Amount for a given PO Line.
1388 -- Financed Amount is calculated based on the Prepayment Invoices
1389 -- against the PO Line. Standard Invoices are not included
1390 -- in the Financed Amount.
1391 -- The API supports only the SPO document type.
1392 --Parameters:
1393 --IN:
1394 --p_line_id
1395 -- The ID of the line for which to calculate the financed amount
1396 --p_data_source
1397 -- Use g_data_source_<> constants
1398 -- g_data_source_TRANSACTION: calculate totals based off of
1399 -- data values in the main txn tables
1400 -- g_data_source_ARCHIVE: calculate totals based off of
1401 -- data values in the archive tables
1402 --p_doc_revision_num
1403 -- This is a DEFAULT NULL paramter
1404 -- It is ignored if p_data_source is TRANSACTION
1405 -- If p_data_source is ARCHIVE, then
1406 -- The revision number of the header in the archive table.
1407 -- If this parameter is passed as null, the latest version in the
1408 -- archive table is assumed.
1409 --Testing:
1410 --
1411 --End of Comments
1412 -------------------------------------------------------------------------------
1413 FUNCTION getAmountFinancedForLine(
1414 p_line_id IN NUMBER
1415 , p_data_source IN VARCHAR2
1416 , p_doc_revision_num IN NUMBER --default null
1417 ) RETURN NUMBER
1418 IS
1419 d_mod CONSTANT VARCHAR2(100) :=
1420 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'getAmountFinancedForLine');
1421 d_position NUMBER := 0;
1422 l_return_val NUMBER := 0;
1423 l_org_id PO_LINES_ALL.org_id%type;
1424 BEGIN
1425
1426 IF PO_LOG.d_proc THEN
1427 PO_LOG.proc_begin(d_mod,'p_line_id',p_line_id);
1428 PO_LOG.proc_begin(d_mod,'p_data_source',p_data_source);
1429 PO_LOG.proc_begin(d_mod,'p_doc_revision_num',p_doc_revision_num);
1430 END IF;
1431
1432 d_position := 10;
1433
1434 SELECT pol.org_id
1435 INTO l_org_id
1436 FROM po_lines_all pol
1437 WHERE pol.po_line_id = p_line_id;
1438
1439 d_position := 20;
1440 IF PO_LOG.d_stmt THEN
1441 PO_LOG.stmt(d_mod,d_position,'l_org_id:',l_org_id);
1442 END IF;
1443
1444 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1445
1446 IF p_data_source = g_data_source_TRANSACTION THEN
1447
1448 d_position := 30;
1449
1450 BEGIN
1451 SELECT SUM(nvl(amount_financed,0))
1452 INTO l_return_val
1453 FROM po_line_locations_all poll
1454 WHERE poll.po_line_id = p_line_id
1455 AND poll.shipment_type = 'PREPAYMENT'
1456 ;
1457 EXCEPTION
1458 WHEN NO_DATA_FOUND THEN
1459 IF PO_LOG.d_stmt THEN
1460 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
1461 END IF;
1462 l_return_val := 0;
1463 END;
1464
1465 ELSIF p_data_source = g_data_source_ARCHIVE THEN
1466
1467 d_position := 40;
1468
1469 BEGIN
1470 SELECT SUM(nvl(amount_financed,0))
1471 INTO l_return_val
1472 FROM po_line_locations_archive_all poll
1473 WHERE poll.po_line_id = p_line_id
1474 AND poll.shipment_type='PREPAYMENT'
1475 AND ( (p_doc_revision_num IS NULL AND poll.latest_external_flag = 'Y')
1476 OR (p_doc_revision_num IS NOT NULL
1477 AND poll.revision_num =
1478 (SELECT max(POLL2.revision_num)
1479 FROM po_line_locations_archive_all poll2
1480 WHERE poll2.line_location_id = poll.line_location_id
1481 AND poll2.revision_num <= p_doc_revision_num)
1482 )
1483 )
1484 ;
1485 EXCEPTION
1486 WHEN NO_DATA_FOUND THEN
1487 IF PO_LOG.d_stmt THEN
1488 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
1489 END IF;
1490 l_return_val := 0;
1491 END;
1492
1493 ELSE
1494
1495 d_position := 50;
1496 IF PO_LOG.d_stmt THEN
1497 PO_LOG.stmt(d_mod,d_position,'Invalid data source: ', p_data_source);
1498 END IF;
1499
1500 END IF;
1501
1502 IF PO_LOG.d_proc THEN
1503 PO_LOG.proc_end(d_mod, 'l_return_val', l_return_val);
1504 END IF;
1505
1506 RETURN l_return_val;
1507
1508 EXCEPTION
1509 WHEN OTHERS THEN
1510 IF PO_LOG.d_exc THEN
1511 PO_LOG.exc(d_mod,d_position,NULL);
1512 END IF;
1513 RAISE;
1514 END getAmountFinancedForLine;
1515
1516
1517
1518 -------------------------------------------------------------------------------
1519 --Start of Comments
1520 --Name: getAmountFinancedForHeader
1521 --Pre-reqs:
1522 -- None
1523 --Modifies:
1524 -- None
1525 --Locks:
1526 -- None
1527 --Function:
1528 -- Calculates the Financed Amount for a given PO Header.
1529 -- Financed Amount is calculated based on the Prepayment Invoices
1530 -- against the PO Header. Standard Invoices are not included
1531 -- in the Financed Amount.
1532 -- The API supports only the SPO document type.
1533 --Parameters:
1534 --IN:
1535 --p_header_id
1536 -- The ID of the header for which to calculate the financed amount
1537 --p_data_source
1538 -- Use g_data_source_<> constants
1539 -- g_data_source_TRANSACTION: calculate totals based off of
1540 -- data values in the main txn tables
1541 -- g_data_source_ARCHIVE: calculate totals based off of
1542 -- data values in the archive tables
1543 --p_doc_revision_num
1544 -- This is a DEFAULT NULL paramter
1545 -- It is ignored if p_data_source is TRANSACTION
1546 -- If p_data_source is ARCHIVE, then
1547 -- The revision number of the header in the archive table.
1548 -- If this parameter is passed as null, the latest version in the
1549 -- archive table is assumed.
1550 --Testing:
1551 --
1552 --End of Comments
1553 -------------------------------------------------------------------------------
1554 FUNCTION getAmountFinancedForHeader(
1555 p_header_id IN NUMBER
1556 , p_data_source IN VARCHAR2
1557 , p_doc_revision_num IN NUMBER --default null
1558 ) RETURN NUMBER
1559 IS
1560 d_mod CONSTANT VARCHAR2(100) :=
1561 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'getAmountFinancedForHeader');
1562 d_position NUMBER := 0;
1563 l_return_val NUMBER := 0;
1564 l_org_id PO_HEADERS_ALL.org_id%type;
1565 BEGIN
1566
1567 IF PO_LOG.d_proc THEN
1568 PO_LOG.proc_begin(d_mod,'p_header_id',p_header_id);
1569 PO_LOG.proc_begin(d_mod,'p_data_source',p_data_source);
1570 PO_LOG.proc_begin(d_mod,'p_doc_revision_num',p_doc_revision_num);
1571 END IF;
1572
1573 d_position := 10;
1574
1575 SELECT poh.org_id
1576 INTO l_org_id
1577 FROM po_headers_all poh
1578 WHERE poh.po_header_id = p_header_id
1579 ;
1580
1581 d_position := 20;
1582 IF PO_LOG.d_stmt THEN
1583 PO_LOG.stmt(d_mod,d_position,'l_org_id:',l_org_id);
1584 END IF;
1585
1586 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1587
1588 IF p_data_source = g_data_source_TRANSACTION THEN
1589
1590 d_position := 30;
1591
1592 BEGIN
1593 SELECT SUM(nvl(amount_financed,0))
1594 INTO l_return_val
1595 FROM po_line_locations_all poll
1596 WHERE poll.po_header_id = p_header_id
1597 AND poll.shipment_type = 'PREPAYMENT'
1598 ;
1599 EXCEPTION
1600 WHEN NO_DATA_FOUND THEN
1601 IF PO_LOG.d_stmt THEN
1602 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
1603 END IF;
1604 l_return_val := 0;
1605 END;
1606
1607 ELSIF p_data_source = g_data_source_ARCHIVE THEN
1608
1609 d_position := 40;
1610
1611 BEGIN
1612 SELECT SUM(nvl(amount_financed,0))
1613 INTO l_return_val
1614 FROM po_line_locations_archive_all poll
1615 WHERE poll.po_header_id = p_header_id
1616 AND poll.shipment_type='PREPAYMENT'
1617 AND ( (p_doc_revision_num IS NULL AND poll.latest_external_flag = 'Y')
1618 OR (p_doc_revision_num IS NOT NULL
1619 AND poll.revision_num =
1620 (SELECT max(POLL2.revision_num)
1621 FROM po_line_locations_archive_all poll2
1622 WHERE poll2.line_location_id = poll.line_location_id
1623 AND poll2.revision_num <= p_doc_revision_num)
1624 )
1625 )
1626 ;
1627 EXCEPTION
1628 WHEN NO_DATA_FOUND THEN
1629 IF PO_LOG.d_stmt THEN
1630 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
1631 END IF;
1632 l_return_val := 0;
1633 END;
1634
1635 ELSE
1636
1637 d_position := 50;
1638 IF PO_LOG.d_stmt THEN
1639 PO_LOG.stmt(d_mod,d_position,'Invalid data source: ', p_data_source);
1640 END IF;
1641
1642 END IF;
1643
1644 IF PO_LOG.d_proc THEN
1645 PO_LOG.proc_end(d_mod, 'l_return_val', l_return_val);
1646 END IF;
1647
1648 RETURN l_return_val;
1649
1650 EXCEPTION
1651 WHEN OTHERS THEN
1652 IF PO_LOG.d_exc THEN
1653 PO_LOG.exc(d_mod,d_position,NULL);
1654 END IF;
1655 RAISE;
1656 END getAmountFinancedForHeader;
1657
1658
1659
1660 -------------------------------------------------------------------------------
1661 --Start of Comments
1662 --Name: getAmountRecoupedForLine
1663 --Pre-reqs:
1664 -- None
1665 --Modifies:
1666 -- None
1667 --Locks:
1668 -- None
1669 --Function:
1670 -- Calculates the Recouped Amount for a given PO Line.
1671 -- The API supports only the SPO document type.
1672 --Parameters:
1673 --IN:
1674 --p_line_id
1675 -- The ID of the line for which to calculate the recouped amount
1676 --p_data_source
1677 -- Use g_data_source_<> constants
1678 -- g_data_source_TRANSACTION: calculate totals based off of
1679 -- data values in the main txn tables
1680 -- g_data_source_ARCHIVE: calculate totals based off of
1681 -- data values in the archive tables
1682 --p_doc_revision_num
1683 -- This is a DEFAULT NULL paramter
1684 -- It is ignored if p_data_source is TRANSACTION
1685 -- If p_data_source is ARCHIVE, then
1686 -- The revision number of the header in the archive table.
1687 -- If this parameter is passed as null, the latest version in the
1688 -- archive table is assumed.
1689 --Testing:
1690 --
1691 --End of Comments
1692 -------------------------------------------------------------------------------
1693 FUNCTION getAmountRecoupedForLine(
1694 p_line_id IN NUMBER
1695 , p_data_source IN VARCHAR2
1696 , p_doc_revision_num IN NUMBER --default null
1697 ) RETURN NUMBER
1698 IS
1699 d_mod CONSTANT VARCHAR2(100) :=
1700 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'getAmountRecoupedForLine');
1701 d_position NUMBER := 0;
1702 l_return_val NUMBER := 0;
1703 l_org_id PO_LINES_ALL.org_id%type;
1704 BEGIN
1705
1706 IF PO_LOG.d_proc THEN
1707 PO_LOG.proc_begin(d_mod,'p_line_id',p_line_id);
1708 PO_LOG.proc_begin(d_mod,'p_data_source',p_data_source);
1709 PO_LOG.proc_begin(d_mod,'p_doc_revision_num',p_doc_revision_num);
1710 END IF;
1711
1712 d_position := 10;
1713
1714 SELECT pol.org_id
1715 INTO l_org_id
1716 FROM po_lines_all pol
1717 WHERE pol.po_line_id = p_line_id;
1718
1719 d_position := 20;
1720 IF PO_LOG.d_stmt THEN
1721 PO_LOG.stmt(d_mod,d_position,'l_org_id:',l_org_id);
1722 END IF;
1723
1724 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1725
1726 IF p_data_source = g_data_source_TRANSACTION THEN
1727
1728 d_position := 10;
1729
1730 BEGIN
1731 SELECT SUM(nvl(amount_recouped,0))
1732 INTO l_return_val
1733 FROM po_line_locations_all poll
1734 WHERE poll.po_line_id = p_line_id
1735 AND poll.shipment_type = 'PREPAYMENT'
1736 ;
1737 EXCEPTION
1738 WHEN NO_DATA_FOUND THEN
1739 IF PO_LOG.d_stmt THEN
1740 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
1741 END IF;
1742 l_return_val := 0;
1743 END;
1744
1745 ELSIF p_data_source = g_data_source_ARCHIVE THEN
1746
1747 d_position := 20;
1748
1749 BEGIN
1750 SELECT SUM(nvl(amount_recouped,0))
1751 INTO l_return_val
1752 FROM po_line_locations_archive_all poll
1753 WHERE poll.po_line_id = p_line_id
1754 AND poll.shipment_type='PREPAYMENT'
1755 AND ( (p_doc_revision_num IS NULL AND poll.latest_external_flag = 'Y')
1756 OR (p_doc_revision_num IS NOT NULL
1757 AND poll.revision_num =
1758 (SELECT max(POLL2.revision_num)
1759 FROM po_line_locations_archive_all poll2
1760 WHERE poll2.line_location_id = poll.line_location_id
1761 AND poll2.revision_num <= p_doc_revision_num)
1762 )
1763 )
1764 ;
1765 EXCEPTION
1766 WHEN NO_DATA_FOUND THEN
1767 IF PO_LOG.d_stmt THEN
1768 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
1769 END IF;
1770 l_return_val := 0;
1771 END;
1772
1773 ELSE
1774
1775 d_position := 30;
1776 IF PO_LOG.d_stmt THEN
1777 PO_LOG.stmt(d_mod,d_position,'Invalid data source: ', p_data_source);
1778 END IF;
1779
1780 END IF;
1781
1782 IF PO_LOG.d_proc THEN
1783 PO_LOG.proc_end(d_mod, 'l_return_val', l_return_val);
1784 END IF;
1785
1786 RETURN l_return_val;
1787
1788 EXCEPTION
1789 WHEN OTHERS THEN
1790 IF PO_LOG.d_exc THEN
1791 PO_LOG.exc(d_mod,d_position,NULL);
1792 END IF;
1793 RAISE;
1794 END getAmountRecoupedForLine;
1795
1796
1797
1798 -------------------------------------------------------------------------------
1799 --Start of Comments
1800 --Name: getAmountRecoupedForHeader
1801 --Pre-reqs:
1802 -- None
1803 --Modifies:
1804 -- None
1805 --Locks:
1806 -- None
1807 --Function:
1808 -- Calculates the Recouped Amount for a given PO Header.
1809 -- The API supports only the SPO document type.
1810 --Parameters:
1811 --IN:
1812 --p_line_id
1813 -- The ID of the header for which to calculate the recouped amount
1814 --p_data_source
1815 -- Use g_data_source_<> constants
1816 -- g_data_source_TRANSACTION: calculate totals based off of
1817 -- data values in the main txn tables
1818 -- g_data_source_ARCHIVE: calculate totals based off of
1819 -- data values in the archive tables
1820 --p_doc_revision_num
1821 -- This is a DEFAULT NULL paramter
1822 -- It is ignored if p_data_source is TRANSACTION
1823 -- If p_data_source is ARCHIVE, then
1824 -- The revision number of the header in the archive table.
1825 -- If this parameter is passed as null, the latest version in the
1826 -- archive table is assumed.
1827 --Testing:
1828 --
1829 --End of Comments
1830 -------------------------------------------------------------------------------
1831 FUNCTION getAmountRecoupedForHeader(
1832 p_header_id IN NUMBER
1833 , p_data_source IN VARCHAR2
1834 , p_doc_revision_num IN NUMBER --default null
1835 ) RETURN NUMBER
1836 IS
1837 d_mod CONSTANT VARCHAR2(100) :=
1838 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'getAmountRecoupedForHeader');
1839 d_position NUMBER := 0;
1840 l_return_val NUMBER := 0;
1841 l_org_id PO_HEADERS_ALL.org_id%type;
1842 BEGIN
1843
1844 IF PO_LOG.d_proc THEN
1845 PO_LOG.proc_begin(d_mod,'p_header_id',p_header_id);
1846 PO_LOG.proc_begin(d_mod,'p_data_source',p_data_source);
1847 PO_LOG.proc_begin(d_mod,'p_doc_revision_num',p_doc_revision_num);
1848 END IF;
1849
1850 d_position := 10;
1851
1852 SELECT poh.org_id
1853 INTO l_org_id
1854 FROM po_headers_all poh
1855 WHERE poh.po_header_id = p_header_id
1856 ;
1857
1858 d_position := 20;
1859 IF PO_LOG.d_stmt THEN
1860 PO_LOG.stmt(d_mod,d_position,'l_org_id:',l_org_id);
1861 END IF;
1862
1863 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1864
1865 IF p_data_source = g_data_source_TRANSACTION THEN
1866
1867 d_position := 30;
1868
1869 BEGIN
1870 SELECT SUM(nvl(amount_recouped,0))
1871 INTO l_return_val
1872 FROM po_line_locations_all poll
1873 WHERE poll.po_header_id = p_header_id
1874 AND poll.shipment_type = 'PREPAYMENT'
1875 ;
1876 EXCEPTION
1877 WHEN NO_DATA_FOUND THEN
1878 IF PO_LOG.d_stmt THEN
1879 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
1880 END IF;
1881 l_return_val := 0;
1882 END;
1883
1884 ELSIF p_data_source = g_data_source_ARCHIVE THEN
1885
1886 d_position := 40;
1887
1888 BEGIN
1889 SELECT SUM(nvl(amount_recouped,0))
1890 INTO l_return_val
1891 FROM po_line_locations_archive_all poll
1892 WHERE poll.po_header_id = p_header_id
1893 AND poll.shipment_type='PREPAYMENT'
1894 AND ( (p_doc_revision_num IS NULL AND poll.latest_external_flag = 'Y')
1895 OR (p_doc_revision_num IS NOT NULL
1896 AND poll.revision_num =
1897 (SELECT max(POLL2.revision_num)
1898 FROM po_line_locations_archive_all poll2
1899 WHERE poll2.line_location_id = poll.line_location_id
1900 AND poll2.revision_num <= p_doc_revision_num)
1901 )
1902 )
1903 ;
1904 EXCEPTION
1905 WHEN NO_DATA_FOUND THEN
1906 IF PO_LOG.d_stmt THEN
1907 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
1908 END IF;
1909 l_return_val := 0;
1910 END;
1911
1912 ELSE
1913
1914 d_position := 50;
1915 IF PO_LOG.d_stmt THEN
1916 PO_LOG.stmt(d_mod,d_position,'Invalid data source: ', p_data_source);
1917 END IF;
1918
1919 END IF;
1920
1921 IF PO_LOG.d_proc THEN
1922 PO_LOG.proc_end(d_mod, 'l_return_val', l_return_val);
1923 END IF;
1924
1925 RETURN l_return_val;
1926
1927 EXCEPTION
1928 WHEN OTHERS THEN
1929 IF PO_LOG.d_exc THEN
1930 PO_LOG.exc(d_mod,d_position,NULL);
1931 END IF;
1932 RAISE;
1933 END getAmountRecoupedForHeader;
1934
1935
1936
1937 -------------------------------------------------------------------------------
1938 --Start of Comments
1939 --Name: getAmountRetainedForLine
1940 --Pre-reqs:
1941 -- None
1942 --Modifies:
1943 -- None
1944 --Locks:
1945 -- None
1946 --Function:
1947 -- Calculates the current Retained Amount for a given PO Line.
1948 -- Retained Amt = Retainge Withheld Amt - Retainage Released Amt
1949 -- The API supports only the SPO document type.
1950 --Parameters:
1951 --IN:
1952 --p_line_id
1953 -- The ID of the line for which to calculate the retained amount
1954 --p_data_source
1955 -- Use g_data_source_<> constants
1956 -- g_data_source_TRANSACTION: calculate totals based off of
1957 -- data values in the main txn tables
1958 -- g_data_source_ARCHIVE: calculate totals based off of
1959 -- data values in the archive tables
1960 --p_doc_revision_num
1961 -- This is a DEFAULT NULL paramter
1962 -- It is ignored if p_data_source is TRANSACTION
1963 -- If p_data_source is ARCHIVE, then
1964 -- The revision number of the header in the archive table.
1965 -- If this parameter is passed as null, the latest version in the
1966 -- archive table is assumed.
1967 --Testing:
1968 --
1969 --End of Comments
1970 -------------------------------------------------------------------------------
1971 FUNCTION getAmountRetainedForLine(
1972 p_line_id IN NUMBER
1973 , p_data_source IN VARCHAR2
1974 , p_doc_revision_num IN NUMBER --default null
1975 ) RETURN NUMBER
1976 IS
1977 d_mod CONSTANT VARCHAR2(100) :=
1978 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'getAmountRetainedForLine');
1979 d_position NUMBER := 0;
1980 l_return_val NUMBER := 0;
1981 l_org_id PO_LINES_ALL.org_id%type;
1982 BEGIN
1983
1984 IF PO_LOG.d_proc THEN
1985 PO_LOG.proc_begin(d_mod,'p_line_id',p_line_id);
1986 PO_LOG.proc_begin(d_mod,'p_data_source',p_data_source);
1987 PO_LOG.proc_begin(d_mod,'p_doc_revision_num',p_doc_revision_num);
1988 END IF;
1989
1990 d_position := 10;
1991
1992 SELECT pol.org_id
1993 INTO l_org_id
1994 FROM po_lines_all pol
1995 WHERE pol.po_line_id = p_line_id;
1996
1997 d_position := 20;
1998 IF PO_LOG.d_stmt THEN
1999 PO_LOG.stmt(d_mod,d_position,'l_org_id:',l_org_id);
2000 END IF;
2001
2002 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
2003
2004 IF p_data_source = g_data_source_TRANSACTION THEN
2005
2006 d_position := 30;
2007
2008 BEGIN
2009 SELECT SUM(nvl(retainage_withheld_amount,0)
2010 - nvl(retainage_released_amount,0))
2011 INTO l_return_val
2012 FROM po_line_locations_all poll
2013 WHERE poll.po_line_id = p_line_id
2014 AND poll.shipment_type = 'STANDARD'
2015 ;
2016 EXCEPTION
2017 WHEN NO_DATA_FOUND THEN
2018 IF PO_LOG.d_stmt THEN
2019 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
2020 END IF;
2021 l_return_val := 0;
2022 END;
2023
2024 ELSIF p_data_source = g_data_source_ARCHIVE THEN
2025
2026 d_position := 40;
2027
2028 BEGIN
2029 SELECT SUM(nvl(retainage_withheld_amount,0)
2030 - nvl(retainage_released_amount,0))
2031 INTO l_return_val
2032 FROM po_line_locations_archive_all poll
2033 WHERE poll.po_line_id = p_line_id
2034 AND poll.shipment_type='STANDARD'
2035 AND ( (p_doc_revision_num IS NULL AND poll.latest_external_flag = 'Y')
2036 OR (p_doc_revision_num IS NOT NULL
2037 AND poll.revision_num =
2038 (SELECT max(POLL2.revision_num)
2039 FROM po_line_locations_archive_all poll2
2040 WHERE poll2.line_location_id = poll.line_location_id
2041 AND poll2.revision_num <= p_doc_revision_num)
2042 )
2043 )
2044 ;
2045 EXCEPTION
2046 WHEN NO_DATA_FOUND THEN
2047 IF PO_LOG.d_stmt THEN
2048 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
2049 END IF;
2050 l_return_val := 0;
2051 END;
2052
2053 ELSE
2054
2055 d_position := 50;
2056 IF PO_LOG.d_stmt THEN
2057 PO_LOG.stmt(d_mod,d_position,'Invalid data source: ', p_data_source);
2058 END IF;
2059
2060 END IF;
2061
2062 IF PO_LOG.d_proc THEN
2063 PO_LOG.proc_end(d_mod, 'l_return_val', l_return_val);
2064 END IF;
2065
2066 RETURN l_return_val;
2067
2068 EXCEPTION
2069 WHEN OTHERS THEN
2070 IF PO_LOG.d_exc THEN
2071 PO_LOG.exc(d_mod,d_position,NULL);
2072 END IF;
2073 RAISE;
2074 END getAmountRetainedForLine;
2075
2076
2077
2078 -------------------------------------------------------------------------------
2079 --Start of Comments
2080 --Name: getAmountRetainedForHeader
2081 --Pre-reqs:
2082 -- None
2083 --Modifies:
2084 -- None
2085 --Locks:
2086 -- None
2087 --Function:
2088 -- Calculates the current Retained Amount for a given PO Header.
2089 -- Retained Amt = Retainge Withheld Amt - Retainage Released Amt
2090 -- The API supports only the SPO document type.
2091 --Parameters:
2092 --IN:
2093 --p_header_id
2094 -- The ID of the header for which to calculate the retained amount
2095 --p_data_source
2096 -- Use g_data_source_<> constants
2097 -- g_data_source_TRANSACTION: calculate totals based off of
2098 -- data values in the main txn tables
2099 -- g_data_source_ARCHIVE: calculate totals based off of
2100 -- data values in the archive tables
2101 --p_doc_revision_num
2102 -- This is a DEFAULT NULL paramter
2103 -- It is ignored if p_data_source is TRANSACTION
2104 -- If p_data_source is ARCHIVE, then
2105 -- The revision number of the header in the archive table.
2106 -- If this parameter is passed as null, the latest version in the
2107 -- archive table is assumed.
2108 --Testing:
2109 --
2110 --End of Comments
2111 -------------------------------------------------------------------------------
2112 FUNCTION getAmountRetainedForHeader(
2113 p_header_id IN NUMBER
2114 , p_data_source IN VARCHAR2
2115 , p_doc_revision_num IN NUMBER --default null
2116 ) RETURN NUMBER
2117 IS
2118 d_mod CONSTANT VARCHAR2(100) :=
2119 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'getAmountRetainedForHeader');
2120 d_position NUMBER := 0;
2121 l_return_val NUMBER := 0;
2122 l_org_id PO_HEADERS_ALL.org_id%type;
2123 BEGIN
2124
2125 IF PO_LOG.d_proc THEN
2126 PO_LOG.proc_begin(d_mod,'p_header_id',p_header_id);
2127 PO_LOG.proc_begin(d_mod,'p_data_source',p_data_source);
2128 PO_LOG.proc_begin(d_mod,'p_doc_revision_num',p_doc_revision_num);
2129 END IF;
2130
2131 d_position := 10;
2132
2133 SELECT poh.org_id
2134 INTO l_org_id
2135 FROM po_headers_all poh
2136 WHERE poh.po_header_id = p_header_id
2137 ;
2138
2139 d_position := 20;
2140 IF PO_LOG.d_stmt THEN
2141 PO_LOG.stmt(d_mod,d_position,'l_org_id:',l_org_id);
2142 END IF;
2143
2144 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
2145
2146 IF p_data_source = g_data_source_TRANSACTION THEN
2147
2148 d_position := 30;
2149
2150 BEGIN
2151 SELECT SUM(nvl(retainage_withheld_amount,0)
2152 - nvl(retainage_released_amount,0))
2153 INTO l_return_val
2154 FROM po_line_locations_all poll
2155 WHERE poll.po_header_id = p_header_id
2156 AND poll.shipment_type = 'STANDARD'
2157 ;
2158 EXCEPTION
2159 WHEN NO_DATA_FOUND THEN
2160 IF PO_LOG.d_stmt THEN
2161 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
2162 END IF;
2163 l_return_val := 0;
2164 END;
2165
2166 ELSIF p_data_source = g_data_source_ARCHIVE THEN
2167
2168 d_position := 40;
2169
2170 BEGIN
2171 SELECT SUM(nvl(retainage_withheld_amount,0)
2172 - nvl(retainage_released_amount,0))
2173 INTO l_return_val
2174 FROM po_line_locations_archive_all poll
2175 WHERE poll.po_header_id = p_header_id
2176 AND poll.shipment_type='STANDARD'
2177 AND ( (p_doc_revision_num IS NULL AND poll.latest_external_flag = 'Y')
2178 OR (p_doc_revision_num IS NOT NULL
2179 AND poll.revision_num =
2180 (SELECT max(POLL2.revision_num)
2181 FROM po_line_locations_archive_all poll2
2182 WHERE poll2.line_location_id = poll.line_location_id
2183 AND poll2.revision_num <= p_doc_revision_num)
2184 )
2185 )
2186 ;
2187 EXCEPTION
2188 WHEN NO_DATA_FOUND THEN
2189 IF PO_LOG.d_stmt THEN
2190 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
2191 END IF;
2192 l_return_val := 0;
2193 END;
2194
2195 ELSE
2196
2197 d_position := 50;
2198 IF PO_LOG.d_stmt THEN
2199 PO_LOG.stmt(d_mod,d_position,'Invalid data source: ', p_data_source);
2200 END IF;
2201
2202 END IF;
2203
2204 IF PO_LOG.d_proc THEN
2205 PO_LOG.proc_end(d_mod, 'l_return_val', l_return_val);
2206 END IF;
2207
2208 RETURN l_return_val;
2209
2210 EXCEPTION
2211 WHEN OTHERS THEN
2212 IF PO_LOG.d_exc THEN
2213 PO_LOG.exc(d_mod,d_position,NULL);
2214 END IF;
2215 RAISE;
2216 END getAmountRetainedForHeader;
2217
2218
2219
2220 -------------------------------------------------------------------------------
2221 --Start of Comments
2222 --Name: getLineLocQuantityForLine
2223 --Pre-reqs:
2224 -- None
2225 --Modifies:
2226 -- None
2227 --Locks:
2228 -- None
2229 --Function:
2230 -- Calculates the total quantity of the saved line locs for a line
2231 -- This API is only intended for QUANTITY-BASED LINES
2232 -- For normal SPOs, this is the sum of the shipment quantities
2233 -- For CWPOs, quantity-based lines have milestone pay items which all
2234 -- have the same quantity as the line, so the line quantity is returned
2235 -- The API supports only the SPO document type.
2236 --Parameters:
2237 --IN:
2238 --p_line_id
2239 -- The ID of the line for which to calculate the line loc total quantity
2240 --Testing:
2241 --
2242 --End of Comments
2243 -------------------------------------------------------------------------------
2244 FUNCTION getLineLocQuantityForLine(
2245 p_line_id IN NUMBER
2246 ) RETURN NUMBER
2247 IS
2248 d_mod CONSTANT VARCHAR2(100) :=
2249 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'getLineLocQuantityForLine');
2250 d_position NUMBER := 0;
2251 l_return_val NUMBER := 0;
2252 l_header_id PO_HEADERS_ALL.po_header_id%TYPE;
2253 l_is_complex_work_po BOOLEAN := FALSE;
2254 BEGIN
2255
2256 IF PO_LOG.d_proc THEN
2257 PO_LOG.proc_begin(d_mod,'p_line_id',p_line_id);
2258 END IF;
2259
2260 d_position := 10;
2261
2262 SELECT pol.po_header_id
2263 INTO l_header_id
2264 FROM po_lines_all pol
2265 WHERE pol.po_line_id = p_line_id
2266 ;
2267
2268 d_position := 20;
2269 IF PO_LOG.d_stmt THEN
2270 PO_LOG.stmt(d_mod,d_position,'l_header_id:',l_header_id);
2271 END IF;
2272
2273 l_is_complex_work_po
2274 := PO_COMPLEX_WORK_PVT.is_complex_work_po(l_header_id);
2275
2276 IF (NOT l_is_complex_work_po) THEN
2277 --Non Complex Work case
2278
2279 d_position := 30;
2280
2281 BEGIN
2282 SELECT SUM(poll.quantity)
2283 INTO l_return_val
2284 FROM po_line_locations_all poll
2285 WHERE poll.po_line_id = p_line_id
2286 AND poll.shipment_type = 'STANDARD'
2287 ;
2288 EXCEPTION
2289 WHEN NO_DATA_FOUND THEN
2290 IF PO_LOG.d_stmt THEN
2291 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
2292 END IF;
2293 l_return_val := 0;
2294 END;
2295
2296 ELSE
2297 --Complex Work case
2298 --For Qty-based Complex Work lines, all Milestone
2299 --Pay Items have the same quantity as the line
2300
2301 d_position := 40;
2302
2303 SELECT nvl(pol.quantity, 0)
2304 INTO l_return_val
2305 FROM po_lines_all pol
2306 WHERE pol.po_line_id = p_line_id
2307 ;
2308
2309 END IF;
2310
2311
2312 IF PO_LOG.d_proc THEN
2313 PO_LOG.proc_end(d_mod, 'l_return_val', l_return_val);
2314 END IF;
2315
2316 RETURN l_return_val;
2317
2318 EXCEPTION
2319 WHEN OTHERS THEN
2320 IF PO_LOG.d_exc THEN
2321 PO_LOG.exc(d_mod,d_position,NULL);
2322 END IF;
2323 RAISE;
2324 END getLineLocQuantityForLine;
2325
2326
2327
2328 -------------------------------------------------------------------------------
2329 --Start of Comments
2330 --Name: getLineLocAmountForLine
2331 --Pre-reqs:
2332 -- None
2333 --Modifies:
2334 -- None
2335 --Locks:
2336 -- None
2337 --Function:
2338 -- Calculates the total amount of the saved line locs for a line
2339 -- This API supports both Quantity-based and Amount-based lines
2340 -- The API supports only the SPO document type.
2341 --Parameters:
2342 --IN:
2343 --p_line_id
2344 -- The ID of the line for which to calculate the line loc total amount
2345 --Testing:
2346 --
2347 --End of Comments
2348 -------------------------------------------------------------------------------
2349 FUNCTION getLineLocAmountForLine(
2350 p_line_id IN NUMBER
2351 ) RETURN NUMBER
2352 IS
2353 d_mod CONSTANT VARCHAR2(100) :=
2354 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'getLineLocAmountForLine');
2355 d_position NUMBER := 0;
2356 l_return_val NUMBER := 0;
2357 l_header_id PO_HEADERS_ALL.po_header_id%TYPE;
2358 l_is_complex_work_po BOOLEAN := FALSE;
2359 l_precision GL_CURRENCIES.precision%TYPE;
2360 l_mau GL_CURRENCIES.minimum_accountable_unit%TYPE;
2361 BEGIN
2362
2363 IF PO_LOG.d_proc THEN
2364 PO_LOG.proc_begin(d_mod,'p_line_id',p_line_id);
2365 END IF;
2366
2367 do_org_currency_setups(
2368 p_doc_level => g_doc_level_LINE
2369 , p_doc_level_id => p_line_id
2370 , x_currency_precision => l_precision
2371 , x_min_acct_unit => l_mau
2372 );
2373
2374 d_position := 5;
2375 IF PO_LOG.d_stmt THEN
2376 PO_LOG.stmt(d_mod,d_position,'l_precision:',l_precision);
2377 PO_LOG.stmt(d_mod,d_position,'l_mau:',l_mau);
2378 END IF;
2379
2380 d_position := 10;
2381
2382 BEGIN
2383 SELECT SUM(
2384 DECODE(poll.matching_basis
2385 , 'AMOUNT', poll.amount-nvl(poll.amount_cancelled,0)
2386 , --QUANTITY
2387 nvl2(l_mau
2388 , round((poll.quantity-nvl(poll.quantity_cancelled,0))
2389 *poll.price_override/l_mau) * l_mau
2390 , round(((poll.quantity-nvl(poll.quantity_cancelled,0))
2391 *poll.price_override),l_precision) ) )) --Bug5391045
2392 INTO l_return_val
2393 FROM po_line_locations_all poll
2394 WHERE poll.po_line_id = p_line_id
2395 AND poll.shipment_type = 'STANDARD'
2396 ;
2397 EXCEPTION
2398 WHEN NO_DATA_FOUND THEN
2399 IF PO_LOG.d_stmt THEN
2400 PO_LOG.stmt(d_mod,d_position,'No line locations exist');
2401 END IF;
2402 l_return_val := 0;
2403 END;
2404
2405 d_position := 20;
2406
2407 IF PO_LOG.d_proc THEN
2408 PO_LOG.proc_end(d_mod, 'l_return_val', l_return_val);
2409 END IF;
2410
2411 RETURN l_return_val;
2412
2413 EXCEPTION
2414 WHEN OTHERS THEN
2415 IF PO_LOG.d_exc THEN
2416 PO_LOG.exc(d_mod,d_position,NULL);
2417 END IF;
2418 RAISE;
2419 END getLineLocAmountForLine;
2420
2421
2422
2423 -------------------------------------------------------------------------------
2424 --Start of Comments
2425 --Name: getDistQuantityForLineLoc
2426 --Pre-reqs:
2427 -- None
2428 --Modifies:
2429 -- None
2430 --Locks:
2431 -- None
2432 --Function:
2433 -- Calculates the total quantity of the saved distributions of a line loc
2434 -- This API is only intended for QUANTITY-BASED LINE LOCS
2435 -- The API supports only the SPO document type.
2436 --Parameters:
2437 --IN:
2438 --p_line_loc_id
2439 -- The ID of the line loc for which to calculate the dist total quantity
2440 --Testing:
2441 --
2442 --End of Comments
2443 -------------------------------------------------------------------------------
2444 FUNCTION getDistQuantityForLineLoc(
2445 p_line_loc_id IN NUMBER
2446 ) RETURN NUMBER
2447 IS
2448 d_mod CONSTANT VARCHAR2(100) :=
2449 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'getDistQuantityForLineLoc');
2450 d_position NUMBER := 0;
2451 l_return_val NUMBER := 0;
2452 BEGIN
2453
2454 IF PO_LOG.d_proc THEN
2455 PO_LOG.proc_begin(d_mod,'p_line_loc_id',p_line_loc_id);
2456 END IF;
2457
2458 d_position := 10;
2459
2460 BEGIN
2461 SELECT SUM(pod.quantity_ordered)
2462 INTO l_return_val
2463 FROM po_distributions_all pod
2464 WHERE pod.line_location_id = p_line_loc_id
2465 ;
2466 EXCEPTION
2467 WHEN NO_DATA_FOUND THEN
2468 IF PO_LOG.d_stmt THEN
2469 PO_LOG.stmt(d_mod,d_position,'No distributions exist');
2470 END IF;
2471 l_return_val := 0;
2472 END;
2473
2474 d_position := 20;
2475
2476 IF PO_LOG.d_proc THEN
2477 PO_LOG.proc_end(d_mod, 'l_return_val', l_return_val);
2478 END IF;
2479
2480 RETURN l_return_val;
2481
2482 EXCEPTION
2483 WHEN OTHERS THEN
2484 IF PO_LOG.d_exc THEN
2485 PO_LOG.exc(d_mod,d_position,NULL);
2486 END IF;
2487 RAISE;
2488 END getDistQuantityForLineLoc;
2489
2490
2491
2492 -------------------------------------------------------------------------------
2493 --Start of Comments
2494 --Name: getDistAmountForLineLoc
2495 --Pre-reqs:
2496 -- None
2497 --Modifies:
2498 -- None
2499 --Locks:
2500 -- None
2501 --Function:
2502 -- Calculates the total amount of the saved distributions of a line loc
2503 -- This API is only intended for AMOUNT-BASED LINE LOCS
2504 -- The API supports only the SPO document type.
2505 --Parameters:
2506 --IN:
2507 --p_line_loc_id
2508 -- The ID of the line loc for which to calculate the dist total amount
2509 --Testing:
2510 --
2511 --End of Comments
2512 -------------------------------------------------------------------------------
2513 FUNCTION getDistAmountForLineLoc(
2514 p_line_loc_id IN NUMBER
2515 ) RETURN NUMBER
2516 IS
2517 d_mod CONSTANT VARCHAR2(100) :=
2518 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'getDistAmountForLineLoc');
2519 d_position NUMBER := 0;
2520 l_return_val NUMBER := 0;
2521 BEGIN
2522
2523 IF PO_LOG.d_proc THEN
2524 PO_LOG.proc_begin(d_mod,'p_line_loc_id',p_line_loc_id);
2525 END IF;
2526
2527 d_position := 10;
2528
2529 BEGIN
2530 SELECT SUM(pod.amount_ordered)
2531 INTO l_return_val
2532 FROM po_distributions_all pod
2533 WHERE pod.line_location_id = p_line_loc_id
2534 ;
2535 EXCEPTION
2536 WHEN NO_DATA_FOUND THEN
2537 IF PO_LOG.d_stmt THEN
2538 PO_LOG.stmt(d_mod,d_position,'No distributions exist');
2539 END IF;
2540 l_return_val := 0;
2541 END;
2542
2543 d_position := 20;
2544
2545 IF PO_LOG.d_proc THEN
2546 PO_LOG.proc_end(d_mod, 'l_return_val', l_return_val);
2547 END IF;
2548
2549 RETURN l_return_val;
2550
2551 EXCEPTION
2552 WHEN OTHERS THEN
2553 IF PO_LOG.d_exc THEN
2554 PO_LOG.exc(d_mod,d_position,NULL);
2555 END IF;
2556 RAISE;
2557 END getDistAmountForLineLoc;
2558
2559
2560
2561 -------------------------------------------------------------------------------
2562 --Start of Comments
2563 --Name: do_org_currency_setups
2564 --Pre-reqs:
2565 -- None
2566 --Modifies:
2567 -- None
2568 --Locks:
2569 -- None
2570 --Function:
2571 -- Sets the org context to the document's org, if the org context was
2572 -- not already set by the caller
2573 -- Retrieves the currency information (min accting unit, currency
2574 -- precision) for the document's currency.
2575 --Parameters:
2576 --IN:
2577 --p_doc_level
2578 -- The type of ids that are being passed. Use g_doc_level_<>
2579 -- HEADER
2580 -- LINE
2581 -- SHIPMENT
2582 --p_doc_level_id
2583 -- Id of the doc level type for which to calculate totals
2584 --OUT:
2585 --x_currency_precision
2586 -- The currency precision of the document's currency
2587 --x_min_acct_unit
2588 -- The minimum accountable unit for the document's currency
2589 --Testing:
2590 --
2591 --End of Comments
2592 -------------------------------------------------------------------------------
2593 PROCEDURE do_org_currency_setups(
2594 p_doc_level IN VARCHAR2
2595 , p_doc_level_id IN NUMBER
2596 , x_currency_precision OUT NOCOPY NUMBER
2597 , x_min_acct_unit OUT NOCOPY NUMBER
2598 ) IS
2599 d_mod CONSTANT VARCHAR2(100) :=
2600 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'do_org_currency_setups');
2601 d_position NUMBER := 0;
2602 l_header_id PO_HEADERS_ALL.po_header_id%type;
2603 l_org_id PO_HEADERS_ALL.org_id%type;
2604 l_po_currency PO_HEADERS_ALL.currency_code%type;
2605 BEGIN
2606
2607 IF PO_LOG.d_proc THEN
2608 PO_LOG.proc_begin(d_mod,'p_doc_level',p_doc_level);
2609 PO_LOG.proc_begin(d_mod,'p_doc_level_id',p_doc_level_id);
2610 END IF;
2611
2612 d_position := 10;
2613
2614 IF p_doc_level = g_doc_level_HEADER THEN
2615
2616 d_position := 20;
2617 l_header_id := p_doc_level_id;
2618
2619 ELSIF p_doc_level = g_doc_level_LINE THEN
2620
2621 d_position := 30;
2622 SELECT pol.po_header_id
2623 INTO l_header_id
2624 FROM po_lines_all pol
2625 WHERE pol.po_line_id = p_doc_level_id;
2626
2627 ELSIF p_doc_level = g_doc_level_SHIPMENT THEN
2628
2629 d_position := 40;
2630 SELECT poll.po_header_id
2631 INTO l_header_id
2632 FROM po_line_locations_all poll
2633 WHERE poll.line_location_id = p_doc_level_id;
2634
2635 ELSIF p_doc_level = g_doc_level_DISTRIBUTION THEN
2636
2637 d_position := 50;
2638 SELECT pod.po_header_id
2639 INTO l_header_id
2640 FROM po_distributions_all pod
2641 WHERE pod.po_distribution_id = p_doc_level_id;
2642
2643 END IF;
2644
2645 d_position := 60;
2646 IF PO_LOG.d_stmt THEN
2647 PO_LOG.stmt(d_mod,d_position,'l_header_id:',l_header_id);
2648 END IF;
2649
2650 SELECT poh.currency_code, poh.org_id
2651 INTO l_po_currency, l_org_id
2652 FROM po_headers_all poh
2653 WHERE poh.po_header_id = l_header_id
2654 ;
2655
2656 d_position := 70;
2657 IF PO_LOG.d_stmt THEN
2658 PO_LOG.stmt(d_mod,d_position,'l_po_currency:',l_po_currency);
2659 PO_LOG.stmt(d_mod,d_position,'l_org_id:',l_org_id);
2660 END IF;
2661
2662 d_position := 80;
2663 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
2664
2665 d_position := 90;
2666 PO_CORE_S2.get_currency_info(
2667 x_currency_code => l_po_currency --IN
2668 , x_precision => x_currency_precision --OUT
2669 , x_min_unit => x_min_acct_unit --OUT
2670 );
2671
2672 d_position := 100;
2673
2674 IF PO_LOG.d_proc THEN
2675 PO_LOG.proc_end(d_mod,'x_currency_precision:',x_currency_precision);
2676 PO_LOG.proc_end(d_mod,'x_min_acct_unit:', x_min_acct_unit);
2677 END IF;
2678
2679 EXCEPTION
2680 WHEN OTHERS THEN
2681 IF PO_LOG.d_exc THEN
2682 PO_LOG.exc(d_mod,d_position,NULL);
2683 END IF;
2684 RAISE;
2685 END do_org_currency_setups;
2686
2687
2688
2689
2690
2691
2692
2693 --TODO: obsolete the following method bodies below once impacts to all
2694 --callers of the get_order_totals have been handled:
2695 -- * get_order_totals
2696 -- * get_order_totals_from_archive
2697 -- * get_totals
2698 -- * populate_temp_table
2699 -- * prepare_temp_table_data
2700 -- * calculate_totals
2701 -- * clear_temp_table
2702
2703
2704 -------------------------------------------------------------------------------
2705 --Start of Comments
2706 --Name: get_order_totals
2707 --Pre-reqs:
2708 -- None.
2709 --Modifies:
2710 -- None.
2711 --Locks:
2712 -- None.
2713 --Function:
2714 -- Calculates various totals (qty ordered, billed etc) for POs or Releases
2715 -- based on the given document level. The result is always returned in the
2716 -- document currency (foreign currency) not the OU functional currency.
2717 --Parameters:
2718 --IN:
2719 --p_doc_type
2720 -- Document type. Use the g_doc_type_<> variables, where <> is:
2721 -- PO
2722 -- RELEASE
2723 --p_doc_subtype
2724 -- Document type. Use the g_doc_type_<> variables, where <> is:
2725 -- STANDARD
2726 -- PLANNED
2727 -- BLANKET
2728 -- SCHEDULED
2729 --p_doc_level
2730 -- The type of ids that are being passed. Use g_doc_level_<>
2731 -- HEADER
2732 -- LINE
2733 -- SHIPMENT
2734 -- DISTRIBUTION
2735 --p_doc_level_id
2736 -- Id of the doc level type for which to calculate totals
2737 --OUT:
2738
2739 --x_quantity_total
2740 -- The total active (uncancelled) quantity ordered for the document level
2741 --x_amount_total
2742 -- The total active (uncancelled) amount ordered for the document level
2743 --x_quantity_delivered
2744 -- The total quantity delivered for the document level
2745 --x_amount_delivered
2746 -- The total amount delivered for the document level
2747 --x_quantity_received
2748 -- The total quantity received for the document level.
2749 -- Always zero if the document level is 'DISTRIBUTION'
2750 --x_amount_received
2751 -- The total amount received for the document level
2752 -- Always zero if the document level is 'DISTRIBUTION'
2753 --x_quantity_shipped
2754 -- The total quantity shipped for the document level.
2755 -- Always zero if the document level is 'DISTRIBUTION'
2756 --x_amount_shipped
2757 -- The total amount shipped for the document level
2758 -- Always zero if the document level is 'DISTRIBUTION'
2759 --x_quantity_billed
2760 -- The total quantity billed for the document level
2761 --x_amount_billed
2762 -- The total amount billed for the document level
2763 --x_quantity_financed
2764 -- The total quantity financed for the document level
2765 --x_amount_financed
2766 -- The total amount financed for the document level
2767 --x_quantity_recouped
2768 -- The total quantity recouped for the document level
2769 --x_amount_recouped
2770 -- The total amount recouped for the document level
2771 --x_retainage_withheld_amount
2772 -- The total retainage withheld for the document level
2773 --x_retainage_released_amount
2774 -- The total retainage released for the document level
2775 --Testing:
2776 --
2777 --End of Comments
2778 -------------------------------------------------------------------------------
2779 PROCEDURE get_order_totals(
2780 p_doc_type IN VARCHAR2,
2781 p_doc_subtype IN VARCHAR2,
2782 p_doc_level IN VARCHAR2,
2783 p_doc_level_id IN NUMBER,
2784 x_quantity_total OUT NOCOPY NUMBER,
2785 x_amount_total OUT NOCOPY NUMBER,
2786 x_quantity_delivered OUT NOCOPY NUMBER,
2787 x_amount_delivered OUT NOCOPY NUMBER,
2788 x_quantity_received OUT NOCOPY NUMBER,
2789 x_amount_received OUT NOCOPY NUMBER,
2790 x_quantity_shipped OUT NOCOPY NUMBER,
2791 x_amount_shipped OUT NOCOPY NUMBER,
2792 x_quantity_billed OUT NOCOPY NUMBER,
2793 x_amount_billed OUT NOCOPY NUMBER,
2794 x_quantity_financed OUT NOCOPY NUMBER,
2795 x_amount_financed OUT NOCOPY NUMBER,
2796 x_quantity_recouped OUT NOCOPY NUMBER,
2797 x_amount_recouped OUT NOCOPY NUMBER,
2798 x_retainage_withheld_amount OUT NOCOPY NUMBER,
2799 x_retainage_released_amount OUT NOCOPY NUMBER
2800 )
2801 IS
2802 d_mod CONSTANT VARCHAR2(100) :=
2803 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_order_totals');
2804 d_position NUMBER := 0;
2805
2806 BEGIN
2807
2808 IF PO_LOG.d_proc THEN
2809 PO_LOG.proc_begin(d_mod,'p_doc_type',p_doc_type);
2810 PO_LOG.proc_begin(d_mod,'p_doc_subtype',p_doc_subtype);
2811 PO_LOG.proc_begin(d_mod,'p_doc_level',p_doc_level);
2812 PO_LOG.proc_begin(d_mod,'p_doc_level_id',p_doc_level_id);
2813 END IF;
2814
2815 -- Logic: call the PVT signature get_totals
2816
2817 d_position := 10;
2818
2819 get_totals(
2820 p_doc_type => p_doc_type,
2821 p_doc_subtype => p_doc_subtype,
2822 p_doc_level => p_doc_level,
2823 p_doc_level_id => p_doc_level_id,
2824 p_data_source => g_data_source_TRANSACTION,
2825 p_doc_revision_num => NULL,
2826 x_quantity_total => x_quantity_total,
2827 x_amount_total => x_amount_total,
2828 x_quantity_delivered => x_quantity_delivered,
2829 x_amount_delivered => x_amount_delivered,
2830 x_quantity_received => x_quantity_received,
2831 x_amount_received => x_amount_received,
2832 x_quantity_shipped => x_quantity_shipped,
2833 x_amount_shipped => x_amount_shipped,
2834 x_quantity_billed => x_quantity_billed,
2835 x_amount_billed => x_amount_billed,
2836 x_quantity_financed => x_quantity_financed,
2837 x_amount_financed => x_amount_financed,
2838 x_quantity_recouped => x_quantity_recouped,
2839 x_amount_recouped => x_amount_recouped,
2840 x_retainage_withheld_amount => x_retainage_withheld_amount,
2841 x_retainage_released_amount => x_retainage_released_amount
2842 );
2843
2844 d_position := 20;
2845
2846 IF PO_LOG.d_proc THEN
2847 PO_LOG.proc_end(d_mod,'x_quantity_total',x_quantity_total);
2848 PO_LOG.proc_end(d_mod,'x_amount_total', x_amount_total);
2849 PO_LOG.proc_end(d_mod,'x_quantity_delivered', x_quantity_delivered);
2850 PO_LOG.proc_end(d_mod,'x_amount_delivered', x_amount_delivered);
2851 PO_LOG.proc_end(d_mod,'x_quantity_received', x_quantity_received);
2852 PO_LOG.proc_end(d_mod,'x_amount_received', x_amount_received);
2853 PO_LOG.proc_end(d_mod,'x_quantity_shipped', x_quantity_shipped);
2854 PO_LOG.proc_end(d_mod,'x_amount_shipped', x_amount_shipped);
2855 PO_LOG.proc_end(d_mod,'x_quantity_billed', x_quantity_billed);
2856 PO_LOG.proc_end(d_mod,'x_amount_billed', x_amount_billed);
2857 PO_LOG.proc_end(d_mod,'x_quantity_financed', x_quantity_financed);
2858 PO_LOG.proc_end(d_mod,'x_amount_financed', x_amount_financed);
2859 PO_LOG.proc_end(d_mod,'x_quantity_recouped', x_quantity_recouped);
2860 PO_LOG.proc_end(d_mod,'x_amount_recouped', x_amount_recouped);
2861 PO_LOG.proc_end(d_mod,'x_retainage_withheld_amount', x_retainage_withheld_amount);
2862 PO_LOG.proc_end(d_mod,'x_retainage_released_amount', x_retainage_released_amount);
2863 END IF;
2864
2865 EXCEPTION
2866 WHEN OTHERS THEN
2867 IF PO_LOG.d_exc THEN
2868 PO_LOG.exc(d_mod,d_position,NULL);
2869 END IF;
2870 RAISE;
2871
2872 END get_order_totals;
2873
2874
2875 -------------------------------------------------------------------------------
2876 --Start of Comments
2877 --Name: get_order_totals_from_archive
2878 --Pre-reqs:
2879 -- None.
2880 --Modifies:
2881 -- None.
2882 --Locks:
2883 -- None.
2884 --Function:
2885 -- Calculates various totals (qty ordered, billed etc) for an archived
2886 -- version of a PO or Release based on the given document level and the
2887 -- revision number of the header. The result is always returned in the
2888 -- document currency (foreign currency) not the OU functional currency.
2889 --Parameters:
2890 --IN:
2891 --p_doc_type
2892 -- Document type. Use the g_doc_type_<> variables, where <> is:
2893 -- PO
2894 -- RELEASE
2895 --p_doc_subtype
2896 -- Document type. Use the g_doc_type_<> variables, where <> is:
2897 -- STANDARD
2898 -- PLANNED
2899 -- BLANKET
2900 -- SCHEDULED
2901 --p_doc_level
2902 -- The type of ids that are being passed. Use g_doc_level_<>
2903 -- HEADER
2904 -- LINE
2905 -- SHIPMENT
2906 -- DISTRIBUTION
2907 --p_doc_level_id
2908 -- Id of the doc level type for which to calculate totals
2909 --p_doc_revision_num
2910 -- The revision number of the header in the archive table.
2911 -- If this parameter is passed as null, the latest version in the table
2912 -- is assumed
2913 --OUT:
2914 --x_quantity_total
2915 -- The total active (uncancelled) quantity ordered for the document level
2916 --x_amount_total
2917 -- The total active (uncancelled) amount ordered for the document level
2918 --x_quantity_delivered
2919 -- The total quantity delivered for the document level
2920 --x_amount_delivered
2921 -- The total amount delivered for the document level
2922 --x_quantity_received
2923 -- The total quantity received for the document level.
2924 -- Always zero if the document level is 'DISTRIBUTION'
2925 --x_amount_received
2926 -- The total amount received for the document level
2927 -- Always zero if the document level is 'DISTRIBUTION'
2928 --x_quantity_shipped
2929 -- The total quantity shipped for the document level.
2930 -- Always zero if the document level is 'DISTRIBUTION'
2931 --x_amount_shipped
2932 -- The total amount shipped for the document level
2933 -- Always zero if the document level is 'DISTRIBUTION'
2934 --x_quantity_billed
2935 -- The total quantity billed for the document level
2936 --x_amount_billed
2937 -- The total amount billed for the document level
2938 --x_quantity_financed
2939 -- The total quantity financed for the document level
2940 --x_amount_financed
2941 -- The total amount financed for the document level
2942 --x_quantity_recouped
2943 -- The total quantity recouped for the document level
2944 --x_amount_recouped
2945 -- The total amount recouped for the document level
2946 --x_retainage_withheld_amount
2947 -- The total retainage withheld for the document level
2948 --x_retainage_released_amount
2949 -- The total retainage released for the document level
2950 --Testing:
2951 --
2952 --End of Comments
2953 -------------------------------------------------------------------------------
2954 PROCEDURE get_order_totals_from_archive(
2955 p_doc_type IN VARCHAR2,
2956 p_doc_subtype IN VARCHAR2,
2957 p_doc_level IN VARCHAR2,
2958 p_doc_level_id IN NUMBER,
2959 p_doc_revision_num IN NUMBER,
2960 x_quantity_total OUT NOCOPY NUMBER,
2961 x_amount_total OUT NOCOPY NUMBER,
2962 x_quantity_delivered OUT NOCOPY NUMBER,
2963 x_amount_delivered OUT NOCOPY NUMBER,
2964 x_quantity_received OUT NOCOPY NUMBER,
2965 x_amount_received OUT NOCOPY NUMBER,
2966 x_quantity_shipped OUT NOCOPY NUMBER,
2967 x_amount_shipped OUT NOCOPY NUMBER,
2968 x_quantity_billed OUT NOCOPY NUMBER,
2969 x_amount_billed OUT NOCOPY NUMBER,
2970 x_quantity_financed OUT NOCOPY NUMBER,
2971 x_amount_financed OUT NOCOPY NUMBER,
2972 x_quantity_recouped OUT NOCOPY NUMBER,
2973 x_amount_recouped OUT NOCOPY NUMBER,
2974 x_retainage_withheld_amount OUT NOCOPY NUMBER,
2975 x_retainage_released_amount OUT NOCOPY NUMBER
2976 )
2977 IS
2978 d_mod CONSTANT VARCHAR2(100) :=
2979 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_order_totals_from_archive');
2980 d_position NUMBER := 0;
2981
2982 l_doc_currency_code GL_CURRENCIES.currency_code%TYPE;
2983 BEGIN
2984
2985 IF PO_LOG.d_proc THEN
2986 PO_LOG.proc_begin(d_mod,'p_doc_type',p_doc_type);
2987 PO_LOG.proc_begin(d_mod,'p_doc_subtype',p_doc_subtype);
2988 PO_LOG.proc_begin(d_mod,'p_doc_level',p_doc_level);
2989 PO_LOG.proc_begin(d_mod,'p_doc_level_id',p_doc_level_id);
2990 PO_LOG.proc_begin(d_mod,'p_doc_revision_num',p_doc_revision_num);
2991 END IF;
2992
2993 -- Logic: call the PVT signature get_totals
2994
2995 d_position := 10;
2996
2997 get_totals(
2998 p_doc_type => p_doc_type,
2999 p_doc_subtype => p_doc_subtype,
3000 p_doc_level => p_doc_level,
3001 p_doc_level_id => p_doc_level_id,
3002 p_data_source => g_data_source_ARCHIVE,
3003 p_doc_revision_num => p_doc_revision_num,
3004 x_quantity_total => x_quantity_total,
3005 x_amount_total => x_amount_total,
3006 x_quantity_delivered => x_quantity_delivered,
3007 x_amount_delivered => x_amount_delivered,
3008 x_quantity_received => x_quantity_received,
3009 x_amount_received => x_amount_received,
3010 x_quantity_shipped => x_quantity_shipped,
3011 x_amount_shipped => x_amount_shipped,
3012 x_quantity_billed => x_quantity_billed,
3013 x_amount_billed => x_amount_billed,
3014 x_quantity_financed => x_quantity_financed,
3015 x_amount_financed => x_amount_financed,
3016 x_quantity_recouped => x_quantity_recouped,
3017 x_amount_recouped => x_amount_recouped,
3018 x_retainage_withheld_amount => x_retainage_withheld_amount,
3019 x_retainage_released_amount => x_retainage_released_amount
3020 );
3021
3022 d_position := 20;
3023
3024 IF PO_LOG.d_proc THEN
3025 PO_LOG.proc_end(d_mod,'x_quantity_total',x_quantity_total);
3026 PO_LOG.proc_end(d_mod,'x_amount_total', x_amount_total);
3027 PO_LOG.proc_end(d_mod,'x_quantity_delivered', x_quantity_delivered);
3028 PO_LOG.proc_end(d_mod,'x_amount_delivered', x_amount_delivered);
3029 PO_LOG.proc_end(d_mod,'x_quantity_received', x_quantity_received);
3030 PO_LOG.proc_end(d_mod,'x_amount_received', x_amount_received);
3031 PO_LOG.proc_end(d_mod,'x_quantity_shipped', x_quantity_shipped);
3032 PO_LOG.proc_end(d_mod,'x_amount_shipped', x_amount_shipped);
3033 PO_LOG.proc_end(d_mod,'x_quantity_billed', x_quantity_billed);
3034 PO_LOG.proc_end(d_mod,'x_amount_billed', x_amount_billed);
3035 PO_LOG.proc_end(d_mod,'x_quantity_financed', x_quantity_financed);
3036 PO_LOG.proc_end(d_mod,'x_amount_financed', x_amount_financed);
3037 PO_LOG.proc_end(d_mod,'x_quantity_recouped', x_quantity_recouped);
3038 PO_LOG.proc_end(d_mod,'x_amount_recouped', x_amount_recouped);
3039 PO_LOG.proc_end(d_mod,'x_retainage_withheld_amount', x_retainage_withheld_amount);
3040 PO_LOG.proc_end(d_mod,'x_retainage_released_amount', x_retainage_released_amount);
3041 END IF;
3042
3043 EXCEPTION
3044 WHEN OTHERS THEN
3045 IF PO_LOG.d_exc THEN
3046 PO_LOG.exc(d_mod,d_position,NULL);
3047 END IF;
3048 RAISE;
3049
3050 END get_order_totals_from_archive;
3051
3052
3053
3054 -------------------------------------------------------------------------------
3055 --Start of Comments
3056 --Name: get_totals
3057 --Pre-reqs:
3058 -- None.
3059 --Modifies:
3060 -- None.
3061 --Locks:
3062 -- None.
3063 --Function:
3064 -- Main PVT API for Totals logic. Acts as a switchboard to call the
3065 -- various subprocedures to populate the GTT and perform the calculations
3066 --Parameters:
3067 --IN:
3068 --p_doc_type
3069 -- Document type. Use the g_doc_type_<> variables, where <> is:
3070 -- PO
3071 -- RELEASE
3072 --p_doc_subtype
3073 -- Document type. Use the g_doc_type_<> variables, where <> is:
3074 -- STANDARD
3075 -- PLANNED
3076 -- BLANKET
3077 -- SCHEDULED
3078 --p_doc_level
3079 -- The type of ids that are being passed. Use g_doc_level_<>
3080 -- HEADER
3081 -- LINE
3082 -- SHIPMENT
3083 -- DISTRIBUTION
3084 --p_data_source
3085 -- Use C_data_source_<> constants
3086 -- C_data_source_TRANSACTION: calculate totals based off of
3087 -- data values in the main txn tables
3088 -- C_data_source_ARCHIVE: calculate totals based off of
3089 -- data values in the archive tables
3090 --p_doc_revision_num
3091 -- The revision number of the header in the archive table.
3092 -- If this parameter is passed as null, the latest version in the table
3093 -- is assumed
3094 --p_doc_level_id
3095 -- Id of the doc level type for which to calculate totals
3096 --OUT:
3097 --x_quantity_total
3098 -- The total active (uncancelled) quantity ordered for the document level
3099 --x_amount_total
3100 -- The total active (uncancelled) amount ordered for the document level
3101 --x_quantity_delivered
3102 -- The total quantity delivered for the document level
3103 --x_amount_delivered
3104 -- The total amount delivered for the document level
3105 --x_quantity_received
3106 -- The total quantity received for the document level.
3107 -- Always zero if the document level is 'DISTRIBUTION'
3108 --x_amount_received
3109 -- The total amount received for the document level
3110 -- Always zero if the document level is 'DISTRIBUTION'
3111 --x_quantity_shipped
3112 -- The total quantity shipped for the document level.
3113 -- Always zero if the document level is 'DISTRIBUTION'
3114 --x_amount_shipped
3115 -- The total amount shipped for the document level
3116 -- Always zero if the document level is 'DISTRIBUTION'
3117 --x_quantity_billed
3118 -- The total quantity billed for the document level
3119 --x_amount_billed
3120 -- The total amount billed for the document level
3121 --x_quantity_financed
3122 -- The total quantity financed for the document level
3123 --x_amount_financed
3124 -- The total amount financed for the document level
3125 --x_quantity_recouped
3126 -- The total quantity recouped for the document level
3127 --x_amount_recouped
3128 -- The total amount recouped for the document level
3129 --x_retainage_withheld_amount
3130 -- The total retainage withheld for the document level
3131 --x_retainage_released_amount
3132 -- The total retainage released for the document level
3133 --Testing:
3134 --
3135 --End of Comments
3136 -------------------------------------------------------------------------------
3137 PROCEDURE get_totals(
3138 p_doc_type IN VARCHAR2,
3139 p_doc_subtype IN VARCHAR2,
3140 p_doc_level IN VARCHAR2,
3141 p_doc_level_id IN NUMBER,
3142 p_data_source IN VARCHAR2,
3143 p_doc_revision_num IN NUMBER,
3144 x_quantity_total OUT NOCOPY NUMBER,
3145 x_amount_total OUT NOCOPY NUMBER,
3146 x_quantity_delivered OUT NOCOPY NUMBER,
3147 x_amount_delivered OUT NOCOPY NUMBER,
3148 x_quantity_received OUT NOCOPY NUMBER,
3149 x_amount_received OUT NOCOPY NUMBER,
3150 x_quantity_shipped OUT NOCOPY NUMBER,
3151 x_amount_shipped OUT NOCOPY NUMBER,
3152 x_quantity_billed OUT NOCOPY NUMBER,
3153 x_amount_billed OUT NOCOPY NUMBER,
3154 x_quantity_financed OUT NOCOPY NUMBER,
3155 x_amount_financed OUT NOCOPY NUMBER,
3156 x_quantity_recouped OUT NOCOPY NUMBER,
3157 x_amount_recouped OUT NOCOPY NUMBER,
3158 x_retainage_withheld_amount OUT NOCOPY NUMBER,
3159 x_retainage_released_amount OUT NOCOPY NUMBER
3160 )
3161 IS
3162 d_mod CONSTANT VARCHAR2(100) :=
3163 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_order_totals');
3164 d_position NUMBER := 0;
3165
3166 l_org_id HR_ALL_ORGANIZATION_UNITS.organization_id%type;
3167 l_distribution_id_tbl po_tbl_number;
3168 l_document_id NUMBER;
3169 l_document_id_tbl po_tbl_number;
3170 l_temp_table_key PO_DOCUMENT_TOTALS_GT.key%TYPE;
3171 l_base_currency_code GL_CURRENCIES.currency_code%TYPE;
3172 l_doc_currency_code GL_CURRENCIES.currency_code%TYPE;
3173 l_temp_table_row_count NUMBER;
3174
3175 BEGIN
3176
3177 IF PO_LOG.d_proc THEN
3178 PO_LOG.proc_begin(d_mod,'p_doc_type',p_doc_type);
3179 PO_LOG.proc_begin(d_mod,'p_doc_subtype',p_doc_subtype);
3180 PO_LOG.proc_begin(d_mod,'p_doc_level',p_doc_level);
3181 PO_LOG.proc_begin(d_mod,'p_doc_level_id',p_doc_level_id);
3182 PO_LOG.proc_begin(d_mod,'p_data_source',p_data_source);
3183 PO_LOG.proc_begin(d_mod,'p_doc_revision_num',p_doc_revision_num);
3184 END IF;
3185
3186 -- Logic:
3187 -- Based on doc type and doc level, get the list of distribution ids
3188 -- Based on the distribution ids, populate the GTT with qty, price etc
3189 -- Do intermediate calculations as necessary
3190 -- Calculate the totals based off of the temp table data
3191
3192 -- Bug 5124868: enhanced the doc totals API to set the org context
3193 -- if not already set.
3194 l_org_id := PO_MOAC_UTILS_PVT.get_entity_org_id(
3195 p_doc_type
3196 , p_doc_level
3197 , p_doc_level_id);
3198 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
3199
3200 d_position := 5;
3201
3202 populate_temp_table(
3203 p_doc_type => p_doc_type,
3204 p_doc_level => p_doc_level,
3205 p_doc_level_id => p_doc_level_id,
3206 p_data_source => p_data_source,
3207 p_doc_revision_num => p_doc_revision_num,
3208 x_temp_table_key => l_temp_table_key,
3209 x_count => l_temp_table_row_count
3210 );
3211
3212 d_position := 10;
3213
3214 IF (l_temp_table_row_count > 0) THEN
3215
3216 -- Get the PO Header ID (or Release ID in BRel/SRel case)
3217 -- based on the passed in doc level ID
3218 PO_CORE_S.get_document_ids(
3219 p_doc_type => p_doc_type,
3220 p_doc_level => p_doc_level,
3221 p_doc_level_id_tbl => po_tbl_number(p_doc_level_id),
3222 x_doc_id_tbl => l_document_id_tbl
3223 );
3224
3225 d_position := 20;
3226
3227 -- There should only be 1 row in the returned table, since this
3228 -- API is called for a single document at a time
3229 l_document_id := l_document_id_tbl(1);
3230
3231 d_position := 30;
3232
3233 prepare_temp_table_data(
3234 p_temp_table_key => l_temp_table_key,
3235 p_document_id => l_document_id
3236 );
3237
3238 d_position := 40;
3239
3240 calculate_totals(
3241 p_temp_table_key => l_temp_table_key,
3242 p_document_id => l_document_id,
3243 p_doc_level => p_doc_level,
3244 x_quantity_total => x_quantity_total,
3245 x_amount_total => x_amount_total,
3246 x_quantity_delivered => x_quantity_delivered,
3247 x_amount_delivered => x_amount_delivered,
3248 x_quantity_received => x_quantity_received,
3249 x_amount_received => x_amount_received,
3250 x_quantity_shipped => x_quantity_shipped,
3251 x_amount_shipped => x_amount_shipped,
3252 x_quantity_billed => x_quantity_billed,
3253 x_amount_billed => x_amount_billed,
3254 x_quantity_financed => x_quantity_financed,
3255 x_amount_financed => x_amount_financed,
3256 x_quantity_recouped => x_quantity_recouped,
3257 x_amount_recouped => x_amount_recouped,
3258 x_retainage_withheld_amount => x_retainage_withheld_amount,
3259 x_retainage_released_amount => x_retainage_released_amount
3260 );
3261
3262 d_position := 50;
3263
3264 ELSE
3265
3266 -- Temp Table Row Count is 0.
3267 -- This can happen for unsaved documents which have no lines yet
3268 x_quantity_total := 0; x_amount_total := 0;
3269 x_quantity_delivered := 0; x_amount_delivered := 0;
3270 x_quantity_received := 0; x_amount_received := 0;
3271 x_quantity_shipped := 0; x_amount_shipped := 0;
3272 x_quantity_billed := 0; x_amount_billed := 0;
3273 x_quantity_financed := 0; x_amount_financed := 0;
3274 x_quantity_recouped := 0; x_amount_recouped := 0;
3275 x_retainage_withheld_amount := 0; x_retainage_released_amount := 0;
3276
3277 END IF;
3278
3279 -- Delete our data from the temp table
3280 clear_temp_table(
3281 p_temp_table_key => l_temp_table_key
3282 );
3283
3284 d_position := 60;
3285
3286 IF PO_LOG.d_proc THEN
3287 PO_LOG.proc_end(d_mod,'x_quantity_total',x_quantity_total);
3288 PO_LOG.proc_end(d_mod,'x_amount_total', x_amount_total);
3289 PO_LOG.proc_end(d_mod,'x_quantity_delivered', x_quantity_delivered);
3290 PO_LOG.proc_end(d_mod,'x_amount_delivered', x_amount_delivered);
3291 PO_LOG.proc_end(d_mod,'x_quantity_received', x_quantity_received);
3292 PO_LOG.proc_end(d_mod,'x_amount_received', x_amount_received);
3293 PO_LOG.proc_end(d_mod,'x_quantity_shipped', x_quantity_shipped);
3294 PO_LOG.proc_end(d_mod,'x_amount_shipped', x_amount_shipped);
3295 PO_LOG.proc_end(d_mod,'x_quantity_billed', x_quantity_billed);
3296 PO_LOG.proc_end(d_mod,'x_amount_billed', x_amount_billed);
3297 PO_LOG.proc_end(d_mod,'x_quantity_financed', x_quantity_financed);
3298 PO_LOG.proc_end(d_mod,'x_amount_financed', x_amount_financed);
3299 PO_LOG.proc_end(d_mod,'x_quantity_recouped', x_quantity_recouped);
3300 PO_LOG.proc_end(d_mod,'x_amount_recouped', x_amount_recouped);
3301 PO_LOG.proc_end(d_mod,'x_retainage_withheld_amount', x_retainage_withheld_amount);
3302 PO_LOG.proc_end(d_mod,'x_retainage_released_amount', x_retainage_released_amount);
3303 END IF;
3304
3305 EXCEPTION
3306 WHEN OTHERS THEN
3307 IF PO_LOG.d_exc THEN
3308 PO_LOG.exc(d_mod,d_position,NULL);
3309 END IF;
3310 RAISE;
3311
3312 END get_totals;
3313
3314
3315
3316 -------------------------------------------------------------------------------
3317 --Start of Comments
3318 --Name: populate_temp_table
3319 --Pre-reqs:
3320 -- None.
3321 --Modifies:
3322 -- PO_DOCUMENT_TOTALS_GT
3323 --Locks:
3324 -- None.
3325 --Function:
3326 -- Based on given doc type and doc level, get the list of distribution ids.
3327 -- Based on the distribution ids, populate the GTT with all relevant columns
3328 -- (e.g. qty, price) needed to perform the calculations
3329 --Parameters:
3330 --IN:
3331 --p_doc_type
3332 -- Document type. Use the g_doc_type_<> variables, where <> is:
3333 -- PO
3334 -- RELEASE
3335 --p_doc_level
3336 -- The type of ids that are being passed. Use g_doc_level_<>
3337 -- HEADER
3338 -- LINE
3339 -- SHIPMENT
3340 -- DISTRIBUTION
3341 --p_doc_level_id
3342 -- Id of the doc level type for which to calculate totals
3343 --p_data_source
3344 -- Use g_data_source_<> constants
3345 -- g_data_source_TRANSACTION: calculate totals based off of
3346 -- data values in the main txn tables
3347 -- g_data_source_ARCHIVE: calculate totals based off of
3348 -- data values in the archive tables
3349 --p_doc_revision_num
3350 -- The revision number of the header in the archive table.
3351 -- If this parameter is passed as null, the latest version in the table
3352 -- is assumed
3353 --OUT:
3354 --x_temp_table_key
3355 -- The unique key value that identifies all rows inserted into
3356 -- PO_DOCUMENT_TOTALS_GT for this transaction
3357 --x_count
3358 -- The number of rows inserted into the temp table
3359 --Testing:
3360 --
3361 --End of Comments
3362 -------------------------------------------------------------------------------
3363 PROCEDURE populate_temp_table(
3364 p_doc_type IN VARCHAR2,
3365 p_doc_level IN VARCHAR2,
3366 p_doc_level_id IN NUMBER,
3367 p_data_source IN VARCHAR2,
3368 p_doc_revision_num IN NUMBER,
3369 x_temp_table_key OUT NOCOPY NUMBER,
3370 x_count OUT NOCOPY NUMBER
3371 )
3372 IS
3373 d_mod CONSTANT VARCHAR2(100) :=
3374 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'populate_temp_table');
3375 d_position NUMBER := 0;
3376
3377 l_distribution_id_tbl PO_TBL_NUMBER;
3378 l_distribution_rev_num_tbl po_tbl_number;
3379 l_temp_table_key PO_DOCUMENT_TOTALS_GT.key%TYPE;
3380 l_distribution_type_filter PO_DISTRIBUTIONS_ALL.distribution_type%TYPE;
3381 BEGIN
3382
3383 IF PO_LOG.d_proc THEN
3384 PO_LOG.proc_begin(d_mod,'p_doc_type',p_doc_type);
3385 PO_LOG.proc_begin(d_mod,'p_doc_level',p_doc_level);
3386 PO_LOG.proc_begin(d_mod,'p_doc_level_id',p_doc_level_id);
3387 END IF;
3388
3389 d_position := 5;
3390
3391 -- Select the unique key to identify GTT rows for this trxn
3392 SELECT PO_DOCUMENT_TOTALS_GT_S.nextval INTO l_temp_table_key from dual;
3393
3394 IF p_data_source = g_data_source_TRANSACTION THEN
3395
3396 d_position := 10;
3397
3398 -- Get the IDs of the distributions from the trxn tables.
3399 -- We will get the qty/amt data to sum up based on dist data.
3400 PO_CORE_S.get_distribution_ids(
3401 p_doc_type => p_doc_type,
3402 p_doc_level => p_doc_level,
3403 p_doc_level_id_tbl => po_tbl_number(p_doc_level_id),
3404 x_distribution_id_tbl => l_distribution_id_tbl
3405 );
3406
3407 d_position := 20;
3408
3409 -- SQL WHAT: Insert relevant data for calculations into the temp table,
3410 -- rolling distribution data up into line location subtotals. This is done
3411 -- to accomodate the fact that some totals fields (e.g. received/shipped)
3412 -- only live at the line location level. If the caller passed in the
3413 -- p_doc_level for totals as DISTRIBUTION, then set these line location
3414 -- fields to zero.
3415 -- SQL WHERE: All distributions under the given document level
3416 FORALL i IN 1 .. l_distribution_id_tbl.COUNT
3417 INSERT INTO PO_DOCUMENT_TOTALS_GT
3418 (
3419 key,
3420 line_location_id,
3421 amount_based_flag,
3422 shipment_type,
3423 payment_type,
3424 price,
3425 quantity_total,
3426 quantity_billed,
3427 quantity_delivered,
3428 quantity_financed,
3429 quantity_recouped,
3430 quantity_received,
3431 quantity_shipped,
3432 amount_total,
3433 amount_billed,
3434 amount_delivered,
3435 amount_financed,
3436 amount_recouped,
3437 amount_received,
3438 amount_shipped,
3439 retainage_withheld_amount,
3440 retainage_released_amount
3441 )
3442 SELECT
3443 l_temp_table_key,
3444 POLL.line_location_id,
3445 DECODE(POLL.value_basis,
3446 'FIXED PRICE', 'Y',
3447 'RATE', 'Y',
3448 'N') amount_based_flag,
3449 POLL.shipment_type,
3450 POLL.payment_type,
3451 POLL.price_override,
3452 SUM( (nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0)) ),
3453 SUM( nvl(POD.quantity_billed,0) ),
3454 SUM( nvl(POD.quantity_delivered,0) ),
3455 SUM( nvl(POD.quantity_financed,0) ),
3456 SUM( nvl(POD.quantity_recouped,0) ),
3457 DECODE(p_doc_level, g_doc_level_DISTRIBUTION, 0, POLL.quantity_received),
3458 DECODE(p_doc_level, g_doc_level_DISTRIBUTION, 0, POLL.quantity_shipped),
3459 SUM( (nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0)) ),
3460 SUM( nvl(POD.amount_billed,0) ),
3461 SUM( nvl(POD.amount_delivered,0) ),
3462 SUM( nvl(POD.amount_financed,0) ),
3463 SUM( nvl(POD.amount_recouped,0) ),
3464 DECODE(p_doc_level, g_doc_level_DISTRIBUTION, 0, POLL.amount_received),
3465 DECODE(p_doc_level, g_doc_level_DISTRIBUTION, 0, POLL.amount_shipped),
3466 SUM( nvl(POD.retainage_withheld_amount,0) ),
3467 SUM( nvl(POD.retainage_released_amount,0) )
3468 FROM
3469 PO_LINE_LOCATIONS_ALL POLL,
3470 PO_DISTRIBUTIONS_ALL POD
3471 WHERE POD.po_distribution_id = l_distribution_id_tbl(i)
3472 AND POD.line_location_id = POLL.line_location_id
3473 GROUP BY POLL.line_location_id, POLL.value_basis, POLL.shipment_type,
3474 POLL.payment_type, POLL.price_override, POLL.quantity_received, POLL.quantity_shipped,
3475 POLL.amount_received, POLL.amount_shipped
3476 ;
3477
3478 x_count := nvl(SQL%ROWCOUNT, 0);
3479
3480 d_position := 30;
3481 IF PO_LOG.d_stmt THEN
3482 PO_LOG.stmt(d_mod,d_position,'Inserted data - rowcount:',x_count);
3483 END IF;
3484
3485 ELSIF p_data_source = g_data_source_ARCHIVE THEN
3486
3487 d_position := 40;
3488
3489 -- Get the IDs of the distributions from the archive tables.
3490 -- We will get the qty/amt data to sum up based on dist data.
3491 PO_CORE_S.get_dist_ids_from_archive(
3492 p_doc_type => p_doc_type,
3493 p_doc_level => p_doc_level,
3494 p_doc_level_id_tbl => po_tbl_number(p_doc_level_id),
3495 p_doc_revision_num => p_doc_revision_num,
3496 x_distribution_id_tbl => l_distribution_id_tbl,
3497 x_distribution_rev_num_tbl => l_distribution_rev_num_tbl
3498 );
3499
3500 d_position := 50;
3501
3502 -- SQL WHAT: Insert relevant data for calculations into the temp table,
3503 -- rolling distribution data up into line location subtotals. This is done
3504 -- to accomodate the fact that some totals fields (e.g. received/shipped)
3505 -- only live at the line location level. If the caller passed in the
3506 -- p_doc_level for totals as DISTRIBUTION, then set these line location
3507 -- fields to zero.
3508 -- SQL WHERE: All distributions under the given document level
3509 FORALL i IN 1 .. l_distribution_id_tbl.COUNT
3510 INSERT INTO PO_DOCUMENT_TOTALS_GT
3511 (
3512 key,
3513 line_location_id,
3514 amount_based_flag,
3515 shipment_type,
3516 payment_type,
3517 price,
3518 quantity_total,
3519 quantity_billed,
3520 quantity_delivered,
3521 quantity_financed,
3522 quantity_recouped,
3523 quantity_received,
3524 quantity_shipped,
3525 amount_total,
3526 amount_billed,
3527 amount_delivered,
3528 amount_financed,
3529 amount_recouped,
3530 amount_received,
3531 amount_shipped,
3532 retainage_withheld_amount,
3533 retainage_released_amount
3534 )
3535 SELECT
3536 l_temp_table_key,
3537 POLL.line_location_id,
3538 DECODE(POLL.value_basis,
3539 'FIXED PRICE', 'Y',
3540 'RATE', 'Y',
3541 'N') amount_based_flag,
3542 POLL.shipment_type,
3543 POLL.payment_type,
3544 POLL.price_override,
3545 SUM( (nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0)) ),
3546 SUM( nvl(POD.quantity_billed,0) ),
3547 SUM( nvl(POD.quantity_delivered,0) ),
3548 SUM( nvl(POD.quantity_financed,0) ),
3549 SUM( nvl(POD.quantity_recouped,0) ),
3550 DECODE(p_doc_level, g_doc_level_DISTRIBUTION, 0, POLL.quantity_received),
3551 DECODE(p_doc_level, g_doc_level_DISTRIBUTION, 0, POLL.quantity_shipped),
3552 SUM( (nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0)) ),
3553 SUM( nvl(POD.amount_billed,0) ),
3554 SUM( nvl(POD.amount_delivered,0) ),
3555 SUM( nvl(POD.amount_financed,0) ),
3556 SUM( nvl(POD.amount_recouped,0) ),
3557 DECODE(p_doc_level, g_doc_level_DISTRIBUTION, 0, POLL.amount_received),
3558 DECODE(p_doc_level, g_doc_level_DISTRIBUTION, 0, POLL.amount_shipped),
3559 SUM( nvl(POD.retainage_withheld_amount,0) ),
3560 SUM( nvl(POD.retainage_released_amount,0) )
3561 FROM
3562 PO_LINE_LOCATIONS_ARCHIVE_ALL POLL,
3563 PO_DISTRIBUTIONS_ARCHIVE_ALL POD
3564 WHERE POD.po_distribution_id = l_distribution_id_tbl(i)
3565 AND POD.revision_num = l_distribution_rev_num_tbl(i)
3566 AND POD.line_location_id = POLL.line_location_id
3567 AND ( (p_doc_revision_num IS NULL AND POLL.latest_external_flag = 'Y')
3568 OR (p_doc_revision_num IS NOT NULL
3569 AND POLL.revision_num =
3570 (SELECT max(POLL2.revision_num)
3571 FROM PO_LINE_LOCATIONS_ARCHIVE_ALL POLL2
3572 WHERE POLL2.line_location_id = POLL.line_location_id
3573 AND POLL2.revision_num <= p_doc_revision_num)
3574 )
3575 )
3576 GROUP BY POLL.line_location_id, POLL.value_basis, POLL.shipment_type,
3577 POLL.payment_type, POLL.price_override, POLL.quantity_received,
3578 POLL.quantity_shipped, POLL.amount_received, POLL.amount_shipped
3579 ;
3580
3581 x_count := nvl(SQL%ROWCOUNT, 0);
3582
3583 d_position := 60;
3584 IF PO_LOG.d_stmt THEN
3585 PO_LOG.stmt(d_mod,d_position,'Inserted data - rowcount:',x_count);
3586 END IF;
3587
3588 ELSE
3589
3590 d_position := 70;
3591 IF PO_LOG.d_stmt THEN
3592 PO_LOG.stmt(d_mod,d_position,'Invalid data source: ', p_data_source);
3593 END IF;
3594
3595 END IF;
3596
3597 x_temp_table_key := l_temp_table_key;
3598
3599 IF PO_LOG.d_proc THEN
3600 PO_LOG.proc_end(d_mod,'x_temp_table_key',x_count);
3601 PO_LOG.proc_end(d_mod,'x_temp_table_key',x_temp_table_key);
3602 END IF;
3603
3604 EXCEPTION
3605 WHEN OTHERS THEN
3606 IF PO_LOG.d_exc THEN
3607 PO_LOG.exc(d_mod,d_position,NULL);
3608 END IF;
3609 RAISE;
3610 END populate_temp_table;
3611
3612
3613 -------------------------------------------------------------------------------
3614 --Start of Comments
3615 --Name: prepare_temp_table_data
3616 --Pre-reqs:
3617 -- PO_DOCUMENT_TOTALS_GT must be populated appropriately.
3618 --Modifies:
3619 -- PO_DOCUMENT_TOTALS_GT
3620 --Locks:
3621 -- None.
3622 --Function:
3623 -- Performs intermediate calculations on input data to the GTT.
3624 -- * Calculates amount_total for quantity lines
3625 -- * Performs rounding on the calculated amount_total
3626 --Parameters:
3627 --IN:
3628 --p_temp_table_key
3629 -- The unique key value that identifies all rows in PO_DOCUMENT_TOTALS_GT
3630 -- related to this transaction
3631 --p_document_id
3632 -- The po_header_id for POs; the po_release_id for Releases
3633 --Testing:
3634 --
3635 --End of Comments
3636 -------------------------------------------------------------------------------
3637 PROCEDURE prepare_temp_table_data(
3638 p_temp_table_key IN NUMBER,
3639 p_document_id IN NUMBER
3640 )
3641 IS
3642 d_mod CONSTANT VARCHAR2(100) :=
3643 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'prepare_temp_table_data');
3644 d_position NUMBER := 0;
3645
3646 l_base_currency_code GL_CURRENCIES.currency_code%TYPE;
3647 l_doc_currency_code GL_CURRENCIES.currency_code%TYPE;
3648 l_precision GL_CURRENCIES.precision%TYPE;
3649 l_mau GL_CURRENCIES.minimum_accountable_unit%TYPE;
3650 BEGIN
3651
3652 IF PO_LOG.d_proc THEN
3653 PO_LOG.proc_begin(d_mod,'p_temp_table_key',p_temp_table_key);
3654 PO_LOG.proc_begin(d_mod,'p_document_id',p_document_id);
3655 END IF;
3656
3657 -- The results will be in PO currency (foreign currency).
3658 -- arusingh <Complex Work R12 TODO>:refactor API to handle release_id case
3659 PO_CORE_S2.get_po_currency(
3660 x_object_id => p_document_id, --in param
3661 x_base_currency => l_base_currency_code, --out param
3662 x_po_currency => l_doc_currency_code --out param
3663 );
3664
3665 d_position := 10;
3666
3667 -- Retrieve the foreign currency precision/mau for rounding
3668 PO_CORE_S2.get_currency_info(
3669 x_currency_code => l_doc_currency_code, --in param
3670 x_precision => l_precision, --out param
3671 x_min_unit => l_mau --out param
3672 );
3673
3674 d_position := 20;
3675
3676 --SQL What: Calculate the amount columns for quantity based rows
3677 --SQL Where: Quantity-based rows
3678 UPDATE PO_DOCUMENT_TOTALS_GT GTT
3679 SET
3680 GTT.amount_total = (GTT.quantity_total * GTT.price)
3681 , GTT.amount_billed = (GTT.quantity_billed * GTT.price)
3682 , GTT.amount_delivered = (GTT.quantity_delivered * GTT.price)
3683 , GTT.amount_financed = (GTT.quantity_financed * GTT.price)
3684 , GTT.amount_recouped = (GTT.quantity_recouped * GTT.price)
3685 , GTT.amount_received = (GTT.quantity_received * GTT.price)
3686 , GTT.amount_shipped = (GTT.quantity_shipped * GTT.price)
3687 WHERE amount_based_flag = 'N'
3688 ;
3689
3690 d_position := 30;
3691
3692 --SQL What: Round the calculated amounts to correct precision
3693 --SQL Where: Quantity-based rows
3694 UPDATE PO_DOCUMENT_TOTALS_GT GTT
3695 SET
3696 GTT.amount_total = nvl2(l_mau
3697 , round(amount_total/l_mau) * l_mau
3698 , round(amount_total, l_precision))
3699 , GTT.amount_billed = nvl2(l_mau
3700 , round(amount_billed/l_mau) * l_mau
3701 , round(amount_billed, l_precision))
3702 , GTT.amount_delivered = nvl2(l_mau
3703 , round(amount_delivered/l_mau) * l_mau
3704 , round(amount_delivered, l_precision))
3705 , GTT.amount_financed = nvl2(l_mau
3706 , round(amount_financed/l_mau) * l_mau
3707 , round(amount_financed, l_precision))
3708 , GTT.amount_recouped = nvl2(l_mau
3709 , round(amount_recouped/l_mau) * l_mau
3710 , round(amount_recouped, l_precision))
3711 , GTT.amount_received = nvl2(l_mau
3712 , round(amount_received/l_mau) * l_mau
3713 , round(amount_received, l_precision))
3714 , GTT.amount_shipped = nvl2(l_mau
3715 , round(amount_shipped/l_mau) * l_mau
3716 , round(amount_shipped, l_precision))
3717 WHERE GTT.amount_based_flag = 'N'
3718 ;
3719
3720 d_position := 40;
3721
3722 IF PO_LOG.d_stmt THEN
3723 PO_LOG.stmt(d_mod,d_position,'Updated amts - rowcount:',SQL%ROWCOUNT);
3724 PO_LOG.proc_end(d_mod);
3725 END IF;
3726
3727 EXCEPTION
3728 WHEN OTHERS THEN
3729 IF PO_LOG.d_exc THEN
3730 PO_LOG.exc(d_mod,d_position,NULL);
3731 END IF;
3732 RAISE;
3733 END prepare_temp_table_data;
3734
3735
3736
3737 -------------------------------------------------------------------------------
3738 --Start of Comments
3739 --Name: calculate_totals
3740 --Pre-reqs:
3741 -- PO_DOCUMENT_TOTALS_GT must be populated appropriately
3742 --Modifies:
3743 -- None.
3744 --Locks:
3745 -- None.
3746 --Function:
3747 -- Based on the values in the temp table, calculates totals for various
3748 -- columns (total order, billed, etc.). Handles normal and Complex Work cases.
3749 --Parameters:
3750 --IN:
3751 --p_temp_table_key
3752 -- The unique key value that identifies all rows in PO_DOCUMENT_TOTALS_GT
3753 -- related to this transaction
3754 --p_document_id
3755 -- The po_header_id for POs; the po_release_id for Releases
3756 --p_doc_level
3757 -- The level for which calculations are being done. Use g_doc_level_<>
3758 -- HEADER
3759 -- LINE
3760 -- SHIPMENT
3761 -- DISTRIBUTION
3762 --OUT:
3763 --x_quantity_total
3764 -- The total active (uncancelled) quantity ordered for the document level
3765 --x_amount_total
3766 -- The total active (uncancelled) amount ordered for the document level
3767 --x_quantity_delivered
3768 -- The total quantity delivered for the document level
3769 --x_amount_delivered
3770 -- The total amount delivered for the document level
3771 --x_quantity_received
3772 -- The total quantity received for the document level.
3773 -- Always zero if the document level is 'DISTRIBUTION'
3774 --x_amount_received
3775 -- The total amount received for the document level
3776 -- Always zero if the document level is 'DISTRIBUTION'
3777 --x_quantity_shipped
3778 -- The total quantity shipped for the document level.
3779 -- Always zero if the document level is 'DISTRIBUTION'
3780 --x_amount_shipped
3781 -- The total amount shipped for the document level
3782 -- Always zero if the document level is 'DISTRIBUTION'
3783 --x_quantity_billed
3784 -- The total quantity billed for the document level
3785 --x_amount_billed
3786 -- The total amount billed for the document level
3787 --x_quantity_financed
3788 -- The total quantity financed for the document level
3789 --x_amount_financed
3790 -- The total amount financed for the document level
3791 --x_quantity_recouped
3792 -- The total quantity recouped for the document level
3793 --x_amount_recouped
3794 -- The total amount recouped for the document level
3795 --x_retainage_withheld_amount
3796 -- The total retainage withheld for the document level
3797 --x_retainage_released_amount
3798 -- The total retainage released for the document level
3799 --Testing:
3800 --
3801 --End of Comments
3802 -------------------------------------------------------------------------------
3803 PROCEDURE calculate_totals(
3804 p_temp_table_key IN NUMBER,
3805 p_document_id IN NUMBER,
3806 p_doc_level IN VARCHAR2,
3807 x_quantity_total OUT NOCOPY NUMBER,
3808 x_amount_total OUT NOCOPY NUMBER,
3809 x_quantity_delivered OUT NOCOPY NUMBER,
3810 x_amount_delivered OUT NOCOPY NUMBER,
3811 x_quantity_received OUT NOCOPY NUMBER,
3812 x_amount_received OUT NOCOPY NUMBER,
3813 x_quantity_shipped OUT NOCOPY NUMBER,
3814 x_amount_shipped OUT NOCOPY NUMBER,
3815 x_quantity_billed OUT NOCOPY NUMBER,
3816 x_amount_billed OUT NOCOPY NUMBER,
3817 x_quantity_financed OUT NOCOPY NUMBER,
3818 x_amount_financed OUT NOCOPY NUMBER,
3819 x_quantity_recouped OUT NOCOPY NUMBER,
3820 x_amount_recouped OUT NOCOPY NUMBER,
3821 x_retainage_withheld_amount OUT NOCOPY NUMBER,
3822 x_retainage_released_amount OUT NOCOPY NUMBER
3823 )
3824 IS
3825 d_mod CONSTANT VARCHAR2(100) :=
3826 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'calculate_totals');
3827 d_position NUMBER := 0;
3828
3829 l_quantity_total_actuals NUMBER :=0;
3830 l_quantity_total_financing NUMBER := 0;
3831 l_quantity_delivered_actuals NUMBER :=0;
3832 l_quantity_delivered_financing NUMBER := 0;
3833 l_quantity_received_actuals NUMBER := 0;
3834 l_quantity_received_financing NUMBER := 0;
3835 l_quantity_shipped_actuals NUMBER := 0;
3836 l_quantity_shipped_financing NUMBER := 0;
3837 l_quantity_billed NUMBER := 0;
3838 l_quantity_financed NUMBER := 0;
3839 l_quantity_recouped NUMBER := 0;
3840 l_amount_total_actuals NUMBER := 0;
3841 l_amount_total_financing NUMBER := 0;
3842 l_amount_delivered_actuals NUMBER := 0;
3843 l_amount_delivered_financing NUMBER := 0;
3844 l_amount_received_actuals NUMBER := 0;
3845 l_amount_received_financing NUMBER := 0;
3846 l_amount_shipped_actuals NUMBER := 0;
3847 l_amount_shipped_financing NUMBER := 0;
3848 l_amount_billed NUMBER := 0;
3849 l_amount_financed NUMBER := 0;
3850 l_amount_recouped NUMBER := 0;
3851 l_retainage_withheld_amount NUMBER := 0;
3852 l_retainage_released_amount NUMBER := 0;
3853 l_is_complex_work_po BOOLEAN := FALSE;
3854 BEGIN
3855
3856 IF PO_LOG.d_proc THEN
3857 PO_LOG.proc_begin(d_mod,'p_temp_table_key',p_temp_table_key);
3858 PO_LOG.proc_begin(d_mod,'p_document_id',p_document_id);
3859 PO_LOG.proc_begin(d_mod,'p_doc_level',p_doc_level);
3860 END IF;
3861
3862 l_is_complex_work_po :=
3863 PO_COMPLEX_WORK_PVT.is_complex_work_po(p_document_id);
3864
3865 d_position := 10;
3866
3867 -- This method will sum the various columns. Note that we need to store
3868 -- separate sums for actuals vs. financing for the received, shipped and
3869 -- delivered columns. This is because RCV reuses the same columns for both
3870 -- financing and actual line locations. We do not need to do this for
3871 -- billed and financed, because AP will only use the billed column for
3872 -- actuals and the financed column for financing. And since retainage only
3873 -- applies to actual line locations, it does not make sense to store a
3874 -- separate variable for retainage on financing line locations.
3875
3876 -- First, do the calculations for the amount rows, as this calculation
3877 -- is the same for all cases
3878
3879 -- SQL WHAT: Sum up the amount columns.
3880 -- SQL WHERE: All rows in the GTT for this trxn
3881 SELECT
3882 SUM(CASE WHEN GTT.shipment_type <> C_ship_type_PREPAYMENT
3883 THEN amount_total ELSE 0 END) amount_total_actuals,
3884 SUM(CASE WHEN GTT.shipment_type = C_ship_type_PREPAYMENT
3885 THEN amount_total ELSE 0 END) amount_total_financing,
3886 SUM(CASE WHEN GTT.shipment_type <> C_ship_type_PREPAYMENT
3887 THEN amount_delivered ELSE 0 END) amount_delivered_actuals,
3888 SUM(CASE WHEN GTT.shipment_type = C_ship_type_PREPAYMENT
3889 THEN amount_delivered ELSE 0 END) amount_delivered_financing,
3890 SUM(CASE WHEN GTT.shipment_type <> C_ship_type_PREPAYMENT
3891 THEN amount_received ELSE 0 END) amount_received_actuals,
3892 SUM(CASE WHEN GTT.shipment_type = C_ship_type_PREPAYMENT
3893 THEN amount_received ELSE 0 END) amount_received_financing,
3894 SUM(CASE WHEN GTT.shipment_type <> C_ship_type_PREPAYMENT
3895 THEN amount_shipped ELSE 0 END) amount_shipped_actuals,
3896 SUM(CASE WHEN GTT.shipment_type = C_ship_type_PREPAYMENT
3897 THEN amount_shipped ELSE 0 END) amount_shipped_financing,
3898 SUM(amount_billed),
3899 SUM(amount_financed),
3900 SUM(amount_recouped),
3901 SUM(retainage_withheld_amount),
3902 SUM(retainage_released_amount)
3903 INTO
3904 l_amount_total_actuals,
3905 l_amount_total_financing,
3906 l_amount_delivered_actuals,
3907 l_amount_delivered_financing,
3908 l_amount_received_actuals,
3909 l_amount_received_financing,
3910 l_amount_shipped_actuals,
3911 l_amount_shipped_financing,
3912 l_amount_billed,
3913 l_amount_financed,
3914 l_amount_recouped,
3915 l_retainage_withheld_amount,
3916 l_retainage_released_amount
3917 FROM PO_DOCUMENT_TOTALS_GT GTT
3918 WHERE key = p_temp_table_key
3919 ;
3920
3921 d_position := 20;
3922 IF PO_LOG.d_stmt THEN
3923 PO_LOG.stmt(d_mod,d_position,'Amount totals - rowcount:',SQL%ROWCOUNT);
3924 END IF;
3925
3926 -- Next, do the calculations for the quantity rows, which is broken into
3927 -- 2 cases: a summation case and a max-value case
3928
3929 IF (NOT l_is_complex_work_po) THEN
3930 -- Normal Shipments (non-Complex Work) case
3931
3932 d_position := 30;
3933
3934 -- SQL WHAT: Sums up the quantity columns for normal shipments
3935 -- SQL WHERE: All qty-based GTT rows for this trxn
3936 SELECT
3937 SUM(quantity_total),
3938 SUM(quantity_delivered),
3939 SUM(quantity_received),
3940 SUM(quantity_shipped),
3941 SUM(quantity_billed),
3942 SUM(quantity_financed),
3943 SUM(quantity_recouped)
3944 INTO
3945 l_quantity_total_actuals,
3946 l_quantity_delivered_actuals,
3947 l_quantity_received_actuals,
3948 l_quantity_shipped_actuals,
3949 l_quantity_billed,
3950 l_quantity_financed,
3951 l_quantity_recouped
3952 FROM PO_DOCUMENT_TOTALS_GT GTT
3953 WHERE GTT.key = p_temp_table_key
3954 AND GTT.amount_based_flag = 'N'
3955 AND nvl(GTT.payment_type, 'NULL') <> C_payment_type_RATE
3956 ;
3957
3958 d_position := 40;
3959 IF PO_LOG.d_stmt THEN
3960 PO_LOG.stmt(d_mod,d_position,'Sum Qty totals - rowcount:',SQL%ROWCOUNT);
3961 END IF;
3962
3963 ELSE
3964 -- Qty Milestone Pay Items case for Header, Line, Line Loc level totals
3965
3966 d_position := 40;
3967
3968 -- SQL WHAT: For Complex Work Qty-based lines, the total is based on
3969 -- the max received, billed etc against the individual Milestone pay items
3970 -- SQL WHERE: All qty-based GTT rows for this trxn
3971 SELECT
3972 MAX(GTTSUM.qty_total_actuals),
3973 MAX(GTTSUM.qty_total_financing),
3974 MAX(GTTSUM.qty_delivered_actuals),
3975 MAX(GTTSUM.qty_delivered_financing),
3976 MAX(GTTSUM.qty_received_actuals),
3977 MAX(GTTSUM.qty_received_financing),
3978 MAX(GTTSUM.qty_shipped_actuals),
3979 MAX(GTTSUM.qty_shipped_financing),
3980 MAX(GTTSUM.qty_billed),
3981 MAX(GTTSUM.qty_financed),
3982 MAX(GTTSUM.qty_recouped)
3983 INTO
3984 l_quantity_total_actuals,
3985 l_quantity_total_financing,
3986 l_quantity_delivered_actuals,
3987 l_quantity_delivered_financing,
3988 l_quantity_received_actuals,
3989 l_quantity_received_financing,
3990 l_quantity_shipped_actuals,
3991 l_quantity_shipped_financing,
3992 l_quantity_billed,
3993 l_quantity_financed,
3994 l_quantity_recouped
3995 FROM
3996 ( SELECT
3997 GTT.line_location_id,
3998 SUM(CASE
3999 WHEN GTT.shipment_type <> C_ship_type_PREPAYMENT
4000 THEN GTT.quantity_total ELSE 0 END) qty_total_actuals,
4001 SUM(CASE
4002 WHEN GTT.shipment_type = C_ship_type_PREPAYMENT
4003 THEN GTT.quantity_total ELSE 0 END) qty_total_financing,
4004 SUM(CASE
4005 WHEN GTT.shipment_type <> C_ship_type_PREPAYMENT
4006 THEN GTT.quantity_delivered ELSE 0 END) qty_delivered_actuals,
4007 SUM(CASE
4008 WHEN GTT.shipment_type = C_ship_type_PREPAYMENT
4009 THEN GTT.quantity_delivered ELSE 0 END) qty_delivered_financing,
4010 SUM(CASE
4011 WHEN GTT.shipment_type <> C_ship_type_PREPAYMENT
4012 THEN GTT.quantity_received ELSE 0 END) qty_received_actuals,
4013 SUM(CASE
4014 WHEN GTT.shipment_type = C_ship_type_PREPAYMENT
4015 THEN GTT.quantity_received ELSE 0 END) qty_received_financing,
4016 SUM(CASE
4017 WHEN GTT.shipment_type <> C_ship_type_PREPAYMENT
4018 THEN GTT.quantity_shipped ELSE 0 END) qty_shipped_actuals,
4019 SUM(CASE
4020 WHEN GTT.shipment_type = C_ship_type_PREPAYMENT
4021 THEN GTT.quantity_shipped ELSE 0 END) qty_shipped_financing,
4022 SUM(GTT.quantity_billed) qty_billed,
4023 SUM(GTT.quantity_financed) qty_financed,
4024 SUM(GTT.quantity_recouped) qty_recouped
4025 FROM PO_DOCUMENT_TOTALS_GT GTT
4026 WHERE GTT.key = p_temp_table_key
4027 AND GTT.amount_based_flag = 'N'
4028 AND nvl(GTT.payment_type, 'NULL') = C_payment_type_MILESTONE
4029 GROUP BY GTT.line_location_id
4030 ) GTTSUM
4031 ;
4032
4033 d_position := 50;
4034 IF PO_LOG.d_stmt THEN
4035 PO_LOG.stmt(d_mod,d_position,'Max Qty totals - rowcount:',SQL%ROWCOUNT);
4036 END IF;
4037
4038 END IF;
4039
4040 -- Assign return values. Always return the actuals result value if both
4041 -- actuals and financing values exist.
4042 x_quantity_total :=
4043 CASE WHEN (l_quantity_total_actuals > 0) THEN l_quantity_total_actuals
4044 ELSE l_quantity_total_financing END;
4045
4046 x_amount_total :=
4047 CASE WHEN (l_amount_total_actuals > 0) THEN l_amount_total_actuals
4048 ELSE l_amount_total_financing END;
4049
4050 x_quantity_delivered :=
4051 CASE WHEN (l_quantity_delivered_actuals > 0) THEN l_quantity_delivered_actuals
4052 ELSE l_quantity_delivered_financing END;
4053
4054 x_amount_delivered :=
4055 CASE WHEN (l_amount_delivered_actuals > 0) THEN l_amount_delivered_actuals
4056 ELSE l_amount_delivered_financing END;
4057
4058 x_quantity_received :=
4059 CASE WHEN (l_quantity_received_actuals > 0) THEN l_quantity_received_actuals
4060 ELSE l_quantity_received_financing END;
4061
4062 x_amount_received :=
4063 CASE WHEN (l_amount_received_actuals > 0) THEN l_amount_received_actuals
4064 ELSE l_amount_received_financing END;
4065
4066 x_quantity_shipped :=
4067 CASE WHEN (l_quantity_shipped_actuals > 0) THEN l_quantity_shipped_actuals
4068 ELSE l_quantity_shipped_financing END;
4069
4070 x_amount_shipped :=
4071 CASE WHEN (l_amount_shipped_actuals > 0) THEN l_amount_shipped_actuals
4072 ELSE l_amount_shipped_financing END;
4073
4074 x_quantity_billed := l_quantity_billed;
4075 x_amount_billed := l_amount_billed;
4076 x_quantity_financed := l_quantity_financed;
4077 x_amount_financed := l_amount_financed;
4078 x_quantity_recouped := l_quantity_recouped;
4079 x_amount_recouped := l_amount_recouped;
4080 x_retainage_withheld_amount := l_retainage_withheld_amount;
4081 x_retainage_released_amount := l_retainage_released_amount;
4082
4083
4084 IF PO_LOG.d_proc THEN
4085 PO_LOG.proc_end(d_mod,'x_quantity_total',x_quantity_total);
4086 PO_LOG.proc_end(d_mod,'x_amount_total', x_amount_total);
4087 PO_LOG.proc_end(d_mod,'x_quantity_delivered', x_quantity_delivered);
4088 PO_LOG.proc_end(d_mod,'x_amount_delivered', x_amount_delivered);
4089 PO_LOG.proc_end(d_mod,'x_quantity_received', x_quantity_received);
4090 PO_LOG.proc_end(d_mod,'x_amount_received', x_amount_received);
4091 PO_LOG.proc_end(d_mod,'x_quantity_shipped', x_quantity_shipped);
4092 PO_LOG.proc_end(d_mod,'x_amount_shipped', x_amount_shipped);
4093 PO_LOG.proc_end(d_mod,'x_quantity_billed', x_quantity_billed);
4094 PO_LOG.proc_end(d_mod,'x_amount_billed', x_amount_billed);
4095 PO_LOG.proc_end(d_mod,'x_quantity_financed', x_quantity_financed);
4096 PO_LOG.proc_end(d_mod,'x_amount_financed', x_amount_financed);
4097 PO_LOG.proc_end(d_mod,'x_quantity_recouped', x_quantity_recouped);
4098 PO_LOG.proc_end(d_mod,'x_amount_recouped', x_amount_recouped);
4099 PO_LOG.proc_end(d_mod,'x_retainage_withheld_amount', x_retainage_withheld_amount);
4100 PO_LOG.proc_end(d_mod,'x_retainage_released_amount', x_retainage_released_amount);
4101 END IF;
4102
4103 EXCEPTION
4104 WHEN OTHERS THEN
4105 IF PO_LOG.d_exc THEN
4106 PO_LOG.exc(d_mod,d_position,NULL);
4107 END IF;
4108 RAISE;
4109 END calculate_totals;
4110
4111
4112 -------------------------------------------------------------------------------
4113 --Start of Comments
4114 --Name: clear_temp_table
4115 --Pre-reqs:
4116 -- None.
4117 --Modifies:
4118 -- PO_DOCUMENT_TOTALS_GT
4119 --Locks:
4120 -- None.
4121 --Function:
4122 -- Deletes data from the temp table for this transaction
4123 --Parameters:
4124 --IN:
4125 --p_temp_table_key
4126 -- The unique key value that identifies all rows in PO_DOCUMENT_TOTALS_GT
4127 -- related to this transaction
4128 --Testing:
4129 --
4130 --End of Comments
4131 -------------------------------------------------------------------------------
4132 PROCEDURE clear_temp_table(
4133 p_temp_table_key IN NUMBER
4134 )
4135 IS
4136 d_mod CONSTANT VARCHAR2(100) :=
4137 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'clear_temp_table');
4138 d_position NUMBER := 0;
4139
4140 BEGIN
4141
4142 IF PO_LOG.d_proc THEN
4143 PO_LOG.proc_begin(d_mod,'p_temp_table_key',p_temp_table_key);
4144 END IF;
4145
4146 d_position := 10;
4147
4148 -- SQL WHAT: Clear the temp table
4149 -- SQL WHERE: All data for this transaction
4150 DELETE FROM PO_DOCUMENT_TOTALS_GT
4151 WHERE key = p_temp_table_key
4152 ;
4153
4154 d_position := 20;
4155 IF PO_LOG.d_stmt THEN
4156 PO_LOG.stmt(d_mod,d_position,'Deleted data - rowcount:',SQL%ROWCOUNT);
4157 END IF;
4158
4159 IF PO_LOG.d_proc THEN
4160 PO_LOG.proc_end(d_mod);
4161 END IF;
4162
4163 EXCEPTION
4164 WHEN OTHERS THEN
4165 IF PO_LOG.d_exc THEN
4166 PO_LOG.exc(d_mod,d_position,NULL);
4167 END IF;
4168 RAISE;
4169 END clear_temp_table;
4170
4171
4172 END PO_DOCUMENT_TOTALS_PVT;
[Home] [Help]