[Home] [Help]
PACKAGE BODY: APPS.PER_PJO_BUS
Source
1 Package Body per_pjo_bus as
2 /* $Header: pepjorhi.pkb 120.0.12010000.2 2008/08/06 09:28:19 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_pjo_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_previous_job_id number default null;
15 g_previous_employer_id number default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_previous_job_id in number
23 ,p_associated_column1 in varchar2 default null
24 ) is
25 --
26 -- Declare cursor
27 --
28 cursor csr_sec_grp is
29 select pbg.security_group_id
30 from per_business_groups pbg
31 , per_previous_jobs pjo
32 , per_all_people_f ppf
33 , per_previous_employers pem
34 where pjo.previous_job_id = p_previous_job_id
35 and pbg.business_group_id = ppf.business_group_id
36 and pem.person_id = ppf.person_id
37 and pem.previous_employer_id = pjo.previous_employer_id;
38 --
39 -- Declare local variables
40 --
41 l_security_group_id number;
42 l_proc varchar2(72) := g_package||'set_security_group_id';
43 --
44 begin
45 --
46 hr_utility.set_location('Entering:'|| l_proc, 10);
47 --
48 -- Ensure that all the mandatory parameter are not null
49 --
50 hr_api.mandatory_arg_error
51 (p_api_name => l_proc
52 ,p_argument => 'previous_job_id'
53 ,p_argument_value => p_previous_job_id
54 );
55 --
56 open csr_sec_grp;
57 fetch csr_sec_grp into l_security_group_id;
58 --
59 if csr_sec_grp%notfound then
60 --
61 close csr_sec_grp;
62 --
63 -- The primary key is invalid therefore we must error
64 --
65 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
66 hr_multi_message.add
67 (p_associated_column1
68 => nvl(p_associated_column1,'PREVIOUS_JOB_ID')
69 );
70 --
71 else
72 close csr_sec_grp;
73 --
74 -- Set the security_group_id in CLIENT_INFO
75 --
76 hr_api.set_security_group_id
77 (p_security_group_id => l_security_group_id
78 );
79 end if;
80 --
81 hr_utility.set_location(' Leaving:'|| l_proc, 20);
82 end set_security_group_id;
83 --
84 -- ---------------------------------------------------------------------------
85 -- |---------------------< return_legislation_code >-------------------------|
86 -- ---------------------------------------------------------------------------
87 --
88 Function return_legislation_code
89 (p_previous_job_id in number
90 )
91 Return Varchar2 Is
92 --
93 -- Declare cursor
94 --
95 cursor csr_leg_code is
96 select pbg.legislation_code
97 from per_business_groups pbg
98 , per_previous_jobs pjo
99 , per_all_people_f ppf
100 , per_previous_employers pem
101 where pjo.previous_job_id = p_previous_job_id
102 and ppf.business_group_id = pbg.business_group_id(+)
103 and pem.person_id = ppf.person_id(+)
104 and pjo.previous_employer_id = pem.previous_employer_id;
105 --
106 -- Declare local variables
107 --
108 l_legislation_code varchar2(150);
109 l_proc varchar2(72) := g_package||'return_legislation_code';
110 --
111 Begin
112 --
113 hr_utility.set_location('Entering:'|| l_proc, 10);
114 --
115 -- Ensure that all the mandatory parameter are not null
116 --
117 hr_api.mandatory_arg_error
118 (p_api_name => l_proc
119 ,p_argument => 'previous_job_id'
120 ,p_argument_value => p_previous_job_id
121 );
122 --
123 if ( nvl(per_pjo_bus.g_previous_job_id, hr_api.g_number)
124 = p_previous_job_id) then
125 --
126 -- The legislation code has already been found with a previous
127 -- call to this function. Just return the value in the global
128 -- variable.
129 --
130 l_legislation_code := per_pjo_bus.g_legislation_code;
131 hr_utility.set_location(l_proc, 20);
132 else
133 --
134 -- The ID is different to the last call to this function
135 -- or this is the first call to this function.
136 --
137 open csr_leg_code;
138 fetch csr_leg_code into l_legislation_code;
139 --
140 if csr_leg_code%notfound then
141 --
142 -- The primary key is invalid therefore we must error
143 --
144 close csr_leg_code;
145 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
146 fnd_message.raise_error;
147 end if;
148 hr_utility.set_location(l_proc,30);
149 --
150 -- Set the global variables so the values are
151 -- available for the next call to this function.
152 --
153 close csr_leg_code;
154 per_pjo_bus.g_previous_job_id := p_previous_job_id;
155 per_pjo_bus.g_legislation_code := l_legislation_code;
156 end if;
157 hr_utility.set_location(' Leaving:'|| l_proc, 40);
158 return l_legislation_code;
159 end return_legislation_code;
160 --
161 -- ----------------------------------------------------------------------------
162 -- |-----------------------------< chk_ddf >----------------------------------|
163 -- ----------------------------------------------------------------------------
164 --
165 -- Description:
166 -- Validates all the Developer Descriptive Flexfield values.
167 --
168 -- Prerequisites:
169 -- All other columns have been validated. Must be called as the
170 -- last step from insert_validate and update_validate.
171 --
172 -- In Arguments:
173 -- p_rec
174 --
175 -- Post Success:
176 -- If the Developer Descriptive Flexfield structure column and data values
177 -- are all valid this procedure will end normally and processing will
178 -- continue.
179 --
180 -- Post Failure:
181 -- If the Developer Descriptive Flexfield structure column value or any of
182 -- the data values are invalid then an application error is raised as
183 -- a PL/SQL exception.
184 --
185 -- Access Status:
186 -- Internal Row Handler Use Only.
187 --
188 -- ----------------------------------------------------------------------------
189 procedure chk_ddf
190 (p_rec in per_pjo_shd.g_rec_type
191 ) is
192 --
193 l_proc varchar2(72) := g_package || 'chk_ddf';
194 --
195 begin
196 hr_utility.set_location('Entering:'||l_proc,10);
197 --
198 if ((p_rec.previous_job_id is not null) and (
199 nvl(per_pjo_shd.g_old_rec.pjo_information_category, hr_api.g_varchar2) <>
200 nvl(p_rec.pjo_information_category, hr_api.g_varchar2) or
201 nvl(per_pjo_shd.g_old_rec.pjo_information1, hr_api.g_varchar2) <>
202 nvl(p_rec.pjo_information1, hr_api.g_varchar2) or
203 nvl(per_pjo_shd.g_old_rec.pjo_information2, hr_api.g_varchar2) <>
204 nvl(p_rec.pjo_information2, hr_api.g_varchar2) or
205 nvl(per_pjo_shd.g_old_rec.pjo_information3, hr_api.g_varchar2) <>
206 nvl(p_rec.pjo_information3, hr_api.g_varchar2) or
207 nvl(per_pjo_shd.g_old_rec.pjo_information4, hr_api.g_varchar2) <>
208 nvl(p_rec.pjo_information4, hr_api.g_varchar2) or
209 nvl(per_pjo_shd.g_old_rec.pjo_information5, hr_api.g_varchar2) <>
210 nvl(p_rec.pjo_information5, hr_api.g_varchar2) or
211 nvl(per_pjo_shd.g_old_rec.pjo_information6, hr_api.g_varchar2) <>
212 nvl(p_rec.pjo_information6, hr_api.g_varchar2) or
213 nvl(per_pjo_shd.g_old_rec.pjo_information7, hr_api.g_varchar2) <>
214 nvl(p_rec.pjo_information7, hr_api.g_varchar2) or
215 nvl(per_pjo_shd.g_old_rec.pjo_information8, hr_api.g_varchar2) <>
216 nvl(p_rec.pjo_information8, hr_api.g_varchar2) or
217 nvl(per_pjo_shd.g_old_rec.pjo_information9, hr_api.g_varchar2) <>
218 nvl(p_rec.pjo_information9, hr_api.g_varchar2) or
219 nvl(per_pjo_shd.g_old_rec.pjo_information10, hr_api.g_varchar2) <>
220 nvl(p_rec.pjo_information10, hr_api.g_varchar2) or
221 nvl(per_pjo_shd.g_old_rec.pjo_information11, hr_api.g_varchar2) <>
222 nvl(p_rec.pjo_information11, hr_api.g_varchar2) or
223 nvl(per_pjo_shd.g_old_rec.pjo_information12, hr_api.g_varchar2) <>
224 nvl(p_rec.pjo_information12, hr_api.g_varchar2) or
225 nvl(per_pjo_shd.g_old_rec.pjo_information13, hr_api.g_varchar2) <>
226 nvl(p_rec.pjo_information13, hr_api.g_varchar2) or
227 nvl(per_pjo_shd.g_old_rec.pjo_information14, hr_api.g_varchar2) <>
228 nvl(p_rec.pjo_information14, hr_api.g_varchar2) or
229 nvl(per_pjo_shd.g_old_rec.pjo_information15, hr_api.g_varchar2) <>
230 nvl(p_rec.pjo_information15, hr_api.g_varchar2) or
231 nvl(per_pjo_shd.g_old_rec.pjo_information16, hr_api.g_varchar2) <>
232 nvl(p_rec.pjo_information16, hr_api.g_varchar2) or
233 nvl(per_pjo_shd.g_old_rec.pjo_information17, hr_api.g_varchar2) <>
234 nvl(p_rec.pjo_information17, hr_api.g_varchar2) or
235 nvl(per_pjo_shd.g_old_rec.pjo_information18, hr_api.g_varchar2) <>
236 nvl(p_rec.pjo_information18, hr_api.g_varchar2) or
237 nvl(per_pjo_shd.g_old_rec.pjo_information19, hr_api.g_varchar2) <>
238 nvl(p_rec.pjo_information19, hr_api.g_varchar2) or
239 nvl(per_pjo_shd.g_old_rec.pjo_information20, hr_api.g_varchar2) <>
240 nvl(p_rec.pjo_information20, hr_api.g_varchar2) or
241 nvl(per_pjo_shd.g_old_rec.pjo_information21, hr_api.g_varchar2) <>
242 nvl(p_rec.pjo_information21, hr_api.g_varchar2) or
243 nvl(per_pjo_shd.g_old_rec.pjo_information22, hr_api.g_varchar2) <>
244 nvl(p_rec.pjo_information22, hr_api.g_varchar2) or
245 nvl(per_pjo_shd.g_old_rec.pjo_information23, hr_api.g_varchar2) <>
246 nvl(p_rec.pjo_information23, hr_api.g_varchar2) or
247 nvl(per_pjo_shd.g_old_rec.pjo_information24, hr_api.g_varchar2) <>
248 nvl(p_rec.pjo_information24, hr_api.g_varchar2) or
249 nvl(per_pjo_shd.g_old_rec.pjo_information25, hr_api.g_varchar2) <>
250 nvl(p_rec.pjo_information25, hr_api.g_varchar2) or
251 nvl(per_pjo_shd.g_old_rec.pjo_information26, hr_api.g_varchar2) <>
252 nvl(p_rec.pjo_information26, hr_api.g_varchar2) or
253 nvl(per_pjo_shd.g_old_rec.pjo_information27, hr_api.g_varchar2) <>
254 nvl(p_rec.pjo_information27, hr_api.g_varchar2) or
255 nvl(per_pjo_shd.g_old_rec.pjo_information28, hr_api.g_varchar2) <>
256 nvl(p_rec.pjo_information28, hr_api.g_varchar2) or
257 nvl(per_pjo_shd.g_old_rec.pjo_information29, hr_api.g_varchar2) <>
258 nvl(p_rec.pjo_information29, hr_api.g_varchar2) or
259 nvl(per_pjo_shd.g_old_rec.pjo_information30, hr_api.g_varchar2) <>
260 nvl(p_rec.pjo_information30, hr_api.g_varchar2) ))
261 or (p_rec.previous_job_id is null) then
262 --
263 -- Only execute the validation if absolutely necessary:
264 -- a) During update, the structure column value or any
265 -- of the attribute values have actually changed.
266 -- b) During insert.
267 --
268 hr_dflex_utility.ins_or_upd_descflex_attribs
269 (p_appl_short_name => 'PER'
270 ,p_descflex_name => 'Previous Job Developer DF'
271 ,p_attribute_category => p_rec.pjo_information_category
272 ,p_attribute1_name => 'PJO_INFORMATION1'
273 ,p_attribute1_value => p_rec.pjo_information1
274 ,p_attribute2_name => 'PJO_INFORMATION2'
275 ,p_attribute2_value => p_rec.pjo_information2
276 ,p_attribute3_name => 'PJO_INFORMATION3'
277 ,p_attribute3_value => p_rec.pjo_information3
278 ,p_attribute4_name => 'PJO_INFORMATION4'
279 ,p_attribute4_value => p_rec.pjo_information4
280 ,p_attribute5_name => 'PJO_INFORMATION5'
281 ,p_attribute5_value => p_rec.pjo_information5
282 ,p_attribute6_name => 'PJO_INFORMATION6'
283 ,p_attribute6_value => p_rec.pjo_information6
284 ,p_attribute7_name => 'PJO_INFORMATION7'
285 ,p_attribute7_value => p_rec.pjo_information7
286 ,p_attribute8_name => 'PJO_INFORMATION8'
287 ,p_attribute8_value => p_rec.pjo_information8
288 ,p_attribute9_name => 'PJO_INFORMATION9'
289 ,p_attribute9_value => p_rec.pjo_information9
290 ,p_attribute10_name => 'PJO_INFORMATION10'
291 ,p_attribute10_value => p_rec.pjo_information10
292 ,p_attribute11_name => 'PJO_INFORMATION11'
293 ,p_attribute11_value => p_rec.pjo_information11
294 ,p_attribute12_name => 'PJO_INFORMATION12'
295 ,p_attribute12_value => p_rec.pjo_information12
296 ,p_attribute13_name => 'PJO_INFORMATION13'
297 ,p_attribute13_value => p_rec.pjo_information13
298 ,p_attribute14_name => 'PJO_INFORMATION14'
299 ,p_attribute14_value => p_rec.pjo_information14
300 ,p_attribute15_name => 'PJO_INFORMATION15'
301 ,p_attribute15_value => p_rec.pjo_information15
302 ,p_attribute16_name => 'PJO_INFORMATION16'
306 ,p_attribute18_name => 'PJO_INFORMATION18'
303 ,p_attribute16_value => p_rec.pjo_information16
304 ,p_attribute17_name => 'PJO_INFORMATION17'
305 ,p_attribute17_value => p_rec.pjo_information17
307 ,p_attribute18_value => p_rec.pjo_information18
308 ,p_attribute19_name => 'PJO_INFORMATION19'
309 ,p_attribute19_value => p_rec.pjo_information19
310 ,p_attribute20_name => 'PJO_INFORMATION20'
311 ,p_attribute20_value => p_rec.pjo_information20
312 ,p_attribute21_name => 'PJO_INFORMATION21'
313 ,p_attribute21_value => p_rec.pjo_information21
314 ,p_attribute22_name => 'PJO_INFORMATION22'
315 ,p_attribute22_value => p_rec.pjo_information22
316 ,p_attribute23_name => 'PJO_INFORMATION23'
317 ,p_attribute23_value => p_rec.pjo_information23
318 ,p_attribute24_name => 'PJO_INFORMATION24'
319 ,p_attribute24_value => p_rec.pjo_information24
320 ,p_attribute25_name => 'PJO_INFORMATION25'
321 ,p_attribute25_value => p_rec.pjo_information25
322 ,p_attribute26_name => 'PJO_INFORMATION26'
323 ,p_attribute26_value => p_rec.pjo_information26
324 ,p_attribute27_name => 'PJO_INFORMATION27'
325 ,p_attribute27_value => p_rec.pjo_information27
326 ,p_attribute28_name => 'PJO_INFORMATION28'
327 ,p_attribute28_value => p_rec.pjo_information28
328 ,p_attribute29_name => 'PJO_INFORMATION29'
329 ,p_attribute29_value => p_rec.pjo_information29
330 ,p_attribute30_name => 'PJO_INFORMATION30'
331 ,p_attribute30_value => p_rec.pjo_information30
332 );
333 end if;
334 --
335 hr_utility.set_location(' Leaving:'||l_proc,20);
336 end chk_ddf;
337 --
338 -- ----------------------------------------------------------------------------
339 -- |------------------------------< chk_df >----------------------------------|
340 -- ----------------------------------------------------------------------------
341 --
342 -- Description:
343 -- Validates all the Descriptive Flexfield values.
344 --
345 -- Prerequisites:
346 -- All other columns have been validated. Must be called as the
347 -- last step from insert_validate and update_validate.
348 --
349 -- In Arguments:
350 -- p_rec
351 --
352 -- Post Success:
353 -- If the Descriptive Flexfield structure column and data values are
354 -- all valid this procedure will end normally and processing will
355 -- continue.
356 --
357 -- Post Failure:
358 -- If the Descriptive Flexfield structure column value or any of
359 -- the data values are invalid then an application error is raised as
360 -- a PL/SQL exception.
361 --
362 -- Access Status:
363 -- Internal Row Handler Use Only.
364 --
365 -- ----------------------------------------------------------------------------
366 procedure chk_df
367 (p_rec in per_pjo_shd.g_rec_type
368 ) is
369 --
370 l_proc varchar2(72) := g_package || 'chk_df';
371 --
372 begin
373 hr_utility.set_location('Entering:'||l_proc,10);
374 --
375 if ((p_rec.previous_job_id is not null) and (
376 nvl(per_pjo_shd.g_old_rec.pjo_attribute_category, hr_api.g_varchar2) <>
377 nvl(p_rec.pjo_attribute_category, hr_api.g_varchar2) or
378 nvl(per_pjo_shd.g_old_rec.pjo_attribute1, hr_api.g_varchar2) <>
379 nvl(p_rec.pjo_attribute1, hr_api.g_varchar2) or
380 nvl(per_pjo_shd.g_old_rec.pjo_attribute2, hr_api.g_varchar2) <>
381 nvl(p_rec.pjo_attribute2, hr_api.g_varchar2) or
382 nvl(per_pjo_shd.g_old_rec.pjo_attribute3, hr_api.g_varchar2) <>
383 nvl(p_rec.pjo_attribute3, hr_api.g_varchar2) or
384 nvl(per_pjo_shd.g_old_rec.pjo_attribute4, hr_api.g_varchar2) <>
385 nvl(p_rec.pjo_attribute4, hr_api.g_varchar2) or
386 nvl(per_pjo_shd.g_old_rec.pjo_attribute5, hr_api.g_varchar2) <>
387 nvl(p_rec.pjo_attribute5, hr_api.g_varchar2) or
388 nvl(per_pjo_shd.g_old_rec.pjo_attribute6, hr_api.g_varchar2) <>
389 nvl(p_rec.pjo_attribute6, hr_api.g_varchar2) or
390 nvl(per_pjo_shd.g_old_rec.pjo_attribute7, hr_api.g_varchar2) <>
391 nvl(p_rec.pjo_attribute7, hr_api.g_varchar2) or
392 nvl(per_pjo_shd.g_old_rec.pjo_attribute8, hr_api.g_varchar2) <>
393 nvl(p_rec.pjo_attribute8, hr_api.g_varchar2) or
394 nvl(per_pjo_shd.g_old_rec.pjo_attribute9, hr_api.g_varchar2) <>
395 nvl(p_rec.pjo_attribute9, hr_api.g_varchar2) or
396 nvl(per_pjo_shd.g_old_rec.pjo_attribute10, hr_api.g_varchar2) <>
397 nvl(p_rec.pjo_attribute10, hr_api.g_varchar2) or
398 nvl(per_pjo_shd.g_old_rec.pjo_attribute11, hr_api.g_varchar2) <>
399 nvl(p_rec.pjo_attribute11, hr_api.g_varchar2) or
400 nvl(per_pjo_shd.g_old_rec.pjo_attribute12, hr_api.g_varchar2) <>
401 nvl(p_rec.pjo_attribute12, hr_api.g_varchar2) or
402 nvl(per_pjo_shd.g_old_rec.pjo_attribute13, hr_api.g_varchar2) <>
403 nvl(p_rec.pjo_attribute13, hr_api.g_varchar2) or
404 nvl(per_pjo_shd.g_old_rec.pjo_attribute14, hr_api.g_varchar2) <>
405 nvl(p_rec.pjo_attribute14, hr_api.g_varchar2) or
406 nvl(per_pjo_shd.g_old_rec.pjo_attribute15, hr_api.g_varchar2) <>
410 nvl(per_pjo_shd.g_old_rec.pjo_attribute17, hr_api.g_varchar2) <>
407 nvl(p_rec.pjo_attribute15, hr_api.g_varchar2) or
408 nvl(per_pjo_shd.g_old_rec.pjo_attribute16, hr_api.g_varchar2) <>
409 nvl(p_rec.pjo_attribute16, hr_api.g_varchar2) or
411 nvl(p_rec.pjo_attribute17, hr_api.g_varchar2) or
412 nvl(per_pjo_shd.g_old_rec.pjo_attribute18, hr_api.g_varchar2) <>
413 nvl(p_rec.pjo_attribute18, hr_api.g_varchar2) or
414 nvl(per_pjo_shd.g_old_rec.pjo_attribute19, hr_api.g_varchar2) <>
415 nvl(p_rec.pjo_attribute19, hr_api.g_varchar2) or
416 nvl(per_pjo_shd.g_old_rec.pjo_attribute20, hr_api.g_varchar2) <>
417 nvl(p_rec.pjo_attribute20, hr_api.g_varchar2) or
418 nvl(per_pjo_shd.g_old_rec.pjo_attribute21, hr_api.g_varchar2) <>
419 nvl(p_rec.pjo_attribute21, hr_api.g_varchar2) or
420 nvl(per_pjo_shd.g_old_rec.pjo_attribute22, hr_api.g_varchar2) <>
421 nvl(p_rec.pjo_attribute22, hr_api.g_varchar2) or
422 nvl(per_pjo_shd.g_old_rec.pjo_attribute23, hr_api.g_varchar2) <>
423 nvl(p_rec.pjo_attribute23, hr_api.g_varchar2) or
424 nvl(per_pjo_shd.g_old_rec.pjo_attribute24, hr_api.g_varchar2) <>
425 nvl(p_rec.pjo_attribute24, hr_api.g_varchar2) or
426 nvl(per_pjo_shd.g_old_rec.pjo_attribute25, hr_api.g_varchar2) <>
427 nvl(p_rec.pjo_attribute25, hr_api.g_varchar2) or
428 nvl(per_pjo_shd.g_old_rec.pjo_attribute26, hr_api.g_varchar2) <>
429 nvl(p_rec.pjo_attribute26, hr_api.g_varchar2) or
430 nvl(per_pjo_shd.g_old_rec.pjo_attribute27, hr_api.g_varchar2) <>
431 nvl(p_rec.pjo_attribute27, hr_api.g_varchar2) or
432 nvl(per_pjo_shd.g_old_rec.pjo_attribute28, hr_api.g_varchar2) <>
433 nvl(p_rec.pjo_attribute28, hr_api.g_varchar2) or
434 nvl(per_pjo_shd.g_old_rec.pjo_attribute29, hr_api.g_varchar2) <>
435 nvl(p_rec.pjo_attribute29, hr_api.g_varchar2) or
436 nvl(per_pjo_shd.g_old_rec.pjo_attribute30, hr_api.g_varchar2) <>
437 nvl(p_rec.pjo_attribute30, hr_api.g_varchar2) ))
438 or (p_rec.previous_job_id is null) then
439 --
440 -- Only execute the validation if absolutely necessary:
441 -- a) During update, the structure column value or any
442 -- of the attribute values have actually changed.
443 -- b) During insert.
444 --
445 hr_dflex_utility.ins_or_upd_descflex_attribs
446 (p_appl_short_name => 'PER'
447 ,p_descflex_name => 'PER_PREVIOUS_JOBS'
448 ,p_attribute_category => p_rec.pjo_attribute_category
449 ,p_attribute1_name => 'PJO_ATTRIBUTE1'
450 ,p_attribute1_value => p_rec.pjo_attribute1
451 ,p_attribute2_name => 'PJO_ATTRIBUTE2'
452 ,p_attribute2_value => p_rec.pjo_attribute2
453 ,p_attribute3_name => 'PJO_ATTRIBUTE3'
454 ,p_attribute3_value => p_rec.pjo_attribute3
455 ,p_attribute4_name => 'PJO_ATTRIBUTE4'
456 ,p_attribute4_value => p_rec.pjo_attribute4
457 ,p_attribute5_name => 'PJO_ATTRIBUTE5'
458 ,p_attribute5_value => p_rec.pjo_attribute5
459 ,p_attribute6_name => 'PJO_ATTRIBUTE6'
460 ,p_attribute6_value => p_rec.pjo_attribute6
461 ,p_attribute7_name => 'PJO_ATTRIBUTE7'
462 ,p_attribute7_value => p_rec.pjo_attribute7
463 ,p_attribute8_name => 'PJO_ATTRIBUTE8'
464 ,p_attribute8_value => p_rec.pjo_attribute8
465 ,p_attribute9_name => 'PJO_ATTRIBUTE9'
466 ,p_attribute9_value => p_rec.pjo_attribute9
467 ,p_attribute10_name => 'PJO_ATTRIBUTE10'
468 ,p_attribute10_value => p_rec.pjo_attribute10
469 ,p_attribute11_name => 'PJO_ATTRIBUTE11'
470 ,p_attribute11_value => p_rec.pjo_attribute11
471 ,p_attribute12_name => 'PJO_ATTRIBUTE12'
472 ,p_attribute12_value => p_rec.pjo_attribute12
473 ,p_attribute13_name => 'PJO_ATTRIBUTE13'
474 ,p_attribute13_value => p_rec.pjo_attribute13
475 ,p_attribute14_name => 'PJO_ATTRIBUTE14'
476 ,p_attribute14_value => p_rec.pjo_attribute14
477 ,p_attribute15_name => 'PJO_ATTRIBUTE15'
478 ,p_attribute15_value => p_rec.pjo_attribute15
479 ,p_attribute16_name => 'PJO_ATTRIBUTE16'
480 ,p_attribute16_value => p_rec.pjo_attribute16
481 ,p_attribute17_name => 'PJO_ATTRIBUTE17'
482 ,p_attribute17_value => p_rec.pjo_attribute17
483 ,p_attribute18_name => 'PJO_ATTRIBUTE18'
484 ,p_attribute18_value => p_rec.pjo_attribute18
485 ,p_attribute19_name => 'PJO_ATTRIBUTE19'
486 ,p_attribute19_value => p_rec.pjo_attribute19
487 ,p_attribute20_name => 'PJO_ATTRIBUTE20'
488 ,p_attribute20_value => p_rec.pjo_attribute20
489 ,p_attribute21_name => 'PJO_ATTRIBUTE21'
490 ,p_attribute21_value => p_rec.pjo_attribute21
491 ,p_attribute22_name => 'PJO_ATTRIBUTE22'
492 ,p_attribute22_value => p_rec.pjo_attribute22
493 ,p_attribute23_name => 'PJO_ATTRIBUTE23'
494 ,p_attribute23_value => p_rec.pjo_attribute23
498 ,p_attribute25_value => p_rec.pjo_attribute25
495 ,p_attribute24_name => 'PJO_ATTRIBUTE24'
496 ,p_attribute24_value => p_rec.pjo_attribute24
497 ,p_attribute25_name => 'PJO_ATTRIBUTE25'
499 ,p_attribute26_name => 'PJO_ATTRIBUTE26'
500 ,p_attribute26_value => p_rec.pjo_attribute26
501 ,p_attribute27_name => 'PJO_ATTRIBUTE27'
502 ,p_attribute27_value => p_rec.pjo_attribute27
503 ,p_attribute28_name => 'PJO_ATTRIBUTE28'
504 ,p_attribute28_value => p_rec.pjo_attribute28
505 ,p_attribute29_name => 'PJO_ATTRIBUTE29'
506 ,p_attribute29_value => p_rec.pjo_attribute29
507 ,p_attribute30_name => 'PJO_ATTRIBUTE30'
508 ,p_attribute30_value => p_rec.pjo_attribute30
509 );
510 end if;
511 --
512 hr_utility.set_location(' Leaving:'||l_proc,20);
513 end chk_df;
514 --
515 -- ----------------------------------------------------------------------------
516 -- |-----------------------< chk_non_updateable_args >------------------------|
517 -- ----------------------------------------------------------------------------
518 -- {Start Of Comments}
519 --
520 -- Description:
521 -- This procedure is used to ensure that non updateable attributes have
522 -- not been updated. If an attribute has been updated an error is generated.
523 --
524 -- Pre Conditions:
525 -- g_old_rec has been populated with details of the values currently in
526 -- the database.
527 --
528 -- In Arguments:
529 -- p_rec has been populated with the updated values the user would like the
530 -- record set to.
531 --
532 -- Post Success:
533 -- Processing continues if all the non updateable attributes have not
534 -- changed.
535 --
536 -- Post Failure:
537 -- An application error is raised if any of the non updatable attributes
538 -- have been altered.
539 --
540 -- {End Of Comments}
541 -- ----------------------------------------------------------------------------
542 Procedure chk_non_updateable_args
543 (p_effective_date in date
544 ,p_rec in per_pjo_shd.g_rec_type
545 ) IS
546 --
547 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
548 --
549 Begin
550 --
551 -- Only proceed with the validation if a row exists for the current
552 -- record in the HR Schema.
553 --
554 IF NOT per_pjo_shd.api_updating
555 (p_previous_job_id => p_rec.previous_job_id
556 ,p_object_version_number => p_rec.object_version_number
557 ) THEN
558 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
559 fnd_message.set_token('PROCEDURE ', l_proc);
560 fnd_message.set_token('STEP ', '5');
561 fnd_message.raise_error;
562 END IF;
563 --
564 -- Add checks to ensure non-updateable args have
565 -- not been updated.
566 if per_pjo_shd.g_old_rec.previous_job_id <> p_rec.previous_job_id
567 then
568 hr_api.argument_changed_error
569 (p_api_name => l_proc
570 ,p_argument => 'previous_job_id'
571 ,p_base_table => per_pjo_shd.g_tab_nam
572 );
573 end if;
574 --
575 hr_utility.set_location(l_proc,15);
576 if per_pjo_shd.g_old_rec.previous_employer_id <> p_rec.previous_employer_id
577 then
578 hr_api.argument_changed_error
579 (p_api_name => l_proc
580 ,p_argument => 'previous_employer_id'
581 ,p_base_table => per_pjo_shd.g_tab_nam
582 );
583 end if;
584 --
585 End chk_non_updateable_args;
586 --
587 -- ----------------------------------------------------------------------------
588 -- |----------------------< chk_previous_employer_id >------------------------|
589 -- ----------------------------------------------------------------------------
590 -- {Start Of Comments}
591 --
592 -- Description:
593 -- This procedure ensures that previous_employer_id is unique
594 --
595 -- Pre Conditions:
596 -- g_old_rec has been populated with details of the values currently in
597 -- the database.
598 --
599 -- In Arguments:
600 -- p_previous_employer_id
601 --
602 -- Post Success:
603 -- Processing continues if previous_employer_id is valid.
604 --
605 -- Post Failure:
606 -- An application error is raised previous_employer_id is invalid.
607 --
608 -- {End Of Comments}
609 -- ----------------------------------------------------------------------------
610 procedure chk_previous_employer_id
611 (p_previous_employer_id
612 in per_previous_jobs.previous_employer_id%type
613 ,p_previous_job_id
614 in per_previous_jobs.previous_job_id%type
615 ,p_object_version_number
616 in per_previous_jobs.object_version_number%type) is
617 cursor csr_previous_employer_id is
618 select previous_employer_id
619 from per_previous_employers
620 where previous_employer_id = p_previous_employer_id;
621 l_previous_employer_id per_previous_employers.previous_employer_id%type;
622 --
623 l_proc varchar2(72) := g_package||'chk_previous_employer_id';
624 l_api_updating boolean;
628 if p_previous_employer_id is not null then
625 begin
626 hr_utility.set_location('Entering:'||l_proc, 5);
627 --
629 hr_utility.set_location(l_proc, 10);
630 l_api_updating := per_pjo_shd.api_updating(p_previous_job_id
631 => p_previous_job_id
632 ,p_object_version_number
633 => p_object_version_number);
634 if (l_api_updating and
635 (nvl(p_previous_employer_id,hr_api.g_number)
636 <> nvl(per_pjo_shd.g_old_rec.previous_employer_id,hr_api.g_number))
637 ) or
638 (not l_api_updating) then
639 hr_utility.set_location(l_proc, 15);
640 open csr_previous_employer_id;
641 fetch csr_previous_employer_id into l_previous_employer_id;
642 if csr_previous_employer_id%notfound then
643 hr_utility.set_location(l_proc, 20);
644 close csr_previous_employer_id;
645 fnd_message.set_name('PER','HR_289537_PJO_VALID_PR_EMPR_ID');
646 fnd_message.raise_error;
647 end if;
648 if csr_previous_employer_id%isopen then
649 close csr_previous_employer_id;
650 end if;
651 end if;
652 end if;
653 --
654 hr_utility.set_location('Leaving:'||l_proc, 25);
655 exception
656 when app_exception.application_exception then
657 if hr_multi_message.exception_add
658 (p_associated_column1 => 'PER_PREVIOUS_JOBS.PREVIOUS_EMPLOYER_ID'
659 ) then
660 hr_utility.set_location(' Leaving:'|| l_proc, 30);
661 raise;
662 end if;
663 hr_utility.set_location(' Leaving:'|| l_proc, 35);
664 end chk_previous_employer_id;
665 --
666 -- ----------------------------------------------------------------------------
667 -- |---------------------------< chk_start_end_dates >------------------------|
668 -- ----------------------------------------------------------------------------
669 -- {Start Of Comments}
670 --
671 -- Description:
672 -- This procedure ensures that start_date is earlier than end_date.
673 --
674 -- Pre Conditions:
675 -- g_old_rec has been populated with details of the values currently in
676 -- the database.
677 --
678 -- In Arguments:
679 -- p_previous_job_id
680 -- p_object_version_number
681 -- p_start_date
682 -- p_end_date
683 --
684 -- Post Success:
685 -- Processing continues if end_date is greater than start_date is valid.
686 --
687 -- Post Failure:
688 -- An application error is raised previous_employer_id is invalid.
689 --
690 -- {End Of Comments}
691 -- ----------------------------------------------------------------------------
692 procedure chk_start_end_dates
693 (p_previous_job_id
694 in per_previous_jobs.previous_job_id%type
695 ,p_object_version_number
696 in per_previous_jobs.object_version_number%type
697 ,p_start_date
698 in per_previous_jobs.start_date%type
699 ,p_end_date
700 in per_previous_jobs.end_date%type) is
701 l_proc varchar2(72) := g_package||'chk_start_end_dates';
702 l_api_updating boolean;
703 begin
704 hr_utility.set_location('Entering:'||l_proc, 5);
705 --
706 if p_start_date is not null and p_end_date is not null then
707 hr_utility.set_location(l_proc, 10);
708 l_api_updating := per_pjo_shd.api_updating(p_previous_job_id
709 => p_previous_job_id
710 ,p_object_version_number
711 => p_object_version_number);
712 if (l_api_updating and
713 (nvl(p_start_date,hr_api.g_sot)
714 <> nvl(per_pjo_shd.g_old_rec.start_date,hr_api.g_sot)
715 or
716 nvl(p_end_date,hr_api.g_eot)
717 <> nvl(per_pjo_shd.g_old_rec.end_date,hr_api.g_eot))
718 ) or
719 (not l_api_updating) then
720 hr_utility.set_location(l_proc, 15);
721 if p_start_date > p_end_date then
722 hr_utility.set_location(l_proc, 20);
723 fnd_message.set_name('PER','HR_289530_PEM_STRT_END_DATES');
724 fnd_message.set_token('START_DATE',TO_CHAR(p_start_date,'DD-MON-YYYY'),TRUE);
725 fnd_message.set_token('END_DATE',TO_CHAR(p_end_date,'DD-MON-YYYY'),TRUE);
726 fnd_message.raise_error;
727 end if;
728 end if;
729 end if;
730 --
731 hr_utility.set_location('Leaving:'||l_proc, 25);
732 exception
733 when app_exception.application_exception then
734 if hr_multi_message.exception_add
735 (p_associated_column1 => 'PER_PREVIOUS_JOBS.START_DATE'
736 ,p_associated_column2 => 'PER_PREVIOUS_JOBS.END_DATE'
737 ) then
738 hr_utility.set_location(' Leaving:'|| l_proc, 50);
739 raise;
740 end if;
741 hr_utility.set_location(' Leaving:'|| l_proc, 60);
742 end chk_start_end_dates;
743 --
744 -- -----------------------------------------------------------------------
745 -- |---------------------------< chk_employment_category >---------------|
746 -- -----------------------------------------------------------------------
747 -- {Start Of Comments}
748 --
749 -- Description:
750 -- This procedure ensures that employment_category has valid lookup
751 --
755 --
752 -- Pre Conditions:
753 -- g_old_rec has been populated with details of the values currently in
754 -- the database.
756 -- In Arguments:
757 -- p_previous_job_id
758 -- p_object_version_number
759 -- p_employment_category
760 -- p_effective_date
761 --
762 -- Post Success:
763 -- Processing continues if employment_category has valid lookup.
764 --
765 -- Post Failure:
766 -- An application error is raised previous_employer_id is invalid.
767 --
768 -- {End Of Comments}
769 -- ----------------------------------------------------------------------------
770 procedure chk_employment_category
771 (p_previous_job_id
772 in per_previous_jobs.previous_job_id%type
773 ,p_object_version_number
774 in per_previous_jobs.object_version_number%type
775 ,p_employment_category
776 in per_previous_jobs.employment_category%type
777 ,p_effective_date in date) is
778 l_proc varchar2(72) := g_package||'chk_employment_category';
779 l_no_lookup boolean;
780 l_effective_date date := p_effective_date;
781 l_lookup_type fnd_lookups.lookup_type%type := 'EMPLOYEE_CATG';
782 l_lookup_code per_previous_jobs.employment_category%type
783 := p_employment_category;
784 l_api_updating boolean;
785 begin
786 hr_utility.set_location('Entering:'||l_proc, 5);
787 --
788 if p_employment_category is not null then
789 hr_utility.set_location(l_proc, 10);
790 l_api_updating := per_pjo_shd.api_updating(p_previous_job_id
791 => p_previous_job_id
792 ,p_object_version_number
793 => p_object_version_number);
794 if ((l_api_updating and
795 ( nvl(p_employment_category,hr_api.g_varchar2)
796 <> nvl(per_pjo_shd.g_old_rec.employment_category,hr_api.g_varchar2)
797 )
798 ) or
799 (not l_api_updating)) then
800 hr_utility.set_location(l_proc, 15);
801 l_no_lookup := hr_api.not_exists_in_leg_lookups
802 (p_effective_date => l_effective_date
803 ,p_lookup_type => l_lookup_type
804 ,p_lookup_code => l_lookup_code
805 );
806 hr_utility.set_location(l_proc, 20);
807 if l_no_lookup = true then
808 hr_utility.set_location(l_proc, 25);
809 fnd_message.set_name('PER','HR_289538_PJO_EMPT_CAT');
810 fnd_message.raise_error;
811 end if;
812 end if;
813 end if;
814 --
815 hr_utility.set_location('Leaving:'||l_proc, 30);
816 exception
817 when app_exception.application_exception then
818 if hr_multi_message.exception_add
819 (p_associated_column1 => 'PER_PREVIOUS_JOBS.EMPLOYMENT_CATEGORY'
820 ) then
821 hr_utility.set_location(' Leaving:'|| l_proc, 50);
822 raise;
823 end if;
824 hr_utility.set_location(' Leaving:'|| l_proc, 60);
825 end chk_employment_category;
826 --
827 -- -----------------------------------------------------------------------
828 -- |---------------------------< chk_period_years >----------------------|
829 -- -----------------------------------------------------------------------
830 -- {Start Of Comments}
831 --
832 -- Description:
833 -- This procedure ensures that period_years value is between 0 and 99
834 --
835 -- Pre Conditions:
836 -- g_old_rec has been populated with details of the values currently in
837 -- the database.
838 --
839 -- In Arguments:
840 -- p_period_years
841 -- p_previous_job_id
842 -- p_object_version_number
843 --
844 -- Post Success:
845 -- Processing continues if period_years is between 0 and 99
846 --
847 -- Post Failure:
848 -- An application error is raised period_years is invalid.
849 --
850 -- {End Of Comments}
851 -- ----------------------------------------------------------------------------
852 procedure chk_period_years
853 (p_period_years
854 in per_previous_jobs.period_years%type
855 ,p_previous_job_id
856 in per_previous_jobs.previous_job_id%type
857 ,p_object_version_number
858 in per_previous_jobs.object_version_number%type) is
859 l_proc varchar2(72) := g_package||'chk_period_years';
860 l_api_updating boolean;
861 begin
862 hr_utility.set_location('Entering:'||l_proc, 5);
863 --
864 if p_period_years is not null then
865 hr_utility.set_location(l_proc, 10);
866 l_api_updating := per_pjo_shd.api_updating(p_previous_job_id
867 => p_previous_job_id
868 ,p_object_version_number
869 => p_object_version_number);
870 hr_utility.set_location(l_proc, 15);
871 if ((l_api_updating and
872 ( nvl(p_period_years,hr_api.g_number)
873 <> nvl(per_pjo_shd.g_old_rec.period_years,hr_api.g_number)
874 )
875 ) or
876 (not l_api_updating)) then
877 hr_utility.set_location(l_proc, 20);
878 if p_period_years not between 0 and 99 then
879 hr_utility.set_location(l_proc, 25);
883 fnd_message.raise_error;
880 fnd_message.set_name('PER','HR_289534_PEM_VALID_PRD_RANGE');
881 fnd_message.set_token('RANGE_START','0',true);
882 fnd_message.set_token('RANGE_END','99',true);
884 end if;
885 end if;
886 end if;
887 --
888 hr_utility.set_location('Leaving:'||l_proc, 30);
889 exception
890 when app_exception.application_exception then
891 if hr_multi_message.exception_add
892 (p_associated_column1 => 'PER_PREVIOUS_JOBS.PERIOD_YEARS'
893 ) then
894 hr_utility.set_location(' Leaving:'|| l_proc, 40);
895 raise;
896 end if;
897 hr_utility.set_location(' Leaving:'|| l_proc, 50);
898 end chk_period_years;
899 --
900 -- -----------------------------------------------------------------------
901 -- |---------------------------< chk_period_months >---------------------|
902 -- -----------------------------------------------------------------------
903 -- {Start Of Comments}
904 --
905 -- Description:
906 -- This procedure ensures that period_months value is between 0 and 11
907 --
908 -- Pre Conditions:
909 -- g_old_rec has been populated with details of the values currently in
910 -- the database.
911 --
912 -- In Arguments:
913 -- p_period_months
914 -- p_previous_job_id
915 -- p_object_version_number
916 --
917 -- Post Success:
918 -- Processing continues if period_months is between 0 and 11
919 --
920 -- Post Failure:
921 -- An application error is raised period_months is invalid.
922 --
923 -- {End Of Comments}
924 -- ----------------------------------------------------------------------------
925 procedure chk_period_months
926 (p_period_months
927 in per_previous_jobs.period_months%type
928 ,p_previous_job_id
929 in per_previous_jobs.previous_job_id%type
930 ,p_object_version_number
931 in per_previous_jobs.object_version_number%type) is
932 l_proc varchar2(72) := g_package||'chk_period_months';
933 l_api_updating boolean;
934 begin
935 hr_utility.set_location('Entering:'||l_proc, 5);
936 --
937 if p_period_months is not null then
938 hr_utility.set_location(l_proc, 10);
939 l_api_updating := per_pjo_shd.api_updating(p_previous_job_id
940 => p_previous_job_id
941 ,p_object_version_number
942 => p_object_version_number);
943 hr_utility.set_location(l_proc, 15);
944 if ((l_api_updating and
945 ( nvl(p_period_months,hr_api.g_number)
946 <> nvl(per_pjo_shd.g_old_rec.period_months,hr_api.g_number)
947 )
948 ) or
949 (not l_api_updating)) then
950 hr_utility.set_location(l_proc, 20);
951 if p_period_months not between 0 and 11 then
952 hr_utility.set_location(l_proc, 25);
953 fnd_message.set_name('PER','HR_289534_PEM_VALID_PRD_RANGE');
954 fnd_message.set_token('RANGE_START','0',true);
955 fnd_message.set_token('RANGE_END','11',true);
956 fnd_message.raise_error;
957 end if;
958 end if;
959 end if;
960 --
961 hr_utility.set_location('Leaving:'||l_proc, 30);
962 exception
963 when app_exception.application_exception then
964 if hr_multi_message.exception_add
965 (p_associated_column1 => 'PER_PREVIOUS_JOBS.PERIOD_MONTHS'
966 ) then
967 hr_utility.set_location(' Leaving:'|| l_proc, 40);
968 raise;
969 end if;
970 hr_utility.set_location(' Leaving:'|| l_proc, 50);
971 end chk_period_months;
972 --
973 -- -----------------------------------------------------------------------
974 -- |---------------------------< chk_period_days >-----------------------|
975 -- -----------------------------------------------------------------------
976 -- {Start Of Comments}
977 --
978 -- Description:
979 -- This procedure ensures that period_days value is between 0 and 365
980 --
981 -- Pre Conditions:
982 -- g_old_rec has been populated with details of the values currently in
983 -- the database.
984 --
985 -- In Arguments:
986 -- p_period_days
987 -- p_previous_job_id
988 -- p_object_version_number
989 --
990 -- Post Success:
991 -- Processing continues if period_years is between 0 and 365
992 --
993 -- Post Failure:
994 -- An application error is raised period_days is invalid.
995 --
996 -- {End Of Comments}
997 -- ----------------------------------------------------------------------------
998 procedure chk_period_days
999 (p_period_days
1000 in per_previous_jobs.period_days%type
1001 ,p_previous_job_id
1002 in per_previous_jobs.previous_job_id%type
1003 ,p_object_version_number
1004 in per_previous_jobs.object_version_number%type) is
1005 l_proc varchar2(72) := g_package||'chk_period_days';
1006 l_api_updating boolean;
1007 begin
1008 hr_utility.set_location('Entering:'||l_proc, 5);
1009 --
1010 if p_period_days is not null then
1011 hr_utility.set_location(l_proc, 10);
1012 l_api_updating := per_pjo_shd.api_updating(p_previous_job_id
1013 => p_previous_job_id
1014 ,p_object_version_number
1018 ( nvl(p_period_days,hr_api.g_number)
1015 => p_object_version_number);
1016 hr_utility.set_location(l_proc, 15);
1017 if ((l_api_updating and
1019 <> nvl(per_pjo_shd.g_old_rec.period_days,hr_api.g_number)
1020 )
1021 ) or
1022 (not l_api_updating)) then
1023 hr_utility.set_location(l_proc, 20);
1024 if p_period_days not between 0 and 365 then
1025 hr_utility.set_location(l_proc, 25);
1026 fnd_message.set_name('PER','HR_289534_PEM_VALID_PRD_RANGE');
1027 fnd_message.set_token('RANGE_START','0',true);
1028 fnd_message.set_token('RANGE_END','365',true);
1029 fnd_message.raise_error;
1030 end if;
1031 end if;
1032 end if;
1033 --
1034 hr_utility.set_location('Leaving:'||l_proc, 30);
1035 exception
1036 when app_exception.application_exception then
1037 if hr_multi_message.exception_add
1038 (p_associated_column1 => 'PER_PREVIOUS_JOBS.PERIOD_DAYS'
1039 ) then
1040 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1041 raise;
1042 end if;
1043 hr_utility.set_location(' Leaving:'|| l_proc, 50);
1044 end chk_period_days;
1045 --
1046 -- -----------------------------------------------------------------------
1047 -- |---------------------------< chk_pjo_start_end_dates >---------------|
1048 -- -----------------------------------------------------------------------
1049 -- {Start Of Comments}
1050 --
1051 -- Description:
1052 -- This procedure ensures that start_date and end_date of
1053 -- previous job are between start_date and end_date of the previous
1054 -- employer assiciated with.
1055 --
1056 -- Pre Conditions:
1057 -- g_old_rec has been populated with details of the values currently in
1058 -- the database.
1059 --
1060 -- In Arguments:
1061 -- p_previous_job_id
1062 -- p_previous_employer_id
1063 -- p_object_version_number
1064 -- p_start_date
1065 -- p_end_date
1066 --
1067 -- Post Success:
1068 -- Processing continues if start_date and end_date are between
1069 -- start_date and end_date of the previous_employer associated with
1070 --
1071 -- Post Failure:
1072 -- An application error is raised if start_date or end_date are
1073 -- beyond the range of the start_date and end_date of previous employer
1074 --
1075 -- {End Of Comments}
1076 -- ----------------------------------------------------------------------------
1077 procedure chk_pjo_start_end_dates
1078 (p_previous_job_id
1079 in per_previous_jobs.previous_job_id%type
1080 ,p_previous_employer_id
1081 in per_previous_jobs.previous_employer_id%type
1082 ,p_object_version_number
1083 in per_previous_jobs.object_version_number%type
1084 ,p_start_date
1085 in per_previous_jobs.start_date%type
1086 ,p_end_date
1087 in per_previous_jobs.end_date%TYPE
1088 ,p_effective_date
1089 IN per_previous_jobs.start_date%type) is
1090 cursor csr_pem_start_end_dates is
1091 select previous_employer_id
1092 from per_previous_employers
1093 where previous_employer_id = p_previous_employer_id
1094 and (p_start_date not between nvl(start_date,hr_api.g_sot)
1095 and nvl(end_date,hr_api.g_eot)
1096 or p_end_date not between nvl(start_date,hr_api.g_sot)
1097 and nvl(end_date,hr_api.g_eot));
1098 l_previous_employer_id per_previous_employers.previous_employer_id%type;
1099 --
1100 l_proc varchar2(72) := g_package||'chk_pjo_start_end_dates';
1101 l_api_updating boolean;
1102 begin
1103 hr_utility.set_location('Entering:'||l_proc, 5);
1104 --
1105 if hr_multi_message.no_all_inclusive_error
1106 (p_check_column1 => 'PER_PREVIOUS_JOBS.START_DATE'
1107 ,p_check_column2 => 'PER_PREVIOUS_JOBS.END_DATE'
1108 ,p_check_column3 => 'PER_PREVIOUS_JOBS.PREVIOUS_EMPLOYER_ID'
1109 ) THEN
1110 hr_utility.set_location('Entering:'||l_proc, 6);
1111 -- This if condition added for bug 7112425
1112 IF p_start_date is not null AND p_start_date > p_effective_date THEN
1113 fnd_message.set_name('PER','HR_INVAL_ST_DT_PJO');
1114 fnd_message.raise_error;
1115 END IF ;
1116 hr_utility.set_location('Entering:'||l_proc, 7);
1117
1118 if p_start_date is not null or p_end_date is not null then
1119 hr_utility.set_location(l_proc, 10);
1120 l_api_updating := per_pjo_shd.api_updating(p_previous_job_id
1121 => p_previous_job_id
1122 ,p_object_version_number
1123 => p_object_version_number);
1124 if ((l_api_updating or
1125 (nvl(p_start_date,hr_api.g_sot)
1126 <> nvl(per_pjo_shd.g_old_rec.start_date,hr_api.g_sot)
1127 or
1128 nvl(p_end_date,hr_api.g_eot)
1129 <> nvl(per_pjo_shd.g_old_rec.end_date,hr_api.g_eot))
1130 ) or
1131 (not l_api_updating)) then
1132 hr_utility.set_location(l_proc, 15);
1133 open csr_pem_start_end_dates;
1134 fetch csr_pem_start_end_dates into l_previous_employer_id;
1135 if csr_pem_start_end_dates%found then
1136 hr_utility.set_location(l_proc, 20);
1137 close csr_pem_start_end_dates;
1141 if csr_pem_start_end_dates%isopen then
1138 fnd_message.set_name('PER','HR_289542_PJO_START_END_DATES');
1139 fnd_message.raise_error;
1140 end if;
1142 close csr_pem_start_end_dates;
1143 end if;
1144 end if;
1145 end if;
1146 end if;
1147 --
1148 hr_utility.set_location('Leaving:'||l_proc, 25);
1149 exception
1150 when app_exception.application_exception then
1151 if hr_multi_message.exception_add
1152 (p_associated_column1 => 'PER_PREVIOUS_JOBS.START_DATE'
1153 ,p_associated_column2 => 'PER_PREVIOUS_JOBS.END_DATE'
1154 ,p_associated_column3 => 'PER_PREVIOUS_JOBS.PREVIOUS_EMPLOYER_ID'
1155 ) then
1156 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1157 raise;
1158 end if;
1159 hr_utility.set_location(' Leaving:'|| l_proc, 35);
1160 end chk_pjo_start_end_dates;
1161 --
1162 -- -----------------------------------------------------------------------
1163 -- |---------------------------< chk_all_assignments >-------------------|
1164 -- -----------------------------------------------------------------------
1165 -- {Start Of Comments}
1166 --
1167 -- Description:
1168 -- This procedure ensures that all_assignments value is valid
1169 -- and also checks if there are any assignment usages assiciated with
1170 -- this previous job.
1171 --
1172 -- Pre Conditions:
1173 -- g_old_rec has been populated with details of the values currently in
1174 -- the database.
1175 --
1176 -- In Arguments:
1177 -- p_previous_job_id
1178 -- p_object_version_number
1179 -- p_all_assignments
1180 --
1181 -- Post Success:
1182 -- Processing continues if all_assignments value is valid and
1183 -- there are no assignments mappped to this previous job.
1184 --
1185 -- Post Failure:
1186 -- An application error is raised if all_assignments value is not valid
1187 -- or if there are any assignment mappings for this previous job.
1188 --
1189 -- {End Of Comments}
1190 -- ----------------------------------------------------------------------------
1191 procedure chk_all_assignments
1192 (p_previous_job_id
1193 in per_previous_jobs.previous_job_id%type
1194 ,p_object_version_number
1195 in per_previous_jobs.object_version_number%type
1196 ,p_all_assignments
1197 in per_previous_jobs.all_assignments%type) is
1198 cursor csr_pjo_assignments is
1199 select previous_job_usage_id
1200 from per_previous_job_usages
1201 where previous_job_id = p_previous_job_id;
1202 l_previous_job_usage_id per_previous_job_usages.previous_job_usage_id%type;
1203 --
1204 l_proc varchar2(72) := g_package||'chk_all_assignments';
1205 l_api_updating boolean;
1206 begin
1207 hr_utility.set_location('Entering:'||l_proc, 5);
1208 --
1209 if hr_multi_message.no_exclusive_error
1210 (p_check_column1 => 'PER_PREVIOUS_JOBS.PREVIOUS_JOB_ID'
1211 ) then
1212 if p_all_assignments is not null then
1213 hr_utility.set_location(l_proc, 10);
1214 l_api_updating := per_pjo_shd.api_updating(p_previous_job_id
1215 => p_previous_job_id
1216 ,p_object_version_number
1217 => p_object_version_number);
1218 if ((l_api_updating and
1219 nvl(p_all_assignments,hr_api.g_varchar2)
1220 <> nvl(per_pjo_shd.g_old_rec.all_assignments,hr_api.g_varchar2)
1221 ) or
1222 (not l_api_updating)) then
1223 hr_utility.set_location(l_proc, 15);
1224 if p_all_assignments = 'Y' or p_all_assignments = 'N' then
1225 hr_utility.set_location(l_proc, 20);
1226 if p_all_assignments = 'Y' then
1227 hr_utility.set_location(l_proc, 25);
1228 open csr_pjo_assignments;
1229 fetch csr_pjo_assignments into l_previous_job_usage_id;
1230 if csr_pjo_assignments%found then
1231 hr_utility.set_location(l_proc, 30);
1232 close csr_pjo_assignments;
1233 fnd_message.set_name('PER','HR_289546_PEM_ALL_ASG_MOD_NA');
1234 hr_multi_message.add
1235 (p_associated_column1 => 'PER_PREVIOUS_JOBS.
1236 PREVIOUS_JOB_ID'
1237 );
1238 end if;
1239 if csr_pjo_assignments%isopen then
1240 close csr_pjo_assignments;
1241 end if;
1242 end if;
1243 else
1244 hr_utility.set_location(l_proc, 35);
1245 fnd_message.set_name('PER','HR_289545_PEM_VALID_ASGMT_FLAG');
1246 hr_multi_message.add
1247 (p_associated_column1 => 'PER_PREVIOUS_JOBS.PREVIOUS_JOB_ID'
1248 ,p_associated_column2 => 'PER_PREVIOUS_JOBS.ALL_ASSIGNMENTS'
1249 );
1250 end if;
1251 end if;
1252 end if;
1253 end if;
1254 --
1255 hr_utility.set_location('Leaving:'||l_proc, 40);
1256 end chk_all_assignments;
1257 --
1258 -- ----------------------------------------------------------------------------
1259 -- |---------------------------< insert_validate >----------------------------|
1260 -- ----------------------------------------------------------------------------
1261 Procedure insert_validate
1262 (p_effective_date in date
1266 l_proc varchar2(72) := g_package||'insert_validate';
1263 ,p_rec in per_pjo_shd.g_rec_type
1264 ) is
1265 --
1267 --
1268 Begin
1269 hr_utility.set_location('Entering:'||l_proc, 5);
1270 --
1271 -- Call all supporting business operations
1272 hr_utility.set_location(l_proc, 10);
1273 hr_api.mandatory_arg_error
1274 (p_api_name => l_proc
1275 ,p_argument => 'EFFECTIVE_DATE'
1276 ,p_argument_value => p_effective_date
1277 );
1278 --
1279 hr_utility.set_location(l_proc, 15);
1280 chk_previous_employer_id(p_previous_employer_id
1281 => p_rec.previous_employer_id
1282 ,p_previous_job_id
1283 => p_rec.previous_job_id
1284 ,p_object_version_number
1285 => p_rec.object_version_number);
1286 --
1287 hr_utility.set_location(l_proc, 20);
1288 chk_start_end_dates(p_previous_job_id
1289 => p_rec.previous_job_id
1290 ,p_object_version_number
1291 => p_rec.object_version_number
1292 ,p_start_date
1293 => p_rec.start_date
1294 ,p_end_date
1295 => p_rec.end_date);
1296 --
1297 hr_utility.set_location(l_proc, 25);
1298 chk_pjo_start_end_dates(p_previous_job_id
1299 => p_rec.previous_job_id
1300 ,p_previous_employer_id
1301 => p_rec.previous_employer_id
1302 ,p_object_version_number
1303 => p_rec.object_version_number
1304 ,p_start_date
1305 => p_rec.start_date
1306 ,p_end_date
1307 => p_rec.end_date
1308 ,p_effective_date
1309 => p_effective_date); -- bug 7112425
1310 --
1311 hr_utility.set_location(l_proc, 30);
1312 chk_employment_category(p_previous_job_id
1313 => p_rec.previous_job_id
1314 ,p_object_version_number
1315 => p_rec.object_version_number
1316 ,p_employment_category
1317 => p_rec.employment_category
1318 ,p_effective_date
1319 => p_effective_date);
1320 --
1321 hr_utility.set_location(l_proc, 35);
1322 chk_period_years(p_period_years
1323 => p_rec.period_years
1324 ,p_previous_job_id
1325 => p_rec.previous_job_id
1326 ,p_object_version_number
1327 => p_rec.object_version_number);
1328 --
1329 hr_utility.set_location(l_proc, 40);
1330 chk_period_months(p_period_months
1331 => p_rec.period_months
1332 ,p_previous_job_id
1333 => p_rec.previous_job_id
1334 ,p_object_version_number
1335 => p_rec.object_version_number);
1336 --
1337 hr_utility.set_location(l_proc, 45);
1338 chk_period_days(p_period_days
1339 => p_rec.period_days
1340 ,p_previous_job_id
1341 => p_rec.previous_job_id
1342 ,p_object_version_number
1343 => p_rec.object_version_number);
1344 --
1345 hr_utility.set_location(l_proc, 50);
1346 chk_all_assignments(p_previous_job_id
1347 => p_rec.previous_job_id
1348 ,p_object_version_number
1349 => p_rec.object_version_number
1350 ,p_all_assignments
1351 => p_rec.all_assignments);
1352 --
1353 hr_utility.set_location(l_proc, 55);
1354 per_pjo_bus.chk_ddf(p_rec);
1355 --
1356 hr_utility.set_location(l_proc, 60);
1357 per_pjo_bus.chk_df(p_rec);
1358 --
1359 hr_utility.set_location(' Leaving:'||l_proc, 65);
1360 End insert_validate;
1361 --
1362 -- ----------------------------------------------------------------------------
1363 -- |---------------------------< update_validate >----------------------------|
1364 -- ----------------------------------------------------------------------------
1365 Procedure update_validate
1366 (p_effective_date in date
1367 ,p_rec in per_pjo_shd.g_rec_type
1368 ) is
1369 --
1370 l_proc varchar2(72) := g_package||'update_validate';
1371 --
1372 Begin
1373 hr_utility.set_location('Entering:'||l_proc, 5);
1374 --
1375 -- Call all supporting business operations
1376 --
1377 hr_utility.set_location(l_proc, 10);
1378 hr_api.mandatory_arg_error
1379 (p_api_name => l_proc
1380 ,p_argument => 'EFFECTIVE_DATE'
1381 ,p_argument_value => p_effective_date
1382 );
1383 --
1384 chk_non_updateable_args
1385 (p_effective_date => p_effective_date
1386 ,p_rec => p_rec
1387 );
1388 --
1389 hr_utility.set_location(l_proc, 20);
1390 chk_start_end_dates(p_previous_job_id => p_rec.previous_job_id
1391 ,p_object_version_number => p_rec.object_version_number
1392 ,p_start_date => p_rec.start_date
1393 ,p_end_date => p_rec.end_date);
1394 --
1398 ,p_object_version_number
1395 hr_utility.set_location(l_proc, 25);
1396 chk_employment_category(p_previous_job_id
1397 => p_rec.previous_job_id
1399 => p_rec.object_version_number
1400 ,p_employment_category
1401 => p_rec.employment_category
1402 ,p_effective_date
1403 => p_effective_date);
1404 --
1405 hr_utility.set_location(l_proc, 30);
1406 chk_period_years(p_period_years
1407 => p_rec.period_years
1408 ,p_previous_job_id
1409 => p_rec.previous_job_id
1410 ,p_object_version_number
1411 => p_rec.object_version_number);
1412 --
1413 hr_utility.set_location(l_proc, 35);
1414 chk_period_months(p_period_months
1415 => p_rec.period_months
1416 ,p_previous_job_id
1417 => p_rec.previous_job_id
1418 ,p_object_version_number
1419 => p_rec.object_version_number);
1420 --
1421 hr_utility.set_location(l_proc, 40);
1422 chk_period_days(p_period_days
1423 => p_rec.period_days
1424 ,p_previous_job_id
1425 => p_rec.previous_job_id
1426 ,p_object_version_number
1427 => p_rec.object_version_number);
1428 --
1429 hr_utility.set_location(l_proc, 45);
1430 chk_pjo_start_end_dates(p_previous_job_id
1431 => p_rec.previous_job_id
1432 ,p_previous_employer_id
1433 => p_rec.previous_employer_id
1434 ,p_object_version_number
1435 => p_rec.object_version_number
1436 ,p_start_date
1437 => p_rec.start_date
1438 ,p_end_date
1439 => p_rec.end_date
1440 ,p_effective_date
1441 => p_effective_date); -- bug 7112425
1442 --
1443 hr_utility.set_location(l_proc, 50);
1444 chk_all_assignments(p_previous_job_id => p_rec.previous_job_id
1445 ,p_object_version_number => p_rec.object_version_number
1446 ,p_all_assignments => p_rec.all_assignments);
1447 --
1448 hr_utility.set_location(l_proc, 55);
1449 per_pjo_bus.chk_ddf(p_rec);
1450 --
1451 hr_utility.set_location(l_proc, 60);
1452 per_pjo_bus.chk_df(p_rec);
1453 --
1454 hr_utility.set_location(l_proc, 65);
1455 hr_utility.set_location(' Leaving:'||l_proc, 70);
1456 End update_validate;
1457 --
1458 -- ----------------------------------------------------------------------------
1459 -- |---------------------------< delete_validate >----------------------------|
1460 -- ----------------------------------------------------------------------------
1461 Procedure delete_validate
1462 (p_rec in per_pjo_shd.g_rec_type
1463 ) is
1464 --
1465 l_proc varchar2(72) := g_package||'delete_validate';
1466 --
1467 Begin
1468 hr_utility.set_location('Entering:'||l_proc, 5);
1469 --
1470 -- Call all supporting business operations
1471 --
1472 hr_utility.set_location(' Leaving:'||l_proc, 10);
1473 End delete_validate;
1474 --
1475 -- ---------------------------------------------------------------------------
1476 -- |--------------------------< return_leg_code >----------------------------|
1477 -- ---------------------------------------------------------------------------
1478 --
1479 Function return_leg_code(
1480 p_previous_employer_id in number
1481 ) return varchar2 is
1482 --
1483 -- Declare cursor
1484 cursor csr_leg_code is
1485 select pbg.legislation_code
1486 from per_business_groups pbg,
1487 per_all_people_f ppf,
1488 per_previous_employers pem
1489 where pem.previous_employer_id = p_previous_employer_id
1490 and ppf.business_group_id = pbg.business_group_id(+)
1491 and pem.person_id = ppf.person_id(+);
1492 --
1493 -- Declare local variables
1494 l_legislation_code varchar2(150);
1495 l_proc varchar2(72) := g_package||'return_leg_code';
1496 --
1497 Begin
1498 --
1499 hr_utility.set_location('Entering:'|| l_proc, 10);
1500 --
1501 -- Ensure that all the mandatory parameter are not null
1502 hr_api.mandatory_arg_error
1503 (p_api_name => l_proc
1504 ,p_argument => 'p_previous_employer_id'
1505 ,p_argument_value => p_previous_employer_id);
1506 --
1507 if(nvl(per_pjo_bus.g_previous_employer_id, hr_api.g_number) =
1508 p_previous_employer_id) then
1509 --
1510 -- The legislation code has already been found with a previous
1511 -- call to this function. Just return the value in the global variable.
1512 l_legislation_code := per_pjo_bus.g_legislation_code;
1513 hr_utility.set_location(l_proc, 20);
1514 --
1515 else
1516 --
1517 -- The ID is different to the last call to this function
1518 -- or this is the first call to this function.
1519 open csr_leg_code;
1520 fetch csr_leg_code into l_legislation_code;
1521 --
1522 if csr_leg_code%notfound then
1523 --
1524 -- The primary key is invalid therefore we must error
1525 close csr_leg_code;
1526 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1527 hr_utility.raise_error;
1528 --
1529 end if;
1530 --
1531 hr_utility.set_location(l_proc,30);
1532 --
1533 -- Set the global variables so the values are
1534 -- available for the next call to this function.
1535 close csr_leg_code;
1536 per_pjo_bus.g_previous_employer_id := p_previous_employer_id;
1537 per_pjo_bus.g_legislation_code := l_legislation_code;
1538 --
1539 end if;
1540 --
1541 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1542 --
1543 return l_legislation_code;
1544 --
1545 end return_leg_code;
1546 --
1547 end per_pjo_bus;