[Home] [Help]
PACKAGE BODY: APPS.IEC_WHERECLAUSE_PVT
Source
1 PACKAGE BODY IEC_WHERECLAUSE_PVT AS
2 /* $Header: IECVWHCB.pls 115.7.1158.3 2002/10/02 18:13:32 lcrew ship $ */
3
4
5 PROCEDURE GETWHERECLAUSE
6 (P_OWNER_ID NUMBER
7 ,P_OWNER_TYPE VARCHAR2
8 ,WHERECLAUSE OUT VARCHAR2) AS
9 e_operror EXCEPTION;
10 TYPE RULE_ID_TBL IS TABLE OF iec_g_rules.rule_id%TYPE;
11 TYPE CRITERIA_ID_TBL IS TABLE OF iec_g_field_criteria.criteria_id%TYPE;
12 TYPE COMBINATION_CODE_TBL IS TABLE OF iec_g_field_criteria.combination_code%TYPE;
13 TYPE FIELD_NAME_TBL IS TABLE OF iec_g_fields.field_name%TYPE;
14 TYPE FIELD_VALUE_TBL IS TABLE OF iec_g_fields.field_value%TYPE;
15 TYPE OPERATOR_CODE_TBL IS TABLE OF iec_g_fields.operator_code%TYPE;
16 TYPE SQL_OPERATOR_TBL IS TABLE OF iec_o_alg_op_defs_b.sql_operator%TYPE;
17 TYPE UNARY_FLAG_TBL IS TABLE OF iec_o_alg_op_defs_b.is_unary_flag%TYPE;
18 l_rule_id_tbl RULE_ID_TBL;
19 l_criteria_id_tbl CRITERIA_ID_TBL;
20 l_comb_code_tbl COMBINATION_CODE_TBL;
21 l_field_name_tbl FIELD_NAME_TBL;
22 l_field_value_tbl FIELD_VALUE_TBL;
23 l_operator_code_tbl OPERATOR_CODE_TBL;
24 l_sql_operator_tbl SQL_OPERATOR_TBL;
25 l_unary_flag_tbl UNARY_FLAG_TBL;
26 l_rule_flag CHAR(1) := 'Y';
27 l_criteria_flag CHAR(1) := 'Y';
28 l_field_flag CHAR(1) := 'Y';
29 l_where_clause VARCHAR2(4000) := NULL;
30 BEGIN
31 select rule_id bulk collect into l_rule_id_tbl from iec_g_rules
32 where owner_id = p_owner_id and owner_type_code = p_owner_type;
33
34 for j in 1 ..l_rule_id_tbl.COUNT
35 loop
36 if(l_rule_flag <> 'Y') then
37 l_where_clause := l_where_clause || ' OR ( ';
38 else
39 l_where_clause := l_where_clause || ' ( ';
40 l_rule_flag := 'N';
41 end if;
42
43 select criteria_id, combination_code bulk collect into l_criteria_id_tbl, l_comb_code_tbl
44 from iec_g_field_criteria where rule_id = l_rule_id_tbl(j);
45 l_criteria_flag := 'Y';
46 for k in 1 .. l_criteria_id_tbl.COUNT
47 loop
48 if(l_criteria_flag <> 'Y') then
49 l_where_clause := l_where_clause || ' AND ( ';
50 else
51 l_where_clause := l_where_clause || ' ( ';
52 l_criteria_flag := 'N';
53 end if;
54
55 select A.field_name,UPPER(LTRIM(RTRIM(A.field_value,' '),' ')),
56 UPPER(LTRIM(RTRIM(A.operator_code,' '),' ')),
57 UPPER(LTRIM(RTRIM(B.sql_operator,' '),' ')),
58 B.is_unary_flag bulk collect into l_field_name_tbl,l_field_value_tbl,
59 l_operator_code_tbl,l_sql_operator_tbl,
60 l_unary_flag_tbl from iec_g_fields A, iec_o_alg_op_defs_b B
61 where A.criteria_id = l_criteria_id_tbl(k) and A.operator_code = B.operator_code;
62
63 l_field_flag := 'Y';
64 for i in 1 .. l_field_name_tbl.COUNT
65 loop
66 if(l_field_flag <> 'Y') then
67 l_where_clause := l_where_clause || ' ';
68 l_where_clause := l_where_clause || l_comb_code_tbl(k);
69 l_where_clause := l_where_clause || ' ';
70 l_where_clause := l_where_clause || ' ( ';
71 else
72 l_where_clause := l_where_clause || ' ( ';
73 l_field_flag := 'N';
74 end if;
75
76 l_where_clause := l_where_clause || 'UPPER(';
77 l_where_clause := l_where_clause || l_field_name_tbl(i);
78 l_where_clause := l_where_clause || ') ';
79 l_where_clause := l_where_clause || l_sql_operator_tbl(i);
80
81 if(l_unary_flag_tbl(i) = 'N') then
82 l_where_clause := l_where_clause || ' ''';
83 if(l_sql_operator_tbl(i) = 'LIKE') then
84 if(l_operator_code_tbl(i) = 'CONTAINS') then
85 l_where_clause := l_where_clause || '%';
86 l_where_clause := l_where_clause || l_field_value_tbl(i);
87 l_where_clause := l_where_clause || '%';
88 elsif(l_operator_code_tbl(i) = 'BGWITH') then
89 l_where_clause := l_where_clause || l_field_value_tbl(i);
90 l_where_clause := l_where_clause || '%';
91 elsif(l_operator_code_tbl(i) = 'ENDWITH') then
92 l_where_clause := l_where_clause || '%';
93 l_where_clause := l_where_clause || l_field_value_tbl(i);
94 else
95 raise e_operror;
96 end if;
97 else
98 l_where_clause := l_where_clause || l_field_value_tbl(i);
99 end if;
100 l_where_clause := l_where_clause || ''' ';
101 l_where_clause := l_where_clause || ')';
102 else
103 l_where_clause := l_where_clause || ')';
104 end if;
105 end loop;
106
107 l_where_clause := l_where_clause || ' ) ';
108 end loop;
109 l_where_clause := l_where_clause || ' ) ';
110 end loop;
111 whereclause := l_where_clause;
112 EXCEPTION
113 WHEN e_operror THEN
114 whereclause := NULL;
115 WHEN others THEN
116 whereclause := NULL;
117 END GETWHERECLAUSE;
118
119 PROCEDURE GETWHERECLAUSEFORSUBSET
120 (P_OWNER_ID NUMBER
121 ,P_OWNER_TYPE VARCHAR2
122 ,WHERECLAUSE OUT VARCHAR2)AS
123 e_operror EXCEPTION;
124 TYPE RULE_ID_TBL IS TABLE OF iec_g_rules.rule_id%TYPE;
125 TYPE SUBSET_ID_TBL IS TABLE OF iec_g_list_subsets.list_subset_id%TYPE;
126 l_subset_id_tbl SUBSET_ID_TBL;
127 l_rule_id_tbl RULE_ID_TBL;
128 l_default_subset_flag VARCHAR2(1) := NULL;
129 l_where_clause VARCHAR2(4000) := NULL;
130 l_where_clause_sub VARCHAR2(4000) := NULL;
131 BEGIN
132 select default_subset_flag into l_default_subset_flag from iec_g_list_subsets
133 where list_subset_id = p_owner_id ;
134
135 if(l_default_subset_flag is null or l_default_subset_flag <>'Y') then
136 GETWHERECLAUSE(p_owner_id, p_owner_type, l_where_clause);
137 else
138 select rule_id bulk collect into l_rule_id_tbl from iec_g_rules
139 where owner_id = p_owner_id and owner_type_code = p_owner_type;
140 if(l_rule_id_tbl.COUNT >0 ) then
141 GETWHERECLAUSE(p_owner_id, p_owner_type, l_where_clause);
142 else
143 select list_subset_id bulk collect into l_subset_id_tbl from iec_g_list_subsets where list_header_id in
144 (select list_header_id from iec_g_list_subsets where list_subset_id = p_owner_id )
145 and ( default_subset_flag <>'Y' or default_subset_flag is null);
146
147 if(l_subset_id_tbl.COUNT = 0) then
148 l_where_clause := NULL;
149 elsif(l_subset_id_tbl.COUNT = 1) then
150 GETWHERECLAUSE(l_subset_id_tbl(1), p_owner_type, l_where_clause_sub);
151 l_where_clause := 'NOT('||l_where_clause_sub||')';
152 else
153 for i in 1 ..l_subset_id_tbl.COUNT-1
154 loop
155 GETWHERECLAUSE(l_subset_id_tbl(i), p_owner_type, l_where_clause_sub);
156 l_where_clause := l_where_clause ||'( '||l_where_clause_sub||')'||'OR';
157 l_where_clause_sub := NULL;
158 end loop;
159 GETWHERECLAUSE(l_subset_id_tbl(l_subset_id_tbl.COUNT), p_owner_type, l_where_clause_sub);
160 l_where_clause := l_where_clause ||'( '||l_where_clause_sub||')';
161 l_where_clause := 'NOT('||l_where_clause||')';
162 end if;
163 end if;
164 end if;
165 whereclause := l_where_clause;
166 EXCEPTION
167 WHEN e_operror THEN
168 whereclause := NULL;
169 WHEN others THEN
170 whereclause := NULL;
171 END GETWHERECLAUSEFORSUBSET;
172
173 PROCEDURE getAMSView( listHeaderId number, viewName out varchar2) AS
174 l_viewname VARCHAR2(100) := NULL;
175 BEGIN
176 select b.tag into l_viewname from ams_list_headers_all a, iec_lookups b where
177 a.list_header_id = listHeaderId and b.lookup_type='IEC_SOURCE_VIEW_MAP' and a.list_source_type = b.lookup_code;
178 viewname := l_viewname;
179 EXCEPTION
180 WHEN others THEN
181 viewname := NULL;
182 END getAMSView;
183 END IEC_WHERECLAUSE_PVT;
184