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