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