1 PACKAGE BODY MC_FA_UTILITIES_PKG as
2 /* $Header: famcutib.pls 120.9 2005/07/25 09:58:25 yyoon ship $ */
3
4 PROCEDURE insert_books_rates
5 (p_set_of_books_id in number,
6 p_asset_id in number,
7 p_book_type_code in varchar2,
8 p_transaction_header_id in number,
9 p_invoice_transaction_id in number,
10 p_exchange_date in date,
11 p_cost in number,
12 p_exchange_rate in number,
13 p_avg_exchange_rate in number,
14 p_last_updated_by in number,
15 p_last_update_date in date,
16 p_last_update_login in number,
17 p_complete in varchar2,
18 p_trigger in varchar2,
19 p_currency_code in varchar2,
20 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
21 BEGIN
22 INSERT
23 INTO fa_mc_books_rates(set_of_books_id,
24 asset_id,
25 book_type_code,
26 transaction_header_id,
27 invoice_transaction_id,
28 transaction_date_entered,
29 cost,
30 exchange_rate,
31 avg_exchange_rate,
32 last_updated_by,
33 last_update_date,
34 last_update_login,
35 complete)
36 VALUES(p_set_of_books_id,
37 p_asset_id,
38 p_book_type_code,
39 p_transaction_header_id,
40 p_invoice_transaction_id,
41 p_exchange_date,
42 p_cost,
43 p_exchange_rate,
44 p_avg_exchange_rate,
45 p_last_updated_by,
46 p_last_update_date,
47 p_last_update_login,
48 p_complete);
49 EXCEPTION
50 WHEN DUP_VAL_ON_INDEX THEN
51 BEGIN
52 -- this gets executed from insert_books_rates call in
53 -- fa_mc_asset_invoices_aid trigger when mass additions
54 -- creates an asset out of merged children
55 -- since all the invoice lines will have the same
56 -- invoice_transaction_id this exception is raised and
57 -- update the exchange_rate with weighted rate calculation
58
59 -- int_debug.print('DUP_VAL_ON_INDEX found');
60
61 -- Fix for Bug 1131880.Use decode to check if total cost
62 -- is 0 and if that is the case use exchange rate of 1.
63 -- This scenario is possible when posting MP and MC lines
64 -- where MC line can also have 0 fixed assets cost and
65 -- MP has 0 cost.Total cost of 0 will result in ora-1476
66
67 UPDATE fa_mc_books_rates a
68 SET a.exchange_rate = decode(a.cost + p_cost,
69 0, 1,
70 (a.cost * a.exchange_rate +
71 p_cost * p_exchange_rate) /
72 (a.cost + p_cost)),
73 a.avg_exchange_rate = decode(a.cost + p_cost,
74 0, 1,
75 (a.cost * a.avg_exchange_rate +
76 p_cost * p_exchange_rate) /
77 (a.cost + p_cost)),
78 a.last_updated_by = p_last_updated_by,
79 a.last_update_date = p_last_update_date,
80 a.last_update_login = p_last_update_login,
81 a.complete = p_complete,
82 a.cost = a.cost + p_cost
83 WHERE a.set_of_books_id = p_set_of_books_id
84 AND a.asset_id = p_asset_id
85 AND a.book_type_code = p_book_type_code
86 AND nvl(a.transaction_header_id,0) =
87 nvl(p_transaction_header_id,0)
88 AND nvl(a.invoice_transaction_id,0) =
89 nvl(p_invoice_transaction_id,0);
90 EXCEPTION
91 WHEN OTHERS THEN
92 fnd_message.set_name('OFA','FA_MRC_UPD_MC_RECS');
93 fnd_message.set_token('TABLE', 'fa_mc_books_rates');
94 fnd_message.set_token('TRIGGER',
95 'fa_mc_asset_invoices_aid');
96 fnd_message.set_token('ERROR',sqlerrm);
97 raise_application_error(-20000,fnd_message.get);
98 END;
99 WHEN OTHERS THEN
100 fnd_message.set_name('OFA', 'FA_MRC_INS_MC_RECS');
101 fnd_message.set_token('TABLE', 'fa_mc_books_rates');
102 fnd_message.set_token('TRIGGER', p_trigger);
103 fnd_message.set_token('ERROR',sqlerrm);
104 raise_application_error(-20000,fnd_message.get);
105 END;
106
107 PROCEDURE get_rate
108 (p_set_of_books_id in number,
109 p_transaction_header_id in number,
110 p_currency_code in varchar2,
111 p_exchange_rate out nocopy number,
112 p_avg_exchange_rate out nocopy number,
113 p_complete out nocopy varchar2,
114 p_result_code out nocopy varchar2,
115 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
116
117 BEGIN
118 p_complete := 'N';
119 p_result_code := 'FOUND';
120 SELECT exchange_rate, avg_exchange_rate, complete
121 INTO p_exchange_rate, p_avg_exchange_rate, p_complete
122 FROM fa_mc_books_rates
123 WHERE set_of_books_id = p_set_of_books_id
124 AND transaction_header_id = p_transaction_header_id;
125 EXCEPTION
126 WHEN NO_DATA_FOUND THEN
127 p_complete := 'N';
128 p_result_code := 'NOT_FOUND';
129 WHEN OTHERS THEN
130 fnd_message.set_name('OFA','FA_MRC_GET_RATE');
131 fnd_message.set_token('ERROR',sqlerrm);
132 raise_application_error(-20000,fnd_message.get);
133 END;
134
135 END MC_FA_UTILITIES_PKG;
[Home] [Help]