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