1 PACKAGE BODY por_change_request_pkg AS
2 /* $Header: PORRCHOB.pls 120.3.12010000.4 2008/11/28 11:53:38 rojain ship $ */
3
4 g_debug CONSTANT VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'), 'N');
5 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_CHANGE_REQUEST_PKG';
6 g_module_prefix CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
7
8 /**************************************************************************
9 * This procedure returns organizational currency's precision and *
10 * extended precision settings. *
11 **************************************************************************/
12 PROCEDURE get_org_precision_values (PRECISION OUT NOCOPY NUMBER,
13 ext_precision OUT NOCOPY NUMBER,
14 min_acct_unit OUT NOCOPY NUMBER)
15 IS
16 functional_cur_code gl_sets_of_books.currency_code%TYPE := '';
17 BEGIN
18 -- get functional currency code
19 SELECT gls.currency_code
20 INTO functional_cur_code
21 FROM
22 financials_system_parameters fsp,
23 gl_sets_of_books gls
24 WHERE
25 fsp.set_of_books_id = gls.set_of_books_id;
26
27 fnd_currency.get_info(functional_cur_code, PRECISION,
28 ext_precision, min_acct_unit);
29
30 END get_org_precision_values;
31
32
33 /**************************************************************************
34 * This function returns conversion rate between Req functional currency *
35 * and PO currency. * **************************************************************************/
36 FUNCTION get_conversion_rate(p_req_ou IN NUMBER,
37 p_po_ou IN NUMBER,
38 p_po_currency_code IN VARCHAR2,
39 p_rate_type IN VARCHAR2,
40 p_rate_date IN DATE) RETURN NUMBER
41 IS
42 l_req_ou_sob_id gl_sets_of_books.set_of_books_id%TYPE;
43 l_rate NUMBER := 1;
44 l_rate_type po_headers_all.rate_type%TYPE;
45 l_inverse_rate_display_flag VARCHAR2(1) := 'N';
46 l_display_rate NUMBER;
47 l_api_name VARCHAR2(30) := 'get_conversion_rate';
48 l_progress VARCHAR2(3) := '000';
49 BEGIN
50
51 IF (g_debug = 'Y' AND
52 fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
53 fnd_log.string(fnd_log.level_statement,
54 g_module_prefix || l_api_name,
55 'Parameters:' || to_char(p_req_ou) || ' ' || to_char(p_po_ou) || ' ' || p_po_currency_code || ' ' || p_rate_type || ' ' || p_rate_date);
56 END IF;
57
58 SELECT req_fsp.set_of_books_id
59 INTO l_req_ou_sob_id
60 FROM financials_system_params_all req_fsp
61 WHERE nvl(req_fsp.org_id, - 99) = nvl(p_req_ou, - 99);
62
63 l_progress := '001';
64
65 IF p_rate_type IS NULL THEN
66 SELECT default_rate_type
67 INTO l_rate_type
68 FROM po_system_parameters_all psp
69 WHERE nvl(psp.org_id, - 99) = nvl(p_po_ou, - 99);
70 ELSE
71 l_rate_type := p_rate_type;
72 END IF;
73
74 l_progress := '002';
75
76 po_currency_sv.get_rate(l_req_ou_sob_id,
77 p_po_currency_code,
78 l_rate_type,
79 p_rate_date,
80 l_inverse_rate_display_flag,
81 l_rate,
82 l_display_rate);
83
84 l_progress := '003';
85
86 IF (g_debug = 'Y' AND
87 fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
88 fnd_log.string(fnd_log.level_statement,
89 g_module_prefix || l_api_name,
90 'Return:' || ' Rate:' || to_char(l_rate));
91 END IF;
92
93 RETURN l_rate;
94 EXCEPTION
95 WHEN OTHERS THEN
96 IF (g_debug = 'Y' AND
97 fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
98 fnd_log.string(fnd_log.level_unexpected,
99 g_module_prefix || l_api_name,
100 'Exception:' || l_progress || ' ' || SQLERRM );
101 END IF;
102 RETURN 1;
103 END get_conversion_rate;
104
105
106 /**************************************************************************
107 * This function calculates price difference between req price and po *
108 * price. * **************************************************************************/
109 FUNCTION calculate_price_diff(p_req_ou NUMBER,
110 p_po_ou NUMBER,
111 p_req_cur_code VARCHAR2,
112 p_req_price NUMBER,
113 p_po_cur_code VARCHAR2,
114 p_po_rate NUMBER,
115 p_po_rate_type VARCHAR2,
116 p_po_rate_date DATE,
117 p_linelocation_price NUMBER,
118 p_po_line_price NUMBER,
119 p_precision NUMBER ) RETURN NUMBER
120 IS
121 l_conversion_rate NUMBER := 1;
122 l_req_cur_code po_requisition_lines.currency_code%TYPE;
123 l_po_cur_code po_headers.currency_code%TYPE;
124 l_req_ou_cur_code gl_sets_of_books.currency_code%TYPE;
125 l_po_ou_cur_code gl_sets_of_books.currency_code%TYPE;
126 l_api_name VARCHAR2(30) := 'calculate_price_diff';
127 l_progress VARCHAR2(3) := '000';
128 BEGIN
129
130 IF (g_debug = 'Y' AND
131 fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
132 fnd_log.string(fnd_log.level_statement,
133 g_module_prefix || l_api_name,
134 'Parameters:' ||
135 to_char(p_req_ou) || ' ' ||
136 to_char(p_po_ou) || ' ' ||
137 p_req_cur_code || ' ' ||
138 to_char(p_req_price) || ' ' ||
139 p_po_cur_code || ' ' ||
140 to_char(p_po_rate) || ' ' ||
141 p_po_rate_type || ' ' ||
142 p_po_rate_date || ' ' ||
143 to_char(p_linelocation_price) || ' ' ||
144 p_po_line_price || ' ' || to_char(p_precision));
145 END IF;
146
147 l_req_cur_code := p_req_cur_code;
148 l_po_cur_code := p_po_cur_code;
149
150 l_progress := '001';
151
152 -- If Req's Org is different then PO's Org
153 IF (p_req_ou <> p_po_ou) THEN
154
155 -- get req's and po's org's functional currency code
156 po_currency_sv.get_functional_currency_code(p_req_ou, l_req_ou_cur_code);
157 po_currency_sv.get_functional_currency_code(p_po_ou, l_po_ou_cur_code);
158
159 l_progress := '002';
160
161 IF (l_req_cur_code IS NULL) THEN
162 -- use functional currency code of REQ's OU
163 l_req_cur_code := l_req_ou_cur_code;
164 END IF;
165
166 IF (l_po_cur_code IS NULL) THEN
167 -- use functional currency of PO's OU
168 l_po_cur_code := l_po_ou_cur_code;
169 END IF;
170
171 l_progress := '003';
172
173 -- conversion needed only if REQ's functional currency is different
174 -- then PO's functional currency
175 IF (l_req_ou_cur_code <> l_po_ou_cur_code) THEN
176
177 l_conversion_rate := get_conversion_rate(p_req_ou,
178 p_po_ou,
179 l_po_cur_code,
180 p_po_rate_type,
181 p_po_rate_date );
182
183 END IF;
184
185 IF (g_debug = 'Y' AND
186 fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
187 fnd_log.string(fnd_log.level_statement,
188 g_module_prefix || l_api_name,
189 'Conversion Rate:' || to_char(l_conversion_rate));
190 END IF;
191
192 l_progress := '004';
193
194 RETURN abs(p_req_price - (l_conversion_rate * nvl(p_linelocation_price, p_po_line_price))) * power(10, p_precision);
195
196 ELSE -- req and po in the same org
197
198 RETURN abs(p_req_price - (nvl(p_po_rate, 1) * nvl(p_linelocation_price, p_po_line_price))) * power(10, p_precision);
199
200 END IF;
201 EXCEPTION
202 WHEN OTHERS THEN
203 IF (g_debug = 'Y' AND
204 fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
205 fnd_log.string(fnd_log.level_unexpected,
206 g_module_prefix || l_api_name,
207 'Exception:' || l_progress || ' ' || SQLERRM );
208 END IF;
209 END calculate_price_diff;
210
211 /**************************************************************************
212 * This function calculates amount difference between req and po for *
213 * amount based lines. *
214 **************************************************************************/
215 FUNCTION calculate_amount_diff(p_req_ou NUMBER,
216 p_po_ou NUMBER,
217 p_req_cur_code VARCHAR2,
218 p_req_amount NUMBER,
219 p_po_cur_code VARCHAR2,
220 p_po_rate NUMBER,
221 p_po_rate_type VARCHAR2,
222 p_po_rate_date DATE,
223 p_linelocation_amount NUMBER,
224 p_po_line_amount NUMBER,
225 p_precision NUMBER ) RETURN NUMBER
226 IS
227 l_conversion_rate NUMBER := 1;
228 l_req_cur_code po_requisition_lines.currency_code%TYPE;
229 l_po_cur_code po_headers.currency_code%TYPE;
230 l_req_ou_cur_code gl_sets_of_books.currency_code%TYPE;
231 l_po_ou_cur_code gl_sets_of_books.currency_code%TYPE;
232 l_api_name VARCHAR2(30) := 'calculate_amount_diff';
233 l_progress VARCHAR2(3) := '000';
234 BEGIN
235
236 IF (g_debug = 'Y' AND
237 fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
238 fnd_log.string(fnd_log.level_statement,
239 g_module_prefix || l_api_name,
240 'Parameters:' ||
241 to_char(p_req_ou) || ' ' ||
242 to_char(p_po_ou) || ' ' ||
243 p_req_cur_code || ' ' ||
244 to_char(p_req_amount) || ' ' ||
245 p_po_cur_code || ' ' ||
246 to_char(p_po_rate) || ' ' ||
247 p_po_rate_type || ' ' ||
248 p_po_rate_date || ' ' ||
249 to_char(p_linelocation_amount) || ' ' ||
250 p_po_line_amount || ' ' || to_char(p_precision));
251 END IF;
252
253 l_req_cur_code := p_req_cur_code;
254 l_po_cur_code := p_po_cur_code;
255
256 l_progress := '001';
257
258 -- If Req's Org is different then PO's Org
259 IF (p_req_ou <> p_po_ou) THEN
260
261 -- get req's and po's org's functional currency code
262 po_currency_sv.get_functional_currency_code(p_req_ou, l_req_ou_cur_code);
263 po_currency_sv.get_functional_currency_code(p_po_ou, l_po_ou_cur_code);
264
265 l_progress := '002';
266
267 IF (l_req_cur_code IS NULL) THEN
268 -- use functional currency code of REQ's OU
269 l_req_cur_code := l_req_ou_cur_code;
270 END IF;
271
272 IF (l_po_cur_code IS NULL) THEN
273 -- use functional currency of PO's OU
274 l_po_cur_code := l_po_ou_cur_code;
275 END IF;
276
277 l_progress := '003';
278
279 -- conversion needed only if REQ's functional currency is different
280 -- then PO's functional currency
281 IF (l_req_ou_cur_code <> l_po_ou_cur_code) THEN
282
283 l_conversion_rate := get_conversion_rate(p_req_ou,
284 p_po_ou,
285 l_po_cur_code,
286 p_po_rate_type,
287 p_po_rate_date );
288
289 END IF;
290
291 IF (g_debug = 'Y' AND
292 fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
293 fnd_log.string(fnd_log.level_statement,
294 g_module_prefix || l_api_name,
295 'Conversion Rate:' || to_char(l_conversion_rate));
296 END IF;
297
298 l_progress := '004';
299
300 RETURN abs(p_req_amount - (l_conversion_rate * nvl(p_linelocation_amount, p_po_line_amount))) * power(10, p_precision);
301
302 ELSE -- req and po in the same org
303
304 RETURN abs(p_req_amount - (nvl(p_po_rate, 1) * nvl(p_linelocation_amount, p_po_line_amount))) * power(10, p_precision);
305
306 END IF;
307 EXCEPTION
308 WHEN OTHERS THEN
309 IF (g_debug = 'Y' AND
310 fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
311 fnd_log.string(fnd_log.level_unexpected,
312 g_module_prefix || l_api_name,
313 'Exception:' || l_progress || ' ' || SQLERRM );
314 END IF;
315 END calculate_amount_diff;
316
317 /**************************************************************************
318 * This function returns whether values for updatable values differ *
319 * between requisition line and corresponding purchase order line or not *
320 * *
321 * The function returns following values depending on the requisition *
322 * type : *
323 * - FIXED_PRICE : if requisition line is a fixed price service line *
324 * type and updatable values are different between req and po *
325 * - LABOR : if requisition line is a temp labor line *
326 * and updatable values are different between req and po *
327 * - Y : if requisition line is otherwise and updatable values *
328 * are different between req and po *
329 * - N : if the updatable values are not different *
330 **************************************************************************/
331 FUNCTION is_order_values_differ(reqlineid NUMBER) RETURN VARCHAR2
332 IS
333 date_diff INTEGER := 0;
334 quantity_diff NUMBER := 0;
335 unit_price_diff NUMBER := 0;
336 amount_diff NUMBER := 0;
337 start_date_diff INTEGER := 0;
338 end_date_diff INTEGER := 0;
339 purchase_basis po_requisition_lines.purchase_basis%TYPE := '';
340 matching_basis po_requisition_lines.matching_basis%TYPE := '';
341 PRECISION NUMBER := 0;
342 ext_precision NUMBER := 0;
343 min_acct_unit NUMBER := 0;
344 po_ou NUMBER;
345 req_ou NUMBER;
346 req_cur_code po_requisition_lines.currency_code%TYPE;
347 req_price NUMBER;
348 po_cur_code po_headers.currency_code%TYPE;
349 po_rate NUMBER;
350 po_rate_type po_headers.rate_type%TYPE;
351 po_rate_date DATE;
352 line_location_price NUMBER;
353 po_line_price NUMBER;
354 req_amount NUMBER;
355 line_location_amount NUMBER;
356 po_line_amount NUMBER;
357 l_api_name VARCHAR2(30) := 'is_order_values_differ';
358 l_progress VARCHAR2(3) := '000';
359 BEGIN
360
361 get_org_precision_values(PRECISION, ext_precision, min_acct_unit);
362
363 l_progress := '001';
364
365 SELECT
366 prl.purchase_basis,
367 prl.matching_basis,
368 trunc(prl.need_by_date) - trunc(pll.need_by_date),
369 (prl.quantity - nvl(prl.quantity_cancelled, 0)) - pll.quantity,
370 prl.org_id,
371 poh.org_id,
372 prl.currency_code,
373 prl.unit_price,
374 poh.currency_code,
375 poh.rate, poh.rate_type, poh.rate_date, pll.price_override,
376 pol.unit_price,
377 prl.amount,
378 pll.amount,
379 pol.amount,
380 trunc(prl.need_by_date) - trunc(pol.start_date),
381 trunc(prl.assignment_end_date) - trunc(pol.expiration_date)
382 INTO
383 purchase_basis,
384 matching_basis,
385 date_diff,
386 quantity_diff,
387 req_ou,
388 po_ou,
389 req_cur_code,
390 req_price,
391 po_cur_code,
392 po_rate,
393 po_rate_type,
394 po_rate_date,
395 line_location_price,
396 po_line_price,
397 req_amount,
398 line_location_amount,
399 po_line_amount,
400 start_date_diff,
401 end_date_diff
402 FROM
403 po_requisition_lines prl,
404 po_req_distributions prd,
405 po_line_locations_all pll,
406 po_headers_all poh,
407 po_lines_all pol,
408 po_distributions_all pod
409 WHERE
410 prl.requisition_line_id = reqlineid AND
411 prd.requisition_line_id = prl.requisition_line_id AND
412 prl.line_location_id = pll.line_location_id AND
413 pll.po_header_id = poh.po_header_id AND
414 pol.po_header_id = poh.po_header_id AND
415 pod.po_line_id = pol.po_line_id AND
416 pod.req_distribution_id = prd.distribution_id AND ROWNUM = 1;
417
418 l_progress := '002';
419
420 /* temp labor line case */
421 IF (purchase_basis = 'TEMP LABOR') THEN
422
423 amount_diff := calculate_amount_diff(req_ou, po_ou, req_cur_code,
424 req_amount, po_cur_code, po_rate, po_rate_type,
425 po_rate_date, line_location_amount, po_line_amount,
426 PRECISION);
427
428 IF (g_debug = 'Y' AND
429 fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
430 fnd_log.string(fnd_log.level_statement,
431 g_module_prefix || l_api_name,
432 'Start_Date_Diff:' || to_char(start_date_diff) || ' ' ||
433 'End_Date_Diff:' || to_char(end_date_diff) || ' ' || 'Amount_Diff:' || to_char(amount_diff));
434 END IF;
435
436 IF (start_date_diff <> 0 OR
437 end_date_diff <> 0 OR
438 amount_diff >= 1) THEN
439 RETURN 'LABOR';
440 END IF;
441
442 RETURN 'N';
443
444 /* fixed price service line case */
445 ELSIF (purchase_basis = 'SERVICES' AND matching_basis = 'AMOUNT') THEN
446
447 amount_diff := calculate_amount_diff(req_ou, po_ou, req_cur_code,
448 req_amount, po_cur_code, po_rate, po_rate_type,
449 po_rate_date, line_location_amount, po_line_amount,
450 PRECISION);
451
452 IF (g_debug = 'Y' AND
453 fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
454 fnd_log.string(fnd_log.level_statement,
455 g_module_prefix || l_api_name,
456 'Date_Diff:' || to_char(date_diff) || ' ' ||
457 'Amount_Diff:' || to_char(amount_diff));
458 END IF;
459
460 IF (date_diff <> 0 OR
461 amount_diff >= 1) THEN
462 RETURN 'FIXED_SERVICE';
463 END IF;
464
465 RETURN 'N';
466
467 ELSE /* other cases */
468
469 unit_price_diff := calculate_price_diff(req_ou, po_ou, req_cur_code,
470 req_price, po_cur_code, po_rate, po_rate_type,
471 po_rate_date, line_location_price, po_line_price,
472 ext_precision);
473
474 IF (g_debug = 'Y' AND
475 fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
476 fnd_log.string(fnd_log.level_statement,
477 g_module_prefix || l_api_name,
478 'Date_Diff:' || to_char(date_diff) || ' ' ||
479 'Quantity_Diff:' || to_char(quantity_diff) || ' ' ||
480 'Unit_Price_Diff:' || to_char(unit_price_diff));
481 END IF;
482
483 IF (date_diff <> 0 OR
484 quantity_diff <> 0 OR
485 unit_price_diff >= 1) THEN
486 RETURN 'Y';
487 END IF;
488
489 RETURN 'N';
490
491 END IF;
492
493 EXCEPTION
494 WHEN OTHERS THEN
495 IF (g_debug = 'Y' AND
496 fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
497 fnd_log.string(fnd_log.level_unexpected,
498 g_module_prefix || l_api_name,
499 'Exception:' || l_progress || ' ' || SQLERRM );
500 END IF;
501 RETURN 'N';
502
503 END is_order_values_differ;
504
505
506 /**************************************************************************
507 * This function calculates the new requisition total during requester *
508 * change order flow *
509 **************************************************************************/
510 FUNCTION get_changed_req_total(reqheaderid IN NUMBER)
511 RETURN NUMBER IS
512 req_total NUMBER := 0;
513 BEGIN
514
515 -- calculate req total by calculating sum of changed line totals
516 SELECT nvl(SUM(get_changed_line_total(requisition_line_id)), 0)
517 INTO req_total
518 FROM
519 po_requisition_lines
520 WHERE
521 requisition_header_id = reqheaderid AND
522 nvl(cancel_flag, 'N') = 'N' AND
523 nvl(modified_by_agent_flag, 'N') = 'N' AND
524 requisition_line_id NOT IN
525 (SELECT DISTINCT document_line_id
526 FROM po_change_requests
527 WHERE document_header_id = reqheaderid
528 AND request_level = 'LINE'
529 AND action_type = 'CANCELLATION');
530
531
532 RETURN req_total;
533
534 EXCEPTION
535 WHEN OTHERS THEN
536 RETURN 0;
537
538 END get_changed_req_total;
539
540
541 /**************************************************************************
542 * This function returns the new non recoverable tax total during *
543 * requester change order flow *
544 **************************************************************************/
545 FUNCTION get_changed_nonrec_tax_total(reqheaderid IN NUMBER) RETURN NUMBER IS
546 tax_total NUMBER := 0;
547 BEGIN
548
549 SELECT nvl(SUM(get_chn_line_nonrec_tax_total(requisition_line_id)), 0)
550 INTO tax_total
551 FROM
552 po_requisition_lines
553 WHERE
554 requisition_header_id = reqheaderid
555 AND requisition_line_id NOT IN
556 (SELECT DISTINCT document_line_id
557 FROM po_change_requests
558 WHERE document_header_id = reqheaderid
559 AND request_level = 'LINE'
560 AND action_type = 'CANCELLATION');
561
562 RETURN tax_total;
563
564 EXCEPTION
565 WHEN OTHERS THEN
566 RETURN 0;
567 END get_changed_nonrec_tax_total;
568
569
570 /**************************************************************************
571 * This function returns unit price of a given line. If there is any *
572 * unit price change exist in po_change_requests table, it returns *
573 * that value otherwise returns unit_price from po_requisition_lines *
574 **************************************************************************/
575 FUNCTION get_unit_price(reqlineid NUMBER, chgreqgrpid IN NUMBER)
576 RETURN NUMBER IS
577 unit_price NUMBER := 0;
578 BEGIN
579
580 unit_price := get_price_break_price(reqlineid, chgreqgrpid);
581
582 IF (unit_price IS NULL) THEN
583
584 SELECT new_price
585 INTO unit_price
586 FROM po_change_requests
587 WHERE
588 document_line_id = reqlineid AND
589 document_type = 'REQ' AND
590 action_type = 'MODIFICATION' AND
591 request_status = 'SYSTEMSAVE' AND
592 new_price IS NOT NULL;
593 END IF;
594
595 RETURN unit_price;
596
597 EXCEPTION
598
599 WHEN no_data_found THEN
600 SELECT unit_price
601 INTO unit_price
602 FROM po_requisition_lines_all
603 WHERE
604 requisition_line_id = reqlineid;
605
606 RETURN unit_price;
607
608 WHEN OTHERS THEN
609 RETURN NULL;
610
611 END get_unit_price;
612
613 /**************************************************************************
614 * This function returns the new line total during requester change order *
615 * flow *
616 **************************************************************************/
617 FUNCTION get_int_changed_line_total(reqlineid IN NUMBER)
618 RETURN NUMBER IS
619 changed_total NUMBER := 0;
620 grp_id NUMBER := 0;
621 amount NUMBER := 0;
622 matching_basis po_requisition_lines.matching_basis%TYPE := '';
623 line_qty NUMBER := 0;
624
625 BEGIN
626
627
628 SELECT MIN(pcr.change_request_group_id)
629 INTO grp_id
630 FROM
631 po_requisition_lines_all prl,
632 po_change_requests pcr
633 WHERE
634 pcr.document_header_id = prl.requisition_header_id
635 AND prl.requisition_line_id = reqlineid
636 AND pcr.request_status = 'SYSTEMSAVE';
637
638
639
640 -- quantity based item
641 BEGIN
642 SELECT new_quantity
643 INTO line_qty
644 FROM
645 po_change_requests
646 WHERE
647 document_line_id = reqlineid AND
648 change_request_group_id = grp_id AND
649 document_type = 'REQ' AND
650 new_quantity IS NOT NULL;
651 EXCEPTION
652 WHEN no_data_found THEN
653 line_qty := get_hist_changed_line_qty(reqlineid, grp_id);
654
655 END;
656
657
658 changed_total := line_qty * get_unit_price(reqlineid, grp_id);
659
660
661
662 IF (changed_total IS NULL) THEN
663 changed_total := 0;
664 END IF;
665
666
667
668 RETURN changed_total;
669
670 EXCEPTION
671 WHEN OTHERS THEN
672 RETURN 0;
673 END get_int_changed_line_total;
674
675 FUNCTION get_changed_line_total(reqlineid IN NUMBER)
676 RETURN NUMBER IS
677 changed_total NUMBER := 0;
678 grp_id NUMBER := 0;
679 amount NUMBER := 0;
680 matching_basis po_requisition_lines.matching_basis%TYPE := '';
681 quantity NUMBER := 0;
682 BEGIN
683
684
685
686
687 SELECT MIN(pcr.change_request_group_id)
688 INTO grp_id
689 FROM
690 po_requisition_lines_all prl,
691 po_change_requests pcr
692 WHERE
693 pcr.document_header_id = prl.requisition_header_id
694 AND prl.requisition_line_id = reqlineid
695 AND pcr.request_status = 'SYSTEMSAVE';
696
697
698
699
700 -- get matching basis
701 SELECT prl.matching_basis
702 INTO matching_basis
703 FROM
704 po_requisition_lines_all prl
705 WHERE
706 prl.requisition_line_id = reqlineid;
707
708
709
710
711 -- quantity based item
712 IF (matching_basis = 'QUANTITY') THEN
713 quantity := get_hist_changed_line_qty(reqlineid, grp_id);
714
715
716
717 IF (quantity IS NULL) THEN
718 quantity := get_hist_line_qty(reqlineid, grp_id);
719
720 END IF;
721 changed_total := quantity * get_unit_price(reqlineid, grp_id);
722
723 IF (changed_total IS NULL) THEN
724 changed_total := 0;
725 END IF;
726
727 -- amount based item
728 ELSE
729
730
731
732
733 changed_total := get_hist_changed_line_amount(reqlineid, grp_id);
734 IF (changed_total IS NULL) THEN
735 changed_total := get_hist_line_amount(reqlineid, grp_id);
736 END IF;
737
738 END IF;
739
740 RETURN changed_total;
741
742 EXCEPTION
743 WHEN OTHERS THEN
744 RETURN 0;
745 END get_changed_line_total;
746
747
748 /**************************************************************************
749 * This function returns the new currency line total during requester *
750 * change order flow *
751 **************************************************************************/
752 FUNCTION get_changed_cur_line_total(reqlineid IN NUMBER)
753 RETURN NUMBER IS
754 changed_total NUMBER := 0;
755 grp_id NUMBER := 0;
756 amount NUMBER := 0;
757 matching_basis po_requisition_lines.matching_basis%TYPE := '';
758 quantity NUMBER := 0;
759 unit_price NUMBER := 0;
760 BEGIN
761
762 SELECT MIN(pcr.change_request_group_id)
763 INTO grp_id
764 FROM
765 po_requisition_lines_all prl,
766 po_change_requests pcr
767 WHERE
768 pcr.document_header_id = prl.requisition_header_id
769 AND prl.requisition_line_id = reqlineid
770 AND pcr.request_status = 'SYSTEMSAVE';
771
772 -- get matching basis
773 SELECT prl.matching_basis
774 INTO matching_basis
775 FROM
776 po_requisition_lines_all prl
777 WHERE
778 prl.requisition_line_id = reqlineid;
779
780 -- quantity based item
781 IF (matching_basis = 'QUANTITY') THEN
782 quantity := get_hist_changed_line_qty(reqlineid, grp_id);
783 IF (quantity IS NULL) THEN
784 quantity := get_hist_line_qty(reqlineid, grp_id);
785 END IF;
786 unit_price := get_currency_unit_price(reqlineid, grp_id);
787
788 IF (unit_price IS NULL) THEN
789 unit_price := get_unit_price(reqlineid, grp_id);
790 END IF;
791
792 changed_total := quantity * unit_price;
793 IF (changed_total IS NULL) THEN
794 changed_total := 0;
795 END IF;
796
797 -- amount based item
798 ELSE
799
800 changed_total := get_hist_chng_cur_line_amount(reqlineid, grp_id);
801 IF (changed_total IS NULL) THEN
802 changed_total := get_hist_cur_line_amount(reqlineid, grp_id);
803 END IF;
804
805 END IF;
806
807 RETURN changed_total;
808
809 EXCEPTION
810 WHEN OTHERS THEN
811 RETURN 0;
812 END get_changed_cur_line_total;
813
814
815 /**************************************************************************
816 * This function returns the non recoverable tax amount of a requisition *
817 * line during requester change order flow *
818 **************************************************************************/
819 FUNCTION get_chn_line_nonrec_tax_total(reqlineid IN NUMBER) RETURN NUMBER IS
820 changed_tax_total NUMBER := 0;
821 new_line_total NUMBER := 0;
822 old_nonrec_tax NUMBER := 0;
823 old_line_total NUMBER := 0;
824 BEGIN
825
826 -- get changed line total
827 new_line_total := get_changed_line_total(reqlineid);
828
829 -- get old line total
830 SELECT SUM(decode(prl.matching_basis, 'AMOUNT', prd.req_line_amount, prd.req_line_quantity * prl.unit_price))
831 INTO old_line_total
832 FROM
833 po_requisition_lines_all prl,
834 po_req_distributions prd
835 WHERE
836 prl.requisition_line_id = reqlineid AND
837 prl.requisition_line_id = prd.requisition_line_id;
838
839 -- get old tax
840 old_nonrec_tax := por_view_reqs_pkg.get_line_nonrec_tax_total(reqlineid);
841
842 changed_tax_total := nvl((old_nonrec_tax * (new_line_total / old_line_total)), 0);
843
844 RETURN changed_tax_total;
845
846 EXCEPTION
847 WHEN OTHERS THEN
848 RETURN 0;
849 END get_chn_line_nonrec_tax_total;
850
851
852 /**************************************************************************
853 * This function returns the recoverable tax amount of a requisition *
854 * line during requester change order flow *
855 **************************************************************************/
856 FUNCTION get_changed_line_rec_tax_total(reqlineid IN NUMBER) RETURN NUMBER IS
857 changed_tax_total NUMBER := 0;
858 new_line_total NUMBER := 0;
859 old_rec_tax NUMBER := 0;
860 old_line_total NUMBER := 0;
861 BEGIN
862 -- get changed line total
863 new_line_total := get_changed_line_total(reqlineid);
864
865 -- get old line total
866 SELECT SUM(decode(prl.matching_basis, 'AMOUNT', prd.req_line_amount, prd.req_line_quantity * prl.unit_price))
867 INTO old_line_total
868 FROM
869 po_requisition_lines_all prl,
870 po_req_distributions_all prd
871 WHERE
872 prl.requisition_line_id = reqlineid AND
873 prl.requisition_line_id = prd.requisition_line_id;
874
875 -- get old tax
876 old_rec_tax := por_view_reqs_pkg.get_line_rec_tax_total(reqlineid);
877
878 changed_tax_total := nvl((old_rec_tax * (new_line_total / old_line_total)), 0);
879
880 RETURN changed_tax_total;
881
882 EXCEPTION
883 WHEN OTHERS THEN
884 RETURN 0;
885 END get_changed_line_rec_tax_total;
886
887
888 /**********************************************************************
889 * This function returns overall status for a given request group id *
890 * and a given requisition line id. The overall status value is *
891 * displayed on Change History Page *
892 * *
893 * The logic : *
894 * - If all requests for the given requisition line is accepted *
895 * return 'ACCEPTED' *
896 * - If any request for the given requisition line is rejected *
897 * return 'REJECTED' *
898 * - else return 'PENDING' *
899 **********************************************************************/
900 FUNCTION get_change_hist_overall_status(requestgroupid IN NUMBER,
901 reqlineid NUMBER)
902 RETURN VARCHAR2 IS
903 overall_status VARCHAR2(30) := 'PENDING';
904 x_value VARCHAR2(30) := '';
905 distinct_values NUMBER := 0;
906
907 CURSOR status_cursor(groupid NUMBER, documentlineid NUMBER) IS
908 SELECT DISTINCT(request_status)
909 FROM
910 po_change_requests pcr
911 WHERE
912 pcr.document_type = 'REQ' AND
913 pcr.document_line_id = documentlineid AND
914 pcr.action_type IN ('MODIFICATION', 'CANCELLATION') AND
915 pcr.change_request_group_id = groupid;
916
917 BEGIN
918
919 OPEN status_cursor(requestgroupid, reqlineid);
920
921 LOOP
922
923 FETCH status_cursor
924 INTO x_value;
925
926 IF (x_value = 'REJECTED') THEN
927 overall_status := 'REJECTED';
928 EXIT; -- exit the loop
929 END IF;
930
931 EXIT WHEN status_cursor%notfound;
932
933 distinct_values := distinct_values + 1;
934
935 END LOOP;
936
937 CLOSE status_cursor;
938
939 IF (x_value = 'ACCEPTED' AND distinct_values < 2) THEN
940 overall_status := 'ACCEPTED';
941 END IF;
942
943 RETURN overall_status;
944
945 EXCEPTION
946 WHEN OTHERS THEN
947 RETURN 0;
948 END get_change_hist_overall_status;
949
950
951 /**********************************************************************
952 * This function returns request status for a given request group id *
953 * and a given requisition line id for rco notifications. *
954 * *
955 * The logic : *
956 * - If all requests for the given requisition line is accepted *
957 * return 'ACCEPTED' *
958 * - If all requests for the given requisition line is rejected *
959 * return 'REJECTED' *
960 * - If some requests for the given requisition line are rejected *
961 * and some are accepted *
962 * return 'PARTIALLY' *
963 * - else return 'PENDING' *
964 **********************************************************************/
965 FUNCTION get_chng_hist_req_status_notfn(requestgroupid IN NUMBER,
966 reqlineid NUMBER)
967 RETURN VARCHAR2 IS
968 overall_status VARCHAR2(30) := 'PENDING';
969 x_value VARCHAR2(30) := '';
970
971 CURSOR status_cursor(groupid NUMBER, documentlineid NUMBER) IS
972 SELECT DISTINCT(request_status)
973 FROM
974 po_change_requests pcr
975 WHERE
976 pcr.document_type = 'REQ' AND
977 pcr.document_line_id = documentlineid AND
978 pcr.action_type IN ('MODIFICATION', 'CANCELLATION') AND
979 pcr.change_request_group_id = groupid;
980 BEGIN
981 OPEN status_cursor(requestgroupid, reqlineid);
982
983 LOOP
984 FETCH status_cursor INTO x_value;
985 EXIT WHEN status_cursor%notfound;
986 IF (x_value IN ('REJECTED', 'ACCEPTED') AND overall_status <> 'PENDING') THEN
987 overall_status := 'PARTIALLY';
988 EXIT; --exit the loop
989 ELSIF (x_value = 'REJECTED') THEN
990 overall_status := 'REJECTED';
991 ELSIF (x_value = 'ACCEPTED') THEN
992 overall_status := 'ACCEPTED';
993 END IF;
994 END LOOP;
995 CLOSE status_cursor;
996
997 RETURN overall_status;
998 EXCEPTION
999 WHEN OTHERS THEN
1000 RETURN 'PENDING';
1001 END get_chng_hist_req_status_notfn;
1002
1003 /**************************************************************************
1004 * This function returns multiple_value if there are multiple *
1005 * distributions or 'SINGLE_VALUE' if there are multiple distributions *
1006 * fo the given requisition line id *
1007 **************************************************************************/
1008 FUNCTION get_multiple_distributions(req_line_id NUMBER) RETURN VARCHAR2 IS
1009 no_of_values NUMBER := 0;
1010 BEGIN
1011 SELECT COUNT(distribution_id)
1012 INTO no_of_values
1013 FROM po_req_distributions_all
1014 WHERE requisition_line_id = req_line_id;
1015
1016 IF (no_of_values > 1) THEN
1017 RETURN 'MULTIPLE_VALUE';
1018 ELSE
1019 RETURN 'SINGLE_VALUE';
1020 END IF;
1021
1022 END get_multiple_distributions;
1023
1024
1025 FUNCTION get_changed_line_quantity(reqlineid IN NUMBER)
1026 RETURN NUMBER IS
1027 changed_quantity NUMBER := 0;
1028 grp_id NUMBER := 0;
1029 BEGIN
1030
1031 SELECT MIN(pcr.change_request_group_id)
1032 INTO grp_id
1033 FROM
1034 po_requisition_lines_all prl,
1035 po_change_requests pcr
1036 WHERE
1037 pcr.document_header_id = prl.requisition_header_id
1038 AND prl.requisition_line_id = reqlineid
1039 AND pcr.request_status = 'SYSTEMSAVE';
1040
1041 SELECT nvl(po_rcotolerance_pvt.get_new_line_quantity(prl.requisition_header_id, prl.requisition_line_id, grp_id), 0)
1042 INTO changed_quantity
1043 FROM po_requisition_lines_all prl
1044 WHERE prl.requisition_line_id = reqlineid ;
1045
1046 RETURN changed_quantity;
1047
1048 EXCEPTION
1049 WHEN OTHERS THEN
1050 RETURN 0;
1051 END get_changed_line_quantity;
1052
1053 /**************************************************************************
1054 * This function returns the updated line total for a given requisition *
1055 * line id and change request group id. *
1056 **************************************************************************/
1057 FUNCTION get_hist_changed_line_total(reqlineid IN NUMBER, chgreqgrpid IN NUMBER)
1058 RETURN NUMBER IS
1059 matching_basis po_requisition_lines.matching_basis%TYPE := '';
1060 BEGIN
1061
1062 SELECT matching_basis
1063 INTO matching_basis
1064 FROM po_requisition_lines_all
1065 WHERE requisition_line_id = reqlineid;
1066
1067 IF (matching_basis = 'QUANTITY') THEN
1068 RETURN (nvl(nvl(get_price_break_price(reqlineid, chgreqgrpid), get_hist_changed_line_price(reqlineid, chgreqgrpid)), nvl(get_hist_line_price(reqlineid, chgreqgrpid), 0)) *
1069 nvl(get_hist_changed_line_qty(reqlineid, chgreqgrpid), get_hist_line_qty(reqlineid, chgreqgrpid)));
1070 ELSE
1071 RETURN nvl(get_hist_changed_line_amount(reqlineid, chgreqgrpid), get_hist_line_amount(reqlineid, chgreqgrpid));
1072 END IF;
1073
1074 EXCEPTION WHEN OTHERS THEN
1075 RETURN 0;
1076
1077 END get_hist_changed_line_total;
1078
1079 /**************************************************************************
1080 * This function returns the updated line price for history page *
1081 * for a given requisition line id and change request group id *
1082 * if there is no price change, then it returns null *
1083 **************************************************************************/
1084 FUNCTION get_hist_changed_line_price(reqlineid IN NUMBER, chgreqgrpid IN NUMBER)
1085 RETURN NUMBER IS
1086 new_price NUMBER;
1087 BEGIN
1088
1089 SELECT new_price
1090 INTO new_price
1091 FROM po_change_requests
1092 WHERE
1093 change_request_group_id = chgreqgrpid AND
1094 document_line_id = reqlineid AND
1095 document_type = 'REQ' AND
1096 request_level = 'LINE' AND
1097 new_price IS NOT NULL;
1098
1099 RETURN new_price;
1100
1101 EXCEPTION WHEN OTHERS THEN
1102 RETURN NULL;
1103
1104 END get_hist_changed_line_price;
1105
1106
1107 /**************************************************************************
1108 * This function returns the old/unchanged line total during
1109 * a change request in Requester Change Order
1110 * it calculates the old line total for a particular change request group id
1111 **************************************************************************/
1112 FUNCTION get_hist_line_total(reqlineid IN NUMBER, chgreqgrpid IN NUMBER)
1113 RETURN NUMBER IS
1114 matching_basis po_requisition_lines.matching_basis%TYPE := '';
1115 BEGIN
1116
1117 SELECT matching_basis
1118 INTO matching_basis
1119 FROM po_requisition_lines_all
1120 WHERE requisition_line_id = reqlineid;
1121
1122 IF (matching_basis = 'QUANTITY') THEN
1123 RETURN nvl(get_hist_line_price(reqlineid, chgreqgrpid), 0) * get_hist_line_qty(reqlineid, chgreqgrpid);
1124 ELSE
1125 RETURN get_hist_line_amount(reqlineid, chgreqgrpid);
1126 END IF;
1127
1128 EXCEPTION WHEN OTHERS THEN
1129 RETURN 0;
1130
1131 END get_hist_line_total;
1132
1133
1134 /***************************************************************************
1135 * This function returns the original line price before the change request *
1136 * for a given requisition line id and change request group id *
1137 ***************************************************************************/
1138 FUNCTION get_hist_line_price(reqlineid IN NUMBER, chgreqgrpid IN NUMBER)
1139 RETURN NUMBER IS
1140 line_price NUMBER;
1141 BEGIN
1142
1143 SELECT DISTINCT(old_price)
1144 INTO line_price
1145 FROM
1146 po_change_requests
1147 WHERE
1148 document_line_id = reqlineid AND
1149 change_request_group_id = chgreqgrpid AND
1150 document_type = 'REQ' AND
1151 old_price IS NOT NULL;
1152
1153 RETURN line_price;
1154
1155 EXCEPTION
1156
1157 WHEN OTHERS THEN
1158 RETURN NULL;
1159
1160 END get_hist_line_price;
1161
1162 /***************************************************************************
1163 * This function returns the original currency line price before the change*
1164 * request for a given requisition line id and change request group id *
1165 ***************************************************************************/
1166 FUNCTION get_hist_cur_line_price(reqlineid IN NUMBER, chgreqgrpid IN NUMBER)
1167 RETURN NUMBER IS
1168 cur_line_price NUMBER;
1169 l_req_rate NUMBER := 1;
1170
1171 BEGIN
1172
1173 SELECT DISTINCT(old_currency_unit_price)
1174 INTO cur_line_price
1175 FROM
1176 po_change_requests
1177 WHERE
1178 document_line_id = reqlineid AND
1179 change_request_group_id = chgreqgrpid AND
1180 document_type = 'REQ' AND
1181 old_currency_unit_price IS NOT NULL;
1182
1183 RETURN cur_line_price;
1184
1185 EXCEPTION
1186 WHEN OTHERS THEN
1187
1188 SELECT rate INTO l_req_rate
1189 FROM po_requisition_lines_all
1190 WHERE requisition_line_id = reqlineid;
1191
1192 IF (l_req_rate IS NOT NULL ) THEN
1193
1194 RETURN get_hist_line_price(reqlineid, chgreqgrpid) / l_req_rate;
1195 ELSE
1196
1197 RETURN get_hist_line_price(reqlineid, chgreqgrpid);
1198
1199 END IF;
1200
1201 END get_hist_cur_line_price;
1202
1203 /***************************************************************************
1204 * This function returns the changed currency line price *
1205 * for a given requisition line id and change request group id *
1206 ***************************************************************************/
1207 FUNCTION get_hist_chng_cur_line_price(reqlineid IN NUMBER, chgreqgrpid IN NUMBER)
1208 RETURN NUMBER IS
1209 cur_line_price NUMBER := 0;
1210 l_req_rate NUMBER := 1;
1211
1212 BEGIN
1213
1214 SELECT DISTINCT(new_currency_unit_price)
1215 INTO cur_line_price
1216 FROM
1217 po_change_requests
1218 WHERE
1219 document_line_id = reqlineid AND
1220 change_request_group_id = chgreqgrpid AND
1221 document_type = 'REQ' AND
1222 new_currency_unit_price IS NOT NULL;
1223
1224 RETURN cur_line_price;
1225
1226 EXCEPTION
1227
1228 WHEN OTHERS THEN
1229 cur_line_price := get_hist_changed_line_price(reqlineid, chgreqgrpid);
1230 IF (cur_line_price IS NULL) THEN
1231 cur_line_price := nvl(get_hist_line_price(reqlineid, chgreqgrpid), 0);
1232 END IF;
1233
1234 -- if req is created in txn currency, need to convert with rate
1235 SELECT rate INTO l_req_rate
1236 FROM po_requisition_lines_all
1237 WHERE requisition_line_id = reqlineid;
1238
1239 IF (l_req_rate IS NOT NULL) THEN
1240 RETURN cur_line_price / l_req_rate;
1241
1242 ELSE
1243 RETURN cur_line_price;
1244
1245 END IF;
1246
1247 END get_hist_chng_cur_line_price;
1248
1249
1250 /**************************************************************************
1251 * This function returns the old/unchanged line quantity *
1252 * for a specific requisition line and change request group id *
1253 **************************************************************************/
1254 FUNCTION get_hist_line_qty(reqlineid IN NUMBER, chgreqgrpid IN NUMBER)
1255 RETURN NUMBER IS
1256 line_qty NUMBER := 0;
1257 BEGIN
1258
1259 SELECT old_quantity
1260 INTO line_qty
1261 FROM
1262 po_change_requests
1263 WHERE
1264 document_line_id = reqlineid AND
1265 change_request_group_id = chgreqgrpid AND
1266 document_type = 'REQ' AND
1267 action_type = 'DERIVED' AND
1268 old_quantity IS NOT NULL;
1269
1270 RETURN line_qty;
1271
1272 EXCEPTION
1273 WHEN no_data_found THEN
1274 SELECT quantity
1275 INTO line_qty
1276 FROM po_requisition_lines_all
1277 WHERE requisition_line_id = reqlineid;
1278
1279 RETURN line_qty;
1280
1281 WHEN OTHERS THEN
1282 RETURN 0;
1283
1284 END get_hist_line_qty;
1285
1286
1287 /**************************************************************************
1288 * This function returns the newly updated line quantity *
1289 * for a specific change request group *
1290 **************************************************************************/
1291 FUNCTION get_hist_changed_line_qty(reqlineid IN NUMBER, chgreqgrpid IN NUMBER)
1292 RETURN NUMBER IS
1293 line_qty NUMBER;
1294 BEGIN
1295
1296 SELECT new_quantity
1297 INTO line_qty
1298 FROM
1299 po_change_requests
1300 WHERE
1301 document_line_id = reqlineid AND
1302 change_request_group_id = chgreqgrpid AND
1303 document_type = 'REQ' AND
1304 ( action_type = 'DERIVED' OR REQUEST_LEVEL = 'LINE') AND
1305 new_quantity IS NOT NULL;
1306
1307 RETURN line_qty;
1308
1309 EXCEPTION WHEN OTHERS THEN
1310 RETURN NULL;
1311
1312 END get_hist_changed_line_qty;
1313
1314
1315 /**************************************************************************
1316 * This function returns the price break price from po_change_requests *
1317 * for a given requisition line id and change request group id *
1318 * if there is no price break price, it returns NULL *
1319 **************************************************************************/
1320 FUNCTION get_price_break_price(reqlineid IN NUMBER, chgreqgrpid IN NUMBER)
1321 RETURN NUMBER IS
1322 new_price NUMBER;
1323 BEGIN
1324
1325 SELECT new_price
1326 INTO new_price
1327 FROM
1328 po_change_requests
1329 WHERE
1330 document_line_id = reqlineid AND
1331 change_request_group_id = chgreqgrpid AND
1332 document_type = 'REQ' AND
1333 action_type = 'DERIVED' AND
1334 new_price IS NOT NULL;
1335
1336 RETURN new_price;
1337
1338 EXCEPTION WHEN OTHERS THEN
1339 RETURN NULL;
1340
1341 END get_price_break_price;
1342
1343 /**************************************************************************
1344 * This function returns the price break price from po_change_requests *
1345 * for a given requisition line id and change request group id *
1346 * if there is no price break price, it returns NULL *
1347 **************************************************************************/
1348 FUNCTION get_price_break_cur_price(reqlineid IN NUMBER, chgreqgrpid IN NUMBER)
1349 RETURN NUMBER IS
1350 new_cur_price NUMBER;
1351 BEGIN
1352
1353 SELECT new_currency_unit_price
1354 INTO new_cur_price
1355 FROM
1356 po_change_requests
1357 WHERE
1358 document_line_id = reqlineid AND
1359 change_request_group_id = chgreqgrpid AND
1360 document_type = 'REQ' AND
1361 action_type = 'DERIVED' AND
1362 new_currency_unit_price IS NOT NULL;
1363
1364 RETURN new_cur_price;
1365
1366 EXCEPTION WHEN OTHERS THEN
1367 RETURN NULL;
1368
1369 END get_price_break_cur_price;
1370
1371 /**************************************************************************
1372 * This function returns the price break currency unit price from *
1373 * po_change_requests *
1374 * for a given requisition line id and change request group id *
1375 * if there is no price break price, it returns NULL *
1376 **************************************************************************/
1377 FUNCTION get_price_break_trx_price(reqlineid IN NUMBER, chgreqgrpid IN NUMBER)
1378 RETURN NUMBER IS
1379 new_price NUMBER;
1380 BEGIN
1381
1382 SELECT nvl(new_currency_unit_price, new_price)
1383 INTO new_price
1384 FROM
1385 po_change_requests
1386 WHERE
1387 document_line_id = reqlineid AND
1388 change_request_group_id = chgreqgrpid AND
1389 document_type = 'REQ' AND
1390 action_type = 'DERIVED' AND
1391 new_price IS NOT NULL;
1392
1393 RETURN new_price;
1394
1395 EXCEPTION WHEN OTHERS THEN
1396 RETURN NULL;
1397
1398 END get_price_break_trx_price;
1399
1400
1401 /**************************************************************************
1402 * This function is called from ChangeOrderReviewDistributionsVO *
1403 * returns changed line quantity for the current change request *
1404 **************************************************************************/
1405 FUNCTION get_dist_changed_line_qty(reqheaderid NUMBER, reqlineid NUMBER)
1406 RETURN NUMBER IS
1407 chggroupid NUMBER := 0;
1408 BEGIN
1409 SELECT MAX(change_request_group_id)
1410 INTO chggroupid
1411 FROM po_change_requests
1412 WHERE document_header_id = reqheaderid AND
1413 document_type = 'REQ';
1414
1415 RETURN get_hist_changed_line_qty(reqlineid, chggroupid);
1416
1417 END get_dist_changed_line_qty;
1418
1419 /**************************************************************************
1420 * This function is called from ChangeOrderReviewDistributionsVO *
1421 * returns changed line quantity for the current change request *
1422 **************************************************************************/
1423 FUNCTION get_dist_changed_line_amt(reqheaderid NUMBER, reqlineid NUMBER)
1424 RETURN NUMBER IS
1425 chggroupid NUMBER := 0;
1426 BEGIN
1427 SELECT MAX(change_request_group_id)
1428 INTO chggroupid
1429 FROM po_change_requests
1430 WHERE document_header_id = reqheaderid AND
1431 document_type = 'REQ';
1432
1433 RETURN get_hist_changed_line_amount(reqlineid, chggroupid);
1434
1435 END get_dist_changed_line_amt;
1436
1437
1438 /**************************************************************************
1439 * This function returns changed line total for amount based lines *
1440 **************************************************************************/
1441 FUNCTION get_hist_changed_line_amount(reqlineid IN NUMBER, chgreqgrpid IN NUMBER)
1442 RETURN NUMBER IS
1443 amount NUMBER := 0;
1444 BEGIN
1445 SELECT new_amount
1446 INTO amount
1447 FROM po_change_requests
1448 WHERE
1449 document_line_id = reqlineid AND
1450 change_request_group_id = chgreqgrpid AND
1451 document_type = 'REQ' AND
1452 action_type = 'DERIVED' AND
1453 new_amount IS NOT NULL;
1454
1455 RETURN amount;
1456
1457 EXCEPTION
1458 WHEN OTHERS THEN
1459 RETURN NULL;
1460
1461 END get_hist_changed_line_amount;
1462
1463
1464 /**************************************************************************
1465 * This function returns the old/unchanged line amount for a given *
1466 * requisition line and change request group id *
1467 **************************************************************************/
1468 FUNCTION get_hist_line_amount(reqlineid IN NUMBER, chgreqgrpid IN NUMBER)
1469 RETURN NUMBER IS
1470 amount NUMBER := 0;
1471 BEGIN
1472 SELECT old_amount
1473 INTO amount
1474 FROM po_change_requests
1475 WHERE
1476 document_line_id = reqlineid AND
1477 change_request_group_id = chgreqgrpid AND
1478 document_type = 'REQ' AND
1479 action_type = 'DERIVED' AND
1480 old_amount IS NOT NULL;
1481
1482 RETURN amount;
1483
1484 EXCEPTION
1485 WHEN no_data_found THEN
1486 SELECT amount
1487 INTO amount
1488 FROM po_requisition_lines_all
1489 WHERE requisition_line_id = reqlineid;
1490
1491 RETURN amount;
1492
1493 WHEN OTHERS THEN
1494 RETURN 0;
1495
1496 END get_hist_line_amount;
1497
1498 /**************************************************************************
1499 * This function returns the old/unchanged currency line total during
1500 * a change request in Requester Change Order
1501 * it calculates the old line total for a particular change request group id
1502 **************************************************************************/
1503 FUNCTION get_hist_cur_line_total(reqlineid IN NUMBER, chgreqgrpid IN NUMBER)
1504 RETURN NUMBER IS
1505 matching_basis po_requisition_lines.matching_basis%TYPE := '';
1506 BEGIN
1507
1508 SELECT matching_basis
1509 INTO matching_basis
1510 FROM po_requisition_lines_all
1511 WHERE requisition_line_id = reqlineid;
1512
1513 IF (matching_basis = 'QUANTITY') THEN
1514 RETURN nvl(get_hist_cur_line_price(reqlineid, chgreqgrpid), 0) * get_hist_line_qty(reqlineid, chgreqgrpid);
1515 ELSE
1516 RETURN get_hist_cur_line_amount(reqlineid, chgreqgrpid);
1517 END IF;
1518
1519 EXCEPTION WHEN OTHERS THEN
1520 RETURN 0;
1521
1522 END get_hist_cur_line_total;
1523
1524 /**************************************************************************
1525 * This function returns line total for amount based lines
1526 **************************************************************************/
1527 FUNCTION get_hist_cur_line_amount(reqlineid IN NUMBER, chgreqgrpid IN NUMBER)
1528 RETURN NUMBER IS
1529 amount NUMBER := 0;
1530 BEGIN
1531 SELECT old_currency_amount
1532 INTO amount
1533 FROM po_change_requests
1534 WHERE
1535 document_line_id = reqlineid AND
1536 change_request_group_id = chgreqgrpid AND
1537 document_type = 'REQ' AND
1538 action_type = 'DERIVED' AND
1539 old_currency_amount IS NOT NULL;
1540
1541 RETURN amount;
1542
1543 EXCEPTION
1544 WHEN no_data_found THEN
1545 SELECT nvl(currency_amount, amount)
1546 INTO amount
1547 FROM po_requisition_lines_all
1548 WHERE requisition_line_id = reqlineid;
1549
1550 RETURN amount;
1551
1552 WHEN OTHERS THEN
1553 RETURN 0;
1554
1555 END get_hist_cur_line_amount;
1556
1557 /**************************************************************************
1558 * This function returns currency unit price of a given line. *
1559 * If there is any currency unit price change exist in po_change_requests *
1560 * table, it returns that value otherwise returns currency_unit_price *
1561 * from po_requisition_lines *
1562 **************************************************************************/
1563 FUNCTION get_currency_unit_price(reqlineid NUMBER, chgreqgrpid IN NUMBER)
1564 RETURN NUMBER IS
1565 cur_unit_price NUMBER := 0;
1566 BEGIN
1567
1568 cur_unit_price := get_price_break_cur_price(reqlineid, chgreqgrpid);
1569
1570 IF (cur_unit_price IS NULL) THEN
1571
1572 SELECT new_currency_unit_price
1573 INTO cur_unit_price
1574 FROM po_change_requests
1575 WHERE
1576 document_line_id = reqlineid AND
1577 document_type = 'REQ' AND
1578 action_type = 'MODIFICATION' AND
1579 request_status = 'SYSTEMSAVE' AND
1580 new_currency_unit_price IS NOT NULL;
1581 END IF;
1582
1583 RETURN cur_unit_price;
1584
1585 EXCEPTION
1586
1587 WHEN no_data_found THEN
1588 SELECT currency_unit_price
1589 INTO cur_unit_price
1590 FROM po_requisition_lines_all
1591 WHERE
1592 requisition_line_id = reqlineid;
1593
1594 RETURN cur_unit_price;
1595
1596 WHEN OTHERS THEN
1597 RETURN NULL;
1598
1599 END get_currency_unit_price;
1600
1601 /**************************************************************************
1602 * This function returns the changed currency line total during
1603 * a change request in Requester Change Order
1604 * it calculates the old line total for a particular change request group id
1605 **************************************************************************/
1606 FUNCTION get_hist_chng_cur_line_total(reqlineid IN NUMBER, chgreqgrpid IN NUMBER)
1607 RETURN NUMBER IS
1608 matching_basis po_requisition_lines.matching_basis%TYPE := '';
1609 BEGIN
1610
1611 SELECT matching_basis
1612 INTO matching_basis
1613 FROM po_requisition_lines_all
1614 WHERE requisition_line_id = reqlineid;
1615
1616 IF (matching_basis = 'QUANTITY') THEN
1617 RETURN get_hist_chng_cur_line_price(reqlineid, chgreqgrpid) * get_hist_changed_line_qty(reqlineid, chgreqgrpid);
1618 ELSE
1619 RETURN get_hist_chng_cur_line_amount(reqlineid, chgreqgrpid);
1620 END IF;
1621
1622 EXCEPTION WHEN OTHERS THEN
1623 RETURN 0;
1624
1625 END get_hist_chng_cur_line_total;
1626
1627 /**************************************************************************
1628 * This function returns line total for amount based lines
1629 **************************************************************************/
1630 FUNCTION get_hist_chng_cur_line_amount(reqlineid IN NUMBER, chgreqgrpid IN NUMBER)
1631 RETURN NUMBER IS
1632 amount NUMBER := 0;
1633 BEGIN
1634 SELECT new_currency_amount
1635 INTO amount
1636 FROM po_change_requests
1637 WHERE
1638 document_line_id = reqlineid AND
1639 change_request_group_id = chgreqgrpid AND
1640 document_type = 'REQ' AND
1641 action_type = 'DERIVED' AND
1642 new_currency_amount IS NOT NULL;
1643
1644 RETURN amount;
1645
1646 EXCEPTION
1647 WHEN OTHERS THEN
1648 RETURN NULL;
1649
1650 END get_hist_chng_cur_line_amount;
1651
1652 /**************************************************************************
1653 * This function returns distribution currency total for amount based lines
1654 **************************************************************************/
1655 FUNCTION get_hist_cur_dist_amount(reqdistid IN NUMBER, chgreqgrpid IN NUMBER)
1656 RETURN NUMBER IS
1657 amount NUMBER := 0;
1658 BEGIN
1659
1660 SELECT old_currency_amount
1661 INTO amount
1662 FROM po_change_requests
1663 WHERE
1664 document_distribution_id = reqdistid AND
1665 change_request_group_id = chgreqgrpid AND
1666 document_type = 'REQ';
1667
1668 RETURN amount;
1669
1670 END get_hist_cur_dist_amount;
1671
1672
1673 /**************************************************************************
1674 * This function returns changed distribution currency total for amount based lines
1675 **************************************************************************/
1676 FUNCTION get_chng_hist_cur_dist_amount(reqdistid IN NUMBER, chgreqgrpid IN NUMBER)
1677 RETURN NUMBER IS
1678 amount NUMBER := 0;
1679 BEGIN
1680 SELECT new_currency_amount
1681 INTO amount
1682 FROM po_change_requests
1683 WHERE
1684 document_distribution_id = reqdistid AND
1685 change_request_group_id = chgreqgrpid AND
1686 document_type = 'REQ';
1687
1688 RETURN amount;
1689
1690
1691 END get_chng_hist_cur_dist_amount;
1692
1693
1694 /**************************************************************************
1695 * This function returns the non recoverable tax amount of a requisition *
1696 * line during requester change order flow *
1697 **************************************************************************/
1698 FUNCTION get_intchnline_nonrectax_total(reqlineid IN NUMBER) RETURN NUMBER IS
1699 changed_tax_total NUMBER := 0;
1700 new_line_total NUMBER := 0;
1701 old_nonrec_tax NUMBER := 0;
1702 old_line_total NUMBER := 0;
1703 BEGIN
1704
1705 -- get changed line total
1706 new_line_total := get_int_changed_line_total(reqlineid);
1707
1708 -- get old line total
1709 --SELECT SUM(decode(prl.matching_basis, 'AMOUNT', prd.req_line_amount,prd.req_line_quantity*prl.unit_price))
1710 SELECT SUM(prd.req_line_quantity * prl.unit_price)
1711 INTO old_line_total
1712 FROM
1713 po_requisition_lines_all prl,
1714 po_req_distributions prd
1715 WHERE
1716 prl.requisition_line_id = reqlineid AND
1717 prl.requisition_line_id = prd.requisition_line_id;
1718
1719 -- get old tax
1720 old_nonrec_tax := por_view_reqs_pkg.get_line_nonrec_tax_total(reqlineid);
1721
1722 changed_tax_total := nvl((old_nonrec_tax * (new_line_total / old_line_total)), 0);
1723
1724 RETURN changed_tax_total;
1725
1726 EXCEPTION
1727 WHEN OTHERS THEN
1728 RETURN 0;
1729 END get_intchnline_nonrectax_total;
1730
1731
1732 /**************************************************************************
1733 * This function returns the recoverable tax amount of a requisition *
1734 * line during requester change order flow *
1735 **************************************************************************/
1736 FUNCTION get_intchnline_rectax_total(reqlineid IN NUMBER) RETURN NUMBER IS
1737 changed_tax_total NUMBER := 0;
1738 new_line_total NUMBER := 0;
1739 old_rec_tax NUMBER := 0;
1740 old_line_total NUMBER := 0;
1741 BEGIN
1742 -- get changed line total
1743 new_line_total := get_int_changed_line_total(reqlineid);
1744
1745 -- get old line total
1746 -- SELECT SUM(decode(prl.matching_basis, 'AMOUNT', prd.req_line_amount,prd.req_line_quantity*prl.unit_price))
1747 SELECT SUM(prd.req_line_quantity * prl.unit_price)
1748 INTO old_line_total
1749 FROM
1750 po_requisition_lines_all prl,
1751 po_req_distributions_all prd
1752 WHERE
1753 prl.requisition_line_id = reqlineid AND
1754 prl.requisition_line_id = prd.requisition_line_id;
1755
1756 -- get old tax
1757 old_rec_tax := por_view_reqs_pkg.get_line_rec_tax_total(reqlineid);
1758
1759 changed_tax_total := nvl((old_rec_tax * (new_line_total / old_line_total)), 0);
1760
1761 RETURN changed_tax_total;
1762
1763 EXCEPTION
1764 WHEN OTHERS THEN
1765 RETURN 0;
1766 END get_intchnline_rectax_total;
1767
1768
1769
1770
1771 END por_change_request_pkg;
[Home] [Help]