[Home] [Help]
PACKAGE BODY: APPS.HZ_WORD_REPLACEMENTS1_PKG
Source
1 PACKAGE BODY HZ_WORD_REPLACEMENTS1_PKG as
2 /*$Header: ARHDQWRB.pls 120.12 2006/06/29 13:45:06 rarajend noship $ */
3
4 PROCEDURE Insert_Row(
5 x_Rowid IN OUT NOCOPY VARCHAR2,
6 x_WORD_LIST_ID NUMBER,
7 x_ORIGINAL_WORD VARCHAR2,
8 x_REPLACEMENT_WORD VARCHAR2,
9 x_TYPE VARCHAR2,
10 x_COUNTRY_CODE VARCHAR2,
11 x_LAST_UPDATE_DATE DATE,
12 x_LAST_UPDATED_BY NUMBER,
13 x_CREATION_DATE DATE,
14 x_CREATED_BY NUMBER,
15 x_LAST_UPDATE_LOGIN NUMBER,
16 x_ATTRIBUTE_CATEGORY VARCHAR2,
17 x_ATTRIBUTE1 VARCHAR2,
18 x_ATTRIBUTE2 VARCHAR2,
19 x_ATTRIBUTE3 VARCHAR2,
20 x_ATTRIBUTE4 VARCHAR2,
21 x_ATTRIBUTE5 VARCHAR2,
22 x_ATTRIBUTE6 VARCHAR2,
23 x_ATTRIBUTE7 VARCHAR2,
24 x_ATTRIBUTE8 VARCHAR2,
25 x_ATTRIBUTE9 VARCHAR2,
26 x_ATTRIBUTE10 VARCHAR2,
27 x_ATTRIBUTE11 VARCHAR2,
28 x_ATTRIBUTE12 VARCHAR2,
29 x_ATTRIBUTE13 VARCHAR2,
30 x_ATTRIBUTE14 VARCHAR2,
31 x_ATTRIBUTE15 VARCHAR2,
32 x_object_version_number IN OUT NOCOPY NUMBER,
33 x_msg_count IN OUT NOCOPY NUMBER,
34 x_condition_id NUMBER DEFAULT NULL,
35 x_user_spec_cond_value VARCHAR2 DEFAULT NULL
36 ) IS
37 CURSOR C IS SELECT rowid FROM HZ_WORD_REPLACEMENTS
38 WHERE WORD_LIST_ID = x_WORD_LIST_ID
39 AND ORIGINAL_WORD = UPPER(x_ORIGINAL_WORD);
40 OAERR EXCEPTION;
41 l_count NUMBER;
42 p_msg_count number := 0;
43 p_msg_data varchar2(1000);
44 BEGIN
45 IF x_WORD_LIST_ID IS NOT NULL THEN
46 x_msg_count := 0;
47 /* Original Word is unique for a word list */
48 SELECT count(*) INTO l_count
49 FROM hz_word_replacements
50 WHERE word_list_id = x_WORD_LIST_ID
51 AND delete_flag = 'N'
52 AND original_word = UPPER(x_ORIGINAL_WORD)
53 AND (condition_id IS NULL
54 OR
55 (decode(x_CONDITION_ID, FND_API.G_MISS_NUM, NULL, x_CONDITION_ID) IS NULL)
56 OR
57 ( (condition_id = x_condition_id)
58 AND ( nvl(user_spec_cond_value,FND_API.G_MISS_CHAR) = nvl(x_user_spec_cond_value,FND_API.G_MISS_CHAR )
59 OR nvl(replacement_word,FND_API.G_MISS_CHAR) = nvl(upper(x_replacement_word),FND_API.G_MISS_CHAR)
60 )
61 )
62 );
63
64 IF l_count > 0 THEN
65 x_msg_count := x_msg_count +1;
66 l_count := 0;
67 FND_MESSAGE.SET_NAME( 'AR', 'HZ_DUPL_WORD' );
68 FND_MESSAGE.SET_TOKEN( 'WORD', UPPER(x_ORIGINAL_WORD) );
69 FND_MSG_PUB.ADD;
70 END IF;
71 /* No Cyclic Pairs */
72 SELECT count(*) INTO l_count
73 FROM hz_word_replacements
74 WHERE word_list_id = x_WORD_LIST_ID
75 AND delete_flag = 'N'
76 AND (original_word = UPPER(x_REPLACEMENT_WORD) OR
77 replacement_word = UPPER(x_ORIGINAL_WORD));
78 IF l_count > 0 THEN
79 x_msg_count := x_msg_count+1;
80 l_count := 0;
81 FND_MESSAGE.SET_NAME( 'AR', 'HZ_DQM_WR_CYCLIC' );
82 FND_MESSAGE.SET_TOKEN( 'ORIGINALWORD', UPPER(x_ORIGINAL_WORD) );
83 FND_MESSAGE.SET_TOKEN( 'REPLACEMENTWORD', UPPER(x_REPLACEMENT_WORD) );
84 FND_MSG_PUB.ADD;
85 END IF;
86 /* Pairs cannot be same */
87 IF UPPER(x_ORIGINAL_WORD) = UPPER(x_REPLACEMENT_WORD) THEN
88 x_msg_count := x_msg_count+1;
89 FND_MESSAGE.SET_NAME( 'AR', 'HZ_DQM_WR_SAME_PAIR_VALUE' );
90 FND_MESSAGE.SET_TOKEN( 'WORD', UPPER(x_REPLACEMENT_WORD) );
91 FND_MSG_PUB.ADD;
92 END IF;
93 /* Original Word is Mandatory */
94 IF (x_ORIGINAL_WORD IS NULL OR x_ORIGINAL_WORD = FND_API.G_MISS_CHAR)
95 THEN
96 x_msg_count := x_msg_count+1;
97 FND_MESSAGE.SET_NAME( 'AR', 'HZ_DQM_WR_ORIG_WORD_MANDATORY' );
98 FND_MSG_PUB.ADD;
99 END IF;
100
101 IF x_msg_count > 0 THEN
102 return;
103 END IF;
104 END IF;
105
106 Insert_Row(
107 x_Rowid ,
108 x_WORD_LIST_ID ,
109 x_ORIGINAL_WORD ,
110 x_REPLACEMENT_WORD ,
111 x_TYPE ,
112 x_COUNTRY_CODE ,
113 x_LAST_UPDATE_DATE ,
114 x_LAST_UPDATED_BY ,
115 x_CREATION_DATE ,
116 x_CREATED_BY ,
117 x_LAST_UPDATE_LOGIN ,
118 x_ATTRIBUTE_CATEGORY,
119 x_ATTRIBUTE1 ,
120 x_ATTRIBUTE2 ,
121 x_ATTRIBUTE3 ,
122 x_ATTRIBUTE4 ,
123 x_ATTRIBUTE5 ,
124 x_ATTRIBUTE6 ,
125 x_ATTRIBUTE7 ,
126 x_ATTRIBUTE8 ,
127 x_ATTRIBUTE9 ,
128 x_ATTRIBUTE10 ,
129 x_ATTRIBUTE11 ,
130 x_ATTRIBUTE12 ,
131 x_ATTRIBUTE13 ,
132 x_ATTRIBUTE14 ,
133 x_ATTRIBUTE15 ,
134 x_object_version_number,
135 x_condition_id ,
136 x_user_spec_cond_value
137 );
138
139 END Insert_Row;
140
141 PROCEDURE Insert_Row(
142 x_Rowid IN OUT NOCOPY VARCHAR2,
143 x_WORD_LIST_ID NUMBER,
144 x_ORIGINAL_WORD VARCHAR2,
145 x_REPLACEMENT_WORD VARCHAR2,
146 x_TYPE VARCHAR2,
147 x_COUNTRY_CODE VARCHAR2,
148 x_LAST_UPDATE_DATE DATE,
149 x_LAST_UPDATED_BY NUMBER,
150 x_CREATION_DATE DATE,
151 x_CREATED_BY NUMBER,
152 x_LAST_UPDATE_LOGIN NUMBER,
153 x_ATTRIBUTE_CATEGORY VARCHAR2,
154 x_ATTRIBUTE1 VARCHAR2,
155 x_ATTRIBUTE2 VARCHAR2,
156 x_ATTRIBUTE3 VARCHAR2,
157 x_ATTRIBUTE4 VARCHAR2,
158 x_ATTRIBUTE5 VARCHAR2,
159 x_ATTRIBUTE6 VARCHAR2,
160 x_ATTRIBUTE7 VARCHAR2,
161 x_ATTRIBUTE8 VARCHAR2,
162 x_ATTRIBUTE9 VARCHAR2,
163 x_ATTRIBUTE10 VARCHAR2,
164 x_ATTRIBUTE11 VARCHAR2,
165 x_ATTRIBUTE12 VARCHAR2,
166 x_ATTRIBUTE13 VARCHAR2,
167 x_ATTRIBUTE14 VARCHAR2,
168 x_ATTRIBUTE15 VARCHAR2,
169 x_object_version_number IN OUT NOCOPY NUMBER,
170 x_condition_id NUMBER DEFAULT NULL,
171 x_user_spec_cond_value VARCHAR2 DEFAULT NULL
172 ) IS
173 CURSOR C IS SELECT rowid FROM HZ_WORD_REPLACEMENTS
174 WHERE WORD_LIST_ID = x_WORD_LIST_ID
175 AND ORIGINAL_WORD = UPPER(x_ORIGINAL_WORD);
176
177 BEGIN
178 INSERT INTO HZ_WORD_REPLACEMENTS(
179 WORD_LIST_ID,
180 ORIGINAL_WORD,
181 REPLACEMENT_WORD,
182 TYPE,
183 COUNTRY_CODE,
184 LAST_UPDATE_DATE,
185 LAST_UPDATED_BY,
186 CREATION_DATE,
187 CREATED_BY,
188 LAST_UPDATE_LOGIN,
189 ATTRIBUTE_CATEGORY,
190 ATTRIBUTE1,
191 ATTRIBUTE2,
192 ATTRIBUTE3,
193 ATTRIBUTE4,
194 ATTRIBUTE5,
195 ATTRIBUTE6,
196 ATTRIBUTE7,
197 ATTRIBUTE8,
198 ATTRIBUTE9,
199 ATTRIBUTE10,
200 ATTRIBUTE11,
201 ATTRIBUTE12,
202 ATTRIBUTE13,
203 ATTRIBUTE14,
204 ATTRIBUTE15,
205 OBJECT_VERSION_NUMBER,
206 CONDITION_ID,
207 USER_SPEC_COND_VALUE,
208 STAGED_FLAG,
209 DELETE_FLAG
210 )
211 VALUES (
212 decode( x_WORD_LIST_ID, FND_API.G_MISS_NUM, NULL, x_WORD_LIST_ID),
213 decode( x_ORIGINAL_WORD, FND_API.G_MISS_CHAR, NULL, UPPER(x_ORIGINAL_WORD)),
214 decode( x_REPLACEMENT_WORD, FND_API.G_MISS_CHAR, NULL, UPPER(x_REPLACEMENT_WORD)),
215 decode( x_TYPE, FND_API.G_MISS_CHAR, NULL, x_TYPE),
216 decode( x_COUNTRY_CODE, FND_API.G_MISS_CHAR, NULL, x_COUNTRY_CODE),
217 decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL,x_LAST_UPDATE_DATE),
218 decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATED_BY),
219 decode( x_CREATION_DATE, FND_API.G_MISS_DATE, NULL,x_CREATION_DATE),
220 decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL,x_CREATED_BY),
221 decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATE_LOGIN),
222 decode( x_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE_CATEGORY),
223 decode( x_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE1),
224 decode( x_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE2),
225 decode( x_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE3),
226 decode( x_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE4),
227 decode( x_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE5),
228 decode( x_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE6),
229 decode( x_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE7),
230 decode( x_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE8),
231 decode( x_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE9),
232 decode( x_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE10),
233 decode( x_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE11),
234 decode( x_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE12),
235 decode( x_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE13),
236 decode( x_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE14),
237 decode( x_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE15),
238 1,
239 decode(x_CONDITION_ID, FND_API.G_MISS_NUM, NULL, x_CONDITION_ID),
240 decode(x_USER_SPEC_COND_VALUE,FND_API.G_MISS_CHAR,null,x_USER_SPEC_COND_VALUE),
241 'N',
242 'N'
243 );
244 OPEN C;
245 FETCH C INTO x_Rowid;
246 If (C%NOTFOUND) then
247 CLOSE C;
248 RAISE NO_DATA_FOUND;
249 End If;
250
251 EXCEPTION
252 WHEN NO_DATA_FOUND THEN
253 NULL;
254
255 End Insert_Row;
256
257 PROCEDURE Delete_Row(X_WORD_LIST_ID IN NUMBER) IS
258 BEGIN
259 DELETE FROM HZ_WORD_REPLACEMENTS
260 WHERE WORD_LIST_ID = X_WORD_LIST_ID;
261 END;
262
263 PROCEDURE Delete_Row( x_WORD_LIST_ID IN NUMBER, X_ORIGINAL_WORD IN VARCHAR2,X_CONDITION_ID IN NUMBER,x_user_spec_cond_value IN VARCHAR2)
264 IS
265 CURSOR C IS SELECT STAGED_FLAG FROM HZ_WORD_REPLACEMENTS
266 WHERE WORD_LIST_ID = x_WORD_LIST_ID
267 AND ORIGINAL_WORD = x_ORIGINAL_WORD
268 AND NVL(CONDITION_ID,-99) = NVL(X_CONDITION_ID,-99)
269 AND nvl(user_spec_cond_value,FND_API.G_MISS_CHAR) = nvl(x_user_spec_cond_value,FND_API.G_MISS_CHAR)
270 AND DELETE_FLAG = 'N';
271 l_staged_flag varchar2(1);
272 BEGIN
273 OPEN C;
274 FETCH C INTO l_staged_flag;
275 /* If (C%NOTFOUND) then
276 CLOSE C;
277 RAISE NO_DATA_FOUND;
278 End If;*/
279 CLOSE C;
280 IF(l_staged_flag = 'N') then
281 DELETE FROM HZ_WORD_REPLACEMENTS
282 WHERE WORD_LIST_ID = x_WORD_LIST_ID
283 AND ORIGINAL_WORD = x_ORIGINAL_WORD
284 AND NVL(CONDITION_ID,-99) = NVL(X_CONDITION_ID,-99)
285 AND nvl(user_spec_cond_value,FND_API.G_MISS_CHAR) = nvl(x_user_spec_cond_value,FND_API.G_MISS_CHAR)
286 AND DELETE_FLAG = 'N'
287 AND STAGED_FLAG = 'N';
288 ELSE
289 UPDATE HZ_WORD_REPLACEMENTS
290 SET DELETE_FLAG = 'Y'
291 WHERE WORD_LIST_ID = x_WORD_LIST_ID
292 AND ORIGINAL_WORD = x_ORIGINAL_WORD
293 AND NVL(CONDITION_ID,-99) = NVL(X_CONDITION_ID,-99)
294 AND nvl(user_spec_cond_value,FND_API.G_MISS_CHAR) = nvl(x_user_spec_cond_value,FND_API.G_MISS_CHAR)
295 AND DELETE_FLAG = 'N'
296 AND STAGED_FLAG = 'Y' ;
297 END IF;
298 END Delete_Row;
299
300
301 PROCEDURE Update_Row(
302 x_Rowid VARCHAR2,
303 x_WORD_LIST_ID NUMBER,
304 x_ORIGINAL_WORD VARCHAR2,
305 x_REPLACEMENT_WORD VARCHAR2,
306 x_TYPE VARCHAR2,
307 x_COUNTRY_CODE VARCHAR2,
308 x_LAST_UPDATE_DATE DATE,
309 x_LAST_UPDATED_BY NUMBER,
310 x_CREATION_DATE DATE,
311 x_CREATED_BY NUMBER,
312 x_LAST_UPDATE_LOGIN NUMBER,
313 x_ATTRIBUTE_CATEGORY VARCHAR2,
314 x_ATTRIBUTE1 VARCHAR2,
315 x_ATTRIBUTE2 VARCHAR2,
316 x_ATTRIBUTE3 VARCHAR2,
317 x_ATTRIBUTE4 VARCHAR2,
318 x_ATTRIBUTE5 VARCHAR2,
319 x_ATTRIBUTE6 VARCHAR2,
320 x_ATTRIBUTE7 VARCHAR2,
321 x_ATTRIBUTE8 VARCHAR2,
322 x_ATTRIBUTE9 VARCHAR2,
323 x_ATTRIBUTE10 VARCHAR2,
324 x_ATTRIBUTE11 VARCHAR2,
325 x_ATTRIBUTE12 VARCHAR2,
326 x_ATTRIBUTE13 VARCHAR2,
327 x_ATTRIBUTE14 VARCHAR2,
328 x_ATTRIBUTE15 VARCHAR2,
329 x_object_version_number in out NOCOPY NUMBER,
330 x_msg_count in out NOCOPY NUMBER,
331 x_condition_id NUMBER DEFAULT NULL,
332 x_user_spec_cond_value VARCHAR2 DEFAULT NULL
333 ) IS
334
335 p_object_version_number number := null;
336 p_msg_count number := 1;
337 p_msg_data varchar2(1000);
338 l_count NUMBER;
339 BEGIN
340 x_msg_count := 0;
341 /* Pairs cannot be same */
342 IF x_ORIGINAL_WORD = UPPER(x_REPLACEMENT_WORD) THEN
343 x_msg_count := x_msg_count+1;
344 l_count := 0;
345 FND_MESSAGE.SET_NAME( 'AR', 'HZ_DQM_WR_SAME_PAIR_VALUE' );
346 FND_MESSAGE.SET_TOKEN( 'WORD', UPPER(x_REPLACEMENT_WORD) );
347 FND_MSG_PUB.ADD;
348 END IF;
349
350 /* Original Word is unique for a word list */
351 SELECT count(*) INTO l_count
352 FROM hz_word_replacements
353 WHERE word_list_id = x_WORD_LIST_ID
354 AND delete_flag = 'N'
355 AND original_word = UPPER(x_ORIGINAL_WORD)
356 AND (condition_id IS NULL
357 OR
358 (decode(x_CONDITION_ID, FND_API.G_MISS_NUM, NULL, x_CONDITION_ID) IS NULL)
359 OR
360 ( (condition_id = x_condition_id)
361 AND ( nvl(user_spec_cond_value,FND_API.G_MISS_CHAR) = nvl(x_user_spec_cond_value,FND_API.G_MISS_CHAR )
362 OR nvl(replacement_word,FND_API.G_MISS_CHAR) = nvl(upper(x_replacement_word),FND_API.G_MISS_CHAR)
363 )
364 )
365 )
366 AND rowid <> x_Rowid;
367
368 IF l_count > 0 THEN
369 x_msg_count := x_msg_count +1;
370 l_count := 0;
371 FND_MESSAGE.SET_NAME( 'AR', 'HZ_DUPL_WORD' );
372 FND_MESSAGE.SET_TOKEN( 'WORD', UPPER(x_ORIGINAL_WORD) );
373 FND_MSG_PUB.ADD;
374 END IF;
375
376 /* No Cyclic Pairs */
377 SELECT count(*) INTO l_count
378 FROM hz_word_replacements
379 WHERE word_list_id = x_WORD_LIST_ID
380 AND delete_flag = 'N'
381 AND (original_word = UPPER(x_REPLACEMENT_WORD) OR
382 replacement_word = UPPER(x_ORIGINAL_WORD))
383 AND rowid <> x_Rowid;
384 IF l_count > 0 THEN
385 x_msg_count := x_msg_count+1;
386 l_count := 0;
387 FND_MESSAGE.SET_NAME( 'AR', 'HZ_DQM_WR_CYCLIC' );
388 FND_MESSAGE.SET_TOKEN( 'ORIGINALWORD', UPPER(x_ORIGINAL_WORD) );
389 FND_MESSAGE.SET_TOKEN( 'REPLACEMENTWORD', UPPER(x_REPLACEMENT_WORD) );
390 FND_MSG_PUB.ADD;
391 END IF;
392 IF x_msg_count > 0 THEN
393 return;
394 END IF;
395 Update_Row(
396 x_Rowid ,
397 x_WORD_LIST_ID ,
398 x_ORIGINAL_WORD ,
399 x_REPLACEMENT_WORD ,
400 x_TYPE ,
401 x_COUNTRY_CODE ,
402 x_LAST_UPDATE_DATE ,
403 x_LAST_UPDATED_BY ,
404 x_CREATION_DATE ,
405 x_CREATED_BY ,
406 x_LAST_UPDATE_LOGIN ,
407 x_ATTRIBUTE_CATEGORY,
408 x_ATTRIBUTE1 ,
409 x_ATTRIBUTE2 ,
410 x_ATTRIBUTE3 ,
411 x_ATTRIBUTE4 ,
412 x_ATTRIBUTE5 ,
413 x_ATTRIBUTE6 ,
414 x_ATTRIBUTE7 ,
415 x_ATTRIBUTE8 ,
416 x_ATTRIBUTE9 ,
417 x_ATTRIBUTE10 ,
418 x_ATTRIBUTE11 ,
419 x_ATTRIBUTE12 ,
420 x_ATTRIBUTE13 ,
421 x_ATTRIBUTE14 ,
422 x_ATTRIBUTE15 ,
423 x_object_version_number,
424 x_condition_id ,
425 x_user_spec_cond_value
426 );
427
428 END Update_Row;
429 PROCEDURE Update_Row(
430 x_Rowid VARCHAR2,
431 x_WORD_LIST_ID NUMBER,
432 x_ORIGINAL_WORD VARCHAR2,
433 x_REPLACEMENT_WORD VARCHAR2,
434 x_TYPE VARCHAR2,
435 x_COUNTRY_CODE VARCHAR2,
436 x_LAST_UPDATE_DATE DATE,
437 x_LAST_UPDATED_BY NUMBER,
438 x_CREATION_DATE DATE,
439 x_CREATED_BY NUMBER,
440 x_LAST_UPDATE_LOGIN NUMBER,
441 x_ATTRIBUTE_CATEGORY VARCHAR2,
442 x_ATTRIBUTE1 VARCHAR2,
443 x_ATTRIBUTE2 VARCHAR2,
444 x_ATTRIBUTE3 VARCHAR2,
445 x_ATTRIBUTE4 VARCHAR2,
446 x_ATTRIBUTE5 VARCHAR2,
447 x_ATTRIBUTE6 VARCHAR2,
448 x_ATTRIBUTE7 VARCHAR2,
449 x_ATTRIBUTE8 VARCHAR2,
450 x_ATTRIBUTE9 VARCHAR2,
451 x_ATTRIBUTE10 VARCHAR2,
452 x_ATTRIBUTE11 VARCHAR2,
453 x_ATTRIBUTE12 VARCHAR2,
454 x_ATTRIBUTE13 VARCHAR2,
455 x_ATTRIBUTE14 VARCHAR2,
456 x_ATTRIBUTE15 VARCHAR2,
457 x_object_version_number in out NOCOPY NUMBER,
458 x_condition_id NUMBER DEFAULT NULL,
459 x_user_spec_cond_value VARCHAR2 DEFAULT NULL
460 ) IS
461
462 p_object_version_number number := null;
463
464 BEGIN
465 p_object_version_number := NVL(X_object_version_number, 1) + 1;
466
467 Update HZ_WORD_REPLACEMENTS
468 SET
469 REPLACEMENT_WORD = decode(x_REPLACEMENT_WORD,NULL,REPLACEMENT_WORD, FND_API.G_MISS_CHAR, NULL,UPPER(x_REPLACEMENT_WORD)),
470 TYPE = decode( x_TYPE,NULL,TYPE,FND_API.G_MISS_CHAR, NULL, x_TYPE),
471 COUNTRY_CODE = decode( x_COUNTRY_CODE, NULL, COUNTRY_CODE,FND_API.G_MISS_CHAR, NULL, x_COUNTRY_CODE),
472 LAST_UPDATE_DATE = decode( x_LAST_UPDATE_DATE, NULL,LAST_UPDATE_DATE,FND_API.G_MISS_DATE,NULL,x_LAST_UPDATE_DATE),
473 LAST_UPDATED_BY = decode( x_LAST_UPDATED_BY, NULL,LAST_UPDATED_BY,FND_API.G_MISS_NUM,NULL,x_LAST_UPDATED_BY),
474 -- Bug 3032780
475 /*
476 CREATION_DATE = decode( x_CREATION_DATE, NULL,CREATION_DATE,FND_API.G_MISS_DATE,NULL,x_CREATION_DATE),
477 CREATED_BY = decode( x_CREATED_BY, NULL,CREATED_BY,FND_API.G_MISS_NUM,NULL,x_CREATED_BY),
478 */
479 LAST_UPDATE_LOGIN = decode( x_LAST_UPDATE_LOGIN, NULL,LAST_UPDATE_LOGIN,FND_API.G_MISS_NUM,NULL,x_LAST_UPDATE_LOGIN),
480 ATTRIBUTE_CATEGORY = decode( x_ATTRIBUTE_CATEGORY, NULL,ATTRIBUTE_CATEGORY,FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE_CATEGORY),
481 ATTRIBUTE1 = decode( x_ATTRIBUTE1, NULL,ATTRIBUTE1,FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE1),
482 ATTRIBUTE2 = decode( x_ATTRIBUTE2, NULL,ATTRIBUTE2,FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE2),
483 ATTRIBUTE3 = decode( x_ATTRIBUTE3, NULL,ATTRIBUTE3,FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE3),
484 ATTRIBUTE4 = decode( x_ATTRIBUTE4, NULL,ATTRIBUTE4,FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE4),
485 ATTRIBUTE5 = decode( x_ATTRIBUTE5, NULL,ATTRIBUTE5,FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE5),
486 ATTRIBUTE6 = decode( x_ATTRIBUTE6, NULL,ATTRIBUTE6,FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE6),
487 ATTRIBUTE7 = decode( x_ATTRIBUTE7, NULL,ATTRIBUTE7,FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE7),
488 ATTRIBUTE8 = decode( x_ATTRIBUTE8, NULL,ATTRIBUTE8,FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE8),
489 ATTRIBUTE9 = decode( x_ATTRIBUTE9, NULL,ATTRIBUTE9,FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE9),
490 ATTRIBUTE10 = decode( x_ATTRIBUTE10, NULL,ATTRIBUTE10,FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE10),
491 ATTRIBUTE11 = decode( x_ATTRIBUTE11, NULL,ATTRIBUTE11,FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE11),
492 ATTRIBUTE12 = decode( x_ATTRIBUTE12, NULL,ATTRIBUTE12,FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE12),
493 ATTRIBUTE13 = decode( x_ATTRIBUTE13, NULL,ATTRIBUTE13,FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE13),
494 ATTRIBUTE14 = decode( x_ATTRIBUTE14, NULL,ATTRIBUTE14,FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE14),
495 ATTRIBUTE15 = decode( x_ATTRIBUTE15, NULL,ATTRIBUTE15,FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE15),
496 OBJECT_VERSION_NUMBER = p_object_version_number,
497 CONDITION_ID = decode(x_CONDITION_ID, NULL, CONDITION_ID,FND_API.G_MISS_NUM,NULL, x_CONDITION_ID),
498 USER_SPEC_COND_VALUE= decode(x_USER_SPEC_COND_VALUE,NULL,USER_SPEC_COND_VALUE,FND_API.G_MISS_CHAR, NULL,x_USER_SPEC_COND_VALUE)
499 where rowid = X_RowId;
500
501 If (SQL%NOTFOUND) then
502 RAISE NO_DATA_FOUND;
503 End If;
504
505 x_object_version_number := p_object_version_number;
506
507 END Update_Row;
508
509 PROCEDURE Lock_Row(
510 x_Rowid VARCHAR2,
511 x_WORD_LIST_ID NUMBER,
512 x_ORIGINAL_WORD VARCHAR2,
513 x_object_version_number NUMBER
514 ) IS
515
516 CURSOR C IS
517 SELECT object_version_number
518 FROM HZ_WORD_REPLACEMENTS
519 WHERE rowid = X_rowid
520 FOR UPDATE of ORIGINAL_WORD NOWAIT;
521 Recinfo C%ROWTYPE;
522 BEGIN
523 OPEN C;
524 FETCH C INTO Recinfo;
525 If (C%NOTFOUND) then
526 CLOSE C;
527 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
528 APP_EXCEPTION.RAISE_EXCEPTION;
529 End If;
530 CLOSE C;
531
532 if(
533 ( recinfo.OBJECT_VERSION_NUMBER IS NULL AND X_object_version_number IS NULL )
534 OR ( recinfo.OBJECT_VERSION_NUMBER IS NOT NULL AND
535 X_object_version_number IS NOT NULL AND
536 recinfo.OBJECT_VERSION_NUMBER = X_object_version_number )
537 ) then
538 null;
539 else
540 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
541 app_exception.raise_exception;
542 end if;
543
544 END Lock_Row;
545
546 END HZ_WORD_REPLACEMENTS1_PKG;