1 PACKAGE BODY CSD_SC_DOMAINS_PVT as
2 /* $Header: csdvscdb.pls 115.6 2004/02/16 03:20:57 gilam noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_SC_DOMAINS_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdvscdb.pls';
6
7 /*--------------------------------------------------*/
8 /* procedure name: Create_SC_Domain */
9 /* description : procedure used to create */
10 /* sc domain */
11 /* */
12 /*--------------------------------------------------*/
13 PROCEDURE Create_SC_Domain
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_sc_domain_rec IN SC_DOMAIN_REC_TYPE,
23 x_sc_domain_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_sc_domains_pvt.create_sc_domain';
35 lc_api_name CONSTANT VARCHAR2(30) := 'Create_SC_Domain';
36 lc_api_version CONSTANT NUMBER := 1.0;
37
38 -- VARIABLES --
39 l_msg_count NUMBER;
40 l_msg_data VARCHAR2(32767);
41 l_msg_index NUMBER;
42 l_obj_ver_num NUMBER := 1;
43 l_dummy VARCHAR2(1) := null;
44 l_valid_cat_flag VARCHAR2(1) := null;
45 l_inventory_item_id NUMBER := null;
46 l_category_id NUMBER := null;
47 l_category_set_id NUMBER := null;
48
49 -- EXCEPTIONS --
50 CSD_SCD_ITEM_MISSING EXCEPTION;
51 CSD_SCD_CATEGORY_SET_MISSING EXCEPTION;
52 CSD_SCD_CATEGORY_MISSING EXCEPTION;
53 CSD_SCD_DOMAIN_EXISTS EXCEPTION;
54 CSD_SCD_INVALID_ITEM EXCEPTION;
55 CSD_SCD_INVALID_CAT_SET EXCEPTION;
56 CSD_SCD_INVALID_CATSET_FLAG EXCEPTION;
57 CSD_SCD_INVALID_CATEGORY EXCEPTION;
58
59 BEGIN
60 -- Standard Start of API savepoint
61 SAVEPOINT Create_SC_Domain;
62
63 -- Standard call to check for call compatibility.
64 IF NOT FND_API.Compatible_API_Call (lc_api_version,
65 p_api_version,
66 lc_api_name ,
67 G_PKG_NAME )
68 THEN
69 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70 END IF;
71
72 -- Initialize message list if p_init_msg_list is set to TRUE.
73 IF FND_API.to_Boolean( p_init_msg_list ) THEN
74 FND_MSG_PUB.initialize;
75 END IF;
76
77 IF (lc_proc_level >= lc_debug_level) THEN
78 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
79 'Entered Create_SC_Domain');
80 END IF;
81
82 -- log parameters
83 IF (lc_stat_level >= lc_debug_level) THEN
84 csd_gen_utility_pvt.dump_sc_domain_rec
85 ( p_sc_domain_rec => p_sc_domain_rec);
86 END IF;
87
88 -- Initialize API return status to success
89 x_return_status := FND_API.G_RET_STS_SUCCESS;
90
91 -- Api body starts
92
93 -- Check the required parameters
94 if (lc_proc_level >= lc_debug_level) then
95 FND_LOG.STRING(lc_proc_level, lc_mod_name,
96 'Checking required parameters');
97 end if;
98
99 -- Check the required parameter
100 CSD_PROCESS_UTIL.Check_Reqd_Param
101 ( p_param_value => p_sc_domain_rec.service_code_id,
102 p_param_name => 'SERVICE_CODE_ID',
103 p_api_name => lc_api_name);
104
105 -- Check the required parameter
106 CSD_PROCESS_UTIL.Check_Reqd_Param
107 ( p_param_value => p_sc_domain_rec.domain_type_code,
108 p_param_name => 'DOMAIN_TYPE_CODE',
109 p_api_name => lc_api_name);
110
111 -- Check if required parameter is passed in as G_MISS
112 if (lc_proc_level >= lc_debug_level) then
113 FND_LOG.STRING(lc_proc_level, lc_mod_name,
114 'Checking if required parameters are passed in as G_MISS');
115 end if;
116
117 -- Check if required parameter is passed in as G_MISS
118 IF (p_sc_domain_rec.domain_type_code = 'ITEM'
119 and (p_sc_domain_rec.inventory_item_id = FND_API.G_MISS_NUM
120 or p_sc_domain_rec.inventory_item_id is null)) THEN
121 RAISE CSD_SCD_ITEM_MISSING;
122 END IF;
123
124 -- Check if required parameter is passed in as G_MISS
125 IF (p_sc_domain_rec.domain_type_code = 'CAT') THEN
126 IF (p_sc_domain_rec.category_set_id = FND_API.G_MISS_NUM
127 or p_sc_domain_rec.category_set_id is null) THEN
128 RAISE CSD_SCD_CATEGORY_SET_MISSING;
129 END IF;
130 IF (p_sc_domain_rec.category_id = FND_API.G_MISS_NUM
131 or p_sc_domain_rec.category_id is null) THEN
132 RAISE CSD_SCD_CATEGORY_MISSING;
133 END IF;
134 END IF;
135
136 -- Validate the domain for service code
137 if (lc_proc_level >= lc_debug_level) then
138 FND_LOG.STRING(lc_proc_level, lc_mod_name,
139 'Validate if the service code domain already exists');
140 end if;
141
142 -- Validate the domain for service code
143 Begin
144 l_dummy := null;
145
146 /* gilam: bug 3445684 - changed query to include service code id in the each or condition
147 select 'X'
148 into l_dummy
149 from csd_sc_domains
150 where service_code_id = p_sc_domain_rec.service_code_id
151 and (domain_type_code = p_sc_domain_rec.domain_type_code
152 and inventory_item_id = p_sc_domain_rec.inventory_item_id)
153 or (domain_type_code = p_sc_domain_rec.domain_type_code
154 and category_set_id = p_sc_domain_rec.category_set_id
155 and category_id = p_sc_domain_rec.category_id);
156 */
157 select 'X'
158 into l_dummy
159 from csd_sc_domains
160 where (service_code_id = p_sc_domain_rec.service_code_id
161 and domain_type_code = p_sc_domain_rec.domain_type_code
162 and inventory_item_id = p_sc_domain_rec.inventory_item_id)
163 or (service_code_id = p_sc_domain_rec.service_code_id
164 and domain_type_code = p_sc_domain_rec.domain_type_code
165 and category_set_id = p_sc_domain_rec.category_set_id
166 and category_id = p_sc_domain_rec.category_id);
167 -- gilam: end bug fix 3445684
168
169 Exception
170
171 WHEN no_data_found THEN
172 null;
173
174 WHEN others THEN
175 l_dummy := 'X';
176
177 End;
178
179 -- If domain already exists, throw an error
180 IF (l_dummy = 'X') then
181 RAISE CSD_SCD_DOMAIN_EXISTS;
182 ELSE
183 if (lc_stat_level >= lc_debug_level) then
184 FND_LOG.STRING(lc_stat_level, lc_mod_name,
185 'Service code domain does not exist');
186 end if;
187 END IF;
188
189
190 -- Validate the inventory item id if domain is ITEM
191 if (lc_proc_level >= lc_debug_level) then
192 FND_LOG.STRING(lc_proc_level, lc_mod_name,
193 'Validate the inventory item id if domain is ITEM');
194 end if;
195
196 -- Validate the inventory item id if domain is ITEM
197 IF (p_sc_domain_rec.domain_type_code = 'ITEM') then
198 Begin
199 l_dummy := null;
200
201 select 'X'
202 into l_dummy
203 from mtl_system_items_kfv
204 where organization_id = cs_std.get_item_valdn_orgzn_id
205 and inventory_item_id = p_sc_domain_rec.inventory_item_id;
206
207 Exception
208
209 WHEN others THEN
210 null;
211
212 End;
213
214 -- If item does not exist, throw an error
215 IF (l_dummy <> 'X') then
216 RAISE CSD_SCD_INVALID_ITEM;
217 ELSE
218 if (lc_stat_level >= lc_debug_level) then
219 FND_LOG.STRING(lc_stat_level, lc_mod_name,
220 'Item for service code domain is valid');
221 end if;
222 END IF;
223 END IF; -- if domain type is ITEM
224
225 -- Validate the category set id and category id if domain is CATEGORY
226
227 -- If domain is CATEGORY
228 IF (p_sc_domain_rec.domain_type_code = 'CAT' ) THEN
229
230 -- Validate the category set id if domain is CAT
231 if (lc_proc_level >= lc_debug_level) then
232 FND_LOG.STRING(lc_proc_level, lc_mod_name,
233 'Validate the category set id if domain is CATEGORY');
234 end if;
235
236 -- Validate the category set id if domain is CAT
237 Begin
238 l_dummy := null;
239
240 select 'X'
241 into l_dummy
242 from mtl_category_sets_vl
243 where category_set_id = p_sc_domain_rec.category_set_id;
244
245 Exception
246
247 WHEN others THEN
248 null;
249
250 End;
251
252 -- If category set does not exist, throw an error
253 IF (l_dummy <> 'X') then
254 RAISE CSD_SCD_INVALID_CAT_SET;
255 ELSE
256 if (lc_stat_level >= lc_debug_level) then
257 FND_LOG.STRING(lc_stat_level, lc_mod_name,
258 'Category set for service code domain is valid');
259 end if;
260 END IF;
261
262 -- Get the validate flag for the category set
263 if (lc_proc_level >= lc_debug_level) then
264 FND_LOG.STRING(lc_proc_level, lc_mod_name,
265 'Get the validate flag for the category set');
266 end if;
267
268 Begin
269 select validate_flag
270 into l_valid_cat_flag
271 from mtl_category_sets_vl
272 where category_set_id = p_sc_domain_rec.category_set_id;
273
274 Exception
275
276 WHEN others THEN
277 null;
278
279 End;
280
281 -- If category set does not exist, throw an error
282 IF (l_valid_cat_flag is null) then
283 RAISE CSD_SCD_INVALID_CATSET_FLAG;
284 ELSE
285 if (lc_stat_level >= lc_debug_level) then
286 FND_LOG.STRING(lc_stat_level, lc_mod_name,
287 'Retrieved validate flag for category set');
288 end if;
289 END IF;
290
291 -- If validate flag is Yes, validate category id from the list of
292 -- valid categories for the category set
293 -- If validate flag is No, validate category id within the same
294 -- structure as the category set
295
296 Begin
297 l_dummy := null;
298
299 if (lc_proc_level >= lc_debug_level) then
300 FND_LOG.STRING(lc_proc_level, lc_mod_name,
301 'Validate category for the category set');
302 end if;
303
304 IF (l_valid_cat_flag = 'Y') then
305
306 if (lc_proc_level >= lc_debug_level) then
307 FND_LOG.STRING(lc_proc_level, lc_mod_name,
308 'Category set validate flag is Yes');
309 end if;
310
311 select 'X'
312 into l_dummy
313 from mtl_category_set_valid_cats_v
314 where category_set_id = p_sc_domain_rec.category_set_id
315 and category_id = p_sc_domain_rec.category_id;
316
317 ELSIF (l_valid_cat_flag = 'N') then
318 if (lc_proc_level >= lc_debug_level) then
319 FND_LOG.STRING(lc_proc_level, lc_mod_name,
320 'Category set validate flag is No');
321 end if;
322
323 select 'X'
324 into l_dummy
325 from mtl_category_sets_vl mcs, mtl_categories_v mc
326 where mcs.category_set_id = p_sc_domain_rec.category_set_id
327 and mcs.structure_id = mc.structure_id
328 and mc.category_id = p_sc_domain_rec.category_id;
329 END IF;
330
331 Exception
332
333 WHEN others THEN
334 null;
335
336 End;
337
338 -- If category set does not exist, throw an error
339 IF (l_dummy <>'X') then
340 RAISE CSD_SCD_INVALID_CATEGORY;
341 ELSE
342 if (lc_stat_level >= lc_debug_level) then
343 FND_LOG.STRING(lc_stat_level, lc_mod_name,
344 'Category is valid');
345 end if;
346 END IF;
347
348 END IF; -- domain is CATEGORY
349
350 -- Set G_MISS parameters according to domain type
351 IF (p_sc_domain_rec.domain_type_code = 'CAT') then
352
353 if (lc_proc_level >= lc_debug_level) then
354 FND_LOG.STRING(lc_proc_level, lc_mod_name,
355 'Domain type is CATEGORY, setting item id to G_MISS_NUM');
356 end if;
357
358 l_inventory_item_id := FND_API.G_MISS_NUM;
359 l_category_id := p_sc_domain_rec.category_id;
360 l_category_set_id := p_sc_domain_rec.category_set_id;
361
362 ELSIF (p_sc_domain_rec.domain_type_code = 'ITEM') then
363
364 if (lc_proc_level >= lc_debug_level) then
365 FND_LOG.STRING(lc_proc_level, lc_mod_name,
366 'Domain type is ITEM, setting category set and category ids to G_MISS_NUM');
367 end if;
368
369 l_category_id := FND_API.G_MISS_NUM;
370 l_category_set_id := FND_API.G_MISS_NUM;
371 l_inventory_item_id := p_sc_domain_rec.inventory_item_id;
372 END IF;
373
374 -- Insert row
375 if (lc_proc_level >= lc_debug_level) then
376 FND_LOG.STRING(lc_proc_level, lc_mod_name,
377 'Calling Insert_Row table handler');
378 end if;
379
380 BEGIN
381
382 -- Insert the new domain
383 CSD_SC_DOMAINS_PKG.Insert_Row
384 (px_sc_domain_id => x_sc_domain_id,
385 p_object_version_number => l_obj_ver_num,
386 p_service_code_id => p_sc_domain_rec.service_code_id,
387 p_inventory_item_id => l_inventory_item_id,
388 p_category_id => l_category_id,
389 p_category_set_id => l_category_set_id,
390 p_created_by => FND_GLOBAL.USER_ID,
391 p_creation_date => SYSDATE,
392 p_last_updated_by => FND_GLOBAL.USER_ID,
393 p_last_update_date => SYSDATE,
394 p_last_update_login => FND_GLOBAL.LOGIN_ID,
395 p_domain_type_code => p_sc_domain_rec.domain_type_code,
396 p_attribute_category => p_sc_domain_rec.attribute_category,
397 p_attribute1 => p_sc_domain_rec.attribute1,
398 p_attribute2 => p_sc_domain_rec.attribute2,
399 p_attribute3 => p_sc_domain_rec.attribute3,
400 p_attribute4 => p_sc_domain_rec.attribute4,
401 p_attribute5 => p_sc_domain_rec.attribute5,
402 p_attribute6 => p_sc_domain_rec.attribute6,
403 p_attribute7 => p_sc_domain_rec.attribute7,
404 p_attribute8 => p_sc_domain_rec.attribute8,
405 p_attribute9 => p_sc_domain_rec.attribute9,
406 p_attribute10 => p_sc_domain_rec.attribute10,
407 p_attribute11 => p_sc_domain_rec.attribute11,
408 p_attribute12 => p_sc_domain_rec.attribute12,
409 p_attribute13 => p_sc_domain_rec.attribute13,
410 p_attribute14 => p_sc_domain_rec.attribute14,
411 p_attribute15 => p_sc_domain_rec.attribute15
412 );
413
414 EXCEPTION
415 WHEN OTHERS THEN
416 IF ( lc_excep_level >= lc_debug_level) THEN
417 FND_LOG.STRING(lc_excep_level,lc_mod_name,'Others exception in CSD_SC_DOMAINS_PKG.Insert_Row Call :'||SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1,255));
418 END IF;
419 x_return_status := FND_API.G_RET_STS_ERROR;
420 END;
421
422 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
423 RAISE FND_API.G_EXC_ERROR;
424 END IF;
425
426 if (lc_proc_level >= lc_debug_level) then
427 FND_LOG.STRING(lc_proc_level, lc_mod_name,
428 'Returned from Insert_Row table handler');
429 end if;
430
431 -- Api body ends here
432
433 -- Standard check of p_commit.
434 IF FND_API.To_Boolean( p_commit ) THEN
435 COMMIT WORK;
436 END IF;
437
438 -- Standard call to get message count and IF count is get message info.
439 FND_MSG_PUB.Count_And_Get
440 (p_count => x_msg_count,
441 p_data => x_msg_data );
442
443 IF (lc_proc_level >= lc_debug_level) THEN
444 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
445 'Leaving Create_SC_Domain');
446 END IF;
447
448 EXCEPTION
449 WHEN CSD_SCD_ITEM_MISSING THEN
450 ROLLBACK TO Create_SC_Domain;
451
452 -- Service code domain item is missing
453 x_return_status := FND_API.G_RET_STS_ERROR ;
454
455 -- save message in fnd stack
456 if (lc_stat_level >= lc_debug_level) then
457 FND_LOG.STRING(lc_stat_level, lc_mod_name,
458 'Adding message CSD_SCD_ITEM_MISSING to FND_MSG stack');
459 end if;
460 FND_MESSAGE.SET_NAME('CSD','CSD_API_MISSING_PARAM');
461 FND_MESSAGE.SET_TOKEN('API_NAME',lc_api_name);
462 FND_MESSAGE.SET_TOKEN('MISSING_PARAM','INVENTORY_ITEM_ID');
463 FND_MSG_PUB.Add;
464
465 FND_MSG_PUB.Count_And_Get
466 (p_count => x_msg_count,
467 p_data => x_msg_data );
468
469 -- save message in debug log
470 IF (lc_excep_level >= lc_debug_level) THEN
471 FND_LOG.STRING(lc_excep_level, lc_mod_name,
472 'Service code domain item is missing');
473 END IF;
474
475 WHEN CSD_SCD_CATEGORY_SET_MISSING THEN
476 ROLLBACK TO Create_SC_Domain;
477
478 -- Service code domain category set is missing
479 x_return_status := FND_API.G_RET_STS_ERROR ;
480
481 -- save message in fnd stack
482 if (lc_stat_level >= lc_debug_level) then
483 FND_LOG.STRING(lc_stat_level, lc_mod_name,
484 'Adding message CSD_SCD_CATEGORY_SET_MISSING to FND_MSG stack');
485 end if;
486 FND_MESSAGE.SET_NAME('CSD','CSD_API_MISSING_PARAM');
487 FND_MESSAGE.SET_TOKEN('API_NAME',lc_api_name);
488 FND_MESSAGE.SET_TOKEN('MISSING_PARAM','CATEGORY_SET_ID');
489 FND_MSG_PUB.Add;
490
491 FND_MSG_PUB.Count_And_Get
492 (p_count => x_msg_count,
493 p_data => x_msg_data );
494
495 -- save message in debug log
496 IF (lc_excep_level >= lc_debug_level) THEN
497 FND_LOG.STRING(lc_excep_level, lc_mod_name,
498 'Service code domain category set is missing');
499 END IF;
500
501 WHEN CSD_SCD_CATEGORY_MISSING THEN
502 ROLLBACK TO Create_SC_Domain;
503
504 -- Service code domain category is missing
505 x_return_status := FND_API.G_RET_STS_ERROR ;
506
507 -- save message in fnd stack
508 if (lc_stat_level >= lc_debug_level) then
509 FND_LOG.STRING(lc_stat_level, lc_mod_name,
510 'Adding message CSD_SCD_CATEGORY_MISSING to FND_MSG stack');
511 end if;
512 FND_MESSAGE.SET_NAME('CSD','CSD_API_MISSING_PARAM');
513 FND_MESSAGE.SET_TOKEN('API_NAME',lc_api_name);
514 FND_MESSAGE.SET_TOKEN('MISSING_PARAM','CATEGORY_ID');
515 FND_MSG_PUB.Add;
516
517 FND_MSG_PUB.Count_And_Get
518 (p_count => x_msg_count,
519 p_data => x_msg_data );
520
521 -- save message in debug log
522 IF (lc_excep_level >= lc_debug_level) THEN
523 FND_LOG.STRING(lc_excep_level, lc_mod_name,
524 'Service code domain category is missing');
525 END IF;
526
527 WHEN CSD_SCD_DOMAIN_EXISTS THEN
528 ROLLBACK TO Create_SC_Domain;
529
530 -- Service code domain already exists
531 x_return_status := FND_API.G_RET_STS_ERROR ;
532
533 -- save message in fnd stack
534 if (lc_stat_level >= lc_debug_level) then
535 FND_LOG.STRING(lc_stat_level, lc_mod_name,
536 'Adding message CSD_SCD_DOMAIN_EXISTS to FND_MSG stack');
537 end if;
538 FND_MESSAGE.SET_NAME('CSD','CSD_SCD_DOMAIN_EXISTS');
539 FND_MESSAGE.SET_TOKEN('SERVICE_CODE_ID',p_sc_domain_rec.service_code_id);
540 FND_MSG_PUB.Add;
541
542 FND_MSG_PUB.Count_And_Get
543 (p_count => x_msg_count,
544 p_data => x_msg_data );
545
546 -- save message in debug log
547 IF (lc_excep_level >= lc_debug_level) THEN
548 FND_LOG.STRING(lc_excep_level, lc_mod_name,
549 'Service code domain already exists');
550 END IF;
551
552 WHEN CSD_SCD_INVALID_ITEM THEN
553 ROLLBACK TO Create_SC_Domain;
554
555 -- Service code domain item is invalid
556 x_return_status := FND_API.G_RET_STS_ERROR ;
557
558 -- save message in fnd stack
559 if (lc_stat_level >= lc_debug_level) then
560 FND_LOG.STRING(lc_stat_level, lc_mod_name,
561 'Adding message CSD_SCD_INVALID_ITEM to FND_MSG stack');
562 end if;
563 FND_MESSAGE.SET_NAME('CSD','CSD_SCD_INVALID_ITEM');
564 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID',p_sc_domain_rec.inventory_item_id);
565 FND_MSG_PUB.Add;
566
567 FND_MSG_PUB.Count_And_Get
568 (p_count => x_msg_count,
569 p_data => x_msg_data );
570
571 -- save message in debug log
572 IF (lc_excep_level >= lc_debug_level) THEN
573 FND_LOG.STRING(lc_excep_level, lc_mod_name,
574 'Service code domain item is invalid');
575 END IF;
576
577 WHEN CSD_SCD_INVALID_CAT_SET THEN
578 ROLLBACK TO Create_SC_Domain;
579
580 -- Service code domain category set is invalid
581 x_return_status := FND_API.G_RET_STS_ERROR ;
582
583 -- save message in fnd stack
584 if (lc_stat_level >= lc_debug_level) then
585 FND_LOG.STRING(lc_stat_level, lc_mod_name,
586 'Adding message CSD_SCD_INVALID_CAT_SET to FND_MSG stack');
587 end if;
588 FND_MESSAGE.SET_NAME('CSD','CSD_SCD_INVALID_CAT_SET');
589 FND_MESSAGE.SET_TOKEN('CATEGORY_SET_ID',p_sc_domain_rec.category_set_id);
590 FND_MSG_PUB.Add;
591
592 FND_MSG_PUB.Count_And_Get
593 (p_count => x_msg_count,
594 p_data => x_msg_data );
595
596 -- save message in debug log
597 IF (lc_excep_level >= lc_debug_level) THEN
598 FND_LOG.STRING(lc_excep_level, lc_mod_name,
599 'Service code domain category set is invalid');
600 END IF;
601
602 WHEN CSD_SCD_INVALID_CATSET_FLAG THEN
603 ROLLBACK TO Create_SC_Domain;
604
605 -- Service code domain category set validate flag is invalid
606 x_return_status := FND_API.G_RET_STS_ERROR ;
607
608 -- save message in fnd stack
609 if (lc_stat_level >= lc_debug_level) then
610 FND_LOG.STRING(lc_stat_level, lc_mod_name,
611 'Adding message CSD_SCD_INVALID_CATSET_FLAG to FND_MSG stack');
612 end if;
613 FND_MESSAGE.SET_NAME('CSD','CSD_SCD_INVALID_CATSET_FLAG');
614 FND_MESSAGE.SET_TOKEN('CATEGORY_SET_ID',p_sc_domain_rec.category_set_id);
615 FND_MSG_PUB.Add;
616
617 FND_MSG_PUB.Count_And_Get
618 (p_count => x_msg_count,
619 p_data => x_msg_data );
620
621 -- save message in debug log
622 IF (lc_excep_level >= lc_debug_level) THEN
623 FND_LOG.STRING(lc_excep_level, lc_mod_name,
624 'Service code domain category set validate flag is invalid');
625 END IF;
626
627 WHEN CSD_SCD_INVALID_CATEGORY THEN
628 ROLLBACK TO Create_SC_Domain;
629
630 -- Service code domain category is invalid
631 x_return_status := FND_API.G_RET_STS_ERROR ;
632
633 -- save message in fnd stack
634 if (lc_stat_level >= lc_debug_level) then
635 FND_LOG.STRING(lc_stat_level, lc_mod_name,
636 'Adding message CSD_SCD_INVALID_CATEGORY to FND_MSG stack');
637 end if;
638 FND_MESSAGE.SET_NAME('CSD','CSD_SCD_INVALID_CATEGORY');
639 FND_MESSAGE.SET_TOKEN('CATEGORY_ID',p_sc_domain_rec.category_id);
640 FND_MSG_PUB.Add;
641
642 FND_MSG_PUB.Count_And_Get
643 (p_count => x_msg_count,
644 p_data => x_msg_data );
645
646 -- save message in debug log
647 IF (lc_excep_level >= lc_debug_level) THEN
648 FND_LOG.STRING(lc_excep_level, lc_mod_name,
649 'Service code domain category is invalid');
650 END IF;
651
652 WHEN FND_API.G_EXC_ERROR THEN
653 ROLLBACK TO Create_SC_Domain;
654
655 x_return_status := FND_API.G_RET_STS_ERROR;
656
657 FND_MSG_PUB.Count_And_Get
658 (p_count => x_msg_count,
659 p_data => x_msg_data );
660
661 -- save message in debug log
662 IF (lc_excep_level >= lc_debug_level) THEN
663 FND_LOG.STRING(lc_excep_level, lc_mod_name,
664 'EXC_ERROR['||x_msg_data||']');
665 END IF;
666
667 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
668 ROLLBACK TO Create_SC_Domain;
669
670 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
671
672 IF FND_MSG_PUB.Check_Msg_Level
673 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
674 THEN
675 if (lc_stat_level >= lc_debug_level) then
676 FND_LOG.STRING(lc_stat_level, lc_mod_name,
677 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
678 end if;
679 FND_MSG_PUB.Add_Exc_Msg
680 (G_PKG_NAME ,
681 lc_api_name );
682 END IF;
683
684 FND_MSG_PUB.Count_And_Get
685 ( p_count => x_msg_count,
686 p_data => x_msg_data );
687
688 -- save message in debug log
689 IF (lc_excep_level >= lc_debug_level) THEN
690 FND_LOG.STRING(lc_excep_level, lc_mod_name,
691 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
692 END IF;
693
694 WHEN OTHERS THEN
695 ROLLBACK TO Create_SC_Domain;
696
697 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
698
699 -- save message in fnd stack
700 IF FND_MSG_PUB.Check_Msg_Level
701 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
702 THEN
703 if (lc_stat_level >= lc_debug_level) then
704 FND_LOG.STRING(lc_stat_level, lc_mod_name,
705 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
706 end if;
707 FND_MSG_PUB.Add_Exc_Msg
708 (G_PKG_NAME ,
709 lc_api_name );
710 END IF;
711
712 FND_MSG_PUB.Count_And_Get
713 (p_count => x_msg_count,
714 p_data => x_msg_data );
715
716 -- save message in debug log
717 IF (lc_excep_level >= lc_debug_level) THEN
718 -- create a seeded message
719 FND_LOG.STRING(lc_excep_level, lc_mod_name,
720 'SQL Message['||sqlerrm||']' );
721 END IF;
722
723 END Create_SC_Domain;
724
725
726 /*--------------------------------------------------*/
727 /* procedure name: Update_SC_Domain */
728 /* description : procedure used to update */
729 /* sc domain */
730 /* */
731 /*--------------------------------------------------*/
732 PROCEDURE Update_SC_Domain
733 (
734 p_api_version IN NUMBER,
735 p_commit IN VARCHAR2,
736 p_init_msg_list IN VARCHAR2,
737 p_validation_level IN NUMBER,
738 x_return_status OUT NOCOPY VARCHAR2,
739 x_msg_count OUT NOCOPY NUMBER,
740 x_msg_data OUT NOCOPY VARCHAR2,
741 p_sc_domain_rec IN SC_DOMAIN_REC_TYPE,
742 x_obj_ver_number OUT NOCOPY NUMBER
743 ) IS
744
745 -- CONSTANTS --
746 lc_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
747 lc_stat_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
748 lc_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
749 lc_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
750 lc_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
751 lc_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
752 lc_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
753 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_sc_domains_pvt.update_sc_domain';
754 lc_api_name CONSTANT VARCHAR2(30) := 'Update_SC_Domain';
755 lc_api_version CONSTANT NUMBER := 1.0;
756
757 -- VARIABLES --
758 l_msg_count NUMBER;
759 l_msg_data VARCHAR2(32767);
760 l_msg_index NUMBER;
761 l_obj_ver_num NUMBER;
762 l_dummy VARCHAR2(1) := null;
763 l_valid_cat_flag VARCHAR2(1) := null;
764 l_inventory_item_id NUMBER := null;
765 l_category_id NUMBER := null;
766 l_category_set_id NUMBER := null;
767
768 -- EXCEPTIONS --
769 CSD_SCD_SC_ID_MISSING EXCEPTION;
770 CSD_SCD_DOMAIN_TYPE_MISSING EXCEPTION;
771 CSD_SCD_ITEM_MISSING EXCEPTION;
772 CSD_SCD_CATEGORY_SET_MISSING EXCEPTION;
773 CSD_SCD_CATEGORY_MISSING EXCEPTION;
774 CSD_SCD_INVALID_ID EXCEPTION;
775 CSD_SCD_GET_OVN_ERROR EXCEPTION;
776 CSD_SCD_OVN_MISMATCH EXCEPTION;
777 CSD_SCD_DOMAIN_EXISTS EXCEPTION;
778 CSD_SCD_GET_ITEM_ERROR EXCEPTION;
779 CSD_SCD_INVALID_ITEM EXCEPTION;
780 CSD_SCD_GET_CAT_SET_ERROR EXCEPTION;
781 CSD_SCD_INVALID_CAT_SET EXCEPTION;
782 CSD_SCD_GET_CATSET_FLAG_ERROR EXCEPTION;
783 CSD_SCD_GET_CATEGORY_ERROR EXCEPTION;
784 CSD_SCD_INVALID_CATEGORY EXCEPTION;
785
786 BEGIN
787 -- Standard Start of API savepoint
788 SAVEPOINT Update_SC_Domain;
789
790 -- Standard call to check for call compatibility.
791 IF NOT FND_API.Compatible_API_Call (lc_api_version,
792 p_api_version,
793 lc_api_name ,
794 G_PKG_NAME )
795 THEN
796 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
797 END IF;
798
799 -- Initialize message list if p_init_msg_list is set to TRUE.
800 IF FND_API.to_Boolean( p_init_msg_list ) THEN
801 FND_MSG_PUB.initialize;
802 END IF;
803
804 IF (lc_proc_level >= lc_debug_level) THEN
805 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
806 'Entered Update_SC_Domain');
807 END IF;
808
809 -- log parameters
810 IF (lc_stat_level >= lc_debug_level) THEN
811 csd_gen_utility_pvt.dump_sc_domain_rec
812 ( p_sc_domain_rec => p_sc_domain_rec);
813 END IF;
814
815 -- Initialize API return status to success
816 x_return_status := FND_API.G_RET_STS_SUCCESS;
817
818 -- Api body starts
819
820 -- Check the required parameters
821 if (lc_proc_level >= lc_debug_level) then
822 FND_LOG.STRING(lc_proc_level, lc_mod_name,
823 'Checking required parameter');
824 end if;
825
826 -- Check the required parameter
827 CSD_PROCESS_UTIL.Check_Reqd_Param
828 ( p_param_value => p_sc_domain_rec.sc_domain_id,
829 p_param_name => 'SC_DOMAIN_ID',
830 p_api_name => lc_api_name);
831
832 -- Check if required parameter is passed in as G_MISS
833 if (lc_proc_level >= lc_debug_level) then
834 FND_LOG.STRING(lc_proc_level, lc_mod_name,
835 'Checking if required parameters are passed in as G_MISS');
836 end if;
837
838 -- Check if required parameter is passed in as G_MISS
839 IF (p_sc_domain_rec.service_code_id = FND_API.G_MISS_NUM) THEN
840 RAISE CSD_SCD_SC_ID_MISSING;
841 END IF;
842
843 -- Check if required parameter is passed in as G_MISS
844 IF (p_sc_domain_rec.domain_type_code = FND_API.G_MISS_CHAR) THEN
845 RAISE CSD_SCD_DOMAIN_TYPE_MISSING;
846 END IF;
847
848 -- Check if required parameter is passed in as G_MISS
849 IF (p_sc_domain_rec.domain_type_code = 'ITEM'
850 and p_sc_domain_rec.inventory_item_id = FND_API.G_MISS_NUM) THEN
851 RAISE CSD_SCD_ITEM_MISSING;
852 END IF;
853
854 -- Check if required parameter is passed in as G_MISS
855 IF (p_sc_domain_rec.domain_type_code = 'CAT') THEN
856 IF (p_sc_domain_rec.category_set_id = FND_API.G_MISS_NUM) THEN
857 RAISE CSD_SCD_CATEGORY_SET_MISSING;
858 END IF;
859
860 IF (p_sc_domain_rec.category_id = FND_API.G_MISS_NUM) THEN
861 RAISE CSD_SCD_CATEGORY_MISSING;
862 END IF;
863 END IF;
864
865 -- Validate the domain for service code
866 if (lc_proc_level >= lc_debug_level) then
867 FND_LOG.STRING(lc_proc_level, lc_mod_name,
868 'Validate the service code domain id');
869 end if;
870
871 -- Validate the service code domain id
872 Begin
873 select 'X'
874 into l_dummy
875 from csd_sc_domains
876 where sc_domain_id = p_sc_domain_rec.sc_domain_id;
877
878 Exception
879 WHEN others THEN
880 null;
881
882 End;
883
884 -- If domain id is invalid, throw an error
885 IF (l_dummy <> 'X') then
886 RAISE CSD_SCD_INVALID_ID;
887 ELSE
888 if (lc_stat_level >= lc_debug_level) then
889 FND_LOG.STRING(lc_stat_level, lc_mod_name,
890 'Service code domain id is valid');
891 end if;
892 END IF;
893
894 -- Get the object version number for service code domain
895 if (lc_proc_level >= lc_debug_level) then
896 FND_LOG.STRING(lc_proc_level, lc_mod_name,
897 'Get object version number for service code domain');
898 end if;
899
900 -- Get object version number for service code domain
901 Begin
902 select object_version_number
903 into l_obj_ver_num
904 from csd_sc_domains
905 where sc_domain_id = p_sc_domain_rec.sc_domain_id;
906
907 Exception
908
909 WHEN others THEN
910 l_obj_ver_num := null;
911
912 End;
913
914 -- If no object version number, throw an error
915 IF (l_obj_ver_num is null) then
916 RAISE CSD_SCD_GET_OVN_ERROR;
917 ELSE
918 if (lc_stat_level >= lc_debug_level) then
919 FND_LOG.STRING(lc_stat_level, lc_mod_name,
920 'Retrieved object version number');
921 end if;
922 END IF;
923
924 -- Validate the object version number for service code domain
925 if (lc_proc_level >= lc_debug_level) then
926 FND_LOG.STRING(lc_proc_level, lc_mod_name,
927 'Validate object version number for service code domain');
928 end if;
929
930 -- Validate if object version number for service code domain is same as the one passed in
931 IF NVL(p_sc_domain_rec.object_version_number,FND_API.G_MISS_NUM) <> l_obj_ver_num THEN
932 RAISE CSD_SCD_OVN_MISMATCH;
933 ELSE
934 if (lc_stat_level >= lc_debug_level) then
935 FND_LOG.STRING(lc_stat_level, lc_mod_name,
936 'Object version number is valid');
937 end if;
938 END IF;
939
940 -- Validate the code for service code
941 if (lc_proc_level >= lc_debug_level) then
942 FND_LOG.STRING(lc_proc_level, lc_mod_name,
943 'Validate if the service code domain already exists ');
944 end if;
945
946 -- Validate the domain for service code
947 Begin
948 l_dummy := null;
949
950 /* gilam: bug 3445684 - changed query to include service code id in the each or condition
951 select 'X'
952 into l_dummy
953 from csd_sc_domains
954 where sc_domain_id <> p_sc_domain_rec.sc_domain_id
955 and service_code_id = p_sc_domain_rec.service_code_id
956 and domain_type_code = p_sc_domain_rec.domain_type_code
957 and inventory_item_id = p_sc_domain_rec.inventory_item_id
958 and category_set_id = p_sc_domain_rec.category_set_id
959 and category_id = p_sc_domain_rec.category_id;
960 */
961 select 'X'
962 into l_dummy
963 from csd_sc_domains
964 where sc_domain_id <> p_sc_domain_rec.sc_domain_id
965 and (service_code_id = p_sc_domain_rec.service_code_id
966 and domain_type_code = p_sc_domain_rec.domain_type_code
967 and inventory_item_id = p_sc_domain_rec.inventory_item_id)
968 or (service_code_id = p_sc_domain_rec.service_code_id
969 and domain_type_code = p_sc_domain_rec.domain_type_code
970 and category_set_id = p_sc_domain_rec.category_set_id
971 and category_id = p_sc_domain_rec.category_id);
972 -- gilam: end bug fix 3445684
973
974 Exception
975
976 WHEN no_data_found THEN
977 null;
978
979 WHEN others THEN
980 l_dummy := 'X';
981
982 End;
983
984 -- If domain already exists, throw an error
985 IF (l_dummy = 'X') then
986 RAISE CSD_SCD_DOMAIN_EXISTS;
987 ELSE
988 if (lc_stat_level >= lc_debug_level) then
989 FND_LOG.STRING(lc_stat_level, lc_mod_name,
990 'Service code domain does not exist');
991 end if;
992 END IF;
993
994 -- If domain is ITEM
995 IF (p_sc_domain_rec.domain_type_code = 'ITEM') then
996
997 -- Validate the inventory item id if domain is ITEM
998 if (lc_proc_level >= lc_debug_level) then
999 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1000 'Domain type is ITEM, perform validations');
1001 end if;
1002
1003 IF (p_sc_domain_rec.inventory_item_id is null) THEN
1004
1005 -- inventory item id is null, get the existing one
1006 if (lc_proc_level >= lc_debug_level) then
1007 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1008 'Retrieve existing inventory item id since nothing is passed in');
1009 end if;
1010
1011 Begin
1012 select inventory_item_id
1013 into l_inventory_item_id
1014 from csd_sc_domains_v
1015 where sc_domain_id = p_sc_domain_rec.sc_domain_id
1016 and domain_type_code = p_sc_domain_rec.domain_type_code;
1017
1018 Exception
1019
1020 WHEN others THEN
1021 l_inventory_item_id := null;
1022 End;
1023
1024 -- If item is not retrieved, throw an error
1025 IF (l_inventory_item_id is null) then
1026 RAISE CSD_SCD_GET_ITEM_ERROR;
1027 ELSE
1028 if (lc_stat_level >= lc_debug_level) then
1029 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1030 'Item for service code domain is retrieved');
1031 end if;
1032 END IF;
1033
1034 ELSE -- if inventory item id is passed in
1035
1036 -- Validate the inventory item id
1037 if (lc_proc_level >= lc_debug_level) then
1038 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1039 'Validate the inventory item id passed in');
1040 end if;
1041
1042 Begin
1043 select inventory_item_id
1044 into l_inventory_item_id
1045 from mtl_system_items_kfv
1046 where organization_id = cs_std.get_item_valdn_orgzn_id
1047 and inventory_item_id = p_sc_domain_rec.inventory_item_id;
1048
1049 Exception
1050
1051 WHEN others THEN
1052 l_inventory_item_id := null;
1053 End;
1054
1055 -- If item is invalid, throw an error
1056 IF (l_inventory_item_id is null) then
1057 RAISE CSD_SCD_INVALID_ITEM;
1058 ELSE
1059 if (lc_stat_level >= lc_debug_level) then
1060 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1061 'Item for service code domain is valid');
1062 end if;
1063 END IF;
1064 END IF; -- inventory item id is null
1065
1066 END IF;-- domain is ITEM
1067
1068 -- Validate the category set id and category id if domain is CATEGORY
1069
1070 -- If domain is CATEGORY
1071 IF (p_sc_domain_rec.domain_type_code = 'CAT' ) THEN
1072
1073 -- Validate the category set id if domain is CAT
1074 if (lc_proc_level >= lc_debug_level) then
1075 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1076 'Domain type is CATEGORY, perform validations');
1077 end if;
1078
1079 -- Validate the category set id if it is passed in
1080 IF (p_sc_domain_rec.category_set_id is null) THEN
1081
1082 -- If category set is null, get the existing category set id
1083 if (lc_proc_level >= lc_debug_level) then
1084 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1085 'Get the existing category set id since nothing is passed in');
1086 end if;
1087
1088 Begin
1089 select category_set_id
1090 into l_category_set_id
1091 from csd_sc_domains_v
1092 where sc_domain_id = p_sc_domain_rec.sc_domain_id
1093 and domain_type_code = p_sc_domain_rec.domain_type_code;
1094
1095 Exception
1096
1097 WHEN others THEN
1098 l_category_set_id := null;
1099
1100 End;
1101
1102 -- If category set does not exist, throw an error
1103 IF (l_category_set_id is null) then
1104 RAISE CSD_SCD_GET_CAT_SET_ERROR;
1105 ELSE
1106 if (lc_stat_level >= lc_debug_level) then
1107 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1108 'Error retrieving existing category set for service code domain');
1109 end if;
1110 END IF;
1111
1112 ELSE
1113
1114 -- Validate the category set id passed in
1115 if (lc_proc_level >= lc_debug_level) then
1116 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1117 'Validate the category set id passed in');
1118 end if;
1119
1120 Begin
1121 select category_set_id
1122 into l_category_set_id
1123 from mtl_category_sets_vl
1124 where category_set_id = p_sc_domain_rec.category_set_id;
1125
1126 Exception
1127
1128 WHEN others THEN
1129 l_category_set_id := null;
1130
1131 End;
1132
1133 -- If category set does not exist, throw an error
1134 IF (l_category_set_id is null) then
1135 RAISE CSD_SCD_INVALID_CAT_SET;
1136 ELSE
1137 if (lc_stat_level >= lc_debug_level) then
1138 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1139 'Category set for service code domain is valid');
1140 end if;
1141 END IF;
1142
1143
1144 END IF;
1145
1146 -- Get the validate flag for the category set
1147 if (lc_proc_level >= lc_debug_level) then
1148 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1149 'Get the validate flag for the category set');
1150 end if;
1151
1152 -- Get the validate flag for the category set
1153 Begin
1154 select validate_flag
1155 into l_valid_cat_flag
1156 from mtl_category_sets_vl
1157 where category_set_id = l_category_set_id;
1158
1159 Exception
1160
1161 WHEN others THEN
1162 null;
1163
1164 End;
1165
1166 -- If category set does not exist, throw an error
1167 IF (l_valid_cat_flag is null) then
1168 RAISE CSD_SCD_GET_CATSET_FLAG_ERROR;
1169 ELSE
1170 if (lc_stat_level >= lc_debug_level) then
1171 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1172 'Retrieved validate flag for category set');
1173 end if;
1174 END IF;
1175
1176 -- If category id is null, get the existing one
1177 IF (p_sc_domain_rec.category_id is null) THEN
1178
1179 if (lc_stat_level >= lc_debug_level) then
1180 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1181 'Retrieved existing category id since nothing is passed in');
1182 end if;
1183
1184 Begin
1185 select category_id
1186 into l_category_id
1187 from csd_sc_domains_v
1188 where sc_domain_id = p_sc_domain_rec.sc_domain_id
1189 and domain_type_code = p_sc_domain_rec.domain_type_code;
1190
1191 Exception
1192
1193 WHEN others THEN
1194 l_category_id := null;
1195
1196 End;
1197
1198 -- If category is not retrieved, throw an error
1199 IF (l_category_id is null) then
1200 RAISE CSD_SCD_GET_CATEGORY_ERROR;
1201 ELSE
1202 if (lc_stat_level >= lc_debug_level) then
1203 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1204 'Retrieved category for service code domain');
1205 end if;
1206 END IF;
1207
1208 ELSE -- category id is passed in
1209
1210 l_category_id := p_sc_domain_rec.category_id;
1211
1212 END IF;
1213
1214 -- If validate flag is Yes, validate category id from the list of
1215 -- valid categories for the category set
1216 -- If validate flag is No, validate category id within the same
1217 -- structure as the category set
1218
1219 Begin
1220 l_dummy := null;
1221
1222 if (lc_proc_level >= lc_debug_level) then
1223 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1224 'Validate category for the category set');
1225 end if;
1226
1227 IF (l_valid_cat_flag = 'Y') then
1228
1229 if (lc_proc_level >= lc_debug_level) then
1230 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1231 'Category set validate flag is Yes');
1232 end if;
1233
1234 select 'X'
1235 into l_dummy
1236 from mtl_category_set_valid_cats_v
1237 where category_set_id = l_category_set_id
1238 and category_id = l_category_id;
1239
1240 ELSIF (l_valid_cat_flag = 'N') then
1241 if (lc_proc_level >= lc_debug_level) then
1242 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1243 'Category set validate flag is No');
1244 end if;
1245
1246 select 'X'
1247 into l_dummy
1248 from mtl_category_sets_vl mcs, mtl_categories_v mc
1249 where mcs.category_set_id = l_category_set_id
1250 and mcs.structure_id = mc.structure_id
1251 and mc.category_id = l_category_id;
1252 END IF;
1253
1254 Exception
1255
1256 WHEN others THEN
1257 null;
1258
1259 End;
1260
1261 -- If category set does not exist, throw an error
1262 IF (l_dummy <>'X') then
1263 RAISE CSD_SCD_INVALID_CATEGORY;
1264 ELSE
1265 if (lc_stat_level >= lc_debug_level) then
1266 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1267 'Category is valid');
1268 end if;
1269 END IF;
1270
1271 END IF; -- If domain is CATEGORY
1272
1273 -- Set G_MISS parameters according to domain type
1274 IF (p_sc_domain_rec.domain_type_code = 'CAT') then
1275
1276 if (lc_proc_level >= lc_debug_level) then
1277 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1278 'Domain type is CATEGORY, setting item id to G_MISS_NUM');
1279 end if;
1280
1281 l_inventory_item_id := FND_API.G_MISS_NUM;
1282
1283 ELSIF (p_sc_domain_rec.domain_type_code = 'ITEM') then
1284
1285 if (lc_proc_level >= lc_debug_level) then
1286 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1287 'Domain type is ITEM, setting category set and category ids to G_MISS_NUM');
1288 end if;
1289
1290 l_category_id := FND_API.G_MISS_NUM;
1291 l_category_set_id := FND_API.G_MISS_NUM;
1292 END IF;
1293
1294 -- Update row
1295 if (lc_proc_level >= lc_debug_level) then
1296 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1297 'Calling Update_Row table handler');
1298 end if;
1299
1300 BEGIN
1301
1302 -- Update the service code domain
1303 CSD_SC_DOMAINS_PKG.Update_Row
1304 (p_sc_domain_id => p_sc_domain_rec.sc_domain_id,
1305 p_object_version_number => l_obj_ver_num + 1,
1306 p_service_code_id => p_sc_domain_rec.service_code_id,
1307 p_inventory_item_id => l_inventory_item_id,
1308 p_category_id => l_category_id,
1309 p_category_set_id => l_category_set_id,
1310 p_created_by => FND_GLOBAL.USER_ID,
1311 p_creation_date => SYSDATE,
1312 p_last_updated_by => FND_GLOBAL.USER_ID,
1313 p_last_update_date => SYSDATE,
1314 p_last_update_login => FND_GLOBAL.LOGIN_ID,
1315 p_domain_type_code => p_sc_domain_rec.domain_type_code,
1316 p_attribute_category => p_sc_domain_rec.attribute_category,
1317 p_attribute1 => p_sc_domain_rec.attribute1,
1318 p_attribute2 => p_sc_domain_rec.attribute2,
1319 p_attribute3 => p_sc_domain_rec.attribute3,
1320 p_attribute4 => p_sc_domain_rec.attribute4,
1321 p_attribute5 => p_sc_domain_rec.attribute5,
1322 p_attribute6 => p_sc_domain_rec.attribute6,
1323 p_attribute7 => p_sc_domain_rec.attribute7,
1324 p_attribute8 => p_sc_domain_rec.attribute8,
1325 p_attribute9 => p_sc_domain_rec.attribute9,
1326 p_attribute10 => p_sc_domain_rec.attribute10,
1327 p_attribute11 => p_sc_domain_rec.attribute11,
1328 p_attribute12 => p_sc_domain_rec.attribute12,
1329 p_attribute13 => p_sc_domain_rec.attribute13,
1330 p_attribute14 => p_sc_domain_rec.attribute14,
1331 p_attribute15 => p_sc_domain_rec.attribute15
1332 );
1333
1334 x_obj_ver_number := l_obj_ver_num + 1;
1335
1336 EXCEPTION
1337 WHEN OTHERS THEN
1338 IF ( lc_excep_level >= lc_debug_level) THEN
1339 FND_LOG.STRING(lc_excep_level,lc_mod_name,'Others exception in CSD_SC_DOMAINS_PKG.Update_Row Call :'||SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1,255));
1340 END IF;
1341 x_return_status := FND_API.G_RET_STS_ERROR;
1342 END;
1343
1344 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1345 RAISE FND_API.G_EXC_ERROR;
1346 END IF;
1347
1348 if (lc_proc_level >= lc_debug_level) then
1349 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1350 'Returned from Update_Row table handler');
1351 end if;
1352
1353 -- Api body ends here
1354
1355 -- Standard check of p_commit.
1356 IF FND_API.To_Boolean( p_commit ) THEN
1357 COMMIT WORK;
1358 END IF;
1359
1360 -- Standard call to get message count and IF count is get message info.
1361 FND_MSG_PUB.Count_And_Get
1362 (p_count => x_msg_count,
1363 p_data => x_msg_data );
1364
1365 IF (lc_proc_level >= lc_debug_level) THEN
1366 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
1367 'Leaving Update_SC_Domain');
1368 END IF;
1369
1370 EXCEPTION
1371 WHEN CSD_SCD_SC_ID_MISSING THEN
1372 ROLLBACK TO Update_SC_Domain;
1373 -- Service code id is missing
1374 x_return_status := FND_API.G_RET_STS_ERROR ;
1375
1376 -- save message in fnd stack
1377 if (lc_stat_level >= lc_debug_level) then
1378 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1379 'Adding message CSD_SCD_SC_ID_MISSING to FND_MSG stack');
1380 end if;
1381 FND_MESSAGE.SET_NAME('CSD','CSD_API_MISSING_PARAM');
1382 FND_MESSAGE.SET_TOKEN('API_NAME',lc_api_name);
1383 FND_MESSAGE.SET_TOKEN('MISSING_PARAM','SERVICE_CODE_ID');
1384 FND_MSG_PUB.Add;
1385
1386 FND_MSG_PUB.Count_And_Get
1387 (p_count => x_msg_count,
1388 p_data => x_msg_data );
1389
1390 -- save message in debug log
1391 IF (lc_excep_level >= lc_debug_level) THEN
1392 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1393 'Service code id is missing');
1394 END IF;
1395
1396 WHEN CSD_SCD_DOMAIN_TYPE_MISSING THEN
1397 ROLLBACK TO Update_SC_Domain;
1398
1399 -- Service code domain type is missing
1400 x_return_status := FND_API.G_RET_STS_ERROR ;
1401
1402 -- save message in fnd stack
1403 if (lc_stat_level >= lc_debug_level) then
1404 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1405 'Adding message CSD_SCD_DOMAIN_TYPE_MISSING to FND_MSG stack');
1406 end if;
1407 FND_MESSAGE.SET_NAME('CSD','CSD_API_MISSING_PARAM');
1408 FND_MESSAGE.SET_TOKEN('API_NAME',lc_api_name);
1409 FND_MESSAGE.SET_TOKEN('MISSING_PARAM','DOMAIN_TYPE_CODE');
1410 FND_MSG_PUB.Add;
1411
1412 FND_MSG_PUB.Count_And_Get
1413 (p_count => x_msg_count,
1414 p_data => x_msg_data );
1415
1416 -- save message in debug log
1417 IF (lc_excep_level >= lc_debug_level) THEN
1418 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1419 'Service code domain type is missing');
1420 END IF;
1421
1422 WHEN CSD_SCD_INVALID_ID THEN
1423 ROLLBACK TO Update_SC_Domain;
1424
1425 -- Service code domain id is invalid
1426 x_return_status := FND_API.G_RET_STS_ERROR ;
1427
1428 -- save message in fnd stack
1429 if (lc_stat_level >= lc_debug_level) then
1430 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1431 'Adding message CSD_SCD_INVALID_ID to FND_MSG stack');
1432 end if;
1433 FND_MESSAGE.SET_NAME('CSD','CSD_SCD_INVALID_ID');
1434 FND_MESSAGE.SET_TOKEN('SC_DOMAIN_ID',p_sc_domain_rec.sc_domain_id);
1435 FND_MSG_PUB.Add;
1436
1437 FND_MSG_PUB.Count_And_Get
1438 (p_count => x_msg_count,
1439 p_data => x_msg_data );
1440
1441 -- save message in debug log
1442 IF (lc_excep_level >= lc_debug_level) THEN
1443 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1444 'Service code domain id is invalid');
1445 END IF;
1446
1447 WHEN CSD_SCD_GET_OVN_ERROR THEN
1448 ROLLBACK TO Update_SC_Domain;
1449
1450 -- Service code domain get object version number error
1451 x_return_status := FND_API.G_RET_STS_ERROR ;
1452
1453 -- save message in fnd stack
1454 if (lc_stat_level >= lc_debug_level) then
1455 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1456 'Adding message CSD_SCD_GET_OVN_ERROR to FND_MSG stack');
1457 end if;
1458 FND_MESSAGE.SET_NAME('CSD','CSD_SCD_GET_OVN_ERROR');
1459 FND_MESSAGE.SET_TOKEN('SC_DOMAIN_ID',p_sc_domain_rec.sc_domain_id);
1460 FND_MSG_PUB.Add;
1461
1462 FND_MSG_PUB.Count_And_Get
1463 (p_count => x_msg_count,
1464 p_data => x_msg_data );
1465
1466 -- save message in debug log
1467 IF (lc_excep_level >= lc_debug_level) THEN
1468 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1469 'Service code domain get object version number error');
1470 END IF;
1471
1472 WHEN CSD_SCD_OVN_MISMATCH THEN
1473 ROLLBACK TO Update_SC_Domain;
1474
1475 -- Service code domain object version number mismatch
1476 x_return_status := FND_API.G_RET_STS_ERROR ;
1477
1478 -- save message in fnd stack
1479 if (lc_stat_level >= lc_debug_level) then
1480 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1481 'Adding message CSD_SCD_OVN_MISMATCH to FND_MSG stack');
1482 end if;
1483 FND_MESSAGE.SET_NAME('CSD','CSD_SCD_OVN_MISMATCH');
1484 FND_MESSAGE.SET_TOKEN('SC_DOMAIN_ID',p_sc_domain_rec.sc_domain_id);
1485 FND_MSG_PUB.Add;
1486
1487 FND_MSG_PUB.Count_And_Get
1488 (p_count => x_msg_count,
1489 p_data => x_msg_data );
1490
1491 -- save message in debug log
1492 IF (lc_excep_level >= lc_debug_level) THEN
1493 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1494 'Service code domain object version number mismatch');
1495 END IF;
1496
1497 WHEN CSD_SCD_DOMAIN_EXISTS THEN
1498 ROLLBACK TO Update_SC_Domain;
1499
1500 -- Service code domain already exists
1501 x_return_status := FND_API.G_RET_STS_ERROR ;
1502
1503 -- save message in fnd stack
1504 if (lc_stat_level >= lc_debug_level) then
1505 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1506 'Adding message CSD_SCD_DOMAIN_EXISTS to FND_MSG stack');
1507 end if;
1508 FND_MESSAGE.SET_NAME('CSD','CSD_SCD_DOMAIN_EXISTS');
1509 FND_MESSAGE.SET_TOKEN('SERVICE_CODE_ID',p_sc_domain_rec.service_code_id);
1510 FND_MSG_PUB.Add;
1511
1512 FND_MSG_PUB.Count_And_Get
1513 (p_count => x_msg_count,
1514 p_data => x_msg_data );
1515
1516 -- save message in debug log
1517 IF (lc_excep_level >= lc_debug_level) THEN
1518 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1519 'Service code domain already exists');
1520 END IF;
1521
1522 WHEN CSD_SCD_GET_ITEM_ERROR THEN
1523 ROLLBACK TO Update_SC_Domain;
1524
1525 -- Service code domain get item error
1526 x_return_status := FND_API.G_RET_STS_ERROR ;
1527
1528 -- save message in fnd stack
1529 if (lc_stat_level >= lc_debug_level) then
1530 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1531 'Adding message CSD_SCD_GET_ITEM_ERROR to FND_MSG stack');
1532 end if;
1533 FND_MESSAGE.SET_NAME('CSD','CSD_SCD_GET_ITEM_ERROR');
1534 FND_MESSAGE.SET_TOKEN('SC_DOMAIN_ID',p_sc_domain_rec.sc_domain_id);
1535 FND_MSG_PUB.Add;
1536
1537 FND_MSG_PUB.Count_And_Get
1538 (p_count => x_msg_count,
1539 p_data => x_msg_data );
1540
1541 -- save message in debug log
1542 IF (lc_excep_level >= lc_debug_level) THEN
1543 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1544 'Service code domain get item error');
1545 END IF;
1546
1547 WHEN CSD_SCD_INVALID_ITEM THEN
1548 ROLLBACK TO Update_SC_Domain;
1549
1550 -- Service code domain item is invalid
1551 x_return_status := FND_API.G_RET_STS_ERROR ;
1552
1553 -- save message in fnd stack
1554 if (lc_stat_level >= lc_debug_level) then
1555 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1556 'Adding message CSD_SCD_INVALID_ITEM to FND_MSG stack');
1557 end if;
1558 FND_MESSAGE.SET_NAME('CSD','CSD_SCD_INVALID_ITEM');
1559 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID',p_sc_domain_rec.inventory_item_id);
1560 FND_MSG_PUB.Add;
1561
1562 FND_MSG_PUB.Count_And_Get
1563 (p_count => x_msg_count,
1564 p_data => x_msg_data );
1565
1566 -- save message in debug log
1567 IF (lc_excep_level >= lc_debug_level) THEN
1568 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1569 'Service code domain item is invalid');
1570 END IF;
1571
1572 WHEN CSD_SCD_GET_CAT_SET_ERROR THEN
1573 ROLLBACK TO Update_SC_Domain;
1574
1575 -- Service code domain get category set error
1576 x_return_status := FND_API.G_RET_STS_ERROR ;
1577
1578 -- save message in fnd stack
1579 if (lc_stat_level >= lc_debug_level) then
1580 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1581 'Adding message CSD_SCD_GET_CAT_SET_ERROR to FND_MSG stack');
1582 end if;
1583 FND_MESSAGE.SET_NAME('CSD','CSD_SCD_GET_CAT_SET_ERROR');
1584 FND_MESSAGE.SET_TOKEN('SC_DOMAIN_ID',p_sc_domain_rec.sc_domain_id);
1585 FND_MSG_PUB.Add;
1586
1587 FND_MSG_PUB.Count_And_Get
1588 (p_count => x_msg_count,
1589 p_data => x_msg_data );
1590
1591 -- save message in debug log
1592 IF (lc_excep_level >= lc_debug_level) THEN
1593 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1594 'Service code domain get category set error');
1595 END IF;
1596
1597 WHEN CSD_SCD_INVALID_CAT_SET THEN
1598 ROLLBACK TO Update_SC_Domain;
1599
1600 -- Service code domain category set is invalid
1601 x_return_status := FND_API.G_RET_STS_ERROR ;
1602
1603 -- save message in fnd stack
1604 if (lc_stat_level >= lc_debug_level) then
1605 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1606 'Adding message CSD_SCD_INVALID_CAT_SET to FND_MSG stack');
1607 end if;
1608 FND_MESSAGE.SET_NAME('CSD','CSD_SCD_INVALID_CAT_SET');
1609 FND_MESSAGE.SET_TOKEN('CATEGORY_SET_ID',p_sc_domain_rec.category_set_id);
1610 FND_MSG_PUB.Add;
1611
1612 FND_MSG_PUB.Count_And_Get
1613 (p_count => x_msg_count,
1614 p_data => x_msg_data );
1615
1616 -- save message in debug log
1617 IF (lc_excep_level >= lc_debug_level) THEN
1618 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1619 'Service code domain category set is invalid');
1620 END IF;
1621
1622 WHEN CSD_SCD_GET_CATSET_FLAG_ERROR THEN
1623 ROLLBACK TO Update_SC_Domain;
1624
1625 -- Service code domain category set validate flag is invalid
1626 x_return_status := FND_API.G_RET_STS_ERROR ;
1627
1628 -- save message in fnd stack
1629 if (lc_stat_level >= lc_debug_level) then
1630 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1631 'Adding message CSD_SCD_GET_CATSET_FLAG_ERROR to FND_MSG stack');
1632 end if;
1633 FND_MESSAGE.SET_NAME('CSD','CSD_SCD_GET_CATSET_FLAG_ERROR');
1634 FND_MESSAGE.SET_TOKEN('CATEGORY_SET_ID',p_sc_domain_rec.category_set_id);
1635 FND_MSG_PUB.Add;
1636
1637 FND_MSG_PUB.Count_And_Get
1638 (p_count => x_msg_count,
1639 p_data => x_msg_data );
1640
1641 -- save message in debug log
1642 IF (lc_excep_level >= lc_debug_level) THEN
1643 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1644 'Service code domain category set validate flag is invalid');
1645 END IF;
1646
1647 WHEN CSD_SCD_GET_CATEGORY_ERROR THEN
1648 ROLLBACK TO Update_SC_Domain;
1649
1650 -- Service code domain get category error
1651 x_return_status := FND_API.G_RET_STS_ERROR ;
1652
1653 -- save message in fnd stack
1654 if (lc_stat_level >= lc_debug_level) then
1655 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1656 'Adding message CSD_SCD_GET_CATEGORY_ERROR to FND_MSG stack');
1657 end if;
1658 FND_MESSAGE.SET_NAME('CSD','CSD_SCD_GET_CATEGORY_ERROR');
1659 FND_MESSAGE.SET_TOKEN('SC_DOMAIN_ID',p_sc_domain_rec.sc_domain_id);
1660 FND_MSG_PUB.Add;
1661
1662 FND_MSG_PUB.Count_And_Get
1663 (p_count => x_msg_count,
1664 p_data => x_msg_data );
1665
1666 -- save message in debug log
1667 IF (lc_excep_level >= lc_debug_level) THEN
1668 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1669 'Service code domain get category error');
1670 END IF;
1671
1672 WHEN CSD_SCD_INVALID_CATEGORY THEN
1673 ROLLBACK TO Update_SC_Domain;
1674
1675 -- Service code domain category is invalid
1676 x_return_status := FND_API.G_RET_STS_ERROR ;
1677
1678 -- save message in fnd stack
1679 if (lc_stat_level >= lc_debug_level) then
1680 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1681 'Adding message CSD_SCD_INVALID_CATEGORY to FND_MSG stack');
1682 end if;
1683 FND_MESSAGE.SET_NAME('CSD','CSD_SCD_INVALID_CATEGORY');
1684 FND_MESSAGE.SET_TOKEN('CATEGORY_ID',p_sc_domain_rec.category_id);
1685 FND_MSG_PUB.Add;
1686
1687 FND_MSG_PUB.Count_And_Get
1688 (p_count => x_msg_count,
1689 p_data => x_msg_data );
1690
1691 -- save message in debug log
1692 IF (lc_excep_level >= lc_debug_level) THEN
1693 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1694 'Service code domain category is invalid');
1695 END IF;
1696
1697 WHEN FND_API.G_EXC_ERROR THEN
1698 ROLLBACK TO Update_SC_Domain;
1699
1700 x_return_status := FND_API.G_RET_STS_ERROR;
1701
1702 FND_MSG_PUB.Count_And_Get
1703 (p_count => x_msg_count,
1704 p_data => x_msg_data );
1705
1706 -- save message in debug log
1707 IF (lc_excep_level >= lc_debug_level) THEN
1708 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1709 'EXC_ERROR['||x_msg_data||']');
1710 END IF;
1711
1712 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1713 ROLLBACK TO Update_SC_Domain;
1714
1715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1716
1717 IF FND_MSG_PUB.Check_Msg_Level
1718 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1719 THEN
1720 if (lc_stat_level >= lc_debug_level) then
1721 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1722 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
1723 end if;
1724 FND_MSG_PUB.Add_Exc_Msg
1725 (G_PKG_NAME ,
1726 lc_api_name );
1727 END IF;
1728
1729 FND_MSG_PUB.Count_And_Get
1730 ( p_count => x_msg_count,
1731 p_data => x_msg_data );
1732
1733 -- save message in debug log
1734 IF (lc_excep_level >= lc_debug_level) THEN
1735 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1736 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
1737 END IF;
1738
1739 WHEN OTHERS THEN
1740 ROLLBACK TO Update_SC_Domain;
1741
1742 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1743
1744 -- save message in fnd stack
1745 IF FND_MSG_PUB.Check_Msg_Level
1746 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1747 THEN
1748 if (lc_stat_level >= lc_debug_level) then
1749 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1750 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
1751 end if;
1752 FND_MSG_PUB.Add_Exc_Msg
1753 (G_PKG_NAME ,
1754 lc_api_name );
1755 END IF;
1756
1757 FND_MSG_PUB.Count_And_Get
1758 (p_count => x_msg_count,
1759 p_data => x_msg_data );
1760
1761 -- save message in debug log
1762 IF (lc_excep_level >= lc_debug_level) THEN
1763 -- create a seeded message
1764 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1765 'SQL Message['||sqlerrm||']' );
1766 END IF;
1767
1768 END Update_SC_Domain;
1769
1770 /*--------------------------------------------------*/
1771 /* procedure name: Delete_SC_Domain */
1772 /* description : procedure used to delete */
1773 /* sc domain */
1774 /* */
1775 /*--------------------------------------------------*/
1776 PROCEDURE Delete_SC_Domain
1777 (
1778 p_api_version IN NUMBER,
1779 p_commit IN VARCHAR2,
1780 p_init_msg_list IN VARCHAR2,
1781 p_validation_level IN NUMBER,
1782 x_return_status OUT NOCOPY VARCHAR2,
1783 x_msg_count OUT NOCOPY NUMBER,
1784 x_msg_data OUT NOCOPY VARCHAR2,
1785 p_sc_domain_id IN NUMBER
1786 ) IS
1787
1788 -- CONSTANTS --
1789 lc_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1790 lc_stat_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
1791 lc_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
1792 lc_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
1793 lc_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
1794 lc_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
1795 lc_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
1796 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_sc_domains_pvt.delete_sc_domain';
1797 lc_api_name CONSTANT VARCHAR2(30) := 'Delete_SC_Domain';
1798 lc_api_version CONSTANT NUMBER := 1.0;
1799
1800 -- VARIABLES --
1801 l_msg_count NUMBER;
1802 l_msg_data VARCHAR2(100);
1803 l_msg_index NUMBER;
1804
1805 BEGIN
1806 -- Standard Start of API savepoint
1807 SAVEPOINT Delete_SC_Domain;
1808
1809 -- Standard call to check for call compatibility.
1810 IF NOT FND_API.Compatible_API_Call (lc_api_version,
1811 p_api_version,
1812 lc_api_name ,
1813 G_PKG_NAME )
1814 THEN
1815 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1816 END IF;
1817
1818 -- Initialize message list if p_init_msg_list is set to TRUE.
1819 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1820 FND_MSG_PUB.initialize;
1821 END IF;
1822
1823 IF (lc_proc_level >= lc_debug_level) THEN
1824 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
1825 'Entered Delete_SC_Domain');
1826 END IF;
1827
1828 -- Initialize API return status to success
1829 x_return_status := FND_API.G_RET_STS_SUCCESS;
1830
1831 -- Api body starts
1832
1833 -- Check the required parameters
1834 if (lc_proc_level >= lc_debug_level) then
1835 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1836 'Checking required parameter');
1837 end if;
1838
1839 -- Check the required parameter
1840 CSD_PROCESS_UTIL.Check_Reqd_Param
1841 ( p_param_value => p_sc_domain_id,
1842 p_param_name => 'SC_DOMAIN_ID',
1843 p_api_name => lc_api_name);
1844
1845 -- Delete row
1846 if (lc_proc_level >= lc_debug_level) then
1847 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1848 'Calling Delete_Row table handler');
1849 end if;
1850
1851 BEGIN
1852
1853 -- Delete the service code domain
1854 CSD_SC_DOMAINS_PKG.Delete_Row
1855 (p_sc_domain_id => p_sc_domain_id );
1856
1857 EXCEPTION
1858 WHEN OTHERS THEN
1859 IF ( lc_excep_level >= lc_debug_level) THEN
1860 FND_LOG.STRING(lc_excep_level,lc_mod_name,'Others exception in CSD_SC_DOMAINS_PKG.Delete_Row Call :'||SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1,255));
1861 END IF;
1862 x_return_status := FND_API.G_RET_STS_ERROR;
1863 END;
1864
1865 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1866 RAISE FND_API.G_EXC_ERROR;
1867 END IF;
1868
1869 if (lc_proc_level >= lc_debug_level) then
1870 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1871 'Returned from Delete_Row table handler');
1872 end if;
1873
1874 -- Api body ends here
1875
1876 -- Standard check of p_commit.
1877 IF FND_API.To_Boolean( p_commit ) THEN
1878 COMMIT WORK;
1879 END IF;
1880
1881 -- Standard call to get message count and IF count is get message info.
1882 FND_MSG_PUB.Count_And_Get
1883 (p_count => x_msg_count,
1884 p_data => x_msg_data );
1885
1886 IF (lc_proc_level >= lc_debug_level) THEN
1887 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
1888 'Leaving Delete_SC_Domain');
1889 END IF;
1890
1891 EXCEPTION
1892
1893 WHEN FND_API.G_EXC_ERROR THEN
1894 ROLLBACK TO Delete_SC_Domain;
1895
1896 x_return_status := FND_API.G_RET_STS_ERROR;
1897
1898 FND_MSG_PUB.Count_And_Get
1899 (p_count => x_msg_count,
1900 p_data => x_msg_data );
1901
1902 -- save message in debug log
1903 IF (lc_excep_level >= lc_debug_level) THEN
1904 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1905 'EXC_ERROR['||x_msg_data||']');
1906 END IF;
1907
1908 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1909 ROLLBACK TO Delete_SC_Domain;
1910
1911 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1912
1913 IF FND_MSG_PUB.Check_Msg_Level
1914 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1915 THEN
1916 if (lc_stat_level >= lc_debug_level) then
1917 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1918 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
1919 end if;
1920 FND_MSG_PUB.Add_Exc_Msg
1921 (G_PKG_NAME ,
1922 lc_api_name );
1923 END IF;
1924
1925 FND_MSG_PUB.Count_And_Get
1926 ( p_count => x_msg_count,
1927 p_data => x_msg_data );
1928
1929 -- save message in debug log
1930 IF (lc_excep_level >= lc_debug_level) THEN
1931 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1932 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
1933 END IF;
1934
1935 WHEN OTHERS THEN
1936 ROLLBACK TO Delete_SC_Domain;
1937
1938 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1939
1940 -- save message in fnd stack
1941 IF FND_MSG_PUB.Check_Msg_Level
1942 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1943 THEN
1944 if (lc_stat_level >= lc_debug_level) then
1945 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1946 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
1947 end if;
1948 FND_MSG_PUB.Add_Exc_Msg
1949 (G_PKG_NAME ,
1950 lc_api_name );
1951 END IF;
1952
1953 FND_MSG_PUB.Count_And_Get
1954 (p_count => x_msg_count,
1955 p_data => x_msg_data );
1956
1957 -- save message in debug log
1958 IF (lc_excep_level >= lc_debug_level) THEN
1959 -- create a seeded message
1960 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1961 'SQL Message['||sqlerrm||']' );
1962 END IF;
1963
1964 END Delete_SC_Domain;
1965
1966 /*--------------------------------------------------*/
1967 /* procedure name: Lock_SC_Domain */
1968 /* description : procedure used to lock */
1969 /* sc domain */
1970 /* */
1971 /*--------------------------------------------------*/
1972 PROCEDURE Lock_SC_Domain
1973 (
1974 p_api_version IN NUMBER,
1975 p_commit IN VARCHAR2,
1976 p_init_msg_list IN VARCHAR2,
1977 p_validation_level IN NUMBER,
1978 x_return_status OUT NOCOPY VARCHAR2,
1979 x_msg_count OUT NOCOPY NUMBER,
1980 x_msg_data OUT NOCOPY VARCHAR2,
1981 p_sc_domain_rec IN SC_DOMAIN_REC_TYPE
1982 ) IS
1983
1984 -- CONSTANTS --
1985 lc_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1986 lc_stat_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
1987 lc_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
1988 lc_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
1989 lc_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
1990 lc_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
1991 lc_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
1992 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_sc_domains_pvt.lock_sc_domain';
1993 lc_api_name CONSTANT VARCHAR2(30) := 'Lock_SC_Domain';
1994 lc_api_version CONSTANT NUMBER := 1.0;
1995
1996 -- VARIABLES --
1997 l_msg_count NUMBER;
1998 l_msg_data VARCHAR2(100);
1999 l_msg_index NUMBER;
2000
2001 BEGIN
2002 -- Standard Start of API savepoint
2003 SAVEPOINT Lock_SC_Domain;
2004
2005 -- Standard call to check for call compatibility.
2006 IF NOT FND_API.Compatible_API_Call (lc_api_version,
2007 p_api_version,
2008 lc_api_name ,
2009 G_PKG_NAME )
2010 THEN
2011 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2012 END IF;
2013
2014 -- Initialize message list if p_init_msg_list is set to TRUE.
2015 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2016 FND_MSG_PUB.initialize;
2017 END IF;
2018
2019 IF (lc_proc_level >= lc_debug_level) THEN
2020 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
2021 'Entered Lock_SC_Domain');
2022 END IF;
2023
2024 -- Initialize API return status to success
2025 x_return_status := FND_API.G_RET_STS_SUCCESS;
2026
2027 -- Api body starts
2028
2029 -- Check the required parameters
2030 if (lc_proc_level >= lc_debug_level) then
2031 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2032 'Checking required parameters');
2033 end if;
2034
2035 -- Check the required parameter
2036 CSD_PROCESS_UTIL.Check_Reqd_Param
2037 ( p_param_value => p_sc_domain_rec.sc_domain_id,
2038 p_param_name => 'SC_DOMAIN_ID',
2039 p_api_name => lc_api_name);
2040
2041 -- Check the required parameter
2042 CSD_PROCESS_UTIL.Check_Reqd_Param
2043 ( p_param_value => p_sc_domain_rec.object_version_number,
2044 p_param_name => 'OBJECT_VERSION_NUMBER',
2045 p_api_name => lc_api_name);
2046
2047 -- Lock row
2048 if (lc_proc_level >= lc_debug_level) then
2049 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2050 'Calling Lock_Row table handler');
2051 end if;
2052
2053 BEGIN
2054
2055 -- Lock the sc domain
2056 CSD_SC_DOMAINS_PKG.Lock_Row
2057 (p_sc_domain_id => p_sc_domain_rec.sc_domain_id,
2058 p_object_version_number => p_sc_domain_rec.object_version_number
2059
2060 --commented out the rest of the record
2061 /*,
2062 p_service_code_id => p_sc_domain_rec.service_code_id,
2063 p_inventory_item_id => p_sc_domain_rec.inventory_item_id,
2064 p_category_id => p_sc_domain_rec.category_id,
2065 p_category_set_id => p_sc_domain_rec.category_set_id,
2066 p_created_by => FND_GLOBAL.USER_ID,
2067 p_creation_date => SYSDATE,
2068 p_last_updated_by => FND_GLOBAL.USER_ID,
2069 p_last_update_date => SYSDATE,
2070 p_last_update_login => FND_GLOBAL.LOGIN_ID,
2071 p_domain_type_code => p_sc_domain_rec.domain_type_code,
2072 p_attribute_category => p_sc_domain_rec.attribute_category,
2073 p_attribute1 => p_sc_domain_rec.attribute1,
2074 p_attribute2 => p_sc_domain_rec.attribute2,
2075 p_attribute3 => p_sc_domain_rec.attribute3,
2076 p_attribute4 => p_sc_domain_rec.attribute4,
2077 p_attribute5 => p_sc_domain_rec.attribute5,
2078 p_attribute6 => p_sc_domain_rec.attribute6,
2079 p_attribute7 => p_sc_domain_rec.attribute7,
2080 p_attribute8 => p_sc_domain_rec.attribute8,
2081 p_attribute9 => p_sc_domain_rec.attribute9,
2082 p_attribute10 => p_sc_domain_rec.attribute10,
2083 p_attribute11 => p_sc_domain_rec.attribute11,
2084 p_attribute12 => p_sc_domain_rec.attribute12,
2085 p_attribute13 => p_sc_domain_rec.attribute13,
2086 p_attribute14 => p_sc_domain_rec.attribute14,
2087 p_attribute15 => p_sc_domain_rec.attribute15
2088 */
2089 --
2090 );
2091
2092 EXCEPTION
2093 WHEN OTHERS THEN
2094 IF ( lc_excep_level >= lc_debug_level) THEN
2095 FND_LOG.STRING(lc_excep_level,lc_mod_name,'Others exception in CSD_SC_DOMAINS_PKG.Lock_Row Call :'||SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1,255));
2096 END IF;
2097 x_return_status := FND_API.G_RET_STS_ERROR;
2098 END;
2099
2100 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2101 RAISE FND_API.G_EXC_ERROR;
2102 END IF;
2103
2104 if (lc_proc_level >= lc_debug_level) then
2105 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2106 'Returned from Lock_Row table handler');
2107 end if;
2108
2109 -- Api body ends here
2110
2111 -- Standard check of p_commit.
2112 IF FND_API.To_Boolean( p_commit ) THEN
2113 COMMIT WORK;
2114 END IF;
2115
2116 -- Standard call to get message count and IF count is get message info.
2117 FND_MSG_PUB.Count_And_Get
2118 (p_count => x_msg_count,
2119 p_data => x_msg_data );
2120
2121 IF (lc_proc_level >= lc_debug_level) THEN
2122 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
2123 'Leaving Lock_SC_Domain');
2124 END IF;
2125
2126 EXCEPTION
2127
2128 WHEN FND_API.G_EXC_ERROR THEN
2129 ROLLBACK TO Lock_SC_Domain;
2130
2131 x_return_status := FND_API.G_RET_STS_ERROR;
2132
2133 FND_MSG_PUB.Count_And_Get
2134 (p_count => x_msg_count,
2135 p_data => x_msg_data );
2136
2137 -- save message in debug log
2138 IF (lc_excep_level >= lc_debug_level) THEN
2139 FND_LOG.STRING(lc_excep_level, lc_mod_name,
2140 'EXC_ERROR['||x_msg_data||']');
2141 END IF;
2142
2143 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2144 ROLLBACK TO Lock_SC_Domain;
2145
2146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2147
2148 IF FND_MSG_PUB.Check_Msg_Level
2149 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2150 THEN
2151 if (lc_stat_level >= lc_debug_level) then
2152 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2153 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
2154 end if;
2155 FND_MSG_PUB.Add_Exc_Msg
2156 (G_PKG_NAME ,
2157 lc_api_name );
2158 END IF;
2159
2160 FND_MSG_PUB.Count_And_Get
2161 ( p_count => x_msg_count,
2162 p_data => x_msg_data );
2163
2164 -- save message in debug log
2165 IF (lc_excep_level >= lc_debug_level) THEN
2166 FND_LOG.STRING(lc_excep_level, lc_mod_name,
2167 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
2168 END IF;
2169
2170 WHEN OTHERS THEN
2171 ROLLBACK TO Lock_SC_Domain;
2172
2173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2174
2175 -- save message in fnd stack
2176 IF FND_MSG_PUB.Check_Msg_Level
2177 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2178 THEN
2179 if (lc_stat_level >= lc_debug_level) then
2180 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2181 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
2182 end if;
2183 FND_MSG_PUB.Add_Exc_Msg
2184 (G_PKG_NAME ,
2185 lc_api_name );
2186 END IF;
2187
2188 FND_MSG_PUB.Count_And_Get
2189 (p_count => x_msg_count,
2190 p_data => x_msg_data );
2191
2192 -- save message in debug log
2193 IF (lc_excep_level >= lc_debug_level) THEN
2194 -- create a seeded message
2195 FND_LOG.STRING(lc_excep_level, lc_mod_name,
2196 'SQL Message['||sqlerrm||']' );
2197 END IF;
2198
2199 END Lock_SC_Domain;
2200
2201 End CSD_SC_DOMAINS_PVT;
2202
[Home] [Help]