[Home] [Help]
PACKAGE BODY: APPS.CSD_COST_ANALYSIS_UTIL
Source
1 PACKAGE BODY CSD_COST_ANALYSIS_UTIL AS
2 /* $Header: csdvanub.pls 120.0 2005/05/24 17:40:33 appldev noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_COST_ANALYSIS_UTIL';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdvanub.pls';
6 l_debug NUMBER := fnd_profile.value('CSD_DEBUG_LEVEL');
7 --hello
8 -- Global variable for storing the debug level
9
10 G_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
11
12 /*----------------------------------------------------------------*/
13
14 /* procedure name: Convert_CurrencyAmount */
15
16 /* description : Converts an amount from one currency to */
17
18 /* another currency */
19
20 /* */
21
22 /* p_api_version Standard IN param */
23
24 /* p_commit Standard IN param */
25
26 /* p_init_msg_list Standard IN param */
27
28 /* p_validation_level Standard IN param */
29
30 /* p_from_currency Required Currency code to convert from */
31
32 /* p_to_currency Required Currency code to convert to */
33
34 /* p_eff_date Required Conversion Date */
35
36 /* p_amount Required Amount to convert */
37
38 /* x_conv_amount Converted amount */
39
40 /* x_return_status Standard OUT param */
41
42 /* x_msg_count Standard OUT param */
43
44 /* x_msg_data Standard OUT param */
45
46 /* */
47
48 /*----------------------------------------------------------------*/
49
50
51 PROCEDURE Convert_CurrencyAmount(p_api_version IN NUMBER,
52 p_commit IN VARCHAR2,
53 p_init_msg_list IN VARCHAR2,
54 p_validation_level IN NUMBER,
55 x_return_status OUT NOCOPY VARCHAR2,
56 x_msg_count OUT NOCOPY NUMBER,
57 x_msg_data OUT NOCOPY VARCHAR2,
58 p_from_currency IN VARCHAR2,
59 p_to_currency IN VARCHAR2,
60 p_eff_date IN DATE,
61 p_amount IN NUMBER,
62 x_conv_amount OUT NOCOPY NUMBER)
63 IS
64 l_api_name CONSTANT VARCHAR2(30) := 'Convert_CurrencyAmount';
65 l_api_version CONSTANT NUMBER := 1.0;
66 l_conversion_type VARCHAR2(30);
67 l_max_roll_days NUMBER;
68 l_denominator NUMBER := NULL;
69 l_numerator NUMBER := NULL;
70 l_rate NUMBER := NULL;
71 l_user_rate NUMBER := NULL;
72
73 -- Variable used in FND log
74
75 l_stat_level number := FND_LOG.LEVEL_STATEMENT;
76 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
77 l_event_level number := FND_LOG.LEVEL_EVENT;
78 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
79 l_error_level number := FND_LOG.LEVEL_ERROR;
80 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
81 l_mod_name varchar2(2000) := 'csd.plsql.csd_cost_analysis_util.Convert_CurrencyAmount';
82
83 BEGIN
84
85 -- Standard Start of API savepoint
86
87 SAVEPOINT Convert_CurrencyAmount_Utl;
88
89 -- Standard call to check for call compatibility.
90
91 IF NOT FND_API.Compatible_API_Call(l_api_version,
92 p_api_version,
93 l_api_name,
94 G_PKG_NAME)
95 THEN
96 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
97 END IF;
98
99 -- Initialize message list if p_init_msg_list is set to TRUE.
100
101 IF FND_API.to_Boolean(p_init_msg_list)
102 THEN
103 FND_MSG_PUB.initialize;
104 END IF;
105
106 -- Initialize API return status to success
107
108 x_return_status := FND_API.G_RET_STS_SUCCESS;
109
110 --
111 -- Begin API Body
112 --
113
114 -- Debug messages
115
116 IF (Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level)
117 THEN
118 FND_LOG.STRING(Fnd_Log.Level_Procedure,
119 'csd.plsql.csd_cost_analysis_util.Convert_CurrencyAmount.BEGIN',
120 'Entered Convert_CurrencyAmount');
121 END IF;
122
123 -- Check if conversion type profile is set. If not then raise error.
124
125 l_conversion_type := FND_PROFILE.value('CSD_CURRENCY_CONVERSION_TYPE');
126 IF (l_conversion_type IS NULL)
127 THEN
128 FND_MESSAGE.SET_NAME('CSD', 'CSD_CST_CURR_CONV_TYPE_NOT_SET');
129 IF (Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level)
130 THEN
131 FND_LOG.MESSAGE(Fnd_Log.Level_Error, l_mod_name, FALSE);
132 END IF;
133 -- ELSE
134 FND_MSG_PUB.ADD;
135 --END IF;
136 RAISE FND_API.G_EXC_ERROR;
137 END IF;
138
139 --Get the max roll days from the profile.
140
141 l_max_roll_days := FND_PROFILE.value('CSD_CURRENCY_MAX_ROLL');
142
143 -- Initialize x_converted_amount to the in parameter p_amount
144
145 x_conv_amount := p_amount;
146 IF (p_amount IS NULL)
147 THEN
148 x_conv_amount := 0;
149 ELSE
150
151 --Call GL API to convert the amount.
152 -- This is an impure API. It raises exceptions - NO_RATE and INVALID_CURRENCY. If so, it gets caught in others exception.
153 GL_CURRENCY_API.CONVERT_CLOSEST_AMOUNT(x_from_currency => p_from_currency,
154 x_to_currency => p_to_currency,
155 x_conversion_date => p_eff_date,
156 x_conversion_type => l_conversion_type,
157 x_user_rate => l_user_rate,
158 x_amount => p_amount,
159 x_max_roll_days => l_max_roll_days,
160 x_converted_amount => x_conv_amount,
161 x_denominator => l_denominator,
162 x_numerator => l_numerator,
163 x_rate => l_rate);
164 END IF;
165
166 -- Debug messages
167
168 IF (FND_LOG.LEVEL_PROCEDURE >= Fnd_Log.G_Current_Runtime_Level)
169 THEN
170 FND_LOG.STRING(Fnd_Log.Level_Procedure,
171 'csd.plsql.csd_cost_analysis_util.Convert_CurrencyAmount.END',
172 'LeavingConvert_CurrencyAmount');
173 END IF;
174
175 --
176 -- End API Body
177 --
178
179 -- Standard check of p_commit.
180
181 IF FND_API.To_Boolean(p_commit)
182 THEN
183 COMMIT WORK;
184 END IF;
185
186 -- Standard call to get message count and IF count is get message info.
187
188 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
189 EXCEPTION
190 WHEN FND_API.G_EXC_ERROR THEN
191
192 --ROLLBACK TO Convert_CurrencyAmount_Utl;
193
194 x_return_status := FND_API.G_RET_STS_ERROR;
195 x_conv_amount := NULL;
196 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
197 p_data => x_msg_data,
198 p_encoded => 'F');
199 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
200 THEN
201
202 FND_LOG.STRING(Fnd_Log.Level_Exception,
203 'csd.plsql.csd_cost_analysis_pvt.Convert_CurrencyAmount',
204 'EXC_ERROR['
205 || x_msg_data
206 || ']');
207 END IF;
208 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
209
210 -- ROLLBACK TO Convert_CurrencyAmount_Utl;
211
212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
213 x_conv_amount := NULL;
214 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
215 p_data => x_msg_data,
216 p_encoded => 'F');
217 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
218 THEN
219 FND_LOG.STRING(Fnd_Log.Level_Exception,
220 'csd.plsql.csd_cost_analysis_pvt.Convert_CurrencyAmount',
221 'EXC_ERROR['
222 || x_msg_data
223 || ']');
224 END IF;
225 WHEN OTHERS THEN
226
227 -- ROLLBACK TO Convert_CurrencyAmount_Utl;
228
229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
230 x_conv_amount := NULL;
231 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
232 THEN
233 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
234 END IF;
235 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
236 p_data => x_msg_data,
237 p_encoded => 'F');
238 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
239 THEN
240 FND_LOG.STRING(Fnd_Log.Level_Exception,
241 'csd.plsql.csd_cost_analysis_pvt.Convert_CurrencyAmount',
242 'SQL Message['
243 || sqlerrm
244 || ']');
245 END IF;
246 END Convert_CurrencyAmount;
247 /*----------------------------------------------------------------*/
248
249 /* procedure name: Compare_MLETotals */
250
251 /* description : Compares any two records of */
252
253 /* MLE_total_record_type by amount and percent. */
254
255 /* Difference is calculated by basis - compare. */
256
257 /* Percent is determined by dividing difference */
258
259 /* by the basis. */
260 /* bugfix 3795221- Percent is now determined by dividing difference by compare */
261 /* If currencies are different, */
262
263 /* Difference will be in currency of compare amts */
264
265 /* */
266
267 /* p_api_version Standard IN param */
268
269 /* p_commit Standard IN param */
270
271 /* p_init_msg_list Standard IN param */
272
273 /* p_validation_level Standard IN param */
274
275 /* p_mle_totals_basis required Totals to use as basis */
276
277 /* p_mle_totals_compare required Totals to compare to basis */
278
279 /* x_diff Basis - Compare */
280
281 /* x_pct_diff (Basis - Compare)*100/Basis */
282
283 /* x_return_status Standard OUT param */
284
285 /* x_msg_count Standard OUT param */
286
287 /* x_msg_data Standard OUT param */
288
289 /* */
290
291 /*----------------------------------------------------------------*/
292
293 PROCEDURE Compare_MLETotals(p_api_version IN NUMBER, p_commit IN VARCHAR2,
294 p_init_msg_list IN VARCHAR2,
295 p_validation_level IN NUMBER,
296 x_return_status OUT NOCOPY VARCHAR2,
297 x_msg_count OUT NOCOPY NUMBER,
298 x_msg_data OUT NOCOPY VARCHAR2,
299 p_mle_totals_basis IN MLE_TOTALS_REC_TYPE,
300 p_mle_totals_compare IN MLE_TOTALS_REC_TYPE,
301 x_diff OUT NOCOPY MLE_TOTALS_REC_TYPE,
302 x_pct_diff OUT NOCOPY MLE_TOTALS_REC_TYPE)
303 IS
304 l_api_name CONSTANT VARCHAR2(30) := 'Compare_MLETotals';
305 l_api_version CONSTANT NUMBER := 1.0;
306 l_mle_totals_basis MLE_TOTALS_REC_TYPE := p_mle_totals_basis;
307
308 -- Variable used in FND log
309
310 l_stat_level number := FND_LOG.LEVEL_STATEMENT;
311 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
312 l_event_level number := FND_LOG.LEVEL_EVENT;
313 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
314 l_error_level number := FND_LOG.LEVEL_ERROR;
315 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
316 l_mod_name varchar2(2000) := 'csd.plsql.csd_cost_analysis_util.Compare_MLETotals';
317
318 BEGIN
319
320 -- Standard Start of API savepoint
321
322 SAVEPOINT Compare_MLETotals_Utl;
323
324 -- Standard call to check for call compatibility.
325
326 IF NOT FND_API.Compatible_API_Call(l_api_version,
327 p_api_version,
328 l_api_name,
329 G_PKG_NAME)
330 THEN
331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
332 END IF;
333
334 -- Initialize message list if p_init_msg_list is set to TRUE.
335
336 IF FND_API.to_Boolean(p_init_msg_list)
337 THEN
338 FND_MSG_PUB.initialize;
339 END IF;
340
341 -- Initialize API return status to success
342
343 x_return_status := FND_API.G_RET_STS_SUCCESS;
344
345 --
346 -- Begin API Body
347 --
348 -- Debug messages
349
350 IF (Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level)
351 THEN
352 FND_LOG.STRING(Fnd_Log.Level_Procedure,
353 'csd.plsql.csd_cost_analysis_util.Compare_MLETotals.BEGIN',
354 'Entered Convert_CurrencyAmount');
355 END IF;
356
357 --Error if currency codes are differnt. While populating the databse, conversion must have happened.
358 -- This is an additional precautionary check.
359
360 IF p_mle_totals_basis.currency_code <> p_mle_totals_compare.currency_code
361 THEN
362
363 -- Throw error
364
365 FND_MESSAGE.SET_NAME('CSD', 'CSD_CST_CURR_CODE_DIFF');
366 IF (Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level)
367 THEN
368 FND_LOG.MESSAGE(Fnd_Log.Level_Error, l_mod_name, FALSE);
369 END IF;
370 -- ELSE
371 FND_MSG_PUB.ADD;
372 --END IF;
373 RAISE FND_API.G_EXC_ERROR;
374 END IF;
375
376 -- Populate difference between basis and compare data
377
381
378 x_diff.materials := p_mle_totals_compare.materials - p_mle_totals_basis.materials;
379 x_diff.labor := p_mle_totals_compare.labor - p_mle_totals_basis.labor;
380 x_diff.expenses := p_mle_totals_compare.expenses - p_mle_totals_basis.expenses;
382 -- total differnece will be null only when all 3 are null else it will be a normal sum.
383
384 IF (x_diff.materials = NULL
385 AND x_diff.labor = NULL
386 AND x_diff.expenses = NULL)
387 THEN
388 x_diff.mle_total := NULL;
389 ELSE
390 x_diff.mle_total := nvl(x_diff.materials, 0) + nvl(x_diff.labor, 0) + nvl(x_diff.expenses,
391 0);
392 END IF;
393 x_diff.currency_code := p_mle_totals_compare.currency_code;
394
395 --Return null if any of the billing category amount is 0.
396
397 IF p_mle_totals_compare.materials = 0
398 THEN
399 x_pct_diff.materials := NULL;
400 ELSE
401 --3795221- sangigup x_pct_diff.materials := round(x_diff.materials * 100 / p_mle_totals_basis.materials, 2);
402 x_pct_diff.materials := round(x_diff.materials * 100 / p_mle_totals_compare.materials, 2);
403 END IF;
404 IF p_mle_totals_compare.labor = 0
405 THEN
406 x_pct_diff.labor := NULL;
407 ELSE
408 --3795221- sangigup x_pct_diff.labor := round(x_diff.labor * 100 / p_mle_totals_basis.labor, 2);
409 x_pct_diff.labor := round(x_diff.labor * 100 / p_mle_totals_compare.labor, 2);
410 END IF;
411 IF p_mle_totals_compare.expenses = 0
412 THEN
413 x_pct_diff.expenses := NULL;
414 ELSE
415 x_pct_diff.expenses := round(x_diff.expenses * 100 / p_mle_totals_basis.expenses, 2);
416 END IF;
417 IF p_mle_totals_compare.mle_total = 0
418 THEN
419 x_pct_diff.mle_total := NULL;
420 ELSE
421 --3795221 sangigup x_pct_diff.mle_total := round(x_diff.mle_total * 100 / p_mle_totals_basis.mle_total, 2);
422 x_pct_diff.mle_total := round(x_diff.mle_total * 100 / p_mle_totals_compare.mle_total, 2);
423 END IF;
424 x_pct_diff.currency_code := NULL;
425 IF (Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level)
426 THEN
427 FND_LOG.STRING(Fnd_Log.Level_Procedure,
428 'csd.plsql.csd_cost_analysis_util.Compare_MLETotals.END',
429 'LEaving Convert_CurrencyAmount');
430 END IF;
431
432 -- End API Body
433 --
434
435 -- Standard check of p_commit.
436
437 IF FND_API.To_Boolean(p_commit)
438 THEN
439 COMMIT WORK;
440 END IF;
441
442 -- Standard call to get message count and IF count is get message info.
443
444 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
445 EXCEPTION
446 WHEN FND_API.G_EXC_ERROR THEN
447 ROLLBACK TO Compare_MLETotals_Utl;
448 x_return_status := FND_API.G_RET_STS_ERROR;
449 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
450 p_data => x_msg_data);
451 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
452 THEN
453 FND_LOG.STRING(Fnd_Log.Level_Exception,
454 'csd.plsql.csd_cost_analysis_pvt.Compare_MLETotals',
455 'EXC_ERROR['
456 || x_msg_data
457 || ']');
458 END IF;
459 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
460 ROLLBACK TO Compare_MLETotals_Utl;
461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
462 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
463 p_data => x_msg_data);
464 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
465 THEN
466 FND_LOG.STRING(Fnd_Log.Level_Exception,
467 'csd.plsql.csd_cost_analysis_pvt.Compare_MLETotals',
468 'EXC_ERROR['
469 || x_msg_data
470 || ']');
471 END IF;
472 WHEN OTHERS THEN
473 ROLLBACK TO Compare_MLETotals_Utl;
474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
475 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
476 THEN
477 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
478 END IF;
479 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
480 p_data => x_msg_data);
481 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
482 THEN
483 FND_LOG.STRING(Fnd_Log.Level_Exception,
484 'csd.plsql.csd_cost_analysis_pvt.Compare_MLETotals',
485 'SQL Message['
486 || sqlerrm
487 || ']');
488 END IF;
489 END Compare_MLETotals;
490 /*----------------------------------------------------------------*/
491
492 /* function name: get_GLCurrencyCode */
493
497
494 /* description : Given an organization_id,returns currency code */
495
496 /* for the Organization */
498 /* p_organization_id IN NUMBER */
499
500 /* */
501
502 /*----------------------------------------------------------------*/
503
504 FUNCTION get_GLCurrencyCode(p_organization_id IN NUMBER) RETURN VARCHAR2
505 IS
506 --Curcor to get GL Currency Code defined in GL set of books.
507 CURSOR cur_getGLCode(p_org_id NUMBER)
508 IS
509 SELECT gl.currency_code
510 FROM gl_sets_of_books gl, hr_operating_units hr
511 WHERE hr.set_of_books_id = gl.set_of_books_id
512 AND hr.organization_id = p_org_id;
513 l_currency_code VARCHAR2(15);
514
515 BEGIN
516 OPEN cur_getGLCOde(p_organization_id);
517 FETCH cur_getGLCode INTO l_currency_code;
518 CLOSE cur_getGLCode;
519 RETURN l_currency_code;
520 EXCEPTION
521 WHEN TOO_MANY_ROWS THEN
522 l_currency_code := NULL;
523 FND_MESSAGE.SET_NAME('CSD', 'CSD_CST_TOO_MANY_ROWS_CURR');
524 FND_MSG_PUB.ADD;
525 /*
526 FND_MSG_PUB.Count_And_Get
527 (p_count => x_msg_count,
528 p_data => x_msg_data
529 );*/
530
531 RETURN l_currency_code;
532 WHEN NO_DATA_FOUND THEN
533 l_currency_code := NULL;
534 FND_MESSAGE.SET_NAME('CSD', 'CSD_CST_NO_DATA_CURR');
535 FND_MSG_PUB.ADD;
536 /*
537 FND_MSG_PUB.Count_And_Get
538 (p_count => x_msg_count,
539 p_data => x_msg_data
540 );*/
541
542 RETURN l_currency_code;
543 WHEN OTHERS THEN
544 l_currency_code := NULL;
545 RETURN l_currency_code;
546 END Get_GLCurrencyCode;
547 /*----------------------------------------------------------------*/
548
549 /* function name: Validate_CostingEnabled */
550
551 /* description : Given an organization_id, returns TRUE if the */
552
553 /* Organization is costing enabled. */
554
555 /* p_organization_id IN NUMBER */
556
557 /* */
558
559 /*----------------------------------------------------------------*/
560
561 FUNCTION Validate_CostingEnabled(p_organization_id IN NUMBER) RETURN BOOLEAN
562 IS
563 l_cost_enabled BOOLEAN;
564 l_PrimaryCostMethod NUMBER;
565 l_costing vARCHAR2(4);
566
567 --Curcor to get the primary cost method for the organization.
568 CURSOR cur_getPrimaryCostMethod(p_organization_id NUMBER)
569 IS
570 SELECT primary_cost_method
571 FROM mtl_parameters
572 WHERE organization_id = p_organization_id;
573 BEGIN
574 l_cost_enabled := TRUE;
575
576 -- get the profile value of CSD_ENABLE_COSTING
577
578 IF nvl(fnd_profile.value('CSD_ENABLE_COSTING'), 'N') <> 'Y'
579 THEN
580 l_cost_enabled := FALSE;
581 END IF;
582 IF l_cost_enabled
583 THEN
584
585 -- get the primary cost method for the organization
586
587 OPEN cur_getPrimaryCostMethod(p_organization_id);
588 FETCH cur_getPrimaryCostMethod INTO l_PrimaryCostMethod;
589 IF cur_getPrimaryCostMethod%NOTFOUND
590 THEN
591 l_cost_enabled := FALSE;
592 END IF;
593 IF l_PrimaryCostMethod <> 1
594 THEN
595 l_cost_enabled := FALSE;
596 END IF;
597 CLOSE cur_getPrimaryCostMethod;
598 END IF;
599 RETURN l_cost_enabled;
600 EXCEPTION
601 WHEN OTHERS THEN
602 l_cost_enabled := FALSE;
603 RETURN l_cost_enabled;
604 END Validate_CostingEnabled;
605
606 END CSD_COST_ANALYSIS_UTIL;