[Home] [Help]
PACKAGE BODY: APPS.BEN_WHATIF_ELIG
Source
1 PACKAGE body ben_whatif_elig as
2 /* $Header: benwatif.pkb 120.0 2005/05/28 09:35:40 appldev noship $ */
3 procedure p_rollback is
4 begin
5 hr_utility.set_location(' Entering: p_rollback' , 10);
6 rollback;
7 hr_utility.set_location(' Leaving: p_rollback' , 10);
8 end;
9
10 --
11 -- This procedure returns the update mode appropriate
12 -- for the given table and effective_date and key.
13 --
14 Procedure Get_DT_Upd_Mode
15 (p_effective_date in date,
16 p_base_table_name in varchar2,
17 p_base_key_column in varchar2,
18 p_base_key_value in number,
19 p_mode out nocopy varchar2
20 ) is
21 --
22 l_correction boolean := TRUE;
23 l_update boolean := FALSE;
24 l_update_override boolean := FALSE;
25 l_update_change_insert boolean := FALSE;
26 --
27 begin
28 hr_utility.set_location(' Entering: Get_DT_Upd_Mode' , 10);
29 --
30 -- Get the appropriate update mode.
31 --
32 DT_Api.Find_DT_Upd_Modes(p_effective_date => p_effective_date,
33 p_base_table_name => p_base_table_name,
34 p_base_key_column => p_base_key_column,
35 p_base_key_value => p_base_key_value,
36 p_correction => l_correction,
37 p_update => l_update,
38 p_update_override => l_update_override,
39 p_update_change_insert => l_update_change_insert);
40 --
41 if l_update_override or l_update_change_insert then
42 p_mode := 'UPDATE_OVERRIDE';
43 elsif l_update then
44 p_mode := 'UPDATE';
45 else
46 p_mode := 'CORRECTION';
47 end if;
48 --
49 hr_utility.set_location(' Leaving: Get_DT_Upd_Mode' , 10);
50 end;
51
52 procedure WATIF_ABSENCE_ATTENDANCES_API(
53 p_person_id in number
54 ,p_ABSENCE_ATTENDANCE_TYPE_ID in varchar2
55 ,p_ABS_ATTENDANCE_REASON_ID in varchar2
56 ,p_DATE_END in date
57 ,p_DATE_START in date
58 ,p_business_group_id in number
59 ,p_effective_date in date
60 ) is
61 --
62 cursor c_abs_att is
63 select
64 rowid, per_absence_attendances.*
65 from per_absence_attendances
66 where person_id = p_person_id and
67 absence_attendance_type_id = p_absence_attendance_type_id and
68 abs_attendance_reason_id =
69 nvl(p_abs_attendance_reason_id, abs_attendance_reason_id) and
70 p_effective_date between nvl(DATE_START, p_effective_date)
71 and nvl(DATE_END, p_effective_date);
72 --
73 per_abs_att_rec c_abs_att%rowtype;
74 --
75 l_rowid varchar2(50);
76 begin
77 hr_utility.set_location(' Entering: WATIF_ABSENCE_ATTENDANCES_API' , 10);
78 --
79 If p_DATE_START is null and p_DATE_END is null and
80 p_ABSENCE_ATTENDANCE_TYPE_ID is null and
81 p_ABS_ATTENDANCE_REASON_ID is null then
82 --
83 fnd_message.set_name('BEN','BEN_91447_ALL_NULL');
84 fnd_message.raise_error;
85 --
86 End If;
87 --
88 open c_abs_att;
89 fetch c_abs_att into per_abs_att_rec;
90 --
91 if p_date_start is not null then
92 per_abs_att_rec.date_start := p_date_start;
93 end if;
94 --
95 if p_date_end is not null then
96 per_abs_att_rec.date_end := p_date_end;
97 end if;
98 --
99 if p_ABSENCE_ATTENDANCE_TYPE_ID is not null then
100 per_abs_att_rec.ABSENCE_ATTENDANCE_TYPE_ID := p_ABSENCE_ATTENDANCE_TYPE_ID;
101 end if;
102 --
103 if p_ABS_ATTENDANCE_REASON_ID is not null then
104 per_abs_att_rec.ABS_ATTENDANCE_REASON_ID := p_ABS_ATTENDANCE_REASON_ID;
105 end if;
106 --
107 if c_abs_att%notfound Then
108 Close c_abs_att;
109 --
110 -- Absence Attendence Record is Not found so create one.
111 --
112 PER_ABSENCE_ATTENDANCES_PKG.Insert_Row(
113 X_Rowid => l_Rowid,
114 X_Absence_Attendance_Id => per_abs_att_rec.Absence_Attendance_Id,
115 X_Business_Group_Id => p_Business_Group_Id,
116 X_Absence_Attendance_Type_Id => per_abs_att_rec.Absence_Attendance_Type_Id,
117 X_Abs_Attendance_Reason_Id => per_abs_att_rec.Abs_Attendance_Reason_Id,
118 X_Person_Id => p_Person_Id,
119 X_Authorising_Person_Id => per_abs_att_rec.Authorising_Person_Id,
120 X_Replacement_Person_Id => per_abs_att_rec.Replacement_Person_Id,
121 X_Period_Of_Incapacity_Id => per_abs_att_rec.Period_Of_Incapacity_Id,
122 X_Absence_Days => per_abs_att_rec.Absence_Days,
123 X_Absence_Hours => per_abs_att_rec.Absence_Hours,
124 X_Comments => per_abs_att_rec.Comments,
125 X_Date_End => per_abs_att_rec.Date_End,
126 X_Date_Notification => per_abs_att_rec.Date_Notification,
127 X_Date_Projected_End => per_abs_att_rec.Date_Projected_End,
128 X_Date_Projected_Start => per_abs_att_rec.Date_Projected_Start,
129 X_Date_Start => per_abs_att_rec.Date_Start,
130 X_Occurrence => per_abs_att_rec.Occurrence,
131 X_Ssp1_Issued => per_abs_att_rec.Ssp1_Issued,
132 X_Time_End => per_abs_att_rec.Time_End,
133 X_Time_Projected_End => per_abs_att_rec.Time_Projected_End,
134 X_Time_Projected_Start => per_abs_att_rec.Time_Projected_Start,
135 X_Time_Start => per_abs_att_rec.Time_Start,
136 X_Attribute_Category => per_abs_att_rec.Attribute_Category,
137 X_Attribute1 => per_abs_att_rec.Attribute1,
138 X_Attribute2 => per_abs_att_rec.Attribute2,
139 X_Attribute3 => per_abs_att_rec.Attribute3,
140 X_Attribute4 => per_abs_att_rec.Attribute4,
141 X_Attribute5 => per_abs_att_rec.Attribute5,
142 X_Attribute6 => per_abs_att_rec.Attribute6,
143 X_Attribute7 => per_abs_att_rec.Attribute7,
144 X_Attribute8 => per_abs_att_rec.Attribute8,
145 X_Attribute9 => per_abs_att_rec.Attribute9,
146 X_Attribute10 => per_abs_att_rec.Attribute10,
147 X_Attribute11 => per_abs_att_rec.Attribute11,
148 X_Attribute12 => per_abs_att_rec.Attribute12,
149 X_Attribute13 => per_abs_att_rec.Attribute13,
150 X_Attribute14 => per_abs_att_rec.Attribute14,
151 X_Attribute15 => per_abs_att_rec.Attribute15,
152 X_Attribute16 => per_abs_att_rec.Attribute16,
153 X_Attribute17 => per_abs_att_rec.Attribute17,
154 X_Attribute18 => per_abs_att_rec.Attribute18,
155 X_Attribute19 => per_abs_att_rec.Attribute19,
156 X_Attribute20 => per_abs_att_rec.Attribute20,
157 X_Linked_Absence_id => per_abs_att_rec.Linked_Absence_id,
158 X_Sickness_Start_Date => per_abs_att_rec.Sickness_Start_Date,
159 X_Sickness_End_Date => per_abs_att_rec.Sickness_End_Date,
160 X_Accept_Late_Notif_Flag => per_abs_att_rec.ACCEPT_LATE_NOTIFICATION_FLAG,
161 X_Reason_For_Late_Notification => per_abs_att_rec.REASON_FOR_NOTIFICATION_DELAY,
162 X_Pregnancy_Related_Illness => per_abs_att_rec.Pregnancy_Related_Illness,
163 X_Maternity_Id => per_abs_att_rec.Maternity_Id,
164 X_ABS_INFORMATION_CATEGORY => NULL,
165 X_ABS_INFORMATION1 => NULL,
166 X_ABS_INFORMATION2 => NULL,
167 X_ABS_INFORMATION3 => NULL,
168 X_ABS_INFORMATION4 => NULL,
169 X_ABS_INFORMATION5 => NULL,
170 X_ABS_INFORMATION6 => NULL,
171 X_ABS_INFORMATION7 => NULL,
172 X_ABS_INFORMATION8 => NULL,
173 X_ABS_INFORMATION9 => NULL,
174 X_ABS_INFORMATION10 => NULL,
175 X_ABS_INFORMATION11 => NULL,
176 X_ABS_INFORMATION12 => NULL,
177 X_ABS_INFORMATION13 => NULL,
178 X_ABS_INFORMATION14 => NULL,
179 X_ABS_INFORMATION15 => NULL,
180 X_ABS_INFORMATION16 => NULL,
181 X_ABS_INFORMATION17 => NULL,
182 X_ABS_INFORMATION18 => NULL,
183 X_ABS_INFORMATION19 => NULL,
184 X_ABS_INFORMATION20 => NULL,
185 X_ABS_INFORMATION21 => NULL,
186 X_ABS_INFORMATION22 => NULL,
187 X_ABS_INFORMATION23 => NULL,
188 X_ABS_INFORMATION24 => NULL,
189 X_ABS_INFORMATION25 => NULL,
190 X_ABS_INFORMATION26 => NULL,
191 X_ABS_INFORMATION27 => NULL,
192 X_ABS_INFORMATION28 => NULL,
193 X_ABS_INFORMATION29 => NULL,
194 X_ABS_INFORMATION30 => NULL
195 );
196 /*
197 fnd_message.set_name('BEN','BEN_91440_NO_ABS_ATT');
198 fnd_message.raise_error;
199 */
200 else
201 --
202 Close c_abs_att;
203 --
204 --
205 -- Call the API -
206 -- ???? Do we need to update all the Per Absence Attendences.
207 --
208 PER_ABSENCE_ATTENDANCES_PKG.Update_Row(
209 X_Rowid => rowidtochar(per_abs_att_rec.Rowid),
210 X_Absence_Attendance_Id => per_abs_att_rec.Absence_Attendance_Id,
211 X_Business_Group_Id => per_abs_att_rec.Business_Group_Id,
212 X_Absence_Attendance_Type_Id => per_abs_att_rec.Absence_Attendance_Type_Id,
213 X_Abs_Attendance_Reason_Id => per_abs_att_rec.Abs_Attendance_Reason_Id,
214 X_Person_Id => per_abs_att_rec.Person_Id,
215 X_Authorising_Person_Id => per_abs_att_rec.Authorising_Person_Id,
216 X_Replacement_Person_Id => per_abs_att_rec.Replacement_Person_Id,
217 X_Period_Of_Incapacity_Id => per_abs_att_rec.Period_Of_Incapacity_Id,
218 X_Absence_Days => per_abs_att_rec.Absence_Days,
219 X_Absence_Hours => per_abs_att_rec.Absence_Hours,
220 X_Comments => per_abs_att_rec.Comments,
221 X_Date_End => per_abs_att_rec.Date_End,
222 X_Date_Notification => per_abs_att_rec.Date_Notification,
223 X_Date_Projected_End => per_abs_att_rec.Date_Projected_End,
224 X_Date_Projected_Start => per_abs_att_rec.Date_Projected_Start,
225 X_Date_Start => per_abs_att_rec.Date_Start,
226 X_Occurrence => per_abs_att_rec.Occurrence,
227 X_Ssp1_Issued => per_abs_att_rec.Ssp1_Issued,
228 X_Time_End => per_abs_att_rec.Time_End,
229 X_Time_Projected_End => per_abs_att_rec.Time_Projected_End,
230 X_Time_Projected_Start => per_abs_att_rec.Time_Projected_Start,
231 X_Time_Start => per_abs_att_rec.Time_Start,
232 X_Attribute_Category => per_abs_att_rec.Attribute_Category,
233 X_Attribute1 => per_abs_att_rec.Attribute1,
234 X_Attribute2 => per_abs_att_rec.Attribute2,
235 X_Attribute3 => per_abs_att_rec.Attribute3,
236 X_Attribute4 => per_abs_att_rec.Attribute4,
237 X_Attribute5 => per_abs_att_rec.Attribute5,
238 X_Attribute6 => per_abs_att_rec.Attribute6,
239 X_Attribute7 => per_abs_att_rec.Attribute7,
240 X_Attribute8 => per_abs_att_rec.Attribute8,
241 X_Attribute9 => per_abs_att_rec.Attribute9,
242 X_Attribute10 => per_abs_att_rec.Attribute10,
243 X_Attribute11 => per_abs_att_rec.Attribute11,
244 X_Attribute12 => per_abs_att_rec.Attribute12,
245 X_Attribute13 => per_abs_att_rec.Attribute13,
246 X_Attribute14 => per_abs_att_rec.Attribute14,
247 X_Attribute15 => per_abs_att_rec.Attribute15,
248 X_Attribute16 => per_abs_att_rec.Attribute16,
249 X_Attribute17 => per_abs_att_rec.Attribute17,
250 X_Attribute18 => per_abs_att_rec.Attribute18,
251 X_Attribute19 => per_abs_att_rec.Attribute19,
252 X_Attribute20 => per_abs_att_rec.Attribute20,
253 X_Linked_Absence_id => per_abs_att_rec.Linked_Absence_id,
254 X_Sickness_Start_Date => per_abs_att_rec.Sickness_Start_Date,
255 X_Sickness_End_Date => per_abs_att_rec.Sickness_End_Date,
256 X_Accept_Late_Notif_Flag => per_abs_att_rec.ACCEPT_LATE_NOTIFICATION_FLAG,
257 X_Reason_For_Late_Notification => per_abs_att_rec.REASON_FOR_NOTIFICATION_DELAY,
258 X_Pregnancy_Related_Illness => per_abs_att_rec.Pregnancy_Related_Illness,
259 X_Maternity_Id => per_abs_att_rec.Maternity_Id,
260 X_ABS_INFORMATION_CATEGORY => NULL,
261 X_ABS_INFORMATION1 => NULL,
262 X_ABS_INFORMATION2 => NULL,
263 X_ABS_INFORMATION3 => NULL,
264 X_ABS_INFORMATION4 => NULL,
265 X_ABS_INFORMATION5 => NULL,
266 X_ABS_INFORMATION6 => NULL,
267 X_ABS_INFORMATION7 => NULL,
268 X_ABS_INFORMATION8 => NULL,
269 X_ABS_INFORMATION9 => NULL,
270 X_ABS_INFORMATION10 => NULL,
271 X_ABS_INFORMATION11 => NULL,
272 X_ABS_INFORMATION12 => NULL,
273 X_ABS_INFORMATION13 => NULL,
274 X_ABS_INFORMATION14 => NULL,
275 X_ABS_INFORMATION15 => NULL,
276 X_ABS_INFORMATION16 => NULL,
277 X_ABS_INFORMATION17 => NULL,
278 X_ABS_INFORMATION18 => NULL,
279 X_ABS_INFORMATION19 => NULL,
280 X_ABS_INFORMATION20 => NULL,
281 X_ABS_INFORMATION21 => NULL,
282 X_ABS_INFORMATION22 => NULL,
283 X_ABS_INFORMATION23 => NULL,
284 X_ABS_INFORMATION24 => NULL,
285 X_ABS_INFORMATION25 => NULL,
286 X_ABS_INFORMATION26 => NULL,
287 X_ABS_INFORMATION27 => NULL,
288 X_ABS_INFORMATION28 => NULL,
289 X_ABS_INFORMATION29 => NULL,
290 X_ABS_INFORMATION30 => NULL
291 );
292 End If;
293 --
294 hr_utility.set_location(' Leaving: WATIF_ABSENCE_ATTENDANCES_API' , 10);
295 --
296 end;
297
298
299
300 procedure WATIF_CONTACT_RELATIONSHIP_API(
301 p_person_id in number
302 ,p_contact_person_id in number
303 ,p_DATE_START in date
304 ,p_DATE_END in date
305 ,p_CONTACT_TYPE in VARCHAR2
306 ,p_PERSONAL_FLAG in VARCHAR2
307 ,p_START_LIFE_REASON_ID in NUMBER
308 ,p_END_LIFE_REASON_ID in NUMBER
309 ,p_RLTD_PER_RSDS_W_DSGNTR_FLAG in VARCHAR2
310 ,p_business_group_id in number
311 ,p_effective_date in date
312 ) is
313 --
314 cursor c_ctr is
315 select
316 rowid, per_contact_relationships.*
317 from per_contact_relationships
318 where person_id = p_person_id and
319 contact_person_id = p_contact_person_id and
320 p_effective_date between nvl(DATE_START, p_effective_date)
321 and nvl(DATE_END, p_effective_date);
322 --
323 cursor c_per_typ is
324 select person_type_id
325 from per_person_types
326 where business_group_id = p_business_group_id
327 and system_PERSON_TYPE = 'OTHER';
328 --
329 l_person_type_id number;
330 --
331 per_ctr_rec c_ctr%rowtype;
332 --
333 l_contact_relationship_id number;
334 l_ctr_object_version_number number;
335 l_per_person_id number;
336 l_per_object_version_number number;
337 l_object_version_number number;
338 l_per_effective_start_date date;
339 l_per_effective_end_date date;
340 l_full_name per_all_people_f.full_name%type; --UTF8 Change Bug 2254683
341 l_per_comment_id number;
342 l_name_combination_warning boolean;
343 l_orig_hire_warning boolean;
344 --
345 l_personal_flag varchar2(1);
346 begin
347 --
348 hr_utility.set_location(' Entering: WATIF_CONTACT_RELATIONSHIP_API' , 10);
349 --
350 If p_DATE_START is null and p_DATE_END is null and
351 p_CONTACT_TYPE is null and p_PERSONAL_FLAG is null and
352 p_PERSONAL_FLAG is null and p_START_LIFE_REASON_ID is null and
353 p_END_LIFE_REASON_ID is null and p_RLTD_PER_RSDS_W_DSGNTR_FLAG is null
354 then
355 --
356 fnd_message.set_name('BEN','BEN_91447_ALL_NULL');
357 fnd_message.raise_error;
358 --
359 End If;
360 --
361 if p_contact_person_id is not null then
362 --
363 open c_ctr;
364 fetch c_ctr into per_ctr_rec;
365 if c_ctr%notfound Then
366 Close c_ctr;
367 --
368 -- Contact Relationship Record is Not found so error.
369 --
370 fnd_message.set_name('BEN','BEN_91441_NO_CTR');
371 fnd_message.raise_error;
372 --
373 End If;
374 Close c_ctr;
375 --
376 if p_date_start is not null then
377 per_ctr_rec.date_start := p_date_start;
378 end if;
379 --
380 if p_date_end is not null then
381 per_ctr_rec.date_end := p_date_end;
382 end if;
383
384 if p_CONTACT_TYPE is not null then
385 per_ctr_rec.CONTACT_TYPE := p_CONTACT_TYPE;
386 end if;
387
388 if p_PERSONAL_FLAG is not null then
389 per_ctr_rec.PERSONAL_FLAG := p_PERSONAL_FLAG;
390 end if;
391
392 if p_START_LIFE_REASON_ID is not null then
393 per_ctr_rec.START_LIFE_REASON_ID := p_START_LIFE_REASON_ID;
394 end if;
395
396 if p_END_LIFE_REASON_ID is not null then
397 per_ctr_rec.END_LIFE_REASON_ID := p_END_LIFE_REASON_ID;
398 end if;
399 --
400 -- Call update routine to update the contact relationship
401 --
402 --
403 -- Call the API - hr_contact_rel_api.update_contact_relationships.
404 --
405 /* update per_contact_relationships
406 set date_start = per_ctr_rec.date_start,
407 date_end = per_ctr_rec.date_end,
408 CONTACT_TYPE = per_ctr_rec.CONTACT_TYPE,
409 PERSONAL_FLAG = per_ctr_rec.PERSONAL_FLAG,
410 START_LIFE_REASON_ID = per_ctr_rec.START_LIFE_REASON_ID,
411 END_LIFE_REASON_ID = per_ctr_rec.END_LIFE_REASON_ID
412 where CONTACT_RELATIONSHIP_ID = per_ctr_rec.CONTACT_RELATIONSHIP_ID; */
413 --
414 hr_contact_rel_api.update_contact_relationship
415 (p_validate => false
416 ,p_effective_date => p_effective_date
417 ,p_contact_relationship_id => per_ctr_rec.contact_relationship_id
418 ,p_contact_type => per_ctr_rec.contact_type
419 ,p_comments => per_ctr_rec.comments
420 ,p_personal_flag => nvl(per_ctr_rec.personal_flag,'N')
421 ,p_dependent_flag => nvl(per_ctr_rec.dependent_flag,'N')
422 ,p_primary_contact_flag => nvl(per_ctr_rec.primary_contact_flag,'N')
423 ,p_third_party_pay_flag => nvl(per_ctr_rec.third_party_pay_flag,'N')
424 ,p_beneficiary_flag => nvl(per_ctr_rec.beneficiary_flag,'N')
425 ,p_bondholder_flag => nvl(per_ctr_rec.bondholder_flag,'N')
426 ,p_cont_attribute_category => per_ctr_rec.cont_attribute_category
427 ,p_cont_attribute1 => per_ctr_rec.cont_attribute1
428 ,p_cont_attribute2 => per_ctr_rec.cont_attribute2
429 ,p_cont_attribute3 => per_ctr_rec.cont_attribute3
430 ,p_cont_attribute4 => per_ctr_rec.cont_attribute4
431 ,p_cont_attribute5 => per_ctr_rec.cont_attribute5
432 ,p_cont_attribute6 => per_ctr_rec.cont_attribute6
433 ,p_cont_attribute7 => per_ctr_rec.cont_attribute7
434 ,p_cont_attribute8 => per_ctr_rec.cont_attribute8
435 ,p_cont_attribute9 => per_ctr_rec.cont_attribute9
436 ,p_cont_attribute10 => per_ctr_rec.cont_attribute10
437 ,p_cont_attribute11 => per_ctr_rec.cont_attribute11
438 ,p_cont_attribute12 => per_ctr_rec.cont_attribute12
439 ,p_cont_attribute13 => per_ctr_rec.cont_attribute13
440 ,p_cont_attribute14 => per_ctr_rec.cont_attribute14
441 ,p_cont_attribute15 => per_ctr_rec.cont_attribute15
442 ,p_cont_attribute16 => per_ctr_rec.cont_attribute16
443 ,p_cont_attribute17 => per_ctr_rec.cont_attribute17
444 ,p_cont_attribute18 => per_ctr_rec.cont_attribute18
445 ,p_cont_attribute19 => per_ctr_rec.cont_attribute19
446 ,p_cont_attribute20 => per_ctr_rec.cont_attribute20
447 ,p_object_version_number => l_object_version_number
448 );
449
450 /* PER_CONTACT_RELATIONSHIPS_PKG.Update_Row(
451 X_Rowid => rowidtochar(per_ctr_rec.Rowid),
452 X_Contact_Relationship_Id => per_ctr_rec.Contact_Relationship_Id,
453 X_Business_Group_Id => per_ctr_rec.Business_Group_Id,
454 X_Person_Id => per_ctr_rec.Person_Id,
455 X_Contact_Person_Id => per_ctr_rec.Contact_Person_Id,
456 X_Contact_Type => per_ctr_rec.Contact_Type,
457 X_Comments => per_ctr_rec.Comments,
458 X_Bondholder_Flag => per_ctr_rec.Bondholder_Flag,
459 X_Third_Party_Pay_Flag => per_ctr_rec.Third_Party_Pay_Flag,
460 X_Primary_Contact_Flag => per_ctr_rec.Primary_Contact_Flag,
461 X_Cont_Attribute_Category => per_ctr_rec.Cont_Attribute_Category,
462 X_Cont_Attribute1 => per_ctr_rec.Cont_Attribute1,
463 X_Cont_Attribute2 => per_ctr_rec.Cont_Attribute2,
464 X_Cont_Attribute3 => per_ctr_rec.Cont_Attribute3,
465 X_Cont_Attribute4 => per_ctr_rec.Cont_Attribute4,
466 X_Cont_Attribute5 => per_ctr_rec.Cont_Attribute5,
467 X_Cont_Attribute6 => per_ctr_rec.Cont_Attribute6,
468 X_Cont_Attribute7 => per_ctr_rec.Cont_Attribute7,
469 X_Cont_Attribute8 => per_ctr_rec.Cont_Attribute8,
470 X_Cont_Attribute9 => per_ctr_rec.Cont_Attribute9,
471 X_Cont_Attribute10 => per_ctr_rec.Cont_Attribute10,
472 X_Cont_Attribute11 => per_ctr_rec.Cont_Attribute11,
473 X_Cont_Attribute12 => per_ctr_rec.Cont_Attribute12,
474 X_Cont_Attribute13 => per_ctr_rec.Cont_Attribute13,
475 X_Cont_Attribute14 => per_ctr_rec.Cont_Attribute14,
476 X_Cont_Attribute15 => per_ctr_rec.Cont_Attribute15,
477 X_Cont_Attribute16 => per_ctr_rec.Cont_Attribute16,
478 X_Cont_Attribute17 => per_ctr_rec.Cont_Attribute17,
479 X_Cont_Attribute18 => per_ctr_rec.Cont_Attribute18,
480 X_Cont_Attribute19 => per_ctr_rec.Cont_Attribute19,
481 X_Cont_Attribute20 => per_ctr_rec.Cont_Attribute20,
482 X_Session_Date => p_effective_date, -- per_ctr_rec.Session_Date,
483 X_Person_Type_Id => per_ctr_rec.Person_Type_Id,
484 X_Last_Name => per_ctr_rec.Last_Name,
485 X_Comment_Id => per_ctr_rec.Comment_Id,
486 X_Date_Of_Birth => per_ctr_rec.Date_Of_Birth,
487 X_First_Name => per_ctr_rec.First_Name,
488 X_Middle_Names => per_ctr_rec.Middle_Names,
489 X_Sex => per_ctr_rec.Sex,
490 X_Title => per_ctr_rec.Title,
491 X_PRE_NAME_ADJUNCT => per_ctr_rec.PRE_NAME_ADJUNCT,
492 X_SUFFIX => per_ctr_rec.SUFFIX,
493 X_Title_Desc => per_ctr_rec.Title_Desc,
494 X_National_identifier => per_ctr_rec.National_identifier,
495 X_Attribute_Category => per_ctr_rec.Attribute_Category,
496 X_Attribute1 => per_ctr_rec.Attribute1,
497 X_Attribute2 => per_ctr_rec.Attribute2,
498 X_Attribute3 => per_ctr_rec.Attribute3,
499 X_Attribute4 => per_ctr_rec.Attribute4,
500 X_Attribute5 => per_ctr_rec.Attribute5,
501 X_Attribute6 => per_ctr_rec.Attribute6,
502 X_Attribute7 => per_ctr_rec.Attribute7,
503 X_Attribute8 => per_ctr_rec.Attribute8,
504 X_Attribute9 => per_ctr_rec.Attribute9,
505 X_Attribute10 => per_ctr_rec.Attribute10,
506 X_Attribute11 => per_ctr_rec.Attribute11,
507 X_Attribute12 => per_ctr_rec.Attribute12,
508 X_Attribute13 => per_ctr_rec.Attribute13,
509 X_Attribute14 => per_ctr_rec.Attribute14,
510 X_Attribute15 => per_ctr_rec.Attribute15,
511 X_Attribute16 => per_ctr_rec.Attribute16,
512 X_Attribute17 => per_ctr_rec.Attribute17,
513 X_Attribute18 => per_ctr_rec.Attribute18,
514 X_Attribute19 => per_ctr_rec.Attribute19,
515 X_Attribute20 => per_ctr_rec.Attribute20,
516 X_Attribute21 => per_ctr_rec.Attribute21,
517 X_Attribute22 => per_ctr_rec.Attribute22,
518 X_Attribute23 => per_ctr_rec.Attribute23,
519 X_Attribute24 => per_ctr_rec.Attribute24,
520 X_Attribute25 => per_ctr_rec.Attribute25,
521 X_Attribute26 => per_ctr_rec.Attribute26,
522 X_Attribute27 => per_ctr_rec.Attribute27,
523 X_Attribute28 => per_ctr_rec.Attribute28,
524 X_Attribute29 => per_ctr_rec.Attribute29,
525 X_Attribute30 => per_ctr_rec.Attribute30,
526 X_Contact_Only => per_ctr_rec.Contact_Only,
527 X_Reentry_Flag => per_ctr_rec.Reentry_Flag,
528 X_Per_Information1 => per_ctr_rec.Per_Information1,
529 X_Per_Information2 => per_ctr_rec.Per_Information2,
530 X_Per_Information3 => per_ctr_rec.Per_Information3,
531 X_Per_Information4 => per_ctr_rec.Per_Information4,
532 X_Per_Information5 => per_ctr_rec.Per_Information5,
533 X_Per_Information6 => per_ctr_rec.Per_Information6,
534 X_Per_Information7 => per_ctr_rec.Per_Information7,
535 X_Per_Information8 => per_ctr_rec.Per_Information8,
536 X_Per_Information9 => per_ctr_rec.Per_Information9,
537 X_Per_Information10 => per_ctr_rec.Per_Information10,
538 X_Per_Information11 => per_ctr_rec.Per_Information11,
539 X_Per_Information12 => per_ctr_rec.Per_Information12,
540 X_Per_Information13 => per_ctr_rec.Per_Information13,
541 X_Per_Information14 => per_ctr_rec.Per_Information14,
542 X_Per_Information15 => per_ctr_rec.Per_Information15,
543 X_Per_Information16 => per_ctr_rec.Per_Information16,
544 X_Per_Information17 => per_ctr_rec.Per_Information17,
545 X_Per_Information18 => per_ctr_rec.Per_Information18,
546 X_Per_Information19 => per_ctr_rec.Per_Information19,
547 X_Per_Information20 => per_ctr_rec.Per_Information20,
548 X_Per_Information21 => per_ctr_rec.Per_Information21,
549 X_Per_Information22 => per_ctr_rec.Per_Information22,
550 X_Per_Information23 => per_ctr_rec.Per_Information23,
551 X_Per_Information24 => per_ctr_rec.Per_Information24,
552 X_Per_Information25 => per_ctr_rec.Per_Information25,
553 X_Per_Information26 => per_ctr_rec.Per_Information26,
554 X_Per_Information27 => per_ctr_rec.Per_Information27,
555 X_Per_Information28 => per_ctr_rec.Per_Information28,
556 X_Per_Information29 => per_ctr_rec.Per_Information29,
557 X_Per_Information30 => per_ctr_rec.Per_Information30,
558 X_Known_As => per_ctr_rec.Known_As,
559 X_Date_Start => per_ctr_rec.Date_Start,
560 X_Start_Life_Reason_Id => per_ctr_rec.Start_Life_Reason_Id,
561 X_Date_End => per_ctr_rec.Date_End,
562 X_End_Life_Reason_Id => per_ctr_rec.End_Life_Reason_Id,
563 X_Rltd_Per_Rsds_W_Dsgntr_Flag => per_ctr_rec.Rltd_Per_Rsds_W_Dsgntr_Flag
564 ); */
565 else
566 --
567 -- Create a contact and contact relationship record.
568 --
569 open c_per_typ;
570 fetch c_per_typ into l_person_type_id;
571 close c_per_typ;
572
573 if p_contact_type in ('P','C','S') then
574 l_personal_flag := 'Y' ;
575 else
576 l_personal_flag := nvl(p_personal_flag , 'N' );
577 end if;
578
579 hr_contact_rel_api.create_contact
580 (
581 p_validate => false
582 ,p_start_date => p_date_start
583 ,p_business_group_id => p_business_group_id
584 ,p_person_id => p_person_id
585 ,p_contact_person_id => null
586 ,p_contact_type => p_contact_type
587 ,p_ctr_comments => null
588 ,p_primary_contact_flag => 'N' -- null
589 ,p_date_start => p_date_start
590 ,p_start_life_reason_id => p_start_life_reason_id
591 ,p_date_end => p_date_end
592 ,p_end_life_reason_id => p_end_life_reason_id
593 ,p_rltd_per_rsds_w_dsgntr_flag => nvl(p_RLTD_PER_RSDS_W_DSGNTR_FLAG,'N')
594 ,p_personal_flag => l_personal_flag
595 ,p_sequence_number => null
596 ,p_cont_attribute_category => null
597 ,p_cont_attribute1 => null
598 ,p_cont_attribute2 => null
599 ,p_cont_attribute3 => null
600 ,p_cont_attribute4 => null
601 ,p_cont_attribute5 => null
602 ,p_cont_attribute6 => null
603 ,p_cont_attribute7 => null
604 ,p_cont_attribute8 => null
605 ,p_cont_attribute9 => null
606 ,p_cont_attribute10 => null
607 ,p_cont_attribute11 => null
608 ,p_cont_attribute12 => null
609 ,p_cont_attribute13 => null
610 ,p_cont_attribute14 => null
611 ,p_cont_attribute15 => null
612 ,p_cont_attribute16 => null
613 ,p_cont_attribute17 => null
614 ,p_cont_attribute18 => null
615 ,p_cont_attribute19 => null
616 ,p_cont_attribute20 => null
617 ,p_third_party_pay_flag => 'N'
618 ,p_bondholder_flag => 'N'
619 ,p_dependent_flag => 'Y'
620 ,p_beneficiary_flag => 'N'
621 ,p_last_name => 'Last 1234'
622 ,p_sex => null
623 ,p_person_type_id => l_person_type_id
624 ,p_per_comments => null
625 ,p_date_of_birth => null
626 ,p_email_address => null
627 ,p_first_name => 'First 1234'
628 ,p_known_as => null
629 ,p_marital_status => null
630 ,p_middle_names => null
631 ,p_nationality => null
632 ,p_national_identifier => null
633 ,p_previous_last_name => null
634 ,p_registered_disabled_flag => null
635 ,p_title => null
636 ,p_work_telephone => null
637 ,p_attribute_category => null
638 ,p_attribute1 => null
639 ,p_attribute2 => null
640 ,p_attribute3 => null
641 ,p_attribute4 => null
642 ,p_attribute5 => null
643 ,p_attribute6 => null
644 ,p_attribute7 => null
645 ,p_attribute8 => null
646 ,p_attribute9 => null
647 ,p_attribute10 => null
648 ,p_attribute11 => null
649 ,p_attribute12 => null
650 ,p_attribute13 => null
651 ,p_attribute14 => null
652 ,p_attribute15 => null
653 ,p_attribute16 => null
654 ,p_attribute17 => null
655 ,p_attribute18 => null
656 ,p_attribute19 => null
657 ,p_attribute20 => null
658 ,p_attribute21 => null
659 ,p_attribute22 => null
660 ,p_attribute23 => null
661 ,p_attribute24 => null
662 ,p_attribute25 => null
663 ,p_attribute26 => null
664 ,p_attribute27 => null
665 ,p_attribute28 => null
666 ,p_attribute29 => null
667 ,p_attribute30 => null
668 ,p_per_information_category => null
669 ,p_per_information1 => null
670 ,p_per_information2 => null
671 ,p_per_information3 => null
672 ,p_per_information4 => null
673 ,p_per_information5 => null
674 ,p_per_information6 => null
675 ,p_per_information7 => null
676 ,p_per_information8 => null
677 ,p_per_information9 => null
678 ,p_per_information10 => null
679 ,p_per_information11 => null
680 ,p_per_information12 => null
681 ,p_per_information13 => null
682 ,p_per_information14 => null
683 ,p_per_information15 => null
684 ,p_per_information16 => null
685 ,p_per_information17 => null
686 ,p_per_information18 => null
687 ,p_per_information19 => null
688 ,p_per_information20 => null
689 ,p_per_information21 => null
690 ,p_per_information22 => null
691 ,p_per_information23 => null
692 ,p_per_information24 => null
693 ,p_per_information25 => null
694 ,p_per_information26 => null
695 ,p_per_information27 => null
696 ,p_per_information28 => null
697 ,p_per_information29 => null
698 ,p_per_information30 => null
699 ,p_correspondence_language => null
700 ,p_honors => null
701 ,p_pre_name_adjunct => null
702 ,p_suffix => null
703 ,p_create_mirror_flag => null
704 ,p_mirror_type => null
705 ,p_contact_relationship_id => l_contact_relationship_id
706 ,p_ctr_object_version_number => l_ctr_object_version_number
707 ,p_per_person_id => l_per_person_id
708 ,p_per_object_version_number => l_per_object_version_number
709 ,p_per_effective_start_date => l_per_effective_start_date
710 ,p_per_effective_end_date => l_per_effective_end_date
711 ,p_full_name => l_full_name
712 ,p_per_comment_id => l_per_comment_id
713 ,p_name_combination_warning => l_name_combination_warning
714 ,p_orig_hire_warning => l_orig_hire_warning
715 );
716 end if;
717 --
718 hr_utility.set_location(' Leaving: WATIF_CONTACT_RELATIONSHIP_API' , 10);
719 --
720 end;
721
722 procedure WATIF_ADDRESSES_API(
723 p_person_id in number
724 ,p_POSTAL_CODE in varchar2
725 ,p_PRIMARY_FLAG in varchar2
726 ,p_REGION_2 in varchar2
727 ,p_ADDRESS_TYPE in varchar2
728 ,p_DATE_FROM in date
729 ,p_DATE_TO in date
730 ,p_effective_date in date
731 ) is
732 --
733
734 --
735 cursor c_addr is
736 select * from per_addresses_v
737 where person_id = p_person_id and
738 p_effective_date between nvl(DATE_FROM, p_effective_date)
739 and nvl(DATE_TO, p_effective_date) and
740 PRIMARY_FLAG = 'Y';
741 --
742 --
743 cursor csr_valid_city_state is
744 select st.state_abbrev, ct.city_name, county.county_name
745 from pay_us_states st,
746 pay_us_counties county,
747 pay_us_city_names ct,
748 pay_us_zip_codes zip
749 where
750 zip.state_code = st.state_code and
751 county.state_code = zip.state_code and
752 county.county_code = zip.county_code and
753 ct.state_code = zip.state_code and
754 ct.county_code = zip.county_code and
755 ct.city_code = zip.city_code and
756 p_postal_code between zip.zip_start and zip.zip_end and
757 ct.primary_flag = 'Y';
758 --
759 --
760 per_addr_rec c_addr%rowtype;
761
762 l_geocodes_installed VARCHAR2(1) := 'N';
763 city_state_rec csr_valid_city_state%rowtype;
764
765 l_primary_flag VARCHAR2(1); --Bug 2856143
766
767 --
768 begin
769 --
770 hr_utility.set_location(' Entering: WATIF_ADDRESSES_API' , 10);
771 -- If postal code is null ????
772 If p_postal_code is null and p_PRIMARY_FLAG is null and
773 p_DATE_FROM is null and p_DATE_TO is null and
774 p_REGION_2 is null and p_ADDRESS_TYPE is null
775 then
776 --
777 fnd_message.set_name('BEN','BEN_91447_ALL_NULL');
778 fnd_message.raise_error;
779 --
780 End If;
781 --
782 open c_addr;
783 fetch c_addr into per_addr_rec;
784 if c_addr%notfound Then
785 Close c_addr;
786 --
787 -- Address Not found so error.
788 --
789 fnd_message.set_name('BEN','BEN_91442_NO_ADDRESS');
790 fnd_message.raise_error;
791 --
792 End If;
793 Close c_addr;
794
795 if p_primary_flag is not null then
796 per_addr_rec.primary_flag := p_primary_flag;
797 end if;
798 --
799 if p_REGION_2 is not null then
800 per_addr_rec.REGION_2 := p_REGION_2;
801 end if;
802 --
803 if p_ADDRESS_TYPE is not null then
804 per_addr_rec.ADDRESS_TYPE := p_ADDRESS_TYPE;
805 end if;
806 --
807 if p_date_from is not null then
808 per_addr_rec.date_from := p_date_from;
809 end if;
810 --
811 if p_date_to is not null then
812 per_addr_rec.date_to := p_date_to;
813 end if;
814
815 -- Bug 3257985
816 if p_date_from is null and p_date_to is null then
817 per_addr_rec.date_from := greatest(per_addr_rec.date_from,p_effective_date);
818 end if;
819 -- Bug 3257985
820 --
821 -- Check if North American Geocides installed.
822 --
823 l_geocodes_installed := hr_general.chk_geocodes_installed;
824 --
825 --
826 -- If US address style and GEOCODES is installed,
827 -- validate for right combination of
828 -- city, state and county.
829 --
830
831
832 if (per_addr_rec.style in ( 'US','CA')
833 and l_geocodes_installed = 'Y')
834 then
835 --
836 if ( p_postal_code is not null and
837 p_postal_code <> per_addr_rec.postal_code )
838 then
839 --
840 -- Select the City, State for the postal code.
841 --
842 open csr_valid_city_state;
843 fetch csr_valid_city_state into city_state_rec;
844 if csr_valid_city_state%notfound Then
845 Close csr_valid_city_state;
846 --
847 -- City State Not Found.
848 --
849 fnd_message.set_name('BEN', 'BEN_91448_NO_CT_ST_CNTY');
850 fnd_message.raise_error;
851 End If;
852 Close csr_valid_city_state;
853 --
854 -- Assign the fetched values to the Record fields.
855 --
856 per_addr_rec.postal_code := p_postal_code;
857 per_addr_rec.REGION_2 := nvl(p_REGION_2,city_state_rec.STATE_ABBREV);
858 per_addr_rec.TOWN_OR_CITY := city_state_rec.city_name;
859 per_addr_rec.region_1 := city_state_rec.county_name;
860 --
861 end if;
862 --
863 --
864 --
865
866 --Bug 2856143 New api being used
867
868 per_addresses_pkg.update_row
869 (p_row_id => per_addr_rec.ROW_ID
870 ,p_address_id => per_addr_rec.ADDRESS_ID
871 ,p_business_group_id => per_addr_rec.BUSINESS_GROUP_ID
872 ,p_person_id => p_person_id
873 ,p_date_from => per_addr_rec.DATE_FROM
874 ,p_primary_flag => per_addr_rec.PRIMARY_FLAG
875 ,p_style => per_addr_rec.STYLE
876 ,p_address_line1 => per_addr_rec.ADDRESS_LINE1
877 ,p_address_line2 => per_addr_rec.ADDRESS_LINE2
878 ,p_address_line3 => per_addr_rec.ADDRESS_LINE3
879 ,p_address_type => per_addr_rec.ADDRESS_TYPE
880 ,p_comments => per_addr_rec.COMMENTS
881 ,p_country => per_addr_rec.COUNTRY
882 ,p_date_to => per_addr_rec.DATE_TO
883 ,p_postal_code => per_addr_rec.POSTAL_CODE
884 ,p_region_1 => per_addr_rec.REGION_1
885 ,p_region_2 => per_addr_rec.REGION_2
886 ,p_region_3 => per_addr_rec.REGION_3
887 ,p_telephone_number_1 => per_addr_rec.TELEPHONE_NUMBER_1
888 ,p_telephone_number_2 => per_addr_rec.TELEPHONE_NUMBER_2
889 ,p_telephone_number_3 => per_addr_rec.TELEPHONE_NUMBER_3
890 ,p_town_or_city => per_addr_rec.TOWN_OR_CITY
891 ,p_request_id => per_addr_rec.REQUEST_ID
892 ,p_program_application_id => per_addr_rec.PROGRAM_APPLICATION_ID
893 ,p_program_id => per_addr_rec.PROGRAM_ID
894 ,p_program_update_date => per_addr_rec.PROGRAM_UPDATE_DATE
895 ,p_addr_attribute_category=> per_addr_rec.ADDR_ATTRIBUTE_CATEGORY
896 ,p_addr_attribute1 => per_addr_rec.ADDR_ATTRIBUTE1
897 ,p_addr_attribute2 => per_addr_rec.ADDR_ATTRIBUTE2
898 ,p_addr_attribute3 => per_addr_rec.ADDR_ATTRIBUTE3
899 ,p_addr_attribute4 => per_addr_rec.ADDR_ATTRIBUTE4
900 ,p_addr_attribute5 => per_addr_rec.ADDR_ATTRIBUTE5
901 ,p_addr_attribute6 => per_addr_rec.ADDR_ATTRIBUTE6
902 ,p_addr_attribute7 => per_addr_rec.ADDR_ATTRIBUTE7
903 ,p_addr_attribute8 => per_addr_rec.ADDR_ATTRIBUTE8
904 ,p_addr_attribute9 => per_addr_rec.ADDR_ATTRIBUTE9
905 ,p_addr_attribute10 => per_addr_rec.ADDR_ATTRIBUTE10
906 ,p_addr_attribute11 => per_addr_rec.ADDR_ATTRIBUTE11
907 ,p_addr_attribute12 => per_addr_rec.ADDR_ATTRIBUTE12
908 ,p_addr_attribute13 => per_addr_rec.ADDR_ATTRIBUTE13
909 ,p_addr_attribute14 => per_addr_rec.ADDR_ATTRIBUTE14
910 ,p_addr_attribute15 => per_addr_rec.ADDR_ATTRIBUTE15
911 ,p_addr_attribute16 => per_addr_rec.ADDR_ATTRIBUTE16
912 ,p_addr_attribute17 => per_addr_rec.ADDR_ATTRIBUTE17
913 ,p_addr_attribute18 => per_addr_rec.ADDR_ATTRIBUTE18
914 ,p_addr_attribute19 => per_addr_rec.ADDR_ATTRIBUTE19
915 ,p_addr_attribute20 => per_addr_rec.ADDR_ATTRIBUTE20
916 ,p_add_information17 => per_addr_rec.ADD_INFORMATION17
917 ,p_add_information18 => per_addr_rec.ADD_INFORMATION18
918 ,p_add_information19 => per_addr_rec.ADD_INFORMATION19
919 ,p_add_information20 => per_addr_rec.ADD_INFORMATION20
920 ,p_default_primary => l_primary_flag
921 );
922
923
924 --Bug 2856143 APi used now is the one being used on the form PERACADR.fmb
925 -- Call the API - update_us_person_address.
926 /* hr_person_address_api.update_us_person_address
927 (p_validate => FALSE
928 ,p_effective_date => p_effective_date
929 ,p_address_id =>per_addr_rec.address_id
930 ,p_object_version_number =>per_addr_rec.object_version_number
931 ,p_date_from =>per_addr_rec.date_from
932 ,p_date_to =>per_addr_rec.date_to
933 ,p_address_type =>per_addr_rec.address_type
934 ,p_comments =>per_addr_rec.comments
935 ,p_address_line1 =>per_addr_rec.address_line1
936 ,p_address_line2 =>per_addr_rec.address_line2
937 ,p_address_line3 =>per_addr_rec.address_line3
938 ,p_city =>per_addr_rec.TOWN_OR_CITY
939 ,p_state =>per_addr_rec.region_2
940 ,p_zip_code =>per_addr_rec.postal_code
941 ,p_county =>per_addr_rec.region_1
942 ,p_country =>per_addr_rec.country
943 ,p_telephone_number_1 =>per_addr_rec.telephone_number_1
944 ,p_telephone_number_2 =>per_addr_rec.telephone_number_2
945 ,p_addr_attribute_category =>per_addr_rec.addr_attribute_category
946 ,p_addr_attribute1 =>per_addr_rec.addr_attribute1
947 ,p_addr_attribute2 =>per_addr_rec.addr_attribute2
948 ,p_addr_attribute3 =>per_addr_rec.addr_attribute3
949 ,p_addr_attribute4 =>per_addr_rec.addr_attribute4
950 ,p_addr_attribute5 =>per_addr_rec.addr_attribute5
951 ,p_addr_attribute6 =>per_addr_rec.addr_attribute6
952 ,p_addr_attribute7 =>per_addr_rec.addr_attribute7
953 ,p_addr_attribute8 =>per_addr_rec.addr_attribute8
954 ,p_addr_attribute9 =>per_addr_rec.addr_attribute9
955 ,p_addr_attribute10 =>per_addr_rec.addr_attribute10
956 ,p_addr_attribute11 =>per_addr_rec.addr_attribute11
957 ,p_addr_attribute12 =>per_addr_rec.addr_attribute12
958 ,p_addr_attribute13 =>per_addr_rec.addr_attribute13
959 ,p_addr_attribute14 =>per_addr_rec.addr_attribute14
960 ,p_addr_attribute15 =>per_addr_rec.addr_attribute15
961 ,p_addr_attribute16 =>per_addr_rec.addr_attribute16
962 ,p_addr_attribute17 =>per_addr_rec.addr_attribute17
963 ,p_addr_attribute18 =>per_addr_rec.addr_attribute18
964 ,p_addr_attribute19 =>per_addr_rec.addr_attribute19
965 ,p_addr_attribute20 =>per_addr_rec.addr_attribute20
966 );
967 else
968 hr_person_address_api.update_person_address
969 (p_validate => FALSE
970 ,p_effective_date => p_effective_date
971 ,p_address_id =>per_addr_rec.address_id
972 ,p_object_version_number =>per_addr_rec.object_version_number
973 ,p_date_from =>per_addr_rec.date_from
974 ,p_date_to =>per_addr_rec.date_to
975 ,p_address_type =>per_addr_rec.address_type
976 ,p_comments =>per_addr_rec.comments
977 ,p_address_line1 =>per_addr_rec.address_line1
978 ,p_address_line2 =>per_addr_rec.address_line2
979 ,p_address_line3 =>per_addr_rec.address_line3
980 ,p_town_or_city =>per_addr_rec.TOWN_OR_CITY
981 ,p_region_1 =>per_addr_rec.region_1
982 ,p_region_2 =>per_addr_rec.region_2
983 ,p_region_3 =>per_addr_rec.region_3
984 ,p_postal_code =>per_addr_rec.postal_code
985 ,p_country =>per_addr_rec.country
986 ,p_telephone_number_1 =>per_addr_rec.telephone_number_1
987 ,p_telephone_number_2 =>per_addr_rec.telephone_number_2
988 ,p_telephone_number_3 =>per_addr_rec.telephone_number_3
989 ,p_addr_attribute_category =>per_addr_rec.addr_attribute_category
990 ,p_addr_attribute1 =>per_addr_rec.addr_attribute1
991 ,p_addr_attribute2 =>per_addr_rec.addr_attribute2
992 ,p_addr_attribute3 =>per_addr_rec.addr_attribute3
993 ,p_addr_attribute4 =>per_addr_rec.addr_attribute4
994 ,p_addr_attribute5 =>per_addr_rec.addr_attribute5
995 ,p_addr_attribute6 =>per_addr_rec.addr_attribute6
996 ,p_addr_attribute7 =>per_addr_rec.addr_attribute7
997 ,p_addr_attribute8 =>per_addr_rec.addr_attribute8
998 ,p_addr_attribute9 =>per_addr_rec.addr_attribute9
999 ,p_addr_attribute10 =>per_addr_rec.addr_attribute10
1000 ,p_addr_attribute11 =>per_addr_rec.addr_attribute11
1001 ,p_addr_attribute12 =>per_addr_rec.addr_attribute12
1002 ,p_addr_attribute13 =>per_addr_rec.addr_attribute13
1003 ,p_addr_attribute14 =>per_addr_rec.addr_attribute14
1004 ,p_addr_attribute15 =>per_addr_rec.addr_attribute15
1005 ,p_addr_attribute16 =>per_addr_rec.addr_attribute16
1006 ,p_addr_attribute17 =>per_addr_rec.addr_attribute17
1007 ,p_addr_attribute18 =>per_addr_rec.addr_attribute18
1008 ,p_addr_attribute19 =>per_addr_rec.addr_attribute19
1009 ,p_addr_attribute20 =>per_addr_rec.addr_attribute20
1010 ); */ --Bug 2856143 APi used now is the one being used on the form PERACADR.fmb
1011 End If;
1012 --
1013 hr_utility.set_location(' Leaving: WATIF_ADDRESSES_API' , 10);
1014 end;
1015
1016
1017
1018 procedure WATIF_ALL_ASSIGNMENTS_F_API(
1019 p_person_id in number
1020 ,p_PAY_BASIS_ID in number
1021 ,p_EMPLOYMENT_CATEGORY in varchar2
1022 ,p_LABOUR_UNION_MEMBER_FLAG in varchar2
1023 ,p_JOB_ID in number
1024 ,p_PAYROLL_ID in number
1025 ,p_PRIMARY_FLAG in varchar2
1026 ,p_LOCATION_ID in number
1027 ,p_CHANGE_REASON in varchar2
1028 ,p_ASSIGNMENT_TYPE in varchar2
1029 ,p_ORGANIZATION_ID in number
1030 ,p_POSITION_ID in number
1031 ,p_BARGAINING_UNIT_CODE in varchar2
1032 ,p_NORMAL_HOURS in number
1033 ,p_FREQUENCY in varchar2
1034 ,p_ASSIGNMENT_STATUS_TYPE_ID in number
1035 ,p_GRADE_ID in number
1036 ,p_PEOPLE_GROUP_ID in NUMBER
1037 ,p_HOURLY_SALARIED_CODE in varchar2
1038 ,p_ASS_ATTRIBUTE_CATEGORY in varchar2
1039 ,p_ASS_ATTRIBUTE1 in VARCHAR2
1040 ,p_ASS_ATTRIBUTE10 in VARCHAR2
1041 ,p_ASS_ATTRIBUTE11 in VARCHAR2
1042 ,p_ASS_ATTRIBUTE12 in VARCHAR2
1043 ,p_ASS_ATTRIBUTE13 in VARCHAR2
1044 ,p_ASS_ATTRIBUTE14 in VARCHAR2
1045 ,p_ASS_ATTRIBUTE15 in VARCHAR2
1046 ,p_ASS_ATTRIBUTE16 in VARCHAR2
1047 ,p_ASS_ATTRIBUTE17 in VARCHAR2
1048 ,p_ASS_ATTRIBUTE18 in VARCHAR2
1049 ,p_ASS_ATTRIBUTE19 in VARCHAR2
1050 ,p_ASS_ATTRIBUTE2 in VARCHAR2
1051 ,p_ASS_ATTRIBUTE20 in VARCHAR2
1052 ,p_ASS_ATTRIBUTE21 in VARCHAR2
1053 ,p_ASS_ATTRIBUTE22 in VARCHAR2
1054 ,p_ASS_ATTRIBUTE23 in VARCHAR2
1055 ,p_ASS_ATTRIBUTE24 in VARCHAR2
1056 ,p_ASS_ATTRIBUTE25 in VARCHAR2
1057 ,p_ASS_ATTRIBUTE26 in VARCHAR2
1058 ,p_ASS_ATTRIBUTE27 in VARCHAR2
1059 ,p_ASS_ATTRIBUTE28 in VARCHAR2
1060 ,p_ASS_ATTRIBUTE29 in VARCHAR2
1061 ,p_ASS_ATTRIBUTE3 in VARCHAR2
1062 ,p_ASS_ATTRIBUTE30 in VARCHAR2
1063 ,p_ASS_ATTRIBUTE4 in VARCHAR2
1064 ,p_ASS_ATTRIBUTE5 in VARCHAR2
1065 ,p_ASS_ATTRIBUTE6 in VARCHAR2
1066 ,p_ASS_ATTRIBUTE7 in VARCHAR2
1067 ,p_ASS_ATTRIBUTE8 in VARCHAR2
1068 ,p_ASS_ATTRIBUTE9 in VARCHAR2
1069 ,p_business_group_id in number
1070 ,p_effective_date in date
1071 ) is
1072 --
1073 per_assign_rec per_all_assignments_f%rowtype;
1074 --
1075 cursor c_assign is
1076 select * from per_all_assignments_f
1077 where person_id = p_person_id and
1078 assignment_type <> 'C' and
1079 p_effective_date between nvl(effective_start_date, p_effective_date)
1080 and nvl(effective_end_date, p_effective_date)
1081 and primary_flag = 'Y';
1082 --
1083 l_soft_coding_keyflex_id number;
1084 l_comment_id number;
1085 l_effective_start_date date;
1086 l_effective_end_date date;
1087 l_concatenated_segments varchar2(100);
1088 l_no_managers_warning boolean;
1089 l_other_manager_warning boolean;
1090 l_upd_mode varchar2(25);
1091 l_upd_extra_asg_flag boolean;
1092 --
1093 l_group_name varchar2(500); -- UTF8 Change Bug 2254683
1094 l_asg_effective_start_date date;
1095 l_asg_effective_end_date date;
1096 l_people_group_id number;
1097 l_org_now_no_manager_warning boolean;
1098 l_spp_delete_warning boolean;
1099 l_entries_changed_warning varchar2(30);
1100 l_tax_district_changed_warning boolean;
1101 l_special_ceiling_step_id per_all_assignments_f.special_ceiling_step_id%type;
1102 --
1103 begin
1104 --
1105 hr_utility.set_location(' Entering: WATIF_ALL_ASSIGNMENTS_F_API' , 10);
1106 --
1107 If p_PAY_BASIS_ID is null and
1108 p_EMPLOYMENT_CATEGORY is null and
1109 p_JOB_ID is null and
1110 p_PAYROLL_ID is null and
1111 p_PRIMARY_FLAG is null and
1112 p_LOCATION_ID is null and
1113 p_ASSIGNMENT_TYPE is null and
1114 p_ORGANIZATION_ID is null and
1115 p_POSITION_ID is null and
1116 p_GRADE_ID is null and
1117 p_ASSIGNMENT_STATUS_TYPE_ID is null and
1118 p_PEOPLE_GROUP_ID is null and
1119 p_LABOUR_UNION_MEMBER_FLAG is null and
1120 p_CHANGE_REASON is null and
1121 p_BARGAINING_UNIT_CODE is null and
1122 p_NORMAL_HOURS is null and
1123 p_FREQUENCY is null and
1124 p_HOURLY_SALARIED_CODE is null and
1125 p_ASS_ATTRIBUTE_CATEGORY is null and
1126 p_ASS_ATTRIBUTE1 is null and
1127 p_ASS_ATTRIBUTE10 is null and
1128 p_ASS_ATTRIBUTE11 is null and
1129 p_ASS_ATTRIBUTE12 is null and
1130 p_ASS_ATTRIBUTE13 is null and
1131 p_ASS_ATTRIBUTE14 is null and
1132 p_ASS_ATTRIBUTE15 is null and
1133 p_ASS_ATTRIBUTE16 is null and
1134 p_ASS_ATTRIBUTE17 is null and
1135 p_ASS_ATTRIBUTE18 is null and
1136 p_ASS_ATTRIBUTE19 is null and
1137 p_ASS_ATTRIBUTE2 is null and
1138 p_ASS_ATTRIBUTE20 is null and
1139 p_ASS_ATTRIBUTE21 is null and
1140 p_ASS_ATTRIBUTE22 is null and
1141 p_ASS_ATTRIBUTE23 is null and
1142 p_ASS_ATTRIBUTE24 is null and
1143 p_ASS_ATTRIBUTE25 is null and
1144 p_ASS_ATTRIBUTE26 is null and
1145 p_ASS_ATTRIBUTE27 is null and
1146 p_ASS_ATTRIBUTE28 is null and
1147 p_ASS_ATTRIBUTE29 is null and
1148 p_ASS_ATTRIBUTE3 is null and
1149 p_ASS_ATTRIBUTE30 is null and
1150 p_ASS_ATTRIBUTE4 is null and
1151 p_ASS_ATTRIBUTE5 is null and
1152 p_ASS_ATTRIBUTE6 is null and
1153 p_ASS_ATTRIBUTE7 is null and
1154 p_ASS_ATTRIBUTE8 is null and
1155 p_ASS_ATTRIBUTE9 is null
1156 then
1157 --
1158 fnd_message.set_name('BEN','BEN_91447_ALL_NULL');
1159 fnd_message.raise_error;
1160 --
1161 End If;
1162 --
1163 if p_LABOUR_UNION_MEMBER_FLAG is not null or
1164 p_CHANGE_REASON is not null or
1165 p_BARGAINING_UNIT_CODE is not null or
1166 p_NORMAL_HOURS is not null or
1167 p_FREQUENCY is not null or
1168 p_HOURLY_SALARIED_CODE is not null or
1169 p_ASS_ATTRIBUTE_CATEGORY is not null or
1170 p_ASS_ATTRIBUTE1 is not null or
1171 p_ASS_ATTRIBUTE10 is not null or
1172 p_ASS_ATTRIBUTE11 is not null or
1173 p_ASS_ATTRIBUTE12 is not null or
1174 p_ASS_ATTRIBUTE13 is not null or
1175 p_ASS_ATTRIBUTE14 is not null or
1176 p_ASS_ATTRIBUTE15 is not null or
1177 p_ASS_ATTRIBUTE16 is not null or
1178 p_ASS_ATTRIBUTE17 is not null or
1179 p_ASS_ATTRIBUTE18 is not null or
1180 p_ASS_ATTRIBUTE19 is not null or
1181 p_ASS_ATTRIBUTE2 is not null or
1182 p_ASS_ATTRIBUTE20 is not null or
1183 p_ASS_ATTRIBUTE21 is not null or
1184 p_ASS_ATTRIBUTE22 is not null or
1185 p_ASS_ATTRIBUTE23 is not null or
1186 p_ASS_ATTRIBUTE24 is not null or
1187 p_ASS_ATTRIBUTE25 is not null or
1188 p_ASS_ATTRIBUTE26 is not null or
1189 p_ASS_ATTRIBUTE27 is not null or
1190 p_ASS_ATTRIBUTE28 is not null or
1191 p_ASS_ATTRIBUTE29 is not null or
1192 p_ASS_ATTRIBUTE3 is not null or
1193 p_ASS_ATTRIBUTE30 is not null or
1194 p_ASS_ATTRIBUTE4 is not null or
1195 p_ASS_ATTRIBUTE5 is not null or
1196 p_ASS_ATTRIBUTE6 is not null or
1197 p_ASS_ATTRIBUTE7 is not null or
1198 p_ASS_ATTRIBUTE8 is not null or
1199 p_ASS_ATTRIBUTE9 is not null
1200 then
1201 l_upd_extra_asg_flag := TRUE;
1202 end if;
1203 --
1204 If p_PAY_BASIS_ID is not null or
1205 p_EMPLOYMENT_CATEGORY is not null or
1206 p_JOB_ID is not null or
1207 p_PAYROLL_ID is not null or
1208 p_PRIMARY_FLAG is not null or
1209 p_LOCATION_ID is not null or
1210 p_ASSIGNMENT_TYPE is not null or
1211 p_ORGANIZATION_ID is not null or
1212 p_POSITION_ID is not null or
1213 p_GRADE_ID is not null or
1214 p_ASSIGNMENT_STATUS_TYPE_ID is not null or
1215 p_PEOPLE_GROUP_ID is not null
1216 then
1217 --
1218 -- Use hr_assignment_api.update_emp_asg_criteria to update
1219 -- above data.
1220 --
1221 hr_utility.set_location('p_PAY_BASIS_ID = ' || p_PAY_BASIS_ID, 999);
1222 hr_utility.set_location('p_EMPLOYMENT_CATEGORY = ' || p_EMPLOYMENT_CATEGORY, 999);
1223 hr_utility.set_location('p_JOB_ID = ' || p_JOB_ID, 999);
1224 hr_utility.set_location('p_PAYROLL_ID = ' || p_PAYROLL_ID, 999);
1225 hr_utility.set_location('p_PRIMARY_FLAG = ' || p_PRIMARY_FLAG, 999);
1226 hr_utility.set_location('p_LOCATION_ID = ' || p_LOCATION_ID, 999);
1227 hr_utility.set_location('p_ASSIGNMENT_TYPE = ' || p_ASSIGNMENT_TYPE, 999);
1228 hr_utility.set_location('p_ORGANIZATION_ID = ' || p_ORGANIZATION_ID, 999);
1229 hr_utility.set_location('p_POSITION_ID = ' || p_POSITION_ID, 999);
1230 hr_utility.set_location('p_GRADE_ID = ' || p_GRADE_ID, 999);
1231 hr_utility.set_location('p_ASSIGNMENT_STATUS_TYPE_ID = ' || p_ASSIGNMENT_STATUS_TYPE_ID, 999);
1232 hr_utility.set_location('p_PEOPLE_GROUP_ID = ' || p_PEOPLE_GROUP_ID, 999);
1233 --
1234 open c_assign;
1235 fetch c_assign into per_assign_rec;
1236 if c_assign%notfound Then
1237 Close c_assign;
1238 --
1239 -- Assignment Record is Not found so error.
1240 --
1241 fnd_message.set_name('BEN','BEN_91443_NO_ASSGN');
1242 fnd_message.raise_error;
1243 --
1244 End If;
1245 Close c_assign;
1246 --
1247 if p_PAY_BASIS_ID is not null then
1248 per_assign_rec.PAY_BASIS_ID := p_PAY_BASIS_ID;
1249 end if;
1250 --
1251 if p_EMPLOYMENT_CATEGORY is not null then
1252 per_assign_rec.EMPLOYMENT_CATEGORY := p_EMPLOYMENT_CATEGORY;
1253 end if;
1254 --
1255 if p_JOB_ID is not null then
1256 per_assign_rec.JOB_ID := p_JOB_ID;
1257 end if;
1258 --
1259 if p_PAYROLL_ID is not null then
1260 per_assign_rec.PAYROLL_ID := p_PAYROLL_ID;
1261 end if;
1262 --
1263 if p_PRIMARY_FLAG is not null then
1264 per_assign_rec.PRIMARY_FLAG := p_PRIMARY_FLAG;
1265 end if;
1266 --
1267 if p_LOCATION_ID is not null then
1268 per_assign_rec.LOCATION_ID := p_LOCATION_ID;
1269 end if;
1270 --
1271 if p_ASSIGNMENT_TYPE is not null then
1272 per_assign_rec.ASSIGNMENT_TYPE := p_ASSIGNMENT_TYPE;
1273 end if;
1274 --
1275 if p_ORGANIZATION_ID is not null then
1276 per_assign_rec.ORGANIZATION_ID := p_ORGANIZATION_ID;
1277 end if;
1278 --
1279 if p_POSITION_ID is not null then
1280 per_assign_rec.POSITION_ID := p_POSITION_ID;
1281 end if;
1282 --
1283 --
1284 if p_ASSIGNMENT_STATUS_TYPE_ID is not null then
1285 per_assign_rec.ASSIGNMENT_STATUS_TYPE_ID := p_ASSIGNMENT_STATUS_TYPE_ID;
1286 end if;
1287 --
1288 if p_GRADE_ID is not null then
1289 per_assign_rec.GRADE_ID := p_GRADE_ID;
1290 end if;
1291 --
1292 if p_PEOPLE_GROUP_ID is not null then
1293 per_assign_rec.PEOPLE_GROUP_ID := p_PEOPLE_GROUP_ID;
1294 end if;
1295 --
1296 Get_DT_Upd_Mode
1297 (p_effective_date => p_effective_date,
1298 p_base_table_name => 'PER_ALL_ASSIGNMENTS_F',
1299 p_base_key_column => 'ASSIGNMENT_ID',
1300 p_base_key_value => per_assign_rec.assignment_id,
1301 p_mode => l_upd_mode);
1302 --
1303 hr_assignment_api.update_emp_asg_criteria
1304 (p_effective_date => p_effective_date
1305 ,p_datetrack_update_mode => l_upd_mode
1306 ,p_assignment_id => per_assign_rec.assignment_id
1307 ,p_object_version_number => per_assign_rec.object_version_number
1308 ,p_grade_id => per_assign_rec.grade_id
1309 ,p_position_id => per_assign_rec.position_id
1310 ,p_job_id => per_assign_rec.job_id
1311 ,p_payroll_id => per_assign_rec.payroll_id
1312 ,p_location_id => per_assign_rec.location_id
1313 ,p_special_ceiling_step_id => l_special_ceiling_step_id
1314 ,p_organization_id => per_assign_rec.organization_id
1315 ,p_pay_basis_id => per_assign_rec.pay_basis_id
1316 ,p_employment_category => per_assign_rec.employment_category
1317 ,p_group_name => l_group_name
1318 ,p_effective_start_date => l_asg_effective_start_date
1319 ,p_effective_end_date => l_asg_effective_end_date
1320 ,p_people_group_id => l_people_group_id
1321 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1322 ,p_other_manager_warning => l_other_manager_warning
1323 ,p_spp_delete_warning => l_spp_delete_warning
1324 ,p_entries_changed_warning => l_entries_changed_warning
1325 ,p_tax_district_changed_warning => l_tax_district_changed_warning
1326 );
1327 --
1328 end if;
1329 --
1330 open c_assign;
1331 fetch c_assign into per_assign_rec;
1332 if c_assign%notfound Then
1333 Close c_assign;
1334 --
1335 -- Assignment Record is Not found so error.
1336 --
1337 fnd_message.set_name('BEN','BEN_91443_NO_ASSGN');
1338 fnd_message.raise_error;
1339 --
1340 End If;
1341 Close c_assign;
1342 --
1343 if l_upd_extra_asg_flag then
1344 if p_LABOUR_UNION_MEMBER_FLAG is not null then
1345 per_assign_rec.LABOUR_UNION_MEMBER_FLAG := p_LABOUR_UNION_MEMBER_FLAG;
1346 end if;
1347 --
1348 --
1349 if p_CHANGE_REASON is not null then
1350 per_assign_rec.CHANGE_REASON := p_CHANGE_REASON;
1351 end if;
1352 --
1353 --
1354 if p_BARGAINING_UNIT_CODE is not null then
1355 per_assign_rec.BARGAINING_UNIT_CODE := p_BARGAINING_UNIT_CODE;
1356 end if;
1357 --
1358 --
1359 if p_NORMAL_HOURS is not null then
1360 per_assign_rec.NORMAL_HOURS := p_NORMAL_HOURS;
1361 end if;
1362 --
1363 --
1364 if p_FREQUENCY is not null then
1365 per_assign_rec.FREQUENCY := p_FREQUENCY;
1366 end if;
1367
1368 if p_HOURLY_SALARIED_CODE is not null then
1369 per_assign_rec.HOURLY_SALARIED_CODE := p_HOURLY_SALARIED_CODE;
1370 end if;
1371
1372 if p_ASS_ATTRIBUTE_CATEGORY is not null then
1373 per_assign_rec.ASS_ATTRIBUTE_CATEGORY := p_ASS_ATTRIBUTE_CATEGORY;
1374 end if;
1375 --
1376 if p_ASS_ATTRIBUTE1 is not null then
1377 per_assign_rec.ASS_ATTRIBUTE1 := p_ASS_ATTRIBUTE1;
1378 end if;
1379
1380 if p_ASS_ATTRIBUTE10 is not null then
1381 per_assign_rec.ASS_ATTRIBUTE10 := p_ASS_ATTRIBUTE10;
1382 end if;
1383
1384 if p_ASS_ATTRIBUTE11 is not null then
1385 per_assign_rec.ASS_ATTRIBUTE11 := p_ASS_ATTRIBUTE11;
1386 end if;
1387
1388 if p_ASS_ATTRIBUTE12 is not null then
1389 per_assign_rec.ASS_ATTRIBUTE12 := p_ASS_ATTRIBUTE12;
1390 end if;
1391
1392 if p_ASS_ATTRIBUTE13 is not null then
1393 per_assign_rec.ASS_ATTRIBUTE13 := p_ASS_ATTRIBUTE13;
1394 end if;
1395
1396 if p_ASS_ATTRIBUTE14 is not null then
1397 per_assign_rec.ASS_ATTRIBUTE14 := p_ASS_ATTRIBUTE14;
1398 end if;
1399
1400 if p_ASS_ATTRIBUTE15 is not null then
1401 per_assign_rec.ASS_ATTRIBUTE15 := p_ASS_ATTRIBUTE15;
1402 end if;
1403
1404 if p_ASS_ATTRIBUTE16 is not null then
1405 per_assign_rec.ASS_ATTRIBUTE16 := p_ASS_ATTRIBUTE16;
1406 end if;
1407
1408 if p_ASS_ATTRIBUTE17 is not null then
1409 per_assign_rec.ASS_ATTRIBUTE17 := p_ASS_ATTRIBUTE17;
1410 end if;
1411
1412 if p_ASS_ATTRIBUTE18 is not null then
1413 per_assign_rec.ASS_ATTRIBUTE18 := p_ASS_ATTRIBUTE18;
1414 end if;
1415
1416 if p_ASS_ATTRIBUTE19 is not null then
1417 per_assign_rec.ASS_ATTRIBUTE19 := p_ASS_ATTRIBUTE19;
1418 end if;
1419
1420 if p_ASS_ATTRIBUTE2 is not null then
1421 per_assign_rec.ASS_ATTRIBUTE2 := p_ASS_ATTRIBUTE2;
1422 end if;
1423
1424 if p_ASS_ATTRIBUTE20 is not null then
1425 per_assign_rec.ASS_ATTRIBUTE20 := p_ASS_ATTRIBUTE20;
1426 end if;
1427
1428 if p_ASS_ATTRIBUTE21 is not null then
1429 per_assign_rec.ASS_ATTRIBUTE21 := p_ASS_ATTRIBUTE21;
1430 end if;
1431
1432 if p_ASS_ATTRIBUTE22 is not null then
1433 per_assign_rec.ASS_ATTRIBUTE22 := p_ASS_ATTRIBUTE22;
1434 end if;
1435
1436 if p_ASS_ATTRIBUTE23 is not null then
1437 per_assign_rec.ASS_ATTRIBUTE23 := p_ASS_ATTRIBUTE23;
1438 end if;
1439
1440 if p_ASS_ATTRIBUTE24 is not null then
1441 per_assign_rec.ASS_ATTRIBUTE24 := p_ASS_ATTRIBUTE24;
1442 end if;
1443
1444 if p_ASS_ATTRIBUTE25 is not null then
1445 per_assign_rec.ASS_ATTRIBUTE25 := p_ASS_ATTRIBUTE25;
1446 end if;
1447
1448 if p_ASS_ATTRIBUTE26 is not null then
1449 per_assign_rec.ASS_ATTRIBUTE26 := p_ASS_ATTRIBUTE26;
1450 end if;
1451
1452 if p_ASS_ATTRIBUTE27 is not null then
1453 per_assign_rec.ASS_ATTRIBUTE27 := p_ASS_ATTRIBUTE27;
1454 end if;
1455
1456 if p_ASS_ATTRIBUTE28 is not null then
1457 per_assign_rec.ASS_ATTRIBUTE28 := p_ASS_ATTRIBUTE28;
1458 end if;
1459
1460 if p_ASS_ATTRIBUTE29 is not null then
1461 per_assign_rec.ASS_ATTRIBUTE29 := p_ASS_ATTRIBUTE29;
1462 end if;
1463
1464 if p_ASS_ATTRIBUTE3 is not null then
1465 per_assign_rec.ASS_ATTRIBUTE3 := p_ASS_ATTRIBUTE3;
1466 end if;
1467
1468 if p_ASS_ATTRIBUTE30 is not null then
1469 per_assign_rec.ASS_ATTRIBUTE30 := p_ASS_ATTRIBUTE30;
1470 end if;
1471
1472 if p_ASS_ATTRIBUTE4 is not null then
1473 per_assign_rec.ASS_ATTRIBUTE4 := p_ASS_ATTRIBUTE4;
1474 end if;
1475
1476 if p_ASS_ATTRIBUTE5 is not null then
1477 per_assign_rec.ASS_ATTRIBUTE5 := p_ASS_ATTRIBUTE5;
1478 end if;
1479
1480 if p_ASS_ATTRIBUTE6 is not null then
1481 per_assign_rec.ASS_ATTRIBUTE6 := p_ASS_ATTRIBUTE6;
1482 end if;
1483
1484 if p_ASS_ATTRIBUTE7 is not null then
1485 per_assign_rec.ASS_ATTRIBUTE7 := p_ASS_ATTRIBUTE7;
1486 end if;
1487
1488 if p_ASS_ATTRIBUTE8 is not null then
1489 per_assign_rec.ASS_ATTRIBUTE8 := p_ASS_ATTRIBUTE8;
1490 end if;
1491
1492 if p_ASS_ATTRIBUTE9 is not null then
1493 per_assign_rec.ASS_ATTRIBUTE9 := p_ASS_ATTRIBUTE9;
1494 end if;
1495
1496 Get_DT_Upd_Mode
1497 (p_effective_date => p_effective_date,
1498 p_base_table_name => 'PER_ALL_ASSIGNMENTS_F',
1499 p_base_key_column => 'ASSIGNMENT_ID',
1500 p_base_key_value => per_assign_rec.assignment_id,
1501 p_mode => l_upd_mode);
1502
1503 --
1504 -- Call the API - hr_assignment_api.update_emp_asg :peasgapi.pkh
1505 --
1506 hr_assignment_api.update_emp_asg
1507 (p_validate => FALSE
1508 ,p_effective_date => p_effective_date
1509 ,p_datetrack_update_mode => l_upd_mode -- 'CORRECTION' -- 'UPDATE_OVERRIDE'
1510 ,p_assignment_id => per_assign_rec.assignment_id
1511 ,p_object_version_number => per_assign_rec.object_version_number
1512 ,p_supervisor_id => per_assign_rec.supervisor_id
1513 ,p_assignment_number => per_assign_rec.assignment_number
1514 ,p_change_reason => per_assign_rec.change_reason
1515 ,p_LABOUR_UNION_MEMBER_FLAG => per_assign_rec.LABOUR_UNION_MEMBER_FLAG
1516 ,p_BARGAINING_UNIT_CODE => per_assign_rec.BARGAINING_UNIT_CODE
1517 ,p_date_probation_end => per_assign_rec.date_probation_end
1518 ,p_default_code_comb_id => per_assign_rec.default_code_comb_id
1519 ,p_frequency => per_assign_rec.frequency
1520 ,p_internal_address_line => per_assign_rec.internal_address_line
1521 ,p_manager_flag => per_assign_rec.manager_flag
1522 ,p_normal_hours => per_assign_rec.normal_hours
1523 ,p_perf_review_period => per_assign_rec.perf_review_period
1524 ,p_perf_review_period_frequency => per_assign_rec.perf_review_period_frequency
1525 ,p_probation_period => per_assign_rec.probation_period
1526 ,p_probation_unit => per_assign_rec.probation_unit
1527 ,p_sal_review_period => per_assign_rec.sal_review_period
1528 ,p_sal_review_period_frequency => per_assign_rec.sal_review_period_frequency
1529 ,p_set_of_books_id => per_assign_rec.set_of_books_id
1530 ,p_source_type => per_assign_rec.source_type
1531 ,p_time_normal_finish => per_assign_rec.time_normal_finish
1532 ,p_time_normal_start => per_assign_rec.time_normal_start
1533 ,p_hourly_salaried_code => per_assign_rec.hourly_salaried_code
1534 ,p_ass_attribute_category => per_assign_rec.ass_attribute_category
1535 ,p_ass_attribute1 => per_assign_rec.ass_attribute1
1536 ,p_ass_attribute2 => per_assign_rec.ass_attribute2
1537 ,p_ass_attribute3 => per_assign_rec.ass_attribute3
1538 ,p_ass_attribute4 => per_assign_rec.ass_attribute4
1539 ,p_ass_attribute5 => per_assign_rec.ass_attribute5
1540 ,p_ass_attribute6 => per_assign_rec.ass_attribute6
1541 ,p_ass_attribute7 => per_assign_rec.ass_attribute7
1542 ,p_ass_attribute8 => per_assign_rec.ass_attribute8
1543 ,p_ass_attribute9 => per_assign_rec.ass_attribute9
1544 ,p_ass_attribute10 => per_assign_rec.ass_attribute10
1545 ,p_ass_attribute11 => per_assign_rec.ass_attribute11
1546 ,p_ass_attribute12 => per_assign_rec.ass_attribute12
1547 ,p_ass_attribute13 => per_assign_rec.ass_attribute13
1548 ,p_ass_attribute14 => per_assign_rec.ass_attribute14
1549 ,p_ass_attribute15 => per_assign_rec.ass_attribute15
1550 ,p_ass_attribute16 => per_assign_rec.ass_attribute16
1551 ,p_ass_attribute17 => per_assign_rec.ass_attribute17
1552 ,p_ass_attribute18 => per_assign_rec.ass_attribute18
1553 ,p_ass_attribute19 => per_assign_rec.ass_attribute19
1554 ,p_ass_attribute20 => per_assign_rec.ass_attribute20
1555 ,p_ass_attribute21 => per_assign_rec.ass_attribute21
1556 ,p_ass_attribute22 => per_assign_rec.ass_attribute22
1557 ,p_ass_attribute23 => per_assign_rec.ass_attribute23
1558 ,p_ass_attribute24 => per_assign_rec.ass_attribute24
1559 ,p_ass_attribute25 => per_assign_rec.ass_attribute25
1560 ,p_ass_attribute26 => per_assign_rec.ass_attribute26
1561 ,p_ass_attribute27 => per_assign_rec.ass_attribute27
1562 ,p_ass_attribute28 => per_assign_rec.ass_attribute28
1563 ,p_ass_attribute29 => per_assign_rec.ass_attribute29
1564 ,p_ass_attribute30 => per_assign_rec.ass_attribute30
1565 ,p_title => per_assign_rec.title
1566 -- ,p_segment1 => per_assign_rec.segment1
1567 -- ,p_segment2 => per_assign_rec.segment2
1568 -- ,p_segment3 => per_assign_rec.segment3
1569 -- ,p_segment4 => per_assign_rec.segment4
1570 -- ,p_segment5 => per_assign_rec.segment5
1571 -- ,p_segment6 => per_assign_rec.segment6
1572 -- ,p_segment7 => per_assign_rec.segment7
1573 -- ,p_segment8 => per_assign_rec.segment8
1574 -- ,p_segment9 => per_assign_rec.segment9
1575 -- ,p_segment10 => per_assign_rec.segment10
1576 -- ,p_segment11 => per_assign_rec.segment11
1577 -- ,p_segment12 => per_assign_rec.segment12
1578 -- ,p_segment13 => per_assign_rec.segment13
1579 -- ,p_segment14 => per_assign_rec.segment14
1580 -- ,p_segment15 => per_assign_rec.segment15
1581 -- ,p_segment16 => per_assign_rec.segment16
1582 -- ,p_segment17 => per_assign_rec.segment17
1583 -- ,p_segment18 => per_assign_rec.segment18
1584 -- ,p_segment19 => per_assign_rec.segment19
1585 -- ,p_segment20 => per_assign_rec.segment20
1586 -- ,p_segment21 => per_assign_rec.segment21
1587 -- ,p_segment22 => per_assign_rec.segment22
1588 -- ,p_segment23 => per_assign_rec.segment23
1589 -- ,p_segment24 => per_assign_rec.segment24
1590 -- ,p_segment25 => per_assign_rec.segment25
1591 -- ,p_segment26 => per_assign_rec.segment26
1592 -- ,p_segment27 => per_assign_rec.segment27
1593 -- ,p_segment28 => per_assign_rec.segment28
1594 -- ,p_segment29 => per_assign_rec.segment29
1595 -- ,p_segment30 => per_assign_rec.segment30
1596 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
1597 ,p_comment_id => l_comment_id
1598 ,p_effective_start_date => l_effective_start_date
1599 ,p_effective_end_date => l_effective_end_date
1600 ,p_concatenated_segments => l_concatenated_segments
1601 ,p_no_managers_warning => l_no_managers_warning
1602 ,p_other_manager_warning => l_other_manager_warning
1603 );
1604 --
1605 end if;
1606 hr_utility.set_location(' Leaving: WATIF_ALL_ASSIGNMENTS_F_API' , 10);
1607 end;
1608
1609
1610
1611 procedure WATIF_ALL_PEOPLE_F_API(
1612 p_person_id in number
1613 ,p_STUDENT_STATUS in varchar2
1614 ,p_MARITAL_STATUS in varchar2
1615 ,p_DATE_OF_DEATH in date
1616 ,p_DATE_OF_BIRTH in date
1617 ,p_COORD_BEN_NO_CVG_FLAG in varchar2
1618 ,p_ON_MILITARY_SERVICE in varchar2
1619 ,p_REGISTERED_DISABLED_FLAG in varchar2
1620 ,p_USES_TOBACCO_FLAG in varchar2
1621 ,p_BENEFIT_GROUP_ID in number
1622 ,p_PER_INFORMATION10 in varchar2
1623 ,p_COORD_BEN_MED_PLN_NO in varchar2
1624 ,p_DPDNT_VLNTRY_SVCE_FLAG in varchar2
1625 ,p_RECEIPT_OF_DEATH_CERT_DATE in date
1626 ,p_sex in varchar2
1627 ,p_ATTRIBUTE1 in VARCHAR2
1628 ,p_ATTRIBUTE10 in VARCHAR2
1629 ,p_ATTRIBUTE11 in VARCHAR2
1630 ,p_ATTRIBUTE12 in VARCHAR2
1631 ,p_ATTRIBUTE13 in VARCHAR2
1632 ,p_ATTRIBUTE14 in VARCHAR2
1633 ,p_ATTRIBUTE15 in VARCHAR2
1634 ,p_ATTRIBUTE16 in VARCHAR2
1635 ,p_ATTRIBUTE17 in VARCHAR2
1636 ,p_ATTRIBUTE18 in VARCHAR2
1637 ,p_ATTRIBUTE19 in VARCHAR2
1638 ,p_ATTRIBUTE2 in VARCHAR2
1639 ,p_ATTRIBUTE20 in VARCHAR2
1640 ,p_ATTRIBUTE21 in VARCHAR2
1641 ,p_ATTRIBUTE22 in VARCHAR2
1642 ,p_ATTRIBUTE23 in VARCHAR2
1643 ,p_ATTRIBUTE24 in VARCHAR2
1644 ,p_ATTRIBUTE25 in VARCHAR2
1645 ,p_ATTRIBUTE26 in VARCHAR2
1646 ,p_ATTRIBUTE27 in VARCHAR2
1647 ,p_ATTRIBUTE28 in VARCHAR2
1648 ,p_ATTRIBUTE29 in VARCHAR2
1649 ,p_ATTRIBUTE3 in VARCHAR2
1650 ,p_ATTRIBUTE30 in VARCHAR2
1651 ,p_ATTRIBUTE4 in VARCHAR2
1652 ,p_ATTRIBUTE5 in VARCHAR2
1653 ,p_ATTRIBUTE6 in VARCHAR2
1654 ,p_ATTRIBUTE7 in VARCHAR2
1655 ,p_ATTRIBUTE8 in VARCHAR2
1656 ,p_ATTRIBUTE9 in VARCHAR2
1657 ,p_business_group_id in number
1658 ,p_effective_date in date
1659 ) is
1660 --
1661 per_person_rec per_all_people_f%rowtype;
1662 --
1663 cursor c_person is
1664 select * from per_all_people_f
1665 where person_id = p_person_id and
1666 p_effective_date between nvl(effective_start_date, p_effective_date)
1667 and nvl(effective_end_date, p_effective_date);
1668 --
1669 l_effective_start_date date;
1670 l_effective_end_date date;
1671 l_full_name per_all_people_f.full_name%type; -- UTF8 Change Bug 2254683
1672 l_comment_id number;
1673 l_name_combination_warning boolean;
1674 l_assign_payroll_warning boolean;
1675 l_ORIG_HIRE_WARNING boolean;
1676 l_upd_mode varchar2(25);
1677 --
1678 begin
1679 --
1680 hr_utility.set_location(' Entering: WATIF_ALL_PEOPLE_F_API' , 10);
1681 --
1682 If p_STUDENT_STATUS is null and
1683 p_MARITAL_STATUS is null and
1684 p_DATE_OF_DEATH is null and
1685 p_DATE_OF_BIRTH is null and
1686 p_COORD_BEN_NO_CVG_FLAG is null and
1687 p_COORD_BEN_MED_PLN_NO is null and
1688 p_ON_MILITARY_SERVICE is null and
1689 p_REGISTERED_DISABLED_FLAG is null and
1690 p_USES_TOBACCO_FLAG is null and
1691 p_BENEFIT_GROUP_ID is null and
1692 p_PER_INFORMATION10 is null and
1693 p_COORD_BEN_MED_PLN_NO is null and
1694 p_DPDNT_VLNTRY_SVCE_FLAG is null and
1695 p_RECEIPT_OF_DEATH_CERT_DATE is null and
1696 p_sex is null
1697 then
1698 --
1699 fnd_message.set_name('BEN','BEN_91447_ALL_NULL');
1700 fnd_message.raise_error;
1701 --
1702 End If;
1703 --
1704 open c_person;
1705 fetch c_person into per_person_rec;
1706 if c_person%notfound Then
1707 Close c_person;
1708 --
1709 -- Person Record is Not found so error.
1710 --
1711 fnd_message.set_name('BEN','BEN_91444_NO_PER');
1712 fnd_message.raise_error;
1713 --
1714 End If;
1715 Close c_person;
1716 --
1717 if p_STUDENT_STATUS is not null then
1718 per_person_rec.STUDENT_STATUS := p_STUDENT_STATUS;
1719 end if;
1720 --
1721 --
1722 if p_MARITAL_STATUS is not null then
1723 per_person_rec.MARITAL_STATUS := p_MARITAL_STATUS;
1724 end if;
1725 --
1726 --
1727 if p_DATE_OF_DEATH is not null then
1728 per_person_rec.DATE_OF_DEATH := p_DATE_OF_DEATH;
1729 end if;
1730 --
1731 --
1732 if p_DATE_OF_BIRTH is not null then
1733 per_person_rec.DATE_OF_BIRTH := p_DATE_OF_BIRTH;
1734 end if;
1735 --
1736 --
1737 if p_COORD_BEN_NO_CVG_FLAG is not null then
1738 per_person_rec.COORD_BEN_NO_CVG_FLAG := p_COORD_BEN_NO_CVG_FLAG;
1739 end if;
1740 --
1741 --
1742 if p_COORD_BEN_MED_PLN_NO is not null then
1743 per_person_rec.COORD_BEN_MED_PLN_NO := p_COORD_BEN_MED_PLN_NO;
1744 end if;
1745 --
1746 --
1747 if p_ON_MILITARY_SERVICE is not null then
1748 per_person_rec.ON_MILITARY_SERVICE := p_ON_MILITARY_SERVICE;
1749 end if;
1750 --
1751 --
1752 if p_REGISTERED_DISABLED_FLAG is not null then
1753 per_person_rec.REGISTERED_DISABLED_FLAG := p_REGISTERED_DISABLED_FLAG;
1754 end if;
1755 --
1756 --
1757 if p_USES_TOBACCO_FLAG is not null then
1758 per_person_rec.USES_TOBACCO_FLAG := p_USES_TOBACCO_FLAG;
1759 end if;
1760 --
1761 --
1762 if p_BENEFIT_GROUP_ID is not null then
1763 per_person_rec.BENEFIT_GROUP_ID := p_BENEFIT_GROUP_ID;
1764 end if;
1765 --
1766
1767 if p_PER_INFORMATION10 is not null then
1768 per_person_rec.PER_INFORMATION10 := p_PER_INFORMATION10;
1769 end if;
1770
1771 if p_COORD_BEN_MED_PLN_NO is not null then
1772 per_person_rec.COORD_BEN_MED_PLN_NO := p_COORD_BEN_MED_PLN_NO;
1773 end if;
1774
1775 if p_DPDNT_VLNTRY_SVCE_FLAG is not null then
1776 per_person_rec.DPDNT_VLNTRY_SVCE_FLAG := p_DPDNT_VLNTRY_SVCE_FLAG;
1777 end if;
1778
1779 if p_RECEIPT_OF_DEATH_CERT_DATE is not null then
1780 per_person_rec.RECEIPT_OF_DEATH_CERT_DATE := p_RECEIPT_OF_DEATH_CERT_DATE;
1781 end if;
1782
1783 if p_sex is not null then
1784 per_person_rec.sex := p_sex;
1785 end if;
1786
1787 if p_ATTRIBUTE16 is not null then
1788 per_person_rec.ATTRIBUTE16 := p_ATTRIBUTE16;
1789 end if;
1790
1791 if p_ATTRIBUTE18 is not null then
1792 per_person_rec.ATTRIBUTE18 := p_ATTRIBUTE18;
1793 end if;
1794
1795 if p_ATTRIBUTE19 is not null then
1796 per_person_rec.ATTRIBUTE19 := p_ATTRIBUTE19;
1797 end if;
1798
1799 if p_ATTRIBUTE20 is not null then
1800 per_person_rec.ATTRIBUTE20 := p_ATTRIBUTE20;
1801 end if;
1802
1803 if p_ATTRIBUTE21 is not null then
1804 per_person_rec.ATTRIBUTE21 := p_ATTRIBUTE21;
1805 end if;
1806
1807 if p_ATTRIBUTE22 is not null then
1808 per_person_rec.ATTRIBUTE22 := p_ATTRIBUTE22;
1809 end if;
1810
1811 if p_ATTRIBUTE23 is not null then
1812 per_person_rec.ATTRIBUTE23 := p_ATTRIBUTE23;
1813 end if;
1814
1815 if p_ATTRIBUTE24 is not null then
1816 per_person_rec.ATTRIBUTE24 := p_ATTRIBUTE24;
1817 end if;
1818
1819 if p_ATTRIBUTE25 is not null then
1820 per_person_rec.ATTRIBUTE25 := p_ATTRIBUTE25;
1821 end if;
1822
1823 if p_ATTRIBUTE26 is not null then
1824 per_person_rec.ATTRIBUTE26 := p_ATTRIBUTE26;
1825 end if;
1826
1827 if p_ATTRIBUTE27 is not null then
1828 per_person_rec.ATTRIBUTE27 := p_ATTRIBUTE27;
1829 end if;
1830
1831 if p_ATTRIBUTE28 is not null then
1832 per_person_rec.ATTRIBUTE28 := p_ATTRIBUTE28;
1833 end if;
1834
1835 if p_ATTRIBUTE29 is not null then
1836 per_person_rec.ATTRIBUTE29 := p_ATTRIBUTE29;
1837 end if;
1838
1839 if p_ATTRIBUTE30 is not null then
1840 per_person_rec.ATTRIBUTE30 := p_ATTRIBUTE30;
1841 end if;
1842
1843 if p_ATTRIBUTE1 is not null then
1844 per_person_rec.ATTRIBUTE1 := p_ATTRIBUTE1;
1845 end if;
1846
1847 if p_ATTRIBUTE2 is not null then
1848 per_person_rec.ATTRIBUTE2 := p_ATTRIBUTE2;
1849 end if;
1850
1851 if p_ATTRIBUTE3 is not null then
1852 per_person_rec.ATTRIBUTE3 := p_ATTRIBUTE3;
1853 end if;
1854
1855 if p_ATTRIBUTE4 is not null then
1856 per_person_rec.ATTRIBUTE4 := p_ATTRIBUTE4;
1857 end if;
1858
1859 if p_ATTRIBUTE5 is not null then
1860 per_person_rec.ATTRIBUTE5 := p_ATTRIBUTE5;
1861 end if;
1862
1863 if p_ATTRIBUTE6 is not null then
1864 per_person_rec.ATTRIBUTE6 := p_ATTRIBUTE6;
1865 end if;
1866
1867 if p_ATTRIBUTE7 is not null then
1868 per_person_rec.ATTRIBUTE7 := p_ATTRIBUTE7;
1869 end if;
1870
1871 if p_ATTRIBUTE8 is not null then
1872 per_person_rec.ATTRIBUTE8 := p_ATTRIBUTE8;
1873 end if;
1874
1875 if p_ATTRIBUTE9 is not null then
1876 per_person_rec.ATTRIBUTE9 := p_ATTRIBUTE9;
1877 end if;
1878
1879 if p_ATTRIBUTE10 is not null then
1880 per_person_rec.ATTRIBUTE10 := p_ATTRIBUTE10;
1881 end if;
1882
1883 if p_ATTRIBUTE11 is not null then
1884 per_person_rec.ATTRIBUTE11 := p_ATTRIBUTE11;
1885 end if;
1886
1887 if p_ATTRIBUTE12 is not null then
1888 per_person_rec.ATTRIBUTE12 := p_ATTRIBUTE12;
1889 end if;
1890
1891 if p_ATTRIBUTE13 is not null then
1892 per_person_rec.ATTRIBUTE13 := p_ATTRIBUTE13;
1893 end if;
1894
1895 if p_ATTRIBUTE14 is not null then
1896 per_person_rec.ATTRIBUTE14 := p_ATTRIBUTE14;
1897 end if;
1898
1899 if p_ATTRIBUTE15 is not null then
1900 per_person_rec.ATTRIBUTE15 := p_ATTRIBUTE15;
1901 end if;
1902
1903 if p_ATTRIBUTE17 is not null then
1904 per_person_rec.ATTRIBUTE17 := p_ATTRIBUTE17;
1905 end if;
1906
1907 --
1908 -- Call the API - hr_person_api.update_person
1909 --
1910 --
1911 Get_DT_Upd_Mode
1912 (p_effective_date => p_effective_date,
1913 p_base_table_name => 'PER_ALL_PEOPLE_F',
1914 p_base_key_column => 'PERSON_ID',
1915 p_base_key_value => per_person_rec.person_id,
1916 p_mode => l_upd_mode);
1917 --
1918 hr_person_api.update_person
1919 (p_validate => FALSE
1920 ,p_effective_date => p_effective_date
1921 ,p_datetrack_update_mode => l_upd_mode -- 'CORRECTION' -- 'UPDATE_OVERRIDE'
1922 ,p_person_id => per_person_rec.person_id
1923 ,p_object_version_number => per_person_rec.object_version_number
1924 ,p_person_type_id => per_person_rec.person_type_id
1925 ,p_last_name => per_person_rec.last_name
1926 ,p_applicant_number => per_person_rec.applicant_number
1927 ,p_comments => null
1928 ,p_date_employee_data_verified => per_person_rec.date_employee_data_verified
1929 ,p_date_of_birth => per_person_rec.date_of_birth
1930 ,p_email_address => per_person_rec.email_address
1931 ,p_employee_number => per_person_rec.employee_number
1932 ,p_expense_check_send_to_addres => per_person_rec.expense_check_send_to_address
1933 ,p_first_name => per_person_rec.first_name
1934 ,p_known_as => per_person_rec.known_as
1935 ,p_marital_status => per_person_rec.marital_status
1936 ,p_middle_names => per_person_rec.middle_names
1937 ,p_nationality => per_person_rec.nationality
1938 ,p_national_identifier => per_person_rec.national_identifier
1939 ,p_previous_last_name => per_person_rec.previous_last_name
1940 ,p_registered_disabled_flag => per_person_rec.registered_disabled_flag
1941 ,p_sex => per_person_rec.sex
1942 ,p_title => per_person_rec.title
1943 ,p_vendor_id => per_person_rec.vendor_id
1944 ,p_work_telephone => per_person_rec.work_telephone
1945 ,p_attribute_category => per_person_rec.attribute_category
1946 ,p_attribute1 => per_person_rec.attribute1
1947 ,p_attribute2 => per_person_rec.attribute2
1948 ,p_attribute3 => per_person_rec.attribute3
1949 ,p_attribute4 => per_person_rec.attribute4
1950 ,p_attribute5 => per_person_rec.attribute5
1951 ,p_attribute6 => per_person_rec.attribute6
1952 ,p_attribute7 => per_person_rec.attribute7
1953 ,p_attribute8 => per_person_rec.attribute8
1954 ,p_attribute9 => per_person_rec.attribute9
1955 ,p_attribute10 => per_person_rec.attribute10
1956 ,p_attribute11 => per_person_rec.attribute11
1957 ,p_attribute12 => per_person_rec.attribute12
1958 ,p_attribute13 => per_person_rec.attribute13
1959 ,p_attribute14 => per_person_rec.attribute14
1960 ,p_attribute15 => per_person_rec.attribute15
1961 ,p_attribute16 => per_person_rec.attribute16
1962 ,p_attribute17 => per_person_rec.attribute17
1963 ,p_attribute18 => per_person_rec.attribute18
1964 ,p_attribute19 => per_person_rec.attribute19
1965 ,p_attribute20 => per_person_rec.attribute20
1966 ,p_attribute21 => per_person_rec.attribute21
1967 ,p_attribute22 => per_person_rec.attribute22
1968 ,p_attribute23 => per_person_rec.attribute23
1969 ,p_attribute24 => per_person_rec.attribute24
1970 ,p_attribute25 => per_person_rec.attribute25
1971 ,p_attribute26 => per_person_rec.attribute26
1972 ,p_attribute27 => per_person_rec.attribute27
1973 ,p_attribute28 => per_person_rec.attribute28
1974 ,p_attribute29 => per_person_rec.attribute29
1975 ,p_attribute30 => per_person_rec.attribute30
1976 ,p_per_information_category => per_person_rec.per_information_category
1977 ,p_per_information1 => per_person_rec.per_information1
1978 ,p_per_information2 => per_person_rec.per_information2
1979 ,p_per_information3 => per_person_rec.per_information3
1980 ,p_per_information4 => per_person_rec.per_information4
1981 ,p_per_information5 => per_person_rec.per_information5
1982 ,p_per_information6 => per_person_rec.per_information6
1983 ,p_per_information7 => per_person_rec.per_information7
1984 ,p_per_information8 => per_person_rec.per_information8
1985 ,p_per_information9 => per_person_rec.per_information9
1986 ,p_per_information10 => per_person_rec.per_information10
1987 ,p_per_information11 => per_person_rec.per_information11
1988 ,p_per_information12 => per_person_rec.per_information12
1989 ,p_per_information13 => per_person_rec.per_information13
1990 ,p_per_information14 => per_person_rec.per_information14
1991 ,p_per_information15 => per_person_rec.per_information15
1992 ,p_per_information16 => per_person_rec.per_information16
1993 ,p_per_information17 => per_person_rec.per_information17
1994 ,p_per_information18 => per_person_rec.per_information18
1995 ,p_per_information19 => per_person_rec.per_information19
1996 ,p_per_information20 => per_person_rec.per_information20
1997 ,p_per_information21 => per_person_rec.per_information21
1998 ,p_per_information22 => per_person_rec.per_information22
1999 ,p_per_information23 => per_person_rec.per_information23
2000 ,p_per_information24 => per_person_rec.per_information24
2001 ,p_per_information25 => per_person_rec.per_information25
2002 ,p_per_information26 => per_person_rec.per_information26
2003 ,p_per_information27 => per_person_rec.per_information27
2004 ,p_per_information28 => per_person_rec.per_information28
2005 ,p_per_information29 => per_person_rec.per_information29
2006 ,p_per_information30 => per_person_rec.per_information30
2007 ,p_date_of_death => per_person_rec.date_of_death
2008 ,p_background_check_status => per_person_rec.background_check_status
2009 ,p_background_date_check => per_person_rec.background_date_check
2010 ,p_blood_type => per_person_rec.blood_type
2011 ,p_correspondence_language => per_person_rec.correspondence_language
2012 ,p_fast_path_employee => per_person_rec.fast_path_employee
2013 ,p_fte_capacity => per_person_rec.fte_capacity
2014 ,p_hold_applicant_date_until => per_person_rec.hold_applicant_date_until
2015 ,p_honors => per_person_rec.honors
2016 ,p_internal_location => per_person_rec.internal_location
2017 ,p_last_medical_test_by => per_person_rec.last_medical_test_by
2018 ,p_last_medical_test_date => per_person_rec.last_medical_test_date
2019 ,p_mailstop => per_person_rec.mailstop
2020 ,p_office_number => per_person_rec.office_number
2021 ,p_on_military_service => per_person_rec.on_military_service
2022 ,p_pre_name_adjunct => per_person_rec.pre_name_adjunct
2023 ,p_projected_start_date => per_person_rec.projected_start_date
2024 ,p_rehire_authorizor => per_person_rec.rehire_authorizor
2025 ,p_rehire_recommendation => per_person_rec.rehire_recommendation
2026 ,p_resume_exists => per_person_rec.resume_exists
2027 ,p_resume_last_updated => per_person_rec.resume_last_updated
2028 ,p_second_passport_exists => per_person_rec.second_passport_exists
2029 ,p_student_status => per_person_rec.student_status
2030 ,p_work_schedule => per_person_rec.work_schedule
2031 ,p_rehire_reason => per_person_rec.rehire_reason
2032 ,p_suffix => per_person_rec.suffix
2033 ,p_benefit_group_id => per_person_rec.benefit_group_id
2034 ,P_RECEIPT_OF_DEATH_CERT_DATE => per_person_rec.RECEIPT_OF_DEATH_CERT_DATE
2035 ,P_COORD_BEN_MED_PLN_NO => per_person_rec.COORD_BEN_MED_PLN_NO
2036 ,P_COORD_BEN_NO_CVG_FLAG => per_person_rec.COORD_BEN_NO_CVG_FLAG
2037 ,P_USES_TOBACCO_FLAG => per_person_rec.USES_TOBACCO_FLAG
2038 ,P_DPDNT_ADOPTION_DATE => per_person_rec.DPDNT_ADOPTION_DATE
2039 ,P_DPDNT_VLNTRY_SVCE_FLAG => per_person_rec.DPDNT_VLNTRY_SVCE_FLAG
2040 ,P_ORIGINAL_DATE_OF_HIRE => per_person_rec.ORIGINAL_DATE_OF_HIRE
2041 ,p_effective_start_date => l_effective_start_date
2042 ,p_effective_end_date => l_effective_end_date
2043 ,p_full_name => l_full_name
2044 ,p_comment_id => l_comment_id
2045 ,p_name_combination_warning => l_name_combination_warning
2046 ,p_assign_payroll_warning => l_assign_payroll_warning
2047 ,P_ORIG_HIRE_WARNING => l_ORIG_HIRE_WARNING
2048 );
2049 --
2050 hr_utility.set_location(' Leaving: WATIF_ALL_PEOPLE_F_API' , 10);
2051 end;
2052
2053
2054
2055
2056
2057 procedure WATIF_PERIODS_OF_SERVICE_API(
2058 p_person_id in number
2059 ,p_per_object_version_number in number
2060 ,p_DATE_START in date
2061 ,p_LEAVING_REASON in varchar2
2062 ,p_ADJUSTED_SVC_DATE in date
2063 ,p_ACTUAL_TERMINATION_DATE in date
2064 ,p_FINAL_PROCESS_DATE in date
2065 ,p_ATTRIBUTE1 in VARCHAR2
2066 ,p_ATTRIBUTE2 in VARCHAR2
2067 ,p_ATTRIBUTE3 in VARCHAR2
2068 ,p_ATTRIBUTE4 in VARCHAR2
2069 ,p_ATTRIBUTE5 in VARCHAR2
2070 ,p_ATTRIBUTE6 in VARCHAR2
2071 ,p_ATTRIBUTE7 in VARCHAR2
2072 ,p_ATTRIBUTE8 in VARCHAR2
2073 ,p_ATTRIBUTE9 in VARCHAR2
2074 ,p_ATTRIBUTE10 in VARCHAR2
2075 ,p_ATTRIBUTE11 in VARCHAR2
2076 ,p_ATTRIBUTE12 in VARCHAR2
2077 ,p_ATTRIBUTE13 in VARCHAR2
2078 ,p_ATTRIBUTE14 in VARCHAR2
2079 ,p_ATTRIBUTE15 in VARCHAR2
2080 ,p_ATTRIBUTE16 in VARCHAR2
2081 ,p_ATTRIBUTE17 in VARCHAR2
2082 ,p_ATTRIBUTE18 in VARCHAR2
2083 ,p_ATTRIBUTE19 in VARCHAR2
2084 ,p_ATTRIBUTE20 in VARCHAR2
2085 ,p_business_group_id in number
2086 ,p_effective_date in date
2087 ) is
2088 --
2089 cursor c_per is
2090 select object_version_number
2091 from per_people_f
2092 where person_id = p_person_id
2093 and p_effective_date between effective_start_date and
2094 effective_end_date;
2095 --
2096 cursor c_service is
2097 select
2098 rowid, per_periods_of_service.*
2099 from per_periods_of_service
2100 where person_id = p_person_id
2101 order by DATE_START;-- and
2102 -- p_effective_date between nvl(DATE_START, p_effective_date)
2103 -- and nvl(DATE_END, p_effective_date);
2104 --
2105 per_service_rec c_service%rowtype;
2106 --
2107 l_assignment_id number;
2108 l_asg_object_version_number number;
2109 l_per_object_version_number number := p_per_object_version_number;
2110 l_per_effective_start_date date;
2111 l_per_effective_end_date date;
2112 l_assignment_sequence number;
2113 l_assignment_number varchar2(1000);
2114 l_rehire_reason varchar2(1000) := null;
2115 l_assign_payroll_warning boolean;
2116 --
2117 l_supervisor_warning boolean;
2118 l_event_warning boolean;
2119 l_interview_warning boolean;
2120 l_review_warning boolean;
2121 l_recruiter_warning boolean;
2122 l_dod_warning boolean;
2123 l_asg_future_changes_warning boolean;
2124 l_entries_changed_warning varchar2(1000);
2125 l_pay_proposal_warning boolean;
2126 l_last_standard_process_date date;
2127 l_count_payrolls number;
2128 l_final_process_date date;
2129 --
2130 l_s_final_process_date date;
2131 l_s_actual_termination_date date;
2132 l_c_assignment_status_type_id number;
2133 l_d_status varchar2(1000);
2134 l_requery_required varchar2(1) := 'N';
2135 L_DODWARNING varchar2(1);
2136 --
2137 begin
2138 --
2139 hr_utility.set_location(' Entering: WATIF_PERIODS_OF_SERVICE_API' , 10);
2140 hr_utility.set_location('p_ADJUSTED_SVC_DATE ' || p_ADJUSTED_SVC_DATE , 11);
2141 hr_utility.set_location('p_ACTUAL_TERMINATION_DATE ' ||p_ACTUAL_TERMINATION_DATE , 11);
2142 hr_utility.set_location('p_LEAVING_REASON ' ||p_LEAVING_REASON , 11);
2143 --
2144 If p_DATE_START is null and p_LEAVING_REASON is null and
2145 p_ADJUSTED_SVC_DATE is null and
2146 p_ATTRIBUTE1 is null and
2147 p_ATTRIBUTE2 is null and
2148 p_ATTRIBUTE3 is null and
2149 p_ATTRIBUTE4 is null and
2150 p_ATTRIBUTE5 is null and
2151 p_ATTRIBUTE6 is null and
2152 p_ATTRIBUTE7 is null and
2153 p_ATTRIBUTE8 is null and
2154 p_ATTRIBUTE9 is null and
2155 p_ATTRIBUTE10 is null and
2156 p_ATTRIBUTE11 is null and
2157 p_ATTRIBUTE12 is null and
2158 p_ATTRIBUTE13 is null and
2159 p_ATTRIBUTE14 is null and
2160 p_ATTRIBUTE15 is null and
2161 p_ATTRIBUTE16 is null and
2162 p_ATTRIBUTE17 is null and
2163 p_ATTRIBUTE18 is null and
2164 p_ATTRIBUTE19 is null and
2165 p_ATTRIBUTE20 is null and
2166 p_FINAL_PROCESS_DATE is null and
2167 p_ACTUAL_TERMINATION_DATE is null then
2168 --
2169 fnd_message.set_name('BEN','BEN_91447_ALL_NULL');
2170 fnd_message.raise_error;
2171 --
2172 End If;
2173
2174
2175 if p_adjusted_svc_date is not null then
2176 -- get the object_version number for the person.
2177 open c_per;
2178 fetch c_per into l_per_object_version_number;
2179 close c_per;
2180 --
2181 hr_utility.set_location(' l_per_object_version_number = '||
2182 to_char(l_per_object_version_number) , 10);
2183 -- Re hire case
2184 hr_employee_api.re_hire_ex_employee
2185 (p_validate => FALSE
2186 ,p_hire_date => p_effective_date
2187 ,p_person_id => p_person_id
2188 ,p_per_object_version_number => l_per_object_version_number
2189 -- ,p_person_type_id => hr_api.g_number
2190 ,p_rehire_reason => null
2191 ,p_assignment_id => l_assignment_id
2192 ,p_asg_object_version_number => l_asg_object_version_number
2193 ,p_per_effective_start_date => l_per_effective_start_date
2194 ,p_per_effective_end_date => l_per_effective_end_date
2195 ,p_assignment_sequence => l_assignment_sequence
2196 ,p_assignment_number => l_assignment_number
2197 ,p_assign_payroll_warning => l_assign_payroll_warning
2198 );
2199 elsif p_LEAVING_REASON is not null and
2200 p_ACTUAL_TERMINATION_DATE is not null
2201 then
2202 -- Termination case.
2203 --
2204 open c_service;
2205 fetch c_service into per_service_rec;
2206 if c_service%notfound Then
2207 Close c_service;
2208 --
2209 -- Periods of Service Record is Not found so error.
2210 --
2211 fnd_message.set_name('BEN','BEN_91445_NO_PERIODS_SERVICE');
2212 fnd_message.raise_error;
2213 --
2214 End If;
2215 Close c_service;
2216 --
2217 -- 9999 ????what to do with p_LEAVING_REASON as not used in the api call.
2218 --
2219 -- Bug : 5142 : This API call is not creating the potential
2220 -- so call the procedure which is called from the form - PERWSTEM
2221 --
2222 /* hr_ex_employee_api.actual_termination_emp
2223 (p_validate => FALSE
2224 ,p_effective_date => p_effective_date
2225 ,p_period_of_service_id => per_service_rec.period_of_service_id
2226 ,p_object_version_number => per_service_rec.object_version_number
2227 ,p_actual_termination_date => p_ACTUAL_TERMINATION_DATE
2228 ,p_last_standard_process_date => l_last_standard_process_date
2229 -- ,p_person_type_id in number default hr_api.g_number
2230 -- ,p_assignment_status_type_id in number default hr_api.g_number
2231 ,p_supervisor_warning => l_supervisor_warning
2232 ,p_event_warning => l_event_warning
2233 ,p_interview_warning => l_interview_warning
2234 ,p_review_warning => l_review_warning
2235 ,p_recruiter_warning => l_recruiter_warning
2236 ,p_asg_future_changes_warning => l_asg_future_changes_warning
2237 ,p_entries_changed_warning => l_entries_changed_warning
2238 ,p_pay_proposal_warning => l_pay_proposal_warning
2239 ,p_dod_warning => l_dod_warning
2240 );
2241 */
2242 --
2243 --Bug 2943163
2244
2245 per_periods_of_service_pkg.get_final_dates(p_period_of_service_id =>per_service_rec.PERIOD_OF_SERVICE_ID
2246 ,p_person_id => per_service_rec.PERSON_ID
2247 ,p_actual_termination_date => P_ACTUAL_TERMINATION_DATE
2248 ,p_no_payrolls => l_count_payrolls
2249 ,p_final_process_date => l_final_process_date
2250 ,p_last_standard_process_date =>l_last_standard_process_date );
2251
2252 --Bug 2943163
2253
2254 per_periods_of_service_pkg.update_term_row (
2255 p_row_id => rowidtochar(per_service_rec.rowid)
2256 ,p_period_of_service_id => per_service_rec.PERIOD_OF_SERVICE_ID
2257 ,p_business_group_id => per_service_rec.BUSINESS_GROUP_ID
2258 ,p_person_id => per_service_rec.PERSON_ID
2259 ,p_date_start => per_service_rec.DATE_START
2260 ,p_termination_accepted_per_id => per_service_rec.TERMINATION_ACCEPTED_PERSON_ID
2261 ,p_accepted_termination_date => per_service_rec.ACCEPTED_TERMINATION_DATE
2262 ,p_actual_termination_date => p_ACTUAL_TERMINATION_DATE
2263 ,p_comments => per_service_rec.COMMENTS
2264 ,p_final_process_date => nvl(p_FINAL_PROCESS_DATE, l_final_process_date)
2265 ,p_last_standard_process_date => nvl(per_service_rec.LAST_STANDARD_PROCESS_DATE, l_last_standard_process_date)
2266 ,p_leaving_reason => p_LEAVING_REASON -- per_service_rec.LEAVING_REASON
2267 ,p_notified_termination_date => per_service_rec.NOTIFIED_TERMINATION_DATE
2268 ,p_projected_termination_date => per_service_rec.PROJECTED_TERMINATION_DATE
2269 ,p_request_id => per_service_rec.REQUEST_ID
2270 ,p_program_application_id => per_service_rec.PROGRAM_APPLICATION_ID
2271 ,p_program_id => per_service_rec.PROGRAM_ID
2272 ,p_program_update_date => per_service_rec.PROGRAM_UPDATE_DATE
2273 ,p_attribute_category => per_service_rec.ATTRIBUTE_CATEGORY
2274 ,p_attribute1 => per_service_rec.ATTRIBUTE1
2275 ,p_attribute2 => per_service_rec.ATTRIBUTE2
2276 ,p_attribute3 => per_service_rec.ATTRIBUTE3
2277 ,p_attribute4 => per_service_rec.ATTRIBUTE4
2278 ,p_attribute5 => per_service_rec.ATTRIBUTE5
2279 ,p_attribute6 => per_service_rec.ATTRIBUTE6
2280 ,p_attribute7 => per_service_rec.ATTRIBUTE7
2281 ,p_attribute8 => per_service_rec.ATTRIBUTE8
2282 ,p_attribute9 => per_service_rec.ATTRIBUTE9
2283 ,p_attribute10 => per_service_rec.ATTRIBUTE10
2284 ,p_attribute11 => per_service_rec.ATTRIBUTE11
2285 ,p_attribute12 => per_service_rec.ATTRIBUTE12
2286 ,p_attribute13 => per_service_rec.ATTRIBUTE13
2287 ,p_attribute14 => per_service_rec.ATTRIBUTE14
2288 ,p_attribute15 => per_service_rec.ATTRIBUTE15
2289 ,p_attribute16 => per_service_rec.ATTRIBUTE16
2290 ,p_attribute17 => per_service_rec.ATTRIBUTE17
2291 ,p_attribute18 => per_service_rec.ATTRIBUTE18
2292 ,p_attribute19 => per_service_rec.ATTRIBUTE19
2293 ,p_attribute20 => per_service_rec.ATTRIBUTE20
2294 ,p_initiate_cancellation => null -- per_service_rec.INITIATE_CANCELLATION
2295 ,p_s_final_process_date => l_s_final_process_date
2296 ,p_s_actual_termination_date => l_s_actual_termination_date
2297 ,p_c_assignment_status_type_id => l_c_assignment_status_type_id
2298 ,p_d_status => l_d_status
2299 ,p_requery_required => l_requery_required -- 'N' -- 999 IO
2300 ,p_clear_details => 'N'
2301 ,p_legislation_code => 'US'
2302 ,p_pds_information_category => per_service_rec.PDS_INFORMATION_CATEGORY
2303 ,p_pds_information1 => per_service_rec.PDS_INFORMATION1
2304 ,p_pds_information2 => per_service_rec.PDS_INFORMATION2
2305 ,p_pds_information3 => per_service_rec.PDS_INFORMATION3
2306 ,p_pds_information4 => per_service_rec.PDS_INFORMATION4
2307 ,p_pds_information5 => per_service_rec.PDS_INFORMATION5
2308 ,p_pds_information6 => per_service_rec.PDS_INFORMATION6
2309 ,p_pds_information7 => per_service_rec.PDS_INFORMATION7
2310 ,p_pds_information8 => per_service_rec.PDS_INFORMATION8
2311 ,p_pds_information9 => per_service_rec.PDS_INFORMATION9
2312 ,p_pds_information10 => per_service_rec.PDS_INFORMATION10
2313 ,p_pds_information11 => per_service_rec.PDS_INFORMATION11
2314 ,p_pds_information12 => per_service_rec.PDS_INFORMATION12
2315 ,p_pds_information13 => per_service_rec.PDS_INFORMATION13
2316 ,p_pds_information14 => per_service_rec.PDS_INFORMATION14
2317 ,p_pds_information15 => per_service_rec.PDS_INFORMATION15
2318 ,p_pds_information16 => per_service_rec.PDS_INFORMATION16
2319 ,p_pds_information17 => per_service_rec.PDS_INFORMATION17
2320 ,p_pds_information18 => per_service_rec.PDS_INFORMATION18
2321 ,p_pds_information19 => per_service_rec.PDS_INFORMATION19
2322 ,p_pds_information20 => per_service_rec.PDS_INFORMATION20
2323 ,p_pds_information21 => per_service_rec.PDS_INFORMATION21
2324 ,p_pds_information22 => per_service_rec.PDS_INFORMATION22
2325 ,p_pds_information23 => per_service_rec.PDS_INFORMATION23
2326 ,p_pds_information24 => per_service_rec.PDS_INFORMATION24
2327 ,p_pds_information25 => per_service_rec.PDS_INFORMATION25
2328 ,p_pds_information26 => per_service_rec.PDS_INFORMATION26
2329 ,p_pds_information27 => per_service_rec.PDS_INFORMATION27
2330 ,p_pds_information28 => per_service_rec.PDS_INFORMATION28
2331 ,p_pds_information29 => per_service_rec.PDS_INFORMATION29
2332 ,p_pds_information30 => per_service_rec.PDS_INFORMATION30
2333 ,p_adjusted_svc_date => per_service_rec.ADJUSTED_SVC_DATE
2334 ,p_dodwarning => l_dodwarning);
2335 --
2336 else
2337 -- Unpaid LOA.
2338 open c_service;
2339 fetch c_service into per_service_rec;
2340 if c_service%notfound Then
2341 Close c_service;
2342 --
2343 -- Periods of Service Record is Not found so error.
2344 --
2345 fnd_message.set_name('BEN','BEN_91445_NO_PERIODS_SERVICE');
2346 fnd_message.raise_error;
2347 --
2348 End If;
2349 Close c_service;
2350 --
2351 --
2352 if p_date_start is not null then
2353 per_service_rec.date_start := p_date_start;
2354 end if;
2355 --
2356 --
2357 if p_LEAVING_REASON is not null then
2358 per_service_rec.LEAVING_REASON := p_LEAVING_REASON;
2359 end if;
2360 --
2361 --
2362 if p_ADJUSTED_SVC_DATE is not null then
2363 per_service_rec.ADJUSTED_SVC_DATE := p_ADJUSTED_SVC_DATE;
2364 end if;
2365 --
2366 --
2367 if p_ACTUAL_TERMINATION_DATE is not null then
2368 per_service_rec.ACTUAL_TERMINATION_DATE := p_ACTUAL_TERMINATION_DATE;
2369 end if;
2370
2371 if p_FINAL_PROCESS_DATE is not null then
2372 per_service_rec.FINAL_PROCESS_DATE := p_FINAL_PROCESS_DATE;
2373 end if;
2374
2375 if p_ATTRIBUTE1 is not null then
2376 per_service_rec.ATTRIBUTE1 := p_ATTRIBUTE1;
2377 end if;
2378
2379 if p_ATTRIBUTE2 is not null then
2380 per_service_rec.ATTRIBUTE2 := p_ATTRIBUTE2;
2381 end if;
2382
2383 if p_ATTRIBUTE3 is not null then
2384 per_service_rec.ATTRIBUTE3 := p_ATTRIBUTE3;
2385 end if;
2386
2387 if p_ATTRIBUTE4 is not null then
2388 per_service_rec.ATTRIBUTE4 := p_ATTRIBUTE4;
2389 end if;
2390
2391 if p_ATTRIBUTE5 is not null then
2392 per_service_rec.ATTRIBUTE5 := p_ATTRIBUTE5;
2393 end if;
2394
2395 if p_ATTRIBUTE6 is not null then
2396 per_service_rec.ATTRIBUTE6 := p_ATTRIBUTE6;
2397 end if;
2398
2399 if p_ATTRIBUTE7 is not null then
2400 per_service_rec.ATTRIBUTE7 := p_ATTRIBUTE7;
2401 end if;
2402
2403 if p_ATTRIBUTE8 is not null then
2404 per_service_rec.ATTRIBUTE8 := p_ATTRIBUTE8;
2405 end if;
2406
2407 if p_ATTRIBUTE9 is not null then
2408 per_service_rec.ATTRIBUTE9 := p_ATTRIBUTE9;
2409 end if;
2410
2411 if p_ATTRIBUTE10 is not null then
2412 per_service_rec.ATTRIBUTE10 := p_ATTRIBUTE10;
2413 end if;
2414
2415 if p_ATTRIBUTE11 is not null then
2416 per_service_rec.ATTRIBUTE11 := p_ATTRIBUTE11;
2417 end if;
2418
2419 if p_ATTRIBUTE12 is not null then
2420 per_service_rec.ATTRIBUTE12 := p_ATTRIBUTE12;
2421 end if;
2422
2423 if p_ATTRIBUTE13 is not null then
2424 per_service_rec.ATTRIBUTE13 := p_ATTRIBUTE13;
2425 end if;
2426
2427 if p_ATTRIBUTE14 is not null then
2428 per_service_rec.ATTRIBUTE14 := p_ATTRIBUTE14;
2429 end if;
2430
2431 if p_ATTRIBUTE15 is not null then
2432 per_service_rec.ATTRIBUTE15 := p_ATTRIBUTE15;
2433 end if;
2434
2435 if p_ATTRIBUTE16 is not null then
2436 per_service_rec.ATTRIBUTE16 := p_ATTRIBUTE16;
2437 end if;
2438
2439 if p_ATTRIBUTE17 is not null then
2440 per_service_rec.ATTRIBUTE17 := p_ATTRIBUTE17;
2441 end if;
2442
2443 if p_ATTRIBUTE18 is not null then
2444 per_service_rec.ATTRIBUTE18 := p_ATTRIBUTE18;
2445 end if;
2446
2447 if p_ATTRIBUTE19 is not null then
2448 per_service_rec.ATTRIBUTE19 := p_ATTRIBUTE19;
2449 end if;
2450
2451 if p_ATTRIBUTE20 is not null then
2452 per_service_rec.ATTRIBUTE20 := p_ATTRIBUTE20;
2453 end if;
2454 --
2455 -- Call the API -
2456 --
2457 PER_PERIODS_OF_SERVICE_PKG.update_row(
2458 p_row_id => rowidtochar(per_service_rec.rowid)
2459 ,p_period_of_service_id => per_service_rec.period_of_service_id
2460 ,p_business_group_id => per_service_rec.business_group_id
2461 ,p_person_id => per_service_rec.person_id
2462 ,p_date_start => per_service_rec.date_start
2463 ,p_termination_accepted_per_id => per_service_rec.termination_accepted_person_id
2464 ,p_accepted_termination_date => per_service_rec.accepted_termination_date
2465 ,p_actual_termination_date => per_service_rec.actual_termination_date
2466 ,p_comments => per_service_rec.comments
2467 ,p_final_process_date => per_service_rec.final_process_date
2468 ,p_last_standard_process_date => per_service_rec.last_standard_process_date
2469 ,p_leaving_reason => per_service_rec.leaving_reason
2470 ,p_notified_termination_date => per_service_rec.notified_termination_date
2471 ,p_projected_termination_date => per_service_rec.projected_termination_date
2472 ,p_request_id => per_service_rec.request_id
2473 ,p_program_application_id => per_service_rec.program_application_id
2474 ,p_program_id => per_service_rec.program_id
2475 ,p_program_update_date => per_service_rec.program_update_date
2476 ,p_attribute_category => per_service_rec.attribute_category
2477 ,p_attribute1 => per_service_rec.attribute1
2478 ,p_attribute2 => per_service_rec.attribute2
2479 ,p_attribute3 => per_service_rec.attribute3
2480 ,p_attribute4 => per_service_rec.attribute4
2481 ,p_attribute5 => per_service_rec.attribute5
2482 ,p_attribute6 => per_service_rec.attribute6
2483 ,p_attribute7 => per_service_rec.attribute7
2484 ,p_attribute8 => per_service_rec.attribute8
2485 ,p_attribute9 => per_service_rec.attribute9
2486 ,p_attribute10 => per_service_rec.attribute10
2487 ,p_attribute11 => per_service_rec.attribute11
2488 ,p_attribute12 => per_service_rec.attribute12
2489 ,p_attribute13 => per_service_rec.attribute13
2490 ,p_attribute14 => per_service_rec.attribute14
2491 ,p_attribute15 => per_service_rec.attribute15
2492 ,p_attribute16 => per_service_rec.attribute16
2493 ,p_attribute17 => per_service_rec.attribute17
2494 ,p_attribute18 => per_service_rec.attribute18
2495 ,p_attribute19 => per_service_rec.attribute19
2496 ,p_attribute20 => per_service_rec.attribute20
2497 ,p_ADJUSTED_SVC_DATE => per_service_rec.ADJUSTED_SVC_DATE
2498 ,p_pds_information_category => per_service_rec.PDS_INFORMATION_CATEGORY
2499 ,p_pds_information1 => per_service_rec.PDS_INFORMATION1
2500 ,p_pds_information2 => per_service_rec.PDS_INFORMATION2
2501 ,p_pds_information3 => per_service_rec.PDS_INFORMATION3
2502 ,p_pds_information4 => per_service_rec.PDS_INFORMATION4
2503 ,p_pds_information5 => per_service_rec.PDS_INFORMATION5
2504 ,p_pds_information6 => per_service_rec.PDS_INFORMATION6
2505 ,p_pds_information7 => per_service_rec.PDS_INFORMATION7
2506 ,p_pds_information8 => per_service_rec.PDS_INFORMATION8
2507 ,p_pds_information9 => per_service_rec.PDS_INFORMATION9
2508 ,p_pds_information10 => per_service_rec.PDS_INFORMATION10
2509 ,p_pds_information11 => per_service_rec.PDS_INFORMATION11
2510 ,p_pds_information12 => per_service_rec.PDS_INFORMATION12
2511 ,p_pds_information13 => per_service_rec.PDS_INFORMATION13
2512 ,p_pds_information14 => per_service_rec.PDS_INFORMATION14
2513 ,p_pds_information15 => per_service_rec.PDS_INFORMATION15
2514 ,p_pds_information16 => per_service_rec.PDS_INFORMATION16
2515 ,p_pds_information17 => per_service_rec.PDS_INFORMATION17
2516 ,p_pds_information18 => per_service_rec.PDS_INFORMATION18
2517 ,p_pds_information19 => per_service_rec.PDS_INFORMATION19
2518 ,p_pds_information20 => per_service_rec.PDS_INFORMATION20
2519 ,p_pds_information21 => per_service_rec.PDS_INFORMATION21
2520 ,p_pds_information22 => per_service_rec.PDS_INFORMATION22
2521 ,p_pds_information23 => per_service_rec.PDS_INFORMATION23
2522 ,p_pds_information24 => per_service_rec.PDS_INFORMATION24
2523 ,p_pds_information25 => per_service_rec.PDS_INFORMATION25
2524 ,p_pds_information26 => per_service_rec.PDS_INFORMATION26
2525 ,p_pds_information27 => per_service_rec.PDS_INFORMATION27
2526 ,p_pds_information28 => per_service_rec.PDS_INFORMATION28
2527 ,p_pds_information29 => per_service_rec.PDS_INFORMATION29
2528 ,p_pds_information30 => per_service_rec.PDS_INFORMATION30
2529 );
2530 end if;
2531 hr_utility.set_location(' Leaving: WATIF_PERIODS_OF_SERVICE_API' , 10);
2532 --
2533 end;
2534
2535
2536
2537 procedure WATIF_PERSON_TYPE_USAGES_F_API(
2538 p_person_id in number
2539 ,p_PERSON_TYPE_ID in varchar2
2540 ,p_business_group_id in number
2541 ,p_effective_date in date
2542 ) is
2543 --
2544 per_per_usg_rec per_person_type_usages_f%rowtype;
2545 --
2546 cursor c_per_usg is
2547 select * from per_person_type_usages_f
2548 where person_id = p_person_id and
2549 p_effective_date between nvl(effective_start_date, p_effective_date)
2550 and nvl(effective_end_date, p_effective_date);
2551 --
2552 cursor c_get_per_typ is
2553 select pt.system_person_type, ptu.*
2554 from per_all_people_f per,
2555 per_person_types pt,
2556 per_person_type_usages_f ptu
2557 where per.person_id = p_person_id
2558 and per.person_type_id = pt.person_type_id
2559 and per.person_id = ptu.person_id
2560 and ptu.person_type_id = pt.person_type_id
2561 and p_effective_date between
2562 nvl(ptu.effective_start_date, p_effective_date)
2563 and nvl(ptu.effective_end_date, p_effective_date)
2564 and p_effective_date between
2565 nvl(per.effective_start_date, p_effective_date)
2566 and nvl(per.effective_end_date, p_effective_date);
2567 --
2568 cursor c_get_desired_per_typ is
2569 select pt.system_person_type
2570 from per_person_types pt
2571 where pt.person_type_id = p_person_type_id
2572 and pt.BUSINESS_GROUP_ID = p_BUSINESS_GROUP_ID
2573 and pt.ACTIVE_FLAG = 'Y';
2574 --
2575 l_sys_per_typ_rec c_get_per_typ%rowtype;
2576 l_sys_per_typ varchar2(100);
2577 l_upd_mode varchar2(25);
2578 l_effective_start_date date;
2579 l_effective_end_date date;
2580 l_desired_per_typ varchar2(100);
2581 --
2582 cursor c_service is
2583 select rowid, per_periods_of_service.*
2584 from per_periods_of_service
2585 where person_id = p_person_id
2586 order by DATE_START;-- and
2587 -- p_effective_date between nvl(DATE_START, p_effective_date)
2588 -- and nvl(DATE_END, p_effective_date);
2589 --
2590 per_service_rec c_service%rowtype;
2591 --
2592 l_supervisor_warning boolean;
2593 l_event_warning boolean;
2594 l_interview_warning boolean;
2595 l_review_warning boolean;
2596 l_recruiter_warning boolean;
2597 l_dod_warning boolean;
2598 l_asg_future_changes_warning boolean;
2599 l_entries_changed_warning varchar2(1000);
2600 l_pay_proposal_warning boolean;
2601 l_last_standard_process_date date;
2602 l_dummy_date date;
2603 l_dummy_num number;
2604 l_desired_per_typ_created boolean := false;
2605 --
2606 --
2607 l_old_ptu_rec ben_ptu_ler.g_ptu_ler_rec;
2608 l_new_ptu_rec ben_ptu_ler.g_ptu_ler_rec;
2609 --
2610 begin
2611 --
2612 hr_utility.set_location(' Entering: WATIF_PERSON_TYPE_USAGES_F_API' , 10);
2613 --
2614 If p_PERSON_TYPE_ID is null then
2615 --
2616 fnd_message.set_name('BEN','BEN_91447_ALL_NULL');
2617 fnd_message.raise_error;
2618 --
2619 End If;
2620 --
2621 open c_per_usg;
2622 fetch c_per_usg into per_per_usg_rec;
2623 if c_per_usg%notfound Then
2624 Close c_per_usg;
2625 --
2626 -- Person Usage Type Record is Not found so error.
2627 --
2628 fnd_message.set_name('BEN','BEN_91446_NO_PER_USG');
2629 fnd_message.raise_error;
2630 --
2631 End If;
2632 Close c_per_usg;
2633 --
2634 -- Fixed 1558516
2635 --
2636 open c_get_per_typ;
2637 fetch c_get_per_typ into l_sys_per_typ_rec;
2638 close c_get_per_typ;
2639 l_sys_per_typ := l_sys_per_typ_rec.system_person_type;
2640 --
2641 -- As person type usage can't be directly updated
2642 -- we need to call the potential creation logic from here
2643 --
2644 l_old_ptu_rec.PERSON_ID := p_person_id;
2645 l_old_ptu_rec.BUSINESS_GROUP_ID := p_business_group_id;
2646 l_old_ptu_rec.PERSON_TYPE_ID := l_sys_per_typ_rec.PERSON_TYPE_ID;
2647 l_old_ptu_rec.PERSON_TYPE_USAGE_ID := l_sys_per_typ_rec.PERSON_TYPE_USAGE_ID;
2648 l_old_ptu_rec.EFFECTIVE_START_DATE := l_sys_per_typ_rec.EFFECTIVE_START_DATE;
2649 l_old_ptu_rec.EFFECTIVE_END_DATE := l_sys_per_typ_rec.EFFECTIVE_END_DATE;
2650
2651 l_new_ptu_rec.PERSON_ID := p_person_id;
2652 l_new_ptu_rec.BUSINESS_GROUP_ID := p_business_group_id;
2653 l_new_ptu_rec.PERSON_TYPE_ID := p_person_type_id;
2654 l_new_ptu_rec.EFFECTIVE_START_DATE := p_effective_date;
2655 l_new_ptu_rec.EFFECTIVE_END_DATE := null;
2656
2657 hr_utility.set_location('l_old_ptu_rec.PERSON_ID ' || l_old_ptu_rec.PERSON_ID, 9999);
2658 hr_utility.set_location('l_old_ptu_rec.BUSINESS_GROUP_ID ' || l_old_ptu_rec.BUSINESS_GROUP_ID, 9999);
2659 hr_utility.set_location('l_old_ptu_rec.PERSON_TYPE_ID ' || l_old_ptu_rec.PERSON_TYPE_ID, 9999);
2660 hr_utility.set_location('l_old_ptu_rec.PERSON_TYPE_USAGE_ID ' || l_old_ptu_rec.PERSON_TYPE_USAGE_ID, 9999);
2661 hr_utility.set_location('l_old_ptu_rec.EFFECTIVE_START_DATE ' || l_old_ptu_rec.EFFECTIVE_START_DATE, 9999);
2662 hr_utility.set_location('l_old_ptu_rec.EFFECTIVE_END_DATE ' || l_old_ptu_rec.EFFECTIVE_START_DATE, 9999);
2663
2664 hr_utility.set_location('l_new_ptu_rec.PERSON_ID ' || l_old_ptu_rec.PERSON_ID, 9999);
2665 hr_utility.set_location('l_new_ptu_rec.BUSINESS_GROUP_ID ' || l_old_ptu_rec.BUSINESS_GROUP_ID, 9999);
2666 hr_utility.set_location('l_new_ptu_rec.PERSON_TYPE_ID ' || l_old_ptu_rec.PERSON_TYPE_ID, 9999);
2667 hr_utility.set_location('l_new_ptu_rec.EFFECTIVE_START_DATE ' || l_old_ptu_rec.EFFECTIVE_START_DATE, 9999);
2668 hr_utility.set_location('l_new_ptu_rec.EFFECTIVE_END_DATE ' || l_old_ptu_rec.EFFECTIVE_END_DATE, 9999);
2669
2670 ben_ptu_ler.ler_chk(p_old => l_old_ptu_rec
2671 ,p_new => l_new_ptu_rec
2672 ,p_effective_date => p_effective_date);
2673
2674 open c_get_desired_per_typ;
2675 fetch c_get_desired_per_typ into l_desired_per_typ;
2676 close c_get_desired_per_typ;
2677 --
2678 if l_desired_per_typ = 'EX_EMP' then
2679 l_desired_per_typ_created := true;
2680 end if;
2681 --
2682 if not l_desired_per_typ_created then
2683 --
2684 -- hr_person_type_usage_api.CREATE_PERSON_TYPE_USAGE Bug 3374767
2685 hr_per_type_usage_internal.CREATE_PERSON_TYPE_USAGE
2686 (
2687 p_validate => FALSE
2688 ,p_person_id => p_person_id
2689 ,p_person_type_id => p_person_type_id
2690 ,p_effective_date => p_effective_date
2691 ,p_person_type_usage_id => l_dummy_num
2692 ,p_object_version_number => l_dummy_num
2693 ,p_effective_start_date => l_dummy_date
2694 ,p_effective_end_date => l_dummy_date
2695 );
2696 --
2697 end if;
2698 --
2699 hr_utility.set_location('Person system type = ' || l_sys_per_typ, 30);
2700 hr_utility.set_location('Desired Person type = ' || l_desired_per_typ, 31);
2701 --
2702 if p_PERSON_TYPE_ID is not null then
2703 per_per_usg_rec.PERSON_TYPE_ID := p_PERSON_TYPE_ID;
2704 end if;
2705 --
2706 -- If person is employee and the desired person type is
2707 -- Ex-emp/ex-emp-apl/retiree then terminate the person.
2708 --
2709 if l_sys_per_typ = 'EMP' and
2710 l_desired_per_typ in ('EX_EMP', 'EX_EMP_APL', 'RETIREE')
2711 then
2712 --
2713 open c_service;
2714 fetch c_service into per_service_rec;
2715 if c_service%notfound Then
2716 Close c_service;
2717 --
2718 -- Periods of Service Record is Not found so error.
2719 --
2720 fnd_message.set_name('BEN','BEN_91445_NO_PERIODS_SERVICE');
2721 fnd_message.raise_error;
2722 --
2723 End If;
2724 Close c_service;
2725 --
2726 hr_ex_employee_api.actual_termination_emp
2727 (p_validate => FALSE
2728 ,p_effective_date => p_effective_date
2729 ,p_period_of_service_id => per_service_rec.period_of_service_id
2730 ,p_object_version_number => per_service_rec.object_version_number
2731 ,p_actual_termination_date => p_effective_date
2732 ,p_last_standard_process_date => l_last_standard_process_date
2733 -- ,p_person_type_id => per_per_usg_rec.PERSON_TYPE_ID
2734 -- ,p_assignment_status_type_id in number default hr_api.g_number
2735 ,p_supervisor_warning => l_supervisor_warning
2736 ,p_event_warning => l_event_warning
2737 ,p_interview_warning => l_interview_warning
2738 ,p_review_warning => l_review_warning
2739 ,p_recruiter_warning => l_recruiter_warning
2740 ,p_asg_future_changes_warning => l_asg_future_changes_warning
2741 ,p_entries_changed_warning => l_entries_changed_warning
2742 ,p_pay_proposal_warning => l_pay_proposal_warning
2743 ,p_dod_warning => l_dod_warning
2744 );
2745 --
2746 if l_desired_per_typ = 'EX_EMP' then
2747 l_desired_per_typ_created := true;
2748 end if;
2749 --
2750 end if;
2751 --
2752 /*
2753 if not l_desired_per_typ_created then
2754 --
2755 hr_person_type_usage_api.CREATE_PERSON_TYPE_USAGE
2756 (
2757 p_validate => FALSE
2758 ,p_person_id => p_person_id
2759 ,p_person_type_id => p_person_type_id
2760 ,p_effective_date => p_effective_date
2761 ,p_person_type_usage_id => l_dummy_num
2762 ,p_object_version_number => l_dummy_num
2763 ,p_effective_start_date => l_dummy_date
2764 ,p_effective_end_date => l_dummy_date
2765 );
2766 --
2767 end if;
2768 --
2769 */
2770 /*
2771 Get_DT_Upd_Mode
2772 (p_effective_date => p_effective_date,
2773 p_base_table_name => 'PER_PERSON_TYPE_USAGES_F',
2774 p_base_key_column => 'PERSON_TYPE_USAGE_ID',
2775 p_base_key_value => per_per_usg_rec.PERSON_TYPE_USAGE_ID,
2776 p_mode => l_upd_mode);
2777 --
2778 -- Call the API -
2779 --
2780 hr_utility.set_location('Before hr_person_type_usage_api ' ||
2781 to_char(per_per_usg_rec.person_type_id), 10);
2782 hr_person_type_usage_api.update_person_type_usage
2783 (p_validate => FALSE
2784 ,p_person_type_usage_id => per_per_usg_rec.PERSON_TYPE_USAGE_ID
2785 ,p_effective_date => p_effective_date
2786 ,p_datetrack_mode => 'CORRECTION' -- always use correction as this api does't allow any other mode. l_upd_mode
2787 ,p_object_version_number => per_per_usg_rec.object_version_number
2788 ,p_person_type_id => per_per_usg_rec.person_type_id
2789 ,p_attribute_category => per_per_usg_rec.attribute_category
2790 ,p_attribute1 => per_per_usg_rec.attribute1
2791 ,p_attribute2 => per_per_usg_rec.attribute2
2792 ,p_attribute3 => per_per_usg_rec.attribute3
2793 ,p_attribute4 => per_per_usg_rec.attribute4
2794 ,p_attribute5 => per_per_usg_rec.attribute5
2795 ,p_attribute6 => per_per_usg_rec.attribute6
2796 ,p_attribute7 => per_per_usg_rec.attribute7
2797 ,p_attribute8 => per_per_usg_rec.attribute8
2798 ,p_attribute9 => per_per_usg_rec.attribute9
2799 ,p_attribute10 => per_per_usg_rec.attribute10
2800 ,p_attribute11 => per_per_usg_rec.attribute11
2801 ,p_attribute12 => per_per_usg_rec.attribute12
2802 ,p_attribute13 => per_per_usg_rec.attribute13
2803 ,p_attribute14 => per_per_usg_rec.attribute14
2804 ,p_attribute15 => per_per_usg_rec.attribute15
2805 ,p_attribute16 => per_per_usg_rec.attribute16
2806 ,p_attribute17 => per_per_usg_rec.attribute17
2807 ,p_attribute18 => per_per_usg_rec.attribute18
2808 ,p_attribute19 => per_per_usg_rec.attribute19
2809 ,p_attribute20 => per_per_usg_rec.attribute20
2810 ,p_effective_start_date => l_effective_start_date
2811 ,p_effective_end_date => l_effective_end_date
2812 );
2813 */
2814 --
2815 hr_utility.set_location(' Leaving: WATIF_PERSON_TYPE_USAGES_F_API' , 10);
2816 --
2817 end;
2818 --
2819
2820 procedure WATIF_CRT_ORDR_API(
2821 p_person_id in number
2822 ,p_pl_id in number
2823 ,p_CRT_ORDR_TYP_CD in varchar2
2824 ,p_APLS_PERD_STRTG_DT in date
2825 ,p_APLS_PERD_ENDG_DT in date
2826 ,p_business_group_id in number
2827 ,p_effective_date in date
2828 ) is
2829 --
2830 cursor c_crt_ordr is
2831 select * from ben_crt_ordr
2832 where person_id = p_person_id and
2833 crt_ordr_typ_cd = p_crt_ordr_typ_cd and
2834 business_group_id = p_business_group_id and
2835 p_effective_date between apls_perd_strtg_dt
2836 and apls_perd_endg_dt;
2837 --
2838 per_crt_ordr_rec c_crt_ordr%rowtype;
2839 l_object_version_number number;
2840 --
2841 begin
2842 --
2843 hr_utility.set_location(' Leaving: WATIF_CRT_ORDR_API' , 10);
2844 --
2845 If p_CRT_ORDR_TYP_CD is null and p_APLS_PERD_STRTG_DT is null and
2846 p_APLS_PERD_ENDG_DT is null and p_pl_id is null
2847 then
2848 --
2849 fnd_message.set_name('BEN','BEN_91447_ALL_NULL');
2850 fnd_message.raise_error;
2851 --
2852 End If;
2853 --
2854 open c_crt_ordr;
2855 fetch c_crt_ordr into per_crt_ordr_rec;
2856 --
2857 if p_crt_ordr_typ_cd is not null then
2858 per_crt_ordr_rec.crt_ordr_typ_cd := p_crt_ordr_typ_cd;
2859 end if;
2860 --
2861 if p_apls_perd_strtg_dt is not null then
2862 per_crt_ordr_rec.apls_perd_strtg_dt := p_apls_perd_strtg_dt;
2863 end if;
2864 --
2865 if p_apls_perd_endg_dt is not null then
2866 per_crt_ordr_rec.apls_perd_endg_dt := p_apls_perd_endg_dt;
2867 end if;
2868 --
2869 if p_pl_id is not null then
2870 per_crt_ordr_rec.pl_id := p_pl_id;
2871 end if;
2872 --
2873 if p_crt_ordr_typ_cd = 'QDRO' and per_crt_ordr_rec.qdro_pct is null then
2874 --
2875 -- If crt_ordr_typ_cd = 'QDRO' then qdro_pct is defaulted to 10
2876 -- 9999 : Does this assumption correct?
2877 --
2878 per_crt_ordr_rec.qdro_pct := 10;
2879 --
2880 end if;
2881 --
2882 if c_crt_ordr%notfound Then
2883 --
2884 -- Error out as pl_id can't be determined.
2885 --
2886 BEN_court_orders_API.create_court_orders
2887 (
2888 p_validate => false
2889 ,p_crt_ordr_id => per_crt_ordr_rec.CRT_ORDR_ID
2890 ,p_crt_ordr_typ_cd => per_crt_ordr_rec.CRT_ORDR_TYP_CD
2891 ,p_apls_perd_endg_dt => per_crt_ordr_rec.APLS_PERD_ENDG_DT
2892 ,p_apls_perd_strtg_dt => per_crt_ordr_rec.APLS_PERD_STRTG_DT
2893 ,p_crt_ident => null
2894 ,p_description => null
2895 ,p_detd_qlfd_ordr_dt => null
2896 ,p_issue_dt => null
2897 ,p_qdro_amt => null
2898 ,p_qdro_dstr_mthd_cd => null
2899 ,p_qdro_pct => per_crt_ordr_rec.qdro_pct
2900 ,p_qdro_num_pymt_val => null
2901 ,p_qdro_per_perd_cd => null
2902 ,p_rcvd_dt => null
2903 ,p_uom => null
2904 ,p_crt_issng => null
2905 ,p_pl_id => p_pl_id
2906 ,p_pl_typ_id => null -- p_pl_typ_id
2907 ,p_person_id => p_person_id
2908 ,p_business_group_id => p_business_group_id
2909 ,p_crt_attribute_category => null
2910 ,p_crt_attribute1 => null
2911 ,p_crt_attribute2 => null
2912 ,p_crt_attribute3 => null
2913 ,p_crt_attribute4 => null
2914 ,p_crt_attribute5 => null
2915 ,p_crt_attribute6 => null
2916 ,p_crt_attribute7 => null
2917 ,p_crt_attribute8 => null
2918 ,p_crt_attribute9 => null
2919 ,p_crt_attribute10 => null
2920 ,p_crt_attribute11 => null
2921 ,p_crt_attribute12 => null
2922 ,p_crt_attribute13 => null
2923 ,p_crt_attribute14 => null
2924 ,p_crt_attribute15 => null
2925 ,p_crt_attribute16 => null
2926 ,p_crt_attribute17 => null
2927 ,p_crt_attribute18 => null
2928 ,p_crt_attribute19 => null
2929 ,p_crt_attribute20 => null
2930 ,p_crt_attribute21 => null
2931 ,p_crt_attribute22 => null
2932 ,p_crt_attribute23 => null
2933 ,p_crt_attribute24 => null
2934 ,p_crt_attribute25 => null
2935 ,p_crt_attribute26 => null
2936 ,p_crt_attribute27 => null
2937 ,p_crt_attribute28 => null
2938 ,p_crt_attribute29 => null
2939 ,p_crt_attribute30 => null
2940 ,p_object_version_number => l_object_version_number
2941 ,p_effective_date => p_effective_date
2942 );
2943 null;
2944 else
2945 --
2946 BEN_court_orders_API.update_court_orders
2947 (p_validate => FALSE
2948 ,p_CRT_ORDR_ID => per_crt_ordr_rec.CRT_ORDR_ID
2949 ,p_CRT_ORDR_TYP_CD => per_crt_ordr_rec.CRT_ORDR_TYP_CD
2950 ,p_APLS_PERD_ENDG_DT => per_crt_ordr_rec.APLS_PERD_ENDG_DT
2951 ,p_APLS_PERD_STRTG_DT => per_crt_ordr_rec.APLS_PERD_STRTG_DT
2952 ,p_CRT_IDENT => per_crt_ordr_rec.CRT_IDENT
2953 ,p_DESCRIPTION => per_crt_ordr_rec.DESCRIPTION
2954 ,p_DETD_QLFD_ORDR_DT => per_crt_ordr_rec.DETD_QLFD_ORDR_DT
2955 ,p_ISSUE_DT => per_crt_ordr_rec.ISSUE_DT
2956 ,p_QDRO_AMT => per_crt_ordr_rec.QDRO_AMT
2957 ,p_QDRO_DSTR_MTHD_CD => per_crt_ordr_rec.QDRO_DSTR_MTHD_CD
2958 ,p_QDRO_PCT => per_crt_ordr_rec.QDRO_PCT
2959 ,p_QDRO_NUM_PYMT_VAL => per_crt_ordr_rec.QDRO_NUM_PYMT_VAL
2960 ,p_QDRO_PER_PERD_CD => per_crt_ordr_rec.QDRO_PER_PERD_CD
2961 ,p_RCVD_DT => per_crt_ordr_rec.RCVD_DT
2962 ,p_UOM => per_crt_ordr_rec.UOM
2963 ,p_CRT_ISSNG => per_crt_ordr_rec.CRT_ISSNG
2964 ,p_PL_ID => per_crt_ordr_rec.PL_ID
2965 ,p_PL_typ_id => per_crt_ordr_rec.PL_typ_id
2966 ,p_PERSON_ID => per_crt_ordr_rec.PERSON_ID
2967 ,p_BUSINESS_GROUP_ID => per_crt_ordr_rec.BUSINESS_GROUP_ID
2968 ,p_CRT_ATTRIBUTE_CATEGORY => per_crt_ordr_rec.CRT_ATTRIBUTE_CATEGORY
2969 ,p_CRT_ATTRIBUTE1 => per_crt_ordr_rec.CRT_ATTRIBUTE1
2970 ,p_CRT_ATTRIBUTE2 => per_crt_ordr_rec.CRT_ATTRIBUTE2
2971 ,p_CRT_ATTRIBUTE3 => per_crt_ordr_rec.CRT_ATTRIBUTE3
2972 ,p_CRT_ATTRIBUTE4 => per_crt_ordr_rec.CRT_ATTRIBUTE4
2973 ,p_CRT_ATTRIBUTE5 => per_crt_ordr_rec.CRT_ATTRIBUTE5
2974 ,p_CRT_ATTRIBUTE6 => per_crt_ordr_rec.CRT_ATTRIBUTE6
2975 ,p_CRT_ATTRIBUTE7 => per_crt_ordr_rec.CRT_ATTRIBUTE7
2976 ,p_CRT_ATTRIBUTE8 => per_crt_ordr_rec.CRT_ATTRIBUTE8
2977 ,p_CRT_ATTRIBUTE9 => per_crt_ordr_rec.CRT_ATTRIBUTE9
2978 ,p_CRT_ATTRIBUTE10 => per_crt_ordr_rec.CRT_ATTRIBUTE10
2979 ,p_CRT_ATTRIBUTE11 => per_crt_ordr_rec.CRT_ATTRIBUTE11
2980 ,p_CRT_ATTRIBUTE12 => per_crt_ordr_rec.CRT_ATTRIBUTE12
2981 ,p_CRT_ATTRIBUTE13 => per_crt_ordr_rec.CRT_ATTRIBUTE13
2982 ,p_CRT_ATTRIBUTE14 => per_crt_ordr_rec.CRT_ATTRIBUTE14
2983 ,p_CRT_ATTRIBUTE15 => per_crt_ordr_rec.CRT_ATTRIBUTE15
2984 ,p_CRT_ATTRIBUTE16 => per_crt_ordr_rec.CRT_ATTRIBUTE16
2985 ,p_CRT_ATTRIBUTE17 => per_crt_ordr_rec.CRT_ATTRIBUTE17
2986 ,p_CRT_ATTRIBUTE18 => per_crt_ordr_rec.CRT_ATTRIBUTE18
2987 ,p_CRT_ATTRIBUTE19 => per_crt_ordr_rec.CRT_ATTRIBUTE19
2988 ,p_CRT_ATTRIBUTE20 => per_crt_ordr_rec.CRT_ATTRIBUTE20
2989 ,p_CRT_ATTRIBUTE21 => per_crt_ordr_rec.CRT_ATTRIBUTE21
2990 ,p_CRT_ATTRIBUTE22 => per_crt_ordr_rec.CRT_ATTRIBUTE22
2991 ,p_CRT_ATTRIBUTE23 => per_crt_ordr_rec.CRT_ATTRIBUTE23
2992 ,p_CRT_ATTRIBUTE24 => per_crt_ordr_rec.CRT_ATTRIBUTE24
2993 ,p_CRT_ATTRIBUTE25 => per_crt_ordr_rec.CRT_ATTRIBUTE25
2994 ,p_CRT_ATTRIBUTE26 => per_crt_ordr_rec.CRT_ATTRIBUTE26
2995 ,p_CRT_ATTRIBUTE27 => per_crt_ordr_rec.CRT_ATTRIBUTE27
2996 ,p_CRT_ATTRIBUTE28 => per_crt_ordr_rec.CRT_ATTRIBUTE28
2997 ,p_CRT_ATTRIBUTE29 => per_crt_ordr_rec.CRT_ATTRIBUTE29
2998 ,p_CRT_ATTRIBUTE30 => per_crt_ordr_rec.CRT_ATTRIBUTE30
2999 ,p_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER
3000 ,p_effective_date => p_effective_date);
3001 --
3002 end if;
3003 --
3004 hr_utility.set_location(' Leaving: WATIF_CRT_ORDR_API' , 80);
3005 --
3006 end;
3007
3008
3009 procedure WATIF_ELIG_CVRD_DPNT_F_API(
3010 p_person_id in number
3011 ,p_CVG_STRT_DT in date
3012 ,p_CVG_THRU_DT in date
3013 ,p_EFFECTIVE_START_DATE in date
3014 ,p_EFFECTIVE_END_DATE in date
3015 ,p_business_group_id in number
3016 ,p_effective_date in date
3017 ) is
3018 --
3019 cursor c_cvrd_dpnt is
3020 select * from ben_elig_cvrd_dpnt_f;
3021 /* where person_id = p_person_id and
3022 bnfts_bal_id = p_BNFTS_BAL_ID and
3023 business_group_id = p_business_group_id and
3024 p_effective_date between effective_start_date
3025 and effective_end_date; */
3026 --
3027 l_effective_start_date date;
3028 l_effective_end_date date;
3029 l_object_version_number number;
3030 l_upd_mode varchar2(50);
3031 --
3032 cvrd_dpnt_rec ben_elig_cvrd_dpnt_f%rowtype;
3033 --
3034 begin
3035 --
3036 hr_utility.set_location(' Entering: WATIF_ELIG_CVRD_DPNT_F_API' , 10);
3037 hr_utility.set_location(' Entering: WATIF_ELIG_CVRD_DPNT_F_API' , 80);
3038 --
3039 end;
3040
3041
3042 procedure WATIF_PER_ASG_BUDG_VAL_F_API(
3043 p_person_id in number
3044 ,p_ASSIGNMENT_BUDGET_VALUE_ID in number
3045 ,p_VALUE in number
3046 ,p_business_group_id in number
3047 ,p_effective_date in date
3048 ) is
3049 --
3050 cursor c_asg_bud is
3051 select abv.*
3052 from per_assignment_budget_values_f abv,
3053 per_all_assignments_f asg
3054 where asg.person_id = p_person_id and
3055 asg.primary_flag = 'Y'and
3056 asg.assignment_type <> 'C' and
3057 p_effective_date between nvl(asg.effective_start_date, p_effective_date)
3058 and nvl(asg.effective_end_date, p_effective_date) and
3059 abv.ASSIGNMENT_ID = asg.ASSIGNMENT_ID and
3060 abv.ASSIGNMENT_BUDGET_VALUE_ID = p_ASSIGNMENT_BUDGET_VALUE_ID and
3061 abv.business_group_id = p_business_group_id and
3062 p_effective_date between abv.effective_start_date
3063 and abv.effective_end_date;
3064 --
3065 l_effective_start_date date;
3066 l_effective_end_date date;
3067 l_object_version_number number;
3068 l_upd_mode varchar2(50);
3069 --
3070 asg_bud_rec per_assignment_budget_values_f%rowtype;
3071 --
3072 begin
3073 --
3074 hr_utility.set_location(' Entering: WATIF_PER_ASG_BUDG_VAL_F_API' , 10);
3075 --
3076 If p_ASSIGNMENT_BUDGET_VALUE_ID is null and p_VALUE is null
3077 then
3078 --
3079 fnd_message.set_name('BEN','BEN_91447_ALL_NULL');
3080 fnd_message.raise_error;
3081 --
3082 End If;
3083 --
3084 open c_asg_bud;
3085 fetch c_asg_bud into asg_bud_rec;
3086 --
3087 if p_VALUE is not null then
3088 asg_bud_rec.VALUE := p_VALUE;
3089 end if;
3090 --
3091 if p_ASSIGNMENT_BUDGET_VALUE_ID is not null then
3092 asg_bud_rec.ASSIGNMENT_BUDGET_VALUE_ID := p_ASSIGNMENT_BUDGET_VALUE_ID;
3093 end if;
3094 --
3095 if c_asg_bud%notfound Then
3096 --
3097 -- Error out as the record not exists.
3098 --
3099 null;
3100 else
3101 --
3102 update per_assignment_budget_values_f
3103 set value = asg_bud_rec.VALUE
3104 where assignment_budget_value_id = p_ASSIGNMENT_BUDGET_VALUE_ID
3105 and business_group_id = p_business_group_id
3106 and p_effective_date between effective_start_date
3107 and effective_end_date;
3108 --
3109 end if;
3110 --
3111 close c_asg_bud;
3112 --
3113 hr_utility.set_location(' Entering: WATIF_PER_ASG_BUDG_VAL_F_API' , 80);
3114 --
3115 end;
3116
3117
3118
3119 procedure WATIF_PER_BNFTS_BAL_F_API(
3120 p_person_id in number
3121 ,p_BNFTS_BAL_ID in number
3122 ,p_VAL in number
3123 ,p_EFFECTIVE_START_DATE in date
3124 ,p_EFFECTIVE_END_DATE in date
3125 ,p_business_group_id in number
3126 ,p_effective_date in date
3127 )is
3128 --
3129 cursor c_bnfts_bal is
3130 select * from ben_per_bnfts_bal_f
3131 where person_id = p_person_id and
3132 bnfts_bal_id = p_BNFTS_BAL_ID and
3133 business_group_id = p_business_group_id and
3134 p_effective_date between effective_start_date
3135 and effective_end_date;
3136 --
3137 l_per_bnfts_bal_id number;
3138 l_effective_start_date date;
3139 l_effective_end_date date;
3140 l_object_version_number number;
3141 l_upd_mode varchar2(50);
3142 --
3143 per_bnft_bal_rec ben_per_bnfts_bal_f%rowtype;
3144 --
3145 begin
3146 --
3147 hr_utility.set_location(' Entering: WATIF_PER_BNFTS_BAL_F_API' , 10);
3148 --
3149 If p_BNFTS_BAL_ID is null and p_VAL is null and
3150 p_EFFECTIVE_START_DATE is null and p_EFFECTIVE_END_DATE is null
3151 then
3152 --
3153 fnd_message.set_name('BEN','BEN_91447_ALL_NULL');
3154 fnd_message.raise_error;
3155 --
3156 End If;
3157 --
3158 open c_bnfts_bal;
3159 fetch c_bnfts_bal into per_bnft_bal_rec;
3160 --
3161 if p_val is not null then
3162 per_bnft_bal_rec.val := p_val;
3163 end if;
3164 --
3165 if p_bnfts_bal_id is not null then
3166 per_bnft_bal_rec.bnfts_bal_id := p_bnfts_bal_id;
3167 end if;
3168 --
3169 if c_bnfts_bal%notfound Then
3170 --
3171 -- Create the benefit balance.
3172 --
3173 ben_person_bnfts_bal_api.create_person_bnfts_bal
3174 (
3175 p_validate => false
3176 ,p_per_bnfts_bal_id => l_per_bnfts_bal_id
3177 ,p_effective_start_date => l_effective_start_date
3178 ,p_effective_end_date => l_effective_end_date
3179 ,p_val => per_bnft_bal_rec.val
3180 ,p_bnfts_bal_id => p_bnfts_bal_id
3181 ,p_person_id => p_person_id
3182 ,p_business_group_id => p_business_group_id
3183 ,p_pbb_attribute_category => per_bnft_bal_rec.pbb_attribute_category
3184 ,p_pbb_attribute1 => per_bnft_bal_rec.pbb_attribute1
3185 ,p_pbb_attribute2 => per_bnft_bal_rec.pbb_attribute2
3186 ,p_pbb_attribute3 => per_bnft_bal_rec.pbb_attribute3
3187 ,p_pbb_attribute4 => per_bnft_bal_rec.pbb_attribute4
3188 ,p_pbb_attribute5 => per_bnft_bal_rec.pbb_attribute5
3189 ,p_pbb_attribute6 => per_bnft_bal_rec.pbb_attribute6
3190 ,p_pbb_attribute7 => per_bnft_bal_rec.pbb_attribute7
3191 ,p_pbb_attribute8 => per_bnft_bal_rec.pbb_attribute8
3192 ,p_pbb_attribute9 => per_bnft_bal_rec.pbb_attribute9
3193 ,p_pbb_attribute10 => per_bnft_bal_rec.pbb_attribute10
3194 ,p_pbb_attribute11 => per_bnft_bal_rec.pbb_attribute11
3195 ,p_pbb_attribute12 => per_bnft_bal_rec.pbb_attribute12
3196 ,p_pbb_attribute13 => per_bnft_bal_rec.pbb_attribute13
3197 ,p_pbb_attribute14 => per_bnft_bal_rec.pbb_attribute14
3198 ,p_pbb_attribute15 => per_bnft_bal_rec.pbb_attribute15
3199 ,p_pbb_attribute16 => per_bnft_bal_rec.pbb_attribute16
3200 ,p_pbb_attribute17 => per_bnft_bal_rec.pbb_attribute17
3201 ,p_pbb_attribute18 => per_bnft_bal_rec.pbb_attribute18
3202 ,p_pbb_attribute19 => per_bnft_bal_rec.pbb_attribute19
3203 ,p_pbb_attribute20 => per_bnft_bal_rec.pbb_attribute20
3204 ,p_pbb_attribute21 => per_bnft_bal_rec.pbb_attribute21
3205 ,p_pbb_attribute22 => per_bnft_bal_rec.pbb_attribute22
3206 ,p_pbb_attribute23 => per_bnft_bal_rec.pbb_attribute23
3207 ,p_pbb_attribute24 => per_bnft_bal_rec.pbb_attribute24
3208 ,p_pbb_attribute25 => per_bnft_bal_rec.pbb_attribute25
3209 ,p_pbb_attribute26 => per_bnft_bal_rec.pbb_attribute26
3210 ,p_pbb_attribute27 => per_bnft_bal_rec.pbb_attribute27
3211 ,p_pbb_attribute28 => per_bnft_bal_rec.pbb_attribute28
3212 ,p_pbb_attribute29 => per_bnft_bal_rec.pbb_attribute29
3213 ,p_pbb_attribute30 => per_bnft_bal_rec.pbb_attribute30
3214 ,p_object_version_number => l_object_version_number
3215 ,p_effective_date => p_effective_date
3216 );
3217 --
3218 else
3219 --
3220 Get_DT_Upd_Mode
3221 (p_effective_date => p_effective_date,
3222 p_base_table_name => 'BEN_PER_BNFTS_BAL_F',
3223 p_base_key_column => 'PER_BNFTS_BAL_ID',
3224 p_base_key_value => per_bnft_bal_rec.per_bnfts_bal_id,
3225 p_mode => l_upd_mode);
3226 --
3227 -- Update the benefit balance.
3228 --
3229 ben_person_bnfts_bal_api.update_person_bnfts_bal
3230 (
3231 p_validate => false
3232 ,p_per_bnfts_bal_id => per_bnft_bal_rec.per_bnfts_bal_id
3233 ,p_effective_start_date => l_effective_start_date
3234 ,p_effective_end_date => l_effective_end_date
3235 ,p_val => per_bnft_bal_rec.val
3236 ,p_bnfts_bal_id => p_bnfts_bal_id
3237 ,p_person_id => p_person_id
3238 ,p_business_group_id => p_business_group_id
3239 ,p_pbb_attribute_category => per_bnft_bal_rec.pbb_attribute_category
3240 ,p_pbb_attribute1 => per_bnft_bal_rec.pbb_attribute1
3241 ,p_pbb_attribute2 => per_bnft_bal_rec.pbb_attribute2
3242 ,p_pbb_attribute3 => per_bnft_bal_rec.pbb_attribute3
3243 ,p_pbb_attribute4 => per_bnft_bal_rec.pbb_attribute4
3244 ,p_pbb_attribute5 => per_bnft_bal_rec.pbb_attribute5
3245 ,p_pbb_attribute6 => per_bnft_bal_rec.pbb_attribute6
3246 ,p_pbb_attribute7 => per_bnft_bal_rec.pbb_attribute7
3247 ,p_pbb_attribute8 => per_bnft_bal_rec.pbb_attribute8
3248 ,p_pbb_attribute9 => per_bnft_bal_rec.pbb_attribute9
3249 ,p_pbb_attribute10 => per_bnft_bal_rec.pbb_attribute10
3250 ,p_pbb_attribute11 => per_bnft_bal_rec.pbb_attribute11
3251 ,p_pbb_attribute12 => per_bnft_bal_rec.pbb_attribute12
3252 ,p_pbb_attribute13 => per_bnft_bal_rec.pbb_attribute13
3253 ,p_pbb_attribute14 => per_bnft_bal_rec.pbb_attribute14
3254 ,p_pbb_attribute15 => per_bnft_bal_rec.pbb_attribute15
3255 ,p_pbb_attribute16 => per_bnft_bal_rec.pbb_attribute16
3256 ,p_pbb_attribute17 => per_bnft_bal_rec.pbb_attribute17
3257 ,p_pbb_attribute18 => per_bnft_bal_rec.pbb_attribute18
3258 ,p_pbb_attribute19 => per_bnft_bal_rec.pbb_attribute19
3259 ,p_pbb_attribute20 => per_bnft_bal_rec.pbb_attribute20
3260 ,p_pbb_attribute21 => per_bnft_bal_rec.pbb_attribute21
3261 ,p_pbb_attribute22 => per_bnft_bal_rec.pbb_attribute22
3262 ,p_pbb_attribute23 => per_bnft_bal_rec.pbb_attribute23
3263 ,p_pbb_attribute24 => per_bnft_bal_rec.pbb_attribute24
3264 ,p_pbb_attribute25 => per_bnft_bal_rec.pbb_attribute25
3265 ,p_pbb_attribute26 => per_bnft_bal_rec.pbb_attribute26
3266 ,p_pbb_attribute27 => per_bnft_bal_rec.pbb_attribute27
3267 ,p_pbb_attribute28 => per_bnft_bal_rec.pbb_attribute28
3268 ,p_pbb_attribute29 => per_bnft_bal_rec.pbb_attribute29
3269 ,p_pbb_attribute30 => per_bnft_bal_rec.pbb_attribute30
3270 ,p_object_version_number => per_bnft_bal_rec.object_version_number
3271 ,p_effective_date => p_effective_date
3272 ,p_datetrack_mode => l_upd_mode
3273 );
3274 --
3275 end if;
3276 Close c_bnfts_bal;
3277 --
3278 hr_utility.set_location(' Leaving: WATIF_PER_BNFTS_BAL_F_API' , 80);
3279 --
3280 end;
3281
3282
3283 --Bug 2831804
3284 procedure WATIF_PER_QUALIFICATIONS_API(
3285 p_person_id in number
3286 ,p_qualification_type_id in number
3287 ,p_title in varchar2
3288 ,p_start_date in date
3289 ,p_end_date in date
3290 ,p_attribute1 in varchar2
3291 ,p_attribute2 in varchar2
3292 ,p_attribute3 in varchar2
3293 ,p_attribute4 in varchar2
3294 ,p_attribute5 in varchar2
3295 ,p_attribute6 in varchar2
3296 ,p_attribute7 in varchar2
3297 ,p_attribute8 in varchar2
3298 ,p_attribute9 in varchar2
3299 ,p_attribute10 in varchar2
3300 ,p_attribute11 in varchar2
3301 ,p_attribute12 in varchar2
3302 ,p_attribute13 in varchar2
3303 ,p_attribute14 in varchar2
3304 ,p_attribute15 in varchar2
3305 ,p_attribute16 in varchar2
3306 ,p_attribute17 in varchar2
3307 ,p_attribute18 in varchar2
3308 ,p_attribute19 in varchar2
3309 ,p_attribute20 in varchar2
3310 ,p_business_group_id in number
3311 ,p_effective_date in date
3312 ) is
3313
3314 cursor c_person is
3315 select * from per_qualifications qua
3316 where person_id = p_person_id
3317 and qua.qualification_type_id = p_qualification_type_id
3318 and qua.business_group_id = p_business_group_id
3319 and p_effective_date between qua.start_date and nvl(qua.end_date, p_effective_date);
3320
3321 l_person_rec c_person%rowtype;
3322 l_object_version_number number;
3323 l_qualification_id number;
3324 per_qual_rec per_qualifications%rowtype;
3325 begin
3326
3327 if p_person_id is not null then
3328 per_qual_rec.person_id := p_person_id;
3329 end if;
3330
3331 if p_business_group_id is not null then
3332 per_qual_rec.business_group_id := p_business_group_id;
3333 end if;
3334
3335 if p_qualification_type_id is not null then
3336 per_qual_rec.qualification_type_id := p_qualification_type_id;
3337 end if;
3338
3339 if p_title is not null then
3340 per_qual_rec.title := p_title;
3341 end if;
3342
3343 if p_start_date is not null then
3344 per_qual_rec.start_date := p_start_date;
3345 end if;
3346
3347 if p_end_date is not null then
3348 per_qual_rec.end_date := p_end_date;
3349 end if;
3350
3351 if p_attribute1 is not null then
3352 per_qual_rec.attribute1 := p_attribute1;
3353 end if;
3354
3355 if p_attribute2 is not null then
3356 per_qual_rec.attribute2 := p_attribute2;
3357 end if;
3358
3359 if p_attribute3 is not null then
3360 per_qual_rec.attribute3 := p_attribute3;
3361 end if;
3362
3363 if p_attribute4 is not null then
3364 per_qual_rec.attribute4 := p_attribute4;
3365 end if;
3366
3367 if p_attribute5 is not null then
3368 per_qual_rec.attribute5 := p_attribute5;
3369 end if;
3370
3371 if p_attribute6 is not null then
3372 per_qual_rec.attribute6 := p_attribute6;
3373 end if;
3374
3375 if p_attribute7 is not null then
3376 per_qual_rec.attribute7 := p_attribute7;
3377 end if;
3378
3379 if p_attribute8 is not null then
3380 per_qual_rec.attribute8 := p_attribute8;
3381 end if;
3382
3383 if p_attribute9 is not null then
3384 per_qual_rec.attribute9 := p_attribute9;
3385 end if;
3386
3387 if p_attribute10 is not null then
3388 per_qual_rec.attribute10 := p_attribute10;
3389 end if;
3390
3391 if p_attribute11 is not null then
3392 per_qual_rec.attribute11 := p_attribute11;
3393 end if;
3394
3395 if p_attribute12 is not null then
3396 per_qual_rec.attribute12 := p_attribute12;
3397 end if;
3398
3399 if p_attribute13 is not null then
3400 per_qual_rec.attribute13 := p_attribute13;
3401 end if;
3402
3403 if p_attribute14 is not null then
3404 per_qual_rec.attribute14 := p_attribute14;
3405 end if;
3406
3407 if p_attribute15 is not null then
3408 per_qual_rec.attribute15 := p_attribute15;
3409 end if;
3410
3411 if p_attribute16 is not null then
3412 per_qual_rec.attribute16 := p_attribute16;
3413 end if;
3414
3415 if p_attribute17 is not null then
3416 per_qual_rec.attribute17 := p_attribute17;
3417 end if;
3418
3419 if p_attribute18 is not null then
3420 per_qual_rec.attribute18 := p_attribute18;
3421 end if;
3422
3423 if p_attribute19 is not null then
3424 per_qual_rec.attribute19 := p_attribute19;
3425 end if;
3426
3427 if p_attribute20 is not null then
3428 per_qual_rec.attribute20 := p_attribute20;
3429 end if;
3430
3431 open c_person;
3432 fetch c_person into l_person_rec;
3433 if c_person%found then
3434 close c_person;
3435 PER_QUALIFICATIONS_API.UPDATE_QUALIFICATION
3436 (p_validate => FALSE
3437 ,p_qualification_type_id => per_qual_rec.qualification_type_id
3438 ,p_title => per_qual_rec.title
3439 ,p_start_date => per_qual_rec.start_date
3440 ,p_end_date => per_qual_rec.end_date
3441 ,p_effective_date => p_effective_date
3442 ,p_attribute1 => per_qual_rec.attribute1
3443 ,p_attribute2 => per_qual_rec.attribute2
3444 ,p_attribute3 => per_qual_rec.attribute3
3445 ,p_attribute4 => per_qual_rec.attribute4
3446 ,p_attribute5 => per_qual_rec.attribute5
3447 ,p_attribute6 => per_qual_rec.attribute6
3448 ,p_attribute7 => per_qual_rec.attribute7
3449 ,p_attribute8 => per_qual_rec.attribute8
3450 ,p_attribute9 => per_qual_rec.attribute9
3451 ,p_attribute10 => per_qual_rec.attribute10
3452 ,p_attribute11 => per_qual_rec.attribute11
3453 ,p_attribute12 => per_qual_rec.attribute12
3454 ,p_attribute13 => per_qual_rec.attribute13
3455 ,p_attribute14 => per_qual_rec.attribute14
3456 ,p_attribute15 => per_qual_rec.attribute15
3457 ,p_attribute16 => per_qual_rec.attribute16
3458 ,p_attribute17 => per_qual_rec.attribute17
3459 ,p_attribute18 => per_qual_rec.attribute18
3460 ,p_attribute19 => per_qual_rec.attribute19
3461 ,p_attribute20 => per_qual_rec.attribute20
3462 ,p_qualification_id => l_person_rec.qualification_id
3463 ,p_object_version_number => l_person_rec.object_version_number
3464 );
3465 else
3466 close c_person;
3467 PER_QUALIFICATIONS_API.CREATE_QUALIFICATION
3468 (p_validate => FALSE
3469 ,p_effective_date => p_effective_date
3470 ,p_qualification_type_id => per_qual_rec.qualification_type_id
3471 ,p_business_group_id => per_qual_rec.business_group_id
3472 ,p_person_id => per_qual_rec.person_id
3473 ,p_title => per_qual_rec.title
3474 ,p_start_date => per_qual_rec.start_date
3475 ,p_end_date => per_qual_rec.end_date
3476 ,p_attribute1 => per_qual_rec.attribute1
3477 ,p_attribute2 => per_qual_rec.attribute2
3478 ,p_attribute3 => per_qual_rec.attribute3
3479 ,p_attribute4 => per_qual_rec.attribute4
3480 ,p_attribute5 => per_qual_rec.attribute5
3481 ,p_attribute6 => per_qual_rec.attribute6
3482 ,p_attribute7 => per_qual_rec.attribute7
3483 ,p_attribute8 => per_qual_rec.attribute8
3484 ,p_attribute9 => per_qual_rec.attribute9
3485 ,p_attribute10 => per_qual_rec.attribute10
3486 ,p_attribute11 => per_qual_rec.attribute11
3487 ,p_attribute12 => per_qual_rec.attribute12
3488 ,p_attribute13 => per_qual_rec.attribute13
3489 ,p_attribute14 => per_qual_rec.attribute14
3490 ,p_attribute15 => per_qual_rec.attribute15
3491 ,p_attribute16 => per_qual_rec.attribute16
3492 ,p_attribute17 => per_qual_rec.attribute17
3493 ,p_attribute18 => per_qual_rec.attribute18
3494 ,p_attribute19 => per_qual_rec.attribute19
3495 ,p_attribute20 => per_qual_rec.attribute20
3496 ,p_qualification_id => l_qualification_id
3497 ,p_object_version_number => l_object_version_number
3498 );
3499 end if;
3500 end;
3501 -- Bug 2831804
3502
3503
3504 --Bug 2831804 Competence
3505 procedure WATIF_PER_COMPETENCE_API(
3506 p_person_id in number
3507 ,p_competence_id in number
3508 ,p_proficiency_level_id in number
3509 ,p_effective_date_from in date
3510 ,p_effective_date_to in date
3511 ,p_attribute1 in varchar2
3512 ,p_attribute2 in varchar2
3513 ,p_attribute3 in varchar2
3514 ,p_attribute4 in varchar2
3515 ,p_attribute5 in varchar2
3516 ,p_attribute6 in varchar2
3517 ,p_attribute7 in varchar2
3518 ,p_attribute8 in varchar2
3519 ,p_attribute9 in varchar2
3520 ,p_attribute10 in varchar2
3521 ,p_attribute11 in varchar2
3522 ,p_attribute12 in varchar2
3523 ,p_attribute13 in varchar2
3524 ,p_attribute14 in varchar2
3525 ,p_attribute15 in varchar2
3526 ,p_attribute16 in varchar2
3527 ,p_attribute17 in varchar2
3528 ,p_attribute18 in varchar2
3529 ,p_attribute19 in varchar2
3530 ,p_attribute20 in varchar2
3531 ,p_business_group_id in number
3532 ,p_effective_date in date
3533 ) is
3534
3535 cursor c_comp_rec is
3536 select * from per_competence_elements cel
3537 where cel.person_id = p_person_id
3538 and cel.competence_id = p_competence_id
3539 and cel.business_group_id = p_business_group_id
3540 and p_effective_date between cel.effective_date_from and nvl(cel.effective_date_to, p_effective_date);
3541
3542 l_comp_elmnt_id number;
3543 l_object_version_number number;
3544 per_comp_elmnt_rec per_competence_elements%rowtype;
3545 l_comp_rec c_comp_rec%rowtype;
3546 begin
3547 if p_person_id is not null then
3548 per_comp_elmnt_rec.person_id := p_person_id;
3549 end if;
3550
3551 if p_business_group_id is not null then
3552 per_comp_elmnt_rec.business_group_id := p_business_group_id;
3553 end if;
3554
3555 if p_competence_id is not null then
3556 per_comp_elmnt_rec.competence_id := p_competence_id;
3557 end if;
3558
3559 if p_proficiency_level_id is not null then
3560 per_comp_elmnt_rec.proficiency_level_id := p_proficiency_level_id;
3561 end if;
3562
3563 if p_effective_date_from is not null then
3564 per_comp_elmnt_rec.effective_date_from := p_effective_date_from;
3565 end if;
3566
3567 if p_effective_date_to is not null then
3568 per_comp_elmnt_rec.effective_date_to:= p_effective_date_to;
3569 end if;
3570
3571 if p_attribute1 is not null then
3572 per_comp_elmnt_rec.attribute1 := p_attribute1;
3573 end if;
3574
3575 if p_attribute2 is not null then
3576 per_comp_elmnt_rec.attribute2 := p_attribute2;
3577 end if;
3578
3579 if p_attribute3 is not null then
3580 per_comp_elmnt_rec.attribute3 := p_attribute3;
3581 end if;
3582
3583 if p_attribute4 is not null then
3584 per_comp_elmnt_rec.attribute4 := p_attribute4;
3585 end if;
3586
3587 if p_attribute5 is not null then
3588 per_comp_elmnt_rec.attribute5 := p_attribute5;
3589 end if;
3590
3591 if p_attribute6 is not null then
3592 per_comp_elmnt_rec.attribute6 := p_attribute6;
3593 end if;
3594
3595 if p_attribute7 is not null then
3596 per_comp_elmnt_rec.attribute7 := p_attribute7;
3597 end if;
3598
3599 if p_attribute8 is not null then
3600 per_comp_elmnt_rec.attribute8 := p_attribute8;
3601 end if;
3602
3603 if p_attribute9 is not null then
3604 per_comp_elmnt_rec.attribute9 := p_attribute9;
3605 end if;
3606
3607 if p_attribute10 is not null then
3608 per_comp_elmnt_rec.attribute10 := p_attribute10;
3609 end if;
3610
3611 if p_attribute11 is not null then
3612 per_comp_elmnt_rec.attribute11 := p_attribute11;
3613 end if;
3614
3615 if p_attribute12 is not null then
3616 per_comp_elmnt_rec.attribute12 := p_attribute12;
3617 end if;
3618
3619 if p_attribute13 is not null then
3620 per_comp_elmnt_rec.attribute13 := p_attribute13;
3621 end if;
3622
3623 if p_attribute14 is not null then
3624 per_comp_elmnt_rec.attribute14 := p_attribute14;
3625 end if;
3626
3627 if p_attribute15 is not null then
3628 per_comp_elmnt_rec.attribute15 := p_attribute15;
3629 end if;
3630
3631 if p_attribute16 is not null then
3632 per_comp_elmnt_rec.attribute16 := p_attribute16;
3633 end if;
3634
3635 if p_attribute17 is not null then
3636 per_comp_elmnt_rec.attribute17 := p_attribute17;
3637 end if;
3638
3639 if p_attribute18 is not null then
3640 per_comp_elmnt_rec.attribute18 := p_attribute18;
3641 end if;
3642
3643 if p_attribute19 is not null then
3644 per_comp_elmnt_rec.attribute19 := p_attribute19;
3645 end if;
3646
3647 if p_attribute20 is not null then
3648 per_comp_elmnt_rec.attribute20 := p_attribute20;
3649 end if;
3650
3651 open c_comp_rec;
3652 fetch c_comp_rec into l_comp_rec;
3653 if c_comp_rec%found then
3654 hr_competence_element_api.update_competence_element
3655 (
3656 p_competence_element_id => l_comp_rec.competence_element_id
3657 ,p_object_version_number => l_comp_rec.object_version_number
3658 ,p_proficiency_level_id => per_comp_elmnt_rec.proficiency_level_id
3659 ,p_effective_date_from => per_comp_elmnt_rec.effective_date_from
3660 ,p_effective_date_to => per_comp_elmnt_rec.effective_date_to
3661 ,p_attribute1 => per_comp_elmnt_rec.attribute1
3662 ,p_attribute2 => per_comp_elmnt_rec.attribute2
3663 ,p_attribute3 => per_comp_elmnt_rec.attribute3
3664 ,p_attribute4 => per_comp_elmnt_rec.attribute4
3665 ,p_attribute5 => per_comp_elmnt_rec.attribute5
3666 ,p_attribute6 => per_comp_elmnt_rec.attribute6
3667 ,p_attribute7 => per_comp_elmnt_rec.attribute7
3668 ,p_attribute8 => per_comp_elmnt_rec.attribute8
3669 ,p_attribute9 => per_comp_elmnt_rec.attribute9
3670 ,p_attribute10 => per_comp_elmnt_rec.attribute10
3671 ,p_attribute11 => per_comp_elmnt_rec.attribute11
3672 ,p_attribute12 => per_comp_elmnt_rec.attribute12
3673 ,p_attribute13 => per_comp_elmnt_rec.attribute13
3674 ,p_attribute14 => per_comp_elmnt_rec.attribute14
3675 ,p_attribute15 => per_comp_elmnt_rec.attribute15
3676 ,p_attribute16 => per_comp_elmnt_rec.attribute16
3677 ,p_attribute17 => per_comp_elmnt_rec.attribute17
3678 ,p_attribute18 => per_comp_elmnt_rec.attribute18
3679 ,p_attribute19 => per_comp_elmnt_rec.attribute19
3680 ,p_attribute20 => per_comp_elmnt_rec.attribute20
3681 ,p_effective_date => p_effective_date
3682 );
3683 else
3684 hr_competence_element_api.create_competence_element
3685 (p_validate => FALSE
3686 ,p_competence_element_id => l_comp_elmnt_id
3687 ,p_object_version_number => l_object_version_number
3688 ,p_type => 'PERSONAL'
3689 ,p_business_group_id => p_business_group_id
3690 ,p_competence_id => per_comp_elmnt_rec.competence_id
3691 ,p_proficiency_level_id => per_comp_elmnt_rec.proficiency_level_id
3692 ,p_person_id => per_comp_elmnt_rec.person_id
3693 ,p_effective_date_from => per_comp_elmnt_rec.effective_date_from
3694 ,p_effective_date_to => per_comp_elmnt_rec.effective_date_to
3695 ,p_attribute1 => per_comp_elmnt_rec.attribute1
3696 ,p_attribute2 => per_comp_elmnt_rec.attribute2
3697 ,p_attribute3 => per_comp_elmnt_rec.attribute3
3698 ,p_attribute4 => per_comp_elmnt_rec.attribute4
3699 ,p_attribute5 => per_comp_elmnt_rec.attribute5
3700 ,p_attribute6 => per_comp_elmnt_rec.attribute6
3701 ,p_attribute7 => per_comp_elmnt_rec.attribute7
3702 ,p_attribute8 => per_comp_elmnt_rec.attribute8
3703 ,p_attribute9 => per_comp_elmnt_rec.attribute9
3704 ,p_attribute10 => per_comp_elmnt_rec.attribute10
3705 ,p_attribute11 => per_comp_elmnt_rec.attribute11
3706 ,p_attribute12 => per_comp_elmnt_rec.attribute12
3707 ,p_attribute13 => per_comp_elmnt_rec.attribute13
3708 ,p_attribute14 => per_comp_elmnt_rec.attribute14
3709 ,p_attribute15 => per_comp_elmnt_rec.attribute15
3710 ,p_attribute16 => per_comp_elmnt_rec.attribute16
3711 ,p_attribute17 => per_comp_elmnt_rec.attribute17
3712 ,p_attribute18 => per_comp_elmnt_rec.attribute18
3713 ,p_attribute19 => per_comp_elmnt_rec.attribute19
3714 ,p_attribute20 => per_comp_elmnt_rec.attribute20
3715 ,p_effective_date => p_effective_date
3716 );
3717 end if;
3718 end;
3719 --Bug 2831804 Competence
3720
3721 --Bug 2831804 Performance
3722
3723 procedure WATIF_PER_PERFORMANCE_API(
3724 p_person_id in number
3725 ,p_performance_rating in varchar2
3726 ,p_event_id in number
3727 ,p_review_date in date
3728 ,p_attribute1 in varchar2
3729 ,p_attribute2 in varchar2
3730 ,p_attribute3 in varchar2
3731 ,p_attribute4 in varchar2
3732 ,p_attribute5 in varchar2
3733 ,p_attribute6 in varchar2
3734 ,p_attribute7 in varchar2
3735 ,p_attribute8 in varchar2
3736 ,p_attribute9 in varchar2
3737 ,p_attribute10 in varchar2
3738 ,p_attribute11 in varchar2
3739 ,p_attribute12 in varchar2
3740 ,p_attribute13 in varchar2
3741 ,p_attribute14 in varchar2
3742 ,p_attribute15 in varchar2
3743 ,p_attribute16 in varchar2
3744 ,p_attribute17 in varchar2
3745 ,p_attribute18 in varchar2
3746 ,p_attribute19 in varchar2
3747 ,p_attribute20 in varchar2
3748 ,p_attribute21 in varchar2
3749 ,p_attribute22 in varchar2
3750 ,p_attribute23 in varchar2
3751 ,p_attribute24 in varchar2
3752 ,p_attribute25 in varchar2
3753 ,p_attribute26 in varchar2
3754 ,p_attribute27 in varchar2
3755 ,p_attribute28 in varchar2
3756 ,p_attribute29 in varchar2
3757 ,p_attribute30 in varchar2
3758 ) is
3759
3760 cursor c_perf_rec is
3761 select * from per_performance_reviews prv
3762 where prv.person_id = p_person_id
3763 and review_date = p_review_date;
3764
3765
3766 l_perf_review_id number;
3767 l_object_version_number number;
3768 l_next_review_date_warning boolean;
3769 per_perf_review_rec per_performance_reviews%rowtype;
3770 l_perf_rec c_perf_rec%rowtype;
3771 begin
3772 if p_person_id is not null then
3773 per_perf_review_rec.person_id := p_person_id;
3774 end if;
3775
3776 if p_performance_rating is not null then
3777 per_perf_review_rec.performance_rating := p_performance_rating;
3778 end if;
3779
3780 if p_event_id is not null then
3781 per_perf_review_rec.event_id := p_event_id;
3782 end if;
3783
3784 if p_review_date is not null then
3785 per_perf_review_rec.review_date := p_review_date;
3786 end if;
3787
3788 if p_attribute1 is not null then
3789 per_perf_review_rec.attribute1 := p_attribute1;
3790 end if;
3791
3792 if p_attribute2 is not null then
3793 per_perf_review_rec.attribute2 := p_attribute2;
3794 end if;
3795
3796 if p_attribute3 is not null then
3797 per_perf_review_rec.attribute3 := p_attribute3;
3798 end if;
3799
3800 if p_attribute4 is not null then
3801 per_perf_review_rec.attribute4 := p_attribute4;
3802 end if;
3803
3804 if p_attribute5 is not null then
3805 per_perf_review_rec.attribute5 := p_attribute5;
3806 end if;
3807
3808 if p_attribute6 is not null then
3809 per_perf_review_rec.attribute6 := p_attribute6;
3810 end if;
3811
3812 if p_attribute7 is not null then
3813 per_perf_review_rec.attribute7 := p_attribute7;
3814 end if;
3815
3816 if p_attribute8 is not null then
3817 per_perf_review_rec.attribute8 := p_attribute8;
3818 end if;
3819
3820 if p_attribute9 is not null then
3821 per_perf_review_rec.attribute9 := p_attribute9;
3822 end if;
3823
3824 if p_attribute10 is not null then
3825 per_perf_review_rec.attribute10 := p_attribute10;
3826 end if;
3827
3828 if p_attribute11 is not null then
3829 per_perf_review_rec.attribute11 := p_attribute11;
3830 end if;
3831
3832 if p_attribute12 is not null then
3833 per_perf_review_rec.attribute12 := p_attribute12;
3834 end if;
3835
3836 if p_attribute13 is not null then
3837 per_perf_review_rec.attribute13 := p_attribute13;
3838 end if;
3839
3840 if p_attribute14 is not null then
3841 per_perf_review_rec.attribute14 := p_attribute14;
3842 end if;
3843
3844 if p_attribute15 is not null then
3845 per_perf_review_rec.attribute15 := p_attribute15;
3846 end if;
3847
3848 if p_attribute16 is not null then
3849 per_perf_review_rec.attribute16 := p_attribute16;
3850 end if;
3851
3852 if p_attribute17 is not null then
3853 per_perf_review_rec.attribute17 := p_attribute17;
3854 end if;
3855
3856 if p_attribute18 is not null then
3857 per_perf_review_rec.attribute18 := p_attribute18;
3858 end if;
3859
3860 if p_attribute19 is not null then
3861 per_perf_review_rec.attribute19 := p_attribute19;
3862 end if;
3863
3864 if p_attribute20 is not null then
3865 per_perf_review_rec.attribute20 := p_attribute20;
3866 end if;
3867
3868 if p_attribute21 is not null then
3869 per_perf_review_rec.attribute1 := p_attribute21;
3870 end if;
3871
3872 if p_attribute22 is not null then
3873 per_perf_review_rec.attribute22 := p_attribute22;
3874 end if;
3875
3876 if p_attribute23 is not null then
3877 per_perf_review_rec.attribute23 := p_attribute23;
3878 end if;
3879
3880 if p_attribute24 is not null then
3881 per_perf_review_rec.attribute24 := p_attribute24;
3882 end if;
3883
3884 if p_attribute25 is not null then
3885 per_perf_review_rec.attribute25 := p_attribute25;
3886 end if;
3887
3888 if p_attribute26 is not null then
3889 per_perf_review_rec.attribute26 := p_attribute26;
3890 end if;
3891
3892 if p_attribute27 is not null then
3893 per_perf_review_rec.attribute27 := p_attribute27;
3894 end if;
3895
3896 if p_attribute28 is not null then
3897 per_perf_review_rec.attribute28 := p_attribute28;
3898 end if;
3899
3900 if p_attribute29 is not null then
3901 per_perf_review_rec.attribute29 := p_attribute29;
3902 end if;
3903
3904 if p_attribute30 is not null then
3905 per_perf_review_rec.attribute30 := p_attribute30;
3906 end if;
3907
3908 open c_perf_rec;
3909 fetch c_perf_rec into l_perf_rec;
3910 if c_perf_rec%found then
3911 hr_perf_review_api.update_perf_review
3912 (
3913 p_performance_review_id => l_perf_rec.performance_review_id
3914 ,p_object_version_number => l_perf_rec.object_version_number
3915 ,p_next_review_date_warning=> l_next_review_date_warning
3916 ,p_review_date => per_perf_review_rec.review_date
3917 ,p_performance_rating => per_perf_review_rec.performance_rating
3918 ,p_event_id => per_perf_review_rec.event_id
3919 ,p_attribute1 => per_perf_review_rec.attribute1
3920 ,p_attribute2 => per_perf_review_rec.attribute2
3921 ,p_attribute3 => per_perf_review_rec.attribute3
3922 ,p_attribute4 => per_perf_review_rec.attribute4
3923 ,p_attribute5 => per_perf_review_rec.attribute5
3924 ,p_attribute6 => per_perf_review_rec.attribute6
3925 ,p_attribute7 => per_perf_review_rec.attribute7
3926 ,p_attribute8 => per_perf_review_rec.attribute8
3927 ,p_attribute9 => per_perf_review_rec.attribute9
3928 ,p_attribute10 => per_perf_review_rec.attribute10
3929 ,p_attribute11 => per_perf_review_rec.attribute11
3930 ,p_attribute12 => per_perf_review_rec.attribute12
3931 ,p_attribute13 => per_perf_review_rec.attribute13
3932 ,p_attribute14 => per_perf_review_rec.attribute14
3933 ,p_attribute15 => per_perf_review_rec.attribute15
3934 ,p_attribute16 => per_perf_review_rec.attribute16
3935 ,p_attribute17 => per_perf_review_rec.attribute17
3936 ,p_attribute18 => per_perf_review_rec.attribute18
3937 ,p_attribute19 => per_perf_review_rec.attribute19
3938 ,p_attribute20 => per_perf_review_rec.attribute20
3939 ,p_attribute21 => per_perf_review_rec.attribute21
3940 ,p_attribute22 => per_perf_review_rec.attribute22
3941 ,p_attribute23 => per_perf_review_rec.attribute23
3942 ,p_attribute24 => per_perf_review_rec.attribute24
3943 ,p_attribute25 => per_perf_review_rec.attribute25
3944 ,p_attribute26 => per_perf_review_rec.attribute26
3945 ,p_attribute27 => per_perf_review_rec.attribute27
3946 ,p_attribute28 => per_perf_review_rec.attribute28
3947 ,p_attribute29 => per_perf_review_rec.attribute29
3948 ,p_attribute30 => per_perf_review_rec.attribute30
3949 );
3950 else
3951 hr_perf_review_api.create_perf_review
3952 (p_performance_review_id => l_perf_review_id
3953 ,p_object_version_number => l_object_version_number
3954 ,p_next_review_date_warning => l_next_review_date_warning
3955 ,p_performance_rating => per_perf_review_rec.performance_rating
3956 ,p_event_id => per_perf_review_rec.event_id
3957 ,p_person_id => per_perf_review_rec.person_id
3958 ,p_review_date => per_perf_review_rec.review_date
3959 ,p_attribute1 => per_perf_review_rec.attribute1
3960 ,p_attribute2 => per_perf_review_rec.attribute2
3961 ,p_attribute3 => per_perf_review_rec.attribute3
3962 ,p_attribute4 => per_perf_review_rec.attribute4
3963 ,p_attribute5 => per_perf_review_rec.attribute5
3964 ,p_attribute6 => per_perf_review_rec.attribute6
3965 ,p_attribute7 => per_perf_review_rec.attribute7
3966 ,p_attribute8 => per_perf_review_rec.attribute8
3967 ,p_attribute9 => per_perf_review_rec.attribute9
3968 ,p_attribute10 => per_perf_review_rec.attribute10
3969 ,p_attribute11 => per_perf_review_rec.attribute11
3970 ,p_attribute12 => per_perf_review_rec.attribute12
3971 ,p_attribute13 => per_perf_review_rec.attribute13
3972 ,p_attribute14 => per_perf_review_rec.attribute14
3973 ,p_attribute15 => per_perf_review_rec.attribute15
3974 ,p_attribute16 => per_perf_review_rec.attribute16
3975 ,p_attribute17 => per_perf_review_rec.attribute17
3976 ,p_attribute18 => per_perf_review_rec.attribute18
3977 ,p_attribute19 => per_perf_review_rec.attribute19
3978 ,p_attribute20 => per_perf_review_rec.attribute20
3979 ,p_attribute21 => per_perf_review_rec.attribute21
3980 ,p_attribute22 => per_perf_review_rec.attribute22
3981 ,p_attribute23 => per_perf_review_rec.attribute23
3982 ,p_attribute24 => per_perf_review_rec.attribute24
3983 ,p_attribute25 => per_perf_review_rec.attribute25
3984 ,p_attribute26 => per_perf_review_rec.attribute26
3985 ,p_attribute27 => per_perf_review_rec.attribute27
3986 ,p_attribute28 => per_perf_review_rec.attribute28
3987 ,p_attribute29 => per_perf_review_rec.attribute29
3988 ,p_attribute30 => per_perf_review_rec.attribute30
3989 );
3990 end if;
3991 end;
3992 --Bug 2831804 Performance
3993
3994 --Bug 2868775 Pay Proposal
3995
3996 procedure WATIF_PAY_PROPOSAL_API(
3997 p_person_id in number
3998 ,p_approved in varchar2
3999 ,p_change_date in date
4000 ,p_event_id in number
4001 ,p_forced_ranking in number
4002 ,p_last_change_date in date
4003 ,p_multiple_components in varchar2
4004 ,p_next_sal_review_date in date
4005 ,p_next_perf_review_date in date
4006 ,p_performance_rating in varchar2
4007 ,p_performance_review_id in number
4008 ,p_proposal_reason in varchar2
4009 ,p_proposed_salary_n in number
4010 ,p_review_date in date
4011 ,p_attribute1 in varchar2
4012 ,p_attribute2 in varchar2
4013 ,p_attribute3 in varchar2
4014 ,p_attribute4 in varchar2
4015 ,p_attribute5 in varchar2
4016 ,p_attribute6 in varchar2
4017 ,p_attribute7 in varchar2
4018 ,p_attribute8 in varchar2
4019 ,p_attribute9 in varchar2
4020 ,p_attribute10 in varchar2
4021 ,p_attribute11 in varchar2
4022 ,p_attribute12 in varchar2
4023 ,p_attribute13 in varchar2
4024 ,p_attribute14 in varchar2
4025 ,p_attribute15 in varchar2
4026 ,p_attribute16 in varchar2
4027 ,p_attribute17 in varchar2
4028 ,p_attribute18 in varchar2
4029 ,p_attribute19 in varchar2
4030 ,p_attribute20 in varchar2
4031 ,p_business_group_id in number
4032 ,p_effective_date in date
4033 ) is
4034
4035 cursor c_pay_rec is
4036 select * from per_pay_proposals pyp
4037 where pyp.assignment_id in
4038 (select assignment_id
4039 from per_all_assignments_f
4040 where person_id = p_person_id
4041 and business_group_id = p_business_group_id)
4042 and pyp.business_group_id = p_business_group_id
4043 and p_change_date = pyp.change_date;
4044
4045 cursor c_assignment_id is
4046 select assignment_id from per_all_assignments_f
4047 where person_id = p_person_id
4048 and business_group_id = p_business_group_id;
4049
4050 l_assignment_id number;
4051 l_object_version_number number;
4052 per_pay_rec per_pay_proposals%rowtype;
4053 l_pay_rec c_pay_rec%rowtype;
4054
4055 l_pay_proposal_id number;
4056 l_element_entry_id number;
4057 l_inv_next_sal_date_warning boolean;
4058 l_proposed_salary_warning boolean;
4059 l_approved_warning boolean;
4060 l_payroll_warning boolean;
4061
4062 begin
4063
4064 open c_assignment_id;
4065 fetch c_assignment_id into l_assignment_id;
4066 close c_assignment_id;
4067
4068
4069 if p_business_group_id is not null then
4070 per_pay_rec.business_group_id := p_business_group_id;
4071 end if;
4072
4073 if p_approved is not null then
4074 per_pay_rec.approved := p_approved;
4075 end if;
4076
4077 if p_change_date is not null then
4078 per_pay_rec.change_date := p_change_date;
4079 end if;
4080
4081 if p_event_id is not null then
4082 per_pay_rec.event_id := p_event_id;
4083 end if;
4084
4085 if p_forced_ranking is not null then
4086 per_pay_rec.forced_ranking := p_forced_ranking;
4087 end if;
4088
4089 if p_last_change_date is not null then
4090 per_pay_rec.last_change_date := p_last_change_date;
4091 end if;
4092
4093 if p_multiple_components is not null then
4094 per_pay_rec.multiple_components := p_multiple_components;
4095 end if;
4096
4097 if p_next_sal_review_date is not null then
4098 per_pay_rec.next_sal_review_date := p_next_sal_review_date;
4099 end if;
4100
4101 if p_next_perf_review_date is not null then
4102 per_pay_rec.next_perf_review_date := p_next_perf_review_date;
4103 end if;
4104
4105 if p_performance_rating is not null then
4106 per_pay_rec.performance_rating := p_performance_rating;
4107 end if;
4108
4109 if p_performance_review_id is not null then
4110 per_pay_rec.performance_review_id := p_performance_review_id;
4111 end if;
4112
4113 if p_proposal_reason is not null then
4114 per_pay_rec.proposal_reason := p_proposal_reason;
4115 end if;
4116
4117 if p_proposed_salary_n is not null then
4118 per_pay_rec.proposed_salary_n := p_proposed_salary_n;
4119 end if;
4120
4121 if p_review_date is not null then
4122 per_pay_rec.review_date := p_review_date;
4123 end if;
4124
4125 if p_attribute1 is not null then
4126 per_pay_rec.attribute1 := p_attribute1;
4127 end if;
4128
4129 if p_attribute2 is not null then
4130 per_pay_rec.attribute2 := p_attribute2;
4131 end if;
4132
4133 if p_attribute3 is not null then
4134 per_pay_rec.attribute3 := p_attribute3;
4135 end if;
4136
4137 if p_attribute4 is not null then
4138 per_pay_rec.attribute4 := p_attribute4;
4139 end if;
4140
4141 if p_attribute5 is not null then
4142 per_pay_rec.attribute5 := p_attribute5;
4143 end if;
4144
4145 if p_attribute6 is not null then
4146 per_pay_rec.attribute6 := p_attribute6;
4147 end if;
4148
4149 if p_attribute7 is not null then
4150 per_pay_rec.attribute7 := p_attribute7;
4151 end if;
4152
4153 if p_attribute8 is not null then
4154 per_pay_rec.attribute8 := p_attribute8;
4155 end if;
4156
4157 if p_attribute9 is not null then
4158 per_pay_rec.attribute9 := p_attribute9;
4159 end if;
4160
4161 if p_attribute10 is not null then
4162 per_pay_rec.attribute10 := p_attribute10;
4163 end if;
4164
4165 if p_attribute11 is not null then
4166 per_pay_rec.attribute11 := p_attribute11;
4167 end if;
4168
4169 if p_attribute12 is not null then
4170 per_pay_rec.attribute12 := p_attribute12;
4171 end if;
4172
4173 if p_attribute13 is not null then
4174 per_pay_rec.attribute13 := p_attribute13;
4175 end if;
4176
4177 if p_attribute14 is not null then
4178 per_pay_rec.attribute14 := p_attribute14;
4179 end if;
4180
4181 if p_attribute15 is not null then
4182 per_pay_rec.attribute15 := p_attribute15;
4183 end if;
4184
4185 if p_attribute16 is not null then
4186 per_pay_rec.attribute16 := p_attribute16;
4187 end if;
4188
4189 if p_attribute17 is not null then
4190 per_pay_rec.attribute17 := p_attribute17;
4191 end if;
4192
4193 if p_attribute18 is not null then
4194 per_pay_rec.attribute18 := p_attribute18;
4195 end if;
4196
4197 if p_attribute19 is not null then
4198 per_pay_rec.attribute19 := p_attribute19;
4199 end if;
4200
4201 if p_attribute20 is not null then
4202 per_pay_rec.attribute20 := p_attribute20;
4203 end if;
4204
4205 open c_pay_rec;
4206 fetch c_pay_rec into l_pay_rec;
4207
4208 if c_pay_rec%found then
4209 close c_pay_rec;
4210
4211 hr_maintain_proposal_api.update_salary_proposal
4212 (p_pay_proposal_id => l_pay_rec.pay_proposal_id
4213 ,p_change_date => l_pay_rec.change_date
4214 ,p_comments => per_pay_rec.comments
4215 ,p_next_sal_review_date => per_pay_rec.next_sal_review_date
4216 ,p_proposal_reason => per_pay_rec.proposal_reason
4217 ,p_proposed_salary_n => nvl(per_pay_rec.proposed_salary_n , l_pay_rec.proposed_salary_n)
4218 ,p_forced_ranking => per_pay_rec.forced_ranking
4219 ,p_performance_review_id => per_pay_rec.performance_review_id
4220 ,p_attribute_category => per_pay_rec.attribute_category
4221 ,p_attribute1 => per_pay_rec.attribute1
4222 ,p_attribute2 => per_pay_rec.attribute2
4223 ,p_attribute3 => per_pay_rec.attribute3
4224 ,p_attribute4 => per_pay_rec.attribute4
4225 ,p_attribute5 => per_pay_rec.attribute5
4226 ,p_attribute6 => per_pay_rec.attribute6
4227 ,p_attribute7 => per_pay_rec.attribute7
4228 ,p_attribute8 => per_pay_rec.attribute8
4229 ,p_attribute9 => per_pay_rec.attribute9
4230 ,p_attribute10 => per_pay_rec.attribute10
4231 ,p_attribute11 => per_pay_rec.attribute11
4232 ,p_attribute12 => per_pay_rec.attribute12
4233 ,p_attribute13 => per_pay_rec.attribute13
4234 ,p_attribute14 => per_pay_rec.attribute14
4235 ,p_attribute15 => per_pay_rec.attribute15
4236 ,p_attribute16 => per_pay_rec.attribute16
4237 ,p_attribute17 => per_pay_rec.attribute17
4238 ,p_attribute18 => per_pay_rec.attribute18
4239 ,p_attribute19 => per_pay_rec.attribute19
4240 ,p_attribute20 => per_pay_rec.attribute20
4241 ,p_object_version_number => l_pay_rec.object_version_number
4242 ,p_multiple_components => per_pay_rec.multiple_components
4243 ,p_approved => per_pay_rec.approved
4244 ,p_validate => FALSE
4245 ,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
4246 ,p_proposed_salary_warning => l_proposed_salary_warning
4247 ,p_approved_warning => l_approved_warning
4248 ,p_payroll_warning => l_payroll_warning
4249 );
4250 else
4251 close c_pay_rec;
4252
4253 hr_maintain_proposal_api.insert_salary_proposal
4254 (p_pay_proposal_id => l_pay_proposal_id
4255 ,p_assignment_id => l_assignment_id
4256 ,p_business_group_id => per_pay_rec.business_group_id
4257 ,p_change_date => per_pay_rec.change_date
4258 ,p_comments => per_pay_rec.comments
4259 ,p_next_sal_review_date => per_pay_rec.next_sal_review_date
4260 ,p_proposal_reason => per_pay_rec.proposal_reason
4261 ,p_proposed_salary_n => per_pay_rec.proposed_salary_n
4262 ,p_forced_ranking => per_pay_rec.forced_ranking
4263 ,p_performance_review_id => per_pay_rec.performance_review_id
4264 ,p_attribute_category => per_pay_rec.attribute_category
4265 ,p_attribute1 => per_pay_rec.attribute1
4266 ,p_attribute2 => per_pay_rec.attribute2
4267 ,p_attribute3 => per_pay_rec.attribute3
4268 ,p_attribute4 => per_pay_rec.attribute4
4269 ,p_attribute5 => per_pay_rec.attribute5
4270 ,p_attribute6 => per_pay_rec.attribute6
4271 ,p_attribute7 => per_pay_rec.attribute7
4272 ,p_attribute8 => per_pay_rec.attribute8
4273 ,p_attribute9 => per_pay_rec.attribute9
4274 ,p_attribute10 => per_pay_rec.attribute10
4275 ,p_attribute11 => per_pay_rec.attribute11
4276 ,p_attribute12 => per_pay_rec.attribute12
4277 ,p_attribute13 => per_pay_rec.attribute13
4278 ,p_attribute14 => per_pay_rec.attribute14
4279 ,p_attribute15 => per_pay_rec.attribute15
4280 ,p_attribute16 => per_pay_rec.attribute16
4281 ,p_attribute17 => per_pay_rec.attribute17
4282 ,p_attribute18 => per_pay_rec.attribute18
4283 ,p_attribute19 => per_pay_rec.attribute19
4284 ,p_attribute20 => per_pay_rec.attribute20
4285 ,p_object_version_number => l_object_version_number
4286 ,p_multiple_components => nvl(per_pay_rec.multiple_components ,'N ')
4287 ,p_approved => per_pay_rec.approved
4288 ,p_validate => FALSE
4289 ,p_element_entry_id => l_element_entry_id
4290 ,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
4291 ,p_proposed_salary_warning => l_proposed_salary_warning
4292 ,p_approved_warning => l_approved_warning
4293 ,p_payroll_warning => l_payroll_warning
4294 );
4295
4296 end if;
4297 end;
4298
4299 --Bug 2868775 Pay Proposal
4300
4301 procedure WATIF_TEMPORAL_LF_EVT_API(
4302 p_person_id in number
4303 ,p_business_group_id in number
4304 ,p_ler_id in number
4305 ,p_temporal_lf_evt in varchar2
4306 ,p_lf_evt_ocrd_dt in date
4307 ,p_effective_date in date
4308 ,p_tpf_val in number default null
4309 ,p_cmp_val in number default null
4310 ,p_cmp_bnft_val in number default null
4311 ,p_cmp_bal_val in number default null
4312 ,p_hwf_val in number default null
4313 ,p_hwf_bnft_val in number default null
4314 )is
4315 --
4316 l_ASSIGNMENT_BUDGET_VALUE_ID number;
4317 l_ptnl_ler_for_per_id ben_ptnl_ler_for_per.ptnl_ler_for_per_id%type;
4318 l_object_version_number ben_ptnl_ler_for_per.object_version_number%type;
4319 l_mnl_dt date;
4320 l_dtctd_dt date;
4321 l_procd_dt date;
4322 l_unprocd_dt date;
4323 l_voidd_dt date;
4324 --
4325 cursor c_assign is
4326 select paf.ASSIGNMENT_ID,
4327 paf.BUSINESS_GROUP_ID,
4328 paf.effective_start_date,
4329 paf.effective_end_date
4330 from per_all_people_f per,
4331 per_assignments_f paf
4332 where per.person_id = p_person_id
4333 and paf.assignment_type <> 'C'
4334 and p_effective_date
4335 between per.effective_start_date
4336 and per.effective_end_date
4337 and per.business_group_id +0 = p_business_group_id
4338 and per.person_id = paf.person_id
4339 and paf.business_group_id +0 = p_business_group_id
4340 and paf.primary_flag = 'Y'
4341 and p_effective_date
4342 between paf.effective_start_date
4343 and paf.effective_end_date;
4344 --
4345 l_assign c_assign%rowtype;
4346 --
4347 cursor c_exist_fte is
4348 select pab.ASSIGNMENT_BUDGET_VALUE_ID
4349 from per_all_people_f per,
4350 per_assignments_f paf,
4351 per_assignment_budget_values pab
4352 where per.person_id = p_person_id
4353 and paf.assignment_type <> 'C'
4354 and p_effective_date
4355 between per.effective_start_date
4356 and per.effective_end_date
4357 and per.business_group_id +0 = p_business_group_id
4358 and per.person_id = paf.person_id -- paf.assignment_id = perassignment_id
4359 and paf.business_group_id +0 = p_business_group_id
4360 and paf.primary_flag = 'Y'
4361 and p_effective_date
4362 between paf.effective_start_date
4363 and paf.effective_end_date
4364 and pab.business_group_id +0 = paf.business_group_id
4365 and pab.assignment_id = paf.assignment_id
4366 and pab.unit = 'FTE';
4367 --
4368 begin
4369 --
4370 hr_utility.set_location(' Entering: WATIF_AGE_TEMPORAL_LF_EVT_API' , 10);
4371 --
4372 -- Store the user entered temporal values in the globals.
4373 -- When profile with compensations factor attached is looked
4374 -- for eligibility this value will be used instead of
4375 -- values from the data base.Modules : bendrpar.pkb bendefct.pkb
4376 --
4377 if p_temporal_lf_evt = 'CMP' then
4378 --
4379 if p_cmp_val is not null then
4380 ben_whatif_elig.g_stat_comp := p_cmp_val;
4381 end if;
4382 --
4383 if p_cmp_bnft_val is not null then
4384 ben_whatif_elig.g_bnft_bal_comp := p_cmp_bnft_val;
4385 end if;
4386 --
4387 if p_cmp_bal_val is not null then
4388 ben_whatif_elig.g_bal_comp := p_cmp_bal_val;
4389 end if;
4390 --
4391 elsif p_temporal_lf_evt = 'HRW' then
4392 --
4393 if p_hwf_val is not null then
4394 ben_whatif_elig.g_bal_hwf_val := p_hwf_val;
4395 end if;
4396 --
4397 if p_hwf_bnft_val is not null then
4398 ben_whatif_elig.g_bnft_bal_hwf_val := p_hwf_bnft_val;
4399 end if;
4400 --
4401 end if;
4402 --
4403 /*
4404 Following code commented as the effective date is set for each
4405 case differently.
4406 if p_temporal_lf_evt = 'AGE' then
4407 -- update all the age determination codes to as of effective date
4408 update ben_age_fctr
4409 set AGE_DET_CD = 'AED',
4410 AGE_DET_RL = null
4411 where business_group_id = p_business_group_id;
4412 elsif p_temporal_lf_evt = 'LOS' then
4413 update ben_los_fctr
4414 set los_det_cd = 'AED',
4415 los_det_rl = null
4416 where business_group_id = p_business_group_id;
4417 elsif p_temporal_lf_evt = 'CMP' then
4418 update ben_comp_lvl_fctr
4419 set comp_lvl_det_cd = 'AED',
4420 comp_lvl_det_rl = null
4421 where business_group_id = p_business_group_id;
4422 end if; */
4423 if p_temporal_lf_evt = 'TPF' then
4424 --
4425 null;
4426 /* set use primary assignment flag = y, and other flags = n;
4427 if a record in the per_assignment_budget_values exists then
4428 change all non primary ones to 0;
4429 change primary one to the value entered by the user;
4430 else
4431 create a record in the per_assignment_budget values if
4432 a primary assignment exists else error out;
4433 end if;
4434 */
4435 -- 9999 do I need to worry about mn_pct_val etc.,
4436 update ben_pct_fl_tm_fctr
4437 set USE_PRMRY_ASNT_ONLY_FLAG = 'Y',
4438 USE_SUM_OF_ALL_ASNTS_FLAG = 'N'
4439 where business_group_id = p_business_group_id;
4440 --
4441 open c_exist_fte;
4442 --
4443 fetch c_exist_fte into l_ASSIGNMENT_BUDGET_VALUE_ID;
4444 if c_exist_fte%notfound then
4445 --
4446 open c_assign;
4447 null;
4448 fetch c_assign into l_assign;
4449 if c_assign%notfound then
4450 --
4451 fnd_message.set_name('BEN','BEN_91443_NO_ASSGN');
4452 fnd_message.raise_error;
4453 --
4454 else
4455 insert into per_assignment_budget_values_f
4456 (assignment_budget_value_id
4457 ,business_group_id
4458 ,assignment_id
4459 ,last_update_date
4460 ,last_updated_by
4461 ,last_update_login
4462 ,created_by
4463 ,creation_date
4464 ,unit
4465 ,value
4466 ,effective_start_date
4467 ,effective_end_date)
4468 values (
4469 per_assignment_budget_values_s.nextval
4470 ,l_assign.business_group_id
4471 ,l_assign.assignment_id
4472 ,sysdate
4473 ,null
4474 ,null
4475 ,null
4476 ,sysdate
4477 ,'FTE'
4478 ,p_tpf_val
4479 ,l_assign.effective_start_date
4480 ,l_assign.effective_end_date);
4481 end if;
4482 --
4483 else
4484 --
4485 update PER_ASSIGNMENT_BUDGET_VALUES_F
4486 set VALUE = p_tpf_val
4487 where ASSIGNMENT_BUDGET_VALUE_ID = l_ASSIGNMENT_BUDGET_VALUE_ID;
4488 --
4489 end if;
4490 --
4491 close c_exist_fte;
4492 --
4493 end if;
4494 --
4495 -- Create potential life event
4496 --
4497 ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
4498 (p_validate => false
4499 ,p_ptnl_ler_for_per_id => l_ptnl_ler_for_per_id
4500 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
4501 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
4502 ,p_ntfn_dt => trunc(sysdate)
4503 ,p_ler_id => p_ler_id
4504 ,p_person_id => p_person_id
4505 ,p_business_group_id => p_business_group_id
4506 ,p_object_version_number => l_object_version_number
4507 ,p_effective_date => p_effective_date
4508 ,p_dtctd_dt => p_effective_date);
4509 --
4510 hr_utility.set_location(' Leaving: WATIF_AGE_TEMPORAL_LF_EVT_API' , 10);
4511 --
4512 end;
4513 --
4514 procedure p_init_watif_globals is
4515 begin
4516 --
4517 ben_whatif_elig.g_stat_comp := null;
4518 ben_whatif_elig.g_bnft_bal_comp := null;
4519 ben_whatif_elig.g_bal_comp := null;
4520 ben_whatif_elig.g_bnft_bal_hwf_val := null;
4521 ben_whatif_elig.g_bal_hwf_val := null;
4522 --
4523 end;
4524 --
4525 END ben_whatif_elig;