[Home] [Help]
PACKAGE BODY: APPS.CSD_SERVICE_CODES_PVT
Source
1 PACKAGE BODY CSD_SERVICE_CODES_PVT as
2 /* $Header: csdvcscb.pls 115.5 2004/02/10 03:15:18 gilam noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_SERVICE_CODES_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdvcscb.pls';
6
7 /*--------------------------------------------------*/
8 /* procedure name: Create_Service_Code */
9 /* description : procedure used to create */
10 /* service code */
11 /* */
12 /*--------------------------------------------------*/
13 PROCEDURE Create_Service_Code
14 (
15 p_api_version IN NUMBER,
16 p_commit IN VARCHAR2,
17 p_init_msg_list IN VARCHAR2,
18 p_validation_level IN NUMBER,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2,
22 p_service_code_rec IN SERVICE_CODE_REC_TYPE,
23 x_service_code_id OUT NOCOPY NUMBER
24 ) IS
25
26 -- CONSTANTS --
27 lc_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
28 lc_stat_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
29 lc_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
30 lc_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
31 lc_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
32 lc_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
33 lc_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
34 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_service_codes_pvt.create_service_code';
35 lc_api_name CONSTANT VARCHAR2(30) := 'Create_Service_Code';
36 lc_api_version CONSTANT NUMBER := 1.0;
37
38 -- VARIABLES --
39 l_msg_count NUMBER;
40 l_msg_data VARCHAR2(100);
41 l_msg_index NUMBER;
42 l_dummy VARCHAR2(1) := null;
43 l_obj_ver_num NUMBER := 1;
44 l_rowid VARCHAR2(32767);
45
46 -- EXCEPTIONS --
47 CSD_SC_CODE_EXISTS EXCEPTION;
48
49 BEGIN
50 -- Standard Start of API savepoint
51 SAVEPOINT Create_Service_Code;
52
53 -- Standard call to check for call compatibility.
54 IF NOT FND_API.Compatible_API_Call (lc_api_version,
55 p_api_version,
56 lc_api_name ,
57 G_PKG_NAME )
58 THEN
59 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
60 END IF;
61
62 -- Initialize message list if p_init_msg_list is set to TRUE.
63 IF FND_API.to_Boolean( p_init_msg_list ) THEN
64 FND_MSG_PUB.initialize;
65 END IF;
66
67 IF (lc_proc_level >= lc_debug_level) THEN
68 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
69 'Entered Create_Service_Code');
70 END IF;
71
72 -- log parameters
73 IF (lc_stat_level >= lc_debug_level) THEN
74 csd_gen_utility_pvt.dump_service_code_rec
75 ( p_service_code_rec => p_service_code_rec);
76 END IF;
77
78 -- Initialize API return status to success
79 x_return_status := FND_API.G_RET_STS_SUCCESS;
80
81 -- Api body starts
82
83 -- Check the required parameters
84 if (lc_proc_level >= lc_debug_level) then
85 FND_LOG.STRING(lc_proc_level, lc_mod_name,
86 'Checking required parameters');
87 end if;
88
89 -- Check the required parameter
90 CSD_PROCESS_UTIL.Check_Reqd_Param
91 ( p_param_value => p_service_code_rec.service_code,
92 p_param_name => 'SERVICE_CODE',
93 p_api_name => lc_api_name);
94
95 -- Check the required parameter
96 CSD_PROCESS_UTIL.Check_Reqd_Param
97 ( p_param_value => p_service_code_rec.name,
98 p_param_name => 'NAME',
99 p_api_name => lc_api_name);
100
101 -- Validate the code for service code
102 if (lc_proc_level >= lc_debug_level) then
103 FND_LOG.STRING(lc_proc_level, lc_mod_name,
104 'Validate if the code of service code already exists');
105 end if;
106
107 -- Validate the code for service code
108 Begin
109 select 'X'
110 into l_dummy
111 from csd_service_codes_b
112 where service_code = UPPER(p_service_code_rec.service_code);
113
114 Exception
115
116 WHEN no_data_found THEN
117 l_dummy := null;
118
119 WHEN others THEN
120 l_dummy := 'X';
121
122 End;
123
124 -- If code already exists, throw an error
125 IF (l_dummy = 'X') then
126 RAISE CSD_SC_CODE_EXISTS;
127 ELSE
128 if (lc_stat_level >= lc_debug_level) then
129 FND_LOG.STRING(lc_stat_level, lc_mod_name,
130 'Service code does not exist');
131 end if;
132 END IF;
133
134 -- Insert row
135 if (lc_proc_level >= lc_debug_level) then
136 FND_LOG.STRING(lc_proc_level, lc_mod_name,
137 'Calling Insert_Row table handler');
138 end if;
139
140 Begin
141
142 -- Insert the new service code
143 CSD_SERVICE_CODES_PKG.Insert_Row
144 (px_rowid => l_rowid,
145 px_service_code_id => x_service_code_id,
146 p_object_version_number => l_obj_ver_num,
147 p_created_by => FND_GLOBAL.USER_ID,
148 p_creation_date => SYSDATE,
149 p_last_updated_by => FND_GLOBAL.USER_ID,
150 p_last_update_date => SYSDATE,
151 p_last_update_login => FND_GLOBAL.LOGIN_ID,
152 p_service_code => p_service_code_rec.service_code,
153 p_name => p_service_code_rec.name,
154 p_description => p_service_code_rec.description,
155 p_active_from => p_service_code_rec.active_from,
156 p_active_to => p_service_code_rec.active_to,
157 p_attribute_category => p_service_code_rec.attribute_category,
158 p_attribute1 => p_service_code_rec.attribute1,
159 p_attribute2 => p_service_code_rec.attribute2,
160 p_attribute3 => p_service_code_rec.attribute3,
161 p_attribute4 => p_service_code_rec.attribute4,
162 p_attribute5 => p_service_code_rec.attribute5,
163 p_attribute6 => p_service_code_rec.attribute6,
164 p_attribute7 => p_service_code_rec.attribute7,
165 p_attribute8 => p_service_code_rec.attribute8,
166 p_attribute9 => p_service_code_rec.attribute9,
167 p_attribute10 => p_service_code_rec.attribute10,
168 p_attribute11 => p_service_code_rec.attribute11,
169 p_attribute12 => p_service_code_rec.attribute12,
170 p_attribute13 => p_service_code_rec.attribute13,
171 p_attribute14 => p_service_code_rec.attribute14,
172 p_attribute15 => p_service_code_rec.attribute15
173 );
174
175 EXCEPTION
176 WHEN OTHERS THEN
177 IF ( lc_excep_level >= lc_debug_level) THEN
178 FND_LOG.STRING(lc_excep_level,lc_mod_name,'Others exception in CSD_SERVICE_CODES_PKG.Insert_Row Call :'||SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1,255));
179 END IF;
180 x_return_status := FND_API.G_RET_STS_ERROR;
181 END;
182
183 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
184 RAISE FND_API.G_EXC_ERROR;
185 END IF;
186
187 if (lc_proc_level >= lc_debug_level) then
188 FND_LOG.STRING(lc_proc_level, lc_mod_name,
189 'Returned from Insert_Row table handler');
190 end if;
191
192 -- Api body ends here
193
194 -- Standard check of p_commit.
195 IF FND_API.To_Boolean( p_commit ) THEN
196 COMMIT WORK;
197 END IF;
198
199 -- Standard call to get message count and IF count is get message info.
200 FND_MSG_PUB.Count_And_Get
201 (p_count => x_msg_count,
202 p_data => x_msg_data );
203
204 IF (lc_proc_level >= lc_debug_level) THEN
205 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
206 'Leaving Create_Service_Code');
207 END IF;
208
209 EXCEPTION
210 WHEN CSD_SC_CODE_EXISTS THEN
211 ROLLBACK TO Create_Service_Code;
212 -- Service code already exists
213 x_return_status := FND_API.G_RET_STS_ERROR ;
214
215 -- save message in fnd stack
216 if (lc_stat_level >= lc_debug_level) then
217 FND_LOG.STRING(lc_stat_level, lc_mod_name,
218 'Adding message CSD_SC_CODE_EXISTS to FND_MSG stack');
219 end if;
220 FND_MESSAGE.SET_NAME('CSD','CSD_SC_CODE_EXISTS');
221 FND_MESSAGE.SET_TOKEN('SERVICE_CODE',p_service_code_rec.service_code);
222 FND_MSG_PUB.ADD;
223 FND_MSG_PUB.Count_And_Get
224 (p_count => x_msg_count,
225 p_data => x_msg_data );
226
227 -- save message in debug log
228 IF (lc_excep_level >= lc_debug_level) THEN
229 FND_LOG.STRING(lc_excep_level, lc_mod_name,
230 'Service code already exists');
231 END IF;
232
233 WHEN FND_API.G_EXC_ERROR THEN
234 ROLLBACK TO Create_Service_Code;
235 x_return_status := FND_API.G_RET_STS_ERROR;
236
237 FND_MSG_PUB.Count_And_Get
238 (p_count => x_msg_count,
239 p_data => x_msg_data );
240
241 -- save message in debug log
242 IF (lc_excep_level >= lc_debug_level) THEN
243 FND_LOG.STRING(lc_excep_level, lc_mod_name,
244 'EXC_ERROR['||x_msg_data||']');
245 END IF;
246
247 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
248 ROLLBACK TO Create_Service_Code;
249 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
250
251 -- save message in fnd stack
252 IF FND_MSG_PUB.Check_Msg_Level
253 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
254 THEN
255 if (lc_stat_level >= lc_debug_level) then
256 FND_LOG.STRING(lc_stat_level, lc_mod_name,
257 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
258 end if;
259 FND_MSG_PUB.Add_Exc_Msg
260 (G_PKG_NAME ,
261 lc_api_name );
262 END IF;
263
264 FND_MSG_PUB.Count_And_Get
265 ( p_count => x_msg_count,
266 p_data => x_msg_data );
267
268 -- save message in debug log
269 IF (lc_excep_level >= lc_debug_level) THEN
270 FND_LOG.STRING(lc_excep_level, lc_mod_name,
271 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
272 END IF;
273
274 WHEN OTHERS THEN
275 ROLLBACK TO Create_Service_Code;
276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
277
278 -- save message in fnd stack
279 IF FND_MSG_PUB.Check_Msg_Level
280 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
281 THEN
282 if (lc_stat_level >= lc_debug_level) then
283 FND_LOG.STRING(lc_stat_level, lc_mod_name,
284 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
285 end if;
286 FND_MSG_PUB.Add_Exc_Msg
287 (G_PKG_NAME ,
288 lc_api_name );
289 END IF;
290
291 FND_MSG_PUB.Count_And_Get
292 (p_count => x_msg_count,
293 p_data => x_msg_data );
294
295 -- save message in debug log
296 IF (lc_excep_level >= lc_debug_level) THEN
297 -- create a seeded message
298 FND_LOG.STRING(lc_excep_level, lc_mod_name,
299 'SQL Message['||sqlerrm||']' );
300 END IF;
301
302 END Create_Service_Code;
303
304
305 /*--------------------------------------------------*/
306 /* procedure name: Update_Service_Code */
307 /* description : procedure used to update */
308 /* service code */
309 /* */
310 /*--------------------------------------------------*/
311 PROCEDURE Update_Service_Code
312 (
313 p_api_version IN NUMBER,
314 p_commit IN VARCHAR2,
315 p_init_msg_list IN VARCHAR2,
316 p_validation_level IN NUMBER,
317 x_return_status OUT NOCOPY VARCHAR2,
318 x_msg_count OUT NOCOPY NUMBER,
319 x_msg_data OUT NOCOPY VARCHAR2,
320 p_service_code_rec IN SERVICE_CODE_REC_TYPE,
321 x_obj_ver_number OUT NOCOPY NUMBER
322 ) IS
323
324 -- CONSTANTS --
325 lc_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
326 lc_stat_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
327 lc_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
328 lc_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
329 lc_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
330 lc_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
331 lc_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
332 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_service_codes_pvt.update_service_code';
333 lc_api_name CONSTANT VARCHAR2(30) := 'Update_Service_Code';
334 lc_api_version CONSTANT NUMBER := 1.0;
335
336 -- VARIABLES --
337 l_msg_count NUMBER;
338 l_msg_data VARCHAR2(100);
339 l_msg_index NUMBER;
340 l_dummy VARCHAR2(1) := null;
341 l_obj_ver_num NUMBER := 1;
342 l_rowid VARCHAR2(32767);
343
344 -- EXCEPTIONS --
345 CSD_SC_CODE_MISSING EXCEPTION;
346 CSD_SC_NAME_MISSING EXCEPTION;
347 CSD_SC_ID_INVALID EXCEPTION;
348 CSD_SC_GET_OVN_ERROR EXCEPTION;
349 CSD_SC_OVN_MISMATCH EXCEPTION;
350 CSD_SC_CODE_EXISTS EXCEPTION;
351
352 BEGIN
353 -- Standard Start of API savepoint
354 SAVEPOINT Update_Service_Code;
355
356 -- Standard call to check for call compatibility.
357 IF NOT FND_API.Compatible_API_Call (lc_api_version,
358 p_api_version,
359 lc_api_name ,
360 G_PKG_NAME )
361 THEN
362 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
363 END IF;
364
365 -- Initialize message list if p_init_msg_list is set to TRUE.
366 IF FND_API.to_Boolean( p_init_msg_list ) THEN
367 FND_MSG_PUB.initialize;
368 END IF;
369
370 IF (lc_proc_level >= lc_debug_level) THEN
371 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
372 'Entered Update_Service_Code');
373 END IF;
374
375 -- log parameters
376 IF (lc_stat_level >= lc_debug_level) THEN
377 csd_gen_utility_pvt.dump_service_code_rec
378 ( p_service_code_rec => p_service_code_rec);
379 END IF;
380
381 -- Initialize API return status to success
382 x_return_status := FND_API.G_RET_STS_SUCCESS;
383
384 -- Api body starts
385
386 -- Check the required parameters
387 if (lc_proc_level >= lc_debug_level) then
388 FND_LOG.STRING(lc_proc_level, lc_mod_name,
389 'Checking required parameters');
390 end if;
391
392 -- Check the required parameter
393 CSD_PROCESS_UTIL.Check_Reqd_Param
394 ( p_param_value => p_service_code_rec.service_code_id,
395 p_param_name => 'SERVICE_CODE_ID',
396 p_api_name => lc_api_name);
397
398 -- Check if required parameter is passed in as G_MISS
399 if (lc_proc_level >= lc_debug_level) then
400 FND_LOG.STRING(lc_proc_level, lc_mod_name,
401 'Checking if required parameters are passed in as G_MISS');
402 end if;
403
404 IF (p_service_code_rec.service_code = FND_API.G_MISS_CHAR) THEN
405 RAISE CSD_SC_CODE_MISSING;
406 END IF;
407
408 -- Check if required parameter is passed in as G_MISS
409 IF (p_service_code_rec.name = FND_API.G_MISS_CHAR) THEN
410 RAISE CSD_SC_NAME_MISSING;
411 END IF;
412
413 -- Validate the id for service code
414 if (lc_proc_level >= lc_debug_level) then
415 FND_LOG.STRING(lc_proc_level, lc_mod_name,
416 'Validate the ID for service code');
417 end if;
418
419 -- Validate the id for service code
420 Begin
421 select 'X'
422 into l_dummy
423 from csd_service_codes_b
424 where service_code_id = p_service_code_rec.service_code_id;
425
426 Exception
427
428 WHEN others THEN
429 l_dummy := null;
430
431 End;
432
433 -- If invalid id, throw an error
434 IF (l_dummy <> 'X') then
435 RAISE CSD_SC_ID_INVALID;
436 ELSE
437 if (lc_stat_level >= lc_debug_level) then
438 FND_LOG.STRING(lc_stat_level, lc_mod_name,
439 'Service code id is valid');
440 end if;
441 END IF;
442
443 -- Get the object version number for service code
444 if (lc_proc_level >= lc_debug_level) then
445 FND_LOG.STRING(lc_proc_level, lc_mod_name,
446 'Get object version number for service code');
447 end if;
448
449 -- Get object version number for service code
450 Begin
451 select object_version_number
452 into l_obj_ver_num
453 from csd_service_codes_b
454 where service_code_id = p_service_code_rec.service_code_id;
455
456 Exception
457
458 WHEN others THEN
459 l_obj_ver_num := null;
460
461 End;
462
463 -- If no object version number, throw an error
464 IF (l_obj_ver_num is null) then
465 RAISE CSD_SC_GET_OVN_ERROR;
466 ELSE
467 if (lc_stat_level >= lc_debug_level) then
468 FND_LOG.STRING(lc_stat_level, lc_mod_name,
469 'Retrieved object version number');
470 end if;
471 END IF;
472
473 -- Validate if object version number of service code is same as the one passed in
474 IF NVL(p_service_code_rec.object_version_number,FND_API.G_MISS_NUM) <> l_obj_ver_num THEN
475 RAISE CSD_SC_OVN_MISMATCH;
476 END IF;
477
478 -- Validate the code for service code
479 if (lc_proc_level >= lc_debug_level) then
480 FND_LOG.STRING(lc_proc_level, lc_mod_name,
481 'Validate if the code of the service code already exists ');
482 end if;
483
484 -- Validate the code for service code
485 Begin
486
487 l_dummy := null;
488
489 select 'X'
490 into l_dummy
491 from csd_service_codes_b
492 where service_code = UPPER(p_service_code_rec.service_code)
493 and service_code_id <> p_service_code_rec.service_code_id;
494
495 Exception
496
497 WHEN no_data_found THEN
498 l_dummy := null;
499
500 WHEN others THEN
501 l_dummy := 'X';
502
503 End;
504
505 -- If code already exists, throw an error
506 IF (l_dummy = 'X') then
507 RAISE CSD_SC_CODE_EXISTS;
508 ELSE
509 if (lc_stat_level >= lc_debug_level) then
510 FND_LOG.STRING(lc_stat_level, lc_mod_name,
511 'Service code does not exist');
512 end if;
513 END IF;
514
515 -- Update row
516 if (lc_proc_level >= lc_debug_level) then
517 FND_LOG.STRING(lc_proc_level, lc_mod_name,
518 'Calling Update_Row table handler');
519 end if;
520
521 Begin
522
523 -- Update the service code
524 CSD_SERVICE_CODES_PKG.Update_Row
525 (p_service_code_id => p_service_code_rec.service_code_id,
526 p_object_version_number => l_obj_ver_num + 1,
527 p_created_by => FND_GLOBAL.USER_ID,
528 p_creation_date => SYSDATE,
529 p_last_updated_by => FND_GLOBAL.USER_ID,
530 p_last_update_date => SYSDATE,
531 p_last_update_login => FND_GLOBAL.LOGIN_ID,
532 p_service_code => p_service_code_rec.service_code,
533 p_name => p_service_code_rec.name,
534 p_description => p_service_code_rec.description,
535 p_active_from => p_service_code_rec.active_from,
536 p_active_to => p_service_code_rec.active_to,
537 p_attribute_category => p_service_code_rec.attribute_category,
538 p_attribute1 => p_service_code_rec.attribute1,
539 p_attribute2 => p_service_code_rec.attribute2,
540 p_attribute3 => p_service_code_rec.attribute3,
541 p_attribute4 => p_service_code_rec.attribute4,
542 p_attribute5 => p_service_code_rec.attribute5,
543 p_attribute6 => p_service_code_rec.attribute6,
544 p_attribute7 => p_service_code_rec.attribute7,
545 p_attribute8 => p_service_code_rec.attribute8,
546 p_attribute9 => p_service_code_rec.attribute9,
547 p_attribute10 => p_service_code_rec.attribute10,
548 p_attribute11 => p_service_code_rec.attribute11,
549 p_attribute12 => p_service_code_rec.attribute12,
550 p_attribute13 => p_service_code_rec.attribute13,
551 p_attribute14 => p_service_code_rec.attribute14,
552 p_attribute15 => p_service_code_rec.attribute15
553 );
554
555 x_obj_ver_number := l_obj_ver_num + 1;
556
557 EXCEPTION
558 WHEN OTHERS THEN
559 IF ( lc_excep_level >= lc_debug_level) THEN
560 FND_LOG.STRING(lc_excep_level,lc_mod_name,'Others exception in CSD_SERVICE_CODES_PKG.Update_Row Call :'||SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1,255));
561 END IF;
562 x_return_status := FND_API.G_RET_STS_ERROR;
563 END;
564
565 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
566 RAISE FND_API.G_EXC_ERROR;
567 END IF;
568
569 if (lc_proc_level >= lc_debug_level) then
570 FND_LOG.STRING(lc_proc_level, lc_mod_name,
571 'Returned from Update_Row table handler');
572 end if;
573
574 -- Api body ends here
575
576 -- Standard check of p_commit.
577 IF FND_API.To_Boolean( p_commit ) THEN
578 COMMIT WORK;
579 END IF;
580
581 -- Standard call to get message count and IF count is get message info.
582 FND_MSG_PUB.Count_And_Get
583 (p_count => x_msg_count,
584 p_data => x_msg_data );
585
586 IF (lc_proc_level >= lc_debug_level) THEN
587 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
588 'Leaving Update_Service_Code');
589 END IF;
590
591 EXCEPTION
592
593 WHEN CSD_SC_CODE_MISSING THEN
594 ROLLBACK TO Update_Service_Code;
595 -- Service code already exists
596 x_return_status := FND_API.G_RET_STS_ERROR ;
597
598 -- save message in fnd stack
599 if (lc_stat_level >= lc_debug_level) then
600 FND_LOG.STRING(lc_stat_level, lc_mod_name,
601 'Adding message CSD_SC_CODE_MISSING to FND_MSG stack');
602 end if;
603 FND_MESSAGE.SET_NAME('CSD','CSD_API_MISSING_PARAM');
604 FND_MESSAGE.SET_TOKEN('API_NAME',lc_api_name);
605 FND_MESSAGE.SET_TOKEN('MISSING_PARAM','SERVICE_CODE');
606 FND_MSG_PUB.ADD;
607 FND_MSG_PUB.Count_And_Get
608 (p_count => x_msg_count,
609 p_data => x_msg_data );
610
611 -- save message in debug log
612 if (lc_proc_level >= lc_debug_level) then
613 FND_LOG.STRING(lc_proc_level, lc_mod_name,
614 'Required parameter Service Code is passed in as G_MISS_CHAR');
615 end if;
616
617 WHEN CSD_SC_NAME_MISSING THEN
618 ROLLBACK TO Update_Service_Code;
619 -- Service code name already exists
620 x_return_status := FND_API.G_RET_STS_ERROR ;
621
622 -- save message in fnd stack
623 if (lc_stat_level >= lc_debug_level) then
624 FND_LOG.STRING(lc_stat_level, lc_mod_name,
625 'Adding message CSD_SC_CODE_MISSING to FND_MSG stack');
626 end if;
627 FND_MESSAGE.SET_NAME('CSD','CSD_API_MISSING_PARAM');
628 FND_MESSAGE.SET_TOKEN('API_NAME',lc_api_name);
629 FND_MESSAGE.SET_TOKEN('MISSING_PARAM','NAME');
630 FND_MSG_PUB.ADD;
631 FND_MSG_PUB.Count_And_Get
632 (p_count => x_msg_count,
633 p_data => x_msg_data );
634
635 -- save message in debug log
636 if (lc_proc_level >= lc_debug_level) then
637 FND_LOG.STRING(lc_proc_level, lc_mod_name,
638 'Required parameter Name is passed in as G_MISS_CHAR');
639 end if;
640
641 WHEN CSD_SC_ID_INVALID THEN
642 ROLLBACK TO Update_Service_Code;
643 -- Service code name already exists
644 x_return_status := FND_API.G_RET_STS_ERROR ;
645
646 -- save message in fnd stack
647 if (lc_stat_level >= lc_debug_level) then
648 FND_LOG.STRING(lc_stat_level, lc_mod_name,
649 'Adding message CSD_SC_ID_INVALID to FND_MSG stack');
650 end if;
651 FND_MESSAGE.SET_NAME('CSD','CSD_SC_ID_INVALID');
652 FND_MESSAGE.SET_TOKEN('SERVICE_CODE',p_service_code_rec.service_code);
653 FND_MSG_PUB.ADD;
654 FND_MSG_PUB.Count_And_Get
655 (p_count => x_msg_count,
656 p_data => x_msg_data );
657
658 -- save message in debug log
659 if (lc_proc_level >= lc_debug_level) then
660 FND_LOG.STRING(lc_proc_level, lc_mod_name,
661 'Service code id is invalid');
662 end if;
663
664 WHEN CSD_SC_GET_OVN_ERROR THEN
665 ROLLBACK TO Update_Service_Code;
666 -- Service code name already exists
667 x_return_status := FND_API.G_RET_STS_ERROR ;
668
669 -- save message in fnd stack
670 if (lc_stat_level >= lc_debug_level) then
671 FND_LOG.STRING(lc_stat_level, lc_mod_name,
672 'Adding message CSD_SC_GET_OVN_ERROR to FND_MSG stack');
673 end if;
674 FND_MESSAGE.SET_NAME('CSD','CSD_SC_GET_OVN_ERROR');
675 FND_MESSAGE.SET_TOKEN('SERVICE_CODE',p_service_code_rec.service_code);
676 FND_MSG_PUB.ADD;
677 FND_MSG_PUB.Count_And_Get
678 (p_count => x_msg_count,
679 p_data => x_msg_data );
680
681 -- save message in debug log
682 if (lc_proc_level >= lc_debug_level) then
683 FND_LOG.STRING(lc_proc_level, lc_mod_name,
684 'Error retrieving object version number');
685 end if;
686
687 WHEN CSD_SC_OVN_MISMATCH THEN
688 ROLLBACK TO Update_Service_Code;
689 -- Service code name already exists
690 x_return_status := FND_API.G_RET_STS_ERROR ;
691
692 -- save message in fnd stack
693 if (lc_stat_level >= lc_debug_level) then
694 FND_LOG.STRING(lc_stat_level, lc_mod_name,
695 'Adding message CSD_SC_OVN_MISMATCH to FND_MSG stack');
696 end if;
697 FND_MESSAGE.SET_NAME('CSD','CSD_SC_OVN_MISMATCH');
698 FND_MESSAGE.SET_TOKEN('SERVICE_CODE',p_service_code_rec.service_code);
699 FND_MSG_PUB.ADD;
700 FND_MSG_PUB.Count_And_Get
701 (p_count => x_msg_count,
702 p_data => x_msg_data );
703
704 -- save message in debug log
705 if (lc_proc_level >= lc_debug_level) then
706 FND_LOG.STRING(lc_proc_level, lc_mod_name,
707 'Object version number passed in does not match the existing one');
708 end if;
709
710 WHEN CSD_SC_CODE_EXISTS THEN
711 ROLLBACK TO Update_Service_Code;
712 -- Service code already exists
713 x_return_status := FND_API.G_RET_STS_ERROR ;
714
715 -- save message in fnd stack
716 if (lc_stat_level >= lc_debug_level) then
717 FND_LOG.STRING(lc_stat_level, lc_mod_name,
718 'Adding message CSD_SC_CODE_EXISTS to FND_MSG stack');
719 end if;
720 FND_MESSAGE.SET_NAME('CSD','CSD_SC_CODE_EXISTS');
721 FND_MESSAGE.SET_TOKEN('SERVICE_CODE',p_service_code_rec.service_code);
722 FND_MSG_PUB.ADD;
723 FND_MSG_PUB.Count_And_Get
724 (p_count => x_msg_count,
725 p_data => x_msg_data );
726
727 -- save message in debug log
728 IF (lc_excep_level >= lc_debug_level) THEN
729 FND_LOG.STRING(lc_excep_level, lc_mod_name,
730 'Service code already exists');
731 END IF;
732
733 WHEN FND_API.G_EXC_ERROR THEN
734 ROLLBACK TO Update_Service_Code;
735 x_return_status := FND_API.G_RET_STS_ERROR;
736
737 FND_MSG_PUB.Count_And_Get
738 (p_count => x_msg_count,
739 p_data => x_msg_data );
740
741 -- save message in debug log
742 IF (lc_excep_level >= lc_debug_level) THEN
743 FND_LOG.STRING(lc_excep_level, lc_mod_name,
744 'EXC_ERROR['||x_msg_data||']');
745 END IF;
746
747 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
748 ROLLBACK TO Update_Service_Code;
749 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
750
751 IF FND_MSG_PUB.Check_Msg_Level
752 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
753 THEN
754 if (lc_stat_level >= lc_debug_level) then
755 FND_LOG.STRING(lc_stat_level, lc_mod_name,
756 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
757 end if;
758 FND_MSG_PUB.Add_Exc_Msg
759 (G_PKG_NAME ,
760 lc_api_name );
761 END IF;
762
763 FND_MSG_PUB.Count_And_Get
764 ( p_count => x_msg_count,
765 p_data => x_msg_data );
766
767 -- save message in debug log
768 IF (lc_excep_level >= lc_debug_level) THEN
769 FND_LOG.STRING(lc_excep_level, lc_mod_name,
770 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
771 END IF;
772
773 WHEN OTHERS THEN
774 ROLLBACK TO Update_Service_Code;
775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
776
777 -- save message in fnd stack
778 IF FND_MSG_PUB.Check_Msg_Level
779 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
780 THEN
781 if (lc_stat_level >= lc_debug_level) then
782 FND_LOG.STRING(lc_stat_level, lc_mod_name,
783 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
784 end if;
785 FND_MSG_PUB.Add_Exc_Msg
786 (G_PKG_NAME ,
787 lc_api_name );
788 END IF;
789
790 FND_MSG_PUB.Count_And_Get
791 (p_count => x_msg_count,
792 p_data => x_msg_data );
793
794 -- save message in debug log
795 IF (lc_excep_level >= lc_debug_level) THEN
796 -- create a seeded message
797 FND_LOG.STRING(lc_excep_level, lc_mod_name,
798 'SQL Message['||sqlerrm||']' );
799 END IF;
800
801 END Update_Service_Code;
802
803 /*--------------------------------------------------*/
804 /* procedure name: Lock_Service_Code */
805 /* description : procedure used to lock */
806 /* service code */
807 /* */
808 /*--------------------------------------------------*/
809 PROCEDURE Lock_Service_Code
810 (
811 p_api_version IN NUMBER,
812 p_commit IN VARCHAR2,
813 p_init_msg_list IN VARCHAR2,
814 p_validation_level IN NUMBER,
815 x_return_status OUT NOCOPY VARCHAR2,
816 x_msg_count OUT NOCOPY NUMBER,
817 x_msg_data OUT NOCOPY VARCHAR2,
818 p_service_code_rec IN SERVICE_CODE_REC_TYPE
819 ) IS
820
821 -- CONSTANTS --
822 lc_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
823 lc_stat_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
824 lc_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
825 lc_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
826 lc_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
827 lc_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
828 lc_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
829 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_service_codes_pvt.lock_service_code';
830 lc_api_name CONSTANT VARCHAR2(30) := 'Lock_Service_Code';
831 lc_api_version CONSTANT NUMBER := 1.0;
832
833 -- VARIABLES --
834 l_msg_count NUMBER;
835 l_msg_data VARCHAR2(100);
836 l_msg_index NUMBER;
837 l_rowid VARCHAR2(32767);
838
839 BEGIN
840 -- Standard Start of API savepoint
841 SAVEPOINT Lock_Service_Code;
842
843 -- Standard call to check for call compatibility.
844 IF NOT FND_API.Compatible_API_Call (lc_api_version,
845 p_api_version,
846 lc_api_name ,
847 G_PKG_NAME )
848 THEN
849 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
850 END IF;
851
852 -- Initialize message list if p_init_msg_list is set to TRUE.
853 IF FND_API.to_Boolean( p_init_msg_list ) THEN
854 FND_MSG_PUB.initialize;
855 END IF;
856
857 IF (lc_proc_level >= lc_debug_level) THEN
858 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
859 'Entered Lock_Service_Code');
860 END IF;
861
862 -- Initialize API return status to success
863 x_return_status := FND_API.G_RET_STS_SUCCESS;
864
865 -- Api body starts
866
867 -- Check the required parameters
868 if (lc_proc_level >= lc_debug_level) then
869 FND_LOG.STRING(lc_proc_level, lc_mod_name,
870 'Checking required parameters');
871 end if;
872
873 -- Check the required parameter
874 CSD_PROCESS_UTIL.Check_Reqd_Param
875 ( p_param_value => p_service_code_rec.service_code_id,
876 p_param_name => 'SERVICE_CODE_ID',
877 p_api_name => lc_api_name);
878
879 -- Check the required parameter
880 CSD_PROCESS_UTIL.Check_Reqd_Param
881 ( p_param_value => p_service_code_rec.object_version_number,
882 p_param_name => 'OBJECT_VERSION_NUMBER',
883 p_api_name => lc_api_name);
884
885 -- Lock row
886 if (lc_proc_level >= lc_debug_level) then
887 FND_LOG.STRING(lc_proc_level, lc_mod_name,
888 'Calling Lock_Row table handler');
889 end if;
890
891 Begin
892
893 -- Lock the service code
894 CSD_SERVICE_CODES_PKG.Lock_Row
895 (px_rowid => l_rowid,
896 p_service_code_id => p_service_code_rec.service_code_id,
897 p_object_version_number => p_service_code_rec.object_version_number
898
899 --commented out the rest of the record
900 /*,
901 p_service_code => p_service_code_rec.service_code,
902 p_name => p_service_code_rec.name,
903 p_description => p_service_code_rec.description,
904 p_active_from => p_service_code_rec.active_from,
905 p_active_to => p_service_code_rec.active_to,
906 p_attribute_category => p_service_code_rec.attribute_category,
907 p_attribute1 => p_service_code_rec.attribute1,
908 p_attribute2 => p_service_code_rec.attribute2,
909 p_attribute3 => p_service_code_rec.attribute3,
910 p_attribute4 => p_service_code_rec.attribute4,
911 p_attribute5 => p_service_code_rec.attribute5,
912 p_attribute6 => p_service_code_rec.attribute6,
913 p_attribute7 => p_service_code_rec.attribute7,
914 p_attribute8 => p_service_code_rec.attribute8,
915 p_attribute9 => p_service_code_rec.attribute9,
916 p_attribute10 => p_service_code_rec.attribute10,
917 p_attribute11 => p_service_code_rec.attribute11,
918 p_attribute12 => p_service_code_rec.attribute12,
919 p_attribute13 => p_service_code_rec.attribute13,
920 p_attribute14 => p_service_code_rec.attribute14,
921 p_attribute15 => p_service_code_rec.attribute15
922 */
923 --
924 );
925
926 EXCEPTION
927 WHEN OTHERS THEN
928 IF ( lc_excep_level >= lc_debug_level) THEN
929 FND_LOG.STRING(lc_excep_level,lc_mod_name,'Others exception in CSD_SERVICE_CODES_PKG.Lock_Row Call :'||SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1,255));
930 END IF;
931 x_return_status := FND_API.G_RET_STS_ERROR;
932 END;
933
934 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
935 RAISE FND_API.G_EXC_ERROR;
936 END IF;
937
938 if (lc_proc_level >= lc_debug_level) then
939 FND_LOG.STRING(lc_proc_level, lc_mod_name,
940 'Returned from Lock_Row table handler');
941 end if;
942
943 -- Api body ends here
944
945 -- Standard check of p_commit.
946 IF FND_API.To_Boolean( p_commit ) THEN
947 COMMIT WORK;
948 END IF;
949
950 -- Standard call to get message count and IF count is get message info.
951 FND_MSG_PUB.Count_And_Get
952 (p_count => x_msg_count,
953 p_data => x_msg_data );
954
955 IF (lc_proc_level >= lc_debug_level) THEN
956 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
957 'Leaving Lock_Service_Code');
958 END IF;
959
960 EXCEPTION
961
962 WHEN FND_API.G_EXC_ERROR THEN
963 ROLLBACK TO Lock_Service_Code;
964 x_return_status := FND_API.G_RET_STS_ERROR;
965
966 FND_MSG_PUB.Count_And_Get
967 (p_count => x_msg_count,
968 p_data => x_msg_data );
969
970 -- save message in debug log
971 IF (lc_excep_level >= lc_debug_level) THEN
972 FND_LOG.STRING(lc_excep_level, lc_mod_name,
973 'EXC_ERROR['||x_msg_data||']');
974 END IF;
975
976 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
977 ROLLBACK TO Lock_Service_Code;
978 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
979
980 IF FND_MSG_PUB.Check_Msg_Level
981 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
982 THEN
983 if (lc_stat_level >= lc_debug_level) then
984 FND_LOG.STRING(lc_stat_level, lc_mod_name,
985 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
986 end if;
987 FND_MSG_PUB.Add_Exc_Msg
988 (G_PKG_NAME ,
989 lc_api_name );
990 END IF;
991
992 FND_MSG_PUB.Count_And_Get
993 ( p_count => x_msg_count,
994 p_data => x_msg_data );
995
996 -- save message in debug log
997 IF (lc_excep_level >= lc_debug_level) THEN
998 FND_LOG.STRING(lc_excep_level, lc_mod_name,
999 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
1000 END IF;
1001
1002 WHEN OTHERS THEN
1003 ROLLBACK TO Lock_Service_Code;
1004 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1005
1006 -- save message in fnd stack
1007 IF FND_MSG_PUB.Check_Msg_Level
1008 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1009 THEN
1010 if (lc_stat_level >= lc_debug_level) then
1011 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1012 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
1013 end if;
1014 FND_MSG_PUB.Add_Exc_Msg
1015 (G_PKG_NAME ,
1016 lc_api_name );
1017 END IF;
1018
1019 FND_MSG_PUB.Count_And_Get
1020 (p_count => x_msg_count,
1021 p_data => x_msg_data );
1022
1023 -- save message in debug log
1024 IF (lc_excep_level >= lc_debug_level) THEN
1025 -- create a seeded message
1026 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1027 'SQL Message['||sqlerrm||']' );
1028 END IF;
1029
1030 END Lock_Service_Code;
1031
1032 End CSD_SERVICE_CODES_PVT;
1033