[Home] [Help]
PACKAGE BODY: APPS.PV_ATTRIBUTE_PVT
Source
1 PACKAGE BODY PV_Attribute_PVT as
2 /* $Header: pvxvatsb.pls 120.4 2008/01/04 05:13:33 abnagapp ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_Attribute_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_Attribute_PVT';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxvatsb.pls';
19
20 G_USER_ID NUMBER := NVL(FND_GLOBAL.USER_ID, -1);
21 G_LOGIN_ID NUMBER := NVL(FND_GLOBAL.CONC_LOGIN_ID, -1);
22
23 -- Hint: Primary key needs to be returned.
24 PROCEDURE Create_Attribute(
25 p_api_version_number IN NUMBER
26 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
27 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
28 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
29
30 ,x_return_status OUT NOCOPY VARCHAR2
31 ,x_msg_count OUT NOCOPY NUMBER
32 ,x_msg_data OUT NOCOPY VARCHAR2
33
34 ,p_attribute_rec IN attribute_rec_type := g_miss_attribute_rec
35 ,x_attribute_id OUT NOCOPY NUMBER
36 )
37
38 IS
39 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Attribute';
40 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
41 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
42 l_return_status_full VARCHAR2(1);
43 l_object_version_number NUMBER := 1;
44 l_org_id NUMBER := FND_API.G_MISS_NUM;
45 l_ATTRIBUTE_ID NUMBER;
46 l_dummy NUMBER;
47 l_attribute_rec attribute_rec_type := p_attribute_rec;
48 l_meaning VARCHAR2(80);
49
50 CURSOR c_id IS
51 SELECT PV_ATTRIBUTES_B_s.NEXTVAL
52 FROM dual;
53
54 CURSOR c_id_exists (l_id IN NUMBER) IS
55 SELECT 1
56 FROM PV_ATTRIBUTES_B
57 WHERE ATTRIBUTE_ID = l_id;
58
59 BEGIN
60 -- Standard Start of API savepoint
61 SAVEPOINT CREATE_Attribute_PVT;
62
63 -- Standard call to check for call compatibility.
64 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
65 p_api_version_number,
66 l_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 )
74 THEN
75 FND_MSG_PUB.initialize;
76 END IF;
77
78 -- Debug Message
79 --DBMS_output.put_line('Private API: ' || L_FULL_NAME || ' start');
80 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
81 PVX_UTILITY_PVT.debug_message('Private API: ' || L_FULL_NAME || ' start');
82 end if;
83
84
85 -- Initialize API return status to SUCCESS
86 x_return_status := FND_API.G_RET_STS_SUCCESS;
87
88 -- Local variable initialization
89
90 IF p_attribute_rec.ATTRIBUTE_ID IS NULL OR p_attribute_rec.ATTRIBUTE_ID = FND_API.g_miss_num THEN
91 LOOP
92 l_dummy := NULL;
93 OPEN c_id;
94 FETCH c_id INTO l_ATTRIBUTE_ID;
95 CLOSE c_id;
96
97 OPEN c_id_exists(l_ATTRIBUTE_ID);
98 FETCH c_id_exists INTO l_dummy;
99 CLOSE c_id_exists;
100 EXIT WHEN l_dummy IS NULL;
101 END LOOP;
102
103 ELSE
104 l_ATTRIBUTE_ID := p_attribute_rec.ATTRIBUTE_ID;
105 END IF;
106
107
108 -- =========================================================================
109 -- Validate Environment
110 -- =========================================================================
111
112 IF FND_GLOBAL.User_Id IS NULL
113 THEN
114 FND_MESSAGE.set_name('PV', 'PV_API_USER_PROFILE_MISSING');
115 FND_MSG_PUB.add;
116 RAISE FND_API.G_EXC_ERROR;
117 END IF;
118
119 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
120 THEN
121 -- Debug message
122 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
123 PVX_UTILITY_PVT.debug_message('Private API: Validate_Attribute');
124 END IF;
125
126 --DBMS_output.put_line('Before validate_attribute');
127
128 -- Populate the default required items
129 l_attribute_rec.attribute_id := l_attribute_id;
130 l_attribute_rec.last_update_date := SYSDATE;
131 l_attribute_rec.last_updated_by := G_USER_ID;
132 l_attribute_rec.creation_date := SYSDATE;
133 l_attribute_rec.created_by := G_USER_ID;
134 l_attribute_rec.last_update_login := G_LOGIN_ID;
135 l_attribute_rec.object_version_number := l_object_version_number;
136
137 -- Invoke validation procedures
138 Validate_attribute(
139 p_api_version_number => 1.0,
140 p_init_msg_list => FND_API.G_FALSE,
141 p_validation_level => p_validation_level,
142 p_validation_mode => JTF_PLSQL_API.g_create,
143 p_attribute_rec => l_attribute_rec,
144 x_return_status => x_return_status,
145 x_msg_count => x_msg_count,
146 x_msg_data => x_msg_data);
147 END IF;
148
149 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
150 RAISE FND_API.G_EXC_ERROR;
151 END IF;
152
153 --check for attribute types
154 IF((l_attribute_rec.attribute_type = 'TEXT' and l_attribute_rec.display_style not in ('CURRENCY','DATE','NUMBER','STRING','NULL_CHECK','PERCENTAGE')) OR
155 (l_attribute_rec.attribute_type = 'DROPDOWN' and l_attribute_rec.display_style not in ('CHECK','MULTI','RADIO','SINGLE','EXTERNAL_LOV','PERCENTAGE')) OR
156 (l_attribute_rec.attribute_type = 'FUNCTION' and l_attribute_rec.display_style not in ('NUMBER','STRING','LOV','DATE','CURRENCY','PERCENTAGE'))
157 ) THEN
158
159 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
160 FND_MESSAGE.Set_Name('PV', 'PV_ATTR_TYPE_STYLE_NOT_CREATE');
161
162 FOR x IN (select meaning from pv_lookups
163 where lookup_type = 'PV_ATTRIBUTE_TYPE'
164 and lookup_code = l_attribute_rec.attribute_type
165 ) LOOP
166 l_meaning := x.meaning;
167 END LOOP;
168 Fnd_Message.set_token('ATTRIBUTE_TYPE',l_meaning);
169
170 FOR x IN (select meaning from pv_lookups
171 where lookup_type = 'PV_ATTR_DISPLAY_STYLE'
172 and lookup_code = l_attribute_rec.display_style
173 ) LOOP
174 l_meaning := x.meaning;
175 END LOOP;
176 FND_MESSAGE.Set_Token('DISPLAY_STYLE',l_meaning );
177 FND_MSG_PUB.Add;
178 END IF;
179
180 RAISE FND_API.G_EXC_ERROR;
181
182 END IF;
183
184
185 --end of check for attributes tyupes and display styles
186
187
188
189
190 -- Debug Message
191 PVX_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
192
193 -- Invoke table handler(PV_ATTRIBUTE_PKG.Insert_Row)
194 PV_ATTRIBUTE_PKG.Insert_Row(
195 px_attribute_id => l_attribute_rec.attribute_id,
196 p_last_update_date => l_attribute_rec.last_update_date,
197 p_last_updated_by => l_attribute_rec.last_updated_by,
198 p_creation_date => l_attribute_rec.creation_date,
199 p_created_by => l_attribute_rec.created_by,
200 p_last_update_login => l_attribute_rec.last_update_login,
201 px_object_version_number => l_attribute_rec.object_version_number,
202 --p_security_group_id => l_attribute_rec.security_group_id,
203 p_enabled_flag => l_attribute_rec.enabled_flag,
204 p_attribute_type => l_attribute_rec.attribute_type,
205 p_attribute_category => l_attribute_rec.attribute_category,
206 p_seeded_flag => l_attribute_rec.seeded_flag,
207 p_lov_function_name => l_attribute_rec.lov_function_name,
208 p_return_type => l_attribute_rec.return_type,
209 p_max_value_flag => l_attribute_rec.max_value_flag,
210 p_name => l_attribute_rec.name,
211 p_description => l_attribute_rec.description,
212 p_short_name => l_attribute_rec.short_name,
213
214 p_display_style => l_attribute_rec.display_style,
215 p_character_width => l_attribute_rec.character_width,
216 p_decimal_points => l_attribute_rec.decimal_points,
217 p_no_of_lines => l_attribute_rec.no_of_lines,
218 p_expose_to_partner_flag => l_attribute_rec.expose_to_partner_flag,
219 p_value_extn_return_type => l_attribute_rec.value_extn_return_type,
220 p_enable_matching_flag => l_attribute_rec.enable_matching_flag,
221 p_performance_flag => l_attribute_rec.performance_flag,
222 p_additive_flag => l_attribute_rec.additive_flag,
223 p_sequence_number => l_attribute_rec.sequence_number
224
225 );
226
227
228 x_attribute_id := l_attribute_id;
229
230 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
231 RAISE FND_API.G_EXC_ERROR;
232 END IF;
233 --
234 -- End of API body
235 --
236
237 -- Standard check for p_commit
238 IF FND_API.to_Boolean( p_commit )
239 THEN
240 COMMIT WORK;
241 END IF;
242
243
244 -- Debug Message
245 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
246 PVX_UTILITY_PVT.debug_message('Private API: ' || L_FULL_NAME || 'end');
247 END IF;
248
249 -- Standard call to get message count and if count is 1, get message info.
250 FND_MSG_PUB.Count_And_Get
251 (p_count => x_msg_count,
252 p_data => x_msg_data
253 );
254 EXCEPTION
255
256 /*
257 WHEN PVX_UTILITY_PVT.resource_locked THEN
258 x_return_status := FND_API.g_ret_sts_error;
259 PVX_UTILITY_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
260 */
261
262 WHEN FND_API.G_EXC_ERROR THEN
263 ROLLBACK TO CREATE_Attribute_PVT;
264 x_return_status := FND_API.G_RET_STS_ERROR;
265 -- Standard call to get message count and if count=1, get the message
266 FND_MSG_PUB.Count_And_Get (
267 p_encoded => FND_API.G_FALSE,
268 p_count => x_msg_count,
269 p_data => x_msg_data
270 );
271
272 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
273 ROLLBACK TO CREATE_Attribute_PVT;
274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
275 -- Standard call to get message count and if count=1, get the message
276 FND_MSG_PUB.Count_And_Get (
277 p_encoded => FND_API.G_FALSE,
278 p_count => x_msg_count,
279 p_data => x_msg_data
280 );
281
282 WHEN OTHERS THEN
283 ROLLBACK TO CREATE_Attribute_PVT;
284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
285 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
286 THEN
287 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
288 END IF;
289 -- Standard call to get message count and if count=1, get the message
290 FND_MSG_PUB.Count_And_Get (
291 p_encoded => FND_API.G_FALSE,
292 p_count => x_msg_count,
293 p_data => x_msg_data
294 );
295 End Create_Attribute;
296
297
298 PROCEDURE Update_Attribute(
299 p_api_version_number IN NUMBER
300 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
301 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
302 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
303
304 ,x_return_status OUT NOCOPY VARCHAR2
305 ,x_msg_count OUT NOCOPY NUMBER
306 ,x_msg_data OUT NOCOPY VARCHAR2
307
308 ,p_attribute_rec IN attribute_rec_type
309 ,x_object_version_number OUT NOCOPY NUMBER
310
311 )
312
313 IS
314
315 CURSOR c_get_attribute(cv_attribute_id NUMBER) IS
316 SELECT *
317 FROM PV_ATTRIBUTES_B
318 where attribute_id = cv_attribute_id;
319
320 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Attribute';
321 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
322 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
323 -- Local Variables
324 l_object_version_number NUMBER;
325 l_ATTRIBUTE_ID NUMBER;
326 l_ref_attribute_rec c_get_Attribute%ROWTYPE ;
327 l_tar_attribute_rec PV_Attribute_PVT.attribute_rec_type := P_attribute_rec;
328 l_rowid ROWID;
329
330 l_being_used_list VARCHAR2(30000);
331 l_delete_flag VARCHAR2(1):='Y';
332 l_meaning VARCHAR2(80);
333 l_sequence_number NUMBER;
334
335 cursor lc_check_rules (pc_attribute_id number) is
336 select distinct rules.process_rule_name
337 from pv_enty_select_criteria criteria,pv_process_rules_vl rules
338 where criteria.attribute_id= pc_attribute_id
339 and criteria.process_rule_id= rules.process_rule_id;
340
341 cursor lc_check_matching (pc_attribute_id number) is
342 select resp.RESPONSIBILITY_NAME
343 from pv_attrib_resp_mappings val, FND_RESPONSIBILITY_VL resp
344 where
345 val.attribute_id= pc_attribute_id and
346 resp.application_id = 691 and
347 val.entity_type = 'MANUAL_MATCHING' and
348 resp.RESPONSIBILITY_ID = val.responsibility_id;
349
350 BEGIN
351 -- Standard Start of API savepoint
352 SAVEPOINT UPDATE_Attribute_PVT;
353
354 -- Standard call to check for call compatibility.
355 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
356 p_api_version_number,
357 l_api_name,
358 G_PKG_NAME)
359 THEN
360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
361 END IF;
362
363 -- Initialize message list if p_init_msg_list is set to TRUE.
364 IF FND_API.to_Boolean( p_init_msg_list )
365 THEN
366 FND_MSG_PUB.initialize;
367 END IF;
368
369 -- Debug Message
370 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
371 PVX_UTILITY_PVT.debug_message('Private API: ' || L_FULL_NAME || 'start');
372 END IF;
373
374
375 -- Initialize API return status to SUCCESS
376 x_return_status := FND_API.G_RET_STS_SUCCESS;
377
378 -- Debug Message
379 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
380 PVX_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
381 END IF;
382
383
384
385 OPEN c_get_Attribute( l_tar_attribute_rec.attribute_id);
386
387 FETCH c_get_Attribute INTO l_ref_attribute_rec ;
388
389 IF ( c_get_Attribute%NOTFOUND) THEN
390 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
391 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_ENTITY');
392 FND_MESSAGE.set_token('MODE','Update');
393 FND_MESSAGE.set_token('ENTITY','Attribute');
394 FND_MESSAGE.set_token('ID',TO_CHAR(l_tar_attribute_rec.attribute_id));
395 FND_MSG_PUB.add;
396 END IF;
397 RAISE FND_API.G_EXC_ERROR;
398 END IF;
399
400 -- Debug Message
401 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
402 PVX_UTILITY_PVT.debug_message('Private API: - Close Cursor');
403 END IF;
404 CLOSE c_get_Attribute;
405
406
407
408
409
410
411 --check it is updatable or not by checkiong rules reference
412
413 if(l_ref_attribute_rec.enabled_flag = 'Y' and p_attribute_rec.enabled_flag= 'N') then
414
415 for x in lc_check_rules (pc_attribute_id =>p_attribute_rec.attribute_id)
416 loop
417 l_delete_flag := 'N' ;
418 l_being_used_list := l_being_used_list || ','|| x.process_rule_name ;
419 end loop;
420
421
422 if(l_delete_flag = 'N') then
423
424 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
425 FND_MESSAGE.Set_Name('PV', 'PV_ATTR_NOTDISABLED_RULE');
426 FND_MESSAGE.Set_Token('ATTRIBUTE_NAME',p_attribute_rec.name );
427 FND_MESSAGE.Set_Token('RULES_LIST',substr(l_being_used_list,2) );
428 FND_MSG_PUB.Add;
429 END IF;
430
431 RAISE FND_API.G_EXC_ERROR;
432
433 end if;
434
435 --check it is updatable or not by checkiong matching reference
436
437 for x in lc_check_matching (pc_attribute_id =>p_attribute_rec.attribute_id)
438 loop
439 l_delete_flag := 'N' ;
440 l_being_used_list := l_being_used_list || ','|| x.RESPONSIBILITY_NAME ;
441 end loop;
442
443
444 if(l_delete_flag = 'N') then
445
446 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
447 FND_MESSAGE.Set_Name('PV', 'PV_ATTR_NOTDISABLED_MATCHING');
448 FND_MESSAGE.Set_Token('ATTRIBUTE_NAME',p_attribute_rec.name );
449 FND_MESSAGE.Set_Token('RESPONSIBILITY_LIST',substr(l_being_used_list,2) );
450 FND_MSG_PUB.Add;
451 END IF;
452
453 RAISE FND_API.G_EXC_ERROR;
454
455 end if;
456 end if;
457
458 --check max_value_flag is updatable or not by checkiong rules reference
459 if(l_ref_attribute_rec.max_value_flag <> p_attribute_rec.max_value_flag) then
460
461 for x in lc_check_rules (pc_attribute_id =>p_attribute_rec.attribute_id)
462 loop
463 l_delete_flag := 'N' ;
464 l_being_used_list := l_being_used_list || ','|| x.process_rule_name ;
465 end loop;
466
467
468 if(l_delete_flag = 'N') then
469
470 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
471 FND_MESSAGE.Set_Name('PV', 'PV_VALUE_TYPE_REFERENCED_RULE');
472 FND_MESSAGE.Set_Token('ATTRIBUTE_NAME',p_attribute_rec.name );
473 FND_MESSAGE.Set_Token('RULES_LIST',substr(l_being_used_list,2) );
474 FND_MSG_PUB.Add;
475 END IF;
476
477 RAISE FND_API.G_EXC_ERROR;
478
479 end if;
480 end if;
481
482 --check ENABLE_MATCHING_FLAG is updatable or not by checkiong rules reference and manual matching reference
483 -- if(l_ref_attribute_rec.enable_matching_flag <> p_attribute_rec.enable_matching_flag) then
484 if(l_ref_attribute_rec.enable_matching_flag = 'Y' and p_attribute_rec.enable_matching_flag= 'N') then
485 for x in lc_check_rules (pc_attribute_id =>p_attribute_rec.attribute_id)
486 loop
487 l_delete_flag := 'N' ;
488 l_being_used_list := l_being_used_list || ','|| x.process_rule_name ;
489 end loop;
490
491
492 if(l_delete_flag = 'N') then
493
494 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
495 FND_MESSAGE.Set_Name('PV', 'PV_MATCHING_REFERENCED_RULE');
496 FND_MESSAGE.Set_Token('ATTRIBUTE_NAME',p_attribute_rec.name );
497 FND_MESSAGE.Set_Token('RULES_LIST',substr(l_being_used_list,2) );
498 FND_MSG_PUB.Add;
499 END IF;
500
501 RAISE FND_API.G_EXC_ERROR;
502
503 end if;
504
505 for x in lc_check_matching (pc_attribute_id =>p_attribute_rec.attribute_id)
506 loop
507 l_delete_flag := 'N' ;
508 l_being_used_list := l_being_used_list || ','|| x.RESPONSIBILITY_NAME ;
509 end loop;
510
511
512 if(l_delete_flag = 'N') then
513
514 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
515 FND_MESSAGE.Set_Name('PV', 'PV_MATCHING_NOTDISABLED');
516 FND_MESSAGE.Set_Token('ATTRIBUTE_NAME',p_attribute_rec.name );
517 FND_MESSAGE.Set_Token('RESPONSIBILITY_LIST',substr(l_being_used_list,2) );
518 FND_MSG_PUB.Add;
519 END IF;
520
521 RAISE FND_API.G_EXC_ERROR;
522
523 end if;
524
525 end if;
526
527 --for bug# 3397200, restricting partner type display style changes
528 --for bug 6723524, commenting the code as partner type cannot
529 --be changed from the ui itself. So this if block is of no use.
530
531 --if(p_attribute_rec.attribute_type is not null and
532 -- p_attribute_rec.display_style is not null and
533 -- p_attribute_rec.attribute_id = 3 and
534 -- l_ref_attribute_rec.display_style in ('SINGLE') and
535 -- p_attribute_rec.display_style in ('SINGLE')
536 -- )
537 -- THEN
538 -- IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
539
540 -- FND_MESSAGE.set_name('PV', 'PV_ATTR_NOT_CHANGE_STYLE');
541 -- FND_MESSAGE.set_token('NAME',p_attribute_rec.name);
542
543 -- FOR x IN (select meaning from pv_lookups
544 -- where lookup_type = 'PV_ATTRIBUTE_TYPE'
545 -- and lookup_code = p_attribute_rec.attribute_type
546 -- ) LOOP
547 -- l_meaning := x.meaning;
548 -- END LOOP;
549 -- Fnd_Message.set_token('TYPE',l_meaning);
550
551 -- FOR x IN (select meaning from pv_lookups
552 -- where lookup_type = 'PV_ATTR_DISPLAY_STYLE'
553 -- and lookup_code = l_ref_attribute_rec.display_style
554 -- ) LOOP
555 -- l_meaning := x.meaning;
556 -- END LOOP;
557 -- FND_MESSAGE.set_token('FROM',l_meaning);
558
559 -- FOR x IN (select meaning from pv_lookups
560 -- where lookup_type = 'PV_ATTR_DISPLAY_STYLE'
561 -- and lookup_code = p_attribute_rec.display_style
562 -- ) LOOP
563 -- l_meaning := x.meaning;
564 -- END LOOP;
565
566 -- FND_MESSAGE.set_token('TO',l_meaning);
567 -- FND_MSG_PUB.add;
568
569 -- END IF;
570 -- RAISE FND_API.G_EXC_ERROR;
571
572
573 -- end if;
574
575 -- end of change for bug# 6723524
576 -- end of change for bug# 3397200, restricting partner type display style changes
577
578
579 --Check whether conversion of display_styles is ok or not
580
581 if(p_attribute_rec.attribute_type is not null and p_attribute_rec.display_style is not null and
582 (
583 (p_attribute_rec.attribute_type in ('TEXT','FUNCTION') and l_ref_attribute_rec.display_style <> p_attribute_rec.display_style) or
584 (p_attribute_rec.attribute_type in ('DROPDOWN') and l_ref_attribute_rec.display_style in ('PERCENTAGE') and p_attribute_rec.display_style not in ('PERCENTAGE')) or
585 (p_attribute_rec.attribute_type in ('DROPDOWN') and l_ref_attribute_rec.display_style not in ('PERCENTAGE') and p_attribute_rec.display_style in ('PERCENTAGE')) or
586 (p_attribute_rec.attribute_type in ('DROPDOWN') and l_ref_attribute_rec.display_style in ('SINGLE','RADIO') and p_attribute_rec.display_style not in ('SINGLE','RADIO')) or
587 (p_attribute_rec.attribute_type in ('DROPDOWN') and l_ref_attribute_rec.display_style not in ('SINGLE','RADIO') and p_attribute_rec.display_style in ('SINGLE','RADIO')) or
588 (p_attribute_rec.attribute_type in ('DROPDOWN') and l_ref_attribute_rec.display_style in ('MULTI','CHECK') and p_attribute_rec.display_style not in ('MULTI','CHECK')) or
589 (p_attribute_rec.attribute_type in ('DROPDOWN') and l_ref_attribute_rec.display_style not in ('MULTI','CHECK') and p_attribute_rec.display_style in ('MULTI','CHECK'))
590 )
591
592
593 ) THEN
594
595 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
596
597 /*FND_MESSAGE.set_name('PV', 'PV_DEBUG_MESSAGE');
598 FND_MESSAGE.set_token('TEXT', 'Can not :' || p_attribute_rec.attribute_type || ':' ||
599 l_ref_attribute_rec.display_style || ':' ||p_attribute_rec.display_style ||':');
600 FND_MSG_PUB.add;
601 */
602
603 FND_MESSAGE.set_name('PV', 'PV_ATTR_NOT_CHANGE_STYLE');
604 FND_MESSAGE.set_token('NAME',p_attribute_rec.name);
605
606 FOR x IN (select meaning from pv_lookups
607 where lookup_type = 'PV_ATTRIBUTE_TYPE'
608 and lookup_code = p_attribute_rec.attribute_type
609 ) LOOP
610 l_meaning := x.meaning;
611 END LOOP;
612 Fnd_Message.set_token('TYPE',l_meaning);
613
614 FOR x IN (select meaning from pv_lookups
615 where lookup_type = 'PV_ATTR_DISPLAY_STYLE'
616 and lookup_code = l_ref_attribute_rec.display_style
617 ) LOOP
618 l_meaning := x.meaning;
619 END LOOP;
620 FND_MESSAGE.set_token('FROM',l_meaning);
621
622 FOR x IN (select meaning from pv_lookups
623 where lookup_type = 'PV_ATTR_DISPLAY_STYLE'
624 and lookup_code = p_attribute_rec.display_style
625 ) LOOP
626 l_meaning := x.meaning;
627 END LOOP;
628
629 FND_MESSAGE.set_token('TO',l_meaning);
630 FND_MSG_PUB.add;
631
632 END IF;
633 RAISE FND_API.G_EXC_ERROR;
634
635
636
637 END IF;
638
639 --for bug#5148569, while updatring attribute category, resetting the sequence_numbe.
640 if(l_ref_attribute_rec.attribute_category <> p_attribute_rec.attribute_category) then
641 l_sequence_number := null;
642 else
643 l_sequence_number := p_attribute_rec.sequence_number;
644 end if;
645
646
647
648 If (l_tar_attribute_rec.object_version_number is NULL or
649 l_tar_attribute_rec.object_version_number = FND_API.G_MISS_NUM ) Then
650
651 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
652 FND_MESSAGE.set_name('PV', 'PV_API_VERSION_MISSING');
653 FND_MESSAGE.set_token('COLUMN', TO_CHAR(l_tar_attribute_rec.last_update_date));
654 FND_MSG_PUB.add;
655 END IF;
656 RAISE FND_API.G_EXC_ERROR;
657 End if;
658 -- Check Whether record has been changed by someone else
659 If (l_tar_attribute_rec.object_version_number <> l_ref_attribute_rec.object_version_number) Then
660 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
661 FND_MESSAGE.set_name('PV', 'PV_API_RECORD_CHANGED');
662 FND_MESSAGE.set_token('VALUE','Attribute');
663 FND_MSG_PUB.add;
664 END IF;
665 RAISE FND_API.G_EXC_ERROR;
666 End if;
667 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
668 THEN
669 -- Debug message
670 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
671 PVX_UTILITY_PVT.debug_message('Private API: Validate_Attribute');
672 END IF;
673 --dbms_output.put_line('Before Validate_attribute');
674 -- Invoke validation procedures
675 Validate_attribute(
676 p_api_version_number => 1.0,
677 p_init_msg_list => FND_API.G_FALSE,
678 p_validation_level => p_validation_level,
679 p_validation_mode => JTF_PLSQL_API.g_update,
680 p_attribute_rec => p_attribute_rec,
681 x_return_status => x_return_status,
682 x_msg_count => x_msg_count,
683 x_msg_data => x_msg_data);
684 END IF;
685
686
687 --DBMS_output.put_line('Before update_row');
688
689 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
690 RAISE FND_API.G_EXC_ERROR;
691 END IF;
692
693 -- Debug Message
694 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
695 PVX_UTILITY_PVT.debug_message('Private API: '||l_full_name||' - Calling update table handler');
696 END IF;
697
698 --DBMS_output.put_line('Before update_row');
699
700 -- Invoke table handler(PV_ATTRIBUTE_PKG.Update_Row)
701 PV_ATTRIBUTE_PKG.Update_Row(
702 p_attribute_id => p_attribute_rec.attribute_id,
703 p_last_update_date => SYSDATE,
704 p_last_updated_by => G_USER_ID,
705 --p_creation_date => SYSDATE,
706 --p_created_by => G_USER_ID,
707 p_last_update_login => G_LOGIN_ID,
708 p_object_version_number => p_attribute_rec.object_version_number,
709 --p_security_group_id => p_attribute_rec.security_group_id,
710 p_enabled_flag => p_attribute_rec.enabled_flag,
711 p_attribute_type => p_attribute_rec.attribute_type,
712 p_attribute_category => p_attribute_rec.attribute_category,
713 p_seeded_flag => p_attribute_rec.seeded_flag,
714 p_lov_function_name => p_attribute_rec.lov_function_name,
715 p_return_type => p_attribute_rec.return_type,
716 p_max_value_flag => p_attribute_rec.max_value_flag,
717 p_name => p_attribute_rec.name,
718 p_description => p_attribute_rec.description,
719 p_short_name => p_attribute_rec.short_name,
720
721
722 p_display_style => p_attribute_rec.display_style,
723 p_character_width => p_attribute_rec.character_width,
724 p_decimal_points => p_attribute_rec.decimal_points,
725 p_no_of_lines => p_attribute_rec.no_of_lines,
726 p_expose_to_partner_flag => p_attribute_rec.expose_to_partner_flag,
727 p_value_extn_return_type => p_attribute_rec.value_extn_return_type,
728 p_enable_matching_flag => p_attribute_rec.enable_matching_flag,
729 p_performance_flag => p_attribute_rec.performance_flag,
730 p_additive_flag => p_attribute_rec.additive_flag,
731 p_sequence_number => l_sequence_number
732
733 );
734
735 x_object_version_number := p_attribute_rec.object_version_number+1;
736
737 --
738 -- End of API body.
739 --
740
741 -- Standard check for p_commit
742 IF FND_API.to_Boolean( p_commit )
743 THEN
744 COMMIT WORK;
745 END IF;
746
747
748 -- Debug Message
749 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
750 PVX_UTILITY_PVT.debug_message('Private API: ' || L_FULL_NAME || 'end');
751 END IF;
752
753 -- Standard call to get message count and if count is 1, get message info.
754 FND_MSG_PUB.Count_And_Get
755 (p_count => x_msg_count,
756 p_data => x_msg_data
757 );
758 EXCEPTION
759
760 /*
761 WHEN PVX_UTILITY_PVT.resource_locked THEN
762 x_return_status := FND_API.g_ret_sts_error;
763 PVX_UTILITY_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
764 */
765
766 WHEN FND_API.G_EXC_ERROR THEN
767 ROLLBACK TO UPDATE_Attribute_PVT;
768 x_return_status := FND_API.G_RET_STS_ERROR;
769 -- Standard call to get message count and if count=1, get the message
770 FND_MSG_PUB.Count_And_Get (
771 p_encoded => FND_API.G_FALSE,
772 p_count => x_msg_count,
773 p_data => x_msg_data
774 );
775
776 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
777 ROLLBACK TO UPDATE_Attribute_PVT;
778 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
779 -- Standard call to get message count and if count=1, get the message
780 FND_MSG_PUB.Count_And_Get (
781 p_encoded => FND_API.G_FALSE,
782 p_count => x_msg_count,
783 p_data => x_msg_data
784 );
785
786 WHEN OTHERS THEN
787 ROLLBACK TO UPDATE_Attribute_PVT;
788 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
789 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
790 THEN
791 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
792 END IF;
793 -- Standard call to get message count and if count=1, get the message
794 FND_MSG_PUB.Count_And_Get (
795 p_encoded => FND_API.G_FALSE,
796 p_count => x_msg_count,
797 p_data => x_msg_data
798 );
799 End Update_Attribute;
800
801
802 PROCEDURE Delete_Attribute(
803 p_api_version_number IN NUMBER
804 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
805 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
806 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
807 ,x_return_status OUT NOCOPY VARCHAR2
808 ,x_msg_count OUT NOCOPY NUMBER
809 ,x_msg_data OUT NOCOPY VARCHAR2
810 ,p_attribute_id IN NUMBER
811 ,p_object_version_number IN NUMBER
812 )
813
814 IS
815 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Attribute';
816 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
817 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
818 l_object_version_number NUMBER;
819 l_attribute_name VARCHAR2(60);
820
821 l_being_used_list VARCHAR2(30000);
822 l_delete_flag VARCHAR2(1):='Y';
823 l_meaning VARCHAR2(80);
824 cursor lc_get_attr_details (pc_attribute_id number) is
825 select name
826 from pv_attributes_vl
827 where attribute_id = pc_attribute_id;
828
829 cursor lc_check_attr_enty_vals (pc_attribute_id number) is
830 select distinct entity
831 from pv_enty_attr_values
832 where attribute_id = pc_attribute_id;
833
834 cursor lc_check_rules (pc_attribute_id number) is
835 select distinct rules.process_rule_name
836 from pv_enty_select_criteria criteria,pv_process_rules_vl rules
837 where criteria.attribute_id= pc_attribute_id
838 and criteria.process_rule_id= rules.process_rule_id;
839
840 cursor lc_check_matching (pc_attribute_id number) is
841 select resp.RESPONSIBILITY_NAME
842 from pv_attrib_resp_mappings val, FND_RESPONSIBILITY_VL resp
843 where
844 val.attribute_id= pc_attribute_id and
845 resp.application_id = 691 and
846 val.entity_type = 'MANUAL_MATCHING' and
847 resp.RESPONSIBILITY_ID = val.responsibility_id;
848
849 cursor lc_check_resp_mappings (pc_attribute_id number) is
850
851 select resp.RESPONSIBILITY_NAME, lkp.meaning
852 from pv_attrib_resp_mappings val,
853 FND_RESPONSIBILITY_VL resp,
854 pv_lookups lkp
855 where
856 val.attribute_id= pc_attribute_id and
857 resp.RESPONSIBILITY_ID = val.responsibility_id and
858 resp.application_id = 691 and
859 val.entity_type = lkp.lookup_code and
860 lkp.lookup_type='PV_ATTR_RESP_MAPPING_ENTITIES';
861
862
863 cursor lc_check_seeded_attr (pc_attribute_id number) is
864 select seeded_flag
865 from pv_attributes_vl
866 where attribute_id = pc_attribute_id;
867
868 cursor lc_entity_attrs (pc_attribute_id number) is
869 select entity_attr_id
870 from pv_entity_attrs
871 where attribute_id = pc_attribute_id;
872
873 cursor lc_attribute_usages (pc_attribute_id number) is
874 select attribute_usage_id
875 from pv_attribute_usages
876 where attribute_id = pc_attribute_id;
877
878 cursor lc_attribute_codes (pc_attribute_id number) is
879 select attr_code_id
880 from PV_ATTRIBUTE_CODES_vl
881 where attribute_id = pc_attribute_id;
882
883
884 BEGIN
885 --DBMS_output.put_line('Begin Delete_attribute');
886
887 -- Standard Start of API savepoint
888 SAVEPOINT DELETE_Attribute_PVT;
889
890 -- Standard call to check for call compatibility.
891 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
892 p_api_version_number,
893 l_api_name,
894 G_PKG_NAME)
895 THEN
896 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
897 END IF;
898
899 -- Initialize message list if p_init_msg_list is set to TRUE.
900 IF FND_API.to_Boolean( p_init_msg_list )
901 THEN
902 FND_MSG_PUB.initialize;
903 END IF;
904
905 -- Debug Message
906 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
907 PVX_UTILITY_PVT.debug_message('Private API: ' || L_FULL_NAME || ' start');
908 END IF;
909
910 -- Initialize API return status to SUCCESS
911 x_return_status := FND_API.G_RET_STS_SUCCESS;
912
913 --
914 -- Api body
915 --
916 -- Debug Message
917
918 --checking whether this ttribute is being referenced by any entity, rule, manual_matching
919
920 --First get Attribute Name
921 open lc_get_attr_details (pc_attribute_id =>p_attribute_id);
922 fetch lc_get_attr_details into l_attribute_name;
923 close lc_get_attr_details;
924
925 --check for seeded attr
926
927 for x in lc_check_seeded_attr (pc_attribute_id =>p_attribute_id)
928 loop
929 if( x.seeded_flag = 'Y') then
930 l_delete_flag :='N';
931 end if;
932 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
933
934 FND_MESSAGE.set_name('PV', 'PV_DEBUG_MESSAGE');
935 FND_MESSAGE.set_token('TEXT', 'Is it Seeded '|| x.seeded_flag);
936 FND_MSG_PUB.add;
937
938 END IF;
939 end loop;
940
941
942 if(l_delete_flag = 'N') then
943
944 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR))
945 THEN
946 FND_MESSAGE.Set_Name('PV', 'PV_ATTR_REFERENCED_SEEDED');
947 FND_MESSAGE.Set_Token('ATTRIBUTE_NAME',l_attribute_name );
948
949 FND_MSG_PUB.Add;
950 END IF;
951
952 RAISE FND_API.G_EXC_ERROR;
953
954 end if;
955
956 --check for entity reference
957
958 for x in lc_check_attr_enty_vals (pc_attribute_id =>p_attribute_id)
959 loop
960 l_delete_flag := 'N';
961
962 FOR y IN (select meaning from pv_lookups
963 where lookup_type = 'PV_VALID_ENTY_VALUE_TYPES'
964 and lookup_code = x.entity
965 ) LOOP
966 l_meaning := y.meaning;
967 END LOOP;
968
969
970 l_being_used_list := l_being_used_list || ','|| l_meaning ;
971 l_meaning := '';
972
973 end loop;
974
975 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
976
977 FND_MESSAGE.set_name('PV', 'PV_DEBUG_MESSAGE');
978 FND_MESSAGE.set_token('TEXT', 'Entity List '|| l_being_used_list);
979 FND_MSG_PUB.add;
980 END IF;
981
982 if(l_delete_flag = 'N') then
983
984 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR))
985 THEN
986 FND_MESSAGE.Set_Name('PV', 'PV_ATTR_REFERENCED_ENTITY');
987 FND_MESSAGE.Set_Token('ATTRIBUTE_NAME',l_attribute_name );
988 FND_MESSAGE.Set_Token('ENTITY_LIST',substr(l_being_used_list,2) );
989 FND_MSG_PUB.Add;
990 END IF;
991
992 RAISE FND_API.G_EXC_ERROR;
993
994 end if;
995
996 --check for rule reference
997
998 for x in lc_check_rules (pc_attribute_id =>p_attribute_id)
999 loop
1000 l_delete_flag := 'N' ;
1001 l_being_used_list := l_being_used_list || ','|| x.process_rule_name ;
1002 end loop;
1003
1004
1005 if(l_delete_flag = 'N') then
1006
1007 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR))
1008 THEN
1009 FND_MESSAGE.Set_Name('PV', 'PV_ATTR_REFERENCED_RULE');
1010 FND_MESSAGE.Set_Token('ATTRIBUTE_NAME',l_attribute_name );
1011 FND_MESSAGE.Set_Token('RULES_LIST',substr(l_being_used_list,2) );
1012 FND_MSG_PUB.Add;
1013 END IF;
1014
1015 RAISE FND_API.G_EXC_ERROR;
1016
1017 end if;
1018
1019 --check for manual matching reference
1020
1021 for x in lc_check_matching (pc_attribute_id =>p_attribute_id)
1022 loop
1023 l_delete_flag := 'N' ;
1024 l_being_used_list := l_being_used_list || ','|| x.RESPONSIBILITY_NAME ;
1025 end loop;
1026
1027
1028 if(l_delete_flag = 'N') then
1029
1030 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR))
1031 THEN
1032 FND_MESSAGE.Set_Name('PV', 'PV_ATTR_REFERENCED_MATCHING');
1033 FND_MESSAGE.Set_Token('ATTRIBUTE_NAME',l_attribute_name );
1034 FND_MESSAGE.Set_Token('RESPONSIBILITY_LIST',substr(l_being_used_list,2) );
1035 FND_MSG_PUB.Add;
1036 END IF;
1037
1038 RAISE FND_API.G_EXC_ERROR;
1039
1040 end if;
1041
1042 --check for responsibilites mappings reference
1043
1044 for x in lc_check_resp_mappings (pc_attribute_id =>p_attribute_id)
1045 loop
1046 l_delete_flag := 'N' ;
1047 l_being_used_list := l_being_used_list || ','|| '(' || x.RESPONSIBILITY_NAME || ' ,' || x.meaning || ')';
1048 end loop;
1049
1050
1051 if(l_delete_flag = 'N') then
1052
1053 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR))
1054 THEN
1055 FND_MESSAGE.Set_Name('PV', 'PV_ATTR_REFERENCED_RESP_MAP');
1056 FND_MESSAGE.Set_Token('ATTRIBUTE_NAME',l_attribute_name );
1057 FND_MESSAGE.Set_Token('MAP_LIST',substr(l_being_used_list,2) );
1058 FND_MSG_PUB.Add;
1059 END IF;
1060
1061 RAISE FND_API.G_EXC_ERROR;
1062
1063 end if;
1064
1065
1066
1067
1068
1069 --start deleting
1070 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1071 PVX_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
1072 END IF;
1073
1074
1075 --delete all rows from pv_attr_codes table
1076
1077 for x in lc_attribute_codes (pc_attribute_id =>p_attribute_id)
1078 loop
1079 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1080 PVX_UTILITY_PVT.debug_message( 'Calling PV_ATTRIBUTE_CODE_PVT.Delete_Attribute_Code for : ' ||x.attr_code_id );
1081 END IF;
1082
1083 PV_ATTRIBUTE_CODE_PVT.Delete_Attribute_Code(
1084 p_api_version_number => p_api_version_number
1085 ,p_init_msg_list => p_init_msg_list
1086 ,p_commit => p_commit
1087 ,p_validation_level => p_validation_level
1088
1089 ,x_return_status => x_return_status
1090 ,x_msg_count => x_msg_count
1091 ,x_msg_data => x_msg_data
1092
1093 ,p_attr_code_id => x.attr_code_id
1094 ,p_object_version_number=> p_object_version_number
1095 );
1096
1097 end loop;
1098
1099 --delete all rows from pv_attribute_usages table
1100
1101 for x in lc_attribute_usages (pc_attribute_id =>p_attribute_id)
1102 loop
1103
1104 PVX_UTILITY_PVT.debug_message( 'Calling PV_Attribute_Usage_PVT.Delete_Attribute_Usage for : ' ||x.attribute_usage_id );
1105 PV_Attribute_Usage_PVT.Delete_Attribute_Usage(
1106 p_api_version_number => p_api_version_number
1107 ,p_init_msg_list => p_init_msg_list
1108 ,p_commit => p_commit
1109 ,p_validation_level => p_validation_level
1110
1111 ,x_return_status => x_return_status
1112 ,x_msg_count => x_msg_count
1113 ,x_msg_data => x_msg_data
1114
1115 ,p_attribute_usage_id => x.attribute_usage_id
1116 ,p_object_version_number=> p_object_version_number
1117 );
1118
1119 end loop;
1120
1121
1122 --delete all rows from pv_entity_attrs table
1123
1124 for x in lc_entity_attrs (pc_attribute_id =>p_attribute_id)
1125 loop
1126
1127 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1128 PVX_UTILITY_PVT.debug_message( 'Calling PV_Entity_Attribute_Pvt.Delete_Entity_Attr for : ' ||x.entity_attr_id );
1129 END IF;
1130 PV_Entity_Attribute_Pvt.Delete_Entity_Attr(
1131 p_api_version_number => p_api_version_number
1132 ,p_init_msg_list => p_init_msg_list
1133 ,p_commit => p_commit
1134 ,p_validation_level => p_validation_level
1135
1136 ,x_return_status => x_return_status
1137 ,x_msg_count => x_msg_count
1138 ,x_msg_data => x_msg_data
1139
1140 ,p_entity_attr_id => x.entity_attr_id
1141 ,p_object_version_number=> p_object_version_number
1142 );
1143 end loop;
1144
1145 -- Invoke table handler(PV_ATTRIBUTE_PKG.Delete_Row)
1146
1147 PV_ATTRIBUTE_PKG.Delete_Row(
1148 p_ATTRIBUTE_ID => p_ATTRIBUTE_ID);
1149 --
1150 -- End of API body
1151 --
1152
1153
1154 -- Standard check for p_commit
1155 IF FND_API.to_Boolean( p_commit )
1156 THEN
1157 COMMIT WORK;
1158 END IF;
1159
1160
1161 -- Debug Message
1162 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1163 PVX_UTILITY_PVT.debug_message('Private API: ' || L_FULL_NAME || 'end');
1164 END IF;
1165
1166 -- Standard call to get message count and if count is 1, get message info.
1167 FND_MSG_PUB.Count_And_Get
1168 (p_count => x_msg_count,
1169 p_data => x_msg_data
1170 );
1171 EXCEPTION
1172
1173 /*
1174 WHEN PVX_UTILITY_PVT.resource_locked THEN
1175 x_return_status := FND_API.g_ret_sts_error;
1176 PVX_UTILITY_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
1177 */
1178
1179 WHEN FND_API.G_EXC_ERROR THEN
1180 ROLLBACK TO DELETE_Attribute_PVT;
1181 x_return_status := FND_API.G_RET_STS_ERROR;
1182 -- Standard call to get message count and if count=1, get the message
1183 FND_MSG_PUB.Count_And_Get (
1184 p_encoded => FND_API.G_FALSE,
1185 p_count => x_msg_count,
1186 p_data => x_msg_data
1187 );
1188
1189 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1190 ROLLBACK TO DELETE_Attribute_PVT;
1191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1192 -- Standard call to get message count and if count=1, get the message
1193 FND_MSG_PUB.Count_And_Get (
1194 p_encoded => FND_API.G_FALSE,
1195 p_count => x_msg_count,
1196 p_data => x_msg_data
1197 );
1198
1199 WHEN OTHERS THEN
1200 ROLLBACK TO DELETE_Attribute_PVT;
1201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1202 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1203 THEN
1204 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1205 END IF;
1206 -- Standard call to get message count and if count=1, get the message
1207 FND_MSG_PUB.Count_And_Get (
1208 p_encoded => FND_API.G_FALSE,
1209 p_count => x_msg_count,
1210 p_data => x_msg_data
1211 );
1212 End Delete_Attribute;
1213
1214
1215
1216 -- Hint: Primary key needs to be returned.
1217 PROCEDURE Lock_Attribute(
1218 p_api_version_number IN NUMBER
1219 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1220
1221 ,x_return_status OUT NOCOPY VARCHAR2
1222 ,x_msg_count OUT NOCOPY NUMBER
1223 ,x_msg_data OUT NOCOPY VARCHAR2
1224
1225 ,p_attribute_id IN NUMBER
1226 ,p_object_version IN NUMBER
1227
1228 )
1229
1230 IS
1231 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Attribute';
1232 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1233 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1234 l_ATTRIBUTE_ID NUMBER;
1235
1236 CURSOR c_Attribute IS
1237 SELECT ATTRIBUTE_ID
1238 FROM PV_ATTRIBUTES_B
1239 WHERE ATTRIBUTE_ID = p_ATTRIBUTE_ID
1240 AND object_version_number = p_object_version
1241 FOR UPDATE NOWAIT;
1242
1243 BEGIN
1244
1245 -- Debug Message
1246 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1247 PVX_UTILITY_PVT.debug_message('Private API: ' || L_FULL_NAME || 'start');
1248 END IF;
1249
1250 -- Initialize message list if p_init_msg_list is set to TRUE.
1251 IF FND_API.to_Boolean( p_init_msg_list )
1252 THEN
1253 FND_MSG_PUB.initialize;
1254 END IF;
1255
1256 -- Standard call to check for call compatibility.
1257 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1258 p_api_version_number,
1259 l_api_name,
1260 G_PKG_NAME)
1261 THEN
1262 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1263 END IF;
1264
1265
1266 -- Initialize API return status to SUCCESS
1267 x_return_status := FND_API.G_RET_STS_SUCCESS;
1268
1269
1270 ------------------------ lock -------------------------
1271
1272 PVX_UTILITY_PVT.debug_message(l_full_name||': start');
1273 OPEN c_Attribute;
1274
1275 FETCH c_Attribute INTO l_ATTRIBUTE_ID;
1276
1277 IF (c_Attribute%NOTFOUND) THEN
1278 CLOSE c_Attribute;
1279 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1280 FND_MESSAGE.set_name('PV', 'PV_API_RECORD_NOT_FOUND');
1281 FND_MSG_PUB.add;
1282 END IF;
1283 RAISE FND_API.g_exc_error;
1284 END IF;
1285
1286 CLOSE c_Attribute;
1287
1288 -------------------- finish --------------------------
1289 FND_MSG_PUB.count_and_get(
1290 p_encoded => FND_API.g_false,
1291 p_count => x_msg_count,
1292 p_data => x_msg_data);
1293 PVX_UTILITY_PVT.debug_message(l_full_name ||': end');
1294 EXCEPTION
1295
1296 WHEN PVX_UTILITY_PVT.resource_locked THEN
1297 x_return_status := FND_API.g_ret_sts_error;
1298 PVX_UTILITY_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
1299
1300 WHEN FND_API.G_EXC_ERROR THEN
1301 ROLLBACK TO LOCK_Attribute_PVT;
1302 x_return_status := FND_API.G_RET_STS_ERROR;
1303 -- Standard call to get message count and if count=1, get the message
1304 FND_MSG_PUB.Count_And_Get (
1305 p_encoded => FND_API.G_FALSE,
1306 p_count => x_msg_count,
1307 p_data => x_msg_data
1308 );
1309
1310 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1311 ROLLBACK TO LOCK_Attribute_PVT;
1312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1313 -- Standard call to get message count and if count=1, get the message
1314 FND_MSG_PUB.Count_And_Get (
1315 p_encoded => FND_API.G_FALSE,
1316 p_count => x_msg_count,
1317 p_data => x_msg_data
1318 );
1319
1320 WHEN OTHERS THEN
1321 ROLLBACK TO LOCK_Attribute_PVT;
1322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1323 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1324 THEN
1325 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1326 END IF;
1327 -- Standard call to get message count and if count=1, get the message
1328 FND_MSG_PUB.Count_And_Get (
1329 p_encoded => FND_API.G_FALSE,
1330 p_count => x_msg_count,
1331 p_data => x_msg_data
1332 );
1333 End Lock_Attribute;
1334
1335
1336 PROCEDURE check_uk_items(
1337 p_attribute_rec IN attribute_rec_type,
1338 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1339 x_return_status OUT NOCOPY VARCHAR2)
1340 IS
1341 l_valid_flag VARCHAR2(1);
1342 l_valid_tl_flag VARCHAR2(1);
1343
1344 cursor lc_get_attr_name is
1345 select name from pv_attributes_tl;
1346
1347
1348 BEGIN
1349
1350 x_return_status := FND_API.g_ret_sts_success;
1351 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1352 l_valid_flag := PVX_UTILITY_PVT.check_uniqueness(
1353 'PV_ATTRIBUTES_B',
1354 'ATTRIBUTE_ID = ''' || p_attribute_rec.ATTRIBUTE_ID ||''''
1355 );
1356 ELSE
1357 l_valid_flag := PVX_UTILITY_PVT.check_uniqueness(
1358 'PV_ATTRIBUTES_B',
1359 'ATTRIBUTE_ID = ''' || p_attribute_rec.ATTRIBUTE_ID ||
1360 ''' AND ATTRIBUTE_ID <> ' || p_attribute_rec.ATTRIBUTE_ID
1361 );
1362 END IF;
1363
1364 IF l_valid_flag = FND_API.g_false THEN
1365 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1366 FND_MESSAGE.set_name('PV', 'PV_API_DUPLICATE_ENTITY');
1367 FND_MESSAGE.set_token('ID',to_char(p_attribute_rec.ATTRIBUTE_ID) );
1368 FND_MESSAGE.set_token('ENTITY','Attribute');
1369 FND_MSG_PUB.add;
1370 END IF;
1371 x_return_status := FND_API.g_ret_sts_error;
1372 RETURN;
1373 END IF;
1374
1375
1376
1377 -- Added for Bug # 2480199 Begin
1378
1379 --check for uniqueness of attribute_code
1380
1381 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1382 for x in lc_get_attr_name
1383 loop
1384 if (UPPER(p_attribute_rec.name)=UPPER(x.name)) then
1385 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1386
1387 FND_MESSAGE.set_name('PV', 'PV_DUPLICATE_RECORD');
1388 --FND_MESSAGE.set_name('PV', 'PV_API_DUPLICATE_ENTITY');
1389 --FND_MESSAGE.set_token('ID',to_char(p_attribute_rec.ATTRIBUTE_ID) || ':::' || p_attribute_rec.name);
1390 --FND_MESSAGE.set_token('ENTITY','Attribute');
1391 FND_MSG_PUB.add;
1392 END IF;
1393 --x_return_status := FND_API.g_ret_sts_error;
1394 RAISE FND_API.G_EXC_ERROR;
1395 end if;
1396
1397 end loop;
1398 END IF;
1399
1400
1401
1402 /* IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1403 l_valid_tl_flag := PVX_UTILITY_PVT.check_uniqueness(
1404 'PV_ATTRIBUTES_TL','SHORT_NAME = ''' || p_attribute_rec.SHORT_NAME
1405 ||''' AND LANGUAGE = ' || userenv('LANG') );
1406 END IF ;
1407
1408 IF l_valid_tl_flag = FND_API.g_false THEN
1409 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1410 FND_MESSAGE.set_name('PV', 'PV_DUPLICATE_RECORD');
1411 FND_MSG_PUB.add;
1412 END IF;
1413 x_return_status := FND_API.g_ret_sts_error;
1414 RETURN;
1415 END IF;
1416
1417 -- End
1418 */
1419 END check_uk_items;
1420
1421 PROCEDURE check_req_items(
1422 p_attribute_rec IN attribute_rec_type,
1423 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1424 x_return_status OUT NOCOPY VARCHAR2
1425 )
1426 IS
1427 BEGIN
1428 x_return_status := FND_API.g_ret_sts_success;
1429
1430 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1431
1432 IF p_attribute_rec.attribute_id = FND_API.g_miss_num OR p_attribute_rec.attribute_id IS NULL THEN
1433 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1434 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1435 FND_MESSAGE.set_token('COLUMN','attribute_id');
1436 FND_MSG_PUB.add;
1437 END IF;
1438 x_return_status := FND_API.g_ret_sts_error;
1439 RETURN;
1440 END IF;
1441
1442
1443 IF p_attribute_rec.last_update_date = FND_API.g_miss_date OR p_attribute_rec.last_update_date IS NULL THEN
1444 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1445 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1446 FND_MESSAGE.set_token('COLUMN','last_update_date');
1447 FND_MSG_PUB.add;
1448 END IF;
1449 x_return_status := FND_API.g_ret_sts_error;
1450 RETURN;
1451 END IF;
1452
1453
1454 IF p_attribute_rec.last_updated_by = FND_API.g_miss_num OR p_attribute_rec.last_updated_by IS NULL THEN
1455 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1456 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1457 FND_MESSAGE.set_token('COLUMN','last_updated_by');
1458 FND_MSG_PUB.add;
1459 END IF;
1460 x_return_status := FND_API.g_ret_sts_error;
1461 RETURN;
1462 END IF;
1463
1464
1465 IF p_attribute_rec.creation_date = FND_API.g_miss_date OR p_attribute_rec.creation_date IS NULL THEN
1466 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1467 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1468 FND_MESSAGE.set_token('COLUMN','creation_date');
1469 FND_MSG_PUB.add;
1470 END IF;
1471 x_return_status := FND_API.g_ret_sts_error;
1472 RETURN;
1473 END IF;
1474
1475
1476 IF p_attribute_rec.created_by = FND_API.g_miss_num OR p_attribute_rec.created_by IS NULL THEN
1477 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1478 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1479 FND_MESSAGE.set_token('COLUMN','created_by');
1480 FND_MSG_PUB.add;
1481 END IF;
1482 x_return_status := FND_API.g_ret_sts_error;
1483 RETURN;
1484 END IF;
1485
1486
1487 IF p_attribute_rec.last_update_login = FND_API.g_miss_num OR p_attribute_rec.last_update_login IS NULL THEN
1488 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1489 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1490 FND_MESSAGE.set_token('COLUMN','last_update_login');
1491 FND_MSG_PUB.add;
1492 END IF;
1493 x_return_status := FND_API.g_ret_sts_error;
1494 RETURN;
1495 END IF;
1496
1497
1498 IF p_attribute_rec.object_version_number = FND_API.g_miss_num OR p_attribute_rec.object_version_number IS NULL THEN
1499 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1500 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1501 FND_MESSAGE.set_token('COLUMN','object_version_number');
1502 FND_MSG_PUB.add;
1503 END IF;
1504 x_return_status := FND_API.g_ret_sts_error;
1505 RETURN;
1506 END IF;
1507
1508
1509 IF p_attribute_rec.enabled_flag = FND_API.g_miss_char OR p_attribute_rec.enabled_flag IS NULL THEN
1510 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1511 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1512 FND_MESSAGE.set_token('COLUMN','enabled_flag');
1513 FND_MSG_PUB.add;
1514 END IF;
1515 x_return_status := FND_API.g_ret_sts_error;
1516 RETURN;
1517 END IF;
1518
1519 IF p_attribute_rec.short_name = FND_API.g_miss_char OR p_attribute_rec.short_name IS NULL THEN
1520 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1521 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1522 FND_MESSAGE.set_token('COLUMN','short_name');
1523 FND_MSG_PUB.add;
1524 END IF;
1525 x_return_status := FND_API.g_ret_sts_error;
1526 RETURN;
1527 END IF;
1528
1529 ELSE
1530
1531
1532 IF p_attribute_rec.attribute_id IS NULL THEN
1533 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1534 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1535 FND_MESSAGE.set_token('COLUMN','attribute_id');
1536 FND_MSG_PUB.add;
1537 END IF;
1538 x_return_status := FND_API.g_ret_sts_error;
1539 RETURN;
1540 END IF;
1541
1542
1543 IF p_attribute_rec.last_update_date IS NULL THEN
1544 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1545 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1546 FND_MESSAGE.set_token('COLUMN','last_update_date');
1547 FND_MSG_PUB.add;
1548 END IF;
1549 x_return_status := FND_API.g_ret_sts_error;
1550 RETURN;
1551 END IF;
1552
1553
1554 IF p_attribute_rec.last_updated_by IS NULL THEN
1555 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1556 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1557 FND_MESSAGE.set_token('COLUMN','last_updated_by');
1558 FND_MSG_PUB.add;
1559 end if;
1560 x_return_status := FND_API.g_ret_sts_error;
1561 RETURN;
1562 END IF;
1563
1564
1565 IF p_attribute_rec.creation_date IS NULL THEN
1566 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1567 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1568 FND_MESSAGE.set_token('COLUMN','creation_date');
1569 FND_MSG_PUB.add;
1570 end if;
1571 x_return_status := FND_API.g_ret_sts_error;
1572 RETURN;
1573 END IF;
1574
1575
1576 IF p_attribute_rec.created_by IS NULL THEN
1577 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1578 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1579 FND_MESSAGE.set_token('COLUMN','created_by');
1580 FND_MSG_PUB.add;
1581 end if;
1582 x_return_status := FND_API.g_ret_sts_error;
1583 RETURN;
1584 END IF;
1585
1586
1587 IF p_attribute_rec.last_update_login IS NULL THEN
1588 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1589 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1590 FND_MESSAGE.set_token('COLUMN','last_update_login');
1591 FND_MSG_PUB.add;
1592 end if;
1593 x_return_status := FND_API.g_ret_sts_error;
1594 RETURN;
1595 END IF;
1596
1597
1598 IF p_attribute_rec.object_version_number IS NULL THEN
1599 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1600 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1601 FND_MESSAGE.set_token('COLUMN','object_version_number');
1602 FND_MSG_PUB.add;
1603 end if;
1604 x_return_status := FND_API.g_ret_sts_error;
1605 RETURN;
1606 END IF;
1607
1608
1609 IF p_attribute_rec.enabled_flag IS NULL THEN
1610 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1611 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1612 FND_MESSAGE.set_token('COLUMN','enabled_flag');
1613 FND_MSG_PUB.add;
1614 end if;
1615 x_return_status := FND_API.g_ret_sts_error;
1616 RETURN;
1617 END IF;
1618 END IF;
1619
1620
1621 IF p_attribute_rec.short_name IS NULL THEN
1622 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1623 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1624 FND_MESSAGE.set_token('COLUMN','short_name');
1625 FND_MSG_PUB.add;
1626 end if;
1627 x_return_status := FND_API.g_ret_sts_error;
1628 RETURN;
1629 END IF;
1630
1631
1632
1633 END check_req_items;
1634
1635 PROCEDURE check_fk_items(
1636 p_attribute_rec IN attribute_rec_type,
1637 x_return_status OUT NOCOPY VARCHAR2
1638 )
1639 IS
1640 BEGIN
1641 x_return_status := FND_API.g_ret_sts_success;
1642
1643 -- Enter custom code here
1644
1645 END check_fk_items;
1646
1647 PROCEDURE check_lookup_items(
1648 p_attribute_rec IN attribute_rec_type,
1649 x_return_status OUT NOCOPY VARCHAR2
1650 )
1651 IS
1652 BEGIN
1653 x_return_status := FND_API.g_ret_sts_success;
1654
1655 -- Enter custom code here
1656
1657 END check_lookup_items;
1658
1659 PROCEDURE Check_attribute_Items (
1660 p_attribute_rec IN attribute_rec_type
1661 ,p_validation_mode IN VARCHAR2
1662 ,x_return_status OUT NOCOPY VARCHAR2
1663 )
1664 IS
1665 BEGIN
1666
1667 -- Check Items Uniqueness API calls
1668
1669 --DBMS_output.put_line('Before check_uk_items');
1670
1671 check_uk_items(
1672 p_attribute_rec => p_attribute_rec,
1673 p_validation_mode => p_validation_mode,
1674 x_return_status => x_return_status);
1675 IF x_return_status <> FND_API.g_ret_sts_success THEN
1676 RETURN;
1677 END IF;
1678
1679 -- Check Items Required/NOT NULL API calls
1680
1681 --DBMS_output.put_line('Before check_req_items');
1682
1683 check_req_items(
1684 p_attribute_rec => p_attribute_rec,
1685 p_validation_mode => p_validation_mode,
1686 x_return_status => x_return_status);
1687 IF x_return_status <> FND_API.g_ret_sts_success THEN
1688 RETURN;
1689 END IF;
1690 -- Check Items Foreign Keys API calls
1691
1692 --DBMS_output.put_line('Before check_fk_items');
1693
1694 check_fk_items(
1695 p_attribute_rec => p_attribute_rec,
1696 x_return_status => x_return_status);
1697 IF x_return_status <> FND_API.g_ret_sts_success THEN
1698 RETURN;
1699 END IF;
1700 -- Check Items Lookups
1701
1702 --DBMS_output.put_line('Before check_lookup_items');
1703
1704 check_lookup_items(
1705 p_attribute_rec => p_attribute_rec,
1706 x_return_status => x_return_status);
1707 IF x_return_status <> FND_API.g_ret_sts_success THEN
1708 RETURN;
1709 END IF;
1710
1711
1712 END Check_attribute_Items;
1713
1714
1715
1716 PROCEDURE Complete_attribute_Rec (
1717 p_attribute_rec IN attribute_rec_type,
1718 x_complete_rec OUT NOCOPY attribute_rec_type)
1719 IS
1720 l_return_status VARCHAR2(1);
1721
1722 CURSOR c_complete IS
1723 SELECT *
1724 FROM pv_attributes_vl
1725 WHERE attribute_id = p_attribute_rec.attribute_id;
1726 l_attribute_rec c_complete%ROWTYPE;
1727 BEGIN
1728
1729 x_complete_rec := p_attribute_rec;
1730
1731
1732 OPEN c_complete;
1733 FETCH c_complete INTO l_attribute_rec;
1734 CLOSE c_complete;
1735
1736 -- attribute_id
1737 IF p_attribute_rec.attribute_id = FND_API.g_miss_num THEN
1738 x_complete_rec.attribute_id := l_attribute_rec.attribute_id;
1739 END IF;
1740
1741 -- last_update_date
1742 IF p_attribute_rec.last_update_date = FND_API.g_miss_date THEN
1743 x_complete_rec.last_update_date := l_attribute_rec.last_update_date;
1744 END IF;
1745
1746 -- last_updated_by
1747 IF p_attribute_rec.last_updated_by = FND_API.g_miss_num THEN
1748 x_complete_rec.last_updated_by := l_attribute_rec.last_updated_by;
1749 END IF;
1750
1751 -- creation_date
1752 IF p_attribute_rec.creation_date = FND_API.g_miss_date THEN
1753 x_complete_rec.creation_date := l_attribute_rec.creation_date;
1754 END IF;
1755
1756 -- created_by
1757 IF p_attribute_rec.created_by = FND_API.g_miss_num THEN
1758 x_complete_rec.created_by := l_attribute_rec.created_by;
1759 END IF;
1760
1761 -- last_update_login
1762 IF p_attribute_rec.last_update_login = FND_API.g_miss_num THEN
1763 x_complete_rec.last_update_login := l_attribute_rec.last_update_login;
1764 END IF;
1765
1766 -- object_version_number
1767 IF p_attribute_rec.object_version_number = FND_API.g_miss_num THEN
1768 x_complete_rec.object_version_number := l_attribute_rec.object_version_number;
1769 END IF;
1770
1771 -- security_group_id
1772 /*
1773 IF p_attribute_rec.security_group_id = FND_API.g_miss_num THEN
1774 x_complete_rec.security_group_id := l_attribute_rec.security_group_id;
1775 END IF;
1776 */
1777
1778 -- enabled_flag
1779 IF p_attribute_rec.enabled_flag = FND_API.g_miss_char THEN
1780 x_complete_rec.enabled_flag := l_attribute_rec.enabled_flag;
1781 END IF;
1782
1783 -- attribute_type
1784 IF p_attribute_rec.attribute_type = FND_API.g_miss_char THEN
1785 x_complete_rec.attribute_type := l_attribute_rec.attribute_type;
1786 END IF;
1787
1788 -- attribute_category
1789 IF p_attribute_rec.attribute_category = FND_API.g_miss_char THEN
1790 x_complete_rec.attribute_category := l_attribute_rec.attribute_category;
1791 END IF;
1792
1793 -- seeded_flag
1794 IF p_attribute_rec.seeded_flag = FND_API.g_miss_char THEN
1795 x_complete_rec.seeded_flag := l_attribute_rec.seeded_flag;
1796 END IF;
1797
1798 -- lov_function_name
1799 IF p_attribute_rec.lov_function_name = FND_API.g_miss_char THEN
1800 x_complete_rec.lov_function_name := l_attribute_rec.lov_function_name;
1801 END IF;
1802
1803 -- return_type
1804 IF p_attribute_rec.return_type = FND_API.g_miss_char THEN
1805 x_complete_rec.return_type := l_attribute_rec.return_type;
1806 END IF;
1807
1808 -- max_value_flag
1809 IF p_attribute_rec.max_value_flag = FND_API.g_miss_char THEN
1810 x_complete_rec.max_value_flag := l_attribute_rec.max_value_flag;
1811 END IF;
1812
1813 -- name
1814 IF p_attribute_rec.name = FND_API.g_miss_char THEN
1815 x_complete_rec.name := l_attribute_rec.name;
1816 END IF;
1817
1818 -- description
1819 IF p_attribute_rec.description = FND_API.g_miss_char THEN
1820 x_complete_rec.description := l_attribute_rec.description;
1821 END IF;
1822
1823 -- short_name
1824 IF p_attribute_rec.short_name = FND_API.g_miss_char THEN
1825 x_complete_rec.short_name := l_attribute_rec.short_name;
1826 END IF;
1827
1828 -- dispaly_style
1829 IF p_attribute_rec.display_style = FND_API.g_miss_char THEN
1830 x_complete_rec.display_style := l_attribute_rec.display_style;
1831 END IF;
1832
1833 -- character_width
1834 IF p_attribute_rec.character_width = FND_API.g_miss_num THEN
1835 x_complete_rec.character_width := l_attribute_rec.character_width;
1836 END IF;
1837
1838 -- decimal_points
1839 IF p_attribute_rec.decimal_points = FND_API.g_miss_num THEN
1840 x_complete_rec.decimal_points := l_attribute_rec.decimal_points;
1841 END IF;
1842
1843 -- no_of_lines
1844 IF p_attribute_rec.no_of_lines = FND_API.g_miss_num THEN
1845 x_complete_rec.no_of_lines := l_attribute_rec.no_of_lines;
1846 END IF;
1847
1848 -- expose_to_partner_flag
1849 IF p_attribute_rec.expose_to_partner_flag = FND_API.g_miss_char THEN
1850 x_complete_rec.expose_to_partner_flag := l_attribute_rec.expose_to_partner_flag;
1851 END IF;
1852
1853 -- value_extn_return_type
1854 IF p_attribute_rec.value_extn_return_type = FND_API.g_miss_char THEN
1855 x_complete_rec.value_extn_return_type := l_attribute_rec.value_extn_return_type;
1856 END IF;
1857
1858 -- enable_matching_flag
1859 IF p_attribute_rec.enable_matching_flag = FND_API.g_miss_char THEN
1860 x_complete_rec.enable_matching_flag := l_attribute_rec.enable_matching_flag;
1861
1862 END IF;
1863
1864 -- PErformance flag
1865 IF p_attribute_rec.performance_flag = FND_API.g_miss_char THEN
1866 x_complete_rec.performance_flag := l_attribute_rec.performance_flag;
1867
1868 END IF;
1869
1870 -- Additive flag
1871 IF p_attribute_rec.additive_flag = FND_API.g_miss_char THEN
1872 x_complete_rec.additive_flag := l_attribute_rec.additive_flag;
1873
1874 END IF;
1875
1876 -- sequence_number
1877 IF p_attribute_rec.sequence_number = FND_API.g_miss_num THEN
1878 x_complete_rec.sequence_number := l_attribute_rec.sequence_number;
1879
1880 END IF;
1881
1882
1883
1884 -- Note: Developers need to modify the procedure
1885 -- to handle any business specific requirements.
1886 END Complete_attribute_Rec;
1887
1888 PROCEDURE Validate_attribute(
1889 p_api_version_number IN NUMBER
1890 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1891 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1892 ,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_update
1893 ,p_attribute_rec IN attribute_rec_type
1894 ,x_return_status OUT NOCOPY VARCHAR2
1895 ,x_msg_count OUT NOCOPY NUMBER
1896 ,x_msg_data OUT NOCOPY VARCHAR2
1897
1898 )
1899 IS
1900 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Attribute';
1901 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
1902 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1903 l_object_version_number NUMBER;
1904 l_attribute_rec PV_Attribute_PVT.attribute_rec_type;
1905
1906 BEGIN
1907 -- Standard Start of API savepoint
1908 SAVEPOINT VALIDATE_ATTRIBUTE_PVT;
1909 --DBMS_output.put_line('Begin Validate_attribute');
1910
1911
1912 -- Standard call to check for call compatibility.
1913 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1914 p_api_version_number,
1915 l_api_name,
1916 G_PKG_NAME)
1917 THEN
1918 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1919 END IF;
1920
1921 -- Initialize message list if p_init_msg_list is set to TRUE.
1922 IF FND_API.to_Boolean( p_init_msg_list )
1923 THEN
1924 FND_MSG_PUB.initialize;
1925 END IF;
1926 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1927
1928 --DBMS_output.put_line('Before Check_attribute_Items');
1929
1930 Check_attribute_Items(
1931 p_attribute_rec => p_attribute_rec,
1932 p_validation_mode => p_validation_mode,
1933 x_return_status => x_return_status
1934 );
1935
1936 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1937 RAISE FND_API.G_EXC_ERROR;
1938 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1939 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1940 END IF;
1941 END IF;
1942
1943 --DBMS_output.put_line('Before Complete_attribute_Rec');
1944
1945 Complete_attribute_Rec(
1946 p_attribute_rec => p_attribute_rec,
1947 x_complete_rec => l_attribute_rec
1948 );
1949
1950 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1951
1952 --DBMS_output.put_line('Before Validate_attribute_Rec');
1953
1954 Validate_attribute_Rec(
1955 p_api_version_number => 1.0
1956 ,p_init_msg_list => FND_API.G_FALSE
1957 ,x_return_status => x_return_status
1958 ,x_msg_count => x_msg_count
1959 ,x_msg_data => x_msg_data
1960 ,p_attribute_rec => l_attribute_rec
1961 ,p_validation_mode => p_validation_mode
1962 );
1963
1964 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1965 RAISE FND_API.G_EXC_ERROR;
1966 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1967 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1968 END IF;
1969 END IF;
1970
1971 -- Debug Message
1972 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1973 PVX_UTILITY_PVT.debug_message('Private API: ' || L_FULL_NAME || 'start');
1974 END IF;
1975
1976
1977 -- Initialize API return status to SUCCESS
1978 x_return_status := FND_API.G_RET_STS_SUCCESS;
1979
1980 -- Debug Message
1981 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
1982 PVX_UTILITY_PVT.debug_message('Private API: ' || L_FULL_NAME || 'end');
1983 END IF;
1984
1985 -- Standard call to get message count and if count is 1, get message info.
1986 FND_MSG_PUB.Count_And_Get
1987 (p_count => x_msg_count,
1988 p_data => x_msg_data
1989 );
1990 EXCEPTION
1991
1992 WHEN PVX_UTILITY_PVT.resource_locked THEN
1993
1994 x_return_status := FND_API.g_ret_sts_error;
1995 PVX_UTILITY_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
1996
1997 WHEN FND_API.G_EXC_ERROR THEN
1998 ROLLBACK TO VALIDATE_Attribute_PVT;
1999 x_return_status := FND_API.G_RET_STS_ERROR;
2000 -- Standard call to get message count and if count=1, get the message
2001 FND_MSG_PUB.Count_And_Get (
2002 p_encoded => FND_API.G_FALSE,
2003 p_count => x_msg_count,
2004 p_data => x_msg_data
2005 );
2006
2007 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2008 ROLLBACK TO VALIDATE_Attribute_PVT;
2009 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2010 -- Standard call to get message count and if count=1, get the message
2011 FND_MSG_PUB.Count_And_Get (
2012 p_encoded => FND_API.G_FALSE,
2013 p_count => x_msg_count,
2014 p_data => x_msg_data
2015 );
2016
2017 WHEN OTHERS THEN
2018 ROLLBACK TO VALIDATE_Attribute_PVT;
2019 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2020 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2021 THEN
2022 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2023 END IF;
2024 -- Standard call to get message count and if count=1, get the message
2025 FND_MSG_PUB.Count_And_Get (
2026 p_encoded => FND_API.G_FALSE,
2027 p_count => x_msg_count,
2028 p_data => x_msg_data
2029 );
2030
2031 End Validate_Attribute;
2032
2033
2034 PROCEDURE Validate_attribute_rec(
2035 p_api_version_number IN NUMBER
2036 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2037 ,x_return_status OUT NOCOPY VARCHAR2
2038 ,x_msg_count OUT NOCOPY NUMBER
2039 ,x_msg_data OUT NOCOPY VARCHAR2
2040 ,p_attribute_rec IN attribute_rec_type
2041 ,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.G_UPDATE
2042 )
2043 IS
2044 BEGIN
2045
2046 --DBMS_output.put_line('validate_attribute_rec');
2047
2048 -- Initialize message list if p_init_msg_list is set to TRUE.
2049 IF FND_API.to_Boolean( p_init_msg_list )
2050 THEN
2051 FND_MSG_PUB.initialize;
2052 END IF;
2053
2054 -- Initialize API return status to SUCCESS
2055 x_return_status := FND_API.G_RET_STS_SUCCESS;
2056
2057 -- Hint: Validate data
2058 -- If data not valid
2059 -- THEN
2060 -- x_return_status := FND_API.G_RET_STS_ERROR;
2061
2062 -- Debug Message
2063 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
2064 PVX_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
2065 END IF;
2066 -- Standard call to get message count and if count is 1, get message info.
2067 FND_MSG_PUB.Count_And_Get
2068 (p_count => x_msg_count,
2069 p_data => x_msg_data
2070 );
2071
2072 END Validate_attribute_Rec;
2073
2074 END PV_Attribute_PVT;