1 PACKAGE BODY hxc_alias_utils_pkg as
2 /* $Header: hxcaliutl.pkb 120.2 2005/09/23 08:03:38 sechandr noship $ */
3 --
4 /*===========================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================+
9 Name
10 hxc_alias_utils_pkg
11 Purpose
12 Used by HXCALIAS (Define Alternate Names) for the hxc_alias_definitions
13 and the hxc_alias_values blocks.
14 Notes
15
16 History
17 01-Nov-00 RAMURTHY 115.0 Date created.
18 09-JUN-02 MHANDA 115.4 added SET VERIFY OFF
19 09-Sep-05 sechandr 115.6 4573414 Conditionally enabling hr_utility calls
20 ============================================================================*/
21
22 g_debug boolean :=hr_utility.debug_enabled;
23 --------------------------------------------------------------------------------
24 PROCEDURE validate_defn_translation (p_alias_definition_id IN number,
25 p_language IN varchar2,
26 p_alias_definition_name IN varchar2,
27 p_description IN varchar2) IS
28
29 /*
30
31 This procedure fails if an alias definition name translation is already
32 present in the table for a given language. Otherwise, no action is performed.
33 It is used to ensure uniqueness of translated alias definition names.
34
35 */
36
37 --
38 -- This cursor implements the validation we require.
39 --
40
41
42 cursor c_translation(p_language IN VARCHAR2,
43 p_alias_definition_name IN VARCHAR2,
44 p_alias_definition_id IN NUMBER) IS
45 SELECT 1
46 FROM hxc_alias_definitions_tl adtl,
47 hxc_alias_definitions ad
48 WHERE upper(adtl.alias_definition_name) =
49 upper(translate(p_alias_definition_name, '_',' '))
50 AND adtl.alias_definition_id = ad.alias_definition_id
51 AND adtl.language = p_language
52 AND (ad.alias_definition_id <> p_alias_definition_id OR
53 p_alias_definition_id IS NULL );
54
55 l_package_name VARCHAR2(80);
56 l_name hxc_alias_definitions.alias_definition_name%type
57 := p_alias_definition_name;
58 l_dummy varchar2(100);
59 l_dummy_number number;
60
61 BEGIN
62 g_debug:=hr_utility.debug_enabled;
63 if g_debug then
64 l_package_name := 'hxc_alias_utils_pkg.validate_defn_translation';
65 hr_utility.set_location (l_package_name,1);
66 end if;
67
68 /*
69 BEGIN
70 hr_chkfmt.checkformat (l_name,
71 'PAY_NAME',
72 l_dummy, null, null, 'N', l_dummy, null);
73 if g_debug then
74 hr_utility.set_location (l_package_name,2);
75 end if;
76
77 EXCEPTION
78 when app_exception.application_exception then
79 if g_debug then
80 hr_utility.set_location (l_package_name,3);
81 end if;
82 fnd_message.set_name ('PAY','PAY_6365_ELEMENT_NO_DB_NAME'); -- check
83 format failure
84 fnd_message.raise_error;
85 END;
86
87 if g_debug then
88 hr_utility.set_location (l_package_name,10);
89 end if;
90 */
91 OPEN c_translation(p_language
92 ,p_alias_definition_name
93 ,p_alias_definition_id);
94
95 if g_debug then
96 hr_utility.set_location (l_package_name,20);
97 end if;
98
99 FETCH c_translation INTO l_dummy_number;
100 if g_debug then
101 hr_utility.set_location (l_package_name,25);
102 end if;
103
104 IF c_translation%NOTFOUND THEN
105 if g_debug then
106 hr_utility.set_location (l_package_name,30);
107 end if;
108 CLOSE c_translation;
109 ELSE
110 if g_debug then
111 hr_utility.set_location (l_package_name,40);
112 end if;
113 CLOSE c_translation;
114 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
115 fnd_message.raise_error;
116 END IF;
117
118 if g_debug then
119 hr_utility.set_location ('Leaving: '||l_package_name,140);
120 end if;
121 END validate_defn_translation;
122
123
124 PROCEDURE validate_name_translation (p_alias_value_id IN number,
125 p_language IN varchar2,
126 p_alias_value_name IN varchar2) IS
127
128 /*
129
130 This procedure fails if an alias value name translation is already
131 present in the table for a given language. Otherwise, no action is performed.
132 It is used to ensure uniqueness of translated alias value names.
133
134 */
135
136 --
137 -- This cursor implements the validation we require.
138 --
139
140
141 cursor c_translation(p_language IN VARCHAR2,
142 p_alias_value_name IN VARCHAR2,
143 p_alias_value_id IN NUMBER) IS
144 SELECT 1
145 FROM hxc_alias_values_tl avtl,
146 hxc_alias_values av
147 WHERE upper(avtl.alias_value_name) =
148 upper(translate(p_alias_value_name, '_',' '))
149 AND avtl.alias_value_id = av.alias_value_id
150 AND avtl.language = p_language
151 AND (av.alias_value_id <> p_alias_value_id OR
152 p_alias_value_id IS NULL );
153
154 l_package_name VARCHAR2(80);
155 l_name hxc_alias_values.alias_value_name%type := p_alias_value_name;
156 l_dummy varchar2(100);
157 l_dummy_number number;
158
159 BEGIN
160 g_debug:=hr_utility.debug_enabled;
161 if g_debug then
162 l_package_name := 'hxc_alias_utils_pkg.validate_name_translation';
163 hr_utility.set_location (l_package_name,1);
164 end if;
165
166 /*
167 BEGIN
168 hr_chkfmt.checkformat (l_name,
169 'PAY_NAME',
170 l_dummy, null, null, 'N', l_dummy, null);
171 if g_debug then
172 hr_utility.set_location (l_package_name,2);
173 end if;
174
175 EXCEPTION
176 when app_exception.application_exception then
177 if g_debug then
178 hr_utility.set_location (l_package_name,3);
179 end if;
180 fnd_message.set_name ('PAY','PAY_6365_ELEMENT_NO_DB_NAME'); -- check
181 format failure
182 fnd_message.raise_error;
183 END;
184
185 if g_debug then
186 hr_utility.set_location (l_package_name,10);
187 end if;
188 */
189 OPEN c_translation(p_language
190 ,p_alias_value_name
191 ,p_alias_value_id);
192
193 if g_debug then
194 hr_utility.set_location (l_package_name,20);
195 end if;
196
197 FETCH c_translation INTO l_dummy_number;
198 if g_debug then
199 hr_utility.set_location (l_package_name,25);
200 end if;
201
202 IF c_translation%NOTFOUND THEN
203 if g_debug then
204 hr_utility.set_location (l_package_name,30);
205 end if;
206 CLOSE c_translation;
207 ELSE
208 if g_debug then
209 hr_utility.set_location (l_package_name,40);
210 end if;
211 CLOSE c_translation;
212 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
213 fnd_message.raise_error;
214 END IF;
215
216 if g_debug then
217 hr_utility.set_location ('Leaving: '||l_package_name,140);
218 end if;
219 END validate_name_translation;
220
221
222 END hxc_alias_utils_pkg;