[Home] [Help]
PACKAGE BODY: APPS.GMF_RESOURCECOST_PUB
Source
1 PACKAGE BODY GMF_ResourceCost_PUB AS
2 /* $Header: GMFPRESB.pls 120.2.12000000.2 2007/03/07 13:10:01 pmarada 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 : GMFPRESB.pls |
11 --| Package Name : GMF_ResourceCost_PUB |
12 --| API name : GMF_ResourceCost_PUB |
13 --| Type : Public |
14 --| Pre-reqs : N/A |
15 --| Function : Allocation Definition 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 public procedures relating to Resource Cost |
30 --| creation, updatation and deletetion. |
31 --| |
32 --| |
33 --| Pre-defined API message levels |
34 --| |
35 --| Valid values for message levels are from 1-50. |
36 --| 1 being least severe and 50 highest. |
37 --| |
38 --| The pre-defined levels correspond to standard API |
39 --| return status. Debug levels are used to control the amount of |
40 --| debug information a program writes to the PL/SQL message table. |
41 --| |
42 --| G_MSG_LVL_UNEXP_ERROR CONSTANT NUMBER := 60; |
43 --| G_MSG_LVL_ERROR CONSTANT NUMBER := 50; |
44 --| G_MSG_LVL_SUCCESS CONSTANT NUMBER := 40; |
45 --| G_MSG_LVL_DEBUG_HIGH CONSTANT NUMBER := 30; |
46 --| G_MSG_LVL_DEBUG_MEDIUM CONSTANT NUMBER := 20; |
47 --| G_MSG_LVL_DEBUG_LOW CONSTANT NUMBER := 10; |
48 --| |
49 --| HISTORY |
50 --| 27/Feb/2001 Uday Moogala Created Bug# 1418689 |
51 --| |
52 --| 05/NOV/2002 Uday Moogala Bug# 2659435 |
53 --| Performance related fixes. |
54 --| 1. remove G_MISS_xxx assignments. |
55 --| 2. Conditionally calling debug routine. |
56 --| Also, fixed issues found during unit testing. Search for the bug |
57 --| number to find the fixes. |
58 --| 30-OCT-2002 RajaSekhar Bug#2641405 Added NOCOPY hint |
59 --| 21/NOV/2002 Uday Moogala Bug# 2681243 |
60 --| 1. Return value of GMA_GLOBAL_GRP.set_who has changed to -1 from 0 |
61 --| in case of invalid users. |
62 --| 2. Removed "when others" section in validate_input_params |
63 --| 03/Dec/2002 Uday Moogala Bug# 2692459 |
64 --| Modified code to verify for same usage um type as rsrc um type. |
65 --| 13/May/2004 Dinesh Vadivel Bug# 3628252 |
66 --| Removed validation for nominal cost in VALIDATE_INPUT_PARAMS |
67 --| so that it takes negative entries |
68 --| 19-sep-2005 Prasad marada Inventory convergence modification, adding|
69 --| legal entity, organization id, period id, |
70 --| cost_type_id,usage_uom coulmns |
71 --+==========================================================================+
72 -- End of comments
73
74
75
76 PROCEDURE Validate_Input_Params
77 (
78 p_rsrc_cost_rec IN Resource_Cost_Rec_Type
79 ,x_rsrc_cost_rec OUT NOCOPY Resource_Cost_Rec_Type
80 ,x_user_id OUT NOCOPY NUMBER
81 ,x_return_status OUT NOCOPY VARCHAR2
82 ) ;
83 --
84 -- Modified the function parameter as per the inventory convergence
85 FUNCTION check_records_exist
86 (
87 p_legal_entity_id cm_rsrc_dtl.legal_entity_id%TYPE,
88 p_organization_id cm_rsrc_dtl.organization_id%TYPE,
89 p_resources cm_rsrc_dtl.resources%TYPE,
90 p_period_id cm_rsrc_dtl.period_id%TYPE,
91 p_cost_type_id cm_rsrc_dtl.cost_type_id%TYPE
92 )
93
94 RETURN BOOLEAN ;
95 --
96 PROCEDURE log_msg -- Bug 2659435: Removed first param for debug level
97 (
98 p_msg_text IN VARCHAR2
99 );
100 --
101 -- Bug 2659435: Added new procedure to log header message
102 PROCEDURE add_header_to_error_stack
103 (
104 p_header Resource_Cost_Rec_Type
105 );
106 --
107
108
109 -- Global variables
110 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMF_ResourceCost_PUB';
111
112 -- Bug 2659435
113 G_operation VARCHAR2(30); -- values will be Insert, Update or Delete
114 G_tmp BOOLEAN := FND_MSG_PUB.Check_Msg_Level(0) ; -- temp call to initialize the
115 -- msg level threshhold gobal
116 -- variable.
117 G_debug_level NUMBER(2) := FND_MSG_PUB.G_Msg_Level_Threshold; -- Use this variable everywhere
118 -- to decide to log a debug msg.
119 G_header_logged VARCHAR2(1); -- to indicate whether header is already in
120 -- error stack or not - avoid logging duplicate headers
121
122
123 --Start of comments
124 --+========================================================================+
125 --| API Name : Create_Resource_Cost |
126 --| TYPE : Public |
127 --| Function : Creates a new Resource Cost based on the input |
128 --| into table CM_RSRC_DTL |
129 --| Pre-reqa : None. |
130 --| Parameters : |
131 --| IN : |
132 --| p_api_version IN NUMBER - Required |
133 --| p_init_msg_list IN VARCHAR2 - Optional |
134 --| p_commit IN VARCHAR2 - Optional |
135 --| p_resource_cost_rec IN Resource_Cost_Rec_Type |
136 --| OUT : |
137 --| x_return_status OUT VARCHAR2 |
138 --| x_msg_count OUT NUMBER |
139 --| x_msg_data OUT VARCHAR2 |
140 --| |
141 --| Version : |
142 --| Current Version : 2.0 |
143 --| Previous Version : 1.0 |
144 --| Initial Version : 1.0 |
145 --| |
146 --| Notes : |
147 --| |
148 --| HISTORY |
149 --| 01-Mar-01 Uday Moogala - Created |
150 --| |
151 --+========================================================================+
152 -- End of comments
153
154 PROCEDURE Create_Resource_Cost
155 ( p_api_version IN NUMBER ,
156 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
157 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
158
159 x_return_status OUT NOCOPY VARCHAR2 ,
160 x_msg_count OUT NOCOPY NUMBER ,
161 x_msg_data OUT NOCOPY VARCHAR2 ,
162
163 p_resource_cost_rec IN Resource_Cost_Rec_Type
164 )
165 IS
166 l_api_name CONSTANT VARCHAR2(30) := 'Create_Resource_Cost' ;
167 l_api_version CONSTANT NUMBER := 2.0 ;
168
169 l_rsrc_cost_rec Resource_Cost_Rec_Type ;
170 l_return_status VARCHAR2(2) ;
171 l_user_id NUMBER ;--fnd_user.user_id%TYPE ;
172 l_count NUMBER(10) ;
173 l_data VARCHAR2(2000) ;
174 l_no_rows_ins NUMBER(10) ;
175
176 BEGIN
177
178 -- Standard Start of API savepoint
179 SAVEPOINT Create_Alloc_Definition_PUB;
180
181 -- Initialize message list if p_init_msg_list is set to TRUE.
182 IF FND_API.to_Boolean( p_init_msg_list ) THEN
183 FND_MSG_PUB.initialize;
184 END IF;
185
186 -- Standard call to check for call compatibility.
187 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
188 p_api_version ,
189 l_api_name ,
190 G_PKG_NAME )
191 THEN
192 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
193 END IF;
194
195 -- Initialize API return status to success
196 x_return_status := FND_API.G_RET_STS_SUCCESS;
197
198 G_operation := 'INSERT'; -- Bug 2659435
199 G_header_logged := 'N'; -- to avoid logging duplicate header for errors
200
201 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
202 log_msg('Validating input parameters');
203 END IF;
204 -- Validate all the input parameters.
205 VALIDATE_INPUT_PARAMS
206 (p_rsrc_cost_rec => p_resource_cost_rec
207 ,x_rsrc_cost_rec => l_rsrc_cost_rec
208 ,x_user_id => l_user_id
209 ,x_return_status => l_return_status) ;
210
211 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
212 log_msg('Return Status after validating : ' || l_return_status);
213 END IF;
214
215
216 -- Return if validation failures detected
217 IF l_return_status = FND_API.G_RET_STS_ERROR
218 THEN
219 RAISE FND_API.G_EXC_ERROR;
220 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
221 THEN
222 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
223 END IF;
224
225 --
226 -- Check for duplicate record.
227 --
228 IF check_records_exist(p_legal_entity_id => l_rsrc_cost_rec.legal_entity_id,
229 p_organization_id => l_rsrc_cost_rec.organization_id,
230 p_resources => l_rsrc_cost_rec.resources,
231 p_period_id => l_rsrc_cost_rec.period_id,
232 p_cost_type_id => l_rsrc_cost_rec.cost_type_id) THEN
233 FND_MESSAGE.SET_NAME('GMF','GMF_API_DUPLICATE_RES_COST');
234 FND_MESSAGE.SET_TOKEN('RESOURCES',l_rsrc_cost_rec.resources);
235 FND_MESSAGE.SET_TOKEN('LEGAL_ENTITY_ID',l_rsrc_cost_rec.legal_entity_id);
236 FND_MESSAGE.SET_TOKEN('ORGANIZATION_ID',l_rsrc_cost_rec.organization_id);
237 FND_MESSAGE.SET_TOKEN('PERIOD_ID',l_rsrc_cost_rec.period_id);
238 FND_MESSAGE.SET_TOKEN('COST_TYPE_ID',l_rsrc_cost_rec.cost_type_id);
239 FND_MSG_PUB.Add;
240 RAISE FND_API.G_EXC_ERROR;
241 END IF;
242
243 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
244 log_msg('Inserting record for resource : ' || l_rsrc_cost_rec.resources ||
245 ' Legal entity id ' || l_rsrc_cost_rec.legal_entity_id ||
246 ' Organization Id ' || l_rsrc_cost_rec.organization_id ||
247 ' Period Id ' || l_rsrc_cost_rec.Period_id ||
248 ' Cost type Id ' || l_rsrc_cost_rec.cost_type_id);
249
250 END IF;
251
252 -- call to private API to insert to record
253 GMF_ResourceCost_PVT.Create_Resource_Cost
254 ( p_api_version => 2.0
255 , p_init_msg_list => FND_API.G_FALSE
256 , p_commit => FND_API.G_FALSE
257
258 , x_return_status => l_return_status
259 , x_msg_count => l_count
260 , x_msg_data => l_data
261
262 , p_resource_cost_rec => l_rsrc_cost_rec
263 , p_user_id => l_user_id
264 );
265 -- created row count
266 l_no_rows_ins := SQL%ROWCOUNT ;
267 -- Return in case of insert fails
268 IF l_return_status = FND_API.G_RET_STS_ERROR
269 THEN
270 RAISE FND_API.G_EXC_ERROR;
271 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
272 THEN
273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
274 END IF;
275
276 add_header_to_error_stack(p_resource_cost_rec); -- Bug 2659435
277 FND_MESSAGE.SET_NAME('GMF','GMF_API_ROWS_INS');
278 FND_MESSAGE.SET_TOKEN('NUM_ROWS',l_no_rows_ins);
279 FND_MSG_PUB.Add;
280
281 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
282 log_msg('1 row inserted');
283 END IF;
284
285 -- Standard check of p_commit.
286 IF FND_API.To_Boolean( p_commit ) THEN
287 COMMIT WORK;
288 END IF;
289
290 -- Standard call to get message count and if count is 1, get message info.
291 FND_MSG_PUB.Count_And_Get
292 ( p_count => x_msg_count ,
293 p_data => x_msg_data
294 );
295
296 EXCEPTION
297 WHEN FND_API.G_EXC_ERROR THEN
298 ROLLBACK TO Create_Alloc_Definition_PUB;
299 x_return_status := FND_API.G_RET_STS_ERROR ;
300 FND_MSG_PUB.Count_And_Get
301 ( p_count => x_msg_count ,
302 p_data => x_msg_data
303 );
304 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
305 ROLLBACK TO Create_Alloc_Definition_PUB;
306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
307 FND_MSG_PUB.Count_And_Get
308 ( p_count => x_msg_count ,
309 p_data => x_msg_data
310 );
311 WHEN OTHERS THEN
312 ROLLBACK TO Create_Alloc_Definition_PUB;
313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
314 IF FND_MSG_PUB.Check_Msg_Level
315 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
316 THEN
317 FND_MSG_PUB.Add_Exc_Msg
318 ( G_PKG_NAME ,
319 l_api_name
320 );
321 END IF;
322 FND_MSG_PUB.Count_And_Get
323 ( p_count => x_msg_count ,
324 p_data => x_msg_data
325 );
326
327 END Create_Resource_Cost;
328
329
330 --Start of comments
331 --+========================================================================+
332 --| API Name : Update_Resource_Cost |
333 --| TYPE : Public |
334 --| Function : Updates Allocation Definition based on the input |
335 --| into GL_ALOC_BAS |
336 --| Pre-reqa : None. |
337 --| Parameters : |
338 --| IN : |
339 --| p_api_version IN NUMBER - Required |
340 --| p_init_msg_list IN VARCHAR2 - Optional |
341 --| p_commit IN VARCHAR2 - Optional |
342 --| p_resource_cost_rec IN Resource_Cost_Rec_Type |
343 --| OUT : |
344 --| x_return_status OUT VARCHAR2 |
345 --| x_msg_count OUT NUMBER |
346 --| x_msg_data OUT VARCHAR2 |
347 --| |
348 --| Version : |
349 --| Current Version : 2.0 |
350 --| Previous Version : 1.0 |
351 --| Initial Version : 1.0 |
352 --| |
353 --| Notes : |
354 --| |
355 --| HISTORY |
356 --| 01-Mar-01 Uday Moogala - Created |
357 --| |
358 --+========================================================================+
359 -- End of comments
360
361 PROCEDURE Update_Resource_Cost
362 ( p_api_version IN NUMBER ,
363 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
364 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
365
366 x_return_status OUT NOCOPY VARCHAR2 ,
367 x_msg_count OUT NOCOPY NUMBER ,
368 x_msg_data OUT NOCOPY VARCHAR2 ,
369
370 p_resource_cost_rec IN Resource_Cost_Rec_Type
371 )
372 IS
373 l_api_name CONSTANT VARCHAR2(30) := 'Update_Resource_Cost' ;
374 l_api_version CONSTANT NUMBER := 2.0 ;
375
376 l_rsrc_cost_rec Resource_Cost_Rec_Type ;
377 l_no_rows_upd NUMBER(10) ;
378 l_return_status VARCHAR2(2) ;
379 l_user_id fnd_user.user_id%TYPE ;
380 l_count NUMBER(10) ;
381 l_data VARCHAR2(2000) ;
382
383 BEGIN
384 -- Standard Start of API savepoint
385 SAVEPOINT Update_Alloc_Definition_PUB;
386
387 -- Initialize message list if p_init_msg_list is set to TRUE.
388 IF FND_API.to_Boolean( p_init_msg_list ) THEN
389 FND_MSG_PUB.initialize;
390 END IF;
391
392 -- Standard call to check for call compatibility.
393 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
394 p_api_version ,
395 l_api_name ,
396 G_PKG_NAME )
397 THEN
398 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
399 END IF;
400
401 -- Initialize API return status to success
402 x_return_status := FND_API.G_RET_STS_SUCCESS;
403
404 G_operation := 'UPDATE'; -- Bug 2659435
405 G_header_logged := 'N'; -- to avoid logging duplicate header for errors
406
407 -- Validate all the input parameters.
408 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
409 log_msg('Validating input parameters');
410 END IF;
411
412 VALIDATE_INPUT_PARAMS
413 (p_rsrc_cost_rec => p_resource_cost_rec
414 ,x_rsrc_cost_rec => l_rsrc_cost_rec
415 ,x_user_id => l_user_id
416 ,x_return_status => l_return_status) ;
417
418 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
419 log_msg('Return Status after validating : ' || l_return_status);
420 END IF;
421
422 -- Return if validation failures detected
423 IF l_return_status = FND_API.G_RET_STS_ERROR
424 THEN
425 RAISE FND_API.G_EXC_ERROR;
426 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
427 THEN
428 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
429 END IF;
430
431 -- Check whether any records exists for update
432 IF NOT check_records_exist(p_legal_entity_id => l_rsrc_cost_rec.legal_entity_id,
433 p_organization_id => l_rsrc_cost_rec.organization_id,
434 p_resources => l_rsrc_cost_rec.resources,
435 p_period_id => l_rsrc_cost_rec.period_id,
436 p_cost_type_id => l_rsrc_cost_rec.cost_type_id) THEN
437
438 FND_MESSAGE.SET_NAME('GMF','GMF_API_RSRC_NO_REC_FOUND');
439 FND_MESSAGE.SET_TOKEN('RESOURCES',l_rsrc_cost_rec.resources);
440 FND_MESSAGE.SET_TOKEN('LEGAL_ENTITY_ID',l_rsrc_cost_rec.legal_entity_id);
441 FND_MESSAGE.SET_TOKEN('ORGANIZATION_ID',l_rsrc_cost_rec.organization_id);
442 FND_MESSAGE.SET_TOKEN('PERIOD_ID',l_rsrc_cost_rec.period_id);
443 FND_MESSAGE.SET_TOKEN('COST_TYPE_ID',l_rsrc_cost_rec.cost_type_id);
444 FND_MSG_PUB.Add;
445 RAISE FND_API.G_EXC_ERROR;
446 END IF ;
447
448 IF l_rsrc_cost_rec.delete_mark = 1 THEN
449 add_header_to_error_stack(p_resource_cost_rec); -- Bug 2659435
450 FND_MESSAGE.SET_NAME('GMF','GMF_API_CANT_MARK_FOR_PURGE');
451 FND_MSG_PUB.Add;
452 RAISE FND_API.G_EXC_ERROR;
453 END IF ;
454
455 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
456 log_msg('Updating record for resource : ' || l_rsrc_cost_rec.resources ||
457 ' Legal entity id ' || l_rsrc_cost_rec.legal_entity_id ||
458 ' Organization Id ' || l_rsrc_cost_rec.organization_id ||
459 ' Period Id ' || l_rsrc_cost_rec.Period_id ||
460 ' Cost type Id ' || l_rsrc_cost_rec.cost_type_id);
461 END IF;
462 -- call to private API to insert to record
463 GMF_ResourceCost_PVT.Update_Resource_Cost
464 ( p_api_version => 2.0
465 , p_init_msg_list => FND_API.G_FALSE
466 , p_commit => FND_API.G_FALSE
467
468 , x_return_status => l_return_status
469 , x_msg_count => l_count
470 , x_msg_data => l_data
471
472 , p_Resource_Cost_rec => l_rsrc_cost_rec
473 , p_user_id => l_user_id
474 );
475 l_no_rows_upd := SQL%ROWCOUNT ;
476
477 -- Return in case of insert fails
478 IF l_return_status = FND_API.G_RET_STS_ERROR
479 THEN
480 RAISE FND_API.G_EXC_ERROR;
481 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
482 THEN
483 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
484 END IF;
485
486 add_header_to_error_stack(p_resource_cost_rec); -- Bug 2659435
487 FND_MESSAGE.SET_NAME('GMF','GMF_API_ROWS_UPD');
488 FND_MESSAGE.SET_TOKEN('NUM_ROWS',l_no_rows_upd);
489 FND_MSG_PUB.Add;
490
491 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
492 log_msg( l_no_rows_upd || ' rows updated.');
493 END IF;
494
495 -- Standard check of p_commit.
496 IF FND_API.To_Boolean( p_commit ) THEN
497 COMMIT WORK;
498 END IF;
499
500 -- Standard call to get message count and if count is 1, get message info.
501 FND_MSG_PUB.Count_And_Get
502 ( p_count => x_msg_count ,
503 p_data => x_msg_data
504 );
505
506 EXCEPTION
507 WHEN FND_API.G_EXC_ERROR THEN
508 ROLLBACK TO Update_Alloc_Definition_PUB;
509 x_return_status := FND_API.G_RET_STS_ERROR ;
510 FND_MSG_PUB.Count_And_Get
511 ( p_count => x_msg_count ,
512 p_data => x_msg_data
513 );
514 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
515 ROLLBACK TO Update_Alloc_Definition_PUB;
516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
517 FND_MSG_PUB.Count_And_Get
518 ( p_count => x_msg_count ,
519 p_data => x_msg_data
520 );
521 WHEN OTHERS THEN
522 ROLLBACK TO Update_Alloc_Definition_PUB;
523 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
524 IF FND_MSG_PUB.Check_Msg_Level
525 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
526 THEN
527 FND_MSG_PUB.Add_Exc_Msg
528 ( G_PKG_NAME ,
529 l_api_name
530 );
531 END IF;
532 FND_MSG_PUB.Count_And_Get
533 ( p_count => x_msg_count ,
534 p_data => x_msg_data
535 );
536
537 END Update_Resource_Cost ;
538
539
540 --Start of comments
541 --+========================================================================+
542 --| API Name : Delete_Resource_Cost |
543 --| TYPE : Public |
544 --| Function : Deletes Resource Costs based on the input from CM_RSRC_MST|
545 --| Pre-reqa : None. |
546 --| Parameters : |
547 --| IN : |
548 --| p_api_version IN NUMBER - Required |
549 --| p_init_msg_list IN VARCHAR2 - Optional |
550 --| p_commit IN VARCHAR2 - Optional |
551 --| p_resource_cost_rec IN Resource_Cost_Rec_Type |
552 --| OUT : |
553 --| x_return_status OUT VARCHAR2 |
554 --| x_msg_count OUT NUMBER |
555 --| x_msg_data OUT VARCHAR2 |
556 --| |
557 --| Version : |
558 --| Current Version : 2.0 |
559 --| Previous Version : 1.0 |
560 --| Initial Version : 1.0 |
561 --| |
562 --| Notes : |
563 --| |
564 --| HISTORY |
565 --| 01-Mar-01 Uday Moogala - Created |
566 --| |
567 --+========================================================================+
568 -- End of comments
569
570 PROCEDURE Delete_Resource_Cost
571 ( p_api_version IN NUMBER ,
572 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
573 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
574
575 x_return_status OUT NOCOPY VARCHAR2 ,
576 x_msg_count OUT NOCOPY NUMBER ,
577 x_msg_data OUT NOCOPY VARCHAR2 ,
578
579 p_resource_cost_rec IN Resource_Cost_Rec_Type
580 )
581 IS
582 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Resource_Cost' ;
583 l_api_version CONSTANT NUMBER := 2.0 ;
584
585 l_rsrc_cost_rec Resource_Cost_Rec_Type ;
586 l_no_rows_del NUMBER(10) ;
587 l_return_status VARCHAR2(2) ;
588 l_user_id fnd_user.user_id%TYPE ;
589 l_count NUMBER(10) ;
590 l_data VARCHAR2(2000) ;
591
592 BEGIN
593
594 -- Standard Start of API savepoint
595 SAVEPOINT Delete_Alloc_Definition_PUB;
596
597 -- Initialize message list if p_init_msg_list is set to TRUE.
598 IF FND_API.to_Boolean( p_init_msg_list ) THEN
599 FND_MSG_PUB.initialize;
600 END IF;
601
602 -- Standard call to check for call compatibility.
603 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
604 p_api_version ,
605 l_api_name ,
606 G_PKG_NAME )
607 THEN
608 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
609 END IF;
610
611 -- Initialize API return status to success
612 x_return_status := FND_API.G_RET_STS_SUCCESS;
613
614 G_operation := 'DELETE'; -- Bug 2659435
615 G_header_logged := 'N'; -- to avoid logging duplicate header for errors
616
617 -- Validate all the input parameters.
618 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
619 log_msg('Validating input parameters');
620 END IF;
621
622 VALIDATE_INPUT_PARAMS
623 (p_rsrc_cost_rec => p_resource_cost_rec
624 ,x_rsrc_cost_rec => l_rsrc_cost_rec
625 ,x_user_id => l_user_id
626 ,x_return_status => l_return_status) ;
627
628 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
629 log_msg('Return Status after validating : ' || l_return_status);
630 END IF;
631
632 -- Return if validation failures detected
633 IF l_return_status = FND_API.G_RET_STS_ERROR
634 THEN
635 RAISE FND_API.G_EXC_ERROR;
636 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
637 THEN
638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
639 END IF;
640
641 -- Check whether any records exists for update
642 IF NOT check_records_exist(p_legal_entity_id => l_rsrc_cost_rec.legal_entity_id,
643 p_organization_id => l_rsrc_cost_rec.organization_id,
644 p_resources => l_rsrc_cost_rec.resources,
645 p_period_id => l_rsrc_cost_rec.period_id,
646 p_cost_type_id => l_rsrc_cost_rec.cost_type_id) THEN
647
648 FND_MESSAGE.SET_NAME('GMF','GMF_API_RSRC_NO_REC_FOUND');
649 FND_MESSAGE.SET_TOKEN('RESOURCES',l_rsrc_cost_rec.resources);
650 FND_MESSAGE.SET_TOKEN('LEGAL_ENTITY_ID',l_rsrc_cost_rec.legal_entity_id);
651 FND_MESSAGE.SET_TOKEN('ORGANIZATION_ID',l_rsrc_cost_rec.organization_id);
652 FND_MESSAGE.SET_TOKEN('PERIOD_ID',l_rsrc_cost_rec.period_id);
653 FND_MESSAGE.SET_TOKEN('COST_TYPE_ID',l_rsrc_cost_rec.cost_type_id);
654 FND_MSG_PUB.Add;
655 RAISE FND_API.G_EXC_ERROR;
656 END IF ;
657
658 -- set delete mark to 1 irrespective of users input.
659 l_rsrc_cost_rec.delete_mark := 1 ;
660
661 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
662 log_msg('Deleting record for resource : ' || l_rsrc_cost_rec.resources ||
663 ' Legal entity id ' || l_rsrc_cost_rec.legal_entity_id ||
664 ' Organization Id ' || l_rsrc_cost_rec.organization_id ||
665 ' Period Id ' || l_rsrc_cost_rec.Period_id ||
666 ' Cost type Id ' || l_rsrc_cost_rec.cost_type_id);
667 END IF;
668
669 -- call to private API to insert to record
670 GMF_ResourceCost_PVT.Update_Resource_Cost
671 ( p_api_version => 2.0
672 , p_init_msg_list => FND_API.G_FALSE
673 , p_commit => FND_API.G_FALSE
674
675 , x_return_status => l_return_status
676 , x_msg_count => l_count
677 , x_msg_data => l_data
678
679 , p_Resource_Cost_rec => l_rsrc_cost_rec
680 , p_user_id => l_user_id
681 );
682 -- deleted records row count
683 l_no_rows_del := SQL%ROWCOUNT ;
684
685 -- Return in case of insert fails
686 IF l_return_status = FND_API.G_RET_STS_ERROR
687 THEN
688 RAISE FND_API.G_EXC_ERROR;
689 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
690 THEN
691 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
692 END IF;
693
694 add_header_to_error_stack(p_resource_cost_rec); -- Bug 2659435
695 FND_MESSAGE.SET_NAME('GMF','GMF_API_ROWS_DEL');
696 FND_MESSAGE.SET_TOKEN('NUM_ROWS',l_no_rows_del);
697 FND_MSG_PUB.Add;
698
699 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
700 log_msg(l_no_rows_del || ' row(s) deleted.');
701 END IF;
702
703 -- Standard check of p_commit.
704 IF FND_API.To_Boolean( p_commit ) THEN
705 COMMIT WORK;
706 END IF;
707
708 -- Standard call to get message count and if count is 1, get message info.
709 FND_MSG_PUB.Count_And_Get
710 ( p_count => x_msg_count ,
711 p_data => x_msg_data
712 );
713
714 EXCEPTION
715 WHEN FND_API.G_EXC_ERROR THEN
716 ROLLBACK TO Delete_Alloc_Definition_PUB;
717 x_return_status := FND_API.G_RET_STS_ERROR ;
718 FND_MSG_PUB.Count_And_Get
719 ( p_count => x_msg_count ,
720 p_data => x_msg_data
721 );
722 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
723 ROLLBACK TO Delete_Alloc_Definition_PUB;
724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
725 FND_MSG_PUB.Count_And_Get
726 ( p_count => x_msg_count ,
727 p_data => x_msg_data
728 );
729 WHEN OTHERS THEN
730 ROLLBACK TO Delete_Alloc_Definition_PUB;
731 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
732 IF FND_MSG_PUB.Check_Msg_Level
733 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
734 THEN
735 FND_MSG_PUB.Add_Exc_Msg
736 ( G_PKG_NAME ,
737 l_api_name
738 );
739 END IF;
740 FND_MSG_PUB.Count_And_Get
741 ( p_count => x_msg_count ,
742 p_data => x_msg_data
743 );
744
745 END Delete_Resource_Cost ;
746
747 --Start of comments
748 --+========================================================================+
749 --| API Name : Get_Resource_Cost |
750 --| TYPE : Public |
751 --| Function : Retrive Resource Cost based on the input from table |
752 --| CM_RSRC_DTL |
753 --| Pre-reqa : None. |
754 --| Parameters : |
755 --| IN : |
756 --| p_api_version IN NUMBER - Required |
757 --| p_init_msg_list IN VARCHAR2 - Optional |
758 --| p_resource_cost_rec IN Resource_Cost_Rec_Type |
759 --| OUT : |
760 --| x_return_status OUT VARCHAR2 |
761 --| x_msg_count OUT NUMBER |
762 --| x_msg_data OUT VARCHAR2 |
763 --| x_resource_cost_rec OUT Resource_Cost_Rec_Type |
764 --| |
765 --| Version : |
766 --| Current Version : 2.0 |
767 --| Previous Version : 1.0 |
768 --| Initial Version : 1.0 |
769 --| |
770 --| Notes : |
771 --| |
772 --| HISTORY |
773 --| 26-Apr-01 Uday Moogala - Created |
774 --| |
775 --+========================================================================+
776 -- End of comments
777
778 PROCEDURE Get_Resource_Cost
779 ( p_api_version IN NUMBER ,
780 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
781
782 x_return_status OUT NOCOPY VARCHAR2 ,
783 x_msg_count OUT NOCOPY NUMBER ,
784 x_msg_data OUT NOCOPY VARCHAR2 ,
785
786 p_resource_cost_rec IN Resource_Cost_Rec_Type ,
787 x_resource_cost_rec OUT NOCOPY Resource_Cost_Rec_Type
788 )
789 IS
790 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Item_Cost' ;
791 l_api_version CONSTANT NUMBER := 2.0 ;
792
793 l_return_status VARCHAR2(2) ;
794 l_count NUMBER(10) ;
795 l_data VARCHAR2(2000) ;
796
797 BEGIN
798
799 -- Standard Start of API savepoint
800 SAVEPOINT Get_Resource_Cost_PUB;
801
802 -- Initialize message list if p_init_msg_list is set to TRUE.
803 IF FND_API.to_Boolean( p_init_msg_list ) THEN
804 FND_MSG_PUB.initialize;
805 END IF;
806
807 -- Standard call to check for call compatibility.
808 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
809 p_api_version ,
810 l_api_name ,
811 G_PKG_NAME )
812 THEN
813 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
814 END IF;
815
816 -- Initialize API return status to success
817 x_return_status := FND_API.G_RET_STS_SUCCESS;
818
819 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
820 log_msg('Beginning Get Resource Cost process.');
821 END IF;
822
823 G_header_logged := 'N'; -- to avoid logging duplicate header for errors
824
825 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
826 log_msg('Calling private API to fetch records...');
827 END IF;
828
829 GMF_ResourceCost_PVT.Get_Resource_Cost
830 (
831 p_api_version => 2.0
832 , p_init_msg_list => FND_API.G_FALSE
833
834 , x_return_status => l_return_status
835 , x_msg_count => l_count
836 , x_msg_data => l_data
837
838 , p_resource_cost_rec => p_resource_cost_rec
839
840 , x_resource_cost_rec => x_resource_cost_rec
841 );
842
843 -- Return if update fails for any reason
844 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
845 RAISE FND_API.G_EXC_ERROR;
846 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
847 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
848 END IF;
849
850 /*
851 -- Standard check of p_commit.
852 IF FND_API.To_Boolean( p_commit ) THEN
853 COMMIT WORK;
854 END IF;
855 */
856 -- Standard call to get message count and if count is 1, get message info.
857 FND_MSG_PUB.Count_And_Get
858 ( p_count => x_msg_count ,
859 p_data => x_msg_data
860 );
861
862 EXCEPTION
863 WHEN FND_API.G_EXC_ERROR THEN
864 ROLLBACK TO Get_Resource_Cost_PUB;
865 x_return_status := FND_API.G_RET_STS_ERROR ;
866 FND_MSG_PUB.Count_And_Get
867 ( p_count => x_msg_count ,
868 p_data => x_msg_data
869 );
870 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
871 ROLLBACK TO Get_Resource_Cost_PUB;
872 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
873 FND_MSG_PUB.Count_And_Get
874 ( p_count => x_msg_count ,
875 p_data => x_msg_data
876 );
877 WHEN OTHERS THEN
878 ROLLBACK TO Get_Resource_Cost_PUB;
879 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
880 IF FND_MSG_PUB.Check_Msg_Level
881 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
882 THEN
883 FND_MSG_PUB.Add_Exc_Msg
884 ( G_PKG_NAME ,
885 l_api_name
886 );
887 END IF;
888 FND_MSG_PUB.Count_And_Get
889 ( p_count => x_msg_count ,
890 p_data => x_msg_data
891 );
892
893 END Get_Resource_Cost;
894
895 -- Proc start of comments
896 --+==========================================================================+
897 --| PROCEDURE NAME |
898 --| Validate_Input_Params |
899 --| |
900 --| DESCRIPTION |
901 --| Validates all the input parameters. |
902 --| |
903 --| PARAMETERS |
904 --| p_resource_cost_rec IN Resource_Cost_Rec_Type |
905 --| x_return_status OUT VARCHAR2 |
906 --| |
907 --| RETURNS |
908 --| TRUE - If succesfully initialized all variables |
909 --| FALSE - If any error |
910 --| |
911 --| HISTORY |
912 --| 27/02/2001 Uday Moogla - Created |
913 --| |
914 --| 03/Dec/2002 Uday Moogala Bug# 2692459 |
915 --| Modified code to verify for same usage um type as rsrc um type. |
916 --| |
917 --| 13/May/2004 Dinesh Vadivel Bug# 3628252 |
918 --| Removed validation for nominal cost so that it takes negative |
919 --| entries |
920 --| 22/Nov/2005 Prasad Marada Bug 4689137,Modified input parameter |
921 --| validation as per datamodel changes |
922 --+==========================================================================+
923 -- Proc end of comments
924
925 PROCEDURE Validate_Input_Params
926 (
927 p_rsrc_cost_rec IN Resource_Cost_Rec_Type
928 ,x_rsrc_cost_rec OUT NOCOPY Resource_Cost_Rec_Type
929 ,x_user_id OUT NOCOPY NUMBER
930 ,x_return_status OUT NOCOPY VARCHAR2
931 )
932 IS
933
934 l_resources cm_rsrc_dtl.resources%TYPE ;
935 l_legal_entity_id cm_rsrc_dtl.legal_entity_id%TYPE;
936 l_organization_id cm_rsrc_dtl.organization_id%TYPE;
937 l_organization_code mtl_parameters.organization_code%TYPE;
938 l_period_id cm_rsrc_dtl.period_id%TYPE;
939 l_calendar_code cm_rsrc_dtl.calendar_code%TYPE ;
940 l_period_code cm_rsrc_dtl.period_code%TYPE ;
941 -- l_period_status cm_cldr_dtl.period_status%TYPE ;
942 l_cost_type_id cm_rsrc_dtl.cost_type_id%TYPE;
943 l_cost_type_code cm_rsrc_dtl.cost_mthd_code%TYPE ;
944 l_usage_uom cm_rsrc_dtl.usage_uom%TYPE ;
945 l_nominal_cost NUMBER ;
946 l_delete_mark NUMBER ;
947 l_user_name fnd_user.user_name%TYPE ;
948 l_user_id NUMBER ;
949
950 -- Bug 2692459
951 l_usage_uom_class mtl_units_of_measure.uom_class%TYPE ;
952 l_resource_uom_class mtl_units_of_measure.uom_class%TYPE ;
953 l_resource_uom cr_rsrc_mst.std_usage_uom%TYPE ;
954
955
956 BEGIN
957
958 l_resources := p_rsrc_cost_rec.resources ;
959 l_legal_entity_id := p_rsrc_cost_rec.legal_entity_id ;
960 l_organization_id := p_rsrc_cost_rec.organization_id;
961 l_organization_code := p_rsrc_cost_rec.organization_code;
962 l_period_id := p_rsrc_cost_rec.period_id ;
963 l_calendar_code := p_rsrc_cost_rec.calendar_code ;
964 l_period_code := p_rsrc_cost_rec.period_code ;
965 l_cost_type_id := p_rsrc_cost_rec.cost_type_id ;
966 l_cost_type_code := p_rsrc_cost_rec.cost_mthd_code ;
967
968 l_usage_uom := p_rsrc_cost_rec.usage_uom ;
969 l_nominal_cost := p_rsrc_cost_rec.nominal_cost ;
970 l_delete_mark := p_rsrc_cost_rec.delete_mark ;
971 l_user_name := p_rsrc_cost_rec.user_name ;
972
973 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
974 log_msg( 'resources : ' || l_resources);
975 log_msg( 'legal_entity_id : ' || l_legal_entity_id);
976 log_msg( 'organization_id : ' || l_organization_id);
977 log_msg( 'period_id : ' || l_period_id);
978 log_msg( 'cost_type_id : ' || l_cost_type_id);
979 log_msg( 'usage_uom : ' || l_usage_uom);
980 log_msg( 'nominal_cost : ' || l_nominal_cost);
981 log_msg( 'delete_mark : ' || l_delete_mark);
982 log_msg( 'user_name : ' || l_user_name);
983 END IF;
984
985 -------------
986 -- Resources
987 -------------
988 IF (l_resources <> FND_API.G_MISS_CHAR) AND
989 (l_resources IS NOT NULL) THEN
990 -- validate alloc_id
991 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
992 log_msg('validating resources : '|| l_resources);
993 END IF;
994
995 --
996 -- Bug 2692459
997 -- get uom type also.
998 --
999 IF NOT (GMF_VALIDATIONS_PVT.Validate_Resources(l_resources)) THEN
1000 add_header_to_error_stack(p_rsrc_cost_rec); -- Bug 2659435
1001 FND_MESSAGE.SET_NAME('GMF','GMF_API_INVALID_RESOURCES');
1002 FND_MESSAGE.SET_TOKEN('RESOURCES',l_resources);
1003 FND_MSG_PUB.Add;
1004 RAISE FND_API.G_EXC_ERROR;
1005 END IF;
1006 -- Get the resource UOM code, resource UOM class for further processing
1007 GMF_VALIDATIONS_PVT.Validate_Resource(l_resources,l_resource_uom,l_resource_uom_class);
1008 IF l_resource_uom IS NULL AND l_resource_uom_class IS NULL THEN
1009 add_header_to_error_stack(p_rsrc_cost_rec); -- Bug 2659435
1010 FND_MESSAGE.SET_NAME('GMF','GMF_API_INVALID_RESOURCES');
1011 FND_MESSAGE.SET_TOKEN('RESOURCES',l_resources);
1012 FND_MSG_PUB.Add;
1013 RAISE FND_API.G_EXC_ERROR;
1014 END IF;
1015 ELSE
1016 add_header_to_error_stack(p_rsrc_cost_rec); -- Bug 2659435
1017 FND_MESSAGE.SET_NAME('GMF','GMF_API_RESOURCES_REQ');
1018 FND_MSG_PUB.Add;
1019 RAISE FND_API.G_EXC_ERROR;
1020 END IF;
1021 -- End Resource
1022
1023 --------------------------
1024 -- Legal entity
1025 --------------------------
1026 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
1027 log_msg('validating legal_entity_id : ' || l_legal_entity_id);
1028 END IF;
1029
1030 IF (l_legal_entity_id <> FND_API.G_MISS_NUM) AND
1031 (l_legal_entity_id IS NOT NULL) THEN
1032 IF NOT GMF_VALIDATIONS_PVT.Validate_legal_entity_id(l_legal_entity_id) THEN
1033 add_header_to_error_stack(p_rsrc_cost_rec); -- Bug 2659435
1034 FND_MESSAGE.SET_NAME('GMF','GMF_API_INVALID_LE_ID');
1035 FND_MESSAGE.SET_TOKEN('LEGAL_ENTITY',l_legal_entity_id);
1036 FND_MSG_PUB.Add;
1037 RAISE FND_API.G_EXC_ERROR;
1038 END IF;
1039 ELSE
1040 add_header_to_error_stack(p_rsrc_cost_rec); -- Bug 2659435
1041 FND_MESSAGE.SET_NAME('GMF','GMF_API_LE_ID_REQ');
1042 FND_MSG_PUB.Add;
1043 RAISE FND_API.G_EXC_ERROR;
1044 END IF;
1045
1046 -- End legal entity
1047
1048 --------------------------
1049 -- Organization validation
1050 --------------------------
1051 -- validate organization id
1052 IF ((l_organization_id <> FND_API.G_MISS_NUM) AND (l_organization_id IS NOT NULL)) THEN
1053 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_debug_level THEN
1054 log_msg('Validation Organization ID : '||l_organization_id);
1055 END IF;
1056 -- invoke validate organization id method
1057 IF NOT gmf_validations_pvt.Validate_organization_id(l_organization_id) THEN
1058 FND_MESSAGE.SET_NAME('GMF','GMF_API_INVALID_ORGN_ID');
1059 FND_MESSAGE.SET_TOKEN('ORGANIZATION_ID', l_organization_id);
1060 FND_MSG_PUB.Add;
1061 RAISE FND_API.G_EXC_ERROR;
1062 END IF;
1063 -- if organization code also passed then log a message to ignore organization code
1064 IF ( l_organization_code <> FND_API.G_MISS_CHAR) AND (l_organization_code IS NOT NULL) THEN
1065 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
1066 FND_MESSAGE.SET_NAME('GMF','GMF_API_IGNORE_ORGN_CODE');
1067 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE', l_organization_code);
1068 FND_MSG_PUB.Add;
1069 END IF;
1070 END IF;
1071 -- Organization code passed
1072 ELSIF (l_organization_code <> FND_API.G_MISS_CHAR ) AND (l_organization_code IS NOT NULL ) THEN
1073 IF (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level) THEN
1074 log_msg('Validating Organization Code : ' ||l_organization_code);
1075 END IF;
1076 -- get the organization id
1077 l_organization_id := gmf_validations_pvt.validate_organization_code(l_organization_code);
1078 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1079 log_msg('Organization id : ' || l_organization_id);
1080 END IF;
1081 -- if organization id is null then log message
1082 IF l_organization_id IS NULL THEN
1083 FND_MESSAGE.SET_NAME('GMF','GMF_API_INVALID_ORGN_CODE');
1084 FND_MESSAGE.SET_TOKEN('ORG_CODE', l_organization_code);
1085 FND_MSG_PUB.Add;
1086 RAISE FND_API.G_EXC_ERROR;
1087 END IF;
1088 -- Either organization id or organization code required
1089 ELSE
1090 FND_MESSAGE.SET_NAME('GMF','GMF_API_ORGANIZATION_ID_REQ');
1091 FND_MSG_PUB.Add;
1092 RAISE FND_API.G_EXC_ERROR;
1093 END IF;
1094 -- End of Organization validation
1095
1096 ----------------------------
1097 -- * Cost Type Validation *
1098 ----------------------------
1099 IF (l_cost_type_id <> FND_API.G_MISS_NUM) AND (l_cost_type_id IS NOT NULL) THEN
1100 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1101 log_msg('Validating Cost Type Id : ' || l_cost_type_id);
1102 END IF;
1103 -- Invoke cost type id validation method
1104 IF NOT gmf_validations_pvt.validate_cost_type_id (l_cost_type_id) THEN
1105 FND_MESSAGE.SET_NAME('GMF','GMF_API_INVALID_COST_TYPE_ID');
1106 FND_MESSAGE.SET_TOKEN('COST_TYPE_ID', l_cost_type_id);
1107 FND_MSG_PUB.Add;
1108 RAISE FND_API.G_EXC_ERROR;
1109 END IF;
1110 -- if cost method code is not then log a message
1111 IF (l_cost_type_code <> FND_API.G_MISS_CHAR) AND (l_cost_type_code IS NOT NULL) THEN
1112 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
1113 FND_MESSAGE.SET_NAME('GMF','GMF_API_IGNORE_COST_TYPE');
1114 FND_MESSAGE.SET_TOKEN('COST_TYPE', l_cost_type_code);
1115 FND_MSG_PUB.Add;
1116 END IF;
1117 END IF;
1118 ELSIF (l_cost_type_code <> FND_API.G_MISS_CHAR) AND (l_cost_type_code IS NOT NULL) THEN
1119
1120 -- Get the cost type id from the method
1121 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1122 log_msg('Validating Cost Type Code : ' || l_cost_type_code);
1123 END IF;
1124
1125 l_cost_type_id := GMF_VALIDATIONS_PVT.Validate_cost_type_code(l_cost_type_code);
1126 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1127 log_msg('Cost Type Id : ' || l_cost_type_id);
1128 END IF;
1129 IF l_cost_type_id IS NULL THEN
1130 FND_MESSAGE.SET_NAME('GMF','GMF_API_INVALID_COST_TYPE');
1131 FND_MESSAGE.SET_TOKEN('COST_TYPE',l_cost_type_code);
1132 FND_MSG_PUB.Add;
1133 RAISE FND_API.G_EXC_ERROR;
1134 END IF;
1135 ELSE
1136 FND_MESSAGE.SET_NAME('GMF','GMF_API_COST_TYPE_ID_REQ');
1137 FND_MSG_PUB.Add;
1138 RAISE FND_API.G_EXC_ERROR;
1139 END IF;
1140 -- End Cost Method
1141 --
1142
1143 --------------------------
1144 -- * Period Validation *
1145 --------------------------
1146 IF (l_period_id <> FND_API.G_MISS_NUM) AND (l_period_id IS NOT NULL) THEN
1147 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1148 log_msg('Validating Period Id : ' || l_period_id);
1149 END IF;
1150 -- Invoke validate period id method
1151 IF NOT gmf_validations_pvt.validate_period_id(l_period_id) THEN
1152 FND_MESSAGE.SET_NAME('GMF','GMF_API_INVALID_PERIOD_ID');
1153 FND_MESSAGE.SET_TOKEN('PERIOD_ID', l_period_id);
1154 FND_MSG_PUB.Add;
1155 RAISE FND_API.G_EXC_ERROR;
1156 END IF;
1157 -- If period code also passed then ignore period code
1158 IF ((l_calendar_code <> FND_API.G_MISS_CHAR) AND (l_calendar_code IS NOT NULL))
1159 AND ((l_period_code <> FND_API.G_MISS_CHAR) AND (l_period_code IS NOT NULL)) THEN
1160 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
1161 FND_MESSAGE.SET_NAME('GMF','GMF_API_IGNORE_PERIOD_CODE');
1162 FND_MESSAGE.SET_TOKEN('CALENDAR_CODE', l_calendar_code);
1163 FND_MESSAGE.SET_TOKEN('PERIOD_CODE', l_period_code);
1164 FND_MSG_PUB.Add;
1165 END IF;
1166 END IF;
1167 ELSIF (l_calendar_code <> FND_API.G_MISS_CHAR) AND (l_calendar_code IS NOT NULL)
1168 AND ((l_period_code <> FND_API.G_MISS_CHAR) AND (l_period_code IS NOT NULL)) THEN
1169
1170 -- Get the period id passing period code to the validate period id method
1171 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1172 log_msg('Validating Calendar Code : ' || l_calendar_code||', Period Code : '||l_period_code);
1173 END IF;
1174
1175 l_period_id := GMF_VALIDATIONS_PVT.Validate_period_code(l_organization_id, l_calendar_code, l_period_code, l_cost_type_id);
1176 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1177 log_msg('Period Id : ' || l_period_id);
1178 END IF;
1179 -- if period id null then log message with invalid period code
1180 IF l_period_id IS NULL THEN
1181 FND_MESSAGE.SET_NAME('GMF','GMF_API_INVALID_CLDR_PERIOD');
1182 FND_MESSAGE.SET_TOKEN('CALENDAR_CODE',l_calendar_code);
1183 FND_MESSAGE.SET_TOKEN('PERIOD_CODE',l_period_code);
1184 FND_MSG_PUB.Add;
1185 RAISE FND_API.G_EXC_ERROR;
1186 END IF;
1187 ELSE
1188 FND_MESSAGE.SET_NAME('GMF','GMF_API_PERIOD_ID_REQ');
1189 FND_MSG_PUB.Add;
1190 RAISE FND_API.G_EXC_ERROR;
1191 END IF;
1192
1193 -- End Period Validation
1194
1195
1196 -- Enough of validations for delete.
1197 -- For update and insert we should do all validations.
1198 --
1199
1200 IF (G_operation <> 'DELETE') THEN
1201
1202 --
1203 -- Usage Unit of Measure
1204 --
1205 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
1206 log_msg('validating usage_uom : ' || l_usage_uom);
1207 END IF;
1208
1209 IF (l_usage_uom <> FND_API.G_MISS_CHAR) AND
1210 (l_usage_uom IS NOT NULL) THEN
1211
1212 --
1213 -- Bug 2692459
1214 -- get usage uom class also and verify whether it is same uom class as resource uom
1215 --
1216 -- get the Usgae UOM class
1217 GMF_VALIDATIONS_PVT.Validate_Usage_Uom(l_usage_uom, l_usage_uom_class) ;
1218 IF l_usage_uom_class IS NULL THEN
1219 add_header_to_error_stack(p_rsrc_cost_rec); -- Bug 2659435
1220 FND_MESSAGE.SET_NAME('GMF','GMF_API_INVALID_USAGE_UM');
1221 FND_MESSAGE.SET_TOKEN('USAGE_UM',l_usage_uom);
1222 FND_MSG_PUB.Add;
1223 RAISE FND_API.G_EXC_ERROR;
1224 END IF;
1225
1226 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
1227 log_msg('Usage UOM Class : ' || l_usage_uom_class ||
1228 ' resource UOM Class : ' || l_resource_uom_class);
1229 END IF;
1230
1231 -- Usage UOM must be of the same type as the resource UOM
1232 IF (l_resource_uom_class <> l_usage_uom_class) THEN
1233 add_header_to_error_stack(p_rsrc_cost_rec);
1234 FND_MESSAGE.SET_NAME('GMF','GMF_API_USAGE_UOM_SAMETYPE_REQ');
1235 FND_MESSAGE.SET_TOKEN('USAGE_UM',l_usage_uom);
1236 FND_MESSAGE.SET_TOKEN('RESOURCE_UM',l_resource_uom);
1237 FND_MSG_PUB.Add;
1238 RAISE FND_API.G_EXC_ERROR;
1239 END IF;
1240 -- End of bug 2692459
1241
1242 ELSIF (l_usage_uom = FND_API.G_MISS_CHAR AND
1243 G_operation = 'UPDATE') OR
1244 (G_operation = 'INSERT') THEN
1245 add_header_to_error_stack(p_rsrc_cost_rec); -- Bug 2659435
1246 FND_MESSAGE.SET_NAME('GMF','GMF_API_USAGE_UM_REQ');
1247 FND_MSG_PUB.Add;
1248 RAISE FND_API.G_EXC_ERROR;
1249 END IF;
1250 -- End Usage Unit of Measure
1251
1252 --
1253 -- Nominal Cost
1254 -- Nominal Cost should be > 0
1255 -- In the form the format mask for this is : 999999999D999999999(999,999,999.999999999)
1256 -- To put that check here, the cost should not be >= 1,000,000,000
1257 --
1258 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
1259 log_msg('Validating Nominal Cost : '||l_nominal_cost);
1260 END IF;
1261
1262 /*************************************************************************
1263 * dvadivel 13-May-2004 Bug # 3628252 Removed validation for Nominal cost
1264 *************************************************************************/
1265 /* IF (l_nominal_cost <> FND_API.G_MISS_NUM) AND
1266 (l_nominal_cost IS NOT NULL) THEN
1267 IF ((nvl(l_nominal_cost,0) <= 0) OR (nvl(l_nominal_cost,0) >= 1000000000)) THEN
1268 add_header_to_error_stack(p_rsrc_cost_rec); -- Bug 2659435
1269 FND_MESSAGE.SET_NAME('GMF','GMF_API_INVALID_NOMINAL_COST');
1270 FND_MESSAGE.SET_TOKEN('NOMINAL_COST',l_nominal_cost);
1271 FND_MSG_PUB.Add;
1272 RAISE FND_API.G_EXC_ERROR;
1273 END IF;
1274 ELSIF (l_nominal_cost = FND_API.G_MISS_NUM AND
1275 */
1276 IF ((l_nominal_cost = FND_API.G_MISS_NUM) OR (l_nominal_cost IS NULL)) AND
1277 ((G_operation = 'UPDATE') OR
1278 (G_operation = 'INSERT')) THEN
1279 add_header_to_error_stack(p_rsrc_cost_rec); -- Bug 2659435
1280 FND_MESSAGE.SET_NAME('GMF','GMF_API_NOMINAL_COST_REQ');
1281 FND_MSG_PUB.Add;
1282 RAISE FND_API.G_EXC_ERROR;
1283 END IF;
1284 -- End Nominal Cost
1285
1286 --
1287 -- Delete Mark
1288 --
1289 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
1290 log_msg('Validating delete_mark : ' || l_delete_mark);
1291 END IF;
1292
1293 IF (l_delete_mark <> FND_API.G_MISS_NUM) AND
1294 (l_delete_mark IS NOT NULL) THEN
1295 IF l_delete_mark NOT IN (0,1) THEN
1296 add_header_to_error_stack(p_rsrc_cost_rec); -- Bug 2659435
1297 FND_MESSAGE.SET_NAME('GMF','GMF_API_INVALID_DELETE_MARK');
1298 FND_MESSAGE.SET_TOKEN('DELETE_MARK',l_delete_mark);
1299 FND_MSG_PUB.Add;
1300 RAISE FND_API.G_EXC_ERROR;
1301 END IF;
1302 ELSIF (l_delete_mark = FND_API.G_MISS_NUM AND
1303 G_operation = 'UPDATE') OR
1304 (G_operation = 'INSERT') THEN
1305 add_header_to_error_stack(p_rsrc_cost_rec); -- Bug 2659435
1306 FND_MESSAGE.SET_NAME('GMF','GMF_API_DELETE_MARK_REQ');
1307 FND_MSG_PUB.Add;
1308 RAISE FND_API.G_EXC_ERROR;
1309 END IF;
1310
1311 -- Bug 2692459
1312 IF ((G_operation = 'UPDATE') AND (l_delete_mark = 1)) THEN
1313 add_header_to_error_stack(p_rsrc_cost_rec);
1314 FND_MESSAGE.SET_NAME('GMF','GMF_API_CANT_MARK_FOR_PURGE');
1315 FND_MSG_PUB.Add;
1316 RAISE FND_API.G_EXC_ERROR;
1317 END IF;
1318 -- End Delete Mark
1319
1320 END IF; -- Bug 2692459: G_operation <> 'DELETE'
1321
1322 -- Populate WHO columns
1323 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
1324 log_msg('Validating user name : ' || l_user_name);
1325 END IF;
1326
1327 IF (l_user_name <> FND_API.G_MISS_CHAR) AND
1328 (l_user_name IS NOT NULL) THEN
1329 GMA_GLOBAL_GRP.Get_who( p_user_name => l_user_name
1330 , x_user_id => l_user_id
1331 );
1332
1333 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
1334 log_msg('user id : ' || l_user_id);
1335 END IF;
1336
1337 IF l_user_id = -1 THEN -- Bug 2681243: GMA changed return status value to -1.
1338 add_header_to_error_stack(p_rsrc_cost_rec); -- Bug 2659435
1339 FND_MESSAGE.SET_NAME('GMF','GMF_API_INVALID_USER_NAME');
1340 FND_MESSAGE.SET_TOKEN('USER_NAME',l_user_name);
1341 FND_MSG_PUB.Add;
1342 RAISE FND_API.G_EXC_ERROR;
1343 END IF;
1344
1345 ELSE
1346 add_header_to_error_stack(p_rsrc_cost_rec); -- Bug 2659435
1347 FND_MESSAGE.SET_NAME('GMF','GMF_API_USER_NAME_REQ');
1348 FND_MSG_PUB.Add;
1349 RAISE FND_API.G_EXC_ERROR;
1350 END IF;
1351 -- End User Name
1352
1353
1354 x_rsrc_cost_rec.resources := l_resources ;
1355 x_rsrc_cost_rec.legal_entity_id := l_legal_entity_id ;
1356 x_rsrc_cost_rec.organization_id := l_organization_id ;
1357 x_rsrc_cost_rec.organization_code:= l_organization_code ;
1358 x_rsrc_cost_rec.period_id := l_period_id;
1359 x_rsrc_cost_rec.calendar_code := l_calendar_code ;
1360 x_rsrc_cost_rec.period_code := l_period_code ;
1361 x_rsrc_cost_rec.cost_type_id := l_cost_type_id ;
1362 x_rsrc_cost_rec.cost_mthd_code := l_cost_type_code ;
1363 x_rsrc_cost_rec.usage_uom := l_usage_uom ;
1364 x_rsrc_cost_rec.nominal_cost := round(l_nominal_cost,9) ;
1365 x_rsrc_cost_rec.delete_mark := l_delete_mark ;
1366 x_rsrc_cost_rec.user_name := l_user_name ;
1367 x_user_id := l_user_id ;
1368
1369 EXCEPTION -- Bug 2681243: removed when others to capture ORA errors.
1370 WHEN FND_API.G_EXC_ERROR THEN
1371 x_return_status := FND_API.G_RET_STS_ERROR ;
1372 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1374 END Validate_Input_Params;
1375
1376 -- Func start of comments
1377 --+==========================================================================+
1378 --| Function Name |
1379 --| check_records_exist |
1380 --| |
1381 --| DESCRIPTION |
1382 --| This procedure checks for the existance of records for a given |
1383 --| resource, orgn, calendar, period and cost method. |
1384 --| |
1385 --| USAGE |
1386 --| In case of insert API, if record exists raise error. |
1387 --| In case of update/delete API, if record does not exists raise error|
1388 --| |
1389 --| PARAMETERS |
1390 --| p_resources IN VARCHAR2 - Actual Message Text |
1391 --| p_orgn_code IN VARCHAR2 - Actual Message Text |
1392 --| p_calendar_code IN VARCHAR2 - Actual Message Text |
1393 --| p_period_code IN VARCHAR2 - Actual Message Text |
1394 --| p_cost_mthd_code IN VARCHAR2 - Actual Message Text |
1395 --| |
1396 --| RETURNS |
1397 --| TRUE : If records exist |
1398 --| TRUE : If records does not exist |
1399 --| |
1400 --| HISTORY |
1401 --| 27/02/2001 Uday Moogla - Created |
1402 --| 2-sep-2005 pmarada - Modified the cursor and where clause |
1403 --| 07-mar-2007 pmarada - Bug 5586122 Modified the cursor parameters, |
1404 --| |
1405 --+==========================================================================+
1406 -- Func end of comments
1407
1408 FUNCTION check_records_exist
1409 (
1410 p_legal_entity_id IN cm_rsrc_dtl.legal_entity_id%TYPE,
1411 p_organization_id IN cm_rsrc_dtl.organization_id%TYPE,
1412 p_resources IN cm_rsrc_dtl.resources%TYPE,
1413 p_period_id IN cm_rsrc_dtl.period_id%TYPE,
1414 p_cost_type_id IN cm_rsrc_dtl.cost_type_id%TYPE
1415 )
1416 RETURN BOOLEAN
1417 IS
1418 CURSOR Cur_rsrc_dtl
1419 ( cp_legal_entity_id cm_rsrc_dtl.legal_entity_id%TYPE,
1420 cp_organization_id cm_rsrc_dtl.organization_id%TYPE ,
1421 cp_resources cm_rsrc_dtl.resources%TYPE ,
1422 cp_period_id cm_rsrc_dtl.period_id%TYPE,
1423 cp_cost_type_id cm_rsrc_dtl.cost_type_id%TYPE
1424 ) IS
1425 SELECT 'x'
1426 FROM cm_rsrc_dtl
1427 WHERE legal_entity_id = cp_legal_entity_id
1428 AND organization_id = cp_organization_id
1429 AND resources = cp_resources
1430 AND period_id = cp_period_id
1431 AND cost_type_id = cp_cost_type_id;
1432
1433 l_rec_found VARCHAR2(10);
1434 BEGIN
1435
1436 l_rec_found := NULL;
1437 OPEN Cur_rsrc_dtl(p_legal_entity_id, p_organization_id, p_resources, p_period_id, p_cost_type_id) ;
1438 FETCH cur_rsrc_dtl INTO l_rec_found;
1439
1440 IF (l_rec_found IS NOT NULL) THEN
1441 RETURN TRUE;
1442 ELSE
1443 RETURN FALSE ;
1444 END IF;
1445 CLOSE Cur_rsrc_dtl ;
1446
1447 END check_records_exist ;
1448
1449 -- Func start of comments
1450 --+==========================================================================+
1451 --| Procedure Name |
1452 --| log_msg |
1453 --| |
1454 --| DESCRIPTION |
1455 --| This procedure logs messages to message stack. |
1456 --| |
1457 --| PARAMETERS |
1458 --| p_msg_lvl IN NUMBER(10) - Message Level |
1459 --| p_msg_text IN NUMBER(10) - Actual Message Text |
1460 --| |
1461 --| RETURNS |
1462 --| |
1463 --| HISTORY |
1464 --| 27/02/2001 Uday Moogla - Created |
1465 --| 05/nov/2002 Uday Moogala Bug 2659435 |
1466 --| Removed first param for debug level |
1467 --| |
1468 --+==========================================================================+
1469 -- Func end of comments
1470
1471 PROCEDURE log_msg
1472 (
1473 p_msg_text IN VARCHAR2
1474 )
1475 IS
1476 BEGIN
1477
1478 -- IF FND_MSG_PUB.Check_Msg_Level (p_msg_lvl) THEN Bug 2659435
1479 FND_MESSAGE.SET_NAME('GMF','GMF_API_DEBUG');
1480 FND_MESSAGE.SET_TOKEN('MSG',p_msg_text);
1481 FND_MSG_PUB.Add;
1482 -- END IF; Bug 2659435
1483
1484 END log_msg ;
1485
1486 -- Func start of comments
1487 --+==========================================================================+
1488 --| Procedure Name |
1489 --| add_header_to_error_stack |
1490 --| |
1491 --| DESCRIPTION |
1492 --| This procedure logs header to message stack. |
1493 --| |
1494 --| PARAMETERS |
1495 --| p_header Header Record to be logged |
1496 --| |
1497 --| RETURNS |
1498 --| |
1499 --| HISTORY |
1500 --| 05/11/2001 Uday Moogla - Created Bug 2659435 |
1501 --| 20-sep-2005 Pmarada - modified the message and tokens |
1502 --| |
1503 --+==========================================================================+
1504 -- Func end of comments
1505
1506 PROCEDURE add_header_to_error_stack
1507 (
1508 p_header Resource_Cost_Rec_Type
1509 )
1510 IS
1511 BEGIN
1512
1513 IF G_header_logged = 'N' THEN
1514 G_header_logged := 'Y';
1515 FND_MESSAGE.SET_NAME('GMF','GMF_API_RESOURCE_COST_HEADER');
1516 FND_MESSAGE.SET_TOKEN('RESOURCES',p_header.resources);
1517 FND_MESSAGE.SET_TOKEN('LEGAL_ENTITY_ID',p_header.legal_entity_id);
1518 FND_MESSAGE.SET_TOKEN('ORGANIZATION_ID',p_header.organization_id);
1519 FND_MESSAGE.SET_TOKEN('PERIOD_ID',p_header.period_id);
1520 FND_MESSAGE.SET_TOKEN('COST_TYPE_ID',p_header.cost_type_id);
1521 FND_MSG_PUB.Add;
1522 END IF;
1523
1524 END add_header_to_error_stack;
1525
1526 END GMF_ResourceCost_PUB;