[Home] [Help]
PACKAGE BODY: APPS.PER_APL_BUS
Source
1 Package Body per_apl_bus as
2 /* $Header: peaplrhi.pkb 120.2.12020000.3 2013/01/18 06:52:05 srannama ship $ */
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) <>
108 nvl(per_apl_shd.g_old_rec.appl_attribute4, 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
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
367 ,p_application_id => p_rec.application_id
368 ,p_object_version_number => p_rec.object_version_number
369 );
370 --
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 --
497 -- The following person checks only need to be performed on an
498 -- insert.
499 --
500 if NOT l_api_updating then
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
551 ,p_associated_column2 => 'PER_APPLICATIONS.PROJECTED_HIRE_DATE'
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'
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
628 (p_associated_column1 => 'PER_APPLICATIONS.DATE_RECEIVED'
629 ,p_associated_column2 => 'PER_APPLICATIONS.PROJECTED_HIRE_DATE'
630 );
631 end if;
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 assignment_type ='A' -- 13782503
664 and effective_start_date > p_date_end;
665
666 --
667 cursor csr_chk_apl_future_changes is
668 select ppf.person_id
669 from per_people_f ppf,
670 per_applications pa
671 where pa.application_id = p_application_id
672 and pa.person_id = ppf.person_id
673 and ppf.effective_start_date > p_date_end
674
675 --bug no 5105005
676 and CURRENT_APPLICANT_FLAG <> 'Y';
677 --bug no 5105005
678 --
679 Begin
680 hr_utility.set_location('Entering:'||l_proc,1);
681 --
682 -- If date_end is non null then do the remaining validation otherwise end
683 --
684 if (p_date_end is not null) then
685 --
689 --
686 hr_utility.set_location(l_proc,11);
687 --
688 -- Check if application is being updated and load g_old_rec if applicable
690 l_api_updating := per_apl_shd.api_updating
691 (p_application_id => p_application_id
692 ,p_object_version_number => p_object_version_number);
693 --
694 -- Proceed with validation based on outcome of api_updating call
695 --
696 if (NOT l_api_updating) then
697 --
698 -- On insert and not null, so raise error
699 --
700 hr_utility.set_location(l_proc,21);
701 --
702 hr_utility.set_message(801,'HR_7441_API_ARG_NOT_SET');
703 hr_utility.set_message_token('ARG_NAME','DATE_END');
704 hr_multi_message.add
705 (p_associated_column1 => 'PER_APPLICATIONS.DATE_END'
706 );
707 --
708 elsif (l_api_updating and
709 nvl(per_apl_shd.g_old_rec.date_end,hr_api.g_date) <>
710 p_date_end) then
711 --
712 -- Date end changed to a not null value so do validation
713 --
714 hr_utility.set_location(l_proc,31);
715 --
716 -- If change is from not-null to different not-null then raise error
717 --
718 if (per_apl_shd.g_old_rec.date_end is not null and
719 per_apl_shd.g_old_rec.date_end <> p_date_end) then
720 hr_utility.set_location('Inside:'||l_proc,35);
721 hr_utility.set_message(801,'HR_51234_APL_INVALID_UPDATE');
722 hr_multi_message.add
723 (p_associated_column1 => 'PER_APPLICATIONS.DATE_END'
724 );
725 end if;
726 hr_utility.set_location(l_proc,41);
727 --
728 -- Raise error if date_end before date_received
729 --
730 if (p_date_end < p_date_received) then
731 hr_utility.set_message(801,'HR_51235_APL_DTE_END_DTE_REC');
732 hr_multi_message.add
733 (p_associated_column1 => 'PER_APPLICATIONS.DATE_RECEIVED'
734 ,p_associated_column2 => 'PER_APPLICATIONS.DATE_END'
735 );
736 end if;
737 hr_utility.set_location(l_proc,51);
738 --
739 -- Raise error if there are future changes to the applicant assignments
740 -- after date_end.
741 --
742 open csr_chk_asg_future_changes;
743 fetch csr_chk_asg_future_changes into l_assignment_id;
744 -- ER FPT
745 if csr_chk_asg_future_changes%found and (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'N')then
746 --
747 close csr_chk_asg_future_changes;
748 --
749 hr_utility.set_message(801,'HR_51236_APL_ASG_FUTURE_CHGS');
750 hr_multi_message.add
751 (p_associated_column1 => 'PER_APPLICATIONS.DATE_END'
752 );
753 else
754 --
755 close csr_chk_asg_future_changes;
756 --
757 end if;
758
759 hr_utility.set_location(l_proc,61);
760 --
761 -- Raise error if there are future changes to the applicant person
762 -- after date_end.
763 --
764 open csr_chk_apl_future_changes;
765 fetch csr_chk_apl_future_changes into l_person_id;
766 if csr_chk_apl_future_changes%found then
767 --
768 close csr_chk_apl_future_changes;
769 --
770 hr_utility.set_message(801,'HR_51237_APL_PER_FUTURE_CHGS');
771 hr_multi_message.add
772 (p_associated_column1 => 'PER_APPLICATIONS.DATE_END'
773 );
774 else
775 --
776 close csr_chk_apl_future_changes;
777 --
778 end if;
779 hr_utility.set_location(l_proc,71);
780 --
781 end if;
782 hr_utility.set_location(l_proc,81);
783 --
784 end if;
785 hr_utility.set_location(' Leaving:' || l_proc,91);
786 end chk_date_end;
787 --
788 -- ----------------------------------------------------------------------------
789 -- |------------------------< chk_successful_flag >---------------------------|
790 -- ----------------------------------------------------------------------------
791 Procedure chk_successful_flag
792 (p_successful_flag in per_applications.successful_flag%TYPE
793 ,p_application_id in per_applications.application_id%TYPE
794 ,p_object_version_number in per_applications.object_version_number%TYPE
795 ) is
796 --
797 l_proc varchar2(72) := g_package||'chk_successful_flag';
798 l_api_updating boolean;
799 --
800 Begin
801 hr_utility.set_location('Entering:'||l_proc,1);
802 --
803 --
804 -- Check if application is being updated and load g_old_rec if applicable
805 --
806 l_api_updating := per_apl_shd.api_updating
807 (p_application_id => p_application_id
808 ,p_object_version_number => p_object_version_number);
809 --
810 -- Proceed with validation based on outcome of api_updating call
811 --
812 if ((l_api_updating and
813 nvl(per_apl_shd.g_old_rec.successful_flag,hr_api.g_date) <>
814 nvl(p_successful_flag,hr_api.g_varchar2))
815 or
816 NOT l_api_updating) then
817 --
818 hr_utility.set_location('Inside:'||l_proc,11);
819 --
820 -- Check that successful flag is null
821 --
822 if p_successful_flag is not null then
823 -- Error: Invalid value
824 hr_utility.set_message(801, 'HR_7441_API_ARG_NOT_SET');
828 hr_utility.set_location('Inside:'||l_proc,21);
825 hr_utility.set_message_token('ARG_NAME','SUCCESSFUL_FLAG');
826 hr_utility.raise_error;
827 end if;
829 --
830 end if;
831 --
832 hr_utility.set_location(' Leaving:' || l_proc, 31);
833 --
834 exception
835 when app_exception.application_exception then
836 if hr_multi_message.exception_add
837 (p_associated_column1 => 'PER_APPLICATIONS.SUCCESSFUL_FLAG'
838 ) then
839 hr_utility.set_location(' Leaving:' || l_proc,40);
840 raise;
841 end if;
842 hr_utility.set_location(' Leaving:' || l_proc,41);
843 end chk_successful_flag;
844 --
845 -- ----------------------------------------------------------------------------
846 -- |-----------------------< chk_termination_reason >-------------------------|
847 -- ----------------------------------------------------------------------------
848 Procedure chk_termination_reason
849 (p_termination_reason in per_applications.termination_reason%TYPE
850 ,p_application_id in per_applications.application_id%TYPE
851 ,p_effective_date in date
852 ,p_object_version_number in per_applications.object_version_number%TYPE
853 ) is
854 --
855 l_proc varchar2(72) := g_package||'chk_termination_reason';
856 l_api_updating boolean;
857 --
858 Begin
859 hr_utility.set_location('Entering:'||l_proc,1);
860 --
861 -- Check mandatory parameters have been set
862 --
863 hr_api.mandatory_arg_error
864 (p_api_name => l_proc
865 ,p_argument => 'effective_date'
866 ,p_argument_value => p_effective_date
867 );
868 --
869 -- If termination_reason is NULL do nothing
870 --
871 if p_termination_reason is not null then
872 --
873 -- Check if application is being updated and load g_old_rec if applicable
874 --
875 l_api_updating := per_apl_shd.api_updating
876 (p_application_id => p_application_id
877 ,p_object_version_number => p_object_version_number);
878 --
879 -- Proceed with validation based on outcome of api_updating call
880 --
881 if ((l_api_updating and
882 nvl(per_apl_shd.g_old_rec.termination_reason,hr_api.g_varchar2) <>
883 nvl(p_termination_reason,hr_api.g_varchar2))
884 or
885 NOT l_api_updating) then
886 --
887 hr_utility.set_location('Inside:'||l_proc,11);
888 --
889 -- Check that termination reason is in the lookups table
890 --
891 if hr_api.not_exists_in_hr_lookups
892 (p_effective_date => p_effective_date
893 ,p_lookup_type => 'TERM_APL_REASON'
894 ,p_lookup_code => p_termination_reason
895 )then
896 -- Error : Invalid Termination Reason
897 hr_utility.set_message(801,'HR_51238_APL_TERM_REASON');
898 hr_utility.raise_error;
899 end if;
900 hr_utility.set_location('Inside:'||l_proc,21);
901 --
902 end if;
903 --
904 end if;
905 --
906 hr_utility.set_location(' Leaving:'|| l_proc, 31);
907 exception
908 when app_exception.application_exception then
909 if hr_multi_message.exception_add
910 (p_associated_column1 => 'PER_APPLICATIONS.TERMINATION_REASON'
911 ) then
912 hr_utility.set_location(' Leaving:'||l_proc,40);
913 raise;
914 end if;
915 hr_utility.set_location(' Leaving:' ||l_proc,41);
916 end chk_termination_reason;
917 --
918 -- ---------------------------------------------------------------------------
919 -- |---------------------< return_legislation_code >-------------------------|
920 -- ---------------------------------------------------------------------------
921 --
922 function return_legislation_code
923 (p_application_id in number
924 ) return varchar2 is
925 --
926 -- Declare cursor
927 --
928 cursor csr_leg_code is
929 select pbg.legislation_code
930 from per_business_groups pbg
931 , per_applications apl
932 where apl.application_id = p_application_id
933 and pbg.business_group_id = apl.business_group_id;
934 --
935 -- Declare local variables
936 --
937 l_legislation_code varchar2(150);
938 l_proc varchar2(72) := g_package||'return_legislation_code';
939 begin
940 hr_utility.set_location('Entering:'|| l_proc, 10);
941 --
942 -- Ensure that all the mandatory parameter are not null
943 --
944 hr_api.mandatory_arg_error(p_api_name => l_proc,
945 p_argument => 'application_id',
946 p_argument_value => p_application_id);
947 --
948 open csr_leg_code;
949 fetch csr_leg_code into l_legislation_code;
950 if csr_leg_code%notfound then
951 close csr_leg_code;
952 --
953 -- The primary key is invalid therefore we must error
954 --
955 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
956 hr_utility.raise_error;
957 end if;
958 --
959 close csr_leg_code;
960 hr_utility.set_location(' Leaving:' || l_proc, 20);
961 --
962 return l_legislation_code;
963 end return_legislation_code;
964 --
965 end per_apl_bus;