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