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