[Home] [Help]
PACKAGE BODY: APPS.CSD_RO_SERVICE_CODES_PVT
Source
1 PACKAGE BODY CSD_RO_SERVICE_CODES_PVT as
2 /* $Header: csdvrscb.pls 120.2 2006/09/27 00:29:49 rfieldma noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_RO_SERVICE_CODES_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdvrscb.pls';
6
7 /*--------------------------------------------------*/
8 /* procedure name: Create_RO_Service_Code */
9 /* description : procedure used to create */
10 /* ro service code */
11 /* */
12 /*--------------------------------------------------*/
13 PROCEDURE Create_RO_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_ro_service_code_rec IN RO_SERVICE_CODE_REC_TYPE,
23 x_ro_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_ro_service_codes_pvt.create_ro_service_code';
35 lc_api_name CONSTANT VARCHAR2(30) := 'Create_RO_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
45 -- EXCEPTIONS --
46 -- CSD_RSC_ASSOCIATION_EXISTS EXCEPTION;
47
48 BEGIN
49 -- Standard Start of API savepoint
50 SAVEPOINT Create_RO_Service_Code;
51
52 -- Standard call to check for call compatibility.
53 IF NOT FND_API.Compatible_API_Call (lc_api_version,
54 p_api_version,
55 lc_api_name ,
56 G_PKG_NAME )
57 THEN
58 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
59 END IF;
60
61 -- Initialize message list if p_init_msg_list is set to TRUE.
62 IF FND_API.to_Boolean( p_init_msg_list ) THEN
63 FND_MSG_PUB.initialize;
64 END IF;
65
66 IF (lc_proc_level >= lc_debug_level) THEN
67 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
68 'Entered Create_RO_Service_Code');
69 END IF;
70
71 -- log parameters
72 IF (lc_stat_level >= lc_debug_level) THEN
73 csd_gen_utility_pvt.dump_ro_service_code_rec
74 ( p_ro_service_code_rec => p_ro_service_code_rec);
75 END IF;
76
77 -- Initialize API return status to success
78 x_return_status := FND_API.G_RET_STS_SUCCESS;
79
80 -- Api body starts
81
82 -- Check the required parameters
83 if (lc_proc_level >= lc_debug_level) then
84 FND_LOG.STRING(lc_proc_level, lc_mod_name,
85 'Checking required parameters');
86 end if;
87
88 -- Check the required parameter
89 CSD_PROCESS_UTIL.Check_Reqd_Param
90 ( p_param_value => p_ro_service_code_rec.repair_line_id,
91 p_param_name => 'REPAIR_LINE_ID',
92 p_api_name => lc_api_name);
93
94 -- Check the required parameter
95 CSD_PROCESS_UTIL.Check_Reqd_Param
96 ( p_param_value => p_ro_service_code_rec.service_code_id,
97 p_param_name => 'SERVICE_CODE_ID',
98 p_api_name => lc_api_name);
99
100 -- Check the required parameter, rfieldma 4666403
101 CSD_PROCESS_UTIL.Check_Reqd_Param
102 ( p_param_value => p_ro_service_code_rec.service_item_id,
103 p_param_name => 'SERVICE_ITEM_ID',
104 p_api_name => lc_api_name);
105
106 -- Check the required parameter
107 CSD_PROCESS_UTIL.Check_Reqd_Param
108 ( p_param_value => p_ro_service_code_rec.source_type_code,
109 p_param_name => 'SOURCE_TYPE_CODE',
110 p_api_name => lc_api_name);
111
112 -- Check the required parameter
113 CSD_PROCESS_UTIL.Check_Reqd_Param
114 ( p_param_value => p_ro_service_code_rec.applicable_flag,
115 p_param_name => 'APPLICABLE_FLAG',
116 p_api_name => lc_api_name);
117
118 -- Validate the repair line ID
119 if (lc_proc_level >= lc_debug_level) then
120 FND_LOG.STRING(lc_proc_level, lc_mod_name,
121 'Validate repair line id');
122 end if;
123
124 -- Validate the repair line ID
125 IF NOT( CSD_PROCESS_UTIL.Validate_rep_line_id
126 ( p_repair_line_id => p_ro_service_code_rec.repair_line_id )) THEN
127 RAISE FND_API.G_EXC_ERROR;
128 END IF;
129
130 /*
131 -- Validate the ro association for service code
132 if (lc_proc_level >= lc_debug_level) then
133 FND_LOG.STRING(lc_proc_level, lc_mod_name,
134 'Validate if the ro association for service code exists');
135 end if;
136
137 -- Validate the ro association for service code
138 Begin
139 l_dummy := null;
140
141 select 'X'
142 into l_dummy
143 from csd_ro_service_codes
144 where repair_line_id = p_ro_service_code_rec.repair_line_id
145 and service_code_id = p_ro_service_code_rec.service_code_id;
146
147 Exception
148
149 WHEN no_data_found THEN
150 null;
151
152 WHEN others THEN
153 l_dummy := 'X';
154
155 End;
156
157 -- If association exists, throw an error
158 IF (l_dummy = 'X') then
159 RAISE CSD_RSC_ASSOCIATION_EXISTS;
160 ELSE
161 if (lc_stat_level >= lc_debug_level) then
162 FND_LOG.STRING(lc_stat_level, lc_mod_name,
163 'RO service code association already exists');
164 end if;
165 END IF;
166 */
167
168 -- Insert row
169 if (lc_proc_level >= lc_debug_level) then
170 FND_LOG.STRING(lc_proc_level, lc_mod_name,
171 'Calling Insert_Row table handler');
172 end if;
173
174 BEGIN
175
176 -- Insert the new ro service code association
177 CSD_RO_SERVICE_CODES_PKG.Insert_Row
178 (px_ro_service_code_id => x_ro_service_code_id,
179 p_object_version_number => l_obj_ver_num,
180 p_repair_line_id => p_ro_service_code_rec.repair_line_id,
181 p_service_code_id => p_ro_service_code_rec.service_code_id,
182 p_created_by => FND_GLOBAL.USER_ID,
183 p_creation_date => SYSDATE,
184 p_last_updated_by => FND_GLOBAL.USER_ID,
185 p_last_update_date => SYSDATE,
186 p_last_update_login => FND_GLOBAL.LOGIN_ID,
187 p_source_type_code => p_ro_service_code_rec.source_type_code,
188 p_source_solution_id => p_ro_service_code_rec.source_solution_id,
189 p_applicable_flag => p_ro_service_code_rec.applicable_flag,
190 p_applied_to_est_flag => 'N',
191 p_applied_to_work_flag => 'N',
192 p_attribute_category => p_ro_service_code_rec.attribute_category,
193 p_attribute1 => p_ro_service_code_rec.attribute1,
194 p_attribute2 => p_ro_service_code_rec.attribute2,
195 p_attribute3 => p_ro_service_code_rec.attribute3,
196 p_attribute4 => p_ro_service_code_rec.attribute4,
197 p_attribute5 => p_ro_service_code_rec.attribute5,
198 p_attribute6 => p_ro_service_code_rec.attribute6,
199 p_attribute7 => p_ro_service_code_rec.attribute7,
200 p_attribute8 => p_ro_service_code_rec.attribute8,
201 p_attribute9 => p_ro_service_code_rec.attribute9,
202 p_attribute10 => p_ro_service_code_rec.attribute10,
203 p_attribute11 => p_ro_service_code_rec.attribute11,
204 p_attribute12 => p_ro_service_code_rec.attribute12,
205 p_attribute13 => p_ro_service_code_rec.attribute13,
206 p_attribute14 => p_ro_service_code_rec.attribute14,
207 p_attribute15 => p_ro_service_code_rec.attribute15,
208 p_service_item_id => p_ro_service_code_rec.service_item_id --rfieldma, 4666403
209 );
210
211 EXCEPTION
212 WHEN OTHERS THEN
213 IF ( lc_excep_level >= lc_debug_level) THEN
214 FND_LOG.STRING(lc_excep_level,lc_mod_name,'Others exception in CSD_RO_SERVICE_CODES_PKG.Insert_Row Call :'||SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1,255));
215 END IF;
216 x_return_status := FND_API.G_RET_STS_ERROR;
217 END;
218
219 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
220 RAISE FND_API.G_EXC_ERROR;
221 END IF;
222
223 if (lc_proc_level >= lc_debug_level) then
224 FND_LOG.STRING(lc_proc_level, lc_mod_name,
225 'Returned from Insert_Row table handler');
226 end if;
227
228 -- Api body ends here
229
230 -- Standard check of p_commit.
231 IF FND_API.To_Boolean( p_commit ) THEN
232 COMMIT WORK;
233 END IF;
234
235 -- Standard call to get message count and IF count is get message info.
236 FND_MSG_PUB.Count_And_Get
237 (p_count => x_msg_count,
238 p_data => x_msg_data );
239
240 IF (lc_proc_level >= lc_debug_level) THEN
241 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
242 'Leaving Create_RO_Service_Code');
243 END IF;
244
245 EXCEPTION
246 /*
247 WHEN CSD_RSC_ASSOCIATION_EXISTS THEN
248 ROLLBACK TO Create_RO_Service_Code;
249
250 -- RO service code already exists
251 x_return_status := FND_API.G_RET_STS_ERROR ;
252
253 -- save message in fnd stack
254 if (lc_stat_level >= lc_debug_level) then
255 FND_LOG.STRING(lc_stat_level, lc_mod_name,
256 'Adding message CSD_RSC_ASSOCIATION_EXISTS to FND_MSG stack');
257 end if;
258 FND_MESSAGE.SET_NAME('CSD','CSD_RSC_ASSOCIATION_EXISTS');
259 FND_MESSAGE.SET_TOKEN('SERVICE_CODE_ID',p_ro_service_code_rec.service_code_id);
260 FND_MSG_PUB.Add;
261
262 FND_MSG_PUB.Count_And_Get
263 (p_count => x_msg_count,
264 p_data => x_msg_data );
265
266 -- save message in debug log
267 IF (lc_excep_level >= lc_debug_level) THEN
268 FND_LOG.STRING(lc_excep_level, lc_mod_name,
269 'RO service code already exists');
270 END IF;
271 */
272
273 WHEN FND_API.G_EXC_ERROR THEN
274 ROLLBACK TO Create_RO_Service_Code;
275
276 x_return_status := FND_API.G_RET_STS_ERROR;
277
278 FND_MSG_PUB.Count_And_Get
279 (p_count => x_msg_count,
280 p_data => x_msg_data );
281
282 -- save message in debug log
283 IF (lc_excep_level >= lc_debug_level) THEN
284 FND_LOG.STRING(lc_excep_level, lc_mod_name,
285 'EXC_ERROR['||x_msg_data||']');
286 END IF;
287
288 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
289 ROLLBACK TO Create_RO_Service_Code;
290
291 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
292
293 IF FND_MSG_PUB.Check_Msg_Level
294 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
295 THEN
296 if (lc_stat_level >= lc_debug_level) then
297 FND_LOG.STRING(lc_stat_level, lc_mod_name,
298 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
299 end if;
300 FND_MSG_PUB.Add_Exc_Msg
301 (G_PKG_NAME ,
302 lc_api_name );
303 END IF;
304
305 FND_MSG_PUB.Count_And_Get
306 ( p_count => x_msg_count,
307 p_data => x_msg_data );
308
309 -- save message in debug log
310 IF (lc_excep_level >= lc_debug_level) THEN
311 FND_LOG.STRING(lc_excep_level, lc_mod_name,
312 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
313 END IF;
314
315 WHEN OTHERS THEN
316 ROLLBACK TO Create_RO_Service_Code;
317
318 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
319
320 -- save message in fnd stack
321 IF FND_MSG_PUB.Check_Msg_Level
322 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
323 THEN
324 if (lc_stat_level >= lc_debug_level) then
325 FND_LOG.STRING(lc_stat_level, lc_mod_name,
326 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
327 end if;
328 FND_MSG_PUB.Add_Exc_Msg
329 (G_PKG_NAME ,
330 lc_api_name );
331 END IF;
332
333 FND_MSG_PUB.Count_And_Get
334 (p_count => x_msg_count,
335 p_data => x_msg_data );
336
337 -- save message in debug log
338 IF (lc_excep_level >= lc_debug_level) THEN
339 -- create a seeded message
340 FND_LOG.STRING(lc_excep_level, lc_mod_name,
341 'SQL Message['||sqlerrm||']' );
342 END IF;
343
344 END Create_RO_Service_Code;
345
346 /*--------------------------------------------------*/
347 /* procedure name: Update_RO_Service_Code */
348 /* description : procedure used to update */
349 /* ro service code */
350 /* */
351 /*--------------------------------------------------*/
352 PROCEDURE Update_RO_Service_Code
353 (
354 p_api_version IN NUMBER,
355 p_commit IN VARCHAR2,
356 p_init_msg_list IN VARCHAR2,
357 p_validation_level IN NUMBER,
358 x_return_status OUT NOCOPY VARCHAR2,
359 x_msg_count OUT NOCOPY NUMBER,
360 x_msg_data OUT NOCOPY VARCHAR2,
361 p_ro_service_code_rec IN RO_SERVICE_CODE_REC_TYPE,
362 x_obj_ver_number OUT NOCOPY NUMBER
363 )IS
364
365 -- CONSTANTS --
366 lc_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
367 lc_stat_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
368 lc_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
369 lc_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
370 lc_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
371 lc_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
372 lc_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
373 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_ro_service_codes_pvt.update_ro_service_code';
374 lc_api_name CONSTANT VARCHAR2(30) := 'Update_RO_Service_Code';
375 lc_api_version CONSTANT NUMBER := 1.0;
376
377 -- VARIABLES --
378 l_msg_count NUMBER;
379 l_msg_data VARCHAR2(100);
380 l_msg_index NUMBER;
381 l_dummy VARCHAR2(1) :=null;
382 l_obj_ver_num NUMBER := 1;
383 l_applied_to_est_flag VARCHAR2(1) :=null;
384 l_applied_to_work_flag VARCHAR2(1) :=null;
385
386 -- EXCEPTIONS --
387 CSD_RSC_RO_ID_MISSING EXCEPTION;
388 CSD_RSC_SC_ID_MISSING EXCEPTION;
389 CSD_RSC_SOURCE_TYPE_MISSING EXCEPTION;
390 CSD_RSC_APPL_FLAG_MISSING EXCEPTION;
391 CSD_RSC_APPL_EST_FLAG_MISSING EXCEPTION;
392 CSD_RSC_APPL_WORK_FLAG_MISSING EXCEPTION;
393 CSD_RSC_INVALID_ID EXCEPTION;
394 CSD_RSC_GET_OVN_ERROR EXCEPTION;
395 CSD_RSC_OVN_MISMATCH EXCEPTION;
396
397 BEGIN
398 -- Standard Start of API savepoint
399 SAVEPOINT Update_RO_Service_Code;
400
401 -- Standard call to check for call compatibility.
402 IF NOT FND_API.Compatible_API_Call (lc_api_version,
403 p_api_version,
404 lc_api_name ,
405 G_PKG_NAME )
406 THEN
407 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
408 END IF;
409
410 -- Initialize message list if p_init_msg_list is set to TRUE.
411 IF FND_API.to_Boolean( p_init_msg_list ) THEN
412 FND_MSG_PUB.initialize;
413 END IF;
414
415 IF (lc_proc_level >= lc_debug_level) THEN
416 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
417 'Entered Update_RO_Service_Code');
418 END IF;
419
420 -- log parameters
421 IF (lc_stat_level >= lc_debug_level) THEN
422 csd_gen_utility_pvt.dump_ro_service_code_rec
423 ( p_ro_service_code_rec => p_ro_service_code_rec);
424 END IF;
425
426 -- Initialize API return status to success
427 x_return_status := FND_API.G_RET_STS_SUCCESS;
428
429 -- Api body starts
430
431 -- Check the required parameters
432 if (lc_proc_level >= lc_debug_level) then
433 FND_LOG.STRING(lc_proc_level, lc_mod_name,
434 'Checking required parameters');
435 end if;
436
437 -- Check the required parameter
438 CSD_PROCESS_UTIL.Check_Reqd_Param
439 ( p_param_value => p_ro_service_code_rec.ro_service_code_id,
440 p_param_name => 'RO_SERVICE_CODE_ID',
441 p_api_name => lc_api_name);
442
443
444 -- Check if required parameters are passed in as G_MISS
445 IF (p_ro_service_code_rec.repair_line_id = FND_API.G_MISS_NUM) THEN
446 RAISE CSD_RSC_RO_ID_MISSING;
447 END IF;
448
449 IF (p_ro_service_code_rec.service_code_id = FND_API.G_MISS_NUM) THEN
450 RAISE CSD_RSC_SC_ID_MISSING;
451 END IF;
452
453 IF (p_ro_service_code_rec.source_type_code = FND_API.G_MISS_CHAR) THEN
454 RAISE CSD_RSC_SOURCE_TYPE_MISSING;
455 END IF;
456
457 IF (p_ro_service_code_rec.applicable_flag = FND_API.G_MISS_CHAR) THEN
458 RAISE CSD_RSC_APPL_FLAG_MISSING;
459 END IF;
460
461 IF (p_ro_service_code_rec.applied_to_est_flag = FND_API.G_MISS_CHAR) THEN
462 RAISE CSD_RSC_APPL_EST_FLAG_MISSING;
463 END IF;
464
465 IF (p_ro_service_code_rec.applied_to_work_flag = FND_API.G_MISS_CHAR) THEN
466 RAISE CSD_RSC_APPL_WORK_FLAG_MISSING;
467 END IF;
468
469 -- Validate the ro service code association
470 if (lc_proc_level >= lc_debug_level) then
471 FND_LOG.STRING(lc_proc_level, lc_mod_name,
472 'Validate the id for ro service code');
473 end if;
474
475 -- Validate the ro service code association
476 Begin
477 l_dummy := null;
478
479 select 'X'
480 into l_dummy
481 from csd_ro_service_codes
482 where ro_service_code_id = p_ro_service_code_rec.ro_service_code_id;
483
484 Exception
485
486 WHEN others THEN
487 null;
488
489 End;
490
491 -- If ro service code association does not exist, throw an error
492 IF (l_dummy <> 'X') then
493 RAISE CSD_RSC_INVALID_ID;
494 ELSE
495 if (lc_stat_level >= lc_debug_level) then
496 FND_LOG.STRING(lc_stat_level, lc_mod_name,
497 'RO service code association is valid');
498 end if;
499 END IF;
500
501 -- Get the object version number for ro service code association
502 if (lc_proc_level >= lc_debug_level) then
503 FND_LOG.STRING(lc_proc_level, lc_mod_name,
504 'Get object version number for ro service code association');
505 end if;
506
507 -- Get object version number for ro service code association
508 Begin
509 select object_version_number
510 into l_obj_ver_num
511 from csd_ro_service_codes
512 where ro_service_code_id = p_ro_service_code_rec.ro_service_code_id;
513
514 Exception
515
516 WHEN others THEN
517 l_obj_ver_num := null;
518
519 End;
520
521 -- If no object version number, throw an error
522 IF (l_obj_ver_num is null) then
523 RAISE CSD_RSC_GET_OVN_ERROR;
524 ELSE
525 if (lc_stat_level >= lc_debug_level) then
526 FND_LOG.STRING(lc_stat_level, lc_mod_name,
527 'Retrieved object version number');
528 end if;
529 END IF;
530
531 -- Validate the object version number for ro service code association
532 if (lc_proc_level >= lc_debug_level) then
533 FND_LOG.STRING(lc_proc_level, lc_mod_name,
534 'Validate object version number for ro service code association');
535 end if;
536
537 -- Validate if object version number for ro service code association is same as the one passed in
538 IF NVL(p_ro_service_code_rec.object_version_number,FND_API.G_MISS_NUM) <> l_obj_ver_num THEN
539 RAISE CSD_RSC_OVN_MISMATCH;
540 ELSE
541 if (lc_stat_level >= lc_debug_level) then
542 FND_LOG.STRING(lc_stat_level, lc_mod_name,
543 'Object version number is valid');
544 end if;
545 END IF;
546
547 -- Validate the repair line id if it is passed in
548 if (lc_proc_level >= lc_debug_level) then
549 FND_LOG.STRING(lc_proc_level, lc_mod_name,
550 'Validate repair line id if it is passed in');
551 end if;
552
553 -- Validate the repair line ID
554 IF (p_ro_service_code_rec.repair_line_id IS NOT NULL) THEN
555 IF NOT( CSD_PROCESS_UTIL.Validate_rep_line_id
556 ( p_repair_line_id => p_ro_service_code_rec.repair_line_id )) THEN
557 RAISE FND_API.G_EXC_ERROR;
558 END IF;
559 END IF;
560
561 -- Update row
562 if (lc_proc_level >= lc_debug_level) then
563 FND_LOG.STRING(lc_proc_level, lc_mod_name,
564 'Calling Update_Row table handler');
565 end if;
566
567 BEGIN
568
569 CSD_RO_SERVICE_CODES_PKG.Update_Row
570 (p_ro_service_code_id => p_ro_service_code_rec.ro_service_code_id,
571 p_object_version_number => l_obj_ver_num + 1,
572 p_repair_line_id => p_ro_service_code_rec.repair_line_id,
573 p_service_code_id => p_ro_service_code_rec.service_code_id,
574 p_created_by => FND_GLOBAL.USER_ID,
575 p_creation_date => SYSDATE,
576 p_last_updated_by => FND_GLOBAL.USER_ID,
577 p_last_update_date => SYSDATE,
578 p_last_update_login => FND_GLOBAL.LOGIN_ID,
579 p_source_type_code => p_ro_service_code_rec.source_type_code,
580 p_source_solution_id => p_ro_service_code_rec.source_solution_id,
581 p_applicable_flag => p_ro_service_code_rec.applicable_flag,
582 p_applied_to_est_flag => p_ro_service_code_rec.applied_to_est_flag,
583 p_applied_to_work_flag => p_ro_service_code_rec.applied_to_work_flag,
584 p_attribute_category => p_ro_service_code_rec.attribute_category,
585 p_attribute1 => p_ro_service_code_rec.attribute1,
586 p_attribute2 => p_ro_service_code_rec.attribute2,
587 p_attribute3 => p_ro_service_code_rec.attribute3,
588 p_attribute4 => p_ro_service_code_rec.attribute4,
589 p_attribute5 => p_ro_service_code_rec.attribute5,
590 p_attribute6 => p_ro_service_code_rec.attribute6,
591 p_attribute7 => p_ro_service_code_rec.attribute7,
592 p_attribute8 => p_ro_service_code_rec.attribute8,
593 p_attribute9 => p_ro_service_code_rec.attribute9,
594 p_attribute10 => p_ro_service_code_rec.attribute10,
595 p_attribute11 => p_ro_service_code_rec.attribute11,
596 p_attribute12 => p_ro_service_code_rec.attribute12,
597 p_attribute13 => p_ro_service_code_rec.attribute13,
598 p_attribute14 => p_ro_service_code_rec.attribute14,
599 p_attribute15 => p_ro_service_code_rec.attribute15,
600 p_service_item_id => p_ro_service_code_rec.service_item_id -- rfieldma, 4666403
601 );
602
603 x_obj_ver_number := l_obj_ver_num + 1;
604
605 EXCEPTION
606 WHEN OTHERS THEN
607 IF ( lc_excep_level >= lc_debug_level) THEN
608 FND_LOG.STRING(lc_excep_level,lc_mod_name,'Others exception in CSD_RO_SERVICE_CODES_PKG.Update_Row Call :'||SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1,255));
609 END IF;
610 x_return_status := FND_API.G_RET_STS_ERROR;
611 END;
612
613 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
614 RAISE FND_API.G_EXC_ERROR;
615 END IF;
616
617 if (lc_proc_level >= lc_debug_level) then
618 FND_LOG.STRING(lc_proc_level, lc_mod_name,
619 'Returned from Update_Row table handler');
620 end if;
621
622 -- Api body ends here
623
624 -- Standard check of p_commit.
625 IF FND_API.To_Boolean( p_commit ) THEN
626 COMMIT WORK;
627 END IF;
628
629 -- Standard call to get message count and IF count is get message info.
630 FND_MSG_PUB.Count_And_Get
631 (p_count => x_msg_count,
632 p_data => x_msg_data );
633
634 IF (lc_proc_level >= lc_debug_level) THEN
635 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
636 'Leaving Update_RO_Service_Code');
637 END IF;
638
639 EXCEPTION
640 WHEN CSD_RSC_RO_ID_MISSING THEN
641 ROLLBACK TO Update_RO_Service_Code;
642 -- Repair line id is missing
643 x_return_status := FND_API.G_RET_STS_ERROR ;
644
645 -- save message in fnd stack
646 if (lc_stat_level >= lc_debug_level) then
647 FND_LOG.STRING(lc_stat_level, lc_mod_name,
648 'Adding message CSD_RSC_RO_ID_MISSING to FND_MSG stack');
649 end if;
650 FND_MESSAGE.SET_NAME('CSD','CSD_API_MISSING_PARAM');
651 FND_MESSAGE.SET_TOKEN('API_NAME',lc_api_name);
652 FND_MESSAGE.SET_TOKEN('MISSING_PARAM','REPAIR_LINE_ID');
653 FND_MSG_PUB.Add;
654
655 FND_MSG_PUB.Count_And_Get
656 (p_count => x_msg_count,
657 p_data => x_msg_data );
658
659 -- save message in debug log
660 IF (lc_excep_level >= lc_debug_level) THEN
661 FND_LOG.STRING(lc_excep_level, lc_mod_name,
662 'Repair line id is missing');
663 END IF;
664
665 WHEN CSD_RSC_SC_ID_MISSING THEN
666 ROLLBACK TO Update_RO_Service_Code;
667 -- Service code id is missing
668 x_return_status := FND_API.G_RET_STS_ERROR ;
669
670 -- save message in fnd stack
671 if (lc_stat_level >= lc_debug_level) then
672 FND_LOG.STRING(lc_stat_level, lc_mod_name,
673 'Adding message CSD_RSC_SC_ID_MISSING to FND_MSG stack');
674 end if;
675 FND_MESSAGE.SET_NAME('CSD','CSD_API_MISSING_PARAM');
676 FND_MESSAGE.SET_TOKEN('API_NAME',lc_api_name);
677 FND_MESSAGE.SET_TOKEN('MISSING_PARAM','SERVICE_CODE_ID');
678 FND_MSG_PUB.Add;
679
680 FND_MSG_PUB.Count_And_Get
681 (p_count => x_msg_count,
682 p_data => x_msg_data );
683
684 -- save message in debug log
685 IF (lc_excep_level >= lc_debug_level) THEN
686 FND_LOG.STRING(lc_excep_level, lc_mod_name,
687 'Service code id is missing');
688 END IF;
689
690 WHEN CSD_RSC_SOURCE_TYPE_MISSING THEN
691 ROLLBACK TO Update_RO_Service_Code;
692
693 -- RO service code source type is missing
694 x_return_status := FND_API.G_RET_STS_ERROR ;
695
696 -- save message in fnd stack
697 if (lc_stat_level >= lc_debug_level) then
698 FND_LOG.STRING(lc_stat_level, lc_mod_name,
699 'Adding message CSD_RSC_SOURCE_TYPE_MISSING to FND_MSG stack');
700 end if;
701 FND_MESSAGE.SET_NAME('CSD','CSD_API_MISSING_PARAM');
702 FND_MESSAGE.SET_TOKEN('API_NAME',lc_api_name);
703 FND_MESSAGE.SET_TOKEN('MISSING_PARAM','SOURCE_TYPE_CODE');
704 FND_MSG_PUB.Add;
705
706 FND_MSG_PUB.Count_And_Get
707 (p_count => x_msg_count,
708 p_data => x_msg_data );
709
710 -- save message in debug log
711 IF (lc_excep_level >= lc_debug_level) THEN
712 FND_LOG.STRING(lc_excep_level, lc_mod_name,
713 'RO service code source type is missing');
714 END IF;
715
716 WHEN CSD_RSC_APPL_FLAG_MISSING THEN
717 ROLLBACK TO Update_RO_Service_Code;
718
719 -- RO service code applicable flag is missing
720 x_return_status := FND_API.G_RET_STS_ERROR ;
721
722 -- save message in fnd stack
723 if (lc_stat_level >= lc_debug_level) then
724 FND_LOG.STRING(lc_stat_level, lc_mod_name,
725 'Adding message CSD_RSC_APPL_FLAG_MISSING to FND_MSG stack');
726 end if;
727 FND_MESSAGE.SET_NAME('CSD','CSD_API_MISSING_PARAM');
728 FND_MESSAGE.SET_TOKEN('API_NAME',lc_api_name);
729 FND_MESSAGE.SET_TOKEN('MISSING_PARAM','APPLICABLE_FLAG');
730 FND_MSG_PUB.Add;
731
732 FND_MSG_PUB.Count_And_Get
733 (p_count => x_msg_count,
734 p_data => x_msg_data );
735
736 -- save message in debug log
737 IF (lc_excep_level >= lc_debug_level) THEN
738 FND_LOG.STRING(lc_excep_level, lc_mod_name,
739 'RO service code applicable flag is missing');
740 END IF;
741
742 WHEN CSD_RSC_APPL_EST_FLAG_MISSING THEN
743 ROLLBACK TO Update_RO_Service_Code;
744
745 -- RO service code applied to estimate flag is missing
746 x_return_status := FND_API.G_RET_STS_ERROR ;
747
748 -- save message in fnd stack
749 if (lc_stat_level >= lc_debug_level) then
750 FND_LOG.STRING(lc_stat_level, lc_mod_name,
751 'Adding message CSD_RSC_APP_EST_FLAG_MISSING to FND_MSG stack');
752 end if;
753 FND_MESSAGE.SET_NAME('CSD','CSD_API_MISSING_PARAM');
754 FND_MESSAGE.SET_TOKEN('API_NAME',lc_api_name);
755 FND_MESSAGE.SET_TOKEN('MISSING_PARAM','APPLIED_TO_EST_FLAG');
756 FND_MSG_PUB.Add;
757
758 FND_MSG_PUB.Count_And_Get
759 (p_count => x_msg_count,
760 p_data => x_msg_data );
761
762 -- save message in debug log
763 IF (lc_excep_level >= lc_debug_level) THEN
764 FND_LOG.STRING(lc_excep_level, lc_mod_name,
765 'RO service code applied to estimate flag is missing');
766 END IF;
767
768 WHEN CSD_RSC_APPL_WORK_FLAG_MISSING THEN
769 ROLLBACK TO Update_RO_Service_Code;
770
771 -- RO service code applied to work flag is missing
772 x_return_status := FND_API.G_RET_STS_ERROR ;
773
774 -- save message in fnd stack
775 if (lc_stat_level >= lc_debug_level) then
776 FND_LOG.STRING(lc_stat_level, lc_mod_name,
777 'Adding message CSD_RSC_APP_WORK_FLAG_MISSING to FND_MSG stack');
778 end if;
779 FND_MESSAGE.SET_NAME('CSD','CSD_API_MISSING_PARAM');
780 FND_MESSAGE.SET_TOKEN('API_NAME',lc_api_name);
781 FND_MESSAGE.SET_TOKEN('MISSING_PARAM','APPLIED_TO_WORK_FLAG');
782 FND_MSG_PUB.Add;
783
784 FND_MSG_PUB.Count_And_Get
785 (p_count => x_msg_count,
786 p_data => x_msg_data );
787
788 -- save message in debug log
789 IF (lc_excep_level >= lc_debug_level) THEN
790 FND_LOG.STRING(lc_excep_level, lc_mod_name,
791 'RO service code applied to work flag is missing');
792 END IF;
793
794 WHEN CSD_RSC_INVALID_ID THEN
795 ROLLBACK TO Update_RO_Service_Code;
796
797 -- RO service code id is invalid
798 x_return_status := FND_API.G_RET_STS_ERROR ;
799
800 -- save message in fnd stack
801 if (lc_stat_level >= lc_debug_level) then
802 FND_LOG.STRING(lc_stat_level, lc_mod_name,
803 'Adding message CSD_RSC_INVALID_ID to FND_MSG stack');
804 end if;
805 FND_MESSAGE.SET_NAME('CSD','CSD_RSC_INVALID_ID');
806 FND_MESSAGE.SET_TOKEN('RO_SERVICE_CODE_ID',p_ro_service_code_rec.ro_service_code_id);
807 FND_MSG_PUB.Add;
808
809 FND_MSG_PUB.Count_And_Get
810 (p_count => x_msg_count,
811 p_data => x_msg_data );
812
813 -- save message in debug log
814 IF (lc_excep_level >= lc_debug_level) THEN
815 FND_LOG.STRING(lc_excep_level, lc_mod_name,
816 'RO service code id is invalid');
817 END IF;
818
819 WHEN CSD_RSC_GET_OVN_ERROR THEN
820 ROLLBACK TO Update_RO_Service_Code;
821
822 -- RO service code get object version number error
823 x_return_status := FND_API.G_RET_STS_ERROR ;
824
825 -- save message in fnd stack
826 if (lc_stat_level >= lc_debug_level) then
827 FND_LOG.STRING(lc_stat_level, lc_mod_name,
828 'Adding message CSD_RSC_GET_OVN_ERROR to FND_MSG stack');
829 end if;
830 FND_MESSAGE.SET_NAME('CSD','CSD_RSC_GET_OVN_ERROR');
831 FND_MESSAGE.SET_TOKEN('RO_SERVICE_CODE_ID',p_ro_service_code_rec.ro_service_code_id);
832 FND_MSG_PUB.Add;
833
834 FND_MSG_PUB.Count_And_Get
835 (p_count => x_msg_count,
836 p_data => x_msg_data );
837
838 -- save message in debug log
839 IF (lc_excep_level >= lc_debug_level) THEN
840 FND_LOG.STRING(lc_excep_level, lc_mod_name,
841 'RO service code get object version number error');
842 END IF;
843
844 WHEN CSD_RSC_OVN_MISMATCH THEN
845 ROLLBACK TO Update_RO_Service_Code;
846
847 -- RO service code object version number mismatch
848 x_return_status := FND_API.G_RET_STS_ERROR ;
849
850 -- save message in fnd stack
851 if (lc_stat_level >= lc_debug_level) then
852 FND_LOG.STRING(lc_stat_level, lc_mod_name,
853 'Adding message CSD_RSC_OVN_MISMATCH to FND_MSG stack');
854 end if;
855 FND_MESSAGE.SET_NAME('CSD','CSD_RSC_OVN_MISMATCH');
856 FND_MESSAGE.SET_TOKEN('RO_SERVICE_CODE_ID',p_ro_service_code_rec.ro_service_code_id);
857 FND_MSG_PUB.Add;
858
859 FND_MSG_PUB.Count_And_Get
860 (p_count => x_msg_count,
861 p_data => x_msg_data );
862
863 -- save message in debug log
864 IF (lc_excep_level >= lc_debug_level) THEN
865 FND_LOG.STRING(lc_excep_level, lc_mod_name,
866 'RO service code object version number mismatch');
867 END IF;
868
869 WHEN FND_API.G_EXC_ERROR THEN
870 ROLLBACK TO Update_RO_Service_Code;
871
872 x_return_status := FND_API.G_RET_STS_ERROR;
873
874 FND_MSG_PUB.Count_And_Get
875 (p_count => x_msg_count,
876 p_data => x_msg_data );
877
878 -- save message in debug log
879 IF (lc_excep_level >= lc_debug_level) THEN
880 FND_LOG.STRING(lc_excep_level, lc_mod_name,
881 'EXC_ERROR['||x_msg_data||']');
882 END IF;
883
884 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
885 ROLLBACK TO Update_RO_Service_Code;
886
887 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
888
889 IF FND_MSG_PUB.Check_Msg_Level
890 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
891 THEN
892 if (lc_stat_level >= lc_debug_level) then
893 FND_LOG.STRING(lc_stat_level, lc_mod_name,
894 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
895 end if;
896 FND_MSG_PUB.Add_Exc_Msg
897 (G_PKG_NAME ,
898 lc_api_name );
899 END IF;
900
901 FND_MSG_PUB.Count_And_Get
902 ( p_count => x_msg_count,
903 p_data => x_msg_data );
904
905 -- save message in debug log
906 IF (lc_excep_level >= lc_debug_level) THEN
907 FND_LOG.STRING(lc_excep_level, lc_mod_name,
908 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
909 END IF;
910
911 WHEN OTHERS THEN
912 ROLLBACK TO Update_RO_Service_Code;
913
914 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
915
916 -- save message in fnd stack
917 IF FND_MSG_PUB.Check_Msg_Level
918 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
919 THEN
920 if (lc_stat_level >= lc_debug_level) then
921 FND_LOG.STRING(lc_stat_level, lc_mod_name,
922 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
923 end if;
924 FND_MSG_PUB.Add_Exc_Msg
925 (G_PKG_NAME ,
926 lc_api_name );
927 END IF;
928
929 FND_MSG_PUB.Count_And_Get
930 (p_count => x_msg_count,
931 p_data => x_msg_data );
932
933 -- save message in debug log
934 IF (lc_excep_level >= lc_debug_level) THEN
935 -- create a seeded message
936 FND_LOG.STRING(lc_excep_level, lc_mod_name,
937 'SQL Message['||sqlerrm||']' );
938 END IF;
939
940 END Update_RO_Service_Code;
941
942
943 /*--------------------------------------------------*/
944 /* procedure name: Delete_RO_Service_Code */
945 /* description : procedure used to delete */
946 /* ro service code */
947 /* */
948 /*--------------------------------------------------*/
949 PROCEDURE Delete_RO_Service_Code
950 (
951 p_api_version IN NUMBER,
952 p_commit IN VARCHAR2,
953 p_init_msg_list IN VARCHAR2,
954 p_validation_level IN NUMBER,
955 x_return_status OUT NOCOPY VARCHAR2,
956 x_msg_count OUT NOCOPY NUMBER,
957 x_msg_data OUT NOCOPY VARCHAR2,
958 p_ro_service_code_id IN NUMBER
959 ) IS
960
961 -- CONSTANTS --
962 lc_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
963 lc_stat_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
964 lc_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
965 lc_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
966 lc_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
967 lc_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
968 lc_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
969 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_ro_service_codes_pvt.delete_ro_service_code';
970 lc_api_name CONSTANT VARCHAR2(30) := 'Delete_RO_Service_Code';
971 lc_api_version CONSTANT NUMBER := 1.0;
972
973 -- VARIABLES --
974 l_msg_count NUMBER;
975 l_msg_data VARCHAR2(100);
976 l_msg_index NUMBER;
977
978 BEGIN
979 -- Standard Start of API savepoint
980 SAVEPOINT Delete_RO_Service_Code;
981
982 -- Standard call to check for call compatibility.
983 IF NOT FND_API.Compatible_API_Call (lc_api_version,
984 p_api_version,
985 lc_api_name ,
986 G_PKG_NAME )
987 THEN
988 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
989 END IF;
990
991 -- Initialize message list if p_init_msg_list is set to TRUE.
992 IF FND_API.to_Boolean( p_init_msg_list ) THEN
993 FND_MSG_PUB.initialize;
994 END IF;
995
996 IF (lc_proc_level >= lc_debug_level) THEN
997 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
998 'Entered Delete_RO_Service_Code');
999 END IF;
1000
1001 -- Initialize API return status to success
1002 x_return_status := FND_API.G_RET_STS_SUCCESS;
1003
1004 -- Api body starts
1005
1006 -- Check the required parameters
1007 if (lc_proc_level >= lc_debug_level) then
1008 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1009 'Checking required parameter');
1010 end if;
1011
1012 -- Check the required parameter
1013 CSD_PROCESS_UTIL.Check_Reqd_Param
1014 ( p_param_value => p_ro_service_code_id,
1015 p_param_name => 'RO_SERVICE_CODE_ID',
1016 p_api_name => lc_api_name);
1017
1018 -- Delete row
1019 if (lc_proc_level >= lc_debug_level) then
1020 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1021 'Calling Delete_Row table handler');
1022 end if;
1023
1024 BEGIN
1025
1026 CSD_RO_SERVICE_CODES_PKG.Delete_Row
1027 (p_ro_service_code_id => p_ro_service_code_id);
1028
1029 EXCEPTION
1030 WHEN OTHERS THEN
1031 IF ( lc_excep_level >= lc_debug_level) THEN
1032 FND_LOG.STRING(lc_excep_level,lc_mod_name,'Others exception in CSD_RO_SERVICE_CODES_PKG.Delete_Row Call :'||SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1,255));
1033 END IF;
1034 x_return_status := FND_API.G_RET_STS_ERROR;
1035 END;
1036
1037 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1038 RAISE FND_API.G_EXC_ERROR;
1039 END IF;
1040
1041 if (lc_proc_level >= lc_debug_level) then
1042 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1043 'Returned from Delete_Row table handler');
1044 end if;
1045
1046 -- Api body ends here
1047
1048 -- Standard check of p_commit.
1049 IF FND_API.To_Boolean( p_commit ) THEN
1050 COMMIT WORK;
1051 END IF;
1052
1053 -- Standard call to get message count and IF count is get message info.
1054 FND_MSG_PUB.Count_And_Get
1055 (p_count => x_msg_count,
1056 p_data => x_msg_data );
1057
1058 IF (lc_proc_level >= lc_debug_level) THEN
1059 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
1060 'Leaving Delete_RO_Service_Code');
1061 END IF;
1062
1063 EXCEPTION
1064 WHEN FND_API.G_EXC_ERROR THEN
1065 ROLLBACK TO Delete_RO_Service_Code;
1066
1067 x_return_status := FND_API.G_RET_STS_ERROR;
1068
1069 FND_MSG_PUB.Count_And_Get
1070 (p_count => x_msg_count,
1071 p_data => x_msg_data );
1072
1073 -- save message in debug log
1074 IF (lc_excep_level >= lc_debug_level) THEN
1075 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1076 'EXC_ERROR['||x_msg_data||']');
1077 END IF;
1078
1079 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1080 ROLLBACK TO Delete_RO_Service_Code;
1081
1082 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1083
1084 IF FND_MSG_PUB.Check_Msg_Level
1085 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1086 THEN
1087 if (lc_stat_level >= lc_debug_level) then
1088 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1089 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
1090 end if;
1091 FND_MSG_PUB.Add_Exc_Msg
1092 (G_PKG_NAME ,
1093 lc_api_name );
1094 END IF;
1095
1096 FND_MSG_PUB.Count_And_Get
1097 ( p_count => x_msg_count,
1098 p_data => x_msg_data );
1099
1100 -- save message in debug log
1101 IF (lc_excep_level >= lc_debug_level) THEN
1102 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1103 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
1104 END IF;
1105
1106 WHEN OTHERS THEN
1107 ROLLBACK TO Delete_RO_Service_Code;
1108
1109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1110
1111 -- save message in fnd stack
1112 IF FND_MSG_PUB.Check_Msg_Level
1113 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1114 THEN
1115 if (lc_stat_level >= lc_debug_level) then
1116 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1117 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
1118 end if;
1119 FND_MSG_PUB.Add_Exc_Msg
1120 (G_PKG_NAME ,
1121 lc_api_name );
1122 END IF;
1123
1124 FND_MSG_PUB.Count_And_Get
1125 (p_count => x_msg_count,
1126 p_data => x_msg_data );
1127
1128 -- save message in debug log
1129 IF (lc_excep_level >= lc_debug_level) THEN
1130 -- create a seeded message
1131 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1132 'SQL Message['||sqlerrm||']' );
1133 END IF;
1134
1135 END Delete_RO_Service_Code;
1136
1137 /*--------------------------------------------------*/
1138 /* procedure name: Lock_RO_Service_Code */
1139 /* description : procedure used to lock */
1140 /* ro service code */
1141 /* */
1142 /*--------------------------------------------------*/
1143 PROCEDURE Lock_RO_Service_Code
1144 (
1145 p_api_version IN NUMBER,
1146 p_commit IN VARCHAR2,
1147 p_init_msg_list IN VARCHAR2,
1148 p_validation_level IN NUMBER,
1149 x_return_status OUT NOCOPY VARCHAR2,
1150 x_msg_count OUT NOCOPY NUMBER,
1151 x_msg_data OUT NOCOPY VARCHAR2,
1152 p_ro_service_code_rec IN RO_SERVICE_CODE_REC_TYPE
1153 )IS
1154
1155 -- CONSTANTS --
1156 lc_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1157 lc_stat_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
1158 lc_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
1159 lc_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
1160 lc_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
1161 lc_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
1162 lc_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
1163 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_ro_service_codes_pvt.lock_ro_service_code';
1164 lc_api_name CONSTANT VARCHAR2(30) := 'Lock_RO_Service_Code';
1165 lc_api_version CONSTANT NUMBER := 1.0;
1166
1167 -- VARIABLES --
1168 l_msg_count NUMBER;
1169 l_msg_data VARCHAR2(100);
1170 l_msg_index NUMBER;
1171
1172 BEGIN
1173 -- Standard Start of API savepoint
1174 SAVEPOINT Lock_RO_Service_Code;
1175
1176 -- Standard call to check for call compatibility.
1177 IF NOT FND_API.Compatible_API_Call (lc_api_version,
1178 p_api_version,
1179 lc_api_name ,
1180 G_PKG_NAME )
1181 THEN
1182 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1183 END IF;
1184
1185 -- Initialize message list if p_init_msg_list is set to TRUE.
1186 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1187 FND_MSG_PUB.initialize;
1188 END IF;
1189
1190 IF (lc_proc_level >= lc_debug_level) THEN
1191 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
1192 'Entered Lock_RO_Service_Code');
1193 END IF;
1194
1195 -- Initialize API return status to success
1196 x_return_status := FND_API.G_RET_STS_SUCCESS;
1197
1198 -- Api body starts
1199
1200 -- Check the required parameters
1201 if (lc_proc_level >= lc_debug_level) then
1202 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1203 'Checking required parameters');
1204 end if;
1205
1206 -- Check the required parameter
1207 CSD_PROCESS_UTIL.Check_Reqd_Param
1208 ( p_param_value => p_ro_service_code_rec.ro_service_code_id,
1209 p_param_name => 'RO_SERVICE_CODE_ID',
1210 p_api_name => lc_api_name);
1211
1212 -- Check the required parameter
1213 CSD_PROCESS_UTIL.Check_Reqd_Param
1214 ( p_param_value => p_ro_service_code_rec.object_version_number,
1215 p_param_name => 'OBJECT_VERSION_NUMBER',
1216 p_api_name => lc_api_name);
1217
1218 -- Lock row
1219 if (lc_proc_level >= lc_debug_level) then
1220 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1221 'Calling Lock_Row table handler');
1222 end if;
1223
1224 BEGIN
1225
1226 CSD_RO_SERVICE_CODES_PKG.Lock_Row
1227 (p_ro_service_code_id => p_ro_service_code_rec.ro_service_code_id,
1228 p_object_version_number => p_ro_service_code_rec.object_version_number
1229
1230 --commented out the rest of the record
1231 /*,
1232 p_repair_line_id => p_ro_service_code_rec.repair_line_id,
1233 p_service_code_id => p_ro_service_code_rec.service_code_id,
1234 p_created_by => null,
1235 p_creation_date => null,
1236 p_last_updated_by => null,
1237 p_last_update_date => null,
1238 p_last_update_login => null,
1239 p_source_type_code => p_ro_service_code_rec.source_type_code,
1240 p_source_solution_id => p_ro_service_code_rec.source_solution_id,
1241 p_applicable_flag => p_ro_service_code_rec.applicable_flag,
1242 p_applied_to_est_flag => null,
1243 p_applied_to_work_flag => null,
1244 p_attribute_category => p_ro_service_code_rec.attribute_category,
1245 p_attribute1 => p_ro_service_code_rec.attribute1,
1246 p_attribute2 => p_ro_service_code_rec.attribute2,
1247 p_attribute3 => p_ro_service_code_rec.attribute3,
1248 p_attribute4 => p_ro_service_code_rec.attribute4,
1249 p_attribute5 => p_ro_service_code_rec.attribute5,
1250 p_attribute6 => p_ro_service_code_rec.attribute6,
1251 p_attribute7 => p_ro_service_code_rec.attribute7,
1252 p_attribute8 => p_ro_service_code_rec.attribute8,
1253 p_attribute9 => p_ro_service_code_rec.attribute9,
1254 p_attribute10 => p_ro_service_code_rec.attribute10,
1255 p_attribute11 => p_ro_service_code_rec.attribute11,
1256 p_attribute12 => p_ro_service_code_rec.attribute12,
1257 p_attribute13 => p_ro_service_code_rec.attribute13,
1258 p_attribute14 => p_ro_service_code_rec.attribute14,
1259 p_attribute15 => p_ro_service_code_rec.attribute15
1260 */
1261 --
1262 );
1263
1264 EXCEPTION
1265 WHEN OTHERS THEN
1266 IF ( lc_excep_level >= lc_debug_level) THEN
1267 FND_LOG.STRING(lc_excep_level,lc_mod_name,'Others exception in CSD_RO_SERVICE_CODES_PKG.Lock_Row Call :'||SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1,255));
1268 END IF;
1269 x_return_status := FND_API.G_RET_STS_ERROR;
1270 END;
1271
1272 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1273 RAISE FND_API.G_EXC_ERROR;
1274 END IF;
1275
1276 if (lc_proc_level >= lc_debug_level) then
1277 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1278 'Returned from Lock_Row table handler');
1279 end if;
1280
1281 -- Api body ends here
1282
1283 -- Standard check of p_commit.
1284 IF FND_API.To_Boolean( p_commit ) THEN
1285 COMMIT WORK;
1286 END IF;
1287
1288 -- Standard call to get message count and IF count is get message info.
1289 FND_MSG_PUB.Count_And_Get
1290 (p_count => x_msg_count,
1291 p_data => x_msg_data );
1292
1293 IF (lc_proc_level >= lc_debug_level) THEN
1294 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
1295 'Leaving Lock_RO_Service_Code');
1296 END IF;
1297
1298 EXCEPTION
1299 WHEN FND_API.G_EXC_ERROR THEN
1300 ROLLBACK TO Lock_RO_Service_Code;
1301
1302 x_return_status := FND_API.G_RET_STS_ERROR;
1303
1304 FND_MSG_PUB.Count_And_Get
1305 (p_count => x_msg_count,
1306 p_data => x_msg_data );
1307
1308 -- save message in debug log
1309 IF (lc_excep_level >= lc_debug_level) THEN
1310 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1311 'EXC_ERROR['||x_msg_data||']');
1312 END IF;
1313
1314 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1315 ROLLBACK TO Lock_RO_Service_Code;
1316
1317 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1318
1319 IF FND_MSG_PUB.Check_Msg_Level
1320 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1321 THEN
1322 if (lc_stat_level >= lc_debug_level) then
1323 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1324 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
1325 end if;
1326 FND_MSG_PUB.Add_Exc_Msg
1327 (G_PKG_NAME ,
1328 lc_api_name );
1329 END IF;
1330
1331 FND_MSG_PUB.Count_And_Get
1332 ( p_count => x_msg_count,
1333 p_data => x_msg_data );
1334
1335 -- save message in debug log
1336 IF (lc_excep_level >= lc_debug_level) THEN
1337 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1338 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
1339 END IF;
1340
1341 WHEN OTHERS THEN
1342 ROLLBACK TO Lock_RO_Service_Code;
1343
1344 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1345
1346 -- save message in fnd stack
1347 IF FND_MSG_PUB.Check_Msg_Level
1348 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1349 THEN
1350 if (lc_stat_level >= lc_debug_level) then
1351 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1352 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
1353 end if;
1354 FND_MSG_PUB.Add_Exc_Msg
1355 (G_PKG_NAME ,
1356 lc_api_name );
1357 END IF;
1358
1359 FND_MSG_PUB.Count_And_Get
1360 (p_count => x_msg_count,
1361 p_data => x_msg_data );
1362
1363 -- save message in debug log
1364 IF (lc_excep_level >= lc_debug_level) THEN
1365 -- create a seeded message
1366 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1367 'SQL Message['||sqlerrm||']' );
1368 END IF;
1369
1370 END Lock_RO_Service_Code;
1371
1372 End CSD_RO_SERVICE_CODES_PVT;