[Home] [Help]
PACKAGE BODY: APPS.PV_ENTY_ATTR_VALUE_PVT
Source
1 PACKAGE BODY PV_Enty_Attr_Value_PVT AS
2 /* $Header: pvxveavb.pls 120.3 2005/11/07 11:51:29 amaram ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_Enty_Attr_Value_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_Enty_Attr_Value_PVT';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxveavb.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_Attr_Value(
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_enty_attr_val_rec IN enty_attr_val_rec_type := g_miss_enty_attr_val_rec
35 ,x_enty_attr_val_id OUT NOCOPY NUMBER
36 )
37
38
39 IS
40 l_api_name CONSTANT VARCHAR2(30) := 'Create_Attr_Value';
41 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
42 l_api_version_number CONSTANT NUMBER := 1.0;
43 l_return_status_full VARCHAR2(1);
44 l_object_version_number NUMBER := 1;
45 l_org_id NUMBER := FND_API.G_MISS_NUM;
46 l_enty_attr_val_id NUMBER;
47 l_dummy NUMBER;
48 l_enty_attr_val_rec enty_attr_val_rec_type := p_enty_attr_val_rec;
49
50 CURSOR c_id IS
51 SELECT PV_ENTY_ATTR_VALUES_s.NEXTVAL
52 FROM dual;
53
54 CURSOR c_id_exists (l_id IN NUMBER) IS
55 SELECT 1
56 FROM PV_ENTY_ATTR_VALUES
57 WHERE ENTY_ATTR_VAL_ID = l_id;
58
59 CURSOR c_get_enty_attr_value(cv_attribute_id NUMBER,cv_entity_id NUMBER, cv_entity VARCHAR2) IS
60 SELECT *
61 FROM PV_ENTY_ATTR_VALUES
62 WHERE attribute_id = cv_attribute_id and
63 entity_id = cv_entity_id and
64 entity = cv_entity and
65 latest_flag = 'Y';
66
67 CURSOR c_get_attr_details(cv_attribute_id NUMBER) IS
68 SELECT attribute_type,display_style,DECIMAL_POINTS,name
69 FROM PV_ATTRIBUTES_VL
70 WHERE attribute_id = cv_attribute_id
71 ;
72 l_already_exists VARCHAR2(1) := 'N';
73 l_attribute_type VARCHAR2(30);
74 l_display_style VARCHAR2(30);
75 l_meaning VARCHAR2(80);
76
77
78 BEGIN
79 -- Standard Start of API savepoint
80 SAVEPOINT Create_Attr_Value_PVT;
81
82 -- Standard call to check for call compatibility.
83 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
84 p_api_version_number,
85 l_api_name,
86 G_PKG_NAME)
87 THEN
88 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
89 END IF;
90
91 -- Initialize message list if p_init_msg_list is set to TRUE.
92 IF FND_API.to_Boolean( p_init_msg_list )
93 THEN
94 FND_MSG_PUB.initialize;
95 END IF;
96
97 -- Debug Message
98 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
99 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - start');
100 END IF;
101
102
103 -- Initialize API return status to SUCCESS
104 x_return_status := FND_API.G_RET_STS_SUCCESS;
105
106 -- Local variable initialization
107
108 IF p_enty_attr_val_rec.ENTY_ATTR_VAL_ID IS NULL OR
109 p_enty_attr_val_rec.ENTY_ATTR_VAL_ID = FND_API.g_miss_num THEN
110 LOOP
111 l_dummy := NULL;
112 OPEN c_id;
113 FETCH c_id INTO l_ENTY_ATTR_VAL_ID;
114 CLOSE c_id;
115
116 OPEN c_id_exists(l_ENTY_ATTR_VAL_ID);
117 FETCH c_id_exists INTO l_dummy;
118 CLOSE c_id_exists;
119 EXIT WHEN l_dummy IS NULL;
120 END LOOP;
121 ELSE
122 l_ENTY_ATTR_VAL_ID := p_enty_attr_val_rec.ENTY_ATTR_VAL_ID;
123 END IF;
124
125 -- =========================================================================
126 -- Validate Environment
127 -- =========================================================================
128
129 IF FND_GLOBAL.User_Id IS NULL
130 THEN
131 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
132 FND_MESSAGE.set_name('PV', 'PV_API_USER_PROFILE_MISSING');
133 FND_MSG_PUB.add;
134 END IF;
135 RAISE FND_API.G_EXC_ERROR;
136 END IF;
137
138 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
139 THEN
140
141 --DBMS_OUTPUT.PUT_LINE(l_full_name||' : Before Validate_attr_value' );
142
143 -- Populate the default required items
144 l_enty_attr_val_rec.enty_attr_val_id := l_enty_attr_val_id;
145 l_enty_attr_val_rec.last_update_date := SYSDATE;
146 l_enty_attr_val_rec.last_updated_by := G_USER_ID;
147 l_enty_attr_val_rec.creation_date := SYSDATE;
148 l_enty_attr_val_rec.created_by := G_USER_ID;
149 l_enty_attr_val_rec.last_update_login := G_LOGIN_ID;
150 l_enty_attr_val_rec.object_version_number := l_object_version_number;
151
152 --Now validating entity attribute value
153 --We would not validate for entity ENRQ.
154 if ( p_enty_attr_val_rec.entity <> 'ENRQ')
155 then
156 -- Debug message
157 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
158 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - Validate_attr_value');
159 END IF;
160
161 -- Invoke validation procedures
162 Validate_attr_value(
163 p_api_version_number => 1.0
164 ,p_init_msg_list => FND_API.G_FALSE
165 ,p_validation_level => p_validation_level
166 ,p_validation_mode => JTF_PLSQL_API.g_create
167 ,p_enty_attr_val_rec => l_enty_attr_val_rec
168 ,x_return_status => x_return_status
169 ,x_msg_count => x_msg_count
170 ,x_msg_data => x_msg_data
171 );
172
173 end if;
174 --DBMS_OUTPUT.PUT_LINE('x_return_status = '||x_return_status );
175
176 --DBMS_OUTPUT.PUT_LINE(l_full_name||' : After Validate_attr_value' );
177
178 END IF;
179
180 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
181 RAISE FND_API.G_EXC_ERROR;
182 END IF;
183
184 --DBMS_OUTPUT.PUT_LINE(l_full_name||' : After Validate' );
185
186 for x in c_get_attr_details(cv_attribute_id => p_enty_attr_val_rec.attribute_id )
187 loop
188 l_attribute_type := x.attribute_type;
189 l_display_style := x.display_style;
190
191 end loop;
192
193 for x in c_get_enty_attr_value(cv_attribute_id => p_enty_attr_val_rec.attribute_id,
194 cv_entity_id => p_enty_attr_val_rec.entity_id,
195 cv_entity => p_enty_attr_val_rec.entity
196 )
197 loop
198
199 l_already_exists := 'Y';
200
201 end loop;
202
203 PVX_Utility_PVT.debug_message(l_full_name ||': l_already_exists ' || l_already_exists);
204
205 if(l_already_exists = 'Y'
206 and not (l_attribute_type = 'DROPDOWN'
207 and l_DISPLAY_STYLE in ('EXTERNAL_LOV','MULTI','CHECK','PERCENTAGE')
208 )
209 ) then
210 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
211 Fnd_Message.set_name('PV', 'PV_ENTY_ATTR_VAL_MULTI_ERROR');
212
213
214 FOR x IN (select meaning from pv_lookups
215 where lookup_type = 'PV_ATTRIBUTE_TYPE'
216 and lookup_code = l_attribute_type
217 ) LOOP
218 l_meaning := x.meaning;
219 END LOOP;
220 Fnd_Message.set_token('ATTR_TYPE',l_meaning);
221
222 FOR x IN (select meaning from pv_lookups
223 where lookup_type = 'PV_ATTR_DISPLAY_STYLE'
224 and lookup_code = l_display_style
225 ) LOOP
226 l_meaning := x.meaning;
227 END LOOP;
228 Fnd_Message.set_token('ATTR_STYLE',l_meaning);
229
230
231 Fnd_Msg_Pub.ADD;
232 END IF;
233 RAISE Fnd_Api.G_EXC_ERROR;
234
235
236 end if;
237
238
239 -- Debug Message
240 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - Calling create table handler');
241
242 -- Invoke table handler(PV_ENTY_ATTR_VALUES_PKG.Insert_Row)
243 PV_ENTY_ATTR_VALUES_PKG.Insert_Row(
244 px_enty_attr_val_id => l_enty_attr_val_rec.enty_attr_val_id
245 ,p_last_update_date => l_enty_attr_val_rec.last_update_date
246 ,p_last_updated_by => l_enty_attr_val_rec.last_updated_by
247 ,p_creation_date => l_enty_attr_val_rec.creation_date
248 ,p_created_by => l_enty_attr_val_rec.created_by
249 ,p_last_update_login => l_enty_attr_val_rec.last_update_login
250 ,px_object_version_number => l_object_version_number
251 ,p_entity => l_enty_attr_val_rec.entity
252 ,p_attribute_id => l_enty_attr_val_rec.attribute_id
253 ,p_party_id => l_enty_attr_val_rec.party_id
254 ,p_attr_value => l_enty_attr_val_rec.attr_value
255 ,p_score => l_enty_attr_val_rec.score
256 ,p_enabled_flag => l_enty_attr_val_rec.enabled_flag
257 ,p_entity_id => l_enty_attr_val_rec.entity_id
258 -- p_security_group_id => p_enty_attr_val_rec.security_group_id
259 ,p_version => l_enty_attr_val_rec.version
260 ,p_latest_flag => l_enty_attr_val_rec.latest_flag
261 ,p_attr_value_extn => l_enty_attr_val_rec.attr_value_extn
262 ,p_validation_id => l_enty_attr_val_rec.validation_id
263 );
264
265
266 --DBMS_OUTPUT.PUT_LINE(l_full_name||' : After' );
267
268 x_enty_attr_val_id := l_enty_attr_val_id;
269
270 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
271 RAISE FND_API.G_EXC_ERROR;
272 END IF;
273 --
274 -- End of API body
275 --
276
277 -- Standard check for p_commit
278 IF FND_API.to_Boolean( p_commit )
279 THEN
280 COMMIT WORK;
281 END IF;
282
283
284 -- Debug Message
285 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
286 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - end');
287 END IF;
288
289 -- Standard call to get message count and if count is 1, get message info.
290 FND_MSG_PUB.Count_And_Get (
291 p_count => x_msg_count
292 ,p_data => x_msg_data
293 );
294
295 EXCEPTION
296 /*
297 WHEN PVX_Utility_PVT.resource_locked THEN
298 x_return_status := FND_API.g_ret_sts_error;
299 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
300 */
301 WHEN FND_API.G_EXC_ERROR THEN
302 ROLLBACK TO Create_Attr_Value_PVT;
303 x_return_status := FND_API.G_RET_STS_ERROR;
304 -- Standard call to get message count and if count=1, get the message
305 FND_MSG_PUB.Count_And_Get (
306 p_encoded => FND_API.G_FALSE
307 ,p_count => x_msg_count
308 ,p_data => x_msg_data
309 );
310
311 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
312 ROLLBACK TO Create_Attr_Value_PVT;
313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
314 -- Standard call to get message count and if count=1, get the message
315 FND_MSG_PUB.Count_And_Get (
316 p_encoded => FND_API.G_FALSE
317 ,p_count => x_msg_count
318 ,p_data => x_msg_data
319 );
320
321 WHEN OTHERS THEN
322 ROLLBACK TO Create_Attr_Value_PVT;
323 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
324 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
325 THEN
326 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
327 END IF;
328 -- Standard call to get message count and if count=1, get the message
329 FND_MSG_PUB.Count_And_Get (
330 p_encoded => FND_API.G_FALSE
331 ,p_count => x_msg_count
332 ,p_data => x_msg_data
333 );
334 End Create_Attr_Value;
335
336
337 PROCEDURE Update_Attr_Value(
338 p_api_version_number IN NUMBER
339 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
340 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
341 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
342
343 ,x_return_status OUT NOCOPY VARCHAR2
344 ,x_msg_count OUT NOCOPY NUMBER
345 ,x_msg_data OUT NOCOPY VARCHAR2
346
347 ,p_enty_attr_val_rec IN enty_attr_val_rec_type
348 ,x_object_version_number OUT NOCOPY NUMBER
349 )
350 IS
351
352 CURSOR c_get_enty_attr_value(cv_enty_attr_val_id NUMBER) IS
353 SELECT *
354 FROM PV_ENTY_ATTR_VALUES
355 WHERE enty_attr_val_id = cv_enty_attr_val_id;
356
357 l_api_name CONSTANT VARCHAR2(30) := 'Update_Attr_Value';
358 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
359 l_api_version_number CONSTANT NUMBER := 1.0;
360 -- Local Variables
361 l_object_version_number NUMBER;
362 l_enty_attr_val_id NUMBER;
363 l_ref_enty_attr_val_rec c_get_Enty_Attr_Value%ROWTYPE ;
364 l_tar_enty_attr_val_rec PV_Enty_Attr_Value_PVT.enty_attr_val_rec_type := p_enty_attr_val_rec;
365 l_rowid ROWID;
366
367 BEGIN
368 -- Standard Start of API savepoint
369 SAVEPOINT Update_Attr_Value_PVT;
370
371 -- Standard call to check for call compatibility.
372 IF NOT FND_API.Compatible_API_Call (
373 l_api_version_number
374 ,p_api_version_number
375 ,l_api_name
376 ,G_PKG_NAME
377 )
378 THEN
379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
380 END IF;
381
382 -- Initialize message list if p_init_msg_list is set to TRUE.
383 IF FND_API.to_Boolean( p_init_msg_list )
384 THEN
385 FND_MSG_PUB.initialize;
386 END IF;
387
388 -- Debug Message
389 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
390 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - start');
391 END IF;
392
393 -- Initialize API return status to SUCCESS
394 x_return_status := FND_API.G_RET_STS_SUCCESS;
395
396 -- Debug Message
397 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
398 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - Open Cursor to Select');
399 END IF;
400
401 OPEN c_get_Enty_Attr_Value( l_tar_enty_attr_val_rec.enty_attr_val_id);
402
403 FETCH c_get_Enty_Attr_Value INTO l_ref_enty_attr_val_rec ;
404
405 If ( c_get_Enty_Attr_Value%NOTFOUND) THEN
406 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
407 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_ENTITY');
408 FND_MESSAGE.set_token('MODE','Update');
409 FND_MESSAGE.set_token('ENTITY','Enty_Attr_Value');
410 FND_MESSAGE.set_token('ID',TO_CHAR(l_tar_enty_attr_val_rec.enty_attr_val_id));
414 END IF;
411 FND_MSG_PUB.add;
412 END IF;
413 RAISE FND_API.G_EXC_ERROR;
415
416 -- Debug Message
417 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
418 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - Close Cursor');
419 END IF;
420 CLOSE c_get_Enty_Attr_Value;
421
422
423 If (l_tar_enty_attr_val_rec.object_version_number is NULL or
424 l_tar_enty_attr_val_rec.object_version_number = FND_API.G_MISS_NUM ) Then
425 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
426 FND_MESSAGE.set_name('PV', 'PV_API_VERSION_MISSING');
427 FND_MESSAGE.set_token('COLUMN','Last_Update_Date');
428 FND_MSG_PUB.add;
429 END IF;
430 RAISE FND_API.G_EXC_ERROR;
431 End if;
432
433 -- Check Whether record has been changed by someone else
434 If (l_tar_enty_attr_val_rec.object_version_number <> l_ref_enty_attr_val_rec.object_version_number) Then
435 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
436 FND_MESSAGE.set_name('PV', 'PV_API_RECORD_CHANGED');
437 FND_MESSAGE.set_token('VALUE','Enty_Attr_Value');
438 FND_MSG_PUB.add;
439 END IF;
440 RAISE FND_API.G_EXC_ERROR;
441 End if;
442 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
443 THEN
444 --Now validating entity attribute value
445 --We would not validate for entity ENRQ.
446 if(p_enty_attr_val_rec.entity <> 'ENRQ')
447 then
448 -- Debug message
449 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
450 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - Validate_attr_value');
451 END IF;
452
453 -- Invoke validation procedures
454 Validate_attr_value(
455 p_api_version_number => 1.0
456 ,p_init_msg_list => FND_API.G_FALSE
457 ,p_validation_level => p_validation_level
458 ,p_validation_mode => JTF_PLSQL_API.g_update
459 ,p_enty_attr_val_rec => p_enty_attr_val_rec
460 ,x_return_status => x_return_status
461 ,x_msg_count => x_msg_count
462 ,x_msg_data => x_msg_data
463 );
464
465 END IF;
466 END IF;
467
468 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
469 RAISE FND_API.G_EXC_ERROR;
470 END IF;
471
472
473 -- Debug Message
474 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
475 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - Calling update table handler');
476 END IF;
477
478 -- Invoke table handler(PV_ENTY_ATTR_VALUES_PKG.Update_Row)
479 PV_ENTY_ATTR_VALUES_PKG.Update_Row(
480 p_enty_attr_val_id => p_enty_attr_val_rec.enty_attr_val_id
484 -- p_created_by => G_USER_ID
481 ,p_last_update_date => SYSDATE
482 ,p_last_updated_by => G_USER_ID
483 -- p_creation_date => SYSDATE
485 ,p_last_update_login => G_LOGIN_ID
486 ,p_object_version_number => p_enty_attr_val_rec.object_version_number
487 ,p_entity => p_enty_attr_val_rec.entity
488 ,p_attribute_id => p_enty_attr_val_rec.attribute_id
489 ,p_party_id => p_enty_attr_val_rec.party_id
490 ,p_attr_value => p_enty_attr_val_rec.attr_value
491 ,p_score => p_enty_attr_val_rec.score
492 ,p_enabled_flag => p_enty_attr_val_rec.enabled_flag
493 ,p_entity_id => p_enty_attr_val_rec.entity_id
494 -- p_security_group_id => p_enty_attr_val_rec.security_group_id
495 ,p_version => p_enty_attr_val_rec.version
496 ,p_latest_flag => p_enty_attr_val_rec.latest_flag
497 ,p_attr_value_extn => p_enty_attr_val_rec.attr_value_extn
498 ,p_validation_id => p_enty_attr_val_rec.validation_id
499 );
500
501 x_object_version_number := p_enty_attr_val_rec.object_version_number + 1;
502 --
503 -- End of API body.
504 --
505
506 -- Standard check for p_commit
507 IF FND_API.to_Boolean( p_commit )
508 THEN
509 COMMIT WORK;
510 END IF;
511
512 -- Debug Message
513 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
514 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - end');
515 END IF;
516 -- Standard call to get message count and if count is 1, get message info.
517 FND_MSG_PUB.Count_And_Get (
518 p_count => x_msg_count
519 ,p_data => x_msg_data
520 );
521 EXCEPTION
522 /*
523 WHEN PVX_Utility_PVT.resource_locked THEN
524 x_return_status := FND_API.g_ret_sts_error;
525 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
526 */
527 WHEN FND_API.G_EXC_ERROR THEN
528 ROLLBACK TO Update_Attr_Value_PVT;
529 x_return_status := FND_API.G_RET_STS_ERROR;
530 -- Standard call to get message count and if count=1, get the message
531 FND_MSG_PUB.Count_And_Get (
532 p_encoded => FND_API.G_FALSE
533 ,p_count => x_msg_count
534 ,p_data => x_msg_data
535 );
536
537 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
538 ROLLBACK TO Update_Attr_Value_PVT;
539 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
540 -- Standard call to get message count and if count=1, get the message
541 FND_MSG_PUB.Count_And_Get (
542 p_encoded => FND_API.G_FALSE
543 ,p_count => x_msg_count
544 ,p_data => x_msg_data
545 );
546
547 WHEN OTHERS THEN
548 ROLLBACK TO Update_Attr_Value_PVT;
549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
550 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
551 THEN
552 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
553 END IF;
554 -- Standard call to get message count and if count=1, get the message
555 FND_MSG_PUB.Count_And_Get (
556 p_encoded => FND_API.G_FALSE
557 ,p_count => x_msg_count
558 ,p_data => x_msg_data
559 );
560 End Update_Attr_Value;
561
562
563 PROCEDURE Delete_Attr_Value(
564 p_api_version_number IN NUMBER
565 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
566 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
567 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
568
569 ,x_return_status OUT NOCOPY VARCHAR2
570 ,x_msg_count OUT NOCOPY NUMBER
571 ,x_msg_data OUT NOCOPY VARCHAR2
572
573 ,p_enty_attr_val_id IN NUMBER
574 ,p_object_version_number IN NUMBER
575 )
576
577 IS
578 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attr_Value';
579 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
580 l_api_version_number CONSTANT NUMBER := 1.0;
581 l_object_version_number NUMBER;
582
583 BEGIN
584 -- Standard Start of API savepoint
585 SAVEPOINT Delete_Attr_Value_PVT;
586
587 -- Standard call to check for call compatibility.
588 IF NOT FND_API.Compatible_API_Call (
589 l_api_version_number
590 ,p_api_version_number
591 ,l_api_name
592 ,G_PKG_NAME
593 )
594 THEN
595 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
596 END IF;
597
598 -- Initialize message list if p_init_msg_list is set to TRUE.
599 IF FND_API.to_Boolean( p_init_msg_list )
600 THEN
601 FND_MSG_PUB.initialize;
602 END IF;
603
604 -- Debug Message
605 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
606 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - start');
607 END IF;
611 --
608 -- Initialize API return status to SUCCESS
609 x_return_status := FND_API.G_RET_STS_SUCCESS;
610
612 -- Api body
613 --
614 -- Debug Message
615 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
616 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - Calling delete table handler');
617 END IF;
618 -- Invoke table handler(PV_ENTY_ATTR_VALUES_PKG.Delete_Row)
619 PV_ENTY_ATTR_VALUES_PKG.Delete_Row(
620 p_ENTY_ATTR_VAL_ID => p_ENTY_ATTR_VAL_ID);
621 --
622 -- End of API body
623 --
624
625 -- Standard check for p_commit
626 IF FND_API.to_Boolean( p_commit )
627 THEN
628 COMMIT WORK;
629 END IF;
630
631 -- Debug Message
632 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
633 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - end');
634 END IF;
635 -- Standard call to get message count and if count is 1, get message info.
636 FND_MSG_PUB.Count_And_Get (
637 p_count => x_msg_count
638 ,p_data => x_msg_data
639 );
640 EXCEPTION
641 /*
642 WHEN PVX_Utility_PVT.resource_locked THEN
643 x_return_status := FND_API.g_ret_sts_error;
644 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
645 */
646 WHEN FND_API.G_EXC_ERROR THEN
647 ROLLBACK TO Delete_Attr_Value_PVT;
648 x_return_status := FND_API.G_RET_STS_ERROR;
649 -- Standard call to get message count and if count=1, get the message
650 FND_MSG_PUB.Count_And_Get (
651 p_encoded => FND_API.G_FALSE
652 ,p_count => x_msg_count
653 ,p_data => x_msg_data
654 );
655
656 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
657 ROLLBACK TO Delete_Attr_Value_PVT;
658 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
659 -- Standard call to get message count and if count=1, get the message
660 FND_MSG_PUB.Count_And_Get (
661 p_encoded => FND_API.G_FALSE
662 ,p_count => x_msg_count
663 ,p_data => x_msg_data
664 );
665
666 WHEN OTHERS THEN
667 ROLLBACK TO Delete_Attr_Value_PVT;
668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
669 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
670 THEN
671 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
672 END IF;
673 -- Standard call to get message count and if count=1, get the message
674 FND_MSG_PUB.Count_And_Get (
675 p_encoded => FND_API.G_FALSE
676 ,p_count => x_msg_count
677 ,p_data => x_msg_data
678 );
679 End Delete_Attr_Value;
680
681
682
683 -- Hint: Primary key needs to be returned.
684 PROCEDURE Lock_Attr_Value(
685 p_api_version_number IN NUMBER
686 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
687
688 ,x_return_status OUT NOCOPY VARCHAR2
689 ,x_msg_count OUT NOCOPY NUMBER
690 ,x_msg_data OUT NOCOPY VARCHAR2
691
692 ,p_enty_attr_val_id IN NUMBER
693 ,p_object_version IN NUMBER
694 )
695
696 IS
697 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Attr_Value';
698 l_api_version_number CONSTANT NUMBER := 1.0;
699 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
700 l_ENTY_ATTR_VAL_ID NUMBER;
701
702 CURSOR c_Enty_Attr_Value IS
703 SELECT ENTY_ATTR_VAL_ID
704 FROM PV_ENTY_ATTR_VALUES
705 WHERE ENTY_ATTR_VAL_ID = p_ENTY_ATTR_VAL_ID
706 AND object_version_number = p_object_version
707 FOR UPDATE NOWAIT;
708
709 BEGIN
710
711 -- Debug Message
712 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
713 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - start');
714 END IF;
715 -- Initialize message list if p_init_msg_list is set to TRUE.
716 IF FND_API.to_Boolean( p_init_msg_list )
717 THEN
718 FND_MSG_PUB.initialize;
719 END IF;
720
721 -- Standard call to check for call compatibility.
722 IF NOT FND_API.Compatible_API_Call (
723 l_api_version_number
724 ,p_api_version_number
725 ,l_api_name
726 ,G_PKG_NAME
727 )
728 THEN
729 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
730 END IF;
731
732 -- Initialize API return status to SUCCESS
733 x_return_status := FND_API.G_RET_STS_SUCCESS;
734
735
736 ------------------------ lock -------------------------
737 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
738 PVX_Utility_PVT.debug_message(l_full_name||': start');
739 END IF;
740 OPEN c_Enty_Attr_Value;
741
742 FETCH c_Enty_Attr_Value INTO l_ENTY_ATTR_VAL_ID;
743
744 IF (c_Enty_Attr_Value%NOTFOUND) THEN
745 CLOSE c_Enty_Attr_Value;
746 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
750 RAISE FND_API.g_exc_error;
747 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
748 FND_MSG_PUB.add;
749 END IF;
751 END IF;
752
753 CLOSE c_Enty_Attr_Value;
754
755 -------------------- finish --------------------------
756 FND_MSG_PUB.count_and_get(
757 p_encoded => FND_API.g_false
758 ,p_count => x_msg_count
759 ,p_data => x_msg_data
760 );
761 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
762 PVX_Utility_PVT.debug_message(l_full_name ||': end');
763 END IF;
764 EXCEPTION
765 /*
766 WHEN PVX_Utility_PVT.resource_locked THEN
767 x_return_status := FND_API.g_ret_sts_error;
768 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
769 */
770 WHEN FND_API.G_EXC_ERROR THEN
771 ROLLBACK TO Lock_Attr_Value_PVT;
772 x_return_status := FND_API.G_RET_STS_ERROR;
773 -- Standard call to get message count and if count=1, get the message
774 FND_MSG_PUB.Count_And_Get (
775 p_encoded => FND_API.G_FALSE
776 ,p_count => x_msg_count
777 ,p_data => x_msg_data
778 );
779
780 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
781 ROLLBACK TO Lock_Attr_Value_PVT;
782 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
783 -- Standard call to get message count and if count=1, get the message
784 FND_MSG_PUB.Count_And_Get (
785 p_encoded => FND_API.G_FALSE
786 ,p_count => x_msg_count
787 ,p_data => x_msg_data
788 );
789
790 WHEN OTHERS THEN
791 ROLLBACK TO Lock_Attr_Value_PVT;
792 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
793 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
794 THEN
795 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
796 END IF;
797 -- Standard call to get message count and if count=1, get the message
798 FND_MSG_PUB.Count_And_Get (
799 p_encoded => FND_API.G_FALSE
800 ,p_count => x_msg_count
801 ,p_data => x_msg_data
802 );
803 End Lock_Attr_Value;
804
805
806 PROCEDURE check_uk_items(
807 p_enty_attr_val_rec IN enty_attr_val_rec_type
808 ,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
809 ,x_return_status OUT NOCOPY VARCHAR2)
810 IS
811 l_valid_flag VARCHAR2(1);
812
813 BEGIN
814
815 --DBMS_OUTPUT.PUT_LINE ('entering check_uk_items');
816
817 x_return_status := FND_API.g_ret_sts_success;
818 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
819 l_valid_flag := PVX_Utility_PVT.check_uniqueness(
820 'PV_ENTY_ATTR_VALUES'
821 ,'ENTY_ATTR_VAL_ID = ''' || p_enty_attr_val_rec.ENTY_ATTR_VAL_ID ||''''
822 );
823 ELSE
824 l_valid_flag := PVX_Utility_PVT.check_uniqueness(
825 'PV_ENTY_ATTR_VALUES'
826 ,'ENTY_ATTR_VAL_ID = ''' || p_enty_attr_val_rec.ENTY_ATTR_VAL_ID ||
827 ''' AND ENTY_ATTR_VAL_ID <> ' || p_enty_attr_val_rec.ENTY_ATTR_VAL_ID
828 );
829 END IF;
830
831 IF l_valid_flag = FND_API.g_false THEN
832 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
833 FND_MESSAGE.set_name('PV', 'PV_API_DUPLICATE_ENTITY');
834 FND_MESSAGE.set_token('ID',to_char(p_enty_attr_val_rec.ENTY_ATTR_VAL_ID) );
835 FND_MESSAGE.set_token('ENTITY','Enty_Attr_Value');
836 FND_MSG_PUB.add;
837 END IF;
838 x_return_status := FND_API.g_ret_sts_error;
839 RETURN;
840 END IF;
841
842 --DBMS_OUTPUT.PUT_LINE ('leaving check_uk_items');
843
844 END check_uk_items;
845
846 PROCEDURE check_req_items(
847 p_enty_attr_val_rec IN enty_attr_val_rec_type
848 ,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
849 ,x_return_status OUT NOCOPY VARCHAR2
850 )
851 IS
852 BEGIN
853 x_return_status := FND_API.g_ret_sts_success;
854 --DBMS_OUTPUT.PUT_LINE('check_req_items::p_validation_mode = '||p_validation_mode);
855 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
856
857 --DBMS_OUTPUT.PUT_LINE('Before calling enty_attr_val_id');
858 --DBMS_OUTPUT.PUT_LINE('p_enty_attr_val_rec.enty_attr_val_id = '||
859 -- TO_CHAR(p_enty_attr_val_rec.enty_attr_val_id));
860
861
862 IF p_enty_attr_val_rec.enty_attr_val_id = FND_API.g_miss_num
863 OR p_enty_attr_val_rec.enty_attr_val_id IS NULL THEN
864 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
865 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
866 FND_MESSAGE.set_token('COLUMN','enty_attr_val_id');
867 FND_MSG_PUB.add;
868 END IF;
869 x_return_status := FND_API.g_ret_sts_error;
870 RETURN;
871 END IF;
872
873 --DBMS_OUTPUT.PUT_LINE('p_enty_attr_val_rec.LAST_UPDATE_DATE = '||
874 -- TO_CHAR(p_enty_attr_val_rec.last_update_date));
875
876 IF p_enty_attr_val_rec.last_update_date = FND_API.g_miss_date
877 OR p_enty_attr_val_rec.last_update_date IS NULL THEN
878
879 --DBMS_OUTPUT.PUT_LINE('p_enty_attr_val_rec.LAST_UPDATE_DATE = '||
880 -- TO_CHAR(p_enty_attr_val_rec.last_update_date));
881 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
885 END IF;
882 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
883 FND_MESSAGE.set_token('COLUMN','last_update_date');
884 FND_MSG_PUB.add;
886 x_return_status := FND_API.g_ret_sts_error;
887 RETURN;
888 END IF;
889
890 --DBMS_OUTPUT.PUT_LINE('p_enty_attr_val_rec.last_updated_by = '||
891 -- TO_CHAR(p_enty_attr_val_rec.last_updated_by));
892
893 IF p_enty_attr_val_rec.last_updated_by = FND_API.g_miss_num
894 OR p_enty_attr_val_rec.last_updated_by IS NULL THEN
895 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
896 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
897 FND_MESSAGE.set_token('COLUMN','last_updated_by');
898 FND_MSG_PUB.add;
899 END IF;
900 x_return_status := FND_API.g_ret_sts_error;
901 RETURN;
902 END IF;
903
904
905 IF p_enty_attr_val_rec.creation_date = FND_API.g_miss_date
906 OR p_enty_attr_val_rec.creation_date IS NULL THEN
907 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
908 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
909 FND_MESSAGE.set_token('COLUMN','creation_date');
910 FND_MSG_PUB.add;
911 END IF;
912 x_return_status := FND_API.g_ret_sts_error;
913 RETURN;
914 END IF;
915
916
917 IF p_enty_attr_val_rec.created_by = FND_API.g_miss_num
918 OR p_enty_attr_val_rec.created_by IS NULL THEN
919 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
920 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
921 FND_MESSAGE.set_token('COLUMN','created_by');
922 FND_MSG_PUB.add;
923 END IF;
924 x_return_status := FND_API.g_ret_sts_error;
925 RETURN;
926 END IF;
927
928
929 IF p_enty_attr_val_rec.last_update_login = FND_API.g_miss_num
930 OR p_enty_attr_val_rec.last_update_login IS NULL THEN
931 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
932 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
933 FND_MESSAGE.set_token('COLUMN','last_update_login');
934 FND_MSG_PUB.add;
935 END IF;
936 x_return_status := FND_API.g_ret_sts_error;
937 RETURN;
938 END IF;
939
940
941 IF p_enty_attr_val_rec.object_version_number = FND_API.g_miss_num
942 OR p_enty_attr_val_rec.object_version_number IS NULL THEN
943 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
944 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
945 FND_MESSAGE.set_token('COLUMN','object_version_number');
946 FND_MSG_PUB.add;
947 END IF;
948 x_return_status := FND_API.g_ret_sts_error;
949 RETURN;
950 END IF;
951
952
953 IF p_enty_attr_val_rec.entity = FND_API.g_miss_char
954 OR p_enty_attr_val_rec.entity IS NULL THEN
955 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
956 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
957 FND_MESSAGE.set_token('COLUMN','entity');
958 FND_MSG_PUB.add;
959 END IF;
960 x_return_status := FND_API.g_ret_sts_error;
961 RETURN;
962 END IF;
963
964
965 IF p_enty_attr_val_rec.attribute_id = FND_API.g_miss_num
966 OR p_enty_attr_val_rec.attribute_id IS NULL THEN
967 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
968 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
969 FND_MESSAGE.set_token('COLUMN','attribute_id');
970 FND_MSG_PUB.add;
971 END IF;
972 x_return_status := FND_API.g_ret_sts_error;
973 RETURN;
974 END IF;
975
976
977 IF p_enty_attr_val_rec.enabled_flag = FND_API.g_miss_char
978 OR p_enty_attr_val_rec.enabled_flag IS NULL THEN
979 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
980 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
981 FND_MESSAGE.set_token('COLUMN','enabled_flag');
982 FND_MSG_PUB.add;
983 END IF;
984 x_return_status := FND_API.g_ret_sts_error;
985 RETURN;
986 END IF;
987
988
989 IF p_enty_attr_val_rec.entity_id = FND_API.g_miss_num
990 OR p_enty_attr_val_rec.entity_id IS NULL THEN
991 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
992 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
993 FND_MESSAGE.set_token('COLUMN','entity_id');
994 FND_MSG_PUB.add;
995 END IF;
996 x_return_status := FND_API.g_ret_sts_error;
997 RETURN;
998 END IF;
999
1000 ELSE
1001
1002 IF p_enty_attr_val_rec.enty_attr_val_id IS NULL THEN
1003 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1004 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1005 FND_MESSAGE.set_token('COLUMN','enty_attr_val_id');
1006 FND_MSG_PUB.add;
1007 END IF;
1008 x_return_status := FND_API.g_ret_sts_error;
1009 RETURN;
1010 END IF;
1011
1012
1016 FND_MESSAGE.set_token('COLUMN','last_update_date');
1013 IF p_enty_attr_val_rec.last_update_date IS NULL THEN
1014 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1015 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1017 FND_MSG_PUB.add;
1018 END IF;
1019 x_return_status := FND_API.g_ret_sts_error;
1020 RETURN;
1021 END IF;
1022
1023
1024 IF p_enty_attr_val_rec.last_updated_by IS NULL THEN
1025 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1026 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1027 FND_MESSAGE.set_token('COLUMN','last_updated_by');
1028 FND_MSG_PUB.add;
1029 END IF;
1030 x_return_status := FND_API.g_ret_sts_error;
1031 RETURN;
1032 END IF;
1033
1034
1035 IF p_enty_attr_val_rec.creation_date IS NULL THEN
1036 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1037 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1038 FND_MESSAGE.set_token('COLUMN','creation_date');
1039 FND_MSG_PUB.add;
1040 END IF;
1041 x_return_status := FND_API.g_ret_sts_error;
1042 RETURN;
1043 END IF;
1044
1045
1046 IF p_enty_attr_val_rec.created_by IS NULL THEN
1047
1048 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1049 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1050 FND_MESSAGE.set_token('COLUMN','created_by');
1051 FND_MSG_PUB.add;
1052 END IF;
1053 x_return_status := FND_API.g_ret_sts_error;
1054 RETURN;
1055 END IF;
1056
1057
1058 IF p_enty_attr_val_rec.last_update_login IS NULL THEN
1059 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1060 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1061 FND_MESSAGE.set_token('COLUMN','last_update_login');
1062 FND_MSG_PUB.add;
1063 END IF;
1064 x_return_status := FND_API.g_ret_sts_error;
1065 RETURN;
1066 END IF;
1067
1068
1069 IF p_enty_attr_val_rec.object_version_number IS NULL THEN
1070 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1071 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1072 FND_MESSAGE.set_token('COLUMN','object_version_number');
1073 FND_MSG_PUB.add;
1074 END IF;
1075 x_return_status := FND_API.g_ret_sts_error;
1076 RETURN;
1077 END IF;
1078
1079
1080 IF p_enty_attr_val_rec.entity IS NULL THEN
1081 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1082 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1083 FND_MESSAGE.set_token('COLUMN','entity');
1084 FND_MSG_PUB.add;
1085 END IF;
1086 x_return_status := FND_API.g_ret_sts_error;
1087 RETURN;
1088 END IF;
1089
1090
1091 IF p_enty_attr_val_rec.attribute_id IS NULL THEN
1092 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1093 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1094 FND_MESSAGE.set_token('COLUMN','attribute_id');
1095 FND_MSG_PUB.add;
1096 END IF;
1097 x_return_status := FND_API.g_ret_sts_error;
1098 RETURN;
1099 END IF;
1100
1101
1102 IF p_enty_attr_val_rec.enabled_flag IS NULL THEN
1103 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1104 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1105 FND_MESSAGE.set_token('COLUMN','enabled_flag');
1106 FND_MSG_PUB.add;
1107 END IF;
1108 x_return_status := FND_API.g_ret_sts_error;
1109 RETURN;
1110 END IF;
1111
1112
1113 IF p_enty_attr_val_rec.entity_id IS NULL THEN
1114 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1115 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1116 FND_MESSAGE.set_token('COLUMN','entity_id');
1117 FND_MSG_PUB.add;
1118 END IF;
1119 x_return_status := FND_API.g_ret_sts_error;
1120 RETURN;
1121 END IF;
1122 END IF;
1123
1124 END check_req_items;
1125
1126 PROCEDURE check_FK_items(
1127 p_enty_attr_val_rec IN enty_attr_val_rec_type
1128 ,x_return_status OUT NOCOPY VARCHAR2
1129 )
1130 IS
1131
1132 CURSOR c_check_attr_exists(cv_attribute_id NUMBER) IS
1133 SELECT 1
1134 FROM PV_ATTRIBUTES_b
1135 WHERE attribute_id = cv_attribute_id
1136 ;
1137 CURSOR c_check_entattr_exists(cv_attribute_id NUMBER, cv_entity VARCHAR2) IS
1138 SELECT 1
1139 FROM PV_ENTITY_ATTRS
1140 WHERE attribute_id = cv_attribute_id and entity = cv_entity
1141 ;
1142
1143 l_exists BOOLEAN := false;
1144 l_api_name CONSTANT VARCHAR2(30) := 'Create_Attr_Value';
1145 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1146
1147 BEGIN
1148 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1149 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - start');
1150 END IF;
1151
1152 x_return_status := FND_API.g_ret_sts_success;
1153
1154 -- Enter custom code here
1155
1159
1156 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1157 PVX_Utility_PVT.debug_message('Attribute Id:'|| p_enty_attr_val_rec.attribute_id||': - entity:' || p_enty_attr_val_rec.entity );
1158 END IF;
1160 for x in c_check_attr_exists(cv_attribute_id => p_enty_attr_val_rec.attribute_id )
1161 loop
1162 l_exists := true;
1163 end loop;
1164
1165 if(l_exists = false) then
1166 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1167 FND_MESSAGE.set_name('PV', 'PV_ATTRIBUTE_NOT_EXISTS');
1168 FND_MESSAGE.set_token('ATTRIBUTE_ID',p_enty_attr_val_rec.attribute_id);
1169 FND_MSG_PUB.add;
1170 END IF;
1171 x_return_status := FND_API.g_ret_sts_error;
1172 return;
1173 end if;
1174
1175 l_exists := false;
1176
1177 for x in c_check_entattr_exists(cv_attribute_id => p_enty_attr_val_rec.attribute_id, cv_entity => p_enty_attr_val_rec.entity )
1178 loop
1179 l_exists := true;
1180 end loop;
1181
1182 if(l_exists = false) then
1183 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1184 FND_MESSAGE.set_name('PV', 'PV_ATTR_ENTITY_NOT_EXISTS');
1185 FND_MESSAGE.set_token('ATTRIBUTE_ID',p_enty_attr_val_rec.attribute_id);
1186 FND_MESSAGE.set_token('ENTITY',p_enty_attr_val_rec.entity);
1187 FND_MSG_PUB.add;
1188 END IF;
1189 x_return_status := FND_API.g_ret_sts_error;
1190 return;
1191 end if;
1192
1193
1194 END check_FK_items;
1195
1196 PROCEDURE check_Lookup_items(
1197 p_enty_attr_val_rec IN enty_attr_val_rec_type
1198 ,x_return_status OUT NOCOPY VARCHAR2
1199 )
1200 IS
1201 BEGIN
1202 x_return_status := FND_API.g_ret_sts_success;
1203
1204 -- Enter custom code here
1205
1206 END check_Lookup_items;
1207
1208 PROCEDURE Check_attr_value_Items (
1209 p_enty_attr_val_rec IN enty_attr_val_rec_type
1210 ,p_validation_mode IN VARCHAR2
1211 ,x_return_status OUT NOCOPY VARCHAR2
1212 )
1213 IS
1214 l_api_name CONSTANT VARCHAR2(30) := 'Check_attr_value_Items';
1215 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1216
1217 BEGIN
1218
1219 -- Check Items Uniqueness API calls
1220
1221 --DBMS_OUTPUT.PUT_LINE(l_full_name||' : Before check_uk_items' );
1222
1223 check_uk_items(
1224 p_enty_attr_val_rec => p_enty_attr_val_rec
1225 ,p_validation_mode => p_validation_mode
1226 ,x_return_status => x_return_status
1227 );
1228 IF x_return_status <> FND_API.g_ret_sts_success THEN
1229 RETURN;
1230 END IF;
1231
1232 -- Check Items Required/NOT NULL API calls
1233
1234 --DBMS_OUTPUT.PUT_LINE(l_full_name||' : Before check_req_items' );
1235
1236 check_req_items(
1237 p_enty_attr_val_rec => p_enty_attr_val_rec
1238 ,p_validation_mode => p_validation_mode
1239 ,x_return_status => x_return_status
1240 );
1241 IF x_return_status <> FND_API.g_ret_sts_success THEN
1242 RETURN;
1243 END IF;
1244
1245 -- Check Items Foreign Keys API calls
1246
1247 --DBMS_OUTPUT.PUT_LINE(l_full_name||' : Before check_FK_items' );
1248
1249 check_FK_items(
1250 p_enty_attr_val_rec => p_enty_attr_val_rec
1251 ,x_return_status => x_return_status
1252 );
1253 IF x_return_status <> FND_API.g_ret_sts_success THEN
1254 RETURN;
1255 END IF;
1256
1257 -- Check Items Lookups
1258
1259 --DBMS_OUTPUT.PUT_LINE(l_full_name||' : Before check_Lookup_items' );
1260
1261 check_Lookup_items(
1262 p_enty_attr_val_rec => p_enty_attr_val_rec
1263 ,x_return_status => x_return_status
1264 );
1265 IF x_return_status <> FND_API.g_ret_sts_success THEN
1266 RETURN;
1267 END IF;
1268
1269 END Check_attr_value_Items;
1270
1271 PROCEDURE Complete_enty_attr_val_rec (
1272 p_enty_attr_val_rec IN enty_attr_val_rec_type
1273 ,x_complete_rec OUT NOCOPY enty_attr_val_rec_type
1274 )
1275 IS
1276 l_return_status VARCHAR2(1);
1277
1278 CURSOR c_complete IS
1279 SELECT *
1280 FROM pv_enty_attr_values
1281 WHERE enty_attr_val_id = p_enty_attr_val_rec.enty_attr_val_id;
1282 l_pv_enty_attr_val_rec c_complete%ROWTYPE;
1283 BEGIN
1284 x_complete_rec := p_enty_attr_val_rec;
1285
1286
1287 OPEN c_complete;
1288 FETCH c_complete INTO l_pv_enty_attr_val_rec;
1289 CLOSE c_complete;
1290
1291 -- enty_attr_val_id
1292 IF p_enty_attr_val_rec.enty_attr_val_id = FND_API.g_miss_num THEN
1293 x_complete_rec.enty_attr_val_id := l_pv_enty_attr_val_rec.enty_attr_val_id;
1294 END IF;
1295
1296 -- last_update_date
1297 IF p_enty_attr_val_rec.last_update_date = FND_API.g_miss_date THEN
1298 x_complete_rec.last_update_date := l_pv_enty_attr_val_rec.last_update_date;
1299 END IF;
1300
1301 -- last_updated_by
1302 IF p_enty_attr_val_rec.last_updated_by = FND_API.g_miss_num THEN
1303 x_complete_rec.last_updated_by := l_pv_enty_attr_val_rec.last_updated_by;
1304 END IF;
1305
1306 -- creation_date
1307 IF p_enty_attr_val_rec.creation_date = FND_API.g_miss_date THEN
1308 x_complete_rec.creation_date := l_pv_enty_attr_val_rec.creation_date;
1309 END IF;
1313 x_complete_rec.created_by := l_pv_enty_attr_val_rec.created_by;
1310
1311 -- created_by
1312 IF p_enty_attr_val_rec.created_by = FND_API.g_miss_num THEN
1314 END IF;
1315
1316 -- last_update_login
1317 IF p_enty_attr_val_rec.last_update_login = FND_API.g_miss_num THEN
1318 x_complete_rec.last_update_login := l_pv_enty_attr_val_rec.last_update_login;
1319 END IF;
1320
1321 -- object_version_number
1322 IF p_enty_attr_val_rec.object_version_number = FND_API.g_miss_num THEN
1323 x_complete_rec.object_version_number := l_pv_enty_attr_val_rec.object_version_number;
1324 END IF;
1325
1326 -- entity
1327 IF p_enty_attr_val_rec.entity = FND_API.g_miss_char THEN
1328 x_complete_rec.entity := l_pv_enty_attr_val_rec.entity;
1329 END IF;
1330
1331 -- attribute_id
1332 IF p_enty_attr_val_rec.attribute_id = FND_API.g_miss_num THEN
1333 x_complete_rec.attribute_id := l_pv_enty_attr_val_rec.attribute_id;
1334 END IF;
1335
1336 -- party_id
1337 IF p_enty_attr_val_rec.party_id = FND_API.g_miss_num THEN
1338 x_complete_rec.party_id := l_pv_enty_attr_val_rec.party_id;
1339 END IF;
1340
1341 -- attr_value
1342 IF p_enty_attr_val_rec.attr_value = FND_API.g_miss_char THEN
1343 x_complete_rec.attr_value := l_pv_enty_attr_val_rec.attr_value;
1344 END IF;
1345
1346 -- score
1347 IF p_enty_attr_val_rec.score = FND_API.g_miss_char THEN
1348 x_complete_rec.score := l_pv_enty_attr_val_rec.score;
1349 END IF;
1350
1351 -- enabled_flag
1352 IF p_enty_attr_val_rec.enabled_flag = FND_API.g_miss_char THEN
1353 x_complete_rec.enabled_flag := l_pv_enty_attr_val_rec.enabled_flag;
1354 END IF;
1355
1356 -- entity_id
1357 IF p_enty_attr_val_rec.entity_id = FND_API.g_miss_num THEN
1358 x_complete_rec.entity_id := l_pv_enty_attr_val_rec.entity_id;
1359 END IF;
1360
1361
1362 -- version
1363 IF p_enty_attr_val_rec.version = FND_API.g_miss_num THEN
1364 x_complete_rec.version := l_pv_enty_attr_val_rec.version;
1365 END IF;
1366
1367 -- latest_flag
1368 IF p_enty_attr_val_rec.latest_flag = FND_API.g_miss_char THEN
1369 x_complete_rec.latest_flag := l_pv_enty_attr_val_rec.latest_flag;
1370 END IF;
1371
1372 -- attr_value_extn
1373 IF p_enty_attr_val_rec.attr_value_extn = FND_API.g_miss_char THEN
1374 x_complete_rec.attr_value_extn := l_pv_enty_attr_val_rec.attr_value_extn;
1375 END IF;
1376
1377 -- version
1378 IF p_enty_attr_val_rec.validation_id = FND_API.g_miss_num THEN
1379 x_complete_rec.validation_id := l_pv_enty_attr_val_rec.validation_id;
1380 END IF;
1381
1382
1383 -- security_group_id
1384 -- IF p_enty_attr_val_rec.security_group_id = FND_API.g_miss_num THEN
1385 -- x_complete_rec.security_group_id := l_pv_enty_attr_val_rec.security_group_id;
1386 -- END IF;
1387 -- Note: Developers need to modify the procedure
1388 -- to handle any business specific requirements.
1389 END Complete_enty_attr_val_rec;
1390
1391 PROCEDURE Validate_attr_value(
1392 p_api_version_number IN NUMBER
1393 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1394 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1395 ,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.G_UPDATE
1396 ,p_enty_attr_val_rec IN enty_attr_val_rec_type
1397 ,x_return_status OUT NOCOPY VARCHAR2
1398 ,x_msg_count OUT NOCOPY NUMBER
1399 ,x_msg_data OUT NOCOPY VARCHAR2
1400 )
1401 IS
1402 l_api_name CONSTANT VARCHAR2(30) := 'Validate_attr_value';
1403 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1404 l_api_version_number CONSTANT NUMBER := 1.0;
1405 l_object_version_number NUMBER;
1406 l_pv_enty_attr_val_rec enty_attr_val_rec_type;
1407
1408 BEGIN
1409
1410 --DBMS_OUTPUT.PUT_LINE ('Enter Validate Procedure with mode= '||p_validation_mode);
1411
1412 -- Standard Start of API savepoint
1413 SAVEPOINT Validate_attr_value;
1414
1415 -- Standard call to check for call compatibility.
1416 IF NOT FND_API.Compatible_API_Call (
1417 l_api_version_number
1418 ,p_api_version_number
1419 ,l_api_name
1420 ,G_PKG_NAME
1421 )
1422 THEN
1423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1424 END IF;
1425
1426 -- Initialize message list if p_init_msg_list is set to TRUE.
1427 IF FND_API.to_Boolean( p_init_msg_list )
1428 THEN
1429 FND_MSG_PUB.initialize;
1430 END IF;
1431 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1432
1433 --DBMS_OUTPUT.PUT_LINE(l_full_name||' : Before Check_attr_value_Items' );
1434
1435 Check_attr_value_Items(
1436 p_enty_attr_val_rec => p_enty_attr_val_rec
1437 ,p_validation_mode => p_validation_mode
1438 ,x_return_status => x_return_status
1439 );
1440
1441 --DBMS_OUTPUT.PUT_LINE('After Check_attr_value_Items::x_return_status = '||x_return_status);
1442 --DBMS_OUTPUT.PUT_LINE(l_full_name||' : After Check_attr_value_Items' );
1443
1444 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1445 RAISE FND_API.G_EXC_ERROR;
1446 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1448 END IF;
1449 END IF;
1450
1454 );
1451 Complete_enty_attr_val_rec(
1452 p_enty_attr_val_rec => p_enty_attr_val_rec
1453 ,x_complete_rec => l_pv_enty_attr_val_rec
1455
1456 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1457 Validate_attr_val_rec(
1458 p_api_version_number => 1.0
1459 ,p_init_msg_list => FND_API.G_FALSE
1460 ,x_return_status => x_return_status
1461 ,x_msg_count => x_msg_count
1462 ,x_msg_data => x_msg_data
1463 ,p_enty_attr_val_rec => l_pv_enty_attr_val_rec
1464 ,p_validation_mode => p_validation_mode
1465 );
1466
1467 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1468 RAISE FND_API.G_EXC_ERROR;
1469 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1470 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1471 END IF;
1472 END IF;
1473
1474 -- Debug Message
1475 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1476 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - start');
1477 END IF;
1478 -- Initialize API return status to SUCCESS
1479 x_return_status := FND_API.G_RET_STS_SUCCESS;
1480
1481 -- Debug Message
1482 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1483 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - end');
1484 END IF;
1485 -- Standard call to get message count and if count is 1, get message info.
1486 FND_MSG_PUB.Count_And_Get (
1487 p_count => x_msg_count
1488 ,p_data => x_msg_data
1489 );
1490 EXCEPTION
1491 /*
1492 WHEN PVX_Utility_PVT.resource_locked THEN
1493 x_return_status := FND_API.g_ret_sts_error;
1494 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
1495 */
1496 WHEN FND_API.G_EXC_ERROR THEN
1497 ROLLBACK TO Validate_attr_value;
1498 x_return_status := FND_API.G_RET_STS_ERROR;
1499 -- Standard call to get message count and if count=1, get the message
1500 FND_MSG_PUB.Count_And_Get (
1501 p_encoded => FND_API.G_FALSE
1502 ,p_count => x_msg_count
1503 ,p_data => x_msg_data
1504 );
1505
1506 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1507 ROLLBACK TO Validate_attr_value;
1508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1509 -- Standard call to get message count and if count=1, get the message
1510 FND_MSG_PUB.Count_And_Get (
1511 p_encoded => FND_API.G_FALSE
1512 ,p_count => x_msg_count
1513 ,p_data => x_msg_data
1514 );
1515
1516 WHEN OTHERS THEN
1517 ROLLBACK TO Validate_attr_value;
1518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1519 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1520 THEN
1521 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1522 END IF;
1523 -- Standard call to get message count and if count=1, get the message
1524 FND_MSG_PUB.Count_And_Get (
1525 p_encoded => FND_API.G_FALSE
1526 ,p_count => x_msg_count
1527 ,p_data => x_msg_data
1528 );
1529 End Validate_attr_value;
1530
1531
1532 PROCEDURE Validate_attr_val_rec(
1533 p_api_version_number IN NUMBER
1534 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1535 ,x_return_status OUT NOCOPY VARCHAR2
1536 ,x_msg_count OUT NOCOPY NUMBER
1537 ,x_msg_data OUT NOCOPY VARCHAR2
1538 ,p_enty_attr_val_rec IN enty_attr_val_rec_type
1539 ,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.G_UPDATE
1540 )
1541 IS
1542 BEGIN
1543 -- Initialize message list if p_init_msg_list is set to TRUE.
1544 IF FND_API.to_Boolean( p_init_msg_list )
1545 THEN
1546 FND_MSG_PUB.initialize;
1547 END IF;
1548
1549 -- Initialize API return status to SUCCESS
1550 x_return_status := FND_API.G_RET_STS_SUCCESS;
1551
1552 -- Hint: Validate data
1553 -- If data not valid
1554 -- THEN
1555 -- x_return_status := FND_API.G_RET_STS_ERROR;
1556
1557 -- Debug Message
1558 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1559 PVX_Utility_PVT.debug_message('Private API: Validate_dm_model_rec');
1560 END IF;
1561 -- Standard call to get message count and if count is 1, get message info.
1562 FND_MSG_PUB.Count_And_Get (
1563 p_count => x_msg_count
1564 ,p_data => x_msg_data
1565 );
1566 END Validate_attr_val_rec;
1567
1568 END PV_Enty_Attr_Value_PVT;