[Home] [Help]
Skip to content
PACKAGE BODY: APPS.POS_TOTALS_PO_SV
Source
1 PACKAGE BODY POS_TOTALS_PO_SV as
2 /* $Header: POSPOTOB.pls 120.10 2006/09/23 23:19:04 shgao noship $ */
3
4 FUNCTION get_po_total
5 (X_header_id number) return number is
6 X_po_total number;
7
8
9 x_min_unit NUMBER;
10 x_precision NUMBER;
11 l_document_type varchar2(30);
12 l_revision_num number;
13
14 BEGIN
15 /* Always calculate the total from archive tables. */
16
17 select type_lookup_code, revision_num
18 into l_document_type, l_revision_num
19 from po_headers_archive_all
20 where po_header_id = X_header_id and latest_external_flag = 'Y';
21
22 X_po_total := POS_TOTALS_PO_SV.get_po_archive_total(X_header_id, l_revision_num,l_document_type);
23 RETURN (X_po_total);
24
25 EXCEPTION
26 WHEN OTHERS then
27 x_po_total := 0;
28 return(x_po_total);
29 END get_po_total;
30
31 FUNCTION get_amount_ordered
32 (X_header_id number,
33 X_revision_num number,
34 X_doc_type varchar) return number is
35 X_po_total number;
36
37
38 x_min_unit NUMBER;
39 x_precision NUMBER;
40 x_global_agree_flag VARCHAR2(1);
41
42 BEGIN
43
44 SELECT fc.minimum_accountable_unit,
45 fc.precision,
46 global_agreement_flag
47 INTO x_min_unit,
48 x_precision,
49 x_global_agree_flag
50 FROM fnd_currencies fc,
51 po_headers_archive_all pha
52 WHERE pha.po_header_id = X_header_id
53 AND pha.revision_num = X_revision_num
54 AND fc.currency_code = pha.currency_code;
55
56 if ( x_global_agree_flag = 'Y') then
57 if (x_min_unit is not null) then
58 SELECT sum ( round ( (decode(pol.quantity, null,
59 (pod.amount_ordered -
60 pod.amount_cancelled),
61 (( pod.quantity_ordered
62 - pod.quantity_cancelled )
63 * poll.price_override)
64 )
65 )
66 / x_min_unit )
67 * x_min_unit )
68 into x_po_total
69 FROM po_distributions_archive_all pod,
70 po_line_locations_archive_all poll,
71 po_lines_archive_all pol
72 WHERE pod.line_location_id = poll.line_location_id
73 AND poll.po_line_id = pol.po_line_id
74 AND pol.from_header_id = X_header_id
75 AND poll.latest_external_flag ='Y'
76 AND pol.latest_external_Flag = 'Y'
77 AND pod.latest_external_flag = 'Y';
78 else
79 SELECT sum (decode(pol.quantity, null,
80 (pod.amount_ordered -
81 pod.amount_cancelled),
82 (( pod.quantity_ordered
83 - pod.quantity_cancelled )
84 * poll.price_override)))
85 into x_po_total
86 FROM po_distributions_archive_all pod,
87 po_line_locations_archive_all poll,
88 po_lines_archive_all pol
89 WHERE pod.line_location_id = poll.line_location_id
90 AND poll.po_line_id = pol.po_line_id
91 AND pol.from_header_id = X_header_id
92 AND poll.latest_external_flag ='Y'
93 AND pol.latest_external_Flag = 'Y'
94 AND pod.latest_external_flag = 'Y';
95
96
97 end if;
98 ELSE
99
100 if x_min_unit is null then
101
102 select sum(round(
103 decode(pll.quantity,
104 null,
105 (pll.amount - nvl(pll.amount_cancelled,0)),
106 (pll.quantity - nvl(pll.quantity_cancelled,0))
107 * nvl(pll.price_override,0)
108 )
109 ,x_precision))
110 INTO x_po_total
111 FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
112 WHERE PLL.po_header_id = x_header_id
113 AND PLL.LATEST_EXTERNAL_FLAG= 'Y'
114 AND PLL.shipment_type in ('BLANKET','SCHEDULED');
115
116 else
117
118 select sum(round(
119 decode(pll.quantity,
120 null,
121 (pll.amount - nvl(pll.amount_cancelled, 0)),
122 (pll.quantity - nvl(pll.quantity_cancelled, 0))
123 * nvl(pll.price_override,0)
124 )
125 / x_min_unit)
126 * x_min_unit)
127 INTO x_po_total
128 FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
129 WHERE PLL.po_header_id = x_header_id
130 AND PLL.LATEST_EXTERNAL_FLAG= 'Y'
131 AND PLL.shipment_type in ('BLANKET','SCHEDULED');
132
133
134
135 END IF;
136 END IF;
137
138 RETURN (X_po_total);
139 EXCEPTION
140 WHEN OTHERS then
141 x_po_total := 0;
142 return (X_po_total);
143
144 END get_amount_ordered;
145
146
147
148 FUNCTION get_po_archive_total
149 (X_header_id number,
150 X_revision_num number,
151 X_doc_type varchar) return number is
152 X_po_total number;
153
154
155 x_min_unit NUMBER;
156 x_precision NUMBER;
157 x_org_id NUMBER;
158
159 BEGIN
160
161 --togeorge 11/15/2000
162 --changed org specific views to _all tables
163 if (X_doc_type in ('STANDARD')) then
164
165 select org_id
166 into x_org_id
167 from po_headers_all
168 where po_header_id = x_header_id;
169
170 PO_MOAC_UTILS_PVT.set_org_context(x_org_id) ;
171
172
173 --x_po_total := PO_CORE_S.get_archive_total_for_any_rev (x_header_id,'H','PO',x_doc_type,x_revision_num,'N');
174 x_po_total := PO_DOCUMENT_TOTALS_PVT.getAmountOrdered('HEADER',x_header_id,'ARCHIVE',x_revision_num);
175
176 elsif (X_doc_type in ('PLANNED')) then
177 /* we should call the same PO api for PLANNED POs as well. Till PO enhaces the API, we will continue to duplicate */
178
179 -- x_po_total := get_archive_total_for_any_rev (x_header_id,'H','PO',x_doc_type,x_revision_num,'N');
180
181 SELECT fc.minimum_accountable_unit,
182 fc.precision
183 INTO x_min_unit,
184 x_precision
185 FROM fnd_currencies fc,
186 po_headers_archive_all pha
187 WHERE pha.po_header_id = X_header_id
188 AND pha.revision_num = X_revision_num
189 AND fc.currency_code = pha.currency_code;
190
191 if (x_min_unit is null) then
192 select sum(round(
193 (plla1.quantity - nvl (plla1.quantity_cancelled, 0)) *
194 nvl(plla1.price_override, 0), x_precision)
195 )
196
197 INTO X_po_total
198 FROM po_line_locations_archive_all plla1
199 where po_header_id = X_header_id
200 and shipment_type in ('PLANNED')
201 and revision_num = (
202 SELECT max(plla2.revision_num)
203 FROM PO_LINE_LOCATIONS_ARCHIVE_ALL plla2
204 WHERE plla2.revision_num <= X_revision_num
205 AND plla2.line_location_id = plla1.line_location_id );
206 else
207 select sum(round((plla1.quantity -
208 nvl(plla1.quantity_cancelled,0)) *
209 nvl(plla1.price_override,0)/x_min_unit)*
210 x_min_unit)
211 INTO X_po_total
212 FROM po_line_locations_archive_all plla1 --po_line_locations_archive
213 WHERE po_header_id = X_header_id
214 AND shipment_type IN ('PLANNED')
215 AND revision_num = (
216 SELECT max( plla2.revision_num )
217 FROM po_line_locations_archive_all plla2 --po_line_locations_archive
218 WHERE plla2.revision_num <= X_revision_num
219 AND plla2.line_location_id = plla1.line_location_id ) ;
220 end if;
221
222 else
223 SELECT BLANKET_TOTAL_AMOUNT
224 INTO X_po_total
225 FROM po_headers_archive_all
226 WHERE revision_num = X_revision_num
227 AND po_header_id = X_header_id;
228 end if;
229
230 RETURN (X_po_total);
231 EXCEPTION
232 WHEN OTHERS then
233 x_po_total := 0;
234 return (X_po_total);
235
236 END get_po_archive_total;
237
238
239
240
241 FUNCTION get_release_archive_total
242 (X_release_id number,
243 X_revision_num number) return number is
244 X_po_total number;
245
246
247 x_min_unit NUMBER;
248 x_precision NUMBER;
249
250 x_org_id NUMBER;
251
252 BEGIN
253
254
255
256 /* x_po_total := po_core_s.get_archive_total_for_any_rev (x_release_id,'R','PO','RELEASE',x_revision_num,'N'); */
257
258 SELECT fc.minimum_accountable_unit,
259 fc.precision
260 INTO x_min_unit,
261 x_precision
262 FROM fnd_currencies fc,
263 po_headers_archive_all pha,
264 po_releases_archive_all pra
265 WHERE pha.po_header_id = pra.po_header_id
266 AND pha.LATEST_EXTERNAL_FLAG = 'Y'
267 AND pra.po_release_id = X_release_id
268 AND pra.revision_num = X_revision_num
269 AND fc.currency_code = pha.currency_code;
270
271
272
273 if x_min_unit is null then
274 select sum(round(
275 decode(plla1.quantity,
276 null,
277 (plla1.amount - nvl(plla1.amount_cancelled,0)),
278 ((plla1.quantity - nvl(plla1.quantity_cancelled,0)) *
279 nvl(plla1.price_override,0))
280 ) ,x_precision))
281 into X_po_total
282 FROM po_line_locations_archive_all plla1
283 WHERE po_release_id = X_release_id
284 AND shipment_type IN ('BLANKET','SCHEDULED')
285 AND revision_num = (
286 SELECT max( plla2.revision_num )
287 FROM po_line_locations_archive_all plla2
288 WHERE plla2.revision_num <= X_revision_num
289 AND plla2.line_location_id = plla1.line_location_id ) ;
290
291 else
292
293 select sum(round(decode(plla1.quantity,
294 null,
295 (plla1.amount - nvl(plla1.amount_cancelled,0)),
296 ((plla1.quantity -nvl(plla1.quantity_cancelled,0)) *
297 nvl(plla1.price_override,0)))/x_min_unit)*
298 x_min_unit)
299 into X_po_total
300 FROM po_line_locations_archive_all plla1
301 WHERE po_release_id = X_release_id
302 AND shipment_type IN ('BLANKET','SCHEDULED')
303 AND revision_num = (
304 SELECT max( plla2.revision_num )
305 FROM po_line_locations_archive_all plla2
306 WHERE plla2.revision_num <= X_revision_num
307 AND plla2.line_location_id = plla1.line_location_id ) ;
308 end if;
309
310
311
312 RETURN (X_po_total);
313
314 EXCEPTION
315 WHEN OTHERS then
316 x_po_total := 0;
317 return (X_po_total);
318
319 END GET_RELEASE_ARCHIVE_TOTAL;
320
321
322
323
324 FUNCTION get_line_total
325 (x_po_header_id in number,
326 x_po_release_id in number,
327 x_po_line_id in number,
328 X_revision_num in number ) return number is
329
330 X_po_total number;
331
332
333 x_min_unit NUMBER;
334 x_precision NUMBER;
335 x_org_id NUMBER;
336
337
338 BEGIN
339 --Bug 5159144
340 IF (PO_COMPLEX_WORK_PVT.is_financing_po(x_po_header_id)) THEN
341
342 SELECT fc.minimum_accountable_unit,
343 fc.precision
344 INTO x_min_unit,
345 x_precision
346 FROM fnd_currencies fc,
347 po_headers_archive_all poh
348 WHERE poh.revision_num = x_revision_num
349 AND poh.po_header_id = x_po_header_id
350 AND fc.currency_code = poh.currency_code;
351
352 if (x_min_unit is null) then
353 select round(
354 decode(plaa1.quantity,
355 null,
356 plaa1.amount ,
357 (plaa1.quantity
358 * nvl(plaa1.unit_price,0)))
359 ,x_precision)
360 INTO X_po_total
361 FROM po_lines_archive_all plaa1
362 where plaa1.po_line_id = x_po_line_id
363 and revision_num = (
364 SELECT max(plaa2.revision_num)
365 FROM po_lines_archive_all plaa2
366 WHERE plaa2.revision_num <= x_revision_num
367 AND plaa2.po_line_id = plaa1.po_line_id );
368 else
369 select round(
370 decode(plaa1.quantity,
371 null,
372 plaa1.amount ,
373 (plaa1.quantity
374 * nvl(plaa1.unit_price,0)
375 )
376 )/x_min_unit)*x_min_unit
377 INTO X_po_total
378 FROM po_lines_archive_all plaa1
379 WHERE plaa1.po_line_id = x_po_line_id
380 AND revision_num = (
381 SELECT max( plaa2.revision_num )
382 FROM po_lines_archive_all plaa2
383 WHERE plaa2.revision_num <= x_revision_num
384 AND plaa2.po_line_id = plaa1.po_line_id ) ;
385 end if;
386
387
388 ELSE
389
390 if x_po_release_id is not null then
391
392 SELECT fc.minimum_accountable_unit,
393 fc.precision
394 INTO x_min_unit,
395 x_precision
396 FROM PO_HEADERS_ALL POH,
397 FND_CURRENCIES FC,
398 PO_RELEASES_ARCHIVE_ALL POR
399 WHERE POR.po_release_id = x_po_release_id
400 AND por.revision_num = x_revision_num
401 AND POH.po_header_id = POR.po_header_id
402 AND FC.CURRENCY_CODE = POH.CURRENCY_CODE;
403 else
404 SELECT fc.minimum_accountable_unit,
405 fc.precision
406 INTO x_min_unit,
407 x_precision
408 FROM fnd_currencies fc,
409 po_headers_archive_all poh
410 WHERE poh.revision_num = x_revision_num
411 AND poh.po_header_id = x_po_header_id
412 AND fc.currency_code = poh.currency_code;
413
414 end if;
415
416 if (x_po_release_id is null) then
417 if (x_min_unit is null) then
418 select sum(round((
419 decode(plla1.quantity,
420 null,
421 (plla1.amount - nvl(plla1.amount_cancelled, 0)),
422 (plla1.quantity - nvl(plla1.quantity_cancelled,0))
423 * nvl(plla1.price_override,0))),x_precision))
424 INTO X_po_total
425 FROM po_line_locations_archive_all plla1
426 where plla1.po_line_id = x_po_line_id
427 and shipment_type in ('STANDARD','PLANNED')
428 and revision_num = (
429 SELECT max(plla2.revision_num)
430 FROM PO_LINE_LOCATIONS_ARCHIVE_ALL plla2
431 WHERE plla2.revision_num <= x_revision_num
432 AND plla2.line_location_id = plla1.line_location_id );
433 else
434 select sum(round((
435 decode(plla1.quantity,
436 null,
437 (plla1.amount - nvl(plla1.amount_cancelled, 0)),
438 (plla1.quantity - nvl(plla1.quantity_cancelled,0))
439 * nvl(plla1.price_override,0)))/x_min_unit)*x_min_unit)
440 INTO X_po_total
441 FROM po_line_locations_archive_all plla1
442 WHERE plla1.po_line_id = x_po_line_id
443 AND shipment_type in ('STANDARD','PLANNED')
444 AND revision_num = (
445 SELECT max( plla2.revision_num )
446 FROM po_line_locations_archive_all plla2
447 WHERE plla2.revision_num <= x_revision_num
448 AND plla2.line_location_id = plla1.line_location_id ) ;
449 end if;
450 else /* po_release_id is not null */
451 if (x_min_unit is null) then
452 select sum(round((
453 decode(plla1.quantity,
454 null,
455 (plla1.amount - nvl(plla1.amount_cancelled, 0)),
456 (plla1.quantity - nvl(plla1.quantity_cancelled,0))
457 * nvl(plla1.price_override,0))),x_precision))
458 INTO X_po_total
459 FROM po_line_locations_archive_all plla1
460 where plla1.po_line_id = x_po_line_id
461 and plla1.po_release_id = x_po_release_id
462 and shipment_type in ('BLANKET','SCHEDULED')
463 and revision_num = (
464 SELECT max(plla2.revision_num)
465 FROM PO_LINE_LOCATIONS_ARCHIVE_ALL plla2
466 WHERE plla2.revision_num <= x_revision_num
467 AND plla2.line_location_id = plla1.line_location_id );
468 else
469 select sum(round((
470 decode(plla1.quantity,
471 null,
472 (plla1.amount - nvl(plla1.amount_cancelled, 0)),
473 (plla1.quantity - nvl(plla1.quantity_cancelled,0))
474 * nvl(plla1.price_override,0)))/x_min_unit)*x_min_unit)
475 INTO X_po_total
476 FROM po_line_locations_archive_all plla1
477 WHERE plla1.po_line_id = x_po_line_id
478 and plla1.po_release_id = x_po_release_id
479 AND shipment_type in ('BLANKET','SCHEDULED')
480 AND revision_num = (
481 SELECT max( plla2.revision_num )
482 FROM po_line_locations_archive_all plla2
483 WHERE plla2.revision_num <= x_revision_num
484 AND plla2.line_location_id = plla1.line_location_id ) ;
485 end if;
486 end if;
487 END IF;/* IF not financing PO */
488 RETURN (X_po_total);
489
490 EXCEPTION
491 WHEN OTHERS then
492 x_po_total := 0;
493 return (X_po_total);
494
495 END get_line_total;
496
497
498
499 FUNCTION get_shipment_total
500 (x_po_line_location_id number,
501 X_revision_num number) return number is
502
503 X_po_total number;
504
505
506 x_min_unit NUMBER;
507 x_precision NUMBER;
508 x_org_id NUMBER;
509
510 BEGIN
511
512 SELECT fc.minimum_accountable_unit,
513 fc.precision
514 INTO x_min_unit,
515 x_precision
516 FROM fnd_currencies fc,
517 po_headers_all pha,
518 po_line_locations_archive_all poll
519 WHERE poll.line_location_id = x_po_line_location_id
520 AND poll.po_header_id = pha.po_header_id
521 AND fc.currency_code = pha.currency_code
522 AND poll.latest_external_flag='Y';
523
524 if (x_min_unit is null) then
525 select round(
526 decode(plla1.quantity,
527 null, (plla1.amount - nvl(plla1.amount_cancelled, 0)),
528 (plla1.quantity - nvl(plla1.quantity_cancelled,0))* nvl(plla1.price_override,0)), x_precision)
529 INTO X_po_total
530 FROM po_line_locations_archive_all plla1
531 WHERE plla1.line_location_id = x_po_line_location_id
532 AND revision_num = (
533 SELECT max(plla2.revision_num)
534 FROM PO_LINE_LOCATIONS_ARCHIVE_ALL plla2
535 WHERE plla2.revision_num <= X_revision_num
536 AND plla2.line_location_id = plla1.line_location_id );
537 else
538 select round(
539 decode(plla1.quantity,
540 null, (plla1.amount - nvl(plla1.amount_cancelled,0)),
541 (plla1.quantity - nvl(plla1.quantity_cancelled,0))* nvl(plla1.price_override,0)) / x_min_unit) * x_min_unit
542 INTO X_po_total
543 FROM po_line_locations_archive_all plla1
544 WHERE plla1.line_location_id = x_po_line_location_id
545 AND revision_num = (
546 SELECT max( plla2.revision_num )
547 FROM po_line_locations_archive_all plla2
548 WHERE plla2.revision_num <= X_revision_num
549 AND plla2.line_location_id = plla1.line_location_id ) ;
550 end if;
551
552 RETURN (X_po_total);
553
554 EXCEPTION
555 WHEN OTHERS then
556 x_po_total := 0;
557 return (X_po_total);
558
559 END get_shipment_total;
560
561
562
563 PROCEDURE get_shipment_amounts (
564 p_po_line_location_id IN NUMBER,
565 p_revision_num IN NUMBER,
566 p_amount_ordered OUT NOCOPY NUMBER,
567 p_amount_received OUT NOCOPY NUMBER,
568 p_amount_billed OUT NOCOPY NUMBER)
569 IS
570
571 x_min_unit NUMBER;
572 x_precision NUMBER;
573
574 BEGIN
575
576 SELECT fc.minimum_accountable_unit,
577 fc.precision
578 INTO x_min_unit,
579 x_precision
580 FROM fnd_currencies fc,
581 po_headers_all pha,
582 po_line_locations_archive_all poll
583 WHERE poll.line_location_id = p_po_line_location_id
584 AND poll.po_header_id = pha.po_header_id
585 AND fc.currency_code = pha.currency_code
586 AND poll.latest_external_flag='Y';
587
588 if (x_min_unit is null) then
589 select round(DECODE(PLLA.matching_basis,
590 'AMOUNT', NVL(PLLA.amount, 0) - NVL(PLLA.amount_cancelled, 0),
591 'QUANTITY', (NVL(PLLA.quantity,0)- NVL(PLLA.quantity_cancelled,0)) *
592 NVL(PLLA.price_override, 0)),
593 x_precision)
594 INTO p_amount_ordered
595 FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLLA
596 WHERE plla.line_location_id = p_po_line_location_id
597 AND revision_num = (
598 SELECT max(plla2.revision_num)
599 FROM PO_LINE_LOCATIONS_ARCHIVE_ALL plla2
600 WHERE plla2.revision_num <= p_revision_num
601 AND plla2.line_location_id = plla.line_location_id );
602
603 SELECT round(DECODE(PLL.matching_basis,
604 'AMOUNT', NVL(PLL.amount_received, 0),
605 'QUANTITY', NVL(PLL.quantity_received, 0)*NVL(PLL.price_override, 0)),
606 x_precision),
607 round(DECODE(PLL.matching_basis,
608 'AMOUNT', NVL(PLL.amount_billed, 0),
609 'QUANTITY', NVL(PLL.quantity_billed, 0)*NVL(PLL.price_override, 0)),
610 x_precision)
611 INTO p_amount_received,
612 p_amount_billed
613 FROM PO_LINE_LOCATIONS_ALL PLL
614 WHERE PLL.line_location_id = p_po_line_location_id;
615
616 else
617 select round((DECODE(PLLA.matching_basis,
618 'AMOUNT', NVL(PLLA.amount, 0) - NVL(PLLA.amount_cancelled, 0),
619 'QUANTITY', (NVL(PLLA.quantity,0)- nvl(PLLA.quantity_cancelled,0))
620 * NVL(PLLA.price_override, 0))
621 / x_min_unit) * x_min_unit)
622 INTO p_amount_ordered
623 FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLLA
624 WHERE plla.line_location_id = p_po_line_location_id
625 AND revision_num = (
626 SELECT max( plla2.revision_num )
627 FROM po_line_locations_archive_all plla2
628 WHERE plla2.revision_num <= p_revision_num
629 AND plla2.line_location_id = plla.line_location_id ) ;
630
631
632 SELECT round((DECODE(PLL.matching_basis,
633 'AMOUNT', NVL(PLL.amount_received, 0),
634 'QUANTITY', NVL(PLL.quantity_received, 0)*NVL(PLL.price_override, 0))
635 / x_min_unit) * x_min_unit),
636 round((DECODE(PLL.matching_basis,
637 'AMOUNT', NVL(PLL.amount_billed, 0),
638 'QUANTITY', NVL(PLL.quantity_billed, 0)*NVL(PLL.price_override, 0))
639 / x_min_unit) * x_min_unit)
640 INTO p_amount_received,
641 p_amount_billed
642 FROM PO_LINE_LOCATIONS_ALL PLL
643 WHERE pll.line_location_id = p_po_line_location_id;
644
645 end if;
646
647 select --sum(quantity_invoiced),
648 nvl(sum(amount), 0)
649 into p_amount_billed
650 from ap_invoice_lines_all
651 where po_line_location_id = p_po_line_location_id;
652
653
654 EXCEPTION
655 WHEN OTHERS then
656 p_amount_ordered := 0;
657 p_amount_received := 0;
658 p_amount_billed := 0;
659
660 END get_shipment_amounts;
661
662
663
664 FUNCTION get_release_total
665 (X_release_id number) return number is
666 X_release_total number;
667
668 x_min_unit NUMBER;
669 x_precision NUMBER;
670 l_revision_num number;
671
672
673 BEGIN
674
675 select revision_num
676 into l_revision_num
677 from po_releases_archive_all
678 where po_release_id = X_release_id
679 and latest_external_flag = 'Y';
680
681 X_release_total := get_release_archive_total
682 (X_release_id,l_revision_num);
683
684 RETURN (X_release_total);
685
686 EXCEPTION
687 WHEN OTHERS then
688 x_release_total := 0;
689 return(x_release_total);
690 END get_release_total;
691
692
693
694 FUNCTION get_po_total_received (
695 p_po_header_id NUMBER,
696 p_po_release_id NUMBER,
697 p_revision_num NUMBER )
698 RETURN NUMBER IS
699
700 x_total_received NUMBER := 0;
701 x_min_unit NUMBER;
702 x_precision NUMBER;
703
704
705 BEGIN
706
707 SELECT fc.minimum_accountable_unit,
708 fc.precision
709 INTO x_min_unit,
710 x_precision
711 FROM fnd_currencies fc,
712 po_headers_archive_all pha
713 WHERE fc.currency_code = pha.currency_code
714 AND pha.po_header_id = p_po_header_id
715 AND pha.latest_external_flag='Y';
716
717
718 if (x_min_unit is null) then
719
720 if (p_po_header_id is not null and p_po_release_id is null) then
721
722 select SUM(round(DECODE(PLL.matching_basis,
723 'AMOUNT', NVL(PLL.amount_received, 0),
724 'QUANTITY', NVL(PLL.quantity_received, 0)*NVL(PLL.price_override, 0)),
725 x_precision))
726 INTO x_total_received
727 FROM po_line_locations_all pll
728 WHERE pll.po_header_id = p_po_header_id
729 AND pll.po_release_id is null;
730
731 elsif (p_po_release_id is not null) then
732
733 select SUM(round(DECODE(PLL.matching_basis,
734 'AMOUNT', NVL(PLL.amount_received, 0),
735 'QUANTITY', NVL(PLL.quantity_received, 0)*NVL(PLL.price_override, 0)),
736 x_precision))
737 INTO x_total_received
738 FROM po_line_locations_all pll
739 WHERE pll.po_release_id = p_po_release_id;
740
741 end if;
742
743 ELSE
744
745 if (p_po_header_id is not null and p_po_release_id is null) then
746 select SUM(round(DECODE(PLL.matching_basis,
747 'AMOUNT', NVL(PLL.amount_received, 0),
748 'QUANTITY', NVL(PLL.quantity_received, 0)*NVL(PLL.price_override, 0))
749 / x_min_unit) * x_min_unit)
750 INTO x_total_received
751 FROM po_line_locations_all pll
752 WHERE pll.po_header_id = p_po_header_id
753 AND pll.po_release_id is null;
754
755 elsif (p_po_release_id is not null) then
756
757 select SUM(round(DECODE(PLL.matching_basis,
758 'AMOUNT', NVL(PLL.amount_received, 0),
759 'QUANTITY', NVL(PLL.quantity_received, 0)*NVL(PLL.price_override, 0))
760 / x_min_unit) * x_min_unit)
761 INTO x_total_received
762 FROM po_line_locations_all pll
763 WHERE pll.po_release_id = p_po_release_id;
764
765 end if;
766 END IF;
767
768 return x_total_received;
769
770 EXCEPTION
771 WHEN OTHERS then
772 x_total_received := -1;
773 return x_total_received;
774
775 END get_po_total_received;
776
777
778
779
780
781 FUNCTION get_po_total_invoiced (
782 p_po_header_id NUMBER,
783 p_po_release_id NUMBER,
784 p_revision_num NUMBER )
785 RETURN NUMBER IS
786
787 x_total_invoiced NUMBER := 0;
788 x_min_unit NUMBER;
789 x_precision NUMBER;
790
791
792 BEGIN
793
794 /*
795 SELECT fc.minimum_accountable_unit,
796 fc.precision
797 INTO x_min_unit,
798 x_precision
799 FROM fnd_currencies fc,
800 po_headers_archive_all pha
801 WHERE fc.currency_code = pha.currency_code
802 AND pha.po_header_id = p_po_header_id
803 AND pha.latest_external_flag='Y';
804
805
806 if (x_min_unit is null) then
807
808 if (p_po_header_id is not null and p_po_release_id is null) then
809 select SUM(round(DECODE(PLL.matching_basis,
810 'AMOUNT', NVL(PLL.amount_billed, 0),
811 'QUANTITY', NVL(PLL.quantity_billed, 0)*NVL(PLL.price_override, 0)),
812 x_precision))
813 INTO x_total_invoiced
814 FROM po_line_locations_all pll
815 WHERE pll.po_header_id = p_po_header_id
816 AND pll.po_release_id is null;
817
818 elsif (p_po_release_id is not null) then
819 select SUM(round(DECODE(PLL.matching_basis,
820 'AMOUNT', NVL(PLL.amount_billed, 0),
821 'QUANTITY', NVL(PLL.quantity_billed, 0)*NVL(PLL.price_override, 0)),
822 x_precision))
823 INTO x_total_invoiced
824 FROM po_line_locations_all pll
825 WHERE pll.po_release_id = p_po_release_id;
826
827 end if;
828
829 ELSE
830
831 if (p_po_header_id is not null and p_po_release_id is null) then
832 select SUM(round(DECODE(PLL.matching_basis,
833 'AMOUNT', NVL(PLL.amount_billed, 0),
834 'QUANTITY', NVL(PLL.quantity_billed, 0)*NVL(PLL.price_override, 0))
835 / x_min_unit) * x_min_unit)
836 INTO x_total_invoiced
837 FROM po_line_locations_all pll
838 WHERE pll.po_header_id = p_po_header_id
839 AND pll.po_release_id is null;
840
841 elsif (p_po_release_id is not null) then
842
843 select SUM(round(DECODE(PLL.matching_basis,
844 'AMOUNT', NVL(PLL.amount_billed, 0),
845 'QUANTITY', NVL(PLL.quantity_billed, 0)*NVL(PLL.price_override, 0))
846 / x_min_unit) * x_min_unit)
847 INTO x_total_invoiced
848 FROM po_line_locations_all pll
849 WHERE pll.po_release_id = p_po_release_id;
850
851 end if;
852 END IF;
853 */
854
855 select --sum(quantity_invoiced),
856 nvl(sum(amount), 0)
857 into x_total_invoiced
858 from ap_invoice_lines_all
859 where (po_header_id = p_po_header_id and po_release_id = p_po_release_id and p_po_release_id is not null)
860 or (po_header_id = p_po_header_id and po_release_id is null and p_po_release_id is null);
861
862
863 return x_total_invoiced;
864
865 EXCEPTION
866 WHEN OTHERS then
867 raise;
868
869 END get_po_total_invoiced;
870
871
872
873
874 FUNCTION get_po_payment_status (p_po_header_id NUMBER,
875 p_po_release_id NUMBER )
876 RETURN VARCHAR2 IS
877
878 l_pay_status_flag VARCHAR2(1) := null;
879 l_inv_paid_flag VARCHAR2(1) := null;
880
881 CURSOR l_po_inv_paid_csr IS
882 select NVL(AI.payment_status_flag, 'N')
883 from AP_INVOICES_ALL AI,
884 AP_INVOICE_DISTRIBUTIONS_ALL AID,
885 PO_DISTRIBUTIONS_ALL POD
886 where AI.invoice_id = AID.invoice_id
887 and AID.po_distribution_id = POD.po_distribution_id
888 and POD.po_header_id = p_po_header_id
889 and POD.po_release_id is null;
890
891 CURSOR l_rel_inv_paid_csr IS
892 select NVL(AI.payment_status_flag, 'N')
893 from AP_INVOICES_ALL AI,
894 AP_INVOICE_DISTRIBUTIONS_ALL AID,
895 PO_DISTRIBUTIONS_ALL POD
896 where AI.invoice_id = AID.invoice_id
897 and AID.po_distribution_id = POD.po_distribution_id
898 and POD.po_header_id = p_po_header_id
899 and POD.po_release_id = p_po_release_id;
900
901 BEGIN
902
903 IF (p_po_release_id is null) THEN
904 OPEN l_po_inv_paid_csr;
905 LOOP
906 FETCH l_po_inv_paid_csr INTO l_inv_paid_flag;
907 EXIT WHEN l_po_inv_paid_csr%NOTFOUND;
908
909 /* If any invoice is partially paid, then payment status is
910 partially paid. */
911 IF (l_inv_paid_flag = 'P') THEN
912 l_pay_status_flag := 'P';
913 EXIT;
914 END IF;
915
916 /* Assign the first rows value to the return flag. */
917 IF (l_pay_status_flag is NULL) THEN
918 l_pay_status_flag := l_inv_paid_flag;
919
920 ELSIF ((l_pay_status_flag = 'N' and l_inv_paid_flag = 'Y') or
921 (l_pay_status_flag = 'Y' and l_inv_paid_flag = 'N')) THEN
922 l_pay_status_flag := 'P';
923 EXIT;
924
925 END IF;
926
927 END LOOP;
928 CLOSE l_po_inv_paid_csr;
929
930 ELSE
931 OPEN l_rel_inv_paid_csr;
932 LOOP
933 FETCH l_rel_inv_paid_csr INTO l_inv_paid_flag;
934 EXIT WHEN l_rel_inv_paid_csr%NOTFOUND;
935
936 /* If any invoice is partially paid, then payment status is
937 partially paid. */
938 IF (l_inv_paid_flag = 'P') THEN
939 l_pay_status_flag := 'P';
940 EXIT;
941 END IF;
942
943 /* Assign the first rows value to the return flag. */
944 IF (l_pay_status_flag is NULL) THEN
945 l_pay_status_flag := l_inv_paid_flag;
946
947 ELSIF ((l_pay_status_flag = 'N' and l_inv_paid_flag = 'Y') or
948 (l_pay_status_flag = 'Y' and l_inv_paid_flag = 'N')) THEN
949 l_pay_status_flag := 'P';
950 EXIT;
951
952 END IF;
953
954 END LOOP;
955 CLOSE l_rel_inv_paid_csr;
956
957 END IF;
958
959 return NVL(l_pay_status_flag, 'N');
960
961 EXCEPTION
962 WHEN OTHERS then
963 return 'F';
964
965 END get_po_payment_status;
966
967
968
969 FUNCTION get_ship_payment_status (p_line_location_id NUMBER)
970 RETURN VARCHAR2 IS
971
972 l_pay_status_flag VARCHAR2(1) := null;
973 l_inv_paid_flag VARCHAR2(1) := 'N';
974
975 /*
976 CURSOR l_inv_paid_csr IS
977 select NVL(AI.payment_status_flag, 'N')
978 from AP_INVOICES_ALL AI,
979 AP_INVOICE_DISTRIBUTIONS_ALL AID,
980 PO_DISTRIBUTIONS_ALL POD
981 where AI.invoice_id = AID.invoice_id
982 and AID.po_distribution_id = POD.po_distribution_id
983 and POD.line_location_id = p_line_location_id;
984 */
985 CURSOR l_inv_paid_csr IS
986 select NVL(AI.payment_status_flag, 'N')
987 from AP_INVOICES_ALL AI,
988 AP_INVOICE_LINES_ALL AIL
989 where AI.invoice_id = AIL.invoice_id
990 and AIL.po_line_location_id = p_line_location_id;
991
992 BEGIN
993
994 OPEN l_inv_paid_csr;
995 LOOP
996 FETCH l_inv_paid_csr INTO l_inv_paid_flag;
997 EXIT WHEN l_inv_paid_csr%NOTFOUND;
998
999 /* If any invoice is partially paid, then payment status is 'P'. */
1000 IF (l_inv_paid_flag = 'P') THEN
1001 l_pay_status_flag := 'P';
1002 EXIT;
1003 END IF;
1004
1005 /* Assign the first rows value to the return flag. */
1006 IF (l_pay_status_flag is NULL) THEN
1007 l_pay_status_flag := l_inv_paid_flag;
1008
1009 ELSIF ((l_pay_status_flag = 'N' and l_inv_paid_flag = 'Y') OR
1010 (l_pay_status_flag = 'Y' and l_inv_paid_flag = 'N')) THEN
1011 l_pay_status_flag := 'P';
1012 EXIT;
1013
1014 END IF;
1015
1016 END LOOP;
1017 CLOSE l_inv_paid_csr;
1018
1019 return l_pay_status_flag;
1020
1021 EXCEPTION
1022 WHEN OTHERS then
1023 return 'F';
1024
1025 END get_ship_payment_status;
1026
1027 END POS_TOTALS_PO_SV;