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