1 PACKAGE BODY HR_NAVIGATION_UNITS_PKG as
2 /* $Header: pewfl01t.pkb 120.0 2005/05/31 23:07:49 appldev noship $ */
3 procedure OWNER_TO_WHO (
4 X_OWNER in VARCHAR2,
5 X_CREATION_DATE out nocopy DATE,
6 X_CREATED_BY out nocopy NUMBER,
7 X_LAST_UPDATE_DATE out nocopy DATE,
8 X_LAST_UPDATED_BY out nocopy NUMBER,
9 X_LAST_UPDATE_LOGIN out nocopy NUMBER
10 ) is
11 begin
12 if X_OWNER = 'SEED' then
13 X_CREATED_BY := 1;
14 X_LAST_UPDATED_BY := 1;
15 else
16 X_CREATED_BY := 0;
17 X_LAST_UPDATED_BY := 0;
18 end if;
19 X_CREATION_DATE := sysdate;
20 X_LAST_UPDATE_DATE := sysdate;
21 X_LAST_UPDATE_LOGIN := 0;
22 end OWNER_TO_WHO;
23
24 PROCEDURE Insert_Row(X_Rowid IN OUT nocopy VARCHAR2,
25 X_Nav_Unit_Id IN OUT nocopy NUMBER,
26 X_Default_Workflow_Id NUMBER,
27 X_Application_Abbrev VARCHAR2,
28 X_Default_Label VARCHAR2,
29 X_Form_Name VARCHAR2,
30 X_Max_Number_Of_Nav_Buttons NUMBER,
31 X_Block_Name VARCHAR2,
32 X_LANGUAGE_CODE in varchar2 default hr_api.userenv_lang
33 ) IS
34 CURSOR C IS SELECT rowid FROM HR_NAVIGATION_UNITS
35
36 WHERE nav_unit_id = X_Nav_Unit_Id;
37
38 CURSOR C2 IS SELECT hr_navigation_units_s.nextval FROM sys.dual;
39 l_language_code varchar2(3);
40 BEGIN
41
42 -- Validate the language parameter. l_language_code should be passed
43 -- instead of p_language_code from now on, to allow an IN OUT parameter to
44 -- be passed through.
45 --
46 l_language_code := x_language_code;
47 hr_api.validate_language_code(p_language_code => l_language_code);
48
49 if (X_Nav_Unit_Id is NULL) then
50 OPEN C2;
51 FETCH C2 INTO X_Nav_Unit_Id;
52 CLOSE C2;
53 end if;
54 INSERT INTO HR_NAVIGATION_UNITS(
55 nav_unit_id,
56 default_workflow_id,
57 application_abbrev,
58 default_label,
59 form_name,
60 max_number_of_nav_buttons,
61 block_name
62 ) VALUES (
63 X_Nav_Unit_Id,
64 X_Default_Workflow_Id,
65 X_Application_Abbrev,
66 X_Default_Label,
67 X_Form_Name,
68 X_Max_Number_Of_Nav_Buttons,
69 X_Block_Name
70 );
71
72 INSERT INTO HR_NAVIGATION_UNITS_TL(
73 nav_unit_id,
74 default_label,
75 language,
76 source_lang)
77 select
78 X_Nav_Unit_Id,
79 x_default_label,
80 l.language_code,
81 userenv('LANG')
82 from FND_LANGUAGES L
83 where L.INSTALLED_FLAG in ('I', 'B')
84 and not exists
85 (select NULL
86 from HR_NAVIGATION_UNITS_TL T
87 where T.NAV_UNIT_ID = X_NAV_UNIT_ID
88 and T.LANGUAGE = L.LANGUAGE_CODE);
89
90 OPEN C;
91 FETCH C INTO X_Rowid;
92 if (C%NOTFOUND) then
93 CLOSE C;
94 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
95 hr_utility.set_message_token('PROCEDURE','INSERT_ROW');
96 hr_utility.set_message_token('STEP','1');
97 hr_utility.raise_error;
98 end if;
99 CLOSE C;
100 END Insert_Row;
101
102 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
103 X_Nav_Unit_Id NUMBER,
104 X_Default_Workflow_Id NUMBER,
105 X_Application_Abbrev VARCHAR2,
106 X_Default_Label VARCHAR2,
107 X_Form_Name VARCHAR2,
108 X_Max_Number_Of_Nav_Buttons NUMBER,
109 X_Block_Name VARCHAR2
110 ) IS
111 CURSOR C IS
112 SELECT *
113 FROM HR_NAVIGATION_UNITS
114 WHERE rowid = X_Rowid
115 FOR UPDATE of Nav_Unit_Id NOWAIT;
116 Recinfo C%ROWTYPE;
117
118 cursor CSR_HR_NAVIGATION_UNITS_TL is
119 select DEFAULT_LABEL,
120 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
121 from HR_NAVIGATION_UNITS_TL TL
122 where nav_unit_id = x_nav_unit_id
123 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
124 for update of nav_unit_id nowait;
125
126 BEGIN
127 OPEN C;
128 FETCH C INTO Recinfo;
129 if (C%NOTFOUND) then
130 CLOSE C;
131 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
132 hr_utility.set_message_token('PROCEDURE','LOCK_ROW');
133 hr_utility.set_message_token('STEP','1');
134 hr_utility.raise_error;
135 end if;
136 CLOSE C;
137
138 Recinfo.default_label := rtrim(Recinfo.default_label);
139 Recinfo.form_name := rtrim(Recinfo.form_name);
140 Recinfo.block_name := rtrim(Recinfo.block_name);
141
142 if (
143 ( (Recinfo.nav_unit_id = X_Nav_Unit_Id)
144 OR ( (Recinfo.nav_unit_id IS NULL)
145 AND (X_Nav_Unit_Id IS NULL)))
146 AND ( (Recinfo.default_workflow_id = X_Default_Workflow_Id)
147 OR ( (Recinfo.default_workflow_id IS NULL)
148 AND (X_Default_Workflow_Id IS NULL)))
149 AND ( (Recinfo.application_abbrev = X_Application_Abbrev)
150 OR ( (Recinfo.application_abbrev IS NULL)
151 AND (X_Application_Abbrev IS NULL)))
152 AND ( (Recinfo.form_name = X_Form_Name)
153 OR ( (Recinfo.form_name IS NULL)
154 AND (X_Form_Name IS NULL)))
155 AND ( (Recinfo.max_number_of_nav_buttons = X_Max_Number_Of_Nav_Buttons)
156 OR ( (Recinfo.max_number_of_nav_buttons IS NULL)
157 AND (X_Max_Number_Of_Nav_Buttons IS NULL)))
158 AND ( (Recinfo.block_name = X_Block_Name)
159 OR ( (Recinfo.block_name IS NULL)
160 AND (X_Block_Name IS NULL)))
161 ) then
162 null;
163 else
164 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
165 APP_EXCEPTION.RAISE_EXCEPTION;
166 end if;
167
168 for tlinfo in CSR_HR_NAVIGATION_UNITS_TL loop
169 if (tlinfo.BASELANG = 'Y') then
170 if ( ((tlinfo.DEFAULT_LABEL = X_DEFAULT_LABEL)
171 OR ((tlinfo.DEFAULT_LABEL is null) AND (X_DEFAULT_LABEL is null)))
172 ) then
173 null;
174 else
175 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
176 app_exception.raise_exception;
177 end if;
178 end if;
179 end loop;
180 return;
181 END Lock_Row;
182
183 -- This one is used by the form
184
185 PROCEDURE Update_Row(X_Rowid VARCHAR2,
186 X_Nav_Unit_Id NUMBER,
187 X_Default_Workflow_Id NUMBER,
188 X_Application_Abbrev VARCHAR2,
189 X_Default_Label VARCHAR2,
190 X_Form_Name VARCHAR2,
191 X_Max_Number_Of_Nav_Buttons NUMBER,
192 X_Block_Name VARCHAR2,
193 X_Language_Code varchar2 default hr_api.userenv_lang
194 )
195 IS
196 l_language_code varchar2(3);
197 BEGIN
198
199 -- Validate the language parameter. l_language_code should be passed
200 -- instead of x_language_code from now on, to allow an IN OUT parameter to
201 -- be passed through.
202 --
203 l_language_code := x_language_code;
204 hr_api.validate_language_code(p_language_code => l_language_code);
205
206 UPDATE HR_NAVIGATION_UNITS
207 SET
208 nav_unit_id = X_Nav_Unit_Id,
209 default_workflow_id = X_Default_Workflow_Id,
210 application_abbrev = X_Application_Abbrev,
211 default_label = X_Default_Label,
212 form_name = X_Form_Name,
213 max_number_of_nav_buttons = X_Max_Number_Of_Nav_Buttons,
214 block_name = X_Block_Name
215 WHERE rowid = X_rowid;
216
217 if (SQL%NOTFOUND) then
218 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
219 hr_utility.set_message_token('PROCEDURE','UPDATE_ROW');
220 hr_utility.set_message_token('STEP','1');
221 hr_utility.raise_error;
222 end if;
223
224 update HR_NAVIGATION_UNITS_TL
225 set
226 DEFAULT_LABEL = X_DEFAULT_LABEL,
227 SOURCE_LANG = userenv('LANG')
228 where NAV_UNIT_ID = X_NAV_UNIT_ID
229 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
230
231 if (sql%notfound) then
232 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
233 hr_utility.set_message_token('PROCEDURE','UPDATE_ROW');
234 hr_utility.set_message_token('STEP','1');
235 hr_utility.raise_error;
236 end if;
237
238 END Update_Row;
239
240 -- Overloaded procedure
241 -- This one is used by loader configuration file
242
243 Procedure UPDATE_ROW (X_NAV_UNIT_ID NUMBER,
244 X_DEFAULT_WORKFLOW_ID NUMBER,
245 X_APPLICATION_ABBREV VARCHAR2,
246 X_DEFAULT_LABEL VARCHAR2,
247 X_FORM_NAME VARCHAR2,
248 X_MAX_NUMBER_OF_NAV_BUTTONS NUMBER,
249 X_BLOCK_NAME VARCHAR2,
250 X_LANGUAGE_CODE in varchar2 default hr_api.userenv_lang
251 ) is
252 l_language_code varchar2(3);
253 begin
254
255 -- Validate the language parameter. l_language_code should be passed
256 -- instead of x_language_code from now on, to allow an IN OUT parameter to
257 -- be passed through.
258 --
259 l_language_code := x_language_code;
260 hr_api.validate_language_code(p_language_code => l_language_code);
261
262
263 update HR_NAVIGATION_UNITS set
264 DEFAULT_WORKFLOW_ID = X_DEFAULT_WORKFLOW_ID,
265 APPLICATION_ABBREV = X_APPLICATION_ABBREV,
266 DEFAULT_LABEL = X_DEFAULT_LABEL,
267 FORM_NAME = X_FORM_NAME,
268 MAX_NUMBER_OF_NAV_BUTTONS = X_MAX_NUMBER_OF_NAV_BUTTONS,
269 BLOCK_NAME = X_BLOCK_NAME
270 where NAV_UNIT_ID = X_NAV_UNIT_ID;
271
272 if (sql%notfound) then
273 raise no_data_found;
274 end if;
275
276 update HR_NAVIGATION_UNITS_TL
277 set
278 DEFAULT_LABEL = X_DEFAULT_LABEL,
279 SOURCE_LANG = userenv('LANG')
280 where NAV_UNIT_ID = X_NAV_UNIT_ID
281 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
282
283 if (sql%notfound) then
284 raise no_data_found;
285 end if;
286
287 end UPDATE_ROW;
288
289 PROCEDURE Delete_Row(x_nav_unit_id varchar2, X_Rowid VARCHAR2) IS
290 BEGIN
291 delete from HR_NAVIGATION_UNITS_TL
292 where NAV_UNIT_ID = X_NAV_UNIT_ID;
293
294 if (sql%notfound) then
295 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
296 hr_utility.set_message_token('PROCEDURE','DELETE_ROW');
297 hr_utility.set_message_token('STEP','1');
298 hr_utility.raise_error;
299 end if;
300
301 DELETE FROM HR_NAVIGATION_UNITS
302 WHERE rowid = X_Rowid;
303
304 if (SQL%NOTFOUND) then
305 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
306 hr_utility.set_message_token('PROCEDURE','DELETE_ROW');
307 hr_utility.set_message_token('STEP','1');
308 hr_utility.raise_error;
309 end if;
310 END Delete_Row;
311
312 procedure ADD_LANGUAGE
313 is
314 begin
315 delete from HR_NAVIGATION_UNITS_TL T
316 where not exists
317 (select NULL
318 from HR_NAVIGATION_UNITS B
319 where B.NAV_UNIT_ID = T.NAV_UNIT_ID
320 );
321
322 update HR_NAVIGATION_UNITS_TL T set (
323 DEFAULT_LABEL
324 ) = (select
325 B.DEFAULT_LABEL
326 from HR_NAVIGATION_UNITS_TL B
327 where B.NAV_UNIT_ID = T.NAV_UNIT_ID
328 and B.LANGUAGE = T.SOURCE_LANG)
329 where (
330 T.NAV_UNIT_ID,
331 T.LANGUAGE
332 ) in (select
333 SUBT.NAV_UNIT_ID,
334 SUBT.LANGUAGE
335 from HR_NAVIGATION_UNITS_TL SUBB, HR_NAVIGATION_UNITS_TL SUBT
336 where SUBB.NAV_UNIT_ID = SUBT.NAV_UNIT_ID
337 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
338 and (SUBB.DEFAULT_LABEL <> SUBT.DEFAULT_LABEL
339 or (SUBB.DEFAULT_LABEL is null and SUBT.DEFAULT_LABEL is not null)
340 or (SUBB.DEFAULT_LABEL is not null and SUBT.DEFAULT_LABEL is null)
341 ));
342
343 insert into HR_NAVIGATION_UNITS_TL (
344 NAV_UNIT_ID,
345 DEFAULT_LABEL,
346 LANGUAGE,
347 SOURCE_LANG
348 ) select
349 B.NAV_UNIT_ID,
350 B.DEFAULT_LABEL,
351 L.LANGUAGE_CODE,
352 B.SOURCE_LANG
353 from HR_NAVIGATION_UNITS_TL B, FND_LANGUAGES L
354 where L.INSTALLED_FLAG in ('I', 'B')
355 and B.LANGUAGE = userenv('LANG')
356 and not exists
357 (select NULL
358 from HR_NAVIGATION_UNITS_TL T
359 where T.NAV_UNIT_ID = B.NAV_UNIT_ID
360 and T.LANGUAGE = L.LANGUAGE_CODE);
361
362 end ADD_LANGUAGE;
363
364 procedure LOAD_ROW(
365 X_FORM_NAME in VARCHAR2,
366 X_BLOCK_NAME in VARCHAR2,
367 X_WORKFLOW_NAME in VARCHAR2,
368 X_APPLICATION_ABBREV in VARCHAR2,
369 X_DEFAULT_LABEL in VARCHAR2,
370 X_MAX_NUMBER_OF_NAV_BUTTONS in VARCHAR2
371 ) is
372 X_NAV_UNIT_ID NUMBER;
373 X_ROWID VARCHAR2(30);
374 X_DEFAULT_WORKFLOW_ID NUMBER;
375 X_LANGUAGE_CODE VARCHAR2(30);
376 Y_DEFAULT_WORKFLOW_ID NUMBER;
377 Y_APPLICATION_ABBREV VARCHAR2(3);
378 Y_DEFAULT_LABEL VARCHAR2(40);
379 Y_MAX_NUMBER_OF_NAV_BUTTONS NUMBER;
380 begin
381
382 if hr_workflows_pkg.g_load_taskflow <> 'N' then
383
384 if X_WORKFLOW_NAME is not null then
385
386 select workflow_id
387 into X_DEFAULT_WORKFLOW_ID
388 from hr_workflows
389 where workflow_name = X_WORKFLOW_NAME;
390
391 else
392 X_DEFAULT_WORKFLOW_ID := null;
393 end if;
394
395 begin
396 select NAV_UNIT_ID,DEFAULT_WORKFLOW_ID, APPLICATION_ABBREV, DEFAULT_LABEL,MAX_NUMBER_OF_NAV_BUTTONS
397 into X_NAV_UNIT_ID, Y_DEFAULT_WORKFLOW_ID, Y_APPLICATION_ABBREV,
398 Y_DEFAULT_LABEL,Y_MAX_NUMBER_OF_NAV_BUTTONS
399 from hr_navigation_units
400 where FORM_NAME = X_FORM_NAME
401 and nvl(block_name,hr_api.g_varchar2) = nvl(x_block_name,hr_api.g_varchar2);
402
403 --
404 -- Fix for bug 3274423 starts here.
405 -- Before updating the record, compare the database row with the row in ldt file.
406 -- If both are same skip updating.
407 --
408
409 IF X_DEFAULT_WORKFLOW_ID <> Y_DEFAULT_WORKFLOW_ID OR
410 X_APPLICATION_ABBREV <> Y_APPLICATION_ABBREV OR
411 X_DEFAULT_LABEL <> Y_DEFAULT_LABEL OR
412 X_MAX_NUMBER_OF_NAV_BUTTONS <> Y_MAX_NUMBER_OF_NAV_BUTTONS THEN
413 UPDATE_ROW(
414 X_NAV_UNIT_ID,
415 X_DEFAULT_WORKFLOW_ID,
416 X_APPLICATION_ABBREV,
417 X_DEFAULT_LABEL,
418 X_FORM_NAME,
419 X_MAX_NUMBER_OF_NAV_BUTTONS,
420 X_BLOCK_NAME
421 );
422 END IF;
423
424 exception
425 when no_data_found then
426 select HR_NAVIGATION_UNITS_S.NEXTVAL
427 into X_NAV_UNIT_ID
428 from dual;
429
430 INSERT_ROW(
431 X_ROWID,
432 X_NAV_UNIT_ID,
433 X_DEFAULT_WORKFLOW_ID,
434 X_APPLICATION_ABBREV,
435 X_DEFAULT_LABEL,
436 X_FORM_NAME,
437 X_MAX_NUMBER_OF_NAV_BUTTONS,
438 X_BLOCK_NAME
439 );
440 end;
441 --
442 -- Fix for bug 3274423 ends here.
443 --
444 end if;
445
446 end LOAD_ROW;
447
448 procedure TRANSLATE_ROW (
449 X_FORM_NAME in VARCHAR2,
450 X_BLOCK_NAME in VARCHAR2,
451 X_DEFAULT_LABEL in VARCHAR2
452 ) is
453 X_NAV_UNIT_ID NUMBER;
454 Y_DEFAULT_LABEL varchar2(40);
455 Y_SOURCE_LANG varchar2(4);
456 --
457 Cursor database_row(p_nav_unit_id number) IS
458 SELECT DEFAULT_LABEL, SOURCE_LANG
459 FROM HR_NAVIGATION_UNITS_TL
460 WHERE userenv('LANG') in (LANGUAGE,SOURCE_LANG)
461 AND nav_unit_id = p_nav_unit_id;
462 --
463 begin
464 select nav_unit_id
465 into x_nav_unit_id
466 from hr_navigation_units
467 where FORM_NAME = X_FORM_NAME
468 and ( (BLOCK_NAME = X_BLOCK_NAME)
469 or (BLOCK_NAME is null and X_BLOCK_NAME is null));
470
471 --
472 -- Fix for bug 3326118 starts here.
473 --
474 open database_row(x_nav_unit_id );
475 fetch database_row into Y_DEFAULT_LABEL, Y_SOURCE_LANG;
476 if database_row%found then
477 if X_DEFAULT_LABEL <> Y_DEFAULT_LABEL OR
478 Y_SOURCE_LANG <> userenv('LANG') THEN
479
480 update HR_NAVIGATION_UNITS_TL
481 set DEFAULT_LABEL = X_DEFAULT_LABEL,
482 SOURCE_LANG = userenv('LANG')
483 where userenv('LANG') in (LANGUAGE,SOURCE_LANG)
484 and nav_unit_id = x_nav_unit_id;
485 end if;
486 end if;
487 close database_row;
488 --
489 -- Fix for bug 3326118 ends here.
490 --
491 --
492 -- Fix for bug 4132782 starts here. Added exception handler.
493 --
494 exception
495 when no_data_found then
496 null;
497 when others then
498 raise;
499 --
500 -- Fix for bug 4132782 ends here.
501 --
502 end TRANSLATE_ROW;
503
504
505 END HR_NAVIGATION_UNITS_PKG;
[Home] [Help]