1 package body FND_FLEX_VALUE_RULES_PKG as
2 /* $Header: AFFFVLRB.pls 120.2.12010000.1 2008/07/25 14:14:52 appldev ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID IN OUT NOCOPY VARCHAR2,
6 X_FLEX_VALUE_RULE_ID in NUMBER,
7 X_FLEX_VALUE_RULE_NAME in VARCHAR2,
8 X_FLEX_VALUE_SET_ID in NUMBER,
9 X_PARENT_FLEX_VALUE_LOW in VARCHAR2,
10 X_PARENT_FLEX_VALUE_HIGH in VARCHAR2,
11 X_ERROR_MESSAGE in VARCHAR2,
12 X_DESCRIPTION in VARCHAR2,
13 X_CREATION_DATE in DATE,
14 X_CREATED_BY in NUMBER,
15 X_LAST_UPDATE_DATE in DATE,
16 X_LAST_UPDATED_BY in NUMBER,
17 X_LAST_UPDATE_LOGIN in NUMBER
18 ) is
19 cursor C is select ROWID from FND_FLEX_VALUE_RULES
20 where FLEX_VALUE_RULE_ID = X_FLEX_VALUE_RULE_ID
21 ;
22 begin
23 insert into FND_FLEX_VALUE_RULES (
24 FLEX_VALUE_RULE_ID,
25 FLEX_VALUE_RULE_NAME,
26 FLEX_VALUE_SET_ID,
27 PARENT_FLEX_VALUE_LOW,
28 PARENT_FLEX_VALUE_HIGH,
29 CREATION_DATE,
30 CREATED_BY,
31 LAST_UPDATE_DATE,
32 LAST_UPDATED_BY,
33 LAST_UPDATE_LOGIN
34 ) values (
35 X_FLEX_VALUE_RULE_ID,
36 X_FLEX_VALUE_RULE_NAME,
37 X_FLEX_VALUE_SET_ID,
38 X_PARENT_FLEX_VALUE_LOW,
39 X_PARENT_FLEX_VALUE_HIGH,
40 X_CREATION_DATE,
41 X_CREATED_BY,
42 X_LAST_UPDATE_DATE,
43 X_LAST_UPDATED_BY,
44 X_LAST_UPDATE_LOGIN
45 );
46
47 insert into FND_FLEX_VALUE_RULES_TL (
48 DESCRIPTION,
49 FLEX_VALUE_RULE_ID,
50 LAST_UPDATE_DATE,
51 LAST_UPDATED_BY,
52 CREATION_DATE,
53 CREATED_BY,
54 LAST_UPDATE_LOGIN,
55 ERROR_MESSAGE,
56 LANGUAGE,
57 SOURCE_LANG
58 ) select
59 X_DESCRIPTION,
60 X_FLEX_VALUE_RULE_ID,
61 X_LAST_UPDATE_DATE,
62 X_LAST_UPDATED_BY,
63 X_CREATION_DATE,
64 X_CREATED_BY,
65 X_LAST_UPDATE_LOGIN,
66 X_ERROR_MESSAGE,
67 L.LANGUAGE_CODE,
68 userenv('LANG')
69 from FND_LANGUAGES L
70 where L.INSTALLED_FLAG in ('I', 'B')
71 and not exists
72 (select NULL
73 from FND_FLEX_VALUE_RULES_TL T
74 where T.FLEX_VALUE_RULE_ID = X_FLEX_VALUE_RULE_ID
75 and T.LANGUAGE = L.LANGUAGE_CODE);
76
77 open c;
78 fetch c into X_ROWID;
79 if (c%notfound) then
80 close c;
81 raise no_data_found;
82 end if;
83 close c;
84
85 end INSERT_ROW;
86
87 procedure LOCK_ROW (
88 X_FLEX_VALUE_RULE_ID in NUMBER,
89 X_FLEX_VALUE_RULE_NAME in VARCHAR2,
90 X_FLEX_VALUE_SET_ID in NUMBER,
91 X_PARENT_FLEX_VALUE_LOW in VARCHAR2,
92 X_PARENT_FLEX_VALUE_HIGH in VARCHAR2,
93 X_ERROR_MESSAGE in VARCHAR2,
94 X_DESCRIPTION in VARCHAR2
95 ) is
96 cursor c is select
97 FLEX_VALUE_RULE_NAME,
98 FLEX_VALUE_SET_ID,
99 PARENT_FLEX_VALUE_LOW,
100 PARENT_FLEX_VALUE_HIGH
101 from FND_FLEX_VALUE_RULES
102 where FLEX_VALUE_RULE_ID = X_FLEX_VALUE_RULE_ID
103 for update of FLEX_VALUE_RULE_ID nowait;
104 recinfo c%rowtype;
105
106 cursor c1 is select
107 ERROR_MESSAGE,
108 DESCRIPTION,
109 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
110 from FND_FLEX_VALUE_RULES_TL
111 where FLEX_VALUE_RULE_ID = X_FLEX_VALUE_RULE_ID
112 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
113 for update of FLEX_VALUE_RULE_ID nowait;
114 begin
115 open c;
116 fetch c into recinfo;
117 if (c%notfound) then
118 close c;
119 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
120 app_exception.raise_exception;
121 end if;
122 close c;
123 if ( (recinfo.FLEX_VALUE_RULE_NAME = X_FLEX_VALUE_RULE_NAME)
124 AND (recinfo.FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID)
125 AND ((recinfo.PARENT_FLEX_VALUE_LOW = X_PARENT_FLEX_VALUE_LOW)
126 OR ((recinfo.PARENT_FLEX_VALUE_LOW is null) AND (X_PARENT_FLEX_VALUE_LOW is null)))
127 AND ((recinfo.PARENT_FLEX_VALUE_HIGH = X_PARENT_FLEX_VALUE_HIGH)
128 OR ((recinfo.PARENT_FLEX_VALUE_HIGH is null) AND (X_PARENT_FLEX_VALUE_HIGH is null)))
129 ) then
130 null;
131 else
132 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
133 app_exception.raise_exception;
134 end if;
135
136 for tlinfo in c1 loop
137 if (tlinfo.BASELANG = 'Y') then
138 if ( ((tlinfo.ERROR_MESSAGE = X_ERROR_MESSAGE)
139 OR ((tlinfo.ERROR_MESSAGE is null) AND (X_ERROR_MESSAGE is null)))
140 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
141 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
142 ) then
143 null;
144 else
145 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
146 app_exception.raise_exception;
147 end if;
148 end if;
149 end loop;
150 return;
151 end LOCK_ROW;
152
153 procedure UPDATE_ROW (
154 X_FLEX_VALUE_RULE_ID in NUMBER,
155 X_FLEX_VALUE_RULE_NAME in VARCHAR2,
156 X_FLEX_VALUE_SET_ID in NUMBER,
157 X_PARENT_FLEX_VALUE_LOW in VARCHAR2,
158 X_PARENT_FLEX_VALUE_HIGH in VARCHAR2,
159 X_ERROR_MESSAGE in VARCHAR2,
160 X_DESCRIPTION in VARCHAR2,
161 X_LAST_UPDATE_DATE in DATE,
162 X_LAST_UPDATED_BY in NUMBER,
163 X_LAST_UPDATE_LOGIN in NUMBER
164 ) is
165 begin
166 update FND_FLEX_VALUE_RULES set
167 FLEX_VALUE_RULE_NAME = X_FLEX_VALUE_RULE_NAME,
168 FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID,
169 PARENT_FLEX_VALUE_LOW = X_PARENT_FLEX_VALUE_LOW,
170 PARENT_FLEX_VALUE_HIGH = X_PARENT_FLEX_VALUE_HIGH,
171 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
172 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
173 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
174 where FLEX_VALUE_RULE_ID = X_FLEX_VALUE_RULE_ID;
175
176 if (sql%notfound) then
177 raise no_data_found;
178 end if;
179
180 update FND_FLEX_VALUE_RULES_TL set
181 ERROR_MESSAGE = X_ERROR_MESSAGE,
182 DESCRIPTION = X_DESCRIPTION,
183 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
184 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
185 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
186 SOURCE_LANG = userenv('LANG')
187 where FLEX_VALUE_RULE_ID = X_FLEX_VALUE_RULE_ID
188 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
189
190 if (sql%notfound) then
191 raise no_data_found;
192 end if;
193 end UPDATE_ROW;
194
195 procedure DELETE_ROW (
196 X_FLEX_VALUE_RULE_ID in NUMBER
197 ) is
198 begin
199 delete from FND_FLEX_VALUE_RULES_TL
200 where FLEX_VALUE_RULE_ID = X_FLEX_VALUE_RULE_ID;
201
202 if (sql%notfound) then
203 raise no_data_found;
204 end if;
205
206 delete from FND_FLEX_VALUE_RULES
207 where FLEX_VALUE_RULE_ID = X_FLEX_VALUE_RULE_ID;
208
209 if (sql%notfound) then
210 raise no_data_found;
211 end if;
212 end DELETE_ROW;
213
214 procedure ADD_LANGUAGE
215 is
216 begin
217 /* Mar/19/03 requested by Ric Ginsberg */
218 /* The following delete and update statements are commented out */
219 /* as a quick workaround to fix the time-consuming table handler issue */
220 /* Eventually we'll need to turn them into a separate fix_language procedure */
221 /*
222
223 delete from FND_FLEX_VALUE_RULES_TL T
224 where not exists
225 (select NULL
226 from FND_FLEX_VALUE_RULES B
227 where B.FLEX_VALUE_RULE_ID = T.FLEX_VALUE_RULE_ID
228 );
229
230 update FND_FLEX_VALUE_RULES_TL T set (
231 ERROR_MESSAGE,
232 DESCRIPTION
233 ) = (select
234 B.ERROR_MESSAGE,
235 B.DESCRIPTION
236 from FND_FLEX_VALUE_RULES_TL B
237 where B.FLEX_VALUE_RULE_ID = T.FLEX_VALUE_RULE_ID
238 and B.LANGUAGE = T.SOURCE_LANG)
239 where (
240 T.FLEX_VALUE_RULE_ID,
241 T.LANGUAGE
242 ) in (select
243 SUBT.FLEX_VALUE_RULE_ID,
244 SUBT.LANGUAGE
245 from FND_FLEX_VALUE_RULES_TL SUBB, FND_FLEX_VALUE_RULES_TL SUBT
246 where SUBB.FLEX_VALUE_RULE_ID = SUBT.FLEX_VALUE_RULE_ID
247 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
248 and (SUBB.ERROR_MESSAGE <> SUBT.ERROR_MESSAGE
249 or (SUBB.ERROR_MESSAGE is null and SUBT.ERROR_MESSAGE is not null)
250 or (SUBB.ERROR_MESSAGE is not null and SUBT.ERROR_MESSAGE is null)
251 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
252 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
253 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
254 ));
255 */
256
257 insert into FND_FLEX_VALUE_RULES_TL (
258 DESCRIPTION,
259 FLEX_VALUE_RULE_ID,
260 LAST_UPDATE_DATE,
261 LAST_UPDATED_BY,
262 CREATION_DATE,
263 CREATED_BY,
264 LAST_UPDATE_LOGIN,
265 ERROR_MESSAGE,
266 LANGUAGE,
267 SOURCE_LANG
268 ) select
269 B.DESCRIPTION,
270 B.FLEX_VALUE_RULE_ID,
271 B.LAST_UPDATE_DATE,
272 B.LAST_UPDATED_BY,
273 B.CREATION_DATE,
274 B.CREATED_BY,
275 B.LAST_UPDATE_LOGIN,
276 B.ERROR_MESSAGE,
277 L.LANGUAGE_CODE,
278 B.SOURCE_LANG
279 from FND_FLEX_VALUE_RULES_TL B, FND_LANGUAGES L
280 where L.INSTALLED_FLAG in ('I', 'B')
281 and B.LANGUAGE = userenv('LANG')
282 and not exists
283 (select NULL
284 from FND_FLEX_VALUE_RULES_TL T
285 where T.FLEX_VALUE_RULE_ID = B.FLEX_VALUE_RULE_ID
286 and T.LANGUAGE = L.LANGUAGE_CODE);
287 end ADD_LANGUAGE;
288
289 PROCEDURE load_row
290 (x_flex_value_set_name IN VARCHAR2,
291 x_parent_flex_value_low IN VARCHAR2,
292 x_flex_value_rule_name IN VARCHAR2,
293 x_who IN fnd_flex_loader_apis.who_type,
294 x_parent_flex_value_high IN VARCHAR2,
295 x_error_message IN VARCHAR2,
296 x_description IN VARCHAR2)
297 IS
298 l_flex_value_set_id NUMBER := NULL;
299 l_flex_value_rule_id NUMBER;
300 l_validation_type VARCHAR2(1);
301 l_rowid VARCHAR2(64);
302 BEGIN
303 SELECT flex_value_set_id, validation_type
304 INTO l_flex_value_set_id, l_validation_type
305 FROM fnd_flex_value_sets
306 WHERE flex_value_set_name = x_flex_value_set_name;
307
308 BEGIN
309 IF (l_validation_type = 'D') THEN
310 SELECT flex_value_rule_id
311 INTO l_flex_value_rule_id
312 FROM fnd_flex_value_rules
313 WHERE flex_value_set_id = l_flex_value_set_id
314 AND flex_value_rule_name = x_flex_value_rule_name
315 AND (parent_flex_value_low = x_parent_flex_value_low OR
316 (parent_flex_value_low IS NULL AND
317 x_parent_flex_value_low IS NULL));
318 ELSE
319 SELECT flex_value_rule_id
320 INTO l_flex_value_rule_id
321 FROM fnd_flex_value_rules
322 WHERE flex_value_set_id = l_flex_value_set_id
323 AND flex_value_rule_name = x_flex_value_rule_name;
324 END IF;
325
326 fnd_flex_value_rules_pkg.update_row
327 (X_FLEX_VALUE_RULE_ID => l_flex_value_rule_id,
328 X_FLEX_VALUE_RULE_NAME => x_flex_value_rule_name,
329 X_FLEX_VALUE_SET_ID => l_flex_value_set_id,
330 X_DESCRIPTION => x_description,
331 X_PARENT_FLEX_VALUE_LOW => x_parent_flex_value_low,
332 X_PARENT_FLEX_VALUE_HIGH => x_parent_flex_value_high,
333 X_ERROR_MESSAGE => x_error_message,
334 X_LAST_UPDATE_DATE => x_who.last_update_date,
335 X_LAST_UPDATED_BY => x_who.last_updated_by,
336 X_LAST_UPDATE_LOGIN => x_who.last_update_login);
337 EXCEPTION
338 WHEN no_data_found THEN
339 SELECT fnd_flex_value_rules_s.NEXTVAL
340 INTO l_flex_value_rule_id
341 FROM dual;
342
343 fnd_flex_value_rules_pkg.insert_row
344 (X_ROWID => l_rowid,
345 X_FLEX_VALUE_RULE_ID => l_flex_value_rule_id,
346 X_FLEX_VALUE_RULE_NAME => x_flex_value_rule_name,
347 X_FLEX_VALUE_SET_ID => l_flex_value_set_id,
348 X_DESCRIPTION => x_description,
349 X_PARENT_FLEX_VALUE_LOW => x_parent_flex_value_low,
350 X_PARENT_FLEX_VALUE_HIGH => x_parent_flex_value_high,
351 X_ERROR_MESSAGE => x_error_message,
352 X_CREATION_DATE => x_who.creation_date,
353 X_CREATED_BY => x_who.created_by,
354 X_LAST_UPDATE_DATE => x_who.last_update_date,
355 X_LAST_UPDATED_BY => x_who.last_updated_by,
356 X_LAST_UPDATE_LOGIN => x_who.last_update_login);
357 END;
358 END load_row;
359
360
361 PROCEDURE translate_row
362 (x_flex_value_set_name IN VARCHAR2,
363 x_parent_flex_value_low IN VARCHAR2,
364 x_flex_value_rule_name IN VARCHAR2,
365 x_who IN fnd_flex_loader_apis.who_type,
366 x_error_message IN VARCHAR2,
367 x_description IN VARCHAR2)
368 IS
369 BEGIN
370 UPDATE fnd_flex_value_rules_tl SET
371 error_message = Nvl(x_error_message, error_message),
372 description = Nvl(x_description, description),
373 last_update_date = x_who.last_update_date,
374 last_updated_by = x_who.last_updated_by,
375 last_update_login = x_who.last_update_login,
376 source_lang = userenv('LANG')
377 WHERE (flex_value_rule_id =
378 (SELECT flex_value_rule_id
379 FROM fnd_flex_value_rules fvr, fnd_flex_value_sets fvs
380 WHERE fvr.flex_value_set_id = fvs.flex_value_set_id
381 AND fvs.flex_value_set_name = x_flex_value_set_name
382 AND fvr.flex_value_rule_name = x_flex_value_rule_name
383 AND ((fvs.validation_type NOT IN ('D', 'Y')) OR
384 (fvs.validation_type IN ('D', 'Y') AND
385 ((fvr.parent_flex_value_low = x_parent_flex_value_low) OR
386 (fvr.parent_flex_value_low IS NULL AND
387 x_parent_flex_value_low IS NULL))))))
388 AND userenv('LANG') in (language, source_lang);
389 END translate_row;
390
391 end FND_FLEX_VALUE_RULES_PKG;
[Home] [Help]