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