1 PACKAGE BODY CSD_SC_WORK_ENTITIES_PVT as
2 /* $Header: csdvscwb.pls 115.6 2004/02/07 02:36:21 gilam noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_SC_WORK_ENTITIES_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdvscwb.pls';
6
7 g_bom_type CONSTANT VARCHAR2(30) := 'BOM';
8 g_task_type CONSTANT VARCHAR2(30) := 'TASK';
9
10 /*--------------------------------------------------*/
11 /* procedure name: Create_SC_Work_Entity */
12 /* description : procedure used to create */
13 /* sc work entity */
14 /* */
15 /*--------------------------------------------------*/
16 PROCEDURE Create_SC_Work_Entity
17 (
18 p_api_version IN NUMBER,
19 p_commit IN VARCHAR2,
20 p_init_msg_list IN VARCHAR2,
21 p_validation_level IN NUMBER,
22 x_return_status OUT NOCOPY VARCHAR2,
23 x_msg_count OUT NOCOPY NUMBER,
24 x_msg_data OUT NOCOPY VARCHAR2,
25 p_sc_work_entity_rec IN SC_WORK_ENTITY_REC_TYPE,
26 x_sc_work_entity_id OUT NOCOPY NUMBER
27 ) IS
28
29 -- CONSTANTS --
30 lc_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
31 lc_stat_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
32 lc_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
33 lc_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
34 lc_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
35 lc_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
36 lc_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
37 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_sc_work_entities_pvt.create_sc_work_entity';
38 lc_api_name CONSTANT VARCHAR2(30) := 'Create_SC_Work_Entity';
39 lc_api_version CONSTANT NUMBER := 1.0;
40
41 -- VARIABLES --
42 l_msg_count NUMBER;
43 l_msg_data VARCHAR2(100);
44 l_msg_index NUMBER;
45 l_dummy VARCHAR2(1) := null;
46 l_obj_ver_num NUMBER := 1;
47
48 -- EXCEPTIONS --
49 CSD_SCW_ENTITY_EXISTS EXCEPTION;
50 CSD_SCW_INVALID_ORGANIZATION EXCEPTION;
51 CSD_SCW_INVALID_BILL_SEQUENCE EXCEPTION;
52 CSD_SCW_INVALID_ROUTE_SEQUENCE EXCEPTION;
53 CSD_SCW_INVALID_TASK_TEMP_GRP EXCEPTION;
54
55 BEGIN
56 -- Standard Start of API savepoint
57 SAVEPOINT Create_SC_Work_Entity;
58
59 -- Standard call to check for call compatibility.
60 IF NOT FND_API.Compatible_API_Call (lc_api_version,
61 p_api_version,
62 lc_api_name ,
63 G_PKG_NAME )
64 THEN
65 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
66 END IF;
67
68 -- Initialize message list if p_init_msg_list is set to TRUE.
69 IF FND_API.to_Boolean( p_init_msg_list ) THEN
70 FND_MSG_PUB.initialize;
71 END IF;
72
73 IF (lc_proc_level >= lc_debug_level) THEN
74 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
75 'Entered Create_SC_Work_Entity');
76 END IF;
77
78 -- log parameters
79 IF (lc_stat_level >= lc_debug_level) THEN
80 csd_gen_utility_pvt.dump_sc_work_entity_rec
81 ( p_sc_work_entity_rec => p_sc_work_entity_rec);
82 END IF;
83
84 -- Initialize API return status to success
85 x_return_status := FND_API.G_RET_STS_SUCCESS;
86
87 -- Api body starts
88
89 -- Check the required parameters
90 if (lc_proc_level >= lc_debug_level) then
91 FND_LOG.STRING(lc_proc_level, lc_mod_name,
92 'Checking required parameters');
93 end if;
94
95 -- Check the required parameter
96 CSD_PROCESS_UTIL.Check_Reqd_Param
97 ( p_param_value => p_sc_work_entity_rec.service_code_id,
98 p_param_name => 'SERVICE_CODE_ID',
99 p_api_name => lc_api_name);
100
101 -- Check the required parameter
102 CSD_PROCESS_UTIL.Check_Reqd_Param
103 ( p_param_value => p_sc_work_entity_rec.work_entity_type_code,
104 p_param_name => 'WORK_ENTITY_TYPE_CODE',
105 p_api_name => lc_api_name);
106
107 -- Validate the work entity for service code
108 if (lc_proc_level >= lc_debug_level) then
109 FND_LOG.STRING(lc_proc_level, lc_mod_name,
110 'Validate if the service code work entity already exists');
111 end if;
112
113 -- Validate the work entity for service code
114 Begin
115 l_dummy := null;
116
117 select 'X'
118 into l_dummy
119 from csd_sc_work_entities
120 where service_code_id = p_sc_work_entity_rec.service_code_id
121 and (work_entity_type_code = p_sc_work_entity_rec.work_entity_type_code
122 and nvl(work_entity_id1, -999) = nvl(p_sc_work_entity_rec.work_entity_id1, -999)
123 and nvl(work_entity_id2, -999) = nvl(p_sc_work_entity_rec.work_entity_id2, -999)
124 and nvl(work_entity_id3, -999) = nvl(p_sc_work_entity_rec.work_entity_id3, -999));
125
126 Exception
127
128 WHEN no_data_found THEN
129 null;
130
131 WHEN others THEN
132 l_dummy := 'X';
133
134 End;
135
136 -- If entity exists, throw an error
137 IF (l_dummy = 'X') then
138 RAISE CSD_SCW_ENTITY_EXISTS;
139 ELSE
140 if (lc_stat_level >= lc_debug_level) then
141 FND_LOG.STRING(lc_stat_level, lc_mod_name,
142 'Service code work entity does not exist');
143 end if;
144 END IF;
145
146 -- Validate the organization id
147 if (lc_proc_level >= lc_debug_level) then
148 FND_LOG.STRING(lc_proc_level, lc_mod_name,
149 'Validate the organization id');
150 end if;
151
152 -- Validate the organization id
153 Begin
154
155 l_dummy := null;
156
157 If (p_sc_work_entity_rec.work_entity_type_code = g_bom_type
158 and p_sc_work_entity_rec.work_entity_id3 is not null) then
159 select 'X'
160 into l_dummy
161 from org_organization_definitions
162 where organization_id = p_sc_work_entity_rec.work_entity_id3;
163 End if;
164
165 Exception
166
167 WHEN others THEN
168 null;
169
170 End;
171
172 -- If organization does not exist, throw an error
173 IF (l_dummy <> 'X') then
174 RAISE CSD_SCW_INVALID_ORGANIZATION;
175 ELSE
176 if (lc_stat_level >= lc_debug_level) then
177 FND_LOG.STRING(lc_stat_level, lc_mod_name,
178 'Org for service code work entity is valid');
179 end if;
180 END IF;
181
182 -- Validate the bill sequence id
183 if (lc_proc_level >= lc_debug_level) then
184 FND_LOG.STRING(lc_proc_level, lc_mod_name,
185 'Validate the bill sequence id');
186 end if;
187
188 -- Validate the bill sequence id
189 Begin
190
191 l_dummy := null;
192
193 If (p_sc_work_entity_rec.work_entity_type_code = g_bom_type
194 and p_sc_work_entity_rec.work_entity_id1 is not null) then
195 select 'X'
196 into l_dummy
197 from bom_bill_of_materials
198 where bill_sequence_id = p_sc_work_entity_rec.work_entity_id1;
199 End if;
200
201 Exception
202
203 WHEN others THEN
204 null;
205
206 End;
207
208 -- If bill sequence does not exist, throw an error
209 IF (l_dummy <> 'X') then
210 RAISE CSD_SCW_INVALID_BILL_SEQUENCE;
211 ELSE
212 if (lc_stat_level >= lc_debug_level) then
213 FND_LOG.STRING(lc_stat_level, lc_mod_name,
214 'Bill reference for service code work entity is valid');
215 end if;
216 END IF;
217
218 -- Validate the routing sequence id
219 if (lc_proc_level >= lc_debug_level) then
220 FND_LOG.STRING(lc_proc_level, lc_mod_name,
221 'Validate the routing sequence id');
222 end if;
223
224 -- Validate the routing sequence id
225 Begin
226
227 l_dummy := null;
228
229 If (p_sc_work_entity_rec.work_entity_type_code = g_bom_type
230 and p_sc_work_entity_rec.work_entity_id2 is not null) then
231 select 'X'
232 into l_dummy
233 from bom_operational_routings
234 where routing_sequence_id = p_sc_work_entity_rec.work_entity_id2;
235 End if;
236
237 Exception
238
239 WHEN others THEN
240 null;
241
242 End;
243
244 -- If bill sequence does not exist, throw an error
245 IF (l_dummy <> 'X') then
246 RAISE CSD_SCW_INVALID_ROUTE_SEQUENCE;
247 ELSE
248 if (lc_stat_level >= lc_debug_level) then
249 FND_LOG.STRING(lc_stat_level, lc_mod_name,
250 'Routing reference for service code work entity is valid');
251 end if;
252 END IF;
253
254 -- Validate the task template group id
255 if (lc_proc_level >= lc_debug_level) then
256 FND_LOG.STRING(lc_proc_level, lc_mod_name,
257 'Validate the task template group id');
258 end if;
259
260 -- Validate the task template group id
261 Begin
262
263 l_dummy := null;
264
265 If (p_sc_work_entity_rec.work_entity_type_code = g_task_type
266 and p_sc_work_entity_rec.work_entity_id1 is not null) then
267 select 'X'
268 into l_dummy
269 from jtf_task_temp_groups_vl
270 where task_template_group_id = p_sc_work_entity_rec.work_entity_id1;
271 End if;
272
273 Exception
274
275 WHEN others THEN
276 null;
277
278 End;
279
280 -- If task template group does not exist, throw an error
281 IF (l_dummy <> 'X') then
282 RAISE CSD_SCW_INVALID_TASK_TEMP_GRP;
283 ELSE
284 if (lc_stat_level >= lc_debug_level) then
285 FND_LOG.STRING(lc_stat_level, lc_mod_name,
286 'Task template group for service code work entity is valid');
287 end if;
288 END IF;
289
290 -- Insert row
291 if (lc_proc_level >= lc_debug_level) then
292 FND_LOG.STRING(lc_proc_level, lc_mod_name,
293 'Calling Insert_Row table handler');
294 end if;
295
296 BEGIN
297
298 -- Insert the new work entity
299 CSD_SC_WORK_ENTITIES_PKG.Insert_Row
300 (px_sc_work_entity_id => x_sc_work_entity_id,
301 p_object_version_number => l_obj_ver_num,
302 p_service_code_id => p_sc_work_entity_rec.service_code_id,
303 p_work_entity_id1 => p_sc_work_entity_rec.work_entity_id1,
304 p_created_by => FND_GLOBAL.USER_ID,
305 p_creation_date => SYSDATE,
306 p_last_updated_by => FND_GLOBAL.USER_ID,
307 p_last_update_date => SYSDATE,
308 p_last_update_login => FND_GLOBAL.LOGIN_ID,
309 p_work_entity_type_code => p_sc_work_entity_rec.work_entity_type_code,
310 p_work_entity_id2 => p_sc_work_entity_rec.work_entity_id2,
311 p_work_entity_id3 => p_sc_work_entity_rec.work_entity_id3,
312 p_attribute_category => p_sc_work_entity_rec.attribute_category,
313 p_attribute1 => p_sc_work_entity_rec.attribute1,
314 p_attribute2 => p_sc_work_entity_rec.attribute2,
315 p_attribute3 => p_sc_work_entity_rec.attribute3,
316 p_attribute4 => p_sc_work_entity_rec.attribute4,
317 p_attribute5 => p_sc_work_entity_rec.attribute5,
318 p_attribute6 => p_sc_work_entity_rec.attribute6,
319 p_attribute7 => p_sc_work_entity_rec.attribute7,
320 p_attribute8 => p_sc_work_entity_rec.attribute8,
321 p_attribute9 => p_sc_work_entity_rec.attribute9,
322 p_attribute10 => p_sc_work_entity_rec.attribute10,
323 p_attribute11 => p_sc_work_entity_rec.attribute11,
324 p_attribute12 => p_sc_work_entity_rec.attribute12,
325 p_attribute13 => p_sc_work_entity_rec.attribute13,
326 p_attribute14 => p_sc_work_entity_rec.attribute14,
327 p_attribute15 => p_sc_work_entity_rec.attribute15
328 );
329
330 EXCEPTION
331 WHEN OTHERS THEN
332 IF ( lc_excep_level >= lc_debug_level) THEN
333 FND_LOG.STRING(lc_excep_level,lc_mod_name,'Others exception in CSD_SC_WORK_ENTITIES_PKG.Insert_Row Call :'||SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1,255));
334 END IF;
335 x_return_status := FND_API.G_RET_STS_ERROR;
336 END;
337
338 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
339 RAISE FND_API.G_EXC_ERROR;
340 END IF;
341
342 if (lc_proc_level >= lc_debug_level) then
343 FND_LOG.STRING(lc_proc_level, lc_mod_name,
344 'Returned from Insert_Row table handler');
345 end if;
346
347 -- Api body ends here
348
349 -- Standard check of p_commit.
350 IF FND_API.To_Boolean( p_commit ) THEN
351 COMMIT WORK;
352 END IF;
353
354 -- Standard call to get message count and IF count is get message info.
355 FND_MSG_PUB.Count_And_Get
356 (p_count => x_msg_count,
357 p_data => x_msg_data );
358
359 IF (lc_proc_level >= lc_debug_level) THEN
360 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
361 'Leaving Create_SC_Work_Entity');
362 END IF;
363
364 EXCEPTION
365 WHEN CSD_SCW_ENTITY_EXISTS THEN
366 ROLLBACK TO Create_SC_Work_Entity;
367
368 -- Service code work entity already exists
369 x_return_status := FND_API.G_RET_STS_ERROR ;
370
371 -- save message in fnd stack
372 if (lc_stat_level >= lc_debug_level) then
373 FND_LOG.STRING(lc_stat_level, lc_mod_name,
374 'Adding message CSD_SCW_ENTITY_EXISTS to FND_MSG stack');
375 end if;
376 FND_MESSAGE.SET_NAME('CSD','CSD_SCW_ENTITY_EXISTS');
377 FND_MESSAGE.SET_TOKEN('SERVICE_CODE_ID',p_sc_work_entity_rec.service_code_id);
378 FND_MSG_PUB.Add;
379
380 FND_MSG_PUB.Count_And_Get
381 (p_count => x_msg_count,
382 p_data => x_msg_data );
383
384 -- save message in debug log
385 IF (lc_excep_level >= lc_debug_level) THEN
386 FND_LOG.STRING(lc_excep_level, lc_mod_name,
387 'Service code work entity already exists');
388 END IF;
389
390 WHEN CSD_SCW_INVALID_ORGANIZATION THEN
391 ROLLBACK TO Create_SC_Work_Entity;
392
393 -- Service code work entity organization is invalid
394 x_return_status := FND_API.G_RET_STS_ERROR ;
395
396 -- save message in fnd stack
397 if (lc_stat_level >= lc_debug_level) then
398 FND_LOG.STRING(lc_stat_level, lc_mod_name,
399 'Adding message CSD_SCW_INVALID_ORGANIZATION to FND_MSG stack');
400 end if;
401 FND_MESSAGE.SET_NAME('CSD','CSD_SCW_INVALID_ORGANIZATION');
402 FND_MESSAGE.SET_TOKEN('ORGANIZATION_ID',p_sc_work_entity_rec.work_entity_id3);
403 FND_MSG_PUB.Add;
404
405 FND_MSG_PUB.Count_And_Get
406 (p_count => x_msg_count,
407 p_data => x_msg_data );
408
409 -- save message in debug log
410 IF (lc_excep_level >= lc_debug_level) THEN
411 FND_LOG.STRING(lc_excep_level, lc_mod_name,
412 'Service code work entity organization is invalid');
413 END IF;
414
415 WHEN CSD_SCW_INVALID_BILL_SEQUENCE THEN
416 ROLLBACK TO Create_SC_Work_Entity;
417
418 -- Service code work entity bill reference is invalid
419 x_return_status := FND_API.G_RET_STS_ERROR ;
420
421 -- save message in fnd stack
422 if (lc_stat_level >= lc_debug_level) then
423 FND_LOG.STRING(lc_stat_level, lc_mod_name,
424 'Adding message CSD_SCW_INVALID_BILL_SEQUENCE to FND_MSG stack');
425 end if;
426 FND_MESSAGE.SET_NAME('CSD','CSD_SCW_INVALID_BILL_SEQUENCE');
427 FND_MESSAGE.SET_TOKEN('BILL_SEQUENCE_ID',p_sc_work_entity_rec.work_entity_id1);
428 FND_MSG_PUB.Add;
429
430 FND_MSG_PUB.Count_And_Get
431 (p_count => x_msg_count,
432 p_data => x_msg_data );
433
434 -- save message in debug log
435 IF (lc_excep_level >= lc_debug_level) THEN
436 FND_LOG.STRING(lc_excep_level, lc_mod_name,
437 'Service code work entity bill reference is invalid');
438 END IF;
439
440 WHEN CSD_SCW_INVALID_ROUTE_SEQUENCE THEN
441 ROLLBACK TO Create_SC_Work_Entity;
442
443 -- Service code work entity routing reference is invalid
444 x_return_status := FND_API.G_RET_STS_ERROR ;
445
446 -- save message in fnd stack
447 if (lc_stat_level >= lc_debug_level) then
448 FND_LOG.STRING(lc_stat_level, lc_mod_name,
449 'Adding message CSD_SCW_INVALID_ROUTE_SEQUENCE to FND_MSG stack');
450 end if;
451 FND_MESSAGE.SET_NAME('CSD','CSD_SCW_INVALID_ROUTE_SEQUENCE');
452 FND_MESSAGE.SET_TOKEN('ROUTING_SEQUENCE_ID',p_sc_work_entity_rec.work_entity_id2);
453 FND_MSG_PUB.Add;
454
455 FND_MSG_PUB.Count_And_Get
456 (p_count => x_msg_count,
457 p_data => x_msg_data );
458
459 -- save message in debug log
460 IF (lc_excep_level >= lc_debug_level) THEN
461 FND_LOG.STRING(lc_excep_level, lc_mod_name,
462 'Service code work entity routing reference is invalid');
463 END IF;
464
465 WHEN CSD_SCW_INVALID_TASK_TEMP_GRP THEN
466 ROLLBACK TO Create_SC_Work_Entity;
467
468 -- Service code work entity task template group is invalid
469 x_return_status := FND_API.G_RET_STS_ERROR ;
470
471 -- save message in fnd stack
472 if (lc_stat_level >= lc_debug_level) then
473 FND_LOG.STRING(lc_stat_level, lc_mod_name,
474 'Adding message CSD_SCW_INVALID_TASK_TEMP_GRP to FND_MSG stack');
475 end if;
476 FND_MESSAGE.SET_NAME('CSD','CSD_SCW_INVALID_TASK_TEMP_GRP');
477 FND_MESSAGE.SET_TOKEN('TASK_TEMP_GROUP_ID',p_sc_work_entity_rec.work_entity_id1);
478 FND_MSG_PUB.Add;
479
480 FND_MSG_PUB.Count_And_Get
481 (p_count => x_msg_count,
482 p_data => x_msg_data );
483
484 -- save message in debug log
485 IF (lc_excep_level >= lc_debug_level) THEN
486 FND_LOG.STRING(lc_excep_level, lc_mod_name,
487 'Service code work entity task template group is invalid');
488 END IF;
489
490 WHEN FND_API.G_EXC_ERROR THEN
491 ROLLBACK TO Create_SC_Work_Entity;
492
493 x_return_status := FND_API.G_RET_STS_ERROR;
494
495 FND_MSG_PUB.Count_And_Get
496 (p_count => x_msg_count,
497 p_data => x_msg_data );
498
499 -- save message in debug log
500 IF (lc_excep_level >= lc_debug_level) THEN
501 FND_LOG.STRING(lc_excep_level, lc_mod_name,
502 'EXC_ERROR['||x_msg_data||']');
503 END IF;
504
505 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
506 ROLLBACK TO Create_SC_Work_Entity;
507
508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
509
510 IF FND_MSG_PUB.Check_Msg_Level
511 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
512 THEN
513 if (lc_stat_level >= lc_debug_level) then
514 FND_LOG.STRING(lc_stat_level, lc_mod_name,
515 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
516 end if;
517 FND_MSG_PUB.Add_Exc_Msg
518 (G_PKG_NAME ,
519 lc_api_name );
520 END IF;
521
522 FND_MSG_PUB.Count_And_Get
523 ( p_count => x_msg_count,
524 p_data => x_msg_data );
525
526 -- save message in debug log
527 IF (lc_excep_level >= lc_debug_level) THEN
528 FND_LOG.STRING(lc_excep_level, lc_mod_name,
529 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
530 END IF;
531
532 WHEN OTHERS THEN
533 ROLLBACK TO Create_SC_Work_Entity;
534
535 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
536
537 -- save message in fnd stack
538 IF FND_MSG_PUB.Check_Msg_Level
539 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
540 THEN
541 if (lc_stat_level >= lc_debug_level) then
542 FND_LOG.STRING(lc_stat_level, lc_mod_name,
543 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
544 end if;
545 FND_MSG_PUB.Add_Exc_Msg
546 (G_PKG_NAME ,
547 lc_api_name );
548 END IF;
549
550 FND_MSG_PUB.Count_And_Get
551 (p_count => x_msg_count,
552 p_data => x_msg_data );
553
554 -- save message in debug log
555 IF (lc_excep_level >= lc_debug_level) THEN
556 -- create a seeded message
557 FND_LOG.STRING(lc_excep_level, lc_mod_name,
558 'SQL Message['||sqlerrm||']' );
559 END IF;
560
561 END Create_SC_Work_Entity;
562
563
564 /*--------------------------------------------------*/
565 /* procedure name: Update_SC_Work_Entity */
566 /* description : procedure used to update */
567 /* sc work entity */
568 /* */
569 /*--------------------------------------------------*/
570 PROCEDURE Update_SC_Work_Entity
571 (
572 p_api_version IN NUMBER,
573 p_commit IN VARCHAR2,
574 p_init_msg_list IN VARCHAR2,
575 p_validation_level IN NUMBER,
576 x_return_status OUT NOCOPY VARCHAR2,
577 x_msg_count OUT NOCOPY NUMBER,
578 x_msg_data OUT NOCOPY VARCHAR2,
579 p_sc_work_entity_rec IN SC_WORK_ENTITY_REC_TYPE,
580 x_obj_ver_number OUT NOCOPY NUMBER
581 ) IS
582
583 -- CONSTANTS --
584 lc_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
585 lc_stat_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
586 lc_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
587 lc_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
588 lc_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
589 lc_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
590 lc_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
591 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_sc_work_entities_pvt.update_sc_work_entity';
592 lc_api_name CONSTANT VARCHAR2(30) := 'Update_SC_Work_Entity';
593 lc_api_version CONSTANT NUMBER := 1.0;
594
595 -- VARIABLES --
596 l_msg_count NUMBER;
597 l_msg_data VARCHAR2(100);
598 l_msg_index NUMBER;
599 l_dummy VARCHAR2(1) := null;
600 l_obj_ver_num NUMBER := 1;
601
602 -- EXCEPTIONS --
603 CSD_SCW_SC_ID_MISSING EXCEPTION;
604 CSD_SCW_ENTITY_TYPE_MISSING EXCEPTION;
605 CSD_SCW_INVALID_ID EXCEPTION;
606 CSD_SCW_GET_OVN_ERROR EXCEPTION;
607 CSD_SCW_OVN_MISMATCH EXCEPTION;
608 CSD_SCW_ENTITY_EXISTS EXCEPTION;
609 CSD_SCW_INVALID_ORGANIZATION EXCEPTION;
610 CSD_SCW_INVALID_BILL_SEQUENCE EXCEPTION;
611 CSD_SCW_INVALID_ROUTE_SEQUENCE EXCEPTION;
612 CSD_SCW_INVALID_TASK_TEMP_GRP EXCEPTION;
613
614 BEGIN
615
616 -- Standard Start of API savepoint
617 SAVEPOINT Update_SC_Work_Entity;
618
619 -- Standard call to check for call compatibility.
620 -- Standard call to check for call compatibility.
621 IF NOT FND_API.Compatible_API_Call (lc_api_version,
622 p_api_version,
623 lc_api_name ,
624 G_PKG_NAME )
625 THEN
626 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
627 END IF;
628
629 -- Initialize message list if p_init_msg_list is set to TRUE.
630 IF FND_API.to_Boolean( p_init_msg_list ) THEN
631 FND_MSG_PUB.initialize;
632 END IF;
633
634 IF (lc_proc_level >= lc_debug_level) THEN
635 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
636 'Entered Update_SC_Work_Entity');
637 END IF;
638
639 -- log parameters
640 IF (lc_stat_level >= lc_debug_level) THEN
641 csd_gen_utility_pvt.dump_sc_work_entity_rec
642 ( p_sc_work_entity_rec => p_sc_work_entity_rec);
643 END IF;
644
645 -- Initialize API return status to success
646 x_return_status := FND_API.G_RET_STS_SUCCESS;
647
648 -- Api body starts
649
650 -- Check the required parameters
651 if (lc_proc_level >= lc_debug_level) then
652 FND_LOG.STRING(lc_proc_level, lc_mod_name,
653 'Checking required parameters');
654 end if;
655
656 -- Check the required parameter
657 CSD_PROCESS_UTIL.Check_Reqd_Param
658 ( p_param_value => p_sc_work_entity_rec.sc_work_entity_id,
659 p_param_name => 'SC_WORK_ENTITY_ID',
660 p_api_name => lc_api_name);
661
662 -- Check if required parameter is passed in as G_MISS
663 if (lc_proc_level >= lc_debug_level) then
664 FND_LOG.STRING(lc_proc_level, lc_mod_name,
665 'Checking if required parameters are passed in as G_MISS');
666 end if;
667
668 -- Check if required parameter is passed in as G_MISS
669 IF (p_sc_work_entity_rec.service_code_id = FND_API.G_MISS_NUM) THEN
670 RAISE CSD_SCW_SC_ID_MISSING;
671 END IF;
672
673 -- Check if required parameter is passed in as G_MISS
674 IF (p_sc_work_entity_rec.work_entity_type_code = FND_API.G_MISS_CHAR) THEN
675 RAISE CSD_SCW_ENTITY_TYPE_MISSING;
676 END IF;
677
678 -- Validate the work entity for service code
679 if (lc_proc_level >= lc_debug_level) then
680 FND_LOG.STRING(lc_proc_level, lc_mod_name,
681 'Validate the id for service code work entity');
682 end if;
683
684 -- Validate the id for service code work entity
685 Begin
686 l_dummy := null;
687
688 select 'X'
689 into l_dummy
690 from csd_sc_work_entities
691 where sc_work_entity_id = p_sc_work_entity_rec.sc_work_entity_id;
692
693 Exception
694
695 WHEN others THEN
696 null;
697
698 End;
699
700 -- If service code work entity does not exist, throw an error
701 IF (l_dummy <> 'X') then
702 RAISE CSD_SCW_INVALID_ID;
703 ELSE
704 if (lc_stat_level >= lc_debug_level) then
705 FND_LOG.STRING(lc_stat_level, lc_mod_name,
706 'Service code work entity is valid');
707 end if;
708 END IF;
709
710 -- Get the object version number for service code
711 if (lc_proc_level >= lc_debug_level) then
712 FND_LOG.STRING(lc_proc_level, lc_mod_name,
713 'Get object version number for service code work entity');
714 end if;
715
716 -- Get object version number for service code work_entity
717 Begin
718 select object_version_number
719 into l_obj_ver_num
720 from csd_sc_work_entities
721 where sc_work_entity_id = p_sc_work_entity_rec.sc_work_entity_id;
722
723 Exception
724
725 WHEN others THEN
726 l_obj_ver_num := null;
727
728 End;
729
730 -- If no object version number, throw an error
731 IF (l_obj_ver_num is null) then
732 RAISE CSD_SCW_GET_OVN_ERROR;
733 ELSE
734 if (lc_stat_level >= lc_debug_level) then
735 FND_LOG.STRING(lc_stat_level, lc_mod_name,
736 'Retrieved object version number');
737 end if;
738 END IF;
739
740 -- Validate the object version number for service code work entity
741 if (lc_proc_level >= lc_debug_level) then
742 FND_LOG.STRING(lc_proc_level, lc_mod_name,
743 'Validate object version number for service code work entity');
744 end if;
745
746 -- Validate if object version number for service code work_entity is same as the one passed in
747 IF NVL(p_sc_work_entity_rec.object_version_number,FND_API.G_MISS_NUM) <> l_obj_ver_num THEN
748 RAISE CSD_SCW_OVN_MISMATCH;
749 ELSE
750 if (lc_stat_level >= lc_debug_level) then
751 FND_LOG.STRING(lc_stat_level, lc_mod_name,
752 'Object version number is valid');
753 end if;
754 END IF;
755
756 -- Validate the work entity for service code
757 if (lc_proc_level >= lc_debug_level) then
758 FND_LOG.STRING(lc_proc_level, lc_mod_name,
759 'Validate if the service code work entity already exists');
760 end if;
761
762 -- Validate the work entity for service code
763 Begin
764
765 l_dummy := null;
766
767 select 'X'
768 into l_dummy
769 from csd_sc_work_entities
770 where sc_work_entity_id <> p_sc_work_entity_rec.sc_work_entity_id
771 and service_code_id = p_sc_work_entity_rec.service_code_id
772 and (work_entity_type_code = p_sc_work_entity_rec.work_entity_type_code
773 and nvl(work_entity_id1, -999) = nvl(p_sc_work_entity_rec.work_entity_id1, -999)
774 and nvl(work_entity_id2, -999) = nvl(p_sc_work_entity_rec.work_entity_id2, -999)
775 and nvl(work_entity_id3, -999) = nvl(p_sc_work_entity_rec.work_entity_id3, -999));
776
777 Exception
778
779 WHEN no_data_found THEN
780 null;
781
782 WHEN others THEN
783 l_dummy := 'X';
784
785 End;
786
787 -- If entity exists, throw an error
788 IF (l_dummy = 'X') then
789 RAISE CSD_SCW_ENTITY_EXISTS;
790 ELSE
791 if (lc_stat_level >= lc_debug_level) then
792 FND_LOG.STRING(lc_stat_level, lc_mod_name,
793 'Service code work entity does not exist');
794 end if;
795 END IF;
796
797 -- Validate the organization id
798 if (lc_proc_level >= lc_debug_level) then
799 FND_LOG.STRING(lc_proc_level, lc_mod_name,
800 'Validate the organization id');
801 end if;
802
803 -- Validate the organization id
804 Begin
805
806 l_dummy := null;
807
808 If (p_sc_work_entity_rec.work_entity_type_code = g_bom_type
809 and p_sc_work_entity_rec.work_entity_id3 is not null) then
810 select 'X'
811 into l_dummy
812 from org_organization_definitions
813 where organization_id = p_sc_work_entity_rec.work_entity_id3;
814 End if;
815
816 Exception
817
818 WHEN others THEN
819 null;
820
821 End;
822
823 -- If organization does not exist, throw an error
824 IF (l_dummy <> 'X') then
825 RAISE CSD_SCW_INVALID_ORGANIZATION;
826 ELSE
827 if (lc_stat_level >= lc_debug_level) then
828 FND_LOG.STRING(lc_stat_level, lc_mod_name,
829 'Org for service code work entity is valid');
830 end if;
831 END IF;
832
833 -- Validate the bill sequence id
834 if (lc_proc_level >= lc_debug_level) then
835 FND_LOG.STRING(lc_proc_level, lc_mod_name,
836 'Validate the bill sequence id');
837 end if;
838
839 -- Validate the bill sequence id
840 Begin
841
842 l_dummy := null;
843
844 If (p_sc_work_entity_rec.work_entity_type_code = g_bom_type
845 and p_sc_work_entity_rec.work_entity_id1 is not null) then
846 select 'X'
847 into l_dummy
848 from bom_bill_of_materials
849 where bill_sequence_id = p_sc_work_entity_rec.work_entity_id1;
850 End if;
851
852 Exception
853
854 WHEN others THEN
855 null;
856
857 End;
858
859 -- If bill sequence does not exist, throw an error
860 IF (l_dummy <> 'X') then
861 RAISE CSD_SCW_INVALID_BILL_SEQUENCE;
862 ELSE
863 if (lc_stat_level >= lc_debug_level) then
864 FND_LOG.STRING(lc_stat_level, lc_mod_name,
865 'Bill reference for service code work entity is valid');
866 end if;
867 END IF;
868
869 -- Validate the routing sequence id
870 if (lc_proc_level >= lc_debug_level) then
871 FND_LOG.STRING(lc_proc_level, lc_mod_name,
872 'Validate the routing sequence id');
873 end if;
874
875 -- Validate the routing sequence id
876 Begin
877
878 l_dummy := null;
879
880 If (p_sc_work_entity_rec.work_entity_type_code = g_bom_type
881 and p_sc_work_entity_rec.work_entity_id2 is not null) then
882 select 'X'
883 into l_dummy
884 from bom_operational_routings
885 where routing_sequence_id = p_sc_work_entity_rec.work_entity_id2;
886 End if;
887
888 Exception
889
890 WHEN others THEN
891 null;
892
893 End;
894
895 -- If bill sequence does not exist, throw an error
896 IF (l_dummy <> 'X') then
897 RAISE CSD_SCW_INVALID_ROUTE_SEQUENCE;
898 ELSE
899 if (lc_stat_level >= lc_debug_level) then
900 FND_LOG.STRING(lc_stat_level, lc_mod_name,
901 'Routing reference for service code work entity is valid');
902 end if;
903 END IF;
904
905 -- Validate the task template group id
906 if (lc_proc_level >= lc_debug_level) then
907 FND_LOG.STRING(lc_proc_level, lc_mod_name,
908 'Validate the task template group id');
909 end if;
910
911 -- Validate the task template group id
912 Begin
913
914 l_dummy := null;
915
916 If (p_sc_work_entity_rec.work_entity_type_code = g_task_type
917 and p_sc_work_entity_rec.work_entity_id1 is not null) then
918 select 'X'
919 into l_dummy
920 from jtf_task_temp_groups_vl
921 where task_template_group_id = p_sc_work_entity_rec.work_entity_id1;
922 End if;
923
924 Exception
925
926 WHEN others THEN
927 null;
928
929 End;
930
931 -- If task template group does not exist, throw an error
932 IF (l_dummy <> 'X') then
933 RAISE CSD_SCW_INVALID_TASK_TEMP_GRP;
934 ELSE
935 if (lc_stat_level >= lc_debug_level) then
936 FND_LOG.STRING(lc_stat_level, lc_mod_name,
937 'Task template group for service code work entity is valid');
938 end if;
939 END IF;
940
941 -- Update row
942 if (lc_proc_level >= lc_debug_level) then
943 FND_LOG.STRING(lc_proc_level, lc_mod_name,
944 'Calling Update_Row table handler');
945 end if;
946
947 BEGIN
948
949 -- Update the sc work entity
950 CSD_SC_WORK_ENTITIES_PKG.Update_Row
951 (p_sc_work_entity_id => p_sc_work_entity_rec.sc_work_entity_id,
952 p_object_version_number => l_obj_ver_num + 1,
953 p_service_code_id => p_sc_work_entity_rec.service_code_id,
954 p_work_entity_id1 => p_sc_work_entity_rec.work_entity_id1,
955 p_created_by => FND_GLOBAL.USER_ID,
956 p_creation_date => SYSDATE,
957 p_last_updated_by => FND_GLOBAL.USER_ID,
958 p_last_update_date => SYSDATE,
959 p_last_update_login => FND_GLOBAL.LOGIN_ID,
960 p_work_entity_type_code => p_sc_work_entity_rec.work_entity_type_code,
961 p_work_entity_id2 => p_sc_work_entity_rec.work_entity_id2,
962 p_work_entity_id3 => p_sc_work_entity_rec.work_entity_id3,
963 p_attribute_category => p_sc_work_entity_rec.attribute_category,
964 p_attribute1 => p_sc_work_entity_rec.attribute1,
965 p_attribute2 => p_sc_work_entity_rec.attribute2,
966 p_attribute3 => p_sc_work_entity_rec.attribute3,
967 p_attribute4 => p_sc_work_entity_rec.attribute4,
968 p_attribute5 => p_sc_work_entity_rec.attribute5,
969 p_attribute6 => p_sc_work_entity_rec.attribute6,
970 p_attribute7 => p_sc_work_entity_rec.attribute7,
971 p_attribute8 => p_sc_work_entity_rec.attribute8,
972 p_attribute9 => p_sc_work_entity_rec.attribute9,
973 p_attribute10 => p_sc_work_entity_rec.attribute10,
974 p_attribute11 => p_sc_work_entity_rec.attribute11,
975 p_attribute12 => p_sc_work_entity_rec.attribute12,
976 p_attribute13 => p_sc_work_entity_rec.attribute13,
977 p_attribute14 => p_sc_work_entity_rec.attribute14,
978 p_attribute15 => p_sc_work_entity_rec.attribute15
979 );
980
981 x_obj_ver_number := l_obj_ver_num + 1;
982
983 EXCEPTION
984 WHEN OTHERS THEN
985 IF ( lc_excep_level >= lc_debug_level) THEN
986 FND_LOG.STRING(lc_excep_level,lc_mod_name,'Others exception in CSD_SC_WORK_ENTITIES_PKG.Update_Row Call :'||SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1,255));
987 END IF;
988 x_return_status := FND_API.G_RET_STS_ERROR;
989 END;
990
991 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
992 RAISE FND_API.G_EXC_ERROR;
993 END IF;
994
995 if (lc_proc_level >= lc_debug_level) then
996 FND_LOG.STRING(lc_proc_level, lc_mod_name,
997 'Returned from Update_Row table handler');
998 end if;
999
1000 -- Api body ends here
1001
1002 -- Standard check of p_commit.
1003 IF FND_API.To_Boolean( p_commit ) THEN
1004 COMMIT WORK;
1005 END IF;
1006
1007 -- Standard call to get message count and IF count is get message info.
1008 FND_MSG_PUB.Count_And_Get
1009 (p_count => x_msg_count,
1010 p_data => x_msg_data );
1011
1012 IF (lc_proc_level >= lc_debug_level) THEN
1013 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
1014 'Leaving Update_SC_Work_Entity');
1015 END IF;
1016
1017 EXCEPTION
1018
1019 WHEN CSD_SCW_SC_ID_MISSING THEN
1020 ROLLBACK TO Update_SC_Work_Entity;
1021 -- Service code id is missing
1022 x_return_status := FND_API.G_RET_STS_ERROR ;
1023
1024 -- save message in fnd stack
1025 if (lc_stat_level >= lc_debug_level) then
1026 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1027 'Adding message CSD_SCW_SC_ID_MISSING to FND_MSG stack');
1028 end if;
1029 FND_MESSAGE.SET_NAME('CSD','CSD_API_MISSING_PARAM');
1030 FND_MESSAGE.SET_TOKEN('API_NAME',lc_api_name);
1031 FND_MESSAGE.SET_TOKEN('MISSING_PARAM','SERVICE_CODE_ID');
1032 FND_MSG_PUB.Add;
1033
1034 FND_MSG_PUB.Count_And_Get
1035 (p_count => x_msg_count,
1036 p_data => x_msg_data );
1037
1038 -- save message in debug log
1039 IF (lc_excep_level >= lc_debug_level) THEN
1040 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1041 'Service code id is missing');
1042 END IF;
1043
1044 WHEN CSD_SCW_ENTITY_TYPE_MISSING THEN
1045 ROLLBACK TO Update_SC_Work_Entity;
1046
1047 -- Service code work entity type is missing
1048 x_return_status := FND_API.G_RET_STS_ERROR ;
1049
1050 -- save message in fnd stack
1051 if (lc_stat_level >= lc_debug_level) then
1052 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1053 'Adding message CSD_SCW_ENTITY_TYPE_MISSING to FND_MSG stack');
1054 end if;
1055 FND_MESSAGE.SET_NAME('CSD','CSD_API_MISSING_PARAM');
1056 FND_MESSAGE.SET_TOKEN('API_NAME',lc_api_name);
1057 FND_MESSAGE.SET_TOKEN('MISSING_PARAM','WORK_ENTITY_TYPE_CODE');
1058 FND_MSG_PUB.Add;
1059
1060 FND_MSG_PUB.Count_And_Get
1061 (p_count => x_msg_count,
1062 p_data => x_msg_data );
1063
1064 -- save message in debug log
1065 IF (lc_excep_level >= lc_debug_level) THEN
1066 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1067 'Service code work entity type is missing');
1068 END IF;
1069
1070 WHEN CSD_SCW_INVALID_ID THEN
1071 ROLLBACK TO Update_SC_Work_Entity;
1072
1073 -- Service code work entity id is invalid
1074 x_return_status := FND_API.G_RET_STS_ERROR ;
1075
1076 -- save message in fnd stack
1077 if (lc_stat_level >= lc_debug_level) then
1078 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1079 'Adding message CSD_SCW_INVALID_ID to FND_MSG stack');
1080 end if;
1081 FND_MESSAGE.SET_NAME('CSD','CSD_SCW_INVALID_ID');
1082 FND_MESSAGE.SET_TOKEN('SC_WORK_ENTITY_ID',p_sc_work_entity_rec.sc_work_entity_id);
1083 FND_MSG_PUB.Add;
1084
1085 FND_MSG_PUB.Count_And_Get
1086 (p_count => x_msg_count,
1087 p_data => x_msg_data );
1088
1089 -- save message in debug log
1090 IF (lc_excep_level >= lc_debug_level) THEN
1091 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1092 'Service code work entity id is invalid');
1093 END IF;
1094
1095 WHEN CSD_SCW_GET_OVN_ERROR THEN
1096 ROLLBACK TO Update_SC_Work_Entity;
1097
1098 -- Service code work entity get object version number error
1099 x_return_status := FND_API.G_RET_STS_ERROR ;
1100
1101 -- save message in fnd stack
1102 if (lc_stat_level >= lc_debug_level) then
1103 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1104 'Adding message CSD_SCW_GET_OVN_ERROR to FND_MSG stack');
1105 end if;
1106 FND_MESSAGE.SET_NAME('CSD','CSD_SCW_GET_OVN_ERROR');
1107 FND_MESSAGE.SET_TOKEN('SC_WORK_ENTITY_ID',p_sc_work_entity_rec.sc_work_entity_id);
1108 FND_MSG_PUB.Add;
1109
1110 FND_MSG_PUB.Count_And_Get
1111 (p_count => x_msg_count,
1112 p_data => x_msg_data );
1113
1114 -- save message in debug log
1115 IF (lc_excep_level >= lc_debug_level) THEN
1116 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1117 'Service code work entity get object version number error');
1118 END IF;
1119
1120 WHEN CSD_SCW_OVN_MISMATCH THEN
1121 ROLLBACK TO Update_SC_Work_Entity;
1122
1123 -- Service code work entity object version number mismatch
1124 x_return_status := FND_API.G_RET_STS_ERROR ;
1125
1126 -- save message in fnd stack
1127 if (lc_stat_level >= lc_debug_level) then
1128 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1129 'Adding message CSD_SCW_OVN_MISMATCH to FND_MSG stack');
1130 end if;
1131 FND_MESSAGE.SET_NAME('CSD','CSD_SCW_OVN_MISMATCH');
1132 FND_MESSAGE.SET_TOKEN('SC_WORK_ENTITY_ID',p_sc_work_entity_rec.sc_work_entity_id);
1133 FND_MSG_PUB.Add;
1134
1135 FND_MSG_PUB.Count_And_Get
1136 (p_count => x_msg_count,
1137 p_data => x_msg_data );
1138
1139 -- save message in debug log
1140 IF (lc_excep_level >= lc_debug_level) THEN
1141 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1142 'Service code work entity object version number mismatch');
1143 END IF;
1144
1145 WHEN CSD_SCW_ENTITY_EXISTS THEN
1146 ROLLBACK TO Update_SC_Work_Entity;
1147
1148 -- Service code work entity already exists
1149 x_return_status := FND_API.G_RET_STS_ERROR ;
1150
1151 -- save message in fnd stack
1152 if (lc_stat_level >= lc_debug_level) then
1153 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1154 'Adding message CSD_SCW_ENTITY_EXISTS to FND_MSG stack');
1155 end if;
1156 FND_MESSAGE.SET_NAME('CSD','CSD_SCW_ENTITY_EXISTS');
1157 FND_MESSAGE.SET_TOKEN('SERVICE_CODE_ID',p_sc_work_entity_rec.service_code_id);
1158 FND_MSG_PUB.Add;
1159
1160 FND_MSG_PUB.Count_And_Get
1161 (p_count => x_msg_count,
1162 p_data => x_msg_data );
1163
1164 -- save message in debug log
1165 IF (lc_excep_level >= lc_debug_level) THEN
1166 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1167 'Service code work entity already exists');
1168 END IF;
1169
1170 WHEN CSD_SCW_INVALID_ORGANIZATION THEN
1171 ROLLBACK TO Update_SC_Work_Entity;
1172
1173 -- Service code work entity organization is invalid
1174 x_return_status := FND_API.G_RET_STS_ERROR ;
1175
1176 -- save message in fnd stack
1177 if (lc_stat_level >= lc_debug_level) then
1178 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1179 'Adding message CSD_SCW_INVALID_ORGANIZATION to FND_MSG stack');
1180 end if;
1181 FND_MESSAGE.SET_NAME('CSD','CSD_SCW_INVALID_ORGANIZATION');
1182 FND_MESSAGE.SET_TOKEN('ORGANIZATION_ID',p_sc_work_entity_rec.work_entity_id3);
1183 FND_MSG_PUB.Add;
1184
1185 FND_MSG_PUB.Count_And_Get
1186 (p_count => x_msg_count,
1187 p_data => x_msg_data );
1188
1189 -- save message in debug log
1190 IF (lc_excep_level >= lc_debug_level) THEN
1191 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1192 'Service code work entity organization is invalid');
1193 END IF;
1194
1195 WHEN CSD_SCW_INVALID_BILL_SEQUENCE THEN
1196 ROLLBACK TO Update_SC_Work_Entity;
1197
1198 -- Service code work entity bill reference is invalid
1199 x_return_status := FND_API.G_RET_STS_ERROR ;
1200
1201 -- save message in fnd stack
1202 if (lc_stat_level >= lc_debug_level) then
1203 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1204 'Adding message CSD_SCW_INVALID_BILL_SEQUENCE to FND_MSG stack');
1205 end if;
1206 FND_MESSAGE.SET_NAME('CSD','CSD_SCW_INVALID_BILL_SEQUENCE');
1207 FND_MESSAGE.SET_TOKEN('BILL_SEQUENCE_ID',p_sc_work_entity_rec.work_entity_id1);
1208 FND_MSG_PUB.Add;
1209
1210 FND_MSG_PUB.Count_And_Get
1211 (p_count => x_msg_count,
1212 p_data => x_msg_data );
1213
1214 -- save message in debug log
1215 IF (lc_excep_level >= lc_debug_level) THEN
1216 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1217 'Service code work entity bill reference is invalid');
1218 END IF;
1219
1220 WHEN CSD_SCW_INVALID_ROUTE_SEQUENCE THEN
1221 ROLLBACK TO Update_SC_Work_Entity;
1222
1223 -- Service code work entity routing reference is invalid
1224 x_return_status := FND_API.G_RET_STS_ERROR ;
1225
1226 -- save message in fnd stack
1227 if (lc_stat_level >= lc_debug_level) then
1228 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1229 'Adding message CSD_SCW_INVALID_ROUTE_SEQUENCE to FND_MSG stack');
1230 end if;
1231 FND_MESSAGE.SET_NAME('CSD','CSD_SCW_INVALID_ROUTE_SEQUENCE');
1232 FND_MESSAGE.SET_TOKEN('ROUTING_SEQUENCE_ID',p_sc_work_entity_rec.work_entity_id2);
1233 FND_MSG_PUB.Add;
1234
1235 FND_MSG_PUB.Count_And_Get
1236 (p_count => x_msg_count,
1237 p_data => x_msg_data );
1238
1239 -- save message in debug log
1240 IF (lc_excep_level >= lc_debug_level) THEN
1241 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1242 'Service code work entity routing reference is invalid');
1243 END IF;
1244
1245 WHEN CSD_SCW_INVALID_TASK_TEMP_GRP THEN
1246 ROLLBACK TO Update_SC_Work_Entity;
1247
1248 -- Service code work entity task template group is invalid
1249 x_return_status := FND_API.G_RET_STS_ERROR ;
1250
1251 -- save message in fnd stack
1252 if (lc_stat_level >= lc_debug_level) then
1253 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1254 'Adding message CSD_SCW_INVALID_TASK_TEMP_GRP to FND_MSG stack');
1255 end if;
1256 FND_MESSAGE.SET_NAME('CSD','CSD_SCW_INVALID_TASK_TEMP_GRP');
1257 FND_MESSAGE.SET_TOKEN('TASK_TEMP_GROUP_ID',p_sc_work_entity_rec.work_entity_id1);
1258 FND_MSG_PUB.Add;
1259
1260 FND_MSG_PUB.Count_And_Get
1261 (p_count => x_msg_count,
1262 p_data => x_msg_data );
1263
1264 -- save message in debug log
1265 IF (lc_excep_level >= lc_debug_level) THEN
1266 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1267 'Service code work entity task template group is invalid');
1268 END IF;
1269
1270 WHEN FND_API.G_EXC_ERROR THEN
1271 ROLLBACK TO Update_SC_Work_Entity;
1272
1273 x_return_status := FND_API.G_RET_STS_ERROR;
1274
1275 FND_MSG_PUB.Count_And_Get
1276 (p_count => x_msg_count,
1277 p_data => x_msg_data );
1278
1279 -- save message in debug log
1280 IF (lc_excep_level >= lc_debug_level) THEN
1281 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1282 'EXC_ERROR['||x_msg_data||']');
1283 END IF;
1284
1285 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1286 ROLLBACK TO Update_SC_Work_Entity;
1287
1288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1289
1290 IF FND_MSG_PUB.Check_Msg_Level
1291 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1292 THEN
1293 if (lc_stat_level >= lc_debug_level) then
1294 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1295 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
1296 end if;
1297 FND_MSG_PUB.Add_Exc_Msg
1298 (G_PKG_NAME ,
1299 lc_api_name );
1300 END IF;
1301
1302 FND_MSG_PUB.Count_And_Get
1303 ( p_count => x_msg_count,
1304 p_data => x_msg_data );
1305
1306 -- save message in debug log
1307 IF (lc_excep_level >= lc_debug_level) THEN
1308 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1309 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
1310 END IF;
1311
1312 WHEN OTHERS THEN
1313 ROLLBACK TO Update_SC_Work_Entity;
1314
1315 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1316
1317 -- save message in fnd stack
1318 IF FND_MSG_PUB.Check_Msg_Level
1319 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1320 THEN
1321 if (lc_stat_level >= lc_debug_level) then
1322 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1323 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
1324 end if;
1325 FND_MSG_PUB.Add_Exc_Msg
1326 (G_PKG_NAME ,
1327 lc_api_name );
1328 END IF;
1329
1330 FND_MSG_PUB.Count_And_Get
1331 (p_count => x_msg_count,
1332 p_data => x_msg_data );
1333
1334 -- save message in debug log
1335 IF (lc_excep_level >= lc_debug_level) THEN
1336 -- create a seeded message
1337 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1338 'SQL Message['||sqlerrm||']' );
1339 END IF;
1340
1341 END Update_SC_Work_Entity;
1342
1343 /*--------------------------------------------------*/
1344 /* procedure name: Delete_SC_Work_Entity */
1345 /* description : procedure used to delete */
1346 /* sc work entity */
1347 /* */
1348 /*--------------------------------------------------*/
1349 PROCEDURE Delete_SC_Work_Entity
1350 (
1351 p_api_version IN NUMBER,
1352 p_commit IN VARCHAR2,
1353 p_init_msg_list IN VARCHAR2,
1354 p_validation_level IN NUMBER,
1355 x_return_status OUT NOCOPY VARCHAR2,
1356 x_msg_count OUT NOCOPY NUMBER,
1357 x_msg_data OUT NOCOPY VARCHAR2,
1358 p_sc_work_entity_id IN NUMBER
1359 ) IS
1360
1361 -- CONSTANTS --
1362 lc_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1363 lc_stat_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
1364 lc_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
1365 lc_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
1366 lc_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
1367 lc_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
1368 lc_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
1369 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_sc_work_entities_pvt.delete_sc_work_entity';
1370 lc_api_name CONSTANT VARCHAR2(30) := 'Delete_SC_Work_Entity';
1371 lc_api_version CONSTANT NUMBER := 1.0;
1372
1373 -- VARIABLES --
1374 l_msg_count NUMBER;
1375 l_msg_data VARCHAR2(100);
1376 l_msg_index NUMBER;
1377
1378 BEGIN
1379 -- Standard Start of API savepoint
1380 SAVEPOINT Delete_SC_Work_Entity;
1381
1382 -- Standard call to check for call compatibility.
1383 IF NOT FND_API.Compatible_API_Call (lc_api_version,
1384 p_api_version,
1385 lc_api_name ,
1386 G_PKG_NAME )
1387 THEN
1388 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1389 END IF;
1390
1391 -- Initialize message list if p_init_msg_list is set to TRUE.
1392 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1393 FND_MSG_PUB.initialize;
1394 END IF;
1395
1396 IF (lc_proc_level >= lc_debug_level) THEN
1397 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
1398 'Entered Delete_SC_Work_Entity');
1399 END IF;
1400
1401 -- Initialize API return status to success
1402 x_return_status := FND_API.G_RET_STS_SUCCESS;
1403
1404 -- Api body starts
1405
1406 -- Check the required parameters
1407 if (lc_proc_level >= lc_debug_level) then
1408 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1409 'Checking required parameter');
1410 end if;
1411
1412 -- Check the required parameter
1413 CSD_PROCESS_UTIL.Check_Reqd_Param
1414 ( p_param_value => p_sc_work_entity_id,
1415 p_param_name => 'SC_WORK_ENTITY_ID',
1416 p_api_name => lc_api_name);
1417
1418 -- Delete row
1419 if (lc_proc_level >= lc_debug_level) then
1420 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1421 'Calling Delete_Row table handler');
1422 end if;
1423
1424 BEGIN
1425
1426 -- Delete the work entity
1427 CSD_SC_WORK_ENTITIES_PKG.Delete_Row
1428 (p_sc_work_entity_id => p_sc_work_entity_id );
1429
1430 EXCEPTION
1431 WHEN OTHERS THEN
1432 IF ( lc_excep_level >= lc_debug_level) THEN
1433 FND_LOG.STRING(lc_excep_level,lc_mod_name,'Others exception in CSD_SC_WORK_ENTITIES_PKG.Delete_Row Call :'||SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1,255));
1434 END IF;
1435 x_return_status := FND_API.G_RET_STS_ERROR;
1436 END;
1437
1438 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1439 RAISE FND_API.G_EXC_ERROR;
1440 END IF;
1441
1442 if (lc_proc_level >= lc_debug_level) then
1443 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1444 'Returned from Delete_Row table handler');
1445 end if;
1446
1447 -- Api body ends here
1448
1449 -- Standard check of p_commit.
1450 IF FND_API.To_Boolean( p_commit ) THEN
1451 COMMIT WORK;
1452 END IF;
1453
1454 -- Standard call to get message count and IF count is get message info.
1455 FND_MSG_PUB.Count_And_Get
1456 (p_count => x_msg_count,
1457 p_data => x_msg_data );
1458
1459 IF (lc_proc_level >= lc_debug_level) THEN
1460 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
1461 'Leaving Delete_SC_Work_Entity');
1462 END IF;
1463
1464 EXCEPTION
1465
1466 WHEN FND_API.G_EXC_ERROR THEN
1467 ROLLBACK TO Delete_SC_Work_Entity;
1468
1469 x_return_status := FND_API.G_RET_STS_ERROR;
1470
1471 FND_MSG_PUB.Count_And_Get
1472 (p_count => x_msg_count,
1473 p_data => x_msg_data );
1474
1475 -- save message in debug log
1476 IF (lc_excep_level >= lc_debug_level) THEN
1477 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1478 'EXC_ERROR['||x_msg_data||']');
1479 END IF;
1480
1481 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1482 ROLLBACK TO Delete_SC_Work_Entity;
1483
1484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1485
1486 IF FND_MSG_PUB.Check_Msg_Level
1487 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1488 THEN
1489 if (lc_stat_level >= lc_debug_level) then
1490 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1491 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
1492 end if;
1493 FND_MSG_PUB.Add_Exc_Msg
1494 (G_PKG_NAME ,
1495 lc_api_name );
1496 END IF;
1497
1498 FND_MSG_PUB.Count_And_Get
1499 ( p_count => x_msg_count,
1500 p_data => x_msg_data );
1501
1502 -- save message in debug log
1503 IF (lc_excep_level >= lc_debug_level) THEN
1504 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1505 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
1506 END IF;
1507
1508 WHEN OTHERS THEN
1509 ROLLBACK TO Delete_SC_Work_Entity;
1510
1511 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1512
1513 -- save message in fnd stack
1514 IF FND_MSG_PUB.Check_Msg_Level
1515 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1516 THEN
1517 if (lc_stat_level >= lc_debug_level) then
1518 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1519 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
1520 end if;
1521 FND_MSG_PUB.Add_Exc_Msg
1522 (G_PKG_NAME ,
1523 lc_api_name );
1524 END IF;
1525
1526 FND_MSG_PUB.Count_And_Get
1527 (p_count => x_msg_count,
1528 p_data => x_msg_data );
1529
1530 -- save message in debug log
1531 IF (lc_excep_level >= lc_debug_level) THEN
1532 -- create a seeded message
1533 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1534 'SQL Message['||sqlerrm||']' );
1535 END IF;
1536
1537 END Delete_SC_Work_Entity;
1538
1539 /*--------------------------------------------------*/
1540 /* procedure name: Lock_SC_Work_Entity */
1541 /* description : procedure used to lock */
1542 /* sc work entity */
1543 /* */
1544 /*--------------------------------------------------*/
1545 PROCEDURE Lock_SC_Work_Entity
1546 (
1547 p_api_version IN NUMBER,
1548 p_commit IN VARCHAR2,
1549 p_init_msg_list IN VARCHAR2,
1550 p_validation_level IN NUMBER,
1551 x_return_status OUT NOCOPY VARCHAR2,
1552 x_msg_count OUT NOCOPY NUMBER,
1553 x_msg_data OUT NOCOPY VARCHAR2,
1554 p_sc_work_entity_rec IN SC_WORK_ENTITY_REC_TYPE
1555 )IS
1556
1557 -- CONSTANTS --
1558 lc_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1559 lc_stat_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
1560 lc_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
1561 lc_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
1562 lc_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
1563 lc_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
1564 lc_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
1565 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_sc_work_entities_pvt.lock_sc_work_entity';
1566 lc_api_name CONSTANT VARCHAR2(30) := 'Lock_SC_Work_Entity';
1567 lc_api_version CONSTANT NUMBER := 1.0;
1568
1569 -- VARIABLES --
1570 l_msg_count NUMBER;
1571 l_msg_data VARCHAR2(100);
1572 l_msg_index NUMBER;
1573
1574 BEGIN
1575 -- Standard Start of API savepoint
1576 SAVEPOINT Lock_SC_Work_Entity;
1577
1578 -- Standard call to check for call compatibility.
1579 IF NOT FND_API.Compatible_API_Call (lc_api_version,
1580 p_api_version,
1581 lc_api_name ,
1582 G_PKG_NAME )
1583 THEN
1584 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1585 END IF;
1586
1587 -- Initialize message list if p_init_msg_list is set to TRUE.
1588 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1589 FND_MSG_PUB.initialize;
1590 END IF;
1591
1592 IF (lc_proc_level >= lc_debug_level) THEN
1593 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
1594 'Entered Lock_SC_Work_Entity');
1595 END IF;
1596
1597 -- Initialize API return status to success
1598 x_return_status := FND_API.G_RET_STS_SUCCESS;
1599
1600 -- Api body starts
1601
1602 -- Check the required parameters
1603 if (lc_proc_level >= lc_debug_level) then
1604 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1605 'Checking required parameters');
1606 end if;
1607
1608 -- Check the required parameter
1609 CSD_PROCESS_UTIL.Check_Reqd_Param
1610 ( p_param_value => p_sc_work_entity_rec.sc_work_entity_id,
1611 p_param_name => 'SC_WORK_ENTITY_ID',
1612 p_api_name => lc_api_name);
1613
1614 -- Check the required parameter
1615 CSD_PROCESS_UTIL.Check_Reqd_Param
1616 ( p_param_value => p_sc_work_entity_rec.object_version_number,
1617 p_param_name => 'OBJECT_VERSION_NUMBER',
1618 p_api_name => lc_api_name);
1619
1620 -- Lock row
1621 if (lc_proc_level >= lc_debug_level) then
1622 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1623 'Calling Lock_Row table handler');
1624 end if;
1625
1626 BEGIN
1627
1628 -- Lock the work entity
1629 CSD_SC_WORK_ENTITIES_PKG.Lock_Row
1630 (p_sc_work_entity_id => p_sc_work_entity_rec.sc_work_entity_id,
1631 p_object_version_number => p_sc_work_entity_rec.object_version_number
1632
1633 --commented out the rest of the record
1634 /*,
1635 p_service_code_id => p_sc_work_entity_rec.service_code_id,
1636 p_work_entity_id1 => p_sc_work_entity_rec.work_entity_id1,
1637 p_created_by => FND_GLOBAL.USER_ID,
1638 p_creation_date => SYSDATE,
1639 p_last_updated_by => FND_GLOBAL.USER_ID,
1640 p_last_update_date => SYSDATE,
1641 p_last_update_login => FND_GLOBAL.LOGIN_ID,
1642 p_work_entity_type_code => p_sc_work_entity_rec.work_entity_type_code,
1643 p_work_entity_id2 => p_sc_work_entity_rec.work_entity_id2,
1644 p_work_entity_id3 => p_sc_work_entity_rec.work_entity_id3,
1645 p_attribute_category => p_sc_work_entity_rec.attribute_category,
1646 p_attribute1 => p_sc_work_entity_rec.attribute1,
1647 p_attribute2 => p_sc_work_entity_rec.attribute2,
1648 p_attribute3 => p_sc_work_entity_rec.attribute3,
1649 p_attribute4 => p_sc_work_entity_rec.attribute4,
1650 p_attribute5 => p_sc_work_entity_rec.attribute5,
1651 p_attribute6 => p_sc_work_entity_rec.attribute6,
1652 p_attribute7 => p_sc_work_entity_rec.attribute7,
1653 p_attribute8 => p_sc_work_entity_rec.attribute8,
1654 p_attribute9 => p_sc_work_entity_rec.attribute9,
1655 p_attribute10 => p_sc_work_entity_rec.attribute10,
1656 p_attribute11 => p_sc_work_entity_rec.attribute11,
1657 p_attribute12 => p_sc_work_entity_rec.attribute12,
1658 p_attribute13 => p_sc_work_entity_rec.attribute13,
1659 p_attribute14 => p_sc_work_entity_rec.attribute14,
1660 p_attribute15 => p_sc_work_entity_rec.attribute15
1661 */
1662 --
1663 );
1664
1665 EXCEPTION
1666 WHEN OTHERS THEN
1667 IF ( lc_excep_level >= lc_debug_level) THEN
1668 FND_LOG.STRING(lc_excep_level,lc_mod_name,'Others exception in CSD_SC_WORK_ENTITIES_PKG.Lock_Row Call :'||SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1,255));
1669 END IF;
1670 x_return_status := FND_API.G_RET_STS_ERROR;
1671 END;
1672
1673 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1674 RAISE FND_API.G_EXC_ERROR;
1675 END IF;
1676
1677 if (lc_proc_level >= lc_debug_level) then
1678 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1679 'Returned from Lock_Row table handler');
1680 end if;
1681
1682 -- Api body ends here
1683
1684 -- Standard check of p_commit.
1685 IF FND_API.To_Boolean( p_commit ) THEN
1686 COMMIT WORK;
1687 END IF;
1688
1689 -- Standard call to get message count and IF count is get message info.
1690 FND_MSG_PUB.Count_And_Get
1691 (p_count => x_msg_count,
1692 p_data => x_msg_data );
1693
1694 IF (lc_proc_level >= lc_debug_level) THEN
1695 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
1696 'Leaving Lock_SC_Domain');
1697 END IF;
1698
1699 EXCEPTION
1700
1701 WHEN FND_API.G_EXC_ERROR THEN
1702 ROLLBACK TO Lock_SC_Work_Entity;
1703
1704 x_return_status := FND_API.G_RET_STS_ERROR;
1705
1706 FND_MSG_PUB.Count_And_Get
1707 (p_count => x_msg_count,
1708 p_data => x_msg_data );
1709
1710 -- save message in debug log
1711 IF (lc_excep_level >= lc_debug_level) THEN
1712 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1713 'EXC_ERROR['||x_msg_data||']');
1714 END IF;
1715
1716 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1717 ROLLBACK TO Lock_SC_Work_Entity;
1718
1719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1720
1721 IF FND_MSG_PUB.Check_Msg_Level
1722 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1723 THEN
1724 if (lc_stat_level >= lc_debug_level) then
1725 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1726 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
1727 end if;
1728 FND_MSG_PUB.Add_Exc_Msg
1729 (G_PKG_NAME ,
1730 lc_api_name );
1731 END IF;
1732
1733 FND_MSG_PUB.Count_And_Get
1734 ( p_count => x_msg_count,
1735 p_data => x_msg_data );
1736
1737 -- save message in debug log
1738 IF (lc_excep_level >= lc_debug_level) THEN
1739 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1740 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
1741 END IF;
1742
1743 WHEN OTHERS THEN
1744 ROLLBACK TO Lock_SC_Work_Entity;
1745
1746 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1747
1748 -- save message in fnd stack
1749 IF FND_MSG_PUB.Check_Msg_Level
1750 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1751 THEN
1752 if (lc_stat_level >= lc_debug_level) then
1753 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1754 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
1755 end if;
1756 FND_MSG_PUB.Add_Exc_Msg
1757 (G_PKG_NAME ,
1758 lc_api_name );
1759 END IF;
1760
1761 FND_MSG_PUB.Count_And_Get
1762 (p_count => x_msg_count,
1763 p_data => x_msg_data );
1764
1765 -- save message in debug log
1766 IF (lc_excep_level >= lc_debug_level) THEN
1767 -- create a seeded message
1768 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1769 'SQL Message['||sqlerrm||']' );
1770 END IF;
1771
1772 END Lock_SC_Work_Entity;
1773
1774 End CSD_SC_WORK_ENTITIES_PVT;
1775
[Home] [Help]