1 PACKAGE BODY HZ_WORD_CONDITIONS_PKG as
2 /*$Header: ARHDQWCB.pls 120.3 2006/03/22 22:26:36 repuri noship $ */
3
4 -- This is the TCA seeded condition function and any future conditions we seed
5 -- will have their logic here
6 FUNCTION tca_eval_condition_rec(
7 p_input_str IN VARCHAR2,
8 p_token_str IN VARCHAR2,
9 p_repl_str IN VARCHAR2,
10 p_condition_id IN NUMBER,
11 p_user_spec_cond_val IN VARCHAR2
12 )
13 RETURN VARCHAR2
14 IS
15 country_str varchar2(100);
16 sql_str varchar2(2000);
17 result_str varchar2(200);
18 adjusted_user_val varchar2(2000);
19 BEGIN
20 CASE p_condition_id
21
22 -- Start of String
23 WHEN 1
24 THEN
25 -- the replacement does not make the original word go to null
26 IF p_repl_str IS NOT NULL
27 THEN
28 IF p_input_str = p_token_str
29 THEN
30 return 'N' ;
31 ELSE
32 --DELIMITED or NON_DELIMITED
33 IF (p_input_str like p_token_str || ' %') OR (p_input_str like p_token_str || '%')
34 THEN
35 return 'Y' ;
36 ELSE
37 return 'N' ;
38 END IF;
39 END IF;
40 -- the replacement makes the original word go to null
41 ELSE
42 IF p_input_str = p_token_str
43 THEN
44 return 'N' ;
45 ELSE
46 --DELIMITED or NON_DELIMITED
47 IF ((p_input_str like p_token_str || ' %') OR (p_input_str like p_token_str || '%'))and replace(p_input_str,p_token_str, p_repl_str) IS NOT NULL
48 THEN
49 return 'Y' ;
50 ELSE
51 return 'N' ;
52 END IF;
53 END IF;
54 END IF ;
55
56 -- End of String
57 WHEN 2
58 THEN
59 -- the replacement does not make the original word go to null
60 IF p_repl_str IS NOT NULL
61 THEN
62 IF p_input_str = p_token_str
63 THEN
64 return 'N' ;
65 ELSE
66 -- DELIMITED OR NON_DELIMITED
67 IF (p_input_str like '% ' || p_token_str ) OR (p_input_str like '%' || p_token_str )
68 THEN
69 return 'Y' ;
70 ELSE
71 return 'N' ;
72 END IF;
73 END IF;
74 -- the replacement makes the original word go to null
75 ELSE
76 IF p_input_str = p_token_str
77 THEN
78 return 'N' ;
79 ELSE
80 -- DELIMITED OR NON_DELIMITED
81 IF ((p_input_str like '% ' || p_token_str ) OR (p_input_str like '%' || p_token_str )) and replace(p_input_str,p_token_str, p_repl_str) IS NOT NULL
82 THEN
83 return 'Y' ;
84 ELSE
85 return 'N' ;
86 END IF;
87 END IF;
88 END IF ;
89 -- Country Equals
90 WHEN 3
91 THEN
92 -- Only one user specified country
93 IF ( instrb(p_user_spec_cond_val,',') = 0 )
94 THEN
95 IF ( get_gbl_condition_rec_value('PARTY_SITES', 'COUNTRY') = p_user_spec_cond_val )
96 THEN
97 return 'Y' ;
98 ELSE
99 return 'N' ;
100 END IF ;
101 -- Range of user specified countries
102 ELSE
103 country_str := get_gbl_condition_rec_value('PARTY_SITES', 'COUNTRY');
104
105 adjusted_user_val := replace(p_user_spec_cond_val,',',''',''');
106
107 adjusted_user_val := ''''||adjusted_user_val||'''';
108
109 ----dbmsput.put_line(adjusted_user_val);
110
111 sql_str := 'select ''Y'' from dual where ''' ||country_str ||''' IN ('||adjusted_user_val||')';
112
113 ----dbmsput.put_line(sql_str);
114
115 begin
116
117 EXECUTE IMMEDIATE sql_str into result_str ;
118
119 ----dbmsput.put_line('result_str is ' || result_str );
120
121
122 EXCEPTION
123 WHEN OTHERS THEN
124 result_str := 'N';
125 end ;
126
127 return result_str ;
128
129
130 END IF ;
131 -- Country Equals
132 WHEN 4
133 THEN
134 -- Only one user specified country
135 IF ( instrb(p_user_spec_cond_val,',') = 0 )
136 THEN
137 IF ( get_gbl_condition_rec_value('PARTY_SITES', 'COUNTRY') <> p_user_spec_cond_val )
138 THEN
139 return 'Y' ;
140 ELSE
141 return 'N' ;
142 END IF ;
143 -- Range of user specified countries
144 ELSE
145 country_str := get_gbl_condition_rec_value('PARTY_SITES', 'COUNTRY');
146
147 adjusted_user_val := replace(p_user_spec_cond_val,',',''',''');
148
149 adjusted_user_val := ''''||adjusted_user_val||'''';
150
151 ----dbmsput.put_line(adjusted_user_val);
152
153 sql_str := 'select ''Y'' from dual where ''' ||country_str ||''' NOT IN ('||adjusted_user_val||')';
154
155 ------dbmsput.put_line(sql_str);
156
157 begin
158
159 EXECUTE IMMEDIATE sql_str into result_str ;
160
161 ----dbmsput.put_line('result_str is ' || result_str );
162
163
164 EXCEPTION
165 WHEN OTHERS THEN
166 result_str := 'N';
167 end ;
168
169 return result_str ;
170
171
172 END IF ;
173
174 -- If we get to this part of the CASE, we return an 'N' instead of erroring out
175 ELSE
176 return 'N' ;
177 END CASE;
178
179 END ;
180
181 /*** This will be used to determine if this attribute is a condition attribute ***/
182 FUNCTION is_a_cond_attrib (p_attribute_id IN NUMBER )
183 RETURN BOOLEAN
184 IS
185 BEGIN
186
187 FOR att_cur in
188 (select condition_id
189 from hz_word_rpl_cond_attribs
190 where assoc_cond_attrib_id = p_attribute_id
191 and rownum < 2
192 )
193 LOOP
194
195 return TRUE ;
196
197 END LOOP ;
198
199 return FALSE ;
200 END ;
201
202
203
204
205 /*** This will be used by search/staging to populate the global condition record ***/
206 PROCEDURE set_gbl_condition_rec (p_attribute_id IN NUMBER, p_attribute_value IN VARCHAR2)
207 IS
208 BEGIN
209 gbl_condition_rec(p_attribute_id) := p_attribute_value ;
210 END ;
211
212 /*** This will be used to return the value of condition record *****/
213 FUNCTION get_gbl_condition_rec_value( p_entity IN VARCHAR2, p_attribute_name IN VARCHAR2 )
214 RETURN VARCHAR2
215 IS
216 BEGIN
217 FOR att_cur in
218 (select attribute_id
219 from hz_trans_attributes_vl
220 where attribute_name = p_attribute_name
221 and entity_name = p_entity)
222 LOOP
223 -- In the get we need to be careful
224 -- since the attribute id may not exists as part of the global record
225 -- if either the match rule does not have the attribute as part of its definition
226 -- or the user does not even pass criteria at that level ( for example an entire
227 -- party site search list may be empty).
228
229 IF gbl_condition_rec.EXISTS(att_cur.attribute_id)
230 THEN
231 return gbl_condition_rec(att_cur.attribute_id) ;
232 ELSE
233 return null ;
234 END IF ;
235 END LOOP ;
236 return null ;
237 END ;
238
239 /********* This will be a wrapper on top of the condition function, that would be used by
240 HZ_TRANS_PKG, so that the user does not have to modify HZ_TRANS_PKG directly.
241 A user who wants to seed a new condition function would call the condition function
242 in the ELSE section of case or modify it in a way he/she sees fit.
243 *****************/
244
245 FUNCTION evaluate_condition (
246 p_input_str IN VARCHAR2,
247 p_token_str IN VARCHAR2,
248 p_repl_str IN VARCHAR2,
249 p_condition_id IN NUMBER,
250 p_user_spec_cond_val IN VARCHAR2
251 )
252 RETURN BOOLEAN IS
253 result_str VARCHAR2(1) ;
254 user_defined_proc_name VARCHAR2(600);
255 sql_str VARCHAR2(2000);
256 BEGIN
257 -- This will address seeded conditions for all conditions that we ship
258 -- For these call the tca seeded condition function
259 IF p_condition_id < 10000 THEN
260 result_str := tca_eval_condition_rec(
261 p_input_str,
262 p_token_str,
263 p_repl_str,
264 p_condition_id,
265 p_user_spec_cond_val
266 ) ;
267 IF result_str = 'Y' THEN
268 return TRUE;
269 ELSE
270 return FALSE;
271 END IF ;
272 -- This section is reserved for calling the user defined condition function dynamically
273 ELSE
274 BEGIN
275 SELECT condition_function INTO user_defined_proc_name
276 FROM HZ_WORD_RPL_CONDS_B
277 WHERE condition_id = p_condition_id ;
278
279 -- Fix for Bug 5007558. Using bind variables for the dynamic procedure input parameters.
280 --dbms_output.put_line('user_defined_proc_name is ' || user_defined_proc_name);
281 sql_str := 'select HZ_WORD_CONDITIONS_PKG.'||user_defined_proc_name||'(:p_input_str,:p_token_str,:p_repl_str, :p_condition_id,:p_user_spec_cond_val) from dual' ;
282 --dbms_output.put_line('SQL string is ' || sql_str);
283 EXECUTE IMMEDIATE sql_str INTO result_str USING p_input_str, p_token_str, p_repl_str, p_condition_id, p_user_spec_cond_val;
284 --dbms_output.put_line('result_str after execute immediate is ' || result_str);
285 EXCEPTION WHEN OTHERS THEN
286 --dbms_output.put_line('in the exception section');
287 --dbms_output.put_line('SQLERRM is - '||sqlerrm);
288 result_str := 'N' ;
289 END ;
290
291 IF result_str = 'Y' THEN
292 return TRUE;
293 ELSE
294 return FALSE;
295 END IF ;
296 END IF ;
297
298 EXCEPTION WHEN OTHERS THEN
299 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
300 FND_MESSAGE.SET_TOKEN('PROC' ,'tca_eval_condition_rec');
301 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
302 FND_MSG_PUB.ADD;
303 RAISE FND_API.G_EXC_ERROR;
304 END ;
305 END ;
[Home] [Help]