1 PACKAGE BODY OKC_FOLDERS_PVT AS
2 /* $Header: OKCVFLDB.pls 120.1 2005/08/05 13:43:13 ssivarap noship $ */
3
4
5 PROCEDURE INSERT_ROW (
6 X_ROWID IN OUT nocopy VARCHAR2,
7 X_FOLDER_ID IN NUMBER,
8 X_OBJECT_VERSION_NUMBER IN NUMBER,
9 X_SAT_CODE IN VARCHAR2,
10 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
11 X_ATTRIBUTE1 IN VARCHAR2,
12 X_ATTRIBUTE2 IN VARCHAR2,
13 X_ATTRIBUTE3 IN VARCHAR2,
14 X_ATTRIBUTE4 IN VARCHAR2,
15 X_ATTRIBUTE5 IN VARCHAR2,
16 X_ATTRIBUTE6 IN VARCHAR2,
17 X_ATTRIBUTE7 IN VARCHAR2,
18 X_ATTRIBUTE8 IN VARCHAR2,
19 X_ATTRIBUTE9 IN VARCHAR2,
20 X_ATTRIBUTE10 IN VARCHAR2,
21 X_ATTRIBUTE11 IN VARCHAR2,
22 X_ATTRIBUTE12 IN VARCHAR2,
23 X_ATTRIBUTE13 IN VARCHAR2,
24 X_ATTRIBUTE14 IN VARCHAR2,
25 X_ATTRIBUTE15 IN VARCHAR2,
26 X_FOLDER_NAME IN VARCHAR2,
27 X_DESCRIPTION IN VARCHAR2,
28 X_ORG_ID IN NUMBER,
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 OKC_FOLDERS_ALL_B
36 WHERE FOLDER_ID = X_FOLDER_ID
37 ;
38 BEGIN
39 INSERT INTO OKC_FOLDERS_ALL_B (
40 FOLDER_ID,
41 OBJECT_VERSION_NUMBER,
42 ORG_ID,
43 SAT_CODE,
44 ATTRIBUTE_CATEGORY,
45 ATTRIBUTE1,
46 ATTRIBUTE2,
47 ATTRIBUTE3,
48 ATTRIBUTE4,
49 ATTRIBUTE5,
50 ATTRIBUTE6,
51 ATTRIBUTE7,
52 ATTRIBUTE8,
53 ATTRIBUTE9,
54 ATTRIBUTE10,
55 ATTRIBUTE11,
56 ATTRIBUTE12,
57 ATTRIBUTE13,
58 ATTRIBUTE14,
59 ATTRIBUTE15,
60 CREATION_DATE,
61 CREATED_BY,
62 LAST_UPDATE_DATE,
63 LAST_UPDATED_BY,
64 LAST_UPDATE_LOGIN
65 ) VALUES (
66 X_FOLDER_ID,
67 X_OBJECT_VERSION_NUMBER,
68 X_ORG_ID,
69 X_SAT_CODE,
70 X_ATTRIBUTE_CATEGORY,
71 X_ATTRIBUTE1,
72 X_ATTRIBUTE2,
73 X_ATTRIBUTE3,
74 X_ATTRIBUTE4,
75 X_ATTRIBUTE5,
76 X_ATTRIBUTE6,
77 X_ATTRIBUTE7,
78 X_ATTRIBUTE8,
79 X_ATTRIBUTE9,
80 X_ATTRIBUTE10,
81 X_ATTRIBUTE11,
82 X_ATTRIBUTE12,
83 X_ATTRIBUTE13,
84 X_ATTRIBUTE14,
85 X_ATTRIBUTE15,
86 X_CREATION_DATE,
87 X_CREATED_BY,
88 X_LAST_UPDATE_DATE,
89 X_LAST_UPDATED_BY,
90 X_LAST_UPDATE_LOGIN
91 );
92
93 INSERT INTO OKC_FOLDERS_ALL_TL (
94 FOLDER_NAME,
95 DESCRIPTION,
96 CREATED_BY,
97 CREATION_DATE,
98 LAST_UPDATE_DATE,
99 LAST_UPDATED_BY,
100 LAST_UPDATE_LOGIN,
101 FOLDER_ID,
102 LANGUAGE,
103 SOURCE_LANG
104 ) SELECT
105 X_FOLDER_NAME,
106 X_DESCRIPTION,
107 X_CREATED_BY,
108 X_CREATION_DATE,
109 X_LAST_UPDATE_DATE,
110 X_LAST_UPDATED_BY,
111 X_LAST_UPDATE_LOGIN,
112 X_FOLDER_ID,
113 L.LANGUAGE_CODE,
114 USERENV('LANG')
115 FROM FND_LANGUAGES L
116 WHERE L.INSTALLED_FLAG IN ('I', 'B')
117 AND NOT EXISTS
118 (SELECT NULL
119 FROM OKC_FOLDERS_ALL_TL T
120 WHERE T.FOLDER_ID = X_FOLDER_ID
121 AND T.LANGUAGE = L.LANGUAGE_CODE);
122
123 OPEN c;
124 FETCH c INTO X_ROWID;
125 IF (c%NOTFOUND) THEN
126 CLOSE c;
127 RAISE NO_DATA_FOUND;
128 END IF;
129 CLOSE c;
130
131 END INSERT_ROW;
132
133 PROCEDURE LOCK_ROW (
134 X_FOLDER_ID IN NUMBER,
135 X_OBJECT_VERSION_NUMBER IN NUMBER,
136 X_SAT_CODE IN VARCHAR2,
137 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
138 X_ATTRIBUTE1 IN VARCHAR2,
139 X_ATTRIBUTE2 IN VARCHAR2,
140 X_ATTRIBUTE3 IN VARCHAR2,
141 X_ATTRIBUTE4 IN VARCHAR2,
142 X_ATTRIBUTE5 IN VARCHAR2,
143 X_ATTRIBUTE6 IN VARCHAR2,
144 X_ATTRIBUTE7 IN VARCHAR2,
145 X_ATTRIBUTE8 IN VARCHAR2,
146 X_ATTRIBUTE9 IN VARCHAR2,
147 X_ATTRIBUTE10 IN VARCHAR2,
148 X_ATTRIBUTE11 IN VARCHAR2,
149 X_ATTRIBUTE12 IN VARCHAR2,
150 X_ATTRIBUTE13 IN VARCHAR2,
151 X_ATTRIBUTE14 IN VARCHAR2,
152 X_ATTRIBUTE15 IN VARCHAR2,
153 X_FOLDER_NAME IN VARCHAR2,
154 X_DESCRIPTION IN VARCHAR2,
155 X_ORG_ID IN NUMBER
156 ) IS
157 CURSOR c IS SELECT
158 OBJECT_VERSION_NUMBER,
159 SAT_CODE,
160 ATTRIBUTE_CATEGORY,
161 ATTRIBUTE1,
162 ATTRIBUTE2,
163 ATTRIBUTE3,
164 ATTRIBUTE4,
165 ATTRIBUTE5,
166 ATTRIBUTE6,
167 ATTRIBUTE7,
168 ATTRIBUTE8,
169 ATTRIBUTE9,
170 ATTRIBUTE10,
171 ATTRIBUTE11,
172 ATTRIBUTE12,
173 ATTRIBUTE13,
174 ATTRIBUTE14,
175 ATTRIBUTE15,
176 ORG_ID
177 FROM OKC_FOLDERS_ALL_B
178 WHERE FOLDER_ID = X_FOLDER_ID
179 FOR UPDATE OF FOLDER_ID NOWAIT;
180 recinfo c%ROWTYPE;
181
182 CURSOR c1 IS SELECT
183 FOLDER_NAME,
184 DESCRIPTION,
185 DECODE(LANGUAGE, USERENV('LANG'), 'Y', 'N') BASELANG
186 FROM OKC_FOLDERS_ALL_TL
187 WHERE FOLDER_ID = X_FOLDER_ID
188 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
189 FOR UPDATE OF FOLDER_ID NOWAIT;
190 BEGIN
191 OPEN c;
192 FETCH c INTO recinfo;
193 IF (c%NOTFOUND) THEN
194 CLOSE c;
195 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
196 app_exception.raise_exception;
197 END IF;
198 CLOSE c;
199 IF ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
200 AND (recinfo.ORG_ID = X_ORG_ID)
201 AND ((recinfo.SAT_CODE = X_SAT_CODE)
202 OR ((recinfo.SAT_CODE IS NULL) AND (X_SAT_CODE IS NULL)))
203 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
204 OR ((recinfo.ATTRIBUTE_CATEGORY IS NULL) AND (X_ATTRIBUTE_CATEGORY IS NULL)))
205 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
206 OR ((recinfo.ATTRIBUTE1 IS NULL) AND (X_ATTRIBUTE1 IS NULL)))
207 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
208 OR ((recinfo.ATTRIBUTE2 IS NULL) AND (X_ATTRIBUTE2 IS NULL)))
209 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
210 OR ((recinfo.ATTRIBUTE3 IS NULL) AND (X_ATTRIBUTE3 IS NULL)))
211 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
212 OR ((recinfo.ATTRIBUTE4 IS NULL) AND (X_ATTRIBUTE4 IS NULL)))
213 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
214 OR ((recinfo.ATTRIBUTE5 IS NULL) AND (X_ATTRIBUTE5 IS NULL)))
215 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
216 OR ((recinfo.ATTRIBUTE6 IS NULL) AND (X_ATTRIBUTE6 IS NULL)))
217 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
218 OR ((recinfo.ATTRIBUTE7 IS NULL) AND (X_ATTRIBUTE7 IS NULL)))
219 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
220 OR ((recinfo.ATTRIBUTE8 IS NULL) AND (X_ATTRIBUTE8 IS NULL)))
221 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
222 OR ((recinfo.ATTRIBUTE9 IS NULL) AND (X_ATTRIBUTE9 IS NULL)))
223 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
224 OR ((recinfo.ATTRIBUTE10 IS NULL) AND (X_ATTRIBUTE10 IS NULL)))
225 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
226 OR ((recinfo.ATTRIBUTE11 IS NULL) AND (X_ATTRIBUTE11 IS NULL)))
227 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
228 OR ((recinfo.ATTRIBUTE12 IS NULL) AND (X_ATTRIBUTE12 IS NULL)))
229 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
230 OR ((recinfo.ATTRIBUTE13 IS NULL) AND (X_ATTRIBUTE13 IS NULL)))
231 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
232 OR ((recinfo.ATTRIBUTE14 IS NULL) AND (X_ATTRIBUTE14 IS NULL)))
233 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
234 OR ((recinfo.ATTRIBUTE15 IS NULL) AND (X_ATTRIBUTE15 IS NULL)))
235 ) THEN
236 NULL;
237 ELSE
238 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
239 app_exception.raise_exception;
240 END IF;
241
242 FOR tlinfo IN c1 LOOP
243 IF (tlinfo.BASELANG = 'Y') THEN
244 IF ( (tlinfo.FOLDER_NAME = X_FOLDER_NAME)
245 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
246 OR ((tlinfo.DESCRIPTION IS NULL) AND (X_DESCRIPTION IS NULL)))
247 ) THEN
248 NULL;
249 ELSE
250 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
251 app_exception.raise_exception;
252 END IF;
253 END IF;
254 END LOOP;
255 RETURN;
256 END LOCK_ROW;
257
258 PROCEDURE UPDATE_ROW (
259 X_FOLDER_ID IN NUMBER,
260 X_OBJECT_VERSION_NUMBER IN NUMBER,
261 X_SAT_CODE IN VARCHAR2,
262 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
263 X_ATTRIBUTE1 IN VARCHAR2,
264 X_ATTRIBUTE2 IN VARCHAR2,
265 X_ATTRIBUTE3 IN VARCHAR2,
266 X_ATTRIBUTE4 IN VARCHAR2,
267 X_ATTRIBUTE5 IN VARCHAR2,
268 X_ATTRIBUTE6 IN VARCHAR2,
269 X_ATTRIBUTE7 IN VARCHAR2,
270 X_ATTRIBUTE8 IN VARCHAR2,
271 X_ATTRIBUTE9 IN VARCHAR2,
272 X_ATTRIBUTE10 IN VARCHAR2,
273 X_ATTRIBUTE11 IN VARCHAR2,
274 X_ATTRIBUTE12 IN VARCHAR2,
275 X_ATTRIBUTE13 IN VARCHAR2,
276 X_ATTRIBUTE14 IN VARCHAR2,
277 X_ATTRIBUTE15 IN VARCHAR2,
278 X_FOLDER_NAME IN VARCHAR2,
279 X_DESCRIPTION IN VARCHAR2,
280 X_ORG_ID IN NUMBER,
281 X_LAST_UPDATE_DATE IN DATE,
282 X_LAST_UPDATED_BY IN NUMBER,
283 X_LAST_UPDATE_LOGIN IN NUMBER
284 ) IS
285 BEGIN
286 UPDATE OKC_FOLDERS_ALL_B SET
287 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
288 SAT_CODE = X_SAT_CODE,
289 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
290 ATTRIBUTE1 = X_ATTRIBUTE1,
291 ATTRIBUTE2 = X_ATTRIBUTE2,
292 ATTRIBUTE3 = X_ATTRIBUTE3,
293 ATTRIBUTE4 = X_ATTRIBUTE4,
294 ATTRIBUTE5 = X_ATTRIBUTE5,
295 ATTRIBUTE6 = X_ATTRIBUTE6,
296 ATTRIBUTE7 = X_ATTRIBUTE7,
297 ATTRIBUTE8 = X_ATTRIBUTE8,
298 ATTRIBUTE9 = X_ATTRIBUTE9,
299 ATTRIBUTE10 = X_ATTRIBUTE10,
300 ATTRIBUTE11 = X_ATTRIBUTE11,
301 ATTRIBUTE12 = X_ATTRIBUTE12,
302 ATTRIBUTE13 = X_ATTRIBUTE13,
303 ATTRIBUTE14 = X_ATTRIBUTE14,
304 ATTRIBUTE15 = X_ATTRIBUTE15,
305 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
306 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
307 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
308 WHERE FOLDER_ID = X_FOLDER_ID;
309
310 IF (SQL%NOTFOUND) THEN
311 RAISE NO_DATA_FOUND;
312 END IF;
313
314 UPDATE OKC_FOLDERS_ALL_TL SET
315 FOLDER_NAME = X_FOLDER_NAME,
316 DESCRIPTION = X_DESCRIPTION,
317 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
318 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
319 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
320 SOURCE_LANG = USERENV('LANG')
321 WHERE FOLDER_ID = X_FOLDER_ID
322 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
323
324 IF (SQL%NOTFOUND) THEN
325 RAISE NO_DATA_FOUND;
326 END IF;
327 END UPDATE_ROW;
328
329 PROCEDURE DELETE_ROW (
330 X_FOLDER_ID IN NUMBER
331 ) IS
332 BEGIN
333 DELETE FROM OKC_FOLDERS_ALL_TL
334 WHERE FOLDER_ID = X_FOLDER_ID;
335
336 IF (SQL%NOTFOUND) THEN
337 RAISE NO_DATA_FOUND;
338 END IF;
339
340 DELETE FROM OKC_FOLDERS_ALL_B
341 WHERE FOLDER_ID = X_FOLDER_ID;
342
343 IF (SQL%NOTFOUND) THEN
344 RAISE NO_DATA_FOUND;
345 END IF;
346 END DELETE_ROW;
347
348 PROCEDURE ADD_LANGUAGE
349 IS
350 BEGIN
351 DELETE FROM OKC_FOLDERS_ALL_TL T
352 WHERE NOT EXISTS
353 (SELECT NULL
354 FROM OKC_FOLDERS_ALL_B B
355 WHERE B.FOLDER_ID = T.FOLDER_ID
356 );
357
358 UPDATE OKC_FOLDERS_ALL_TL T SET (
359 FOLDER_NAME,
360 DESCRIPTION
361 ) = (SELECT
362 B.FOLDER_NAME,
363 B.DESCRIPTION
364 FROM OKC_FOLDERS_ALL_TL B
365 WHERE B.FOLDER_ID = T.FOLDER_ID
366 AND B.LANGUAGE = T.SOURCE_LANG)
367 WHERE (
368 T.FOLDER_ID,
369 T.LANGUAGE
370 ) IN (SELECT
371 SUBT.FOLDER_ID,
372 SUBT.LANGUAGE
373 FROM OKC_FOLDERS_ALL_TL SUBB, OKC_FOLDERS_ALL_TL SUBT
374 WHERE SUBB.FOLDER_ID = SUBT.FOLDER_ID
375 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
376 AND (SUBB.FOLDER_NAME <> SUBT.FOLDER_NAME
377 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
378 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
379 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
380 ));
381
382 INSERT INTO OKC_FOLDERS_ALL_TL (
383 FOLDER_NAME,
384 DESCRIPTION,
385 CREATED_BY,
386 CREATION_DATE,
387 LAST_UPDATE_DATE,
388 LAST_UPDATED_BY,
389 LAST_UPDATE_LOGIN,
390 FOLDER_ID,
391 LANGUAGE,
392 SOURCE_LANG
393 ) SELECT /*+ ORDERED */
394 B.FOLDER_NAME,
395 B.DESCRIPTION,
396 B.CREATED_BY,
397 B.CREATION_DATE,
398 B.LAST_UPDATE_DATE,
399 B.LAST_UPDATED_BY,
400 B.LAST_UPDATE_LOGIN,
401 B.FOLDER_ID,
402 L.LANGUAGE_CODE,
403 B.SOURCE_LANG
404 FROM OKC_FOLDERS_ALL_TL B, FND_LANGUAGES L
405 WHERE L.INSTALLED_FLAG IN ('I', 'B')
406 AND B.LANGUAGE = USERENV('LANG')
407 AND NOT EXISTS
408 (SELECT NULL
409 FROM OKC_FOLDERS_ALL_TL T
410 WHERE T.FOLDER_ID = B.FOLDER_ID
411 AND T.LANGUAGE = L.LANGUAGE_CODE);
412 END ADD_LANGUAGE;
413
414 END OKC_FOLDERS_PVT;
[Home] [Help]