DBA Data[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;