[Home] [Help]
PACKAGE BODY: APPS.AMS_LIST_SRC_FIELD_PVT
Source
1 PACKAGE BODY AMS_List_Src_Field_PVT as
2 /* $Header: amsvlsfb.pls 120.1 2005/09/20 02:18:42 batoleti noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_List_Src_Field_PVT
7 -- Purpose
8 --
9 -- History
10 -- 14-Oct-2002 nyostos Added callout to AMS_DM_MODEL_PVT.
11 -- handle_data_source_changes() if analytics_flag is changed
12 -- for a data source field.
13 -- 22-Jan-2004 kbasavar Commented out validation for field
14 -- column name in procedure Update_list_Src_Field
15 -- 20-SEP-2005 batoleti Added p_column_type parameter to insert_row
16 -- and update_row procedure calls.
17 -- Refer bug# 4619184.
18 -- NOTE
19 --
20 -- End of Comments
21 -- ===============================================================
22
23
24 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_List_Src_Field_PVT';
25 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvlsfb.pls';
26
27
28 -- Hint: Primary key needs to be returned.
29 PROCEDURE Create_List_Src_Field(
30 p_api_version_number IN NUMBER,
31 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
32 p_commit IN VARCHAR2 := FND_API.G_FALSE,
33 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
34
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2,
38
39 p_list_src_field_rec IN list_src_field_rec_type := g_miss_list_src_field_rec,
40 x_list_source_field_id OUT NOCOPY NUMBER
41 )
42
43 IS
44 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_List_Src_Field';
45 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
46 l_return_status_full VARCHAR2(1);
47 l_object_version_number NUMBER := 1;
48 l_org_id NUMBER := FND_API.G_MISS_NUM;
49 l_LIST_SOURCE_FIELD_ID NUMBER;
50 l_dummy NUMBER;
51 l_table_name VARCHAR2(30);
52 l_column_name VARCHAR2(30);
53
54 CURSOR c_id IS
55 SELECT AMS_LIST_SRC_FIELDS_s.NEXTVAL
56 FROM dual;
57
58 CURSOR c_id_exists (l_id IN NUMBER) IS
59 SELECT 1
60 FROM AMS_LIST_SRC_FIELDS
61 WHERE LIST_SOURCE_FIELD_ID = l_id;
62
63 CURSOR c_get_column_name(p_table_name VARCHAR2, p_column_name VARCHAR2) IS
64 SELECT COLUMN_NAME
65 FROM sys.all_tab_columns
66 WHERE table_name = UPPER(p_table_name)
67 AND column_name = UPPER(p_column_name);
68
69 CURSOR c_get_table_name(p_list_source_type_id NUMBER) IS
70 SELECT source_object_name
71 FROM ams_list_src_types
72 WHERE list_source_type_id = p_list_source_type_id;
73
74 BEGIN
75 -- Standard Start of API savepoint
76 SAVEPOINT CREATE_List_Src_Field_PVT;
77
78 -- Standard call to check for call compatibility.
79 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
80 p_api_version_number,
81 l_api_name,
82 G_PKG_NAME)
83 THEN
84 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
85 END IF;
86
87 -- Initialize message list if p_init_msg_list is set to TRUE.
88 IF FND_API.to_Boolean( p_init_msg_list )
89 THEN
90 FND_MSG_PUB.initialize;
91 END IF;
92
93 -- Debug Message
94 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
95
96
97 -- Initialize API return status to SUCCESS
98 x_return_status := FND_API.G_RET_STS_SUCCESS;
99
100 -- Local variable initialization
101
102 IF p_list_src_field_rec.LIST_SOURCE_FIELD_ID IS NULL OR p_list_src_field_rec.LIST_SOURCE_FIELD_ID = FND_API.g_miss_num THEN
103 LOOP
104 l_dummy := NULL;
105 OPEN c_id;
106 FETCH c_id INTO l_LIST_SOURCE_FIELD_ID;
107 CLOSE c_id;
108
109 OPEN c_id_exists(l_LIST_SOURCE_FIELD_ID);
110 FETCH c_id_exists INTO l_dummy;
111 CLOSE c_id_exists;
112 EXIT WHEN l_dummy IS NULL;
113 END LOOP;
114 END IF;
115
116 -- =========================================================================
117 -- Validate Environment
118 -- =========================================================================
119
120 IF FND_GLOBAL.User_Id IS NULL
121 THEN
122 AMS_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
123 RAISE FND_API.G_EXC_ERROR;
124 END IF;
125
126 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
127 THEN
128 -- Debug message
129 AMS_UTILITY_PVT.debug_message('Private API: Validate_List_Src_Field');
130
131 -- Invoke validation procedures
132 Validate_list_src_field(
133 p_api_version_number => 1.0,
134 p_init_msg_list => FND_API.G_FALSE,
135 p_validation_level => p_validation_level,
136 p_list_src_field_rec => p_list_src_field_rec,
137 x_return_status => x_return_status,
138 x_msg_count => x_msg_count,
139 x_msg_data => x_msg_data);
140 END IF;
141
142 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
143 RAISE FND_API.G_EXC_ERROR;
144 END IF;
145
146 -- check if source_column_name exist
147 OPEN c_get_table_name(p_list_src_field_rec.list_source_type_id);
148 FETCH c_get_table_name INTO l_table_name;
149 if (c_get_table_name%NOTFOUND)
150 THEN
151 close c_get_table_name;
152 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD','MISS_FIELD','source_object_name');
153 RAISE FND_API.G_EXC_ERROR;
154 END IF;
155 close c_get_table_name;
156
157
158 OPEN c_get_column_name(l_table_name, p_list_src_field_rec.source_column_name);
159 FETCH c_get_column_name INTO l_column_name;
160 if (c_get_column_name%NOTFOUND)
161 THEN
162 close c_get_column_name;
163 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD','MISS_FIELD','source_column_name');
164 RAISE FND_API.G_EXC_ERROR;
165
166 END IF;
167 close c_get_column_name;
168
169
170 -- check if field_column_name exist
171 /*
172 OPEN c_get_column_name('AMS_LIST_ENTRIES', p_list_src_field_rec.field_column_name);
173 FETCH c_get_column_name INTO l_column_name;
174 if (c_get_column_name%NOTFOUND)
175 THEN
176 close c_get_column_name;
177 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD','MISS_FIELD','field_column_name');
178 RAISE FND_API.G_EXC_ERROR;
179 END IF;
180 close c_get_column_name;
181 */
182
183 -- Debug Message
184 AMS_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
185
186 -- Invoke table handler(AMS_LIST_SRC_FIELDS_PKG.Insert_Row)
187 AMS_LIST_SRC_FIELDS_PKG.Insert_Row(
188 px_list_source_field_id => l_list_source_field_id,
189 p_last_update_date => SYSDATE,
190 p_last_updated_by => FND_GLOBAL.USER_ID,
191 p_creation_date => SYSDATE,
192 p_created_by => FND_GLOBAL.USER_ID,
193 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
194 px_object_version_number => l_object_version_number,
195 p_de_list_source_type_code => p_list_src_field_rec.de_list_source_type_code,
196 p_list_source_type_id => p_list_src_field_rec.list_source_type_id,
197 p_field_table_name => p_list_src_field_rec.field_table_name,
198 p_field_column_name => p_list_src_field_rec.field_column_name,
199 p_source_column_name => p_list_src_field_rec.source_column_name,
200 p_source_column_meaning => p_list_src_field_rec.source_column_meaning,
201 p_enabled_flag => p_list_src_field_rec.enabled_flag,
202 p_start_position => p_list_src_field_rec.start_position,
203 p_end_position => p_list_src_field_rec.end_position,
204 p_field_data_type => p_list_src_field_rec.field_data_type,
205 p_field_data_size => p_list_src_field_rec.field_data_size,
206 p_default_ui_control => p_list_src_field_rec.default_ui_control,
207 p_field_lookup_type => p_list_src_field_rec.field_lookup_type,
208 p_field_lookup_type_view_name => p_list_src_field_rec.field_lookup_type_view_name,
209 p_allow_label_override => p_list_src_field_rec.allow_label_override,
210 p_field_usage_type => p_list_src_field_rec.field_usage_type,
211 p_dialog_enabled => p_list_src_field_rec.dialog_enabled,
212 p_analytics_flag => p_list_src_field_rec.analytics_flag,
213 p_auto_binning_flag => p_list_src_field_rec.auto_binning_flag,
214 p_no_of_buckets => p_list_src_field_rec.no_of_buckets,
215 p_attb_lov_id => p_list_src_field_rec.attb_lov_id,
216 p_lov_defined_flag => p_list_src_field_rec.lov_defined_flag,
217 p_column_type => NULL
218 );
219
220 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
221 RAISE FND_API.G_EXC_ERROR;
222 END IF;
223 --
224 -- End of API body
225 --
226 x_list_source_field_id := l_list_source_field_id;
227 -- Standard check for p_commit
228 IF FND_API.to_Boolean( p_commit )
229 THEN
230 COMMIT WORK;
231 END IF;
232
233
234 -- Debug Message
235 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
236
237 -- Standard call to get message count and if count is 1, get message info.
238 FND_MSG_PUB.Count_And_Get
239 (p_count => x_msg_count,
240 p_data => x_msg_data
241 );
242 EXCEPTION
243
244 WHEN AMS_Utility_PVT.resource_locked THEN
245 x_return_status := FND_API.g_ret_sts_error;
246 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
247
248 WHEN FND_API.G_EXC_ERROR THEN
249 ROLLBACK TO CREATE_List_Src_Field_PVT;
250 x_return_status := FND_API.G_RET_STS_ERROR;
251 -- Standard call to get message count and if count=1, get the message
252 FND_MSG_PUB.Count_And_Get (
253 p_encoded => FND_API.G_FALSE,
254 p_count => x_msg_count,
255 p_data => x_msg_data
256 );
257
258 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
259 ROLLBACK TO CREATE_List_Src_Field_PVT;
260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
261 -- Standard call to get message count and if count=1, get the message
262 FND_MSG_PUB.Count_And_Get (
263 p_encoded => FND_API.G_FALSE,
264 p_count => x_msg_count,
265 p_data => x_msg_data
266 );
267
268 WHEN OTHERS THEN
269 ROLLBACK TO CREATE_List_Src_Field_PVT;
270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
271 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
272 THEN
273 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
274 END IF;
275 -- Standard call to get message count and if count=1, get the message
276 FND_MSG_PUB.Count_And_Get (
277 p_encoded => FND_API.G_FALSE,
278 p_count => x_msg_count,
279 p_data => x_msg_data
280 );
281 End Create_List_Src_Field;
282
283
284 PROCEDURE Update_List_Src_Field(
285 p_api_version_number IN NUMBER,
286 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
287 p_commit IN VARCHAR2 := FND_API.G_FALSE,
288 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
289
290 x_return_status OUT NOCOPY VARCHAR2,
291 x_msg_count OUT NOCOPY NUMBER,
292 x_msg_data OUT NOCOPY VARCHAR2,
293
294 p_list_src_field_rec IN list_src_field_rec_type,
295 x_object_version_number OUT NOCOPY NUMBER
296 )
297
298 IS
299
300 CURSOR c_get_list_src_field(list_source_field_id NUMBER) IS
301 SELECT *
302 FROM AMS_LIST_SRC_FIELDS
303 WHERE list_source_field_id =p_list_src_field_rec.list_source_field_id;
304
305 CURSOR c_get_analytics_flag(p_list_source_field_id NUMBER) IS
306 SELECT analytics_flag
307 FROM AMS_LIST_SRC_FIELDS
308 WHERE list_source_field_id = p_list_source_field_id;
309
310 CURSOR c_get_column_name(p_table_name VARCHAR2, p_column_name VARCHAR2) IS
311 SELECT COLUMN_NAME
312 FROM sys.all_tab_columns
313 WHERE table_name = UPPER(p_table_name)
314 AND column_name = UPPER(p_column_name);
315
316 CURSOR c_get_table_name(p_list_source_type_id NUMBER) IS
317 SELECT source_object_name,nvl(remote_flag,'N'),database_link
318 FROM ams_list_src_types
319 WHERE list_source_type_id = p_list_source_type_id;
320
321 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_List_Src_Field';
322 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
323 -- Local Variables
324 l_object_version_number NUMBER;
325 l_LIST_SOURCE_FIELD_ID NUMBER;
326 l_ref_list_src_field_rec c_get_List_Src_Field%ROWTYPE ;
327 l_tar_list_src_field_rec AMS_List_Src_Field_PVT.list_src_field_rec_type := P_list_src_field_rec;
328 l_rowid ROWID;
329
330 l_ref_analytics_flag VARCHAR2(1);
331
332 l_table_name VARCHAR2(30);
333 l_column_name VARCHAR2(30);
334 l_remote_flag VARCHAR2(1);
335 l_database_link VARCHAR2(1000);
336 BEGIN
337 -- Standard Start of API savepoint
338 SAVEPOINT UPDATE_List_Src_Field_PVT;
339
340 -- Standard call to check for call compatibility.
341 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
342 p_api_version_number,
343 l_api_name,
344 G_PKG_NAME)
345 THEN
346 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
347 END IF;
348
349 -- Initialize message list if p_init_msg_list is set to TRUE.
350 IF FND_API.to_Boolean( p_init_msg_list )
351 THEN
352 FND_MSG_PUB.initialize;
353 END IF;
354
355 -- Debug Message
356 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
357
358
359 -- Initialize API return status to SUCCESS
360 x_return_status := FND_API.G_RET_STS_SUCCESS;
361
362 -- Debug Message
363 AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
364
365 -- check if source_column_name exist
366 OPEN c_get_table_name(p_list_src_field_rec.list_source_type_id);
367 FETCH c_get_table_name INTO l_table_name,l_remote_flag,l_database_link;
368 if (c_get_table_name%NOTFOUND)
369 THEN
370 close c_get_table_name;
371 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD','MISS_FIELD','source_object_name');
372 RAISE FND_API.G_EXC_ERROR;
373 END IF;
374 close c_get_table_name;
375
376 if l_remote_flag = 'N' then
377 OPEN c_get_column_name(l_table_name, p_list_src_field_rec.source_column_name);
378 FETCH c_get_column_name INTO l_column_name;
379 if (c_get_column_name%NOTFOUND)
380 THEN
381 close c_get_column_name;
382 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD','MISS_FIELD','source_column_name');
383 RAISE FND_API.G_EXC_ERROR;
384 END IF;
385 close c_get_column_name;
386 end if;
387
388
389 if l_remote_flag = 'Y' then
390 EXECUTE IMMEDIATE
391 'BEGIN
392 SELECT distinct COLUMN_NAME INTO :1
393 FROM sys.all_tab_columns'||'@'||l_database_link||
394 ' WHERE table_name = UPPER( :1 )'||
395 ' AND column_name = UPPER( :2 ); END;'
396 USING l_table_name,
397 p_list_src_field_rec.source_column_name,
398 OUT l_column_name;
399 -- ' WHERE table_name = UPPER('||''''||l_table_name||''''||')'||
400 -- ' AND column_name = UPPER('|| ''''||p_list_src_field_rec.source_column_name||''''||'); END;' USING OUT l_column_name;
401 if l_column_name is NULL
402 THEN
403 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD','MISS_FIELD','source_column_name');
404 RAISE FND_API.G_EXC_ERROR;
405 END IF;
406 end if;
407
408
409 If (l_tar_list_src_field_rec.object_version_number is NULL or
410 l_tar_list_src_field_rec.object_version_number = FND_API.G_MISS_NUM ) Then
411 AMS_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
412 p_token_name => 'COLUMN',
413 p_token_value => 'Last_Update_Date') ;
414 raise FND_API.G_EXC_ERROR;
415 End if;
416 -- Check Whether record has been changed by someone else
417 If (l_tar_list_src_field_rec.object_version_number <> l_ref_list_src_field_rec.object_version_number) Then
418 AMS_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
419 p_token_name => 'INFO',
420 p_token_value => 'List_Src_Field') ;
421 raise FND_API.G_EXC_ERROR;
422 End if;
423 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
424 THEN
425 -- Debug message
426 AMS_UTILITY_PVT.debug_message('Private API: Validate_List_Src_Field');
427
428 -- Invoke validation procedures
429 Validate_list_src_field(
430 p_api_version_number => 1.0,
431 p_init_msg_list => FND_API.G_FALSE,
432 p_validation_level => p_validation_level,
433 p_list_src_field_rec => p_list_src_field_rec,
434 x_return_status => x_return_status,
435 x_msg_count => x_msg_count,
436 x_msg_data => x_msg_data);
437 END IF;
438
439 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
440 RAISE FND_API.G_EXC_ERROR;
441 END IF;
442
443
444 -- Debug Message
445 -- AMS_UTILITY_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling update table handler');
446
447 -- Invoke table handler(AMS_LIST_SRC_FIELDS_PKG.Update_Row)
448 AMS_LIST_SRC_FIELDS_PKG.Update_Row(
449 p_list_source_field_id => p_list_src_field_rec.list_source_field_id,
450 p_last_update_date => SYSDATE,
451 p_last_updated_by => FND_GLOBAL.USER_ID,
452 p_creation_date => SYSDATE,
453 p_created_by => FND_GLOBAL.USER_ID,
454 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
455 p_object_version_number => p_list_src_field_rec.object_version_number,
456 p_de_list_source_type_code => p_list_src_field_rec.de_list_source_type_code,
457 p_list_source_type_id => p_list_src_field_rec.list_source_type_id,
458 p_field_table_name => p_list_src_field_rec.field_table_name,
459 p_field_column_name => p_list_src_field_rec.field_column_name,
460 p_source_column_name => p_list_src_field_rec.source_column_name,
461 p_source_column_meaning => p_list_src_field_rec.source_column_meaning,
462 p_enabled_flag => p_list_src_field_rec.enabled_flag,
463 p_start_position => p_list_src_field_rec.start_position,
464 p_end_position => p_list_src_field_rec.end_position,
465 p_field_data_type => p_list_src_field_rec.field_data_type,
466 p_field_data_size => p_list_src_field_rec.field_data_size,
467 p_default_ui_control => p_list_src_field_rec.default_ui_control,
468 p_field_lookup_type => p_list_src_field_rec.field_lookup_type,
469 p_field_lookup_type_view_name => p_list_src_field_rec.field_lookup_type_view_name,
470 p_allow_label_override => p_list_src_field_rec.allow_label_override,
471 p_field_usage_type => p_list_src_field_rec.field_usage_type,
472 p_dialog_enabled => p_list_src_field_rec.dialog_enabled,
473 p_analytics_flag => p_list_src_field_rec.analytics_flag,
474 p_auto_binning_flag => p_list_src_field_rec.auto_binning_flag,
475 p_no_of_buckets => p_list_src_field_rec.no_of_buckets,
476 p_attb_lov_id => p_list_src_field_rec.attb_lov_id,
477 p_lov_defined_flag => p_list_src_field_rec.lov_defined_flag,
478 p_column_type => NULL
479 );
480
481 -- Beginning of Code for Analytics Data Source Fields
482 -- Added by nyostos - June 11, 2002
483 -- If the auto_binning_flag is set to 'Y', then remove all binning details for the field
484 IF p_list_src_field_rec.auto_binning_flag = 'Y' THEN
485 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' AutoBinning Flag - Going to delete bin values for this field');
486 AMS_Dm_Binvalues_PVT.Delete_Dm_Binvalues_For_Field (p_datasource_field_id => p_list_src_field_rec.list_source_field_id);
487 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' AutoBinning Flag - After delete of bin values for this field');
488 END IF;
489 -- End of Code for Analytics Data Source Fields
490
491 -- Added by nyostos - Oct 14, 2002
492 -- If the analytics_flag has been changed for the field, then call
493 -- a procedure to INVALIDATE all AVAILABLE models using this data source.
494 -- First get the saved analytics_flag
495 OPEN c_get_analytics_flag (p_list_src_field_rec.list_source_field_id);
496 FETCH c_get_analytics_flag INTO l_ref_analytics_flag;
497 CLOSE c_get_analytics_flag;
498
499 IF (l_ref_analytics_flag IS NULL AND p_list_src_field_rec.analytics_flag IS NOT NULL) OR
500 (l_ref_analytics_flag <> p_list_src_field_rec.analytics_flag ) THEN
501 AMS_DM_MODEL_PVT.handle_data_source_changes(p_list_src_field_rec.list_source_type_id);
502 END IF;
503 -- End of addition by nyostos - Oct 14, 2002
504
505 --
506 -- End of API body.
507 --
508
509 -- Standard check for p_commit
510 IF FND_API.to_Boolean( p_commit )
511 THEN
512 COMMIT WORK;
513 END IF;
514
515
516 -- Debug Message
517 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
518
519 -- Standard call to get message count and if count is 1, get message info.
520 FND_MSG_PUB.Count_And_Get
521 (p_count => x_msg_count,
522 p_data => x_msg_data
523 );
524 EXCEPTION
525
526 WHEN AMS_Utility_PVT.resource_locked THEN
527 x_return_status := FND_API.g_ret_sts_error;
528 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
529
530 WHEN FND_API.G_EXC_ERROR THEN
531 ROLLBACK TO UPDATE_List_Src_Field_PVT;
532 x_return_status := FND_API.G_RET_STS_ERROR;
533 -- Standard call to get message count and if count=1, get the message
534 FND_MSG_PUB.Count_And_Get (
535 p_encoded => FND_API.G_FALSE,
536 p_count => x_msg_count,
537 p_data => x_msg_data
538 );
539
540 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
541 ROLLBACK TO UPDATE_List_Src_Field_PVT;
542 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
543 -- Standard call to get message count and if count=1, get the message
544 FND_MSG_PUB.Count_And_Get (
545 p_encoded => FND_API.G_FALSE,
546 p_count => x_msg_count,
547 p_data => x_msg_data
548 );
549
550 WHEN OTHERS THEN
551 ROLLBACK TO UPDATE_List_Src_Field_PVT;
552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
553 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
554 THEN
555 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
556 END IF;
557 -- Standard call to get message count and if count=1, get the message
558 FND_MSG_PUB.Count_And_Get (
559 p_encoded => FND_API.G_FALSE,
560 p_count => x_msg_count,
561 p_data => x_msg_data
562 );
563 End Update_List_Src_Field;
564
565
566 PROCEDURE Delete_List_Src_Field(
567 p_api_version_number IN NUMBER,
568 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
569 p_commit IN VARCHAR2 := FND_API.G_FALSE,
570 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
571 x_return_status OUT NOCOPY VARCHAR2,
572 x_msg_count OUT NOCOPY NUMBER,
573 x_msg_data OUT NOCOPY VARCHAR2,
574 p_list_source_field_id IN NUMBER,
575 p_object_version_number IN NUMBER
576 )
577
578 IS
579 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_List_Src_Field';
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_List_Src_Field_PVT;
586
587 -- Standard call to check for call compatibility.
588 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
589 p_api_version_number,
590 l_api_name,
591 G_PKG_NAME)
592 THEN
593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
594 END IF;
595
596 -- Initialize message list if p_init_msg_list is set to TRUE.
597 IF FND_API.to_Boolean( p_init_msg_list )
598 THEN
599 FND_MSG_PUB.initialize;
600 END IF;
601
602 -- Debug Message
603 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
604
605
606 -- Initialize API return status to SUCCESS
607 x_return_status := FND_API.G_RET_STS_SUCCESS;
608
609 --
610 -- Api body
611 --
612 -- Debug Message
613 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
614
615 -- Invoke table handler(AMS_LIST_SRC_FIELDS_PKG.Delete_Row)
616 AMS_LIST_SRC_FIELDS_PKG.Delete_Row(
617 p_LIST_SOURCE_FIELD_ID => p_LIST_SOURCE_FIELD_ID);
618
619
620 -- Beginning of Code for Analytics Data Source Fields
621 -- Added by nyostos - June 11, 2002
622 -- Remove all binning details for the field
623 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Going to delete bin values for this field');
624 AMS_Dm_Binvalues_PVT.Delete_Dm_Binvalues_For_Field (p_datasource_field_id => p_list_source_field_id);
625 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' After delete of bin values for this field');
626 -- End of Code for Analytics Data Source Fields
627
628
629 --
630 -- End of API body
631 --
632
633 -- Standard check for p_commit
634 IF FND_API.to_Boolean( p_commit )
635 THEN
636 COMMIT WORK;
637 END IF;
638
639
640 -- Debug Message
641 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
642
643 -- Standard call to get message count and if count is 1, get message info.
644 FND_MSG_PUB.Count_And_Get
645 (p_count => x_msg_count,
646 p_data => x_msg_data
647 );
648 EXCEPTION
649
650 WHEN AMS_Utility_PVT.resource_locked THEN
651 x_return_status := FND_API.g_ret_sts_error;
652 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
653
654 WHEN FND_API.G_EXC_ERROR THEN
655 ROLLBACK TO DELETE_List_Src_Field_PVT;
656 x_return_status := FND_API.G_RET_STS_ERROR;
657 -- Standard call to get message count and if count=1, get the message
658 FND_MSG_PUB.Count_And_Get (
659 p_encoded => FND_API.G_FALSE,
660 p_count => x_msg_count,
661 p_data => x_msg_data
662 );
663
664 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
665 ROLLBACK TO DELETE_List_Src_Field_PVT;
666 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
667 -- Standard call to get message count and if count=1, get the message
668 FND_MSG_PUB.Count_And_Get (
669 p_encoded => FND_API.G_FALSE,
670 p_count => x_msg_count,
671 p_data => x_msg_data
672 );
673
674 WHEN OTHERS THEN
675 ROLLBACK TO DELETE_List_Src_Field_PVT;
676 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
677 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
678 THEN
679 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
680 END IF;
681 -- Standard call to get message count and if count=1, get the message
682 FND_MSG_PUB.Count_And_Get (
683 p_encoded => FND_API.G_FALSE,
684 p_count => x_msg_count,
685 p_data => x_msg_data
686 );
687 End Delete_List_Src_Field;
688
689
690
691 -- Hint: Primary key needs to be returned.
692 PROCEDURE Lock_List_Src_Field(
693 p_api_version_number IN NUMBER,
694 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
695
696 x_return_status OUT NOCOPY VARCHAR2,
697 x_msg_count OUT NOCOPY NUMBER,
698 x_msg_data OUT NOCOPY VARCHAR2,
699
700 p_list_source_field_id IN NUMBER,
701 p_object_version IN NUMBER
702 )
703
704 IS
705 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_List_Src_Field';
706 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
707 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
708 l_LIST_SOURCE_FIELD_ID NUMBER;
709
710 CURSOR c_List_Src_Field IS
711 SELECT LIST_SOURCE_FIELD_ID
712 FROM AMS_LIST_SRC_FIELDS
713 WHERE LIST_SOURCE_FIELD_ID = p_LIST_SOURCE_FIELD_ID
714 AND object_version_number = p_object_version
715 FOR UPDATE NOWAIT;
716
717 BEGIN
718
719 -- Debug Message
720 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
721
722 -- Initialize message list if p_init_msg_list is set to TRUE.
723 IF FND_API.to_Boolean( p_init_msg_list )
724 THEN
725 FND_MSG_PUB.initialize;
726 END IF;
727
728 -- Standard call to check for call compatibility.
729 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
730 p_api_version_number,
731 l_api_name,
732 G_PKG_NAME)
733 THEN
734 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
735 END IF;
736
737
738 -- Initialize API return status to SUCCESS
739 x_return_status := FND_API.G_RET_STS_SUCCESS;
740
741
742 ------------------------ lock -------------------------
743
744 AMS_Utility_PVT.debug_message(l_full_name||': start');
745 OPEN c_List_Src_Field;
746
747 FETCH c_List_Src_Field INTO l_LIST_SOURCE_FIELD_ID;
748
749 IF (c_List_Src_Field%NOTFOUND) THEN
750 CLOSE c_List_Src_Field;
751 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
752 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
753 FND_MSG_PUB.add;
754 END IF;
755 RAISE FND_API.g_exc_error;
756 END IF;
757
758 CLOSE c_List_Src_Field;
759
760 -------------------- finish --------------------------
761 FND_MSG_PUB.count_and_get(
762 p_encoded => FND_API.g_false,
763 p_count => x_msg_count,
764 p_data => x_msg_data);
765 AMS_Utility_PVT.debug_message(l_full_name ||': end');
766 EXCEPTION
767
768 WHEN AMS_Utility_PVT.resource_locked THEN
769 x_return_status := FND_API.g_ret_sts_error;
770 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
771
772 WHEN FND_API.G_EXC_ERROR THEN
773 ROLLBACK TO LOCK_List_Src_Field_PVT;
774 x_return_status := FND_API.G_RET_STS_ERROR;
775 -- Standard call to get message count and if count=1, get the message
776 FND_MSG_PUB.Count_And_Get (
777 p_encoded => FND_API.G_FALSE,
778 p_count => x_msg_count,
779 p_data => x_msg_data
780 );
781
782 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
783 ROLLBACK TO LOCK_List_Src_Field_PVT;
784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
785 -- Standard call to get message count and if count=1, get the message
786 FND_MSG_PUB.Count_And_Get (
787 p_encoded => FND_API.G_FALSE,
788 p_count => x_msg_count,
789 p_data => x_msg_data
790 );
791
792 WHEN OTHERS THEN
793 ROLLBACK TO LOCK_List_Src_Field_PVT;
794 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
795 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
796 THEN
797 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
798 END IF;
799 -- Standard call to get message count and if count=1, get the message
800 FND_MSG_PUB.Count_And_Get (
801 p_encoded => FND_API.G_FALSE,
802 p_count => x_msg_count,
803 p_data => x_msg_data
804 );
805 End Lock_List_Src_Field;
806
807
808 PROCEDURE check_list_src_field_uk_items(
809 p_list_src_field_rec IN list_src_field_rec_type,
810 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
811 x_return_status OUT NOCOPY VARCHAR2)
812 IS
813 l_valid_flag VARCHAR2(1);
814
815 BEGIN
816 x_return_status := FND_API.g_ret_sts_success;
817 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
818 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
819 'AMS_LIST_SRC_FIELDS',
820 'LIST_SOURCE_FIELD_ID = ''' || p_list_src_field_rec.LIST_SOURCE_FIELD_ID ||''''
821 );
822 ELSE
823 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
824 'AMS_LIST_SRC_FIELDS',
825 'LIST_SOURCE_FIELD_ID = ''' || p_list_src_field_rec.LIST_SOURCE_FIELD_ID ||
826 ''' AND LIST_SOURCE_FIELD_ID <> ' || p_list_src_field_rec.LIST_SOURCE_FIELD_ID
827 );
828 END IF;
829
830 IF l_valid_flag = FND_API.g_false THEN
831 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_LIST_SOURCE_FIELD_ID_DUPLICATE');
832 x_return_status := FND_API.g_ret_sts_error;
833 RETURN;
834 END IF;
835
836 IF p_validation_mode = JTF_PLSQL_API.g_create
837 AND p_list_src_field_rec.source_column_name IS NOT NULL
838 AND p_list_src_field_rec.field_column_name IS NOT NULL
839 AND p_list_src_field_rec.field_table_name IS NOT NULL
840 AND p_list_src_field_rec.list_source_type_id IS NOT NULL
841
842 THEN
843 IF AMS_Utility_PVT.check_uniqueness(
844 'ams_list_src_fields',
845 'source_column_name = ' || p_list_src_field_rec.source_column_name||
846 ' and field_table_name = '||p_list_src_field_rec.field_table_name
847 ||' and field_column_name = '||p_list_src_field_rec.field_column_name
848 ||' and list_source_type_id = '||p_list_src_field_rec.list_source_type_id
849
850 ) = FND_API.g_false
851 THEN
852 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
853 FND_MESSAGE.set_name('AMS', 'BAD_COMB');
854 FND_MSG_PUB.add;
855 END IF;
856 x_return_status := FND_API.g_ret_sts_error;
857 RETURN;
858 END IF;
859 END IF;
860
861
862 END check_list_src_field_uk_items;
863
864 PROCEDURE check_list_src_field_req_items(
865 p_list_src_field_rec IN list_src_field_rec_type,
866 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
867 x_return_status OUT NOCOPY VARCHAR2
868 )
869 IS
870 BEGIN
871 x_return_status := FND_API.g_ret_sts_success;
872
873 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
874
875
876 IF p_list_src_field_rec.list_source_field_id = FND_API.g_miss_num OR p_list_src_field_rec.list_source_field_id IS NULL THEN
877 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD','MISS_FIELD','LIST_SOURCE_FIELD_ID');
878 x_return_status := FND_API.g_ret_sts_error;
879 END IF;
880
881
882 IF p_list_src_field_rec.de_list_source_type_code = FND_API.g_miss_char OR p_list_src_field_rec.de_list_source_type_code IS NULL THEN
883 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD','MISS_FIELD','DE_LIST_SOURCE_TYPE_CODE' );
884 x_return_status := FND_API.g_ret_sts_error;
885 END IF;
886
887
888 IF p_list_src_field_rec.list_source_type_id = FND_API.g_miss_num OR p_list_src_field_rec.list_source_type_id IS NULL THEN
889 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD','MISS_FIELD','LIST_SOURCE_TYPE_ID' );
890 x_return_status := FND_API.g_ret_sts_error;
891 END IF;
892 /*
893 field_table_name and field_column_name are checked in check_lstsrcfld_business()
894 IF p_list_src_field_rec.field_table_name = FND_API.g_miss_char OR p_list_src_field_rec.field_table_name IS NULL THEN
895 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD','MISS_FIELD','FIELD_TABLE_NAME' );
896 x_return_status := FND_API.g_ret_sts_error;
897 END IF;
898
899
900 IF p_list_src_field_rec.field_column_name = FND_API.g_miss_char OR p_list_src_field_rec.field_column_name IS NULL THEN
901 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD','MISS_FIELD','FIELD_COLUMN_NAME' );
902 x_return_status := FND_API.g_ret_sts_error;
903 END IF;
904 */
905
906 IF p_list_src_field_rec.source_column_name = FND_API.g_miss_char OR p_list_src_field_rec.source_column_name IS NULL THEN
907 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD','MISS_FIELD','SOURCE_COLUMN_NAME' );
908 x_return_status := FND_API.g_ret_sts_error;
909 END IF;
910
911 IF p_list_src_field_rec.enabled_flag = FND_API.g_miss_char OR p_list_src_field_rec.enabled_flag IS NULL THEN
912 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD','MISS_FIELD','ENABLED_FLAG' );
913 x_return_status := FND_API.g_ret_sts_error;
914 END IF;
915
916 END IF;
917
918
919 END check_list_src_field_req_items;
920
921 PROCEDURE check_list_src_field_FK_items(
922 p_list_src_field_rec IN list_src_field_rec_type,
923 x_return_status OUT NOCOPY VARCHAR2
924 )
925 IS
926 BEGIN
927 x_return_status := FND_API.g_ret_sts_success;
928
929 -- Enter custom code here
930
931 END check_list_src_field_FK_items;
932
933 PROCEDURE check_lstsrcfld_lookup_items(
934 p_list_src_field_rec IN list_src_field_rec_type,
935 x_return_status OUT NOCOPY VARCHAR2
936 )
937 IS
938 BEGIN
939 x_return_status := FND_API.g_ret_sts_success;
940
941 -- Enter custom code here
942
943 END check_lstsrcfld_lookup_items;
944
945 PROCEDURE check_lstsrcfld_business(
946 p_list_src_field_rec IN list_src_field_rec_type,
947 x_return_status OUT NOCOPY VARCHAR2
948 )
949 IS
950
951 CURSOR c_lstsrcfld_crt IS
952 SELECT *
953 FROM ams_list_src_fields_vl
954 WHERE list_source_type_id=p_list_src_field_rec.list_source_type_id
955 and source_column_name = p_list_src_field_rec.source_column_name;
956
957 CURSOR c_lstsrcfld_upd IS
958 SELECT *
959 FROM ams_list_src_fields_vl
960 WHERE list_source_type_id=p_list_src_field_rec.list_source_type_id
961 and source_column_name = p_list_src_field_rec.source_column_name
962 and list_source_field_id <> p_list_src_field_rec.list_source_field_id;
963
964 CURSOR c_fldcolname_crt IS
965 -- Bug 3664542, SOLIN SQL Repository, tuning
966 -- SELECT *
967 -- FROM ams_list_src_fields_vl
968 -- WHERE
969 -- (list_source_type_id=p_list_src_field_rec.list_source_type_id
970 -- OR list_source_type_id IN
971 -- ( SELECT sub_source_type_id
972 -- FROM ams_list_src_type_assocs
973 -- WHERE ( master_source_type_id = p_list_src_field_rec.list_source_type_id
974 -- OR sub_source_type_id = p_list_src_field_rec.list_source_type_id)
975 -- AND enabled_flag = 'Y'
976 -- )
977 -- )
978 -- AND field_column_name = p_list_src_field_rec.field_column_name;
979 SELECT *
980 FROM ams_list_src_fields_vl
981 WHERE list_source_type_id=p_list_src_field_rec.list_source_type_id
982 AND field_column_name = p_list_src_field_rec.field_column_name
983 UNION ALL
984 SELECT *
985 FROM ams_list_src_fields_vl
986 WHERE list_source_type_id IN
987 ( SELECT sub_source_type_id
988 FROM ams_list_src_type_assocs
989 WHERE ( master_source_type_id = p_list_src_field_rec.list_source_type_id
990 OR sub_source_type_id = p_list_src_field_rec.list_source_type_id)
991 AND enabled_flag = 'Y'
992 )
993 AND field_column_name = p_list_src_field_rec.field_column_name;
994
995 CURSOR c_fldcolname_upd IS
996 -- Bug 3664542, SOLIN SQL Repository, tuning
997 -- SELECT *
998 -- FROM ams_list_src_fields_vl
999 -- WHERE
1000 -- (list_source_type_id=p_list_src_field_rec.list_source_type_id
1001 -- OR list_source_type_id IN
1002 -- ( SELECT sub_source_type_id
1003 -- FROM ams_list_src_type_assocs
1004 -- WHERE ( master_source_type_id = p_list_src_field_rec.list_source_type_id
1005 -- OR sub_source_type_id = p_list_src_field_rec.list_source_type_id)
1006 -- AND enabled_flag = 'Y'
1007 -- )
1008 -- )
1009 -- AND field_column_name = p_list_src_field_rec.field_column_name
1010 -- AND list_source_field_id <> p_list_src_field_rec.list_source_field_id;
1011 SELECT *
1012 FROM ams_list_src_fields_vl
1013 WHERE list_source_type_id=p_list_src_field_rec.list_source_type_id
1014 AND field_column_name = p_list_src_field_rec.field_column_name
1015 AND list_source_field_id <> p_list_src_field_rec.list_source_field_id
1016 UNION ALL
1017 SELECT *
1018 FROM ams_list_src_fields_vl
1019 WHERE list_source_type_id IN
1020 ( SELECT sub_source_type_id
1021 FROM ams_list_src_type_assocs
1022 WHERE ( master_source_type_id = p_list_src_field_rec.list_source_type_id
1023 OR sub_source_type_id = p_list_src_field_rec.list_source_type_id)
1024 AND enabled_flag = 'Y'
1025 )
1026 AND field_column_name = p_list_src_field_rec.field_column_name
1027 AND list_source_field_id <> p_list_src_field_rec.list_source_field_id;
1028
1029 l_lstsrcfld_crt_rec c_lstsrcfld_crt%ROWTYPE;
1030 l_fldcolname_crt_rec c_fldcolname_crt%ROWTYPE;
1031 l_lstsrcfld_upd_rec c_lstsrcfld_upd%ROWTYPE;
1032 l_fldcolname_upd_rec c_fldcolname_upd%ROWTYPE;
1033
1034 BEGIN
1035
1036 x_return_status := FND_API.g_ret_sts_success;
1037
1038 -- Create
1039 IF p_list_src_field_rec.list_source_field_id = FND_API.g_miss_num or
1040 p_list_src_field_rec.list_source_field_id IS NULL
1041 THEN
1042 OPEN c_lstsrcfld_crt;
1043 FETCH c_lstsrcfld_crt INTO l_lstsrcfld_crt_rec;
1044
1045 IF (c_lstsrcfld_crt%FOUND) THEN
1046 FND_MESSAGE.set_name('AMS', 'AMS_DUPLICATE_SRC_COL_NAME');
1047 FND_MSG_PUB.Add;
1048 x_return_status := FND_API.g_ret_sts_error;
1049 RETURN;
1050 END IF;
1051
1052 OPEN c_fldcolname_crt;
1053 FETCH c_fldcolname_crt INTO l_fldcolname_crt_rec;
1054
1055 IF (c_fldcolname_crt%FOUND) THEN
1056 FND_MESSAGE.set_name('AMS', 'AMS_DUPLICATE_FLD_COL_NAME');
1057 FND_MSG_PUB.Add;
1058 x_return_status := FND_API.g_ret_sts_error;
1059 RETURN;
1060 END IF;
1061
1062 -- Update
1063 ELSE
1064 OPEN c_lstsrcfld_upd;
1065 FETCH c_lstsrcfld_upd INTO l_lstsrcfld_upd_rec;
1066
1067 IF (c_lstsrcfld_upd%FOUND) THEN
1068 FND_MESSAGE.set_name('AMS', 'AMS_DUPLICATE_SRC_COL_NAME');
1069 FND_MSG_PUB.Add;
1070 x_return_status := FND_API.g_ret_sts_error;
1071 RETURN;
1072 END IF;
1073
1074 OPEN c_fldcolname_upd;
1075 FETCH c_fldcolname_upd INTO l_fldcolname_upd_rec;
1076
1077 IF (c_fldcolname_upd%FOUND) THEN
1078 FND_MESSAGE.set_name('AMS', 'AMS_DUPLICATE_FLD_COL_NAME');
1079 FND_MSG_PUB.Add;
1080 x_return_status := FND_API.g_ret_sts_error;
1081 RETURN;
1082 END IF;
1083
1084 END IF;
1085
1086 /*
1087 IF p_list_src_field_rec.de_list_source_type_code <> 'ANALYTICS' THEN
1088 IF p_list_src_field_rec.field_table_name = FND_API.g_miss_char OR p_list_src_field_rec.field_table_name IS NULL THEN
1089 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD','MISS_FIELD','field_table_name' );
1090 x_return_status := FND_API.g_ret_sts_error;
1091 END IF;
1092
1093 IF p_list_src_field_rec.field_column_name = FND_API.g_miss_char OR p_list_src_field_rec.field_column_name IS NULL THEN
1094 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD','MISS_FIELD','field_column_name' );
1095 x_return_status := FND_API.g_ret_sts_error;
1096 END IF;
1097 END IF;
1098 */
1099 IF p_list_src_field_rec.enabled_flag = 'Y' THEN
1100 IF p_list_src_field_rec.field_table_name = FND_API.g_miss_char OR p_list_src_field_rec.field_table_name IS NULL THEN
1101 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD','MISS_FIELD','field_table_name' );
1102 x_return_status := FND_API.g_ret_sts_error;
1103 END IF;
1104
1105 IF p_list_src_field_rec.field_column_name = FND_API.g_miss_char OR p_list_src_field_rec.field_column_name IS NULL THEN
1106 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD','MISS_FIELD','field_column_name' );
1107 x_return_status := FND_API.g_ret_sts_error;
1108 END IF;
1109 END IF;
1110
1111 END check_lstsrcfld_business;
1112
1113 PROCEDURE Check_list_src_field_Items (
1114 P_list_src_field_rec IN list_src_field_rec_type,
1115 p_validation_mode IN VARCHAR2,
1116 x_return_status OUT NOCOPY VARCHAR2
1117 )
1118 IS
1119 BEGIN
1120
1121 -- Check Items Uniqueness API calls
1122
1123 check_list_src_field_uk_items(
1124 p_list_src_field_rec => p_list_src_field_rec,
1125 p_validation_mode => p_validation_mode,
1126 x_return_status => x_return_status);
1127 IF x_return_status <> FND_API.g_ret_sts_success THEN
1128 RETURN;
1129 END IF;
1130
1131 -- Check Items Required/NOT NULL API calls
1132
1133 check_list_src_field_req_items(
1134 p_list_src_field_rec => p_list_src_field_rec,
1135 p_validation_mode => p_validation_mode,
1136 x_return_status => x_return_status);
1137 IF x_return_status <> FND_API.g_ret_sts_success THEN
1138 RETURN;
1139 END IF;
1140 -- Check Items Foreign Keys API calls
1141
1142 check_list_src_field_FK_items(
1143 p_list_src_field_rec => p_list_src_field_rec,
1144 x_return_status => x_return_status);
1145 IF x_return_status <> FND_API.g_ret_sts_success THEN
1146 RETURN;
1147 END IF;
1148 -- Check Items Lookups
1149
1150 check_lstsrcfld_lookup_items(
1151 p_list_src_field_rec => p_list_src_field_rec,
1152 x_return_status => x_return_status);
1153 IF x_return_status <> FND_API.g_ret_sts_success THEN
1154 RETURN;
1155 END IF;
1156
1157 AMS_UTILITY_PVT.debug_message('Private API: ' || 'before check_lstsrcfld_business');
1158 check_lstsrcfld_business(
1159 p_list_src_field_rec => p_list_src_field_rec,
1160 x_return_status => x_return_status);
1161 IF x_return_status <> FND_API.g_ret_sts_success THEN
1162 RETURN;
1163 END IF;
1164 AMS_UTILITY_PVT.debug_message('Private API: ' || 'after check_lstsrcfld_business');
1165
1166 END Check_list_src_field_Items;
1167
1168
1169 PROCEDURE Complete_list_src_field_Rec (
1170 p_list_src_field_rec IN list_src_field_rec_type,
1171 x_complete_rec OUT NOCOPY list_src_field_rec_type)
1172 IS
1173 l_return_status VARCHAR2(1);
1174
1175 CURSOR c_complete IS
1176 SELECT *
1177 FROM ams_list_src_fields_vl
1178 WHERE list_source_field_id = p_list_src_field_rec.list_source_field_id;
1179 l_list_src_field_rec c_complete%ROWTYPE;
1180 BEGIN
1181 x_complete_rec := p_list_src_field_rec;
1182
1183
1184 OPEN c_complete;
1185 FETCH c_complete INTO l_list_src_field_rec;
1186 CLOSE c_complete;
1187
1188 -- list_source_field_id
1189 IF p_list_src_field_rec.list_source_field_id = FND_API.g_miss_num THEN
1190 x_complete_rec.list_source_field_id := l_list_src_field_rec.list_source_field_id;
1191 END IF;
1192
1193 -- last_update_date
1194 IF p_list_src_field_rec.last_update_date = FND_API.g_miss_date THEN
1195 x_complete_rec.last_update_date := l_list_src_field_rec.last_update_date;
1196 END IF;
1197
1198 -- last_updated_by
1199 IF p_list_src_field_rec.last_updated_by = FND_API.g_miss_num THEN
1200 x_complete_rec.last_updated_by := l_list_src_field_rec.last_updated_by;
1201 END IF;
1202
1203 -- creation_date
1204 IF p_list_src_field_rec.creation_date = FND_API.g_miss_date THEN
1205 x_complete_rec.creation_date := l_list_src_field_rec.creation_date;
1206 END IF;
1207
1208 -- created_by
1209 IF p_list_src_field_rec.created_by = FND_API.g_miss_num THEN
1210 x_complete_rec.created_by := l_list_src_field_rec.created_by;
1211 END IF;
1212
1213 -- last_update_login
1214 IF p_list_src_field_rec.last_update_login = FND_API.g_miss_num THEN
1215 x_complete_rec.last_update_login := l_list_src_field_rec.last_update_login;
1216 END IF;
1217
1218 -- object_version_number
1219 IF p_list_src_field_rec.object_version_number = FND_API.g_miss_num THEN
1220 x_complete_rec.object_version_number := l_list_src_field_rec.object_version_number;
1221 END IF;
1222
1223 -- de_list_source_type_code
1224 IF p_list_src_field_rec.de_list_source_type_code = FND_API.g_miss_char THEN
1225 x_complete_rec.de_list_source_type_code := l_list_src_field_rec.de_list_source_type_code;
1226 END IF;
1227
1228 -- list_source_type_id
1229 IF p_list_src_field_rec.list_source_type_id = FND_API.g_miss_num THEN
1230 x_complete_rec.list_source_type_id := l_list_src_field_rec.list_source_type_id;
1231 END IF;
1232
1233 -- field_table_name
1234 IF p_list_src_field_rec.field_table_name = FND_API.g_miss_char THEN
1235 x_complete_rec.field_table_name := l_list_src_field_rec.field_table_name;
1236 END IF;
1237
1238 -- field_column_name
1239 IF p_list_src_field_rec.field_column_name = FND_API.g_miss_char THEN
1240 x_complete_rec.field_column_name := l_list_src_field_rec.field_column_name;
1241 END IF;
1242
1243 -- source_column_name
1244 IF p_list_src_field_rec.source_column_name = FND_API.g_miss_char THEN
1245 x_complete_rec.source_column_name := l_list_src_field_rec.source_column_name;
1246 END IF;
1247
1248 -- source_column_meaning
1249 IF p_list_src_field_rec.source_column_meaning = FND_API.g_miss_char THEN
1250 x_complete_rec.source_column_meaning := l_list_src_field_rec.source_column_meaning;
1251 END IF;
1252
1253 -- enabled_flag
1254 IF p_list_src_field_rec.enabled_flag = FND_API.g_miss_char THEN
1255 x_complete_rec.enabled_flag := l_list_src_field_rec.enabled_flag;
1256 END IF;
1257
1258 -- start_position
1259 IF p_list_src_field_rec.start_position = FND_API.g_miss_num THEN
1260 x_complete_rec.start_position := l_list_src_field_rec.start_position;
1261 END IF;
1262
1263 -- end_position
1264 IF p_list_src_field_rec.end_position = FND_API.g_miss_num THEN
1265 x_complete_rec.end_position := l_list_src_field_rec.end_position;
1266 END IF;
1267
1268 -- Note: Developers need to modify the procedure
1269 -- to handle any business specific requirements.
1270 END Complete_list_src_field_Rec;
1271 PROCEDURE Validate_list_src_field(
1272 p_api_version_number IN NUMBER,
1273 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1274 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1275 p_list_src_field_rec IN list_src_field_rec_type,
1276 x_return_status OUT NOCOPY VARCHAR2,
1277 x_msg_count OUT NOCOPY NUMBER,
1278 x_msg_data OUT NOCOPY VARCHAR2
1279 )
1280 IS
1281 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_List_Src_Field';
1282 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1283 l_object_version_number NUMBER;
1284 l_list_src_field_rec AMS_List_Src_Field_PVT.list_src_field_rec_type;
1285
1286 BEGIN
1287 -- Standard Start of API savepoint
1288 SAVEPOINT VALIDATE_List_Src_Field_;
1289
1290 -- Standard call to check for call compatibility.
1291 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1292 p_api_version_number,
1293 l_api_name,
1294 G_PKG_NAME)
1295 THEN
1296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1297 END IF;
1298
1299 -- Initialize message list if p_init_msg_list is set to TRUE.
1300 IF FND_API.to_Boolean( p_init_msg_list )
1301 THEN
1302 FND_MSG_PUB.initialize;
1303 END IF;
1304
1305
1306
1307 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1308 Check_list_src_field_Items(
1309 p_list_src_field_rec => p_list_src_field_rec,
1310 p_validation_mode => JTF_PLSQL_API.g_update,
1311 x_return_status => x_return_status
1312 );
1313
1314 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1315 RAISE FND_API.G_EXC_ERROR;
1316 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1318 END IF;
1319 END IF;
1320
1321 Complete_list_src_field_Rec(
1322 p_list_src_field_rec => p_list_src_field_rec,
1323 x_complete_rec => l_list_src_field_rec
1324 );
1325
1326 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1327 Validate_list_src_field_Rec(
1328 p_api_version_number => 1.0,
1329 p_init_msg_list => FND_API.G_FALSE,
1330 x_return_status => x_return_status,
1331 x_msg_count => x_msg_count,
1332 x_msg_data => x_msg_data,
1333 p_list_src_field_rec => l_list_src_field_rec);
1334
1335 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1336 RAISE FND_API.G_EXC_ERROR;
1337 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1338 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1339 END IF;
1340 END IF;
1341
1342
1343 -- Debug Message
1344 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1345
1346
1347 -- Initialize API return status to SUCCESS
1348 x_return_status := FND_API.G_RET_STS_SUCCESS;
1349
1350
1351 -- Debug Message
1352 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1353
1354 -- Standard call to get message count and if count is 1, get message info.
1355 FND_MSG_PUB.Count_And_Get
1356 (p_count => x_msg_count,
1357 p_data => x_msg_data
1358 );
1359 EXCEPTION
1360
1361 WHEN AMS_Utility_PVT.resource_locked THEN
1362 x_return_status := FND_API.g_ret_sts_error;
1363 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1364
1365 WHEN FND_API.G_EXC_ERROR THEN
1366 ROLLBACK TO VALIDATE_List_Src_Field_;
1367 x_return_status := FND_API.G_RET_STS_ERROR;
1368 -- Standard call to get message count and if count=1, get the message
1369 FND_MSG_PUB.Count_And_Get (
1370 p_encoded => FND_API.G_FALSE,
1371 p_count => x_msg_count,
1372 p_data => x_msg_data
1373 );
1374
1375 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1376 ROLLBACK TO VALIDATE_List_Src_Field_;
1377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1378 -- Standard call to get message count and if count=1, get the message
1379 FND_MSG_PUB.Count_And_Get (
1380 p_encoded => FND_API.G_FALSE,
1381 p_count => x_msg_count,
1382 p_data => x_msg_data
1383 );
1384
1385 WHEN OTHERS THEN
1386 ROLLBACK TO VALIDATE_List_Src_Field_;
1387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1388 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1389 THEN
1390 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1391 END IF;
1392 -- Standard call to get message count and if count=1, get the message
1393 FND_MSG_PUB.Count_And_Get (
1394 p_encoded => FND_API.G_FALSE,
1395 p_count => x_msg_count,
1396 p_data => x_msg_data
1397 );
1398 End Validate_List_Src_Field;
1399
1400
1401 PROCEDURE Validate_list_src_field_rec(
1402 p_api_version_number IN NUMBER,
1403 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1404 x_return_status OUT NOCOPY VARCHAR2,
1405 x_msg_count OUT NOCOPY NUMBER,
1406 x_msg_data OUT NOCOPY VARCHAR2,
1407 p_list_src_field_rec IN list_src_field_rec_type
1408 )
1409 IS
1410 BEGIN
1411 -- Initialize message list if p_init_msg_list is set to TRUE.
1412 IF FND_API.to_Boolean( p_init_msg_list )
1413 THEN
1414 FND_MSG_PUB.initialize;
1415 END IF;
1416
1417 -- Initialize API return status to SUCCESS
1418 x_return_status := FND_API.G_RET_STS_SUCCESS;
1419
1420 -- Hint: Validate data
1421 -- If data not valid
1422 -- THEN
1423 -- x_return_status := FND_API.G_RET_STS_ERROR;
1424
1425 -- Debug Message
1426 AMS_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
1427 -- Standard call to get message count and if count is 1, get message info.
1428 FND_MSG_PUB.Count_And_Get
1429 (p_count => x_msg_count,
1430 p_data => x_msg_data
1431 );
1432 END Validate_list_src_field_Rec;
1433
1434 END AMS_List_Src_Field_PVT;