[Home] [Help]
PACKAGE BODY: APPS.IEX_FILTER_PUB
Source
1 PACKAGE BODY IEX_FILTER_PUB AS
2 /* $Header: iexpfilb.pls 120.13 2010/07/28 16:34:35 barathsr ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IEX_FILTER_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexpfilb.pls';
6 G_Batch_Size NUMBER := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '1000'));
7 PG_DEBUG NUMBER;
8
9 /*
10 || Overview: this function will return a dynamic SQL statement to
11 || execute as the universe of objects to score for a particular
12 || scoring engine
13 ||
14 || Parameter: p_object_id Scoring_Engine or Strategy Engine attached to the universe
15 || p_object_type = EITHER SCORE OR STRATEGY
16 ||
17 || Return value: table of ids for the universe
18 ||
19 || Source Tables: IEX_OBJECT_FILTERS
20 ||
21 || Target Tables: none
22 ||
23 || Creation date: 01/09/02 3:38:PM
24 ||
25 || Major Modifications: when who what
26 || 01/09/02 raverma created
27 */
28 -- Start for bug 9387044
29 function buildsql(p_object_id IN NUMBER
30 ,p_object_type IN VARCHAR2
31 ,p_query_obj_id in varchar2 --Added for Bug 9670348 27-May-2009 barathsr
32 ,p_limit_rows in number default null) --Added for Bug 9670348 27-May-2009 barathsr
33 return varchar2
34 is
35 l_select_column varchar2(30);
36 l_entity_name varchar2(30);
37 vstr1 varchar2(100);
38 vstr2 varchar2(100);
39 vstr3 varchar2(100);
40 vstr4 varchar2(100);
41 vPLSQL varchar2(1000);
42 begin
43
44 vstr1 := 'SELECT ';
45 vstr2 := ' FROM ';
46 vstr3 := ' WHERE ';
47 vstr4 := ' IS NOT NULL ';
48
49 execute immediate
50 'Select select_column, entity_name ' ||
51 ' From IEX_OBJECT_FILTERS ' ||
52 ' Where object_id = :p_object_id AND ' ||
53 ' Object_Filter_Type = :p_object_Type '
54 into l_select_column , l_entity_name
55 using p_object_id, p_object_Type;
56
57 vPLSQL:= vstr1 || l_select_column ||
58 vstr2 || l_entity_name ||
59 vstr3 || l_select_column || vstr4;
60
61 --Begin Bug 9670348 27-May-2010 barathsr
62 if p_query_obj_id is not null then
63 vPLSQL:= vPLSQL||' AND ' || l_select_column || ' IN ('||p_query_obj_id||')';
64 end if;
65
66 if p_limit_rows is not null then
67 vPLSQL:= vPLSQL|| ' AND ROWNUM <= '|| p_limit_rows;
68 end if;
69 --End Bug 9670348 27-May-2010 barathsr
70
71 vPLSQL:=vPLSQL|| ' and '||l_select_column|| ' >0 '; --Added for Bug 9790046 22-Jun-2010 barathsr
72 vPLSQL:= vPLSQL|| ' ORDER BY ' || l_select_column;
73 FND_FILE.PUT_LINE(FND_FILE.LOG,'Scoring engine object filter SQL: ' || vPLSQL);
74
75 return vPLSQL;
76
77 exception
78 when others then
79 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Failed to construct Scoring engine object filter');
80 return vPLSQL;
81
82 end buildsql;
83
84 -- end for bug 9387044
85
86 function buildUniverse(p_object_id IN NUMBER
87 ,p_query_obj_id in varchar2 --Added for Bug 8933776 16-Dec-2009 barathsr
88 ,p_limit_rows in number default null --Added for Bug 8933776 16-Dec-2009 barathsr
89 ,p_object_type IN VARCHAR2
90 ,p_last_object_scored in out nocopy number
91 ,x_end_of_universe out nocopy boolean) return IEX_FILTER_PUB.UNIVERSE_IDS
92
93 IS
94 l_select_column varchar2(30); -- select column for universeQuery
95 l_entity_name varchar2(30); -- entity for universeQuery
96
97 Type refCur is Ref Cursor;
98 --object_cur refCur;
99 vPLSQL varchar2(2000);
100 Universe_cur refCur;
101 l_universe_ids IEX_FILTER_PUB.UNIVERSE_IDS;
102 i number := 0;
103 l_last_object_scored number;
104 l_api_name varchar2(20);
105
106 -- clchang updated for sql bind var 05/07/2003
107 vstr1 varchar2(100);
108 vstr2 varchar2(100);
109 vstr3 varchar2(100);
110 vstr4 varchar2(100);
111
112
113 begin
114
115 l_api_name := 'buildUniverse';
116 x_end_of_universe := true;
117
118 IEX_DEBUG_PUB.logMessage(G_PKG_NAME || ': ' || l_api_name || ': Start time ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
119 IEX_DEBUG_PUB.logMessage(G_PKG_NAME || ': ' || l_api_name || ': p_object_id = ' || p_object_id);
120 IEX_DEBUG_PUB.logMessage(G_PKG_NAME || ': ' || l_api_name || ': p_object_type = ' || p_object_type);
121 IEX_DEBUG_PUB.logMessage(G_PKG_NAME || ': ' || l_api_name || ': p_last_object_scored = ' || p_last_object_scored);
122
123 --chang updated for sql bind var 05/07/2003
124 vstr1 := 'SELECT ';
125 vstr2 := ' FROM ';
126 vstr3 := ' WHERE ';
127 vstr4 := ' is not null ';
128
129 if p_last_object_scored is null then
130 l_last_object_scored := 0;
131 else
132 l_last_object_scored := p_last_object_scored;
133 end if;
134
135 -- figure out the universe to select from
136 execute immediate
137 'Select select_column, entity_name ' ||
138 ' From IEX_OBJECT_FILTERS ' ||
139 ' Where object_id = :p_object_id AND ' ||
140 ' Object_Filter_Type = :p_object_Type '
141 into l_select_column , l_entity_name
142 using p_object_id, p_object_Type;
143
144
145
146 -- build SQL for universe
147 -- clchang updated for sql bind var 05/07/2003
148
149 vPLSQL:= vstr1 || l_select_column ||
150 vstr2 || l_entity_name ||
151 vstr3 || l_select_column || vstr4 ||
152 ' AND ' || l_select_column || ' > :1 ' ; --l_last_object_scored || --Added bind variable for bug#7166924 by schekuri on 25-Aug-2008
153 --Begin Bug 8933776 16-Dec-2009 barathsr
154 if p_query_obj_id is not null then
155 vPLSQL:= vPLSQL||' AND ' || l_select_column || ' IN ('||p_query_obj_id||')';
156 end if;
157
158 if p_limit_rows is not null then
159 vPLSQL:= vPLSQL|| ' AND ROWNUM <= '|| p_limit_rows;
160 end if;
161 --End Bug 8933776 16-Dec-2009 barathsr
162 vPLSQL:= vPLSQL|| ' ORDER BY ' || l_select_column;
163
164
165
166 IEX_DEBUG_PUB.logMessage(G_PKG_NAME || ': ' || l_api_name || ': vPLSQL is ' || vPLSQL );
167 FND_FILE.PUT_LINE(FND_FILE.LOG,'sql-->'||vPLSQL);
168
169 -- "fill" the universe
170 open universe_cur for
171 vPLSQL using l_last_object_scored; --Added bind variable for bug#7166924 by schekuri on 25-Aug-2008
172 --Begin bug#7166924 by schekuri on 25-Aug-2008
173 fetch universe_cur BULK COLLECT INTO l_universe_ids limit G_Batch_Size;
174
175 if l_universe_ids.count=G_BATCH_SIZE then
176 x_end_of_universe := false;
177 p_last_object_scored := l_universe_ids(l_universe_ids.last);
178 return l_universe_ids;
179 end if;
180 /*LOOP
181 if i > G_Batch_Size then
182 x_end_of_universe := false;
183 IEX_DEBUG_PUB.logMessage(G_PKG_NAME || ': ' || l_api_name || ':Batch size met');
184 IEX_DEBUG_PUB.logMessage(G_PKG_NAME || ': ' || l_api_name || ':last object : ' || p_last_object_scored);
185 return l_universe_ids;
186 end if;
187 i := i + 1;
188 fetch universe_cur into l_universe_ids(i);
189 p_last_object_scored := l_universe_ids(i);
190 exit when universe_cur%NOTFOUND;
191 end loop;*/
192 --End bug#7166924 by schekuri on 25-Aug-2008
193
194 x_end_of_universe := true;
195 IEX_DEBUG_PUB.logMessage(G_PKG_NAME || ': ' || l_api_name || ': Reached the End');
196
197 close universe_cur;
198 return l_universe_ids;
199 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'unv_size-->'||l_universe_ids.count);
200 IEX_DEBUG_PUB.logMessage(G_PKG_NAME || ': ' || l_api_name || ': End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
201
202 exception
203
204 When No_Data_Found then
205 IEX_DEBUG_PUB.logMessage('IEX_FILTER_PUB: buildUniverse: ' || sqlerrm ||' ' || sqlcode);
206 return l_universe_ids;
207 When others Then
208 IEX_DEBUG_PUB.logMessage('IEX_FILTER_PUB: buildUniverse: ' || sqlerrm ||' ' || sqlcode);
209 return l_universe_ids;
210 end buildUniverse;
211
212 Procedure Validate_FILTER(P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
213 P_FILTER_rec IN IEX_FILTER_PUB.FILTER_REC_TYPE,
214 X_Dup_Status OUT NOCOPY VARCHAR2,
215 X_Return_Status OUT NOCOPY VARCHAR2,
216 X_Msg_Count OUT NOCOPY NUMBER,
217 X_Msg_Data OUT NOCOPY VARCHAR2)
218
219 IS
220 l_filter_rec IEX_FILTER_PUB.FILTER_REC_TYPE;
221 l_table_name varchar2(50);
222 l_col_name varchar2(25);
223 l_return_Status varchar2(1);
224 L_API_NAME varchar2(25);
225 BEGIN
226 -- Initialize message list IF p_init_msg_list is set to TRUE.
227 L_API_NAME := 'Validate_Filter';
228 l_filter_rec := p_filter_rec;
229 IF FND_API.to_Boolean( p_init_msg_list )
230 THEN
231 FND_MSG_PUB.initialize;
232 END IF;
233
234 -- Initialize API return status to SUCCESS
235 x_return_status := FND_API.G_RET_STS_SUCCESS;
236
237 iex_dunning_pvt.WriteLog('iexpfilb:Validate_Filter');
238
239 -- api body
240 -- validate object_filter_id
241 /*
242 IEX_UTILITIES.VALIDATE_ANY_ID(P_COL_ID => p_filter_rec.object_filter_id,
243 P_COL_NAME => 'OBJECT_FILTER_ID',
244 P_TABLE_NAME => 'IEX_OBJECT_FILTERS',
245 X_Return_Status => x_return_status,
246 X_Msg_Count => x_msg_count,
247 X_Msg_Data => x_msg_data,
248 p_init_msg_list => fnd_api.g_false);
249
250 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
251 RAISE FND_API.G_EXC_ERROR;
252 END IF;
253 */
254
255 iex_dunning_pvt.WriteLog('iexpfilb:Validate_Filter:Validate_Any_VARCHAR');
256 -- validate object_filter_name
257 IEX_UTILITIES.VALIDATE_ANY_VARCHAR(P_COL_VALUE => p_filter_rec.object_filter_name,
258 P_COL_NAME => 'OBJECT_FILTER_NAME',
259 P_TABLE_NAME => 'IEX_OBJECT_FILTERS',
260 X_Return_Status => l_return_status,
261 X_Msg_Count => x_msg_count,
262 X_Msg_Data => x_msg_data,
263 p_init_msg_list => fnd_api.g_false);
264 --dbms_output.put_line('Validating Obj Filter Name ' || l_return_status);
265 iex_dunning_pvt.WriteLog('iexpfilb:Validate_Filter:Validate_Any_VARCHAR:'||l_return_status);
266
267 -- if found API will return 'S' therefore we have a duplicate Filter_Name
268 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
269 x_dup_status := IEX_SCORE_PVT.IEX_DUPLICATE_NAME;
270 RAISE FND_API.G_EXC_ERROR;
271 --dbms_output.put_line('dup=' || x_dup_status);
272 END IF;
273
274 -- validate lookup_type/ code
275 iex_dunning_pvt.WriteLog('iexpfilb:Validate_Filter:Validate_lookup_Code:');
276 iex_dunning_pvt.WriteLog('iexpfilb:Validate_Filter:lookup_code:'||p_filter_rec.object_filter_type);
277 IEX_UTILITIES.validate_lookup_code(P_LOOKUP_TYPE => 'IEX_OBJECT_FILTERS',
278 P_LOOKUP_CODE => p_filter_rec.OBJECT_FILTER_TYPE,
279 X_Return_Status => l_return_status,
280 X_Msg_Count => x_msg_count,
281 X_Msg_Data => x_msg_data,
282 p_init_msg_list => fnd_api.g_false,
283 p_lookup_view => 'IEX_LOOKUPS_V');
284
285 iex_dunning_pvt.WriteLog('iexpfilb:Validate_Filter:Validate_lookup_Code:'||l_return_status);
286 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
287 RAISE FND_API.G_EXC_ERROR;
288 END IF;
289 --dbms_output.put_line('Validating Obj Filter Type ' || l_return_status);
290
291 -- validate object_id
292 if p_filter_rec.object_filter_type = 'IEXSCORE' then
293 l_table_name := 'IEX_SCORES';
294 l_col_name := 'SCORE_ID';
295 elsif p_filter_rec.object_filter_type = 'IEXSTRAT' then
296 l_table_name := 'IEX_STRATEGY_TEMPLATES_B';
297 l_col_name := 'STRATEGY_TEMP_ID';
298 elsif p_filter_rec.object_filter_type = 'IEXAGING' then
299 l_table_name := 'AR_AGING_BUCKETS';
300 l_col_name := 'AGING_BUCKET_ID';
301 -- added by jypark 02/21/2002 for Customer Status Stratification
302 elsif p_filter_Rec.object_filter_type = 'IEXCUST' then
303 l_table_name := 'IEX_CUST_STATUS_RULES';
304 l_col_name := 'STATUS_RULE_ID';
305 end if;
306
307 iex_dunning_pvt.WriteLog('iexpfilb:Validate_Filter:Validate_any_id:');
308 IEX_UTILITIES.VALIDATE_ANY_ID(P_COL_ID => p_filter_rec.object_id,
309 P_COL_NAME => l_col_name,
310 P_TABLE_NAME => l_table_name,
311 X_Return_Status => l_return_status,
312 X_Msg_Count => x_msg_count,
313 X_Msg_Data => x_msg_data,
314 p_init_msg_list => fnd_api.g_false);
315 --dbms_output.put_line('Validating Object ID ' || l_return_status );
316 iex_dunning_pvt.WriteLog('iexpfilb:Validate_Filter:Validate_any_id:'||l_return_status);
317
318 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
319 RAISE FND_API.G_EXC_ERROR;
320 END IF;
321
322 /*******************************
323 * BUG 4113025
324 * since validate_filter will be called by create_object_filter only,
325 * create_object_filter should be from HTML/ADMIN,
326 * and ADMIN UI will check ENTITY and COLUMN,
327 * we dont validate entity(view) and column here.
328 *
329 -- validate entity_name
330 iex_dunning_pvt.WriteLog('iexpfilb:Validate_Filter:Validate_any_varchar');
331 IEX_UTILITIES.VALIDATE_ANY_VARCHAR(P_COL_VALUE => p_filter_rec.ENTITY_NAME,
332 P_COL_NAME => 'VIEW_NAME',
333 P_TABLE_NAME => 'ALL_VIEWS',
334 X_Return_Status => l_return_status,
335 X_Msg_Count => x_msg_count,
336 X_Msg_Data => x_msg_data,
337 p_init_msg_list => fnd_api.g_false);
338 --dbms_output.put_line('Validating Entity Name ' || l_return_status );
339
340 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
341 RAISE FND_API.G_EXC_ERROR;
342 END IF;
343
344 -- validate select_column
345 iex_dunning_pvt.WriteLog('iexpfilb:Validate_Filter:Validate_any_varchar');
346 IEX_UTILITIES.VALIDATE_ANY_VARCHAR(P_COL_VALUE => p_filter_rec.SELECT_COLUMN,
347 P_COL_NAME => 'COLUMN_NAME',
348 P_TABLE_NAME => 'ALL_TAB_COLUMNS',
349 X_Return_Status => l_return_status,
350 X_Msg_Count => x_msg_count,
351 X_Msg_Data => x_msg_data,
352 p_init_msg_list => fnd_api.g_false);
353 --dbms_output.put_line('Validating Select Column ' || l_return_status);
354
355 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
356 RAISE FND_API.G_EXC_ERROR;
357 END IF;
358 ************************************************/
359
360
361 x_return_status := l_return_status;
362 -- Standard call to get message count and IF count is 1, get message info.
363 FND_MSG_PUB.Count_And_Get
364 ( p_count => x_msg_count,
365 p_data => x_msg_data
366 );
367
368 Exception
369 WHEN FND_API.G_EXC_ERROR THEN
370 iex_dunning_pvt.WriteLog('iexpfilb:Validate_Filter:EXC ERROR');
371 x_return_status := FND_API.G_RET_STS_ERROR;
372 iex_dunning_pvt.WriteLog('iexpfilb:Validate_Filter:err='||SQLERRM);
373 FND_MSG_PUB.Count_And_Get
374 ( p_count => x_msg_count,
375 p_data => x_msg_data
376 );
377
378 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
379 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
380 iex_dunning_pvt.WriteLog('iexpfilb:Validate_Filter:UNEXC ERROR');
381 iex_dunning_pvt.WriteLog('iexpfilb:Validate_Filter:err='||SQLERRM);
382 FND_MSG_PUB.Count_And_Get
383 ( p_count => x_msg_count,
384 p_data => x_msg_data
385 );
386
387 WHEN OTHERS THEN
388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
389 iex_dunning_pvt.WriteLog('iexpfilb:Validate_Filter:OTHERS ERROR');
390 iex_dunning_pvt.WriteLog('iexpfilb:Validate_Filter:err='||SQLERRM);
391 FND_MSG_PUB.Count_And_Get
392 ( p_count => x_msg_count,
393 p_data => x_msg_data
394 );
395
396
397 END Validate_FILTER;
398
399
400 Procedure Create_OBJECT_FILTER
401 (p_api_version IN NUMBER := 1.0,
402 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
403 p_commit IN VARCHAR2 := FND_API.G_FALSE,
404 P_FILTER_REC IN IEX_FILTER_PUB.FILTER_REC_TYPE := G_MISS_FILTER_REC,
405 x_dup_status OUT NOCOPY VARCHAR2,
406 x_return_status OUT NOCOPY VARCHAR2,
407 x_msg_count OUT NOCOPY NUMBER,
408 x_msg_data OUT NOCOPY VARCHAR2,
409 X_FILTER_ID OUT NOCOPY NUMBER)
410
411 IS
412 l_api_name CONSTANT VARCHAR2(30) := 'Create_Object_Filter';
413 l_api_version_number CONSTANT NUMBER := 1.0;
414 l_return_status VARCHAR2(1);
415 l_msg_count NUMBER;
416 l_msg_data VARCHAR2(32767);
417 l_rowid Varchar2(50);
418 l_FILTER_REC IEX_FILTER_PUB.FILTER_REC_TYPE ;
419 l_object_filter_id NUMBER ;
420
421
422 BEGIN
423 -- Standard Start of API savepoint
424 SAVEPOINT CREATE_OBJECT_FILTER_PVT;
425
426 l_FILTER_REC := P_FILTER_REC;
427 -- Standard call to check for call compatibility.
428 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
429 p_api_version,
430 l_api_name,
431 G_PKG_NAME)
432 THEN
433 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
434 END IF;
435
436 -- Initialize message list IF p_init_msg_list is set to TRUE.
437 IF FND_API.to_Boolean( p_init_msg_list )
438 THEN
439 FND_MSG_PUB.initialize;
440 END IF;
441
442 -- Debug Message
443 iex_dunning_pvt.WriteLog('iexpfilb:CreateFilter:Start');
444
445
446 -- Initialize API return status to SUCCESS
447 x_return_status := FND_API.G_RET_STS_SUCCESS;
448
449 --
450 -- API body
451 --
452
453 -- Validate Data
454 iex_dunning_pvt.WriteLog('iexpfilb:CreateFilter:Calling Validate_Filter');
455 Validate_FILTER(P_FILTER_rec => l_filter_rec,
456 X_Dup_Status => x_dup_status,
457 X_Return_Status => l_return_status,
458 X_Msg_Count => l_msg_count,
459 X_Msg_Data => l_msg_data);
460
461 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
462 RAISE FND_API.G_EXC_ERROR;
463 END IF;
464
465 iex_dunning_pvt.WriteLog('iexpfilb:CreateFilter:get object_filter_id from seq');
466
467 Select IEX_OBJECT_FILTERS_S.nextval into x_filter_id
468 From Dual;
469 iex_dunning_pvt.WriteLog('iexpfilb:CreateFilter:object_filter_id='||x_filter_id);
470
471 iex_dunning_pvt.WriteLog('iexpfilb:CreateFilter:insert row');
472
473 -- Create Filter
474 IEX_OBJECT_FILTERS_PKG.insert_row(
475 x_rowid => l_rowid
476 ,p_object_filter_id => x_filter_id
477 ,p_object_filter_type => l_FILTER_REC.object_filter_type
478 ,p_object_filter_name => l_FILTER_REC.object_filter_name
479 ,p_object_id => l_FILTER_REC.object_id
480 ,p_select_column => l_FILTER_REC.select_column
481 ,p_entity_name => l_FILTER_REC.entity_name
482 ,p_active_flag => l_filter_rec.active_flag
483 ,p_object_version_number => 1
484 ,P_CREATED_BY => FND_GLOBAL.USER_ID
485 ,P_CREATION_DATE => sysdate
486 ,P_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
487 ,P_LAST_UPDATE_DATE => sysdate
488 ,P_LAST_UPDATE_LOGIN => FND_GLOBAL.USER_ID);
489
490 --
491 -- End of API body
492 --
493
494 -- Standard check for p_commit
495 IF FND_API.to_Boolean( p_commit )
496 THEN
497 COMMIT WORK;
498 END IF;
499
500 -- Debug Message
501 iex_dunning_pvt.WriteLog('iexpfilb:CreateFilter:end');
502
503 -- Standard call to get message count and IF count is 1, get message info.
504 FND_MSG_PUB.Count_And_Get
505 ( p_count => x_msg_count,
506 p_data => x_msg_data
507 );
508
509
510 EXCEPTION
511 WHEN FND_API.G_EXC_ERROR THEN
512 ROLLBACK TO CREATE_OBJECT_FILTER_PVT;
513 x_return_status := FND_API.G_RET_STS_ERROR;
514 iex_dunning_pvt.WriteLog('iexpfilb:CreatedFilter:EXC ERROR');
515 iex_dunning_pvt.WriteLog('iexpfilb:CreatedFilter:err='||SQLERRM);
516 FND_MSG_PUB.Count_And_Get
517 ( p_count => x_msg_count,
518 p_data => x_msg_data
519 );
520
521 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
522 ROLLBACK TO CREATE_OBJECT_FILTER_PVT;
523 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
524 iex_dunning_pvt.WriteLog('iexpfilb:CreatedFilter:UNEXC ERROR');
525 iex_dunning_pvt.WriteLog('iexpfilb:CreatedFilter:err='||SQLERRM);
526 FND_MSG_PUB.Count_And_Get
527 ( p_count => x_msg_count,
528 p_data => x_msg_data
529 );
530
531 WHEN OTHERS THEN
532 ROLLBACK TO CREATE_OBJECT_FILTER_PVT;
533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
534 iex_dunning_pvt.WriteLog('iexpfilb:CreatedFilter:OTHER ERROR');
535 iex_dunning_pvt.WriteLog('iexpfilb:CreatedFilter:err='||SQLERRM);
536 FND_MSG_PUB.Count_And_Get
537 ( p_count => x_msg_count,
538 p_data => x_msg_data
539 );
540
541 END CREATE_OBJECT_FILTER;
542
543
544 Procedure Update_OBJECT_FILTER
545 (p_api_version IN NUMBER := 1.0,
546 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
547 p_commit IN VARCHAR2 := FND_API.G_FALSE,
548 P_FILTER_REC IN IEX_FILTER_PUB.FILTER_REC_TYPE := G_MISS_FILTER_REC,
549 x_dup_status OUT NOCOPY VARCHAR2,
550 x_return_status OUT NOCOPY VARCHAR2,
551 x_msg_count OUT NOCOPY NUMBER,
552 x_msg_data OUT NOCOPY VARCHAR2)
553 IS
554 l_api_name CONSTANT VARCHAR2(30) := 'UPdate_Object_Filter';
555 l_api_version_number CONSTANT NUMBER := 1.0;
556 l_return_status VARCHAR2(1);
557 l_msg_count NUMBER;
558 l_msg_data VARCHAR2(32767);
559 l_rowid Varchar2(50);
560 l_FILTER_REC IEX_FILTER_PUB.FILTER_REC_TYPE ;
561 l_filter_ref_rec IEX_FILTER_PUB.FILTER_REC_TYPE;
562 l_name_cnt NUMBER;
563
564
565 CURSOR C_CHK_FILTER_NAME (IN_OBJECT_FILTER_ID NUMBER, IN_NAME VARCHAR) IS
566 SELECT COUNT(OBJECT_FILTER_NAME)
567 FROM IEX_OBJECT_FILTERS
568 WHERE OBJECT_FILTER_ID <> IN_OBJECT_FILTER_ID
569 AND OBJECT_FILTER_NAME = IN_NAME;
570
571 CURSOR C_get_filter_Rec (IN_FILTER_ID NUMBER) is
572 SELECT ROWID
573 ,OBJECT_FILTER_ID
574 ,OBJECT_FILTER_TYPE
575 ,OBJECT_FILTER_NAME
576 ,OBJECT_ID
577 ,SELECT_COLUMN
578 ,ENTITY_NAME
579 ,ACTIVE_FLAG
580 ,OBJECT_VERSION_NUMBER
581 ,CREATION_DATE
582 ,CREATED_BY
583 ,LAST_UPDATE_DATE
584 ,LAST_UPDATED_BY
585 ,LAST_UPDATE_LOGIN
586 from iex_object_filters
587 where object_filter_id = in_filter_id
588 FOR UPDATE NOWAIT;
589
590 BEGIN
591 -- Standard Start of API savepoint
592 SAVEPOINT UPDATE_OBJECT_FILTER_PVT;
593
594 l_FILTER_REC := P_FILTER_REC;
595 -- Standard call to check for call compatibility.
596 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
597 p_api_version,
598 l_api_name,
599 G_PKG_NAME)
600 THEN
601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
602 END IF;
603
604 -- Initialize message list IF p_init_msg_list is set to TRUE.
605 IF FND_API.to_Boolean( p_init_msg_list )
606 THEN
607 FND_MSG_PUB.initialize;
608 END IF;
609
610 -- Debug Message
611 iex_dunning_pvt.WriteLog('iexpfilb:UpdateFilter:start');
612
613 -- Initialize API return status to SUCCESS
614 x_return_status := FND_API.G_RET_STS_SUCCESS;
615
616 -- added by clchang 02/25/2002
617 -- validate dup filter_Name
618 iex_dunning_pvt.WriteLog('iexpfilb:UpdateFilter:validation:chk_dup_filter_name');
619 Open C_chk_filter_name(l_FILTER_REC.OBJECT_FILTER_ID,
620 l_FILTER_REC.OBJECT_FILTER_NAME);
621 Fetch C_chk_filter_name into l_NAME_CNT;
622 if (l_name_cnt > 0) then
623 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
624 THEN
625 FND_MESSAGE.Set_Name('IEX', 'IEX_API_DUPLICATE_NAME');
626 FND_MESSAGE.Set_Token ('COLUMN', 'OBJECT_FILTER_NAME', FALSE);
627 FND_MESSAGE.Set_Token ('VALUE', l_Filter_rec.object_filter_name, FALSE);
628 FND_MSG_PUB.Add;
629 END IF;
630 x_dup_status := IEX_SCORE_PVT.IEX_DUPLICATE_NAME;
631 iex_dunning_pvt.WriteLog('iexpfilb:UpdateFilter:dup object_filter_name');
632 RAISE FND_API.G_EXC_ERROR;
633 end if;
634 close C_chk_filter_name;
635
636 --
637 -- API body
638 --
639 Open C_get_filter_Rec(l_FILTER_REC.OBJECT_FILTER_ID);
640 Fetch C_get_filter_Rec into
641 l_rowid
642 ,l_filter_ref_rec.OBJECT_FILTER_ID
643 ,l_filter_ref_rec.OBJECT_FILTER_TYPE
644 ,l_filter_ref_rec.OBJECT_FILTER_NAME
645 ,l_filter_ref_rec.OBJECT_ID
646 ,l_filter_ref_rec.SELECT_COLUMN
647 ,l_filter_ref_rec.ENTITY_NAME
648 ,l_filter_ref_rec.ACTIVE_FLAG
649 ,l_filter_ref_rec.OBJECT_VERSION_NUMBER
650 ,l_filter_ref_rec.CREATION_DATE
651 ,l_filter_ref_rec.CREATED_BY
652 ,l_filter_ref_rec.LAST_UPDATE_DATE
653 ,l_filter_ref_rec.LAST_UPDATED_BY
654 ,l_filter_ref_rec.LAST_UPDATE_LOGIN;
655
656 IF (C_get_filter_Rec%NOTFOUND) THEN
657 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
658 THEN
659 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
660 FND_MESSAGE.Set_Token ('INFO', 'iex_object_filters', FALSE);
661 FND_MSG_PUB.Add;
662 END IF;
663 RAISE FND_API.G_EXC_ERROR;
664 END IF;
665
666 close C_get_filter_Rec;
667
668
669 IF (l_filter_ref_rec.last_update_date is NULL or
670 l_filter_ref_rec.last_update_date = FND_API.G_MISS_Date)
671 THEN
672 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
673 THEN
674 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_ID');
675 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
676 FND_MSG_PUB.ADD;
677 END IF;
678 RAISE FND_API.G_EXC_ERROR;
679 End IF;
680
681 -- Transfer Data into target record
682 l_filter_rec.CREATION_DATE := l_filter_ref_rec.CREATION_DATE;
683 l_filter_rec.CREATED_BY := l_filter_ref_rec.CREATED_BY;
684
685 IF ((l_filter_rec.OBJECT_FILTER_NAME = FND_API.G_MISS_CHAR) OR
686 (l_filter_rec.OBJECT_FILTER_NAME = NULL)) THEN
687 l_filter_rec.OBJECT_FILTER_NAME := l_filter_ref_rec.OBJECT_FILTER_NAME;
688 END IF;
689 IF ((l_filter_rec.OBJECT_FILTER_TYPE = FND_API.G_MISS_CHAR) OR
690 (l_filter_rec.OBJECT_FILTER_TYPE = NULL)) THEN
691 l_filter_rec.OBJECT_FILTER_TYPE := l_filter_ref_rec.OBJECT_FILTER_TYPE;
692 END IF;
693 IF ((l_filter_rec.OBJECT_ID = FND_API.G_MISS_NUM) OR
694 (l_filter_rec.OBJECT_ID = NULL)) THEN
695 l_filter_rec.OBJECT_ID := l_filter_ref_rec.OBJECT_ID;
696 END IF;
697 IF ((l_filter_rec.SELECT_COLUMN = FND_API.G_MISS_CHAR) OR
698 (l_filter_rec.SELECT_COLUMN = NULL)) THEN
699 l_filter_rec.SELECT_COLUMN := l_filter_ref_rec.SELECT_COLUMN;
700 END IF;
701 IF ((l_filter_rec.ENTITY_NAME = FND_API.G_MISS_CHAR) OR
702 (l_filter_rec.ENTITY_NAME = NULL)) THEN
703 l_filter_rec.ENTITY_NAME := l_filter_ref_rec.ENTITY_NAME;
704 END IF;
705 IF ((l_filter_rec.ACTIVE_FLAG = FND_API.G_MISS_CHAR) OR
706 (l_filter_rec.ACTIVE_FLAG = NULL)) THEN
707 l_filter_rec.ACTIVE_FLAG := l_filter_ref_rec.ACTIVE_FLAG;
708 END IF;
709 IF ((l_filter_rec.OBJECT_VERSION_NUMBER = FND_API.G_MISS_NUM) OR
710 (l_filter_rec.OBJECT_VERSION_NUMBER = NULL)) THEN
711 l_filter_rec.OBJECT_VERSION_NUMBER := l_filter_ref_rec.OBJECT_VERSION_NUMBER;
712 END IF;
713
714 iex_dunning_pvt.WriteLog('iexpfilb:UpdateFilter:update row');
715 IEX_OBJECT_FILTERS_PKG.update_row(
716 x_rowid => l_rowid
717 ,p_object_filter_id => l_filter_rec.object_filter_id
718 ,p_object_filter_type => l_FILTER_REC.object_filter_type
719 ,p_object_filter_name => l_FILTER_REC.object_filter_name
720 ,p_object_id => l_FILTER_REC.object_id
721 ,p_select_column => l_FILTER_REC.select_column
722 ,p_entity_name => l_FILTER_REC.entity_name
723 ,p_active_flag => l_filter_rec.active_flag
724 ,p_object_version_number => 1
725 ,P_CREATED_BY => FND_GLOBAL.USER_ID
726 ,P_CREATION_DATE => sysdate
727 ,P_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
728 ,P_LAST_UPDATE_DATE => sysdate
729 ,P_LAST_UPDATE_LOGIN => FND_GLOBAL.USER_ID);
730
731 --
732 -- End of API body
733 --
734
735 -- Standard check for p_commit
736 IF FND_API.to_Boolean( p_commit )
737 THEN
738 COMMIT WORK;
739 END IF;
740
741 -- Debug Message
742 iex_dunning_pvt.WriteLog('iexpfilb:UpdateFilter:end');
743
744 -- Standard call to get message count and IF count is 1, get message info.
745 FND_MSG_PUB.Count_And_Get
746 ( p_count => x_msg_count,
747 p_data => x_msg_data
748 );
749
750
751 EXCEPTION
752 WHEN FND_API.G_EXC_ERROR THEN
753 ROLLBACK TO UPDATE_OBJECT_FILTER_PVT;
754 x_return_status := FND_API.G_RET_STS_ERROR;
755 iex_dunning_pvt.WriteLog('iexpfilb:UpdateFilter:EXC ERROR');
756 iex_dunning_pvt.WriteLog('iexpfilb:UpdateFilter:err='||SQLERRM);
757 FND_MSG_PUB.Count_And_Get
758 ( p_count => x_msg_count,
759 p_data => x_msg_data
760 );
761
762 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
763 ROLLBACK TO UPDATE_OBJECT_FILTER_PVT;
764 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
765 iex_dunning_pvt.WriteLog('iexpfilb:UpdateFilter:UNEXC ERROR');
766 iex_dunning_pvt.WriteLog('iexpfilb:UpdateFilter:err='||SQLERRM);
767 FND_MSG_PUB.Count_And_Get
768 ( p_count => x_msg_count,
769 p_data => x_msg_data
770 );
771
772 WHEN OTHERS THEN
773 ROLLBACK TO UPDATE_OBJECT_FILTER_PVT;
774 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
775 iex_dunning_pvt.WriteLog('iexpfilb:UpdateFilter:OTHER ERROR');
776 iex_dunning_pvt.WriteLog('iexpfilb:UpdateFilter:err='||SQLERRM);
777 FND_MSG_PUB.Count_And_Get
778 ( p_count => x_msg_count,
779 p_data => x_msg_data
780 );
781
782
783 END UPDATE_OBJECT_FILTER;
784
785
786 Procedure Delete_OBJECT_FILTER
787 (p_api_version IN NUMBER := 1.0,
788 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
789 p_commit IN VARCHAR2 := FND_API.G_FALSE,
790 P_OBJECT_FILTER_ID IN NUMBER,
791 x_return_status OUT NOCOPY VARCHAR2,
792 x_msg_count OUT NOCOPY NUMBER,
793 x_msg_data OUT NOCOPY VARCHAR2)
794 IS
795 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Object_Filter';
796 l_api_version_number CONSTANT NUMBER := 1.0;
797 l_return_status VARCHAR2(1);
798 l_msg_count NUMBER;
799 l_msg_data VARCHAR2(32767);
800 l_rowid Varchar2(50);
801
802 CURSOR C_GET_FILTER (IN_FILTER_ID NUMBER) IS
803 SELECT rowid
804 FROM IEX_OBJECT_FILTERS
805 WHERE OBJECT_FILTER_ID = IN_FILTER_ID;
806
807 BEGIN
808 -- Standard Start of API savepoint
809 SAVEPOINT DELETE_OBJECT_FILTER_PVT;
810
811 -- Standard call to check for call compatibility.
812 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
813 p_api_version,
814 l_api_name,
815 G_PKG_NAME)
816 THEN
817 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
818 END IF;
819
820 -- Initialize message list IF p_init_msg_list is set to TRUE.
821 IF FND_API.to_Boolean( p_init_msg_list )
822 THEN
823 FND_MSG_PUB.initialize;
824 END IF;
825
826 -- Debug Message
827 iex_dunning_pvt.WriteLog('iexpfilb:DeleteFilter:Start');
828
829
830 -- Initialize API return status to SUCCESS
831 x_return_status := FND_API.G_RET_STS_SUCCESS;
832
833 --
834 -- API body
835 --
836 Open C_GET_FILTER(p_object_filter_id);
837 Fetch C_GET_FILTER into
838 l_rowid;
839
840 IF ( C_GET_FILTER%NOTFOUND) THEN
841 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
842 THEN
843 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
844 FND_MESSAGE.Set_Token ('INFO', 'iex_object_filters', FALSE);
845 FND_MSG_PUB.Add;
846 END IF;
847 RAISE FND_API.G_EXC_ERROR;
848 END IF;
849
850 Close C_GET_FILTER;
851
852 iex_dunning_pvt.WriteLog('iexpfilb:DeleteFilter:Delete Row');
853
854 -- Invoke table handler
855 IEX_object_filters_PKG.Delete_Row(
856 x_rowid => l_rowid);
857
858
859 --
860 -- End of API body
861 --
862
863 -- Standard check for p_commit
864 IF FND_API.to_Boolean( p_commit )
865 THEN
866 COMMIT WORK;
867 END IF;
868
869 -- Debug Message
870 iex_dunning_pvt.WriteLog('iexpfilb:DeleteFilter:End');
871
872 -- Standard call to get message count and IF count is 1, get message info.
873 FND_MSG_PUB.Count_And_Get
874 ( p_count => x_msg_count,
875 p_data => x_msg_data
876 );
877
878
879 EXCEPTION
880 WHEN FND_API.G_EXC_ERROR THEN
881 ROLLBACK TO DELETE_OBJECT_FILTER_PVT;
882 iex_dunning_pvt.WriteLog('iexpfilb:DeleteFilter:EXP ERROR');
883 iex_dunning_pvt.WriteLog('iexpfilb:DeleteFilter:err='||SQLERRM);
884 x_return_status := FND_API.G_RET_STS_ERROR;
885 FND_MSG_PUB.Count_And_Get
886 ( p_count => x_msg_count,
887 p_data => x_msg_data
888 );
889
890 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
891 ROLLBACK TO DELETE_OBJECT_FILTER_PVT;
892 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
893 iex_dunning_pvt.WriteLog('iexpfilb:DeleteFilter:UNEXP ERROR');
894 iex_dunning_pvt.WriteLog('iexpfilb:DeleteFilter:err='||SQLERRM);
895 FND_MSG_PUB.Count_And_Get
896 ( p_count => x_msg_count,
897 p_data => x_msg_data
898 );
899
900 WHEN OTHERS THEN
901 ROLLBACK TO DELETE_OBJECT_FILTER_PVT;
902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
903 iex_dunning_pvt.WriteLog('iexpfilb:DeleteFilter:OTHER ERROR');
904 iex_dunning_pvt.WriteLog('iexpfilb:DeleteFilter:err='||SQLERRM);
905 FND_MSG_PUB.Count_And_Get
906 ( p_count => x_msg_count,
907 p_data => x_msg_data
908 );
909
910
911 END DELETE_OBJECT_FILTER;
912
913 BEGIN
914
915 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
916
917 END IEX_FILTER_PUB;