[Home] [Help]
PACKAGE BODY: APPS.FA_MC_UTIL_PVT
Source
1 PACKAGE BODY FA_MC_UTIL_PVT as
2 /* $Header: FAVMCUB.pls 120.10 2005/11/29 21:21:16 bridgway noship $ */
3
4 FUNCTION get_existing_rate
5 (p_set_of_books_id IN number,
6 p_transaction_header_id IN number,
7 px_rate IN OUT NOCOPY number,
8 px_avg_exchange_rate OUT NOCOPY number,
9 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
10
11 l_reporting_currency_code varchar2(15);
12
13 l_exchange_rate number;
14 l_avg_exchange_rate number;
15 l_complete varchar2(1);
16 l_result_code varchar2(15);
17
18 calc_err EXCEPTION;
19
20 BEGIN
21
22 l_reporting_currency_code := gl_mc_currency_pkg.get_currency_code (
23 p_set_of_books_id => p_set_of_books_id);
24
25 -- get the exchange rate from the corporate transaction
26 MC_FA_UTILITIES_PKG.get_rate
27 (p_set_of_books_id => p_set_of_books_id,
28 p_transaction_header_id => p_transaction_header_id,
29 p_currency_code => l_reporting_currency_code,
30 p_exchange_rate => l_exchange_rate,
31 p_avg_exchange_rate => l_avg_exchange_rate,
32 p_complete => l_complete,
33 p_result_code => l_result_code,
34 p_log_level_rec => p_log_level_rec);
35
36 if (l_result_code <> 'FOUND') then
37 raise calc_err;
38 end if;
39
40 px_rate := l_exchange_rate;
41 px_avg_exchange_rate := l_avg_exchange_rate;
42
43 return true;
44
45 EXCEPTION
46 when calc_err then
47 fa_srvr_msg.add_message(calling_fn => 'fa_mc_util_pvt.get_existing_rate',
48 p_log_level_rec => p_log_level_rec);
49 return false;
50
51 when others then
52 fa_srvr_msg.add_sql_error(calling_fn => 'fa_mc_util_pvt.get_existing_rate'
53 ,p_log_level_rec => p_log_level_rec);
54 return false;
55
56 END get_existing_rate;
57
58 ----------------------------------------------------------------------------------------
59
60 FUNCTION get_trx_rate
61 (p_prim_set_of_books_id IN number,
62 p_reporting_set_of_books_id IN number,
63 px_exchange_date IN OUT NOCOPY date,
64 p_book_type_code IN varchar2,
65 px_rate IN OUT NOCOPY number,
66 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
67
68 l_exchange_date date;
69 l_primary_currency_code varchar2(15);
70 l_conversion_type gl_daily_conversion_types.conversion_type%TYPE;
71 l_result_code varchar2(15);
72 l_exchange_rate number;
73 l_denominator_rate number;
74 l_numerator_rate number;
75
76 l_reporting_currency_code varchar2(15);
77 l_fixed_rate boolean;
78 l_relation varchar2(15);
79 l_trans_date date;
80
81 calc_err EXCEPTION;
82
83 BEGIN
84
85 if (p_log_level_rec.statement_level) then
86 fa_debug_pkg.add('get_trx',
87 'getting',
88 'currency code - primary',
89 p_log_level_rec);
90 end if;
91
92
93 l_primary_currency_code := gl_mc_currency_pkg.get_currency_code (
94 p_set_of_books_id => p_prim_set_of_books_id);
95
96
97 if (p_log_level_rec.statement_level) then
98 fa_debug_pkg.add('get_trx',
99 'getting',
100 'currency code - reporting',
101 p_log_level_rec);
102 end if;
103
104 l_reporting_currency_code := gl_mc_currency_pkg.get_currency_code (
105 p_set_of_books_id => p_reporting_set_of_books_id);
106
107 if (p_log_level_rec.statement_level) then
108 fa_debug_pkg.add('get_trx',
109 'getting',
110 'exchange_rate',
111 p_log_level_rec);
112 end if;
113
114
115 l_trans_date := px_exchange_date;
116
117 gl_mc_currency_pkg.get_rate(
118 p_primary_set_of_books_id => p_prim_set_of_books_id,
119 p_reporting_set_of_books_id => p_reporting_set_of_books_id,
120 p_trans_date => l_trans_date,
121 p_trans_currency_code => l_primary_currency_code,
122 p_trans_conversion_type => l_conversion_type,
123 p_trans_conversion_date => px_exchange_date,
124 p_trans_conversion_rate => l_exchange_rate,
125 p_application_id => 140,
126 p_org_id => NULL,
127 p_fa_book_type_code => p_book_type_code,
128 p_je_source_name => NULL,
129 p_je_category_name => NULL,
130 p_result_code => l_result_code,
131 p_denominator_rate => l_denominator_rate,
132 p_numerator_rate => l_numerator_rate);
133
134 if (p_log_level_rec.statement_level) then
135 fa_debug_pkg.add('get_trx',
136 'getting',
137 'relation',
138 p_log_level_rec);
139 end if;
140
141
142 gl_currency_api.get_relation(
143 x_from_currency => l_primary_currency_code,
144 x_to_currency => l_reporting_currency_code,
145 x_effective_date => px_exchange_date,
146 x_fixed_rate => l_fixed_rate,
147 x_relationship => l_relation);
148
149 if (p_log_level_rec.statement_level) then
150 fa_debug_pkg.add('get_trx',
151 'done getting',
152 'relation', p_log_level_rec);
153 end if;
154
155
156 if l_fixed_rate then
157 px_rate := l_numerator_rate / l_denominator_rate;
158 else
159 px_rate := l_exchange_rate;
160 end if;
161
162 return true;
163
164 EXCEPTION
165 when calc_err then
166 fa_srvr_msg.add_message(calling_fn => 'fa_mc_util_pvt.get_trx_rate',
167 p_log_level_rec => p_log_level_rec);
168 return false;
169
170 when others then
171 fa_srvr_msg.add_sql_error(calling_fn => 'fa_mc_util_pvt.get_trx_rate'
172 ,p_log_level_rec => p_log_level_rec);
173 return false;
174
175 END get_trx_rate;
176
177 -----------------------------------------------------------------------------
178
179 FUNCTION get_latest_rate
180 (p_asset_id IN number,
181 p_book_type_code IN varchar2,
182 p_set_of_books_id IN number,
183 px_rate OUT NOCOPY number,
184 px_avg_exchange_rate OUT NOCOPY number,
185 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
186
187 BEGIN
188
189 select br1.exchange_rate,
190 br1.avg_exchange_rate
191 into px_rate,
192 px_avg_exchange_rate
193 from fa_mc_books_rates br1
194 where br1.asset_id = p_asset_id
195 and br1.book_type_code = p_book_type_code
196 and br1.set_of_books_id = p_set_of_books_id
197 and br1.transaction_header_id =
198 (select max(br2.transaction_header_id)
199 from fa_mc_books_rates br2
200 where br2.asset_id = p_asset_id
201 and br2.book_type_code = p_book_type_code
202 and br2.set_of_books_id = p_set_of_books_id);
203
204 return true;
205
206 EXCEPTION
207
208 when others then
209 fa_srvr_msg.add_sql_error(calling_fn => 'fa_mc_util_pvt.get_latest_rate'
210 ,p_log_level_rec => p_log_level_rec);
211 return false;
212
213 END get_latest_rate;
214
215 -----------------------------------------------------------------------------
216
217 FUNCTION get_invoice_rate
218 (p_inv_rec IN FA_API_TYPES.inv_rec_type,
219 p_book_type_code IN varchar2,
220 p_set_of_books_id IN number,
221 px_exchange_date IN OUT NOCOPY date,
222 px_inv_rate_rec IN OUT NOCOPY FA_API_TYPES.inv_rate_rec_type,
223 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN boolean IS
224
225 -- new variables for merging mrc trigger logic
226 l_exchange_date DATE;
227 l_inv_exchange_date DATE;
228 l_mc_inv_exchange_date DATE;
229 l_exchange_rate NUMBER;
230 l_inv_exchange_rate NUMBER;
231 l_mc_inv_exchange_rate NUMBER;
232 l_current_asset_cost NUMBER;
233 l_result_code VARCHAR2(15);
234 l_inv_currency_code fnd_currencies.currency_code%TYPE;
235 l_mc_inv_currency_code fnd_currencies.currency_code%TYPE;
236 l_exchange_rate_type gl_daily_conversion_types.conversion_type%TYPE;
237 l_inv_exchange_rate_type gl_daily_conversion_types.conversion_type%TYPE;
238 l_mc_inv_exchange_rate_type gl_daily_conversion_types.conversion_type%TYPE;
239 l_denominator_rate NUMBER;
240 l_numerator_rate NUMBER;
241 l_line_base_amount NUMBER;
242 l_mc_line_base_amount NUMBER;
243 l_prj_fixed_assets_cost NUMBER;
244
245 l_currency_code fnd_currencies.currency_code%TYPE;
246 l_primary_currency_code fnd_currencies.currency_code%TYPE;
247 l_primary_sob_id NUMBER;
248
249
250 l_trans_date date;
251
252 l_calling_fn varchar2(35) := 'fa_mc_util_pvt.get_invoice_rate';
253
254 -- exceptions
255 error_found exception;
256
257 BEGIN
258
259 l_primary_sob_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
260 l_result_code := 'NOT_FOUND';
261
262 -- first check if this is a split line and if so,
263 -- attempt to get the rate from the parent. if it
264 -- doesn't exist, we'll just enter the main logic below
265
266 if (p_inv_rec.split_merged_code = 'SC') then
267
268 BEGIN
269 l_result_code := 'FOUND';
270
271 select exchange_rate
272 into l_exchange_rate
273 from fa_mc_mass_rates
274 where mass_addition_id = p_inv_rec.split_parent_mass_additions_id
275 and set_of_books_id = p_set_of_books_id;
276
277 EXCEPTION
278 when NO_DATA_FOUND then
279 -- this can happen when a reporting book is
280 -- associated to a FA book but not in AP
281 l_result_code := 'NOT_FOUND';
282 when OTHERS then
283 fa_srvr_msg.add_sql_error(
284 calling_fn => l_calling_fn,
285 p_log_level_rec => p_log_level_rec);
286 raise error_found;
287 END;
288
289 end if;
290
291 -- if rate not found from split logic above
292
293 if (l_result_code = 'NOT_FOUND') then
294
295 BEGIN
296
297 -- get the currency codes first
298 if (p_inv_rec.feeder_system_name = 'ORACLE PAYABLES' or
299 p_inv_rec.feeder_system_name = 'ORACLE PROJECTS') then
300
301 if (p_log_level_rec.statement_level) then
302 fa_debug_pkg.add(l_calling_fn,
303 'getting',
304 'currency code - reporting', p_log_level_rec);
305 end if;
306
307 l_currency_code := gl_mc_currency_pkg.get_currency_code (
308 p_set_of_books_id => p_set_of_books_id);
309
310 if (p_log_level_rec.statement_level) then
311 fa_debug_pkg.add(l_calling_fn,
312 'getting',
313 'currency code - primary', p_log_level_rec);
314 end if;
315
316 l_primary_currency_code := gl_mc_currency_pkg.get_currency_code (
317 p_set_of_books_id => l_primary_sob_id);
318
319 end if;
320
321 if (p_log_level_rec.statement_level) then
322 fa_debug_pkg.add(l_calling_fn,
323 'feeder system name',
324 p_inv_rec.feeder_system_name, p_log_level_rec);
325 end if;
326
327 -- if the rate is not provided for non-ap and non-pa lines,
328 -- derive and load it... this is needed for quick/detail adds
329 -- and source line additions as well as for flexibility
330
331 if (p_log_level_rec.statement_level) then
332 fa_debug_pkg.add('X','p sob', l_primary_sob_id, p_log_level_rec);
333 fa_debug_pkg.add('X','r sob', p_set_of_books_id, p_log_level_rec);
334 fa_debug_pkg.add('X','p date', px_exchange_date, p_log_level_rec);
335 fa_debug_pkg.add('X','p book', p_book_type_code, p_log_level_rec);
336 end if;
337
338 if not get_trx_rate
339 (p_prim_set_of_books_id => l_primary_sob_id,
340 p_reporting_set_of_books_id => p_set_of_books_id,
341 px_exchange_date => px_exchange_date,
342 p_book_type_code => p_book_type_code,
343 px_rate => l_exchange_rate,
344 p_log_level_rec => p_log_level_rec) then
345 raise error_found;
346 end if;
347
348 END; -- BEGIN block for invoices
349 end if; -- if found (for split)
350
351 -- assign the exchange rate back to the record
352 px_inv_rate_rec.exchange_rate := l_exchange_rate;
353
354 return true;
355
356 EXCEPTION
357
358 when error_found then
359 fa_srvr_msg.add_message(calling_fn => l_calling_fn
360 ,p_log_level_rec => p_log_level_rec);
361 return false;
362
363 when others then
364 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
365 ,p_log_level_rec => p_log_level_rec);
366 return false;
367
368
369 END get_invoice_rate;
370
371 END FA_MC_UTIL_PVT;