1 package body hr_history as
2 /* $Header: dthistry.pkb 115.6 2004/01/28 01:51:32 sxshah ship $ */
3 /*
4 --
5 -- Copyright (c) Oracle Corporation 1991, 1992, 1993. All rights reserved.
6 --
7 /*
8 NAME
9 dthistry.pkb -- Procedures for DateTrack History
10 --
11 DESCRIPTION
12 Procedures used by Forms4 DateTrack History (DTH)
13 --
14 MODIFIED (DD-MON-YYYY)
15 P.K.Attwood 03-JUN-1994 - created.
16 P.K.Attwood 19-JUL-1994 - Added extra check for _D view. If the _D view
17 cannot be found in user_views go onto check
18 for a synonym, as the user could be logged on
19 as a secure user.
20 P.K.Attwood 23-JAN-1996 - Fix for wwbug 295511. Added extra parameter
21 p_view_owner to the get_view_and_prompts
22 procedure. It returns the name of the Oracle
23 account which actually owns the _D view or
24 _F table. This is required for extra secure
25 user logic.
26 P.K.Attwood 08-MAY-1998 - 115.1 Changes for wwbug 658889.
27 New DateTrack History feature.
28 The Forms coder can optionally specify an
29 alternative DateTrack History view. If this
30 view name is not specified or cannot be
31 found in the database then the standard _D
32 view or _F table will be used as before.
33 To provide the required behaviour re-wrote
34 get_view_and_prompts procedure and simplified
35 the logic due to security changes in R11.0.
36 Old code specific for database version 10.5
37 and 10.6 has been removed.
38 P.K.Attwood 16-MAR-1999 - 115.2 Changes made to provide MLS for
39 DateTrack History prompts.
40 Changed table name references from
41 DT_TITLE_PROMPTS and DT_DATE_PROMPTS to
42 DT_TITLE_PROMPTS_TL and DT_DATE_PROMPTS_TL.
43 Changed the get_view_and_prompts
44 p_language_code parameter from IN to OUT.
45 Value is now derived from userenv('LANG').
46 Added find_language_code procedure.
47 M.Enderby 28-NOV-2002 - Changes to derive_view_table_names,
48 GSCC changes (bug 2620598)
49 A.Holt 05-Dec-2002 NOCOPY Performance Changes for 11.5.9
50 S.Shah 28-Jan-2004 Bug fix for 3312530 to change l_col var. */
51 --
52 -- Global Package Definitions
53 --
54 g_package varchar2(33) := ' hr_history.'; -- Global package name
55 --
56 -- Private Procedures to this package
57 --
58 -- ----------------------------------------------------------------------------
59 -- |--------------------------< find_language_code >--------------------------|
60 -- ----------------------------------------------------------------------------
61 -- {Start Of Comments}
62 --
63 -- Description:
64 -- Obtains the user's language from userenv('LANG').
65 --
66 -- Prerequisites:
67 -- None.
68 --
69 -- In Parameters:
70 -- None.
71 --
72 -- Post Success:
73 -- p_language_code will be set to the value from userenv('LANG').
74 --
75 -- Name Type Description
76 -- p_language_code varchar2 User's language.
77 --
78 -- Post Failure:
79 -- Processing stops and an error is raised if an unexpected error occurs.
80 --
81 -- Access Status:
82 -- Internal Development Use Only.
83 --
84 -- {End Of Comments}
85 --
86 procedure find_language_code
87 (p_language_code out nocopy varchar2
88 ) is
89 --
90 -- Cursor to find language
91 --
92 cursor csr_lang is
93 select userenv('LANG')
94 from dual;
95 --
96 -- Declare local variables
97 --
98 l_language_code varchar2(80);
99 l_proc varchar2(72) := g_package||'find_language_code';
100 begin
101 hr_utility.set_location('Entering:'||l_proc, 10);
102 --
103 -- Find out if the view definition exists in the current account
104 --
105 open csr_lang;
106 fetch csr_lang into l_language_code;
107 if csr_lang%notfound then
108 close csr_lang;
109 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
110 hr_utility.set_message_token('PROCEDURE', l_proc);
111 hr_utility.set_message_token('STEP','20');
112 hr_utility.raise_error;
113 end if;
114 close csr_lang;
115 --
116 p_language_code := l_language_code;
117 hr_utility.set_location(' Leaving:'||l_proc, 30);
118 end find_language_code;
119 --
120 -- ----------------------------------------------------------------------------
121 -- |------------------------< find_view_and_details >-------------------------|
122 -- ----------------------------------------------------------------------------
123 -- {Start Of Comments}
124 --
125 -- Description:
126 -- Checks if the specified view exists in the current database account.
127 -- If it exists also derived the name of the database account and the
128 -- title prompt from DT_TITLE_PROMPTS_TL.
129 --
130 -- Prerequisites:
131 -- p_view_name must be in upper case.
132 --
133 -- In Parameters:
134 -- Name Reqd Type Description
135 -- p_view_name Yes varchar2 Name of the view to find.
136 -- p_language_code Yes varchar2 The user entity title name
137 -- will be returned for the
138 -- specified language.
139 --
140 -- Post Success:
141 -- p_view_found will be set to false if the view definition does not exist
142 -- in user_views. All other OUT parameters will be null.
143 --
144 -- Name Type Description
145 -- p_view_found boolean Indicates if the view definition
146 -- was found in user_views.
147 -- p_view_owner varchar2 Name of the database account
148 -- which owns the view.
149 -- p_title_prompt varchar2 User entity name to include in
150 -- the window title prompt. Derived
151 -- from the DT_TITLE_PROMPTS_TL.
152 --
153 -- Post Failure:
154 -- Processing stops and an error is raised if an unexpected error occurs.
155 --
156 -- Access Status:
157 -- Internal Development Use Only.
158 --
159 -- {End Of Comments}
160 --
161 procedure find_view_and_details
162 (p_view_name in varchar2
163 ,p_language_code in varchar2
164 ,p_view_found out nocopy boolean
165 ,p_view_owner out nocopy varchar2
166 ,p_title_prompt out nocopy varchar2
167 ) is
168 --
169 -- Cursor to find view definition
170 --
171 cursor csr_view_exists is
172 select user
173 from user_views
174 where view_name = p_view_name;
175 --
176 -- Cursor to obtain the entity title name.
177 --
178 cursor csr_title is
179 select title_prompt
180 from dt_title_prompts_tl
181 where view_name = p_view_name
182 and language = p_language_code;
183 --
184 -- Declare local variables
185 --
186 l_view_found boolean;
187 l_view_owner varchar2(80);
188 l_title_prompt varchar2(80);
189 l_proc varchar2(72) := g_package||'find_view_and_details';
190 begin
191 hr_utility.set_location('Entering:'||l_proc, 10);
192 --
193 -- Find out if the view definition exists in the current account
194 --
195 open csr_view_exists;
196 fetch csr_view_exists into l_view_owner;
197 if csr_view_exists%found then
198 close csr_view_exists;
199 l_view_found := true;
200 --
201 -- Obtain the entity title name.
202 --
203 open csr_title;
204 fetch csr_title into l_title_prompt;
205 if csr_title%notfound then
206 close csr_title;
207 hr_utility.set_message(801,'DTH_34950_MISSING_TITLE');
208 hr_utility.set_message_token('VIEW', p_view_name);
209 hr_utility.raise_error;
210 end if;
211 close csr_title;
212 hr_utility.set_location(l_proc, 20);
213 else
214 close csr_view_exists;
215 l_view_found := false;
216 l_view_owner := null;
217 l_title_prompt := null;
218 --
219 -- Note: From R11.0 onwards it is no longer necessary to double check
220 -- for a synonym. Secure users now connect to the APPS account
221 -- where the view definitions are held.
222 --
223 hr_utility.set_location(l_proc, 30);
224 end if;
225 --
226 p_view_found := l_view_found;
227 p_view_owner := l_view_owner;
228 p_title_prompt := l_title_prompt;
229 hr_utility.set_location(' Leaving:'||l_proc, 40);
230 end find_view_and_details;
231 --
232 -- ----------------------------------------------------------------------------
233 -- |-----------------------< derive_view_table_names >------------------------|
234 -- ----------------------------------------------------------------------------
235 -- {Start Of Comments}
236 --
237 -- Description:
238 -- Takes the provided table name and returns the name of the standard _D
239 -- DateTrack history view. Just in case the Form FND_SESSION view name
240 -- has been provided by mistake, the table name is also formatted. Appending
241 -- an _F suffix if it does not exist. All values are returned in uppercase.
242 -- For example, if p_table_name is set to 'per_all_people' then
243 -- p_st_view_name will be set to PER_ALL_PEOPLE_D
244 -- p_st_table_name will be set to PER_ALL_PEOPLE_F
245 --
246 -- Prerequisites:
247 -- p_table_name is not null.
248 --
249 -- In Parameters:
250 -- Name Reqd Type Description
251 -- p_table_name Yes varchar2 Name of the table.
252 --
253 -- Post Success:
254 -- Returns name of the standard _D DateTrack history view and the name of
255 -- the _F table and the name of table without the '_F' suffix. All in upper
256 -- case.
257 --
258 -- Post Failure:
259 -- Processing stops and an ORA error is raise.
260 --
261 -- Access Status:
262 -- Internal Development Use Only.
263 --
264 -- {End Of Comments}
265 --
266 procedure derive_view_table_names
267 (p_table_name in varchar2
268 ,p_st_view_name out nocopy varchar2
269 ,p_st_table_name out nocopy varchar2
270 ,p_st_table_name_trim out nocopy varchar2
271 ) is
272 --
273 -- Declare local variables
274 --
275 l_table_trim user_views.view_name%type; -- Table name, without white
276 -- space, without _F and in
277 -- uppercase.
278 l_name_length number; -- Length of the table
279 -- name provided without white
280 -- space.
281 l_pos_last_unders number; -- Position of the last '_'
282 -- in the provided table name.
283 l_proc varchar2(72) := g_package||'derive_view_table_names';
284 begin
285 hr_utility.set_location('Entering:'||l_proc, 10);
286 --
287 l_table_trim := upper(rtrim(p_table_name));
288 l_name_length := length(l_table_trim);
289 l_pos_last_unders := instrb(l_table_trim, '_F', -1);
290 --
291 -- Remove _F, if it exists, from the end of the base table name.
292 --
293 if (l_pos_last_unders = (l_name_length - 1)) then
294 l_table_trim := substrb(l_table_trim,
295 1,
296 l_name_length - 2);
297 end if;
298 --
299 p_st_view_name := l_table_trim || '_D';
300 p_st_table_name := l_table_trim || '_F';
301 p_st_table_name_trim := l_table_trim;
302 --
303 hr_utility.set_location(' Leaving:'||l_proc, 20);
304 end derive_view_table_names;
305 --
306 -- ----------------------------------------------------------------------------
307 -- |------------------------< find_table_and_details >------------------------|
308 -- ----------------------------------------------------------------------------
309 -- {Start Of Comments}
310 --
311 -- Description:
312 -- Checks the current database account has access to the specified table.
313 --
314 -- Prerequisites:
315 -- p_table_name must be in upper case and contain the '_F' suffix.
316 -- i.e. The "derive_view_table_names" procedure has already been executed.
317 --
318 -- In Parameters:
319 -- Name Reqd Type Description
320 -- p_table_name Yes varchar2 Table name to validate.
321 -- p_table_name_trim Yes varchar2 Name of the table without
322 -- the '_F' suffix.
323 --
324 -- Post Success:
325 -- If the table synonym exists processing continues and the following
326 -- OUT parameters will be populated.
327 --
328 -- Name Type Description
332 -- the window title prompt. Derived
329 -- p_table_owner varchar2 Name of the database account
330 -- which owns the table.
331 -- p_title_prompt varchar2 User entity name to include in
333 -- from the table name.
334 --
335 -- Post Failure:
336 -- Processing stops and an application error is raised if the table
337 -- synonym cannot be found.
338 --
339 -- Access Status:
340 -- Internal Development Use Only.
341 --
342 -- {End Of Comments}
343 --
344 procedure find_table_and_details
345 (p_table_name in varchar2
346 ,p_table_name_trim in varchar2
347 ,p_table_owner out nocopy varchar2
348 ,p_title_prompt out nocopy varchar2
349 ) is
350 --
351 -- Cursor to find table synonym definition.
352 --
353 cursor csr_tab_synonym_exists is
354 select table_owner
355 from user_synonyms syn
356 where syn.table_name = p_table_name;
357 --
358 -- Declare local variables
359 --
360 l_table_owner varchar2(30);
361 l_title_prompt varchar2(80);
362 l_proc varchar2(72) := g_package||'find_table_and_details';
363 begin
364 hr_utility.set_location('Entering:'||l_proc, 10);
365 --
366 -- Find out if the table synonym definition exists in the current account
367 -- Note: From R11.0 onwards, there is no need to check the actual table
368 -- definition is held in the current account. Table definitions will
369 -- exist in HR where as users connect to the APPS account.
370 --
371 open csr_tab_synonym_exists;
372 fetch csr_tab_synonym_exists into l_table_owner;
373 if csr_tab_synonym_exists%notfound then
374 close csr_tab_synonym_exists;
375 hr_utility.set_message(801, 'DTH_34952_ACCESS_TABLE');
376 hr_utility.set_message_token('TABLE', p_table_name);
377 hr_utility.raise_error;
378 end if;
379 close csr_tab_synonym_exists;
380 --
381 -- Derive the entity title name from the table name.
382 -- Remove the product code prefix, replace underscores
383 -- with spaces and initcap the result.
384 --
385 l_title_prompt := substr(p_table_name_trim,
386 (instrb(p_table_name_trim, '_') + 1));
387 l_title_prompt := initcap(replace(l_title_prompt, '_', ' '));
388 -- Set out parameters
389 p_table_owner := l_table_owner;
390 p_title_prompt := l_title_prompt;
391 --
392 hr_utility.set_location(' Leaving:'||l_proc, 20);
393 end find_table_and_details;
394 --
395 -- ----------------------------------------------------------------------------
396 -- |----------------------------< get_date_prompts >--------------------------|
397 -- ----------------------------------------------------------------------------
398 -- {Start Of Comments}
399 --
400 -- Description:
401 -- Obtains the effective_start_date and effective_end_date column display
402 -- prompts, from DT_DATE_PROMPTS_TL, for a particular language.
403 --
404 -- Prerequisites:
405 -- None.
406 --
407 -- In Parameters:
408 -- Name Reqd Type Description
409 -- p_language_code Yes varchar2 Prompts will be returned
410 -- for the specified language.
411 --
412 -- Post Success:
413 -- Details about the prompts are derived and the following OUT parameters
414 -- will be populated.
415 --
416 -- Name Type Description
417 -- p_effective_start_prompt varchar2 Effective start date end user
418 -- display prompt, for the specified
419 -- language.
420 -- p_effective_end_prompt varchar2 Effective end date end user
421 -- display prompt, for the specified
422 -- language.
423 --
424 -- Post Failure:
425 -- Processing stops and an application error is raised if an unexpected
426 -- error occurs.
427 --
428 -- Access Status:
429 -- Internal Development Use Only.
430 --
431 -- {End Of Comments}
432 --
433 procedure get_date_prompts
434 (p_language_code in varchar2
435 ,p_effective_start_prompt out nocopy varchar2
436 ,p_effective_end_prompt out nocopy varchar2
437 ) is
438 --
439 -- Cursor to obtain the date display prompts.
440 --
441 cursor csr_prompts is
442 select effective_start_prompt
443 , effective_end_prompt
444 from dt_date_prompts_tl
445 where language = p_language_code;
446 --
447 l_proc varchar2(72) := g_package||'get_date_prompts';
448 begin
449 hr_utility.set_location('Entering:'||l_proc, 10);
450 --
451 -- Obtain date display prompts.
452 --
453 open csr_prompts;
454 fetch csr_prompts into p_effective_start_prompt
455 , p_effective_end_prompt;
456 if csr_prompts%notfound then
457 close csr_prompts;
458 hr_utility.set_message(801, 'DTH_34951_DATE_PROMPT');
459 hr_utility.set_message_token('LANGUAGE', p_language_code);
460 hr_utility.raise_error;
461 end if;
462 close csr_prompts;
466 -- Procedures declared in the package header
463 hr_utility.set_location(' Leaving:'||l_proc, 20);
464 end get_date_prompts;
465 --
467 --
468 -- ----------------------------------------------------------------------------
469 -- |-------------------------< get_view_and_prompts >-------------------------|
470 -- ----------------------------------------------------------------------------
471 --
472 procedure get_view_and_prompts
473 (p_table_name in varchar2
474 ,p_alternative_history_view in varchar2
475 ,p_language_code out nocopy varchar2
476 ,p_view_name out nocopy varchar2
477 ,p_view_owner out nocopy varchar2
478 ,p_title_prompt out nocopy varchar2
479 ,p_effective_start_prompt out nocopy varchar2
480 ,p_effective_end_prompt out nocopy varchar2
481 ) is
482 --
483 -- Declare local variables
484 --
485 l_view_name user_views.view_name%type; -- Name of the view, or table,
486 -- to return OUT of this
487 -- procedure.
488 l_al_view_name user_views.view_name%type; -- Alternative DT history view
489 -- in upper case.
490 l_st_view_name user_views.view_name%type; -- Standard DT history _D view
491 -- in upper case.
492 l_al_view_found boolean default false; -- Indicates if the alternative
493 -- DT history view exists.
494 l_st_view_found boolean default false; -- Indicates if the standard
495 -- DT history _D view exists.
496 l_st_table_name varchar2(30); -- Formatted table name, with
497 -- _F suffix, in upper case.
498 l_st_table_name_trim varchar2(30); -- Name of the table, without
499 -- the _F suffix, in upper case.
500 l_effective_start_prompt varchar2(80); -- Effective start date display
501 -- column prompt.
502 l_effective_end_prompt varchar2(80); -- Effective end date display
503 -- column prompt.
504 l_view_owner varchar2(30); -- Name of the database account
505 -- which owns the view or table
506 -- definition.
507 l_title_prompt varchar2(80); -- The user entity name to be
508 -- included in the Form window
509 -- title.
510 l_language_code varchar2(80); -- Value from userenv('LANG')
511 l_proc varchar2(72) := g_package||'get_view_and_prompts';
512 --
513 -- Algorithm :-
514 --
515 -- Get language from userenv('LANG')
516 --
517 -- IF alternative_history_view has been specified THEN
518 -- Check to see if alternative_history_view exists in user_views
519 -- Get the view owner account name from "user" and the title name
520 -- from DT_TITLE_PROMPTS_TL.
521 -- END IF
522 --
523 -- IF (alternative_history_view has NOT been specified) OR
524 -- (alternative_history_view was specified but was not found) THEN
525 -- Check to see if standard <table_name_no_suffix>_D view exists
526 -- in user_views
527 -- Get the view owner account name from "user" and the title name
528 -- from DT_TITLE_PROMPTS_TL.
529 --
530 -- N.B. From R11.0 onwards it is no longer necessary to double check
531 -- for a synonym. Secure users now connect to the APPS account
532 -- where the view definitions are held.
533 -- END IF
534 --
535 -- IF (alternative_history_view does not exist) AND
536 -- (<table_name_no_suffix>_D view does not exist) THEN
537 --
538 -- Check that _F table actually exists
539 -- Tables are defined in the HR account, not in the APPS account the
540 -- user is connected to. So the table name will not exist in user_tables.
541 -- Just check a synonym exists in the current account for the table name.
542 -- Get the table owner account name from user_synonyms.
543 --
544 -- N.B. From R11.0 onwards addition logic is not required for secure users
545 -- as they are also connected to the APPS account.
546 --
547 -- When the synonym does exist then:
548 -- Derive p_title_prompt value from the table name. The PER_/PAY_/FF_
549 -- prefix is removed, the _D or _F suffix is removed, underscore
550 -- characters are replaced with spaces and initcap the remaining text.
551 --
552 -- END IF
553 --
554 -- Get p_effective_start_prompt value from DT_DATE_PROMPTS_TL
555 -- Get p_effective_end_prompt value from DT_DATE_PROMPTS_TL
556 --
557 begin
558 hr_utility.set_location('Entering:'||l_proc, 10);
559 --
560 -- Get language from userenv('LANG')
561 --
562 find_language_code
563 (p_language_code => l_language_code
564 );
565 --
566 -- If an alternative history view has been specified then check it can
567 -- be found. Also obtain the title and owner database account name.
568 --
569 if p_alternative_history_view is not null then
570 l_al_view_name := upper(p_alternative_history_view);
574 ,p_view_found => l_al_view_found
571 find_view_and_details
572 (p_view_name => l_al_view_name
573 ,p_language_code => l_language_code
575 ,p_view_owner => l_view_owner
576 ,p_title_prompt => l_title_prompt
577 );
578 end if;
579 --
580 -- If the alternative history view has not been specified OR
581 -- it was specified but was not found then check to see if the
582 -- standard DateTrack History _D view can be found.
583 --
584 if not l_al_view_found then
585 -- First, work out the full table name, name
586 -- of the standard _D view and format table.
587 derive_view_table_names
588 (p_table_name => p_table_name
589 ,p_st_view_name => l_st_view_name
590 ,p_st_table_name => l_st_table_name
591 ,p_st_table_name_trim => l_st_table_name_trim
592 );
593 find_view_and_details
594 (p_view_name => l_st_view_name
595 ,p_language_code => l_language_code
596 ,p_view_found => l_st_view_found
597 ,p_view_owner => l_view_owner
598 ,p_title_prompt => l_title_prompt
599 );
600 end if;
601 --
602 if (not l_st_view_found) AND (not l_al_view_found) then
603 --
604 -- Neither view exists check the actual _F table exists.
605 -- If the table cannot be found then raise an error.
606 -- When the table does exist obtain details of the
607 -- database account and the entity title name.
608 --
609 find_table_and_details
610 (p_table_name => l_st_table_name
611 ,p_table_name_trim => l_st_table_name_trim
612 ,p_table_owner => l_view_owner
613 ,p_title_prompt => l_title_prompt
614 );
615 l_view_name := l_st_table_name;
616 --
617 elsif l_al_view_found then
618 --
619 -- The alternative view was found.
620 --
621 l_view_name := l_al_view_name;
622 --
623 elsif l_st_view_found then
624 --
625 -- The standard view was found.
626 --
627 l_view_name := l_st_view_name;
628 --
629 else
630 --
631 -- This code point should never be reached. If neither
632 -- view can be found, and the table does not exist then
633 -- the "check_table_exists" procedure should have already
634 -- raised an error.
635 --
636 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
637 hr_utility.set_message_token('PROCEDURE', l_proc);
638 hr_utility.set_message_token('STEP','20');
639 hr_utility.raise_error;
640 end if;
641 --
642 -- Obtained effective date prompts for the specified language
643 --
644 get_date_prompts
645 (p_language_code => l_language_code
646 ,p_effective_start_prompt => l_effective_start_prompt
647 ,p_effective_end_prompt => l_effective_end_prompt
648 );
649 --
650 -- Populate OUT parameters
651 --
652 p_language_code := l_language_code;
653 p_view_name := l_view_name;
654 p_view_owner := l_view_owner;
655 p_title_prompt := l_title_prompt;
656 p_effective_start_prompt := l_effective_start_prompt;
657 p_effective_end_prompt := l_effective_end_prompt;
658 hr_utility.set_location(' Leaving:'||l_proc, 30);
659 --
660 end get_view_and_prompts;
661 --
662 -- Left an old overload version of the get_view_and_prompts
663 -- procedure, without the new p_alternative_history_view parameter
664 -- or changed p_language_code parameter.
665 -- This old version can be removed after R11.5, as the DTXHISTY
666 -- Form will have been changed to always call the new version.
667 --
668 procedure get_view_and_prompts
669 ( p_table_name in varchar2,
670 p_language_code in varchar2,
671 p_view_name out nocopy varchar2,
672 p_view_owner out nocopy varchar2,
673 p_title_prompt out nocopy varchar2,
674 p_effective_start_prompt out nocopy varchar2,
675 p_effective_end_prompt out nocopy varchar2
676 ) is
677 --
678 l_language_code varchar2(80);
679 --
680 begin
681 --
682 -- Call new overload version setting the new
683 -- p_alternative_history_view parameter to null.
684 --
685 get_view_and_prompts
686 (p_table_name => p_table_name
687 ,p_alternative_history_view => null
688 ,p_language_code => l_language_code
689 ,p_view_name => p_view_name
690 ,p_view_owner => p_view_owner
691 ,p_title_prompt => p_title_prompt
692 ,p_effective_start_prompt => p_effective_start_prompt
693 ,p_effective_end_prompt => p_effective_end_prompt
694 );
695 end get_view_and_prompts;
696 --
697 -- Function to return column prompt for a given table.
698 --
699 FUNCTION fetch_dt_column_prompt(p_table_name IN VARCHAR2
700 ,p_column_name IN VARCHAR2
701 ,p_language_code IN VARCHAR2) RETURN VARCHAR2 IS
702 --
703 l_col dt_column_prompts_tl.column_prompt%TYPE;
704 --
705 BEGIN
706 --
707 SELECT dcp.column_prompt
708 INTO l_col
709 FROM dt_column_prompts_tl dcp
710 WHERE dcp.view_name = p_table_name
711 AND dcp.column_name = p_column_name
712 AND dcp.language = p_language_code;
713 --
714 RETURN(l_col);
715 EXCEPTION
716 WHEN OTHERS THEN
717 RETURN(INITCAP(REPLACE(p_column_name, '_', ' ')));
718 END fetch_dt_column_prompt;
719 --
720 end hr_history;
[Home] [Help]