[Home] [Help]
PACKAGE BODY: APPS.HR_CONTRACT_API
Source
1 Package Body hr_contract_api as
2 /* $Header: hrctcapi.pkb 120.1.12020000.1 2012/06/28 23:49:40 appldev ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_contract_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< create_contract >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_contract
13 (p_validate in boolean default false
14 ,p_contract_id out nocopy number
15 ,p_effective_start_date out nocopy date
16 ,p_effective_end_date out nocopy date
17 ,p_object_version_number out nocopy number
18 ,p_person_id in number
19 ,p_reference in varchar2
20 ,p_type in varchar2
21 ,p_status in varchar2
22 ,p_status_reason in varchar2 default null
23 ,p_doc_status in varchar2 default null
24 ,p_doc_status_change_date in date default null
25 ,p_description in varchar2 default null
26 ,p_duration in number default null
27 ,p_duration_units in varchar2 default null
28 ,p_contractual_job_title in varchar2 default null
29 ,p_parties in varchar2 default null
30 ,p_start_reason in varchar2 default null
31 ,p_end_reason in varchar2 default null
32 ,p_number_of_extensions in number default null
33 ,p_extension_reason in varchar2 default null
34 ,p_extension_period in number default null
35 ,p_extension_period_units in varchar2 default null
36 ,p_ctr_information_category in varchar2 default null
37 ,p_ctr_information1 in varchar2 default null
38 ,p_ctr_information2 in varchar2 default null
39 ,p_ctr_information3 in varchar2 default null
40 ,p_ctr_information4 in varchar2 default null
41 ,p_ctr_information5 in varchar2 default null
42 ,p_ctr_information6 in varchar2 default null
43 ,p_ctr_information7 in varchar2 default null
44 ,p_ctr_information8 in varchar2 default null
45 ,p_ctr_information9 in varchar2 default null
46 ,p_ctr_information10 in varchar2 default null
47 ,p_ctr_information11 in varchar2 default null
48 ,p_ctr_information12 in varchar2 default null
49 ,p_ctr_information13 in varchar2 default null
50 ,p_ctr_information14 in varchar2 default null
51 ,p_ctr_information15 in varchar2 default null
52 ,p_ctr_information16 in varchar2 default null
53 ,p_ctr_information17 in varchar2 default null
54 ,p_ctr_information18 in varchar2 default null
55 ,p_ctr_information19 in varchar2 default null
56 ,p_ctr_information20 in varchar2 default null
57 ,p_attribute_category in varchar2 default null
58 ,p_attribute1 in varchar2 default null
59 ,p_attribute2 in varchar2 default null
60 ,p_attribute3 in varchar2 default null
61 ,p_attribute4 in varchar2 default null
62 ,p_attribute5 in varchar2 default null
63 ,p_attribute6 in varchar2 default null
64 ,p_attribute7 in varchar2 default null
65 ,p_attribute8 in varchar2 default null
66 ,p_attribute9 in varchar2 default null
67 ,p_attribute10 in varchar2 default null
68 ,p_attribute11 in varchar2 default null
69 ,p_attribute12 in varchar2 default null
70 ,p_attribute13 in varchar2 default null
71 ,p_attribute14 in varchar2 default null
72 ,p_attribute15 in varchar2 default null
73 ,p_attribute16 in varchar2 default null
74 ,p_attribute17 in varchar2 default null
75 ,p_attribute18 in varchar2 default null
76 ,p_attribute19 in varchar2 default null
77 ,p_attribute20 in varchar2 default null
78 ,p_effective_date in date
79 ) is
80 --
81 -- Declare cursors and local variables
82 --
83 l_contract_id per_contracts_f.contract_id%TYPE;
84 l_effective_start_date per_contracts_f.effective_start_date%TYPE;
85 l_effective_end_date per_contracts_f.effective_end_date%TYPE;
86 l_effective_date date;
87 l_proc varchar2(72) := g_package||'create_contract';
88 l_object_version_number per_contracts_f.object_version_number%TYPE;
89 l_business_group_id per_contracts_f.business_group_id%TYPE;
90 --
91 cursor csr_get_derived_details is
92 select per.business_group_id
93 from per_all_people_f per
94 where per.person_id = p_person_id
95 and p_effective_date between per.effective_start_date
96 and per.effective_end_date;
97 begin
98 --
99 hr_utility.set_location('Entering:'|| l_proc, 10);
100 --
101 -- Issue a savepoint if operating in validation only mode
102 --
103 savepoint create_contract;
104 --
105 hr_utility.set_location(l_proc, 20);
106 --
107 -- Process Logic
108 --
109 -- Get person details.
110 --
111 open csr_get_derived_details;
112 fetch csr_get_derived_details
113 into l_business_group_id;
114 --
115 if csr_get_derived_details%NOTFOUND then
116 --
117 close csr_get_derived_details;
118 --
119 hr_utility.set_location(l_proc, 30);
120 --
121 hr_utility.set_message(801,'HR_7432_ASG_INVALID_PERSON');
122 hr_utility.raise_error;
123 end if;
124 --
125 close csr_get_derived_details;
126 --
127 hr_utility.set_location(l_proc, 40);
128 --
129 begin
130 --
131 -- Start of API User Hook for the before hook of create_contract
132 --
133 hr_contract_bk1.create_contract_b
134 (
135 p_business_group_id => l_business_group_id
136 ,p_person_id => p_person_id
137 ,p_reference => p_reference
138 ,p_type => p_type
139 ,p_status => p_status
140 ,p_status_reason => p_status_reason
141 ,p_doc_status => p_doc_status
142 ,p_doc_status_change_date => p_doc_status_change_date
143 ,p_description => p_description
144 ,p_duration => p_duration
145 ,p_duration_units => p_duration_units
146 ,p_contractual_job_title => p_contractual_job_title
147 ,p_parties => p_parties
148 ,p_start_reason => p_start_reason
149 ,p_end_reason => p_end_reason
150 ,p_number_of_extensions => p_number_of_extensions
151 ,p_extension_reason => p_extension_reason
152 ,p_extension_period => p_extension_period
153 ,p_extension_period_units => p_extension_period_units
154 ,p_ctr_information_category => p_ctr_information_category
155 ,p_ctr_information1 => p_ctr_information1
156 ,p_ctr_information2 => p_ctr_information2
157 ,p_ctr_information3 => p_ctr_information3
158 ,p_ctr_information4 => p_ctr_information4
159 ,p_ctr_information5 => p_ctr_information5
160 ,p_ctr_information6 => p_ctr_information6
161 ,p_ctr_information7 => p_ctr_information7
162 ,p_ctr_information8 => p_ctr_information8
163 ,p_ctr_information9 => p_ctr_information9
164 ,p_ctr_information10 => p_ctr_information10
165 ,p_ctr_information11 => p_ctr_information11
166 ,p_ctr_information12 => p_ctr_information12
167 ,p_ctr_information13 => p_ctr_information13
168 ,p_ctr_information14 => p_ctr_information14
169 ,p_ctr_information15 => p_ctr_information15
170 ,p_ctr_information16 => p_ctr_information16
171 ,p_ctr_information17 => p_ctr_information17
172 ,p_ctr_information18 => p_ctr_information18
173 ,p_ctr_information19 => p_ctr_information19
174 ,p_ctr_information20 => p_ctr_information20
175 ,p_attribute_category => p_attribute_category
176 ,p_attribute1 => p_attribute1
177 ,p_attribute2 => p_attribute2
178 ,p_attribute3 => p_attribute3
179 ,p_attribute4 => p_attribute4
180 ,p_attribute5 => p_attribute5
181 ,p_attribute6 => p_attribute6
182 ,p_attribute7 => p_attribute7
183 ,p_attribute8 => p_attribute8
184 ,p_attribute9 => p_attribute9
185 ,p_attribute10 => p_attribute10
186 ,p_attribute11 => p_attribute11
187 ,p_attribute12 => p_attribute12
188 ,p_attribute13 => p_attribute13
189 ,p_attribute14 => p_attribute14
190 ,p_attribute15 => p_attribute15
191 ,p_attribute16 => p_attribute16
192 ,p_attribute17 => p_attribute17
193 ,p_attribute18 => p_attribute18
194 ,p_attribute19 => p_attribute19
195 ,p_attribute20 => p_attribute20
196 ,p_effective_date => trunc(p_effective_date)
197 );
198 exception
199 when hr_api.cannot_find_prog_unit then
200 hr_api.cannot_find_prog_unit_error
201 (
202 p_module_name => 'CREATE_contract'
203 ,p_hook_type => 'BP'
204 );
205 --
206 -- End of API User Hook for the before hook of create_contract
207 --
208 hr_utility.set_location(l_proc, 30);
209 --
210 --
211 -- Validation in addition to Table Handlers
212 hr_api.mandatory_arg_error
213 (p_api_name => l_proc
214 ,p_argument => 'person_id'
215 ,p_argument_value => p_person_id
216 );
217 --
218 hr_api.mandatory_arg_error
219 (p_api_name => l_proc
220 ,p_argument => 'effective_date'
221 ,p_argument_value => l_effective_date
222 );
223 --
224 hr_utility.set_location(l_proc, 40);
225 --
226 end;
227 --
228 per_ctc_ins.ins
229 (
230 p_contract_id => l_contract_id
231 ,p_effective_start_date => l_effective_start_date
232 ,p_effective_end_date => l_effective_end_date
233 ,p_business_group_id => l_business_group_id
234 ,p_object_version_number => l_object_version_number
235 ,p_person_id => p_person_id
236 ,p_reference => p_reference
237 ,p_type => p_type
238 ,p_status => p_status
239 ,p_status_reason => p_status_reason
240 ,p_doc_status => p_doc_status
241 ,p_doc_status_change_date => p_doc_status_change_date
242 ,p_description => p_description
243 ,p_duration => p_duration
244 ,p_duration_units => p_duration_units
245 ,p_contractual_job_title => p_contractual_job_title
246 ,p_parties => p_parties
247 ,p_start_reason => p_start_reason
248 ,p_end_reason => p_end_reason
249 ,p_number_of_extensions => p_number_of_extensions
250 ,p_extension_reason => p_extension_reason
251 ,p_extension_period => p_extension_period
252 ,p_extension_period_units => p_extension_period_units
253 ,p_ctr_information_category => p_ctr_information_category
254 ,p_ctr_information1 => p_ctr_information1
255 ,p_ctr_information2 => p_ctr_information2
256 ,p_ctr_information3 => p_ctr_information3
257 ,p_ctr_information4 => p_ctr_information4
258 ,p_ctr_information5 => p_ctr_information5
259 ,p_ctr_information6 => p_ctr_information6
260 ,p_ctr_information7 => p_ctr_information7
261 ,p_ctr_information8 => p_ctr_information8
262 ,p_ctr_information9 => p_ctr_information9
263 ,p_ctr_information10 => p_ctr_information10
264 ,p_ctr_information11 => p_ctr_information11
265 ,p_ctr_information12 => p_ctr_information12
266 ,p_ctr_information13 => p_ctr_information13
267 ,p_ctr_information14 => p_ctr_information14
268 ,p_ctr_information15 => p_ctr_information15
269 ,p_ctr_information16 => p_ctr_information16
270 ,p_ctr_information17 => p_ctr_information17
271 ,p_ctr_information18 => p_ctr_information18
272 ,p_ctr_information19 => p_ctr_information19
273 ,p_ctr_information20 => p_ctr_information20
274 ,p_attribute_category => p_attribute_category
275 ,p_attribute1 => p_attribute1
276 ,p_attribute2 => p_attribute2
277 ,p_attribute3 => p_attribute3
278 ,p_attribute4 => p_attribute4
279 ,p_attribute5 => p_attribute5
280 ,p_attribute6 => p_attribute6
281 ,p_attribute7 => p_attribute7
282 ,p_attribute8 => p_attribute8
283 ,p_attribute9 => p_attribute9
284 ,p_attribute10 => p_attribute10
285 ,p_attribute11 => p_attribute11
286 ,p_attribute12 => p_attribute12
287 ,p_attribute13 => p_attribute13
288 ,p_attribute14 => p_attribute14
289 ,p_attribute15 => p_attribute15
290 ,p_attribute16 => p_attribute16
291 ,p_attribute17 => p_attribute17
292 ,p_attribute18 => p_attribute18
293 ,p_attribute19 => p_attribute19
294 ,p_attribute20 => p_attribute20
295 ,p_effective_date => trunc(p_effective_date)
296 );
297 --
298 begin
299 --
300 -- Start of API User Hook for the after hook of create_contract
301 --
302 hr_contract_bk1.create_contract_a
303 (
304 p_contract_id => l_contract_id
305 ,p_effective_start_date => l_effective_start_date
306 ,p_effective_end_date => l_effective_end_date
307 ,p_business_group_id => l_business_group_id
308 ,p_object_version_number => l_object_version_number
309 ,p_person_id => p_person_id
310 ,p_reference => p_reference
311 ,p_type => p_type
312 ,p_status => p_status
313 ,p_status_reason => p_status_reason
314 ,p_doc_status => p_doc_status
315 ,p_doc_status_change_date => p_doc_status_change_date
316 ,p_description => p_description
317 ,p_duration => p_duration
318 ,p_duration_units => p_duration_units
319 ,p_contractual_job_title => p_contractual_job_title
320 ,p_parties => p_parties
321 ,p_start_reason => p_start_reason
322 ,p_end_reason => p_end_reason
323 ,p_number_of_extensions => p_number_of_extensions
324 ,p_extension_reason => p_extension_reason
325 ,p_extension_period => p_extension_period
326 ,p_extension_period_units => p_extension_period_units
327 ,p_ctr_information_category => p_ctr_information_category
328 ,p_ctr_information1 => p_ctr_information1
329 ,p_ctr_information2 => p_ctr_information2
330 ,p_ctr_information3 => p_ctr_information3
331 ,p_ctr_information4 => p_ctr_information4
332 ,p_ctr_information5 => p_ctr_information5
333 ,p_ctr_information6 => p_ctr_information6
334 ,p_ctr_information7 => p_ctr_information7
335 ,p_ctr_information8 => p_ctr_information8
336 ,p_ctr_information9 => p_ctr_information9
337 ,p_ctr_information10 => p_ctr_information10
338 ,p_ctr_information11 => p_ctr_information11
339 ,p_ctr_information12 => p_ctr_information12
340 ,p_ctr_information13 => p_ctr_information13
341 ,p_ctr_information14 => p_ctr_information14
342 ,p_ctr_information15 => p_ctr_information15
343 ,p_ctr_information16 => p_ctr_information16
344 ,p_ctr_information17 => p_ctr_information17
345 ,p_ctr_information18 => p_ctr_information18
346 ,p_ctr_information19 => p_ctr_information19
347 ,p_ctr_information20 => p_ctr_information20
348 ,p_attribute_category => p_attribute_category
349 ,p_attribute1 => p_attribute1
350 ,p_attribute2 => p_attribute2
351 ,p_attribute3 => p_attribute3
352 ,p_attribute4 => p_attribute4
353 ,p_attribute5 => p_attribute5
354 ,p_attribute6 => p_attribute6
355 ,p_attribute7 => p_attribute7
356 ,p_attribute8 => p_attribute8
357 ,p_attribute9 => p_attribute9
358 ,p_attribute10 => p_attribute10
359 ,p_attribute11 => p_attribute11
360 ,p_attribute12 => p_attribute12
361 ,p_attribute13 => p_attribute13
362 ,p_attribute14 => p_attribute14
363 ,p_attribute15 => p_attribute15
364 ,p_attribute16 => p_attribute16
365 ,p_attribute17 => p_attribute17
366 ,p_attribute18 => p_attribute18
367 ,p_attribute19 => p_attribute19
368 ,p_attribute20 => p_attribute20
369 ,p_effective_date => trunc(p_effective_date)
370 );
371 exception
372 when hr_api.cannot_find_prog_unit then
373 hr_api.cannot_find_prog_unit_error
374 (p_module_name => 'CREATE_contract'
375 ,p_hook_type => 'AP'
376 );
377 --
378 -- End of API User Hook for the after hook of create_contract
379 --
380 end;
381 --
382 hr_utility.set_location(l_proc, 60);
383 --
384 -- When in validation only mode raise the Validate_Enabled exception
385 --
386 if p_validate then
387 raise hr_api.validate_enabled;
388 end if;
389 --
390 -- Set all output arguments
391 --
392 p_contract_id := l_contract_id;
393 p_effective_start_date := l_effective_start_date;
394 p_effective_end_date := l_effective_end_date;
395 p_object_version_number := l_object_version_number;
396 --
397 hr_utility.set_location(' Leaving:'||l_proc, 70);
398 --
399 exception
400 --
401 when hr_api.validate_enabled then
402 --
403 -- As the Validate_Enabled exception has been raised
404 -- we must rollback to the savepoint
405 --
406 ROLLBACK TO create_contract;
407 --
408 -- Only set output warning arguments
409 -- (Any key or derived arguments must be set to null
410 -- when validation only mode is being used.)
411 --
412 p_contract_id := null;
413 p_effective_start_date := null;
414 p_effective_end_date := null;
415 p_object_version_number := null;
416 hr_utility.set_location(' Leaving:'||l_proc, 80);
417 --
418 when others then
419 --
420 -- A validation or unexpected error has occured
421 --
422 ROLLBACK TO create_contract;
423 -- Set OUT parameters to null
424 p_contract_id := null;
425 p_effective_start_date := null;
426 p_effective_end_date := null;
427 p_object_version_number := null;
428 raise;
429 --
430 end create_contract;
431 --
432 -- ----------------------------------------------------------------------------
433 -- |------------------------< update_contract >--- ------------------|
434 -- ----------------------------------------------------------------------------
435 --
436 procedure update_contract
437 (p_validate in boolean default false
438 ,p_contract_id in number
439 ,p_effective_start_date out nocopy date
440 ,p_effective_end_date out nocopy date
441 ,p_object_version_number in out nocopy number
442 ,p_person_id in number
443 ,p_reference in varchar2
444 ,p_type in varchar2
445 ,p_status in varchar2
446 ,p_status_reason in varchar2 default hr_api.g_varchar2
447 ,p_doc_status in varchar2 default hr_api.g_varchar2
448 ,p_doc_status_change_date in date default hr_api.g_date
449 ,p_description in varchar2 default hr_api.g_varchar2
450 ,p_duration in number default hr_api.g_number
451 ,p_duration_units in varchar2 default hr_api.g_varchar2
452 ,p_contractual_job_title in varchar2 default hr_api.g_varchar2
453 ,p_parties in varchar2 default hr_api.g_varchar2
454 ,p_start_reason in varchar2 default hr_api.g_varchar2
455 ,p_end_reason in varchar2 default hr_api.g_varchar2
456 ,p_number_of_extensions in number default hr_api.g_number
457 ,p_extension_reason in varchar2 default hr_api.g_varchar2
458 ,p_extension_period in number default hr_api.g_number
459 ,p_extension_period_units in varchar2 default hr_api.g_varchar2
460 ,p_ctr_information_category in varchar2 default hr_api.g_varchar2
461 ,p_ctr_information1 in varchar2 default hr_api.g_varchar2
462 ,p_ctr_information2 in varchar2 default hr_api.g_varchar2
463 ,p_ctr_information3 in varchar2 default hr_api.g_varchar2
464 ,p_ctr_information4 in varchar2 default hr_api.g_varchar2
465 ,p_ctr_information5 in varchar2 default hr_api.g_varchar2
466 ,p_ctr_information6 in varchar2 default hr_api.g_varchar2
467 ,p_ctr_information7 in varchar2 default hr_api.g_varchar2
468 ,p_ctr_information8 in varchar2 default hr_api.g_varchar2
469 ,p_ctr_information9 in varchar2 default hr_api.g_varchar2
470 ,p_ctr_information10 in varchar2 default hr_api.g_varchar2
471 ,p_ctr_information11 in varchar2 default hr_api.g_varchar2
472 ,p_ctr_information12 in varchar2 default hr_api.g_varchar2
473 ,p_ctr_information13 in varchar2 default hr_api.g_varchar2
474 ,p_ctr_information14 in varchar2 default hr_api.g_varchar2
475 ,p_ctr_information15 in varchar2 default hr_api.g_varchar2
476 ,p_ctr_information16 in varchar2 default hr_api.g_varchar2
477 ,p_ctr_information17 in varchar2 default hr_api.g_varchar2
478 ,p_ctr_information18 in varchar2 default hr_api.g_varchar2
479 ,p_ctr_information19 in varchar2 default hr_api.g_varchar2
480 ,p_ctr_information20 in varchar2 default hr_api.g_varchar2
481 ,p_attribute_category in varchar2 default hr_api.g_varchar2
482 ,p_attribute1 in varchar2 default hr_api.g_varchar2
483 ,p_attribute2 in varchar2 default hr_api.g_varchar2
484 ,p_attribute3 in varchar2 default hr_api.g_varchar2
485 ,p_attribute4 in varchar2 default hr_api.g_varchar2
486 ,p_attribute5 in varchar2 default hr_api.g_varchar2
487 ,p_attribute6 in varchar2 default hr_api.g_varchar2
488 ,p_attribute7 in varchar2 default hr_api.g_varchar2
489 ,p_attribute8 in varchar2 default hr_api.g_varchar2
490 ,p_attribute9 in varchar2 default hr_api.g_varchar2
491 ,p_attribute10 in varchar2 default hr_api.g_varchar2
492 ,p_attribute11 in varchar2 default hr_api.g_varchar2
493 ,p_attribute12 in varchar2 default hr_api.g_varchar2
494 ,p_attribute13 in varchar2 default hr_api.g_varchar2
495 ,p_attribute14 in varchar2 default hr_api.g_varchar2
496 ,p_attribute15 in varchar2 default hr_api.g_varchar2
497 ,p_attribute16 in varchar2 default hr_api.g_varchar2
498 ,p_attribute17 in varchar2 default hr_api.g_varchar2
499 ,p_attribute18 in varchar2 default hr_api.g_varchar2
500 ,p_attribute19 in varchar2 default hr_api.g_varchar2
501 ,p_attribute20 in varchar2 default hr_api.g_varchar2
502 ,p_effective_date in date
503 ,p_datetrack_mode in varchar2
504 ) is
505 --
506 -- Declare cursors and local variables
507 --
508 l_proc varchar2(72) := g_package||'update_contract';
509 l_object_version_number per_contracts_f.object_version_number%TYPE;
510 l_effective_start_date per_contracts_f.effective_start_date%TYPE;
511 l_effective_end_date per_contracts_f.effective_end_date%TYPE;
512 l_business_group_id per_contracts_f.business_group_id%TYPE;
513
514 l_temp_ovn number := p_object_version_number;
515 --
516 cursor csr_get_derived_details is
517 select per.business_group_id
518 from per_all_people_f per
519 where per.person_id = p_person_id
520 and p_effective_date between per.effective_start_date
521 and per.effective_end_date;
522 --
523 begin
524 --
525 hr_utility.set_location('Entering:'|| l_proc, 10);
526 --
527 -- Issue a savepoint if operating in validation only mode
528 --
529 savepoint update_contract;
530 --
531 hr_utility.set_location(l_proc, 20);
532 --
533 -- Process Logic
534 -- Get person details.
535 --
536 open csr_get_derived_details;
537 fetch csr_get_derived_details
538 into l_business_group_id;
539 --
540 if csr_get_derived_details%NOTFOUND then
541 close csr_get_derived_details;
542 hr_utility.set_location(l_proc, 30);
543 hr_utility.set_message(801,'HR_7432_ASG_INVALID_PERSON');
544 hr_utility.raise_error;
545 end if;
546 --
547 close csr_get_derived_details;
548 --
549 hr_utility.set_location(l_proc, 40);
550 --
551 l_object_version_number := p_object_version_number;
552 --
553 begin
554 --
555 -- Start of API User Hook for the before hook of update_contract
556 --
557 hr_contract_bk2.update_contract_b
558 (
559 p_contract_id => p_contract_id
560 ,p_business_group_id => l_business_group_id
561 ,p_object_version_number => p_object_version_number
562 ,p_person_id => p_person_id
563 ,p_reference => p_reference
564 ,p_type => p_type
565 ,p_status => p_status
566 ,p_status_reason => p_status_reason
567 ,p_doc_status => p_doc_status
568 ,p_doc_status_change_date => p_doc_status_change_date
569 ,p_description => p_description
570 ,p_duration => p_duration
571 ,p_duration_units => p_duration_units
572 ,p_contractual_job_title => p_contractual_job_title
573 ,p_parties => p_parties
574 ,p_start_reason => p_start_reason
575 ,p_end_reason => p_end_reason
576 ,p_number_of_extensions => p_number_of_extensions
577 ,p_extension_reason => p_extension_reason
578 ,p_extension_period => p_extension_period
579 ,p_extension_period_units => p_extension_period_units
580 ,p_ctr_information_category => p_ctr_information_category
581 ,p_ctr_information1 => p_ctr_information1
582 ,p_ctr_information2 => p_ctr_information2
583 ,p_ctr_information3 => p_ctr_information3
584 ,p_ctr_information4 => p_ctr_information4
585 ,p_ctr_information5 => p_ctr_information5
586 ,p_ctr_information6 => p_ctr_information6
587 ,p_ctr_information7 => p_ctr_information7
588 ,p_ctr_information8 => p_ctr_information8
589 ,p_ctr_information9 => p_ctr_information9
590 ,p_ctr_information10 => p_ctr_information10
591 ,p_ctr_information11 => p_ctr_information11
592 ,p_ctr_information12 => p_ctr_information12
593 ,p_ctr_information13 => p_ctr_information13
594 ,p_ctr_information14 => p_ctr_information14
595 ,p_ctr_information15 => p_ctr_information15
596 ,p_ctr_information16 => p_ctr_information16
597 ,p_ctr_information17 => p_ctr_information17
598 ,p_ctr_information18 => p_ctr_information18
599 ,p_ctr_information19 => p_ctr_information19
600 ,p_ctr_information20 => p_ctr_information20
601 ,p_attribute_category => p_attribute_category
602 ,p_attribute1 => p_attribute1
603 ,p_attribute2 => p_attribute2
604 ,p_attribute3 => p_attribute3
605 ,p_attribute4 => p_attribute4
606 ,p_attribute5 => p_attribute5
607 ,p_attribute6 => p_attribute6
608 ,p_attribute7 => p_attribute7
609 ,p_attribute8 => p_attribute8
610 ,p_attribute9 => p_attribute9
611 ,p_attribute10 => p_attribute10
612 ,p_attribute11 => p_attribute11
613 ,p_attribute12 => p_attribute12
614 ,p_attribute13 => p_attribute13
615 ,p_attribute14 => p_attribute14
616 ,p_attribute15 => p_attribute15
617 ,p_attribute16 => p_attribute16
618 ,p_attribute17 => p_attribute17
619 ,p_attribute18 => p_attribute18
620 ,p_attribute19 => p_attribute19
621 ,p_attribute20 => p_attribute20
622 ,p_effective_date => trunc(p_effective_date)
623 ,p_datetrack_mode => p_datetrack_mode
624 );
625 exception
626 when hr_api.cannot_find_prog_unit then
627 hr_api.cannot_find_prog_unit_error
628 (p_module_name => 'UPDATE_contract'
629 ,p_hook_type => 'BP'
630 );
631 --
632 -- End of API User Hook for the before hook of update_contract
633 --
634 end;
635 --
636 -- Validation in addition to Table Handlers
637 hr_api.mandatory_arg_error
638 (p_api_name => l_proc
639 ,p_argument => 'person_id'
640 ,p_argument_value => p_person_id
641 );
642 --
643 hr_api.mandatory_arg_error
644 (p_api_name => l_proc
645 ,p_argument => 'effective_date'
646 ,p_argument_value => p_effective_date
647 );
648 --
649 per_ctc_upd.upd
650 (
651 p_contract_id => p_contract_id
652 ,p_effective_start_date => l_effective_start_date
653 ,p_effective_end_date => l_effective_end_date
654 ,p_object_version_number => l_object_version_number
655 ,p_reference => p_reference
656 ,p_type => p_type
657 ,p_status => p_status
658 ,p_status_reason => p_status_reason
659 ,p_doc_status => p_doc_status
660 ,p_doc_status_change_date => p_doc_status_change_date
661 ,p_description => p_description
662 ,p_duration => p_duration
663 ,p_duration_units => p_duration_units
664 ,p_contractual_job_title => p_contractual_job_title
665 ,p_parties => p_parties
666 ,p_start_reason => p_start_reason
667 ,p_end_reason => p_end_reason
668 ,p_number_of_extensions => p_number_of_extensions
669 ,p_extension_reason => p_extension_reason
670 ,p_extension_period => p_extension_period
671 ,p_extension_period_units => p_extension_period_units
672 ,p_ctr_information_category => p_ctr_information_category
673 ,p_ctr_information1 => p_ctr_information1
674 ,p_ctr_information2 => p_ctr_information2
675 ,p_ctr_information3 => p_ctr_information3
676 ,p_ctr_information4 => p_ctr_information4
677 ,p_ctr_information5 => p_ctr_information5
678 ,p_ctr_information6 => p_ctr_information6
679 ,p_ctr_information7 => p_ctr_information7
680 ,p_ctr_information8 => p_ctr_information8
681 ,p_ctr_information9 => p_ctr_information9
682 ,p_ctr_information10 => p_ctr_information10
683 ,p_ctr_information11 => p_ctr_information11
684 ,p_ctr_information12 => p_ctr_information12
685 ,p_ctr_information13 => p_ctr_information13
686 ,p_ctr_information14 => p_ctr_information14
687 ,p_ctr_information15 => p_ctr_information15
688 ,p_ctr_information16 => p_ctr_information16
689 ,p_ctr_information17 => p_ctr_information17
690 ,p_ctr_information18 => p_ctr_information18
691 ,p_ctr_information19 => p_ctr_information19
692 ,p_ctr_information20 => p_ctr_information20
693 ,p_attribute_category => p_attribute_category
694 ,p_attribute1 => p_attribute1
695 ,p_attribute2 => p_attribute2
696 ,p_attribute3 => p_attribute3
697 ,p_attribute4 => p_attribute4
698 ,p_attribute5 => p_attribute5
699 ,p_attribute6 => p_attribute6
700 ,p_attribute7 => p_attribute7
701 ,p_attribute8 => p_attribute8
702 ,p_attribute9 => p_attribute9
703 ,p_attribute10 => p_attribute10
704 ,p_attribute11 => p_attribute11
705 ,p_attribute12 => p_attribute12
706 ,p_attribute13 => p_attribute13
707 ,p_attribute14 => p_attribute14
708 ,p_attribute15 => p_attribute15
709 ,p_attribute16 => p_attribute16
710 ,p_attribute17 => p_attribute17
711 ,p_attribute18 => p_attribute18
712 ,p_attribute19 => p_attribute19
713 ,p_attribute20 => p_attribute20
714 ,p_effective_date => trunc(p_effective_date)
715 ,p_datetrack_mode => p_datetrack_mode
716 );
717 --
718 begin
719 --
720 -- Start of API User Hook for the after hook of update_contract
721 --
722 hr_contract_bk2.update_contract_a
723 (
724 p_contract_id => p_contract_id
725 ,p_effective_start_date => l_effective_start_date
726 ,p_effective_end_date => l_effective_end_date
727 ,p_business_group_id => l_business_group_id
728 ,p_object_version_number => l_object_version_number
729 ,p_person_id => p_person_id
730 ,p_reference => p_reference
731 ,p_type => p_type
732 ,p_status => p_status
733 ,p_status_reason => p_status_reason
734 ,p_doc_status => p_doc_status
735 ,p_doc_status_change_date => p_doc_status_change_date
736 ,p_description => p_description
737 ,p_duration => p_duration
738 ,p_duration_units => p_duration_units
739 ,p_contractual_job_title => p_contractual_job_title
740 ,p_parties => p_parties
741 ,p_start_reason => p_start_reason
742 ,p_end_reason => p_end_reason
743 ,p_number_of_extensions => p_number_of_extensions
744 ,p_extension_reason => p_extension_reason
745 ,p_extension_period => p_extension_period
746 ,p_extension_period_units => p_extension_period_units
747 ,p_ctr_information_category => p_ctr_information_category
748 ,p_ctr_information1 => p_ctr_information1
749 ,p_ctr_information2 => p_ctr_information2
750 ,p_ctr_information3 => p_ctr_information3
751 ,p_ctr_information4 => p_ctr_information4
752 ,p_ctr_information5 => p_ctr_information5
753 ,p_ctr_information6 => p_ctr_information6
754 ,p_ctr_information7 => p_ctr_information7
755 ,p_ctr_information8 => p_ctr_information8
756 ,p_ctr_information9 => p_ctr_information9
757 ,p_ctr_information10 => p_ctr_information10
758 ,p_ctr_information11 => p_ctr_information11
759 ,p_ctr_information12 => p_ctr_information12
760 ,p_ctr_information13 => p_ctr_information13
761 ,p_ctr_information14 => p_ctr_information14
762 ,p_ctr_information15 => p_ctr_information15
763 ,p_ctr_information16 => p_ctr_information16
764 ,p_ctr_information17 => p_ctr_information17
765 ,p_ctr_information18 => p_ctr_information18
766 ,p_ctr_information19 => p_ctr_information19
767 ,p_ctr_information20 => p_ctr_information20
768 ,p_attribute_category => p_attribute_category
769 ,p_attribute1 => p_attribute1
770 ,p_attribute2 => p_attribute2
771 ,p_attribute3 => p_attribute3
772 ,p_attribute4 => p_attribute4
773 ,p_attribute5 => p_attribute5
774 ,p_attribute6 => p_attribute6
775 ,p_attribute7 => p_attribute7
776 ,p_attribute8 => p_attribute8
777 ,p_attribute9 => p_attribute9
778 ,p_attribute10 => p_attribute10
779 ,p_attribute11 => p_attribute11
780 ,p_attribute12 => p_attribute12
781 ,p_attribute13 => p_attribute13
782 ,p_attribute14 => p_attribute14
783 ,p_attribute15 => p_attribute15
784 ,p_attribute16 => p_attribute16
785 ,p_attribute17 => p_attribute17
786 ,p_attribute18 => p_attribute18
787 ,p_attribute19 => p_attribute19
788 ,p_attribute20 => p_attribute20
789 ,p_effective_date => trunc(p_effective_date)
790 ,p_datetrack_mode => p_datetrack_mode
791 );
792 exception
793 when hr_api.cannot_find_prog_unit then
794 hr_api.cannot_find_prog_unit_error
795 (p_module_name => 'UPDATE_contract'
796 ,p_hook_type => 'AP'
797 );
798 --
799 -- End of API User Hook for the after hook of update_contract
800 --
801 end;
802 --
803 hr_utility.set_location(l_proc, 60);
804 --
805 -- When in validation only mode raise the Validate_Enabled exception
806 --
807 if p_validate then
808 raise hr_api.validate_enabled;
809 end if;
810 --
811 -- Set all output arguments
812 --
813 p_object_version_number := l_object_version_number;
814 p_effective_start_date := l_effective_start_date;
815 p_effective_end_date := l_effective_end_date;
816 --
817 hr_utility.set_location(' Leaving:'||l_proc, 70);
818 --
819 exception
820 --
821 when hr_api.validate_enabled then
822 --
823 -- As the Validate_Enabled exception has been raised
824 -- we must rollback to the savepoint
825 --
826 ROLLBACK TO update_contract;
827 --
828 -- Only set output warning arguments
829 -- (Any key or derived arguments must be set to null
830 -- when validation only mode is being used.)
831 -- Reset IN OUT and set OUT parameters.
832 p_effective_start_date := null;
833 p_effective_end_date := null;
834 p_object_version_number := l_temp_ovn;
835 --
836 hr_utility.set_location(' Leaving:'||l_proc, 80);
837 --
838 when others then
839 --
840 -- A validation or unexpected error has occured
841 --
842 ROLLBACK TO update_contract;
843 -- Reset IN OUT and set OUT parameters.
844 p_effective_start_date := null;
845 p_effective_end_date := null;
846 p_object_version_number := l_temp_ovn;
847 raise;
848 --
849 end update_contract;
850 -- ----------------------------------------------------------------------------
851 -- |------------------------< delete_contract >----------------------|
852 -- ----------------------------------------------------------------------------
853 --
854 procedure delete_contract
855 (p_validate in boolean default false
856 ,p_contract_id in number
857 ,p_effective_start_date out nocopy date
858 ,p_effective_end_date out nocopy date
859 ,p_object_version_number in out nocopy number
860 ,p_effective_date in date
861 ,p_datetrack_mode in varchar2
862 ) is
863 --
864 -- Declare cursors and local variables
865 --
866 l_proc varchar2(72) := g_package||'update_contract';
867 l_object_version_number per_contracts_f.object_version_number%TYPE;
868 l_effective_start_date per_contracts_f.effective_start_date%TYPE;
869 l_effective_end_date per_contracts_f.effective_end_date%TYPE;
870
871 l_temp_ovn number := p_object_version_number;
872 --
873 begin
874 --
875 hr_utility.set_location('Entering:'|| l_proc, 10);
876 --
877 -- Issue a savepoint if operating in validation only mode
878 --
879 savepoint delete_contract;
880 --
881 hr_utility.set_location(l_proc, 20);
882 --
883 -- Process Logic
884 --
885 hr_utility.set_location(l_proc, 30);
886 --
887 l_object_version_number := p_object_version_number;
888 --
889 begin
890 --
891 -- Start of API User Hook for the before hook of delete_contract
892 --
893 hr_contract_bk3.delete_contract_b
894 (
895 p_contract_id => p_contract_id
896 ,p_object_version_number => p_object_version_number
897 ,p_effective_date => trunc(p_effective_date)
898 ,p_datetrack_mode => p_datetrack_mode
899 );
900 exception
901 when hr_api.cannot_find_prog_unit then
902 hr_api.cannot_find_prog_unit_error
903 (p_module_name => 'DELETE_contract'
904 ,p_hook_type => 'BP'
905 );
906 --
907 -- End of API User Hook for the before hook of delete_contract
908 --
909 end;
910 --
911 per_ctc_del.del
912 (
913 p_contract_id => p_contract_id
914 ,p_effective_start_date => l_effective_start_date
915 ,p_effective_end_date => l_effective_end_date
916 ,p_object_version_number => l_object_version_number
917 ,p_effective_date => p_effective_date
918 ,p_datetrack_mode => p_datetrack_mode
919 );
920 --
921 begin
922 --
923 -- Start of API User Hook for the after hook of delete_contract
924 --
925 hr_contract_bk3.delete_contract_a
926 (
927 p_contract_id => p_contract_id
928 ,p_effective_start_date => l_effective_start_date
929 ,p_effective_end_date => l_effective_end_date
930 ,p_object_version_number => l_object_version_number
931 ,p_effective_date => trunc(p_effective_date)
932 ,p_datetrack_mode => p_datetrack_mode
933 );
934 exception
935 when hr_api.cannot_find_prog_unit then
936 hr_api.cannot_find_prog_unit_error
937 (p_module_name => 'DELETE_CONTRACT'
938 ,p_hook_type => 'AP'
939 );
940 --
941 -- End of API User Hook for the after hook of delete_contract
942 --
943 end;
944 --
945 hr_utility.set_location(l_proc, 60);
946 --
947 -- When in validation only mode raise the Validate_Enabled exception
948 --
949 if p_validate then
950 raise hr_api.validate_enabled;
951 end if;
952 --
953 hr_utility.set_location(' Leaving:'||l_proc, 70);
954 --
955 exception
956 --
957 when hr_api.validate_enabled then
958 --
959 -- As the Validate_Enabled exception has been raised
960 -- we must rollback to the savepoint
961 --
962 ROLLBACK TO delete_contract;
963 --
964 -- Only set output warning arguments
965 -- (Any key or derived arguments must be set to null
966 -- when validation only mode is being used.)
967 --
968 p_effective_start_date := null;
969 p_effective_end_date := null;
970 --
971 when others then
972 --
973 -- A validation or unexpected error has occured
974 --
975 ROLLBACK TO delete_contract;
976 -- Reset IN OUT and set OUT parameters.
977 p_effective_start_date := null;
978 p_effective_end_date := null;
979 p_object_version_number := l_temp_ovn;
980 raise;
981 --
982 end delete_contract;
983 --
984 -- ----------------------------------------------------------------------------
985 -- |-------------------------------< lck >------------------------------------|
986 -- ----------------------------------------------------------------------------
987 --
988 procedure lck
989 (
990 p_contract_id in number
991 ,p_object_version_number in number
992 ,p_effective_date in date
993 ,p_datetrack_mode in varchar2
994 ,p_validation_start_date out nocopy date
995 ,p_validation_end_date out nocopy date
996 ) is
997 --
998 --
999 -- Declare cursors and local variables
1000 --
1001 l_proc varchar2(72) := g_package||'lck';
1002 l_validation_start_date date;
1003 l_validation_end_date date;
1004 --
1005 begin
1006 --
1007 hr_utility.set_location('Entering:'|| l_proc, 10);
1008 --
1009 per_ctc_shd.lck
1010 (
1011 p_contract_id => p_contract_id
1012 ,p_validation_start_date => l_validation_start_date
1013 ,p_validation_end_date => l_validation_end_date
1014 ,p_object_version_number => p_object_version_number
1015 ,p_effective_date => p_effective_date
1016 ,p_datetrack_mode => p_datetrack_mode
1017 );
1018 --
1019 hr_utility.set_location(' Leaving:'||l_proc, 70);
1020 --
1021 end lck;
1022 --
1023 -- ----------------------------------------------------------------------------
1024 -- |--------------------------< maintain_contracts >--------------------------|
1025 -- ----------------------------------------------------------------------------
1026 --
1027 procedure maintain_contracts
1028 (
1029 p_person_id number,
1030 p_new_start_date date,
1031 p_old_start_date date
1032 ) is
1033 --
1034 -- This procedure is called to mantain contracts when person's start-date changes
1035 --
1036 cursor csr_ctr_before IS
1037 SELECT contract_id, effective_start_date, effective_end_date, object_version_number
1038 FROM per_contracts_f pcf
1039 WHERE pcf.person_id = p_person_id
1040 AND pcf.effective_start_date < p_new_start_date
1041 ORDER BY 1,2; -- 'Order by' Added for bug#8670853
1042 --
1043 -- This cursor will bring back all the contracts that
1044 -- start before the new start-date
1045 -- (used when the start-date is moved forward)
1046 --
1047 cursor csr_asg_ctr IS
1048 SELECT assignment_id, contract_id
1049 FROM per_all_assignments_f paf
1050 WHERE paf.person_id = p_person_id
1051 AND p_old_start_date BETWEEN paf.effective_start_date AND
1052 paf.effective_end_date
1053 AND paf.contract_id IS NOT NULL;
1054 --
1055 -- This cursor will bring back all the assignments which
1056 -- exist as of the old start-date
1057 -- and which reference a contract
1058 --
1059 cursor csr_ctr_min (p_contract_id in number) IS
1060 SELECT min(effective_start_date)
1061 FROM per_contracts_f
1062 WHERE contract_id = p_contract_id;
1063 --
1064 cursor csr_ctr_ovn (p_contract_id in number, p_start_date in date) IS
1065 SELECT object_version_number
1066 FROM per_contracts_f
1067 WHERE contract_id = p_contract_id and
1068 effective_start_date = p_start_date;
1069 --
1070 -- cursor for finding orphaned contracts
1071 --
1072 cursor csr_ctr_orphaned (p_person_id in number) is
1073 select contract_id, effective_start_date, object_version_number
1074 from per_contracts_f
1075 where person_id = p_person_id
1076 and not exists(select 1 from per_all_people_f
1077 where person_id = p_person_id);
1078 --
1079 l_contract_id per_contracts_f.contract_id%TYPE;
1080 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
1081 l_start_date per_contracts_f.effective_start_date%TYPE;
1082 l_end_date per_contracts_f.effective_end_date%TYPE;
1083 l_effective_start_date per_contracts_f.effective_start_date%TYPE;
1084 l_object_version_number per_contracts_f.object_version_number%TYPE;
1085 l_proc varchar2(72) := g_package||'maintain_contracts';
1086 --Added below variables for bug#8670853
1087 l_contract_start_date per_contracts_f.effective_start_date%TYPE;
1088 l_temp varchar2(1);
1089 l_prev_con_num per_contracts_f.contract_id%TYPE;
1090 --
1091 BEGIN
1092 --
1093 hr_utility.set_location('Entering:'||l_proc, 5);
1094 l_prev_con_num := 0; -- Fix for bug#8670853
1095 --
1096 IF p_new_start_date > p_old_start_date THEN
1097 --
1098 -- The start-date is moving forward in time :
1099 -- either update start-date for related contracts
1100 -- or remove contracts (when completely out of synch)
1101 --
1102 OPEN csr_ctr_before;
1103 LOOP
1104 FETCH csr_ctr_before INTO l_contract_id, l_start_date, l_end_date,
1105 l_object_version_number;
1106 EXIT WHEN csr_ctr_before%NOTFOUND;
1107 --
1108 -- Fix for bug#8670853
1109 -- The below cursor is used to the find the start date of the current contract
1110 OPEN csr_ctr_min(l_contract_id);
1111 FETCH csr_ctr_min into l_contract_start_date;
1112 IF l_contract_start_date >= p_old_start_date then
1113 l_temp := 'Y';
1114 ELSE
1115 l_temp := 'N';
1116 END IF;
1117 CLOSE csr_ctr_min;
1118 -- l_temp holds the flag, which is used while updating/deleting the contract records
1119 IF l_end_date < p_new_start_date OR l_prev_con_num = l_contract_id THEN
1120 IF l_temp = 'Y' OR l_prev_con_num = l_contract_id THEN -- Fix for bug#8670853
1121 l_prev_con_num := l_contract_id;
1122 --
1123 -- Remove the contract as it now ends before the person exists
1124 --
1125 hr_utility.set_location('Entering:'||l_proc, 10);
1126 --
1127 per_ctc_del.delete_row
1128 (l_contract_id,
1129 l_start_date,
1130 l_object_version_number);
1131 END IF;
1132 ELSE
1133 --
1134 -- Row spanning the new start-date : move its start-date forward
1135 --
1136 hr_utility.set_location('Entering:'||l_proc, 20);
1137 --
1138 -- Added the below condition for bug#8670853
1139 IF l_temp = 'Y' THEN
1140 per_ctc_upd.update_effective_start_date
1141 (l_contract_id,
1142 l_start_date,
1143 p_new_start_date,
1144 l_object_version_number);
1145 END IF;
1146 --
1147 END IF;
1148 END LOOP;
1149 CLOSE csr_ctr_before;
1150 --
1151 ELSE
1152 --
1153 -- The start-date is moving backward in time :
1154 -- move contracts start-date (if any moving assignments).
1155 --
1156 OPEN csr_asg_ctr;
1157 --
1158 LOOP
1159 FETCH csr_asg_ctr INTO l_assignment_id, l_contract_id;
1160 EXIT WHEN csr_asg_ctr%NOTFOUND;
1161 --
1162 OPEN csr_ctr_min (l_contract_id);
1163 --
1164 LOOP
1165 --
1166 FETCH csr_ctr_min INTO l_start_date;
1167 EXIT WHEN csr_ctr_min%NOTFOUND;
1168 --
1169 OPEN csr_ctr_ovn (l_contract_id, l_start_date);
1170 FETCH csr_ctr_ovn INTO l_object_version_number;
1171 CLOSE csr_ctr_ovn;
1172 --
1173 IF l_start_date > p_new_start_date THEN
1174 --
1175 -- The earliest assigned contract does not exist
1176 -- as of the new start-date : move it backwards.
1177 --
1178 hr_utility.set_location('Entering:'||l_proc, 30);
1179 --
1180 per_ctc_upd.update_effective_start_date
1181 (l_contract_id,
1182 l_start_date,
1183 p_new_start_date,
1184 l_object_version_number);
1185 --
1186 END IF;
1187 --
1188 END LOOP;
1189 CLOSE csr_ctr_min;
1190 --
1191 END LOOP;
1192 --
1193 CLOSE csr_asg_ctr;
1194 --
1195 END IF;
1196 --
1197 -- delete all contracts orphaned as a result of purging a person
1198 --
1199 OPEN csr_ctr_orphaned(p_person_id);
1200 loop
1201 FETCH csr_ctr_orphaned into l_contract_id, l_effective_start_date,
1202 l_object_version_number;
1203 EXIT WHEN csr_ctr_orphaned%NOTFOUND;
1204 --
1205 delete_contract
1206 (
1207 p_contract_id => l_contract_id,
1208 p_effective_start_date => l_start_date,
1209 p_effective_end_date => l_end_date,
1210 p_object_version_number => l_object_version_number,
1211 p_effective_date => l_effective_start_date,
1212 p_datetrack_mode => 'ZAP'
1213 );
1214 end loop;
1215 --
1216 CLOSE csr_ctr_orphaned;
1217 --
1218 END maintain_contracts;
1219 --
1220 function get_pps_start_date
1221 (p_person_id in number,
1222 p_active_date in date) return date is
1223
1224 cursor csr_date is select pps.date_start
1225 from per_periods_of_service pps
1226 where p_person_id=pps.person_id
1227 and p_active_date between pps.date_start
1228 and nvl(pps.actual_termination_date, hr_general.end_of_time);
1229
1230 -- set up the variables
1231
1232 l_start_date per_periods_of_service.date_start%type;
1233
1234 begin
1235
1236 open csr_date;
1237 fetch csr_date into l_start_date;
1238 close csr_date;
1239
1240 return(l_start_date);
1241
1242 end get_pps_start_date;
1243 --
1244 function get_pps_end_date
1245 (p_person_id in number,
1246 p_active_date in date) return date is
1247
1248 cursor csr_date is select pps.actual_termination_date
1249 from per_periods_of_service pps
1250 where p_person_id=pps.person_id
1251 and p_active_date between pps.date_start
1252 and pps.actual_termination_date;
1253
1254 -- set up the variables
1255
1256 l_end_date per_periods_of_service.actual_termination_date%type;
1257
1258 begin
1259
1260 open csr_date;
1261 fetch csr_date into l_end_date;
1262 close csr_date;
1263
1264 return(l_end_date);
1265
1266 end get_pps_end_date;
1267 --
1268 function get_meaning
1269 (p_lookup_code in varchar2,
1270 p_lookup_type in varchar2) return varchar2 is
1271
1272 cursor csr_meaning is select meaning
1273 from hr_lookups hrl
1274 where p_lookup_code = hrl.lookup_code
1275 and p_lookup_type = hrl.lookup_type
1276 and hrl.application_id = 800;
1277
1278 -- set up the variables
1279
1280 l_meaning hr_lookups.meaning%type;
1281
1282 begin
1283
1284 open csr_meaning;
1285 fetch csr_meaning into l_meaning;
1286 close csr_meaning;
1287
1288 return(l_meaning);
1289
1290 end get_meaning;
1291 --
1292 function get_active_start_date
1293 (p_contract_id in number,
1294 p_effective_date in date,
1295 p_status in varchar2) return date is
1296
1297 cursor csr_date_active is select min(pcf1.effective_start_date)
1298 from per_contracts_f pcf1
1299 where p_contract_id=pcf1.contract_id
1300 and p_effective_date >= pcf1.effective_start_date
1301 and pcf1.status like 'A-%';
1302
1303 cursor csr_date_other is select max(pcf1.effective_end_date) + 1
1304 from per_contracts_f pcf1
1305 where p_contract_id=pcf1.contract_id
1306 and p_effective_date >= pcf1.effective_end_date
1307 and pcf1.status not like 'A-%';
1308
1309
1310 cursor csr_prev_date_active is select max(pcf1.effective_start_date)
1311 from per_contracts_f pcf1
1312 where p_contract_id=pcf1.contract_id
1313 and p_effective_date > pcf1.effective_start_date
1314 and pcf1.status like 'A-%';
1315
1316 -- set up the variables
1317
1318 l_other_start_date per_contracts_f.EFFECTIVE_END_DATE%type;
1319 l_active_start_date per_contracts_f.EFFECTIVE_START_DATE%type;
1320 l_prev_start_date per_contracts_f.EFFECTIVE_START_DATE%type;
1321
1322
1323 begin
1324
1325 if p_status like 'A-%' then
1326
1327 open csr_date_active;
1328 fetch csr_date_active into l_active_start_date;
1329 close csr_date_active;
1330
1331 open csr_date_other;
1332 fetch csr_date_other into l_other_start_date;
1333 close csr_date_other;
1334
1335 if l_other_start_date is null then
1336 l_other_start_date := hr_general.start_of_time;
1337 end if;
1338
1339 if (l_active_start_date > l_other_start_date) then
1340 return(l_active_start_date);
1341 else
1342 return(l_other_start_date);
1343 end if;
1344
1345 else
1346
1347 open csr_prev_date_active;
1348 fetch csr_prev_date_active into l_prev_start_date;
1349 close csr_prev_date_active;
1350 return(l_prev_start_date);
1351 end if;
1352
1353 end get_active_start_date;
1354 --
1355 function get_active_end_date (p_contract_id in number,
1356 p_effective_date in date,
1357 p_status in varchar2) return date is
1358
1359 cursor csr_date_active is select max(pcf1.effective_end_date)
1360 from per_contracts_f pcf1
1361 where p_contract_id = pcf1.contract_id
1362 and p_effective_date <= pcf1.effective_end_date
1363 and pcf1.status like 'A-%';
1364
1365 cursor csr_date_other is select min(pcf1.effective_start_date) - 1
1366 from per_contracts_f pcf1
1367 where p_contract_id=pcf1.contract_id
1368 and p_effective_date <=pcf1.effective_start_date
1369 and pcf1.status not like 'A-%';
1370
1371 cursor csr_prev_date_active is select max(pcf1.effective_end_date)
1372 from per_contracts_f pcf1
1373 where p_contract_id = pcf1.contract_id
1374 and p_effective_date > pcf1.effective_end_date
1375 and pcf1.status like 'A-%';
1376
1377
1378 -- set up the variables
1379
1380 l_other_end_date per_contracts_f.EFFECTIVE_END_DATE%type;
1381 l_active_end_date per_contracts_f.EFFECTIVE_START_DATE%type;
1382 l_prev_end_date per_contracts_f.EFFECTIVE_START_DATE%type;
1383
1384
1385 begin
1386
1387 if p_status like 'A-%' then
1388
1389 open csr_date_active;
1390 fetch csr_date_active into l_active_end_date;
1391 close csr_date_active;
1392
1393 open csr_date_other;
1394 fetch csr_date_other into l_other_end_date;
1395 close csr_date_other;
1396
1397 if l_other_end_date is null then
1398 l_other_end_date := hr_general.end_of_time;
1399 end if;
1400
1401 if (l_active_end_date <= l_other_end_date) then
1402
1403 if (l_active_end_date = hr_general.end_of_time) then
1404 l_active_end_date := null;
1405 end if;
1406 return(l_active_end_date);
1407 else
1408 return(l_other_end_date);
1409 end if;
1410
1411 else
1412 open csr_prev_date_active;
1413 fetch csr_prev_date_active into l_prev_end_date;
1414 close csr_prev_date_active;
1415 return(l_prev_end_date);
1416 end if;
1417
1418 end get_active_end_date;
1419 --
1420 end hr_contract_api;