[Home] [Help]
PACKAGE BODY: APPS.HZ_WORD_RPL_CONDS_PKG
Source
1 PACKAGE BODY HZ_WORD_RPL_CONDS_PKG as
2 /*$Header: ARHWRCDB.pls 120.4 2005/06/02 22:51:23 cvijayan noship $ */
3
4 PROCEDURE Insert_Row (
5 x_condition_id IN OUT NOCOPY NUMBER,
6 x_entity IN VARCHAR2,
7 x_condition_function IN VARCHAR2,
8 x_condition_val_fmt_flag IN VARCHAR2,
9 x_condition_name IN VARCHAR2,
10 x_condition_description IN VARCHAR2,
11 x_object_version_number IN NUMBER
12 ) IS
13
14
15 CURSOR C2 IS SELECT HZ_WORD_RPL_CONDS_S.nextval FROM sys.dual ;
16 l_success VARCHAR2(1) := 'N';
17
18 BEGIN
19
20 WHILE l_success = 'N' LOOP
21 BEGIN
22 IF ( X_CONDITION_ID IS NULL) OR (X_CONDITION_ID = FND_API.G_MISS_NUM) THEN
23 OPEN C2;
24 FETCH C2 INTO X_CONDITION_ID;
25 CLOSE C2;
26 END IF;
27
28 INSERT INTO HZ_WORD_RPL_CONDS_B(
29 condition_id,
30 entity,
31 condition_function,
32 condition_val_fmt_flag,
33 last_update_date,
34 last_updated_by,
35 creation_date,
36 created_by,
37 last_update_login,
38 object_version_number
39 )
40 VALUES (
41 x_condition_id,
42 DECODE(x_entity,
43 FND_API.G_MISS_CHAR, NULL,
44 x_entity),
45 DECODE(x_condition_function,
46 FND_API.G_MISS_CHAR, NULL,
47 x_condition_function),
48 DECODE(x_condition_val_fmt_flag,
49 FND_API.G_MISS_CHAR, NULL,
50 x_condition_val_fmt_flag),
51 hz_utility_v2pub.last_update_date,
52 hz_utility_v2pub.last_updated_by,
53 hz_utility_v2pub.creation_date,
54 hz_utility_v2pub.created_by,
55 hz_utility_v2pub.last_update_login,
56 DECODE(x_object_version_number,
57 FND_API.G_MISS_NUM, NULL,
58 x_object_version_number)
59 ) ;
60
61 INSERT INTO HZ_WORD_RPL_CONDS_TL (
62 condition_id,
63 condition_name,
64 condition_description,
65 last_update_date,
66 last_updated_by,
67 creation_date,
68 created_by,
69 last_update_login,
70 language,
71 source_lang,
72 object_version_number
73 )
74 SELECT
75 x_condition_id,
76 x_condition_name,
77 x_condition_description,
78 hz_utility_v2pub.last_update_date,
79 hz_utility_v2pub.last_updated_by,
80 hz_utility_v2pub.creation_date,
81 hz_utility_v2pub.created_by,
82 hz_utility_v2pub.last_update_login,
83 L.LANGUAGE_CODE,
84 userenv('LANG'),
85 x_object_version_number
86 FROM FND_LANGUAGES L
87 where L.INSTALLED_FLAG in ('I', 'B')
88 and not exists
89 (select NULL
90 from HZ_WORD_RPL_CONDS_TL T
91 where T.CONDITION_ID = X_CONDITION_ID
92 and T.LANGUAGE = L.LANGUAGE_CODE);
93 l_success := 'Y';
94 END;
95
96 END LOOP;
97
98 END Insert_Row;
99
100
101 procedure Update_Row (
102 x_condition_id IN NUMBER,
103 x_entity IN VARCHAR2,
104 x_condition_function IN VARCHAR2,
105 x_condition_val_fmt_flag IN VARCHAR2,
106 x_condition_name IN VARCHAR2,
107 x_condition_description IN VARCHAR2,
108 x_object_version_number IN OUT NOCOPY NUMBER
109 )
110 IS
111 p_object_version_number NUMBER ;
112 BEGIN
113 p_object_version_number := NVL(x_object_version_number, 1) + 1;
114
115 UPDATE HZ_WORD_RPL_CONDS_B set
116 entity = x_entity,
117 condition_function = x_condition_function,
118 condition_val_fmt_flag = x_condition_val_fmt_flag,
119 object_version_number = p_object_version_number,
120 last_update_date = hz_utility_v2pub.last_update_date,
121 last_updated_by = hz_utility_v2pub.last_updated_by,
122 last_update_login = hz_utility_v2pub.last_update_login
123 where condition_id = x_condition_id ;
124
125 if (sql%notfound) then
126 raise no_data_found;
127 end if;
128
129 update HZ_WORD_RPL_CONDS_TL set
130 condition_name = x_condition_name,
131 condition_description = x_condition_description,
132 object_version_number = p_object_version_number,
133 last_update_date = hz_utility_v2pub.last_update_date,
134 last_updated_by = hz_utility_v2pub.last_updated_by,
135 last_update_login = hz_utility_v2pub.last_update_login,
136 source_lang = userenv('LANG')
137 where condition_id = x_condition_id
138 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
139
140 x_object_version_number := p_object_version_number ;
141
142 if (sql%notfound) then
143 raise no_data_found;
144 end if;
145
146 END ;
147
148
149 procedure Delete_Row (
150 x_condition_id in NUMBER
151 )
152 IS
153 BEGIN
154 delete from HZ_WORD_RPL_CONDS_B
155 where condition_id = x_condition_id ;
156
157 if (sql%notfound) then
158 raise no_data_found;
159 end if;
160
161 delete from HZ_WORD_RPL_CONDS_TL
162 where condition_id = x_condition_id ;
163
164 if (sql%notfound) then
165 raise no_data_found;
166 end if;
167 END ;
168
169 procedure Lock_Row (
170 x_condition_id in NUMBER,
171 x_object_version_number in NUMBER
172 )
173 IS
174 cursor c is select
175 object_version_number
176 from HZ_WORD_RPL_CONDS_B
177 where condition_id = x_condition_id
178 for update of condition_id nowait;
179
180 recinfo c%rowtype;
181
182 BEGIN
183
184 open c;
185 fetch c into recinfo;
186 if (c%notfound) then
187 close c;
188 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
189 app_exception.raise_exception;
190 end if;
191 close c;
192
193 if(
194 ( recinfo.object_version_number IS NULL AND x_object_version_number IS NULL )
195 OR ( recinfo.object_version_number IS NOT NULL AND
196 x_object_version_number IS NOT NULL AND
197 recinfo.object_version_number = x_object_version_number )
198 ) then
199 null;
200 else
201 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
202 app_exception.raise_exception;
203 end if;
204
205 return;
206
207 END Lock_Row ;
208
209
210 procedure Add_Language
211 IS
212 BEGIN
213 delete from HZ_WORD_RPL_CONDS_TL T
214 where not exists
215 (select NULL
216 from HZ_WORD_RPL_CONDS_B B
217 where B.condition_id = T.condition_id
218 );
219
220 update HZ_WORD_RPL_CONDS_TL T set (
221 condition_name,
222 condition_description,
223 object_version_number ------> VJN Introduced for Bug 4397811
224 ) = (select
225 B.condition_name,
226 B.condition_description,
227 NVL(T.object_version_number, 1) + 1 ------> VJN Introduced for Bug 4397811
228 from HZ_WORD_RPL_CONDS_TL B
229 where B.condition_id = T.condition_id
230 and B.language = T.source_lang)
231 where (
232 T.condition_id,
233 T.language
234 ) in (select
235 SUBT.condition_id,
236 SUBT.language
237 from HZ_WORD_RPL_CONDS_TL SUBB, HZ_WORD_RPL_CONDS_TL SUBT
238 where SUBB.condition_id = SUBT.condition_id
239 and SUBB.language = SUBT.source_lang
240 and (SUBB.condition_name <> SUBT.condition_name
241 or SUBB.condition_description <> SUBT.condition_description
242 or (SUBB.condition_description is null and SUBT.condition_description is not null)
243 or (SUBB.condition_description is not null and SUBT.condition_description is null)
244 ));
245
246 insert into HZ_WORD_RPL_CONDS_TL (
247 LAST_UPDATE_DATE,
248 CREATION_DATE,
249 CREATED_BY,
250 CONDITION_DESCRIPTION,
251 CONDITION_NAME,
252 CONDITION_ID,
253 LAST_UPDATED_BY,
254 LAST_UPDATE_LOGIN,
255 LANGUAGE,
256 SOURCE_LANG,
257 OBJECT_VERSION_NUMBER ------> VJN Introduced for Bug 4397811
258 ) select
259 B.LAST_UPDATE_DATE,
260 B.CREATION_DATE,
261 B.CREATED_BY,
262 B.CONDITION_DESCRIPTION,
263 B.CONDITION_NAME,
264 B.CONDITION_ID,
265 B.LAST_UPDATED_BY,
266 B.LAST_UPDATE_LOGIN,
267 L.LANGUAGE_CODE,
268 B.SOURCE_LANG,
269 1 ------> VJN Introduced for Bug 4397811
270 from HZ_WORD_RPL_CONDS_TL B, FND_LANGUAGES L
271 where L.INSTALLED_FLAG in ('I', 'B')
272 and B.LANGUAGE = userenv('LANG')
273 and not exists
274 (select NULL
275 from HZ_WORD_RPL_CONDS_TL T
276 where T.condition_id = B.condition_id
277 and T.language = L.language_code );
278 END ;
279
280
281 procedure Load_Row (
282 x_condition_id IN OUT NOCOPY NUMBER,
283 x_entity IN VARCHAR2,
284 x_condition_function IN VARCHAR2,
285 x_condition_val_fmt_flag IN VARCHAR2,
286 x_condition_name IN VARCHAR2,
287 x_condition_description IN VARCHAR2,
288 x_object_version_number IN NUMBER,
289 x_last_update_date IN DATE,
290 x_last_updated_by IN NUMBER,
291 x_last_update_login IN NUMBER,
292 x_owner IN VARCHAR2,
293 x_custom_mode IN VARCHAR2
294 )
295 IS
296 l_f_luby number; -- entity owner in file
297 l_f_ludate date; -- entity update date in file
298 l_db_luby number; -- entity owner in db
299 l_db_ludate date; -- entity update date in db
300 l_object_version_number number ;
301
302 begin
303
304 -- Translate owner to file_last_updated_by
305 IF (x_owner = 'SEED')
306 THEN
307 l_f_luby := 1;
308 ELSE
309 l_f_luby := 0;
310 END IF ;
311
312 -- Get last update date of ldt entity
313 l_f_ludate := nvl(x_last_update_date, sysdate);
314
315 l_object_version_number := x_object_version_number ;
316
317 BEGIN
318 select LAST_UPDATED_BY, LAST_UPDATE_DATE
319 into l_db_luby, l_db_ludate
320 from HZ_WORD_RPL_CONDS_B
321 where condition_id = x_condition_id ;
322
323 -- Update record, honoring customization mode.
324 -- Record should be updated only if:
325 -- a. CUSTOM_MODE = FORCE, or
326 -- b. file owner is CUSTOM, db owner is SEED
327 -- c. owners are the same, and file_date > db_date
328
329 IF ((x_custom_mode = 'FORCE') or
330 ((l_f_luby = 0) and (l_db_luby = 1)) or
331 ((l_f_luby = l_db_luby) and (l_f_ludate > l_db_ludate)))
332 THEN
333
334 HZ_WORD_RPL_CONDS_PKG.UPDATE_ROW(
335 x_condition_id => x_condition_id,
336 x_entity => x_entity,
337 x_condition_function => x_condition_function,
338 x_condition_val_fmt_flag => x_condition_val_fmt_flag,
339 x_condition_name => x_condition_name,
340 x_condition_description => x_condition_description,
341 x_object_version_number => l_object_version_number
342 );
343 END IF ;
344
345 EXCEPTION
346 WHEN NO_DATA_FOUND
347 THEN
348
349 HZ_WORD_RPL_CONDS_PKG.INSERT_ROW(
350 x_condition_id => x_condition_id,
351 x_entity => x_entity,
352 x_condition_function => x_condition_function,
353 x_condition_val_fmt_flag => x_condition_val_fmt_flag,
354 x_condition_name => x_condition_name,
355 x_condition_description => x_condition_description,
356 x_object_version_number => x_object_version_number
357 );
358
359 END ;
360
361 END ;
362
363 -- update rows that have not been altered by user
364 procedure Translate_Row (
365 x_condition_id in NUMBER,
366 x_condition_name in VARCHAR2,
367 x_condition_description in VARCHAR2,
368 x_owner in VARCHAR2)
369 IS
370 BEGIN
371
372 UPDATE HZ_WORD_RPL_CONDS_TL set
373 condition_name = x_condition_name,
374 condition_description = x_condition_description,
375 source_lang = userenv('LANG'),
376 last_update_date = sysdate,
377 last_updated_by = decode(x_owner, 'SEED', 1, 0),
378 last_update_login = 0
379 where condition_id = x_condition_id
380 and userenv('LANG') in (language, source_lang);
381
382 END ;
383
384
385 END HZ_WORD_RPL_CONDS_PKG ;
386