[Home] [Help]
PACKAGE BODY: APPS.PER_APL_BUS
Source
1 Package Body per_apl_bus as
2 /* $Header: peaplrhi.pkb 120.1 2005/10/25 00:31:11 risgupta noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_apl_bus.'; -- Global package name
9 --
10 --
11 -- ----------------------------------------------------------------------------
12 -- | Local Procedure Definitions |
13 -- ----------------------------------------------------------------------------
14 --
15 -- ----------------------------------------------------------------------------
16 -- |--------------------< check_non_updateable_args >-------------------------|
17 -- ----------------------------------------------------------------------------
18 Procedure check_non_updateable_args(p_rec in per_apl_shd.g_rec_type)
19 is
20 --
21 l_proc varchar2(72) := g_package||'check_non_updateable_args';
22 --
23 Begin
24 hr_utility.set_location('Entering:'||l_proc, 5);
25 --
26 -- Only proceed with validation if a row exists for
27 -- the current record in the HR Schema
28 --
29 if not per_apl_shd.api_updating
30 (p_application_id => p_rec.application_id
31 ,p_object_version_number => p_rec.object_version_number
32 ) then
33 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
34 hr_utility.set_message_token('PROCEDURE', l_proc);
35 hr_utility.set_message_token('STEP', '5');
36 hr_utility.raise_error;
37 end if;
38 --
39 hr_utility.set_location(l_proc, 6);
40 --
41 --
42 if nvl(p_rec.business_group_id, hr_api.g_number) <>
43 per_apl_shd.g_old_rec.business_group_id then
44 hr_api.argument_changed_error
45 (p_api_name => l_proc
46 ,p_argument => 'BUSINESS_GROUP_ID'
47 ,p_base_table => per_apl_shd.g_tab_nam
48 );
49 end if;
50 --
51 if nvl(p_rec.person_id, hr_api.g_number) <>
52 per_apl_shd.g_old_rec.person_id then
53 hr_api.argument_changed_error
54 (p_api_name => l_proc
55 ,p_argument => 'PERSON_ID'
56 ,p_base_table => per_apl_shd.g_tab_nam
57 );
58 end if;
59 hr_utility.set_location(' Leaving:'||l_proc, 14);
60 end check_non_updateable_args;
61 --
62 -- ---------------------------------------------------------------------------
63 -- |----------------------< df_update_validate >---------------------------|
64 -- ---------------------------------------------------------------------------
65 --
66 -- Description:
67 -- Calls the descriptive flex validation routine (hr_dflex_utility)
68 -- if either the attribute_category or attribute1..30 have changed.
69 --
70 -- Pre-conditions:
71 -- Can only be called from update_validate
72 --
73 -- In Arguments:
74 -- p_rec
75 --
76 -- Post Success:
77 -- If the attribute_category and attribute1.30 haven't changed then the
78 -- validation is not performed and the processing continues.
79 -- If the attribute_category or attribute1.30 have changed then routine
80 -- hr_dflex_utility.ins_or_upd_descflex_attribs validates the descriptive
81 -- flex.
82 -- If an exception is not raised then processing continues.
83 --
84 -- Post Failure:
85 -- If an exception is raised within this procedure or lower
86 -- procedure calls, then it is raised through the normal exception
87 -- handling mechanism.
88 --
89 -- Access Status:
90 -- Internal Table Handler Use Only.
91 -- ---------------------------------------------------------------------------
92 procedure df_update_validate
93 (p_rec in per_apl_shd.g_rec_type) is
94 --
95 l_proc varchar2(72) := g_package||'df_update_validate';
96 --
97 begin
98 hr_utility.set_location('Entering:'||l_proc, 10);
99 --
100 if nvl(per_apl_shd.g_old_rec.appl_attribute_category, hr_api.g_varchar2) <>
101 nvl(p_rec.appl_attribute_category, hr_api.g_varchar2) or
102 nvl(per_apl_shd.g_old_rec.appl_attribute1, hr_api.g_varchar2) <>
103 nvl(p_rec.appl_attribute1, hr_api.g_varchar2) or
104 nvl(per_apl_shd.g_old_rec.appl_attribute2, hr_api.g_varchar2) <>
105 nvl(p_rec.appl_attribute2, hr_api.g_varchar2) or
106 nvl(per_apl_shd.g_old_rec.appl_attribute3, hr_api.g_varchar2) <>
107 nvl(p_rec.appl_attribute3, hr_api.g_varchar2) or
108 nvl(per_apl_shd.g_old_rec.appl_attribute4, hr_api.g_varchar2) <>
109 nvl(p_rec.appl_attribute4, hr_api.g_varchar2) or
110 nvl(per_apl_shd.g_old_rec.appl_attribute5, hr_api.g_varchar2) <>
111 nvl(p_rec.appl_attribute5, hr_api.g_varchar2) or
112 nvl(per_apl_shd.g_old_rec.appl_attribute6, hr_api.g_varchar2) <>
113 nvl(p_rec.appl_attribute6, hr_api.g_varchar2) or
114 nvl(per_apl_shd.g_old_rec.appl_attribute7, hr_api.g_varchar2) <>
115 nvl(p_rec.appl_attribute7, hr_api.g_varchar2) or
116 nvl(per_apl_shd.g_old_rec.appl_attribute8, hr_api.g_varchar2) <>
117 nvl(p_rec.appl_attribute8, hr_api.g_varchar2) or
118 nvl(per_apl_shd.g_old_rec.appl_attribute9, hr_api.g_varchar2) <>
119 nvl(p_rec.appl_attribute9, hr_api.g_varchar2) or
120 nvl(per_apl_shd.g_old_rec.appl_attribute10, hr_api.g_varchar2) <>
121 nvl(p_rec.appl_attribute10, hr_api.g_varchar2) or
122 nvl(per_apl_shd.g_old_rec.appl_attribute11, hr_api.g_varchar2) <>
123 nvl(p_rec.appl_attribute11, hr_api.g_varchar2) or
124 nvl(per_apl_shd.g_old_rec.appl_attribute12, hr_api.g_varchar2) <>
125 nvl(p_rec.appl_attribute12, hr_api.g_varchar2) or
126 nvl(per_apl_shd.g_old_rec.appl_attribute13, hr_api.g_varchar2) <>
127 nvl(p_rec.appl_attribute13, hr_api.g_varchar2) or
128 nvl(per_apl_shd.g_old_rec.appl_attribute14, hr_api.g_varchar2) <>
129 nvl(p_rec.appl_attribute14, hr_api.g_varchar2) or
130 nvl(per_apl_shd.g_old_rec.appl_attribute15, hr_api.g_varchar2) <>
131 nvl(p_rec.appl_attribute15, hr_api.g_varchar2) or
132 nvl(per_apl_shd.g_old_rec.appl_attribute16, hr_api.g_varchar2) <>
133 nvl(p_rec.appl_attribute16, hr_api.g_varchar2) or
134 nvl(per_apl_shd.g_old_rec.appl_attribute17, hr_api.g_varchar2) <>
135 nvl(p_rec.appl_attribute17, hr_api.g_varchar2) or
136 nvl(per_apl_shd.g_old_rec.appl_attribute18, hr_api.g_varchar2) <>
137 nvl(p_rec.appl_attribute18, hr_api.g_varchar2) or
138 nvl(per_apl_shd.g_old_rec.appl_attribute19, hr_api.g_varchar2) <>
139 nvl(p_rec.appl_attribute19, hr_api.g_varchar2) or
140 nvl(per_apl_shd.g_old_rec.appl_attribute20, hr_api.g_varchar2) <>
141 nvl(p_rec.appl_attribute20, hr_api.g_varchar2)
142 then
143 -- either the attribute_category or attribute1..30 have changed
144 -- so we must call the DFF validation routine
145 --
146 hr_dflex_utility.ins_or_upd_descflex_attribs(
147 p_appl_short_name => 'PER'
148 ,p_descflex_name => 'PER_APPLICATIONS'
149 ,p_attribute_category => p_rec.appl_attribute_category
150 ,p_attribute1_name => 'APPL_ATTRIBUTE1'
151 ,p_attribute1_value => p_rec.appl_attribute1
152 ,p_attribute2_name => 'APPL_ATTRIBUTE2'
153 ,p_attribute2_value => p_rec.appl_attribute2
154 ,p_attribute3_name => 'APPL_ATTRIBUTE3'
155 ,p_attribute3_value => p_rec.appl_attribute3
156 ,p_attribute4_name => 'APPL_ATTRIBUTE4'
157 ,p_attribute4_value => p_rec.appl_attribute4
158 ,p_attribute5_name => 'APPL_ATTRIBUTE5'
159 ,p_attribute5_value => p_rec.appl_attribute5
160 ,p_attribute6_name => 'APPL_ATTRIBUTE6'
161 ,p_attribute6_value => p_rec.appl_attribute6
162 ,p_attribute7_name => 'APPL_ATTRIBUTE7'
163 ,p_attribute7_value => p_rec.appl_attribute7
164 ,p_attribute8_name => 'APPL_ATTRIBUTE8'
165 ,p_attribute8_value => p_rec.appl_attribute8
166 ,p_attribute9_name => 'APPL_ATTRIBUTE9'
167 ,p_attribute9_value => p_rec.appl_attribute9
168 ,p_attribute10_name => 'APPL_ATTRIBUTE10'
169 ,p_attribute10_value => p_rec.appl_attribute10
170 ,p_attribute11_name => 'APPL_ATTRIBUTE11'
171 ,p_attribute11_value => p_rec.appl_attribute11
172 ,p_attribute12_name => 'APPL_ATTRIBUTE12'
173 ,p_attribute12_value => p_rec.appl_attribute12
174 ,p_attribute13_name => 'APPL_ATTRIBUTE13'
175 ,p_attribute13_value => p_rec.appl_attribute13
176 ,p_attribute14_name => 'APPL_ATTRIBUTE14'
177 ,p_attribute14_value => p_rec.appl_attribute14
178 ,p_attribute15_name => 'APPL_ATTRIBUTE15'
179 ,p_attribute15_value => p_rec.appl_attribute15
180 ,p_attribute16_name => 'APPL_ATTRIBUTE16'
181 ,p_attribute16_value => p_rec.appl_attribute16
182 ,p_attribute17_name => 'APPL_ATTRIBUTE17'
183 ,p_attribute17_value => p_rec.appl_attribute17
184 ,p_attribute18_name => 'APPL_ATTRIBUTE18'
185 ,p_attribute18_value => p_rec.appl_attribute18
186 ,p_attribute19_name => 'APPL_ATTRIBUTE19'
187 ,p_attribute19_value => p_rec.appl_attribute19
188 ,p_attribute20_name => 'APPL_ATTRIBUTE20'
189 ,p_attribute20_value => p_rec.appl_attribute20
190 );
191 --
192 end if;
193 --
194 hr_utility.set_location(' Leaving:'||l_proc, 10);
195 --
196 end df_update_validate;
197 --
198 -- ----------------------------------------------------------------------------
199 -- |---------------------------< insert_validate >----------------------------|
200 -- ----------------------------------------------------------------------------
201 Procedure insert_validate(p_rec in per_apl_shd.g_rec_type
202 ,p_effective_date in date
203 ,p_validate_df_flex in boolean default true) is -- 4689836
204 --
205 l_proc varchar2(72) := g_package||'insert_validate';
206 --
207 Begin
208 hr_utility.set_location('Entering:'||l_proc, 5);
209 --
210 -- Validate Important Attributes
211 --
212 hr_api.validate_bus_grp_id
213 (p_business_group_id => p_rec.business_group_id
214 ,p_associated_column1 => per_apl_shd.g_tab_nam || '.BUSINESS_GROUP_ID'
215 ); -- Validate Bus Grp
216 --
217 --After validating the set of important attributes,
218 --if multiple message detection is enabled and atleast
219 --one error has been found then abort further validation.
220 --
221 hr_multi_message.end_validation_set;
222 --
223 -- Call all supporting business operations
224 --
225 -- Validate PERSON_ID, DATE_RECEIVED_COMBINATION
226 --
227 per_apl_bus.chk_date_received_person_id
228 (p_person_id => p_rec.person_id
229 ,p_application_id => p_rec.application_id
230 ,p_business_group_id => p_rec.business_group_id
231 ,p_date_received => p_rec.date_received
232 ,p_date_end => p_rec.date_end
233 ,p_projected_hire_date => p_rec.projected_hire_date
234 ,p_object_version_number => p_rec.object_version_number
235 );
236 --
237 -- Validate PROJECTED_HIRE_DATE
238 --
239 per_apl_bus.chk_projected_hire_date
240 (p_date_received => p_rec.date_received
241 ,p_projected_hire_date => p_rec.projected_hire_date
242 ,p_application_id => p_rec.application_id
243 ,p_object_version_number => p_rec.object_version_number
244 );
245 --
246 -- Validate TERMINATION_REASON
247 --
248 per_apl_bus.chk_termination_reason
249 (p_termination_reason => p_rec.termination_reason
250 ,p_application_id => p_rec.application_id
251 ,p_effective_date => p_effective_date
252 ,p_object_version_number => p_rec.object_version_number
253 );
254 --
255 -- Validate Descriptive flexfields
256 --
257 if nvl(p_validate_df_flex,true) then -- 4689836
258 hr_dflex_utility.ins_or_upd_descflex_attribs(
259 p_appl_short_name => 'PER'
260 ,p_descflex_name => 'PER_APPLICATIONS'
261 ,p_attribute_category => p_rec.appl_attribute_category
262 ,p_attribute1_name => 'APPL_ATTRIBUTE1'
263 ,p_attribute1_value => p_rec.appl_attribute1
264 ,p_attribute2_name => 'APPL_ATTRIBUTE2'
265 ,p_attribute2_value => p_rec.appl_attribute2
266 ,p_attribute3_name => 'APPL_ATTRIBUTE3'
267 ,p_attribute3_value => p_rec.appl_attribute3
268 ,p_attribute4_name => 'APPL_ATTRIBUTE4'
269 ,p_attribute4_value => p_rec.appl_attribute4
270 ,p_attribute5_name => 'APPL_ATTRIBUTE5'
271 ,p_attribute5_value => p_rec.appl_attribute5
272 ,p_attribute6_name => 'APPL_ATTRIBUTE6'
273 ,p_attribute6_value => p_rec.appl_attribute6
274 ,p_attribute7_name => 'APPL_ATTRIBUTE7'
275 ,p_attribute7_value => p_rec.appl_attribute7
276 ,p_attribute8_name => 'APPL_ATTRIBUTE8'
277 ,p_attribute8_value => p_rec.appl_attribute8
278 ,p_attribute9_name => 'APPL_ATTRIBUTE9'
279 ,p_attribute9_value => p_rec.appl_attribute9
280 ,p_attribute10_name => 'APPL_ATTRIBUTE10'
281 ,p_attribute10_value => p_rec.appl_attribute10
282 ,p_attribute11_name => 'APPL_ATTRIBUTE11'
283 ,p_attribute11_value => p_rec.appl_attribute11
284 ,p_attribute12_name => 'APPL_ATTRIBUTE12'
285 ,p_attribute12_value => p_rec.appl_attribute12
286 ,p_attribute13_name => 'APPL_ATTRIBUTE13'
287 ,p_attribute13_value => p_rec.appl_attribute13
288 ,p_attribute14_name => 'APPL_ATTRIBUTE14'
289 ,p_attribute14_value => p_rec.appl_attribute14
290 ,p_attribute15_name => 'APPL_ATTRIBUTE15'
291 ,p_attribute15_value => p_rec.appl_attribute15
292 ,p_attribute16_name => 'APPL_ATTRIBUTE16'
293 ,p_attribute16_value => p_rec.appl_attribute16
294 ,p_attribute17_name => 'APPL_ATTRIBUTE17'
295 ,p_attribute17_value => p_rec.appl_attribute17
296 ,p_attribute18_name => 'APPL_ATTRIBUTE18'
297 ,p_attribute18_value => p_rec.appl_attribute18
298 ,p_attribute19_name => 'APPL_ATTRIBUTE19'
299 ,p_attribute19_value => p_rec.appl_attribute19
300 ,p_attribute20_name => 'APPL_ATTRIBUTE20'
301 ,p_attribute20_value => p_rec.appl_attribute20
302 );
303 end if;
304 --
305 hr_utility.set_location(' Leaving:'||l_proc, 10);
306 --
307 End insert_validate;
308 --
309 -- ----------------------------------------------------------------------------
310 -- |---------------------------< update_validate >----------------------------|
311 -- ----------------------------------------------------------------------------
312 Procedure update_validate(p_rec in per_apl_shd.g_rec_type
313 ,p_effective_date in date) is
314 --
315 l_proc varchar2(72) := g_package||'update_validate';
316 --
317 Begin
318 hr_utility.set_location('Entering:'||l_proc, 5);
319 --
320 -- Validate Important Attributes
321 --
322 hr_api.validate_bus_grp_id
323 (p_business_group_id => p_rec.business_group_id
324 ,p_associated_column1 => per_apl_shd.g_tab_nam || '.BUSINESS_GROUP_ID'
325 ); -- Validate Bus Grp
326 --
327 --After validating the set of important attributes,
328 --if multiple message detection is enabled and atleast
329 --one error has been found then abort further validation.
330 --
331 hr_multi_message.end_validation_set;
332 --
333 -- Call all supporting business operations
334 --
335 hr_utility.set_location(l_proc, 6);
336 --
337 -- Check the non-updateable arguments have in fact not been modified
338 --
339 per_apl_bus.check_non_updateable_args(p_rec => p_rec);
340 --
341 -- Validate DATE_RECEIVED
342 --
343 per_apl_bus.chk_date_received_person_id
344 (p_person_id => p_rec.person_id
345 ,p_application_id => p_rec.application_id
346 ,p_business_group_id => p_rec.business_group_id
347 ,p_date_received => p_rec.date_received
348 ,p_date_end => p_rec.date_end
349 ,p_projected_hire_date => p_rec.projected_hire_date
350 ,p_object_version_number => p_rec.object_version_number
351 );
352 --
353 -- Validate DATE_END
354 --
355 per_apl_bus.chk_date_end
356 (p_date_end => p_rec.date_end
357 ,p_date_received => p_rec.date_received
358 ,p_application_id => p_rec.application_id
359 ,p_object_version_number => p_rec.object_version_number
360 );
361 --
362 -- Validate PROJECTED_HIRE_DATE
363 --
364 per_apl_bus.chk_projected_hire_date
365 (p_date_received => p_rec.date_received
366 ,p_projected_hire_date => p_rec.projected_hire_date
370 --
367 ,p_application_id => p_rec.application_id
368 ,p_object_version_number => p_rec.object_version_number
369 );
371 -- Validate TERMINATION_REASON
372 --
373 per_apl_bus.chk_termination_reason
374 (p_termination_reason => p_rec.termination_reason
375 ,p_application_id => p_rec.application_id
376 ,p_effective_date => p_effective_date
377 ,p_object_version_number => p_rec.object_version_number
378 );
379 --
380 -- Validate Descriptive flexfields
381 --
382 per_apl_bus.df_update_validate(p_rec => p_rec);
383 --
384 hr_utility.set_location(' Leaving:'||l_proc, 10);
385 End update_validate;
386 --
387 -- ----------------------------------------------------------------------------
388 -- |---------------------------< delete_validate >----------------------------|
389 -- ----------------------------------------------------------------------------
390 Procedure delete_validate(p_rec in per_apl_shd.g_rec_type) is
391 --
392 l_proc varchar2(72) := g_package||'delete_validate';
393 --
394 Begin
395 hr_utility.set_location('Entering:'||l_proc, 5);
396 --
397 -- Call all supporting business operations
398 --
399 hr_utility.set_location(' Leaving:'||l_proc, 10);
400 End delete_validate;
401 -- ----------------------------------------------------------------------------
402 -- |--------------------< chk_date_received_person_id >-----------------------|
403 -- ----------------------------------------------------------------------------
404 Procedure chk_date_received_person_id
405 (p_person_id in per_applications.person_id%TYPE
406 ,p_business_group_id in per_applications.business_group_id%TYPE
407 ,p_date_received in per_applications.date_received%TYPE
408 ,p_date_end in per_applications.date_end%TYPE
409 ,p_projected_hire_date in per_applications.projected_hire_date%TYPE
410 ,p_application_id in per_applications.application_id%TYPE
411 ,p_object_version_number in per_applications.object_version_number%TYPE
412 ) is
413 --
414 l_proc varchar2(72) := g_package||'chk_person_id';
415 l_business_group_id per_applications.business_group_id%TYPE;
416 l_system_person_type per_person_types.system_person_type%TYPE;
417 l_api_updating boolean;
418 l_application_id per_applications.application_id%TYPE;
419 --
420 --
421 -- Cursor to check that person_id exists, in addition obtain
422 -- the system_person_type and the business_group_id for the
423 -- other validation checks
424 --
425 cursor csr_valid_person_per_people_f is
426 select per.business_group_id,
427 typ.system_person_type
428 from per_all_people_f per,
429 per_person_types typ
430 where per.person_id = p_person_id
431 and per.person_type_id = typ.person_type_id
432 and per.effective_start_date = p_date_received;
433 --
434 --
435 -- Cursor to check person_id, date_received combination
436 --
437 cursor csr_valid_person_per_apl is
438 select application_id
439 from per_applications
440 where person_id = p_person_id
441 and date_received = p_date_received;
442 --
443 begin
444 hr_utility.set_location('Entering:'||l_proc,1);
445 --
446 -- Perform mandatory parameter checks
447 --
448 hr_api.mandatory_arg_error
449 (p_api_name => l_proc
450 ,p_argument => 'date_received'
451 ,p_argument_value => p_date_received
452 );
453 --
454 -- Perform person_id mandatory check
455 --
456 hr_api.mandatory_arg_error
457 (p_api_name => l_proc
458 ,p_argument => 'person_id'
459 ,p_argument_value => p_person_id
460 );
461 --
462 hr_utility.set_location(l_proc,11);
463 --
464 -- Check if application is being updated and load g_old_rec if applicable
465 --
466 l_api_updating := per_apl_shd.api_updating
467 (p_application_id => p_application_id
468 ,p_object_version_number => p_object_version_number);
469 --
470 -- Proceed with validation based on outcome of api_updating call
471 -- On update, only the date_received can change so no need to check
472 -- for person_id changes.
473 --
474 if ((l_api_updating and
475 per_apl_shd.g_old_rec.date_received <> p_date_received)
476 or (NOT l_api_updating)) then
477 --
478 hr_utility.set_location(l_proc,21);
479 --
480 -- Check the person_id exist date_effectively
481 --
482 open csr_valid_person_per_people_f;
483 fetch csr_valid_person_per_people_f
484 into l_business_group_id, l_system_person_type;
485 if (csr_valid_person_per_people_f%notfound) then
486 close csr_valid_person_per_people_f;
487 hr_utility.set_message(801,'HR_51194_APL_INV_DT_PERSON');
488 hr_multi_message.add
489 (p_associated_column1 => 'PER_APPLICATIONS.PERSON_ID'
490 ,p_associated_column2 => 'PER_APPLICATIONS.DATE_RECEIVED'
491 );
492 else
493 close csr_valid_person_per_people_f;
494 --
495 hr_utility.set_location(l_proc,31);
496 --
500 if NOT l_api_updating then
497 -- The following person checks only need to be performed on an
498 -- insert.
499 --
501 --
502 hr_utility.set_location(l_proc,41);
503 --
504 -- Check the system_person_type of the applicant is 'APL','EMP_APL',
505 -- 'APL_EX_APL','EX_EMP_APL'
506 --
507 if (NOT l_api_updating and
508 l_system_person_type <> 'APL' AND
509 l_system_person_type <> 'EMP_APL' AND
510 l_system_person_type <> 'APL_EX_APL' AND
511 l_system_person_type <> 'EX_EMP_APL') then
512 hr_utility.set_message(801,'HR_51185_APL_INV_SYS_PER_TYPE');
513 hr_multi_message.add
514 (p_associated_column1 => 'PER_APPLICATIONS.PERSON_ID'
515 );
516 end if;
517 --
518 hr_utility.set_location(l_proc,51);
519 --
520 -- Check the application is in the same business group as the person
521 --
522 if (p_business_group_id <> l_business_group_id) then
523 hr_utility.set_message(801,'HR_51187_APL_INV_BUS_GRP');
524 hr_multi_message.add
525 (p_associated_column1 => 'PER_APPLICATIONS.PERSON_ID'
526 );
527 end if;
528 end if;
529 end if;
530 --
531 hr_utility.set_location(l_proc,61);
532 --
533 -- Validate date received with respect to DATE_END
534 --
535 if (p_date_received > nvl(p_date_end,hr_api.g_eot)) then
536 hr_utility.set_message(801,'HR_51188_APL_DTE_REC_DTE_END');
537 hr_multi_message.add
538 (p_associated_column1 => 'PER_APPLICATIONS.DATE_RECEIVED'
539 ,p_associated_column2 => 'PER_APPLICATIONS.DATE_END'
540 );
541 end if;
542 --
543 hr_utility.set_location(l_proc,71);
544 --
545 -- Validate date received with respect to PROJECTED HIRE DATE
546 --
547 if (p_date_received > nvl(p_projected_hire_date,hr_api.g_eot)) then
548 hr_utility.set_message(801,'HR_51189_APL_DTE_REC_PROJ_HIRE');
549 hr_multi_message.add
550 (p_associated_column1 => 'PER_APPLICATIONS.DATE_RECEIVED'
551 ,p_associated_column2 => 'PER_APPLICATIONS.PROJECTED_HIRE_DATE'
552 );
553 end if;
554 --
555 hr_utility.set_location(l_proc,81);
556 --
557 /* Removed 18-Aug-97
558 Reinstated 19-Jan-98, Version 110.3, S.Bhattal
559 */
560 --
561 -- Validate date_received, person_id combination not exists
562 --
563 open csr_valid_person_per_apl;
564 fetch csr_valid_person_per_apl into l_application_id;
565 if (csr_valid_person_per_apl%found) then
566 close csr_valid_person_per_apl;
567 hr_utility.set_message(801,'HR_51190_APL_DTE_REC_PERSON');
568 hr_multi_message.add
569 (p_associated_column1 => 'PER_APPLICATIONS.PERSON_ID'
570 ,p_associated_column2 => 'PER_APPLICATIONS.DATE_RECEIVED'
571 );
572 else
573 close csr_valid_person_per_apl;
574 end if;
575 --
576 hr_utility.set_location(l_proc,91);
577 /*
578 */
579 --
580 end if;
581 hr_utility.set_location('Leaving '||l_proc, 101);
582 end chk_date_received_person_id;
583 --
584 -- ---------------------------------------------------------------------------
585 -- |----------------< chk_projected_hire_date >------------------------------|
586 -- ---------------------------------------------------------------------------
587 Procedure chk_projected_hire_date
588 (p_date_received in per_applications.date_received%TYPE
589 ,p_projected_hire_date in per_applications.projected_hire_date%TYPE
590 ,p_application_id in per_applications.application_id%TYPE
591 ,p_object_version_number in per_applications.object_version_number%TYPE
592 ) is
593 --
594 l_proc varchar2(72) := g_package||'chk_proj_hire_date';
595 l_api_updating boolean;
596 --
597 Begin
598 hr_utility.set_location('Entering:'||l_proc,1);
599 --
600 -- Check if projected hire date is not null, only validate if it is not null
601 --
602 if hr_multi_message.no_all_inclusive_error
603 (p_check_column1 => 'PER_APPLICATIONS.DATE_RECEIVED'
604 ) then
605 if (p_projected_hire_date is not null) then
606 --
607 -- Check if application is being updated and load g_old_rec if applicable
608 --
609 l_api_updating := per_apl_shd.api_updating
610 (p_application_id => p_application_id
611 ,p_object_version_number => p_object_version_number);
612 --
613 -- Proceed with validation based on outcome of api_updating call
614 --
615 if ((l_api_updating and
616 nvl(per_apl_shd.g_old_rec.projected_hire_date,hr_api.g_date) <>
617 nvl(p_projected_hire_date,hr_api.g_date))
618 or
619 NOT l_api_updating) then
620 --
621 hr_utility.set_location('Inside:'||l_proc,11);
622 --
623 -- Validate projected hire date WRT date received.
624 --
625 if (p_date_received > p_projected_hire_date) then
626 hr_utility.set_message(801,'HR_51192_APL_PROJ_HIRE_DTE_REC');
627 hr_multi_message.add
631 end if;
628 (p_associated_column1 => 'PER_APPLICATIONS.DATE_RECEIVED'
629 ,p_associated_column2 => 'PER_APPLICATIONS.PROJECTED_HIRE_DATE'
630 );
632 end if;
633 hr_utility.set_location('Inside:'||l_proc,21);
634 --
635 end if;
636 hr_utility.set_location('Inside:'||l_proc,31);
637 --
638 end if;
639 hr_utility.set_location(' Leaving:' || l_proc, 41);
640 end chk_projected_hire_date;
641 --
642 -- ----------------------------------------------------------------------------
643 -- |---------------------------< chk_date_end >-------------------------------|
644 -- ----------------------------------------------------------------------------
645 Procedure chk_date_end
646 (p_date_end in per_applications.date_end%TYPE
647 ,p_date_received in per_applications.date_received%TYPE
648 ,p_application_id in per_applications.application_id%TYPE
649 ,p_object_version_number in per_applications.object_version_number%TYPE
650 ) is
651 --
652 l_proc varchar2(72) := g_package||'chk_date_end';
653 l_api_updating boolean;
654 l_person_id number;
655 l_assignment_id number;
656 --
657 -- Cursors
658 --
659 cursor csr_chk_asg_future_changes is
660 select assignment_id
661 from per_assignments_f
662 where application_id = p_application_id
663 and effective_start_date > p_date_end;
664 --
665 cursor csr_chk_apl_future_changes is
666 select ppf.person_id
667 from per_people_f ppf,
668 per_applications pa
669 where pa.application_id = p_application_id
670 and pa.person_id = ppf.person_id
671 and ppf.effective_start_date > p_date_end;
672 --
673 Begin
674 hr_utility.set_location('Entering:'||l_proc,1);
675 --
676 -- If date_end is non null then do the remaining validation otherwise end
677 --
678 if (p_date_end is not null) then
679 --
680 hr_utility.set_location(l_proc,11);
681 --
682 -- Check if application is being updated and load g_old_rec if applicable
683 --
684 l_api_updating := per_apl_shd.api_updating
685 (p_application_id => p_application_id
686 ,p_object_version_number => p_object_version_number);
687 --
688 -- Proceed with validation based on outcome of api_updating call
689 --
690 if (NOT l_api_updating) then
691 --
692 -- On insert and not null, so raise error
693 --
694 hr_utility.set_location(l_proc,21);
695 --
696 hr_utility.set_message(801,'HR_7441_API_ARG_NOT_SET');
697 hr_utility.set_message_token('ARG_NAME','DATE_END');
698 hr_multi_message.add
699 (p_associated_column1 => 'PER_APPLICATIONS.DATE_END'
700 );
701 --
702 elsif (l_api_updating and
703 nvl(per_apl_shd.g_old_rec.date_end,hr_api.g_date) <>
704 p_date_end) then
705 --
706 -- Date end changed to a not null value so do validation
707 --
708 hr_utility.set_location(l_proc,31);
709 --
710 -- If change is from not-null to different not-null then raise error
711 --
712 if (per_apl_shd.g_old_rec.date_end is not null and
713 per_apl_shd.g_old_rec.date_end <> p_date_end) then
714 hr_utility.set_location('Inside:'||l_proc,35);
715 hr_utility.set_message(801,'HR_51234_APL_INVALID_UPDATE');
716 hr_multi_message.add
717 (p_associated_column1 => 'PER_APPLICATIONS.DATE_END'
718 );
719 end if;
720 hr_utility.set_location(l_proc,41);
721 --
722 -- Raise error if date_end before date_received
723 --
724 if (p_date_end < p_date_received) then
725 hr_utility.set_message(801,'HR_51235_APL_DTE_END_DTE_REC');
726 hr_multi_message.add
727 (p_associated_column1 => 'PER_APPLICATIONS.DATE_RECEIVED'
728 ,p_associated_column2 => 'PER_APPLICATIONS.DATE_END'
729 );
730 end if;
731 hr_utility.set_location(l_proc,51);
732 --
733 -- Raise error if there are future changes to the applicant assignments
734 -- after date_end.
735 --
736 open csr_chk_asg_future_changes;
737 fetch csr_chk_asg_future_changes into l_assignment_id;
738 if csr_chk_asg_future_changes%found then
739 --
740 close csr_chk_asg_future_changes;
741 --
742 hr_utility.set_message(801,'HR_51236_APL_ASG_FUTURE_CHGS');
743 hr_multi_message.add
744 (p_associated_column1 => 'PER_APPLICATIONS.DATE_END'
745 );
746 else
747 --
748 close csr_chk_asg_future_changes;
749 --
750 end if;
751
752 hr_utility.set_location(l_proc,61);
753 --
754 -- Raise error if there are future changes to the applicant person
755 -- after date_end.
756 --
757 open csr_chk_apl_future_changes;
758 fetch csr_chk_apl_future_changes into l_person_id;
759 if csr_chk_apl_future_changes%found then
760 --
761 close csr_chk_apl_future_changes;
762 --
766 );
763 hr_utility.set_message(801,'HR_51237_APL_PER_FUTURE_CHGS');
764 hr_multi_message.add
765 (p_associated_column1 => 'PER_APPLICATIONS.DATE_END'
767 else
768 --
769 close csr_chk_apl_future_changes;
770 --
771 end if;
772 hr_utility.set_location(l_proc,71);
773 --
774 end if;
775 hr_utility.set_location(l_proc,81);
776 --
777 end if;
778 hr_utility.set_location(' Leaving:' || l_proc,91);
779 end chk_date_end;
780 --
781 -- ----------------------------------------------------------------------------
782 -- |------------------------< chk_successful_flag >---------------------------|
783 -- ----------------------------------------------------------------------------
784 Procedure chk_successful_flag
785 (p_successful_flag in per_applications.successful_flag%TYPE
786 ,p_application_id in per_applications.application_id%TYPE
787 ,p_object_version_number in per_applications.object_version_number%TYPE
788 ) is
789 --
790 l_proc varchar2(72) := g_package||'chk_successful_flag';
791 l_api_updating boolean;
792 --
793 Begin
794 hr_utility.set_location('Entering:'||l_proc,1);
795 --
796 --
797 -- Check if application is being updated and load g_old_rec if applicable
798 --
799 l_api_updating := per_apl_shd.api_updating
800 (p_application_id => p_application_id
801 ,p_object_version_number => p_object_version_number);
802 --
803 -- Proceed with validation based on outcome of api_updating call
804 --
805 if ((l_api_updating and
806 nvl(per_apl_shd.g_old_rec.successful_flag,hr_api.g_date) <>
807 nvl(p_successful_flag,hr_api.g_varchar2))
808 or
809 NOT l_api_updating) then
810 --
811 hr_utility.set_location('Inside:'||l_proc,11);
812 --
813 -- Check that successful flag is null
814 --
815 if p_successful_flag is not null then
816 -- Error: Invalid value
817 hr_utility.set_message(801, 'HR_7441_API_ARG_NOT_SET');
818 hr_utility.set_message_token('ARG_NAME','SUCCESSFUL_FLAG');
819 hr_utility.raise_error;
820 end if;
821 hr_utility.set_location('Inside:'||l_proc,21);
822 --
823 end if;
824 --
825 hr_utility.set_location(' Leaving:' || l_proc, 31);
826 --
827 exception
828 when app_exception.application_exception then
829 if hr_multi_message.exception_add
830 (p_associated_column1 => 'PER_APPLICATIONS.SUCCESSFUL_FLAG'
831 ) then
832 hr_utility.set_location(' Leaving:' || l_proc,40);
833 raise;
834 end if;
835 hr_utility.set_location(' Leaving:' || l_proc,41);
836 end chk_successful_flag;
837 --
838 -- ----------------------------------------------------------------------------
839 -- |-----------------------< chk_termination_reason >-------------------------|
840 -- ----------------------------------------------------------------------------
841 Procedure chk_termination_reason
842 (p_termination_reason in per_applications.termination_reason%TYPE
843 ,p_application_id in per_applications.application_id%TYPE
844 ,p_effective_date in date
845 ,p_object_version_number in per_applications.object_version_number%TYPE
846 ) is
847 --
848 l_proc varchar2(72) := g_package||'chk_termination_reason';
849 l_api_updating boolean;
850 --
854 -- Check mandatory parameters have been set
851 Begin
852 hr_utility.set_location('Entering:'||l_proc,1);
853 --
855 --
856 hr_api.mandatory_arg_error
857 (p_api_name => l_proc
858 ,p_argument => 'effective_date'
859 ,p_argument_value => p_effective_date
860 );
861 --
862 -- If termination_reason is NULL do nothing
863 --
864 if p_termination_reason is not null then
865 --
866 -- Check if application is being updated and load g_old_rec if applicable
867 --
868 l_api_updating := per_apl_shd.api_updating
869 (p_application_id => p_application_id
870 ,p_object_version_number => p_object_version_number);
871 --
872 -- Proceed with validation based on outcome of api_updating call
873 --
874 if ((l_api_updating and
875 nvl(per_apl_shd.g_old_rec.termination_reason,hr_api.g_varchar2) <>
876 nvl(p_termination_reason,hr_api.g_varchar2))
877 or
878 NOT l_api_updating) then
879 --
880 hr_utility.set_location('Inside:'||l_proc,11);
881 --
882 -- Check that termination reason is in the lookups table
883 --
884 if hr_api.not_exists_in_hr_lookups
885 (p_effective_date => p_effective_date
886 ,p_lookup_type => 'TERM_APL_REASON'
887 ,p_lookup_code => p_termination_reason
888 )then
889 -- Error : Invalid Termination Reason
890 hr_utility.set_message(801,'HR_51238_APL_TERM_REASON');
891 hr_utility.raise_error;
892 end if;
893 hr_utility.set_location('Inside:'||l_proc,21);
894 --
895 end if;
896 --
897 end if;
898 --
899 hr_utility.set_location(' Leaving:'|| l_proc, 31);
900 exception
901 when app_exception.application_exception then
902 if hr_multi_message.exception_add
903 (p_associated_column1 => 'PER_APPLICATIONS.TERMINATION_REASON'
904 ) then
905 hr_utility.set_location(' Leaving:'||l_proc,40);
906 raise;
907 end if;
908 hr_utility.set_location(' Leaving:' ||l_proc,41);
909 end chk_termination_reason;
910 --
911 -- ---------------------------------------------------------------------------
912 -- |---------------------< return_legislation_code >-------------------------|
913 -- ---------------------------------------------------------------------------
914 --
915 function return_legislation_code
916 (p_application_id in number
917 ) return varchar2 is
918 --
919 -- Declare cursor
920 --
921 cursor csr_leg_code is
922 select pbg.legislation_code
923 from per_business_groups pbg
924 , per_applications apl
925 where apl.application_id = p_application_id
926 and pbg.business_group_id = apl.business_group_id;
927 --
928 -- Declare local variables
929 --
930 l_legislation_code varchar2(150);
931 l_proc varchar2(72) := g_package||'return_legislation_code';
932 begin
933 hr_utility.set_location('Entering:'|| l_proc, 10);
934 --
935 -- Ensure that all the mandatory parameter are not null
936 --
937 hr_api.mandatory_arg_error(p_api_name => l_proc,
938 p_argument => 'application_id',
939 p_argument_value => p_application_id);
940 --
941 open csr_leg_code;
942 fetch csr_leg_code into l_legislation_code;
943 if csr_leg_code%notfound then
944 close csr_leg_code;
945 --
946 -- The primary key is invalid therefore we must error
947 --
948 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
949 hr_utility.raise_error;
950 end if;
951 --
952 close csr_leg_code;
953 hr_utility.set_location(' Leaving:' || l_proc, 20);
954 --
955 return l_legislation_code;
956 end return_legislation_code;
957 --
958 end per_apl_bus;