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