[Home] [Help]
PACKAGE BODY: APPS.HR_CONTRACT_API
Source
1 Package Body hr_contract_api as
2 /* $Header: hrctcapi.pkb 115.18 2002/12/16 09:37:50 hjonnala 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
320 ,p_parties => p_parties
317 ,p_duration => p_duration
318 ,p_duration_units => p_duration_units
319 ,p_contractual_job_title => p_contractual_job_title
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 -- ----------------------------------------------------------------------------
436 procedure update_contract
433 -- |------------------------< update_contract >--- ------------------|
434 -- ----------------------------------------------------------------------------
435 --
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
522 --
519 where per.person_id = p_person_id
520 and p_effective_date between per.effective_start_date
521 and per.effective_end_date;
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'
632 -- End of API User Hook for the before hook of update_contract
629 ,p_hook_type => 'BP'
630 );
631 --
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
742 ,p_end_reason => p_end_reason
739 ,p_contractual_job_title => p_contractual_job_title
740 ,p_parties => p_parties
741 ,p_start_reason => p_start_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 -- ----------------------------------------------------------------------------
856 ,p_contract_id in number
853 --
854 procedure delete_contract
855 (p_validate in boolean default false
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 --
1004 --
1001 l_proc varchar2(72) := g_package||'lck';
1002 l_validation_start_date date;
1003 l_validation_end_date date;
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 --
1042 -- This cursor will bring back all the contracts that
1043 -- start before the new start-date
1044 -- (used when the start-date is moved forward)
1045 --
1046 cursor csr_asg_ctr IS
1047 SELECT assignment_id, contract_id
1048 FROM per_all_assignments_f paf
1049 WHERE paf.person_id = p_person_id
1050 AND p_old_start_date BETWEEN paf.effective_start_date AND
1051 paf.effective_end_date
1052 AND paf.contract_id IS NOT NULL;
1053 --
1054 -- This cursor will bring back all the assignments which
1055 -- exist as of the old start-date
1056 -- and which reference a contract
1057 --
1058 cursor csr_ctr_min (p_contract_id in number) IS
1059 SELECT min(effective_start_date)
1060 FROM per_contracts_f
1061 WHERE contract_id = p_contract_id;
1062 --
1063 cursor csr_ctr_ovn (p_contract_id in number, p_start_date in date) IS
1064 SELECT object_version_number
1065 FROM per_contracts_f
1066 WHERE contract_id = p_contract_id and
1067 effective_start_date = p_start_date;
1068 --
1069 -- cursor for finding orphaned contracts
1070 --
1071 cursor csr_ctr_orphaned (p_person_id in number) is
1072 select contract_id, effective_start_date, object_version_number
1073 from per_contracts_f
1074 where person_id = p_person_id
1075 and not exists(select 1 from per_all_people_f
1076 where person_id = p_person_id);
1077 --
1078 l_contract_id per_contracts_f.contract_id%TYPE;
1079 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
1080 l_start_date per_contracts_f.effective_start_date%TYPE;
1081 l_end_date per_contracts_f.effective_end_date%TYPE;
1082 l_effective_start_date per_contracts_f.effective_start_date%TYPE;
1083 l_object_version_number per_contracts_f.object_version_number%TYPE;
1084 l_proc varchar2(72) := g_package||'maintain_contracts';
1085 --
1086 BEGIN
1087 --
1088 hr_utility.set_location('Entering:'||l_proc, 5);
1089 --
1090 IF p_new_start_date > p_old_start_date THEN
1091 --
1092 -- The start-date is moving forward in time :
1093 -- either update start-date for related contracts
1094 -- or remove contracts (when completely out of synch)
1095 --
1096 OPEN csr_ctr_before;
1097 LOOP
1098 FETCH csr_ctr_before INTO l_contract_id, l_start_date, l_end_date,
1099 l_object_version_number;
1100 EXIT WHEN csr_ctr_before%NOTFOUND;
1101 --
1102 IF l_end_date < p_new_start_date THEN
1103 --
1104 -- Remove the contract as it now ends before the person exists
1105 --
1106 hr_utility.set_location('Entering:'||l_proc, 10);
1107 --
1108 per_ctc_del.delete_row
1109 (l_contract_id,
1110 l_start_date,
1111 l_object_version_number);
1112 ELSE
1113 --
1114 -- Row spanning the new start-date : move its start-date forward
1115 --
1116 hr_utility.set_location('Entering:'||l_proc, 20);
1117 --
1118 per_ctc_upd.update_effective_start_date
1119 (l_contract_id,
1120 l_start_date,
1121 p_new_start_date,
1122 l_object_version_number);
1123 --
1124 END IF;
1125 END LOOP;
1126 CLOSE csr_ctr_before;
1127 --
1128 ELSE
1129 --
1130 -- The start-date is moving backward in time :
1131 -- move contracts start-date (if any moving assignments).
1132 --
1133 OPEN csr_asg_ctr;
1134 --
1135 LOOP
1136 FETCH csr_asg_ctr INTO l_assignment_id, l_contract_id;
1137 EXIT WHEN csr_asg_ctr%NOTFOUND;
1138 --
1139 OPEN csr_ctr_min (l_contract_id);
1140 --
1141 LOOP
1142 --
1146 OPEN csr_ctr_ovn (l_contract_id, l_start_date);
1143 FETCH csr_ctr_min INTO l_start_date;
1144 EXIT WHEN csr_ctr_min%NOTFOUND;
1145 --
1147 FETCH csr_ctr_ovn INTO l_object_version_number;
1148 CLOSE csr_ctr_ovn;
1149 --
1150 IF l_start_date > p_new_start_date THEN
1151 --
1152 -- The earliest assigned contract does not exist
1153 -- as of the new start-date : move it backwards.
1154 --
1155 hr_utility.set_location('Entering:'||l_proc, 30);
1156 --
1157 per_ctc_upd.update_effective_start_date
1158 (l_contract_id,
1159 l_start_date,
1160 p_new_start_date,
1161 l_object_version_number);
1162 --
1163 END IF;
1164 --
1165 END LOOP;
1166 CLOSE csr_ctr_min;
1167 --
1168 END LOOP;
1169 --
1170 CLOSE csr_asg_ctr;
1171 --
1172 END IF;
1173 --
1174 -- delete all contracts orphaned as a result of purging a person
1175 --
1176 OPEN csr_ctr_orphaned(p_person_id);
1177 loop
1178 FETCH csr_ctr_orphaned into l_contract_id, l_effective_start_date,
1179 l_object_version_number;
1180 EXIT WHEN csr_ctr_orphaned%NOTFOUND;
1181 --
1182 delete_contract
1183 (
1184 p_contract_id => l_contract_id,
1185 p_effective_start_date => l_start_date,
1186 p_effective_end_date => l_end_date,
1187 p_object_version_number => l_object_version_number,
1188 p_effective_date => l_effective_start_date,
1189 p_datetrack_mode => 'ZAP'
1190 );
1191 end loop;
1192 --
1193 CLOSE csr_ctr_orphaned;
1194 --
1195 END maintain_contracts;
1196 --
1197 function get_pps_start_date
1198 (p_person_id in number,
1199 p_active_date in date) return date is
1200
1201 cursor csr_date is select pps.date_start
1202 from per_periods_of_service pps
1203 where p_person_id=pps.person_id
1204 and p_active_date between pps.date_start
1205 and nvl(pps.actual_termination_date, hr_general.end_of_time);
1206
1207 -- set up the variables
1208
1209 l_start_date per_periods_of_service.date_start%type;
1210
1211 begin
1212
1213 open csr_date;
1214 fetch csr_date into l_start_date;
1215 close csr_date;
1216
1217 return(l_start_date);
1218
1219 end get_pps_start_date;
1220 --
1221 function get_pps_end_date
1222 (p_person_id in number,
1223 p_active_date in date) return date is
1224
1225 cursor csr_date is select pps.actual_termination_date
1226 from per_periods_of_service pps
1227 where p_person_id=pps.person_id
1228 and p_active_date between pps.date_start
1229 and pps.actual_termination_date;
1230
1231 -- set up the variables
1232
1233 l_end_date per_periods_of_service.actual_termination_date%type;
1234
1235 begin
1236
1237 open csr_date;
1238 fetch csr_date into l_end_date;
1239 close csr_date;
1240
1241 return(l_end_date);
1242
1243 end get_pps_end_date;
1244 --
1245 function get_meaning
1246 (p_lookup_code in varchar2,
1247 p_lookup_type in varchar2) return varchar2 is
1248
1249 cursor csr_meaning is select meaning
1250 from hr_lookups hrl
1251 where p_lookup_code = hrl.lookup_code
1252 and p_lookup_type = hrl.lookup_type
1253 and hrl.application_id = 800;
1254
1255 -- set up the variables
1256
1257 l_meaning hr_lookups.meaning%type;
1258
1259 begin
1260
1261 open csr_meaning;
1262 fetch csr_meaning into l_meaning;
1263 close csr_meaning;
1264
1265 return(l_meaning);
1266
1267 end get_meaning;
1268 --
1269 function get_active_start_date
1270 (p_contract_id in number,
1271 p_effective_date in date,
1272 p_status in varchar2) return date is
1273
1274 cursor csr_date_active is select min(pcf1.effective_start_date)
1275 from per_contracts_f pcf1
1276 where p_contract_id=pcf1.contract_id
1277 and p_effective_date >= pcf1.effective_start_date
1278 and pcf1.status like 'A-%';
1279
1280 cursor csr_date_other is select max(pcf1.effective_end_date) + 1
1281 from per_contracts_f pcf1
1282 where p_contract_id=pcf1.contract_id
1283 and p_effective_date >= pcf1.effective_end_date
1284 and pcf1.status not like 'A-%';
1285
1286
1287 cursor csr_prev_date_active is select max(pcf1.effective_start_date)
1288 from per_contracts_f pcf1
1289 where p_contract_id=pcf1.contract_id
1290 and p_effective_date > pcf1.effective_start_date
1291 and pcf1.status like 'A-%';
1292
1293 -- set up the variables
1294
1295 l_other_start_date per_contracts_f.EFFECTIVE_END_DATE%type;
1296 l_active_start_date per_contracts_f.EFFECTIVE_START_DATE%type;
1297 l_prev_start_date per_contracts_f.EFFECTIVE_START_DATE%type;
1298
1299
1300 begin
1301
1302 if p_status like 'A-%' then
1303
1304 open csr_date_active;
1305 fetch csr_date_active into l_active_start_date;
1306 close csr_date_active;
1307
1308 open csr_date_other;
1309 fetch csr_date_other into l_other_start_date;
1310 close csr_date_other;
1311
1312 if l_other_start_date is null then
1313 l_other_start_date := hr_general.start_of_time;
1314 end if;
1315
1316 if (l_active_start_date > l_other_start_date) then
1317 return(l_active_start_date);
1318 else
1319 return(l_other_start_date);
1320 end if;
1321
1322 else
1323
1324 open csr_prev_date_active;
1325 fetch csr_prev_date_active into l_prev_start_date;
1326 close csr_prev_date_active;
1327 return(l_prev_start_date);
1328 end if;
1329
1330 end get_active_start_date;
1331 --
1332 function get_active_end_date (p_contract_id in number,
1333 p_effective_date in date,
1334 p_status in varchar2) return date is
1335
1336 cursor csr_date_active is select max(pcf1.effective_end_date)
1337 from per_contracts_f pcf1
1338 where p_contract_id = pcf1.contract_id
1339 and p_effective_date <= pcf1.effective_end_date
1340 and pcf1.status like 'A-%';
1341
1342 cursor csr_date_other is select min(pcf1.effective_start_date) - 1
1343 from per_contracts_f pcf1
1344 where p_contract_id=pcf1.contract_id
1345 and p_effective_date <=pcf1.effective_start_date
1346 and pcf1.status not like 'A-%';
1347
1348 cursor csr_prev_date_active is select max(pcf1.effective_end_date)
1349 from per_contracts_f pcf1
1350 where p_contract_id = pcf1.contract_id
1351 and p_effective_date > pcf1.effective_end_date
1352 and pcf1.status like 'A-%';
1353
1354
1355 -- set up the variables
1356
1357 l_other_end_date per_contracts_f.EFFECTIVE_END_DATE%type;
1358 l_active_end_date per_contracts_f.EFFECTIVE_START_DATE%type;
1359 l_prev_end_date per_contracts_f.EFFECTIVE_START_DATE%type;
1360
1361
1362 begin
1363
1364 if p_status like 'A-%' then
1368 close csr_date_active;
1365
1366 open csr_date_active;
1367 fetch csr_date_active into l_active_end_date;
1369
1370 open csr_date_other;
1371 fetch csr_date_other into l_other_end_date;
1372 close csr_date_other;
1373
1374 if l_other_end_date is null then
1375 l_other_end_date := hr_general.end_of_time;
1376 end if;
1377
1378 if (l_active_end_date <= l_other_end_date) then
1379
1380 if (l_active_end_date = hr_general.end_of_time) then
1381 l_active_end_date := null;
1382 end if;
1383 return(l_active_end_date);
1384 else
1385 return(l_other_end_date);
1386 end if;
1387
1388 else
1389 open csr_prev_date_active;
1390 fetch csr_prev_date_active into l_prev_end_date;
1391 close csr_prev_date_active;
1392 return(l_prev_end_date);
1393 end if;
1394
1395 end get_active_end_date;
1396 --
1397 end hr_contract_api;