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