[Home] [Help]
PACKAGE BODY: APPS.HR_AE_PREVIOUS_EMPLOYMENT_API
Source
1 package body hr_ae_previous_employment_api as
2 /* $Header: pepemaei.pkb 120.0 2005/06/03 05:07:23 appldev noship $ */
3
4 -- Package Variables
5 g_package VARCHAR2(33) := 'hr_ae_previous_employment_api.';
6 g_debug boolean := hr_utility.debug_enabled;
7
8 procedure create_ae_previous_employer
9 ( p_effective_date IN date
10 ,p_validate IN boolean default false
11 ,p_business_group_id IN number
12 ,p_person_id IN number
13 ,p_party_id IN number default null
14 ,p_start_date IN date default null
15 ,p_end_date IN date default null
16 ,p_period_years IN number default null
17 ,p_period_months IN number default null
18 ,p_period_days IN number default null
19 ,p_employer_name IN varchar2
20 ,p_employer_country IN varchar2 default null
21 ,p_employer_address IN varchar2 default null
22 ,p_employer_type IN varchar2 default null
23 ,p_employer_subtype IN varchar2 default null
24 ,p_description IN varchar2 default null
25 ,p_all_assignments IN varchar2 default 'N'
26 ,p_pem_attribute_category IN varchar2 default null
27 ,p_pem_attribute1 IN varchar2 default null
28 ,p_pem_attribute2 IN varchar2 default null
29 ,p_pem_attribute3 IN varchar2 default null
30 ,p_pem_attribute4 IN varchar2 default null
31 ,p_pem_attribute5 IN varchar2 default null
32 ,p_pem_attribute6 IN varchar2 default null
33 ,p_pem_attribute7 IN varchar2 default null
34 ,p_pem_attribute8 IN varchar2 default null
35 ,p_pem_attribute9 IN varchar2 default null
36 ,p_pem_attribute10 IN varchar2 default null
37 ,p_pem_attribute11 IN varchar2 default null
38 ,p_pem_attribute12 IN varchar2 default null
39 ,p_pem_attribute13 IN varchar2 default null
40 ,p_pem_attribute14 IN varchar2 default null
41 ,p_pem_attribute15 IN varchar2 default null
42 ,p_pem_attribute16 IN varchar2 default null
43 ,p_pem_attribute17 IN varchar2 default null
44 ,p_pem_attribute18 IN varchar2 default null
45 ,p_pem_attribute19 IN varchar2 default null
46 ,p_pem_attribute20 IN varchar2 default null
47 ,p_pem_attribute21 IN varchar2 default null
48 ,p_pem_attribute22 IN varchar2 default null
49 ,p_pem_attribute23 IN varchar2 default null
50 ,p_pem_attribute24 IN varchar2 default null
51 ,p_pem_attribute25 IN varchar2 default null
52 ,p_pem_attribute26 IN varchar2 default null
53 ,p_pem_attribute27 IN varchar2 default null
54 ,p_pem_attribute28 IN varchar2 default null
55 ,p_pem_attribute29 IN varchar2 default null
56 ,p_pem_attribute30 IN varchar2 default null
57 ,p_termination_reason IN varchar2 default null
58 ,p_previous_employer_id OUT NOCOPY number
59 ,p_object_version_number OUT NOCOPY number
60 ) is
61
62 --
63 -- Declare cursors and local variables
64 --
65
66 l_proc varchar2(72) ;
67 l_effective_date date;
68 l_legislation_code per_business_groups.legislation_code%type;
69 l_discard_varchar2 varchar2(30);
70 --
71
72 cursor check_legislation
73 (c_person_id per_people_f.person_id%TYPE,
74 c_effective_date date
75 )
76 is
77 select bgp.legislation_code
78 from per_people_f per,
79 per_business_groups bgp
80 where per.business_group_id = bgp.business_group_id
81 and per.person_id = c_person_id
82 and c_effective_date
83 between per.effective_start_date and per.effective_end_date;
84 --
85 begin
86
87 l_proc := g_package||'create_ae_previous_employer';
88 if g_debug then
89 hr_utility.set_location('Entering:'|| l_proc, 5);
90 end if;
91 --
92 -- Initialise local variables
93 --
94 l_effective_date := trunc(p_effective_date);
95 --
96 -- Validation in addition to Row Handlers
97 --
98 -- Check that the person exists.
99 --
100 open check_legislation(p_person_id, l_effective_date);
101 fetch check_legislation into l_legislation_code;
102 if check_legislation%notfound then
103 close check_legislation;
104 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
105 hr_utility.raise_error;
106 end if;
107 close check_legislation;
108 if g_debug then
109 hr_utility.set_location(l_proc, 20);
110 end if;
111 --
112 -- Check that the legislation of the specified business group is 'AE'.
113 --
114 if l_legislation_code <> 'AE' then
115 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
116 hr_utility.set_message_token('LEG_CODE','AE');
117 hr_utility.raise_error;
118 end if;
119 if g_debug then
120 hr_utility.set_location(l_proc, 30);
121 end if;
122 --
123 -- Create the previous employer record using the create_previous_employer
124 --
125 hr_previous_employment_api.create_previous_employer
126 ( p_effective_date => l_effective_date
127 ,p_validate => p_validate
128 ,p_business_group_id => p_business_group_id
129 ,p_person_id => p_person_id
130 ,p_party_id => p_party_id
131 ,p_start_date => p_start_date
132 ,p_end_date => p_end_date
133 ,p_period_years => p_period_years
134 ,p_period_months => p_period_months
135 ,p_period_days => p_period_days
136 ,p_employer_name => p_employer_name
137 ,p_employer_country => p_employer_country
138 ,p_employer_address => p_employer_address
139 ,p_employer_type => p_employer_type
140 ,p_employer_subtype => p_employer_subtype
141 ,p_description => p_description
142 ,p_all_assignments => p_all_assignments
143 ,p_pem_attribute_category => p_pem_attribute_category
144 ,p_pem_attribute1 => p_pem_attribute1
145 ,p_pem_attribute2 => p_pem_attribute2
146 ,p_pem_attribute3 => p_pem_attribute3
147 ,p_pem_attribute4 => p_pem_attribute4
148 ,p_pem_attribute5 => p_pem_attribute5
149 ,p_pem_attribute6 => p_pem_attribute6
150 ,p_pem_attribute7 => p_pem_attribute7
151 ,p_pem_attribute8 => p_pem_attribute8
152 ,p_pem_attribute9 => p_pem_attribute9
153 ,p_pem_attribute10 => p_pem_attribute10
154 ,p_pem_attribute11 => p_pem_attribute11
155 ,p_pem_attribute12 => p_pem_attribute12
156 ,p_pem_attribute13 => p_pem_attribute13
157 ,p_pem_attribute14 => p_pem_attribute14
158 ,p_pem_attribute15 => p_pem_attribute15
159 ,p_pem_attribute16 => p_pem_attribute16
160 ,p_pem_attribute17 => p_pem_attribute17
161 ,p_pem_attribute18 => p_pem_attribute18
162 ,p_pem_attribute19 => p_pem_attribute19
163 ,p_pem_attribute20 => p_pem_attribute20
164 ,p_pem_attribute21 => p_pem_attribute21
165 ,p_pem_attribute22 => p_pem_attribute22
166 ,p_pem_attribute23 => p_pem_attribute23
167 ,p_pem_attribute24 => p_pem_attribute24
168 ,p_pem_attribute25 => p_pem_attribute25
169 ,p_pem_attribute26 => p_pem_attribute26
170 ,p_pem_attribute27 => p_pem_attribute27
171 ,p_pem_attribute28 => p_pem_attribute28
172 ,p_pem_attribute29 => p_pem_attribute29
173 ,p_pem_attribute30 => p_pem_attribute30
174 ,p_pem_information_category => 'AE'
175 ,p_pem_information1 => p_termination_reason
176 ,p_previous_employer_id => p_previous_employer_id
177 ,p_object_version_number => p_object_version_number
178 );
179
180 --
181 if g_debug then
182 hr_utility.set_location(' Leaving:'||l_proc, 40);
183 end if;
184 --
185 end create_ae_previous_employer;
186 --------------------------------------------------------------------------------- -
187
188 procedure update_ae_previous_employer
189 ( p_effective_date IN date
190 ,p_validate IN boolean default false
191 ,p_previous_employer_id IN number
192 ,p_start_date IN date default hr_api.g_date
193 ,p_end_date IN date default hr_api.g_date
194 ,p_period_years IN number default hr_api.g_number
195 ,p_period_months IN number default hr_api.g_number
196 ,p_period_days IN number default hr_api.g_number
197 ,p_employer_name IN varchar2 default hr_api.g_varchar2
198 ,p_employer_country IN varchar2 default hr_api.g_varchar2
199 ,p_employer_address IN varchar2 default hr_api.g_varchar2
200 ,p_employer_type IN varchar2 default hr_api.g_varchar2
201 ,p_employer_subtype IN varchar2 default hr_api.g_varchar2
202 ,p_description IN varchar2 default hr_api.g_varchar2
203 ,p_all_assignments IN varchar2 default 'N'
204 ,p_pem_attribute_category IN varchar2 default hr_api.g_varchar2
205 ,p_pem_attribute1 IN varchar2 default hr_api.g_varchar2
206 ,p_pem_attribute2 IN varchar2 default hr_api.g_varchar2
207 ,p_pem_attribute3 IN varchar2 default hr_api.g_varchar2
208 ,p_pem_attribute4 IN varchar2 default hr_api.g_varchar2
209 ,p_pem_attribute5 IN varchar2 default hr_api.g_varchar2
210 ,p_pem_attribute6 IN varchar2 default hr_api.g_varchar2
211 ,p_pem_attribute7 IN varchar2 default hr_api.g_varchar2
212 ,p_pem_attribute8 IN varchar2 default hr_api.g_varchar2
213 ,p_pem_attribute9 IN varchar2 default hr_api.g_varchar2
214 ,p_pem_attribute10 IN varchar2 default hr_api.g_varchar2
215 ,p_pem_attribute11 IN varchar2 default hr_api.g_varchar2
216 ,p_pem_attribute12 IN varchar2 default hr_api.g_varchar2
217 ,p_pem_attribute13 IN varchar2 default hr_api.g_varchar2
218 ,p_pem_attribute14 IN varchar2 default hr_api.g_varchar2
219 ,p_pem_attribute15 IN varchar2 default hr_api.g_varchar2
220 ,p_pem_attribute16 IN varchar2 default hr_api.g_varchar2
221 ,p_pem_attribute17 IN varchar2 default hr_api.g_varchar2
222 ,p_pem_attribute18 IN varchar2 default hr_api.g_varchar2
223 ,p_pem_attribute19 IN varchar2 default hr_api.g_varchar2
224 ,p_pem_attribute20 IN varchar2 default hr_api.g_varchar2
225 ,p_pem_attribute21 IN varchar2 default hr_api.g_varchar2
226 ,p_pem_attribute22 IN varchar2 default hr_api.g_varchar2
227 ,p_pem_attribute23 IN varchar2 default hr_api.g_varchar2
228 ,p_pem_attribute24 IN varchar2 default hr_api.g_varchar2
229 ,p_pem_attribute25 IN varchar2 default hr_api.g_varchar2
230 ,p_pem_attribute26 IN varchar2 default hr_api.g_varchar2
231 ,p_pem_attribute27 IN varchar2 default hr_api.g_varchar2
232 ,p_pem_attribute28 IN varchar2 default hr_api.g_varchar2
233 ,p_pem_attribute29 IN varchar2 default hr_api.g_varchar2
234 ,p_pem_attribute30 IN varchar2 default hr_api.g_varchar2
235 ,p_termination_reason IN varchar2 default hr_api.g_varchar2
236 ,p_object_version_number IN OUT NOCOPY number
237 ) is
238
239 --
240 -- Declare cursors and local variables
241 --
242
243 l_proc varchar2(72) ;
244 l_effective_date date;
245 l_legislation_code per_business_groups.legislation_code%type;
246 l_discard_varchar2 varchar2(30);
247 --
248
249 cursor check_legislation
250 (c_previous_employer_id per_previous_employers.previous_employer_id%TYPE,
251 c_effective_date date
252 )
253 is
254 select bgp.legislation_code
255 from per_people_f per,
256 per_business_groups bgp,
257 per_previous_employers pem
258 where per.business_group_id = bgp.business_group_id
259 and per.person_id = pem.person_id
260 and pem.previous_employer_id = c_previous_employer_id
261 and c_effective_date
262 between per.effective_start_date and per.effective_end_date;
263 --
264 begin
265
266 l_proc := g_package||'update_ae_previous_employer';
267 if g_debug then
268 hr_utility.set_location('Entering:'|| l_proc, 5);
269 end if;
270 --
271 -- Initialise local variables
272 --
273 l_effective_date := trunc(p_effective_date);
274 --
275 -- Validation in addition to Row Handlers
276 --
277 -- Check that the person exists.
278 --
279 open check_legislation(p_previous_employer_id, l_effective_date);
280 fetch check_legislation into l_legislation_code;
281 if check_legislation%notfound then
282 close check_legislation;
283 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
284 hr_utility.raise_error;
285 end if;
286 close check_legislation;
287 if g_debug then
288 hr_utility.set_location(l_proc, 20);
289 end if;
290 --
291 -- Check that the legislation of the specified business group is 'AE'.
292 --
293 if l_legislation_code <> 'AE' then
294 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
295 hr_utility.set_message_token('LEG_CODE','AE');
296 hr_utility.raise_error;
297 end if;
298 if g_debug then
299 hr_utility.set_location(l_proc, 30);
300 end if;
301 --
302 -- Create the previous employer record using the create_previous_employer
303 --
304 hr_previous_employment_api.update_previous_employer
305 ( p_effective_date => l_effective_date
306 ,p_validate => p_validate
307 ,p_previous_employer_id => p_previous_employer_id
308 ,p_start_date => p_start_date
309 ,p_end_date => p_end_date
310 ,p_period_years => p_period_years
311 ,p_period_months => p_period_months
312 ,p_period_days => p_period_days
313 ,p_employer_name => p_employer_name
314 ,p_employer_country => p_employer_country
315 ,p_employer_address => p_employer_address
316 ,p_employer_type => p_employer_type
317 ,p_employer_subtype => p_employer_subtype
318 ,p_description => p_description
319 ,p_all_assignments => p_all_assignments
320 ,p_pem_attribute_category => p_pem_attribute_category
321 ,p_pem_attribute1 => p_pem_attribute1
322 ,p_pem_attribute2 => p_pem_attribute2
323 ,p_pem_attribute3 => p_pem_attribute3
324 ,p_pem_attribute4 => p_pem_attribute4
325 ,p_pem_attribute5 => p_pem_attribute5
326 ,p_pem_attribute6 => p_pem_attribute6
327 ,p_pem_attribute7 => p_pem_attribute7
328 ,p_pem_attribute8 => p_pem_attribute8
329 ,p_pem_attribute9 => p_pem_attribute9
330 ,p_pem_attribute10 => p_pem_attribute10
331 ,p_pem_attribute11 => p_pem_attribute11
332 ,p_pem_attribute12 => p_pem_attribute12
333 ,p_pem_attribute13 => p_pem_attribute13
334 ,p_pem_attribute14 => p_pem_attribute14
335 ,p_pem_attribute15 => p_pem_attribute15
336 ,p_pem_attribute16 => p_pem_attribute16
337 ,p_pem_attribute17 => p_pem_attribute17
338 ,p_pem_attribute18 => p_pem_attribute18
339 ,p_pem_attribute19 => p_pem_attribute19
340 ,p_pem_attribute20 => p_pem_attribute20
341 ,p_pem_attribute21 => p_pem_attribute21
342 ,p_pem_attribute22 => p_pem_attribute22
343 ,p_pem_attribute23 => p_pem_attribute23
344 ,p_pem_attribute24 => p_pem_attribute24
345 ,p_pem_attribute25 => p_pem_attribute25
346 ,p_pem_attribute26 => p_pem_attribute26
347 ,p_pem_attribute27 => p_pem_attribute27
348 ,p_pem_attribute28 => p_pem_attribute28
349 ,p_pem_attribute29 => p_pem_attribute29
350 ,p_pem_attribute30 => p_pem_attribute30
351 ,p_pem_information_category => 'AE'
352 ,p_pem_information1 => p_termination_reason
353 ,p_object_version_number => p_object_version_number
354 );
355 --
356 if g_debug then
357 hr_utility.set_location(' Leaving:'||l_proc, 40);
358 end if;
359 --
360 end update_ae_previous_employer;
361
362 end hr_ae_previous_employment_api;