[Home] [Help]
PACKAGE BODY: APPS.PA_MC_CURRENCY_PKG
Source
1 PACKAGE BODY PA_MC_CURRENCY_PKG AS
2 --$Header: PAXMCURB.pls 120.5 2007/12/26 09:34:19 hkansal ship $
3
4
5 FUNCTION CurrRound( x_amount IN NUMBER ,
6 x_currency_code IN VARCHAR2 := FunctionalCurrency )
7 RETURN NUMBER
8 IS
9 l_mau fnd_currencies.minimum_accountable_unit%TYPE;
10 l_precision fnd_currencies.precision%TYPE;
11
12 BEGIN
13
14 SELECT precision,
15 minimum_accountable_unit
16 INTO l_precision,
17 l_mau
18 FROM fnd_currencies
19 WHERE currency_code = x_currency_code;
20
21 IF l_mau IS NOT NULL THEN
22
23 IF l_mau < 0.00001 THEN
24 RETURN( round(x_amount, 5));
25 ELSE
26 RETURN( round(x_amount/l_mau) * l_mau );
27 END IF;
28
29 ELSIF l_precision IS NOT NULL THEN
30
31 IF l_precision > 5 THEN
32 RETURN( round(x_amount, 5));
33 ELSE
34 RETURN( round(x_amount, l_precision));
35 END IF;
36
37 ELSE
38 RETURN( round(x_amount, 5));
39
40 END IF;
41 EXCEPTION
42 WHEN OTHERS THEN
43 RAISE;
44 END CurrRound;
45 -------------------------------------------------------------
46
47 FUNCTION functional_currency(x_org_id IN NUMBER) RETURN VARCHAR2
48 IS
49 currency VARCHAR2(30);
50 BEGIN
51
52 IF (x_org_id = G_PREV_ORG_ID) THEN
53
54 RETURN (G_PREV_CURRENCY);
55 ELSE
56
57 G_PREV_ORG_ID := x_org_id;
58
59 SELECT gl.currency_code
60 INTO currency
61 FROM gl_sets_of_books gl,
62 pa_implementations_all i
63 -- WHERE NVL(i.org_id,-99) = NVL(x_org_id,-99)
64 WHERE i.org_id = x_org_id -- x_org_id also taken from Implementation table.
65 AND i.set_of_books_id = gl.set_of_books_id;
66
67 G_PREV_CURRENCY := currency;
68 RETURN (currency);
69
70 END IF;
71
72 EXCEPTION WHEN OTHERS THEN
73 G_PREV_ORG_ID := x_org_id;
74 G_PREV_CURRENCY := NULL;
75 RAISE;
76
77 END functional_currency;
78
79 -------------------------------------------------------------
80
81 FUNCTION set_of_books(x_org_id IN NUMBER) RETURN NUMBER
82 IS
83 sob_id NUMBER;
84
85 BEGIN
86
87 IF (x_org_id = G_PREV_ORG_ID2) THEN
88
89 RETURN (G_PREV_SOB_ID);
90 ELSE
91
92 G_PREV_ORG_ID2 := x_org_id;
93
94 SELECT set_of_books_id
95 INTO sob_id
96 FROM pa_implementations_all i
97 -- WHERE NVL(i.org_id,-99) = NVL(x_org_id,-99);
98 where i.org_id = x_org_id ;
99
100 G_PREV_SOB_ID := sob_id;
101 RETURN sob_id;
102
103 END IF;
104
105 EXCEPTION WHEN OTHERS THEN
106 G_PREV_ORG_ID2:= x_org_id;
107 G_PREV_SOB_ID := NULL;
108 RAISE;
109
110 END set_of_books;
111
112 -------------------------------------------------------------
113 FUNCTION set_of_books RETURN NUMBER
114 IS
115 sob_id NUMBER;
116
117 /* This function returns SOB id from pa_implementations
118 Hence, no Org info needed */
119
120 BEGIN
121
122 SELECT set_of_books_id
123 INTO sob_id
124 FROM pa_implementations;
125
126 RETURN sob_id;
127
128 EXCEPTION WHEN OTHERS THEN
129 RAISE;
130
131 END set_of_books;
132
133 -------------------------------------------------------------
134 FUNCTION get_mrc_sob_type_code( x_set_of_books_id IN NUMBER )
135 RETURN VARCHAR2
136 IS
137 sob_type VARCHAR2(1);
138 /* Returns MRC_SOB_TYPE_CODE for the given SOB */
139
140 BEGIN
141
142 SELECT mrc_sob_type_code
143 INTO sob_type
144 FROM gl_sets_of_books gl
145 WHERE gl.set_of_books_id = x_set_of_books_id;
146
147 RETURN sob_type;
148
149 EXCEPTION WHEN OTHERS THEN
150 RAISE;
151
152 END get_mrc_sob_type_code;
153 -------------------------------------------------------------
154 FUNCTION get_mrc_sob_type_code RETURN VARCHAR2
155 IS
156 sob_type VARCHAR2(1);
157 /* Returns MRC_SOB_TYPE_CODE for the SOB from
158 PA_Implementations */
159
160 BEGIN
161 SELECT mrc_sob_type_code
162 INTO sob_type
163 FROM gl_sets_of_books gl,
164 pa_implementations imp
165 WHERE gl.set_of_books_id = imp.set_of_books_id;
166
167 RETURN sob_type;
168
169 EXCEPTION WHEN OTHERS THEN
170 RAISE;
171
172 END get_mrc_sob_type_code;
173
174 -------------------------------------------------------------
175 PROCEDURE eiid_details( x_eiid IN NUMBER,
176 x_orig_trx OUT NOCOPY VARCHAR2,
177 x_adj_item OUT NOCOPY NUMBER,
178 x_linkage OUT NOCOPY VARCHAR2,
179 x_ei_date OUT NOCOPY DATE,
180 --Bug#1078399
181 --New parameter x_txn_source added in eiid_details() - to be used to
182 --check whether the EI is an imported-one or not.
183 x_txn_source OUT NOCOPY VARCHAR2,
184 x_err_stack IN OUT NOCOPY VARCHAR2,
185 x_err_stage IN OUT NOCOPY VARCHAR2,
186 x_err_code OUT NOCOPY NUMBER)
187
188 IS
189 l_old_stack VARCHAR2(2000);
190
191
192 BEGIN
193 l_old_stack := x_err_stack;
194 x_err_code := 0;
195 x_err_stack := x_err_stack ||'->PA_MC_CURRENCY_PKG.eiid_details';
196 x_err_stage := ' Select from pa_expenditure_items_all';
197
198 SELECT eia.orig_transaction_reference,
199 nvl(eia.adjusted_expenditure_item_id, transferred_from_exp_item_id),
200 eia.system_linkage_function,
201 eia.expenditure_item_date,
202 --Bug#1078399
203 --New parameter x_txn_source added in eiid_details() - to be used to
204 --check whether the EI is an imported-one or not.
205 eia.transaction_source
206 INTO x_orig_trx,
207 x_adj_item,
208 x_linkage,
209 x_ei_date,
210 --Bug#1078399
211 x_txn_source
212 FROM pa_expenditure_items_all eia
213 WHERE eia.expenditure_item_id = x_eiid;
214
215 x_err_stack := l_old_stack;
216
217 EXCEPTION
218 WHEN NO_DATA_FOUND THEN
219 x_err_code := SQLCODE;
220 x_orig_trx := Null;
221 x_adj_item := Null;
222 x_linkage := Null;
223 x_ei_date := NUll;
224 x_txn_source := Null;
225 WHEN OTHERS THEN
226 x_orig_trx := Null;
227 x_adj_item := Null;
228 x_linkage := Null;
229 x_ei_date := NUll;
230 x_txn_source := Null;
231 RAISE;
232
233 END eiid_details;
234
235 -------------------------------------------------------------
236 PROCEDURE eiid_details( x_eiid IN NUMBER,
237 x_orig_trx OUT NOCOPY VARCHAR2,
238 x_adj_item OUT NOCOPY NUMBER,
239 x_linkage OUT NOCOPY VARCHAR2,
240 x_ei_date OUT NOCOPY DATE,
241 x_err_stack IN OUT NOCOPY VARCHAR2,
242 x_err_stage IN OUT NOCOPY VARCHAR2,
243 x_err_code OUT NOCOPY NUMBER)
244
245 IS
246 l_old_stack VARCHAR2(2000);
247
248
249 BEGIN
250 l_old_stack := x_err_stack;
251 x_err_code := 0;
252 x_err_stack := x_err_stack ||'->PA_MC_CURRENCY_PKG.eiid_details';
253 x_err_stage := ' Select from pa_expenditure_items_all';
254
255 SELECT eia.orig_transaction_reference,
256 nvl(eia.adjusted_expenditure_item_id, transferred_from_exp_item_id),
257 eia.system_linkage_function,
258 eia.expenditure_item_date
259 INTO x_orig_trx,
260 x_adj_item,
261 x_linkage,
262 x_ei_date
263 FROM pa_expenditure_items_all eia
264 WHERE eia.expenditure_item_id = x_eiid;
265
266 x_err_stack := l_old_stack;
267
268 EXCEPTION
269 WHEN NO_DATA_FOUND THEN
270 x_err_code := SQLCODE;
271 x_orig_trx := Null;
272 x_adj_item := Null;
273 x_linkage := Null;
274 x_ei_date := NUll;
275 WHEN OTHERS THEN
276 x_orig_trx := Null;
277 x_adj_item := Null;
278 x_linkage := Null;
279 x_ei_date := NUll;
280 RAISE;
281
282 END eiid_details;
283
284 -------------------------------------------------------------
285
286 FUNCTION max_cost_line ( x_eiid IN NUMBER,
287 x_sob IN NUMBER) RETURN NUMBER IS
288
289 out_line NUMBER;
290
291 BEGIN
292
293 SELECT max(line_num)
294 INTO out_line
295 FROM pa_mc_cost_dist_lines_all
296 WHERE expenditure_item_id = x_eiid
297 AND set_of_books_id = x_sob
298 AND line_type||'' = 'R';
299
300 RETURN out_line;
301
302 EXCEPTION WHEN OTHERS THEN
303 RAISE ;
304 END max_cost_line;
305
306 -------------------------------------------------------------
307
308 FUNCTION max_rev_line(x_eiid IN NUMBER,
309 x_sob IN NUMBER) RETURN NUMBER IS
310
311 out_line NUMBER;
312
313 BEGIN
314
315 SELECT max(line_num)
316 INTO out_line
317 FROM pa_mc_cust_rdl_all
318 WHERE expenditure_item_id = x_eiid
319 AND set_of_books_id = x_sob;
320
321
322 RETURN out_line;
323
324 EXCEPTION WHEN OTHERS THEN
325 RAISE;
326 END max_rev_line;
327
328 -------------------------------------------------------------
329
330 PROCEDURE get_orig_cost_rates( x_adj_item IN NUMBER,
331 x_line_num IN NUMBER,
332 x_set_of_books_id IN NUMBER,
333 x_exchange_rate OUT NOCOPY NUMBER,
334 x_exchange_date OUT NOCOPY DATE,
335 x_exchange_rate_type OUT NOCOPY VARCHAR2,
336 x_err_stack IN OUT NOCOPY VARCHAR2,
337 x_err_stage IN OUT NOCOPY VARCHAR2,
338 x_err_code OUT NOCOPY NUMBER)
339 IS
340 l_old_stack VARCHAR2(2000);
341
342
343 BEGIN
344 l_old_stack := x_err_stack;
345 x_err_code := 0;
346 x_err_stack := x_err_stack ||'->PA_MC_CURRENCY_PKG.get_orig_cost_rates';
347 x_err_stage := ' Select from pa_mc_cost_dist_lines_all';
348
349 SELECT exchange_rate,
350 conversion_date,
351 rate_type
352 INTO x_exchange_rate,
353 x_exchange_date,
354 x_exchange_rate_type
355 FROM pa_mc_cost_dist_lines_all
356 WHERE expenditure_item_id = x_adj_item
357 AND line_num = x_line_num
358 AND set_of_books_id = x_set_of_books_id;
359
360 x_err_stack := l_old_stack;
361
362 EXCEPTION
363 WHEN NO_DATA_FOUND THEN
364 x_err_code := SQLCODE;
365 x_exchange_rate := Null;
366 x_exchange_date := Null;
367 x_exchange_rate_type := Null;
368 WHEN OTHERS THEN
369 x_exchange_rate := Null;
370 x_exchange_date := Null;
371 x_exchange_rate_type := Null;
372 RAISE;
373
374 END get_orig_cost_rates;
375
376 PROCEDURE get_orig_ei_cost_rates( x_exp_item_id IN NUMBER,
377 x_set_of_books_id IN NUMBER,
378 x_exchange_rate OUT NOCOPY NUMBER,
379 x_exchange_date OUT NOCOPY DATE,
380 x_exchange_rate_type OUT NOCOPY VARCHAR2,
381 x_err_stack IN OUT NOCOPY VARCHAR2,
382 x_err_stage IN OUT NOCOPY VARCHAR2,
383 x_err_code OUT NOCOPY NUMBER)
384 IS
385
386 l_old_stack VARCHAR2(2000);
387
388 BEGIN
389
390 l_old_stack := x_err_stack;
391 x_err_code := 0;
392 x_err_stack := x_err_stack ||'->PA_MC_CURRENCY_PKG.get_orig_ei_cost_rates';
393 x_err_stage := ' Select from pa_mc_exp_items_all';
394
395 SELECT cost_exchange_rate,
396 cost_conversion_date,
397 cost_rate_type
398 INTO x_exchange_rate,
399 x_exchange_date,
400 x_exchange_rate_type
401 FROM pa_mc_exp_items_all
402 WHERE expenditure_item_id = x_exp_item_id
403 AND set_of_books_id = x_set_of_books_id;
404
405 x_err_stack := l_old_stack;
406
407 EXCEPTION
408 WHEN NO_DATA_FOUND THEN
409 x_err_code := SQLCODE;
410 x_exchange_rate := Null;
411 x_exchange_date := Null;
412 x_exchange_rate_type := Null;
413 WHEN OTHERS THEN
414 x_exchange_rate := Null;
415 x_exchange_date := Null;
416 x_exchange_rate_type := Null;
417 RAISE;
418
419 END get_orig_ei_cost_rates;
420
421 -------------------------------------------------------------
422
423 PROCEDURE get_cost_amts(x_exp_item_id IN NUMBER,
424 x_set_of_books_id IN NUMBER,
425 x_line_num IN NUMBER,
426 x_amount OUT NOCOPY NUMBER,
427 x_quantity OUT NOCOPY NUMBER,
428 x_exchange_rate OUT NOCOPY NUMBER,
429 x_exchange_date OUT NOCOPY DATE,
430 x_exchange_rate_type OUT NOCOPY VARCHAR2,
431 x_err_stack IN OUT NOCOPY VARCHAR2,
432 x_err_stage IN OUT NOCOPY VARCHAR2,
433 x_err_code OUT NOCOPY NUMBER)
434 IS
435
436 l_old_stack VARCHAR2(2000);
437
438 BEGIN
439
440 l_old_stack := x_err_stack;
441 x_err_code := 0;
442 x_err_stack := x_err_stack ||'->PA_MC_CURRENCY_PKG.get_cost_amts';
443 x_err_stage := ' Select from pa_mc_cost_dist_lines_all';
444
445 SELECT amount,
446 quantity,
447 exchange_rate,
448 conversion_date,
449 rate_type
450 INTO x_amount,
451 x_quantity,
452 x_exchange_rate,
453 x_exchange_date,
454 x_exchange_rate_type
455 FROM pa_mc_cost_dist_lines_all
456 WHERE expenditure_item_id = x_exp_item_id
457 AND line_num = x_line_num
458 AND set_of_books_id = x_set_of_books_id;
459
460 x_err_stack := l_old_stack;
461
462 EXCEPTION
463 WHEN NO_DATA_FOUND THEN
464 x_err_code := SQLCODE;
465 x_amount := Null;
466 x_quantity := Null;
467 x_exchange_rate := Null;
468 x_exchange_date := Null;
469 x_exchange_rate_type := Null;
470 WHEN OTHERS THEN
471 x_amount := Null;
472 x_quantity := Null;
473 x_exchange_rate := Null;
474 x_exchange_date := Null;
475 x_exchange_rate_type := Null;
476 RAISE;
477
478 END get_cost_amts;
479 -------------------------------------------------------------
480 PROCEDURE get_max_cost_amts(x_exp_item_id IN NUMBER,
481 x_set_of_books_id IN NUMBER,
482 x_raw_cost OUT NOCOPY NUMBER,
483 x_burdened_cost OUT NOCOPY NUMBER,
484 x_exchange_rate OUT NOCOPY NUMBER,
485 x_exchange_date OUT NOCOPY DATE,
486 x_exchange_rate_type OUT NOCOPY VARCHAR2,
487 x_err_stack IN OUT NOCOPY VARCHAR2,
488 x_err_stage IN OUT NOCOPY VARCHAR2,
489 x_err_code OUT NOCOPY NUMBER)
490 IS
491
492 l_old_stack VARCHAR2(2000);
493
494 BEGIN
495
496 l_old_stack := x_err_stack;
497 x_err_code := 0;
498 x_err_stack := x_err_stack ||'->PA_MC_CURRENCY_PKG.get_max_cost_amts';
499 x_err_stage := ' Select from pa_mc_cost_dist_lines_all';
500 x_raw_cost := NULL;
501 x_burdened_cost := NULL;
502
503 SELECT amount,
504 NVL(burdened_cost,0),
505 NVL(exchange_rate,0),
506 conversion_date,
507 rate_type
508 INTO x_raw_cost,
509 x_burdened_cost,
510 x_exchange_rate,
511 x_exchange_date,
512 x_exchange_rate_type
513 FROM pa_mc_cost_dist_lines_all
514 WHERE set_of_books_id = x_set_of_books_id
515 AND expenditure_item_id = x_exp_item_id
516 AND line_num = ( select max(line_num)
517 from PA_COST_DISTRIBUTION_LINES_ALL
518 where expenditure_item_id = x_exp_item_id
519 and line_type = 'R' );
520
521 x_err_stack := l_old_stack;
522
523 EXCEPTION
524 WHEN NO_DATA_FOUND THEN
525 x_err_stack := l_old_stack;
526 x_err_code := SQLCODE;
527 x_raw_cost := Null;
528 x_burdened_cost := Null;
529 x_exchange_rate := Null;
530 x_exchange_date := Null;
531 x_exchange_rate_type := Null;
532 WHEN OTHERS THEN
533 x_raw_cost := Null;
534 x_burdened_cost := Null;
535 x_exchange_rate := Null;
536 x_exchange_date := Null;
537 x_exchange_rate_type := Null;
538 RAISE;
539
540 END get_max_cost_amts;
541 -------------------------------------------------------------
542 PROCEDURE get_max_crdl_amts(x_exp_item_id IN NUMBER,
543 x_set_of_books_id IN NUMBER,
544 x_revenue OUT NOCOPY NUMBER,
545 x_bill_amount OUT NOCOPY NUMBER,
546 x_exchange_rate OUT NOCOPY NUMBER,
547 x_err_stack IN OUT NOCOPY VARCHAR2,
548 x_err_stage IN OUT NOCOPY VARCHAR2,
549 x_err_code OUT NOCOPY NUMBER)
550 IS
551
552 l_old_stack VARCHAR2(2000);
553
554 BEGIN
555
556 l_old_stack := x_err_stack;
557 x_err_code := 0;
558 x_err_stack := x_err_stack ||'->PA_MC_CURRENCY_PKG.get_max_crdl_amts';
559 x_err_stage := ' Select from pa_mc_cust_rdl_all ';
560 x_revenue := NULL;
561 x_bill_amount := NULL;
562
563 SELECT SUM(amount),
564 SUM(NVL(bill_amount,0)),
565 Min(NVL(exchange_rate,0))
566 INTO x_revenue,
567 x_bill_amount,
568 x_exchange_rate
569 FROM pa_mc_cust_rdl_all
570 WHERE set_of_books_id = x_set_of_books_id
571 AND expenditure_item_id = x_exp_item_id;
572
573 x_err_stack := l_old_stack;
574
575 EXCEPTION
576 WHEN NO_DATA_FOUND THEN
577 x_err_stack := l_old_stack;
578 x_err_code := SQLCODE;
579 x_exchange_rate := Null;
580 WHEN OTHERS THEN
581 x_exchange_rate := Null;
582 RAISE;
583
584 END get_max_crdl_amts;
585
586 -------------------------------------------------------------
587
588
589 PROCEDURE get_orig_rev_rates( x_adj_item IN NUMBER,
590 x_line_num IN NUMBER,
591 x_set_of_books_id IN NUMBER,
592 x_exchange_rate OUT NOCOPY NUMBER,
593 x_exchange_date OUT NOCOPY DATE,
594 x_exchange_rate_type OUT NOCOPY VARCHAR2,
595 x_err_stack IN OUT NOCOPY VARCHAR2,
596 x_err_stage IN OUT NOCOPY VARCHAR2,
597 x_err_code OUT NOCOPY NUMBER)
598 IS
599
600 l_old_stack VARCHAR2(2000);
601
602 BEGIN
603
604 l_old_stack := x_err_stack;
605 x_err_code := 0;
606 x_err_stack := x_err_stack ||'->PA_MC_CURRENCY_PKG.get_orig_rev_rates';
607 x_err_stage := ' Select from pa_mc_cust_rdl_all';
608
609 SELECT exchange_rate,
610 conversion_date
611 INTO x_exchange_rate,
612 x_exchange_date
613 FROM pa_mc_cust_rdl_all
614 WHERE expenditure_item_id = x_adj_item
615 AND line_num = x_line_num
616 AND set_of_books_id = x_set_of_books_id;
617
618 x_err_stack := l_old_stack;
619
620 EXCEPTION
621 WHEN NO_DATA_FOUND THEN
622 x_err_code := SQLCODE;
623 x_exchange_rate := Null;
624 x_exchange_date := Null;
625 x_exchange_rate_type := Null;
629 x_exchange_rate_type := Null;
626 WHEN OTHERS THEN
627 x_exchange_rate := Null;
628 x_exchange_date := Null;
630 RAISE;
631
632 END get_orig_rev_rates;
633 -------------------------------------------------------------
634
635 PROCEDURE get_orig_ei_mc_rates( x_adj_exp_item_id IN NUMBER,
636 x_xfer_exp_item_id IN NUMBER,
637 x_set_of_books_id IN NUMBER,
638 x_raw_cost OUT NOCOPY NUMBER,
639 x_raw_cost_rate OUT NOCOPY NUMBER,
640 x_burden_cost OUT NOCOPY NUMBER,
641 x_burden_cost_rate OUT NOCOPY NUMBER,
642 x_bill_amount OUT NOCOPY NUMBER,
643 x_bill_rate OUT NOCOPY NUMBER,
644 x_accrued_revenue OUT NOCOPY NUMBER,
645 x_accrual_rate OUT NOCOPY NUMBER,
646 x_transfer_price OUT NOCOPY NUMBER,
647 x_adjusted_rate OUT NOCOPY NUMBER,
648 x_exchange_rate OUT NOCOPY NUMBER,
649 x_exchange_date OUT NOCOPY DATE,
650 x_exchange_rate_type OUT NOCOPY VARCHAR2,
651 x_err_stack IN OUT NOCOPY VARCHAR2,
652 x_err_stage IN OUT NOCOPY VARCHAR2,
653 x_err_code OUT NOCOPY NUMBER)
654
655 IS
656
657 l_old_stack VARCHAR2(2000);
658
659 BEGIN
660
661 l_old_stack := x_err_stack;
662 x_err_code := 0;
663 x_err_stack := x_err_stack ||'->PA_MC_CURRENCY_PKG.get_orig_ei_mc_rates';
664 x_err_stage := ' Select from pa_mc_exp_items_all';
665
666 select RAW_COST,
667 RAW_COST_RATE,
668 BURDEN_COST,
669 BURDEN_COST_RATE,
670 BILL_AMOUNT,
671 BILL_RATE,
672 ACCRUED_REVENUE,
673 ACCRUAL_RATE,
674 TRANSFER_PRICE,
675 ADJUSTED_RATE,
676 COST_EXCHANGE_RATE,
677 COST_CONVERSION_DATE,
678 COST_RATE_TYPE
679 INTO x_raw_cost,
680 x_raw_cost_rate ,
681 x_burden_cost,
682 x_burden_cost_rate ,
683 x_bill_amount,
684 x_bill_rate ,
685 x_accrued_revenue,
686 x_accrual_rate ,
687 x_transfer_price,
688 x_adjusted_rate,
689 x_exchange_rate,
690 x_exchange_date,
691 x_exchange_rate_type
692 FROM PA_MC_EXP_ITEMS_ALL
693 WHERE SET_OF_BOOKS_ID = x_set_of_books_id
694 AND EXPENDITURE_ITEM_ID = nvl(x_adj_exp_item_id,x_xfer_exp_item_id);
695
696 x_err_stack := l_old_stack;
697
698 EXCEPTION
699 WHEN NO_DATA_FOUND THEN
700 x_err_code := SQLCODE;
701 x_raw_cost := Null;
702 x_raw_cost_rate := Null;
703 x_burden_cost := Null;
704 x_burden_cost_rate := Null;
705 x_bill_amount := Null;
706 x_bill_rate := Null;
707 x_accrued_revenue := Null;
708 x_accrual_rate := Null;
709 x_transfer_price := Null;
710 x_adjusted_rate := Null;
711 x_exchange_rate := Null;
712 x_exchange_date := Null;
713 x_exchange_rate_type := Null;
714
715 WHEN OTHERS THEN
716 x_raw_cost := Null;
717 x_raw_cost_rate := Null;
718 x_burden_cost := Null;
719 x_burden_cost_rate := Null;
720 x_bill_amount := Null;
721 x_bill_rate := Null;
722 x_accrued_revenue := Null;
723 x_accrual_rate := Null;
724 x_transfer_price := Null;
725 x_adjusted_rate := Null;
726 x_exchange_rate := Null;
727 x_exchange_date := Null;
728 x_exchange_rate_type := Null;
729 RAISE;
730
731 END get_orig_ei_mc_rates;
732 -------------------------------------------------------------
733
734 PROCEDURE get_orig_ei_mc_rates( x_adj_exp_item_id IN NUMBER,
735 x_xfer_exp_item_id IN NUMBER,
736 x_set_of_books_id IN NUMBER,
737 x_raw_cost OUT NOCOPY NUMBER,
738 x_raw_cost_rate OUT NOCOPY NUMBER,
739 x_burden_cost OUT NOCOPY NUMBER,
740 x_burden_cost_rate OUT NOCOPY NUMBER,
741 x_bill_amount OUT NOCOPY NUMBER,
742 x_bill_rate OUT NOCOPY NUMBER,
743 x_accrued_revenue OUT NOCOPY NUMBER,
744 x_accrual_rate OUT NOCOPY NUMBER,
745 x_transfer_price OUT NOCOPY NUMBER,
746 x_adjusted_rate OUT NOCOPY NUMBER,
747 x_exchange_rate OUT NOCOPY NUMBER,
748 x_exchange_date OUT NOCOPY DATE,
749 x_exchange_rate_type OUT NOCOPY VARCHAR2,
750 x_raw_revenue OUT NOCOPY NUMBER,/*3024103*/
754 x_err_stage IN OUT NOCOPY VARCHAR2,
751 x_adj_revenue OUT NOCOPY NUMBER,/*3024103*/
752 x_forecast_revenue OUT NOCOPY NUMBER,/*3024103*/
753 x_err_stack IN OUT NOCOPY VARCHAR2,
755 x_err_code OUT NOCOPY NUMBER)
756
757 IS
758 l_old_stack VARCHAR2(2000);
759
760 BEGIN
761 l_old_stack := x_err_stack;
762 x_err_code := 0;
763 x_err_stack := x_err_stack ||'->PA_MC_CURRENCY_PKG.get_orig_ei_mc_rates';
764 x_err_stage := ' Select from pa_mc_exp_items_all';
765
766 select RAW_COST,
767 RAW_COST_RATE,
768 BURDEN_COST,
769 BURDEN_COST_RATE,
770 BILL_AMOUNT,
771 BILL_RATE,
772 ACCRUED_REVENUE,
773 ACCRUAL_RATE,
774 TRANSFER_PRICE,
775 ADJUSTED_RATE,
776 COST_EXCHANGE_RATE,
777 COST_CONVERSION_DATE,
778 COST_RATE_TYPE,
779 RAW_REVENUE, /*3024103*/
780 ADJUSTED_REVENUE,/*3024103*/
781 FORECAST_REVENUE/*3024103*/
782 INTO x_raw_cost,
783 x_raw_cost_rate ,
784 x_burden_cost,
785 x_burden_cost_rate ,
786 x_bill_amount,
787 x_bill_rate ,
788 x_accrued_revenue,
789 x_accrual_rate ,
790 x_transfer_price,
791 x_adjusted_rate,
792 x_exchange_rate,
793 x_exchange_date,
794 x_exchange_rate_type,
795 x_raw_revenue,/*3024103*/
796 x_adj_revenue,/*3024103*/
797 x_forecast_revenue /*3024103*/
798 FROM PA_MC_EXP_ITEMS_ALL
799 WHERE SET_OF_BOOKS_ID = x_set_of_books_id
800 AND EXPENDITURE_ITEM_ID = nvl(x_adj_exp_item_id,x_xfer_exp_item_id);
801
802 x_err_stack := l_old_stack;
803
804 EXCEPTION
805 WHEN NO_DATA_FOUND THEN
806 x_err_code := SQLCODE;
807 x_raw_cost := Null;
808 x_raw_cost_rate := Null;
809 x_burden_cost := Null;
810 x_burden_cost_rate := Null;
811 x_bill_amount := Null;
812 x_bill_rate := Null;
813 x_accrued_revenue := Null;
814 x_accrual_rate := Null;
815 x_transfer_price := Null;
816 x_adjusted_rate := Null;
817 x_exchange_rate := Null;
818 x_exchange_date := Null;
819 x_exchange_rate_type := Null;
820 x_raw_revenue := Null;
821 x_adj_revenue := Null;
822 x_forecast_revenue := Null;
823
824 WHEN OTHERS THEN
825 x_raw_cost := Null;
826 x_raw_cost_rate := Null;
827 x_burden_cost := Null;
828 x_burden_cost_rate := Null;
829 x_bill_amount := Null;
830 x_bill_rate := Null;
831 x_accrued_revenue := Null;
832 x_accrual_rate := Null;
833 x_transfer_price := Null;
834 x_adjusted_rate := Null;
835 x_exchange_rate := Null;
836 x_exchange_date := Null;
837 x_exchange_rate_type := Null;
838 x_raw_revenue := Null;
839 x_adj_revenue := Null;
840 x_forecast_revenue := Null;
841 RAISE;
842
843 END get_orig_ei_mc_rates;
844 -------------------------------------------------------------
845
846 /* Funding MRC Changes : Adding the New param for Revenue attributes and Invoice conversion attributes */
847
848 PROCEDURE get_orig_event_amts( x_project_id IN NUMBER,
849 x_event_num IN NUMBER,
850 x_task_id IN NUMBER,
851 x_set_of_books_id IN NUMBER,
852 x_bill_amount OUT NOCOPY NUMBER,
853 x_revenue_amount OUT NOCOPY NUMBER,
854 x_rev_rate_type OUT NOCOPY VARCHAR2,
855 x_rev_exchange_rate OUT NOCOPY NUMBER,
856 x_rev_exchange_date OUT NOCOPY DATE,
857 x_inv_exchange_rate OUT NOCOPY NUMBER,
858 x_inv_exchange_date OUT NOCOPY DATE,
859 x_err_stack IN OUT NOCOPY VARCHAR2,
860 x_err_stage IN OUT NOCOPY VARCHAR2,
861 x_err_code OUT NOCOPY NUMBER)
862 IS
863
864 l_old_stack VARCHAR2(2000);
865
866 BEGIN
867
868 l_old_stack := x_err_stack;
869 x_err_code := 0;
870 x_err_stack := x_err_stack ||'->PA_MC_CURRENCY_PKG.get_orig_event_amts';
871 x_err_stage := ' Select from pa_mc_events';
872
873
874 /* Funding MRC Changes : Adding the new attributes for the revenue conversion attributes and
875 invoice conversion attributes */
876
877
878 SELECT rate_type,
879 exchange_rate,
880 conversion_date,
881 projfunc_inv_exchange_rate,
882 projfunc_inv_rate_date,
883 bill_amount,
884 revenue_amount
885 INTO x_rev_rate_type,
886 x_rev_exchange_rate,
887 x_rev_exchange_date,
891 x_revenue_amount
888 x_inv_exchange_rate,
889 x_inv_exchange_date,
890 x_bill_amount,
892 FROM PA_MC_EVENTS
893 WHERE SET_OF_BOOKS_ID = x_set_of_books_id
894 AND PROJECT_ID = x_project_id
895 AND EVENT_NUM = x_event_num
896 AND nvl(TASK_ID,-99) = nvl(x_task_id, -99);
897
898 x_err_stack := l_old_stack;
899
900 EXCEPTION
901 WHEN NO_DATA_FOUND THEN
902 x_err_code := SQLCODE;
903 x_bill_amount := Null;
904 x_revenue_amount := Null;
905 x_rev_rate_type := Null;
906 x_rev_exchange_rate := Null;
907 x_rev_exchange_date := Null;
908 x_inv_exchange_rate := Null;
909 x_inv_exchange_date := Null;
910
911 WHEN OTHERS THEN
912 x_bill_amount := Null;
913 x_revenue_amount := Null;
914 x_rev_rate_type := Null;
915 x_rev_exchange_rate := Null;
916 x_rev_exchange_date := Null;
917 x_inv_exchange_rate := Null;
918 x_inv_exchange_date := Null;
919 RAISE;
920
921 END get_orig_event_amts;
922 -------------------------------------------------------------
923
924 PROCEDURE get_imported_rates( x_set_of_books_id IN NUMBER,
925 x_exp_item_id IN NUMBER,
926 x_raw_cost OUT NOCOPY NUMBER,
927 x_raw_cost_rate OUT NOCOPY NUMBER,
928 x_burden_cost OUT NOCOPY NUMBER,
929 x_burden_cost_rate OUT NOCOPY NUMBER,
930 x_exchange_rate OUT NOCOPY NUMBER,
931 x_exchange_date OUT NOCOPY DATE,
932 x_exchange_rate_type OUT NOCOPY VARCHAR2,
933 x_err_stack IN OUT NOCOPY VARCHAR2,
934 x_err_stage IN OUT NOCOPY VARCHAR2,
935 x_err_code OUT NOCOPY NUMBER)
936 IS
937
938 l_old_stack VARCHAR2(2000);
939
940 BEGIN
941
942 l_old_stack := x_err_stack;
943 x_err_code := 0;
944 x_err_stack := x_err_stack ||'->PA_MC_CURRENCY_PKG.get_imported_rates';
945 x_err_stage := ' Select from pa_mc_txn_interface_all';
946
947 SELECT mc.raw_cost,
948 mc.raw_cost_rate,
949 mc.burdened_cost,
950 mc.burdened_cost_rate,
951 mc.exchange_rate,
952 mc.conversion_date,
953 mc.rate_type
954 INTO x_raw_cost,
955 x_raw_cost_rate,
956 x_burden_cost,
957 x_burden_cost_rate,
958 x_exchange_rate,
959 x_exchange_date,
960 x_exchange_rate_type
961 FROM pa_mc_txn_interface_all mc,
962 pa_transaction_interface_all txn
963 WHERE mc.txn_interface_id = txn.txn_interface_id
964 AND txn.expenditure_item_id = x_exp_item_id
965 AND mc.set_of_books_id = x_set_of_books_id;
966
967 x_err_stack := l_old_stack;
968
969 EXCEPTION
970 WHEN NO_DATA_FOUND THEN
971 x_err_code := SQLCODE;
972 x_raw_cost := Null;
973 x_raw_cost_rate := Null;
974 x_burden_cost := Null;
975 x_burden_cost_rate := Null;
976 x_exchange_rate := Null;
977 x_exchange_date := Null;
978 x_exchange_rate_type := Null;
979
980 WHEN OTHERS THEN
981 x_raw_cost := Null;
982 x_raw_cost_rate := Null;
983 x_burden_cost := Null;
984 x_burden_cost_rate := Null;
985 x_exchange_rate := Null;
986 x_exchange_date := Null;
987 x_exchange_rate_type := Null;
988 RAISE;
989
990 END get_imported_rates;
991 -------------------------------------------------------------
992
993
994
995 PROCEDURE get_ap_keys( x_eiid IN NUMBER,
996 x_ref2 OUT NOCOPY VARCHAR2,
997 x_ref3 OUT NOCOPY VARCHAR2,
998 x_err_stack IN OUT NOCOPY VARCHAR2,
999 x_err_stage IN OUT NOCOPY VARCHAR2,
1000 x_err_code OUT NOCOPY NUMBER)
1001 IS
1002
1003 l_old_stack VARCHAR2(2000);
1004
1005 BEGIN
1006
1007 l_old_stack := x_err_stack;
1008 x_err_code := 0;
1009 x_err_stack := x_err_stack ||'->PA_MC_CURRENCY_PKG.get_ap_keys';
1010 x_err_stage := ' Select from pa_cost_distribution_lines_all';
1011
1012
1013 SELECT cdl.system_reference2,
1014 cdl.system_reference3
1015 INTO x_ref2, -- invoice id
1016 x_ref3 -- line num
1017 FROM pa_cost_distribution_lines_all cdl
1018 WHERE cdl.expenditure_item_id = x_eiid
1019 AND rownum = 1;
1020
1021 x_err_stack := l_old_stack;
1022
1023 EXCEPTION
1024 WHEN NO_DATA_FOUND THEN
1025 x_err_code := SQLCODE;
1026 x_ref2 := Null;
1027 x_ref3 := Null;
1028 WHEN OTHERS THEN
1029 x_ref2 := Null;
1030 x_ref3 := Null;
1031 RAISE;
1032
1033 END get_ap_keys;
1034
1035 -------------------------------------------------------------
1039 x_system_reference4 IN VARCHAR2 ,
1036
1037 PROCEDURE get_ap_rate( x_invoice_id IN NUMBER,
1038 x_line_num IN NUMBER,
1040 x_transaction_source IN VARCHAR2 ,
1041 x_sob IN NUMBER,
1042 x_exchange_rate OUT NOCOPY NUMBER,
1043 x_exchange_date OUT NOCOPY DATE,
1044 x_exchange_rate_type OUT NOCOPY VARCHAR2,
1045 x_amount OUT NOCOPY NUMBER,
1046 x_err_stack IN OUT NOCOPY VARCHAR2,
1047 x_err_stage IN OUT NOCOPY VARCHAR2,
1048 x_err_code OUT NOCOPY NUMBER)
1049
1050 IS
1051
1052 l_old_stack VARCHAR2(2000);
1053 l_reporting_curr_code VARCHAR2(5);
1054 l_amount NUMBER;
1055 l_denom_amt_var NUMBER;
1056 l_base_amt_var NUMBER;
1057
1058 BEGIN
1059
1060 l_old_stack := x_err_stack;
1061 x_err_code := 0;
1062 x_err_stack := x_err_stack ||'->PA_MC_CURRENCY_PKG.get_ap_rate';
1063 x_err_stage := ' Select from ap_mc_invoice_dists';
1064
1065 -- To get the ap rates logic used : FC:Foreign curr, PC:Pri Curr,Rep:Reporting
1066 -- If foreign curr inv, then ap.amount = FC amount, ap.base_Amount = PC amt
1067 -- and mc.base_amount = Rep Amount, mc.amount = FC or PC amt.
1068 -- and exch rate is from FC to Rep, not from PC to rep.
1069 -- IF FC = Rep Curr, then the mc rec for that rep SOB has
1070 -- mc.amount = FC amount, all other amount and exchange cols are NULL.
1071 -- IF PC inv, then base_amount cols are null in both tables and exch rates
1072 -- on mc recs are correct.
1073
1074
1075 /* added the checks for transaction source and system_refereence4
1076 in order to process AP VARIANCE amounts */
1077
1078 IF (x_transaction_source = 'AP VARIANCE' AND x_system_reference4='IPV' ) THEN
1079
1080 SELECT mc.exchange_rate, -- Bug3056201
1081 -- Bug3056201 decode(NVL(ap.BASE_INVOICE_PRICE_VARIANCE,0),0,mc.EXCHANGE_RATE,
1082 -- (mc.BASE_INVOICE_PRICE_VARIANCE/ap.BASE_INVOICE_PRICE_VARIANCE)) exchange_rate,
1083 nvl(mc.exchange_date,ap.exchange_date) exchange_date,
1084 mc.exchange_rate_type,
1085 nvl(nvl(mc.BASE_INVOICE_PRICE_VARIANCE,
1086 GL_MC_CURRENCY_PKG.CurrRound(ap.amount_variance * nvl(mc.exchange_rate,1),l_reporting_curr_code)),0) amount,/*Bug 4292891*/
1087 sob.currency_code
1088 INTO x_exchange_rate,
1089 x_exchange_date,
1090 x_exchange_rate_type,
1091 x_amount,
1092 l_reporting_curr_code
1093 FROM gl_sets_of_books sob,
1094 ap_mc_invoice_dists mc,
1095 ap_invoice_distributions ap
1096 WHERE ap.invoice_id = x_invoice_id
1097 AND ap.distribution_line_number = x_line_num
1098 AND mc.invoice_id = ap.invoice_id
1099 AND mc.distribution_line_number = ap.distribution_line_number
1100 AND mc.set_of_books_Id = x_sob
1101 AND mc.set_of_books_id = sob.set_of_books_id;
1102
1103 /*
1104 IF nvl(x_amount,0) = 0 THEN
1105 AP_PA_API_PKG.get_inv_amount_var(x_invoice_id,x_line_num,l_denom_amt_var,l_base_amt_var);
1106 l_amount := nvl(l_denom_amt_var,0) * nvl(x_exchange_rate,1);
1107 x_amount := GL_MC_CURRENCY_PKG.CurrRound(l_amount, l_reporting_curr_code);
1108 END IF;
1109 */
1110
1111 ELSIF (x_transaction_source = 'AP VARIANCE' AND x_system_reference4='ERV' ) THEN
1112
1113 SELECT /*mc.exchange_rate, Bug3056201 */ /*reverted for bug 3927230 */
1114 decode(NVL(ap.EXCHANGE_RATE_VARIANCE,0),0,mc.EXCHANGE_RATE,
1115 (mc.EXCHANGE_RATE_VARIANCE/ap.EXCHANGE_RATE_VARIANCE)) exchange_rate,
1116 nvl(mc.exchange_date,ap.exchange_date) exchange_date,
1117 mc.exchange_rate_type,
1118 nvl(mc.EXCHANGE_RATE_VARIANCE,0) amount /*bug 4292891*/
1119 INTO x_exchange_rate,
1120 x_exchange_date,
1121 x_exchange_rate_type,
1122 x_amount
1123 FROM ap_mc_invoice_dists mc,
1124 ap_invoice_distributions ap
1125 WHERE ap.invoice_id = x_invoice_id
1126 AND ap.distribution_line_number = x_line_num
1127 AND mc.invoice_id = ap.invoice_id
1128 AND mc.distribution_line_number = ap.distribution_line_number
1129 AND mc.set_of_books_Id = x_sob;
1130
1131 ELSE
1132
1133 SELECT mc.exchange_rate, -- Bug3056201
1134 -- Bug3056201 decode(NVL(ap.base_amount,0),0,mc.exchange_rate,
1135 -- (NVL(mc.base_amount,mc.amount)/ap.base_amount)) exchange_rate,
1136 NVL(mc.exchange_date,ap.exchange_date) exchange_date,
1137 mc.exchange_rate_type,
1138 decode(mc.base_amount,NULL,NVL(mc.amount,0),mc.base_amount) amount /*Bug 4292891 */
1139 INTO x_exchange_rate,
1140 x_exchange_date,
1141 x_exchange_rate_type,
1142 x_amount
1143 FROM ap_mc_invoice_dists mc,
1144 ap_invoice_distributions ap
1145 WHERE ap.invoice_id = x_invoice_id
1146 AND ap.distribution_line_number = x_line_num
1147 AND mc.invoice_id = ap.invoice_id
1151 END IF;
1148 AND mc.distribution_line_number = ap.distribution_line_number
1149 AND mc.set_of_books_Id = x_sob;
1150
1152 x_err_stack := l_old_stack;
1153
1154
1155 EXCEPTION
1156 WHEN NO_DATA_FOUND THEN
1157 x_err_code := SQLCODE;
1158 x_exchange_rate := Null;
1159 x_exchange_date := Null;
1160 x_exchange_rate_type := Null;
1161 x_amount := Null;
1162
1163 WHEN OTHERS THEN
1164 x_exchange_rate := Null;
1165 x_exchange_date := Null;
1166 x_exchange_rate_type := Null;
1167 x_amount := Null;
1168 RAISE;
1169
1170 END get_ap_rate;
1171
1172 -------------------------------------------------------------
1173
1174 FUNCTION sum_rev_rdl( x_project_id IN NUMBER,
1175 x_dr_num IN NUMBER,
1176 x_sob IN NUMBER) RETURN NUMBER
1177 IS
1178 rdl_amt NUMBER;
1179 rdl_amt_event NUMBER;
1180 rdl_amt_sum NUMBER;
1181
1182 BEGIN
1183
1184 SELECT sum(nvl(amount,0))
1185 INTO rdl_amt
1186 FROM pa_mc_cust_rdl_all
1187 WHERE project_id = x_project_id
1188 AND draft_revenue_num = x_dr_num
1189 AND set_of_books_id = x_sob;
1190
1191
1192 SELECT sum(nvl(amount,0))
1193 INTO rdl_amt_event
1194 FROM pa_mc_cust_event_rdl_all
1195 WHERE project_id = x_project_id
1196 AND draft_revenue_num = x_dr_num
1197 AND set_of_books_id = x_sob;
1198
1199 rdl_amt_sum := nvl(rdl_amt,0) + nvl(rdl_amt_event,0);
1200
1201 RETURN nvl(rdl_amt_sum,0);
1202
1203 EXCEPTION WHEN OTHERS THEN
1204 RAISE;
1205
1206 END sum_rev_rdl;
1207
1208 -------------------------------------------------------------
1209
1210 FUNCTION sum_inv( x_project_id IN NUMBER,
1211 x_di_num IN NUMBER,
1212 x_line_num IN NUMBER,
1213 x_sob IN NUMBER) RETURN NUMBER
1214 IS
1215 inv_amt NUMBER;
1216
1217 BEGIN
1218
1219 SELECT sum(nvl(amount,0))
1220 INTO inv_amt
1221 FROM pa_mc_draft_inv_items
1222 WHERE project_id = x_project_id
1223 AND draft_invoice_num = x_di_num
1224 AND line_num = x_line_num
1225 AND set_of_books_id = x_sob;
1226
1227 RETURN nvl(inv_amt,0);
1228
1229 EXCEPTION
1230 WHEN OTHERS THEN
1231 RAISE;
1232
1233 END sum_inv;
1234
1235 -------------------------------------------------------------
1236
1237 FUNCTION sum_inv_rdl( x_project_id IN NUMBER,
1238 x_di_num IN NUMBER,
1239 x_line_num IN NUMBER,
1240 x_sob IN NUMBER) RETURN NUMBER
1241 IS
1242 cust_rdl_amt NUMBER;
1243 ic_rdl_amt NUMBER;
1244 rdl_amt NUMBER;
1245
1246 BEGIN
1247
1248 -- Either the invoice will be a customer invoice or IC invoice
1249 -- so the one of the selects will return a value
1250 --
1251
1252 SELECT sum(nvl(bill_amount,0))
1253 INTO cust_rdl_amt
1254 FROM pa_mc_cust_rdl_all
1255 WHERE project_id = x_project_id
1256 AND draft_invoice_num = x_di_num
1257 AND draft_invoice_item_line_num = x_line_num
1258 AND set_of_books_id = x_sob;
1259
1260 SELECT sum(nvl(mcdii.bill_amount,0))
1261 INTO ic_rdl_amt
1262 FROM pa_mc_draft_inv_details_all mcdii ,
1263 pa_draft_invoice_details_all dii
1264 WHERE dii.project_id = x_project_id
1265 AND dii.draft_invoice_num = x_di_num
1266 AND dii.draft_invoice_line_num = x_line_num
1267 AND dii.draft_invoice_detail_id = mcdii.draft_invoice_detail_id
1268 AND mcdii.set_of_books_id = x_sob;
1269
1270 rdl_amt := nvl(cust_rdl_amt,0) + nvl(ic_rdl_amt,0);
1271
1272 RETURN nvl(rdl_amt,0);
1273
1274 EXCEPTION
1275 WHEN OTHERS THEN
1276 RAISE;
1277
1278 END sum_inv_rdl;
1279
1280 -------------------------------------------------------------
1281
1282 FUNCTION sum_inv_erdl( x_project_id IN NUMBER,
1283 x_di_num IN NUMBER,
1284 x_line_num IN NUMBER,
1285 x_sob IN NUMBER) RETURN NUMBER
1286 IS
1287 rdl_amt NUMBER;
1288
1289 BEGIN
1290
1291 SELECT sum(nvl(amount,0))
1292 INTO rdl_amt
1293 FROM pa_mc_cust_event_rdl_all
1294 WHERE project_id = x_project_id
1295 AND draft_invoice_num = x_di_num
1296 AND draft_invoice_item_line_num = x_line_num
1297 AND set_of_books_Id = x_sob;
1298
1299 RETURN nvl(rdl_amt,0);
1300
1301 EXCEPTION
1302 WHEN OTHERS THEN
1303 RAISE;
1304
1305 END sum_inv_erdl;
1306
1307 -------------------------------------------------------------
1308
1309 FUNCTION sum_inv_ev( x_project_id IN NUMBER,
1310 x_task_id IN NUMBER,
1311 x_event_num IN NUMBER,
1312 x_sob IN NUMBER) RETURN NUMBER
1313
1314 IS
1315 rdl_amt NUMBER;
1316
1317 BEGIN
1318
1319 SELECT sum(nvl(bill_amount,0))
1320 INTO rdl_amt
1321 FROM pa_mc_events
1325 AND set_of_books_id = x_sob;
1322 WHERE project_id = x_project_id
1323 AND nvl(task_id,-99) = nvl(x_task_id,-99)
1324 AND event_num = x_event_num
1326
1327 RETURN nvl(rdl_amt,0);
1328
1329 EXCEPTION
1330 /* IF 0 retuned then trigger to raise error */
1331
1332 WHEN OTHERS THEN
1333 RAISE;
1334
1335 END sum_inv_ev;
1336
1337 -------------------------------------------------------------
1338
1339 FUNCTION event_date( x_project_id IN NUMBER,
1340 x_task_id IN NUMBER,
1341 x_event_Num IN NUMBER) RETURN DATE
1342 IS
1343 event_date DATE;
1344
1345 BEGIN
1346
1347 SELECT completion_date
1348 INTO event_date
1349 FROM pa_events
1350 WHERE project_id = x_project_id
1351 AND nvl(task_Id, -99) = nvl(x_task_id, -99)
1352 AND event_num = x_event_num;
1353
1354 RETURN event_date;
1355
1356 EXCEPTION
1357 WHEN NO_DATA_FOUND THEN
1358 RETURN NULL;
1359 WHEN OTHERS THEN
1360 RAISE;
1361 END event_date;
1362
1363 -------------------------------------------------------------
1364
1365 FUNCTION sum_mc_cust_rdl_erdl( x_project_id IN NUMBER,
1366 x_draft_revenue_num IN NUMBER,
1367 x_draft_revenue_item_line_num IN NUMBER) RETURN NUMBER
1368
1369 IS
1370 rdl_amt NUMBER;
1371 erdl_amt NUMBER;
1372 BEGIN
1373
1374 SELECT sum(nvl(rdl.amount,0))
1375 INTO rdl_amt
1376 FROM pa_mc_cust_rdl_all rdl
1377 -- pa_implementations imp -- Fix for Perf Bug 2695336
1378 WHERE rdl.project_id = x_project_id
1379 AND rdl.draft_revenue_num = x_draft_revenue_num
1380 AND rdl.draft_revenue_item_line_num = x_draft_revenue_item_line_num
1381 AND rdl.set_of_books_id = NVL(TO_NUMBER( SUBSTRB( USERENV('CLIENT_INFO'), 45,10) ), -99);
1382
1383 -- Modified below query for bug 6696736
1384 SELECT sum(nvl(erdl.amount,0))
1385 INTO erdl_amt
1386 FROM pa_mc_cust_event_rdl_all erdl,
1387 pa_cust_event_rdl_all cerdl
1388 -- pa_implementations imp -- Fix for Perf Bug 2695336
1389 WHERE cerdl.project_id = x_project_id
1390 AND cerdl.draft_revenue_num = x_draft_revenue_num
1391 AND cerdl.draft_revenue_item_line_num = x_draft_revenue_item_line_num
1392 AND erdl.set_of_books_id = NVL(TO_NUMBER( SUBSTRB( USERENV('CLIENT_INFO'), 45,10) ), -99)
1393 AND cerdl.project_id = erdl.project_id
1394 AND cerdl.event_num = erdl.event_num
1395 AND NVL(cerdl.task_id,-99) = NVL(erdl.task_id,-99)
1396 AND cerdl.line_num = erdl.line_num;
1397
1398
1399 RETURN (nvl(rdl_amt,0) + nvl(erdl_amt,0));
1400
1401 EXCEPTION WHEN OTHERS THEN
1402 RAISE;
1403
1404 END sum_mc_cust_rdl_erdl;
1405
1406 -------------------------------------------------------------
1407
1408
1409 FUNCTION orgid( x_project_id IN NUMBER) RETURN NUMBER
1410 IS
1411 orgid NUMBER;
1412 BEGIN
1413 SELECT org_id
1414 INTO orgid
1415 FROM pa_projects_all
1416 WHERE project_id = x_project_id;
1417
1418 RETURN orgid;
1419
1420 EXCEPTION WHEN OTHERS THEN
1421 RAISE;
1422 END orgid;
1423 -------------------------------------------------------------
1424
1425 FUNCTION get_wo_factor(x_project_id IN NUMBER,
1426 x_di_num IN NUMBER,
1427 x_di_num_org IN NUMBER ) RETURN NUMBER
1428 IS
1429 wo_factor number := 0;
1430 BEGIN
1431 SELECT (inv.amount/invorg.amount)
1432 INTO wo_factor
1433 FROM pa_draft_invoice_items invorg,
1434 pa_draft_invoice_items inv
1435 WHERE inv.project_id = x_project_id
1436 AND inv.draft_invoice_num = x_di_num
1437 AND inv.line_num = 1
1438 AND invorg.project_id = inv.project_id
1439 AND invorg.draft_invoice_num = x_di_num_org
1440 AND invorg.line_num = inv.line_num;
1441
1442 RETURN (wo_factor);
1443
1444 EXCEPTION
1445 WHEN NO_DATA_FOUND then
1446 pa_mc_currency_pkg.raise_error('PA_MRC_WO_FACTOR_ERROR', 'PAMRCDIS:9');
1447
1448 WHEN OTHERS THEN
1449 RAISE;
1450 END get_wo_factor;
1451
1452 -------------------------------------------------------------
1453
1454 FUNCTION get_cancel_flag( x_project_id IN NUMBER,
1455 x_di_num IN NUMBER ) RETURN VARCHAR2
1456 IS
1457 cancel_flag VARCHAR2(2);
1458 BEGIN
1459 SELECT NVL(canceled_flag,'N')
1460 INTO cancel_flag
1461 FROM pa_draft_invoices
1462 WHERE project_id = x_project_id
1463 AND draft_invoice_num = x_di_num;
1464
1465 RETURN cancel_flag;
1466
1467 EXCEPTION
1468 WHEN NO_DATA_FOUND then
1469 raise_error('PA_MRC_CANCEL_FLAG_ERROR','PAMRCDIS:8');
1470
1471 WHEN OTHERS THEN
1472 RAISE;
1473 END get_cancel_flag;
1474
1475 -------------------------------------------------------------
1476
1477 FUNCTION get_invoice_action RETURN VARCHAR2
1478 IS
1479 BEGIN
1480 IF NVL(pa_mc_currency_pkg.Invoice_Action,'NONE') = 'CANCEL' then
1481 RETURN ('Y');
1482 ELSE RETURN ('N');
1486 -------------------------------------------------------------
1483 END IF;
1484 END;
1485
1487
1488 FUNCTION get_rtn_amount( x_project_id IN NUMBER,
1489 x_di_num IN NUMBER,
1490 x_rtn_pcnt IN NUMBER,
1491 x_sob_id IN NUMBER ) RETURN NUMBER
1492 IS
1493 rtn_amount NUMBER := 0;
1494 BEGIN
1495 SELECT sum(mii.amount)
1496 INTO rtn_amount
1497 FROM pa_mc_draft_inv_items mii,
1498 pa_draft_invoice_items ii
1499 WHERE ii.project_id = x_project_id
1500 AND ii.draft_invoice_num = x_di_num
1501 AND ii.invoice_line_type <> 'RETENTION'
1502 AND mii.set_of_books_id = x_sob_id
1503 AND mii.project_id = x_project_id
1504 AND mii.draft_invoice_num = x_di_num
1505 AND mii.line_num = ii.line_num;
1506
1507 RETURN (NVL(rtn_amount,0)*x_rtn_pcnt/100);
1508
1509 EXCEPTION
1510 /* If 0 returned, then the trigger should raise error */
1511
1512 WHEN OTHERS THEN
1513 RAISE ;
1514 END get_rtn_amount;
1515
1516 -------------------------------------------------------------
1517
1518 PROCEDURE raise_error(x_msg IN VARCHAR2,
1519 x_module IN VARCHAR2,
1520 x_currency IN VARCHAR2 )
1521 IS
1522
1523 BEGIN
1524 fnd_message.set_name('PA', x_msg);
1525 fnd_message.set_token('MODULE', x_module);
1526 IF (x_currency IS NOT NULL) then
1527 fnd_message.set_token('CURRENCY', x_currency);
1528 END IF;
1529 raise_application_error(-20009,fnd_message.get);
1530
1531 END raise_error;
1532
1533 -------------------------------------------------------------
1534
1535 /*------------------------------ ins_mc_txn_interface_all ----------------------*/
1536 /* This procedure will populate the Pa_mc_txn_interface_all table for a invoice */
1537 /* distribution line pulled over from AP . First it will look for the data in */
1538 /* the AP MRC sub-table otherwise it will get the rates from GL based on the */
1539 /* Invoice Date and compute the amounts and populate the pa_mc_txn_interface_all*/
1540 /* table */
1541 /*------------------------------------------------------------------------------*/
1542
1543 /* Changed the IN parameter names and local variables
1544 from p_vendor_id to p_system_reference1,
1545 p_invoice_id to p_system_reference2,
1546 p_dist_line_num to p_system_reference3,
1547 p_invoice_payment_id to p_system_reference4
1548 */
1549
1550 /*
1551 PROCEDURE ins_mc_txn_interface_all(
1552
1553 p_vendor_id IN NUMBER,
1554 p_invoice_id IN NUMBER,
1555 p_dist_line_num IN NUMBER,
1556 p_interface_id IN NUMBER,
1557 p_transaction_source IN VARCHAR2,
1558 p_invoice_payment_id IN NUMBER DEFAULT NULL) IS
1559 */
1560
1561 PROCEDURE ins_mc_txn_interface_all(
1562 p_system_reference1 IN NUMBER,
1563 p_system_reference2 IN NUMBER,
1564 p_system_reference3 IN NUMBER,
1565 p_system_reference4 IN VARCHAR2 ,
1566 p_interface_id IN NUMBER,
1567 p_transaction_source IN VARCHAR2,
1568 p_acct_evt_id IN NUMBER DEFAULT NULL) --pricing changes, added param p_acct_evt_id
1569
1570 IS
1571
1572
1573 l_old_stack VARCHAR2(2000);
1574 l_err_code NUMBER;
1575 l_sob NUMBER;
1576 l_org_id NUMBER;
1577 l_txn_raw_cost NUMBER;
1578 l_raw_cost NUMBER;
1579 l_burdened_cost NUMBER;
1580 l_currency VARCHAR2(30);
1581 l_txn_interface_id NUMBER;
1582 l_exchange_rate NUMBER;
1583 l_denominator_rate NUMBER;
1584 l_numerator_rate NUMBER;
1585 l_exchange_date DATE;
1586 l_exchange_rate_type VARCHAR2(30);
1587
1588 -- Bug 1131476, creating new variables to pass to get_ap_rate API
1589 l_ap_exchange_date DATE;
1590 l_ap_exchange_rate_type VARCHAR2(30);
1591 l_ap_exchange_rate NUMBER;
1592 l_result_code VARCHAR2(15);
1593
1594 -- Added new variables for get_po_rate API
1595 l_po_exchange_date DATE;
1596 l_po_exchange_rate_type VARCHAR2(30);
1597 l_po_exchange_rate NUMBER;
1598
1599 --Added for performance changes
1600 l_rcv_txn_id NUMBER;
1601 l_po_dist_id NUMBER;
1602 l_inv_pay_id NUMBER;
1603
1604 BEGIN
1605
1606 l_old_stack := PAAPIMP_PKG.G_err_stack;
1607 PAAPIMP_PKG.G_err_code := 0;
1608 PAAPIMP_PKG.G_err_stack := PAAPIMP_PKG.G_err_stack||'->PA_MC_CURRENCY_PKG.ins_mc_txn_interface_all';
1609 PAAPIMP_PKG.G_err_stage := ' Insert into pa_mc_txn_interface_all';
1610
1611 PAAPIMP_PKG.write_log(LOG, PAAPIMP_PKG.G_err_stack);
1612 PAAPIMP_PKG.write_log(LOG, 'Inserting transaction source: '||p_transaction_source||
1613 'system_ref2 : ' ||p_system_reference2||
1614 'system_ref3 : ' ||p_system_reference3 ||
1615 'system_ref4: ' ||p_system_reference4||
1616 ' into pa_mc_txn_interface_all......');
1617
1618 PAAPIMP_PKG.G_err_stage := 'GET ORG_ID IN INS_MC_TXN_INTERFACE';
1622 from pa_implementations;
1619 --select NVL(org_id,-99)
1620 select org_id
1621 into l_org_id
1623
1624 /**
1625
1626 PAAPIMP_PKG.G_err_stage := 'CALLING FUNCTIONAL_CURRENCY IN INS_MC_TXN_INTERFACE';
1627 l_currency := pa_mc_currency_pkg.functional_currency(l_org_id);
1628 **/
1629
1630 PAAPIMP_PKG.G_err_stage := 'CALLING SET_OF_BOOKS IN INS_MC_TXN_INTERFACE';
1631 l_sob := pa_mc_currency_pkg.set_of_books();
1632
1633 PAAPIMP_PKG.G_err_stage := 'GET TXN_INTERFACE_ID and BURDEN_COST IN INS_MC_TXN_INTERFACE';
1634
1635 -- change the IF conditions to be based on p_system_refernce4
1636 -- IF p_invoice_payment_id IS NULL THEN
1637
1638 IF p_system_reference4 IS NULL THEN
1639
1640 PAAPIMP_PKG.write_log (LOG,'getting denom information for invoices IN INS_MC_TXN_INTERFACE');
1641
1642 SELECT txn_interface_id,
1643 denom_burdened_cost,
1644 denom_raw_cost,
1645 --removed, should get from MRC table acct_rate_type,
1646 --removed, should get from MRC table acct_exchange_rate,
1647 denom_currency_code
1648 into l_txn_interface_id,
1649 l_burdened_cost,
1650 l_txn_raw_cost,
1651 --removed l_exchange_rate_type,
1652 --removed l_exchange_rate,
1653 l_currency
1654 from pa_transaction_interface_all
1655 where interface_id = p_interface_id
1656 and cdl_system_reference1 = to_char(p_system_reference1)
1657 and cdl_system_reference2 = to_char(p_system_reference2)
1658 and cdl_system_reference3 = to_char(p_system_reference3)
1659 and transaction_source||'' = p_transaction_source;
1660
1661 --Change IF condition to be based on p_system_reference4
1662 --ELSIF p_invoice_payment_id IS NOT NULL THEN
1663
1664 ELSIF p_system_reference4 IS NOT NULL THEN
1665
1666 PAAPIMP_PKG.write_log (LOG,'getting denom information for non-invoices IN INS_MC_TXN_INTERFACE');
1667
1668 SELECT txn_interface_id,
1669 denom_burdened_cost,
1670 denom_raw_cost,
1671 denom_currency_code
1672 into l_txn_interface_id,
1673 l_burdened_cost,
1674 l_txn_raw_cost,
1675 l_currency
1676 from pa_transaction_interface_all
1677 where interface_id = p_interface_id
1678 and cdl_system_reference1 = to_char(p_system_reference1)
1679 and cdl_system_reference2 = to_char(p_system_reference2)
1680 and cdl_system_reference3 = to_char(p_system_reference3)
1681 and cdl_system_reference4 = p_system_reference4
1682 and transaction_source||''= p_transaction_source;
1683
1684 END IF;
1685
1686 PAAPIMP_PKG.write_log(LOG,'txn interface id is:'||l_txn_interface_id||
1687 'denom_burdened_cost is:'||l_burdened_cost||
1688 'denom_raw_cost is:'||l_txn_raw_cost||
1689 'denom_currency_code is:'||l_currency);
1690
1691
1692 FOR i IN 1..g_rsob_tab.COUNT
1693
1694 LOOP
1695
1696 BEGIN
1697
1698 --instead of using invoice_payment_id, use system_reference4
1699 IF (p_system_reference4 IS NULL or
1700 p_transaction_source='AP VARIANCE') THEN
1701
1702 PAAPIMP_PKG.G_err_stage := 'CALLING GET_AP_RATE IN INS_MC_TXN_INTERFACE';
1703
1704 pa_mc_currency_pkg.get_ap_rate(x_invoice_id => p_system_reference2,
1705 x_line_num => p_system_reference3,
1706 x_system_reference4 => p_system_reference4,
1707 x_transaction_source => p_transaction_source,
1708 x_sob => g_rsob_tab(i).rsob_id,
1709 x_exchange_rate => l_ap_exchange_rate,
1710 x_exchange_date => l_ap_exchange_date,
1711 x_exchange_rate_type => l_ap_exchange_rate_type,
1712 x_amount => l_raw_cost,
1713 x_err_stack => PAAPIMP_PKG.G_err_stack,
1714 x_err_stage => PAAPIMP_PKG.G_err_stage,
1715 x_err_code => l_err_code);
1716
1717 END IF;
1718
1719 IF (l_err_code <> 0 AND l_err_code <> -1403 AND l_err_code <> 100)THEN
1720
1721 pa_mc_currency_pkg.raise_error('PA_MRC_AP_RATES','PAMRCDIS:4',g_rsob_tab(i).rcurrency_code);
1722
1723 END IF;
1724
1725
1726 IF (p_system_reference4 IS NOT NULL AND
1727 p_transaction_source IN ('PO RECEIPT','PO RECEIPT NRTAX', 'PO RECEIPT PRICE ADJ'
1728 ,'PO RECEIPT NRTAX PRICE ADJ')) THEN -- pricing changes
1729
1730 PAAPIMP_PKG.G_err_stage := 'CALLING GET_PO_RATE IN INS_MC_TXN_INTERFACE';
1731
1732 pa_mc_currency_pkg.get_po_rate(x_po_dist_id => p_system_reference3,
1733 x_rcv_txn_id => p_system_reference4,
1734 x_transaction_source => p_transaction_source,
1738 x_exchange_rate_type => l_po_exchange_rate_type,
1735 x_sob => g_rsob_tab(i).rsob_id,
1736 x_exchange_rate => l_po_exchange_rate,
1737 x_exchange_date => l_po_exchange_date,
1739 x_amount => l_raw_cost,
1740 x_err_stack => PAAPIMP_PKG.G_err_stack,
1741 x_err_stage => PAAPIMP_PKG.G_err_stage,
1742 x_err_code => l_err_code,
1743 x_acct_evt_id => p_acct_evt_id);
1744
1745 END IF;
1746
1747 IF (l_err_code <> 0 AND l_err_code <> -1403 AND l_err_code <> 100)THEN
1748
1749 pa_mc_currency_pkg.raise_error('PA_MRC_PO_RATES','PAMRCDIS:4',g_rsob_tab(i).rcurrency_code);
1750
1751 END IF;
1752
1753 --instead of using invoice_payment_id, use transaction_source to specify processing AP DISCOUNTS
1754 IF ( l_err_code = -1403 OR l_err_code = 100 OR p_transaction_source='AP DISCOUNTS')
1755
1756 THEN -- if no AP MRC rates then get rates from GL based on invoice date
1757
1758 PAAPIMP_PKG.G_err_stage := 'GET EXCHANGE_DATE and INVOICE_DATE IN INS_MC_TXN_INTERFACE';
1759 PAAPIMP_PKG.write_log(LOG,'getting exchange date for transaction_source: '||p_transaction_source);
1760 PAAPIMP_PKG.write_log(LOG,'sys_ref2 is: '||p_system_reference2||
1761 'sys_ref3 is: '||p_system_reference3||
1762 'sys_ref4 is: '||p_system_reference4);
1763
1764 IF (p_system_reference4 IS NULL OR p_transaction_source = 'AP VARIANCE') THEN
1765
1766 SELECT nvl(b.exchange_date,a.invoice_date)
1767 INTO l_exchange_date
1768 FROM ap_invoices_all a,
1769 ap_invoice_distributions_all b
1770 WHERE a.invoice_id = p_system_reference2
1771 AND a.invoice_id = b.invoice_id
1772 AND b.distribution_line_number = p_system_reference3;
1773
1774 ELSIF p_system_reference4 IS NOT NULL
1775 AND p_transaction_source = 'AP DISCOUNTS' THEN
1776
1777 --performance change
1778 l_inv_pay_id := to_number(p_system_reference4);
1779
1780 SELECT nvl(b.exchange_date,a.invoice_date)
1781 INTO l_exchange_date
1782 FROM ap_invoices_all a,
1783 ap_invoice_distributions_all b,
1784 ap_invoice_payments c
1785 WHERE c.invoice_payment_id = l_inv_pay_id
1786 AND c.invoice_id = p_system_reference2
1787 AND c.invoice_id = b.invoice_id
1788 AND a.invoice_id = b.invoice_id
1789 AND b.distribution_line_number = p_system_reference3;
1790
1791 ELSIF p_system_reference4 IS NOT NULL
1792 AND p_transaction_source IN ('PO RECEIPT','PO RECEIPT NRTAX','PO RECEIPT PRICE ADJ'
1793 ,'PO RECEIPT NRTAX PRICE ADJ') THEN -- pricing changes
1794
1795 --Performance change
1796 l_rcv_txn_id := to_number(p_system_reference4);
1797
1798 SELECT nvl(a.currency_conversion_date,a.transaction_date)
1799 INTO l_exchange_date
1800 FROM rcv_transactions a
1801 WHERE a.po_distribution_id = p_system_reference3
1802 AND a.transaction_id = l_rcv_txn_id;
1803
1804 END IF;
1805
1806 PAAPIMP_PKG.write_log(LOG,'primaray set of books id: '||l_sob||
1807 'reporting set of books id: '||g_rsob_tab(i).rsob_id||
1808 'exchange date: '||l_exchange_date||
1809 'currency: '||l_currency||
1810 'exchange type: '||l_exchange_rate_type||
1811 'exchange rate: '||l_exchange_rate);
1812
1813
1814 PAAPIMP_PKG.G_err_stage := 'CALLING GET_RATE IN INS_MC_TXN_INTERFACE 1';
1815 gl_mc_currency_pkg.get_rate( p_primary_set_of_books_id => l_sob,
1816 p_reporting_set_of_books_id => g_rsob_tab(i).rsob_id,
1817 p_trans_date => l_exchange_date,
1818 p_trans_currency_code => l_currency,
1819 p_trans_conversion_type => l_exchange_rate_type,
1820 p_trans_conversion_date => l_exchange_date,
1821 p_trans_conversion_rate => l_exchange_rate,
1822 p_application_id => 275,
1823 p_org_id => l_org_id,
1824 p_fa_book_type_code => NULL,
1825 p_je_source_name => NULL,
1829 p_numerator_rate => l_numerator_rate);
1826 p_je_category_name => NULL,
1827 p_result_code => l_result_code,
1828 p_denominator_rate => l_denominator_rate,
1830 PAAPIMP_PKG.write_log(LOG,'after get rate from GL');
1831
1832 PAAPIMP_PKG.G_err_stage := 'CALLING CURRROUND IN INS_MC_TXN_INTERFACE PROCEDURE';
1833 IF (l_exchange_rate_type = 'User') THEN
1834
1835 l_burdened_cost := pa_mc_currency_pkg.CurrRound
1836 ((l_burdened_cost *l_exchange_rate), g_rsob_tab(i).rcurrency_code);
1837
1838 l_raw_cost := pa_mc_currency_pkg.CurrRound
1839 ((l_txn_raw_cost*l_exchange_rate),g_rsob_tab(i).rcurrency_code);
1840
1841 ELSE
1842
1843 l_burdened_cost := pa_mc_currency_pkg.CurrRound
1844 (((l_burdened_cost/l_denominator_rate)*l_numerator_rate),
1845 g_rsob_tab(i).rcurrency_code);
1846
1847 l_raw_cost := pa_mc_currency_pkg.CurrRound
1848 (((l_txn_raw_cost/l_denominator_rate)*l_numerator_rate),
1849 g_rsob_tab(i).rcurrency_code);
1850 END IF;
1851
1852 ELSE -- rates are found
1853
1854 /* Getting the conversion rate for burdened cost */
1855
1856 /* IF p_transaction_source <> 'PO RECEIPT' THEN -- Modified for Bug#3059995 */
1857 IF p_transaction_source NOT IN ( 'PO RECEIPT','PO RECEIPT NRTAX','PO RECEIPT PRICE ADJ'
1858 ,'PO RECEIPT NRTAX PRICE ADJ') THEN -- pricing changes
1859
1860 l_exchange_rate_type := l_ap_exchange_rate_type;
1861 l_exchange_rate := l_ap_exchange_rate;
1862 l_exchange_date := l_ap_exchange_date;
1863
1864 ELSIF p_transaction_source IN ( 'PO RECEIPT','PO RECEIPT NRTAX','PO RECEIPT PRICE ADJ'
1865 ,'PO RECEIPT NRTAX PRICE ADJ') THEN
1866
1867 l_exchange_rate_type := l_po_exchange_rate_type;
1868 l_exchange_rate := l_po_exchange_rate;
1869 l_exchange_date := l_po_exchange_date;
1870
1871 END IF;
1872
1873 PAAPIMP_PKG.G_err_stage := 'CALLING GET_RATE IN INS_MC_TXN_INTERFACE 2';
1874 gl_mc_currency_pkg.get_rate(p_primary_set_of_books_id => l_sob,
1875 p_reporting_set_of_books_id => g_rsob_tab(i).rsob_id,
1876 p_trans_date => l_exchange_date,
1877 p_trans_currency_code => l_currency,
1878 p_trans_conversion_type => l_exchange_rate_type,
1879 p_trans_conversion_date => l_exchange_date,
1880 p_trans_conversion_rate => l_exchange_rate,
1881 p_application_id => 275,
1882 p_org_id => l_org_id,
1883 p_fa_book_type_code => NULL,
1884 p_je_source_name => NULL,
1885 p_je_category_name => NULL,
1886 p_result_code => l_result_code,
1887 p_denominator_rate => l_denominator_rate,
1888 p_numerator_rate => l_numerator_rate);
1889
1890 IF (l_exchange_rate_type = 'User') THEN
1891
1892 l_burdened_cost := pa_mc_currency_pkg.CurrRound
1893 ((l_burdened_cost *l_exchange_rate),
1894 g_rsob_tab(i).rcurrency_code);
1895 ELSE
1896
1897 l_burdened_cost := pa_mc_currency_pkg.CurrRound
1898 (((l_burdened_cost/l_denominator_rate)*l_numerator_rate),
1899 g_rsob_tab(i).rcurrency_code);
1900
1901 END IF;
1902
1903 END IF;
1904
1905 PAAPIMP_PKG.write_log(LOG,'before inserting into pa_mc_txn_interface_all table');
1906
1907 PAAPIMP_PKG.G_err_stage := 'INSERT RECORD INTO PA_MC_TXN_INTERFACE_ALL';
1908 PAAPIMP_PKG.write_log(LOG,'insert SOBID:'||g_rsob_tab(i).rsob_id||
1909 'insert txn_interface_id:'||l_txn_interface_id||
1910 'insert raw_cost:'||l_raw_cost||
1911 'exchange rate:'||l_exchange_rate);
1912
1913 INSERT INTO pa_mc_txn_interface_all (
1914 set_of_books_id ,
1915 txn_interface_id ,
1916 raw_cost ,
1917 raw_cost_rate ,
1918 burdened_cost ,
1919 burdened_cost_rate ,
1920 currency_code ,
1921 exchange_rate ,
1922 conversion_date )
1923 VALUES (
1924 g_rsob_tab(i).rsob_id,
1925 l_txn_interface_id ,
1926 l_raw_cost ,
1927 NULL ,
1931 l_exchange_rate ,
1928 l_burdened_cost ,
1929 NULL ,
1930 g_rsob_tab(i).rcurrency_code ,
1932 l_exchange_date );
1933
1934 END;
1935 END LOOP; -- End of Loop for the cursor c_reporting_sob
1936 PAAPIMP_PKG.write_log(LOG,'after inserting');
1937
1938 PAAPIMP_PKG.G_err_stack := l_old_stack;
1939
1940 EXCEPTION
1941 WHEN OTHERS THEN
1942 PAAPIMP_PKG.G_err_stack := l_old_stack;
1943 PAAPIMP_PKG.G_err_code := SQLCODE;
1944 PAAPIMP_PKG.G_TRANSACTION_STATUS_CODE := 'R';
1945 PAAPIMP_PKG.G_TRANSACTION_REJECTION_CODE := 'PA_INSERT_MRC_FAILED';
1946 PAAPIMP_PKG.write_log(LOG, 'Inserting system reference2: ' || to_char(p_system_reference2) ||
1947 ' system reference3: ' || to_char(p_system_reference3) ||
1948 ' into pa_mc_txn_interface_all failed in stage: ' || PAAPIMP_PKG.G_err_stage);
1949 PAAPIMP_PKG.write_log(LOG, substr(SQLERRM, 1, 200));
1950
1951 END ins_mc_txn_interface_all;
1952 -------------------------------------------------------------------------------
1953
1954 PROCEDURE get_ccdl_tp_amts( x_exp_item_id IN NUMBER,
1955 x_set_of_books_id IN NUMBER,
1956 x_transfer_price OUT NOCOPY NUMBER,
1957 x_tp_exchange_rate OUT NOCOPY NUMBER,
1958 x_tp_exchange_date OUT NOCOPY DATE,
1959 x_tp_rate_type OUT NOCOPY VARCHAR2,
1960 x_err_stack IN OUT NOCOPY VARCHAR2,
1961 x_err_stage IN OUT NOCOPY VARCHAR2,
1962 x_err_code OUT NOCOPY NUMBER)
1963
1964 IS
1965
1966 l_old_stack VARCHAR2(2000);
1967
1968 Cursor C_TP_REC IS
1969 SELECT amount,
1970 acct_tp_exchange_rate,
1971 acct_tp_rate_date,
1972 acct_tp_rate_type
1973 FROM pa_mc_cc_dist_lines_all
1974 WHERE set_of_books_id = x_set_of_books_id
1975 AND expenditure_item_id = x_exp_item_id
1976 AND line_num = ( select max(line_num)
1977 from PA_CC_DIST_LINES_ALL
1978 where expenditure_item_id = x_exp_item_id
1979 and line_type = 'BL' );
1980
1981 BEGIN
1982
1983 l_old_stack := x_err_stack;
1984 x_err_code := 0;
1985 x_err_stack := x_err_stack ||'->PA_MC_CURRENCY_PKG.get_ccdl_tp_amts';
1986 x_err_stage := ' Select from pa_mc_cc_dist_lines_all';
1987 x_transfer_price := NULL;
1988
1989 open C_TP_REC ;
1990
1991 fetch C_TP_REC into
1992 x_transfer_price,
1993 x_tp_exchange_rate,
1994 x_tp_exchange_date,
1995 x_tp_rate_type ;
1996
1997 close C_TP_REC;
1998
1999 x_err_stack := l_old_stack;
2000
2001 Exception
2002 When Others Then
2003 x_transfer_price := Null;
2004 x_tp_exchange_rate := Null;
2005 x_tp_exchange_date := Null;
2006 x_tp_rate_type := Null;
2007 x_err_code := sqlcode;
2008 Raise;
2009
2010 END get_ccdl_tp_amts;
2011 --------------------------------------------------------------------------------
2012
2013 PROCEDURE get_invdtl_tp_amts( x_exp_item_id IN NUMBER,
2014 x_set_of_books_id IN NUMBER,
2015 x_transfer_price OUT NOCOPY NUMBER,
2016 x_tp_exchange_rate OUT NOCOPY NUMBER,
2017 x_tp_exchange_date OUT NOCOPY DATE,
2018 x_tp_rate_type OUT NOCOPY VARCHAR2,
2019 x_err_stack IN OUT NOCOPY VARCHAR2,
2020 x_err_stage IN OUT NOCOPY VARCHAR2,
2021 x_err_code OUT NOCOPY NUMBER)
2022
2023 IS
2024
2025 Cursor C_TP_REC IS
2026 SELECT SUM(bill_amount),
2027 Min(acct_exchange_rate),
2028 Min(acct_rate_type),
2029 Min(acct_rate_date)
2030 FROM pa_mc_draft_inv_details_all
2031 WHERE set_of_books_id = x_set_of_books_id
2032 AND draft_invoice_detail_id in
2033 (select draft_invoice_detail_id
2034 from pa_draft_invoice_details_all
2035 where expenditure_item_id = x_exp_item_id) ;
2036
2037 l_old_stack Varchar2(2000);
2038
2039 BEGIN
2040
2041 l_old_stack := x_err_stack;
2042 x_err_code := 0;
2043 x_err_stack := x_err_stack ||'->PA_MC_CURRENCY_PKG.get_invdtl_tp_amts';
2044 x_err_stage := ' Select from pa_mc_draft_inv_details_all';
2045 x_transfer_price := NULL;
2046
2047 OPEN C_TP_REC;
2048
2049 FETCH C_TP_REC
2050 INTO x_transfer_price,
2051 x_tp_exchange_rate,
2052 x_tp_rate_type,
2053 x_tp_exchange_date ;
2054
2055 CLOSE C_TP_REC;
2056
2057 x_err_stack := l_old_stack;
2058
2059 Exception
2060 When Others Then
2061 x_transfer_price := Null;
2062 x_tp_exchange_rate := Null;
2063 x_tp_exchange_date := Null;
2064 x_tp_rate_type := Null;
2065 Raise;
2066
2070 PROCEDURE get_po_rate( x_po_dist_id IN NUMBER,
2067 END get_invdtl_tp_amts;
2068
2069
2071 x_rcv_txn_id IN VARCHAR2,
2072 x_transaction_source IN VARCHAR2,
2073 x_sob IN NUMBER,
2074 x_exchange_rate OUT NOCOPY NUMBER,
2075 x_exchange_date OUT NOCOPY DATE,
2076 x_exchange_rate_type OUT NOCOPY VARCHAR2,
2077 x_amount OUT NOCOPY NUMBER,
2078 x_err_stack IN OUT NOCOPY VARCHAR2,
2079 x_err_stage IN OUT NOCOPY VARCHAR2,
2080 x_err_code OUT NOCOPY NUMBER,
2081 x_acct_evt_id IN NUMBER DEFAULT NULL)
2082 IS
2083
2084 l_old_stack VARCHAR2(2000);
2085 l_rcv_txn_id NUMBER;
2086
2087 BEGIN
2088
2089 l_old_stack := x_err_stack;
2090 x_err_code := 0;
2091 x_err_stack := x_err_stack ||'->PA_MC_CURRENCY_PKG.get_po_rate';
2092 x_err_stage := ' Select from rcv_mc_rec_sub_ledger';
2093
2094 l_rcv_txn_id := to_number(x_rcv_txn_id);
2095
2096 -- To get the porates logic used : FC:Foreign curr, PC:Pri Curr,Rep:Reporting
2097 -- If foreign curr inv, then po.amount = FC amount, po.base_Amount = PC amt
2098 -- and mc.base_amount = Rep Amount, mc.amount = FC or PC amt.
2099 -- and exch rate is from FC to Rep, not from PC to rep.
2100 -- IF FC = Rep Curr, then the mc rec for that rep SOB has
2101 -- mc.amount = FC amount, all other amount and exchange cols are NULL.
2102 -- IF PC inv, then base_amount cols are null in both tables and exch rates
2103 -- on mc recs are correct.
2104
2105 IF x_transaction_source IN ('PO RECEIPT','PO RECEIPT PRICE ADJ') THEN -- pricing changes, added 'PO RECEIPT PRICE ADJ'
2106
2107 /* for the amount that we are selecting, we need to see whether the transaction is an
2108 EXPENSE or RECEIVING transactions. EXPENSE means we take the positive value of dr column
2109 minus the tax amount while RECEIVNG transaction means it is a return, so we take the
2110 negative of the cr column plus the tax amount */
2111
2112 SELECT mcsub.CURRENCY_CONVERSION_RATE exchange_rate, --Bug#3218750
2113 -- Bug#3218750 decode(NVL(rcvsub.ACCOUNTED_DR,0),0,mcsub.CURRENCY_CONVERSION_RATE,
2114 -- (mcsub.ACCOUNTED_DR/rcvsub.accounted_dr )) exchange_rate,
2115 nvl(mcsub.CURRENCY_CONVERSION_DATE,mctxn.CURRENCY_CONVERSION_DATE) exchange_date,
2116 nvl(mctxn.CURRENCY_CONVERSION_TYPE,rcvtxn.CURRENCY_CONVERSION_TYPE) excahnge_rate_type,
2117 decode(rcvtxn.transaction_type,
2118 'RETURN TO RECEIVING',(-nvl(mcsub.accounted_cr,0)+nvl(mcsub.accounted_nr_tax,0)),
2119 'RETURN TO VENDOR',(-nvl(mcsub.accounted_cr,0)+nvl(mcsub.accounted_nr_tax,0)),
2120 (nvl(mcsub.ACCOUNTED_DR,0)-nvl(mcsub.accounted_nr_tax,0))) amount -- Bug 40571541 Added Nvl() clause for accounted_cr or accounted_cr as only one can be populated at a time
2121 INTO x_exchange_rate,
2122 x_exchange_date,
2123 x_exchange_rate_type,
2124 x_amount
2125 FROM rcv_transactions rcvtxn,
2126 rcv_receiving_sub_ledger rcvsub,
2127 rcv_mc_rec_sub_ledger mcsub,
2128 rcv_mc_transactions mctxn,
2129 po_distributions po_dist
2130 WHERE rcvtxn.transaction_id = l_rcv_txn_id
2131 AND rcvtxn.po_distribution_id = x_po_dist_id
2132 AND rcvtxn.po_distribution_id = po_dist.po_distribution_id
2133 AND po_dist.code_combination_id = rcvsub.code_combination_id
2134 AND po_dist.code_combination_id = mcsub.code_combination_id
2135 AND rcvsub.actual_flag = 'A'
2136 AND mcsub.actual_flag = 'A'
2137 AND rcvtxn.transaction_id = rcvsub.rcv_transaction_id
2138 AND rcvtxn.transaction_id = mctxn.transaction_id
2139 AND rcvtxn.transaction_id = mcsub.RCV_TRANSACTION_ID
2140 AND mctxn.SET_OF_BOOKS_ID = x_sob
2141 AND mcsub.SET_OF_BOOKS_ID = x_sob
2142 AND rcvsub.accounting_event_id = nvl(x_acct_evt_id, rcvsub.accounting_event_id) -- pricing changes
2143 AND mcsub.accounting_event_id = nvl(x_acct_evt_id, mcsub.accounting_event_id); -- pricing changes
2144
2145 ELSIF x_transaction_source IN ('PO RECEIPT NRTAX', 'PO RECEIPT NRTAX PRICE ADJ') THEN -- pricing changes
2146
2147 /* If it is a tax line, we want to take the positive amount of the tax column if the
2148 transaction is an 'EXPENSE'. If the transaction is a 'RECEIVING', then we take the
2149 negative value of the tax column. */
2150
2151 SELECT mcsub.CURRENCY_CONVERSION_RATE exchange_rate, --Bug#3218750
2152 -- Bug#3218750 decode(NVL(rcvsub.ACCOUNTED_DR,0),0,mcsub.CURRENCY_CONVERSION_RATE,
2153 -- Bug#3218750 (mcsub.ACCOUNTED_DR/rcvsub.accounted_dr)) exchange_rate,
2154 nvl(mcsub.CURRENCY_CONVERSION_DATE,mctxn.CURRENCY_CONVERSION_DATE) exchange_date,
2155 nvl(mctxn.CURRENCY_CONVERSION_TYPE,rcvtxn.CURRENCY_CONVERSION_TYPE) excahnge_rate_type,
2156 decode(rcvtxn.transaction_type,
2157 'RETURN TO RECEIVING',nvl(-mcsub.accounted_nr_tax,0),
2158 'RETURN TO VENDOR',nvl(-mcsub.accounted_nr_tax,0),
2159 nvl(mcsub.accounted_nr_tax,0)) amount /* Bug 4292891 */
2160 INTO x_exchange_rate,
2164 FROM rcv_transactions rcvtxn,
2161 x_exchange_date,
2162 x_exchange_rate_type,
2163 x_amount
2165 rcv_receiving_sub_ledger rcvsub,
2166 rcv_mc_rec_sub_ledger mcsub,
2167 rcv_mc_transactions mctxn,
2168 po_distributions po_dist
2169 WHERE rcvtxn.transaction_id = l_rcv_txn_id
2170 AND rcvtxn.po_distribution_id = x_po_dist_id
2171 AND rcvtxn.po_distribution_id = po_dist.po_distribution_id
2172 AND po_dist.code_combination_id = rcvsub.code_combination_id
2173 AND po_dist.code_combination_id = mcsub.code_combination_id
2174 AND rcvsub.actual_flag = 'A'
2175 AND mcsub.actual_flag = 'A'
2176 AND rcvtxn.transaction_id = rcvsub.rcv_transaction_id
2177 AND rcvtxn.transaction_id = mctxn.transaction_id
2178 AND rcvtxn.transaction_id = mcsub.RCV_TRANSACTION_ID
2179 AND mctxn.SET_OF_BOOKS_ID = x_sob
2180 AND mcsub.SET_OF_BOOKS_ID = x_sob
2181 AND rcvsub.accounting_event_id = nvl(x_acct_evt_id, rcvsub.accounting_event_id) -- pricing changes
2182 AND mcsub.accounting_event_id = nvl(x_acct_evt_id, mcsub.accounting_event_id); -- pricing changes
2183 END IF;
2184
2185 x_err_stack := l_old_stack;
2186
2187 EXCEPTION
2188 WHEN NO_DATA_FOUND THEN
2189 x_err_code := SQLCODE;
2190 x_exchange_rate := Null;
2191 x_exchange_date := Null;
2192 x_exchange_rate_type := Null;
2193 x_amount := Null;
2194 WHEN OTHERS THEN
2195 x_exchange_rate := Null;
2196 x_exchange_date := Null;
2197 x_exchange_rate_type := Null;
2198 x_amount := Null;
2199 RAISE;
2200
2201 END get_po_rate;
2202
2203 -------------------------------------------------------------
2204 --History
2205 -- 29-APR-03 Vgade Re-Burdening Changes .
2206
2207 -- Description
2208 -- This has been created to return original ei burden cost and the burden delta
2209 -- to the CDL mrc trigger, so that the C and D lines of MRC record will have the
2210 ---prorated cost of the delta.
2211 -- Changes
2212 PROCEDURE eiid_details( x_eiid IN NUMBER,
2213 x_orig_trx OUT NOCOPY VARCHAR2,
2214 x_adj_item OUT NOCOPY NUMBER,
2215 x_linkage OUT NOCOPY VARCHAR2,
2216 x_ei_date OUT NOCOPY DATE,
2217 x_txn_source OUT NOCOPY VARCHAR2,
2218 x_ei_burdened_cost OUT NOCOPY NUMBER,
2219 x_ei_burdened_delta OUT NOCOPY NUMBER,
2220 x_err_stack IN OUT NOCOPY VARCHAR2,
2221 x_err_stage IN OUT NOCOPY VARCHAR2,
2222 x_err_code OUT NOCOPY NUMBER)
2223
2224 IS
2225
2226 l_old_stack VARCHAR2(2000);
2227
2228 BEGIN
2229
2230 l_old_stack := x_err_stack;
2231 x_err_code := 0;
2232 x_err_stack := x_err_stack ||'->PA_MC_CURRENCY_PKG.eiid_details';
2233 x_err_stage := ' Select from pa_expenditure_items_all';
2234
2235 SELECT eia.orig_transaction_reference,
2236 nvl(eia.adjusted_expenditure_item_id, transferred_from_exp_item_id),
2237 eia.system_linkage_function,
2238 eia.expenditure_item_date,
2239 eia.transaction_source,
2240 eia.burden_cost,
2241 eia.posted_projfunc_burdened_cost
2242 INTO x_orig_trx,
2243 x_adj_item,
2244 x_linkage,
2245 x_ei_date,
2246 x_txn_source,
2247 x_ei_burdened_cost,
2248 x_ei_burdened_delta
2249 FROM pa_expenditure_items_all eia
2250 WHERE eia.expenditure_item_id = x_eiid;
2251
2252 x_err_stack := l_old_stack;
2253
2254 EXCEPTION
2255 WHEN NO_DATA_FOUND THEN
2256 x_err_code := SQLCODE;
2257 x_orig_trx := Null;
2258 x_adj_item := Null;
2259 x_linkage := Null;
2260 x_ei_date := Null;
2261 x_txn_source := Null;
2262 x_ei_burdened_cost := Null;
2263 x_ei_burdened_delta := Null;
2264
2265 WHEN OTHERS THEN
2266 x_orig_trx := Null;
2267 x_adj_item := Null;
2268 x_linkage := Null;
2269 x_ei_date := Null;
2270 x_txn_source := Null;
2271 x_ei_burdened_cost := Null;
2272 x_ei_burdened_delta := Null;
2273 RAISE;
2274
2275 END eiid_details;
2276
2277 -------------------------------------------------------------
2278
2279 END PA_MC_CURRENCY_PKG;