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