[Home] [Help]
PACKAGE BODY: APPS.CSI_CTR_PROPERTY_TEMPLATE_PKG
Source
1 PACKAGE BODY CSI_CTR_PROPERTY_TEMPLATE_PKG AS
2 /* $Header: csitcptb.pls 120.2.12010000.2 2008/10/31 21:21:44 rsinn ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSI_CTR_PROPERTY_TEMPLATE_PKG';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csitcptb.pls';
6
7 PROCEDURE Insert_Row(
8 px_COUNTER_PROPERTY_ID IN OUT NOCOPY NUMBER
9 ,p_COUNTER_ID NUMBER
10 ,p_PROPERTY_DATA_TYPE VARCHAR2
11 ,p_IS_NULLABLE VARCHAR2
12 ,p_DEFAULT_VALUE VARCHAR2
13 ,p_MINIMUM_VALUE VARCHAR2
14 ,p_MAXIMUM_VALUE VARCHAR2
15 ,p_UOM_CODE VARCHAR2
16 ,p_START_DATE_ACTIVE DATE
17 ,p_END_DATE_ACTIVE DATE
18 ,p_OBJECT_VERSION_NUMBER NUMBER
19 ,p_LAST_UPDATE_DATE DATE
20 ,p_LAST_UPDATED_BY NUMBER
21 ,p_CREATION_DATE DATE
22 ,p_CREATED_BY NUMBER
23 ,p_LAST_UPDATE_LOGIN NUMBER
24 ,p_ATTRIBUTE1 VARCHAR2
25 ,p_ATTRIBUTE2 VARCHAR2
26 ,p_ATTRIBUTE3 VARCHAR2
27 ,p_ATTRIBUTE4 VARCHAR2
28 ,p_ATTRIBUTE5 VARCHAR2
29 ,p_ATTRIBUTE6 VARCHAR2
30 ,p_ATTRIBUTE7 VARCHAR2
31 ,p_ATTRIBUTE8 VARCHAR2
32 ,p_ATTRIBUTE9 VARCHAR2
33 ,p_ATTRIBUTE10 VARCHAR2
34 ,p_ATTRIBUTE11 VARCHAR2
35 ,p_ATTRIBUTE12 VARCHAR2
36 ,p_ATTRIBUTE13 VARCHAR2
37 ,p_ATTRIBUTE14 VARCHAR2
38 ,p_ATTRIBUTE15 VARCHAR2
39 ,p_ATTRIBUTE_CATEGORY VARCHAR2
40 ,p_MIGRATED_FLAG VARCHAR2
41 ,p_PROPERTY_LOV_TYPE VARCHAR2
42 ,p_SECURITY_GROUP_ID NUMBER
43 ,p_NAME VARCHAR2
44 ,p_DESCRIPTION VARCHAR2
45 ) IS
46
47 CURSOR C1 IS
48 SELECT CSI_COUNTER_PROPERTIES_B_S.nextval
49 FROM dual;
50 BEGIN
51 IF (px_COUNTER_PROPERTY_ID IS NULL) OR (px_COUNTER_PROPERTY_ID = FND_API.G_MISS_NUM) then
52 OPEN C1;
53 FETCH C1 INTO px_COUNTER_PROPERTY_ID;
54 CLOSE C1;
55 END IF;
56
57 INSERT INTO CSI_CTR_PROPERTY_TEMPLATE_B(
58 COUNTER_PROPERTY_ID
59 ,COUNTER_ID
60 ,PROPERTY_DATA_TYPE
61 ,IS_NULLABLE
62 ,DEFAULT_VALUE
63 ,MINIMUM_VALUE
64 ,MAXIMUM_VALUE
65 ,UOM_CODE
66 ,START_DATE_ACTIVE
67 ,END_DATE_ACTIVE
68 ,OBJECT_VERSION_NUMBER
69 ,LAST_UPDATE_DATE
70 ,LAST_UPDATED_BY
71 ,CREATION_DATE
72 ,CREATED_BY
73 ,LAST_UPDATE_LOGIN
74 ,ATTRIBUTE1
75 ,ATTRIBUTE2
76 ,ATTRIBUTE3
77 ,ATTRIBUTE4
78 ,ATTRIBUTE5
79 ,ATTRIBUTE6
80 ,ATTRIBUTE7
81 ,ATTRIBUTE8
82 ,ATTRIBUTE9
83 ,ATTRIBUTE10
84 ,ATTRIBUTE11
85 ,ATTRIBUTE12
86 ,ATTRIBUTE13
87 ,ATTRIBUTE14
88 ,ATTRIBUTE15
89 ,ATTRIBUTE_CATEGORY
90 ,MIGRATED_FLAG
91 ,PROPERTY_LOV_TYPE
92 ,SECURITY_GROUP_ID
93 )
94 VALUES(
95 px_COUNTER_PROPERTY_ID
96 ,decode(p_COUNTER_ID, FND_API.G_MISS_NUM, NULL,p_COUNTER_ID)
97 ,decode(p_PROPERTY_DATA_TYPE, FND_API.G_MISS_CHAR, NULL, p_PROPERTY_DATA_TYPE)
98 ,decode(p_IS_NULLABLE, FND_API.G_MISS_CHAR, NULL, p_IS_NULLABLE)
99 ,decode(p_DEFAULT_VALUE, FND_API.G_MISS_CHAR, NULL, p_DEFAULT_VALUE)
100 ,decode(p_MINIMUM_VALUE, FND_API.G_MISS_CHAR, NULL, p_MINIMUM_VALUE)
101 ,decode(p_MAXIMUM_VALUE, FND_API.G_MISS_CHAR, NULL, p_MAXIMUM_VALUE)
102 ,decode(p_UOM_CODE, FND_API.G_MISS_CHAR, NULL, p_UOM_CODE)
103 ,decode(p_START_DATE_ACTIVE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_START_DATE_ACTIVE)
104 ,decode(p_END_DATE_ACTIVE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_END_DATE_ACTIVE)
105 ,decode(p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL,p_OBJECT_VERSION_NUMBER)
106 ,decode(p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE)
107 ,decode(p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
108 ,decode(p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE)
109 ,decode(p_CREATED_BY, FND_API.G_MISS_NUM, NULL,p_CREATED_BY)
110 ,decode(p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_LAST_UPDATE_LOGIN)
111 ,decode(p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1)
112 ,decode(p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2)
113 ,decode(p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3)
114 ,decode(p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4)
115 ,decode(p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5)
116 ,decode(p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6)
117 ,decode(p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7)
118 ,decode(p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8)
119 ,decode(p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9)
120 ,decode(p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10)
121 ,decode(p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11)
122 ,decode(p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12)
123 ,decode(p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13)
124 ,decode(p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14)
125 ,decode(p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15)
126 ,decode(p_ATTRIBUTE_CATEGORY , FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_CATEGORY)
127 ,decode(p_MIGRATED_FLAG, FND_API.G_MISS_CHAR, NULL, p_MIGRATED_FLAG)
128 ,decode(p_PROPERTY_LOV_TYPE, FND_API.G_MISS_CHAR, NULL, p_PROPERTY_LOV_TYPE)
129 ,decode(p_SECURITY_GROUP_ID, FND_API.G_MISS_NUM, NULL,p_SECURITY_GROUP_ID)
130 );
131
132 INSERT INTO CSI_CTR_PROPERTY_TEMPLATE_TL(
133 COUNTER_PROPERTY_ID
134 ,NAME
135 ,DESCRIPTION
136 ,LANGUAGE
137 ,SOURCE_LANG
138 ,CREATED_BY
139 ,CREATION_DATE
140 ,LAST_UPDATED_BY
141 ,LAST_UPDATE_DATE
142 ,LAST_UPDATE_LOGIN
143 ,MIGRATED_FLAG
144 )
145 SELECT px_counter_property_id
146 ,decode(p_name, fnd_api.g_miss_char, NULL, p_name)
147 ,decode(p_description, fnd_api.g_miss_char, NULL, p_description)
148 ,L.language_code
149 ,userenv('LANG')
150 ,decode(p_created_by, fnd_api.g_miss_num, NULL, p_created_by)
151 ,decode(p_creation_date, fnd_api.g_miss_date, to_date(NULL), p_creation_date)
152 ,decode(p_last_updated_by, fnd_api.g_miss_num, NULL, p_last_updated_by)
153 ,decode(p_last_update_date, fnd_api.g_miss_date, to_date(NULL), p_last_update_date)
154 ,decode(p_last_update_login, fnd_api.g_miss_num, NULL, p_last_update_login)
155 ,decode(p_migrated_flag, fnd_api.g_miss_char, NULL, p_migrated_flag)
156 FROM fnd_languages L
157 WHERE L.installed_flag IN ('I','B')
158 AND NOT EXISTS (SELECT 'x'
159 FROM csi_ctr_property_template_tl cct
160 WHERE cct.counter_property_id = px_counter_property_id
161 AND cct.language = L.language_code);
162 End Insert_Row;
163
164 PROCEDURE Update_Row(
165 p_COUNTER_PROPERTY_ID NUMBER
166 ,p_COUNTER_ID NUMBER
167 ,p_PROPERTY_DATA_TYPE VARCHAR2
168 ,p_IS_NULLABLE VARCHAR2
169 ,p_DEFAULT_VALUE VARCHAR2
170 ,p_MINIMUM_VALUE VARCHAR2
171 ,p_MAXIMUM_VALUE VARCHAR2
172 ,p_UOM_CODE VARCHAR2
173 ,p_START_DATE_ACTIVE DATE
174 ,p_END_DATE_ACTIVE DATE
175 ,p_OBJECT_VERSION_NUMBER NUMBER
176 ,p_LAST_UPDATE_DATE DATE
177 ,p_LAST_UPDATED_BY NUMBER
178 ,p_CREATION_DATE DATE
179 ,p_CREATED_BY NUMBER
180 ,p_LAST_UPDATE_LOGIN NUMBER
181 ,p_ATTRIBUTE1 VARCHAR2
182 ,p_ATTRIBUTE2 VARCHAR2
183 ,p_ATTRIBUTE3 VARCHAR2
184 ,p_ATTRIBUTE4 VARCHAR2
185 ,p_ATTRIBUTE5 VARCHAR2
186 ,p_ATTRIBUTE6 VARCHAR2
187 ,p_ATTRIBUTE7 VARCHAR2
188 ,p_ATTRIBUTE8 VARCHAR2
189 ,p_ATTRIBUTE9 VARCHAR2
190 ,p_ATTRIBUTE10 VARCHAR2
191 ,p_ATTRIBUTE11 VARCHAR2
192 ,p_ATTRIBUTE12 VARCHAR2
193 ,p_ATTRIBUTE13 VARCHAR2
194 ,p_ATTRIBUTE14 VARCHAR2
195 ,p_ATTRIBUTE15 VARCHAR2
196 ,p_ATTRIBUTE_CATEGORY VARCHAR2
197 ,p_MIGRATED_FLAG VARCHAR2
198 ,p_PROPERTY_LOV_TYPE VARCHAR2
199 ,p_SECURITY_GROUP_ID NUMBER
200 ,p_NAME VARCHAR2
201 ,p_DESCRIPTION VARCHAR2) IS
202 BEGIN
203 -- Read the debug profiles values in to global variable 7197402
204 CSI_CTR_GEN_UTILITY_PVT.read_debug_profiles;
205 csi_ctr_gen_utility_pvt.put_line(' Default Value = '||p_default_value);
206
207 UPDATE CSI_CTR_PROPERTY_TEMPLATE_B
208 SET COUNTER_ID = decode(p_COUNTER_ID, NULL, COUNTER_ID, FND_API.G_MISS_NUM, NULL, p_COUNTER_ID)
209 ,PROPERTY_DATA_TYPE = decode(p_PROPERTY_DATA_TYPE, NULL, PROPERTY_DATA_TYPE, FND_API.G_MISS_CHAR, NULL, p_PROPERTY_DATA_TYPE)
210 ,IS_NULLABLE = decode(p_IS_NULLABLE, NULL, IS_NULLABLE, FND_API.G_MISS_CHAR, NULL, p_IS_NULLABLE)
211 ,DEFAULT_VALUE = decode(p_DEFAULT_VALUE, NULL, DEFAULT_VALUE, FND_API.G_MISS_CHAR, NULL, p_DEFAULT_VALUE)
212 ,MINIMUM_VALUE = decode(p_MINIMUM_VALUE, NULL, MINIMUM_VALUE, FND_API.G_MISS_CHAR, NULL, p_MINIMUM_VALUE)
213 ,MAXIMUM_VALUE = decode(p_MAXIMUM_VALUE, NULL, MAXIMUM_VALUE, FND_API.G_MISS_CHAR, NULL, p_MAXIMUM_VALUE)
214 ,UOM_CODE = decode(p_UOM_CODE, NULL, UOM_CODE, FND_API.G_MISS_CHAR, NULL, p_UOM_CODE)
215 ,START_DATE_ACTIVE = decode(p_START_DATE_ACTIVE, NULL, START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, p_START_DATE_ACTIVE)
216 ,END_DATE_ACTIVE = decode(p_END_DATE_ACTIVE, NULL, END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_END_DATE_ACTIVE)
217 ,OBJECT_VERSION_NUMBER = decode(p_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER)
218 ,LAST_UPDATE_DATE = decode(p_LAST_UPDATE_DATE, NULL, LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL,p_LAST_UPDATE_DATE)
219 ,LAST_UPDATED_BY = decode(p_LAST_UPDATED_BY, NULL,LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_LAST_UPDATED_BY)
220 ,CREATION_DATE = decode(p_CREATION_DATE, NULL, CREATION_DATE, FND_API.G_MISS_DATE, NULL, p_CREATION_DATE)
221 ,CREATED_BY = decode(p_CREATED_BY, NULL, CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
222 ,LAST_UPDATE_LOGIN = decode(p_LAST_UPDATE_LOGIN, NULL,LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_LAST_UPDATE_LOGIN)
223 ,ATTRIBUTE1 = decode(p_ATTRIBUTE1, NULL, ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1)
224 ,ATTRIBUTE2 = decode(p_ATTRIBUTE2, NULL, ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2)
225 ,ATTRIBUTE3 = decode(p_ATTRIBUTE3, NULL, ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3)
226 ,ATTRIBUTE4 = decode(p_ATTRIBUTE4, NULL, ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4)
227 ,ATTRIBUTE5 = decode(p_ATTRIBUTE5, NULL, ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5)
228 ,ATTRIBUTE6 = decode(p_ATTRIBUTE6, NULL, ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6)
229 ,ATTRIBUTE7 = decode(p_ATTRIBUTE7, NULL, ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7)
230 ,ATTRIBUTE8 = decode(p_ATTRIBUTE8, NULL, ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8)
231 ,ATTRIBUTE9 = decode(p_ATTRIBUTE9, NULL, ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9)
232 ,ATTRIBUTE10 = decode(p_ATTRIBUTE10, NULL, ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10)
233 ,ATTRIBUTE11 = decode(p_ATTRIBUTE11, NULL, ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11)
234 ,ATTRIBUTE12 = decode(p_ATTRIBUTE12, NULL, ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12)
235 ,ATTRIBUTE13 = decode(p_ATTRIBUTE13, NULL, ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13)
236 ,ATTRIBUTE14 = decode(p_ATTRIBUTE14, NULL, ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14)
237 ,ATTRIBUTE15 = decode(p_ATTRIBUTE15, NULL, ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15)
238 ,ATTRIBUTE_CATEGORY = decode(p_ATTRIBUTE_CATEGORY, NULL, ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_CATEGORY)
239 ,MIGRATED_FLAG = decode(p_MIGRATED_FLAG, NULL, MIGRATED_FLAG, FND_API.G_MISS_CHAR,NULL, p_MIGRATED_FLAG)
240 ,PROPERTY_LOV_TYPE = decode(p_PROPERTY_LOV_TYPE, NULL, PROPERTY_LOV_TYPE, FND_API.G_MISS_CHAR, NULL, p_PROPERTY_LOV_TYPE)
241 ,SECURITY_GROUP_ID = decode(p_SECURITY_GROUP_ID, NULL, SECURITY_GROUP_ID, FND_API.G_MISS_NUM, NULL, p_SECURITY_GROUP_ID)
242 WHERE COUNTER_PROPERTY_ID = p_COUNTER_PROPERTY_ID;
243
244 UPDATE CSI_CTR_PROPERTY_TEMPLATE_TL
245 SET source_lang = userenv('LANG'),
246 name = decode(p_name, NULL, name, fnd_api.g_miss_char, NULL, p_name),
247 description = decode(p_description, NULL, description, fnd_api.g_miss_char, NULL, p_description),
248 created_by = decode(p_created_by, NULL, created_by, fnd_api.g_miss_num, NULL, p_created_by),
249 creation_date = decode(p_creation_date, NULL, creation_date, fnd_api.g_miss_date, NULL, p_creation_date),
250 last_updated_by = decode(p_last_updated_by, NULL, last_updated_by, fnd_api.g_miss_num, NULL,p_last_updated_by),
251 last_update_date = decode(p_last_update_date,NULL,last_update_date,fnd_api.g_miss_date,NULL,p_last_update_date),
252 -- last_update_login = decode(p_last_update_login,last_update_login, fnd_api.g_miss_num,NULL, p_last_update_login),
253 migrated_flag = decode(p_migrated_flag, NULL, migrated_flag, fnd_api.g_miss_char,NULL, p_migrated_flag)
254 WHERE counter_property_id = p_counter_property_id
255 AND userenv('LANG') IN (LANGUAGE,SOURCE_LANG);
256
257 If (SQL%NOTFOUND) then
258 RAISE NO_DATA_FOUND;
259 End If;
260
261 END Update_Row;
262
263 PROCEDURE delete_row(p_COUNTER_PROPERTY_ID NUMBER) IS
264 BEGIN
265 DELETE FROM CSI_CTR_PROPERTY_TEMPLATE_B
266 WHERE COUNTER_PROPERTY_ID = p_COUNTER_PROPERTY_ID;
267 IF (SQL%NOTFOUND) then
268 RAISE NO_DATA_FOUND;
269 END IF;
270 END delete_row;
271
272 PROCEDURE Lock_Row(
273 p_COUNTER_PROPERTY_ID NUMBER
274 ,p_COUNTER_ID NUMBER
275 ,p_PROPERTY_DATA_TYPE VARCHAR2
276 ,p_IS_NULLABLE VARCHAR2
277 ,p_DEFAULT_VALUE VARCHAR2
278 ,p_MINIMUM_VALUE VARCHAR2
279 ,p_MAXIMUM_VALUE VARCHAR2
280 ,p_UOM_CODE VARCHAR2
281 ,p_START_DATE_ACTIVE DATE
282 ,p_END_DATE_ACTIVE DATE
283 ,p_OBJECT_VERSION_NUMBER NUMBER
284 ,p_LAST_UPDATE_DATE DATE
285 ,p_LAST_UPDATED_BY NUMBER
286 ,p_CREATION_DATE DATE
287 ,p_CREATED_BY NUMBER
288 ,p_LAST_UPDATE_LOGIN NUMBER
289 ,p_ATTRIBUTE1 VARCHAR2
290 ,p_ATTRIBUTE2 VARCHAR2
291 ,p_ATTRIBUTE3 VARCHAR2
292 ,p_ATTRIBUTE4 VARCHAR2
293 ,p_ATTRIBUTE5 VARCHAR2
294 ,p_ATTRIBUTE6 VARCHAR2
295 ,p_ATTRIBUTE7 VARCHAR2
296 ,p_ATTRIBUTE8 VARCHAR2
297 ,p_ATTRIBUTE9 VARCHAR2
298 ,p_ATTRIBUTE10 VARCHAR2
299 ,p_ATTRIBUTE11 VARCHAR2
300 ,p_ATTRIBUTE12 VARCHAR2
301 ,p_ATTRIBUTE13 VARCHAR2
302 ,p_ATTRIBUTE14 VARCHAR2
303 ,p_ATTRIBUTE15 VARCHAR2
304 ,p_ATTRIBUTE_CATEGORY VARCHAR2
305 ,p_MIGRATED_FLAG VARCHAR2
306 ,p_PROPERTY_LOV_TYPE VARCHAR2
307 ,p_SECURITY_GROUP_ID NUMBER
308 ,p_NAME VARCHAR2
309 ,p_DESCRIPTION VARCHAR2) IS
310
311 CURSOR C1 IS
312 SELECT *
313 FROM CSI_CTR_PROPERTY_TEMPLATE_B
314 WHERE COUNTER_PROPERTY_ID = p_COUNTER_PROPERTY_ID
315 FOR UPDATE of COUNTER_PROPERTY_ID NOWAIT;
316 Recinfo C1%ROWTYPE;
317
318 CURSOR c2 IS
319 SELECT name,
320 description,
321 decode(language, userenv('LANG'), 'Y', 'N') baselang
322 FROM CSI_CTR_PROPERTY_TEMPLATE_TL
323 WHERE counter_property_id = p_counter_property_id
324 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG)
325 FOR UPDATE OF counter_property_id NOWAIT;
326 BEGIN
327 OPEN c1;
328 FETCH c1 INTO recinfo;
329 IF (c1%notfound) THEN
330 CLOSE c1;
331 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
332 app_exception.raise_exception;
333 END IF;
334 CLOSE c1;
335
336
337 IF (recinfo.object_version_number=p_object_version_number)
338 THEN
339 RETURN;
340 ELSE
341 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
342 app_exception.raise_exception;
343 END IF;
344
345 FOR tlinfo IN c2 LOOP
346 IF (tlinfo.baselang = 'Y') THEN
347 IF ( (tlinfo.name = p_name)
348 AND ((tlinfo.description = p_description)
349 OR ((tlinfo.description IS NULL) AND (p_description IS NULL)))
350 ) THEN
351 NULL;
352 ELSE
353 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
354 app_exception.raise_exception;
355 END IF;
356 END IF;
357 END LOOP;
358 RETURN;
359
360 END Lock_Row;
361
362 PROCEDURE add_language IS
363 BEGIN
364 DELETE FROM CSI_CTR_PROPERTY_TEMPLATE_TL t
365 WHERE NOT EXISTS (SELECT NULL
366 FROM CSI_CTR_PROPERTY_TEMPLATE_B b
367 WHERE b.counter_property_id = t.counter_property_id);
368
369 UPDATE CSI_CTR_PROPERTY_TEMPLATE_TL t
370 SET (name,description) = (SELECT b.name,
371 b.description
372 FROM CSI_CTR_PROPERTY_TEMPLATE_TL b
373 WHERE b.counter_property_id = t.counter_property_id
374 AND b.language = t.source_lang)
375 WHERE (t.counter_property_id,t.language) IN (SELECT subt.counter_property_id,
376 subt.language
377 FROM CSI_CTR_PROPERTY_TEMPLATE_TL subb, CSI_CTR_PROPERTY_TEMPLATE_TL subt
378 WHERE subb.counter_property_id = subt.counter_property_id
379 AND subb.language = subt.source_lang
380 AND (subb.name <> subt.name
381 OR subb.description <> subt.description
382 OR (subb.description IS NULL AND subt.description IS NOT NULL)
383 OR (subb.description iS NOT NULL AND subt.description IS NULL)
384 )
385 );
386
387 INSERT INTO CSI_CTR_PROPERTY_TEMPLATE_TL(
388 counter_property_id,
389 name,
390 description,
391 last_update_date,
392 last_updated_by,
393 creation_date,
394 created_by,
395 last_update_login,
396 language,
397 source_lang
398 )
399 SELECT b.counter_property_id,
400 b.name,
401 b.description,
402 b.last_update_date,
403 b.last_updated_by,
404 b.creation_date,
405 b.created_by,
406 b.last_update_login,
407 l.language_code,
408 b.source_lang
409 FROM CSI_CTR_PROPERTY_TEMPLATE_TL b, fnd_languages l
410 WHERE l.installed_flag in ('I', 'B')
411 AND b.language = userenv('LANG')
412 AND NOT EXISTS (SELECT NULL
413 FROM CSI_CTR_PROPERTY_TEMPLATE_TL t
414 WHERE t.counter_property_id = b.counter_property_id
415 AND t.language = l.language_code);
416 END add_language;
417
418 PROCEDURE translate_row(
419 p_counter_property_id IN NUMBER
420 ,p_name IN VARCHAR2
421 ,p_description IN VARCHAR2
422 ,p_owner IN VARCHAR2) IS
423 BEGIN
424 UPDATE CSI_CTR_PROPERTY_TEMPLATE_TL
425 SET name = p_name,
426 description = p_description,
427 last_update_date = sysdate,
428 last_updated_by = decode(p_owner, 'SEED', 1, 0),
429 last_update_login = 0,
430 source_lang = userenv('LANG')
431 WHERE counter_property_id = p_counter_property_id
432 AND userenv('LANG') IN (language, source_lang);
433 END translate_row;
434
435 End CSI_CTR_PROPERTY_TEMPLATE_PKG;