[Home] [Help]
PACKAGE BODY: APPS.AMV_SEARCH_PVT
Source
1 PACKAGE BODY amv_search_pvt AS
2 /* $Header: amvvserb.pls 120.2 2005/07/28 11:55:02 appldev ship $ */
3
4 --
5 -- NAME
6 -- AMV_SEARCH_PVT
7 --
8 -- HISTORY
9 -- 10/06/1999 SLKRISHN CREATED
10 -- 06/15/2000 svatsa UPDATED
11 -- Added the procedure Parse_IMT_String to handle the 256 character limitation
12 -- Added a record type and a table type for this purpose: parsed_rec_type and
13 -- parsed_tbl_type
14 -- The following API have been modified due to this change :
15 -- 1. Build_Chan_Name_Sql
16 -- 2. Build_Items_Name_Sql
17 -- 3. Build_Items_File_Sql
18 -- 4. Build_Items_Text_Sql
19 -- 5. Build_Items_URL_Sql
20 --
21 -- 10/23/00 jjwu Removed 'UNION ALL' statements
22
23 -- insert into 'amv_temp_ids' table right after each
24
25 -- substatement construction
26
27 --
28
29 --
30
31 -- 6/07/04 Sharma Fixed bug 2719461
32 -- SQL in procedure Build_Items_File_Sql, Build_Items_Text_Sql
33 -- Build_Items_URL_Sql modified to get correct score
34 --
35 -- 27-Jul-2005 MKETTLE Removed Schema prefix for trunc of AMV_TEMP_NUMBERS
36 --
37
38
39
40
41 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMV_SEARCH_PVT';
42
43 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amvvserb.pls';
44
45 --
46
47 TYPE CursorType IS REF CURSOR;
48
49 G_AMV_SEARCH CONSTANT VARCHAR2(30) := 'AMV_SEARCH';
50
51 G_CONTENT_AREA CONSTANT VARCHAR2(30) := 'CONTENT_AREA';
52
53 G_SEARCH_AREA CONSTANT VARCHAR2(30) := 'SEARCH_AREA';
54
55 G_CONDITION_CONS CONSTANT VARCHAR2(30) := 'CONDITION_CONS';
56
57 G_WORD_CONS CONSTANT VARCHAR2(30) := 'WORD_CONS';
58
59
60
61 G_PUBLIC CONSTANT VARCHAR2(30) := AMV_UTILITY_PVT.G_PUBLIC;
62
63 G_PRIVATE CONSTANT VARCHAR2(30) := AMV_UTILITY_PVT.G_PRIVATE;
64
65 G_GROUP CONSTANT VARCHAR2(30) := AMV_UTILITY_PVT.G_GROUP;
66
67 G_CONTENT CONSTANT VARCHAR2(30) := AMV_UTILITY_PVT.G_CONTENT;
68
69
70
71 G_APPROVED CONSTANT VARCHAR2(30) := AMV_UTILITY_PVT.G_APPROVED;
72
73 G_CHANNEL CONSTANT VARCHAR2(30) := AMV_UTILITY_PVT.G_CHANNEL;
74
75 G_CATEGORY CONSTANT VARCHAR2(30) := AMV_UTILITY_PVT.G_CATEGORY;
76
77 G_ITEM CONSTANT VARCHAR2(30) := AMV_UTILITY_PVT.G_ITEM;
78
79 G_AUTHOR CONSTANT VARCHAR2(30) := 'AUTHOR';
80
81 G_KEYWORD CONSTANT VARCHAR2(30) := 'KEYWORD';
82
83 G_TITLE_DESC CONSTANT VARCHAR2(30) := 'TITLE_DESC';
84
85 G_CAN_CONTAIN CONSTANT VARCHAR2(30) := 'CAN_CONTAIN';
86
87 G_MUST_CONTAIN CONSTANT VARCHAR2(30) := 'MUST_CONTAIN';
88
89 G_MUST_NOT_CONTAIN CONSTANT VARCHAR2(30) := 'MUST_NOT_CONTAIN';
90
91
92
93 -- Record and Table Type for Parse_IMT_String
94
95 TYPE parsed_rec_type IS RECORD
96
97 (
98
99 imt_string VARCHAR2(250)
100
101 );
102
103
104
105 TYPE parsed_tbl_type IS TABLE OF parsed_rec_type INDEX BY BINARY_INTEGER;
106
107
108
109 --------------------------------------------------------------------------------
110
111 FUNCTION Default_AreaArray return AMV_CHAR_VARRAY_TYPE
112
113 IS
114
115 l_array amv_char_varray_type;
116
117 BEGIN
118
119 l_array := amv_char_varray_type();
120
121 l_array.extend;
122
123 l_array(1) := G_ITEM;
124
125
126
127 return l_array;
128
129 END Default_AreaArray;
130
131 --------------------------------------------------------------------------------
132
133 FUNCTION Default_ContentArray return AMV_CHAR_VARRAY_TYPE
134
135 IS
136
137 l_array amv_char_varray_type;
138
139 BEGIN
140
141 l_array := amv_char_varray_type();
142
143 l_array.extend;
144
145 l_array(1) := G_AUTHOR;
146
147 l_array.extend;
148
149 l_array(2) := G_KEYWORD;
150
151 l_array.extend;
152
153 l_array(3) := G_TITLE_DESC;
154
155
156
157 return l_array;
158
159 END Default_ContentArray;
160
161 --------------------------------------------------------------------------------
162
163 --------------------------------------------------------------------------------
164
165 -- Procedure for parsing the IMT String
166
167 PROCEDURE Parse_IMT_String
168
169 (
170
171 p_imt_string IN VARCHAR2
172
173 ,x_parsed_tbl OUT NOCOPY parsed_tbl_type
174
175 );
176
177 --------------------------------------------------------------------------------
178
179 -- build an array of optional, required and excluded parameters
180
181 PROCEDURE parse_parameter_array(
182
183 p_param_array IN AMV_SEARCHPAR_VARRAY_TYPE,
184
185 x_optional_array OUT NOCOPY AMV_CHAR_VARRAY_TYPE,
186
187 x_required_array OUT NOCOPY AMV_CHAR_VARRAY_TYPE,
188
189 x_excluded_array OUT NOCOPY AMV_CHAR_VARRAY_TYPE,
190
191 x_keywords_search OUT NOCOPY VARCHAR2);
192
193 --------------------------------------------------------------------------------
194
195 PROCEDURE get_chan_attr_stmt(
196
197 p_table_name IN VARCHAR2,
198
199 p_where_column IN VARCHAR2,
200
201 p_application_id IN NUMBER,
202
203 p_days IN NUMBER,
204
205 p_include_chns IN VARCHAR2 := FND_API.G_TRUE,
206
207 p_optional_array IN AMV_CHAR_VARRAY_TYPE,
208
209 p_required_array IN AMV_CHAR_VARRAY_TYPE,
210
211 p_excluded_array IN AMV_CHAR_VARRAY_TYPE,
212
213 p_index IN OUT NOCOPY PLS_INTEGER,
214
215 x_sql_statement IN OUT NOCOPY DBMS_SQL.VARCHAR2S);
216
217 --------------------------------------------------------------------------------
218
219 PROCEDURE get_item_attr_stmt(
220
221 p_table_name IN VARCHAR2,
222
223 p_where_column IN VARCHAR2,
224
225 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
226
227 p_days IN NUMBER,
228
229 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
230
231 p_include_chns IN VARCHAR2 := FND_API.G_TRUE,
232
233 p_search_level IN VARCHAR2,
234
235 p_optional_array IN AMV_CHAR_VARRAY_TYPE,
236
237 p_required_array IN AMV_CHAR_VARRAY_TYPE,
238
239 p_excluded_array IN AMV_CHAR_VARRAY_TYPE,
240
241 p_index IN OUT NOCOPY PLS_INTEGER,
242
243 x_sql_statement IN OUT NOCOPY DBMS_SQL.VARCHAR2S);
244
245 --------------------------------------------------------------------------------
246
247 PROCEDURE get_user_accessable_channels(
248
249 p_user_id IN NUMBER,
250
251 p_application_id IN NUMBER,
252
253 x_channel_array OUT NOCOPY AMV_NUMBER_VARRAY_TYPE);
254
255 --------------------------------------------------------------------------------
256
257 PROCEDURE get_app_categories(
258
259 p_application_id IN NUMBER,
260
261 x_category_array OUT NOCOPY AMV_NUMBER_VARRAY_TYPE);
262
263 --------------------------------------------------------------------------------
264
265 PROCEDURE get_category_channel (
266
267 p_category_id IN AMV_NUMBER_VARRAY_TYPE,
268
269 p_application_id IN NUMBER,
270
271 p_include_subcats IN VARCHAR2,
272
273 x_category_array OUT NOCOPY AMV_NUMBER_VARRAY_TYPE,
274
275 x_channel_array OUT NOCOPY AMV_NUMBER_VARRAY_TYPE);
276
277 --------------------------------------------------------------------------------
278
279 PROCEDURE build_chan_name_sql (
280
281 p_index IN OUT NOCOPY PLS_INTEGER,
282
283 p_imt_string IN VARCHAR2,
284
285 p_application_id IN NUMBER,
286
287 p_excluded_flag IN VARCHAR2,
288
289 p_include_chns IN VARCHAR2 := FND_API.G_TRUE,
290
291 p_days IN NUMBER,
292
293 x_sql_statement IN OUT NOCOPY DBMS_SQL.VARCHAR2S
294
295 );
296
297 --------------------------------------------------------------------------------
298
299 PROCEDURE build_items_name_sql (
300
301 p_index IN OUT NOCOPY PLS_INTEGER,
302
303 p_imt_string IN VARCHAR2,
304
305 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
306
307 p_include_chns IN VARCHAR2 := FND_API.G_TRUE,
308
309 p_days IN NUMBER,
310
311 p_search_level IN VARCHAR2,
312
313 p_excluded_flag IN VARCHAR2,
314
315 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
316
317 x_sql_statement IN OUT NOCOPY DBMS_SQL.VARCHAR2S
318
319 );
320
321 --------------------------------------------------------------------------------
322
323 PROCEDURE build_items_file_sql (
324
325 p_index IN OUT NOCOPY PLS_INTEGER,
326
327 p_imt_string IN VARCHAR2,
328
329 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
330
331 p_include_chns IN VARCHAR2 := FND_API.G_TRUE,
332
333 p_days IN NUMBER,
334
335 p_search_level IN VARCHAR2,
336
337 p_excluded_flag IN VARCHAR2,
338
339 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
340
341 x_sql_statement IN OUT NOCOPY DBMS_SQL.VARCHAR2S
342
343 );
344
345 --------------------------------------------------------------------------------
346
347 PROCEDURE build_items_text_sql (
348
349 p_index IN OUT NOCOPY PLS_INTEGER,
350
351 p_imt_string IN VARCHAR2,
352
353 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
354
355 p_include_chns IN VARCHAR2 := FND_API.G_TRUE,
356
357 p_days IN NUMBER,
358
359 p_search_level IN VARCHAR2,
360
361 p_excluded_flag IN VARCHAR2,
362
363 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
364
365 x_sql_statement IN OUT NOCOPY DBMS_SQL.VARCHAR2S
366
367 );
368
369 --------------------------------------------------------------------------------
370
371 PROCEDURE build_items_url_sql (
372
373 p_index IN OUT NOCOPY PLS_INTEGER,
374
375 p_imt_string IN VARCHAR2,
376
377 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
378
379 p_include_chns IN VARCHAR2 := FND_API.G_TRUE,
380
381 p_days IN NUMBER,
382
383 p_search_level IN VARCHAR2,
384
385 p_excluded_flag IN VARCHAR2,
386
387 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
388
389 x_sql_statement IN OUT NOCOPY DBMS_SQL.VARCHAR2S
390
391 );
392
393 --------------------------------------------------------------------------------
394
395 PROCEDURE insert_temp_numbers(p_id_array IN AMV_NUMBER_VARRAY_TYPE,
396
397 x_status OUT NOCOPY VARCHAR2);
398
399 --------------------------------------------------------------------------------
400
401 PROCEDURE insert_temp_ids(p_stmt IN OUT NOCOPY DBMS_SQL.VARCHAR2S,
402
403 p_start_index IN PLS_INTEGER,
404
405 p_end_index IN PLS_INTEGER,
406
407 x_status OUT NOCOPY VARCHAR2);
408
409 --------------------------------------------------------------------------------
410
411 PROCEDURE populate_channel_results (
412
413 p_results_requested IN NUMBER,
414
415 x_start_with IN OUT NOCOPY NUMBER,
416
417 x_results_array IN OUT NOCOPY AMV_SEARCHRES_VARRAY_TYPE,
418
419 x_results_populated IN OUT NOCOPY NUMBER,
420
421 x_total_results IN OUT NOCOPY NUMBER);
422
423 --------------------------------------------------------------------------------
424
425 PROCEDURE populate_item_results (
426
427 p_search_level IN VARCHAR2,
428
429 p_results_requested IN NUMBER,
430
431 x_start_with IN OUT NOCOPY NUMBER,
432
433 x_results_array IN OUT NOCOPY AMV_SEARCHRES_VARRAY_TYPE,
434
435 x_results_populated IN OUT NOCOPY NUMBER,
436
437 x_total_results IN OUT NOCOPY NUMBER);
438
439 --------------------------------------------------------------------------------
440
441 PROCEDURE build_channel_stmt (
442
443 p_content_array IN AMV_CHAR_VARRAY_TYPE,
444
445 p_imt_string IN VARCHAR2,
446
447 p_optional_array IN AMV_CHAR_VARRAY_TYPE,
448
449 p_required_array IN AMV_CHAR_VARRAY_TYPE,
450
451 p_excluded_array IN AMV_CHAR_VARRAY_TYPE,
452
453 p_keywords_search IN VARCHAR2 := FND_API.G_TRUE,
454
455 p_excluded_flag IN VARCHAR2 := FND_API.G_FALSE,
456
457 p_application_id IN NUMBER,
458
459 p_days IN NUMBER,
460
461 p_include_chns IN VARCHAR2 := FND_API.G_TRUE,
462
463 p_search_level IN VARCHAR2 := G_CHANNEL,
464
465 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
466
467 p_index IN OUT NOCOPY PLS_INTEGER,
468
469 x_chan_sql_stmt OUT NOCOPY DBMS_SQL.VARCHAR2S,
470
471 x_chan_sql_status OUT NOCOPY VARCHAR2);
472
473 --------------------------------------------------------------------------------
474
475 PROCEDURE build_item_stmt (
476
477 p_content_array IN AMV_CHAR_VARRAY_TYPE,
478
479 p_optional_array IN AMV_CHAR_VARRAY_TYPE,
480
481 p_required_array IN AMV_CHAR_VARRAY_TYPE,
482
483 p_excluded_array IN AMV_CHAR_VARRAY_TYPE,
484
485 p_keywords_search IN VARCHAR2 := FND_API.G_TRUE,
486
487 p_excluded_flag IN VARCHAR2 := FND_API.G_FALSE,
488
489 p_imt_string IN VARCHAR2,
490
491 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
492
493 p_days IN NUMBER,
494
495 p_include_chns IN VARCHAR2 := FND_API.G_TRUE,
496
497 p_search_level IN VARCHAR2,
498
499 p_external_contents IN VARCHAR2,
500
501 p_index IN OUT NOCOPY PLS_INTEGER,
502
503 x_item_sql_stmt OUT NOCOPY DBMS_SQL.VARCHAR2S,
504
505 x_item_sql_status OUT NOCOPY VARCHAR2);
506
507 --------------------------------------------------------------------------------
508
509 PROCEDURE search_items(
510
511 p_area_array IN AMV_CHAR_VARRAY_TYPE,
512
513 p_content_array IN AMV_CHAR_VARRAY_TYPE,
514
515 p_imt_string IN VARCHAR2,
516
517 p_optional_array IN AMV_CHAR_VARRAY_TYPE,
518
519 p_required_array IN AMV_CHAR_VARRAY_TYPE,
520
521 p_excluded_array IN AMV_CHAR_VARRAY_TYPE,
522
523 p_keywords_search IN VARCHAR2,
524
525 p_excluded_flag IN VARCHAR2,
526
527 p_application_id IN NUMBER,
528
529 p_days IN NUMBER,
530
531 p_include_chns IN VARCHAR2,
532
533 p_search_level IN VARCHAR2,
534
535 p_external_contents IN VARCHAR2,
536
537 p_records_requested IN NUMBER,
538
539 x_start_with IN OUT NOCOPY NUMBER,
540
541 x_results_populated IN OUT NOCOPY NUMBER,
542
543 x_total_count IN OUT NOCOPY NUMBER,
544
545 x_searchres_array IN OUT NOCOPY AMV_SEARCHRES_VARRAY_TYPE);
546
547 --------------------------------------------------------------------------------
548
549 --------------------------------------------------------------------------------
550
551 -- Procedure for parsing the IMT String
552
553 PROCEDURE Parse_IMT_String
554
555 (
556
557 p_imt_string IN VARCHAR2
558
559 ,x_parsed_tbl OUT NOCOPY parsed_tbl_type
560
561 )
562
563 IS
564
565 l_imt_string VARCHAR2(32000) := p_imt_string; -- Initialize with p_imt_string
566
567 l_string_length NUMBER; -- Total string length of the IMT string
568
569 l_search_length NUMBER := 200; -- Search every 200 characters of the IMT string
570
571 l_split_at NUMBER; -- Place in the string at which the split is supposed to occur
572
573 l_counter NUMBER; -- Loop counter
574
575 l_rec_count NUMBER; -- Table record count for the table, x_parsed_tbl
576
577
578
579 BEGIN
580
581
582
583 -- Get the total string length of l_imt_string
584
585 l_string_length := LENGTH(l_imt_string);
586
587 --DBMS_OUTPUT.PUT_LINE('Complete String Length = '||l_string_length );
588
589
590
591 -- Get the number of times the string will be parsed into l_counter
592
593 -- Function CEIL gives the highest integer for the argument passed
594
595 l_counter := CEIL(l_string_length/l_search_length);
596
597
598
599 --DBMS_OUTPUT.PUT_LINE('Loop Counts = '||to_char( l_counter ) );
600
601
602
603 -- Populate the x_parsed_tbl
604
605 -- Initialize the l_rec_count
606
607 l_rec_count := 1;
608
609 FOR i in 1 .. l_counter LOOP
610
611 -- Search the first occurence of the word , (comma) in l_imt_string after every 200(l_search_length) characters
612
613 l_split_at := INSTR(UPPER(l_imt_string),',',l_search_length,1);
614
615
616
617 --DBMS_OUTPUT.PUT_LINE('Search About = '||to_char(l_split_at ) );
618
619
620
621 -- If the occurence of comma is found then populate the table record with the searched place less one
622
623 -- else populate the table record with the reminder of the l_imt_string and exit the loop
624
625
626
627 IF l_split_at <> 0 THEN
628
629 x_parsed_tbl(l_rec_count).imt_string := SUBSTR(l_imt_string,1,l_split_at -1);
630
631 /* --DBMS_OUTPUT.PUT_LINE('x_parsed_tbl('||to_char(l_rec_count)||').imt_string = '
632
633 || x_parsed_tbl(l_rec_count).imt_string );*/
634
635 ELSE
636
637 x_parsed_tbl(l_rec_count).imt_string := l_imt_string;
638
639 /* --DBMS_OUTPUT.PUT_LINE('x_parsed_tbl('||to_char(l_rec_count)||').imt_string = '
640
641 || l_imt_string );*/
642
643 EXIT;
644
645 END IF;
646
647
648
649 -- Modify l_imt_string for next iteration of the FOR loop. This will begin from the place where the
650
651 -- occurence of comma is found
652
653 l_imt_string := substr(l_imt_string,l_split_at);
654
655
656
657 -- Increment the table record count
658
659 l_rec_count := l_rec_count + 1;
660
661 END LOOP;
662
663 EXCEPTION
664
665 WHEN OTHERS THEN
666
667 RAISE;
668
669 /* FND_MESSAGE.SET_NAME('AMV','AMV_API_ERROR');
670
671 FND_MESSAGE.Set_Token('API', 'Parse_IMT_String');
672
673 FND_MSG_PUB.ADD;*/
674
675 END Parse_IMT_String;
676
677
678
679 --------------------------------------------------------------------------------
680
681 -- build an array of optional, required and excluded parameters
682
683 PROCEDURE parse_parameter_array(
684
685 p_param_array IN AMV_SEARCHPAR_VARRAY_TYPE,
686
687 x_optional_array OUT NOCOPY AMV_CHAR_VARRAY_TYPE,
688
689 x_required_array OUT NOCOPY AMV_CHAR_VARRAY_TYPE,
690
691 x_excluded_array OUT NOCOPY AMV_CHAR_VARRAY_TYPE,
692
693 x_keywords_search OUT NOCOPY VARCHAR2)
694
695 IS
696
697 l_opt_num number := 1;
698
699 l_req_num number := 1;
700
701 l_exc_num number := 1;
702
703 BEGIN
704
705 --DBMS_OUTPUT.PUT_LINE('Enter : parse_parameter_array ');
706
707 x_optional_array := amv_char_varray_type();
708
709 x_required_array := amv_char_varray_type();
710
711 x_excluded_array := amv_char_varray_type();
712
713 IF p_param_array.count > 0 THEN
714
715 FOR i in 1..p_param_array.count LOOP
716
717 IF p_param_array(i).operator = G_CAN_CONTAIN THEN
718
719 x_optional_array.extend;
720
721 x_optional_array(l_opt_num) := p_param_array(i).search_string;
722
723 l_opt_num := l_opt_num + 1;
724
725 ELSIF p_param_array(i).operator = G_MUST_CONTAIN THEN
726
727 x_required_array.extend;
728
729 x_required_array(l_req_num) := p_param_array(i).search_string;
730
731 l_req_num := l_req_num + 1;
732
733 ELSIF p_param_array(i).operator = G_MUST_NOT_CONTAIN THEN
734
735 x_excluded_array.extend;
736
737 x_excluded_array(l_exc_num) := p_param_array(i).search_string;
738
739 l_exc_num := l_exc_num + 1;
740
741 END IF;
742
743 END LOOP;
744
745 x_keywords_search := FND_API.G_TRUE;
746
747 ELSE
748
749 x_keywords_search := FND_API.G_FALSE;
750
751 END IF;
752
753 --DBMS_OUTPUT.PUT_LINE('Exit : parse_parameter_array');
754
755 EXCEPTION
756
757 WHEN OTHERS THEN
758
759 --DBMS_OUTPUT.PUT_LINE('Others : parse_parameter_array' );
760
761 RAISE;
762
763 END parse_parameter_array;
764
765 --------------------------------------------------------------------------------
766
767 -- build imt search string
768
769 PROCEDURE build_imt_string(
770
771 p_optional_array IN AMV_CHAR_VARRAY_TYPE,
772
773 p_required_array IN AMV_CHAR_VARRAY_TYPE,
774
775 p_excluded_array IN AMV_CHAR_VARRAY_TYPE,
776
777 x_exc_flag OUT NOCOPY VARCHAR2,
778
779 x_imt_string OUT NOCOPY VARCHAR2)
780
781 IS
782
783 l_req_string varchar2(4000);
784
785 l_opt_string varchar2(4000);
786
787 BEGIN
788
789 --DBMS_OUTPUT.PUT_LINE('Enter : build_imt_string' );
790
791 -- build string for AND operator
792
793 if p_required_array.count > 0 then
794
795 l_req_string := l_req_string || '(' ;
796
797 for i in 1..p_required_array.count loop
798
799 if i = 1 then
800
801 l_req_string := l_req_string ||'{'||p_required_array(i)||'}';
802
803 else
804
805 l_req_string := l_req_string ||'&'||'{'||p_required_array(i)||'}';
806
807 end if;
808
809 end loop;
810
811 l_req_string := l_req_string || ')' ;
812
813 end if;
814
815
816
817 -- build string for ACCUM operator
818
819 if p_optional_array.count > 0 then
820
821 l_opt_string := l_opt_string || '(' ;
822
823 for i in 1..p_optional_array.count loop
824
825 if i = 1 then
826
827 l_opt_string := l_opt_string ||'{'|| p_optional_array(i)||'}';
828
829 else
830
831 l_opt_string := l_opt_string ||','||'{'||p_optional_array(i)||'}';
832
833 end if;
834
835 end loop;
836
837 l_opt_string := l_opt_string || ')' ;
838
839 end if;
840
841
842
843 if l_req_string is not null then
844
845 if l_opt_string is not null then
846
847 x_imt_string := x_imt_string||
848
849 l_req_string||'|('||l_req_string||'&'||l_opt_string||')';
850
851 else
852
853 x_imt_string := x_imt_string|| l_req_string;
854
855 end if;
856
857 x_exc_flag := FND_API.G_FALSE;
858
859 else
860
861 if l_opt_string is not null then
862
863 x_imt_string := x_imt_string|| l_opt_string;
864
865 x_exc_flag := FND_API.G_FALSE;
866
867 end if;
868
869 end if;
870
871
872
873 -- build string for NOT operator
874
875 if p_excluded_array.count > 0 then
876
877 if x_imt_string is null then
878
879 x_imt_string := x_imt_string || '(' ;
880
881 x_exc_flag := FND_API.G_TRUE;
882
883 else
884
885 x_imt_string := x_imt_string || ' ~ (' ;
886
887 x_exc_flag := FND_API.G_FALSE;
888
889 end if;
890
891
892
893 for i in 1..p_excluded_array.count loop
894
895 if i = 1 then
896
897 x_imt_string := x_imt_string ||'{'||p_excluded_array(i)||'}';
898
899 else
900
901 x_imt_string := x_imt_string ||','||'{'||p_excluded_array(i)||'}';
902
903 end if;
904
905 end loop;
906
907 x_imt_string := x_imt_string || ')' ;
908
909 end if;
910
911 --DBMS_OUTPUT.PUT_LINE('Exit : build_imt_string' );
912
913 EXCEPTION
914
915 WHEN OTHERS THEN
916
917 --DBMS_OUTPUT.PUT_LINE('Others : build_imt_string' );
918
919 RAISE;
920
921
922
923 END build_imt_string;
924
925 --------------------------------------------------------------------------------
926
927 PROCEDURE get_chan_attr_stmt(
928
929 p_table_name IN VARCHAR2,
930
931 p_where_column IN VARCHAR2,
932
933 p_application_id IN NUMBER,
934
935 p_days IN NUMBER,
936
937 p_include_chns IN VARCHAR2 := FND_API.G_TRUE,
938
939 p_optional_array IN AMV_CHAR_VARRAY_TYPE,
940
941 p_required_array IN AMV_CHAR_VARRAY_TYPE,
942
943 p_excluded_array IN AMV_CHAR_VARRAY_TYPE,
944
945 p_index IN OUT NOCOPY PLS_INTEGER,
946
947 x_sql_statement IN OUT NOCOPY DBMS_SQL.VARCHAR2S)
948
949 IS
950
951 l_where_clause varchar2(10);
952
953 l_select varchar2(100);
954
955 l_from varchar2(400);
956
957 l_where varchar2(2000);
958
959 l_optional varchar2(1000);
960
961 l_required varchar2(1000);
962
963 l_excluded varchar2(1000);
964
965 l_join_col varchar2(100);
966
967 --l_where_column varchar2(100) := 'INITCAP('||p_where_column||')';
968
969 l_where_column varchar2(100) := p_where_column;
970
971 l_op_br varchar2(10);
972
973 l_cl_br varchar2(10);
974
975
976
977 --l_union varchar2(10) := ' UNION ';
978
979 l_minus varchar2(10) := ' MINUS ';
980
981
982
983 BEGIN
984
985 --DBMS_OUTPUT.PUT_LINE('Enter : get_chan_attr_stmt' );
986
987 -- build sql statement for channels
988
989 -- select construct
990
991 l_select := ' SELECT a.channel_id, a.channel_id, 50 ';
992
993 l_from := ' FROM '|| p_table_name ||' a ';
994
995 l_from := l_from || ' , amv_c_channels_vl b ';
996
997 IF p_include_chns = FND_API.G_TRUE THEN
998
999 l_from := l_from || ' , amv_temp_numbers id ';
1000
1001 END IF;
1002
1003 l_where := ' WHERE a.channel_id = b.channel_id';
1004
1005 l_where := l_where || ' AND b.application_id = ' || p_application_id;
1006
1007 l_where := l_where || ' AND b.effective_start_date <= sysdate';
1008
1009 l_where := l_where || ' and nvl(b.expiration_date, sysdate) >= sysdate';
1010
1011 IF p_days >= 0 THEN
1012
1013 l_where := l_where || ' and a.last_update_date >= (sysdate - ' || p_days || ' )';
1014
1015 END IF;
1016
1017 IF p_include_chns = FND_API.G_TRUE THEN
1018
1019 l_where := l_where || ' AND b.channel_id = id.number_value ';
1020
1021 END IF;
1022
1023
1024
1025 if p_optional_array.count > 0 or p_required_array.count > 0 then
1026
1027 x_sql_statement(p_index) := l_select;
1028
1029 p_index := p_index + 1;
1030
1031 x_sql_statement(p_index) := l_from;
1032
1033 p_index := p_index + 1;
1034
1035 x_sql_statement(p_index) := l_where;
1036
1037 p_index := p_index + 1;
1038
1039 end if;
1040
1041
1042
1043 l_where_clause := ' AND ';
1044
1045
1046
1047 -- where clause for required words
1048
1049 if p_required_array.count > 0 then
1050
1051 for i in 1..p_required_array.count LOOP
1052
1053 l_required := l_where_clause || l_where_column ||
1054
1055 ' LIKE INITCAP('''||p_required_array(i)||'%'||''')';
1056
1057
1058
1059 x_sql_statement(p_index) := l_required;
1060
1061 p_index := p_index + 1;
1062
1063
1064
1065 end loop;
1066
1067 end if;
1068
1069
1070
1071 -- where clause for optional words
1072
1073 if p_optional_array.count > 0 then
1074
1075 for i in 1..p_optional_array.count LOOP
1076
1077 if i=1 then
1078
1079 l_op_br := '(';
1080
1081 end if;
1082
1083 if i=p_optional_array.count then
1084
1085 l_cl_br := ')';
1086
1087 end if;
1088
1089
1090
1091 l_optional := l_where_clause || l_op_br || l_where_column ||
1092
1093 ' LIKE INITCAP('''||p_optional_array(i)||'%'||''')'|| l_cl_br;
1094
1095
1096
1097 l_where_clause := ' OR ';
1098
1099 l_op_br := null;
1100
1101 l_cl_br := null;
1102
1103
1104
1105 x_sql_statement(p_index) := l_optional;
1106
1107 p_index := p_index + 1;
1108
1109 end loop;
1110
1111 end if;
1112
1113
1114
1115 if p_optional_array.count > 0 or p_required_array.count > 0 then
1116
1117 if p_excluded_array.count > 0 then
1118
1119 x_sql_statement(p_index) := l_minus;
1120
1121 p_index := p_index + 1;
1122
1123 end if;
1124
1125 else
1126
1127 if p_excluded_array.count > 0 then
1128
1129 x_sql_statement(p_index) := l_select;
1130
1131 p_index := p_index + 1;
1132
1133 x_sql_statement(p_index) := l_from;
1134
1135 p_index := p_index + 1;
1136
1137 x_sql_statement(p_index) := l_where;
1138
1139 p_index := p_index + 1;
1140
1141
1142
1143 x_sql_statement(p_index) := l_minus;
1144
1145 p_index := p_index + 1;
1146
1147 end if;
1148
1149 end if;
1150
1151
1152
1153 -- where clause for excluded words
1154
1155 if p_excluded_array.count > 0 then
1156
1157 x_sql_statement(p_index) := l_select;
1158
1159 p_index := p_index + 1;
1160
1161 x_sql_statement(p_index) := l_from;
1162
1163 p_index := p_index + 1;
1164
1165 x_sql_statement(p_index) := l_where;
1166
1167 p_index := p_index + 1;
1168
1169
1170
1171 l_where_clause := ' AND ';
1172
1173 for i in 1..p_excluded_array.count LOOP
1174
1175 if i=1 then
1176
1177 l_op_br := '(';
1178
1179 end if;
1180
1181 if i=p_excluded_array.count then
1182
1183 l_cl_br := ')';
1184
1185 end if;
1186
1187
1188
1189 l_excluded := l_where_clause || l_op_br || l_where_column ||
1190
1191 ' LIKE INITCAP('''||p_excluded_array(i)||'%'||''')'|| l_cl_br;
1192
1193 l_where_clause := ' OR ';
1194
1195 l_op_br := null;
1196
1197 l_cl_br := null;
1198
1199
1200
1201 x_sql_statement(p_index) := l_excluded;
1202
1203 p_index := p_index + 1;
1204
1205
1206
1207 end loop;
1208
1209 end if;
1210
1211 --DBMS_OUTPUT.PUT_LINE('Exit : get_chan_attr_stmt' );
1212
1213 EXCEPTION
1214
1215 WHEN OTHERS THEN
1216
1217 x_sql_statement(p_index) := 'ERROR';
1218
1219 --DBMS_OUTPUT.PUT_LINE('Others : get_chan_attr_stmt' );
1220
1221 --RAISE;
1222
1223 END get_chan_attr_stmt;
1224
1225 --------------------------------------------------------------------------------
1226
1227 PROCEDURE get_item_attr_stmt(
1228
1229 p_table_name IN VARCHAR2,
1230
1231 p_where_column IN VARCHAR2,
1232
1233 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
1234
1235 p_days IN NUMBER,
1236
1237 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
1238
1239 p_include_chns IN VARCHAR2 := FND_API.G_TRUE,
1240
1241 p_search_level IN VARCHAR2,
1242
1243 p_optional_array IN AMV_CHAR_VARRAY_TYPE,
1244
1245 p_required_array IN AMV_CHAR_VARRAY_TYPE,
1246
1247 p_excluded_array IN AMV_CHAR_VARRAY_TYPE,
1248
1249 p_index IN OUT NOCOPY PLS_INTEGER,
1250
1251 x_sql_statement IN OUT NOCOPY DBMS_SQL.VARCHAR2S)
1252
1253 IS
1254
1255 l_where_clause varchar2(10);
1256
1257 l_select varchar2(100);
1258
1259 l_from varchar2(400);
1260
1261 l_where varchar2(1000);
1262
1263 l_where1 varchar2(1000);
1264
1265 l_optional varchar2(1000);
1266
1267 l_required varchar2(1000);
1268
1269 l_excluded varchar2(1000);
1270
1271 l_join_col varchar2(100);
1272
1273 --l_where_column varchar2(100) := 'INITCAP('||p_where_column||')';
1274
1275 l_where_column varchar2(100) := p_where_column;
1276
1277 l_op_br varchar2(10);
1278
1279 l_cl_br varchar2(10);
1280
1281
1282
1283 --l_union varchar2(10) := ' UNION ';
1284
1285 l_minus varchar2(10) := ' MINUS ';
1286
1287 --Following are variable declaration used by new code
1288 l_mainSelect varchar2(100);
1289 l_mainFrom varchar2(400);
1290 l_mainWhere varchar2(1000);
1291
1292 l_subSelect varchar2(100);
1293 l_subFrom varchar2(400);
1294 l_subWhere varchar2(1000);
1295
1296 x_sqlSubSql DBMS_SQL.VARCHAR2S;
1297 sqlSubSql_index PLS_INTEGER := 0;
1298
1299 -- End Following are variable declaration used by new code
1300
1301 BEGIN
1302
1303
1304
1305 --DBMS_OUTPUT.PUT_LINE('Enter : get_item_attr_stmt' );
1306
1307 -- build attribute tables sql statement for items
1308
1309 -- select construct
1310
1311 --l_select := ' SELECT a.item_id ';
1312
1313 l_subSelect := ' SELECT b.item_id ';
1314
1315
1316 --ss
1317 l_mainSelect := ' SELECT cim.item_id ';
1318
1319 --ss
1320
1321
1322 IF p_search_level = G_CHANNEL THEN
1323
1324 --l_select := l_select || ', cim.channel_id';
1325
1326 --ss
1327 l_mainSelect := l_mainSelect||' , cim.channel_id ';
1328
1329 --ss
1330 ELSIF p_search_level = G_CATEGORY THEN
1331
1332 --l_select := l_select || ', cim.channel_category_id';
1333
1334 --ss
1335 l_mainSelect := l_mainSelect||', cim.channel_category_id';
1336
1337 --ss
1338 ELSE
1339
1340 --l_select := l_select || ', a.item_id';
1341
1342 --ss
1343 l_mainSelect := l_mainSelect||', cim.item_id';
1344
1345 --ss
1346 END IF;
1347
1348
1349
1350
1351
1352 --l_select := l_select || ', 50 ';
1353
1354 --ss
1355 l_mainSelect := l_mainSelect||', 50 ';
1356 --ss
1357
1358 --Commented for new SQL
1359 -- l_from := ' FROM '|| p_table_name ||' a ' ||
1360
1361 -- ' , jtf_amv_items_vl b ';
1362
1363
1364 --ss
1365
1366 l_subFrom := ' FROM '|| p_table_name ||' a ' ||
1367
1368 ' , jtf_amv_items_b b ';
1369
1370 --ss
1371 IF p_include_chns = FND_API.G_TRUE THEN
1372
1373 --Commented for new SQL
1374 --l_from := l_from || ' , amv_c_chl_item_match cim ';
1375
1376 --l_from := l_from || ' , amv_temp_numbers id ';
1377
1378
1379 --ss
1380
1381 l_mainFrom := ' FROM amv_c_chl_item_match cim ';
1382
1383 l_mainFrom := l_mainFrom || ' , amv_temp_numbers id ';
1384 --ss
1385
1386 END IF;
1387
1388
1389 --Commented for new SQL
1390 --l_where := ' WHERE a.item_id = b.item_id' ||
1391
1392 --' AND nvl(b.effective_start_date,sysdate)<=sysdate+1' ||
1393
1394 --' AND nvl(b.expiration_date, sysdate) >= sysdate';
1395
1396 --End Commented for new SQL
1397
1398
1399 --ss
1400
1401 l_subWhere := ' WHERE a.item_id = b.item_id' ||
1402
1403 ' AND nvl(b.effective_start_date,sysdate)<=sysdate+1' ||
1404
1405 ' AND nvl(b.expiration_date, sysdate) >= sysdate';
1406
1407 --ss
1408 IF p_days >= 0 THEN
1409
1410 --commented for new sql
1411 --l_where := l_where || ' AND a.last_update_date >= (sysdate - ' || p_days || ' )';
1412
1413
1414 --ss
1415 l_subWhere := l_subWhere || ' AND a.last_update_date >= (sysdate - ' || p_days || ' )';
1416
1417 --ss
1418 END IF;
1419
1420
1421
1422
1423 IF p_application_id <> FND_API.G_MISS_NUM THEN
1424
1425 --commented for new sql code
1426 --l_where := l_where || ' AND b.application_id = ' || p_application_id;
1427
1428
1429 --ss
1430 l_subWhere := l_subWhere || ' AND b.application_id = ' || p_application_id;
1431
1432 --ss
1433 END IF;
1434
1435
1436
1437 IF p_external_contents <> FND_API.G_FALSE THEN
1438
1439 ----commented for new sql code
1440 --l_where := l_where || ' AND b.external_access_flag = ''' ||
1441
1442 -- p_external_contents ||'''';
1443
1444 --ss
1445
1446 l_subWhere := l_subWhere || ' AND b.external_access_flag = ''' ||
1447
1448 p_external_contents ||'''';
1449
1450 --ss
1451 END IF;
1452
1453
1454
1455 IF p_include_chns = FND_API.G_TRUE THEN
1456
1457 --commented for new sql code
1458 --l_where1 := l_where1 || ' AND b.item_id = cim.item_id ';
1459
1460 --ss
1461 l_subWhere := l_subWhere || ' AND b.item_id = cim.item_id ';
1462
1463 --ss
1464 IF p_search_level = G_CHANNEL THEN
1465
1466 --commented for new sql code
1467 --l_where1 := l_where1 || ' AND cim.channel_id = id.number_value ';
1468
1469
1470 --ss
1471 l_mainWhere := l_mainWhere || ' where cim.channel_id = id.number_value ';
1472
1473 --ss
1474 ELSIF p_search_level = G_CATEGORY THEN
1475
1476 --commented for new sql code
1477 --l_where1 := l_where1|| ' AND cim.channel_category_id = id.number_value ';
1478
1479 --l_where1 := l_where1|| ' AND cim.channel_id is null ';
1480
1481
1482 --ss
1483 l_mainWhere := l_mainWhere || ' where cim.channel_category_id = id.number_value ';
1484
1485 l_mainWhere := l_mainWhere || ' AND cim.channel_id is null ';
1486
1487 --ss
1488 END IF;
1489
1490
1491
1492 --commented for new sql code
1493 --l_where1 := l_where1 || ' AND cim.approval_status_type = '''||
1494
1495 --G_APPROVED ||'''' || ' AND cim.table_name_code = '''||
1496
1497 --AMV_UTILITY_PVT.G_TABLE_NAME_CODE ||'''' ||
1498
1499 --' AND cim.available_for_channel_date <= sysdate ';
1500
1501
1502 --ss
1503
1504 l_mainWhere := l_mainWhere || ' AND cim.approval_status_type = '''||
1505
1506 G_APPROVED ||'''' || ' AND cim.table_name_code = '''||
1507
1508 AMV_UTILITY_PVT.G_TABLE_NAME_CODE ||'''' ||
1509
1510 ' AND cim.available_for_channel_date <= sysdate ';
1511
1512 --ss
1513 END IF;
1514
1515
1516
1517 if p_optional_array.count > 0 or p_required_array.count > 0 then
1518
1519 --commented for new sql code
1520 --x_sql_statement(p_index) := l_select; -- 2
1521
1522 --p_index := p_index + 1;
1523
1524 --x_sql_statement(p_index) := l_from; -- 3
1525
1526 --p_index := p_index + 1;
1527
1528 --x_sql_statement(p_index) := l_where; -- 4
1529
1530 --p_index := p_index + 1;
1531
1532
1533 --ss
1534 x_sqlSubSql(sqlSubSql_index) := l_subSelect; -- 2
1535
1536 sqlSubSql_index := sqlSubSql_index + 1;
1537
1538 x_sqlSubSql(sqlSubSql_index) := l_subFrom; -- 3
1539
1540 sqlSubSql_index := sqlSubSql_index + 1;
1541
1542 x_sqlSubSql(sqlSubSql_index) := l_subWhere; -- 4
1543
1544 sqlSubSql_index := sqlSubSql_index + 1;
1545
1546 --ss
1547
1548
1549 --DBMS_OUTPUT.PUT_LINE('l_select: ' || l_select);
1550
1551 --DBMS_OUTPUT.PUT_LINE('l_from: ' || l_from);
1552
1553 --DBMS_OUTPUT.PUT_LINE('l_where: ' || l_where);
1554
1555
1556
1557 --DBMS_OUTPUT.PUT_LINE('p_index A is: ' || p_index); -- 5
1558
1559
1560
1561 --commented for new sql code
1562
1563 --IF p_include_chns = FND_API.G_TRUE THEN
1564
1565 --x_sql_statement(p_index) := l_where1;
1566
1567 --p_index := p_index + 1;
1568
1569 --END IF;
1570
1571
1572
1573 --DBMS_OUTPUT.PUT_LINE('p_index B is: ' || p_index);
1574
1575 end if;
1576
1577
1578
1579 l_where_clause := ' AND ';
1580
1581
1582
1583 -- where clause for required words
1584
1585 if p_required_array.count > 0 then
1586
1587 for i in 1..p_required_array.count LOOP
1588
1589 l_required := l_where_clause || l_where_column ||
1590
1591 ' LIKE INITCAP('''||p_required_array(i)||'%'||''')';
1592
1593 l_where_clause := ' AND ';
1594
1595
1596
1597 --commented for new sql code
1598 --x_sql_statement(p_index) := l_required;
1599
1600 --p_index := p_index + 1;
1601
1602
1603 --ss
1604 x_sqlSubSql(sqlSubSql_index) := l_required;
1605 sqlSubSql_index := sqlSubSql_index + 1;
1606
1607 --ss
1608
1609
1610 end loop;
1611
1612
1613 end if;
1614
1615
1616
1617 --DBMS_OUTPUT.PUT_LINE('p_index C is: ' || p_index);
1618
1619
1620
1621 -- where clause for optional words
1622
1623 if p_optional_array.count > 0 then
1624
1625 for i in 1..p_optional_array.count LOOP
1626
1627 if i=1 then
1628
1629 l_op_br := '(';
1630
1631 end if;
1632
1633 if i=p_optional_array.count then
1634
1635 l_cl_br := ')';
1636
1637 end if;
1638
1639
1640
1641 l_optional := l_where_clause || l_op_br || l_where_column ||
1642
1643 ' LIKE INITCAP('''||p_optional_array(i)||'%'||''')'|| l_cl_br;
1644
1645
1646
1647 l_where_clause := ' OR ';
1648
1649 l_op_br := null;
1650
1651 l_cl_br := null;
1652
1653
1654
1655 --commented for new sql code
1656 --x_sql_statement(p_index) := l_optional;
1657
1658 --DBMS_OUTPUT.PUT_LINE('l_optional: ' || l_optional);
1659
1660 --p_index := p_index + 1;
1661
1662
1663 --ss
1664 x_sqlSubSql(sqlSubSql_index) := l_optional;
1665 sqlSubSql_index := sqlSubSql_index + 1;
1666
1667 --ss
1668
1669 end loop;
1670
1671 end if;
1672
1673
1674
1675
1676
1677 --DBMS_OUTPUT.PUT_LINE('p_index D is: ' || p_index);
1678
1679
1680
1681 if p_optional_array.count > 0 or p_required_array.count > 0 then
1682
1683 if p_excluded_array.count > 0 then
1684
1685 --commented for new sql code
1686 --x_sql_statement(p_index) := l_minus;
1687
1688 --p_index := p_index + 1;
1689
1690
1691 --ss
1692 x_sqlSubSql(sqlSubSql_index) := l_minus;
1693 sqlSubSql_index := sqlSubSql_index + 1;
1694
1695 --ss
1696
1697 end if;
1698
1699
1700
1701 else
1702
1703 if p_excluded_array.count > 0 then
1704
1705 --commented for new sql code
1706 --x_sql_statement(p_index) := l_select;
1707
1708 --p_index := p_index + 1;
1709
1710 --x_sql_statement(p_index) := l_from;
1711
1712 --p_index := p_index + 1;
1713
1714 --x_sql_statement(p_index) := l_where;
1715
1716 --p_index := p_index + 1;
1717
1718
1719 --IF p_include_chns = FND_API.G_TRUE THEN
1720
1721 --x_sql_statement(p_index) := l_where1;
1722
1723 --p_index := p_index + 1;
1724
1725 --END IF;
1726
1727
1728
1729 --x_sql_statement(p_index) := l_minus;
1730
1731 --p_index := p_index + 1;
1732
1733
1734 --ss
1735 x_sqlSubSql(sqlSubSql_index) := l_minus;
1736 sqlSubSql_index := sqlSubSql_index + 1;
1737
1738 --ss
1739 end if;
1740
1741 end if;
1742
1743
1744
1745
1746
1747 --DBMS_OUTPUT.PUT_LINE('p_index E is: ' || p_index);
1748
1749
1750
1751 -- where clause for excluded words
1752
1753 if p_excluded_array.count > 0 then
1754
1755 --commented for new sql code
1756 --x_sql_statement(p_index) := l_select;
1757
1758 --p_index := p_index + 1;
1759
1760 --x_sql_statement(p_index) := l_from;
1761
1762 --p_index := p_index + 1;
1763
1764 --x_sql_statement(p_index) := l_where;
1765
1766 --p_index := p_index + 1;
1767
1768
1769
1770 l_where_clause := ' AND ';
1771
1772 for i in 1..p_excluded_array.count LOOP
1773
1774 if i=1 then
1775
1776 l_op_br := '(';
1777
1778 end if;
1779
1780 if i=p_excluded_array.count then
1781
1782 l_cl_br := ')';
1783
1784 end if;
1785
1786
1787
1788 l_excluded := l_where_clause || l_op_br || l_where_column ||
1789
1790 ' LIKE INITCAP('''||p_excluded_array(i)||'%'||''')'|| l_cl_br;
1791
1792 l_where_clause := ' OR ';
1793
1794 l_op_br := null;
1795
1796 l_cl_br := null;
1797
1798
1799
1800 --commented for new sql code
1801 --x_sql_statement(p_index) := l_excluded;
1802
1803 --p_index := p_index + 1;
1804
1805
1806
1807 --ss
1808 x_sqlSubSql(sqlSubSql_index) := l_excluded;
1809 sqlSubSql_index := sqlSubSql_index + 1;
1810
1811 --ss
1812
1813 end loop;
1814
1815 end if;
1816
1817
1818
1819 --DBMS_OUTPUT.PUT_LINE('get_item_attr_stmt : p_index F is: ' || p_index);
1820
1821 --DBMS_OUTPUT.PUT_LINE('get_item_attr_stmt : x_sql_statement.count is: ' || x_sql_statement.count);
1822
1823
1824 --new sql code, Join the main and sub sql to get the final sql
1825
1826 x_sql_statement(p_index) := l_mainSelect;
1827
1828 p_index := p_index + 1;
1829
1830 x_sql_statement(p_index) := l_mainFrom;
1831
1832 p_index := p_index + 1;
1833
1834 x_sql_statement(p_index) := l_mainWhere;
1835
1836 p_index := p_index + 1;
1837
1838
1839 x_sql_statement(p_index) := ' AND EXISTS (';
1840
1841 p_index := p_index + 1;
1842
1843
1844 FOR pti in 0..x_sqlSubSql.count-1 LOOP
1845 --DBMS_OUTPUT.PUT_LINE('SUB SQL Line '||pti||'='||x_sqlSubSql(pti));
1846 x_sql_statement(p_index) := x_sqlSubSql(pti);
1847
1848 p_index := p_index + 1;
1849
1850 END LOOP;
1851 x_sql_statement(p_index) := ' )';
1852
1853 p_index := p_index + 1;
1854
1855
1856
1857
1858 --DBMS_OUTPUT.PUT_LINE('Exit : get_item_attr_stmt' );
1859
1860 EXCEPTION
1861
1862 WHEN OTHERS THEN
1863
1864 x_sql_statement(p_index) := 'ERROR';
1865
1866 --DBMS_OUTPUT.PUT_LINE('Others : get_item_attr_stmt' );
1867
1868 RAISE;
1869
1870 END get_item_attr_stmt;
1871
1872
1873 --------------------------------------------------------------------------------
1874
1875 PROCEDURE build_chan_name_sql (
1876
1877 p_index IN OUT NOCOPY PLS_INTEGER,
1878
1879 p_imt_string IN VARCHAR2,
1880
1881 p_application_id IN NUMBER,
1882
1883 p_excluded_flag IN VARCHAR2,
1884
1885 p_include_chns IN VARCHAR2 := FND_API.G_TRUE,
1886
1887 p_days IN NUMBER,
1888
1889 x_sql_statement IN OUT NOCOPY DBMS_SQL.VARCHAR2S
1890
1891 )
1892
1893 IS
1894
1895 -- Declare local variables to be used in the parsing logic
1896
1897 l_parsed_tbl parsed_tbl_type;
1898
1899 l_rec_count NUMBER;
1900
1901 BEGIN
1902
1903 IF p_imt_string IS NOT NULL THEN
1904
1905 --DBMS_OUTPUT.PUT_LINE('Enter : build_chan_name_sql ');
1906
1907 -- Call the Parse_IMT_String
1908
1909 Parse_IMT_String
1910
1911 (p_imt_string => p_imt_string
1912
1913 ,x_parsed_tbl => l_parsed_tbl
1914
1915 );
1916
1917 END IF;
1918
1919
1920
1921 -- content index
1922
1923 x_sql_statement(p_index) := ' select b.channel_id';
1924
1925 p_index := p_index + 1;
1926
1927 x_sql_statement(p_index) := ', b.channel_id, (score(1) + score(2))/2 ';
1928
1929 p_index := p_index + 1;
1930
1931 x_sql_statement(p_index) := ' from amv_c_channels_vl b ';
1932
1933 p_index := p_index + 1;
1934
1935 IF p_include_chns = FND_API.G_TRUE THEN
1936
1937 x_sql_statement(p_index) := ' , amv_temp_numbers id ';
1938
1939 p_index := p_index + 1;
1940
1941 END IF;
1942
1943
1944
1945 IF p_excluded_flag = FND_API.G_TRUE THEN
1946
1947 x_sql_statement(p_index) := ' where ( contains( b.channel_name,';
1948
1949 p_index := p_index + 1;
1950
1951 -- IMT String Parsing Logic used
1952
1953 l_rec_count := 1;
1954
1955 LOOP
1956
1957 x_sql_statement(p_index) := ''''||l_parsed_tbl(l_rec_count).imt_string||''''||'||';
1958
1959 EXIT WHEN l_rec_count = l_parsed_tbl.COUNT;
1960
1961 l_rec_count := l_rec_count + 1;
1962
1963 p_index := p_index + 1;
1964
1965 END LOOP;
1966
1967
1968
1969 -- Remove the concatenation operator from the last element
1970
1971 x_sql_statement(p_index) := SUBSTR(x_sql_statement(p_index),1,LENGTH(x_sql_statement(p_index))-2);
1972
1973 -- Increment the array index count
1974
1975 p_index := p_index + 1;
1976
1977 -- IMT logic ends
1978
1979 x_sql_statement(p_index) := ', 1) = 0';
1980
1981 p_index := p_index + 1;
1982
1983
1984
1985 x_sql_statement(p_index) := ' or contains( b.description, ';
1986
1987 p_index := p_index + 1;
1988
1989 -- IMT String Parsing Logic used
1990
1991 l_rec_count := 1;
1992
1993 LOOP
1994
1995 x_sql_statement(p_index) := ''''||l_parsed_tbl(l_rec_count).imt_string||''''||'||';
1996
1997 EXIT WHEN l_rec_count = l_parsed_tbl.COUNT;
1998
1999 l_rec_count := l_rec_count + 1;
2000
2001 p_index := p_index + 1;
2002
2003 END LOOP;
2004
2005
2006
2007 -- Remove the concatenation operator from the last element
2008
2009 x_sql_statement(p_index) := SUBSTR(x_sql_statement(p_index),1,LENGTH(x_sql_statement(p_index))-2);
2010
2011 -- Increment the array index count
2012
2013 p_index := p_index + 1;
2014
2015 -- IMT logic ends
2016
2017
2018
2019 x_sql_statement(p_index) := ', 2) = 0)';
2020
2021 p_index := p_index + 1;
2022
2023
2024
2025 ELSE
2026
2027 x_sql_statement(p_index) := ' where ( contains( b.channel_name,';
2028
2029 p_index := p_index + 1;
2030
2031 -- IMT String Parsing Logic used
2032
2033 l_rec_count := 1;
2034
2035 LOOP
2036
2037 x_sql_statement(p_index) := ''''||l_parsed_tbl(l_rec_count).imt_string||''''||'||';
2038
2039 EXIT WHEN l_rec_count = l_parsed_tbl.COUNT;
2040
2041 l_rec_count := l_rec_count + 1;
2042
2043 p_index := p_index + 1;
2044
2045 END LOOP;
2046
2047
2048
2049 -- Remove the concatenation operator from the last element
2050
2051 x_sql_statement(p_index) := SUBSTR(x_sql_statement(p_index),1,LENGTH(x_sql_statement(p_index))-2);
2052
2053 -- Increment the array index count
2054
2055 p_index := p_index + 1;
2056
2057 -- IMT logic ends
2058
2059 x_sql_statement(p_index) := ', 1) > 0';
2060
2061 p_index := p_index + 1;
2062
2063
2064
2065 x_sql_statement(p_index) := ' or contains( b.description, ';
2066
2067 p_index := p_index + 1;
2068
2069 -- IMT String Parsing Logic used
2070
2071 l_rec_count := 1;
2072
2073 LOOP
2074
2075 x_sql_statement(p_index) := ''''||l_parsed_tbl(l_rec_count).imt_string||''''||'||';
2076
2077 EXIT WHEN l_rec_count = l_parsed_tbl.COUNT;
2078
2079 l_rec_count := l_rec_count + 1;
2080
2081 p_index := p_index + 1;
2082
2083 END LOOP;
2084
2085
2086
2087 -- Remove the concatenation operator from the last element
2088
2089 x_sql_statement(p_index) := SUBSTR(x_sql_statement(p_index),1,LENGTH(x_sql_statement(p_index))-2);
2090
2091 -- Increment the array index count
2092
2093 p_index := p_index + 1;
2094
2095 -- IMT logic ends
2096
2097 x_sql_statement(p_index) := ', 2) > 0)';
2098
2099 p_index := p_index + 1;
2100
2101
2102
2103 END IF;
2104
2105 x_sql_statement(p_index) := ' and b.application_id = ' || p_application_id;
2106
2107 p_index := p_index + 1;
2108
2109 IF p_include_chns = FND_API.G_TRUE THEN
2110
2111 x_sql_statement(p_index) := ' and b.channel_id = id.number_value';
2112
2113 p_index := p_index + 1;
2114
2115 END IF;
2116
2117 x_sql_statement(p_index) :=
2118
2119 ' and b.effective_start_date <= sysdate';
2120
2121 p_index := p_index + 1;
2122
2123 x_sql_statement(p_index) :=
2124
2125 ' and nvl(b.expiration_date, sysdate) >= sysdate';
2126
2127 p_index := p_index + 1;
2128
2129 IF p_days >= 0 THEN
2130
2131 x_sql_statement(p_index) :=
2132
2133 ' and b.last_update_date >= (sysdate - ' || p_days || ' )';
2134
2135 p_index := p_index + 1;
2136
2137 END IF;
2138
2139
2140
2141 --DBMS_OUTPUT.PUT_LINE('Exit : build_chan_name_sql ');
2142
2143
2144
2145 EXCEPTION
2146
2147 WHEN OTHERS THEN
2148
2149 x_sql_statement(p_index) := 'ERROR';
2150
2151 --DBMS_OUTPUT.PUT_LINE('OTHERS : build_chan_name_sql ');
2152
2153 --RAISE;
2154
2155 END build_chan_name_sql;
2156
2157 --------------------------------------------------------------------------------
2158
2159 PROCEDURE build_items_name_sql (
2160
2161 p_index IN OUT NOCOPY PLS_INTEGER,
2162
2163 p_imt_string IN VARCHAR2,
2164
2165 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
2166
2167 p_include_chns IN VARCHAR2 := FND_API.G_TRUE,
2168
2169 p_days IN NUMBER,
2170
2171 p_search_level IN VARCHAR2,
2172
2173 p_excluded_flag IN VARCHAR2,
2174
2175 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
2176
2177 x_sql_statement IN OUT NOCOPY DBMS_SQL.VARCHAR2S
2178
2179 )
2180
2181 IS
2182
2183 -- Declare local variables to be used in the parsing logic
2184
2185 l_parsed_tbl parsed_tbl_type;
2186
2187 l_rec_count NUMBER;
2188
2189 --Added as part of code to fix 2719461
2190
2191 --start of code
2192
2193 CURSOR category_id_list IS
2194
2195 select number_value
2196
2197 from amv_temp_numbers;
2198
2199
2200
2201 l_category_counter number :=1;
2202
2203 l_category_id number;
2204
2205 l_category_id_list VARCHAR2(32000);
2206
2207 --end of code
2208 BEGIN
2209
2210
2211 --Added as part of code to fix 2719461
2212
2213 --start of code
2214
2215 OPEN category_id_list;
2216
2217 LOOP
2218
2219 FETCH category_id_list INTO l_category_id;
2220
2221
2222
2223 EXIT WHEN category_id_list%NOTFOUND;
2224
2225
2226
2227 IF l_category_counter = 1 THEN
2228
2229 l_category_id_list := l_category_id;
2230
2231 ELSE
2232
2233 l_category_id_list := l_category_id_list ||','||l_category_id;
2234
2235 END IF;
2236
2237
2238
2239 l_category_counter := l_category_counter +1;
2240
2241
2242
2243 END LOOP;
2244
2245 CLOSE category_id_list;
2246
2247 --DBMS_OUTPUT.PUT_LINE('l_category_id_list ='||l_category_id_list );
2248
2249 --end of code
2250
2251
2252 IF p_imt_string IS NOT NULL THEN
2253
2254 --DBMS_OUTPUT.PUT_LINE('Enter : build_items_name_sql ');
2255
2256 -- Call the Parse_IMT_String
2257
2258 Parse_IMT_String
2259
2260 (p_imt_string => p_imt_string
2261
2262 ,x_parsed_tbl => l_parsed_tbl
2263
2264 );
2265
2266 END IF;
2267
2268 -- content search
2269
2270 x_sql_statement(p_index) := ' select b.item_id';
2271
2272 p_index := p_index + 1;
2273
2274 IF p_search_level = G_CHANNEL THEN
2275
2276 x_sql_statement(p_index) := ', cim.channel_id';
2277
2278 p_index := p_index + 1;
2279
2280 ELSIF p_search_level = G_CATEGORY THEN
2281
2282 x_sql_statement(p_index) := ', cim.channel_category_id';
2283
2284 p_index := p_index + 1;
2285
2286 ELSE
2287
2288 x_sql_statement(p_index) := ', b.item_id';
2289
2290 p_index := p_index + 1;
2291
2292 END IF;
2293
2294 x_sql_statement(p_index) := ', (score(1) + score(2))/2';
2295
2296 p_index := p_index + 1;
2297
2298 x_sql_statement(p_index) := ' from jtf_amv_items_vl b ';
2299
2300 p_index := p_index + 1;
2301
2302 IF p_include_chns = FND_API.G_TRUE THEN
2303
2304 x_sql_statement(p_index) := ' , amv_c_chl_item_match cim ';
2305
2306 p_index := p_index + 1;
2307
2308 x_sql_statement(p_index) := ' , amv_temp_numbers id ';
2309
2310 p_index := p_index + 1;
2311
2312 END IF;
2313
2314 IF p_excluded_flag = FND_API.G_TRUE THEN
2315
2316 x_sql_statement(p_index) := ' where (contains( b.item_name, ';
2317
2318 p_index := p_index + 1;
2319
2320 -- IMT String Parsing Logic used
2321
2322 l_rec_count := 1;
2323
2324 LOOP
2325
2326 x_sql_statement(p_index) := ''''||l_parsed_tbl(l_rec_count).imt_string||''''||'||';
2327
2328 EXIT WHEN l_rec_count = l_parsed_tbl.COUNT;
2329
2330 l_rec_count := l_rec_count + 1;
2331
2332 p_index := p_index + 1;
2333
2334 END LOOP;
2335
2336
2337
2338 -- Remove the concatenation operator from the last element
2339
2340 x_sql_statement(p_index) := SUBSTR(x_sql_statement(p_index),1,LENGTH(x_sql_statement(p_index))-2);
2341
2342 -- Increment the array index count
2343
2344 p_index := p_index + 1;
2345
2346 -- IMT logic ends
2347
2348 x_sql_statement(p_index) := ', 1) = 0';
2349
2350 p_index := p_index + 1;
2351
2352
2353
2354 x_sql_statement(p_index) := ' or contains( b.description, ';
2355
2356 p_index := p_index + 1;
2357
2358 -- IMT String Parsing Logic used
2359
2360 l_rec_count := 1;
2361
2362 LOOP
2363
2364 x_sql_statement(p_index) := ''''||l_parsed_tbl(l_rec_count).imt_string||''''||'||';
2365
2366 EXIT WHEN l_rec_count = l_parsed_tbl.COUNT;
2367
2368 l_rec_count := l_rec_count + 1;
2369
2370 p_index := p_index + 1;
2371
2372 END LOOP;
2373
2374
2375
2376 -- Remove the concatenation operator from the last element
2377
2378 x_sql_statement(p_index) := SUBSTR(x_sql_statement(p_index),1,LENGTH(x_sql_statement(p_index))-2);
2379
2380 -- Increment the array index count
2381
2382 p_index := p_index + 1;
2383
2384 -- IMT logic ends
2385
2386 x_sql_statement(p_index) := ', 2) = 0 ) ';
2387
2388 p_index := p_index + 1;
2389
2390
2391
2392 ELSE
2393
2394 x_sql_statement(p_index) := ' where (contains( b.item_name, ';
2395
2396 p_index := p_index + 1;
2397
2398 -- IMT String Parsing Logic used
2399
2400 l_rec_count := 1;
2401
2402 LOOP
2403
2404 x_sql_statement(p_index) := ''''||l_parsed_tbl(l_rec_count).imt_string||''''||'||';
2405
2406 EXIT WHEN l_rec_count = l_parsed_tbl.COUNT;
2407
2408 l_rec_count := l_rec_count + 1;
2409
2410 p_index := p_index + 1;
2411
2412 END LOOP;
2413
2414
2415
2416 -- Remove the concatenation operator from the last element
2417
2418 x_sql_statement(p_index) := SUBSTR(x_sql_statement(p_index),1,LENGTH(x_sql_statement(p_index))-2);
2419
2420 -- Increment the array index count
2421
2422 p_index := p_index + 1;
2423
2424 -- IMT logic ends
2425
2426 x_sql_statement(p_index) := ', 1) > 0';
2427
2428 p_index := p_index + 1;
2429
2430
2431
2432
2433
2434 x_sql_statement(p_index) := ' or contains( b.description, ';
2435
2436 p_index := p_index + 1;
2437
2438 -- IMT String Parsing Logic used
2439
2440 l_rec_count := 1;
2441
2442 LOOP
2443
2444 x_sql_statement(p_index) := ''''||l_parsed_tbl(l_rec_count).imt_string||''''||'||';
2445
2446 EXIT WHEN l_rec_count = l_parsed_tbl.COUNT;
2447
2448 l_rec_count := l_rec_count + 1;
2449
2450 p_index := p_index + 1;
2451
2452 END LOOP;
2453
2454
2455
2456 -- Remove the concatenation operator from the last element
2457
2458 x_sql_statement(p_index) := SUBSTR(x_sql_statement(p_index),1,LENGTH(x_sql_statement(p_index))-2);
2459
2460 -- Increment the array index count
2461
2462 p_index := p_index + 1;
2463
2464 -- IMT logic ends
2465
2466 x_sql_statement(p_index) := ', 2) > 0 ) ';
2467
2468 p_index := p_index + 1;
2469
2470
2471
2472 END IF;
2473
2474 IF p_application_id <> FND_API.G_MISS_NUM THEN
2475
2476 x_sql_statement(p_index) :=
2477
2478 ' and b.application_id = ' || p_application_id;
2479
2480 p_index := p_index + 1;
2481
2482 END IF;
2483
2484 IF p_external_contents <> FND_API.G_FALSE THEN
2485
2486 x_sql_statement(p_index) :=
2487
2488 ' and b.external_access_flag = '''|| p_external_contents ||'''';
2489
2490 p_index := p_index + 1;
2491
2492 END IF;
2493
2494 x_sql_statement(p_index) :=
2495
2496 ' and nvl(b.effective_start_date, sysdate) <= sysdate+1';
2497
2498 p_index := p_index + 1;
2499
2500 x_sql_statement(p_index) :=
2501
2502 ' and nvl(b.expiration_date, sysdate) >= sysdate';
2503
2504 p_index := p_index + 1;
2505
2506 IF p_days >= 0 THEN
2507
2508 x_sql_statement(p_index) :=
2509
2510 ' and b.last_update_date >= (sysdate - ' || p_days || ' )';
2511
2512 p_index := p_index + 1;
2513
2514 END IF;
2515
2516 IF p_include_chns = FND_API.G_TRUE THEN
2517
2518 x_sql_statement(p_index) := ' and b.item_id = cim.item_id';
2519
2520 p_index := p_index + 1;
2521
2522 IF p_search_level = G_CHANNEL THEN
2523
2524 --commented for bug fix 2719461
2525
2526 --x_sql_statement(p_index) :=
2527
2528 --' and cim.channel_id = id.number_value';
2529
2530 x_sql_statement(p_index) :=
2531
2532 ' and cim.channel_id in ('||l_category_id_list||') ';
2533
2534 p_index := p_index + 1;
2535
2536 ELSIF p_search_level = G_CATEGORY THEN
2537
2538 --commented for bug fix 2719461
2539
2540 --x_sql_statement(p_index) :=
2541
2542 --' and cim.channel_category_id = id.number_value';
2543
2544 x_sql_statement(p_index) :=
2545
2546 ' and cim.channel_category_id in ('||l_category_id_list||') ';
2547
2548 p_index := p_index + 1;
2549
2550 x_sql_statement(p_index) :=
2551
2552 ' and cim.channel_id is null ';
2553
2554 p_index := p_index + 1;
2555
2556 END IF;
2557
2558 x_sql_statement(p_index) :=
2559
2560 ' and cim.approval_status_type = '''|| G_APPROVED ||'''';
2561
2562 p_index := p_index + 1;
2563
2564 x_sql_statement(p_index) :=
2565
2566 ' AND cim.table_name_code = '''||
2567
2568 AMV_UTILITY_PVT.G_TABLE_NAME_CODE ||'''';
2569
2570 p_index := p_index + 1;
2571
2572 x_sql_statement(p_index) :=
2573
2574 ' and cim.available_for_channel_date <= sysdate';
2575
2576 p_index := p_index + 1;
2577
2578 END IF;
2579
2580 --DBMS_OUTPUT.PUT_LINE('Exit : build_items_name_sql ');
2581
2582 EXCEPTION
2583
2584 WHEN OTHERS THEN
2585
2586 x_sql_statement(p_index) := 'ERROR';
2587
2588 --DBMS_OUTPUT.PUT_LINE('OTHERS IN : build_items_name_sql ');
2589
2590 --RAISE;
2591
2592 END build_items_name_sql;
2593
2594 --------------------------------------------------------------------------------
2595
2596 PROCEDURE build_items_file_sql (
2597
2598 p_index IN OUT NOCOPY PLS_INTEGER,
2599
2600 p_imt_string IN VARCHAR2,
2601
2602 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
2603
2604 p_include_chns IN VARCHAR2 := FND_API.G_TRUE,
2605
2606 p_days IN NUMBER,
2607
2608 p_search_level IN VARCHAR2,
2609
2610 p_excluded_flag IN VARCHAR2,
2611
2612 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
2613
2614 x_sql_statement IN OUT NOCOPY DBMS_SQL.VARCHAR2S
2615
2616 )
2617
2618 IS
2619
2620 -- Declare local variables to be used in the parsing logic
2621
2622 l_parsed_tbl parsed_tbl_type;
2623
2624 l_rec_count NUMBER;
2625
2626
2627
2628 --Added as part of code to fix 2719461
2629
2630 --start of code
2631
2632 CURSOR category_id_list IS
2633
2634 select number_value
2635
2636 from amv_temp_numbers;
2637
2638
2639
2640 l_category_counter number :=1;
2641
2642 l_category_id number;
2643
2644 l_category_id_list VARCHAR2(32000);
2645
2646 --end of code
2647
2648 BEGIN
2649
2650 --Added as part of code to fix 2719461
2651
2652 --start of code
2653
2654 OPEN category_id_list;
2655
2656 LOOP
2657
2658 FETCH category_id_list INTO l_category_id;
2659
2660
2661
2662 EXIT WHEN category_id_list%NOTFOUND;
2663
2664
2665
2666 IF l_category_counter = 1 THEN
2667
2668 l_category_id_list := l_category_id;
2669
2670 ELSE
2671
2672 l_category_id_list := l_category_id_list ||','||l_category_id;
2673
2674 END IF;
2675
2676
2677
2678 l_category_counter := l_category_counter +1;
2679
2680
2681
2682 END LOOP;
2683
2684 CLOSE category_id_list;
2685
2686 --DBMS_OUTPUT.PUT_LINE('l_category_id_list ='||l_category_id_list );
2687
2688 --end of code
2689
2690 IF p_imt_string IS NOT NULL THEN
2691
2692 --DBMS_OUTPUT.PUT_LINE('Enter : build_items_file_sql ');
2693
2694 --DBMS_OUTPUT.PUT_LINE('p_excluded_flag : '||p_excluded_flag);
2695
2696 -- Call the Parse_IMT_String
2697
2698 Parse_IMT_String
2699
2700 (p_imt_string => p_imt_string
2701
2702 ,x_parsed_tbl => l_parsed_tbl
2703
2704 );
2705
2706 END IF;
2707
2708 -- file items
2709
2710 x_sql_statement(p_index) := ' select b.item_id';
2711
2712 p_index := p_index + 1;
2713
2714 IF p_search_level = G_CHANNEL THEN
2715
2716 x_sql_statement(p_index) := ', cim.channel_id';
2717
2718 p_index := p_index + 1;
2719
2720 ELSIF p_search_level = G_CATEGORY THEN
2721
2722 x_sql_statement(p_index) := ', cim.channel_category_id';
2723
2724 p_index := p_index + 1;
2725
2726 ELSE
2727
2728 x_sql_statement(p_index) := ', b.item_id';
2729
2730 p_index := p_index + 1;
2731
2732 END IF;
2733
2734 x_sql_statement(p_index) := ', score(1)';
2735
2736 p_index := p_index + 1;
2737
2738 x_sql_statement(p_index) := ' from jtf_amv_items_vl b ';
2739
2740 p_index := p_index + 1;
2741
2742 x_sql_statement(p_index) := ' , jtf_amv_attachments a ';
2743
2744 p_index := p_index + 1;
2745
2746 x_sql_statement(p_index) := ' , fnd_lobs fl ';
2747
2748 p_index := p_index + 1;
2749
2750 IF p_include_chns = FND_API.G_TRUE THEN
2751
2752 x_sql_statement(p_index) := ' , amv_c_chl_item_match cim ';
2753
2754 p_index := p_index + 1;
2755
2756
2757
2758 --commented to fix bug 2719461, this clause is added in where condition as sub query
2759
2760 --x_sql_statement(p_index) := ' , amv_temp_numbers id ';
2761
2762 --p_index := p_index + 1;
2763
2764 END IF;
2765
2766 IF p_excluded_flag = FND_API.G_TRUE THEN
2767
2768 x_sql_statement(p_index) := ' where contains(fl.file_data, ';
2769
2770 p_index := p_index + 1;
2771
2772 -- IMT String Parsing Logic used
2773
2774 l_rec_count := 1;
2775
2776 LOOP
2777
2778 x_sql_statement(p_index) := ''''||l_parsed_tbl(l_rec_count).imt_string||''''||'||';
2779
2780 EXIT WHEN l_rec_count = l_parsed_tbl.COUNT;
2781
2782 l_rec_count := l_rec_count + 1;
2783
2784 p_index := p_index + 1;
2785
2786 END LOOP;
2787
2788
2789
2790 -- Remove the concatenation operator from the last element
2791
2792 x_sql_statement(p_index) := SUBSTR(x_sql_statement(p_index),1,LENGTH(x_sql_statement(p_index))-2);
2793
2794 -- Increment the array index count
2795
2796 p_index := p_index + 1;
2797
2798 -- IMT logic ends
2799
2800 x_sql_statement(p_index) := ', 1) = 0 ';
2801
2802 p_index := p_index + 1;
2803
2804
2805
2806 ELSE
2807
2808 x_sql_statement(p_index) := ' where contains(fl.file_data, ';
2809
2810 p_index := p_index + 1;
2811
2812 -- IMT String Parsing Logic used
2813
2814 l_rec_count := 1;
2815
2816 LOOP
2817
2818 x_sql_statement(p_index) := ''''||l_parsed_tbl(l_rec_count).imt_string||''''||'||';
2819
2820 EXIT WHEN l_rec_count = l_parsed_tbl.COUNT;
2821
2822 l_rec_count := l_rec_count + 1;
2823
2824 p_index := p_index + 1;
2825
2826 END LOOP;
2827
2828
2829
2830 -- Remove the concatenation operator from the last element
2831
2832 x_sql_statement(p_index) := SUBSTR(x_sql_statement(p_index),1,LENGTH(x_sql_statement(p_index))-2);
2833
2834 -- Increment the array index count
2835
2836 p_index := p_index + 1;
2837
2838 -- IMT logic ends
2839
2840 x_sql_statement(p_index) := ', 1) > 0 ';
2841
2842 p_index := p_index + 1;
2843
2844
2845
2846 END IF;
2847
2848 IF p_application_id <> FND_API.G_MISS_NUM THEN
2849
2850 x_sql_statement(p_index) :=
2851
2852 ' and b.application_id = ' || p_application_id;
2853
2854 p_index := p_index + 1;
2855
2856 END IF;
2857
2858 IF p_external_contents <> FND_API.G_FALSE THEN
2859
2860 x_sql_statement(p_index) :=
2861
2862 ' and b.external_access_flag = '''|| p_external_contents ||'''';
2863
2864 p_index := p_index + 1;
2865
2866 END IF;
2867
2868 x_sql_statement(p_index) :=
2869
2870 ' and nvl(b.effective_start_date, sysdate) <= sysdate+1';
2871
2872 p_index := p_index + 1;
2873
2874 x_sql_statement(p_index) :=
2875
2876 ' and nvl(b.expiration_date, sysdate) >= sysdate';
2877
2878 p_index := p_index + 1;
2879
2880 IF p_days >= 0 THEN
2881
2882 x_sql_statement(p_index) :=
2883
2884 ' and b.last_update_date >= (sysdate - ' || p_days || ' )';
2885
2886 p_index := p_index + 1;
2887
2888 END IF;
2889
2890 x_sql_statement(p_index) :=
2891
2892 ' and b.item_id = a.attachment_used_by_id ';
2893
2894 p_index := p_index + 1;
2895
2896 x_sql_statement(p_index) :=
2897
2898 ' and a.attachment_used_by = '''||'ITEM'||'''';
2899
2900 p_index := p_index + 1;
2901
2902 x_sql_statement(p_index) := ' and a.file_id = fl.file_id ';
2903
2904 p_index := p_index + 1;
2905
2906 IF p_include_chns = FND_API.G_TRUE THEN
2907
2908 x_sql_statement(p_index) := ' and b.item_id = cim.item_id';
2909
2910 p_index := p_index + 1;
2911
2912 IF p_search_level = G_CHANNEL THEN
2913
2914 --commented to fix bug 2719461, this clause is added in where condition as sub query just below
2915
2916 --x_sql_statement(p_index) :=
2917
2918 --' and cim.channel_id = id.number_value';
2919
2920 x_sql_statement(p_index) := ' and cim.channel_id in ('||l_category_id_list||') ';
2921
2922 p_index := p_index + 1;
2923
2924 ELSIF p_search_level = G_CATEGORY THEN
2925
2926 --commented to fix bug 2719461, this clause is added in where condition as sub query just below
2927
2928 --x_sql_statement(p_index) :=
2929
2930 --' and cim.channel_category_id = id.number_value';
2931
2932 x_sql_statement(p_index) := ' and cim.channel_category_id in ('||l_category_id_list||') ';
2933
2934 p_index := p_index + 1;
2935
2936 x_sql_statement(p_index) :=
2937
2938 ' and cim.channel_id is null ';
2939
2940 p_index := p_index + 1;
2941
2942 END IF;
2943
2944 x_sql_statement(p_index) :=
2945
2946 ' and cim.approval_status_type = '''|| G_APPROVED ||'''';
2947
2948 p_index := p_index + 1;
2949
2950 x_sql_statement(p_index) :=
2951
2952 ' AND cim.table_name_code = '''||
2953
2954 AMV_UTILITY_PVT.G_TABLE_NAME_CODE ||'''';
2955
2956 p_index := p_index + 1;
2957
2958 x_sql_statement(p_index) :=
2959
2960 ' and cim.available_for_channel_date <= sysdate';
2961
2962 p_index := p_index + 1;
2963
2964 END IF;
2965
2966 --DBMS_OUTPUT.PUT_LINE('Exit : build_items_file_sql ');
2967
2968 EXCEPTION
2969
2970 WHEN OTHERS THEN
2971
2972 x_sql_statement(p_index) := 'ERROR';
2973
2974 --DBMS_OUTPUT.PUT_LINE('OTHERS IN : build_items_file_sql ');
2975
2976 --RAISE;
2977
2978 END build_items_file_sql;
2979
2980 --------------------------------------------------------------------------------
2981
2982 PROCEDURE build_items_text_sql (
2983
2984 p_index IN OUT NOCOPY PLS_INTEGER,
2985
2986 p_imt_string IN VARCHAR2,
2987
2988 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
2989
2990 p_include_chns IN VARCHAR2 := FND_API.G_TRUE,
2991
2992 p_days IN NUMBER,
2993
2994 p_search_level IN VARCHAR2,
2995
2996 p_excluded_flag IN VARCHAR2,
2997
2998 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
2999
3000 x_sql_statement IN OUT NOCOPY DBMS_SQL.VARCHAR2S
3001
3002 )
3003
3004 IS
3005
3006 -- Declare local variables to be used in the parsing logic
3007
3008 l_parsed_tbl parsed_tbl_type;
3009
3010 l_rec_count NUMBER;
3011
3012
3013
3014 --Added as part of code to fix 2719461
3015
3016 --start of code
3017
3018 CURSOR category_id_list IS
3019
3020 select number_value
3021
3022 from amv_temp_numbers;
3023
3024
3025
3026 l_category_counter number :=1;
3027
3028 l_category_id number;
3029
3030 l_category_id_list VARCHAR2(32000);
3031
3032 --end of code
3033
3034
3035
3036 BEGIN
3037
3038 --Added as part of code to fix 2719461
3039
3040 --start of code
3041
3042 OPEN category_id_list;
3043
3044 LOOP
3045
3046 FETCH category_id_list INTO l_category_id;
3047
3048
3049
3050 EXIT WHEN category_id_list%NOTFOUND;
3051
3052
3053
3054 IF l_category_counter = 1 THEN
3055
3056 l_category_id_list := l_category_id;
3057
3058 ELSE
3059
3060 l_category_id_list := l_category_id_list ||','||l_category_id;
3061
3062 END IF;
3063
3064
3065
3066 l_category_counter := l_category_counter +1;
3067
3068
3069
3070 END LOOP;
3071
3072 CLOSE category_id_list;
3073
3074 --DBMS_OUTPUT.PUT_LINE('l_category_id_list ='||l_category_id_list );
3075
3076 --end of code
3077
3078
3079
3080 IF p_imt_string IS NOT NULL THEN
3081
3082 --DBMS_OUTPUT.PUT_LINE('Enter : build_items_text_sql ');
3083
3084 -- Call the Parse_IMT_String
3085
3086 Parse_IMT_String
3087
3088 (p_imt_string => p_imt_string
3089
3090 ,x_parsed_tbl => l_parsed_tbl
3091
3092 );
3093
3094 END IF;
3095
3096 -- text items
3097
3098 x_sql_statement(p_index) := ' select b.item_id';
3099
3100 p_index := p_index + 1;
3101
3102 IF p_search_level = G_CHANNEL THEN
3103
3104 x_sql_statement(p_index) := ', cim.channel_id';
3105
3106 p_index := p_index + 1;
3107
3108 ELSIF p_search_level = G_CATEGORY THEN
3109
3110 x_sql_statement(p_index) := ', cim.channel_category_id';
3111
3112 p_index := p_index + 1;
3113
3114 ELSE
3115
3116 x_sql_statement(p_index) := ', b.item_id';
3117
3118 p_index := p_index + 1;
3119
3120 END IF;
3121
3122 x_sql_statement(p_index) := ', score(1)';
3123
3124 p_index := p_index + 1;
3125
3126 x_sql_statement(p_index) := ' from jtf_amv_items_vl b ';
3127
3128 p_index := p_index + 1;
3129
3130 IF p_include_chns = FND_API.G_TRUE THEN
3131
3132 x_sql_statement(p_index) := ' , amv_c_chl_item_match cim ';
3133
3134 p_index := p_index + 1;
3135
3136 --commented 2 lines to fix 2719461, the lines are added as subquery in join condition in where clause
3137
3138 --x_sql_statement(p_index) := ' , amv_temp_numbers id ';
3139
3140 --p_index := p_index + 1;
3141
3142 END IF;
3143
3144 IF p_excluded_flag = FND_API.G_TRUE THEN
3145
3146 x_sql_statement(p_index) := ' where contains(b.text_string, ';
3147
3148 p_index := p_index + 1;
3149
3150 -- IMT String Parsing Logic used
3151
3152 l_rec_count := 1;
3153
3154 LOOP
3155
3156 x_sql_statement(p_index) := ''''||l_parsed_tbl(l_rec_count).imt_string||''''||'||';
3157
3158 EXIT WHEN l_rec_count = l_parsed_tbl.COUNT;
3159
3160 l_rec_count := l_rec_count + 1;
3161
3162 p_index := p_index + 1;
3163
3164 END LOOP;
3165
3166
3167
3168 -- Remove the concatenation operator from the last element
3169
3170 x_sql_statement(p_index) := SUBSTR(x_sql_statement(p_index),1,LENGTH(x_sql_statement(p_index))-2);
3171
3172 -- Increment the array index count
3173
3174 p_index := p_index + 1;
3175
3176 -- IMT logic ends
3177
3178 x_sql_statement(p_index) := ', 1) = 0';
3179
3180 p_index := p_index + 1;
3181
3182
3183
3184 ELSE
3185
3186 x_sql_statement(p_index) := ' where contains(b.text_string, ';
3187
3188 p_index := p_index + 1;
3189
3190 -- IMT String Parsing Logic used
3191
3192 l_rec_count := 1;
3193
3194 LOOP
3195
3196 x_sql_statement(p_index) := ''''||l_parsed_tbl(l_rec_count).imt_string||''''||'||';
3197
3198 EXIT WHEN l_rec_count = l_parsed_tbl.COUNT;
3199
3200 l_rec_count := l_rec_count + 1;
3201
3202 p_index := p_index + 1;
3203
3204 END LOOP;
3205
3206
3207
3208 -- Remove the concatenation operator from the last element
3209
3210 x_sql_statement(p_index) := SUBSTR(x_sql_statement(p_index),1,LENGTH(x_sql_statement(p_index))-2);
3211
3212 -- Increment the array index count
3213
3214 p_index := p_index + 1;
3215
3216 -- IMT logic ends
3217
3218 x_sql_statement(p_index) := ', 1) > 0';
3219
3220 p_index := p_index + 1;
3221
3222
3223
3224 END IF;
3225
3226 IF p_application_id <> FND_API.G_MISS_NUM THEN
3227
3228 x_sql_statement(p_index) :=
3229
3230 ' and b.application_id = ' || p_application_id;
3231
3232 p_index := p_index + 1;
3233
3234 END IF;
3235
3236 IF p_external_contents <> FND_API.G_FALSE THEN
3237
3238 x_sql_statement(p_index) :=
3239
3240 ' and b.external_access_flag = '''|| p_external_contents ||'''';
3241
3242 p_index := p_index + 1;
3243
3244 END IF;
3245
3246 x_sql_statement(p_index) :=
3247
3248 ' and nvl(b.effective_start_date, sysdate) <= sysdate+1';
3249
3250 p_index := p_index + 1;
3251
3252 x_sql_statement(p_index) :=
3253
3254 ' and nvl(b.expiration_date, sysdate) >= sysdate';
3255
3256 p_index := p_index + 1;
3257
3258 IF p_days >= 0 THEN
3259
3260 x_sql_statement(p_index) :=
3261
3262 ' and b.last_update_date >= (sysdate - ' || p_days || ' )';
3263
3264 p_index := p_index + 1;
3265
3266 END IF;
3267
3268 IF p_include_chns = FND_API.G_TRUE THEN
3269
3270 x_sql_statement(p_index) := ' and b.item_id = cim.item_id';
3271
3272 p_index := p_index + 1;
3273
3274 IF p_search_level = G_CHANNEL THEN
3275
3276 --commented to fix bug 2719461
3277
3278 --x_sql_statement(p_index) :=
3279
3280 --' and cim.channel_id = id.number_value';
3281
3282 x_sql_statement(p_index) := ' and cim.channel_id in ( '||l_category_id_list ||') ';
3283
3284 p_index := p_index + 1;
3285
3286 ELSIF p_search_level = G_CATEGORY THEN
3287
3288 --commented to fix bug 2719461
3289
3290 --x_sql_statement(p_index) :=
3291
3292 --' and cim.channel_category_id = id.number_value';
3293
3294 x_sql_statement(p_index) := ' and cim.channel_category_id in ( '||l_category_id_list ||') ';
3295
3296 p_index := p_index + 1;
3297
3298 x_sql_statement(p_index) :=
3299
3300 ' and cim.channel_id is null ';
3301
3302 p_index := p_index + 1;
3303
3304 END IF;
3305
3306 x_sql_statement(p_index) :=
3307
3308 ' and cim.approval_status_type = '''|| G_APPROVED ||'''';
3309
3310 p_index := p_index + 1;
3311
3312 x_sql_statement(p_index) :=
3313
3314 ' AND cim.table_name_code = '''||
3315
3316 AMV_UTILITY_PVT.G_TABLE_NAME_CODE ||'''';
3317
3318 p_index := p_index + 1;
3319
3320 x_sql_statement(p_index) :=
3321
3322 ' and cim.available_for_channel_date <= sysdate';
3323
3324 p_index := p_index + 1;
3325
3326 END IF;
3327
3328 --DBMS_OUTPUT.PUT_LINE('Exit : build_items_text_sql ');
3329
3330 EXCEPTION
3331
3332 WHEN OTHERS THEN
3333
3334 x_sql_statement(p_index) := 'ERROR';
3335
3336 --DBMS_OUTPUT.PUT_LINE('OTHERS IN : build_items_text_sql ');
3337
3338 --RAISE;
3339
3340 END build_items_text_sql;
3341
3342 --------------------------------------------------------------------------------
3343
3344 PROCEDURE build_items_url_sql (
3345
3346 p_index IN OUT NOCOPY PLS_INTEGER,
3347
3348 p_imt_string IN VARCHAR2,
3349
3350 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
3351
3352 p_include_chns IN VARCHAR2 := FND_API.G_TRUE,
3353
3354 p_days IN NUMBER,
3355
3356 p_search_level IN VARCHAR2,
3357
3358 p_excluded_flag IN VARCHAR2,
3359
3360 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
3361
3362 x_sql_statement IN OUT NOCOPY DBMS_SQL.VARCHAR2S
3363
3364 )
3365
3366 IS
3367
3368 -- Declare local variables to be used in the parsing logic
3369
3370 l_parsed_tbl parsed_tbl_type;
3371
3372 l_rec_count NUMBER;
3373
3374
3375
3376 --Added as part of code to fix 2719461
3377
3378 --start of code
3379
3380 CURSOR category_id_list IS
3381
3382 select number_value
3383
3384 from amv_temp_numbers;
3385
3386
3387
3388 l_category_counter number :=1;
3389
3390 l_category_id number;
3391
3392 l_category_id_list VARCHAR2(32000);
3393
3394 --end of code
3395
3396
3397
3398 BEGIN
3399
3400 --Added as part of code to fix 2719461
3401
3402 --start of code
3403
3404 OPEN category_id_list;
3405
3406 LOOP
3407
3408 FETCH category_id_list INTO l_category_id;
3409
3410
3411
3412 EXIT WHEN category_id_list%NOTFOUND;
3413
3414
3415
3416 IF l_category_counter = 1 THEN
3417
3418 l_category_id_list := l_category_id;
3419
3420 ELSE
3421
3422 l_category_id_list := l_category_id_list ||','||l_category_id;
3423
3424 END IF;
3425
3426
3427
3428 l_category_counter := l_category_counter +1;
3429
3430
3431
3432 END LOOP;
3433
3434 CLOSE category_id_list;
3435
3436 --DBMS_OUTPUT.PUT_LINE('l_category_id_list ='||l_category_id_list );
3437
3438 --end of code
3439
3440
3441
3442 IF p_imt_string IS NOT NULL THEN
3443
3444 --DBMS_OUTPUT.PUT_LINE('Enter : build_items_url_sql ');
3445
3446 -- Call the Parse_IMT_String
3447
3448 Parse_IMT_String
3449
3450 (p_imt_string => p_imt_string
3451
3452 ,x_parsed_tbl => l_parsed_tbl
3453
3454 );
3455
3456 END IF;
3457
3458 -- url items
3459
3460 x_sql_statement(p_index) := ' select b.item_id';
3461
3462 p_index := p_index + 1;
3463
3464 IF p_search_level = G_CHANNEL THEN
3465
3466 x_sql_statement(p_index) := ', cim.channel_id';
3467
3468 p_index := p_index + 1;
3469
3470 ELSIF p_search_level = G_CATEGORY THEN
3471
3472 x_sql_statement(p_index) := ', cim.channel_category_id';
3473
3474 p_index := p_index + 1;
3475
3476 ELSE
3477
3478 x_sql_statement(p_index) := ', b.item_id';
3479
3480 p_index := p_index + 1;
3481
3482 END IF;
3483
3484 x_sql_statement(p_index) := ', score(1)';
3485
3486 p_index := p_index + 1;
3487
3488 x_sql_statement(p_index) := ' from jtf_amv_items_vl b ';
3489
3490 p_index := p_index + 1;
3491
3492 IF p_include_chns = FND_API.G_TRUE THEN
3493
3494 x_sql_statement(p_index) := ' , amv_c_chl_item_match cim ';
3495
3496 p_index := p_index + 1;
3497
3498 --commented to fix bug 2719461, this is added as subquery to where clase in the join
3499
3500 --x_sql_statement(p_index) := ' , amv_temp_numbers id ';
3501
3502 --p_index := p_index + 1;
3503
3504 END IF;
3505
3506 IF p_excluded_flag = FND_API.G_TRUE THEN
3507
3508 x_sql_statement(p_index) := ' where contains(b.url_string, ';
3509
3510 p_index := p_index + 1;
3511
3512 -- IMT String Parsing Logic used
3513
3514 l_rec_count := 1;
3515
3516 LOOP
3517
3518 x_sql_statement(p_index) := ''''||l_parsed_tbl(l_rec_count).imt_string||''''||'||';
3519
3520 EXIT WHEN l_rec_count = l_parsed_tbl.COUNT;
3521
3522 l_rec_count := l_rec_count + 1;
3523
3524 p_index := p_index + 1;
3525
3526 END LOOP;
3527
3528
3529
3530 -- Remove the concatenation operator from the last element
3531
3532 x_sql_statement(p_index) := SUBSTR(x_sql_statement(p_index),1,LENGTH(x_sql_statement(p_index))-2);
3533
3534 -- Increment the array index count
3535
3536 p_index := p_index + 1;
3537
3538 -- IMT logic ends
3539
3540 x_sql_statement(p_index) := ', 1) = 0 ';
3541
3542 p_index := p_index + 1;
3543
3544
3545
3546 ELSE
3547
3548 x_sql_statement(p_index) := ' where contains(b.url_string, ';
3549
3550 p_index := p_index + 1;
3551
3552 -- IMT String Parsing Logic used
3553
3554 l_rec_count := 1;
3555
3556 LOOP
3557
3558 x_sql_statement(p_index) := ''''||l_parsed_tbl(l_rec_count).imt_string||''''||'||';
3559
3560 EXIT WHEN l_rec_count = l_parsed_tbl.COUNT;
3561
3562 l_rec_count := l_rec_count + 1;
3563
3564 p_index := p_index + 1;
3565
3566 END LOOP;
3567
3568
3569
3570 -- Remove the concatenation operator from the last element
3571
3572 x_sql_statement(p_index) := SUBSTR(x_sql_statement(p_index),1,LENGTH(x_sql_statement(p_index))-2);
3573
3574 -- Increment the array index count
3575
3576 p_index := p_index + 1;
3577
3578 -- IMT logic ends
3579
3580 x_sql_statement(p_index) := ', 1) > 0 ';
3581
3582 p_index := p_index + 1;
3583
3584
3585
3586 END IF;
3587
3588 IF p_application_id <> FND_API.G_MISS_NUM THEN
3589
3590 x_sql_statement(p_index) :=
3591
3592 ' and b.application_id = ' || p_application_id;
3593
3594 p_index := p_index + 1;
3595
3596 END IF;
3597
3598 IF p_external_contents <> FND_API.G_FALSE THEN
3599
3600 x_sql_statement(p_index) :=
3601
3602 ' and b.external_access_flag = '''|| p_external_contents ||'''';
3603
3604 p_index := p_index + 1;
3605
3606 END IF;
3607
3608 x_sql_statement(p_index) :=
3609
3610 ' and nvl(b.effective_start_date, sysdate) <= sysdate+1';
3611
3612 p_index := p_index + 1;
3613
3614 x_sql_statement(p_index) :=
3615
3616 ' and nvl(b.expiration_date, sysdate) >= sysdate';
3617
3618 p_index := p_index + 1;
3619
3620 IF p_days >= 0 THEN
3621
3622 x_sql_statement(p_index) :=
3623
3624 ' and b.last_update_date >= (sysdate - ' || p_days || ' )';
3625
3626 p_index := p_index + 1;
3627
3628 END IF;
3629
3630 IF p_include_chns = FND_API.G_TRUE THEN
3631
3632 x_sql_statement(p_index) := ' and b.item_id = cim.item_id';
3633
3634 p_index := p_index + 1;
3635
3636 IF p_search_level = G_CHANNEL THEN
3637
3638 --commented to fix bug 2719461
3639
3640 --x_sql_statement(p_index) :=
3641
3642 --' and cim.channel_id = id.number_value';
3643
3644 x_sql_statement(p_index) := ' and cim.channel_id in ( '||l_category_id_list ||') ';
3645
3646 p_index := p_index + 1;
3647
3648 ELSIF p_search_level = G_CATEGORY THEN
3649
3650 --commented to fix bug 2719461
3651
3652 --x_sql_statement(p_index) :=
3653
3654 --' and cim.channel_category_id = id.number_value';
3655
3656 x_sql_statement(p_index) := ' and cim.channel_category_id in ( '||l_category_id_list ||') ';
3657
3658 p_index := p_index + 1;
3659
3660 x_sql_statement(p_index) :=
3661
3662 ' and cim.channel_id is null ';
3663
3664 p_index := p_index + 1;
3665
3666 END IF;
3667
3668 x_sql_statement(p_index) :=
3669
3670 ' and cim.approval_status_type = '''|| G_APPROVED ||'''';
3671
3672 p_index := p_index + 1;
3673
3674 x_sql_statement(p_index) :=
3675
3676 ' AND cim.table_name_code = '''||
3677
3678 AMV_UTILITY_PVT.G_TABLE_NAME_CODE ||'''';
3679
3680 p_index := p_index + 1;
3681
3682 x_sql_statement(p_index) :=
3683
3684 ' and cim.available_for_channel_date <= sysdate';
3685
3686 p_index := p_index + 1;
3687
3688 END IF;
3689
3690 --DBMS_OUTPUT.PUT_LINE('Exit : build_items_url_sql ');
3691
3692 --DBMS_OUTPUT.PUT_LINE('Exit : build_items_url_sql ');
3693
3694 EXCEPTION
3695
3696 WHEN OTHERS THEN
3697
3698 x_sql_statement(p_index) := 'ERROR';
3699
3700 --DBMS_OUTPUT.PUT_LINE('OTHERS IN : build_items_url_sql ');
3701
3702 --RAISE;
3703
3704 END build_items_url_sql;
3705
3706 --------------------------------------------------------------------------------
3707
3708 --------------------------------------------------------------------------------
3709
3710 PROCEDURE get_user_accessable_channels(
3711
3712 p_user_id IN NUMBER,
3713
3714 p_application_id IN NUMBER,
3715
3716 x_channel_array OUT NOCOPY AMV_NUMBER_VARRAY_TYPE)
3717
3718 IS
3719
3720
3721
3722 l_record_count number := 1;
3723
3724 l_channel_id number;
3725
3726
3727
3728 -- get the list of public channels user has access to
3729
3730 CURSOR get_pub_channels IS
3731
3732 select b.channel_id
3733
3734 from amv_c_channels_vl b
3735
3736 where b.channel_type = G_CONTENT
3737
3738 and b.access_level_type = G_PUBLIC
3739
3740 and b.application_id = p_application_id
3741
3742 UNION ALL
3743
3744 select b.channel_id
3745
3746 from amv_c_channels_vl b
3747
3748 where b.channel_type = G_PRIVATE
3749
3750 and b.owner_user_id = p_user_id
3751
3752 UNION ALL
3753
3754 select b.channel_id
3755
3756 from amv_c_channels_vl b
3757
3758 , amv_u_access au
3759
3760 , jtf_rs_group_members_vl jgm
3761
3762 where b.channel_type = G_GROUP
3763
3764 and b.channel_id = au.access_to_table_record_id
3765
3766 and au.access_to_table_code = G_CHANNEL
3767
3768 and au.user_or_group_type = G_GROUP
3769
3770 and au.user_or_group_id = jgm.group_id
3771
3772 and jgm.resource_id = p_user_id;
3773
3774
3775
3776 BEGIN
3777
3778 --DBMS_OUTPUT.PUT_LINE('Enter : get_user_accessable_channels' );
3779
3780 -- return all public channels owned by application
3781
3782 -- and all channels owner by user
3783
3784 -- and all group channels user belongs to
3785
3786 x_channel_array := amv_number_varray_type();
3787
3788
3789
3790 OPEN get_pub_channels;
3791
3792 LOOP
3793
3794 FETCH get_pub_channels INTO l_channel_id;
3795
3796 EXIT WHEN get_pub_channels%NOTFOUND;
3797
3798 x_channel_array.extend;
3799
3800 x_channel_array(l_record_count) := l_channel_id;
3801
3802 l_record_count := l_record_count + 1;
3803
3804 END LOOP;
3805
3806 CLOSE get_pub_channels;
3807
3808 --DBMS_OUTPUT.PUT_LINE('Exit : get_user_accessable_channels' );
3809
3810 EXCEPTION
3811
3812 WHEN OTHERS THEN
3813
3814 l_record_count := 0;
3815
3816 --DBMS_OUTPUT.PUT_LINE('Others : get_user_accessable_channels' );
3817
3818 --RAISE;
3819
3820 END get_user_accessable_channels;
3821
3822 --------------------------------------------------------------------------------
3823
3824 --------------------------------------------------------------------------------
3825
3826 PROCEDURE get_app_categories(
3827
3828 p_application_id IN NUMBER,
3829
3830 x_category_array OUT NOCOPY AMV_NUMBER_VARRAY_TYPE)
3831
3832 IS
3833
3834 l_record_count number := 0;
3835
3836 l_category_id number;
3837
3838 l_category_name varchar2(100);
3839
3840
3841
3842 CURSOR channel_category_csr IS
3843
3844 select channel_category_id
3845
3846 , channel_category_name
3847
3848 from amv_c_categories_vl
3849
3850 where application_id = p_application_id;
3851
3852 --where channel_category_name not in ('AMV_GROUP','AMV_PRIVATE')
3853
3854
3855
3856 BEGIN
3857
3858 --DBMS_OUTPUT.PUT_LINE('Enter : get_app_categories' );
3859
3860 x_category_array := amv_number_varray_type();
3861
3862 OPEN channel_category_csr;
3863
3864 LOOP
3865
3866 FETCH channel_category_csr INTO l_category_id, l_category_name;
3867
3868 EXIT WHEN channel_category_csr%NOTFOUND;
3869
3870 IF l_category_name not in ('AMV_GROUP', 'AMV_PRIVATE') THEN
3871
3872 l_record_count := l_record_count + 1;
3873
3874 x_category_array.extend;
3875
3876 x_category_array(l_record_count) := l_category_id;
3877
3878 END IF;
3879
3880 END LOOP;
3881
3882 CLOSE channel_category_csr;
3883
3884 --DBMS_OUTPUT.PUT_LINE('Exit : get_app_categories' );
3885
3886 EXCEPTION
3887
3888 WHEN OTHERS THEN
3889
3890 l_record_count := 0;
3891
3892 --DBMS_OUTPUT.PUT_LINE('Others : get_app_categories' );
3893
3894 --RAISE;
3895
3896 END get_app_categories;
3897
3898 --------------------------------------------------------------------------------
3899
3900 --------------------------------------------------------------------------------
3901
3902 PROCEDURE get_category_channel (
3903
3904 p_category_id IN AMV_NUMBER_VARRAY_TYPE,
3905
3906 p_application_id IN NUMBER,
3907
3908 p_include_subcats IN VARCHAR2,
3909
3910 x_category_array OUT NOCOPY AMV_NUMBER_VARRAY_TYPE,
3911
3912 x_channel_array OUT NOCOPY AMV_NUMBER_VARRAY_TYPE)
3913
3914 IS
3915
3916 l_api_version CONSTANT NUMBER := 1.0;
3917
3918 l_validation_level number := 0;
3919
3920 l_return_status varchar2(1);
3921
3922 l_msg_count number;
3923
3924 l_msg_data varchar2(400);
3925
3926 l_cat_count number := 0;
3927
3928 l_chn_count number := 0;
3929
3930 l_categoryhr_array amv_category_pvt.amv_cat_hierarchy_varray_type;
3931
3932 l_channelhr_array amv_category_pvt.amv_cat_hierarchy_varray_type;
3933
3934 l_temp_id number := 100;
3935
3936 l_category_id amv_number_varray_type;
3937
3938 BEGIN
3939
3940 --DBMS_OUTPUT.PUT_LINE('Enter : get_category_channel' );
3941
3942 l_category_id := amv_number_varray_type();
3943
3944 x_category_array := amv_number_varray_type();
3945
3946 x_channel_array := amv_number_varray_type();
3947
3948
3949
3950 IF p_category_id.count = 0 THEN
3951
3952 get_app_categories( p_application_id => p_application_id,
3953
3954 x_category_array => l_category_id);
3955
3956 ELSE
3957
3958 l_category_id := p_category_id;
3959
3960 END IF;
3961
3962
3963
3964 FOR i in 1..l_category_id.count LOOP
3965
3966 IF AMV_UTILITY_PVT.Is_CategoryIdValid(l_category_id(i)) THEN
3967
3968 IF p_include_subcats = FND_API.G_FALSE THEN
3969
3970 l_cat_count := l_cat_count + 1;
3971
3972 x_category_array.extend;
3973
3974 x_category_array(l_cat_count) := l_category_id(i);
3975
3976 ELSE
3977
3978 AMV_CATEGORY_PVT.Get_CatChildrenHierarchy(
3979
3980 P_API_VERSION => l_api_version,
3981
3982 P_INIT_MSG_LIST => FND_API.G_FALSE,
3983
3984 P_VALIDATION_LEVEL => l_validation_level,
3985
3986 X_RETURN_STATUS => l_return_status,
3987
3988 X_MSG_COUNT => l_msg_count,
3989
3990 X_MSG_DATA => l_msg_data,
3991
3992 P_CHECK_LOGIN_USER => FND_API.G_FALSE,
3993
3994 P_CATEGORY_ID => l_category_id(i),
3995
3996 X_CATEGORY_HIERARCHY => l_categoryhr_array );
3997
3998
3999
4000 FOR i in 1..l_categoryhr_array.count LOOP
4001
4002 l_cat_count := l_cat_count + 1;
4003
4004 x_category_array.extend;
4005
4006 x_category_array(l_cat_count) := l_categoryhr_array(i).id;
4007
4008 END LOOP;
4009
4010 END IF;
4011
4012
4013
4014 AMV_CATEGORY_PVT.Get_ChannelsPerCategory(
4015
4016 P_API_VERSION => l_api_version,
4017
4018 P_INIT_MSG_LIST => FND_API.G_FALSE,
4019
4020 P_VALIDATION_LEVEL => l_validation_level,
4021
4022 X_RETURN_STATUS => l_return_status,
4023
4024 X_MSG_COUNT => l_msg_count,
4025
4026 X_MSG_DATA => l_msg_data,
4027
4028 P_CHECK_LOGIN_USER => FND_API.G_FALSE,
4029
4030 P_CATEGORY_ID => l_category_id(i),
4031
4032 P_INCLUDE_SUBCATS => p_include_subcats,
4033
4034 X_CONTENT_CHAN_ARRAY => l_channelhr_array );
4035
4036 IF l_channelhr_array.count > 0 THEN
4037
4038 FOR i in 1..l_channelhr_array.count LOOP
4039
4040 l_chn_count := l_chn_count + 1;
4041
4042 x_channel_array.extend;
4043
4044 x_channel_array(l_chn_count) := l_channelhr_array(i).id;
4045
4046 END LOOP;
4047
4048 END IF;
4049
4050 END IF;
4051
4052 END LOOP;
4053
4054 --DBMS_OUTPUT.PUT_LINE('Exit : get_category_channel' );
4055
4056 EXCEPTION
4057
4058 WHEN OTHERS THEN
4059
4060 l_temp_id := 0;
4061
4062 --DBMS_OUTPUT.PUT_LINE('Others : get_category_channel' );
4063
4064 --RAISE;
4065
4066 END;
4067
4068 --------------------------------------------------------------------------------
4069
4070 --------------------------------------------------------------------------------
4071
4072 PROCEDURE insert_temp_numbers(p_id_array IN AMV_NUMBER_VARRAY_TYPE,
4073
4074 x_status OUT NOCOPY VARCHAR2)
4075
4076 IS
4077
4078 l_stmt varchar2(200) :='INSERT INTO amv_temp_numbers (number_value) VALUES (:id)';
4079
4080 -- Added for Schmema swap changes
4081 l_status varchar2(30);
4082 l_schema varchar2(30);
4083 l_industry varchar2(30);
4084 l_return_status boolean;
4085
4086 BEGIN
4087
4088 l_return_status := FND_INSTALLATION.get_app_info('AMV',l_status,l_industry,l_schema);
4089 --DBMS_OUTPUT.PUT_LINE('Enter : insert_temp_numbers' );
4090
4091 -- delete channels from temp table
4092
4093 --TRUNCATE also empties the session entries into amv_temp_ids
4094
4095 --Since temp tables are empty at beginning of session, then
4096
4097 -- amv_temp_ids must also be empty after TRUNCATE
4098
4099 l_return_status := FND_INSTALLATION.get_app_info('AMV',l_status,l_industry,l_schema);
4100 --EXECUTE IMMEDIATE 'TRUNCATE TABLE amv.amv_temp_numbers';
4101
4102 EXECUTE IMMEDIATE 'TRUNCATE TABLE amv_temp_numbers';
4103
4104
4105
4106 -- build ids insert statement
4107
4108 FOR i in 1..p_id_array.count LOOP
4109
4110 --DBMS_OUTPUT.PUT_LINE('insert into amv_temp_numbers (number_value) values (' || p_id_array(i) ||');');
4111
4112 EXECUTE IMMEDIATE l_stmt USING p_id_array(i);
4113
4114 END LOOP;
4115
4116
4117
4118 x_status := FND_API.G_TRUE;
4119
4120 --
4121
4122 --DBMS_OUTPUT.PUT_LINE('Exit : insert_temp_numbers' );
4123
4124 EXCEPTION
4125
4126 WHEN OTHERS THEN
4127
4128 x_status := FND_API.G_FALSE;
4129
4130 --DBMS_OUTPUT.PUT_LINE('Others : insert_temp_numbers' );
4131
4132 --RAISE;
4133
4134 END insert_temp_numbers;
4135
4136 --
4137
4138 --------------------------------------------------------------------------------
4139
4140 PROCEDURE insert_temp_ids(p_stmt IN OUT NOCOPY DBMS_SQL.VARCHAR2S,
4141
4142 p_start_index IN PLS_INTEGER,
4143
4144 p_end_index IN PLS_INTEGER,
4145
4146 x_status OUT NOCOPY VARCHAR2)
4147
4148 IS
4149
4150 l_ins_stmt varchar2(100) := 'INSERT INTO amv_temp_ids (id,number_value,score) ';
4151
4152 l_rows_processed PLS_INTEGER;
4153
4154 l_cursor_id PLS_INTEGER;
4155
4156
4157
4158 BEGIN
4159
4160 --DBMS_OUTPUT.PUT_LINE('Enter : insert_temp_ids' );
4161
4162
4163
4164
4165
4166 --DBMS_OUTPUT.PUT_LINE('start index:' || p_start_index || 'end index: ' ||
4167
4168 -- p_end_index);
4169
4170 /*
4171
4172 FOR i IN p_start_index..(p_end_index-1) LOOP
4173
4174 DBMS_OUTPUT.PUT_LINE(p_stmt(i));
4175
4176 END LOOP;
4177
4178 DBMS_OUTPUT.PUT_LINE('-------------------------');
4179
4180 */
4181
4182
4183
4184
4185
4186
4187
4188 -- clear the temporary global table
4189
4190 --This is commented OUT NOCOPY because truncation empties all
4191
4192 -- temporary table for session, including amv_temp_numbers
4193
4194 -- which causes search result to be empty.
4195
4196 --EXECUTE IMMEDIATE 'TRUNCATE TABLE amv.amv_temp_ids';
4197
4198
4199
4200 p_stmt(p_start_index - 1) := l_ins_stmt;
4201
4202
4203
4204 -- prepare a cursor for getting the results
4205
4206 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
4207
4208
4209
4210 -- parse dbms sql
4211
4212 DBMS_SQL.PARSE( l_cursor_id, -- Cursor identifier
4213
4214 p_stmt, -- SQL statement in VARCHAR2S table
4215
4216 p_start_index-1, -- Index to first row of statement
4217
4218 p_end_index-1, -- Index to last row of statement
4219
4220 TRUE, -- Insert linefeed for each row
4221
4222 DBMS_SQL.NATIVE);
4223
4224
4225
4226 -- execute dbms_sql
4227
4228 l_rows_processed := DBMS_SQL.EXECUTE(l_cursor_id);
4229
4230 --DBMS_OUTPUT.PUT_LINE('# Rows Executed:' || l_rows_processed);
4231
4232
4233
4234 -- close cursor
4235
4236 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
4237
4238
4239
4240 x_status := FND_API.G_TRUE;
4241
4242 --DBMS_OUTPUT.PUT_LINE('Exit : insert_temp_ids' );
4243
4244 EXCEPTION
4245
4246
4247
4248 WHEN OTHERS THEN
4249
4250 x_status := FND_API.G_FALSE;
4251
4252 --DBMS_OUTPUT.PUT_LINE('Others : insert_temp_ids' );
4253
4254 --DBMS_OUTPUT.PUT_LINE('Exception : insert_temp_ids' );
4255
4256 --DBMS_OUTPUT.PUT_LINE('ERROR IN Here - '||SQLCODE||':'||SQLERRM );
4257
4258 RAISE;
4259
4260 END insert_temp_ids;
4261
4262 --
4263
4264 --------------------------------------------------------------------------------
4265
4266 PROCEDURE populate_channel_results (
4267
4268 p_results_requested IN NUMBER,
4269
4270 x_start_with IN OUT NOCOPY NUMBER,
4271
4272 x_results_array IN OUT NOCOPY AMV_SEARCHRES_VARRAY_TYPE,
4273
4274 x_results_populated IN OUT NOCOPY NUMBER,
4275
4276 x_total_results IN OUT NOCOPY NUMBER)
4277
4278 IS
4279
4280 l_total_count number := 1;
4281
4282 l_channel_id number;
4283
4284 l_last_update_date date;
4285
4286 l_channel_name varchar2(80);
4287
4288 l_description varchar2(2000);
4289
4290 l_score number;
4291
4292 l_null varchar2(1) := null;
4293
4294 l_temp_total number := 0;
4295
4296 l_start_with number;
4297
4298
4299
4300 l_id number;
4301
4302 l_name varchar2(80);
4303
4304
4305
4306 CURSOR channels_csr IS
4307
4308 select chn.channel_id
4309
4310 , chn.last_update_date
4311
4312 , chn.channel_name
4313
4314 , chn.description
4315
4316 , tmp.number_value
4317
4318 , max(tmp.score)
4319
4320 from amv_c_channels_vl chn
4321
4322 , amv_temp_ids tmp
4323
4324 where chn.channel_id = tmp.id
4325
4326 group by chn.channel_id, chn.last_update_date, chn.channel_name, chn.description, tmp.number_value
4327
4328 order by max(tmp.score) desc;
4329
4330
4331
4332 CURSOR channel_count_csr IS
4333
4334 select sum(count(distinct id))
4335
4336 from amv_temp_ids
4337
4338 group by number_value;
4339
4340
4341
4342 CURSOR category_chn_csr IS
4343
4344 select channel_category_id
4345
4346 , channel_name
4347
4348 from amv_c_channels_vl
4349
4350 where channel_id = l_id;
4351
4352
4353
4354 BEGIN
4355
4356 --DBMS_OUTPUT.PUT_LINE('Enter : populate_channel_results' );
4357
4358 OPEN channel_count_csr;
4359
4360 FETCH channel_count_csr INTO l_temp_total;
4361
4362 x_total_results := x_total_results + nvl(l_temp_total,0);
4363
4364 CLOSE channel_count_csr;
4365
4366
4367
4368 l_start_with := x_start_with;
4369
4370 IF x_start_with <= x_total_results THEN
4371
4372 OPEN channels_csr;
4373
4374 LOOP
4375
4376 FETCH channels_csr INTO l_channel_id,
4377
4378 l_last_update_date,
4379
4380 l_channel_name,
4381
4382 l_description,
4383
4384 l_id,
4385
4386 l_score;
4387
4388 EXIT WHEN channels_csr%NOTFOUND;
4389
4390 IF ( x_start_with <= l_total_count AND
4391
4392 x_results_populated < p_results_requested)
4393
4394 THEN
4395
4396 OPEN category_chn_csr;
4397
4398 FETCH category_chn_csr INTO l_id, l_name;
4399
4400 CLOSE category_chn_csr;
4401
4402 x_results_populated := x_results_populated + 1;
4403
4404 x_results_array.extend;
4405
4406 x_results_array(x_results_populated).title := l_channel_name;
4407
4408 x_results_array(x_results_populated).url_string :=
4409
4410 'amvnvctd.jsp?chnid='||l_channel_id;
4411
4412 x_results_array(x_results_populated).description :=
4413
4414 l_description;
4415
4416 x_results_array(x_results_populated).score :=l_score;
4417
4418 x_results_array(x_results_populated).area_id := l_channel_id;
4419
4420 x_results_array(x_results_populated).area_code := G_CHANNEL;
4421
4422 x_results_array(x_results_populated).user1 :=
4423
4424 to_char(l_last_update_date, 'DD-MON-YYYY');
4425
4426 x_results_array(x_results_populated).user2 := l_id;
4427
4428 x_results_array(x_results_populated).user3 := G_CATEGORY;
4429
4430 /*
4431
4432 x_results_array(x_results_populated) :=
4433
4434 amv_searchres_obj_type(
4435
4436 l_channel_name,
4437
4438 'amvnvctd.jsp?chnid='||l_channel_id,
4439
4440 l_description,
4441
4442 l_score,
4443
4444 l_channel_id,
4445
4446 G_CHANNEL,
4447
4448 to_char(l_last_update_date, 'DD-MON-YYYY'),
4449
4450 l_id,
4451
4452 G_CATEGORY);
4453
4454 */
4455
4456 END IF;
4457
4458 IF (x_results_populated >= p_results_requested) THEN
4459
4460 exit;
4461
4462 END IF;
4463
4464 l_total_count := l_total_count + 1;
4465
4466 END LOOP;
4467
4468 CLOSE channels_csr;
4469
4470 -- reset the start index to 1 for next loops
4471
4472 x_start_with := 1;
4473
4474 ELSE
4475
4476 -- decrement the start index with the number of results skipped
4477
4478 x_start_with := l_start_with - x_total_results;
4479
4480 END IF;
4481
4482 --DBMS_OUTPUT.PUT_LINE('Exit : populate_channel_results' );
4483
4484 EXCEPTION
4485
4486 WHEN OTHERS THEN
4487
4488 l_temp_total := 0;
4489
4490 --DBMS_OUTPUT.PUT_LINE('Others : populate_channel_results' );
4491
4492 --RAISE;
4493
4494 END populate_channel_results;
4495
4496 --------------------------------------------------------------------------------
4497
4498 PROCEDURE populate_item_results (
4499
4500 p_search_level IN VARCHAR2,
4501
4502 p_results_requested IN NUMBER,
4503
4504 x_start_with IN OUT NOCOPY NUMBER,
4505
4506 x_results_array IN OUT NOCOPY AMV_SEARCHRES_VARRAY_TYPE,
4507
4508 x_results_populated IN OUT NOCOPY NUMBER,
4509
4510 x_total_results IN OUT NOCOPY NUMBER)
4511
4512 IS
4513
4514 l_total_count number := 1;
4515
4516 l_item_id number;
4517
4518 l_last_update_date date;
4519
4520 l_item_name varchar2(240);
4521
4522 l_description varchar2(2000);
4523
4524 l_score number;
4525
4526 l_temp_total number := 0;
4527
4528 l_null varchar2(1) := null;
4529
4530 l_start_with number;
4531
4532
4533
4534 l_id number;
4535
4536 l_name varchar2(80);
4537
4538
4539
4540 --select itm.item_id
4541
4542
4543
4544 CURSOR items_csr IS
4545
4546 select itm.item_id, itm.last_update_date, itm.item_name,
4547
4548 itm.description, max(tmp.score)
4549
4550 from jtf_amv_items_vl itm, amv_temp_ids tmp
4551
4552 where itm.item_id = tmp.id
4553
4554 group by itm.item_id, itm.last_update_date, itm.item_name, itm.description
4555
4556 order by max(tmp.score) desc;
4557
4558
4559
4560 CURSOR item_count_csr IS
4561
4562 select sum(count(distinct id))
4563
4564 from amv_temp_ids
4565
4566 group by id;
4567
4568
4569
4570 CURSOR item_cat_csr IS
4571
4572 select number_value
4573
4574 , max(score)
4575
4576 from amv_temp_ids tmp
4577
4578 where id = l_item_id
4579
4580 group by number_value;
4581
4582
4583
4584 CURSOR category_cat_csr IS
4585
4586 select channel_category_id
4587
4588 , channel_category_name
4589
4590 from amv_c_categories_vl
4591
4592 where channel_category_id = l_id;
4593
4594
4595
4596 CURSOR category_chn_csr IS
4597
4598 select channel_id
4599
4600 , channel_name
4601
4602 from amv_c_channels_vl
4603
4604 where channel_id = l_id;
4605
4606
4607
4608
4609
4610 l_count number;
4611
4612
4613
4614 BEGIN
4615
4616 --DBMS_OUTPUT.PUT_LINE('Enter : populate_item_results' );
4617
4618
4619
4620 select count(*) into l_count from amv_temp_ids;
4621
4622 --DBMS_OUTPUT.PUT_LINE('count is: ' || l_count);
4623
4624
4625
4626 OPEN item_count_csr;
4627
4628 FETCH item_count_csr INTO l_temp_total;
4629
4630 x_total_results := x_total_results + nvl(l_temp_total,0);
4631
4632 CLOSE item_count_csr;
4633
4634
4635
4636 --DBMS_OUTPUT.PUT_LINE('position A');
4637
4638
4639
4640 l_start_with := x_start_with;
4641
4642 IF x_start_with <= x_total_results THEN
4643
4644 --DBMS_OUTPUT.PUT_LINE('position B');
4645
4646 OPEN items_csr;
4647
4648 LOOP
4649
4650 FETCH items_csr INTO l_item_id,
4651
4652 l_last_update_date,
4653
4654 l_item_name,
4655
4656 l_description,
4657
4658 l_score;
4659
4660 OPEN item_cat_csr;
4661
4662 FETCH item_cat_csr INTO l_id, l_score;
4663
4664 CLOSE item_cat_csr;
4665
4666
4667
4668 EXIT WHEN items_csr%NOTFOUND;
4669
4670 --DBMS_OUTPUT.PUT_LINE('position C');
4671
4672 IF ( x_start_with <= l_total_count AND
4673
4674 x_results_populated < p_results_requested)
4675
4676 THEN
4677
4678 IF p_search_level = G_CHANNEL THEN
4679
4680 OPEN category_chn_csr;
4681
4682 FETCH category_chn_csr INTO l_id, l_name;
4683
4684 CLOSE category_chn_csr;
4685
4686 ELSIF p_search_level = G_CATEGORY THEN
4687
4688 OPEN category_cat_csr;
4689
4690 FETCH category_cat_csr INTO l_id, l_name;
4691
4692 CLOSE category_cat_csr;
4693
4694 END IF;
4695
4696 x_results_populated := x_results_populated + 1;
4697
4698 x_results_array.extend;
4699
4700 x_results_array(x_results_populated).title := l_item_name;
4701
4702 x_results_array(x_results_populated).url_string :=
4703
4704 'amvnvitm.jsp?itemid='||l_item_id;
4705
4706 x_results_array(x_results_populated).description :=
4707
4708 l_description;
4709
4710 x_results_array(x_results_populated).score :=l_score;
4711
4712 x_results_array(x_results_populated).area_id := l_item_id;
4713
4714 x_results_array(x_results_populated).area_code := G_ITEM;
4715
4716 x_results_array(x_results_populated).user1 :=
4717
4718 to_char(l_last_update_date, 'DD-MON-YYYY');
4719
4720 x_results_array(x_results_populated).user2 := l_id;
4721
4722 x_results_array(x_results_populated).user3 := p_search_level;
4723
4724 /*
4725
4726 x_results_array(x_results_populated) :=
4727
4728 amv_searchres_obj_type(
4729
4730 l_item_name,
4731
4732 'amvnvitm.jsp?itemid='||l_item_id,
4733
4734 l_description,
4735
4736 l_score,
4737
4738 l_item_id,
4739
4740 G_ITEM,
4741
4742 to_char(l_last_update_date,'DD-MON-YYYY'),
4743
4744 l_id,
4745
4746 p_search_level);
4747
4748 */
4749
4750 END IF;
4751
4752 IF (x_results_populated >= p_results_requested) THEN
4753
4754 exit;
4755
4756 END IF;
4757
4758 l_total_count := l_total_count + 1;
4759
4760 END LOOP;
4761
4762 CLOSE items_csr;
4763
4764 -- reset the start index to 1 for next loops
4765
4766 x_start_with := 1;
4767
4768 ELSE
4769
4770 -- decrement the start index with the number of results skipped
4771
4772 x_start_with := l_start_with - x_total_results;
4773
4774 END IF;
4775
4776 --DBMS_OUTPUT.PUT_LINE('Exit : populate_item_results' );
4777
4778 EXCEPTION
4779
4780 WHEN OTHERS THEN
4781
4782 --DBMS_OUTPUT.PUT_LINE('Others : populate_item_results' );
4783
4784 RAISE;
4785
4786
4787
4788 END populate_item_results;
4789
4790 --------------------------------------------------------------------------------
4791
4792 PROCEDURE build_channel_stmt (
4793
4794 p_content_array IN AMV_CHAR_VARRAY_TYPE,
4795
4796 p_imt_string IN VARCHAR2,
4797
4798 p_optional_array IN AMV_CHAR_VARRAY_TYPE,
4799
4800 p_required_array IN AMV_CHAR_VARRAY_TYPE,
4801
4802 p_excluded_array IN AMV_CHAR_VARRAY_TYPE,
4803
4804 p_keywords_search IN VARCHAR2 := FND_API.G_TRUE,
4805
4806 p_excluded_flag IN VARCHAR2 := FND_API.G_FALSE,
4807
4808 p_application_id IN NUMBER,
4809
4810 p_days IN NUMBER,
4811
4812 p_include_chns IN VARCHAR2 := FND_API.G_TRUE,
4813
4814 p_search_level IN VARCHAR2 := G_CHANNEL,
4815
4816 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
4817
4818 p_index IN OUT NOCOPY PLS_INTEGER,
4819
4820 x_chan_sql_stmt OUT NOCOPY DBMS_SQL.VARCHAR2S,
4821
4822 x_chan_sql_status OUT NOCOPY VARCHAR2)
4823
4824 IS
4825
4826
4827
4828 --l_index pls_integer;
4829
4830 l_search_level varchar2(30);
4831
4832 l_chan_insert_status varchar2(1);
4833
4834
4835
4836
4837
4838 l_start_index PLS_INTEGER := 2;
4839
4840
4841
4842 BEGIN
4843
4844
4845
4846 --DBMS_OUTPUT.PUT_LINE('Enter : build_channel_stmt' );
4847
4848 IF p_include_chns = FND_API.G_FALSE THEN
4849
4850 l_search_level := FND_API.G_MISS_CHAR;
4851
4852 ELSE
4853
4854 l_search_level := p_search_level;
4855
4856 END IF;
4857
4858
4859
4860 --DBMS_OUTPUT.PUT_LINE('p_index is: ' || p_index);
4861
4862
4863
4864 --******LOOP starts here
4865
4866 FOR i in 1..p_content_array.count LOOP
4867
4868
4869
4870
4871
4872 -- reset p_index since we are inserting right after each
4873
4874 -- sub-statement construction
4875
4876 -- initial input 'p_index' should be '2' as well
4877
4878 p_index := l_start_index;
4879
4880
4881
4882 IF p_content_array(i) = G_AUTHOR THEN
4883
4884 IF p_keywords_search = FND_API.G_TRUE THEN
4885
4886 --l_index := p_index;
4887
4888 get_chan_attr_stmt(
4889
4890 p_table_name => 'amv_c_authors',
4891
4892 p_where_column => 'author',
4893
4894 p_application_id => p_application_id,
4895
4896 p_days => p_days,
4897
4898 p_include_chns => p_include_chns,
4899
4900 p_optional_array => p_optional_array,
4901
4902 p_required_array => p_required_array,
4903
4904 p_excluded_array => p_excluded_array,
4905
4906 p_index => p_index,
4907
4908 x_sql_statement => x_chan_sql_stmt);
4909
4910
4911
4912
4913
4914 --DBMS_OUTPUT.PUT_LINE('Channel A -- p_index is: '
4915
4916 --|| p_index);
4917
4918 --DBMS_OUTPUT.PUT_LINE('count is : ' || x_chan_sql_stmt.count);
4919
4920
4921
4922 IF l_start_index <> p_index then
4923
4924 insert_temp_ids(p_stmt => x_chan_sql_stmt,
4925
4926 p_start_index => l_start_index,
4927
4928 p_end_index => p_index,
4929
4930 x_status => l_chan_insert_status);
4931
4932 x_chan_sql_stmt.delete;
4933
4934 p_index := l_start_index;
4935
4936
4937
4938 END IF;
4939
4940 END IF;
4941
4942 ELSIF p_content_array(i) = G_KEYWORD THEN
4943
4944 -- build sql statement for keywords
4945
4946 IF p_keywords_search = FND_API.G_TRUE THEN
4947
4948 --l_index := p_index;
4949
4950 get_chan_attr_stmt(
4951
4952 p_table_name => 'amv_c_keywords',
4953
4954 p_where_column => 'keyword',
4955
4956 p_application_id => p_application_id,
4957
4958 p_days => p_days,
4959
4960 p_include_chns => p_include_chns,
4961
4962 p_optional_array => p_optional_array,
4963
4964 p_required_array => p_required_array,
4965
4966 p_excluded_array => p_excluded_array,
4967
4968 p_index => p_index,
4969
4970 x_sql_statement => x_chan_sql_stmt);
4971
4972
4973
4974 --DBMS_OUTPUT.PUT_LINE('Channel B -- p_index is: '
4975
4976 --|| p_index);
4977
4978 --DBMS_OUTPUT.PUT_LINE('count is : ' || x_chan_sql_stmt.count);
4979
4980
4981
4982 IF l_start_index <> p_index then
4983
4984 insert_temp_ids(p_stmt => x_chan_sql_stmt,
4985
4986 p_start_index => l_start_index,
4987
4988 p_end_index => p_index,
4989
4990 x_status => l_chan_insert_status);
4991
4992 x_chan_sql_stmt.delete;
4993
4994 p_index := l_start_index;
4995
4996 END IF;
4997
4998 END IF;
4999
5000 ELSIF p_content_array(i) = G_TITLE_DESC THEN
5001
5002 -- title search
5003
5004 build_chan_name_sql(
5005
5006 p_index => p_index,
5007
5008 p_imt_string => p_imt_string,
5009
5010 p_application_id => p_application_id,
5011
5012 p_excluded_flag => p_excluded_flag,
5013
5014 p_include_chns => p_include_chns,
5015
5016 p_days => p_days,
5017
5018 x_sql_statement => x_chan_sql_stmt);
5019
5020
5021
5022 --DBMS_OUTPUT.PUT_LINE('Channel C -- p_index is: '
5023
5024 --|| p_index);
5025
5026 --DBMS_OUTPUT.PUT_LINE('count is : ' || x_chan_sql_stmt.count);
5027
5028 insert_temp_ids(p_stmt => x_chan_sql_stmt,
5029
5030 p_start_index => l_start_index,
5031
5032 p_end_index => p_index,
5033
5034 x_status => l_chan_insert_status);
5035
5036 x_chan_sql_stmt.delete;
5037
5038 p_index := l_start_index;
5039
5040
5041
5042 END IF;
5043
5044
5045
5046 END LOOP;
5047
5048
5049
5050
5051
5052 x_chan_sql_status := FND_API.G_TRUE;
5053
5054 --DBMS_OUTPUT.PUT_LINE('Exit : build_channel_stmt' );
5055
5056 EXCEPTION
5057
5058 WHEN OTHERS THEN
5059
5060 x_chan_sql_status := FND_API.G_FALSE;
5061
5062 --DBMS_OUTPUT.PUT_LINE('Others : build_channel_stmt' );
5063
5064 --RAISE;
5065
5066 END build_channel_stmt;
5067
5068 --------------------------------------------------------------------------------
5069
5070 PROCEDURE build_item_stmt (
5071
5072 p_content_array IN AMV_CHAR_VARRAY_TYPE,
5073
5074 p_optional_array IN AMV_CHAR_VARRAY_TYPE,
5075
5076 p_required_array IN AMV_CHAR_VARRAY_TYPE,
5077
5078 p_excluded_array IN AMV_CHAR_VARRAY_TYPE,
5079
5080 p_keywords_search IN VARCHAR2 := FND_API.G_TRUE,
5081
5082 p_excluded_flag IN VARCHAR2 := FND_API.G_FALSE,
5083
5084 p_imt_string IN VARCHAR2,
5085
5086 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5087
5088 p_days IN NUMBER,
5089
5090 p_include_chns IN VARCHAR2 := FND_API.G_TRUE,
5091
5092 p_search_level IN VARCHAR2,
5093
5094 p_external_contents IN VARCHAR2,
5095
5096 p_index IN OUT NOCOPY PLS_INTEGER,
5097
5098 x_item_sql_stmt OUT NOCOPY DBMS_SQL.VARCHAR2S,
5099
5100 x_item_sql_status OUT NOCOPY VARCHAR2)
5101
5102 IS
5103
5104
5105
5106 --l_index pls_integer;
5107
5108 l_search_level varchar2(30);
5109
5110
5111
5112 l_start_index pls_integer := 2;
5113
5114
5115
5116 l_item_insert_status varchar2(1);
5117
5118
5119
5120
5121
5122 BEGIN
5123
5124 --DBMS_OUTPUT.PUT_LINE('Enter : build_item_stmt' );
5125
5126 IF p_include_chns = FND_API.G_FALSE THEN
5127
5128 l_search_level := FND_API.G_MISS_CHAR;
5129
5130 ELSE
5131
5132 l_search_level := p_search_level;
5133
5134 END IF;
5135
5136
5137
5138
5139
5140 FOR i in 1..p_content_array.count LOOP
5141
5142
5143
5144
5145
5146 --DBMS_OUTPUT.PUT_LINE('Reset p_index');
5147
5148 x_item_sql_stmt.delete;
5149
5150 p_index := l_start_index;
5151
5152
5153
5154 IF p_content_array(i) = G_AUTHOR THEN
5155
5156 IF p_keywords_search = FND_API.G_TRUE THEN
5157
5158 -- build sql statement for authors in items
5159
5160 --l_index := p_index;
5161
5162
5163
5164 get_item_attr_stmt(
5165
5166 p_table_name => 'jtf_amv_item_authors',
5167
5168 p_where_column => 'author',
5169
5170 p_application_id => p_application_id,
5171
5172 p_days => p_days,
5173
5174 p_external_contents => FND_API.G_FALSE,
5175
5176 p_include_chns => p_include_chns,
5177
5178 p_search_level => l_search_level,
5179
5180 p_optional_array => p_optional_array,
5181
5182 p_required_array => p_required_array,
5183
5184 p_excluded_array => p_excluded_array,
5185
5186 p_index => p_index,
5187
5188 x_sql_statement => x_item_sql_stmt);
5189
5190
5191
5192
5193
5194 --DBMS_OUTPUT.PUT_LINE('Item A -- p_index is: '
5195
5196 --|| p_index);
5197
5198 --DBMS_OUTPUT.PUT_LINE('count is : ' || x_item_sql_stmt.count);
5199
5200
5201
5202 IF l_start_index <> p_index THEN
5203
5204 insert_temp_ids(p_stmt => x_item_sql_stmt,
5205
5206 p_start_index => l_start_index,
5207
5208 p_end_index => p_index,
5209
5210 x_status => l_item_insert_status);
5211
5212 x_item_sql_stmt.delete;
5213
5214 p_index := l_start_index;
5215
5216 END IF;
5217
5218
5219
5220 IF p_external_contents = FND_API.G_TRUE THEN
5221
5222 --l_index := p_index;
5223
5224 get_item_attr_stmt(
5225
5226 p_table_name => 'jtf_amv_item_authors',
5227
5228 p_where_column => 'author',
5229
5230 p_application_id => FND_API.G_MISS_NUM,
5231
5232 p_days => p_days,
5233
5234 p_external_contents => p_external_contents,
5235
5236 p_include_chns => p_include_chns,
5237
5238 p_search_level => l_search_level,
5239
5240 p_optional_array => p_optional_array,
5241
5242 p_required_array => p_required_array,
5243
5244 p_excluded_array => p_excluded_array,
5245
5246 p_index => p_index,
5247
5248 x_sql_statement => x_item_sql_stmt);
5249
5250
5251
5252 --DBMS_OUTPUT.PUT_LINE('Item B -- p_index is: '
5253
5254 --|| p_index);
5255
5256 --DBMS_OUTPUT.PUT_LINE('count is : ' || x_item_sql_stmt.count);
5257
5258 IF l_start_index <> p_index THEN
5259
5260 insert_temp_ids(p_stmt => x_item_sql_stmt,
5261
5262 p_start_index => l_start_index,
5263
5264 p_end_index => p_index,
5265
5266 x_status => l_item_insert_status);
5267
5268 x_item_sql_stmt.delete;
5269
5270 p_index := l_start_index;
5271
5272 END IF;
5273
5274 END IF;
5275
5276 END IF;
5277
5278 ELSIF p_content_array(i) = G_KEYWORD THEN
5279
5280 -- build sql statement for keywords in items
5281
5282 IF p_keywords_search = FND_API.G_TRUE THEN
5283
5284 --l_index := p_index;
5285
5286 get_item_attr_stmt(
5287
5288 p_table_name => 'jtf_amv_item_keywords',
5289
5290 p_where_column => 'keyword',
5291
5292 p_application_id => p_application_id,
5293
5294 p_days => p_days,
5295
5296 p_external_contents => FND_API.G_FALSE,
5297
5298 p_include_chns => p_include_chns,
5299
5300 p_search_level => l_search_level,
5301
5302 p_optional_array => p_optional_array,
5303
5304 p_required_array => p_required_array,
5305
5306 p_excluded_array => p_excluded_array,
5307
5308 p_index => p_index,
5309
5310 x_sql_statement => x_item_sql_stmt);
5311
5312
5313
5314 --DBMS_OUTPUT.PUT_LINE('Item C -- p_index is: '
5315
5316 --|| p_index);
5317
5318 --DBMS_OUTPUT.PUT_LINE('count is : ' || x_item_sql_stmt.count);
5319
5320
5321
5322 IF l_start_index <> p_index THEN
5323
5324 insert_temp_ids(p_stmt => x_item_sql_stmt,
5325
5326 p_start_index => l_start_index,
5327
5328 p_end_index => p_index,
5329
5330 x_status => l_item_insert_status);
5331
5332 x_item_sql_stmt.delete;
5333
5334 p_index := l_start_index;
5335
5336 END IF;
5337
5338
5339
5340 IF p_external_contents = FND_API.G_TRUE THEN
5341
5342 --l_index := p_index;
5343
5344 get_item_attr_stmt(
5345
5346 p_table_name => 'jtf_amv_item_keywords',
5347
5348 p_where_column => 'keyword',
5349
5350 p_application_id => FND_API.G_MISS_NUM,
5351
5352 p_days => p_days,
5353
5354 p_external_contents => p_external_contents,
5355
5356 p_include_chns => p_include_chns,
5357
5358 p_search_level => l_search_level,
5359
5360 p_optional_array => p_optional_array,
5361
5362 p_required_array => p_required_array,
5363
5364 p_excluded_array => p_excluded_array,
5365
5366 p_index => p_index,
5367
5368 x_sql_statement => x_item_sql_stmt);
5369
5370
5371
5372
5373
5374 --DBMS_OUTPUT.PUT_LINE('Item D -- p_index is: '
5375
5376 --|| p_index);
5377
5378 --DBMS_OUTPUT.PUT_LINE('count is : ' || x_item_sql_stmt.count);
5379
5380
5381
5382 IF l_start_index <> p_index THEN
5383
5384 insert_temp_ids(p_stmt => x_item_sql_stmt,
5385
5386 p_start_index => l_start_index,
5387
5388 p_end_index => p_index,
5389
5390 x_status => l_item_insert_status);
5391
5392 x_item_sql_stmt.delete;
5393
5394 p_index := l_start_index;
5395
5396 END IF;
5397
5398 END IF;
5399
5400 END IF;
5401
5402 ELSIF p_content_array(i) = G_TITLE_DESC THEN
5403
5404 -- title search
5405
5406 build_items_name_sql(p_index => p_index,
5407
5408 p_imt_string => p_imt_string,
5409
5410 p_application_id => p_application_id,
5411
5412 p_include_chns => p_include_chns,
5413
5414 p_days => p_days,
5415
5416 p_search_level => l_search_level,
5417
5418 p_excluded_flag => p_excluded_flag,
5419
5420 p_external_contents => FND_API.G_FALSE,
5421
5422 x_sql_statement => x_item_sql_stmt);
5423
5424
5425
5426
5427
5428 --DBMS_OUTPUT.PUT_LINE('Item E -- p_index is: '
5429
5430 --|| p_index);
5431
5432 --DBMS_OUTPUT.PUT_LINE('count is : ' || x_item_sql_stmt.count);
5433
5434 --sql union
5435
5436 insert_temp_ids(p_stmt => x_item_sql_stmt,
5437
5438 p_start_index => l_start_index,
5439
5440 p_end_index => p_index,
5441
5442 x_status => l_item_insert_status);
5443
5444 x_item_sql_stmt.delete;
5445
5446 p_index := l_start_index;
5447
5448
5449
5450
5451
5452
5453
5454 IF p_external_contents = FND_API.G_TRUE THEN
5455
5456 build_items_name_sql(p_index => p_index,
5457
5458 p_imt_string => p_imt_string,
5459
5460 p_application_id => FND_API.G_MISS_NUM,
5461
5462 p_include_chns => p_include_chns,
5463
5464 p_days => p_days,
5465
5466 p_search_level => l_search_level,
5467
5468 p_excluded_flag => p_excluded_flag,
5469
5470 p_external_contents => p_external_contents,
5471
5472 x_sql_statement =>x_item_sql_stmt);
5473
5474
5475
5476
5477
5478 --DBMS_OUTPUT.PUT_LINE('Item F -- p_index is: '
5479
5480 --|| p_index);
5481
5482 --DBMS_OUTPUT.PUT_LINE('count is : ' || x_item_sql_stmt.count);
5483
5484 insert_temp_ids(p_stmt => x_item_sql_stmt,
5485
5486 p_start_index => l_start_index,
5487
5488 p_end_index => p_index,
5489
5490 x_status => l_item_insert_status);
5491
5492 x_item_sql_stmt.delete;
5493
5494 p_index := l_start_index;
5495
5496 END IF;
5497
5498 ELSIF p_content_array(i) = G_CONTENT THEN
5499
5500 -- file items
5501
5502 IF p_imt_string <> '({%})' THEN
5503
5504
5505
5506 build_items_file_sql(p_index => p_index,
5507
5508 p_imt_string => p_imt_string,
5509
5510 p_application_id => p_application_id,
5511
5512 p_include_chns => p_include_chns,
5513
5514 p_days => p_days,
5515
5516 p_search_level => l_search_level,
5517
5518 p_excluded_flag => p_excluded_flag,
5519
5520 p_external_contents => FND_API.G_FALSE,
5521
5522 x_sql_statement => x_item_sql_stmt);
5523
5524
5525
5526 --DBMS_OUTPUT.PUT_LINE('Item G -- p_index is: '
5527
5528 --|| p_index);
5529
5530 --DBMS_OUTPUT.PUT_LINE('count is : ' || x_item_sql_stmt.count);
5531
5532
5533
5534 -- sql union
5535
5536 insert_temp_ids(p_stmt => x_item_sql_stmt,
5537
5538 p_start_index => l_start_index,
5539
5540 p_end_index => p_index,
5541
5542 x_status => l_item_insert_status);
5543
5544 x_item_sql_stmt.delete;
5545
5546 p_index := l_start_index;
5547
5548
5549
5550
5551
5552 IF p_external_contents = FND_API.G_TRUE THEN
5553
5554 build_items_file_sql(p_index => p_index,
5555
5556 p_imt_string => p_imt_string,
5557
5558 p_application_id => FND_API.G_MISS_NUM,
5559
5560 p_include_chns => p_include_chns,
5561
5562 p_days => p_days,
5563
5564 p_search_level => l_search_level,
5565
5566 p_excluded_flag => p_excluded_flag,
5567
5568 p_external_contents => p_external_contents,
5569
5570 x_sql_statement => x_item_sql_stmt);
5571
5572
5573
5574 --DBMS_OUTPUT.PUT_LINE('Item H -- p_index is: '
5575
5576 --|| p_index);
5577
5578 --DBMS_OUTPUT.PUT_LINE('count is : ' || x_item_sql_stmt.count);
5579
5580 -- sql union
5581
5582 insert_temp_ids(p_stmt => x_item_sql_stmt,
5583
5584 p_start_index => l_start_index,
5585
5586 p_end_index => p_index,
5587
5588 x_status => l_item_insert_status);
5589
5590 x_item_sql_stmt.delete;
5591
5592 p_index := l_start_index;
5593
5594
5595
5596
5597
5598 END IF;
5599
5600 END IF;
5601
5602
5603
5604 -- text items
5605
5606 build_items_text_sql(p_index => p_index,
5607
5608 p_imt_string => p_imt_string,
5609
5610 p_application_id => p_application_id,
5611
5612 p_include_chns => p_include_chns,
5613
5614 p_days => p_days,
5615
5616 p_search_level => l_search_level,
5617
5618 p_excluded_flag => p_excluded_flag,
5619
5620 p_external_contents => FND_API.G_FALSE,
5621
5622 x_sql_statement => x_item_sql_stmt);
5623
5624
5625
5626 --DBMS_OUTPUT.PUT_LINE('Item H -- p_index is: '
5627
5628 --|| p_index);
5629
5630 --DBMS_OUTPUT.PUT_LINE('count is : ' || x_item_sql_stmt.count);
5631
5632
5633
5634 -- sql union
5635
5636 insert_temp_ids(p_stmt => x_item_sql_stmt,
5637
5638 p_start_index => l_start_index,
5639
5640 p_end_index => p_index,
5641
5642 x_status => l_item_insert_status);
5643
5644 x_item_sql_stmt.delete;
5645
5646 p_index := l_start_index;
5647
5648
5649
5650 IF p_external_contents = FND_API.G_TRUE THEN
5651
5652 build_items_text_sql(p_index => p_index,
5653
5654 p_imt_string => p_imt_string,
5655
5656 p_application_id => FND_API.G_MISS_NUM,
5657
5658 p_include_chns => p_include_chns,
5659
5660 p_days => p_days,
5661
5662 p_search_level => l_search_level,
5663
5664 p_excluded_flag => p_excluded_flag,
5665
5666 p_external_contents => p_external_contents,
5667
5668 x_sql_statement => x_item_sql_stmt);
5669
5670
5671
5672
5673
5674 --DBMS_OUTPUT.PUT_LINE('Item I -- p_index is: '
5675
5676 --|| p_index);
5677
5678 --DBMS_OUTPUT.PUT_LINE('count is : ' || x_item_sql_stmt.count);
5679
5680 -- sql union
5681
5682 insert_temp_ids(p_stmt => x_item_sql_stmt,
5683
5684 p_start_index => l_start_index,
5685
5686 p_end_index => p_index,
5687
5688 x_status => l_item_insert_status);
5689
5690 x_item_sql_stmt.delete;
5691
5692 p_index := l_start_index;
5693
5694
5695
5696 END IF;
5697
5698
5699
5700 -- url items
5701
5702 build_items_url_sql(p_index => p_index,
5703
5704 p_imt_string => p_imt_string,
5705
5706 p_application_id => p_application_id,
5707
5708 p_include_chns => p_include_chns,
5709
5710 p_days => p_days,
5711
5712 p_search_level => l_search_level,
5713
5714 p_excluded_flag => p_excluded_flag,
5715
5716 p_external_contents => FND_API.G_FALSE,
5717
5718 x_sql_statement => x_item_sql_stmt);
5719
5720
5721
5722 --DBMS_OUTPUT.PUT_LINE('Item J -- p_index is: '
5723
5724 --|| p_index);
5725
5726 --DBMS_OUTPUT.PUT_LINE('count is : ' || x_item_sql_stmt.count);
5727
5728 -- sql union
5729
5730 insert_temp_ids(p_stmt => x_item_sql_stmt,
5731
5732 p_start_index => l_start_index,
5733
5734 p_end_index => p_index,
5735
5736 x_status => l_item_insert_status);
5737
5738 x_item_sql_stmt.delete;
5739
5740 p_index := l_start_index;
5741
5742
5743
5744 IF p_external_contents = FND_API.G_TRUE THEN
5745
5746 build_items_url_sql(p_index => p_index,
5747
5748 p_imt_string => p_imt_string,
5749
5750 p_application_id => FND_API.G_MISS_NUM,
5751
5752 p_include_chns => p_include_chns,
5753
5754 p_days => p_days,
5755
5756 p_search_level => l_search_level,
5757
5758 p_excluded_flag => p_excluded_flag,
5759
5760 p_external_contents => p_external_contents,
5761
5762 x_sql_statement => x_item_sql_stmt);
5763
5764
5765
5766
5767
5768 --DBMS_OUTPUT.PUT_LINE('Item K -- p_index is: '
5769
5770 --|| p_index);
5771
5772 --DBMS_OUTPUT.PUT_LINE('count is : ' || x_item_sql_stmt.count);
5773
5774 -- sql union
5775
5776 insert_temp_ids(p_stmt => x_item_sql_stmt,
5777
5778 p_start_index => l_start_index,
5779
5780 p_end_index => p_index,
5781
5782 x_status => l_item_insert_status);
5783
5784 x_item_sql_stmt.delete;
5785
5786 p_index := l_start_index;
5787
5788
5789
5790
5791
5792 END IF;
5793
5794 END IF;
5795
5796
5797
5798 END LOOP;
5799
5800
5801
5802
5803
5804 x_item_sql_status := FND_API.G_TRUE;
5805
5806 --DBMS_OUTPUT.PUT_LINE('Exit : build_item_stmt' );
5807
5808 EXCEPTION
5809
5810 WHEN OTHERS THEN
5811
5812 x_item_sql_status := FND_API.G_FALSE;
5813
5814 --DBMS_OUTPUT.PUT_LINE('Others : build_item_stmt' );
5815
5816 --RAISE;
5817
5818 END build_item_stmt;
5819
5820 --------------------------------------------------------------------------------
5821
5822 PROCEDURE search_items(
5823
5824 p_area_array IN AMV_CHAR_VARRAY_TYPE,
5825
5826 p_content_array IN AMV_CHAR_VARRAY_TYPE,
5827
5828 p_imt_string IN VARCHAR2,
5829
5830 p_optional_array IN AMV_CHAR_VARRAY_TYPE,
5831
5832 p_required_array IN AMV_CHAR_VARRAY_TYPE,
5833
5834 p_excluded_array IN AMV_CHAR_VARRAY_TYPE,
5835
5836 p_keywords_search IN VARCHAR2,
5837
5838 p_excluded_flag IN VARCHAR2,
5839
5840 p_application_id IN NUMBER,
5841
5842 p_days IN NUMBER,
5843
5844 p_include_chns IN VARCHAR2,
5845
5846 p_search_level IN VARCHAR2,
5847
5848 p_external_contents IN VARCHAR2,
5849
5850 p_records_requested IN NUMBER,
5851
5852 x_start_with IN OUT NOCOPY NUMBER,
5853
5854 x_results_populated IN OUT NOCOPY NUMBER,
5855
5856 x_total_count IN OUT NOCOPY NUMBER,
5857
5858 x_searchres_array IN OUT NOCOPY AMV_SEARCHRES_VARRAY_TYPE )
5859
5860 IS
5861
5862 l_flag varchar2(1);
5863
5864
5865
5866 l_chan_sql_status varchar2(1);
5867
5868 l_item_sql_status varchar2(1);
5869
5870 l_chan_insert_status varchar2(1);
5871
5872 l_item_insert_status varchar2(1);
5873
5874
5875
5876 l_chan_sql_stmt DBMS_SQL.VARCHAR2S;
5877
5878 l_item_sql_stmt DBMS_SQL.VARCHAR2S;
5879
5880 l_start_index PLS_INTEGER := 2;
5881
5882 l_index PLS_INTEGER := 2;
5883
5884
5885
5886 l_category_flag varchar2(1) := FND_API.G_FALSE;
5887
5888 l_item_flag varchar2(1) := FND_API.G_FALSE;
5889
5890 l_content_flag varchar2(1) := FND_API.G_FALSE;
5891
5892
5893
5894 BEGIN
5895
5896 --DBMS_OUTPUT.PUT_LINE('Enter : search_items' );
5897
5898
5899
5900
5901
5902 FOR j in 1..p_area_array.count LOOP
5903
5904 IF p_area_array(j) = G_CATEGORY THEN
5905
5906 l_category_flag := FND_API.G_TRUE;
5907
5908 ELSIF p_area_array(j) = G_ITEM THEN
5909
5910 l_item_flag := FND_API.G_TRUE;
5911
5912 END IF;
5913
5914 END LOOP;
5915
5916
5917
5918 -- channels/categories search
5919
5920 IF l_category_flag = FND_API.G_TRUE THEN
5921
5922 -- no search on channel done at category level
5923
5924 IF p_search_level <> G_CATEGORY THEN
5925
5926
5927
5928
5929
5930 -- build sql statement for searching categories
5931
5932 l_index := l_start_index;
5933
5934 build_channel_stmt (
5935
5936 p_content_array => p_content_array,
5937
5938 p_imt_string => p_imt_string,
5939
5940 p_optional_array =>p_optional_array,
5941
5942 p_required_array => p_required_array,
5943
5944 p_excluded_array => p_excluded_array,
5945
5946 p_keywords_search => p_keywords_search,
5947
5948 p_excluded_flag => p_excluded_flag,
5949
5950 p_application_id => p_application_id,
5951
5952 p_days => p_days,
5953
5954 p_include_chns => p_include_chns,
5955
5956 p_search_level => p_search_level,
5957
5958 p_external_contents => p_external_contents,
5959
5960 p_index => l_index,
5961
5962 x_chan_sql_stmt => l_chan_sql_stmt,
5963
5964 x_chan_sql_status => l_chan_sql_status);
5965
5966
5967
5968 --IF l_chan_sql_stmt.count > 0 THEN
5969
5970 -- execute sql statement and insert into temp table
5971
5972 --insert_temp_ids(p_stmt => l_chan_sql_stmt,
5973
5974 -- p_start_index => l_start_index,
5975
5976 -- p_end_index => l_index - 1, -- 1 for union
5977
5978 -- x_status => l_chan_insert_status);
5979
5980
5981
5982 -- populate results cursor
5983
5984 populate_channel_results (
5985
5986 p_results_requested => p_records_requested,
5987
5988 x_start_with => x_start_with,
5989
5990 x_results_array => x_searchres_array,
5991
5992 x_results_populated => x_results_populated,
5993
5994 x_total_results => x_total_count);
5995
5996 --END IF;
5997
5998 END IF;
5999
6000 END IF;
6001
6002
6003
6004 -- Items search
6005
6006 IF l_item_flag = FND_API.G_TRUE THEN
6007
6008 -- build sql statement for searching items
6009
6010 l_index := l_start_index;
6011
6012 build_item_stmt (
6013
6014 p_content_array => p_content_array,
6015
6016 p_optional_array => p_optional_array,
6017
6018 p_required_array => p_required_array,
6019
6020 p_excluded_array => p_excluded_array,
6021
6022 p_keywords_search => p_keywords_search,
6023
6024 p_excluded_flag => p_excluded_flag,
6025
6026 p_imt_string => p_imt_string,
6027
6028 p_application_id => p_application_id,
6029
6030 p_days => p_days,
6031
6032 p_include_chns => p_include_chns,
6033
6034 p_search_level => p_search_level,
6035
6036 p_external_contents => p_external_contents,
6037
6038 p_index => l_index,
6039
6040 x_item_sql_stmt => l_item_sql_stmt,
6041
6042 x_item_sql_status => l_item_sql_status);
6043
6044
6045
6046 --IF l_item_sql_stmt.count > 0 THEN
6047
6048 -- execute sql statement and insert into temp table
6049
6050 --insert_temp_ids(p_stmt => l_item_sql_stmt,
6051
6052 -- p_start_index => l_start_index,
6053
6054 -- p_end_index => l_index -1, -- 1 for union
6055
6056 -- x_status => l_item_insert_status);
6057
6058
6059
6060 -- populate results cursor with items
6061
6062 populate_item_results (
6063
6064 p_search_level => p_search_level,
6065
6066 p_results_requested => p_records_requested,
6067
6068 x_start_with => x_start_with,
6069
6070 x_results_array => x_searchres_array,
6071
6072 x_results_populated => x_results_populated,
6073
6074 x_total_results => x_total_count);
6075
6076 --END IF;
6077
6078 END IF;
6079
6080 --
6081
6082 --DBMS_OUTPUT.PUT_LINE('Exit : search_items' );
6083
6084 EXCEPTION
6085
6086 WHEN OTHERS THEN
6087
6088 --DBMS_OUTPUT.PUT_LINE('Others : search_items' );
6089
6090 l_flag := FND_API.G_TRUE;
6091
6092 --RAISE;
6093
6094 END search_items;
6095
6096 --------------------------------------------------------------------------------
6097
6098 --------------------------------------------------------------------------------
6099
6100 -- Start of comments
6101
6102 -- API name : find_repositories
6103
6104 -- Type : Group or Public
6105
6106 -- Pre-reqs : Total number of repository names retrieved at a time will
6107
6108 -- not need to exceed amv_utility_pub.g_max_array_size. By
6109
6110 -- not needing to exceed this limit, the parameters to
6111
6112 -- control a "sliding window" of retrieved values is not
6113
6114 -- needed, thus simplifying this API's signature.
6115
6116 -- Function : Retrieves all repository names participating with
6117
6118 -- MES Search that match the input parameters specified.
6119
6120 -- Typically, only the status parameter will be set to
6121
6122 -- retrieve only active Repositories.
6123
6124 --
6125
6126 -- Marketing Encyclopedia (MES) will employ this procedure
6127
6128 -- within its Search API and screens to retrieve
6129
6130 -- repositories participating with MES search.
6131
6132 --
6133
6134 --
6135
6136 -- Parameters (Standard parameters not mentioned):
6137
6138 -- IN : p_repository_id IN NUMBER Optional
6139
6140 -- Repository ID of the Repository to retrieve
6141
6142 -- information for. Corresponds to the column
6143
6144 -- amv_d_entities_b.entity_id
6145
6146 -- where amv_d_entities_b.usage_indicator = 'AMV_SEARCH'
6147
6148 --
6149
6150 -- : p_repository_code IN VARCHAR2(255) Optional
6151
6152 -- Repository Code of the Repository to retrieve
6153
6154 -- information for. Corresponds to the column
6155
6156 -- amv_d_entities_b.table_name
6157
6158 -- where amv_d_entities_b.usage_indicator='AMV_SEARCH'
6159
6160 --
6161
6162 -- : p_repository_name IN VARCHAR2(80) Optional
6163
6164 -- Description of the Repository that should appear
6165
6166 -- on the Advanced Repository Area Search page.
6167
6168 -- Corresponds to the column
6169
6170 -- amv_d_entities_tl.entity_name.
6171
6172 --
6173
6174 -- : p_status IN VARCHAR2 Optional
6175
6176 -- Status condition to be queried.
6177
6178 -- (ACTIVE= active, INACTIVE=inactive).
6179
6180 --
6181
6182 -- : p_object_version_number IN NUMBER Optional
6183
6184 -- Used as a means of detecting updates to a row.
6185
6186 --
6187
6188 -- OUT : x_searchrep_array OUT ARRAY_TYPE
6189
6190 -- Varying Array of Object amv_searchrep_obj_type that
6191
6192 -- holds the resulting search matches.
6193
6194 --
6195
6196 -- repository_id OUT NUMBER
6197
6198 -- Repository ID that met the search criteria
6199
6200 -- provided.
6201
6202 --
6203
6204 -- repository_code OUT VARCHAR2(255)
6205
6206 -- Repository code that met the search criteria
6207
6208 -- provided.
6209
6210 --
6211
6212 -- repository_name OUT VARCHAR2(80)
6213
6214 -- Name of the Repository that met the
6215
6216 -- search criteria provided. Value will be
6217
6218 -- what is displayed on the Advanced Repository Area
6219
6220 -- Search page.
6221
6222 --
6223
6224 -- status OUT VARCHAR2(30)
6225
6226 -- Status of the record.
6227
6228 --
6229
6230 -- object_version_number OUT NUMBER
6231
6232 -- Version number stamp of the record.
6233
6234 --
6235
6236 -- Version : Current version 1.0
6237
6238 -- {add comments here}
6239
6240 -- Previous version 1.0
6241
6242 -- Initial version 1.0
6243
6244 -- End of comments
6245
6246 --
6247
6248 PROCEDURE find_repositories
6249
6250 (p_api_version IN NUMBER,
6251
6252 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
6253
6254 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
6255
6256 x_return_status OUT NOCOPY VARCHAR2,
6257
6258 x_msg_count OUT NOCOPY NUMBER,
6259
6260 x_msg_data OUT NOCOPY VARCHAR2,
6261
6262 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
6263
6264 p_object_version_number IN NUMBER := FND_API.G_MISS_NUM,
6265
6266 p_repository_id IN NUMBER := FND_API.G_MISS_NUM,
6267
6268 p_repository_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
6269
6270 p_repository_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
6271
6272 p_status IN VARCHAR2 := FND_API.G_MISS_CHAR,
6273
6274 x_searchrep_array OUT NOCOPY amv_searchrep_varray_type)
6275
6276 IS
6277
6278 l_api_name CONSTANT VARCHAR2(30) := 'find_repositories';
6279
6280 l_api_version CONSTANT NUMBER := 1.0;
6281
6282 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
6283
6284 --
6285
6286 l_resource_id number;
6287
6288 l_user_id number;
6289
6290 l_login_user_id number;
6291
6292 l_login_user_status varchar2(30);
6293
6294 l_Error_Msg varchar2(2000);
6295
6296 l_Error_Token varchar2(80);
6297
6298 l_application_id number := 520;
6299
6300 --
6301
6302 l_cursor CursorType;
6303
6304 l_sql_statement varchar2(2000);
6305
6306 l_where_clause varchar2(2000);
6307
6308 l_fetch_count number := 0;
6309
6310 l_repository_id number;
6311
6312 l_repository_code varchar2(30);
6313
6314 l_repository_name varchar2(80);
6315
6316 l_status varchar2(30);
6317
6318 l_object_version_number number;
6319
6320 --
6321
6322 BEGIN
6323
6324 --DBMS_OUTPUT.PUT_LINE('Enter : find_repositories' );
6325
6326 -- Standard begin of API savepoint
6327
6328 SAVEPOINT Find_Repositories;
6329
6330 -- Standard call to check for call compatibility.
6331
6332 IF NOT FND_API.Compatible_API_Call (
6333
6334 l_api_version,
6335
6336 p_api_version,
6337
6338 l_api_name,
6339
6340 G_PKG_NAME)
6341
6342 THEN
6343
6344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6345
6346 END IF;
6347
6348 -- Debug Message
6349
6350 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
6351
6352 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
6353
6354 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
6355
6356 FND_MSG_PUB.Add;
6357
6358 END IF;
6359
6360 --Initialize message list if p_init_msg_list is TRUE.
6361
6362 IF FND_API.To_Boolean (p_init_msg_list) THEN
6363
6364 FND_MSG_PUB.initialize;
6365
6366 END IF;
6367
6368 -- Get the current (login) user id.
6369
6370 AMV_UTILITY_PVT.Get_UserInfo(
6371
6372 x_resource_id => l_resource_id,
6373
6374 x_user_id => l_user_id,
6375
6376 x_login_id => l_login_user_id,
6377
6378 x_user_status => l_login_user_status
6379
6380 );
6381
6382 -- check login user
6383
6384 IF (p_check_login_user = FND_API.G_TRUE) THEN
6385
6386 -- Check if user is login and has the required privilege.
6387
6388 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
6389
6390 -- User is not login.
6391
6392 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6393
6394 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
6395
6396 FND_MSG_PUB.Add;
6397
6398 END IF;
6399
6400 RAISE FND_API.G_EXC_ERROR;
6401
6402 END IF;
6403
6404 END IF;
6405
6406 -- Initialize API return status to sucess
6407
6408 x_return_status := FND_API.G_RET_STS_SUCCESS;
6409
6410
6411
6412 --
6413
6414 -- construct synamic sql statement based on the parameters
6415
6416 l_sql_statement := 'SELECT b.entity_id, ' ||
6417
6418 ' b.table_name, ' ||
6419
6420 ' tl.entity_name, ' ||
6421
6422 ' b.status, ' ||
6423
6424 ' b.object_version_number ' ||
6425
6426 'FROM amv_d_entities_b b ' ||
6427
6428 ', amv_d_entities_tl tl ' ||
6429
6430 'WHERE b.usage_indicator = '''||
6431
6432 G_AMV_SEARCH||'''';
6433
6434
6435
6436 --Construct the WHERE clause
6437
6438 IF (p_repository_id <> FND_API.G_MISS_NUM) THEN
6439
6440 l_where_clause :=l_where_clause ||' AND b.application_id = ' || p_repository_id;
6441
6442 END IF;
6443
6444
6445
6446 IF (p_repository_code <> FND_API.G_MISS_CHAR) THEN
6447
6448 l_where_clause:=l_where_clause||' AND b.table_name = ''' ||
6449
6450 p_repository_code||'''';
6451
6452 END IF;
6453
6454
6455
6456 IF (p_status <> FND_API.G_MISS_CHAR) THEN
6457
6458 l_where_clause := l_where_clause || ' AND b.status = ''' ||
6459
6460 p_status||'''';
6461
6462 END IF;
6463
6464
6465
6466 IF (p_repository_name <> FND_API.G_MISS_CHAR) THEN
6467
6468 l_where_clause:=l_where_clause||' AND tl.entity_name=''' ||
6469
6470 p_repository_name||'''';
6471
6472 END IF;
6473
6474 l_where_clause := l_where_clause ||
6475
6476 ' AND tl.language = userenv(' || '''lang''' || ') ' ||
6477
6478 ' AND b.entity_id = tl.entity_id ';
6479
6480 --
6481
6482 l_sql_statement := l_sql_statement || l_where_clause;
6483
6484 --Now execute the SQL statement:
6485
6486 OPEN l_cursor FOR l_sql_statement;
6487
6488 x_searchrep_array := AMV_SEARCHREP_VARRAY_TYPE();
6489
6490 -- NOTE change to fetch into obj
6491
6492 LOOP
6493
6494 l_fetch_count := l_fetch_count + 1;
6495
6496 x_searchrep_array.extend;
6497
6498 FETCH l_cursor INTO x_searchrep_array(l_fetch_count);
6499
6500 EXIT WHEN l_cursor%NOTFOUND;
6501
6502 /*
6503
6504 FETCH l_cursor INTO
6505
6506 l_repository_id,
6507
6508 l_repository_code,
6509
6510 l_repository_name,
6511
6512 l_status,
6513
6514 l_object_version_number;
6515
6516 EXIT WHEN l_cursor%NOTFOUND;
6517
6518 l_fetch_count := l_fetch_count + 1;
6519
6520 x_searchrep_array.extend;
6521
6522 x_searchrep_array(l_fetch_count) :=
6523
6524 amv_searchrep_obj_type(
6525
6526 l_repository_id,
6527
6528 l_repository_code,
6529
6530 l_repository_name,
6531
6532 l_status,
6533
6534 l_object_version_number);
6535
6536 */
6537
6538 END LOOP;
6539
6540 CLOSE l_cursor;
6541
6542
6543
6544 /*
6545
6546 -- Success message
6547
6548 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
6549
6550 THEN
6551
6552 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
6553
6554 FND_MESSAGE.Set_Token('ROW', l_full_name);
6555
6556 FND_MSG_PUB.Add;
6557
6558 END IF;
6559
6560 */
6561
6562 -- Debug Message
6563
6564 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
6565
6566 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
6567
6568 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
6569
6570 FND_MSG_PUB.Add;
6571
6572 END IF;
6573
6574 --Standard call to get message count and if count=1, get the message
6575
6576 FND_MSG_PUB.Count_And_Get (
6577
6578 p_encoded => FND_API.G_FALSE,
6579
6580 p_count => x_msg_count,
6581
6582 p_data => x_msg_data
6583
6584 );
6585
6586 --DBMS_OUTPUT.PUT_LINE('Exit : find_repositories' );
6587
6588 EXCEPTION
6589
6590 WHEN FND_API.G_EXC_ERROR THEN
6591
6592 ROLLBACK TO Find_Repositories;
6593
6594 x_return_status := FND_API.G_RET_STS_ERROR;
6595
6596 -- Standard call to get message count and if count=1, get the message
6597
6598 FND_MSG_PUB.Count_And_Get (
6599
6600 p_encoded => FND_API.G_FALSE,
6601
6602 p_count => x_msg_count,
6603
6604 p_data => x_msg_data
6605
6606 );
6607
6608 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6609
6610 ROLLBACK TO Find_Repositories;
6611
6612 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6613
6614 -- Standard call to get message count and if count=1, get the message
6615
6616 FND_MSG_PUB.Count_And_Get (
6617
6618 p_encoded => FND_API.G_FALSE,
6619
6620 p_count => x_msg_count,
6621
6622 p_data => x_msg_data
6623
6624 );
6625
6626 WHEN OTHERS THEN
6627
6628 ROLLBACK TO Find_Repositories;
6629
6630 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6631
6632 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6633
6634 THEN
6635
6636 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
6637
6638 END IF;
6639
6640 -- Standard call to get message count and if count=1, get the message
6641
6642 FND_MSG_PUB.Count_And_Get (
6643
6644 p_encoded => FND_API.G_FALSE,
6645
6646 p_count => x_msg_count,
6647
6648 p_data => x_msg_data
6649
6650 );
6651
6652 --
6653
6654 END find_repositories;
6655
6656 --
6657
6658 --------------------------------------------------------------------------------
6659
6660 -- Start of comments
6661
6662 -- API name : find_repository_areas
6663
6664 -- Type : Group or Public
6665
6666 -- Pre-reqs : Total number of repository areas retrieved at a time will
6667
6668 -- not need to exceed amv_utility_pub.g_max_array_size. By
6669
6670 -- not needing to exceed this limit, the parameters to
6671
6672 -- control a "sliding window" of retrieved values is not
6673
6674 -- needed, thus simplifying this API's signature.
6675
6676 -- Function : Retrieves all repository areas for a given repository
6677
6678 -- that is participating with MES Search which matches the
6679
6680 -- input parameters specified.
6681
6682 -- Typically, the repository code will be provided to
6683
6684 -- restrict the return to include only areas for that
6685
6686 -- repository; The status parameter will usually be set to
6687
6688 -- retrieve only active Repository Areas.
6689
6690 --
6691
6692 -- Marketing Encyclopedia (MES) will employ this procedure
6693
6694 -- within its Search API and screens to retrieve Repository
6695
6696 -- Areas valid for an MES search with the specified Repository.
6697
6698 --
6699
6700 -- Parameters (Standard parameters not mentioned):
6701
6702 -- IN : p_repository_id IN NUMBER Optional
6703
6704 -- Repository identifier of the Repository Code to
6705
6706 -- retrieve information for. Corresponds to the column
6707
6708 -- amv_d_entities_b.entity_id
6709
6710 -- where amv_d_entities_b.usage_indicator = 'AMV_SEARCH'
6711
6712 --
6713
6714 -- : p_repository_code IN VARCHAR2(255) Optional
6715
6716 -- Repository Code of the Repository to retrieve
6717
6718 -- information for. Corresponds to the column
6719
6720 -- amv_d_entities_b.table_name
6721
6722 -- where amv_d_entities_b.usage_indicator = 'AMV_SEARCH'
6723
6724 --
6725
6726 -- : p_area_id IN NUMBER Optional
6727
6728 -- Repository Area identifier of the Repository Area to
6729
6730 -- retrieve information for. Corresponds to the column
6731
6732 -- amv_d_ent_attributes_b.attribute_id
6733
6734 -- where amv_d_ent_attributes_b.usage_indicator=
6735
6736 -- 'CONTENT_AREA'
6737
6738 -- 'SEARCH_AREA'
6739
6740 -- 'CONDITION_CONS'
6741
6742 -- 'WORD_CONS'
6743
6744 --
6745
6746 -- : p_area_code IN VARCHAR2(255) Optional
6747
6748 -- Area Repository Code of the Repository to retrieve
6749
6750 -- information for. Corresponds to the column
6751
6752 -- amv_d_ent_attributes_b.column_name
6753
6754 --
6755
6756 -- : p_area_name IN VARCHAR2(80) Optional
6757
6758 -- Description of the Repository that should appear
6759
6760 -- on the Advanced Repository Area Search page.
6761
6762 -- Corresponds to the column
6763
6764 -- amv_d_ent_attributes_tl.attribute_name.
6765
6766 --
6767
6768 -- : p_status IN VARCHAR2 Optional
6769
6770 -- Status condition to be queried.
6771
6772 -- (ACTIVE= active, INACTIVE=inactive).
6773
6774 --
6775
6776 -- : p_object_version_number IN NUMBER Optional
6777
6778 -- Used as a means of detecting updates to a row.
6779
6780 --
6781
6782 -- OUT : x_searcharea_array OUT ARRAY_TYPE
6783
6784 -- Varying Array of Object amv_searchrep_obj_type that
6785
6786 -- holds the resulting search matches.
6787
6788 --
6789
6790 -- repository_id OUT NUMBER
6791
6792 -- Repository ID that met the search criteria
6793
6794 -- provided.
6795
6796 --
6797
6798 -- repository_code OUT VARCHAR2(255)
6799
6800 -- Repository code that met the search criteria
6801
6802 -- provided.
6803
6804 --
6805
6806 -- area_id OUT NUMBER
6807
6808 -- Area ID that met the search criteria
6809
6810 -- provided.
6811
6812 --
6813
6814 -- area_code OUT VARCHAR2(80)
6815
6816 -- Area code that met the search criteria
6817
6818 -- provided.
6819
6820 --
6821
6822 -- area_name OUT VARCHAR2(80)
6823
6824 -- Name of the Repository Area that met the
6825
6826 -- search criteria provided. Value will be
6827
6828 -- what is displayed on the Advanced Repository Area
6829
6830 -- Search page.
6831
6832 --
6833
6834 -- status OUT VARCHAR2(30)
6835
6836 -- Status of the record.
6837
6838 --
6839
6840 -- object_version_number OUT NUMBER
6841
6842 -- Version number stamp of the record.
6843
6844 --
6845
6846 -- Version : Current version 1.0
6847
6848 -- {add comments here}
6849
6850 -- Previous version 1.0
6851
6852 -- Initial version 1.0
6853
6854 -- End of comments
6855
6856 --
6857
6858 PROCEDURE find_repository_areas
6859
6860 (p_api_version IN NUMBER,
6861
6862 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
6863
6864 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
6865
6866 x_return_status OUT NOCOPY VARCHAR2,
6867
6868 x_msg_count OUT NOCOPY NUMBER,
6869
6870 x_msg_data OUT NOCOPY VARCHAR2,
6871
6872 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
6873
6874 p_searcharea_obj IN amv_searchara_obj_type,
6875
6876 x_searcharea_array OUT NOCOPY amv_searchara_varray_type)
6877
6878 IS
6879
6880 l_api_name CONSTANT VARCHAR2(30) := 'find_repository_areas';
6881
6882 l_api_version CONSTANT NUMBER := 1.0;
6883
6884 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
6885
6886 --
6887
6888 l_resource_id number;
6889
6890 l_user_id number;
6891
6892 l_login_user_id number;
6893
6894 l_login_user_status varchar2(30);
6895
6896 l_Error_Msg varchar2(2000);
6897
6898 l_Error_Token varchar2(80);
6899
6900 l_application_id number := 520;
6901
6902 --
6903
6904 l_cursor CursorType;
6905
6906 l_sql_statement varchar2(2000);
6907
6908 l_where_clause varchar2(2000);
6909
6910 l_fetch_count number := 0;
6911
6912 l_repository_id number;
6913
6914 l_repository_code varchar2(30);
6915
6916 l_area_id number;
6917
6918 l_area_code varchar2(30);
6919
6920 l_area_name varchar2(80);
6921
6922 l_area_indicator varchar2(30);
6923
6924 l_status varchar2(30);
6925
6926 l_object_version_number number;
6927
6928
6929
6930 --
6931
6932 BEGIN
6933
6934 --DBMS_OUTPUT.PUT_LINE('Enter : find_repository_areas' );
6935
6936 -- Standard begin of API savepoint
6937
6938 SAVEPOINT Find_RepositoryAreas;
6939
6940 -- Standard call to check for call compatibility.
6941
6942 IF NOT FND_API.Compatible_API_Call (
6943
6944 l_api_version,
6945
6946 p_api_version,
6947
6948 l_api_name,
6949
6950 G_PKG_NAME)
6951
6952 THEN
6953
6954 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6955
6956 END IF;
6957
6958 -- Debug Message
6959
6960 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
6961
6962 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
6963
6964 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
6965
6966 FND_MSG_PUB.Add;
6967
6968 END IF;
6969
6970 --Initialize message list if p_init_msg_list is TRUE.
6971
6972 IF FND_API.To_Boolean (p_init_msg_list) THEN
6973
6974 FND_MSG_PUB.initialize;
6975
6976 END IF;
6977
6978 -- Get the current (login) user id.
6979
6980 AMV_UTILITY_PVT.Get_UserInfo(
6981
6982 x_resource_id => l_resource_id,
6983
6984 x_user_id => l_user_id,
6985
6986 x_login_id => l_login_user_id,
6987
6988 x_user_status => l_login_user_status
6989
6990 );
6991
6992 -- check login user
6993
6994 IF (p_check_login_user = FND_API.G_TRUE) THEN
6995
6996 -- Check if user is login and has the required privilege.
6997
6998 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
6999
7000 -- User is not login.
7001
7002 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
7003
7004 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
7005
7006 FND_MSG_PUB.Add;
7007
7008 END IF;
7009
7010 RAISE FND_API.G_EXC_ERROR;
7011
7012 END IF;
7013
7014 END IF;
7015
7016 -- Initialize API return status to sucess
7017
7018 x_return_status := FND_API.G_RET_STS_SUCCESS;
7019
7020
7021
7022 --
7023
7024 -- construct synamic sql statement based on the parameters
7025
7026 l_sql_statement := 'SELECT eb.entity_id, ' ||
7027
7028 ' eb.table_name, ' ||
7029
7030 ' ab.data_type, ' ||
7031
7032 ' ab.attribute_id, ' ||
7033
7034 ' ab.column_name, ' ||
7035
7036 ' atl.attribute_name, ' ||
7037
7038 ' ab.status, ' ||
7039
7040 ' ab.object_version_number ' ||
7041
7042 'FROM amv_d_entities_b eb ' ||
7043
7044 ', amv_d_entities_tl etl ' ||
7045
7046 ', amv_d_ent_attributes_b ab ' ||
7047
7048 ', amv_d_ent_attributes_tl atl ' ||
7049
7050 'WHERE ab.usage_indicator = '''|| G_AMV_SEARCH ||'''';
7051
7052
7053
7054 --Construct the WHERE clause
7055
7056 IF (p_searcharea_obj.repository_id <> FND_API.G_MISS_NUM) THEN
7057
7058 l_where_clause :=l_where_clause ||' AND eb.application_id = '||
7059
7060 p_searcharea_obj.repository_id;
7061
7062 END IF;
7063
7064
7065
7066 IF (p_searcharea_obj.repository_code <> FND_API.G_MISS_CHAR) THEN
7067
7068 l_where_clause:=l_where_clause||' AND eb.table_name = '''||
7069
7070 p_searcharea_obj.repository_code||'''';
7071
7072 END IF;
7073
7074
7075
7076 IF (p_searcharea_obj.area_id <> FND_API.G_MISS_NUM) THEN
7077
7078 l_where_clause :=l_where_clause ||' AND ab.attribute_id = '||
7079
7080 p_searcharea_obj.area_id;
7081
7082 END IF;
7083
7084
7085
7086 IF (p_searcharea_obj.area_code <> FND_API.G_MISS_CHAR) THEN
7087
7088 l_where_clause:=l_where_clause||' AND ab.column_name = '''||
7089
7090 p_searcharea_obj.area_code||'''';
7091
7092 END IF;
7093
7094
7095
7096 IF (p_searcharea_obj.area_indicator <> FND_API.G_MISS_CHAR) THEN
7097
7098 l_where_clause:=l_where_clause||' AND ab.data_type = '''||
7099
7100 p_searcharea_obj.area_indicator||'''';
7101
7102 END IF;
7103
7104
7105
7106 IF (p_searcharea_obj.status <> FND_API.G_MISS_CHAR) THEN
7107
7108 l_where_clause := l_where_clause || ' AND ab.status = '''||
7109
7110 p_searcharea_obj.status||'''';
7111
7112 END IF;
7113
7114
7115
7116 IF (p_searcharea_obj.area_name <> FND_API.G_MISS_CHAR) THEN
7117
7118 l_where_clause:=l_where_clause||' AND atl.attribute_name='''||
7119
7120 p_searcharea_obj.area_name||'''';
7121
7122 END IF;
7123
7124 l_where_clause := l_where_clause ||
7125
7126 ' AND eb.entity_id = etl.entity_id ' ||
7127
7128 ' AND etl.language = '''|| userenv('lang') ||''''||
7129
7130 ' AND eb.entity_id = ab.entity_id ' ||
7131
7132 ' AND ab.attribute_id = atl.attribute_id ' ||
7133
7134 ' AND atl.language = '''|| userenv('lang') ||''''||
7135
7136 ' ORDER BY ab.column_name ';
7137
7138 --
7139
7140 l_sql_statement := l_sql_statement || l_where_clause;
7141
7142 --Now execute the SQL statement:
7143
7144 OPEN l_cursor FOR l_sql_statement;
7145
7146 x_searcharea_array := AMV_SEARCHARA_VARRAY_TYPE();
7147
7148 LOOP
7149
7150 l_fetch_count := l_fetch_count + 1;
7151
7152 x_searcharea_array.extend;
7153
7154 FETCH l_cursor INTO x_searcharea_array(l_fetch_count);
7155
7156 EXIT WHEN l_cursor%NOTFOUND;
7157
7158 /*
7159
7160 FETCH l_cursor INTO
7161
7162 l_repository_id,
7163
7164 l_repository_code,
7165
7166 l_area_indicator,
7167
7168 l_area_id,
7169
7170 l_area_code,
7171
7172 l_area_name,
7173
7174 l_status,
7175
7176 l_object_version_number;
7177
7178 EXIT WHEN l_cursor%NOTFOUND;
7179
7180 l_fetch_count := l_fetch_count + 1;
7181
7182 x_searcharea_array.extend;
7183
7184 x_searcharea_array(l_fetch_count) :=
7185
7186 amv_searchara_obj_type(
7187
7188 l_repository_id,
7189
7190 l_repository_code,
7191
7192 l_area_indicator,
7193
7194 l_area_id,
7195
7196 l_area_code,
7197
7198 l_area_name,
7199
7200 l_status,
7201
7202 l_object_version_number);
7203
7204 */
7205
7206 END LOOP;
7207
7208 CLOSE l_cursor;
7209
7210 --
7211
7212
7213
7214 -- Success message
7215
7216 /*
7217
7218 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
7219
7220 THEN
7221
7222 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
7223
7224 FND_MESSAGE.Set_Token('ROW', l_full_name);
7225
7226 FND_MSG_PUB.Add;
7227
7228 END IF;
7229
7230 */
7231
7232 -- Debug Message
7233
7234 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
7235
7236 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
7237
7238 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
7239
7240 FND_MSG_PUB.Add;
7241
7242 END IF;
7243
7244 --Standard call to get message count and if count=1, get the message
7245
7246 FND_MSG_PUB.Count_And_Get (
7247
7248 p_encoded => FND_API.G_FALSE,
7249
7250 p_count => x_msg_count,
7251
7252 p_data => x_msg_data
7253
7254 );
7255
7256 --DBMS_OUTPUT.PUT_LINE('Exit : find_repository_areas' );
7257
7258 EXCEPTION
7259
7260 WHEN FND_API.G_EXC_ERROR THEN
7261
7262 ROLLBACK TO Find_RepositoryAreas;
7263
7264 x_return_status := FND_API.G_RET_STS_ERROR;
7265
7266 -- Standard call to get message count and if count=1, get the message
7267
7268 FND_MSG_PUB.Count_And_Get (
7269
7270 p_encoded => FND_API.G_FALSE,
7271
7272 p_count => x_msg_count,
7273
7274 p_data => x_msg_data
7275
7276 );
7277
7278 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7279
7280 ROLLBACK TO Find_RepositoryAreas;
7281
7282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7283
7284 -- Standard call to get message count and if count=1, get the message
7285
7286 FND_MSG_PUB.Count_And_Get (
7287
7288 p_encoded => FND_API.G_FALSE,
7289
7290 p_count => x_msg_count,
7291
7292 p_data => x_msg_data
7293
7294 );
7295
7296 WHEN OTHERS THEN
7297
7298 ROLLBACK TO Find_RepositoryAreas;
7299
7300 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7301
7302 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
7303
7304 THEN
7305
7306 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
7307
7308 END IF;
7309
7310 -- Standard call to get message count and if count=1, get the message
7311
7312 FND_MSG_PUB.Count_And_Get (
7313
7314 p_encoded => FND_API.G_FALSE,
7315
7316 p_count => x_msg_count,
7317
7318 p_data => x_msg_data
7319
7320 );
7321
7322 --
7323
7324 END find_repository_areas;
7325
7326 --
7327
7328 --------------------------------------------------------------------------------
7329
7330 -- Start of comments
7331
7332 -- API name : Content_Search
7333
7334 -- Type : Group or Public
7335
7336 -- Pre-reqs : None
7337
7338 -- Function : Accept a search string of a standardized syntax for the
7339
7340 -- searching of one or more repositories for which
7341
7342 -- this repository API supports. The API name will be
7343
7344 -- registered within the tables amv_d_entities_%,
7345
7346 -- amv_d_ent_attributes_tl, and amv_d_ent_attributes_b
7347
7348 -- with the later table\022s column, FUNCTION_CALL set to
7349
7350 -- {user-defined name of [package.]procedure} (i.e. the
7351
7352 -- name of this API).
7353
7354 -- As the value stored within the column FUNCTION_CALL will be
7355
7356 -- concatenated along with a pre-determined procedure
7357
7358 -- specification for participation in a dynamic PL/SQL call,
7359
7360 -- it is imperative that this value conforms to a valid
7361
7362 -- Oracle PL/SQL [package.]procedure name.
7363
7364 --
7365
7366 -- Marketing Encyclopedia (MES) will employ this procedure
7367
7368 -- within its Search API and screens to retrieve and filter
7369
7370 -- another repository's data that meets the specified
7371
7372 -- search criteria passed in.
7373
7374 --
7375
7376 -- Parameters (Standard parameters not mentioned):
7377
7378 -- IN : p_imt_search_string IN VARCHAR2(400) Required
7379
7380 -- Search string defining what to search in interMedia
7381
7382 -- Text syntax. The intent is for this API to accept
7383
7384 -- the string as-is, and drop the string into a
7385
7386 -- Dynamic SQL statement containing the iMT CONTAINS()
7387
7388 -- clause for immediate execution.
7389
7390 --
7391
7392 -- Note, this string will NOT include the iMT keyword
7393
7394 -- CONTAINS along with it's parentheses, just a valid
7395
7396 -- string that can be dropped as-is into the CONTAINS
7397
7398 -- clause.
7399
7400 --
7401
7402 -- : p_search_param_array IN amv_searchpar_array_type Required
7403
7404 -- Array of object amv_searchpar_obj_type listing values
7405
7406 -- to search against database columns that are not
7407
7408 -- interMedia Text enabled.
7409
7410 --
7411
7412 -- The attributes of the object follow:
7413
7414 --
7415
7416 -- : operator IN VARCHAR2(30) Required
7417
7418 --
7419
7420 -- Oracle operators consisting values in
7421
7422 -- {=,<>IN,NOT IN,LIKE,NOT LIKE}.
7423
7424 --
7425
7426 -- : string_value IN VARCHAR2(400) Required
7427
7428 --
7429
7430 -- Value portion of the search in string form.
7431
7432 --
7433
7434 -- The format of the two columns of this object type
7435
7436 -- is such that the API will be able to concatenate
7437
7438 -- these values with appropriate white space and the
7439
7440 -- search source column name; This would form a
7441
7442 -- syntactically valid SQL predicate for construction
7443
7444 -- of a Dynamic SQL Statement.
7445
7446 --
7447
7448 -- Example:
7449
7450 --
7451
7452 -- col_name||\022 \021||operator||\022 \021||string_value
7453
7454 --
7455
7456 -- The string_value will conform to the proper SQL
7457
7458 -- syntax for it\022s corresponding operator. (e.g. the
7459
7460 -- string_value will be enclosed in parentheses for
7461
7462 -- the IN operator). As there could be multiple
7463
7464 -- string_values, this API must be able to build a
7465
7466 -- Dynamic SQL statement using all cells of this array.
7467
7468 --
7469
7470 -- : p_area_array IN amv_area_array_type Optional
7471
7472 -- Array structure that lists a subset of all areas
7473
7474 -- of the repository for which this API is based. If the
7475
7476 -- array is NULL (by default), then all areas are to be
7477
7478 -- searched. Areas listed within this array must, for
7479
7480 -- validation purposes, be registered under the MES tables
7481
7482 -- amv_d_entities_%, amv_d_ent_attributes_% and
7483
7484 -- amv_d_ attrib_operators. Valid areas will be
7485
7486 -- identified in the column
7487
7488 -- amv_d_ent_attributes_b.column_name.
7489
7490 --
7491
7492 -- The main AMV Search API will only recognize areas
7493
7494 -- defined within this table. The API will also refer to
7495
7496 -- the status column of this table to ignore areas
7497
7498 -- where this column's value is set to "disabled".
7499
7500 --
7501
7502 -- : p_user_id IN NUMBER Required
7503
7504 -- Identifier from FND that declares the end-user. This
7505
7506 -- API may required the ID to filter privileged items.
7507
7508 --
7509
7510 -- : p_request_array IN amv_request_array_type Required
7511
7512 -- Object structure that specifies and controls a sliding
7513
7514 -- window to the retrieved LOV results set (i.e. restricts
7515
7516 -- the subset of rows returned, and controls its starting
7517
7518 -- and ending record position of the complete set of rows
7519
7520 -- that could potentially be retrieved). See package
7521
7522 -- amv_utility_pub for further specifications to the
7523
7524 -- object's structure. The attributes of the object and
7525
7526 -- their description follow:
7527
7528 --
7529
7530 -- records_requested IN NUMBER
7531
7532 -- Specifies the maximum number of records to return
7533
7534 -- in the varray results subset (Defaults to
7535
7536 -- (amv_utility_pub.g_amv_max_varray_size).
7537
7538 --
7539
7540 -- start_record_position IN NUMBER
7541
7542 -- Specifies a subscript into the varray results
7543
7544 -- set for the first record to be returned in the
7545
7546 -- retrieval subset. Usually used in conjunction
7547
7548 -- with p_request_obj.next_record_position
7549
7550 -- (Default 1 ).
7551
7552 --
7553
7554 -- return_total_count_flag IN VARCHAR2
7555
7556 -- Flag consisting of the values {fnd_api.g_true,
7557
7558 -- fnd_api.g_false} to specify whether
7559
7560 -- p_request_obj.total_record_count is
7561
7562 -- derived, albeit at a possible cost to resources
7563
7564 -- (Default fnd_api.g_false).
7565
7566 --
7567
7568 -- OUT : x_return_obj OUT OBJ_TYPE
7569
7570 -- Object structure that reports information about the
7571
7572 -- retrieved results set defined by p_request_obj.
7573
7574 -- See package amv_utility_pub for further
7575
7576 -- specifications to the object's structure.
7577
7578 -- Object structure of:
7579
7580 --
7581
7582 -- returned_record_count OUT NUMBER
7583
7584 -- Indicates the total number of records returned
7585
7586 -- for the retrieved subset. This value will not
7587
7588 -- exceed p_request_obj.records_requested.
7589
7590 --
7591
7592 -- next_record_position OUT NUMBER
7593
7594 -- Indicates the subscript to the varray that is the
7595
7596 -- starting point to the next subset of records in
7597
7598 -- the set (base 1; that is, the first record of the
7599
7600 -- set is one, NOT zero). Will return 0 if there are
7601
7602 -- no more rows.
7603
7604 --
7605
7606 -- total_record_count OUT NUMBER
7607
7608 -- Indicates the total record count in the complete
7609
7610 -- varray retrieval set only if
7611
7612 -- p_request_obj.return_total_count is set
7613
7614 -- to fnd_api.g_true; Otherwise undefined.
7615
7616 --
7617
7618 -- : x_searchres_array OUT ARRAY_TYPE
7619
7620 -- Varying Array of Object amv_searchres_obj_type that
7621
7622 -- holds the resulting search matches.
7623
7624 --
7625
7626 -- title IN VARCHAR2(80)
7627
7628 -- Title of the item that met the search criteria
7629
7630 -- provided.
7631
7632 --
7633
7634 -- url_string IN VARCHAR2(2000)
7635
7636 -- URL of the item that met the search. If this item
7637
7638 -- is a file, then it will conform to MIME types.
7639
7640 -- If the item has it's body of a table column, then
7641
7642 -- the URL will point to an appropriate viewer with
7643
7644 -- the table column provided as a parameter into the
7645
7646 -- viewer call.
7647
7648 --
7649
7650 -- description IN VARCHAR2(200)
7651
7652 -- Abbreviated description of the item that met the
7653
7654 -- search criteria provided.
7655
7656 --
7657
7658 -- score IN NUMBER
7659
7660 -- Weighted score of the item that met the search.
7661
7662 -- The determination of the score is derived by
7663
7664 -- interMedia Text ranged 0 to 100 with 100 being
7665
7666 -- the best score. Exact matches against table
7667
7668 -- columns which are not interMedia Text enabled will
7669
7670 -- automatically score 100.
7671
7672 --
7673
7674 -- area_id IN VARCHAR2(30)
7675
7676 -- The area identifier of the area code.
7677
7678 -- Corresponds to the column
7679
7680 -- amv_d_ent_attributes_b.column_name where
7681
7682 -- amv_d_ent_attributes_b.usage_indicator = 'ASRA'
7683
7684 --
7685
7686 -- area_code IN VARCHAR2(30)
7687
7688 -- The area code of the repository for which this API
7689
7690 -- supports. Valid values will be found within the
7691
7692 -- column amv_d_ent_attributes_b.column_name where
7693
7694 -- amv_d_ent_attributes_b.usage_indicator = 'ASRA'
7695
7696 --
7697
7698 -- user1 - user3 IN VARCHAR2(255)
7699
7700 -- Unused columns that exist for customized needs.
7701
7702 --
7703
7704 --
7705
7706 -- Version : Current version 1.0
7707
7708 -- {add comments here}
7709
7710 -- Previous version 1.0
7711
7712 -- Initial version 1.0
7713
7714 -- End of comments
7715
7716 --
7717
7718 PROCEDURE Content_Search
7719
7720 (p_api_version IN NUMBER,
7721
7722 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
7723
7724 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
7725
7726 x_return_status OUT NOCOPY VARCHAR2,
7727
7728 x_msg_count OUT NOCOPY NUMBER,
7729
7730 x_msg_data OUT NOCOPY VARCHAR2,
7731
7732 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
7733
7734 p_application_id IN NUMBER,
7735
7736 p_area_array IN amv_char_varray_type,
7737
7738 p_content_array IN amv_char_varray_type,
7739
7740 p_param_array IN amv_searchpar_varray_type,
7741
7742 p_imt_string IN VARCHAR2 := FND_API.G_MISS_CHAR,
7743
7744 p_days IN NUMBER := FND_API.G_MISS_NUM,
7745
7746 p_user_id IN NUMBER := FND_API.G_MISS_NUM,
7747
7748 p_category_id IN amv_number_varray_type,
7749
7750 p_include_subcats IN VARCHAR2 := FND_API.G_FALSE,
7751
7752 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
7753
7754 p_request_obj IN amv_request_obj_type,
7755
7756 x_return_obj OUT NOCOPY amv_return_obj_type,
7757
7758 x_searchres_array OUT NOCOPY amv_searchres_varray_type)
7759
7760 IS
7761
7762 l_api_name CONSTANT VARCHAR2(30) := 'Content_Search';
7763
7764 l_api_version CONSTANT NUMBER := 1.0;
7765
7766 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
7767
7768 --
7769
7770 l_resource_id number;
7771
7772 l_user_id number;
7773
7774 l_login_user_id number;
7775
7776 l_login_user_status varchar2(30);
7777
7778 l_Error_Msg varchar2(2000);
7779
7780 l_Error_Token varchar2(80);
7781
7782 l_object_version_number number := 1;
7783
7784 l_application_id number := 520;
7785
7786
7787
7788 l_optional_array amv_char_varray_type;
7789
7790 l_required_array amv_char_varray_type;
7791
7792 l_excluded_array amv_char_varray_type;
7793
7794 l_imt_string varchar2(4000);
7795
7796 l_days NUMBER;
7797
7798
7799
7800 l_channel_array amv_number_varray_type;
7801
7802 l_category_array amv_number_varray_type;
7803
7804 l_null varchar2(1) := null;
7805
7806 l_rec_count number := 0;
7807
7808
7809
7810 l_id_insert_status varchar2(1);
7811
7812 l_user_status varchar2(1);
7813
7814 l_channel_search varchar2(1) := FND_API.G_FALSE;
7815
7816 l_category_search varchar2(1) := FND_API.G_FALSE;
7817
7818 l_search_level varchar2(20);
7819
7820 l_keywords_search varchar2(1);
7821
7822 l_include_chns varchar2(1) := FND_API.G_TRUE;
7823
7824 l_excluded_flag varchar2(1);
7825
7826 l_start_with number := 1;
7827
7828 l_total_count number := 0;
7829
7830
7831
7832 l_id number;
7833
7834 cursor id_csr is select id from amv_temp_ids;
7835
7836 --
7837
7838 BEGIN
7839
7840 --DBMS_OUTPUT.PUT_LINE('Enter : Content_Search' );
7841
7842 -- Standard begin of API savepoint
7843
7844 SAVEPOINT Content_Search;
7845
7846 -- Standard call to check for call compatibility.
7847
7848 IF NOT FND_API.Compatible_API_Call (
7849
7850 l_api_version,
7851
7852 p_api_version,
7853
7854 l_api_name,
7855
7856 G_PKG_NAME)
7857
7858 THEN
7859
7860 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7861
7862 END IF;
7863
7864 -- Debug Message
7865
7866 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
7867
7868 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
7869
7870 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
7871
7872 FND_MSG_PUB.Add;
7873
7874 END IF;
7875
7876 --Initialize message list if p_init_msg_list is TRUE.
7877
7878 IF FND_API.To_Boolean (p_init_msg_list) THEN
7879
7880 FND_MSG_PUB.initialize;
7881
7882 END IF;
7883
7884 -- Get the current (login) user id.
7885
7886 AMV_UTILITY_PVT.Get_UserInfo(
7887
7888 x_resource_id => l_resource_id,
7889
7890 x_user_id => l_user_id,
7891
7892 x_login_id => l_login_user_id,
7893
7894 x_user_status => l_login_user_status
7895
7896 );
7897
7898 -- check login user
7899
7900 IF (p_check_login_user = FND_API.G_TRUE) THEN
7901
7902 -- Check if user is login and has the required privilege.
7903
7904 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
7905
7906 -- User is not login.
7907
7908 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
7909
7910 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
7911
7912 FND_MSG_PUB.Add;
7913
7914 END IF;
7915
7916 RAISE FND_API.G_EXC_ERROR;
7917
7918 END IF;
7919
7920 END IF;
7921
7922 -- Initialize API return status to sucess
7923
7924 x_return_status := FND_API.G_RET_STS_SUCCESS;
7925
7926
7927
7928 -- set days for last update days
7929
7930 IF p_days = FND_API.G_MISS_NUM OR
7931
7932 p_days is null
7933
7934 THEN
7935
7936 l_days := -1;
7937
7938 ELSE
7939
7940 l_days := p_days;
7941
7942 END IF;
7943
7944
7945
7946 -- build an array of optional, required and excluded parameters
7947
7948 parse_parameter_array(p_param_array,
7949
7950 l_optional_array,
7951
7952 l_required_array,
7953
7954 l_excluded_array,
7955
7956 l_keywords_search);
7957
7958
7959
7960 IF p_imt_string = FND_API.G_MISS_CHAR OR p_imt_string is null THEN
7961
7962 -- build imt search string
7963
7964 build_imt_string(l_optional_array,
7965
7966 l_required_array,
7967
7968 l_excluded_array,
7969
7970 l_excluded_flag,
7971
7972 l_imt_string);
7973
7974 IF l_imt_string is null THEN
7975
7976 -- Must pass query string
7977
7978 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
7979
7980 FND_MESSAGE.Set_name('AMV','AMV_SRCH_QRY_STR_NULL');
7981
7982 FND_MSG_PUB.Add;
7983
7984 END IF;
7985
7986 RAISE FND_API.G_EXC_ERROR;
7987
7988 END IF;
7989
7990 ELSE
7991
7992 l_imt_string := p_imt_string;
7993
7994 END IF;
7995
7996
7997
7998 l_category_array := amv_number_varray_type();
7999
8000 l_channel_array := amv_number_varray_type();
8001
8002
8003
8004 -- determine for search level
8005
8006 IF p_user_id = FND_API.G_MISS_NUM OR
8007
8008 p_user_id is null
8009
8010 THEN
8011
8012 -- does not include item channel match if categories are null
8013
8014 -- uncomment to search for items not associated to categories
8015
8016 IF p_category_id.count = 0 THEN
8017
8018 l_include_chns := FND_API.G_FALSE;
8019
8020 l_category_search := FND_API.G_TRUE;
8021
8022 ELSE
8023
8024 -- search under the category passed
8025
8026 get_category_channel ( p_category_id => p_category_id,
8027
8028 p_application_id => p_application_id,
8029
8030 p_include_subcats => p_include_subcats,
8031
8032 x_category_array => l_category_array,
8033
8034 x_channel_array => l_channel_array );
8035
8036 IF l_category_array.count > 0 THEN
8037
8038 -- set flag for category level search
8039
8040 l_category_search := FND_API.G_TRUE;
8041
8042 END IF;
8043
8044
8045
8046 IF l_channel_array.count > 0 THEN
8047
8048 -- set flag for channel level search
8049
8050 l_channel_search := FND_API.G_TRUE;
8051
8052 END IF;
8053
8054 END IF;
8055
8056 ELSE
8057
8058 -- search based on user privilege
8059
8060 -- get the list of categories accessable by the user
8061
8062 get_app_categories( p_application_id => p_application_id,
8063
8064 x_category_array => l_category_array);
8065
8066
8067
8068 IF l_category_array.count > 0 THEN
8069
8070 -- set flag for category level search
8071
8072 l_category_search := FND_API.G_TRUE;
8073
8074 END IF;
8075
8076
8077
8078 -- get the list of channels which is accessable by the user
8079
8080 get_user_accessable_channels( p_user_id => p_user_id,
8081
8082 p_application_id => p_application_id,
8083
8084 x_channel_array => l_channel_array);
8085
8086
8087
8088 IF l_channel_array.count > 0 THEN
8089
8090 -- set flag for channel level search
8091
8092 l_channel_search := FND_API.G_TRUE;
8093
8094 END IF;
8095
8096
8097
8098 IF l_channel_array.count = 0 AND l_category_array.count = 0 THEN
8099
8100 -- user does not have access to any channels or categories
8101
8102 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
8103
8104 THEN
8105
8106 FND_MESSAGE.Set_name('AMV','AMV_CAT_USR_NOACCS');
8107
8108 FND_MESSAGE.Set_Token('TKN',p_user_id);
8109
8110 FND_MSG_PUB.Add;
8111
8112 END IF;
8113
8114 RAISE FND_API.G_EXC_ERROR;
8115
8116 END IF;
8117
8118 END IF;
8119
8120 --
8121
8122 -- initialize search array
8123
8124 x_searchres_array := amv_searchres_varray_type();
8125
8126
8127
8128 -- set start index
8129
8130 l_start_with := p_request_obj.start_record_position;
8131
8132
8133
8134 -- check if search area and content exists
8135
8136
8137
8138 -- perform search
8139
8140 -- insert the list of categories to be search and perform search
8141
8142 IF l_category_search = FND_API.G_TRUE THEN
8143
8144 insert_temp_numbers(p_id_array => l_category_array,
8145
8146 x_status => l_id_insert_status);
8147
8148
8149
8150 l_search_level := G_CATEGORY;
8151
8152 search_items(
8153
8154 p_area_array => p_area_array,
8155
8156 p_content_array => p_content_array,
8157
8158 p_imt_string => l_imt_string,
8159
8160 p_optional_array => l_optional_array,
8161
8162 p_required_array => l_required_array,
8163
8164 p_excluded_array => l_excluded_array,
8165
8166 p_keywords_search => l_keywords_search,
8167
8168 p_excluded_flag => l_excluded_flag,
8169
8170 p_application_id => p_application_id,
8171
8172 p_days => l_days,
8173
8174 p_include_chns => l_include_chns,
8175
8176 p_search_level => l_search_level,
8177
8178 p_external_contents => p_external_contents,
8179
8180 p_records_requested => p_request_obj.records_requested,
8181
8182 x_start_with => l_start_with,
8183
8184 x_results_populated => l_rec_count,
8185
8186 x_total_count => l_total_count,
8187
8188 x_searchres_array => x_searchres_array);
8189
8190 END IF;
8191
8192
8193
8194 -- insert the list of channels to be search and perform search
8195
8196 IF l_channel_search = FND_API.G_TRUE THEN
8197
8198 insert_temp_numbers(p_id_array => l_channel_array,
8199
8200 x_status => l_id_insert_status);
8201
8202 l_search_level := G_CHANNEL;
8203
8204 search_items(
8205
8206 p_area_array => p_area_array,
8207
8208 p_content_array => p_content_array,
8209
8210 p_imt_string => l_imt_string,
8211
8212 p_optional_array => l_optional_array,
8213
8214 p_required_array => l_required_array,
8215
8216 p_excluded_array => l_excluded_array,
8217
8218 p_keywords_search => l_keywords_search,
8219
8220 p_excluded_flag => l_excluded_flag,
8221
8222 p_application_id => p_application_id,
8223
8224 p_days => l_days,
8225
8226 p_include_chns => l_include_chns,
8227
8228 p_search_level => l_search_level,
8229
8230 p_external_contents => p_external_contents,
8231
8232 p_records_requested => p_request_obj.records_requested,
8233
8234 x_start_with => l_start_with,
8235
8236 x_results_populated => l_rec_count,
8237
8238 x_total_count => l_total_count,
8239
8240 x_searchres_array => x_searchres_array);
8241
8242 END IF;
8243
8244 --
8245
8246 --
8247
8248 x_return_obj.returned_record_count := l_rec_count;
8249
8250 x_return_obj.next_record_position :=
8251
8252 p_request_obj.start_record_position + l_rec_count;
8253
8254 x_return_obj.total_record_count := l_total_count;
8255
8256
8257
8258 /*
8259
8260 x_return_obj := amv_return_obj_type(
8261
8262 l_rec_count,
8263
8264 p_request_obj.start_record_position + l_rec_count,
8265
8266 l_total_count);
8267
8268 */
8269
8270
8271
8272 /*
8273
8274 -- Success message
8275
8276 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
8277
8278 THEN
8279
8280 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
8281
8282 FND_MESSAGE.Set_Token('ROW', l_full_name);
8283
8284 FND_MSG_PUB.Add;
8285
8286 END IF;
8287
8288 */
8289
8290 -- Debug Message
8291
8292 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
8293
8294 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
8295
8296 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
8297
8298 FND_MSG_PUB.Add;
8299
8300 END IF;
8301
8302 --Standard call to get message count and if count=1, get the message
8303
8304 FND_MSG_PUB.Count_And_Get (
8305
8306 p_encoded => FND_API.G_FALSE,
8307
8308 p_count => x_msg_count,
8309
8310 p_data => x_msg_data
8311
8312 );
8313
8314 --DBMS_OUTPUT.PUT_LINE('Exit : Content_Search' );
8315
8316 EXCEPTION
8317
8318 WHEN FND_API.G_EXC_ERROR THEN
8319
8320 ROLLBACK TO Content_Search;
8321
8322 x_return_status := FND_API.G_RET_STS_ERROR;
8323
8324 -- Standard call to get message count and if count=1, get the message
8325
8326 FND_MSG_PUB.Count_And_Get (
8327
8328 p_encoded => FND_API.G_FALSE,
8329
8330 p_count => x_msg_count,
8331
8332 p_data => x_msg_data
8333
8334 );
8335
8336 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8337
8338 ROLLBACK TO Content_Search;
8339
8340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8341
8342 -- Standard call to get message count and if count=1, get the message
8343
8344 FND_MSG_PUB.Count_And_Get (
8345
8346 p_encoded => FND_API.G_FALSE,
8347
8348 p_count => x_msg_count,
8349
8350 p_data => x_msg_data
8351
8352 );
8353
8354 WHEN OTHERS THEN
8355
8356 ROLLBACK TO Content_Search;
8357
8358 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8359
8360 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
8361
8362 THEN
8363
8364 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
8365
8366 END IF;
8367
8368 -- Standard call to get message count and if count=1, get the message
8369
8370 FND_MSG_PUB.Count_And_Get (
8371
8372 p_encoded => FND_API.G_FALSE,
8373
8374 p_count => x_msg_count,
8375
8376 p_data => x_msg_data
8377
8378 );
8379
8380 --
8381
8382 END content_search;
8383
8384 --
8385
8386 --------------------------------------------------------------------------------
8387
8388 --------------------------------------------------------------------------------
8389
8390 END amv_search_pvt;
8391