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