[Home] [Help]
PACKAGE BODY: APPS.POGOT_S
Source
1 PACKAGE BODY POGOT_S AS
2 /* $Header: POGOTB.pls 115.7 2002/11/25 19:51:48 sbull ship $*/
3 /*===========================================================================
4 PROCEDURE NAME: GET_CURRENCY_INFO
5 ===========================================================================*/
6 PROCEDURE GET_CURRENCY_INFO (x_currency_code IN VARCHAR2,
7 x_precision OUT NOCOPY NUMBER,
8 x_min_unit OUT NOCOPY NUMBER ) is
9 x_ext_precision NUMBER;
10 x_progress VARCHAR2(3) := NULL;
11 BEGIN
12 x_progress := 10;
13 fnd_currency.get_info(x_currency_code,
14 x_precision,
15 x_ext_precision,
16 x_min_unit );
17 EXCEPTION
18 WHEN OTHERS THEN
19 po_message_s.sql_error('get_currency_info', x_progress, sqlcode);
20 RAISE;
21 END GET_CURRENCY_INFO;
22 /*===========================================================================
23 PROCEDURE NAME: GET_PO_CURRENCY
24 ===========================================================================*/
25 PROCEDURE GET_PO_CURRENCY (x_object_id IN NUMBER,
26 x_base_currency OUT NOCOPY VARCHAR2,
27 x_po_currency OUT NOCOPY VARCHAR2) is
28 x_progress VARCHAR2(3) := NULL;
29 BEGIN
30 x_progress := 10;
31
32 SELECT GSB.currency_code,
33 POH.currency_code
34 INTO x_base_currency,
35 x_po_currency
36 FROM PO_HEADERS POH,
37 FINANCIALS_SYSTEM_PARAMETERS FSP,
38 GL_SETS_OF_BOOKS GSB
39 WHERE POH.po_header_id = x_object_id
40 AND FSP.set_of_books_id = GSB.set_of_books_id;
41
42 EXCEPTION
43 WHEN OTHERS THEN
44 po_message_s.sql_error('get_po_currency', x_progress, sqlcode);
45 RAISE;
46 END GET_PO_CURRENCY;
47 /*===========================================================================
48 PROCEDURE NAME: GET_REQ_CURRENCY
49 ===========================================================================*/
50 PROCEDURE GET_REQ_CURRENCY (x_object_id IN NUMBER,
51 x_base_currency OUT NOCOPY VARCHAR2 ) is
52 x_progress VARCHAR2(3) := NULL;
53 BEGIN
54 x_progress := 10;
55
56 SELECT GSB.currency_code
57 INTO x_base_currency
58 FROM FINANCIALS_SYSTEM_PARAMETERS FSP,
59 GL_SETS_OF_BOOKS GSB
60 WHERE FSP.set_of_books_id = GSB.set_of_books_id;
61
62 EXCEPTION
63 WHEN OTHERS THEN
64 po_message_s.sql_error('get_req_currency', x_progress, sqlcode);
65 RAISE;
66 END GET_REQ_CURRENCY;
67
68 /*===========================================================================
69 FUNCTION NAME: get_total
70
71 ===========================================================================*/
72 FUNCTION get_total (x_object_type IN VARCHAR2,
73 x_object_id IN NUMBER,
74 x_base_cur_result IN BOOLEAN) RETURN NUMBER IS
75 x_progress VARCHAR2(3) := NULL;
76 x_base_currency VARCHAR2(16);
77 x_po_currency VARCHAR2(16);
78 x_min_unit NUMBER;
79 x_base_min_unit NUMBER;
80 x_precision INTEGER;
81 x_base_precision INTEGER;
82 x_result_fld NUMBER;
83 BEGIN
84
85 if (x_object_type in ('H','B') ) then
86
87 if x_base_cur_result then
88 /* Result should be returned in base currency. Get the currency code
89 of the PO and the base currency code
90 */
91 x_progress := 10;
92 get_po_currency (x_object_id,
93 x_base_currency,
94 x_po_currency );
95
96 /* Chk if base_currency = po_currency */
97 if x_base_currency <> x_po_currency then
98 /* Get precision and minimum accountable unit of the PO CURRENCY */
99 x_progress := 20;
100 get_currency_info (x_po_currency,
101 x_precision,
102 x_min_unit );
103
104 /* Get precision and minimum accountable unit of the base CURRENCY */
105 x_progress := 30;
106 get_currency_info (x_base_currency,
107 x_base_precision,
108 x_base_min_unit );
109
110
111 x_progress := 40;
112 SELECT
113 nvl(sum
114 (decode (x_base_min_unit, NULL,
115 round (decode (x_min_unit, NULL,
116 round ((nvl(POD.quantity_ordered, 0) -
117 nvl(POD.quantity_cancelled, 0)) *
118 nvl(PLL.price_override, 0), x_precision),
119 round ((nvl(POD.quantity_ordered, 0) -
120 nvl(POD.quantity_cancelled, 0)) *
121 nvl(PLL.price_override, 0) / x_min_unit) * x_min_unit)
122 * POD.rate, x_base_precision),
123 round (decode (x_min_unit, NULL,
124 round ((nvl(POD.quantity_ordered, 0) -
125 nvl(POD.quantity_cancelled, 0)) *
126 nvl(PLL.price_override, 0), x_precision),
127 round ((nvl(POD.quantity_ordered, 0) -
128 nvl(POD.quantity_cancelled, 0)) *
129 nvl(PLL.price_override, 0) / x_min_unit) *
130 x_min_unit) * POD.rate / x_base_min_unit) *
131 x_base_min_unit)), 0)
132 INTO x_result_fld
133 FROM PO_DISTRIBUTIONS POD, PO_LINE_LOCATIONS PLL
134 WHERE PLL.po_header_id = x_object_id
135 AND PLL.shipment_type in ('STANDARD','PLANNED', 'BLANKET')
136 AND PLL.line_location_id = POD.line_location_id;
137
138 end if; /* x_base_currency <> x_po_currency */
139
140 else
141
142 /* if we donot want result converted to base currency or if
143 the currencies are the same then do the check without
144 rate conversion */
145 x_progress := 50;
146 SELECT
147 sum( decode (C.minimum_accountable_unit, NULL,
148 round ((PLL.quantity -
149 nvl(PLL.quantity_cancelled, 0)
150 ) * nvl(PLL.price_override, 0), C.precision),
151 round ((PLL.quantity -
152 nvl(PLL.quantity_cancelled, 0)
153 ) * nvl(PLL.price_override, 0) /
154 C.minimum_accountable_unit
155 ) * C.minimum_accountable_unit))
156 INTO x_result_fld
157 FROM PO_LINE_LOCATIONS PLL,
158 PO_HEADERS PH,
159 FND_CURRENCIES C
160 WHERE PLL.po_header_id = x_object_id
161 AND PH.po_header_id = PLL.po_header_id
162 AND PH.currency_code = C.currency_code
163 AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET');
164
165 end if;
166
167 elsif (x_object_type = 'P') then /* For PO Planned */
168
169 if x_base_cur_result then
170
171 /* Result should be returned in base currency. Get the currency code
172 of the PO and the base currency code */
173
174 x_progress := 60;
175 get_po_currency (x_object_id,
176 x_base_currency,
177 x_po_currency );
178
179 /* Chk if base_currency = po_currency */
180 if x_base_currency <> x_po_currency then
181 /* Get precision and minimum accountable unit of the PO CURRENCY */
182 x_progress := 70;
183 get_currency_info (x_po_currency,
184 x_precision,
185 x_min_unit );
186
187 /* Get precision and minimum accountable unit of the base CURRENCY */
188 x_progress := 80;
189 get_currency_info (x_base_currency,
190 x_base_precision,
191 x_base_min_unit );
192
193
194 x_progress := 90;
195 SELECT
196 nvl(sum
197 (decode (x_base_min_unit, NULL,
198 round (decode (x_min_unit, NULL,
199 round ((nvl(POD.quantity_ordered, 0) -
200 nvl(POD.quantity_cancelled, 0)) *
201 nvl(PLL.price_override, 0), x_precision),
202 round ((nvl(POD.quantity_ordered, 0) -
203 nvl(POD.quantity_cancelled, 0)) *
204 nvl(PLL.price_override, 0) / x_min_unit) * x_min_unit)
205 * POD.rate, x_base_precision),
206 round (decode (x_min_unit, NULL,
207 round ((nvl(POD.quantity_ordered, 0) -
208 nvl(POD.quantity_cancelled, 0)) *
209 nvl(PLL.price_override, 0), x_precision),
210 round ((nvl(POD.quantity_ordered, 0) -
211 nvl(POD.quantity_cancelled, 0)) *
212 nvl(PLL.price_override, 0) / x_min_unit) *
213 x_min_unit) * POD.rate / x_base_min_unit) *
214 x_base_min_unit)), 0)
215 INTO x_result_fld
216 FROM PO_DISTRIBUTIONS POD, PO_LINE_LOCATIONS PLL
217 WHERE PLL.po_header_id = x_object_id
218 AND PLL.shipment_type = 'SCHEDULED'
219 AND PLL.line_location_id = POD.line_location_id;
220
221 end if; /* x_base_currency <> x_po_currency */
222
223 else
224
225 /* if we donot want result converted to base currency or if
226 the currencies are the same then do the check without
227 rate conversion */
228 x_progress := 100;
229 SELECT
230 sum( decode (C.minimum_accountable_unit, NULL,
231 round ((PLL.quantity -
232 nvl(PLL.quantity_cancelled, 0)
233 ) * nvl(PLL.price_override, 0), C.precision),
234 round ((PLL.quantity -
235 nvl(PLL.quantity_cancelled, 0)
236 ) * nvl(PLL.price_override, 0) /
237 C.minimum_accountable_unit
238 ) * C.minimum_accountable_unit))
239 INTO x_result_fld
240 FROM PO_LINE_LOCATIONS PLL,
241 PO_HEADERS PH,
242 FND_CURRENCIES C
243 WHERE PLL.po_header_id = x_object_id
244 AND PH.po_header_id = PLL.po_header_id
245 AND PH.currency_code = C.currency_code
246 AND PLL.shipment_type = 'SCHEDULED';
247
248 end if;
249
250 elsif (x_object_type = 'E' ) then /* Requisition Header */
251 x_progress := 110;
252 get_req_currency (x_object_id,
253 x_base_currency );
254
255 x_progress := 120;
256 get_currency_info (x_base_currency,
257 x_base_precision,
258 x_base_min_unit );
259
260 x_progress := 130;
261 SELECT
262 sum( decode
263 ( x_base_min_unit, NULL,
264 round( nvl(quantity, 0) * nvl(unit_price, 0), x_base_precision),
265 round( nvl(quantity, 0) * nvl(unit_price, 0) / x_base_min_unit)
266 * x_base_min_unit))
267 INTO x_result_fld
268 FROM PO_REQUISITION_LINES
269 WHERE requisition_header_id = x_object_id
270 AND nvl(cancel_flag, 'N') = 'N'
271 AND nvl(modified_by_agent_flag, 'N') = 'N';
272
273 elsif (x_object_type = 'I' ) then /* Requisition Line */
274
275 x_progress := 140;
276 get_req_currency (x_object_id,
277 x_base_currency );
278
279 x_progress := 150;
280 get_currency_info (x_base_currency,
281 x_base_precision,
282 x_base_min_unit );
283
284 x_progress := 160;
285 SELECT
286 sum( decode
287 ( x_base_min_unit, NULL,
288 round( nvl(quantity, 0) * nvl(unit_price, 0), x_base_precision),
289 round( nvl(quantity, 0) * nvl(unit_price, 0) / x_base_min_unit)
290 * x_base_min_unit))
291 INTO x_result_fld
292 FROM PO_REQUISITION_LINES
293 WHERE requisition_line_id = x_object_id
294 AND nvl(cancel_flag, 'N') = 'N'
295 AND nvl(modified_by_agent_flag, 'N') = 'N';
296
297 elsif (x_object_type = 'C' ) then /* Contract */
298
299 x_progress := 170;
300 SELECT
301 nvl( sum
302 (decode (C.minimum_accountable_unit, NULL,
303 round ((nvl(PLL.quantity,0) -
304 nvl(PLL.quantity_cancelled, 0) -
305 sum (nvl(PLL2.quantity, 0) -
306 nvl(PLL2.quantity_cancelled,0)))
307 * nvl(PLL.price_override,0), C.precision),
308 round ((nvl(PLL.quantity,0) -
309 nvl(PLL.quantity_cancelled, 0) -
310 sum (nvl(PLL2.quantity, 0) -
311 nvl(PLL2.quantity_cancelled,0)))
312 * nvl(PLL.price_override,0) /
313 C.minimum_accountable_unit) *
314 C.minimum_accountable_unit)), 0)
315 INTO x_result_fld
316 FROM PO_LINE_LOCATIONS PLL,
317 PO_LINE_LOCATIONS PLL2,
318 PO_LINES PL,
319 PO_HEADERS PH,
320 FND_CURRENCIES C
321 WHERE PH.po_header_id = x_object_id
322 AND PH.segment1 = PL.contract_num
323 AND PH.currency_code = C.currency_code
324 AND PL.po_line_id = PLL.po_line_id
325 AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET','SCHEDULED')
326 AND PLL.line_location_id = PLL2.source_shipment_id (+)
327 GROUP BY C.minimum_accountable_unit, C.precision,
328 PLL.quantity, PLL.quantity_cancelled,
329 PLL.price_override, PLL.line_location_id;
330
331 elsif (x_object_type = 'R' ) then /* Release */
332
333 if x_base_cur_result then
334 x_progress := 180;
335 SELECT GSB.currency_code,
336 POH.currency_code
337 INTO x_base_currency,
338 x_po_currency
339 FROM PO_HEADERS POH,
340 FINANCIALS_SYSTEM_PARAMETERS FSP,
341 GL_SETS_OF_BOOKS GSB,
342 PO_RELEASES POR
343 WHERE POH.po_header_id = POR.po_header_id
344 AND POR.po_release_id = x_object_id
345 AND FSP.set_of_books_id = GSB.set_of_books_id;
346
347 if (x_base_currency <> x_po_currency) then
348 /* Get precision and minimum accountable unit of the PO CURRENCY */
349 x_progress := 190;
350 get_currency_info (x_po_currency,
351 x_precision,
352 x_min_unit );
353
354 /* Get precision and minimum accountable unit of the base CURRENCY */
355 x_progress := 200;
356 get_currency_info (x_base_currency,
357 x_base_precision,
358 x_base_min_unit );
359
360
361 x_progress := 210;
362 SELECT
363 nvl(sum (decode (x_base_min_unit, NULL,
364 round ( ( decode (x_min_unit, NULL,
365 round ((nvl(POD.quantity_ordered, 0) *
366 nvl(PLL.price_override, 0)), x_precision),
367 round ((nvl(POD.quantity_ordered, 0) *
368 nvl(PLL.price_override, 0) / x_min_unit))
369 * x_min_unit) * POD.rate), x_base_precision),
370 round ( ( decode (x_min_unit, NULL,
371 round ((nvl(POD.quantity_ordered, 0) *
372 nvl(PLL.price_override, 0)), x_precision),
373 round ((nvl(POD.quantity_ordered, 0) *
374 nvl(PLL.price_override, 0) / x_min_unit)
375 ) * x_min_unit) * POD.rate) / x_base_min_unit
376 ) * x_base_min_unit)), 0)
377 INTO x_result_fld
378 FROM PO_DISTRIBUTIONS POD, PO_LINE_LOCATIONS PLL
379 WHERE PLL.po_release_id = x_object_id
380 AND PLL.line_location_id = POD.line_location_id
381 AND PLL.shipment_type in ('SCHEDULED','BLANKET');
382
383 end if;
384 else
385 x_progress := 220;
386 SELECT
387 sum( decode (C.minimum_accountable_unit, NULL,
388 round ((POL.quantity - nvl(POL.quantity_cancelled, 0))
389 * nvl(POL.price_override, 0), C.precision),
390 round ((POL.quantity - nvl(POL.quantity_cancelled, 0))
391 * nvl(POL.price_override, 0) /
392 C.minimum_accountable_unit) * C.minimum_accountable_unit))
393 INTO x_result_fld
394 FROM PO_LINE_LOCATIONS POL, PO_HEADERS POH,
395 FND_CURRENCIES C
396 WHERE POL.po_release_id = x_object_id
397 AND POH.po_header_id = POL.po_header_id
398 AND POH.currency_code = C.currency_code
399 AND POL.shipment_type in ('SCHEDULED','BLANKET');
400
401 end if;
402
403 elsif (x_object_type = 'L' ) then /* Po Line */
404 x_progress := 230;
405 SELECT
406 sum( decode (C.minimum_accountable_unit, NULL,
407 round ((POL.quantity - nvl(POL.quantity_cancelled, 0))
408 * nvl(POL.price_override, 0), C.precision),
409 round ((POL.quantity - nvl(POL.quantity_cancelled, 0))
410 * nvl(POL.price_override, 0) /
411 C.minimum_accountable_unit)
412 * C.minimum_accountable_unit))
413 INTO x_result_fld
414 FROM PO_LINE_LOCATIONS POL, PO_HEADERS POH,
415 FND_CURRENCIES C
416 WHERE POL.po_line_id = x_object_id
417 AND POH.po_header_id = POL.po_header_id
418 AND POH.currency_code = C.currency_code
419 AND POL.shipment_type in ('STANDARD','PLANNED','BLANKET');
420
421 elsif (x_object_type = 'S' ) then /* PO Shipment */
422 x_progress := 240;
423 SELECT
424 sum( decode (C.minimum_accountable_unit, NULL,
425 round ((POL.quantity - nvl(POL.quantity_cancelled, 0))
426 * nvl(POL.price_override, 0), C.precision),
427 round ((POL.quantity - nvl(POL.quantity_cancelled , 0))
428 * nvl(POL.price_override, 0) /
429 C.minimum_accountable_unit)
430 * C.minimum_accountable_unit))
431 INTO x_result_fld
432 FROM PO_LINE_LOCATIONS POL, PO_HEADERS POH,
433 FND_CURRENCIES C
434 WHERE line_location_id = x_object_id
435 AND POH.po_header_id = POL.po_header_id
436 AND POH.currency_code = C.currency_code;
437
438 end if; /* x_object_type */
439
440 RETURN(x_result_fld);
441
442 EXCEPTION
443 WHEN OTHERS THEN
444 RETURN(0);
445 po_message_s.sql_error('get_total', x_progress, sqlcode);
446 RAISE;
447
448 END get_total;
449 /*===========================================================================
450 PROCEDURE NAME: ECX_GET_PO_CURRENCY
451 ===========================================================================*/
452 PROCEDURE ECX_GET_PO_CURRENCY (x_object_id IN NUMBER,
453 x_base_currency OUT NOCOPY VARCHAR2,
454 x_po_currency OUT NOCOPY VARCHAR2) is
455 x_progress VARCHAR2(3) := NULL;
456 BEGIN
457 x_progress := 10;
458
459 SELECT GSB.currency_code,
460 POH.currency_code
461 INTO x_base_currency,
462 x_po_currency
463 FROM PO_HEADERS_ALL POH,
464 FINANCIALS_SYSTEM_PARAMETERS FSP,
465 GL_SETS_OF_BOOKS GSB
466 WHERE POH.po_header_id = x_object_id
467 AND FSP.set_of_books_id = GSB.set_of_books_id;
468
469 EXCEPTION
470 WHEN OTHERS THEN
471 po_message_s.sql_error('get_po_currency', x_progress, sqlcode);
472 RAISE;
473 END ECX_GET_PO_CURRENCY;
474
475 /*===========================================================================
476 FUNCTION NAME: ecx_get_total
477
478 ===========================================================================*/
479 FUNCTION ecx_get_total (x_object_type IN VARCHAR2,
480 x_object_id IN NUMBER,
481 x_po_currency IN VARCHAR2) RETURN NUMBER IS
482 x_progress VARCHAR2(3) := NULL;
483 x_base_currency VARCHAR2(16);
484 x_po_currency_1 VARCHAR2(16);
485 x_min_unit NUMBER;
486 x_base_min_unit NUMBER;
487 x_precision INTEGER;
488 x_base_precision INTEGER;
489 x_result_fld NUMBER;
490 x_base_cur_result BOOLEAN;
491 BEGIN
492
493 x_base_cur_result:=FALSE;
494
495 if (x_object_type in ('H','B') ) then
496
497 if x_base_cur_result then
498 /* Result should be returned in base currency. Get the currency code
499 of the PO and the base currency code
500 */
501 x_progress := 10;
502 ecx_get_po_currency (x_object_id,
503 x_base_currency,
504 x_po_currency_1 );
505
506 /* Chk if base_currency = po_currency */
507 if x_base_currency <> x_po_currency_1 then
508 /* Get precision and minimum accountable unit of the PO CURRENCY */
509 x_progress := 20;
510 get_currency_info (x_po_currency,
511 x_precision,
512 x_min_unit );
513
514 /* Get precision and minimum accountable unit of the base CURRENCY */
515 x_progress := 30;
516 get_currency_info (x_base_currency,
517 x_base_precision,
518 x_base_min_unit );
519
520
521 x_progress := 40;
522 SELECT
523 nvl(sum
524 (decode (x_base_min_unit, NULL,
525 round (decode (x_min_unit, NULL,
526 round ((nvl(POD.quantity_ordered, 0) -
527 nvl(POD.quantity_cancelled, 0)) *
528 nvl(PLL.price_override, 0), x_precision),
529 round ((nvl(POD.quantity_ordered, 0) -
530 nvl(POD.quantity_cancelled, 0)) *
531 nvl(PLL.price_override, 0) / x_min_unit) * x_min_unit)
532 * POD.rate, x_base_precision),
533 round (decode (x_min_unit, NULL,
534 round ((nvl(POD.quantity_ordered, 0) -
535 nvl(POD.quantity_cancelled, 0)) *
536 nvl(PLL.price_override, 0), x_precision),
537 round ((nvl(POD.quantity_ordered, 0) -
538 nvl(POD.quantity_cancelled, 0)) *
539 nvl(PLL.price_override, 0) / x_min_unit) *
540 x_min_unit) * POD.rate / x_base_min_unit) *
541 x_base_min_unit)), 0)
542 INTO x_result_fld
543 FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
544 WHERE PLL.po_header_id = x_object_id
545 AND PLL.shipment_type in ('STANDARD','PLANNED', 'BLANKET')
546 AND PLL.line_location_id = POD.line_location_id;
547
548 end if; /* x_base_currency <> x_po_currency_1 */
549
550 else
551
552 /* if we donot want result converted to base currency or if
553 the currencies are the same then do the check without
554 rate conversion */
555 x_progress := 50;
556 SELECT
557 sum( decode (C.minimum_accountable_unit, NULL,
558 round ((PLL.quantity -
559 nvl(PLL.quantity_cancelled, 0)
560 ) * nvl(PLL.price_override, 0), C.precision),
561 round ((PLL.quantity -
562 nvl(PLL.quantity_cancelled, 0)
563 ) * nvl(PLL.price_override, 0) /
564 C.minimum_accountable_unit
565 ) * C.minimum_accountable_unit))
566 INTO x_result_fld
567 FROM PO_LINE_LOCATIONS_ALL PLL,
568 PO_HEADERS_ALL PH,
569 FND_CURRENCIES C
570 WHERE PLL.po_header_id = x_object_id
571 AND PH.po_header_id = PLL.po_header_id
572 AND PH.currency_code = C.currency_code
573 AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET');
574
575 end if;
576
577 elsif (x_object_type = 'P') then /* For PO Planned */
578
579 if x_base_cur_result then
580
581 /* Result should be returned in base currency. Get the currency code
582 of the PO and the base currency code */
583
584 x_progress := 60;
585 ecx_get_po_currency (x_object_id,
586 x_base_currency,
587 x_po_currency_1 );
588
589 /* Chk if base_currency = po_currency */
590 if x_base_currency <> x_po_currency_1 then
591 /* Get precision and minimum accountable unit of the PO CURRENCY */
592 x_progress := 70;
593 get_currency_info (x_po_currency_1,
594 x_precision,
595 x_min_unit );
596
597 /* Get precision and minimum accountable unit of the base CURRENCY */
598 x_progress := 80;
599 get_currency_info (x_base_currency,
600 x_base_precision,
601 x_base_min_unit );
602
603
604 x_progress := 90;
605 SELECT
606 nvl(sum
607 (decode (x_base_min_unit, NULL,
608 round (decode (x_min_unit, NULL,
609 round ((nvl(POD.quantity_ordered, 0) -
610 nvl(POD.quantity_cancelled, 0)) *
611 nvl(PLL.price_override, 0), x_precision),
612 round ((nvl(POD.quantity_ordered, 0) -
613 nvl(POD.quantity_cancelled, 0)) *
614 nvl(PLL.price_override, 0) / x_min_unit) * x_min_unit)
615 * POD.rate, x_base_precision),
616 round (decode (x_min_unit, NULL,
617 round ((nvl(POD.quantity_ordered, 0) -
618 nvl(POD.quantity_cancelled, 0)) *
619 nvl(PLL.price_override, 0), x_precision),
620 round ((nvl(POD.quantity_ordered, 0) -
621 nvl(POD.quantity_cancelled, 0)) *
622 nvl(PLL.price_override, 0) / x_min_unit) *
623 x_min_unit) * POD.rate / x_base_min_unit) *
624 x_base_min_unit)), 0)
625 INTO x_result_fld
626 FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
627 WHERE PLL.po_header_id = x_object_id
628 AND PLL.shipment_type = 'SCHEDULED'
629 AND PLL.line_location_id = POD.line_location_id;
630
631 end if; /* x_base_currency <> x_po_currency_1 */
632
633 else
634
635 /* if we donot want result converted to base currency or if
636 the currencies are the same then do the check without
637 rate conversion */
638 x_progress := 100;
639 SELECT
640 sum( decode (C.minimum_accountable_unit, NULL,
641 round ((PLL.quantity -
642 nvl(PLL.quantity_cancelled, 0)
643 ) * nvl(PLL.price_override, 0), C.precision),
644 round ((PLL.quantity -
645 nvl(PLL.quantity_cancelled, 0)
646 ) * nvl(PLL.price_override, 0) /
647 C.minimum_accountable_unit
648 ) * C.minimum_accountable_unit))
649 INTO x_result_fld
650 FROM PO_LINE_LOCATIONS_ALL PLL,
651 PO_HEADERS_ALL PH,
652 FND_CURRENCIES C
653 WHERE PLL.po_header_id = x_object_id
654 AND PH.po_header_id = PLL.po_header_id
655 AND PH.currency_code = C.currency_code
656 AND PLL.shipment_type = 'SCHEDULED';
657
658 end if;
659
660 elsif (x_object_type = 'E' ) then /* Requisition Header */
661 x_progress := 110;
662 get_req_currency (x_object_id,
663 x_base_currency );
664
665 x_progress := 120;
666 get_currency_info (x_base_currency,
667 x_base_precision,
668 x_base_min_unit );
669
670 x_progress := 130;
671 SELECT
672 sum( decode
673 ( x_base_min_unit, NULL,
674 round( nvl(quantity, 0) * nvl(unit_price, 0), x_base_precision),
675 round( nvl(quantity, 0) * nvl(unit_price, 0) / x_base_min_unit)
676 * x_base_min_unit))
677 INTO x_result_fld
678 FROM PO_REQUISITION_LINES_ALL
679 WHERE requisition_header_id = x_object_id
680 AND nvl(cancel_flag, 'N') = 'N'
681 AND nvl(modified_by_agent_flag, 'N') = 'N';
682
683 elsif (x_object_type = 'I' ) then /* Requisition Line */
684
685 x_progress := 140;
686 get_req_currency (x_object_id,
687 x_base_currency );
688
689 x_progress := 150;
690 get_currency_info (x_base_currency,
691 x_base_precision,
692 x_base_min_unit );
693
694 x_progress := 160;
695 SELECT
696 sum( decode
697 ( x_base_min_unit, NULL,
698 round( nvl(quantity, 0) * nvl(unit_price, 0), x_base_precision),
699 round( nvl(quantity, 0) * nvl(unit_price, 0) / x_base_min_unit)
700 * x_base_min_unit))
701 INTO x_result_fld
702 FROM PO_REQUISITION_LINES_ALL
703 WHERE requisition_line_id = x_object_id
704 AND nvl(cancel_flag, 'N') = 'N'
705 AND nvl(modified_by_agent_flag, 'N') = 'N';
706
707 elsif (x_object_type = 'C' ) then /* Contract */
708
709 x_progress := 170;
710 SELECT
711 nvl( sum
712 (decode (C.minimum_accountable_unit, NULL,
713 round ((nvl(PLL.quantity,0) -
714 nvl(PLL.quantity_cancelled, 0) -
715 sum (nvl(PLL2.quantity, 0) -
716 nvl(PLL2.quantity_cancelled,0)))
717 * nvl(PLL.price_override,0), C.precision),
718 round ((nvl(PLL.quantity,0) -
719 nvl(PLL.quantity_cancelled, 0) -
720 sum (nvl(PLL2.quantity, 0) -
721 nvl(PLL2.quantity_cancelled,0)))
722 * nvl(PLL.price_override,0) /
723 C.minimum_accountable_unit) *
724 C.minimum_accountable_unit)), 0)
725 INTO x_result_fld
726 FROM PO_LINE_LOCATIONS_ALL PLL,
727 PO_LINE_LOCATIONS_ALL PLL2,
728 PO_LINES_ALL PL,
729 PO_HEADERS_ALL PH,
730 FND_CURRENCIES C
731 WHERE PH.po_header_id = x_object_id
732 AND PH.segment1 = PL.contract_num
733 AND PH.currency_code = C.currency_code
734 AND PL.po_line_id = PLL.po_line_id
735 AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET','SCHEDULED')
736 AND PLL.line_location_id = PLL2.source_shipment_id (+)
737 GROUP BY C.minimum_accountable_unit, C.precision,
738 PLL.quantity, PLL.quantity_cancelled,
739 PLL.price_override, PLL.line_location_id;
740
741 elsif (x_object_type = 'R' ) then /* Release */
742
743 if x_base_cur_result then
744 x_progress := 180;
745 SELECT GSB.currency_code,
746 POH.currency_code
747 INTO x_base_currency,
748 x_po_currency_1
749 FROM PO_HEADERS_ALL POH,
750 FINANCIALS_SYSTEM_PARAMETERS FSP,
751 GL_SETS_OF_BOOKS GSB,
752 PO_RELEASES_ALL POR
753 WHERE POH.po_header_id = POR.po_header_id
754 AND POR.po_release_id = x_object_id
755 AND FSP.set_of_books_id = GSB.set_of_books_id;
756
757 if (x_base_currency <> x_po_currency_1) then
758 /* Get precision and minimum accountable unit of the PO CURRENCY */
759 x_progress := 190;
760 get_currency_info (x_po_currency_1,
761 x_precision,
762 x_min_unit );
763
764 /* Get precision and minimum accountable unit of the base CURRENCY */
765 x_progress := 200;
766 get_currency_info (x_base_currency,
767 x_base_precision,
768 x_base_min_unit );
769
770
771 x_progress := 210;
772 SELECT
773 nvl(sum (decode (x_base_min_unit, NULL,
774 round ( ( decode (x_min_unit, NULL,
775 round ((nvl(POD.quantity_ordered, 0) *
776 nvl(PLL.price_override, 0)), x_precision),
777 round ((nvl(POD.quantity_ordered, 0) *
778 nvl(PLL.price_override, 0) / x_min_unit))
779 * x_min_unit) * POD.rate), x_base_precision),
780 round ( ( decode (x_min_unit, NULL,
781 round ((nvl(POD.quantity_ordered, 0) *
782 nvl(PLL.price_override, 0)), x_precision),
783 round ((nvl(POD.quantity_ordered, 0) *
784 nvl(PLL.price_override, 0) / x_min_unit)
785 ) * x_min_unit) * POD.rate) / x_base_min_unit
786 ) * x_base_min_unit)), 0)
787 INTO x_result_fld
788 FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
789 WHERE PLL.po_release_id = x_object_id
790 AND PLL.line_location_id = POD.line_location_id
791 AND PLL.shipment_type in ('SCHEDULED','BLANKET');
792
793 end if;
794 else
795 x_progress := 220;
796 SELECT
797 sum( decode (C.minimum_accountable_unit, NULL,
798 round ((POL.quantity - nvl(POL.quantity_cancelled, 0))
799 * nvl(POL.price_override, 0), C.precision),
800 round ((POL.quantity - nvl(POL.quantity_cancelled, 0))
801 * nvl(POL.price_override, 0) /
802 C.minimum_accountable_unit) * C.minimum_accountable_unit))
803 INTO x_result_fld
804 FROM PO_LINE_LOCATIONS_ALL POL, PO_HEADERS_ALL POH,
805 FND_CURRENCIES C
806 WHERE POL.po_release_id = x_object_id
807 AND POH.po_header_id = POL.po_header_id
808 AND POH.currency_code = C.currency_code
809 AND POL.shipment_type in ('SCHEDULED','BLANKET');
810
811 end if;
812
813 elsif (x_object_type = 'L' ) then /* Po Line */
814 x_progress := 230;
815 SELECT
816 sum( decode (C.minimum_accountable_unit, NULL,
817 round ((POL.quantity - nvl(POL.quantity_cancelled, 0))
818 * nvl(POL.price_override, 0), C.precision),
819 round ((POL.quantity - nvl(POL.quantity_cancelled, 0))
820 * nvl(POL.price_override, 0) /
821 C.minimum_accountable_unit)
822 * C.minimum_accountable_unit))
823 INTO x_result_fld
824 FROM PO_LINE_LOCATIONS_ALL POL, PO_HEADERS_ALL POH,
825 FND_CURRENCIES C
826 WHERE POL.po_line_id = x_object_id
827 AND POH.po_header_id = POL.po_header_id
828 AND POH.currency_code = C.currency_code
829 AND POL.shipment_type in ('STANDARD','PLANNED','BLANKET');
830
831 elsif (x_object_type = 'S' ) then /* PO Shipment */
832 x_progress := 240;
833 SELECT
834 sum( decode (C.minimum_accountable_unit, NULL,
835 round ((POL.quantity - nvl(POL.quantity_cancelled, 0))
836 * nvl(POL.price_override, 0), C.precision),
837 round ((POL.quantity - nvl(POL.quantity_cancelled , 0))
838 * nvl(POL.price_override, 0) /
839 C.minimum_accountable_unit)
840 * C.minimum_accountable_unit))
841 INTO x_result_fld
842 FROM PO_LINE_LOCATIONS_ALL POL, PO_HEADERS_ALL POH,
843 FND_CURRENCIES C
844 WHERE line_location_id = x_object_id
845 AND POH.po_header_id = POL.po_header_id
846 AND POH.currency_code = C.currency_code;
847
848 end if; /* x_object_type */
849
850 RETURN(x_result_fld);
851
852 EXCEPTION
853 WHEN OTHERS THEN
854 RETURN(0);
855 po_message_s.sql_error('get_total', x_progress, sqlcode);
856 RAISE;
857
858 END ecx_get_total;
859
860 END POGOT_S;