[Home] [Help]
PACKAGE BODY: APPS.AMS_ADV_FILTER_PVT
Source
1 PACKAGE BODY AMS_Adv_Filter_PVT as
2 /* $Header: amsvadfb.pls 120.1 2005/11/24 03:25:21 srivikri noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_Adv_Filter_PVT
7 -- Purpose
8 --
9 -- History
10 -- 20-Aug-2003 rosharma Fixed bug 3104201.
11 -- 19-Sep-2003 rosharma Audience Data Sources Uptake.
12 --
13 -- NOTE
14 --
15 -- End of Comments
16 -- ===============================================================
17
18 ---------------------------------------------------------------------
19 -- PROCEDURE
20 -- check_filter_changes
21 --
22 -- HISTORY
23 -- 14-Oct-2002 nyostos Check if the Filter record has changed
24 ---------------------------------------------------------------------
25 PROCEDURE check_filter_changes(
26 p_filter_rec IN filter_rec_type,
30
27 x_rec_changed OUT NOCOPY VARCHAR2
28 );
29
31 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_Adv_Filter_PVT';
32 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvadfb.pls';
33
34 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
35 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
36
37
38 PROCEDURE Create_Filter_Row
39 (
40 p_api_version_number IN NUMBER,
41 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
42 p_commit IN VARCHAR2 := FND_API.G_FALSE,
43 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
44 x_return_status OUT NOCOPY VARCHAR2,
45 x_msg_count OUT NOCOPY NUMBER,
46 x_msg_data OUT NOCOPY VARCHAR2,
47 p_filter_rec IN filter_rec_type := g_miss_filter_rec,
48 x_query_param_id OUT NOCOPY NUMBER
49 )
50
51 IS
52 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Filter_Row';
53 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
54 l_return_status_full VARCHAR2(1);
55 l_org_id NUMBER := FND_API.G_MISS_NUM;
56 l_query_param_id NUMBER;
57 l_dummy NUMBER;
58
59 l_filter_rec filter_rec_type := p_filter_rec;
60
61 CURSOR c_id IS
62 SELECT JTF_PERZ_QUERY_PARAM_S.NEXTVAL
63 FROM dual;
64
65 CURSOR c_id_exists (l_id IN NUMBER) IS
66 SELECT 1
67 FROM JTF_PERZ_QUERY_PARAM
68 WHERE QUERY_PARAM_ID = l_id;
69
70 l_obj_type VARCHAR2(30);
71 l_obj_id NUMBER;
72 l_parameter_type VARCHAR2(30);
73 l_temp_str VARCHAR2(30);
74
75 BEGIN
76 -- Standard Start of API savepoint
77 SAVEPOINT CREATE_Filter_Row_PVT;
78
79 -- Standard call to check for call compatibility.
80 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
81 p_api_version_number,
82 l_api_name,
83 G_PKG_NAME)
84 THEN
85 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
86 END IF;
87
88 -- Initialize message list if p_init_msg_list is set to TRUE.
89 IF FND_API.to_Boolean( p_init_msg_list ) 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 -- Initialize API return status to SUCCESS
97 x_return_status := FND_API.G_RET_STS_SUCCESS;
98
99 -- Local variable initialization
100 IF l_filter_rec.query_param_id IS NULL OR l_filter_rec.query_param_id = FND_API.g_miss_num THEN
101 LOOP
102 l_dummy := NULL;
103 OPEN c_id;
104 FETCH c_id INTO l_query_param_id;
105 CLOSE c_id;
106
107 OPEN c_id_exists(l_query_param_id);
108 FETCH c_id_exists INTO l_dummy;
109 CLOSE c_id_exists;
110 EXIT WHEN l_dummy IS NULL;
111 END LOOP;
112 END IF;
113
114 l_filter_rec.query_param_id := l_query_param_id;
115 l_filter_rec.created_by := G_USER_ID;
116 l_filter_rec.last_updated_by := G_USER_ID;
117 l_filter_rec.last_update_date := sysdate;
118
119 -- =========================================================================
120 -- Validate Environment
121 -- =========================================================================
122
123 IF FND_GLOBAL.User_Id IS NULL THEN
124 AMS_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
125 RAISE FND_API.G_EXC_ERROR;
126 END IF;
127
128 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
129 -- Debug message
130 AMS_UTILITY_PVT.debug_message('Private API: Validate_Filter_Row');
131
132 -- Invoke validation procedures
133 Validate_Filter_Row(
134 p_api_version_number => 1.0,
135 p_init_msg_list => FND_API.G_FALSE,
136 p_validation_level => p_validation_level,
137 p_validation_mode => JTF_PLSQL_API.g_create,
138 p_filter_rec => l_filter_rec,
139 x_return_status => x_return_status,
140 x_msg_count => x_msg_count,
141 x_msg_data => x_msg_data);
142 END IF;
143
144 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
145 RAISE FND_API.G_EXC_ERROR;
146 END IF;
147
148
149 -- Debug Message
150 AMS_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
151
152
153 AMS_ADV_FILTER_PKG.Insert_Row
154 (
155 px_query_param_id => l_query_param_id,
156 p_query_id => l_filter_rec.query_id,
157 p_parameter_name => l_filter_rec.parameter_name,
158 p_parameter_type => l_filter_rec.parameter_type,
159 p_parameter_value => l_filter_rec.parameter_value,
160 p_parameter_condition => l_filter_rec.parameter_condition,
161 p_parameter_sequence => l_filter_rec.parameter_sequence,
162 p_created_by => l_filter_rec.created_by,
163 p_last_updated_by => l_filter_rec.last_updated_by,
164 p_last_update_date => l_filter_rec.last_update_date,
165 p_last_update_login => G_LOGIN_ID,
166 p_security_group_id => l_filter_rec.security_group_id
167 );
168
169 x_query_param_id := l_query_param_id;
170
171 -- Added by nyostos on Oct 14, 2002
172 -- Adding a Filter record to a Model/Scoring Run data sets
173 -- may INVALIDATE the Model if it has already been built or the Scoring
174 -- Run if it has already run. Call the appropriate procedure to check.
175 l_parameter_type := l_filter_rec.parameter_type;
176 IF l_parameter_type IS NOT NULL AND l_parameter_type <> FND_API.g_miss_char THEN
177
178 l_obj_type := SUBSTR(l_parameter_type, 1, INSTR(l_parameter_type, ';') - 1);
179
180 IF l_obj_type IN ('MODL', 'SCOR') THEN
181
182 l_temp_str := SUBSTR(l_parameter_type, 6, 30);
183 l_obj_id := to_number(SUBSTR(l_temp_str, 1, INSTR(l_temp_str,';') - 1)) ;
184
185 IF l_obj_type = 'MODL' THEN
186 AMS_DM_MODEL_PVT.handle_data_selection_changes(l_obj_id);
187 ELSE
188 AMS_DM_SCORE_PVT.handle_data_selection_changes(l_obj_id);
189 END IF;
190 END IF;
191 END IF;
192 -- End of addition by nyostos.
193
194 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
195 RAISE FND_API.G_EXC_ERROR;
196 END IF;
197
198 --
199 -- End of API body
200 --
201
202 -- Standard check for p_commit
203 IF FND_API.to_Boolean( p_commit )
204 THEN
205 COMMIT WORK;
206 END IF;
207
208
209 -- Debug Message
210 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
211
212 -- Standard call to get message count and if count is 1, get message info.
213 FND_MSG_PUB.Count_And_Get
214 (p_count => x_msg_count,
215 p_data => x_msg_data
216 );
217
218 EXCEPTION
219
220 WHEN AMS_Utility_PVT.resource_locked THEN
221 x_return_status := FND_API.g_ret_sts_error;
222 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
223
224 WHEN FND_API.G_EXC_ERROR THEN
225 ROLLBACK TO CREATE_Filter_Row_PVT;
226 x_return_status := FND_API.G_RET_STS_ERROR;
227 -- Standard call to get message count and if count=1, get the message
228 FND_MSG_PUB.Count_And_Get (
229 p_encoded => FND_API.G_FALSE,
230 p_count => x_msg_count,
231 p_data => x_msg_data
232 );
233
234 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
235 ROLLBACK TO CREATE_Filter_Row_PVT;
236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
237 -- Standard call to get message count and if count=1, get the message
238 FND_MSG_PUB.Count_And_Get (
239 p_encoded => FND_API.G_FALSE,
240 p_count => x_msg_count,
241 p_data => x_msg_data
242 );
243
244 WHEN OTHERS THEN
245 ROLLBACK TO CREATE_Filter_Row_PVT;
246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
247 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
248 THEN
249 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
250 END IF;
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 End Create_Filter_Row;
258
259
260 PROCEDURE Update_Filter_Row(
261 p_api_version_number IN NUMBER,
262 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
263 p_commit IN VARCHAR2 := FND_API.G_FALSE,
264 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
265 x_return_status OUT NOCOPY VARCHAR2,
266 x_msg_count OUT NOCOPY NUMBER,
267 x_msg_data OUT NOCOPY VARCHAR2,
268 p_filter_rec IN filter_rec_type
269 )
270 IS
271
272 CURSOR c_get_filter_row(p_query_param_id NUMBER) IS
273 SELECT *
274 FROM JTF_PERZ_QUERY_PARAM
275 WHERE query_param_id = p_query_param_id;
276
277 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Filter_Row';
278 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
279 l_filter_rec filter_rec_type := p_filter_rec;
280
281 l_query_param_id NUMBER;
282 l_ref_filter_rec c_get_filter_row%ROWTYPE ;
283 l_tar_filter_rec AMS_Adv_Filter_PVT.filter_rec_type := p_filter_rec;
284 l_rowid ROWID;
285
286 l_rec_changed VARCHAR2(1) := 'N';
287 l_obj_type VARCHAR2(30);
288 l_obj_id NUMBER;
289 l_parameter_type VARCHAR2(30);
290 l_temp_str VARCHAR2(30);
291
292 BEGIN
293 -- Standard Start of API savepoint
294 SAVEPOINT UPDATE_Filter_Row_PVT;
295
296 -- Standard call to check for call compatibility.
297 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
298 p_api_version_number,
299 l_api_name,
300 G_PKG_NAME)
301 THEN
302 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
303 END IF;
304
305 -- Initialize message list if p_init_msg_list is set to TRUE.
306 IF FND_API.to_Boolean( p_init_msg_list )
307 THEN
308 FND_MSG_PUB.initialize;
309 END IF;
310
311 -- Debug Message
312 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
313
314
315 -- Initialize API return status to SUCCESS
316 x_return_status := FND_API.G_RET_STS_SUCCESS;
317
318 l_query_param_id := l_tar_filter_rec.query_param_id;
319
320 OPEN c_get_Filter_row(l_tar_filter_rec.query_param_id);
321 FETCH c_get_filter_row INTO l_ref_filter_rec ;
322 IF ( c_get_Filter_row%NOTFOUND) THEN
323 AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
324 p_token_name => 'INFO',
325 p_token_value => 'Filter_Row'
326 ) ;
327 RAISE FND_API.G_EXC_ERROR;
328 END IF;
329 CLOSE c_get_filter_row;
330
331 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
332 -- Debug message
333 AMS_UTILITY_PVT.debug_message('Private API: Validate_Filter_Rec');
334
335 -- Invoke validation procedures
336 Validate_Filter_Row( p_api_version_number => 1.0,
337 p_init_msg_list => FND_API.G_FALSE,
338 p_validation_level => p_validation_level,
339 p_validation_mode => JTF_PLSQL_API.g_update,
340 p_filter_rec => l_filter_rec,
341 x_return_status => x_return_status,
342 x_msg_count => x_msg_count,
343 x_msg_data => x_msg_data);
344 END IF;
345
346 FND_MSG_PUB.Count_And_Get (
347 p_encoded => FND_API.G_FALSE,
348 p_count => x_msg_count,
349 p_data => x_msg_data
350 );
351
352 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
353 RAISE FND_API.G_EXC_ERROR;
354 END IF;
355
356 -- Added by nyostos on Oct 14, 2002
357 -- Check if the record has any changes
358 check_filter_changes(p_filter_rec, l_rec_changed);
359
360 AMS_UTILITY_PVT.debug_message('Private API: Before Calling Table Update Row');
361
362 AMS_ADV_FILTER_PKG.Update_Row(
363 px_query_param_id => p_filter_rec.query_param_id,
364 p_query_id => p_filter_rec.query_id,
365 p_parameter_name => p_filter_rec.parameter_name,
366 p_parameter_type => p_filter_rec.parameter_type,
367 p_parameter_value => p_filter_rec.parameter_value,
368 p_parameter_condition => p_filter_rec.parameter_condition,
369 p_parameter_sequence => p_filter_rec.parameter_sequence,
370 p_last_updated_by => G_USER_ID,
371 p_last_update_date => SYSDATE,
372 p_last_update_login => G_LOGIN_ID,
373 p_security_group_id => p_filter_rec.security_group_id
374 );
375
376 AMS_UTILITY_PVT.debug_message('Private API: After Update Row');
377
378 -- Added by nyostos on Oct 14, 2002
379 -- Adding a Filter record to a Model/Scoring Run data sets
380 -- may INVALIDATE the Model if it has already been built or the Scoring
381 -- Run if it has already run. Call the appropriate procedure to check.
382 l_parameter_type := p_filter_rec.parameter_type;
383
384 IF l_rec_changed = 'Y' THEN
385 IF l_parameter_type IS NOT NULL AND l_parameter_type <> FND_API.g_miss_char THEN
386
387 l_obj_type := SUBSTR(l_parameter_type, 1, INSTR(l_parameter_type,';') - 1);
388
389 IF l_obj_type IN ('MODL', 'SCOR') THEN
390
391 l_temp_str := SUBSTR(l_parameter_type, 6, 30);
392 l_obj_id := to_number(SUBSTR(l_temp_str, 1, INSTR(l_temp_str,';') - 1)) ;
393
394 IF l_obj_type = 'MODL' THEN
395 AMS_DM_MODEL_PVT.handle_data_selection_changes(l_obj_id);
396 ELSE
397 AMS_DM_SCORE_PVT.handle_data_selection_changes(l_obj_id);
398 END IF;
399 END IF;
400 END IF;
401 END IF;
402 -- End of addition by nyostos.
403
404 -- Standard check for p_commit
405 IF FND_API.to_Boolean( p_commit )
406 THEN
407 COMMIT WORK;
408 END IF;
409
410 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
411
412 -- Standard call to get message count and if count is 1, get message info.
413 FND_MSG_PUB.Count_And_Get
414 (p_count => x_msg_count,
415 p_data => x_msg_data
416 );
417 EXCEPTION
418
419 WHEN AMS_Utility_PVT.resource_locked THEN
420 x_return_status := FND_API.g_ret_sts_error;
421 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
422
423 WHEN FND_API.G_EXC_ERROR THEN
424 ROLLBACK TO UPDATE_Filter_Row_PVT;
425 x_return_status := FND_API.G_RET_STS_ERROR;
426 -- Standard call to get message count and if count=1, get the message
427 FND_MSG_PUB.Count_And_Get (
428 p_encoded => FND_API.G_FALSE,
429 p_count => x_msg_count,
430 p_data => x_msg_data
431 );
432
433 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
434 ROLLBACK TO UPDATE_Filter_Row_PVT;
435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
436 -- Standard call to get message count and if count=1, get the message
437 FND_MSG_PUB.Count_And_Get (
438 p_encoded => FND_API.G_FALSE,
439 p_count => x_msg_count,
440 p_data => x_msg_data
441 );
442
443 WHEN OTHERS THEN
444 ROLLBACK TO UPDATE_Filter_Row_PVT;
445 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
446 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
447 THEN
448 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
449 END IF;
450 -- Standard call to get message count and if count=1, get the message
451 FND_MSG_PUB.Count_And_Get (
452 p_encoded => FND_API.G_FALSE,
453 p_count => x_msg_count,
454 p_data => x_msg_data
455 );
456 End Update_Filter_Row;
457
458
459 PROCEDURE Delete_Filter_Row
460 (
461 p_api_version_number IN NUMBER,
462 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
463 p_commit IN VARCHAR2 := FND_API.G_FALSE,
464 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
465 x_return_status OUT NOCOPY VARCHAR2,
466 x_msg_count OUT NOCOPY NUMBER,
467 x_msg_data OUT NOCOPY VARCHAR2,
468 p_query_param_id IN NUMBER
469 )
470
471 IS
472
473 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Filter_Row';
474 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
475
476 CURSOR c_get_parameter_type(p_query_param_id NUMBER) IS
477 SELECT parameter_type
478 FROM JTF_PERZ_QUERY_PARAM
479 WHERE query_param_id = p_query_param_id;
480
481 l_obj_type VARCHAR2(30);
482 l_obj_id NUMBER;
483 l_parameter_type VARCHAR2(30);
484 l_temp_str VARCHAR2(30);
485
486 BEGIN
487 -- Standard Start of API savepoint
488 SAVEPOINT DELETE_Filter_Row_PVT;
489
490 -- Standard call to check for call compatibility.
491 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
492 p_api_version_number,
493 l_api_name,
494 G_PKG_NAME)
495 THEN
496 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
497 END IF;
498
499 -- Initialize message list if p_init_msg_list is set to TRUE.
500 IF FND_API.to_Boolean( p_init_msg_list )
501 THEN
502 FND_MSG_PUB.initialize;
503 END IF;
504
505 -- Debug Message
506 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
507
508
509 -- Initialize API return status to SUCCESS
510 x_return_status := FND_API.G_RET_STS_SUCCESS;
511
512 -- Get the parameter type
513 OPEN c_get_parameter_type(p_query_param_id);
514 FETCH c_get_parameter_type INTO l_parameter_type;
515 CLOSE c_get_parameter_type;
516
517 --
518 -- Api body
519 --
520 -- Debug Message
521 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
522
523 AMS_ADV_FILTER_PKG.Delete_Row(
524 p_query_param_id => p_query_param_id);
525
526 -- Added by nyostos on Oct 14, 2002
527 -- Adding a Filter record to a Model/Scoring Run data sets
528 -- may INVALIDATE the Model if it has already been built or the Scoring
529 -- Run if it has already run. Call the appropriate procedure to check.
530 IF l_parameter_type IS NOT NULL AND l_parameter_type <> FND_API.g_miss_char THEN
531
532 l_obj_type := SUBSTR(l_parameter_type, 1, INSTR(l_parameter_type,';') - 1);
533
534 IF l_obj_type IN ('MODL', 'SCOR') THEN
535
536 l_temp_str := SUBSTR(l_parameter_type, 6, 30);
537 l_obj_id := to_number(SUBSTR(l_temp_str, 1, INSTR(l_temp_str,';') - 1)) ;
538
539 IF l_obj_type = 'MODL' THEN
540 AMS_DM_MODEL_PVT.handle_data_selection_changes(l_obj_id);
541 ELSE
542 AMS_DM_SCORE_PVT.handle_data_selection_changes(l_obj_id);
543 END IF;
544 END IF;
545 END IF;
546 -- End of addition by nyostos.
547
548
549 -- End of API body
550
551 -- Standard check for p_commit
552 IF FND_API.to_Boolean( p_commit )
553 THEN
554 COMMIT WORK;
555 END IF;
556
557
558 -- Debug Message
559 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
560
561 -- Standard call to get message count and if count is 1, get message info.
562 FND_MSG_PUB.Count_And_Get
563 (p_count => x_msg_count,
564 p_data => x_msg_data
565 );
566 EXCEPTION
567
568 WHEN AMS_Utility_PVT.resource_locked THEN
569 x_return_status := FND_API.g_ret_sts_error;
570 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
571
572 WHEN FND_API.G_EXC_ERROR THEN
573 ROLLBACK TO DELETE_Filter_Row_PVT;
574 x_return_status := FND_API.G_RET_STS_ERROR;
575 -- Standard call to get message count and if count=1, get the message
576 FND_MSG_PUB.Count_And_Get (
577 p_encoded => FND_API.G_FALSE,
578 p_count => x_msg_count,
579 p_data => x_msg_data
580 );
581
582 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
583 ROLLBACK TO DELETE_Filter_Row_PVT;
584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
585 -- Standard call to get message count and if count=1, get the message
586 FND_MSG_PUB.Count_And_Get (
587 p_encoded => FND_API.G_FALSE,
588 p_count => x_msg_count,
589 p_data => x_msg_data
590 );
591
592 WHEN OTHERS THEN
593 ROLLBACK TO DELETE_Filter_Row_PVT;
594 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
595 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
596 THEN
597 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
598 END IF;
599 -- Standard call to get message count and if count=1, get the message
600 FND_MSG_PUB.Count_And_Get (
601 p_encoded => FND_API.G_FALSE,
602 p_count => x_msg_count,
603 p_data => x_msg_data
604 );
605 End Delete_Filter_Row;
606
607
608
609
610 PROCEDURE check_filter_row_uk_items
611 (
612 p_filter_rec IN filter_rec_type,
613 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
614 x_return_status OUT NOCOPY VARCHAR2
615 )
616
617 IS
618
619 l_valid_flag VARCHAR2(1);
620
621
622 BEGIN
623 x_return_status := FND_API.g_ret_sts_success;
624 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
625 l_valid_flag := 'T';
626 -- AMS_Utility_PVT.check_uniqueness(
627 -- 'JTF_PERZ_QUERY_PARAM',
628 -- 'query_param_id = 0'
629 -- || p_filter_rec.query_param_id ||''''
630 -- );
631 ELSE
632 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
633 'JTF_PERZ_QUERY_PARAM',
634 'query_param_id= ''' || p_filter_rec.query_param_id ||
635 ''' AND query_param_id <> ' || p_filter_rec.query_param_id
636 );
637 END IF;
638
639 IF l_valid_flag = FND_API.g_false THEN
640 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_QUERY_PARAM_ID_DUPLICATE');
641 x_return_status := FND_API.g_ret_sts_error;
642 RETURN;
643 END IF;
644
645 END check_filter_row_uk_items;
646
647 PROCEDURE check_filter_row_req_items
648 (
649 p_filter_rec IN filter_rec_type,
650 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
651 x_return_status OUT NOCOPY VARCHAR2
652 )
653
654 IS
655
656 BEGIN
657 x_return_status := FND_API.g_ret_sts_success;
658
659 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
660
661 AMS_UTILITY_PVT.debug_message('Private API: Inside check_filter_row_req_items');
662
663 IF p_filter_rec.query_id = FND_API.g_miss_num OR p_filter_rec.query_id IS NULL THEN
664 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
665 FND_MESSAGE.set_token('MISS_FIELD','QUERY_ID');
666 x_return_status := FND_API.g_ret_sts_error;
667 RETURN;
668 END IF;
669
670 IF p_filter_rec.parameter_name = FND_API.g_miss_char OR p_filter_rec.parameter_name IS NULL THEN
671 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
672 FND_MESSAGE.set_token('MISS_FIELD','PAREMETER_NAME');
673 x_return_status := FND_API.g_ret_sts_error;
674 RETURN;
675 END IF;
676
680 x_return_status := FND_API.g_ret_sts_error;
677 IF p_filter_rec.parameter_type = FND_API.g_miss_char OR p_filter_rec.parameter_type IS NULL THEN
678 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
679 FND_MESSAGE.set_token('MISS_FIELD','PARAMETER_NAME');
681 RETURN;
682 END IF;
683
684 IF p_filter_rec.parameter_value = FND_API.g_miss_char OR p_filter_rec.parameter_value IS NULL THEN
685 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
686 FND_MESSAGE.set_token('MISS_FIELD','PAREMETER_VALUE');
687 x_return_status := FND_API.g_ret_sts_error;
688 RETURN;
689 END IF;
690
691 IF p_filter_rec.parameter_condition = FND_API.g_miss_char OR p_filter_rec.parameter_condition IS NULL THEN
692 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
693 FND_MESSAGE.set_token('MISS_FIELD','PAREMETER_CONDITION');
694 x_return_status := FND_API.g_ret_sts_error;
695 RETURN;
696 END IF;
697
698 IF p_filter_rec.created_by = FND_API.g_miss_num OR p_filter_rec.created_by IS NULL THEN
699 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
700 FND_MESSAGE.set_token('MISS_FIELD','CREATED_BY');
701 x_return_status := FND_API.g_ret_sts_error;
702 RETURN;
703 END IF;
704
705 IF p_filter_rec.last_updated_by = FND_API.g_miss_num OR p_filter_rec.last_updated_by IS NULL THEN
706 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
707 FND_MESSAGE.set_token('MISS_FIELD','LAST_UPDATED_BY');
708 x_return_status := FND_API.g_ret_sts_error;
709 RETURN;
710 END IF;
711
712 IF p_filter_rec.last_update_date = FND_API.g_miss_date OR p_filter_rec.last_update_date IS NULL THEN
713 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
714 FND_MESSAGE.set_token('MISS_FIELD','LAST_UPDATE_DATE');
715 x_return_status := FND_API.g_ret_sts_error;
716 RETURN;
717 END IF;
718 ELSE
719
720 IF p_filter_rec.query_param_id IS NULL THEN
721 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_Filter_row_no_query_param_id');
722 x_return_status := FND_API.g_ret_sts_error;
723 RETURN;
724 END IF;
725
726 IF p_filter_rec.query_id IS NULL THEN
727 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_Filter_row_no_query_id');
728 x_return_status := FND_API.g_ret_sts_error;
729 RETURN;
730 END IF;
731
732 IF p_filter_rec.parameter_name IS NULL THEN
733 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_Filter_row_no_parameter_name');
734 x_return_status := FND_API.g_ret_sts_error;
735 RETURN;
736 END IF;
737
738 IF p_filter_rec.parameter_type IS NULL THEN
739 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_Filter_row_no_parameter_type');
740 x_return_status := FND_API.g_ret_sts_error;
741 RETURN;
742 END IF;
743
744 IF p_filter_rec.parameter_value IS NULL THEN
745 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_Filter_row_no_parameter_value');
746 x_return_status := FND_API.g_ret_sts_error;
747 RETURN;
748 END IF;
749
750 IF p_filter_rec.parameter_condition IS NULL THEN
751 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_Filter_row_no_parameter_condition');
752 x_return_status := FND_API.g_ret_sts_error;
753 RETURN;
754 END IF;
755 END IF;
756
757 END check_filter_row_req_items;
758
759
760 PROCEDURE Check_filter_Items (
761 p_filter_rec IN filter_rec_type,
762 p_validation_mode IN VARCHAR2,
763 x_return_status OUT NOCOPY VARCHAR2
764 )
765 IS
766 BEGIN
767
768 -- Check Items Uniqueness API calls
769 AMS_UTILITY_PVT.debug_message('Private API: Inside Check Filter Items. Calling UK Items');
770 check_filter_row_uk_items(
771 p_filter_rec => p_filter_rec,
772 p_validation_mode => p_validation_mode,
773 x_return_status => x_return_status);
774 IF x_return_status <> FND_API.g_ret_sts_success THEN
775 RETURN;
776 END IF;
777
778 -- Check Items Required/NOT NULL API calls
779
780 check_filter_row_req_items(
781 p_filter_rec => p_filter_rec,
782 p_validation_mode => p_validation_mode,
783 x_return_status => x_return_status);
784 IF x_return_status <> FND_API.g_ret_sts_success THEN
785 RETURN;
786 END IF;
787
788 END Check_filter_Items;
789
790
791 PROCEDURE Complete_Filter_Rec
792 (
793 p_filter_rec IN filter_rec_type,
794 x_complete_rec OUT NOCOPY filter_rec_type
795 )
796
797 IS
798 l_return_status VARCHAR2(1);
799
800 CURSOR c_complete IS
801 SELECT *
802 FROM jtf_perz_query_param
803 WHERE query_param_id = p_filter_rec.query_param_id ;
804
805 l_filter_rec c_complete%ROWTYPE;
806
807 BEGIN
808 x_complete_rec := p_filter_rec;
809
810 OPEN c_complete;
811 FETCH c_complete INTO l_filter_rec;
812 CLOSE c_complete;
813
814 -- query_param_id
815 IF p_filter_rec.query_param_id = FND_API.g_miss_num THEN
816 x_complete_rec.query_param_id := l_filter_rec.query_param_id;
817 END IF;
818
819 -- parameter_name
820 IF p_filter_rec.parameter_name = FND_API.g_miss_char THEN
821 x_complete_rec.parameter_name := l_filter_rec.parameter_name;
822 END IF;
823
824 -- parameter_type
825 IF p_filter_rec.parameter_type = FND_API.g_miss_char THEN
826 x_complete_rec.parameter_type := l_filter_rec.parameter_type;
827 END IF;
828
829 -- parameter_value
830 IF p_filter_rec.parameter_value = FND_API.g_miss_char THEN
831 x_complete_rec.parameter_value := l_filter_rec.parameter_value;
832 END IF;
833
834 -- parameter_condition
835 IF p_filter_rec.parameter_condition = FND_API.g_miss_char THEN
836 x_complete_rec.parameter_condition := l_filter_rec.parameter_condition;
837 END IF;
838
839
840 -- created_by
841 IF p_filter_rec.created_by = FND_API.g_miss_num THEN
842 x_complete_rec.created_by := l_filter_rec.created_by;
843 END IF;
844
845
846 -- last_updated_by
847 IF p_filter_rec.last_updated_by = FND_API.g_miss_num THEN
848 x_complete_rec.last_updated_by := l_filter_rec.last_updated_by;
849 END IF;
850
851 -- last_update_date
852 IF p_filter_rec.last_update_date = FND_API.g_miss_date THEN
853 x_complete_rec.last_update_date := l_filter_rec.last_update_date;
854 END IF;
855
856 -- last_update_login
857 IF p_filter_rec.last_update_login = FND_API.g_miss_num THEN
858 x_complete_rec.last_update_login := l_filter_rec.last_update_login;
859 END IF;
860
861 -- security_group_id
862 IF p_filter_rec.security_group_id = FND_API.g_miss_num THEN
863 x_complete_rec.security_group_id := l_filter_rec.security_group_id;
864 END IF;
865
866 END Complete_Filter_Rec;
867
868
869 PROCEDURE Validate_Filter_Row
870 (
871 p_api_version_number IN NUMBER,
872 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
873 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
874 p_filter_rec IN filter_rec_type,
875 p_validation_mode IN VARCHAR2,
876 x_return_status OUT NOCOPY VARCHAR2,
877 x_msg_count OUT NOCOPY NUMBER,
878 x_msg_data OUT NOCOPY VARCHAR2
879 )
880
881 IS
882
883 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Filter_Row';
884 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
885 l_filter_rec filter_rec_type := p_filter_rec;
886
887 BEGIN
888 -- Standard Start of API savepoint
889 SAVEPOINT VALIDATE_Filter_Row;
890
891 AMS_UTILITY_PVT.debug_message('Private API: Inside' || l_api_name );
892
893 -- Standard call to check for call compatibility.
894 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
895 p_api_version_number,
896 l_api_name,
897 G_PKG_NAME)
898 THEN
899 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
900 END IF;
901
902 -- Initialize message list if p_init_msg_list is set to TRUE.
903 IF FND_API.to_Boolean( p_init_msg_list )
904 THEN
905 FND_MSG_PUB.initialize;
906 END IF;
907
908 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
909 Check_Filter_Items(
910 p_filter_rec => p_filter_rec,
911 p_validation_mode => p_validation_mode,
912 x_return_status => x_return_status
913 );
914
915 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
916 RAISE FND_API.G_EXC_ERROR;
917 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
918 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
919 END IF;
920 END IF;
921
922 Complete_Filter_Rec(
923 p_filter_rec => p_filter_rec,
924 x_complete_rec => l_filter_rec
925 );
926
927 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
928 Validate_Filter_Row_Rec
929 (
930 p_api_version_number => 1.0,
931 p_init_msg_list => FND_API.G_FALSE,
932 x_return_status => x_return_status,
933 x_msg_count => x_msg_count,
934 x_msg_data => x_msg_data,
935 p_filter_rec => l_filter_rec
936 );
937
938 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
939 RAISE FND_API.G_EXC_ERROR;
940 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
941 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
942 END IF;
943 END IF;
944
945
946 -- Debug Message
947 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
948
949
950 -- Initialize API return status to SUCCESS
951 x_return_status := FND_API.G_RET_STS_SUCCESS;
952
953
954 -- Debug Message
955 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
956
957 -- Standard call to get message count and if count is 1, get message info.
958 FND_MSG_PUB.Count_And_Get
959 (p_count => x_msg_count,
960 p_data => x_msg_data
961 );
962 EXCEPTION
963
967
964 WHEN AMS_Utility_PVT.resource_locked THEN
965 x_return_status := FND_API.g_ret_sts_error;
966 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
968 WHEN FND_API.G_EXC_ERROR THEN
969 ROLLBACK TO VALIDATE_Filter_Row;
970 x_return_status := FND_API.G_RET_STS_ERROR;
971 -- Standard call to get message count and if count=1, get the message
972 FND_MSG_PUB.Count_And_Get (
973 p_encoded => FND_API.G_FALSE,
974 p_count => x_msg_count,
975 p_data => x_msg_data
976 );
977
978 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
979 ROLLBACK TO VALIDATE_Filter_Row;
980 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
981 -- Standard call to get message count and if count=1, get the message
982 FND_MSG_PUB.Count_And_Get (
983 p_encoded => FND_API.G_FALSE,
984 p_count => x_msg_count,
985 p_data => x_msg_data
986 );
987
988 WHEN OTHERS THEN
989 ROLLBACK TO VALIDATE_Filter_Row;
990 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
991 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
992 THEN
993 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
994 END IF;
995 -- Standard call to get message count and if count=1, get the message
996 FND_MSG_PUB.Count_And_Get (
997 p_encoded => FND_API.G_FALSE,
998 p_count => x_msg_count,
999 p_data => x_msg_data
1000 );
1001 End Validate_Filter_Row;
1002
1003
1004 PROCEDURE Validate_Filter_Row_Rec
1005 (
1006 p_api_version_number IN NUMBER,
1007 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1008 x_return_status OUT NOCOPY VARCHAR2,
1009 x_msg_count OUT NOCOPY NUMBER,
1010 x_msg_data OUT NOCOPY VARCHAR2,
1011 p_filter_rec IN filter_rec_type
1012 )
1013 IS
1014
1015 BEGIN
1016 -- Initialize message list if p_init_msg_list is set to TRUE.
1017 IF FND_API.to_Boolean( p_init_msg_list )
1018 THEN
1019 FND_MSG_PUB.initialize;
1020 END IF;
1021
1022 -- Initialize API return status to SUCCESS
1023 x_return_status := FND_API.G_RET_STS_SUCCESS;
1024
1025 -- Hint: Validate data
1026 -- If data not valid
1027 -- THEN
1028 -- x_return_status := FND_API.G_RET_STS_ERROR;
1029
1030 -- Debug Message
1031 AMS_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
1032 -- Standard call to get message count and if count is 1, get message info.
1033 FND_MSG_PUB.Count_And_Get
1034 (p_count => x_msg_count,
1035 p_data => x_msg_data
1036 );
1037 END Validate_Filter_Row_Rec;
1038
1039
1040 -- Procedure to get the Filter Record on the basis of parameters passed.
1041
1042 PROCEDURE Get_filter_data
1043 ( p_objType IN VARCHAR2,
1044 p_objectId IN NUMBER,
1045 p_dataSourceId IN NUMBER,
1046 x_return_status OUT NOCOPY VARCHAR2,
1047 x_msg_count OUT NOCOPY NUMBER,
1048 x_msg_data OUT NOCOPY VARCHAR2,
1049 x_filters OUT NOCOPY filter_rec_tbl_type
1050 )
1051
1052 IS
1053 CURSOR c_queryId IS
1054 SELECT qry.query_id
1055 FROM JTF_PERZ_PROFILE profile, JTF_PERZ_QUERY qry
1056 WHERE profile.profile_name = 'AMS_DEFAULT_PROFILE'
1057 AND profile.profile_id = qry.profile_id
1058 AND qry.query_name = 'advancedFilter';
1059
1060
1061 --kbasavar 12/27/2004 modified for 3906434
1062 CURSOR c_filterRec(l_qid IN NUMBER ,l_ptype in VARCHAR2) IS
1063 SELECT QParam.query_param_id ,QParam.parameter_name,QParam.parameter_type,QParam.parameter_value
1064 ,QParam.parameter_condition,QParam.parameter_sequence,QParam.created_by,QParam.last_updated_by
1065 ,QParam.last_update_date,QParam.last_update_login
1066 FROM JTF_PERZ_QUERY Query, JTF_PERZ_QUERY_PARAM QParam, AMS_LIST_SRC_FIELDS Fields
1067 WHERE (Query.query_id = l_qid
1068 AND QParam.parameter_type = l_ptype
1069 AND QParam.parameter_name = fields.list_source_field_id
1070 AND Query.query_id = QParam.query_id );
1071
1072 /* SELECT query_param_id ,parameter_name,parameter_type,parameter_value
1073 ,parameter_condition,parameter_sequence,created_by,last_updated_by
1074 ,last_update_date,last_update_login
1075 FROM jtf_perz_query_param
1076 WHERE query_id = l_qid
1077 AND parameter_type = l_ptype;
1078 */
1079
1080 CURSOR c_fieldName(l_fieldId IN NUMBER , p_ds_id IN NUMBER) IS
1081 SELECT b.source_object_name || '.' || a.source_column_name
1082 FROM ams_list_src_fields_vl a , ams_list_src_types b , ams_list_src_types d
1083 WHERE a.list_source_field_id = l_fieldId
1084 AND a.list_source_type_id = b.list_source_type_id
1085 AND d.list_source_type_id = p_ds_id
1086 AND d.enabled_flag = 'Y'
1087 AND a.enabled_flag = 'Y'
1088 AND b.enabled_flag = 'Y'
1089 AND (EXISTS (SELECT 1 from ams_list_src_type_assocs c WHERE c.master_source_type_id = p_ds_id
1090 AND c.sub_source_type_id = b.list_source_type_id AND c.enabled_flag = 'Y')
1091 OR b.list_source_type_id = p_ds_id)
1092 ;
1093
1094 l_nQueryId NUMBER;
1095 l_curfilterRec c_filterRec%ROWTYPE;
1096 l_filterRec filter_rec_type;
1097 l_filterRec_tbl filter_rec_tbl_type;
1098 l_iIndex NUMBER;
1099 l_parameterType VARCHAR2(60);
1100 l_fieldName VARCHAR2(60);
1101 l_api_name CONSTANT VARCHAR2(40) := 'Get_Filter_data';
1102
1103 BEGIN
1104
1105 -- Initialize API return status to SUCCESS
1106 x_return_status := FND_API.G_RET_STS_SUCCESS;
1107
1108 OPEN c_queryId;
1109 FETCH c_queryId INTO l_nQueryId;
1110 CLOSE c_queryId;
1111
1112 IF p_objType = FND_API.g_miss_char OR p_objType IS NULL THEN
1113 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_OBJ_TYPE');
1114 FND_MESSAGE.set_token('MISS_FIELD','OBJ_TYPE');
1115 x_return_status := FND_API.g_ret_sts_error;
1116 RETURN;
1117 END IF;
1118 IF p_objectId = FND_API.g_miss_num OR p_objType IS NULL THEN
1119 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_OBJECT_ID');
1120 FND_MESSAGE.set_token('MISS_FIELD','OBJECT_ID');
1121 x_return_status := FND_API.g_ret_sts_error;
1122 RETURN;
1123 END IF;
1124 IF p_dataSourceId = FND_API.g_miss_num OR p_dataSourceId IS NULL THEN
1125 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_DATASOURCE_ID');
1126 FND_MESSAGE.set_token('MISS_FIELD','DATASOURCE_ID');
1127 x_return_status := FND_API.g_ret_sts_error;
1128 RETURN;
1129 END IF;
1130
1131 l_parameterType := p_objType||';'||to_char(p_objectId)||';'||to_char(p_dataSourceId);
1132
1133 AMS_UTILITY_PVT.debug_message('Private API: ' || 'Get_filter_date ' || 'Parameter Name is:: ' || l_parameterType);
1134
1135 l_iIndex := 0;
1136
1137 OPEN c_filterRec(l_nQueryId,l_parameterType);
1138
1139 LOOP
1140 FETCH c_filterRec INTO l_curfilterRec;
1141 EXIT WHEN c_FilterRec%NOTFOUND;
1142
1143 OPEN c_fieldName(to_number(l_curfilterRec.parameter_name) , p_dataSourceId);
1144 FETCH c_fieldName INTO l_fieldName;
1145 IF c_fieldName%NOTFOUND THEN
1146 CLOSE c_fieldName;
1147 AMS_UTILITY_PVT.debug_message('Disabled attribute/Disabled Data Source/Disabled Data Source Association....raising exception');
1148 AMS_Utility_PVT.error_message('AMS_FILTER_ATTRIBUTE_DISABLED');
1149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1150 END IF;
1151 CLOSE c_fieldName;
1152
1153 AMS_UTILITY_PVT.debug_message('Private API: ' || 'Get_filter_data ' || 'field Name is:: ' || l_fieldName);
1154
1155 l_filterRec.query_param_id := l_curfilterRec.query_param_id;
1156 l_filterRec.query_id := l_nQueryId;
1157 l_filterRec.parameter_name := l_fieldName;
1158 l_filterRec.parameter_type := l_curfilterRec.parameter_type;
1159 l_filterRec.parameter_value := l_curfilterRec.parameter_value;
1160 l_filterRec.parameter_condition := l_curfilterRec.parameter_condition;
1161 l_filterRec.parameter_sequence := l_curfilterRec.parameter_sequence;
1162 l_filterRec.created_by := l_curfilterRec.created_by;
1163 l_filterRec.last_updated_by := l_curfilterRec.last_updated_by;
1164 l_filterRec.last_update_date := l_curfilterRec.last_update_date;
1165 l_filterRec.last_update_login := l_curfilterRec.last_update_login;
1166
1167 l_iIndex := l_iIndex+1;
1168
1169 l_filterRec_tbl(l_iIndex) := l_filterRec;
1170
1171
1172 END LOOP;
1173
1174 CLOSE c_filterRec;
1175
1176 x_filters := l_filterRec_tbl;
1177
1178 FND_MSG_PUB.Count_And_Get
1179 ( p_count => x_msg_count,
1180 p_data => x_msg_data
1181 );
1182
1183 EXCEPTION
1184
1185 WHEN FND_API.G_EXC_ERROR THEN
1186 CLOSE c_filterRec;
1187 x_return_status := FND_API.G_RET_STS_ERROR;
1188 -- Standard call to get message count and if count=1, get the message
1189 FND_MSG_PUB.Count_And_Get
1190 (
1191 p_encoded => FND_API.G_FALSE,
1192 p_count => x_msg_count,
1193 p_data => x_msg_data
1194 );
1195
1196 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1198 -- Standard call to get message count and if count=1, get the message
1199 FND_MSG_PUB.Count_And_Get
1203 p_data => x_msg_data
1200 (
1201 p_encoded => FND_API.G_FALSE,
1202 p_count => x_msg_count,
1204 );
1205
1206 WHEN OTHERS THEN
1207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1208 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1209 THEN
1210 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1211 END IF;
1212 -- Standard call to get message count and if count=1, get the message
1213 FND_MSG_PUB.Count_And_Get
1214 (
1215 p_encoded => FND_API.G_FALSE,
1216 p_count => x_msg_count,
1217 p_data => x_msg_data
1218 );
1219
1220
1221
1222 END Get_filter_data;
1223
1224 ---------------------------------------------------------------------
1225 -- PROCEDURE
1226 -- check_filter_changes
1227 --
1228 -- HISTORY
1229 -- 14-Oct-2002 nyostos Check if the Filter record has changed
1230 ---------------------------------------------------------------------
1231 PROCEDURE check_filter_changes(
1232 p_filter_rec IN filter_rec_type,
1233 x_rec_changed OUT NOCOPY VARCHAR2
1234 )
1235 IS
1236
1237 CURSOR c_get_filter_row(p_query_param_id NUMBER) IS
1238 SELECT *
1239 FROM JTF_PERZ_QUERY_PARAM
1240 WHERE query_param_id = p_query_param_id;
1241
1242 l_ref_filter_rec c_get_filter_row%ROWTYPE ;
1243
1244 BEGIN
1245
1246 -- Initialize record changed flag to 'N'
1247 x_rec_changed := 'N';
1248
1249 -- Open cursor to get the reference filter record.
1250 OPEN c_get_filter_row (p_filter_rec.query_param_id);
1251 FETCH c_get_filter_row INTO l_ref_filter_rec;
1252 CLOSE c_get_filter_row;
1253
1254 -- parameter_name
1255 IF (l_ref_filter_rec.parameter_name IS NULL AND p_filter_rec.parameter_name IS NOT NULL) OR
1256 (l_ref_filter_rec.parameter_name <> p_filter_rec.parameter_name) THEN
1257 x_rec_changed := 'Y';
1258 RETURN;
1259 END IF;
1260
1261 -- parameter_type
1262 IF (l_ref_filter_rec.parameter_type IS NULL AND p_filter_rec.parameter_type IS NOT NULL) OR
1263 (l_ref_filter_rec.parameter_type <> p_filter_rec.parameter_type) THEN
1264 x_rec_changed := 'Y';
1265 RETURN;
1266 END IF;
1267
1268 -- parameter_value
1269 IF (l_ref_filter_rec.parameter_value IS NULL AND p_filter_rec.parameter_value IS NOT NULL) OR
1270 (l_ref_filter_rec.parameter_value <> p_filter_rec.parameter_value) THEN
1271 x_rec_changed := 'Y';
1272 RETURN;
1273 END IF;
1274
1275 -- parameter_condition
1276 IF (l_ref_filter_rec.parameter_condition IS NULL AND p_filter_rec.parameter_condition IS NOT NULL) OR
1277 (l_ref_filter_rec.parameter_condition <> p_filter_rec.parameter_condition) THEN
1278 x_rec_changed := 'Y';
1279 RETURN;
1280 END IF;
1281
1282 END check_filter_changes;
1283
1284 PROCEDURE copy_filter_data (
1285 p_api_version IN NUMBER,
1286 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1287 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1288 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1289 p_objType IN VARCHAR2,
1290 p_old_objectId IN NUMBER,
1291 p_new_objectId IN NUMBER,
1292 p_dataSourceId IN NUMBER,
1293 x_return_status OUT NOCOPY VARCHAR2,
1294 x_msg_count OUT NOCOPY NUMBER,
1295 x_msg_data OUT NOCOPY VARCHAR2
1296 )
1297 IS
1298 L_API_NAME CONSTANT VARCHAR2(30) := 'copy_filter_data';
1299 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1300
1301 CURSOR c_fieldId(p_fieldName IN VARCHAR2 , p_ds_id IN NUMBER) IS
1302 SELECT to_char(list_source_field_id)
1303 FROM ams_list_src_fields_vl
1304 WHERE source_column_name = SUBSTR(p_fieldName , INSTR(p_fieldName , '.') + 1)
1305 AND list_source_type_id = p_ds_id
1306 ;
1307 l_fieldName VARCHAR2(60);
1308
1309 --start kbasavar 4/23/2004 for bug 3565835
1310 CURSOR c_get_data_source(p_query_param_id IN NUMBER) IS
1311 SELECT Fields.LIST_SOURCE_TYPE_ID
1312 FROM JTF_PERZ_QUERY_PARAM QParam, AMS_LIST_SRC_FIELDS fields
1313 WHERE (QParam.QUERY_PARAM_ID = p_query_param_id AND QParam.parameter_name = fields.list_source_field_id );
1314 l_data_source NUMBER ;
1315 --end kbasavar 4/23/2004 for bug 3565835
1316
1317 l_ref_filter_tbl filter_rec_tbl_type;
1318 l_new_filter_rec filter_rec_type;
1319 l_query_param_id NUMBER;
1320
1321 BEGIN
1322 -- Standard Start of API savepoint
1323 SAVEPOINT ams_filter_pvt_copy_filter;
1324
1325 -- Standard call to check for call compatibility.
1326 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1327 p_api_version,
1328 l_api_name,
1329 G_PKG_NAME)
1330 THEN
1331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1332 END IF;
1333
1334 -- Initialize message list if p_init_msg_list is set to TRUE.
1335 IF FND_API.to_Boolean( p_init_msg_list )THEN
1336 FND_MSG_PUB.initialize;
1337 END IF;
1338
1339 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
1340
1341 -- Initialize API return status to SUCCESS
1342 x_return_status := FND_API.G_RET_STS_SUCCESS;
1343
1344 --
1345 -- Start of API body.
1346 --
1347 -- Get the adv filter records for passed object type and object id
1348 -- For each record, replace the parameter type and call create_filter_row
1349 get_filter_data (
1350 p_objType => p_objType,
1351 p_objectId => p_old_objectId,
1352 p_dataSourceId => p_dataSourceId,
1353 x_return_status => x_return_status,
1354 x_msg_count => x_msg_count,
1355 x_msg_data => x_msg_data,
1356 x_filters => l_ref_filter_tbl
1357 );
1358
1359 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1361 END IF;
1362
1363 IF l_ref_filter_tbl IS NULL THEN
1364 RETURN;
1365 END IF;
1366
1367 IF l_ref_filter_tbl.COUNT = 0 THEN
1368 RETURN;
1369 END IF;
1370
1371 FOR i IN 1..l_ref_filter_tbl.COUNT LOOP
1372 --start kbasavar 4/23/2004 for bug 3565835
1373 OPEN c_get_data_source(l_ref_filter_tbl(i).query_param_id);
1374 FETCH c_get_data_source INTO l_data_source;
1375 CLOSE c_get_data_source;
1376 --end kbasavar 4/23/2004 for bug 3565835
1377 OPEN c_fieldId (l_ref_filter_tbl(i).parameter_name , l_data_source); --kbasavar 4/23/2004
1378 FETCH c_fieldId INTO l_fieldName;
1379 CLOSE c_fieldId;
1380 l_new_filter_rec.query_id := l_ref_filter_tbl(i).query_id;
1381 l_new_filter_rec.parameter_name := l_fieldName;
1382 l_new_filter_rec.parameter_type := p_objType||';'||to_char(p_new_objectId)||';'||to_char(p_dataSourceId);
1383 l_new_filter_rec.parameter_value := l_ref_filter_tbl(i).parameter_value;
1384 l_new_filter_rec.parameter_condition := l_ref_filter_tbl(i).parameter_condition;
1385 l_new_filter_rec.parameter_sequence := l_ref_filter_tbl(i).parameter_sequence;
1386 l_new_filter_rec.created_by := G_USER_ID;
1387 l_new_filter_rec.last_updated_by := G_USER_ID;
1388 l_new_filter_rec.last_update_date := SYSDATE;
1389 l_new_filter_rec.security_group_id := l_ref_filter_tbl(i).security_group_id;
1390
1391 --create the filter row
1392 Create_Filter_Row
1393 (
1394 p_api_version_number => 1.0,
1395 p_init_msg_list => FND_API.G_FALSE,
1396 p_commit => FND_API.G_FALSE,
1397 p_validation_level => p_validation_level,
1398 x_return_status => x_return_status,
1399 x_msg_count => x_msg_count,
1400 x_msg_data => x_msg_data,
1401 p_filter_rec => l_new_filter_rec,
1402 x_query_param_id => l_query_param_id
1403 );
1404 END LOOP;
1405
1406 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1407 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1408 END IF;
1409 --
1410 -- End of API body.
1411 --
1412
1413 -- Standard check for p_commit
1414 IF FND_API.to_Boolean( p_commit ) THEN
1415 COMMIT WORK;
1416 END IF;
1417
1418
1419 -- Debug Message
1420 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' end');
1421
1422 -- Standard call to get message count and if count is 1, get message info.
1423 FND_MSG_PUB.Count_And_Get (
1424 p_count => x_msg_count,
1425 p_data => x_msg_data
1426 );
1427 EXCEPTION
1428 WHEN FND_API.G_EXC_ERROR THEN
1429 ROLLBACK TO ams_filter_pvt_copy_filter;
1430 x_return_status := FND_API.G_RET_STS_ERROR;
1431 -- Standard call to get message count and if count=1, get the message
1432 FND_MSG_PUB.Count_And_Get (
1433 p_encoded => FND_API.G_FALSE,
1434 p_count => x_msg_count,
1435 p_data => x_msg_data
1436 );
1437 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1438 ROLLBACK TO ams_filter_pvt_copy_filter;
1439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1440 -- Standard call to get message count and if count=1, get the message
1441 FND_MSG_PUB.Count_And_Get (
1442 p_encoded => FND_API.G_FALSE,
1443 p_count => x_msg_count,
1444 p_data => x_msg_data
1445 );
1446 WHEN OTHERS THEN
1447 ROLLBACK TO ams_filter_pvt_copy_filter;
1448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1449 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1450 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1451 END IF;
1452 -- Standard call to get message count and if count=1, get the message
1453 FND_MSG_PUB.Count_And_Get (
1454 p_encoded => FND_API.G_FALSE,
1455 p_count => x_msg_count,
1456 p_data => x_msg_data
1457 );
1458 END copy_filter_data;
1459
1460 END AMS_Adv_Filter_PVT;