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