[Home] [Help]
PACKAGE BODY: APPS.HZ_DSS_GROUPS_PKG
Source
1 PACKAGE BODY HZ_DSS_GROUPS_PKG AS
2 /* $Header: ARHPDSGB.pls 120.2 2005/06/16 21:13:28 jhuang noship $ */
3
4 PROCEDURE Insert_Row (
5 x_rowid IN OUT NOCOPY VARCHAR2,
6 x_dss_group_code IN VARCHAR2,
7 x_rank IN NUMBER,
8 x_status IN VARCHAR2,
9 x_dss_group_name IN VARCHAR2,
10 x_description IN VARCHAR2,
11 x_bes_enable_flag IN VARCHAR2,
12 x_object_version_number IN NUMBER
13 ) IS
14
15 l_success VARCHAR2(1) := 'N';
16
17 BEGIN
18
19 WHILE l_success = 'N' LOOP
20 BEGIN
21 INSERT INTO HZ_DSS_GROUPS_B (
22 dss_group_code,
23 rank,
24 status,
25 last_update_date,
26 last_updated_by,
27 creation_date,
28 created_by,
29 last_update_login,
30 bes_enable_flag,
31 object_version_number
32 )
33 VALUES (
34 DECODE(x_dss_group_code,
35 FND_API.G_MISS_CHAR, NULL, x_dss_group_code),
36 DECODE(x_rank,
37 FND_API.G_MISS_NUM, NULL,
38 x_rank),
39 DECODE(x_status,
40 FND_API.G_MISS_CHAR, 'A',
41 NULL, 'A',
42 x_status),
43 hz_utility_v2pub.last_update_date,
44 hz_utility_v2pub.last_updated_by,
45 hz_utility_v2pub.creation_date,
46 hz_utility_v2pub.created_by,
47 hz_utility_v2pub.last_update_login,
48 DECODE(x_bes_enable_flag,
49 FND_API.G_MISS_CHAR, NULL,
50 x_bes_enable_flag),
51 DECODE(x_object_version_number,
52 FND_API.G_MISS_NUM, NULL,
53 x_object_version_number)
54 ) RETURNING
55 rowid
56 INTO
57 x_rowid;
58
59 INSERT INTO HZ_DSS_GROUPS_TL (
60 dss_group_code,
61 language,
62 source_lang,
63 dss_group_name,
64 description,
65 last_update_date,
66 last_updated_by,
67 creation_date,
68 created_by,
69 last_update_login
70 )
71 SELECT
72 x_dss_group_code,
73 L.LANGUAGE_CODE,
74 B.LANGUAGE_CODE,
75 x_dss_group_name,
76 x_description,
77 hz_utility_v2pub.last_update_date,
78 hz_utility_v2pub.last_updated_by,
79 hz_utility_v2pub.creation_date,
80 hz_utility_v2pub.created_by,
81 hz_utility_v2pub.last_update_login
82 FROM
83 FND_LANGUAGES L, FND_LANGUAGES B
84 WHERE L.INSTALLED_FLAG in ('I', 'B')
85 and B.INSTALLED_FLAG = 'B'
86 and not exists
87 (SELECT NULL
88 FROM HZ_DSS_GROUPS_TL T
89 WHERE T.dss_group_code = x_dss_group_code AND
90 T.LANGUAGE = L.LANGUAGE_CODE);
91 l_success := 'Y';
92
93 END;
94
95 END LOOP;
96
97
98
99 END Insert_Row;
100
101 PROCEDURE Update_Row (
102 x_rowid IN OUT NOCOPY VARCHAR2,
103 -- x_dss_group_code IN VARCHAR2,
104 x_rank IN NUMBER,
105 x_status IN VARCHAR2,
106 x_dss_group_name IN VARCHAR2,
107 x_description IN VARCHAR2,
108 x_bes_enable_flag IN VARCHAR2,
109 x_object_version_number IN NUMBER
110 ) IS
111 l_dss_group_code varchar2(30);
112 BEGIN
113
114 UPDATE HZ_DSS_GROUPS_B
115 SET
116 rank =
117 DECODE(x_rank,
118 NULL, rank,
119 FND_API.G_MISS_NUM, NULL,
120 x_rank),
121 status =
122 DECODE(x_status,
123 NULL, status,
124 FND_API.G_MISS_CHAR, NULL,
125 x_status),
126 last_update_date = hz_utility_v2pub.last_update_date,
127 last_updated_by = hz_utility_v2pub.last_updated_by,
128 creation_date = creation_date,
129 created_by = created_by,
130 last_update_login = hz_utility_v2pub.last_update_login,
131 bes_enable_flag =
132 DECODE(x_bes_enable_flag,
133 NULL, bes_enable_flag,
134 FND_API.G_MISS_CHAR, NULL,
135 x_bes_enable_flag),
136 object_version_number=
137 DECODE(x_object_version_number,
138 NULL, object_version_number,
139 FND_API.G_MISS_NUM, NULL,
140 x_object_version_number)
141 WHERE rowid = x_rowid
142 returning dss_group_code into l_dss_group_code ;
143 IF ( SQL%NOTFOUND ) THEN
144 RAISE NO_DATA_FOUND;
145 END IF;
146
147 UPDATE HZ_DSS_GROUPS_TL
148 SET
149 source_lang =
150 DECODE(USERENV('LANG'),
151 NULL, source_lang,
152 FND_API.G_MISS_CHAR, NULL,
153 USERENV('LANG') ),
154 dss_group_name =
155 DECODE(x_dss_group_name,
156 NULL, dss_group_name,
157 FND_API.G_MISS_CHAR, NULL,
158 x_dss_group_name),
159 description =
160 DECODE(x_description,
161 NULL, description,
162 FND_API.G_MISS_CHAR, NULL,
163 x_description),
164 last_update_date = hz_utility_v2pub.last_update_date,
165 last_updated_by = hz_utility_v2pub.last_updated_by,
166 last_update_login = hz_utility_v2pub.last_update_login
167
168 WHERE dss_group_code = l_dss_group_code AND
169 USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
170
171 IF ( SQL%NOTFOUND ) THEN
172 RAISE NO_DATA_FOUND;
173 END IF;
174
175 END Update_Row;
176
177 PROCEDURE Lock_Row (
178 x_rowid IN OUT NOCOPY VARCHAR2,
179 x_dss_group_code IN VARCHAR2,
180 x_rank IN NUMBER,
181 x_status IN VARCHAR2,
182 x_last_update_date IN DATE,
183 x_last_updated_by IN NUMBER,
184 x_creation_date IN DATE,
185 x_created_by IN NUMBER,
186 x_last_update_login IN NUMBER,
187 x_bes_enable_flag IN VARCHAR2,
188 x_object_version_number IN NUMBER
189 ) IS
190
191 CURSOR c IS
192 SELECT * FROM hz_dss_groups_b
193 WHERE rowid = x_rowid
194 FOR UPDATE NOWAIT;
195 Recinfo c%ROWTYPE;
196
197 BEGIN
198
199 OPEN c;
200 FETCH c INTO Recinfo;
201 IF ( c%NOTFOUND ) THEN
202 CLOSE c;
203 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
204 APP_EXCEPTION.RAISE_EXCEPTION;
205 END IF;
206 CLOSE C;
207
208 IF (
209 ( ( Recinfo.dss_group_code = x_dss_group_code )
210 OR ( ( Recinfo.dss_group_code IS NULL )
211 AND ( x_dss_group_code IS NULL ) ) )
212 AND ( ( Recinfo.rank = x_rank )
213 OR ( ( Recinfo.rank IS NULL )
214 AND ( x_rank IS NULL ) ) )
215 AND ( ( Recinfo.status = x_status )
216 OR ( ( Recinfo.status IS NULL )
217 AND ( x_status IS NULL ) ) )
218 AND ( ( Recinfo.last_update_date = x_last_update_date )
219 OR ( ( Recinfo.last_update_date IS NULL )
220 AND ( x_last_update_date IS NULL ) ) )
221 AND ( ( Recinfo.last_updated_by = x_last_updated_by )
222 OR ( ( Recinfo.last_updated_by IS NULL )
223 AND ( x_last_updated_by IS NULL ) ) )
224 AND ( ( Recinfo.creation_date = x_creation_date )
225 OR ( ( Recinfo.creation_date IS NULL )
226 AND ( x_creation_date IS NULL ) ) )
227 AND ( ( Recinfo.created_by = x_created_by )
228 OR ( ( Recinfo.created_by IS NULL )
229 AND ( x_created_by IS NULL ) ) )
230 AND ( ( Recinfo.last_update_login = x_last_update_login )
231 OR ( ( Recinfo.last_update_login IS NULL )
232 AND ( x_last_update_login IS NULL ) ) )
233 AND ( ( Recinfo.bes_enable_flag = x_bes_enable_flag )
234 OR ( ( Recinfo.bes_enable_flag IS NULL )
235 AND ( x_bes_enable_flag IS NULL ) ) )
236 AND ( ( Recinfo.object_version_number = x_object_version_number)
237 OR ( ( Recinfo.object_version_number IS NULL )
238 AND ( x_object_version_number IS NULL ) ) )
239 ) THEN
240 RETURN;
241 ELSE
242 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
243 APP_EXCEPTION.RAISE_EXCEPTION;
244 END IF;
245
246 END Lock_Row;
247
248 PROCEDURE Select_Row (
249 x_dss_group_code IN OUT NOCOPY VARCHAR2,
250 x_rank OUT NOCOPY NUMBER,
251 x_status OUT NOCOPY VARCHAR2,
252 x_dss_group_name OUT NOCOPY VARCHAR2,
253 x_description OUT NOCOPY VARCHAR2,
254 x_bes_enable_flag OUT NOCOPY VARCHAR2,
255 x_object_version_number OUT NOCOPY NUMBER
256
257 ) IS
258 x_dummy1 VARCHAR2(2000); x_dummy2 varchar2(2000);
259 BEGIN
260
261 SELECT
262 NVL(dss_group_code, FND_API.G_MISS_CHAR),
263 NVL(rank, FND_API.G_MISS_NUM),
264 NVL(status, FND_API.G_MISS_CHAR),
265 NVL(bes_enable_flag, FND_API.G_MISS_CHAR),
266 NVL(object_version_number, FND_API.G_MISS_NUM)
267 INTO
268 x_dss_group_code,
269 x_rank,
270 x_status,
271 x_bes_enable_flag,
272 x_object_version_number
273 FROM HZ_DSS_GROUPS_B
274 WHERE dss_group_code = x_dss_group_code;
275
276
277 SELECT
278 NVL(dss_group_code, FND_API.G_MISS_CHAR),
279 NVL(dss_group_name, FND_API.G_MISS_CHAR),
280 NVL(description, FND_API.G_MISS_CHAR)
281 INTO
282 x_dss_group_code, x_dummy1, x_dummy2
283 FROM HZ_DSS_GROUPS_TL
284 WHERE dss_group_code = x_dss_group_code AND
285 USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
286
287 EXCEPTION
288 WHEN NO_DATA_FOUND THEN
289 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
290 FND_MESSAGE.SET_TOKEN('RECORD', 'dss_group_rec');
291 FND_MESSAGE.SET_TOKEN('VALUE', x_dss_group_code);
292 FND_MSG_PUB.ADD;
293 RAISE FND_API.G_EXC_ERROR;
294
295 END Select_Row;
296
297 PROCEDURE Delete_Row (
298 x_dss_group_code IN VARCHAR2
299 ) IS
300 BEGIN
301
302 DELETE FROM HZ_DSS_GROUPS_B
303 WHERE dss_group_code = x_dss_group_code;
304
305 IF ( SQL%NOTFOUND ) THEN
306 RAISE NO_DATA_FOUND;
307 END IF;
308
309 DELETE FROM HZ_DSS_GROUPS_TL
310 WHERE dss_group_code = x_dss_group_code;
311
312 IF ( SQL%NOTFOUND ) THEN
313 RAISE NO_DATA_FOUND;
314 END IF;
315
316 END Delete_Row;
317
318
319
320 procedure ADD_LANGUAGE
321 is
322 begin
323 delete from HZ_DSS_GROUPS_TL T
324 where not exists
325 (select NULL
326 from HZ_DSS_GROUPS_B B
327 where B.DSS_GROUP_CODE = T.DSS_GROUP_CODE
328 );
329
330 update HZ_DSS_GROUPS_TL T set (
331 DSS_GROUP_CODE,
332 DESCRIPTION
333 ) = (select
334 B.DSS_GROUP_NAME,
335 B.DESCRIPTION
336 from HZ_DSS_GROUPS_TL B
337 where B.DSS_GROUP_CODE = T.DSS_GROUP_CODE
338 and B.LANGUAGE = T.SOURCE_LANG)
339 where (
340 T.DSS_GROUP_CODE,
341 T.LANGUAGE
345 from HZ_DSS_GROUPS_TL SUBB, HZ_DSS_GROUPS_TL SUBT
342 ) in (select
343 SUBT.DSS_GROUP_CODE,
344 SUBT.LANGUAGE
346 where SUBB.DSS_GROUP_CODE = SUBT.DSS_GROUP_CODE
347 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
348 and (SUBB.DSS_GROUP_NAME <> SUBT.DSS_GROUP_NAME
349 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
350 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
351 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
352 ));
353
354 insert into HZ_DSS_GROUPS_TL (
355 DSS_GROUP_CODE,
356 DSS_GROUP_NAME,
357 DESCRIPTION,
358 LAST_UPDATE_DATE,
359 LAST_UPDATED_BY,
360 CREATION_DATE,
361 CREATED_BY,
362 LAST_UPDATE_LOGIN,
363 LANGUAGE,
364 SOURCE_LANG
365 ) select
366 B.DSS_GROUP_CODE,
367 B.DSS_GROUP_NAME,
368 B.DESCRIPTION,
369 B.LAST_UPDATE_DATE,
370 B.LAST_UPDATED_BY,
371 B.CREATION_DATE,
372 B.CREATED_BY,
373 B.LAST_UPDATE_LOGIN,
374 L.LANGUAGE_CODE,
375 B.SOURCE_LANG
376 from HZ_DSS_GROUPS_TL B, FND_LANGUAGES L
377 where L.INSTALLED_FLAG in ('I', 'B')
378 and B.LANGUAGE = userenv('LANG')
379 and not exists
380 (select NULL
381 from HZ_DSS_GROUPS_TL T
382 where T.DSS_GROUP_CODE = B.DSS_GROUP_CODE
383 and T.LANGUAGE = L.LANGUAGE_CODE);
384 end ADD_LANGUAGE;
385
386
387 procedure LOAD_ROW (
388 X_DSS_GROUP_CODE in VARCHAR2,
389 X_DATABASE_OBJECT_NAME in VARCHAR2,
390 X_DSS_GROUP_NAME in VARCHAR2,
391 X_DESCRIPTION in VARCHAR2,
392 X_OWNER in VARCHAR2, -- "SEED" or "CUSTOM"
393 X_LAST_UPDATE_DATE in DATE,
394 X_CUSTOM_MODE in VARCHAR2,
395 X_RANK in NUMBER,
396 X_STATUS in VARCHAR2,
397 X_BES_ENABLE_FLAG IN VARCHAR2,
398 X_OBJECT_VERSION_NUMBER IN NUMBER
399 ) is
400 l_f_luby number; -- entity owner in file
401 l_f_ludate date; -- entity update date in file
402 l_db_luby number; -- entity owner in db
403 l_db_ludate date; -- entity update date in db
404 l_rowid varchar2(64);
405
406 begin
407
408 -- Translate owner to file_last_updated_by
409 if (x_owner = 'SEED') then
410 l_f_luby := 1;
411 else
412 l_f_luby := 0;
413 end if;
414
415 -- Get last update date of ldt entity
416 l_f_ludate := nvl(x_last_update_date, sysdate);
417
418 begin
419 select LAST_UPDATED_BY, LAST_UPDATE_DATE
420 into l_db_luby, l_db_ludate
421 from HZ_DSS_GROUPS_B
422 where DSS_GROUP_CODE = x_dss_group_code;
423
424 -- Update record, honoring customization mode.
425 -- Record should be updated only if:
426 -- a. CUSTOM_MODE = FORCE, or
427 -- b. file owner is CUSTOM, db owner is SEED
428 -- c. owners are the same, and file_date > db_date
429
430 if ((x_custom_mode = 'FORCE') or
431 ((l_f_luby = 0) and (l_db_luby = 1)) or
432 ((l_f_luby = l_db_luby) and (l_f_ludate > l_db_ludate)))
433 then
434 hz_dss_groups_pkg.update_row (
435 X_ROWID => L_ROWID,
436 --X_DSS_GROUP_CODE => X_DSS_GROUP_CODE,
437 X_RANK => X_RANK,
438 X_STATUS => X_STATUS,
439 X_DSS_GROUP_NAME => X_DSS_GROUP_NAME,
440 X_DESCRIPTION => X_DESCRIPTION,
441 X_BES_ENABLE_FLAG => X_BES_ENABLE_FLAG,
442 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER
443 );
444 end if;
445
446 exception
447 when no_data_found then
448 -- record not found, insert in all cases
449 hz_dss_groups_pkg.insert_row(
450 x_rowid => l_rowid,
451 x_dss_group_code => X_DSS_GROUP_CODE,
452 x_rank => x_rank,
453 x_status => x_status,
454 x_dss_group_name => X_DSS_GROUP_NAME,
455 x_description => X_DESCRIPTION,
456 x_bes_enable_flag => x_bes_enable_flag,
457 x_object_version_number => x_object_version_number
458 );
459 end;
460
461 end LOAD_ROW;
462
463 procedure TRANSLATE_ROW (
464 X_DSS_GROUP_CODE in VARCHAR2,
465 X_DSS_GROUP_NAME in VARCHAR2,
466 X_DESCRIPTION in VARCHAR2,
467 X_OWNER in VARCHAR2, -- "SEED" or "CUSTOM"
468 X_LAST_UPDATE_DATE in DATE,
469 X_CUSTOM_MODE in VARCHAR2
470 ) is
471 l_f_luby number; -- entity owner in file
472 l_f_ludate date; -- entity update date in file
473 l_db_luby number; -- entity owner in db
474 l_db_ludate date; -- entity update date in db
475 begin
476 -- Translate owner to file_last_updated_by
477 if (x_owner = 'SEED') then
478 l_f_luby := 1;
479 else
480 l_f_luby := 0;
481 end if;
482
483 -- Get last update date of ldt entity
484 l_f_ludate := nvl(x_last_update_date, sysdate);
485
486 begin
487 select LAST_UPDATED_BY, LAST_UPDATE_DATE
488 into l_db_luby, l_db_ludate
489 from HZ_DSS_GROUPS_TL
490 where DSS_GROUP_CODE = x_dss_group_code
491 and LANGUAGE = userenv('LANG');
492
493 -- Update record, honoring customization mode.
494 -- Record should be updated only if:
495 -- a. CUSTOM_MODE = FORCE, or
496 -- b. file owner is CUSTOM, db owner is SEED
497 -- c. owners are the same, and file_date > db_date
498
499 if ((x_custom_mode = 'FORCE') or
500 ((l_f_luby = 0) and (l_db_luby = 1)) or
501 ((l_f_luby = l_db_luby) and (l_f_ludate > l_db_ludate)))
502 then
503 update HZ_DSS_GROUPS_TL
504 set DSS_GROUP_NAME = nvl(X_DSS_GROUP_NAME,DSS_GROUP_NAME),
505 DESCRIPTION = nvl(X_DESCRIPTION,DESCRIPTION),
506 LAST_UPDATE_DATE = l_f_ludate,
507 LAST_UPDATED_BY = l_f_luby,
508 LAST_UPDATE_LOGIN = 0,
509 SOURCE_LANG = userenv('LANG')
510 where DSS_GROUP_CODE = X_DSS_GROUP_CODE
511 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
512 end if;
513 exception
514 when no_data_found then
515 null; -- no translation found. standards say do nothing.
516 end;
517
518 end TRANSLATE_ROW;
519
520
521 END HZ_DSS_GROUPS_PKG;