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