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