DBA Data[Home] [Help]

PACKAGE BODY: APPS.HR_CONTINGENT_WORKER_API

Source


1 Package Body hr_contingent_worker_api as
2 /* $Header: pecwkapi.pkb 120.4.12010000.1 2008/07/28 04:28:10 appldev ship $ */
3 --
4 -- Package Variables
5 --
6 g_package  varchar2(33) := '  hr_contingent_worker_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------------< create_cwk >------------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_cwk
13   (p_validate                      in     boolean  default false
14   ,p_start_date                    in     date
15   ,p_business_group_id             in     number
16   ,p_last_name                     in     varchar2
17   ,p_person_type_id                in     number   default null
18   ,p_npw_number                    in out nocopy varchar2
19   ,p_background_check_status       in     varchar2 default null
20   ,p_background_date_check         in     date     default null
21   ,p_blood_type                    in     varchar2 default null
22   ,p_comments                      in     varchar2 default null
23   ,p_correspondence_language       in     varchar2 default null
24   ,p_country_of_birth              in     varchar2 default null
25   ,p_date_of_birth                 in     date     default null
26   ,p_date_of_death                 in     date     default null
27   ,p_dpdnt_adoption_date           in     date     default null
28   ,p_dpdnt_vlntry_svce_flag        in     varchar2 default null
29   ,p_email_address                 in     varchar2 default null
30   ,p_first_name                    in     varchar2 default null
31   ,p_fte_capacity                  in     number   default null
32   ,p_honors                        in     varchar2 default null
33   ,p_internal_location             in     varchar2 default null
34   ,p_known_as                      in     varchar2 default null
35   ,p_last_medical_test_by          in     varchar2 default null
36   ,p_last_medical_test_date        in     date     default null
37   ,p_mailstop                      in     varchar2 default null
38   ,p_marital_status                in     varchar2 default null
39   ,p_middle_names                  in     varchar2 default null
40   ,p_national_identifier           in     varchar2 default null
41   ,p_nationality                   in     varchar2 default null
42   ,p_office_number                 in     varchar2 default null
43   ,p_on_military_service           in     varchar2 default null
44   ,p_party_id                      in     number   default null
45   ,p_pre_name_adjunct              in     varchar2 default null
46   ,p_previous_last_name            in     varchar2 default null
47   ,p_projected_placement_end       in     date     default null
48   ,p_receipt_of_death_cert_date    in     date     default null
49   ,p_region_of_birth               in     varchar2 default null
50   ,p_registered_disabled_flag      in     varchar2 default null
51   ,p_resume_exists                 in     varchar2 default null
52   ,p_resume_last_updated           in     date     default null
53   ,p_second_passport_exists        in     varchar2 default null
54   ,p_sex                           in     varchar2 default null
55   ,p_student_status                in     varchar2 default null
56   ,p_suffix                        in     varchar2 default null
57   ,p_title                         in     varchar2 default null
58   ,p_town_of_birth                 in     varchar2 default null
59   ,p_uses_tobacco_flag             in     varchar2 default null
60   ,p_vendor_id                     in     number   default null
61   ,p_work_schedule                 in     varchar2 default null
62   ,p_work_telephone                in     varchar2 default null
63   ,p_exp_check_send_to_address     in     varchar2 default null
64   ,p_hold_applicant_date_until     in     date     default null
65   ,p_date_employee_data_verified   in     date     default null
66   ,p_benefit_group_id              in     number   default null
67   ,p_coord_ben_med_pln_no          in     varchar2 default null
68   ,p_coord_ben_no_cvg_flag         in     varchar2 default null
69   ,p_original_date_of_hire         in     date     default null
70   ,p_attribute_category            in     varchar2 default null
71   ,p_attribute1                    in     varchar2 default null
72   ,p_attribute2                    in     varchar2 default null
73   ,p_attribute3                    in     varchar2 default null
74   ,p_attribute4                    in     varchar2 default null
75   ,p_attribute5                    in     varchar2 default null
76   ,p_attribute6                    in     varchar2 default null
77   ,p_attribute7                    in     varchar2 default null
78   ,p_attribute8                    in     varchar2 default null
79   ,p_attribute9                    in     varchar2 default null
80   ,p_attribute10                   in     varchar2 default null
81   ,p_attribute11                   in     varchar2 default null
82   ,p_attribute12                   in     varchar2 default null
83   ,p_attribute13                   in     varchar2 default null
84   ,p_attribute14                   in     varchar2 default null
85   ,p_attribute15                   in     varchar2 default null
86   ,p_attribute16                   in     varchar2 default null
87   ,p_attribute17                   in     varchar2 default null
88   ,p_attribute18                   in     varchar2 default null
89   ,p_attribute19                   in     varchar2 default null
90   ,p_attribute20                   in     varchar2 default null
91   ,p_attribute21                   in     varchar2 default null
92   ,p_attribute22                   in     varchar2 default null
93   ,p_attribute23                   in     varchar2 default null
94   ,p_attribute24                   in     varchar2 default null
95   ,p_attribute25                   in     varchar2 default null
96   ,p_attribute26                   in     varchar2 default null
97   ,p_attribute27                   in     varchar2 default null
98   ,p_attribute28                   in     varchar2 default null
99   ,p_attribute29                   in     varchar2 default null
100   ,p_attribute30                   in     varchar2 default null
101   ,p_per_information_category      in     varchar2 default null
102   ,p_per_information1              in     varchar2 default null
103   ,p_per_information2              in     varchar2 default null
104   ,p_per_information3              in     varchar2 default null
105   ,p_per_information4              in     varchar2 default null
106   ,p_per_information5              in     varchar2 default null
107   ,p_per_information6              in     varchar2 default null
108   ,p_per_information7              in     varchar2 default null
109   ,p_per_information8              in     varchar2 default null
110   ,p_per_information9              in     varchar2 default null
111   ,p_per_information10             in     varchar2 default null
112   ,p_per_information11             in     varchar2 default null
113   ,p_per_information12             in     varchar2 default null
114   ,p_per_information13             in     varchar2 default null
115   ,p_per_information14             in     varchar2 default null
116   ,p_per_information15             in     varchar2 default null
117   ,p_per_information16             in       varchar2 default null
118   ,p_per_information17             in       varchar2 default null
119   ,p_per_information18             in       varchar2 default null
120   ,p_per_information19             in       varchar2 default null
121   ,p_per_information20             in       varchar2 default null
122   ,p_per_information21             in       varchar2 default null
123   ,p_per_information22             in       varchar2 default null
124   ,p_per_information23             in       varchar2 default null
125   ,p_per_information24             in       varchar2 default null
126   ,p_per_information25             in       varchar2 default null
127   ,p_per_information26             in       varchar2 default null
128   ,p_per_information27             in       varchar2 default null
129   ,p_per_information28             in       varchar2 default null
130   ,p_per_information29             in       varchar2 default null
131   ,p_per_information30             in       varchar2 default null
132   ,p_person_id                        out nocopy   number
133   ,p_per_object_version_number        out nocopy   number
134   ,p_per_effective_start_date         out nocopy   date
135   ,p_per_effective_end_date           out nocopy   date
136   ,p_pdp_object_version_number        out nocopy   number
137   ,p_full_name                        out nocopy   varchar2
138   ,p_comment_id                       out nocopy   number
139   ,p_assignment_id                    out nocopy   number
140   ,p_asg_object_version_number        out nocopy   number
141   ,p_assignment_sequence              out nocopy   number
142   ,p_assignment_number                out nocopy   varchar2
143   ,p_name_combination_warning         out nocopy   boolean
144   ) is
145   --
146   -- Declare cursors and local variables
147   --
148 
149   l_proc                varchar2(72) := g_package||'create_cwk';
150   l_person_id                         number;
151   l_person_type_id                    per_person_types.person_type_id%type  := p_person_type_id;
152   l_person_type_id1                   per_person_types.person_type_id%type;
153   l_start_date                        date;
154   l_date_of_birth                     date;
155   l_date_of_death                     date;
156   l_receipt_of_death_cert_date        date;
157   l_dpdnt_adoption_date               date;
158   l_per_object_version_number         number;
159   l_per_effective_start_date          date;
160   l_per_effective_end_date            date;
161   l_current_npw_flag                  per_people_f.current_npw_flag%type;
162   l_current_applicant_flag            per_people_f.current_applicant_flag%type;
163   l_current_employee_flag             per_people_f.current_employee_flag%type;
164   l_current_emp_or_apl_flag           per_people_f.current_emp_or_apl_flag%type;
165   l_employee_number                   per_people_f.employee_number%TYPE;
166   l_applicant_number                  per_people_f.applicant_number%TYPE;
167   l_npw_number                        per_people_f.npw_number%TYPE;
168   l_full_name                         per_people_f.full_name%type;
169   l_comment_id                        number;
170   l_dob_null_warning                  boolean;
171   l_orig_hire_warning                 boolean;
172   l_assignment_id                     number;
173   l_asg_object_version_number         number;
174   l_assignment_sequence               per_assignments_f.assignment_sequence%type;
175   l_assignment_number                 per_assignments_f.assignment_number%type;
176   l_name_combination_warning          boolean;
177   l_period_of_placement_id            number;
178   l_pdp_object_version_number         number;
179   l_phn_object_version_number         number;
180   l_phone_id                          number;
181 --
182 begin
183 
184   hr_utility.set_location('Entering:'|| l_proc, 10);
185 
186   --
187   -- Issue a savepoint
188   --
189   savepoint create_cwk;
190   --
191   -- Truncate the time portion from all IN date parameters
192   --
193   l_start_date                  := trunc(p_start_date);
194   l_date_of_birth               := trunc(p_date_of_birth);
195   l_date_of_death               := trunc(p_date_of_death);
196   l_receipt_of_death_cert_date  := trunc(p_receipt_of_death_cert_date);
197   l_dpdnt_adoption_date         := trunc(p_dpdnt_adoption_date);
198   --
199   -- Call Before Process User Hook
200   --
201   begin
202     hr_contingent_worker_bk1.create_cwk_b
203       (p_start_date                    => l_start_date
204       ,p_business_group_id             => p_business_group_id
205       ,p_last_name                     => p_last_name
206       ,p_person_type_id                => p_person_type_id
207       ,p_npw_number                    => p_npw_number
208       ,p_background_check_status       => p_background_check_status
209       ,p_background_date_check         => p_background_date_check
210       ,p_blood_type                    => p_blood_type
211       ,p_comments                      => p_comments
212       ,p_correspondence_language       => p_correspondence_language
213       ,p_country_of_birth              => p_country_of_birth
214       ,p_date_of_birth                 => l_date_of_birth
215       ,p_date_of_death                 => l_date_of_death
216       ,p_dpdnt_adoption_date           => l_dpdnt_adoption_date
217       ,p_dpdnt_vlntry_svce_flag        => p_dpdnt_vlntry_svce_flag
218       ,p_email_address                 => p_email_address
219       ,p_first_name                    => p_first_name
220       ,p_fte_capacity                  => p_fte_capacity
221       ,p_honors                        => p_honors
222       ,p_internal_location             => p_internal_location
223       ,p_known_as                      => p_known_as
224       ,p_last_medical_test_by          => p_last_medical_test_by
225       ,p_last_medical_test_date        => p_last_medical_test_date
226       ,p_mailstop                      => p_mailstop
227       ,p_marital_status                => p_marital_status
228       ,p_middle_names                  => p_middle_names
229       ,p_national_identifier           => p_national_identifier
230       ,p_nationality                   => p_nationality
231       ,p_office_number                 => p_office_number
232       ,p_on_military_service           => p_on_military_service
233       ,p_party_id                      => p_party_id
234       ,p_pre_name_adjunct              => p_pre_name_adjunct
235       ,p_previous_last_name            => p_previous_last_name
236       ,p_projected_placement_end       => null
237       ,p_receipt_of_death_cert_date    => l_receipt_of_death_cert_date
238       ,p_region_of_birth               => p_region_of_birth
239       ,p_registered_disabled_flag      => p_registered_disabled_flag
240       ,p_resume_exists                 => p_resume_exists
241       ,p_resume_last_updated           => p_resume_last_updated
242       ,p_second_passport_exists        => p_second_passport_exists
243       ,p_sex                           => p_sex
244       ,p_student_status                => p_student_status
245       ,p_suffix                        => p_suffix
246       ,p_title                         => p_title
247       ,p_town_of_birth                 => p_town_of_birth
248       ,p_uses_tobacco_flag             => p_uses_tobacco_flag
249       ,p_vendor_id                     => p_vendor_id
250       ,p_work_schedule                 => p_work_schedule
251       ,p_work_telephone                => p_work_telephone
252       ,p_exp_check_send_to_address     => p_exp_check_send_to_address
253       ,p_hold_applicant_date_until     => p_hold_applicant_date_until
254       ,p_date_employee_data_verified   => p_date_employee_data_verified
255       ,p_benefit_group_id              => p_benefit_group_id
256       ,p_coord_ben_med_pln_no          => p_coord_ben_med_pln_no
257       ,p_coord_ben_no_cvg_flag         => p_coord_ben_no_cvg_flag
258       ,p_original_date_of_hire         => p_original_date_of_hire
259       ,p_attribute_category            => p_attribute_category
260       ,p_attribute1                    => p_attribute1
261       ,p_attribute2                    => p_attribute2
262       ,p_attribute3                    => p_attribute3
263       ,p_attribute4                    => p_attribute4
264       ,p_attribute5                    => p_attribute5
265       ,p_attribute6                    => p_attribute6
266       ,p_attribute7                    => p_attribute7
267       ,p_attribute8                    => p_attribute8
268       ,p_attribute9                    => p_attribute9
269       ,p_attribute10                   => p_attribute10
270       ,p_attribute11                   => p_attribute11
271       ,p_attribute12                   => p_attribute12
272       ,p_attribute13                   => p_attribute13
273       ,p_attribute14                   => p_attribute14
274       ,p_attribute15                   => p_attribute15
275       ,p_attribute16                   => p_attribute16
276       ,p_attribute17                   => p_attribute17
277       ,p_attribute18                   => p_attribute18
278       ,p_attribute19                   => p_attribute19
279       ,p_attribute20                   => p_attribute20
280       ,p_attribute21                   => p_attribute21
281       ,p_attribute22                   => p_attribute22
282       ,p_attribute23                   => p_attribute23
283       ,p_attribute24                   => p_attribute24
284       ,p_attribute25                   => p_attribute25
285       ,p_attribute26                   => p_attribute26
286       ,p_attribute27                   => p_attribute27
287       ,p_attribute28                   => p_attribute28
288       ,p_attribute29                   => p_attribute29
289       ,p_attribute30                   => p_attribute30
290       ,p_per_information_category      => p_per_information_category
291       ,p_per_information1              => p_per_information1
292       ,p_per_information2              => p_per_information2
293       ,p_per_information3              => p_per_information3
294       ,p_per_information4              => p_per_information4
295       ,p_per_information5              => p_per_information5
296       ,p_per_information6              => p_per_information6
297       ,p_per_information7              => p_per_information7
298       ,p_per_information8              => p_per_information8
299       ,p_per_information9              => p_per_information9
300       ,p_per_information10             => p_per_information10
301       ,p_per_information11             => p_per_information11
302       ,p_per_information12             => p_per_information12
303       ,p_per_information13             => p_per_information13
304       ,p_per_information14             => p_per_information14
305       ,p_per_information15             => p_per_information15
306       ,p_per_information16             => p_per_information16
307       ,p_per_information17             => p_per_information17
308       ,p_per_information18             => p_per_information18
309       ,p_per_information19             => p_per_information19
310       ,p_per_information20             => p_per_information20
311       ,p_per_information21             => p_per_information21
312       ,p_per_information22             => p_per_information22
313       ,p_per_information23             => p_per_information23
314       ,p_per_information24             => p_per_information24
315       ,p_per_information25             => p_per_information25
316       ,p_per_information26             => p_per_information26
317       ,p_per_information27             => p_per_information27
318       ,p_per_information28             => p_per_information28
319       ,p_per_information29             => p_per_information29
320       ,p_per_information30             => p_per_information30
321       );
322   exception
323     when hr_api.cannot_find_prog_unit then
324       hr_api.cannot_find_prog_unit_error
325         (p_module_name => 'create_cwk'
326         ,p_hook_type   => 'BP'
327         );
328   end;
329   --
330   hr_utility.set_location(l_proc, 20);
331   --
332   -- Validation in addition to Row Handlers
333   --
334   -- If the specified person type id is not null then check that it
335   -- corresponds to type 'CWK', is currently active and is in the correct
336   -- business group, otherwise set person type to the active default for CWK
337   -- in the current business group.
338   --
339   per_per_bus.chk_person_type
340     (p_person_type_id    => l_person_type_id
341     ,p_business_group_id => p_business_group_id
342     ,p_expected_sys_type => 'CWK'
343     );
344   --
345   hr_utility.set_location(l_proc, 30);
346   --
347   -- Initialise local variables as appropriate
348   --
349   l_applicant_number := null;
350   l_employee_number := null;
351   l_npw_number := p_npw_number;
352   l_current_npw_flag := 'Y';
353   l_person_type_id1   := hr_person_type_usage_info.get_default_person_type_id
354                                          (p_business_group_id,
355                                           'OTHER');
356   --
357   -- Process Logic
358   --
359   per_per_ins.ins
360      (p_start_date                   => l_start_date
361      ,p_effective_date               => l_start_date
362      ,p_business_group_id            => p_business_group_id
363      ,p_person_type_id               => l_person_type_id1
364      ,p_last_name                    => p_last_name
365      ,p_background_check_status      => p_background_check_status
366      ,p_background_date_check        => p_background_date_check
367      ,p_blood_type                   => p_blood_type
368      ,p_comments                     => p_comments
369      ,p_correspondence_language      => p_correspondence_language
370      ,p_country_of_birth             => p_country_of_birth
371      ,p_current_npw_flag             => l_current_npw_flag
372      ,p_date_of_birth                => l_date_of_birth
373      ,p_date_of_death                => l_date_of_death
374      ,p_dpdnt_adoption_date          => l_dpdnt_adoption_date
375      ,p_dpdnt_vlntry_svce_flag       => p_dpdnt_vlntry_svce_flag
376      ,p_email_address                => p_email_address
377      ,p_first_name                   => p_first_name
378      ,p_fte_capacity                 => p_fte_capacity
379      ,p_honors                       => p_honors
380      ,p_internal_location            => p_internal_location
381      ,p_known_as                     => p_known_as
382      ,p_last_medical_test_by         => p_last_medical_test_by
383      ,p_last_medical_test_date       => p_last_medical_test_date
384      ,p_mailstop                     => p_mailstop
385      ,p_marital_status               => p_marital_status
386      ,p_middle_names                 => p_middle_names
387      ,p_national_identifier          => p_national_identifier
388      ,p_nationality                  => p_nationality
389      ,p_office_number                => p_office_number
390      ,p_on_military_service          => p_on_military_service
391      ,p_party_id                     => p_party_id
392      ,p_pre_name_adjunct             => p_pre_name_adjunct
393      ,p_previous_last_name           => p_previous_last_name
394      ,p_receipt_of_death_cert_date   => l_receipt_of_death_cert_date
395      ,p_region_of_birth              => p_region_of_birth
396      ,p_registered_disabled_flag     => p_registered_disabled_flag
397      ,p_resume_exists                => p_resume_exists
398      ,p_resume_last_updated          => p_resume_last_updated
399      ,p_second_passport_exists       => p_second_passport_exists
400      ,p_sex                          => p_sex
401      ,p_student_status               => p_student_status
402      ,p_suffix                       => p_suffix
403      ,p_title                        => p_title
404      ,p_town_of_birth                => p_town_of_birth
405      ,p_uses_tobacco_flag            => p_uses_tobacco_flag
406      ,p_work_schedule                => p_work_schedule
407      ,p_expense_check_send_to_addres => p_exp_check_send_to_address
408      ,p_hold_applicant_date_until    => p_hold_applicant_date_until
409      ,p_date_employee_data_verified  => p_date_employee_data_verified
410      ,p_benefit_group_id             => p_benefit_group_id
411      ,p_coord_ben_med_pln_no         => p_coord_ben_med_pln_no
412      ,p_coord_ben_no_cvg_flag        => p_coord_ben_no_cvg_flag
413      ,p_original_date_of_hire        => p_original_date_of_hire
414      ,p_attribute_category           => p_attribute_category
415      ,p_attribute1                   => p_attribute1
416      ,p_attribute2                   => p_attribute2
417      ,p_attribute3                   => p_attribute3
418      ,p_attribute4                   => p_attribute4
419      ,p_attribute5                   => p_attribute5
420      ,p_attribute6                   => p_attribute6
421      ,p_attribute7                   => p_attribute7
422      ,p_attribute8                   => p_attribute8
423      ,p_attribute9                   => p_attribute9
424      ,p_attribute10                  => p_attribute10
425      ,p_attribute11                  => p_attribute11
426      ,p_attribute12                  => p_attribute12
427      ,p_attribute13                  => p_attribute13
428      ,p_attribute14                  => p_attribute14
429      ,p_attribute15                  => p_attribute15
430      ,p_attribute16                  => p_attribute16
431      ,p_attribute17                  => p_attribute17
432      ,p_attribute18                  => p_attribute18
433      ,p_attribute19                  => p_attribute19
434      ,p_attribute20                  => p_attribute20
435      ,p_attribute21                  => p_attribute21
436      ,p_attribute22                  => p_attribute22
437      ,p_attribute23                  => p_attribute23
438      ,p_attribute24                  => p_attribute24
439      ,p_attribute25                  => p_attribute25
440      ,p_attribute26                  => p_attribute26
441      ,p_attribute27                  => p_attribute27
442      ,p_attribute28                  => p_attribute28
443      ,p_attribute29                  => p_attribute29
444      ,p_attribute30                  => p_attribute30
445      ,p_per_information_category     => p_per_information_category
446      ,p_per_information1             => p_per_information1
447      ,p_per_information2             => p_per_information2
448      ,p_per_information3             => p_per_information3
449      ,p_per_information4             => p_per_information4
450      ,p_per_information5             => p_per_information5
451      ,p_per_information6             => p_per_information6
452      ,p_per_information7             => p_per_information7
453      ,p_per_information8             => p_per_information8
454      ,p_per_information9             => p_per_information9
455      ,p_per_information10            => p_per_information10
456      ,p_per_information11            => p_per_information11
457      ,p_per_information12            => p_per_information12
458      ,p_per_information13            => p_per_information13
459      ,p_per_information14            => p_per_information14
460      ,p_per_information15            => p_per_information15
461      ,p_per_information16            => p_per_information16
462      ,p_per_information17            => p_per_information17
463      ,p_per_information18            => p_per_information18
464      ,p_per_information19            => p_per_information19
465      ,p_per_information20            => p_per_information20
466      ,p_per_information21            => p_per_information21
467      ,p_per_information22            => p_per_information22
468      ,p_per_information23            => p_per_information23
469      ,p_per_information24            => p_per_information24
470      ,p_per_information25            => p_per_information25
471      ,p_per_information26            => p_per_information26
472      ,p_per_information27            => p_per_information27
473      ,p_per_information28            => p_per_information28
474      ,p_per_information29            => p_per_information29
475      ,p_per_information30            => p_per_information30
476      --
477      ,p_applicant_number             => l_applicant_number
478      ,p_employee_number              => l_employee_number
479      ,p_npw_number                   => p_npw_number
480      ,p_person_id                    => l_person_id
481      ,p_object_version_number        => l_per_object_version_number
482      ,p_effective_start_date         => l_per_effective_start_date
483      ,p_effective_end_date           => l_per_effective_end_date
484      ,p_full_name                    => l_full_name
485      ,p_comment_id                   => l_comment_id
486      ,p_current_applicant_flag       => l_current_applicant_flag
487      ,p_current_employee_flag        => l_current_employee_flag
488      ,p_current_emp_or_apl_flag      => l_current_emp_or_apl_flag
489      ,p_name_combination_warning     => l_name_combination_warning
490      ,p_dob_null_warning             => l_dob_null_warning
491      ,p_orig_hire_warning            => l_orig_hire_warning
492       );
493   --
494   hr_utility.set_location(l_proc, 40);
495 
496   --
497   -- Add this person to the relevant security definitions.
498   --
499   hr_security_internal.populate_new_person
500     (p_person_id         => l_person_id
501     ,p_business_group_id => p_business_group_id);
502 
503   hr_utility.set_location(l_proc, 45);
504 
505   --
506   -- Create the period of placement record
507   --
508   per_pdp_ins.ins
509     (p_date_start                   => l_start_date
510     ,p_effective_date               => l_start_date
511     ,p_business_group_id            => p_business_group_id
512     ,p_person_id                    => l_person_id
513     ,p_projected_termination_date   => null
514     ,p_validate_df_flex             => false
515     --
516     ,p_object_version_number        => l_pdp_object_version_number
517      );
518 
519   hr_utility.set_location(l_proc, 60);
520 
521   --
522   -- Maintain Person Type Usages
523   --
524   hr_per_type_usage_internal.maintain_person_type_usage
525    (p_effective_date       => l_start_date
526    ,p_person_id            => l_person_id
527    ,p_person_type_id       => l_person_type_id
528    );
529   --
530   --
531   hr_utility.set_location(l_proc, 70);
532   --
533   -- Create the default non-payrolled assignment
534   --
535   hr_assignment_internal.create_default_cwk_asg
536     (p_effective_date                  => l_start_date
537     ,p_business_group_id               => p_business_group_id
538     ,p_person_id                       => l_person_id
539     ,p_placement_date_start            => l_start_date
540     --
541     ,p_assignment_id                   => l_assignment_id
542     ,p_object_version_number           => l_asg_object_version_number
543     ,p_assignment_sequence             => l_assignment_sequence
544     ,p_assignment_number               => l_assignment_number
545     );
546   --
547   hr_utility.set_location(l_proc, 80);
548   --
549   -- Create a phone row using the newly created person as the parent row.
550   -- This phone row replaces the work_telephone column on the person.
551   --
552   if p_work_telephone is not null then
553      hr_phone_api.create_phone
554        (p_date_from                 => l_start_date
555        ,p_date_to                   => null
556        ,p_phone_type                => 'W1'
557        ,p_phone_number              => p_work_telephone
558        ,p_parent_id                 => l_person_id
559        ,p_parent_table              => 'PER_ALL_PEOPLE_F'
560        ,p_validate                  => FALSE
561        ,p_effective_date            => l_start_date
562        ,p_object_version_number     => l_phn_object_version_number  --out
563        ,p_phone_id                  => l_phone_id                   --out
564        );
565   end if;
566   --
567   -- Call After Process User Hook
568   --
569   begin
570     hr_contingent_worker_bk1.create_cwk_a
571       (p_start_date                    => l_start_date
572       ,p_business_group_id             => p_business_group_id
573       ,p_last_name                     => p_last_name
574       ,p_person_type_id                => p_person_type_id
575       ,p_npw_number                    => p_npw_number
576       ,p_background_check_status       => p_background_check_status
577       ,p_background_date_check         => p_background_date_check
578       ,p_blood_type                    => p_blood_type
579       ,p_comments                      => p_comments
580       ,p_correspondence_language       => p_correspondence_language
581       ,p_country_of_birth              => p_country_of_birth
582       ,p_date_of_birth                 => l_date_of_birth
583       ,p_date_of_death                 => l_date_of_death
584       ,p_dpdnt_adoption_date           => l_dpdnt_adoption_date
585       ,p_dpdnt_vlntry_svce_flag        => p_dpdnt_vlntry_svce_flag
586       ,p_email_address                 => p_email_address
587       ,p_first_name                    => p_first_name
588       ,p_fte_capacity                  => p_fte_capacity
589       ,p_honors                        => p_honors
590       ,p_internal_location             => p_internal_location
591       ,p_known_as                      => p_known_as
592       ,p_last_medical_test_by          => p_last_medical_test_by
593       ,p_last_medical_test_date        => p_last_medical_test_date
594       ,p_mailstop                      => p_mailstop
595       ,p_marital_status                => p_marital_status
596       ,p_middle_names                  => p_middle_names
597       ,p_national_identifier           => p_national_identifier
598       ,p_nationality                   => p_nationality
599       ,p_office_number                 => p_office_number
600       ,p_on_military_service           => p_on_military_service
601       ,p_party_id                      => p_party_id
602       ,p_pre_name_adjunct              => p_pre_name_adjunct
603       ,p_previous_last_name            => p_previous_last_name
604       ,p_projected_placement_end       => null
605       ,p_receipt_of_death_cert_date    => l_receipt_of_death_cert_date
606       ,p_region_of_birth               => p_region_of_birth
607       ,p_registered_disabled_flag      => p_registered_disabled_flag
608       ,p_resume_exists                 => p_resume_exists
609       ,p_resume_last_updated           => p_resume_last_updated
610       ,p_second_passport_exists        => p_second_passport_exists
611       ,p_sex                           => p_sex
612       ,p_student_status                => p_student_status
613       ,p_suffix                        => p_suffix
614       ,p_title                         => p_title
615       ,p_town_of_birth                 => p_town_of_birth
616       ,p_uses_tobacco_flag             => p_uses_tobacco_flag
617       ,p_vendor_id                     => p_vendor_id
618       ,p_work_schedule                 => p_work_schedule
619       ,p_work_telephone                => p_work_telephone
620       ,p_exp_check_send_to_address     => p_exp_check_send_to_address
621       ,p_hold_applicant_date_until     => p_hold_applicant_date_until
622       ,p_date_employee_data_verified   => p_date_employee_data_verified
623       ,p_benefit_group_id              => p_benefit_group_id
624       ,p_coord_ben_med_pln_no          => p_coord_ben_med_pln_no
625       ,p_coord_ben_no_cvg_flag         => p_coord_ben_no_cvg_flag
626       ,p_original_date_of_hire         => p_original_date_of_hire
627       ,p_attribute_category            => p_attribute_category
628       ,p_attribute1                    => p_attribute1
629       ,p_attribute2                    => p_attribute2
630       ,p_attribute3                    => p_attribute3
631       ,p_attribute4                    => p_attribute4
632       ,p_attribute5                    => p_attribute5
633       ,p_attribute6                    => p_attribute6
634       ,p_attribute7                    => p_attribute7
635       ,p_attribute8                    => p_attribute8
636       ,p_attribute9                    => p_attribute9
637       ,p_attribute10                   => p_attribute10
638       ,p_attribute11                   => p_attribute11
639       ,p_attribute12                   => p_attribute12
640       ,p_attribute13                   => p_attribute13
641       ,p_attribute14                   => p_attribute14
642       ,p_attribute15                   => p_attribute15
643       ,p_attribute16                   => p_attribute16
644       ,p_attribute17                   => p_attribute17
645       ,p_attribute18                   => p_attribute18
646       ,p_attribute19                   => p_attribute19
647       ,p_attribute20                   => p_attribute20
648       ,p_attribute21                   => p_attribute21
649       ,p_attribute22                   => p_attribute22
650       ,p_attribute23                   => p_attribute23
651       ,p_attribute24                   => p_attribute24
652       ,p_attribute25                   => p_attribute25
653       ,p_attribute26                   => p_attribute26
654       ,p_attribute27                   => p_attribute27
655       ,p_attribute28                   => p_attribute28
656       ,p_attribute29                   => p_attribute29
657       ,p_attribute30                   => p_attribute30
658       ,p_per_information_category      => p_per_information_category
659       ,p_per_information1              => p_per_information1
660       ,p_per_information2              => p_per_information2
661       ,p_per_information3              => p_per_information3
662       ,p_per_information4              => p_per_information4
663       ,p_per_information5              => p_per_information5
664       ,p_per_information6              => p_per_information6
665       ,p_per_information7              => p_per_information7
666       ,p_per_information8              => p_per_information8
667       ,p_per_information9              => p_per_information9
668       ,p_per_information10             => p_per_information10
669       ,p_per_information11             => p_per_information11
670       ,p_per_information12             => p_per_information12
671       ,p_per_information13             => p_per_information13
672       ,p_per_information14             => p_per_information14
673       ,p_per_information15             => p_per_information15
674       ,p_per_information16             => p_per_information16
675       ,p_per_information17             => p_per_information17
676       ,p_per_information18             => p_per_information18
677       ,p_per_information19             => p_per_information19
678       ,p_per_information20             => p_per_information20
679       ,p_per_information21             => p_per_information21
680       ,p_per_information22             => p_per_information22
681       ,p_per_information23             => p_per_information23
682       ,p_per_information24             => p_per_information24
683       ,p_per_information25             => p_per_information25
684       ,p_per_information26             => p_per_information26
685       ,p_per_information27             => p_per_information27
686       ,p_per_information28             => p_per_information28
687       ,p_per_information29             => p_per_information29
688       ,p_per_information30             => p_per_information30
689       ,p_person_id                     => l_person_id
690       ,p_per_object_version_number     => l_per_object_version_number
691       ,p_per_effective_start_date      => l_per_effective_start_date
692       ,p_per_effective_end_date        => l_per_effective_end_date
693       ,p_pdp_object_version_number     => l_pdp_object_version_number
694       ,p_full_name                     => l_full_name
695       ,p_comment_id                    => l_comment_id
696       ,p_assignment_id                 => l_assignment_id
697       ,p_asg_object_version_number     => l_asg_object_version_number
698       ,p_assignment_sequence           => l_assignment_sequence
699       ,p_assignment_number             => l_assignment_number
700       ,p_name_combination_warning      => l_name_combination_warning
701       );
702   exception
703     when hr_api.cannot_find_prog_unit then
704       hr_api.cannot_find_prog_unit_error
705         (p_module_name => 'create_cwk'
706         ,p_hook_type   => 'AP'
707         );
708   end;
709   --
710   -- When in validation only mode raise the Validate_Enabled exception
711   --
712   if p_validate then
713     raise hr_api.validate_enabled;
714   end if;
715   --
716   -- Set all output arguments
717   --
718 
719   --
720   -- Start of fix for bug 3684087 by risgupta on 12092005 as done in bug# 3062967
721   --
722   begin
723     SELECT object_version_number
724       INTO p_per_object_Version_number
725       FROM per_all_people_f
726      WHERE person_id = l_person_id
727        And effective_start_Date = l_per_effective_start_date
728        and effective_end_Date = l_per_effective_end_date;
729   exception
730     when no_data_found then
731       p_per_object_Version_number := l_per_object_version_number;
732   end;
733   --
734   -- END of fix for bug 3684087
735   --
736 
737   p_person_id                 := l_person_id;
738   p_assignment_sequence       := l_assignment_sequence;
739   p_assignment_number         := l_assignment_number;
740   p_assignment_id             := l_assignment_id;
741   -- commented for bug# 3684087
742   --p_per_object_version_number := l_per_object_version_number;
743   p_asg_object_version_number := l_asg_object_version_number;
744   p_pdp_object_version_number := l_pdp_object_version_number;
745   p_per_effective_start_date  := l_per_effective_start_date;
746   p_per_effective_end_date    := l_per_effective_end_date;
747   p_full_name                 := l_full_name;
748   p_comment_id                := l_comment_id;
749   p_name_combination_warning  := l_name_combination_warning;
750   --
751   hr_utility.set_location(' Leaving:'||l_proc, 70);
752 exception
753   when hr_api.validate_enabled then
754     --
755     -- As the Validate_Enabled exception has been raised
756     -- we must rollback to the savepoint
757     --
758     rollback to create_cwk;
759     --
760     -- Only set output warning arguments
761     -- (Any key or derived arguments must be set to null
762     -- when validation only mode is being used.)
763     --
764   p_person_id                 := null;
765   p_npw_number                 := l_npw_number;
766   p_assignment_sequence       := null;
767   p_assignment_number         := null;
768   p_assignment_id             := null;
769   p_per_object_version_number := null;
770   p_asg_object_version_number := null;
771   p_pdp_object_version_number := null;
772   p_per_effective_start_date  := null;
773   p_per_effective_end_date    := null;
774   p_full_name                 := null;
775   p_comment_id                := null;
776   p_name_combination_warning  := l_name_combination_warning;
777     hr_utility.set_location(' Leaving:'||l_proc, 80);
778   when others then
779     --
780     -- A validation or unexpected error has occured
781     --
782     rollback to create_cwk;
783     --
784     -- set in out parameters and set out parameters
785     --
786   p_person_id                 := null;
787   p_npw_number                 := l_npw_number;
788   p_assignment_sequence       := null;
789   p_assignment_number         := null;
790   p_assignment_id             := null;
791   p_per_object_version_number := null;
792   p_asg_object_version_number := null;
793   p_pdp_object_version_number := null;
794   p_per_effective_start_date  := null;
795   p_per_effective_end_date    := null;
796   p_full_name                 := null;
797   p_comment_id                := null;
798   p_name_combination_warning  := l_name_combination_warning;
799     hr_utility.set_location(' Leaving:'||l_proc, 90);
800     raise;
801 end create_cwk;
802 --
803 -- ----------------------------------------------------------------------------
804 -- |------------------------------< convert_to_cwk >--------------------------|
805 -- ----------------------------------------------------------------------------
806 --
807 procedure convert_to_cwk
808   (p_validate                      in     boolean default false
809   ,p_effective_date                in     date
810   ,p_person_id                     in     number
811   ,p_object_version_number         in out nocopy number
812   ,p_npw_number                    in out nocopy varchar2
813   ,p_projected_placement_end       in     date     default null
814   ,p_person_type_id                in     number  default null
815   ,p_datetrack_update_mode         in     varchar2
816   ,p_per_effective_start_date         out nocopy date
817   ,p_per_effective_end_date           out nocopy date
818   ,p_pdp_object_version_number        out nocopy   number
819   ,p_assignment_id                    out nocopy number
820   ,p_asg_object_version_number        out nocopy number
821   ,p_assignment_sequence              out nocopy number
822   ) is
823   --
824   -- Declare cursors and local variables
825   --
826   l_proc                varchar2(72) := g_package||'convert_to_cwk';
827   l_object_version_number per_people_f.object_version_number%type;
828   l_npw_number            per_people_f.npw_number%type;
829   l_person_type_id        number := p_person_type_id;
830   l_person_type_id1       number;
831   l_effective_date        date;
832   l_datetrack_update_mode      varchar2(30);
833   l_ptu_update_mode            varchar2(30);
834   l_per_effective_start_date   date;
835   l_per_effective_end_date     date;
836   l_comment_id                 number;
837   l_current_applicant_flag     varchar2(1);
838   l_current_emp_or_apl_flag    varchar2(1);
839   l_current_employee_flag      varchar2(1);
840   l_full_name                  per_all_people_f.full_name%TYPE;
841   l_name_combination_warning   boolean;
842   l_dob_null_warning           boolean;
843   l_orig_hire_warning          boolean;
844   l_pdp_object_version_number  number;
845   l_assignment_id              number;
846   l_asg_object_version_number  number;
847   l_assignment_sequence        number;
848   l_assignment_number          per_assignments_f.assignment_number%type;
849   --
850   cursor csr_per_details
851     (p_person_id number
852     ,p_effective_date date
853     )
854   IS
855     SELECT ppf.business_group_id
856           ,ppf.person_type_id
857           ,ppt.system_person_type
858           ,ppf.npw_number
859           ,ppf.applicant_number
860           ,ppf.employee_number
861      FROM  per_all_people_f ppf
862           ,per_person_types ppt
863     WHERE ppt.person_type_id = ppf.person_type_id
864       AND ppf.person_id = p_person_id
865       AND p_effective_date between ppf.effective_start_date and ppf.effective_end_date;
866   --
867   l_per_details_rec   csr_per_details%rowtype;
868   --
869 begin
870   hr_utility.set_location('Entering:'|| l_proc, 10);
871 
872   --
873   -- Ensure mandatory arguments have been passed
874   --
875   hr_api.mandatory_arg_error
876     (p_api_name                     => l_proc
877     ,p_argument                     => 'person_id'
878     ,p_argument_value               => p_person_id
879     );
880   --
881   hr_api.mandatory_arg_error
882     (p_api_name                     => l_proc
883     ,p_argument                     => 'effective_date'
884     ,p_argument_value               => p_effective_date
885     );
886   --
887   -- Issue a savepoint
888   --
889   savepoint convert_to_cwk;
890   --
891   -- Truncate the time portion from all IN date parameters
892   --
893   l_effective_date := trunc(p_effective_date);
894   --
895   l_object_version_number := p_object_version_number;
896   l_npw_number       := p_npw_number;
897   --
898   -- Call Before Process User Hook
899   --
900   begin
901     hr_contingent_worker_bk2.convert_to_cwk_b
902       (p_effective_date                => l_effective_date
903       ,p_person_id                     => p_person_id
904       ,p_object_version_number         => p_object_version_number
905       ,p_npw_number                    => p_npw_number
906       ,p_projected_placement_end       => null
907       ,p_person_type_id                => p_person_type_id
908       ,p_datetrack_update_mode         => p_datetrack_update_mode
909       );
910   exception
911     when hr_api.cannot_find_prog_unit then
912       hr_api.cannot_find_prog_unit_error
913         (p_module_name => 'convert_to_cwk'
914         ,p_hook_type   => 'BP'
915         );
916   end;
917   hr_utility.set_location(l_proc, 20);
918   --
919   -- Validation in addition to Row Handlers
920   --
921   -- Get derived details for person on effective date
922   --
923   OPEN csr_per_details
924     (p_person_id                    => p_person_id
925     ,p_effective_date               => p_effective_date
926     );
927   FETCH csr_per_details INTO l_per_details_rec;
928   IF csr_per_details%NOTFOUND
929   THEN
930     CLOSE csr_per_details;
931     fnd_message.set_name('PER','PER_289602_CWK_INV_PERSON_ID');
932     fnd_message.raise_error;
933   END IF;
934   CLOSE csr_per_details;
935   --
936   -- If the specified person type id is not null then check that it
937   -- corresponds to type 'CWK', is currently active and is in the correct
938   -- business group, otherwise set person type to the active default for CWK
939   -- in the current business group.
940   --
941   per_per_bus.chk_person_type
942     (p_person_type_id    => l_person_type_id
943     ,p_business_group_id => l_per_details_rec.business_group_id
944     ,p_expected_sys_type => 'CWK'
945     );
946   hr_utility.set_location(l_proc, 30);
947   --
948   -- Check for future person_type_changes
949   --
950   if hr_person.chk_future_person_type(l_per_details_rec.system_person_type
951                                      ,p_person_id
952                                      ,l_per_details_rec.business_group_id
953                                      ,p_effective_date) then
954     fnd_message.set_name('PAY','HR_7193_PER_FUT_TYPE_EXISTS');
955     fnd_message.raise_error;
956   end if;
957   --
958   -- Check against the existing person type records.
959   --
960   if hr_general2.is_person_type(p_person_id
961                                ,'EMP'
962                                ,l_effective_date)
963    or hr_general2.is_person_type(p_person_id
964                                ,'CWK'
965                                ,l_effective_date) then
966     fnd_message.set_name('PER','PER_289603_CWK_INV_PERSON_TYPE');
967     fnd_message.raise_error;
968   elsif l_per_details_rec.system_person_type = 'OTHER' then
969       l_person_type_id1 :=  hr_person_type_usage_info.get_default_person_type_id
970                                          (l_per_details_rec.business_group_id,
971                                           'OTHER');
972   else
973       l_person_type_id1 := l_per_details_rec.person_type_id;
974     hr_utility.set_location(l_proc,50);
975   end if;
976   --
977   -- Ensure the npw_number will not be changed if it exists
978   --
979   IF l_per_details_rec.npw_number IS NOT NULL
980     AND NVL(p_npw_number,hr_api.g_number) <> l_per_details_rec.npw_number
981   THEN
982     hr_utility.set_location(l_proc,60);
983      p_npw_number := l_per_details_rec.npw_number;
984   END IF;
985   --
986   -- Check: is this back-to-back contract?
987   --
988   if hr_general2.is_person_type(p_person_id
989                                ,'EX_CWK'
990                                ,l_effective_date)
991     and hr_general2.is_person_type(p_person_id
992                                   ,'CWK'
993                                   ,l_effective_date-1) then
994      l_datetrack_update_mode := 'CORRECTION';
995      l_ptu_update_mode := 'CORRECTION';
996    hr_utility.set_location(l_proc,70);
997   elsif hr_general2.is_person_type(p_person_id
998                                   ,'EX_EMP'
999                                   ,l_effective_date)
1000      and hr_general2.is_person_type(p_person_id
1001                                    ,'EMP'
1002                                    ,l_effective_date-1) then
1003      l_datetrack_update_mode := 'CORRECTION';
1004      l_ptu_update_mode := 'UPDATE';
1005    hr_utility.set_location(l_proc,80);
1006   else
1007      --
1008      -- Support the use of CORRECTION for the person table handler routines.
1009      --
1010      if p_datetrack_update_mode = 'CORRECTION' then
1011        l_datetrack_update_mode := p_datetrack_update_mode;
1012      else
1013        l_datetrack_update_mode := 'UPDATE';
1014      end if;
1015      l_ptu_update_mode := 'UPDATE';
1016   end if;
1017   --
1018   -- Process Logic
1019   --
1020   per_per_upd.upd
1021   (p_person_id                    => p_person_id
1022   ,p_effective_date               => l_effective_date
1023   ,p_datetrack_mode               => l_datetrack_update_mode
1024   ,p_person_type_id               => l_person_type_id1
1025   ,p_applicant_number             => l_per_details_rec.applicant_number
1026   ,p_employee_number              => l_per_details_rec.employee_number
1027   ,p_npw_number                   => p_npw_number
1028   ,p_current_npw_flag             => 'Y'
1029   ,p_object_version_number        => p_object_version_number
1030 
1031   ,p_effective_start_date         => l_per_effective_start_date
1032   ,p_effective_end_date           => l_per_effective_end_date
1033   ,p_comment_id                   => l_comment_id
1034   ,p_current_applicant_flag       => l_current_applicant_flag
1035   ,p_current_emp_or_apl_flag      => l_current_emp_or_apl_flag
1036   ,p_current_employee_flag        => l_current_employee_flag
1037   ,p_full_name                    => l_full_name
1038   ,p_name_combination_warning     => l_name_combination_warning
1039   ,p_dob_null_warning             => l_dob_null_warning
1040   ,p_orig_hire_warning            => l_orig_hire_warning
1041   );
1042 
1043   hr_utility.set_location(l_proc, 90);
1044 
1045   hr_security_internal.populate_new_person
1046      (p_business_group_id            => l_per_details_rec.business_group_id
1047      ,p_person_id                    => p_person_id);
1048 
1049   hr_utility.set_location(l_proc, 93);
1050 
1051   --
1052   -- Maintain PTU
1053   --
1054   hr_per_type_usage_internal.maintain_person_type_usage
1055    (p_effective_date        => l_effective_date
1056    ,p_person_id             => p_person_id
1057    ,p_person_type_id        => l_person_type_id
1058    ,p_datetrack_update_mode => l_ptu_update_mode
1059    );
1060 
1061   hr_utility.set_location(l_proc, 95);
1062 
1063   --
1064   -- Create the period of placement record
1065   --
1066   per_pdp_ins.ins
1067     (p_date_start                   => l_effective_date
1068     ,p_effective_date               => l_effective_date
1069     ,p_business_group_id            => l_per_details_rec.business_group_id
1070     ,p_person_id                    => p_person_id
1071     ,p_projected_termination_date   => null
1072     ,p_validate_df_flex             => false
1073     --
1074     ,p_object_version_number        => l_pdp_object_version_number
1075      );
1076   --
1077   hr_utility.set_location(l_proc, 100);
1078   --
1079   -- Create the default non-payrolled assignment
1080   --
1081   hr_assignment_internal.create_default_cwk_asg
1082     (p_effective_date                  => l_effective_date
1083     ,p_business_group_id               => l_per_details_rec.business_group_id
1084     ,p_person_id                       => p_person_id
1085     ,p_placement_date_start            => l_effective_date
1086     --
1087     ,p_assignment_id                   => l_assignment_id
1088     ,p_object_version_number           => l_asg_object_version_number
1089     ,p_assignment_sequence             => l_assignment_sequence
1090     ,p_assignment_number               => l_assignment_number
1091     );
1092   --
1093   hr_utility.set_location(l_proc, 110);
1094   --
1095   -- Call After Process User Hook
1096   --
1097   begin
1098     hr_contingent_worker_bk2.convert_to_cwk_a
1099       (p_effective_date                => l_effective_date
1100       ,p_person_id                     => p_person_id
1101       ,p_object_version_number         => p_object_version_number
1102       ,p_npw_number                    => p_npw_number
1103       ,p_projected_placement_end       => null
1104       ,p_person_type_id                => l_person_type_id
1105       ,p_datetrack_update_mode         => p_datetrack_update_mode
1106       ,p_per_effective_start_date      => l_per_effective_start_date
1107       ,p_per_effective_end_date        => l_per_effective_end_date
1108       ,p_pdp_object_version_number     => l_pdp_object_version_number
1109       ,p_assignment_id                 => l_assignment_id
1110       ,p_asg_object_version_number     => l_asg_object_version_number
1111       ,p_assignment_sequence           => l_assignment_sequence
1112       );
1113   exception
1114     when hr_api.cannot_find_prog_unit then
1115       hr_api.cannot_find_prog_unit_error
1116         (p_module_name => 'convert_to_cwk'
1117         ,p_hook_type   => 'AP'
1118         );
1119   end;
1120   --
1121   -- When in validation only mode raise the Validate_Enabled exception
1122   --
1123   if p_validate then
1124     raise hr_api.validate_enabled;
1125   end if;
1126   --
1127   -- Set all output arguments
1128   --
1129   p_per_effective_start_date  := l_per_effective_start_date;
1130   p_per_effective_end_date    := l_per_effective_end_date;
1131   p_pdp_object_version_number := l_pdp_object_version_number;
1132   p_assignment_id             := l_assignment_id;
1133   p_asg_object_version_number := l_asg_object_version_number;
1134   p_assignment_sequence       := l_assignment_sequence;
1135   --
1136   hr_utility.set_location(' Leaving:'||l_proc, 170);
1137 exception
1138   when hr_api.validate_enabled then
1139     --
1140     -- As the Validate_Enabled exception has been raised
1141     -- we must rollback to the savepoint
1142     --
1143     rollback to convert_to_cwk;
1144     --
1145     -- Only set output warning arguments
1146     -- (Any key or derived arguments must be set to null
1147     -- when validation only mode is being used.)
1148     --
1149   p_object_version_number     := l_object_version_number;
1150   p_npw_number                := l_npw_number;
1151   p_pdp_object_version_number := null;
1152   p_per_effective_start_date  := null;
1153   p_per_effective_end_date    := null;
1154   p_assignment_id             := null;
1155   p_asg_object_version_number := null;
1156   p_assignment_sequence       := null;
1157   hr_utility.set_location(' Leaving:'||l_proc, 180);
1158   when others then
1159     --
1160     -- A validation or unexpected error has occured
1161     --
1162     rollback to convert_to_cwk;
1163    --
1164     -- set in out parameters and set out parameters
1165     --
1166 p_object_version_number     := l_object_version_number;
1167   p_npw_number                := l_npw_number;
1168   p_pdp_object_version_number := null;
1169   p_per_effective_start_date  := null;
1170   p_per_effective_end_date    := null;
1171   p_assignment_id             := null;
1172   p_asg_object_version_number := null;
1173   p_assignment_sequence       := null;
1174    --
1175     hr_utility.set_location(' Leaving:'||l_proc, 190);
1176     raise;
1177 end convert_to_cwk;
1178 --
1179 -- ----------------------------------------------------------------------------
1180 -- |------------------------------< apply_for_job >---------------------------|
1181 -- ----------------------------------------------------------------------------
1182 --
1183 procedure apply_for_job
1184   (p_validate                      in     boolean default false
1185   ,p_effective_date                in     date
1186   ,p_person_id                     in     number
1187   ,p_object_version_number         in out nocopy number
1188   ,p_applicant_number              in out nocopy varchar2
1189   ,p_person_type_id                in     number  default null
1190   ,p_vacancy_id                    in     number  default null
1191   ,p_per_effective_start_date         out nocopy date
1192   ,p_per_effective_end_date           out nocopy date
1193   ,p_application_id                   out nocopy number
1194   ,p_apl_object_version_number        out nocopy number
1195   ,p_assignment_id                    out nocopy number
1196   ,p_asg_object_version_number        out nocopy number
1197   ,p_assignment_sequence              out nocopy number
1198   ) is
1199   --
1200   -- Declare cursors and local variables
1201   --
1202   l_proc                varchar2(72) := g_package||'apply_for_job';
1203   l_effective_date             date;
1204   l_person_type_id             number := p_person_type_id;
1205   l_person_type_id1            number;
1206   l_comment_id                 number;
1207   l_current_applicant_flag     varchar2(1);
1208   l_current_emp_or_apl_flag    varchar2(1);
1209   l_current_employee_flag      varchar2(1);
1210   l_full_name                  per_all_people_f.full_name%TYPE;
1211   l_name_combination_warning   boolean;
1212   l_dob_null_warning           boolean;
1213   l_orig_hire_warning          boolean;
1214   l_per_effective_start_date   date;
1215   l_per_effective_end_date     date;
1216   l_application_id            number;
1217   l_apl_object_version_number number;
1218   l_assignment_id             number;
1219   l_asg_object_version_number number;
1220   l_assignment_sequence       number;
1221   l_object_version_number     number;
1222   l_applicant_number          per_people_f.applicant_number%type;
1223   l_dummy                     varchar2(1);
1224   --
1225   cursor csr_per_details
1226     (p_person_id number
1227     ,p_effective_date date
1228     )
1229   IS
1230     SELECT ppf.business_group_id
1231           ,ppf.person_type_id
1232           ,ppt.system_person_type
1233           ,ppf.npw_number
1234           ,ppf.applicant_number
1235           ,ppf.employee_number
1236      FROM  per_all_people_f ppf
1237           ,per_person_types ppt
1238     WHERE ppt.person_type_id = ppf.person_type_id
1239       AND ppf.person_id = p_person_id
1240       AND p_effective_date between ppf.effective_start_date and ppf.effective_end_date;
1241   --
1242   l_per_details_rec   csr_per_details%rowtype;
1243   --
1244   cursor csr_fut_apl
1245     (p_person_id number
1246     ,p_effective_date date
1247     )
1248   IS
1249    select 'Y'
1250    from dual
1251    where exists (select 'Y'
1252                  from per_person_type_usages_f ptu
1253                      ,per_person_types ppt
1254                  where ptu.person_id = p_person_id
1255                      and ppt.person_type_id = ptu.person_type_id
1256                      and ptu.effective_start_date >= p_effective_date
1257                      and ppt.system_person_type = 'APL');
1258   --
1259 begin
1260   hr_utility.set_location('Entering:'|| l_proc, 10);
1261   --
1262   -- Ensure mandatory arguments have been passed
1263   --
1264   hr_api.mandatory_arg_error
1265     (p_api_name                     => l_proc
1266     ,p_argument                     => 'person_id'
1267     ,p_argument_value               => p_person_id
1268     );
1269   --
1270   hr_api.mandatory_arg_error
1271     (p_api_name                     => l_proc
1272     ,p_argument                     => 'effective_date'
1273     ,p_argument_value               => p_effective_date
1274     );
1275   --
1276   -- Issue a savepoint
1277   --
1278   savepoint apply_for_job;
1279   --
1280   -- Truncate the time portion from all IN date parameters
1281   --
1282   l_effective_date := trunc(p_effective_date);
1283   --
1284   l_object_version_number := p_object_version_number;
1285   l_applicant_number       := p_applicant_number;
1286   --
1287   -- Call Before Process User Hook
1288   --
1289   begin
1290     hr_contingent_worker_bk3.apply_for_job_b
1291       (p_effective_date                => l_effective_date
1292       ,p_person_id                     => p_person_id
1293       ,p_object_version_number         => p_object_version_number
1294       ,p_applicant_number              => p_applicant_number
1295       ,p_person_type_id                => p_person_type_id
1296       ,p_vacancy_id                    => p_vacancy_id
1297       );
1298   exception
1299     when hr_api.cannot_find_prog_unit then
1300       hr_api.cannot_find_prog_unit_error
1301         (p_module_name => 'apply_for_job'
1302         ,p_hook_type   => 'BP'
1303         );
1304   end;
1305   hr_utility.set_location(l_proc, 20);
1306   --
1307   -- Validation in addition to Row Handlers
1308   --
1309   --
1310   -- Get derived details for person on effective date
1311   --
1312   OPEN csr_per_details
1313     (p_person_id                    => p_person_id
1314     ,p_effective_date               => p_effective_date
1315     );
1316   FETCH csr_per_details INTO l_per_details_rec;
1317   IF csr_per_details%NOTFOUND
1318   THEN
1319     CLOSE csr_per_details;
1320     fnd_message.set_name('PER','PER_289602_CWK_INV_PERSON_ID');
1321     fnd_message.raise_error;
1322   END IF;
1323   CLOSE csr_per_details;
1324   hr_utility.set_location(l_proc, 25);
1325   --
1326   -- Check for future person_type_changes
1327   --
1328   if hr_person.chk_future_person_type(l_per_details_rec.system_person_type
1329                                      ,p_person_id
1330                                      ,l_per_details_rec.business_group_id
1331                                      ,p_effective_date) then
1332     fnd_message.set_name('PAY','HR_7193_PER_FUT_TYPE_EXISTS');
1333     fnd_message.raise_error;
1334   end if;
1335 
1336   hr_utility.set_location(l_proc, 28);
1337   --
1338   -- Check against the existing person type records.
1339   --
1340   open csr_fut_apl(p_person_id,l_effective_date);
1341   fetch csr_fut_apl into l_dummy;
1342   if csr_fut_apl%found
1343    or not hr_general2.is_person_type(p_person_id
1344                                ,'CWK'
1345                                ,l_effective_date) then
1346     close csr_fut_apl;
1347     fnd_message.set_name('PER','PER_289603_CWK_INV_PERSON_TYPE');
1348     fnd_message.raise_error;
1349   else
1350     close csr_fut_apl;
1351   end if;
1352   hr_utility.set_location(l_proc, 30);
1353   --
1354   -- If the specified person type id is not null then check that it
1355   -- corresponds to type 'APL', is currently active and is in the correct
1356   -- business group, otherwise set person type to the active default for APL
1357   -- in the current business group.
1358   --
1359   per_per_bus.chk_person_type
1360     (p_person_type_id    => l_person_type_id
1361     ,p_business_group_id => l_per_details_rec.business_group_id
1362     ,p_expected_sys_type => 'APL'
1363     );
1364   hr_utility.set_location(l_proc, 40);
1365   --
1366   -- Ensure the applicant number will not be changed if it exists
1367   --
1368   IF l_per_details_rec.applicant_number IS NOT NULL
1369     AND NVL(p_applicant_number,hr_api.g_number) <> l_per_details_rec.applicant_number
1370   THEN
1371     hr_utility.set_location(l_proc,50);
1372      p_applicant_number := l_per_details_rec.applicant_number;
1373   END IF;
1374   --
1375   l_person_type_id1 :=  hr_person_type_usage_info.get_default_person_type_id
1376             (l_per_details_rec.business_group_id,
1377           'APL');
1378   --
1379   -- Process Logic
1380   --
1381   per_per_upd.upd
1382   (p_person_id                    => p_person_id
1383   ,p_effective_date               => l_effective_date
1384   ,p_datetrack_mode               => hr_api.g_update
1385   ,p_person_type_id               => l_person_type_id1
1386   ,p_applicant_number             => p_applicant_number
1387   ,p_employee_number              => l_per_details_rec.employee_number
1388   ,p_npw_number                   => l_per_details_rec.npw_number
1389   ,p_current_npw_flag             => 'Y'
1390   ,p_object_version_number        => p_object_version_number
1391 
1392   ,p_effective_start_date         => l_per_effective_start_date
1393   ,p_effective_end_date           => l_per_effective_end_date
1394   ,p_comment_id                   => l_comment_id
1395   ,p_current_applicant_flag       => l_current_applicant_flag
1396   ,p_current_emp_or_apl_flag      => l_current_emp_or_apl_flag
1397   ,p_current_employee_flag        => l_current_employee_flag
1398   ,p_full_name                    => l_full_name
1399   ,p_name_combination_warning     => l_name_combination_warning
1400   ,p_dob_null_warning             => l_dob_null_warning
1401   ,p_orig_hire_warning            => l_orig_hire_warning
1402   );
1403   hr_utility.set_location(l_proc, 60);
1404   --
1405   hr_security_internal.populate_new_person
1406        (p_business_group_id            => l_per_details_rec.business_group_id
1407        ,p_person_id                    => p_person_id
1408        );
1409   hr_utility.set_location(l_proc, 70);
1410   --
1411   -- Maintain PTU
1412   --
1413   hr_per_type_usage_internal.maintain_person_type_usage
1414   (p_effective_date       => l_effective_date
1415   ,p_person_id            => p_person_id
1416   ,p_person_type_id       => l_person_type_id
1417   );
1418   hr_utility.set_location(l_proc, 80);
1419   --
1420   -- create an application.
1421   --
1422   per_apl_ins.ins
1423     (p_application_id            => l_application_id
1424     ,p_business_group_id         => l_per_details_rec.business_group_id
1425     ,p_person_id                 => p_person_id
1426     ,p_date_received             => l_effective_date
1427     ,p_object_version_number     => l_apl_object_version_number
1428     ,p_effective_date            => l_effective_date
1429     );
1430   hr_utility.set_location(l_proc, 90);
1431   --
1432   hr_assignment_internal.create_default_apl_asg
1433      (p_effective_date               => l_effective_date
1434      ,p_person_id                    => p_person_id
1435      ,p_business_group_id            => l_per_details_rec.business_group_id
1436      ,p_application_id               => l_application_id
1437      ,p_assignment_id                => l_assignment_id
1438      ,p_object_version_number        => l_asg_object_version_number
1439      ,p_assignment_sequence          => l_assignment_sequence
1440      );
1441   hr_utility.set_location(l_proc, 100);
1442   --
1443   -- Call After Process User Hook
1444   --
1445   begin
1446     hr_contingent_worker_bk3.apply_for_job_a
1447       (p_effective_date                => l_effective_date
1448       ,p_person_id                     => p_person_id
1449       ,p_object_version_number         => p_object_version_number
1450       ,p_applicant_number              => p_applicant_number
1451       ,p_person_type_id                => l_person_type_id
1452       ,p_vacancy_id                    => p_vacancy_id
1453       ,p_per_effective_start_date      => l_per_effective_start_date
1454       ,p_per_effective_end_date        => l_per_effective_end_date
1455       ,p_application_id                => l_application_id
1456       ,p_apl_object_version_number     => l_apl_object_version_number
1457       ,p_assignment_id                 => l_assignment_id
1458       ,p_asg_object_version_number     => l_asg_object_version_number
1459       ,p_assignment_sequence           => l_assignment_sequence
1460       );
1461   exception
1462     when hr_api.cannot_find_prog_unit then
1463       hr_api.cannot_find_prog_unit_error
1464         (p_module_name => 'apply_for_job'
1465         ,p_hook_type   => 'AP'
1466         );
1467   end;
1468   --
1469   -- When in validation only mode raise the Validate_Enabled exception
1470   --
1471   if p_validate then
1472     raise hr_api.validate_enabled;
1473   end if;
1474   --
1475   -- Set all output arguments
1476   --
1477   p_per_effective_start_date  := l_per_effective_start_date;
1478   p_per_effective_end_date    := l_per_effective_end_date;
1479   p_application_id            := l_application_id;
1480   p_apl_object_version_number := l_apl_object_version_number;
1481   p_assignment_id             := l_assignment_id;
1482   p_asg_object_version_number := l_asg_object_version_number;
1483   p_assignment_sequence       := l_assignment_sequence;
1484   --
1485   hr_utility.set_location(' Leaving:'||l_proc, 170);
1486 exception
1487   when hr_api.validate_enabled then
1488     --
1489     -- As the Validate_Enabled exception has been raised
1490     -- we must rollback to the savepoint
1491     --
1492     rollback to apply_for_job;
1493     --
1494     -- Only set output warning arguments
1495     -- (Any key or derived arguments must be set to null
1496     -- when validation only mode is being used.)
1497     --
1498     p_object_version_number     := l_object_version_number;
1499     p_applicant_number          := l_applicant_number;
1500     p_per_effective_start_date  := null;
1501     p_per_effective_end_date    := null;
1502     p_application_id            := null;
1503     p_apl_object_version_number := null;
1504     p_assignment_id             := null;
1505     p_asg_object_version_number := null;
1506     p_assignment_sequence       := null;
1507     hr_utility.set_location(' Leaving:'||l_proc, 180);
1508   when others then
1509     --
1510     -- A validation or unexpected error has occured
1511     --
1512     rollback to apply_for_job;
1513     --
1514     --
1515     -- set in out parameters and set out parameters
1516     --
1517     p_object_version_number     := l_object_version_number;
1518     p_applicant_number          := l_applicant_number;
1519     p_per_effective_start_date  := null;
1520     p_per_effective_end_date    := null;
1521     p_application_id            := null;
1522     p_apl_object_version_number := null;
1523     p_assignment_id             := null;
1524     p_asg_object_version_number := null;
1525     p_assignment_sequence       := null;
1526     --
1527     hr_utility.set_location(' Leaving:'||l_proc, 190);
1528     raise;
1529 end apply_for_job;
1530 --
1531 -- ----------------------------------------------------------------------------
1532 -- |---------------------------< pre_term_check >-----------------------------|
1533 -- ----------------------------------------------------------------------------
1534 --
1535 procedure pre_term_check(p_status              IN OUT NOCOPY VARCHAR2
1536                         ,p_business_group_id   IN     NUMBER
1537                         ,p_person_id           IN     NUMBER
1538                         ,p_session_date        IN     DATE
1539          )is
1540 --
1541 --
1542 v_dummy VARCHAR2(1);
1543 l_proc varchar2(45) := g_package||'pre_term_check';
1544 --
1545 begin
1546    if p_status = 'SUPERVISOR' then
1547       begin
1548          hr_utility.set_location(l_proc,10);
1549          Select 'X'
1550          into v_dummy
1551          from   sys.dual
1552          where  exists (select 'Assignments Exist'
1553                         from   per_all_assignments_f paf
1554                         where  paf.supervisor_id     = p_person_id
1555                         and    paf.business_group_id = p_business_group_id
1556                         and    p_session_date between paf.effective_start_date
1557                                    and paf.effective_end_date);
1558          p_status := 'WARNING';
1559          return;
1560       exception
1561         when no_data_found then
1562           return;
1563       end;
1564    elsif p_status = 'EVENT' then
1565       begin
1566          hr_utility.set_location(l_proc,30);
1567          select 'X'
1568          into v_dummy
1569          from   sys.dual
1570          where  exists ( select 'Events exist'
1571                          from   per_events pe
1572                          ,      per_bookings pb
1573                          where  pe.business_group_id = pb.business_group_id
1574                          and    (pb.business_group_id = p_business_group_id OR
1575                       nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
1576                          and    pe.event_id           = pb.event_id
1577                          and    pe.event_or_interview = 'E'
1578                          and    pb.person_id          = p_person_id
1579           and    pe.date_start         > p_session_date
1580                         );
1581          p_status := 'WARNING';
1582          return;
1583       exception
1584         when no_data_found then
1585           return;
1586       end;
1587    end if;
1588    if p_status = 'INTERVIEW' then
1589       begin
1590          hr_utility.set_location(l_proc,40);
1591          select 'X'
1592          into v_dummy
1593          from   sys.dual
1594          where  exists(select 'Interview rows exist'
1595                        from   per_events pe
1596                        where  pe.business_group_id          = p_business_group_id
1597                        and    pe.event_or_interview         = 'I'
1598                        and    pe.internal_contact_person_id = p_person_id
1599              and    pe.date_start                 > p_session_date
1600                       )
1601        OR
1602       exists(select 'Interview rows exist'
1603              from    per_events pe
1604                                ,per_bookings pb
1605                        where  pe.business_group_id = pb.business_group_id
1606                        and    (pb.business_group_id  = p_business_group_id OR
1607                        nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
1608                        and    pe.event_id           = pb.event_id
1609                        and    pe.event_or_interview = 'I'
1610                        and    pb.person_id          = p_person_id
1611              and    pe.date_start         > p_session_date
1612                       );
1613          p_status := 'WARNING';
1614          return;
1615       exception
1616         when no_data_found then
1617           return;
1618       end;
1619    end if;
1620    if p_status = 'REVIEW' then
1621       begin
1622          hr_utility.set_location(l_proc,50);
1623          select 'X'
1624          into v_dummy
1625          from   sys.dual
1626          where  exists ( select 'Perf Review rows exist'
1627                       from   per_performance_reviews ppr
1628             where  ppr.person_id          = p_person_id
1629               and  review_date > p_session_date
1630                     );
1631          p_status := 'WARNING';
1632          return;
1633       exception
1634         when no_data_found then
1635           return;
1636       end;
1637    end if;
1638    if p_status = 'RECRUITER' then
1639       begin
1640          hr_utility.set_location(l_proc,60);
1641          select 'X'
1642          into v_dummy
1643          from   sys.dual
1644          where  exists (select 'Recruiter for vacancy'
1645                         from  per_vacancies pv
1646                         where
1647                           -- Fix for bug 3446782. This condition exists in the view.
1648                           /*(pv.business_group_id = p_business_group_id OR
1649                               nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
1650                              and */
1651                           pv.recruiter_id         = p_person_id
1652              and   nvl(pv.date_to, p_session_date) >= p_session_date);
1653          p_status := 'WARNING';
1654          return;
1655       exception
1656         when no_data_found then
1657           return;
1658       end;
1659    end if;
1660 end pre_term_check;
1661 --
1662 -- ----------------------------------------------------------------------------
1663 -- |-----------------------< actual_termination_placement >-------------------|
1664 -- ----------------------------------------------------------------------------
1665 --
1666 procedure actual_termination_placement
1667   (p_validate                     in     boolean  default false
1668   ,p_effective_date               in     date
1669   ,p_person_id                    in     number
1670   ,p_date_start                   in     date
1671   ,p_object_version_number        in out nocopy number
1672   ,p_actual_termination_date      in     date
1673   ,p_last_standard_process_date   in out nocopy date
1674   ,p_person_type_id               in     number   default hr_api.g_number
1675   ,p_assignment_status_type_id    in     number   default hr_api.g_number
1676   ,p_termination_reason           in     varchar2 default hr_api.g_varchar2
1677   ,p_supervisor_warning              out nocopy boolean
1678   ,p_event_warning                   out nocopy boolean
1679   ,p_interview_warning               out nocopy boolean
1680   ,p_review_warning                  out nocopy boolean
1681   ,p_recruiter_warning               out nocopy boolean
1682   ,p_asg_future_changes_warning      out nocopy boolean
1683   ,p_entries_changed_warning         out nocopy varchar2
1684   ,p_pay_proposal_warning            out nocopy boolean
1685   ,p_dod_warning                     out nocopy boolean
1686   ) is
1687   --
1688   -- Declare cursors and local variables
1689   --
1690   -- Out variables
1691   --
1692   l_asg_future_changes_warning boolean     := FALSE;
1693   l_entries_changed_warning    varchar2(1) := 'N';
1694   l_event_warning              boolean     := FALSE;
1695   l_interview_warning          boolean     := FALSE;
1696   l_last_standard_process_date date;
1697   l_pdp_object_version_number  number;
1698   l_recruiter_warning          boolean     := FALSE;
1699   l_review_warning             boolean     := FALSE;
1700   l_supervisor_warning         boolean     := FALSE;
1701   l_dod_warning                boolean     := FALSE;
1702   --
1703   l_assignment_status_type_id  number;
1704   l_business_group_id          number;
1705   l_comment_id                 number;
1706   l_cr_asg_future_changes_warn boolean     := FALSE;
1707   l_cr_entries_changed_warn    varchar2(1) := 'N';
1708   l_pay_proposal_warn          boolean     := FALSE;
1709   l_current_npw_flag           varchar2(1);
1710   l_dob_null_warning           boolean;
1711   l_effective_date             date;
1712   l_effective_end_date         date;
1713   l_effective_start_date       date;
1714   l_npw_number                 per_people_f.npw_number%TYPE;
1715   l_exists                     varchar2(1);
1716   l_full_name                  per_all_people_f.full_name%TYPE;
1717   l_legislation_code           per_business_groups.legislation_code%TYPE;
1718   l_max_tpe_end_date           date;
1719   l_name_combination_warning   boolean;
1720   l_orig_hire_warning          boolean;
1721   l_per_object_version_number  number;
1722   l_per_system_status          per_assignment_status_types.per_system_status%TYPE;
1723   l_person_id                  number;
1724   l_date_start                 date;
1725   l_person_type_id             number;
1726   l_proc                       varchar2(72)
1727                                        := g_package ||
1728                                          'actual_termination_placement';
1729   l_system_person_type1        per_person_types.system_person_type%TYPE;
1730   l_per_effective_start_date   date;
1731   l_datetrack_mode             varchar2(30);
1732   l_validation_start_date      date;
1733   l_validation_end_date        date;
1734   l_actual_termination_date    date;
1735   l_status                     varchar2(11);
1736   l_current_dod                date;
1737   l_date_of_death              date;
1738   l_ptu_object_version_number  number;
1739   l_person_type_usage_id       number;
1740   l_applicant_number           per_all_people_f.applicant_number%TYPE;
1741   l_employee_number            per_all_people_f.employee_number%TYPE := hr_api.g_varchar2;
1742   l_current_applicant_flag     per_all_people_f.current_applicant_flag%TYPE;
1743   l_current_emp_or_apl_flag    per_all_people_f.current_emp_or_apl_flag%TYPE;
1744   l_current_employee_flag      per_all_people_f.current_employee_flag%TYPE;
1745   --
1746 
1747   cursor csr_future_per_changes is
1748     select null
1749       from per_all_people_f per
1750      where per.person_id            = l_person_id
1751        and per.effective_start_date > l_actual_termination_date;
1752   --
1753   cursor csr_get_asgs_to_terminate is
1754     select asg.assignment_id
1755          , asg.object_version_number
1756       from per_all_assignments_f asg
1757      where asg.person_id      = l_person_id
1758        and asg.period_of_placement_date_start     = l_date_start
1759        and l_actual_termination_date + 1 between asg.effective_start_date
1760                                          and     asg.effective_end_date
1761      order by asg.primary_flag;
1762   --
1763   cursor csr_get_derived_details is
1764     select bus.business_group_id
1765          , bus.legislation_code
1766          , per.person_id
1767          , per.effective_start_date
1768          , per.object_version_number
1769          , per.npw_number
1770          , per.applicant_number
1771       from per_all_people_f         per
1772          , per_business_groups      bus
1773          , per_periods_of_placement pdp
1774      where pdp.person_id = p_person_id
1775      and   pdp.date_start = p_date_start
1776      and   bus.business_group_id     = pdp.business_group_id
1777      and   per.person_id             = pdp.person_id
1778      and   l_actual_termination_date between per.effective_start_date
1779                                      and     per.effective_end_date;
1780   --
1781   cursor csr_get_max_tpe_end_date is
1782     select max(tpe.end_date)
1783     from   per_time_periods  tpe
1784           ,per_all_assignments_f asg
1785     where  asg.person_id = l_person_id
1786     and    asg.period_of_placement_date_start = l_date_start
1787     and    l_actual_termination_date between asg.effective_start_date
1788                                      and     asg.effective_end_date
1789     and    asg.payroll_id            is not null
1790     and    tpe.payroll_id            = asg.payroll_id
1791     and    l_actual_termination_date between tpe.start_date
1792                                      and     tpe.end_date;
1793   --
1794   cursor csr_date_of_death is
1795     select date_of_death
1796     from per_all_people_f
1797     where person_id = l_person_id;
1798   --
1799   --
1800 begin
1801   hr_utility.set_location('Entering:'|| l_proc, 1);
1802   --
1803   -- Issue a savepoint.
1804   --
1805   savepoint actual_termination_placement;
1806   --
1807   -- Initialise local variables
1808   --
1809   l_person_type_id             := p_person_type_id;
1810   l_assignment_status_type_id  := p_assignment_status_type_id;
1811   l_last_standard_process_date := trunc(p_last_standard_process_date);
1812   l_pdp_object_version_number  := p_object_version_number;
1813   l_actual_termination_date    := trunc(p_actual_termination_date);
1814   l_effective_date             := trunc(p_effective_date);
1815   l_date_start                 := trunc(p_date_start);
1816   --
1817   hr_utility.set_location(l_proc, 10);
1818   --
1819   -- Validation in addition to Table Handlers
1820   --
1821   -- Check period of placement and get business group details for validation.
1822   --
1823   hr_api.mandatory_arg_error
1824      (p_api_name       => l_proc
1825      ,p_argument       => 'person_id'
1826      ,p_argument_value => p_person_id
1827      );
1828   --
1829   hr_api.mandatory_arg_error
1830      (p_api_name       => l_proc
1831      ,p_argument       => 'date_start'
1832      ,p_argument_value => p_date_start
1833      );
1834   --
1835   hr_api.mandatory_arg_error
1836      (p_api_name       => l_proc
1837      ,p_argument       => 'actual_termination_date'
1838      ,p_argument_value => l_actual_termination_date
1839      );
1840   --
1841   hr_utility.set_location(l_proc, 20);
1842   --
1843   open  csr_get_derived_details;
1844   fetch csr_get_derived_details
1845    into l_business_group_id
1846       , l_legislation_code
1847       , l_person_id
1848       , l_per_effective_start_date
1849       , l_per_object_version_number
1850       , l_npw_number
1851       , l_applicant_number;
1852   --
1853   if csr_get_derived_details%NOTFOUND
1854   then
1855     --
1856     hr_utility.set_location(l_proc, 30);
1857     --
1858     close csr_get_derived_details;
1859     --
1860     fnd_message.set_name('PER','HR_289609_PDP_NOT_EXISTS');
1861     fnd_message.raise_error;
1862   end if;
1863   --
1864   close csr_get_derived_details;
1865 
1866   hr_utility.set_location(l_proc, 40);
1867   --
1868   -- Check that the corresponding person has a person type usage of
1869   -- contingent worker at the actual termination date.
1870   --
1871   if not (hr_general2.is_person_type
1872            (p_person_id       => p_person_id
1873            ,p_person_type     => 'CWK'
1874            ,p_effective_date  => l_actual_termination_date))
1875   then
1876 
1877     hr_utility.set_location(l_proc, 50);
1878 
1879     fnd_message.set_name('PER','HR_289612_PDP_NOT_PTU_CWK');
1880     fnd_message.raise_error;
1881 
1882   end if;
1883 
1884   hr_utility.set_location(l_proc, 60);
1885 
1886   --
1887   -- Check that there are not any future changes to the person.
1888   --
1889   open  csr_future_per_changes;
1890   fetch csr_future_per_changes
1891    into l_exists;
1892   --
1893   if csr_future_per_changes%FOUND
1894   then
1895     --
1896     hr_utility.set_location(l_proc, 70);
1897     --
1898     close csr_future_per_changes;
1899     --
1900     fnd_message.set_name('PAY','HR_7957_PDS_INV_ATT_FUTURE');
1901     fnd_message.raise_error;
1902   end if;
1903   --
1904   hr_utility.set_location(l_proc, 80);
1905   --
1906   close csr_future_per_changes;
1907 
1908   --
1909   -- if p_person_type_id is entered, is should be of an ex-
1910   -- contingent worker person type.  l_person_type_id is
1911   -- passed because it is an IN OUT parameter and is populated
1912   -- with the default person_type_id if it was null.
1913   --
1914   per_per_bus.chk_person_type
1915       (p_person_type_id    => l_person_type_id
1916       ,p_business_group_id => l_business_group_id
1917       ,p_expected_sys_type => 'EX_CWK'
1918       );
1919   --
1920   hr_utility.set_location(l_proc, 90);
1921   hr_utility.trace('l_assignment_status_type_id: '||
1922                     to_char(l_assignment_status_type_id));
1923   hr_utility.trace('l_business_group_id: '||
1924                     to_char(l_business_group_id));
1925   hr_utility.trace('l_legislation_code: '||l_legislation_code);
1926 
1927 
1928   --
1929   -- If l_assignment_status_type_id is g_number then derive it's
1930   -- default value, otherwise validate it (the parameter is an
1931   -- IN OUT).
1932   --
1933   -- For CWK Phase I we bypass this check because we are not
1934   -- changing the assignment status to 'terminated'.
1935   --
1936 /*
1937   per_asg_bus1.chk_assignment_status_type
1938     (p_assignment_status_type_id => l_assignment_status_type_id
1939     ,p_business_group_id         => l_business_group_id
1940     ,p_legislation_code          => l_legislation_code
1941     ,p_expected_system_status    => 'TERM_CWK_ASG'
1942     );
1943 */
1944   --
1945   hr_utility.set_location(l_proc, 100);
1946   --
1947   -- Validate/derive the last standard process date.
1948   --
1949   --
1950   hr_utility.set_location(l_proc, 130);
1951   --
1952   if l_last_standard_process_date is not null
1953   then
1954       --
1955       hr_utility.set_location(l_proc, 140);
1956       --
1957       -- Check that the last standard process date is on or after the actual
1958       -- termination date.
1959       --
1960       if not l_last_standard_process_date >= l_actual_termination_date
1961       then
1962         --
1963         hr_utility.set_location(l_proc, 150);
1964         --
1965         fnd_message.set_name('PAY','HR_7505_PDS_INV_LSP_ATT_DT');
1966         fnd_message.raise_error;
1967       end if;
1968   else
1969       --
1970       hr_utility.set_location(l_proc, 160);
1971       --
1972       -- Last standard process date is null => derive it.
1973       --
1974       -- Find the max tpe end date of any payrolls that are assigned.
1975       --
1976       open  csr_get_max_tpe_end_date;
1977       fetch csr_get_max_tpe_end_date
1978        into l_max_tpe_end_date;
1979       --
1980       if csr_get_max_tpe_end_date%NOTFOUND
1981       then
1982         --
1983         hr_utility.set_location(l_proc, 170);
1984         --
1985         close csr_get_max_tpe_end_date;
1986         --
1987         -- As the cursor should always return at least a null value, this
1988         -- should never happen!
1989         --
1990         fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1991         fnd_message.set_token('PROCEDURE', l_proc);
1992         fnd_message.set_token('STEP','175');
1993         fnd_message.raise_error;
1994       end if;
1995       --
1996       close csr_get_max_tpe_end_date;
1997       --
1998       hr_utility.set_location(l_proc, 180);
1999       --
2000       if l_max_tpe_end_date is not null
2001       then
2002         --
2003         hr_utility.set_location(l_proc, 190);
2004         --
2005         -- A time period end date has been found, so set the last standard
2006         -- process date to that.
2007         --
2008         l_last_standard_process_date := l_max_tpe_end_date;
2009       else
2010         --
2011         hr_utility.set_location(l_proc, 200);
2012         --
2013         -- Either there was not an assignment assigned to a payroll, or
2014         -- there was no time period for that payroll as of the actual
2015         -- termination date. It doesn't matter which as we will default
2016    -- the LSPD to the ATD.
2017         --
2018         l_last_standard_process_date := l_actual_termination_date;
2019       end if;
2020   end if;
2021   --
2022   hr_utility.set_location(l_proc, 240);
2023   --
2024   -- Lock the person record in PER_PEOPLE_F ready for UPDATE at a later point.
2025   -- (Note: This is necessary because calling the table handlers in locking
2026   --        ladder order invokes an error in per_pdp_upd.upd due to the person
2027   --        being modified by the per_per_upd.upd table handler.)
2028   --
2029   l_datetrack_mode     := 'UPDATE';
2030   --
2031   per_per_shd.lck
2032     (p_effective_date                 => l_actual_termination_date + 1
2033     ,p_datetrack_mode                 => l_datetrack_mode
2034     ,p_person_id                      => l_person_id
2035     ,p_object_version_number          => l_per_object_version_number
2036     ,p_validation_start_date          => l_validation_start_date
2037     ,p_validation_end_date            => l_validation_end_date
2038     );
2039 
2040 hr_utility.set_location(l_proc, 245);
2041   --
2042   -- Update actual termination date and last standard process date in
2043   -- periods of placement table.
2044 
2045   per_pdp_upd.upd
2046     (p_effective_date             => p_actual_termination_date + 1
2047     ,p_object_version_number      => l_pdp_object_version_number
2048     ,p_person_id                  => l_person_id
2049     ,p_date_start                 => l_date_start
2050     ,p_actual_termination_date    => l_actual_termination_date
2051     ,p_last_standard_process_date => l_last_standard_process_date
2052     ,p_termination_reason         => p_termination_reason);
2053 
2054 --
2055   if p_termination_reason = 'D' then
2056     open csr_date_of_death;
2057     fetch csr_date_of_death into l_current_dod;
2058     if l_current_dod is null then
2059       l_date_of_death := p_actual_termination_date;
2060       l_dod_warning := TRUE;
2061     else
2062       l_date_of_death := l_current_dod;
2063     end if;
2064     close csr_date_of_death;
2065   end if;
2066 --
2067 if l_dod_warning = TRUE then
2068   hr_utility.set_location(l_proc, 998);
2069 else
2070   hr_utility.set_location(l_proc,999);
2071 end if;
2072 --
2073   -- Update person type in person table.
2074   --
2075   hr_utility.set_location(l_proc, 250);
2076   per_per_upd.upd
2077     (p_person_id                => l_person_id
2078     ,p_effective_start_date     => l_effective_start_date
2079     ,p_effective_end_date       => l_effective_end_date
2080     ,p_comment_id               => l_comment_id
2081     ,p_current_npw_flag         => l_current_npw_flag
2082     ,p_npw_number               => l_npw_number
2083     ,p_applicant_number         => l_applicant_number
2084     ,p_employee_number          => l_employee_number
2085     ,p_current_applicant_flag   => l_current_applicant_flag
2086     ,p_current_emp_or_apl_flag  => l_current_emp_or_apl_flag
2087     ,p_current_employee_flag    => l_current_employee_flag
2088     ,p_full_name                => l_full_name
2089     ,p_object_version_number    => l_per_object_version_number
2090     ,p_effective_date           => l_actual_termination_date + 1
2091     ,p_datetrack_mode           => 'UPDATE'
2092     ,p_date_of_death            => l_date_of_death
2093     ,p_validate                 => p_validate
2094     ,p_name_combination_warning => l_name_combination_warning
2095     ,p_dob_null_warning         => l_dob_null_warning
2096     ,p_orig_hire_warning        => l_orig_hire_warning
2097     );
2098   --
2099   hr_utility.set_location(l_proc, 260);
2100   --
2101   -- Terminate the assignments, ensuring that the non-primaries are
2102   -- processed before the primary (implemented via 'order by primary_flag'
2103   -- clause in cursor declaration).
2104   --
2105   for csr_rec in csr_get_asgs_to_terminate
2106   loop
2107     --
2108     hr_utility.set_location(l_proc, 270);
2109     --
2110     hr_assignment_internal.actual_term_cwk_asg
2111       (p_assignment_id              => csr_rec.assignment_id
2112       ,p_object_version_number      => csr_rec.object_version_number
2113       ,p_actual_termination_date    => l_actual_termination_date
2114       ,p_last_standard_process_date => l_last_standard_process_date
2115       ,p_assignment_status_type_id  => l_assignment_status_type_id
2116       ,p_effective_start_date       => l_effective_start_date
2117       ,p_effective_end_date         => l_effective_end_date
2118       ,p_asg_future_changes_warning => l_cr_asg_future_changes_warn
2119       ,p_entries_changed_warning    => l_cr_entries_changed_warn
2120       ,p_pay_proposal_warning       => l_pay_proposal_warn
2121       );
2122     --
2123     hr_utility.set_location(l_proc, 280);
2124     --
2125     -- Set entries changed warning using the precedence of 'S', then 'Y', then
2126     -- 'N'.
2127     --
2128     if l_cr_entries_changed_warn = 'S' or
2129        l_entries_changed_warning = 'S' then
2130       --
2131       hr_utility.set_location(l_proc, 290);
2132       --
2133        l_entries_changed_warning := 'S';
2134       --
2135     elsif l_cr_entries_changed_warn = 'Y' or
2136         l_entries_changed_warning = 'Y' then
2137       --
2138       hr_utility.set_location(l_proc, 300);
2139       --
2140       l_entries_changed_warning := 'Y';
2141 
2142     else
2143       --
2144       hr_utility.set_location(l_proc, 305);
2145       --
2146       l_entries_changed_warning := 'N';
2147 
2148     end if;
2149     --
2150     hr_utility.set_location(l_proc, 310);
2151     --
2152     -- Set future changes warning.
2153     --
2154     if l_cr_asg_future_changes_warn or l_asg_future_changes_warning
2155     then
2156       --
2157       hr_utility.set_location(l_proc, 320);
2158       --
2159       l_asg_future_changes_warning := TRUE;
2160 
2161     end if;
2162 
2163   end loop;
2164   --
2165   hr_utility.set_location(l_proc, 330);
2166   --
2167   -- Added code to support the following Out warning parameters.
2168   --
2169   l_status := 'SUPERVISOR';
2170   pre_term_check(l_status,
2171        l_business_group_id,
2172        l_person_id,
2173        l_actual_termination_date);
2174   if l_status = 'WARNING' then
2175     p_supervisor_warning := TRUE;
2176   else
2177     p_supervisor_warning := FALSE;
2178   end if;
2179   --
2180   l_status := 'EVENT';
2181   pre_term_check(l_status,
2182        l_business_group_id,
2183        l_person_id,
2184        l_actual_termination_date);
2185   if l_status = 'WARNING' then
2186     p_event_warning := TRUE;
2187   else
2188     p_event_warning := FALSE;
2189   end if;
2190   --
2191   l_status := 'INTERVIEW';
2192   pre_term_check(l_status,
2193        l_business_group_id,
2194        l_person_id,
2195        l_actual_termination_date);
2196   if l_status = 'WARNING' then
2197     p_interview_warning := TRUE;
2198   else
2199     p_interview_warning := FALSE;
2200   end if;
2201   --
2202   l_status := 'REVIEW';
2203   pre_term_check(l_status,
2204        l_business_group_id,
2205        l_person_id,
2206        l_actual_termination_date);
2207   if l_status = 'WARNING' then
2208     p_review_warning := TRUE;
2209   else
2210     p_review_warning := FALSE;
2211   end if;
2212   --
2213   l_status := 'RECRUITER';
2214   pre_term_check(l_status,
2215        l_business_group_id,
2216        l_person_id,
2217        l_actual_termination_date);
2218   if l_status = 'WARNING' then
2219     p_recruiter_warning := TRUE;
2220   else
2221     p_recruiter_warning := FALSE;
2222   end if;
2223   --
2224 
2225   --
2226   -- Make the PTU changes for terminating a contingent worker.
2227   -- l_person_type_id holds validated flavour of EX_EMP
2228 
2229   hr_per_type_usage_internal.maintain_person_type_usage
2230        (p_effective_date        =>  p_actual_termination_date +1
2231        ,p_person_id             => l_person_id
2232        ,p_person_type_id        => l_person_type_id
2233        ,p_datetrack_update_mode => 'UPDATE'
2234        );
2235   --
2236   -- When in validation only mode raise the Validate_Enabled exception
2237   --
2238   if p_validate then
2239     raise hr_api.validate_enabled;
2240   end if;
2241   --
2242   -- Set all output arguments
2243   --
2244   p_asg_future_changes_warning := l_asg_future_changes_warning;
2245   p_entries_changed_warning    := l_entries_changed_warning;
2246   p_pay_proposal_warning       := l_pay_proposal_warn;
2247   p_dod_warning                := l_dod_warning;
2248   p_last_standard_process_date := l_last_standard_process_date;
2249   p_object_version_number      := l_pdp_object_version_number;
2250   --
2251   --
2252   hr_utility.set_location(' Leaving:'||l_proc, 340);
2253 exception
2254   when hr_api.validate_enabled then
2255     --
2256     -- As the Validate_Enabled exception has been raised
2257     -- we must rollback to the savepoint
2258     --
2259     ROLLBACK TO actual_termination_placement;
2260     --
2261     -- Only set output warning arguments
2262     -- (Any key or derived arguments must be set to null
2263     -- when validation only mode is being used.)
2264     --
2265     p_asg_future_changes_warning := l_asg_future_changes_warning;
2266     p_entries_changed_warning    := l_entries_changed_warning;
2267     p_pay_proposal_warning       := l_pay_proposal_warn;
2268     p_dod_warning                := l_dod_warning;
2269     --
2270     -- p_object_version_number and p_last_standard_process_date
2271     -- should return their IN values, they still hold their IN values
2272     -- so do nothing here.
2273     --
2274     --
2275   when others then
2276     --
2277     -- A validation or unexpected error has occurred
2278     --
2279     ROLLBACK TO actual_termination_placement;
2280     --
2281     -- set in out parameters and set out parameters
2282     --
2283   p_object_version_number  := l_pdp_object_version_number;
2284  p_last_standard_process_date   := l_last_standard_process_date;
2285   p_supervisor_warning          := null;
2286   p_event_warning               := null;
2287   p_interview_warning           := null;
2288   p_review_warning              := null;
2289   p_recruiter_warning           := null;
2290   p_asg_future_changes_warning  := null;
2291   p_entries_changed_warning     := null;
2292   p_pay_proposal_warning        := null;
2293   p_dod_warning                 := null;
2294 
2295     raise;
2296     --
2297     -- End of fix.
2298     --
2299 end actual_termination_placement;
2300 --
2301 -- ----------------------------------------------------------------------------
2302 -- |-------------------------< final_process_placement >----------------------|
2303 -- ----------------------------------------------------------------------------
2304 --
2305 procedure final_process_placement
2306   (p_validate                     in     boolean  default false
2307   ,p_person_id                    in     number
2308   ,p_date_start                   in     date
2309   ,p_object_version_number        in out nocopy number
2310   ,p_final_process_date           in out nocopy date
2311   ,p_org_now_no_manager_warning      out nocopy boolean
2312   ,p_asg_future_changes_warning      out nocopy boolean
2313   ,p_entries_changed_warning         out nocopy varchar2
2314   ) is
2315   --
2316   -- Declare cursors and local variables
2317   --
2318   -- Out variables
2319   --
2320   l_asg_future_changes_warning boolean     := FALSE;
2321   l_entries_changed_warning    varchar2(1) := 'N';
2322   l_final_process_date         date;
2323   l_org_now_no_manager_warning boolean     := FALSE;
2324   l_pdp_object_version_number  number;
2325   --
2326   l_actual_termination_date    date;
2327   l_cr_asg_future_changes_warn boolean     := FALSE;
2328   l_cr_entries_changed_warn    varchar2(1) := 'N';
2329   l_cr_org_now_no_manager_warn boolean     := FALSE;
2330   l_effective_end_date         date;
2331   l_effective_start_date       date;
2332   l_exists                     varchar2(1);
2333   l_last_standard_process_date date;
2334   l_legislation_code           per_business_groups.legislation_code%TYPE;
2335   l_object_version_number      number;
2336   l_person_id                  number;
2337   l_proc                       varchar2(72) := g_package ||
2338                                             'final_process_placement';
2339   l_exemppet_eff_date          date;
2340   --
2341   cursor csr_get_derived_details is
2342     select bus.legislation_code
2343          , pdp.actual_termination_date
2344          , pdp.last_standard_process_date
2345          , pdp.person_id
2346     , pdp.object_version_number
2347       from per_business_groups    bus
2348          , per_periods_of_placement pdp
2349      where pdp.person_id = p_person_id
2350      and   pdp.date_start = p_date_start
2351      and   bus.business_group_id    = pdp.business_group_id;
2352   --
2353   cursor csr_get_asgs_to_final_proc is
2354     select asg.assignment_id
2355          , asg.object_version_number
2356          , asg.primary_flag
2357       from per_all_assignments_f asg
2358      where asg.person_id = p_person_id
2359        and asg.period_of_placement_date_start = p_date_start
2360        and l_final_process_date     between asg.effective_start_date
2361                                     and     asg.effective_end_date
2362      order by asg.primary_flag;
2363   --
2364 
2365 begin
2366   hr_utility.set_location('Entering:'|| l_proc, 1);
2367   --
2368   l_final_process_date          := trunc(p_final_process_date);
2369   --
2370   -- Issue a savepoint.
2371   --
2372   savepoint final_process_placement;
2373 
2374   hr_utility.set_location(l_proc, 10);
2375   --
2376   -- Validation in addition to Table Handlers
2377   --
2378   -- Check person id.
2379   --
2380   hr_api.mandatory_arg_error
2381      (p_api_name       => l_proc
2382      ,p_argument       => 'person_id'
2383      ,p_argument_value => p_person_id
2384      );
2385   --
2386   -- Check date start.
2387   --
2388   hr_api.mandatory_arg_error
2389      (p_api_name       => l_proc
2390      ,p_argument       => 'date_start'
2391      ,p_argument_value => p_date_start
2392      );
2393   --
2394   -- Check object version number.
2395   --
2396   hr_api.mandatory_arg_error
2397      (p_api_name       => l_proc
2398      ,p_argument       => 'object_version_number'
2399      ,p_argument_value => p_object_version_number
2400      );
2401   --
2402   hr_utility.set_location(l_proc, 20);
2403   --
2404   open  csr_get_derived_details;
2405   fetch csr_get_derived_details
2406    into l_legislation_code
2407        ,l_actual_termination_date
2408        ,l_last_standard_process_date
2409        ,l_person_id
2410        ,l_object_version_number;
2411   --
2412   if csr_get_derived_details%NOTFOUND
2413   then
2414     --
2415     hr_utility.set_location(l_proc, 30);
2416     --
2417     close csr_get_derived_details;
2418     --
2419     fnd_message.set_name('PER','HR_289609_PDP_NOT_EXISTS');
2420     fnd_message.raise_error;
2421   end if;
2422   --
2423   close csr_get_derived_details;
2424   --
2425   -- Validate the derived OVN with passed OVN.
2426 
2427   if  l_object_version_number <> p_object_version_number
2428   then
2429 
2430     fnd_message.set_name('PAY','HR_7155_OBJECT_INVALID');
2431     fnd_message.raise_error;
2432   end if;
2433 
2434   hr_utility.set_location(l_proc, 40);
2435   --
2436   -- Check that the actual termination date has already been set.
2437   --
2438   if l_actual_termination_date is null
2439   then
2440     --
2441     hr_utility.set_location(l_proc, 50);
2442     --
2443     fnd_message.set_name('PAY','HR_51007_ASG_INV_NOT_ACT_TERM');
2444     fnd_message.raise_error;
2445   end if;
2446   --
2447   -- Check if the final process date is set
2448   --
2449   if l_legislation_code = 'US'
2450     and p_final_process_date is null
2451   then
2452     --
2453     -- Default the FPD to the LSPD
2454     --
2455     l_final_process_date := l_actual_termination_date;
2456     --
2457     -- Add one day to the last standard process date to get the
2458     -- validation date
2459     --
2460     -- Set the EX CWK effective date to the FPD + 1
2461     --
2462     --   Note: Since the FPD equals the ATD then the cwk
2463     --         has not been Ex CWK for at least one day
2464     --
2465     l_exemppet_eff_date := l_final_process_date+1;
2466     --
2467   elsif p_final_process_date is null
2468   then
2469     --
2470     -- Default the FPD to the LSPD
2471     --
2472     l_final_process_date := l_last_standard_process_date;
2473     --
2474     -- Add one day to the last standard process date to get the
2475     -- validation date
2476     --
2477     -- Set the EX CWK effective date to the FPD + 1
2478     --
2479     --   Note: Since the FPD equals the LSPD then the cwk
2480     --         has not been Ex CWK for at least one day
2481     --
2482     l_exemppet_eff_date := l_final_process_date+1;
2483     --
2484   elsif p_final_process_date = l_actual_termination_date then
2485     --
2486     l_final_process_date := p_final_process_date;
2487     --
2488     -- Set the EX CWK effective date to the FPD + 1
2489     --
2490     --   Note: Since the FPD equals the ATD then the cwk
2491     --         has not been Ex CWK for at least one day
2492     --
2493     l_exemppet_eff_date := p_final_process_date+1;
2494     --
2495   else
2496     --
2497     l_final_process_date := p_final_process_date;
2498     --
2499     l_exemppet_eff_date := p_final_process_date;
2500     --
2501   end if;
2502   --
2503   hr_utility.set_location(l_proc, 90);
2504   --
2505   -- Check that the corresponding person is of EX_CWK system person type.
2506   --
2507   if not (hr_general2.is_person_type
2508            (p_person_id       => p_person_id
2509            ,p_person_type     => 'EX_CWK'
2510            ,p_effective_date  => l_exemppet_eff_date))
2511   then
2512     --
2513     hr_utility.set_location(l_proc, 100);
2514     --
2515     fnd_message.set_name('PER','HR_289613_PDP_NOT_PTU_EX_CWK');
2516     fnd_message.raise_error;
2517     --
2518   end if;
2519   --
2520   hr_utility.set_location(l_proc, 110);
2521   --
2522   --
2523   -- Check that there are no COBRA benefits after the final process date.
2524   --
2525   -- Not implemented yet due to outstanding issues.
2526   --
2527   -- Update final process date in periods of placement table.
2528   --
2529   per_pdp_upd.upd
2530     (p_person_id                  => p_person_id
2531     ,p_date_start                 => p_date_start
2532     ,p_final_process_date         => l_final_process_date
2533     ,p_object_version_number      => l_object_version_number
2534     ,p_effective_date             => l_final_process_date
2535     );
2536 
2537   --
2538   hr_utility.set_location(l_proc, 120);
2539   --
2540   -- Final process the assignments, ensuring that the non-primaries are
2541   -- processed before the primary (implemented via 'order by primary_flag'
2542   -- clause in cursor declaration).
2543   --
2544   for csr_rec in csr_get_asgs_to_final_proc
2545   loop
2546     --
2547     hr_utility.set_location(l_proc, 130);
2548     --
2549     hr_assignment_internal.final_process_cwk_asg
2550       (p_assignment_id              => csr_rec.assignment_id
2551       ,p_object_version_number      => csr_rec.object_version_number
2552       ,p_actual_termination_date    => l_actual_termination_date
2553       ,p_final_process_date         => l_final_process_date
2554       ,p_effective_start_date       => l_effective_start_date
2555       ,p_effective_end_date         => l_effective_end_date
2556       ,p_org_now_no_manager_warning => l_cr_org_now_no_manager_warn
2557       ,p_asg_future_changes_warning => l_cr_asg_future_changes_warn
2558       ,p_entries_changed_warning    => l_cr_entries_changed_warn
2559       );
2560     --
2561     hr_utility.set_location(l_proc, 140);
2562     --
2563     -- Set entries changed warning using the precedence of 'S', then 'Y', then
2564     -- 'N'.
2565     --
2566     if l_cr_entries_changed_warn = 'S'
2567        or l_entries_changed_warning = 'S'
2568     then
2569       --
2570       hr_utility.set_location(l_proc, 150);
2571       --
2572       l_entries_changed_warning := 'S';
2573       --
2574     elsif l_cr_entries_changed_warn = 'Y'
2575      or  l_entries_changed_warning = 'Y'
2576     then
2577       --
2578       hr_utility.set_location(l_proc, 160);
2579       --
2580       l_entries_changed_warning := 'Y';
2581       --
2582      else
2583       --
2584       hr_utility.set_location(l_proc, 165);
2585       --
2586       l_entries_changed_warning := 'N';
2587       --
2588     end if;
2589     --
2590     hr_utility.set_location(l_proc, 170);
2591     --
2592     -- Set future changes warning.
2593     --
2594     if l_cr_asg_future_changes_warn
2595     then
2596       --
2597       hr_utility.set_location(l_proc, 180);
2598       --
2599       l_asg_future_changes_warning := TRUE;
2600     end if;
2601     --
2602     -- Set org now no manager warning.
2603     --
2604     if l_cr_org_now_no_manager_warn
2605     then
2606       --
2607       hr_utility.set_location(l_proc, 190);
2608       --
2609       l_org_now_no_manager_warning := TRUE;
2610     end if;
2611   end loop;
2612 
2613   hr_utility.set_location(l_proc, 200);
2614 
2615   --
2616   -- When in validation only mode raise the Validate_Enabled exception
2617   --
2618   if p_validate then
2619     raise hr_api.validate_enabled;
2620   end if;
2621   --
2622   -- Set all output arguments
2623   --
2624   p_asg_future_changes_warning := l_asg_future_changes_warning;
2625   p_entries_changed_warning    := l_entries_changed_warning;
2626   p_final_process_date         := l_final_process_date;
2627   p_object_version_number      := l_object_version_number;
2628   p_org_now_no_manager_warning := l_org_now_no_manager_warning;
2629   --
2630   hr_utility.set_location(' Leaving:'||l_proc, 400);
2631 exception
2632   when hr_api.validate_enabled then
2633     --
2634     -- As the Validate_Enabled exception has been raised
2635     -- we must rollback to the savepoint
2636     --
2637     ROLLBACK TO final_process_placement;
2638     --
2639     -- Only set output warning arguments
2640     -- (Any key or derived arguments must be set to null
2641     -- when validation only mode is being used.)
2642     --
2643     p_asg_future_changes_warning := l_asg_future_changes_warning;
2644     p_entries_changed_warning    := l_entries_changed_warning;
2645     p_org_now_no_manager_warning := l_org_now_no_manager_warning;
2646     --
2647   when others then
2648     --
2649     -- A validation or unexpected error has occurred
2650     --
2651     ROLLBACK TO final_process_placement;
2652     --
2653     -- set in out parameters and set out parameters
2654     --
2655  p_object_version_number      := l_object_version_number;
2656  p_final_process_date         := l_final_process_date;
2657  p_org_now_no_manager_warning := l_org_now_no_manager_warning;
2658  p_asg_future_changes_warning := l_asg_future_changes_warning;
2659  p_entries_changed_warning    := null;
2660     --
2661     raise;
2662     --
2663 end final_process_placement;
2664 --
2665 -- ----------------------------------------------------------------------------
2666 -- |-------------------------< terminate_placement >--------------------------|
2667 -- ----------------------------------------------------------------------------
2668 --
2669 procedure terminate_placement
2670   (p_validate                      in     boolean  default false
2671   ,p_effective_date                in     date
2672   ,p_person_id                     in     number
2673   ,p_date_start                    in     date
2674   ,p_object_version_number         in out nocopy number
2675   ,p_person_type_id                in     number   default hr_api.g_number
2676   ,p_assignment_status_type_id     in     number   default hr_api.g_number
2677   ,p_actual_termination_date       in     date     default hr_api.g_date
2678 
2679 /*
2680    The following two parameters are available for internal-use only until
2681    payroll support for contingent workers is introduced. Setting them has
2682    no impact.
2683 */
2684   ,p_final_process_date            in out nocopy date
2685   ,p_last_standard_process_date    in out nocopy date
2686 
2687   ,p_termination_reason            in     varchar2 default hr_api.g_varchar2
2688   ,p_projected_termination_date    in     date     default hr_api.g_date
2689   ,p_attribute_category            in     varchar2 default hr_api.g_varchar2
2690   ,p_attribute1                    in     varchar2 default hr_api.g_varchar2
2691   ,p_attribute2                    in     varchar2 default hr_api.g_varchar2
2692   ,p_attribute3                    in     varchar2 default hr_api.g_varchar2
2693   ,p_attribute4                    in     varchar2 default hr_api.g_varchar2
2694   ,p_attribute5                    in     varchar2 default hr_api.g_varchar2
2695   ,p_attribute6                    in     varchar2 default hr_api.g_varchar2
2696   ,p_attribute7                    in     varchar2 default hr_api.g_varchar2
2697   ,p_attribute8                    in     varchar2 default hr_api.g_varchar2
2698   ,p_attribute9                    in     varchar2 default hr_api.g_varchar2
2699   ,p_attribute10                   in     varchar2 default hr_api.g_varchar2
2700   ,p_attribute11                   in     varchar2 default hr_api.g_varchar2
2701   ,p_attribute12                   in     varchar2 default hr_api.g_varchar2
2702   ,p_attribute13                   in     varchar2 default hr_api.g_varchar2
2703   ,p_attribute14                   in     varchar2 default hr_api.g_varchar2
2704   ,p_attribute15                   in     varchar2 default hr_api.g_varchar2
2705   ,p_attribute16                   in     varchar2 default hr_api.g_varchar2
2706   ,p_attribute17                   in     varchar2 default hr_api.g_varchar2
2707   ,p_attribute18                   in     varchar2 default hr_api.g_varchar2
2708   ,p_attribute19                   in     varchar2 default hr_api.g_varchar2
2709   ,p_attribute20                   in     varchar2 default hr_api.g_varchar2
2710   ,p_attribute21                   in     varchar2 default hr_api.g_varchar2
2711   ,p_attribute22                   in     varchar2 default hr_api.g_varchar2
2712   ,p_attribute23                   in     varchar2 default hr_api.g_varchar2
2713   ,p_attribute24                   in     varchar2 default hr_api.g_varchar2
2714   ,p_attribute25                   in     varchar2 default hr_api.g_varchar2
2715   ,p_attribute26                   in     varchar2 default hr_api.g_varchar2
2716   ,p_attribute27                   in     varchar2 default hr_api.g_varchar2
2717   ,p_attribute28                   in     varchar2 default hr_api.g_varchar2
2718   ,p_attribute29                   in     varchar2 default hr_api.g_varchar2
2719   ,p_attribute30                   in     varchar2 default hr_api.g_varchar2
2720   ,p_information_category          in     varchar2 default hr_api.g_varchar2
2721   ,p_information1                  in     varchar2 default hr_api.g_varchar2
2722   ,p_information2                  in     varchar2 default hr_api.g_varchar2
2723   ,p_information3                  in     varchar2 default hr_api.g_varchar2
2724   ,p_information4                  in     varchar2 default hr_api.g_varchar2
2725   ,p_information5                  in     varchar2 default hr_api.g_varchar2
2726   ,p_information6                  in     varchar2 default hr_api.g_varchar2
2727   ,p_information7                  in     varchar2 default hr_api.g_varchar2
2728   ,p_information8                  in     varchar2 default hr_api.g_varchar2
2729   ,p_information9                  in     varchar2 default hr_api.g_varchar2
2730   ,p_information10                 in     varchar2 default hr_api.g_varchar2
2731   ,p_information11                 in     varchar2 default hr_api.g_varchar2
2732   ,p_information12                 in     varchar2 default hr_api.g_varchar2
2733   ,p_information13                 in     varchar2 default hr_api.g_varchar2
2734   ,p_information14                 in     varchar2 default hr_api.g_varchar2
2735   ,p_information15                 in     varchar2 default hr_api.g_varchar2
2736   ,p_information16                 in     varchar2 default hr_api.g_varchar2
2737   ,p_information17                 in     varchar2 default hr_api.g_varchar2
2738   ,p_information18                 in     varchar2 default hr_api.g_varchar2
2739   ,p_information19                 in     varchar2 default hr_api.g_varchar2
2740   ,p_information20                 in     varchar2 default hr_api.g_varchar2
2741   ,p_information21                 in     varchar2 default hr_api.g_varchar2
2742   ,p_information22                 in     varchar2 default hr_api.g_varchar2
2743   ,p_information23                 in     varchar2 default hr_api.g_varchar2
2744   ,p_information24                 in     varchar2 default hr_api.g_varchar2
2745   ,p_information25                 in     varchar2 default hr_api.g_varchar2
2746   ,p_information26                 in     varchar2 default hr_api.g_varchar2
2747   ,p_information27                 in     varchar2 default hr_api.g_varchar2
2748   ,p_information28                 in     varchar2 default hr_api.g_varchar2
2749   ,p_information29                 in     varchar2 default hr_api.g_varchar2
2750   ,p_information30                 in     varchar2 default hr_api.g_varchar2
2751   ,p_supervisor_warning               out nocopy boolean
2752   ,p_event_warning                    out nocopy boolean
2753   ,p_interview_warning                out nocopy boolean
2754   ,p_review_warning                   out nocopy boolean
2755   ,p_recruiter_warning                out nocopy boolean
2756   ,p_asg_future_changes_warning       out nocopy boolean
2757   ,p_entries_changed_warning          out nocopy varchar2
2758   ,p_pay_proposal_warning             out nocopy boolean
2759   ,p_dod_warning                      out nocopy boolean
2760   ,p_org_now_no_manager_warning       out nocopy boolean
2761   ,p_addl_rights_warning              out nocopy boolean -- Fix 1370960
2762   ) is
2763 
2764 l_proc varchar2(80) := g_package||'terminate_placement';
2765 l_curr_actual_termination_date    date;
2766 l_curr_final_process_date         date;
2767 l_final_process_date              date;
2768 dummy                             number := 0; -- fix 1370960
2769 
2770 cursor csr_get_pdp_details is
2771     select actual_termination_date
2772           ,final_process_date
2773       from per_periods_of_placement
2774      where person_id  = p_person_id
2775      and   date_start = p_date_start;
2776 
2777 -- fix 1370960
2778 cursor csr_roles_to_terminate is
2779   select
2780     role_id
2781   , object_version_number
2782   , end_date
2783   from per_roles
2784   where person_id = p_person_id
2785   and p_actual_termination_date
2786   between start_date
2787   and nvl(end_date, hr_api.g_eot);
2788 
2789 cursor csr_chk_addl_rights is
2790   select role_id
2791   from per_roles
2792   where person_id = p_person_id
2793   and EMP_RIGHTS_FLAG = 'Y'
2794   and nvl(end_of_rights_date, hr_api.g_eot) > p_actual_termination_date;
2795 -- end fix 1370960
2796 
2797 begin
2798 
2799   hr_utility.set_location('Entering: '||l_proc, 10);
2800 
2801   --
2802   -- Issue a savepoint
2803   --
2804   savepoint terminate_placement;
2805 
2806   hr_utility.set_location(l_proc, 15);
2807 
2808   /* For CWK Phase I we default the final process
2809      and last standard process date to the actual
2810      termination date so that assignments are immediately
2811      ended.
2812      For CWK Phase II, this defaulting will be removed
2813      so that the behaviour supports CWKs on payrolls. */
2814 
2815 --  l_final_process_date := p_final_process_date;
2816   l_final_process_date := p_actual_termination_date;
2817   p_last_standard_process_date := p_actual_termination_date;
2818 
2819   --
2820   -- Start of API User Hook for the before hook of terminate_placement
2821   --
2822   begin
2823      hr_contingent_worker_bk4.terminate_placement_b
2824        (p_effective_date                => p_effective_date
2825        ,p_person_id                     => p_person_id
2826        ,p_date_start                    => p_date_start
2827        ,p_object_version_number         => p_object_version_number
2828        ,p_person_type_id                => p_person_type_id
2829        ,p_assignment_status_type_id     => p_assignment_status_type_id
2830        ,p_actual_termination_date       => p_actual_termination_date
2831        ,p_final_process_date            => l_final_process_date
2832        ,p_last_standard_process_date    => p_last_standard_process_date
2833        ,p_termination_reason            => p_termination_reason
2834        ,p_projected_termination_date    => null
2835        ,p_attribute_category            => p_attribute_category
2836        ,p_attribute1                    => p_attribute1
2837        ,p_attribute2                    => p_attribute2
2838        ,p_attribute3                    => p_attribute3
2839        ,p_attribute4                    => p_attribute4
2840        ,p_attribute5                    => p_attribute5
2841        ,p_attribute6                    => p_attribute6
2842        ,p_attribute7                    => p_attribute7
2843        ,p_attribute8                    => p_attribute8
2844        ,p_attribute9                    => p_attribute9
2845        ,p_attribute10                   => p_attribute10
2846        ,p_attribute11                   => p_attribute11
2847        ,p_attribute12                   => p_attribute12
2848        ,p_attribute13                   => p_attribute13
2849        ,p_attribute14                   => p_attribute14
2850        ,p_attribute15                   => p_attribute15
2851        ,p_attribute16                   => p_attribute16
2852        ,p_attribute17                   => p_attribute17
2853        ,p_attribute18                   => p_attribute18
2854        ,p_attribute19                   => p_attribute19
2855        ,p_attribute20                   => p_attribute20
2856        ,p_attribute21                   => p_attribute21
2857        ,p_attribute22                   => p_attribute22
2858        ,p_attribute23                   => p_attribute23
2859        ,p_attribute24                   => p_attribute24
2860        ,p_attribute25                   => p_attribute25
2861        ,p_attribute26                   => p_attribute26
2862        ,p_attribute27                   => p_attribute27
2863        ,p_attribute28                   => p_attribute28
2864        ,p_attribute29                   => p_attribute29
2865        ,p_attribute30                   => p_attribute30
2866        ,p_information_category          => p_information_category
2867        ,p_information1                  => p_information1
2868        ,p_information2                  => p_information2
2869        ,p_information3                  => p_information3
2870        ,p_information4                  => p_information4
2871        ,p_information5                  => p_information5
2872        ,p_information6                  => p_information6
2873        ,p_information7                  => p_information7
2874        ,p_information8                  => p_information8
2875        ,p_information9                  => p_information9
2876        ,p_information10                 => p_information10
2877        ,p_information11                 => p_information11
2878        ,p_information12                 => p_information12
2879        ,p_information13                 => p_information13
2880        ,p_information14                 => p_information14
2881        ,p_information15                 => p_information15
2882        ,p_information16                 => p_information16
2883        ,p_information17                 => p_information17
2884        ,p_information18                 => p_information18
2885        ,p_information19                 => p_information19
2886        ,p_information20                 => p_information20
2887        ,p_information21                 => p_information21
2888        ,p_information22                 => p_information22
2889        ,p_information23                 => p_information23
2890        ,p_information24                 => p_information24
2891        ,p_information25                 => p_information25
2892        ,p_information26                 => p_information26
2893        ,p_information27                 => p_information27
2894        ,p_information28                 => p_information28
2895        ,p_information29                 => p_information29
2896        ,p_information30                 => p_information30
2897        );
2898   exception
2899      when hr_api.cannot_find_prog_unit then
2900        hr_api.cannot_find_prog_unit_error
2901          (p_module_name       => 'TERMINATE_PLACEMENT',
2902           p_hook_type         => 'BP'
2903          );
2904   end;
2905   --
2906   -- End of API User Hook for the before hook of actual_termination
2907   --
2908   hr_utility.set_location(l_proc, 20);
2909 
2910   /*
2911   ** We need to get the details currently on the PDP record so that
2912   ** we know if the person has already been partially or fully terminated.
2913   ** If partial termination (ATD set but FPD not set) then don't call
2914   ** actual_termination_emp API. If full termination (ATD and FPD both set)
2915   ** then don't call either termination API and just update the details.
2916   */
2917   open  csr_get_pdp_details;
2918   fetch csr_get_pdp_details
2919    into l_curr_actual_termination_date
2920        ,l_curr_final_process_date;
2921   close csr_get_pdp_details;
2922 
2923   hr_utility.set_location(l_proc, 30);
2924 
2925   /*
2926   ** Save the non-termination related PDP information....
2927   */
2928   hr_periods_of_placement_api.update_pdp_details
2929      (p_validate                    => FALSE
2930      ,p_effective_date              => p_effective_date
2931      ,p_object_version_number       => p_object_version_number
2932      ,p_person_id                   => p_person_id
2933      ,p_date_start                  => p_date_start
2934      ,p_termination_reason          => p_termination_reason
2935      ,p_projected_termination_date  => null
2936      ,p_attribute_category          => p_attribute_category
2937      ,p_attribute1                  => p_attribute1
2938      ,p_attribute2                  => p_attribute2
2939      ,p_attribute3                  => p_attribute3
2940      ,p_attribute4                  => p_attribute4
2941      ,p_attribute5                  => p_attribute5
2942      ,p_attribute6                  => p_attribute6
2943      ,p_attribute7                  => p_attribute7
2944      ,p_attribute8                  => p_attribute8
2945      ,p_attribute9                  => p_attribute9
2946      ,p_attribute10                 => p_attribute10
2947      ,p_attribute11                 => p_attribute11
2948      ,p_attribute12                 => p_attribute12
2949      ,p_attribute13                 => p_attribute13
2950      ,p_attribute14                 => p_attribute14
2951      ,p_attribute15                 => p_attribute15
2952      ,p_attribute16                 => p_attribute16
2953      ,p_attribute17                 => p_attribute17
2954      ,p_attribute18                 => p_attribute18
2955      ,p_attribute19                 => p_attribute19
2956      ,p_attribute20                 => p_attribute20
2957      ,p_attribute21                 => p_attribute21
2958      ,p_attribute22                 => p_attribute22
2959      ,p_attribute23                 => p_attribute23
2960      ,p_attribute24                 => p_attribute24
2961      ,p_attribute25                 => p_attribute25
2962      ,p_attribute26                 => p_attribute26
2963      ,p_attribute27                 => p_attribute27
2964      ,p_attribute28                 => p_attribute28
2965      ,p_attribute29                 => p_attribute29
2966      ,p_attribute30                 => p_attribute30
2967      ,p_information_category        => p_information_category
2968      ,p_information1                => p_information1
2969      ,p_information2                => p_information2
2970      ,p_information3                => p_information3
2971      ,p_information4                => p_information4
2972      ,p_information5                => p_information5
2973      ,p_information6                => p_information6
2974      ,p_information7                => p_information7
2975      ,p_information8                => p_information8
2976      ,p_information9                => p_information9
2977      ,p_information10               => p_information10
2978      ,p_information11               => p_information11
2979      ,p_information12               => p_information12
2980      ,p_information13               => p_information13
2981      ,p_information14               => p_information14
2982      ,p_information15               => p_information15
2983      ,p_information16               => p_information16
2984      ,p_information17               => p_information17
2985      ,p_information18               => p_information18
2986      ,p_information19               => p_information19
2987      ,p_information20               => p_information20
2988      ,p_information21               => p_information21
2989      ,p_information22               => p_information22
2990      ,p_information23               => p_information23
2991      ,p_information24               => p_information24
2992      ,p_information25               => p_information25
2993      ,p_information26               => p_information26
2994      ,p_information27               => p_information27
2995      ,p_information28               => p_information28
2996      ,p_information29               => p_information29
2997      ,p_information30               => p_information30
2998   );
2999 
3000   hr_utility.set_location(l_proc, 40);
3001 
3002   /*
3003   ** Process actual termination date if it's set for the first time....
3004   */
3005   if     l_curr_actual_termination_date is null
3006      and p_actual_termination_date is not null
3007   then
3008 
3009     hr_utility.set_location(l_proc, 50);
3010 
3011     hr_contingent_worker_api.actual_termination_placement
3012        (p_validate                   => FALSE
3013        ,p_effective_date             => p_effective_date
3014        ,p_person_id                  => p_person_id
3015        ,p_date_start                 => p_date_start
3016        ,p_object_version_number      => p_object_version_number
3017        ,p_actual_termination_date    => p_actual_termination_date
3018        ,p_last_standard_process_date => p_last_standard_process_date
3019        ,p_person_type_id             => p_person_type_id
3020        ,p_assignment_status_type_id  => p_assignment_status_type_id
3021        ,p_termination_reason         => p_termination_reason
3022        ,p_supervisor_warning         => p_supervisor_warning
3023        ,p_event_warning              => p_event_warning
3024        ,p_interview_warning          => p_interview_warning
3025        ,p_review_warning             => p_review_warning
3026        ,p_recruiter_warning          => p_recruiter_warning
3027        ,p_asg_future_changes_warning => p_asg_future_changes_warning
3028        ,p_entries_changed_warning    => p_entries_changed_warning
3029        ,p_pay_proposal_warning       => p_pay_proposal_warning
3030        ,p_dod_warning                => p_dod_warning
3031        );
3032 
3033       -- fix 1370960
3034       -- Terminate the roles
3035       for roles_rec in csr_roles_to_terminate
3036       loop
3037         per_supplementary_role_api.update_supplementary_role(
3038         p_effective_date                => p_effective_date
3039         ,p_role_id                      => roles_rec.role_id
3040         ,p_object_version_number        => roles_rec.object_version_number
3041         ,p_end_date                     => p_actual_termination_date
3042         ,p_old_end_date                 => roles_rec.end_date
3043         );
3044       end loop;
3045 
3046       -- Raise a warning if extra rights are there for the person
3047       open csr_chk_addl_rights;
3048       fetch csr_chk_addl_rights into dummy;
3049       if csr_chk_addl_rights%found then
3050         p_addl_rights_warning := TRUE;
3051       else
3052         p_addl_rights_warning := FALSE;
3053       end if;
3054       close csr_chk_addl_rights;
3055       -- end fix 1370960
3056 
3057    end if;
3058 
3059   hr_utility.set_location(l_proc, 60);
3060 
3061   /*
3062   ** If it's set process final process date....
3063   */
3064   if l_curr_final_process_date is null
3065      and l_final_process_date is not null
3066   then
3067 
3068     hr_utility.set_location(l_proc, 70);
3069 
3070     hr_contingent_worker_api.final_process_placement
3071        (p_validate                    => FALSE
3072        ,p_person_id                   => p_person_id
3073        ,p_date_start                  => p_date_start
3074        ,p_object_version_number       => p_object_version_number
3075        ,p_final_process_date          => l_final_process_date
3076        ,p_org_now_no_manager_warning  => p_org_now_no_manager_warning
3077        ,p_asg_future_changes_warning  => p_asg_future_changes_warning
3078        ,p_entries_changed_warning     => p_entries_changed_warning
3079        );
3080   end if;
3081 
3082   hr_utility.set_location(l_proc, 80);
3083   --
3084   -- Added HR workflow for the termination process. Bug 3829474
3085   -- Even though the following call is psecific to period of service,
3086   -- used the same forperiod of placement.
3087   --
3088 
3089   /* Bug 5504659
3090   PER_HRWF_SYNCH.per_pds_wf(
3091        p_person_id      => p_person_id
3092       ,p_date_start     => p_actual_termination_date
3093       ,p_date           => p_actual_termination_date
3094       ,p_action         => 'TERMINATION');
3095 Note : added p_date_start to test, earlier code does not work*/
3096 
3097   --
3098   -- Fix for bug 3829474 ends here.
3099   --
3100   -- Start of API User Hook for the after hook of terminate_placement
3101   --
3102   begin
3103      hr_contingent_worker_bk4.terminate_placement_a
3104        (p_effective_date                => p_effective_date
3105        ,p_person_id                     => p_person_id
3106        ,p_date_start                    => p_date_start
3107        ,p_object_version_number         => p_object_version_number
3108        ,p_person_type_id                => p_person_type_id
3109        ,p_assignment_status_type_id     => p_assignment_status_type_id
3110        ,p_actual_termination_date       => p_actual_termination_date
3111        ,p_final_process_date            => l_final_process_date
3112        ,p_last_standard_process_date    => p_last_standard_process_date
3113        ,p_termination_reason            => p_termination_reason
3114        ,p_projected_termination_date    => null
3115        ,p_attribute_category            => p_attribute_category
3116        ,p_attribute1                    => p_attribute1
3117        ,p_attribute2                    => p_attribute2
3118        ,p_attribute3                    => p_attribute3
3119        ,p_attribute4                    => p_attribute4
3120        ,p_attribute5                    => p_attribute5
3121        ,p_attribute6                    => p_attribute6
3122        ,p_attribute7                    => p_attribute7
3123        ,p_attribute8                    => p_attribute8
3124        ,p_attribute9                    => p_attribute9
3125        ,p_attribute10                   => p_attribute10
3126        ,p_attribute11                   => p_attribute11
3127        ,p_attribute12                   => p_attribute12
3128        ,p_attribute13                   => p_attribute13
3129        ,p_attribute14                   => p_attribute14
3130        ,p_attribute15                   => p_attribute15
3131        ,p_attribute16                   => p_attribute16
3132        ,p_attribute17                   => p_attribute17
3133        ,p_attribute18                   => p_attribute18
3134        ,p_attribute19                   => p_attribute19
3135        ,p_attribute20                   => p_attribute20
3136        ,p_attribute21                   => p_attribute21
3137        ,p_attribute22                   => p_attribute22
3138        ,p_attribute23                   => p_attribute23
3139        ,p_attribute24                   => p_attribute24
3140        ,p_attribute25                   => p_attribute25
3141        ,p_attribute26                   => p_attribute26
3142        ,p_attribute27                   => p_attribute27
3143        ,p_attribute28                   => p_attribute28
3144        ,p_attribute29                   => p_attribute29
3145        ,p_attribute30                   => p_attribute30
3146        ,p_information_category          => p_information_category
3147        ,p_information1                  => p_information1
3148        ,p_information2                  => p_information2
3149        ,p_information3                  => p_information3
3150        ,p_information4                  => p_information4
3151        ,p_information5                  => p_information5
3152        ,p_information6                  => p_information6
3153        ,p_information7                  => p_information7
3154        ,p_information8                  => p_information8
3155        ,p_information9                  => p_information9
3156        ,p_information10                 => p_information10
3157        ,p_information11                 => p_information11
3158        ,p_information12                 => p_information12
3159        ,p_information13                 => p_information13
3160        ,p_information14                 => p_information14
3161        ,p_information15                 => p_information15
3162        ,p_information16                 => p_information16
3163        ,p_information17                 => p_information17
3164        ,p_information18                 => p_information18
3165        ,p_information19                 => p_information19
3166        ,p_information20                 => p_information20
3167        ,p_information21                 => p_information21
3168        ,p_information22                 => p_information22
3169        ,p_information23                 => p_information23
3170        ,p_information24                 => p_information24
3171        ,p_information25                 => p_information25
3172        ,p_information26                 => p_information26
3173        ,p_information27                 => p_information27
3174        ,p_information28                 => p_information28
3175        ,p_information29                 => p_information29
3176        ,p_information30                 => p_information30
3177        ,p_supervisor_warning            => p_supervisor_warning
3178        ,p_event_warning                 => p_event_warning
3179        ,p_interview_warning             => p_interview_warning
3180        ,p_review_warning                => p_review_warning
3181        ,p_recruiter_warning             => p_recruiter_warning
3182        ,p_asg_future_changes_warning    => p_asg_future_changes_warning
3183        ,p_entries_changed_warning       => p_entries_changed_warning
3184        ,p_pay_proposal_warning          => p_pay_proposal_warning
3185        ,p_dod_warning                   => p_dod_warning
3186        ,p_org_now_no_manager_warning    => p_org_now_no_manager_warning
3187        ,p_addl_rights_warning           => p_addl_rights_warning -- Fix 1370960
3188        );
3189   exception
3190      when hr_api.cannot_find_prog_unit then
3191        hr_api.cannot_find_prog_unit_error
3192          (p_module_name       => 'TERMINATE_PLACEMENT',
3193           p_hook_type         => 'AP'
3194          );
3195   end;
3196 
3197   --
3198   -- When in validation only mode raise the Validate_Enabled exception
3199   --
3200   IF p_validate THEN
3201     RAISE hr_api.validate_enabled;
3202   END IF;
3203 
3204   hr_utility.set_location('Leaving: '||l_proc, 90);
3205 
3206 EXCEPTION
3207   when hr_api.validate_enabled then
3208     --
3209     -- As the Validate_Enabled exception has been raised
3210     -- we must rollback to the savepoint
3211     --
3212     ROLLBACK TO terminate_placement;
3213 
3214   when others then
3215     --
3216     -- A validation or unexpected error has occurred
3217     --
3218     ROLLBACK TO terminate_placement;
3219     --
3220     -- set in out parameters and set out parameters
3221     --
3222     --p_object_version_number := l_object_version_number;
3223     --
3224     raise;
3225     --
3226     hr_utility.set_location('Leaving: '||l_proc, 100);
3227 
3228 end terminate_placement;
3229 --
3230 -- ----------------------------------------------------------------------------
3231 -- |-------------------------< reverse_terminate_placement >------------------|
3232 -- ----------------------------------------------------------------------------
3233 --
3234 procedure reverse_terminate_placement
3235   (p_validate                      in     boolean  default false
3236   ,p_person_id                     in     number
3237   ,p_actual_termination_date       in     date
3238   ,p_clear_details                 in     varchar2 default 'N'
3239   ,p_fut_actns_exist_warning       out nocopy    boolean
3240   ) is
3241 
3242   l_proc varchar2(80) := g_package||'reverse_terminate_placement';
3243   l_final_process_date         DATE;
3244   l_last_standard_process_date DATE;
3245   l_per_system_status          VARCHAR2(30);
3246   l_max_end_date               DATE;
3247   l_effective_end_date         DATE;
3248   l_action_chk                 VARCHAR2(1) := 'N';
3249   l_action_date                DATE;
3250   l_asg_status_type_id         NUMBER;
3251   FPD_FLAG                     BOOLEAN;
3252   b_future_person_type_err     BOOLEAN := FALSE;
3253 
3254   CURSOR c_assignment IS
3255   SELECT assignment_id
3256   ,      assignment_status_type_id
3257   ,      business_group_id
3258   FROM   per_all_assignments_f ass
3259   WHERE  ass.person_id = p_person_id
3260   AND    ass.effective_end_date = p_actual_termination_date
3261   FOR UPDATE;
3262   --
3263   CURSOR  future_person_types IS
3264   SELECT  pt.system_person_type
3265   FROM    per_person_type_usages_f ptu,
3266           per_person_types pt
3267   WHERE   ptu.person_id = p_person_id
3268   AND     ptu.person_type_id = pt.person_type_id
3269   AND     ptu.effective_start_date > p_actual_termination_date;
3270 
3271   cursor c1 is
3272     select *
3273     from   per_periods_of_placement
3274     where  person_id = p_person_id
3275     and    actual_termination_date = p_actual_termination_date;
3276   --
3277 
3278   -- fix 1370960
3279     cursor csr_roles is
3280     select role_id
3281     ,object_version_number
3282     ,old_end_date from
3283     per_roles
3284     where person_id = p_person_id
3285     and end_date = p_actual_termination_date
3286     for update nowait;
3287   -- fix 1370960 end
3288 
3289   l_c1 c1%rowtype;
3290   --
3291   --
3292   -- START WWBUG fix for 1390173
3293   --
3294   l_old   ben_pps_ler.g_pps_ler_rec;
3295   l_new   ben_pps_ler.g_pps_ler_rec;
3296   --
3297   -- END WWBUG fix for 1390173
3298   --
3299   --
3300   -- Start of Fix for WWBUG 1408379
3301   --
3302   cursor c2(p_assignment_id number) is
3303     select *
3304     from   per_assignment_budget_values_f
3305     where  assignment_id = p_assignment_id
3306     and    effective_end_date = l_final_process_date;
3307   --
3308   l_old_abv   ben_abv_ler.g_abv_ler_rec;
3309   l_new_abv   ben_abv_ler.g_abv_ler_rec;
3310   l_c2 c2%rowtype;
3311   --
3312   -- End of Fix for WWBUG 1408379
3313   --
3314 
3315 begin
3316 
3317   hr_utility.set_location('Entering: '||l_proc, 10);
3318 
3319   --
3320   -- Issue a savepoint.
3321   --
3322   savepoint reverse_terminate_placement;
3323 
3324   hr_api.mandatory_arg_error
3325      (p_api_name       => l_proc
3326      ,p_argument       => 'person_id'
3327      ,p_argument_value => p_person_id
3328      );
3329   --
3330   hr_api.mandatory_arg_error
3331      (p_api_name       => l_proc
3332      ,p_argument       => 'actual_termination_date'
3333      ,p_argument_value => p_actual_termination_date
3334      );
3335 
3336   --
3337   -- Start of API User Hook for the before hook of reverse_terminate_placement
3338   --
3339   begin
3340      hr_contingent_worker_bk5.reverse_terminate_placement_b
3341        (p_validate                      => p_validate
3342        ,p_person_id                     => p_person_id
3343        ,p_actual_termination_date       => p_actual_termination_date
3344        ,p_clear_details                 => p_clear_details
3345        );
3346   exception
3347      when hr_api.cannot_find_prog_unit then
3348        hr_api.cannot_find_prog_unit_error
3349          (p_module_name       => 'REVERSE_TERMINATE_PLACEMENT',
3350           p_hook_type         => 'BP'
3351          );
3352   end;
3353   --
3354   -- End of API User Hook for the before hook of reverse_terminate_placement
3355   --
3356   hr_utility.set_location(l_proc, 20);
3357 
3358   hr_utility.trace('Entered reverse termination for '||p_person_id);
3359   --
3360   hr_utility.set_location(l_proc,25);
3361   begin
3362   SELECT pdp.final_process_date
3363   ,      pdp.last_standard_process_date
3364   INTO   l_final_process_date
3365   ,      l_last_standard_process_date
3366   FROM   per_periods_of_placement pdp
3367   WHERE  pdp.person_id = p_person_id
3368   AND    pdp.actual_termination_date = p_actual_termination_date;
3369   --
3370   exception when NO_DATA_FOUND then
3371 
3372     fnd_message.set_name('PER','HR_289614_PDP_STILL_OPEN');
3373     fnd_message.raise_error;
3374   end;
3375   --
3376   hr_utility.set_location(l_proc,30);
3377 
3378   --
3379   -- Check for future person types
3380   --
3381   for fpt_rec in future_person_types loop
3382 
3383     hr_utility.set_location(l_proc,35);
3384     hr_utility.trace('System person type: '||fpt_rec.system_person_type);
3385 -- start of bug 4457651
3386 -- commented out the if condition and redifined it
3387 --    if fpt_rec.system_person_type <> 'EX_CWK' then
3388     if fpt_rec.system_person_type in ('EMP','APL','CWK') then
3389       b_future_person_type_err := TRUE;
3390     end if;
3391 
3392   end loop;
3393 
3394   if b_future_person_type_err = TRUE then
3395      fnd_message.set_name('PAY','HR_7122_EMP_CNCL_TERM_INVLD');
3396      fnd_message.raise_error;
3397   end if;
3398   --
3399   hr_utility.set_location(l_proc,40);
3400 
3401   --
3402   -- Check for future completed actions.
3403   --
3404   if l_last_standard_process_date is not null then
3405 
3406     if p_actual_termination_date is not null
3407     and l_last_standard_process_date > p_actual_termination_date then
3408       l_action_date := l_last_standard_process_date;
3409     else
3410       l_action_date := null;
3411     end if;
3412 
3413   else
3414 
3415     l_action_date := p_actual_termination_date;
3416 
3417   end if;
3418 
3419   BEGIN
3420     SELECT 'Y'
3421       INTO   l_action_chk
3422       FROM   dual
3423       WHERE  exists
3424            (SELECT null
3425             FROM   pay_payroll_actions pac,
3426                    pay_assignment_actions act,
3427                    per_all_assignments_f asg
3428             WHERE  asg.person_id = p_person_id
3429             AND    act.assignment_id = asg.assignment_id
3430             AND    pac.payroll_action_id = act.payroll_action_id
3431             AND    pac.action_type not in  ('X','BEE')    -- Bug 889806,2711532
3432             AND    pac.effective_date > l_final_process_date);
3433     exception when NO_DATA_FOUND then null;
3434     END;
3435     --
3436     hr_utility.set_location(l_proc,45);
3437 
3438     IF l_action_chk = 'N' THEN
3439       BEGIN
3440         SELECT 'W'
3441         INTO   l_action_chk
3442         FROM   sys.dual
3443         WHERE  exists
3444           (SELECT null
3445            FROM    pay_payroll_actions pac,
3446                    pay_assignment_actions act,
3447                    per_all_assignments_f asg
3448            WHERE   asg.person_id = p_person_id
3449            AND     act.assignment_id = asg.assignment_id
3450            AND     pac.payroll_action_id = act.payroll_action_id
3451            AND     pac.action_status = 'C'
3452            AND    (pac.effective_date BETWEEN l_action_date AND l_final_process_date));
3453         --
3454         hr_utility.set_location(l_proc,7);
3455         exception when NO_DATA_FOUND then null;
3456         END;
3457     END IF;
3458 
3459   --
3460   IF l_action_chk = 'W' THEN
3461 
3462     fnd_message.set_name('PER','HR_289615_FUTURE_ACTIONS_EXIST');
3463     fnd_message.raise_error;
3464   END IF;
3465   --
3466   if l_action_chk = 'Y' then
3467     p_fut_actns_exist_warning := TRUE;
3468   end if;
3469   --
3470   hr_utility.set_location(l_proc,50);
3471 
3472   FPD_FLAG := (l_final_process_date IS NOT NULL);
3473   --
3474   hr_utility.set_location(l_proc,55);
3475   UPDATE per_people_f pp
3476   SET    pp.effective_end_date = hr_api.g_eot
3477   WHERE  pp.person_id          = p_person_id
3478   AND    p_actual_termination_date
3479          BETWEEN pp.effective_start_date
3480          AND     pp.effective_end_date;
3481   --
3482   hr_utility.set_location(l_proc,60);
3483   DELETE per_people_f pp
3484   WHERE  pp.person_id = p_person_id
3485   AND    pp.effective_start_date > p_actual_termination_date;
3486   --
3487   hr_utility.set_location(l_proc,65);
3488   --
3489   -- WWBUG #       - CERN want to keep old details
3490   --    was a feature of rel 9
3491   if (p_clear_details = 'N') then
3492      --
3493 --
3494 -- START WWBUG fix for 1390173
3495 --
3496 /*     open c1;
3497        fetch c1 into l_c1;
3498        if c1%found then
3499          --
3500          l_old.PERSON_ID := l_c1.person_id;
3501          l_old.BUSINESS_GROUP_ID := l_c1.business_group_id;
3502          l_old.DATE_START := l_c1.date_start;
3503          l_old.ACTUAL_TERMINATION_DATE := l_c1.actual_termination_date;
3504          l_old.TERMINATION_REASON := l_c1.termination_reason;
3505          l_old.ATTRIBUTE1 := l_c1.attribute1;
3506          l_old.ATTRIBUTE2 := l_c1.attribute2;
3507          l_old.ATTRIBUTE3 := l_c1.attribute3;
3508          l_old.ATTRIBUTE4 := l_c1.attribute4;
3509          l_old.ATTRIBUTE5 := l_c1.attribute5;
3510          l_old.FINAL_PROCESS_DATE := l_c1.FINAL_PROCESS_DATE;
3511          l_new.PERSON_ID := l_c1.person_id;
3512          l_new.BUSINESS_GROUP_ID := l_c1.business_group_id;
3513          l_new.DATE_START := l_c1.date_start;
3514          l_new.ACTUAL_TERMINATION_DATE := null;
3515          l_new.TERMINATION_REASON := null;
3516          l_new.ATTRIBUTE1 := l_c1.attribute1;
3517          l_new.ATTRIBUTE2 := l_c1.attribute2;
3518          l_new.ATTRIBUTE3 := l_c1.attribute3;
3519          l_new.ATTRIBUTE4 := l_c1.attribute4;
3520          l_new.ATTRIBUTE5 := l_c1.attribute5;
3521          l_new.FINAL_PROCESS_DATE := l_c1.FINAL_PROCESS_DATE;
3522          --
3523          ben_pps_ler.ler_chk(p_old            => l_old
3524                             ,p_new            => l_new
3525                             ,p_event          => 'UPDATING'
3526                             ,p_effective_date => l_c1.date_start);
3527          --
3528        end if;
3529      close c1;
3530      --
3531 */
3532 --
3533 -- END WWBUG fix for 1390173
3534 --
3535      UPDATE per_periods_of_placement pdp
3536      SET    pdp.actual_termination_date     = null
3537      ,      pdp.last_standard_process_date  = null
3538      ,      pdp.final_process_date          = null
3539      ,      pdp.termination_reason          = null
3540      ,      pdp.projected_termination_date  = null
3541      WHERE  pdp.person_id                   = p_person_id
3542      AND    pdp.actual_termination_date     = p_actual_termination_date;
3543   else
3544      --
3545 --
3546 -- START WWBUG fix for 1390173
3547 --
3548 /*
3549      open c1;
3550        fetch c1 into l_c1;
3551        if c1%found then
3552          --
3553          l_old.PERSON_ID := l_c1.person_id;
3554          l_old.BUSINESS_GROUP_ID := l_c1.business_group_id;
3555          l_old.DATE_START := l_c1.date_start;
3556          l_old.ACTUAL_TERMINATION_DATE := l_c1.actual_termination_date;
3557          l_old.TERMINATION_REASON := l_c1.leaving_reason;
3558          l_old.ATTRIBUTE1 := l_c1.attribute1;
3559          l_old.ATTRIBUTE2 := l_c1.attribute2;
3560          l_old.ATTRIBUTE3 := l_c1.attribute3;
3561          l_old.ATTRIBUTE4 := l_c1.attribute4;
3562          l_old.ATTRIBUTE5 := l_c1.attribute5;
3563          l_old.FINAL_PROCESS_DATE := l_c1.FINAL_PROCESS_DATE;
3564          l_new.PERSON_ID := l_c1.person_id;
3565          l_new.BUSINESS_GROUP_ID := l_c1.business_group_id;
3566          l_new.DATE_START := l_c1.date_start;
3567          l_new.ACTUAL_TERMINATION_DATE := null;
3568          l_new.TERMINATION_REASON := null;
3569          l_new.ATTRIBUTE1 := l_c1.attribute1;
3570          l_new.ATTRIBUTE2 := l_c1.attribute2;
3571          l_new.ATTRIBUTE3 := l_c1.attribute3;
3572          l_new.ATTRIBUTE4 := l_c1.attribute4;
3573          l_new.ATTRIBUTE5 := l_c1.attribute5;
3574          l_new.FINAL_PROCESS_DATE := l_c1.FINAL_PROCESS_DATE;
3575          --
3576          ben_pps_ler.ler_chk(p_old            => l_old
3577                             ,p_new            => l_new
3578                             ,p_event          => 'UPDATING'
3579                             ,p_effective_date => l_c1.date_start);
3580          --
3581        end if;
3582      close c1;
3583 */
3584      --
3585 --
3586 -- END WWBUG fix for 1390173
3587 --
3588      UPDATE per_periods_of_placement pdp
3589      SET    pdp.actual_termination_date     = null
3590      ,      pdp.last_standard_process_date  = null
3591      ,      pdp.final_process_date          = null
3592      ,      pdp.termination_reason          = null
3593      ,      pdp.projected_termination_date  = null
3594      WHERE  pdp.person_id                   = p_person_id
3595      AND    pdp.actual_termination_date     = p_actual_termination_date;
3596      --
3597   end if;
3598 
3599   --
3600   -- FIX to WWBUG 1176101
3601   --
3602 /*
3603   ben_dt_trgr_handle.periods_of_service
3604     (p_rowid              => null
3605     ,p_person_id          => p_person_id
3606     ,p_pds_atd            => null
3607     ,p_pds_leaving_reason => null
3608     -- Bug 1854968
3609     ,p_pds_old_atd        => l_old.actual_termination_date
3610     ,p_pds_fpd            => null);
3611 */
3612   --
3613   --
3614   --
3615   hr_utility.set_location(l_proc,70);
3616   FOR c_asg_rec IN c_assignment LOOP
3617   --
3618     hr_utility.set_location(l_proc,75);
3619     SELECT per_system_status
3620     INTO   l_per_system_status
3621     FROM   per_assignment_status_types
3622     WHERE  assignment_status_type_id = c_asg_rec.assignment_status_type_id;
3623     --
3624     -- Get the assignment_status_id from the record which ended on ATD
3625     -- so that we can set the other records for this assignment which
3626     -- are currently TERM_CWK_ASG back to the appropriate ACTIVE_CWK_ASG status.
3627     --
3628     SELECT assignment_status_type_id
3629     INTO   l_asg_status_type_id
3630     FROM   per_all_assignments_f
3631     WHERE  assignment_id = c_asg_rec.assignment_id
3632     AND    effective_end_date = p_actual_termination_date;
3633     --
3634     hr_utility.set_location(l_proc,80);
3635 
3636     SELECT  max(asg.effective_end_date)
3637     INTO    l_max_end_date
3638     FROM    per_all_assignments_f asg
3639     WHERE   asg.assignment_id = c_asg_rec.assignment_id;
3640 
3641     --
3642     if l_per_system_status <> 'TERM_CWK_ASG' then
3643       hr_utility.set_location(l_proc,85);
3644       if FPD_FLAG then
3645         hr_utility.set_location(l_proc,90);
3646         if l_max_end_date <> l_final_process_date then
3647            l_effective_end_date := l_max_end_date;
3648         else
3649            hr_utility.set_location(l_proc,95);
3650            l_effective_end_date := hr_api.g_eot;
3651         end if;
3652       else
3653          hr_utility.set_location(l_proc,100);
3654          l_effective_end_date := l_max_end_date;
3655       end if;
3656       --
3657       hr_utility.set_location(l_proc,105);
3658       --
3659       -- Open out the last dated assignment record to the end of time or
3660       -- max_end_date based on above logic.
3661       --
3662       UPDATE per_all_assignments_f ass
3663       SET    ass.effective_end_date = l_effective_end_date
3664       WHERE  assignment_id = c_asg_rec.assignment_id
3665         AND  effective_end_date = l_max_end_date;
3666       --
3667       -- We want to keep all the assignment records after the ATD so
3668       -- update them all to the same assignment_status as the record
3669       -- which ends on ATD.
3670       --
3671       -- As a result of the changes due to bug 1271513 as a result of
3672       -- terminating and reverse terminating an additional assignment
3673       -- record will exist which runs from ATD+1 to the start date-1
3674       -- of the next date effective record for the person or EOT
3675       -- depending on the data present at time of termination.
3676       --
3677       UPDATE per_all_assignments_f ass
3678       SET    ass.assignment_status_type_id = l_asg_status_type_id
3679       WHERE  assignment_id = c_asg_rec.assignment_id
3680         AND  effective_start_date >= p_actual_termination_date;
3681       --
3682     end if;
3683     --
3684     if FPD_FLAG then
3685       hr_utility.set_location(l_proc,110);
3686       if l_max_end_date <> l_final_process_date then
3687         null;
3688       else
3689         hr_utility.set_location(l_proc,115);
3690         l_effective_end_date := hr_api.g_eot;
3691         --
3692         hr_utility.set_location(l_proc,120);
3693         UPDATE per_secondary_ass_statuses sas
3694         SET    sas.end_date = null
3695         WHERE  sas.assignment_id = c_asg_rec.assignment_id
3696         AND    sas.end_date = l_final_process_date;
3697         --
3698         hr_utility.set_location(l_proc,125);
3699         UPDATE pay_personal_payment_methods_f ppm
3700         SET    ppm.effective_end_date = l_effective_end_date
3701         WHERE  ppm.assignment_id      = c_asg_rec.assignment_id
3702         AND    ppm.effective_end_date = l_final_process_date;
3703         --
3704         hr_utility.set_location(l_proc,130);
3705         UPDATE pay_cost_allocations_f pca
3706         SET    pca.effective_end_date = l_effective_end_date
3707         WHERE  pca.assignment_id      = c_asg_rec.assignment_id
3708         AND    pca.effective_end_date = l_final_process_date;
3709         --
3710         hr_utility.set_location(l_proc,135);
3711         UPDATE per_spinal_point_placements_f spp
3712         SET    spp.effective_end_date = l_effective_end_date
3713         WHERE  spp.assignment_id      = c_asg_rec.assignment_id
3714         AND    spp.effective_end_date = l_final_process_date;
3715         --
3716         UPDATE pay_grade_rules_f pgr
3717         SET    pgr.effective_end_date = l_effective_end_date
3718         WHERE  pgr.grade_or_spinal_point_Id = c_asg_rec.assignment_id
3719         AND    pgr.rate_type          = 'A'
3720         AND    pgr.effective_end_date = l_final_process_date;
3721         --
3722         --
3723         -- Adding code to update the date tracked tax tables to resolve bug
3724         -- 920233.
3725         -- Adding an extra verification to make sure the tax records are reverse
3726         -- only for US legislation. This extra verification is done because
3727         -- this package peempter.pkb is part of the CORE HR code and UK
3728         -- customer do not use these TAX tables. Only Customers with HR/CERIDIAN
3729         -- use this TAX tables.
3730 
3731         if hr_general.chk_geocodes_installed ='Y' then
3732           hr_utility.set_location(l_proc,140);
3733           pay_us_update_tax_rec_pkg.reverse_term_emp_tax_records
3734                                    (c_asg_rec.assignment_id
3735                                    ,l_final_process_date);
3736 
3737         end if; /* verification chk_geocodes_installed */
3738         --
3739         -- SASmith 30-APR-1998
3740         -- Due to date tracking of assignment_budget_values
3741 
3742         hr_utility.set_location(l_proc,145);
3743         --
3744         -- Start of Fix for WWBUG 1408379
3745         --
3746         open c2(c_asg_rec.assignment_id);
3747           --
3748           loop
3749             --
3750             fetch c2 into l_c2;
3751             exit when c2%notfound;
3752             --
3753             l_old_abv.assignment_id := l_c2.assignment_id;
3754             l_old_abv.business_group_id := l_c2.business_group_id;
3755             l_old_abv.value := l_c2.value;
3756             l_old_abv.assignment_budget_value_id := l_c2.assignment_budget_value_id;
3757             l_old_abv.effective_start_date := l_c2.effective_start_date;
3758             l_old_abv.effective_end_date := l_c2.effective_end_date;
3759             l_new_abv.assignment_id := l_c2.assignment_id;
3760             l_new_abv.business_group_id := l_c2.business_group_id;
3761             l_new_abv.value := l_c2.value;
3762             l_new_abv.assignment_budget_value_id := l_c2.assignment_budget_value_id;
3763             l_new_abv.effective_start_date := l_c2.effective_start_date;
3764             l_new_abv.effective_end_date := l_effective_end_date;
3765             --
3766             update per_assignment_budget_values_f abv
3767             set    abv.effective_end_date = l_effective_end_date
3768             where  abv.assignment_id      = c_asg_rec.assignment_id
3769             and    abv.assignment_budget_value_id = l_c2.assignment_budget_value_id
3770             and    abv.effective_end_date = l_final_process_date;
3771 /*            --
3772             ben_abv_ler.ler_chk(p_old            => l_old_abv,
3773                                 p_new            => l_new_abv,
3774                                 p_effective_date => l_c2.effective_start_date);
3775 */            --
3776           end loop;
3777           --
3778         close c2;
3779         --
3780         -- End of Fix for WWBUG 1408379
3781         --
3782       end if;
3783     --
3784     end if;
3785   --
3786   -- open up element entries closed down by the termination
3787   --
3788   hr_utility.set_location(l_proc,150);
3789   hrentmnt.maintain_entries_asg(c_asg_rec.assignment_id
3790                                ,c_asg_rec.business_group_id
3791                                ,'CNCL_TERM'
3792                                ,p_actual_termination_date
3793                                ,l_last_standard_process_date
3794                                ,l_final_process_date
3795                                ,'DELETE_NEXT_CHANGE'
3796                                ,null
3797                                ,null);
3798   --
3799   --
3800   END LOOP;
3801 
3802   --
3803   -- Change the person type usage record back to contingent worker
3804   --
3805   hr_per_type_usage_internal.cancel_person_type_usage
3806     (p_effective_date         => p_actual_termination_date+1
3807     ,p_person_id              => p_person_id
3808     ,p_system_person_type     => 'EX_CWK'
3809     );
3810 
3811   hr_utility.set_location(l_proc, 155);
3812 
3813   --
3814   -- Start of API User Hook for the after hook of reverse_terminate_placement
3815   --
3816   begin
3817      hr_contingent_worker_bk5.reverse_terminate_placement_a
3818        (p_validate                      => p_validate
3819        ,p_person_id                     => p_person_id
3820        ,p_actual_termination_date       => p_actual_termination_date
3821        ,p_clear_details                 => p_clear_details
3822        ,p_fut_actns_exist_warning       => p_fut_actns_exist_warning
3823        );
3824   exception
3825      when hr_api.cannot_find_prog_unit then
3826        hr_api.cannot_find_prog_unit_error
3827          (p_module_name       => 'REVERSE_TERMINATE_PLACEMENT',
3828           p_hook_type         => 'AP'
3829          );
3830   end;
3831   --
3832   -- End of API User Hook for the after hook of reverse_terminate_placement
3833   --
3834 
3835   -- fix 1370960
3836   for roles_rec in csr_roles
3837   loop
3838     per_supplementary_role_api.update_supplementary_role(
3839         p_effective_date                => p_actual_termination_date+1
3840         ,p_role_id                      => roles_rec.role_id
3841         ,p_object_version_number        => roles_rec.object_version_number
3842         ,p_end_date                     => roles_rec.old_end_date
3843         );
3844   end loop;
3845   -- 1370960 end
3846 
3847   hr_utility.set_location('Leaving: '||l_proc, 200);
3848 
3849 exception
3850   when hr_api.validate_enabled then
3851     --
3852     -- As the Validate_Enabled exception has been raised
3853     -- we must rollback to the savepoint
3854     --
3855     ROLLBACK TO reverse_terminate_placement;
3856 
3857   when others then
3858     --
3859     -- A validation or unexpected error has occurred
3860     --
3861     ROLLBACK TO reverse_terminate_placement;
3862     --
3863     -- set in out parameters and set out parameters
3864     --
3865     --p_object_version_number := l_object_version_number;
3866     --
3867     raise;
3868     --
3869 end reverse_terminate_placement;
3870 --
3871 -- ----------------------------------------------------------------------------
3872 -- |--------------------------< Get_Length_of_Placement >---------------------|
3873 -- ----------------------------------------------------------------------------
3874 --
3875 procedure get_length_of_placement
3876   (p_effective_date     in    date
3877   ,p_business_group_id  in    number
3878   ,p_person_id          in    number
3879   ,p_date_start         in    date
3880   ,p_total_years        out nocopy   number
3881   ,p_total_months       out nocopy   number) is
3882 
3883   l_proc         varchar2(80) := g_package||'get_length_of_placement';
3884   l_total_years  number := 0;
3885   l_total_months number := 0;
3886 
3887   cursor c_get_length is
3888   select trunc(sum(months_between
3889                     (least
3890                       (nvl(ACTUAL_TERMINATION_DATE + 1, p_effective_date + 1),
3891                        p_effective_date + 1)
3892                     ,DATE_START)) / 12, 0) total_years,
3893          trunc(mod(sum(months_between
3894                          (least
3895                            (nvl(ACTUAL_TERMINATION_DATE + 1, p_effective_date + 1),
3896                             p_effective_date + 1)
3897                          ,DATE_START)), 12), 0) total_months
3898   from   PER_PERIODS_OF_PLACEMENT
3899   where  PERSON_ID          = p_person_id
3900   and    business_group_id  = p_business_group_id
3901   and    DATE_START         = p_date_start
3902   and    DATE_START        <= p_effective_date;
3903 
3904 
3905 begin
3906 
3907   hr_utility.set_location('Entering: '||l_proc, 10);
3908   --
3909   -- Get the total length of the placement
3910   --
3911   open  c_get_length;
3912   fetch c_get_length into l_total_years,
3913                           l_total_months;
3914 
3915   if c_get_length%NOTFOUND then
3916 
3917     --
3918     -- The person could not be found.
3919     --
3920     hr_utility.set_location(l_proc, 30);
3921 
3922   end if;
3923 
3924   close c_get_length;
3925 
3926   --
3927   -- Set the out parameters
3928   --
3929   p_total_years  := l_total_years;
3930   p_total_months := l_total_months;
3931 
3932   hr_utility.set_location('Leaving: '||l_proc, 30);
3933 
3934 end get_length_of_placement;
3935 
3936 end hr_contingent_worker_api;