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