[Home] [Help]
PACKAGE BODY: APPS.IEC_RECORD_FILTER_PVT
Source
1 PACKAGE BODY IEC_RECORD_FILTER_PVT AS
2 /* $Header: IECRECFB.pls 115.10 2004/09/03 16:34:19 alromero noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'IEC_RECORD_FILTER_PVT';
5
6 g_error_msg VARCHAR2(2048);
7 g_source_id NUMBER(15);
8
9 PROCEDURE Log ( p_activity_desc IN VARCHAR2
10 , p_method_name IN VARCHAR2
11 , p_sub_method IN VARCHAR2
12 , p_sql_code IN NUMBER
13 , p_sql_errm IN VARCHAR2)
14 IS
15 l_error_msg VARCHAR2(2048);
16 BEGIN
17
18 IEC_OCS_LOG_PVT.LOG_INTERNAL_PLSQL_ERROR
19 ( 'IEC_RECORD_FILTER_PVT'
20 , p_method_name
21 , p_sub_method
22 , p_activity_desc
23 , p_sql_code
24 , p_sql_errm
25 , l_error_msg
26 );
27
28 IF g_error_msg IS NULL THEN
29
30 g_error_msg := l_error_msg;
31
32 END IF;
33
34 END Log;
35
36 FUNCTION Get_AppsSchemaName
37 RETURN VARCHAR2
38 IS
39 l_schema_name VARCHAR2(30);
40 BEGIN
41
42 SELECT ORACLE_USERNAME
43 INTO l_schema_name
44 FROM FND_ORACLE_USERID
45 WHERE READ_ONLY_FLAG = 'U';
46
47 RETURN l_schema_name;
48
49 EXCEPTION
50 WHEN OTHERS THEN
51 Log( NULL
52 , 'Get_AppsSchemaName'
53 , 'MAIN'
54 , SQLCODE
55 , SQLERRM);
56 RAISE fnd_api.g_exc_unexpected_error;
57
58 END Get_AppsSchemaName;
59
60 -----------------------------++++++-------------------------------
61 --
62 -- Start of comments
63 --
64 -- API name : Make_ListEntriesAvailable
65 -- Type : Private
66 -- Pre-reqs : None
67 -- Function : Makes list entries with specified do not use reason available by setting
68 -- the DO_NOT_USE_FLAG to 'N' in IEC_G_RETURN_ENTRIES. Report counts
69 -- are updated to reflect that these entries are now available.
70 --
71 -- Parameters : p_list_header_id IN NUMBER Required
72 -- p_dnu_reason_code IN NUMBER Required
73 -- p_commit IN VARCHAR2 Required
74 -- x_return_status OUT VARCHAR2 Required
75 --
76 -- Version : Initial version 1.0
77 --
78 -- End of comments
79 --
80 -----------------------------++++++-------------------------------
81 PROCEDURE Make_ListEntriesAvailable
82 ( p_list_header_id IN NUMBER
83 , p_dnu_reason_code IN NUMBER
84 , p_commit IN BOOLEAN
85 , x_return_status OUT NOCOPY VARCHAR2)
86 IS
87
88 l_returns_ids system.number_tbl_type := system.number_tbl_type();
89 l_itm_cc_tz_ids system.number_tbl_type := system.number_tbl_type();
90
91 BEGIN
92
93 SAVEPOINT SP1;
94
95 x_return_status := 'S';
96
97 IF p_dnu_reason_code IS NOT NULL THEN
98
99 SELECT RETURNS_ID, ITM_CC_TZ_ID
100 BULK COLLECT INTO l_returns_ids, l_itm_cc_tz_ids
101 FROM IEC_G_RETURN_ENTRIES
102 WHERE LIST_HEADER_ID = P_LIST_HEADER_ID AND DO_NOT_USE_FLAG = 'Y' AND DO_NOT_USE_REASON = P_DNU_REASON_CODE;
103
104 ELSE
105
106 SELECT RETURNS_ID, ITM_CC_TZ_ID
107 BULK COLLECT INTO l_returns_ids, l_itm_cc_tz_ids
108 FROM IEC_G_RETURN_ENTRIES
109 WHERE LIST_HEADER_ID = P_LIST_HEADER_ID AND DO_NOT_USE_FLAG = 'Y';
110
111 END IF;
112
113 IF l_returns_ids IS NOT NULL AND l_returns_ids.COUNT > 0 THEN
114
115 FORALL I IN l_returns_ids.FIRST..l_returns_ids.LAST
116 UPDATE IEC_G_RETURN_ENTRIES
117 SET DO_NOT_USE_FLAG = 'N'
118 , DO_NOT_USE_REASON = NULL
119 WHERE RETURNS_ID = l_returns_ids(I);
120
121 FORALL I IN l_itm_cc_tz_ids.FIRST..l_itm_cc_tz_ids.LAST
122 UPDATE IEC_G_MKTG_ITEM_CC_TZS
123 SET RECORD_COUNT = NVL(RECORD_COUNT, 0) + 1
124 WHERE ITM_CC_TZ_ID = l_itm_cc_tz_ids(I);
125
126 END IF;
127
128 IF p_commit THEN
129 COMMIT;
130 END IF;
131
132 EXCEPTION
133 WHEN OTHERS THEN
134 ROLLBACK TO SP1;
135 x_return_status := 'E';
136
137 END Make_ListEntriesAvailable;
138
139 -----------------------------++++++-------------------------------
140 --
141 -- Start of comments
142 --
143 -- API name : Verify_RecordFilterView
144 -- Type : Private
145 -- Pre-reqs : None
146 -- Function :
147 --
148 -- Parameters : P_SUBSET_ID IN NUMBER Required
149 -- X_RETURN_CODE OUT VARCHAR2 Required
150 --
151 -- Version : Initial version 1.0
152 --
153 -- End of comments
154 --
155 -----------------------------++++++-------------------------------
156 PROCEDURE Verify_RecordFilterView
157 ( p_record_filter_id IN NUMBER
158 , x_view_name OUT NOCOPY VARCHAR2
159 , x_view_exists OUT NOCOPY VARCHAR2
160 , x_return_code OUT NOCOPY VARCHAR2
161 )
162 IS
163 l_status VARCHAR2(10);
164 l_view_name VARCHAR2(30);
165 l_view_owner VARCHAR2(30);
166 l_return_code VARCHAR2(1);
167
168 BEGIN
169
170 X_RETURN_CODE := FND_API.G_RET_STS_SUCCESS;
171 X_VIEW_EXISTS := 'N';
172
173 L_VIEW_NAME := 'IEC_REC_FILTER_' || p_record_filter_id || '_V';
174 l_view_owner := Get_AppsSchemaName;
175
176 BEGIN
177 EXECUTE IMMEDIATE ' SELECT STATUS ' ||
178 ' FROM ALL_OBJECTS ' ||
179 ' WHERE OWNER = :owner ' ||
180 ' AND OBJECT_NAME = :b1 ' ||
181 ' AND OBJECT_TYPE = ''VIEW'' '
182 INTO l_status
183 USING l_view_owner
184 , l_view_name;
185
186 IF (l_status <> 'VALID')
187 THEN
188 -- TODO: Need to log that this record filter has invalid criteria.
189 -- Might want to attempt to rebuild the view to make
190 -- sure the lates changes have been built into the view
191 -- prior to making this assertion.
192 x_return_code := FND_API.G_RET_STS_ERROR;
193 ELSE
194 x_view_exists := 'Y';
195 END IF;
196
197 x_view_name := l_view_name;
198
199 EXCEPTION
200 WHEN NO_DATA_FOUND THEN
201 x_view_exists := 'N';
202 x_view_name := l_view_name;
203 WHEN OTHERS THEN
204 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
205 END;
206
207 EXCEPTION
208 WHEN FND_API.G_EXC_ERROR THEN
209 X_RETURN_CODE := 'E';
210 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
211 X_RETURN_CODE := 'U';
212 WHEN OTHERS THEN
213 X_RETURN_CODE := 'U';
214
215 END Verify_RecordFilterView;
216
217 -----------------------------++++++-------------------------------
218 --
219 -- Start of comments
220 --
221 -- API name : Create_RecordFilterView
222 -- Type : Private
223 -- Pre-reqs : None
224 -- Function :
225 -- Parameters : P_LIST_ID IN NUMBER Required
226 -- X_RETURN_CODE OUT VARCHAR2 Required
227 --
228 -- Version : Initial version 1.0
229 --
230 -- End of comments
231 --
232 -----------------------------++++++-------------------------------
233 PROCEDURE Create_RecordFilterView
234 ( p_record_filter_id IN NUMBER
235 , p_view_name IN VARCHAR2
236 , p_source_type_view_name IN VARCHAR2
237 , x_return_code OUT NOCOPY VARCHAR2
238 )
239 IS
240 PRAGMA AUTONOMOUS_TRANSACTION;
241
242 ----------------------------------------------------------------
243 -- A table of VARCHAR2(256) that is used to build the record
244 -- filter query.
245 ----------------------------------------------------------------
246 l_create_statement DBMS_SQL.VARCHAR2S;
247
248 ----------------------------------------------------------------
249 -- The identifier for the DBMS_SQL cursor that we are going to
250 -- use.
251 ----------------------------------------------------------------
252 l_work_cursor NUMBER;
253
254 ----------------------------------------------------------------
255 -- Dummy number variable used in the execute function.
256 ----------------------------------------------------------------
257 l_dummy NUMBER;
258
259 ----------------------------------------------------------------
260 -- The first part of the record filter query SQL that is unique to
261 -- each list by source view id and list id:
262 ----------------------------------------------------------------
263 l_create_start_str CONSTANT VARCHAR2(16) := 'CREATE VIEW ';
264
265 ----------------------------------------------------------------
266 -- The first part of the record filter query SQL that is unique to
267 -- each list by source view id and list id:
268 ----------------------------------------------------------------
269 l_create_as_str CONSTANT VARCHAR2(100) := ' AS SELECT LIST_HEADER_ID, LIST_ENTRY_ID FROM ';
270
271 ----------------------------------------------------------------
272 -- The first part of the record filter query SQL that is unique to
273 -- each list by source view id and list id:
274 ----------------------------------------------------------------
275 l_create_where_str CONSTANT VARCHAR2(32) := ' WHERE ';
276
277 ----------------------------------------------------------------
278 -- Local Status code.
279 ----------------------------------------------------------------
280 l_status_code VARCHAR2(1);
281
282 BEGIN
283
284 X_RETURN_CODE := FND_API.G_RET_STS_SUCCESS;
285
286 l_create_statement(1) := l_create_start_str
287 || p_view_name
288 || l_create_as_str
289 || p_source_type_view_name
290 || l_create_where_str;
291
292 IEC_CRITERIA_UTIL_PVT.Append_RecFilterCriteriaClause
293 ( NULL
294 , p_record_filter_id
295 , p_source_type_view_name
296 , l_create_statement
297 , l_status_code);
298
299 IF l_status_code = FND_API.G_RET_STS_SUCCESS THEN
300 l_work_cursor := DBMS_SQL.OPEN_CURSOR;
301
302 DBMS_SQL.PARSE( c => l_work_cursor
303 , statement => l_create_statement
304 , lb => 1
305 , ub => l_create_statement.COUNT
306 , lfflg => FALSE
307 , language_flag => DBMS_SQL.NATIVE);
308
309
310 l_dummy := DBMS_SQL.EXECUTE(l_work_cursor);
311
312 DBMS_SQL.CLOSE_CURSOR(l_work_cursor);
313
314 ELSIF l_status_code = 'N' THEN
315 Log( 'View creation for record filter ' || p_record_filter_id
316 , 'Create_RecordFilterView'
317 , 'CREATE_VIEW_DBMS_SQL'
318 , NULL
319 , 'Error building record filter criteria WHERE clause.'
320 );
321
322 X_RETURN_CODE := l_status_code;
323 ELSE
324
325 X_RETURN_CODE := l_status_code;
326 END IF;
327
328 COMMIT;
329
330 EXCEPTION
331 WHEN FND_API.G_EXC_ERROR THEN
332 IF DBMS_SQL.IS_OPEN(l_work_cursor)
333 THEN
334 DBMS_SQL.CLOSE_CURSOR(l_work_cursor);
335 END IF;
336 ROLLBACK;
337 X_RETURN_CODE := 'E';
338 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
339 IF DBMS_SQL.IS_OPEN(l_work_cursor)
340 THEN
341 DBMS_SQL.CLOSE_CURSOR(l_work_cursor);
342 END IF;
343 ROLLBACK;
344 X_RETURN_CODE := 'U';
345 WHEN OTHERS THEN
346 IF DBMS_SQL.IS_OPEN(l_work_cursor)
347 THEN
348 DBMS_SQL.CLOSE_CURSOR(l_work_cursor);
349 END IF;
350 ROLLBACK;
351 X_RETURN_CODE := 'U';
352
353 END Create_RecordFilterView;
354
355 -----------------------------++++++-------------------------------
356 --
357 -- Start of comments
358 --
359 -- API name : Drop_RecordFilterView
360 -- Type : Private
361 -- Pre-reqs : None
362 -- Function :
363 --
364 -- Parameters : p_record_filter_id IN NUMBER Required
365 -- x_return_code OUT VARCHAR2 Required
366 --
367 -- Version : Initial version 1.0
368 --
369 -- End of comments
370 --
371 -----------------------------++++++-------------------------------
372 PROCEDURE Drop_RecordFilterView
373 ( p_record_filter_id IN NUMBER
374 , x_return_code OUT NOCOPY VARCHAR2
375 )
376 IS
377 PRAGMA AUTONOMOUS_TRANSACTION;
378 l_status_code VARCHAR2(1);
379 l_view_name VARCHAR2(30);
380 l_view_owner VARCHAR2(30);
381 l_ignore NUMBER;
382
383 BEGIN
384
385 x_return_code := FND_API.G_RET_STS_SUCCESS;
386
387 -- DROP VIEW IEC_REC_FILTER_<id>_V
388
389 l_view_name := 'IEC_REC_FILTER_' || p_record_filter_id || '_V';
390 l_view_owner := Get_AppsSchemaName;
391
392 BEGIN
393 SELECT 1
394 INTO l_ignore
395 FROM ALL_VIEWS
396 WHERE VIEW_NAME = UPPER(l_view_name)
397 AND OWNER = UPPER(l_view_owner);
398
399 EXECUTE IMMEDIATE 'DROP VIEW ' || l_view_name;
400
401 EXCEPTION
402 WHEN NO_DATA_FOUND THEN
403 NULL;
404 WHEN OTHERS THEN
405 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
406 END;
407
408 COMMIT;
409 EXCEPTION
410 WHEN FND_API.G_EXC_ERROR THEN
411 ROLLBACK;
412 X_RETURN_CODE := 'E';
413 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
414 ROLLBACK;
415 X_RETURN_CODE := 'U';
416 WHEN OTHERS THEN
417 ROLLBACK;
418 X_RETURN_CODE := 'U';
419
420 END Drop_RecordFilterView;
421
422 -----------------------------++++++-------------------------------
423 --
424 -- Start of comments
425 --
426 -- API name : Get_RecordFilterView
427 -- Type : Public
428 -- Pre-reqs : None
429 -- Function : Returns the record filter view name after verifying that the view
430 -- exists, creating the view if necessary.
431 --
432 -- Parameters : p_record_filter_id IN NUMBER Required
433 -- p_source_type_view_name IN VARCHAR2 Required
434 -- x_return_code OUT VARCHAR2 Required
435 --
436 -- Version : Initial version 1.0
437 --
438 -- End of comments
439 --
440 -----------------------------++++++-------------------------------
441 FUNCTION Get_RecordFilterView
442 ( p_record_filter_id IN NUMBER
443 , p_source_type_view_name IN VARCHAR2
444 , x_return_code OUT NOCOPY VARCHAR2
445 )
446 RETURN VARCHAR2
447 IS
448 l_view_name VARCHAR2(500);
449 l_return_code VARCHAR2(1);
450 l_view_exists VARCHAR2(1);
451
452 BEGIN
453 l_return_code := FND_API.G_RET_STS_SUCCESS;
454 l_view_exists := 'N';
455 ----------------------------------------------------------------
456 -- Initialize the return code.
457 ----------------------------------------------------------------
458 X_RETURN_CODE := FND_API.G_RET_STS_SUCCESS;
459
460 ----------------------------------------------------------------
461 -- Create save point for this procedure.
462 ----------------------------------------------------------------
463 SAVEPOINT GET_RECORD_FILTER_VIEW_SP;
464
465 l_view_name := 'IEC_REC_FILTER_' || p_record_filter_id || '_V';
466
467 Verify_RecordFilterView ( p_record_filter_id => p_record_filter_id
468 , x_view_name => l_view_name
469 , x_view_exists => l_view_exists
470 , x_return_code => l_return_code
471 );
472
473 IF (l_return_code = FND_API.G_RET_STS_SUCCESS AND l_view_exists = 'N')
474 THEN
475 Create_RecordFilterView ( p_record_filter_id => p_record_filter_id
476 , p_view_name => l_view_name
477 , p_source_type_view_name => p_source_type_view_name
478 , x_return_code => l_return_code
479 );
480
481 IF (l_return_code <> FND_API.G_RET_STS_SUCCESS) THEN
482 IF (l_return_code = 'N')
483 THEN
484 l_view_name := NULL;
485 ELSE
486 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
487 END IF;
488 END IF;
489
490 ELSIF (l_return_code <> FND_API.G_RET_STS_SUCCESS) THEN
491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
492 END IF;
493
494 RETURN l_view_name;
495
496 EXCEPTION
497 WHEN OTHERS THEN
498 ROLLBACK TO GET_RECORD_FILTER_VIEW_SP;
499 X_RETURN_CODE := FND_API.G_RET_STS_UNEXP_ERROR;
500
501 END Get_RecordFilterView;
502
503 -----------------------------++++++-------------------------------
504 --
505 -- Start of comments
506 --
507 -- API name : Get_RecordFilterSourceType
508 -- Type : Public
509 -- Pre-reqs : None
510 -- Procedure : Returns the source type view for the record filter.
511 --
512 -- Parameters : p_record_filter_id IN NUMBER Required
513 -- x_source_type_view OUT VARCHAR2 Required
514 --
515 -- Version : Initial version 1.0
516 --
517 -- End of comments
518 --
519 -----------------------------++++++-------------------------------
520 PROCEDURE Get_RecordFilterSourceType
521 ( p_record_filter_id IN NUMBER
522 , x_source_type_view OUT NOCOPY VARCHAR2
523 )
524 IS
525 l_ignore VARCHAR2(500);
526 l_view_owner VARCHAR2(30);
527
528 BEGIN
529
530 -- Get source type of record filter
531 BEGIN
532 EXECUTE IMMEDIATE
533 'SELECT B.TAG
534 FROM IEC_O_RELEASE_CTLS_B A, IEC_LOOKUPS B
535 WHERE A.RELEASE_CONTROL_ID = :record_filter_id
536 AND B.LOOKUP_TYPE = ''IEC_SOURCE_VIEW_MAP''
537 AND A.SOURCE_TYPE_CODE = B.LOOKUP_CODE'
538 INTO x_source_type_view
539 USING p_record_filter_id;
540
541 EXCEPTION
542 WHEN NO_DATA_FOUND THEN
543 RAISE_APPLICATION_ERROR(-20999, 'Source type view not supported by Advanced Outbound.');
544 WHEN OTHERS THEN
545 RAISE_APPLICATION_ERROR(-20999, 'Unexpected error: ' || SQLERRM);
546 END;
547
548 l_view_owner := Get_AppsSchemaName;
549 BEGIN
550 EXECUTE IMMEDIATE
551 'SELECT VIEW_NAME
552 FROM ALL_VIEWS
553 WHERE VIEW_NAME = UPPER(:source_type_view)
554 AND OWNER = UPPER(:owner)'
555 INTO l_ignore
556 USING x_source_type_view
557 , l_view_owner;
558 EXCEPTION
559 WHEN NO_DATA_FOUND THEN
560 RAISE_APPLICATION_ERROR(-20999, 'Source type view ' || x_source_type_view || ' has not been created in Oracle Marketing Online.');
561 END;
562
563 END Get_RecordFilterSourceType;
564
565 -----------------------------++++++-------------------------------
566 --
567 -- Start of comments
568 --
569 -- API name : Recreate_RecordFilterView
570 -- Type : Public
571 -- Pre-reqs : None
572 -- Procedure : Recreates the record filter view, deleting it first if necessary.
573 --
574 -- Parameters : p_record_filter_id IN NUMBER Required
575 -- x_record_filter_view_name OUT VARCHAR2 Required
576 --
577 -- Version : Initial version 1.0
578 --
579 -- End of comments
580 --
581 -----------------------------++++++-------------------------------
582 PROCEDURE Recreate_RecordFilterView
583 ( p_record_filter_id IN NUMBER
584 , x_record_filter_view_name OUT NOCOPY VARCHAR2
585 )
586 IS
587 l_source_type_view_name VARCHAR2(30);
588 l_view_name VARCHAR2(500);
589 l_view_exists VARCHAR2(1);
590 l_return_code VARCHAR2(1);
591
592 BEGIN
593 l_view_exists := 'N';
594 ----------------------------------------------------------------
595 -- Initialize the return code.
596 ----------------------------------------------------------------
597 l_return_code := FND_API.G_RET_STS_SUCCESS;
598
599 x_record_filter_view_name := 'ERROR';
600
601 ----------------------------------------------------------------
602 -- Create save point for this procedure.
603 ----------------------------------------------------------------
604 SAVEPOINT RECREATE_RECORD_FILTER_VIEW_SP;
605
606 ----------------------------------------------------------------
607 -- Retrieve the source type view for the record filter.
608 ----------------------------------------------------------------
609 Get_RecordFilterSourceType ( p_record_filter_id
610 , l_source_type_view_name
611 );
612
613 l_view_name := 'IEC_REC_FILTER_' || p_record_filter_id || '_V';
614
615 ----------------------------------------------------------------
616 -- Check to see if the view already exists.
617 ----------------------------------------------------------------
618 Verify_RecordFilterView ( p_record_filter_id => p_record_filter_id
619 , x_view_name => l_view_name
620 , x_view_exists => l_view_exists
621 , x_return_code => l_return_code
622 );
623
624 ----------------------------------------------------------------
625 -- If the view already exists then drop the view.
626 ----------------------------------------------------------------
627 IF (l_return_code = FND_API.G_RET_STS_SUCCESS AND l_view_exists = 'Y')
628 THEN
629 Drop_RecordFilterView ( p_record_filter_id => p_record_filter_id
630 , x_return_code => l_return_code);
631
632 ELSIF (l_return_code <> FND_API.G_RET_STS_SUCCESS)
633 THEN
634 g_error_msg := SUBSTR( 'Verifying view for record filter: ' || p_record_filter_id
635 || ' SLQCODE: ' || SQLCODE || ':' || SQLERRM
636 , 1
637 , 2048);
638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
639
640 END IF;
641
642 ----------------------------------------------------------------
643 -- If everything was successful up to this point then we
644 -- create the record filter view.
645 ----------------------------------------------------------------
646 IF (l_return_code = FND_API.G_RET_STS_SUCCESS)
647 THEN
648 Create_RecordFilterView ( p_record_filter_id => p_record_filter_id
649 , p_view_name => l_view_name
650 , p_source_type_view_name => l_source_type_view_name
651 , x_return_code => l_return_code
652 );
653
654 IF (l_return_code <> FND_API.G_RET_STS_SUCCESS) THEN
655
656 IF (l_return_code = 'N')
657 THEN
658 g_error_msg := SUBSTR( 'Creating view for record filter: ' || p_record_filter_id
659 || ' SLQCODE: ' || SQLCODE || ':' || SQLERRM
660 , 1
661 , 2048);
662 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
663 END IF;
664 END IF;
665
666 x_record_filter_view_name := l_view_name;
667
668 ELSE
669 g_error_msg := SUBSTR( 'Dropping view for record filter: ' || p_record_filter_id
670 || ' SLQCODE: ' || SQLCODE || ':' || SQLERRM
671 , 1
672 , 2048);
673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
674 END IF;
675
676 EXCEPTION
677 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
678 THEN
679 ROLLBACK TO RECREATE_RECORD_FILTER_VIEW_SP;
680 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
681 WHEN OTHERS THEN
682 g_error_msg := SUBSTR( 'Recreating view for record filter: ' || p_record_filter_id
683 || ' SLQCODE: ' || SQLCODE || ':' || SQLERRM
684 , 1
685 , 2048);
686 ROLLBACK TO RECREATE_RECORD_FILTER_VIEW_SP;
687 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
688
689 END Recreate_RecordFilterView;
690
691 -----------------------------++++++-------------------------------
692 --
693 -- Start of comments
694 --
695 -- API name : Apply_RecordFilter
696 -- Type : Public
697 -- Pre-reqs : None
698 -- Procedure : Applies a specified record filter to an entry
699 -- belonging to specified target group.
700 --
701 -- Parameters : p_list_entry_id IN NUMBER Required
702 -- p_list_id IN NUMBER Required
703 -- p_returns_id IN NUMBER Required
704 -- p_record_filter_id IN NUMBER Required
705 -- p_source_type_view_name IN VARCHAR2 Required
706 -- x_callable_flag OUT VARCHAR2 Required
707 --
708 -- Version : Initial version 1.0
709 --
710 -- End of comments
711 --
712 -----------------------------++++++-------------------------------
713 PROCEDURE Apply_RecordFilter
714 ( p_list_entry_id IN NUMBER
715 , p_list_id IN NUMBER
716 , p_returns_id IN NUMBER
717 , p_record_filter_id IN NUMBER
718 , p_source_type_view_name IN VARCHAR2
719 , x_callable_flag OUT NOCOPY VARCHAR2
720 )
721 AS
722 l_return_code VARCHAR2(1);
723 l_view_name VARCHAR2(32);
724
725 BEGIN
726
727 x_callable_flag := 'Y';
728
729 BEGIN
730 l_view_name := Get_RecordFilterView
731 ( p_record_filter_id
732 , p_source_type_view_name
733 , l_return_code
734 );
735 EXCEPTION
736 -- If we have a problem getting the record filter view, then
737 -- simply assume that record filter is invalid and do not apply
738 WHEN OTHERS THEN
739 l_view_name := NULL;
740 END;
741
742 IF l_view_name IS NOT NULL THEN
743 BEGIN
744 EXECUTE IMMEDIATE
745 'SELECT ''N''
746 FROM ' || l_view_name || '
747 WHERE LIST_HEADER_ID = :list_id
748 AND LIST_ENTRY_ID = :list_entry_id
749 AND ROWNUM <= 1'
750 INTO x_callable_flag
751 USING IN p_list_id
752 , IN p_list_entry_id;
753 EXCEPTION
754 WHEN NO_DATA_FOUND THEN
755 x_callable_flag := 'Y';
756 WHEN OTHERS THEN
757 RAISE;
758 END;
759
760 IF x_callable_flag = 'N' THEN
761 iec_returns_util_pvt.Update_Entry( p_returns_id
762 , -1
763 , to_char(null)
764 , to_char(null)
765 , to_char(null)
766 , 38
767 , 0
768 , 0
769 , 'N');
770 END IF;
771 END IF;
772
773 EXCEPTION
774 WHEN OTHERS THEN
775 RAISE_APPLICATION_ERROR(-20999, 'Error applying record filter ' || l_view_name || ': ' || SQLERRM);
776
777 END Apply_RecordFilter;
778
779 -----------------------------++++++-------------------------------
780 --
781 -- Start of comments
782 --
783 -- API name : Cancel_RecordFilter
784 -- Type : Public
785 -- Pre-reqs : None
786 -- Procedure : Remove record filter from all affected entries.
787 --
788 -- Parameters : p_record_filter_id IN VARCHAR2 Required
789 --
790 -- Version : Initial version 1.0
791 --
792 -- End of comments
793 --
794 -----------------------------++++++-------------------------------
795 PROCEDURE Cancel_RecordFilter(p_record_filter_id IN NUMBER)
796 IS
797 cursor c_list_rlse is
798 select list_header_id from ams_list_headers_all where release_control_alg_id = p_record_filter_id;
799
800 l_return_status VARCHAR2(1);
801
802 BEGIN
803
804 FOR v_list_rlse IN c_list_rlse LOOP
805
806 Make_ListEntriesAvailable
807 ( v_list_rlse.list_header_id
808 , 8
809 , TRUE
810 , l_return_status);
811
812 IF l_return_status <> 'S' THEN
813 RAISE_APPLICATION_ERROR(-20999, 'Error removing record filter ' || p_record_filter_id || ' from target group ' || v_list_rlse.list_header_id);
814 END IF;
815
816 END LOOP;
817
818 END Cancel_RecordFilter;
819
820 -----------------------------++++++-------------------------------
821 --
822 -- Start of comments
823 --
824 -- API name : Cancel_RecordFilterForList
825 -- Type : Public
826 -- Pre-reqs : None
827 -- Procedure : Remove record filter from specified list.
828 --
829 -- Parameters : p_list_header_id IN NUMBER Required
830 --
831 -- Version : Initial version 1.0
832 --
833 -- End of comments
834 --
835 -----------------------------++++++-------------------------------
836 PROCEDURE Cancel_RecordFilterForList(p_list_header_id IN NUMBER)
837 IS
838 l_return_status VARCHAR2(1);
839
840 BEGIN
841
842 Make_ListEntriesAvailable
843 ( p_list_header_id
844 , 8
845 , TRUE
846 , l_return_status);
847
848 IF l_return_status <> 'S' THEN
849 RAISE_APPLICATION_ERROR(-20999, 'Error removing record filter from target group ' || p_list_header_id);
850 END IF;
851
852 END Cancel_RecordFilterForList;
853
854 END IEC_RECORD_FILTER_PVT;