1 package body HR_NAVIGATION_PATHS_PKG as
2 /* $Header: hrnvplct.pkb 120.3 2011/05/20 05:36:12 vepravee ship $ */
3
4 procedure OWNER_TO_WHO (
5 X_OWNER in VARCHAR2,
6 X_CREATION_DATE out nocopy DATE,
7 X_CREATED_BY out nocopy NUMBER,
8 X_LAST_UPDATE_DATE out nocopy DATE,
9 X_LAST_UPDATED_BY out nocopy NUMBER,
10 X_LAST_UPDATE_LOGIN out nocopy NUMBER
11 ) is
12 begin
13 if X_OWNER = 'SEED' then
14 X_CREATED_BY := 1;
15 X_LAST_UPDATED_BY := 1;
16 else
17 X_CREATED_BY := 0;
18 X_LAST_UPDATED_BY := 0;
19 end if;
20 X_CREATION_DATE := sysdate;
21 X_LAST_UPDATE_DATE := sysdate;
22 X_LAST_UPDATE_LOGIN := 0;
23 end OWNER_TO_WHO;
24
25 procedure INSERT_ROW (
26 X_ROWID in out nocopy VARCHAR2,
27 X_NAV_PATH_ID in NUMBER,
28 X_FROM_NAV_NODE_USAGE_ID in NUMBER,
29 X_TO_NAV_NODE_USAGE_ID in NUMBER,
30 X_NAV_BUTTON_REQUIRED in VARCHAR2,
31 X_SEQUENCE in NUMBER,
32 X_OVERRIDE_LABEL in VARCHAR2,
33 X_LANGUAGE_CODE in varchar2 default hr_api.userenv_lang
34 ) is
35 l_language_code varchar2(3);
36 cursor C is select ROWID from HR_NAVIGATION_PATHS
37 where NAV_PATH_ID = X_NAV_PATH_ID
38 ;
39 begin
40 -- Validate the language parameter. l_language_code should be passed
41 -- instead of x_language_code from now on, to allow an IN OUT parameter to
42 -- be passed through.
43 --
44 l_language_code := x_language_code;
45 hr_api.validate_language_code(p_language_code => l_language_code);
46
47 --
48 -- Added the following code as a part of Zero Downtime Patching Project.
49 -- Code Starts Here.
50 --
51
52 /* Commented and Added as a part of fix for bug#12554709 Starts
53 BEGIN
54 PER_RIC_PKG.chk_integrity (
55 p_entity_name => 'HR_NAVIGATION_PATHS',
56 p_ref_entity => 'HR_NAVIGATION_NODE_USAGES',
57 p_ref_column_info => PER_RIC_PKG.column_info_tbl(
58 PER_RIC_PKG.col_info_rec('NAV_NODE_USAGE_ID',NULL,X_FROM_NAV_NODE_USAGE_ID,NULL) ,
59 PER_RIC_PKG.col_info_rec('NAV_NODE_USAGE_ID',NULL,X_TO_NAV_NODE_USAGE_ID,NULL)
60 ),
61 p_ref_type => 'INS');
62
63 END;*/
64
65 BEGIN
66 PER_RIC_PKG.chk_integrity (
67 p_entity_name => 'HR_NAVIGATION_PATHS',
68 p_ref_entity_info => PER_RIC_PKG.ref_entity_tbl(
69 PER_RIC_PKG.ref_info_rec('HR_NAVIGATION_NODE_USAGES', PER_RIC_PKG.column_info_tbl(
70 PER_RIC_PKG.col_info_rec('NAV_NODE_USAGE_ID',NULL,X_FROM_NAV_NODE_USAGE_ID,NULL))),
71 PER_RIC_PKG.ref_info_rec('HR_NAVIGATION_NODE_USAGES', PER_RIC_PKG.column_info_tbl(
72 PER_RIC_PKG.col_info_rec('NAV_NODE_USAGE_ID',NULL,X_TO_NAV_NODE_USAGE_ID,NULL)))
73 ),
74 p_ref_type => 'INS');
75
76 END;
77
78 /*Commented and Added as a part of fix for bug#12554709 Ends */
79
80 --
81 -- Code Ends Here.
82 --
83
84 insert into HR_NAVIGATION_PATHS (
85 NAV_PATH_ID,
86 FROM_NAV_NODE_USAGE_ID,
87 TO_NAV_NODE_USAGE_ID,
88 NAV_BUTTON_REQUIRED,
89 SEQUENCE,
90 OVERRIDE_LABEL
91 ) values (
92 X_NAV_PATH_ID,
93 X_FROM_NAV_NODE_USAGE_ID,
94 X_TO_NAV_NODE_USAGE_ID,
95 X_NAV_BUTTON_REQUIRED,
96 X_SEQUENCE,
97 X_OVERRIDE_LABEL
98 );
99
100
101 --
102 -- Added the following code as a part of Zero Downtime Patching Project.
103 -- Code Starts Here.
104 --
105
106 BEGIN
107 PER_RIC_PKG.chk_integrity (
108 p_entity_name => 'HR_NAVIGATION_PATHS_TL',
109 p_ref_entity => 'HR_NAVIGATION_PATHS',
110 p_ref_column_name => 'NAV_PATH_ID',
111 p_ref_col_value_number => X_NAV_PATH_ID,
112 p_ref_col_value_varchar => NULL,
113 p_ref_col_value_date => NULL,
114 p_ref_type => 'INS');
115
116 END;
117 --
118 -- Code Ends Here.
119 --
120
121 INSERT INTO HR_NAVIGATION_PATHS_TL(
122 nav_path_id,
123 override_label,
124 language,
125 source_lang)
126 select
127 X_Nav_Path_Id,
128 x_override_label,
129 l.language_code,
130 userenv('LANG')
131 from FND_LANGUAGES L
132 where L.INSTALLED_FLAG in ('I', 'B')
133 and not exists
134 (select NULL
135 from HR_NAVIGATION_PATHS_TL T
136 where T.NAV_PATH_ID = X_NAV_PATH_ID
137 and T.LANGUAGE = L.LANGUAGE_CODE);
138
139 open c;
140 fetch c into X_ROWID;
141 if (c%notfound) then
142 close c;
143 raise no_data_found;
144 end if;
145 close c;
146
147 end INSERT_ROW;
148
149 procedure LOCK_ROW (
150 X_NAV_PATH_ID in NUMBER,
151 X_FROM_NAV_NODE_USAGE_ID in NUMBER,
152 X_TO_NAV_NODE_USAGE_ID in NUMBER,
153 X_NAV_BUTTON_REQUIRED in VARCHAR2,
154 X_SEQUENCE in NUMBER,
155 X_OVERRIDE_LABEL in VARCHAR2
156 ) is
157 cursor c1 is select
158 FROM_NAV_NODE_USAGE_ID,
159 TO_NAV_NODE_USAGE_ID,
160 NAV_BUTTON_REQUIRED,
161 SEQUENCE,
162 OVERRIDE_LABEL
163 from HR_NAVIGATION_PATHS
164 where NAV_PATH_ID = X_NAV_PATH_ID
165 for update of NAV_PATH_ID nowait;
166
167 cursor CSR_HR_NAVIGATION_PATHS_TL is
168 select OVERRIDE_LABEL,
169 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
170 from HR_NAVIGATION_PATHS_TL TL
171 where nav_path_id = x_nav_path_id
172 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
173 for update of nav_path_id nowait;
174
175 begin
176 for tlinfo in c1 loop
177 if (
178 (tlinfo.FROM_NAV_NODE_USAGE_ID = X_FROM_NAV_NODE_USAGE_ID)
179 AND (tlinfo.TO_NAV_NODE_USAGE_ID = X_TO_NAV_NODE_USAGE_ID)
180 AND (tlinfo.NAV_BUTTON_REQUIRED = X_NAV_BUTTON_REQUIRED)
181 AND (tlinfo.SEQUENCE = X_SEQUENCE)
182 ) then
183 null;
184 else
185 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
186 app_exception.raise_exception;
187 end if;
188 end loop;
189
190 for tlinf in CSR_HR_NAVIGATION_PATHS_TL loop
191 if (tlinf.BASELANG = 'Y') then
192 if ( ((tlinf.OVERRIDE_LABEL = X_OVERRIDE_LABEL)
193 OR ((tlinf.OVERRIDE_LABEL is null) AND (X_OVERRIDE_LABEL is null)))
194 ) then
195 null;
196 else
197 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
198 app_exception.raise_exception;
199 end if;
200 end if;
201 end loop;
202
203 return;
204 end LOCK_ROW;
205
206 procedure UPDATE_ROW (
207 X_NAV_PATH_ID in NUMBER,
208 X_FROM_NAV_NODE_USAGE_ID in NUMBER,
209 X_TO_NAV_NODE_USAGE_ID in NUMBER,
210 X_NAV_BUTTON_REQUIRED in VARCHAR2,
211 X_SEQUENCE in NUMBER,
212 X_OVERRIDE_LABEL in VARCHAR2,
213 X_LANGUAGE_CODE in varchar2 default hr_api.userenv_lang
214 ) is
215 l_language_code varchar2(3);
216 begin
217 -- Validate the language parameter. l_language_code should be passed
218 -- instead of x_language_code from now on, to allow an IN OUT parameter to
219 -- be passed through.
220 --
221 l_language_code := x_language_code;
222 hr_api.validate_language_code(p_language_code => l_language_code);
223
224 update HR_NAVIGATION_PATHS set
225 FROM_NAV_NODE_USAGE_ID = X_FROM_NAV_NODE_USAGE_ID,
226 TO_NAV_NODE_USAGE_ID = X_TO_NAV_NODE_USAGE_ID,
227 NAV_BUTTON_REQUIRED = X_NAV_BUTTON_REQUIRED,
228 SEQUENCE = X_SEQUENCE,
229 OVERRIDE_LABEL = X_OVERRIDE_LABEL
230 where NAV_PATH_ID = X_NAV_PATH_ID;
231
232 if (sql%notfound) then
233 raise no_data_found;
234 end if;
235
236 update HR_NAVIGATION_PATHS_TL
237 set
238 OVERRIDE_LABEL = X_OVERRIDE_LABEL,
239 SOURCE_LANG = userenv('LANG')
240 where NAV_PATH_ID = X_NAV_PATH_ID
241 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
242
243 if (sql%notfound) then
244 raise no_data_found;
245 end if;
246
247 end UPDATE_ROW;
248
249 procedure DELETE_ROW (
250 X_NAV_PATH_ID in NUMBER
251 ) is
252 begin
253
254 delete from HR_NAVIGATION_PATHS_TL
255 where NAV_PATH_ID = X_NAV_PATH_ID;
256
257 if (sql%notfound) then
258 raise no_data_found;
259 end if;
260
261 delete from HR_NAVIGATION_PATHS
262 where NAV_PATH_ID = X_NAV_PATH_ID;
263
264 if (sql%notfound) then
265 raise no_data_found;
266 end if;
267
268 end DELETE_ROW;
269
270 procedure ADD_LANGUAGE
271 is
272 begin
273 delete from HR_NAVIGATION_PATHS_TL T
274 where not exists
275 (select NULL
276 from HR_NAVIGATION_PATHS B
277 where B.NAV_PATH_ID = T.NAV_PATH_ID
278 );
279
280 update HR_NAVIGATION_PATHS_TL T set (
281 OVERRIDE_LABEL
282 ) = (select
283 B.OVERRIDE_LABEL
284 from HR_NAVIGATION_PATHS_TL B
285 where B.NAV_PATH_ID = T.NAV_PATH_ID
286 and B.LANGUAGE = T.SOURCE_LANG)
287 where (
288 T.NAV_PATH_ID,
289 T.LANGUAGE
290 ) in (select
291 SUBT.NAV_PATH_ID,
292 SUBT.LANGUAGE
293 from HR_NAVIGATION_PATHS_TL SUBB, HR_NAVIGATION_PATHS_TL SUBT
294 where SUBB.NAV_PATH_ID = SUBT.NAV_PATH_ID
295 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
296 and (SUBB.OVERRIDE_LABEL <> SUBT.OVERRIDE_LABEL
297 or (SUBB.OVERRIDE_LABEL is null and SUBT.OVERRIDE_LABEL is not null)
298 or (SUBB.OVERRIDE_LABEL is not null and SUBT.OVERRIDE_LABEL is null)
299 ));
300
301 insert into HR_NAVIGATION_PATHS_TL (
302 NAV_PATH_ID,
303 OVERRIDE_LABEL,
304 LANGUAGE,
305 SOURCE_LANG
306 ) select
307 B.NAV_PATH_ID,
308 B.OVERRIDE_LABEL,
309 L.LANGUAGE_CODE,
310 B.SOURCE_LANG
311 from HR_NAVIGATION_PATHS_TL B, FND_LANGUAGES L
312 where L.INSTALLED_FLAG in ('I', 'B')
313 and B.LANGUAGE = userenv('LANG')
314 and not exists
315 (select NULL
316 from HR_NAVIGATION_PATHS_TL T
317 where T.NAV_PATH_ID = B.NAV_PATH_ID
318 and T.LANGUAGE = L.LANGUAGE_CODE);
319 end ADD_LANGUAGE;
320
321 procedure LOAD_ROW(
322 X_WORKFLOW_NAME in VARCHAR2,
323 X_NODE_NAME_FROM in VARCHAR2,
324 X_NODE_NAME_TO in VARCHAR2,
325 X_NAV_BUTTON_REQUIRED in VARCHAR2,
326 X_SEQUENCE in VARCHAR2,
327 X_OVERRIDE_LABEL in VARCHAR2,
328 X_NVP_FLAG in VARCHAR2
329 ) is
330 X_WORKFLOW_ID NUMBER;
331 X_FROM_NAV_NODE_ID NUMBER;
332 X_TO_NAV_NODE_ID NUMBER;
333 X_NAV_PATH_ID NUMBER;
334 X_ROWID VARCHAR2(30);
335 X_FROM_NAV_NODE_USAGE_ID NUMBER;
336 X_TO_NAV_NODE_USAGE_ID NUMBER;
337 l_flag varchar2(1) := 'Y';
338 begin
339
340 -- Note that for navigation paths, the upload will fail if either
341 -- of the nav_node_usage_id's have not been extracted in the download or are
342 -- not already present on the remote site. This can happen because a
343 -- navigation node usage can exist across taskflows and therefore need not
344 -- be extracted for a particular taskflow. However, to ensure that
345 -- this does not stop the data upload on the remote site, the uploader
346 -- traps and surpresses any error raised because of this. Since the
347 -- downloader downloads for the occurrence of navigation node usage in both
348 -- from and to nav_node_usage_id columns, the relevant records will get
349 -- populated when the other navigation node usage is being loaded.
350 -- x_nav_flag is used to raise an application error if no data is
351 -- found when 'to' navigation node usages are being handled. The l_flag
352 -- is used to surpress errors when 'from' navigtion node usages are being
353 -- handled.
354
355
356 if hr_workflows_pkg.g_load_taskflow <> 'N' then
357 l_flag := 'Y';
358
359 select WORKFLOW_ID
360 into X_WORKFLOW_ID
361 from HR_WORKFLOWS
362 where WORKFLOW_NAME = X_WORKFLOW_NAME;
363
364 begin
365
366 select NAV_NODE_ID
367 into X_FROM_NAV_NODE_ID
368 from HR_NAVIGATION_NODES
369 where NAME = X_NODE_NAME_FROM;
370
371 select NAV_NODE_USAGE_ID
372 into X_FROM_NAV_NODE_USAGE_ID
373 from HR_NAVIGATION_NODE_USAGES
374 where WORKFLOW_ID = X_WORKFLOW_ID
375 and NAV_NODE_ID = X_FROM_NAV_NODE_ID;
376
377 exception
378 when no_data_found then
379 if x_nvp_flag = 'FROM' then
380 raise;
381 else
382 l_flag := 'N';
383 end if;
384 end;
385
386 begin
387
388 select NAV_NODE_ID
389 into X_TO_NAV_NODE_ID
390 from HR_NAVIGATION_NODES
391 where NAME = X_NODE_NAME_TO;
392
393 select NAV_NODE_USAGE_ID
394 into X_TO_NAV_NODE_USAGE_ID
395 from HR_NAVIGATION_NODE_USAGES
396 where WORKFLOW_ID = X_WORKFLOW_ID
397 and NAV_NODE_ID = X_TO_NAV_NODE_ID;
398
399 exception
400 when no_data_found then
401 if x_nvp_flag = 'TO' then
402 raise;
403 else
404 l_flag := 'N';
405 end if;
406 end;
407
408 if l_flag = 'Y' then
409 begin
410 select NAV_PATH_ID
411 into X_NAV_PATH_ID
412 from HR_NAVIGATION_PATHS
413 where FROM_NAV_NODE_USAGE_ID = X_FROM_NAV_NODE_USAGE_ID
414 and TO_NAV_NODE_USAGE_ID = X_TO_NAV_NODE_USAGE_ID;
415 exception
416 when no_data_found then
417 select HR_NAVIGATION_PATHS_S.NEXTVAL
418 into X_NAV_PATH_ID
419 from dual;
420 end;
421
422 begin
423 UPDATE_ROW(
424 X_NAV_PATH_ID,
425 X_FROM_NAV_NODE_USAGE_ID,
426 X_TO_NAV_NODE_USAGE_ID,
427 X_NAV_BUTTON_REQUIRED,
428 X_SEQUENCE,
429 X_OVERRIDE_LABEL
430 );
431 exception
432 when no_data_found then
433 INSERT_ROW(
434 X_ROWID,
435 X_NAV_PATH_ID,
436 X_FROM_NAV_NODE_USAGE_ID,
437 X_TO_NAV_NODE_USAGE_ID,
438 X_NAV_BUTTON_REQUIRED,
439 X_SEQUENCE,
440 X_OVERRIDE_LABEL
441 );
442 end;
443
444 end if;
445
446 end if;
447
448 end LOAD_ROW;
449
450 procedure TRANSLATE_ROW (
451 X_WORKFLOW_NAME in VARCHAR2,
452 X_NODE_NAME_FROM in VARCHAR2,
453 X_NODE_NAME_TO in VARCHAR2,
454 X_OVERRIDE_LABEL in VARCHAR2,
455 X_NVP_LABEL in VARCHAR2
456 ) is
457 X_WORKFLOW_ID NUMBER;
458 X_FROM_NAV_NODE_ID NUMBER;
459 X_TO_NAV_NODE_ID NUMBER;
460 X_NAV_PATH_ID NUMBER;
461 X_ROWID VARCHAR2(30);
462 X_FROM_NAV_NODE_USAGE_ID NUMBER;
463 X_TO_NAV_NODE_USAGE_ID NUMBER;
464 l_flag varchar2(1) := 'Y';
465 x_nvp_flag varchar2(30) := x_nvp_label;
466 begin
467
468
469 l_flag := 'Y';
470
471 select WORKFLOW_ID
472 into X_WORKFLOW_ID
473 from HR_WORKFLOWS
474 where WORKFLOW_NAME = X_WORKFLOW_NAME;
475
476 begin
477
478 select NAV_NODE_ID
479 into X_FROM_NAV_NODE_ID
480 from HR_NAVIGATION_NODES
481 where NAME = X_NODE_NAME_FROM;
482
483 select NAV_NODE_USAGE_ID
484 into X_FROM_NAV_NODE_USAGE_ID
485 from HR_NAVIGATION_NODE_USAGES
486 where WORKFLOW_ID = X_WORKFLOW_ID
487 and NAV_NODE_ID = X_FROM_NAV_NODE_ID;
488
489 exception
490 when no_data_found then
491 if x_nvp_flag = 'FROM' then
492 raise;
493 else
494 l_flag := 'N';
495 end if;
496 end;
497
498 begin
499
500 select NAV_NODE_ID
501 into X_TO_NAV_NODE_ID
502 from HR_NAVIGATION_NODES
503 where NAME = X_NODE_NAME_TO;
504
505 select NAV_NODE_USAGE_ID
506 into X_TO_NAV_NODE_USAGE_ID
507 from HR_NAVIGATION_NODE_USAGES
508 where WORKFLOW_ID = X_WORKFLOW_ID
509 and NAV_NODE_ID = X_TO_NAV_NODE_ID;
510
511 exception
512 when no_data_found then
513 if x_nvp_flag = 'TO' then
514 raise;
515 else
516 l_flag := 'N';
517 end if;
518 end;
519
520 if l_flag = 'Y' then
521
522 begin
523 select NAV_PATH_ID
524 into X_NAV_PATH_ID
525 from HR_NAVIGATION_PATHS
526 where FROM_NAV_NODE_USAGE_ID = X_FROM_NAV_NODE_USAGE_ID
527 and TO_NAV_NODE_USAGE_ID = X_TO_NAV_NODE_USAGE_ID;
528 exception
529 when no_data_found then
530 select HR_NAVIGATION_PATHS_S.NEXTVAL
531 into X_NAV_PATH_ID
532 from dual;
533 end;
534
535 begin
536 update HR_NAVIGATION_PATHS_TL
537 set OVERRIDE_LABEL = X_OVERRIDE_LABEL,
538 SOURCE_LANG = userenv('LANG')
539 where userenv('LANG') in (LANGUAGE,SOURCE_LANG)
540 and nav_path_id = x_nav_path_id;
541 end;
542
543 end if;
544 -- Fix for Bug 4109347 starts here. Added exception block.
545 exception
546 when no_data_found then
547 null;
548 when others then
549 raise;
550 -- Fix for bug 4109347 ends here.
551 end TRANSLATE_ROW;
552
553 end HR_NAVIGATION_PATHS_PKG;
[Home] [Help]