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