1 package body EDR_FILES_PKG as
2 /* $Header: EDRGFILB.pls 120.2.12000000.1 2007/01/18 05:53:25 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_FILE_ID in NUMBER,
6 X_FILE_NAME in VARCHAR2,
7 X_ORIGINAL_FILE_NAME in VARCHAR2,
8 X_VERSION_LABEL in VARCHAR2,
9 X_CATEGORY_ID in NUMBER,
10 X_CONTENT_TYPE in VARCHAR2,
11 X_FILE_FORMAT in VARCHAR2,
12 X_STATUS in VARCHAR2,
13 X_VERSION_NUMBER in NUMBER,
14 X_FND_DOCUMENT_ID in NUMBER,
15 X_ATTRIBUTE_CATEGORY in VARCHAR2,
16 X_ATTRIBUTE1 in VARCHAR2,
17 X_ATTRIBUTE2 in VARCHAR2,
18 X_ATTRIBUTE3 in VARCHAR2,
19 X_ATTRIBUTE4 in VARCHAR2,
20 X_ATTRIBUTE5 in VARCHAR2,
21 X_ATTRIBUTE6 in VARCHAR2,
22 X_ATTRIBUTE7 in VARCHAR2,
23 X_ATTRIBUTE8 in VARCHAR2,
24 X_ATTRIBUTE9 in VARCHAR2,
25 X_ATTRIBUTE10 in VARCHAR2,
26 X_ATTRIBUTE11 in VARCHAR2,
27 X_ATTRIBUTE12 in VARCHAR2,
28 X_ATTRIBUTE13 in VARCHAR2,
29 X_ATTRIBUTE14 in VARCHAR2,
30 X_ATTRIBUTE15 in VARCHAR2,
31 X_DESCRIPTION in VARCHAR2,
32 X_CREATION_DATE in DATE,
33 X_CREATED_BY in NUMBER,
34 X_LAST_UPDATE_DATE in DATE,
35 X_LAST_UPDATED_BY in NUMBER,
36 X_LAST_UPDATE_LOGIN in NUMBER)
37 is
38 cursor C is select ROWID from EDR_FILES_B
39 where FILE_ID = X_FILE_ID
40 ;
41 begin
42 insert into EDR_FILES_B (
43 FILE_ID,
44 FILE_NAME,
45 ORIGINAL_FILE_NAME,
46 VERSION_LABEL,
47 CATEGORY_ID,
48 CONTENT_TYPE,
49 ATTRIBUTE4,
50 ATTRIBUTE2,
51 ATTRIBUTE3,
52 ATTRIBUTE_CATEGORY,
53 ATTRIBUTE1,
54 FILE_FORMAT,
55 STATUS,
56 VERSION_NUMBER,
57 FND_DOCUMENT_ID,
58 ATTRIBUTE5,
59 ATTRIBUTE6,
60 ATTRIBUTE7,
61 ATTRIBUTE8,
62 ATTRIBUTE9,
63 ATTRIBUTE10,
64 ATTRIBUTE11,
65 ATTRIBUTE12,
66 ATTRIBUTE13,
67 ATTRIBUTE14,
68 ATTRIBUTE15,
69 CREATION_DATE,
70 CREATED_BY,
71 LAST_UPDATE_DATE,
72 LAST_UPDATED_BY,
73 LAST_UPDATE_LOGIN
74 ) values (
75 X_FILE_ID,
76 X_FILE_NAME,
77 X_ORIGINAL_FILE_NAME,
78 X_VERSION_LABEL,
79 X_CATEGORY_ID,
80 X_CONTENT_TYPE,
81 X_ATTRIBUTE4,
82 X_ATTRIBUTE2,
83 X_ATTRIBUTE3,
84 X_ATTRIBUTE_CATEGORY,
85 X_ATTRIBUTE1,
86 X_FILE_FORMAT,
87 X_STATUS,
88 X_VERSION_NUMBER,
89 X_FND_DOCUMENT_ID,
90 X_ATTRIBUTE5,
91 X_ATTRIBUTE6,
92 X_ATTRIBUTE7,
93 X_ATTRIBUTE8,
94 X_ATTRIBUTE9,
95 X_ATTRIBUTE10,
96 X_ATTRIBUTE11,
97 X_ATTRIBUTE12,
98 X_ATTRIBUTE13,
99 X_ATTRIBUTE14,
100 X_ATTRIBUTE15,
101 X_CREATION_DATE,
102 X_CREATED_BY,
103 X_LAST_UPDATE_DATE,
104 X_LAST_UPDATED_BY,
105 X_LAST_UPDATE_LOGIN
106 );
107
108 insert into EDR_FILES_TL (
109 LAST_UPDATE_DATE,
110 LAST_UPDATED_BY,
111 LAST_UPDATE_LOGIN,
112 DESCRIPTION,
113 CREATED_BY,
114 CREATION_DATE,
115 FILE_ID,
116 LANGUAGE,
117 SOURCE_LANG
118 ) select
119 X_LAST_UPDATE_DATE,
120 X_LAST_UPDATED_BY,
121 X_LAST_UPDATE_LOGIN,
122 X_DESCRIPTION,
123 X_CREATED_BY,
124 X_CREATION_DATE,
125 X_FILE_ID,
126 L.LANGUAGE_CODE,
127 userenv('LANG')
128 from FND_LANGUAGES L
129 where L.INSTALLED_FLAG in ('I', 'B')
130 and not exists
131 (select NULL
132 from EDR_FILES_TL T
133 where T.FILE_ID = X_FILE_ID
134 and T.LANGUAGE = L.LANGUAGE_CODE);
135
136 open c;
137
138 fetch c into X_ROWID;
139 if (c%notfound) then
140 close c;
141 raise no_data_found;
142 end if;
143
144 close c;
145 end INSERT_ROW;
146
147 procedure LOCK_ROW (
148 X_FILE_ID in NUMBER,
149 X_FILE_NAME in VARCHAR2,
150 X_ORIGINAL_FILE_NAME in VARCHAR2,
151 X_VERSION_LABEL in VARCHAR2,
152 X_CATEGORY_ID in NUMBER,
153 X_CONTENT_TYPE in VARCHAR2,
154 X_FILE_FORMAT in VARCHAR2,
155 X_STATUS in VARCHAR2,
156 X_VERSION_NUMBER in NUMBER,
157 X_FND_DOCUMENT_ID in NUMBER,
158 X_ATTRIBUTE_CATEGORY in VARCHAR2,
159 X_ATTRIBUTE1 in VARCHAR2,
160 X_ATTRIBUTE2 in VARCHAR2,
161 X_ATTRIBUTE3 in VARCHAR2,
162 X_ATTRIBUTE4 in VARCHAR2,
163 X_ATTRIBUTE5 in VARCHAR2,
164 X_ATTRIBUTE6 in VARCHAR2,
165 X_ATTRIBUTE7 in VARCHAR2,
166 X_ATTRIBUTE8 in VARCHAR2,
167 X_ATTRIBUTE9 in VARCHAR2,
168 X_ATTRIBUTE10 in VARCHAR2,
169 X_ATTRIBUTE11 in VARCHAR2,
170 X_ATTRIBUTE12 in VARCHAR2,
171 X_ATTRIBUTE13 in VARCHAR2,
172 X_ATTRIBUTE14 in VARCHAR2,
173 X_ATTRIBUTE15 in VARCHAR2,
174 X_DESCRIPTION in VARCHAR2
175 ) is
176 cursor c is select
177 FILE_NAME,
178 ORIGINAL_FILE_NAME,
179 VERSION_LABEL,
180 CATEGORY_ID,
181 CONTENT_TYPE,
182 ATTRIBUTE4,
183 ATTRIBUTE2,
184 ATTRIBUTE3,
185 ATTRIBUTE_CATEGORY,
186 ATTRIBUTE1,
187 FILE_FORMAT,
188 STATUS,
189 VERSION_NUMBER,
190 FND_DOCUMENT_ID,
191 ATTRIBUTE5,
192 ATTRIBUTE6,
193 ATTRIBUTE7,
194 ATTRIBUTE8,
195 ATTRIBUTE9,
196 ATTRIBUTE10,
197 ATTRIBUTE11,
198 ATTRIBUTE12,
199 ATTRIBUTE13,
200 ATTRIBUTE14,
201 ATTRIBUTE15
202 from EDR_FILES_B
203 where FILE_ID = X_FILE_ID
204 for update of FILE_ID nowait;
205 recinfo c%rowtype;
206 cursor c1 is select
207 DESCRIPTION,
208 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
209 from EDR_FILES_TL
210 where FILE_ID = X_FILE_ID
211 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
212 for update of FILE_ID nowait;
213 begin
214 open c;
215 fetch c into recinfo;
216 if (c%notfound) then
217 close c;
218 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
219 app_exception.raise_exception;
220 end if;
221 close c;
222 if ( (recinfo.FILE_NAME = X_FILE_NAME)
223 AND (recinfo.ORIGINAL_FILE_NAME = X_ORIGINAL_FILE_NAME)
224 AND (recinfo.VERSION_LABEL = X_VERSION_LABEL)
225 AND (recinfo.CATEGORY_ID = X_CATEGORY_ID)
226 AND (recinfo.CONTENT_TYPE = X_CONTENT_TYPE)
227 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
228 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
229 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
230 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
231 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
232 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
233 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
234 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
235 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
236 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
237 AND (recinfo.FILE_FORMAT = X_FILE_FORMAT)
238 AND ((recinfo.STATUS = X_STATUS)
239 OR ((recinfo.STATUS is null) AND (X_STATUS is null)))
240 AND (recinfo.VERSION_NUMBER = X_VERSION_NUMBER)
241 AND ((recinfo.FND_DOCUMENT_ID = X_FND_DOCUMENT_ID)
242 OR ((recinfo.FND_DOCUMENT_ID is null) AND (X_FND_DOCUMENT_ID is null)))
243 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
244 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
245 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
246 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
247 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
248 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
249 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
250 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
251 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
252 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
253 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
254 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
255 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
256 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
257 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
258 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
259 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
260 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
261 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
262 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
263 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
264 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
265 ) then
266 null;
267 else
268 NULL;
269 -- fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
270 -- app_exception.raise_exception;
271 end if;
272 for tlinfo in c1 loop
273 if (tlinfo.BASELANG = 'Y') then
274 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
275 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
276 ) then
277 null;
278 else
279 null;
280 -- fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
281 -- app_exception.raise_exception;
282 end if;
283 end if;
284 end loop;
285 return;
286 end LOCK_ROW;
287
288 procedure UPDATE_ROW (
289 X_FILE_ID in NUMBER,
290 X_FILE_NAME in VARCHAR2,
291 X_ORIGINAL_FILE_NAME in VARCHAR2,
292 X_VERSION_LABEL in VARCHAR2,
293 X_CATEGORY_ID in NUMBER,
294 X_CONTENT_TYPE in VARCHAR2,
295 X_FILE_FORMAT in VARCHAR2,
296 X_STATUS in VARCHAR2,
297 X_VERSION_NUMBER in NUMBER,
298 X_FND_DOCUMENT_ID in NUMBER,
299 X_ATTRIBUTE_CATEGORY in VARCHAR2,
300 X_ATTRIBUTE1 in VARCHAR2,
301 X_ATTRIBUTE2 in VARCHAR2,
302 X_ATTRIBUTE3 in VARCHAR2,
303 X_ATTRIBUTE4 in VARCHAR2,
304 X_ATTRIBUTE5 in VARCHAR2,
305 X_ATTRIBUTE6 in VARCHAR2,
306 X_ATTRIBUTE7 in VARCHAR2,
307 X_ATTRIBUTE8 in VARCHAR2,
308 X_ATTRIBUTE9 in VARCHAR2,
309 X_ATTRIBUTE10 in VARCHAR2,
310 X_ATTRIBUTE11 in VARCHAR2,
311 X_ATTRIBUTE12 in VARCHAR2,
312 X_ATTRIBUTE13 in VARCHAR2,
313 X_ATTRIBUTE14 in VARCHAR2,
314 X_ATTRIBUTE15 in VARCHAR2,
315 X_DESCRIPTION in VARCHAR2,
316 X_LAST_UPDATE_DATE in DATE,
317 X_LAST_UPDATED_BY in NUMBER,
318 X_LAST_UPDATE_LOGIN in NUMBER
319 ) is
320 begin
321 update EDR_FILES_B set
322 FILE_NAME = X_FILE_NAME,
323 ORIGINAL_FILE_NAME = X_ORIGINAL_FILE_NAME,
324 VERSION_LABEL = X_VERSION_LABEL,
325 CATEGORY_ID = X_CATEGORY_ID,
326 ATTRIBUTE4 = X_ATTRIBUTE4,
327 ATTRIBUTE2 = X_ATTRIBUTE2,
328 ATTRIBUTE3 = X_ATTRIBUTE3,
329 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
330 ATTRIBUTE1 = X_ATTRIBUTE1,
331 STATUS = X_STATUS,
332 VERSION_NUMBER = X_VERSION_NUMBER,
333 FND_DOCUMENT_ID = X_FND_DOCUMENT_ID,
334 ATTRIBUTE5 = X_ATTRIBUTE5,
335 ATTRIBUTE6 = X_ATTRIBUTE6,
336 ATTRIBUTE7 = X_ATTRIBUTE7,
337 ATTRIBUTE8 = X_ATTRIBUTE8,
338 ATTRIBUTE9 = X_ATTRIBUTE9,
339 ATTRIBUTE10 = X_ATTRIBUTE10,
340 ATTRIBUTE11 = X_ATTRIBUTE11,
341 ATTRIBUTE12 = X_ATTRIBUTE12,
342 ATTRIBUTE13 = X_ATTRIBUTE13,
343 ATTRIBUTE14 = X_ATTRIBUTE14,
344 ATTRIBUTE15 = X_ATTRIBUTE15,
345 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
346 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
347 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
348 where FILE_ID = X_FILE_ID;
349 if (sql%notfound) then
350 raise no_data_found;
351 end if;
352 update EDR_FILES_TL set
353 DESCRIPTION = X_DESCRIPTION,
354 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
355 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
356 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
357 SOURCE_LANG = userenv('LANG')
358 where FILE_ID = X_FILE_ID
359 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
360 if (sql%notfound) then
361 raise no_data_found;
362 end if;
363 end UPDATE_ROW;
364
365 procedure DELETE_ROW (
366 X_FILE_ID in NUMBER
367 ) is
368 begin
369 delete from EDR_FILES_TL
370 where FILE_ID = X_FILE_ID;
371 if (sql%notfound) then
372 raise no_data_found;
373 end if;
374 delete from EDR_FILES_B
375 where FILE_ID = X_FILE_ID;
376 if (sql%notfound) then
377 raise no_data_found;
378 end if;
379 end DELETE_ROW;
380
381 procedure ADD_LANGUAGE
382 is
383 begin
384 delete from EDR_FILES_TL T
385 where not exists
386 (select NULL
387 from EDR_FILES_B B
388 where B.FILE_ID = T.FILE_ID
389 );
390 update EDR_FILES_TL T set (
391 DESCRIPTION
392 ) = (select
393 B.DESCRIPTION
394 from EDR_FILES_TL B
395 where B.FILE_ID = T.FILE_ID
396 and B.LANGUAGE = T.SOURCE_LANG)
397 where (
398 T.FILE_ID,
399 T.LANGUAGE
400 ) in (select
401 SUBT.FILE_ID,
402 SUBT.LANGUAGE
403 from EDR_FILES_TL SUBB, EDR_FILES_TL SUBT
404 where SUBB.FILE_ID = SUBT.FILE_ID
405 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
406 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
407 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
408 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
409 ));
410 insert into EDR_FILES_TL (
411 LAST_UPDATE_DATE,
412 LAST_UPDATED_BY,
413 LAST_UPDATE_LOGIN,
417 FILE_ID,
414 DESCRIPTION,
415 CREATED_BY,
416 CREATION_DATE,
418 LANGUAGE,
419 SOURCE_LANG
420 ) select
421 B.LAST_UPDATE_DATE,
422 B.LAST_UPDATED_BY,
423 B.LAST_UPDATE_LOGIN,
424 B.DESCRIPTION,
425 B.CREATED_BY,
426 B.CREATION_DATE,
427 B.FILE_ID,
428 L.LANGUAGE_CODE,
429 B.SOURCE_LANG
430 from EDR_FILES_TL B, FND_LANGUAGES L
431 where L.INSTALLED_FLAG in ('I', 'B')
432 and B.LANGUAGE = userenv('LANG')
433 and not exists
434 (select NULL
435 from EDR_FILES_TL T
436 where T.FILE_ID = B.FILE_ID
437 and T.LANGUAGE = L.LANGUAGE_CODE);
438 end ADD_LANGUAGE;
439 -- Bug 4472517 : start
440
441 FUNCTION check_document_references(X_Document_id NUMBER,X_ENTITY_NAME VARCHAR2)
442 RETURN NUMBER IS
443 reference_count NUMBER;
444 BEGIN
445
446 SELECT count(*) INTO reference_count FROM fnd_attached_documents
447 WHERE document_id = X_document_id and ENTITY_NAME <> X_ENTITY_NAME;
448
449 IF (reference_count > 0) THEN
450 reference_count := 1;
451 ELSE
452 reference_count := 0;
453 END IF;
454 RETURN reference_count;
455 END;
456
457 -- Bug 4472517 : End
458
459 end EDR_FILES_PKG;
[Home] [Help]