DBA Data[Home] [Help]

PACKAGE BODY: APPS.PO_DOCUMENT_TOTALS_PVT

Source


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