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;