[Home] [Help]
PACKAGE BODY: APPS.FND_EXECUTABLES_PKG
Source
1 package body FND_EXECUTABLES_PKG as
2 /* $Header: AFCPMPEB.pls 120.2 2005/08/19 20:05:53 tkamiya ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_APPLICATION_ID in NUMBER,
7 X_EXECUTABLE_ID in NUMBER,
8 X_EXECUTABLE_NAME in VARCHAR2,
9 X_EXECUTION_METHOD_CODE in VARCHAR2,
10 X_EXECUTION_FILE_NAME in VARCHAR2,
11 X_SUBROUTINE_NAME in VARCHAR2,
12 X_EXECUTION_FILE_PATH in VARCHAR2,
13 X_USER_EXECUTABLE_NAME in VARCHAR2,
14 X_DESCRIPTION in VARCHAR2,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER
20 ) is
21 cursor C is select ROWID from FND_EXECUTABLES
22 where APPLICATION_ID = X_APPLICATION_ID
23 and EXECUTABLE_ID = X_EXECUTABLE_ID
24 ;
25 begin
26 insert into FND_EXECUTABLES (
27 APPLICATION_ID,
28 EXECUTABLE_ID,
29 EXECUTABLE_NAME,
30 EXECUTION_METHOD_CODE,
31 EXECUTION_FILE_NAME,
32 SUBROUTINE_NAME,
33 EXECUTION_FILE_PATH,
34 CREATION_DATE,
35 CREATED_BY,
36 LAST_UPDATE_DATE,
37 LAST_UPDATED_BY,
38 LAST_UPDATE_LOGIN
39 ) values (
40 X_APPLICATION_ID,
41 X_EXECUTABLE_ID,
42 X_EXECUTABLE_NAME,
43 X_EXECUTION_METHOD_CODE,
44 X_EXECUTION_FILE_NAME,
45 X_SUBROUTINE_NAME,
46 X_EXECUTION_FILE_PATH,
47 X_CREATION_DATE,
48 X_CREATED_BY,
49 X_LAST_UPDATE_DATE,
50 X_LAST_UPDATED_BY,
51 X_LAST_UPDATE_LOGIN
52 );
53
54 insert into FND_EXECUTABLES_TL (
55 APPLICATION_ID,
56 EXECUTABLE_ID,
57 CREATION_DATE,
58 CREATED_BY,
59 LAST_UPDATE_DATE,
60 LAST_UPDATED_BY,
61 LAST_UPDATE_LOGIN,
62 USER_EXECUTABLE_NAME,
63 DESCRIPTION,
64 LANGUAGE,
65 SOURCE_LANG
66 ) select
67 X_APPLICATION_ID,
68 X_EXECUTABLE_ID,
69 X_CREATION_DATE,
70 X_CREATED_BY,
71 X_LAST_UPDATE_DATE,
72 X_LAST_UPDATED_BY,
73 X_LAST_UPDATE_LOGIN,
74 X_USER_EXECUTABLE_NAME,
75 X_DESCRIPTION,
76 L.LANGUAGE_CODE,
77 userenv('LANG')
78 from FND_LANGUAGES L
79 where L.INSTALLED_FLAG in ('I', 'B')
80 and not exists
81 (select NULL
82 from FND_EXECUTABLES_TL T
83 where T.APPLICATION_ID = X_APPLICATION_ID
84 and T.EXECUTABLE_ID = X_EXECUTABLE_ID
85 and T.LANGUAGE = L.LANGUAGE_CODE);
86
87 open c;
88 fetch c into X_ROWID;
89 if (c%notfound) then
90 close c;
91 raise no_data_found;
92 end if;
93 close c;
94
95 end INSERT_ROW;
96
97 procedure LOCK_ROW (
98 X_APPLICATION_ID in NUMBER,
99 X_EXECUTABLE_ID in NUMBER,
100 X_EXECUTABLE_NAME in VARCHAR2,
101 X_EXECUTION_METHOD_CODE in VARCHAR2,
102 X_EXECUTION_FILE_NAME in VARCHAR2,
103 X_SUBROUTINE_NAME in VARCHAR2,
104 X_EXECUTION_FILE_PATH in VARCHAR2,
105 X_USER_EXECUTABLE_NAME in VARCHAR2,
106 X_DESCRIPTION in VARCHAR2
107 ) is
108 cursor c is select
109 EXECUTABLE_NAME,
110 EXECUTION_METHOD_CODE,
111 EXECUTION_FILE_NAME,
112 SUBROUTINE_NAME,
113 EXECUTION_FILE_PATH
114 from FND_EXECUTABLES
115 where APPLICATION_ID = X_APPLICATION_ID
116 and EXECUTABLE_ID = X_EXECUTABLE_ID
117 for update of APPLICATION_ID nowait;
118 recinfo c%rowtype;
119
120 cursor c1 is select
121 USER_EXECUTABLE_NAME,
122 DESCRIPTION
123 from FND_EXECUTABLES_TL
124 where APPLICATION_ID = X_APPLICATION_ID
125 and EXECUTABLE_ID = X_EXECUTABLE_ID
126 and LANGUAGE = userenv('LANG')
127 for update of APPLICATION_ID nowait;
128 tlinfo c1%rowtype;
129
130 begin
131 open c;
132 fetch c into recinfo;
133 if (c%notfound) then
134 close c;
135 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
136 app_exception.raise_exception;
137 end if;
138 close c;
139 if ( (recinfo.EXECUTABLE_NAME = X_EXECUTABLE_NAME)
140 AND (recinfo.EXECUTION_METHOD_CODE = X_EXECUTION_METHOD_CODE)
141 AND ((recinfo.EXECUTION_FILE_NAME = X_EXECUTION_FILE_NAME)
142 OR ((recinfo.EXECUTION_FILE_NAME is null) AND (X_EXECUTION_FILE_NAME is null)))
143 AND ((recinfo.SUBROUTINE_NAME = X_SUBROUTINE_NAME)
144 OR ((recinfo.SUBROUTINE_NAME is null) AND (X_SUBROUTINE_NAME is null)))
145 AND ((recinfo.EXECUTION_FILE_PATH = X_EXECUTION_FILE_PATH)
146 OR ((recinfo.EXECUTION_FILE_PATH is null) AND (X_EXECUTION_FILE_PATH is null)))
147 ) then
148 null;
149 else
150 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
151 app_exception.raise_exception;
152 end if;
153
154 open c1;
155 fetch c1 into tlinfo;
156 if (c1%notfound) then
157 close c1;
158 return;
159 end if;
160 close c1;
161
162 if ( (tlinfo.USER_EXECUTABLE_NAME = X_USER_EXECUTABLE_NAME)
163 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
164 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
165 ) then
166 null;
167 else
168 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
169 app_exception.raise_exception;
170 end if;
171 return;
172 end LOCK_ROW;
173
174 procedure UPDATE_ROW (
175 X_APPLICATION_ID in NUMBER,
176 X_EXECUTABLE_ID in NUMBER,
177 X_EXECUTABLE_NAME in VARCHAR2,
178 X_EXECUTION_METHOD_CODE in VARCHAR2,
179 X_EXECUTION_FILE_NAME in VARCHAR2,
180 X_SUBROUTINE_NAME in VARCHAR2,
181 X_EXECUTION_FILE_PATH in VARCHAR2,
182 X_USER_EXECUTABLE_NAME in VARCHAR2,
183 X_DESCRIPTION in VARCHAR2,
184 X_LAST_UPDATE_DATE in DATE,
185 X_LAST_UPDATED_BY in NUMBER,
186 X_LAST_UPDATE_LOGIN in NUMBER
187 ) is
188 begin
189 update FND_EXECUTABLES set
190 EXECUTABLE_NAME = X_EXECUTABLE_NAME,
191 EXECUTION_METHOD_CODE = X_EXECUTION_METHOD_CODE,
192 EXECUTION_FILE_NAME = X_EXECUTION_FILE_NAME,
193 SUBROUTINE_NAME = X_SUBROUTINE_NAME,
194 EXECUTION_FILE_PATH = X_EXECUTION_FILE_PATH,
195 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
196 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
197 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
198 where APPLICATION_ID = X_APPLICATION_ID
199 and EXECUTABLE_ID = X_EXECUTABLE_ID;
200
201 if (sql%notfound) then
202 raise no_data_found;
203 end if;
204
205 update FND_EXECUTABLES_TL set
206 USER_EXECUTABLE_NAME = nvl(X_USER_EXECUTABLE_NAME, USER_EXECUTABLE_NAME),
207 DESCRIPTION = nvl(X_DESCRIPTION, DESCRIPTION),
208 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
209 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
210 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
211 SOURCE_LANG = userenv('LANG')
212 where APPLICATION_ID = X_APPLICATION_ID
213 and EXECUTABLE_ID = X_EXECUTABLE_ID
214 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
215
216 if (sql%notfound) then
217 raise no_data_found;
218 end if;
219 end UPDATE_ROW;
220
221
222 -- Overloaded in case x_custom_mode and x_last_update_date not used
223 procedure TRANSLATE_ROW (
224 x_executable_name in varchar2,
225 x_application_short_name in varchar2,
226 x_owner in varchar2,
227 x_user_executable_name in varchar2,
228 x_description in varchar2)
229 is
230 begin
231 fnd_executables_pkg.translate_row(
232 x_executable_name => x_executable_name,
233 x_application_short_name => x_application_short_name,
234 x_owner => x_owner,
235 x_user_executable_name => x_user_executable_name,
236 x_description => x_description,
237 x_custom_mode => null,
238 x_last_update_date => null);
239 end TRANSLATE_ROW;
240
241
242 -- Overloaded
243 procedure TRANSLATE_ROW (
244 x_executable_name in varchar2,
245 x_application_short_name in varchar2,
246 x_owner in varchar2,
247 x_user_executable_name in varchar2,
248 x_description in varchar2,
249 x_custom_mode in varchar2,
250 x_last_update_date in varchar2)
251 is
252 app_id number := 0;
253 key_id number := 0;
254 f_luby number; -- entity owner in file
255 f_ludate date; -- entity update date in file
256 db_luby number; -- entity owner in db
257 db_ludate date; -- entity update in db
258 begin
259
260 -- Translate owner to file_last_updated_by
261 f_luby := fnd_load_util.OWNER_ID(x_owner);
262
263 -- Translate char last_update_date to date
264 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
265
266 begin
267 select APPLICATION_ID into app_id
268 from fnd_application
269 where APPLICATION_SHORT_NAME = x_application_short_name;
270
271 select EXECUTABLE_ID into key_id
272 from fnd_executables
273 where EXECUTABLE_NAME = x_executable_name
274 and APPLICATION_ID = app_id;
275
276 select LAST_UPDATED_BY, LAST_UPDATE_DATE
277 into db_luby, db_ludate
278 from FND_EXECUTABLES_TL
279 where APPLICATION_ID = app_id
280 and EXECUTABLE_ID = key_id
281 and LANGUAGE = userenv('LANG');
282 -- Update record, honoring customization mode.
283 -- Record should be updated only if:
284 -- a. CUSTOM_MODE = FORCE, or
285 -- b. file owner is USER, db owner is SEED
286 -- c. owners are the same, and file_date > db_date
287 if (fnd_load_util.UPLOAD_TEST(
288 p_file_id => f_luby,
289 p_file_lud => f_ludate,
290 p_db_id => db_luby,
291 p_db_lud => db_ludate,
292 p_custom_mode => x_custom_mode))
293 then
294 update FND_EXECUTABLES_TL set
295 USER_EXECUTABLE_NAME = nvl(x_user_executable_name,
296 USER_EXECUTABLE_NAME),
297 DESCRIPTION = nvl(x_description, DESCRIPTION),
298 SOURCE_LANG = userenv('LANG'),
299 LAST_UPDATE_DATE = f_ludate,
300 LAST_UPDATED_BY = f_luby,
301 LAST_UPDATE_LOGIN = 0
302 where APPLICATION_ID = app_id
303 and EXECUTABLE_ID = key_id
304 and LANGUAGE = userenv('LANG');
305 end if;
306 exception
307 when no_data_found then
308 null;
309 end;
310 end TRANSLATE_ROW;
311
312 -- Overloaded in case x_custom_mode and x_last_update_date not used
313 procedure LOAD_ROW (
314 x_executable_name in varchar2,
315 x_application_short_name in varchar2,
316 x_owner in varchar2,
317 x_execution_method_code in varchar2,
318 x_execution_file_name in varchar2,
319 x_subroutine_name in varchar2,
320 x_execution_file_path in varchar2,
321 x_user_executable_name in varchar2,
322 x_description in varchar2)
323 is
324 begin
325 fnd_executables_pkg.load_row(
326 x_executable_name => x_executable_name,
327 x_application_short_name => x_application_short_name,
328 x_owner => x_owner,
329 x_execution_method_code => x_execution_method_code,
330 x_execution_file_name => x_execution_file_name,
331 x_subroutine_name => x_subroutine_name,
332 x_execution_file_path => x_execution_file_path,
333 x_user_executable_name => x_user_executable_name,
334 x_description => x_description,
335 x_custom_mode => null,
336 x_last_update_date => null);
337 end LOAD_ROW;
338
339
340 -- Overloaded
341 procedure LOAD_ROW (
342 x_executable_name in varchar2,
343 x_application_short_name in varchar2,
344 x_owner in varchar2,
345 x_execution_method_code in varchar2,
346 x_execution_file_name in varchar2,
347 x_subroutine_name in varchar2,
348 x_execution_file_path in varchar2,
349 x_user_executable_name in varchar2,
350 x_description in varchar2,
351 x_custom_mode in varchar2,
352 x_last_update_date in varchar2)
353 is
354 app_id number := 0;
355 key_id number := 0;
356 exec_method varchar2(255) := NULL;
357 f_luby number; -- entity owner in file
358 f_ludate date; -- entity update date in file
359 db_luby number; -- entity owner in db
360 db_ludate date; -- entity update in db
361 begin
362
363 -- Translate owner to file_last_updated_by
364 f_luby := fnd_load_util.OWNER_ID(x_owner);
365
366 -- Translate char last_update_date to date
367 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
368
369 begin
370 select APPLICATION_ID into app_id
371 from fnd_application
372 where APPLICATION_SHORT_NAME = x_application_short_name;
373
374 select EXECUTABLE_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE
375 into key_id, db_luby, db_ludate
376 from fnd_executables
377 where EXECUTABLE_NAME = x_executable_name
378 and APPLICATION_ID = app_id;
379
380 -- Update record, honoring customization mode.
381 -- Record should be updated only if:
382 -- a. CUSTOM_MODE = FORCE, or
383 -- b. file owner is CUSTOM, db owner is SEED
384 -- c. owners are the same, and file_date > db_date
385 if (fnd_load_util.UPLOAD_TEST(
386 p_file_id => f_luby,
387 p_file_lud => f_ludate,
388 p_db_id => db_luby,
389 p_db_lud => db_ludate,
390 p_custom_mode => x_custom_mode))
391 then
392 fnd_executables_pkg.update_row(
393 x_application_id => app_id,
394 x_executable_id => key_id,
395 x_executable_name => x_executable_name,
396 x_execution_method_code => x_execution_method_code,
397 x_execution_file_name => x_execution_file_name,
398 x_subroutine_name => x_subroutine_name,
399 x_execution_file_path => x_execution_file_path,
400 x_user_executable_name => x_user_executable_name,
401 x_description => x_description,
402 x_last_update_date => f_ludate,
403 X_last_updated_by => f_luby,
404 x_last_update_login => 0);
405 end if;
406 exception when no_data_found then
407 select meaning
408 into exec_method
409 from fnd_lookup_values
410 where lookup_code = x_execution_method_code
411 and lookup_type = 'CP_EXECUTION_METHOD_CODE'
412 and enabled_flag = 'Y'
413 and rownum = 1;
414
415 if (f_luby = 1) then fnd_program.set_session_mode('seed_data');
416 else fnd_program.set_session_mode('customer_data');
417 end if;
418
419 begin
420 fnd_program.executable(
421 executable => x_user_executable_name,
422 application => x_application_short_name,
423 short_name => x_executable_name,
424 description => x_description,
425 execution_method => exec_method,
426 execution_file_name => x_execution_file_name,
427 subroutine_name => x_subroutine_name,
428 icon_name => null,
429 language_code => userenv('LANG'),
433 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
430 execution_file_path => x_execution_file_path);
431 exception
432 when DUP_VAL_ON_INDEX then
434 fnd_message.set_token('ROUTINE', 'FND_EXECUTABLES_PKG.LOAD_ROW');
435 fnd_message.set_token('ERRNO', SQLCODE);
436 fnd_message.set_token('REASON', SQLERRM);
437 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
438 fnd_log.message(FND_LOG.LEVEL_EVENT,
439 'fnd.plsql.fnd_executables_pkg.load_row.exception', FALSE);
440 end if;
441 when others then
442 fnd_message.set_name('FND','GENERIC-INTERNAL ERROR');
443 fnd_message.set_token('REASON',fnd_program.message);
444 app_exception.raise_exception;
445 end;
446 end;
447 end LOAD_ROW;
448
449 procedure DELETE_ROW (
450 X_APPLICATION_ID in NUMBER,
451 X_EXECUTABLE_ID in NUMBER
452 ) is
453 begin
454 delete from FND_EXECUTABLES
455 where APPLICATION_ID = X_APPLICATION_ID
456 and EXECUTABLE_ID = X_EXECUTABLE_ID;
457
458 if (sql%notfound) then
459 raise no_data_found;
460 end if;
461
462 delete from FND_EXECUTABLES_TL
463 where APPLICATION_ID = X_APPLICATION_ID
464 and EXECUTABLE_ID = X_EXECUTABLE_ID;
465
466 if (sql%notfound) then
467 raise no_data_found;
468 end if;
469 end DELETE_ROW;
470
471
472 procedure ADD_LANGUAGE
473 is
474 begin
475
476 /* Mar/19/03 requested by Ric Ginsberg */
477 /* The following delete and update statements are commented out */
478 /* as a quick workaround to fix the time-consuming table handler issue */
479 /* Eventually we'll need to turn them into a separate fix_language procedure */
480 /*
481
482 delete from FND_EXECUTABLES_TL T
483 where not exists
484 (select NULL
485 from FND_EXECUTABLES B
486 where B.APPLICATION_ID = T.APPLICATION_ID
487 and B.EXECUTABLE_ID = T.EXECUTABLE_ID
488 );
489
490 update FND_EXECUTABLES_TL T set (
491 USER_EXECUTABLE_NAME,
492 DESCRIPTION
493 ) = (select
494 B.USER_EXECUTABLE_NAME,
495 B.DESCRIPTION
496 from FND_EXECUTABLES_TL B
497 where B.APPLICATION_ID = T.APPLICATION_ID
498 and B.EXECUTABLE_ID = T.EXECUTABLE_ID
499 and B.LANGUAGE = T.SOURCE_LANG)
500 where (
501 T.APPLICATION_ID,
502 T.EXECUTABLE_ID,
503 T.LANGUAGE
504 ) in (select
505 SUBT.APPLICATION_ID,
506 SUBT.EXECUTABLE_ID,
507 SUBT.LANGUAGE
508 from FND_EXECUTABLES_TL SUBB, FND_EXECUTABLES_TL SUBT
509 where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
510 and SUBB.EXECUTABLE_ID = SUBT.EXECUTABLE_ID
511 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
512 and (SUBB.USER_EXECUTABLE_NAME <> SUBT.USER_EXECUTABLE_NAME
513 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
514 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
515 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
516 ));
517 */
518
519 insert into FND_EXECUTABLES_TL (
520 APPLICATION_ID,
521 EXECUTABLE_ID,
522 CREATION_DATE,
523 CREATED_BY,
524 LAST_UPDATE_DATE,
525 LAST_UPDATED_BY,
526 LAST_UPDATE_LOGIN,
527 USER_EXECUTABLE_NAME,
528 DESCRIPTION,
529 LANGUAGE,
530 SOURCE_LANG
531 ) select
532 B.APPLICATION_ID,
533 B.EXECUTABLE_ID,
534 B.CREATION_DATE,
535 B.CREATED_BY,
536 B.LAST_UPDATE_DATE,
537 B.LAST_UPDATED_BY,
538 B.LAST_UPDATE_LOGIN,
539 B.USER_EXECUTABLE_NAME,
540 B.DESCRIPTION,
541 L.LANGUAGE_CODE,
542 B.SOURCE_LANG
543 from FND_EXECUTABLES_TL B, FND_LANGUAGES L
544 where L.INSTALLED_FLAG in ('I', 'B')
545 and B.LANGUAGE = userenv('LANG')
546 and not exists
547 (select NULL
548 from FND_EXECUTABLES_TL T
549 where T.APPLICATION_ID = B.APPLICATION_ID
550 and T.EXECUTABLE_ID = B.EXECUTABLE_ID
551 and T.LANGUAGE = L.LANGUAGE_CODE);
552 end ADD_LANGUAGE;
553
554 end FND_EXECUTABLES_PKG;