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