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