eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.FND_FLEX_VDATION_RULES_PKG

Source


1 package body FND_FLEX_VDATION_RULES_PKG as
2 /* $Header: AFFFVDRB.pls 120.2.12010000.1 2008/07/25 14:14:48 appldev ship $ */
3 
4 procedure INSERT_ROW (
5   X_ROWID IN OUT NOCOPY VARCHAR2,
6   X_APPLICATION_ID in NUMBER,
7   X_ID_FLEX_CODE in VARCHAR2,
8   X_ID_FLEX_NUM in NUMBER,
9   X_FLEX_VALIDATION_RULE_NAME in VARCHAR2,
10   X_ENABLED_FLAG in VARCHAR2,
11   X_ERROR_SEGMENT_COLUMN_NAME in VARCHAR2,
12   X_START_DATE_ACTIVE in DATE,
13   X_END_DATE_ACTIVE in DATE,
14   X_ERROR_MESSAGE_TEXT in VARCHAR2,
15   X_DESCRIPTION in VARCHAR2,
16   X_CREATION_DATE in DATE,
17   X_CREATED_BY in NUMBER,
18   X_LAST_UPDATE_DATE in DATE,
19   X_LAST_UPDATED_BY in NUMBER,
20   X_LAST_UPDATE_LOGIN in NUMBER
21 ) is
22   cursor C is select ROWID from FND_FLEX_VALIDATION_RULES
23     where APPLICATION_ID = X_APPLICATION_ID
24     and ID_FLEX_CODE = X_ID_FLEX_CODE
25     and ID_FLEX_NUM = X_ID_FLEX_NUM
26     and FLEX_VALIDATION_RULE_NAME = X_FLEX_VALIDATION_RULE_NAME
27     ;
28 begin
29   insert into FND_FLEX_VALIDATION_RULES (
30     APPLICATION_ID,
31     ID_FLEX_CODE,
32     ID_FLEX_NUM,
33     FLEX_VALIDATION_RULE_NAME,
34     ENABLED_FLAG,
35     ERROR_SEGMENT_COLUMN_NAME,
36     START_DATE_ACTIVE,
37     END_DATE_ACTIVE,
38     CREATION_DATE,
39     CREATED_BY,
40     LAST_UPDATE_DATE,
41     LAST_UPDATED_BY,
42     LAST_UPDATE_LOGIN
43   ) values (
44     X_APPLICATION_ID,
45     X_ID_FLEX_CODE,
46     X_ID_FLEX_NUM,
47     X_FLEX_VALIDATION_RULE_NAME,
48     X_ENABLED_FLAG,
49     X_ERROR_SEGMENT_COLUMN_NAME,
50     X_START_DATE_ACTIVE,
51     X_END_DATE_ACTIVE,
52     X_CREATION_DATE,
53     X_CREATED_BY,
54     X_LAST_UPDATE_DATE,
55     X_LAST_UPDATED_BY,
56     X_LAST_UPDATE_LOGIN
57   );
58 
59   insert into FND_FLEX_VDATION_RULES_TL (
60     DESCRIPTION,
61     APPLICATION_ID,
62     ID_FLEX_CODE,
63     ID_FLEX_NUM,
64     FLEX_VALIDATION_RULE_NAME,
65     LAST_UPDATE_DATE,
66     LAST_UPDATED_BY,
67     CREATION_DATE,
68     CREATED_BY,
69     LAST_UPDATE_LOGIN,
70     ERROR_MESSAGE_TEXT,
71     LANGUAGE,
72     SOURCE_LANG
73   ) select
74     X_DESCRIPTION,
75     X_APPLICATION_ID,
76     X_ID_FLEX_CODE,
77     X_ID_FLEX_NUM,
78     X_FLEX_VALIDATION_RULE_NAME,
79     X_LAST_UPDATE_DATE,
80     X_LAST_UPDATED_BY,
81     X_CREATION_DATE,
82     X_CREATED_BY,
83     X_LAST_UPDATE_LOGIN,
84     X_ERROR_MESSAGE_TEXT,
85     L.LANGUAGE_CODE,
86     userenv('LANG')
87   from FND_LANGUAGES L
88   where L.INSTALLED_FLAG in ('I', 'B')
89   and not exists
90     (select NULL
91     from FND_FLEX_VDATION_RULES_TL T
92     where T.APPLICATION_ID = X_APPLICATION_ID
93     and T.ID_FLEX_CODE = X_ID_FLEX_CODE
94     and T.ID_FLEX_NUM = X_ID_FLEX_NUM
95     and T.FLEX_VALIDATION_RULE_NAME = X_FLEX_VALIDATION_RULE_NAME
96     and T.LANGUAGE = L.LANGUAGE_CODE);
97 
98   open c;
99   fetch c into X_ROWID;
100   if (c%notfound) then
101     close c;
102     raise no_data_found;
103   end if;
104   close c;
105 
106 end INSERT_ROW;
107 
108 procedure LOCK_ROW (
109   X_APPLICATION_ID in NUMBER,
110   X_ID_FLEX_CODE in VARCHAR2,
111   X_ID_FLEX_NUM in NUMBER,
112   X_FLEX_VALIDATION_RULE_NAME in VARCHAR2,
113   X_ENABLED_FLAG in VARCHAR2,
114   X_ERROR_SEGMENT_COLUMN_NAME in VARCHAR2,
115   X_START_DATE_ACTIVE in DATE,
116   X_END_DATE_ACTIVE in DATE,
117   X_ERROR_MESSAGE_TEXT in VARCHAR2,
118   X_DESCRIPTION in VARCHAR2
119 ) is
120   cursor c is select
121       ENABLED_FLAG,
122       ERROR_SEGMENT_COLUMN_NAME,
123       START_DATE_ACTIVE,
124       END_DATE_ACTIVE
125     from FND_FLEX_VALIDATION_RULES
126     where APPLICATION_ID = X_APPLICATION_ID
127     and ID_FLEX_CODE = X_ID_FLEX_CODE
128     and ID_FLEX_NUM = X_ID_FLEX_NUM
129     and FLEX_VALIDATION_RULE_NAME = X_FLEX_VALIDATION_RULE_NAME
130     for update of APPLICATION_ID nowait;
131   recinfo c%rowtype;
132 
133   cursor c1 is select
134       ERROR_MESSAGE_TEXT,
135       DESCRIPTION,
136       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
137     from FND_FLEX_VDATION_RULES_TL
138     where APPLICATION_ID = X_APPLICATION_ID
139     and ID_FLEX_CODE = X_ID_FLEX_CODE
140     and ID_FLEX_NUM = X_ID_FLEX_NUM
141     and FLEX_VALIDATION_RULE_NAME = X_FLEX_VALIDATION_RULE_NAME
142     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
143     for update of APPLICATION_ID nowait;
144 begin
145   open c;
146   fetch c into recinfo;
147   if (c%notfound) then
148     close c;
149     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
150     app_exception.raise_exception;
151   end if;
152   close c;
153   if (    (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
154       AND ((recinfo.ERROR_SEGMENT_COLUMN_NAME = X_ERROR_SEGMENT_COLUMN_NAME)
155            OR ((recinfo.ERROR_SEGMENT_COLUMN_NAME is null) AND (X_ERROR_SEGMENT_COLUMN_NAME is null)))
156       AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
157            OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
158       AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
159            OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
160   ) then
161     null;
162   else
163     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
164     app_exception.raise_exception;
165   end if;
166 
167   for tlinfo in c1 loop
168     if (tlinfo.BASELANG = 'Y') then
169       if (    (tlinfo.ERROR_MESSAGE_TEXT = X_ERROR_MESSAGE_TEXT)
170           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
171                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
172       ) then
173         null;
174       else
175         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
176         app_exception.raise_exception;
177       end if;
178     end if;
179   end loop;
180   return;
181 end LOCK_ROW;
182 
183 procedure UPDATE_ROW (
184   X_APPLICATION_ID in NUMBER,
185   X_ID_FLEX_CODE in VARCHAR2,
186   X_ID_FLEX_NUM in NUMBER,
187   X_FLEX_VALIDATION_RULE_NAME in VARCHAR2,
188   X_ENABLED_FLAG in VARCHAR2,
189   X_ERROR_SEGMENT_COLUMN_NAME in VARCHAR2,
190   X_START_DATE_ACTIVE in DATE,
191   X_END_DATE_ACTIVE in DATE,
192   X_ERROR_MESSAGE_TEXT in VARCHAR2,
193   X_DESCRIPTION in VARCHAR2,
194   X_LAST_UPDATE_DATE in DATE,
195   X_LAST_UPDATED_BY in NUMBER,
196   X_LAST_UPDATE_LOGIN in NUMBER
197 ) is
198 begin
199   update FND_FLEX_VALIDATION_RULES set
200     ENABLED_FLAG = X_ENABLED_FLAG,
201     ERROR_SEGMENT_COLUMN_NAME = X_ERROR_SEGMENT_COLUMN_NAME,
202     START_DATE_ACTIVE = X_START_DATE_ACTIVE,
203     END_DATE_ACTIVE = X_END_DATE_ACTIVE,
204     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
205     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
206     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
207   where APPLICATION_ID = X_APPLICATION_ID
208   and ID_FLEX_CODE = X_ID_FLEX_CODE
209   and ID_FLEX_NUM = X_ID_FLEX_NUM
210   and FLEX_VALIDATION_RULE_NAME = X_FLEX_VALIDATION_RULE_NAME;
211 
212   if (sql%notfound) then
213     raise no_data_found;
214   end if;
215 
216   update FND_FLEX_VDATION_RULES_TL set
217     ERROR_MESSAGE_TEXT = X_ERROR_MESSAGE_TEXT,
218     DESCRIPTION = X_DESCRIPTION,
219     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
220     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
221     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
222     SOURCE_LANG = userenv('LANG')
223   where APPLICATION_ID = X_APPLICATION_ID
224   and ID_FLEX_CODE = X_ID_FLEX_CODE
225   and ID_FLEX_NUM = X_ID_FLEX_NUM
226   and FLEX_VALIDATION_RULE_NAME = X_FLEX_VALIDATION_RULE_NAME
227   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
228 
229   if (sql%notfound) then
230     raise no_data_found;
231   end if;
232 end UPDATE_ROW;
233 
234 procedure DELETE_ROW (
235   X_APPLICATION_ID in NUMBER,
236   X_ID_FLEX_CODE in VARCHAR2,
237   X_ID_FLEX_NUM in NUMBER,
238   X_FLEX_VALIDATION_RULE_NAME in VARCHAR2
239 ) is
240 begin
241   delete from FND_FLEX_VDATION_RULES_TL
242   where APPLICATION_ID = X_APPLICATION_ID
243   and ID_FLEX_CODE = X_ID_FLEX_CODE
244   and ID_FLEX_NUM = X_ID_FLEX_NUM
245   and FLEX_VALIDATION_RULE_NAME = X_FLEX_VALIDATION_RULE_NAME;
246 
247   if (sql%notfound) then
248     raise no_data_found;
249   end if;
250 
251   delete from FND_FLEX_VALIDATION_RULES
252   where APPLICATION_ID = X_APPLICATION_ID
253   and ID_FLEX_CODE = X_ID_FLEX_CODE
254   and ID_FLEX_NUM = X_ID_FLEX_NUM
255   and FLEX_VALIDATION_RULE_NAME = X_FLEX_VALIDATION_RULE_NAME;
256 
257   if (sql%notfound) then
258     raise no_data_found;
259   end if;
260 end DELETE_ROW;
261 
262 procedure ADD_LANGUAGE
263  is
264  begin
265 /* Mar/19/03 requested by Ric Ginsberg */
266 /* The following delete and update statements are commented out */
267 /* as a quick workaround to fix the time-consuming table handler issue */
268 /* Eventually we'll need to turn them into a separate fix_language procedure */
269 /*
270 
271    delete from FND_FLEX_VDATION_RULES_TL T
272    where not exists
273      (select NULL
274      from FND_FLEX_VALIDATION_RULES B
275      where B.APPLICATION_ID = T.APPLICATION_ID
276      and B.ID_FLEX_CODE = T.ID_FLEX_CODE
277      and B.ID_FLEX_NUM = T.ID_FLEX_NUM
278      and B.FLEX_VALIDATION_RULE_NAME = T.FLEX_VALIDATION_RULE_NAME
279      );
280 
281    update FND_FLEX_VDATION_RULES_TL T set (
282        ERROR_MESSAGE_TEXT,
283        DESCRIPTION
284      ) = (select
285        B.ERROR_MESSAGE_TEXT,
286        B.DESCRIPTION
287      from FND_FLEX_VDATION_RULES_TL B
288      where B.APPLICATION_ID = T.APPLICATION_ID
289      and B.ID_FLEX_CODE = T.ID_FLEX_CODE
290      and B.ID_FLEX_NUM = T.ID_FLEX_NUM
291      and B.FLEX_VALIDATION_RULE_NAME = T.FLEX_VALIDATION_RULE_NAME
292      and B.LANGUAGE = T.SOURCE_LANG)
293    where (
294        T.APPLICATION_ID,
295        T.ID_FLEX_CODE,
296        T.ID_FLEX_NUM,
297        T.FLEX_VALIDATION_RULE_NAME,
298        T.LANGUAGE
299    ) in (select
300        SUBT.APPLICATION_ID,
301        SUBT.ID_FLEX_CODE,
302        SUBT.ID_FLEX_NUM,
303        SUBT.FLEX_VALIDATION_RULE_NAME,
304        SUBT.LANGUAGE
305      from FND_FLEX_VDATION_RULES_TL SUBB, FND_FLEX_VDATION_RULES_TL SUBT
306      where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
307      and SUBB.ID_FLEX_CODE = SUBT.ID_FLEX_CODE
308      and SUBB.ID_FLEX_NUM = SUBT.ID_FLEX_NUM
309      and SUBB.FLEX_VALIDATION_RULE_NAME = SUBT.FLEX_VALIDATION_RULE_NAME
310      and SUBB.LANGUAGE = SUBT.SOURCE_LANG
311      and (SUBB.ERROR_MESSAGE_TEXT <> SUBT.ERROR_MESSAGE_TEXT
312        or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
313        or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
314        or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
315    ));
316 */
317 
318    insert /*+ append parallel(tt) */ into FND_FLEX_VDATION_RULES_TL tt (
319      DESCRIPTION,
320      APPLICATION_ID,
321      ID_FLEX_CODE,
322      ID_FLEX_NUM,
323      FLEX_VALIDATION_RULE_NAME,
324      LAST_UPDATE_DATE,
325      LAST_UPDATED_BY,
326      CREATION_DATE,
327      CREATED_BY,
328      LAST_UPDATE_LOGIN,
329      ERROR_MESSAGE_TEXT,
330      LANGUAGE,
331      SOURCE_LANG
332    )
333      select /*+ parallel(v) parallel(t) use_nl(t) */ v.* from
334      (select /*+ no_merge ordered parallel(b) */
335      B.DESCRIPTION,
336      B.APPLICATION_ID,
337      B.ID_FLEX_CODE,
338      B.ID_FLEX_NUM,
339      B.FLEX_VALIDATION_RULE_NAME,
340      B.LAST_UPDATE_DATE,
341      B.LAST_UPDATED_BY,
342      B.CREATION_DATE,
343      B.CREATED_BY,
344      B.LAST_UPDATE_LOGIN,
345      B.ERROR_MESSAGE_TEXT,
346      L.LANGUAGE_CODE,
347      B.SOURCE_LANG
348    from FND_FLEX_VDATION_RULES_TL B, FND_LANGUAGES L
349    where L.INSTALLED_FLAG in ('I', 'B')
350    and B.LANGUAGE = userenv('LANG')
351    ) v, FND_FLEX_VDATION_RULES_TL t
352    where t.application_id(+) = v.application_id
353    and t.id_flex_code(+) = v.id_flex_code
354    and t.id_flex_num(+) = v.id_flex_num
355    and t.flex_validation_rule_name(+) = v.flex_validation_rule_name
356    and t.language(+) = v.language_code
357    and t.application_id is NULL
358    and t.id_flex_code is NULL
359    and t.id_flex_num is NULL
360    and t.flex_validation_rule_name is NULL;
361 
362 end ADD_LANGUAGE;
363 
364 PROCEDURE load_row
365   (x_application_short_name       IN VARCHAR2,
366    x_id_flex_code                 IN VARCHAR2,
367    x_id_flex_structure_code       IN VARCHAR2,
368    x_flex_validation_rule_name    IN VARCHAR2,
369    x_who                          IN fnd_flex_loader_apis.who_type,
370    x_enabled_flag                 IN VARCHAR2,
371    x_error_segment_column_name    IN VARCHAR2,
372    x_start_date_active            IN DATE,
373    x_end_date_active              IN DATE,
374    x_error_message_text           IN VARCHAR2,
375    x_description                  IN VARCHAR2)
376   IS
377      l_application_id    NUMBER;
378      l_id_flex_num       NUMBER;
379      l_rowid             VARCHAR2(64);
380 BEGIN
381    SELECT application_id
382      INTO l_application_id
383      FROM fnd_application
384      WHERE application_short_name = x_application_short_name;
385 
386    SELECT id_flex_num
387      INTO l_id_flex_num
388      FROM fnd_id_flex_structures
389      WHERE application_id = l_application_id
390      AND id_flex_code = x_id_flex_code
391      AND id_flex_structure_code = x_id_flex_structure_code;
392 
393    BEGIN
394       fnd_flex_vdation_rules_pkg.update_row
395 	(X_APPLICATION_ID               => l_application_id,
396 	 X_ID_FLEX_CODE                 => x_id_flex_code,
397 	 X_ID_FLEX_NUM                  => l_id_flex_num,
398 	 X_FLEX_VALIDATION_RULE_NAME    => x_flex_validation_rule_name,
399 	 X_DESCRIPTION                  => x_description,
400 	 X_ENABLED_FLAG                 => x_enabled_flag,
401 	 X_ERROR_SEGMENT_COLUMN_NAME    => x_error_segment_column_name,
402 	 X_START_DATE_ACTIVE            => x_start_date_active,
403 	 X_END_DATE_ACTIVE              => x_end_date_active,
404 	 X_ERROR_MESSAGE_TEXT           => x_error_message_text,
405 	 X_LAST_UPDATE_DATE             => x_who.last_update_date,
406 	 X_LAST_UPDATED_BY              => x_who.last_updated_by,
407 	 X_LAST_UPDATE_LOGIN            => x_who.last_update_login);
408    EXCEPTION
409       WHEN no_data_found THEN
410 	 fnd_flex_vdation_rules_pkg.insert_row
411 	   (X_ROWID                        => l_rowid,
412 	    X_APPLICATION_ID               => l_application_id,
413 	    X_ID_FLEX_CODE                 => x_id_flex_code,
414 	    X_ID_FLEX_NUM                  => l_id_flex_num,
415 	    X_FLEX_VALIDATION_RULE_NAME    => x_flex_validation_rule_name,
416 	    X_DESCRIPTION                  => x_description,
417 	    X_ENABLED_FLAG                 => x_enabled_flag,
418 	    X_ERROR_SEGMENT_COLUMN_NAME    => x_error_segment_column_name,
419 	    X_START_DATE_ACTIVE            => x_start_date_active,
420 	    X_END_DATE_ACTIVE              => x_end_date_active,
421 	    X_ERROR_MESSAGE_TEXT           => x_error_message_text,
422 	    X_CREATION_DATE                => x_who.creation_date,
423   	    X_CREATED_BY                   => x_who.created_by,
424 	    X_LAST_UPDATE_DATE             => x_who.last_update_date,
425 	    X_LAST_UPDATED_BY              => x_who.last_updated_by,
426 	    X_LAST_UPDATE_LOGIN            => x_who.last_update_login);
427    END;
428 END load_row;
429 
430 PROCEDURE translate_row
431   (x_application_short_name       IN VARCHAR2,
432    x_id_flex_code                 IN VARCHAR2,
433    x_id_flex_structure_code       IN VARCHAR2,
434    x_flex_validation_rule_name    IN VARCHAR2,
435    x_who                          IN fnd_flex_loader_apis.who_type,
436    x_error_message_text           IN VARCHAR2,
437    x_description                  IN VARCHAR2)
438   IS
439 BEGIN
440    UPDATE fnd_flex_vdation_rules_tl SET
441      error_message_text = Nvl(x_error_message_text, error_message_text),
442      description        = Nvl(x_description, description),
443      last_update_date   = x_who.last_update_date,
444      last_updated_by    = x_who.last_updated_by,
445      last_update_login  = x_who.last_update_login,
446      source_lang        = userenv('LANG')
447      WHERE ((application_id, id_flex_code, id_flex_num) =
448 	    (SELECT application_id, id_flex_code, id_flex_num
449 	     FROM fnd_id_flex_structures
450 	     WHERE (application_id =
451 		    (SELECT application_id
452 		     FROM fnd_application
453                      WHERE application_short_name = x_application_short_name))
454 	     AND id_flex_code = x_id_flex_code
455              AND id_flex_structure_code = x_id_flex_structure_code))
456      AND flex_validation_rule_name = x_flex_validation_rule_name
457      AND userenv('LANG') in (language, source_lang);
458 END translate_row;
459 
460 end FND_FLEX_VDATION_RULES_PKG;