[Home] [Help]
PACKAGE BODY: APPS.PER_SHT_BUS
Source
1 Package Body per_sht_bus as
2 /* $Header: peshtrhi.pkb 120.0 2005/05/31 21:06:23 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_sht_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_shared_type_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- shared_type_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_shared_type_id(p_shared_type_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_shared_type_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := per_sht_shd.api_updating
47 (p_shared_type_id => p_shared_type_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_shared_type_id,hr_api.g_number)
52 <> per_sht_shd.g_old_rec.shared_type_id) then
53 --
54 -- raise error as PK has changed
55 --
56 per_sht_shd.constraint_error('PER_SHARED_TYPES_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_shared_type_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 per_sht_shd.constraint_error('PER_SHARED_TYPES_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_shared_type_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_system_type_cd >------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- This procedure is used to check that the lookup value is valid.
82 --
83 -- Pre Conditions
84 -- None.
85 --
86 -- In Parameters
87 -- shared_type_id PK of record being inserted or updated.
88 -- system_type_cd Value of lookup code.
89 -- effective_date effective date
90 -- object_version_number Object version number of record being
91 -- inserted or updated.
92 --
93 -- Post Success
94 -- Processing continues
95 --
96 -- Post Failure
97 -- Error handled by procedure
98 --
99 -- Access Status
100 -- Internal table handler use only.
101 --
102 Procedure chk_system_type_cd(p_shared_type_id in number,
103 p_lookup_type in varchar2,
104 p_system_type_cd in varchar2,
105 p_effective_date in date,
106 p_object_version_number in number) is
107 --
108 l_proc varchar2(72) := g_package||'chk_system_type_cd';
109 l_api_updating boolean;
110 --
111 Begin
112 --
113 hr_utility.set_location('Entering:'||l_proc, 5);
114 --
115 l_api_updating := per_sht_shd.api_updating
116 (p_shared_type_id => p_shared_type_id,
117 p_object_version_number => p_object_version_number);
118 --
119 if (l_api_updating
120 and p_system_type_cd
121 <> nvl(per_sht_shd.g_old_rec.system_type_cd,hr_api.g_varchar2)
122 or not l_api_updating) then
123 --
124 -- check if value of lookup falls within lookup type.
125 --
126 --
127 if hr_api.not_exists_in_hr_lookups
128 (p_lookup_type => p_lookup_type,
129 p_lookup_code => p_system_type_cd,
130 p_effective_date => p_effective_date) then
131 --
132 -- raise error as does not exist as lookup
133 --
134 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
135 hr_utility.raise_error;
136 --
137 end if;
138 --
139 end if;
140 --
141 hr_utility.set_location('Leaving:'||l_proc,10);
142 --
143 end chk_system_type_cd;
144 --
145 -- ----------------------------------------------------------------------------
146 -- |------< chk_duplicate_key >------|
147 -- ----------------------------------------------------------------------------
148 --
149 -- Description
150 -- This procedure is used to check that the key combination is unique
151 --
152 -- Pre Conditions
153 -- None.
154 --
155 -- In Parameters
156 -- shared_type_id PK of record being inserted or updated.
157 -- system_type_cd Value of lookup code.
158 -- shared_type_code developer key
159 -- lookup_type
160 -- Post Success
161 -- Processing continues
162 --
163 -- Post Failure
164 -- Error handled by procedure
165 --
166 -- Access Status
167 -- Internal table handler use only.
168 --
169 Procedure chk_duplicate_key(p_shared_type_id in number,
170 p_lookup_type in varchar2,
171 p_system_type_cd in varchar2,
172 p_shared_type_code in varchar2,
173 p_business_group_id in number,
174 p_shared_type_name in Varchar2 ) is
175 --
176 l_proc varchar2(72) := g_package||'chk_duplicate_key';
177 l_shared_type_id number;
178 l_lookup_code hr_lookups.lookup_code%TYPE;
179 --
180 -- Fix for bug 3478716. added upper for shared_type_name in the following cursor.
181 --
182 cursor c1 is select shared_type_id
183 from per_shared_types
184 where lookup_type = p_lookup_type
185 and system_type_cd = p_system_type_cd
186 and shared_type_code = p_shared_type_code
187 and upper(Shared_type_name) = upper(p_shared_type_name)
188 AND ((business_group_id = p_business_group_id)
189 OR (business_group_id IS NULL)
190 OR (p_business_group_id IS NULL));
191 --
192 Begin
193 --
194 hr_utility.set_location('Entering:'||l_proc, 5);
195 hr_utility.set_location('value of shared_type_id:'||p_shared_type_id, 5);
196 hr_utility.set_location('value of shared_type_code:'||p_shared_type_code, 5);
197 hr_utility.set_location('value of system_type_cd:'||p_system_type_cd, 5);
198 hr_utility.set_location('value of lookup_type:'||p_lookup_type, 5);
199 hr_utility.set_location('value of shared_type_name:'||p_shared_type_name, 5);
200 hr_utility.set_location('value of business_group_id:'||p_business_group_id, 5);
201 --
202 open c1;
203 fetch c1 into l_shared_type_id;
204 if c1%found then
205 -- key exists
206 if p_shared_type_id is null
207 or (p_shared_type_id is not null and p_shared_type_id <> l_shared_type_id) then
208 close c1;
209 select hr_general.decode_lookup(p_lookup_type,p_system_type_cd) into l_lookup_code from dual;
210 -- different record is being updated inserted with same key, raise error.
211 hr_utility.set_message(800,'PER_SHARE_TYPE_KEY_EXISTS');
212 hr_utility.set_message_token('LOOKUP_TYPE',p_lookup_type);
213 hr_utility.set_message_token('SHARED_TYPE_CODE',p_shared_type_code);
214 hr_utility.set_message_token('LOOKUP_CODE',l_lookup_code);
215 hr_utility.raise_error;
216 --
217 end if;
218 end if;
219 close c1;
220 --
221 hr_utility.set_location('Leaving:'||l_proc,10);
222 --
223 end chk_duplicate_key;
224 --
225 -- ----------------------------------------------------------------------------
226 -- |---------------------------< insert_validate >----------------------------|
227 -- ----------------------------------------------------------------------------
228 Procedure insert_validate(p_rec in per_sht_shd.g_rec_type
229 ,p_effective_date in date) is
230 --
231 l_proc varchar2(72) := g_package||'insert_validate';
232 --
233 Begin
234 hr_utility.set_location('Entering:'||l_proc, 5);
235 --
236 -- Call all supporting business operations
237 --
238 chk_shared_type_id
239 (p_shared_type_id => p_rec.shared_type_id,
240 p_object_version_number => p_rec.object_version_number);
241 --
242 chk_system_type_cd
243 (p_shared_type_id => p_rec.shared_type_id,
244 p_lookup_type => p_rec.lookup_type,
245 p_system_type_cd => p_rec.system_type_cd,
246 p_effective_date => p_effective_date,
247 p_object_version_number => p_rec.object_version_number);
248 --
249 chk_duplicate_key(p_shared_type_id => p_rec.shared_type_id,
250 p_lookup_type => p_rec.lookup_type,
251 p_system_type_cd => p_rec.system_type_cd,
252 p_shared_type_code => p_rec.shared_type_code,
253 p_shared_type_name => p_rec.shared_type_name,
254 p_business_group_id => p_rec.business_group_id);
255 --
256 -- hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
257 --
258 hr_utility.set_location(' Leaving:'||l_proc, 10);
259 End insert_validate;
260 --
261 -- ----------------------------------------------------------------------------
262 -- |---------------------------< update_validate >----------------------------|
263 -- ----------------------------------------------------------------------------
264 Procedure update_validate(p_rec in per_sht_shd.g_rec_type
265 ,p_effective_date in date) is
266 --
267 l_proc varchar2(72) := g_package||'update_validate';
268 --
269 Begin
270 hr_utility.set_location('Entering:'||l_proc, 5);
271 --
272 -- Call all supporting business operations
273 --
274 chk_shared_type_id
275 (p_shared_type_id => p_rec.shared_type_id,
276 p_object_version_number => p_rec.object_version_number);
277 --
278 chk_system_type_cd
279 (p_shared_type_id => p_rec.shared_type_id,
280 p_lookup_type => p_rec.lookup_type,
281 p_system_type_cd => p_rec.system_type_cd,
282 p_effective_date => p_effective_date,
283 p_object_version_number => p_rec.object_version_number);
284 --
285 chk_duplicate_key(p_shared_type_id => p_rec.shared_type_id,
286 p_lookup_type => p_rec.lookup_type,
287 p_system_type_cd => p_rec.system_type_cd,
288 p_shared_type_code => p_rec.shared_type_code,
289 p_shared_type_name => p_rec.shared_type_name,
290 p_business_group_id => p_rec.business_group_id);
291 --
292 -- hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
293 --
294 hr_utility.set_location(' Leaving:'||l_proc, 10);
295 End update_validate;
296 --
297 -- ----------------------------------------------------------------------------
298 -- |---------------------------< delete_validate >----------------------------|
299 -- ----------------------------------------------------------------------------
300 Procedure delete_validate(p_rec in per_sht_shd.g_rec_type
301 ,p_effective_date in date) is
302 --
303 l_proc varchar2(72) := g_package||'delete_validate';
304 --
305 Begin
306 hr_utility.set_location('Entering:'||l_proc, 5);
307 --
308 -- Call all supporting business operations
309 --
310 hr_utility.set_location(' Leaving:'||l_proc, 10);
311 End delete_validate;
312 --
313 --
314 -- ---------------------------------------------------------------------------
315 -- |---------------------< return_legislation_code >-------------------------|
316 -- ---------------------------------------------------------------------------
317 --
318 function return_legislation_code
319 (p_shared_type_id in number) return varchar2 is
320 --
321 -- Declare cursor
322 --
323 cursor csr_leg_code is
324 select a.legislation_code
325 from per_business_groups a,
326 per_shared_types b
327 where b.shared_type_id = p_shared_type_id
328 and a.business_group_id = b.business_group_id;
329 --
330 -- Declare local variables
331 --
332 l_legislation_code varchar2(150);
333 l_proc varchar2(72) := g_package||'return_legislation_code';
334 --
335 begin
336 --
337 hr_utility.set_location('Entering:'|| l_proc, 10);
338 --
339 -- Ensure that all the mandatory parameter are not null
340 --
341 hr_api.mandatory_arg_error(p_api_name => l_proc,
342 p_argument => 'shared_type_id',
343 p_argument_value => p_shared_type_id);
344 --
345 open csr_leg_code;
346 --
347 fetch csr_leg_code into l_legislation_code;
348 --
349 if csr_leg_code%notfound then
350 --
351 close csr_leg_code;
352 --
353 -- The primary key is invalid therefore we must error
354 --
355 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
356 hr_utility.raise_error;
357 --
358 end if;
359 --
360 close csr_leg_code;
361 --
362 hr_utility.set_location(' Leaving:'|| l_proc, 20);
363 --
364 return l_legislation_code;
365 --
366 end return_legislation_code;
367 --
368 end per_sht_bus;