[Home] [Help]
PACKAGE BODY: APPS.FRM_DIRECTORY_PKG
Source
1 package body FRM_DIRECTORY_PKG as
2 /* $Header: frmdirb.pls 120.2 2005/09/29 00:03:20 ghooker noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_DIRECTORY_ID in NUMBER,
6 X_PARENT_ID in NUMBER,
7 X_SEQUENCE_NUMBER in NUMBER,
8 X_OBJECT_VERSION_NUMBER in NUMBER,
9 X_USER_NAME in VARCHAR2,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER
15 ) is
16 cursor C is select ROWID from FRM_DIRECTORY_B
17 where DIRECTORY_ID = X_DIRECTORY_ID
18 ;
19 begin
20 insert into FRM_DIRECTORY_B (
21 DIRECTORY_ID,
22 PARENT_ID,
23 SEQUENCE_NUMBER,
24 OBJECT_VERSION_NUMBER,
25 CREATION_DATE,
26 CREATED_BY,
27 LAST_UPDATE_DATE,
28 LAST_UPDATED_BY,
29 LAST_UPDATE_LOGIN
30 ) values (
31 X_DIRECTORY_ID,
32 X_PARENT_ID,
33 X_SEQUENCE_NUMBER,
34 X_OBJECT_VERSION_NUMBER,
35 X_CREATION_DATE,
36 X_CREATED_BY,
37 X_LAST_UPDATE_DATE,
38 X_LAST_UPDATED_BY,
39 X_LAST_UPDATE_LOGIN
40 );
41
42 insert into FRM_DIRECTORY_TL (
43 DIRECTORY_ID,
44 USER_NAME,
45 CREATION_DATE,
46 CREATED_BY,
47 LAST_UPDATED_BY,
48 LAST_UPDATE_LOGIN,
49 LAST_UPDATE_DATE,
50 LANGUAGE,
51 SOURCE_LANG
52 ) select
53 X_DIRECTORY_ID,
54 X_USER_NAME,
55 X_CREATION_DATE,
56 X_CREATED_BY,
57 X_LAST_UPDATED_BY,
58 X_LAST_UPDATE_LOGIN,
59 X_LAST_UPDATE_DATE,
60 L.LANGUAGE_CODE,
61 userenv('LANG')
62 from FND_LANGUAGES L
63 where L.INSTALLED_FLAG in ('I', 'B')
64 and not exists
65 (select NULL
66 from FRM_DIRECTORY_TL T
67 where T.DIRECTORY_ID = X_DIRECTORY_ID
68 and T.LANGUAGE = L.LANGUAGE_CODE);
69
70 open c;
71 fetch c into X_ROWID;
72 if (c%notfound) then
73 close c;
74 raise no_data_found;
75 end if;
76 close c;
77
78 end INSERT_ROW;
79
80 procedure LOCK_ROW (
81 X_DIRECTORY_ID in NUMBER,
82 X_PARENT_ID in NUMBER,
83 X_SEQUENCE_NUMBER in NUMBER,
84 X_OBJECT_VERSION_NUMBER in NUMBER,
85 X_USER_NAME in VARCHAR2
86 ) is
87 cursor c is select
88 PARENT_ID,
89 SEQUENCE_NUMBER,
90 OBJECT_VERSION_NUMBER
91 from FRM_DIRECTORY_B
92 where DIRECTORY_ID = X_DIRECTORY_ID
93 for update of DIRECTORY_ID nowait;
94 recinfo c%rowtype;
95
96 cursor c1 is select
97 USER_NAME,
98 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
99 from FRM_DIRECTORY_TL
100 where DIRECTORY_ID = X_DIRECTORY_ID
101 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
102 for update of DIRECTORY_ID nowait;
103 begin
104 open c;
105 fetch c into recinfo;
106 if (c%notfound) then
107 close c;
108 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
109 app_exception.raise_exception;
110 end if;
111 close c;
112 if ( ((recinfo.PARENT_ID = X_PARENT_ID)
113 OR ((recinfo.PARENT_ID is null) AND (X_PARENT_ID is null)))
114 AND (recinfo.SEQUENCE_NUMBER = X_SEQUENCE_NUMBER)
115 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
116 ) then
117 null;
118 else
119 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
120 app_exception.raise_exception;
121 end if;
122
123 for tlinfo in c1 loop
124 if (tlinfo.BASELANG = 'Y') then
125 if ( (tlinfo.USER_NAME = X_USER_NAME)
126 ) then
127 null;
128 else
129 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
130 app_exception.raise_exception;
131 end if;
132 end if;
133 end loop;
134 return;
135 end LOCK_ROW;
136
137 procedure UPDATE_ROW (
138 X_DIRECTORY_ID in NUMBER,
139 X_PARENT_ID in NUMBER,
140 X_SEQUENCE_NUMBER in NUMBER,
141 X_OBJECT_VERSION_NUMBER in NUMBER,
142 X_USER_NAME in VARCHAR2,
143 X_LAST_UPDATE_DATE in DATE,
144 X_LAST_UPDATED_BY in NUMBER,
145 X_LAST_UPDATE_LOGIN in NUMBER
146 ) is
147 begin
148 update FRM_DIRECTORY_B set
149 PARENT_ID = X_PARENT_ID,
150 SEQUENCE_NUMBER = X_SEQUENCE_NUMBER,
151 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
152 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
153 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
154 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
155 where DIRECTORY_ID = X_DIRECTORY_ID;
156
157 if (sql%notfound) then
158 raise no_data_found;
159 end if;
160
161 update FRM_DIRECTORY_TL set
162 USER_NAME = X_USER_NAME,
163 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
164 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
165 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
166 SOURCE_LANG = userenv('LANG')
167 where DIRECTORY_ID = X_DIRECTORY_ID
168 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
169
170 if (sql%notfound) then
171 raise no_data_found;
172 end if;
173 end UPDATE_ROW;
174
175 procedure DELETE_ROW (
176 X_DIRECTORY_ID in NUMBER
177 ) is
178 begin
179 delete from FRM_DIRECTORY_TL
180 where DIRECTORY_ID = X_DIRECTORY_ID;
181
182 if (sql%notfound) then
183 raise no_data_found;
184 end if;
185
186 delete from FRM_DIRECTORY_B
187 where DIRECTORY_ID = X_DIRECTORY_ID;
188
189 if (sql%notfound) then
190 raise no_data_found;
191 end if;
192 end DELETE_ROW;
193
194 procedure ADD_LANGUAGE
195 is
196 begin
197 delete from FRM_DIRECTORY_TL T
198 where not exists
199 (select NULL
200 from FRM_DIRECTORY_B B
201 where B.DIRECTORY_ID = T.DIRECTORY_ID
202 );
203
204 update FRM_DIRECTORY_TL T set (
205 USER_NAME
206 ) = (select
207 B.USER_NAME
208 from FRM_DIRECTORY_TL B
209 where B.DIRECTORY_ID = T.DIRECTORY_ID
210 and B.LANGUAGE = T.SOURCE_LANG)
211 where (
212 T.DIRECTORY_ID,
213 T.LANGUAGE
214 ) in (select
215 SUBT.DIRECTORY_ID,
216 SUBT.LANGUAGE
217 from FRM_DIRECTORY_TL SUBB, FRM_DIRECTORY_TL SUBT
218 where SUBB.DIRECTORY_ID = SUBT.DIRECTORY_ID
219 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
220 and (SUBB.USER_NAME <> SUBT.USER_NAME
221 ));
222
223 insert into FRM_DIRECTORY_TL (
224 DIRECTORY_ID,
225 USER_NAME,
226 CREATION_DATE,
227 CREATED_BY,
228 LAST_UPDATED_BY,
229 LAST_UPDATE_LOGIN,
230 LAST_UPDATE_DATE,
231 LANGUAGE,
232 SOURCE_LANG
233 ) select /*+ ORDERED */
234 B.DIRECTORY_ID,
235 B.USER_NAME,
236 B.CREATION_DATE,
237 B.CREATED_BY,
238 B.LAST_UPDATED_BY,
239 B.LAST_UPDATE_LOGIN,
240 B.LAST_UPDATE_DATE,
241 L.LANGUAGE_CODE,
242 B.SOURCE_LANG
243 from FRM_DIRECTORY_TL B, FND_LANGUAGES L
244 where L.INSTALLED_FLAG in ('I', 'B')
245 and B.LANGUAGE = userenv('LANG')
246 and not exists
247 (select NULL
248 from FRM_DIRECTORY_TL T
249 where T.DIRECTORY_ID = B.DIRECTORY_ID
250 and T.LANGUAGE = L.LANGUAGE_CODE);
251 end ADD_LANGUAGE;
252
253 --------------------------------------------------------------------------------
254 -- PROCEDURE: LOAD_ROW --
255 -- --
256 -- DESCRIPTION: Load a row into the FRM_DIRECTORY entity. --
257 -- This proc is called from the apps loader. --
258 -- --
259 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/fndloadqr.txt --
260 -- --
261 -- --
262 -- MODIFICATION HISTORY --
263 -- Date Username Description --
264 -- 12-Jul-04 MLUETCHF CREATED --
265 --------------------------------------------------------------------------------
266
267 procedure LOAD_ROW(
268 x_directory_id IN VARCHAR2,
269 x_parent_id IN VARCHAR2,
270 x_sequence_number IN VARCHAR2,
271 x_object_version_number IN VARCHAR2,
272 x_user_name IN VARCHAR2,
273 x_owner IN VARCHAR2,
274 x_last_update_date IN VARCHAR2,
275 x_custom_mode IN VARCHAR2
276 )
277 is
278 l_row_id varchar2(64);
279 f_luby number; -- entity owner in file
280 f_ludate date; -- entity update date in file
281 db_luby number; -- entity owner in db
282 db_ludate date; -- entity update date in db
283 begin
284 -- Translate owner to file_last_updated_by
285 f_luby := fnd_load_util.owner_id(x_owner);
286
287 -- Translate char last_update_date to date
288 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
289 begin
290 select LAST_UPDATED_BY, LAST_UPDATE_DATE
291 into db_luby, db_ludate
292 from FRM_DIRECTORY_B
293 where DIRECTORY_ID = x_directory_id;
294
295 -- Test for customization and version
296 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
297 db_ludate, x_custom_mode)) then
298 -- Update existing row
299 FRM_DIRECTORY_PKG.Update_Row(
300 X_DIRECTORY_ID => x_directory_id,
301 X_PARENT_ID => x_parent_id,
302 X_SEQUENCE_NUMBER => x_sequence_number,
303 X_OBJECT_VERSION_NUMBER => x_object_version_number,
304 X_USER_NAME => x_user_name,
305 X_LAST_UPDATE_DATE => f_ludate,
306 X_LAST_UPDATED_BY => f_luby,
307 X_LAST_UPDATE_LOGIN => 0
308 );
309 end if;
310 exception
311 when no_data_found then
312 -- Record doesn't exist - insert in all cases
313 FRM_DIRECTORY_PKG.Insert_Row(
314 X_ROWID => l_row_id,
315 X_DIRECTORY_ID => x_directory_id,
316 X_PARENT_ID => x_parent_id,
317 X_SEQUENCE_NUMBER => x_sequence_number,
318 X_OBJECT_VERSION_NUMBER => x_object_version_number,
319 X_USER_NAME => x_user_name,
320 X_CREATION_DATE => f_ludate,
321 X_CREATED_BY => f_luby,
322 X_LAST_UPDATE_DATE => f_ludate,
323 X_LAST_UPDATED_BY => f_luby,
324 X_LAST_UPDATE_LOGIN => 0
325 );
326 end;
327 end LOAD_ROW;
328
329
330 --------------------------------------------------------------------------------
331 -- PROCEDURE: TRANSLATE_ROW --
332 -- --
333 -- DESCRIPTION: Load a translation into the FRM_DIRECTORY entity. --
334 -- This proc is called from the apps loader. --
335 -- --
336 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/fndloadqr.txt --
337 -- --
338 -- --
339 -- MODIFICATION HISTORY --
340 -- Date Username Description --
341 -- 12-Jul-04 MLUETCHF CREATED --
342 --------------------------------------------------------------------------------
343
344 procedure TRANSLATE_ROW(
345 x_directory_id IN VARCHAR2,
346 x_user_name IN VARCHAR2,
347 x_owner IN VARCHAR2,
348 x_last_update_date IN VARCHAR2,
349 x_custom_mode IN VARCHAR2
350 )
351 is
352 f_luby number; -- entity owner in file
353 f_ludate date; -- entity update date in file
354 db_luby number; -- entity owner in db
355 db_ludate date; -- entity update date in db
356 begin
357 -- Translate owner to file_last_updated_by
358 f_luby := fnd_load_util.owner_id(x_owner);
359
360 -- Translate char last_update_date to date
361 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
362 begin
363 select LAST_UPDATED_BY, LAST_UPDATE_DATE
364 into db_luby, db_ludate
365 from FRM_DIRECTORY_TL
366 where DIRECTORY_ID = x_directory_id
367 and LANGUAGE = userenv('LANG');
368
369 -- Test for customization and version
370 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
371 db_ludate, x_custom_mode)) then
372
373 update FRM_DIRECTORY_TL
374 set USER_NAME = x_user_name,
375 LAST_UPDATE_DATE = f_ludate,
376 LAST_UPDATED_BY = f_luby,
377 LAST_UPDATE_LOGIN = 0,
378 SOURCE_LANG = userenv('LANG')
379 where DIRECTORY_ID = x_directory_id
380 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG)
381 ;
382 end if;
383 exception
384 when no_data_found then
385 -- Do not insert missing translations, skip this row
386 null;
387 end;
388 end TRANSLATE_ROW;
389
390 end FRM_DIRECTORY_PKG;