1 package body hrdateth as
2 /* $Header: dtdateth.pkb 115.1 2002/12/06 16:13:55 apholt ship $ */
3 /*
4 --
5 -- Copyright (c) Oracle Corporation 1991, 1992, 1993. All rights reserved.
6 --
7 /*
8 NAME
9 dtdateth.pkb -- procedure for Date Track History (DTH)
10 --
11 DESCRIPTION
12 This procedure is called from the 'C' code of the Date Track History
13 program. It is responsible for seeing if a view can be used rather than
14 the supplied base table to drive the date track history, and also for
15 forming the title to be displayed on the values screen of DTH. If a view
16 exists then this is returned in 'p_base_table', else if no view exists it
17 returns the supplied base table name. The view name is similar to the
18 base table name, except that the end string of "_f" (if it exists) is
19 removed, and concatenated with "_DTH_V". For example, the base table name
20 of 'per_people_f' would have a DTH view of 'per_people_dth_v'.
21 --
22 MODIFIED (DD-MON-YYYY)
23 mwcallag 18-JUL-1994 - test added : to look for synonym a when the code
24 is being called by a secure user.
25 mwcallag 03-AUG-1993 - view name changed to have suffix of '_d'
26 mwcallag 13-JUL-1993 - created.
27
28 115.1 A.Holt 06-Dec-2002 - NOCOPY Performance Changes for 11.5.9
29 GSCC complinance*/
30 --
31 procedure get_view
32 (
33 p_base_table in out nocopy varchar2,
34 p_out_title out nocopy varchar2
35 ) is
36 l_table_name user_catalog.table_name%type;
37 l_title user_catalog.table_name%type;
38 l_name_length number;
39 l_count number;
40 l_found_flag boolean;
41 l_temp varchar2(10);
42 BEGIN
43 --
44 -- remove the "_f" (if its present) from the base table.
45 --
46 l_table_name := upper (rtrim (p_base_table));
47 l_name_length := length (l_table_name); -- get table string length
48 l_count :=instrb (l_table_name, '_', -1); -- get position of last '_'
49 --
50 if (l_count = (l_name_length - 1)) then
51 l_table_name := rtrim (l_table_name, '_F');
52 end if;
53 --
54 -- strip off the leading product, and convert to title display
55 --
56 l_title := substr (l_table_name, (instrb (l_table_name, '_') + 1));
57 l_title := initcap (replace (l_title, '_', ' '));
58 --
59 -- now see if the DTH view owned by the user exists:
60 --
61 l_table_name := l_table_name || '_D';
62 l_found_flag := TRUE;
63 hr_utility.set_location ('hrdateth.get_view', 1);
64 begin
65 select 1
66 into l_temp
67 from user_catalog
68 where table_name = l_table_name
69 and table_type = 'VIEW';
70 exception
71 when no_data_found then
72 --
73 -- If we are logged in as a secure user, the DTH 'view' could be defined
74 -- as a synonym:
75 --
76 -- Note: In order to perform a complete check, the sql statement below
77 -- should access the table 'table_privileges' to see if the user
78 -- has select privileges. So, the SQL should be:
79 --
80 -- select 1
81 -- into l_temp
82 -- from all_synonyms SYN
83 -- , table_privileges PRIV
84 -- where SYN.table_name = l_table_name
85 -- and SYN.owner in ('PUBLIC', user)
86 -- and PRIV.table_name = SYN.table_name
87 -- and PRIV.select_priv = 'Y'
88 -- and rownum = 1;
89 --
90 -- However, accessing table_privileges causes an ORA-600 error.
91 -- This has been logged previously as bug 200413 and is fixed in
92 -- the Oracle release 7.1. So, for now this table is not included
93 -- in the sql statement, and may be added at a later date when 7.1
94 -- is available if it is deemed necessary.
95 --
96 begin
97 select 1
98 into l_temp
99 from all_synonyms SYN
100 where SYN.table_name = l_table_name
101 and SYN.owner in ('PUBLIC', user)
102 and rownum = 1;
103 --
104 exception
105 when no_data_found then l_found_flag := FALSE;
106 end;
107 end;
108 if (l_found_flag = TRUE) then -- use the view name
109 p_base_table := l_table_name;
110 p_out_title := l_title || ' View';
111 else -- no change to base table name
112 p_out_title := l_title;
113 end if;
114 END get_view;
115 end hrdateth;
[Home] [Help]