[Home] [Help]
PACKAGE BODY: APPS.GMF_RESOURCECOST_PVT
Source
1 PACKAGE BODY GMF_ResourceCost_PVT AS
2 /* $Header: GMFVRESB.pls 120.2 2011/05/05 13:30:53 phiriyan ship $ */
3
4 -- Start of comments
5 --+==========================================================================+
6 --| Copyright (c) 1998 Oracle Corporation |
7 --| Redwood Shores, CA, USA |
8 --| All rights reserved. |
9 --+==========================================================================+
10 --| File Name : GMFVRESB.pls |
11 --| Package Name : GMF_ResourceCost_PVT |
12 --| API name : GMF_ResourceCost_PVT |
13 --| Type : Private |
14 --| Pre-reqs : N/A |
15 --| Function : Resource Cost creation, updatation and |
16 --| deletetion. |
17 --| Parameters : N/A |
18 --| |
19 --| Current Vers : 2.0 |
20 --| Previous Vers : 1.0 |
21 --| Initial Vers : 1.0 |
22 --| |
23 --| Contents |
24 --| Create_Resource_Cost |
25 --| Update_Resource_Cost |
26 --| Delete_Resource_Cost |
27 --| |
28 --| Notes |
29 --| This package contains private procedures relating to Resource Cost |
30 --| creation, updatation and deletetion. |
31 --| |
32 --| HISTORY |
33 --| 27/Feb/2001 Uday Moogala Created Bug# 1418689 |
34 --| |
35 --| 30-OCT-2002 RajaSekhar Bug#2641405 Added NOCOPY hint |
36 --| 05/NOV/2002 Uday Moogala Bug# 2659435 |
37 --| Performance related fixes. |
38 --| 1. remove G_MISS_xxx assignments. |
39 --| 2. Conditionally calling debug routine. |
40 --| Also, fixed issues found during unit testing. Search for the bug |
41 --| number to find the fixes. |
42 --| 22/Nov/2005 Prasad Marada Bug 4689137, API changes for convergence |
43 --| 28-Apr-2011 Pramod B.H. Bug# 12404853 |
44 --| Modified procedures Update_Resource_Cost and Get_Resource_Cost to |
45 --| handle the condition of Organization_id not mandatory in cm_rsrc_dtl|
46 --+==========================================================================+
47 -- End of comments
48
49 --
50
51
52 PROCEDURE log_msg -- Bug 2659435: Removed first paramter for debug level
53 (
54 p_msg_text IN VARCHAR2
55 );
56 --
57
58 -- Global variables
59 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMF_ResourceCost_PVT';
60
61 G_debug_level NUMBER(2) := FND_MSG_PUB.G_Msg_Level_Threshold; -- Use this variable everywhere
62 -- to decide to log a debug msg.
63
64
65 --Start of comments
66 --+========================================================================+
67 --| API Name : Create_Resource_Cost |
68 --| TYPE : Private |
69 --| Function : Creates a new Resource Cost based on the input |
70 --| into table CM_RSRC_DTL |
71 --| Pre-reqa : None. |
72 --| Parameters : |
73 --| IN : |
74 --| p_api_version IN NUMBER - Required |
75 --| p_init_msg_list IN VARCHAR2 - Optional |
76 --| p_commit IN VARCHAR2 - Optional |
77 --| p_resource_cost_rec IN Resource_Cost_Rec_Type |
78 --| OUT : |
79 --| x_return_status OUT VARCHAR2 |
80 --| x_msg_count OUT NUMBER |
81 --| x_msg_data OUT VARCHAR2 |
82 --| |
83 --| Version : |
84 --| Current Version : 2.0 |
85 --| Previous Version : 1.0 |
86 --| Initial Version : 1.0 |
87 --| |
88 --| Notes : |
89 --| |
90 --| HISTORY |
91 --| 01-Mar-01 Uday Moogala - Created |
92 --| 01-Oct-05 Prasad marada - Modified as per inventory convergence |
93 --| |
94 --+========================================================================+
95 -- End of comments
96
97 PROCEDURE Create_Resource_Cost
98 ( p_api_version IN NUMBER ,
99 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
100 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
101
102 x_return_status OUT NOCOPY VARCHAR2 ,
103 x_msg_count OUT NOCOPY NUMBER ,
104 x_msg_data OUT NOCOPY VARCHAR2 ,
105
106 p_resource_cost_rec IN GMF_ResourceCost_PUB.Resource_Cost_Rec_Type ,
107 p_user_id IN NUMBER
108 )
109 IS
110 l_api_name CONSTANT VARCHAR2(30) := 'Create_Resource_Cost' ;
111 l_api_version CONSTANT NUMBER := 2.0 ;
112
113 --l_rsrc_cost_rec Resource_Cost_Rec_Type ;
114 --l_return_status VARCHAR2(2) ;
115 BEGIN
116
117 -- Standard Start of API savepoint
118 SAVEPOINT Create_Resource_Cost_PVT;
119
120 -- Initialize message list if p_init_msg_list is set to TRUE.
121 IF FND_API.to_Boolean( p_init_msg_list ) THEN
122 FND_MSG_PUB.initialize;
123 END IF;
124
125 -- Standard call to check for call compatibility.
126 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
127 p_api_version ,
128 l_api_name ,
129 G_PKG_NAME )
130 THEN
131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
132 END IF;
133
134 -- Initialize API return status to success
135 x_return_status := FND_API.G_RET_STS_SUCCESS;
136
137 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
138 log_msg('Inserting record for resource : ' || p_resource_cost_rec.resources ||
139 ' Legal Entity Id : ' || p_resource_cost_rec.legal_entity_id ||
140 ' Organization id : ' || p_resource_cost_rec.organization_id ||
141 ' Period Id : ' || p_resource_cost_rec.period_id ||
142 ' Cost type id : '|| p_resource_cost_rec.cost_type_id);
143 END IF;
144
145 INSERT INTO cm_rsrc_dtl
146 (
147 resources
148 , nominal_cost
149 , text_code
150 , delete_mark
151 , rollover_ind
152 , creation_date
153 , created_by
154 , last_update_date
155 , last_updated_by
156 , trans_cnt
157 , last_update_login
158 , organization_id
159 , cost_type_id
160 , period_id
161 , usage_uom
162 , legal_entity_id
163 )
164 VALUES
165 (
166 p_resource_cost_rec.resources
167 , p_resource_cost_rec.nominal_cost
168 , '' -- text code
169 , 0 -- delete mark
170 , 0 -- rollover Indicator
171 , sysdate
172 , p_user_id
173 , sysdate
174 , p_user_id
175 , '' -- transaction count (not in use)
176 , FND_GLOBAL.LOGIN_ID
177 , p_resource_cost_rec.organization_id
178 , p_resource_cost_rec.cost_type_id
179 , p_resource_cost_rec.period_id
180 , p_resource_cost_rec.usage_uom
181 , p_resource_cost_rec.legal_entity_id
182 );
183
184 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
185 log_msg('1 row inserted');
186 END IF;
187
188 -- Standard check of p_commit.
189 IF FND_API.To_Boolean( p_commit ) THEN
190 COMMIT WORK;
191 END IF;
192
193 -- Standard call to get message count and if count is 1, get message info.
194 FND_MSG_PUB.Count_And_Get
195 ( p_count => x_msg_count ,
196 p_data => x_msg_data
197 );
198
199 EXCEPTION
200 WHEN FND_API.G_EXC_ERROR THEN
201 ROLLBACK TO Create_Resource_Cost_PVT;
202 x_return_status := FND_API.G_RET_STS_ERROR ;
203 FND_MSG_PUB.Count_And_Get
204 ( p_count => x_msg_count ,
205 p_data => x_msg_data
206 );
207 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
208 ROLLBACK TO Create_Resource_Cost_PVT;
209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
210 FND_MSG_PUB.Count_And_Get
211 ( p_count => x_msg_count ,
212 p_data => x_msg_data
213 );
214 WHEN OTHERS THEN
215 ROLLBACK TO Create_Resource_Cost_PVT;
216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
217 IF FND_MSG_PUB.Check_Msg_Level
218 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
219 THEN
220 FND_MSG_PUB.Add_Exc_Msg
221 ( G_PKG_NAME ,
222 l_api_name
223 );
224 END IF;
225 FND_MSG_PUB.Count_And_Get
226 ( p_count => x_msg_count ,
227 p_data => x_msg_data
228 );
229
230 END Create_Resource_Cost;
231
232
233 --Start of comments
234 --+========================================================================+
235 --| API Name : Update_Resource_Cost |
236 --| TYPE : Private |
237 --| Function : Updates Resource Cost based on the input |
238 --| into CM_RSRC_DTL |
239 --| Pre-reqa : None. |
240 --| Parameters : |
241 --| IN : |
242 --| p_api_version IN NUMBER - Required |
243 --| p_init_msg_list IN VARCHAR2 - Optional |
244 --| p_commit IN VARCHAR2 - Optional |
245 --| p_resource_cost_rec IN Resource_Cost_Rec_Type |
246 --| OUT : |
250 --| |
247 --| x_return_status OUT VARCHAR2 |
248 --| x_msg_count OUT NUMBER |
249 --| x_msg_data OUT VARCHAR2 |
251 --| Version : |
252 --| Current Version : 2.0 |
253 --| Previous Version : 1.0 |
254 --| Initial Version : 1.0 |
255 --| |
256 --| Notes : |
257 --| |
258 --| HISTORY |
259 --| 01-Mar-01 Uday Moogala - Created |
260 --| |
261 --+========================================================================+
262 -- End of comments
263
264 PROCEDURE Update_Resource_Cost
265 ( p_api_version IN NUMBER ,
266 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
267 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
268
269 x_return_status OUT NOCOPY VARCHAR2 ,
270 x_msg_count OUT NOCOPY NUMBER ,
271 x_msg_data OUT NOCOPY VARCHAR2 ,
272
273 p_resource_cost_rec IN GMF_ResourceCost_PUB.Resource_Cost_Rec_Type ,
274 p_user_id IN NUMBER
275 )
276 IS
277 l_api_name CONSTANT VARCHAR2(30) := 'Update_Resource_Cost' ;
278 l_api_version CONSTANT NUMBER := 2.0 ;
279
280 --l_rsrc_cost_rec Resource_Cost_Rec_Type ;
281 --l_no_rows_upd NUMBER(10) ;
282 --l_return_status VARCHAR2(2) ;
283
284 BEGIN
285 -- Standard Start of API savepoint
286 SAVEPOINT Update_Resource_Cost_PVT;
287
288 -- Initialize message list if p_init_msg_list is set to TRUE.
289 IF FND_API.to_Boolean( p_init_msg_list ) THEN
290 FND_MSG_PUB.initialize;
291 END IF;
292
293 -- Standard call to check for call compatibility.
294 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
295 p_api_version ,
296 l_api_name ,
297 G_PKG_NAME )
298 THEN
299 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
300 END IF;
301
302 -- Initialize API return status to success
303 x_return_status := FND_API.G_RET_STS_SUCCESS;
304
305 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
306 log_msg('Updating record for resource : ' || p_resource_cost_rec.resources ||
307 ' Legal Entity Id : ' || p_resource_cost_rec.legal_entity_id ||
308 ' Organization id : ' || p_resource_cost_rec.organization_id ||
309 ' Period Id : ' || p_resource_cost_rec.period_id ||
310 ' Cost type id : '|| p_resource_cost_rec.cost_type_id);
311 END IF;
312
313 UPDATE cm_rsrc_dtl
314 SET
315 -- Modified uage_um to usage_uom by pmarada
316 usage_uom = decode(p_resource_cost_rec.usage_uom,
317 FND_API.G_MISS_CHAR, NULL,
318 NULL, usage_uom,
319 p_resource_cost_rec.usage_uom)
320 ,nominal_cost = decode(p_resource_cost_rec.nominal_cost,
321 FND_API.G_MISS_NUM, NULL,
322 NULL, nominal_cost,
323 p_resource_cost_rec.nominal_cost)
324 ,delete_mark = decode(p_resource_cost_rec.delete_mark,
325 FND_API.G_MISS_NUM, NULL,
326 NULL, delete_mark,
327 p_resource_cost_rec.delete_mark)
328 ,last_update_date = sysdate
329 ,last_updated_by = p_user_id
330 ,last_update_login = FND_GLOBAL.LOGIN_ID
331 WHERE
332 legal_entity_id = p_resource_cost_rec.legal_entity_id
333 /*B12404853 - Added nvl as Organization_id is not mandatory in cm_rsrc_dtl (Refer B12333658)
334 AND organization_id = p_resource_cost_rec.organization_id */
335 AND nvl(organization_id,0) = nvl(p_resource_cost_rec.organization_id, 0) /*B12404853*/
336 AND resources = p_resource_cost_rec.resources
337 AND period_id = p_resource_cost_rec.period_id
338 AND cost_type_id = p_resource_cost_rec.cost_type_id
339 ;
340
341 -- Standard check of p_commit.
342 IF FND_API.To_Boolean( p_commit ) THEN
343 COMMIT WORK;
344 END IF;
345
346 -- Standard call to get message count and if count is 1, get message info.
347 FND_MSG_PUB.Count_And_Get
348 ( p_count => x_msg_count ,
349 p_data => x_msg_data
350 );
351
352 EXCEPTION
353 WHEN FND_API.G_EXC_ERROR THEN
354 ROLLBACK TO Update_Resource_Cost_PVT;
355 x_return_status := FND_API.G_RET_STS_ERROR ;
356 FND_MSG_PUB.Count_And_Get
357 ( p_count => x_msg_count ,
358 p_data => x_msg_data
359 );
360 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
361 ROLLBACK TO Update_Resource_Cost_PVT;
365 p_data => x_msg_data
362 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
363 FND_MSG_PUB.Count_And_Get
364 ( p_count => x_msg_count ,
366 );
367 WHEN OTHERS THEN
368 ROLLBACK TO Update_Resource_Cost_PVT;
369 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
370 IF FND_MSG_PUB.Check_Msg_Level
371 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
372 THEN
373 FND_MSG_PUB.Add_Exc_Msg
374 ( G_PKG_NAME ,
375 l_api_name
376 );
377 END IF;
378 FND_MSG_PUB.Count_And_Get
379 ( p_count => x_msg_count ,
380 p_data => x_msg_data
381 );
382
383 END Update_Resource_Cost ;
384
385 --Start of comments
386 --+========================================================================+
387 --| API Name : Get_Resource_Cost |
388 --| TYPE : Private |
389 --| Function : Retrive Resource Cost based on the input from table |
390 --| CM_RSRC_DTL |
391 --| Pre-reqa : None. |
392 --| Parameters : |
393 --| IN : |
394 --| p_api_version IN NUMBER - Required |
395 --| p_init_msg_list IN VARCHAR2 - Optional |
396 --| p_resource_cost_rec IN Resource_Cost_Rec_Type |
397 --| OUT : |
398 --| x_return_status OUT VARCHAR2 |
399 --| x_msg_count OUT NUMBER |
400 --| x_msg_data OUT VARCHAR2 |
401 --| x_resource_cost_rec OUT Resource_Cost_Rec_Type |
402 --| |
403 --| Version : |
404 --| Current Version : 2.0 |
405 --| Previous Version : 1.0 |
406 --| Initial Version : 1.0 |
407 --| |
408 --| Notes : |
409 --| |
410 --| HISTORY |
411 --| 26-Apr-01 Uday Moogala - Created |
412 --| 20-sep-2005 Prasad marada - included organization_id, cost_type_id, |
413 --| period_id etc..
414 --| |
415 --+========================================================================+
416 -- End of comments
417
418 PROCEDURE Get_Resource_Cost
419 ( p_api_version IN NUMBER ,
420 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
421
422 x_return_status OUT NOCOPY VARCHAR2 ,
423 x_msg_count OUT NOCOPY NUMBER ,
424 x_msg_data OUT NOCOPY VARCHAR2 ,
425
426 p_resource_cost_rec IN GMF_ResourceCost_PUB.Resource_Cost_Rec_Type ,
427 x_resource_cost_rec OUT NOCOPY GMF_ResourceCost_PUB.Resource_Cost_Rec_Type
428 )
429 IS
430 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Item_Cost' ;
431 l_api_version CONSTANT NUMBER := 2.0 ;
432
433 BEGIN
434
435 -- Standard Start of API savepoint
436 SAVEPOINT Get_Reousrce_Cost_PVT;
437
438 -- Initialize message list if p_init_msg_list is set to TRUE.
439 IF FND_API.to_Boolean( p_init_msg_list ) THEN
440 FND_MSG_PUB.initialize;
441 END IF;
442
443 -- Standard call to check for call compatibility.
444 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
445 p_api_version ,
446 l_api_name ,
447 G_PKG_NAME )
448 THEN
449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
450 END IF;
451
452 -- Initialize API return status to success
453 x_return_status := FND_API.G_RET_STS_SUCCESS;
454
455 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
456 log_msg('Beginning Private Get Resource Cost API.');
457 END IF;
458
459 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
460 log_msg('Retrieving Resource Costs for legal entity id : ' || p_resource_cost_rec.legal_entity_id ||
461 ' Organization Id : ' || p_resource_cost_rec.organization_id ||
462 ' Resource : ' || p_resource_cost_rec.resources ||
463 ' Period Id : ' || p_resource_cost_rec.Period_id ||
464 ' Cost type Id : ' || p_resource_cost_rec.cost_type_id ) ;
465 END IF;
466
467 x_resource_cost_rec.resources := p_resource_cost_rec.resources ;
468 x_resource_cost_rec.legal_entity_id := p_resource_cost_rec.legal_entity_id ;
469 x_resource_cost_rec.organization_id := p_resource_cost_rec.organization_id ;
470 x_resource_cost_rec.organization_code := p_resource_cost_rec.organization_code ;
474 x_resource_cost_rec.cost_type_id := p_resource_cost_rec.cost_type_id ;
471 x_resource_cost_rec.period_id := p_resource_cost_rec.period_id ;
472 x_resource_cost_rec.calendar_code := p_resource_cost_rec.calendar_code ;
473 x_resource_cost_rec.Period_code := p_resource_cost_rec.Period_code ;
475 x_resource_cost_rec.cost_mthd_code := p_resource_cost_rec.cost_mthd_code ;
476
477 SELECT
478 r.usage_uom
479 , r.nominal_cost
480 , r.delete_mark
481 , f.user_name
482 INTO
483 x_resource_cost_rec.usage_uom
484 , x_resource_cost_rec.nominal_cost
485 , x_resource_cost_rec.delete_mark
486 , x_resource_cost_rec.user_name
487 FROM
488 fnd_user f, cm_rsrc_dtl r
489 WHERE
490 legal_entity_id = p_resource_cost_rec.legal_entity_id
491 /*B12404853 - Added nvl as Organization_id is not mandatory in cm_rsrc_dtl (Refer B12333658)
492 AND organization_id = p_resource_cost_rec.organization_id */
493 AND nvl(organization_id,0) = nvl(p_resource_cost_rec.organization_id,0) /*B12404853*/
494 AND resources = p_resource_cost_rec.resources
495 AND period_id = p_resource_cost_rec.period_id
496 AND cost_type_id = p_resource_cost_rec.cost_type_id
497 AND f.user_id = r.last_updated_by
498 ;
499
500
501 /*
502 -- Standard check of p_commit.
503 IF FND_API.To_Boolean( p_commit ) THEN
504 COMMIT WORK;
505 END IF;
506 */
507
508 -- Standard call to get message count and if count is 1, get message info.
509 FND_MSG_PUB.Count_And_Get
510 ( p_count => x_msg_count ,
511 p_data => x_msg_data
512 );
513
514 EXCEPTION
515 WHEN FND_API.G_EXC_ERROR THEN
516 ROLLBACK TO Get_Reousrce_Cost_PVT;
517 x_return_status := FND_API.G_RET_STS_ERROR ;
518 FND_MSG_PUB.Count_And_Get
519 ( p_count => x_msg_count ,
520 p_data => x_msg_data
521 );
522 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
523 ROLLBACK TO Get_Reousrce_Cost_PVT;
524 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
525 FND_MSG_PUB.Count_And_Get
526 ( p_count => x_msg_count ,
527 p_data => x_msg_data
528 );
529 WHEN OTHERS THEN
530 ROLLBACK TO Get_Reousrce_Cost_PVT;
531 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
532 IF FND_MSG_PUB.Check_Msg_Level
533 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
534 THEN
535 FND_MSG_PUB.Add_Exc_Msg
536 ( G_PKG_NAME ,
537 l_api_name
538 );
539 END IF;
540 FND_MSG_PUB.Count_And_Get
541 ( p_count => x_msg_count ,
542 p_data => x_msg_data
543 );
544 END Get_Resource_Cost;
545
546 -- Func start of comments
547 --+==========================================================================+
548 --| Procedure Name |
549 --| log_msg |
550 --| |
551 --| DESCRIPTION |
552 --| This procedure logs messages to message stack. |
553 --| |
554 --| PARAMETERS |
555 --| p_msg_lvl IN NUMBER(10) - Message Level |
556 --| p_msg_text IN NUMBER(10) - Actual Message Text |
557 --| |
558 --| RETURNS |
559 --| |
560 --| HISTORY |
561 --| 27/02/2001 Uday Moogla - Created |
562 --| |
563 --+==========================================================================+
564 -- Func end of comments
565
566 PROCEDURE log_msg
567 (
568 p_msg_text IN VARCHAR2
569 )
570 IS
571 BEGIN
572
573 -- IF FND_MSG_PUB.Check_Msg_Level (p_msg_lvl) THEN -- Bug 2659435
574 FND_MESSAGE.SET_NAME('GMF','GMF_API_DEBUG');
575 FND_MESSAGE.SET_TOKEN('MSG',p_msg_text);
576 FND_MSG_PUB.Add;
577 -- END IF;
578
579 END log_msg ;
580
581 END GMF_ResourceCost_PVT;