1 PACKAGE BODY CSD_COST_ANALYSIS_PVT AS
2 /* $Header: csdvanab.pls 120.1 2005/06/06 16:14:40 appldev $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_COST_ANALYSIS_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdvanab.pls';
6
7
8 -- Global variable for storing the debug level
9
10 G_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
11 /*----------------------------------------------------------------*/
12
13 /* procedure name: Get_TotalEstCosts */
14
15 /* description : procedure used to get total estimated costs */
16
17 /* */
18
19 /* p_api_version Standard IN param */
20
21 /* p_commit Standard IN param */
22
23 /* p_init_msg_list Standard IN param */
24
25 /* p_validation_level Standard IN param */
26
27 /* p_repair_estimate_id Required Estimate ID to get */
28 /* est costs */
29
30 /* p_organization_id Required Organization Id */
31
32 /* p_ro_currency_code Required Repair Order Currency Code*/
33
34 /* x_costs Total MLE costs for repair line */
35
36 /* x_return_status Standard OUT param */
37
38 /* x_msg_count Standard OUT param */
39
40 /* x_msg_data Standard OUT param */
41
42 /* */
43
44 /*----------------------------------------------------------------*/
45
46 PROCEDURE Get_TotalEstCosts(p_api_version IN NUMBER, p_commit IN VARCHAR2,
47 p_init_msg_list IN VARCHAR2,
48 p_validation_level IN NUMBER,
49 x_return_status OUT NOCOPY VARCHAR2,
50 x_msg_count OUT NOCOPY NUMBER,
51 x_msg_data OUT NOCOPY VARCHAR2,
52 p_repair_estimate_id IN NUMBER,
53 p_organization_id IN NUMBER,
54 p_ro_currency_code IN VARCHAR2,
55 x_costs OUT NOCOPY CSD_COST_ANALYSIS_UTIL.MLE_TOTALS_REC_TYPE)
56
57 IS
58
59 --Cursor to get total cost for an estimate line, group by MLE.
60
61 CURSOR cur_getTotalCost(p_repair_estimate_id NUMBER)
62 IS
63 SELECT btc.billing_category,
64 sum(rel.item_Cost * ed.quantity_required) TotalCost -- sangita
65 FROM CSD_REPAIR_ESTIMATE_LINES rel, CS_ESTIMATE_DETAILS ed,
66 CS_BILLING_TYPE_CATEGORIES btc, CS_TXN_BILLING_TYPES btt
67 WHERE rel.repair_estimate_id = p_repair_estimate_id
68 AND rel.estimate_detail_id = ed.estimate_detail_id
69 AND ed.txn_billing_type_id = btt.txn_billing_type_id
70 AND btc.billing_type = btt.billing_type
71 GROUP BY btc.billing_category;
72
73 --Cursor to get the number of rows with null item cost for an estimate line.
74
75 CURSOR cur_getNullCostRows(p_repair_estimate_id NUMBER)
76 IS
77 SELECT count(rel.estimate_detail_id)
78 FROM CSD_REPAIR_ESTIMATE_LINES rel
79 WHERE rel.repair_estimate_id = p_repair_estimate_id
80 AND rel.item_cost IS NULL
81 AND rownum = 1;
82
83 --Cursor to determine the count of estimate lines for an estimate header.
84
85 CURSOR cur_getEstLineCount(p_repair_Estimate_id NUMBER)
86 IS
87 SELECT count(repair_estimate_line_id)
88 FROM CSD_REPAIR_ESTIMATE_LINES
89 WHERE repair_estimate_id = p_repair_estimate_id
90 ANd rownum=1;
91 l_api_name CONSTANT VARCHAR2(30) := 'Get_TotalEstCosts';
92 l_api_version CONSTANT NUMBER := 1.0;
93 l_nullCostRows NUMBER;
94 l_count NUMBER;
95
96 -- Variable used in FND log
97
98 l_stat_level number := FND_LOG.LEVEL_STATEMENT;
99 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
100 l_event_level number := FND_LOG.LEVEL_EVENT;
101 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
102 l_error_level number := FND_LOG.LEVEL_ERROR;
103 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
104 l_mod_name varchar2(2000) := 'csd.plsql.csd_cost_analysis_pvt.get_totalestcosts';
105
106 BEGIN
107
108 -- Standard Start of API savepoint
109 -- No need to create savepoints because no updates/inserts are being done.
110 -- SAVEPOINT Get_TotalEstCosts_Pvt;
111
112 -- Standard call to check for call compatibility.
113
114 IF NOT FND_API.Compatible_API_Call(l_api_version,
115 p_api_version,
116 l_api_name,
117 G_PKG_NAME)
118 THEN
119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
120 END IF;
121
122 -- Initialize message list if p_init_msg_list is set to TRUE.
123
124 IF FND_API.to_Boolean(p_init_msg_list)
125 THEN
126 FND_MSG_PUB.initialize;
127 END IF;
128
129 -- Initialize API return status to success
130
131 x_return_status := FND_API.G_RET_STS_SUCCESS;
132
133 --
134 -- Begin API Body
135 --
136 -- Debug messages
137
138 IF (Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level)
139 THEN
140 FND_LOG.STRING(Fnd_Log.Level_Procedure,
141 'csd.plsql.csd_cost_analysis_pvt.get_totalestcosts.BEGIN',
142 'Entered Get_TotalEstCosts');
143 END IF;
144
145 -- IF not costing enabled then throw exception
146
147 IF NOT (CSD_COST_ANALYSIS_UTIL.Validate_CostingEnabled(p_organization_id))
148 THEN
149
150 -- Throw error
151
152 FND_MESSAGE.SET_NAME('CSD', 'CSD_CST_COSTING_NOT_ENABLED');
153 IF (Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level)
154 THEN
155 FND_LOG.MESSAGE(Fnd_Log.Level_Error, l_mod_name, FALSE);
156 END IF;
157 -- ELSE
158 FND_MSG_PUB.ADD;
159 --END IF;
160 RAISE FND_API.G_EXC_ERROR;
161 END IF;
162
163 -- Raise warning if no estimate lines are found.
164
165 OPEN cur_getEstLineCount(p_repair_estimate_id);
166 FETCH cur_getEstLineCount INTO l_count;
167 IF (l_count = 0)
168 THEN
169 CLOSE cur_getEstLineCount;
170
171 -- Throw error
172
173 FND_MESSAGE.SET_NAME('CSD', 'CSD_CST_NO_CHG_ROWS_SELECTED');
174 IF (Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level)
175 THEN
176 FND_LOG.MESSAGE(Fnd_Log.Level_Error, l_mod_name, FALSE);
177 END IF;
178 -- ELSE
179 FND_MSG_PUB.ADD;
180 --END IF;
181 RAISE FND_API.G_EXC_ERROR;
182 END IF;
183 CLOSE cur_getEstLineCount;
184
185 -- Raise warning if any item cost is null
186
187 OPEN cur_getNullCostRows(p_repair_estimate_id);
188 FETCH cur_getNullCostRows INTO l_nullCostRows;
189 IF l_nullCostRows > 0
190 THEN
191 CLOSE cur_getNullCostRows;
192 FND_MESSAGE.SET_NAME('CSD', 'CSD_CST_NULL_ITEM_COST');
193 IF (Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level)
194 THEN
195 FND_LOG.MESSAGE(Fnd_Log.Level_Error, l_mod_name, FALSE);
196 END IF;
197 -- ELSE
198 FND_MSG_PUB.ADD;
199 --END IF;
200 RAISE FND_API.G_EXC_ERROR;
201 END IF;
202 CLOSE cur_getNullCostRows;
203
204 -- Loop through the cursor and get the MLE lines cost.
205
206 FOR data IN cur_getTotalCost(p_repair_Estimate_id) LOOP
207 IF data.billing_Category = 'M'
208 THEN
209 x_costs.materials := data.TotalCost;
210 ELSIF data.billing_category = 'L'
211 THEN
212 x_costs.labor := data.TotalCost;
213 ELSIF data.billing_category = 'E'
214 THEN
215 x_costs.expenses := data.TotalCost;
216 END IF;
217 x_costs.currency_code := p_ro_currency_code;
218 END LOOP;
219
220 --close the cursor if open.
221
222 IF cur_getTotalCost%ISOPEN
223 THEN
224 CLOSE cur_getTotalCost;
225 END IF;
226
227 --Caluculate MLE_TOTAL
228
229 x_costs.MLE_TOTAL := nvl(x_costs.materials, 0) + nvl(x_costs.labor,
230 0) + nvl(x_costs.expenses,
231 0);
232
233 -- Debug messages
234
235 IF (Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level)
236 THEN
237 FND_LOG.STRING(Fnd_Log.Level_Procedure,
238 'csd.plsql.csd_cost_analysis_pvt.get_totalestcosts.END',
239 'Leaving Get_TotalEstCosts');
240 END IF;
241
242 --
243 -- End API Body
244 --
245
246 -- Standard check of p_commit.
247
248 IF FND_API.To_Boolean(p_commit)
249 THEN
250 COMMIT WORK;
251 END IF;
252
253 -- Standard call to get message count and IF count is get message info.
254
255 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
256 EXCEPTION
257 WHEN FND_API.G_EXC_ERROR THEN
258
259 --ROLLBACK TO Get_TotalEstCosts_Pvt;
260
261 x_return_status := FND_API.G_RET_STS_ERROR;
262 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
263 p_data => x_msg_data,
264 p_encoded => 'F');
265 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
266 THEN
267 FND_LOG.STRING(Fnd_Log.Level_Exception,
268 'csd.plsql.csd_cost_analysis_pvt.Get_TotalEstCosts',
269 'EXC_ERROR['
270 || x_msg_data
271 || ']');
272 END IF;
273 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
274
275 --ROLLBACK TO Get_TotalEstCosts_Pvt;
276
277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
278 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
279 p_data => x_msg_data,
280 p_encoded => 'F');
281 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
282 THEN
283 FND_LOG.STRING(Fnd_Log.Level_Exception,
284 'csd.plsql.csd_cost_analysis_pvt.Get_TotalEstCosts',
285 'EXC_ERROR['
286 || x_msg_data
287 || ']');
288 END IF;
289 WHEN OTHERS THEN
290
291 --ROLLBACK TO Get_TotalEstCosts_Pvt;
292
293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
294 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
295 THEN
296 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
297 END IF;
298 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
299 p_data => x_msg_data,
300 p_encoded => 'F');
301 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
302 THEN
303 FND_LOG.STRING(Fnd_Log.Level_Exception,
304 'csd.plsql.csd_cost_analysis_pvt.Get_TotalEstCosts',
305 'SQL Message['
306 || sqlerrm
307 || ']');
308 END IF;
309 END Get_TotalEstCosts;
310 /*----------------------------------------------------------------*/
311
312 /* procedure name: Get_TotalEstCharges */
313
314 /* description : procedure used to get total estimated charges */
315
316 /* */
317
318 /* p_api_version Standard IN param */
319
320 /* p_commit Standard IN param */
321
322 /* p_init_msg_list Standard IN param */
323
324 /* p_validation_level Standard IN param */
325
326 /* p_repair_estimate_id Required Estimate ID to get est charges */
327
328 /* p_ro_currency_code Required Repair Order Currency Code*/
329
330 /* x_charges Total MLE charges for repair line */
331
332 /* x_return_status Standard OUT param */
333
334 /* x_msg_count Standard OUT param */
335
336 /* x_msg_data Standard OUT param */
337
338 /* */
339
340 /*----------------------------------------------------------------*/
341
342 PROCEDURE Get_TotalEstCharges(p_api_version IN NUMBER,
343 p_commit IN VARCHAR2,
344 p_init_msg_list IN VARCHAR2,
345 p_validation_level IN NUMBER,
346 x_return_status OUT NOCOPY VARCHAR2,
347 x_msg_count OUT NOCOPY NUMBER,
348 x_msg_data OUT NOCOPY VARCHAR2,
349 p_repair_estimate_id IN NUMBER,
350 p_ro_currency_code IN VARCHAR2,
351 x_charges OUT NOCOPY CSD_COST_ANALYSIS_UTIL.MLE_TOTALS_REC_TYPE)
352 IS
353 --Cursor to get teh line count for an estimate header.
354 CURSOR cur_getEstLineCount(p_repair_Estimate_id NUMBER)
355 IS
356 SELECT count(repair_estimate_line_id)
357 FROM CSD_REPAIR_ESTIMATE_LINES
358 WHERE repair_estimate_id = p_repair_estimate_id;
359
360 --Cursor to get the charges, group by MLE.
361 CURSOR cur_getTotalCharges(p_repair_estimate_id NUMBER)
362 IS
363 SELECT btc.billing_category,
364 sum(nvl(ed.after_warranty_cost, 0)) TotalCharges
365 FROM CSD_REPAIR_ESTIMATE_LINES rel, CS_ESTIMATE_DETAILS ed,
366 CS_BILLING_TYPE_CATEGORIES btc, CS_TXN_BILLING_TYPES btt
367 WHERE rel.repair_estimate_id = p_repair_estimate_id
368 AND rel.estimate_detail_id = ed.estimate_detail_id
369 AND ed.txn_billing_type_id = btt.txn_billing_type_id
370 AND btc.billing_type = btt.billing_type
371 GROUP BY btc.billing_category;
372
373 l_api_name CONSTANT VARCHAR2(30) := 'Get_TotalEstCharges';
374 l_api_version CONSTANT NUMBER := 1.0;
375 l_nullCostRows NUMBER;
376 l_count NUMBER;
377
378 -- Variable used in FND log
379
380 l_stat_level number := FND_LOG.LEVEL_STATEMENT;
381 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
382 l_event_level number := FND_LOG.LEVEL_EVENT;
383 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
384 l_error_level number := FND_LOG.LEVEL_ERROR;
385 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
386 l_mod_name varchar2(2000) := 'csd.plsql.csd_cost_analysis_pvt.get_totalestcharges';
387
388 BEGIN
389
390 -- Standard Start of API savepoint
391 --SAVEPOINT Get_TotalEstCharges_Pvt;
392
393 -- Standard call to check for call compatibility.
394
395 IF NOT FND_API.Compatible_API_Call(l_api_version,
396 p_api_version,
397 l_api_name,
398 G_PKG_NAME)
399 THEN
400 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
401 END IF;
402
403 -- Initialize message list if p_init_msg_list is set to TRUE.
404
405 IF FND_API.to_Boolean(p_init_msg_list)
406 THEN
407 FND_MSG_PUB.initialize;
408 END IF;
409
410 -- Initialize API return status to success
411
412 x_return_status := FND_API.G_RET_STS_SUCCESS;
413
414 --
415 -- Begin API Body
416 --
417 -- Debug messages
418
419 IF (Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level)
420 THEN
421 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
422 'csd.plsql.csd_cost_analysis_pvt.get_totalestcosts.BEGIN',
423 'Entered Get_TotalEstCharges');
424 END IF;
425
426 -- No need to check if costing enabled because these are charges.
427
428 OPEN cur_getEstLineCount(p_repair_estimate_id);
429 FETCH cur_getEstLineCount INTO l_count;
430 IF (l_count = 0)
431 THEN
432 CLOSE cur_getEstLineCount;
433
434 -- Throw error
435
436 FND_MESSAGE.SET_NAME('CSD', 'CSD_CST_NO_CHG_ROWS_SELECTED');
437 IF (Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level)
438 THEN
439 FND_LOG.MESSAGE(Fnd_Log.Level_Error, l_mod_name, FALSE);
440 END IF;
441 --ELSE
442 FND_MSG_PUB.ADD;
443 -- END IF;
444 RAISE FND_API.G_EXC_ERROR;
445 END IF;
446 CLOSE cur_getEstLineCount;
447
448 --Get MLE charge amount
449
450 FOR x IN cur_getTotalCharges(p_repair_Estimate_id) LOOP
451 IF x.billing_Category = 'M'
452 THEN
453 x_charges.materials := x.TotalCharges;
454 ELSIF x.billing_category = 'L'
455 THEN
456 x_charges.labor := x.TotalCharges;
457 ELSIF x.billing_category = 'E'
458 THEN
459 x_charges.expenses := x.TotalCharges;
460 END IF;
461 x_charges.currency_code := p_ro_currency_code;
462 END LOOP;
463 IF cur_getTotalCharges%ISOPEN
464 THEN
465 CLOSE cur_getTotalCharges;
466 END IF;
467
468 --CLOSE cur_getTotalCharges;
469
470 x_charges.MLE_TOTAL := nvl(x_charges.materials, 0) + nvl(x_charges.labor,
471 0) + nvl(x_charges.expenses,
472 0);
473
474 -- Debug messages
475
476 IF (Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level)
477 THEN
478 FND_LOG.STRING(Fnd_Log.Level_Procedure,
479 'csd.plsql.csd_cost_analysis_pvt.get_totalestcharges.END',
480 ' Exiting Get_TotalEstCharges');
481 END IF;
482
483 --
484 -- End API Body
485 --
486
487 -- Standard check of p_commit.
488
489 IF FND_API.To_Boolean(p_commit)
490 THEN
491 COMMIT WORK;
492 END IF;
493
494 -- Standard call to get message count and IF count is get message info.
495
496 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
497 EXCEPTION
498 WHEN FND_API.G_EXC_ERROR THEN
499
500 -- ROLLBACK TO Get_TotalEstCharges_Pvt;
501
502 x_return_status := FND_API.G_RET_STS_ERROR;
503 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
504 p_data => x_msg_data,
505 p_encoded => 'F');
506 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
507 THEN
508 FND_LOG.STRING(Fnd_Log.Level_Exception,
509 'csd.plsql.csd_cost_analysis_pvt.Get_TotalEstCharges',
510 'EXC_ERROR['
511 || x_msg_data
512 || ']');
513 END IF;
514 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
515
516 --ROLLBACK TO Get_TotalEstCharges_Pvt;
517
518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
519 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
520 p_data => x_msg_data,
521 p_encoded => 'F');
522 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
523 THEN
524 FND_LOG.STRING(Fnd_Log.Level_Exception,
525 'csd.plsql.csd_cost_analysis_pvt.Get_TotalEstCharges',
526 'EXC_ERROR['
527 || x_msg_data
528 || ']');
529 END IF;
530 WHEN OTHERS THEN
531
532 --ROLLBACK TO Get_TotalEstCharges_Pvt;
533
534 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
535 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
536 THEN
537 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
538 END IF;
539 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
540 p_data => x_msg_data,
541 p_encoded => 'F');
542 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
543 THEN
544 FND_LOG.STRING(Fnd_Log.Level_Exception,
545 'csd.plsql.csd_cost_analysis_pvt.Get_TotalEstCharges',
546 'SQL Message['
547 || sqlerrm
548 || ']');
549 END IF;
550 END Get_TotalEstCharges;
551 /*----------------------------------------------------------------*/
552
553 /* procedure name: Compare_EstChargesAndCosts */
554
555 /* description : procedure used to compare estimated charges to */
556
557 /* estimated costs */
558
559 /* */
560
561 /* p_api_version Standard IN param */
562
563 /* p_commit Standard IN param */
564
565 /* p_init_msg_list Standard IN param */
566
567 /* p_validation_level Standard IN param */
568
569 /* p_repair_estimate_id Required Estimate ID to get est charges */
570
571 /* p_organization_id Required Organization Id */
572
573 /* p_ro_currency_code Required Repair Order Currency Code */
574
575 /* x_charges Total MLE charges for estimate */
576
577 /* x_costs MLE Costs for estimate */
578
579 /* x_profit MLE Profit */
580
581 /* x_profit_margin MLE Profit Margin (%) */
582
583 /* x_return_status Standard OUT param */
584
585 /* x_msg_count Standard OUT param */
586
587 /* x_msg_data Standard OUT param */
588
589 /* */
590
591 /*----------------------------------------------------------------*/
592
593 PROCEDURE Compare_EstChargesAndCosts(p_api_version IN NUMBER,
594 p_commit IN VARCHAR2,
595 p_init_msg_list IN VARCHAR2,
596 p_validation_level IN NUMBER,
597 x_return_status OUT NOCOPY VARCHAR2,
598 x_msg_count OUT NOCOPY NUMBER,
599 x_msg_data OUT NOCOPY VARCHAR2,
600 p_repair_estimate_id IN NUMBER,
601 p_organization_id IN NUMBER,
602 p_ro_currency_code IN VARCHAR2,
603 x_charges OUT NOCOPY CSD_COST_ANALYSIS_UTIL.MLE_TOTALS_REC_TYPE,
604 x_costs OUT NOCOPY CSD_COST_ANALYSIS_UTIL.MLE_TOTALS_REC_TYPE,
605 x_profit OUT NOCOPY CSD_COST_ANALYSIS_UTIL.MLE_TOTALS_REC_TYPE,
606 x_profit_margin OUT NOCOPY CSD_COST_ANALYSIS_UTIL.MLE_TOTALS_REC_TYPE)
607 IS
608 l_api_name CONSTANT VARCHAR2(30) := 'Compare_EstChargesAndCosts';
609 l_api_version CONSTANT NUMBER := 1.0;
610
611 -- Variable used in FND log
612
613 l_stat_level number := FND_LOG.LEVEL_STATEMENT;
614 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
615 l_event_level number := FND_LOG.LEVEL_EVENT;
616 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
617 l_error_level number := FND_LOG.LEVEL_ERROR;
618 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
619 l_mod_name varchar2(2000) := 'csd.plsql.csd_cost_analysis_pvt.Compare_EstChargesAndCosts';
620
621 BEGIN
622
623 -- Standard Start of API savepoint to the database
624 --- No need to have savepoint because no update
625
626 -- SAVEPOINT Compare_EstChargesAndCosts_Pvt;
627
628 -- Standard call to check for call compatibility.
629
630 IF NOT FND_API.Compatible_API_Call(l_api_version,
631 p_api_version,
632 l_api_name,
633 G_PKG_NAME)
634 THEN
635 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
636 END IF;
637
638 -- Initialize message list if p_init_msg_list is set to TRUE.
639
640 IF FND_API.to_Boolean(p_init_msg_list)
641 THEN
642 FND_MSG_PUB.initialize;
643 END IF;
644
645 -- Initialize API return status to success
646
647 x_return_status := FND_API.G_RET_STS_SUCCESS;
648
649 --
650 -- Begin API Body
651 --
652
653 -- Debug messages
654
655 IF (Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level)
656 THEN
657 FND_LOG.STRING(Fnd_Log.Level_Procedure,
658 l_mod_name
659 || 'BEGIN',
660 'Entered Compare_EstChargesAndCosts');
661 END IF;
662
663 -- Get charges for the estimate lines
664
665 Get_TotalEstCharges(p_api_version => p_api_version,
666 p_commit => p_commit,
667 p_init_msg_list => p_init_msg_list,
668 p_validation_level => p_validation_level,
669 p_repair_estimate_id => p_repair_estimate_id,
670 p_ro_currency_code => p_ro_currency_code,
671 x_charges => x_charges,
672 x_return_status => x_return_status,
673 x_msg_count => x_msg_count,
674 x_msg_data => x_msg_data);
675
676 -- Throw exception if API fails.
677
678 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
679 THEN
680 RAISE FND_API.G_EXC_ERROR;
681 END IF;
682
683 -- Get Costs for the estimate line
684
685 Get_TotalEstCosts(p_api_version => p_api_version,
686 p_commit => p_commit,
687 p_init_msg_list => p_init_msg_list,
688 p_validation_level => p_validation_level,
689 p_repair_estimate_id => p_repair_estimate_id,
690 p_organization_id => p_organization_id,
691 p_ro_currency_code => p_ro_currency_code,
692 x_costs => x_costs,
693 x_return_status => x_return_status,
694 x_msg_count => x_msg_count,
695 x_msg_data => x_msg_data);
696
697 -- Throw exception if API fails.
698
699 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
700 THEN
701 RAISE FND_API.G_EXC_ERROR;
702 END IF;
703
704 -- Throw exception if charge and cost currency codes are different.
705
706 IF x_charges.currency_code <> x_costs.currency_code
707 THEN
708 FND_MESSAGE.SET_NAME('CSD', 'CSD_CST_CURR_CODE_DIFF');
709 IF (Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level)
710 THEN
711 FND_LOG.MESSAGE(Fnd_Log.Level_Error, l_mod_name, FALSE);
712 END IF;
713 --ELSE
714 FND_MSG_PUB.ADD;
715 -- END IF;
716 RAISE FND_API.G_EXC_ERROR;
717 END IF;
718
719 --Compare charges and costs amount and return the profit and percent profit.
720
721 CSD_COST_ANALYSIS_UTIL.Compare_MLETotals(p_api_version => p_api_version,
722 p_commit => p_commit,
723 p_init_msg_list => p_init_msg_list,
724 p_validation_level => p_validation_level,
725 p_mle_totals_basis => x_costs,
726 p_mle_totals_compare => x_charges,
727 x_diff => x_profit,
728 x_pct_diff => x_profit_margin,
729 x_return_status => x_return_status,
730 x_msg_count => x_msg_count,
731 x_msg_data => x_msg_data);
732
733 --Throw exception if API fails.
734
735 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
736 THEN
737 RAISE FND_API.G_EXC_ERROR;
738 END IF;
739 IF (FND_LOG.LEVEL_PROCEDURE >= Fnd_Log.G_Current_Runtime_Level)
740 THEN
741 FND_LOG.STRING(Fnd_Log.Level_Procedure,
742 'csd.plsql.csd_cost_analysis_pvt.Compare_EstChargesAndCosts.END',
743 'Exiting Compare_EstChargesAndCosts');
744 END IF;
745
746 --
747 -- End API Body
748 --
749
750 -- Standard check of p_commit.
751
752 IF FND_API.To_Boolean(p_commit)
753 THEN
754 COMMIT WORK;
755 END IF;
756
757 -- Standard call to get message count and IF count is get message info.
758
759 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
760 EXCEPTION
761 WHEN FND_API.G_EXC_ERROR THEN
762
763 --ROLLBACK TO Compare_EstChargesAndCosts_Pvt;
764
765 x_return_status := FND_API.G_RET_STS_ERROR;
766 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
767 p_data => x_msg_data,
768 p_encoded => 'F');
769 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
770 THEN
771 FND_LOG.STRING(Fnd_Log.Level_Exception,
772 'csd.plsql.csd_cost_analysis_pvt.Compare_EstChargesAndCosts',
773 'EXC_ERROR['
774 || x_msg_data
775 || ']');
776 END IF;
777 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
778
779 -- ROLLBACK TO Compare_EstChargesAndCosts_Pvt;
780
781 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
782 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
783 p_data => x_msg_data,
784 p_encoded => 'F');
785 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
786 THEN
787 FND_LOG.STRING(Fnd_Log.Level_Exception,
788 'csd.plsql.csd_cost_analysis_pvt.Compare_EstChargesAndCosts',
789 'EXC_ERROR['
790 || x_msg_data
791 || ']');
792 END IF;
793 WHEN OTHERS THEN
794
795 --ROLLBACK TO Compare_EstChargesAndCosts_Pvt;
796
797 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
798 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
799 THEN
800 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
801 END IF;
802 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
803 p_data => x_msg_data,
804 p_encoded => 'F');
805 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
806 THEN
807 FND_LOG.STRING(Fnd_Log.Level_Exception,
808 'csd.plsql.csd_cost_analysis_pvt. Compare_EstChargesAndCosts',
809 'SQL Message['
810 || sqlerrm
811 || ']');
812 END IF;
813 END Compare_EstChargesAndCosts;
814 /*----------------------------------------------------------------*/
815
816 /* procedure name: Get_InvItemCost */
817
818 /* description : procedure used get item cost for an */
819
820 /* inventory item in the charges curency. CSD_REPAIR_ESTIMATE_LINES */
821 /* table will be populated with the converted item_cost. */
822
823 /* */
824
825 /* */
826
827 /* p_api_version Standard IN param */
828
829 /* p_commit Standard IN param */
830
831 /* p_init_msg_list Standard IN param */
832
833 /* p_validation_level Standard IN param */
834
835 /* p_inventory_item_id Inventory Item ID */
836
837 /* p_quantity Quantity of Inventory Items */
838
839 /* p_organization_id Inventory Organization ID */
840
841 /* p_charge_amt Total Charge Amt to compare to */
842
843 /* p_currency_code Currency of Charge Amt */
844
845 /* x_item_cost Item cost of Inv Item */
846
847 /* x_return_status Standard OUT param */
848
849 /* x_msg_count Standard OUT param */
850
851 /* x_msg_data Standard OUT param */
852
853 /* */
854
855 /*----------------------------------------------------------------*/
856
857 PROCEDURE Get_InvItemCost(p_api_version IN NUMBER, p_commit IN VARCHAR2,
858 p_init_msg_list IN VARCHAR2,
859 p_validation_level IN NUMBER,
860 x_return_status OUT NOCOPY VARCHAR2,
861 x_msg_count OUT NOCOPY NUMBER,
862 x_msg_data OUT NOCOPY VARCHAR2,
863 p_inventory_item_id IN NUMBER,
864 p_organization_id IN NUMBER,
865 p_charge_date IN DATE,
866 p_currency_code IN VARCHAR2,
867 p_chg_line_uom_code IN VARCHAR2, --
868 x_item_cost OUT NOCOPY NUMBER
869 )
870 IS
871 --Curcor to get item cost for an item. We only cosider standard/frozen costing type.
872 CURSOR cur_getItemCost(p_inventory_item_id NUMBER,
873 p_organization_id NUMBER)
874 IS
875 SELECT CIC.item_cost
876 FROM CST_ITEM_COSTS CIC
877 WHERE CIC.inventory_item_id = p_inventory_item_id
878 AND CIC.organization_id = p_organization_id
879 AND CIC.cost_type_id = 1; -- standard/frozen cost
880
881 --Cursor to get primary_uom_code (item cost uom) for a given item
882 CURSOR cur_getPrimaryUomCode(p_inventory_item_id NUMBER,
883 p_organization_id NUMBER)
884 IS
885 SELECT primary_uom_code
886 FROM mtl_system_items MSI
887 WHERE MSI.inventory_item_id = p_inventory_item_id
888 AND MSI.organization_id = p_organization_id;
889
890
891 l_api_name CONSTANT VARCHAR2(30) := 'Get_InvItemCost';
892 l_api_version CONSTANT NUMBER := 1.0;
893 l_cost_currency_code VARCHAR2(30);
894 l_item_cost NUMBER;
895 l_primary_uom_code VARCHAR2(30);
896
897
898 -- Variable used in FND log
899
900 l_stat_level number := FND_LOG.LEVEL_STATEMENT;
901 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
902 l_event_level number := FND_LOG.LEVEL_EVENT;
903 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
904 l_error_level number := FND_LOG.LEVEL_ERROR;
905 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
906 l_mod_name varchar2(2000) := 'csd.plsql.csd_cost_analysis_pvt.Get_InvItemCost';
907
908 BEGIN
909
910 -- Standard Start of API savepoint
911
912 SAVEPOINT Get_InvItemCostAnalysis_Pvt;
913
914 -- Standard call to check for call compatibility.
915
916 IF NOT FND_API.Compatible_API_Call(l_api_version,
917 p_api_version,
918 l_api_name,
919 G_PKG_NAME)
920 THEN
921 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
922 END IF;
923
924 -- Initialize message list if p_init_msg_list is set to TRUE.
925
926 IF FND_API.to_Boolean(p_init_msg_list)
927 THEN
928 FND_MSG_PUB.initialize;
929 END IF;
930
931 -- Initialize API return status to success
932
933 x_return_status := FND_API.G_RET_STS_SUCCESS;
934
935 --
936 -- Begin API Body
937 --
938
939 IF (Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level)
940 THEN
941 FND_LOG.STRING(Fnd_Log.Level_Procedure,
942 'csd.plsql.csd_cost_analysis_pvt.Get_InvItemCost.BEGIN',
943 'Entering Get_InvItemCost');
944 END IF;
945
946 --Check if costing enabled
947
948 IF (CSD_COST_ANALYSIS_UTIL.Validate_CostingEnabled(p_organization_id))
949 THEN
950
951 -- Get item cost
952
953 OPEN cur_getItemCost(p_inventory_item_id, p_organization_id);
954 FETCH cur_getItemCost INTO l_item_cost;
955 IF cur_getItemCost%NOTFOUND
956 THEN
957 CLOSE cur_getItemCost;
958 FND_MESSAGE.SET_NAME('CSD', 'CSD_CST_NO_ITEM_COST_AVAIL');
959 IF (Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level)
960 THEN
961 FND_LOG.MESSAGE(Fnd_Log.Level_Error, l_mod_name, FALSE);
962 END IF;
963 -- ELSE
964 FND_MSG_PUB.ADD;
965 --END IF;
966 RAISE FND_API.G_EXC_ERROR;
967 END IF;
968 CLOSE cur_getItemCost;
969
970 -- Get primary uom code from inventory for the item.
971
972 OPEN cur_getPrimaryUomCode(p_inventory_item_id, p_organization_id);
973 FETCH cur_getPrimaryUomCode INTO l_primary_uom_code;
974 IF cur_getPrimaryUomCode%NOTFOUND
975 THEN
976 CLOSE cur_getPrimaryUomCode;
977 FND_MESSAGE.SET_NAME('CSD', 'CSD_CST_NO_PRIMARY_UOM_CODE'); --new message
978 IF (Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level)
979 THEN
980 FND_LOG.MESSAGE(Fnd_Log.Level_Error, l_mod_name, FALSE);
981 END IF;
982 -- ELSE
983 FND_MSG_PUB.ADD;
984 --END IF;
985 RAISE FND_API.G_EXC_ERROR;
986 END IF;
987 CLOSE cur_getPrimaryUomCode;
988
989 --Raise exception if the charge line UOM is different from item primary uom
990 --Cannot do cost analysis if the UUOMs are different
991 IF ( l_primary_uom_code <> p_chg_line_uom_code) THEN
992 FND_MESSAGE.SET_NAME('CSD', 'CSD_CST_DIFF_UOM_CODE'); --new message
993 IF (Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level)
994 THEN
995 FND_LOG.MESSAGE(Fnd_Log.Level_Error, l_mod_name, FALSE);
996 END IF;
997 -- ELSE
998 FND_MSG_PUB.ADD;
999 --END IF;
1000 RAISE FND_API.G_EXC_ERROR;
1001 END IF;
1002 l_cost_currency_code := CSD_COST_ANALYSIS_UTIL.get_GLCurrencyCode(p_organization_id);
1003
1004 --do the conversion if currency codes differ.
1005
1006 IF (p_currency_code <> l_cost_currency_code)
1007 THEN
1008 CSD_COST_ANALYSIS_UTIL.Convert_CurrencyAmount(p_api_version => p_api_version,
1009 p_commit => p_commit,
1010 p_init_msg_list => p_init_msg_list,
1011 p_validation_level => p_validation_level,
1012 p_from_currency => l_cost_currency_code,
1013 p_to_currency => p_currency_code,
1014 p_eff_date => p_charge_date,
1015 p_amount => l_item_cost,
1016 x_conv_amount => x_item_cost,
1017 x_return_status => x_return_status,
1018 x_msg_count => x_msg_count,
1019 x_msg_data => x_msg_data);
1020 ELSE
1021
1022 -- no need to convert becuase currency is same. Just set the variable.
1023
1024 x_item_cost := l_item_cost;
1025 END IF;
1026
1027 -- Throw exception if API fails.
1028
1029 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1030 THEN
1031 RAISE FND_API.G_EXC_ERROR;
1032 END IF;
1033 END IF;
1034 IF (Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level)
1035 THEN
1036 FND_LOG.STRING(Fnd_Log.Level_Procedure,
1037 'csd.plsql.csd_cost_analysis_pvt.Get_InvItemCost.END',
1038 'Exiting Get_InvItemCost');
1039 END IF;
1040
1041 --
1042 -- End API Body
1043 --
1044
1045 -- Standard check of p_commit.
1046
1047 IF FND_API.To_Boolean(p_commit)
1048 THEN
1049 COMMIT WORK;
1050 END IF;
1051
1052 -- Standard call to get message count and IF count is get message info.
1053
1054 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1055 EXCEPTION
1056 WHEN FND_API.G_EXC_ERROR THEN
1057 ROLLBACK TO Get_InvItemCostAnalysis_Pvt;
1058 x_return_status := FND_API.G_RET_STS_ERROR;
1059 x_item_cost := NULL;
1060 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1061 p_data => x_msg_data,
1062 p_encoded => 'F');
1063 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
1064 THEN
1065 FND_LOG.STRING(Fnd_Log.Level_Exception,
1066 'csd.plsql.csd_cost_analysis_pvt.Get_InvItemCost',
1067 'EXC_ERROR['
1068 || x_msg_data
1069 || ']');
1070 END IF;
1071 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1072 ROLLBACK TO Get_InvItemCostAnalysis_Pvt;
1073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1074 x_item_cost := NULL;
1075 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1076 p_data => x_msg_data,
1077 p_encoded => 'F');
1078 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
1079 THEN
1080 FND_LOG.STRING(Fnd_Log.Level_Exception,
1081 'csd.plsql.csd_cost_analysis_pvt.Get_InvItemCost',
1082 'EXC_ERROR['
1083 || x_msg_data
1084 || ']');
1085 END IF;
1086 WHEN OTHERS THEN
1087 ROLLBACK TO Get_InvItemCostAnalysis_Pvt;
1088 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1089 x_item_cost := NULL;
1090 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1091 THEN
1092 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1093 END IF;
1094 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1095 p_data => x_msg_data,
1096 p_encoded => 'F');
1097 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
1098 THEN
1099 FND_LOG.STRING(Fnd_Log.Level_Exception,
1100 'csd.plsql.csd_cost_analysis_pvt.Get_InvItemCost',
1101 'SQL Message['
1102 || sqlerrm
1103 || ']');
1104 END IF;
1105 END Get_InvItemCost;
1106 /*----------------------------------------------------------------*/
1107
1108 /* procedure name: Get_ResItemCost */
1109
1110 /* description : procedure used get resource item cost */
1111
1112 /* for a bom resource */
1113 /* This will be called to obtain labor item cost. If a resource id is */
1114 /* passed, then resource cost will be returned in x_item_Cost */
1115 /* If resource_id is not passed then cost is obtained from the labor */
1116 /* inventory item. */
1117
1118 /* This is a new API. During code review it was suggested that */
1119
1120 /* since it will never be called independently, there is no need */
1121 /* to use standard input parameters. Hence I am removing them in */
1122 /* the new API. */
1123
1124 /* x_return_status Standard OUT param */
1125
1126 /* x_msg_count Standard OUT param */
1127
1128 /* x_msg_data Standard OUT param */
1129
1130 /* p_inventory_item_id Inventory item id for the labor item */
1131 /* p_organization_id Inventory Organization ID (Service validation org) */
1132
1133 /* p_bom_resource_id BOM Resource ID */
1134 /*p_charge_date Charge date */
1135
1136 /* p_currency_code Currency of Charge Amt */
1137
1138 /* x_item_cost Resource rate of BOM resource */
1139
1140
1141 /* */
1142
1143 /*----------------------------------------------------------------*/
1144
1145
1146 PROCEDURE Get_ResItemCost
1147 (
1148 x_return_status OUT NOCOPY VARCHAR2,
1149 x_msg_count OUT NOCOPY NUMBER,
1150 x_msg_data OUT NOCOPY VARCHAR2,
1151 p_inventory_item_id IN NUMBER,
1152 p_organization_id IN NUMBER,
1153 p_bom_resource_id IN NUMBER,
1154 p_charge_date IN DATE,
1155 p_currency_code IN VARCHAR2,
1156 p_chg_line_uom_code IN VARCHAR2,
1157 x_item_cost OUT NOCOPY NUMBER
1158 )
1159 IS
1160 --Cursor to get item cost for an item. We only cosider standard/frozen costing type.
1161 CURSOR cur_getItemCost(p_inventory_item_id NUMBER,
1162 p_organization_id NUMBER)
1163 IS
1164 SELECT CIC.item_cost
1165 FROM CST_ITEM_COSTS CIC
1166 WHERE CIC.inventory_item_id = p_inventory_item_id
1167 AND CIC.organization_id = p_organization_id
1168 AND CIC.cost_type_id = 1; -- standard/frozen cost
1169
1170 --Cursor to get resource cost for a resource id. We only consider standard/frozen costing type.
1171 CURSOR cur_getResCost(p_bom_resorce_id NUMBER,
1172 p_organization_id NUMBER)
1173 IS
1174 SELECT CRC.resource_rate
1175 FROM cst_resource_costs CRC
1176 WHERE CRC.resource_id = p_bom_resource_id
1177 AND CRC.organization_id = p_organization_id
1178 AND CRC.cost_type_id = 1; -- standard/frozen cost
1179
1180 --Cursor to get resource UOM code for the given resource id
1181 CURSOR cur_getResUOMCode (p_bom_resource_id NUMBER)
1182 IS
1183 SELECT BR.unit_of_measure
1184 FROM BOM_RESOURCES BR
1185 WHERE BR.resource_id = p_bom_resource_id;
1186
1187
1188 l_api_name CONSTANT VARCHAR2(30) := 'Get_ResItemCost';
1189 l_api_version CONSTANT NUMBER := 1.0;
1190 l_cost_currency_code VARCHAR2(30);
1191 l_item_cost NUMBER;
1192 l_res_uom_code VARCHAR2(30);
1193 p_api_version CONSTANT NUMBER := 1.0;
1194 p_commit CONSTANT VARCHAR2(1) := 'F';
1195 p_init_msg_list CONSTANT VARCHAR2(1) := 'T';
1196 p_validation_level CONSTANT NUMBER :=fnd_api.g_valid_level_full;
1197 -- Variable used in FND log
1198
1199 l_stat_level number := FND_LOG.LEVEL_STATEMENT;
1200 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
1201 l_event_level number := FND_LOG.LEVEL_EVENT;
1202 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
1203 l_error_level number := FND_LOG.LEVEL_ERROR;
1204 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
1205 l_mod_name varchar2(2000) := 'csd.plsql.csd_cost_analysis_pvt.Get_InvItemCost';
1206
1207 BEGIN
1208 -- Initialize API return status to success
1209
1210 x_return_status := FND_API.G_RET_STS_SUCCESS;
1211
1212 --
1213 -- Begin API Body
1214 --
1215
1216 IF (Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level)
1217 THEN
1218 FND_LOG.STRING(Fnd_Log.Level_Procedure,
1219 'csd.plsql.csd_cost_analysis_pvt.Get_ResItemCost.BEGIN',
1220 'Entering Get_ResItemCost');
1221 END IF;
1222
1223 --Check if costing enabled
1224
1225 IF (CSD_COST_ANALYSIS_UTIL.Validate_CostingEnabled(p_organization_id))
1226 THEN
1227
1228 --Check if the resource id was passed. If so, then get the resource cost
1229 If p_bom_resource_id is not null THEN
1230 IF (Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level)
1231 THEN
1232 FND_LOG.STRING(Fnd_Log.Level_Statement,
1233 'csd.plsql.csd_cost_analysis_pvt.Get_ResItemCost',
1234 'Resource id :'|| p_bom_resource_id);
1235 END IF;
1236
1237 -- Check if the resource UOM is different from Charge line UOM.
1238 -- If it is, then null out the item cost and exit. User will get here intentionally
1239 -- because when user is selecting a resource and UOM does not match,
1240 -- he is warned about it.
1241 OPEN cur_getResUOMCode(p_bom_resource_id);
1242 FETCH cur_getResUOMCode into l_Res_uom_code;
1243 CLOSE cur_getResUOMCode;
1244 --codes are different, let the user know before nulling out the item cost
1245 IF (l_res_uom_code <> p_chg_line_uom_code ) THEN
1246 FND_MESSAGE.SET_NAME('CSD', 'CSD_CST_DIFF_UOM_CODE'); --new message
1247 IF (Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level)
1248 THEN
1249 FND_LOG.MESSAGE(Fnd_Log.Level_Error, l_mod_name, FALSE);
1250 END IF;
1251
1252 FND_MSG_PUB.ADD;
1253
1254 RAISE FND_API.G_EXC_ERROR;
1255 END IF;
1256
1257 OPEN cur_getResCost(p_bom_resource_id, p_organization_id);
1258 FETCH cur_getResCost INTO l_item_cost;
1259 -- IF cur_getResCost%NOTFOUND
1260 -- THEN
1261 -- CLOSE cur_getResCost;
1262 -- END IF;
1263 CLOSE cur_getResCost;
1264 END IF; --if p_bom_resource_id
1265
1266 --no resource id was passed or item cost
1267 -- based on resource id was null, derive the labor item cost instead.
1268 -- Get item cost
1269 IF ( p_bom_resource_id is null OR l_item_cost is null ) then
1270 IF (Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level)
1271 THEN
1272 FND_LOG.STRING(Fnd_Log.Level_Statement,
1273 'csd.plsql.csd_cost_analysis_pvt.Get_ResItemCost',
1274 'No resource information. Deriving Labor item cost');
1275 END IF;
1276 OPEN cur_getItemCost(p_inventory_item_id, p_organization_id);
1277 FETCH cur_getItemCost INTO l_item_cost;
1278 IF cur_getItemCost%NOTFOUND
1279 THEN
1280 CLOSE cur_getItemCost;
1281 FND_MESSAGE.SET_NAME('CSD', 'CSD_CST_NO_ITEM_COST_AVAIL');
1282 IF (Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level)
1283 THEN
1284 FND_LOG.MESSAGE(Fnd_Log.Level_Error, l_mod_name, FALSE);
1285 END IF;
1286 -- ELSE
1287 FND_MSG_PUB.ADD;
1288 --END IF;
1289 RAISE FND_API.G_EXC_ERROR;
1290 END IF;
1291 CLOSE cur_getItemCost;
1292 END IF;
1293 l_cost_currency_code := CSD_COST_ANALYSIS_UTIL.get_GLCurrencyCode(p_organization_id);
1294
1295 --do the conversion if currency codes differ.
1296
1297 IF (p_currency_code <> l_cost_currency_code)
1298 THEN
1299 CSD_COST_ANALYSIS_UTIL.Convert_CurrencyAmount(p_api_version => p_api_version,
1300 p_commit => p_commit,
1301 p_init_msg_list => p_init_msg_list,
1302 p_validation_level => p_validation_level,
1303 p_from_currency => l_cost_currency_code,
1304 p_to_currency => p_currency_code,
1305 p_eff_date => p_charge_date,
1306 p_amount => l_item_cost,
1307 x_conv_amount => x_item_cost,
1308 x_return_status => x_return_status,
1309 x_msg_count => x_msg_count,
1310 x_msg_data => x_msg_data);
1311 ELSE
1312
1313 -- no need to convert becuase currency is same. Just set the variable.
1314
1315 x_item_cost := l_item_cost;
1316 END IF;
1317
1318 -- Throw exception if API fails.
1319
1320 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1321 THEN
1322 RAISE FND_API.G_EXC_ERROR;
1323 END IF;
1324 END IF;
1325 IF (Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level)
1326 THEN
1327 FND_LOG.STRING(Fnd_Log.Level_Procedure,
1328 'csd.plsql.csd_cost_analysis_pvt.Get_ResItemCost.END',
1329 'Exiting Get_InvItemCost');
1330 END IF;
1331
1332 --
1333 -- End API Body
1334 --
1335
1336 -- Standard call to get message count and IF count is get message info.
1337
1338 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1339 EXCEPTION
1340 WHEN FND_API.G_EXC_ERROR THEN
1341 x_return_status := FND_API.G_RET_STS_ERROR;
1342 x_item_cost := NULL;
1343 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1344 p_data => x_msg_data,
1345 p_encoded => 'F');
1346 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
1347 THEN
1348 FND_LOG.STRING(Fnd_Log.Level_Exception,
1349 'csd.plsql.csd_cost_analysis_pvt.Get_ResItemCost',
1350 'EXC_ERROR['
1351 || x_msg_data
1352 || ']');
1353 END IF;
1354 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1355
1356 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1357 x_item_cost := NULL;
1358 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1359 p_data => x_msg_data,
1360 p_encoded => 'F');
1361 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
1362 THEN
1363 FND_LOG.STRING(Fnd_Log.Level_Exception,
1364 'csd.plsql.csd_cost_analysis_pvt.Get_ResItemCost',
1365 'EXC_ERROR['
1366 || x_msg_data
1367 || ']');
1368 END IF;
1369 WHEN OTHERS THEN
1370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1371 x_item_cost := NULL;
1372 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1373 THEN
1374 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1375 END IF;
1376 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1377 p_data => x_msg_data,
1378 p_encoded => 'F');
1379 IF (Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level)
1380 THEN
1381 FND_LOG.STRING(Fnd_Log.Level_Exception,
1382 'csd.plsql.csd_cost_analysis_pvt.Get_ResItemCost',
1383 'SQL Message['
1384 || sqlerrm
1385 || ']');
1386 END IF;
1387 END get_ResItemCost;
1388
1389
1390 END CSD_COST_ANALYSIS_PVT;
[Home] [Help]